SQL 语法

一、基础

主键的值不允许修改,也不允许复用(不能将已经删除的主键值赋给新数据行的主键)。

SQL注释:

## 注释1
SELECT *
FROM mytable; -- 注释2
/* 注释3
   注释3 */

数据库创建与使用:

CREATE DATABASE test;
USE test;

二、创建表

CREATE TABLE mytable (
  # int 类型,不为空,自增
  id INT NOT NULL AUTO_INCREMENT,
  # int 类型,不可为空,默认值为 1,不为空
  col1 INT NOT NULL DEFAULT 1,
  # 变长字符串类型,最长为 45 个字符,可以为空
  col2 VARCHAR(45) NULL,
  # 日期类型,可为空
  col3 DATE NULL,
  # 设置主键为 id
  PRIMARY KEY (`id`));

三、修改表

添加列

ALTER TABLE mytable
ADD col CHAR(20);

删除列

ALTER TABLE mytable
DROP COLUMN col;

删除表

DROP TABLE mytable;

四、插入

普通插入

INSERT INTO mytable(col1, col2)
VALUES(val1, val2);

/* 插入多行,每行用()表示
INSERT INTO mytable(col1, col2)
VALUES(11, "val2"),(22,"test"); */

插入检索出来的数据

INSERT INTO mytable1(col1, col2)
SELECT col1, col2
FROM mytable2;

将一个表的内容插入到一个新表

CREATE TABLE newtable AS
SELECT * FROM mytable;

五、更新

UPDATE mytable
SET col = val
WHERE id = 1;

六、删除

DELETE FROM mytable
WHERE id = 1;

TRUNCATE TABLE 可以清空表,也就是删除所有行。

TRUNCATE TABLE mytable;

使用更新和删除操作时一定要用 WHERE 子句,不然会把整张表的数据都破坏。

七、查询

DISTINCT

相同值只会出现一次。它作用于所有列,也就是说所有列的值都相同才算相同。

SELECT DISTINCT col1, col2
FROM mytable;

LIMIT

限制返回的行数。可以有两个参数,第一个参数为起始行,从 0 开始;第二个参数为返回的总行数。

返回前 5 行:

SELECT *
FROM mytable
LIMIT 5;
SELECT *
FROM mytable
LIMIT 0, 5;

返回第 3 ~ 5 行:

SELECT *
FROM mytable
LIMIT 2, 3;

八、排序

  • ASC :升序(默认)
  • DESC :降序

可以按多个列进行排序,并且为每个列指定不同的排序方式:

SELECT *
FROM mytable
ORDER BY col1 DESC, col2 ASC;

九、过滤

不进行过滤的数据非常大,导致通过网络传输了多余的数据,从而浪费了网络带宽。因此尽量使用 SQL 语句来过滤不必要的数据,而不是传输所有的数据到客户端中然后由客户端进行过滤。

SELECT *
FROM mytable
WHERE col IS NULL;
## SELECT * FROM mytable WHERE col1 BETWEEN 1 AND 12;

下表显示了 WHERE 子句可用的操作符

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

应该注意到,NULL 与 0、空字符串都不同。

AND 和 OR 用于连接多个过滤条件。优先处理 AND,当一个过滤表达式涉及到多个 AND 和 OR 时,可以使用 () 来决定优先级,使得优先级关系更清晰。

IN 操作符用于匹配一组值,其后用括号()接一个 SELECT 子句,从而匹配子查询得到的一组值。

SELECT * FROM mytable WHERE col1 IN (111,11);

SELECT * FROM mytable WHERE col1 IN (SELECT col1 FROM mytable);

NOT 操作符用于否定一个条件。

十、通配符

通配符也是用在过滤语句中,但它只能用于文本字段。

  • % 匹配 >=0 个任意字符;

  • _ 匹配 ==1 个任意字符;

  • [ ] 可以匹配集合内的字符,例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定,也就是不匹配集合内的字符。

使用 Like 来进行通配符匹配。

SELECT *
FROM mytable
WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本

不要滥用通配符,通配符位于开头处匹配会非常慢。

十一、计算字段

在数据库服务器上完成数据的转换和格式化的工作往往比客户端上快得多,并且转换和格式化后的数据量更少的话可以减少网络通信量。

计算字段通常需要使用 AS 来取别名,否则输出的时候字段名为计算表达式。

SELECT col1 * col2 AS alias
FROM mytable;

CONCAT() 用于连接两个字段。许多数据库会使用空格把一个值填充为列宽,因此连接的结果会出现一些不必要的空格,使用 TRIM() 可以去除首尾空格。

SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM mytable;

十二、函数

各个 DBMS 的函数都是不相同的,因此不可移植,以下主要是 MySQL 的函数。

汇总

函 数说 明
AVG()返回某列的平均值
COUNT()返回某列的行数
MAX()返回某列的最大值
MIN()返回某列的最小值
SUM()返回某列值之和

AVG() 会忽略 NULL 行。

使用 DISTINCT 可以汇总不同的值。

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable;

文本处理

函数说明
LEFT()左边的字符
RIGHT()右边的字符
LOWER()转换为小写字符
UPPER()转换为大写字符
LTRIM()去除左边的空格
RTRIM()去除右边的空格
LENGTH()长度
SOUNDEX()转换为语音值

其中, SOUNDEX() 可以将一个字符串转换为描述