学习笔记-SQL基础语法2

本篇内容承接上一篇SQL基础语法继续学习，首先我们的例子重新说明一下。

一个名为basketball 的数据库，里面有一个team的表，内容如下：

idnameposition1张三PF2李四SF3王五PG4赵六SG

此次，我们需要在basketball下再创建一个表data，内容如下：

idPointsReboundAssistsStealsblocks1201510152258831322512505356540

四、操作数据

4.6 BETWEEN关键字：

格式：SELECT * FROM 表名 WHERE 条件列名 BETWEEN 值1 AND 值2 ;

BETWEEN操作符在WHERE子句中使用，作用是选取介于两个值之间的数据范围。

SELECT * FROM team WHERE position BETWEEN 'PF' AND 'PG' ;

结果集返回team表中position为PF到PG 的数据，这里返回PF，SF，PG三行数据。

如果需要两个值以外的数据范围，BETWEEN前加NOT即可。

格式：SELECT * FROM 表名 WHERE 条件列名 NOT BETWEEN 值1 AND 值2 ;

SELECT * FROM team WHERE position NOT BETWEEN 'PF' AND 'PG' ;

结果集返回team表中position为PF至PG以外的数据，这里返回SG的数据。

tips：不同数据库对BETWEEN...AND操作符的处理是存在差异的。有些可能包含值1和值2，有些只包含值1，有些均不包含。

这里使用的MySQL5.0是包含值1和值2的，NOT则不包含。

4.7 Alias（别名）

所谓别名，就是为我们制定的表或者列设定一个方便阅读的名字。

格式：表名 AS 表的别名，列名 AS 列的别名

SELECT t.id,t.name,t.position,d.Points FROM team AS t,data AS d ;

结果集返回team表中的id，name，position和data表中的Points，这里将team设为t，data设为d。

4.8 JOIN关键字

查询多个表时，需要将数据返回到一个结果集上，我们可以使用的方法有：

SELECT 表1.列1 , 表2.列1 FROM 表1 , 表2...;

SELECT * FROM 表1 , 表2 WHERE 表1.id = 表2. id ;

当然我们还可以使用JOIN关键字将两张表联系起来，JOIN一共有四种连接方式：

JOIN/INNER JOIN ：左右表都匹配，则返回行。

格式：SELECT * FROM 表1 [INNER] JOIN 表2 ON 表1.id = 表2.id ;

SELECT * FROM team JOIN data ON team.id = data.id ;

用team表中的id和data表中的id进行JOIN匹配，返回两张表中id为1,2,3的所有数据。

LEFT JOIN ：即使右表中没有匹配，也从左表返回所有行，右表用NULL进行填充。

格式：SELECT * FROM 表1 LEFT JOIN 表2 ON 表1.id = 表2.id ;

SELECT * FROM team LEFT JOIN data ON team.id = data.id ;

用team表中的id和data表中的id进行LEFT JOIN匹配，返回两张表中id为1,2,3,4的所有数据，data中没有id为4的数据，则用NULL填充。

RIGHT JOIN ：即使左表中没有匹配，也从右表返回所有行，左表用NULL进行填充。

格式：SELECT * FROM 表1 RIGHT JOIN 表2 ON 表1.id = 表2.id ;

SELECT * FROM team RIGHT JOIN data ON team.id = data.id ;

用team表中的id和data表中的id进行RIGHT JOIN 匹配，返回两张表中id为1,2,3,5的所有数据，team中没有id为5的数据，则用NULL填充。

FULL JOIN ：只要一个表中存在匹配，就返回行。在MySQL中5.1以上版本才能使用。

格式：SELECT * FROM 表1 FULL JOIN 表2 ON 表1.id = 表2.id ;

4.9 UNION关键字

UNION操作符：联合，用于合并两个或多个SELECT语句的结果集。

格式：

(SELECT 列 FROM 表)

UNION[ALL]

(SELECT 列 FROM 表)

要求：

• 两次查询子句返回的结果集的列数必须一样，类型相似。
• UNION会自动去掉重复行，不想去掉可以再UNION后加ALL。
• 如果查询子句中没有ORDER BY ...LIMIT ，可以不使用()。
• 在联合语句的查询子句中如果要使用ORDER BY 进行排序，那么必须跟上LIMIT 限定，否则OEDER BY 无效。
• 返回结果集的字段名，是用第一个查询语句的字段名来命名的。
• 用于将两个查询逻辑完全相反的结果集合并到一起。

tips：上述两次均可换为多次。

SELECT id FROM teamUNIONSELECT id FROM data ;

返回名为id的结果集，其中数据为1,2,3,4,5 。

五、SQL约束

约束用于限制接入表的数据类型，可以在建表时设定约束，也可以使用ALTER TABLE 语句修改。主要有以下几种约束：

5.1 NOT NULL （非空）：

NOT NULL 约束该字段不能接受NULL值，也就是说如果不向此字段加入数据值，就无法插入新的数据。

格式：

CREATE TABLE 表名 （字段名 字段类型 NOT NULL）;

ALTER TABLE 表名 MODIFY 字段名 字段类型 NOT NULL ;

ALTER TABLE team MODIFY id int(10) NOT NULL ;

为team中的id加上NOT NULL约束。

5.2 UNIQUE（唯一性）：

UNIQUE约束，用来表示这个字段数据的唯一性，防止重复。

格式：

添加单列：

CREATE TABLE 表名 （列1 列1类型，列2 列2类型，UNIQUE(列1)）;

ALTER TABLE 表名 ADD UNIQUE(列) ;

添加多列：

ALTER TABLE 表名 ADD CONSTRAINT UNIQUE约束名 UNIQUE (列名...) ;

设置多列UNIQUE约束后，需要这多列全部重复，才会生效。

ALTER TABLE team ADD UNIQUE(position) ;

为team表中的position添加唯一性限定，防止重复。

删除UNIQUE约束：

ALTER TABLE 表名 DROP INDEX UNIQEU约束名 ;

这里如果只添加了一列UNIQUE约束，那么约束名即为那一列的字段名；如果添加的多列的UNIQUE约束时，这个约束名为我们命名的UNIQUE约束名。

ALTER TABLE team DROP INDEX position ;

将添加给team表中的position的UNIQUE限定删除。

5.3 PRIMARY KEY（主键）：

用于约束唯一标识数据库表中的每条记录。

要求：

• 主键必须包含唯一值（不能重复）。
• 主键不能包含NULL值（不能不填，第一次可以不填，会自动补默认值）。
• 每个表中只能有一个主键。

格式：

添加主键：

CREATE TABLE 表名 (列1 列1类型，列2 列2类型，PRIMARY KEY(列1)) ;

ALTER TABLE 表名 ADD PRIMARY KEY(列) ;

删除主键：

ALTER TABLE 表名 DROP PRIMARY KEY ;

ALTER TABLE team ADD PRIMARY KEY(id) ;

为team表中的id添加主键约束。

tips：当我们要制定的字段数据中不符合主键约束时，无法添加。

5.4 FOREIGN KEY（外键）：

外键约束，用来指向另一个表中的PRIMARY KEY 。

用来预防破坏表之间连接的动作，也可以防止非法数据的插入，因为外键必须是他指向的那个表的值之一。

格式：

添加外键：

CREATE TABLE 表名 (列1 列1类型，列2 列2类型，FOREIGN KEY(列1) REFERENCES 表2(表2的列)) ;

ALTER TABLE 表名 ADD FOREIGN KEY(列) REFERENCES 表2 (表2的列) ;

删除外键：

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名 ;

ALTER TABLE data ADD FOREIGN KEY(id) REFERENCES team(id) ; 

为data表的id添加外键，指向team表中的id。

tips：因为当制定作为外键的字段中数据不符合外键约束时，外键无法添加，所以这个例子中需要先将data中的id为5的一行删除。

5.5 CHECK（值限定）：

设定列的值得范围。

格式：

添加约束：

CREATE TABLE 表名(列1 列1类型，列2 列2类型，CHECK(列1>=值)) ;

ALTER TABLE 表名 ADD CHECK(列>=值) ;

这里的>=可以替换为其他关系运算符。

删除约束：

ALTER TABLE 表名 DROP CHECK 约束名 ;

ALTER TABLE team ADD CHECK(id > 0) ;

为team表中的id添加值约束，需大于0 。

5.6 DEFAULT（默认值）：

设定字段的默认值。

• MySQL会给这些列自动加一个默认值为NULL 的默认值。
• 如果追加默认值会覆盖掉前面设定的默认值。
• 如果删除默认值，不会自动回到DEFAULT NULL，而是没有了默认值，没有默认值，那么在插入数据时就一定要插入这一列的数据，这个数据也可以为NULL。

格式：

添加默认值：

CREATE TABLE 表名 (字段名 字段类型 DEFAULT 默认值，字段名 字段类型 ...) ;

ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值 ;

删除默认值：

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT ;

ALTER TABLE team ALTER position SET DEFAULT 'C' ;

为team表中的position添加一个默认值C。

六、VIEW视图

视图是基于SQL语句的结果集的可视化的表。

特点：

• 暴露部分数据给外部
• 视图主要用于读取数据，因为插入数据要受到原来的表的限定。
• 视图包含行和列，就像一个真实的表，但实质上并不是一张表，尽管看起来一模一样，但是数据实质上是存在原来的表中的。
• 我们可以向视图添加SQL函数、WHERE以及JOIN语句，我们也可以提交数据，就像这些来自于某个单一的表。
• 可以简化我们某些较为复杂的业务逻辑。

tips：数据库的设计和结构不会受到视图中的函数、WHERE或JOIN语句的影响。

格式：

创建视图：

CREATE VIEW 视图名 AS SELECT STARTMENT (查询语句) ;

CREATE VIEW v_team_data AS SELECT team.id,team.name,team.position,data.points FROM team,data WHERE team.id = data.id ;

创建一个视图v_team_data，里面放入id为1,2,3的team的id，name，position和data的points的数据。

更新视图：

CREATE OR REPLACE VIEW 视图名 AS SELECT STARTMENT(查询语句) ;

删除视图：

DROP VIEW 视图名 ;

七、事务

事务包含了一组数据库操作命令，这组命令要么全部执行，要么都不执行，是一个不可分割的工作逻辑单元。可以保持数据的完整性。

功能实现原理：

• 在我们的InnoDB表中，我们执行的SQL语句，都要先存入日志文件，然后再将执行结果提交到服务器，完成整个操作。
• 我们这里将开始由MySQL所掌握的自我提交功能，转变为由我们手动操作的提交功能，当成功完成一组SQL语句，手动提交后，才真实改变数据库的操作。

特性（ACID）：

• 原子性（Atomicity）：执行一组SQL语句，他们中只要有一条语句执行失败，整个事务则都失败。
• 一致性（Consisitency）：在事务开始之前和事务结束以后，数据库的设定是一样的。
• 隔离性（Isolation）：当使用事务对数据进行操作时，我们另外的会话是无法对此条数据进行操作的。我们的InnoDB使用的是数据行锁。
• 持久性（Durability）：当事务成功，数据将提交到数据库，数据就被持久化了。

语法：

1. SET @@autocommit = 0 ; (1：自动提交，0：手动提交)

COMMIT ;手动提交

ROLLBACK ; 回滚

2. START TRANSACTION ; 启动事务

COMMIT ; 成功后提交

ROLLBACK ;  回到事务启动前的状态

SAVEPOINT 点名 ; 设置标记，用于食用ROLLBACK TO 点名 回滚到此点状态。