SQL

sql是结构化查询语言。

sql是一种标准,几乎所有关系型数据库都遵守。

但是不同的数据库又有自己的扩展。

SQL分为两部分,DML和DDL。

SQL大小写敏感,文本使用单引号,数值不需要引号。

sql语法

sql注释:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# 多行注释
/*
comment multi lines
in a sql file.
*/

# 单行注释
/* comment single line */

# 单行注释还可以直接用 -- 表示
select * from table -- where condition;
-- select * from table where condition;

DDL

DDL: 数据定义语言

create

创建数据库:

1
CREATE DATABASE database_name;

建表:

1
2
3
4
5
CREATE TABLE table(
    column1 type1,
    column2 type2,
    ...
);

create也可以用来创建索引和视图.

drop

删除数据库:

1
DROP DATABASE database_name;

删表:

1
2
3
DROP TABLE table;
# 仅仅删除表中的数据,保留表
TRUNCATE TABLE table;

drop也可以用来删除索引和视图.

alter

变更表:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# 添加列
ALTER TABLE table ADD COLUMN column type;

# 删除列
ALTER TABLE table DROP COLUMN column;

# 改变列的数据类型
# sql server
ALTER TABLE table ALTER COLUMN column type;
# mysql
ALTER TABLE table MODIFY COLUMN column type;
# mysql/oracle
ALTER TABLE table MODIFY column type;

DML

DML: 数据操作语言

select

查询操作:

1
2
3
4
5
SELECT * FROM table;
# 单表查询
SELECT column FROM table;
# 多表查询
SELECT table1.column1, table2.column2 FROM table1, table2;

where子句选取数据:

1
SELECT column FROM table WHERE condition;

引号的使用:

1
2
3
4
# 文本使用单引号,大部分数据库也接受双引号
SELECT column FROM table WHERE name='text';
# 数字不能使用引号.
SELECT column FROM table WHERE id=number;

where子句条件表达式可用的运算符:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
=
<>
!=
>
<
>=
<=

BETWEEN [value1, value2]
BETWEEN value1 AND value2
NOT BETWEEN

IN (value1, value2)
NOT IN

IS NULL
IS NOT NULL

LIKE
NOT LIKE

like运算符的通配符:

1
2
3
4
5
6
%    替代一个或多个字符
_    替代一个字符
[char list]    字符列中的任何单一字符
[^char list]    不在字符列中的任何单一字符
[!char list]    和上面一个等效
SELECT * FROM table WHERE name LIKE '[abc]%' # 以abc开头的name

where子句多个条件可以使用的运算符:

1
2
3
4
AND    与运算
SELECT * FROM table WHERE name='canux' AND id=10;
OR    或运算
SELECT * FROM table WHERE name='canux' OR id=10;

order by子句对结果进行排序,默认升序(ASC):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# 默认升序
SELECT column, column1 FROM table ORDER BY column;
SELECT column, column1 FROM table ORDER BY column, column1;
SELECT column, column1 FROM table ORDER BY column ASC;
# DESC降序
SELECT column, column1 FROM table ORDER BY column DESC;
SELECT column, column1 FROM table ORDER BY column DESC, column1 ASC;
# 根据第几个字段排序
SELECT column, column1 FROM table ORDER BY number;
# 多个字段排序,优先级从前到后
SELECT column FROM table ORDER BY column1, column2
SELECT column FROM table ORDER BY column1 DESC, column2 DESC

top子句用于规定要返回的记录数目:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# SQL Server
SELECT TOP number * FROM table;
# mysql
SELECT * FROM table LIMIT number;
# oracle
SELECT * FROM table WHERE ROWNUM <= number;

Top 一般需要order by
# number最小的10个
select top 10 from table order by number
# number最大的10个
select top 10 from table order by number desc

group by子句用来分组,放在where子句后面如果有的话:

group by一定要用合计函数(count, max, min, sum, avg, …).

1
SELECT column1, aggregate_function(column2) FROM table GROUP BY column1;

having子句用来过滤group by的结果(相当where),放在group by子句后面:

因为where不能和合计函数一起使用,所以使用having子句.

1
SELECT column1, aggregate_function(column2) FROM table GROUP BY column1 HAVING aggregate_function(column) condition;

distinct关键字排除重复:

1
SELECT DISTINCT column FROM table;

as关键字指定别名:

1
2
3
4
5
6
7
# 指定表的别名
SELECT alias_table1.column1, alias_table2.column2
FROM table1 AS alias_table1, table2 AS alias_table2
WHERE alias_table1.column2='test';

# 指定字段别名
SELECT column1 AS alias1, column2 AS alias2 FROM table;

执行顺序:

1
from -> where -> group by -> having -> select -> distinct -> union -> order by -> top

insert into

向表格插入新的行:

1
2
3
4
5
6
7
8
# 一次插入完整行
INSERT INTO table VALUES (value1, value2, ...);
# 一次插入多行
INSERT INTO table VALUES (val11, val12, ...) (val21, val22, ...) ...
# 一次插入一行的一部分
INSERT INTO table (column1, column2, ...) VALUES (value1, value2, ...);
# 一次插入多行的一部分
INSERT INTO table (col1, col2, ...) VALUES (val11, val12, ...) (val21, val22, ...)

update set

修改表中数据:

1
UPDATE table SET column1=value1 WHERE condition;

delete from

删除表中的行:

1
2
3
4
DELETE FROM table WHERE condition;
DELETE FROM table; # 删除所有行

TRUNCATE TABLE table; # mysql清空表的内容,不可恢复

函数

不同的数据库内置的部分函数不同.下面只列出大部分数据库都有的函数.

sql内置两种函数:合计(aggregate)函数 和 标量(scalar)函数.

sql函数的语法:

1
SELECT FUNCTION(args) FROM table ...;

aggregate function

1
2
3
4
5
6
7
AVG(): 求平均值
COUNT(): 统计行数
FIRST(): 返回指定字段中第一个记录的值
LAST(): 返回指定字段中最后一个记录的值
MAX(): 返回一列中的最大值
MIN(): 返回一列中的最小值
SUM(): 返回一列的和

scalar function

1
2
3
4
5
6
7
UCASE(): 把字段的值转换为大写
LCASE(): 把字段的值转换为小写
MID(column, start[, length]): 从文本字段中提取字符,start从1开始
LEN(): 返回文本字段中的长度
FORMAT(column, format): 对字段进行格式化
ROUND(column, decimals): 把数值字段舍入为指定的小数位数
NOW()    返回当前的日期和时间

join

为了从多个表中获取结果,就需要用join.

inner join

inner join也就是默认的join.

全部匹配才返回.相当于table1和table2与.

1
2
3
4
5
6
7
8
9
# 两张表连接
SELECT table1.column, table2.column
FROM table1 INNER JOIN table2
ON table1.column = table2.column;

# 三张表连接
SELECT column
FROM ((table1 INNER JOIN table2 ON table1.column1 = table2.column1) INNER JOIN table3
ON table1.column2 = table3.column2);

left join

左连接.返回左表table1的所有行,和右表table2匹配的行

1
2
3
SELECT column
FROM table1 LEFT JOIN table2
ON table1.column = table2.column;

right join

右连接,返回右表table2的所有行,和左表table1匹配的行

1
2
3
SELECT column
FROM table1 RIGHT JOIN table2
ON table1.column = table2.column;

full join

返回两张表的所有行.

1
2
3
SELECT column
FROM table1 FULL JOIN table2
ON table1.column = table2.column;

union

union操作符用于合并两个或多个select语句的结果集.

union只选取不同的值,也就是说table1和table2中的相同column只出现一次.

1
2
3
SELECT column FROM table1
UNION
SELECT column FROM table2;

union all会列出所有的值,包括重复的.

1
2
3
SELECT column FROM table1
UNION ALL
SELECT column FROM table2;

select into

创建表的备份复件.

把table1的所有列插入到table2:

1
2
3
SELECT * INTO table2 FROM table1 WHERE condition;
# table2属于另外一个数据库externaldatabase
SELECT * INTO table2 IN externaldatabase FROM table1 WHERE condition;

把table1的部分列插入到table2:

1
2
3
SELECT column INTO table2 FROM table1 WHERE condition;
# table2属于另外一个数据库externaldatabase
SELECT column INTO table2 IN externaldatabase FROM table1 WHERE condition;

mysql需要用insert into … select:

1
2
3
4
# 如果table2已经存在
INSERT INTO table2 SELECT * FROM table1 WHERE condition;
# 如果table2不存在
CREATE TABLE table2 AS SELECT * FROM table1 WHERE condition;

constraints

constraints约束用于限制加入表的数据的类型.

NOT NULL

not null强制约束列不能接受null值.

1
2
3
4
CREATE TABLE tablename (
    id int NOT NULL,
    name varchar(255)
);

UNIQUE

unique约束唯一标识数据库表中的每条记录.

每个表可以有多个unique约束.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# mysql:
CREATE TABLE table (
    id int NOT NULL,
    name varchar(255),
    UNIQUE (id)
);

# sql server/oracle:
CREATE TABLE table(
    id int NOT NULL UNIQUE,
    name varchar(255)
);

给约束命名,并且标记多个列到unique:

1
2
3
4
5
6
CREATE TABLE table (
    id int NOT NULL,
    firstname varchar(255),
    lastname varchar(255),
    CONSTRAINT constraintname UNIQUE (id, lastname)
);

给已经存在的表添加约束:

1
2
3
ALTER TABLE table ADD UNIQUE (id);
# 给约束命名,并且标记多个列到unique
ALTER TABLE table ADD CONSTRAINT constraintname UNIQUE (id, lastname);

撤销约束:

1
2
3
4
# mysql:
ALTER TABLE table DROP INDEX constraintname;
# sql server/oracle:
ALTER TABLE table DROP CONSTRAINTNAME constraintname;

PRIMARY KEY

primary key主键必须包含唯一的值,主键列不能包含NULL值.

每张表最多只能有一个主键.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# mysql:
CREATE TABLE table (
    id int NOT NULL,
    firstname varchar(255),
    lastname varchar(255),
    PRIMARY KEY (id)
);

# sql server/oracle:
CREATE TABLE table (
    id int NOT NULL PRIMARY KEY,
    firstname varchar(255),
    lastname varchar(255)
);

给主键命名,并且添加多个列到primary key:

1
2
3
4
5
6
CREATE TABLE table (
    id int NOT NULL,
    firstname varchar(255),
    lastname varchar(255),
    CONSTRAINT constraintname PRIMARY KEY (id, lastname)
);

给已经存在的表添加约束:

1
2
3
ALTER TABLE table ADD PRIMARY KEY (id);
# 给约束命名,并且添加多个列到主键:
ALTER TABLE table ADD CONSTRAINT constraintname PRIMARY KEY (id, lastname);

撤销约束:

1
2
3
4
# mysql:
ALTER TABLE table DROP PRIMARY KEY;
# sql server/oracle:
ALTER TABLE talbe DROP CONSTRAINT constraintname;

FOREIGN KEY

一个表中的外键指向另一个表中的主键.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# mysql:
CREATE TABLE table1 (
    id_1 int NOT NULL,
    id_2 int,
    PRIMARY KEY (id_1),
    FOREIGN KEY (id_2) REFERENCES table2(id_2)
);

# sql server/oracle:
CREATE TABLE table1 (
    id_1 int NOT NULL PRIMARY KEY,
    id_2 int FOREIGN KEY REFERENCES table2(id_2)
);

给外键命名,并且添加多个列到foreign key:

1
2
3
4
5
6
CREATE TABLE table1 (
    id_1 int NOT NULL,
    id_2 int,
    PRIMARY KEY (id_1),
    CONSTRAINT constraintname FOREIGN KEY (id_2) REFERENCES table2(id_2)
);

给已经存在的表添加约束:

1
2
3
ALTER TABLE table1 ADD FOREIGN KEY (id_2) REFERENCES table2(id_2);
# 给外键命名,并且添加多列到外键
ALTER TABLE table1 ADD CONSTRAINT constraintname FOREIGN KEY (id_2) REFERENCES table2(id_2);

撤销约束:

1
2
3
4
# mysql:
ALTER TABLE table1 DROP FOREIGN KEY constraintname;
# sql server/oracle:
ALTER TABLE table1 DROP CONSTRAINT constraintname;

CHECK

check用于限制列中的值的范围.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# mysql:
CREATE TABLE table (
    id int NOT NULL,
    firstname varchar(255),
    lastname varchar(255),
    CHECK (id>0)
);

# sql server/oracle:
CREATE TABLE table (
    id int NOT NULL CHECK (id>0),
    firstname varchar(255),
    lastname varchar(255)
);

给约束命名,并且添加多个列到约束:

1
2
3
4
5
6
CREATE TABLE table (
    id int NOT NULL,
    firstname varchar(255),
    lastname varchar(255),
    CONSTRAINT constraintname CHECK (id>0 AND lastname='cheng')
);

给已经存在的表添加约束:

1
2
3
ALTER TABLE table ADD CHECK (id>0);
# 给约束命名,并且添加多列到约束
ALTER TABLE table ADD CONSTRAINT constraintname CHECK (id>0 AND lastname='cheng');

撤销约束:

1
2
3
4
# mysql:
ALTER TABLE table DROP CONSTRAINT constraintname;
# sql server/oracle:
ALTER TABLE table DROP CHECK constraintname;

DEFAULT

default约束用于向列中插入默认值.

1
2
3
4
CREATE TABLE table (
    id int NOT NULL,
    country varcha(255) DEFAULT 'china'
);

给已经存在的表添加约束:

1
2
3
4
# mysql
ALTER TABLE table ALTER country SET DEFAULT 'china';
# sqlserver/oracle
ALTER TABLE table ALTER COLUMN country SET DEFAULT 'china';

撤销约束:

1
2
3
4
# mysql:
ALTER TABLE table ALTER country DROP DEFAULT;
# sql server/oracle:
ALTER TABLE table ALTER COLUMN country DROP DEFAULT;

increment

auto increment在每次插入新记录时,自动创建主键字段的值.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# mysql使用auto_increment
CREATE TABLE table (
    id int NOT NULL AUTO_INCREMENT,
    firstname varchar(255),
    lastname varchar(255),
    PRIMARY KEY (id)
);

# sql server使用identity:
CREATE TABLE table (
    id int PRIMARY KEY IDENTITY;
    firstname varchar(255),
    lastname varchar(255)
);

# oracle使用sequence对:
CREATE SEQUENCE sequencename
MINVALUE 1
START WITH 1
INCRREMENT BY 1
CACHE 10
# 使用nextval函数获取下一个值:
INSERT INTO table (id, firstname, lastname) VALUES (sequencename.nextval, 'canux', 'cheng');

auto increment的默认起始值是1,每次插入一行默认加一,可以修改起始值:

1
ALTER TABLE table AUTO_INCREMENT=10;

index

创建索引可以快速高效查询数据,但是用户无法看到索引.

1
2
3
4
5
# 在表中创建简单索引,允许使用重复的值:
CREATE INDEX indexname ON table (column, column1, ...);

# 在表中创建唯一索引,两个行不能有相同索引值:
CREATE UNIQUE INDEX indexname ON table (column, column1, ...);

删除索引:

1
2
3
4
5
6
# mysql:
ALTER TABLE table DROP INDEX indexname;
# sql server:
DROP INDEX table.indexname;
# oracle:
DROP INDEX indexname;

view

视图是基于sql语句的结果集的可视化的表:

1
CREATE VIEW viewname AS SELECT * FROM table WHERE condition;

更新视图:

1
CREATE OR REPLACE VIEW viewname AS SELECT column FROM table WHERE condition;

查询视图:

1
SELECT * FROM viewname;

删除视图:

1
DROP VIEW viewname;