/*建表
create table books(
id int identity(1,1),
name char(20),
author char(10)
)
*/

/*alter table books add publisher char(50)*/ /*增加一列*/

/*alter table books alter column publisher char(10)*/ /*修改列的类型*/

/*alter table books drop column publisher*/ /*删除一列*/

/*insert into books values('ASP','尚俊杰')*/ /*添加一条完整记录*/

/*insert into books(name) values('ASP.NET')*/ /*添加一条不太完整的记录*/

/*select count(*) as 记录总数 from books */ /*记录总数目*/

/*
use master
exec sp_renamedb victor_new,victor         /*改数据库名称*/
*/

/*
exec sp_rename book,books         /*改表的名称*/
*/

/*exec sp_rename 'books.bookname','name','column' */ /*改列的名称*/

/*
create table publisher(
id int identity(1,1),
p_name char(50),
address char(100),
name char(50)
)
*/

/*exec sp_rename 'publisher.name','book_name','column'*/

/*insert into publisher values('清华大学出版社','北京市海淀区','ASP')*/

/*select *from books,publisher where books.name=publisher.book_name*/

/*select books.id,books.name,books.author,publisher.p_name,publisher.address from books,publisher where books.name=publisher.book_name*/

/*select  books.id,books.name,books.author,publisher.p_name,publisher.address from books left join publisher on books.name=publisher.book_name*/

/*select  books.id,books.name,books.author,publisher.p_name,publisher.address from books right join publisher on books.name=publisher.book_name*/

/*select  books.id,books.name,books.author,publisher.p_name,publisher.address from books inner join publisher on books.name=publisher.book_name*/

/*exec sp_createstats*/  /*显示所有的表*/

/*exec sp_columns @table_name='books'*/  /*返回指定的表的列信息*/

/*
if exists(select table_name from INFORMATION_SCHEMA.TABLES where table_name='books')
print 'BOOKS表已经存在!'
*/

/*exec sp_addlogin test*/  /*添加一个帐户，密码为空*/

/*exec sp_droplogin test*/  /*删除账户*/

/*exec sp_addlogin 'test',飋siyu' */   /*添加一个有密码的帐户*/

/*exec sp_password 飋siyu',?','test'*/  /*修改密码 旧密码,新密码,用户名*/

/*
use victor
exec sp_grantdbaccess test   /*用管理员身份登录到允许访问的数据库*/
*/             

/*exec sp_revokedbaccess test */  /*取消访问*/

/*
use master
grant create database to test      /*只能在 master 数据库中授予 CREATE DATABASE 权限。*/
*/

/*
use victor
grant create table to test          /*允许用户在该数据库上创建一个新表*/
*/

/*
use victor                              /*收回用户在该数据库创建表*/
revoke create table from test
*/

/*
use victor
grant create ? to test        /* ?指database,table,view,rule,function,backup,procedure */
*/
 
/*
use victor
revoke create ? from test   /* ?指database,table,view,rule,function,backup,procedure */
*/ 

/*
use victor
grant ? on books to test   /* ?指select,update,insert,delete*/  /*赋予对表的操作权限*/
*/ 

/*
use victor
revoke ? on books from test    /*?指select,update,insert,delete*/  /*收回对表的操作权限*/
*/

/*
use victor
grant all privileges on books to test       /*赋予一切权限*/
*/

/*
use victor
revoke all privileges on books from test   /*取消一切权限*/
*/