DQL（数据查询语言）语言学习

练习题：p75、76、77、86、94、93，96，97，98，99
查询时为字段起别名（如果别名中有特殊符号，建议将别名用双引号/单引号引起来）

select     列名1    as   别名1, 列名2     as     别名2    from 表名;

select     列名1     别名1, 列名2     别名2    from 表名;

---

查询员工表中的部门编号，去重 distinct

select distinct department_id from employees;

---

mysql中(加号) + 的作用
1：mysql中加号只能做为运算符，不能做连接符
2：‘123’+90 : 其中一方为字符型，mysql会试图将字符型转换为数值型，转换成功，正常做加法运算，转换失败，将字符型数值转换为0，然后做加法运算
3：null+10：只要有一方为null,结果就肯定为null.

---

mysql拼接 concat(字段1，字段2，，，字段n) 函数
employees表中查询员工名（last_name）和姓（first_name）连接成一个字段，并显示为 姓名

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

注意： 如果concat函数中有一个字段为null，则查询结果为null
具体视频

---

ifnull(字段名,返回值) 函数
查询表employees中所有员工的奖金率(commission_pct),如果奖金率为null,则奖金率显示为0

select concat(last_name,first_name) as 姓名  , ifnull(commission_pct,0)  as 奖金率 from employees 

显示出表employees的全部列(名，姓，奖金率)，如果奖金率为null则显示为null，各个列之间用逗号连接，列名显示成out_put

select 
	  concat(first_name,     ',' ,     last_name,     ',' ,     ifnull(commission_pct,0))     as     'out_put'  
from  employees;

isnull(字段名) 函数
如果每一个字段的值是null,返回1，字段值不为null，返回0

---

条件查询
1：按条件表达式筛选：

>  <  =  !=或者<>  >=  <=

2：按逻辑表达式：

&&或者and  ||或者or   !或者not

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

select * from employees where not(department_id >= 90 and department_id <= 110) or salary > 15000;

3：模糊查询

like , between and , in , is null , is not null
与like息息相关的通配符：
					% : 匹配任意多个字符
					_ : 匹配一个字符
模糊查询会过滤掉空值

查询 employees 表中名含有a的员工的所有信息

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

查询 employees 表中员工名中第二个字符为o，第五个字符为a的员工所有信息

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

查询员工id（employee_id）在100到120区间内的信息

select * from employees  where employee_id>=100 and employee_id<=120;

select * from employees  where employee_id>=100 && employee_id<=120;

select * from employees  where employee _id between 100 and 120;

查询没有奖金率(commission_pct)的员工名和奖金率

select last_name,commission_pct from employees where commission_pct = null
错误：等于号不能判断null值

select last_name,commission_pct from employees where commission_pct is null

---

阶段性总结视频

---

order by()：排序查询（默认为升序）
查询员工信息，要求工资从低到高

select * from employees order by salary asc;

查询员工信息，要求工资从高到低

select * from employees order by salary desc;

查询员工信息，要求聘用时间从高到低

select * from employees order by hiredate desc;

查询员工名字并按首字母升序排列

点击查看substr()方法使用

select last_name from employees order by substr(last_name,1,1) asc

查询员工表的 employee_id,job_id,last_name ，按照 department_id 降序，salary升序

select employee_id,job_id,last_name from employees order by department_id desc, salary asc;

---

求长度 length(字段名) 函数
查询员工id， 姓名，姓名长度，并按姓名长度升序排列

select employee_id 员工id,  concat(last_name,first_name)  姓名 , length(last_name)+length(first_name) 姓名长度 from employees order by 姓名长度 asc;

查询员工id， 姓名，姓名长度，并按姓名长度升序排列，如果长度相同，按id升序排列

select employee_id 员工id,  concat(last_name,first_name)  姓名 , length(last_name)+length(first_name) 姓名长度 from employees order by 姓名长度 asc  ,员工id asc;

查询员工表中员工工资不在8000~17000之间的全部信息，并按工资排序

select * from employees where not (salary>=8000 && salary<=17000) order by salary asc

---

单行函数

• 字符函数
• 数字函数
• 日期函数
• 其他函数
• 流程控制函数

---

字符函数
length()：获取参数值的字节个数
concat()：拼接字符串

upper(): 将参数值大写
lower():将参数值小写
查询员工表中所有员工的姓名，将姓大写，名字小写

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

---

substr() / substring() :字符截取函数

select substr('我爱你没道理！'  ,  4) 字符截取

select substr('我爱你没道理！'  ,  2   , 6) 字符截取

查询员工表中所有员工的名字，并将首字母大写

select 
	concat(
		upper(
			substr(last_name,1,1)
			 ),
		lower(
			substr(last_name,2)
			 )
	)  as  名字
from employees ;

---

instr() : 返回一个字符串在另一个字符串第一次出现的起始索引，如果找不到，返回0

select instr('OK兄弟们，全体目光向我看齐，看我看我，我宣布个事儿，我是个傻逼，没毛病啊'  ,   '我是个傻逼') as 起始索引

rand()： 获取一个0到1之间的随机浮点数

select rand();

trim(): 去掉字符串头尾的空格

select  trim('      社会你虎哥      ');

---

trim( ~ from ~)：去掉字符串中的某个字符

select   trim(  'q'  from    'qqq社会你龙哥q');

---

lpad()：用指定字符，将某一字符串填充到指定长度 ,从左填充

select lpad('你好',5,'*');

---

rpad()：用指定字符，将某一字符串填充到指定长度 ,从左填充

select rpad('你好',5,'*');

---

replace()：替换

select replace('社会你虎哥'  ,  '虎哥'    ,  '虎弟');

---

round() : 四舍五入 进位

select round(1.5);   #结果： 2
select round(1.4);   #结果： 1
select round(-0.7);  #结果：-1
select round(1.225,2); #结果:1.23

---

ceil()：向上取整 返回>=该参数的最小整数

select  ceil(1.02);    #结果：2
select  ceil(-1.02);   #结果：-1 

---

floor()：向下取整 返回<=该参数的最小整数

select  floor(1.02);    #结果：1
select  floor(-1.02);   #结果：-2 

---

truncate()：截断（小数点后保留几位，不进位）

select truncate(1.9999,1);   #结果：1.9

---

mod()：取余/求模

select mod(10,3);    #结果：1
select mod(10,-3);   #结果：1
select mod(-10,3);    #结果：-1
select mod(-10,-3);    #结果：-1

---

now()：返回当前系统日期+时间

select now();  #结果：2021-11-28 16:05:09

---

curdate()：返回当前系统日期

select curdate();  #结果：2021-11-28

---

curtime()：返回当前系统时间

select curtime();  #结果：16:09:12

---

datediff()：查询两个日期之间存在多少时间间隔

查询出生到现在活了多少天

select datediff(now(),'2000-10-28');

获取年，月，日

点击观看尚硅谷视频

---

select year(new());
select year('1999-10-11');
select year('1999/10/11');
select year('1999.10.11');
select year(表中带有年份时间的字段);

month(~)：获取月份

day(~)：获取日

time(~)：获取时

str_to_date()：将字符通过指定的格式转换为日期

观看视频并跳转到第9分钟

---

其他函数：
version()：查看当前数据库版本
database()：查看当前所在库
user()：查看当前所登录的用户

---

流程控制函数：
if()：点击观看视频

---

分组(聚合)函数
分类：

• sum()：求和 不计算为null的值
• avg()：求平均 不计算为null的值
• max()：求最大 不将为null的值包括在内
• min()：求最小 不将为null的值包括在内
• count()：计算个数 不将为null的值包括在内

点击查看为什么不将null包括在内

---

sum()：求和

查询员工表中所有员工一个月工资的总和（不算奖金）

select sum(salary) from employees;

查询员工表中所有员工一个月工资的总和（算奖金）

select sum(salary*(1+ifnull(commission_pct,0))) from employees;

---

avg()：平均

查询所有员工薪资的平均值

select avg(salary) from employees;

---

max()：返回最大值

select max(salary) from employees;

---

min()：返回最小值

select min(salary) from employees;

查询所有员工的平均值，最大值，最小值，工资总和

select avg(salary) 平均值,max(salary) 最大值,min(salary) 最小值,sum(salary) 工资总和 from employees;

---

count()：返回当前字段不为null的个数/总数

select count(commission_pct) from employees;

---

分组查询：group by
查询每个工种的最高工资

select max(salary) , job_id from employees group by job_id;

---

查询邮箱中包含a字符的，每个部门的平均工资

select avg(salary) ,department_id from employees where email like '%a%' group by department_id ;

---

查询有奖金（commission_pct为奖金率）的每个领导手下员工的最高工资

select max(salary) 最高工资 ,manager_id from employees where commission_pct is not null group by manager_id;

---

筛选：having （位于group by字句后面）
注意：

• having与where类似，用于筛选数据
• where用于分组(group by)前

select id,name from employees where id>10 group by age

• having用于分组(group by)后
• where后跟的字段为表中原有字段
• having后的字段可以为分组函数

查询哪些部门的员工数大于2

#错误写法：
select count(*) 员工个数, department_id from employees where count(*) > 2 group by department_id;

#正确写法：
select count(*) 员工个数, department_id from employees group by department_id having count(*) > 2;

---

查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

select max(salary) 最高工资 , job_id from employees where commission_pct is not null group by job_id having 最高工资 > 12000

---

查询每个部门每个工种的员工的平均工资

select avg(salary),job_id,department_id from employees group by department_id,job_id;

---

连接查询(多表查询) 点击观看视频

• 内连接(sql92,sql99)

  • 等值连接
  • 非等值连接
  • 自连接
    

• 外连接

  • 左外连接：查询表一中有，表二没有的数据
    
    

  • 右外连接：查询表二中有，表一中没有的数据
    
    

  • 全外连接/全连接(mysql不支持)：查询表一，表二共有的数据+查询表一中有，表二没有的数据+查询表二中有，表一中没有的数据
    
    MySQL虽然语法不支持全连接，但是可以通过左连接+union(联合查询)+右连接实现功能

    select * from tbl_dept left join tbl_emp on tbl_dept.id=tbl_emp.deptId
    union
    select * from tbl_dept right join tbl_emp on tbl_dept.id=tbl_emp.deptId;
    

    

select * from tbl_dept left join tbl_emp on tbl_dept.id=tbl_emp.deptId where tbl_emp.deptId is null
union
select * from tbl_dept right join tbl_emp on tbl_dept.id=tbl_emp.deptId where tbl_dept.id is null;

• 交叉连接：笛卡尔乘积

---

---

(内连接) 等值连接（sql92）
查询女神对应的男神名

select beauty.name ,boys.boyname from boys,beauty where boys.id=beauty.boyfriend_id;

查询员工名，工种号，工种名

select employees.employee_id ,jobs.job_id,jobs.job_title from employees,jobs where jobs.job_id=employees.job_id

查询城市名中第二个字符为o的部门名和城市名

select 
	d.department_id,l.city 
from 
	departments d,locations l 
where
	d.location_id = l.location_id 
and
	substr(l.city,2,1) = 'o';

查询每个城市的部门个数

select 
	count(department_id) ,locations.city
from 
	departments,locations 
where 
	departments.location_id = locations.location_id 
group by 
	locations.city;

查询有奖金的 每个部门的部门名 和 部门的领导编号 以及 该部门的最低薪资

select 
	departments.department_name,departments.manager_id,min(employees.salary)
from 
	employees,departments
where 
	employees.department_id = departments.department_id
and 
	employees.commission_pct is not null
group by 
	departments.department_id;

实现三表连接查询(多表连接，多表查询)

查询员工名，部门名和所在城市

select 
	employees.last_name,departments.department_name,locations.city 
from 
	employees,departments,locations 
where
	employees.department_id = departments.department_id 
and
	departments.location_id = locations.location_id;

---

(内连接)非等值连接
查询员工的工资和工资级别

select
	employees.last_name,employees.salary ,job_grades.grade_level 
from 
	employees,job_grades 
where 
	employees.salary 
between 
	job_grades.lowest_sal 
and
	job_grades.highest_sal;

---

自连接(自己连接自己)

查询员工名,员工id和对应的上级领导名，领导id
select 
	e.last_name,e.employee_id,m.last_name,m.employee_id 
from 
	employees as e ,employees as m
where 
	e.manager_id = m.employee_id;

---

sql99语法：

等值连接

内连接：( 表一 inner join 表二 on 连接条件)

• 等值连接
• 非等值连接
• 自连接

查询员工名以及对应的部门名

#sql99语法

select 
	e.last_name,d.department_name	
from 
	employees e 
inner join 
	departments d
on 
	e.department_id = d.department_id;

#sql92语法
select 
	e.last_name,d.department_name 
from 
	employees e , departments d 
where
	e.department_id = d.department_id;

---

查询名字中包含e的员工名和与之对应的工种名

select 
	employees.last_name,jobs.job_title 
from 
	employees 
inner join 
	jobs 
on 
	employees.job_id = jobs.job_id 
where 
	employees.last_name 
like 
	'%e%';

---

查询部门个数大于3个的城市名和部门个数

select 
	l.city,count(department_id) 部门个数 
from 
	locations l 
inner join 
	departments d 
on 
	l.location_id = d.location_id 
group by 
	l.city
having 
	部门个数 > 3

---

等值连接(三个表)

查询员工名，部门名，工种名，并按部门名降序

select 
	e.last_name,d.department_name,j.job_title
from 
	employees e 
inner join 
	departments d 
on 
	e.department_id = d.department_id
inner join 
	jobs j
on
	e.job_id = j.job_id
order by
	d.department_name
desc;

---

非等值连接

查询员工工资级别

#sql99语法
select e.salary,j.grade_level from employees e inner join job_grades j on e.salary between j.lowest_sal and j.highest_sal

#sql92语法
select e.salary,j.grade_level from employees e ,job_grades j where e.salary between j.lowest_sal and j.highest_sal;

查询每个工资级别对应的员工个数，并且按工资级别降序

select j.grade_level,count(*) from employees e inner join job_grades j on e.salary between j.lowest_sal and j.highest_sal group by j.grade_level order by grade_level desc;

---

自连接

查询员工的名字，以及员工所对应的上级的名字

select 
	e.last_name,m.last_name
from 
	employees e 
inner join 
	employees m 
on 
	e.manager_id = m.employee_id;

---

外连接
点击观看尚硅谷视频

查询没有男朋友的女神

select 
	b.`name` 
from 
	beauty b 
left join 
	boys bo 
on 
	b.boyfriend_id = bo.id 
where 
	bo.id 
is null

---

交叉连接：笛卡尔乘积

#sql92语法
select * from beauty,boys;
#sql99语法
select * from beauty cross join boys;

---

连接查询总结

---

子查询(嵌套查询)

谁的工资比abel高

select 
	last_name
from 
	employees 
where 
	salary > (
		select 
			salary 
		from  
			employees 
		where 
			last_name = 'abel'
	);

---

查询location_id是1400或1700的部门中的所有员工姓名 （in）

select 
		last_name 
		from 
			employees 
		where 
			department_id 
		in(
			select 
			distinct 
				department_id 
				from 
					departments 
				where 
					location_id 
				in(1400,1700)
		)

---

any/some（功能相同）
返回其他部门中比job_id为it_prog部门任一工资低的员工的员工号，姓名，job_id以及salary

select 
	employee_id,last_name,job_id,salary 
from 
	employees 
where 
	salary < some(select distinct salary from employees where job_id = 'it_prog') 
and 
	job_id != 'it_prog';

---

all
返回其他部门中比job_id为it_prog部门所有工资低的员工的员工号，姓名，job_id以及salary

select 
	employee_id,last_name,job_id,salary 
from 
	employees 
where 
	salary < all(select distinct salary from employees where job_id = 'it_prog') 
and 
	job_id != 'it_prog';

---

查询每个部门的员工个数

select 
	d.department_id ,
	(select count(*) from employees e where e.department_id = d.department_id) 个数 
from 
departments d;

---

查询每个部门的平均工资的工资等级

点击观看视频

select 
	ag_dep.department_id , ag_dep.a,j.grade_level 
from 
	(select department_id , avg(salary) a from employees e group by e.department_id) ag_dep 
inner join 
	job_grades j 
on 
	ag_dep.a 
between 
	j.lowest_sal 
and 
	j.highest_sal

---

分页查询： limit 起始位置(从0开始)，页面条数
查询员工表的前五条

select * from employees limit 0,5;

---

联合查询： union
查询部门编号 大于90 或者邮箱包含a的员工信息

select * from employees where email like '%a%' or department_id > 90;

select * from employees where email like '%a%' union select * from employees where department_id > 90;

联合查询特点：

• 要求多条查询语句的查询列数是一致的
• 要求多条查询语句的查询的每一列的类型和顺序是一致的
• union关键字默认去重，如果使用union all 可以包含重复项

---

正则表达式