SQL

SQL概述

sql(structured query language)是一套程序员和底层数据库交流的一套标准

SQL的分类

数据查询语言（DQL-data query language）

代表关键字select

数据操纵语言DML（data manipulation language）

代表关键字 insert delete update

数据定义语言（DDL data definition language）

代表关键字 create drop alter

事务控制语言（TCL transactional contral language）

代表关键字：commit,rollback

数据控制语言（DCL data control language）

代表关键字 grant revoke

 

SQL语句练习

查询

查询员工姓名

select ename from emp;

查询员工编号，姓名，上级领导编号

setect ename,empno,mgr from emp；

select * from emp; //查询全部内容用*，但是不建议使用

查询年薪

select empno,ename,sal*12 from emp;//虽然sal*12,但是不会修改数据库的内容

把查询后显示的字段重命名

select ename,empno,sal*12 as yearsal from emp;//as可以省略不写
select ename,empno,sal*12 yearsal from emp;
select ename,empno,sal*12 ‘年薪’ from emp;//重命名时，新的名字是中文的话要加单引号

注意：1.sql语句不区分大小写

           2.sql语句以分号结尾

           3.sql语句中字符串是以单引号包起来的

条件查询需要用到where语句，where必须放到from语句表的后面

查询工资等于5000的员工
select empno,ename,sal from emp where sal=5000;//先查询，后过滤


查询工资大于等于3000，并且小于等于5000
select empno,ename，sal from emp where sal>3000 and sal<=5000;
select empno,ename，sal from emp where sal between 3000 and 5000;//必须前小后大
select empno,ename，sal from emp where sal between 5000 and 3000;//错误
between and 是闭区间

select ename form emp where ename between 'A' and 'k';
//between and 应用在字符上面，就是前闭后开，，可以动手试一下,就是不包含k

查找补助为空的员工
select ename,comm,from emp where comm =NULL;//NULL不是一个值，不能用等号
select ename,comm,from emp where comm is NULL;//用is

查询补助不为空的员工 
select ename,comm,from emp where comm is not NULL;
select ename,sal from emp where sal>=1500 and sal<=3000;

//查找工作岗位为SALEMAN和MANAGER的员工
select ename,job form emp where where job='SALEMAN' and job='MANAGER';

//这条语句查找的是工作岗位为SALEMAN并且为MANAGE的员工,应该用or
select ename,job form emp where where job='SALEMAN' or job='MANAGER';

and和or同时出现，and的优先级高
//找出工资大与1800的员工，要求部门编号是20或者是30de
select ename,empno,sal,deptno form emp where sal>1800 and deptno=20 or deptno=30;//err
select ename,empno,sal,deptno form emp where sal>1800 and (deptno=20 or deptno=30);//0k

//加括号就行

//找工作岗位为SALEMAN和MANAGER的员工
select ename,job from emp where job in('MANAGER','SALERMAN');


select ename,sal from emp where sal in(1500,5000);//不是区间

select ename,sal from emp where sal not in(1500,5000);


//查找所有名字里面含有o的
select ename from emp where enmae like '%0%';//模糊查询%表示0~n个占位字符
//查找名字第一个字母为z的
select ename form emp where ename like 'z%';
//查找名字最后一个字符是g的
select ename form emp where ename like '%g';
//查找第二个字母是a的名字
select ename form emp where ename like '_a%';
//查找第三个字母是a的名字
select ename form emp where ename like '_ _a%';

//查找名字中倒数第二个字母是a的
select ename form emp where ename like '%a_';

模糊查询：

%代表0——n个任意占位字符
_代表任意的一个占位字符

排序

select ename ,sal from emp order by sal;//默认升序排序
select ename ,sal from emp order by sal asc;//还是升序
select ename ,sal from emp order by sal desc;//降序


如果工资相同，可以再加其他的字段排序
select ename ,sal from emp order by sal desc,ename asc;
//靠前的字段起决定性作用，靠后的可能用不上

select ename,sal from emp order by 2;     //2的意思是说按照第二个字段排序，按sal排序
select ename,sal from emp order by sal;   //健壮性差
select sal,ename from emp order by 2;     //2的意思是说按照第二个字段排序，按ename排序
//这样写健壮性差,数字是查询结果中字段的编号

 数据处理函数/单行处理函数

这些函数在不同的数据库管理系统不通用

select lower(ename) from emp;//将查询结果显示为小写，并没有更改底层数据库

select substr(ename,1,1) form emp;
//第二个字母是A的
select enmae form emp where substr(ename,2,1)='A';
select length(ename) from emp;
select ename from emp where ename=trim('SMITH');

select round(123.456);//默认保留整数

select round(123.456，0)；//同上
select round(123.456，1)；//保留一位小数  123.5
select round(123.456，2)；//保留两位小数  123.46

select round(123.456，-1);//120
select round(123.456,-2);//100
select round(153.456,-2);//200

select rand();

//查找员工的名字和补助，补助为空的显示0
select ename,ifnull(comm,0) from emp;
select ename,ifnull(comm,0) as comm from emp;

//在数据库有空值参加数学运算，最后结果还为空，，为了避免空值出现就要用ifnull;

//计算年薪（工资+补助）*12
select enmae,(sal+comm,0))*12 from emp;//补助可能为空，所以计算可能有误

select enmae,(sal+ifnull(comm,0))*12 from emp;

1.mysql日期处理
1.1 每一个数据库处理日期的时候采用的机制都是不同的，日期处理都有一套自己的机制，所以在实际开发中
    表中的字段定义为DATA类型，这种情况很少，因为一旦使用日期类型。那末程序将不能通用，在实际开发   中，一般会使用‘日期字符串’来表示日期。 

2.mysql数据库管理系统中对日期的处理提供了两个重要函数
   str_to_date
   date_format
3. str_to_date//将日期字符串字转日期类型  varchar ----->date
   该函数的执行结果是data类型，还函数的使用格式、

     str_to_date('日期字符串'，'日期格式');
mysql中的日期格式：
%Y %m %d %H %i %s  年月日时分秒
案例：
查询1981年12/17入职的员工
select enmae  ,hriedate from emp where hiredate='1980-12-27';//类型自动转换，字符串转换成日期格式，mysql默认的日期格式 %y-%m-%d,以上的如期字符串‘1980-12-17’正好和默认的日期格式一样，存在了自动类型转换，自动将如期字符串转换为日期类型。 

select enmae , hriedate from emp where hiredate='12-27-1980';
//'12-27-1980'这个日期字符串和mysql默认的日期格式不同，hiredate是日期类型，'12-27-1980'是一个字符串varchar类型。类型不匹配，所以无法查询结果，并且有警告【错误】发生。
纠正以上sql语句的错误

select enmae , hriedate from emp where hiredate=str_to_date('12-27-1980','%m-%d-%Y');


//select enmae  ,hriedate from emp where hiredate='1980-12-27';

select enmae  ,hriedate from emp where hiredate=str_to_date('1980-12-27',‘%Y-%m-%d’);

默认是这个所以能够发生类型转换

str_to_da通常使用在插入操作中，字段是date类型，不接受字符串varchar类型，需要先通过该函数将varchar变为date类型才可以。


date_format  :将日期类型date转换为具有特定格式的字符串varchar
该函数的语法格式：date-format(日期类型数据，‘日期格式’);

案例:查询员工的入职日期，以‘%m-%d-%Y’的格式显示到窗口中
select ename,date_format(hirdate,'%m-%d-%Y') as hirdate from emp;

案例：查询员工日期以’10/12/1980‘的格式显示在窗口中
select ename, date_foemat(hridate,'%m/%d/%Y') as hridate from emp;
不加转换直接查的话会有自动类型转换，转化成默认的日期方式


以下两个 sql语句的执行方式完全相同
select ename,hridate form emp;[这里的hridate自动转换成varchar类型，采用'%Y-%m-%d'格式];
select ename,date_format(hridate,'%Y-%m-%d') as hridate from emp;

date_format主要使用早查询操作中，客户需要日期以特定格式展示的时候，需要使用该函数。

 

分组函数/聚合函数/多行处理函数

（一行输入对应一行输出，是单行处理函数）（多行处理函数，是多行对应一行结果）

//求所有员工的工资和
select sum(sal) form emp;
//求所有员工的工资的平均值
select avg(sal) from emp;
//求记录数
select count（ename) from emp;
select count (comm) from emp;

结论：min max avg sum count 这些函数会自动忽略空值，不需要手动加where条件排除空值
count 后面可以写字段名，也可以写*
select count（*） from emp where xxx;
//查找符合条件的所有记录数

//分组函数不能直接使用在where语句中
select ename.sal from emp where sal>avg(sal);//err

 

去重

distinct 查询结果中重复的去掉

结论：distinct 只能出现在所有字段的最前面

两个字段联合起来去重，，也可以使用多个字段连个去重
select distinct deptno,job from emp;

分组查询

1.分组查询涉及到的两个子句——group by

                                              ——having

2.group by 

2.1.order  by是指通过哪个或者哪些字段进行排序

      order by 是指通过哪个或者哪些字段进行分组

找出每个工作岗位的最高薪水

先按照工作岗位分组，然后使用max求最高薪水

select max(sal) from emp group by job;//先按照job分组 然后对每组使用max分组

//工作岗位可以写在select后面吗？可以正好工作岗位也是五个不同的值，正好等于分组数
select job,max(sal) from emp group by job;//ok

enmae可以加到select后面吗？可以，但是查询没有意义
select ename，job,max(sal) from emp group by job;//error
//mysql中可以，语法在这方面比较松散，不严格，但查询出来的结果没有实际意义

重点：如果Dql语句中有group by 子句，那末select 后面只能根参加分组的字段，或者是分组函数，其他字段不能跟

案例：计算每个部门的平均薪水
select  deptno,avg(sal) as avlsal form emp group by deptno;


案例：计算出（不同部门的）不同工作岗位的最高薪水
select deptno , job,  max(sal) from emp group by  deptno job;//ok


案例：找出每个工作岗位的最高薪水，除manager之外
select job,max(sal) from emp where job!='manager' group by job;

案例：找出每个工作岗位的平均薪水，要求显示平均薪水大于1500的；
select job,avg(sal) from emp where avg(sal)>1500 group by job;//error
分组函数是要先分组在查询，分组在where后面还没分组，不能查询

having  和where功能相同where在group by之前完成过滤
        having 在group by 之后完成过滤
注意：尽量在where中过滤，无法过滤的数据，，通常都是需要先分组之后再过滤的，这个时候可以选择使用having，效率问题。

一个完整的DQL语句总结
select 
where
group by
having 
order by
第一：以上的关键字顺序不能变，严格遵守
第二：执行顺序：
      1.from 从某张表中检索数据
2.where 经过某条件进行过滤
3.group by 然后分组
4.having 分组之后不满意在过滤
5.select 查询出来
6.order by 排序输出

   

连接查询

1.什么是连接查询？
--查询的时候只从一张表中检索数据，成为单表查询
--在实际开发中数据并不会值存储在一章表中，是同时存储在多张表中的，这些表和表之间
存在关系，我们在检索的时候是需要将多张表联合起来取得有效数据，这种多表查询被称为
连接查询或者叫跨表查询。
2.连接拆寻根据 出现的年份分类：
-sql92
-sql99[更新语法，主要掌握这种语法]
DBA:DataBase Administrator [数据库管理员]

3.连接查询根据连接方式可以分为 
-内链接
  *等值连接
  *非等值连接
  *自连接
-外连接
  *左外连接（左连接）
  *右外连接（右连接）
-全连接（使用少）

4.当多张表进行连接查询，若没有任何条件进行限制，会发生什莫现像？
案例：查询每一个员工所在的部门名称，要求最终显示员工名和对应的部门名
emp 员工表  dept 部门表
主要分析：多张表连接查询，如没有任何条件限制，会发生什么？
select ename ，dname form emp,dept;
小知识点：在进行多表连接查询时，尽量给表起别名，这样效率高，可读性高
一张表的一个记录会和另一张表的所有记录进行匹配。
结论：若两张表进行连接拆查询的时候，没有任何条件限制。最终的查询结果总数是
两张表记录条数的乘积，这种现象被称为笛卡尔积现象，必须在进行表连接的时候添
加限制条件

5.案例：查询每一个员工所在的部门名称，要求最终显示员工名称和对应 的部门名称
sql92语法，内链接中的等值连接
select e. ename,d.dname form emp e,dept d  where e.deptno=d.deptno;
这个是把表连接操作放在where后面
sql99语法，
select e.ename,d.dname from emp e join dept dept d on e.deptno=d.deptno;
select e.ename,d.dname from表连接操作 where xxx; 
sql99语法优点：表连接独立出来，结构更加清晰，对表连接不满意的话。可以追加where
进行过滤。
select e.ename,d.dname from emp e  inner join dept dept d on e.deptno=d.deptno;
（inner 可以省略不写）
注意：在连接查询时虽然使用了限制，但是匹配的次数并没有减少，还是乘积，只不
过这一次的结果都是有效记录

6.案例：找出每一个员工对应的工资等级，要求显示员工名，工资，工资等级
sql92语法；内链接的非 等值
select e.ename,e.sal,s.salgrade from emp e,salgrade s 
where  e.sal between s.losal and s.hisal;

sql99语法；内链接的非 等值
select e.ename,e.sal,s.salgrade from emp e join salgrade s 
on e.sal between s.losal and s.hisal;

7.找出每个员工的上级领导，要求显示员工名以及对应的领导名
员工的领导号和领导的员工号相等（一张表中查找）
SQL99:内链接中的自连接（一张表看成两张表）
select a.ename empname,b.ename lerdername  from emp a join emp b 
on a.mgr=b.empno;//inner可以省略

8.案例：找出每一个员工对应的部门名称 ，要求部门名称全部显示（员工表，部门表）
SQL99语法：外连接中有右外连接
select e.ename ,d.dname form emp e right outer join dept d on  
edeptno =d.deptno;（outer可以省略）

SQL99语法：外连接中有左外连接
select e.ename ,d.dname form  dept dleft  outer join emp e on  
edeptno =d.deptno;（outer可以省略）
内连接：A表和B表能够完全匹配的记录查询
外连接：A表和B表能够完全匹配的记录查询出来之外，其中一张表的记录无条件
的完全查询出来，对方表没有匹配的记录，会自动模拟出NULL与之匹配，这种
查询被称为外连接。
外连接的查询结果数>=内链接的查询结果数

注意：任何一个右外链家而都可以写成左外连接，任何一个左连接页同样可以写成右连接

为什莫inner outer 可以省略，加上去有什么好处？
因为区分内链接外联接依靠的不是这些关键字，而是sql 语句中是否存在left right 这些关键字
，若存在则一定是外连接，其他都是内链接。加上去的好处时增加可读性。

select a.enmae empname ,b.ename leadername from emp a left join emp b on a.mgr
=b.empno;

10.案例：找出每一个员工对应的部门名称，以及该员工对应的工资等级，要求显示
员工名，部门名，工资等级。(多表连接)
a join b on 条件 join c on 条件;
select  xxx from a join b on 条件 join c  on  条件；
原理：

a先与b连接之后，然后a与c连接

select 
e.ename ,d.dname,s.grade
 from
 emp e 
join 
dept d 
on 
e.deptno=d.deptno
join 
salgrade s 
on 
e.sal between s.losal and s.hisal;