sql2005的表结构修改和数据库创建和授权的讲解

use test;
--这是查看当前使用的数据库的一些信息(主要数据库文件，日志文件)
select * from sysfiles;
--查看有哪些数据库
select * from master..sysdatabases;
--查看一个数据库有哪些表
select * from sysobjects where  xtype = 'u';
use master;
select * from sysdatabases;--查看系统数据库

--创建数据库
use test;
/*
   一.建库
     数据库文件
     主要数据库文件.mdf  有且只有一个 ==1
     次要数据库文件.ndf  可有可无 >=0
     日志数据库文件.ldf  >=1
     select * from sysfiles; 查看当前数据库文件的位置

     create database 数据库名
     on
     (
        --主要数据库
     ),
     (
       --这里是次要数据库文件
      )
      ,
      (
         次要
      )
       ....
      log on
      (

          日志
       )

   二.创建表
     1.查询所有表
       select * from sysobjects where xtype='u'
   
     2.系统表(系统创建)和用户表(普通，临时表(#局部，##全局))
    
*/
create database caowen
--如果这里没有用primary指定主数据库文件，那么第一个数据库文件就是主数据库文件，后面的是次数据库文件
--on primary 这是指定了主数据库文件的格式
on(
 name='caowenmdf',--这是文件指定逻辑名称
 filename='D:\go\caowen.mdf',--文件指定操作系统文件名。
 size=10mb,--指定定义的文件的大小。如果主文件的 <filespec> 中没有提供 SIZE 参数，那么 SQL Server 将使用 model 数据库
      --中的主文件大小。如果次要文件或日志文件的 <filespec> 中没有指定 SIZE 参数，则 SQL Server 将使文件大小为 1 MB。
 maxsize=20mb,--定义的文件可以增长到的最大大小。可以使用千字节 (KB)、兆字节 (MB)、千兆字节 (GB) 或兆兆字节 (TB) 后缀。
      --默认值为 MB。指定一个整数，不要包含小数位。如果没有指定 max_size，那么文件将增长到磁盘变满为止。
    filegrowth=10%--定义的文件的增长增量。文件的 FILEGROWTH 设置不能超过 MAXSIZE 设置。
),
--这是第一个次数据库文件
(
   name='caowenndf1',
   filename='D:\go\caowen1.ndf',
   size=1mb,
   maxsize=2mb,
   filegrowth=10%
),
(
   name='caowenndf2',
   filename='D:\go\caowen2.ndf',
   size=1mb,
   maxsize=2mb,
   filegrowth=10%
)
log on(
 name='caowenldf1',
 filename='D:\go\caowen1.ldf',
 size=1mb,
 maxsize=2mb,
 filegrowth=10%
);

use caowen;
select * from sysfiles;
--select * from sysfiles;这是查询当前数据库的数据文件的位置
--select * from sysobjects where xtype='u';--这是查询当前数据库所含有的数据库表

--bbs的用户表
go
create table tbl_bbs_user(
 userid int identity(1,1) ,
 username varchar(30),
 password varchar(30),
 status int
);
go
select * from tbl_bbs_user;
go
--板块表
create table tbl_bbs_board(
 boardid int identity primary key,
 boardname varchar(50),
 boarddesc varchar(20),
 pid int
);
select * from tbl_bbs_board;
go
--版主表（一个版块对应多个版主)
create table tbl_board_master
(
   boardid int ,
   userid  int
);
go

--主帖表
create table tbl_bbs_topic
(
   tid int identity,  --帖子编号
   title varchar(50), --标题
   userid  int      , --发帖人
   createtime datetime , --创建时间
   boardid   int       , --所在版块
   content   varchar(1000), --内容
   status    int       ,   --帖子状态（是否锁定)
   isok      int       ,   --是否精华 1:yes  0:no
   istop     int           --是否置顶
);
go
--增加主键约束
alter table tbl_bbs_user
    add  constraint pk_bbs_user primary key(userid);
--增加唯一约束
alter table tbl_bbs_user
     add constraint unique_bbs_user unique(username);
--增加check约束
alter table tbl_bbs_user
     add constraint check_bbs_user check(status in (0,1));

--联合主键
alter table tbl_board_master
     add constraint pk_bbs_board_master  primary key(boardid, userid);
--增加外键约束
alter table tbl_board_master
 add constraint fk_bbs_board_master_userid foreign key(userid) references tbl_bbs_user(userid);
 
--增加非空约束，
alter table tbl_board_master alter column userid int  not null;
alter table tbl_board_master alter column boardid int  not null;
--上面的增加约束(主键、唯一约束、check约束、联合主键、增外键约束)，其实他们的语法都差不多
--alter table 表名 add  constraint 约束名  相关的约束
--但是非空约束有点不同alter table 表名  alter column 列名 相关的约束，其实把这里的非空约束看成
--是下面的修改字段的约束就好理解了

--给一个表增加一个字段
alter table tbl_bbs_user
 add bbb int not null;
--删除一个字段
alter  table tbl_bbs_user
   drop column aaa;
--修改一个字段
alter  table tbl_bbs_user
 alter column bbb varchar(20) null;
select * from tbl_bbs_user;
--总结上面对字段的处理，其实他们的语法都差不多，alter  table 表名 drop/alter  column 约束条件
--但是在增加一个字段的时候，千万注意不能add column不需要这个column

 

--创建一个新用户，第一个参数是用户名，第二个参数是密码
exec sp_addlogin 'caohuan','wenbin';
exec sp_grantdbaccess
use caowen;

exec sp_revokedbaccess 'caohuan';
--将当前数据库的访问权限赋予给'caohuan'这个新用户,并且取一个别名，这个别名必须要存在，否则下面的赋予权限就会存在问题
exec sp_grantdbaccess 'caohuan', 'ccc';
--这是赋予dep表的select权限给ccc这个别名的用户
grant select on dep to ccc;
grant insert, delete on dep to ccc;
grant all on dep to ccc;
--这是移除ccc用户访问dep表的select权限
revoke select on dep from ccc;

--第一个参数是旧密码，第二个参数是新密码，第三个参数是用户名
sp_password aaa, bbb, ccc;