MySQL_SQL常用语句自我测试练习

1、MySQL SQL常用语句自我测试练习1.建库建表-create database companycreate database company;use company;-create tables-部门表create table dept(deptno int(3 primary key,dname varchar(14,loc varchar(13;-雇员表create table emp(empno int(4 not null primary key,ename varchar(10,job varchar(10,mgr int(4,hiredate datetime,sal doub

2、le,comm double,deptno int(3,foreign key(deptno references dept(deptno;-工资级别表create table salgrade(grade int(3 primary key,losal int(3,hisal int(3;-2.插入数据(进行初始化use company;-往部门表中查数据insert into dept values(10,'Accounting','New York'insert into dept values(20,'Research','Dal

3、las'insert into dept values(30,'Sales','Chicago'insert into dept values(40,'Operations','Boston'insert into dept values(50,'Admin','Washing'-往雇员表中插数据insert into emp values(7369,'Smith','Clerk',7902,'1980-12-17',800,0,20;inse

4、rt into emp values(7499,'Allen','Salesman',7698,'1981-2-20',1600,300,30;insert into emp values(7844,'Turner','Salesman',7499,'1981-9-8',1500,0,30;insert into emp values(7698,'Tom','Manager',0,'1981-9-8',6100,600,40;insert into e

5、mp values(7876,'Adams','Clerk',7900,'1987-5-23',1100,0,20;insert into emp values(7900,'James','Clerk',7698,'1981-12-3',2400,0,30;insert into emp values(7902,'Ford','Analyst',7698,'1981-12-3',3000,null,20;insert into emp values(7

6、901,'Kik','Clerk',7900,'1981-12-3',1900,0,30;-往工资级别表中插数据insert into salgrade values(1,700,1200;insert into salgrade values(2,1201,1400;insert into salgrade values(3,1401,2000;insert into salgrade values(4,2001,3000;insert into salgrade values(5,3001,5000;insert into salgrade

7、values(6,5001,10000;-3.必会的5个组函数:max,min,avg,sum,count 要牢牢记住-4.练习:查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。select empno,ename,sal,deptno from empwhere sal = (select max(sal from emp;单条查询语句综合练习题:薪水大于1200的雇员,按照部门编号进行分组,分组后的平均薪水必须大于1500,查询各分组的平均工资,按照工资的倒序进行排列。select avg(sal avg_sal, deptnofrom empwhere sal > 12

8、00group by deptnohaving avg_sal > 1500order by avg_sal desc;说明:此句基本上包含了SQL语句的子语句和排列顺序:select(要查询的字段->from(从哪一张或哪几张表或视图->where(过滤条件->group by(having(分组及条件->order by(按哪个或哪几个字段进行升序或降序排列。注意:SqlServer4.1中可能不支持在order语句中使用组函数avg,报错说:invalid use of group f unction(错误提示和现象有点对不上解决办法:给avg(sal起个别

9、名avg_sal,这样在order语句中就直接使用这个别名等值连接:查询每个雇员和其所在的部门名select ename,dname from emp,dept where (emp.deptno = dept.deptno;或者(推荐(on中就写连接条件,where中就写过滤条件,各司其职select ename,dname from emp join dept on(emp.deptno = dept.deptno;非等值连接:查询每个雇员姓名及其工资所在的等级select ename,grade from emp e join salgrade s on(e.sal between s.

10、losal and s.hisal;查询雇员名第2个字母不是a的雇员的姓名、所在的组名、工资所在的等级。三张表的连接查询(先连接,再加上where语句进行过滤select ename,dname,gradefrom emp e join dept d on(e.deptno = d.deptnojoin salgrade s on(e.sal between s.losal and s.hisalwhere ename not like '_a%'查询每个雇员和其经理的姓名自连接:(事实上只有一张表,但把它当成两张表来用,使用别名来进行区分select e1.ename,e2.

11、ename from emp e1,emp e2 where (e1.mgr = e2.empno;或者:(推荐用join语句select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno;查询每个雇员和其经理的姓名(包括公司老板本身(他上面没有经理左外连接(会把左表中不符合连接条件的记录也显示出来:select e1.ename,e2.ename from emp e1 left join emp e2 on(e1.mgr = e2.empno;查询每个雇员的姓名及其所在部门的部门名(包括没有雇员的部门右外连接(会把

12、右表中不符合连接条件的记录也显示出来:select ename,dname from emp e right join dept d on(e.deptno = d.deptno;子查询1:查询每个部门中工资最高的人的姓名、薪水和部门编号先求出每个部门中的最高工资:select max(sal max_sal,deptno from emp group by deptno在使用连接查询:select ename,sal,e.deptnofrom emp e join(select max(sal max_sal,deptno from emp group by deptno ton(e.sal

13、 = t.max_sal and e.deptno = t.deptno;子查询2:查询每个部门平均工资所在的等级select deptno,avg_sal,grade from salgradejoin(select deptno,avg(sal avg_sal from emp group by deptno ton(t.avg_sal between salgrade.losal and salgrade.hisal;或者:select deptno,avg_sal,grade from(select deptno,avg(sal avg_sal from emp group by de

14、ptno tjoin salgrade s on(t.avg_sal between s.losal and s.hisal;?子查询3:查询每个部门内平均的薪水等级先求每个人的薪水等级select ename,deptno,grade from emp join salgrade son(emp.sal between s.losal and s.hisal;再按组进行分组求平均select deptno,avg(grade from(select ename,deptno,grade from emp join salgrade son(emp.sal between s.losal an

15、d s.hisal tgroup by deptno;?子查询4:查询雇员中有哪些人是经理人:select ename from emp where empno in(select distinct mgr from emp;或者:select ename from emp join(select distinct mgr from emp ton(emp.empno=t.mgr;?子查询5:不准用库函数,求雇员表中薪水的最高值。思路:两张完全相同的雇员表,左边一张,右边一张。拿左表中的薪水和右表中的薪水进行比较,左表中的最高薪水值必定不可能小于右表中的某一薪水值。先求出emp表中最高薪水以下

16、的所有薪水值select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal;不在此列的薪水值即为最高薪水值select distinct sal from emp where sal not in(select distinct e1.sal from emp e1 join e mp e2 on(e1.sal < e2.sal;?子查询 6：平均薪水最高的部门的部门编号 ：先求出每个部门的平均薪水和部门号（把这个看成一张表） select avg(sal avg_sal,deptno from emp group

17、 by deptno; ：再求出平均薪水最高值（把这个看成一个值） select max(avg_sal from (select avg(sal avg_sal,deptno from emp group by deptno t; ：对表使用条件进行查询即可 select avg_sal,deptno from (select avg(sal avg_sal,deptno from emp group by deptno t1 where avg_sal= (select max(avg_sal from (select avg(sal avg_sal,deptno from emp group by deptno t2; 说明:在 Oracle 中可以使用组函数嵌套来稍微简化 SQL 语句的复杂程度（最多是两层嵌套）： select max(avg(sal from emp group by deptno;来求出平均薪水最高值 注意：这种组函数嵌套在 MySQL 中不可以用 ?子查询 7：求平均薪水最