RDMS 第2篇 SQL

RDMS 第2篇 SQL

1. 分类

数据定义：create、drop、alter

数据查询：select

数据操纵：insert、update、delete

---

数据控制：grant、revoke

事务控制：commit、rollback

2. 基本概念

外模式：视图 、部分基本表

模式：基本表

内模式：存储文件

3. 数据类型

数字：tinyint smallint mediumint integer bigint、float double、decimal

字符串：char varchar text blob enum set

时间：date time datetime timestamp year

类型	说明
SMALLINT	半字长二进制整数
INTEGER INT	全字长二进制整数
DECIMAL(p[, q])或DEC(p[,q])	压缩十进制数，共p位，小数点后有q位(q为0可省略)
FLOAT	双字节浮点数
CHARTER(n)或CHAR(n)	长度为n，定长字符串
VARCHAR	最大长度为n，变长字符擦混
GRAPHIC(n)	长度为n，定长图形字符串
VARGRAPHIC(n)	最大长度为n，变长图形字符串
DATE	日期型，格式为：YYYY-MM-DD
TIME	时间型，格式为：HH.MM.SS
TIMESTAMP	日期加时间

4. SQL

4.1 数据定义语言

操作对象：库、表、视图、索引

操作对象	创建	删除	修改
库	create database	drop database	alter database
表	create table	drop table	alter table
视图	create view	drop view
索引	create index	drop index

表

-- 创建表
create table <表名> (
    <列名> <数据类型> [列级完整性约束条件], 
    ...,
    [表级完整性约束条件]
);

-- 修改表
alter table <表名>
	[add <新列名> <数据类型> [完整性约束]]
	[drop <完整性约束名>]
	[modify <列名> <数据类型>];
	
-- 删除表
drop table <表名>;

索引

单列索引和多列索引
唯一索引
聚簇索引和非聚簇索引

create index <索引名> on <表名> (<列名>)
create unique index <索引名> on <表名> (<列名>)
create cluster index <索引名> on <表名> (<列名>)

drop index <索引名>;

4.2 数据查询

表student，teacher

单表查询

1. 返回结果：
   select * from student
select name,age from student
select name,(2020-age) as '出生年' from student
select distinct name from student
2. 条件查询：大小、范围、集合、匹配、空值
   select * from student where age<20
select * from student where age between 20 and 30
select * from student where age in(20, 22)
select * from student where name like '王%'
select * from student where grade is not null
select * from student where subject = 'CS' and name like '王__'
3. 排序显示
   select * from student order by age ASC
4. 分组统计
   select subject, count(*), avg(age) from student group by subject
select age, count(*) from student group by age having age > 20
5. 分页查询
   select * from student limit 10 offset 1

说明

SQL格式

select [all | distinct] <目标列表达式, ..>
from <表名或视图名, ..>
[where <条件表达式>]
[group by <列名1> [having <条件表达式>]]
[order by <列名2> [ASC|DESC]]

字符匹配

• %和_（中文两个_）
• escape ‘<换码字符>’（查询的串还有%或_，需转义，如select * from stu where name like 'DB\_Design' escape '\'）

查询条件

查询条件	谓词
比较	=, >, <, >=, <=, !=, <> !>, !<, not+
确定范围	between and, not between and
确定集合	in, not in
字符匹配	like, not like
空值	is null, is not null
多重条件（逻辑运算）	and, or, not

多表查询

select students.name, class_id from students, classes where students.class_id = classes.id

连接查询

1. 内连接：两张表同时存在
   • 等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值
   • 不等连接：在连接条件，使用除等于运算符以外的其它比较运算符比较被连接的列的列值
   • 自然连接：两个表是同一个表
     SELECT s.*, c.* FROM students s INNER JOIN classes c ON s.class_id = c.id;
2. 外连接
   • 左外连接：左表都存在
   • 右外连接：右表都存在
   • 全外连接：两张表的x
     SELECT s.*, c.* FROM students s RIGHT OUTER JOIN classes c ON s.class_id = c.id;

嵌套查询

select name from student where tid in(select tid from teacher where age > 45)

查询学生信息，要求：老师年龄大于45

备注：常见谓词为in、>、<等比较，或者组合any all使用、或者exists

集合查询

并：union

交：intersect

差：except

派生表

select <列> from (select * from student where age>20)

from后面的语句为派生表

4.3 数据更新

1. 插入
2. 删除
3. 更新

4.4 函数

聚合函数

函数	说明
count([all | distinct] *)	统计元组个数
count([all | distinct] <列名>)	统计列的值个数
sum	计算列总和，列需为数值型
avg	计算列平均值，列需为数值型
max	列最大值
min	列最小值
first	返回第一个记录的值
last	返回最后一个记录的值

字符串

字符串转换

• ASCII()
• CHAR()
• LOWER()和UPPER()
• STR()

去空格

• LTRIM()和RTRIM()

子串：

• left
• right
• substring

比较
操作

日期时间

• day
• month
• year
• dateadd
• datediff
• datename
• datepart
• getdate

其他

比如数学函数、系统相关等

4.5 安全

权限、角色、用户

授权：grant

撤销：revoke

4.6 约束

实体完整性：主码。primary key(id)

1. 值唯一
2. 非空

参照完整性：外码。foreign key(tid) references teacher(id) [on <delete|update|> <no action|cascade>]

拒绝

级联

设为空值

用户定义的完整性

1. 属性上的约束

   列值非空

   列值唯一

   默认值

   check

2. 元组上的约束

   check

完整性约束条件

constraint <完整性约束条件名> <完整性约束条件>

4.7 触发器|存储过程

略