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 约束名称增加的约束类型（列名）
添加部门和员⼯
mysql查询面试题及答案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-
zblog免费的主题下载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、返回拥有员⼯的部门名、部门号。writelines（）
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 ame from emp e
7 left outer join emp m = m.empno;
8 ame ,(ame from emp m pno = e.mgr) ename from emp e;
9 ame , m.ename from emp e , emp m = m.empno; 10 #4、返回雇员的雇佣⽇期早于其经理雇佣⽇期的员⼯及其经理姓名。11 ame from emp e 12 inner join emp m = m.empno 13 where e.hiredate < m.hiredate; 14 ame from emp e,emp m 15 =m.empno 16 and e.hiredate < m.hiredate; 17 #5、返回员⼯姓名及其所在的部门名称。18 ame,d.dname from emp e , dept d where e.deptno = d.deptno; 19 #6、返回从事clerk⼯作的员⼯姓名和所在部门名称。
20 ame,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 26group by deptno 27 #8、返回销售部(sales)所有员⼯的姓名。28 ame 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 pno != 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 =
haslayoutemp.deptno 50group 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 59group 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 ade = 4; 66 ame,emp.sal from 67 emp ,(select losal,hisal from salgrade where grade=4) g 68 where emp.sal between
alert和alarm的区别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 ame = 'smith'; 76 select e.* from emp e, salgrade s 77 whe
re s.hisal < e.sal ade = 1; 78 select e.* from emp e, salgrade s 79 where s.hisal < e.sal ade = (select grade from salgrade s ,emp e 80 where s.losal < e.sal and s.hisal > e.sal ame = 'smith'); 81
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论