SQL Server 数据库操作 

--================================================ 
go 
--使用T-SQL语句创建数据库 
CREATE  DATABASE  new_db  --数据库名称 
 ON PRIMARY 
( 
    NAME= 'new.mdf', 
    FILENAME= 'D:\My Documents\SQL_Server\new.mdf', 
    SIZE=3mb, 
    MAXSIZE=30mb, 
    FILEGROWTH=10% 
) 
go 
--修改数据库容量 
ALTER DATABASE new_db 
MODIFY  FILE 
( 
    NAME='new.mdf',  --逻辑名称 
    SIZE=5MB 
) 
go 
--缩减数据库容量 
DBCC SHRINKDATABASE(new_db,1)  
go 
--使用数据库 
use new_db 
go 
--切换使用的数据库 
use master 
go 
--更改数据库名称 
EXEC sp_renamedb new_db , new_db123 
EXEC SP_RENAMEDB new_db123 , new_db 
go 
--删除数据库 
DROP DATABASE new_db 
go 
--分离数据库 
 
--附加数据库 
 
----------------------------------------------------- 
--创建数据库 
create database st_db 
on primary 
( 
    name='st.mdf', 
    filename='D:\My Documents\SQL_Server\st_db.mdf', 
    size=5mb, 
    maxsize=50mb, 
    filegrowth=10% 
) 
 
go 
--使用数据库 
use st_db 
 
go 
--创建表st_table 
create table st_table 
        ( 
            学号 int primary key identity , 
            名称 char(6) not null, 
            专业方向 varchar(10) not null, 
            系部代码 char(2) not null, 
            备注 varchar(50) 
        ) 
 
go 
--重命名表： 
exec sp_rename 'st_table','st_table123' 
exec sp_rename 'st_table123','st_table' 
 
go 
--重命名列： 
exec sp_rename 'st_table.学号','学号123','column' 
exec sp_rename 'st_table.学号123','学号','column' 
go 
 
--添加新列： 
alter table st_table add 成绩 smallint 
go 
--更改列的数据类型： 
alter table st_table  alter column  成绩  char(4)  
go 
--删除列： 
 alter table st_table drop column 成绩 
go 
--使用系统存储过程sp_help查看表信息 
EXEC  sp_help st_table 
go 
--删除表： 
drop table st_table 
 
go 
--创建表sc_table 
create table sc_table 
( 
    学号 int primary key identity 
) 
go 
alter table sc_table 
    add 姓名 char(6) not null 
go 
--删除表sc_table 
drop table sc_table     
/* 
alter table sc_table 
    add 专业 varchar(10) not null 
alter table sc_table 
    add 系部代码 char(2) not null 
*/ 
--创建另一个表 
create table t1 
( 
    学号 int not null, 
    us varchar(30) 
) 
 
--删除表的所有数据： 
Truncate table t1 
go 
--创建主键约束 
alter table t1 
    add constraint pk 
    primary key clustered (学号) 
 
go 
--创建t2表 
create table t2 
( 
    学号 int not null, 
    us varchar(30) 
) 
go     
--创建外建约束 
alter table t2 
    add constraint wz 
    foreign key (学号) 
    references t1(学号) 
go 
--删除t2表 
drop table t2 
go 
--删除t1表 
drop table t1 
--============================================================= 
 
go 
--创建表 
create table st_table2 
        ( 
            学号 int not null identity , 
            名称 char(10) not null, 
            专业方向 varchar(10) not null, 
            系部代码 char(2) not null, 
            备注 varchar(50) 
        ) 
go 
alter table st_table2 
    add 高考分数 int 
go 
 
 
 
--数据的添加 
insert st_table2 values ('李四','计算机系','01',null,null) 
insert st_table2 values ('张三','中文系','02','',null) 
insert st_table2 values ('张龙','计算机系','01','HELLO !','492') 
insert st_table2 values ('王五','外语系','03','外语系学习各国的语言',null) 
insert st_table2 values ('赵六','中文系','02',null,'532') 
insert st_table2 values ('赵七八','计算机系','01','学习C语言等有关课程','581') 
go 
--省略values 的insert 语句 
insert st_table2(名称,专业方向,系部代码,备注) 
    select 名称,专业方向,系部代码,备注 from st_table2 
insert st_table2(名称,专业方向,系部代码,备注) 
    select 名称,专业方向,系部代码,备注 from st_table2 
insert st_table2(名称,专业方向,系部代码,备注) 
    select 名称,专业方向,系部代码,备注 from st_table2 
insert st_table2(名称,专业方向,系部代码,备注) 
    select 名称,专业方向,系部代码,备注 from st_table2 
go 
--数据的修改     
update st_table2 set 系部代码='01'  
update st_table2 set 系部代码='02'where 专业方向='中文系' 
update st_table2 set 系部代码='03'where 专业方向='外语系' 
 
go 
--输出表中的所有列 
select * from st_table2 
select 学号,名称,专业方向,系部代码,备注 from st_table2 
go 
--输出表中部分列 
select 学号,名称 from st_table2 
select 系部代码,名称 from st_table2 
go 
--选择表中的若干记录 
select distinct 系部代码 from st_table2 
select distinct 系部代码,专业方向 from st_table2 
select distinct 系部代码,名称 from st_table2 
go 
--限制返回行数 
select top 10 * from st_table2 
select top 5 系部代码,名称 from st_table2 
select top 5 学号,名称 from st_table2 
go 
 
--常用的查询条件 
select * from st_table2 where 学号>=65 
select * from st_table2 where 学号<>65 and 学号<>66 and 学号<>67 and 学号<>68 
select * from st_table2 where 学号 between 60 and 80 or 系部代码=01 
select * from st_table2 where 学号 in(2,23,24,3,4,5,6,7,8) 
select * from st_table2 where 学号 not in(1,2,23,24,3,4,5,6,7,8) 
select * from st_table2 where 学号 not between 60 and 80 
go 
select distinct * from st_table2  
    where (专业方向 like '外语系'  or 名称 like '张三') and 备注 is not null 
go 
select * from st_table2  
    where 专业方向 not like '外语系' and 专业方向 not like'计算机系' 
go 
select * from st_table2 where 名称 like '张_' 
select * from st_table2 where 名称 like '赵__' 
select * from st_table2 where 名称 like '赵%' 
select * from st_table2 where 备注 like '学习%' or 备注 like '外语系%' 
go 
--涉及空值的查询 
select * from st_table2 where  备注 is null 
select * from st_table2 where  not 备注 is null 
select * from st_table2 where  备注 is not null 
go 
--多重条件查询 
select * from st_table2 where not 名称 like '张_' 
select * from st_table2 where not 备注 like '学习%' or 备注 like '外语系%' 
go 
--用查询结果生成新表 
select * into new_st_table2 from st_table2 where  not 备注 is null 
go 
--删除该表 
drop table new_st_table2 
go 
select * into new2_st_table2 from st_table2 
    where 专业方向 not like '外语系' and 专业方向 not like'计算机系' 
go 
--删除该表 
drop table new2_st_table2 
go 
--新表前加"#"是创建临时表 
select 学号,名称,专业方向,高考分数  
    into #new3_st_table2  
    from st_table2  
    where  not 备注 is null 
select * from #new3_st_table2 
go 
--删除new3_st_table2表 
drop table #new3_st_table2 
/* 
完整的select 语句的基本语法格式 
 
虽然select 语句的完整语法较复杂，但是其主要的语法格式可归纳如下： 
 
SELECT select_list 
[into new_table_name] 
 FROM table_list 
[WHERE search_conditions] 
[GROUP BY group_by_expression] 
[HAVING search_condition] 
[ORDER BY  order_expression [asc|desc] ] 
 
SELECT select_list 描述结果集的列，它是一个逗号分隔的表达式， 
在选择列表中使用 “*”表达式指定返回源表中所有的列 
 
[INTO new_table_name] 用于指定使用结果集来创建一个新表，new_table_name是新表的名称 
 
FROM table_list 结果集数据来源于哪些表或视图。 
 
[WHERE search_conditions]查询条件 
 
GROUP BY group_by_expression    根据列中的值将结果分组。 
 
HAVING search_conditions 结果集附加筛选，通常与GROUP BY 一起使用。 
 
ORDER BY  order_expression [ASC|DESC] 对结果进行分组ASC 和DESC 关键字用于指定行是 
                                      按升序还是降序排序。 
*/ 
 
 
 
--对结果进行分组 
select 专业方向,名称,高考分数 from st_table2 
    group by 专业方向,名称,高考分数 
go     
--HAVING 筛选条件表达式对结果集分组  
select 专业方向,名称,高考分数 from st_table2 
    group by 专业方向,名称,高考分数 
    having 专业方向 not like '动漫' 
go 
select 专业方向,名称,高考分数 from st_table2 
    group by 专业方向,名称,高考分数 
    having 专业方向<>'动漫' 
go     
--对查询的结果排序 
select * from st_table2 order by 高考分数 asc  --升序排列 
select * from st_table2 order by 高考分数 desc  --升序排列 
go 
--对数据进行统计 
select top 3 * from st_table2 order by 高考分数 desc  --升序排列 
select COUNT(*) from st_table2 
select COUNT(学号) as 高考总人数 from st_table2 
select sum(高考分数) as 高考总分数 from st_table2 
select AVG(高考分数) as 高考平均分 from st_table2 
select max(高考分数) as 高考最高分 from st_table2 
select min(高考分数) as 高考最低分 from st_table2 
go 
 
--使用COMPUTE  
---对查询结果集中的所有记录进行汇总统计 
select *from st_table2 
    order by 高考分数 desc 
    compute avg(高考分数) 
go 
select 专业方向,COUNT(*) 系部总人数,AVG(高考分数) 系部平均成绩 
    from st_table2 
    group by 专业方向 
    compute count(count(*))  
    compute sum(count(*))  
    compute avg(avg(高考分数))  
    /*这个与 select AVG(高考分数) as 高考平均分 from st_table2 
    算出来的结果为什么不同  ? ? ? */ 
go     
select 专业方向,名称,高考分数 from st_table2 where 专业方向='外语系' 
select 专业方向,名称,高考分数 from st_table2 where 专业方向 like '外语系' 
go 
select  distinct 专业方向,高考分数  
    from st_table2  
    where 专业方向='外语系'     
    compute count(专业方向) 
 
go 
--清空数据 
truncate table st_table2 
go 
--插入数据 
insert st_table2 values('张学友','网络','01','没有','411') 
insert st_table2 values('刘德华','计算机','02','没有','412') 
insert st_table2 values('舒淇','计算机','01','没有','413') 
insert st_table2 values('梁咏琪','动漫','02','没有','431') 
insert st_table2 values('杨千嬅','计算机','01','没有','465') 
insert st_table2 values('李宇春','动漫','02','没有','485') 
insert st_table2 values('蔡依林','网络','01','没有','468') 
insert st_table2 values('郑源','计算机','02','没有','510') 
insert st_table2 values('陈楚生','动漫','01','没有','550') 
insert st_table2 values('张韶涵','计算机','02','没有','421') 
insert st_table2 values('猛非','动漫','01','没有','423') 
insert st_table2 values('郑秀文','网络','02','没有','411') 
insert st_table2 values('林俊杰','计算机','01','没有','511') 
insert st_table2 values('羽泉','计算机','01','没有','500') 
insert st_table2 values('郭富城','网络','02','没有','400') 
insert st_table2 values('黄品源','动漫','02','没有','589') 
insert st_table2 values('梁朝伟','计算机','02','没有','530') 
insert st_table2 values('李克勤','网络','01','没有','520') 
insert st_table2 values('陈小春','国际金融','02','没有','512') 
insert st_table2 values('刘若英','证券期货','02','没有','421') 
insert st_table2 values('刘嘉玲','房地产金融','01','没有','428') 
insert st_table2 values('谭咏麟','房地产金融','02','没有','498') 
insert st_table2 values('张学友','证券期货','01','没有','454') 
insert st_table2 values('张卫健','证券期货','02','没有','515') 
insert st_table2 values('周传雄','房地产金融','01','没有','532') 
insert st_table2 values('周星驰','国际金融','02','没有','423') 
insert st_table2 values('游鸿明','房地产金融','02','没有','477') 
insert st_table2 values('言承旭','国际金融','02','没有','488') 
insert st_table2 values('许志安','国际金融','01','没有','582') 
insert st_table2 values('叶倩文','房地产金融','01','没有','495') 
insert st_table2 values('叶世荣','房地产金融','02','没有','499') 
insert st_table2 values('张雨生','证券期货','02','没有','531') 
insert st_table2 values('周润发','国际金融','01','没有','531') 
insert st_table2 values('张信哲','证券期货','01','没有','424') 
insert st_table2 values('周渝民','证券期货','02','没有','412') 
insert st_table2 values('太极乐队','证券期货','02','没有','423') 
go 
select * from st_table2 
go 
--数据的删除 
delete st_table2 where 系部代码='03' 
delete st_table2  --删除表中的所有数据 
 
go 
--删除st_table2表 
drop table st_table2 
go 
---======================================================================== 
 
--学校数据库表----------- 
 
--创建系部表 
create table 系部 
( 
    系部代码 char(6) not null primary key, 
    系部名称 varchar(30) not null, 
    系主任 char(8) 
) 
--drop table 系部 
go 
--创建专业表 
create table 专业 
( 
    专业代码 char(4) primary key not null, 
    专业名称 char(20) not null, 
    系部代码 char(6) constraint fk01 references 系部(系部代码) 
) 
--drop table 专业 
go 
/* 
--另外一种方法创建主键与外键 
alter table 系部  
    add constraint pk01  
    primary key clustered(系部代码)  
 
alter table 专业 
    add constraint fk_系部代码 
    foreign key(系部代码) 
    references 系部(系部代码) 
*/ 
 
--创建班级表 
create table 班级 
( 
    班级代码 char(9) not null primary key , 
    班级名称 varchar(20) , 
    专业代码 char(4) constraint wz1 references 专业(专业代码), 
    系部代码 char(6)  constraint wz2 references 系部(系部代码), 
    备注     varchar(50)  
) 
go 
--创建学生表 
create table 学生 
( 
    学号 char(12) not null primary key , 
    姓名 char(8), 
    性别 char(2), 
    出生日期 datetime, 
    入学时间 datetime, 
    班级代码 char(9) constraint wz3 references 班级(班级代码), 
    系部代码 char(6) constraint wz4 references 系部(系部代码), 
    专业代码 char(4) constraint wz5 references 专业(专业代码), 
    高考分数 int  
) 
 
/* 
drop table 学生 
alter table 学生 alter column 学号 int 
*/ 
go 
--删除表 
drop table 学生 
drop table 班级 
drop table 专业 
drop table 系部 
 
go 
--切换数据库 
use master 
go 
--删除数据库st_db 
drop database st_db 
 
------=================================================================== 
 
use master 
go 
if exists (select name from sys.databases where name=N'stt_db') 
drop database stt_db 
go 
create database stt_db 
 
--use master 
--drop database stt_db 
go 
use stt_db 
 
go 
  --学校数据库表  ------------------------------------------------------ 
 create table 系部 
( 
    系部代码 char(6) not null primary key, 
    系部名称 varchar(30) not null , 
    系主任 char(8)  
) 
go 
create table 专业 
( 
    专业代码 char(4) not null primary key, 
    专业名称 varchar(20) not null, 
    系部代码 char(6) constraint wz11 references 系部(系部代码) 
) 
go 
create table 班级 
( 
    班级代码 char(9) not null primary key , 
    班级名称 varchar(20) , 
    专业代码 char(4) constraint wz1 references 专业(专业代码), 
    系部代码 char(6)  constraint wz2 references 系部(系部代码), 
    备注     varchar(50)  
) 
go 
create table 学生 
( 
    学号 char(12) not null primary key , 
    姓名 char(8), 
    性别 char(2), 
    出生日期 datetime, 
    入学时间 datetime, 
    班级代码 char(9) constraint wz3 references 班级(班级代码), 
    系部代码 char(6) constraint wz4 references 系部(系部代码), 
    专业代码 char(4) constraint wz5 references 专业(专业代码), 
    高考分数 int  
) 
go 
 
insert 系部(系部代码,系部名称,系主任) values('01','计算机系','老张')  
go 
insert 系部(系部代码,系部名称,系主任) values('02','经济管理系','老陈')  
go 
insert 系部(系部代码,系部名称,系主任) values('03','机械系','老李')  
go 
insert 系部(系部代码,系部名称,系主任) values('04','数学系','老梁')  
go 
  
 
insert 专业(专业代码,专业名称,系部代码) values('0101','软件工程','01') 
go 
insert 专业(专业代码,专业名称,系部代码) values('0102','网络工程','01') 
go 
insert 专业(专业代码,专业名称,系部代码) values('0103','信息工程','01') 
go 
insert 专业(专业代码,专业名称,系部代码) values('0201','工商管理','02') 
go 
insert 专业(专业代码,专业名称,系部代码) values('0202','物流管理','02') 
go 
insert 专业(专业代码,专业名称,系部代码) values('0301','模具加工','03') 
go 
insert 专业(专业代码,专业名称,系部代码) values('0302','机电一体化','03') 
go 
insert 专业(专业代码,专业名称,系部代码) values('0401','应用数学','04') 
go 
insert 专业(专业代码,专业名称,系部代码) values('0402','金融数学','04') 
go 
 
 
insert 班级(班级代码,班级名称,专业代码,系部代码,备注) 
values('010101','软件工程1班','0101','01','暂无') 
go 
insert 班级(班级代码,班级名称,专业代码,系部代码,备注) 
values('010102','软件工程2班','0101','01','暂无') 
go 
insert 班级(班级代码,班级名称,专业代码,系部代码,备注) 
values('010103','网络工程1班','0102','01','暂无') 
go 
insert 班级(班级代码,班级名称,专业代码,系部代码,备注) 
values('010104','网络工程2班','0102','01','暂无') 
go  
insert 班级(班级代码,班级名称,专业代码,系部代码,备注) 
values('010105','信息工程1班','0103','01','暂无') 
go 
insert 班级(班级代码,班级名称,专业代码,系部代码,备注) 
values('010106','工商管理1班','0201','02','暂无') 
go 
insert 班级(班级代码,班级名称,专业代码,系部代码,备注) 
values('010107','物流管理1班','0202','02','暂无') 
go 
insert 班级(班级代码,班级名称,专业代码,系部代码,备注) 
values('010108','模具加工1班','0301','03','暂无') 
go 
insert 班级(班级代码,班级名称,专业代码,系部代码,备注) 
values('010109','应用数学1班','0401','04','暂无') 
go 
insert 班级(班级代码,班级名称,专业代码,系部代码,备注) 
values('0101010','金融数学1班','0402','04','暂无') 
go 
insert 班级(班级代码,班级名称,专业代码,系部代码,备注) 
values('0101011','金融数学2班','0402','04','暂无') 
go 
 
 
insert 学生 values('010101000000','刘德华','男','1988-5-5','2010-9-1','010101','01','0101',356) 
go 
insert 学生 values('010101000001','张学友','男','1988-1-4','2010-9-1','010101','01','0101',354) 
go 
insert 学生 values('010101000002','梁静茹','女','1988-2-1','2010-9-1','010101','01','0101',342) 
go 
insert 学生 values('010101000003','陈奕迅','男','1983-5-3','2010-9-1','010102','01','0101',441) 
go 
insert 学生 values('010101000004','张韶涵','女','1987-8-6','2010-9-1','010102','01','0101',354) 
go 
insert 学生 values('010101000005','林俊杰','男','1988-6-6','2010-9-1','010102','01','0101',498) 
go 
insert 学生 values('010101000006','孙燕姿','女','1984-5-3','2010-9-1','010106','02','0201',522) 
go 
insert 学生 values('010101000007','周华健','男','1986-8-6','2010-9-1','010106','02','0201',378) 
go 
insert 学生 values('010101000008','尚雯婕','女','1988-6-6','2010-9-1','010106','02','0201',365) 
go  
insert 学生 values('010101000009','任贤齐','男','1984-5-3','2010-9-1','010108','03','0301',421) 
go 
insert 学生 values('010101000010','魏晨','男','1986-8-6','2010-9-1','010108','03','0301',574) 
go 
insert 学生 values('010101000011','庞龙','男','1988-6-6','2010-9-1','010108','03','0301',452) 
go  
insert 学生 values('010101000012','刘若英','女','1988-5-3','2010-9-1','0101011','04','0402',354) 
go 
insert 学生 values('010101000013','李圣杰','男','1989-8-6','2010-9-1','0101011','04','0402',324) 
go 
insert 学生 values('010101000014','克群','男','1989-2-9','2010-9-1','0101011','04','0402',321) 
go  
 
 
 
--外连接查询表-------------------------------------------------------- 
create table 产品 
( 
    产品编号 char(9) not null , 
    产品名称 varchar(20)  not null , 
) 
go 
create table 产品销售 
( 
    产品编号 char(9) not null , 
    销量 int  
) 
go 
 insert 产品 values('001','显视器') 
 insert 产品 values('002','键盘') 
 insert 产品 values('003','鼠标') 
insert 产品销售 values('001','25') 
insert 产品销售 values('003','35') 
insert 产品销售 values('005','30') 
 
go 
--stt_table ------------------------------------------------------ 
create table stt_table 
( 
    学号 int not null identity, 
    姓名 char(8) not null, 
    专业方向 varchar(50) not null, 
    系部代码 char(2) not null, 
    备注 varchar(50), 
    高考分数 int  
) 
go 
insert stt_table values('张学友','网络','01','没有','411') 
insert stt_table values('刘德华','计算机','02','没有','412') 
insert stt_table values('舒淇','计算机','01','没有','413') 
insert stt_table values('梁咏琪','动漫','02','没有','431') 
insert stt_table values('杨千嬅','计算机','01','没有','465') 
insert stt_table values('李宇春','动漫','02','没有','485') 
insert stt_table values('蔡依林','网络','01','没有','468') 
insert stt_table values('郑源','计算机','02','没有','510') 
insert stt_table values('陈楚生','动漫','01','没有','550') 
insert stt_table values('张韶涵','计算机','02','没有','421') 
insert stt_table values('猛非','动漫','01','没有','423') 
insert stt_table values('郑秀文','网络','02','没有','411') 
insert stt_table values('林俊杰','计算机','01','没有','511') 
insert stt_table values('羽泉','计算机','01','没有','500') 
insert stt_table values('郭富城','网络','02','没有','400') 
insert stt_table values('黄品源','动漫','02','没有','589') 
insert stt_table values('梁朝伟','计算机','02','没有','530') 
insert stt_table values('李克勤','网络','01','没有','520') 
insert stt_table values('陈小春','国际金融','02','没有','512') 
insert stt_table values('刘若英','证券期货','02','没有','421') 
insert stt_table values('刘嘉玲','房地产金融','01','没有','428') 
insert stt_table values('谭咏麟','房地产金融','02','没有','498') 
insert stt_table values('张学友','证券期货','01','没有','454') 
insert stt_table values('张卫健','证券期货','02','没有','515') 
insert stt_table values('周传雄','房地产金融','01','没有','532') 
insert stt_table values('周星驰','国际金融','02','没有','423') 
insert stt_table values('游鸿明','房地产金融','02','没有','477') 
insert stt_table values('言承旭','国际金融','02','没有','488') 
insert stt_table values('许志安','国际金融','01','没有','582') 
insert stt_table values('叶倩文','房地产金融','01','没有','495') 
insert stt_table values('叶世荣','房地产金融','02','没有','499') 
insert stt_table values('张雨生','证券期货','02','没有','531') 
insert stt_table values('周润发','国际金融','01','没有','531') 
insert stt_table values('张信哲','证券期货','01','没有','424') 
insert stt_table values('周渝民','证券期货','02','没有','412') 
insert stt_table values('太极乐队','证券期货','02','没有','423') 
 
go 
select * from 学生 
select * from 班级 
go 
--交叉连接查询 
select * from 学生 cross join 班级  
select 学生.学号,学生.姓名,班级.班级名称  
    from 学生 cross join 班级    
select 学生.学号,学生.姓名,班级.班级名称  
    from 学生 cross join 班级 
    where 学生.班级代码=班级.班级代码 
go 
--自然连接 
select 学生.学号,学生.姓名,班级.班级名称  
    from 学生 join 班级 
    on 学生.班级代码=班级.班级代码 
go 
--自身连接查询 
select a.学号,a.姓名,a.性别,b.班级代码,b.专业代码,b.高考分数 
    from 学生 as a join 学生 as b 
    on a.学号=b.学号 
go     
select  distinct 学号,姓名,性别,班级代码,专业代码,高考分数 
    from 学生 
go 
--外连接查询 
--左外连接(LEFT OUTER JOIN) 
select * from 产品 left join 产品销售 
    on 产品.产品编号=产品销售.产品编号 
go 
--右外连接(LEFT OUTER JOIN) 
select * from 产品 right join 产品销售 
    on 产品.产品编号=产品销售.产品编号 
go 
--完全外连接(FULL OUTER JOIN) 
select * from 产品 full join 产品销售 
    on 产品.产品编号=产品销售.产品编号 
go 
--复合连接条件查询 
SELECT 学生.学号,学生.姓名,学生.性别,班级.班级名称,专业.专业名称,系部.系部名称 
FROM 学生 JOIN 班级 ON 学生.班级代码=班级.班级代码 
          JOIN 专业 ON 学生.专业代码=专业.专业代码 
          JOIN 系部 ON 学生.系部代码=系部.系部代码 
go           
SELECT 学生.学号,学生.姓名,学生.性别,班级.班级名称 
FROM 学生 JOIN 班级 ON 学生.班级代码=班级.班级代码 and 学生.性别='男' 
go 
--合并结果集 
/*参加UNION操作的各结果集的列数必须相同，对应的数据类型也必须相同, 
系统将自己动去掉并集的得复记录。 
*/ 
 select 学生.姓名,学生.性别,学生.出生日期  into new_table from 学生 
    union 
    select 学生.姓名,学生.性别,学生.出生日期 from 学生 
--打开new_table表,修改里面的数据,然后查询结果 
go 
 select 姓名,性别,出生日期 from new_table 
    union 
    select 姓名,性别,出生日期 from new_table 
 
go     
drop table new_table 
 
--子查询 
go 
select * from 学生 where 班级代码 in 
    ( 
        select 班级代码 from 班级 where 专业代码 in 
        ( 
            select 专业代码 from 专业 where 系部代码 in 
            ( 
                select 系部代码 from 系部 where 系部代码='01' 
            ) 
        ) 
    ) 
go 
select * from 学生 where 系部代码='01' 
--如果学生表中有 系部代码 这个字段,可以直接用这种方法 
go 
--带有比较运算符的子查询 
select * from 学生 where 出生日期 > 
    (select 出生日期 from 学生 where 姓名='刘德华')  
    order by 出生日期 asc 
 
go 
--带有ANY 或 ALL运算符的子查询 
select * from 学生 where 高考分数 > any 
    (select 高考分数 from 学生 where 学号 in(010101000009,010101000005)) 
select * from 学生 where 高考分数 > all 
    (select 高考分数 from 学生 where 学号 in(010101000005,010101000009)) 
 
select * from 学生  order by 高考分数 desc 
 
go 
--带有EXISTS运算符的子查询 
select * from 学生 where exists 
    (select * from 学生 where 姓名='刘德华') 
select * from 学生 where exists 
    (select * from 学生 where 姓名='郎咸平') 
go     
select * from 学生 as a where exists 
    (select * from new_table where a.姓名=new_table.姓名) 
go     
select * from 系部 where 系部代码= 
    (select 系部代码 from 专业 where 专业代码= 
        (select 专业代码 from 班级 where 班级代码= 
            (select 班级代码 from 学生 where 姓名='刘德华'))) 
select * from 学生 where 姓名='刘德华' 
 
 
 
go 
--使用SQL 语句创建唯一约束 
alter table 系部 
    add constraint  wywy 
    unique nonclustered(系部名称) 
go 
--创建检查约束 
alter table stt_table 
    add constraint ck_name 
    check(高考分数>300 and 高考分数<600) 
go     
--创建默认约束 
alter table stt_table 
    add constraint df_constraint 
    default '这就这个字段的默认值' for 备注  
 
go 
--查看约束的定义 
EXEC sp_help wywy 
EXEC sp_help ck_name 
/*如果该约束有具体的定义和文本，那么可以用sp_helptext 
来查看其语法格式为：           */ 
EXEC sp_help df_constraint 
EXEC sp_helptext df_constraint     
go     
--删除约束 
alter table stt_table 
    drop constraint df_constraint 
alter table stt_table 
    drop constraint ck_name 
alter table 系部 
    drop constraint wywy 
go 
--创建规则 
create rule gz 
    as @a>300 and @a<600 
     
go     
--绑定规则 
execute sp_bindrule 'gz','stt_table.高考分数' 
 
go 
select * into stt_table2 from stt_table 
execute sp_bindrule 'gz','stt_table2.高考分数'  
 
go 
--解绑规则 
execute sp_unbindrule 'stt_table.高考分数' 
execute sp_unbindrule 'stt_table2.高考分数' 
drop table stt_table2 
 
go 
--删除规则 
drop rule gz 
 
go 
--创建默认 
create default df_sex 
    as '男' 
 
go 
execute sp_bindefault 'df_sex','学生.性别' 
 
go 
--解除默认绑定 
execute sp_unbindefault '学生.性别' 
 
go 
--删除默认 
drop default df_sex 
 
go 
--创建索引 
/* 
CREATE [UNIQUE][CLUSTERED][NONCLUSTERED]     INDEX 索引名 
        ON 表名(列名) 
*/ 
select * into new_table from stt_table 
create clustered index 姓名_索引 
    on new_table(姓名) 
go 
drop index new_table.姓名_索引 
 
go 
--delete new_table where 姓名='张学友' 
--select * from new_table where 姓名='张学友' 
delete new_table where 学号='23' 
create unique clustered index 姓名_唯一索引 
    on new_table(姓名) 
go 
----查看索引信息 
exec sp_helpindex new_table 
go 
drop index new_table.姓名_唯一索引 
 
 
 
go 
--创建视图 
create view v1  
    as 
    select 学生.学号,学生.姓名,班级.班级名称,专业.专业名称,系部.系部名称 
    from 学生  
        join 班级 on 学生.班级代码=班级.班级代码 
        join 专业 on 学生.专业代码=专业.专业代码 
        join 系部 on 学生.系部代码=系部.系部代码 
go 
select * from v1 
/* 
CREATE VIEW  视图名 
    WITH [ENCRYPTION] [SCHEMABINDING] 
    AS 查询语句 
     [ENCRYPTION]      加密视图 
    [SCHEMABINDING]    视图及表的架构绑定 
*/ 
 
--打开new_table的IDENTITY_INSERT的属性 
go 
SET IDENTITY_INSERT new_table ON 
 
--修改new_table的列属性 
go 
alter table new_table  alter column 专业方向 varchar(50) null 
alter table new_table  alter column 系部代码 varchar(2) null 
 
--创建视图v2 
go 
create view v2 as select * from new_table 
 
--创建加密视图 
go     
create view 加密视图 with encryption 
    as select * from new_table 
 
--创建视图及表的架构绑定 
go 
create view 架构绑定 with schemabinding 
    as select 学号,姓名,高考分数 from dbo.new_table 
     
--查询视图 
go 
select * from v1 
select * from v2     
select * from new_table 
select * from 架构绑定 
 
--向视图插入数据 
go 
insert v2(学号,姓名,高考分数) values('25','郎小平','423') 
 
--修改视图数据 
go 
update v2 set 姓名='胡锦涛' where 姓名='郎小平' 
 
--删除视图数据 
go 
delete v2 where 姓名='胡锦涛' or 姓名='郎小平' 
 
--视图信息 
go 
exec sp_helptext v1 
exec sp_helptext v2 
exec sp_helptext 加密视图 
exec sp_helptext 架构绑定 
 
--删除视图 
go 
drop view v1 
drop view v2 
drop view 加密视图 
drop view 架构绑定 
 
--修改new_table属性 
go 
alter table new_table  alter column 专业方向 varchar(50) not null 
alter table new_table  alter column 系部代码 varchar(2) not null 
 
--关闭new_table的IDENTITY_INSERT属性 
go 
SET IDENTITY_INSERT new_table off 
go 
 
 
--测试资料 
CREATE TABLE t1(ID int IDENTITY,A int) 
INSERT t1 VALUES(1) 
INSERT t1 VALUES(2) 
INSERT t1 VALUES(3) 
DELETE FROM t1 WHERE A=2 
GO 
 
--将ID=3的记录的ID值改为2 
SET IDENTITY_INSERT t1 ON 
INSERT t1(ID,A) SELECT 2,A FROM t1 WHERE ID=3 
DELETE FROM t1 WHERE ID=3 
SET IDENTITY_INSERT t1 OFF 
SELECT * FROM t1 
DROP TABLE t1 
go 
 
 
--创建、执行、修改、删除简单的存储过程 
/* 
CREATE PROC 储存过程名 
    [@parameter data_type] [=default] [OUTPUT][,….] 
    AS 
    SQL语句 
*/ 
go 
--创建存储过程 
create proc p 
    as 
    select 学生.学号,学生.姓名,班级.班级名称,专业.专业名称,系部.系部名称 
    from 学生 
        join 班级 on 学生.班级代码=班级.班级代码 
        join 专业 on 学生.专业代码=专业.专业代码 
        join 系部 on 学生.系部代码=系部.系部代码 
go         
--创建含变量及参数的存储过程 
create proc p1 
    @sex varchar(2), 
    @id varchar(8) 
    as 
    select 学生.学号,学生.姓名,班级.班级名称,专业.专业名称,系部.系部名称 
    from 学生 
        join 班级 on 学生.班级代码=班级.班级代码 and 学生.性别=@sex 
        join 专业 on 学生.专业代码=专业.专业代码 
        join 系部 on 学生.系部代码=系部.系部代码 and 系部.系部代码=@id 
go         
create proc p2 
    @setname varchar(10), 
    @getnum varchar(8) output 
    as 
    select @getnum=学号 from 学生 where 姓名=@setname 
go 
--执行存储过程 
execute p 
execute p1 '女','02' 
execute p2 '刘德华','' 
go 
--查看存储过程 
execute sp_helptext p 
execute sp_helptext p1 
execute sp_helptext p2 
execute sp_depends p 
execute sp_depends p1 
execute sp_depends p2 
execute sp_help p 
execute sp_help p1 
execute sp_help p2 
go 
--删除存储过程 
drop procedure p 
drop procedure p1 
drop procedure p2 
 
--创建触发器 
/* 
CREATE TRIGGER trigger_name 
    ON { table | view } 
    FOR  {[INSERT],[DELETE],[UPDATE]} 
    SQL语句 
*/ 
go 
create trigger myinsert 
    on 产品 
    for insert 
    as 
    declare @a char(10) 
    select @a=产品编号 from inserted 
    insert into 产品销售 values(@a,0) 
go 
create trigger mydelete 
    on 产品 
    for delete 
    as 
    declare @a char(10) 
    select @a=产品编号 from deleted 
    delete 产品销售 where @a=产品编号 
 
     
go 
select * from 产品 
select * from 产品销售 
go 
--当执行插入操作时,触发器响应,产品销售表中也插入两条数据 
insert 产品 values('004','摄相头') 
insert 产品 values('008','手机') 
go 
--删除插入的数据时,触发器响应,产品销售表中也插入两条数据 
--注意,这里删除语句一定要写成两行,不能写成(产品编号='004' or 产品编号='008') 
delete 产品 where 产品编号='004' 
delete 产品 where 产品编号='008' 
go 
delete 产品销售 where 产品编号='004' or 产品编号='008' 
go 
--查看触发器信息 
execute sp_helptrigger 产品 
--删除触发器 
go 
drop trigger mydelete 
drop trigger myinsert 
go 
 
--SQL Server 的各种内置函数 
 
--聚合函数 
/* 
AVG ()--平均值 
    COUNT ()--返回行中项目的数量 
    MAX ()--最大值 
    MIN ()--最小值 
    SUM ()--求和 
*/ 
select 班级名称,avg(高考分数) from 班级  
    join 学生 on 班级.班级代码=学生.班级代码 
    group by 班级名称 
select 班级名称,max(高考分数) from 班级  
    join 学生 on 班级.班级代码=学生.班级代码 
    group by 班级名称 
select 班级名称,min(高考分数) from 班级  
    join 学生 on 班级.班级代码=学生.班级代码 
    group by 班级名称 
select 班级名称,sum(高考分数) from 班级  
    join 学生 on 班级.班级代码=学生.班级代码 
    group by 班级名称 
go 
--数学函数 
/* 
    ABS (x)--返回绝对值 
    CEILING(x)--返回大于或等于所给数字的最小整数 
    PI ()--pi值 
    POWER (x,y)--返回x的y次方 
    RAND ()--返回0~1之间的随机数 
*/ 
select ABS(8) 绝对值 
select ABS(-8) as 绝对值 
go 
select CEILING(8.5) 返回结果 
select CEILING(-8.5) as 返回结果 
go 
select PI() pi_的值 
go 
select POWER(2,3) 返回值 
go 
select RAND() 从0到1的随机数 
go 
--配置函数 
SELECT @@VERSION --获取当前数据库版本 
SELECT @@LANGUAGE --当前语言 
go 
--时间函数 
select getdate()   当前时间 
select day(getdate())   取出天 
select month(getdate()) 取出月 
select year(getdate())    取出年 
select dateadd(d,3,getdate()) as wawa_dateadd_加三天  
 -- ‘d’表示天,‘m’表示月,‘yy’表示年 
select datename(d,'2010-07-15')    取出时间的某一部分 
/* 
Year Yy yyyy 年 1753 ~ 9999  
Quarter Qq q 季 1 ~ 4  
Month Mm m 月1 ~ 12  
Day of year Dy y 一年的日数,一年中的第几日 1-366  
Day Dd d 日，1-31  
Weekday Dw w 一周的日数，一周中的第几日 1-7  
Week Wk ww 周，一年中的第几周 0 ~ 51  
Hour Hh h 时0 ~ 23  
Minute Mi n 分钟0 ~ 59  
Second Ss s 秒 0 ~ 59  
Millisecond Ms - 毫秒 0 ~ 999  
*/ 
 
--字符串函数 
Select LEN('str1111') 返回字符串的字符个数 
Select lower('ABC')  小写,lower('Abc')  小写,upper('Abc') 大写,upper('abc') 大写 --转换大小写  
select ltrim('           左边没有空格')   去空格 
select rtrim('右边没有空格    ')  去空格 
select ltrim(rtrim('    左右都没有空格    ')) 去空格 
select left('sql server',3) 取左,right('sql server',6) 取右 --取左或者取右 
Select replace('原字符串','原','这是一个')   字符串替换 
Select replicate('go',5) 指定字符重复5次  --指定的次数重复字符 
select reverse('这是字符串') 字符串返序排列 --将定字符串返序排列 
select substring('这是一个原字符串',2,3) 截取第2个位置开始的3个数字  --指定截取指字字符串 
 
go 
--排名函数 
/* 
rank()返回结果集的分区内每行的排名 
            rank() over (order by 列名) 
*/ 
select RANK() over(order by 高考分数 desc) 名次,姓名,高考分数 from 学生 
 
go     
drop table new_table 
go 
use master 
go 
drop database stt_db