ZB-027-01-数据库原理

DDL-SQL

  • create table
  • drop table
  • alter table 修改表，添加列，建索引

随用随查

基本SQL

  • insert into
  • delete from
  • update
  • select

重要概念

  • sql的关键字不区分大小写
  • 命名风格是 user_name 下划线形式
  • 物理删除 、 逻辑删除
  • 创建表的时候 如果字段是 sql的关键字 请使用这样的形式：“反引号包裹字段名”
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 用户表
CREATE TABLE USER (
    ID BIGINT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    -- 密码一定不能存明文， 参考 CSDN 明文存密码
    PASSWORD VARCHAR(100) NOT NULL,
    TEL VARCHAR (200) NOT NULL UNIQUE,
    ADDRESS VARCHAR (100),
    CREATED_AT TIMESTAMP NOT NULL DEFAULT NOW(),
    UPDATED_AT TIMESTAMP NOT NULL DEFAULT NOW(),
    -- 逻辑删除的标识
    -- STATUS TINYINT NOT NULL
);

-- 新增列,由于之前已经有了数据  ，再次新增列 not null 就会失败，所以设置 default
alter table USER add STATUS TINYINT NOT NULL default 1

-- delete 操作是非常危险的

-- 删除用户 id=2的数据的正确姿势,应该同步更新 updated_at字段
update user set status=0, updated_at=now() where id=2

select之一次数据库查询的时间是多少？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CPU时钟周期
  CPU 3Ghz的电脑 = 3G/s  30亿次/秒
  指令周期  0.3ns = 0.3 x 10的-9次方 秒

内存 
  寻址时间：10us 微秒 =  10x10的-6次方 秒 (比CPU慢一个数量级) 

SSD 
  寻址时间：100us = 100x10的-6次方 秒

HDD 
  寻址时间：1～10ms 毫秒  = 10的-3次方 ～ 10的-2次方 秒

同机房网络IO
  1ms 毫秒  = 10的-3次方 秒

数据库操作
  典型时间： 1ms (查询算快的情况)

数据库存多少算是多的？

  • 过早优化乃万恶之源！！！
  • 过早优化乃万恶之源！！！
  • 过早优化乃万恶之源！！！
1
2
3
4
5
1w / 100w / 1000w / 1亿

对于mysql来说 100w ～ 1000w 对它没啥本质区别

上亿才会对他性能有所下架

money这种敏感信息如何存

  • int分 *100 有点low
  • decimal

使用JDBC访问数据库

  • Java Database Connection
    • 连接串
    • 用户名
    • 密码
  • Statement 语句
  • PrepareStatement 防SQL注入的
  • ResultSet

Statement

  • 最好别用，有注入问题
1
2
3
4
5
6
7
// 如果用户这样输入 字符串 name = "zhangsan' or '1'='1" 就会骗过mysql 查出所有的用户
try (Connection conn = DriverManager.getConnection(Config.JDBC_URL);
      Statement st = conn.createStatement()) {
    ResultSet rs = null;
    rs = st.executeQuery("select * from USER where NAME='"+name +"'");
    printResult(rs);
}
  • 原因是 Statement 是把sql整个字符串 变成 sql的 AST

最好使用 PrepareStatement

  • 防注入
1
2
PreparedStatement st = conn.prepareStatement("select * from USER where NAME= ?")) {
st.setString(1,name);
  • PreparedStatement 是先把sql字符串 变成 sql的 AST，然后在把对应内容 设置到AST的节点上

sql操作

建表语句和灌入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- 用户表
CREATE TABLE USER (
    ID BIGINT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    TEL VARCHAR (200) NOT NULL UNIQUE,
    ADDRESS VARCHAR (100),
    CREATED_AT TIMESTAMP NOT NULL DEFAULT NOW(),
    UPDATED_AT TIMESTAMP NOT NULL DEFAULT NOW()
);

-- 商品表
CREATE TABLE GOODS(
    ID BIGINT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    PRICE DECIMAL NOT NULL,
    CREATED_AT TIMESTAMP NOT NULL DEFAULT NOW(),
    UPDATED_AT TIMESTAMP NOT NULL DEFAULT NOW()
);

-- 订单表
CREATE TABLE `ORDER`(
    ID BIGINT PRIMARY KEY AUTO_INCREMENT,
    USER_ID BIGINT,
    GOODS_ID BIGINT,
    GOODS_NUM INT,  -- 下单的商品数量
    GOODS_PRICE DECIMAL NOT NULL, -- 下单时的商品单价
    CREATED_AT TIMESTAMP NOT NULL DEFAULT NOW(),
    UPDATED_AT TIMESTAMP NOT NULL DEFAULT NOW()
);

INSERT INTO USER (ID, NAME, TEL, ADDRESS) VALUES (1, 'zhangsan', 'tel1', 'beijing');
INSERT INTO USER (ID, NAME, TEL, ADDRESS) VALUES (2, 'lisi', 'tel2', 'shanghai');
INSERT INTO USER (ID, NAME, TEL, ADDRESS) VALUES (3, 'wangwu', 'tel3', 'shanghai');
INSERT INTO USER (ID, NAME, TEL, ADDRESS) VALUES (4, 'zhangsan', 'tel4', 'shenzhen');

INSERT INTO GOODS (ID, NAME, PRICE) VALUES (1, 'goods1', 10);
INSERT INTO GOODS (ID, NAME, PRICE) VALUES (2, 'goods2', 20);
INSERT INTO GOODS (ID, NAME, PRICE) VALUES (3, 'goods3', 30);
INSERT INTO GOODS (ID, NAME, PRICE) VALUES (4, 'goods4', 40);
INSERT INTO GOODS (ID, NAME, PRICE) VALUES (5, 'goods5', 50);

INSERT INTO `ORDER` (ID,USER_ID, GOODS_ID, GOODS_NUM, GOODS_PRICE) VALUES (1,1, 1, 5, 10);
INSERT INTO `ORDER` (ID,USER_ID, GOODS_ID, GOODS_NUM, GOODS_PRICE) VALUES (2,2, 1, 1, 10);
INSERT INTO `ORDER` (ID,USER_ID, GOODS_ID, GOODS_NUM, GOODS_PRICE) VALUES (3,2, 1, 2, 10);
INSERT INTO `ORDER` (ID,USER_ID, GOODS_ID, GOODS_NUM, GOODS_PRICE) VALUES (4,4, 2, 4, 20);
INSERT INTO `ORDER` (ID,USER_ID, GOODS_ID, GOODS_NUM, GOODS_PRICE) VALUES (5,4, 2, 100, 20);
INSERT INTO `ORDER` (ID,USER_ID, GOODS_ID, GOODS_NUM, GOODS_PRICE) VALUES (6,4, 3, 1, 20);
INSERT INTO `ORDER` (ID,USER_ID, GOODS_ID, GOODS_NUM, GOODS_PRICE) VALUES (7,5, 4, 1, 20);
INSERT INTO `ORDER` (ID,USER_ID, GOODS_ID, GOODS_NUM, GOODS_PRICE) VALUES (8,5, 6, 1, 60);
  • 表信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
用户表：
+----+----------+------+----------+
| ID | NAME     | TEL  | ADDRESS  |
+----+----------+------+----------+
| 1  | zhangsan | tel1 | beijing  |
+----+----------+------+----------+
| 2  | lisi     | tel2 | shanghai |
+----+----------+------+----------+
| 3  | wangwu   | tel3 | shanghai |
+----+----------+------+----------+
| 4  | zhangsan | tel4 | shenzhen |
+----+----------+------+----------+
商品表：
+----+--------+-------+
| ID | NAME   | PRICE |
+----+--------+-------+
| 1  | goods1 | 10    |
+----+--------+-------+
| 2  | goods2 | 20    |
+----+--------+-------+
| 3  | goods3 | 30    |
+----+--------+-------+
| 4  | goods4 | 40    |
+----+--------+-------+
| 5  | goods5 | 50    |
+----+--------+-------+
订单表：
+------------+-----------------+------------------+---------------------+-------------------------------+
| ID(订单ID) | USER_ID(用户ID) | GOODS_ID(商品ID) | GOODS_NUM(商品数量) | GOODS_PRICE(下单时的商品单价)        |
+------------+-----------------+------------------+---------------------+-------------------------------+
| 1          | 1               | 1                | 5                   | 10                            |
+------------+-----------------+------------------+---------------------+-------------------------------+
| 2          | 2               | 1                | 1                   | 10                            |
+------------+-----------------+------------------+---------------------+-------------------------------+
| 3          | 2               | 1                | 2                   | 10                            |
+------------+-----------------+------------------+---------------------+-------------------------------+
| 4          | 4               | 2                | 4                   | 20                            |
+------------+-----------------+------------------+---------------------+-------------------------------+
| 5          | 4               | 2                | 100                 | 20                            |
+------------+-----------------+------------------+---------------------+-------------------------------+
| 6          | 4               | 3                | 1                   | 20                            |
+------------+-----------------+------------------+---------------------+-------------------------------+
| 7          | 5               | 4                | 1                   | 20                            |
+------------+-----------------+------------------+---------------------+-------------------------------+
| 8          | 5               | 6                | 1                   | 60                            |
+------------+-----------------+------------------+---------------------+-------------------------------+

sql练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
# 01 查询所有用户
select * from USER;

# 02 查询用户个数
select count(*) from USER;

# 03 查询 地址 = 'shanghai' 用户
select * from USER where ADDRESS='shanghai'

# 04 查询 地址 = 'shanghai' 用户个数
select count(*) from USER where ADDRESS='shanghai'

# 05 查询具体的列
select id,name from USER where ADDRESS='shanghai'

# 06 在上海的用户 id降序排列
select id,name from USER where ADDRESS='shanghai' order by id desc;

# 07 用户列表分页 limit<从第几个元素开始>,<最多返回几个元素>
# limit <(pageNo-1)*pageSize>,<pageSize>
select * from USER limit 2,2;

# 08 按地区分组
select ADDRESS from USER group by ADDRESS;

# 09 每个地区有多少人
select ADDRESS,count(*) from USER group by ADDRESS;

# 10 订单表：每个商品下了几单
select GOODS_ID,count(*) from `ORDER` group by GOODS_ID;

# 11 订单表：每个商品下了几单 ——列名：重新命名
select GOODS_ID,count(*)as count from `ORDER` group by GOODS_ID;

# 12 订单表：每个订单商品和对应的总价格
select GOODS_ID,(GOODS_NUM*GOODS_PRICE)as total_price from `ORDER`;

# 13 订单表：每个商品对应的销售额
select GOODS_ID,sum(GOODS_NUM*GOODS_PRICE) from `ORDER` group by GOODS_ID;

# 14 订单表：每个商品对应的销售额降序排列
select GOODS_ID,sum(GOODS_NUM*GOODS_PRICE)as total from `ORDER` group by GOODS_ID order by total desc;

进阶：JOIN操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
# join操作,
# 单独一个 join 是内连接 inner join 两个表共有数据

# 15 订单信息表， 订单和商品名称
select `ORDER`.id,`ORDER`.USER_ID,`ORDER`.GOODS_ID,GOODS.NAME from `ORDER`
  join GOODS
on `ORDER`.GOODS_ID = GOODS.ID;
# 简化
select o.id,o.USER_ID,o.GOODS_ID,g.NAME from `ORDER` as o
  join GOODS as g
on o.GOODS_ID = g.ID;

# 16 查询所有订单的信息，和对应的 商品名称 ,哪怕这个goods没有
select o.id,o.USER_ID,o.GOODS_ID,g.NAME from `ORDER` as o
  left join GOODS as g
on o.GOODS_ID = g.ID;

# 17 连表 订单信息，商品名称，用户信息(存在的用户和订单)
select o.id,o.USER_ID,o.GOODS_ID,g.NAME,u.NAME,u.TEL,u.ADDRESS from `ORDER` as o
  join GOODS as g
on o.GOODS_ID = g.ID
  join USER as u
on o.USER_ID = u.ID;

# 18 连表 订单信息，商品名称，用户信息,（即使用户不存在）
select o.id,o.USER_ID,o.GOODS_ID,g.NAME,u.NAME,u.TEL,u.ADDRESS from `ORDER` as o
  join GOODS as g
on o.GOODS_ID = g.ID
  left join USER as u
on o.USER_ID = u.ID;

# 19 查询 beijing 的用户订单信息
select o.id,o.USER_ID,o.GOODS_ID,g.NAME,u.NAME,u.TEL,u.ADDRESS from `ORDER` as o
  join GOODS as g
on o.GOODS_ID = g.ID
  join USER as u
on o.USER_ID = u.ID
where u.ADDRESS = 'beijing';

综合练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 01 查询有多少用户买过指定商品 如 goods_id = 1
# 01-1 查询指定商品
select * from `ORDER` where GOODS_ID = 1;
# 有三条 uid=1的一条订单， uid=2的两条订单
# 所以你不能直接 这样 因为没去重复 返回的是3 ，应该是2
select count(*) from `ORDER` where GOODS_ID = 1;
# 第一种方式
# 01-2 谁曾经下过单 distinct 去重复
select distinct USER_ID from `ORDER` where GOODS_ID = 1;
# 01-3 count 统计
select count(distinct USER_ID) from `ORDER` where GOODS_ID = 1;

# 第二种方式 子查询
# 01-4
select * from USER where id in (
select USER_ID from `ORDER` where GOODS_ID = 1
);
# 等价于 select * from USER where id in (1,2,2);
# 01-5
select count(*) from USER where id in (
  select USER_ID from `ORDER` where GOODS_ID = 1
);

代码参考