Permalink
Switch branches/tags
Nothing to show
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
1186 lines (577 sloc) 23.6 KB
layout title date categories
post
sql 基础
2017-06-05 09:00:05 +0800
sql

SQL 快速参考

SELECT

SELECT DISTINCT

在一个表中,一个列可能包含多个重复中值,通过 DISTINCT 仅仅列出不同的值

SELECT DISTINCT column_name, column_name FROM table_name;

WHERE 子句中的运算符

  • = 等于

  • <> 不等于

  • `> 大于

  • < 小于

  • `>= 大于等于

  • <= 小于等于

  • BETWEEN 在某个范围内

  • LIKE 搜索某种模式

  • IN 指定针对某个列的多个可能值

SELECT TOP

用于规定要返回的记录的数目,尤其对于拥有大量记录的大型表非常有用。

# SQL Server

SELECT TOP number | percent column_name from table_name;

# MySQL

SELECT column_name from table_name LIMIT number;

# Oracle

SELECT column_name from table_name WHERE ROWNUM <= number;

LIKE

LIKE 操作符用于在 WHERE 子句中搜索列中的特定模式

其中可以使用 % 通配符

SELECT * FROM table_name WHERE column_name LIKE '%key%"';

SELECT * FROM table_name WHERE column_name NOT LIKE 'xx';

REGEXP

通过使用 REGEXP 关键字可以使用正则表达式匹配

SELECT * from table_name WHERE column_name REGEXP 'regex_expression';

IN

IN 操作符可以在 WHERE 子句判断是否在一个集合中

SELECT * FROM table_name WHERE column_name (NOT) IN (value1, value2, ..., valueN);

AND OR

AND 关键字等价与逻辑 &&

OR 关键字等价与逻辑 ||

SELECT * FROM table_name WHERE expression1 AND | OR  expression2;

BETWEEN

BETWEEN 关键字可以过滤值在某个一个范围内

值的范围可以是数值、文本、日期

SELECT * FROM table_name WHERE column_name (NOT) BETWEEM value1 AND value2;

# 文本

SELECT * FROM table_name WHERE name BETWEEN 'A' AND 'H';

# 时间

SELECT * FROM table_name WHERE date BETWEEN '2017-02-26' AND now();

请注意,在不同的数据库中,BETWEEN 操作符会产生不同的结果! 在某些数据库中,BETWEEN 选取介于两个值之间但不包括两个测试值的字段。 在某些数据库中,BETWEEN 选取介于两个值之间且包括两个测试值的字段。 在某些数据库中,BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。 因此,请检查您的数据库是如何处理 BETWEEN 操作符

时间

时间格式可以使用文本插入,也可以使用 now() 等时间函数插入

INSERT INTO time_tabel (date) values ('2017-02-28');

INSERT INTO time_table (date) values (now());

JOIN

在所有的 JOIN 子句中,如果没有加 GROUP BY | ORDER BY 等排序命令,得到的结果均是以右表原本的记录的顺序,一一与左表进行匹配

INNER JOIN

使用左表去依次匹配右表的记录,只显示成功匹配的记录

SELECT * FROM w.name, a.date FROM websites w  JOIN access_log ON w.id=a.site_id ORDER a.count;

LEFT JOIN

LEFT JOIN 关键字从左表(table1)返回所有的行,即使右表(table2)中没有匹配。如果右表中没有匹配,则结果为 NULL。

RIGHT JOIN

RIGHT JOIN 关键字从右表 table2 返回所有的行,即使左表中没有与之匹配。如果左表中没有与之匹配,则返回 NULL。

FULL OUTER JOIN

FULL OUTER JOIN 相当于是 LEFT JOIN + RIGHT JOIN。返回左表和右表的所有结果,如果左表中的数据在右表中没有与之对应的就返回 NULL,如果右表中的数据在左表没有与之对应的就返回 NULL。

MySQL 不支持 FULL OUTER JOIN。

UNION

UNION 操作符合并两个或者多个 SELECT 语句的结果。

注意:UNION 内部的每个 SELECT 语句必须拥有相同数量的列。咧业必须拥有相似的数据类型(经过尝试大多数都可以转化为字符串类型)。

SELECT column_name FROM table1

UNION

SELECT column_name FROM tables;

注释:默认的,UNION 操作符会选取不同的值。如果允许重复,请使用 UNION ALL

SELECT column_name FROM table1

UNION ALL

SELECT column_name FROM table2;

注释:UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句的列名。

MySQL 复制数据到新表

CREATE TABLE new_table SELECT column_name (AS new_column_name) FROM old_table WHERE expression;

通过 AS 语句来修改新表中字段名

复制数据只是单纯地复制数据以及数据类型到新表,但是各种外健主键约束等需要手动加。

SELECT INTO

把从一个表中的查询结果存储到新表中

可以使用 AS 子句来应用新名称

SELECT column_name (AS new_column_name)

INTO newtable 

FROM table1;

MySQL 并不支持 SELECT ... INTO 语句。

但是支持 INSERT INTO ... SELECT

INSERT INTO ... SELECT

INSERT INTO table2 

SELECT column_name  FROM table1;

# 或者指定列

INSERT INTO table2 (column_name)

SELECT column_name FROM table1;

CREATE DATABASE

创建数据库

CREATE DATABASE dbname;

DROP DATABASE

删除数据库

DROP DATABASE dbname;

UNIQUE

CREATE TABLE Person(

P_id INT NOT NULL,

LastName VARCHAR(255) NOT NULL,

FirstName VARCHAR(255),

Address VARCHAR(255),

City VARCHAR(255),

# 在创建表时添加唯一约束

CONSTRAINT uc_PersonID UNIQUE(P_id, LastName)

);



# 或者直接修改表

ALTER TABLE Person ADD CONSTRAINT UNIQUE(P_id, LastName);

撤销 UNIQUE 约束

  • MySQL:
ALTER TABLE Person DROP INDEX uc_PersonID;
  • SQL Server / Oracle
ALTER TABLE Person DROP CONSTRAINT uc_PersonID;

PRIMARY KEY

PRIMARY KEY = NOT NULL + UNIQUE

# 创建表时声明主键

CREATE TABLE Person(

P_id INT NOT NULL,

LastName VARCHAR(255) NOT NULL,

FirstName VARCHAR(255),

Address VARCHAR(255),

City VARCHAR(255),

# pk_PersonID 是主键,其有 P_id + LastName 组成

CONSTRAINT pk_PersonID PRIMARY KEY (P_id, LastName)

);



# 在表创建后加入主键

ALTER TABLE Person ADD CONSTRAINT pk_PersonID PRIMARY KEY(P_id, LastName);

撤销 PRIMARY KEY 约束

MySQL

# PRIMARY KEY 唯一,所以无需指定

ALTER TABLE Person DROP PRIMARY KEY;

SQL Server / Oracle

ALTER TABLE Person DROP CONSTRAINT pk_PersonID;

SHOW CREATE TABLE

查看表信息

SHOW CREATE TABLE table_name;

FORENGN KEY

FORENGN KEY 约束用于预防破坏表之间连接的行为。

FORENGN KEY 约束能防治非法数据插入外健列,因为它必须是指向的那个表中的值之一。

MySQL

CREATE TABLE orders(

id INT NOT NULL PRIMARY KEY,

orderNo INT NOT NULL,

p_id int,

# p_id 连接到 persons 表的 id

FOREIGN KEY (p_id) REFERENCES persons(id)

);

SQL Server | Oracle

CREATE TABLE Orders
(
id int NOT NULL PRIMARY KEY,
orderNo int NOT NULL,
p_id int FOREIGN KEY REFERENCES persons(p_id)
);

如需命名 FOREIGN KEY 约束,并定义多个列的 FOREIGN KEY 约束

CREATE TABLE orders(

id INT NOT NULL,

orderNo INT NOT NULL,

p_id INT,

PRIMARY KEY (id),

# 外健名称为 fk_perOrders

CONSTRAINT fk_perOrders FOREIGN KEY (p_id) REFERENCES persons(id)

);

添加 FOREIGN KEY 约束

MySQL

ALTER TABLE orders ADD FORENGN KEY (p_id) REFERENCES persons(id);

MySQL | SQL Server | Oracle

ALTER TABLE  orders ADD CONSTRAINT foreign_key_name FOREIGN KEY (p_id)

 REFERENCES persons(id);

撤销 FOREIGN KEY 约束

SHOW CREATE TABLE orders; 可以查看对应的外健名

MySQL

ALTER TABLE orders DROP FOREIGN KEY foreign_key_name;

SQL Server | Oracle

ALTER TABLE orders DROP CONSTRAINT foreign_key_name;

CHECK

“所有的存储引擎均对CHECK子句进行分析,但是忽略CHECK子句。” The CHECK clause is parsed but ignored by all storage engines.

CREATE TABLE persons(

id NOT NULL,

name VARCHAR(255) NOT NULL,

ADD CONSTRAINT chk_person CHECK (id > 0 AND name <> 'xxx' )

);

# 在创建表后添加 CHECK 约束

ALTER TABLE persons ADD CONSTRAINT chk_person CHECK(id  > 0 AND name <> 'xxx');

DEFAULT

DEFAULT 约束可以跟的是一个函数,如 now();

在创建表后添加 DEFAULT 约束

MySQL | SQL Server

ALTER TABLE persons 

ALTER city SET DEFAULT 'New York';

Oracle

ALTER TABLE persons 

MODIFY city DEFAULT 'New York';

撤销 DEFAULT 约束

ALTER TABLE persons 

ALTER COLUMN city DROP DEFAULT;

INDEX

CREATE INDEX 语句在表中创建索引,再不读取整个表的情况下,索引使书库库应用程序可以更快地查找到数据。

注释:更新一个包含索引的表需要比一个没有索引的表花费更多的时间,所以只在常常被所搜的列上创建索引。

在表中创建一个简单索引,允许使用重复的值。

CREATE INDEX index_name ON table_name (column_name);

在表中创建一个唯一的索引,两个行不能拥有相同的索引值。

CREATE UNIQUE INDEX index_name ON table_name (column_name);

如果需要索引不止一个列,可以如下:

CREATE INDEX index_name ON table_name (column_name_1, column_name_2);

撤销 INDEX

index_name 可以通过 SHOW CREATE TABLE table_name; 查得

DROP INDEX index_name ON table_name;

# MySQL 上还可以这样:

ALTER TABLE table_name DROP INDEX index_name;

删除表中的所有数据,并且不删除表

  • 方法1
DELETE FROM table_name;
  • 方法2
TURNCATE TABLE table_name;

对表中的列进行操作

  1. 添加列
ALTER TABLE table_name ADD column_name datatype;

2.删除列

ALTER TABLE table_name DROP COLUMN column_name;

3.修改列

ALTER TABLE table_name MODIFY column_name datatype;

修改 AUTO_INCREMENET 序列的起始值

MySQL

ALTER TABLE table_name AUTO_INCREMENT=start_number;

SQL Server

CREATE TABLE persons(

# start_number 起始值 interval_number 间隔

id INT IDENTITY(start_number, interval_number) PRIMARY KEY

);

Oracle

需要创建 SEQUENCE 对象

CREATE SEQUENCE seq

MINVALUE 1

START WITH 1

INCREMENT BY 1

# 缓存10个值提高性能

CACHE 10

插入SQL

INSERT INTO table_name(id, column1, column2) VALUES (seq.nextval, xxx, xxx);

视图 VIEW

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

创建视图

CREATE VIEW view_name AS

SELECT column_name(s) FROM table1;

调用视图

SELECT * FROM view_name;

修改视图

CREATE OR REPLACE VIEW view_name AS

SELECT column(s) FROM table1;

撤销视图

DROP VIEW view_name;

Date 函数

MySQL Date 函数

函数 功能 实例
NOW() 返回当前日期和时间 2017-02-26 16:23:16
CURDATE() 返回当前日期 2017-02-26
CURTIME() 返回当前时间 16:23:16
DATE(date) 提取日期或者日期/时间表达式的日期部分 DATE(NOW()) ==> 16:23:16
EXTRACT(unit FROM date) 通过unit指定返回date中年/天/周/日/时/分/秒等 EXTREACT(YEAR FROM now()) ==> 2017
DATE_ADD(date, INTERVAL expr type) 向日期添加指定的时间间隔 DATE_ADD(NOW(), INTERVAL 1 DAY) ==> 2017-02-27 16:23:16
DATE_SUB(date, INTERVAL expr type) 向日期减去指定的时间间隔 DATE_SUB(NOW(), INTERVAL 1 DAY) ==> 2017-02-26 16:23:16
DATEDIFF(date1, date2) 返回 date1 - date2 的天数 DATEDIFF('2017-02-27', '2017-02-28') ==> -1
DATE_FORMAT(date, format) 以不同的格式输出日期(自定义) DATE_FORMAT('2017-02-28 16:23:16', '%b %d %Y %h:%i %p') ==> FEB 26 2017 4:23 pm

MySQL 日期格式

类型 格式
DATE YYYY-MM-DD
DATETIME YYYY-MM-DD HH:MM:SS
TIMESTAMP YYYY-MM-DD HH:MM:SS
YEAR YYYY 或 YY

2017-02-26 与 2017-02-26 00:00:00 并不会匹配

提示:如果希望查询简单且容易维护,请不要将日期中使用时间部分。

NULL

NULL 值与其他值不一样,NULL 无法使用运算符来测试 NULL 值,比如:= , < , <>

判断某个值是否是 NULL 使用 IS NULL

判断某个值是否不是 NULL 使用 IS NOT NULL

# IS NULL

SELECT * FROM persons WHERE city IS NULL;

# IS NOT NULL

SELECT * FROM persons WHERE city IS NOT NULL;

部分函数、数学或者逻辑运算与 NULL 运算后会得到 NULL 值。

如果我们希望把 NULL 在运算中用某个数据来代替 ,我们可以这样做:

SQL Server

SELECT ISNULL(NULL, 0);    # return 0

Oracle

SELECT NVL(NULL, ' '); # return ' '

MySQL

SELECT IFNULL(NULL, 1); # return 1

# OR

SELECT COALESCE(NULL, 'ABC');  # return 'abc'

SQL 数据类型

SQL 通用数据类型

数据类型 描述
CHARACTER(n) 字符/字符串。固定长度 n。
VARCHAR(n) 或 CHARACTER VARYING(n) 字符/字符串。可变长度。最大长度 n。
BINARY(n) 二进制串。固定长度 n。
BOOLEAN 存储 TRUE 或 FALSE 值
VARBINARY(n) 或 BINARY VARYING(n) 二进制串。可变长度。最大长度 n。
INTEGER(p) 整数值(没有小数点)。精度 p。
SMALLINT 整数值(没有小数点)。精度 5。
INTEGER 整数值(没有小数点)。精度 10。
BIGINT 整数值(没有小数点)。精度 19。
DECIMAL(p,s) 精确数值,精度 p,小数点后位数 s。例如:decimal(5,2) 是一个小数点前有 3 位数小数点后有 2 位数的数字。
NUMERIC(p,s) 精确数值,精度 p,小数点后位数 s。(与 DECIMAL 相同)
FLOAT(p) 近似数值,尾数精度 p。一个采用以 10 为基数的指数计数法的浮点数。该类型的 size 参数由一个指定最小精度的单一数字组成。
REAL 近似数值,尾数精度 7。
FLOAT 近似数值,尾数精度 16。
DOUBLE PRECISION 近似数值,尾数精度 16。
DATE 存储年、月、日的值。
TIME 存储小时、分、秒的值。
TIMESTAMP 存储年、月、日、小时、分、秒的值。
INTERVAL 由一些整数字段组成,代表一段时间,取决于区间的类型。
ARRAY 元素的固定长度的有序集合
MULTISET 元素的可变长度的无序集合
XML 存储 XML 数据

**SQL 数据类型在不同平台的区别 **

数据类型 Access SQLServer Oracle MySQL PostgreSQL
boolean Yes/No Bit Byte N/A Boolean
integer Number (integer) Int Number Int Integer Int Integer
float Number (single) Float Real Number Float
currency Currency Money N/A N/A Money
string (fixed) N/A Char Char Char Char
string (variable) Text (<256) Memo (65k+) Varchar Varchar Varchar2 Varchar Varchar
binary object OLE Object Memo Binary (fixed up to 8K) Varbinary (<8K) Image (<2GB) Long Raw Blob Text Binary Varbinary

MySQL 数据类型

MySQL 主要有三种类型:Text(文本) 、 Number(数字) 、 Date/Time (日期/时间)

Text

数据类型 描述
CHAR(size) 保存固定长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size) 保存可变长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的最大长度。最多 255 个字符。注释:如果值的长度大于 255,则被转换为 TEXT 类型。
TINYTEXT 存放最大长度为 255 个字符的字符串。
TEXT 存放最大长度为 65,535 个字符的字符串。
BLOB 用于 BLOBs(Binary Large OBjects)。存放最多 65,535 字节的数据。
MEDIUMTEXT 存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB 用于 BLOBs(Binary Large OBjects)。存放最多 16,777,215 字节的数据。
LONGTEXT 存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.) 允许您输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值,则插入空值。注释:这些值是按照您输入的顺序排序的。可以按照此格式输入可能的值: ENUM('X','Y','Z')
SET 与 ENUM 类似,不同的是,SET 最多只能包含 64 个列表项且 SET 可存储一个以上的选择。

Number

数据类型 描述
TINYINT(size) -128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。
SMALLINT(size) -32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。
MEDIUMINT(size) -8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。
INT(size) -2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。
BIGINT(size) -9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。
FLOAT(size,d) 带有浮动小数点的小数字。在 size 参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d) 带有浮动小数点的大数字。在 size 参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d) 作为字符串存储的 DOUBLE 类型,允许固定的小数点。在 size 参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。

这些整数类型拥有额外的选项 UNSIGNED。通常,整数可以是负数或正数。如果添加 UNSIGNED 属性,那么范围将从 0 开始,而不是某个负数。

Date

数据类型 描述
DATE() 日期。格式:YYYY-MM-DD **注释:**支持的范围是从 '1000-01-01' 到 '9999-12-31'
DATETIME() *日期和时间的组合。格式:YYYY-MM-DD HH:MM:SS **注释:**支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'
TIMESTAMP() *时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的秒数来存储。格式:YYYY-MM-DD HH:MM:SS **注释:**支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC
TIME() 时间。格式:HH:MM:SS **注释:**支持的范围是从 '-838:59:59' 到 '838:59:59'
YEAR() 2 位或 4 位格式的年。**注释:**4 位格式所允许的值:1901 到 2155。2 位格式所允许的值:70 到 69,表示从 1970 到 2069。

即便 DATETIME 和 TIMESTAMP 返回相同的格式,它们的工作方式很不同。在 INSERT 或 UPDATE 查询中,TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式,比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

SQL 函数

SQL Aggregate 函数

  • AVG() - 返回平均值

SELECT site_id, count FROM access_log WHERE count > (SELECT AVG(count) FROM access_log);

  • COUNT() - 返回行数

  • FIRST() - 返回第一条记录 **注释:**只有 MS Access 支持 FIRST()

  • LAST() - 返回最后一条记录 **注释:**只有 MS Access 支持 LAST()

  • MAX() - 返回最大值

SELECT MAX(column_name) FROM table_name;

  • MIN() - 返回最小值

SELECT MIN(column_name) FROM table_name;

  • SUM() - 返回总和

SELECT SUM(column_name) FROM table_name;

SQL Scalar 函数

  • UCASE() - 将某个字段转换为大写

SELECT UCASE(column_name) FROM table_name;

  • LCASE() - 将某个字段转换为小写

SELECT LCASE(column_name) FROM table_name;

  • MID() - 从某个文本字段提取字符

SELECT MID('WORK HARD', 1, 4); # return WORK

**注释:**在 MySQL 中是前闭后闭, 字符串是从第 1 位开始算

  • LEN() - 返回某个文本字段的长度

**注释:**在MySQL 中是 LENGTH(),一个汉字长度为3,一个英文长度为1

  • ROUND() - 对某个数值字段进行指定小数位数的四舍五入

SELECT ROUND(column_name,decimals) FROM table_name;

注释: ROUND 返回值被转化为一个 BIGINT!

  • NOW() - 返回当前的系统日期和时间

  • FORMAT() - 格式化某个字段的显示方式

COUNT(column_name)

SELECT COUNT(column_name) FROM table1;

返回指定列的数目,NULL 不计数

SELECT COUNT(DISTINCT column_name) FROM table1;

返回指定列不同值的数目

只需要第一条数据

SQL Server

SELECT TOP 1 column_name FROM table1 ORDER BY column_name ASC;

MySQL

SELECT column_name FROM table1 ORDER BY column_name ASC

LIMIT 1;    # LIMIT number 指定最多只返回多少条记录

Oracle

SELECT column_name FROM table1 ORDER BY column_name ASC

WHERE ROWNUM <= 1; 

只需要最后一条记录

SQL Server

SELECT TOP 1 column_name FROM table

ORDER BY column_name DESC;

MySQL

SELECT column_name FROM table1 ORDER BY column_name DESC

LIMIT 1;

Oracle

SELECT column_name FROM table1 ORDER BY column_name DESC

WHERE ROWNUM <= 1;

GROUP BY

GROUP BY 语句用于结合聚合函数,根据一个或多个列对结果集进行分组。

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value

# 相同的 column_name 的行将会被归为一行
GROUP BY column_name;

HAVING

SQL 中增加 HAVING 子句的原因是:WHERE 关键字无法与聚合函数一起使用,HAVING 子句就是用来与聚合函数一起使用的。

DATE_FORMAT()