MySQL查询表达

2021-07-12 20:29:51

查询表达式

CREATE DATABASE IF NOT EXISTS cms DEFAULT CHARACTER SET utf8;
USE cms;
-- 管理员表cms_admin
CREATE TABLE cms_admin(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT 'admin@qq.com',
role ENUM('普通管理员','超级管理员') DEFAULT '普通管理员'
);
INSERT cms_admin(username,password,email,role) VALUES('admin','admin','admin@qq.com',2);

INSERT cms_admin(username,password) VALUES('king','king'),

('麦子','maizi'),

('queen','queen'),

('test','test');

-- 创建分类表cms_cate
CREATE TABLE cms_cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(200) NOT NULL DEFAULT ''
);

INSERT cms_cate(cateName,cateDesc) VALUES('国内新闻','聚焦当今最热的国内新闻'),
('国际新闻','聚焦当今最热的国际新闻'),
('体育新闻','聚焦当今最热的体育新闻'),
('军事新闻','聚焦当今最热的军事新闻'),
('教育新闻','聚焦当今最热的教育新闻');

-- 创建新闻表cms_news
CREATE TABLE cms_news(
id INT UNSIGNED AUTO_INCREMENT KEY,
title VARCHAR(50) NOT NULL UNIQUE,
content TEXT,
clickNum INT UNSIGNED DEFAULT 0,
pubTime INT UNSIGNED,
cId TINYINT UNSIGNED NOT NULL COMMENT '新闻所属分类，对应分类表中的id',
aId TINYINT UNSIGNED NOT NULL COMMENT '哪个管理员发布的，对应管理员表中的id'
);
INSERT cms_news(title,content,pubTime,cId,aId) VALUES('亚航客机失联搜救尚无线索 未发求救信号','马来西亚亚洲航空公司一架搭载155名乘客的客机28日早晨从印度尼西亚飞往新加坡途中与空中交通控制塔台失去联系，下落不明。',1419818808,1,2),
('北京新开通四条地铁线路 迎接首位客人','12月28日凌晨，随着北京地铁6号线二期、7号线、15号线西段、14号线东段的开通试运营，北京的轨道交通运营里程将再添62公里，共计达到527公里。当日凌晨5时许，北京地铁7号线瓷器口换乘站迎来新线开通的第一位乘客。',1419818108,2,1),
('考研政治题多次出现*讲话内容','新京报讯 （记者许路阳 (微博)）APEC反腐宣言、国家公祭日、依法治国……昨日，全国硕士研究生招生考试进行首日初试，其中，思想政治理论考题多次提及时事热点，并且多次出现*在不同场合的讲话内容。',1419818208,3,2),
('深度-曾雪麟：佩兰别重蹈卡马乔覆辙','12月25日是前国足主帅曾雪麟的85岁大寿，恰逢圣诞节，患有尿毒症老爷子带着圣诞帽度过了自己的生日。此前，腾讯记者曾专访曾雪麟，尽管已经退休多年，但老爷子仍旧关心着中国足球，为国足揪心，对于国足近几位的教练，他只欣赏高洪波。对即将征战亚洲杯的国足，老爷子希望佩兰不要重蹈卡马乔的覆辙',1419818308,2,4),
('国产JAD-1手枪枪架投入使用 手枪可变"冲锋枪"','日前，JAD-1型多功能手枪枪架通过*部特种警用装备质量监督检验中心检验，正式投入生产使用。此款多功能枪架由京安盾(北京)警用装备有限公司开发研制，期间经广东省江门市*特警支队试用，获得好评。',1419818408,4,4),
('麦子学院荣获新浪教育大奖','麦子学院最大的职业IT教育平台，获奖了',1419818508,1,5),
('麦子学院荣获腾讯教育大奖','麦子学院最大的职业IT教育平台，获奖了',1419818608,1,5),
('麦子学院新课上线','麦子学院PHP课程马上上线了，小伙伴快来报名学习哈',1419818708,1,5);

-- 创建身份表 provinces
CREATE TABLE provinces(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
proName VARCHAR(10) NOT NULL UNIQUE
);
INSERT provinces(proName) VALUES('北京'),
('上海'),
('深圳'),
('广州'),
('重庆');

-- 创建用户表cms_user
CREATE TABLE cms_user(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT 'user@qq.com',
regTime INT UNSIGNED NOT NULL,
face VARCHAR(100) NOT NULL DEFAULT 'user.jpg',
proId TINYINT UNSIGNED NOT NULL COMMENT '用户所属省份'
);

INSERT cms_user(username,password,regTime,proId)

VALUES('张三','zhangsan',1419811708,1),
('张三丰','zhangsanfeng',1419812708,2),
('章子怡','zhangsan',1419813708,3),
('long','long',1419814708,4),
('ring','ring',1419815708,2),
('queen','queen',1419861708,3),
('king','king',1419817708,5),
('blek','blek',1419818708,1),
('rose','rose',1419821708,2),
('lily','lily',1419831708,2),
('john','john',1419841708,2);

一、单表查询

SELECT 字段 FROM 表名 WHERE条件
建立了cms数据库，以及多个表

最简单的查询语句SELECT * FROM cms_admin;其中代表的是所有的查询字段，也就是表示查询表中的所有列，效果等同于SELECT cms_admin. FROM cms_admin;

用第二种方法是因为同一个数据库中有多张表，如果查询某个字段，这个字段可能在多个表中都有，所以需要在前面指定表名

SELECT id,username FROM cms_admin;

如果只想查询其中某几个字段，字段之间需要用逗号隔开，查询某个列的时候至少要有一列

SELECT id,username,role FROM cms.cms_admin;

也可以是库名.表名

SELECT cms_admin.username,cms_admin.id FROM cms.cms_admin;

可以在字段前面加上表名，在表名前面加上库名

 SELECT id,username FROM cms_admin [AS] a; 其中as可以省略

给表名起别名为a

SELECT a.id,a.username,a.role FROM cms_admin AS a;

给字段起别名

SELECT id AS '编号',username AS '用户名',role AS '角色' FROM cms_admin;

MySQL查询表达
给表和字段都起别名

SELECT a.id AS i,a.username AS u,a.role AS r FROM cms.cms_admin AS a;

二、带条件的查询

MySQL查询表达

查新第一条记录的相关字段
SELECT id,username,email FROM cms_user WHERE id=1;

查询用户名为king的相关字段
SELECT id,username,email FROM cms_user WHERE username='King';

查询id不等于1的数据
SELECT id,username,email FROM cms_user WHERE id!=1;

或者SELECT id,username,email FROM cms_user WHERE id<>1;

给现存的表添加字段

ALTER TABLE cms_user ADD age TINYINT UNSIGNED DEFAULT 18;

INSERT cms_user(username,password,regTime,proId,age) VALUES('test1',123456,1419811708,1,NULL);
增加了一个年龄为null值的数据，查询数值为null的数据

SELECT * FROM cms_user WHERE age=NUll;

直接用等号形式是查不出来的哦
会出现Empty set
只能用<=>来查询等于空值的数据，该符号比等号多了一个功能，就是检测null值

SELECT * FROM cms_user WHERE age<=>NULL;

SELECT * FROM cms_user WHERE age IS NULL;

MySQL查询表达
除了检测null之外，<=> 和 = 的效果一样

SELECT * FROM cms_user WHERE age<=>18;

MySQL查询表达

SELECT * FROM cms_user WHERE age IS NOT NULL;

MySQL查询表达

三、范围查询

1、between and

SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10;

MySQL查询表达

2、指定集合in 或者 not in

大小写并不影响查询结果

SELECT * FROM cms_user WHERE id IN(1,3,5,7,9,11,13,15,100,1000);

SELECT * FROM cms_user WHERE proID IN(1,3);

SELECT * FROM cms_user WHERE username IN('king','queen','张三','章子怡');

SELECT * FROM cms_user WHERE username IN('King','Queen','张三','章子怡');

四、模糊查询

匹配字符：LIKE | NOT LIKE
%:代表0个一个或者多个任意字符
_:代表1个任意字符

SELECT * FROM cms_user WHERE username LIKE '%张%';

MySQL查询表达

查询姓张的数据
SELECT * FROM cms_user WHERE username LIKE '张%';

查询用户名中包含in这两个字母的数据
SELECT * FROM cms_user WHERE username LIKE '%in%';

百分号代表任意字符，可以查询出所有的数据
SELECT * FROM cms_user WHERE username LIKE '%';

查询用户名为3位的用户，用三个下划线来表示
SELECT * FROM cms_user WHERE username LIKE '___';

查询用户名有4位的数据
SELECT * FROM cms_user WHERE username LIKE '___';

查询第二位是i，后面是否还有字符不知道，所以用百分号表示
SELECT * FROM cms_user WHERE username LIKE '_i%';

加上not就有取反的作用

五、逻辑运算符

AND | OR
and要求两个条件同时满足|or表示两个条件中的一个满足即可

SELECT * FROM cms_user WHERE username='king' AND password='king';
SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL;

SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL AND proID=3;

SELECT * FROM cms_user WHERE 5<=id<=10 AND username LIKE '____';

SELECT * FROM cms_user WHERE id BETWEEN 5 AND 10 AND username LIKE '____';

 SELECT * FROM cms_user WHERE username LIKE '张%' OR proID IN(2,4);

六、分组查询GROUP BY

将字段值相同的分为一组，且分组之后只会显示组中的第一条记录

SELECT * FROM cms_user GROUP BY proID;

动态增加字段：性别
ALTER TABLE cms_user ADD sex ENUM('男','女','保密');

UPDATE cms_user SET sex='男' WHERE id IN(1,3,5,7,9);
UPDATE com_user SET sex='女' WHERE id IN(2,4,6,8,10);
UPDATE cms_user SET sex='保密' WHERE id=11;

通过性别字段进行分组

SELECT * FROM cms_user GROUP BY sex;

按照位置也可以，按照位置需要知道每个字段对应的位置在哪儿
SELECT * FROM cms_user GROUP BY 7

同理可知，按照年龄分组为
SELECT * FROM cms_user GROUP BY 9;

按照多个字段分组
SELECT * FROM cms_user GROUP BY sex,proID;

先按照性别进行分组，再按照省份进行分组

MySQL查询表达

如果有条件的话，先通过where筛选出一部分结果，然后再按照分组进行展示
查询编号大于等于5的用户，并且按照sex进行分组

SELECT * FROM cms_user WHERE id>5 GROUP BY sex;

MySQL查询表达

七、分组查询配合聚合函数

查询按照性别分组的用户名详情以及组中第一条记录的id和sex
SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;

MySQL查询表达

查看表中按照省份id进行分组后的性别、注册时间和用户名详情
SELECT proID,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime) FROM cms_user GROUP BY proID;

MySQL查询表达

UPDATE cms_user SET age=11 WHERE id=1;

更改了数据


UPDATE cms_user SET age=11 WHERE id=1;
UPDATE cms_user SET age=21 WHERE id=2;
UPDATE cms_user SET age=33 WHERE id=3;
UPDATE cms_user SET age=44 WHERE id=4;
UPDATE cms_user SET age=25 WHERE id=5;
UPDATE cms_user SET age=77 WHERE id=6;
UPDATE cms_user SET age=56 WHERE id=7;
UPDATE cms_user SET age=88 WHERE id=8;
UPDATE cms_user SET age=12 WHERE id=9;
UPDATE cms_user SET age=32 WHERE id=10;
UPDATE cms_user SET age=65 WHERE id=11;

查询编号，性别，用户名详情以及组中总人数按照sex分组

SELECT id,sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalusers FROM cms_user GROUP BY sex;

如果不重新定义名称的话，字段长度过长
MySQL查询表达
统计表中所有记录
SELECT COUNT(*) AS totalusers FROM cms_user;

当使用count（字段）的时候不统计null值记录
SELECT COUNT(id) AS totalusers FROM cms_user;

查询编号、性别、用户名详情、组中总人数、组中最大年龄、最小年龄、平均年龄，以及年龄总和并按照性别分组

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 cms_user 
GROUP BY sex;

MySQL查询表达

SELECT id,sex,
COUNT(*) AS totalusers,
MAX(age) AS max_age,
MIN(age) AS min_age
FROM cms_user GROUP BY sex WITH ROLLUP;

WITH ROLLUP会对每个聚合函数在最后进行一个汇总

MySQL查询表达

SELECT id,sex,
COUNT(*) AS totalusers,
MAX(age) AS max_age,
MIN(age) AS min_age,
SUM(age) AS sum_age
FROM cms_user GROUP BY sex;

八、having二次查询语句

having语句只有配合了GROUP BY才有用

having语句对分组结果进行二次筛选
只能使用到分组之后
查询性别、用户名详情。组中总人数，最大年龄，年龄总和根据性别进行分组

SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalusers,
MAX(age),
SUM(age) 
FROM cms_user GROUP BY sex;

MySQL查询表达
对分组后的结果进行二次筛选，筛选组中人数大于2的组

SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalusers,
MAX(age),
SUM(age) 
FROM cms_user GROUP BY sex HAVING COUNT(*)>2;

MySQL查询表达
此时可以看出，已经把保密组的数据筛选掉了

查询组中人数大于2且最大年龄大于60的

SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalusers,
MAX(age),
SUM(age) 
FROM cms_user GROUP BY sex HAVING COUNT(*)>2 AND MAX(age)>60;

注意：在二次查询中并不能使用别名，只能用聚合函数
MySQL查询表达
现在在分组的时候有条件了，就应该把条件写在分组之前

SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalusers,
MAX(age),
SUM(age) 
FROM cms_user 
WHERE id>2
GROUP BY sex HAVING COUNT(*)>2 AND MAX(age)>60;

MySQL查询表达

九、ORDER BY 对查询结果进行排序

默认是按照id升序来排序
SELECT * FROM cms_user ORDER BY id;
或者是SELECT * FROM cms_user ORDER BY id ASC;

降序用DESC
SELECT * FROM cms_user ORDER BY id DESC;
同样的这里的id可以用位置1来代替
SELECT * FROM cms_user ORDER BY 1 DESC;

 按照年龄升序排列
SELECT * FROM cms_user ORDER BY age ASC;

按照多个字段来进行排序，按照年龄升序和id降序来排列
SELECT * FROM cms_user ORDER BY age ASC,id DESC;

SELECT id,age,sex,GROUP_CONCAT(username),COUNT(*) AS totalUsers,SUM(age) AS sum_age 
FROM cms_user 
WHERE id>=2 
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC,id ASC;

MySQL查询表达
SELECT * FROM cms_user ORDER BY RAND();
随机提取记录，每次排列的效果都不一样

十、通过limit限制显示条数

查询表中前3条记录
SELECT * FROM cms_user LIMIT 3;

降序排列之后再显示前5条记录
SELECT * FROM cms_user ORDER BY id DESC LIMIT 5;

查询表中第一条记录
SELECT * FROM cms_user LIMIT 0,1;

SELECT * FROM cms_user LIMIT 1,1;
前面的1表示的是相对于第一条数据来说，第二个1是偏移量为1，每页显示1条记录

SELECT * FROM cms_user LIMIT 0,5;
下一页
SELECT * FROM cms_user LIMIT 5,5;

十一、更新和删除order by 和 limit

更新用户名为4位的用户，让其已有年龄-3
UPDATE cms_user SET age=age-3 WHERE username LIKE '____';

更新前三条记录让已有年龄+10
做更新或者删除的时候，limit函数只能有一个参数，不能设置偏移量
UPDATE cms_user SET age=age+10 LIMIT 3;

按照id降序排列，更新前三条
UPDATE cms_user SET age=age+10 ORDER BY id DESC LIMIT 3

DELETE FROM cms_user WHERE sex='男' ORDER BY age DESC LIMIT 1;

十二、连接查询

1、什么是连接查询？

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

2、内连接查询

JOIN|CROSS JOIN INNER JOIN
通过ON连接条件
显示两个表中复合连接条件的记录
以两个表为例

SELECT id,username,proID FROM cms_user;
但这里的省份使用id来表示的，我们并不知道每个省份代表的含义是什么
而这个省份名称来自于provinces省份表

SELECT id,username,proID FROM cms_user,provinces;
通过这个语句进行查询的时候报错了，是因为两个表中都有id这个字段
1052 - Column 'id' in field list is ambiguous

SELECT cms_user.id,username,proName FROM cms_user,provinces;

MySQL查询表达
但这并不是我们想得到的结果，每个人都对应5个省份，所以我们需要通过内连接来进行查询

SELECT cms_user.id,username,proName FROM cms_user,provinces WHERE cms_user.proID=provinces.id;

MySQL查询表达
该方式可以通过内连接方式实现

-- 查询cms_user表中id,username,email,sex
-- 查询provinces表proName

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
INNER JOIN provinces AS p
ON u.proID=p.id;

效果等同于：

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
CROSS JOIN provinces AS p
ON u.proID=p.id;

也等同于

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
JOIN provinces AS p
ON u.proID=p.id;

MySQL查询表达

-- 查询cms_user id,username,sex
-- 查询provinces proName
-- 条件是cms_user的性别为男的用户

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
JOIN provinces AS p
ON u.proID=p.id
WHERE u.sex='男';

MySQL查询表达
– 根据proName分组

SELECT u.id,u.username,u.email,u.sex,p.proName,COUNT(*) AS totalusers,GROUP_CONCAT(username)
FROM cms_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.email,u.sex,p.proName,COUNT(*) AS totalusers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN provinces AS p
ON u.proID=p.id
WHERE u.sex='男'
GROUP BY p.proName
HAVING COUNT(*)>=1;

– 按照id升序排列

SELECT u.id,u.username,u.email,u.sex,p.proName,COUNT(*) AS totalusers,GROUP_CONCAT(username)
FROM cms_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;

– 限制显示条数 前2条

SELECT u.id,u.username,u.email,u.sex,p.proName,COUNT(*) AS totalusers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN provinces AS p
ON u.proID=p.id
WHERE u.sex='男'
GROUP BY p.proName
HAVING COUNT(*)>=1
ORDER BY id ASC
LIMIT 2;

DESC cms_news;
MySQL查询表达
其中cID对应的是分类表中的id，aID对应的是管理员中的id

--　查询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;

MySQL查询表达

-- cms_news id ,title
-- cms_cate cateName
-- cms_admin username,role

SELECT n.id,n.title,c.cateName,a.username,a.role
FROM cms_news AS n
JOIN cms_Cate AS c
on n.cID=c.id
JOIN cms_admin AS a
ON n.aID=a.id;

3、外连接查询

插入一条不符合规则的无效数据，在进行内连接的时候查不出来
我们可以了解到省份一共只有5个
MySQL查询表达
为了测试，我们再cms_user表里面插入一条超过范围的数据
INSERT cms_user(username,password,regTime,proID) VALUES(‘TEST1’,‘TEST2’,1231203,20);

再次进行内连接查询的时候，我们发现没有办法查找到次记录
MySQL查询表达
MySQL查询表达
左外以左表为主，右外以右表为主

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
LEFT JOIN provinces AS p
ON u.proID=p.id;

LEFT之前的表是主表，先把主表的内容查找出来，在右表中范围之外的数值用null来代替
查询结果为：
MySQL查询表达

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
LEFT JOIN cms_user AS u
ON u.proID=p.id;

交换一下顺序，provinces就成了主表

由于province是主表，先查询主表的内容，然后根据规则进行查询的结果就没有TEST1的记录了
MySQL查询表达
同理，右外连接的原理相同，将right join之后的表作为主表

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
RIGHT JOIN cms_user AS u
ON u.proID=p.id;

十三、外键操作

外键是表的一个特殊字段，被参照的表时主表，外键所在字段的表为子表。设置外键的原则就是依赖于数据库已存在的表的主键。外键的作用是建立该表与其父表的关联关系。父表中对记录做操作时，子表中与之对应的信息也会有相应的改变。

外键的作用保持数据的一致性和完整性。

注意：
（1）子表和父表必须使用相同的存储引擎，而且禁止使用临时表；
（2）数据表的存储引擎只能是INNODB；
（3）外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符长度可以不同

建立主表：
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('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);

MySQL查询表达
MySQL查询表达

SELECT e.id,e.username,p.depname
FROM department as p
JOIN employee as e
ON e.depID=p.id;

MySQL查询表达
DELETE FROM department WHERE depname=‘督导部’;
但是员工表下仍然有这个部门的人员
MySQL查询表达
但是这时就有了一些问题，当我们把部门解散之前应该先把该部门原来的员工安排好才行

当附表中与主表有关联的字段，应该对附表做一些限制——外键约束操作

所以应该是先把附表中第4部门的人员删除掉，才能删除主表中的督导部

如果要保证这种效果，就要使用到外表

主表
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depID TINYINT UNSIGNED
FOREING KEY(depID) REFERENCES department(id)
)ENGINE=INNODB;
 

员工表

CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depID TINYINT UNSIGNED
FOREING KEY(depID) REFERENCES department(id)
)ENGINE=INNODB;

INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);

此时如果我们还想删除部门表中的数据则会报错

DELETE FROM department WHERE id=1;

1451 - Cannot delete or update a parent row: a foreign key constraint fails (`cms`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId`) REFERENCES `department` (`id`))

有了外键约束，在这个部门下员工，如果想删除这个部门，则必须先删除该部门下的员工

删除员工表中属于1部门的人

只有先删除部门1的员工才能删除部门1

mysql> DELETE FROM employee WHERE depID=1;
Query OK, 1 row affected

mysql> DELETE FROM department WHERE id=1;
Query OK, 1 row affected

插入无效数据，之前没有外键约束的条件下可以成功，但是现在有了外键约束，所以无效数据插入失败

十四、添加哈删除外键

给外键加一个名称 CONSTRAINT emp_fk_dep

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;

再来查看表的定义
MySQL查询表达
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称

ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;

外键删除成功，所有的限制就没有，此时可以随便删除部门的数据

动态添加外键

ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depID) REFERENCES department(id);

CASCADE:从父表删除或更新且自动删除和更新子表中匹配的数据
SET NULL：从父表删除或更新数据，并设置子表的外键列为NULL，但是如果使用该选项，必须保证子表中没有指定NOT NULL

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;

此时，父子表之间可以同步删除主键相关记录

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 ON UPDATE CASCADE
)ENGINE=INNODB;

此时，父子表之间可以同步更新部门id

UPDATE department SET id=id+10;

在员工表中的id也随之更新

父表中的记录删除之后，主表中的数据自动变为NULL值

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 SET NULL ON UPDATE SET NULL
)ENGINE=INNODB;

MySQL查询表达
UPDATE department SET id=id+10;
MySQL查询表达

十五、联合查询

UNION会去掉相同的记录，UNION ALL 是简单的合并到一起

SELECT username FROM employee UNION SELECT username FROM cms_user;

MySQL查询表达
union将重复记录剔除了

SELECT username FROM employee UNION ALL SELECT username FROM cms_user;

MySQL查询表达
仅仅将查询结果进行合并

必须保证两个查询语句查询的字段数目相同

SELECT id,username FROM employee UNION ALL SELECT username,age FROM cms_user;

MySQL查询表达

十六、子查询

子查询就是将一个查询语句放在另一个查询语句中。内层查询语句的查询结果可以为外层查询语句提供条件。
引发子查询的情况？
MySQL查询表达

SELECT id,username FROM employee WHERE depID IN(SELECT id FROM department);

INSERT employee(username,depId) VALUES('testtest',8);
SELECT id,username FROM employee WHERE depID NOT IN(SELECT id FROM department);

MySQL查询表达

创建学员表和奖学金表

--　创建学员表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('king',95),
('king1',35),
('king2',45),
('king3',55),
('king4',65),
('king5',75),
('king6',80),
('king7',90),
('king8',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);

查一下获得一等奖学金的学生有哪些

先查一下一等奖学金的分数段

SELECT level FROM scholarship WHERE id=1;
--可以看到一等奖学金需要分数大于等于90

SELECT id,username FROM student WHERE score>=90;

--一行解决问题：
SELECT id,username FROM student WHERE score>(SELECT level FROM scholarship WHERE id=1);

内层查询一定要放在括号

部门表中不存在id=5的部门，所以我们可以使用exists来查询

存在的时候返回真，为真的时候才能查到结果，才能执行外层语句

SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=5);

十七、使用ANY|SOME或者ALL的子查询

MySQL查询表达
查询所有获得奖学金的同学的编号和名称

SELECT id,username FROM student WHERE score>=ANY(SELECT level FROM scholarship);

SELECT id,username FROM student WHERE score>=SOME(SELECT level FROM scholarship);

--查询获得一等奖学金的同学的信息
 SELECT id,username FROM student WHERE score>=ALL(SELECT level FROM scholarship);

--查看没有得到奖学金的同学的信息
SELECT id,username FROM student WHERE score<ALL(SELECT level FROM scholarship);

SELECT id,username FROM student WHERE score=ANY(SELECT level FROM scholarship);
--效果等同于
SELECT id,username FROM student WHERE score IN (SELECT level FROM scholarship);
--查询出现过90,80,70分数的同学的编号和名称

将查询结果写入新表中

创建一个新表

CREATE TABLE test1 (
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
num TINYINT UNSIGNED
);

INSERT test1(id,num) SELECT id,score FROM student;

MySQL查询表达
或者在建表的时候直接设置插入的数据

CREATE TABLE test2 (
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
num TINYINT UNSIGNED
)SELECT id,score FROM student;

MySQL查询表达
由于字段不匹配，所以得到的结果是num字段为null，可以将id字段变为num字段

十八、正则表达查询

MySQL查询表达
MySQL查询表达

-- ^匹配字符开始的部分
-- 查询用户名以t开始的用户
SELECT * FROM cms_user WHERE username REGEXP '^t';
-- $匹配字符串结尾的部分
SELECT * FROM cms_user WHERE username REGEXP 'g$';
-- .代表任意字符
SELECT * FROM cms_user WHERE username REGEXP '.';
--可以查询到所有的结果

SELECT * FROM cms_user WHERE username REGEXP 'r..g';
--效果等同于
SELECT * FROM cms_user WHERE username LIKE 'r__g';

-- [字符集合] [lto]
SELECT * FROM cms_user WHERE username REGEXP '[LTO]';

-- [^字符集合] 不全是字符集合中的内容
SELECT * FROM cms_user WHERE username REGEXP '[^LTO]';

--增加一些新记录
INSERT cms_user(username,password,regTime,proId)
VALUES('lll','lll',138212349,2),
('ttt','lll',138212349,2),
('ooo','lll',138212349,2);

#再次执行
SELECT * FROM cms_user WHERE username REGEXP '[^LTO]';

MySQL查询表达
用户名中全部都是l/o/t的记录查询不出来了

SELECT * FROM cms_user WHERE username REGEXP '[a-k]';
--用户名中出现a-k的字母都可以被查询到

--查询用户名中包括ng或者qu的数据
SELECT * FROM cms_user WHERE username REGEXP 'ng|ku';

SELECT * FROM cms_user WHERE username REGEXP 'kin*';
--其中n可以出现任意次，0次1次或多次都可以

#*代表0个或1个或多个*前面的字符

#+代表1个或多个+前面的字符

SELECT * FROM cms_user WHERE username REGEXP 't+';
--username中必须有t这个字符

#{n}表示前面的字符出现n次
SELECT * FROM cms_user WHERE username REGEXP 'l{1}';
SELECT * FROM cms_user WHERE username REGEXP 'l{1,3}';
#表示查询用户名称中出现1-3次l字母的记录
上一篇：【垃圾回收】百度面试被问到cms垃圾回收机制，麻了麻了


下一篇：grafan8.0安装aliyun_cms_grafana_datasource