基础知识

数据库设计三个范式

第一范式

• 内容相似的数据列必须消除（“消除”的办法是在创建一个数据表来存放它们）。
• 必须为每一组相关数据分别创建一个数据表
• 每条数据记录必须用一个主键来标识

第二范式

• 只要数据列里的内容出现重复，就意味着应该把数据表拆分为多个子表。
• 拆分形成的数据表必须用外键关联起来

第三范式

• 第三范式只有一条规则：与主键没有直接关系的数据列必须消除（“消除”的办法是再创建一个数据表来存放它们）

范式的优缺点

范式的缺点

数据表的个数越多，把从网页上的表单输入的数据分门别类地存入这些数据表的复杂性就越大。这种复杂性不仅会程序员带来烦恼，也会给最终用户带来不便（他们不得不一个接一个地填写表单）

不仅如此，数据表的个数越多，从中提取相关数据生成查询结果的复杂性越大。为了提高查询效率，适度的冗余有时反而是必要的。从多个数据表提取数据往往要比从单个数据表提取数据来得慢；这对那些已不再需要或很少需要修改但经常需要对复杂查询做出快速响应的数据库来说更是如此。（数据库的世界里有一个领域叫做“数据仓库”。在设计数据仓库的时候，人们往往会有意识地增加一些冗余度以获得更好的响应速度。不过，MySQL数据库系统至少在目前还不是人们在建设数据仓库时的首选。所以对这一特殊应用领域里的细节问题也就不多加介绍）

范式的优点

冗余意味着存储空间的浪费。这种浪费在硬盘的单体容量已经达到400GB的今天似乎并不是什么大问题，但事实却是一个大数据库往往同时也是一个慢数据库（至少在数据库的大小超出了计算机内存容量的时候会如此）。

一般而言，严格按照范式设计出来的数据库能够提供最丰富、最灵活的查询选项。

关系

在将数据库转换为范式的过程中，需要把一系列数据关联起来。这种关联用数据库术语来说就是“关系”。两个数据表之间的关联/引用关系可以细分为一下3种：

1. 1:1关系，一对一关系。
2. 1:n关系，一对多关系。
3. n:m关系，多对多关系。

SQL命令可以分为3大类别

• DML(Data Manipulation Language 数据处理语言)：这类命令主要包括SELECT、INSERT、UPDATE、DELETE以及另外几个用来从数据表读出数据、把数据存入数据表或是对数据表里的现有记录进行修改的命令。
• DFL(Data Definition Language 数据定义语言)：这类命令主要包括CREATE TABLE、ALTER TABLE等用来定义和改变数据库结构的命令。
• DCL(Data Control Language 数据控制语言)：这类命令主要包括GRANT、REVOKE以及另外几个用来帮助人们设置和调整MySQL访问控制机制的SQL命令。

简单查询（SELECT）

SELECT * FROM table;

确定数据表里有多少条数据记录（数据行）

SELECT COUNT(*) FROM table;

确定数据表里有多少条内容不重复的数据记录（DISTINCT）

SELECT COUNT(DISTINCT publID) FROM table;

限制查询结果中的数据记录个数（LIMIT）

查询1-10条数据记录
SELECT * FROM table LIMIT 10;
SELECT * FROM table LIMIT 0,10;
查询11-20条数据记录
SELECT * FROM table LIMIT 10,10;

在使用LIMIT关键字确定数据表里的记录数（SQL_CALC_FOUND_ROWS,FOUND_ROWS()）

因为使用SQL_CALC_FOUND_ROWS选项会让MySQL无法对LIMIT查询进行某些特定的优化，所以建议大家只在有必要使用FOUND_ROWS()函数的时候才使用SQL_CALC_FOUND_ROWS选项

SELECT SQL_CALC_FOUND_ROWS * FROM `admin` WHERE group=2 LIMIT 30,10;
SELECT FOUND_ROWS();

对查询结果进行排序（ORDER BY）

让查询结果按照某种顺序排列，如果不加，查询结果通常会按照id编号顺序排列。

选择一种排序方式

在需要对字符串进行排序的时候，MySQL首先选择用的是将人们在创建数据表时为有关数据列设置的排序方式；如果那个数据列上没有排序方式，MySQL将选用数据表上的排序方式；如果数据表上也没有排序方式，MySQL将选用数据库上的排序方式；如果数据库上还没有排序方式，MySQL将回过头来选用那个数据列所使用的字符集的默认排序方式。（一个给定字符集的默认排序方式可以用SQL命令SHOW CHARACTER SET命令查看）

如果希望临时改用另外一种排序方式对查询结果进行排序，可以在SELECT命令里用COLLATE子命令来进行设置，但此时有多少种排序方式可供选择（可以用SHOW COLLATE命令查看）还要取决于有关数据列具体使用的字符集。这种临时改用其他排序方式的做法将导致MySQL在排序时不使用任何索引。换句话说，对于一个大数据表，临时改用其他排序方式对查询结果进行排序将又慢又没有效率！

SELECT * FROM table ORDER BY time COLLATE utf8;

也可以永久性地改变某个数据列的排序方式，这会使有关索引将被自动更新：

ALTER TABLE authors MODIFY authName VARCHAR(60)
CHARACTER SET latinl COLLATE latinl_german2_ci;

如果给定数据列上的可用排序方式里没有所需要的，可以用CONVERT命令为这个数据列里的数据临时指定另外一种字符集。但必须明白这样的后果：这对于一个大数据表来说将是一个需要花费大量时间的过程。下面的例子里，SELECT命令将先把作者姓名从latinl字符集转换为utf8字符集，然后在按照Polish（波兰）排序方式对它进行排序：

SELECT authName FROM authors
ORDER BY CONVERT(authName USING utf8) COLLATE utf8_polish_ci;

试用不同的排序方式

获取可用排序方式列表

SHOW COLLATION;

SELECT latinlchar FROM table
ORDER BY latinlchar COLLATE latinl_general_ci;

SELECT latinlchar FROM table
ORDER BY latinlchar COLLATE latinl_germanl_ci;

SELECT utf8text FROM table
ORDER BY utf8text COLLATE utf8_general_ci;

筛选数据记录（WHERE,HAVING）

SELECT * FROM table WHERE id>10;

SELECT * FROM table WHERE username LIKE '%name%';

注意： 使用LIKE操作符进行比较操作在比较大的操作表上往往非常慢，一是因为MySQL需要读取和分析数据表里的全部数据记录，二是因为这类查询无法用索引来优化。使用了LIKE操作符的查询往往可以改写为一个利用全文索引进行的全文检索查询

查询条件并非只能由关键字WHERE引导，还可以使用关键字HAVING来引导查询条件。如果它们同时出现，MySQL将优先执行WHERE子句，而HAVING子句只能用来对SELECT…WHERE…查询的结果（中间结果）做进一步筛选。HAVING关键字的优点是查询条件还可以作用于有关字段的数学计算结果。

对于MySQL来说HAVING子句不像WHERE条件子句那么容易优化，而这意味着我们应该尽可能的避免使用HAVING子句——除非找不到与之等价的WHERE子句

注解： MySQL不支持colname=NULL这样的用法——如果打算对包含NULL值的数据记录进行搜索，就必须使用ISNULL(colname)函数。

涉及多个表的关联查询（LEFT/RIGHT JOIN）

两个数据表的关联

SELECT * FROM a,b WHERE a.id = b.id;

SELECT * FROM a LEFT JOIN b ON a.id = b.id;

SELECT * FROM a LEFT JOIN b USING (id);

为了最大限度地与其他品牌的数据库系统保持兼容，最好不要使用RIGHT JOIN——应该把它改写为LEFT JOIN (把数据表的左、右顺序调换一下)。

在细节方面，两个USING变体都要求两个数据表里的ID字段有相同的名字。

合并查询结果（UNION）

关键字UNION可以把两个或多个SELECT查询命令合并在一起，而最终的结果是各次查询结果的顺序排列。

当然，不同数据表上的查询也可以合并，但需要保证各次查询结果的数据列在个数和数据类型上都是一样的。否则，MySQL将把最终的结果数据全部转换为第一条SELECT 命令的数据类型。

SELECT command UNION [ALL] SELECT command ...

(SELECT * FROM table LIMIT 0,5)
UNION
(SELECT * FROM table LIMIT 5,5)

分组查询，统计函数（GROUP BY)

SELECT * FROM a,b WHERE a.id = b.id GROUP BY title;

统计函数 GROUP_CONCAT()

GROUP BY …WITH ROLLUP

加上WITH ROLLUP关键字的效果是MySQL将在查询结果的最后一行将自动增加一条总数统计记录，这条记录的ID字段取值或者说这条记录的名字永远是NULL。

SQL解决方案

合并字符串

CONCAT(str1,str2,…)函数把给定字符串合并为一个字符串

SELECT CONCAT(add_province,add_city,add_county) AS address FROM `supply_addres`;

截取字符串

SUBSTRING(s,pos,n)函数的返回值是给定字符串s从位置pos(字符串里的第一个字符的pos=1，而不是pos=0)算起的n个字符。

SELECT SUBSTR(title,1,10) FROM table;

确定字符串的长度

CHAR_LENGTH(s)函数的返回值是字符串中的字符个数，LENGTH()函数的返回值是字符串的字节长度值。

把字符串缩短到一个给定长度

函数IF(a,b,c)将先对条件表达式a求值，如果结果为真（TRUE），则返回b，否则返回c。

将titles数据表里的书名（title字段的值）缩短到30个字符，不足30个字符的书名保持不变；查询结果中的书名将显示为“书名的前20个字符…书名的后5个字符”的形式：

SELECT IF(CHAR_LENGTH(title)>30,
    CONCAT(LEFT(title,20),'...',RIGHT(title,5)),
    title) AS shorttitle
FROM titles;

日期/时间数据的输出格式

DATE_FORMAT(date,format)按自己的想法为日期/时间设置输出格式

SELECT id,time,DATE_FORMAT(time,'%Y-%m-%d %H:%i:%s') FROM `test`;

变量

MySQL的变量可以分为3大类

• 普通变量 这类变量的标志是以@开头，它们在SQL连接被关闭时将失去内容。
• 系统变量和服务器变量 它们的标志是以两个@@字符开头
• 存储过程里的局部变量 这些变量是在存储过程内声明的，只在存储过程内有效。它们没有统一的特殊标志，但是变量名必须与数据表和数据列的名字有区别。

变量赋值
SET @varname = 3;
SELECT @varname := 3;

变量的使用
SELECT @varname;

存储过程和触发器

存储过程（stored procedure, SP）

存储过程是一些存储和执行在MySQL服务器里的SQL语句。根据具体的应用长须，它们有以下好处：

• 更快的速度。
• 避免代码冗余。
• 提高数据库的安全性。

SP的最大缺点之一是很难把它们从一个数据库移植到另一个数据库里去。