SQL基础

数据库

通过命令符直接进入数据库

mysql -uroot -ppassword

SQL是访问和处理关系数据库的计算机标准语言。

NoSQL是非SQL的数据库，包括MongoDB、Cassandra、Dynamo等等，它们都不是关系数据库。有很多人鼓吹现代Web程序已经无需关系数据库了，只需要使用NoSQL就可以。但事实上，SQL数据库从始至终从未被取代过。回顾一下NoSQL的发展历程：

• 1970: NoSQL = We have no SQL
• 1980: NoSQL = Know SQL
• 2000: NoSQL = No SQL!
• 2005: NoSQL = Not only SQL
• 2013: NoSQL = No, SQL!

数据库关系概述

数据库是作为一种专门管理数据的软件出现的。应用程序不需要自己管理数据，而是通过数据库软件提供的接口来读写数据。至于数据本身如何存储到文件，那是数据库软件的事情

数据类型

数据库按照数据结构来组织、存储和管理数据，实际上，数据库一共有三种模型：

• 层次模型
• 网状模型
• 关系模型
  层次模型就是以“上下级”的层次关系来组织数据的一种方式，层次模型的数据结构看起来就像一颗树：
  
  网状模型把每个数据节点和其他很多节点都连接起来，它的数据结构看起来就像很多城市之间的路网：
  
  关系模型把数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定，它的数据模型看起来就是一个Excel表：
  
  在目前，基于关系模型的关系数据库获得了绝对市场份额。

关系数据库的关系模型是基于数学理论建立的。
一个班的学生可以用一个表格存起来，定义如下

ID 姓名 班级ID 性别 年龄
1 小明 201 M 9
2 小红 202 F 8
3 小军 202 M 8
4 小白 201 F 9

其中，班级ID对应着另一个班级表：
ID 名称 班主任
201 二年级一班 王老师
202 二年级二班 李老师

通过给定一个班级名称，可以查到一条班级纪录，根据班级ID，又可以查到多条学生记录，这样，二维表之间就通过ID映射建立了“一对多”的关系。

数据类型

名称 类型 说明

INT	整型	4字节整数类型，范围约+/-21亿
BIGINT	长整型	8字节整数类型，范围约+/-922亿亿	
REAL	浮点型	4字节浮点数，范围约+/-1038
DOUBLE	浮点型	8字节浮点数，范围约+/-10308
DECIMAL(M,N)	高精度小数	由用户指定精度的小数，例如，D		ECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算
CHAR(N)	定长字符串	存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串
VARCHAR(N)	变长字符串	存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串
BOOLEAN	布尔类型	存储True或者False
DATE	日期类型	存储日期，例如，2018-06-22
TIME	时间类型	存储时间，例如，12:20:59
DATETIME	日期和时间类型	存储日期+时间，例如，2018-06-22 12:20:59

很多类型还有别名，例如，REAL又可以写成FLOAT（24）。还有一些不常用的数据类型，例如，TINYINT（范围在0-255）。各数据库厂商还会支持特定的数据类型，例如JSON。

主流关系数据库
目前，主流的关系数据库主要分为以下几类：
1.商用数据库，例如：Oracle，SQL Server，DB2等；
2.开源数据库，例如：MySQL，PostgreSQL等；
3.桌面数据库，以微软Access为代表，适合桌面应用程序使用。
4.嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序

SQL

SQL是结构化查询语言的缩写，用来访问和操作数据库系统，SQL语句既可以查询数据库中的数据，也可以添加、更新和删除数据库中的数据，还可以对数据库进行管理和维护操作。不同的数据库都支持SQL。

SQL语言定义了这么几种操作数据库的能力：
DDL：允许用户定义数据，也就是创建表，删除表、修改表结构这些操作。通常，DLL由数据库管理员执行。
DML：为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。
DQL：允许用户查询数据，也是通常最频繁的数据库日常操作。

语法特点：

SQL语言关键字不区分大小写！！！但是，针对不同的数据库，对于表名和列名，有的数据库区分大小写，有的数据库不区分大小写。同一个数据库，有的在Linux上区分大小写，有的在Windows上不区分大小写。

该博客中SQL关键字总是大写，以示突出，表名和列名均使用小写。

关系模型

表的每一行称为记录，记录是一个逻辑意义上的数据
表的每一列称为字段，同一个表的每一行记录都拥有相同的若干字段

字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为NULL，注意NULL表示字段数据不存在，一个整型字段如果为NULL不表示它的值为0，同样的，一个字符串型字段为NULL也不表示它的值为空串’ ‘

通常情况下，字段应该避免允许为NULL，不允许为NULL可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断是否为NULL。

和excel表有所不同的是，关系数据库的表和表之间需要建立“一对多”，“多对一”和“一对一”的关系，这样才能够按照应用程序的逻辑来组织和存储数据。

班级表
ID 名称 班主任
201 二年级一班 王老师
202 二年级二班 李老师
每一行对应着一个班级，而一个班级对应着多个学生，所以班级表和学生表的关系就是“一对多”：

ID 姓名 班级ID 性别 年龄
1 小明 201 M 9
2 小红 202 F 8
3 小军 202 M 8
4 小白 201 F 9

ID 姓名 班级ID 性别 年龄
1 小明 201 M 9
2 小红 202 F 8
3 小军 202 M 8
4 小白 201 F 9
如果我们先在学生表中定位了一行记录，例如ID=1的小明，要确定他的班级，只需要根据他的“班级ID”对应的值201找到班级表中ID=201的记录，即二年级一班。所以，学生表和班级表是“多对一”的关系

在关系数据库中，关系是通过主键和外键来维护的。

主键

在关系数据库中，一张表中的每一行数据被称为一条记录。一条记录就是由多个字段组成的。例如，students表中的两行记录：

id class_id name gender score
1 1 小明 M 90
2 1 小红 F 95

每一条记录都包含若干定义好的字段。同一个表的所有记录都有相同的字段定义。

对于关系表，有个很重要的约束，就是任意两条记录不能重复，不能重复指的不是两条记录不完全相同，而是指能够通过某个字段唯一分出不同的记录，这个字段被称为主键。

假设我们把name字段作为主键，那么通过名字小明或小红就能唯一确定一条记录，但是，这么设定，就没法存储同名的同学了，因为插入相同主键的两条记录是不被允许的。

对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。

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

因此，选取主键的一个原本原则是：不使用任何业务相关的字段作为主键。

我们一般把与业务无关的主键称为id，常见的可作为id字段的类型有：
1.自增整数类型：数据库会再插入数据时自动为每一条记录分配一个自增整数，这样不用担心主键重复，也不用自己预先生成主键。

2.全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似
8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算处主键。

对于大部分应用来说，通常自增型的主键就能满足需求。我们在students表中定义的主键也是BIGINT NOT NULL AUTO_INCREMENT类型。

如果使用INT自增类型，那么当一张表的记录数超过2147483647（约21亿）时，会达到上限而出错。使用BIGINT自增类型则可以最多约922亿亿条记录。

联合主键

关系数据库实际上海允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。

对于联合主键，允许一列有重复，只要不是所有主键列都重复即可：

id_num id_type other columns…
1 A …
2 A …
2 B …

如果我们把上述表的id_num和id_type这两列违联合主键，那么上面的三条记录都是允许的，因为没有两列主键组合起来是相同的。

没有必要的情况，我们尽量不使用联合主键，因为它给关系表点来了复杂度的上升。

外键：

当我们用主键唯一标识记录时，我们就可以在students表中确定任意一个学生的记录。

id name other columns…
1 小明 …
2 小红 …

我们还可以在classes表中确定任意一个班级记录：

id name other columns…
1 一班 …
2 二班 …

这两个表的关系可以称为“一对多”，即一个classes的记录可以对应多个students表的记录。

为了表达这中一对多的关系，我们需要在students表中加入一列class_id，让它的值与classes表的某条记录相对应：

id class_id name other columns…
1 1 小明 …
2 1 小红 …
5 2 小白 …

这样我们可以根据class_id这个列直接定位出一个students表记录应用对应到classes的那条记录。

在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键。

外键并不是通过列名实现的，而是通过定义外键约束实现的。

ALTER RABLE students
ADD CONSREAINT  fk_class_id
FOREIGN KEY  (class_id)
REFERENCES    class(id)；

其中，外键约束的名称fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）。

通过定义外键的约束，关系数据库可以保证无法插入无效的数据。

由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class_id仅仅是一个普通的列，只是它起到了外键的作用而已。

要删除一个外键约束，也是通过ALTER TABLE实现的

ALTER TABLE students
DROP FOREIGN KEY fk_class_id；

注意：删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN实现的。

多对多

通过一个表的外键关联到另外一个表，我们可以定义出一对多关系。有些时候，还需要定义“多对多”关系。例如，一个老师可以对应多个班级，一个班级也可以对应多个老师，因此，班级表和老师表存在多对多关系。

多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系就形成了多对多关系：
teachers表：

id name
1 张老师
2 王老师
3 李老师
4 赵老师

classes表：
id name
1 一班
2 二班

中间表teacher_class关联两个一对多关系：

id teacher_id class_id
1 1 1
2 1 2
3 2 1
4 2 2
5 3 1
6 4 2

因此，通过中间表，我们就定义了一个“多对多”关系

一对一

一对一关系是指，一个表的记录对应到另外一个表的唯一记录。

例如，students表中的每一个学生都有自己的联系方式，如果把联系方式存入另一个表contacts中，我们就可以得到一个”一对一“关系：

id student_id mobile
1 1 135xxxx6300
2 2 138xxxx2209
3 5 139xxxx8086

还有一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。例如，把一个大的用户表分拆为用户基本信息表user_info和用户详细信息表user_profiles，大部分时候，只需要查询user_info表，并不需要查询user_profiles表，这样就提高了查询速度。

索引

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

例如，students表:
id class_id name gender score
1 1 小明 M 90
2 1 小红 F 95
3 1 小军 M 88

ALTER TABLE students
ADD INDEX idx_score(socre)；

使用ADD INDEX idx_score(score)就创建了一个名称为idx_score，使用列score的索引。索引名称是任意的，索引如果有多列，可以在括号里依次写上，例如：

ALTER TABLE students
ADD INDEX idx_name_score(name,score)；

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

可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入，更新和删除记录时，需要同时修改索引，因此，索引越多，插入，更新和删除记录的速度就越慢。

对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。

唯一索引

可以给看上去唯一的列添加一个唯一索引，假设students表的name不能重复：

ALTER TABLE students
ADD UNIQUE INDEX uni_name（name）;

通过UNIQUE关键字我们就添加了一个唯一索引

也可以只对某一列添加一个唯一约束而不创建唯一索引；

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE(name);

这种情况下，name列没有索引，但仍然具有唯一性保证。

无论是否创建索引，对于用户和应用程序来说，使用关系数据库不会有任何区别，当我们在数据库中查询时，如果有相应的索引可用，数据库系统就会自动使用索引来提高查询效率。

查询数据

基本查询

SELECT * FROM <表名>

假设表名是students，要查询students表的所有行，我们用如下SQL语句：

SELECT * FROM students;

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

查询结果也是个二维表，它包含列名和每一行的数据。

SELECT 语句其实并不要求一定要有FROM字句，比如

SELECT 100+200;

300

上述查询会直接计算出表达式的结果，虽然可以SELECT可以用作计算，但它并不是SQL的强项，但是，不带FROM字句的SELECT语句有一个有用的用途，就是用来判断当前到数据库的链接是否有效。许多检测工具会执行一SELECT 1；来测试数据库连接。

条件查询

SELECT语句可以通过WHERE条件来设定查询条件，查询结果是满足查询条件的记录。例如，要指定条件“分数在80分或以上的学生”，写成WHERE条件就是

SELECT * FORM students WHERE score >= 80

其中，WHERE关键字后面的score >= 80 就是条件，score是列名，该列存储了学生的成绩，因此score >= 80就筛选出了指定条件的记录

条件查询的语法为

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

条件表达式可以用<条件1> AND <条件2>表达满足条件1并且满足条件2。例如，把符合条件“分数在80分或以上”且 性别为“男”

SELECT * FROM students WHERE score >= 80 AND gender = 'M';

第二种条件是<条件1> OR <条件2>，表示满足条件1或者满足条件2。例如，把符合条件“分数在80分或以上”或 性别为“男”

SELECT * FROM students WHERE score >= 80 OR gender = 'M'

第三种条件是NOT <条件>，表示“不符合该条件”的记录。比如查询不是二班的学生

SELECT * FROM students WHERE NOT class_id  = 2;

上面的NOT class_id = 2 其实等价于class_id <>2，
要组合三个或者更多的条件，就需要用小括号（）来表示如何进行条件查询

如果不加括号，条件运算按照NOT，AND，OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。

WHERE 60 <= score <= 90，意思是score >=60 or score <=90

投影查询

使用SELECT * FROM <表名> WHERE <条件> 可以选出表中的若干条记录。我们注意到返回的二维表结构和原表是相同的，即结果集的所有列与原表的所有列都一一对应。

如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1，列2 FORM …，让结果集包含指定列。这种操作称为投影查询。

例如从students表中返回id、score和name这三列：

SELECT id,socre,name FROM students;

这样返回的结果集就只包含了我们指定的列，并且，结果集的列名就可以与原表的列名不同

此外，还可以给每一列起别名，这样，结果集的列名就可以与原表的列名不同。它的语法是SELECT 列1 别名1，列2 别名2 FROM …
例如，以下SELECT语句将列名score重命名为points，而id和name列名保持不变：

SELECT id,score points,name FROM students;

投影查询同样可以接WHERE条件，实现复杂的查询：
例如

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

排序

当我们使用SELECT查询时，查询结果集通常是按照id排序，也就是根据主键排序。

如果我们要根据其他条件排序，可以加上ORDER BY字句，如按照成绩从低到高

SELECT id,name,gender,score FROM students ORDER BY score;

按照成绩从高到低，可以加上DESC表示倒序。

SELECT id,name,gender,score FROM students ORDER BY score DESC;

默认的排序规则是ASC：“升序”，即从小到大。ASC可以省略，即ORDER BY score ASC
和ORDER BY score效果一样。

如果有WHERE字句，那么ORDER BY字句要放到WHERE字句后面。例如，查询一班学生成绩，并按照倒序排序：

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

分页

使用SELECT 查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。

要实现分页功能，实际上就是从结果集中显示第1-100条记录作为第一页，显示第101-200条记录作为第二页，依次类推。

因此，分页实际上就是从结果集中“截取”出第M-N条记录。这个查询可以通过LIMIT < M> OFFSET < N>字句实现。我们先把所有学生按照成绩从高到低进行排序：

SELECT id,name,gender,score FROM students ORDER BY score DESC;

现在，我们把结果集分页，每页三条记录。要获取第一页的记录，可以使用LIMIT 3 OFFSET 0

SELECT id,name,gender,score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

上述查询LIMIT 3 OFFSET 0 表示，对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始。

如果要查询第二页，那么我们只需要跳过头3条记录，也就是对结果集从3号记录开始查询，把OFFSET设定为3：

类似的，查询第3页的时候，OFFSET应该设定为6：

查询第4页的时候，OFFSET应该设定为9；

由于低4页只有一条记录，因此最终结果集按照实际数量1显示。LIMIT 3 表示的意思是“最多3条记录”。

可见，分页查询的关键在于，首先要确定每页需要显示的结果数量(pageSize)（这里是3），然后根据当前页的索引(pageIndex)（从1开始），确定LIMIT和OFFSET应该设定的值：

• LIMIT总是设定为pageSize
• OFFSET计算公式为pageSize * （pageIndex - 1）。
  这样就能正确查询出第N页的记录集

OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。

注意
OFFSET是可选的，如果只写LIMIT 15 ，那么相当于LIMIT 15 OFFSET 0。

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

使用LIMIT < M> OFFSET < N>分页时，随着N越来越大，查询效率也会越来越低。

聚合查询

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

仍然以查询students表一共有多少条记录为例，我们可以使用SQL内置的COUNT（）函数查询：

SELECT COUNT(*) FROM students;

COUNT（ *）表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT （ *）。

通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果：

SELECT COUNT(*) num FROM students;

COUNT（*）和COUNT（id）实际上是一样的效果。另外注意，聚合查询同样可以使用WHERE条件，因此我们可以很方便地统计出有多少男生，多少女生、多少80分以上的学生等。

SELECT COUNT(*) boys FROM students WHERE gender = 'M';

除了COUNT()函数外。SQL还提供了如下聚合函数：

函数	说明
SUM	计算某一列的合计值，该列必须为数值类型
AVG	计算某一列的平均值，该列必须为数值类型
MAX	计算某一列的最大值
MIN	计算某一列的最小值

注意，MAX（）和MIN（）函数并不限于数据类型。如果是字符类型，MAX（）和MIN（）会返回排序最后和排序最前的字符。
要统计男生的平均成绩，我们用下面的聚合查询：

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

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

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

分组

如果要统计一班的学生数量，可用SELECT COUNT(*) num FROM students WHERE class_id = 1；如果要继续统计二班，三班的学生数量呢；

对于聚合查询，SQL还提供了“分组聚合”的功能。

按class_id分组：

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

执行这个查询，COUNT（）的结果不再是一个，而是3个，这是因为，GROUP BY字句指定了按class_id分组，因此，执行该SELECT语句时，会把class_id相同的列先分组，再分别计算，因此，得到了3行结果。

这下结果集就可以一目了然地看出各个班级的学生人数。我们再试试把name放入结果集：

SELECT name,class_id,COUNT（*） num FROM students GROUP BY class_id;

name一列全为NULL，因为在任意一个分组中，只有class_id都相同，SQL引擎不能把多个name的值放入一行记录中。因此，聚合查询的列中，只能放入分组的列。

注意：AlaSQL并没有严格执行SQL标准，上述SQL在浏览器可以正常执行，但是在MySQL、Oracle等环境下将报错，请自行在MySQL中测试。

也可以使用多个列进行分组。例如，我们像统计各班的男生和女生人数：

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

上述查询结果分别对应各班级的男生和女生的人数。

多表查询

SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。查询多张表的语法是： SELECT * FROM < 表1> < 表2>。

同时从students表和classes表的“乘积”，即查询数据，可以这么写：

SELECT * FROM students,classes;

这种一次查询两个表的数据，查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。

这种多表查询又称笛卡尔查询，使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，对两个各自有100行记录的表进行笛卡尔查询将返回一万条记录，对两个各自有一万行记录的表进行笛卡尔查询将返回一亿条记录。

在查询结果中可能有两个表相同的列，这时不好区别

我们可以使用投影查询的“设置列的别名”来给两个表各自的id和name列起别名：

SELECT 
	students.id sid,
	students.name,
	students.gender,
	students.score,
	classes.id cid,
	classes.name cname
FROM students,classes;

多表查询时，要使用–表名.列名–这样的方式来引用和设置别名，这样就避免了结果集的列名重复问题。但是，用—表名.列名—这种方式列举两个表的所有列实现是很麻烦，所有SQL还允许给表设置一个别名，让我们在投影查询中引用起来稍微简洁一点；

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

注意到FROM字句给表设置别名的语法是FROM <表名1><别名1>，<表名2><别名2>。这样我用别名s和c分别表示students表和classes表。
多表查询也是可以添加WHERE条件的。

SELECT
	s.id sid,
	s.name,
	s.score,
	c.id cid,
	c.name cname
FROM students s,classes c
WHERE s.gender = 'M' AND c.id = 1；

连接查询

连接查询是另已中农类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

例如，我们想要选出students表的所有学生信息，可以用一条简单的SELECT语句完成。

SELECT s.id, s.name , s.class_id,s.gender,s.score FROM students s;

但是，假设我们希望结果集同时包含所在班级的名称，上面的结果集只有class_id列，缺少对应班级的name列。

现在问题是，存在班级名称的name列存储在classes表中，只有根据students表的class_id，找到classes表对应的行，再取出name列，就可以获得班级名称。

这时，连接查询就派上了用场。我们先使用最常见的一种内连接–INNER JOIN来实现：

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

id name class_id class_name gender score
1 小明 1 一班 M 90
2 小红 1 一班 F 95
3 小军 1 一班 M 88
4 小米 1 一班 F 73
5 小白 2 二班 F 81
6 小兵 2 二班 M 55
7 小林 2 二班 M 85
8 小新 3 三班 F 91
9 小王 3 三班 M 89
10 小丽 3 三班 F 88

注意INNER JOIN查询的写法是：

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

使用别名不是必须的，但可以更好地简化查询语句。
那什么是内连接（INNER JOIN）呢？先别着急，有内连接（INNER JOIN）就有外连接（OUTER JOIN）。我们把内连接查询改成外连接查询。

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;

id name class_id class_name gender score
1 小明 1 一班 M 90
2 小红 1 一班 F 95
3 小军 1 一班 M 88
4 小米 1 一班 F 73
5 小白 2 二班 F 81
6 小兵 2 二班 M 55
7 小林 2 二班 M 85
8 小新 3 三班 F 91
9 小王 3 三班 M 89
10 小丽 3 三班 F 88
NULL NULL NULL 四班 NULL NULL

执行RIGHT OUTER JOIN 可以看到，和INNER JOIN相比，RIGHT OUTER JOIN多了一行，多处来的一行是“四班“，但是学生相关的列如name、gender、score都为NULL。

这也容易理解，因为根据ON条件s.class_id = c.id，classes表的id = 4的行正是“四班”，但是，students表中并不存在class_id = 4的行。

有RIGHT OUTER JOIN，就有LEFT OUTER JOIN，以及FULL OUTER JOIN。 他们的区别是：

INNER JOIN只返回同时存在于两张表的行数据，由于students表的class_id包含1，2，3，classes表的id包含1,2,3,4，所以，INNER JOIN根据条件s.class_id = c.id返回的结果集仅包含1,2，3。

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

LEFT OUTER JOIN则是返回左表都存在的行。如果我们给students表增加一行，并添加class_id = 5，由于classes表并不存在id = 5的行，所以，LEFT OUTER JOIN的结果会增加一行，对应的class_name是NULL。

这么多种JOIN查询，该使用哪一种呢？我们通过用图来表示结果集就一目了然了。
假设查询语句是：

SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;

我们把tableA看作左表，把tableB看成右表，那么INNER JOIN是选出两张表都纯在的记录：

LEFT OUTER JOIN是选出左表存在的记录：

RIGHT OUTER JOIN是选出右表存在的记录：

FULL OUTER JOIN则是选出左右表都存在的记录：

JOIN查询需要先确定主表，然后把另一个表的数据“附加”到结果集上；

INNER JOIN是最常用的一种JOIN查询，它的语法是SELECT … FROM <表1> INNER <表2> ON <条件…>；

JOIN查询仍然可以使用WHERE条件和ORDER BY排序。

修改数据

关系库的基本操作就是增删查改，即CRUD：Create、Retrieve、Updata、Delete。其中，对于查询，我们已经详细讲述了SELECT语句的详细用法。

而对于增删改，对应的SQL语句分别是：

• INSERT：插入新记录；
• UPDATA：更新已有记录；
• DELETE：删除已有记录。

INSERT

INSERT语句的基本语法是：

INSERT INTO<表名>（字段1，字段2，…）VALUES（值1，值2，…）

例如，我们向students表插入一条新记录，先列举出需要插入的字段名称，然后再VALUES字句中依次写出对应字段的值：

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

我们并没有列出id字段，也没有列出id字段对应的值，这是因为id字段是一个自增主键，它的值可以由数据库自己推算出来。此外，如果一个字段有默认值，那么在INSERT语句中也可以不出现。

要注意，字段顺序不必和数据库表的字段顺序一致，但值的顺序必须和字段顺序一致。

还可以一次性添加多条记录，只需要在VALUES字句中指定多个纪录值，每个纪录是由（…）包含的一组值：

INSERT INTO stdents (class_id,name,gender,socre)VALUES
(1,'大宝','M',87),
(1,'大宝','M',87);

UPDATE

更新数据库表中的记录
UPDATE语句的基本语法是：

UPDATE<表名> SET 字段1=值1，字段2=值2，...WHERE...；

例如，我们想更新students表id=1的记录的name和score这两个字段，先写出UPDATE students SET name = ‘大牛’，score = 66。然后再WHERE字句中写出需要更新的行的筛选条件id=1；

注意到UPDATE语句的WHERE条件和SELECT语句的WHERE条件其实是一样的，因此完全可以一次更新多条记录；

UPDATE students SET name='小牛',score = 77 WHERE id>=5 AND id<=7;

在UPDATE语句中，更新字段时可以使用表达式。例如，把所有80分以下的同学的成绩加10分

UPDATE students SET score=score+10 WHERE score<80;

其中，SET score = score+10 就是给当前行的score字段的值加上了10。
如果WHERE条件没有匹配到任何记录，UPDATE语句不会报错，也不会有任何记录被更新。

最后，UPDATE语句可以没有WHERE条件，例如：

UPDATE students SET score = 60;

这时，整个表的所有记录都会被更新。所以，在执行UPDATE语句时要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE更新。

MySQL
使用MySQL这类真正的关系数据库时，UPDATE语句会返回更新的行数以及WHERE条件匹配的行数。
如，更新id=1的记录时：

mysql > UPDATE student SET name= '大宝' WHERE id= 1;
Query OK,1 row affected (0.00 sec)
Row matched:1 Changed:1 Warnings:0

MySQL会返回1，可以从打印的结果Rows matched：1 Changed ： 1看到。

当更新id = 999的记录时：

mysql> UPDATE students SET name='大宝' WHERE id=999;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

MySQL会返回0，可以从打印的结果Rows matched：0 Changed ：0 看到。

DELETE

DELETE语句的基本语法是：

DELETE FROM <表名> WHERE ...

例如，我们想删除students表中id=1的记录，就需要这么写

DELETE FROM students WHERE id = 1;

注意到DELETE语句的WHERE条件也是用来筛选需要删除的行，因此和UPDATE类似，DELETE语句也可以一次性删除多条记录：

DELETE FROM students WHERE id>=5 AND id<=7;

如果WHERE条件没有匹配到任何记录，DELETE语句不会报错，也不会有任何记录被删除。

最后，要特别注意的是，和UPDATE类似，不带WHERE条件的DELETE语句会删除整个表的数据；

DELETE FROM students;

这时，整个表的所有记录都会被删除。所以，在执行DELETE语句时也要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用DELETE删除。

在使用MySQL这类真正的关系数据库时，DELETE语句也会返回删除的行数以及WHERE条件匹配的行数。
例如，分别执行删除id=1和id=999的记录;

mysql> DELETE FROM students WHERE id=1;
Query OK, 1 row affected (0.01 sec)

mysql> DELETE FROM students WHERE id=999;
Query OK, 0 rows affected (0.01 sec)

MySQL

安装完MySQL后，除了MySQL Server ，即真正的MySQL服务器外，还附赠一个MySQL Client程序。MySQL Client是一个命令行客户端，可以通过MySQL Client登录MySQL，然后，输入SQL语句并执行。

打开命令提示符，输入命令mysql -u root -p ，提示输入口令。填入MySQL的root口令，如果正确，就连上了MySQL Server，同时提示符变为mysql>:

输入exit断开与MySQL Server的连接并返回到命令提示符。

MySQL Client的可执行程序是mysql，MySQL Server的可执行程序是mysqld。

MySQL Client和MySQL Server的关系如下：

在MySQL Client中输入的SQL语句通过TCP连接发送到MySQL Server。默认端号口是3306，即如果发送到本机MySQL Server，地址就是127.0.0.1：3306。

也可以只安装MySQL Client，然后连接到远程MySQL Server。假设远程MySQL Server的IP地址是10.0.1.99，那么就使用-h指定IP或域名：

mysql -h 10.0.1.99 -u root -p

命令行程序mysql实际上是MySQL客户端，真正的MySQL服务器程序是mysqld，在后台运行。

管理MySQL

要管理mysql可以使用可视化图形界面MySQL Workbench。

MySQL Workbench可以用可视化的方式查询、创建和修改数据库表，但是，归根到底，MySQL Workbench是一个图形客户端，它对MySQL的操作仍然是发送SQL语句并执行。因此，本质上，MySQL Workbench和MySQL Client命令行都是客户端，和MySQL交互，唯一的接口就是SQL。

因此，MySQL提供了大量的SQL语句用于管理。虽然可以使用MySQL Workbench图形界面来直接管理MySQL，但是，很多时候，通过SSH远程连接时，只能使用SQL命令，所以，了解并掌握常用的SQL管理操作是必须的。

数据库

在一个运行MySQL的服务器上，实际上可以创建多个数据库（Database）。要列出所有数据库，使用命令：

SHOW DATABASES

其中，information_schema、mysql、performance_schema和sys是系统库，不要去改动他们。其他的是用户创建的数据库

要创建一个数据库

CREATE DATABASE test;

要删除一个数据库，使用命令:

DROP DATABASE test;

注意：删除一个数据库将导致该数据库的所有表全部被删除。
对一个数据库进行操作时，要首先将其切换成当前数据库:

USE test;

表

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

SHOW TABLES;

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

DESC students;

还可以使用以下命令查看创建表的SQL语句：

SHOW CREATE TABLE students;

创建表使用CREATE TABLE语句，而删除表使用DROP TABLE语句：

DROP TABLE students:

修改表就比较复杂，如果要给students表新增一列birth，使用：

ALTER TABLE students ADD COLUMN birth VARCHAR（10） NOT NULL;

要修改birth列，例如把列名改为birthday，类型改为VARCHAR（20）：

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

要删除列，使用：

ALTER TABLE students DROP COLUMN birthday;

退出MySQL
使用exit命令退出MySQL；

实用SQL语句

插入或替换

如果我们希望插入一条新记录（INSERT），但是如果记录已经存在，就先删除原记录，再插入新记录。
此时，可以使用REPLACE语句，这样就不必先查询，再决定是否先删除再插入；

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

若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记
录。

插入或更新

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录，此时，可以使用
INSERT INTO .. NO 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语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由
UPDATE指定。

插入或忽略

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略，此时，可以使用INSERT IGNORE INTO...语句：

INNERT IGNORE INTO students（id,class_id,name,gender,socre） 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语句的结果集直接插入到指定表中。例如，创建一个统计成绩的表statistics，记录各班的平均成绩：

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;

确保INSERT语句的列和SELECT语句的列能一一对应，就可以在statistics表中直接保存查询的结果：

SELECT * FROM statistics;

强制使用指定索引

在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系
统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用FORCE INDEX
强制查询使用指定的索引。

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

指定索引的前提是索引idx_class_id必须存在。