mysql for developer

教程来源于 MySQL Tutorial for Developers 网站。

本文记录的是使用 mysql 的完整的一个知识链,包含了存储过程、触发器、数据库视图等知识。

create database

1
2
3
4
5
6
create database [if not exists] database_name;
use database_name;
drop database [if exists] database_name;

上面就是创建、删除、使用数据库表的过程;

MySQL Data Types

mysql 中的数据类型很多,不同的业务需求,不同的要呈现的数据类型,都可以选择不同的 mysql 提供的数据类型。

具体可以参考 http://www.mysqltutorial.org/mysql-data-types.aspx

datetime 和 timestamp 的区别需要注意的是,先说两者的存储结构, datetime 就是直接存储为 YYYY-mm-DD HH:MM:SS,
而 timestamp 存储的是自 1970 年以来的秒;其次就是占用空间的不一样,datetime 需要 8bytes,timestamp 只需要 4bytes;第三个就是时区相关性了,datetime 存储的是字符时间,所以和时区无关,而 timestamp 存储的是自 1970 年以来的秒,它会在存储和查询时,转换为当前的时区。

Creating Tables

1
create table [if not exists] table_name(xxxx,yyyy);

要在定义的表中表示相应的列,可以使用如下的语法

1
column_name data_type[size] [NOT NULL|NULL] [DEFAULT value]

一个表中只能有一列是自增的。

下面是一个例子:

1
2
3
4
5
6
7
8
CREATE TABLE IF NOT EXISTS tasks (
task_id int(11) NOT NULL AUTO_INCREMENT,
subject varchar(45) DEFAULT NULL,
start_date DATE DEFAULT NULL,
end_date DATE DEFAULT NULL,
description varchar(200) DEFAULT NULL,
PRIMARY KEY (task_id)
) ENGINE=InnoDB

关于自增属性 AUTO_INCREMENT 和使用 LAST_INSERT_ID 时,有些需要注意的是:

  1. 通常情况下从 1 开始递增;
  2. 如果我们插入了数据后,我们想要知道当前的 id 是多少,可以使用 LAST_INSERT_ID() 来获取数据;
  3. 如果批量插入数据,只会返回插入的第一个数据的 id,这里需要特别注意;
  4. 如果多次操作插入后,或者插入多个表,只会返回最后操作的表的最后一次插入的第一个数据插入时的 id;
  5. 不是线程安全的,需要注意这里;

使用时,可以使用:

1
select LAST_INSERT_ID()

来读取数据

MySQL Primary Key

两种创建方式,一种是创建表时创建,另外一种是后通过 alter table 来创建。

primary key 要求对应的列必须不为 NULL,并且是唯一的。

1
2
3
4
5
6
create table test(
id int not null auto_increment,
name varchar(32) not null,
primary key(id)
);

或者

1
alter table test add primary key(id);

PRIMARY KEY vs. UNIQUE KEY vs. KEY
在 mysql 中, KEY 就是 INDEX 的同义词, UNIQUE KEY 要求对应的列具有唯一性,但是可以为空,且一个表中可以有多个列是 UNIQUE 属性的。

在一个已有的表中,创建/追加 key/unique 的方法是:

1
2
3
4
5
6
create unique index idx_test on test(title);
ALTER TABLE Persons ADD UNIQUE (Id_P);
ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName);
ALTER TABLE Persons DROP INDEX uc_PersonID;
drop index index_name on table_name;

MySQL Foreign Key

外键是关系型数据库中很重要的一部分,它的好处在于数据的完整性和业务逻辑性的体现。

关于外键主要的是联动删除和联动更新;

可以参考: MySQL Foreign Key

MySQL Temporary Table

临时表有什么作用呢?主要用在什么地方呢?

在说这个之前,先看看临时表的特征:

  1. temporary table 只对创建它的用户可见,当用户结束会话后,temporary table 自动被 drop 掉;
  2. 不同的客户端创建的同名临时表是没有互相影响的,原因如 1;
  3. 在同一个 session 中,不能存在同一个名字的 temporary table;
  4. 临时表可以永久表同名,当访问时,访问的是临时表,只有临时表被 drop 掉后,才访问永久表;

从上面来看,临时表的一个特征就是只在同一个连接的会话期间存在,连接断开,会话关闭,临时表就不存在了。

所以临时表的一个用处就是和存储过程合起来用;另外一个用途就是存储用户产生的数据,直到需要存储到永久表时再去操作。

需要注意的一点是,因为数据库连接池的存在,有可能客户端断开连接后,数据库连接并没有关闭,而是回收到数据库连接池中给了其他连接使用,这个时候,临时表并没有被删除,所以,好的编程实践是显式的在不需要临时表时手动删除它,并且好的编程是使用 drop temporary table temp_name 而不是 drop table temp_name 来删除它。

创建 temporary table 的语法是:

1
create temporary table table_name

下面是一个例子,包含了创建、访问、删除 temporary table 的过程。

1
2
3
4
5
6
7
8
9
10
11
create temporary table top10customers
select p.customerNumber, c.customerName, format(sum(p.amount), 2) as total
from payments as p
inner join customers as c on c.customerNumber = p.customerNumber
group by p.customerNumber
order by total desc
limit 10;
SELECT * FROM top10customers;
drop temporary table top10customers;

更多内容,可以参考: MySQL Temporary Table

Using MySQL SELECT Statement to Query Data

一个最全的 select 语法如下所示:

1
2
3
4
5
6
7
8
SELECT column_1,column_2...
FROM table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
WHERE conditions
GROUP BY group
HAVING group_conditions
ORDER BY column_1 [ASC | DESC]
LIMIT offset, row_count

关于上面的语法解释如下:

SELECT chooses which columns of the table you want to get the data.

FROM specifies the table from which you get the data.

JOIN gets data from multiple table based on certain join conditions.

WHERE filters rows to select.

GROUP BY group rows to apply aggregate functions on each group.

HAVING filters group based on groups defined by GROUP BY clause.

ORDER BY specifies the order of the returned result set.

LIMIT constrains number of returned rows.

where

关于 where 字句,后续跟随的 condition 包括测试相等性,不等性等等,比如:

  • AND / OR
  • < !=

  • BETWEEN
  • LIKE
  • IN
  • IS NULL

测试是否为空值,用的是 is null。

order by

1
2
3
SELECT col1, col2,...
FROM tbl
ORDER BY col1 [ASC|DESC], col2 [ASC|DESC],...

对多列进行排序时,排序的规则是,首先按第一列规则进行排序,然后对于第二列的排序规则,是在第一列的内部进行排序,对于后续的每一个排序规则,都是在前一个的内部进行排序。

这里的内部进行排序是啥子意思呢?首先对第一列排序后,就有了一个按照第一列排序后的列表了,这个时候,如果还有后续排序规则,这个排序规则不是对整个表按照它的规则来排序,而是针对第一列的数据,对第一列相同的数据的行,进行第二列的规则进行排序。注意!这里的差别就在于排序所选择的集合的不一样了,第一列选择的是整个表,第二列选择的是第一列值相同的那些行,第三列选择的是第二列值相同的那些行,就这样一步步的筛选下去,后续列的排序规则的所操作的集合是前一个列的值相同的那些行。

上述过程就是对多列进行排序时,数据库执行时,实际发生的事情。排序所能操作的数据集变了

在实际使用中,除了跟 column 外,还可以跟表达式 或者按照自己的顺序来排序,下面是两个例子:

1
2
3
4
5
SELECT orderNumber,
quantityOrdered * priceEach AS subTotal
FROM orderdetails
ORDER BY orderNumber,
subTotal;
1
2
3
4
5
6
7
8
SELECT orderNumber, status
FROM orders
ORDER BY FIELD(status, 'In Process',
'On Hold',
'Cancelled',
'Resolved',
'Disputed',
'Shipped');

这个例子中,status 字段的可选值就是上面的数据了,在排序时就按照上面的顺序来排。

DISTINCT

筛选去掉重复的 rows 的语法:

1
2
3
SELECT DISTINCT columns
FROM table_name
WHERE where_conditions

这里需要注意的是,distinct 不仅可以对单列进行去重,还可以对多列进行去重,对多列进行去重的规则是,对这些列的组合进行去重,也就是说,只有选择的多列都一样时,才去重,否则就不算重复的。这里与 order by 里面的规则有点区别。

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT DISTINCT state, city
FROM customers
WHERE state IS NOT NULL
ORDER BY state, city
```
#### IN
关于 IN 的语法如下:
```sql
SELECT column_list
FROM table_name
WHERE (expr|column) IN ('value1','value2',...)

这里 where 后跟列或者表达式,然后是 IN 的列表,当 IN里面有多个数据时,使用逗号分割。

下面是一些使用的例子:

1
2
3
SELECT officeCode, city, phone
FROM offices
WHERE country IN ('USA','France')
1
2
3
SELECT officeCode, city, phone
FROM offices
WHERE country NOT IN ('USA','France')
1
2
3
4
5
6
7
8
9
10
SELECT orderNumber,
customerNumber,
status,
shippedDate
FROM orders
WHERE orderNumber IN (
SELECT orderNumber
FROM orderDetails
GROUP BY orderNumber
HAVING SUM(quantityOrdered * priceEach) > 60000)

BETWEEN

它的语法如下:

1
expr (NOT) BETWEEN begin_expr AND end_expr

很多时候, between 可以用不等式和 and/or 语句来替换,但是使用 between 使 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
SELECT productCode,
productName,
buyPrice
FROM products
WHERE buyPrice BETWEEN 90 AND 100
SELECT productCode,
productName,
buyPrice
FROM products
WHERE buyPrice >= 90 AND buyPrice <= 100
SELECT productCode,
productName,
buyPrice
FROM products
WHERE buyPrice NOT BETWEEN 20 AND 100
SELECT productCode,
productName,
buyPrice
FROM products
WHERE buyPrice < 20 OR buyPrice > 100
SELECT orderNumber,
requiredDate,
status
FROM orders
WHERE requireddate
BETWEEN CAST('2003-01-01' AS DATE) AND
CAST('2003-01-31' AS DATE)

LIKE

关于 LIKE 主要有两个方面的通配符来使用: %_

  • % 代表0或多个字符;
  • _ 代表单个字符;

有时候会用上面的两个通配符,那怎么办呢?转义!默认使用 \ 来转义,也可以使用明确的 escape 语法来转义。

下面就是一些例子:

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
SELECT employeeNumber, lastName, firstName
FROM employees
WHERE firstName LIKE 'a%'
SELECT employeeNumber, lastName, firstName
FROM employees
WHERE lastName LIKE '%on'
SELECT employeeNumber, lastName, firstName
FROM employees
WHERE lastname LIKE '%on%'
SELECT employeeNumber, lastName, firstName
FROM employees
WHERE firstname LIKE 'T_m'
SELECT employeeNumber, lastName, firstName
FROM employees
WHERE lastName NOT LIKE 'B%'
SELECT productCode, productName
FROM products
WHERE productCode LIKE '%\_20%'
SELECT productCode, productName
FROM products
WHERE productCode LIKE '%$_20%' ESCAPE '$'

UNION

union 的作用是把从多个表查询出来的数据合并为单独的表数据。

它的语法如下:

1
2
3
4
5
6
SELECT column1, column2
UNION [DISTINCT | ALL]
SELECT column1, column2
UNION [DISTINCT | ALL]

在使用 union 时,有两点需要注意的是:

  1. select 语句后面对应的列的数目要想等;
  2. select 语句后的列的类型要一致,或者要转换成一致;

在使用 union 时,不需要两个表的字段名一样或者表结构一样,只要满足上述两个要求即可。如果在 union 时,第一个表和后续表的列名不一致,又没有使用 alias 来转换名字,那么会显示第一个表的列名作为结果表名。

通常情况下,使用 union 是 union distinct,如果想要看到重复的行,需要明确的使用 union all。

如果想要对结果集进行排序,那么需要对每一个 select 字句使用小括号包裹起来,然后在最后跟上 order by 字句。

下面是一些例子:

1
2
3
4
5
SELECT customerNumber id, contactLastname name
FROM customers
UNION
SELECT employeeNumber id,firstname name
FROM employees
1
2
3
4
5
6
7
8
9
10
11
12
13
(SELECT customerNumber id,contactLastname name
FROM customers)
UNION
(SELECT employeeNumber id,firstname name
FROM employees)
ORDER BY name,id
(SELECT customerNumber, contactLastname
FROM customers)
UNION
(SELECT employeeNumber, firstname
FROM employees)
ORDER BY contactLastname, customerNumber

JOIN

有四种 join, inner join/ left join/ right join/ self join。
join 的实质是求满足一定条件的集合。分为主表和 join 表。

下面依次来说说三种的求集合的情况以及使用方法。

INNER JOIN

1
2
3
4
5
6
SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...
WHERE where_conditions;

其中 column_list 的列数据可以是从单个表中来的,也可以是多个表中来的,如果是从多个表中来的,其中某些表的这个选择的列相同时,为了避免引起错误,需要明确的指出是从哪个表的来的数据 tablename.col 这样的形式。

这里 inner join 采用的是求交集的方法,对于 T1 中的每一行的数据,去和 T2 表的每一行数据做对比,看它们两个是否满足 condition,如果满足,就返回这个指定的 column_list,这里的关键是,只有 T1 和 T2 同时满足 condition 的数据才会返回。

换句话说,这里的满足条件,就是 T1 要满足这个条件,且 T2 也要满足这个条件,如果有多个列,那么依次重复上述过程,所有表都满足那个条件返回。

下面是一个例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT productCode,
productName,
textDescription
FROM products T1
INNER JOIN productlines T2 ON T1.productline = T2.productline;
```
上面的例子中,productCode 和 productName 来源与 products 表,textDescription 来源于 productlines 表。
返回的数据满足的条件是 T1 和 T2 表中的 productline 列值想等。
另外一个例子:
```sql
SELECT T1.orderNumber,
status,
SUM(quantityOrdered * priceEach) total
FROM orders AS T1
INNER JOIN orderdetails AS T2 ON T1.orderNumber = T2.orderNumber
GROUP BY orderNumber

LEFT JOIN/RIGHT JOIN

left join 的结果集就是左边全部,右边只有符合要求的数据被提取出来。从求交集的结果来看,就是先求了两个表的交集,然后再把左边表的剩余结果加入进去,组成了新的结果,如果在结果中,提取了右边表的数据,而右边表又不满足要求,那么对应的列的数据就显示为 NULL。

下面是 left join 的语法规则:

1
2
3
SELECT T1.c1, T1.c2,... T2.c1,T2.c2
FROM T1
LEFT JOIN T2 ON T1.c1 = T2.c1...

下面是例子:

1
2
3
4
5
6
SELECT c.customerNumber,
c.customerName,
orderNumber,
o.status
FROM customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber

从 customers 和 orders 表取数据,采用的是 left join。从业务逻辑上来看,orders 表中的每条数据在 customers 表中都有对应的 customerNumber 值,而 customers 表的中某些数据不一定有 orders 数据。

所以上面的查询操作,提取的结果就是有些人有订单,有些数据 orderNumber 和 status值是空的。这里如果使用 inner join,得到的结果就是有订单号的客户了。那要是想要抽取出来没有订单号的客户呢?

采用 where 字句,提取出来 orderNumber 为空的那些数据,下面是代码:

1
2
3
4
5
6
7
SELECT c.customerNumber,
c.customerName,
orderNumber,
o.status
FROM customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber
WHERE orderNumber IS NULL

right join 和 left join 的行为是相似的,只是为了右表取整个集合,左表取和右表满足条件的内容,其余用 NULL 代替。

self join
实质上没有一个关键词叫 self join,这个是概念上的意思,它的实质是一个表和自己做 join 操作。

要让一个表满足能和自己做 join 操作,那么我们需要使用 alias 表名来对同一个表进行左右的区分,这样一个表就变成了两个表了,就可以对其中的一些字段做 inner join 或者 left/right join 操作。这就是 self join 的实质。

下面是例子:

在这个例子中,有一张雇员表的信息表,其中每一条记录都有一个雇员 id 和他要 reportsto 的人的 id,也就是说,雇员和雇员的上级的关系在一张表中,那么我们如何找出来所有的雇员和它们的经理之间的关系呢?

这里就是使用了 self join 的技术,当 reportsto 和 雇员id 相等时,即为雇员和经理的关系。
也就是 T1.reportsto == T2.id 表明了 T1.id 是雇员, T2.id 是经理,下面就是根据这个思路写的第一版本的 sql:

1
2
3
select concat(e.firstName, ', ', e.lastName) as 'guyuan', concat(r.firstName, ', ', r.lastName) as 'Manager'
from employees as e
inner join employees as r on e.reportsTo = r.employeeNumber;

但是这里有个问题是,某些人,比如说最高层的人是没有回报对象的,即它的 reportesto 是空值,上面使用的是 inner join 就会去掉这些值,如果我们想要提取出来这些人,就需要使用 left join了,这样的情况下,reportsto 值是空的,我们需要进行处理后,就可以让显示的信息更有意义了,而不是显示个NULL,下面是包含了顶级管理员的 sql 语句:

1
2
3
select concat(e.firstName, ', ', e.lastName) as 'guyuan', ifnull(concat(r.firstName, ', ', r.lastName), 'boss') as 'Manager'
from employees as e
left join employees as r on e.reportsTo = r.employeeNumber;

以上就是 mysql 中使用 inner join/ left join/ right join/ self join 的具体用法和它们的意义,在很多时候,灵活的使用这些数据,可以让程序效率更高,数据处理更明晰。

MySQL GROUP BY

The MySQL GROUP BY clause is used with the SELECT statement to group rows into subgroups by the one or more values of columns or expressions.

group by 是把表里的数据进行分组,它可以根据多个列或者表达式来操作。

group by 主要是搭配聚合函数使用,如果是单纯的使用,似乎结果和使用 distinct 差不多,下面是两个例子,这样的命令看着例子一下子就明白了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT status
FROM orders
GROUP BY status
SELECT DISTINCT status
FROM orders
SELECT status, count(*)
FROM orders
GROUP BY status
SELECT status, count(*)
FROM orders
GROUP BY status DESC;

MySQL HAVING

having 类似于 where 的功能,也是对筛选查询结果的,不同于 where 的是, where 是对每一行进行筛选判断,having 是对 subgroup 进行筛选的。这就是它们两者的不同的地方。

having 的语法规则和使用与 where 相似,下面是例子:

1
2
3
4
5
6
SELECT ordernumber,
SUM(quantityOrdered) AS itemsCount,
SUM(priceeach) AS total
FROM orderdetails
GROUP BY ordernumber
HAVING total > 1000

在上面的语句中,我们查询了订单号,订单数量和价格。我们期望的结果是我们每一个订单号,总共卖了多少份,总的销售价是多少。所以需要用到 group by 来对订单号分组,然后我们又期望看到那些销售额在 1000 以上的订单号,所以我们就有了筛选的语句 having total > 1000;

通常而言,group by 和聚合函数一起使用,而 having 就是搭配 group by 使用。

MySQL Subquery

大多数情况下 mysql 的子查询语句就是被小括号包裹起来的另外一个查询语句,它的返回结果给了外层语句作为外层语句的一部分存在。

更多信息参考: MySQL Subquery

关于 mysql 的基础知识,基本上就到此为止了,更多的基础知识的参考,可以参见:
Basic MySQL Tutorial

下面进入 mysql 的高级主题,包含 MySQL Stored Procedure, MySQL Triggers, MySQL Views 以及 MySQL Functions

MySQL Stored Procedure

MySQL Stored Procedure