首页 > 代码库 > MySQL查询数据操作(DQL)

MySQL查询数据操作(DQL)

查询记录

单表查询SELECT语句完整形式:

SELECT select_expr [ , select_expr ... ]

[

FROM tbl_references

[WHERE 条件]

[GROUP BY {col_name | position} [ASC | DESC], ... 分组]

[HAVING 条件 对分组结果进行二次筛选]

[ORDER BY {col_name | position} [ASC | DESC], ...排序]

[LIMIT 限制显示条数]

]


查询表达式(select_expr)

1. 每一个表达式表示想要查询的一列,至少有一列,多个列之间以逗号分隔

2. *表示所有列,tbl_name.*可以表示命名的所有列

3. [AS] alias_name 为其赋予别名

SELECT id, username, role FROM cms.cms_admin;

给字段起别名

例如:SELECT id AS ‘编号‘,username AS ‘用户名‘,email AS ‘邮箱‘ FROM 表名; 查询完后别名代替原名显示。


WHERE条件

1. 比较 = < <= > >= != <> !

2. BETWEEN AND 、NOT BETEEN AND 指定范围

IN、NOT IN 指定集合


3. 模糊查询 LIKE

% :代表0个 1个 或多个任意字符

_ :代表1个任意字符

例如查询姓王的用户:

SELECT * FROM user WHERE username LIKE ‘王%‘;

查询名字中含有in的用户:

SELECT * FROM user WHERE username LIKE ‘%in%‘;

查询名字长度为4的用户:

SELECT * FROM user WHERE username LIKE ‘_ _ _ _‘;

查询名字第二位是i的用户:

SELECT* FROM user WHERE username LIKE ‘_i%‘;


当没有使用%或_时 LIKE相当于等号,精准查询。


4. 是否为空值IS NULL、IS NOT NULL

5. 多个查询条件 AND  OR

例如查询用户名为jack,密码为123456的用户:

SELECT * FROM user WHERE username=‘jack‘ AND password=‘123456‘;

查询id大于等于3且年龄不为NULL的用户:

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

查询id大于等于3,年龄不为NULL,proId等于3的用户:

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

查询编号在5~10之间的,用户名为4位的用户:

SELECT * FROM user WHERE id BETWEEN 5 AND 10 AND username LIKE ‘____‘;

查询用户名以张开头或者id为2或4的用户

SELECT * FROM user WHERE username LIKE ‘张%‘ OR id IN(2, 4);


分组查询GROUP BY

只会显示各个组中的第一条记录。

按照用户所在省份分组proId:

SELECT * FROM user GROUP BY proId;

按照性别分组:

SELECT * FROM user GROUP BY sex;

按照字段位置分组:

SELECT * FROM user GROUP BY 7;(按照第7个位置的字段分组)

按照多个字段分组:

SELECT * FROM user GROUP BY sex,proId;(先按照sex分组,在sex中又以proId来分组。 )

通过WHERE条件筛选,再把结果分组

例如查询id大于等于5的用户按照sex分组:

SELECT * FROM user WHERE id>=5 GROUP BY sex;


GROUP_CONCAT(字段名称)     得到某个字段详情

GROUP BY配合GROUP_CONCAT()函数得到分组详情

查询id,sex,用户详情,按照sex分组

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

查询proId、sex、regTime、username详情,按proId分组、

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


聚合函数

COUNT()      统计某个字段的总数

MAX()          最大值

MIN()           最小值

AVG()           平均值

SUM()          和

注意:COUNT(字段)不统计NULL值。

例如查询id、sex、username详情、组中总人数,并按sex分组:

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

统计表中所有记录总和:

SELECT COUNT(*) AS totalUsers FROM user;

查询编号、性别、用户名详情,组中总人数,组中最大年龄,最小年龄,平均年龄,年龄总和,按性别分组:

SELECT id,sex,GROUP_CONCAT(username),

COUNT(*) AS total_users,

MAX(age) AS max_age,

MIN(age) AS min_age,

AVG(age) AS avg_age,

SUM(age) AS sum_age

FROM user

GROUP BY sex;


WITH ROLLUP聚合统计上面所有记录

使用HAVING子句对分组结果进行二次筛选

WHERE是对记录第一次筛选,HAVING子句是对分组结果的二次筛选

注意:HAVING子句只能配合分组使用才有意义,放在分组之后

例如查询id>=2、sex、用户名详情、组中总人数、最大年龄、年龄总和:

SELECT id,sex,GROUP_CONCAT(username) AS users,

COUNT(*) AS totalUsers,

MAX(age) AS max_age,

SUM(age) AS sum_age

FROM user

--id大于等于2

WHERE id >=2

GROUP BY sex

-- 继续筛选人数大于2的组并且最大年龄大于60

HAVING COUNT(*)>2  AND MAX(age)>60;


对查询结果进行排序  ORDER BY

例如:按照id升序排序

SELECT * FROM user ORDER BY id ASC;

按照id降序排序

SELECT * FROM user ORDER BY id DESC;

按多字段排序

例如:按年龄升序,id降序排列

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

对结果随机排序

SELECT * FROM user ORDER BY RAND();


限制查询结果显示条数 LIMIT

1.LIMIT 显示条数

2.LIMIT 偏移量,显示条数

查询结果集 前三条记录:

SELECT * FROM user LIMIT 3;

SELECT * FROM user ORDER BY id DESC LIMIT 3;

查询表中前一条记录:

SELECT * FROM user LIMIT 1;

实现分页,偏移量+每页显示记录数

SELECT * FROM user LIMIT 0,5;  

SELECT * FROM user LIMIT 5,5;  

SELECT * FROM user LIMIT 10,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

FROM user

WHERE id>=1  根据条件第一次筛选

GROUP BY sex  对筛选结果分组

HAVING COUNT(*)>=2 对查询结果二次筛选

ORDER BY age DESC  筛选结果按照age字段排序

LIMIT 0,2;  限制显示条数


本文出自 “zgw285763054” 博客,请务必保留此出处http://zgw285763054.blog.51cto.com/11591804/1879420

MySQL查询数据操作(DQL)