MySQL SQL常用语句自我测试练习

binghai03

　　本文用MySQL数据库，对于数据库关键就是查询语句，查询分为简单查询 单表内，复合查询 多表
　　分析好了要查询的数据，确定需要几张表，根据语义仔细分析，应该也不是很难的。加油！！
　　平时注意练习，今天学会了，过几天不看就又得从零开始了，着实比较蛋疼。。
　　1、首先建库，建表 搭建练习的环境

1、建库建表

--create database company

create database company;

use company;

2、创建表--部门表

　　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)
　　);

3、.插入数据（进行初始化）

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);

-------------------------------------------------------------------------------4、必会的5个组函数：max,min,avg,sum,count 要牢牢记住

  分组函数，一定要找一个分组的依据，根据部门编号等，所以组函数只能跟一个字段，用来分组，having是用来筛选分组以后的数据，只能用于组函数，一般字段信息的过滤用 where。

-------------------------------------------------------------------------------

5、练习：

①查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。

首先分析是否都在一个表中，如果要选的，条件都在一个表中就是单表查询

否则为多表查询

　　select empno,ename,sal,deptno from emp
　　where sal = (select max(sal) from emp);

②单表查询语句综合练习题：

　　薪水大于1200的雇员，按照部门编号进行分组，分组后的平均薪水必须大于1500，查询各分组的平均工资，按照工资的倒序进行排列。

分析：首先搭好架子，

Select      //要取的字段

From tableName //单个表，或多张表

Where     //简单判断

Group By //用于分组

Having    //用于对组函数进行判断

Order By //排序

Limit      //分页

薪水大于1200               where sal >1200

按照部门编号进行分组   group by deptno

分组后的平均薪水必须大于1500 组函数 avg(sal)>1500

按照工资的倒序进行排列 order by avg(sal) desc

　　select avg(sal) avg_sal, deptno
　　from emp
　　where sal > 1200
　　group by deptno
　　having avg_sal > 1500
　　order by avg_sal desc;

或者

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 by语句中使用组函数avg，报错说：invalid use of group function（错误提示和现象有点对不上）
　　解决办法：给avg(sal)起个别名avg_sal，这样在order语句中就直接使用这个别名

③等值连接（部门编号相等作为条件）：

　　查询每个雇员和其所在的部门名

   分析：雇员表只有部门编号，要查询部门名，所以必须是在两张表(emp,dept)中查询，其中雇员表的（deptno）外键关联部门表

如果不指定 where条件，将会形成笛卡尔积，以deptno为参考列

　　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);

④非等值连接（没有相等的条件，只是在一个范围内）：

　　查询每个雇员姓名及其工资所在的等级

分析：查询 姓名-工资等级 涉及两张表 emp，salgrade，但是两张表没有共同的字段，不能建立等值连接，只能拿sal 和 salgrade表的 losal，hisal进行比较，根据是否在某一个范围内，确定工资等级

　　select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);

select ename,grade

From emp,salgrade s

　　//写到这个地方，产生了笛卡尔积，必须通过过滤条件(where 或者 上面的 on)去掉笛卡尔积。。

where emp.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 ename,dname

from emp,dept

where emp.deptno=dept.deptno and ename not like '_a%'

第二步，在此基础上求出工资等级

select ename,dname,grade

from emp,dept,salgrade s

where (emp.deptno=dept.deptno and ename not like '_a%') and (sal between

s.losal and s.hisal);

我自己写的看着不是很清晰，应该就用join on 进行连接条件的判断，where进行结果的过滤。

⑥查询每个雇员和其经理的姓名

　　自连接：（事实上只有一张表，但把它当成两张表来用，使用别名来进行区分）
　　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);

下面是我写的，是错误的：

select ename,sal,deptno

from emp

group by deptno

having max(sal)

select ename,sal,deptno

from emp

group by deptno 这样写虽然也没报错，但是分组一定要和分组函数一起使用，否则没有任何意义，这个查询结果就是从部门编号 20,30,40中的人员中分别选出一位。

上面我又加了一条having max(sal) 这是没有任何意义的，having 一定要加判断条件，而且是组函数的判断条件，不是一般字段的过滤条件

10子查询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);

  11子查询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;

如果不加上面的t 会报错：

　　Every derived table must have its own alias
　　这句话的意思是说每个派生出来的表都必须有一个自己的别名
　　一般在多表查询时，会出现此错误。
　　因为，进行嵌套查询的时候子查询出来的的结果是作为一个派生表来进行上一级的查询的，所以子查询的结果必须要有一个别名
　　把MySQL语句改成：select count(*) from (select * from ……) as total;
　　问题就解决了，虽然只加了一个没有任何作用的别名total，但这个别名是必须的