SQL——基本表
环境
DBMS：MySQL 8.0.17
工具：Navicat Premium 11.2.16
创建
CREATE TABLE[ IF NOT EXISTS] <表名>( <列名> <数据类型>[(<长度>)][ <列级约束>][ AUTO_INCREMENT] [,[CONSTRAINT <约束名> ]<表级约束>] );
“if not exists”关键字：在创建前判断是否存在同名的表，不存在则创建表。
“auto_increment”关键字：自动递增。该字段必须是整数类型或小数类型的字段。一个表只能有一个自增列。
创建基本表sc，并添加列sno、cno（主键）：
基本表可以通过复制表创建。复制表有两种：
第一种是只复制指定表的结构，不复制表中的数据。
CREATE TABLE <表名> LIKE <表名>;
创建基本表student_course，只复制表sc的结构：
第二种是复制指定表的结构和数据：
CREATE TABLE <表名> <子查询>;
子查询使用的是表子查询。
创建基本表student_course，复制表sc的结构以及课程2和3的数据：
修改
修改表名
ALTER TABLE <表名> RENAME TO <表名>;
将基本表sc表名修改为student_course：
添加列
ALTER TABLE <表名> ADD COLUMN <列名> <数据类型>[(<长度>)][ <列级约束>][ AUTO_INCREMENT];
向基本表student_course中添加列grade：
修改列
ALTER TABLE <表名> CHANGE COLUMN <列名> <列名> <数据类型>[(<长度>)][ <列级约束>][ AUTO_INCREMENT];
将基本表student_course中列grade改名为score，数据类型改为tinyint：
删除列
ALTER TABLE <表名> DROP COLUMN <列名>;
删除基本表student_course中的列score：
添加表级约束
ALTER TABLE <表名> ADD[ CONSTRAINT <约束名>] <表级约束>;
为基本表student_course中的字段sno添加唯一约束：
查看表
SHOW TABLES;
查看表的详细信息
DESC <表名>;
查看基本表student_course的详细信息：
删除
DROP TABLE[ IF EXISTS] <表名>;
“if exists”关键字：在删除前判断表是否存在，存在则删除该表。
删除基本表student_score：
数据查询
一般查询
SELECT <字段> FROM <表名>;
查询结果将会是一张表，表中列的顺序为select关键字后字段的顺序。
查询所有字段可以使用“*”通配符。
查询基本表student表的所有数据：
查询时可以为字段或表起别名。
SELECT <字段>[ AS] <字段别名> FROM <表名>[ AS] <表别名>;
查询学生的学号和姓名：
可以查询表达式或者函数。
SELECT <表达式>|<函数> FROM <表名>;
查询学生的姓名、年龄、出生年份、所在系（小写）：
可以使用distinct关键字去掉查询中的重复数据。
SELECT DISTINCT <字段> FROM <表名>;
查询有选课的学生学号：
1.没有添加distinct关键字：
2.添加distinct关键字：
条件查询
条件查询是在查询时添加条件筛选记录。需要在查询语句中添加where子句。
SELECT <字段> FROM <表名> WHERE <条件表达式>;
where子句的条件表达式中使用的字段可以使用表中的所有字段，但是不能使用分组函数。
常见的查询条件可分为以下几种：
比较大小
用于比较的运算符有：>（大于）、<（小于）、=（等于）、>=（大于等于）、<=（小于等于）、<>（不等于）。
查询年龄小于20岁的学生信息：
确定范围
使用between ... and ...判断数值是否在指定范围内（含边界）；使用not between ... and ...判断数值是否不在指定范围内（不含边界）。
注意，between后的数值不能比and后的数值大。
查询年龄在17~19岁范围内的学生信息：
查询年龄不在17~19岁范围内的学生信息：
确定集合
使用in(...)判断字段的值是否在集合内；使用not in(...)判断字段的值是否不在集合内。
查询计算机科学系和数学系的学生信息：
查询既不是计算机科学系，也不是数学系的学生信息：
字符匹配
使用like关键字设置匹配串，用于判断字符串是否匹配。
匹配串中可以使用通配符：
- _：代表任意一个字符。
- %：代表任意长度的字符串。
查询姓李的学生信息：
查询名字中不包含敏字的学生信息：
如果匹配串要包含字符“_”或“%”，为了不被识别为通配符，需要在前面加上转义字符。转义字符可以使用escape关键字定义。
需要注意，作为转义字符的字符为了不被识别为转义字符也需要进行转义。
MySQL不支持该方式。
确定空值
使用is null判断是否为空值；使用is not null判断是否不为空值。
查询没有先行课的课程信息：
查询有先行课的课程信息：
多重条件
当筛选的条件有多个时：
- 多个条件同时满足：使用and关键字连接。
- 多个条件满足其中之一：使用or关键字连接。
注意，and的优先级高于or。
查询年龄大于18岁的或数学系的男生信息：
1.没有加“( )”：
可以看到查询的是年龄大于18岁的学生，或者是数学系的男生。
2.加“( )”：
这才是要查询到的结果。
排序查询
排序查询是指将记录按照指定的一个或多个字段进行排列。需要在查询语句中添加order by子句。
SELECT <字段> FROM <表名> ORDER BY <排列方式>;
其中，使用asc关键字表示升序，使用desc关键字表示降序。
排列方式可以定义多个，次序与定义时的先后顺序有关。
查询学生成绩，并按照学号升序排序，成绩降序排序：
可以看到是先将所有记录按学号升序排序，再将学号相同的记录按成绩降序排序。
若是将两个排序条件调换顺序：
可以看到是先将所有记录按成绩降序排序，再将成绩相同的记录按学号升序排序。
分组查询
分组查询是指将记录按指定字段进行分组。需要在查询语句中使用group by子句。
SELECT <字段> FROM <表名> GROUP BY <字段>;
分组查询中查询的字段必须是group by后的字段。
查询所有学生的成绩，包括总分、科目数、平均分：
对分组后的记录进行筛选，可以添加having子句。
SELECT <字段> FROM <表名> GROUP BY <字段> HAVING <条件表达式>;
having子句的条件表达式中使用的字段必须是group by后面的字段，不过可以使用分组函数。
查询选了3门及以上的课的学生的总分：
分页查询
分页查询是指只显示部分记录。需要在查询语句中使用limit子句。
SELECT <字段> FROM <表名> LIMIT <起始索引>,<记录数>;
索引从0开始计，即表中的第一条记录的索引为0。
查询基本表course中的第2~4条记录（索引1开始接下去3条记录）：
分页查询一般用于在大量记录的情况下分页显示，接收到的数据一般为页码和每页显示的记录数。若已知页码和记录数，则：起始索引 = (页码 - 1) * 记录数。
连接查询
连接查询是指查询涉及多张基本表，需要对多张表进行连接。需要在from子句中声明表的连接方式。连接方式分为3种：内连接、外连接和交叉连接。
注意，若多张表中出现同名字段，需要使用“表名.字段名”的形式指定字段。
内连接
内连接的语法有两种：
SELECT <字段> FROM <表1>,<表2> WHERE <连接条件>;
或者是：
SELECT <字段> FROM <表1> INNER JOIN <表2> ON <连接条件>;
内连接只能查询到满足连接条件的记录。
查询课程及其先行课程：
或者是：
条件限制c1.cpon=c2.con，隐含限制了cpon不能为空。所以只显示有先行课程的记录。
外连接
外连接分为3种：左外连接、右外连接和全外连接。其中，左外连接和右外连接有主表和从表之分。全外连接没有主表和从表之分，可以把所有表都看作主表。
外连接的查询结果除了满足连接条件的记录，还包括主表对应字段为空值的记录，此时该类记录对应从表字段的取值都为空值。
左外连接
SELECT <字段> FROM <表1> LEFT OUTER JOIN <表2> ON <连接条件>;
其中，表1为主表，表2为从表。
查询课程及其先行课程：
将c1作为主表，可以看到查询结果除了满足c1.cpon=c2.con的记录外，还有c1.cpno=null的记录，该类记录对应c2.cname字段的取值都为空值。
右外连接
SELECT <字段> FROM <表1> RIGHT OUTER JOIN <表2> ON <连接条件>;
与左外连接相反，表1为从表，表2为主表。
查询课程及其先行课程：
全外连接
SELECT <字段> FROM <表1> FULL OUTER JOIN <表2> ON <连接条件>;
表1和表2都为主表。
MySQL不支持全外连接。
交叉连接
交叉连接是对两个表进行全连接，相当于进行笛卡儿积运算。
如果表1有m条记录，表2有n条记录，那么交叉连接后的结果会有m*n条记录。
交叉连接没有连接条件，其语法有2种：
SELECT <字段> FROM <表1>,<表2>;
或者是：
SELECT <字段> FROM <表1> CROSS JOIN <表2>;
查询每个学生选修所有课程的情况：
或者是：
一共有28条记录。
嵌套查询
嵌套查询是指一个查询的筛选条件中嵌套了另一个查询。外层查询称为父查询，内层查询称为子查询。子查询一般嵌套在父查询的where子句或having子句中。
按查询结果分，子查询可以分为：
- 行列子查询：查询结果只有一行一列，即查询结果为一个值。
- 行子查询：查询结果为一行多列。
- 列子查询：查询结果为一列多行，即查询结果为一个集合。
- 表子查询：查询结果为多行多列。
一般情况下，比较大小和确定范围的条件使用的是行列子查询；确定集合的条件使用的是列子查询。
带有any（some）或all的子查询
any（some）和all一般用于比较大小，子查询使用的是列子查询。其中any或some表示集合中的任意一个值，all表示集合中的所有值。
查询非计算机科学系中年龄比计算机科学系任意一个学生小的学生信息：
年龄比计算机科学系任意一个学生小也就是，年龄比计算机科学系中年龄最大的学生（李勇，20岁）小即可。
查询非计算机科学系中年龄比计算机科学系所有学生小的学生信息：
年龄比计算机科学系所有学生小也就是，年龄要比计算机科学系中年龄最小的学生（刘晨，19岁）小。
带有exists的子查询
exists代表存在量词。带有exists的子查询使用的是表子查询。
带有exists的子查询不返回数据，子查询结果不为空则返回逻辑真，为空则返回逻辑假。由于只是判断是否有记录，而与记录内容无关，子查询中设定的字段是无实际意义的，一般使用通配符“*”。
查询所有选修了3号课程的学生信息：
查询选修了有先行课的课程的学生学号和课程号：
派生表查询
派生表查询是指将查询结果作为一张表使用。需要在from子句中嵌套表子查询。定义派生表必须起别名。
查询成绩不低于平均分的学生学号、课程号、成绩和平均分：
通式
基本表查询语句的通式为：
SELECT <字段> FROM <表> WHERE <条件表达式> GROUP BY <字段> HAVING <条件表达式> ORDER BY <排列方式> LIMIT <起始索引>,<记录数>;
含义：
- 获取from子句中指定的表的所有记录。
- 根据where子句筛选记录。
- 根据group by子句进行分组。
- 根据having子句对分组后的记录进行筛选。
- 根据select子句决定查询的字段。至此得到查询结果。
- 根据order by子句对查询结果进行排列。
- 根据limit子句决定返回的部分查询结果。
联合查询
联合查询是指将多个查询结果进行并操作。需要使用union关键字连接两个查询语句。
需要注意的是，参加集合操作的查询结果列数必须相同，对应项的数据类型也必须相同。
联合查询的结果会自动去重。
查询选修了课程1或者课程2的学生学号：
数据操作
插入数据
INSERT INTO <表名>[(<字段1>,<字段2>,...)] VALUES(<值1>,<值2>,...);
values后的每一个值都得与表名后的每一个字段相对应。
若表名后没有指定字段，则默认是为表中所有字段赋值，字段的顺序为定义表时字段的顺序。
MySQL支持批量插入，可以一条插入语句插入多条数据。只需要在values后放入多组数据即可，每组数据之间用“,”隔开。
添加学号为201215123的成绩，其中课程3成绩为84，课程5成绩为92：
还可以插入子查询的结果。
INSERT INTO <表名>[(<字段1>,<字段2>,...)] <子查询>;
子查询使用的是表子查询。
需要注意的是，子查询的字段必须与表的字段一一对应。
复制学号为201215121的学生成绩，并变更学号为201215125：
更新数据
UPDATE <表名> SET <字段>=<值>|<表达式>[ WHERE <条件表达式>];
通过where子句筛选指定表中的记录，并为筛选出来的记录中的指定字段重新赋值。
将学号为201215123的学生的课程5的成绩改为90：
将学号为201215125的学生的所有成绩减去10分：
删除数据
DELETE FROM <表名>[ WHERE <条件表达式>];
通过where筛选要删除的记录。
将成绩低于70的记录删除：
清空数据
TRUNCATE TABLE <表名>;
清空指定表的所有数据。
清空所有学生成绩：
delete和truncate的区别
- delete语句可以通过where子句筛选要删除的记录，truncate语句只能删除所有记录。
- delete语句会返回受影响的记录数，truncate语句不会。
- 在同个事务中执行delete语句可以回滚，但是执行truncate语句不能回滚。
- 对于标识列来说，delete语句清空基本表后再插入数据，标识列取值从断点开始；而truncate语句清空基本表后再插入数据，标识列取值从1开始。
创建基本表test，包含字段id（主键，自增）、name（非空）：
插入数据，“小明”、“小红”、“小亮”：
-----使用delete语句-----
清空基本表test：
可以看到返回受影响的行数为3。
插入数据“小光”：
可以看到id为4。
-----使用truncate语句-----
清空基本表test：
可以看到没有返回受影响的行数。
插入数据“小光”：
可以看到id为1。
