MySQL语法2

DQL语句介绍

DQL是数据查询语言，用来查询数据库中表的记录

DQL-基本查询语句

 SELECT

字段列表

 FROM

表名列表

 WHERE

条件列表

 GROUP BY

分组字段列表

 HAVIMG

分组后条件列表

 ORDER BY

排列字段列表

 LIMIT

分页参数

讲解过程：基本查询、条件查询、聚合函数、分组查询、排序查询、分页查询

DQL-基本查询

1. 查询多个字段

SELECT 字段1,字段2,字段3,... FROM表名;

SELECT name,wordno

FROM emp;

SELECT * FROM 表名;（查询返回所有字段）

SELECT *

FROM emp

1. 设置别名

设置别名SELECT 字段1[AS 别名],字段2[AS 别名]。。。FROM 表名;

SELECT entrydate as '入职日期'

FROM emp;

1. 去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

SELECT DISTINCT entrydate as '入职日期'

FROM emp;

DQL-条件查询

1.语法

SELECT * FROM 表名;（查询返回所有字段）

1. 条件

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<>或！=	不等于
BETWEEN...AND...	在某个范围之类（含最小值、最大值）
IN(...)	在in之后的列表中的值、多选一
LIKE占位符	模糊匹配（_匹配单个字符，%匹配日任意个字符）
IS NULL	是NULL

逻辑运算符	功能
AND或 &&	并且（多个条件同时成立）
OR或||	或者（多个条件任意一个成立）
NOT或！	非，不是

实例代码：

查询年龄小于20的员工姓名：

SELECT name
FROM emp
WHERE age<20;

查询年龄小于等于20 的员工姓名：

SELECT *
FROM emp
WHERE age<=20;

查询身份证号为空的员工：

SELECT *
FROM emp
WHERE idcard is null;

查询15到20岁之间的员工（不包含）：

SELECT *

FROM emp

WHERE 15<age AND age<20;（开区间）

查询15到20岁之间的员工（包含）SELECT *

FROM emp

WHERE age BETWEEN 15 AND 20（含最小值、最大值）

查询15岁、20岁、23岁的员工SELECT * FROM emp WHERE age=18 || age = 20 || age = 23; 与IN（）替换效果相同：SELECT *  FROM emp  WHERE age IN(18,20,23);

查询姓名为三个字的员工信息：

SELECT *

FROM emp

WHERE name like '___';

查询身份证号最后一位为6的员工信息：

SELECT *

FROM emp

WHERE idcard like '%6';

LIKE占位符：模糊匹配（_匹配单个字符，%匹配日任意个字符）

DQL-聚合函数

1. 聚合函数：将一列数据作为一个整体，进行纵向计算。
2. 常见聚合函数：

函数	功能
Count	统计数量
Max	最大值
Min	最小值
Avg	平均值
Sum	求和

1. 语法：

SELECT 聚合函数（字段列表） FROM 表名;

实例：

求员工总人数：

SELECT COUNT(*) as'总人数'

FROM emp;

求员工平均年龄：

SELECT avg(age) AS '员工平均年龄'

FROM emp;

求员工中最大年龄：

SELECT max(age) as '最大年龄'

FROM emp;

求员工中最小年龄：

SELECT min(age) AS '最小年纪'

FROM emp;

求员工中男生年龄总和：

SELECT sum(age) AS '男生年龄之和'

FROM emp

WHERE gender = '男';

DQL-分组查询

1. 语法：

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件]；

实例：

按照性别分组，显示各个性别人数；

SELECT gender,COUNT(*) as '人数'

FROM emp

GROUP BY gender;

按照性别分组，显示各个性别平均年龄；

SELECT gender ,avg(age) as '平均年龄'

FROM emp

GROUP BY gender;

筛选年龄30以下，按照年龄分组，展示人数大于等于2的：

SELECT age , count(*)

FROM emp

WHERE age <30

GROUP BY age

HAVING count(*)>=2;

1. WHERE和HAVING区别

>执行时机不同：WHERE是分组之前进行过滤，不满足WHERE条件，不参与分组：而having是分组之后对对结果进行过滤。

>判断条件不同：WHERE不能对聚合函数进行判断，而HAVING可以。

注意：

1. 执行顺序：WHERE>聚合函数>HAVING
2. 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

DQL-排序字段列表

1. 语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2；

1. 排序方式：

>ASC:升序（默认值）

>DESC:降序

实例：

按员工年龄大小正序排列

SELECT *

FROM emp

ORDER BY age ;

按入职时间长短排序

SELECT *

FROM emp

ORDER BY entrydate;

按年龄大小排序，相同年龄按入职时间倒叙排列

SELECT *

FROM emp

ORDER BY age ,entrydate DESC;

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

DQL-分页查询（方言：不同数据库语法不同）

1. 语法

SELECT 字段列表 FROM 表名LIMIT 起始索引，查询记录数；

实例：

查询第一页，每页展示3条数据

SELECT *

FROM emp

LIMIT 0,3;

查询第二页，每页展示3条数据

SELECT *

FROM emp

LIMIT 3,3;

查询第三页，每页展示3条数据

SELECT *

FROM emp

LIMIT 3,3;

注意：

起始索引从0开始，起始索引= (查询页码 1)每页显示记录数

分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT

如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

DQL-练习：

查询性别为男，年龄为20，21，23的员工；

SELECT *

FROM emp

WHERE gender = '男' AND age IN (20,21,23);

查询年纪20到40之间，姓名为三个字的男生；

SELECT *

FROM emp

WHERE gender= '男'AND name like '___' AND age BETWEEN 20 and 40;

查询年龄小于60，的男女数量；

SELECT gender,count(*)

FROM emp

WHERE age <60

GROUP BY gender;

查询所有小于等于35岁员工的姓名和年纪，并对结果按年龄升序排序，如果年纪相同按入职时间降序排列；

SELECT *

FROM emp

WHERE age <= 35

ORDER BY age ASC, entrydate DESC;

查询性别男，且年龄在20-40岁（含）以内的五个员工，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。

SELECT *

FROM emp

WHERE age BETWEEN 20 and 40 AND gender = '男'

ORDER BY age ,entrydate

LIMIT 0,5;

DQL语句-执行顺序

 

 

MySQL-DCL语句

介绍：DCL数据控制语言，用来管理数据库用户、控制数据库的访问权限。

DCL-管理用户：

1. 1.查询用户表

  USE mysql ;

  SELECT * FROM user;

1. 创建用户：

CREATE USER ‘用户名’@’主机名’ IDENTIFED BY ‘密码’；

1. 修改用户密码：

 ALTER USER ‘用户名’@’主机名’ IDEBTIFIED WITH mysql_native_passwoerd BY ‘新密码’；

1. 删除用户：

DROP USER ‘用户名’@’主机名’;

查询用户表：

USE mysql;

SELECT * FROM user;

创建用户：

CREATE user 'GUI1'@'%' identified by '123456';

修改用户密码：

ALTER USER 'GUI1'@'%' identified with mysql_native_password by '1234';

删除用户：

drop user 'GUI1'@'%';

DROP USER 'GUI'@'localhoust';

注意：

主机名可以使用%统配。

这类SQL开发人员操作的比较少，主要DBA数据库管理员使用；

DCL-权限控制

权限	说明
ALL,ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

其他权限描述及其含义，可以参考官方文档

1. 查询权限：

SHOW GRANTS FOR ‘用户名’@’主机名’;

1. 授予权限：

GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@’主机名’;

GRANT ALL on sjk01.* TO 'GUI1'@'%';

1. 撤销权限：

REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@’主机名’；

revoke all on sjk01.* from 'GUI1'@'%';

查询权限：

SHOW GRANTS FOR GUI1;

授予权限

GRANT ALL on sjk01.* TO 'GUI1'@'%';

撤销权限：

revoke all on sjk01.* from 'GUI1'@'%';