DQL数据查询语言

DQL数据查询语言

1. 基础查

1.1 查询所有列

select * from 表名
例如：查询student表
select * from student

1.2 指定查询

select 字段 from 表名
例如：查询student 表的名字和年龄
select name,age from student

1.3 AS别名

select 字段 as 别名 from 表名 / select 字段 from 表名 as 别名 (as可省略)
例如：查询student表的名字和年龄
select name 名字, age 年龄 from student / select name as 名字, age as 年龄 from student

1.4去重（复）查询

DISTINCT 用于从表中获取不重复的数据
select distinct 列名 from 表名
例如：查询 student 表中所有的不同年龄
select distinct age from student

2.条件查询

select 字段 from 表名 where 条件
例如：student表中查询age = 18的name
select name from student where age = 18

2.1 算术运算符查询

语法：>（大于）, <（小于）, =（等于）, !=（不等于）, <>（不等于）, >=（大于等于）, <=（小于等于）
例如：student表中查询 age >=20的所有记录
select * from student where age >= 20;

2.2 逻辑运算符查询

语法：and（且）, or（或）, not（非） sql 会首先执行 and 条件，再执行 or 语句。除非加括号
例如：student表中查询 age >=20并且成绩高于50分的所有记录
select * from student where age >= 20 and score > 50

例如：student表中查询 age = 15 或 score = 70 的所有记录
select * from student where age = 15 or score = 70

2.3 正则表达式查询

正则表达式要用regexp
select 字段 from 表名 where 字段 regexp '正则表达式'
例如：student表中查询 name 含有李的所有记录
select * from student where name regexp '李'

查询name字段值包含字母 a~t 以外的字符的所有记录
select * from student where name regexp'[^a-t]'

2.4 模糊查询

select 字段 from 表名 where 字段 like '%数据%'

例如：student表中查询 name 中含有 '张' 的所有记录
select * from student where name like '%张%'
例如：student表中查询 name 中姓 '张' 的所有记录
select * from student where name like '张%'

2.5 范围查询

 in与not in
 select 字段 from 表名 where 字段 in(列表) 或 not in(列表);
 
例如：student表中查询 age 为 (18, 19, 20) 之间的所有记录
select * from student where age in(18, 19, 20)
例如：student表中查询 除了age 为 (18, 19, 20) 之间的所有记录
select * from student where age not in(18, 19, 20)

between … and …：范围连续（包含端点）
select 字段 from 表名 where 字段 between 值1 and 值2
例如：student表中查询 age 为 (18, 19, 20) 之间的所有记录
select * from student where age between 18 and 20

2.6是否非空判断查询

null（为空）  not （非空） 判断是否为空要用is
select 字段 from 表名 where 字段 is null(not null);
例如：student表中查询 age 未填写（为空）的所有记录
select * from student where age is null;

注意：优先级
优先级由高到低的顺序为：小括号 > NOT > 比较运算符 > 逻辑运算符
AND比OR先运算，如果同时出现并希望先算or，需要结合()使用

3.排序查询

select 字段 from 表名 order by 字段 排序方式(升序 asc, 降序 desc)

注意：排序方式
ASC : 升序 ( 默认值 )
DESC: 降序

例如：student表中查询所有记录并按照 age 升序排序
select * from student order by age asc

select 字段 from 表名 order by 字段 排序方式，字段 排序方式;
（当第一个字段相同时，按第二个字段排序顺序来）
例如：student表中查询所有记录并按照 age 升序，当 age 相同时，按score降序排序
select * from student order by age asc,score desc

例如：student表中查询所有记录并按照 age 降序排序
select * from student order by age desc

4. 分页查询LIMIT

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

起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10

例如：student表中查询第三行到第六行的记录，也就是第三行开始查询4条记录
select * from student limit 2, 4

5. 分组查询

select 字段 from 表名 group by 字段 

例如：student表中查询age值和每个age都有多少人
select age ,count(*) from student group by age

注意：
GROUP BY子句必须出现在WHERE子句之后， ORDER BY子句之前

5.1 HAVING

HAVING 语句用于筛选(过滤)分组后的数据

select 字段 from 表名 group by 字段 having 条件
例如：student表中查询age>19值和每个age>19都有多少人
select age ,count(*) from student group by age having age > 19

6. 高级查询

6.1 子查询（嵌套查询）

一个 select 语句中,嵌入另外一个 select 语句, 被嵌入的 select 语句称为子查询语句，外部select语句则称为主查询。
子查询是嵌入到主查询中
子查询是辅助主查询的，要么充当条件,要么充当数据源
子查询是可以独立存在的语句，是一条完整的 select 语句

例如：查询学生平均年龄，再查询大于平均年龄的学生
select * from student where age > (select avg(age) from student)

6.2 连接查询

6.2.1 内连接

INNER JOIN

select 字段 from 表1 inner join 表2 on 表1.字段 = 表2.字段
根据两个表中共有的字段进行匹配，然后将符合条件的合集进行拼接on后面是连接条件，也就是共有字段

select * from a_table a inner join b_table b on a.a_id = b.b_id

6.2.2 左（外）连接

左连接意味着左侧表取所有记录，而右侧表仅取匹配的记录。如果左侧表与右侧表没有匹配的记录，则右侧表将返回NULL值

left join on / left outer join on

select 字段 from 表1 left join 表2 on 连接条件
select * from a_table a left join b_table b on a.a_id = b.b_id

6.2.3 右（外）连接

右连接与左连接相似，只是左侧表和右侧表的角色翻转了

right join on / right outer join on

select 字段 from 表1 right join 表2 on 连接条件
select * from a_table a right join b_table b on a.a_id = b.b_id

6.2.4 全外连接（组合）

在SQL中，外连接还有一种FULL OUTER JOIN（全外连接），但是MySQL不支持。因此，我们可以通过UNION语句来模拟全外连接。

union与union all
union会去重，如果在一个条件中返回了，下一个条件中就算是有也不会返回。union all不会去重， 如果在一个条件中返回了，下一个如果有重复的，也会返回

union /union all

 select id, name, age from s1 where age >18 union select id, name, agefrom s2 where age >18

7 聚合函数

聚合函数是用来做纵向运算的函数：

• COUNT()：统计指定列不为 NULL 的记录行数
• MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
• MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算
• SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为 0
• AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为 0

COUNT：当需要纵向统计时可以使用 COUNT()

查询 emp 表中记录数：
select count(*) as cnt from emp

查询 emp 表中有佣金的人数：
SELECT COUNT(comm) cnt FROM emp;
注意，因为 count()函数中给出的是 comm 列，那么只统计 comm 列非 NULL 的行数。

查询 emp 表中月薪大于 2500 的人数：
SELECT COUNT(*) FROM emp WHERE sal > 2500;

统计月薪与佣金之和大于 2500 元的人数：
SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;
查询有佣金的人数，以及有领导的人数：
SELECT COUNT(comm), COUNT(mgr) FROM emp;

SUM 和 AVG：当需要纵向求和时使用 sum()函数。

查询所有雇员月薪和：
SELECT SUM(sal) FROM emp;

查询所有雇员月薪和，以及所有雇员佣金和：
SELECT SUM(sal), SUM(comm) FROM emp;

查询所有雇员月薪+佣金和：
SELECT SUM(sal+IFNULL(comm,0)) FROM emp;

统计所有员工平均工资：
SELECT SUM(sal), COUNT(sal) FROM emp;
或者
SELECT AVG(sal) FROM emp;

MAX 和 MIN
查询最高工资和最低工资：
SELECT MAX(sal), MIN(sal) FROM emp;