MySQL_SQL常用语句自我测试练习由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“测试常用sql常用语句”。
MySQLSQL常用语句自我测试练习
1.建库建表
--create database company
create 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 double,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','Dallas');
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);
insert 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 emp 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(7901,'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 values(6,5001,10000);
--------
3.必会的5个组函数：max,min,avg,sum,count 要牢牢记住
-------
4.练习：
①查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。
select empno,ename,sal,deptno from emp
where sal =(select max(sal)from emp);
②单条查询语句综合练习题：
薪水大于1200的雇员，按照部门编号进行分组，分组后的平均薪水必须大于1500，查询各分组的平均工资，按照工资的倒序进行排列。
select avg(sal)avg_sal, deptno
from emp
where sal > 1200
group by deptno
having avg_sal > 1500
order by avg_sal desc;
说明：此句基本上包含了SQL语句的子语句和排列顺序：select（要查询的字段）->from（从哪一张或哪几张表或视图）->where（过滤条件）->group by(having)（分组及条件）->order by（按哪个或哪几个字段进行升序或降序排列）。
注意：SqlServer4.1中可能不支持在order语句中使用组函数avg，报错说：invalid use of group function（错误提示和现象有点对不上）
解决办法：给avg(sal)起个别名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.losal and s.hisal);
⑤查询雇员名第2个字母不是a的雇员的姓名、所在的组名、工资所在的等级。
三张表的连接查询（先连接，再加上where语句进行过滤）
select ename,dname,grade
from emp e join dept d on(e.deptno = d.deptno)
join salgrade s on(e.sal between s.losal and s.hisal)
where ename not like '_a%';
⑥查询每个雇员和其经理的姓名
自连接：（事实上只有一张表，但把它当成两张表来用，使用别名来进行区分）
select e1.ename,e2.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);
⑧查询每个雇员的姓名及其所在部门的部门名（包括没有雇员的部门）
右外连接（会把右表中不符合连接条件的记录也显示出来）：
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.deptno
from emp e join
(select max(sal)max_sal,deptno from emp group by deptno)t
on(e.sal = t.max_sal and e.deptno = t.deptno);
⑩子查询2：查询每个部门平均工资所在的等级
select deptno,avg_sal,grade from salgrade
join(select deptno,avg(sal)avg_sal from emp group by deptno)t
on(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 deptno)t
join salgrade s on(t.avg_sal between s.losal and s.hisal);
?子查询3：查询每个部门内平均的薪水等级
先求每个人的薪水等级
select ename,deptno,grade from emp join salgrade s
on(emp.sal between s.losal and s.hisal);
再按组进行分组求平均
select deptno,avg(grade)from
(select ename,deptno,grade from emp join salgrade s
on(emp.sal between s.losal and s.hisal))t
group 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)t
on(emp.empno=t.mgr);
?子查询5：不准用库函数，求雇员表中薪水的最高值。
思路：两张完全相同的雇员表，左边一张，右边一张。拿左表中的薪水和右表中的薪水进行比较，左表中的最高薪水值必定不可能小于右表中的某一薪水值。
先求出emp表中最高薪水以下的所有薪水值
select distinct e1.sal from emp e1 join emp e2 on(e1.sal
不在此列的薪水值即为最高薪水值
select distinct sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on(e1.sal
?子查询6：平均薪水最高的部门的部门编号
①：先求出每个部门的平均薪水和部门号（把这个看成一张表）
select avg(sal)avg_sal,deptno from emp group 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：求平均薪水最高的部门的部门名称（在子查询6的基础之上）
说明：在实际应用中，达到这种程度的SQL语句已经算比较复杂了。
select dname from dept where deptno =
(select 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));
