关于SQL

关系模型

1.主键

主键：能够通过某个字段唯一区分出不同的记录，这个字段即为主键。

选取主键的原则：不使用任何业务相关的字段作为主键。

常用的id类型：

• 自增整数类型
• 全局唯一GUID类型

联合主键：两个或更多的字段都设置为主键。此时允许一列有重复，只要不是所有主键列都重复即可

2.外键

把数据与另一张表关联起来，这种列称为外键。

3.索引

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。如果记录的列存在大量相同的值，对该列创建索引就没有意义。

索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引。

通过创建唯一索引，可以保证某一列的值具有唯一性。

查询

先明确两个表students、classes

基本查询：

SELECT * FROM <表名>

• SELECT是关键字，表示将要执行一个查询
• *表示“所有列”
• FROM表示将要从哪个表查询

条件查询

SELECT * FROM <表名> WHERE <条件表达式>

例如：

 SELECT * FROM students WHERE score >= 80;
 ​
 SELECT * FROM students WHERE score >= 80 AND gender = 'M';
 ​
 SELECT * FROM students WHERE score >= 80 OR gender = 'M';
 ​
 SELECT * FROM students WHERE NOT class_id = 2;
 ​
 SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
 ​
 SELECT * FROM students WHERE name LIKE 'ab%';

指定了条件：分数在80分或以上的学生。

第3行：<条件1> AND <条件2>，设置了两个条件，且是同时满足的关系。

第5行：<条件1> OR <条件2>，满足两个条件之一即可。

第7行：NOT <条件>，不满足该条件的。（该方式等价于class_id <> 2，'<>'表示不等于）

第9行：组合三个或者更多的条件，就需要用小括号()（否则按照NOT > AND > OR的优先级进行查询）

第11行：%表示任意字符，例如'ab%'将匹配'ab'，'abc'，'abcd'

投影查询

• SELECT 列1, 列2, 列3 FROM ...

  让结果返回某些列的数据

• SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...

  表示给查询的列1结果起了个别名1（其他以此类推）

排序

ORDER BY关键字

 SELECT id, name, gender, score FROM students ORDER BY score;
 ​
 SELECT id, name, gender, score FROM students ORDER BY score DESC;
 ​
 SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
 ​
 SELECT id, name, gender, score
 FROM students
 WHERE class_id = 1
 ORDER BY score DESC;

• 表示查询结果按照score的数值从高到低排序。
• 加上DESC关键字，表示”倒序“排序
• 表示先按score列倒序，如果有相同分数的，再按gender列排序
• ORDER BY子句要放到WHERE子句后面（7-11）

分页显示

使用LIMIT OFFSET 对结果进行分页显示。

• LIMIT：设定为每页显示结果的数量
• OFFSET：第几页（从0开始）

在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。

聚合查询

1.内饰函数：

函数	说明
COUNT	计算记录的条数。COUNT(*)表示查询所有列的行数
SUM	计算某一列的合计值，该列必须为数值类型
AVG	计算某一列的平均值，该列必须为数值类型
MAX	计算某一列的最大值
MIN	计算某一列的最小值

如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()、MIN()会返回NULL

2.分组聚合：GROUB BY

 SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

多表查询

SELECT * FROM <表1> <表2>

 SELECT * FROM students, classes;
 ​
 SELECT
     students.id sid,
     students.name,
     students.gender,
     students.score,
     classes.id cid,
     classes.name cname
 FROM students, classes;
 ​
 SELECT
     s.id sid,
     s.name,
     s.gender,
     s.score,
     c.id cid,
     c.name cname
 FROM students s, classes c;
 ​

• from后的两个表：同时从students表和classes表的笛卡尔积中查询
• 以表名.列名的方式来引用列和设置别名。格式为：FROM <表名1> <别名1>, <表名2> <别名2>
• 12行的方式是给students、classes表设置了别名s和c，然后再给列设置别名。

连续查询

对多个表进行JOIN运算：先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

内连接:INNER JOIN

只返回同时存在于两张表的行数据

 SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
 FROM students s
 INNER JOIN classes c
 ON s.class_id = c.id;

1. 先确定主表，仍然使用FROM <表1>的语法；(这里的主表为别名为s的students表)
2. 再确定需要连接的表，使用INNER JOIN <表2>的语法；（别名为c的classes表）
3. 然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
4. 可选：加上WHERE子句、ORDER BY等子句。

外连接：OUTER JOIN

RIGHT OUTER JOIN：返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。

LEFT OUTER JOIN：返回左表都存在的行

FULL OUTER JOIN：把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL。

修改数据

INSERT

 INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

• 字段顺序不必和数据库表的字段顺序一致，但值的顺序必须和字段顺序一致
• 可以一次性添加多条记录，VALUES子句中指定多个记录值，用多个（）修饰并用逗号隔开即可。

UPDATE

 UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
 ​
 UPDATE students SET score=score+10 WHERE score<80;

• 更新字段时可以使用表达式(第3行将80分以下的同学score字段加10)
• WHERE条件没有匹配到任何记录，UPDATE语句不会报错，也不会有任何记录被更新
• UPDATE语句可以没有WHERE条件，这时，整个表的所有记录都会被更新

DELETE

 DELETE FROM <表名> WHERE ...;

需要注意的点与update相似：可以一次删除多行、where没有匹配到时不会删除任何语句、无where时删除所有记录。

关于MYSQL

连接MYSQL Server:

 mysql -u root -p;

列出所有数据库:

 SHOW DATABASES;

创建数据库和删除一个数据库：

 CREATE DATABASE 数据库名称;
 DROP DATABASE 数据库名称;

对一个数据库进行操作时，要首先将其切换为当前数据库：

 USE 数据库名称;

---

对于表的操作

列出当前数据库的所有表，使用命令：

 SHOW TABLES；

查看一个表的结构，使用命令：

 DESC 表名称;

查看创建表的SQL语句：

 SHOW CREATE TABLE 表名称;

创建表和使用表：

 CREATE TABLE 表名称;
 DROP TABLE 表名称;

给某个表增加一列、修改一列、删除一列（如要给students表增加一列birth、把birth修改为birthday、删除birthday）：

 ALTER TABLE students CHANGE COLUMN birth VARCHAR(10) NOT NULL;
 ​
 ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
 ​
 ALTER TABLE students DROP COLUMN birthday;

实用SQL语句

插入或替换

插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。使用REPLACE INTO语句。

将原INSERT语句的关键词替换为REPLACE INTO。

插入或更新

INSERT INTO ... ON DUPLICATE KEY UPDATE ...

 INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

id = 1不存在即执行insert，否则执行update。

插入或忽略

INSERT IGNORE INTO ...

 INSERT IGNORT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

id=1不存在则执行insert，否则不执行任何操作。

快照

复制一份当前表的数据到一个新表。结合CREATE TABLE和SELECT：

 CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

新创建的表结构和SELECT使用的表结构完全一致。

写入查询结果集

结合INSERT和SELECT，将SELECT语句的结果集直接插入到指定表中。

 INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

将select的内容存入statistics表中。

强制使用指定索引

使用FORCE INDEX强制查询使用指定的索引：

 SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

索引idx_class_id必须存在

---

事务

数据库事务：把多条语句作为一个整体进行操作的功能。

ACID特性：

• A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
• C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
• I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
• D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

隐式事务：数据库系统自动将单条SQL语句作为一个事务执行。

显式事务：把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务

ROLLBACK回滚事务，整个事务会失败

SQL标准定义了4种隔离级别：

Isolation Level	脏读（Dirty Read）	不可重复读（Non Repeatable Read）	幻读（Phantom Read）
Read Uncommitted	Yes	Yes	Yes
Read Committed	-	Yes	Yes
Repeatable Read	-	-	Yes
Serializable	-	-	-

四种隔离级别的例子——廖雪峰SQL教程