查询数据操作（DQL）

-- 查询表中的所有记录
SELECT * FROM cms_admin;*代表所有列
SELECT cms_admin.* FROM cms_admin;#可以用表名.字段的形式告诉要查询哪个表的字段
-- 查询管理员的编号，名称
SELECT id,username FROM cms_admin;
SELECT username,id,role FROM cms_admin; #显示的顺序跟SELECT字段的顺序一样
-- 表来自哪个数据库，采用db_name.tb_name的形式
SELECT id,username,role FROM cms.cms_admin;
SELECT id,username FROM maizi.user1;
-- 字段来自于哪张表
SELECT cms_admin.id,cms_admin.username FROM cms.cms_admin;
-- 给表名起别名
SELECT id,username FROM cms_admin as a;#一般别名为表名的缩写，最多不要超过两个字母，这里的as可以省略,最好不要省略
SELECT id,username FROM cms_admin a;
SELECT a.id,a.username,a.role FROM cms_admin AS a;
-- 给字段起别名
SELECT id AS '编号',username AS '用户名',email AS '邮箱' FROM cms_admin;#这时查询结果显示的就是别名
SELECT a.id AS i,a.username as u,a.role AS r FROM cms_admin AS a;
SELECT 1,2,3,4,5,id,username FROM cms_user;

WHERE条件如下：

WHERE条件

比较查询

-- 查询编号为1的用户
SELECT id,username,email FROM cms_user WHERE id=1;#条件不满足，查询为空
-- 查询编号大于5 的用户
SELECT * FROM cms_user WHERE id>5;
-- 查询编号不为1的用户
SELECT * FROM cms_user WHERE id!=1;# '<>'也是不等于
-- cms_user没有age字段，添加age字段
ALTER TABLE cms_user ADD age TINYINT UNSIGNED DEFAULT 18;#之前表里的每个记录的age都成了18
INSERT cms_user (username,password,regTime,proig,age) VALUES('test1','test1',111111,1,NULL);
SELECT * FROM cms_user WHERE age=NULL;#此时查询不出来
SELECT * FROM cms_user WHERE age<=>NULL;#用这个等号就可以查询出来，'<=>'就比'='多一个查询NULL值的功能
-- 用IS NULL 或者IS NOT NULL 检测空值
SELECT * FROM cms_user WHERE age IS NULL;
SELECT * FROM cms_user WHERE age IS NOT NULL;

范围查询

-- 查询编号在3~10之间的用户
SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10;
SELECT * FROM cms_user WHERE id NOT BETWEEN 3 AND 10;#取反
-- 指定集合
-- 查询编号为1，3，5，7，9，11，13，100
SELECT * FROM cms_user WHERE  id IN(1,3,5,7,9,11,13,100,1000);
-- 查询proId 为1和3的用户
SELECT * FROM cms_user WHERE  proId IN(1,3);
SELECT * FROM cms_user WHERE username IN('KinG','QUEEN','章子怡');#不区分大小写，不管大写还是小写都可以查出来

模糊查询

-- 模糊查询
-- %：代表0个一个或者多个任意字符
-- _:代表一个任意字符
-- 查询姓张的用户
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%
SELECT * FROM cms_user WHERE username LIKE '_i%';
SELECT * FROM cms_user WHERE username LIKE 'KING';

逻辑运算符

-- 查询用户名为‘KING’并且密码为KING的用户
SELECT * FROM cms_user WHERE username='king' AND password='king';
-- 查询编号大于等于3的并且年龄不为NULL的用户
SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL;
-- 查询编号在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);

分组查询

-- 按照用户所属省份来分组
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(11,12);
-- 按照性别来分组
SELECT * FROM cms_user GROUP BY sex;
-- 按照多个字段分组
SELECT * FROM cms_user GROUP BY sex,proId;
-- 查询编号大于等于5的用户并且按照性别分组
SELECT * FROM cms_userWHERE id>5 GROUP BY sex;

分组查询配合聚合函数

-- 查询id,sex,用户详情按照sex分组
SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;
-- 查询proId,性别详情，注册时间详情，用户名详情，按照proId分组
SELECT proId,GROUP_CONCAT(sex),GROUP_CONCAT(regTime),GROUP_CONCAT(username) FROM cms_user GROUP BY proId;
SELECT proId,GROUP_CONCAT(sex),GROUP_CONCAT(regTime),GROUP_CONCAT(username) FROM cms_user GROUP BY proId\G;# '\G'为了使查询结果立体显示 

UPDATE cms_user SET age=11 WHERE id=1;
-- 查询编号，性别，用户名详情以及组中人数，按照性别分组
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 ROLLUP
SELECT id,sex,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age
FROM cms_user
GROUP BY sex WITH ROLLUP; #在末尾多一条记录，得到这一列的结果

HAVING子句进行二次删选

-- having子句进行二次删选 查询组中人数大于2
SELECT id,sex,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age
FROM cms_user
GROUP BY sex
HAVING COUNT(*)>2;
-- 查询组中人数大于2并且最大年龄大于60的
SELECT id,sex,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age
FROM cms_user
GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>60; #having语句一定要配合上GROUP BY才有意义

降序升序排列

-- 按照id降序排列DESC，默认的是ASC
SELECT * FROM cms_user ORDER BY id;#默认是升序
SELECT * FROM cms_user ORDER BY id DESC;
SELECT * FROM cms_user ORDER BY 1 DESC; #按照位置来排序，1代表位置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;
-- 实现随机提取记录
SELECT * FROM cms_user ORDER BY RAND();

LIMIT限制查询结果显示条数

-- LIMIT限制查询结果显示条数
SELECT * FROM cms_user LIMIT 5;
SELECT * FROM cms_user ORDER BY id DESC LIMIT 5; #按照id降序排列，显示前5条
SELECT * FROM cms_user LIMIT 1,1;# 从1开始，显示1条
SELECT * FROM cms_user LIMIT 0,5; #从0开始显示5条
-- 综合写一下查询语句
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
WHERE id>=1
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC
LIMIT 0,2;
-- 更新用户名为4位的用户，让其已有年龄减3
UPDATE cms_user SET age=age-3 WHERE username LIKE '____';
-- 更新前三条记录，让已有年龄加10
UPDATE cms_user SET age=age+10 LIMIT 3;
-- 从零开始，更新前三条记录，让已有年龄加10
UPDATE cms_user SET age=age+10 LIMIT 0,3;
--  按照id降序排列，更新前3条
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;