文章目录
- 一. 数据的操作
- 1. DML
- 2. DQL
- 二. 连接查询
- 1. 内连接查询
- 2. 外连接查询
- 3. 外键操作
- 4. 联合查询
- 三. 子查询
- 1. 引发子查询的情况
- 2. 将查询结果写入数据表中
- 3. 创建数据表时同时将查询结果写入数据表中
- 四. 正则表达式查询
一. 数据的操作
1. DML
- 更新数据
--更新用户名为4位的用户,让其年龄-3
UPDATE cms_user SET age=age-3 WHERE username LIKE '____';
--更新前三条记录,让已有年龄+10,更新或删除时不能用偏移量
UPDATE cms_user SET age=age+10 LIMIT 3;
--按照id降序排列,更新前3条
UPDATE cms_user SET age=age+10 ORDER BY id DESC LIMIT 3;
--删除用户性别为男的,按照年龄降序排列,删除前1条记录
DELETE FROM cms_user WHERE sex='male' ORDER BY age DESC LIMIT 1;
2. DQL
- ORDER BY 对查询结果排序
--按照id降序排列,默认是ASC
SELECT * FROM cms_iser ORDER BY id;
SELECT * FROM cms_iser ORDER BY id DESC;
--按照年龄升序排列
SELECT * FROM cms_user ORDER BY age ASC;
SELECT * FROM cms_user ORDER BY 1 DESC;
--按照多个字段排序
SELECT * FROM cms_user ORDER BY age ASC,id DESC;
SELECT id,age,sex,GROUP_CONCAT(username), COUNT (*) AS totalUsers
FROM cms_user
WHERE id>=2
GROUP BY sex
HAVING COUNT (*)>=2
ORDER BY age DESC,id ASC;
--随机提取记录
SELECT * FROM cms_user ORDER BY RAND();
- 通过limit限制显示条数
(1) 显示条数
--查询表中前三条记录
SELECT * FROM cms_user LIMIT 3;
SELECT * FROM cms_user ORDER BY id DESC LIMIT 5;
(2)偏移量,显示条数
--查询表中前一条记录,第一个表示第几条记录开始,第二数表示显示多少条记录
SELECT * FROM cms_user LIMIT 1;
SELECT * FROM cms_user LIMIT 0,1;
SELECT * FROM cms_user LIMIT 1,1;
SELECT * FROM cms_user LIMIT 0,5;
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 cms_user
WHERE id>=1
GROUP BY sex
HAVING COUNT (*)>=2
ORDER BY age DESC
LIMIT 0,2;
二. 连接查询
1. 内连接查询
- JOIN|CROSS JOINNER
- 通过ON连接查询
- 显示两个表中符合连接条件的记录
--查询cms_user id,username
--provinces, proName
SELECT cms_user.id,username,proName FROM cms_user,provinces;
--cms_user的proId对应省份表中的id
SELECT cms_user.id,username,proName FROM cms_user,provinces
WHERE cms_id.proId=provinces.id;
--查询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 provinces AS p
CROSS JOIN cms_user AS u
ON u.proId=p.id;
--查询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='male';
--根据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='male'
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='male'
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='male'
GROUP BY p.proName
HAVING COUNT (*)>=1
ORDER BY u.id ASC;
-- 限制显示条数,前两条
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='male'
GROUP BY p.proName
HAVING COUNT (*)>=1
ORDER BY u.id ASC
LIMIT 0,2;
--查询新闻表中的id,title
--查询cms_cate中的cateName
SELECT n.id,n.title,c.cateName
FROM cms_news AS n
JOIN cms_cate AS c
ON n.aId=c.id;
--查询cms_new 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;
2. 外连接查询
- 左外连接,显示左表中的全部记录及右表符合连接条件的记录
--左外连接,left join左边为主表
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;
- 右外连接,显示右表中的全部记录及左表符合连接条件的记录
--右外连接
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
RIGHT JOIN provinces AS p
ON u.proId=p.id;
3. 外键操作
- 保证数据一致性和完整性
被参照的表是主表,外键所在的字段表为字表。
父表对记录做记录时,字表中与之对应的信息也应有相应的改变。
可以实现一对一或一对多的关系。
外键列和参照列必须具有相似的数据类型。
外键列和参照列必须创建索引。
--创建部门表department(主表)
--id,depName
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR (20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');
--创建员工表employee(子表)
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)
)ENGINE=INNODB;
INSERT employee(username,depId) VALUES ('king',1),
('queen',2),
('zhangsan',3),
('lisi',4),-加外键名称em_fk_dep
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR (20) NOT NULL UNIQUE ,
depId TINYINT UNSIGNED,
CONSTRAINT em_fk_dep FOREIGN KEY (depId) REFERENCES department(id)
)ENGINE=INNODB;
INSERT employee(username,depId) VALUES ('king',3),
('queen',2),
('zhangsan',3),
('lisi',4),
('wangwu',2);
--删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_fn_dep;
--添加外键
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);
('wangwu',1);
--删除主表中的记录
--先删除子表中的属于1部门的人
DELETE FROM employee WHERE depId=1;
DELETE FROM department WHERE id=1;
- 添加删除外键
在这里插入代码片
(1) CASCADE
--创建部门表department(主表)
--id,depName
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR (20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');
--父表中删除相应记录,字表中也会删除CASCADE
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR (20) NOT NULL UNIQUE ,
depId TINYINT UNSIGNED,
CONSTRAINT em_fk_dep FOREIGN KEY (depId) REFERENCES department(id) ON DELETE CASCADE
)ENGINE=INNODB;
INSERT employee(username,depId) VALUES ('king',3),
('queen',2),
('zhangsan',3),
('lisi',4),
('wangwu',2);
--删除部门表中的第一个部门
DELETE FROM department WHERE id=1;
-----------
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR (20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');
--父表中删除相应记录,字表中也会删除CASCADE,能让父表更新,字表跟着更新
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR (20) NOT NULL UNIQUE ,
depId TINYINT UNSIGNED,
CONSTRAINT em_fk_dep FOREIGN KEY (depId) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=INNODB;
INSERT employee(username,depId) VALUES ('king',1),
('queen',2),
('zhangsan',3),
('lisi',4),
('wangwu',1);
UPDATE department SET id=id+10;
(2) SET NULL
--父表中删除相应记录,没有改变的值是NULL
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR (20) NOT NULL UNIQUE ,
depId TINYINT UNSIGNED,
CONSTRAINT em_fk_dep FOREIGN KEY (depId) REFERENCES department(id) ON DELETE SET NULL ON UPDATE SET NULL
)ENGINE=INNODB;
4. 联合查询
- UNION 去掉相同的记录
SELECT usrname FROM employee UNION SELECT username FROM cms_user;
- UNION ALL简单的合并
SELECT usrname FROM employee UNION ALL SELECT username FROM cms_user;
三. 子查询
1. 引发子查询的情况
子查询是将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果,可以作为外层查询语句提供条件。
- 使用[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);
SELECT id,username FROM employee WHERE depId NOT IN(SELECT id FROM department);
- 使用比较运算符的子查询
--创建student
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',95),
('king3',75),
('king2',85),
('king4',25),
('king5',90);
--scholarship
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;
SELECT id,username FROM student WHERE score>=90;
SELECT id,username FROM student WHERE score>=(SELECT level FROM scholarship WHERE id=1);
- 使用[NOT]EXISTS的子查询
--查询部门中,判断内侧是不是真
SELECT * FROM department WHERE id=5;
SELECT id,username FROM employee WHERE EXISTS (SELECT * FROM department WHERE id=5);
SELECT id,username FROM employee WHERE NOT EXISTS (SELECT * FROM department WHERE id=5);
- 使用ANY]SOME或者ALL查询
| | ANY | SOME | ALL
|>,>=| 最小值|最小值|最大值
|<.<=| 最大值|最大值|最小值
|= | 任意值|任意值|
|<>.!= | | |任意值
--查询所有获得奖学金的学生
SELECT id,username,score FROM student WHERE score>=ANY(SELECT level FROM scholarship);
SELECT id,username,score FROM student WHERE score>=SOME(SELECT level FROM scholarship);
--查询获得一等奖学金的学生
SELECT id,username,score FROM student WHERE score>=ALL(SELECT level FROM scholarship);
--查询没有获得奖学金的学生
SELECT id,username,score FROM student WHERE score<ALL(SELECT level FROM scholarship);
--相当于IN
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);
--相当于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);
2. 将查询结果写入数据表中
CREATE TABLE test1(
id TINYINT UNSIGNED AUTO_INCREMENT KEY ,
number TINYINT UNSIGNED
);
INSERT test1(id,number)
SELECT id,score FROM student;
3. 创建数据表时同时将查询结果写入数据表中
CREATE TABLE test2(
id TINYINT UNSIGNED AUTO_INCREMENT KEY ,
number TINYINT UNSIGNED
)SELECT id,score FROM student;
CREATE TABLE test3(
id TINYINT UNSIGNED AUTO_INCREMENT KEY ,
score TINYINT UNSIGNED
)SELECT id,score FROM student;
四. 正则表达式查询
常用匹配方式
- ^匹配字符开始的地方
--查询用户名以t开始
SELECT * FROM cms_user WHERE username REGEXP '^t';
- $匹配字符结尾的地方
SELECT * FROM cms_user WHERE username REGEXP'g$';
- . 匹配字符串中的任意一个字符,包括回车和换行
SELECT * FROM cms_user WHERE username REGEXP'r..g';
- [字符集合]匹配字符集合中的任意一个字符
SELECT * FROM cms_user WHERE username REGEXP'[lto]';
- [^字符集合]匹配除了字符集合中的任意一个字符
SELECT * FROM cms_user WHERE username REGEXP'[^lto]';
- S1|S2|S3 匹配S1,S2,S3中的任意一个
SELECT * FROM cms_user WHERE username REGEXP'ng|qu|te';
- *代表0个1个或多个其前的字符
SELECT * FROM cms_user WHERE username REGEXP'que*';
- +代表1个或多个其前的字符
SELECT * FROM cms_user WHERE username REGEXP'que+';
- String{N}字符串出现N次
SELECT * FROM cms_user WHERE username REGEXP'que{2}';
- String{M,N}字符串至少出现M次,最多出现N次
SELECT * FROM cms_user WHERE username REGEXP'que{1,3}';