C++ 数据库MySQL 学习笔记(2) - 数据库操作
表索引
索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录，是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。
通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列，否则数据库系统将读取每条记录的所有信息进行匹配。例如，索引相当于新华字典的音序表，如果要查“过”字，如果不适用音序，就需要从字典的第一页开始翻几百页；如果提取拼音出来，构成音序表，就只需要从10多页的音序表中直接查找，这样就可以大大节省时间。因此，使用索引可以在很大程度上提高数据库的查询速度，有效地提高了数据库系统的性能。
索引类型
索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。下文中将详细介绍~
索引存储
数据库底层索引实现主要有两种存储类型，B树(BTREE)和哈希(HASH)索引，InnoDB和MyISAM 使用BTREE索引；而MEMORY 存储引擎可以使用BTREE 和HASH 索引，默认用BTREE.在没有指定的情况下，数据库使用的引擎是 InnoDB。
索引优点
可以提高检索数据的速度.
索引缺点
创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态地维护索引，造成数据的维护速度降低了。
使用建议
索引可以提高查询的速度，但是会影响插入记录的速度，因为向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。
创建和查看索引
创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。创建索引有3种方式，分别是创建表的时候创建索引、在已经存在的表上创建索引和使用ALTER TABLE语句来创建索引。本节将根据具体的索引分类详细的讲解这3种创建方法。
普通索引
所谓普通索引，就是在创建索引时，不附加任何限制条件（唯一、非空等限制）。该类型的索引可以创建在任何数据类型的字段上。
创建表时定义索引
CREATE TABLE tablename(
propname1 type1,
propname2 type2,
……
propnamen type..n,
INDEX | KEY
[indexname] (propnamen [(length)] [ ASC | DESC ] ) );
其中，参数INDEX和KEY是用来指定字段为索引的，两者选择其中之一就可以了，作用是一样的；参数indexname是索引名字，可省略；参数propnamen是索引对应的字段的名称，该字段必须为前面定义好的字段；参数length是可选参数，其指索引的长度，必须是字符串类型才可以使用；参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列，如果不指定，则为升序。
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64), INDEX index_no(id DESC)); #创建表class, 并建立为id 字段索引
mysql> show create table class; #查看表结构
mysql> insert into class values(1, '一班', 'Martin'); # 插入记录1
mysql> insert into class values(1, '二班', 'Rock'); # 插入记录2
mysql> select * from class where id > 0 ; #根据id查询记录，结果将降序排列
已存在的表上创建索引
方法一:执行create 语句
CREATE INDEX indexname
ON tablename (propname [(length)] [ASC|DESC]);
参数INDEX是用来指定字段为索引，此处不能为KEY；参数indexname是新创建的索引的名字；参数tablename是指需要创建索引的表的名称，该表必须是已经存在的，如果不存在，需要先创建；参数propname指定索引对应的字段的名称，该字段必须为前面定义好的字段；参数length是可选参数，表示索引的长度，必须是字符串类型才可以使用；参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列,默认升序。
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class, 并建立为id 字段索引
mysql> create index index_id on class(id ASC); #追加升序索引
mysql> show create table class; #查看表定义
mysql> insert into class values(1, '一班', 'Martin'); # 插入记录1
mysql> insert into class values(1, '二班', 'Rock'); # 插入记录2
mysql> select * from class where id > 0 ; #根据id查询记录，结果将降序排列
方法二: 执行ALTER TABLE 语句
ALTER TABLE tablename ADD INDEX | KEY indexname
(propname [(length)] [ASC|DESC]);
在上述语句中，参数tablename是需要创建索引的表；关键字INDEX或KEY用来指定创建普通索引；参数indexname用来指定所创建的索引名；参数propname用来指定索引所关联的字段的名称；参数length用来指定索引的长度；参数ASC用来指定升序排序；参数DESC用来指定降序排序。
查看索引执行情况
输出结果：
key： 实际使用的索引。如果为NULL，则没有使用索引
**possible_keys：**显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引 将被列出，但不一定被查询实际使用
key_len： 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。此值越短越好！
唯一索引
所谓唯一索引，就是在创建索引时，限制索引的字段值必须是唯一的。通过该类型的索引可以比普通索引更快速地查询某条记录。
创建表时定义索引
CREATE TABLE tablename(
propname1 type1,
……
propnamen type..n,
UNIQUE INDEX | KEY [indexname] (propnamen [(length)] [ ASC | DESC ] ) );
**注意：**参数UNIQUE INDEX和UNIQUE KEY是用来指定字段为索引的，两者选择其中之一即可；参数indexname是索引名字，可省略；参数propnamen是索引对应的字段的名称，该字段必须为前面定义好的字段且必须定义为 UNIQUE 约束；参数length是可选参数，其指索引的长度，必须是字符串类型才可以使用；参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列，如果不指定，则为升序。
已存在的表上创建索引
方法一 :
CREATE UNIQUE INDEX indexname
ON tablename (propname [(length)] [ASC|DESC]);
方法二:
ALTER TABLE tablename ADD UNIQUE INDEX | KEY indexname (propname [(length)] [ASC|DESC]);
全文索引
全文索引主要对字符串类型建立基于分词的索引，主要是基于CHAR、VARCHAR和TEXT的字段上，以便能够更加快速地查询数据量较大的字符串类型的字段。
全文索引以词为基础的，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符。
MySQL从3.23.23版本开始支持全文索引，MySQL5.6以前只能在存储引擎为MyISAM的数据表上创建全文索引,5.6之后InnoDB开始支持全文索引(5.7之后支持中文全文索引) 。在默认情况下，全文索引的搜索执行方式为不区分大小写，如果全文索引所关联的字段为二进制数据类型，就以区分大小写的搜索方式执行。
创建表时定义索引
CREATE TABLE tablename(
propname1 type1,
propname2 type2,
……
propnamen type..n,
FULLTEXT INDEX | KEY
[indexname] (propnamen [(length)] ) );
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql>create table class(id int, name varchar(128) UNIQUE, teacher varchar(64),comment varchar(1024),FULLTEXT INDEX index_comm(comment)); #创建表class, 并建立为comment 字段为全文索引
mysql> insert into class values(1,'1班','Martin','我是一个兵，来自老百姓!'); # 插入记录1
mysql> insert into class values(2,'2班','Rock','此班主任毕业自唐僧系'); # 插入记录2
mysql> insert into class values(3,'3班','Janny','I''m Miss Zhang.'); #插入记录3
mysql> select * from class where match(comment) AGAINST('我是一个兵');#利用全文检索索引快速查询记录
已存在的表上创建索引
方法一: 执行create 语句
在MySQL中创建全文索引除了通过SQL语句FULLTEXT INDEX来实现外，还可以通过SQL语句CREATE FULLTEXT INDEX来实现，其语法形式如下：
CREATE FULLTEXT INDEX indexname
ON tablename( propname1 [ ( length ) ] );
在上述语句中，关键字CREATE FULLTEXT INDEX表示用来创建全文索引。
如下例表已存在，可通过CREATE语句创建全文索引：
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class, 并建立为id 字段索引
mysql> create FULLTEXT index index_teacher on class(teacher ); #追加全文索引
mysql> show create table class; #查看表定义
方法二: 执行ALTER TABLE 语句
除了上述两种方式来创建全文索引外，在MySQL中创建全文索引还可以通过SQL语句ALTER来实现，其语法形式如下：
ALTER TABLE tablename
ADD FULLTEXT INDEX|KEY indexname(propname [(length)]);
使用场景
根据全文索引字段进行全文检索数据：
SELECT * FROM 表名WHERE MATCH(列名) AGAINST(‘关键字’);
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64),info varchar(1024),FULLTEXT INDEX index_des(info)); #创建表class, 并建立为info 字段为全文索引
mysql> insert into class values(1,'1班','Martin','我是一个兵，来自老百姓!'); # 插入记录1
mysql> insert into class values(2,'2班','Rock','此班主任毕业自唐僧系'); # 插入记录2
mysql> insert into class values(3,'3班','Janny','I'm Miss Zhang.'); # 插入记录3
mysql> select * from class where match(teacher) AGAINST('我是一个兵');#根据id查询记录
MySQL8 中文分词支持
配置文件my.ini（Windows 10默认路径: C:\ProgramData\MySQL\MySQL Server 8.0） 中增加如下配置项，同时重启MySQL80 服务:
[mysqld]
ngram_token_size=2
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64),comment varchar(1024),FULLTEXT INDEX index_des(comment) with parser ngram); #创建表class, 并建立为comment 字段为全文索引
mysql> insert into class values(1,'1班','Martin','我是一个兵，来自老百姓!'); # 插入记录1
mysql> insert into class values(2,'2班','Rock','此班主任毕业自唐僧系'); # 插入记录2
mysql> insert into class values(3,'3班','Janny','I''m Miss Zhang.'); #插入记录3
mysql> select * from class where match(comment) AGAINST('百姓');#利用全文检索索引快速查询记录
mysql> select * from class where match(comment) AGAINST('唐僧');#利用全文检索索引快速查询记录
多列索引
多列索引，是指在创建索引时所关联的字段不是一个字段，而是多个字段，虽然可以通过所关联的字段进行查询，但是只有查询条件中使用了所关联字段中的第一个字段，多列索引才会被使用。
创建表时定义索引
CREATE TABLE tablename(
propname1 type1,
……
propnamen type..n,
INDEX | KEY [indexname] (propname1 [(length)] [ ASC | DESC ],
Propname2 [(length)] [ ASC | DESC ],
... ...
Propnamen [(length)] [ ASC | DESC ])
);
**注意：**和普通索引定义基本相同，不同之处就是增加了多个索引列。
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64), INDEX index_mult_columns(id, teacher)); #创建表class, 并建立包含id,teacher字段的多列索引
mysql> show create table class; #查看表定义
mysql> insert into class values(1, '一班', 'Martin'); # 插入记录1
mysql> insert into class values(1, '二班', 'Rock'); # 插入记录2
mysql> select * from class where id > 0 ; #仅根据id查询记录会启用多列索引
已存在的表上创建索引
方法一: 执行create 语句
CREATE INDEX indexname
ON tablename( propname1 [(length)] [ ASC | DESC ],
Propname2 [(length)] [ ASC | DESC ],
... ...
Propnamen [(length)] [ ASC | DESC ] );
在上述语句中，关键字CREATE INDEX表示用来创建多列索引。
如下例表已存在，可通过CREATE语句创建多列索引：
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class, 并建立为id 字段索引
mysql> create index index_id on class(id, name ); #追加多列索引
mysql> show create table class; #查看表定义
方法二: 执行ALTER TABLE 语句
除了上述两种方式来创建全文索引外，在MySQL中创建全文索引还可以通过SQL语句ALTER来实现，其语法形式如下：
ALTER TABLE tablename
ADD INDEX|KEY indexname(propname1 [(length)] [ ASC | DESC ],
Propname2 [(length)] [ ASC | DESC ],
... ...
Propnamen [(length)] [ ASC | DESC ] );
隐藏索引
MySQL 8开始支持隐藏索引。隐藏索引提供了更人性化的数据库操作。
隐藏索引，顾名思义，让索引暂时不可见，不会被优化器使用。默认情况下索引是可见的。隐藏索引可以用来测试索引的性能。验证索引的必要性时不需要删除索引，可以先将索引隐藏，如果优化器性能无影响就可以真正地删除索引。
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #取消隐藏
删除索引
所谓删除索引，就是删除表中已经创建的索引。之所以要删除索引，是因为这些索引会降低表的更新速度，影响数据库的性能。
在MySQL中删除索引通过SQL语句DROP INDEX来实现，其语法形式如下：
DROP INDEX indexname ON tablename;
修改索引 先删除，后增加
索引的设计原则
为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。本节将介绍一些索引的设计原则。
-
选择唯一性索引
唯一性索引的值是唯一的，可以更快速地通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段，为该字段建立唯一性索引可以很快确定某个学生的信息，如果使用姓名的话，可能存在同名现象，从而降低查询速度。
-
为经常需要排序、分组和联合操作的字段建立索引
经常需要使用ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间，如果为其建立索引，可以有效地避免排序操作。
-
为经常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度，为这样的字段建立索引可以提高整个表的查询速度。
-
限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大，修改表时，对索引的重构和更新很麻烦。
-
尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间多。
-
尽量使用前缀来索引
如果索引的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间，如果只检索字段前面的若干字符，这样可以提高检索速度。
-
删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
表的插入
插入数据记录是常见的数据操作，可以显示向表中增加的新的数据记录。在MySQL中可以通过“INSERT INTO”语句来实现插入数据记录，该SQL语句可以通过如下4种方式使用：插入完整数据记录、插入部分数据记录、插入多条数据记录和插入JSON结构的数据记录（暂且不讲）。
插入完整数据记录
在MySQL中插入完整的数据记录可通过SQL语句INSERT来实现，其语法形式如下：
INSERT INTO tablename(field1, field2, field3, ..., fieldn) VALUES(value1, value2, value3, ..., valuen);
可以缩写为：
INSERT INTO tablename VALUES(value1, value2, value3, ..., valuen);
参数: tablename表示所要插入完整记录的表名，参数fieldn表示表中的字段名字，参数valuen表示所 要插入的数值，并且参数fieldn与参数valuen一一对应。
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class, 并建立包含id,teacher字段的多列索引
mysql> show create table class; #查看表定义
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'); # 插入完整记录
mysql> insert into class values(2, '二班', 'Rock'); # 方式二，完整插入可缩写
插入部分数据记录
INSERT INTO tablename(field1, field2, field3, ..., fieldn) VALUES(value1, value2, value3, ..., valuen);
注意**: 如果域没有设定默认值，也没有设定为自增长，则插入记录时必须插入值
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> show create table class; #查看表定义
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'); # 插入完整记录
mysql> insert into class (name, teacher)values('二班', 'Rock'); # 插入部分记录
插入多条数据记录
语法形式:
INSERT INTO tablename(field1, field2, field3, ..., fieldn)
VALUES(value11, value12, value13, ..., value1n)，
(value21, value22, value23, ..., value2n)，
... ....
(valuen1, valuen2, valuen3, ..., valuenn);
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> show create table class; #查看表定义
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'),(2,'二班', 'Rock'),(3, '三班', 'Janny'); # 插入多条记录
更新表中的数据记录
更新数据记录是数据操作中常见的操作，可以更新表中已经存在数据记录中的值。在MySQL中可以通过UPDATE语句来实现更新数据记录，该SQL语句可以通过如下几种方式使用：更新特定数据记录、更新所有数据记录、更新JSON结构的数据记录。
更新特定数据记录
在MySQL中更新特定数据记录可通过SQL语句UPDATE来实现，其语法形式如下：
UPDATE tablename SET field1 = value1, field2 = value2, field3 = value3, ..., fieldn = valuen)
WHERE CONDITION
上述语句中，参数tablename表示所要更新数据记录的表名，参数field表示表中所要更新数值的字段名字，参数valuen表示更新后的数值，参数CONDITION指定更新满足条件的特定数据记录。
例如：校长新找了个如花老师把Jack老师给换了!
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'),(2,'二班', 'Jack'),(3, '三班', 'Janny'); # 插入多条记录
mysql> update class set teacher = '如花老师' where teacher = 'Jack'; #通过teacher 字段匹配更新记录 或
mysql> update class set teacher = '如花老师' where id = 2; #通过id 字段匹配更新记录
更新所有数据记录
在MySQL中更新特定数据记录可通过SQL语句UPDATE来实现，其语法形式如下：
UPDATE tablename SET field1 = value1, field2 = value2, field3 = value3, ..., fieldn = valuen) WHERE CONDITION;
或
UPDATE tablename SET field1 = value1, field2 = value2, field3 = value3, ..., fieldn = valuen) ；
在上述语句中，参数tablename表示所要更新数据记录的表名，参数field表示表中所要更新数值的字段名字，参数valuen表示更新后的数值，参数CONDITION表示满足表tablename中的所有数据记录，或不使用关键字WHERE语句。
例如：校长新找了个如花老师就把所有老师都给换了!
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'),(2,'二班', 'Rock'),(3, '三班', 'Janny'); # 插入多条记录
mysql> update class set teacher = '如花老师' ; #把所有记录的teacher列改成如花老师
或
mysql> update class set teacher = '如花老师' where 1=1;
删除表中的数据记录
删除数据记录是数据操作中常见的操作，可以删除表中已经存在的数据记录。在MySQL中可以通过DELETE语句来删除数据记录，该SQL语句可以通过以下几种方式使用：删除特定数据记录、删除所有数据记录。
删除特定数据记录
在MySQL中删除特定数据记录可通过SQL语句DELETE来实现，其语法形式如下：
DELETE FROM tablename WHERE CONDITION;
在上述语句中，参数tablename表示所要删除数据记录的表名，参数CONDITION指定删除满足条件的特定数据记录。
例如：Martin 所带的一班毕业了，从class 表中删除!
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'),(2,'二班', 'Rock'),(3, '三班', 'Janny'); # 插入多条记录
mysql> delete from class where teacher = 'Martin'; #通过teacher字段删除记录 或
mysql> delete from class where id = 1; #通过id 字段删除匹配的记录
删除所有数据记录
在MySQL中删除所有数据记录同样可通过SQL语句DELETE来实现，其语法形式如下：
DELETE FROM tablename WHERE CONDITION;
或
DELETE FROM tablename；
在上述语句中，为了删除所有的数据记录，参数CONDITION需要满足表tablename中所有数据记录，如id>0; 或者无关键字WHERE语句。
例如：所有老师带的班级都毕业了，从class 表中删除!
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'),(2,'二班', 'Rock'),(3, '三班', 'Janny'); # 插入多条记录
mysql> delete from class ; #直接删除所有记录 或
mysql> delete from class where id > 0; #通过id 字段删除匹配的所有记录
作业：设计游戏用户信息表
回顾下Jack 要实现的用户登陆模块，具体需求如下：
-
游戏玩家访问游戏客户端，通过客户端界面输入用户名和密码
-
在游戏玩家点击”确认”后，客户端连接至数据库服务器对用户名和密码进行确认，
-
如果验证成功，则 玩家可以进入大厅，如果失败，则不允许进入！
俗话说得好，要开车，先修路！
请帮Jack 设计好相应的数据库表，并插入少量样例数据!
mysql> create database game_db; #创建游戏数据库game_db
mysql> use game_db; #选择数据库game_db
mysql> create table users (
id int(11) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
username varchar(64) NOT NULL UNIQUE COMMENT '用户名',
password varchar(32) NOT NULL COMMENT '密码',
nickname varchar(64) DEFAULT NULL COMMENT '昵称',
mobile char(11) DEFAULT NULL COMMENT '手机号码',
age tinyint(3) unsigned DEFAULT 18 COMMENT '年龄',
idno char(18) DEFAULT NULL COMMENT '身份证号码'
);
作用1： 验证用户的身份
作用2： 用来保存用户的基本信息
(上面设计的数据库没技术, 初级程序员做法…)
更高逼格的设计： 分成两个表，用户信息表和用户验证表
基于以下三方面的原因：
面向对象方面考虑 - 用户信息和用户验证是两种”对象”
性能方面考虑 - 登陆验证的时候列较少，查询速度快。
安全方面考虑 - 防止在查询用户信息时，把密码也直接查询出来，会容易被攻击和进行恶意操作。
mysql> create database game_db; #创建游戏数据库game_db
mysql> use game_db; #选择数据库game_db
mysql> create table users (
id int(11) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
username varchar(64) NOT NULL UNIQUE COMMENT '用户名',
nickname varchar(64) DEFAULT NULL COMMENT '昵称',
mobile char(11) DEFAULT NULL COMMENT '手机号码',
age tinyint(3) unsigned DEFAULT 18 COMMENT '年龄',
idno char(18) DEFAULT NULL COMMENT '身份证号码'
);
create table user_auths(
userid int(11) unsigned NOT NULL COMMENT '外键,对应users表中的id',
username varchar(64) NOT NULL UNIQUE COMMENT '用户名',
password varchar(32) NOT NULL COMMENT '密码',
FOREIGN KEY(userid) REFERENCES users(id)
);
mysql> insert into users (username, nickname, mobile, age, idno) values('martin', '程咬金', '18684518289', 39, '430623871234567898');
mysql> insert into user_auths values(1, 'martin', '123456qweQWE'); #密码明文，不安全
mysql> insert into user_auths values(1, 'martin', md5('123456qweQWE'));
使用技巧
-
使用到了外键的方式连接两个表的关联性.
-
使用到了MD5 加密客户的密码一遍程序员无法直接查看到用户密码.
数据查询
查询数据是指从数据库中获取所需要的数据。如Jack 要达到验证用户名和密码的目的，就需要从数据库已保存的用户表中读取当前用户的密码进行验证，以验明正身！保存查询数据是数据库操作中常用且重要的操作。用户可以根据自己对数据的需求，使用不同的查询方式，获得不同的数据。
简单查询
在MySQL中可以通过SQL语句来实现基本数据查询，SQL语句可以通过如下多种使用：查询所有字段数据、查询指定字段数据、避免重复数据查询、对结果进行排序和分组等查询。
数据库中可能包含数量庞大的表，表中可能包含无数的记录，如果没有两把刷子要获得所需的数据并非易事。在MySQL中，可以使用SELECT语句来查询数据，根据查询条件的不同，数据库系统会找到不同的数据，通过SELECT语句可以很方便地获取所需的信息。
在MySQL中，SELECT语句的基本语法形式如下：
SELECT field1 field2 … fieldn
FROM tablename
[WHERE CONDITION1]
[GROUP BY fieldm [HAVING CONDITION2]]
[ORDER BY fieldn [ASC|DESC]]；
其中，filed1~fieldn参数表示需要查询的字段名；tablename参数表示表的名称；CONDITION1参数表示查询条件；fieldm参数表示按该字段中的数据进行分组；CONDITION2参数表示满足该表达式的数据才能输出；fieldn参数指按该字段中数据进行排序。排序方式由ASC和DESC两个参数指出；ASC参数表示按升序的顺序进行排序，是默认参数；DESC参数表示按降序的顺序进行排序。
查询所有字段数据
查询所有字段是指查询表中所有字段的数据，这种方式可以将表中所有字段的数据都查询出来。MySQL有两种方式可以查询表中的所有字段。
- 列出表的所有字段
通过SQL语句SELECT列出表的所有字段，具体语法形式如下：
SELECT field1,field2,…,fieldn FROM tablename;
其中，filed1~fieldn参数表示需要查询的字段名；tablename参数表示表的名称。
-
”符号表示所有字段
查询所有字段数据，除了使用上面的方式外，还可以通过符号“*”来实现，具体语法形式如下：
SELECT * FROM tablename;
其中，符号“”表示所有字段名；tablename参数表示表的名称。与上一种方式相比，“”符号方式的优势比较明显，即可用该符号代替表中的所有字段，但是这种方式不够灵活，只能按照表中字段的固定顺序显示，不能随便改变字段的顺序。
- 查询指定字段数据
查询指定字段数据，只需修改关键字SELECT后的字段列表为指定字段即可。
SELECT field1,field2,…,fieldn FROM tablename;
例如，从班级表中查询班主任字段，SQL语句如下所示。
SELECT teacher FROM class;
如果关键字SELECT后面的字段不包含在所查询的表中，那么MySQL会报错。
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'),(2,'二班', 'Rock'),(3, '三班', 'Janny'),(4,'四班', 'Janny'); # 插入多条记录
mysql> select teacher from class ; #查询class表中的teacher 域
DISTINCT查询
当在MySQL中执行简单数据查询时，有时会显示出重复数据。为了实现查询不重复数据，MySQL提供了DISTINCT功能，SQL语法如下：
SELECT DISTINCT field1,field2,…,fieldn FROM tablename;
在上述语句中，关键字DISTINCT去除重复的数据。下面将通过一个具体的示例来说明如何实现查询不重复数据。
执行SQL语句SELECT，在数据库school中查询班级表 class 中teacher字段的数据。具体步骤如下:
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'),(2,'二班', 'Rock'),(3, '三班', 'Janny'),(4,'四班', 'Janny'); # 插入多条记录
mysql> select teacher from class ; #查询class表中的teacher 域，包含重复记录
mysql> select distinct teacher from class ; #查询class表中的teacher 域，去重
IN 查询
有的时候，当我们需要查询的目标记录限定在某个集合中的时候，在MySQL中可以使用关键字IN来实现，关键字IN可以实现判断字段的数值是否在指定集合中，该关键字的具体语句形式如下：
SELECT field1,field2,…,fieldn
FROM tablename WHERE filedm IN(value1,value2,value3,…,valuen);
**注：**参数fieldn表示名称为tablename的表中的字段名，参数valuen表示集合中的值，通过关键字IN来判断字段fieldm的值是否在集合（value1,value2,value3,…,valuen）中，如果字段fieldm的值在集合中，就满足查询条件，该记录会被查询出来，否则不会被查询出来。
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'),(2,'二班', 'Rock'),(3, '三班', 'Janny'),(4,'四班', 'Janny'); # 插入多条记录
mysql> select * from class where teacher in ('Martin','Rock') ; #查询class表中的teacher 域是martin 和Rock 的记录
使用NOT IN可以反向查询非集合中的数据
SELECT field1,field2,…,fieldn
FROM tablename WHERE filedm NOT IN(value1,value2,value3,…,valuen);
集合查询的注意:集合中慎用 NULL
在具体使用关键字IN时，查询的集合中如果存在NULL，则不会影响查询,NULL 存不存在的效果都一样；但如果使用关键字NOT IN，查询的集合中如果存在NULL，则不会查询到任何的结果。
BETWEEN AND 查询
当我们需要查询指定范围内的数据（如： id 从0 到 100）的时候，MySQL提供了关键字BETWEEN AND，用来实现判断字段的数值是否在指定范围内的条件查询。该关键字的具体语法形式如下：
SELECT field1,field2,…,fieldn
FROM tablename WHERE fieldm BETWEEN minvalue AND maxvalue
在上述语句中，参数fieldn表示名称为tablename的表中的字段名，通过关键字BETWEEN和AND来设置字段field的取值范围，如果字段field的值在所指定的范围内，那么满足查询条件，该记录会被查询出来，否则不会被查询出来。
BETWEEN minvalue AND maxvalue，表示的是一个范围间的判断过程，只针对数字类型.
- 范围的数据记录查询
通过关键字BETWEEN和AND设置查询范围，以实现查询语文成绩（字段Chinese）在80和90之间的学生，具体SQL如下：
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table grade(id int UNIQUE AUTO_INCREMENT, name varchar(128) NOT NULL, math tinyint unsigned, chinese tinyint unsigned, english tinyint unsigned, create_date date); #创建成绩表 grade
mysql> insert into grade values(1, '甲', 80, 87, 91,'2020-02-03'),(2,'乙', 72, 64, 89,'2020-05-01'),(3, '丙', 54, 69, 87,'2020-04-21'),(4,'丁', 78, 79, 89, '2020-06-04'); # 插入多条记录
mysql> select * from class where chinese between 80 and 90 ; #查询成绩表中语文成绩在80 和 90 之间的学员记录
mysql> select * from grade where create_date between '2020-05-01' and '2020-06-04'
- 不符合范围的数据记录查询
通过关键字NOT设置反向查询非查询范围的条件，具体SQL语句如下：
SELECT name,chinese
FROM grade WHERE Chinese NOT BETWEEN 85 AND 90;
LIKE 模糊查询
当我们只想用字符串中间的一部分特征查找含有特征字串的信息时，MySQL提供了关键字LIKE来实现模糊查询，需要使用通配符，具体语法形式如下：
SELECT field1,field2,…,fieldn
FROM tablename WHERE fieldm LIKE value;
在上述语句中，参数tablename表示表名，参数fieldn表示表中的字段名字，通过关键字LIKE来判断字段field的值是否与value字符串匹配，如果相匹配，则满足查询条件，该记录就会被查询出来；否则就不会被查询出来。
在MySQL中，字符串必须加上单引号（‘’）和双引号（″″）。由于关键字LIKE可以实现模糊查询，因此该关键字后面的字符串参数除了可以使用完整的字符串外，还可以包含通配符。LIKE关键字支持的通配符如表5-1所示。
-
查找某个字段含有‘三’字的记录：
SELECT * FROM class WHERE teacher LIKE ‘%三%’;
注意： 将会把teacher字段为“三爷”，“张三”，“张猫三”、“三脚猫”，“唐三藏”等等含“三”的记录全找出来。
-
查找teacher字段中既有“三”又有“猫”的记录，请使用and条件
SELECT * FROM class WHERE teacher LIKE ‘%三%’ AND teacher LIKE ‘%猫%’; -
查询teacher字段中既有“三”又有“猫”的记录，同时“猫”在“三”之后的记录
SELECT * FROM class WHERE teacher LIKE ‘%三%猫%’;
注意： 虽然能搜索出“三脚猫”，但不能搜索出符合条件的“张猫三”。
-
只找出“唐三藏”这样teacher为三个字且中间一个字是“三”的;
SELECT * FROM ［user］ WHERE u_name LIKE ‘_三_’;
注意：_表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句：（可以代表一个中文字符）
-
只找出“三脚猫”这样teacher为三个字且第一个字是“三”的;
SELECT * FROM class WHERE teacher LIKE ‘三__’;
实战
带有“%”通配符的查询
-
查询字段teacher中以字母Ma开头的数据记录，具体SQL语句如下：
SELECT teacher FROM class WHERE teacher LIKE '%Ma%'; -
MySQL不区别大小写，上述SQL语句可以修改如下：
SELECT * FROM class WHERE teacher LIKE 'MA%'; -
如果想查询不是以字母Ma开头的全部老师，可以执行逻辑非运算符（NOT），具体SQL语句如下:
SELECT teacher FROM class WHERE teacher NOT LIKE '%ar%';
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'),(2,'二班', 'Rock'),(3, '三班', 'Janny'),(4,'四班', 'Janny'); # 插入多条记录
mysql> SELECT teacher FROM class WHERE teacher LIKE '%Ma%'; #查询class表中的teacher 域，包含重复记录
mysql> SELECT name FROM class WHERE name NOT LIKE '%ar%';
带有“_”通配符的查询
-
查询字段teacher中第二个字母为A的数据记录，具体SQL语句如下：
SELECT * FROM class WHERE teacher LIKE '_A%'; -
如果想查询第二个字母不是A的全部老师，可以执行逻辑非运算符（NOT），具体SQL语句如下：
SELECT * FROM class WHERE NOT teacher LIKE '_A%'; -
如果想查询第二个字母不是A的全部老师，也可以用以下SQL语句查询：
SELECT * FROM class WHERE teacher NOT LIKE '_A%';
使用LIKE关键字查询其他类型数据
-
执行SQL语句SELECT，查询grade表字段english带有数字9的全部学生，具体SQL语句如下：
SELECT name,english FROM grade WHERE english LIKE '%9%'; -
对于LIKE关键字，如果匹配“%%”，就表示查询所有数据记录。
SELECT name FROM grade WHERE name LIKE '%%';
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table grade(id int UNIQUE AUTO_INCREMENT, name varchar(128) NOT NULL, math tinyint unsigned, chinese tinyint unsigned, english tinyint unsigned, create_date date); #创建成绩表 grade
mysql> insert into grade values(1, '甲', 80, 87, 91,'2020-02-03'),(2,'乙', 72, 64, 89,'2020-05-01'),(3, '丙', 54, 69, 87,'2020-04-21'),(4,'丁', 78, 79, 89, '2020-06-04'); # 插入多条记录
mysql> select * from grade where english like '9_' ; #查询英语成绩是90 - 99 的记录
对查询结果排序
在MySQL中，从表中查询出的数据可能是无序的，或者其排列顺序不是用户所期望的顺序，为了使查询结果的顺序满足用户的要求，可以使用关键字ORDER BY对记录进行排序，其语法形式如下：
SELECT field1, field2, field3, …, fieldn
FROM tablename ORDER BY fieldm [ASC|DESC]
在上述语句中，参数tablename表示所要进行排序的表名，参数fieldn表示表中的字段名字，参数fieldm表示按照该字段进行排序；ASC表示按升序进行排序；DESC表示按降序进行排序。默认的情况下按ASC进行排序。
**注意：**如果存在一条记录字段的值为空值（NULL），那么按升序排序时，含空值的记录将最先显示，可以理解为空值是该字段的最小值；按降序排列时，字段为空值的记录将最后显示。
-
执行SQL语句SELECT，查询表grade中所有的数据记录，按照语文成绩（字段chinese）升序排序，具体SQL语句如下：
SELECT id,name,chinese FROM class ORDER BY chinese ASC;
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table grade(id int UNIQUE AUTO_INCREMENT, name varchar(128) NOT NULL, math tinyint unsigned, chinese tinyint unsigned, english tinyint unsigned, create_date date); #创建成绩表 grade
mysql> insert into grade values(1, '甲', 80, 87, 91,'2020-02-03'),(2,'乙', 72, 64, 89,'2020-05-01'),(3, '丙', 54, 69, 87,'2020-04-21'),(4,'丁', 78, 79, 89, '2020-06-04'); # 插入多条记录
mysql> SELECT id,name,chinese FROM class ORDER BY chinese ASC; #按升序的方式查询学员记录
简单分组查询
MySQL软件提供了5个统计函数来帮助用户统计数据，可以使用户很方便地对记录进行统计数、计算和、计算平均数、计算最大值和最小值，而不需要查询所有数据。
在具体使用统计函数时，都是针对表中所有记录数或指定特定条件（WHERE子句）的数据记录进行统计计算。在现实应用中，经常会先把所有数据记录进行分组，再对这些分组后的数据记录进行统计计算。
MySQL通过SQL语句GROUP BY来实现，分组数据查询语法如下：
SELECT function()[,filed ]
FROM tablename WHERE CONDITION GROUP BY field;
在上述语句中，参数field表示某字段名，通过该字段对名称为tablename的表的数据记录进行分组。
**注意：**在具体进行分组查询时，分组所依据的字段上的值一定要具有重复值，否则分组没有任何意义。
五个统计函数简介
-
统计数量
COUNT(*)：该种方式可以实现对表中记录进行统计,不管表字段中包含的是NULL值还是非NULL值。
COUNT(field):该种方式可以实现对指定字段的记录进行统计,在具体统计时将忽略NULL值。
-
统计计算平均值
该函数只有一种使用方式。
AVG(field)使用方式:该种方式可以实现对指定字段的平均值进行计算,在具体统计时将忽略NULL值。
-
统计计算求和
该函数只有一种使用方式。
SUM(field)使用方式:该种方式可以实现计算指定字段值之和,在具体统计时将忽略NULL值。
-
统计最大值
该函数只有一种使用方式。
MAX(field)使用方式:该种方式可以实现计算指定字段值中的最大值,在具体统计时将忽略NULL值。
-
统计最小值
该函数只有一种使用方式。
MIN(field)使用方式:该种方式可以实现计算指定字段值中的最小值,在具体统计时将忽略NULL值。
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table grade(id int UNIQUE AUTO_INCREMENT, name varchar(128) NOT NULL, math tinyint unsigned, chinese tinyint unsigned, english tinyint unsigned, class_id int NOT NULL); #创建成绩表 grade
mysql> insert into grade values(1, '甲', 80, 87, 91, 1),(2,'乙', 72, 64, 89,2),(3, '丙', 54, 69, 87,2),(4,'丁', 78, 79, 89, 1); #插入多条记录
mysql> select class_id, sum(math) from grade group by class_id; #对两个班级的数学成绩进行统计
mysql> select class_id, avg(math), avg(chinese), avg(english) from grade group by class_id; #统计平均分，会忽略掉NULL 记录
统计分组查询
在MySQL中，只实现简单的分组查询有时候可能没有任何实际意义，因为关键字GROUP BY单独使用时，默认查询出每个分组中随机的一条记录，具有很大的不确定性，一般建议将分组关键字与统计函数一起使用。
如果想显示每个分组中的字段，可以通过函数GROUP_CONCAT()来实现。该函数可以实现显示每个分组中的指定字段，函数的具体语法形式如下：
SELECT GROUP_CONCAT(field)
FROM tablename
WHERE CONDITION GROUP BY field;
在上述语句中会显示每个数组中的字段值。
【示例5-5】使用GROUP_CONCAT()对班级进行统计分组，并显示每组中学生的姓名。
执行SQL语句GROUP_CONCAT()，显示每个分组，具体SQL语句如下：
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table grade(id int UNIQUE AUTO_INCREMENT, name varchar(128) NOT NULL, math tinyint unsigned, chinese tinyint unsigned, english tinyint unsigned, class_id int NOT NULL); #创建成绩表 grade
mysql> insert into grade values(1, '甲', 80, 87, 91, 1),(2,'乙', 72, 64, 89,2),(3, '丙', 54, 69, 87,2),(4,'丁', 78, 79, 89, 1); #插入多条记录
mysql> SELECT GROUP_CONCAT(name) name, sum(math) FROM grade GROUP BY class_id; #按class_id 进行分组，统计数学总分并显示每个分组中的姓名
联合查询
内连接查询
在实际开发中，我们会联合多个表来实现查询，比如把班级表和学生表联合起来就同时可以看到班级、老师和学员的信息，一个班级表：
一个班级对应着多个学生，以下是与之对应的学生表”：
将两个表中ID 相同的记录关联起来组成一个新的“列表”，这就是联合查询:
这种只有2张表匹配的行才能显示的连接方式在Mysql 中称之为内连接： INNER JOIN
在MySQL中内连接数据查询通过“INNER JOIN…ON”语句来实现，语法形式如下所示。
SELECT field1,field2,…,fieldn FROM tablename1
INNER JOIN tablename2 [INNER JOIN tablenamen] ON CONDITION
其中，参数fieldn表示要查询的字段名，来源于所连接的表tablename1和tablename2，关键字INNER JOIN表示表进行内连接，参数CONDITION表示进行匹配的条件。
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> CREATE TABLE class (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(128) DEFAULT NULL,
`teacher` varchar(64) DEFAULT NULL,
UNIQUE KEY `id` (`id`)
); #创建班级表 class
mysql> insert into class values(101, '萌新一班', 'Martin'),(102, '萌新二班', 'Rock'),(103, '萌新三班', 'Janny'); #创建成绩表 grade
mysql> CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT UNIQUE,
`name` varchar(64) DEFAULT NULL,
`class_id` int DEFAULT NULL,
`sex` enum('F','M') DEFAULT NULL
);
mysql> insert into student values(1,'小花',101,'M'),(2,'小红',102, 'F'),(3,'小军',102,'F'),(4,'小白',101,'F'); #插入多条记录
mysql> select * from class inner join student on class.id = student.class_id; #查询class 表和student 表中班级字段相同的记录并进行内连接
mysql> select * from class as a inner join student as b where a.id = b.class_id; #同上，使用别名查询
当表名特别长时，直接使用表名很不方便，或者在实现自连接操作时，直接使用表名无法区别表。为了解决这一类问题，MySQL提供了一种机制来为表取别名，具体语法如下:
SELECT field1, field2, ...,fieldn [AS] otherfieldn
FROM tablename1 [AS] othertablename1, ...,
Tablenamen [AS] othertablenamen ... where othertablename1.fieldx = othertablenamen.fieldx ... ;
其中，参数tablename为表原来的名字，参数othertablename为新表名，之所以要为表设置新的名字，是为了让SQL语句代码更加直观、更加人性化和实现更加复杂的功能。
自连接
内连接查询中存在一种特殊的等值连接——自连接。所谓自连接，就是指表与其自身进行连接。
如：查询学生 ”小红 ”所在班级的其他学生,SQL语句如下：
mysql> use school; #选择数据库school
mysql> select t1.id, t1.name, t1.class_id from student t1 inner join student t2 on t1.class_id = t2.class_id and t2.name= '小红'; #查询学生 ”小红 ”所在班级的其他学生
等值连接
内连接查询中的等值连接就是在关键字ON后的匹配条件中通过等于关系运算符（=）来实现等值条件。
mysql> select * from class as a inner join student as b where a.id = b.class_id;
不等值连接
内连接查询中的不等连接就是在关键字ON后的匹配条件中通过除了等于关系运算符来实现不等条件外，还可以使用关系运算符，包含“>”“>=”“<”“<=”和“!=”等运算符号。
mysql> select * from class as a inner join student as b where a.id != b.class_id;
外连接查询
当我们在查询数据时，要求返回所操作表中至少一个表的所有数据记录，通过SQL语句“OUTER JOIN…ON”来实现。外连接数据查询语法形式如下：
SELECT field1,field2,…,fieldn
FROM tablename1 LEFT|RIGHT [OUTER] JOIN tablename2
ON CONDITION
在上述语句中，参数fieldn表示所要查询的字段名字，来源于所连接的表tablename1和tablename2，关键字OUTER JOIN表示表进行外连接，参数CONDITION表示进行匹配的条件。
外连接查询可以分为以下二类：
左外连接
外连接查询中的左外连接，就是指新关系中执行匹配条件时，以关键字LEFT JOIN左边的表为参考表。左连接的结果包括LEFT OUTER字句中指定的左表的所有行，而不仅仅是连接列所匹配的行，如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表均为空值。
例如：查询所有学生的学号、姓名、班级编号、性别、班级名、班主任信息，具体SQL语句如下：
mysql> use school; #选择数据库school
mysql> select * from student as a left join class as b on a.class_id = b.id; #左连接查询所有学生对应的班级信息
mysql> select * from class as a left join student as b on a.id = b.class_id; #左连接查询所有班级的学员信息
(左连接查询一 所有学员对应的班级信息)
(左连接查询二 所有班级对应的学员信息)
右外连接
外连接查询中的右外连接在新关系中执行匹配条件时，以关键字RIGHT JOIN右边的表为参考表，如果右表的某行在左表中没有匹配行，左表将返回空值。
例如：查询所有班级的所有学生信息。具体SQL语句如下：
mysql> use school; #选择数据库school
mysql> select * from student as a right join class as b on a.class_id = b.id; #右连接查询所有班级对应的学员信息
mysql> select * from class as a right join student as b on a.id = b.class_id; #右连接查询所有学员对应的班级信息
合并查询数据记录
在MySQL中通过关键字UNION来实现合并操作，即可以通过其将多个SELECT语句的查询结果合并在一起，组成新的关系。在MySQL软件中，合并查询数据记录可通过SQL语句UNION来实现，具体语法形式如下：
SELECT field1,field2,…,fieldn FROM tablename1
UNION | UNION ALL
SELECT field1,field2,…,fieldn FROM tablename2
UNION | UNION ALL SELECT field1,field2,…,fieldn ;
注意： 多个选择语句select 的列数相同就可以合并,union和union all的主要区别是union all是把结果集直接合并在一起，而union 是将union all后的结果再执行一次distinct，去除重复的记录后的结果。
mysql> use school; #选择数据库school
mysql> select teacher from class union all select name from student; #查询班级表所有老师和学生表中所有学生姓名
mysql> select teacher as people from class union all select name as people from student; #同上，使用别名查询
mysql> select teacher,id from class union all select name,class_id from student; #查询班级表所有(老师、班级ID)和学生表中所有学生(姓名、班级ID)
子查询
所谓子查询，是指在一个查询中嵌套了其他的若干查询，即在一个SELECT查询语句的WHERE或FROM子句中包含另一个SELECT查询语句。在查询语句中，外层SELECT查询语句称为主查询，WHERE子句中的SELECT查询语句被称为子查询，也被称为嵌套查询。
通过子查询可以实现多表查询，该查询语句中可能包含IN、ANY、ALL和EXISTS等关键字，除此之外还可能包含比较运算符。理论上，子查询可以出现在查询语句的任意位置，但是在实际开发中子查询经常出现在WHERE和FROM子句中。
带比较运算符的子查询
子查询可以使用比较运算符。这些比较运算符包括=、!=、>、>=、<、<=和<>等。其中，<>与!=是等价的。比较运算符在子查询中使用得非常广泛，如查询分数、年龄、价格和收入等。
例如：查询student 表中“小花”所在班级班主任的名字。SQL语句如下：
mysql> use school; #选择数据库school
mysql> select teacher from class where id = (select class_id from student where name='小花'); #查询“小花”所在班级班主任的姓名
注意：使用比较运算符时，select 子句获得的记录数不能大于1条!!!
带关键字IN的子查询
一个查询语句的条件可能落在另一个SELECT语句的查询结果中，这时可以使用IN关键字，SQL示例如下：
NOT IN的用法与IN相同。
例如：查询student 表中“小花”所在班级班主任的名字。SQL语句如下：
mysql> use school; #选择数据库school
mysql> select teacher from class where id in (select class_id from student where name='小花'); #查询student 表中“小花”所在班级班主任的名字
mysql> select teacher from class where id in (select class_id from student where name like '小%'); #查询姓名以“小”字开头的学生所在班级班主任的姓名
带关键字EXISTS的子查询
关键字EXISTS表示存在，后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行；如果至少返回一行，那么EXISTS的结果为true，此时外层语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。
例如：如果102班存在学生记录，就查询102班的班级信息的记录。SQL示例语句如下：
mysql> use school; #选择数据库school
mysql> select * from class where id=102 and exists (select * from student where class_id=102); #如果102班存在学生记录，就查询102班的班级信息
带关键字ANY的子查询
关键字ANY表示满足其中任一条件。使用关键ANY时，只要满足内层查询语句返回的结果中的任何一个就可以通过该条件来执行外层查询语句。例如，需要查询哪些学生可以获取奖学金，那么首先要有一张奖学金表，从表中查询出各种奖学金要求的最低分，只要一个同学的乘积大于等于不同奖学金最低分的任何一个，这个同学就可以获得奖学金。关键字ANY通常和比较运算符一起使用。例如，“>ANY”表示大于任何一个值，“=ANY”表示等于任何一个值。
例如：查询数据库school的表student中哪些学生可以获得奖学金。学生的成绩达到其中任何一项奖学金规定的分数即可，SQL语句示例如下：
mysql> use school; #选择数据库school
mysql> create table scholarship (score int, level varchar(64));
mysql> insert into scholarship values(240, '二等奖'),(257,'一等奖');
mysql> select st.id, st.name, st.math+st.chinese+st.english total from grade st where (math+chinese+english) >= ANY (select score from scholarship); #查询能获得奖学金的学院记录
带关键字ALL的子查询
关键字ALL表示满足所有条件。使用关键字ALL时，只有满足内层查询语句返回的所有结果才可以执行外层查询语句。例如，需要查询哪些同学能够获得一等奖学金，首先要从奖学金表中查询出各种奖学金要求的最低分。因为一等奖学金要求的分数最高，只有当成绩高于所有奖学金最低分时，这个同学才可能获得一等奖学金。关键字ALL也经常与比较运算符一起使用。例如，“>ALL”表示大于所有值，“<ALL”表示小于所有值。
例如：查询数据库school的表student中哪些学生可以获得一等奖学金，即学生的总成绩要达到一等奖学金规定的分数，而一等奖学金是最高奖学金。SQL语句示例如下：
mysql> use school; #选择数据库school
mysql> select st.id, st.name, st.math+st.chinese+st.english total from grade st where (math+chinese+english) >= ALL (select score from scholarship); #查询能获得一等奖学金的同学记录
mysql> select st.id, st.name, st.math+st.chinese+st.english total from grade st where (math+chinese+english) < ALL (select score from scholarship); #查询不能获得奖学金的同学记录
457
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
