SQL整理笔记

	描述
'%a'	以a为结尾的数据
'a%'	以a开头的数据
'%a%'	含有a的数据
'_a_'	三位且中间字母是a的
'_a'	两位且结尾字母是a的
'a_'	两位且开头字母是a的

 

SQL 通配符

通配符	描述
%	替代 0 个或多个字符
_	替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或 [!charlist]	不在字符列中的任何单一字符

实例1：选取url以字母“https”开始的：

select * from webs where url like ‘https%’

实例2：选取url包含模式“oo”的所有网站：

select * from webs where url like ‘%oo%’

实例3：选取name以任意一个字符开始，然后是“oogle”的所有客户：

select * from webs where name like ‘_oogle’

实例4：选取name以G开始，然后是一个任意字符，然后是o，然后是任意一个字符，然后是le的所有网站。

select * from webs where name like ‘G_o_le’

使用SQL [charlist] 通配符

1、选取name以G、F或s开始的

select * from webs where name REGEXP '^[GFs]'

2、选取name以A到H字母开头的

select * from webs where name REGEXP '^[A-H]'

3、选取name不以A到H字母开头的

select * from webs where name REGEXP '^[^A-H]'

 

SQL 链接（JOIN）

INNER JOIN：如果表中有至少一个匹配，则返回行

LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行

RIGHT JOIN：即使左表中没有匹配，也从右表中返回所有的行

FULL JOIN：只要其中一个表存在匹配，则返回行

SQL 约束

NOT NULL - 指示某列不能存储NULL值。

UNIQUE - 保证某列的每行必须有唯一的值。

PRIMARY KEY - NOT NULL 和 UNIQUE的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

FOREIGN KEY 保证一个表中的数据匹配另一个表中的值得参照完整性。

CHECK - 保证列中的值符合指定的条件。

DEFAULT - 规定没有给列赋值时的默认值。

 

SQL Server Date 函数

函数	描述
GETDATE()	返回当前的日期和时间
DATEPART()	返回日期/时间的单独部分
DATEADD()	在日期中添加或减去指定的时间间隔
DATEDIFF()	返回两个日期之间的时间
CONVERT()	用不同的格式显示日期/时间

 

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 Server 数据类型

String类型

数据类型	描述	存储
char(n)	固定长度的字符串。最多 8,000 个字符。	Defined width
varchar(n)	可变长度的字符串。最多 8,000 个字符。	2 bytes + number of chars
varchar(max)	可变长度的字符串。最多 1,073,741,824 个字符。	2 bytes + number of chars
text	可变长度的字符串。最多 2GB 文本数据。	4 bytes + number of chars
nchar	固定长度的 Unicode 字符串。最多 4,000 个字符。	Defined width x 2
nvarchar	可变长度的 Unicode 字符串。最多 4,000 个字符。
nvarchar(max)	可变长度的 Unicode 字符串。最多 536,870,912 个字符。
ntext	可变长度的 Unicode 字符串。最多 2GB 文本数据。
bit	允许 0、1 或 NULL
binary(n)	固定长度的二进制字符串。最多 8,000 字节。
varbinary	可变长度的二进制字符串。最多 8,000 字节。
varbinary(max)	可变长度的二进制字符串。最多 2GB。
image	可变长度的二进制字符串。最多 2GB。

Number类型

数据类型	描述	存储
tinyint	允许从 0 到 255 的所有数字。	1 字节
smallint	允许介于 -32,768 与 32,767 的所有数字。	2 字节
int	允许介于 -2,147,483,648 与 2,147,483,647 的所有数字。	4 字节
bigint	允许介于 -9,223,372,036,854,775,808 与 9,223,372,036,854,775,807 之间的所有数字。	8 字节
decimal(p,s)	固定精度和比例的数字。 允许从 -10^38 +1 到 10^38 -1 之间的数字。 p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。	5-17 字节
numeric(p,s)	固定精度和比例的数字。 允许从 -10^38 +1 到 10^38 -1 之间的数字。 p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。	5-17 字节
smallmoney	介于 -214,748.3648 与 214,748.3647 之间的货币数据。	4 字节
money	介于 -922,337,203,685,477.5808 与 922,337,203,685,477.5807 之间的货币数据。	8 字节
float(n)	从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。 n 参数指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节，而 float(53) 保存 8 字节。n 的默认值是 53。	4 或 8 字节
real	从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据。	4 字节

Date类型

数据类型	描述	存储
datetime	从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 3.33 毫秒。	8 字节
datetime2	从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 100 纳秒。	6-8 字节
smalldatetime	从 1900 年 1 月 1 日 到 2079 年 6 月 6 日，精度为 1 分钟。	4 字节
date	仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。	3 bytes
time	仅存储时间。精度为 100 纳秒。	3-5 字节
datetimeoffset	与 datetime2 相同，外加时区偏移。	8-10 字节
timestamp	存储唯一的数字，每当创建或修改某行时，该数字会更新。timestamp 值基于内部时钟，不对应真实时间。每个表只能有一个 timestamp 变量。

其他数据类型

数据类型	描述
sql_variant	存储最多 8,000 字节不同数据类型的数据，除了 text、ntext 以及 timestamp。
uniqueidentifier	存储全局唯一标识符 (GUID)。
xml	存储 XML 格式化数据。最多 2GB。
cursor	存储对用于数据库操作的指针的引用。
table	存储结果集，供稍后处理。

 

SQL Aggregate 函数

SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。

AVG() - 返回平均值

COUNT() - 返回行数

FIRST() - 返回第一个记录的值（只有 MS Access支持）

LAST() - 返回最后一个记录的值（只有 MS Access支持）

MAX() - 返回最大值

MIN() - 返回最小值

SUN() - 返回总和

 

SQL Scalar 函数

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

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

MID() - 从某个文本字段提取字符，MySql中使用

SubString（字段，1，end） - 从某个文本字段提取字符

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

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

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

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

 

快速参考

SQL 语句	语法
AND / OR	SELECT column_name(s) FROM table_name WHERE condition AND|OR condition
ALTER TABLE	ALTER TABLE table_name  ADD column_name datatype or ALTER TABLE table_name  DROP COLUMN column_name
AS (alias)	SELECT column_name AS column_alias FROM table_name or SELECT column_name FROM table_name AS table_alias
BETWEEN	SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
CREATE DATABASE	CREATE DATABASE database_name
CREATE TABLE	CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name2 data_type, ... )
CREATE INDEX	CREATE INDEX index_name ON table_name (column_name) or CREATE UNIQUE INDEX index_name ON table_name (column_name)
CREATE VIEW	CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
DELETE	DELETE FROM table_name WHERE some_column=some_value or DELETE FROM table_name  (Note: Deletes the entire table!!) DELETE * FROM table_name  (Note: Deletes the entire table!!)
DROP DATABASE	DROP DATABASE database_name
DROP INDEX	DROP INDEX table_name.index_name (SQL Server) DROP INDEX index_name ON table_name (MS Access) DROP INDEX index_name (DB2/Oracle) ALTER TABLE table_name DROP INDEX index_name (MySQL)
DROP TABLE	DROP TABLE table_name
GROUP BY	SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
HAVING	SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value
IN	SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..)
INSERT INTO	INSERT INTO table_name VALUES (value1, value2, value3,....) or INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,....)
INNER JOIN	SELECT column_name(s) FROM table_name1 INNER JOIN table_name2  ON table_name1.column_name=table_name2.column_name
LEFT JOIN	SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2  ON table_name1.column_name=table_name2.column_name
RIGHT JOIN	SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2  ON table_name1.column_name=table_name2.column_name
FULL JOIN	SELECT column_name(s) FROM table_name1 FULL JOIN table_name2  ON table_name1.column_name=table_name2.column_name
LIKE	SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
ORDER BY	SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC]
SELECT	SELECT column_name(s) FROM table_name
SELECT *	SELECT * FROM table_name
SELECT DISTINCT	SELECT DISTINCT column_name(s) FROM table_name
SELECT INTO	SELECT * INTO new_table_name [IN externaldatabase] FROM old_table_name or SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_table_name
SELECT TOP	SELECT TOP number|percent column_name(s) FROM table_name
TRUNCATE TABLE	TRUNCATE TABLE table_name
UNION	SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2
UNION ALL	SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2
UPDATE	UPDATE table_name SET column1=value, column2=value,... WHERE some_column=some_value
WHERE	SELECT column_name(s) FROM table_name WHERE column_name operator value