MySQL数据库基础（笔记整理一）

数据库的组织结构

MySQL属于关系数据库管理系统(Relational Database Management System, RDBMS)

数据库(Database,DB)即一个用来存放信息的仓库，他们构造简单，遵守一定规则；

数据库里的数据集合都存放在数据表(table)里；

数据表由数据行(row)和数据列(column)构成；

一个数据行就是数据表里的一条记录(record);

记录可以包含多个信息项，数据表里的每一个数据列都对应一个信息项。

管理系统(Management System, MS) 指的是用来对数据进行插入、检索、修改、删除等操作的软件。
关系(Relational ,R)表示RDBMS是DBMS中的一种，RDBMS专长是把分别存放在两个数据表里的信息联系起来（匹配），这种联系是通过查找两个数据表的共同元素来实现的。RDBMS的强项在于它能方便地抽取出数据表里的数据并把它们与其他数据表里的信息结合起来为那些单独利用某个数据表无法找到答案的问题提供答案。

数据库的查询语言

为了与MySQL交互，你需要使用一种名为SQL（Structed Query Language，结构化查询语言）的语言。SQL是标准化数据库语言，SQL的各种语句是它能够搞效率地与你的数据库尽心互动。

MySQL的体系结构

MySQL采用的是C/S（客户/服务器）体系结构。

mysqld （MySQL的服务器程序），运行在存放你数据库的机器上。负责在网络上监听并处理来自客户的服务请求，并根据这些请求去访问数据库的内容，再将有关信息回传给客户。
Client （MySQL的客户程序），负责连接到数据库服务器，并通过向服务器发出命令来告知它们需要哪些信息。
MySQL的“客户/服务器”体系结构的优点：

并发控制，由服务器提供，因而不会出现两个用户同时修改一条记录的现象。
你不必非要在存放你数据库的那台机器上登录MySQL。

创建MySQL用户并设置访问权限

首先以root的身份登录MySQL，再用CREATE USER和GRANT语句新建一个MySQL用户，并分配访问数据库的权限。

$ mysql -u root -p
Enter password: \*\*\*\*\*\*
mysql> CREATE USER 'example'@'localhost' IDENTIFIED BY '123456' ;
mysql> GRANT ALL ON webdata.* TO 'example'@'localhost' ;

mysql命令的-p选项会让mysql提示MySQL的root用户输入密码。假设你打算使用远程登录的方式管理数据库，CREATE USER中的localhost就应该改为%（或者你要使用的另一台计算机的IP）

在创建用户账号后，必须接着分配访问权限。新创建的用户账号没有访问权限。它们能登录MySQL，但看不到数据，也不能对任何数据库进行操作。

可以使用 SHOW GRANTS FOR example; 查看用户的权限

使用GRANT语句，至少要给出：1)要授予的权限; 2)被授予访问权限的数据库或表; 3)用户名

多次授权允许用逗号分隔，将多条GRANT语句连在一起，如：GRANT SELECT, INSERT ON databse.* TO example;

同样要取消用户的特定权限，可以用REVOKE语句。（用法参考GRANT）

GRANT和REVOKE可在几个层次上控制访问权限：

整个服务器 (GRANT ALL 和REVOKE ALL)

整个数据库( ON database.*)

特定的表(ON database.table)

特定的列

特定的存储过程

在使用GRANT和REVOKE时，用户账号必须存在，但对所涉及的对象没有这个要求。这允许DBA在创建数据库和表之前设计和实现安全措施。

建立/断开与MySQL服务器的连接

mysql -h host_name -p -u user_name [database]

-h host_name 带连接的服务器主机名。本地可省略。

-u user_name 你的MySQL用户名。

-p 提示输入MySQL口令。

连接常用的数据库（不必在USE database）

quit/exit 即可断开与MySQL的连接。

小技巧：

Linux下可以将常用的mysql命令写入 ～/.bashrc 例如，可以将以下语句写入 .bashrc：

alias mysql-example='mysql -u example -p sample'
alias mysql-root='mysql -u root -p'
alias mysqlstart='sudo systemctl start mysqld'
alias mysqlstop='sudo systemctl stop mysqld'
alias mysqlstatus='sudo systemctl status mysqld'

在执行 source .bashrc ，再次连接MySQL数据库只需输入mysql-example即可连接到example用户，而启动/停止/查看，MySQL服务用上面的别名也会方便很多。

执行SQL语句

连接上服务器后，现在就可以通过select语句进行相关的数据查询了。利用mysql命令行的方式进行数据库查询很简单：敲入有关命令，命令末尾以分号 ; 或\g 表示语句结束。事实上，这个过程是在你输入完查询语句之后，查询命令将由mysql发往服务器执行，服务器对查询进行处理并把其结果返回给mysql，最后由mysql将查询结果显示在屏幕上。
例如：

mysql> SELECT NOW();

上面这条语句将返回系统的当前日期和时间。上面说过，除了分号 ; 以外 \g (表示go) 也可以表示语句的结束。其实还可以使用 \G (将会竖直排列显示结果，每行一个值)

mysql> SELECT NOW(), USER(), VERSION() \G
     NOW():    2018/10/07    17:21
    USER():    example@localhost
VERSION:     10.1.36 -MariaDB

如果查询命令的输出行比较短，以 \G 作为查询命令的结束符效果还不太明显。但万一输出行比较长，在屏幕上显示为好几行的时候，\G 结束符就能使屏幕输出的内容更便于阅读。

因为mysql必须等待语句结束符，所以我们没必要把查询命令完整地写在同一行上。例如：

mysql> SELECT NOW(),
    ->    USER(),
    ->    VERSION()
    ->    ;

假如，我已经输入了好几行查询命令但却突然发现不应该执行它，输入 \c 即可取消它。有一点特别注意，当查询命令的 '未配对的的情况下，应该用 '\c 来取消执行。

批处理的方式执行SQL语句

可以用SQL脚本的方式，让mysql从文件读入数据和命令也不是通过键盘的输入。这样的处理方式，在数据量比较大的时候，要比在命令行插入数据高效的多。我们可以通过shell的输入重定向功能，将SQL语句写入一个名为 mysqlscript.sql的文件中，通过下面的方式执行它们：

$ mysql < mysqlscript.sql

或者是在msql命令行中以 source 的方式执行sql脚本：

mysql> source /home/example/mysqlscript.sql

需要注意的是，mysqlscript.sql 要给出它的绝对路径。通过这样的方法，我们可以方便的批量创建一系列表或者从很多文件中LOAD 数据。

创建数据库

创建数据库：首先用mysql连接服务器，以root的身份创建数据库 CREATE DATABASE database1; 任何时候都应该避免直接登录root去操作数据库。
创建数据库之后，并不意味着已经把它选定为当前的默认数据库了。你需要用 USE database1把它设置为当前默认数据库。选定数据库的另一种方法是在上一篇中提到的在启动mysql的命令行上给出数据库的名字：

$ mysql -u example -p database1

创建数据表

创建数据表，可以用CREATE TABLE语句来完成，这条语句的格式如下：

CREATE TABLE tbl_name (column_specs);
其中，tbl_name 是表名，column_specs则是该数据库里的各个数据列以及各种索引的定义。索引能够加快信息的检索速度。

CREATE TABLE customers
(     
    stu_id      int    UNSIGNED    NOT NULL     AUTO_INCREMENT,
    stu_name    char(50)  NOT NULL ,
    stu_address char(50)  NULL ,                                                
    stu_city    char(50)  NULL ,
    stu_contact char(50)  NULL ,
    stu_email   char(255) NULL ,
    stu_gradate    date    NOT NULL,
   PRIMARY KEY (stu_id)
   ) ENGINE=InnoDB;

上面就是一个创建数据表的CREATE TABLE语句，执行这条语句的方法有好几种。可以手动输入，也可以写入create1.sql脚本中通过shell输入重导向。

$ mysql -u example -p database1 < create1.sql

执行上面的命令时，需要切换到create1.sql下去执行，否则应该给出create1.sql的绝对路径。

在Linux中执行这条语句时，mysql -u example -p可以另起一个别名以此来简化输入。
当然你也可以，在连接数据库之后 source create1.sql的方式执行脚本，同样可以完成创建数据库的操作。(source 后面要跟create1.sql的绝对路径)

在上面的数据表中，大部分数据列的类型都是可变长度的字符串。只有两个数据列例外，用来保存学号的stu_id和毕业期限的 stu_gradate 而数据列stu_id的值必须是唯一的。这也是为什么我们用stu_id 来作为上表的主键，而AUTO_INCREMENT当我们没有给出stu_id 列的值时，会自动生成下一个编号并赋值给这个数据列。数据表class的定义包含如下几个部分：

INT 表示这个数据列将用来保存整数值
UNSIGNED 不允许出现负数
NOT NULL 必须填有数据，不得为空
AUTO_INCREMENT 主键自增属性
PRIMRY KEY (stu_id) 将数据列stu_id 设为数据表主键
ENGINE=InnoDB 为数据表的设置InnoDB引擎（支持事物处理）

当创建完数据表后，可以用 DESCRIBE class ; 命令，查看class表相关的结构信息。也可以用 DESC class ; 查看，DESC是DESCRIBE的简写。

DESCRIBE class;
DESC class;
EXPLAIN class;
SHOW COLUMNS FROM class;
SHOW FIELDS FROM class;

上面这几条语句实际上都是一样，显示class数据表的信息。
SHOW 还有其它更多的用法，例如:

SHOW COLUMNS FORM class LIKE '%id' ;

可以将输出内容限制为特定的数据列。

SHOW TABLES;
SHOW DATABASES;
SHOW WARNINGS;

上面都是SHOW的用法，第一条SHOW语句表示列出数据库中都有哪些数据表；第二条SHOW语句表示列出当前连接的服务器上的数据库；最后一条用于在sql语句提示warnings时，查看wanrnings信息。

添加新的数据行

利用INSERT语句添加数据

INSTERT INTO tbl_name VALUES(value1, value2,....);

mysql>    INSTERT INTO student VALUES(‘RYLE', 'M', NULL);

在MySQL中也可以用一条INSERT 语句把多个数据行插入到数据表里去，语法如下；

    INSERT INTO tbl_name VALUES(...),(...),...;

mysql>    INSERT INTO student VALUES('Avery','F', NULL),('Nathan','M', NULL);

更可靠的INSERT语句是对数据列进行赋值，先给出数据列的名字，再列出它的值。语法如下：

    INSERT INTO tbl_name (col_name1,col_name2,...) VALUES(value1, value2,...);

mysql>    INSERT INTO member (last_nmae,first_name)VALUES('Stein', 'Waldo');

这种形式同样可以一次插入多个记录：

mysql>    INSERT INTO student (name,sex) VALUES('Abby','F'), ('Joseph','M');

没有在INSERT语句中出现的数据列将被赋予默认值。例如：上面两条语句没有给出member_id的数据列的值，所以MySQL将把默认值NULL赋给它们。而AUTO_INCREMENT数据列，将赋给member_id 一个自动生成的下一个序列号。

还可以使用包含col_name =value （非values) 的SET子句的INSERT语句给数据列赋值。

    INSERT INTO member SET last_name =’Steim‘， first_name = ’Waldo‘ ;

同样没有出现在SET子句里的数据列将被赋予为默认值。这种形式的INSERT语句不允许一次插入多个数据行。

通过从文件中读取来添加新行

把数据记录加载到数据表里的另一个方式是从一个文档里把它们直接读出来。文档内容可以是一些新行添加的INSERT语句。也就是上面提到过的运行SQL脚本的方法：

    $ mysql-example sampdb < insert_president.sql;

或者在mysql命令行下：

    $ source insert_president.sql ;

上面两条语句都是基于文档里的内容是新加记录的INSERT语句，如果文档中的记录并不是以INSERT语句的形式写在文挡中，而是以纯数据值的形式来存放的，我们这时可以用Load DATA语句或mysqlimport工具来加载它们，例如：

    mysql > LOAD DATA LOCAL INFILE 'member.txt' INTO TABLE member;

假设数据就是保存在member.txt文档中，上面的语句将读这个文件并把它的内容发送到服务器以加载到member数据表。

上面的LOAD DATA 语句还有一点需要注意，在默认情况下，LOAD DATA 语句将默认各数据列的值以制表符分隔，各数据行以制表符分隔，数据值的排列顺序与各数据列在数据表里的先后顺序一致。如果不使用默认的制表符作为txt文件的分隔符，则需要指定文件记录的分隔格式，要用到fields关键字。例如：

LOAD DATA INFILE 'member.txt' INTO TABLE member FIELDS TERMINATED BY ';'  ;

上面这条语句给出了如何加载以;作为分隔符的数据文件。

检索信息

SELECT语句允许以你喜欢的方式检索和显示数据表里的信息。通常SELECT语句由以下几个部分组成：

SELECT what to retrieve
FORM table or tables
WHERE conditions that data must satisfy;

在写SELECT语句时，尽量把你想检索的东西描述清楚，再把可选子句写出来。(FROM和WHERE就是比较常见的两个子句)，其他子句包括GROUP BY、ORDER BY和LIMIT等。
有趣的是，SQL语句对书写格式的要求并没有Shell或大多数编程语言那么严格，所以你在书写SELECT语句时，选择换行符的位置以及大小写的要求时完全可以按照你的个人习惯来书写，但是良好的书写习惯会使SQL语句的阅读性和后期的数据库维护更便捷。

FROM子句一般不能省略(你要指定从哪个数据表检索数据)，但SELECT 当操作不涉及数据表时，完全没有必要把FROM子句写出来，例如：

mysql>    SECLECT 2+2,    'Hello, World!',    VERSION();

MySQL允许把表达式的计算结果当做输出列的值，而不引用数据表。但是，当你明确需要使用哪个数据表检索数据时，应该用FROM子句指定数据表，当然还需要把想要查看的数据列的名字列举出来。例如：

mysql>    SELECT * FROM student;

上面这条查询将把student数据表的所有数据列全部显示出来。（当然，检索时应尽量避免 * 的使用）想要查询某一列的数据时，在SELECT后把数据列的名字逐个列出来就行，多个数据列之间用逗号隔开。例如：

mysql>    SELECT name, student_id FROM student;

上面提到过SQL语句对于大小写并没有严格的要求，但是需要注意的是，数据表的名字和数据库的名字可能需要区分大小写。（比如，在Linux系统上数据表和数据库的名字是严格区分大小写的）

指定检索条件

想要完成更为精确的查询，需要给SELECT语句加上一个WHERE子句来筛选你想要的数据。例如：

mysql>    SELECT * FROM score WHERE score > 95;

或者匹配某一特定行的记录：

mysql>    SELECT name, city FROM student WHERE student_id = '2017011111' ;

WHERE子句里的表达式允许使用算术运算符、比较运算符和逻辑运算符，你可以灵活的使用常数、数据表的数据列和函数调用进行运算。例如：

mysql>    SELECT name, birth , city, score FROM student 
        ->    WHERE birth < '1999-10-10' AND city IN('长春‘  ,  '吉林') ;

上面的这条查询的意思是，从student表中找出家在长春或者吉林并且出生日期早于1999年10月10日的同学，并把他们的姓名、生日、籍贯、分数列出来。在这条查询中，用IN()操作符来查找几个值中的某一个会很方便。

NULL 值

NULl值是一个很特殊的值。它的含义是缺省（无数据或者未知数据），所以不能用它与“有数据”的值进行运算或者比较。如果需要对NULL值进行查找，就必须使用IS NULL 或IS NOT NULL 来判断。例如：在一张记录学生成绩的表中把缺考的学生找出来：

mysql>    SELECT name, student_id, score FROM student_exam 
        ->    WHERE score IS NUll;

当然使用MySQL专用的比较操作符 <=> 也能完成NULL值与NULL 值之间的比较。上面的查询可以改为：

mysql>    SELECT name, student_id, score FROM student_exam
        ->    WHERE NOT( score <=> NULL);

   需要注意的是，NULL值与0或者空字符串 并不相等。

对查询结果进行排序

虽然数据记录在查询结果中的先后顺序通常与他们在当初被插入时的先后顺序一致。但如果数据表一旦经过增删改查等一些操作后，这些操作往往会改变数据行在服务器所返回的数据表检索结果中的先后顺序。所以，除非你能够保证从服务器返回的数据事先没有经过任何变动。否则，想让查询结果返回你希望的先后顺序显示，就必须给查询命令增加一条ORDER BY 子句。例如：

mysql>    SELECT name, student_id, sex, score FROM 
        ->    student ORDER BY score;

ORDER BY子句默认的排序方式是升序(ASC)，在ORDER BY 子句中的数据列名字后面加上DESC则表示按降序排列。也可以对查询结果的多个数据列进行排序，而每一个数据列又都可以互不影响地分别按升序或降序进行排列。例如：

mysql>    SELECT name, student_id, score, sex, city 
        ->    FROM student ORDER BY score DESC, student_id ASC;

   对于包含NULL的数据行，如果设定按升序排列，他们将出现在查询的开头；如果设定按降序排列，他们将出现在查询结果的末尾。

限制查询结果中的数据行个数

查询结果往往由很多数据行构成，如果你只想要其中一小部分，可以可查询命令增加一个LIMIT子句。ORDER BY 配合LIMIT 1列出查询中的排在第一行的那条结果。当然，你也可以指定从查询结果中抽出一部分，此时必须指定两个值，第一个值给出要在查询结果的开头部分跳过的数据记录个数，第二个值则是需要返回的数据记录个数。例如：

mysql>    SELECT name, student_id, score 
        ->    FROM student ORDER BY score DESC LIMIT 10, 5;

上面这条查询返回成绩排在11到15名的同学的信息。

对输出列进行求值和命名

前面提到过MySQL允许把表达式的结果当做输出列的值，而不引用数据表。数据表里的数据列名字也可以用在表达式里，例如：

mysql>    SELECT CONCAT(student_id,' ', name) as stuinfo, CONCAT(city, ',' ,state) 
        ->    FROM student;

在上面的查询中，我们对输出咧的格式进行了设置：把学生的学号和姓名合起来显示（以空格分隔）,城市和省份也合起来输出（以逗号分隔），并且在输出结果中分别以别名显示这两列的标题。在为数据列提供别名时，关键字AS可以省略，但是如果省略它稍不留神可能会出现错误，例如：

mysql>    SELECT name city FROM student;

上面的查询本意是查询学生的姓名和籍贯，但是漏了name和city数据列之间的逗号，于是city将被视为name的别名从而成为了输出列的表头。

与日期相关的问题

MySQL中常见的有关日期的操作，有下面几种：

按日期排序
查找某个日期或者某个日期范围
提取日期中的年、月、日等组成部分
计算两个日期之间的时间距离
用一个日期加上或减去一个时间间隔以求出另一个日期

# 1.
mysql>    SELECT * FROM student WHERE date = '2018-10-10' ;

# 2. 
mysql>    SELECT * FROM student WHERE date >= '2018-10-01' AND date < ' 2018-10-13' ;

# 3.  日期中的年、 月、 日可以用函数 YEAR() 、 MONTH()、 DAYOFMONTH() 分别提取出来。
mysql>    SELECT * FROM student WHERE MONTH(birth) = 3;  # 把生日在3月的同学列出来，也可以用MONTHNAME(birth) = 'March'

#4. 生日在同一天的同学，不一定同年。（所以可以用DAYOFMONTH)
mysql>    SELECT * FROM student WHERE MONTH(birth) = 3 AND DAYOFMONTH(birth) = 29;

#5. 
mysql>    SELECT name, city, TIMESTAMPDIFF(YEAR, birth,CURDATE()) as age FROM student 
        ->    WHERE birth IS NOT NULL  ORDER BY age DESC LIMIT 1;

模式匹配

MySQL支持模式匹配操作，这使得我们能够在没有给出精确比较值的情况下把有关的数据行检索出来，模式匹配用（LIKE和NOT LIKE 操作符），还需要你提供一个包含通配符的字符串。_只能匹配一个字符， % 能匹配零到任意字符序列。例如：

mysql>    SELECT name, city, student_id FROM student WHERE student_id LIKE '1%' ;

mysql>    SELECT name, city , student_id FROM WHERE city LIKE '__';

MySQL还提供基于正则表达式和REGEXP操作符的另一种更为灵活和强大的匹配形式。例如：

mysql>    SELECT name, city , student_id, sex FROM WHERE city REGEXP '^吉’ ;
# 把籍贯以吉开头的同学的信息列出来
# 更多关于正则表达是的知识，可以参考我关于正则表达是的笔记

设置和使用SQL变量

MySQL允许自定义变量。我们可以使用查询结果来设置变量，这使我们能够方便地把一些值保存起来以供今后查询。

mysql>    SELECT @score := score FROM student WHERE score = 60 ；

mysql>    SELECT name, student_id , score FROM student 
        ->    WHERE score > @score ORDER BY score DESC;

变量额命名语法是“@变量名” ，赋值语法是在SELECT语句里使用一个“@变量名：= 值” 形式的表达式。其实上面的查询可以通过一个联结或子查询语句得到，稍后我们会看到。
SET语句也能用来对变量赋值。

mysql>    SET @today := CURDATE() ;

生成计数信息

MySQL最有用的功能之一是它能够依据大量未经加工的数据生成多种统计汇总信息。找出一组数据里到底有多少种不同的取值是一项比较常见的统计工作，而关键字DISTINCT恰好能让我们把在查询结果中重复出现的数据行清除掉。例如：

mysql>    SELECT DISTINCT city FROM student ORDER BY city;

上面的查询把学生籍贯不加重复的列举出来。另一个比较常见的统计工作是利用COUNT() 函数来计数。COUNT(*)能把你的查询到底选取了多少数据行做一个统计。例如：

mysql>    SELECT COUNT(*) FROM student WHERE score <60 ;

上面的查询返回成绩不及格的同学的人数。COUNT(*)的统计结果是被选中的数据行的总数，而COUNT(数据列名称)值则只统计全体非NULL值的个数。COUNT()可以和DISTINCT连用，用以统计有多少不同的非NULL值。例如：

mysql>    SELECT COUNT(DISTINCT city) FROM student;

上面的查询可以统计出学生数据表中到底有多少不同的城市个数。COUNT()函数和WHERE子句连用，可以筛选出不同类型的个数。例如：

mysql>    SELECT COUNT(*) FROM student WHERE sex = 'F';
mysql>    SELECT COUNT(*) FROM student WHERE sex = 'M';

事实上上面的查询可以用更为方便的GROUP BY子句进行分类，MySQL可以只用一个查询就把某数据列里的不同值分别出现过多少次的情况统计出来。上面的例子可以做如下修改，例如：

mysql>    SELECT sex, COUNT(*) AS 人数 FROM student GROUP BY sex ;

如果需要进行这种分门别类的统计，GROUP BY子句是必不可少的选择，它的作用是让MySQL知道在统计之前应该如何对有关的数据记录分类。与反复使用多个彼此近似的查询来分别统计某数据列不同取值出现次数的做法相比，把COUNT(*)函数与GROUP BY子句相结合的做法有很多有点：

在开始统计之前，我们不必知道将被统计的数据列不同取值出现次数到底有多少种不同的取值
我们只需要使用一个而不是好几个查询
因为只用一个查询就能把所有的结果都查出来，所以我们还能对输出进行排序

前两个优点有助于简化查询语句的书写，而第三个优点它能让我们更加灵活地显示查询结果。例如：

mysql>    SELECT city, COUNT(*) AS 人数 FROM student GROUP BY city ORDER  BY 人数 DESC;

上面的查询把学生表中分别来自什么城市做一个分类，统计数量后按降序排序。（来自哪个地方的学生最多）如果你打算用ORDER BY 子句对一个计算出来的结果进行归类，可以使用输出列的别名或者它们在查询结果里的出现位置来设定（不推荐，也不属于标准SQL的一部分）。例如：

mysql>    SELECT MONTH(birth) AS Month, MONTHNAME(birth) AS name, COUNT(*) AS 人数 
        ->    FROM student GROUP BY name ORDER BY Month;

同样，COUNT()函数还能与ORDER BY 和LIMIT子句联合使用。而想要把与某个特定COUNT()值相对应的记录找出来，需要使用HAVING子句。（WHERE和HAVING，简单地可以理解为一个在分组前筛选数据[WHERE]，一个在分组后筛选数据[HAVING]，WHERE后面不能跟聚合函数）例如：

mysql>    SELECT city, COUNT(*) AS 人数 FROM student GROUP city HAVING 人数 > 2 ORDER BY 人数 DESC;

除COUNT()以外，MySQL还有其他一些汇总函数。（MIN()、MAX()、SUM()和AVG()..) 要让MySQL对数据行分组统计结果做进一步的统计得到所谓的 “超级聚合” 值。加上WITH ROLLUP 子句即可。例如：

mysql>    SELECT sex, COUNT(*) FROM student GROUP BY sex WITH ROLLUP;

上面这条查询将对两种性别的学生人数进行汇总并生成一行输出。

多表查询

当你打算从多个数据表选取信息时，有一种方法叫做联结(join)。把一个数据表与另一个数据表中的信息结合起来才能得到查询结果。联结操作是通过并把两个（或多个）数据表里的同类数据进行匹配而完成的。多表操作的另一种方法是将SELECT语句嵌套在另一个SELECT语句里，前者叫做子查询。例如：

mysql>    SELECT student.name,grade_event.date, score.score, grade_event.category 
        ->    FROM grade_event INNER JOIN score INNER JOIN student 
        ->    ON grade_event.event_id = score.event_id 
        ->    AND score.student_id = student.student_id
        ->    WHERE grade_event.date = '2018-10-07' ;

这条查询先查出给定日期的grade_event行，再利用此行里的event_id 把score数据表里拥有同一event_id 的考试分数都查出来，并且把score和student数据表里都有student_id的数据列查出来，利用这个关系把学号映射为他们的姓名。每找到一组彼此都匹配的grade_event行和score行，score行和student行，就把学生的姓名、考试分数、日期和考试事件的类型显示出来。这条查询与单表查询相比：

在FROM子句里，我们列举出了多个数据表的名字，因为我们要从多个数据表里检索数据
在ON子句里，我们给出了grade_event数据表和score数据表，score数据表和student数据表的联结条件，这grade_event和score数据表里的event_id 列的值必须相互匹配（并且为了消除歧义，这里对event_id进行了完全限定)，而score和student数据表里的student_id列的值必须相互匹配。

换一种情况，加入现在有一个表是记录学生们的考试缺考情况（absence表），如果你想看到缺考的学生信息，就需要把absence表与student表通过student_id 列的值联结起来。例如：

mysql>    SELECT student.student_id, student.name,
        ->    COUNT(absence.date) AS absences
        ->    FROM student INNER JOIN absence 
        ->    ON student.student_id = absence.student_id
        ->    GROUP BY student.student_id;

上面的这条查询对于想知道都有哪些学生缺考的情况，查询结果正是我们需要的。但假如你需要对所有学生的考生出勤情况做一个统计，（即需要同时显示出没有缺考的学生信息）那么我们就需要用LEFT JOIN来代替普通的联结操作。 LEFT JOIN 将使MySQL 为联结操作中第一个数据表（即关键字LEFT JOIN左边的数据表)里的每一个中选数据行生成一个输出行。上例可以修改为：

mysql>    SELECT student.student_id, student.name,
        ->    COUNT(absence.date) AS absences
        ->    FROM student LEFT JOIN absence
        ->    ON student.student_id = absence.student_id 
        ->    GROUP BY student.student_id;

上面这条查询，不仅可以列出缺考的学生的信息，还会返回没有缺考的学生的信息，并且缺考次数统计为0 。

   RIGHT OUTER JOIN ....ON 即为右联结，与左联结正好相反，用法相同。其实对左联结来说，将表的顺序交换就是右联结。还有全联结（全外联结），但MySQL不支持此种联结方式。

联结操作并非只能作用于不同的数据表，你可以把某个数据表和它自身结合起来。例如：你想知道在student表中是否有两个同学籍贯相同。

mysql>    SELECT s1.name, s1.city, s1.state FROM student AS s1 INNER JOIN student AS s2 
        ->    ON s1.city = s2.city AND s1.state =s2.state 
        ->    WHERE (s1.name != s2.name)
        ->    ORDER BY state, city , name;

这条查询有两个需要注意的地方：

它需要两次用到同一个表，所以我们必须为它创建两个别名（s1 和s2）才能把表中的同名数据列区分开来。
用WHERE子句来确保每名学生的记录只能与其他学生相匹配，来剔除“记录与它本身相匹配”的情况。

   MySQL认为任何一个查询都是一次“关联” ，并不仅仅是一个查询需要两个表匹配才叫关联，所以在MySQL中，每一个查询，每一个片段（包括子查询，甚至是基于单表查 询）都可以是一次关联。MySQL执行关联的策略很简单：MySQL对任何关联都执行嵌套循环关联操作。即MySQL先在一个表中循环取出单条数据，然后在嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中的匹配行为止。然后根据各个表匹配的行，返回查询需要的各个列。

多表检索的另一类方法，嵌套查询

嵌套查询也就是它一条SELECT语句嵌套在另一条里。例如：

mysql>    SELECT * FROM student WHERE student_id
        ->    NOT IN(SELECT student_id FROM absence);

上面的这条查询把全体没有缺勤的学生找出来。嵌套与内层的SELECT语句用来生成一个absence数据表里出现过的student_id 值的集合，外层的SELECT语句负责把与该机合理的任何一个ID值都不匹配的student数据行检索出来(NOT IN)。

END

posted @ 2018-10-07 19:26 Keith_fool 阅读(448) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Login: life &

人生到处知何似？应似飞鸿踏雪泥。