数据查询语言(DQL)

一.基础查询

基础查询： select 查询列表 from 表名;

特点:

1. 在进行查询时要先进入指定的库中：USE 数据库名;
2. 查询列表可以是表中的字段、常量值、表达式、函数······
3. 查询的结果是一个虚拟的表格，实际上数据库里没有这张表

1.查询表中的单个字段

select   `name`  from  employees;

2.查询表中的多个字段

select  name,sex,birthday  from  employees;

3.查询表中所有字段（*代表所有）

select  *from  employees;

注意： 表中的字段可能和关键字重名，可以用着重号(`)标识一下字段，用以区分到底是字段还是关键字。

4.查询常量值

select  100;
select   'john';

注意： (MySQL与java不同，不区分字符和字符串，因此单引号和双引号都可以)

5.查询表达式

select   100%98;

6.查询函数

select  version();

7. 给查询列表起别名

语法： select 查询列表 as 别名(as可以省略) from 表名;

优点：

1. 便于理解
2. 如果要查询的字段有重名的情况，使用别名可以区分开来。

select   `name`  as  姓名   from   student;
select   100%98   取余   from   account;

注意: 如果起的别名包含特殊符号，需要用引号把别名匡起来。
案例: 查询salary，显示结果为out put

slect   salary  as   "out  put"   from  employees;

8.去重（去除重复的数据），在select后加distinct

案例： 查询员工表中涉及到的部门编号

select  distinct  department_id from  employees;

9.+号的作用

java中的+号：

1. 充当运算符，两个操作数都为数值型。
2. 充当连接符，只要有一个操作数为字符串。

MySQL中的+号：

• 仅仅只有一个功能，充当运算符。没有拼接字符串的功能。

特点：

1. 两个操作数都为数值型，则作加法运算

select  100+90; --  结果为190

2. 其中一方为字符型，则试图将字符型数值转换成数值型再运算

select  ’123‘+90;  --  结果为213

3. 如果转换失败，则将字符型数值转换成0

select  'john' + 90;  - -  结果为90

4. 如果任意其中一方为null，则结果肯定为null，类似于java中的与&&运算

select   null +10; - -  结果为null

10.concat拼接函数，用于拼接字符串

语法： select concat（‘a’,‘b’,‘c’）as 别名 from 表名;

案例： 查询员工名和姓连接成一个字段，显示为姓名

select  concat(last_name,first_name)  as  姓名    from  employees;

11.ifnull函数

语法：select ifnull(字段,指定值) from 表名;

功能： 判断某字段是否为null，如果为null，返回指定的值，否则返回原本的值。

select    ifnull(commission_pct,0) from  employees;

解释： 查询员工表中员工的奖金率，如果有员工奖金率为null值，则返回0;
不为null值则返回原本得值。

12. isnull函数

语法:select isnull(字段) from 表名;

功能： 判断某字段或表达式是否为null，如果是则返回1，否则返回0。

select   isnull(commission_pct),commission_pct  from   employees;

二.条件查询

语法： select 查询列表 from 表名 where 筛选条件;

筛选条件的分类：

按条件表达式筛选

条件运算符：> < = != <> >= <=
案例1： 查询工资>12000的员工信息

select   *from  employees
where salary>12000;

案例2： 查询部门编号不等于90号的员工名和部门编号

select  last_name  员工名,department_id 部门编号
from employees
where department_id <>90;--  这里使用!=也行

按逻辑表达式筛选

逻辑运算符：
java中：&&(与) ||(或) !(非)
MySQL中：and or not
案例1： 查询工资在10000到20000之间的员工名、工资以及奖金

select last_name 员工名,salary  工资,commision_pct  奖金
from employees
where  
salary >=10000 and salary<=20000;

案例2： 查询部门编号不是在90到110之间,或者工资高于15000的员工信息

select *from employees
where   department_id  <90  or department_id>110  or  salary>15000;

或者not(department_id>=90 and department_id<=110) or salary>15000;

模糊查询

1.like

特点： 一般和通配符搭配使用。

通配符：
%，代表任意多个字符，包含0个
_ ，任意单个字符
案例1： 查询员工名包含字符a的员工信息

select *from employees
where  last_name  like '%a%';

案例2： 查询员工名中第三个字符为e，第五个字符为a的员工名和工资

select last_name,salary from employees
where last_name  like '_ _e_ a%';

案例3： 查询员工名中第二个字符为_的员工名

select last_name fromemployees
where last_name  like '_ $_%'  ESCAPE  ‘$’;

注意： 需要用转义字符来声明一下后面的_是字符而不是通配符，通过ESCAPE '转义字符’来声明，转义字符自己可以随便设。

2. between···and···

特点：

1. 使用between···and···可以提高语句的简洁度（相比条件运算符>=、<=）
2. 包含两个临界值，闭区间 [ 下限,上限 ]
3. 两个临界值不能调换顺序

案例1： 查询员工编号在100到200之间的员工信息

select * from employees
where employee_id  between  100  and  200;

等于 employee_id  >=100 and  employee_id <=120;

3.in

含义： 用于判断某字段的值是否属于in列表中的某一项
特点：

1. 使用in可以提高语句的简洁度（相比逻辑运算符or）
2. in列表中的数据类型必须一致或兼容
3. in列表中的数据不能用到通配符，虽然in和like都是模糊查询，但通配符是like的专属

案例1： 查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个的员工名和工种编号

select last_name,job_id from employees
where job_id  in (‘IT_PROG’,'AD_VP','AD_PRES');

或者job_id ='IT_PROG'  or  job_id ='AD_VP'  or  job_id ='AD_PRES';  

4. is null 或者is not null

= <> 和is (not) null 的区别：

1. =或者<>不能用于判断null值，只能判断数值。
2. is (not) null 可以判断null值，但不能判断数值。

错误示范：

where salary =null;
where salary is 12000;

正确示范：

where job_id <> ‘IT_PROG’;
where salary is null;

案例1： 查询没有奖金的员工名和奖金率

select last_name,commission_pct from comployees
where commission_pct   is   null;

案例2： 查询有奖金的员工名和奖金率

select last_name, commission_pct from comployees
where commission_pct   is  not  null;

5.安全等于<=>,判断是否等于，如果等于返回true。

案例1： 查询没有奖金的员工名和奖金率
<=>和is null的区别：

• is null仅仅可以判断null值，可读性较高，建议使用is null
• <=> 既可以判断null值，也可以判断普通的数值，可读性较低

select last_name, commission_pct
from comployees
where commission_pct   <=>   null;

案例2： 查询工资为12000的员工信息

select *from employees
where salary <=> 12000;

三.排序查询

语法：
select 查询列表 from 表名
【where 筛选条件】 – 中括号代表可选项
order by 排序列表 asc | desc
group by 列名
having

特点： asc代表升序，desc代表降序，如果什么都不写，默认是升序。
案例1： 查询员工信息，要就工资从高到低排序

select    *from   employees
order  by  salary   desc;

案例2： 查询部门编号>=90的员工信息，按入职时间的先后顺序进行排序

select  *from  employees
where dapartment_id >=90
order by hiredate;

案例3： 按年薪的高低显示员工的信息和年薪【按表达式排序】

select   * , salary*12*(1+ IFNULL(commission_pct,0) 年薪
from   employees
order  by  salary*12*(1+ IFNULL(commission_pct,0) desc;

案例4： 按年薪的高低显示员工的信息和年薪【按别名排序】

select   * , salary*12*(1+ IFNULL(commission_pct,0) 年薪
from   employees
order  by  年薪  desc;

四.聚合函数查询

函数	功能
count( )	统计行数
sum( )	针对数值列求和
avg( )	针对数值列求平均值
max( )	求最大值
min( )	求最小值

案例1： 统计表里面总共有多少行数据

select count(*) from salary;

案例2： 统计表里面总共有多少行数据,自动剔除数据为null情况

SELECT count(sal) from salary;

注意： count统计某一字段的行数时，不会统计数据为null的所在行，会自动剔除数据为null的行。

案例3： 计算所有工资的和

SELECT sum(sal) from salary;

案例4： 求所有工资的平均值 自动排除值为null的情况

select avg(sal) from salary;

案例5： 求工资的最大值

SELECT max(sal) from salary;

案例6： 求工资最小值

SELECT min(sal) from salary;

五.分组查询

group by …

– 将需要分组的列值进行搜集相同的，放入一个组进行聚合函数统计。

案例1： 按学号分组，统计各学生的选修记录数。

select studentid 学号,count(*) 选修课程数 
from score
group by studentid

案例2： 按多列同时分组 [将select列表中所有非聚合函数列，全部放到group by子句中]

select studentid 学号,cno 课程号,count(*) 选修课程数 ,sum(grade) 选修成绩,avg(grade) 平均选修成绩
from score
group by studentid,cno

案例3： 查询学生表中 按性别分组的人数统计

select gender 性别,count(*) 人数
from student
where gender=0  -- 没分组，原始数据条件筛选
group by gender

案例4： 查询学生表中 按班级分组的人数统计

select class 班级号,count(*) 人数
from student
group by class

having …

在分组之后如果还需要进行条件筛选，则使用having 作为条件。 having 子句后面跟聚合函数。
注意事项：

1. having必须是在group by 后面，前提是有having时，必有group by ,有group by 不一定有having。
2. having条件必须是带聚合函数的条件判断。
   案例： 查询平均分大于80的学生学号，总分，平均分，选修课程数。

select studentid as 学号,sum(grade) 总分,avg(grade) 平均分,count(grade) 选修课程数
from score
group by studentid
having avg(grade)>=80  -- 在分组后进一步进行条件筛选

六.分页查询

分页查询也称为返回限制行。

limit n	从第一行开始查询，查询n行
limit m,n	从第m+1行开始查询，查询n行

案例1： 查询成绩表中前三行的所有信息

select * 
from score
order by grade desc
limit 3  --表示返回限制行数，返回前三行

案例2： 查询成绩表中的第4,5,6行。

select * 
from score
order by grade desc
limit 3,3
-- 第一个参数 表示记录索引号 从0开始,起始的索引号
-- 第二个参数 表示记录数

连接查询

也可以叫跨表查询，需要关联多个表进行查询，一般进行连接查询的表都是有主外键关系的表。

内连接

内连接有两种写法，分别是92标准和99标准。
案例: 查询学生的学号/姓名/课程号/成绩。

1. 92标准写法：

select studentid 学号,name 姓名,cno 课程号,grade 成绩
from student,score
where id=studentid;

2. 99标准写法：

select studentid 学号,name 姓名,cno 课程号,grade 成绩
from student inner join score on id=studentid;

注意： 如果关联的表中出现重命的列名，则建议使用表名称【简化了操作，也不会出现列不明确ambious的报错】。

• 为表命名别名

-- 为表命名别名 [推荐]
select sc.id 学号,s.name 姓名,sc.cno 课程号,sc.grade 成绩
from student as s inner join score as sc on s.id=sc.id;

三表连接的语法也一样。

-- 三表连接  
-- 99标准
select sc.id 学号,s.name 姓名,c.cname 课程名,sc.grade 成绩
from student as s inner join score as sc on s.id=sc.id
			      inner join course as c on sc.cno=c.cno;
									

-- 92标准
select sc.id 学号,s.name 姓名,c.cname 课程名,sc.grade 成绩
from student as s ,score as sc ,course as c 
where sc.cno=c.cno and s.id=sc.id;

外连接

右外连接只支持99标准。

1. 左外连接 - left join 关键字左边表有数据,在右边表没有数据,也需要显示,对应右边数据显示为空。

select s.id 学号,name 姓名,cno 课程号,grade 成绩
from student as s left join score  as sc on s.id = sc.id;

2. 右外连接 - right join 关键字右边表有数据,在左边表没有数据,也需要显示,对应左边数据显示为空。

select s.id 学号,name 姓名,cno 课程号,grade 成绩
from score  as sc  right join student as s on s.id = sc.id

子查询

也称为嵌套查询,表示一个查询语句中,某个子句内部又包含一个查询。

案例1： 查询成绩大于1号学生所有选修课程的平均分的选修信息。

select *
from score
where grade > (select avg(grade) from score where id=1);

案例2： 查询学生的成绩占全班总成绩的占比

select id,cno,grade,round(100*grade/(select sum(grade) from score),2) '占比%'
from score;