/*建表
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 /*取消一切权限*/
*/
