2021年07月18日 随堂笔记1 SQL

发表于 分类于 ， ， Waline： 本文字数： 5.6k 阅读时长 ≈ 5 分钟

回顾

编程学习时，软件密码不要设置的太过复杂，真正应用的时候再设置复杂的密码，

软件最后用免安装版本

建表

使用图形化工具==MySQLWorkbench.exe==进行建表，然后记录建表过程。

SQL语句

要了解增删改查，特别是查询

SQL语言的基本概念

基本表

基本表（BASE TABLE）：是独立存在的表，不是由其它的表导出的表。一个关系对应一个基本表，一个或多个基本表对应一个存储文件。

视图

视图（VIEW）：是一个==虚拟==的表，是从一个或几个基本表导出的表。它本身不独立存在于数据库中，数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。当基本表中的数据发生变化时，从视图中查询出来的数据也随之改变。

外模式 模式 内模式

SQL语言支持数据库的三级模式结构，如图3.1所示。其中

  • 外模式对应于视图和部分基本表，
  • 模式对应于基本表，
  • 内模式对应于存储文件。

image-20210718091401207

字段数据类型

数据库迁移很重要

不要使用存储过程，触发器，用的数据类型为int，varchar，其他数据库特有的数据类型尽量不要使用。

以主键形式保存的字段的数据类型使用char,不要使用varchar，这样索引主键比较方便。

text数据类型也是常用的，保存新闻，或者富文本编辑区保存的内容。

建立数据库

1
create database <数据库名>;

创建表示例

1
2
3
4
5
6
7
USE STUDENT
CREATE TABLE S
(SNO CHAR(8) ,
SN VARCHAR(20),
AGE INT,
SEX CHAR(2) DEFAULT '男' ,
DEPT VARCHAR(20));

一对一不要建表，把任意一段的主键，放到另一端作为外键

一对多不建表，把一的一端的主键，放到多的一端作为外键

多对多要建表，把两者的主键提取出来，放到关系表中作为外键，共同组成关系表的主键

​

索引后续会说

SQL数据查询

SELECT命令的格式与基本使用

数据查询是数据库中最常见的操作。

SQL语言提供SELECT语句，通过查询操作可得到所需的信息。

SELECT语句的一般格式为：

1
2
3
4
5
SELECT〈列名〉[{,〈列名〉}]
FROM〈表名或视图名〉[{,〈表名或视图名〉}]
[WHERE〈检索条件〉]
[GROUP BY <列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2>[ASC|DESC]];

SELECT语句的格式：

1
2
3
4
5
6
7
8
9
SELECT	[ALL|DISTINCT][TOP N [PERCENT][WITH TIES]]
列名1 [AS 别名1]
[, 列名2 [ AS 别名2]…]
[INTO 新表名]
FROM 表名 1[[AS] 表1别名]
[INNER|RIGHT|FULL|OUTER][OUTER]JOIN
    表名2 [[AS] 表2别名]
ON 条件
;

查询的结果是仍是一个表。

SELECT语句的执行过程是:

  • 根据WHERE子句的检索条件，从FROM子句指定的基本表或视图中选取满足条件的元组，再按照SELECT子句中指定的列，投影得到结果表。
  • 如果有GROUP子句，则将查询结果按照<列名1>相同的值进行分组。
  • 如果GROUP子句后有HAVING短语，则只输出满足HAVING条件的元组。
  • 如果有ORDER子句，查询结果还要按照<列名2>的值进行排序。

关系代数

例3.21 查询全体学生的学号、姓名和年龄。

1
SELECT SNO, SN, AGE FROM S;

例3.22 查询学生的全部信息。

1
SELECT * FROM S;

用‘ * ’表示S表的全部列名，而不必逐一列出。

例3.23 查询选修了课程的学生号。

SELECT ==DISTINCT== SNO FROM SC;

查询结果中的重复行被去掉

上述查询均为不使用WHERE子句的无条件查询，也称作投影查询。

另外，利用投影查询可控制列名的顺序，并可通过指定别名改变查询结果的列标题的名字。

例3.24 查询全体学生的姓名、学号和年龄。

1
SELECT SNAME NAME, SNO, AGE FROM S;

其中，NAME为SNAME的别名

条件查询

比较大小

多重条件查询

确定范围

例3.28 查询工资在1000至1500之间的教师的教师号、姓名及职称。
1
2
3
4
SELECT TNO,TN,PROF
FROM T
WHERE SAL BETWEEN 1000 AND 1500
;

BETWEEN … AND不是所有的DBMS都支持，为了通用性，改成如下标准形式：

等价于

1
2
3
SELECT TNO,TN,PROF
FROM T
WHERE SAL>=1000 AND SAL<=1500;
例3.29 查询工资不在1000至1500之间的教师的教师号、姓名及职称。

确定集合

利用“IN”操作可以查询属性值属于指定集合的元组。

利用“NOT IN”可以查询指定集合外的元组。

部分匹配查询

上例均属于完全匹配查询，当不知道完全精确的値时，用户还可以使用LIKE或NOT LIKE进行部分匹配查询（也称模糊查询）。

LIKE定义的一般格式为：
1
<属性名> LIKE <字符串常量>

属性名必须为字符型，字符串常量的字符可以包含如下两个特殊符号：

%：表示任意知长度的字符串；

_：表示任意单个字符。 utf-8编码则一个下划线表示汉字，

例3.32 查询所有姓张的教师的教师号和姓名。
1
2
3
SELECT TNO, TN 
FROM T
WHERE TN LIKE '张%';

SQL中的字符串单引号和双引号部分，通常使用单引号

例3.33 查询姓名中第二个汉字是“力”的教师号和姓名。
1
2
3
SELECT TNO, TN 
FROM T
WHERE TN LIKE '__力%';

注：一个汉字占两个字符。

空值查询

常用库函数及统计汇总查询

长度固定的字符串是可以使用MAX和MIN来计算

SUM是求和，COUNT是计数的

不要用任何DBMS的语言去编程数据库自定义函数，然后调用，这样的话无法迁移到其他DBMS

例3.37 求计算机系学生的总数

1
2
SELECT COUNT(SNO) FROM S
WHERE DEPT='计算机';

或者统计有多少条记录：

1
select count(*) from s where WHERE DEPT='计算机';

分组查询

s(sno,sname,sage,dept,sex);

1
2
3
select sex,count(*) as total form s 
group by sex
having count(*)>0;
1
2
3
4
select sex,count(*) as total form s 
where dept='计算机'
group by sex
having count(*)>0;

having之后跟在group by之后

1
2
3
4
select sex,count(*) as total form s 
where dept='计算机'
group by dept,sex
having count(*)>0;

分组之后只会看总体信息，不能看具体信息

例题

1
SC(sno,cno,score);

查询同时选修了c1和c2课程的学生学号？

1
2
3
4
select sno from SC
where cno='c1' or cno='c2'
group by sno
having count(*)=2;

如果都选择这两个课程，那么统计的结果为2，如果只选择了一门，那么统计的结果为1.

分组查询

GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。

……

WHERE－GROUP BY－ HAVING

当在一个SQL查询中同时使用WHERE子句，GROUP BY 子句和HAVING子句时，其顺序是WHERE－GROUP BY－ HAVING。

WHERE与HAVING子句的根本区别

WHERE与HAVING子句的根本区别在于作用对象不同。
WHERE子句作用于基本表或视图，从中选择满足条件的元组；
HAVING子句作用于组，选择满足条件的组，必须用于GROUP BY子句之后，但GROUP BY子句可没有HAVING子句。

查询的排序
当需要对查询结果排序时，应该使用ORDER BY子句
ORDER BY子句必须出现在其他子句之后
排序方式可以指定，DESC为降序，ASC为升序，缺省时为升序

例3.44 查询选修C1 的学生学号和成绩，并按成绩降序排列。
SELECT SNO, SCORE
FROM SC
WHERE CNO=’C1’
ORDER BY SCORE DESC

例3.45 查询选修C2、C3、C4或C5课程的学号、课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排列。

1
2
3
4
SELECT SNO,CNO, SCORE 
FROM SC
WHERE CNO IN ('C2' ,'C3', 'C4','C5')
ORDER BY SNO,SCORE DESC  ;

例3.46 求选课在三门以上且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序列出。

1
2
3
4
5
SELECT SNO,SUM(SCORE) AS TotalScore  FROM SC
WHERE SCORE>=60
GROUP BY SNO
HAVING COUNT(*)>=3
ORDER BY SUM(SCORE) DESC;

执行顺序（重要）

此语句为分组排序，执行过程如下：

1.（FROM）取出整个SC

2.（WHERE）筛选SCORE>=60的元组

3.（GROUP BY）将选出的元组按SNO分组

4.（HAVING）筛选选课三门以上的分组

5.（SELECT）以剩下的组中提取学号和总成绩

6.（ORDER BY）将选取结果排序

数据表连接及连接查询

数据表之间的联系是通过表的字段值来体现的，这种字段称为连接字段。

连接操作的目的就是通过加在连接字段的条件将多个表连接起来，以便从多个表中查询数据。

前面的查询都是针对一个表进行的，当查询同时涉及两个以上的表时，称为连接查询。

表的连接方法有两种：

  • 方法1：表之间满足一定的条件的行进行连接，此时FROM子句中指明进行连接的表名，WHERE子句指明连接的列名及其连接条件。
  • 方法2：利用关键字JOIN进行连接。

查询选修了3门课以上的学生学号

1
2
select sno,sname from S where sno in
(select son form SC grop by sno having count(*)>=3)

能有嵌套查询尽量使用嵌套查询

笛卡尔积

两张表做联查，先做笛卡尔积。

(1,2)X(3,4)=( (1,3),(1,4),(2,3),(2,4) )

内连接

1
2
S(sno,sn)
SC(sno,cno,score)
1
2
3
select s.sno,s.sn,SC.cno,SC.score
from S,SC
where S.sno=SC.sno;

上面的写法成为内连接，隐式写法

对应的显示写法为：

1
2
3
select s.sno,s.sn,SC.cno,SC.score
from S inner join SC
on S.sno=SC.sno;

多表内连接显示写法：

1
2
3
4
5
6
select s.sno,s.sn,SC.cno,SC.score
from
(S inner join SC
on S.sno=SC.sno)
inner join C ......
;

连接很费时，能用嵌套查询就用嵌套查询来做

外连接

外连接（outer join）分为如下其中

  • left join
  • right join
  • full join
  • cross join—-就是笛卡尔积

左外连接

保证左边这张表的所有记录都要在结果里面，如果右边没有对应的记录，则使用null填充

1
2
3
select s.sno,s.sn,SC.cno,SC.score
from S left join SC
on S.sno=SC.sno;

右连接

保证右边表中的素所有记录都在结果里，左边没有对应的记录则使用null填充

全连接

左边，右边的记录都保留在结果集里面

INNER JOIN ：显示符合条件的记录，此为默认值；
LEFT （OUTER） JOIN：显示符合条件的数据行以及左边表中不符合条件的数据行，此时右边数据行会以NULL来显示，此称为左连接；

RIGHT （OUTER） JOIN：显示符合条件的数据行以及右边表中不符合条件的数据行，此时左边数据行会以NULL来显示，此称为右连接；

FULL （OUTER） JOIN：显示符合条件的数据行以及左边表和右边表中不符合条件的数据行，此时缺乏数据的数据行会以NULL来显示；

CROSS JOIN：会将一个表的每一笔数据和另一表的每笔数据匹配成新的数据行。

当将JOIN 关键词放于FROM子句中时，应有关键词ON与之相对应，以表明连接的条件。

如果非要使用连接的话，可以先使用子查询筛选出要查询的信息作为中间表，尽量缩小数据量之后，在做连接：

image-20210718110107652

自身连接

1
2
3
select a.sno from SC as a,SC
where a.sno=SC.sno
and a.cno='C1' and sc.cno='C2';

ANY不是所有的数据库都支持，不建议使用。

重要

普通子查询和相关子查询

image-20210718111248416

尽量不要使用相关子查询

子查询里面用到父表里面的条件，就是相关子查询

数据库更新

插入数据

insert into

insert的时候不要简写，不要省略字段名，如果数据库迁移的时候，字段的顺序可能会改变。

修改记录

删除记录

下午讲异常机制

先做SQL语句的作业