在sqlserver中创建表

1：在sql语句中，临时表有两类，分别是局部(local)和全局(global)临时表，局部临时表只在其会话（事务)中可见，全局临时表可以被会话(事务)中的任何程序或者

? 模块访问

2：创建局部临时表

? ? ?

1. use?db_sqlserver??
2. go??
3. create?table?#db_local_table??
4. (??
5. ??id??int,??
6. ??name?varchar(50),??
7. ??age?int,??
8. ??area?int??
9. )??

use db_sqlserver
go
create table #db_local_table
(
  id  int,name varchar(50),age int,area int
)

3：创建全局临时表

1. use?db_sqlserver??
2. go??
3. create?table?##db_local_table??
4. (??
5. ??id??int,??
6. ??area?int??
7. )??

use db_sqlserver
go
create table ##db_local_table
(
  id  int,area int
)

全局临时表对所有用户都是可见的，在每个访问该表的用户都断开服务器连接时，全局临时表才会被删除

1. use?db_sqlserver;??
2. go??
3. create?table?db_table5??
4. (??
5. ??职工编号?int?primary?key,??
6. ??职工号??varchar(50)?unique,??
7. ??仓库号??varchar(50),??
8. ??工资???int??
9. )??
10. ??
11. go??
12. create?table?db_table6??
13. (??
14. ??订单编号?int?primary?key,??
15. ??订单号??varchar(50)?unique,??
16. ??职工号?varchar(50)?references?db_table5(职工号),??
17. ??订购日期?datetime,??
18. ??销售金额?int??
19. )??

use db_sqlserver;
go
create table db_table5
(
  职工编号 int primary key,职工号  varchar(50) unique,仓库号  varchar(50),工资   int
)

go
create table db_table6
(
  订单编号 int primary key,订单号  varchar(50) unique,职工号 varchar(50) references db_table5(职工号),订购日期 datetime,销售金额 int
)

1. use?db_sqlserver;??
2. go??
3. create?table?db_table7??
4. (??
5. ??仓库编号?int?primary?key,??
6. ??工资???int,??
7. ??面积??int?check(面积>=600?and?面积<=1800)??
8. )??

use db_sqlserver;
go
create table db_table7
(
  仓库编号 int primary key,工资   int,面积  int check(面积>=600 and 面积<=1800)
)

??

1. use?db_sqlserver;??
2. go??
3. create?table?db_table8??
4. (??
5. ??职工编号?int?primary?key,??
6. ??职工号?varchar(50)?unique,??
7. ??仓库号?varchar(50),??
8. ??基本工资?int?check(基本工资>=800?and?基本工资<=2100),??
9. ??加班工资?int,??
10. ??奖金?int,??
11. ??扣率?int,??
12. ??应发工资?as?(基本工资?+?加班工资?+?奖金?-?扣率)??
13. )??

use db_sqlserver;
go
create table db_table8
(
  职工编号 int primary key,职工号 varchar(50) unique,仓库号 varchar(50),基本工资 int check(基本工资>=800 and 基本工资<=2100),加班工资 int,奖金 int,扣率 int,应发工资 as (基本工资 + 加班工资 + 奖金 - 扣率)
)

7：创建含有自动编号字段的数据库表

? ??

1. use?db_sqlserver;??
2. go??
3. create?table?db_table9??
4. (??
5. ???仓库编号?int?identity(1,1)?primary?key,??
6. ???仓库号?varchar(50)?unique,??
7. ???城市?varchar(50)?default('青岛'),??
8. ???面积?int?check(面积>=300?and?面积<=1800)??
9. )??

use db_sqlserver;
go
create table db_table9
(
   仓库编号 int identity(1,1) primary key,仓库号 varchar(50) unique,城市 varchar(50) default('青岛'),面积 int check(面积>=300 and 面积<=1800)
)

向表中添加记录：

1. insert?into?[db_sqlserver].[dbo].[db_table9](仓库号,?面积)?values('400',?1600);??

 insert into [db_sqlserver].[dbo].[db_table9](仓库号,面积) values('400',1600);

8：创建含有排序字段的数据表

1. create?table?db_table10???
2. (??
3. ???仓库编号?int?identity(1,?1)?primary?key,??
4. ???仓库号?varchar(50)?collate?french_CI_AI?not?null,??
5. ???城市?varchar(50)?default?'青岛',??
6. ???面积?int?check(面积>=300?and?面积<=1800)??
7. )??

create table db_table10 
(
   仓库编号 int identity(1,仓库号 varchar(50) collate french_CI_AI not null,城市 varchar(50) default '青岛',面积 int check(面积>=300 and 面积<=1800)
)

和不区分重音的排序。如果要区分大小和和区分排序，修改代码为：French_CS_AS

9：动态判断数据库表是否存在

1. use?db_sqlserver;??
2. go??
3. if(Exists(select?*?from?sys.sysobjects?where?id=OBJECT_ID('db_table9')))??
4. ??print?'数据库表名已经存在'??
5. ????
6. else???
7. ??print?'该数据库表名不存在，可以利用该名创建表'??

use db_sqlserver;
go
if(Exists(select * from sys.sysobjects where id=OBJECT_ID('db_table9')))
  print '数据库表名已经存在'
  
else 
  print '该数据库表名不存在，可以利用该名创建表'

10：查看表的各种信息，可以查看指定数据库表的属性、表中字段属性、各种约束等信息

?

1. use?db_sqlserver;??
2. go??
3. execute?sp_help?db_table9;??

use db_sqlserver;
go
execute sp_help db_table9;

11：用select语句查看数据库表的属性信息

1. use?db_sqlserver;??
2. go??
3. select?*?from?sysobjects?where?type='U'??

use db_sqlserver;
go
select * from sysobjects where type='U'

1. use?db_sqlserver;??
2. go??
3. execute?sp_rename?"db_table9",?"db_renametable"??

use db_sqlserver;
go
execute sp_rename "db_table9","db_renametable"

1. use?db_sqlserver;??
2. go??
3. alter?table?db_table1?add?电子邮件?varchar(50)??
4. alter?table?db_table1?add?联系方式?varchar(50)?default?'0532-88886396'??
5. ??
6. select?name?字段名,?xusertype?类型编号,?length?长度?from?syscolumns?where?id?=?object_id('db_table1')??

use db_sqlserver;
go
alter table db_table1 add 电子邮件 varchar(50)
alter table db_table1 add 联系方式 varchar(50) default '0532-88886396'

select name 字段名,xusertype 类型编号,length 长度 from syscolumns where id = object_id('db_table1')

1. use?db_sqlserver;??
2. go??
3. alter?table?db_table1?alter?column?电子邮件?varchar(200)??
4. ??
5. ??
6. select?name?字段名,?length?长度?from?syscolumns?where?id?=?object_id('db_table1')??

use db_sqlserver;
go
alter table db_table1 alter column 电子邮件 varchar(200)


select name 字段名,length 长度 from syscolumns where id = object_id('db_table1')

1. use?db_sqlserver;??
2. go??
3. alter?table?db_table1?drop?column?电子邮件???
4. ??
5. ??
6. select?name?字段名,?length?长度?from?syscolumns?where?id?=?object_id('db_table1')??

use db_sqlserver;
go
alter table db_table1 drop column 电子邮件 


select name 字段名,length 长度 from syscolumns where id = object_id('db_table1')

1. use?db_sqlserver;??
2. go??
3. drop?table?db_table1??
4. drop?table?db_table1,?db_table2??

use db_sqlserver;
go
drop table db_table1
drop table db_table1,db_table2

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!