目录
一、实验目的
实验一: MySQL数据库表的数据插人、修改、删除操作实验
1.掌握MySQL数据库表的数据插入、修改、删除操作SQL语法格式。
2.掌握数据表的数据的录人、增加和删除的方法。
实验二.MySQL 数据库表的查询操作实验
1.掌握select语句的基本语法格式。
2.掌握select语句的执行方法。
3.掌握select语句的group by和order by子句的作用。
二、实验内容
2.1.验证性实验
1.1学校教师管理数据库中的teacherInfo表，其定义如下表所示，请完成如下操作：
| 字段名 | 字段描述 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
| num | 教工号 | INT(10) | 是 | 否 | 是 | 是 | 否 |
| name | 姓名 | VARCHAR(20) | 否 | 否 | 是 | 否 | 否 |
| sex | 性别 | VARCHAR(4) | 否 | 否 | 是 | 否 | 否 |
| birthday | 出生日期 | DATETIME | 否 | 否 | 否 | 否 | 否 |
| address | 家庭住址 | VARCHAR(50) | 否 | 否 | 否 | 否 | 否 |
(1)创建teacherInfo表:
CREATE TEBLE teacherInfo(
num INT(10) NOT NULL UNIQUE,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
brithday DATETIME,
address VARCHAR(50)
);(2) 向teacherInfo表中插入记录。写出INSERT语句大代码如下：
INSERT INTO teacherInfo VALUES(1001,'张龙','男','1984-11-08','北京市昌平区'),
(1002,'李梅','女','1970-01-21','北京市海淀区'),
(1003,'王一丰','男','1976-10-30','北京市昌平区'),
(1004,'赵六','男','1980-06-05','北京市顺义区');
（3）更新教工号为1003的记录，将生日（birthday）改为“1982-11-08”。UPDATE语句的代码如下：
UPDATE teacherInfo SET birthday='1982-11-08' WHERE num=1003;（4）将性别（sex）为“男”的记录的家庭住址（address）都变为“北京市朝阳区”。UPDATE语句的代码如下：
UPDATE teacherInfo SET address='北京市朝阳区' WHERE sex='男';（5）删除教工号（num）为1002的记录删除。DELETE语句的代码如下：
DELETE FROM teacherInfo WHERE num=1002;2.2、验证性实验2
2.1某超市的食品管理的数据库的Food表，Food表的定义如表所示，请完成插入数据、更新数据和删除数据。
| 字段名 | 字段描述 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
| foodid | 食品编号 | INT(4) | 是 | 否 | 是 | 是 | 是 |
| name | 食品名称 | VARCHAR(20) | 否 | 否 | 是 | 否 | 否 |
| company | 生产厂商 | VARCHAR(30) | 否 | 否 | 是 | 否 | 否 |
| price | 价格（单位：元） | FLOAT | 否 | 否 | 是 | 否 | 否 |
| product_time | 生产年份 | YEAR | 否 | 否 | 否 | 否 | 否 |
| validity_time | 保质期（单位：年） | INT(4) | 否 | 否 | 否 | 否 | 否 |
| address | 厂址 | VARCHAR(50) | 否 | 否 | 否 | 否 | 否 |
按照下列要求进行操作：
（1）创建food表：
CREATE TABLE food(
foodid INT(4) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
conpany VARCHAR(30) NOT NULL,
price FLOAT NOT NULL,
product_time YEAR,
validity_tima INT(4)
address VARCHAR(50)
);(2) 采用3种方式，将表的记录插入到Food表中。
方法一：不指定具体的字段，插入数据： 'QQ饼干','QQ饼干厂',2.5,'2018',3,'北京'。
INSERT INTO food VALUES(null,'QQ饼干','QQ饼干厂',2.5,'2018',3,'北京');方法二：依次指定food表的字段，插入数据： 'MN牛奶','MN牛奶厂',3.5,'2019',1,'河北')。
INSERT INTO food VALUES(2,'MN牛奶','MN牛奶厂',3.5,'2019',1,'河北');方法三：同时插入多条记录，插入数据：
'EE果冻','EE果冻厂',1.5,'2017',2,'北京',
'FF咖啡','FF咖啡厂',20,'2012',5,'天津',
'GG奶糖','GG奶糖',14,'2013',3,'广东';
INSERT INTO food (foodid,name,company,price,product_time,validity_time,address)
VALUES(3,'EE果冻','EE果冻厂',1.5,'2017',2,'北京'),
(4,'FF咖啡','FF咖啡厂',20,'2012',5,'天津'),
(5,'GG奶糖','GG奶糖',14,'2013',3,'广东');（3）将“MN牛奶厂”的厂址(address)改为“内蒙古”，并且将价格改为3.2。
UPDATE food SET address = '内蒙古', price = 3.2 WHERE company = 'MN牛奶厂';（4）将厂址在北京的公司的保质期(validity_time)都改为5年。
UPDATE food SET validity_time = 5
WHERE address = '北京';（5）删除过期食品的记录。若当前时间-生产年份(producetime)>保质期（validity_time），则视为过期食品。
DELETE FROM food
WHERE validity_time < (2021 - product_time);（6）删除厂址为“北京”的食品的记录。
DELETE FROM food
WHERE address = '北京';三、设计性实验
3.1 设计性实验1
在公司的部门员工管理数据库的bumen表和yuangong表上进行信息查询。Bumen表和yuangong表的定义如表所示。
| 字段名 | 字段描述 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
| d_id | 部门号 | INT(4) | 是 | 否 | 是 | 是 | 否 |
| d_name | 部门名称 | VARCHAR(20) | 否 | 否 | 是 | 是 | 否 |
| function | 部门职能 | VARCHAR(20) | 否 | 否 | 否 | 否 | 否 |
| address | 工作地点 | VARCHAR(30) | 否 | 否 | 否 | 否 | 否 |
| 字段名 | 字段描述 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
| id | 员工号 | INT(4) | 是 | 否 | 是 | 是 | 否 |
| name | 姓名 | VARCHAR(20) | 否 | 否 | 是 | 否 | 否 |
| sex | 性别 | VARCHAR(4) | 否 | 否 | 是 | 否 | 否 |
| birthday | 年龄 | INT(4) | 否 | 否 | 否 | 否 | 否 |
| d_id | 部门号 | VARCHAR(20) | 否 | 是 | 是 | 否 | 否 |
| salary | 工资 | Float | 否 | 否 | 否 | 否 | 否 |
| address | 家庭住址 | VARCHAR(50) | 否 | 否 | 否 | 否 | 否 |
创建bumen表(这里的function因为是保留字所以要用“``”引起来)：
CREATE TABLE bumen(
d_id INT(4) NOT NULL UNIQUE PRIMARY KEY,
d_name VARCHAR(20) NOT NULL UNIQUE KEY,
`function` VARCHAR(20),
address VARCHAR(30)
);创建yuangong表：
CREATE TABLE yuangong(
id INT(4) NOT NULL UNIQUE PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday INT(4),
d_id INT(4) NOT NULL,
salary FLOAT,
address VARCHAR(50),
FOREIGN KEY (d_id) REFERENCES bumen (d_id)
);bumen表的练习数据：
1001,'人事部','人事管理','北京'
1002,'科研部','研发产品','北京'
1003,'生产部','产品生产','天津'
1004,'销售部','产品销售','上海'
INSERT INTO bumen
VALUES(1001,'人事部','人事管理','北京'),
(1002,'科研部','研发产品','北京'),
(1003,'生产部','产品生产','天津'),
(1004,'销售部','产品销售','上海');yuangong表的数据：
8001,'韩鹏','男',25,1002,4000,'北京市海淀区'
8002,'张峰','男',26,1001,2500,'北京市昌平区'
8003,'欧阳','男',20,1003,1500,'湖南省永州市'
8004,'王武','男',30,1001,3500,'北京市顺义区'
8005,'欧阳宝贝','女',21,1002,3000,'北京市昌平区'
8006,'呼延','男',28,1003,1800,'天津市南开区'
INSERT INTO yuangong
VALUES(8001,'韩鹏','男',25,1002,4000,'北京市海淀区'),
(8002,'张峰','男',26,1001,2500,'北京市昌平区'),
(8003,'欧阳','男',20,1003,1500,'湖南省永州市'),
(8004,'王武','男',30,1001,3500,'北京市顺义区'),
(8005,'欧阳宝贝','女',21,1002,3000,'北京市昌平区'),
(8006,'呼延','男',28,1003,1800,'天津市南开区');（1）查询yuangong表的所有记录。SQL代码：
SELECT * FROM yuangong;或者列出yuangong表的所有字段名称。SQL代码：
SELECT id,name,sex,age,d_id,salary,address FROM yuangong;（2）查询yuangong表的第四条到第五条记录。SQL代码：
SELECT id,name,sex,age,d_id,salary,address FROM yuangongLIMIT3,2;（3）从bumen表查询部门号（d_id）、部门名称（d_name）和部门职能（function）。SQL代码：
SELECT d_id,d_name,function FROM yuangong;（4）从yuangong表中查询人事部和科研部的员工的信息。先从bumen表查询出人事部和科研部的部门号。然后到yuangong表中去查询员工的信息。SQL代码：
SELECT * FROM yuangong
WHERE d_id=ANY(
SELECT d_id FROM bumen
WHERE d_nameIN('人事部','科研部'));
或者使用下面的代码。SQL语句如下：：
SELECT * FROM yuangong
WHERE d_idIN(
SELECT d_id FROM bumen
WHERE d_name='人事部'ORd_name='科研部');
（5）从yuangong表中查询年龄在25到30之间的员工的信息。可以通过两种方式来查询。
第一种方式的SQL代码：
SELECT * FROM yuangong WHERE age BETWEEN 25 AND 30;第二种方式的SQL代码：
SELECT * FROM yuangong WHERE age>=25 AND age<=30;（6）查询每个部门有多少员工。先按部门号进行分组，然后用COUNT()函数来计算每组的人数。SQL代码：
SELECT d_id,COUNT(id) FROM yuangong GROUP BY d_id;或者给COUNT(id)取名为sum。其SQL代码为：
SELECT d_id,COUNT(id) AS sum FROM yuangong GROUP BY d_id;（7）查询每个部门的最高工资。先按部门号进行分组，然后用MAX()函数来计算最大值。SQL代码：
SELECT d_id,MAX(salary) FROM yuangong GROUP BY d_id;（8）用左连接的方式查询bumen表和yuangong表。使用 LEFT JOINON 来实现左连接。SQL代码：
SELECT bumen.d_id,d_name,function,bumen.address,id,name,age,sex,salary,yuangong.address
FROM bumen LEFT JOIN yuangong ON yuangong.d_id=bumen.d_id;
（9）计算每个部门的总工资。先按部门号进行分组，然后用SUM()函数来求和。SQL代码：
SELECT d_id,SUM(salary) FROM yuangong GROUP BY d_id;（10）查询yuangong表，按照工资从高到低的顺序排列。SQL代码：
SELECT * FROM yuangong ORDER BY salary DESC;（11）从bumen表和yuangong表中查询出部门号，然后使用UNION合并查询结果。SQL代码：
SELECT d_id FROM yuangong UNION SELECT d_id FROM bumen;（12）查询家是北京市员工的姓名、年龄、家庭住址。这里使用 LIKE 关键字。SQL代码：
SELECT name,age,address FROM yuangong WHERE address LIKE '北京%';3.2设计性实验2
将在student表和score表上进行查询。Student表和score表的定义如表所示：
| 字段名 | 字段描述 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
| num | 学号 | INT(10) | 是 | 否 | 是 | 是 | 否 |
| name | 姓名 | VARCHAR(20) | 否 | 否 | 是 | 否 | 否 |
| sex | 性别 | VARCHAR(4) | 否 | 否 | 是 | 否 | 否 |
| brithday | 出生年份 | DATETIME | 否 | 否 | 否 | 否 | 否 |
| bumen | 院系 | VARCHAR(20) | 否 | 否 | 是 | 否 | 否 |
| address | 家庭住址 | VARCHAR(50) | 否 | 否 | 否 | 否 | 否 |
| 字段名 | 字段描述 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
| id | 编号 | INT(10) | 是 | 否 | 是 | 是 | 否 |
| c_name | 课程名 | VARCHAR(20) | 否 | 否 | 否 | 否 | 否 |
| stu_id | 学号 | INT(10) | 否 | 是 | 是 | 否 | 否 |
| grade | 成绩 | INT(10) | 否 | 否 | 否 | 否 | 否 |
表创建成功后，查看两个表的结构。创建student表，SQL代码如下：
CREATE TABLE student(
num INT(10) NOT NULL UNIQUE PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday DATETIME,
bumen VARCHAR(20) NOT NULL,
address varchar(50)
);查看student表的结构;SQL语句如下：
desc student;创建score表，SQL代码如下：
CREATE TABLE score(
id INT(10) NOT NULLUNIQUE PRIMARY KEY,
c_name VARCHAR(20),
stu_id INT(10) NOT NULL,
grade INT(10),
FOREIGE KEY (stu_id) REFERENCES student (num)
);
查看score表的结构;SQL语句如下：
desc score;Student练习数据如下：
901,'张军','男',1995,'计算机系','北京市海淀区'
902,'张超','男',1996,'中文系','北京市昌平区'
903,'张美','女',2000,'中文系','湖南省永州市'
904,'李五一','男',2000,'英语系','辽宁省阜新市'
905,'王芳','女',2001,'英语系','福建省厦门市'
906,'王桂','男',1998,'计算机系','湖南省衡阳市'
INSERT INTO student
VALUES(901,'张军','男',1995,'计算机系','北京市海淀区'),
(902,'张超','男',1996,'中文系','北京市昌平区'),
(903,'张美','女',2000,'中文系','湖南省永州市'),
(904,'李五一','男',2000,'英语系','辽宁省阜新市'),
(905,'王芳','女',2001,'英语系','福建省厦门市'),
(906,'王桂','男',1998,'计算机系','湖南省衡阳市');score表练习数据如下：
901,'计算机',98
901,'英语',80
902,'计算机',65
902,'中文',88
903,'中文',95
904,'计算机',70
904,'英语',92
905,'英语',94
906,'计算机',90
906,'英语',85
INSERT INTO score
VALUES(901,'计算机',98),
(901,'英语',80),
(902,'计算机',65),
(902,'中文',88).
(903,'中文',95),
(904,'计算机',70),
(904,'英语',92),
(905,'英语',94),
(906,'计算机',90),
(906,'英语',85);
按照下列要求进行表操作：
（1）查询student表的所有记录。
方法一：用”*“。SQL语句如下：
SELSETE * FROM student;方法二：列出所有的列名。SQL语句如下：
SELECT num,name,sex,birthday,bumen,address FROM student;（2）查询student表的第二条到第四条记录。SQL语句如下：
SELECT * FROM student LIMIT 1,3;（3）从student表查询所有学生的学号、姓名和院系的信息。SQL语句如下：
SELECT num,name,bumen, FROM student;（4）查询计算机系和英语系的学生的信息。
方法一：使用IN关键字,SQL语句如下：
SELECT * FROM student
SHERE bumen IN('计算机系','英语系');方法二：使用OR关键字,SQL语句如下：
SELECT * FROM student
SHERE bumen IN = '计算机系' OR bumen = '英语系';（5）从student表中查询年龄为18到22岁的学生的信息。
方法一：使用BETWEEN AND 关键字来查询,SQL语句如下：
SELECT * FROM student
SHERE year (now()) - birthday BETWEEN 20 AND 32;方式二：使用 AND 关键字和比较运算符,SQL语句如下：
SELECT * FROM student
SHERE year (now()) - birthday >= 20 AND year (now()) - birthday <= 32;（6）student表中查询每个院系有多少人，为统计的人数列取别名sum_of_bumen。SQL语句如下：
SELECT bumen,count(num) AS sum_of_bumen FROM student group BY bumen;（7）从score表中查询每个科目的最高分。SQL语句如下：
SELECT c_name,MAX(grade) FROM score group BY c_name; （8）查询李五一的考试科目（c_name）和考试成绩（grade）。SQL语句如下：
SELECT c_name,grade FROM score
WHERE stu_id IN(
SELECT num FROM student
WHERE name = '李五一';（9）用连接查询的方式查询所有学生的信息和考试信息。SQL语句如下：
SELECT student.num,name,sex,birthday,bumen,address,score.c_name,score.grade
FROM student LEFT JOIN score ON student.num = score.stu_id;（10）计算每个学生的总成绩（需显示学生姓名）,SQL语句如下：
SELECT name,sum(grade) FROM student
s LEFT join SCORE sc ON s.num = sc.stu_id group BY num;（11）计算每个考试科目的平均成绩。SQL语句如下：
SELECT c_name,avg(grade) FROM score
group BY c_name;（12）查询计算机成绩低于95的学生的信息。SQL语句如下：
SELECT * FROM student
WHERE num IN(
SELECT stu_id FROM score
WHERE c_name = '计算机' AND grade , 95);（13）查询同时参加计算机和英语考试的学生的信息。SQL语句如下：
SELECT * FROM student
WHERE num = ANY(
SELECT stu_id FROM score
WHERE stu_id IN(
SELECT stu_id FROM score
WHERE c_name = '计算机' AND c_name = '英语'); （14）将计算机成绩按从高到低进行排序。SQL语句如下：
SELECT c_name,grade FROM score
WHERE c_name = '计算机' ORDER BY grade DESC;（15）从student表和score表中查询出学生的学号，然后合并查询结果。SQL语句如下：
SELECT num FROM student
UNION SELECT stu_id FROM score;（16）查询姓张或者姓王的同学的姓名、院系、考试科目和成绩。SQL语句如下：
SELECT student.name,bumen,c_name,grade FROM student,score
WHERE (name LIKE '张%' OR name LIKE '王%'
AND student.num = score.stu_id;（17）查询都是湖南的同学的姓名、年龄、院系、考试科目和成绩。SQL语句如下：
SELECT name,brithday,bumen,c_name,grade FROM student,score
WHERE (address LIKE '湖南%')
AND student.num = score.stu_id;四、观察与思考。
1、LIKE的通配符有哪些？分别代表什么含义？
答：%：有零个或更多个字符组成的任意字符串
_ ：任意单个字符
[ ]：用于指定范围，例如[A ~ F]，表示A ~ F范围内的任何单个字符
[ ^ ]：表示指定范围之外的，例如[ ^A ~ F ]，表示A ~ F 范围以外的任何单个字符。
2、知道学生的出生日期，如何求出其年龄？
答：SQL计算年龄可以使用求时间差值的函数：datediff。若出生日期列名为：date,那么SQL语句应该这样写：
select datediff(yyyy,col1,getdate()) as 周岁,datediff(yyyy,date,getdate())+1 as 虚岁 from 学生表
3、IS能用“=”来代替吗？如何周全地考虑“空数据”的情况？
答：不可以的，is是用来判断null的，比如 remark is null 或者remark is not null=是用来直接比较值的。
4、关键字ALL和DISTINCT有什么不同的含义？关键字ALL是否可以省略不写？
答：all：返回所有的记录；distinct：去掉查询返回的记录中重复的记录。all在select子句里省略，对结果无影响，在union子句里省略则表示剔除有重复的行，反之则保留所有记录行。
5、聚集函数能否直接使用在SELECT子句、HAVING子句、WHERE子句、GROUP BY子句中？
答：聚集函数只能用于select子句，group by中的having子句，where子句是一个条件语句，在where后面跟的是条件。
6、WHERE子句与HAVING子句有何不同？
答：作用对象不同：where子句作用于基本表或视图，从中选择满足条件的元祖，having子句作用于组，从中选择满足条件的组。
7、 count(*)、count(列名)、count(distinct 列名)三者的区别是什么？通过一个实例说明。
答：count(*):明确的返回数据表中的元组数据个数，不会忽略值为null的字段
count(列名):返回数据表中的某列数据个数，不统计值为null的字段
count(disinct 列名):返回数据表中某列不重复的数据个数，不统计值为null的字段
select count(distinct d_id) from yuangong;
select count(d_id) from yuangong;
select count(*) from yuangong;
8、 内连接与外连接有什么区别？
答：内连接：比较运算符根据每个表共有的列的值匹配两个表中的行（使用像 = 或 <> 之类的比较运算符）外连接：当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值（使用left/right/full join或left/right/full outer join关键字）
9、“=”与IN在什么情况下作用相同？
答：当in的候选值只有一个时作用相同。
五、学习反思。
通过本次的实验，我成功掌握SELECT 语句的基本语法格式、SELECT 语句的执行方法以及SELECT 语句的 GROUP BY 和 ORDER BY 子句的作用。但是自己在操作中还存在许多输入错误，导致自己的实验进度比较慢。
3548
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
