LINUX -SQL笔记（自学用）

1.安装

sudo apt-get install mysql-server
sudo mysql -u root -p
1
2

2.关系模型
在关系数据库中，一张表中的每一行数据被称为一条记录。一条记录就是由多个字段组成的。
每一条记录都包含若干定义好的字段。同一个表的所有记录都有相同的字段定义。
对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。
1.记录一旦插入到表中，主键最好不要再修改。
2.不使用任何业务相关的字段作为主键。
3.作为主键最好是完全业务无关的字段，我们一般把这个字段命名为id。
常见的可作为id字段的类型有：
1.自增整数类型：
2.全局唯一GUID类型

联合主键
关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。
对于联合主键，允许一列有重复，只要不是所有主键列都重复即可。量不使用联合主键，因为它给关系表带来了复杂度的上升。

外键：数据与另一张表关联起来，这种列称为外键。
索引：可以加快查询速度，唯一索引，唯一约束

查询数据

1.基本查询

SELECT * FROM <表名>;
SELECT * FROM students;
使用SELECT * FROM students时，SELECT是关键字，表示将要执行一个查询，*表示“所有列”，FROM表示将要从哪个表查询，
1
2
3

2.条件查询

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;//class_id <> 2
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';//如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。
1
2
3
4
5
6
7

3.投影查询
如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM …，让结果集仅包含指定列。这种操作称为投影查询。
可以给每一列起个别名，这样，结果集的列名就可以与原表的列名不同。SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM …

SELECT id, score, name FROM students;
SELECT id, score points, name FROM students;
SELECT id, score points, name FROM students WHERE gender = 'M';
1
2
3

4.排序

SELECT id, name, gender, score FROM students ORDER BY score;//按score从低到高
SELECT id, name, gender, score FROM students ORDER BY score DESC;//按照成绩从高到底排序，我们可以加上DESC表示“倒序”
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;//score列有相同的数据，要进一步排序，可以继续添加列名。

SELECT id, name, gender, score 
FROM students 
WHERE class_id = 1
ORDER BY score DESC;//带WHERE条件的ORDER BY  一个关键词一行清晰表达
1
2
3
4
5
6
7
8

5.分页查询
分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT OFFSET 子句实现.

查询第一页
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;//结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始。

查询第二页
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;//跳过头3条记录，也就是对结果集从3号记录开始查询，把OFFSET设定为3
1
2
3
4
5
6
7
8
9
10
11

6.聚合查询
统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。

查询students表一共有多少条记录为例，我们可以使用SQL内置的COUNT()函数查询
SELECT COUNT(*) FROM students;//聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)。

SELECT COUNT(*) num FROM students;//通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果

SELECT COUNT(*) boys FROM students WHERE gender = 'M';
聚合函数：
SUM	计算某一列的合计值，该列必须为数值类型
AVG	计算某一列的平均值，该列必须为数值类型
MAX	计算某一列的最大值
MIN	计算某一列的最小值

SELECT AVG(score) average FROM students WHERE gender = 'M';

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

对于聚合查询，SQL还提供了“分组聚合”的功能：
SELECT COUNT(*) num FROM students GROUP BY class_id;

SELECT name, class_id, COUNT(*) num FROM students GROUP BY class_id;//把name放入结果集

SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;//使用多个列进行分组
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

7.多表查询（笛卡尔查询）
由于结果集是目标表的行数乘积，对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录，对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。

SELECT * FROM students, classes;//每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。

SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;
//投影查询的“设置列的别名”来给两个表各自的id和name列起别名


SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;
//用表名.列名这种方式列举两个表的所有列实在是很麻烦，所以SQL还允许给表设置一个别名

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;//多表查询也是可以添加WHERE条件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

8.连接查询
连接查询是另一种类型的多表查询。连接查询对多个表进行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;
//希望结果集同时包含所在班级的名称，上面的结果集只有class_id列，缺少对应班级的name列。
//存放班级名称的name列存储在classes表中，只有根据students表的class_id，找到classes表对应的行，再取出name列，就可以获得班级名称。
//这时，连接查询就派上了用场。我们先使用最常用的一种内连接——INNER JOIN来实现：
1
2
3
4
5
6
7
8

注意INNER JOIN查询的写法是：

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

使用别名不是必须的，但可以更好地简化查询语句。

外连接
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;

//INNER JOIN只返回同时存在于两张表的行数据。

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

//LEFT OUTER JOIN则返回左表都存在的行。

//FULL OUTER JOIN，它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL。
1
2
3
4
5
6
7
8
9
10
11
12
13

修改数据

1.INSERT

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

INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);

SELECT * FROM students;//查询并观察结果:
1
2
3

INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '大宝', 'M', 87),
  (2, '二宝', 'M', 81);

SELECT * FROM students;
1
2
3
4
5

2.UPDATE

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
1

//更新id=1的记录
UPDATE students SET name='大牛', score=66 WHERE id=1;
-- 查询并观察结果:
SELECT * FROM students WHERE id=1;
1
2
3
4

//更新id=5,6,7的记录
UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;
-- 查询并观察结果:
SELECT * FROM students;
1
2
3
4

//在UPDATE语句中，更新字段时可以使用表达式。
UPDATE students SET score=score+10 WHERE score<80;
-- 查询并观察结果:
SELECT * FROM students;

1
2
3
4
5

UPDATE语句可以没有WHERE条件，整个表的所有记录都会被更新。

3.DELETE

DELETE FROM <表名> WHERE ...;
1

//删除id=1的记录
DELETE FROM students WHERE id=1;
-- 查询并观察结果:
SELECT * FROM students;

DELETE FROM students WHERE id>=5 AND id<=7;
-- 查询并观察结果:
SELECT * FROM students;

1
2
3
4
5
6
7
8
9

不带WHERE条件的DELETE语句会删除整个表的数据：DELETE FROM students;

MYSQL

EXIT仅仅断开了客户端和服务器的连接，MySQL服务器仍然继续运行

1.管理MYSQL

SHOW DATABASES;
//information_schema、mysql、performance_schema和sys是系数据库，不要改动。

CREATE DATABASE test;//创建一个新数据库test
DROP DATABASE test;//删除

USE test;//切换当前数据库

SHOW TABLES;//列出当前数据库的所有表
DESC students;//查看一个表的结构

SHOW CREATE TABLE students;//查看创建表的SQL语句
创建表使用 CREATE TABLE 语句，而删除表使用 DROP TABLE  语句

修改表比较复杂
ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
//students表新增一列birth
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
//修改birth列，例如把列名改为birthday，类型改为VARCHAR(20)
ALTER TABLE students DROP COLUMN birthday;
//ALTER TABLE students DROP COLUMN birthday;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

实用SQL语句

插入或替换

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
//若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。
1
2

插入或更新

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语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定。
1
2

插入或忽略

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
//若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。
1
2

快照

- 对class_id=1的记录进行快照，并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;
1
2

写入查询结果集

CREATE TABLE statistics (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    average DOUBLE NOT NULL,
    PRIMARY KEY (id)
);
INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

1
2
3
4
5
6
7
8

强制使用指定索引

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

事务

数据库事务具有ACID这4个特性：

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

要手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务，例如，把上述的转账操作作为一个显式事务：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
1
2
3
4

COMMIT是指提交事务，即试图把事务内的所有SQL所做的修改永久保存。如果COMMIT语句执行失败了，整个事务也会失败。
我们希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;
1
2
3
4

Isolation Level
脏读（Dirty Read）
不可重复读（Non Repeatable Read）
幻读（Phantom Read）

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;在使用 “READ UNCOMMITTED” 隔离级别的事务中：
事务可以读取当前由其他事务修改的数据，这可能导致脏读、不可重复读和幻读等问题。数据被读取时不会加锁，允许其他事务同时修改相同的数据。
这个级别提供了最高程度的并发性，但牺牲了数据的一致性和完整性。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;在Read Committed隔离级别下，一个事务可能会遇到不可重复读（Non Repeatable Read）的问题。
不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;在Repeatable Read隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题。
幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。

Serializable是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。
虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。