2020.3.25 MySQL 数据的操作，查询数据，连接查询，子查询

数据的操作DML

重命名数据表

-- 表的格式：
#CREATE TABLE [IF NOT EXISTS] tbl_name(
#字段名称 字段类型 [UNSIGNED|ZEROFILL] [NOT NULL] [DEFAULT 默认值][[PRIMARY] KEY| UNIQUE [KEY]] [AUTO_INCREMENT]
#)ENGINE=INNODB CHARSET=UTF8 AUTO_INCREMENT=100;



-- 创建用户表
CREATE TABLE IF NOT EXISTS user10(
id SMALLINT UNSIGNED KEY AUTO_INCREMENT,  #一般都是固定的
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT '1641924500@qq.com',
age TINYINT UNSIGNED DEFAULT 18,
sex ENUM('男','女','保密') DEFAULT '保密',
addr VARCHAR(200) NOT NULL DEFAULT '北京',
salary FLOAT(6,2),
pubTime INT UNSIGNED,
face CHAR(100) NOT NULL DEFAULT 'default.jpg'   #头像的意思，一般都是传路径，所以100就够了
);


-- 修改表名，将user10重命名成user11
ALTER TABLE user10 RENAME TO user11;
ALTER TABLE user11 RENAME AS user10;
ALTER TABLE user11 RENAME user11;  #TO可以省略
RENAME TABLE user11 TO user10;  #TO不可以省略

添加和删除字段

-- 测试添加card字段CHAR(18)，以user10为例
ALTER TABLE user10 ADD card CHAR(18);  #默认添加到末尾
ALTER TABLE user10 ADD test1 VARCHAR(100) NOT NULL UNIQUE;
ALTER TABLE user10 ADD test2 VARCHAR(20) NOT NULL FIRST;  #加到表的第一个位置上
ALTER TABLE user10 ADD test3 INT NOT NULL DEFAULT 100 AFTER username;  #添加到username字段之后，用AFTER


-- 选中一次表，完成多个操作
ALTER TABLE user10
ADD test4 INT NOT NULL DEFAULT 123 AFTER password,
ADD test5 FLOAT(6,2) FIRST,
ADD test6 SET('A','B','C');


-- 删除指定字段
-- 例如删除表名为user10的test6字段
ALTER TABLE user10 DROP test6;
-- 一次选中表，删除多个字段，例如一次删除test2,test3,test4,test5
ALTER TABLE user10
DROP test2,
DROP test3,
DROP test4,
DROP test5;


-- 添加test字段删除addr字段
ALTER TABLE user10
ADD test INT UNSIGNED NOT NULL DEFAULT 10 AFTER sex,
DROP addr;

修改字段

-- 修改字段MODIFY
-- 例如将email VARCHAR(50)修改成VARCHAR(200)
ALTER TABLE user10 MODIFY email VARCHAR(200);  #但是没写完整性约束条件，所以Null的位置从NO变成了YES
#如果想保留完整性约束条件，进行如下操作:就是都写上
ALTER TABLE user10 MODIFY email VARCHAR(50) NOT NULL DEFAULT '1641924500@qq.com';

-- 将card字段移动到test之后
ALTER TABLE user10 MODIFY card CHAR(18) AFTER test;
-- 将test字段修改为CHAR(32) NOT NULL DEFAULT '123' 并且移动到第一个位置上
ALTER TABLE user10 MODIFY test CHAR(32) NOT NULL DEFAULT '123' FIRST;


-- 修改字段名称 CHANGE
-- 将test字段改为test1
ALTER TABLE user10 CHANGE test test1 CHAR(32) NOT NULL DEFAULT '123';

ALTER TABLE user10 CHANGE test1 test VARCHAR(200) NOT NULL AFTER username;
ALTER TABLE user10 CHANGE test test INT;

添加和删除默认值

-- 添加默认值SET DEFAULT
CREATE TABLE IF NOT EXISTS user11(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
age TINYINT UNSIGNED
);
ALTER TABLE user11 ALTER age SET DEFAULT 18;
ALTER TABLE user11 ADD email VARCHAR(50);
ALTER TABLE user11 ALTER email SET DEFAULT '1641924500@qq.com';


-- 删除默认值DROP DEFAULT
ALTER TABLE user11 ALTER age DROP DEFAULT;
ALTER TABLE user11 ALTER email DROP DEFAULT;

添加删除主键

-- 添加主键
CREATE TABLE IF NOT EXISTS test13(
id INT
);
ALTER TABLE test13 ADD PRIMARY KEY(id);

CREATE TABLE IF NOT EXISTS test14(
id INT,
card CHAR(18),
username VARCHAR(20) NOT NULL
);
ALTER TABLE test14 ADD PRIMARY KEY(id,card); #添加复合主键

-- 删除主键
-- 例如删除test13表的主键
ALTER TABLE test13 DROP PRIMARY KEY;
ALTER TABLE test14 DROP PRIMARY KEY;  #复合主键也被删除了

ALTER TABLE test13 ADD CONSTRAINT symbol PRIMARY KEY index_type(id);  #完整形式，添加主键

-- 可能遇到的问题：
CREATE TABLE IF NOT EXISTS test15(
id INT UNSIGNED KEY AUTO_INCREMENT
);
# ALTER TABLE test15 DROP PRIMARY KEY;  #报错！因为一个表只能有一个自增长，有自增长的必须是主键，故不能删除，要先把自增长改掉，如下：
ALTER TABLE test15 MODIFY id INT UNSIGNED;  #这样就不是自增长了，就可以删除主键了
ALTER TABLE test15 DROP PRIMARY KEY;

添加删除唯一索引

-- 添加唯一索引
CREATE TABLE IF NOT EXISTS user12(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
card CHAR(18) NOT NULL,
test VARCHAR(20) NOT NULL,
test1 VARCHAR(32) NOT NULL
);
ALTER TABLE user12 ADD UNIQUE(username);  #将username设为唯一性索引
ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE KEY uni_card(card);  #完整形式，注意顺序
ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE INDEX mulUni_test_test1(test,test1);  #复合索引


-- 删除唯一索引
ALTER TABLE user12 DROP INDEX username;
ALTER TABLE user12 DROP KEY uni_card;  #另一种写法
ALTER TABLE user12 DROP KEY mulUni_test_test1;  #删除复合索引


-- 修改表的存储引擎为MyISAM
ALTER TABLE user12 ENGINE=MyISAM;
ALTER TABLE user12 ENGINE=INNODB;


-- 设置自增长的值，要保证表中已经有字段是自增长的
ALTER TABLE user12 AUTO_INCREMENT=100;

删除数据表

-- 删除数据表
DROP TABLE user12;
DROP TABLE IF EXISTS user12; #不会报错，但是会出现WARNINGS
DROP TABLE IF EXISTS user10,user11,user9;  #删除三张表
DROP TABLE IF EXISTS user7，user123;  #如果删除一个不存在的，会出现warning，但是user7也会被删除，对user7的删除没有影响

插入记录

-- 测试插入记录INSERT
CREATE TABLE IF NOT EXISTS user(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT '1641924500@qq.com',
age TINYINT UNSIGNED DEFAULT 18
);
INSERT INTO user VALUES(1,'KING','KING','KING@QQ.com',20);  #INTO可以省略
INSERT user VALUE(2,'queen','queen','queen@qq.com',30);  #VALUE或VALUES均可

-- 列出指定字段
INSERT user(username,password) VALUES('A','AAA');
INSERT user(password,username) VALUES('BBB','B');  #顺序要对应！别的字段没传值就是默认值
INSERT user(username,id,password,email,age) VALUES('C',55,'CCC','CCC@QQ.COM',DEFAULT);


-- 同时插入多条记录
INSERT user VALUES(6,'D','DDD','DDD@QQ.COM',35),
(8,'E','EEE','EEE@QQ.COM',9),
(18,'F','FFF','FFF@QQ.COM',32);


-- 通过SET形式插入记录
INSERT user SET id=98,username='test',password='1234556',email='123@qq.com',age=18;
INSERT user SET username='maizi',password='maizixueyuan';


-- 将查询结果插入表中，将其他表中数据插入表中
CREATE TABLE IF NOT EXISTS testUser(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE
);
INSERT testUser SELECT id,username FROM user;   #user表中的id和username的数据插入到表testUser中的id和username中
#要保证字段一致，数量一致，否则报错
INSERT testUser(username) SELECT usesrname FROM user; #只把表user中的username数据插入到表testUser中的username中

更新和删除记录

-- 更新数据
-- 注：如果不加WHERE条件，表中所有数据都会更新
-- 将用户表中所有的用户年龄更新为15
UPDATE user SET age=5;
UPDATE user SET age=20,email='123@qq.com';


-- 将第一条记录的password,email,age改变，利用主键
UPDATE user SET password='hj123',email='hj@qq.com',age=99
WHERE id=1;

UPDATE user SET age=age-5 WHERE id>=3;  #加条件
UPDATE user SET age=DEFAULT WHERE username='A';



-- 删除数据  #一定要谨慎！！！！
-- 例如删除testUser表中的数据
DELETE FROM testUser；

-- 删除user表中id为1的用户
DELETE FROM testUser WHERE id=1;
-- 但是没删掉AUTO_INCREMENT的值，需要重置



-- 彻底清空数据表：删除记录并重置自增长的值
TRUNCATE TABLE user；#TABLE可以不加，但不能带WHERE条件

查询数据DQL

查询表达式

-- 查询数据操作DQL

-- 查询记录
SELECT * FROM user;
SELECT user.* FROM user;  #*代表所有列，*所在的位置应该写你要查询的字段

-- 查询某些字段，显示时按你查询的顺序显示，多个字段之间以逗号,分隔
SELECT id,username FROM user;

-- 查询表来自于哪个数据库:形式：库名.表名，即db_name.tbl_name
SELECT id,username FROM maizi1.user; 

-- 字段来自于哪张表
SELECT user.id,user.username FROM maizi1.user;   #先是表名.字段名，后来是库名.表名

-- 给表名起别名
SELECT id,username FROM user AS u; #给表名user起个别名u，AS可以省略，最好不要省略
SELECT u.id,u.username,u.email FROM user AS u;

-- 给字段起别名
SELECT id AS '编号',username AS '用户名',email AS '邮箱' FROM user;

-- 表名和字段名都起了别名
SELECT u.id AS id1,u.username AS n,u.email AS e FROM user AS u;

带条件的查询

-- WHERE条件
-- 查询编号为1的用户
SELECT id,username,email FROM user WHERE id=1;
SELECT id,username,email FROM user WHERE username='hj'; #查不到就为空empty
SELECT * FROM user WHERE id>=5;
-- 查询编号不为1的用户
SELECT * FROM user WHERE id!=1;
SELECT * FROM user WHERE id<>1;

-- 添加age1字段
ALTER TABLE user ADD age1 TINYINT UNSIGNED DEFAULT 18;
INSERT user(username,password,email,age,age1)
VALUES('test1','test','12@qq.com',15,NULL);

-- 查询表中记录age1值为NULL
SELECT * FROM user WHERE age1<=>NULL;  #不能用=
SELECT * FROM user WHERE age1<=>18; 

-- IS NULL 或者IS NOT NULL
SELECT * FROM user WHERE age IS NOT NULL;
SELECT * FROM user WHERE age IS NULL;

范围查询

-- 筛选某一范围 BETWEEN AND
-- 查询编号在3-10之间的用户
SELECT * FROM user WHERE id BETWEEN 3 AND 10;
SELECT * FROM user WHERE id NOT BETWEEN 3 AND 10;

-- 筛选指定集合 IN 或NOT IN
-- 查询编号为1，3，5，9，11，13，100的用户
SELECT * FROM user WHERE id IN(1,3,5,7,9,11,13,100);  #没有的话就查不出来，有哪个编号就能查出来哪个编号

-- 查询age为18和20的用户
SELECT * FROM user WHERE age IN(18,20);

-- 查询字符串
-- 查询用户名为queen和A的用户
SELECT * FROM user WHERE username IN('queen','A');  #大小写均可，例如queen写成QUeen查询结果是一样的
SELECT * FROM user WHERE username NOT IN('queen','A');

模糊查询

-- 模糊查询LIKE 、NOT LIKE
-- %:代表0个1个或者多个任意字符
-- _:代表1个任意字符

-- 查询含st的用户
SELECT * FROM user WHERE username LIKE '%st%';

-- 查询以te开头的用户
SELECT * FROM user WHERE username LIKE 'te%';
SELECT * FROM user WHERE username LIKE '%';  #即所有用户

-- 查询用户名为4位的用户
SELECT * FROM user WHERE username LIKE '____';  #4个下划线

-- 用户名_i%
SELECT * FROM user WHERE username LIKE '_I%';  #I前面有一位的，后面无所谓
SELECT * FROM user WHERE username NOT LIKE '_I%';  #取反

分组查询

-- 对记录按某些字段进行分组
-- GROUP BY

-- 按照用户的age进行分组
SELECT * FROM user GROUP BY age;  #同一个age的只会显示第一个记录

-- 动态添加字段
-- 向用户表中添加性别字段
ALTER TABLE user ADD sex ENUM('男','女','保密');
UPDATE user SET sex='男' WHERE id IN(1,3,6,18,98,100); 
UPDATE user SET sex='女' WHERE id IN(2,4,8);
UPDATE user SET sex='保密' WHERE id IN(55,99);

-- 按照性别分组
SELECT * FROM user GROUP BY sex;

-- 按照字段位置分组
SELECT * FROM user GROUP BY 6;  #按第六个字段分类

-- 按照多个字段分组
SELECT * FROM user GROUP BY sex,age;  #先按sex分，再按age分

-- 查询编号>=5的用户，并且按照sex分组
-- 规则：先写WHERE条件，再进行分组
SELECT * FROM user WHERE id>=5 GROUP BY sex;

分组查询配合聚合函数

-- 查询id,sex,用户名详情，按照sex分组
SELECT id,sex,GROUP_CONCAT(username) FROM user GROUP BY sex;

-- 查询email,sex详情，用户名详情，按照email来分
SELECT email,GROUP_CONCAT(username),GROUP_CONCAT(sex) FROM user GROUP BY email;




UPDATE user SET age=11 WHERE id=1;
UPDATE user SET age=23 WHERE id=2;
UPDATE user SET age=24 WHERE id=3;
UPDATE user SET age=45 WHERE id=4;
UPDATE user SET age=90 WHERE id=6;
UPDATE user SET age=65 WHERE id=8;
UPDATE user SET age=78 WHERE id=18;
UPDATE user SET age=35 WHERE id=55;
UPDATE user SET age=64 WHERE id=98;
UPDATE user SET age=22 WHERE id=99;

-- 查询编号，sex，用户名详情以及组中总人数，按照sex分组
SELECT id,sex,GROUP_CONCAT(username) AS users, COUNT(*) AS totalUsers FROM user GROUP BY sex;
#即查询的是男、女、保密都有哪个用户，每个性别的总人数

-- 统计表中所有记录
SELECT COUNT(*) AS totalUsers FROM user;  #一共多少条记录
SELECT COUNT(id) AS totalUsers FROM user;  #统计id字段有多少个值，即表中有多少记录，一样的

-- COUNT(字段)不统计NULL值
SELECT COUNT(age1) AS totalUsers FROM user;  #NULL不算数

-- 查询编号，性别，用户名详情，组中总人数，组中最大年龄，最小年龄，平均年龄，年龄总和,并按照性别来分组
SELECT id,sex,GROUP_CONCAT(username),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM user
GROUP BY sex WITH ROLLUP;
#加WITH ROLLUP末尾多一行记录，id为一共多少个用户，totalUsers为以上的总和，max_age为组的最大年龄，min_age为组的最小年龄，以此类型
#即对组取命令

HAVING语句对分组结果进行二次筛选

-- 对分组之后的结果进行二次筛选
-- HAVING子句
-- 只能使用到分组之后

-- 查询性别，用户名详情，组中总人数，最大年龄，年龄总和,按照性别分组
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM user
GROUP BY sex    #要对分组结果进行二次筛选，则此处不能加分号;,否则报错
-- 查询组中人数大于2的
HAVING COUNT(*)>2;

-- 查询组中人数大于2并且最大年龄大于66的
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM user
GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>66;  #用AND连接

-- 查询编号大于等于2的用户
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM user
WHERE id>=2  #在此加条件
GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>66;

ORDER BY对查询结果排序

-- 测试ORDER BY
-- 按照id降序排列DESC 默认的是ASC
SELECT * FROM user ORDER BY id;  #升序，ASC可省略
SELECT * FROM user ORDER BY id ASC;
SELECT * FROM user ORDER BY id DESC;  #降序

-- 按照年龄升序排列
SELECT * FROM user ORDER BY age ASC;
SELECT * FROM user ORDER BY 1 DESC;  #也可以使用字段的位置，例如此位置为1的字段为id
UPDATE user SET age=12 WHERE id=6;

-- 按照年龄升序，id降序排列
SELECT * FROM user ORDER BY age ASC, id DESC;  #age相同时，则按照id降序排列
SELECT id,age,username,GROUP_CONCAT(username),COUNT(*) AS totalUsers,SUM(age) AS sum_age
FROM user
WHERE id>=2
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC,id ASC;

-- 实现随机提取记录
SELECT * FROM user ORDER BY RAND();

通过LIMIT限制显示条数

-- LIMIT 限制查询结果显示条数
-- 查询表中前三条记录
SELECT * FROM user LIMIT 3;  #显示前三条，跟id没关系
SELECT * FROM user ORDER BY id DESC LIMIT 5;  #对降序排列后的结果进行查询前5条
-- 查询表中第一条记录
SELECT * FROM user LIMIT 1;
SELECT * FROM user LIMIT 0,1;  #0代表偏移量，第一条偏移量为0，后面的1表示从你选定的偏移量开始，要显示几条记录
SELECT * FROM user LIMIT 1,1; #从第2条开始，查询前1条
SELECT * FROM user LIMIT 0,5;  #从第1条开始，查询前5条
SELECT * FROM user LIMIT 5,5;  #相当于显示下5条，即下一页
SELECT * FROM user LIMIT 1,5;   #从第2条开始，查询前5条

通过ORDER BY和LIMIT来更新删除

-- 针对单表查询的完整形式
-- SELECT语句完整形式
SELECT id,sex,age,GROUP_CONCAT(username),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM user
WHERE id>=1
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC
LIMIT 0,2;

MySQL连接查询

多表查询

内连接

-- 更新用户名为4位的用户，让其已有年龄减3
UPDATE user SET age=age-3 WHERE username LIKE '____';  #四个下划线，用到了模糊查询

-- 更新前3条记录，让已有年龄加10
UPDATE user SET age=age+10 LIMIT 3;
# 报错 UPDATE user SET age=age+10 LIMIT 0,3;  #更新时limit参数只能有1个

-- 按照id降序排列，更新前3条
UPDATE user SET age=age+10 ORDER BY id DESC LIMIT 3;  #也只能limit一个参数

-- 删除用户性别为男的用户，按照年龄降序排列，删除前1条数据
DELETE FROM user WHERE sex='男' ORDER BY age DESC LIMIT 1;




-- 连接查询：是将两个或两个以上的表按照某个条件连接起来，从中选取需要的数据。
-- 连接查询是同时查询两个或两个以上的表时使用的。
-- 当不同的表中存在相同意义的字段时，可以通过该字段连接这几个表。

CREATE TABLE IF NOT EXISTS `provinces`(
id SMALLINT,
proName VARCHAR(200)
);
INSERT provinces VALUES(1,'北京');
INSERT provinces VALUES(2,'上海');
INSERT provinces VALUES(3,'深圳');
INSERT provinces VALUES(4,'杭州');
INSERT provinces VALUES(5,'沈阳');

UPDATE user SET proID=1 WHERE id=1;
UPDATE user SET proID=3 WHERE id=2;
UPDATE user SET proID=2 WHERE id=3;
UPDATE user SET proID=4 WHERE id=4;
UPDATE user SET proID=5 WHERE id=6;
UPDATE user SET proID=1 WHERE id=8;
UPDATE user SET proID=3 WHERE id=55;
UPDATE user SET proID=4 WHERE id=98;
UPDATE user SET proID=2 WHERE id=99;
UPDATE user SET proID=5 WHERE id=100;

-- 查询user表中id，username
-- 查询另一张表provinces中的proName
SELECT user.id,username,proName FROM user,provinces;

-- user的proID对应省份表provinces中的id
SELECT user.id,username,proName FROM user,provinces
WHERE user.proID=provinces.id;




-- 内连接 INNER JOIN或CROSS JOIN和ON

-- 查询user表中的id,username,email,sex
-- 查询provinces表中的proName
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM user AS u
INNER JOIN provinces AS p    #和哪个表结合，并起别名
ON u.proID=p.id;  #连接条件

-- 另一种写法 CROSS JOIN
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
CROSS JOIN user AS u
ON u.proID=p.id;

-- 另一种写法  直接写JOIN
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
JOIN user AS u
ON u.proID=p.id;

-- 查询user表中的id，username，sex
-- 查询provinces表中的proName
-- 条件是use的性别为男的用户
-- 根据proName分组
-- 得到每组的组中人数
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM user AS u
JOIN provinces AS p
ON u.proID=p.id
WHERE u.sex='男'
GROUP BY p.proName;

-- 再对分组结果进行筛选，选出组中人数>=1的
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM user AS u
JOIN provinces AS p
ON u.proID=p.id
WHERE u.sex='男'
GROUP BY p.proName
HAVING COUNT(*)>=1;  #多加的

-- 按照user表中id升序排列
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM user AS u
JOIN provinces AS p
ON u.proID=p.id
WHERE u.sex='男'
GROUP BY p.proName
HAVING COUNT(*)>=1
ORDER BY u.id ASC;  #多加的

-- 限制显示条数，前两条
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM user AS u
JOIN provinces AS p
ON u.proID=p.id
WHERE u.sex='男'
GROUP BY p.proName
HAVING COUNT(*)>=1
ORDER BY u.id ASC
LIMIT 0,2;



-- 查询cms_news中的id，title
-- 查询cms_cate 中的cateName
SELECT n.id,n.title,c.cateName
FROM cms_news AS n
JOIN cms_cate AS c
ON n.cid=c.id;

-- 查询cmd_news 中的id，title
-- 查询cms_admin中的username，role
SELECT n.id,n.title,a.username,a.role
FROM cms_news AS n
JOIN cms_admin AS a
ON n.aID=a.id;

-- 查询cms_news中id，title
-- 查询cms_cate中catename
-- 查询cms_admin中username,role
-- 即查询三张表,找到表与表之间的连接关系即可
SELECT n.id,n.title,c.catename,a.username,a.role
FROM cms_cate AS c
JOIN cms_news AS n
ON n.cid=c.id
JOIN cms_admin AS a
ON n.aID=a.id;



-- 插入错误的数据
INSERT user(username,password,proId)
VALUES('HJ','HJ','20');  #而provinces表中没有id为20的数据，故内连接永远显示不出此条

外连接

-- 外连接：
-- 左外连接：显示左表的全部记录及右表符合连接条件的记录
-- 右外连接：显示右表的全部记录及左表符合连接条件的记录

-- 左外连接
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM user AS u
LEFT JOIN provinces AS p  
#如果是INNER JOIN则看不到HJ这个数据，但此处因为是左连接，先显示左表，故HJ会显示出来，只不过proName下的数据显示成NULL
ON u.proID=p.id;

# 调换了两个表的顺序，主表变为provinces，则全部显示provinces表，对应到user表中没有的，则显示NULL。
# 且HJ对应数据不显示了，因为HJ对应20，在provinces表中没有20
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
LEFT JOIN user AS u
ON u.proID=p.id;

-- 右外连接
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM user AS u
RIGHT JOIN provinces AS p  
ON u.proID=p.id;

外键

-- 外键：外键是表的一个特殊字段。被参照的表是主表，外键所在字段的表为子表。
-- 设置外键的原则需要记住，就是依赖于数据库中已经存在的表的主键。
-- 外键的作用是建立该表与其父表的关联关系。父表中对记录做操作时，子表中与之对应的信息也应有相应的改变。
-- 外键的作用是保持数据的一致性和完整性，可以实现一对一或一对多的关系。
-- 注意：1.父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
-- 			 2.数据表的存储引擎只能为InnoDB。
--       3.外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
--       4.外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。



-- 创建部门表department(主表)，先有主表部门，才能在部门下加入员工
-- id depName depDesc
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教育部'),
('市场部'),
('运营部'),
('督导部');

-- 创建员工表（子表）
-- id,username,deId
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED
)ENGINE=INNODB;
INSERT employee(username,depId) VALUES('HJ1',1),
('HJ2',2),
('HJ3',3),
('HJ4',4),
('HJ5',1);

SELECT e.id,e.username,d.depName FROM
employee AS e
JOIN
department AS d
ON e.depId=d.id;

-- 删除督导部，所以部分下不能有员工了，故应先安排好员工，再解散部门
-- 以下操作虽删掉了部门，但是employee表中还有督导部的员工
DELETE FROM department WHERE depName='督导部';
-- 应进行下面的操作：
DELETE FROM employee WHERE depId=1;
DELETE FROM department WHERE id=1;
#比较麻烦吧，现在删掉两个表，重新来，学习外键
DROP TABLE employee,department;



-- 创建部门表department(主表)，先有主表部门，才能在部门下加入员工
-- id depName depDesc
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教育部'),
('市场部'),
('运营部'),
('督导部');


-- 创建员工表（子表）
-- id,username,deId
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id)  #创建外键depId，参照主表department的主键id
)ENGINE=INNODB;
INSERT employee(username,depId) VALUES('HJ1',1),
('HJ2',2),
('HJ3',3),
('HJ4',4),
('HJ5',1);


-- 删除主表中的记录
#DELETE FROM department WHERE id=1; 
#报错！因为有constraint，只能先删除员工（子表），才能删除部门（主表）

-- 删除员工表中的属于1部门的人
DELETE FROM employee WHERE depId=1;
DELETE FROM department WHERE id=1; #再删除主表部门

-- 测试插入垃圾数据
#INSERT employee(username,depId) VALUES('HJ6',10);
#报错！因为外键约束，主表中没有10对应的部门，故子表中不能插入
#不能插入也不能更新主表中没有的

添加删除外键

-- 删除员工表
DROP TABLE employee;
-- 定义外键名称
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id)  
#上述操作定义了外键名称
)ENGINE=INNODB;
INSERT employee(username,depId) VALUES('HJ1',3),
('HJ2',2),
('HJ3',3),
('HJ4',4),
('HJ5',2);

-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep; 

-- 添加外键
DELETE FROM employee WHERE depId=2;  
#由于我自己删除了父表部门id为2的数据，所以直接进行下面的操作是不行的，要先删掉子表对应的数据
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);

-- 级联操作
-- CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行。
-- SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL。
-- RESTRICT:拒绝对父表的删除或更新操作。
-- NO ACTION:标准SQL的关键字，在MySQL中与RESTRICT相同。

-- 先删掉表
DROP TABLE employee,department;

CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教育部'),
('市场部'),
('运营部'),
('督导部');

CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE #此处变了
)ENGINE=INNODB;
INSERT employee(username,depId) VALUES('HJ1',1),
('HJ2',2),
('HJ3',3),
('HJ4',4),
('HJ5',1);

-- 删除部门表的第1个部门
DELETE FROM department WHERE id=1;  #则子表中对应id=1的部分也自动删除了

#UPDATE department SET id=id+10;  
#报错，不让你更新，应把子表先全删了，再更新

#如果把FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE改成如下形式：
#FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE
#UPDATE department SET id=id+10;就不会报错了，子表会自动更新

-- SET NULL
#如果把FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE改成如下形式：
#FOREIGN KEY(depId) REFERENCES department(id) ON DELETE SET NULL ON UPDATE SET NULL
#被删除的数据变成NULL

联合查询

-- 联合查询
-- UNION和UNION ALL
-- 区别：UNION去掉相同记录，UNION ALL是简单的合并到一起

SELECT username FROM employee UNION SELECT username FROM user;  #会去掉相同的记录
SELECT username FROM employee UNION ALL SELECT username FROM user;  #只是简单的合并

#SELECT id,username FROM employee UNION ALL SELECT username FROM user;
#报错，两个查询的字段数量必须相同

SELECT id,username FROM employee UNION ALL SELECT username,age FROM user;  #可以，但是很少这样做，因为无关，结果无用

子查询

子查询的形式

-- 子查询：是将一个查询语句嵌套在另一个查询语句中。
-- 内层查询语句的查询结果，可以作为外层查询语句提供条件
DROP TABLE employee,department;

CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教育部'),
('市场部'),
('运营部'),
('督导部');

CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED
)ENGINE=INNODB;
INSERT employee(username,depId) VALUES('HJ1',1),
('HJ2',2),
('HJ3',3),
('HJ4',4),
('HJ5',1);

#不用子查询的方式很复杂：SELECT id,username FROM employee WHERE depId IN (1,2,3,4);

-- 由[NOT] IN 引发的子查询
#合并以下两句：
#SELECT id FROM department;
#SELECT id,username FROM employee WHERE depId IN(1,2,3,4);
#变为如下：为子查询，只有一句的形式：
SELECT id,username FROM employee WHERE depId IN(SELECT id FROM department);

-- 插入垃圾数据
INSERT employee(username,depId) VALUES('HJ6',8);  #因为表department中没有8，是垃圾数据
SELECT id,username FROM employee WHERE depId IN(SELECT id FROM department);  #查询不到HJ6
SELECT id,username FROM employee WHERE depId NOT IN(SELECT id FROM department);  #NOT IN 则能查询到HJ6



-- 创建学员表student
-- id,username,score
CREATE TABLE IF NOT EXISTS student(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
score TINYINT UNSIGNED
);
INSERT student(username,score) VALUES('HJ1',95),
('HJ2',35),
('HJ3',45),
('HJ4',55),
('HJ5',65),
('HJ6',75),
('HJ7',85),
('HJ8',90),
('HJ9',25);
-- 创建奖学金表scholarship
-- id,level
CREATE TABLE IF NOT EXISTS scholarship(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
level TINYINT UNSIGNED
);
INSERT scholarship(level) VALUES(90),(80),(70);

-- 查询获得一等奖学金的学员（则为分数大于等于90的）
#SELECT level FROM scholarship WHERE id = 1;
#SELECT id,username FROM student WHERE score>=90;
SELECT id,username FROM student WHERE score>=(SELECT level FROM scholarship WHERE id = 1);

-- [NOT] EXISTS
-- 查询部门表中，然而部门表中一共有4个部门，则第五个部门不存在
SELECT * FROM department WHERE id=5;  #得到empty
SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=5);
#要先执行括号里的语句，如果为真，才执行外层，id=5为假，则不执行外层，得到的结果为Empty set


-- ANY|SOME 或 ALL
-- 查询所有获得奖学金的学生
# >=ANY,为>=ANY括号里的最小值
SELECT id,username,score FROM student WHERE score>=ANY(SELECT level FROM scholarship);
# >=SOME,为>=SOME括号里的最小值
SELECT id,username,score FROM student WHERE score>=SOME(SELECT level FROM scholarship);

-- 查询所有学员中获得一等奖学金的学员
# >=ALL,为>=ALL括号里的最大值
SELECT id,username,score FROM student WHERE score>=ALL(SELECT level FROM scholarship);

-- 查询哪些学员没有获得奖学金
#<ALL 即小于括号里的最小值
SELECT id,username,score FROM student WHERE score<ALL(SELECT level FROM scholarship);

#<ANY为<括号内的最大值
SELECT id,username,score FROM student WHERE score<ANY(SELECT level FROM scholarship);

#ANY|SOME 相当于IN，不可以=ALL
SELECT id,username,score FROM student WHERE score=ANY(SELECT level FROM scholarship);
SELECT id,username,score FROM student WHERE score IN (SELECT level FROM scholarship);

#<>ALL或!=ALL 相当于NOT IN
SELECT id,username,score FROM student WHERE score!=ALL(SELECT level FROM scholarship);
SELECT id,username,score FROM student WHERE score NOT IN (SELECT level FROM scholarship);



-- 将查询结果写进数据表中
CREATE TABLE test0(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
num TINYINT UNSIGNED
);
INSERT test0(id,num)
SELECT id,score FROM student;   #字段数量一定要一致


CREATE TABLE test01(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
num TINYINT UNSIGNED
)SELECT id,score FROM student;  #此处变了   #num下都是NULL，id和score都被写进来了


CREATE TABLE test02(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
score TINYINT UNSIGNED  #此处变了
)SELECT id,score FROM student;   #id，score部分被写进来了，且只有这两部分

正则表达式查询

-- 正则表达式
-- REGEXP '匹配方式'

-- ^匹配字符开始的部分
-- 查询用户名以t开始的用户
SELECT * FROM user WHERE username REGEXP '^t';  #也不区分大小写

-- $匹配字符串结尾的部分
-- 查询以j结尾的用户
SELECT * FROM user WHERE username REGEXP 'j$';

-- .代表任意字符
SELECT * FROM user WHERE username REGEXP '.';  #即查询所有记录
SELECT * FROM user WHERE username REGEXP 't..t';
SELECT * FROM user WHERE username LIKE 't__t'; #和上面操作一样，此为模糊查询

-- [字符集合] 匹配[lto]中的任何一个都可以,只要字符中含有l或t或o都可以
SELECT * FROM user WHERE username REGEXP '[lto]';

-- [^字符集合] 除了字符集合中的任何一个字符
SELECT * FROM user WHERE username REGEXP '[^lto]';

-- 查询在一个范围中的
SELECT * FROM user WHERE username REGEXP '[a-s]';

-- S1|S2|S3 匹配S1、S2、S3任意一个字符串
SELECT * FROM user WHERE username REGEXP 'ng|qu|te';

-- *代表0个1个或者多个其前的字符
SELECT * FROM user WHERE username REGEXP 'que*';  #e出现0、1或者多次

-- +代表1个或者多个其前的字符
SELECT * FROM user WHERE username REGEXP 't+';  #t至少出现1次

-- String{N} 字符串出现N次
SELECT * FROM user WHERE username REGEXP 'que{2}';  #指定e出现2次

-- 字符串{M,N} 字符串至少出现M次，最多N次
SELECT * FROM user WHERE username REGEXP 'que{1,3}'; #e最少出现1次最多出现3次