SQL语言学习笔记

本文最后更新于：2020年7月20日晚上

一、SQL简介

1.1 SQL是什么

SQL是结构化查询语言，它是一种用于存储，操作和检索存储在关系数据库中的数据的计算机语言

关系型数据库可以通过E-R图转换而来，E-R图中矩形是实体集，菱形是关系，椭圆是属性。

1.2 SQL命令

1） DDL——数据定义语言

序号	命令	描述
1	CREATE	用于在数据库中创建新表，表视图或其他对象
2	ALTER	用于修改现有数据库对象，例如：表
3	DROP	用于删除整个表，数据库中的表或其他对象的视图

2） DML——数据操纵语言

序号	命令	描述
1	SELECT	从一个或多个表中检索某些记录
2	INSERT	创建一条记录
3	UPDATE	用于修改(更新)记录
4	DELETE	删除记录

3） DCL——数据控制语言

序号	命令	描述
1	GRANT	为用户提供权限
2	REVOKE	撤销用户授予的权限

1.3 数据的完整性

每个RDBMS(关系型数据库)都存在以下类别的数据完整性

实体完整性 - 表中没有重复的行。
域完整性 - 通过限制值的类型，格式或范围，为给定列强制执行有效条目。
参照完整性 - 其他记录使用(引用)导致这些行无法删除。
用户定义的完整性 - 实施一些不属于实体，域或参照完整性的特定业务规则

1.4 SQL约束

NOT NULL约束 - 确保列不能具有NULL值。
默认值约束 - 在未指定列时为列提供默认值。
唯一约束 - 确保列中的所有值都不同。
主键 - 唯一标识数据库表中的每一行/记录。
外键 - 唯一标识任何其他数据库表中的行/记录。
检查约束 - CHECK约束确保列中的所有值都满足特定条件。
索引 - 用于非常快速地从数据库创建和检索数据。

1.5 数据库范式

1）第一范式（1NF）

在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。
所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。
简而言之，第一范式就是无重复的列。

2）第二范式（2NF）

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。
第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。
简而言之，第二范式就是非主属性部分依赖于主关键字。

3）第三范式（3NF）

满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。
简而言之，第三范式就是属性不依赖于其它非主属性。

1.6 运算符

假设变量a的值是：10，变量b的值是：20

1） SQL算术运算符

操作符	描述	示例
+	加法，执行加法运算。	a + b = 30
-	减法，执行减法运算。	a + b = -10
*	除法，执行除法运算	a * b = 200
/	用左操作数除右手操作数	b / a = 2
%	用左手操作数除左手操作数并返回余数	b % a = 0

1.7 在MySQL中创建表的示例

create database test default character set utf8 collate utf8_general_ci;

CREATE TABLE regions (
    region_id INT (11) AUTO_INCREMENT PRIMARY KEY,
    region_name VARCHAR (25) DEFAULT NULL
);

CREATE TABLE countries (
    country_id CHAR (2) PRIMARY KEY,
    country_name VARCHAR (40) DEFAULT NULL,
    region_id INT (11) NOT NULL,
    FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE locations (
    location_id INT (11) AUTO_INCREMENT PRIMARY KEY,
    street_address VARCHAR (40) DEFAULT NULL,
    postal_code VARCHAR (12) DEFAULT NULL,
    city VARCHAR (30) NOT NULL,
    state_province VARCHAR (25) DEFAULT NULL,
    country_id CHAR (2) NOT NULL,
    FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE jobs (
    job_id INT (11) AUTO_INCREMENT PRIMARY KEY,
    job_title VARCHAR (35) NOT NULL,
    min_salary DECIMAL (8, 2) DEFAULT NULL,
    max_salary DECIMAL (8, 2) DEFAULT NULL
);

CREATE TABLE departments (
    department_id INT (11) AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR (30) NOT NULL,
    location_id INT (11) DEFAULT NULL,
    FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE employees (
    employee_id INT (11) AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR (20) DEFAULT NULL,
    last_name VARCHAR (25) NOT NULL,
    email VARCHAR (100) NOT NULL,
    phone_number VARCHAR (20) DEFAULT NULL,
    hire_date DATE NOT NULL,
    job_id INT (11) NOT NULL,
    salary DECIMAL (8, 2) NOT NULL,
    manager_id INT (11) DEFAULT NULL,
    department_id INT (11) DEFAULT NULL,
    FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);

CREATE TABLE dependents (
    dependent_id INT (11) AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR (50) NOT NULL,
    last_name VARCHAR (50) NOT NULL,
    relationship VARCHAR (25) NOT NULL,
    employee_id INT (11) NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
);

二、SQL数据库操作

语句SHOW DATABASES;用于查看可用数据库列表。

2.0 帮助命令 help

如果不知道命令或者函数怎么用，可以使用help命令，mariaDB里面都会罗列出语法。

--示例1
help

/*示例2*/
help create table
help join
help trigger
help grant

2.1 Create Database 语句

SQL中CREATE DATABASE语句用于创建新的SQL数据库：

--语法格式
CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] database_name
[DEFAULT] CHARACTER SET [=]charset_name|[DEFAULT] COLLATE [=]collation_name;
/*[]标记其内容为可选，|用于分隔跨括号中的选择项，DEFAULT用于指定默认值，关键字CHARACTER SET用于指定数据库字符集，关键字COLLATE用于指定字符集的校对规则*/

/*示例*/
CREATE DATABASE testdb;

/*查看*/
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| testdb             |
+--------------------+
3 rows in set

数据库名称(database_name)在RDBMS(关系数据库管理系统)中必须是唯一的

2.2 Drop Database 语句

SQL中DROP DATABASE语句用于删除SQL模式中已存在的数据库。

--语法格式
DROP DATABASE database_name;

/*示例*/
DROP DATABASE testdb;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set

2.3 Rename Database 语句

当需要更改数据库的名称时，将使用SQL RENAME DATABASE

1
2
3

RENAME DATABASE old_db_name TO new_db_name;

ALTER DATABASE old_name MODIFY NAME = new_name;

2.4 Use 语句

如果SQL模式中有多个数据库，那么在开始操作之前，需要选择一个将执行操作的数据库。

SQL中的USE语句用于选择SQL模式中的任何现有数据库。

--语法格式
USE database_name;

/*示例*/
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set

USE test;

三、表操作

3.1 创建表 Create Table

CREATE [TEMPORARY] TABLE table_name(
     column_name_1 data_type default value column_constraint,
     column_name_2 data_type default value column_constraint,
     ...,
     table_constraint
);

由table_name指定的表名在数据库中必须是唯一的
每个列定义由列名，列的数据类型，默认值和一个或多个列约束组成
列的数据类型指定列可以存储的数据类型。列的数据类型可以是数字，字符，日期等
列约束控制可以存储在列中的值的类型。例如，NOT NULL约束确保列不包含NULL值。列可能有多个列约束。例如，users表的username列可以同时具有NOT NULL和UNIQUE约束。
如果约束包含多个列，则使用表约束。例如，如果表的主键包含两列，则在这种情况下，必须使用PRIMARY KEY主键约束。
TEMPORARY关键字用于创建临时表，当断开与数据库连接时，mysql会自动删除它们。

/*示例*/
CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(50) NOT NULL
);

/*
courses课程表有两列：course_id和course_name;

course_id是课程表的主键列。 每个表都有一个且只有一个主键，用于唯一标识表中的每一行

course_id的数据类型是整数，由INT关键字表示。 此外，course_id列的值为AUTO_INCREMENT(自动递增)。表示表中数据类为整型的列设置自增属性，自动设置为“此前表中该列的最大值加1”，同时每个表智能有一个AUTO_INCREMENT列，并且它必须被索引。

course_name存储课程名称。 其数据类型是最大长度为50的可变长度的字符串(VARCHAR)。NOT NULL约束确保course_name列中不存储NULL值。
*/

--示例
/*有了课程表。 要存储训练数据，请按如下方式创建名为training的新表。*/
CREATE TABLE trainings (
    employee_id INT,
    course_id INT,
    taken_date DATE,
    PRIMARY KEY (employee_id , course_id)
);
/*trainings表包含三列：

employee_id列存储参加课程的员工的ID。
course_id列存储员工所采用的课程。
taken_date列存储员工参加课程的日期。

因为trainings表的主键由两列组成：employee_id和course_id，所以必须使用PRIMARY KEY表约束。*/

3.2 更新表 Alter Table

使用SQL ALTER TABLE更改数据库中现有表的结构

ALTER TABLE语句用于对现有表执行以下操作：

使用ADD子句添加新列。
使用CHANGE子句用于修改表中的列名称和数据类型。
使用ALTER SET DEFAULT子句修改或删除表中指定列的默认值。
使用MODIFY子句修改列的属性，例如：约束，默认值等。
使用DROP子句删除列。

3.2.1 ALTER TABLE ADD列

该子句的作用是向表中添加一个或多个新列

--语法格式
ALTER TABLE table_name
ADD [COLUMN] column_name data_type column_constraint [AFTER existing_column];
/*要向表中添加一个或多个列，需要执行以下步骤：

首先，在ALTER TABLE子句之后指定要添加table_name，表示列所在的表。
其次，将新列定义放在ADD子句之后。 如果要在表中指定新列的顺序，可以使用可选子句AFTER existing_column。
*/

/* 以下语句将一个名称为credit_hours的新列添加到courses表中。*/
ALTER TABLE courses ADD credit_hours INT NOT NULL;

/*示例*/
ALTER TABLE courses
ADD COLUMN fee NUMERIC (10, 2) AFTER course_name,
ADD COLUMN max_limit INT AFTER course_name;

3.2.2 ALTER TABLE CHANGE列名称和属性

CHANGE子句用于修改表中的列名称和数据类型。

--语法格式
ALTER TABLE table_name
CHANGE [COLUMN] column_name new_column_name column_properties;

/*示例*/
ALTER TABLE students
CHANGE students_name names varchar(100) NOT NULL;

3.2.3 ALTER TABLE ALTER SET DEFAULT列

ALTER [COLUMN] SET子句修改或删除表中指定列的默认值。

--语法格式
ALTER TABLE table_name
ALTER [COLUMN] column_name SET DEFAULT 'xxx';

/*示例*/
ALTER TABLE customers
ALTER city SET DEFAULT 'beijing';

3.2.4 ALTERE TABLE MODIFY列

MODIFY子句用于更改现有列的数据类型。

--语法格式
ALTER TABLE table_name
MODIFY [COLUMN] column_name column_properties;

/*示例*/
ALTER TABLE students MODIFY name VARCHAR(20) NOT NULL;

3.2.5 ALTER TABLE DROP列

当表的列已过时且未被任何其他数据库对象(如触发器，视图，存储过程和存储过程)使用时，将其从表中删除，请使用以下语法：

--语法格式
ALTER TABLE table_name
DROP [COLUMN] column_name,
DROP [COLUMN] column_name,
...

/*示例1-删除一列*/
ALTER TABLE courses DROP fee;

/*示例2-删除多列*/
ALTER TABLE courses
DROP COLUMN max_limit,
DROP COLUMN credit_hours;

3.2.6 ALTER TABLE RENAME TO

RENAME TO子句为表重新赋予一个表名。

--语法格式
ALTER TABLE table_name
RENAME [TO] new_table_name;

/*示例*/
ALTER TABLE classes
RENAME new_classes;

3.3 删除表 DROP TABLE

使用SQL DROP TABLE语句删除数据库中的一个或多个表

--语法格式
DROP [TEMPORARY] TABLE [IF EXISTS] table_name [ RESTRICT | CASCADE ];
--为了防止删除不存在的表的错误，使用可选子句IF EXISTS。

/*示例*/
DROP TABLE students;

/*删除多个表*/
DROP TABLE IF EXISTS table_name1,table_name2,...;

DROP TABLE语句永久删除表的数据和结构，某些数据库系统要求表中的记录必须为空时才能从数据库中删除。这有助于防止意外删除仍在使用的表。
要删除表中的所有数据，可以使用DELETE或TRUNCATE TABLE语句。
要删除由另一个表的外键约束引用的表，必须在删除表之前禁用或删除外部约束。

3.4 快速删除表 TURNCATE TABLE

使用SQL TRUNCATE TABLE语句高效，快速地删除表中的所有数据

--语法格式
TRUNCATE TABLE table_name;     /*快速删除大表中的所有行*/

TRUNCATE TABLE IF EXISTS table_name1, table_name2, ...;

/*示例*/
TRUNCATE TABLE IF EXISTS students, teachers;

3.5 重命名表 RENAME TABLE

SQL RENAME TABLE用于更改表的名称。

--语法格式1
ALTER TABLE table_name
RENAME [TO] new_table_name;
--语法格式2
RENAME TABLE old_table_name To new_table_name;

/*示例1*/
ALTER TABLE Students
RENAME Student_bank;
/*示例2*/
RENAME TABLE Student_bank TO Students;

3.6 复制表&临时表

1）复制表

如果要将SQL表复制到同一数据库中的另一个表中，可以使用select语句。

--语法格式1
--从一个表复制到另一个表的语法如下：
SELECT * INTO <destination_table> FROM <source_table>

/*示例*/
SELECT * INTO my_table_2 FROM my_table_1;

注意：SELECT INTO与INSERT INTO语句完全不同。

2）临时表

临时表可以在运行时创建，并且可以像普通表一样执行各种操作，这些临时表是在tempdb数据库中创建的。

根据行为和范围，有如下两种类型的临时表。1. 局部临时变量。 2. 全局临时变量。

① 局部临时变量

/*示例*/
/*局部临时变量表仅在当前连接时可用。 当用户与实例断开连接时，它会自动删除。 它以哈希(#)符号开头*/
CREATE TABLE #local temp table (  
    User_id int,  
    User_name varchar (50),  
    User_address varchar (150)  
)

② 全局临时变量

/*全局临时表名称以双哈希(##)开头。 创建此表后，它就像一个永久表。 它始终为所有用户准备好，并且在撤消总连接之前不会被删除。*/
CREATE TABLE ##new global temp table (  
    User_id int,  
    User_name varchar (50),  
    User_address varchar (150)

3.7 查看表

3.7.1 查看表的名称

--语法格式
SHOW [FULL] TABLES [{ FROM | IN } table_name]
[ LIKE 'pattern' | WHERE expr];

/*示例*/
SHOW TABLES;

3.7.2 显示表的结构

--语法格式
SHOW [FULL] COLUMNS {FROM|IN} table_name [{ FROM | IN } table_name]
[ LIKE 'pattern' | WHERE expr];
--或者使用DESCRIBE语句
{DESCRIBE | DESC} table_name [ col_name | wild ];

/*示例*/
DESC test.classes;

四、数据操作语句

4.1 INSERT语句

SQL提供了INSERT语句，用于将一行或多行插入表中。 INSERT语句用于：

向表中插入一行
向表中插入多行
将行从一个表复制到另一个表中。

--语法格式1
--插入一行,不建议省略column_name。
INSERT [INTO] table_name [(col_name,...)]
{VALUES | VALUE}
    ({expr | DEFAULT},...),(...),...;
--语法格式2
INSERT [INTO] table_name
    SET col_name={expr | DEFAULT},...
/*"expr"，表示一个常量、变量或一个表达式，也可以是空值NULL
“DEFAULT”关键字，用于指定此列值为该列的默认值，前提是该列已经有默认值。*/

--从其他表复制行记录
INSERT INTO table1 (column1, column2,...)
SELECT
    column1,
    column2
FROM
    table2
WHERE
    condition1;

/*示例1*/
INSERT students(class_id,name,gender,score)
VALUE
    ('3','小绿','M','95');

/*示例2*/
INSERT INTO temp(id,name)
VALUES
    (2,'xiaoli'),
    (3,'xiaowang'),
    (4,'xiaojun');

/*示例3*/
INSERT INTO customers
    SET cust_name='李四',cust_address='武汉市',cust_sex=DEFAULT;

4.2 Insert Into Select

在表中插入多行，可以将Insert与select语句结合使用。

1
2
3

INSERT INTO "table 1" ("column1", "column2",....)  
SELECT "column3", "column4",....  
FROM "table2";

INSERT INTO语句还可以包含许多子句，如：SELECT，GROUP BY，HAVING以及JOIN和ALIAS。因此，insert into select语句可能会有些复杂。

/*示例*/
INSERT INTO store (store_name, sales, transaction_date)  
SELECT store_name, sum (sales), transaction_date  
FROM sales_information  
GROUP BY store_name, transaction_date;

4.3 Update语句

使用SQL UPDATE语句来修改表中现有行的数据

--语法格式
UPDATE table_name
    SET col_name1={expr1 | DEFAULT}
        [,col_name2 = {expr2 | DEFAULT}] ...
    [WHERE condition]
    [ORDER BY ...]
    [LIMIT row_count];
/*在上面的语法中
首先，在SET子句中指定要修改的列。 SET子句中未列出的列的值不会被修改。
其次，指定WHERE子句中要更新的行。
第三，WHERE子句是可选的，如果省略它，表中的所有行都将受到影响。*/

/*示例1--小于3000的工资改为3000*/
UPDATE
    workers
SET
    salary=3000
WHERE
    salary<3000;

/*示例2--确保子项dependents的last_name始终与employees表中的last_name匹配*/
UPDATE dependents
SET last_name = (
    SELECT
        last_name
    FROM
        employees
    WHERE
        employee_id = dependents.employee_id
);

/*示例3--给工资超过100000美元的教师涨3%的工资，其余教师涨5%*/
UPDATE instructor
SET salary=case
        when salary<=100000 then salary * 1.05
        else salary * 1.03
end

4.4 UPDATE & JOIN语句

UPDATE JOIN可使用一个表和连接条件来更新另一个表。

/*假设有一个客户表，要用最新数据来更新客户表。可使用客户ID来连接在目标表和源表之间执行连接*/

/*语法格式*/
UPDATE customer_table  
INNER JOIN  
Customer_table  
ON customer_table.rel_cust_name = customer_table.cust_id  
SET customer_table.rel_cust_name = customer_table.cust_name

--示例
/*在table2中共有3行，假设想要将table1中的值更新为table2中column1为21和31行的值。
仅更新column2和column3的值。
最简单和最常用的方法是在update语句中使用join子句并在update语句中使用多个表。*/
UPDATE table1 t1
LEFT JOIN  table2 t2
ON t1.column1 = t2.column1
SET t1.column2 = t2.column2,  
t1.column3 = t2.column3
where t1.column1 in(21,31);

4.5 更新日期数据

要在SQL中更新日期和时间字段，则应使用以下查询。

--语法格式
UPDATE table
SET Column_Name = 'YYYY-MM-DD HH:MM:SS'  
WHERE Id = value

/*示例*/
UPDATE table1
SET EndDate = '2019-03-16 00:00:00.000'
WHERE id in(1,3);

4.6 DELETE语句

使用SQL DELETE语句删除表中的一行或多行。

--语法格式
DELETE
FROM
    table_name
WHERE
    condition;
/*1. 提供要删除行的表名称(table_name)。
2. 在WHERE子句中指定条件以标识需要删除的行记录。 如果省略WHERE子句，则将删除表中的所有行记录。 因此，应始终谨慎使用DELETE语句。*/

/*示例*/
DELETE FROM dependents
WHERE
    employee_id IN (100 , 101, 102);

五、SQL查询语句

SQL查询语句的通用形式：

select <[distinct] c1，c2…>
from <r1,……>
[where <condition>]
[group by <c1，c2.…> [having <cond2>]]
[order by<c1[desc] ,[c2[desc|asc],…]>

SQL查询语句执行顺序：
from→where→group（aggregate）→having→select→order by

5.1 SELECT语句

除了SELECT和FROM子句之外，SELECT语句还可以包含许多其他子句，例如 -

WHERE - 用于根据指定条件过滤数据
JOIN - 用于查询来自多个相关表的数据
GROUP BY - 用于根据一列或多列对数据进行分组
HAVING - 用于过滤分组
ORDER BY - 用于对结果集进行排序
LIMIT - 用于限制返回的行

--语法格式
SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
/*select子句的使用顺序需要按照上述语法格式依次输入执行。
[ALL | DISTINCT | DISTINCTROW ]指定是否返回结果集中的重复行，默认ALL。
SELECT * FROM table_name查询tbl_name中所有行列。*/

--语法格式附加
--替换查询结果集中的数据
CASE
WHEN 条件1 THEN 表达式1
    WHEN 条件2 THEN 表达式2
    ...
    WHEN 条件n THEN 表达式n
ELSE 表达式n
END

/*示例*/
/*查询特定的列*/
SELECT
    employee_id,
    first_name,
    last_name,
    hire_date
FROM
    employees;

/*示例2*/
/*以下查询使用FLOOR()，DATEDIFF()和CURRENT_DATE函数计算员工的服务年份。要计算服务年份，将DATEDIFF()函数的结果除以365。FLOOR()函数返回小于或等于数值表达式结果的最大整数。YoS是下面表达式的列别名。*/
SELECT
    employee_id,
    first_name,
    last_name,
    FLOOR(DATEDIFF(NOW(), hire_date) / 365) AS YoS
FROM
    employees;

/*示例3--判断结果集中的值，如果值为M，输出男，否则为女，同时列名为性别*/
SELECT name,
    CASE
    WHEN gender='M' THEN '男'
    ELSE '女'
    END AS 性别
FROM students;

5.2 ORDER BY排序

SQL ORDER BY子句根据指定的标准按升序或降序对结果集进行排序。

--语法格式
SELECT
    column1, column2
FROM
    table_name
ORDER BY column1 ASC ,  --默认升序
         column2 DESC;  --降序
/*在此语法中，ORDER BY子句放在FROM子句之后。 如果SELECT语句包含WHERE子句，则ORDER BY子句必须放在WHERE子句之后。
要指定要排序的列以及排序顺序的类型：
1. 升序(使用：ASC表示，默认是升序)
2. 降序(使用：DESC表示)*/

/*示例--班级id按人数多少升序排列*/
select class_id,count(*) AS "人数"
    FROM students
    GROUP BY class_id
    HAVING count(*)>=1
    ORDER BY count(*);

5.3 DISTINCT运算符

使用SQL DISTINCT运算符从结果集中删除重复数据项。

--语法格式
SELECT DISTINCT
    column1,column2......
FROM
    table1;
/*DISTINCT运算符将所有NULL值视为相同的值。因此在结果集中，DISTINCT运算符只保留一个NULL值，并从结果集中删除其它的NULL值。*/

/*示例*/
SELECT DISTINCT
    job_id,
    salary
FROM
    employees
ORDER BY
    job_id,
    salary DESC;

5.4 LIMIT子句

使用SQL LIMIT子句来限制SELECT语句返回的行数。

--语法格式
--使用LIMIT和OFFSET子句。
LIMIT {[offset,] row_count | row_count OFFSET offset}
/*1. row_count用于返回数据的行数。
2. OFFSET可选项，默认为0。用于指定返回数据的第一行在SELECT语句结果集中的偏移量，其必须是非负的整数常量。
3.row_count OFFSET offset:从第offset+1行开始，取row_count行。*/

/*示例--跳过三行并获取接下来的五行,即显示4-8行*/
SELECT
    employee_id, first_name, last_name
FROM
    employees
ORDER BY first_name
LIMIT 5 OFFSET 3;
/*LIMIT 5 OFFSET 3等同于LIMIT 3,5*/

/*示例2-获取薪水最高的前5名*/
SELECT
    employee_id, first_name, last_name, salary
FROM
    employees
ORDER BY salary DESC
LIMIT 5;

/*示例3-获取薪水排名第三高的人名*/
/*通过嵌套子查询，先查出第三高的是多少salary，然后查name*/
SELECT
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary = (SELECT DISTINCT
            salary
        FROM
            employees
        ORDER BY salary DESC
        LIMIT 2 , 1);

5.5 ALL,ANY,SOME语句

SQL ALL与集合中所有元素比较；ANY和SOME与集合中的某些元素比较。

/*示例1--找出所有 工资至少比生物系一个教师的工资要高 的教师*/
SELECT name
FROM instructor
WHERE salary > SOME(SELECT salary
                    FROM instructor
                    WHERE dept_name='Biology');

/*示例2--找出平均工资最高的系*/
SELECT dept_name
FROM instructor
GROUP BY dept_name
having avg(salary) >= ALL(SELECT avg(salary)
                          FROM instructor
                          GROUP BY dept_name);

5.6 BETWEEN | IN | LIKE | NULL

5.6.1 BETWEEN

SQL BETWEEN运算符选择指定范围内的值。

--语法格式
expression BETWEEN low AND high;
expression NOT BETWEEN low AND high;

/*示例*/
SELECT
    employee_id, first_name, last_name, hire_date
FROM
    employees
WHERE
    hire_date BETWEEN '1999-01-01' AND '2000-12-31'
ORDER BY hire_date;

/*1. BETWEEN运算符需要低值和高值。如果低值大于高值，将得到一个空的结果集。
2. 2000-12-31默认为2000-12-31  00:00:00.000000*/

5.6.2 IN

SQL IN运算符将列中的值与括号内的一组值进行比较。
要比较连续的值可用LIMIT，比如5-100。

--语法格式
expression IN (value1,value2,...)
expression NOT IN (value1, value2,...)

/*示例--查找工作ID不是8,9或10的所有员工*/
SELECT
    employee_id, first_name, last_name, job_id
FROM
    employees
WHERE
    job_id NOT IN (8, 9, 10)
ORDER BY
    job_id;

5.6.3 LIKE

SQL LIKE运算符来测试表达式是否与模式匹配。

要构造模式，请使用两个SQL通配符：

% 百分号匹配零个，一个或多个字符。
_ 下划线符号匹配单个字符

下表说明了一些模式及其含义：

模式	含义
LIKE ‘Yii%’	匹配以Yii开始的字符串
LIKE ‘%su’	匹配以su结尾的字符串
LIKE ‘%ch%	匹配包含ch的字符串
LIKE ‘Luc_’	以Luc开始，后面只有一个字符，例如：Lucy，LucC等
LIKE ‘_cy’	以cy结尾，前面只有一个字符，例如：Lcy，ucy等
LIKE ‘%yiiBai_’	包含yiiBai，以任意数量的字符开头，最多以一个字符结尾。
LIKE ‘_yiiBai%’	包含yiiBai，最多以一个字符开头，以任意数量的字符结尾。

--语法格式
expression LIKE pattern

/*如果要匹配通配符%或_，则必须使用反斜杠字符\来对其进行转义。 如果要使用其它的转义字符而不是反斜杠，可以在LIKE表达式中使用ESCAPE子句，如下所示：*/
expression LIKE pattern ESCAPE escape_character

/*示例--要查找名字以M开头但不以Ma开头的所有员工，请使用以下语句：*/
SELECT
    employee_id, first_name, last_name
FROM
    employees
WHERE
    first_name LIKE 'M%'
AND first_name NOT LIKE 'Ma%'
ORDER BY
    first_name;

5.6.4 NULL

使用SQL IS NULL和IS NOT NULL运算符来测试表达式是否为NULL。

NULL表示数据未知的值，在数据库中缺少数据。

--语法格式
expression IS NULL;

/*示例--查找具有电话号码的所有员工，请使用IS NOT NULL，如以下语句所示：*/
SELECT
    employee_id,
    first_name,
    last_name,
    phone_number
FROM
    employees
WHERE
    phone_number IS NOT NULL;

注意：

不能使用比较运算符的等于(=)将值与NULL值进行比较
NULL值是特殊的，任何与NULL值的比较都不会返回true或false，而是返回未知

5.7 SQL别名

SQL别名，包括表和列别名，使查询更短，更易理解。

使用关键词AS，可省略。

5.7.1 列别名

/*示例*/
SELECT
    inv_no AS invoice_no,
    amount,
    due_date AS '截止日期',
    cust_no '客户编号'
FROM
    invoices;
/*别名包含空格，必须使用单引号(')或双引号(")来包围别名。*/

5.7.2 表别名

--语法格式
SELECT
    d.department_name
FROM
    departments AS d

/*示例一--以下查询使用inner join子句从employees和departments表中选择数据。*/
SELECT
    employee_id,
    first_name,
    last_name,
    e.department_id,
    department_name
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
ORDER BY
    first_name;

/*示例二--以下查询使用self-join将employee表自联接。*/
SELECT
    e.first_name AS employee,
    m.first_name AS manager
FROM
    employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id
ORDER BY
    manager;

5.8 JOIN操作

SELECT语句不仅能从单个表中查询数据，而且可以将多个表链接在一起。连接表的过程称为Join。

SQL提供了多种连接，如内连接，左连接，右连接，全外连接等。

5.8.1 INNER JOIN

SQL INNER JOIN内连接子句来查询来自两个或多个表的数据。

内连接子句消除了与另一个表的行不匹配的行，相当于交集。

--语法格式
SELECT
  A.n
FROM A
INNER JOIN B ON B.n = A.n

/*示例--employees表中的department_id列是将员工链接到departments表的外键列。*/
SELECT
    first_name,
    last_name,
    employees.department_id,
    departments.department_id,
    department_name
FROM
    employees
        INNER JOIN
    departments ON departments.department_id = employees.department_id
WHERE
    employees.department_id IN (1, 2, 3);

--语法格式
/*INNER JOIN子句可以连接三个或更多表，只要它们具有关系，通常是外键关系。*/
SELECT
  A.n
FROM A
INNER JOIN B ON B.n = A.n
INNER JOIN C ON C.n = A.n;

/*示例--使用内部联接子句连接3个表：员工，部门和工作岗位，以获取在部门ID为：1,2和3中工作的员工的信息。*/
SELECT
    first_name, last_name, job_title, department_name
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
INNER JOIN jobs j ON j.job_id = e.job_id
WHERE
    e.department_id IN (1, 2, 3);

5.8.2 LEFT JOIN

左连接将返回左表中的所有行，而不管右表中是否存在匹配的行，相当于AB交集后显示A。

--语法格式
SELECT
    A.n
FROM
    A
LEFT JOIN B ON B.n = A.n;

/*示例*/
SELECT
    c.country_name, c.country_id, l.country_id, l.street_address, l.city
FROM
    countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
    c.country_id IN ('US', 'UK', 'CN')
/*右表中的非匹配行使用NULL值填充*/

5.8.3 FULL OUTER JOIN

完全外连接是左连接和右连接的组合。完整外连接包括连接表中的所有行，相当于并集。

如果连接表中的行不匹配，则使用NULL值填充。

--语法格式
SELECT column_list
FROM A
FULL OUTER JOIN B ON B.n = A.n;

/*示例--查找不存储任何水果的空篮子*/
SELECT
    basket_name,
    fruit_name
FROM
    fruits
FULL OUTER JOIN baskets ON baskets.basket_id = fruits.basket_id
WHERE
    fruit_name IS NULL;

5.8.4 Cross Join

SQL Cross Join交叉连接是一种连接操作，它生成两个或多个表的笛卡尔积。

--语法格式
SELECT column_list
FROM table_A
CROSS JOIN table_B;
/*在SQL中，假设A表有n行，而B表有m行，那么A和B表的交叉连接结果有n x m行*/

/*以下语句等同于使用上面的CROSS JOIN子句的语句*/
SELECT
    column_list
FROM
    table_A,table_B;

5.8.5 自连接

SQL自连接技术将表连接到自身。

我们将一张表连接到自身来评估同一个表中其他行的行。要执行自联接，我们使用内连接或左连接子句。

因为同一张表在单个查询中出现两次，所以必须使用表别名。

--语法格式
SELECT
    column1,
    column2,
    column3,
        ...
FROM
    table1 A
INNER JOIN table1 B ON B.column1 = A.column2;
/*在此语句中，使用INNER JOIN子句将table1连接到自身。 A和B是table1的表别名。 B.column1 = A.column2是连接条件。*/

/*示例一--查询每位员工的上级经理信息。*/
SELECT
    CONCAT(e.first_name, ' ', e.last_name) as employee,
    CONCAT(m.first_name, ' ', m.last_name) as manager
FROM
    employees e
        INNER JOIN
    employees m ON m.employee_id = e.manager_id
ORDER BY manager;

/*示例二--查询每位员工的上级经理信息。包含最高领导。*/
SELECT
    CONCAT(e.first_name, ' ', e.last_name) as employee,
    CONCAT(m.first_name, ' ', m.last_name) as manager
FROM
    employees e
        LEFT JOIN
    employees m ON m.employee_id = e.manager_id
ORDER BY manager;

六、聚合函数

SQL聚合函数计算一组值并返回单个值。

因为聚合函数对一组值进行操作，所以它通常与SELECT语句的GROUP BY子句一起使用。GROUP BY子句将结果集划分为值分组，聚合函数为每个分组返回单个值。

以下是常用的SQL聚合函数：

AVG() - 返回集合的平均值。
COUNT() - 返回集合中的项目数。
MAX() - 返回集合中的最大值。
MIN() - 返回集合中的最小值
SUM() - 返回集合中所有或不同值的总和。

6.1 AVG （平均）

AVG()函数返回集合中的平均值

--语法格式
AVG([ALL|DISTINCT] expression)
/*ALL关键字计算所有值的平均值，而DISTINCT关键字强制函数仅对不同的值进行操作。默认情况下，使用ALL选项*/

/*示例1*/
SELECT
    AVG(salary)
FROM
    employees;
SELECT
    ROUND(AVG(DISTINCT salary), 2)
FROM
    employees;
--使用ROUND函数，结果舍入为2位小数

/*示例2 ： 与分组一起使用*/
SELECT
    department_id,
    AVG(salary)
FROM
    employees
GROUP BY
    department_id;

/*示例3：与JOIN一起使用*/
SELECT
    e.department_id,
    department_name,
    AVG(salary)
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
    e.department_id;

/*示例4 ： 与HAVING一起使用*/
SELECT
    e.department_id,
    department_name,
    AVG(salary) AS avgsalary
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
    e.department_id
HAVING avgsalary<5000
ORDER BY
    AVG(salary) DESC;

/*示例5： 子查询*/
SELECT
    AVG(employee_sal_avg)
FROM
    (
        SELECT
            AVG(salary) employee_sal_avg
        FROM
            employees
        GROUP BY
            department_id
    ) t;

6.2 COUNT（统计）

SQL COUNT函数来获取组中的项目数。它返回符合条件行数。

--语法格式
COUNT ([ALL | DISTINCT] expression);
/*COUNT(*)函数返回表中的行数，包括包含NULL值的行。*/

/*示例1 ： 与GROUP BY一起使用*/
SELECT
    e.department_id,
    department_name,
    COUNT(*)
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
    e.department_id;

/*示例2 ：与HAVING一起使用 */
/*要按COUNT(*)函数的结果过滤分组，需要在COUNT(*)函数使用HAVING子句*/
SELECT
    e.department_id,
    department_name,
    COUNT(*)
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
    e.department_id
HAVING
    COUNT(*) > 5
ORDER BY
    COUNT(*) DESC;

6.3 SUM（求和）

--语法格式
SUM([ALL|DISTINCT] expression)
/*只能将SUM函数应用于数字列，SUM函数忽略NULL值。*/

/*示例1 ：与HAVING 以及 GROUP BY一起使用*/
SELECT
    e.department_id,
    department_name,
    SUM(salary)
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
    e.department_id
HAVING
    SUM(salary) > 30000
ORDER BY
    SUM(salary) DESC;

6.4 MAX & MIN

SQL Max函数查找组中的最大值，Min函数查找组中的最小值。。

--语法格式
MAX(expression)
MIN(expression)

/*示例1 ： 用于子查询中*/
SELECT
    employee_id,
    first_name,
    last_name,
    salary
FROM
    employees
WHERE
    salary = (
        SELECT
            MAX(salary)
        FROM
            employees
    );

/*示例2 ： 返回每个部门中员工的最高工资，并根据最高工资对结果集进行排序。*/
SELECT
    d.department_id,
    department_name,
    MAX(salary)
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
    e.department_id
ORDER BY
    MAX(salary) DESC;

/*示例3 ： 获得具有最高薪水大于12000的员工的部门*/
SELECT
    d.department_id,
    department_name,
    MAX(salary)
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
    e.department_id
HAVING
    MAX(salary) > 12000;

6.6 Group By子句

GROUP BY子句是SELECT语句的可选子句，它根据指定列中的匹配值将行组合成组，每组返回一行。

--语法格式
[GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
/*1. col_name:指定用于分组的选择列，可指定多列，彼此间用逗号分隔。
2.expr:指定用于分组的表达式
3.position:指定用于分组的选择列在SELECT语句结果集中的位置，通常是一个正整数。
4.ASC | DESC:升降序排列，默认ASC升序。
5.WITH ROLLUP:用于指定在结果集中不仅包含由GROUP BY子句分组后的数据行，还包括各分组的汇总行，以及所有分组的整体汇总行。*/

/*示例--结果集包含相同地址的男性客户人数、女性客户人数、总人数以及客户的总人数*/
SELECT cust_address,cust_sex,COUNT(*) AS "人数"
    FROM customers
    GROUP BY cust_address,cust_sex
    WITH ROLLUP;

6.6 HAVING

SQL HAVING子句，该子句用于为GROUP BY子句汇总的组指定条件，一般搭配GROUP BY子句使用。

--语法格式
SELECT
    column1,
    column2,
    AGGREGATE_FUNCTION (column3)
FROM
    table1
GROUP BY
    column1,
    column2
HAVING
    group_condition;
/*注：需要注意的是，在GROUP BY子句之前应用WHERE子句之后应用HAVING子句之前*/

/*示例--统计人数大于1的班级*/
SELECT class_id,count(*) AS "人数"
    FROM students
    GROUP BY class_id
    HAVING count(*)>1;

七、高级查询

7.1 GROUPING SETS运算符

使用SQL GROUPING SETS运算符生成多个分组集

分组集是一组使用GROUP BY子句进行分组的列。通常，单个聚合查询定义单个分组集

--语法格式
SELECT
    c1,
    c2,
    aggregate (c3)
FROM
    table
GROUP BY
    GROUPING SETS (
        (c1, c2),
        (c1),
        (c2),
        ()
);

7.2 ROLLUP运算符

ROLLUP是GROUP BY子句的扩展。ROLLUP选项允许包含表示小计的额外行，通常称为超级聚合行，以及总计行。

--语法格式
SELECT
    c1, c2, aggregate_function(c3)
FROM
    table
GROUP BY ROLLUP (c1, c2);
/*ROLLUP假定输入列之间存在层次结构。 例如，如果输入列是(c1，c2)，则层次结构c1> c2。
ROLLUP生成考虑此层次结构有意义的所有分组集。 这就是为什么我们经常使用ROLLUP来生成小计和总计以用于报告目的*/

/*示例1*/
SELECT
    COALESCE(warehouse, 'All warehouses') AS warehouse,
    SUM(quantity)
FROM
    inventory
GROUP BY ROLLUP (warehouse);

/*示例2*/
SELECT
    warehouse, product, SUM(quantity)
FROM
    inventory
GROUP BY warehouse, ROLLUP (product);

7.3 UNION运算符 (并集)

使用SQL UNION组合来自多个查询的两个或多个结果集，UNION运算符将两个或多个SELECT语句的结果集合并到一个结果集中,相当于并集。

--语法格式
SELECT
    column1, column2
FROM
    table1
UNION [ALL]
SELECT
    column3, column4
FROM
    table2;
/*带ALL参数不去重，不带ALL则去重*/

7.4 INTERSECT(交)

SQL INTERSECT运算符，来获取两个或多个查询的交集。

--语法格式
SELECT
    id
FROM
    a
INTERSECT
SELECT
    id
FROM
    b;

要使用INTERSECT运算符，SELECT语句的列需要遵循以下规则：

列的数据类型必须兼容。
SELECT语句中的列数及其顺序必须相同

7.5 MINUS（差）

SQL MINUS运算符从另一个结果集中减去一个结果集。

--语法格式
SELECT
    id
FROM
    A
MINUS
SELECT
    id
FROM
    B;

/*示例*/
SELECT
    employee_id
FROM
    employees
MINUS
SELECT
    employee_id
FROM
    dependents
ORDER BY employee_id;

7.6 子查询

/*示例1*/
SELECT
    employee_id, first_name, last_name
FROM
    employees
WHERE
    department_id NOT IN (SELECT
            department_id
        FROM
            departments
        WHERE
            location_id = 1700)
ORDER BY first_name , last_name;

/*示例2*/
SELECT
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary > (SELECT
            AVG(salary)
        FROM
            employees);

7.7 EXISTS运算符

EXISTS运算符用于指定子查询以测试行的存在。如果子查询包含任何行，则返回true。否则，它返回false。

--语法格式
EXISTS (subquery)
NOT EXISTS (subquery)

/*示例--查找没有任何家属的员工*/
SELECT
    department_name
FROM
    departments d
WHERE
    NOT EXISTS( SELECT
            1
        FROM
            employees e
        WHERE
            salary > 10000
                AND e.department_id = d.department_id)
ORDER BY department_name;

八、约束

完整性约束保证授权用户对数据库所做的修改不会破坏数据的一致性。

8.1 实体完整性-主键约束

每个表都有一个且只有一个主键。主键不接受NULL或重复值。
如果主键由两列或更多列组成，则值可能在一列中重复，但主键中所有列的值组合必须是唯一的。
复合主键不能包含不必要的多余列。

/*示例1 ： 主键由多列组成，用PRIMARY KEY定义主键*/
CREATE TABLE project_assignments (
    project_id INT,
    employee_id INT,
    join_date DATETIME NOT NULL,
    CONSTRAINT pk_assgn PRIMARY KEY (project_id , employee_id)
);

/*示例2-- 使用ALTER TABLE语句添加主键，将milestone_id列设置为主键。*/
ALTER TABLE project_milestones
ADD PRIMARY KEY (milestone_id);

/*示例3-- 删除project_milestones表的主键约束*/
ALTER TABLE project_milestones
DROP CONSTRAINT pk_milestone_id;

8.2 实体完整性-唯一约束

使用SQL UNIQUE约束强制列或一组列中值的唯一性

UNIQUE约束定义了一个规则，该规则可防止存储在不参与主键的特定列中有重复值

UNIQUE约束和PRIMARY KEY约束之间的区别：
比较项|PRIMARY KEY约束|UNIQUE约束
-|-|-
约束的数量|一个|多个
NULL值|不允许|允许

/*示例1--创建了UNIQUE约束作为列约束。*/
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL
);

/*示例2--表约束语法创建的UNIQUE约束*/
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    CONSTRAINT uc_username UNIQUE (username)
);

/*示例3： 将UNIQUE约束添加到现有表*/
ALTER TABLE users
ADD CONSTRAINT uc_username UNIQUE(username);

ALTER TABLE users
ADD new_column data_type UNIQUE;

/*示例4： 删除UNIQUE约束*/
ALTER TABLE table_name
DROP CONSTRAINT unique_constraint_name;

8.3 参照完整性-外键约束

SQL外键FOREIGN KEY约束以强制表之间的关系。

定义外码的主要目的是：让系统做参照完整性约束的检查。

参照关系中外码的值必须在被参照关系中实际存在或为null。

--语法格式
--默认地，外码参照被参照关系中的主码，即下面FOREIGN KEY后面是外键，REFERENCES后面列是主键
[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]
--其中reference_option的语法格式如下:
RESTRICT | CASCADE | SET NULL | NO ACTION

/*
相关语法说明如下：

1. CONSTRAINT [symbol]：可以为外键约束指定名称。
2. tbl_name：指定外键所参照的表名，这个表称为被参照表，而外键所在表称为参照表。
3. index_col_name：指定被参照的列名。
4. ON DELETE/UPDATE：指定参照动作所对应的DELETE/UPDATE语句
5. reference_option：指定参照完整性约束的实现策略。RESTRICT是限制策略，同时也是默认策略，CASCADE是级联策略，SET NULL是置空策略，NO ACTION表示不采取实施策略。
6. RESTRICT限制策略：当要删除或更新被参照表中被参照列上在外键中出现的值时，拒绝对被参照表的删除或更新操作。默认策略。
7. CASCADE级联策略：即从被参照表中删除或更新记录行时，自动删除或更新参照表中匹配的记录行。
8. SET NULL置空策略：即在被参照表中操作时，设置参照表中与之对应的外键列值为NULL。
9. NO ACTION不采取实施策略：语义动作同RESTRICT限制策略。
*/

/*示例1--创建表时创建FOREIGN KEY约束*/
CREATE TABLE project_milestones (
    milestone_id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT,
    milestone_name VARCHAR(100),
    FOREIGN KEY (project_id)
        REFERENCES projects (project_id)
);
--FOREIGN KEY子句将project_milestones表的project_id设置为引用project表的project_id列的外键。

/*示例2--可以为FOREIGN KEY约束指定名称*/
CREATE TABLE project_milestones (
    milestone_id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT,
    milestone_name VARCHAR(100),
    CONSTRAINT fk_project FOREIGN KEY (project_id)
        REFERENCES projects (project_id)
);
--fk_project是FOREIGN KEY约束的名称。

/*示例3*/
CREATE TABLE orders
(
    order_id INT NOT NULL AUTO_INCREMENT,
    order_product CHAR(50) NOT NULL,
    order_product_type CHAR(50) NOT NULL,
    cust_id INT NOT NULL,
    order_date DATETIME NOT NULL,
    order_price DOUBLE NOT NULL,
    order_amount INT NOT NULL,
    PRIMARY KEY(order_id),
    FOREIGN KEY(cust_id)
        REFERENCES customers(cust_id)
            ON DELETE RESTRICT
            ON UPDATE RESTRICT
);
/*该外键参照完整性约束确保：插入表orders中的每一个订购客户id号都执行一次检测，查看这个订购客户id号是否已经出现在表customers的客户id号（主键）中，若没有，数据无法正常插入。*/

/*示例4--使用ALTER来向现有表中添加FOREIGN KEY约束*/
ALTER TABLE table_1
ADD CONSTRAINT fk_name FOREIGN KEY (fk_key_column)
   REFERENCES table_2(pk_key_column)

/*示例5--删除外键约束*/
ALTER TABLE table_name
DROP CONSTRAINT fk_name;

8.4 用户定义完整性-NOT NULL

NOT NULL约束是一个列约束，它定义将列限制为仅具有非NULL值的规则。

--语法格式
CREATE TABLE table_name(
   ...
   column_name data_type NOT NULL,
   ...
);

/*示例1*/
CREATE TABLE training (
    employee_id INT,
    course_id INT,
    taken_date DATE NOT NULL,
    PRIMARY KEY (employee_id , course_id)
);

/*示例2--ALTER TABLE NOT NULL语句*/
ALTER TABLE training
MODIFY taken_date date NOT NULL;

8.5 用户定义完整性-CHECK约束

CHECK约束是SQL中的完整性约束，它允许指定列或列集中的值必须满足布尔表达式

--语法格式
--CHECK约束由关键字CHECK后跟括号中的布尔表达式组成：
CHECK(Boolean_expression)
--如果要为CHECK约束指定名称，请使用以下语法：
CONSTRAINT constraint_name CHECK(Boolean_expression)

/*示例1--其products_price列中的值必须为正数*/
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    selling_price NUMERIC(10,2) CHECK (selling_price > 0)
);

/*示例2--分配CHECK约束名称*/
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    selling_price NUMERIC(10,2) CONSTRAINT positive_selling_price CHECK (selling_price > 0)
);

/*示例3： 涉及多个列 （表约束）*/
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR (255) NOT NULL,
    selling_price NUMERIC (10, 2) CHECK (selling_price > 0),
    cost NUMERIC (10, 2) CHECK (cost > 0),
    CONSTRAINT valid_selling_price  CHECK (selling_price > cost)
);

8.6 域约束

create domain域约束是完整性约束的最基本形式，可用于检测插入到数据库中的数据的合法性

--从现有数据类型可以创建新的域
create domain Dollars as numeric(12，2) not null
create domain Pounds as numeric(12，2);
create table instructor
    (ID char(5) primary key,
    name varchar(20),
    dept name varchar(20),
    salary Dollars,
    comm Pounds
    );

九、视图与索引

9.1 视图

在某些情况下，让所有用户看到整个逻辑模型是不合适的，视图就提供了这种机制：向用户隐藏特定的数据。

SQL允许通过查询来定义“虚关系”，它在概念上包含查询的结果，但并不预先计算并存储。像这种作为虚关系对用户可见的关系称为视图（view）。

9.1.1 创建视图

--语法格式
CREATE [OR REPLACE] VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]
/*1. view_name:指定视图的名称，且必须唯一。不能重名。
2. column_list:可选项，用于为视图中每个列指定明确的名称，必须与select的列数相同，逗号分隔。
3.select_statement:指定创建视图的SELECT语句。
4.WITH CHECK OPTION:可选项，用于指定在可更新视图上所进行的修改都需要符合select_statement中所指定的限制条件，这样可以确保数据修改后，仍可以通过视图看到修改后的数据。
5.CASCADED | LOCAL:决定检查测试的范围。默认CASCADED，它会对所有视图进行检查。而LOCAL只对定义的视图进行检查。*/

/*示例--视图physics_fall_2009_Watson，列出于2009年秋季学期在Watson大楼开设的所有Physics课程的标识和教室号*/
create view physics_fall_2009_watson as
select course_id, room_number
from physics_fall_2009
where building='Watson';

/*示例2--创建视图customers_view,要求包含客户信息表中所有男性客户，并要求保证今后对该视图数据的修改都必须符和客户性别为男性这个条件。*/
CREATE OR REPLACE VIEW mysql_test.customers_view
    AS
    SELECT * FROM mysql_test.customers
        WHERE cust_sex='M'
    WITH CHECK OPTION;

9.1.2 删除视图

--语法格式
DROP VIEW [IF EXISTS]
    view_name [, view_name] ...
    [RESTRICT | CASCADE]

9.1.3 更新视图

--语法格式
ALTER VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]
/*语法与创建视图语法相似，也可以通过先删除再创建来实现修改视图*/

9.1.4 查看视图定义

1 2	`--语法格式 SHOW CREATE VIEW view_name`

9.1.5 更新视图数据

一般地，如果定义视图的查询能满足下列条件，我们称SQL视图是可更新的（updatable），即视图上可以执行插入、更新或删除

from子句中只有一个数据库关系
select子句中只包含关系的属性名，不包含任何表达式、聚集或distinct声明
任何没有出现在select子句中的属性可以取空值；即这些属性上没有not null约束，也不构成主码的一部分
查询中不含有group by或having子句

/*示例1--通过INSERT语句通过视图向基本表插入数据*/
--假设我们向视图faculty插入一条新元组，可写为：
insert into faculty values(‘30765'，‘Green'，‘Music');
/*该语句能够成功执行，是因为创建视图时添加了WITH CHECK OPTION*/

/*示例2--使用UPDATE语句通过视图修改基本表的数据*/
UPDATE mysql_test.customers_view
    SET cust_address='上海市';

/*示例3--使用DELETE语句通过视图删除基本表的数据*/
DELETE FROM mysql_test.customers_view
    WHERE cust_name='周明';

9.2 索引

9.2.1 索引的创建

我们用create index命令，为关系中的某些属性创建索引。索引，就是DBMS根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，因而索引实质上是一张描述索引列的列值与原表中的记录行之间一对应关系的有序表。

更新表的时候索引会被自动更新，因此索引提高查询速度，降低更新速度。

9.2.1.1 使用CREATE INDEX语句

--语法格式全面
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] ...

--语法格式常用
CREATE [UNIQUE] INDEX index_name ON table_name (index_col_name,...);
/*其中index_col_name的格式为:
    col_name [(length)] [ASC | DESC]
可选项length指定前length个字符创建索引，可减少索引文件大小。
关键字[ASC | DESC]指定索引升序还是降序排列，默认ASC*/

/*示例--在students表上用学生name前三个字符创建名为index_students的索引*/
CREATE INDEX index_students ON students(name(3) ASC);

--我们用`create unique index`命令，为关系中的某些属性创建唯一索引
CREATE UNIQUE INDEX uni_stu_index ON student(ID,name);

9.2.1.2 使用CREATE TABLE语句

--语法格式
--在create table后面添加以下语法成分
{INDEX | KEY} [index_name] (index_col_name,...)
/*关键字KEY是关键字INDEX的同义词*/

/*示例*/
CREATE TABLE seller
    (
    seller_id INT NOT NULL AUTO_INCREMENT,
    seller_name char(50) NOT NULL,
    product_type int(5) NULL,
    sales INT NULL,
    PRIMARY KEY(seller_id,product_type),
    KEY index_seller(sales)
    );

9.2.1.3 使用ALTER TABLE语句

--语法格式
--在alter table后面添加以下语法成分
ADD {INDEX | KEY} [index_name] (index_col_name,...)

/*示例*/
ALTER TABLE seller
    ADD INDEX index_seller_name (seller_name);

9.2.2 索引的查看

--语法格式
SHOW {INDEX | INDEXES | KEYS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [WHERE expr]

/*示例*/
SHOW KEYS IN seller;

9.2.3 索引的删除

9.2.3.1 使用DROP INDEX语句

--语法格式
DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name

/*示例*/
DROP INDEX index_seller_name ON seller;

9.2.3.2 使用ALTER TABLE语句

在ALTER TABLE语句下面添加以下一项。

--语法格式
DROP PRIMARY KEY;
DROP INDEX index_name;
/*删除主键也就是删除索引，主键也是一个索引*/

/*示例*/
ALTER TABLE seller
    DROP PRIMARY KEY,
    DROP INDEX index_seller;

十、断言及触发器

10.1 断言

断言（assertion）是表达要求数据库永远满足的条件的谓词（复杂check条件）

SQL中的断言形式如下：

1	`create assertion <assertion_name> check <predicate>`

创建了某断言之后，系统将检查它的合法性，并对每一个可能破坏该断言的数据库更新进行检测
这种检测会产生大量的开销，因此断言的使用应非常谨慎

--例2，每位教师不能在同一个学期的同一个时间段在两个不同的教室授
create assertion ins_teaches_constraint check not exists
(select ID，name,section_id,semester,year,time_slot_id,
    count(distinct building,room,number)
from instructor natural join teaches natural join section
group by (ID,name,section_id,semester,year,time_slot_id)
having count(building,room_number)>1)

10.2 触发器trigger

触发器（trigger）是由数据库更新操作引起的被系统自动执行的语句。

设计触发器必须：

指明触发器被执行的条件
指明触发器执行时所做的具体操作

1）创建触发器

--语法格式
CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_body
/*1. trigger_name：指定触发器名称，名称唯一。
2. trigger_time：指定触发器被触发的时刻。有2个选项，BEFORE和AFTER。
3. trigger_event：指定触发事件。可以是关键字INSERT,UPDATE,DELETE。
4. FOR EACH ROW：指定对于受触发事件影响的每一行都要激活触发器的动作。
5. trigger_body：指定触发器动作主体，即MYSQL语句块。
6. 每个表每个事件只允许一个触发器，即INSERT,UPDATE,DELETE的“之前”、“之后”，共最多支持6个触发器。
7. INSERT触发器代码内，可引用一个名为NEW的虚拟表，来访问被插入的行。
8. DELETE触发器代码内，可引用一个名为OLD的虚拟表，来访问被删除的行。
9. UPDATE触发器代码内，可引用名为NEW的虚拟表，来访问新更新的值，OLD访问以前的值。
*/

/*例1--INSERT触发器--每次插入数据时，设置用户变量str的值为新插入客户的id*/
CREATE TRIGGER mysql_test.customers_insert_trigger AFTER INSERT
    ON mysql_test.customers FOR EACH ROW SET @str=NEW.cust_id;

/*例2--UPDATE触发器--每次更新表时，将表中cust_address列的值设置为cust_contact列的值*/
CREATE TRIGGER mysql_test.customers_update_trigger BEFORE UPDATE
    ON mysql_test.customers FOR EACH ROW
    SET NEW.cust_address=OLD.cust_contact;

/*例3--使用触发器来确保关系section中属性time_slot_id的参照完整性*/
create trigger timeslot_check1 after insert
        ON section
        referencing new row as nrow
        for each row
        when (nrow.time_slot_id not in
            (select time_slot_id from
                time_slot))/*time_slot中不存在该time_slot_id*/
begin rollback end;

/*例4--某种物品库存量小到一定程度就发订货单或打开报警灯*/
create trigger reorder_trigger after update of level on inventory
    referencing old row as orow, new row as nrow
    for each row
    when nrow.level<=/*保证修改后库存量低于最小库存量,更新前库存大于最小库存*/
            (select level/*目的是只更新一次*/
            from minlevel
            where minlevel.item=nrow.item) and orow.level>
                    (select level
                    from minlevel
                    where minlevel.item= orow.item)
begin
    insert into ordersbegin
    insert into orders
            (select item, amount
             from reorder
             where reorder.item= orow.item)
end

2）删除触发器

--语法格式
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
/*1. schema_name.：用于指定触发器所在数据库名称
2. 当删除一个表时，自动删除该表上的触发器。且触发器无法修改，只能删除再创建的方式来修改。*/

/*示例*/
DROP TRIGGER IF EXISTS mysql_test.customers_insert_trigger;

十一、安全性

11.1 用户账号管理

11.1.1 创建账户

--语法格式
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
/*1. user：指定创建用户账号，格式为'user_name'@'host_name'。其中host_name表示主机名,主机名默认为'%'。
1. IDENTIFIED BY:可选项，指定用户账号对应口令。
2. PASSWORD:可选项，用于指定散列口令，若需要使用明文设置口令，需忽略PASSWORD关键字。
3. 'password'：指定用户账号的口令。
4. 创建的账户权限很少，只允许进行不需要权限的操作。*/

/*示例--添加2个新用户，用户名为zhangsan和lisi，主机名localhost，张三明文口令123，李四口令为456对应PASSWORD()函数返回的散列值*/
SELECT PASSWORD(456);
>>*531E182E2F72080AB0740FE2F2D689DBE0146E04
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123',
            'lisi'@'localhost' IDENTIFIED BY PASSWORD '*531E182E2F72080AB0740FE2F2D689DBE0146E04';

11.1.2 删除账户

--语法格式
DROP USER [IF EXISTS] user [, user] ...

/*示例*/
DROP USER lisi@localhost;
/*用户删除不会影响他们创建的数据库对象，MYSQL并不记录是谁创建的。*/

11.1.3 修改用户账号

--语法格式
RENAME USER old_user TO new_user
    [, old_user TO new_user] ...

/*示例*/
RENAME USER 'zhangsan'@'localhost' TO 'wangwu'@'127.0.0.1';

11.1.4 修改用户口令

--语法格式
SET PASSWORD [FOR user] =
    {
        PASSWORD('cleartext password')
      | 'encrypted password'
    }
/*1. FOR user：格式为'user_name'@'host_name'，不加上的话默认当前账户。
2. 只能用PASSWORD('cleartext password')或'encrypted password'中一项，且必须一项。
3. cleartext password是明文，encrypted password是加密密码*/

/*示例*/
SET PASSWORD FOR 'bob'@'%.example.org' = PASSWORD('cleartext password');

11.2 用户权限管理

新创建的账户没有访问权限，不能执行任何数据库操作。

1 2	`--查看用户权限 SHOW GRANTS FOR 'user_name'@'host_name';`

11.2.1 权限的授予

--语法格式
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [WITH with_option ...]
/*1. priv_type：用于指定权限的名称；
2. column_list：用于指定权限要授予给表中哪些具体的列；
3. ON: 用于指定权限授予的对象和级别；
4. object_type：可选项，指定权限授予的对象类型；
5. priv_level：指定权限的级别，可以授予的权限有：列权限、表权限、数据库权限和用户权限；'*'表示当前数据库中所有表，'*.*'表示所有数据库中所有表，'db_name.routine_name'表示某个数据库中某个存储程序或函数；
6. TO：用来设定用户口令，以及指定被授予权限的用户user。GRANT 语句也可以用来创建用户账户；
7. user_specification：语法格式为`user [IDENTIFIED BY [PASSWORD] 'password']`;
8. WITH with_option:用于权限转移；
*/

其中priv_type可有以下选择:

select、insert、update、delete：允许读关系，或查询视图、插入元组、修改元组、删除元组
references：创建关系时允许声明外键
CREATE、ALTER、DROP: 允许创建表、修改表、删除表权限
INDEX：允许定义索引权限
CREATE ROUTINE、ALTER ROUTINE、EXECUTE ROUTINE：允许创建、更新/删除、调用特定数据库的存储过程和存储函数的权限
CREATE USER、SHOW DATABASES:允许创建或删除新用户、查看已有数据库的定义的权限
all privileges：所有权限
with grant option：允许用户把被授予的权限再转授给其他用户

/*示例1--授予张三再customers上拥有列cust_id和name的select权限*/
GRANT SELECT (cust_id,cust_name)
    ON mysql_test.customers
    TO 'zhangsan'@'localhost';

/*示例2--创建李明和黄两个用户，并赋予表customers上SELECT和UPDATE权限*/
GRANT SELECT,UPDATE
    ON mysql_test.customers
    TO 'liming'@'127.0.0.1' IDENTIFIED BY '123'
        'huang'@'127.0.0.1' IDENTIFIED BY '789';

/*示例3--授予王五mysql_test的所有表所有操作权限*/
GRANT ALL
    ON mysql_test.*
    TO 'wangwu'@'localhost';

/*示例4--授予王五拥有创建用户权限*/
GRANT CREATE USER
    ON *.*
    TO 'wangwu'@'localhost';

/*示例5--授予用户可以SSH远程登录权限*/
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '登录密码';
--刷新权限
flush privileges;

11.2.2 权限的转移

with grant option使用这条语句，可把自身拥有的权限赋予其他人

11.2.3 权限的撤销

--语法格式
REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

/*语法格式--收回所有权限*/
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [,user] ...

/*示例1*/
revoke select
    on mysql_test.customers from 'tom'@'localhost';
/*从一用户收回权限可能导致其他用户也失去该权限，称为级联回收*/

/*示例2--指定restrict可以阻止级联回收*/
revoke select
    on instructor from U1,U2,U3 restrict;
/*如果要求级联回收，则带有restrict的revoke命令将会失败*/

11.3 审计跟踪

分为语句审计和对象审计

--语法格式-语句审计
AUDIT <st-opt> [BY <users>]
[BY SESSION | ACCESS]
[WHENEVER SUCCESSFUL | WHENEVER NOT SUCCESSFUL]
--当BY <users>缺省，对所有用户审计
--BY SESSION每次会话期间，相同类型的需审计的SQL语句仅记录一次
--常用的<st-opt>:table，view，role，index，……
--取消审计：NOAUDIT…（其余同audit语句）

/*示例--审计用户scott每次成功执行有关table的语句。*/
audit table by scott by access whenever successful;

--语法格式-对象审计
AUDIT <obj-opt> ON <obj>|DEFAULT
[BY SESSION | BY ACCESS]
[WHENEVER SUCCESSFUL | WHENEVER NOT SUCCESSFUL]
--obj-opt:insert，delete，update，select，grant，…
--实体审计对所有的用户起作用
--ON<obj>指出审计对象表、视图名
--ONDEFAULT 对其后创建的所有对象起作用
--取消审计：NOAUDIT…

/*示例--审计所有用户对student表的delete和update操作*/
audit delete,update on student;

11.4 事务

11.4.1 事务的概念

当多个用户同时更新时，为了保证数据库的正确性，避免数据库的不一致性，控制这种并发操作的机制称为“并发控制”。而事务就是为保证数据的一致性而产生的一个概念和基本手段。

事务（transaction）用户定义的一个数据操作序列，是一个完整的工作单元，要么全部执行，要么全部不执行。

事务以BEGIN TRANSACTION语句开始。

下列SQL语句之一会结束一个事务：

Commit：提交当前事务，也就是将该事务所做的更新在数据库中持久保存。在事务被提交后，一个新的事务自动开始
Rollback：回滚当前事务，即撤销该事务中所有SQL语句对数据库的更新。这样，数据库就恢复到执行该事务第一条语句之前的状态

11.4.2 事务的特征

为保证数据一致性和正确性，数据库确保事务具有如下4个特征，简称事务的ACID特征：

原子性(Atomicity)：一组更新操作是原子不可分。
一致性(Consistency)：满足数据库完整性约束。
隔离性(Isolation)：事务彼此独立，隔离，不被其他事务干扰。一个执行完，另一个才能存取。
持续性(Durability)：一旦提交，对数据的改变是永久的。

11.4.3 并发操作问题

事务的ACID特征可能遭到破坏的原因之一是多个事务对数据库的并发操作造成的。

典型的并发操作问题有如下三个：

丢失更新：读入同一数据并修改，导致之前修改丢失。
不可重复读：读第一次和读第二次的结果不一样，因为别人在中间修改插入删除了某些数据
1. 事务T1读取某一数据后，T2对其修改，T1再次读到与之前不一样的值。
2. T1读取某一数据后，T2删除部分记录，T1再次读发现某些记录丢失了。
3. T1读取某一数据后，T2插入一些记录，T1再次读发现多了一些记录。
读‘脏’数据：T1修改某一数据并写回，T2读取，T1撤销操作，T2的数据便是脏数据

11.4.4 封锁

封锁是最常用的并发控制技术。原理是：需要时，事务通过向系统请求对它所希望的数据对象加锁，确保它不被非预期改变。

11.4.4.1 封锁类型

基本的封锁类型有两种：排他锁（X锁）、共享锁（S锁）。

排他锁：写锁。若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他任何事务都不能再对A加任何类型的锁，直到T释放A上的锁为止。这就保证了其他事务在T释放A上的锁之前不能再读取和修改A。
共享锁：读锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁为止，这就保证了其他事务可以读A，但在T上释放A的S锁之前不能对A做任何修改。

11.4.4.2 封锁协议

一级封锁协议：事务T在修改数据R前必须先对其加X锁，直到事务结束才释放。只能保证不发生丢失修改。
二级封锁协议：在一级封锁协议基础上，增加事务T在读取数据R前必须先对其加S锁，读完后即可释放S锁。增加保证了不发生读“脏”数据
三级封锁协议：在一级封锁协议的基础上，增加事务T在读取数据R前必须先对其加S锁，直到事务结束才释放进一步防止了不可重复读

封锁协议级别越高，一致性程度越高

不同级别的封锁协议和一致性保证

11.4.4.3 活锁与死锁

避免活锁最简单的方法是“先来先服务”。

两个以上事务循环等待被同组中另一事务锁住的数据单元的情形，称为“死锁”。

死锁解决办法

预防死锁的办法：

一次封锁法：每个事务必须一次将所有要使用的数据全部加锁。
缺点：降低了系统的并发度。而且数据是不断变化的，很难事先精确确定每个事务所要封锁的数据对象。
顺序封锁法：预先对数据对象规定一个封锁顺序，所有事务都按这个顺序实现封锁。
缺点：封锁的数据对象极多且在不断变化。事务的封锁请求随着事务的执行而动态地决定，很难事先确定。
序列化处理：通过应用设计为每一数据单元建立“主人程序”，所有请求发给“主人”，而“主人”以单道运行。
缺点：系统性能、数据完整性可能受到影响。
资源剥夺：每当事务因锁请求不能满足而受阻，强行令冲突中的一方回滚，释放所有锁，然后重新运行。
缺点：需要预防活锁发生。

死锁的诊断与解除

超时法：实现简单，用得最多
缺点：可能误判。若时限太长不能及时发现
等待图法：精确判断死锁

11.4.4.4 可串行性

定义：若一个调度等价于某一串行高度，即它所产生的结果与某一串行调度的结果一样，称这种调度是可串行化的。

两段封锁法是一种简单有效的保障封锁其调度是可串行性的方法。

两段封锁法：所有事务必须分两个阶段对数据项进行加锁和解锁

在对任何数据进行读写操作之前，首先要申请并获得对该数据的封锁
在释放一个封锁之后，事务不再申请和获得任何其他封锁

事务分为两个阶段：

获得封锁，也称为扩展阶段，可以申请获得任何数据项上的任何类型的锁，不能释放任何锁
释放封锁，也称为收缩阶段，可以释放任何锁，但是不能申请任何锁

可以证明，若并发执行的所有事务均遵守两段锁协议，则对这些事务的任何并发调度策略都是可串行化的（充分不必要条件），但是可能导致死锁。

11.5 备份与恢复表

用SELECT INTO … OUTFILE备份数据
用LOAD DATA…INFILE恢复数据

--备份语法格式
SELECT * INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name'
/*其中export_options格式为*/
[{FIELDS | COLUMNS}
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
]
[LINES TERMINATED BY 'string']
/*1. FIELDS和LINES子句指定数据行在备份文件中存储的格式。
2. TERMINATED BY：指定字段值之间的符号；
3. ENCLOSED BY：指定包裹文件中字符值的符号；[OPTIONALLY]可选项，所有值都放在符号中。
4. ESCAPED BY：指定转义字符。
5. TERMINATED BY：指定数据行结束标志。
6. DUMPFILE：导出的所有数据行彼此紧挨，值与行之间没有标记。*/

--恢复语法格式
LOAD DATA INFILE 'file_name'
    INTO TABLE tbl_name
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
/*STARTING BY：指定一个前缀，导入数据行时，忽略数据行中该前缀和前缀之间的内容。若某行不包括该前缀，则整个数据行被跳过。*/

/*示例--备份*/
SELECT * FROM mysql_test.customers
    INTO OUTFILE 'C:/BACKUP/backupfile.txt'
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY ""
    LINES TERMINATED BY '?';

/*示例--恢复*/
LOAD DATA INFILE 'C:/BACKUP/backupfile.txt'
    INTO TABLE mysql_test.customers_copy
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY ""
    LINES TERMINATED BY '?';

注意：多个用户使用MYSQL时，备份时在指定表上使用LOCK TABLES table_name READ语句做个读锁定，防止备份中被其他用户更新；恢复时使用LOCK TABLES table_name WRITE语句做个写锁定，防止数据冲突。备份恢复完成后，用UNLOCK TABLES语句对该表进行解锁。

十二、数据库编程

12.1 存储过程

12.1.1 存储过程的基本概念

存储过程是一组SQL语句编译成一个SQL。类似于批量处理的SQL脚本。

存储过程的好处：

可增强SQL语言的功能和灵活性
良好的封装性
高性能
可减少网络流量
存储过程可作为一种安全机制来确保数据库的安全性和数据的完整性

12.1.2 创建存储过程

delimiter是MySQL中的命令，这个命令与存储过程没什么关系。

其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。即改变输入结束符。

默认情况下，delimiter是分号“;”。

但有时候，不希望MySQL这么做。因为可能输入较多的语句，且语句中包含有分号。

默认情况下，mysql一遇到分号，它就要自动执行。

这种情况下，就可以使用delimiter，把delimiter后面换成其它符号，如//或$$。

此时，delimiter作用就是对整个小段语句做一个简单的封装。

1 2	`delimiter // delimiter ;`

每次使用完要记得换回原来的分号;

使用CREATE PROCEDURE来创建存储过程：

--语法格式
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    routine_body
/*其中“proc_parameter”语法格式是[IN | OUT |INOUT]param_name type,
1.在此语法格式中，“sp_name”用于指定存储过程的名称，且默认在当前数据库中创建。
2.“proc_parameter”用于指定存储过程中的参数列表，“type”为SQL的数据类型。
3.IN | OUT |INOUT：表示输入、输出和输入/输出参数。输入参数传递参数给存储过程；输出参数用于存储过程返回一个操作结果；而输出/输出参数则两者皆可。
4.参数的取名不能和表中列名相同，会引发不可预知结果。
5.routine_body：表示存储过程的主体部分。以BEGIN开始，END结束。*/

/*示例*/
use test;
delimiter $$
CREATE PROCEDURE sp_update_sex (IN cid INT,IN csex CHAR(1))
BEGIN
    UPDATE customers SET cust_sex=csex WHERE cust_id=cid;
END$$
delimiter ;

12.1.3 存储过程体

12.1.3.1 局部变量

使用DECLARE语句来声明局部变量。

--语法格式
DECLARE var_name [, var_name] ... type [DEFAULT value]
/*var_name:用于指定局部变量的名称。
type:用于声明局部变量的数据类型。
DEFAULT:用于为局部变量指定一个默认值，若没有指定，默认为NULL。*/

/*示例*/
DECLARE cid INT(10);
--局部变量智能在存储过程体的BEGIN...END语句块中声明使用。

12.1.3.2 SET语句

SET为局部变量赋值。

--语法格式
SET var_name=expr [, var_name=expr] ...

/*示例*/
SET cid=910;

12.1.3.3 SELECT…INTO语句

SELECT...INTO语句把选定列的值直接存储到局部变量中。

--语法格式
SELECT col_name [,...] INTO var_name [,...] table_expr
/*col_name用于指定列名。
var_name用于指定要赋值的变量名。
table_expr表示SELECT语句中FROM子句及其后面的语法部分。
SELECT...INTO语句返回的结果集只能有一行数据。*/

12.1.3.4 游标

在使用SELECT语句检索时，返回的是一个结果集数据行，该结果集中有多行数据，这些数据无法被一行行的进行处理，此时，需要使用游标。

游标是一个被SELECT语句检索出来的结果集。在存储了游标后，应用程序或用户就可以根据需要滚动或浏览其中的数据。使用游标的步骤如下：

1）声明游标

--语法格式
DECLARE cursor_name CURSOR FOR select_statement
/*1. cursor_name：用于指定要创建的游标的名称
2. select_statement:用于指定一个SELECT语句，返回一到多行数据。*/

2）打开游标

必须打开游标才能使用。

1 2	`OPEN cursor_name; /游标可被打开多次，若其他用户或程序更新数据表，每次打开游标结果集可能不同/`

3）读取数据

1
2
3

FETCH cursor_name INTO var_name [,var_name] ...
/*cursor_name:用于指定已经打开的游标
var_name:指定存放数据的变量名*/

4）关闭游标

1	`CLOSE cursor_name;`

5）示例

创建一个存储过程，用于计算表customers中数据行的行数。

delimiter $$
create procedure sp_sumofrow(out rows int)
begin
    declare cid int;
    declare found boolean default true;
    declare cur_cid cursor for
        select cust_id from customers;
    declare continue handler for not found
        set found=false;
    set rows=0;
    open cur_cid;
    fetch cur_cid into cid;
    while found do
        set rows=rows+1;
        fetch cur_cid into cid;
    end while;
    close cur_cid;
end$$
delimiter ;
/*定义一个CONTINUE HANDLER句柄，它是在条件出现时被执行的代码，用于控制循环语句，实现游标的下移。
定义局部变量必须在定义任意游标和句柄之前。*/

/*游标只能用于存储过程或存储函数中，不能单独在查询操作中使用。*/

12.1.4 调用存储过程

--语法格式
CALL sp_name([parameter[,...]]);
CALL sp_name[()];
/*sp_name:指定被调用的存储过程的名称。
parameter：指定调用存储过程所使用的参数*/

/*示例*/
CALL sp_update_sex(909,'M');

12.1.5 删除存储过程

--语法格式
DROP PROCEDURE [IF EXISTS] sp_name;

/*示例*/
DROP PROCEDURE IF EXISTS sp_update_sex;

12.2 存储函数

存储过程和存储函数的区别：

存储函数不能拥有输出参数，自身即是输出参数；存储过程可以拥有输出参数
存储函数可以被直接调用，而存储过程必须通过CALL语句调用
存储函数中必须包含一条RETURN语句，而这条特殊的SQL语句不允许包含于存储过程中

--1.创建函数
--语法格式
CREATE FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    routine_body

/*示例--根据给定学生id号返回学生性别，如果没有给定id，则返回"没有该学生"*/
USE test;
DELIMITER $$
CREATE FUNCTION fn_search(cid INT)
    RETURNS CHAR(2)
BEGIN
    DECLARE SEX CHAR(2);
    SELECT gender INTO SEX FROM students
        WHERE id=cid;
    IF SEX IS NULL THEN
        RETURN(SELECT "没有该学生");
    ELSE
        IF SEX='F' THEN
            RETURN(SELECT '女');
        ELSE
            RETURN(SELECT '男');
        END IF;
    END IF;
END$$
DELIMITER ;


--2.调用函数
--语法格式
SELECT sp_name([func_parameter[,...]])

/*示例*/
SELECT fn_search(10);


--3.删除函数
--语法格式
DROP FUNCTION [IF EXISTS] sp_name

/*示例*/
DROP FUNCTION IF EXISTS fn_search;

读书笔记 SQL

SQL 读书笔记

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议，转载请注明出处！

按键精灵语法上一篇

【转】利用爬虫和树莓派打造萝莉音天气闹钟下一篇