MySQL-查询数据操作（DQL）

单表查询

• SELECT查询格式说明

SELECT select_expr [,select_expr...]
[
FROM tnl_name
[WHERE 条件]
[GROUP BY {col_name|position} [ASC|DESC], ... 分组]
[HAVING 条件 对分组结果进行二次筛选]
[ORDER BY {col_name|position} [ASC|DESC], ... 排序]
[LIMIT 限制条数]
]

数据库：cms
数据表：cms_admin cms_cate cms_news cms_user provinces

• –查询全部

SELECT * FROM user;
SELECT user.* FROM user;	#查询表user的所有数据

• –查询字段

SELECT id,username FROM cms_admin;

• –给表名起别名

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

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

• –给字段起别名

SELECT a.id AS '编号',username AS '用户名',email AS '邮箱',role AS '角色' FROM cms_admin;

• –表别名和字段别名结合起来

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

• –WHERE条件
• –指定范围 BETWEEN AND NOT BETWEEN AND
• –查询编号3~10之前的记录

SELECT * FROM cms_user WHERE id BETWEEB 3 AND 10;
SELECT * FROM cms_user WHERE id NOT BETWEEB 3 AND 10;

• –指定集合 查询编号为1,3,5,7,9,11,100的记录

SELECT * FROM cms_user WHERE id IN(1,3,5,7,9,11,100);
SLEECT * FROM cms_user WHERE proId IN(1,5);
SELECT * FROM cms_user WHERE username IN('zhao','qian','sun');

• –匹配字符

–模糊查询：
%：代表0个一个或者多个任意字符；
_：代表1个任意字符；

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

• –查询用户名为3位的记录

SELECT * FROM cms_user WHERE username LIKE '___';

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

• –查询用户名为wang并且密码为tian的用户

SELECT * FROM cms_user WHERE username='wang' AND password='tian';

• –查询编号大于等于3并且年龄不为NULL的用户

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

• –查询编号大于等于3并且年龄不为NULL的用户并且proId等于3

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

• –查询编号在5~10且用户名为4位的用户

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

• –查询用户名以张开头，或者是用户所在省份为2,4的记录

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

• –分组 GROUP BY

SELECT * FROM cms_user GROUP BY proId;
SELECT * FROM cms_user GROUP BY sex;

• –按照多个字段分组；先按照proId分，在组内按照sex分

SELECT * FROM cms_user GROUP BY proId,sex;

• –GROUP BY配合GROUP_CONCAT()得到分组详情
• –查询id，sex，用户名详情，按照性别分组

SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;

• –查询proId，性别详情，注册时间详情，用户名详情，按照proId分组

SELECT proId,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime) FROM cms_user GROUP BY proId;

• –GROUP BY配合聚合函数COUNT() MAX() MIN() AVG() SUM()
• –查询编号，sex，用户名详情以及组中总人数按照sex分组

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

• –统计表中所有记录

SEELCT COUNT(*) AS totalUsers FROM cms_user;
SEELCT COUNT(id) AS totalUsers FROM cms_user;

• –COUNT()函数不统计NULL值

SELECT COUNT(age) AS totalUser 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 ave_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY sex;

• –GROUP BY配合 WITH ROLLUP 记录上面所有记录的总和

SELECT id,sex,
GROUP_CONCAT(username),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS ave_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY sex WITH ROLLUP;

• –HAVING 子句对分组结果进行二次筛选
• –查询性别，用户名详情组中总人数，最大年龄，年龄总和，按照性别分组

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

• –查询组中人数大于2的；对上一条sql结果进行二次筛选

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

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

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

• –ORDER BY 对查询结果进行排序

  DESC 降序，ASC 升序

SELECT * FROM cms_user ORDER BY id DESC;
SELECT * FROM cms_user ORDER BY age ASC;

• –按照年龄升序，id降序排列

SELECT * FROM cms_user ORDER BY age ASC,id DESC;

• –取随机数

SELECT * FROM cms_user ORDER BY RAND();

• –LIMIT 限制查询结果显示条数

  LIMIT 显示条数
  LIMIT 偏移量,显示条数 #实现分页显示

SELECT * FROM cms_user LIMIT 5;

SELECT * FROM cms_user LIMIT 1;
SELECT * FROM cms_user LIMIT 0,1;

• –总结查询sql

SELECT id,sex,age,GROUP_CONCAT(username),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVE(age) AS ave_age,
SUM(age) AS sum_age
FROM cms_user
WHERE id >= 1
GROUP BY sex
HAVING COUNT(*) >= 2
ORDER BY age DESC
LIMIT 0,3;

联合查询

1. UNION
2. UNION ALL
   区别是 UNION 去掉相同记录，UNION ALL 是简单的合并到一起

SELECT username FROM employee;
SELECT username	FROM cms_user;

• –整合上面两个查询结果，可以使用联合查询：

SELECT username FROM employee UNION SELECT username FROM cms_user;
SELECT username FROM employee UNION ALL SELECT username FROM cms_user;

• –子查询：子查询是将一个查询语句嵌套在另一个查询语句中，内层查询语句的查询结果，可以作为外层查询语句的条件
• –由 [NOT] IN 的子查询

SELECT id,usename FROM employee WHERE departId IN (1,2,3,4);
SELECT id,usename FROM employee WHERE departId IN (SELECT id FROM department);
SELECT id,usename FROM employee WHERE departId NOT IN (SELECT id FROM department);

• –使用运算比较符的子查询 =,<,>,<=,>=,<>,!=,<=>

• –使用 [NOT]EXISTS 的子查询

• –使用 ANY|SOME|ALL 配合运算比较符的子查询

运算法	ANY	SOME	ALL
>,>=	最小值	最大值	最大值
<,<=	最大值	最大值	最小值
=	任意值	任意值
<>,!=			任意值

• –建表：学员表，奖学金表

CREATE TABLE IF NOT EXISTS student(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
usename VARCHAR(20) NOT NULL UNSIGNED,
score TINYINT UNSIGNED
);
INSERT INTO student(username,score) VALUES ('wang',10),
('zhao',10),
('qian',10),
('chen',10),
('cui',10),
('he',10),
('tian',10),
('sun',10);

CREATE TABLE IF NOT EXISTS scholarship(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
level TINYINT UNSIGNED
);
INSERT scholarship(level) VALUES (70),(80),(90);

• –查询所有获得奖学金的学员

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

• –将子查询的结果写入到数据表

INSERT [INTO] tnl_name [(col_name,...)] SELECT ...

• –创建数据表的同时将查询结果写入到数据表

CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] select_statement

正则表达式查询

• –使用方式：

REGEXP '匹配方式'

• –常用匹配方式

模式字符	含义
^	匹配字符开始的部分
$	匹配字符串结尾的部分
.	代表字符串的任意一个字符，包括回车和换行
[字符集合]	匹配字符集合中的任意一个字符
[^字符集合]	匹配除了字符集合以外的任何一个字符串
S1	S2
*	代表0个1个或者多个其前的字符
+	代表0个或者多个其前的字符
String{N}	字符串出现N次
字符串{M,N}	字符串至少出现M次，最多N次

• –练习，查询用户表中用户名以t开头的记录

SELECT * FROM cms_user WHERE username REGEXP '^t';