SQL Server 2008

1.创建数据库

// 主数据 辅助数据 日志数据
create database db1
on primary
(
name = 'f1',
filename = 'E:\sqlwj\f1.mdf',
size = 3mb,
maxsize = unlimited,
filegrowth = 3%
),
(
name = 'f2',
filename = 'E:\sqlwj\f2.ndf',
size = 10mb,
maxsize = 500mb,
filegrowth = 6mb
)
log on 
(
name = 'f3',
filename = 'E:\sqlwj\f3.ldf',
size = 1mb,
maxsize = unlimited,
filegrowth = 2% 
)
    
// 修改数据库名称
alter database db1
modify name = database1

// 增加辅助数据和日志数据
alter database db1 
add file 
(
name = 'f4',
filename = 'E:\sqlwj\f4.ndf',
size = 3mb,
maxsize = unlimited,
filegrowth = 2mb
)
    
alter database db1
add log file
(
name = 'f5',
filename = 'E:\sqlwj\f5.ldf',
size = 1mb,
maxsize = 400mb,
filegrowth = 4% 
)
    
// 删除数据库
drop database db1

2.创建表

use db1
    
create table ts
(
id varchar(50) primary key,
name varchar(50) not null,
age int check (age >18 and age <30),
birthday datetime,
school varchar(50) default 'XXX学校',
phone varchar(20) unique,
sid varchar(50) foreign key references student(id)
)
    
// 增加字段
alter table ts 
add teacher varchar(50) not null
    
// 修改字段数据类型
alter table ts 
alter column teacher varchar(20)

// 删除字段
alter table ts 
drop column teacher
    
// 修改字段名称
exex sp_rename 'ts.name','tsname'

// 修改表名称
exex sp_rename 'ts','ts2'
  
// 删除表
drop table ts

3.创建约束

use db1
    
// 主键
alter table student
add constraint PK_ID primary key (id)
    
// 唯一键
alter table student
add constraint UQ_NAME unique (name)
    
// 检查约束
alter table student
add constraint CK_AGE check (age > 18 and age < 30)
    
// 默认约束
alter table student
add constraint DF_SEX default '男' for sex
    
// 外键
alter table school
add constraint FK_SID foreign key (sid)
references student(id)

// 删除约束
alter table student
drop constraint PK_ID

alter table student
drop constraint UQ_NAME

alter table student
drop constraint CK_AGE

alter table student
drop constraint DF_SEX

alter table student
drop constraint FK_SID

4.变量和运算符

// 声明局部变量
declare @name varchar(20), @age int
    
// 赋值
set @name = '张三'
set @age = 18
    
// 输出
print @name
print @age
    
// 算术运算符 + - * / %
declare @a int, @b int, @z int
set @a = 18
set @b = 4
set @z = @a + @b
print @z
    
// 赋值运算符 =
    
// 位运算符 & | ^
    
// 比较运算符 = < > <= >= <> != !< !>
    
// 逻辑运算符 and or between and in like not
    
// 连接运算符 +

5.流控制语句

// begin end
declare @a int, @b int, @z int
begin
set @a = 18
set @b = 4
set @z = @a + @b
end
print @z
    
// if else
declare @a int
set @a = 6
if @a > 0
    print 'true'
else 
    print 'false'

// while 
declare @a int, @sum int
set @a = 1
set @sum = 0
while @a <= 10
begin
set @sum = @sum + @a
set @a = @a + 1
end
print @sum
    
// break continue
    
// case
declare @grade int, @msg varchar(20)
set @grade = 88
set @msg = 
case
    when @grade >= 90 and @grade <= 100 then 'excellent'
    when @grade >= 70 and @grade < 90 then 'good'
    when @grade >= 60 and @grade < 70 then 'pass'
    else 'fail'
end
print @msg
        
// waitfor
waitfor delay '00:00:03'
print '你好'
        
waitfor time '10:11:00'
print '你好'
        
// goto
declare @x int
set @x = 1
loving:
       print @x
       set @x = @x + 1
while @x <= 3
goto loving
    
// return

6.查询语句

// distinct 去重(只能针对某一字段进行去重)
select distinct name from student
    
// top n 查询前n行
select top 3 * from student
    
// 计算列
select age-2 as new_age from student
    
// in 满足其中一个条件即可
select * from student where age in (20, 22, 24)
    
// like
select * from student where name like '王%'
    '王_'
    '_[大小]%'
    '_[^大小]%'
    
// is null is not null
    
// 聚合函数 除count()外，其他聚合函数均忽略null值
    select sum(grade) as total_grade from student
    select count(distinct bm) from student
    avg()
    max()
    min()
    count()
    
// group by having子句可以包含聚合函数，where不可以
select school 
from student 
group by school 
having sum(grade) < 100
    
// 在查询的基础上创建新表
select * into new_tab from tab1
    
// select语句
select
into
from
where
group by
having
order by

7.表连接

// 
select ...
from tab1, tab2
where tab1.id = tab2.glid
    
// join on
select ...
from tab1 join tab2
on tab1.id = tab2.glid
    
// left join on right join on

// full join on
    

8.子查询

// 嵌套子查询，where子句后的条件是什么，子查询就查什么
select * from sc where sno in (select sno from student where sdept='计算机系')
    
// 相关子查询

9.增删改

insert into student(name,age,sex) values('张三'，20，'男'),(...)
insert into student values('李四'，26，'男'),(...)
insert into new_student select name,age from student
    
update student set name = 'XXX', age = 30-2 where ...
    
delete from student where ...
delete top (20) percent from student
delete top (10) from student

10.视图

// 创建视图
create view shitu1 as select * from student
    
// 修改视图名称
exec sp_rename 'shitu1','shitu2'
    
// 修改视图字段
exec sp_rename 'shitu2.name','sname'
    
// 修改视图 alter 可以修改数据和结构
alter view shitu1 as select name,age from student
    
// 添加数据，原表数据也跟着变
insert into shitu2 values(...)
    
// 修改视图数据，原表数据也会跟着修改
update shitu2 set name = 'XX', age = 23 where ...
    
// 删除视图，原表不受影响
drop view shitu2
    
// 删除视图数据，原表数据也会删除
delete from shitu2 where ...

11.索引

// 创建非聚集索引
create index suoyin_name on student(name)
    
// 创建聚集索引
create unique clustered index suoyin_name on student(id)
    
// 删除索引
drop index student.suoyin_name
    
// 视图也可以创建索引。创建主键会自动创建聚集索引，每张表只有一个聚集索引，删除主键索引先删除主键约束或唯一约束（索引会自动删除）。