“尚硅谷”Mysql数据库DQL

DB，DBMS，SQL的概念

DB：数据库(database)：存储数据的”仓库“。(并非是数仓)。
DBMS：数据库管理系统(Database Management System)。数据库是通过DBMS创建和操作的容器。例如mysql，oracle。
SQL：结构话查询语言(Structure Query Language)：专门用来与数据库通信的语言。

DQL数据查询语言

基本查询

“+” 的使用

在mysql中，"+“并不能对字段值进行拼接操作，”+"只能作为运算符来使用。
mysql字符拼接使用拼接函数concat
mysql数据库中字符串和字符并无区别

select查询

select查询的结果可当作一张虚拟的表。

#两个操作数都为数值型，做加法运算。
select 100+90;#190

#其中一方为字符型，试图将字符型数值转换成数值型。
#转换成功，继续做加法运算。
#转换失败，将字符型数值转换为0.
select '100'+90;#190
select 'jack'+90;#90

#其中一方为null，结果为null
select null+20;#null

查询函数

#查询mysql的版本号
select version();

ifnull

select ifnull(sal,0);
#如果sal的值为null，那么就将null替换为0

运算符 <>：不等于

模糊查询

like

通配符：" % " 任意多个字符，" _ "任意单个字符 。
当字符发生冲突，可以使用转意字符 " \ " 也可以使用ESCAPE函数。

select name from emp where name like '_$_%' escape '$';
#其中$就被定义为转意字符

select 
		*
from
		emp
where
		name like '__a%'#查询第三个字符为a的员工姓名

between and

包含临界值，等价于： >= and <=，
并且临界值不能够调换顺序。

in

判断某字段的值是否属于in列表中的某一项

select name from emp where name in('jack','tom');

is null

" = "或 " <> "不能用于判断null值。
is null或is not null 可以判断null值，且只能判断null。

安全等于" <=> "也可以判断null值，也可以判断常规数值

排序查询

order by

1.asc：升序 desc：降序。
2.order by 子句中可支持单个字段，多个字段，表达式，函数，别名。
3.order by 子句一般是放在查询语句的最后面，limit子句除外。

select * from emp order by sal desc;#倒叙排序，默认是升序

#按照姓名的长度显示员工姓名和工资【按函数排序】
select length(name) lengths,name,sal from emp order by length(name) desc;

#查询员工信息，要求先按工资排序，再按员工编号排序【按多个字段排序】
select * from emp order by sal,eid desc;

常见函数

一、单行函数

字符函数

#length:获取参数的字节数
select length('john');

# concat：拼接字符
select concat(last_name,'_' first_name) name from emp;

#upper\lower:转换大小写 
select upper('john');
select lower('HHADdsad');

#substr\subString 字符截取
#sql索引从1开始
select substr('huangfeihong',6);#eihong
select substr('shisanyi',1,3);#shi, 3指字符长度

#instr：返回子串第一次出现的索引，如果找不到返回0
select instr('klsdgiwenlafiwenwenwen','wen');#7

#trim：除去前后空格
select trim('   lsls      '); #lsls
#去除前后指定字符
select trim('a' from 'aaaaalsaaalsaaaa');#lsaaals

#lpad：用指定的字符实现左填充到指定长度
select lpad('aaa',5,'*');#aaa**
#如果指定长度大于原本字符长度，则截断原本字符
select lpad('aaa',2,'*');#aa
#rpad：用指定的字符实现右填充到指定长度
select rpad('aaa',5,'x');#xxaaa

#replace: 替换
select replace ('wwaaddt','w','a');#aaaaddt

数学函数

#round：四舍五入
select round(1.45);#1
select round(4.567,2);#4.57,保留两位小数

#ceil：向上取整,返回>=该参数的最小整数
select ceil(1.002);#2
select ceil(-1.002);#-1
#floor：向下取整，返回<=该参数的最大整数
select floor(-9.99);

# truncate：截断
select truncate(1.69999,1);#1.6

# mod：取余
select mod(10,3);#1

日期函数

#now 返回当前系统日期+时间
select now();#2020-09-03 45:56:11

#curdate 返回当前系统日期，不包含时间
select curdate();#2020-09-03

#curtime 返回当前时间，不包含日期
select curtime();#45:56:11

#获取指定的部分，年月日时分秒
select year(now());#2020
select year('1998-4-4');#1998
select month(now());#9
select monthname(now());#September

#str_to_date 将字符通过指定的格式转换成日期
select str_to_date('1998-3-2','%Y-%c-%d');#1998-03-02 
select str_to_date('4-3 1992','%c-%d %Y');#1992-4-3 00:00:00

#date_format 将日期转换成字符
select date_format(now(),'%yn年%m月%d日');#20年09月03日

流程控制函数

#if :if else效果
select if(10<5,'da',"xiao");#xiao

#case: switch case 效果
/*
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
else 要显示的值n或语句n;
end
*/
#查询工资，部门编号为20，30，40分别为工资的1.1倍，1.2倍，1.3倍
select sal,deptno,
case deptno
when 30 then sal*1.1
when 40 then sal*1.2
when 50 then sal*1.3
else sal
end
from emp;

# case ： 类似于多重if
/*case
when 条件1 then 显示的值1或语句1
when 条件2 then 显示的值2或语句2
else 显示的值n或者语句n
end
*/
/*
如果工资>20000 A级
工资>15000 B级
工资>10000 c级
否则，d级
*/
select sal,
case
when sal>20000 then 'a'
when sal>15000 then 'b'
when sal>10000 then 'c'
else 'd'
end
from emp;

其他

select version();#mysql版本号
select database();#当前数据库
select user();#当前的用户

二、分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数。
sum,avg,max,min,count

**特点：
1、sum、avg一般用于处理数值型，max、min、count可以处理任何类型。
2、以上分组函数都忽略null值。
3、可以和distinct搭配使用。
4、和分组函数一同查询的字段有限制,一同查询的字段要求是group by后的字段。
例：select avg(sal),name from emp;两个字段不能同时使用，avg的返回字段为一个，对应name为多个，无法进行匹配，语句虽不会报错，但是没有意义。
**

count(*):查询表的总行数
count(1):相当于在表中加入了一列1，统计1的个数，也就是表的总行数。

效率： 
MYISAM存储引擎下，count(*)的效率高。
INNODB存储引擎下，count(*)和count（1）效率差不多，比count(字段)高一些。

分组查询

group by 大都与分组函数进行连用。
可以使用having 对条件进行补充。

至于是否使用having，还是使用where，要根据条件是在分组之前进行还是在分组之后进行的。

select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal > 2000 order by deptno desc;

连接查询

一、sql92标准：只支持内链接

1、等值连接

1、多表等值连接的结果为多表的交集部分
2、n表连接，至少需要n-1个连接条件
3、多表的顺序没有要求
4、一般需要为表起别名

select name,deptname,city
from emp e,dept d,locations l
where e.deptno=d.deptno and d.loc = l.loc;

2、非等值连接

select sal,grade_level
from emp e,job g
where sal between g.low_sal and g.high_sal; 

3、自连接

select empno,name,empno,name
from emp e,emp m
where e.manager_id=m.empno;

sql99语法

内连接：[inner] join on

等值连接

select name,dname from emp e inner join dept d on e.deptno=d.deptno where name like '%e%';

select d.dname,count(*) count_1 from emp e 
inner join dept d on e.deptno=d.dpetno 
group by dname having count_1>3 
order by count_1 desc;

非等值连接
grade_level :工资级别

select count(*),grade_level
from emp e
join job_grades g 
on e.sal between 1000 and 5000
group by grade_level
having count(*) >20
order by grade_level desc;

自连接

select e.name,m.name
from emp e 
join emp m 
on e.manager_id = m.empno;

外连接

左外 : left [outer] join on
右外 : right [outer] join on
全外 : full [outer] join on
特点：

外连接的查询结果为主表中的所有记录
			如果从表中有和它匹配的，则显示匹配的值
			如果从表中没有和它匹配的，则显示null
			外连接查询结果=内连接结果+主表中有而从表没有的记录
全外连接：显示表中所有记录，不匹配的使用null值代替，mysql不支持

交叉连接 ：cross join on

就是一个笛卡尔积

select e.*,d.*
from emp e
cross join dept d;

子查询

含义：出现在其他语句中的select语句，称为子查询或内查                			询，外部的查询语句，称为主查询或外查询。
分类：
       select后：仅仅支持标量子查询
       from后：支持表子查询
       where或having后：标量子查询，列子查询，行子查询
       exists后：表子查询
按结果集的行列数不同：
				标量子查询(结果集只有一行一列)
				列子查询(结果集只有一列多行)
				行子查询(结果集有一行多列)
				表子查询(结果集一般为多行多列)

一、where或having后面

1、标量子查询(单行子查询)
2、列子查询(多行子查询)
3、行子查询(多列多行)

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用 > < >= <= = <>

列子查询，一般搭配多行操作符使用 in、any/some、all

标量子查询(单行子查询)

#查询公司工资最少的员工
select last_name,job_id,salary
from emp e
where salary=(
		select min(salary)
		from emp
);

#查询最低工资大于50号部门的最低工资的部门id和其最低工资
select min(sal) from emp where deptno=50; t1
select deptno,min(sal) min_sal from emp group by deptno having min_sal>t1;

列子查询

#查询location_id是1400或1700的部门号
select name 
from emp
where deptno in (
		select distinct deptno
		from dept
		where location_id in(1400,1700)
)

#返回其他工种中比job——id为'IT_PROG'工种任一工资低的员工号、姓名、job_id、sal
select distinct sal from emp where job_id = 'IT_RPOG'; t1
select empno,ename,job_id,sal from emp where sal<any(t1) 
and job_id <> 'IT_PROG';

行子查询

#查询员工编号最小并且工资最高的员工信息
select * from emp
where (empno.sal) = (
	select min(empno),max(sal)
	from emp
);

二、select后面

#查询每个部门的员工个数
select d.*,
(select count(*) from emp e
where e.deptno = d.deptno)
from deptno d;

三、from后面

必须起别名

#查询每个部门的平均工资等级
select ag_dep.*,g.grade_level
from (
select avg(sal) ag,deptno
from emp 
group by deptno) ad_dep
join job_grades g 
on ag_dep.ag between lowest_sal and highest_sal;

四、exists后(相关子查询)

exists表示是否存在
1或0
先执行主查询再执行子查询

select exists(select empno from emp);#1

select dname from dept d 
where exists(
	select * from emp e
	where d.deptno=e.deptno);

分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求。
limit 【offset】,size
offset：要显示条目的起始索引(起始索引从0开始)
size：要显示的条目个数

#查询11条-25条数据
select * from emp limit 10,15;

union联合查询

union：将多条查询语句的结果合并成一个结果。
特点：
1、要求多条查询语句的查询列数是一致的。
2、要求多条查询语句的查询的每一列的类型和顺序最好一致。
3、union默认是去重的，使用union all可包含重复字段

#查询部门编号>90或邮箱包含a的员工信息
select * from emp where email like '%a%'
union
select * from emp where deptno>90;