SQL基础(2)：数据操作-增删改

以下只针对SQL Server，其它数据库类型可能不支持某些操作或操作名不同。

create/drop database：创建/删除数据库

create database db2_name;
drop database db2_name;

create/drop table：创建/删除表

create table person(
                      [ID]   int primary key,
		              [name] varchar(20),
		              [city] varchar(30)
		           );			
drop table person;

insert into ... values()：插入一行数据，即一条记录

需要注意的是如果插入的数据不是一整条记录，则应列出所插入的字段名，如：

create table person(
								[ID]   int primary key,
								[name] varchar(20),
								[city] varchar(30)
);
insert person values(1001, 'leon', 'beijing');--插入一整条记录
insert person([ID], [name]) values(1002, 'leon');--插入部分数据，其余字段值设为NULL

delete from ... ：删除记录

insert into author values('A10', 'jack', null, null, null, null, null);
insert into author([au_id], [au_name]) values('A10', 'jack');--其余字段被设置为null

delete from authors where [au_id]='A09';--删除id是A09的一条记录
delete from authors;--删除所有记录

a lter table ... add ...：插入一列，即插入一个字段

alter table ... drop column ...：删除一列

alter table ... alter column ...：更改列的数据类型

alter table authors add [add_com] varchar(10);
alter table authors alter column [add_com] int;
alter table authors drop column [add_com];	

update ... set ...：修改数据库中的值

update  表名 set 字段名 = 某个值  

update authors set [au_fname] = 'sarah'; --将作者名全部改成sarah
update authors set [au_fname] = 'sarah', [au_lname] = 'buchman' where [au_id] = 'A01'; --将ID是A01的作者名字改成sarah， 姓改成buchman

truncate table ...：清空整个表

truncate table authors;	

select ... into ...：复制表中指定数据到另一个表

select * into [db1].[dbo].[authors_backup] from authors;--复制表中所有数据到另一个数据库上的表

select [au_fname], [au_lname] into authors_backup from authors 
	where [state] = 'NY';--复制表中指定数据到另一个表

select t1.[au_fname], t2.[royalty_share] into table_name_share
	from authors as t1 join title_authors as t2 on t1.[au_id] = t2.[au_id];--复制两个表中指定数据到另一个表

create index ... on ...：创建索引

drop index ...：删除索引

索引的优缺点：优点为可以加快数据查询，缺点为索引会占用额外的空间, 而且在对表中数据增、删、改时也必须维护和更新索引。

对于经常发生以下动作的列，创建索引是合适的：

查询select

条件判断where

排序order by

分组group by

联结join ... on ...

计算统计max()、min()、......

  主键列上一定要有索引，外键列上可以创建索引

对于下列情况，不应该创建索引：

text、bit、image数据类型的列上不能创建索引

在那些重复值比较多，查询较少的列上不要建立索引

索引的分类：

1，按存储结构可分为：

a、聚集索引：指物理存储顺序与索引顺序完全相同，它由上下两层组成，上层为索引页，下层为数据页，只有一种排序方式，因 此 每个表中只能创建一个聚集索引。

b、非聚集索引：指存储的数据顺序一般和表的物理数据的存储结构不同。通过下表我们可以分析出：（其中在学号上建立非聚集 索 引）

2，根基索引键值是否唯一，可以判定是否为唯一索引，唯一索引的列中不能有重复的数据。

3，基于多个字段的组合创建的索引称为组合索引。

4，根据索引键值的排序方式可以分为升序索引和降序索引：创建列的索引后当对列进行查询时默认会以升序排序方式列出列中键值，即默认为升序索引。

create index pub_id_idx on titles([pub_id]);--在表titles中pub_id列上建立索引,索引名为pub_id_idx

create unique index title_id_idx on titles([title_id]);--建立唯一索引 

create index state_city_idx on authors([state], [city]);--建立组合索引：当按照[state]+[city]进行排序,检索时索引才有效

create index price_idx on titles([price] desc);--建立降序索引

drop index titles.price_idx;--删除索引

以上索引内容部分转自http://www.jb51.net/article/30950.htm

create view ... as ...：创建视图

drop view ...：删除视图

视图就是基于SQL语句结果集的一个虚拟表，同普通的表一样，可以对视图进行查询等操作。

create view au_name_view as select [au_id], [au_fname], [au_lname] from authors;--创建视图au_name_view
select * from au_name;--列出视图au_name_view中所有数据

drop view au_name;--删除视图

存储过程

存储过程是预编译并存储在数据库上的一条或多条SQL语句，其优点有：

 ①、执行速度快：存储过程只在创建的时候进行分析和编译。

 ②、减少网络开销：程序调用的时候只是使用存储过程名称和参数。

 ③、方便代码移植：存储过程在数据库上创建和修改，对应用程序的代码无影响。

 ④、安全性高：可以设置存储过程的权限，网络传输中只是存储过程的调用。

触发器

触发器是一种特殊的存储过程，不同之处在于触发器主要是事件（对表进行插入、更新、删除）触发的时候被自动调用执行的，存储过程是通过EXECUTE语句调用的。触发器可以有事件之前触发、事件之后触发等类型。

事务

事务是一种机制，它将多条SQL语句组合成一个执行单元，这些SQL语句要么全部执行，要么都不执行。可以说事务是一个原子性的完整操作。

SQL Server 数据类型

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 参数指示可以存储的最大位数（小数点左侧和右侧）。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 bytes
datetime2	从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 100 纳秒。	6-8 bytes
smalldatetime	从 1900 年 1 月 1 日 到 2079 年 6 月 6 日，精度为 1 分钟。	4 bytes
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	存储结果集，供稍后处理。

   sql Server数据类型转自http://www.w3school.com.cn/sql/sql_datatypes.asp