数据库高级教程

SQL简介

SQL (Structured Query Language:结构化查询语言) 是用于管理关系数据库管理系统（RDBMS）。 SQL 的范围包括数据插入、查询、更新和删除，数据库模式创建和修改，以及数据访问控制。

演示用数据库

表s

+-----+-----------+--------+------+------+
| sno | sname     | gender | dept | age  |
+-----+-----------+--------+------+------+
| 801 | 金贤重    | 男     | CS   |   23 |
| 802 | 李敏镐    | 男     | MA   |   25 |
| 803 | 那英      | 女     | CS   |   41 |
| 804 | 金秀贤    | 女     | MA   |   24 |
| 805 | 刘德华    | 男     | NULL |   52 |
| 806 | 文章      | 男     | CS   |   27 |
| 807 | 刘欢      | 男     | MA   |   51 |
| 808 | 马伊琍    | 女     | CS   |   36 |
| 809 | 姚笛      | 女     | MA   |   23 |
| 810 | 周杰伦    | 男     | NULL |   39 |
+-----+-----------+--------+------+------+

表sc

+-----+-----+-------+
| sno | cno | grade |
+-----+-----+-------+
| 801 | 01  |    81 |
| 801 | 02  |  NULL |
| 801 | 03  |    90 |
| 801 | 04  |    40 |
| 802 | 01  |    59 |
| 802 | 02  |    30 |
| 802 | 03  |    60 |
| 802 | 04  |     0 |
| 803 | 01  |     0 |
| 803 | 02  |    45 |
| 803 | 03  |    40 |
| 803 | 04  |  NULL |
| 804 | 01  |    60 |
| 804 | 02  |    80 |
| 804 | 03  |  NULL |
| 804 | 04  |    80 |
| 805 | 01  |    30 |
| 805 | 02  |    70 |
| 805 | 03  |    50 |
| 805 | 04  |    90 |
| 806 | 01  |    90 |
| 806 | 02  |    60 |
| 806 | 03  |    70 |
| 806 | 04  |    60 |
| 807 | 01  |    60 |
| 807 | 03  |    70 |
| 807 | 04  |    70 |
| 808 | 01  |  NULL |
| 808 | 03  |    80 |
| 808 | 04  |    50 |
| 809 | 05  |     0 |
+-----+-----+-------+

SQL SELECT TOP, LIMIT, ROWNUM 子句

SQL SELECT TOP 子句

注意:并非所有的数据库系统都支持 SELECT TOP 语句。 MySQL 支持 LIMIT 语句来选取指定的条数数据， Oracle 可以使用 ROWNUM 来选取。

MySQL 语法

SELECT column_name(s)
FROM table_name
LIMIT number;

MySQL SQL SELECT TOP实例

SELECT
	*
FROM
	s
LIMIT 5;

运行结果:

+-----+-----------+--------+------+------+
| sno | sname     | gender | dept | age  |
+-----+-----------+--------+------+------+
| 801 | 金贤重    | 男     | CS   |   23 |
| 802 | 李敏镐    | 男     | MA   |   25 |
| 803 | 那英      | 女     | CS   |   41 |
| 804 | 金秀贤    | 女     | MA   |   24 |
| 805 | 刘德华    | 男     | NULL |   52 |
+-----+-----------+--------+------+------+

SQL 通配符

通配符可用于替代字符串中的任何其他字符。

SQL 通配符

在 SQL 中，通配符与 SQL LIKE 操作符一起使用。
|通配符|描述|
|—|—|
|%|替代 0 个或多个字符|
|_ | 替代一个字符 |
|[charlist] |字符列中的任何单一字符|
|[^charlist]-或[!charlist]|不在字符列中的任何单一字符|

SQL 连接(JOIN)

SQL join 用于把来自两个或多个表的行结合起来。
下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

SQL INNER JOIN实例

SELECT
	s.sno,s.sname,sc.grade
FROM
	s
INNER JOIN
	sc
ON 
	s.sno = sc.sno;

运行结果:

+-----+-----------+-------+
| sno | sname     | grade |
+-----+-----------+-------+
| 801 | 金贤重    |    81 |
| 801 | 金贤重    |  NULL |
| 801 | 金贤重    |    90 |
| 801 | 金贤重    |    40 |
| 802 | 李敏镐    |    59 |
| 802 | 李敏镐    |    30 |
| 802 | 李敏镐    |    60 |
| 802 | 李敏镐    |     0 |
| 803 | 那英      |     0 |
| 803 | 那英      |    45 |
| 803 | 那英      |    40 |
| 803 | 那英      |  NULL |
| 804 | 金秀贤    |    60 |
| 804 | 金秀贤    |    80 |
| 804 | 金秀贤    |  NULL |
| 804 | 金秀贤    |    80 |
| 805 | 刘德华    |    30 |
| 805 | 刘德华    |    70 |
| 805 | 刘德华    |    50 |
| 805 | 刘德华    |    90 |
| 806 | 文章      |    90 |
| 806 | 文章      |    60 |
| 806 | 文章      |    70 |
| 806 | 文章      |    60 |
| 807 | 刘欢      |    60 |
| 807 | 刘欢      |    70 |
| 807 | 刘欢      |    70 |
| 808 | 马伊琍    |  NULL |
| 808 | 马伊琍    |    80 |
| 808 | 马伊琍    |    50 |
| 809 | 姚笛      |     0 |
+-----+-----------+-------+
31 rows in set (0.00 sec)

SQL LEFT JOIN 关键字

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

SQL LEFT JOIN 语法

• LEFT JOIN
• LEFT OUTER JOIN

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

SQL RIGHT JOIN 关键字

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

SQL RIGHT JOIN 语法

• RIGHT JOIN
• RIGHT OUTER JOIN

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

SQL FULL OUTER JOIN 关键字

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.
FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

SQL FULL OUTER JOIN 语法

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

SQL UNION 操作符

SQL UNION 操作符合并两个或多个 SELECT 语句的结果。

SQL UNION语法

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

SQL UNION ALL语法

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

• 默认UNION取出的值不重复,如允许重复则使用UNION ALL
• UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名

• SQL UNION
• SQL UNION ALL

SELECT
	sno
FROM
	s
UNION
SELECT
	sno
FROM
	sc;

运行结果:

+-----+
| sno |
+-----+
| 801 |
| 802 |
| 803 |
| 804 |
| 805 |
| 806 |
| 807 |
| 808 |
| 809 |
| 810 |
+-----+

SELECT
	sno
FROM
	s
UNION ALL
SELECT
	sno
FROM
	sc;

运行结果:

+-----+
| sno |
+-----+
| 801 |
| 802 |
| 803 |
| 804 |
| 805 |
| 806 |
| 807 |
| 808 |
| 809 |
| 810 |
| 801 |
| 801 |
| 801 |
| 801 |
| 802 |
| 802 |
| 802 |
| 802 |
| 803 |
| 803 |
| 803 |
| 803 |
| 804 |
| 804 |
| 804 |
| 804 |
| 805 |
| 805 |
| 805 |
| 805 |
| 806 |
| 806 |
| 806 |
| 806 |
| 807 |
| 807 |
| 807 |
| 808 |
| 808 |
| 808 |
| 809 |
+-----+

SQL CREATE DATABASE 语句

CREATE DATABASE 语句用于创建数据库

SQL CREATE DATABASE 语法

CREATE DATABASE dbname;

SQL CREATE TABLE 语句

CREATE TABLE 语句用于创建数据库中的表。

表由行和列组成，每个表都必须有个表名。

SQL CREATE TABLE 语法

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

• column_name 参数规定表中列的名称
• data_type 参数规定列的数据类型（例如 varchar、integer、decimal、date 等等）
• size 参数规定表中列的最大长度。

SQL CREATE TABLE 实例

创建一个名为”s”的表,包含5列: sno,sname,gender,dept,age

CREATE TABLE s
(
	sno int,
	sname varchar(255),
	gender char(2),
	dept char(2),
	age int
);

SQL 约束（Constraints）

• SQL 约束用于规定表中的数据规则。
• 如果存在违反约束的数据行为，行为会被约束终止。
• 约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

SQL CREATE TABLE + CONSTRAINT 语法

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

SQL中已存在的约束

约束名	描述
NOT NULL	不能存NULL
UNIQUE	值唯一
PRIMARY KEY	不为空且唯一 主键 用于确定唯一记录
FOREIGN KEY	一个表中的数据匹配另一个表 外键 保证数据完整性
CHECK	保证值符合给定条件
DEFAULT	规定未赋值时的默认值

SQL NOT NULL 约束

• NOT NULL 约束强制列不接受 NULL 值。

SQL NOT NULL 约束实例

• CREATE
• ALERT

CREATE TABLE s
(
	sno int NOT NULL,
	sname varchar(255) NOT NULL,
	gender char(2) NOT NULL,
	dept char(2),
	age int
);

• 添加

ALTER TABLE s
MODIFY age int NOT NULL;

• 删除

ALTER TABLE s
MODIFY age int NULL;

SQL UNIQUE 约束

• UNIQUE 约束唯一标识数据库表中的每条记录
• 每个表可以有多个 UNIQUE 约束

SQL UNIQUE 约束实例

• CREATE
• ALERT

CREATE TABLE s
(
	sno int NOT NULL,
	sname varchar(255) NOT NULL,
	gender char(2) NOT NULL,
	dept char(2),
	age int
	UNIQUE (sno)
);

• 添加

ALTER TABLE s
ADD UNIQUE (sno);

• 删除

ALTER TABLE s
DROP INDEX sno;

SQL PRIMARY KEY 约束

• PRIMARY KEY 约束唯一标识数据库表中的每条记录
• 每个表都应该有一个主键，并且每个表只能有一个主键

SQL PRIMARY KEY 约束实例

• CREATE
• ALERT

CREATE TABLE s
(
	sno int,
	sname varchar(255) NOT NULL,
	gender char(2) NOT NULL,
	dept char(2),
	age int
	PRIMARY KEY (sno)
);

• 添加

ALTER TABLE s
ADD PRIMARY KEY (sno);

• 删除

ALTER TABLE s
DROP PRIMARY KEY;

SQL FOREIGN KEY 约束

• 一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。

SQL FOREIGN KEY 约束实例

• CREATE
• ALERT

CREATE TABLE sc
(
	sno int,
	cno int NOT NULL,
	grade int,
	FOREIGN KEY (sno) REFERENCES s(sno)
);

• 添加

ALTER TABLE Orders
ADD CONSTRAINT fk_sno 
FOREIGN KEY (sno) REFERENCES s(sno);

• 删除

ALTER TABLE Orders
DROP FOREIGN KEY fk_sno;

SQL CHECK 约束

• CHECK 约束用于限制列中的值的范围。
• 如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
• 如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。

SQL CHECK 约束实例

• CREATE
• ALERT

CREATE TABLE s
(
	sno int,
	sname varchar,
	gender char(2),
	dept char(2),
	age int,
	PRIMARY KEY (sno),
	CHECK (gender = '男' OR gender = '女')
);

• 添加

ALTER TABLE s
ADD CONSTRAINT fk_sno CHECK (sno>0);

• 删除

ALTER TABLE s
DROP CONSTRAINT fk_sno;

SQL DEFAULT 约束

• 如果没有规定其他的值，那么会将默认值添加到所有的新记录。

SQL DEFAULT 约束实例

• CREATE
• ALERT

CREATE TABLE s
(
	sno int,
	sname varchar,
	gender char(2),
	dept char(2),
	age int DEFAULT 0,
	PRIMARY KEY (sno),
	CHECK (gender = '男' OR gender = '女')
);

• 添加

ALERT TABLE s
ALERT age DEFAULT 0;

• 删除

ALERT TABLE s
ALERT COLUMN age DROP DEFAULT;