SQL温习笔记【SQL Server】

一些重要的DDL语句

CREATE DATABASE 数据库名 - 创建新数据库
ALTER DATABASE 数据库名 - 修改数据库
CREATE TABLE 表名(列1 数据类型,列2 数据类型...) - 创建新表
ALTER TABLE - 变更数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引

注：truncate、delete和drop都是删除，但有不同：
truncate：仅删除表内的数据，不删除表本身，且不激活触发器，删除不能回滚。
delete：仅删除表内的数据，不删除表本身，激活触发器，删除可回滚。
drop：表的结构、属性、索引都将被删除，且不激活触发器，删除不能回滚。

select查询语句

从表里选取列的值：SELECT 列名称 FROM 表名称
注：列名处用星号（ * ）则选取所有列，且SQL 语句对大小写不敏感

从表的列中选取唯一、不同的值：SELECT DISTINCT 列名称 FROM 表名称

根据条件选取值：SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
注：SQL中，文本值用单引号，数值不用

        运算符表
操作符	         描述
=		         等于
<>（!=）		 不等于
>		         大于
<		         小于
>=		         大于等于
<=		         小于等于
BETWEEN		     在某个范围内
LIKE	         搜索某种模式

对结果集排序：order by （默认按升序 asc，降序用desc关键字）
示例：
先按列1的逆序（降序）排列，如列1有相同值则按列2升序再排：
select 列1,列2,列3 from 表名 order by 列1 desc, 列2 asc

选取头几条记录：SELECT TOP 数字|百分比数 列名 FROM 表名
示例：
选头50%的记录：SELECT TOP 50 PERCENT * FROM 表名

搜索列中有指定模式的记录：SELECT 列名 FROM 表名 WHERE 列名 LIKE 'pattern'
"%"用于定义通配符（模式中缺少的字母）
示例：
以N开头的模式：like 'N%'
以N结尾的模式：like '%N'
包含LON的模式：like '%LON%'

搜索不包含指定模式的记录：NOT LIKE

                                通配符表
通配符(须与like一起用)	     描述                     示例
%	                         替代一个或多个字符
_	                         仅替代一个字符
[charlist]	                 字符列中的任何单一字符    '[ABC]%'以A或B或C开头的
[^charlist]或者[!charlist]   不在字符列中的任何单一字符     

在where子句中规定多个值：SELECT 列名 FROM 表名 WHERE 列名 IN (值1,值2,...)

选取介于两个值之间的数据范围：SELECT 列名 FROM 表名 WHERE 列名 BETWEEN 值1 AND 值2
选取不介于两个值之间的数据范围：NOT BETWEEN
注：值可以是数值、文本或日期。不同的数据库对between…and的处理方式有差异，可能会列出值1、值2间但不包含值1、值2的数据，可能包含双边，也可能只包含单边

为表名指定别名：SELECT 列名 FROM 表名 AS 别名（简化SQL语句）
为列名指定别名：SELECT 列名 AS 别名 FROM 表名（改变列名的显示）

合并多个select语句的结果集：SELECT 列名 FROM 表1 UNION SELECT 列名 FROM 表2
注：union操作符会选取不同的值。若表1、表2里有A值，则结果集只会选取表1的A。
如允许重复的值出现在结果集，则用SELECT 列名 FROM 表1 UNION ALL SELECT 列名 FROM 表2
注：UNION 内部的 SELECT 语句必须拥有相同数量的列。列必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。结果集中的列名总是等于 第一个 SELECT 语句中的列名。

从当前数据库中的表选取数据插入新表中，常用于创建表的备份或者对记录进行存档（会创建新表）：SELECT 列名 INTO 新表 FROM 旧表
向其他数据库中拷贝数据：SELECT 列名 INTO 新表 IN '数据库名.mdb' FROM 旧表

insert插入语句

插入新行：INSERT INTO 表名 VALUES (值1, 值2,....)

在指定列插入新一行数据：INSERT INTO 表名 (列1, 列2,...) VALUES (值1, 值2,....)

update更新语句

修改（更新）表中的数据：UPDATE 表名 SET 列名 = 新值 WHERE 列名 = 某值

delete删除语句

删除行：DELETE FROM 表名 WHERE 列名 = 值

删除所有行的数据：DELETE FROM 表名或DELETE * FROM 表名

join连接

inner join： 只查询匹配得上筛选结果的条件。
示例：select * from 表1 inner join 表2 on 条件

join：如果表中至少有一个匹配，则返回行。（效果等于 inner join 和 where）

left join：即使右表中没有匹配，也从左表返回所有的行。（=left outer join）

right join：即使左表中没有匹配，也从右表中返回所有的行。（=right outer join）

full join：返回左、右表所有的行，即使没有匹配。（=full outer join）

表约束constraints

可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）。

约束列不接受NULL值：create table 表名（列名 数据类型 not null,...）

约束唯一标识数据库表中的每条记录：create table 表名（列名 数据类型 unique,...）
为多个列定义unique约束：create table 表名（列1...,列2..., constraint 约束名 unique()数据类型 unique,...）
为已有的列加unique约束：alter table 表名 add unique（列名）
为已有的多个列加unique约束：alter table 表名 add constraint 约束名 unique（列1,列2...）
撤销unique约束：alter table 表名 drop constraint 约束名

主键约束：唯一标识数据库表中的每条记录。主键列必须是唯一且不为空的值。每个表都应有且只能有一个主键。
创建表时创建主键约束：create table 表名(列名 数据类型 primary key,...)
为多个列定义主键约束：create table 表名(...,constraint 约束名 primary key(列1,列2,...))
为已存在的列创建主键约束：alter table 表名 add primary key(列名)
为已存在的多个列创建主键约束：alter table 表名 add constraint 约束名 primary key(列1,列2,...)

注：如果用 alter table语句添加主键，须把主键列声明为不含 NULL 值（在表首次创建时）。

数据类型

Character 字符串：

数据类型	              描述	                                   存储
char(n)   	    固定长度的字符串。最多 8,000 个字符。	            n
varchar(n)	    可变长度的字符串。最多 8,000 个字符。	 
varchar(max)	可变长度的字符串。最多 1,073,741,824 个字符。	 
text	        可变长度的字符串。最多 2GB 字符数据。

Unicode 字符串：

数据类型	              描述	
nchar(n)	    固定长度的 Unicode 数据。最多 4,000 个字符。	 
nvarchar(n)	    可变长度的 Unicode 数据。最多 4,000 个字符。	 
nvarchar(max)	可变长度的 Unicode 数据。最多 536,870,912 个字符。	 
ntext	        可变长度的 Unicode 数据。最多 2GB 字符数据。

Binary 类型：

数据类型         	  描述	
bit	            允许 0、1 或 NULL	 
binary(n)	    固定长度的二进制数据。最多 8,000 字节。	 
varbinary(n)	可变长度的二进制数据。最多 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参数指示可以存储的最大位数（小数点左侧和右侧）。  5-17
                p必须是1到38之间的值。默认是18。s参数指示小数点右侧     字节
                存储的最大位数。s必须是0到p之间的值。默认是 0。          


numeric(p,s)    固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 
                之间的数字。p参数指示可以存储的最大位数（小数点左侧     5-17
                和右侧）。p必须是1到38之间的值。默认是18。s参数指示     字节
                小数点右侧存储的最大位数。s必须是0到p之间的值。默认是 0。
                
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字节，而     4 或 8
                float(53) 保存 8 字节。n 的默认值是 53。	            字节

real	        从-3.40E+38到3.40E+38的浮动精度数字数据。	            4 字节

Date 类型：

数据类型	              描述	                                        存储
datetime	    从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，       8 bytes
                精度为 3.33 毫秒。	

datetime2	    从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，     6-8 bytes
                精度为 100 纳秒。	                                

smalldatetime	从 1900 年 1 月 1 日 到 2079 年 6 月 6 日，         4 bytes
                精度为 1 分钟。	

date	        从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。       3 bytes
                仅存储日期。

time	        仅存储时间。精度为 100 纳秒。	                    3-5 bytes

datetimeoffset	与 datetime2 相同，外加时区偏移。	               8-10 bytes

timestamp	    存储唯一的数字，每当创建或修改某行时，该数字会更新。
                timestamp 基于内部时钟，不对应真实时间。每个表只能
                有一个 timestamp 变量。	

其他数据类型：

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