SQL基础语句入门

SQL语句入门

起因

学校开设数据库相关的课程了，打算总结一篇关于基础SQL语句的文章。

SQL介绍

SQL最早版本是由IBM开发的，一直发展到至今。

SQL语言有如下几个部分：

• 数据定义语言DDL：提供定义关系模式、删除关系以及修改关系模式的命令
• 数据操纵语言DML：提供从数据库中查询信息以及在数据库中插入元组、删除元组以及修改元组的能力
• 完整性：SQL DDL包括定义完整性的约束命令
• 视图定义：SQL DDL包括定义视图的命令
• 事务控制：SQL包括定义事务的开始点和结束点命令
• 嵌入式SQL和动态SQL：定义如何嵌入如C++等这样的编程语言
• 授权：SQL DDL包括定义对关系和视图的访问权限的命令

SQL标准支持多种固有类型：

• char(n) 具有用户指定长度为n的字符串
• varchar(n) 最大长度为n的可变长字符串
• int 整数
• smallint 小正数(依赖于机器的整数类型的子集)
• numeric(p,d) 指定精度的定点数。此数字有p位(加上一个符号位)小数点右边有p位数中的d位数字
• real 单精度
• double 双精度
• float(n) 精度至少为n的浮点数

SQL语句入门

首先说明一下，SQL语句后面的分号是可选的。

创建关系

CREATE TABLE student
(
	Sname VARCHAR(20),
	Sid INT,
	PRIMARY KEY(Sid)
);

上述语句会创建一个student的关系(表)，它包含学生姓名和学生id号码。其中，Sname学生姓名的最大长度不超过20个字符，Sid为整型，并且PRIMARY KEY(Sid)对关系进行了完整性约束，说明学号作为主键，也就是说同一个学生他的id号码是唯一的。

SQL还支持许多完整性约束，例如外码与非空，下面仅对这两种进行介绍：

外码声明关系中的任意元组在属性上的取值必须对应于另外的某个关系的某元组在主码属性上的取值，其形式为foreign key(A,B,C)references X，其中A B C为属性，X为指定的关系

非空完整性约束表示某个属性的取值不能为空，其形式A INT not null表示类型为整型名为A的属性不能为空

下面我们创建一个与课程相关的表，要求主键(主码)为课程id且不为空，外码为上面student关系的学生姓名属性来做演示：

CREATE TABLE course
(
	Cid INT NOT NULL,
	Sname VARCHAR(20),
	Cname VARCHAR(20),
	PRIMARY KEY(Cid),
	FOREIGN KEY(Sname) REFERENCES student
);

此时Sname的取值必须是student关系的Sname中的的一个取值。

删除关系

删除操作有两种：

下方这条语句会删除名为course的关系

DROP TABLE course;

而

DELETE FROM course;

则会删除course关系中的所有元组（数据），但该关系保留

说白一点就是前者连表带数据一块删除，后者只剩一个空表

增加与删除属性

有时候会向关系中添加一个新的属性，则需要用到以下语句

ALTER TABLE student ADD Sage INT;

该语句向student中添加了一个名为Sage，类型为整型的属性，用于表示学生的年龄

在student中原有的元组在Sage的取值都将为空，即null（意思就是老数据原来没有年龄，现在新建了年龄这一列，之前的老数据该列上的取值都为空）

删除属性就很简单了，我们删除student关系中的Sage属性为例，语句如下

ALTER TABLE student DROP Sage;

但是注意，很多数据库都不支持这一点，执行该语句可能会报错。

查询

SQL查询结构由三个子居构成select，from，where

查询以在from子句列出的关系作为输入，并在这些关系上进行where和select子句中的指定运算，然后产生一个关系作为结果。

为方便演示，先向student表中插入如下几条数据

INSERT INTO student VALUES('CairBin',1);
INSERT INTO student VALUES('Bob',2);
INSERT INTO student VALUES('Alice',3);
INSERT INTO student VALUES('Alice',4);

INSERT INTO course VALUES(4,'Alex','chemistry');
INSERT INTO course VALUES(1,'CairBin','math');
INSERT INTO course VALUES(2,'CairBin','chemistry');
INSERT INTO course VALUES(3,'Alice','chemistry');

单关系查询

我们现在想要获取student关系中的所有学生的姓名，也就是Sname属性列下的所有数据，则可以使用如下语句

SELECT Sname FROM student;

当然可以跟随多个属性，例如输出学生姓名和id

SELECT Sname,Sid FROM student;

另外，在关系模型的形式化数学定义中，关系是一个集合，因此重复的元组不会出现在集合中。但是对于实际操作来讲，去重是相当费时的，所以SQL允许在数据库关系和数据库表达式的结果中出现重复。

（但是包含主码声明的关系中不可以，因为这回违背主码的完整性约束。例如上方的student关系，它不允许两个名为Alice的学生的id号码相同）

我么执行下方语句

SELECT Sname FROM student;

结果如下

Sname

CairBin

Bob

Alice

Alice

这在SQL表达式结果中出现重复，如果想要强行去重，则需要用到关键字distinct

我们对该语句进行变形

SELECT DISTINCT Sname FROM student;

查看结果发现两个Alice只剩下一个了

select子句中还可含有加减乘除运算符+ - * /

例如查询每个学生的id，并输出它们各自乘10的结果

SELECT Sid*10 FROM student;

where子句会筛选处那些在from子句所指定关系中满足特定条件的元组，并且where子句允许使用如下逻辑连词以及运算符

逻辑连词以及运算符	描述
and	且
not	非
or	或
<	小于
<=	小于等于
>	大于
>=	大于等于
=	等于
<>	不等于

例如我们要输出student中id大于1的学生姓名

Select Sname from student where Sid>1;

这里应当注意，以下语句是不符合SQL标准的，对于一些数据库或者它们的早期版本会报错（例如MySQL，尽管在后来版本进行了支持，但是不要这样做）

SELECT Sname FROM student WHERE 1<Sid;

多关系查询

例如我们要输出course中Sname属性与student的Sname属性取值相同的课程名和学生名：

SELECT Cname,student.Sname FROM course,student WHERE course.Sname = student.Sname;

更名运算

更名运算用到了as子句，它既可以出现在from中也可以出现在select中，作用是重新命名

（由于早期SQL没有as关键字，在像Oracle这样的数据库中，不允许form出现as关键字，如果要做别名，则需将oldName as newName改成oldName newName）

我们先看看原语句效果

Select Sid from student where Sname = 'Alice';

使用as子句

Select Sid as 学号 from student where Sname = 'Alice';

当然还有as在from中的情况，这种情况是为了方便指代关系名

例如

SELECT course.Cname,student.Sname FROM course,student WHERE course.Sname = student.Sname;

利用as可以这样写

SELECT c.Cname,s.Sname FROM course AS c,student AS s WHERE c.Sname = s.Sname;

看到这里可能会有人问，这有什么意义？当然，对于简短的语句效果不太明显，但是对于下方这种语句你就能看到它的好处了

SELECT s FROM(
	SELECT Sname AS s, Sid AS id FROM student
) AS T
WHERE T.id>1;

字符串运算

SQL标准是对字符串大小写敏感的，但是在一些数据库系统如MySQL、SQL Server中是不区分大小写的。

SQL还允许对字符串进行函数操作，如upper()将字符串转大写，lower()转小写，trim()去掉字符串后面的空格。不同数据库系统提供的函数可能会不同。

在字符串上可使用like运算符来实现模式匹配，模式是大小写敏感的（但MySQL中，或在PostgreSQL中使用ilike时例外）

一般用两个特殊字符来描述模式，一个是百分号%，另外一个是下划线_

前者是个通配符，也就是说表示某个字符后面所有的位；后者是个占位符，仅表示某个字符后的一位，下面举例

SELECT Sname FROM student WHERE Sname LIKE 'C%';

输出结果是CairBin

SELECT Sname FROM student WHERE Sname LIKE 'C_';

输出结果是一张空表

前者会查找Sname列所有以C开头的数据，后者会查找Sname列所有以C开头但C后面仅有一位（也就是说总长度为两字符）的数据

当然，SQL允许使用转义字符来表示特殊字符

SELECT Sname FROM student WHERE Sname LIKE 'C/%%' ESCAPE '/';

该语句会用/来表示转义字符，作用是查找以C和百分号开头的数据。在语句中/%%应当分成/%和%来看，前者是个转义字符，表示普通的百分号，后者是个通配符表示后面的任意位为任意数字。

排序

SQL为用户提供了对关系中元组显示次序的一些控制，order by子句可以让查询结果中的元组按排列顺序显示。

例如，升序输出student关系中的学生id

SELECT Sid FROM student ORDER BY Sid;

该子句还有两个关键字，asc表示升序，desc表示降序

接下来进行降序输出

SELECT Sid FROM student ORDER BY Sid DESC;

between谓词

X between a and b表示X属性列中取大于等于a小于等于b的值

SELECT * FROM student WHERE Sid BETWEEN 1 AND 3;

该语句相当于下方语句

SELECT * FROM student WHERE Sid >= 1 AND Sid <= 3;

集合运算

SQL的union、intersect、except对应数学上集合的交集、并集、差集运算

例如student表中学号小于2与学号大于3的学生信息元组组成的集合的并集

(SELECT * FROM student where Sid <2) union (select * from student where Sid>3);

注意：MySQL不支持except

聚集函数

聚集函数是以值集为输入并返回单个值的函数。SQL标准提供了五个固有的聚集函数：

• 平均值 avg()
• 最小值 min()
• 最大值 max()
• 求和 sum()
• 计数 count()

基本聚集操作比较简单，例如要求student关系中所有学生id的平均值

SELECT AVG(Sid) FROM student;

还有一种操作是分组聚集，它可以将聚集函数作用在一组元组集上

例如求student中每组同名学生的id之和

SELECT Sname,SUM(sid) FROM student GROUP BY Sname;

在有些时候，某属性的取值可能为空，但需要对该属性列进行聚集，但SQL聚集函数并不认为结果为null，而是认为应该跳过该值。

另外，在SQL:1999中引入了布尔类型，它可以取true、false、unknown，聚集函数some()和every()可应用于布尔值的集合，并分别计算这些值的析取(or)和合取(and)

having子句

有些时候我们对分组有限定条件要求，例如求student中每组同名学生的id之和，但是要求和在5以上的，这种操作需要利用having子句

SELECT Sname,SUM(sid) FROM student GROUP BY Sname HAVING SUM(sid)>5;

注意，任何出现在having子句中但是没被聚集的属性都需要出现在group by子句中

数据库修改

删除

删除与查询类似，例如删除student中id为1的学生信息

DELETE FROM student WHERE Sid = 1;

插入

例如我们向student插入一个名为Alex且id为5的一条数据，最简单的一条insert语句如下：

INSERT INTO student VALUES('Alex',5);

这种情况values后的括号中的值的顺序与关系中属性的顺序一致。

还有一种情况是指定属性和顺序，如下：

INSERT INTO student(Sid,Sname) VALUES(5,'Alex');

该语句指定了两个属性，第一个值对应的属性为id，第二个值对应的属性为学生名。

更新

某些情况下，我们不想改变元组的所有值，但是要改变元组的局部值，这就需要用到update语句

如我们将之前插入的名为Alexid为5的学生姓名改为CairBin

UPDATE student SET Sname='CairBin' WHERE Sid = 5;

SQL提供case结构，它在单条update语句中执行多条更新。

例如我们将course关系中所有为chemistry的学科改成math，math改成chemistry

UPDATE course SET Cname=
CASE Cname
	WHEN 'math' THEN 'chemistry'
	WHEN 'chemistry' THEN 'math'
END;

当然该语句还支持else，当所有when的条件都不符合时就会取else后的值

UPDATE course SET Cname=
CASE Cname
	WHEN 'math' THEN 'chemistry'
	WHEN 'chemistry' THEN 'math'
ELSE
	'English'
END;

结束

本文章并没有给出专门介绍SQL嵌套子查询相关的部分，这是因为我认为嵌套子查询基本上是原有语句的组合并且在举例的时候也用到过（尤其是更名运算那部分，所以我打算将这部分放到后面的文章去写。