《数据库原理与应用》

一.Mysql操作环境及数据库操作

1.1创建数据库

使用SQL语句创建数据库，设置默认字符集为utf8mb4,设置默认校对规则为utf8mb4_ general_ci，数据库的名称为teaching。

create database if not exists teaching
default character set utf8mb4
default collate utf8mb4_general_ci;

1.2查看数据库

使用SQL语句查看数据库teaching，请给出SQL代码。

show create database teaching;

1.3修改数据库

使用SQL语句修改数据库，将teaching数据库中的默认字符修改为gbk,默认校队规则修改为gbk_chinese_ci，请给出SQL代码。

alter database teaching
default character set gbk
default collate gbk_chinese_ci;

1.4删除数据库

使用SQL语句删除teaching数据库

drop database if exists teaching;

二. 表的创建和管理

2.1 表的创建

2.1.1 学生信息表student： 学号、姓名、性别、年龄、专业、院系

字段名	数据类型	默认值	完整性约束	主码/外码约束
Sno	Char(10)		…not null…	主码
Sname	varchar(45)		unique
Sex	Enum(男，女)	男	Not null
Age	int		Not null
Maj	varchar(45)
Dept	varchar(45)

CREATE TABLE 'Sys'.'student'(
'Sno' CHAR(18) NOT NULL,
'Sname' VARCHAR(45) NOT NULL,
'Sex' ENUM('男','女') NOT NULL DEFAULT '男',
'Age' INT NOT NULL,
'Maj' VARCHAR(45) NOT NULL,
'Dept' VARCHAR(45) NOT NULL,
PRIMARY KEY( 'Sno'));

1. 2.1.2 教师信息表t： 教师号、姓名、性别、年龄、职称、工资、专业、院系

字段名	数据类型	默认值	完整性约束	主码/外码约束
Tno	Char(10)		…not null…	主码
Tname	varchar(45)		unique
Sex	Enum(男，女)	男	Not null
Age	int		Not null
Prof	varchar(45)		Not null
Sal	Decimal(6,2)		Not null
Maj	varchar(45)		Not null
Dept	varchar(45)		Not null

'Tno'  CHAR(10) NOT NULL,
'Tname' VARCHAR(45) NOT NULL,
'Sex' ENUM('男','女') NOT NULL DEFAULT '男',
'Age'  INT NOT NULL,
'Prof' VARCHAR(45) NOT NULL,
'Sa1' DECIMAL(6,2) NOT NULL,
'Maj' VARCHAR(45) NOT NULL,
'Dept' VARCHAR(45) NOT NULL,
PRIMARY KEY( 'Tno'));

2.1.3 课程信息表 course :课程号、课程名、课时

字段名	数据类型	默认值	完整性约束	主码/外码约束
Cno	Char(10)		…Not null…	主码
课程名称	varchar(45)		Not null
Ct	int		Not null

CREATE TABLE 'sys'.'course'(
'Cno' CHAR(10) NOT NULL,
'课程名称' VARCHAR(45) NOT NULL,
'Ct' INT NOT NULL,
PRIMARY KEY('CnO'));

2.1.4 选课表sc:学号、课程号、教师号，成绩、开课日期

字段名	数据类型	默认值	完整性约束	主码/外码约束
Sno	Char(10)		…Not null…	主码、外码
Cno	Char(10)		Not null	主码、外码
Tno	Char(10)		Not null	主码、外码
Score	Decimal(4,2)		取值范围为0-100
Tcdate	Date

 CREATE TABLE 'sys'.'sc'(
'Sno' CHAR(10) NOT NULL,
'Cno' CHAR(10) NOT NULL,
'TnO' CHAR(10) NOT NULL,
'Score' DECIMAL(4,2) CHECK(Score>0 and Score<100) NULL,
'Tcdate'  DATE NULL,
PRIMARY KEY('Sno','cno','Tno' ));

2.2 表的管理

2.2.1 表的查看

（1）查看2.1中已创建的表。

（2）查看2.1中学生信息表student的表结构。

（3）查看2.1中教师信息表t中数据。

SHOW TABLES;

DESC student;

SHOW CREATE TABLE t;

2.2.2 表的修改

（1）在学生信息表student中增加一个班号class_no的字段.

alter table student
add class_no varchar(6);

（2）将教师信息表t名称改为teacher.

alter table t
rename teacher;

（3）将课程信息表course中字段名称课程名称改为Cname.

alter table course
change 课程名称 Cname varchar(45);

（4）将学生信息表student中Sname字段的数据类型改为varchar(30).

alter table student
modify Sname varchar(30);

（5）删除学生信息表student中的class_no字段.

alter table student
drop class_no;

2.2.3 表的删除

（1）删除选课表sc.

drop table if exists sc;

三.数据操纵语言

3.1 插入

3.1.1 学生信息表student： 学号、姓名、性别、年龄、专业、院系

学号	姓名	性别	年龄	专业	院系
s1	王彤	女	18	计算机	信息学院
s2	苏乐	女	20	信息	信息学院
s3	林昕	男	19	信息	信息学院
s4	陶然	女	18	自动化	工学院
s5	魏立	男	17	数学	理学院
s6	何欣荣	女	21	计算机	信息学院

INSERT INTO student(sno, sname, sex,age,maj, dept)
VALUES('s1', '王彤', '女', 18, '计算机','信息学院'),
('s2', '苏乐', '女', 20, '信息','信息学院'),
('s3', '林昕', '男', 19, '信息','信息学院'),
('s4', '陶然', '女', 18, '自动化','工学院'),
('s5', '魏立', '男', 17, '数学','理学院'),
('s6', '何欣荣', '女', 21, '计算机','信息学院');

3.1.2 教师信息表teacher： 教师号、姓名、性别、年龄、职称、工资、专业、院系

教师号	姓名	性别	年龄	职称	工资	专业	院系
t1	刘杨	男	40	教授	3610.5	计算机	信息学院
t2	石丽	女	26	讲师	2923.3	信息	信息学院
t3	顾伟	男	32	副教授	3145	计算机	信息学院
t4	赵礼	女	50	教授	4267.9	自动化	工学院
t5	赵希希	女	36	副教授	3332.67	数学	理学院
t6	张刚	男	30	讲师	3012	自动化	工学院

INSERT INTO teacher(tno, tname, sex,age,prof,sal,maj, dept)
VALUES('t1', '刘杨', '男', 40,'教授','3610.5', '计算机','信息学院'),
('t2', '石丽', '女', 26,'讲师','2923.3', '信息','信息学院'),
('t3', '顾伟', '男', 32,'副教授','3145', '计算机','信息学院'),
('t4', '赵礼', '女', 50,'教授','4267.9', '自动化','工学院'),
('t5', '赵希希', '女', 36,'副教授','3332.67', '数学','理学院'),
('t6', '张刚', '男', 30,'讲师','3012', '自动化','工学院');

3.1.3 课程信息表 course :课程号、课程名、课时

课程号	课程名	课时
c1	Java程序设计	40
c2	程序设计基础	48
c3	线性代数	48
c4	数据结构	64
c5	数据库系统	56
c6	数据挖掘	32

INSERT INTO course(cno, sname, ct)
VALUES('c1', 'Java程序设计', '40'),
('c2', '程序设计基础', '48'),
('c3', '线性代数', '48'),
('c4', '数据结构', '64'),
('c5', '数据库系统', '56'),
('c6', '数据挖掘', '32');

3.1.4 选课表sc:学号、课程号、教师号，成绩、开课日期

学号	课程号	教师号	成绩	开课日期
s1	c1	t1	90.5	20210903
s1	c2	t1	85	20210904
s3	c2	t3	70.5	20210308
s4	c1	t1	93	20210903
s5	c5	t6		20210906
s6	c6	t5		20210910

INSERT INTO sc(sno, cno, tno,score,tcdate)
VALUES('s1','c1','t1', '90.5', '20210903'),
('s1','c2','t1', '85', '20210903'),
('s3','c2','t3', '70.5', '20210903'),
('s4','c1','t1', '93', '20210903'),
('s5','c5','t6', null, '20210903'),
('s6','c6','t5', null, '20210903');

3.2 修改

3.2.1 将刘杨老师转到工学院。

UPDATE teacher
SET dept='工学院'
WHERE tname='刘杨';

3.2.2 将所有学生的年龄增加一岁。

UPDATE student
SET age=age+1;

3.2.3 将所有课程的课时提高到原来的1.5倍

UPDATE course
SET ct=1.5*ct;

3.2.4 将s1学生的性别改为男

UPDATE student
SET sex='男'
WHERE sno='s1';

3.2.5 将赵希希老师的职称改为教授

UPDATE teacher
SET prof='教授'
WHERE tname='赵希希';

3.2.6 将c3课程的课时改为60

UPDATE course
SET ct='60'
WHERE cno='c3';

3.3 删除

3.3.1 删除成绩为90.5分的记录。

DELETE FROM sc
WHERE score=90.5;

3.3.2 删除课程号为c3的课程信息

DELETE FROM course
WHERE Cno='c3';

3.3.3 删除课程号为c6的课程信息

DELETE FROM course
WHERE Cno='c6';

3.3.4 删除所有学生的选课记录

DELETE FROM sc;

四.简单查询（基本查询与条件查询）

4.1 无条件查询

4.1.1查询课程表中的全部数据

select *
FROM c;

4.1.2查询已选课的学生的学号

SELECT DISTINCT sno
FROM sc;

4.2 条件查询

4.2.1查询成绩在90分及其以上的选课信息

SELECT *
FROM sc
WHERE score>=90;

4.2.2查询职称为教授的教师的教师号、姓名和专业

SELECT tno,tname,maj
FROM teacher
WHERE prof IN ('教授')

4.2.3查询专业是计算机和数学的学生信息

SELECT *
FROM student
WHERE maj IN('计算机','数学')

4.2.4查询年龄在30-40（包括30和40）岁的教师的教师号、姓名和职称

SELECT tno,tname,prof
FROM teacher
WHERE age>=30 AND age<=40

4.2.5查询课时不在30-40（包括30和40）课时的课程的课程号、课程名和课时

SELECT *
FROM course
WHERE ct>=30 AND ct<=40

4.2.6查询课程号为c4和c6的选课信息，包括学号、课程号和成绩。

SELECT sno,cno,score
FROM sc
WHERE cno='c4' OR cno='c6'

4.2.7查询年龄大于20的学生的学号、姓名和年龄， 结果列名为汉字

SELECT sno 学号,sname 姓名,age 年龄
FROM student
WHERE age>20

4.2.8查询选修了课程的学生学号

SELECT DISTINCT sno
FROM sc

4.2.9查询不是计算机系或信息系学生

SELECT *
FROM student
WHERE maj NOT IN('计算机','信息')

4.2.10查询姓名长度至少是三个汉字且第三个汉字必须是“马”的学生

SELECT *
FROM student
WHERE sname LIKE '__马%'

4.2.11查询姓名中含有“然”的学生信息

SELECT *
FROM student
WHERE sname LIKE '%然%'

4.2.12查询选修't3'老师，成绩在80至90之间学生的信息

SELECT sno FROM sc WHERE (score>=80 AND score<=90)AND tno='t3'

4.2.13查询没有成绩的学生的学号和课程号

SELECT sno,cno
FROM sc
WHERE score IS Null

4.2.14查询学号为s1的学生的课程的平均分

SELECT AVG(score)
FROM sc
WHERE sno='s1'

4.2.15查询选课表sc中选课学生人数

SELECT COUNT(DISTINCT sno) FROM sc

4.2.16查询选课表sc中每个学生的选课信息及每个学生的选课门数

SELECT sno 学号, COUNT(*) 选课门数 ,
FROM sc GROUP BY sno

4.2.17查询选课表sc中选了1门以上课程的学生的选课信息及学生的选课门数（不包括1门）

SELECT *
FROM sc
GROUP BY sno HAVING COUNT(sno)>1

4.2.18查询学号为s1的学生的选课信息，按照成绩降序排序

SELECT *
FROM sc
WHERE sno='s1'
ORDER BY score DESC

4.2.19查询从第三位学生开始的4位学生的信息

SELECT *
FROM student
where sno>='s3'

4.2.20查询选课表sc中每门课程的课程号及选课人数，按照选课人数降序排列，并且显示前5行

SELECT cno 课程号, COUNT(*) 选课人数
FROM sc
GROUP BY cno
ORDER BY COUNT(*) DESC LIMIT 5