以职员表 emp 和 部门表 dept 来讲解
实战学习

• 创建数据库

create database bbb

-- 指定使用此数据库
use bbb;

部门表

create table dept (
deptno int primary key,
dname nvarchar(30),
loc nvarchar(30))

insert into dept (deptno, dname, loc) values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc) values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc) values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc) values (40, 'OPERATIONS', 'BOSTON')

职员表

create table emp (
  empno int primary key,
  ename nvarchar(30),
  job nvarchar(30),
  mgr int,
  hiredate datetime,
  sal numeric(9,2),
  comm numeric(9,2),
  deptno int)

insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500.00,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7566,'JONES','MANAGER',7839,'1981-4-2',2975,null,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-4-19', 3000, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7876, 'ADAMS', 'CLERK', 7788, '1987-5-23', 1100, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10);

SQL 语句

• 删除 delete

--删除雇员表中工资为2000和2500的记录
delete from emp where sal=2000 or sal=2500

• 查询 select

select * from emp
select * from dept

--查询指定列
select deptno from dept

--查询SMITH的薪水、工作和所在部门
select sal,job,deptno from emp where ename='SMITH'--在sql server 中不区分大小写,在oracle中区分
select sal,job,deptno from emp where ename='SmiTH'--此语句和上一句效果一样
select sal,job,dname  from emp, dept where emp.ename='smITH' and emp.deptno=dept.deptno 

--区分重复行->>统计共有多少部门编号
select distinct deptno from emp

--> 显示每个雇员的年工资=13个月工资+奖金
--> ifnull(comm,0),判别为null，为null返回0，不为null返回本身数值
--> 年工资是指定的列的别名 
--> 别名，即年工资 加不加单引号 ，或双引号 ，或啥也不加 都可以,即下面三句话效果一样
select ename,sal*13+ifnull(comm,0)*13 年工资 from emp
select ename,sal*13+ifnull(comm,0)*13 '年工资' from emp
select ename,sal*13+ifnull(comm,0)*13 "年工资" from emp

--显示工资高于3000的员工
--下面这两句话的查询结果是不一样的，>和>=差一个3000
select ename from emp where sal>3000
select ename from emp where sal>=3000

--查找1982-1-1后入职的员工
select * from emp where hiredate>'1982-1-1'

--查找工资在2000-2500员工的情况
--1、方法一
select * from emp where sal>2000 and sal<2500
--2、方法二 ->注意此方法查询时包括边界，即包括2000 和2500
select * from emp where sal between 2000 and 2500

--查询首字母为s的员工和员工工资
select ename,sal from emp where ename like 's%'--%表示0到多个字符

--显示第三个字母为 o 的员工姓名和工资
select ename,sal from emp where ename like '__o%'--_(下划线)表示单个字符 

-- 在where条件中使用in
-- 如何显示empno为123、345、800..的情况？
select * from emp where empno=123 or empno=345 or empno=800--这种方法效率低下
-- 一般我们用in这个关键字，效率高
select * from emp where empno in(123,345,800,7499)

--使用 is null 操作符
select * from emp where mgr is null--千万不能等于即mgr='null'

--使用逻辑操作符号
--查询工资高于500或是岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为j
select * from emp where (sal>500 or job='MANAGER') and ename like 'j%'

--使用order by语句 ->默认是asc从低到高排列，即升序排列
        -->desc从高到低排列，即降序排列
--按照工资从低到高的顺序显示员工的信息
select * from emp order by sal asc --从低到高排列,在此处asc写不写都可以，因为默认就是asc
select * from emp order by sal desc--从高到低排列

--按照入职先后顺序排列
select * from emp order by hiredate

--按照部门号升序而雇员工资降序排列
--order by可以根据不同字段排序,先排第一个字段，再排第二个字段...
select * from emp order by deptno,sal desc

--按照部门号和雇员工资降序排列
select * from emp order by deptno desc,sal desc

--使用列的别名，年薪排序，->>先把年薪算出来，再排序
select ename,sal*13+ifnull(comm,0)*13 年薪 from emp order by 年薪 desc

-- 统计每个部门多少人
select count(*) from emp group by deptno

• 复杂查询

--1、如何显示所有员工中的最高工资和最低工资？
select max(sal) 最高工资 ,min(sal) 最低工资 from emp

--2、显示所有员工的平均工资和工资总和？
select avg(sal) 平均工资 ,sum(sal) 工资总和 from emp

--3、计算共有多少员工？
select count(empno) 员工数量 from emp
select count(*) 员工数量 from emp

--4、显示工资最高的员工的姓名、工作岗位？--SQL语句执行时是从右到左
select ename,job from emp where sal=(select max(sal) from emp)

--5、显示工资高于平均工资的员工信息？
select ename,sal from emp where sal>(select avg(sal) from emp)

• group by 和 having 字句

--1、如何显示每个部门的平均工资和最高工资？-->>group by用于对查询的结果分组统计
select avg(sal) 平均工资,max(sal) 最高工资,deptno from emp group by deptno

--如何显示部门名称？？？ -->>多表查询？？
select avg(sal) 平均工资,max(sal) 最高工资,dname,emp.deptno from emp,dept 
 where emp.deptno=dept.deptno group by emp.deptno,dname


--2、如何显示每个部门的每种岗位的平均工资和最低工资？
select avg(sal) 平均工资,min(sal) 最低工资,deptno,job from emp group by deptno,job order by deptno

--3、显示平均工资低于2000的部门和它的平均工资？
--having 往往和 group by 结合使用,可以对分组查询到的结果进行筛选
select deptno,avg(sal) from emp group by deptno having avg(sal)<2000

--注意->>>如果在select 语句中同时包含有group by、having、order by 那么它们的顺序是 group by、having、order by 

• 复杂查询，多表查询

-->>>多表匹配原理笛卡尔集，从右往左匹配

--如果两张表都有相同的字段，则需要带表名（或表的别名） ->不是相同的字段，表名可带可不带
--显示雇员名、雇员工资和其所在部门的名字，部门号 <下两句话效果一样>
select emp.ename,emp.sal,dept.dname,dept.deptno from emp,dept where emp.deptno=dept.deptno
select ename,sal,dname,dept.deptno from emp,dept where emp.deptno=dept.deptno

-->>用表的别名代替表名（在表名较长较复杂时用），注意->要用别名则此语句都用别名
select ename,sal,dname,d.deptno from emp e,dept d where e.deptno=d.deptno

--显示sales部门位置和其员工的姓名
select dept.loc,emp.ename from dept,emp where dept.dname='sales' and emp.deptno=dept.deptno 

--如何显示部门号为10的部门名字、员工名和工资
select dept.dname,emp.ename,emp.sal from dept,emp where dept.deptno=10 and emp.deptno=dept.deptno

--显示雇员名，雇员工资及所在部门的名字，并按部门排序
select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno order by dept.dname 


--？显示某个员工的上级领导的姓名->比如显示‘ford’的上级
--首先知道'frod'的上级编号
select mgr from emp where ename='ford'
select ename from emp where empno=(select mgr from emp where ename='ford')

--思考->如何显示公司每个员工名字和他上级的名字
-->把emp看作两张表 worker 和 boss
select worker.ename 员工,boss.ename 上级 from emp worker,emp boss where boss.empno=worker.mgr

• 子查询

--一般单行子查询用 = ，多行子查询用 in

--1、单行子查询
--思考：如何查找与’smith’同一部门的所有员工
select * from emp where deptno=(select deptno from emp where ename='smith')
select * from emp where deptno in (select deptno from emp where ename='smith')

--2、多行子查询
--如何查询和部门10的工作相同的雇员的名字，岗位、工资、部门号  ->>如何排除10号部门?
select ename,job,sal,deptno from emp 
where job in (select distinct job from emp where deptno=10) 
and deptno<>10  --在此语句中把 in 换为 = 会出错
--不等号 <> 换为 != 也可以

-- 3、在from 子句中使用子查询
--如何显示高于部门平均工资的员工姓名、工资、部门平均工资
-- 3.1、先查找部门平均工资
select avg(sal),emp.deptno aa from emp group by deptno
-- 3.2、把上面的查询结果当作一张临时表对待
--查找高于部门平均工资员工姓名、工资、部门平均工资、部门编号
select emp.ename,emp.sal,qq.aa,emp.deptno from emp,
(select avg(sal) aa,emp.deptno from emp group by deptno) qq --qq为别名->>必须取
where emp.sal>qq.aa and emp.deptno=qq.deptno

注意点

• isnull VS ifnull

isnull(exper)  判断exper是否为空，是则返回1，否则返回0
ifnull(exper1,exper2) 判断exper1是否为空，是则用exper2代替

• --注意->>>如果在select 语句中同时包含有group by、having、order by 那么它们的顺序是 group by、having、order by