【mysql】E1 基于Mysql的SQL应用

E1 基于Mysql的SQL应用

一、实验目的:
要求学生熟练掌握Mysql平台下SQL指令的应用，完成对基本表、视图创建、删除、数据插入、查询、更新和删除等数据管理工作。

二、实验要求:
1、基本硬件配置:英特尔Pentium III 以上,大于4G内存；
2、软件要求:Mysql；
3、时间:4小时；
4、撰写实验报告并按时提交。

三、实验内容:
Group 1: 用命令“Select”查询相关数据：
1、检索所有的职工姓名与年薪

Select ename, sal*12 from emp;

2、检索所有职工的工作和相应的部门编号

Select job, deptno from emp;

3、检索工作是salesman的员工姓名；

Select ename from emp where job = ‘saleman’;

4、检索员工津贴comm为null或comm小于300的员工姓名和津贴值；

Select ename, comm from emp where comm < 300 or comm is null;

5、检索所有职员的姓名和所在部门名称；

Select ename, dname from emp, dept where emp.deptno = dept.deptno;

6、检索所有员工及其相关领导的姓名。

Select e.ename, l.ename from emp as e, emp as l where e.mgr = l.empno;

7、查询聘用日期早于他们的领导的雇员姓名。

Select e.ename from emp as e, emp as l where e.mgr = l.empno and e.hiredate < l.hiredate;

8、检索在任何位置有字母“A”的员工姓名;

Select ename from emp where ename like ‘%a%’;

9、检索名字没有字母串‘%R’的员工姓名;

Select ename from emp where ename not like ‘%\%r%’;

10、检索所有员工姓名的前三个字母；

Select left(ename, 3) from emp;

11、查询部门2所有的经理、部门3所有clerks以及所有其他所有工资超过2000元的员工姓名；

Select ename from emp where deptno = 2 and job = ‘manager’ or deptno = 3 and job = ‘clerks’ or sal > 2000;

12、查询所有manager岗位而非3号部门的员工姓名；

Select ename from emp where job = ‘manager’ and deptno != 3;

13、检索所有员工的姓名、职位和工资，按职位降序排列；

Select ename, job, sal from emp order by job desc;

14、检索所有员工的姓名、年收入(sal+comm) * 12（要求重命名），按年收入升序显示；

Select ename, (sal+comm)*12 as yearsal from emp order by yearsal asc;

15、检索1号部门所有员工的人数和平均年薪；

Select count(*), avg(sal*12) from emp where deptno = 1;

16、检索每个部门的名称和人数。

select dname, count(e.deptno) from emp as e right join dept as d on e.deptno = d.deptno group by dname;

17、检索每一种工作的最低工资和相应的工作。

select min(sal), job from emp group by job;

18、查询各个部门经理职位（manager）的员工最低工资。

select min(sal), deptno from emp where job = 'Manager' group by deptno;

19、查询各部门员工人数、平均工资、平均参加工作的天数；

select dname, count(emp.deptno), avg(sal), avg(comm) from emp right join dept on emp.deptno = dept.deptno group by dname;

20、检索最低工资在1500元以上的工作岗位；

select job from emp where sal > 1500 group by job;

21、检索至少有两个人的部门名称和人数；

select dname, count(*) from emp as e, dept as d where e.deptno = d.deptno group by dname having count(*) >= 2;

22、检索与SCOTT的工作岗位相同的所有员工的姓名；

select ename from emp where job=(select job from emp where ename = 'scott') and ename != 'scott';

23、检索所有工资高于Smith的工资的员工姓名和所在部门；

select ename, dname from emp as e, dept as d where e.deptno = d.deptno and sal > (select sal from emp where ename = 'Smith');

24、检索所有工资高于整个公司平均工资的员工的姓名和工资；

select ename, sal from emp where sal > (select avg(sal) from emp);

25、检索所有工资高于1号部门所有职工工资水平的员工姓名和工资(>any或>all)；

select ename, sal from emp where sal > all(select sal from emp where deptno = 1);

26、检索所有工资高于1号部门某职工工资水平的员工姓名和工资(>any或>all)；

select ename, sal from emp where sal > any(select sal from emp where deptno = 1);

27、查询与3号部门某个员工工作岗位相同的员工姓名和工资；

select ename, sal from emp where job in(select job from emp where deptno = 3);

28、检索所有部门名称和所有员工，包括那些没有任何员工的部门。

select dname, ename from emp as e right join dept as d on e.deptno = d.deptno;

29、创建一个包括1号部门员工所有信息的视图。

create view deptno1 as select * from emp where deptno = 1;

Group 2请使用你的账户创建两个表，包括属性、数据类型、主键和外键。
ddept (deptno, dname loc)
deptno:integer;dname: varchar (20); loc: varchar (30);
primary key:deptno;

Create table ddept(deptno int primary key, dname varchar(20), loc varchar(30));

eemp (empno, ename,job,hiredate,mgr,salary,comm,deptno)
empno: integer;ename: varchar(20);job:varchar(20),hiredate:datetime, salary:double; comm:double;deptno: integer, not null;mgr:integer;
Primary key:empno;forign keys:deptno reference ddept(deptno)，mgr reference EEmp(empno)。

create table eemp(empno int, ename varchar(20), job varchar(20), hiredate datetime, salary double, comm double, deptno int not null, mgr int, primary key(empno), foreign key(deptno) references ddept(deptno), foreign key(mgr) references eemp(empno));

Group 3: 完成以下操作:
1.通过alter table添加一个check约束(comm在1和3000之间);

Alter table emp add check(comm between 1 and 3000);

2.向表eemp中插入至少5条员工记录;

insert into eemp values(1, 'laurie', 'teacher', '2018-10-15', 8888.5, 320, 1, null);

insert into eemp values(2, 'xxy', 'student','2020-02-15', 1118.5, 300, 1, 1);

insert into eemp values(3, 'amy', 'student','2020-03-25', 2018.5, 310, 2, 1);

insert into eemp values(4, 'fxx', 'teacher','2012-03-25', 4231.5, 290, 2, 1);

insert into eemp values(5, 'ming', 'student','2019-11-15', 7231.5, 290, 2, 2);

insert into eemp values(6, 'doinb', 'student','2018-03-08', 1511.5, 180, 2, 1);

insert into eemp values(7, 'Smith', 'student','2015-05-08', 2811.5, 210, 2, 1);

3.从eemp中删除名为“SMITH”的记录;

delete from eemp where ename = 'Smith';

4.创建部门研究信息视图，包括empno、ename、salary和comm。

create view deptView as select empno, ename, salary, comm from eemp;

5. 将所有员工的工资提高20%。

update eemp set salary = salary * 1.2;

Group 4: 综合设计
根据某个应用的需求，创建一个新的数据库。其中至少包括3个表，每个表至少定义4个合理的属性，分别具有适合的数据类型，并确定表的主键。然后，根据您设计的数据库，将每个表中至少插入4行数据。

create database library;

use library;

create table book(BookId int primary key, BookName varchar(20), Author varchar(20), Publisher varchar(20));

create table borrow_book(BorrowId int primary key, BookId int, BookName varchar(20), Author varchar(20), BorrowTime datetime);

create table return_book(ReturnId int primary key, BookId int, BookName varchar(20), Author varchar(20), BorrowTime datetime);

insert into book values(1, '飘', '米切尔', '南海出版社');

insert into book values(2, '活着', '余华', '南海出版社');

insert into book values(3, '1Q84', '村上春树', '人民出版社');

insert into book values(4, '厌女', '上野千鹤子', '上海三联书店');

insert into borrow_book values(1, 2, '活着', '余华', '2022-4-3');

insert into borrow_book values(2, 4, '厌女', '上野千鹤子', '2022-4-4 14:20');

insert into borrow_book values(3, 3, '1Q84', '村上春树', '2022-4-3 9:45');

insert into borrow_book values(4, 1, '飘', '米切尔', '2022-4-2 18:38');

insert into return_book values(1, 1, '飘', '米切尔', '2022-4-3 13:28');

insert into return_book values(2, 4, '厌女', '上野千鹤子', '2022-4-4 19:13');

insert into return_book values(3, 3, '1Q84', '村上春树', '2022-4-4 14:47');

insert into return_book values(4, 2, '活着', '余华', '2022-4-4 16:42');

验证:

select * from book;

select * from borrow_book;

select * from return_book;

总结：
我学习到了对于mysql的增删改查操作以及对于用户创建等的操作,同时对于数据库和数据表的创建有了深刻的体会.