blueyi's notes

Follow Excellence,Success will chase you!

0%

SQL学习总结

Posted on In
Symbols count in article: 55k Reading time ≈ 50 mins.

对SQL的了解一直就那几个语句，关键是那几个语句还经常记不清楚，虽然上过一次数据库的课，但并没有认真听，也没有系统的学习一遍SQL的基础知识，所以对于SQL，基本还处于完全陌生态。趁今晚的闲暇来系统的学习一下SQL相关知识（其实是因为今晚不想学习，权当休闲，哈哈）。
本文内容主要参考:

  • 《SQL必知必会》
  • 《MySQL必知必会》（后来发现有这本书，所以就补上了）

工欲善其事，必先利其器。为了方便后面练习，首先安装MySQL，这里只以Ubuntu下的安装为例

后期补充内容：
1.为了使字段名可以使用MySQL关键字，同时也为了更好地区分字段名、表名等，最好在MySQL语句中使用反单引号（backtick）

1
`

将表名、字段名等括住，例如：

1
SELECT `user_name`, `user_id`, `score` FROM `user_db`.`user`;

具体更多原因查看SOF上的回答：http://stackoverflow.com/questions/11069044/what-does-back-tick-do-in-mysql-statements

安装MySQL

由于只是用于练习之用，所以暂不考虑各种编译优化等，所以直接使用apt安装。
安装方法可以参见官方A Quick Guide to Using the MySQL APT Repository

安装命令如下：

1
2
3
sudo apt-get install mysql-server #安装MySQL服务端及核心程序
sudo apt-get install mysql-client #安装MySQL客户端
sudo apt-get install mysql-workbench #安装MySQL工作台，这个工作台提供对MySQL的管理，非常好用，而且有图形界面

安装过程中会提示输入root密码或直接确认设置默认密码为空
安装结束后使用以下命令验证安装是否成功：

1
sudo netstat -tap | grep mysql

如果安装成功，输出大致为：

1
2
blueyi@vm:~$ sudo netstat -tap | grep mysql
tcp        0      0 localhost:mysql         *:*                     LISTEN      9495/mysqld

MySQL管理命令：

1
2
3
sudo service mysql status   #查看MySQL的当前状态
sudo service mysql stop   #停止MySQL服务
sudo service mysql start   #启动MySQL服务

当然还有如下：start|stop|restart|reload|force-reload|status管理MySQL服务的命令

概念

1.数据库（database）是指保存有组织的数据的容器，一个数据库中可以包含多个表（table），每个表中都可以存储同类型的多条数据。表可以被想像成类似excel中由行和列构成的表一样。
2.通常所说的MySQL、PostgreSQL、SQLite及Microsoft SQL Server等并不是数据库，它们只是被称为数据库管理系统（即DBMS）的数据库软件，数据库是通过DBMS创建和操作数据的容器。而SQL是一咱语言而不是一个应用程序。
3.数据库中的表（table）是指某种特定类型数据的结构化清单。同一个数据库中的每个表名都必须唯一。表具有一些特性，这些特性定义了数据在表中如何存储等信息，这组信息即称为模式（schema），模式即可以用来描述数据库中特定的表，也可以用来描述整个数据库（和其中表的关系），所以模式即关于数据库和表的布局及特性的信息。
4.表中的列（column）即表中的一个字段，数据库中的每个列都有相应的数据类型（datatype），数据类型及其名称是SQL不兼容的一个主要原因。
5.表中的行（row）是表中的一个记录（record），表中的数据是按行存储的。
6.主键即是指一列（或一组列），其值能够唯一标识表中的每一行。能够唯一标识表中每行的这个列称为主键。虽然不总是需要主键，但应该总是定义主键。表中的任何列都可以作为主键，一个表可以有多个主键，主键需要满足以下条件:

  • 任意两行都不具有相同的主键值；
  • 每一行都必须具有一个主键值（主键列不允许NULL值）；
  • 主键列中的值不允许修改或更新；
  • 主键值不能重用（如何某行从表中删除，它的主键不能赋给以后的新行）

7.SQL（发音为字母S-Q-L或sequel）是结构化查询语言（Structured Query Language）的缩写。SQL是一种专门用来与数据库沟通的语言。许多DBMS厂商通过增加语句或指令来对SQL进行扩展。标准SQL由ANSI标准委员会管理，称为ANSI SQL，各个DBMS都有自己的名字，例如MySQL，但它们都会支持标准SQL。

MySQL基本操作概览

为了后续先讲解SELECT等基础语句，这里先迅速脑补一下MySQL的基本操作

登录

1.打开MySQL，并使用root用户登录：

1
2
3
4
5
6
7
~$ sudo service mysql start
#不带-h参数，默认表示登录本地localhost的mysql
~$ mysql -u root   #如果MySQL的root密码为空，直接登录，否则会报ERROR 1045
~$ mysql -u root -p    #回车会提示你输入你的root账户登录密码
~$ mysql -u root -p123    #直接附带上登录密码123，注意密码与-p之间没有空格
~$ mysql -u root -p123    #直接附带上登录密码123，注意密码与-p之间没有空格
~$ mysql -h 192.168.1.1 -P 3306 -u root -p123    #-h后面是要登录的mysql主机IP，-P后面是端口

登录成功后会提示如下：

1
2
3
4
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.5.46-0ubuntu0.14.04.2 (Ubuntu)
#....

并且bash的前导符会变成mysql>

查看

2.查看数据库

1
2
3
4
5
6
7
8
9
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

这三个数据库是MySQL安装后自动生成的，每个数据库中都包含有多个不同的表用来存储数据
注意不要漏掉SQL语句后面的分号“;”，如果SQL语句太长可以直接回车换行，直到分号换行才表示一个语句完成，有点类型C/C++
SQL语句大小写不敏感，也就是说上面的查看数据库的语句等价于SHOW DATABASES;

选择某个数据库

3.连接/使用某个数据库

1
2
3
4
5
mysql> USE information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

这个USE语句后面可以没有分号，也会直接执行，另外在选择数据库时数据库名称也可以不区分大小写，但你会发现使用大写的数据库名字通常无法使用Tab键自动补全。

查看表

4.查看表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> SHOW tables;
+----------------------------------------------+
| Tables_in_performance_schema                 |
+----------------------------------------------+
| cond_instances                               |
| events_waits_current                         |
| events_waits_history                         |
| events_waits_history_long                    |
| events_waits_summary_by_instance             |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_global_by_event_name    |
| file_instances                               |
| file_summary_by_event_name                   |
| file_summary_by_instance                     |
| mutex_instances                              |
| performance_timers                           |
| rwlock_instances                             |
| setup_consumers                              |
| setup_instruments                            |
| setup_timers                                 |
| threads                                      |
+----------------------------------------------+
17 rows in set (0.00 sec)

5.查看表列，或称为字段

1
2
3
4
5
6
7
8
9
10
mysql> SHOW COLUMNS FROM performance_timers;
+------------------+---------------------------------------------------------------+------+-----+---------+-------+
| Field            | Type                                                          | Null | Key | Default | Extra |
+------------------+---------------------------------------------------------------+------+-----+---------+-------+
| TIMER_NAME       | enum('CYCLE','NANOSECOND','MICROSECOND','MILLISECOND','TICK') | NO   |     | NULL    |       |
| TIMER_FREQUENCY  | bigint(20)                                                    | YES  |     | NULL    |       |
| TIMER_RESOLUTION | bigint(20)                                                    | YES  |     | NULL    |       |
| TIMER_OVERHEAD   | bigint(20)                                                    | YES  |     | NULL    |       |
+------------------+---------------------------------------------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

或：

1
2
3
4
5
6
7
8
9
10
mysql> DESCRIBE performance_timers;
+------------------+---------------------------------------------------------------+------+-----+---------+-------+
| Field            | Type                                                          | Null | Key | Default | Extra |
+------------------+---------------------------------------------------------------+------+-----+---------+-------+
| TIMER_NAME       | enum('CYCLE','NANOSECOND','MICROSECOND','MILLISECOND','TICK') | NO   |     | NULL    |       |
| TIMER_FREQUENCY  | bigint(20)                                                    | YES  |     | NULL    |       |
| TIMER_RESOLUTION | bigint(20)                                                    | YES  |     | NULL    |       |
| TIMER_OVERHEAD   | bigint(20)                                                    | YES  |     | NULL    |       |
+------------------+---------------------------------------------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

创建数据库

6.创建一个名为mysql_test的数据库

1
2
mysql> CREATE DATABASE mysql_test;
Query OK, 1 row affected (0.00 sec)

查看创建后的数据库变化

1
2
3
4
5
6
7
8
9
10
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_test         |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

批量运行数据库命令

7.批量运行数据库命令
这里使用《MySQ必知必会》中提供的样例文件命令为例，关于文件的说明：

create.txt 包含创建 5 个数据库表(包括定义所有主键和外键约束)的 SQL 语句。
populate.txt 包含用来填充这些表的 SQL INSERT语句。

通过使用source命令，可以导入需要批量执行的文件并执行。注意作者所提供的create.txt并不是使用utf-8编码，且其中含有大量^M结尾的windows平台换行符，在linux下操作会出错，可以使用文本编辑器重新另存成utf-8格式。或者vim打开后，使用:%s /\r$//g清除掉后面的^M后保存，再进行以下操作。也可以直接下载我已经处理好了的这两个文件create.txt、populate.txt·
我们将create.txt和populate.txt中的命令作用于以面创建的数据库mysql_test：
SQL语句中后#号后面是注释，SQL命令后面的内容都是相应SQL命令执行的输出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
mysql> USE mysql_test   #选择mysql_test数据库   
Database changed

mysql> source /home/blueyi/Downloads/TeachYourselfSQL_MySQL/create.txt  #导入create.txt中的命令来创建表
Query OK, 0 rows affected (0.20 sec)

Query OK, 0 rows affected (0.19 sec)

Query OK, 0 rows affected (0.23 sec)

Query OK, 0 rows affected (0.16 sec)
#....省略很多输出#

mysql> show tables;   #查看现在mysql_test中的表都有哪些
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| Customers            |
| OrderItems           |
| Orders               |
| Products             |
| Vendors              |
+----------------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM Customers;   #查看表Customers中的数据，显示为空
Empty set (0.00 sec)

mysql> source /home/blueyi/Downloads/TeachYourselfSQL_MySQL/populate.txt   #导入populate.txt中的命令来填充表内容
Query OK, 1 row affected (0.05 sec)

Query OK, 1 row affected (0.07 sec)

Query OK, 1 row affected (0.06 sec)
#....省略很多输出#

mysql> SELECT * FROM Customers;   #再次查看表Customers中的内容，显然已经成功创建
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id    | cust_name     | cust_address         | cust_city | cust_state | cust_zip | cust_country | cust_contact       | cust_email            |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys  | 200 Maple Lane       | Detroit   | MI         | 44444    | USA          | John Smith         | sales@villagetoys.com |
| 1000000002 | Kids Place    | 333 South Lake Drive | Columbus  | OH         | 43333    | USA          | Michelle Green     | NULL                  |
| 1000000003 | Fun4All       | 1 Sunny Place        | Muncie    | IN         | 42222    | USA          | Jim Jones          | jjones@fun4all.com    |
| 1000000004 | Fun4All       | 829 Riverside Drive  | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens | dstephens@fun4all.com |
| 1000000005 | The Toy Store | 4545 53rd Street     | Chicago   | IL         | 54545    | USA          | Kim Howard         | NULL                  |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
5 rows in set (0.00 sec)

HELP

8.善用HELP命令，直接HELP;会显示总的帮助。HELP command;可以显示想要查看的帮助，例如HELP SHOW;会显示与SHOW相关的命令帮助。

查看错误与警告

查看上一句命令的错误信息：

1
SHOW ERRORS;

查看上一句命令的警告信息：

1
SHOW WARNINGS;

9.退出MySQL

1
2
mysql> quit   #或者exit
Bye

检索数据

通常建议在写SQL语句时，SQL关键字应该使用大写，并且总是以分号“;”结尾，较长的语句可以分成多行，SQL语句中的所有空格都会被忽略。

SELECT语句的基本用法

1.SELECT语句必须至少给出两条信息——想选择什么，以及从什么地方选择。SELECT语句的作用对象是表（table）而不能是数据库。
2.SELECT语句检索单列

1
2
SELECT prod_name
FROM Products;

表示从表Products中选出列prod_name列中的所有内容。
3.SELECT检索多列：

1
2
SELECT prod_id, prod_name, prod_price
FROM Products;

表示从表Products中选择三列并输出这三列，将需要选择的不同列使用逗号,分隔。
4.检索所有列：

1
2
SELECT *
FROM Products;

5.添加关键字DISTINCT检索不相同的值：

1
2
SELECT DISTINCT vend_id
FROM Products;

会从表Products中选出列vend_id下的所有不相同的值，即会自动过滤掉多个相同的值而只保留一个。
DISTINCT会作用于其后面的所有列，也就是说会输出各列中所有不同的值，由于SQL语句的输出总是按原表中各行对应的顺序输出，所以输出结果中有些列会有重复值，但不会所有列都有重复的值。如

1
SELECT DISTINCT vend_id, prod_price FROM Products;

该语句的输出结果为：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT DISTINCT vend_id, prod_price FROM Products;
+---------+------------+
| vend_id | prod_price |
+---------+------------+
| DLL01   |       3.49 |
| BRS01   |       5.99 |
| BRS01   |       8.99 |
| BRS01   |      11.99 |
| DLL01   |       4.99 |
| FNG01   |       9.49 |
+---------+------------+
6 rows in set (0.00 sec)

vend_id列有重复值，但prod_price列没有。
6.SELECT语句默认会返回指定表中匹配的所有行，可以通过限制来返回指定数量的行，但不同的DBMS的实现不同。对于MySQL通过关键字LIMIT和OFFSET来实现。：

1
2
3
SELECT prod_name
FROM Products
LIMIT 5 OFFSET 3;

表示从表Products的prod_name列的第3行开始，输出检索到的前5条数据，LIMIT后面的数据表示需要检索的行数，OFFSET后面的数字表示从第几行开始，SQL中默认行号从0开始。
也可以通过逗号“,”来简写而省略OFFSET关键字，但LIMIT关键字不能省略，且此时逗号前面的数字表示从哪儿开始，逗号后面的数字表示检索的行数，上述语句等价于

1
2
3
SELECT prod_name
FROM Products
LIMIT 3, 5;

7.SQL支持三种注释方式：行内注释使用#或--，注意那是2个短划线后跟一个空格，即#后面可以直接跟注释，短划线后面必须有个空格然后再跟注释内容。或者使用多行注释/*common*/，类似与C语言。

1
2
3
SELECT prod_name   #这是注释
FROM Products      -- 这也是注释
LIMIT 3, 5;       /*这同样是注释*/

排序数据

1.SQL语句由子句构成，有些子句是必须的，有些子句是可选的，一个子句通常由一个关键字加上所提供的数据组成。上面的SELECT…FROM…语句中就有SELECT语句的FROM子句。
2.使用ORDER BY来对SELECT语句输出的结果进行排序，ORDER BY子句可以跟一个或多个列的名字，以这些名字来对输出进行排序，当有多个列时，只有前一个列相同时才按后一个列进行排序。默认情况是按字典序升序排列。

1
2
3
SELECT prod_name, prod_id, prod_price
FROM Products
ORDER BY prod_price, prod_name;

ORDER BY子句可以通过数字来指定相对位置而不是列名，SELECT子句后面的列名从序号1开始，所以上述语句等价于

1
2
3
SELECT prod_name, prod_id, prod_price
FROM Products
ORDER BY 3, 1;

3.ORDER BY子句后面的列可以是表中的任意列，不需要必须是选择的列
4.可以通过ORDER BY子句的列后面跟DESC或DESCENDING来指定该列按降序排列，当然也可以指定ASC或ASCENDING，如果想在多个列上进行降序，必需对每一列指定DESC关键字。上述语句按prod_price和prod_name的降序排列语句为：

1
2
3
SELECT prod_name, prod_id, prod_price
FROM Products
ORDER BY prod_price DESC, prod_name DESC;

过滤数据

1.SELECT语句可以通过WHERE子句进行输出结果的过滤，WHERE子句后面需要跟上相应的过滤条件，大多数DBMS支持以下条件的判断：

操作符 说 明
= 等于
< > 不等于
!= 不等于
< 小于
<= 小于等于
! 不小于
> 大于
>= 大于等于
!> 不大于
BETWEEN..AND.. 在指定的两个值之间
IS NULL 为NULL值

如下例所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT * FROM Products
WHERE prod_price = 3.49;  #选择所有prod_price值为3.49的数据

SELECT * FROM Products
WHERE prod_price BETWEEN 4 AND 10;  #选择所有prod_price值在4到10之间的数据

SELECT vend_id, prod_name 
FROM Products
WHERE vend_id != 'DLL01';  #选择vend_id的值不等于DLL01的vend_id列和prod_name列

SELECT * FROM Products
WHERE prod_price BETWEEN 4 AND 10 
ORDER BY prod_price DESC;  #选择所有prod_price值在4到10之间的数据，并按prod_price降序排列

2.注意上例中对于非数值列进行比较时需要使用单引号将值括起来。ORDER BY子句必须放在WHERE子句的后面，否则会报错。

3.空值检查必须使用IS NULL，并且NULL（即无值，no value）与字段为0、空字符串或只包含空格都不同，而且不能通过检查是否=NULL来验证，必须使用IS NULL。如：

1
2
3
SELECT vend_id, prod_name 
FROM Products
WHERE vend_id IS NULL;  #选择vend_id的值不等于DLL01的vend_id列和prod_name列

4.通过过滤不包含指定值的所有行时，无法获得值为NULL的行，所以过滤数据时，一定要验证被过滤列中含有NULL的行确实出现在返回的数据中。
5.WHERE子句中有多个条件时，可以使用AND或OR进行连接，他们的意义也就是正常的逻辑意思，但当AND与OR混合连接时，默认情况下AND的优先级较高，可以通过加圆括号来改变默认优先级：

1
2
3
4
SELECT prod_id, vend_id, prod_price 
FROM Products 
WHERE (vend_id = 'BRS01' OR vend_id = 'DLL01') AND prod_price >= 4
ORDER BY prod_price DESC, prod_name DESC;

意思是从表Products中选择列prod_id、vend_id、prod_price，且要求vend_id的值为BRS01或DLL01，且prod_price的值大于或等于4，然后将结果进行排序后输出
6.IN操作符可以用来指定条件范围，范围中的每个条件都可以进行匹配。IN取一组由逗号分隔、括在圆括号中的合法值，功能类似于OR，但圆括号中可以有其他SELECT子句。上面5中的语句与下面这句等价：

1
2
3
4
SELECT prod_id, vend_id, prod_price
FROM Products
WHERE vend_id IN ('BRS01', 'DLL01') AND prod_price >= 4
ORDER BY prod_price DESC, prod_name DESC;

IN操作符有很多优点，且它比OR速度要快

7.NOT操作符用来否定其后所跟的条件，如果需要否定后面的每个条件，则需要括号来限定范围，NOT从不单独使用。如要选出prod_price的值不为3.49和9.49的所有数据，代码可为：

1
2
3
SELECT * 
FROM Products  
WHERE NOT (prod_price = 3.49 OR prod_price = 9.49);

8.关键字LIKE可使WHERE子句支持通配符（wildcard）匹配，由字面值、通配符或两者组合构成的搜索条件称为搜索模式（search pattern）。LIKE被称为谓词（predicate）而不是操作符。
SQL通常支持的通配符有：

  • 百分号（%）–用于匹配0个或多个字符，Microsoft Access使用*而不是%
  • 下划线（）–用于匹配1个字符，Microsoft Access使用?而不是

不同的DBMS支持的通配符不一样。
通配符使用示例：

1
SELECT * FROM Products WHERE prod_name LIKE '%bean%';

搜索出prod_name中含有bean的所有行。
注意：

  • 百分号不能匹配NULL
  • 注意表内容后面所跟的空格，包括Access在内的许多DBMS都用空格来填补字段的内容，即如果某列有50个字符，而实际只有10个字符，则会自动使用40个空格填充
  • 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
  • 在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。
  • 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

用正则表达式搜索

1.关于正则表达式的基础知识，可以参见这里正则表达式学习笔记
2.MySQL通过关键字REGEXP来提供对正则表达式的支持。默认情况下不支持大小写，如果要增加对大小写的支持，需要在REGEXP后面跟上关键字BINARY。eg:

1
2
3
4
SELECT prod_name
FROM Products
WHERE prod_name REGEXP '[123] ton'
ORDER BY prod_name;

3.对于字符类的匹配，不支持直接使用\d、\s等，需要使用以下字符类：

  • [:alnum:] 任意字母和数字(同[a-zA-Z0-9])
  • [:alpha:] 任意字符(同[a-zA-Z])
  • [:blank:] 空格和制表(同[\t])
  • [:cntrl:] ASCII控制字符(ASCII 0到31和127)
  • [:digit:] 任意数字(同[0-9])
  • [:graph:] 与[:print:]相同,但不包括空格
  • [:lower:] 任意小写字母(同[a-z])
  • [:print:] 任意可打印字符
  • [:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字符
  • [:space:] 包括空格在内的任意空白字符(同[\f\n\r\t\v])
  • [:upper:] 任意大写字母(同[A-Z])
  • [:xdigit:] 任意十六进制数字(同[a-fA-F0-9])

例如要匹配以数字开头，然后后面跟的是单词inch，后面再跟任意多个字符时，不能使用\d+\s*inch.*，而需要使用[[:digit:]]+[[:blank:]]*inch.*，也就是使用字符类替换元字符。

4.可以使用SELECT语句测试正则表达式。如：

1
SELECT 'Hello world!' REGEXP BINARY 'He.*';

测试语句只能返回0和1,例如上句将返回1。

计算字段

1.存储在数据库表中的数据一般不是应用程序所需要的格式，通常需要通过运行时使用SELECT语句创建字段（field），然后再在应用程序中引用这个字段。
2.字段基本上与列（column）意思相同，但字段通常与计算字段一起使用，可以通过不同的操作符及函数对不同的列进行操作，例如使用CONCAT()函数（MySQL中是CONCAT函数，Access中是+号）将两列字符串拼接为一列后输出，使用+、-、*、/运算符将不同列的数据运算为相应的一列结果。如：

1
2
3
4
SELECT prod_id, quantity, item_price, quantity * item_price
FROM OrderItems 
WHERE order_num = 20008
ORDER BY prod_id;  #将quantity * item_price相乘后输出

3.使用AS可以为新创建的字段创建一个别名，这个别名将可以被外部应用像引用输出的列一样引用。

1
2
3
4
5
6
SELECT prod_id, quantity, item_price, 
CONCAT(prod_id, '(', quantity * item_price, ')') 
AS prod_name_price 
FROM OrderItems  
WHERE order_num = 20008 
ORDER  BY prod_id;

输出内容

1
2
3
4
5
6
7
8
9
10
+---------+----------+------------+-----------------+
| prod_id | quantity | item_price | prod_name_price |
+---------+----------+------------+-----------------+
| BNBG01  |       10 |       3.49 | BNBG01(34.90)   |
| BNBG02  |       10 |       3.49 | BNBG02(34.90)   |
| BNBG03  |       10 |       3.49 | BNBG03(34.90)   |
| BR03    |        5 |      11.99 | BR03(59.95)     |
| RGAN01  |        5 |       4.99 | RGAN01(24.95)   |
+---------+----------+------------+-----------------+
5 rows in set (0.00 sec)

注意prod_name_price就是新别名
别名有时也称为导出列（derived column）

4.SELECT语句可以测试计算，也可以直接执行一些函数，而不需要后面跟FROM子句。eg:

1
2
3
4
SELECT 3 * 2; #计算3 * 2
SELECT TRIM(' abc '); #删除其中的空格
SELECT NOW(); #显示当前时间
SELECT VERSION();  #查看版本

5.不同的DBMS都提供了大量不同的函数和操作符，对于MySQL所支持的函数和操作符，可以参见官方文档Chapter 12 Functions and Operators

函数

基本函数

1.在上面计算字段中已经多次使用了函数，例如CONCAT()用于拼接字符串的函数，TRIM()用于清理多余空格的函数等。各公司的DBMS提供的函数都不相同，SQL函数是不可移植的。但大多数DBMS都提供如下函数：

  • 用于处理文本字符串（如删除或填充值，转换值为大写或小写）的文本函数。
  • 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
  • 用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数。
  • 返回DBMS正使用的特殊信息（如返回用户登录信息）的系统函数。

MySQL提供的函数可在其官方文档functions中找到，有大量非常实用的函数。

2.举一个使用函数的例子：

1
2
SELECT vend_name ,UPPER(vend_name) 
FROM Vendors;  #输出vend_name及vend_name大写之后的内容

3.有一个比较特别的函数SOUNDEX()，该函数可以检测出发音一样的内容，如“Michelle Green”与“Michael Green”发音一样，则可以通过SOUNDEX()函数过滤出这两个发音相同的数据：

1
2
3
SELECT cust_name, cust_contact 
FROM Customers 
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');  #选出cust_contact列中发音与Michael Green一样的内容

输出结果为：

1
2
3
4
5
6
+------------+----------------+
| cust_name  | cust_contact   |
+------------+----------------+
| Kids Place | Michelle Green |
+------------+----------------+
1 row in set (0.00 sec)

验证：

1
2
3
4
5
6
7
mysql> SELECT SOUNDEX('Michael Green'), SOUNDEX('Michelle Green');
+--------------------------+---------------------------+
| SOUNDEX('Michael Green') | SOUNDEX('Michelle Green') |
+--------------------------+---------------------------+
| M24265                   | M24265                    |
+--------------------------+---------------------------+
1 row in set (0.00 sec)

4.日期和时间处理函数是非常重要的一组函数，也经常使用，所以MySQL中对日期和时间采用了相应的数据类型和特殊的格式存储。日期格式通常必须为yyyy-mm-dd，如2016年6月27就是2016-06-27。通常默认的时间数据类型为datetime时，这种类型即包含日期，又包含时间，所以获取这种数据类型的列时需要使用Date()函数。例如查询2012年2月3号的订单：

1
2
3
4
5
6
7
mysql> SELECT order_num, cust_id FROM Orders WHERE Date(order_date) = '2012-02-03';
+-----------+------------+
| order_num | cust_id    |
+-----------+------------+
|     20008 | 1000000005 |
+-----------+------------+
1 row in set (0.00 sec)

注意SQL中除了数值之外的数据，都需要使用单引号括住

5.查看3个月内没有签到的用户：

1
SELECT email FROM user WHERE UNIX_TIMESTAMP(now()) - last_check_in_time > 7862400;

注：last_check_in_time中存储的时间是unix时间戳

聚集函数

1.聚集函数（aggregate function）也就是那些用于汇总数据的函数，MySQL提供的聚集函数有：

Name Description
AVG() Return the average value of the argument
BIT_AND() Return bitwise AND
BIT_OR() Return bitwise OR
BIT_XOR() Return bitwise XOR
COUNT() Return a count of the number of rows returned
COUNT(DISTINCT) Return the count of a number of different values
GROUP_CONCAT() Return a concatenated string
MAX() Return the maximum value
MIN() Return the minimum value
STD() Return the population standard deviation
STDDEV() Return the population standard deviation
STDDEV_POP() Return the population standard deviation
STDDEV_SAMP() Return the sample standard deviation
SUM() Return the sum
VAR_POP() Return the population standard variance
VAR_SAMP() Return the sample variance
VARIANCE() Return the population standard variance

官方文档在这里GROUP BY (Aggregate) Function Descriptions

2.AVG的用法

1
2
3
4
5
6
7
mysql> SELECT AVG(prod_price) AS prod_price_avg FROM Products;
+----------------+
| prod_price_avg |
+----------------+
|       6.823333 |
+----------------+
1 row in set (0.00 sec)

注意AVG的参数只能是一个表达式，当然后面也可以增加WHERE子句来缩小计算范围

3.COUNT()函数有两种使用方式：

  • 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
  • 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

4.MAX()函数不仅可以作用于数值或日期，也可以使用于字符串，作用于字符串时返回该列排序后的最后一行。MIN()函数同理。

5.这些函数也可以计算合计之后的列的值，如：

1
2
3
4
5
6
7
mysql> SELECT SUM(quantity * item_price)  FROM OrderItems;
+----------------------------+
| SUM(quantity * item_price) |
+----------------------------+
|                    5730.70 |
+----------------------------+
1 row in set (0.00 sec)

6.这些函数都默认会忽略掉列值为NULL的行

7.可以使用DISTINCT参数聚集不同的值，这些聚集函数通常都可以如下使用：

  • 对所有行执行计算，指定ALL参数或不指定参数（因为ALL是默认行为）。也就是说ALL参数不需要指定，因为它是默认行为。如果不指定DISTINCT，则假定为ALL
  • 只包含不同的值，指定DISTINCT参数。
    如：
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    mysql> SELECT prod_price FROM Products WHERE vend_id = 'DLL01';  #所有DLL01的价格
    +------------+
    | prod_price |
    +------------+
    |       3.49 |
    |       3.49 |
    |       3.49 |
    |       4.99 |
    +------------+
    4 rows in set (0.00 sec)

    mysql> SELECT AVG( prod_price ) AS avg_price FROM Products WHERE vend_id = 'DLL01';  
    +-----------+
    | avg_price |
    +-----------+
    |  3.865000 |#所有DLL01价格的平均值
    +-----------+
    1 row in set (0.00 sec)

    mysql> SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01'; 
    +-----------+
    | avg_price |
    +-----------+
    |  4.240000 | #使用DISTINCT之后只是价格不同的行进行平均
    +-----------+
    1 row in set (0.01 sec)

8.可以一次使用多个聚集函数：

1
2
3
4
5
SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM Products;

分组数据

1.分组使用SELECT语句的GROUP BY子句创建，如下：

1
2
3
4
5
SELECT vend_id, COUNT(*) AS num_prod, prod_price
FROM Products 
WHERE prod_price >= 4 
GROUP BY vend_id 
ORDER BY num_prod, vend_id;

输出结果为

1
2
3
4
5
6
7
8
+---------+----------+------------+
| vend_id | num_prod | prod_price |
+---------+----------+------------+
| DLL01   |        1 |       4.99 |
| FNG01   |        2 |       9.49 |
| BRS01   |        3 |       5.99 |
+---------+----------+------------+
3 rows in set (0.01 sec)

意思是从表Products中选择vend_id列，prod_price列，并用聚合函数COUNT进行计数，要求prod_price必须大于等于4，使用GROUP BY按vend_id排序并分组数据，最后将计算结果使用COUNT(*)和vend_id排序。
对照以下内容：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT *  FROM Products;
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| prod_id | vend_id | prod_name           | prod_price | prod_desc                                                             |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| BNBG01  | DLL01   | Fish bean bag toy   |       3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG02  | DLL01   | Bird bean bag toy   |       3.49 | Bird bean bag toy, eggs are not included                              |
| BNBG03  | DLL01   | Rabbit bean bag toy |       3.49 | Rabbit bean bag toy, comes with bean bag carrots                      |
| BR01    | BRS01   | 8 inch teddy bear   |       5.99 | 8 inch teddy bear, comes with cap and jacket                          |
| BR02    | BRS01   | 12 inch teddy bear  |       8.99 | 12 inch teddy bear, comes with cap and jacket                         |
| BR03    | BRS01   | 18 inch teddy bear  |      11.99 | 18 inch teddy bear, comes with cap and jacket                         |
| RGAN01  | DLL01   | Raggedy Ann         |       4.99 | 18 inch Raggedy Ann doll                                              |
| RYL01   | FNG01   | King doll           |       9.49 | 12 inch king doll with royal garments and crown                       |
| RYL02   | FNG01   | Queen doll          |       9.49 | 12 inch queen doll with royal garments and crown                      |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
9 rows in set (0.00 sec)

2.由上例可见GROUP BY子句指示DBMS分组数据，然后对每个分组而不是整个结果集进行聚集。
3.GROUP BY在使用时需要注意以下内容：

  • GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
  • 如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
  • GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
  • 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型（如文本或备注型字段）。
  • 除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。
  • 如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
  • GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

4.类似与WHERE子句可以过滤掉特定的行，HAVING子句可以过滤分组。如：

1
2
3
4
5
6
SELECT vend_id, COUNT(*) AS num_prod, prod_price  
FROM Products 
WHERE prod_price >= 4 
GROUP BY vend_id 
HAVING num_prod >= 2 
ORDER BY num_prod, vend_id;

输出结果为

1
2
3
4
5
6
7
+---------+----------+------------+
| vend_id | num_prod | prod_price |
+---------+----------+------------+
| FNG01   |        2 |       9.49 |
| BRS01   |        3 |       5.99 |
+---------+----------+------------+
2 rows in set (0.00 sec)

可见只输出了COUNT(*)大于等于2的数组
5.HAVING子句的语法与WHERE相同，同样支持通配符、逻辑运算等。但它们的意义却完全不同，WHERE在数据分组前按行进行过滤，HAVING是在数据分组后按组进行过滤，WHERE排除的行将不被包括在分组中。
6.GROUP BY分组的数据虽然也会排序，但不应该依赖于它的排序，应该使用ORDER BY进行排序
7.SELECT子句的顺序：

子　　句 说　　明 是否必须使用
SELECT 要返回的列或表达式 是
FROM 从中检索数据的表 仅在从表选择数据时使用
WHERE 行级过滤 否
GROUP BY 分组说明 仅在按组计算聚集时使用
HAVING 组级过滤 否
ORDER BY 输出排序顺序 否
LIMIT 要检索的行数 否

8.MySQL在进行分组查询时，可以在GROUP BY子句附带WITH ROLLUP关键字来对每个分组进行汇总。如：

1
2
3
4
SELECT vend_id, COUNT(*) AS num_prod, prod_price, SUM(prod_price) AS price_total 
FROM Products 
WHERE prod_price >=4 
GROUP BY vend_id;

输出为：

1
2
3
4
5
6
7
8
+---------+----------+------------+-------------+
| vend_id | num_prod | prod_price | price_total |
+---------+----------+------------+-------------+
| BRS01   |        3 |       5.99 |       26.97 |
| DLL01   |        1 |       4.99 |        4.99 |
| FNG01   |        2 |       9.49 |       18.98 |
+---------+----------+------------+-------------+
3 rows in set (0.00 sec)

为GROUP BY子句加上WITH ROLLUP之后：

1
2
3
4
5
SELECT vend_id, COUNT(*) AS num_prod, prod_price, SUM(prod_price) AS price_total 
FROM Products 
WHERE prod_price >=4 
GROUP BY vend_id 
WITH ROLLUP;

输出结果为：

1
2
3
4
5
6
7
8
9
+---------+----------+------------+-------------+
| vend_id | num_prod | prod_price | price_total |
+---------+----------+------------+-------------+
| BRS01   |        3 |       5.99 |       26.97 |
| DLL01   |        1 |       4.99 |        4.99 |
| FNG01   |        2 |       9.49 |       18.98 |
| NULL    |        6 |       9.49 |       50.94 |
+---------+----------+------------+-------------+
4 rows in set (0.00 sec)

可以看到最后一行多了NULL，后面对应的数值除了prod_price列之外（对该列的统计本身也就没有意义），num_prod和price_total的最后一行都是之前3行的总和。这就是WITH ROLLUP的作用。
但需要注意的是ROLLUP关键字与ORDER BY关键字互斥，也就是使用了ROLLUP之后就不能再使用ORDER BY进行排序。

子查询

1.SELECT语句是SQL的查询（query），任何SQL语句都是查询，但查询通常是指SELECT语句。子查询（subquery）是指嵌套在其他查询中的查询，即把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。或者将子查询作为计算字段。
如这个例子中，列出订购物品RGAN01的所有顾客

1
2
3
4
5
6
7
SELECT cust_name, cust_email  
FROM Customers 
WHERE cust_id IN (SELECT cust_id
                  FROM Orders 
                  WHERE order_num IN(SELECT order_num 
                                     FROM OrderItems 
                                     WHERE prod_id = 'RGAN01'));

输出结果：

1
2
3
4
5
6
7
+---------------+-----------------------+
| cust_name     | cust_email            |
+---------------+-----------------------+
| Fun4All       | dstephens@fun4all.com |
| The Toy Store | NULL                  |
+---------------+-----------------------+
2 rows in set (0.00 sec)

子查询没有嵌套次数限制，每个子查询都会从内到外依次解析，但过深的子查询会影响性能。
2.作为计算字段使用子查询，即使用子查询创建计算字段。如要显示Customers表中每个顾客的订单总数，但订单信息都存储在Orders表中，共有的字段是cust_id。代码为：

1
2
3
4
5
SELECT cust_name, cust_email, (SELECT COUNT(*) 
                               FROM Orders 
                               WHERE Orders.cust_id = Customers.cust_id) AS Orders
FROM Customers
ORDER BY cust_name ;

这里的Orders.cust_id称为完全限定列名，为了避免歧义
结果为

1
2
3
4
5
6
7
8
9
10
+---------------+-----------------------+--------+
| cust_name     | cust_email            | Orders |
+---------------+-----------------------+--------+
| Fun4All       | jjones@fun4all.com    |      1 |
| Fun4All       | dstephens@fun4all.com |      1 |
| Kids Place    | NULL                  |      0 |
| The Toy Store | NULL                  |      1 |
| Village Toys  | sales@villagetoys.com |      2 |
+---------------+-----------------------+--------+
5 rows in set (0.00 sec)

注意在作为计算字段的子查询中，这里的子查询对检索出的每个顾客各执行一次，由于对Customers检索出了5个顾客，所以共执行了5次。这里的子查询是在原查询的SELECT语句中，所以不像前面嵌套查询那样是在WHERE子句中必须先执行，而是在SELECT语句的检索到相应的行之后传递给子查询，然后由子查询进行计算字段的查询

联结表

内联结

1.联结（join）表就是使用SELECT将多个表通过特定的列中的相等值联结在一起进行查询。这也是关系数据库设计的基础，即将信息分解成多个表，一类数据一个表，各表通过某些共同的值互相关联，而不是为存储共有的信息而给每个数据行都单独存一份。
一个表的外键（foreign key）定义它与另一表的关系。外键为某个表的一列，它包含另一个表的主键值，定义了两个表之间的关系。
2.可以在WHERE子句中使用等号（=）创建联结，也可以使用INNER JOIN…ON子句创建联结。如：

1
2
3
4
SELECT vend_name, prod_name, prod_price  
FROM Vendors, Products 
WHERE Vendors.vend_id = Products.vend_id 
ORDER BY prod_price;

等价于

1
2
3
4
SELECT vend_name, prod_name, prod_price  
FROM Vendors INNER JOIN Products 
ON Vendors.vend_id = Products.vend_id
ORDER BY prod_price;

注意这里面没有WHERE子句，这种基于两个表之间的相等测试产生的联结称为等值链接（equition）,也称为内联结（inner join）。
笛卡儿积（cartesian product）：由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行为数目将是第一个表中的行数乘以第二个表中的行数。
3.需要联结多个表时使用AND将需要链接多个表联结在一起即可，如使用子查询列出RGAN01所有顾客的例子可以改写为：

1
2
3
4
5
SELECT cust_name, cust_email
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND Orders.order_num = OrderItems.order_num
AND OrderItems.prod_id = 'RGAN01';

4.当SQL语句中表名太长时，也可以为表定义别名，如上述选择RGAN01产品的顾客信息的SQL语句可改写为

1
2
3
4
5
SELECT cust_name, cust_contact  
FROM Customers AS C, Orders AS O, OrderItems AS OI 
WHERE C.cust_id = O.cust_id 
AND OI.order_num = O.order_num 
AND prod_id = 'RGAN01';

表别名不仅可以用于WHERE子句，也可用于SELECT列表、ORDER BY子句以及其他语句部分
对于Oracle用户可以去掉AS，因为Oracel不支持AS，直接表名后面跟一个别名就可以

自联结

自联结就是当需要多次引用同一个表时，可以为该表创建多个不同的别名，然后引用，可以用于代替子查询实现的效果，而且比子查询速度要快。
例如要选择出Customers表中的与Jim Jones同一公司的所有顾客，使用子查询为

1
2
3
4
5
SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name 
                   FROM Customers
                   WHERE cust_contact = 'Jim Jones');

这段语句意思是先从表Customers中选出contact为Jim Jones的人所在的公司名字，然后再从Customers表中选出该公司名字下的所有用户。
那么可以理解为两次引用了Customers表，相当于内容相同的两个Customers表，选择的依据是从第一个表中选择与第二个名具有相同cust_name的用户，并且要求第二表的cust_contact必须为Jim Jones。使用自联结实现为

1
2
3
4
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

自然联结

自然联结也就是说能够进行相关联的表，通常都会有一列的一一对应相同的，当对这些数组进行联结后统一输出，就会出现重复的列，可以通过限定条件之后，使用通配符一次输出所需要各表的所有列。如要输出购买RGAN01的所有顾客的所有信息，可以这样写：

1
2
3
4
5
SELECT C.*, O.*, OI.* 
FROM Customers AS C, Orders AS O, OrderItems AS OI 
WHERE C.cust_id = O.cust_id  
AND OI.order_num = O.order_num  
AND prod_id = 'RGAN01';

注意这些联结条件中列cust_id至少在两个表中相同，order_num也至少在两个表中相同，所以它们都分别分出现两次，可以通过将第一个使用通配符而后面的都直接使用确定的列避免，如：

1
2
3
4
5
SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_price 
FROM Customers AS C, Orders AS O, OrderItems AS OI 
WHERE C.cust_id = O.cust_id  
AND OI.order_num = O.order_num  
AND prod_id = 'RGAN01';

外联结

1.一般的联结是将一个表中的行与另一个表中的行相关联，会忽略掉那些没有关联行的那些行，而外联结则可以包含所有行。
例如要检索所有顾客的订单，可以这样：

1
2
3
4
SELECT Customers.cust_id, Orders.order_num 
FROM Customers 
INNER JOIN Orders 
ON Customers.cust_id = Orders.cust_id;

这样检索的数据会忽略掉cust_id不相等的行，结果为

1
2
3
4
5
6
7
8
9
10
+------------+-----------+
| cust_id    | order_num |
+------------+-----------+
| 1000000001 |     20005 |
| 1000000001 |     20009 |
| 1000000003 |     20006 |
| 1000000004 |     20007 |
| 1000000005 |     20008 |
+------------+-----------+
5 rows in set (0.00 sec)

如果要包含Customers中cust_id与Orders中没有匹配的行，就需要使用外链接：

1
2
3
4
SELECT Customers.cust_id, Orders.order_num 
FROM Customers 
LEFT OUTER JOIN Orders  
ON Customers.cust_id = Orders.cust_id;

结果为

1
2
3
4
5
6
7
8
9
10
11
+------------+-----------+
| cust_id    | order_num |
+------------+-----------+
| 1000000001 |     20005 |
| 1000000001 |     20009 |
| 1000000002 |      NULL |
| 1000000003 |     20006 |
| 1000000004 |     20007 |
| 1000000005 |     20008 |
+------------+-----------+
6 rows in set (0.00 sec)

对于Customers中的cust_id来说1000000002无法与Orders中匹配，因为Orders中没有1000000002。此时就需要对Customers使用外链接。
2.外联结通过使用OUTER JOIN子句来完成，在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。上面的例子使用LEFT OUTER JOIN从FROM子句左边的表（Customers表）中选择所有行。如果为了从右边的表中选择所有行，则需要使用RIGHT OUTER JOIN。当然很多DBMS也支持FULL OUTER JOIN，即同时包含左边和右边的表。

使用带聚集函数的联结

聚集函数也可以与联结一起使用：
例如要检索所有顾客及每个顾客的下单数：

1
2
3
4
SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ord 
FROM Customers 
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id 
GROUP BY Customers.cust_id;

也可以使用外联结检索出没有下过单的用户：

1
2
3
4
SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ord 
FROM Customers 
LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id 
GROUP BY Customers.cust_id;

联结使用条件

  • 注意所使用的联结类型。一般我们使用内联结，但使用外联结也有效。
  • 关于确切的联结语法，应该查看具体的文档，看相应的DBMS支持何种语法（大多数DBMS使用这两课中描述的某种语法）。
  • 保证使用正确的联结条件（不管采用哪种语法），否则会返回不正确的数据。
  • 应该总是提供联结条件，否则会得出笛卡儿积。
  • 在一个联结中可以包含多个表，甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前分别测试每个联结。这会使故障排除更为简单。

组合查询

1.利用UNION操作符可以将多条SELECT语句组合成一个结果集，并将结果集返回，称为并（union）或复合查询（compound query），默认情况下UNION会将消除重复的行。UNION使用方法即直接将多条SELECT语句使用UNION连接起来即可。
如下这个使用WHERE子句实现的查询：

1
2
3
4
SELECT cust_name, cust_contact, cust_email  
FROM Customers 
WHERE cust_state IN ('IL', 'IN', 'MI') OR cust_name = 'Fun4ALL'
ORDER BY cust_name, cust_contact;

改成UNION查询为：

1
2
3
4
5
6
7
8
SELECT cust_name, cust_contact, cust_email  
FROM Customers 
WHERE cust_state IN ('IL', 'IN', 'MI') 
UNION  
SELECT cust_name, cust_contact, cust_email  
FROM Customers 
WHERE cust_name = 'Fun4ALL' 
ORDER BY cust_name, cust_contact;

注意只能有一个ORDER BY子句

2.UNION不仅可以用于对一个条的多次查询，也可以用于对不同表的查询。UNION使用有以下限制：

  • UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合四条SELECT语句，将要使用三个UNION关键字）。
  • UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。
  • 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。

全文本搜索

1.全文本搜索使得MySQL可以进行高级的数据查询和选择，可以快速地检索每一个词，比LIKE功能强大而且速度更快。然而并不是所有的数据库引擎都支持全文本搜索，MyISAM较早地支持了全文本搜索，而InnoDB引擎在MySQL 5.6之后才支持。而且全文本搜索无法支持像中文这种需要单独分词的语言。

2.为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在索引之后，SELECT可与Match()和Against()一起使用来进行实际的搜索。并且必须要对需要进行全文本搜索的列启动全文本搜索才能在后续对其建立索引。

3.一般在创建表时使用FULLTEXT子句对需要被索引的列增加全文索引功能，当需要索引多个列时，则使用逗号分隔。下面是一个创建表时增加全文本搜索的例子：

1
2
3
4
5
6
7
8
9
CREATE TABLE productnotes
(
    note_id    int           NOT NULL AUTO_INCREMENT,
    prod_id    char(10)      NOT NULL,
    note_date datetime       NOT NULL,
    note_text  text          NULL ,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)     #对note_text列启用全文本搜索
) ENGINE=MyISAM;

在将某列定义为全文本搜索之后，MySQL将自动维护该索引。在增加、更新或删除行时，索引随之自动更新。可以在创建表时指定FULLTEXT，或在稍后指定（在这种情况下所有已有数据必须立即索引）。不要在导入数据时使用FULLTEXT，应该在导入之后再修改表，定义FULLTEXT，这样有助于更快导入数据，而且总是索引时间也会比每行导入都索引的总时间小。

4.使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。
如：

1
2
3
SELECT note_text 
FROM productnotes 
WHERE MATCH(note_text) Against('rabbit');

这个语句会对列note_text进行全文搜索，搜索内容是含有词rabbit的行，并返回搜索到的行。默认情况下全文本搜索不区分大小写
上面的语句与以下LIKE语句完成的功能相同

1
2
3
SELECT note_text 
FROM productnotes 
WHERE note_text LIKE '%rabbit%';

5.全文本搜索会对返回结果按等级排序，各行的等级确定方式是根据匹配的程序来定，等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。可以通过下SELECT语句来执行MATCH和AGAINST函数来查看各行等级，数值越大，等级越高：

1
2
SELECT note_text, Match(note_text) Against('rabbit') AS rank 
FROM productnotes;

6.可以通过在AGAINST()函数中添加WITH QUERY EXPANSION关键字来扩展查询结果。使用查询扩展时，MySQL会通过对数据和索引进行两遍扫描来完成搜索，第一遍扫描进行基本的全文本搜索，第二遍扫描根据匹配行中所有有用的词进行再次全文本搜索，最后返回所有结果。
如：

1
2
3
SELECT note_text
FROM productnotes 
WHERE MATCH(note_text) Against('anvils' WITH QUERY EXPANSION);

如果不使用扩展查询，只能返回一行数据，因为只有一行中包含关键字anvils，而使用扩展搜索之后会返回多行。

7.MySQL也可以通过布尔方式（boolean mode）支持全文本搜索。布尔搜索不需要FULLTEXT索引也可以使用，这是一种非常缓慢的操作。布尔搜索支持以下操作：

  • 要匹配的词;
  • 要排斥的词(如果某行包含这个词,则不返回该行,即使它包含其他指定的词也是如此);
  • 排列提示(指定某些词比其他词更重要,更重要的词等级更高) ;
  • 表达式分组;
  • 另外一些内容

一些布尔搜索的例子：
简单的布尔搜索，搜索note_text中含有heavy词的行：

1
2
3
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);

匹配含有heavy但不包含任意以rope开始的词的行：

1
2
3
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

注意上面这个例子中，需要搜索的搜索串使用-进行排除和*进行匹配结尾。
全文本布尔操作符有以下这些：

1
2
3
4
5
6
7
8
+   包含,词必须存在
+- 排除,词必须不出现
>  包含,而且增加等级值
><  包含,且减少等级值
()  把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等)
~  取消一个词的排序值
*  词尾的通配符
*""  定义一个短语(与单个词的列表不一样,它匹配整个短语以便包含或排除这个短语)

一些布尔搜索串举例：
Against('+rabbit +bait' IN BOOLEAN MODE) 搜索匹配含词rabbit和bait的行
Against('rabbit bait' IN BOOLEAN MODE) 搜索匹配含rabbit和bait中至少一个词的行
Against('"rabbit bait"' IN BOOLEAN MODE) 搜索匹配短语rabbit bait而不是两个词
Against('+rabbit +(<bait)' IN BOOLEAN MODE) 搜索匹配词rabbit和bait，降低后者的等级

8.全文搜索的一些说明：

  • 在索引全文本数据时,短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词(如果需要,这个数目可以更改) 。
  • MySQL带有一个内建的非用词(stopword)列表,这些词在索引全文本数据时总是被忽略。如果需要,可以覆盖这个列表(请参阅MySQL文档以了解如何完成此工作) 。
  • 许多词出现的频率很高,搜索它们没有用处(返回太多的结果)。因此,MySQL规定了一条50%规则,如果一个词出现在50%以上 的行中,则将它作为一个非用词忽略。 50%规则不用于 IN BOOLEAN MODE 。
  • 如果表中的行数少于3行,则全文本搜索不返回结果(因为每个词或者不出现,或者至少出现在50%的行中)。
  • 忽略词中的单引号。例如, don’t 索引为 dont 。
  • 不具有词分隔符(包括日语和汉语)的语言不能恰当地返回全文本搜索结果。
  • 并不是所有数据库引擎都支持全文搜索

插入数据

1.SQL使用INSERT（或INSERT INTO）来向表中插入数据，在不与SELECT配合的情况下一次只能插入一行数据，可以插入完整行（即所有列都有的行），也可以插入部分行（即省略有些列的插入）插入完整行：

1
2
3
4
5
6
7
8
9
10
INSERT INTO Customers 
VALUES('10000007', 
'Toy land', 
'123 Any Street', 
'New York', 
'NY', 
'1111', 
'USA', 
NULL, 
NULL);

注意，像上面这样不指定列名插入数据时，必须每一列数据都与原表中的列一一对应，不能少，也不能错列，其中INTO关键字在很多DBMS中可以省略。
或者像下面这样指定要插入的列，只插入一部分数据，有些数据使用表设置的默认值：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
INSERT INTO Customers(cust_name,
cust_id,
cust_city,
cust_address,
cust_state,
cust_zip,
cust_country)
VALUES('Toy land', 
'10000009', 
'New York', 
'123 Any Street', 
'NY', 
'1111', 
'USA');

注意此时有些列并没有给出，因为表中设置了默认值，所以会自动插入默认值。而且列值的顺序也与原表中的列在顺序上不是一一对应，而是通过指定列名进行插入，但指定的列名顺序必须与下面要插入的值一一对应。
2.省略的列必须满足以下条件：

  • 该列定义为允许NULL值（无值或空值）。
  • 在表定义中给出默认值。这表示如果不给出值，将使用默认值。

对于有些列被设置为自动增量，在插入时如果没有指定限定列，而必须在值相应的位置使用NULL，这样MySQL会在插入时自动填入合适的增量值。对于指定限定列的情况下，可以忽略，增量值会被自动填入。

2.这样的INSERT语句一次只能插入一行，要插入多行必须使用多个INSERT语句，多条INSERT语句使用分号分隔后一次提交即可。但可以有效率更高的方式，如下所示一次插入多行，只需要列名和次序相同，各组值之间使用逗号分隔即可：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
INSERT INTO Customers(cust_id,
                      cust_contact,
                      cust_email,
                      cust_name)
               VALUES('10000009', 
                      'Toy land',
                      'sfasa@qq.com', 
                      'blueyi'
                     ),
                     ('100000011', 
                      'Land',
                      'ddaa@qq.com', 
                      'maxwi'
                     );

下面这种与SELECT配合使用的时候可以批量插入数据
3.使用INSERT…SELECT从一个表检索数据并插入另一个，当然也可以是同一个表，但主键不能重复，这种方式一次可以插入多行。如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
INSERT INTO Customers(cust_id,
                      cust_contact,
                      cust_email,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country)
SELECT cust_id,
       cust_contact,
       cust_email,
       cust_name,
       cust_address,
       cust_city,
       cust_state,
       cust_zip,
       cust_country
FROM CustNew;

使用这种方式插入时，并不要求前后列名都一致，只要数据可以匹配即可。实际上DBMS并不是根据SELECT列名进行获取数据插入，而是使用列的位置，但INSERT中的列名必须是已经存入于Customers，当然也不需要顺序与原表中的列完全一致。

4.从一个表复制数据到另一表的语句是：

1
2
3
CREATE TABLE CustCopy AS
SELECT *
FROM Customers;

创建名为CustCopy的表，并从Customers中复制所有数据到该表。
有些DBMS的该语句是SELECT * INTO CustCopy FROM Customers;

5.SELECT INTO支持以下操作：

  • 任何SELECT选项和子句都可以使用，包括WHERE和GROUP BY；
  • 可利用联结从多个表插入数据；
  • 不管从多少个表中检索数据，数据都只能插入到一个表中

6.为了提供性能，如果对数据检索是最重要的，可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY指示MySQL降低INSERT语句的优先级。如下所示：

1
INSERT LOW_PRIORITY INTO

这种方式也同时适用于UPDATE和DELETE语句。

更新和删除数据

更新数据

1.使用UPDATE…SET…语句更新数据，UPDATE语句即可用于更新表中的特定行，也可以更新表中的所有行。
指定WHERE子句即可限定只更新特定行，当省略WHERE子句时，即直接更新所有行，所以要慎重省略WHERE。
UPDATE语句由三部分组成：

  • 要更新的表；
  • 列名和它们的新值；
  • 确定要更新哪些行的过滤条件

需要更新某一行的多个列时，只需要将不同的“列=值”对之间用逗号分隔（最后一列之后不用逗号）。
如设置某个cust_id的联系方式和邮箱：

1
2
3
4
UPDATE Customers
SET cust_contact = 'Blueyi',
cust_email = 'hello@gmail.com'
WHERE cust_id = '10000000006';

当省略上述的WHERE之句时，所有cust_contact列和cust_email列都将被更新。

2.UPDATE语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据。也可以使用FROM子句，用一个表的数据更新另一表的数据，详见各DBMS说明。

3.要删除某列的值，可以设置它为NULL（假定表定义允许NULL值）

4.如果用UPDATE语句更新多行时，当其中一行或多行出现一个错误，整个UPDATE操作都会被取消，为使发生错误后继续进行更新，可使用IGNORE关键字：

1
UPDATE IGNORE Customers...

删除数据

1.使用DELETE FROM语句删除表（FROM关键字在有些DBMS中可以省略），即可使用DELETE删除特定的行，也可以删除所有行。
同样是使用WHERE子句来限定删除特定的行，否则会删除所有行。
如删除某个ID的行：

1
2
DELETE FROM Customers
WHERE cust_id = '1000000006';

2.当使用DELETE语句从表中删除所有行时，DELETE不删除表本身，删除的只是行。如果要删除所有行，可以使用TRUNCATE TABLE CustCopy;，而不是使用DELETE FROM CustCopy; ，因为前者速度更快（因为它不记录数据的变动）。

3.DBMS通常会使用外键来严格定义表相互之间的关系，当存在外键时，DBMS为了使用它们实施引用的完整性，通常不允许删除某个关系需要用到的行。

4.下面是许多SQL程序员使用UPDATE或DELETE时所遵循的重要原则。

  • 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
  • 保证每个表都有主键，尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。
  • 在UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
  • 使用强制实施引用完整性的数据库，这样DBMS将不允许删除其数据与其他表相关联的行。
  • 有的DBMS允许数据库管理员施加约束，防止执行不带WHERE子句的UPDATE或DELETE语句。如果所采用的DBMS支持这个特性，应该使用它。

复制表的两种方式

1.当已经存在表时，使用：

1
2
3
INSERT INTO CustCopy    #CustCopy表已经存储，里面没有数据，当然也可以有数据，但主键不能重复
SELECT *
FROM Customers;

2.当表不存在时，使用：

1
2
3
CREATE TABLE CustCopy3 AS   #AS可以被省略
SELECT * 
FROM Customers;

清空表的两种方式

  • DELETE FROM CustCopy;
  • TRUNCATE TABLE CustCopy;

清空数据库

删除数据库中的所有表，而不删除数据库本身，注意，不是删除数据库中表的数据，而是对数据库中的所有表执行DROP TABLE <表名>操作：
首先使用以下语句获取到数据库中的所有表的删除语句，然后批量执行该删除语句即可：

1
SELECT CONCAT('drop table ', table_name, ';') FROM information_schema.`TABLES` WHERE table_schema='数据库名';

创建和操纵表

创建表

1.使用CREATE TABLE创建表，创建表时必须给出下列信息：

  • 新表的名字，在关键字CREATE TABLE之后给出；
  • 表列的名字和定义，用逗号分隔；
  • 有的DBMS还要求指定表的位置。

如：

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE customers
(
    cust_id      int       NOT NULL AUTO_INCREMENT,
    cust_name    char(50)  NOT NULL ,
    cust_address char(50)  NULL ,
    cust_city    char(50)  NULL ,
    cust_state   char(5)   NULL ,
    cust_zip     char(10)  NULL ,
    cust_country char(50)  NULL ,
    cust_contact char(50)  NULL ,
    cust_email   char(255) NULL ,
    PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

从上面的例子可以看到，表名紧跟CREATE TABLE关键字。实际的表定义（所有列）括在圆括号之中，各列之间用逗号分隔。这个表由9列组成。每列的定义以列名（它在表中必须是唯一的）开始，后跟列的数据类型。整条语句以圆括号后的分号结束。表的主键可以在创建表时使用PRIMARY KEY关键字指定。这里，列cust_id指定作为主键列。
可以在表名后面添加关键字IF NOT EXISTS来指定只在一个表不存在时创建它。

2.查看表都有哪些列：

1
DESCRIBE Products;

如：

1
2
3
4
5
6
7
8
9
10
11
mysql> DESCRIBE Products;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| prod_id    | char(10)     | NO   |     | NULL    |       |
| vend_id    | char(10)     | NO   |     | NULL    |       |
| prod_name  | char(254)    | NO   |     | NULL    |       |
| prod_price | decimal(8,2) | NO   |     | NULL    |       |
| prod_desc  | text         | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

3.关于NULL值，就是是否允许该列在插入时不给出该列的值，不允许NULL值的列不接受没有列值的行。当不指名是否为NULL时，默认为允许NULL值。主键是唯一标识表中每一行的列，所以允许NULL值的列不能作为主键列。

3.主键值必须唯一，主键可以使用单个列，也可以使用多列，如果使用多个列，则这些列的组合值必须唯一（也就是说单个列可以不唯一）。将多个列作为主键时，各列之间使用逗号分隔：PRIMARY KEY(order_num, order_item)

4.AUTO_INCREMENT即是用来设置插入某些列时的自动增量值，每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）。被设置为AUTO_INCREMENT列也可以在插入时手动指定个值，只要该值唯一即可，后续的增量将开始使用该手工插入的值为基础值。可以使用函数last_insert_id()耿获取最后一个AUTO_INCREMENT值，如SELECT last_insert_id();

4.可以在创建表时使用DEFAULT关键字指定默认值。如：

1
2
3
4
5
6
7
8
9
CREATE TABLE OrderItems 
(
order_num      INTEGER          NOT NULL,
order_item     INTEGER          NOT NULL,
prod_id        CHAR(10)         NOT NULL,
quantity       INTEGER          NOT NULL      DEFAULT 1,
item_price     DECIMAL(8,2)     ,    #默认为允许NULL
item_desc    text(1000)  #默认为允许NULL
);

上面的创建结果为：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> DESCRIBE OrderItems;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| order_num  | int(11)      | NO   |     | NULL    |       |
| order_item | int(11)      | NO   |     | NULL    |       |
| prod_id    | char(10)     | NO   |     | NULL    |       |
| quantity   | int(11)      | NO   |     | 1       |       |
| item_price | decimal(8,2) | YES  |     | NULL    |       |
| item_desc  | text         | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

MySQL允许使用DEFAULT CURRENT_DATE()将当前时间设定为默认值

5.为已存储在列添加默认值：

1
2
alter table表名alter column字段名drop default; (若本身存在默认值，则先删除)
alter table表名 alter column 字段名 set default默认值;(若本身不存在则可以直接设定)

如：

1
ALTER TABLE user ALTER COLUMN theme SET DEFAULT 'material';

更改或删除表

5.使用ALTER关键字更改表结构，必须给出如下信息：

  • 在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）；
  • 列出要做哪些更改。
    如：
    1
    2
    3
    4
    5
    ALTER TABLE OrderItems
    ADD isbn CHAR(20);  #增加列

    ALTER TABLE OrderItems
    DROP COLUMN isbn;   #删除列
    ALTER TABLE一种常见的用途是定义外键：
    1
    2
    3
    ALTER TABLE orderitems
    ADD CONSTRAINT fk_orderitems_orders
    FOREIGN KEY (order_num) REFERENCES orders (order_num);

6.删除表使用

1
DROP TABLE CustCopy;

7.重命名表：

1
RENAME TABLE OrderItems TO NewOrder;

也可以一次重命名多个表

1
2
3
RENAME TABLE OrderItems1 TO NewOrder1,
             OrderItems2 TO NewOrder2,
             OrderItems2 TO NewOrder2;

8.重命名列：

1
2
ALTER TABLE OrderItems
CHANGE isbn Isbn CHAR(20)

9.改变列的类型及是否为NULL：

1
2
3
4
5
ALTER TABLE OrderItems
CHANGE isbn Isbn INTEGER not NULL; #更改为不允许为null的integer

ALTER TABLE OrderItems
CHANGE isbn Isbn FLOAT NULL; 更改isbn列名字为Isbn，且值为可NULL的float

8.复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

  • 用新的列布局创建一个新表；
  • 使用INSERT SELECT语句（关于这条语句的详细介绍，请参阅第15课）从旧表复制数据到新表。有必要的话，可以使用转换函数和计算字段；
  • 检验包含所需数据的新表；
  • 重命名旧表（如果确定，可以删除它）；
  • 用旧表原来的名字重命名新表；
  • 根据需要，重新创建触发器、存储过程、索引和外键。

9.创建表时可以在最后面使用ENGINE=来指定需要的引擎类型，如果不指定，MySQL会使用默认引擎。与其他DBMS一样，MySQL有一个具体管理和处理数据的内部引擎。在使用CREATE TABLE语句时，该引擎具体创建表，同样，在使用SELECT等语句操作数据库时，都是该引擎在内部处理请求。MySQL具有多个引擎，不同的引擎具有不同的功能和特性。主要有以下几个：

  • InnoDB是一个可靠的事务处理引擎
  • MEMORY在功能上等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）
  • MyISAM是一个性能极高的引擎，但不支持事务处理

引擎类型可以混合使用，但外键不能跨引擎。

视图

1.视图是虚拟的表，它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种封装SELECT语句的层次，可以用来简化数据处理，重新格式化或保护基础数据。视图创建完之后，使用起来就像使用表一样，但实际上它每次都要重新进行查询，所以大量的视图可能会影响性能。视图可以理解为一种动态的表，相当于临时用，临时创建，可以大大减化复杂而且需要重复使用的查询过程。

2.使用CREATE VIEW创建视图，类似于CREATE TABLE一样，只能用于创建不存在的视图，当然也可以使用DROP VIEW viewname;删除视图。
以上文中选择所有购买了RGAN01产品的顾客信息为例，假如经常需要检索出购买某种产品的所有顾客信息，而这些信息又在多个表中，每次检索都需要输入相同的SELECT和WHERE限定条件，那么可以创建一个这些公共部分的视图，相当于创建一个包含我们所需要的所有顾客的信息列组成的虚拟表。以后在需要检索哪个产品时，就从这个虚拟表检索即可。过程如下：
创建视图：

1
2
3
4
5
CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;

查看现在都有哪个表：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SHOW TABLES;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| Customers            |
| OrderItems           |
| Orders               |
| ProductCustomers     |
| Products             |
| Vendors              |
+----------------------+
10 rows in set (0.00 sec)

可以看到多了一个刚刚创建的视图虚拟表，
查看已创建的视图ProductCustomers的信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> SELECT * FROM ProductCustomers;
+---------------+--------------------+---------+
| cust_name     | cust_contact       | prod_id |
+---------------+--------------------+---------+
| Village Toys  | John Smith         | BR01    |
| Village Toys  | John Smith         | BR03    |
| Village Toys  | John Smith         | BNBG01  |
| Village Toys  | John Smith         | BNBG02  |
| Village Toys  | John Smith         | BNBG03  |
| Fun4All       | Jim Jones          | BR01    |
| Fun4All       | Jim Jones          | BR02    |
| Fun4All       | Jim Jones          | BR03    |
| Fun4All       | Denise L. Stephens | BR03    |
| Fun4All       | Denise L. Stephens | BNBG01  |
| Fun4All       | Denise L. Stephens | BNBG02  |
| Fun4All       | Denise L. Stephens | BNBG03  |
| Fun4All       | Denise L. Stephens | RGAN01  |
| The Toy Store | Kim Howard         | RGAN01  |
| The Toy Store | Kim Howard         | BR03    |
| The Toy Store | Kim Howard         | BNBG01  |
| The Toy Store | Kim Howard         | BNBG02  |
| The Toy Store | Kim Howard         | BNBG03  |
+---------------+--------------------+---------+
18 rows in set (0.00 sec)

现在在视图上检索购买了RGAN01的所有顾客：

1
2
3
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

当然，此时需要检索其他产品，也直接在这个视图上即可，就不需要每次都去输入那些联结表的信息了。

3.视图的操作：

  • 视图用 CREATE VIEW 语句来创建。
  • 使用 SHOW CREATE VIEW viewname;来查看创建视图的语句。
  • 用 DROP 删除视图,其语法为 DROP VIEW viewname;。
  • 更新视图时,可以先用DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在,则第 2 条更新语句会创建一个视图;如果要更新的视图存在,则第 2 条更新语句会替换原有视图。

3.利用视图可以为任意检索出的新数据创建一个虚拟表，以便后续使用。
3.1例如之前检索过需要的格式化数据，使用视图可以这样：

1
2
3
CREATE VIEW VendorLocations AS 
SELECT CONCAT(RTRIM(vend_name), ' (', RTRIM(vend_country), ')')  AS vend_title 
FROM Vendors;

以后再使用时，直接

1
2
SELECT *
FROM VendorLocations;

即可
3.2使用视图过滤数据：

1
2
3
4
CREATE VIEW CustomerEMailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;

3.3创建含有计算字段的视图：

1
2
3
4
5
6
7
CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems;

视图通常是可更新的，如果你对视图增加或删除行,实际上是对其基表增加或删除行。

存储过程

1.存储过程就是为以后的使用而保存的一条或多条MySQL语句的集合，可将其视为针对MySQL的批处理，其实类似于编程语言中的函数。使用存储过程的好处：简单、安全、高性能。

创建存储过程

存储过程的使用方法为先使用CREATE PROCEDURE创建存储过程，然后使用CALL来调用创建的存储过程名。需要注意的是默认的MySQL语句分隔符为;（也就是当编辑多条MySQL语句时，需要使用分号分隔），但mysql命令行实用程序（也就是我们这里使用的mysql命令行程序）也使用;作为命令结束的分隔符（也就是mysql命令行程序在遇到分号时就开始对之前的语句进行解析）。这样就会导致在编写存储过程时，存储过程内的MySQL语句分隔符，会被mysql程序当成一句已经结束了，而进行解析。为解决这个问题，可以使用DELIMITER关键字临时更改命令行实用程序的语句分隔符。如下所示：

1
2
3
4
5
6
7
DELIMITER //  #更改mysql程序的语句分隔符为//
CREATE PROCEDURE productpricing()   #创建名为productpricing的过程
BEGIN  
  SELECT Avg(prod_price) AS price_average 
  FROM Products; 
END //
DELIMITER ;   #再改回来，以方便后续继续使用;作为mysql的命令行程序的语句分隔符

除了\符号外，任何字符都可以作为语句分隔符。
上面的语句创建了一个名为productpricing的存储过程，注意存储过程名后面需要有()，其实就相当于函数，可以有参数放在()之间，当然这里没有参数，然后存储过程体放在BEGIN和END之间。
调用存储过程：

1
2
3
4
5
6
7
8
9
mysql> CALL productpricing();
+---------------+
| price_average |
+---------------+
|      6.823333 |
+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

与函数不同的是需要使用CALL来调用存储过程，而不能使用SELECT。
由于这个存储过程中有SELECT语句，所以会直接显示出结果。

查看存储过程

1.显示某个存储过程的详细信息，包括存储过程语句：

1
SHOW CREATE PROCEDURE productpricing;

2.显示系统中的所有存储过程：

1
SHOW PROCEDURE STATUS;

3.可以使用LIKE指定过滤模式，如：

1
SHOW PROCEDURE STATUS LIKE 'ordertotal';

删除存储过程

使用DROP PROCEDURE语句删除存储过程，存储过程一旦创建之后，在删除之前一直存储于系统之中。存储过程创建之后不能再创建与之同名的存储过程，只能先将其删除，再重新创建。如：

1
2
DROP PROCEDURE productpricing;  #如果不存在，会报错
DROP PROCEDURE IF EXISTS productpricing;   #只在存在时删除，不存在也不产生错误

删除名为productpricing的存储过程，注意这里存储过程的后面不需要有()

使用参数

1.一般存储过程不显示结果，而是将结果返回给指定的变量（variable），用来临时存储数据。如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DELIMITER //  #更改mysql程序的语句分隔符为//
CREATE PROCEDURE productpricing(
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
)
BEGIN
    SELECT Min(prod_price)
    INTO pl
    FROM Products;

    SELECT MAX(prod_price)
    INTO ph
    FROM Products;

    SELECT Avg(prod_price)
    INTO pa
    FROM Products;
END //
DELIMITER ;

此存储过程接受3个参数：pl存储最低价，ph存储最高价，pa存储平均价，这三个参数相当于形参。每个参数都必须具有指定的类型，这里使用的是十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL支持IN（传递给存储过程）、OUT（从存储过程付出）和INOUT（对存储过程传入和付出）三种类型的参数。通过INTO关键字将检索到的相应数据存储到指定的变量而不是显示。
2.调用些存储过程必须传递给他们3个参数，不多也不能少，当然传递进去的参数并不需要提前声明。所有MySQL变量都必须以@开始，当然在存储过程中使用的不需要。如：

1
2
3
CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage);

3.显示这些参数值：

1
2
3
4
5
6
7
mysql> SELECT @pricehigh, @pricelow, @priceaverage;
+------------+-----------+---------------+
| @pricehigh | @pricelow | @priceaverage |
+------------+-----------+---------------+
|      11.99 |      3.49 |          6.82 |
+------------+-----------+---------------+
1 row in set (0.00 sec)

4.以下这个例子创建一个存储过程，当调用该存储过程时自动显示相应订单号的总价格：

1
2
3
4
5
6
7
8
9
10
11
12
DELIMITER //  #更改mysql程序的语句分隔符为//
CREATE PROCEDURE ordertotal(
  IN onumber INT,   #用于传递需要查看订单价格的订单号
  OUT ototal DECIMAL(8,2)   #存储总价格
)
BEGIN
  SELECT Sum(item_price * quantity)
  FROM OrderItems
  WHERE order_num = onumber;
  INTO ototal   #注意这个例子里面INTO语句放在了FROM之句的后面，当然也可以放在FROM之句之前
END //
DELIMITER ;

调用：

1
2
3
4
5
6
7
8
CALL ordertotal(20009, @total);
mysql> SELECT @total;
+---------+
| @total  |
+---------+
| 1867.50 |
+---------+
1 row in set (0.00 sec)

建立存储过程：

存储过程可以很复杂，可以包含像函数一样的内总计算，并在编写存储过程是使用注释（前面放置– 或#），使用DECLARE语句定义局部变量，使用IF..THEN..ELSEIF..ELSE等语句进行流程控制，也可以COMMENT关键字添加备注信息，以便使用SHOW PROCEDURE STATUS来查看。
下面是一个例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
DELIMITER //
-- Name: ordertotal
-- Parameters:  onumber = order number
--              taxable = 0 if not taxable, 1 if taxable
--              ototal = order total variable
CREATE PROCEDURE ordertotal(
  IN onumber INT,
  IN taxable BOOLEAN,
  OUT ototal DECIMAL(8,2)  
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
  -- Declare variable for total
  DECLARE total DECIMAL(8, 2);   #声明时需要指定类型
  -- Declare tax percentage
  DECLARE taxrate INT DEFAULT 6;   #声明时指定默认值
  -- Get the order total
  SELECT Sum(item_price * quantity)
  FROM OrderItems 
  WHERE order_num = onumber
  INTO total;
  -- Is this taxable?
  IF taxable THEN 
    -- Yes, so add taxrate to the total
    SELECT total + (total / 100 * taxrate) INTO TOTAL;
  END IF;
  -- And finally, save to out variable
  SELECT total INTO ototal;
END //
DELIMITER ;

对于BOOLEAN类型来说，非0为真，0为假
调用：

1
2
3
4
5
6
7
8
9
10
mysql> CALL ordertotal(20009, 0, @total);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @total;
+---------+
| @total  |
+---------+
| 1867.50 |
+---------+
1 row in set (0.00 sec)

使用游标

1.游标就是使得MySQL可以通过存储过程或函数对SELECT语句获取到的数据进行一行一行处理的方式，也就是说游标也只能通过存储过程或函数来使用，相当于存储过程或函数中的一个局部变量。游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

2.使用游标需要以下几个步骤：

  • 在能够使用游标前,必须声明(定义)它。这个过程实际上没有检索数据,它只是定义要使用的 SELECT 语句。
  • 一旦声明后,必须打开游标以供使用。这个过程用前面定义的SELECT 语句把数据实际检索出来。
  • 对于填有数据的游标,根据需要取出(检索)各行。
  • 在结束游标使用时,必须关闭游标。

游标声明后，可以根据需要频繁地打开和关闭它，游标打开后可以根据需要频繁地执行取操作。

创建游标

1.使用DECLARE语句创建游标，并定义相应的SELECT语句。如：

1
2
3
4
5
6
7
8
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
  DECLARE ordernumbers CURSOR   #创建一个名ordernumbers的游标
  FOR 
  SELECT order_num FROM Orders;
END//
DELIMITER ;

这个例子中游标什么也没干，定义之后，在存储过程执行完成时游标自动消失。

2.使用OPEN cursor_name打开游标，CLOSE cursor_name关闭游标以释放游标使用的所有内存内存和资源，如果没有手动关闭游标，MySQL将会在到达END语句时自动关闭它（毕竟游标只是个局部变量）。

使用游标

使用FETCH语句获取游标中的数据，并将游标指向下一个位置
下面这个例子利用游标来创建一个新表，该表用于存储各订单号对应的总额。各句注释

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN 
  -- Declare local variables
  DECLARE done BOOLEAN DEFAULT 0;  #声明BLOOEAN型变量done，默认值为0，用于控制循环
  DECLARE o INT;
  DECLARE t DECIMAL(8,2);

  -- Declare the cursor
  DECLARE ordernumbers CURSOR   #声明游标，游标中存放的是order_num
  FOR
  SELECT order_num FROM Orders;

  -- Declare continue handler
  #这句的意思是定义一个CONTINUE HANDLER，它是条件出现时被执行的代码，这里使用SQLSTATE指定当出现错误信息`02000`时将done设置为1
  #02000不服水土一个未找到的条件，即当REPEATE由于没有更多行供循环时，出现这个条件
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;   

  -- Create a table to store the results
  CREATE TABLE IF NOT EXISTS ordertotals(
      order_num INT,
      total DECIMAL(8,2)
  );

  -- Open the cursor
  OPEN ordernumbers;

  -- Loop through all rows
  REPEAT   #循环以下内容
    -- Get order number
    FETCH ordernumbers INTO o;  #获取游标中的order_num到变量o中

    -- Get the total for this order
    CALL ordertotal(o, 1, t);   #调用之前创建个存储过程，来计算总价格，并存入变量t中

    -- Insert order and total into ordertotals
    INSERT INTO ordertotals(order_num, total)
                      VALUES(o, t);
  -- END of loop
  UNTIL done END REPEAT;   #当done为真时，结束循环

  -- Close the cursor
  CLOSE ordernumbers;

END//
DELIMITER ;

注意，这里使用了循环、错误处理等高级语句，关于更多MySQL的错误码，可以参见这里http://dev.mysql.com/doc/mysql/en/error-handling.html
也可以通过使用LEAVE语句手动退出循环
FETCH可以获取当前游标所指向的内容，并更新游标指向下一个位置

通过调用刚创建的存储过程，来使用游标：

1
CALL processorders();

调用刚才的存储过程后，将创建并填充一个表，下面查看该表：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT * FROM ordertotals;
+-----------+---------+
| order_num | total   |
+-----------+---------+
|     20005 | 1746.88 |
|     20009 | 1979.55 |
|     20006 |  349.38 |
|     20007 | 1797.76 |
|     20008 |  200.98 |
|     20008 |  200.98 |
+-----------+---------+
6 rows in set (0.00 sec)

触发器

1.触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：DELETE，INSERT，UPDATE。
2.使用CREATE TRIGGER来创建触发器
由于MySQL现在的触发器规则改变，导致书上的例子或讲解可能会出现问题，所以暂时路过这部分，以后有需要了再补上

事务处理

1.事务处理（transaction processing）可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。相当于让对数据库的操作可以回退。
2.一般的MySQL语句都隐含两步操作，即处理数据和提交处理，直有提交之后操作才真得彻底写入数据库。例如删除某一行的语句DELETE操作之后，系统会默认进行隐含提交来真正删除那行。也就是说，如果MySQL不进行隐式提交的话，是可以被回退的。管理事务处理也就是将SQL语句分解为多个逻辑块，然后在需要的时候进行回退。MySQL使用START TRANSACTION开始一个事务处理

ROLLBACK进行回退

1.MySQL使用ROLLBACK命令来回退（撤销）MySQL语句。只有在开始一个事务处理，或者将关闭自动提交之后才能进行回退：

1
2
3
START TRANSACTION;  #开始一个事务处理
DELETE FROM ordertotals;  #删除所有行
ROLLBACK;  #回退到START TRANSACTION之前的状态

也就是说进入START TRANSACTION之后的语句时，MySQL语句的自动提交将被临时关闭，直到执行ROLLBACK或COMMIT语句。

2.事务处理可以用来管理INSERT、UPDATE和DELETE语句。不能回退CREATE或DROP操作，事务块中可以使用这两条语句，但执行回退并不会影响这两条语句的操作。

COMMIT提交操作

1.默认情况下MySQL语句都会自动隐含提交，当关闭自动提交或开始事务处理之后，提交不会隐含地进行，只有手动使用COMMIT语句才能进行明确提交。如：

1
2
3
4
START TRANSACTION;
DELETE FROM OrderItems WHERE order_num = 20009;
DELETE FROM Orders WHERE order_num = 20009;
COMMIT;

COMMIT之后便无法再使用ROLLBACK回退，当然如果以上两条语句有一条失败，COMMIT也不会提交，也就是说DELETE会被自动撤销。
2.通过设置autocommit的值可以更改默认的提交行为。

1
SET autocommit = 0; #设置为不自动提交

autocommit标志决定是否自动提交更改。将autocommit设置为0之后，MySQL将不再进行自动提交，直到该值再次设置为1为止。当然设置为不自动提交之后可以使用COMMIT进行手动提交，也可以使用ROLLBACK回退到最后一次提交之前的状态，注意设置为自动提交之前都会有默认自动提交。
autocommit标志是针对每个连接而不是服务器。也就是说当退出后再次登录MySQL，之前设置的autocommit状态就会被重置为默认状态。

SAVEPOINT设置保留点

1.简单的ROLLBACK和COMMIT语句可以写入或撤销整个事务，而结合SAVEPOINT设置的保留点之后，就像建快照以样，可以回退到任何一个保留点状态。
使用方法就是首先创建占位符（place-holder），然后使用ROLLBACK TO回退到相应的保留点：

1
2
SAVEPOINT delete1;
ROLLBACK TO delete1;

使用SAVEPOINT设置保留点之后，不管是否在事务处理中，都会默认进行不自动提交的状态。如果使用ROLLBACK而不是ROLLBACK TO，会默认回退到最后一个保留点处，回退到保留点之后，会自动删除该保留点。

2.保留点越多越好，不限制个数，保留点在事务处理完成之后（执行一条ROLLBACK或COMMIT）后自动释放。也可以用RELEASE SAVEPOINT idertifer明确地释放保留点。

字符集

1.MySQL支持众多的字符集，使用以下语句可以查看字符集的相关内容：

1
2
SHOW CHARACTER SET;  #显示所有可用的字符集以及每个字符集的描述和默认校对
SHOW COLLATION;  #显示所有可用的校对，以及它们适用的字符集

校对为规定字符如何比较的指令。

2.通常系统管理在安装时会定义一个默认的字符集和校对，也可以在创建数据库时，指定默认的字符集和校对。可以使用以下语句查看所使用的字符集和校对：

1
2
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

3.字符集很少是服务器范围的，甚至不同列都可能需要不同的字符集，可以在创建表时指定字符集，也可以为列指定字符集：

1
2
3
4
5
6
CREATE TABLE mytable
(
  column1 INT,
  column2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;

此语句创建一个包含两列的表，并指定一个字符集和一个校对顺序。

4.使用CHARACTER SET和COLLATE来指定字符集和校对，它们的使用规则如下：

  • 如果指定 CHARACTER SET 和 COLLATE 两者,则使用这些值。
  • 如果只指定 CHARACTER SET ,则使用此字符集及其默认的校对(如SHOW CHARACTER SET 的结果中所示)。
  • 如果既不指定 CHARACTER SET ,也不指定 COLLATE ,则使用数据库默认。

创建表时为列指定字符集和校对：

1
2
3
4
5
6
7
CREATE TABLE mytable
(
  column1 INT,
  column2 VARCHAR(10)
  column2 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;

使用ORDER BY或其他SELECT子句时也可以临时指定校对顺序：

1
2
SELECT * FROM Customers
ORDER BY lastname, firstname COLLATE latin1_general_cs;

5.可以使用Cast()或Convert()函数来在字符集之间转换

访问控制

查看用户

1.MySQL用户账号和信息存储在名为mysql的MySQL数据库中，通常不需要直接访问mysql数据库和表，也不建议直接访问，因为一旦出错，通常都是致命的。

1
2
USE mysql;
SELECT user FROM user;  #查看有哪些用户

创建用户

1.使用CREATE USER创建用户

1
CREATE USER blueyi IDENTIFIED BY 'testpasswd';

创建用户blueyi并通常IDENTIFIED BY指定密码，当然也可以不指定密码
虽然也可以通过向user表插入行的方式增加用户，但这样很不安全

2.使用GRANT分配权限的同时创建账户：

1
GRANT SELECT ON mysql_test.* TO maxwi;

创建名为maxwi的用户，并分配SELECT权限

3.授权的一些例子：

1
2
3
GRANT USAGE ON *.* TO 'ss'@'%' IDENTIFIED BY PASSWORD 'passwd'; # 创建用户ss，但不分配任何权限
GRANT SELECT ON `sol`.`ukey` TO 'ss'@'%'; # 授予用户ss远程登录并可以访问表sol中的字段ukey
GRANT SELECT, INSERT, UPDATE, DELETE ON `sol`.`node` TO 'ss'@'%';
1
FLUSH PRIVILEGES; # 使权限更改生效

4.在不改变表访问权限的情况取消ss的远程访问：

1
2
UPDATE `user` SET `host` = 'localhost' WHERE `user` = 'ss';
FLUSH PRIVILEGES;

5.在不改变表访问权限的情况下添加ss的远程访问权限：

1
2
UPDATE `user` SET `host` = '%' WHERE `user` = 'ss';
FLUSH PRIVILEGES;

6.使用RENAME修改用户名

1
RENAME USER blueyi TO testuser;

也可以使用UPDATE直接更新user表

删除用户账号

1.使用DROP USER删除用户：

1
DROP USER blueyi;

设置访问权限

1.可以使用SHOW GRANTS FOR来查看用户的权限：

1
2
3
4
5
6
7
mysql> SHOW GRANTS FOR maxwi;
+------------------------------------------------------------------------------------------------------+
| Grants for maxwi@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'maxwi'@'%' IDENTIFIED BY PASSWORD '*CA2DC8B6C2CC6ACB3DE44D56C0BF821DBF383E0B' |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

输出中maxwi有一个权限为USAGE ON .，USAGE表示根本没有权限。
用户定义为user@host MySQL的权限用用户名和主机名结合定义，如果不指定主机名，则使用默认的主机名%。

2.使用GRANT语句设置访问权限，GRANT要求至少提供以下信息：

  • 要授予的权限;
  • 被授予访问权限的数据库或表;
  • 用户名。

如：

1
GRANT SELECT ON mysql_test.* TO maxwi;

此GRANT允许用户在mysql_test.*（即它的所有表）上使用SELECT。
查看权限授予结果：

1
2
3
4
5
6
7
8
mysql> SHOW GRANTS FOR maxwi;
+------------------------------------------------------------------------------------------------------+
| Grants for maxwi@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'maxwi'@'%' IDENTIFIED BY PASSWORD '*CA2DC8B6C2CC6ACB3DE44D56C0BF821DBF383E0B' |
| GRANT SELECT ON `mysql_test`.* TO 'maxwi'@'%'                                                        |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

3.使用REVOKE可以撤销特定的权限：

1
REVOKE SELECT ON mysql_test.* FROM maxwi;

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

  • 整个服务器,使用 GRANT ALL 和 REVOKE ALL;
  • 整个数据库,使用 ON database.*;
  • 特定的表,使用 ON database.table;
  • 特定的列;
  • 特定的存储过程。

5.可供管理的权限：
ALL 除GRANT OPTION外的所有权限
ALTER 使用ALTER TABLE
ALTER ROUTINE 使用ALTER PROCEDURE和DROP PROCEDURE
CREATE 使用CREATE TABLE
CREATE ROUTINE 使用CREATE PROCEDURE
CREATE TEMPORARY TABLES 使用CREATE TEMPORARY TABLE
CREATE USER 使用CREATE USER、 DROP USER、 RENAME USER和REVOKE ALL PRIVILEGES
CREATE VIEW 使用CREATE VIEW
DELETE 使用DELETE
DROP 使用DROP TABLE
EXECUTE 使用CALL和存储过程
FILE 使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION 使用GRANT和REVOKE
INDEX 使用CREATE INDEX和DROP INDEX
INSERT 使用INSERT
LOCK TABLES 使用LOCK TABLES
PROCESS 使用SHOW FULL PROCESSLIST
RELOAD 使用FLUSH
REPLICATION CLIENT 服务器位置的访问
REPLICATION SLAVE 由复制从属使用
SELECT 使用SELECT
SHOW DATABASES 使用SHOW DATABASES
SHOW VIEW 使用SHOW CREATE VIEW
SHUTDOWN 使用mysqladmin shutdown(用来关闭MySQL)
SUPER 使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER 和SET GLOBAL。还允许mysqladmin调试登录
UPDATE 使用UPDATE
USAGE 无访问权限

6.当权限被授予之后，如果删除了表，权限会依然存在，当再次创建该被授予相应权限的表时，权限依然起作用。

7.可以通过逗号分隔一次授予多个权限：

1
GRANT SELECT, INSERT ON mysql_test.* TO maxwi;

更改口令

1.MySQL使用SET PASSWORD 设置用户密码，新密码必须传递到Password()函数进行加密：

1
SET PASSWORD FOR maxwi = Password('test@$$pss');

2.当不指定用户名时，则是为当前登录用户设置口令：

1
SET PASSWORD = Password('testpass');

备份数据库

导入，导出

导入、导出操作使用的是MySQL的语句实现的，所以需要先登录MySQL
1.可以将数据库某个表中的数据保存到一个文件之中，语法格式为

1
SELECT column1, column2 INTO OUTFILE 'file_path and file_name' FROM table_name;

例如要将Orders信息导出取文件Orders.txt，语句如下：

1
SELECT * INTO OUTFILE 'Orders.txt' FROM Orders;

关于导出路径，如果不指定文件夹，则导出到默认文件夹，默认文件夹可以查看mysql的配置文件，通常在配置文件在/etc/mysql/my.cnf，其中的datadir就是数据路径变量，例如我这里导出的文件路径是/var/lib/mysql/mysql_test/Orders.txt，注意系统会默认放在以相应数据库名命名的文件夹中。
如果指定了绝对路径之后报错，是由于系统保护问题，可以参见这里http://stackoverflow.com/questions/2783313/how-can-i-get-around-mysql-errcode-13-with-select-into-outfile

导出的文件中数据按表格的方式存放，如：

1
2
3
4
5
20005   2012-05-01 00:00:00 1000000001
20006   2012-01-12 00:00:00 1000000003
20007   2012-01-30 00:00:00 1000000004
20008   2012-02-03 00:00:00 1000000005
20009   2012-02-08 00:00:00 1000000001

2.导入数据的方式为LOAD DATA INFILE 'fine_path' INTO TABLE table_name;
如将刚导入的Orders.txt导入到表ordercopy中，导入时列必须完全对应，所以必须使用与原表结构完全一样结构。所以这里采用将原表复制一份，然后删除复制表里面的所有行，再次导入到复制表中（不删除会导致主键重复而无法导入）：

1
2
3
4
5
6
#复制原表结构及数据
CREATE TALBE ordercopy AS SELECT * FROM Orders;
#删除ordercopy中的行
DELETE FROM ordercopy;
#导入数据
LOAD DATA INFILE 'Orders.txt' INTO TABLE ordercopy;

mysqldump

1.mysqldump是MySQL用于备份数据库的实用程序，所以它的操作就是命令行工具，它主要产生一个SQL脚本文件，其中包含从头重新创建数据库所必须的所有命令。
mysqldump使用方法：

1
2
3
4
mysqldump -h host -u user -ppassword 数据库名 > 备份文件名   #备份整个数据库
mysqldump -h host -u user -ppassword  --databases 数据库名1 数据库名2 > 备份文件名   #同时备份多个数据库
mysqldump -h host -u user -ppassword --all-databases > 备份文件名   #备份所有数据库
mysqldump -h host -u user -ppassword 数据库名 表名字 > 备份文件名  #备份整个表

当然如果是本机操作，可以省略-h参数，如果没有密码，也可以省略-p参数
例如备份整个mysql_test数据库和，备份表Products的命令：

1
2
mysqldump -u root -ppassword mysql_test > mysql_test_backup.sql #备份整个数据库
mysqldump -u root -ppassword mysql_test.Products > mysql_test_products_backup.sql  #备份表

2.恢复数据库可以使用之前用过的source语句，也就是先登录到MySQL，然后使用语句source进行恢复，也可以使用mysql实用程序进行恢复：

1
mysql -h host -u user -ppassword 数据库 < 数据库的备份文件

如将mysql_test备份的文件恢复到数据库test，当然需要首先登录MySQL后创建数据库test，恢复命令：

1
mysql -u root -ppassword test < mysql_test_backup.sql

mysqlhotcopy

mysqlhotcopy是MySQL提供的另一个可用于备份的实用程序。

在备份前为了保证所有数据被写到磁盘（包括索引数据），可以使用FLUSH TABLES table_name语句。

Welcome to my other publishing channels