mysql数据库练习题（含答案）
创建数据库
1 DROP DATABASE IF EXISTS test1; #如果存在test1数据库删除test1数据库 2 CREATE DATABASE test1; #创建test1数据库 3 USE test1; #切换的test1数据库里
创建部门表
1 DROP IF EXISTS TABLE DEPT; #如果存在dept数据表,删除dept数据表. 2 CREATE TABLE DEPT( 3 DEPTNO int PRIMARY KEY, #部门编号 4 DNAME VARCHAR(14) , #部门名称 5 LOC VARCHAR(13) #部门地址 6 ) ;
向部门表插入数据
1 INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); 2 INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); 3 INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); 4 INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
创建员工表
1 DROP IF EXISTS TABLE EMP; #如果存在emp数据表,删除emp数据表. 2 CREATE TABLE EMP( 3 EMPNO int PRIMARY KEY, #员工编号 4 ENAME VARCHAR(10), #员工姓名 5 JOB VARCHAR(9), #员工工作 6 MGR int, #员工直属领导编号 7 HIREDATE DATE, #入职时间 8 SAL double, #工资 9 COMM double, #奖金 10 DEPTNO int #对应dept表的外键 11 );
添加 部门 和 员工 之间的主外键关系
1 ALTER TABLE EMP ADD CONSTRAINT FOREIGN KEY EMP(DEPTNO) REFERENCES DEPT (DEPTNO); #加主键约束
1.主键约束：要对一个列加主键约束的话，这列就必须要满足的条件就是分空因为主键约 束：就是对一个列进行了约束，约束为（非空、不重复）以下是代码 要对一个列加主键，列名为id,表名为emp
格式为：alter table 表格名称 add constraint 约束名称 增加的约束类型 （列名）
添加 部门 和 员工
1 INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20); 2 INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); 3 INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 4 INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 5 INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 6 INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 7 INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 8 INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20); 9 INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); 10 INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 11 INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20); 12 INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 13 INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 14 INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10);
创建工资等级表
1 DROP IF EXISTS TABLE SALGRADE; #如果存在salgrade数据表,删除salgrare数据表. 2 CREATE TABLE SALGRADE( 3 GRADE int, #等级 4 LOSAL double, #最低工资 5 HISAL double ); #最高工资 6 INSERT INTO SALGRADE VALUES (1,700,1200); 7 INSERT INTO SALGRADE VALUES (2,1201,1400); 8 INSERT INTO SALGRADE VALUES (3,1401,2000); 9 INSERT INTO SALGRADE VALUES (4,2001,3000); 10 INSERT INTO SALGRADE VALUES (5,3001,9999);
单表查询题目及答案
1 #1、查找部门30中员工的详细信息。 2 select * from emp where deptno = 30; 3 #2、找出从事clerk工作的员工的编号、姓名、部门号。 4 select empno,ename,deptno from emp where job = 'clerk'; 5 #3、检索出奖金多于基本工资的员工信息。 6 select * from emp where comm > sal; 7 #4、检索出奖金多于基本工资60%的员工信息。 8 select * from emp where comm > sal * 0.6; 9 #5、找出10部门的经理、20部门的职员 的员工信息。 10 select * from emp where deptno = 10 and job='MANAGER' or deptno = 20 and job = 'CLERK'; 11 #6、找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。 12 select * from emp 13 where deptno = 10 and job='MANAGER' 14 or deptno = 20 and job = 'CLERK' 15 or job!='MANAGER' and job != 'CLERK' and sal > 2000 ; 16 ## job not in ('MANAGER','CLERK') 17 #7、找出获得奖金的员工的工作。 18 select * from emp where comm > 0; 19 #8、找出奖金少于100或者没有获得奖金的员工的信息。 20 select * from emp where comm < 100 or comm is null; 21 #9、找出姓名以A、B、S开始的员工信息。 22 select * from emp where ename like 'A%' or ename like 'B%' or ename like 'S%'; 23 #10、找到名字长度为6个字符的员工信息。 24 select * from emp where length(ename) = 6; 25 #select * from emp where ename like '______'; 26 #11、名字中不包含R字符的员工信息。 27 select * from emp where ename not like '%R%'; 28 #12、返回员工的详细信息并按姓名排序。 29 select * from emp order by ename asc; 30 #13、返回员工的信息并按工作降序工资升序排列。 31 select * from emp order by job desc , sal asc; 32 #14、计算员工的日薪(按30天)。 33 select ename,sal/30 as '日薪' from emp; 34 select ename,truncate(sal/30,2) '日薪' from emp; 35 #15、找出姓名中包含A的员工信息。 36 select * from emp where ename like '%A%';
多表查询题目及答案
1 #1、返回拥有员工的部门名、部门号。 2 select distinct d.dname, d.deptno from dept d,emp e where d.deptno = e.deptno; 3 #2、工资水平多于smith的员工信息。 4 select *from emp where sal > (select sal from emp where ename = 'smith'); 5 #3、返回员工和所属经理的姓名。 6 select e.ename,m.ename from emp e 7 left outer join emp m on e.mgr = m.empno; 8 select e.ename ,(select m.ename from emp m where m.empno = e.mgr) ename from emp e; 9 select e.ename , m.ename from emp e , emp m where e.mgr = m.empno; 10 #4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。 11 select e.ename,m.ename from emp e 12 inner join emp m on e.mgr = m.empno 13 where e.hiredate < m.hiredate; 14 select e.ename,m.ename from emp e,emp m 15 where e.mgr=m.empno 16 and e.hiredate < m.hiredate; 17 #5、返回员工姓名及其所在的部门名称。 18 select e.ename,d.dname from emp e , dept d where e.deptno = d.deptno; 19 #6、返回从事clerk工作的员工姓名和所在部门名称。 20 select e.ename,d.dname 21 from emp e , dept d 22 where e.deptno = d.deptno and e.job = 'CLERK'; 23 #7、返回部门号及其本部门的最低工资。 24 select deptno ,min(sal) sal 25 from emp 26 group by deptno 27 #8、返回销售部(sales)所有员工的姓名。 28 select e.ename from emp e,dept d 29 where e.deptno = d.deptno and d.dname = 'sales'; 30 select ename from emp where deptno=(select deptno from dept where dname='sales'); 31 #9、返回工资水平多于平均工资的员工。 32 select * from emp e 33 where e.sal > (select avg(sal) from emp); 34 #10、返回与SCOTT从事相同工作的员工。 35 select * from emp 36 where job = (select job from emp where ename = 'scott'); 37 select e1.* from emp e1 , (select empno,job from emp where ename = 'scott') e2 38 where e1.job = e2.job and e1.empno != e2.empno; 39 #11、返回与30部门员工工资水平相同的员工姓名与工资。 40 select ename,sal from emp 41 where sal in (select sal from emp where deptno = 30); 42 #12、返回工资高于30部门所有员工工资水平的员工信息。 43 select * from emp 44 where sal > all(select sal from emp where deptno = 30); 45 select * from emp 46 where sal > (select max(sal) from emp where deptno = 30); 47 #13、返回部门号、部门名、部门所在位置及其每个部门的员工总数。 48 select dept.deptno,dept.dname,dept.loc,count(emp.deptno) number from dept,emp 49 where dept.deptno = emp.deptno 50 group by emp.deptno; 51 #14、返回员工的姓名、所在部门名及其工资。 52 select ename,dname,sal from emp ,dept 53 where emp.deptno = dept.deptno; 54 #15、返回员工的详细信息。(包括部门名) 55 select e.* , d.dname from emp e, dept d 56 where e.deptno = d.deptno; 57 #16、返回员工工作及其从事此工作的最低工资。 58 select job , min(sal) sal from emp 59 group by job 60 #17、计算出员工的年薪，并且以年薪排序。 61 select ename, sal * 12 as ySalary from emp order by ySalary; 62 #18、返回工资处于第四级别的员工的姓名。 63 select ename,sal from emp e ,salgrade s 64 where e.sal >= s.losal and e.sal <= s.hisal 65 and s.grade = 4; 66 select emp.ename,emp.sal from 67 emp ,(select losal,hisal from salgrade where grade=4) g 68 where emp.sal between g.losal and g.hisal; 69 #19、返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资 70 select ename ,dname ,sal ,losal,hisal from emp,dept,salgrade 71 where emp.deptno = dept.deptno and grade = 2 72 and sal >= losal and sal < hisal; 73 #20.工资等级多于smith的员工信息。 74 select grade from salgrade s ,emp e 75 where s.losal < e.sal and s.hisal > e.sal and e.ename = 'smith'; 76 select e.* from emp e, salgrade s 77 where s.hisal < e.sal and s.grade = 1; 78 select e.* from emp e, salgrade s 79 where s.hisal < e.sal and s.grade = (select grade from salgrade s ,emp e 80 where s.losal < e.sal and s.hisal > e.sal and e.ename = 'smith'); 81
