、实验目的

1．掌握MySQL数据库表的数据插入、修改、删除、查询操作SQL语法格式。

二、插入、修改、删除试验

某超市的食品管理的数据库的Food表，Food表的定义如表所示，请完成插入数据、更新数据和删除数据。

Food表的定义

我们先在在数据库里面新建一个food表，代码如下：

CREATE TABLE IF NOT EXISTS food(

foodid INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY ,

name VARCHAR(20) NOT NULL,

company VARCHAR(30) NOT NULL,

price FLOAT NOT NULL,

product_time YEAR,

validity_time INT//查看food表

按照下列要求进行操作：

(1)采用3种方式，将表的记录插入到Food表中。

方法一：不指定具体的字段，插入数据： 'QQ饼干','QQ饼干厂',2.5,'2018',3,'北京'。

INSERT INTO food VALUES (‘1’, 'QQ饼干', 'QQ饼干厂', 2.5, 2018, 3, '北京');

//最前面的“1”是编号，我们前面设置了NOT NULL，所以这里我们可以自己设置编号，也可以用“NULL”代替，NULL会自动生成编号并排序

SELECT * FROM food;

//查看food表中数据

方法二：依次指定food表的字段，插入数据： 'MN牛奶','MN牛奶厂',3.5,'2019',1,'河北')。

INSERT INTO food VALUES (‘2’,’MN牛奶’,'MN牛奶厂',3.5,'2019',1,'河北');

//最前面的“2”是编号，我们前面设置了NOT NULL，所以这里我们可以自己设置编号，也可以用“NULL”代替，NULL会自动生成编号并排序

方法三：同时插入多条记录，插入数据：

    'EE果冻','EE果冻厂',1.5,'2017',2,'北京',

    'FF咖啡','FF咖啡厂',20,'2012',5,'天津',

    'GG奶糖','GG奶糖',14,'2013',3,'广东';

INSERT INTO food VALUES('3','EE果冻','EE果冻厂',1.5,'2017',2,'北京'),

('4','FF咖啡','FF咖啡厂',20,'2012',5,'天津'),

('5','GG奶糖','GG奶糖',14,'2013',3,'广东');

分别写出相应语句。

(2)将“MN牛奶厂”的厂址(address)改为“内蒙古”，并且将价格改为3.2。

UPDATE food

SET address='内蒙古',price='3.2'

WHERE company='MN牛奶厂';

(3)将厂址在北京的公司的保质期(validity_time)都改为5年。

UPDATE food

SET validity_time='5'

WHERE address='北京';

(4)删除过期食品的记录。若当前时间-生产年份(producetime)>保质期（validity_time），则视为过期食品。

DELETE FROM food

WHERE YEAR(CURDATE()) - product_time > validity_time;

//现在时间为2024，所以每一个食品都已经过期了。

(5)删除厂址为“北京”的食品的记录。

DELETE FROM food

WHERE address=’北京’;

三、查询试验

将在student表和score表上进行查询。Student表和score表的定义如表所示：

Student表的内容

score表的内容

表创建成功后，查看两个表的结构。

CREATE TABLE IF NOT EXISTS student(

num INT(10) NOT NULL PRIMARY KEY ,

name VARCHAR(20) NOT NULL,

sex VARCHAR(4) NOT NULL,

birthday DATETIME NOT NULL,

bumen VARCHAR(20) NOT NULL,

address VARCHAR(50)

)ENGINE=INNODB CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS score(

id INT(10) NOT NULL PRIMARY KEY,

C_name VARCHAR(20),

stu_id INT(10) NOT NULL,

grade INT(10),

CONSTRAINT score_fk FOREIGN KEY (stu_id) REFERENCES student(num)

)ENGINE=INNODB CHARSET=UTF8;

SELECT * FROM score;//Score表中stu_id的外键引用的是student的num列

Student练习数据如下：

901,'张军','男',1985,'计算机系','北京市海淀区'

902,'张超','男',1986,'中文系','北京市昌平区'

903,'张美','女',1990,'中文系','湖南省永州市'

904,'李五一','男',1990,'英语系','辽宁省阜新市'

905,'王芳','女',1991,'英语系','福建省厦门市'

906,'王桂','男',1988,'计算机系','湖南省衡阳市'

我们可以看见“1985”这样的字段是错误的，birthday的类型是DATETIME，所以我们需要用正确详细的格式，例如下图：

score表练习数据如下：

901,'计算机',98

901,'英语',80

902,'计算机',65

902,'中文',88

903,'中文',95

904,'计算机',70

904,'英语',92

905,'英语',94

906,'计算机',90

906,'英语',85

INSERT INTO score (id, C_name, stu_id, grade) VALUES

(1, '计算机', 901, 98),

(2, '英语', 901, 80),

(3, '计算机', 902, 65),

(4, '中文', 902, 88),

(5, '中文', 903, 95),

(6, '计算机', 904, 70),

(7, '英语', 904, 92),

(8, '英语', 905, 94),

(9, '计算机', 906, 90),

(10, '英语', 906, 85);

SELECT * FROM score;

然后按照下列要求进行表操作：

（1）查询student表的所有记录。

方法一：用”*“。

SELECT * FROM student;

方法二：列出所有的列名。

SELECT num, name, sex, birthday, bumen, address FROM student;

（2）查询student表的第二条到第四条记录。

SELECT * FROM student

LIMIT 3 OFFSET 1;

//将 OFFSET 设置为 1，本来位置是在1，我们要获取第二条，所以偏移量是1，获取3条，将 LIMIT 设置为 3。

这将返回从第二条开始的三条记录，即第二条、第三条和第四条。

（3）从student表查询所有学生的学号、姓名和院系的信息。

SELECT num,name,bumen FROM student;

（4）查询计算机系和英语系的学生的信息。

方法一：使用IN关键字

SELECT * FROM student

WHERE bumen IN ('计算机系', '英语系');

方法二：使用OR关键字

SELECT * FROM student

WHERE bumen = '计算机系' OR bumen = '英语系';

（5）从student表中查询年龄为18到22岁的学生的信息。

方法一：使用BETWEEN AND 关键字来查询

SELECT * FROM student

WHERE TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 18 AND 22;

方式二：使用 AND 关键字和比较运算符。

SELECT * FROM student

WHERE TIMESTAMPDIFF(YEAR, birthday, CURDATE()) >= 18

AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) <= 22;

（6）student表中查询每个院系有多少人，为统计的人数列取别名sum_of_bumen。

SELECT bumen, COUNT(*) AS sum_of_bumen

FROM student

GROUP BY bumen;

（7）从score表中查询每个科目的最高分。

SELECT C_name, MAX(grade) AS highest_grade

FROM score

GROUP BY C_name

ORDER BY highest_grade DESC;

SELECT * FROM score;

（8）查询李五一的考试科目（c_name）和考试成绩（grade）。

SELECT s.name, sc.c_name, sc.grade

FROM student s

JOIN score sc ON s.num = sc.stu_id

WHERE s.name = '李五一';

SELECT * FROM score;

（9）用连接查询的方式查询所有学生的信息和考试信息。

SELECT

    student.num,

    student.name,

    student.sex,

    student.birthday,

    student.bumen,

    student.address,

    score.id AS score_id,

    score.C_name,

    score.grade

FROM

    student

INNER JOIN

    score

ON

    student.num = score.stu_id;

（10）计算每个学生的总成绩（需显示学生姓名）。

SELECT student.name AS student_name, SUM(score.grade) AS total_score

FROM score

JOIN student ON score.stu_id = student.num

GROUP BY student.name;

（12）计算每个考试科目的平均成绩。

SELECT C_name, AVG(grade) AS average_grade

FROM score

GROUP BY C_name;

（13）查询计算机成绩低于95的学生的信息。

SELECT s.*

FROM student s

JOIN score sc ON s.num = sc.stu_id

WHERE sc.C_name = '计算机' AND sc.grade < 95;

（14）查询同时参加计算机和英语考试的学生的信息。

SELECT s1.stu_id, s1.C_name AS Course1, s1.grade AS Grade1, s2.C_name AS Course2, s2.grade AS Grade2

FROM score s1

JOIN score s2 ON s1.stu_id = s2.stu_id

WHERE s1.C_name = '计算机' AND s2.C_name = '英语';

（15）将计算机成绩按从高到低进行排序。

SELECT * FROM score

WHERE C_name = '计算机'

ORDER BY grade DESC;

（16）从student表和score表中查询出学生的学号，然后合并查询结果。

SELECT s.num AS student_id, s.name AS student_name, sc.C_name AS course_name, sc.grade AS course_grade

FROM student s

JOIN score sc ON s.num = sc.stu_id;

（17）查询姓张或者姓王的同学的姓名、院系、考试科目和成绩。

SELECT

    student.name AS 学生姓名,

    student.bumen AS 院系,

    score.C_name AS 考试科目,

    score.grade AS 成绩

FROM

    student

JOIN

    score ON student.num = score.stu_id

WHERE

student.name LIKE '张%' OR student.name LIKE '王%';

（18）查询都是湖南的同学的姓名、年龄、院系、考试科目和成绩。

SELECT

    s.name AS 学生姓名,

    s.bumen AS 院系,

    sc.C_name AS 考试科目,

    sc.grade AS 成绩

FROM

    student s

JOIN

    score sc ON s.num = sc.stu_id

WHERE

s.address LIKE '%湖南%';