概述

本文主要介绍索引、数据类型和更多复杂的查询结构

P.S.：译自SQL for Beginners Part 2¹

Database Indexes

Indexes或keys主要用于提高表的数据获取速度。对于指数化数据库的列的大多数原因如下：

每个表应该有一个PRIMARY KEY索引，通常作为一个id列
如果一列预计含有唯一的值，那么该列应该有一个UNIQUE索引
如果经常在一列上执行搜索（在WHERE语句里），应该有一个正规的索引
如果一列用于和另一个表的关系，如果可能，该列应该是一个FOREIGN KEY，否则只有一个正规的索引

PRIMARY KEY

几乎每个表都应该有一个PRIMARY KEY，大多数情况下作为一个带AUTO_INCREMENT选项的INT

CREATE TABLE states (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(20)
);

CREATE TABLE states (
  id INT AUTO_INCREMENT,
  name VARCHAR(20),
  PRIMARY KEY (id)
);

UNIQUE

表示该列有唯一的值。默认UNIQUE索引会以列名来命名，也可以指定其它名称

CREATE TABLE states (
  id INT AUTO_INCREMENT,
  name VARCHAR(20),
  PRIMARY KEY (id),
  UNIQUE (name)
);

CREATE TABLE states (
  id INT AUTO_INCREMENT,
  name VARCHAR(20),
  PRIMARY KEY (id),
  UNIQUE state_name (name)
);

INDEX

这种类型的索引不具有唯一性的限制

CREATE TABLE states (
  id INT AUTO_INCREMENT,
  name VARCHAR(20),
  join_year INT,
  PRIMARY KEY (id),
  UNIQUE (name),
  INDEX (join_year)
);

也可以使用KEY代替INDEX

CREATE TABLE states (
  id INT AUTO_INCREMENT,
  name VARCHAR(20),
  join_year INT,
  PRIMARY KEY (id),
  UNIQUE (name),
  KEY (join_year)
);

More About Performance

添加一个索引减少了INSERT和UPDATE查询的性能，因为每次新数据被添加到表里时，索引数据也自动更新，需要做额外的工作，而在SELECT查询的性能增益通常远大于此。

Function

GROUP BY

GROUP BY语句将获取到的数据行分组

1	SELECT * FROM states GROUP BY join_year;

COUNT(*)

COUNT(*)是结合GROUP BY查询的最常用的函数，返回每组的行数

1	SELECT COUNT(*), join_year FROM states GROUP BY join_year;

Grouping Everything

如果不适用GROUP BY函数或语句，全部的结果会放在单一组里

1	SELECT COUNT(*) FROM states;

MIN(), MAX() and AVG()

这些函数返回最小数，最大数和平均数

1	SELECT MIN(population), MAX(population), AVG(population);

GROUP_CONCAT()

将组里所有的值使用提供的分隔符连接成一个单一字符串

1 2	SELECT GROUP_CONCAT(name SEPERATOR ‘, ‘), join_year FROM states GROUP BY join_year;

SUM()

累加数字值

1	SELECT SUM(population) AS usa_population FROM states;

Control Flow

IF()

需要三个参数，第一个参数是条件，如果条件为true，使用第二个参数，否则使用第三个参数

1	SELECT IF(true, ‘foo’, ‘bar’);

CASE

类似于switch-case语句

SELECT COUNT(*),
CASE
  WHEN population > 5000000 THEN ‘big’
  WHEN population > 1000000 THEN ‘medium’
  ELSE ‘small’ END
  AS state_size
FROM states GROUP BY state_size;

Other

Having

允许应用条件到隐藏的域上，如聚合函数返回的结果，通常和GROUP BY结合使用

1
2
3

SELECT COUNT(*), join_year FROM states
GROUP BY join_year
HAVING COUNT(*) > 1;

Subqueries

在获取一个查询的结果后，使用该结果用于其它查询

1
2
3

SELECT * FROM states WHERE population = (
  SELECT MAX(population) FROM states
);

注意下面的查询语句和上面查询语句的区别

1	SELECT * FROM states ORDER BY population DESC LIMIT 1;

IN()

通过内部查询获取多个结果

SELECT * FROM states WHERE join_year IN (
  SELECT join_year FROM states
  GROUP BY join_year
  HAVING COUNT(*) > 1
) ORDER BY join_year;

UNION

使用UNION查询，可以结合多个SELECT查询的结果

1
2
3

(SELECT * FROM states WHERE name LIKE ‘n%’)
UNION
(SELECT * FROM states WHERE population > 10000000);

Note：

使用UNION查询的结果只会出现一次，因为重复行自动从查询结果中移除
使用UNION可以在不同的表里结合查询结果

INSERT … ON DUPLICATE KEY UPDATE

首先执行INSERT，如果因为带PRIMARY KEY或UNIQUE KEY索引的重复值而失败，就去执行UPDATE

1 2	INSERT INTO products SET name = ‘breadmaker’, stock = 1 ON DUPLICATE KEY UPDATE stock = stock + 1;

REPLACE INTO

如果一个重复行被发现，先删除然后再执行INSERT

1	REPLACE INTO products SET name = ‘breadmaker’, stock = 5;

INSERT IGNORE

禁止重复错误的一种方式，通常用来阻止应用破坏。有时企图添加一个新行，万一有重复行，让该操作直接失败，没有任何抱怨

1	INSERT IGNORE INTO products SET name = ‘breadmaker’, stock = 1;

Date Types

每个表列需要有一个数据类型

Integer Data Types

整数列只能存放自然数（没有小数点），默认可以是正负数，如果指定了UNSIGNED，则只能存放整数

Type	Bytes
TINYINT	1
SMALLINT	2
MEDIUMINT	3
INT	4
BIGINT	8

Non-Integer Numeric Data Types

这些数据类型可以存放小数，如FLOAT、DOUBLE和DECIMAL

Type	Description
FLOAT	4字节
DOUBLE	8字节，有更好的进度
DECIMAL(M, N)	有基于精度级别的大小，`M`是数字的最大位数，`N`是小数点右边的位数

String Data Types

这些数据类型可以存放字符串

Type	Description
CHAR(N)	存放N个字符，有一个固定的大小，最多存放255个字符
VARCHAR(N)	存放N个字符，但存储大小不固定，N只是用于最大大小，最多存放65535个字符
TEXT	适合于长字符串，有65535个字符的限制
MEDIUMTEXT	1670W个字符限制
LONGTEXT	43亿个字符限制

Date Types

Type	Description
DATE	存放日期，以`YYYY-MM-DD`的格式显示，不包含时间信息；范围从`1001-01-01`到`9999-12-31`
DATETIME	包含日期和时间，以`YYYY-MM-DD HH:MM:SS`的格式显示；范围从`1001-01-01 00:00:00`到`9999-12-31 23:59:59`；占8个字节
TIMESTAMP	占4个字节；范围从`1970-01-01 00:00:01`到`2038-01-19 03:14:07`

1.SQL for Beginners Part 2 ↩