SQL建表语句约束

一、基础(建表、建约束、关系)

约束（constraint）是microsoft sql server 提供的自动保持数据库完整性的一种方法，定义了可输入表或表的单个列中的数据的限制条件（有关数据完整性的介绍请参见第9

章）。在sql server 中有5 种约束：主关键字约束（primary key constraint）、外关键字约束（foreign key constraint）、惟一性约束（unique constraint）、检查约束（check

constraint）和缺省约束（default constraint）。

(最后几页介绍sql基础约束)

1、说明：创建数据库

create database database-name

2、说明：删除数据库

drop database dbname

3、说明：备份sql server

--- 创建备份数据的 device

use master

exec sp_addumpdevice disk, testback, c:mssql7backupmynwind_ --- 开始备份

backup database pubs to testback

4、说明：创建新表

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

--建表、建约束、关系

create table tableok

(

col1 int, col2_notnull int not null,

col3_default nchar(1) not null default(男),

--默认男

col4_default datetime not null default(getdate()),

--默认得到系统时间

col5_checkintnot null check(col5_check>=18 and col5_check<=55), --添加约束，数据值在18到55之间

col6_checknchar(9) not null check(col6_check like msd0902[0-9][^6-9]),

--添加约束，数据值前7位必须是?msd0902?，倒数第两位可以是0-9中任意一个数字，最后一位不是6-9之间的数字。

cola_primary nchar(5) not null primary key,

--建立主键

colb_unique int unique,

--唯一约束

col7_identity int not null identity(100,1),

--自增长，从100开始，每列值增加1个

col8_identity numeric(5,0) not null identity(1,1)

--自增长，从1开始，每列值增加1个，最大值是5位的整数

col9_guid uniqueidentifier not null default(newid())

--使用newid（）函数，随机获取列值

)

--alter

--主外键/引用/关系 约束

alter table 从表名 [with check]--启用 with nocheck--禁用约束

add constraint fk_主表名_从表名

foreign key (从表中的字段名) references 主表名 (主表中的字段名)

--其它非主外键约束

alter table wf

add constraint 约束名约束类型具体的约束说明

alter table wf--修改联合主键

add constraint pk_cola_primary primary key(cola_primary,col1)

根据已有的表创建新表：

a：create table tab_new like tab_old (使用旧表创建新表)

b：create table tab_new as select col1,col2… from tab_old definition only

5、说明：删除新表

drop table tabname

6、说明：增加一个列

alter table tabname add column col type

注：列增加后将不能删除。db2中列加上后数据类型也不能改变，唯一能改变的是增加

7、说明：添加主键： alter table tabname add primary key(col)

说明：删除主键： alter table tabname drop primary key(col)

8、说明：创建索引：create [unique] index idxname on tabname(col….)

删除索引：drop index idxname

注：索引是不可更改的，想更改必须删除重新建。

9、说明：创建视图：create view viewname as select statement

删除视图：drop view viewname

10、说明：几个简单的基本的sql语句

选择：select * from table1 where 范围

插入：insert into table1(field1,field2) values(value1,value2)

删除：delete from table1 where 范围

更新：update table1 set field1=value1 where 范围

查找：select * from table1 where field1 like ?%value1%? ---like的语法很精妙，查资料!

排序：select * from table1 order by field1,field2 [desc]

总数：select count 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

11、说明：几个高级查询运算词

a： union 运算符

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

b： except 运算符

except 运算符通过包括所有在 table1 中但不在 table2 中的行并消除所有重复行而

派生出一个结果表。当 all 随 except 一起使用时 (except all)，不消除重复行。 c：

intersect 运算符

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

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

12、说明：使用外连接

a、left （outer） join：

左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。 sql:

select a.a, a.b, a.c, b.c, b.d, b.f from a left out join b on a.a = b.c

b：right （outer） join:

右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。 c：full/cross （outer） join：

全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

12、分组:group by:

一张表，一旦分组完成后，查询后只能得到组相关的信息。

组相关的信息：（统计信息） count,sum,max,min,avg 分组的标准)

在sqlserver中分组时：不能以text,ntext,image类型的字段作为分组依据 在selecte统计函数中的字段，不能和普通的字段放在一起；

13、对数据库进行操作：

14.如何修改数据库的名称:

sp_renamedb old_name, new_name

二、提升

1、说明：复制表(只复制结构,源表名：a 新表名：b) (access可用)

法一：select * into b from a where 1<>1（仅用于sqlserver）

法二：select top 0 * into b from a

2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (access可用)

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

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

insert into b(a, b, c) select d,e,f from b in ?具体数据库? where 条件

例子：..from b in &h(.)& & where..

4、说明：子查询(表名1：a 表名2：b)

select a,b,c from a where a in (select d from b ) 或者: select a,b,c from a where

a in (1,2,3)

5、说明：显示文章、提交人和最后回复时间

select ,me,e from table a,(select max(adddate) adddate

from table where =) b

6、说明：外连接查询(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.f from a left out join b on a.a = b.c

7、说明：在线视图查询(表名1：a )

select * from (select a,b,c from a) t where t.a > 1;

8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 数值1 and 数值2

9、说明：in 的使用方法

select * from table1 where a [not] in (?值1?,?值2?,?值4?,?值6?)篇二：sql之-建库、建表、建约束、关系sql基本语句大全

sql之-建库、建表、建约束、关系、部分t-sql语句

--建库 if exists(select * from abases where name=wf) begin use master

drop database wf end go create database wf on (name=nwf,filename=ne:mycodeetc收费站etc收费站etc_,size=3mb,maxsize=unlimited,filegrowth=1) --建库

if exists(select * from abases where name=wf)

begin

use master

drop database wf

end

go

create database wf

on

(name=nwf,filename=ne:mycodeetc收费站etc收费站etc_,size=3mb,maxsize=unlimited,filegrowth=1)

log on

(name=nwf,filename=ne:mycodeetc收费站etc收费站etc_datawf_,size=3mb,maxsize=unlimited,filegrowth=1)

go

use wf

go

if exists(select * from ects where name=wf )

begin

drop table wf

end

--建表、建约束、关系

use wf

go

create table tableok

(

col1 int,

col2_notnull int not null,

col3_default nchar(1) not null default(男), --默认男

col4_default datetime not null default(getdate()), --默认得到系统时间

col5_checkintnot null check(col5_check>=18 and col5_check<=55), --添加约束，数据值在18到55之间

col6_checknchar(9) not null check(col6_check like msd0902[0-9][^6-9]), --添加约束，数据值前7位必须是‘msd0902’，倒数第两位可以是0-9中任意一个数字，最后一位不是6-9之间的数字。

cola_primary nchar(5) not null primary key, --建立主键

colb_unique int unique, --唯一约束

col7_identity int not null identity(100,1), --自增长，从100开始，每列值增加

1个

col8_identity numeric(5,0) not null identity(1,1) --自增长，从1开始，每列值增加1个，最大值是5位的整数

col9_guid uniqueidentifier not null default(newid()) --使用newid（）函数，随机获取列值

)

--alter

--主外键/引用/关系 约束

alter table 从表名 [with check]--启用 with nocheck--禁用约束

add constraint fk_主表名_从表名

foreign key (从表中的字段名) references 主表名 (主表中的字段名)

--其它非主外键约束

alter table wf

add constraint 约束名 约束类型 具体的约束说明

alter table wf--修改联合主键

add constraint pk_cola_primary primary key(cola_primary,col1)

--- 【into】 <表名>【列名】 values <值列表>

--1）全部【列名】 可以省略

insert stuinfo values(,,)

create table outtable

(

id int not null primary key,

[name] nvarchar(4) not null,

adrress nvarchar(20) default 地址不详

)

truncate table outtable

alter table outtable alter column id int identity(4,1)

insert outtable values (1,22,)

insert outtable values(2,32,)

insert outtable values(3,33,)

--2）部分

insert stuinfo (stuno) values ()

--3)自动增长列，不能手动插入。将列名、列值省略

---2多行插入

---1）从一个现有表中取出所取字段插入到目标表中

insert into stuinfo (stuno,stuname) select stuno,stuname from stumarks insert

into stuinfo(stuno) select stuno from stumarks

---2）从现有的表中取出数据，创建一个新表，将数据插入到新表中

select [列名] into <表名> from <源表名>

select id as 编号,[name] into newtable from outtable

select cast(id as varchar(4))+cast([name] as nvarchar(4)) as 编号及姓名 ,id

into newtable from outtable

select * from newtable

drop table newtable

---3)union 现有的多个表中取数据放入现有的表3中

insert into<表名3> [列名]

select * from 表1 union

select * from 表2

--3.更新语句 update <表名> set <列名=更新值> [where <更新条件>]

---注意：一般where不要省略,如不写将修改整个表

--4truncate删除数据

--比delete只能全部删除数据，不能部分删除，删除的效率高，可以重置自增长列

select * from stumarks

select * from stuinfos

--给考试成绩各提5分,100分封顶。

update stumarks set writtenexam=100

where writtenexam>95

update stumarks set labexam=100

where labexam>95

update stumarks

set writtenexam=writtenexam+5

where writtenexam+5<=100

update stumarks

set labexam=labexam+5

where labexam+5<=100

select examno,stuno,writtenexam+5 as 笔试,labexam+5 as

where writtenexam+5<=100 and labexam+5<=100

create table t

(

id int ,

name nchar(4),

dt datetime,

age int,

score int

)

insert t values (1,a,getdate(),20,50)

insert t values (2,b,getdate(),21,60)

insert t values (3,c,getdate(),21,100)

insert t values (4,d,getdate(),23,80)

select * from t

select top 2 * from t

select top 60 percent * from t

select top 5 * from products

select top 5 percent * from products

--启别名：两种方式 as（可省略） 、=

select productid as 编号 from products

select productid 编号 from products

机试 from stumarks

select 编号=productid from products

select employeeid from employees

--declare @a nvarchar(6)

--set @a=员工编号为：

select n员工编号为：+cast(employeeid as nvarchar(2)) from employees

select distinct country from suppliers order by country

select * from t

select * from products

select * from products order by 4 asc,6 desc

select * from products where unitprice>16 and productname like t% or

productid=16

select * from suppliers where country in(japan,italy)

(1)char、varchar、text和nchar、nvarchar、ntext

char和varchar的长度都在1到8000之间，它们的区别在于char是定长字符数据，而varchar是变长字符数据。所谓定长就是长度固定的，当输入的数据长度没有达到指定的长度时将自动以英文空格在其后面填充，使长度达到相应的长度；而变长字符数据则不会以空格填充。text存储可变长度的非unicode数据，最大长度为2^31-1(2,147,483,647)个字符。

后面三种数据类型和前面的相比，从名称上看只是多了个字母n，它表示存储的是unicode数据类型的字符。写过程序的朋友对unicode应该很了解。字符中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱，unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000之间。和char、varchar比较：nchar、nvarchar则最多存储4000个字符，不论是英文还是汉字；而char、varchar最多能存储8000个英文，4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。

(2)datetime和smalldatetime

datetime：从1753年1月1日到9999年12月31日的日期和时间数据，精确到百分之三秒。 smalldatetime：从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟。

(3)bitint、int、smallint、tinyint和bit

bigint：从-2^63(-9223372)到2^63-1(9223372)的整型数据。

int：从-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型数据。

smallint：从-2^15(-32,768)到2^15-1(32,767)的整数数据。

tinyint：从0到255的整数数据。

bit：1或0的整数数据。

(4)decimal和numeric

这两种数据类型是等效的。都有两个参数：p（精度）和s（小数位数）。p指定小数点左边和右边可以存储的十进制数字的最大个数，p必须是从 1到38之间的值。s指定小数点右边可以存储的十进制数字的最大个数，s必须是从0到p之间的值，默认小数位数是0。

(5)float和real

float：从-1.79^308到1.79^308之间的浮点数字数据。

real：从-3.40^38到3.40^38之间的浮点数字数据。在sql server中，real的同义词为float(24)。