目录
一、实验目的
实验一: 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 子句的作用。但是自己在操作中还存在许多输入错误,导致自己的实验进度比较慢。