用MySQL数据库创建表格
- 创建数据库删除数据库
1
CREATE DATABASE student
创建完数据库需要刷新才能出现数据库1
drop database student
- 创建表格
需要先确定列的类型
1 | USE student; |
数值类型
TINYINT 微整型,占1个字节 范围-12812732767
SMALLINT 小整型,占2个字节 范围-32768
INT 整型,占4个字节 范围 -2147483648~2147483647
BIGINT 大整型,占8个字节
FLOAT 单精度浮点型,占4个字节,范围3.4e38,范围比INT大的多,可能产生计算误差。
DOUBLE 双精度浮点型,占8个字节,范围比BIGINT大的多
DECIMAL(M,D) 定点小数,不会产生计算误差,M代表总的有效位数,D代表小数点后的有效位数
BOOL 布尔型,只有两个结果TRUE/1、FALSE/0,TRUE和FALSE不能添加引号;真正存储数据的时候,会使用TINYINT。日期时间类型
DATE 日期型 ‘2018-12-31’
TIME 时间型 ‘14:22:30’
DATETIME 日期时间型 ‘2018-12-31 14:22:30’字符串类型——必须添加引号
VARCHAR(M) 变长字符串,不会产生空间浪费,操作速度相对较慢,M最大值是65535
CHAR(M) 定长字符串,可能产生空间浪费,操作速度较快,M最大值是255;用于存储手机号码,身份证号等固定长度的字符。
TEXT(M) 大型变长字符串,最多存2G填值
增加
1
INSERT INTO student Values('张三',2,...),
修改
1
UPDATE student SET sa=18 where sno='张三'
删除
1
DELETE FROM student where sno='张三'
查表
查看数据库所有的表
1 | SHOW TABLES; |
修改表
1 | 添加字段 |
查询列
1 | 查询所有列 |
增加
1 | 全列插入 |
select一般形式
selcet 列名,*,运算,聚合函数
From 表|视图|派生表
where 条件
- 比较运算符<> != > < >= <= =
- 范围 between and | not between and
- 集合in not in
- 字符匹配(模糊查询)like ‘%_’%匹配次数不限 _只匹配一次
- 多重条件(逻辑运算)and or not
group by[HAVING<条件表达式>]
order by[ASC|DESC]
条件查询
1 | 查询编号大于三的学生 |
逻辑运算符
1 | and or not |
范围查询
1 | in 和 between and |
连接查询
连接查询:同时涉及两个以上的表的查询
连接条件或连接谓词:用来连接两个表的条件
注:连接条件中的各连接字段类型必须是可比的,但名字不必相同
等值与非等值连接查询
等值连接:连接运算符为=
查询每个学生及其选修课程情况
1 | SELECT stu.*, SC.* |
查询结果
嵌套循环法
首先在表1中找到第一个元组,然后从头开始扫描表2,逐一查找满足连接件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组。
表2全部查找完后,再找表1中第二个元组,然后再从头开始扫描表2,逐一查找满足连接条件的元组,找到后就将表1中的第二个元组与该元组拼接起来,形成结果表中一个元组。
排序合并法
常用于=连接
首先按连接属性对表1和表2排序
对表1的第一个元组,从头开始扫描表2,顺序查找满足连接条件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时,对表2的查询不再继续
索引连接
对表2按连接字段建立索引
对表1中的每个元组,依次根据其连接字段值查询表2的索引,从中找到满足条件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组
自身连接
自身连接:一个表与其自己进行连接
注:1. 需要给表起别名用来区分
2.由于所有属性名都是同名属性,因此必须用别名前缀
3.要为单个表取两个别名,一个是first,另一个是second
1 | SELECT FIRST.Cno, SECOND.Cpno |
外连接
- 普通连接操作只输出满足连接条件的元组
- 外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出
- 左外连接
列出左边关系中所有的元组 - 右外连接
列出右边关系中所有的元组
多表连接
多表连接:两个以上的表进行连接
查询每个学生的学号、姓名、选修的课程名及成绩
1 | SELECT Student.Sno,Sname,Cname,Grade |
嵌套查询
一个SELECT-FROM-WHERE语句称为一个查询块
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
1 | SELECT Sname /*外层查询/父查询*/ |
上层的查询块称为外层查询或父查询
下层查询块称为内层查询或子查询
注:
- SQL语言允许多层嵌套查询
即一个子查询中还可以嵌套其他子查询 - 子查询的限制
不能使用ORDER BY
嵌套查询求解方法
不相关子查询:
- 子查询的查询条件不依赖于父查询
- 由里向外 逐层处理。即每个子查询在上一级查询处理之前求解,子查询的结果用于建立其父查询的查找条件
相关子查询:子查询的查询条件依赖于父查询 - 首先取外层查询中表的第一个元组,根据它与内层查询相关的属性值处理内层查询,若where返回值为真,则取此元组放入结果表中
- 然后再取外层表的下一个元组
- 重复这一过程,直至外层表全部检查完为止
带有IN谓词的子查询
查询与“刘晨”在同一个系学习的学生。(此查询要求可以分步来完成)
1 | <!-- 确定“刘晨”所在系名 --> |
查找所有在CS系学习的学生。
1 | SELECT Sno, Sname, Sdept |
1 | SELECT Sno, Sname, Sdept |
带有比较运算符的子查询
当能确切知道内层查询返回单值时,可用比较运算符(<,>,=,>=,<=,!=,<>)
找出每个学生超过他选修课程平均成绩的课程号。
1 | SELECT Sno, C5no |
分为三步
- 从外层查询中取出SC的一个元组x,将元组x的Sno值(201215121)传送给内层查询。
1 | SELECT AVG(Grade) |
- 执行内层查询,得到值88(近似值),用该值代替内层查询,得到外层查询:
1
2
3SELECT Sno,Cno
FROM SC x
WHERE Grade >=88;
带有ANY(SOME)或ALL谓词的子查询
- ALL> 大于子查询结果中的所有值
- < ANY 小于子查询结果中的某个值
- < ALL 小于子查询结果中的所有值
- => ANY 大于等于子查询结果中的某个值
- => ALL 大于等于子查询结果中的所有值
- !=(或<>)ANY 不等于子查询结果中的某个值
- !=(或<>)ALL 不等于子查询结果中的任何一个值
查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。1
2
3
4
5
6SELECT Sname,Sage
FROM Student
WHERE Sage < ANY (SELECT Sage
FROM Student
WHERE Sdept= ' CS ')
AND Sdept <> ‘CS ' /*父查询块中的条件 */
用ALL谓词聚集函数1
2
3
4
5
6
7
8SELECT Sname,Sage
FROM Student
WHERE Sage < ALL
(SELECT Sage
FROM Student
WHERE Sdept= ' CS ')
AND Sdept <> ' CS ’;1
2
3
4
5
6
7
8SELECT Sname,Sage
FROM Student
WHERE Sage <
(SELECT MIN(Sage)
FROM Student
WHERE Sdept= ' CS ')
AND Sdept <>' CS ';
带有EXISTS的子查询
- 带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”。
- 若内层查询结果非空,则外层的WHERE子句返回真值
- 若内层查询结果为空,则外层的WHERE子句返回假值
- 由EXISTS引出的子查询,其目标列表达式通常都用 * ,因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义。
查询所有选修了1号课程的学生姓名。 - 在Student中依次取每个元组的Sno值,用此值去检查SC表
- 若SC中存在这样的元组,其Sno值等于此Student.Sno值,并且其Cno= ‘1’,则取此Student.Sname送入结果表
1
2
3
4
5
6
7SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno= ' 1 ');
UNION和UNION ALL
- UNION:将多个查询结果合并起来时,系统自动去掉重复元组
- UNION ALL:将多个查询结果合并起来时,保留重复元组
查询选修了课程1或者选修了课程2的学生。1
2
3
4
5
6
7
8SELECT Sno
FROM SC
WHERE Cno=' 1 '
UNION
SELECT Sno
FROM SC
WHERE Cno= ' 2 ';