T-SQL语句

一、基础

1、创建数据库

（1）定义　

create database database_name

[ on

[primary] [<filespec> [,...n] ]

]

[ log on

[<filespec>[,...n]]

];

<filespec>::=

(

name=logical_file_name

[ , newname = new_login_name ]

[ , fileName = {'os_file_name' | 'fileStream_path'} ]

[ , size = size[ KB | MB | GB | TB] ]

[ , MaxSize = {max_size [ KB | MB |GB |TB] | UNLIMITED} ]

[ , FILEGROWTH = growth_increment [ KB | MB |GB | TB | %] ]

);

database_name：数据库名称，不能与SQL SERVER中现有的数据库实例名称相冲突,最多可包含128个字符。
ON：显示定义用来存储数据库中的数据的磁盘文件。
PRIMARY：关联的<filespec>列表定义的主文件，在主文件组<filespec>项中指定第一个文件将生成主文件，一个数据库只能有一个主文件。如果没有指定primary，那么create datebase 语句中列出的第一个文件将成为主文件。
LOG ON：用来存储数据库日志的日志文件。LOG ON后跟以逗号分隔的用以定义日志文件的<filespec>列表。如果没有指定log on，将自动创建一个日志文件，其大小为该数据库的所有文件大小总和的25%或521KB，取两者之中最大者。
name：文件的逻辑名称。指定filename时，需要使用name，除非指定 FOR ATTCH 子句之一。无法将filename文件组命名为primary。
filename：创建文件时由操作系统使用的路径和文件名。执行create datebase 语句前，指定路径必须存在。
size：数据库文件的初始大小，如果没有为主文件提供size，数据库引擎使用model数据库中主文件的大小。
max_size：指定文件可增大的最大大小。可使用KB、MB、GB和TB做后缀，默认值为MB。max_size是整数值.如果不指定max_size,则文件将不断增长直至磁盘被占满。UNLIMITED表示文件一直增长到磁盘装满。
filegrowth：文件的自动增量。文件的filegrowth设置不能超过MAXSIZE设置。该值可以 MB、KB、GB、TB或百分比（%）为单位指定，默认值为MB，如果指定%，则增量大小为发生增长时文件大小的的指定百分比。值为0表明自动增长被设为关闭，不允许增加空间。

（2）示例

创建一个数据库sample_db，该数据库的主数据文件逻辑名为sample_db，物理文件名称为sample_db.mdf，初始大小为5MB，最大尺寸为30MB，增长速度为5%；数据库日志文件的逻辑名称为sample_log，保存日志文件的物理名称为sample_log.ldf，初始大小为1MB，最大尺寸为8MB，增长速度为128KB。

create database sample_db
on  primary  -- 默认就属于primary文件组,可省略
(
　　/*--数据文件的具体描述--*/
    name='sample_db',  -- 主数据文件的逻辑名称
    filename='D:\sample_db.mdf', -- 主数据文件的物理名称
    size=5mb, --主数据文件的初始大小
    maxsize=30mb, -- 主数据文件增长的最大值
    filegrowth=5%--主数据文件的增长率
)
log on
(
　　/*--日志文件的具体描述,各参数含义同上--*/
    name='sample_log',
    filename='D:\sample_log.ldf',
    size=1mb,
　　 maxsize=8mb,
    filegrowth=10%
)

2、修改数据库

使用alter database语句进行修改。alter database 语句可以进行以下的修改：

增加或删除数据文件
改变数据文件或日志文件的大小和增长方式
增加或者删除日志文件和文件组

（1）基本语法

alter database database_name
{
   modify name=new_database_name
   | Add file<filespec> [ ,...n ] [ TO filegroup {  filegroup_name } ]
   | Add log file <filespec> [ ,...n ] 
   | remove file logical_file_name
   |modify file <filespec>
}
  <filespec>::=
   (
     name=logical_file_name
     [  ,  newname = new_login_name ]
     [  ,  fileName = {'os_file_name' | 'filestream_path'} ]
     [  ,  size = size[ KB | MB | GB | TB]  ] 
     [  ,  MaxSize  = {max_size [ KB | MB |GB |TB] | UNLIMITED}  ] 
     [  ,  FILEGROWTH  = growth_increment [ KB  | MB  |GB  | TB  | %]    ] 
     [    ,     offline ]
);

database_name：要修改的数据库的名称
modify name：指定新的数据库名称
Add file：向数据库中添加文件
to filegroup{filegroup_name}：将指定文件添加到文件组。filegroup_name为文件组名称
Add log file：将要添加的日志文件添加到指定的数据库
remove file logical_file_name：从SQL Server的实例中删除逻辑文件并删除物理文件。除非文件为空，否则无法删除文件。logical_file_name是在Sql Server 中引用文件时所用的逻辑名称
modify file：指定应修改的文件，一次只能更改一个<filespec>属性。必须在<filespec>中指定name,以标识要修改的文件。如果指定了size，那么新大小必须比文件当前大小要大

（2）示例

将sample_db数据库中的主数据文件的初始大小修改为15MB

alter database sample_db
  modify file
   (
    name='sample_db',
    size=15MB
  );

注意：修改数据库文件的初始大小时，指定的size的大小必须大于或等于当前大小，如果小于，代码不能执行。

增加sample_db数据库的容量

alter database sample_db
   modify file
    (
     name='sample_db',
     maxsize=50MB 
    );
  go

缩减 sample_db数据库的容量

alter database sample_db
  modify file
   ( 
     name ='sample_db',
     MAXSIZE=15MB
   );
  go

3、删除数据库　　　　

drop database database_name[  , ...n ]

（1）示例

use master -- 设置当前数据库为master,以便访问sysdatabases表
go
if exists(select * from sysdatabases where name='sample_db')
drop database sample_db
go

（2）注意

删除数据库时一定要慎重，因为系统无法轻易恢复被删除的数据，除非做过数据库的备份。另外千万不能删除系统数据库，否则会导致sql server服务器无法启动。
- master：数据库服务器的核心
- model：创建数据库的模版
- msdb：提供运行SQL Server Agent工作的信息
- tempdb：Sql Server中的一个临时数据库，用于存放临时对象和中间结果，Sql server关闭后，该数据库中的内容被清空，每次重新启动服务器后tempdb数据库将被重建.

并不是所有的数据库在任何时候都可以被删除，只有处于正常状态下的数据库，才能使用drop语句删除。当数据库处于以下状态时不能删除：
- 数据库正在使用
- 数据库正在恢复
- 数据库包含用于复制的对象。

4、备份数据库

--- 创建 备份数据的 device

use master
exec sp_addumpdevice 'disk','testBack', 'c:\mssql7backup\MyNwind_1.dat'

--- 开始 备份
BACKUP DATABASE pubs TO testBack

5、创建数据表

create table tablename(col1 type1 [not null] [primary key],col2 type2 [not null],..)

示例

use sample_db
go 
if exists(select * from sysobjects where name='sample_db')
　　drop table sample_db
go
create table sample_db
(
    ExamNo      int     identity(1,1) primary key,
    stuNo       char(6) not null,
    writtenExam int     not null,
    LabExam     int     not null
)
go

6、修改数据表结构

增加列

ALTER TABLE products ADD COLUMN description text；新增的字段对于表中已经存在的行而言最初将先填充所给出的缺省值（如果你没有声明DEFAULT子句，那么缺省是空值）。

增加列（含约束）

ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

删除列

alter table 表名 drop column 列名

删除列（含外键）

ALTER TABLE 表名 DROP COLUMN 列名 CASCADE

删除列（含约束）

alter table 表名 drop constraint 约束名

alter table 表名 drop column 列名

增加约束

ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

删除约束

ALTER TABLE products DROP CONSTRAINT some_name

删除约束（含依赖）

ALTER TABLE products DROP CONSTRAINT some_name CASCADE

表注释

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'表注释' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=表名'

增加字段注释

execute sp_addextendedproperty 'MS_Description','字段注释','user','dbo','table','表名','column','字段名';

修改字段注释

execute sp_updateextendedproperty 'MS_Description','字段注释 ','user','dbo','table','表名','column','字段名';

删除字段注释

execute sp_dropextendedproperty 'MS_Description','user','dbo','table','表名','column','字段名';

7、SQL约束

SQL 约束用于规定表中的数据规则。如果存在违反约束的数据行为，行为会被约束终止。约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。在 SQL 中，我们有如下约束：

NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。
DEFAULT - 规定没有给列赋值时的默认值。

增加约束

alter table 表名 add constraint 约束名称 增加的约束类型 （列名）

删除约束

ALTER TABLE 表名 DROP CONSTRAINT 约束名  --除not null约束外

8、SQL NOT NULL 约束

NOT NULL 约束强制列不接受 NULL 值。NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

（1）通过创建数据表添加not null 约束

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

（2）通过修改数据表添加not null 约束

ALTER TABLE Persons MODIFY Age int NOT NULL;

（3）删除not null 约束

ALTER TABLE Persons MODIFY Age int NULL;

9、SQL UNIQUE 约束

UNIQUE 约束唯一标识数据库表中的每条记录。UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

（1）CREATE TABLE 时的 SQL UNIQUE 约束

CREATE TABLE Persons
(
　　P_Id int NOT NULL UNIQUE,
　　LastName varchar(255) NOT NULL,
　　FirstName varchar(255),
　　Address varchar(255),
　　City varchar(255)
)

（2）ALTER TABLE 时的 SQL UNIQUE 约束

ALTER TABLE Persons ADD UNIQUE (P_Id)

如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：

ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

（3）撤销 UNIQUE 约束

ALTER TABLE Persons DROP CONSTRAINT uc_PersonID

10、SQL PRIMARY KEY 约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。主键必须包含唯一的值。主键列不能包含 NULL 值。每个表都应该有一个主键，并且每个表只能有一个主键。

（1）CREATE TABLE 时的 SQL PRIMARY KEY 约束

CREATE TABLE Persons
(
　　P_Id int NOT NULL PRIMARY KEY,
　　LastName varchar(255) NOT NULL,
　　FirstName varchar(255),
　　Address varchar(255),
　　City varchar(255)
)

如需命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法：

CREATE TABLE Persons
(
　　P_Id int NOT NULL,
　　LastName varchar(255) NOT NULL,
　　FirstName varchar(255),
　　Address varchar(255),
　　City varchar(255),
　　CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

（2）ALTER TABLE 时的 SQL PRIMARY KEY 约束

ALTER TABLE Persons ADD PRIMARY KEY (P_Id)

如需命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法：

ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

（3）撤销 PRIMARY KEY 约束

ALTER TABLE Persons DROP CONSTRAINT pk_PersonID

撤销PRIMARY KEY约束时，不论约束条件为一列还是多列，对于MySQL，撤销都是

ALTER TABLE Persons DROP PRIMARY KEY

但对于 SQL Server / Oracle / MS Access，一个列

ALTER TABLE Persons DROP CONSTRAINT P_Id

若起约束名，也可如下多个列

ALTER TABLE Persons DROP CONSTRAINT pk_PersonID

11、SQL FOREIGN KEY 约束

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。

（1）CREATE TABLE 时的 SQL FOREIGN KEY 约束

CREATE TABLE Orders
(
　　O_Id int NOT NULL PRIMARY KEY,
　　OrderNo int NOT NULL,
　　P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束，请使用下面的 SQL 语法：

CREATE TABLE Orders
(
　　O_Id int NOT NULL,
　　OrderNo int NOT NULL,
　　P_Id int,
　　PRIMARY KEY (O_Id),
　　CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)　REFERENCES Persons(P_Id)
)

（2）ALTER TABLE 时的 SQL FOREIGN KEY 约束

ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束，请使用下面的 SQL 语法：

ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

（3）撤销 FOREIGN KEY 约束

ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders

12、SQL CHECK 约束

CHECK 约束用于限制列中的值的范围。如果对单个列定义 CHECK 约束，那么该列只允许特定的值。如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。

（1）CREATE TABLE 时的 SQL CHECK 约束

CREATE TABLE Persons
(
　　P_Id int NOT NULL CHECK (P_Id>0),
　　LastName varchar(255) NOT NULL,
　　FirstName varchar(255),
　　Address varchar(255),
　　City varchar(255)
)

如需命名 CHECK 约束，并定义多个列的 CHECK 约束，请使用下面的 SQL 语法：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

（2）ALTER TABLE 时的 SQL CHECK 约束

ALTER TABLE Persons ADD CHECK (P_Id>0)

如需命名 CHECK 约束，并定义多个列的 CHECK 约束，请使用下面的 SQL 语法：

ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

（3）撤销 CHECK 约束

ALTER TABLE Persons DROP CONSTRAINT chk_Person

13、SQL DEFAULT 约束

DEFAULT 约束用于向列中插入默认值。如果没有规定其他的值，那么会将默认值添加到所有的新记录。

（1）CREATE TABLE 时的 SQL DEFAULT 约束

CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) DEFAULT 'Sandnes'
)

通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值：

CREATE TABLE Orders
(
    O_Id int NOT NULL,
    OrderNo int NOT NULL,
    P_Id int,
    OrderDate date DEFAULT GETDATE()
)

（2）ALTER TABLE 时的 SQL DEFAULT 约束

ALTER TABLE Persons ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for City

（3）撤销 DEFAULT 约束

ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT

14、DISTINCT

DISTINCT 关键词用于返回唯一不同的值。但是Distinct只能放在字段的开头，如果多个字段，将不会去重。

SELECT DISTINCT column_name,column_name FROM table_name;

15、SQL ORDER BY 关键字

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。

SELECT column_name,column_name FROM table_name ORDER BY column_name,column_name ASC|DESC;

16、Where

WHERE 子句用于提取那些满足指定条件的记录。

特殊条件

空值判断： is null

Select * from emp where comm is null;

查询 emp 表中 comm 列中的空值。

between and (在之间的值)

Select * from emp where sal between 1500 and 3000;

查询 emp 表中 SAL 列中大于 1500 的小于 3000 的值。注意：大于等于 1500 且小于等于 3000， 1500 为下限，3000 为上限，下限在前，上限在后，查询的范围包涵有上下限的值。

Select * from emp where ename like 'M%';

查询 EMP 表中 Ename 列中有 M 的值，M 为要查询内容中的模糊信息。 % 表示多个字值，_ 下划线表示一个字符；

%M_ : 表示查询以M在倒数第二位的所有内容。

17、INSERT INTO

INSERT INTO 语句可以有两种编写形式。

第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：

INSERT INTO table_name VALUES (value1,value2,value3,...);

第二种形式需要指定列名及被插入的值：

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

插入多行记录：

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...)，（value1,value2,value3,...）,（value1,value2,value3,...）...;

另外还有以下插入方式：

create table tab_new like tab_old (使用旧表创建新表)
create table tab_new as select col1,col2… from tab_old definition only

18、UPDATE

UPDATE语句用于更新表中已存在的记录。

UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;

19、DELETE

DELETE 语句用于删除表中的行。

DELETE FROM table_name WHERE some_column=some_value;

20、SELECT TOP 子句

SELECT TOP 用于规定要返回的记录的数目。在SQLServer 中还可以使用百分比作为参数。下面的 SQL 语句从 websites 表中选取前面百分之 50 的记录：

SELECT TOP 50 PERCENT * FROM Websites;

21、LIKE 操作符

LIKE 操作符用于在 WHERE 中搜索列中的指定模式。通过使用 NOT 关键字，您可以选取不匹配模式的记录。下面的 SQL 语句选取 name 不包含模式 "oo" 的所有客户：

SELECT * FROM Websites WHERE name NOT LIKE '%oo%';

SQL 通配符

下面的 SQL 语句选取 name 以一个任意字符开始，然后是 "oogle" 的所有客户：

SELECT * FROM Websites WHERE name LIKE '_oogle';

22、BETWEEN 操作符

BETWEEN 操作符用于选取介于两个值之间的数据范围内的值。如需显示不在上面实例范围内的网站，请使用 NOT BETWEEN：

SELECT * FROM Websites WHERE alexa NOT BETWEEN 1 AND 20;

请注意，在不同的数据库中，BETWEEN 操作符会产生不同的结果！
在某些数据库中，BETWEEN 选取介于两个值之间但不包括两个测试值的字段。
在某些数据库中，BETWEEN 选取介于两个值之间且包括两个测试值的字段。
在某些数据库中，BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。

在sqlserver中between包含两个临界值。

23、SQL 别名

通过使用 SQL，可以为表名称或列名称指定别名。基本上，创建别名是为了让列名称的可读性更强。

列的 SQL 别名语法

SELECT column_name AS alias_name
FROM table_name;

表的 SQL 别名语法

SELECT column_name(s)
FROM table_name AS alias_name;

24、连接(JOIN)

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

INNER JOIN：如果表中有至少一个匹配，则返回行
LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
FULL JOIN：只要其中一个表中存在匹配，则返回行

25、INNER JOIN 关键字

INNER JOIN 关键字在表中存在至少一个匹配时返回行。SQL INNER JOIN 语法

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

或：

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

注释：INNER JOIN 与 JOIN 是相同的。

关键字 on

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用 left jion 时，on 和 where 条件的区别如下：

on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录
where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

26、LEFT JOIN

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

SQL LEFT JOIN 语法：

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

或：

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

注释：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN

27、RIGHT JOIN

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

SQL RIGHT JOIN 语法

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

或：

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

注释：在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。

28、FULL OUTER JOIN

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

SQL FULL OUTER JOIN 语法：

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

29、UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

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

SQL UNION ALL 语法

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

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

30、SELECT INTO 语句

通过 SQL，您可以从一个表复制信息到另一个表。SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。注意：MySQL 数据库不支持 SELECT ... INTO 语句，但支持 INSERT INTO ... SELECT

（1）语法

可以复制所有的列插入到新表中：

SELECT *
INTO newtable [IN externaldb]
FROM table1;

或者只复制希望的列插入到新表中：

SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

（2）示例

创建 Websites 的备份复件：

SELECT * INTO WebsitesBackup2016 FROM Websites;

只复制一些列插入到新表中：

SELECT name, url INTO WebsitesBackup2016 FROM Websites;

只复制中国的网站插入到新表中：

SELECT * INTO WebsitesBackup2016 FROM Websites WHERE country='CN';

复制多个表中的数据插入到新表中：

SELECT Websites.name, access_log.count, access_log.date
INTO WebsitesBackup2016 FROM Websites LEFT JOIN access_log ON Websites.id=access_log.site_id;

提示：SELECT INTO 语句可用于通过另一种模式创建一个新的空表。只需要添加促使查询没有数据返回的 WHERE 子句即可：

SELECT * INTO newtable FROM table1 WHERE 1=0;

31、INSERT INTO SELECT 语句

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。

（1）SQL INSERT INTO SELECT 语法

我们可以从一个表中复制所有的列插入到另一个已存在的表中：

INSERT INTO table2 SELECT * FROM table1;

或者我们可以只复制希望的列插入到另一个已存在的表中：

INSERT INTO table2 (column_name(s)) SELECT column_name(s) FROM table1;

（2）select into from 和 insert into select

两者都是用来复制表，主要区别为： select into from 要求目标表不存在，因为在插入时会自动创建；insert into select from 要求目标表存在。

如果两个表结构一样：

insert into table_name_new select * from table_name_old

如果两个表结构不一样：

insert into table_name_new(column1,column2...) select column1,column2... from table_name_old

32、创建删除索引

创建索引

create [unique] index idxname on tabname(col….)

删除索引

drop index idxname

注：索引是不可更改的，想更改必须删除重新建。下面的 SQL 语句在 "Persons" 表的 "LastName" 列上创建一个名为 "PIndex" 的索引：

CREATE INDEX PIndex ON Persons (LastName)

如果您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：

CREATE INDEX PIndex ON Persons (LastName, FirstName)

33、DROP 语句

通过使用 DROP 语句，可以轻松地删除索引、表和数据库。

DROP INDEX table_name.index_name
DROP TABLE table_name
DROP DATABASE database_name

如果我们仅仅需要删除表内的数据，但并不删除表本身，请使用 TRUNCATE TABLE 语句：

TRUNCATE TABLE table_name

34、ALTER TABLE

ALTER TABLE 语句用于在已有的表中添加、删除或修改列。

如需在表中添加列，请使用下面的语法:

ALTER TABLE table_name ADD column_name datatype

如需删除表中的列，请使用下面的语法（请注意，某些数据库系统不允许这种在数据库表中删除列的方式）：

ALTER TABLE table_name DROP COLUMN column_name

要改变表中列的数据类型，请使用下面的语法：

ALTER TABLE table_name ALTER COLUMN column_name datatype

35、AUTO INCREMENT 字段

用于 SQL Server 的语法，下面的 SQL 语句把 "Persons" 表中的 "ID" 列定义为 auto-increment 主键字段：

CREATE TABLE Persons
(
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

MS SQL Server 使用 IDENTITY 关键字来执行 auto-increment 任务。在上面的实例中，IDENTITY 的开始值是 1，每条新记录递增 1。

提示：要规定 "ID" 列以 10 起始且递增 5，请把 identity 改为 IDENTITY(10,5)。

给已经存在的colume添加自增语法：

ALTER TABLE table_name CHANGE column_name column_name data_type(size) constraint_name AUTO_INCREMENT;

比如：

ALTER TABLE student CHANGE id id INT( 11 ) NOT NULL AUTO_INCREMENT;

36、创建删除视图

视图的作用：

视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。
视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限）
从而加强了安全性，使用户只能看到视图所显示的数据。
视图还可以被嵌套，一个视图中可以嵌套另一个视图。

创建视图

create view viewname as select statement

删除视图

drop view viewname

注释：视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据。

37、SQL Date 函数

SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：

DATE - 格式：YYYY-MM-DD
DATETIME - 格式：YYYY-MM-DD HH:MM:SS
SMALLDATETIME - 格式：YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式：唯一的数字

38、SQL NULL 值

数据库建表的时候默认是 NULL，但在工作中一般建表的时候都会禁止使用 NULL 的！NULL 表示的是什么都没有，它与空字符串、0 这些是不等价的，是不能用于比较的！如： <expr> = NULL 、 NULL = '' 得到的结果为 false，判断 NULL 必须使用 IS NULL 或 IS NOT NULL 进行判断。

为什么工作中不使用 NULL？

不利于代码的可读性和可维护性，特别是强类型语言，查询 INT 值，结果得到一个 NULL，程序可能会奔溃...如果要兼容这些情况程序往往需要多做很多操作来兜底
若所在列存在 NULL 值，会影响 count()、 <col> != <value>、 NULL + 1 等查询、统计、运算情景的结果

39、SQL Server 数据类型

40、SQL 函数

（1）SQL Aggregate 函数

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

AVG() - 返回平均值
COUNT() - 返回行数
FIRST() - 返回第一个记录的值
LAST() - 返回最后一个记录的值
MAX() - 返回最大值
MIN() - 返回最小值
SUM() - 返回总和

（2）SQL Scalar 函数

SQL Scalar 函数基于输入值，返回一个单一的值。有用的 Scalar 函数：

UCASE() - 将某个字段转换为大写
LCASE() - 将某个字段转换为小写
MID() - 从某个文本字段提取字符，MySql 中使用
SubString(字段，1，end) - 从某个文本字段提取字符
LEN() - 返回某个文本字段的长度
ROUND() - 对某个数值字段进行指定小数位数的四舍五入
NOW() - 返回当前的系统日期和时间
FORMAT() - 格式化某个字段的显示方式

二、扩展

1、几个简单的基本的sql语句

选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2),(),()
删除：delete from table1 where 范围
更新：update table1 set field1=value1,field2=value2 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [asc/desc]
总数：select count(0) as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1

2、几个高级查询运算词

注：使用运算词的几个查询结果行必须是一致的。

UNION 运算符

　　UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

EXCEPT 运算符

　　EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

INTERSECT 运算符

　　INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。

3、drop、truncate和delete的区别

（1）Drop的用法

　　删除表中的数据和定义，释放空间，最暴力不保留任何东西。

（2）Delete的用法

　　删除表中数据不删除定义，不释放空间。

只是删除表中某些数据，表结构还在.
Delete 可以带where子句来删除一部分数据
自动编号不恢复到初始值。

（3）Truncate的用法

　　删除表中数据、释放空间但不删除定义（不能单独删除某一行数据）。

truncate语句不能跟where条件，无法根据条件来删除，只能全部删除数据。
自动编号恢复到初始值。
使用truncate删除表中所有数据要比delete效率高的多，因为truncate操作采用按最小方式来记录日志.
truncate删除数据，不触发delete触发器。

总结：

在速度上drop> truncate > delete
在使用drop和truncate时一定要注意，删除后不能恢复
如果想删除部分数据用delete，注意带上where子句，回滚段要足够大
如果想删除表，当然用drop
如果想保留表而将所有数据删除，如果和事务无关，用truncate即可
如果和事务有关，或者想触发trigger，还是用delete；
如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

三、提升

1、仅复制表结构

法一：select * into b from a where 1<>1
法二：select top 0 * into b from a

2、拷贝表

insert into b(a, b, c) select d,e,f from b;

3、跨数据库之间表的拷贝(具体数据使用绝对路径)

insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..

4、显示文章、提交人和最后回复时间

select a.title,a.username,b.adddate from table a,(select max(adddate) as adddate from table where table.title=a.title) b

5、日程安排提前五分钟提醒

select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

6、包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表

(select a from tableA ) except (select a from tableB) except (select a from tableC)

7、随机取出10条数据

select top 10 * from tablename order by newid()

8、删除重复记录

Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

9、列出数据库里所有的表名

select name from sysobjects where type='U'

10、列出表里的所有的列

select name from syscolumns where id=object_id('TableName')

11、列是type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。

select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type

显示结果：

type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3

12、初始化表table1

TRUNCATE TABLE table1

13、选择从10到15的记录

select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

四、技巧

1、1=1，1=2的使用

在SQL语句组合时用的较多，“where 1=1” 是表示选择全部 “where 1=2”全部不选。

2、压缩数据库

dbcc shrinkdatabase(dbname)

3、SQL SERVER中直接循环写入数据

declare @i int
set @i=1
while @i<30
begin
   insert into test (userid) values(@i)
   set @i=@i+1
end

posted @ 2016-07-22 15:59 搬砖滴阅读(1175) 评论(0) 编辑收藏举报

刷新页面返回顶部

搬砖滴

T-SQL语句

一、基础

1、创建数据库

（1）定义

（2）示例

2、修改数据库

（1）基本语法

（2）示例

3、删除数据库

（1）示例

（2）注意

4、备份数据库

5、创建数据表

6、修改数据表结构

7、SQL约束

8、SQL NOT NULL 约束

9、SQL UNIQUE 约束

10、SQL PRIMARY KEY 约束

11、SQL FOREIGN KEY 约束

12、SQL CHECK 约束

13、SQL DEFAULT 约束

14、DISTINCT

15、SQL ORDER BY 关键字

16、Where

特殊条件

between and (在 之间的值)

like

17、INSERT INTO

18、UPDATE

19、DELETE

20、SELECT TOP 子句

21、LIKE 操作符

SQL 通配符

22、BETWEEN 操作符

23、SQL 别名

24、连接(JOIN)

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

25、INNER JOIN 关键字

关键字 on

26、LEFT JOIN

27、RIGHT JOIN

28、FULL OUTER JOIN

29、UNION 操作符

SQL UNION 语法

SQL UNION ALL 语法

30、SELECT INTO 语句

（1）语法

（2）示例

31、INSERT INTO SELECT 语句

（1）SQL INSERT INTO SELECT 语法

（2）select into from 和 insert into select

32、创建删除索引

33、DROP 语句

34、ALTER TABLE

35、AUTO INCREMENT 字段

36、创建删除视图

37、SQL Date 函数

38、SQL NULL 值

39、SQL Server 数据类型

40、SQL 函数

（1）SQL Aggregate 函数

（2）SQL Scalar 函数

二、扩展

1、几个简单的基本的sql语句

2、几个高级查询运算词

3、drop、truncate和delete的区别

总结：

三、提升

1、仅复制表结构

2、拷贝表

4、显示文章、提交人和最后回复时间

5、日程安排提前五分钟提醒

6、包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表

7、随机取出10条数据

8、删除重复记录

9、列出数据库里所有的表名

10、列出表里的所有的列

11、列是type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。

12、初始化表table1

（1）定义　

3、删除数据库　　　　

between and (在之间的值)