SQL基础

2022-08-27

SQL基础

一、SQL的组成

SQL包括以下几个部分：

1.DDL，提供定义relation schemas，删除relations和修改relation schemas的命令。

2.DML，提供在database中增删查改tuples的命令。

3.Integrity，DDL命令会对database加上一些确保Integrity的约束，例如给某些attribute赋予某些属性，主键或外键等等。

4.View definition，相当于可以把一些命令给打包。

5.Transaction control，用于处理一些特殊情况，例如并发读写数据库、阻止某些操作破坏操作的原子性。

6.Authorization，相当于Linux里面的用户组和权限，可以给权限到某些用户组/用户上。读/写/增/删都有对应的权限。

二、SQL数据定义

基本数据类型

1.**char(n)**：一个定长字符串类型，长度为n，是character的缩写。

2.**varchar(n)**：可变长字符串类型，最大长度为n。

3.int：整数类型。

4.smallint：小整数类型。

5.**numeric(p,d)**：固定浮点类型，整个数字的位数为p,小数点后的位数为d。

6.**float(n)**：精度至少为n位的浮点数类型。

对于字符串建议都用varchar，如果不同长度的char进行比较，会对较短的char填充空格。

基本规格定义

1.用create table命令来定义一个relation。

create table department
		(dept_name varchar(20),
        building varchar(15),
        budget numeric(12,2),
        primary key(dept_name));

在SQL中每个命令的结尾都必须带有分号。

2.主键可以是一个属性的集合，被要求必须是notnull和unique的。

3.外键关联两个relation，声明的形式为：

1	foreign key(A1,A2,...,An) references s

对于定义在relation A中的外键，如果他的外键目标是relation B,那么称A是referring，B为referred。其中A1，A2,…,An的值都必须同时要在A中和B中出现（属性名字可以不相同），并且在B中必须是B的主键。可以通过外键进行Integrity限制。例如要往A中插入的tuple的A1在B中找不到值对应时，这个操作就是不合法的。

基本SQL查询

1
2
3

select (all/distinct) attributes
from tablenames
where conditions;

从表中查询得到的数据也组成一张表，默认情况下是不会去重的，也就是括号里面all的情况，如果要去重就要加上distinct。

2.可以对attributes进行算术运算等。where就是筛选条件。

3.如果指定了多个table，执行顺序是：先将from的表格组成笛卡尔积，然后应用where里面的条件判断，最后选出select中的属性值。

Null

1.这里null应该理解为暂时不知道的数据而不是空值，虽然在很多数据库实现上是被显示为空值。

2.任何和null进行算术运算的结果都是null。

3.和null进行比较的结果都是null。

4.and/or/not等逻辑运算的短路定律依然成立。如果在有null的这些逻辑表达式中不能运用短路定律，则结果为null。

5.因为有null这种结果存在，where成立当且仅当结果为true。

聚合函数

1.有以下几种聚合函数：

avg，min，max，sum，count。

2.使用方法如下：

1
2
3

select avg(salary) as avg_salary
from instructor
where dept_name = 'Comp.sci';

1 2	select count(distinct ID) ...

有几点值的注意的是，这里聚合函数计算的结果最好是重命名，而且最后的输出结果只会有一个tuple，即聚合函数计算的结果。

如果想用count函数来计算有多少个tuple，用count(*)

3.如果在select中使用了聚合函数，那么输出结果只会有一个tuple。如果要输出另外的属性，就必须要进行分组。当然也可以利用分组操作实现对每个组内单独计算聚合函数值到一个tuple中。

1 2	... group by attributenames

这样凡是在attributenames中的attributes相同的tuples都会被分到同一组中。每个组最后会作为一个tuple输出。而在select中选择的属性要么是聚合函数计算结果，要么是出现在group中的属性，因为一个group只会输出一个tuple，如果一个属性不在group by中，那么就不知道要输出这个组中的哪一个tuple中的属性值了。

4.可以在group by语句后面加上having语句，这里和where语句不同的是，having语句用于对group进行筛选，而where语句是对tuple进行筛选。

完整SQL语句执行顺序

1.from语句首先执行得到一个relation。

2.如果where语句存在，则应用where来筛选得到的每一个tuple。

3.where筛选出来的tuple被group by语句进行分组，如果group by不存在，则所有筛选出来的语句组成一整个group。

4.执行having语句。

5.执行select语句。

嵌套查询

可以在增加子语句在where中，形式如下：

1 2	... where ... action(...)

其中括号内的是另一条query语句，这里action可以有以下几种：

1.in/not in：是否存在。

2.比较符号+all/some：与查询结果中的一个或所有tuples进行比较，这里比较符号可以为<,<=,>,>=,=,<>.

3.exist：结果的count是否>0。

4.unique：结果中是否有重复的tuple。

增删改

1.删除操作：

1 2	delete from r while P;

这里一条delete命令只能删除一个relation。

2.插入操作：

有两种形式：

1
2
3

insert into student
		select *
		from student;

或者

1 2	insert into course values("1","2")

3.修改操作：

update instructor
set salary = case
				when salary <= 100000 then salary * 1.05
				else salary * 1.03
		 	 end

CTE

提供了一种在大规模查询中写任意声明的方法。必须命名，可以指定返回参数的名字：

WITH cteName (col1,col2) AS (
	SELECT 1,2
)
SELECT col1 + col2 FROM cteName

WITH cteSource (maxId) As (
	SELECT MAX(sid) FROM enrolled
)
SELECT name FROM student,cteSource
WHERE student.sid = cteSource.maxId

SQL基础

一、SQL的组成

二、SQL数据定义

基本数据类型

基本规格定义

基本SQL查询

更多SQL基本操作

Null

聚合函数

完整SQL语句执行顺序

嵌套查询

增删改

CTE