1
2
3
4
5

-- IF EXISTS：如果存在才会操作，IF NOT EXISTS：反之
DROP TABLE IF EXISTS cards;

DROP TABLE IF EXISTS persons;
DROP TABLE IF EXISTS users;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE IF NOT EXISTS persons (
  id INT PRIMARY KEY AUTO_INCREMENT,
  id_card CHAR(18) UNIQUE COMMENT '身份证',  -- UNIQUE 唯一，身份证应该是唯一的，也是固定长度都 18位
  name VARCHAR(30) NOT NULL COMMENT '名字（[DLE]不允许为空）', -- varchar(n)  n<= 65,535
  phone CHAR(11) COMMENT '手机号',   -- 手机号的长度是定长都是11位
  nickname varchar(30) COMMENT '昵称（允许为空）',
  birthday DATE COMMENT '生日',  -- YYYY-MM-dd
  created_at TIMESTAMP NOT NULL default CURRENT_TIMESTAMP COMMENT '记录的创建时间', -- YYYY-MM-DD HH:ii:ss 默认值是函数，数据插入时，如果没值，用插入的时间作为这个时间
  modified_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录的变更时间' -- 每次记录变更，自动更新到当前时间
);

select LENGTH('陈文'); -- 6，如果不确定你要插入字符长度，使用这个来检查

1
2
3
4
5

INSERT INTO persons VALUES (DEFAULT, '35010419X70Y25493X', 'ichexw', null, null, '1987-05-25', DEFAULT, null); -- 插入全部数据
INSERT INTO persons (id_card, name) VALUES ('35010419XX05X5493X', 'ichexw2');  -- 插入指定列数据，只能忽略空的字段或者非空有设置默认值的字段
INSERT INTO persons (id_card, name) VALUES
  ('35010419X70Y254931', 'ichexw3'),
  ('35010419X70Y254932', 'ichexw4');  -- 一次插入多条

1
2
3
4
5
6

UPDATE persons SET nickname='[DLE]nickname1' WHERE id = 1;  -- 这个时候 第一条记录会昵称会变成 nickname1，而且 modified_at 会更新到当前时间
UPDATE persons SET 
  nickname='[DLE]nickname2',
  name='陈某某',
  phone='13515021100'
WHERE id = 2;  -- 也可以更新多个字段

1
2
3
4
5
6
7
8
9

ALTER TABLE persons ADD description varchar(200) COMMENT '描述';    -- 添加字段
ALTER TABLE persons MODIFY description varchar(200) COMMENT '描述' AFTER birthday; -- 修改字段，把位子放到生日后面（AFTER）
ALTER TABLE persons DROP description; -- 删除字段
ALTER TABLE persons ADD COLUMN description varchar(200)  COMMENT '描述' AFTER birthday; -- 添加字段到生日后面
ALTER TABLE persons RENAME COLUMN description TO note;    -- 变更字段名， 一定要有 COLUMN
-- ADD/MODIFY/DROP 后面的 COLUMN 不是必须的，重命名字段一定要有 COLUMN

-- 变更表名字
ALTER TABLE persons RENAME TO users;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE IF NOT EXISTS cards (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id int REFERENCES users(id), -- 外键约束，关联用户表的 id
  card_no CHAR(19) NOT NULL UNIQUE COMMENT '卡号', 
  balance DECIMAL(15, 2) NOT NULL DEFAULT 0 COMMENT '余额',
  created_at TIMESTAMP NOT NULL default CURRENT_TIMESTAMP COMMENT '记录的创建时间',
  modified_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录的变更时间'
)

INSERT cards (user_id, card_no) VALUES
  (1, '6118481162710558818'),
  (1, '6128482262710558818'),
  (1, '6348483362710558818');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 内连接
SELECT * FROM cards c INNER JOIN users u ON c.user_id=u.id;
SELECT * FROM cards c, users u WHERE c.user_id=u.id;  -- 和上一个语句是等价的
-- 只会关联出 cards 和 users 表中都有的数据

-- 左外连接
SELECT * FROM cards c LEFT JOIN users u ON c.user_id=u.id;
-- cards 的数据全保留

-- 右外连接
SELECT * FROM cards c RIGHT JOIN users u ON c.user_id=u.id;
-- users 的数据全保留，如果左表数据有多条，会关联多条

 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

-- 一个用户和卡是一对多，一个银行员工和卡是一对多的关系，那么用户和银行员工是多对多的关系
CREATE TABLE IF NOT EXISTS staffs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  staff_code CHAR(10) NOT NULL COMMENT '员工号',
  staff_name VARCHAR(30) NOT NULL COMMENT '员工名称',
  hire_date DATE NOT NULL COMMENT '入职时间',
  created_at TIMESTAMP NOT NULL default CURRENT_TIMESTAMP COMMENT '记录的创建时间',
  modified_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录的变更时间'
);

INSERT INTO staffs (staff_code, staff_name, hire_date) VALUES
  ('35000pl', '小星', CURRENT_DATE),
  ('35000px', '小文', CURRENT_DATE);

INSERT cards (user_id, card_no) VALUES
  (2, '6118481162710558819'),
  (2, '6128482262710558819'),
  (2, '6348483362710558819');
  
-- 给卡添加开卡柜员号
ALTER TABLE cards ADD created_staff_id INT COMMENT '开卡员工ID' AFTER created_at;
-- 添加外键关联
ALTER TABLE cards 
  ADD CONSTRAINT ‘staff_create_card’
  FOREIGN KEY (created_staff_id)
  REFERENCES staffs(id)
  -- ON DELETE SET NULL     -- 某个员工删除的，将 created_staff_id（关联的外键）设成 null
  -- ON DELETE CASCADE      -- 某个员工删除的，cards 中关联的数据会同步删除
  ;
-- 删除外键约束
-- ALTER TABLE cards DROP CONSTRAINT staff_create_card;

UPDATE cards 
SET created_staff_id = 1
WHERE id IN (1, 3, 8);
UPDATE cards 
SET created_staff_id = 2
WHERE id IN (2, 7, 9);

SELECT id, id IN (2, 7, 9) from cards; -- 可以把 where 语句输入到 select 中，验证下结果，0 代表不成立，1代表不成立

-- 表关联（三张表）
SELECT u.name, u.id_card, s.staff_code, c.* FROM   -- c.* 表示取 cards 表中的所有字段
  cards c 
    INNER JOIN users u ON c.user_id=u.id 
    INNER JOIN staffs s ON c.created_staff_id=s.id;