MySql练习
/* Navicat MySQL Data Transfer Source Server : localhost_3306 Source Server Version : 50549 Source Host : localhost:3306 Source Database : mydb Target Server Type : MYSQL Target Server Version : 50549 File Encoding : 65001 Date: 2017-11-13 15:22:02 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for `dept` -- ---------------------------- DROP TABLE IF EXISTS `dept`; CREATE TABLE `dept` ( `DEPTNO` bigint(20) NOT NULL AUTO_INCREMENT, `DNAME` varchar(20) DEFAULT NULL, `LOC` varchar(20) DEFAULT NULL, PRIMARY KEY (`DEPTNO`) ) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of dept -- ---------------------------- 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'); /* Navicat MySQL Data Transfer Source Server : localhost_3306 Source Server Version : 50549 Source Host : localhost:3306 Source Database : mydb Target Server Type : MYSQL Target Server Version : 50549 File Encoding : 65001 Date: 2017-11-13 15:22:12 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for `emp` -- ---------------------------- DROP TABLE IF EXISTS `emp`; CREATE TABLE `emp` ( `EMPNO` bigint(20) NOT NULL AUTO_INCREMENT, `ENAME` varchar(20) DEFAULT NULL, `JOB` varchar(20) DEFAULT NULL, `MGR` bigint(20) DEFAULT NULL, `HIREDATE` date DEFAULT NULL, `SAL` double(7,2) DEFAULT NULL, `COMM` double(7,2) DEFAULT NULL, `DEPTNO` bigint(2) DEFAULT NULL, PRIMARY KEY (`EMPNO`) ) ENGINE=InnoDB AUTO_INCREMENT=7935 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of emp -- ---------------------------- INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800.00', null, '20'); INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600.00', '300.00', '30'); INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30'); INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975.00', null, '20'); INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250.00', '1400.00', '30'); INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850.00', null, '30'); INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450.00', null, '10'); INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000.00', null, '20'); INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000.00', null, '10'); INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500.00', '0.00', '30'); INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100.00', null, '20'); INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950.00', null, '30'); INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000.00', null, '20'); INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300.00', null, '10');
01.查询每个雇员的编号、姓名、职位。
select * from emp;
SELECT EMPNO AS '编号',ENAME AS '姓名',JOB AS '职位' FROM emp;
02.查询出所有的职位，使用DISTINCT消除掉显示的重复行记录。
select distinct job AS '职位' from emp;
03.计算每个雇员的编号、姓名、基本年薪。年薪=(工资+奖金)*12，(使用IFNULL函数判断NULL）
select empno,ename,mgr,sal,(ifnull(sal,0)+ifnull(comm,0))*12 as "基本年薪" from emp;
select empno,ename,(ifnull(sal,0)+ifnull(comm,0))*12 as "基本年薪" from emp;
04.每个雇员每个月公司会补贴饭食200元，交通补助300元，计算年薪。
select empno,ename,(500.0+ifnull(sal,0)+ifnull(comm,0))*12 as "年薪" from emp; -- 有问题
05.查询基本工资高于2000的全部雇员信息。
select ename,mgr from emp where mgr>2000;
select * from emp where mgr>2000;
06.查询出smith的信息。
select * from emp where ename='smith'; -- 不能使用`` 要使用单引号
07.查询出所有不是CLERK的详细信息。
select * from emp where ename!='clerk'; -- 不能使用not = 或者is not
08.查询出所有销售人员(SALESMAN)的基本信息，并且要求销售人员的工资高于1300
select * from emp where job='salesman' and sal>1300;
09.查询出工资范围在1500~3000之间的全部雇员信息（包含1500和3000）。
select * from emp where sal>1500 and sal<3000;
10.查询出所有经理或者是销售人员的信息，并且要求这些人的基本工资高于1500。
select * from emp where (job='salesman' or job='manager') and sal>1500;
11.要求查询出所有在1981年雇佣的雇员信息。
select * from emp where DATE_FORMAT(hiredate,'%Y')=1981;
select * from emp WHERE HIREDATE like '1981%';
12.查询所有领取奖金的雇员信息（comm不为空）。
select * from emp where comm is not null;
13.查询所有领取奖金高于100的雇员信息。
select * from emp where comm>100;
14.查询出雇员编号是7369、7566、9999的雇员信息。
select * from emp where empno=7369 or empno=7566 or empno=9999;
select * from emp where empno=7369 || empno=7566 || empno=9999;
15.查询出所有雇员姓名是以A开头的全部雇员信息。
select * from emp where ename like 'A%';
select * from emp where ename REGEXP '^[a]';
16.查询出雇员姓名第二个字母是M的全部雇员信息。
SELECT * from emp where ename like '_M%';
17.查询出雇员姓名任意位置上包含字母A的全部雇员信息。
SELECT * from emp where ename like '%A%';
18.查询出所有雇员的信息，要求按照工资排序。
select * from emp order by sal;
select *, ifnull(mgr,0) from emp order by mgr;
19.要求查询所有雇员的信息，按照雇佣日期由先后排序。
SELECT * FROM emp ORDER BY hiredate DESC;
20.查询全部雇员信息，按照工资由高到低排序，如果工资相同，则按照雇佣日期由先后排序。
SELECT * FROM emp ORDER BY sal DESC ,hiredate ASC;
21.查询部门30中的所有员工。
SELECT * from emp WHERE deptno=30;
22.查询出所有办事员（CLERK）的姓名，编号和部门编号。
SELECT empno,ename,deptno,job from emp where job='clerk';
23.查询出奖金高于薪金的员工。
SELECT * from emp WHERE comm>sal;
SELECT * from emp WHERE comm>IFNULL(sal,0);
24.查询出奖金高于薪金的60%的员工。
SELECT * from emp WHERE comm>(sal*0.6);
-- SELECT * from emp WHERE comm>(sal*`60%`);
25.查询出部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK）的详细资料。
SELECT * from emp where (job='manager'&&deptno=10) or (job='clerk'&&deptno=20);
26.查询出部门10中所有经理，部门20中所有办事员，既不是经理又不是办事员但其薪金大于或等于2000的所有员工的信息。
SELECT * from emp where (job='manager'&&deptno=10) or (job='clerk'&&deptno=20) or (job!='clerk'&&job!='manager');
SELECT * from emp,dept where (emp.DEPTNO=dept.DEPTNO AND emp.JOB='manager')
OR (emp.DEPTNO=dept.DEPTNO AND emp.JOB='clerk')
or (emp.DEPTNO=dept.DEPTNO AND emp.JOB not in ('manager') and emp.JOB not in ('clerk') and emp.sal>2000);
-- ==》更加优化的写法：emp.JOB not in('manager','clerk');
27.查询出收取了奖金的员工从事的工作。
-- select job from emp where comm is not null; -- 怎么取出单一的一条？
select DISTINCT job from emp where comm>0 ;
select DISTINCT job from emp where comm>0 and comm is not null;
28.查询出不收取奖金或收取的奖金低于100的员工。
select * from emp where comm is null or comm<100;
29.查询出不带有“R”的员工的姓名。
select * from emp where ename not like '%R%' ;
30.查询出每个雇员的姓名、职位、领导姓名。
SELECT e.ename,e.job,b.ename FROM EMP AS e,emp AS b WHERE e.MGR= b.empno;
31.查询出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。
SELECT e.empno as 员工编号,e.ename 员工姓名,b.empno 领导编号,b.ename 领导姓名,b.sal*12 领导年薪 FROM emp AS e,emp as b where e.mgr =b.empno order by 领导年薪 DESC;
SELECT a.empno 员工编号, a.ename 员工名字, a.mgr 领导编号,b.ename 领导姓名,b.sal*12 年薪 FROM emp a,emp b WHERE a.mgr=b.empno ORDER BY 年薪 DESC;
32.查询出在销售部（SALES）工作的员工姓名、基本工资、雇佣日期、部门名称。（不知道销售部编号）。
select emp.ename as '姓名',emp.sal as '基本工资',emp.hiredate as '雇佣日期',dept.dname as '部门名称'
from emp,dept where emp.deptno = dept.deptno AND dept.DNAME='SALES'; -- emp as emp,dept as dept as可以省略
33.查询出所有员工的姓名、部门名称和工资。
select emp.ename as '姓名',dept.dname as '部门',emp.sal as '工资' from emp,dept where emp.deptno = dept.deptno;
34.查询出所有员工的年工资，所在部门名称，按年薪从低到高排序。
select emp.ename,emp.sal*12 as '年薪',dept.dname as '部门' from emp,dept where emp.deptno=dept.deptno ORDER BY emp.sal*12 DESC;
select emp.ename,emp.sal*12 as 'sum',dept.dname as '部门' from emp,dept where emp.deptno=dept.deptno ORDER BY 'sum' DESC; -- 错误
select emp.ename,emp.sal*12 as 年薪,dept.dname as '部门' from emp,dept where emp.deptno=dept.deptno ORDER BY 年薪 DESC; -- ok
select emp.ename,emp.sal*12 as '年薪',dept.dname as '部门' from emp,dept where emp.deptno=dept.deptno ORDER BY '年薪' DESC; -- 不行
35.查询出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000。
select e.ename,b.ename,dept.dname from emp as e,emp as b,dept where e.mgr = b.empno and b.deptno=dept.deptno;
SELECT a.ename 员工名称 ,b.dname 员工的部门名称,c.ename 上级主管, d.dname 主管部门名称 FROM emp a,dept b,emp c,dept d WHERE a.deptno=b.deptno AND a.mgr=c.empno AND c.deptno=d.deptno;
36.查询出公司的最高和最低工资。
select MAX(sal) as '最高工资',MIN(sal) as '最低工资' from emp;
37.查询出每个部门的人数、平均工资，只显示部门编号。
select COUNT(ifnull(comm,0)) from emp;
38.查询出每种职位的最高和最低工资。
select job,MAX(sal) as '最高工资',MIN(sal) as '最低工资' from emp GROUP BY job with ROLLUP;
39.查询平均工资高于2000的职位信息，以及从事此职位的雇员人数、平均工资。
select job,count(empno) as '雇员人数',avg(sal) as 平均工资 from emp where sal>2000 GROUP BY job HAVING 平均工资 >=3000;
SELECT emp.job 职位,DEPT.* ,COUNT(*) 雇员人数, AVG(sal) FROM DEPT,EMP WHERE emp.deptno=dept.deptno GROUP BY emp.job;
40.查询员工编号大于其领导编号的每个员工的姓名、职位、领导名称。
select e.ename,e.job,b.ename from emp as e,emp as b where e.empno>b.empno;
41查询出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。
SELECT dept.deptno,dept.dname,avg(emp.sal) as '平均工资',MAX(emp.sal) as '最高工资',MIN(emp.sal) as '最低工资' from dept,emp where emp.deptno = dept.deptno GROUP BY dept.dname;
42.查询出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。
SELECT dept.dname as '部门名称',COUNT(emp.empno) as '员工数量',avg(sal) as '平均工资' from dept LEFT JOIN emp on emp.deptno = dept.deptno GROUP BY dept.dname;
43.查询工资比 smith更高的全部员工信息。
select * from emp where sal>(select sal from emp where ename = 'smith');
44.查询工资和职位和 smith相同的所有员工信息。
select * from emp where job in (select job from emp where ename='smith') ;
45.查询各部门的部门编号，部门名词，部门地址，员工人数和平均工资。
SELECT dept.deptno 部门编号,dept.dname 部门名称,dept.loc 部门地址,COUNT(emp.deptno) 员工人数,avg(emp.sal) 平均工资 from dept,emp where emp.deptno = dept.deptno group by emp.deptno;
浙公网安备 33010602011771号