3.2 数据定义 SQL 语句
一、基础
-- 数据库创建：
CREATE DATABASE CourseDB;
-- 数据库修改：
ALTER DATABASE CourseDB RENAME TO CourseManangeDB;
-- 数据库删除：
DROP DATABASE CourseManageDB;
二、数据库表创建
PRIMARY KEY -- 主键
NOT NULL -- 非空值
NULL -- 空值
UNIQUE -- 值唯一
CHECK -- 有效性检查
DEFAULT -- 缺省值
CREATE TABLE Student
(StudentID char(13) PRIMARY KEY,
StudentName varchar(10) NOT NULL,
StudentGender char(2) NULL,
BirthDay date NULL,
Major varchar(30) NULL,
StudentPhone char(11) NULL
);
在创建下面课程信息表 Course 时，如何在 SQL 语句中定义列取值唯一、缺省值以及取值范围约束？
CREATE TABLE Course
(CourseID char(4) PRIMARY KEY,
CourseName varchar(20) NOT NULL UNIQUE,
CourseType varchar(10) NULL CHECK(CourseType IN('基础课','专业课','选修课')),
CourseCredit smallint NULL,
CoursePeriod smallint NULL,
TestMethod char(4) NULL DEFAULT '闭卷考试'
);
CREATE TABLE Plan
(CourseID char(4) NOT NULL,
TeacherID char(4) NOT NULL,
CourseRoom varchar(30),
CourseTime varchar(30),
Note varchar(50),
CONSTRAINT CoursePlan_PK PRIMARY Key(CourseID, TeacherID)
);
使用表约束主键的好处：
- 便于定义复合主键
- 可命名主键约束
- 便于定义代理键
CREATE TABLE Plan
(CoursePlanID serial NOT NULL,
-- 注：代理键的数据类型为 serial，列完整性约束为 NOT NULL
CourseID char(4) NOT NULL,
TeacherID char(4) NOT NULL,
CourseRoom varchar(30),
CourseTime varchar(30),
Note varchar(50),
CONSTRAINT CoursePlanID_PK PRIMARY Key(CoursePlanID) -- 这里与定义主键时相同
);
CREATE TABLE Register
(CourseRegID serial NOT NULL,
CoursePlanID Int NOT NULL,
StudentID char(13) NOT NULL,
Note varchar(30) NULL,
CONSTRAINT CourseRegID_PK PRIMARY Key(CourseRegID),
CONSTRAINT CoursePlanID_FK FOREIGN KEY(CoursePlanID)
REFERENCES Plan(CoursePlanID) -- 指将Register表中的CoursePlanID列和Plan表中的CoursePlanID列关联
ON DELETE CASCADE, -- 这句表示如果与之关联的Plan表中的记录被删除，那么Register表中的相应记录也会被删除
CONSTRAINT StudentID_FK FOREIGN KEY(StudentID)
REFERENCES Student(StudentID)
ON DELETE CASCADE
);
三、修改表结构 SQL 语句
-- 基本格式：
ALTER TABLE <表名> <修改方式>;
-- 主要语句类型
-- ADD修改方式，用于增加新列or列完整性约束
ALTER TABLE <表名> ADD <新列名称><数据类型>|[完整性约束];
-- DROP修改方式，用于删除指定列or列的完整性约束条件
ALTER TABLE <表名> DROP COLUMN <列名>;
ALTER TABLE <表名> DROP CONSTRAINT <列名>;
-- RENAME修改方式，用于修改表名称、列名称
ALTER TABLE <表名> RENAME TO <新表名>;
ALTER TABLE <表名> RENAME <原列名> TO <新列名>;
-- ALTER修改方式，用于修改列的数据类型
ALTER TABLE <表名> ALTER COLUMN <列名> TYPE <新的数据类型>;
-- 在Student表中新增一个'email'列
ALTER TABLE Student ADD email varchar(20);
四、删除表结构SQL语句
-- 基本格式
DROP TABLE <表名>; -- 注：这样会删除该表的所有数据及其结构
-- 例：删除注册表Register及其数据
DROP TABLE Register;
五、索引
索引（Index）是一种数据结构，用于提高对表中数据的检索速度和效率。
索引可以看作是表中某一列或多列的排序列表，其中列值以及对应的行位置。索引使数据库系统能够更快地定位和访问数据行，特别是在大型表中或者需要频繁查询的列上。
- 优点：
- 提高关系表数据检索速度
- 可快速连接关联表
- 减少分组和排序时间
- 局限：
- 创建和维护索引需要较大时间开销
- 索引占用额外存储空间
- 操作索引带来额外系统性能开销
-- 索引创建基本格式
CREATE INDEX <索引名> ON <表名><(列名)>;
-- 例
CREATE INDEX Birthday_Idx ON STUDENT (Birthday);
-- 索引修改基本格式
ALTER INDEX <索引名> <修改项>;
-- 例
ALTER INDEX Birthday_Idx RENAME TO Bday_Idx;
-- 索引删除基本格式
DROP INDEX <索引名>;
DROP INDEX Bday_idx;
- 索引引用效果对比如下：
因为全表扫描时，数据库需要遍历整个表的数据，而索引并不会提供太大的帮助，因为它仍然需要扫描整个索引树。
当数据库执行全表扫描时，它会一次性读取大量的数据块，这些数据块通常会在内存中进行缓存，从而减少了从磁盘读取数据的次数。因此，无论是否使用索引，实际上的磁盘 I/O 操作并没有太大区别。
- 在全表查询时，数据库优化器甚至可能会选择不使用索引，因为使用索引可能会导致额外的开销，比如索引的扫描和读取。
3.3 数据操纵 SQL 语句
一、数据插入
-- 基本格式
INSERT INTO <表名|视图名>[<列表名>] VALUES (列表值);
-- 例
INSERT INTO Student VALUES('2020012608024','科比','男','1978-08-23','软件工程','BlackMamba@gmail.com');
-- 多条数据插入
INSERT INTO Student VALUES
('2020012608024','牢大','男','1978-08-23','肘击篮球','BlackMamba@whatcanisay.man',
'2018010109009','坤坤','男','1998-08-02','唱跳篮球','GennyTieMay@niganma.aiyo'
);
二、数据更新
-- 基本格式
UPDATE <表名|视图名>
SET <列名1>=<表达式1>[,<列名2>=<表达式2>...]
[WHERE <条件表达式>];
-- 例：
UPDATE Student
SET Email='IcedTea@elbowing.nba'
WHERE StudentName = '牢大'; -- 注：如果没有WHERE子句，执行后将会改变所有行！
三、数据删除
-- 基本格式
DELETE
FROM <表名|视图名>
[WHERE <条件表达式>];
-- 例：
DELETE
FROM Student
WHERE StudentName = '坤坤'; -- -- 注：如果没有WHERE子句，执行后将会删除所有行！
四、表数据清空
-- 基本格式
TRUNCATE TABLE <表名>;
-- 例：
TRUNCATE TABLE Student;
TRUNCATE 和在表上执行无条件 DELETE 语句效果相同而且速度更快（因为 DELETE 还需要做索引维护，TRUNCATE 没有实际扫描表）
3.4 数据库查询 SQL 语句
一、数据查询语句
-- 基本格式如下：
SELECT [ ALL | DISTINCT ] <目标列>[,<目标列>...]
[INTO <>]
FROM <表名|视图名>[,<表名|视图名>...]
[WHERE <条件表达式>]
[GROUP BY <列名>[HAVING <条件表达式>]]
ORDER BY <列名>[ASC|DESC];
二、从单个表读取指定列
-- 基本格式如下：
SELECT <目标列>[,<目标列>...]
FROM <关系表>;
例子如下：
-- 查询Student表数据所有列数据
SELECT *
FROM Student;
-- 只查询部分列，如 Major 列
SELECT Major
FROM Student;
-- 为了过滤重复数据，可以：
SELECT DISTINCT Major
FROM Student;
三、从单个表读取指定行
-- 基本格式如下：
SELECT *
FROM <关系表>
WHERE <条件表达式>;
例子如下：
-- 从Student表中查询男生数据
SELECT *
FROM Student
WHERE StudentGender='男';
四、从单个表读取指定行和列
-- 基本格式如下：
SELECT <目标列>[,<目标列>...]
FROM <关系表>
WHERE <条件表达式>;
例子如下：
-- 从Student表中查询性别为“男”的学生学号、学生姓名、性别、专业数据
SELECT StudentID, StudentName, StudentGender, Major
FROM Student
WHERE StudentGender = '男';
五、Where条件字句
-- 从Student表中查询出生日期在“2000-01-01”到“2000-12-30”的学生数据：
SELELT *
FROM Student
WHERE Birthday BETWEEN '2000-01-01' AND '2000-12-30';
-- 从Student表中查询邮箱域名为"@163.com"的学生数据：
SELECT *
FROM Student WHERE Email LIKE '%@163.com'; -- LIKE 用于模糊匹配的操作符, 匹配以'%@163.com'结尾的邮箱
-- %是用作通配符，用于表示任意字符序列
-- 从Student表中查询性别为“男”并且专业为“软件工程”的学生数据：
SELECT StudentID,StudentName,StudentGender,Major
FROM Student WHERE Major='软件工程' AND StudentGender='男'；
-- 在Student表查询时，使用 IN 关键词限定范围“计算机应用”、“电子工程”、“软件工程”专业的学生：
SELECT StudentID,StudentName,StudentGender,Major
FROM Student
WHERE Major IN ('计算机应用', '电子工程', '软件工程');
六、对结果集进行排序
-- 从Student表中按学生出生日期降序输出学生数据：
SELECT *
FROM Student
ORDER BY Birthday DESC;
-- ASC:升序 DESC:降序
-- 若要对多个列排序：
SELECT *
FROM Student
ORDER BY Birthday DESC, StudentName ASC;
七、SQL内置函数类型
- 聚合函数
- 算术函数
- 字符串函数
- 日期时间函数
- 数据类型转换函数
八、SQL聚合函数
AVG() -- 计算结果集指定列数据的平均值
COUNT() -- 计算结果集行数
MIN() -- 找出结果集指定列数据的最小值
MAX() -- 找出结果集指定列数据的最大值
SUM() -- 计算结果集指定列数据的总和
例子如下：
-- 统计Student表中的学生人数：
SELECT COUNT(*) AS 学生人数
FROM Student;
-- 找出Student表中年龄最大和年龄最小的学生出生日期：
SELECT Min(Birthday) AS 最大年龄, Max(Birthday) AS 最小年龄
FROM Student;
九、SQL内置函数与分组统计
-- 基本格式如下：
SELECT 统计函数（目标列）
FROM <表名>
[WHERE 条件]
GROUP BY <目标列>
[Having 条件];
例子如下：
-- 分专业统计Student表中的学生人数，可以在SELECT语句中使用GROUP BY分组子句完成统计：
-- '专业' 是为这个列指定的别名。这样，查询结果中的该列将以“专业”作为名称呈现，而不是原始的列名。
SELECT Major AS 专业，COUNT(StudentID) AS 学生人数
FROM Student
GROUP BY Major;
-- 分专业统计Student表中男生人数，并且限定只统计人数大于2的专业的人数
SELECT Major AS 专业, COUNT(StudentID) AS 学生人数
FROM Student
WHERE StudentGender='男'
GROUP BY Major
HAVING COUNT(*)>2; -- 注：此处用 COUNT(*) 和 COUNT(StudentID) 是等效的，因为 COUNT(*)就是统计行数
-- 在分组统计语句中，不可改变 where 子句和 having 子句的顺序
十、子表查询与多表关联查询
-- 基本格式如下：
SELECT <目标列>[,<目标列>...]
FROM <表名>
WHERE <条件中嵌套另一关系表的SELECT查询结果集>;
例子如下：
-- 在选课管理系统数据库中检索“计算机学院”的教师名单
-- 该操作需要关联教师信息表 Teacher 和学院信息表 College
SELECT TeacherID, TeacherName, TeacherTitle
FROM Teacher
WHERE CollegeID IN
(SELECT CollegeID
FROM College
WHERE CollegeName='计算机学院');
十一、使用连接关联多表查询
-- 基本格式如下：
SELECT <目标列>[,<目标列>...]
FROM <表名1>,<表名2>,...,<表名n>
WHERE <关系表之间的连接关联条件>
例子如下：
-- 在选课管理系统数据库中获取各个学院的教师信息列表
-- 包括学院名称、教师编号、教师姓名、教师性别、职称等信息
-- 要求按学院名称、教师编号分别排序输出
SELECT B.CollegeName AS 学院名称, A.TeacherID AS 编号,
A.TeacherName AS 姓名, A.TeacherGender AS 性别,
A.TeacherTitle AS 职称
FROM Teacher AS A, College AS B
WHERE A.CollegeID=B.CollegeID
ORDER BY B.CollegeName, A.TeacherID; -- 默认升序
十二、SQL JOIN···ON连接查询语句
-- 基本格式如下：
SELECT <目标列>[,<目标列>...]
FROM <表名1> JOIN <表名2> ON <连接条件>
例子如下：
-- 要求同十一点的例子
SELECT B.CollegeName AS 学院名称, A.TeacherID AS 编号,
A.TeacherName AS 姓名, A.TeacherGender AS 性别,
A.TeacherTitle AS 职称
FROM Teacher AS A JOIN COLLEGE AS B
ON A.CollegeID = B.CollegeID
ORDER BY B.CollegeName, A.TeacherID;
十三、外部连接
之前介绍的多表连接方式在 SELECT 查询语句称为内部连接。在一些特殊情况下，如关联表中一些行的主键与外键不匹配，查询结果集就会丢失部分数据。
-- 在选课管理DB中，希望查询所有开设课程的学生选课情况，包括课程名称、任课教师、选课学生人数。
-- 这需要关联课程信息表COURSE、教师信息表TEACHER、开课计划表PLAN、选课注册信息表REGISTER
-- 其连接查询的SQL语句如下：
SELECT C.CourseName AS 课程名称, T.TeacherName AS 教师,
COUNT (R.CoursePlanID) AS 选课人数
FROM COURSE AS C
JOIN PLAN AS P ON C.CourseID = P.CourseID
JOIN TEACHER AS T ON P.TeacherID = T.TeacherID
JOIN REGISTER AS R ON P.CoursePlanID = R.CoursePlanID
GROUP BY C.CourseName, T.TeacherName;
在上面的内连接查询中，只能找出有学生注册的课程名称和选课人数，但不能找出没有学生注册的课程名称
在SQL应用中，有时候也希望输出那些不满足连接条件的元组数据。此时可以用JOIN···ON外连接方式实现。
- LEFT JOIN: 左外连接。即使没有与右表关联列值匹配，也从左表返回所有的行。
- RIGHT JOIN:右外连接。即使没有与左表关联列值匹配，也从右表返回所有的行。
- FULL JOIN: 全外连接。同时进行左连接和右连接，就返回所有行。
-- 条件同上一题，但如果使用左外连接：
SELECT C.CourseName AS 课程名称, T.TeacherName AS 教师,
COUNT (R.CoursePlanID) AS 选课人数
FROM COURSE AS C
JOIN PLAN AS P ON C.CourseID = P.CourseID
JOIN TEACHER AS T ON P.TeacherID = T.TeacherID
LEFT JOIN REGISTER AS R ON P.CoursePlanID = R.CoursePlanID
GROUP BY C.CourseName, T.TeacherName;
3.5 视图SQL语句
一、什么是视图
“视图”（View）是一个虚拟的表，其内容是从一个或多个基本表中导出的。视图是基于 SQL 查询的结果集，它可以包含来自一个或多个表的列和行数据。但是，视图本身并不存储实际的数据，而是根据查询语句动态生成结果。
二、视图创建SQL语句
-- 基本语句如下
CREATE VIEW <视图名>[(列名1),(列名2),...] AS <SELECT查询>;
-- 在选课管理系统数据库中，若需建立一个查看基础课数据的视图BasicCoureseView，其创建SQL语句如下：
CREATE VIEW BasicCourseView AS
SELECT CourseName, CourseCredit, CoursePeriod, TestMethod
FROM COURSE
WHERE CourseType='基础课';
创建视图后，用户可以像访问关系表一样去查询、修改、删除和插入访问视图 BasicCourseView：
SELECT *
FROM BasicCourseView
ORDER BY CourseName;
三、视图删除
-- 基本语句如下：
DROP VIEW <视图名>;
DROP VIEW BasicCourseView;
四、SQL视图应用
1. 使用视图简化复杂SQL查询操作
可以先定义一个名为 DatabaseCourseView 的视图：
CREATE VIEW DatabaseCourseView AS
SELECT C.CourseName AS 课程名称, S.StudentID AS 学号, S.StudentName AS 姓名
FROM COURSE AS C, PLAN AS P, REGISTER AS R, STUDENT AS S
WHERE C.CourseID = P.CourseID AND C.CourseName = '数据库系统原理与开发'
AND P.CoursePlanID = R.CoursePlanID AND R.StudentID = S.StudentID;
-- 然后，就可以：
SELECT * FROM DatabaseCourseView;
2. 使用视图提高数据访问安全性
CREATE VIEW BasicTeacherInfoView AS
SELECT T.TeacherID AS 编号, T.TeacherName AS 教师姓名, T.TeacherGender AS 性别,
T.TeacherTitle AS 职称, C.CollegeName AS 所属学院
FROM TEACHER AS T, COLLEGE AS C
WHERE T.CollegeID = C.CollegeID;
-- 当该视图被创建完成后，外部程序可以通过这个封装后的视图进行 SELECT 语句数据查询:
SELECT * FROM BasicTeacherInfoView
ORDER BY 所属学院,教师姓名;
3. 提供一定程度的数据逻辑独立性
当数据表结构发生改变时，只要视图结构不变，应用程序可以不做修改。
4. 集中展示用户所感兴趣的特定数据
可以将用户不关心的数据进行过滤。
后记·易忘
-
-- 为了过滤重复数据，可以： SELECT DISTINCT Major FROM Student; -
-- 从Student表中查询邮箱域名为"@163.com"的学生数据： SELECT * FROM Student WHERE Email LIKE '%@163.com'; -- LIKE 用于模糊匹配的操作符, 匹配以'%@163.com'结尾的邮箱 -- %是用作通配符，用于表示任意字符序列 -
SELECT * FROM Student ORDER BY Birthday DESC, StudentName ASC; -- ASC:升序 DESC:降序 -
AVG() -- 计算结果集指定列数据的平均值 COUNT() -- 计算结果集行数 MIN() -- 找出结果集指定列数据的最小值 MAX() -- 找出结果集指定列数据的最大值 SUM() -- 计算结果集指定列数据的总和 -
-- 分专业统计Student表中男生人数，并且限定只统计人数大于2的专业的人数 -- '专业' 是为这个列指定的别名。这样，查询结果中的该列将以“专业”作为名称呈现，而不是原始的列名。 SELECT Major AS 专业, COUNT(StudentID) AS 学生人数 FROM Student WHERE StudentGender='男' GROUP BY Major HAVING COUNT(*)>2; -- 注：此处用 COUNT(*) 和 COUNT(StudentID) 是等效的，因为 COUNT(*)就是统计行数 -- 在分组统计语句中，不可改变 where 子句和 having 子句的顺序 -
-- 在选课管理系统数据库中检索“计算机学院”的教师名单 -- 该操作需要关联教师信息表 Teacher 和学院信息表 College SELECT TeacherID, TeacherName, TeacherTitle FROM Teacher WHERE CollegeID IN (SELECT CollegeID FROM College WHERE CollegeName='计算机学院'); -
-- 在选课管理系统数据库中获取各个学院的教师信息列表 -- 包括学院名称、教师编号、教师姓名、教师性别、职称等信息 -- 要求按学院名称、教师编号分别排序输出 SELECT B.CollegeName AS 学院名称, A.TeacherID AS 编号, A.TeacherName AS 姓名, A.TeacherGender AS 性别, A.TeacherTitle AS 职称 FROM Teacher AS A, College AS B WHERE A.CollegeID=B.CollegeID ----------------------------------------- -- FROM Teacher AS A JOIN COLLEGE AS B -- --- 另一种写法 -- ON A.CollegeID = B.CollegeID -- ----------------------------------------- ORDER BY B.CollegeName, A.TeacherID; -- 默认升序 -
-- 在选课管理DB中，希望查询所有开设课程的学生选课情况，包括课程名称、任课教师、选课学生人数。 -- 这需要关联课程信息表COURSE、教师信息表TEACHER、开课计划表PLAN、选课注册信息表REGISTER -- 其连接查询的SQL语句如下： SELECT C.CourseName AS 课程名称, T.TeacherName AS 教师, COUNT (R.CoursePlanID) AS 选课人数 FROM COURSE AS C JOIN PLAN AS P ON C.CourseID = P.CourseID JOIN TEACHER AS T ON P.TeacherID = T.TeacherID JOIN REGISTER AS R ON P.CoursePlanID = R.CoursePlanID GROUP BY C.CourseName, T.TeacherName; -- 以上的 JOIN 均为 INNER JOIN , 可以理解为交集 -- 还有 LEFT JOIN , RIGHT JOIN , FULL JOIN(可以理解为并集)
285
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
