[zz] MySQL SQL话语练习

[zz] MySQL SQL语句练习

MySQL? SQL语句练习：

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 d