SQL一文入门助记

什么是SQL

SQL(Structured Query Language)是用于操作数据库的语言。一个博客有许多网站，一个游戏要储存许多游戏的账号密码，这些都离不开数据库操作。

关系型数据库与NoSQL

关系型数据库就是一个表格，每一个横行就是每一条数据，每一个纵列就是每条数据有哪些信息：

id	name	score	gender
1	Alice	90	F
2	Bob	92	M
3	Carol	90	F
4	David	98	M

常见的关系型数据库： Oracle、MySql、Microsoft SQL Server、SQLite

非关系型数据库就是没有这些限制的数据库，他们结构更自由，通常数据自身就以对象的方式存储，NoSQL 往往为某些特别数据量身打造，在许多场合效率更高。

常见的 NoSQL 数据库：MongoDB、Redis、Memcached、HBase

但总体而言，SQL 的使用场合多得多，NoSQL 意为"Not Only SQL"，用于辅助 SQL 数据库，而非取而代之。

MySQL, SQL 与 InnoDB

SQL 是一种结构化查询语言，是一门 ANSI 标准的计算机语言，但是仍然存在着多种不同版本的 SQL 语言。在不同的软件里，每款数据库的 SQL 语法通常有区别。但在基本的增删改查功能上他们是一样的，但某个特型数据库提供的 SQL 语句可能就不能在其他地方执行了。

SQL语句通常不区分大小写，但一般关键字用大写；SQL语句末尾要打分号。SQL以--comment为单行注释，/* comment */为多行注释。

MySQL 是一款优秀的 SQL 软件，是目前应用最广泛的开源关系数据库之一。MySQL最早是由瑞典的 MySQL AB 公司开发，该公司在2008年被 SUN 公司收购，紧接着，SUN 公司在 2009 年被 Oracle 公司收购，所以 MySQL 最终就变成了 Oracle 旗下的产品。这篇文章以 MySQL 为载体，介绍最简单的 SQL 语句。

而 MySQL 本身也只是一个接口，内部的引擎（真正做增删改查的）被分离出来了，称作数据库引擎，常用的引擎之一是 InnoDB。

MySQL 分免费版和付费版，他们的代码功能是一样的，付费版多出来的功能是一些管理功能。

免费版下载：https://dev.mysql.com/downloads/mysql/

Mysql 和 mysqld

安装完成后通常服务会自己启动，这个服务是运行在后台的，此时我们可以用 python，node 等连接，也可以用mysql控制台连接：

mysql -u root -p

-u代表用户名，-p代表接下来输入密码，默认密码应该是root。见到提示符mysql>后，就可以手动操作了。

如果重启了电脑，后台SQL服务就关闭了，输入mysqld即可再次启动。

创建数据库和数据表

一个软件里有几个数据库，一个数据库里有几个数据表；SHOW databases;查看数据库；USE ***;选中一个数据库；SHOW tables;查看这个数据库里面的数据表。

CREATE DATABASE test; --创建数据库
USE test;
CREATE TABLE students(
	id INT, 
	name varchar(100),
	score INT,
	gender varchar(100)
); --创建表
SHOW CREATE TABLE students; --查看刚刚创建students的语句
DESC students; --查看表的字段信息
TRUNCATE TABLE students; --清空表数据，但表还在
DROP TABLE students; --删除表
DROP DATABASE test; --删除数据库

DESC 将输出字段信息：

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

SELECT 查询

SELECT用于选取数据。

SELECT * FROM table1; --从table1中选取所有数据
SELECT id,score FROM students;  --从students中选取id列和score列（name列不会返回，返回的数据是两列）
SELECT score/2 FROM students; -- 输出score除以2的值，列名就叫score/2
SELECT DISTINCT score FROM students;  --列出score列其中所有不同的值，返回的score列里同分的只保留一个

AS 选取别名

你可以为每个列名选取一个别名，直接附在列名的后面就可以了

SELECT id AS stunumber,score AS point FROM students;

输出列表头的id会变成stunumber。AS通常可以省略

WHERE 条件

WHERE 子句用于提取那些参数满足指定条件的记录。

WHERE后的条件是布尔判断值可以使用关系运算符，不等号用<>表示，不能连等，字符串按字典序；也可以用NOT AND OR逻辑运算符，优先级从前往后，可以加上括号改变优先级。

SELECT * FROM students WHERE score>=95; --只返回score值大于等于95的横行记录
SELECT * FROM students WHERE 85<=score AND score<=90 AND gender = 'M'; 
--返回score在85到90且gender是M的行

BETWEEN、LIKE 和 IN

• BETWEEN表示“在……之间”，如85<=score AND score<=90等价于BETWEEN 85 AND 90（左闭右闭）；

• IN表示“在这……其中”，可在多个之中选择，如gender='M' OR gener='F'等价于gender IN ('M', 'F')；

• LIKE用于字符串通配，如WHERE name LIKE '%a'表示“以a结尾的”。在SQL中，%代表0,1或多个任意字符，_代表一个任意字符；[a-z]和[^123]等则代表选定的字符集之一，规则同正则表达式

ORDER BY 排序

这个用于排序，排序输出的数据。

SELECT id,score FROM students ORDER BY score; --按score从小到大
SELECT id,score FROM students ORDER BY score DESC; --按score从大到小
SELECT id,score FROM students ORDER BY score DESC, id; --按score从大到小，同分再按id从小到大

语句是串联起来的，ORDER BY放到WHERE之后，先筛选，再排序。

LIMIT 只返回部分数据

如果返回的数据太多了，我们可以使用LIMIT只返回一部分数据

SELECT * from table1 LIMIT 3; --只取前三个数据
SELECT * from table1 ORDER BY score LIMIT 3; --排序后取前三个
SELECT * from table1 LIMIT 3 OFFSET 4; --从第四个数据开始（0based）只取前三个数据，即4, 5, 6行。超出下标的忽略

并非所有数据库都支持LIMIT - OFFSET语法，但每款软件都有自己的选取数据语法。

INDEX 索引

如果经常根据某一列进行查询，可以根据这一列创建索引：

CREATE INDEX indexofscore ON students (score);
ALTER TABLE students ADD INDEX indexofscore (score, id); --都可以创建索引

CREATE UNIQUE INDEX indexofscore ON students (score);--创建一个唯一索引，保证 score 唯一
ALTER TABLE students DROP INDEX indexofscore; --删除索引

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，例如gender 列，大约一半的记录值是 M，另一半是 F，因此，对该列创建索引就没有意义。

索引将加入额外信息维护数据，使查询更快；但在增删改时也要更新索引，所以修改数据会变慢。

增删改

INSERT INTO 插入

插入几行数据

INSERT INTO table1 VALUES (value1,value2,value3); 
--插入一列(value1,value2,value3)到table1表，按顺序对应原来的列

INSERT INTO students (score,id,gender)
VALUES (89,5,'M'), (90,6,'M'); --可以指定每个数据对应的列，可以插入多个数据

修改完成后会返回信息，有多少行被修改
Query OK, 6 rows affected (0.02 sec)
Rows matched: 6 Changed: 6 Warnings: 0

UPDATE 更新

修几行的数据

UPDATE table1
SET column1 = value1, column2 = value2, ...
WHERE condition;

对于每个符合 WHERE 条件的行，修改 SET 里指定的数据列，如果没有WHERE条件，就修改全部的数据

UPDATE students SET score=100 WHERE gender='M'; --gender为M的全部修改为100分
UPDATE students SET score=score+id WHERE gender='M'; --gender为M的全部加上id。（不能用+=）

DELETE 删除

删除一些行的数据

DELETE FROM table1 WHERE condition;
DELETE FROM students WHERE score>100;

每个符合 WHERE 条件的行都会被删除，如果没有WHERE条件，整个表的行都被删除。

JOIN 连接查询

现在每个学生还有一个班，为了表示每个学生的班，再加一列 class：

id	name	score	gender	class
1	Alice	90	F	1
2	Bob	92	M	2
3	Carol	90	F	3
4	David	98	M	2

在另一个表 classes 里，是每一个班的名字：

id	name
1	火箭班
2	自动化
3	编程班
4	战神班

现在要查询全体学生所在的班，也就是查询 student 表的同时根据 student.class 再到 classes 表中附上一列 class.name，这就要用到连接查询。

SELECT * FROM students /*选取 students 里的数据*/
INNER JOIN classes  /*并和 classes 取积*/
ON students.class=classes.id;  /* 保留 students.class=classes.id 的值*/

我们得到的两个表拼起来后的输出：

+----+-------+-------+--------+-------+----+-----------+
| id | class | name  | gender | score | id | name      |
+----+-------+-------+--------+-------+----+-----------+
|  1 |     1 | Alice | F      |    90 |  1 | 火箭班    |
|  2 |     1 | Bob   | M      |    92 |  1 | 火箭班    |
|  3 |     1 | Carol | F      |    90 |  1 | 火箭班    |
|  4 |     2 | David | M      |    98 |  2 | 自动化    |
+----+-------+-------+--------+-------+----+-----------+

如果没有 ON，那么就不会筛选数据，直接取两个表的直积（16个数据）

AS 给表取别名

上面表中 id 和 name 都重复出现了，如果不用 SELECT *，而是只取部分列，要用“表名.列名”的方式区分开（students.id 和 classes.id），而表的名字多次出现 太长了，可以为他设置别名（AS 可以省略）。

SELECT s.id, c.name AS classname, s.name, s.gender, s.score FROM students AS s 
INNER JOIN classes AS c
ON s.class=c.id; 

这样就符合我们的阅读习惯了：

+----+-----------+-------+--------+-------+
| id | classname | name  | gender | score |
+----+-----------+-------+--------+-------+
|  1 | 火箭班    | Alice | F      |    90 |
|  2 | 火箭班    | Bob   | M      |    92 |
|  3 | 火箭班    | Carol | F      |    90 |
|  4 | 自动化    | David | M      |    98 |
+----+-----------+-------+--------+-------+

各种形式的 JOIN

• INNER JOIN：将集合取直积，并只返回符合条件的数据。

• LEFT (OUTER) JOIN：确保左表的每一行都保留，如果左表有一行配不上任何右表，他仍会显示出来，在右表有关的列显示 NULL。

• RIGHT (OUTER) JOIN：同上，确保左表的每一行都保留。如果把上面的例子改成 RIGHT JOIN，就会多出一行没有人的“战神班”，其他列全为 NULL

• FULL (OUTER) JOIN：结果上，FULL JOIN 同时拥有 LEFT JOIN 和 RIGHT JOIN 多出来的行。MySQL 是不支持 FULL JOIN 的。

数据库操作和关系模型

Primary Key 主键

关系数据库里，每一行是一条“记录”，每一列是一个“字段”。两条记录不能完全相同，至少要有一个能区分他们的字段，使每条数据都不相同，这个字段叫做主键（Primary Key）。比如在上面的 students 表里，性别和分数可以相同，名字也可以一样，id就适合成为主键，每个 id 可以唯一定位到一个人。

主键是用来直接定位一个记录的，一旦插入就最好不要更改。

数据类型

这是关系数据库，所以每个字段要有唯一的类型，在 students 表中，id、score是整数，而 name、gender则是字符串。

一些类型	描述
INT	32位有符号整数
BIGINT	64位有符号整数
REAL/FLOAT	32位IEEE浮点
DOUBLE	64位IEEE浮点
DECIMAL(p,s)	精确十进制实数，最多p位，小数位最多s位
CHAR(n)	定长度为 n 的字符串
VARCHAR(n)	变长字符串，最长为n
BOOLEAN	true或者false
DATE/TIME/DATETIME	储存日期，时间。不同数据库规则有差异

一般最常见的就是整数 INT 和 字符串 VARCHAR(n)。

Constraints 约束

每一个字段都可以有自己的约束规则，比如不能为空(NOT NULL)，唯一（UNIQUE）等等。上面的PRIMARY KEY也是一种约束。约束在创建表的时候就可以添加在数据类型后面或者使用CONSTRAINT单独添加。

CREATE TABLE table_name(
	column_name1 type constraint_name,
	column_name2 type constraint_name,
	column_name3 type constraint_name,
	CONSTRAINT constraint_name ……
);

CREATE TABLE Orders(
	O_Id int NOT NULL,
	OrderNo int NOT NULL CHECK (OrderNo>0),
	P_Id int,

	PRIMARY KEY (O_Id),
	CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

• NOT NULL：表示本字段不接受 NULL 值，必须给定值。

• UNIQUE：保证该字段在表中不重复

• PRIMARY KEY：表示这是主键，主键只能有一个，数据上的限制相当于 NOT NULL 加上 UNIQUE

• FOREIGN KEY：表示这是外键，每个外键中的值对应另一个表中的 UNIQUE，比如 students.class 就是指向 classes.id 的外键。逻辑上， students.class 就是用来对应 classes.id 的，即使没有显式指明外键也能连接查询；指定外键后语义更明确，程序会预防插入非法值，外键必须在另一个表里存在，但使用外键也会占用更多的资源。

• CHECK：为字段添加条件约束，如CHECK (OrderNo>0)限制只能取正数

• DEFAULT：为字段添加默认值，如score INT DEFAULT 60不给定分数的值，默认赋值60

• AUTO_INCREMENT：表明该字段无需赋值，每次创建时都会自动加一，多用于没有实际意义的主键。

NULL 的应用

没有限定NOT NULL，没有默认值又没有给定值的数据就是 NULL。WHERE 条件中判定 NULL 不能用等于号。

SELECT score FROM students WHERE score IS NOT NULL; --IS NULL表示是NULL；IS NOT NULL表示不是NULL
SELECT id, IFNULL(score,0) FROM students; --有分数的输出分数，分数为NULL的输出成0

函数

SQL 语句里提供丰富的函数供我们使用，一类是聚合（aggregate）函数，另一类是标量（scalar）函数。

标量函数

这些函数进一个值，出一个值，没有什么特殊的。如：

• LEN()：字符串长度
• ROUND()：浮点数四舍五入
• UCASE()：字符串转为大写
• NOW()：当前时间

聚合函数

聚合函数参数是表中的一列，输出一个数。

• AVG()：返回平均值
• SUM()：返回和
• COUNT()：返回长度
• MIN() MAX()：返回最值

SELECT COUNT(id) FROM students; --一共有多少个id，即数据数量，输出列名就是COUNT(id)，只有一条数据
SELECT AVG(score) FROM students; --求平均分
SELECT class, AVG(score) FROM students; --这是错误的，class列有四行，AVG(score)只有一行，拼不出表

GROUP BY 分组查询

上面的第三句失败了，如果我想查询每个班的平均分，那应该使用 GROUP BY 查询：

SELECT class, AVG(score) FROM students 
GROUP BY class;

按照class分组，每一组计算一个 AVG(score)，所以 AVG(score) 有了三行，class也是三行，输出的就是每个班的平均分。GROUP BY也可以接受几个值，把值不一样的都分开统计，比如GROUP BY class, gender可以统计每个班每个性别的平均分。

+-------+------------+
| class | AVG(score) |
+-------+------------+
|     1 |    90.0000 |
|     2 |    95.0000 |
|     3 |    90.0000 |
+-------+------------+

GROUP BY 想和条件筛选一起用怎么办？WHERE 可以在 GROUP BY 之前用，但不能放在 GROUP BY 之后，如果是函数算出的东西还要筛选，HAVING 就排上了用场。

SELECT class, AVG(score) FROM students GROUP BY class
HAVING AVG(score)>90; --筛选平均分大于90的班