mysql：DQL语言（DATA Query Language）（SELECT）

mysql复习

概念

数据库的好处

1.可以持久化数据到本地
2.结构化的查询

数据库的常见概念

1.DB 数据库，存储数据的容器
2.DBMS：数据库管理系统，又称为数据库软件和数据库产品，用于创建和管理DB
3.SQl：结构化查询语言，用于和数据库通信的语言，不是某个数据库特有的，而是几乎所有的数据库软件通用的语言。

没见过的新点

tee 路径   在黑窗口中  吧输入的SQL语句全部输出在一个文件里

数据库的操作

一，连接数据库
mysql 【-h主机名 -P端口号】 -u账号	-p密码  或者	mysql -u账号	-p

入门基本的语句
1，先选库	再使用库	表也一样
use	database	Use	库名	使用库
show	databases	展示	数据库
show	tables	展示	表
use	table	use	表名	使用表

2，创建数据库						指定字符集
create	database	数据库名	charset  utf8

3，删除数据库
drop	database	数据库名

4，修改数据库名字？	不可以
在mysql中只能修改列/表的名字。
rename	table	oldname	to	newname

建表优化

 一个表的所有字段都为定长是将会提升查询的速度
 当有一个必须为不定长的字段是可以分表
 在开发中，会员的信息优化往往是，吧频繁用到的信息，优先考虑效率，存储到一张表中。
 不常用的信息和会比较占据空间的信息，优先考虑空间占用，存储到辅表中。

DQL语言（DATA Query Language）（SELECT）

基础查询

简单实用

select 查询列表(字段) from 表名

查询常量值

select 100；
select 'json';

查询表达式

select 100*98

查询函数

selsct version();

起别名(AS)

AS  可省略  建议不省略
有特殊情况的 加''

去重

distinct (di si xing te) 
selecct distinct 列名 from 表

+号的作用（运算符）

select 100+90; 两个操作数都为数值型，则作加法的运算
select '120'+80; #中一方为字符型，试图将字符型转换成数值型  
#果转换成功，则作运算，
#果转换失败，咋将失败的字符转换为0。
select null+0;  #要其中一方为null则结果肯定为nul。

拼接（concat）

案例： 查询员工的姓名和编号连接成一个字段  并显示为 姓名
select concat(name,id) as 姓名 from 表名   
#注意 只要一个字段为null   结果就为null

IFNULL(判断是否为空）

select ifnull(E1,E2) as 别名 from 表
#E1 判断的字段  #E2 为空时显示什么值

条件查询（where）

简单使用

select 查询列表(字段) from 表名 where 筛选条件

按条件表达式筛选

条件运算符：> < = != <> >= <=

按逻辑表达式筛选

and 
to 
not

模糊查询 ESCAPE '任意符号’意为转义

like  %
like '%a%'
like '__a__%'
#查询员工名字中第二个字符为_的员工名
like '_\_ %'

between and  #包含临界值 不要轻易调换位置
in() #（）里的类型要一致
is null  #只能判断null

安全等于 <=>

判断是否等于   null和数值 
where 字段 <=> null
where 字段 <=> 13000；

排序查询【order by】【asc|desc】

简单使用

select 查询列表(字段) from 表名
【where 筛选条件】
order by 字段 asc|desc

按表达式排序

 select *,salary*12(1+ifnull(奖金字段,0)) as 年薪
 from 表名
     order by 年薪 desc; 
     ----------------------------------------
  select *,salary*12(1+ifnull(奖金字段,0)) as 年薪
 from 表名
 order by salary*12(1+ifnull(奖金字段,0)) desc;

按函数排序

select length(`name`) AS 名字,`name` salary, from 表名 order by length(`name`);
----------------------------------------------
select length(`name`) AS 名字,`name` salary, from 表名 order by 名字;

按多个字段排序

先按工资，再按id排序
select * from 表名
order by salart asc,id DESC;

常见函数

select 函数名(实参列表) 【from 表名】
#单行函数
#功能：将一个数据进行处理，返回一个值
字符函数，数学函数，日期函数，其他函数，流程控制函数
#分组函数
#功能：将虚拟表看做一个组，处理一组数据，返回一个值
sum avg max min count group by

单行函数

字符函数

#一个汉字 GBK是2个字节  UTF——8是三个字节
length() #获取参数值的字节个数
concat（字段,字段） #拼接
upper() #转大写
lower() #转小写
substr() substring() #截取字符串  
#mysql的索引从1开始  字符的长度
select substr('猫和老鼠',3) as out_put;   结果：老鼠
select substr('猫和老鼠',1,2) as out_put;   结果：猫和
instr()
#返回子集在父集中的起始索引
#多个《老鼠》的话  返回第一次出现的索引  
#没有的话返回0
select instr('猫和老鼠','老鼠') as  out_put;  结果：3
trim()  
#去前后空格或者字符
select trim('     老师     ') as  out_put;  结果：老师
select trim('a' from 'aaaa老师aaaa') as  out_put;  结果：老师
lpad() #左 
rpad() #右
#用指定的字符左填充指定字符
#老鼠加上*达到10个字符 如果10变成2  就不会填充结果为老鼠 
select lpad('老鼠',10,'*') as out_put; 结果：*******老鼠
replace() #替换包
select lpad('汤姆要去抓杰瑞','杰瑞','老鼠') as out_put;
结果：汤姆要去抓老鼠

数学函数

round() #四舍五入
select round(1.55); 结果：2
select round(1.556,2); 结果：1.56 #保留两位小数
truncate() #截断
select truncate(1.69999,1); 结果：1.6 #不进行四舍五入。
ceil() #向上取整 返回>=该参数的最小整数
select ceil(1.00001); 结果：2
select ceil(1.00000); 结果：1
select ceil(-1.00001); 结果：-1
floor() #向下取整 返回<=该参数的最小整数
select floor(1.00001); 结果：1
select floor(-1.00001); 结果：-2
mod() #取余  绝对公式 mod(a,b): a-a/b*b
select mod(10,3); 结果： 1

日期函数

now() # 返回当前的系统时间+时间
curdate() #返回当前系统日期，不包含时间
curtime() #返回当前时间，不包含日期
year(now()) #返回当前的年份
year('1998-1-1'）#1998
year(时间字段)
month() #月 
monthName() #月的英文单词
day() #天
hour() #小时
minute() #分
second() #秒 
str_to_date() #将日期格式的字符装换成指定格式的日期
str_to_date('9-13-1556','%m-%d-%Y')  #1556-13-9
date_format() #将日期转换成字符
date_format('2018/07/08','%m年%d月-%Y日')  #2018年07月08日
time_to_sec() #将分转换成秒的函数
time   00:01:30
time_to_sec(time)  #90 
datediff(起始时间，结束时间)

其他函数

version()
database()
user()
use()

流程控制函数

IF(E1,E2,E3) #E1 条件表达式  ture E2的值  false E3的值
select if(10<5,'大','小')

# 类似switch case的效果    使用一
case #要判断的字段或者表达式
when /*常量值1*/ then /* 要显示的值或者【语句【语句要加分号】】*/ 
when /*常量值2*/ then /* 要显示的值或者【语句【语句要加分号】】*/
...
else #默认的值或者【语句【语句要加分号】
end  #结束

select salary as 原始工资,id,
case id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end 
as 新工资
from 表名

#类似多重IF  IF() ELSE IF() ELSE  使用二
case #后面没有要判断的字段或者表达式 直接 when
when /*条件1*/ then #要显示的值或者【语句【语句要加分号】
when /*条件2*/ then #要显示的值或者【语句【语句要加分号】
...
else #默认的值或者【语句【语句要加分号】

select salary
case
when salary>20000 then 'A'
when salary>15000 then 'B'
when salary>10000 then 'C'
else 'D'
end 
as 工资级别
from 表名

流程控制函数用在where后面

/*
	语法：
		where
		【and 。。。】 #如果case前面有条件  在case前面添加and
		【and】 case 的使用方法一 或者方法二
*/
#   以下都是case方法二的写法  方法一省略 。
#	报错的语法  这种不对
select * 
from A, B
where B.name in
CASE
    when A.id = 0 then ('aaa', 'bbb')
    when A.id = 1 then ('bbb', 'ccc')
    when A.id = 2 then ('ccc', 'ddd')
END



#  第一种写法
select * 
from A, B
where 
CASE
    when A.id = 0 then B.name in ('aaa', 'bbb')
    when A.id = 1 then B.name in ('bbb', 'ccc')
    when A.id = 2 then B.name in ('ccc', 'ddd')
END

#  第二种写法 也对
select * 
from A, B
where B.name = 
CASE
    when A.id = 0 then 'aaa'
    when A.id = 1 then 'bbb'
    when A.id = 2 then 'ccc'
END

分组函数

sum() avg() max() min() count() group by

简单使用

select sum(salary) AS 和,round(avg(),2) AS 平均,max() AS 最大,count() AS 个数 from 表名

参数类型支持那些

sum() avg() #一般用于处理数值型
max() min() count() #可以处理任意类型
#以上分组函数都是忽略null值的

分组函数个distinct搭配

可以个distinct搭配实现去重的运算

count函数的单独介绍(4种用法)

#单个字段计数
select count(字段) from 表名
#所有字段非空计数    一般用于统计多少行。
select count(*) from 表名
#相当于多了一列，统计 1 个数
select count(1) from 表名
select count('') from 表名
#效率：innodb存储引擎下，count(*)和count(1)差不多，比count(字段)要高，因为写字段的话要判断是否为null的
#	   myinam存储引擎下，count(*)的效率高 因为这个引擎内部就有一个计数器，会直接返回个数。

分组函数使用的限制

#和分组函数一同查询的字段要求是 group by后的字段
select avg(salary), id from 表名
#这个时候avg的结果为一行 而id应该是很多行的   结果为一行 虽然没有报错但是结果为错。

分组查询 group by

语法：

语法：
        select /*分组函数*/,#列(要求出现在group by的后面)
        from #表
        #【where 筛选条件】
        group by #分组的列表
        #【order by子句】
注意：
        查询的列表必须特殊，要求是分组函数和group by后出现的出现的字段

简单使用

select max(salary),job_id
from #表名
group by job_id

添加分组前的筛选 where

select count(*),id
from #表名
#【where】 后面跟的判断条件的字段必须是【表中的】
group by id 
having count(*)>2; #  having后面的判断条件可以是分组函数

添加分组后的筛选 having

select count(*),id
from #表名
#【where】 后面跟的判断条件的字段必须是【表中的】
group by id 
having count(*)>2; #  having后面的判断条件可以是函数     【结果虚表中的条件】

添加分组前后筛选的总结

/*
分组查询中的筛选条件分为两类
					   数据源						位置					关键字
分组前筛选			原始表						group by子句的前面			where
分组后筛选			分组后的结果集虚表			group by子句的后面			having
分组函数做条件肯定是放在having子句中
能用分组前的筛选的，优先使用分组前的筛选
*/

按表达式分组或函数分组

select count(*),length(name) AS name
from # 表名
group by length(name)
having length(name)>5;  ||  having count(*)>5;

按多个字段分组

select avg(salary),id,job_id 
from # 
group by id,job_id;

添加排序

select avg(salary),id,job_id 
from #表名 
group by id,job_id 
order by avg(salary) desc;

连接查询

/*
	含义：又称为多表查询，当我们查询的字段来自多张个表的时候，就会用到【连接查询】了
*/

笛卡尔集的错误显示

#表一【beauty】 12条记录 表二【boys】 4条记录 
select name,boyName from beauty,boys; 

#【产生12*4条结果】 
#发生原因：没有有效的连接条件 
#如何避免：添加有效的连接条件 
select name,boyName 
from beauty,boys 
where beauty.boyfriend_id = boys.id

【连接查询的分类】★

/*
按年代的分类 在mysql支持的 
sql192标准：
	仅仅支持内连接 #是1992年推出的标准 
	等值连接 
	非等值连接 
	自连接 
	也支持一部分外连接（用于oracle sqlserver | mysql不支持）
    
sql199标准【推荐】： #是1999年推出的标准 也就是在mysql中 【不支持全外】 
	支持内连接 inner
		等值连接 
		非等值连接 
		自连接 
	外连接(左外右外) 
		左外连接 left【outer】 
		右外连接 right【outer】 
		全外连接（mysql不支持） 
		full【outer】 
	交叉连接 cross 
按功能分类 
	内连接：
		等值连接 
		非等值连接 
		自连接 
	外连接：
		左外连接 
		右外连接 
		全外连接 
	交叉连接：
*/

一，sql92标准 仅仅支持内连接

等值连接

/*
语法：
	select 字段 
	from 表1 AS a，表2 AS b 
	where 等值条件
*/

select name,boyName 
from beauty,boys 
where beauty.boyfriend_id = boys.id 

1，为表起别名

/*好处： 
提高语句的简洁度 
区分多个重名的字段 

注意：如果为表起了别名，则查询的字段就不能用原来的表名去限定 */
select e.last_name AS 员工名字,d.department_name AS 部门名字 
from employees AS e,departments AS d 
where e.`department_id = d.`department_id`;

2，两个表名的顺序是可以调换的

select e.last_name AS 员工名字,d.department_name AS 部门名字 
from departments AS d,employees AS e 
where e.`department_id = d.`department_id`;

3，可以加筛选

select e.last_name AS 员工名字,d.department_name AS 部门名字 
from employees AS e,departments AS d 
where e.`department_id = d.`department_id` 
and e.salary is not null;

4，可以加分组

select count(*) AS 个数,city 
from departments AS d,locations l 
where d.`location_id` = `l.location_id` 
group by city;

5，可以加排序

select count(*) AS 个数,city 
from departments AS d,locations l 
where d.`location_id` = l.`location_id` 
group by city 
order by count(*);

6，可以三表连接

select e.last_name AS 员工名字,d.department_name AS 部门名字,city AS 城市 
from departments AS d,locations l, employees AS e 
where d.`location_id` = l.`location_id` 
and e.`department_id = d.`department_id` 
and city like '%s'; 
order by 员工名字 desc

非等值连接

                            select * from job_grades;

select salary,grade_level AS 工资等级 
from employees e,job_grades g 
where salary between g.`lowset_sal` and g.`highest_sal`; #非等值连接的条件

筛选||分组||排序

select salary,grade_level AS 工资等级 
from employees e,job_grades g 
where salary between g.`lowset_sal` and g.`highest_sal`; 
and g.`grade_level` = 'A' 
and g.`grade_level` = 'B' 
group by g.`grade_level` 
order by g.`grade_level`

自连接(自己连接自己)

select e.`name` as 员工,a.`name` as 领导 
from employees e,employees a 
where e.`f_id` = a.`id`

二，sql99语法 在mysql中【不支持全外】

/*
语法：
	select 查询列表 
	from 表1 别名 【连接类型】 
	join 表2 别名 
	on 连接条件 
	【where】 
	【group by】 
	【having】 
	【order by】 
*/

内连接 inner可以省略

等值连接

/*语法：
	select 查询列表 
	from 表1 别名 
	inner join 表2 别名 
	on 连接条件 
*/
select e.last_name AS 员工名字,d.department_name AS 部门名字 
from employees AS e 
inner join departments AS d 
on e.`department_id = d.`department_id`;

筛选||分组||排序

select count(*),e.last_name AS 员工名字,d.department_name AS 部门名字 
from employees AS e 
inner join departments AS d 
on e.`department_id = d.`department_id` 
where e.`last_name` like '%e%' 
group by e.`last_name` 
having count(*)>3 
order by count(*); 
# 注释： 这只是语法

三表连接

select e.last_name AS 员工名字,d.department_name AS 部门名字,city AS 城市 
from departments AS d 
inner join employees AS e on e.`department_id = d.`department_id` 
inner join locations AS l on d.`location_id` = l.`location_id` 
order by 员工名字 desc

非等值连接

select salary,grade_level AS 工资等级 
from employees e 
join job_grades g 
on e.`salary` between g.`lowset_sal` and g.`highest_sal`; #非等值连接的条件

筛选||分组||排序

select count(*),grade_level AS 工资等级 
from employees e 
join job_grades g 
on e.`salary` between g.`lowset_sal` and g.`highest_sal` #非等值连接的条件 
group by grade_level 
having count(*) > 20 
order by grade_level desc

自连接

select e.name as 员工,a.name as 领导 
from employees e 
join employees a 
on e.f_id = a.id

外连接

/*
应用场景：用于查询一个表中有，另一个表中没有的记录 
特点： 
	1，外连接的查询结果为主表中的所有记录 
		有匹配的显示匹配的值 
		没有匹配的就显示null 
		外连接查询的结果+内连接结果+主表中没有的结果 
	2，左外连接：left中左边的是主表 
		右外连接：right中 右边的是主表 
	3，左外和右外交换连个表的顺序，可以实现相同的结果 
	4，全外连接=内连接结果+表1有表2没有的结果+表2有表1没有的结果
*/

左外联

select b.name,bo.* 
from beauty AS b 
left outer join boys bo 
on b.boyfriend_id = bo.id

右外联

select b.name,bo.* 
from boys AS bo 
right outer 
join beauty AS b 
on b.boyfriend_id = bo.id

全外连接【mysql不支持】【语法】

select b.name,bo.* 
from boys AS bo 
full outer 
join beauty AS b 
on b.boyfriend_id = bo.id

交叉链接【笛卡尔乘积】

select b.name,bo.* 
from boys AS bo 
cross join beauty AS b

子查询★

/*
含义： 
出现在其他语句中的select语句，称为子查询或内查询 
外部的查询语句称为住查询或者外查询 

分类： 
按子查询出现的位置： 
	select后面： 
		仅仅支持【标量子查询】 
	from后面： 
		支持【表子查询】 
	where或having后面： 
		【标量子查询】 
		【列子查询】 
		【行子查询】 
	exists后面（相关子查询） 布尔 查看有没有值 
		【标量子查询】 
		【列子查询】 
		【行子查询】 
		【表子查询】 
按结果集的行列数不同： 
	标量子查询（结果集只有一行一列） 
	列子查询（结果集只有一列多行） 
	行子查询（结果集有一行多列）或（多行多列） 
	表子查询（结果集一般为多行多列） 
*/

where或having后面的【标量子查询】

/* 
	标量子查询（结果集只有一行一列） 
	列子查询（结果集只有一列多行） 
	行子查询（结果集有一行多列）或（多行多列） 
特点： 
	标量子查询刚在小括号内 
	子查询一般放在条件的右侧 
	标量子查询吗、，一般搭配着单行操作符使用 
	【< > <= >= = <>】 
	
	列子查询，一般搭配多行操作符使用 
	【in any/some all】 
	
	子查询的执行是优先于主查询执行，主查询的条件用到了子查询的结果 
*/

where

# 谁的工资比name的高 
select *
from 表名 
where salary > ( 
    select salary 
    from 表名 
    where last_name = 'name' 
)
# 返回job_id与141号员工相同，salary比143号员工多的员工 
# 1查询141号员工的jod_id 
select job_id 
from 表名 
where id=141 


# 2查询143号员工的
salary select salary 
from 表名 
where id = 143 

select name,job_id,salary from 表名 
where job_id = ( 
    select job_id 
    from 表名 
    where id=141 
) and salary > ( 
    select salary 
    from 表名 
    where id = 143
)

having

#查询最低工资大于50号部门最低工资的部门id和最低工资 

#1 查询50号部门的最低工资 
select min(salary) from 表名 
where 部门id = 50 

#2 查询每个部门的的最低工资 
select min(salary) , department_id 
from 表名 
gruop by department_id 

#3 合并 
select min(salary),department_id 
from 表名 
gruop by department_id 
having min(salary) > ( 
    select min(salary) 
    from 表名 
    where 部门id = 50 
);

列子查询(多行子查询)

/*
列子查询，一般搭配多行操作符使用 
【in/not in any/some all】
any/some 含义：任意一个 
all 含义：所有 
*/
select last_name 
from 表名 
where department_id in(
    select distinct department_id 
    from 表名 
    where location_id in(1400,1700)
);

行子查询（结果集有一行多列）或 （多行多列）

# 查询编号最小员工并且工资最高的的员工信息 
# 1 查询最小编号的员工 
select min(employee_id) 
from 表名 

# 2 查询工资最高员工 
select max(salary) 
from 表名 

# 3 查询员工信息 
select * 
from 表名 
where employee_id = ( 
    select min(employee_id) 
    from 表名 
) and salary = ( 
    select max(salary) 
    from 表名 
)

#另一种写法 
select * 
from 表名 
where (employees_id,salary)=( 
    select min(employee_id),max(salary) 
    from 表名 
)

select后面只支持的【标量子查询】

#查询没个部门的员工个数 
select d.*,(
    selectd count(*) 
    from employees e 
    where e.`department_id` = d.`department_id` 
) AS 个数 
from departments d

from后面的【表子查询】

#查询每个部门的的平均工资的工资等级 
# 1 查询每个部门的平均工资 
select AVG(salaty),department_id 
from employees 
group by department_id 

# 工资等级表 
select * 
from job_grades 

# 结合 
select avg.*,j.`grade_level` 
from (
    select AVG(salaty) AS a,department_id AS id 
    from employees 
    group by department_id 
) AS avg 
inner join job_grades AS j 
on avg.a between j.lowest_sal and j.highest_sal

exists后面【相关子查询】 布尔 查看有没有值★

/*
exists后面【相关子查询】 布尔 查看有没有值 
语法： 
exists(完整的查询语句) 

结果： true：1 false：0 

用 in 也能完成 
*/

select exists(select * from employees) # 结果：1 

select exists(select * from employees where salary < 0) #结果：0 

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

# in 
select department_name 
from departments d 
where d.`department_id` in ( 
    select department_id 
    from employees 
)

分页查询

/*
应用场景：当要显示的数据，一页显示不全，需要分页提交的sql请求 
语法： 
		select 查询列表 
		from 表 
		【join type join 表2 
		on 连接条件 
		where 筛选条件 
		group by 分组字段 
		having 分组后的筛选 
		order by 排序的字段】 
		limit offset，size 
		
		offset：要显示的条目的起始索引（mysql索引从0开始） 
		size：要显示的条目个数 

特点： 
		1，limit语句在查询语句最后 
		2，公式 
		(page-1)*size,size 
		要显示的页数 page， 每页的条目数size 
		select 查询列表 
		from 表 
		limit (page-1)*size,size; 
*/


# 查询前五条 
select * 
from 表 
limit 0,5 

select * 
from 表 
limit 5

# 查询11到25 
select *
from 表 
limit 10 ,15;

union【all】联合查询

/*
union 联合 合并：将多条查询语句的结果合并成一个结果 
union 自带【去重效果】 
union all 不去重 
语法：
	查询语句1 
	union 【all】 
	查询语句2 union 【all】 
	... 
	
应用场景： 
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时 

特点：
	1，要求多条查询语句的查询列数是一致的！ 
	2，要求多条查询语句的查询的每一列的类型和顺序最好一致(结果集字段名默认是第一条的字段名) 
	3，union关键字默认去重，如果使用union all 可以包含重复项 
*/

select * from where email like '%a%' 
union【all】 
select * from where department_id > 90; 

select id,cname,csex from t_ca where csex = '男' 
union【all】 
select t_id,tName,tGender from t_ua where tGender = 'male';

DQL语言的【书写】【执行】顺序

#	【书写】												【执行】
	select													from 
	from													join on 
	join on													where 
	where													group by 
	group by												having 
	having													select 
	order by												order by 
	limit													limit

----------当你发现自己的才华撑不起野心时，就请安静下来学习吧！----------