课后作业和总结

课后作业

• 作业一

第一题：D选项是错误的

第二题：B选项是正确的

第三题：C选项是错误的

• 作业二

# 作业二

-- 写出查看DEPT表和EMP表的结构的sql语句
desc dept
desc emp

// 使用简单查询语句完成
-- 1.显示所有部门名称
select dname from dept;
-- 2.显示所有雇员名称及其全年收入13月(工资+补助)，并指定列别名“年收入”
select ename , (sal+ifnull(comm,0)) * 13 '年收入' from emp;

// 限制查询数据
-- 1.显示工资超过2850的雇员姓名和工资
select ename,sal from emp where sal > 2850;
-- 2.显示工资不在1500到2850之间的所有雇员名及工资
select ename,sal from emp where sal > 2850 or sal < 1500;
-- 3.显示编号为7566的雇员姓名及所在部门编号
select ename,deptno from emp where empno = 7566;
-- 4.显示部门10和30中工资超过1500的雇员名及工资
select ename,sal from emp where deptno = 10 or deptno = 30;
-- 5.显示无管理者的雇员及岗位
select ename,job from emp where mgr is null;

// 排序数据
-- 1.显示在1991年2月1日到1991年5月1日之间雇用的雇用名，岗位及雇佣日期，并以雇佣日期进行排序
select ename,job,hiredate from emp where hiredate between '1991-02-01' and '1991-05-01' order by hiredate;
-- 2.显示获得补助的所有雇用名，工资以及补助，并以工资降序排列
select ename,sal,comm from emp where comm is not null order by sal desc;

• 作业三

# 作业三

// 根据emp员工表写出正确sql
-- 1.选择部门30中的所有员工
select * from emp where deptno = 30;
-- 2.列出所有办事员(clerk)的姓名，编号和部门编号
select ename,empno,deptno from emp where job = 'CLERK';
-- 3.找出佣金高于薪金的员工
select * from emp where ifnull(comm,0) > sal;
-- 4.找出佣金高于薪金60%的员工
select * from emp where if(comm,0) > sal*0.6;
-- 5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料
select * from emp where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK');
-- 6.找出部门10中所有经理，部门20中所有办事员，还有既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料
select * from emp where  (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK') or (job not in ('MANAGER','CLERK') and sal >= 2000);
-- 7.找出收取佣金的员工的不同工作
select distinct job from emp where comm is not null;
-- 8.找出不收取佣金或收取的佣金低于100员工
select * from emp where comm is null or comm < 100;
-- 9.找出各月倒数第3天受雇的所有员工
select * from emp where last_day(hiredate) - 2 = hiredate;
-- 10.找出早于12年受雇的员工
select * from emp where year(hiredate)+12 < now();
-- 11.以首字母小写的方式显示所有员工的姓名
 select concat(lcase(left(ename,1)),substring(ename,2,length(ename))) from emp;
-- 12.显示正好为5个字符的员工的姓名
 select ename from emp where length(ename) = 5;

• 作业四

# 作业四

-- 1.显示不带有“R”的员工的姓名
select ename from emp where not like '%R%';
-- 2.显示所有员工姓名的前三个字符
 select substring(ename,1,3) from emp;
-- 3.显示所有员工的姓名，用a替换所有的“A”
select replace(ename,'A','a') from emp;
-- 4.显示满10年服务年限的员工的姓名和受雇日期
select ename,hiredate from emp where year(hiredate)+10 < now();
-- 5.显示员工的详细资料，按姓名排序
select * from emp  order by ename;
-- 6.显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面
select ename,hiredate from emp order by hiredate;
-- 7.显示所有员工的姓名、工作和薪金，按工作降序排列，若工作相同则按照薪金排列
select ename,job,sal from emp order by job desc,sal;
-- 8.显示所有员工的姓名、加入公司的年份和月份，按受雇日期所在的月排序，若月份相同则将最早年份的员工排在最前面
select ename,concat(year(hiredate),'年',month(hiredate),'月') from emp order by month(hiredate),hiredate;
-- 9.显示在一个月为30天的情况所有员工的日薪金，忽略余数
select floor(sal/30) day_sal from emp;
-- 10.找出在（任何年份的）2月受聘的所有员工
select * from emp where month(hiredate) = 2;
-- 11.对于每个员工，显示其加入公司的天数
 select ename,datediff(now(),hiredate) from emp;
-- 12.显示名字字段的任何位置包含“A”的所有员工姓名
select ename from emp where ename like '%A%';
-- 13.以年月日的方式显示所有员工的服务年限(大概)
select from_days(datediff(now(),hiredate)) from emp;

• 作业五

# 作业五

-- 1.列出至少有一个员工的所有部门
select deptno,count(deptno) from emp group by emp.deptno having count(deptno) >= 1;
-- 2.列出薪金比“SMITH”多的所有员工
select ename from emp where sal > (select sal from emp where ename = 'SMITH');
-- 3.列出受雇日期晚于其直接上级的所有员工
 select work1.ename from emp work1,emp work2 where work1.hiredate > work2.hiredate and work1.mgr = work2.empno;
-- 4.列出部门名称和这些部门的员工信息，同时列出哪些没有员工的部门。
select dname,ename from dept left join emp on dept.deptno = emp.deptno;
-- 5.列出所有“ClERK”的姓名及其部门名称
select ename,dname from emp,dept where emp.deptno = dept.deptno and job = 'CLERK';
-- 6.列出最低薪金大于1500的各种工作
 select job,MIN(sal) from emp group by job having MIN(sal) > 1500;
-- 7.列出在部门“SALES” 工作的员工的姓名
 select ename from emp,dept where dept.deptno = emp.deptno and dname = 'SALES';
-- 8.列出薪金高于公司平均薪金的所有员工
select ename from emp where sal > (select avg(sal) from emp);

• 作业六

# 作业六

-- 1.列出“SCOTT”从事相同工作的所有员工
 select * from emp where job = (select job from emp where ename = 'SCOTT');
-- 2.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金
select ename,sal from emp where sal > (select max(sal) from emp where deptno = 30);
-- 3.列出在每个部门工作的员工数量、平均工资和平均服务期限
select deptno,count(*),avg(sal),avg(datediff(now(),hiredate)) from emp group by deptno;
-- 4.列出所有员工的姓名、部门名称和工资
select ename,dname,sal from emp,dept  where emp.deptno = dept.deptno;
-- 5.列出所有部门的详细信息和部门人数
select dept.*,总人数 from dept,(select deptno,count(*) as '总人数' from emp group by deptno) temp where temp.deptno = dept.deptno;
-- 6.列出各种工作最低工资
select job,min(sal) from emp group by job;
-- 7.列出MANAGER的最低薪金
select min(sal) from emp where job = 'MANAGER';
-- 8.列出所有员工的年工资，按年薪从低到高排序
select ename,12*(sal + ifnull(comm,0)) year_sal as '年工资' from emp order by year_sal;

• 作业七

# 作业七

/** 设学校环境如下：一个系有若干个专业，每个专业一年只招一个班，每个班有若干
个学生。想要建立系、学生、班级的数据库，关系模式为：
班CLASS （班级号classid，专业名subject，系名deptname，入学年份enrolltime，人数num）
学生STUDENT（学号studentid，姓名name，年龄age，班号classid）
系DEPARTMENT（系号departmentid，系名deptname）
*/

-- 1.建表，在定义中要求声明：
-- 每个表的主外码。
-- deptname是唯一约束
-- 学生姓名不能为空
 create table `class`(
     classid int primary key,
     subject varchar(64) not null,
     deptname varchar(64) not null,
     enrolltime int not null,
     num int not null default 0,
 		 foreign key (deptname) references department(deptname));

create table `student`(
     studentid int primary key,
     name varchar(32) not null,
     age int not null,
     classid int not null,
     foreign key (classid) references class(classid));

create table department(
     departmentid int primary key,
     deptname varchar(64) unique not null);
-- 2.插入如下数据
-- department（001，数学，002，计算机，003，化学；004，中文，005，经济）

insert into department values (001,'数学');
insert into department values (002,'计算机');
insert into department values (003,'化学');
insert into department values (004,'中文');
insert into department values (005,'经济');


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


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

• 作业八

# 作业八

-- 1.完成以下查询功能
-- 找出所有李姓学生
select * from student where name like '李%';
-- 列出所有开设超过1个专业的名字
 select deptname,count(*) from class group by deptname having count(*) > 1;
-- 列出人数大于等于30的系的编号和名字
 select departmentid,t.* from 
	department , (select deptname,sum(num) from class GROUP BY deptname having sum(num) >= 30) t 
		where t.deptname = department.deptname;
-- 2.学校又新增了一个物理系，编号为006
insert into department values (006,'物理');
-- 3.学生张三退学，请更新相关的表
start transaction
update class set num = num - 1 where (classid = select classid from student where name = '张三');
delete  from student where name = '张三';
commit

2.学校又新增了一个物理系，编号为006
insert into department values (006,'物理');
-- 3.学生张三退学，请更新相关的表
start transaction
update class set num = num - 1 where (classid = select classid from student where name = '张三');
delete  from student where name = '张三';
commit

总结：

通过一个星期的学习老韩MySQL，对我的帮助很大，本来一开始最简单的sql语句写起来都很困难，一些概念也非常模糊。特别是复杂sql想都不用想。现在自己能设计一些简单表，可以通过外键约束将多个表连接起来。将复杂sql简单化，一步一步的写出最终sql语句。知道了怎么分组、过滤、排序、分页… 对mysql的数据类型有了基本了解，还会使用一些常用的函数。了解了mysql的事务怎么开启，怎么结束，怎么回退。还知道事务的隔离级别。