03 数据库和表的创建

/*********************** 创建数据库 一个实例创建后有N多系统数据表，其中主要的有：master、model、msdb、tempdb 1)master:存放整个数据库的架构信息，在中其功能更强， 在中其部分功能由mssqlsystemresource来代替，在图形界面中看不到。 2)model:用户创建数据库的模板数据库，改变后会影响实例下的所有数据库。 3)msdb:数据库计划备份和系统警告 4)tempdb:存放临时数据库表，决定数据库的快慢 ***********************/ -- 物理环境准备 sp_configure 'show advanced options',1 go reconfigure go sp_configure 'xp_cmdshell',1 go reconfigure go xp_cmdshell 'if not exist d:/accp5.0/database (md d:/accp5.0/database)',no_output go -- 创建数据库GameCardSale use master go if exists (select name from sys.databases where name='GameCardSale') drop database GameCardSale go --创建数据库ExamDB create database GameCardSale on primary ( name='GameCardSale_dat', filename='d:/accp5.0/database/GameCardSale_dat.mdf', size=10mb, maxsize=100mb, filegrowth=1mb ) log on ( name='GameCardSale_log', filename='d:/accp5.0/database/GameCardSale_log.ldf', size=10mb, maxsize=100mb, filegrowth=1mb ) go -- 使用数据库CameCardSale use GameCardSale go --exists方法对null值的处理为真true if exists (select null) print 'null' else print 'not null' --判断数据库是否存在的方法 if db_id('accp') is not null if exists (select name from databases where name='accp') --查看当前数据库 select db_name() --返回当前数据库名 select db_id() --返回当前数据库ID号 --可以用db_name()和db_id()两个函数来查询数据库的name和id之间的对应名称 /*********************** 创建表 ***********************/ /* SQL Server 2005 数据类型 SQL 内部数据类型 整数 bigint 从 -2^63 到 2^63-1的整型数据（所有数字） int 从 -2^31 到 2^31-1 的整型数据（所有数字） smallint 从 -2^15 (-32,768) 到 2^15-1 (32,767) 的整数数据。 tinyint 从 0 到 255 的整数数据。 位 bit 1 或 0 的整数数据。 decimal 和 numeric 具有固定精度和小数位的数字数据。 money 和 smallmoney 货币数据类型 近似数字 float和real 浮点精度数字数据类型 datetime 和 smalldatetime 从1753.1.1到9999.12.31的日期和时间数据类型 字符串 char 固定长度的非 Unicode 字符数据，最大长度为 8,000 个字符。 varchar 可变长度的非 Unicode 数据，最长为 8,000 个字符。 text 可变长度的非 Unicode 数据，最大长度为 2^31-1(2,147,483,647)个字符。 Unicode 字符串 nchar 固定长度的 Unicode 数据，最大长度为 4,000 个字符。 nvarchar 可变长度 Unicode 数据，其最大长度为 4,000 字符。 ntext 可变长度 Unicode 数据，其最大长度为 2^30-1 (1,073,741,823)个字符。 二进制字符串 binary 固定长度的二进制数据，其最大长度为 8,000 个字节。 varbinary 可变长度的二进制数据，其最大长度为 8,000 个字节。 image 可变长度的二进制数据，其最大长度为 2^31-1 (2,147,483,647)个字节。 其它数据类型 cursor 游标的引用。 sql_variant 一种存储 SQL Server 支持的各种数据类型（text、ntext、timestamp 和 sql_variant 除外）值的数据类型。 table 一种特殊的数据类型，存储供以后处理的结果集。 timestamp 数据库范围的唯一数字，每次更新行时也进行更新。 uniqueidentifier 全局唯一标识符 (GUID)。 */ -- 创建用户表 UserInfo -- 存储用户基本信息量 if not exists(select name from sys.tables where name='userinfo') create table UserInfo ( id int not null identity(1,1) primary key , --用户编号 UserId varchar(50) not null , --用户登录ID UserName varchar(50) not null , --真实姓名 PassWord varchar(50) not null, --密码 UserRole int not null, --用户角色 Gender int not null, --性别 PassQuestion varchar(50) not null, --密码提示问题 PassAnswer varchar(50) not null, --密码提示答案 Email varchar(50) not null, --用户邮箱 birthday datetime not null, --用户生日 TelNo varchar(50) not null, --电话号码 Address varchar(50), --联系地址 IDCardNo varchar(18) , --身份证号 Money decimal, --用户余额 UserState int not null --用户状态(用户状态表UserState的ID外键) ) go /* 主键Primary key 1)天然主键：天生就具备主键的特点 2)人工主键：identity 标识列 --个人觉得标识列根本就不能算主键，)它不能作为被引用列2)当打开可插入值模式时可以插入相同的值 所以标识列使用时一定要与pk配合使用才行。 PK与UN primary key && unique 主键约束是唯一性约束中的一种特殊形式，主键是表级约束(一个表只有一个主键)，主键约束的是行，是实体完整性，唯一性约束是行级约束，但可以仅有一次的null值，一个表中可以有多个唯一性约束。 null 代表未知 外键约束 foreign key constraint 子表中的键是引用主表中的键，两者类型必须一致，而且主表中的键必须是 主键列或唯一约束列。同样一个表中可以有多个外键约束。 域：指的是表中的列 实体：表中的行 关系型数据库其实就是指表，所以我们一直在建表。 */ /* 约束创建的方式 1、在字段后指定 2、建表时在所有字段后添加constraint 3、alter table添加约 约束创建写法 主键约束：constraint pk_name primary key(colname) 唯一约束：constraint uq_name primary key(colname) 检查约束：constraint chk_name check(expression) 默认约束：constraint def_name default(value) for colname 外键约束: constraint fk_t1_t2 foreign key(colname1) references t2(colname2) 添加外键约束时可以指定级联操作 on delete cascade 级联删除 on update cascade 级联更新 */ create table p1 ( id int not null primary key identity(1,1) , --行内指定主键 realname varchar(10) not null default 'accp', --行内指定主键 mobilephone varchar(11), age int not null, constraint uq_mobilephone unique(mobilephone) --表内添加约束 ) go alter table p1 add constraint chk_age Check(age between 20 and 50) alter table p1 add constraint def_age default(28) for age insert into p1(mobilephone,age) values('13388889999',26) insert into p1(mobilephone) values('13855556666') select * from p1 --查看表中所有约束 sp_helpconstraint p1 --删除约束 alter table p1 drop constraint def_age --查看当前数据库中所有约束 select obj.[name] 约束名,obj.xtype 约束类型,com.text 约束文本 from sys.sysobjects obj left join sys.syscomments com on obj.id=com.id where obj.xtype in('C','D','F','PK','UQ') go /* 数据增删改查时默认会返回受影响的行数 关闭回显 set nocount on 打开回显示 set nocount off */ create table m ( col1 int, col2 int ) go insert into m(col1,col2) values(2,3) set nocount on insert into m(col1,col2) values(8,8) set nocount off insert into m(col1,col2) values(9,9) select * from m update m set col2=10 where col1=8 delete m where col1=8 /* 标识列也可以手工插入指定值 1)打开插入值设置 2)必须显式插入的列名 set identity_insert table_name on 允许某张表的标识列手式插入值 set identity_insert table_name off 关闭此项操作 */ create table t1 ( sid int not null identity(1,3) primary key, sno int not null, sname varchar(10) not null ) go set identity_insert t1 on --打开插入操作 go insert into t1(sid,sno,sname) values(5,203,'db2') /* 查询指定表中列名 col_name(obj,col_pos) obj : object_id(table_name) col_pos : 字段的位置，从1开始 */ print col_name(object_id('m'),2) /* 限制返回 top 并列返回 with ties ... order by col_name 过滤重复 distinct */ set identity_insert t1 off insert into t1(sno,sname) values(203,'db2') --重复执行10次 select * from t1 select top 5 * from t1 select distinct top 5 sno,sname from t1 select top 5 with ties * from t1 order by newid() /* 表的高级操作 1、初始化表 truncate table table_name 2、更改字段 增加字段：add 字段描述， 注意:如果之前表中有数据，且该字段不允许为空的话就必须为该字段增加默认约束 添加空字段的话，则之前数据将插入null值 删除字段 drop column col_name null --> not null update t1 set col2=100 where col2 is null alter table t1 alter column col2 int not null not null --> null alter column col1 int 更改数据类型 : 有约束时先删除约束 3、查询表结构 4、表的备份 select * into table_new from table_old 只能备份数据，而约束等其它对象不能备份 */ --初始化表t1 truncate table t1 --添加非空字段 alter table t1 add col1 int not null default(2) select * from t1 --默认值插入 insert into t1(sno,sname,col1) values(203,'db2',default) --添加允许为空字段 alter table t1 add col2 int --删除字段 alter table t1 drop column sno select * from t1 --非空not null 改为允许为空null alter table t1 alter column col1 int select * from t1 --允许为null改为非空not null update t1 set col2=100 where col2 is null alter table t1 alter column col2 int not null select * from t1 --将col2字段数据类型改为varchar(50) alter table t1 drop constraint def_col2 alter table t1 alter column col2 varchar(50) not null select * from t1 --查看表结构 select 't1' 表名,col.[name] 字段名,type.[name] 数据类型,col.max_length 字段长度, (case when col.is_nullable=1 then 'null' else 'not null' end) 允许空值, (case when col.is_identity=1 then '是' else '否' end) 是否标识列 from sys.columns col inner join sys.systypes type on col.system_type_id=type.xtype where col.object_id=object_id('t1') go --将表t1备份 (只能复制数据) select * into temp from t1 select * from temp sp_helpconstraint temp