Java与SQL

数据库的作用

• 数据库提供结构化数据的持久化存储
• 索引保证数据查询的速度
• 事务的原子性保证数据不丢失

数据库基本概念

• Schema：数据表
• 数据库的类型与SQL语句
  • int/bigint
  • varchar(length)/TEXT
  • timestamp
• 行与列
• 外键
• JDBC：Java Database Connection
  • 本质：你给我一个连接字符串，我就能读取你的数据库信息
  • 使用JDBC存取数据
  • 一般有：连接串、用户名、密码

DDL

平时用得较少，可以需要时查资料，基本都是一次操作

1. create table

2. drop table

3. alter table

基本SQL

平时用得最多的

1. insert into

2. delete from

3. update

4. select

一些注意事项

• SQL语句不区分大小写
• 命名风格是下划线命名
• 数据库的字符串是单引号
• 数据库的注释是 —
• 分号分割多个SQL语句

增删改查的基本概念

• 数据库的查询时间包括：指令的执行时间，磁盘IO时间，网络IO时间
• 数据库操作的经典时间为：1ms（一般会比这个长，不会比这个短）

INSERT INTO user (name, password, tel, avatar, create_at, update_at)
    VALUES ('xxx', '123456', '1388888888', 'http://image.png', now(), now());
ALTER TABLE user ADD status tinyint NOT null DEFAULT 1;
UPDATE user SET status = 0, update_at = now() WHERE id = 1;

Select

• select *
• select count(*) count(1)
• select max/min/avg
• select limit 分页
  select * from user limit 1(从第几个开始找), 4(最多返回几个元素 )
• select order by 排序
• select is null/is not null
• select where id in (select id from …) 子查询

JDBC & SQL注入

1. 使用JDBC访问数据库

• statement（SQL语句）
• prepareStatement - 防止SQL注入的

• resultSet

  2. SQL注入

• SQL没有验证传入的参数，导致攻击者精心设计的参数拼装出的SQL达到了他的目的

• 防范：不直接使用statement，而是使用prepareStatement预先编译statement

用Docker安装数据库

• 解决宿主机兼容问题
• 统一、方便、卸载无残留
• 数据默认只在容器中有效，不是持久化的
  • 除非启动容器的时候使用-v参数
    -v /path/dir:/var/lib/postgresql/data

表的设计原则

• 每个实体对应一张表（比如：用户、商品）
  • 每个实体都有一个主键ID
  • 按照业务需要建索引
• 每个关系用一张表联系
  • 关系型数据库

JOIN

1. 为什么需要JOIN查询

• 需要多表联合的信息

• 例如：需要订单和商品的信息
  SELECT order.id, order.user_id, order.goods_id, goods.name FROM order JOIN goods ON order.goods_id = goods.id;

  2. INNER JOIN

• 选择两张表里都有的数据

  3. LEFT JOIN

• 选择所有在左表存在的

  4. RIGHT JOIN

• 选择所有在右表存在的