【MySQL数据库】DQL语言，查询语句及常见函数

DQL语言的学习

数据库模式定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言。

进阶1：基础查询

​ 语法：

SELECT 要查询的东西	
【FROM 表名】;

类似于Java中 :System.out.println(要打印的东西);
特点：
1) 通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
2) 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数

进阶2：条件查询

​ 条件查询：根据条件过滤原始表的数据，查询到想要的数据
​ 语法：

select 
​		要查询的字段|表达式|常量值|函数
​	from 
​		表
​	where 
​		条件 ;

分类：
一、条件表达式
	示例：salary>10000
	条件运算符：
	> < >= <= = != <>

二、逻辑表达式
	示例：salary>10000 && salary<20000

逻辑运算符：
	and（&&）:两个条件如果同时成立，结果为true，否则为false
	or(||)：两个条件只要有一个成立，结果为true，否则为false
	not(!)：如果条件成立，则not后为false，否则为true

三、模糊查询
	示例：last_name like '_a%'
	’%‘表示0或多个字符，’_‘表示一个字符

进阶3：排序查询

语法：

select
	要查询的东西
from
	表
where 
	条件
order by 排序的字段|表达式|函数|别名 【asc|desc】

进阶4：常见函数

提高重用性和隐藏实现细节

一、单行函数

1、字符函数

 concat：拼接（拼接内容有一个为null，则返回null）
​ substr：截取子串（索引从1开始，前后都包括，一个参数表示从它到字符串末尾）
​ upper：转换成大写
​ lower：转换成小写
​ trim：默认是去除前后指定的空格，也可以指定去除字符 (trim('aa' from 'aaabcaaa');#去除前后的aa结果为'abca')
​ ltrim：去左边空格
​ rtrim：去右边空格
​ replace：替换 (replace('张无忌爱上了周芷若','周芷若','赵敏'); 结果：'张无忌爱上了赵敏'，全部对应字符串都会替换)
​ lpad：左填充字符 (lpad('唐小尊',2,'ab');结果是'唐小'，lpad('唐小尊',6,'ab');结果是'aba唐小尊')
​ rpad：右填充字符
​ instr：返回子串第一次出现的索引 (没找到返回0)
​ length：获取字节个数

使用函数的返回结果进行判断或排序时，通常取别名，通过别名来进行校验。
例如：

 #将员工的姓名按首字母排序，并同时输出姓名的长度(length)
 select length(last_name) 长度,substr(last_name,1,1) 首字符,last_name
 from employees
 order by 首字符;

2、数学函数

（第二个参数都是小数点后保留几位）

 round：四舍五入 (一个参数或两个参数，第二个参数为小数点后保留位数)
​ rand：随机数
​ floor：向下取整 (小于等于它的第一个整数)
​ ceil：向上取整 (大于等于它的第一个整数)
​ mod：取余 (mod(a,b)=a%b，结果是a-a/b*b，如果a,b有负数，则结果是绝对值的a%b，符号为a的符号)
​ truncate：截断 (truncate(1.69999,1);结果是1.6)

3、日期函数

 now：当前系统日期+时间
​ curdate：当前系统日期
​ curtime：当前系统时间
​ str_to_date：将字符转换成日期 (str_to_date('9-13-1999','%m-%d-%Y');结果为1999-09-13的日期格式。只有合法的数据才可以转换)
​ date_format：将日期转换成字符 (date_formate('2018/6/6','%Y年%m月%d日');输出2018年06月06日)
​ datediff(now(),'1995-1-1')：得到前面的时间与后面时间相差的天数
​ 得到年、月、日、星期、小时、分钟、秒
    year(now())：【输出】今年
    year('1998-1-1')年：【输出】1998年
    month(date)：数字月份
    monthname(date)：英文的月份
    day(date)：日
    YEARWEEK()：星期，外国人的周日是第一天，得到中国的周几为yearweek(date,1);
    hour(date)：小时
    minute(date)：分钟
    second(date)：秒

select date_format(now(),'%y年%m月%d日');#输出现在的日期

序号	格式符	功能
1	%Y	4位的年份
2	%y	2位的年份
3	%m	月份（01，02，…）补零
4	%c	月份（1，2，…）
5	%d	日（01，02…）
6	%H	小时（24小时制）
7	%h	小时（12小时制）
8	%i	分钟（00，01，…，59）
9	%s	秒（00，01，…，59）

4、流程控制函数

1）if

if(10>5,'满足','不满足');【输出】满足
# 案例一：
select if(1>2,'正确','错误');#输出错误
# 案例二：
select if(count(*)>100,'超过100人','select .....')
from student;

2）case

case使用方法一：类似switch

case 要判断的字段或表达式
       when 常量1 then 要显示的值1或语句1
       when 常量2 then 要显示的值1或语句2
       else 要显示的值n或语句n
end

case使用方法一 案例：

/*案例：查询员工的工资，要求

部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资

*/
select salary 原始工资,department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;

case使用方法二：类似于多重if

与方法一只有一个地方不同，就是case后面没有加东西

case
    when 条件1 then 要显示的值1或语句1
    when 条件2 then 要显示的值2或语句2
    ...
    else 要显示的值n或语句n
end

case使用方法二 案例：

/*案例：查询员工的工资的情况

如果工资>20000，显示A级别
如果工资>15000，显示B级别
如果工资>10000，显示C级别
否则显示D级别
*/
select salary
case
	when salary>20000 then 'A'
	when salary>15000 then 'B'
	when salary>10000 then 'C'
	else 'D'
end as 工资级别
from employees

5、其他函数

version：#版本 使用方法：select version();
database：#当前库
user：#当前连接用户
password('字符')：#返回该字符的密码

​

二、分组函数

sum：求和
max：最大值
min：最小值
avg：平均值
count：计数

特点：
    1、以上五个分组函数都忽略null值，除了count(*)
    2、sum和avg用于处理数值型，处理字符型数据返回0
    max、min、count可以处理任何数据类型
    3、全部都可以和去重关键字distinct搭配使用，用于统计去重后的结果。sum(distinct data);
    4、count的参数可以支持：
    字段、*、常量值，一般放1
	5、count函数的详细介绍
	count(1)：在一列中查找一个常量值（相当于多出来一列，同样是得到全部的行数）
	建议使用 count(*)
    原因：
        myisam存储引擎下，有一个内部的计数器，直接就返回了个数，count(*)效率高
        innodb存储引擎下，count(*)和count(1)差不多，比count(字段)要高一些，因为这个字段需要判断，耗时。
    6、和分组函数一同查询的字段有限制
    和分组函数异同查询的字段要求是group by后的字段！

进阶5：分组查询

分组函数直接使用相当于是把整个表当成一个大组。但是如果要求某个部门的分组数据，则需要分组查询与分组函数配合使用。

1、语法

#语法：
select 查询的字段（该字段要求出现在group by的后面），分组函数
from 表
【where 筛选条件】
group by 分组的字段
# 注意：查询的字段必须特殊，要求是分组函数和group by后出现的字段

2、普通分组

# 案例一：查询每个工种的最高工资（每个xx，其中查询的字段是xx，group by后面的字段也是xx）（按工种分组）
select max(salary),job_id
from employess
group by job_id;
# 案例二：查询每个位置上的部门个数（按位置分组）
select count(*),location_id
from departments
group by location_id;

3、加分组前筛选条件

# 添加筛选条件
# 案例一：查询邮箱中包含a字符的，每个部门的平均工资（按部门分组）
select avg(salary),department_id
from employees
where email like '%a%'
group by  department_id
# 案例二：查询有奖金的每个领导手下员工的最高工资（按领导分组）
select max(salary),manager_id
from employees
where commission_pct is not null
group by manager_id;

4、加分组结果筛选条件

# 添加复杂的筛选条件（筛选条件在group的结果集中，先出结果再筛选）
# 案例一：查询那个部门的员工个数>2
# ①查询每个部门的员工个数
select count(*),department_id
from employees
group by department_id;
# ②根据①的结果进行筛选，查询哪个部门的员工个数>2
select count(*),department_id
from employees
group by department_id
having count(*)>2;
# 案例二：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
# 如果条件太多，分不清楚筛选条件放在前还是后，可以先把select、from、group by这三行写好，再加入前后筛选条件
select max(salary),job_id
from employess
where commission_pct is not null
group by job_id
having max(salary)>12000;
# 案例三：查询领导编号>102的每个领导手下的最低工资>5000的领导是谁，以及其最低工资
# 分组的字段是领导，有关领导的条件放在where后。
# 分组函数求最低工资，有关分组函数结果的条件放在having后
select 领导id，min(工资)
from 员工表
where 领导id>102
group by 领导id
having min(工资)>5000;

5、按表达式或函数分组

# 按表达式或函数分组
# 按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些？
select count(*),length(last_name) len_name
form employees
group by length(last_name)
having count(*)>5

6、按多个字段分组

# 按多个字段分组
# 案例：查询每个部门每个工种的员工的平均工资
select avg(salary),department_id,job_id
from employees
group by department_id,job_id;#这两个字段都一样才会分成一个小组

7、添加排序

# 添加排序
# 案例：查询每个部门每个工种的员工的平均工资，并且按平均工资的从高到低显示
select avg(salary),department_id,job_id
from employees
where department_id is not null
group by department_id,job_id
having avg(salary)>10000 #平均工资高于10000
order by avg(salary) desc;#除了limit，就是它最后，比having靠后

8、group by特点

1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选
		    针对的表	            位置			关键字
分组前筛选：	原始表		    group by的前面	    where
分组后筛选：	分组后的结果集	group by的后面		having

4、可以按多个字段分组，字段之间用逗号隔开
5、可以支持排序
6、分组函数做条件肯定是放在having中，having后可以支持别名
7、考虑到性能问题，能用分组前筛选，就优先考虑分组前筛选。
8、group by、having后面都可以跟别名。Oracle不支持

进阶6：多表连接查询

一、引入多表联查

如果需要查询的字段来自于多个表时，就需要多表连接查询。

笛卡尔乘积现象： 表1 有m行，表2 有n行，结果位m*m行。

产生原因： 没有有效的连接条件。如果连接条件省略或无效则会出现。

如何避免： 添加有效的连接条件。where。

select name,boyName from boys,beauty
where beauty.boyfriend_id=boys.id;#连接条件

连接查询分类：

• 按年代分类：
  • sql92标准：仅仅支持内连接
  • sql99标准【推荐】支持内连接+外连接（左外和右外）+交叉连接
• 按功能分类：
  • 内连接
    • 等值连接
    • 非等值连接
    • 自连接
  • 外连接
    • 左外连接
    • 右外连接
    • 全外连接
  • 交叉连接

二、sql92标准 传统模式下的连接 ：等值连接——非等值连接

1.等值连接的结果 = 多个表的交集
2.n表连接，至少需要n-1个连接条件
3.多个表不分主次，没有顺序要求
4.一般为表起别名，提高阅读性和性能

1、等值连接

# 案例1：查询女神名和对应的男神名
select name,boyName from boys,beauty
where beauty.boyfriend_id=boys.id;#连接条件

# 案例2：查询员工名和对应的部门名
select last_name,department_name
from employees,departments
where employees.department_id=departments.department_id;

一定要为表写别名，这样写代码会更美观。

注意：如果为表起了别名，则查询的字段就不能使用原来的表名取限定。

# 案例3：查询员工名、工种号、工种名
select e.last_name,e.job_id,j.job_title
from employees as e,jobs as j
where e.job_id=j.job_id

# 案例4：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
select 部门表.部门名，部门表.领导编号，min(员工表.工资)
from 部门表，员工表
where 部门表.奖金 is not null and 部门表.部门id=员工表.部门id
group by 部门表.部门id,部门表.部门名

# 案例5：查询每个工种的工种名和员工的个数，并且按员工个数降序
select job_title,count(*)
from employees e,jobs j
where e.job_id=j.job_id
group by job_title
order by count(*) desc;

2、非等值连接

（用非等号外，其他的筛选关键字）

# 实现三表连接
# 案例6：查询员工名、部门名和所在的城市
select last_name,department_name,city
from employees e,departments d,locations l
where e.department_id=d.department_id
and d.location_id=l.location_id
and city like 's%' 
order by department_name desc;

3、自连接

# 自己连接自己
# 案例：查询 员工名和上级名
# 同一个表，当作两个表，员工表、上级表。上级也是员工，
select e.employee_id,e.last_name,m.employee_id,m.last_name
from employees e,employees m
where e.manager_id=m.employee_id

① 多表等值连接的结果为多表的交集部分

② n表连接，至少需要n-1个连接条件

③ 多表的顺序没有要求

④ 一般需要为表取别名

⑤ 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

三、sql99语法：通过join关键字实现连接

含义：1999年推出的sql语法
支持：
等值连接、非等值连接 （内连接）
外连接
交叉连接

语法：

select 字段，...
from 表1
【inner|left outer|right outer|cross】join 表2 on  连接条件
【inner|left outer|right outer|cross】join 表3 on  连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

好处：语句上，连接条件和筛选条件实现了分离，简洁明了！

注意：直接使用join，默认是内连接。
外连接有些数据库要求使用 outer 关键字

• join：内连接
• inner join：内连接
• left 【outer】 join：左外连接
• right 【outer】 join：右外连接
• full 【outer】 join：全外连接

select 查询条件
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件 # 提高了可读性
【where 筛选】
【group by 分组】
【having 筛选分组】
【order by 排序列表】

1、内连接：inner join

只有满足筛选条件的数据，才放入结果集中

# 语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件

#等值、非等值、自连接
# 1、等值连接
# 案例1.查询员工名、部门名
select last_name,department_name
from employees e
inner join departments d
on e.department_id=d.department_id

# 案例2.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
select last_name,department_name,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 department_name desc

# 2、非等值连接
#查询员工的工资级别  直接使用join，默认是内连接
select salary,grade_level
from employees e
join job_grades g
on e.salary between g.lowest_sal and g.hightst_sal;

2、外连接

应用场景：查询一个表中有，另一个表中没有的数据

特点：

1、外连接的查询结果为主表中的所有记录
	如果从表中有和它匹配的，则显示匹配的值
	如果从表中没有和它匹配的，则显示匹配的值
	外连接查询结果 = 内连接结果 + 主表中有而从表没有的记录
2、 左外连接，left join 左边的是主表
	右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接 = 内连接的结果 + 表1中有但表2没有的 + 表2中有但表1没有的
5、全外连接：两个表都可以看作是主表，互相是对方的从表，没有主从之分

外连接案例：
可以发现，如果从表没有数据匹配这一行的主表数据，依然会把这一行主表数据放入结果集，并且会把这一行的从表数据全部设为空！

下面两个结果相同：

# 左外
select d.*,e.employee_id
from departments d
left outer join employees e
on d.department_id = e.department_id
where e.employee_id is null;

# 右外
select d.*,e.employee_id
from employees e
right outer join departments d
on d.department_id = e.department_id
where e.employee_id is null;

3、交叉连接

# 交叉连接
select b.*,bo.*
from beauty b
cross join boys bo;

交叉连接就是一个笛卡尔积

4、自连接

#案例一：查询员工名和直接上级的名称
# sql99语法
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m ON e.`manager_id`=m.`employee_id`;
# sql92语法
SELECT e.last_name,m.last_name
FROM employees e,employees m 
WHERE e.`manager_id`=m.`employee_id`;

# 案例二：查询部门名为SAL或IT的员工信息
select e.*,d.department_name
from departments d
left join employees e
on d.department_id = e.department_id
where d.department_name in ('SAL','IT');

进阶7：子查询

含义：

一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询
在外面的查询语句，称为主查询或外查询

特点：

1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面、exists后面(相关子查询)，但一般放在条件的右侧
3、子查询优先于主查询执行，主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类：
1)单行子查询
	结果集只有一行
	一般搭配单行操作符使用：> < = <> >= <= 
	非法使用子查询的情况：
	a、子查询的结果为一组值
	b、子查询的结果为空
	
2)多行子查询
	结果集有多行
	一般搭配多行操作符使用：any（和子查询返回的任意一个值比较，<any()，小于任意一个）/some、all（和子查询返回的所有值比较）、in（等于列表中的任意一个）/not in
	in： 属于子查询结果中的任意一个就行，in()等价于=any()
	any和all往往可以用其他查询代替

# 子查询的查询结果可以直接当成一个表来使用，需要取别名。
select ag_dep.*,g.grade_level
from (
	select avg(salary) ag,department_id
	from employees
	group by department_id
) ag_dep
inner join job_grades g
on ag_dep.ag between lowest_sal and highest_sal;
	   

exists函数

#返回结果代表括号内查询结果是否不为空，如果不为空，则输出1，否则输出0。
select exists(select...);

# 案例1：查询有员工的部门名
select department_name
from departments d
where exists(
	select *
	from employees e
	where d.department_id=e.department_id
);

七种Join

进阶8：分页查询

应用场景：

实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句

语法：

select 字段|表达式,...
from 表
	【where 条件】
	【group by 分组字段】
	【having 条件】
	【order by 排序的字段】
limit 【起始的条目索引，】条目数;#如果不设置起始索引，默认起始索引是0

特点：

1.起始条目索引从0开始

2.limit子句放在查询语句的最后

3.公式：select * from  表 limit （page-1）*sizePerPage,sizePerPage
假如:
每页显示条目数sizePerPage
要显示的页数 page

进阶9：联合查询

将多条查询语句的结果合并成一个结果

引入：

union 联合、合并

语法：

select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union  【all】
.....
select 字段|常量|表达式|函数 【from 表】 【where 条件】

特点：

1、多条查询语句的查询的列数必须是一致的
2、多条查询语句的查询的列的类型几乎相同
3、union代表去重，union all代表不去重