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');--插入部分数据 

delete from ... ：删除记录

insert into author values('A10', 'jack', null, null, null, null, null);insert into author([au_id], [au_name]) values('A10', 'jack');--其余字段被设置为nulldelete from authors where [au_id]='A09';--删除id是A09的一条记录delete from authors;--删除所有记录

alter 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'; --将作者名全部改成sarahupdate 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_sharefrom 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_idxcreate 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_viewselect * from au_name;--列出视图au_name_view中所有数据drop view au_name;--删除视图

 

SQL Server 数据类型

Character 字符串：

数据类型描述存储char(n)固定长度的字符串。最多 8,000 个字符。nvarchar(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 bytesdatetime2从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 100 纳秒。6-8 bytessmalldatetime从 1900 年 1 月 1 日 到 2079 年 6 月 6 日，精度为 1 分钟。4 bytesdate仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。3 bytestime仅存储时间。精度为 100 纳秒。3-5 bytesdatetimeoffset与 datetime2 相同，外加时区偏移。8-10 bytestimestamp存储唯一的数字，每当创建或修改某行时，该数字会更新。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