MSSQL l操作入门学习笔记

use master
go
CREATE DATABASE studio ON  PRIMARY
(   
    NAME = studio ,
    FILENAME = N'd:\studio.mdf' , --创建主数据文件
    SIZE = 3072KB , 
    MAXSIZE = UNLIMITED, 
    FILEGROWTH = 1024KB
),
fileGroup testFG01                  --创建文件组testFG01
(   
    NAME = 'arti01', 
    FILENAME = 'd:\arti01.ndf' , --创建辅数据文件
    SIZE = 512kb, 
    MAXSIZE = UNLIMITED, 
    FILEGROWTH = 2%
),
(   
    NAME = 'arti02', 
    FILENAME = 'e:\arti02.ndf' , --创建辅数据文件
    SIZE = 512kb, 
    MAXSIZE = UNLIMITED, 
    FILEGROWTH = 2%
),
fileGroup testFG02                  --创建文件组testFG02
(   
    NAME = 'task01', 
    FILENAME = 'd:\task01.ndf' , --创建辅数据文件
    SIZE = 1MB, 
    MAXSIZE = UNLIMITED, 
    FILEGROWTH = 1%
),
(   
    NAME = 'task02', 
    FILENAME = 'e:\task02.ndf' , --创建辅数据文件
    SIZE = 1MB, 
    MAXSIZE = UNLIMITED, 
    FILEGROWTH = 2%
)
LOG ON
(  
    NAME = studio_log, 
    FILENAME = N'd:\studio_log.ldf' , --创建事务日志文件
    SIZE = 1024KB,
    MAXSIZE = 1024GB, 
    FILEGROWTH = 10%
)
 
go
use studio
--创建用户表
go
create table users(
    id int  not null,
    userName varchar(32) not null,
    age tinyint not null,
    sex char(2) not null
    constraint pk_user primary key clusterED(id),
    constraint CK_age Check(age>0)
)on testFG01;
 
alter table users
add constraint CK_sex Check(sex in('男','女'))
 
--创建角色表
create table roles(
    id int not null,
    roles varchar(32) not null
    constraint pk_rol primary key(id)
)on testFG02;
--创建用户角色中间表
create table u_rol(
    uid int not null,
    rid int not null,
    constraint pk_u_rol primary key(uid,rid),--联合主键
    constraint fk_user foreign key (uid) references users(id),
    constraint fk_roles foreign key(rid) references roles(id)
)
 
 
declare @i int
set @i=10000
while(@i<=100000)
begin
    insert into studio.dbo.users(id,sex,age,userName) values(@i,'男',21,'ylfeiu')
    set @i=@i+1
end
 
 
insert into studio.dbo.users(id,userName,sex,age)values(2,'viva','女',21);
insert into studio.dbo.roles(id,roles)values('002','游客');
 
insert into studio.dbo.u_rol(uid,rid)values(2,'002')
 
select * from users;
select * from roles;
select * from studio.dbo.u_rol;
 
select u.*,r.roles from users u
inner join u_rol ur on u.id=ur.uid
inner join roles r on r.id=ur.rid
where u.id='u002'
 
use master
go

drop database studio