数据库与SQL
本文内容主要来自Sololearn的SQL课程以及课程的评论区。
自动生成数据可使用GenerateData
主要内容：
本文所涉及的内容及顺序：
- 数据库和SQL都是什么?
- 数据库查询。
- 数据操作。怎么修改一个表中的数据，即插入数据、更新数据、删除数据
- 表结构的操作。表的创建、修改与删除，列的增加、删除、和修改。
概念
数据库
数据库是一个便于访问的数据组织形式，同时可以高效管理和升级。以表的形式存储相关信息。
数据表以行和列的结构来存储和显示数据，看起来就行Excel那样。数据库一般包含多张表，每张表根据要存储的数据不同，包含它自己的字段集合。
SQL
Structured Query Language 结构化查询语言，方便访问和操作数据库。 SQL是一个ANSI标准。就是说说SQL只是一个语言标准，但不是语言。它在不同的数据库引擎上，有不同的实现版本。每个都在主要命令的基础上有自己的扩展。本文使用MySQL引擎。
SQL常用于：
- 插入、更新、删除数据。
- 创建新的数据库、表、存储过程、视图。
- 从数据库取回数据。
注意：
- 关于大小写：
- SQL语句大小写不敏感。在SQL语句中：大小写无关执行效率，Oracle推荐大写，具体看个人，保持统一的写法就好，不仅方便阅读理解，也会减少不必要的缓存。事实上，即使是小写，经过词法分析后也会自动转换为大写，只是可能会增加一丁点的编译时间。
- 数据库内部表名列名，一些人说的是大小写敏感的，可能在一些引擎确实如此。但在MySQL(我使用的是5.7.20)所有的表名、列名都会被转换为小写，不管是创建还是查询。所以你无法同时添加Name和name两个字段，查询NAme和name也没有区别。所以这样看，至少可以说MySQL 5.7.20(截至2017.10.30日，最新的稳定版本)是完全大小写不敏感的。
- 词之间的空格和空行会自动被忽略，所以分成多行写SQL会使语句比较清晰，在一些数据库中如果使用tab缩进的话需要在后面加上空格，不然会被识别为一个词。
- 每个完整的语句都由一个'；'结尾，切记。
- 值为文本时，需要将值用单引号包裹起来。不能用双引号。
- 关于存储过程，课程里没有提及。查了一下，我大致说一下。首先数据库指令是先编译后执行的，这也是为什么上边说大小写不敏感。就像在写程序的时候我们会把一些代码提取成一个方法（为了复用、稳定性等），存储过程也就像一个方法块或者说脚本，只不过他是编译过的，不需要再次编译，可以直接拿来执行一些操作。我们不需要每次都去写一些重复的SQL语句。
先从用的最多的查开始。事实上，当一个人进入一家公司，需要接触已有的数据库时，这也是需要最先掌握的。
查
这一部分有这些内容：
- USE语句
- SHOW语句
- SELECT语句
- 多个查询
- DISTINCT标识
- LIMIT语句
- ORDER BY语句
- 自定义数据列
- 子查询
- 多表操作，JOIN、UNION
USE语句
选择当前要使用的数据库。之后可以在该数据库执行增加表、修改表等操作。
Tips:
- 选完数据库后，在操作表时可以直接使用表名，类似文件中的相对路径。
- 如果想要选择到别的数据库中的表，也可以使用全称表名(databaseName.tableName)，数据库名和表名之间用','隔开。就像我们说文件中的绝对路径。即使你选择了数据库B，也可以使用这种方式选择到数据库A中的某张表。
SHOW语句
显示数据库或表中的信息，帮助你跟踪了解数据库的内容，提醒你表的结构。
-
SHOW DATABASES; 显示所有的数据库。 -
SHOW TABLES;显示某个数据库中的所有表。需要先使用USE databaseName 选择到特定数据库。 -
SHOW COLUMNS FROM tableName;显示某个表中的所有列信息。DESC，DESCRIBE直接加表名有同样效果。(MySQL 5.7.20)。显示的信息如下（注意空和NULL的区别，NULL是应该有值但是现在还没有，空这是没有那个属性。）：-
Field，列名 -
Type，列的数据类型，数字字符等。比如char(1)，int(11)等。 -
Null，指示列是否可以为空。对应NOT NULL。比如：NO，YES。 -
Key，指示列是否被索引。目前发现：设置了主键的为PRI，其他为空。 -
Default，列的默认值。比如NULL。 -
Extra，可能包含给定列的所有附加信息，比如自增长。也可能为空。
-
SELECT语句
用于从一个数据库选择数据，结果会被存储在一个结果表(result table)中，被称为result-set。
一次查询可能会从一列或多列中取回数据。
SELECT column_lists FROM table_name
column_lists包含会被返回的列；table_name指示从哪个表获取数据。
多个查询
可以同时执行多个查询。
SELECT column1 FROM table1;
SELECT column2 FROM table1;
这种写法会返回两个结果集。
也可以一次选择多列，用,隔开。需要选择所有可以用*(all)替换列名。
SELECT column1,column2 FROM table1；
这种写法会把之前那种方式的两个结果集整合为一个结果集。
DISTINCT标识
SELECT DISTINCT age FROM students;
去掉重复的结果。
LIMIT语句
限制返回的数据条目，要放在语句最后。
SELECT * FROM table1 LIMIT 3,5;
上边的句子中，LIMIT 3,5，代表把第三行看作第0行（第0行不是实际的行，而是一个作为起始点的假定行），并开始显示第4行，一共显示5行。
Tips：
- LIMIT 100相当于LIMIT 0,100；
- 平常的查询中，不做特殊声明的情况下，一般会有一个默认的Limit。可以发现不写limit其实会被执行为... LIMIT 0,1000。也就是默认返回前1000条数据。
ORDER BY语句
配合SELECT 对返回的数据依据某个字段进行排序。默认升序排列(ASC)，也可以在列名(字段)后面加上DESC，降序排列。
ORDER BY Age
也可以指定多个排序列名，用,分开。当第一个字段有重复的时候，会根据第二个字段对重复的进行排序...
因为升序降序是对列名起作用的。所以你可以这样写：
ORDER BY age, weight DESC
//意思是：先按照age 升序排列，再安装weight 降序排列。
WHERE语句
过滤结果集，用来提取符合条件的记录。
SELECT column_lists
FROM table_name
WHERE condition；
表达式的构建
比较操作符，用于构建基础的表达式。
| 操作符 | 意义 |
|---|---|
| = | 相等 |
| != | 不等， 在ANSI中的标准写法是<> |
| > | 大于 |
| < | 小于 |
| <=/>= | 小于等于/大于等于 |
| BETWEEN | 在一个范围之内，需要配合and使用，前面的需要比后面的小。 有的引擎会排除第一个和最后一个，所以如果不确定的话，结合使用>=和<=会更加稳定。 |
连词操作符，连接不同的基础表达式，构建一个复合表达式。
| 操作符 | 意义 |
|---|---|
| AND | 与，两边都为TRUE时，结果为TRUE |
| OR | 或，两边有一个为TRUE时，结果即为TRUE |
| IN | 操作与一组表达式中的一个相等，结果即为TRUE |
| NOT | 如果表达式不为TRUE，则结果为TRUE |
注意：IN，需要用 '（）' 将内容包裹起来，可以看作多个OR的简化写法。比如选出家在A,B,C的学生
SELECT *
FROM students
WHERE address IN ('A','B','C');
同理，NOT IN 为排除。需要注意，请确保你不需要隐藏NULL值，比如上边的情况，如果address为NULL的使用NOT IN也会被添加到结果集中。
Like表达式
LIKE用于特征匹配。
_匹配单个字符，%匹配任意数量个字符。
比如：
SELECT name, cost
FROM items
WHERE name LIKE '%boxes of frogs' AND seller_id IN (68, 6, 18);
自定义数据列
对返回的数据进行一些处理
CONCAT
SELECT CONCAT("id为",id,"的同学，名字是：",name)
FROM students;
将students里的每个学生信息以 id为1的同学，名字是：张二狗 这样的格式返回，如下图，注意表头。注意这里面的自定义文字不是数据值，需要使用双引号。
使用CONCAT
AS ALIASING，别名。
可以用于将返回的结果的列起一个别名。比如重新写上边的那个语句，注意表头。
SELECT concat("id为",id,"的同学，名字是：",name) AS Detail
FROM students;
使用AS
数字运算符，+-*/
就是可以对返回的数据做一些处理， 只影响返回的数据，不影响数据库里的数据。
SELECT ID, FirstName, LastName, Salary+500 AS Salary
FROM employees;
方法
- UPPER/LOWER对返回的数据进行大小写转换处理。
- SQRT，求平方根
- AVG，求平均
- SUM，求和。
- MIN，查找最小值
子查询/嵌套查询
比如查出平均后，再查出高于平均的数据。
SELECT FirstName, Salary FROM employees
WHERE Salary > (SELECT AVG(Salary) FROM employees)
ORDER BY Salary DESC;
多表操作
之前的所有查询都是一次针对一张表进行的。而SQL最大的好处之一是多表查询。
多表查询，在遇到比较长的表名时，对表名使用别名会更加方便。 这种情况下AS可以省略
SELECT ct.ID, ct.Name, ord.Name, ord.Amount
FROM customers AS ct, orders ord
WHERE ct.ID=ord.Customer_ID
ORDER BY ct.ID;
JOIN
joining tables意思是连接多个表的数据，Join创建了一个临时的表用于展示被连结的数据。 在JOIN中的条件需要使用ON关键字。
Join的类型(注意在使用时，OUTER是可以省略的)：
-
INNER JOIN，相当于JOIN，返回各个表中匹配的部分。
-
LEFT OUTER JOIN，即使右侧的表中没有匹配的数据，也会显示左侧表中所有条目。比如要统计学生选了什么课，有些学生即使没有选课也会被显示出来（选课表中没有该学生选课的信息，显示在查询结果里会是某某某选课为NULL）。
-
RIGHT OUTER JOIN，同理，即使左侧表中没有匹配的数据，也会显示右侧表中所有条目。
比如我们查出所有同学的成绩：
SELECT stu.name,scores.score
FROM students stu LEFT JOIN scores
ON stu.name=scores.stu_name;
UNION数据整合
UNION也是针对多表，JOIN一般用于查询数据的关联部分，比如通过一家医院的病人信息表和药品销售表，查出哪些病人买了什么药。而UNION一般用于整合数据，比如查出市里所有医院的病人信息，需要整合各个医院的病人信息表。使用UNION，或许需要把多个表的数据连结到一个综合数据表里，有时候需要跨数据库，甚至跨服务器。
UNION的类型：
- UNION 连结多个数据集到一个单独的数据集，并且移除重复的。
- UNION ALL 类似UNION，但不移除重复的。所以它要比UNION 快一点。
连结多个SELECT语句的数据集，使用：
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
需要使用UNION合并的查询结果集必须具有相同的列、列名、数据格式、以及一致的列顺序。如果你需要一种信息，但在另外的数据库里没有该信息，你可以在加一个NULL列来匹配查询。比如下边的：
SELECT FirstName, LastName, Company FROM businessContacts
UNION
SELECT FirstName, LastName, NULL FROM otherContacts;
数据的增删改
这一部分有这些内容：
-
往表里插入数据
-
更新表中的数据
-
删除表中的数据
插入数据
INSERT INTO tableName（columnsName）
VALUES(a,b,'c',12);
关于columnsName：
- 可以不指定，则按照列的顺序来，需要确保和列的顺序、数据类型都一致。
- 按照一定的顺序指定某几个列，则后面添加数据时也要按照指定的顺序。如果未给表中某列分配数据，请确保它有默认值或可以为NULL
列名和值都用' , '隔开。
支持多行同时插入：
INSERT INTO customer (ID, FirstName, LastName, Age)
VALUES
(1, `Emily`, `Adam`, 34),
(2, `Chloe`, `Anderson`, 27),
(3, `Daniel`, 'Harris`, 30),
(4, ` James`, `Robert`, 31),
(5, `John`, `Smith`, 35),
(6, `Andrew`, `Thomas`, 45),
(7, `David`, `Williams`, 23),
(8, `Anthony`, `Young`, 35);
Tips：使用时可能会发现有VALUES和VALUE，它们效果一样，没有区别，都是合法的。
更新数据
UPDATE table_name
SET column1=value1, column2=value2, ...
WHERE condition;
注意：
- 如果不跟WHERE则所有的数据都会被修改。
- 默认是安全模式，而在安全模式下，必须有WHERE，且条件必须是主键。
- 在SET里，可以不按顺序来。
插入或更新数据
把 INSERT 换为 REPLACE，其他和插入的语法一样。比较好用。
REPLACE INTO tableName（columnsName）
VALUES(a,b,'c',12);
删除数据
DELETE FROM table_name
WHERE condition;
限制类似Update；
Tips：该操作是永久的，所以在执行DELETE之前，先将DELETE替换为SELECT * ，看看你会删除什么数据。确定可以删除再执行DELETE。
涉及表结构的增删改
这一部分有这些内容：
- 表的创建、删除和重命名。
- 列的增加、删除、修改。
表
支持的数据类型：
- 数字
- INT，正常大小的数字。可以被标记为有符号或没符号。
- FLOAT(M,D)，单精度浮点数，不能被标记。可以定义显示长度M和小数位数D
- DOUBLE(M,D)，双精度浮点数，其他同上。
- 时间和日期
- DATE - YYYY-MM-DD 格式
- DATETIME - YYYY-MM-DD HH:MM:SS格式
- TIMESTAMP - Linux时间戳，从1970年1月1日午夜开始计算。
- TIME - HH:MM:SS时间格式
- 字符类型
- CHAR(M)，固定长度字符串。M为指定的长度，最大255bytes.
- VARCHAR(M)， Variable-length character string 可变长度字符串，括号指定最大值。
- BLOB，二进制对象，一般用于存储大量的二进制数据。比如图片其他文件等。
- TEXT，大量的文字。
一个数据库里可以存放多个表，每个都扮演着自己独特的角色。
表由行和列组成，每列负责存储不同的数据类型，包括数字、文字、数据，甚至是文件。
约束语句
作用与表中数据的需要遵守的规则，在表创建时，可以在某列的数据类型后面指定约束。
- PRIMARY KEY : 强制表中某列只接受独特的值，不能为空。并且为之创建索引可用于快速访问。是表中数据的独特标识，每张表只允许有一个主键。
- NOT NULL：指示该列不能包含任何NULL值
- UNIQUE：该列不能插入重复的值。
- CHECK : 通过一个逻辑表达式决定值是否有效。 EmployeeId int Not Null Check (EmployeeId > 0)，如果数据中该值大于0则拒绝插入该数据。
- DEFAULT：插入数据时，如果没有向该列提供值，则该列从DEFAULT获取值。
- AUTO INCREMENT：自增长允许我们在插入数据的时候自动生成一个独特的数字。通常我们都将其用于主键。默认情况下，它从1开始，每次增加1。注意：数据库不一样实现方式可能也不一样，比如MS SQL中使用的是 IDENTITY标识。
创建表
CREATE TABLE语句用于创建一个新表。创建表意味着三件事
- 命名新表。
- 确定有哪些字段（列）。
- 确定每列的数据类型和大小。
比如下边的建表语句（注意该语句对表是否存在做了判断，并且单独设置了主键，其实也可以像其他的约束一样，写在某列的数据类型后面）：
CREATE TABLE IF NOT EXISTS table_name
(
column_name1 data_type(size) NOT NULL AUTO_INCREMENT,
column_name2 data_type(size),
column_name3 data_type(size),
....
columnN data_type(size),
PRIMARY KEY(column_name1)
);
删除表
DROP TABLE tableName;
重命名表
RENAME TABLE People TO Users
ALTER TABLE/列的修改
用于表中列的增删改（ADD、DROP、CHANGE，CHANGE包含了重命名、增加和删除约束。名字不变时，即修改了列定义）。
ALTER TABLE tableName
ADD newColumn date NOT NULL;
ALTER TABLE tableName
DROP COLUMN columnName;
ALTER TABLE tableName
CHANGE columnName newName datatype(size) NULL；
Tips： 在MySQL中，如果新添加的列指定了NOT NULL，会为已经有的数据添加列的数据类型的默认值。比如列的数据为date，不指定NOT NULL则该列为null，否则值为0000-00-00。
VIEW
VIEW是一个基于结果集的虚拟的表。就像一个真实的表一样，它也有行和列，它里面的数据都来自一个或多个真实的表。
我们可以使用View:
- 结构化数据，以使各种用户可以直观自然的查找数据。
- 限制数据访问，使用户可以只接触和操作到他们可以需要的数据。
- 汇总各个表格的数据，生成报表。
创建
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;
之后可以通过SELECT语法去访问该view。
注意：
- View的名称不能和表相同。
- View的数据都是最新的，每次你去查询一个view的时候，数据库引擎都会使用view的SQL语句去重新创建它的数据。
- View并非只读的，在一些情况下，你也可以通过它对基础表的数据进行插入、删除操作(MySQL - Updatable and Insertable Views)。比如我建了一个View，使用的是SELECT * ，也就是说我的View和基础表是完全对应的。之后我做了插入数据的操作，执行更新，发现View的数据包含了我插入的数据，去查询表，发现数据确实已经更新。
修改View
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;
删除View
DROP VIEW viewName;
