SQL基本操作【转】

/*------------------创建数据库-------------------------*/
if exists(select * from sysdatabases where name = 'Test')
begin
drop database Test
print 'ok'
end
create database Test
/*------------------创建用户表-------------------------*/
if exists(select * from sysObjects where name = 'userinfo')
begin
drop table userinfo
print 'ok'
end

create table UserInfo
(
userId int identity(1,1) primary key,
userName varchar(8000) not null,
userSex int not null,
userAddress nvarchar(4000) not null,
userPhone varchar(8000) not null,
userRemark text null
)
select * from userinfo
/*------------------插入数据-------------------------*/
insert into userinfo values ('强强',1,'XXX','13542786654','')
insert into userinfo values('光光',1,'xxxxx','13542786654','')
insert into userinfo values ('波波',1,'镇xx县','13542786654','')
insert into userinfo values ('勇勇',1,'xx','13542786654','')
insert into userinfo values ('伟伟',1,'八旗子弟','13542786654','')
select * from userinfo
/*------------------修改数据-------------------------*/
update userinfo set userPhone = '15991442568' where userId = 2
update userinfo set userPhone = '12345678984',userSex = 0 where userId = 3
select * from userinfo
/*------------------删除数据-------------------------*/
delete from userinfo where userId in (6,7,8,9,10)
delete from userinfo where userId = 7
select * from userinfo

/*------------------给表添加约束-------------------------*/
alter table userinfo
add constraint DF_userSex default 0 for userSex
alter table userinfo
add constraint PK_userId primary key(userId)
/*------------------删除约束-------------------------*/
alter table userinfo drop constraint DF_userSex
alter table userinfo drop constraint PK_userId

/*-------------------创建学生成绩表---------------------------------*/
delete from studentAchievement
create table studentAchievement
(
id int identity(1,1) primary key,
studentID varchar(8),
courseID varchar(10),
achievement int
)
go

insert into studentAchievement values('学号1','课程1',80)
insert into studentAchievement values('学号2','课程1',45)
insert into studentAchievement values('学号3','课程1',65)
insert into studentAchievement values('学号4','课程1',36)
insert into studentAchievement values('学号1','课程2',67)
insert into studentAchievement values('学号2','课程2',36)
insert into studentAchievement values('学号3','课程2',97)
insert into studentAchievement values('学号4','课程2',74)
insert into studentAchievement values('学号1','课程3',63)
insert into studentAchievement values('学号2','课程3',47)
insert into studentAchievement values('学号3','课程3',68)
insert into studentAchievement values('学号4','课程3',79)
insert into studentAchievement values('学号1','课程4',78)
insert into studentAchievement values('学号2','课程4',85)
insert into studentAchievement values('学号3','课程4',68)
insert into studentAchievement values('学号4','课程4',89)

/*------------------创建登陆用户-------------------------*/
/*exec sp_grantlogin '计算机名\用户名'*/
exec sp_grantlogin 'BlackPower\yang' /*添加windows用户(貌似可以不要)*/
/*exec sp_addlogin '登录名','密码'*/
exec sp_addlogin 'yang','sql' /*添加SQL登陆用户*/


/*------------------创建数据库用户-------------------------*/
use Test
/*exec sp_grantdbaccess 'SQL用户名','不知道'*/
exec sp_grantdbaccess 'yang','yanguser' /*给yang分配Test库的访问权限*/

/*------------------???为用户分配对某表的操作权限???-------------------------*/
grant select,insert,delete,update
on userinfo to yang /*为用户yang分配对Test库中userinfo表的增删查改权限*/