Mysql6

查询数据操作DQL
24、查询表达式
查询记录：SELECT select_expr [,select_expr …]
[
FROM table_references
[WHERE 条件］
[GROUP BY {col_name | positon} [ASC | DESC], … 分组]
[HAVING 条件 对分组结果进行二次筛选]
[ORDER BY {col_name | position} [ASC | DESC], … 排序]
[LIMIT 限制显示条数]
]
查询表达式：
每个表达式表示想要的一列，必须至少有一列，多个列之间以逗号分隔–
*表示所有列，tbl_name.*可以表示命名表的所有列
查询表达式可以使用[AS]alias_name为其赋予别名
– 查询
SELECT * FROM cms_admin;
SELECT cms_admin. * FROM cms_admin;

– 查询管理员编号和名称
SELECT id, username FROM cms_admin;
SELECT username,id,role FROM cms_admin;

–表来自哪个数据库下 db_name.tbl_name
SELECT id,username,role FROM cms.cms_admin;
SELECT id,username FROM 库名.表名;

– 字段来自哪张表
SELECT cms_admin.id,cms_admin.username FROM cms.cms_admin;

–给表名起别名
SELECT id,username FROM cms_admin AS a;
SELECT id,username FROM cms_admin a; —省略AS一样
SELECT a.id,a.username,a.email,a.role FROM cms_admin AS a;

–给字段起别名
SELECT id AS ‘编号’,username AS ‘用户名’,email AS ‘邮箱’,role ‘角色’ FROM cms_admin;
SELECT a.id AS id1,a.username AS u,a.email AS e,a.role AS r FROM cms_admin AS a;
SELECT id proId ,proId id,username FROM cms_user; —可读性比较差
SELECT 1,2,3,4,5,id,username FROM cms_user;

25、待条件的查询
WHERE条件：
比较 =，<，<=，>，>=，!=， <>，!>，!<，<=>
– WHERE条件
– 查询编号为1的用户
SELECT id,username,email FROM cms_user WHERE id=1;
SELECT id,username,email FROM cms_user WHERE username=‘king’;
SELECT * FROM cms_user WHERE id>=5;
–查询编号不为1的用户
SELECT * FROM cms_user WHERE id!=1;
SELECT * FROM cms_user WHERE id<>1;

–添加age字段
ALTER TABLE cms_user ADD age TINYINT UNSIGNED DEFAULT 18;
INSERT cms_user(username,password,regTime,proId,age)
VALUES(‘test1’,‘test1’,1419811708,1,NULL0;

–查询表中记录age值为NULL
SELECT * FROM cms_user WHERE age=NULL; —查不出来
SELECT * FROM cms_user WHERE age<=>NULL; —可以；<=>就是等号，还可以用于检测NULL

–IS NULL
SELECT * FROM cms_user WHERE age IS NULL;

26、范围查询
WHERE条件：
指定范围：BETWEEN AND，NOT BETWEEN AND
– 查询编号在3-10之间的用户
SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10;
SELECT * FROM cms_user WHERE id NOT BETWEEN 3 AND 10;
指定集合： IN ，NOT IN（固定值）
–查询编号为1，3，5，7，9，11，13，100的用户
SELECT * FROM cms_user WHERE id IN(1,3,5,7,9,11,13,100);
–查询proId为1和3的用户
SELECT * FROM cms_user WHERE proId IN(1,3);
–查询用户名为king，queen，张三，章子怡的记录
SELECT * FROM cms_user WHERE username IN（‘king’,‘queen’,‘张三’,‘章子怡’);
SELECT * FROM cms_user WHERE username IN（‘KING’,‘QUEEN’,‘张三’,‘章子怡’);–大小写无所谓
SELECT * FROM cms_user WHERE username NOT IN（‘king’,‘queen’,‘张三’,‘章子怡’);

27、模糊查询
WHERE条件：
匹配字符：LIKE，NOT LIKE
–模糊查询
–%：代表0个一个或多个任意字符
–_：代表1个任意字符
–查询姓张的用户
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 ‘___’; —三个下划线

–查询用户名_i%（i前面忘了，后面有没有无所谓）
SELECT * FROM cms_user WHERE username LIKE ‘_i%’;
SELECT * FROM cms_user WHERE username LIKE ‘king’; —没有下划线和%，like等于=，相当于精准查询
SELECT * FROM cms_user WHERE username NOT LIKE ‘_i%’;
SELECT * FROM cms_user WHERE username LIKE ‘张%’ OR proId IN(2,4);

28、分组查询
–按照用户所属省份分组proId
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 cms_user SET sex=‘女’ WHERE id IN(2,4,6,8,10);
UPDATE cms_user SET sex=‘女’ WHERE id IN(2,4,6,8,10);
UPDATE cms_user SET sex=‘保密’ WHERE id IN(12,11);

–按照用户性别分组
SELECT * FROM cms_user GROUP BY sex;

–按照字段位置分组
SELECT * FROM cms_user GROUP BY 7;

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

–查询编号大于等于5的用户按照sex分组
SELECT * FROM cms_user WHERE id>=5 GROUP BY sex;

29、分组查询配合聚合函数
–查询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\G;
GROUP BY 查询结果分组
配合GROUP_CONCAT()得到分组详情
配合聚合函数：COUNT(),MAX(),MIN(),AVG(),SUM()
配合WITH ROLLUP记录上面所有记录的总和
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，用户名详情以及组中总人数按照sex分组
SELECT id,sex,GROUP_CONCAT(username)AS users,COUNT(*) AS totalUsers FROM cms_user GROUP BY sex;

–统计表中所有记录
SELECT COUNT(*) AS totalUsers FROM cms_user;
SELECT COUNT(id) AS totalUsers FROM cms_user;

–COUNT(字段)不统计NULL值
SELECT COUNT(age) 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;

–WITH GROUP
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 WITH ROLLIP;

30、having语句对分组结果进行二次筛选
HAVING子句：通过HAVING子句对分组结果进行二次筛选
–查询性别sex，用户名详情，组中总人数，最大年龄，年龄总和,根据性别分组
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的
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;

–查询组中人数大于2并且最大年龄大于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;