数据库笔记
数据库的安装和使用
安装教程
使用教程
数据库基本语法
数据库层次结构
启动
1 |
|
数据库的创建
创建基本的一个学生-课程数据库
1 |
|
表操作
基本数据类型
数值类型
TINYINT | 1 Bytes | (-128,127) | (0,255) | 小整数值 |
---|---|---|---|---|
SMALLINT | 2 Bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 Bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 Bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 Bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 Bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度 浮点数值 |
DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个”零”值,当指定不合法的MySQL不能表示的值时使用”零”值。
TIMESTAMP类型有专有的自动更新特性,将在后面描述。
类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
注意:char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。
创建一个表和删除一个表
创建
1 |
|
删除
1 |
|
修改表名
1 |
|
修改列名
1 |
|
表——增删改查
增
可以用两种方式编写INSERT INTO语句。
第一种方法指定列名和要插入的值:
1 |
|
如果要为表的所有列添加值,则无需在SQL查询中指定列名。但是,请确保值的顺序与表中的列的顺序相同。INSERT INTO语法如下:
1 |
|
代码举例(一定注意:字符型数据需要加引号):
1 |
|
最后得到如下几个表:
Sno | Sname | Ssex | Sage | Sdept |
---|---|---|---|---|
201215121 | 李勇 | 男 | 20 | CS |
201215122 | 刘晨 | 女 | 19 | CS |
201215123 | 王敏 | 女 | 18 | MA |
201215125 | 张立 | 男 | 19 | IS |
Cno | Cname | Cpno | Ccredit |
---|---|---|---|
1 | 数据库 | 5 | 4 |
2 | 数学 | NULL | 2 |
3 | 信息系统 | 1 | 4 |
4 | 操作系统 | 6 | 3 |
5 | 数据结构 | 7 | 4 |
Sno | Cno | Grade |
---|---|---|
201215121 | 1 | 92 |
201215121 | 2 | 85 |
201215121 | 3 | 88 |
201215122 | 2 | 90 |
201215122 | 3 | 80 |
还有一种插入方式,需要学习select语句,直接把语句放到下面
对于每一个系,求学生的平均年龄,并把结果放到数据库里
1 |
|
删
1 |
|
这里的where子句可以配合一些列其他句子使用,如exists,in等等
改
让我们先想想有什么需要修改?
对于列来说:
- 增加新的一列
增加入学时间
1 |
|
- 修改原有列的数据类型(使用MODIFY或者CHANGE)或者增加约束条件
1 |
|
- 删除列和删除约束条件)
1 |
|
对于行来说:
- 修改一行中已有的数据(使用SET语句)
1 |
|
这里的where子句可以配合一些列其他句子使用,如exists,in等等
查
使用where子句和列名可以实现查找(查找是一个大块,内容很复杂,后面再介绍)
1 |
|
学号 | 性别 |
---|---|
201215122 | 女 |
201215123 | 女 |
201215125 | 男 |
如果这样查输出奇怪的东西(sql注入可能用到)
1 |
|
1 | 2 | 3 |
---|---|---|
1 | 2 | 3 |
1 | 2 | 3 |
1 | 2 | 3 |
索引的创建
1 |
|
数据查询:cry:
单表查询
例1:查询全体学生的姓名、出生年份和所在院系,要求用小写字母表示系名
1 |
|
可以看到这里的可以制定别名,同时想要查找的列可以用一个表达式表示 2022 - Sage
例2:查询选修了课程的学生学号(去掉重复项)
1 |
|
使用distinct子句来保证结果唯一
例3:查询计算机系的全体学生
1 |
|
这里使用了where子句,这是一个很强大的功能:
查询条件 | 谓词 |
---|---|
比较 | = , > , < , >= , <= , != , <> , !> , !< , ; NOT + 上述比较符 |
确定范围 | BETWEEN AND , NOT BETWEEN AND |
确定集合 | IN , NOT ,IN |
字符匹配 | LIKE , NOT LIKE |
空值 | IS NULL , IS NOT NULL |
多重条件(逻辑运算) | AND , OR , NOT |
例4:查询计算机系(CS)和数学(MA)系中年龄在19~20之间的学生姓名
1 |
|
这里between and也可以用=<20 AND >=19代替
例5:查询名字中课程名中含有下划线的课程
1 |
|
这里使用了LIKE进行字符匹配:
- %(百分号)代表任意长度(可以为0)的字符串
- _(下划线)代表任意单个字符
例6:查询全体学生情况,查询结果按照系号升序,同一系中的学生按照年龄降序
1 |
|
用DESC来进行降序
例7:查询选修了课程的学生人数
1 |
|
返回一个表只有一个值:
COUNT(DISTINCT Sno) |
---|
2 |
注意这里必须使用distinct语句,默认是all即全部行都要统计
例8:查询各个课程号及相应的选课人数
1 |
|
GROUP把课程号相同的学生分到一组,然后COUNT函数统计每一组的Sno结果,输出如下结果:
Cno | COUNT(Sno) |
---|---|
1 | 1 |
2 | 2 |
3 | 2 |
如果GROUP不配合COUNT,AVG(这些函数对于一个表只返回一个值)使用,那么返回这样的结果:
1 |
|
输出的结果只有不同Cno的第一个行
Sno | Cno | Grade |
---|---|---|
201215121 | 1 | 92 |
201215121 | 2 | 85 |
201215121 | 3 | 88 |
例9:查询选修了三门及以上课程的学生学号
1 |
|
注意这里不能用where子句,where子句作用于基本表或者视图;
而having短语作用于组,从中选择满足条件的组;
连接查询
等值连接与非等值连接
例1:查询每个学生及其选修课程的情况
1 |
|
这里是一个等值连接,如果建立了索引这里的速度会更快
例2:查询选修了2号课程且成绩在90分及以上的所有学生的学号和姓名
1 |
|
为什么要用student.sno而不是直接用sno,因为SELECT student. , sc. 返回的值是两个表的笛卡尔积,包括两个sno分别是student.sno和sc.sno,这里只需要提取student.sno
自身连接
例1:查询每一门课的间接先修课(先修课的先修课)
1 |
|
外连接
例1:查询每个学生及其选修课程的情况
类比2.5.2.1中,会发现返回的结果里面没有学号为201215123的同学,因为他们被开除了没有选课,但是我们又希望显示他们的选课结果,用NULL表示,这里就需要用到外连接,而且是左外连接
1 |
|
这样就能显示啦!:happy:
- 左外连接列出左边关系中的所有元组
- 右外连接列出右边关系中的所有元组
多表连接
例1:查询每个学生的学号,姓名,选修的课程名及名称
1 |
|
这样还是显示不了没选课的人,需要用到外连接才能实现,我们暂时先不讨论
嵌套查询
sql语句中,一个SELECT-FROM-WHERE语句为一个查询块
。将一个查询块嵌入到另一个查询块的where子句中或者having短语中的查询成为嵌套
带有IN谓词子查询
例1:查询与“刘晨”在同一个系学习的学生的名字
1 |
|
例2:查询选修了课程名为“信息系统”的学生学号和姓名
1 |
|
带有比较运算符的子查询
父子查询之间用比较运算符连接
例1:找出每个学生超过他自己选修课程平均成绩的课程号
1 |
|
可能有点难理解,我们把语句的底层实现过程拆分成下面三个步骤:
1.从外层查询中取出sc的一个元组x,将元组x的Sno值(201215121)传给内层查询
select avg(grade)
from sc y
where y.sno = ‘201215121’;
2.执行内层查询,得到88(近似值),用该值代替内层查询,得到外层查询
3.遍历所有元组,把重复的值去掉
需要注意,这里的子查询为相关子查询
,和之前的例子不一样,内层查询和外层是相关的,所以在实现上内层查询不是一次返回多个值,而是返回一个值(88),这一个值只对应sc的第一行,第二行依旧按照如此进行查询。
带有ANY(SOME)或者ALL谓词的子查询
望文生义,如果内层查询返回一组数,那么使用any或者all来修饰,同时配合运算比较符号来查询,例如
>any 表示大于子查询结果中的某个值
例1:查询非计算机科学系中比计算机系任意一个学生年龄小的学生姓名和年龄
1 |
|
例2:查询非计算机科学系中比计算机系所有一个学生年龄小的学生姓名和年龄
1 |
|
带有EXISTS谓词的子查询
exists代表存在量词。带有exists谓词的子查询不返回任何数据,只产生逻辑值true和false
例1:查询选修了1号课程的学生姓名
1 |
|
这也是一个相关子查询,第一次只传一个元组进入内层(这时学号为201215121),然后在sc表中找出学号为201215121的人选择的1号课程,返回如果有值,那么返回true,如果是空值,返回false。直至外层元组全部检索完。
EXISTS执行顺序](https://zhuanlan.zhihu.com/p/351922940))
1 |
|
1、首先执行一次外部查询,并缓存结果集,如 SELECT * FROM A
2、遍历外部查询结果集的每一行记录R,代入子查询中作为条件进行查询,如 SELECT 1 FROM B WHERE B.id = A.id
3、如果子查询有返回结果,则EXISTS子句返回TRUE,这一行R可作为外部查询的结果行,否则不能作为结果
例2:查询选修了全部课程的学生姓名>
注意mysql里面没有全程量词,但是可以用存在量词和非来等价替换
1 |
|
例3:查询至少选修了学生201215122选修的全部课程的学生号码
本查询可以用逻辑蕴涵来表达:对于学号为x的学生,对于所有的课程y,只要201215122学生选修了课程y,那么就有学生x选修了课程y
形式化如下:
用p表示谓词:学生201215122选修了课程y
用q表示谓词:学生x选修了课程y
则上述查询为 :
SQL语言中没有蕴涵的逻辑运算,但是可以通过$\exists$和$\neg$来等价替换
加上前置的条件完成的转换为:
表达的意思为:不存在这样的课程y,学生201215122选修了y,而学生x没有选
1 |
|
仿造例2的另一种解法,
1 |
|
集合查询
select语句的查询结果是元组的集合,所以多个select语句的结果可以进行结合操作。集合操作主要包括并操作union、交操作intersect和差操作except。
注意:参加集合操作的各查询结果的列数必须相同;对应的数据类型也必须相同例1:查询计算机科学系的学生及年龄不大于19岁的学生
1 |
|
实际上使用union将多个查询结果合并起来,系统会自动去掉重复的元组。如果要保留,使用union all操作符。
例2:查询选修了课程1或者选修了课程2的学生的姓名
1 |
|
实际上这个union等价于下面的语句
1 |
|
例3:查询计算机科学系的学生与年龄不大于19岁的学生的交集
有些数据库语言是没有intersect交集的操作,可以使用in语句来等价替换,如下面的例子
1 |
|
解释一下这个语句:一个元组即满足属性sdept = ‘cs’又满足in中的条件,年龄不大于19
如果不能理解,还可以用and语句来实现
1 |
|
例4:查询计算机科学系的学生与年龄不大于19岁的学生的差集
1 |
|
基于派生词的查询
子查询不仅可以出现在where子句中,还可以出现在from子句中,这是子查询生成的临时派生表成为主查询的查询对象。
1 |
|
这种派生的方法实际上是把两个表连接了起来,如果原有的两个表的行列数分别为(m1,n1)和(m2,n2),则得到的规模为(m1*m2,n1+n2)
select语句的一般格式
select语句是mysql的核心语句,一般有如下格式:
1 |
|
目标列表达式的可选格式
- *
- <表名>.*
- count([distinct | all] *)
- [<表名>.]<属性列表达式>
其中属性列表达式可以由属性列、作用于属性列的聚集函数和常量的任意算数运算(+,-,*,/)组成的运算公式,见例题
聚集函数的一般形式
where子句的条件表达式的可选格式
(1)其中 $\theta$ 为比较符号
(2)
(3)
(4)
(5)
(6)
(7)
视图
定义视图
建立视图
使用create view 命令建立视图,一般的格式为:
1 |
|
with check option表示对视图进行update、insert和delete操作时要保证更新、插入或删除的行满足视图定义中的谓词条件。
例1:建立信息系学生的视图,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生
1 |
|
执行create view语句的结果只是把视图的定义存入数据字典,并不执行其中的select语句
。只是在对视图查询的时候,才按视图的定义从基本表中将数据查出。所以,如果基本表的结构被破坏,视图可能不会正常工作。
例2:建立信息系选修了一号课程的学生的视图(包括学生、姓名、成绩)
1 |
|
例3:将学生的学号以及平均成绩定义为一个视图
1 |
|
删除视图
例1:删除视图is_s1
1 |
|
如果在is_s1视图的基础上建立了其他视图,那么这里是不能删除成功的,在后方插入cascade指令可以把和is_s1级联的视图同时删除
1 |
|
查询视图
例1:在s_g视图中查询平均成绩在90分以上的学生学号和平均成绩
1 |
|
但是会报错,invalid use of group function,因为s_g底层是group聚集函数组成的,不能使用where语句