MySQL数据库第1章：数据库入门

知识点：
MySQL数据库第1章：数据库入门
对应SQL命令：

# 退出MySQL服务器登录,以下两种退出方式，任性一种即可
exit;
quit;

# 设置密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

# 取消密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '';

# 查看MySQL服务器状态信息
status

MySQL数据库第2章：数据库基本操作

知识点：
MySQL数据库第2章：数据库基本操作
对应SQL命令：

/* 2.1 数据库操作 */

# 创建一个名称为mydb的数据库
CREATE DATABASE mydb;
CREATE DATABASE IF NOT EXISTS mydb;

# 查看错误警告信息
SHOW WARNINGS;

# 查看MySQL服务器中已经存在的数据库
SHOW DATABASES;

# 查看创建mydb数据库的语句
SHOW CREATE DATABASE mydb;

# 选择数据库
USE mydb;

# 删除数据库
DROP DATABASE mydb;
DROP DATABASE IF EXISTS mydb;


# 此处填写单行注释内容，如：若服务器中没有mydb数据库，则创建，否则忽略此SQL
CREATE DATABASE IF NOT EXISTS mydb;
-- 此处填写单行注释内容，如：若服务器中存在mydb数据库，则删除，否则忽略此SQL
DROP DATABASE IF EXISTS mydb;
/* 
此处填写多行注释内容
如：利用以下SQL查看当前服务器中的所有数据库
*/
SHOW DATABASES;


/* 2.2 数据表操作 */

# ① 创建mydb数据库
CREATE DATABASE mydb;
# ② 选择mydb数据库
USE mydb;
# ③ 创建goods数据表
CREATE TABLE goods (
 id INT COMMENT '编号',
 name VARCHAR(32) COMMENT '商品名',
 price INT COMMENT '价格',
 description VARCHAR(255) COMMENT '商品描述'
);

# 省略②，修改③创建goods数据表
CREATE TABLE mydb.goods (
 id INT COMMENT '编号',
 name VARCHAR(32) COMMENT '商品名',
 price INT COMMENT '价格',
 description VARCHAR(255) COMMENT '商品描述'
);

# 为mydb数据库再添加一张数据表new_goods
CREATE TABLE new_goods (
 id INT COMMENT '编号',
 name VARCHAR(32) COMMENT '商品名',
 price INT COMMENT '价格',
 description VARCHAR(255) COMMENT '商品描述'
);

# ① 查看所有数据表
SHOW TABLES;
# ② 查看名称中含有new的数据表
SHOW TABLES LIKE '%new%';


# 查看mydb数据库下含有new的数据表的详细信息
SHOW TABLE STATUS FROM mydb LIKE '%new%'\G

# 将new_goods表的名称修改为my_goods
RENAME TABLE new_goods TO my_goods;
# 查看所有数据表
SHOW TABLES;

# ① 将my_goods数据表的字符集改为utf8
ALTER TABLE my_goods CHARSET = utf8;
# ② 查看修改结果
SHOW CREATE TABLE my_goods \G

# ① 所有字段
DESC my_goods;
# ② name字段
DESC my_goods name;

# 查看my_goods数据表的创建语句
SHOW CREATE TABLE my_goods \G

# 查看my_goods数据表结构的详细信息
SHOW FULL COLUMNS FROM my_goods;

# ① 将my_goods数据表中名为description的字段修改为des
ALTER TABLE my_goods CHANGE description des VARCHAR(255);
# ② 查看字段名的修改情况
DESC my_goods;

# ① 修改my_goods数据表中des字段的数据类型，将VARCHAR (255)修改为CHAR(255)
ALTER TABLE my_goods MODIFY des CHAR(255);
# ② 查看字段类型的修改情况
DESC my_goods des;

# ① 将my_goods表中最后一个字段des移动到name字段后
ALTER TABLE my_goods MODIFY des VARCHAR(255) AFTER name;
# ② 查看字段位置的修改结果
DESC my_goods;

# ① 在my_goods数据表中字段name后新增一个num字段，表示商品的数量
ALTER TABLE my_goods ADD num INT AFTER name;
# ② 查看新增的字段
DESC my_goods;

# ① 删除my_goods数据表中num字段
ALTER TABLE my_goods DROP num;
# ② 看删除num字段后数据表中的字段
DESC my_goods;

# 删除数据表my_goods
DROP TABLE IF EXISTS my_goods;

/* 2.3 数据操作 */

# 为所有字段添加数据
INSERT INTO goods
VALUES (1, 'notebook', 4998, 'High cost performance');

# 添加含有中文的数据
INSERT  INTO goods
VALUES(2, '笔记本', 9998, '续航时间超过10个小时');

# 修改goods表中name和description字段的字符集
ALTER TABLE goods
MODIFY name VARCHAR(32) CHARACTER SET utf8,
MODIFY description VARCHAR(255) CHARACTER SET utf8;
	
# 为部分字段添加数据	
INSERT INTO goods (id, name) VALUES (3, 'Mobile phone');
INSERT INTO goods SET id = 3, name = 'Mobile phone';	
	
# 一次添加多行数据
INSERT  INTO goods VALUES
(1, 'notebook', 4998, 'High cost performance'),
(2, '笔记本', 9998, '续航时间超过10个小时'),
(3, 'Mobile phone', NULL, NULL);

# 查询表中全部数据	
SELECT * FROM goods;

# 查询表中部分字段
SELECT id, name FROM goods;

# 简单条件查询数据
SELECT * FROM goods WHERE id = 1;
	
# 将goods表中编号为2的商品价格由9998元修改为5899元。	
UPDATE goods SET price = 5899 WHERE id = 2;	
# 查看编号为2的商品价格修改情况
SELECT * FROM goods WHERE id = 2;	
	
	
# 删除goods表中编号等于3的商品数据
DELETE FROM goods WHERE id = 3;
# 查询goods表中记录的变化
SELECT * FROM goods;


/* 动手实践 */

# 选择数据库
CREATE DATABASE IF NOT EXISTS mydb;

# 创建电子杂志订阅表
CREATE TABLE subscribe (
 id INT COMMENT '编号',
 email VARCHAR(60) COMMENT '邮件订阅的邮箱地址',
 status INT COMMENT '是否确认，0未确认，1已确认',
 code VARCHAR(10) COMMENT '邮箱确认的验证码'
) DEFAULT CHARSET=utf8;

# 添加数据
INSERT INTO subscribe VALUES
(1, 'tom123@163.com', 1, 'TRBXPO'),
(2, 'lucy123@163.com', 1, 'LOICPE'),
(3, 'lily123@163.com', 0, 'JIXDAMI'),
(4, 'jimmy123@163.com', 0, 'QKOLPH'),
(5, 'joy123@163.com', 1, 'JSMWNL');
# 查询所有数据
SELECT * FROM subscribe;
	
# 查看已经通过邮箱确认的电子杂志订阅信息	
SELECT * FROM subscribe WHERE status = 1;

# 将编号等于4的确认状态设置为已确认。
UPDATE subscribe SET status = 1 WHERE id = 4;
# 查看编号等于4的记录修改后的信息
SELECT * FROM subscribe WHERE id = 4;

# 删除编号等于5的电子杂志订阅信息
DELETE FROM subscribe WHERE id = 5;
# 查看删除数据后表中的数据
SELECT * FROM subscribe;

MySQL数据库第3章：数据类型与约束

知识点：
MySQL数据库第3章：数据类型与约束
对应SQL命令：

/* 3.1 数据类型 */

# ① 演示整数类型的使用及注意事项
USE mydb;
CREATE TABLE my_int (
 int_1 INT,
 int_2 INT UNSIGNED,
 int_3 TINYINT,
 int_4 TINYINT UNSIGNED
);
# ② 插入成功测试
INSERT INTO my_int VALUES(1000, 1000, 100, 100);
# ③ 插入失败测试
INSERT INTO my_int VALUES(1000, -1000, 100, 100);
# ④ 查看表结构
DESC my_int;

# ① 为字段设置零填充（ZEROFILL）
CREATE TABLE my_int2 (
 int_1 INT(3) ZEROFILL,
 int_2 TINYINT(6) ZEROFILL
);
# ② 查看表结构
DESC my_int2;
# ③ 插入测试数据
INSERT INTO my_int2 VALUES(1234, 2);
# ④ 查看表中数据
SELECT * FROM my_int2;

# ① 创建表，选取FLOAT类型进行测试
CREATE TABLE my_float (f1 FLOAT, f2 FLOAT);
# ② 插入未超出精度的数字
INSERT INTO my_float VALUES(111111, 1.11111);
# ③ 插入超出精度的数字
INSERT INTO my_float VALUES(1111111, 1.111111);
# ④ 插入7位数，第7位四舍五入
INSERT INTO my_float VALUES(1111114, 1111115);
# ⑤ 插入8位数，第7位四舍五入，第8位忽略
INSERT INTO my_float VALUES(11111149, 11111159);
# ⑥ 查询结果
SELECT * FROM my_float;

# ① 创建表，选取DECIMAL类型进行测试
CREATE TABLE my_decimal (d1 DECIMAL(4,2), d2 DECIMAL(4,2));
# ② 插入的小数部分超出范围时，会四舍五入并出现警告
INSERT INTO my_decimal VALUES(1.234, 1.235);
SHOW WARNINGS;
# ③ 插入的小数部分四舍五入导致整数部分进位时，插入失败
INSERT INTO my_decimal VALUES(99.99, 99.999);
# ④ 查询结果
SELECT * FROM my_decimal;

# ① 获取字符“A”的ASCII码，结果为65
SELECT ASCII('A');
# ② 将十进制数转换为二进制，并计算长度，结果分别为1000001、7
SELECT BIN(65), LENGTH(BIN(65));
# ③ 创建表，然后插入数据
CREATE TABLE my_bit (b BIT(7));
INSERT INTO my_bit VALUES(65);
# ④ 查询数据，查询结果为“A”
SELECT * FROM my_bit WHERE b = 65;
# ⑤ 查询数据并转为二进制数字显示，查询结果为“1000001”
SELECT BIN(b) FROM my_bit;

# 设置y字段的数据类型为YEAR
CREATE TABLE my_year (y YEAR);
# 插入年份数据，2020年			
INSERT INTO my_year VALUES(2020);

# 设置d字段的数据类型为DATE
CREATE TABLE my_date (d DATE);
# 插入日期数据
INSERT INTO my_date VALUES('2020-01-21');
# 插入当前系统日期
INSERT INTO my_date VALUES(CURRENT_DATE);
# 插入当前系统日期
INSERT INTO my_date VALUES(NOW());

# 为TIMESTAMP字段手动设置DEFAULT属性时，该字段将不会自动设置ON UPDATE属性
CREATE TABLE my_timestamp (
 t1 TIMESTAMP,
 t2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 t3 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
# 查看表结构
DESC my_timestamp;

# ① 直接测试比较结果
SELECT 'a ' = 'A';
SELECT BINARY 'a' = 'A', 'a' = BINARY 'A';
SELECT BINARY 'A ' = 'A';
SELECT BINARY 'A' = 'A', 'A' = BINARY 'A';
# ② 在查询条件中进行二进制比较
CREATE TABLE my_char (c CHAR(2));
INSERT INTO my_char VALUES('A');
SELECT c FROM my_char WHERE c = 'a ';
SELECT c FROM my_char WHERE BINARY c = 'a';
SELECT c FROM my_char WHERE BINARY c = 'A ';
SELECT c FROM my_char WHERE BINARY c = 'A';

# ① 创建表时设置字段的校对集
CREATE TABLE my_char (
  c1 CHAR(2) CHARACTER SET latin1 COLLATE latin1_bin,
  c2 CHAR(2) CHARACTER SET gbk COLLATE gbk_bin,
  c3 CHAR(2) CHARACTER SET utf8 COLLATE utf8_bin
);
# ② 插入测试数据
INSERT INTO my_char VALUES('A', 'A', 'A');
# ③ 查询测试
SELECT c1 = 'a', c2 = 'a', c3 = 'a' FROM my_char;
SELECT c1 = 'A', c2 = 'A', c3 = 'A' FROM my_char;
SELECT c1 = 'A ',c2 = 'A ',c3 = 'A ' FROM my_char;

# ① 创建表
CREATE TABLE my_enum (gender ENUM('male', 'female'));
# ② 插入两条测试记录
INSERT INTO my_enum VALUES('male'), ('female');
# ③ 查询记录，查询结果为“female”
SELECT * FROM my_enum WHERE gender = 'female';
# ④ 插入枚举列表中没有的值测试
INSERT INTO my_enum VALUES('m');

# ① 创建表
CREATE TABLE my_set (hobby SET('book', 'game', 'code'));
# ② 插入3条测试记录
INSERT INTO my_set VALUES(''), ('book'), ('book,code');
# ③ 查询记录，查询结果为“book,code”
SELECT * FROM my_set WHERE hobby = 'book,code';

# ① 创建表，插入测试记录
CREATE TABLE my_binary (b1 BINARY(4), b2 VARBINARY(4));
INSERT INTO my_binary VALUES('abc', 'xyz');
# ② 查询记录，两次查询结果分别为“abc\0”和“xyz”（\0显示为空格）
SELECT b1 FROM my_binary WHERE b1 = 'abc\0';
SELECT b2 FROM my_binary WHERE b2 = 'xyz';
# ③ 查询记录，由于区分大小写，查询结果都为空
SELECT b1 FROM my_binary WHERE b1 = 'ABC\0';
SELECT b2 FROM my_binary WHERE b2 = 'XYZ';

# ① 创建表，插入测试记录
CREATE TABLE my_blob (b BLOB);
INSERT INTO my_blob VALUES('data');
# ② 查询记录，查询结果为“data”
SELECT b FROM my_blob WHERE b = 'data';
# ③ 查询记录，由于区分大小写，查询结果为空
SELECT b FROM my_blob WHERE b = 'Data';

# ① JSON数组
["abc", 10, null, true, false]
# ② JSON对象
{"k1": "value", "k2": 10}

# ① 创建表，插入测试记录
CREATE TABLE my_json (j1 JSON, j2 JSON);
INSERT INTO my_json VALUES
('{"k1": "value", "k2": 10}', '["run", "sing"]');
# ② 查询记录
SELECT * FROM my_json;


/* 3.2 表的约束 */

# 创建表，查看表结构
CREATE TABLE my_default (
 name VARCHAR(10),
 age INT UNSIGNED DEFAULT 18
);
DESC my_default;

# ① 在插入记录时省略name和age字段
INSERT INTO my_default VALUES();
Query OK, 1 row affected (0.00 sec)
# ② 在插入记录时省略age字段
INSERT INTO my_default (name) VALUES('a');
Query OK, 1 row affected (0.00 sec)
# ③ 在age字段中插入NULL值
INSERT INTO my_default VALUES('b', NULL);
Query OK, 1 row affected (0.00 sec)
# ④ 在age字段中使用默认值
INSERT INTO my_default VALUES('c', DEFAULT);
Query OK, 1 row affected (0.00 sec)
# ⑤ 查询结果
SELECT * FROM my_default;

# ① 删除默认约束
ALTER TABLE my_default MODIFY age INT UNSIGNED;
# ② 添加默认约束
ALTER TABLE my_default MODIFY age INT UNSIGNED DEFAULT 18;

# 创建表，查看表结构
CREATE TABLE my_not_null (
 n1 INT,
 n2 INT NOT NULL,
 n3 INT NOT NULL DEFAULT 18
);
DESC my_not_null;

# ① 省略n2字段，插入失败，提示n2没有默认值
INSERT INTO my_not_null VALUES();
# ② 将n2字段设为NULL，插入失败，提示n2字段不能为NULL
INSERT INTO my_not_null VALUES(NULL, NULL, NULL);
# ③ 将n3字段设为NULL，插入失败，提示n3字段不能为NULL
INSERT INTO my_not_null VALUES(NULL, 20, NULL);
# ④ 省略n1和n3字段，插入成功
INSERT INTO my_not_null (n2) VALUES(20);
# ⑤ 查询结果
SELECT * FROM my_not_null;

# 列级约束，查看表结构
CREATE TABLE my_unique_1 (
 id INT UNSIGNED UNIQUE,
 username VARCHAR(10) UNIQUE
);
DESC my_unique_1

# 表级约束，查看表结构
CREATE TABLE my_unique_2 (
 id INT UNSIGNED,
 username VARCHAR(10),
 UNIQUE(id),
 UNIQUE(username)
);
DESC my_unique_2

# ① 插入不重复记录，插入成功
INSERT INTO my_unique_1 (id) VALUES(1);
INSERT INTO my_unique_1 (id) VALUES(2);
# ② 插入重复记录，插入失败
INSERT INTO my_unique_1 (id) VALUES(1);
# ③ 查询插入的结果
SELECT * FROM my_unique_1;

# ① 创建测试表
CREATE TABLE my_unique_3 (id INT);
# ② 添加唯一约束
ALTER TABLE my_unique_3 ADD UNIQUE(id);
# ③ 查看添加结果
SHOW CREATE TABLE my_unique_3\G
# ④ 删除唯一约束
ALTER TABLE my_unique_3 DROP INDEX id;
# ⑤ 查看删除结果
SHOW CREATE TABLE my_unique_3\G

# ① 创建测试表，添加复合唯一键
CREATE TABLE my_unique_4 (
 id INT UNSIGNED, username VARCHAR(10),
 UNIQUE(id, username)
);
# ② 插入不重复记录，插入成功
INSERT INTO my_unique_4 VALUES(1, '2');
INSERT INTO my_unique_4 VALUES(1, '3');
# ③ 插入重复记录，插入失败
INSERT INTO my_unique_4 VALUES(1, '2');

# 创建表，查看表结构
CREATE TABLE my_primary (
 id INT UNSIGNED PRIMARY KEY,
 username VARCHAR(20)
);
DESC my_primary;

# ① 插入测试记录，插入成功
INSERT INTO my_primary VALUES(1, 'Tom');
# ② 为主键插入NULL值，插入失败
INSERT INTO my_primary VALUES(NULL, 'Jack');
# ③ 为主键插入重复值，插入失败
INSERT INTO my_primary VALUES(1, 'Alex');

# ① 删除主键约束
ALTER TABLE my_primary DROP PRIMARY KEY;
# ② 查看删除结果
DESC my_primary;
# ③ 删除id字段的非空约束（此步骤可选）
ALTER TABLE my_primary MODIFY id INT UNSIGNED;
# ④ 添加主键约束
ALTER TABLE my_primary ADD PRIMARY KEY (id);
# ⑤ 查看添加结果
DESC my_primary;


/* 3.3 自动增长 */

# 创建表，查看表结构
CREATE TABLE my_auto (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 username VARCHAR(20)
);
DESC my_auto;

# ① 插入时省略id字段，将会使用自动增长值
INSERT INTO my_auto (username) VALUES('a');
# ② 为id字段插入NULL，将会使用自动增长值
INSERT INTO my_auto VALUES(NULL, 'b');
# ③ 为id字段插入具体值6
INSERT INTO my_auto VALUES(6, 'c');
# ④ 为id字段插入0，使用自动增长值
INSERT INTO my_auto VALUES(0, 'd');

# 查看表中数据
SELECT * FROM my_auto;
# 查看表创建语句
SHOW CREATE TABLE my_auto\G

# ① 修改自动增长值
ALTER TABLE my_auto AUTO_INCREMENT = 10;
# ② 删除自动增长
ALTER TABLE my_auto MODIFY id INT UNSIGNED;
# ③ 重新为id添加自动增长
ALTER TABLE my_auto MODIFY id INT UNSIGNED AUTO_INCREMENT;

/* 3.4 字符集与校对集 */

# 查看与字符集相关的变量
SHOW VARIABLES LIKE 'character%';

# 修改客户端、连接层和查询结果的字符集
set character_set_client = gbk;
set character_set_connection = gbk;
set character_set_results = gbk;

# 创建数据库，指定字符集为utf8，使用默认校对集utf8_general_ci
CREATE DATABASE mydb_1 CHARACTER SET utf8;
# 创建数据库，指定字符集为utf8，校对集为utf8_bin
CREATE DATABASE mydb_2 CHARACTER SET utf8 COLLATE utf8_bin;

# 创建数据表，设置字符集utf8，校对集utf8_bin
CREATE TABLE my_charset (
  username VARCHAR(20)
) CHARACTER SET utf8 COLLATE utf8_bin;

# 指定字段字符集与校对集
CREATE TABLE my_charset (
  username VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_bin
);

/* 动手实践 */

# 创建用户表
CREATE TABLE mydb.user (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '用户id',
 username VARCHAR(20) UNIQUE NOT NULL COMMENT '用户名',
 mobile CHAR(11) NOT NULL COMMENT '手机号码',
 gender ENUM('男', '女', '保密') NOT NULL COMMENT '性别',
 reg_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
 level TINYINT UNSIGNED NOT NULL COMMENT '会员等级'
) DEFAULT CHARSET=utf8;

# 添加测试记录
INSERT INTO mydb.user VALUES
( NULL, '小明','12311111111', '男','2018-01-01 11:11:11', 1);
	
查看用户表中的记录
SELECT * FROM mydb.user;

MySQL数据库第4章：数据库设计

知识点：
MySQL数据库第4章：数据库设计
对应SQL命令：


/* 4.4 数据库设计——电子商务网站 */

# 创建并选择数据库
CREATE DATABASE shop;
USE shop;

# 创建商品分类表
CREATE TABLE sh_goods_category (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '分类id',
 parent_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级分类id',
 name VARCHAR(100) NOT NULL DEFAULT '' COMMENT '名称',
 sort INT NOT NULL DEFAULT 0 COMMENT '排序',
 is_show TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否显示',
 create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 update_time DATETIME DEFAULT NULL COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 添加测试数据
INSERT INTO sh_goods_category (id, parent_id, name) VALUES
(1, 0, '办公'), (2, 1, '耗材'), (3, 2, '文具'),
(4, 0, '电子产品'), (5, 4, '通讯'), (6, 5, '手机'),
(7, 4, '影音'), (8, 7, '音箱'), (9, 7, '耳机'),
(10, 4, '电脑'), (11, 10, '台式电脑'), (12, 10, '笔记本'),
(13, 0, '服装'), (14, 13, '女装'), (15, 14, '风衣'), (16, 14, '毛衣');

# 创建商品表
CREATE TABLE sh_goods (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '商品id',
 category_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '分类id',
 spu_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SPU id',
 sn VARCHAR(20) NOT NULL DEFAULT '' COMMENT '编号',
 name VARCHAR(120) NOT NULL DEFAULT '' COMMENT '名称',
 keyword VARCHAR(255) NOT NULL DEFAULT '' COMMENT '关键词',
 picture VARCHAR(255) NOT NULL DEFAULT '' COMMENT '图片',
 tips VARCHAR(255) NOT NULL DEFAULT '' COMMENT '提示',
 description VARCHAR(255) NOT NULL DEFAULT '' COMMENT '描述',
 content TEXT NOT NULL COMMENT '详情',
 price DECIMAL(10, 2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '价格',
 stock INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '库存',
 score DECIMAL(3, 2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '评分',
 is_on_sale TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否上架',
 is_del TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否删除',
 is_free_shipping TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否包邮',
 sell_count INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '销量计数',
 comment_count INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '评论计数',
 on_sale_time DATETIME DEFAULT NULL COMMENT '上架时间',
 create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 update_time DATETIME DEFAULT NULL COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 添加测试数据
INSERT INTO sh_goods (id, category_id, name, keyword, content, price, 
stock, score, comment_count) VALUES
(1, 3, '2B铅笔', '文具', '考试专用', 0.5, 500, 4.9, 40000),
(2, 3, '钢笔', '文具', '练字必不可少', 15, 300, 3.9, 500),
(3, 3, '碳素笔', '文具', '平时使用', 1, 500, 5, 98000),
(4, 12, '超薄笔记本', '电子产品', '轻小便携', 5999, 0, 2.5, 200),
(5, 6, '智能手机', '电子产品', '人人必备', 1999, 0, 5, 98000),
(6, 8, '桌面音箱', '电子产品', '扩音装备', 69, 750, 4.5, 1000),
(7, 9, '头戴耳机', '电子产品', '独享个人世界', 109, 0, 3.9, 500),
(8, 10, '办公电脑', '电子产品', '适合办公', 2000, 0, 4.8, 6000),
(9, 15, '收腰风衣', '服装', '春节潮流单品', 299, 0, 4.9, 40000),
(10, 16, '薄毛衣', '服装', '居家旅行必备', 48, 0, 4.8, 98000);

# 商品SPU表
CREATE TABLE sh_goods_spu (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'SPU id',
name VARCHAR(80) NOT NULL DEFAULT '' COMMENT 'SPU名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 商品规格表
CREATE TABLE sh_goods_spec (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '规格id',
name VARCHAR(80) NOT NULL DEFAULT '' COMMENT '规格名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 商品规格项表
CREATE TABLE sh_goods_spec_item (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '规格项id',
spec_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '规格id',
name VARCHAR(80) NOT NULL DEFAULT '' COMMENT '名称',
description VARCHAR(255) NOT NULL DEFAULT '' COMMENT '描述',
picture VARCHAR(255) NOT NULL DEFAULT '' COMMENT '可选图'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 商品规格组合表
CREATE TABLE sh_goods_spec_set (
goods_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SKU id',
spec_item_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '规格id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 添加测试数据
INSERT INTO sh_goods_spu (id, name) VALUES
(1, '新款智能手机');
INSERT INTO sh_goods_spec (id, name) VALUES
(1, '网络'), (2, '颜色'), (3, '内存');
INSERT INTO sh_goods_spec_item (id, spec_id, name) VALUES
(1, 1, '移动'), (2, 1, '电信'), (3, 2, '白色'), 
(4, 2, '黑色'), (5, 3, '4G'), (6, 3, '6G');
INSERT INTO sh_goods_spec_set (goods_id, spec_item_id) VALUES
(5, 2), (5, 3), (5, 5);

# 商品属性表
CREATE TABLE sh_goods_attr (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '属性id',
 parent_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级属性id',
 category_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品分类id',
 name VARCHAR(80) NOT NULL DEFAULT '' COMMENT '名称',
 sort INT NOT NULL DEFAULT 0 COMMENT '排序'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 商品属性值表
CREATE TABLE sh_goods_attr_value (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '属性值id',
 goods_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品id',
 attr_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '属性id',
 attr_value VARCHAR(80) NOT NULL DEFAULT '' COMMENT '属性值'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 商品筛选表
CREATE TABLE sh_goods_selector (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '筛选id',
 parent_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级筛选id',
 category_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品分类id',
 name VARCHAR(80) NOT NULL DEFAULT '' COMMENT '名称',
 sort INT NOT NULL DEFAULT 0 COMMENT '排序'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 商品筛选值表
CREATE TABLE sh_goods_selector_value (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '筛选值id',
 goods_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品id',
 selector_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '筛选id',
 selector_value VARCHAR(80) NOT NULL DEFAULT '' COMMENT '筛选值'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 添加测试数据
INSERT INTO sh_goods_attr VALUES
(1, 0, 6, '基本信息', 0), (2, 1, 6, '机身颜色', 0),
(3, 1, 6, '输入方式', 1), (4, 1, 6, '操作系统', 2),
(5, 0, 6, '屏幕', 1), (6, 5, 6, '屏幕尺寸', 0), (7, 5, 6, '屏幕材质', 1),
(8, 5, 6, '分辨率', 2), (9, 0, 6, '摄像头', 2),
(10, 9, 6, '前置摄像头', 0), (11, 9, 6, '后置摄像头', 1),
(12, 0, 6, '电池信息', 3), (13, 12, 6, '电池容量', 0),
(14, 12, 6, '是否可拆卸', 1);

INSERT INTO sh_goods_attr_value VALUES
(1, 5, 2, '黑色'), (2, 5, 3, '触摸屏'), (3, 5, 4, 'Android'),
(4, 5, 6, '5.5寸'), (5, 5, 7, 'IPS'), (6, 5, 8, '1920*1080'),
(7, 5, 10, '1600万'), (8, 5, 11, '800万'),
(9, 5, 13, '3500mAh'), (10, 5, 14, '否');

# 创建用户表
CREATE TABLE sh_user (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '用户id',
 name VARCHAR(100) NOT NULL UNIQUE DEFAULT '' COMMENT '用户名',
 password VARCHAR(255) NOT NULL DEFAULT '' COMMENT '密码',
 salt CHAR(32) NOT NULL DEFAULT '' COMMENT '密码盐',
 email VARCHAR(128) NOT NULL DEFAULT '' COMMENT '邮箱',
 mobile CHAR(11) NOT NULL DEFAULT '' COMMENT '手机号',
 level TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户级别',
 money DECIMAL(10, 2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '金额',
 gender TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '性别',
 qq VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'QQ',
 is_active TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否激活',
 reg_time DATETIME DEFAULT NULL COMMENT '注册时间',
 create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 update_time DATETIME DEFAULT NULL COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 查看MD5加盐算法
SELECT MD5(CONCAT(MD5('password'), 'salt'));

# 添加测试数据
INSERT INTO sh_user (id, name, password, salt, money, is_active) VALUES
(1, 'Alex', MD5(CONCAT(MD5('123'), 'salt1')), 'salt1', 1000, 1),
(2, 'Bill', MD5(CONCAT(MD5('123'), 'salt2')), 'salt2', 1000, 1);

# 查看sh_user表中的记录，观察密码加密结果
SELECT id, name, password, salt FROM sh_user;

# 创建商品评论表
CREATE TABLE sh_goods_comment (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '评论id',
 parent_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级评论id',
 user_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户id',
 goods_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品id',
 content TEXT NOT NULL COMMENT '评论内容',
 is_staff TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否为工作人员',
 is_show TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否显示',
 is_del TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否删除',
 create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 update_time DATETIME DEFAULT NULL COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 添加测试数据
INSERT INTO `sh_goods_comment` (id, user_id, goods_id, content,
is_show, create_time) VALUES
(1, 1, 8, '好', 0, '2017-11-08 00:00:00'),
(2, 2, 10, '不错', 1, '2017-12-03 00:00:00'),
(3, 3, 9, '满意', 1, '2017-12-30 00:00:00'),
(4, 4, 4, '携带方便', 1, '2018-01-19 00:00:00'),
(5, 4, 7, '中低音效果特别棒', 1, '2018-01-19 00:00:00'),
(6, 5, 8, '卡机', 1, '2018-01-22 00:00:00'),
(7, 6, 5, '黑夜拍照也清晰', 1, '2018-02-15 00:00:00'),
(8, 7, 9, '掉色、有线头', 0, '2018-03-03 00:00:00'),
(9, 4, 9, '还行', 1, '2018-04-05 00:00:00'),
(10, 8, 9, '特别彰显气质', 1,'2018-04-16 00:00:00');


/* 动手实践 */

# 创建购物车表
CREATE TABLE sh_user_shopcart (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '购物车id',
 user_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户id',
 goods_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品id',
 goods_price DECIMAL(10, 2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '单价',
 goods_num INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '购买件数',
 is_select TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否选中',
 create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 update_time DATETIME DEFAULT NULL COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 创建收货地址表
CREATE TABLE sh_user_address (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '地址id',
 user_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户id',
 is_default TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否默认',
 province VARCHAR(20) NOT NULL DEFAULT '' COMMENT '省',
 city VARCHAR(20) NOT NULL DEFAULT '' COMMENT '市',
 district VARCHAR(20) NOT NULL DEFAULT '' COMMENT '区',
 address VARCHAR(255) NOT NULL DEFAULT '' COMMENT '具体地址',
 zip VARCHAR(20) NOT NULL DEFAULT '' COMMENT '邮编',
 consignee VARCHAR(20) NOT NULL DEFAULT '' COMMENT '收件人',
 phone VARCHAR(20) NOT NULL DEFAULT '' COMMENT '联系电话',
 create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 update_time DATETIME DEFAULT NULL COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 订单表
CREATE TABLE sh_order (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '订单id',
 user_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户id',
 total_price DECIMAL(10, 2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单总价',
 order_price DECIMAL(10, 2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '应付金额',
 province VARCHAR(20) NOT NULL DEFAULT '' COMMENT '省',
 city VARCHAR(20) NOT NULL DEFAULT '' COMMENT '市',
 district VARCHAR(20) NOT NULL DEFAULT '' COMMENT '区',
 address VARCHAR(255) NOT NULL DEFAULT '' COMMENT '具体地址',
 zip VARCHAR(20) NOT NULL DEFAULT '' COMMENT '邮编',
 consignee VARCHAR(20) NOT NULL DEFAULT '' COMMENT '收件人',
 phone VARCHAR(20) NOT NULL DEFAULT '' COMMENT '联系电话',
 is_valid TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否有效',
 is_cancel TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否取消',
 is_pay TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否付款',
 status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '物流状态',
 is_del TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否删除',
 create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 update_time DATETIME DEFAULT NULL COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 订单商品表
CREATE TABLE sh_order_goods (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'id',
 order_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单id',
 goods_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品id',
 goods_name VARCHAR(120) NOT NULL DEFAULT '' COMMENT '商品名称',
 goods_num INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '购买数量',
 goods_price DECIMAL(10, 2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '单价',
 user_note VARCHAR(255) NOT NULL DEFAULT '' COMMENT '用户备注',
 staff_note VARCHAR(255) NOT NULL DEFAULT '' COMMENT '卖家备注'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 创建商品评分表
CREATE TABLE sh_goods_score (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '评分id',
 user_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户id',
 goods_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品id',
 goods_score TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品评分',
 service_score TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '服务评分',
 express_score TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '物流评分',
 is_invalid TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否无效',
 create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '评分时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

MySQL数据库第5章：单表操作

知识点：
MySQL数据库第5章：单表操作
对应SQL命令：

/* 5.1 数据操作 */

# 选择数据库
USE shop;

# 复制表结构
CREATE  TABLE  mydb.my_goods LIKE sh_goods;

# 查看my_goods表的结构
SHOW CREATE TABLE mydb.my_goods\G

# 复制已有的表数据
INSERT INTO mydb.my_goods SELECT * FROM sh_goods;

# 复制已有的表数据，主键冲突 
INSERT INTO mydb.my_goods SELECT * FROM sh_goods;

# 复制除主键外的其他数据
INSERT INTO mydb.my_goods (category_id, name, keyword, price, 
content) SELECT category_id, name, keyword, price, content
FROM sh_goods;

# 方式1：创建临时表
CREATE TEMPORARY TABLE mydb.tmp_table1 (id int);
# 方式2：创建临时表
CREATE TEMPORARY TABLE mydb.tmp_table2 SELECT id,name FROM shop.sh_goods;

# 主键冲突
INSERT INTO mydb.my_goods(id, name, content, keyword)
VALUES (20, '橡皮', '修正书写错误', '文具');

# 主键冲突更新，并查看数据
INSERT INTO mydb.my_goods (id, name, content, keyword)
VALUES (20, '橡皮', '修正书写错误', '文具')
ON DUPLICATE KEY UPDATE name = '橡皮', content = '修正书写错误',keyword = '文具';

SELECT name, content, keyword FROM mydb.my_goods WHERE id = 20;
	
# 主键冲突替换，并查看数据
REPLACE INTO mydb.my_goods (id, name, content, keyword)
VALUES (20, '橡皮', '修正书写错误', '文具');

SELECT name, content, keyword FROM mydb.my_goods WHERE id = 20;

# 清空数据
TRUNCATE TABLE mydb.my_goods;

# 准备数据，清空、插入、查看
INSERT INTO mydb.my_goods SELECT * FROM sh_goods;
TRUNCATE TABLE mydb.my_goods;
INSERT INTO mydb.my_goods (name, content, keyword)VALUES ('苹果', '一种很有营养的水果', '水果');
SELECT id, name, content, keyword FROM mydb.my_goods;

# 准备数据，删除、插入、查看
INSERT INTO mydb.my_goods SELECT * FROM sh_goods;
DELETE FROM mydb.my_goods;
INSERT INTO mydb.my_goods (name, content, keyword)VALUES ('苹果', '一种很有营养的水果', '水果');
SELECT id, name, content, keyword FROM mydb.my_goods;

# 不去重与去重查询数据
SELECT keyword FROM sh_goods;
SELECT DISTINCT keyword FROM sh_goods;	

/* 5.2 排序与限量 */

# 单字段排序
SELECT id, name, price FROM sh_goods ORDER BY price DESC;

# 多字段排序
SELECT category_id, id, name, price FROM sh_goods
ORDER BY category_id, price DESC;

# 限制记录数
SELECT id, name, price FROM sh_goods ORDER BY price DESC LIMIT 1;

# 获取指定区间的记录
SELECT id, name, price FROM sh_goods LIMIT 0, 5;

# 数据更新的排序与限量
UPDATE sh_goods SET stock = 500 ORDER BY price ASC LIMIT 2;
SELECT id, name, price, stock FROM sh_goods ORDER BY price;


/* 5.3 分组与聚合函数 */

# 分组统计
SELECT category_id, MAX(price) FROM sh_goods GROUP BY category_id;

# 分组排序
SELECT category_id, GROUP_CONCAT(id), GROUP_CONCAT(name)
FROM sh_goods GROUP BY category_id DESC;

# 多分组统计
SELECT score, COUNT(*), GROUP_CONCAT(name), comment_count
FROM sh_goods GROUP BY score DESC, comment_count;

# 回溯统计
SELECT category_id, COUNT(*)
FROM sh_goods GROUP BY category_id WITH ROLLUP;

# 多分组回溯统计
SELECT score, comment_count, COUNT(*)
FROM sh_goods
GROUP BY score, comment_count WITH ROLLUP;

# 统计筛选
SELECT score, comment_count, GROUP_CONCAT(id)
FROM sh_goods
GROUP BY score, comment_count
HAVING COUNT(*) = 2;

# 字段与表别名的使用
SELECT category_id cid, MAX(price) max_price FROM sh_goods
GROUP BY cid HAVING cid = 3 OR cid = 6;

SELECT g.category_id cid, MAX(g.price) max_price FROM sh_goods g
GROUP BY cid HAVING cid = 3 OR cid = 6;

# 聚合函数
SELECT MAX(price), MIN(price) FROM sh_goods;

# 聚合函数与分组
SELECT category_id, MAX(price), MIN(price)
FROM sh_goods GROUP BY category_id HAVING COUNT(*) > 2;

# JSON相关的聚合函数
SELECT category_id, JSON_ARRAYAGG(id), JSON_OBJECTAGG(id, name) 
FROM sh_goods GROUP BY category_id;


/* 5.4 运算符 */

# 无符号的加减乘法运算
SELECT id, id+1, id-1, id*2 FROM sh_goods LIMIT 5;

# 有符号的减法运算结果
SELECT id-3 FROM sh_goods LIMIT 5;
SELECT CAST(id AS SIGNED)-3 FROM sh_goods LIMIT 5;

# 含有精度的运算
SELECT name, price, stock, price*0.75, stock+850.00
FROM sh_goods WHERE score = 5;

# “/”运算
SHOW VARIABLES LIKE 'div_precision_increment';
SELECT name, stock, stock/5 FROM sh_goods WHERE stock > 200;

# NULL参与算术运算
SELECT NULL+1, 3-NULL, 7*NULL, 2/NULL, NULL/3;

# DIV与MOD运算符
SELECT 8/5, 8 DIV 5, 0.6/1.2, 0.6 DIV 1.2;
SELECT 8 MOD 5, -8 MOD 5, 8 MOD -5, -8 MOD -5;

# 获取大于等于1且小于10的任意一个随机整数
SELECT FLOOR(1+RAND()*(10-1));

# 获取相同的随机数
SELECT RAND(4);
SELECT RAND();
SELECT RAND(4);

# 随机获取某一商品分类下的所有商品id
SELECT category_id, GROUP_CONCAT(id) FROM sh_goods
GROUP BY category_id ORDER BY RAND() LIMIT 1;

# 数据类型自动转换
SELECT 5>='5', 3.0<>3;

# 比较结果为NULL
SELECT 0 = NULL, NULL<1, NULL<>2;

# “=”与“<=>”的区别
SELECT NULL=NULL, NULL=1, NULL <=> NULL, NULL<=>1;

# BETWEEN…AND…
SELECT id, name, price FROM sh_goods
WHERE price BETWEEN 2000 AND 6000;

SELECT id, name, price FROM sh_goods
WHERE price NOT BETWEEN 2000 AND 6000;

# IS NULL与IS NOT NULL
SELECT id, name, price, keyword FROM sh_goods
WHERE keyword IS NOT NULL
ORDER BY price DESC LIMIT 2;

# LIKE与NOT LIKE
SELECT id, name, price, content FROM sh_goods
WHERE name LIKE '%笔%';

# 正则匹配查询
SELECT id, name, content FROM sh_goods
WHERE content REGEXP '人|必备';

# 比较运算符函数
SELECT id, name, keyword, category_id FROM sh_goods
WHERE category_id IN(3, 15);

# 逻辑与
SELECT id, name, price FROM sh_goods
WHERE keyword = '电子产品' && score = 5;

SELECT id, name, price FROM sh_goods
WHERE (keyword, score) = ('电子产品', 5);

SELECT 1&&NULL, NULL&&1, 0&&NULL, NULL&&0;

# 逻辑或
SELECT id, name, price, score FROM sh_goods
WHERE score = 4.5 || price < 10;

SELECT 1||NULL, NULL||1, 0||NULL, NULL||0;

#逻辑非
SELECT NOT 10, NOT 0, NOT NULL, NOT 0 + !0, !0 + !0;

# 逻辑异或
SELECT 1 XOR 2, 0 XOR 0, 0 XOR 2, NULL XOR 2;

# 查看数据
SELECT id, name, stock FROM sh_goods WHERE score = 4.5;
# 利用赋值运算符更新数据
UPDATE sh_goods SET stock = 1000 WHERE score = 4.5;
UPDATE sh_goods SET stock := 1000 WHERE score = 4.5;

# ① 创建数据表，含有VARBINARY类型的字段
CREATE TABLE mydb.mybin (b1 VARBINARY(20), b2 VARBINARY(20));
# ② 插入数据
INSERT INTO mydb.mybin VALUES (2, 6), (3, 1), (4, 9);
# ③ 查看数据表中两个字段的按位与、按位或结果
SELECT b1&b2, b1|b2 FROM mydb.mybin;
# ④ 查看警告信息
SHOW WARNINGS\G
# ⑤ 解决问题
SELECT CAST(b1 AS UNSIGNED) & CAST(b2 AS UNSIGNED) AS one,
CAST(b1 AS UNSIGNED) | CAST(b2 AS UNSIGNED) AS two FROM mydb.mybin;

# 运算符优先级
SELECT 2+3*5, (2+3)*5;

/* 动手实践 */

# 查询商品id等于8且有效的评论内容
SELECT id, content FROM sh_goods_comment
WHERE goods_id = 8 && is_show = 1;

# 查询每个用户评论的商品数量
SELECT user_id, COUNT(goods_id) FROM sh_goods_comment
GROUP BY user_id;

# 查询最新发布的5条有效商品评论信息
SELECT id, content, user_id, goods_id
FROM sh_goods_comment
WHERE is_show = 1
ORDER BY create_time DESC
LIMIT 5;

# 查询评论过两种以上不同商品的用户id及对应的商品id
SELECT user_id, GROUP_CONCAT(goods_id)
FROM sh_goods_comment
GROUP BY user_id
HAVING COUNT(DISTINCT goods_id) >= 2;

# 结合sh_goods和sh_goods_comment表，查询没有任何评论信息的商品id和name
SELECT id,name FROM sh_goods WHERE id
NOT IN(SELECT DISTINCT goods_id FROM sh_goods_comment);

# 结合sh_goods和sh_goods_comment表，查询商品评分为5星的商品评论信息
SELECT id,content FROM sh_goods_comment
WHERE goods_id IN(SELECT id FROM sh_goods WHERE score = 5);

MySQL数据库第6章：多表操作

知识点：
MySQL数据库第6章：多表操作
对应SQL命令：

/* 6.1 多表查询 */
# 选择数据库
USE shop;

# 去重联合查询
SELECT id, name, price FROM sh_goods WHERE category_id = 9
UNION
SELECT id, name, keyword FROM sh_goods WHERE category_id = 6;

# 联合查询排序
(SELECT id, name, price FROM sh_goods WHERE category_id <> 3 ORDER by price DESC LIMIT 7)
UNION
(SELECT id, name, price FROM sh_goods WHERE category_id = 3 ORDER by price ASC LIMIT 3);

# 交叉连接
SELECT c.id cid, c.name cname, g.id gid, g.name gname
FROM sh_goods_category AS c
CROSS JOIN sh_goods AS g;
	
# 多表查询
SELECT c.id, c.name, g.id, g.name 
FROM sh_goods_category AS c, sh_goods AS g;

# 内连接
SELECT g.id gid, g.name gname, c.id cid, c.name cname
FROM sh_goods g JOIN sh_goods_category c
ON g.category_id = c.id;

# 自连接查询
SELECT DISTINCT g1.id, g1.name FROM sh_goods g1
JOIN sh_goods g2
ON g2.name = '钢笔' AND g2.category_id = g1.category_id;

# 左连接查询
SELECT g.id gid, g.name gname, c.id cid, c.name cname
FROM sh_goods g LEFT JOIN sh_goods_category c
ON g.category_id = c.id AND g.score = 5;

# 右连接查询
SELECT g.id gid, g.name gname, c.id cid, c.name cname
FROM sh_goods g RIGHT JOIN sh_goods_category c
ON c.id = g.category_id AND g.score = 5;

# USING关键字
SELECT DISTINCT g1.id, g1.name FROM sh_goods g1
JOIN sh_goods g2
USING(category_id) WHERE g2.name='钢笔';

/* 6.2 子查询 */

# 标量子查询
SELECT name FROM sh_goods_category
WHERE id = (SELECT category_id FROM sh_goods WHERE name='智能手机');

# 列子查询
SELECT name FROM sh_goods_category
WHERE id IN(SELECT DISTINCT category_id FROM sh_goods);

# 行子查询
SELECT id, name, price, score, content FROM sh_goods WHERE
(price, score) = (SELECT MAX(price), MIN(score) FROM sh_goods);

# 表子查询
SELECT a.id, a.name, a.price, a.category_id FROM sh_goods a,
(SELECT category_id, MAX(price) max_price FROM sh_goods
GROUP BY category_id) b
WHERE a.category_id = b.category_id AND a.price = b.max_price;

# 带EXISTS关键字的子查询
UPDATE sh_goods SET name='电饭煲', price=599,
category_id=(SELECT id FROM sh_goods_category WHERE name='厨具')
WHERE EXISTS(SELECT id FROM sh_goods_category WHERE name='厨具')
AND id=5;


# 带ANY关键字的子查询
SELECT name FROM sh_goods_category
WHERE id =
ANY(SELECT DISTINCT category_id FROM sh_goods WHERE price<500);

SELECT name FROM sh_goods_category
WHERE id <>
ANY(SELECT DISTINCT category_id FROM sh_goods WHERE price<500);

# 带ALL关键字的子查询
SELECT id, name, price, keyword FROM sh_goods
WHERE category_id=3 AND price <
ALL(SELECT DISTINCT price FROM sh_goods WHERE category_id = 8);


/* 6.3 外键约束 */


# ① 在mydb数据库下创建主表
CREATE TABLE mydb.department (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '部门编号',
 name VARCHAR(50) NOT NULL COMMENT '部门名称'
) DEFAULT CHARSET=utf8;
# ② 在mydb数据库下创建从表，添加外键约束
CREATE TABLE mydb.employees (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '员工编号',
 name VARCHAR(120) NOT NULL COMMENT '员工姓名',
 dept_id INT UNSIGNED NOT NULL COMMENT '部门编号',
 CONSTRAINT FK_ID FOREIGN KEY(dept_id) REFERENCES department(id)
 ON DELETE RESTRICT ON UPDATE CASCADE
) DEFAULT CHARSET=utf8;

# ALTER TABLE时添加外键约束
ALTER TABLE mydb.employees
ADD CONSTRAINT FK_ID FOREIGN KEY(dept_id) REFERENCES department(id)
ON DELETE RESTRICT ON UPDATE CASCADE;

# 查看外键约束
DESC mydb.employees dept_id;
SHOW CREATE TABLE mydb.employees\G

# 关联表添加数据
INSERT INTO mydb.employees(name, dept_id) VALUES ('Tom', 3);

INSERT INTO mydb.department(id, name) VALUES(3, '研发部');
INSERT INTO mydb.employees(name, dept_id) VALUES('Tom', 3);

# 关联表更新数据
UPDATE mydb.department SET id = 1 WHERE name = '研发部';
SELECT name, dept_id FROM mydb.employees;

# 关联表删除数据
DELETE FROM mydb.department WHERE id = 1;

DELETE FROM mydb.employees WHERE dept_id = 1;
DELETE FROM mydb.department WHERE id = 1;

# 删除外键约束
ALTER TABLE mydb.employees DROP FOREIGN KEY FK_ID;

# 查看外键约束
DESC mydb.employees dept_id;
SHOW CREATE TABLE mydb.employees \G

# 删除索引
ALTER TABLE mydb.employees DROP KEY FK_ID;

/* 动手实践 */

# 查询id为6的分类所具有的属性信息，将属性按照层级并排显示
SELECT a.sort sort1, a.name name1, b.sort sort2, b.name name2
FROM sh_goods_attr a
JOIN sh_goods_attr b ON a.id = b.parent_id
WHERE a.category_id = 6
ORDER BY a.sort ASC, b.sort ASC;

# 查询id为5的商品的所有属性信息，将属性名称和属性值并排显示
SELECT b.name, a.attr_value FROM sh_goods_attr_value a
JOIN sh_goods_attr b ON a.attr_id = b.id
WHERE a.goods_id = 5;
	
SELECT c.sort sort1, c.name name1, b.sort sort2, b.name name2,
a.attr_value FROM sh_goods_attr_value a
JOIN sh_goods_attr b ON a.attr_id = b.id
JOIN sh_goods_attr c ON b.parent_id = c.id
WHERE a.goods_id = 5
ORDER BY c.sort ASC, b.sort ASC;

	
# 查询id为1的属性的所有子属性值
SELECT attr_value FROM sh_goods_attr_value WHERE attr_id IN
(SELECT id FROM sh_goods_attr WHERE parent_id = 1);

# 查询拥有属性值个数大于1的商品id和名称
SELECT id, name FROM sh_goods WHERE id IN
(SELECT goods_id FROM sh_goods_attr_value GROUP BY goods_id
 HAVING COUNT(id) > 1);

MySQL数据库第7章：用户与权限

知识点：
MySQL数据库第7章：用户与权限
对应SQL命令：

/* 7.1 用户与权限概述 */
# 查看用户表字段
DESC mysql.user

# 账号字段
SELECT host, user FROM mysql.user;

# 身份验证字段
SELECT plugin, authentication_string FROM mysql.user
WHERE user='root';

# 查看MySQL是否支持SSL加密连接
SHOW VARIABLES LIKE 'have_openssl';


/* 7.2 用户管理 */

# 创建最简单的用户，查看MySQL的所有账号
CREATE USER 'test1';

SELECT host, user FROM mysql.user;

# ① 创建含有密码的用户
CREATE USER 'test2'@'localhost' IDENTIFIED BY '123456';
# ② 查看密码相关字段
SELECT plugin, authentication_string FROM mysql.user
WHERE user='test2';
# ③ 设置密码验证插件
CREATE USER 'test2'@'localhost'
IDENTIFIED WITH 'mysql_native_password' BY '123456';

# 同时创建多个用户
CREATE USER
'test3'@'localhost' IDENTIFIED BY '333333',
'test4'@'localhost' IDENTIFIED BY '444444';

# 设置用户可操作资源范围
CREATE USER
'test5'@'localhost' IDENTIFIED BY '555555'
WITH MAX_UPDATES_PER_HOUR 10;

SELECT max_updates FROM user WHERE user='test5';

# 设置有密码期限的用户
CREATE USER 'test6'@'localhost' IDENTIFIED BY '666666'
PASSWORD EXPIRE INTERVAL 180 DAY;

# 设置用户是否锁定
CREATE USER 'test7'@'localhost' IDENTIFIED BY '777777'
PASSWORD EXPIRE ACCOUNT LOCK;

SELECT account_locked FROM mysql.user WHERE user='test7';

# 为指定用户设置密码
ALTER USER 'test1'@'%' IDENTIFIED BY '123456';

# 为登录用户设置密码
ALTER USER USER() IDENTIFIED BY '000000';

# 当前通过MySQL服务器验证的用户与主机名
SELECT CURRENT_USER();

# 修改验证插件
ALTER USER test1
IDENTIFIED WITH sha256_password BY '111111'
PASSWORD EXPIRE;
SELECT authentication_string FROM mysql.user
WHERE user='test1' AND plugin='sha256_password';


# 解锁用户
ALTER USER 'test7'@'localhost' ACCOUNT UNLOCK;

# 同时修改多个用户的资源限定
ALTER USER
'test1' IDENTIFIED WITH mysql_native_password,
'test2'@'localhost' IDENTIFIED BY '222222'
WITH max_user_connections 2;

# 为用户重命名 
RENAME USER 'test6'@'localhost' TO 'xiaoming'@'localhost';

/* 7.3 权限管理 */

# 查看一下root和test1用户被授权
SHOW GRANTS FOR 'root'@'localhost';
SHOW GRANTS FOR 'test1'@'%';

# ① 为用户授权
GRANT SELECT,INSERT (name, price)
ON shop.sh_goods TO 'test1'@'%';
# ② 查看授权表
SELECT db,table_name,table_priv,column_priv 
FROM mysql.tables_priv WHERE user = 'test1';

SELECT db,table_name,column_name,column_priv
FROM mysql.columns_priv WHERE user='test1';

# 创建用户
GRANT SELECT ON *.* TO 'test8'@'';

# ① 清空默认SQL模式
SET sql_mode = '';
# ② 授予权限时，创建不存在的用户
GRANT SELECT ON *.* TO 'test8'@'';
# ③ 查看警告信息
SHOW WARNINGS\G
# ④ 将SQL模式修改回默认值
SET sql_mode = @@global.sql_mode;

# 回收权限并测试
REVOKE INSERT (name, price)
ON shop.sh_goods FROM 'test1'@'%';

INSERT INTO shop.sh_goods(name, price) VALUES('test', 23);

# 刷新权限
FLUSH PRIVILEGES;

/* 动手实践 */

# 创建用户
CREATE USER IF NOT EXISTS 'shop'@'127.0.0.%'
IDENTIFIED BY '123456';

# 将密码设置为过期
ALTER USER 'shop'@'127.0.0.%' PASSWORD EXPIRE;


# 修改用户密码
SHOW DATABASES;

ALTER USER 'shop'@'127.0.0.%' IDENTIFIED BY '2c5-q8h';

# 为用户设置权限
GRANT SELECT ON shop.sh_goods TO 'shop'@'127.0.0.%';

DROP TABLE shop.sh_goods;

# 回收用户权限
REVOKE SELECT ON shop.sh_goods FROM 'shop'@'127.0.0.%';

DROP USER IF EXISTS 'shop'@'localhost';

SELECT * FROM shop.sh_goods;