【mysql】E1 基于Mysql的SQL应用

最新推荐文章于 2022-04-29 23:05:59 发布
最新推荐文章于 2022-04-29 23:05:59 发布
阅读量4.3k 收藏 9
点赞数 4
分类专栏： 与君共勉 mysql 文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/woodwhale/article/details/121873655
版权

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;

img

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;

img

Group 1: 用命令“Select”查询相关数据：

1、 检索所有的职工姓名与年薪

select ename,12 * sal from emp;

img

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

select distinct job,deptno from emp;

img

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

select ename from emp where job ='salesman';

img

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

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

img

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

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

img

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

select e.ename as emp, m.ename as manager from emp e, emp m where m.empno = e.mgr;

img

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

select ename from emp as e where e.hiredate<(select hiredate from emp where empno=e.mgr);

img

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

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

img

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

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

img

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

select substr(ename,1,3) from emp;

img

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

select ename from emp where deptno = 2 and job = 'manager' or deptno = 3 and job = 'clerk' or sal > 2000;

img

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

select ename from emp e where (e.job='manager' and e.deptno!=3);

img

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

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

img

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

select ename,(sal+comm) * 12 as annual_salry from emp order by annual_salry;

img

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

select count(*), avg(sal) from emp where emp.deptno=1;

img

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

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

img

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

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

img

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

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

img

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

select dname, count(*), avg(sal), avg(timestampdiff(day, hiredate,now())) from emp,dept where dept.deptno = emp.deptno group by emp.deptno;

img

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

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

img

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';

img

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

select ename, Dname from emp, dept where sal > (select sal from emp where ename = 'smith') and emp.deptno = dept.deptno;

img

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

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

img

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

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

img

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

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

img

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

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

img

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

select dname,ename from dept left join emp on dept.deptno = emp.deptno;

img

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

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

img

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));

img

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));

img

Group 3: 完成以下操作:

  1. 通过alter table添加一个check约束(comm在1和3000之间);
alter table emp add check(comm between 1 and 3000);

img

  1. 向表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);

img

  1. 从eemp中删除名为“SMITH”的记录;
delete from eemp where ename = ‘smith’;

img

  1. 创建部门研究信息视图，包括empno、ename、salary和comm。
create index researchindex on eemp(empno, ename, salary, comm);

img

  1. 将所有员工的工资提高20%。
update eemp set salary = salary * 1.2;

img

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的各种查询方式，并且在最后的综合实验中，进一步学习了如何创建数据库和创建表格，同时也学习到了如何给表中添加数据。总之就是进一步增强了对数据库的实际操作！

woodwhale
关注
  • 4
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
sql数据库实验实验一
05-09
数据库实验实验一：创建表、更新表和实施数据完整性 1． 运行给定的SQL Script，建立数据库GlobalToyz。 2． 在企业管理器中建立所有表的关系图。 3． 利用系统定义的存储过程sp_helpdb查看数据库
E1 基于Mysql的SQL应用
jxh1025_的博客
11-15 1965
E1 基于Mysql的SQL应用 实验目的：完成对基本表、视图创建、删除、数据插入、查询、更新和删除等数据管理工作。 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 =
数据库实验一
Touale的博客
04-26 1533
数据库实验一 // 1.创建学生选课关系数据库中的STUDENT表（特别提示：表结构见1.3节学生选课关系数据库，使用课本上的表结构是错误的，后同）； CREATE TABLE STUDENT (SNO VARCHAR(9)PRIMARY KEY, SNAME VARCHAR(9), SSEX VARCHAR(20), SAGE SMALLINT, SDEPT VARCHAR(20)); // table STUDENT 已创建。 // 2.创建学生选课关系数据库中的COURSE表； CREA
SQL server 数据库 实验一
qiaoqingchen的博客
04-29 987
大致操作一览management操作1.创建数据库和日志文件2.把1中所创建数据库分离并且备份到E盘，再附加回服务器。再分离，复制到U盘3.创建D_student数据库，后删除4.把PUBS和NORTHWND两个数据库附加到服务器上T-SQL语句操作5.创建数据库T-student.mdf和日志文件T_student_log.ldf6.修改2中的数据库文件增长值 management操作 1.创建数据库和日志文件 右键“数据库”点击“新建数据库”，命名数据库，并且设置其中文件的参数（初始大小，自动增长，路径
数据库——SQL实验一
zaifengzhong52的博客
06-15 1071
SQL实验一 21.6.15 旨在回顾SQL语法，尽可能使用多种写法 实验一 建表、删表 create table test1_student ( sid char(12), name varchar2(10) ); // 建表 drop table test1_student; 插入数据 insert into test1_student values('200020002000','王菲') // 要按顺序写，标点要是英文标点 题目 1-1 not null 不允许值为空 cre
MySQL查询部门工资前三高的所有员工
12-14
0x01.需求 Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。 +----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | ...
MySQL数据库下用户及用户权限配置
12-16
问题：使用某大腿写的远程工具管理Mysql数据库时发现所有数据能正常显示，但是无法进行删除、修改等操作。 　思路：可以远程读取到数据库里的信息，说明当前主机可以远程连接数据库。却无法进行删除、修改这些操作，...
Oracle10g通过DBLink访问MySQL示例
12-15
1, 首先在Oracle所在计算机安装MySQL的Client端软件，并且x86_64和i386版本的都需要安装，以便可以连接MySQL数据库。查看Client安装情况： 代码如下:rpm -qa |grep mysql mysql-5.0.45-7.el5 mysql-5.0.45-7.el5 ...
mySQL事务处理
06-13
关于mysql的事务处理 public static void StartTransaction(Connection con, String[] sqls) throws Exception { if (sqls == null) { return; } Statement sm = null; try { // 事务开始 System....
用命令创建MySQL数据库(de1)的方法
01-19
一、连接MYSQL 格式： mysql -h主机地址 -u用户名 -p用户密码 1、 连接到本机上的MYSQL。 首先打开DOS窗口，然后进入目录mysql\bin，再键入命令mysql -u root -p，回车后提示你输密码.注意用户 名前可以有空格也可以...
SQL数据库实验
05-02
数据库的创建删除，表格的创建删除，数据的插入筛选等等。USE YGGL GO CREATE TABLE Employees ( EmployeesID char(6) NOT NULL PRIMARY KEY, Name char(10) NOT NULL, Education char(4) NOT NULL, Birthday datetime NOT NULL, Sex bit NOT NULL DEFAULT 1, WorkYear
简单的sql数据库实验
05-27
这是一个简单的实验，关于触发器的。最简单的。。
sql数据库实验报告
04-27
自己写的数据库的实验报告，并包含sql语句实现！
SQL数据库实验三_简单查询
07-31
SQL数据库实验三_简单查询(1)解答.doc
SQL数据库实验四_复杂查询
07-31
SQL数据库实验四_复杂查询(1)解答.doc
sqlserver数据库实验一
GilgameshG的博客
04-04 645
1. 一些sql语法sqlserver并不支持 2. 当用insert插入date型数据的时候，要用单引号括起来 …values(‘2012/10/1’)
MySQL-SQL基础应用(SQL基础)
wwj0407的博客
08-07 254
1、SQL基础应用 1.1 SQL的介绍 1>SQL标准https://blog.csdn.net/weixin_33963189/article/details/90584378 SQL-92：https://blog.csdn.net/zhongmengya/article/details/5049025 SQL-99：https://blog.csdn.net/qq_...
SQL 实验一
森宝的博客
03-26 2277
实验内容 本节的主要内容是 : 使用 CREATE 语句创建基本表 ; 更改基本表的定义 ,增加列 ,删除列 ,修改列的数据类型 . 创建表的升降序索引 . 取消表,表的索引或表的约束. 实验步骤 : (1) 使用SQL 语句创建关系数据库表:人员表 PERSON(P# ,Pname ,Page ,Pgender) ...
SQL Server 数据库实验课第一周
沃然
03-09 636
数据库基本操作准备：查看SQL Server是否运行一、新建数据库二、新建一张表三、在表中增、删、查1.增加数据2. 删除数据3.查询数据四、键五、 问题 准备：查看SQL Server是否运行 1.在开始界面找到SQL Server 配置管理器，打开 2.查看SQL Server服务是否运行 打开Microsoft SQL Server Management Studio 连接 一、新建数据库 连接好数据库服务器后，展开根目录，右击数据库，新建名为Test的数据库 成功新建一个数据库Test
mysql 分组查询limit
最新发布
01-11
MySQL的分组查询limit是指在分组查询的结果中，限制返回的记录条数。它可以用来获取每个分组中的前几条记录，或者获取整个结果集中的前几条记录。 下面是一个示例，演示如何使用MySQL的分组查询limit： 假设我们有...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值