MySQL-05-练习题

MySQL - 练习题

建表

drop table `dept`;
drop table `emp`;

show create table dept;
-- 部门表
create table dept(
	deptno int(11) not null primary key COMMENT'部门编号（主键）', -- 部门编号
	dname varchar(14) COMMENT'部门名字',	  -- 部门名字
	loc varchar(13) COMMENT'地址'   -- 地址
) character set utf8;
-- 员工表
create table emp(
	empno int(11) not null primary key COMMENT'员工编号',-- 员工编号
	ename varchar(10) COMMENT'员工姓名', -- 员工姓名										-
	job varchar(9) COMMENT'岗位',	-- 岗位
	mgr int COMMENT'直接领导编号',	 -- 直接领导编号
	hiredate date COMMENT'雇佣日期，入职日期', -- 雇佣日期，入职日期
	sal int COMMENT'薪水', -- 薪水
	comm int COMMENT'提成',   -- 提成
	deptno int not null COMMENT'部门编号' -- 部门编号
-- 	foreign key (deptno) references dept(deptno)
) character set utf8;


insert into dept values(10,'财务部','北京');
insert into dept values(20,'研发部','上海');
insert into dept values(30,'销售部','广州');
insert into dept values(40,'行政部','深圳');
insert into emp values(7369,'刘一','职员',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'陈二','推销员',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'李四','经理',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'周八','分析师',7566,'1987-06-13',3000,null,20);
insert into emp values(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'郑十','推销员',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'郭十一','职员',7788,'1987-06-13',1100,null,20);
insert into emp values(7900,'钱多多','职员',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'大锦鲤','分析师',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'木有钱','职员',7782,'1983-01-23',1300,null,10);

insert into emp(empno, ename, job, hiredate, sal, deptno) values
	(78933, 'FFOOW', '清洁工', '1981-06-01', 1000, 10),
	(7937, 'SAMA', '清洁工', '1981-06-01', 1000, 10),
	(7979, 'MAKE', '清洁工', '1981-06-01', 1000, 10),
	(7833, 'CARI', '清洁工', '1981-06-01', 1000, 10)
	;

-- 找出每个部门的平均工资
select deptno, avg(sal)
	from emp
	group by deptno;

-- 完整
SELECT emp.deptno, emp.`ename`,sal, temp.avg_sal
	FROM emp, (select deptno, avg(sal) avg_sal
							from emp
							group by deptno
	) temp
	where temp.deptno = emp.deptno
	and temp.avg_sal < emp.sal;

desc dept;

show index from emp;
show indexes from emp;
show keys from emp;

select @@tx_isolation;

desc dept;
desc emp;

select * from emp;
select * from dept;
2.
select dname from dept;

3.
select ename, (sal + if(comm is null, 0, comm))*13 as '年收入' from emp;
	select ename, (sal + ifnull(comm, 0))*13 as '年收入' from emp;

4.1
select ename, sal from emp where sal > 2850;

4.2
select ename, sal from emp where sal not between 1500 and 2850;

4.3
select ename, deptno from emp where empno = 7566;

4.4
select ename, sal, deptno 
	from emp where deptno in(30, 10) and sal > 1500;

4.5
select ename, job from emp where mgr is null;

5.1
select ename, job, hiredate 
	from emp where hiredate between '1981-02-01' and '1981-05-01'
	order by hiredate;
	
5.2
select ename, sal, comm 
	from emp where comm is not null and comm != 0
	order by sal desc;

6.1
select * from emp where deptno = 30;

6.2
select ename, empno, job, deptno
	from emp where empno in(
		select mgr from emp where mgr is not null
	);

6.3
select * from emp where comm > sal;

6.4
select * from emp where comm > (sal * 0.6);

6.5
select distinct * from emp where job = '经理'
	or (deptno = 20 and job = '职员');

6.6 方式1
select * from emp where (deptno = 10 and job = '经理')
	or (deptno = 20 and job = '职员') 
	or (job not in('经理','职员') and sal >= 2000);
方式2
select * from emp where deptno = 10 and job = '经理'
union
select * from emp where deptno = 20 and job = '职员'
union
select * from emp where job not in('经理','职员') and sal >= 2000;

6.7
select distinct job from emp where comm is not null;

6.8
select * from emp where comm is null or ifnull(comm, 0) < 100;

6.9 select LAST_DAY(now()) - 2
select * from emp where LAST_DAY(hiredate) = date_add(hiredate, interval 2 day);

6.10 1995
select * from emp where year(hiredate) < year(now()) - 12;
select * from emp where DATE_ADD(hiredate,interval 3 year) > now();
select * from emp where year(hiredate) < year('1995-01-01') - 12;

6.11 
select replace(
	ename,
	left(ename, 1),
	lcase(left(ename, 1))
) from emp;

6.12
select * from emp where length(ename) = 5;

6.13
select ename from emp where ename not like '%R%';

6.14
select left(ename, 3) from emp;

6.15
select replace(ename, 'A', 'a') from emp;

6.16
方式1
select ename, hiredate, year(now()) - year(hiredate) as workyear from emp where year(now()) - year(hiredate) > 10;
方式2
select ename, hiredate from emp where DATE_ADD(hiredate,interval 10 year) <= now();

6.17
select * from emp order by ename;

6.18
select ename, hiredate from emp order by year(now()) - year(hiredate) desc;

6.19
select ename,job,sal from emp order by job,sal;

6.20
select ename,concat(year(hiredate),'年',month(hiredate),'月')
	from emp order by month(hiredate),year(hiredate);

6.21 怎么忽略余数？floor() 向下取整
select ename, floor(sal / 30) as '日薪' from emp;

6.22
select * from emp where month(hiredate) = 2;

6.23 datediff() 
select ename, DATEDIFF(now(),hiredate) as '工作天数' from emp;

6.24
select ename from emp where ename like '%A%';

6.25
select UNIX_TIMESTAMP()

7.1
select deptno,count(empno) as deptSum from emp group by deptno having deptSum > 1;

7.2 改为赵六
select * from emp where sal > (
	select sal from emp where ename = '赵六'
);

7.3 不会过。
select worker.ename as '员工', worker.hiredate as '员工日期', boss.ename as '上级', boss.hiredate as '上级时间' from emp worker, emp boss where worker.hiredate < boss.hiredate and worker.mgr = boss.empno; 

7.4 使用外连接
select dname, emp.* from dept left join emp on emp.deptno = dept.deptno;

7.5
select ename, dname from dept, emp where dept.deptno = emp.deptno and job = '推销员';

7.6 不会过
select job, min(sal) as min_sal 
	from emp 
	group by job having min_sal > 1500; 
	
7.7 
方式1
select ename from emp where deptno = (
	select deptno from dept where dname = '销售部'
);
方式2
select ename, dname from emp, dept where dept.deptno = emp.deptno	and dname = '销售部';

7.8
select * from emp where sal > (
	select avg(sal) from emp
);

7.9
select ename, job from emp where job = (
	select job from emp where ename = 'SAMA'
);

7.10 也可以用max()
select ename, sal from emp where sal > all (
	select sal from emp where deptno = 30
);

7.11 平均年限如果是年：/ 365 如果不要小数点：floor(num, 小数位数)
select deptno,count(deptno) as '员工数量', avg(sal) as '平均薪资', avg(DATEDIFF(NOW(),hiredate)) as '平均服务时间 day'
	from emp group by deptno;

7.12
select ename, dname, (sal + IFNULL(comm,0)) as '工资' 
	from dept, emp
	where dept.deptno = emp.deptno;

7.13 将查询出来的统计信息看作临时表temp
temp:
	select deptno, count(deptno) from emp group by deptno;

select dept.*, temp.count_deptno
	from dept , (
		select deptno, count(deptno) as count_deptno 
		from emp 
		group by deptno
	) temp
	where dept.deptno = temp.deptno;

7.14
select job, min(sal + IFNULL(comm,0)) from emp group by job;

7.15
select job, min(sal) from emp group by job having job = '经理';
select job, min(sal) from emp where job = '经理';

7.16
select ename, (sal + IFNULL(comm,0))*12 as year_sal 
	from emp 
	order by year_sal;

第8题：

8.
drop table `w_department`;

create database wang_school;
use wang_school;

create table `class`(
	`classid` varchar(20) primary key comment '班号',
	`subject` varchar(50) comment '专业名',
	`deptname` varchar(50) comment '系名',
	`enrolltime` year comment '入学年份',
	`num` int  comment '人数'
) character set utf8;

create table `student`(
	`studentid` varchar(20) primary key  comment '学号',
	`name` varchar(20) not null  comment '姓名',
	`age` int  comment '年龄',
	`classid` varchar(20)  comment '班号',
	foreign key(classid) references `class`(classid)
) character set utf8;

create table `department`(
	`departmentid` varchar(20) primary key  comment '系号',
	`deptname` varchar(50) unique  comment '系名'
) character set utf8;

insert into `department` values 
	('001', '数学'),
	('002', '计算机'),
	('003', '化学'),
	('004', '中文'),
	('005', '经济')
	;
	
insert into `class` values
	('101', '软件', '计算机', '1995', '20'),
	('102', '微电子', '计算机', '1956', '30'),
	('111', '无机化学', '化学', '1995', '29'),
	('112', '高分子化学', '化学', '1996', '25'),
	('121', '统计数学', '数学', '1995', '20'),
	('131', '现代语言', '中文', '1996', '20'),
	('141', '国际贸易', '经济', '1997', '30'),
	('142', '国际金融', '经济', '1996', '14')
	;

insert into `student` values
	('8101', '张三', 18, '101'),
	('8102', '钱四', 16, '102'),
	('8103', '王玲', 17, '131'),
	('8105', '李飞', 19, '102'),
	('8109', '赵四', 18, '141'),
	('8110', '李可', 20, '142'),
	('8201', '张飞', 18, '111'),
	('8302', '周瑜', 16, '112'),
	('8203', '王亮', 17, '111'),
	('8305', '董庆', 19, '102'),
	('8409', '赵龙', 18, '101'),
	('8510', '李丽', 20, '142')
	;
select * from student;
select * from class;
select * from department;

8.3.1
select * from student where `name` like '李%';

8.3.2
select deptname, count(deptname) as count_deptname
	from class 
	group by deptname
	having count_deptname > 1;

8.3.3
select departmentid, class.deptname
	from department, class
	where department.deptname = class.deptname
	and num >= 30;

8.4
insert into department values
	('006', '物理系');
	
8.5 补充需要通过事务控制，人删除了，班级人数也要减1
-- 以防没有数据
insert into `student` values('8101', '张三', 18, '101');
update `class` set num = num + 1 
	where classid = (
		select classid from student where `name` = '张三'
	);
-- 开启事务
start transaction;

-- 先减少班级人数，再删除学生，因为需要用到学生的信息。
update `class` set num = num - 1 
	where classid = (
		select classid from student where `name` = '张三'
	);
select * from class;

delete from student where `name` = '张三';
select * from student;

-- 可选，回滚到最初状态
rollback;

-- 提交事务
commit;
select * from student;