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;