E1 基于Mysql的SQL应用
一、实验目的:
要求学生熟练掌握Mysql平台下SQL指令的应用,完成对基本表、视图创建、删除、数据插入、查询、更新和删除等数据管理工作。
二、实验要求:
1、基本硬件配置:英特尔Pentium III 以上,大于4G内存;
2、软件要求:Mysql;
3、时间:4小时;
4、撰写实验报告并按时提交。
三、实验内容:
emp表
CREATE TABLE `emp` (
`ename` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`job` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`deptno` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`sal` double NOT NULL,
`comm` double NOT NULL,
`hiredate` date NOT NULL,
`empno` int(11) NOT NULL,
`mgr` int(11) NOT NULL,
PRIMARY KEY (`empno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
dept表
CREATE TABLE `dept` (
`deptno` int(11) NOT NULL,
`dname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`loc` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
Group 1: 用命令“Select”查询相关数据:
1、 检索所有的职工姓名与年薪
select ename,12 * sal from emp;
2、 检索所有职工的工作和相应的部门编号
select distinct job,deptno from emp;
3、 检索工作是salesman的员工姓名;
select ename from emp where job ='salesman';
4、 检索员工津贴comm为null或comm小于300的员工姓名和津贴值;
select ename, comm from emp where comm is null or comm < 300;
5、 检索所有职员的姓名和所在部门名称;
select emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno;
6、 检索所有员工及其相关领导的姓名。
select e.ename as emp, m.ename as manager from emp e, emp m where m.empno = e.mgr;
7、 查询聘用日期早于他们的领导的雇员姓名。
select ename from emp as e where e.hiredate<(select hiredate from emp where empno=e.mgr);
8、 检索在任何位置有字母“A”的员工姓名;
select ename from emp where ename like '%A%';
9、 检索名字没有字母串‘%R’的员工姓名;
select ename from emp where ename not like '%%R%';
10、 检索所有员工姓名的前三个字母;
select substr(ename,1,3) from emp;
11、 查询部门2所有的经理、部门3所有clerks以及所有其他所有工资超过2000元的员工姓名;
select ename from emp where deptno = 2 and job = 'manager' or deptno = 3 and job = 'clerk' or sal > 2000;
12、 查询所有manager岗位而非3号部门的员工姓名;
select ename from emp e where (e.job='manager' and e.deptno!=3);
13、 检索所有员工的姓名、职位和工资,按职位降序排列;
select ename, job, sal from emp order by job desc;
14、 检索所有员工的姓名、年收入(sal+comm) * 12(要求重命名),按年收入升序显示;
select ename,(sal+comm) * 12 as annual_salry from emp order by annual_salry;
15、 检索1号部门所有员工的人数和平均年薪;
select count(*), avg(sal) from emp where emp.deptno=1;
16、 检索每个部门的名称和人数。
select dname, count(*) from emp, dept where dept.deptno = emp.deptno group by emp.deptno;
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(*), avg(sal), avg(timestampdiff(day, hiredate,now())) from emp,dept where dept.deptno = emp.deptno group by emp.deptno;
20、 检索最低工资在1500元以上的工作岗位;
select job, min(sal) from emp group by job having min(sal) > 1500;
21、 检索至少有两个人的部门名称和人数;
select dname, count(*) from emp, dept where dept.deptno = emp.deptno
group by emp.deptno 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, dept where sal > (select sal from emp where ename = 'smith') and emp.deptno = dept.deptno;
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 avg(sal) from emp where deptno=1);
26、 检索所有工资高于1号部门某职工工资水平的员工姓名和工资(>any或>all);
select ename, sal from emp where sal > any(select avg(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 dept left join emp on dept.deptno = emp.deptno;
29、 创建一个包括1号部门员工所有信息的视图。
create view view1 as select * from emp where emp.deptno=1;
Group 2请使用你的账户创建两个表,包括属性、数据类型、主键和外键。
ddept (deptno, dname loc)
deptno:integer;dname: varchar (20); loc: varchar (30);
primary key:deptno;
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 ddept(deptno integer, dname varchar(20), loc varchar(30), primary key(deptno));
create table eemp(empno integer, ename varchar(20), job varchar(20), hiredate datetime, salary double, mgr int, comm double, deptno integer not null, primary key(empno), foreign key(deptno) references ddept(deptno), foreign key(mgr) references eemp(empno));
Group 3: 完成以下操作:
- 通过alter table添加一个check约束(comm在1和3000之间);
alter table emp add check(comm between 1 and 3000);
- 向表eemp中插入至少5条员工记录;
INSERT INTO `eemp` VALUES (1,'woodwhale', 'manager', '2021-10-26',10000, null, 20000, 1);
INSERT INTO `eemp` VALUES (2,'sheepbotany', 'salesman', '2021-11-17', 5000, null, 20000, 2);
INSERT INTO `eemp` VALUES (3,'zhang', 'clerks','2021-11-11', 4000, null, 20000, 3);
INSERT INTO `eemp` VALUES (4,'yu', 'manager','2021-11-06', 5999, null, 20000, 4);
INSERT INTO `eemp` VALUES (5,'wu', 'salesman','2021-11-16', 5219, null, 20000, 5);
- 从eemp中删除名为“SMITH”的记录;
delete from eemp where ename = ‘smith’;
- 创建部门研究信息视图,包括empno、ename、salary和comm。
create index researchindex on eemp(empno, ename, salary, comm);
- 将所有员工的工资提高20%。
update eemp set salary = salary * 1.2;
Group 4: 综合设计
根据某个应用的需求,创建一个新的数据库。其中至少包括3个表,每个表至少定义4个合理的属性,分别具有适合的数据类型,并确定表的主键。然后,根据您设计的数据库,将每个表中至少插入4行数据。
创建一个宠物商店数据库
CREATE DATABASE `petshop` ;
创建管理员表
CREATE TABLE `admin` (
`username` varchar(20) NOT NULL COMMENT '管理员用户名',
`password` varchar(20) NOT NULL COMMENT '管理员密码',
`hobby` varchar(20) NOT NULL COMMENT '管理员爱好',
`money` int NOT NULL COMMENT '管理员工资',
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
管理员表插入4条数据
insert into `admin`(`username`,`password`,`hobby`,`money`) values
('laowu','12345','猫',11451),
('wangwu','123456','狗',19198),
('laozhang','123456','猫',11451),
('laoli','123456','仓鼠',11451);
创建宠物表
CREATE TABLE `pet` (
`variety` varchar(10) NOT NULL COMMENT '宠物类别',
`petname` varchar(20) NOT NULL COMMENT '宠物姓名',
`sex` varchar(2) NOT NULL COMMENT '宠物性别',
`birthday` date NOT NULL COMMENT '宠物上架日期',
`buy_day` date DEFAULT NULL COMMENT '宠物被购买日期',
`master` varchar(20) DEFAULT NULL COMMENT '宠物主人',
`love` int(3) NOT NULL DEFAULT '0' COMMENT '宠物好感度',
`health` varchar(1) NOT NULL DEFAULT '是' COMMENT '宠物是否健康',
PRIMARY KEY (`petname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
向宠物表插入4条数据
insert into `pet`(`variety`,`petname`,`sex`,`birthday`,`buy_day`,`master`,`love`,`health`) values
('狗狗','小白','雄性','2021-02-19',NULL,NULL,0,'是'),
('狗狗','白糖','雄性','2021-02-19','2021-02-19','woodwhale',50,'是'),
('猫咪','花椒','雌性','2021-02-19','2021-02-20','woodwhale',20,'是'),
('猫咪','豆腐','雌性','2021-02-19','2021-02-19','lisi',10,'是');
创建店主表
CREATE TABLE `shopkeeper` (
`id` int(1) unsigned NOT NULL COMMENT '店主的唯一标识',
`keepername` varchar(20) NOT NULL COMMENT '店主的用户名',
`password` varchar(20) NOT NULL COMMENT '店主的密码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入4条店主数据
insert into `shopkeeper`(`id`,`keepername`,`password`) values
(1,'shopkeeper','123456'),
(2,'shopkeeper1','123456'),
(3,'shopkeeper2','123456'),
(4,'shopkeeper3','123456');
创建顾客表
CREATE TABLE `visitor` (
`username` varchar(20) NOT NULL COMMENT '游客用户名',
`password` varchar(20) NOT NULL COMMENT '游客密码',
`food` int(3) NOT NULL DEFAULT '0' COMMENT '游客拥有的宠物食物',
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入4条顾客数据
insert into `visitor`(`username`,`password`,`food`) values
('lisi','123456',0),
('woodwhale','123456',18),
('wuyuhan','123456',0),
('zhangsan','123456',0);
总结:
本次实验,学习了select的各种查询方式,并且在最后的综合实验中,进一步学习了如何创建数据库和创建表格,同时也学习到了如何给表中添加数据。总之就是进一步增强了对数据库的实际操作!