--创建数据库命令
create database j1216
on
(
name=j1216,
filename='E:\shuju\j1216\j1216.mdf',
size=10,
maxsize=50,
filegrowth=5
)
log on
(
name=j1216_log,
filename='E:\shuju\j1216\j1216_log.ldf',
size=5,
maxsize=25,
filegrowth=5
)
go
-----------------------------------------------------------------------------------
--创建表
--创建学生表
create table tb_student
(
id int identity(1,1) primary key not null,
s_name varchar(20),
sex varchar(10),
age int
)
--创建成绩表
create table tb_sc
(
tno varchar(20) primary key not null,
grade int,
sno varchar(20) not null,
c_no int not null
)
--创建课程表
create table tb_course
(
c_id int identity(1,1) primary key not null,
c_no int not null,
c_name varchar(50)
)
--创建部门表
create table tb_depart
(
id int identity(1,1) primary key not null,
d_no int ,
d_name varchar(50)
)
--创建成绩表
create table tb_grade
(
g_no varchar(20) primary key not null,
s_no varchar(20) not null,
w_grade int,
l_grade int
)
--创建订单表
create table OrderDetails
(
OrderD int primary key not null,
ProductD int,
UnitPrice money,
Quantity int
)
--创建货仓表
create table Products
(
ProductD int primary key not null,
UnetislnStock int
)
---------------------------------------------------------------------------------
--删除表
select * from  tb_student
select * from  tb_user
select * from OrderDetails;
select * from tb_sc;
drop table tb_sc
drop table tb_student_clone
drop table tb_student_clone2
 
create table tb_user
(
id int,
uname varchar(20)
)
--------------------------------------------------------------------------------
--使用数据库
 use j1216
---------------------------------------------------------------------------------
--修改表的字段类型
alter table tb_student alter column s_name varchar(30)
--删除字段
alter table tb_student drop column age
--添加字段
alter table tb_student add  age int
alter table tb_student add tno varchar(20)
alter table tb_student add d_no int
--字段重命名
exec sp_rename 'tb_student.s_name','sname','column'
--------------------------------------------------------------------------------
-------给表添加主键
----添加列语法：ALTER TABLE 表名 Add 列名 类型 ...
alter table tb_user add userid int not null identity(10,1);
 
------ 语法：   alter table 表名 add constraint 约束名 primary key (你的主键);  
alter table tb_user add constraint pk_tb_user primary key (userid);
 
--给表tb_student添加外键
----- 语法：alter table 表名 add constraint 约束名 foreign key (你的外键) references (表名)(字段名)
------ 指定表中的数据是否用新添加的或重新启用的 FOREIGN KEY 或 CHECK 约束进行验证。如果没有指定，对于新约束，假定为 WITH CHECK，对于重新启用的约束，假定为 WITH NOCHECK
alter table tb_student with check add constraint pk_tno
foreign key(tno) references tb_sc(tno)
 
--给表OrderDetails添加外键
alter table OrderDetails with nocheck add constraint pk_ProductD
foreign key(ProductD) references Products(ProductD)
---------------------------------------------------------------------------------
--向表中添加数据
--向学生表中添加数据
insert into  tb_student(s_name,sex,age,tno,d_no) values('张三','男',21,'1203',15);
insert into  tb_student(s_name,sex,age,tno,d_no) values('欧阳锋','男',120,'1208',63);
insert into  tb_student(s_name,sex,age,tno,d_no) values('欧阳亚雄','男',27,'1203',42);
insert into  tb_student(s_name,sex,age,tno,d_no) values('DB_i11','男',32,'1207',77);
insert into  tb_student(s_name,sex,age,tno,d_no) values('DB_ii1111','男',23,'1201',56);
--向成绩表中添加数据
insert into tb_sc(tno,grade,sno,c_no) values('1201',85,'1',1000)
insert into tb_sc(tno,grade,sno,c_no) values('1202',80,'2',1001)
insert into tb_sc(tno,grade,sno,c_no) values('1203',77,'1',1000)
insert into tb_sc(tno,grade,sno,c_no) values('1204',65,'2',1001)
insert into tb_sc(tno,grade,sno,c_no) values('1205',45,'1',1000)
insert into tb_sc(tno,grade,sno,c_no) values('1206',98,'1',1000)
insert into tb_sc(tno,grade,sno,c_no) values('1207',78,'2',1001)
insert into tb_sc(tno,grade,sno,c_no) values('1208',86,'2',1001)
--向课程表中添加数据
insert into tb_course(c_no,c_name) values(1000,'java')
insert into tb_course(c_no,c_name) values(1001,'c++')
insert into tb_course(c_no,c_name) values(1002,'c#')
insert into tb_course(c_no,c_name) values(1003,'.net')
insert into tb_course(c_no,c_name) values(1004,'php')
insert into tb_course(c_no,c_name) values(1005,'android')
--向院系表中添加信息
insert into tb_depart(d_no,d_name) values(1111,'计算机学院')
insert into tb_depart(d_no,d_name) values(2222,'管理学院')
insert into tb_depart(d_no,d_name) values(3333,'旅游学院')
insert into tb_depart(d_no,d_name) values(4444,'烹饪学院')
--向货仓表中添加数据
insert into Products(ProductD,UnetislnStock) values(1,15)
insert into Products(ProductD,UnetislnStock) values(2,65)
insert into Products(ProductD,UnetislnStock) values(3,20)
insert into Products(ProductD,UnetislnStock) values(4,50)
--向订单表中添加数据
insert into OrderDetails(OrderD, ProductD, UnitPrice, Quantity) values(10522,1,31.00,6)
insert into OrderDetails(OrderD, ProductD, UnitPrice, Quantity) values(10523,2,9.65,7)
insert into OrderDetails(OrderD, ProductD, UnitPrice, Quantity) values(10524,3,30.00,10)
insert into OrderDetails(OrderD, ProductD, UnitPrice, Quantity) values(10525,4,19.00,5)
-----------------------------------------------------------------------------
--循环向tb_grade表中添加数据
--声明变量
declare @t int
declare @a int
declare @s int
   --随即产生1-100之间的整数
   set @t=rand()*100
   set @a=rand()*100
   print @t;
   print @a;
   set @s=0
   print @s;
while(@s<100)
begin
   if(@t>10 and @a>10)
       begin
             -----一般来讲cast()适用范围略广，convert(),一般用于日期和字符串之间进行转换SELECT CAST(CONVERT(CHAR(10),CURRENT_TIMESTAMP,121) AS DATETIME
          insert into tb_grade(g_no, s_no, w_grade, l_grade) values('s27181'+cast(@s as varchar(2)),'s2530'+cast(@s as varchar(2)),@t,@a)
          set @t=rand()*100
          set @a=rand()*100
       end
          set @s=@s+1
end
go
 
select * from tb_grade;
select * from tb_student;
---------------------------------------------------------------------------------
--修改表中信息
update tb_student set sex='女' where s_name like '%三'
update tb_student set sex='男' where s_name like 'DB%'
update tb_student set d_no=1111 where id<7
update tb_student set d_no=2222 where id<23 and id>=21
---------------------------------------------------------------------------------
--删除整张表的信息
delete from tb_student
delete from tb_student_clone
delete from tb_student_clone2
delete from tb_sc
delete from tb_grade
delete from OrderDetails
delete from Products
--删除部分数据
delete from tb_student where id=1
delete from tb_student where s_name like '欧阳%'
---------------------------------------------------------------------------------
--查询语句
select * from tb_student
select * from tb_sc
select * from tb_course
select * from tb_grade
select * from tb_student_clone
select * from tb_student_clone2
select * from Products
select * from OrderDetails
--根据id降序查询
select * from tb_student order by id desc
--根据id升序查询
select * from tb_student order by id asc
 
--查询所有年龄在20岁以下的学生的姓名及其年龄
select s_name,age from tb_student where age>20
--查询年龄在20到25岁之间的学生的信息
select * from tb_student where age>20 and age<25
select * from tb_student where age between 21 and 24
--查询姓"欧阳"且全名只有3个字的学生的姓名
--- sql中like用法 ：
---                *          它同于DOS命令中的通配符，代表多个字符。                          (如：c*c代表cc,cBc,cbc,cabdfec等)
---                %          这种方法在很多程序中要用到，主要是查询包含子串的。               (如：%c%代表agdcagd等)
---               [*]         代替*                                                            (如:a[*]a代表a*a)
---                ?          同于DOS命令中的？通配符，代表单个字符                            (如:b?b代表brb,bFb等)
---                #          大致同上，不同的是代只能代表单个数字。                           (如：k#k代表k1k,k8k,k0k)
---                _          指定一个范围中任意一个                                           (如：[a-z]代表a到z的26个字母中任意一个)
---             [!字符]       它只代表单个字符                                                 (如：[!a-z]代表9,0,%,*等)
---             [!数字]       它只代表单个字符                                                 (如：[!0-9]代表A,b,C,d等)
---    字符[范围类型]字符     可以和其它几种方式组合使用                                       (如：cc[!a-d]#代表ccF#等)
select s_name from tb_student where s_name like '欧阳_'
select s_name from tb_student where s_name like '欧阳%' and len(s_name)=3
--查询"DB_"开头,且倒数第三个字符为i的课程的详细情况
--当记录中有通配符时使用转义\escape '\'
select * from tb_student where s_name like 'DB\_%i__'escape '\'
--查询选修了3号课程的学生的学号及其成绩,查询结果按分数的降序排列
select sno,grade from tb_sc where sno=3 order by grade desc
--查询男女生各有多少人
select count(*)from tb_student where sex='男'
union
select count(*)from tb_student where sex='女'
--按性别分组查询
select sex as '性别',count(*) as '人数' from tb_student  group by sex
----------------------------------------------------------------------------------
--把相同的字段去掉
select distinct sex from tb_student
--truncate 和delete,truncate 执行的速度比delete快
--删除全部数据,保留表的结构
truncate table tb_course
----------------------------------------------------------------------------------
--T-SQL
--循环想表中添加数据的SQL脚本
declare @t int --声明一个整型变量
declare @s char(2) --性别
set @t=0 --给整型变量赋值
while(@t<10)  --循环
begin
   --循环体
   if(@t%2!=0)
       set @s='男'
   else
      set @s='女'
   insert into tb_student(s_name,sex,age,tno) values('刘备'+cast(@t as varchar(2)),@s,35+@t,cast((1202+@t) as varchar(30) ))
    set @t=@t+1
end
--循环修改表中的数据
declare @t int --声明一个变量
declare @s int
   set @t=1197  --给整型变量赋值
   set @s=31
while(@t<1204) --循环
begin
    --循环体
    update tb_student set d_no=@t where id=@s
    set @t=@t+1
    set @s=@s+1
end
----------------------------------------------------------------------------------
--复制表
select * into tb_student_clone from tb_student where 1=1
--复制表结构
select * into tb_student_clone2 from tb_student where 1=2
-----------------------------------------------------------------------------------
--分页每页显示5条记录
select top 5 * from tb_student
select top 5 * from tb_student order by id asc
--显示第二页
select top 5 * from tb_student where id not in(select top 5 id from tb_student)
-----------------------------------------------------------------------------------
--冒牌分页
declare @t1 int --声明一个整型变量
declare @pagecount int
set @t1=0 --给整型变量赋值
set @pagecount=(cast((select count(*) as count from tb_student) as int)/5)+1;
print @pagecount;
while(@t1<(@pagecount)) --循环
begin
--循环体
       select top 5 * from tb_student where id not in(select top (5*@t1) id from tb_student)
    set @t1=@t1+1
end
-------------------------------------------------------------------------------------
--每页显示m条数据,查询第n页结果
declare @m int ---每页显示多少条数据
declare @n int ---
set @m=5
set @n=(cast((select count(*) as count from tb_student) as int)/5)+1;
print @n
if (@n-1)>0
begin
   select top (@m) * from tb_student where id not in(select top (@m*(@n-1)) id from tb_student)
end
------------------------------------------------------------------------------------
--求交集,可以直接用and
select * from tb_student where sex='女'
intersect
select * from tb_student where age>35
--求并集 系统会自动将重复的元组去掉
select * from tb_student where sex='男'
union
select * from tb_student where age>21
----求交集   系统保留重复元素组
select * from tb_student where sex='男'
union all
select * from tb_student where age>21
--求补集
select * from tb_student where sex='男'
except
select * from tb_student where age<30
------------------------------------------------------------------------------------
--获得当前增量的值 identity--系统变量
select @@identity as abc
-----------------------------------------------------------------------------------
--cast类型转换函数
select cast('123' as int)
select cast('2012-12-11' as datetime)
select cast(CONVERT(varchar(800),GETDATE(),121) as datetime)
--convert类型转换函数
select convert(datetime, '2012-12-11')
--获取当前时间
select current_timestamp
--获取当前主机端口号和主机名
select host_id() as '主机端口号',host_name() as '主机名'
-----------------------------------------------------------------------------------
--字符串的连接
select * from tb_student where s_name=('刘备'+'0')
--当表中不存在此字段时,系统会自动创建一个虚列字段
select *,('abc'+'123') as a from tb_student
select *  from tb_student
select * from tb_depart
-----------------------------------------------------------------------------------
--多表查询
select distinct * from tb_student as s,tb_course as c,tb_sc as sc
 where s.tno=sc.tno and c.c_no=sc.c_no
--连接查询
--内联查询
select a.*,b.d_name from tb_student a inner join tb_depart b on b.d_name='计算机学院' and a.d_no=b.d_no
--左连查询,以左边表为基表,满足条件的查询出来,不满足条件用null填充
select * from tb_student a left join tb_depart b on a.d_no=b.d_no and b.d_name='计算机学院'
--右连查询,以右边表为基表,满足条件的查询出来,不满足条件用null填充
select * from tb_student a right join tb_depart b on a.d_no=b.d_no and b.d_name='计算机学院'
--嵌套查询
select * from tb_depart as a where a.d_no  in(select d_no from tb_student where s_name='张三')
select * from tb_depart as a where a.d_no not in(select d_no from tb_student where s_name='张三')
---------------------------------------------------------------------------------
--创建视图
create view view_sc
  as
   select a.*,b.d_name from tb_student a right join tb_depart b on a.d_no=b.d_no and b.d_name='计算机学院'
--查询视图
select * from view_sc a,tb_sc b where a.tno=b.tno
---------------------------------------------------------------------------------
--判断数据库是否存在,如果存在,就删除它
 if exists (select * from sysdatabases where name='j1216') drop database j1216
--判断表在数据库中是否存在,如果存在,就删除它
 if exists (select * from sysdatabases where name='tb_sc') drop table tb_sc
--------------------------------------------------------------------------------
--定义变量保存表中的记录
declare @t2 int
select @t2=count(*) from tb_student
print @t2;
--打印变量
----print 直接返回一个值
----select 返回一个带有结构的值，比如说有列名
print @t2
select @t2
--------------------------------------------------------------------------------
--全局变量的使用
--打印SQL Server的版本
 print  'SQL Server的版本: '+@@VERSION
--打印服务器的名称
 print  '服务器的名称: '+@@SERVERNAME
--向tb_student表中添加一行数据
INSERT INTO tb_student(s_name,sex,age,tno,d_no)   
     VALUES('武松','男',23,'1205',63)
--如果大于0表示上一条语句执行有错误
print '当前错误号: '+convert(varchar(5),@@ERROR)
--打印刚在表中添加的数据的ID号
 print '刚才报名的学员，座位号为:'  
              +convert(varchar(5),@@IDENTITY )
--修改tb_student表中姓名为'李四'的学生的年龄
UPDATE tb_student SET age=85
       WHERE s_name='李四'
--如果大于0表示上一条语句执行有错误
print '当前错误号: '+convert(varchar(5),@@ERROR)
--GO是批处理的标志，
--表示SQL Server将这些T-SQL语句编译为一个执行单元，提高执行效率
GO
--------------------------------------------------------------------------------
--if-else语句的使用
declare @a float
  select @a=avg(w_grade) from tb_grade
  print @a
  print '平均分是:'+convert(varchar(10),@a)
if(@a>=60)
    begin
        print '平均成绩:良'
        select top 3 g_no, s_no, w_grade from tb_grade order by w_grade desc
   end
else
   begin
       print '平均成绩:差'
       select top 3 g_no, s_no, w_grade from tb_grade order by w_grade asc
   end
go
---------------------------------------------------------------------------------
--while循环语句
declare @a1 float
while(1=1)
   begin
     select @a1=count(*) from tb_grade where w_grade<60
     print @a1
         if(@a1>0)
              begin
                 update tb_grade set w_grade=w_grade+2 where w_grade<60
              end
         else
              begin
                 break;
              end
    end
go
select * from tb_grade;
--变量保存小于60分的学生人数
declare @c int
   select @c=count(*) from tb_grade where w_grade<60
while(@c>0)
begin
   update tb_grade set w_grade=w_grade+2
   select @c=count(*) from tb_grade where w_grade<60
end
go
-------------------------------------------------------------------------------
--case end
---相当于switch  
select *,
 case
    when w_grade>=90 then 'A'
    when w_grade>=80 and w_grade<=89 then 'B'
    when w_grade>=70 and w_grade<=79 then 'C'
    when w_grade>=60 and w_grade<=69 then 'D'
 else 'E'
   end as '成绩评定'
from tb_grade
go
-------------------------------------------------------------------------------
--触发器的语法
----     create trigger triggerName on tableName
----     for delete,|insert,|update
----     as
----     begin
----        --T-SQL
----     end
--创建一张备份表
select * into tb_student_clone from tb_student where 1=2
--创建触发器的脚本
--deleted inserted是删除,增加时调用的虚拟表
drop trigger tg_student;
drop trigger tg_student1;
drop trigger tg_student2;
------ tb_student和tb_sc有主外键关联，创建删除触发器需要先删除从表后删主表
---------------------------------删除触发器
create trigger tg_student on tb_student
for delete
as
begin
   insert into tb_student_clone(id,s_name,sex,age,tno,d_no)
   select id,s_name,sex,age,tno,d_no from deleted
end
----调用触发器
delete from tb_student where id=25  ---报错: 当 IDENTITY_INSERT 设置为 OFF 时，不能为表 'tb_student_clone' 中的标识列插入显式值。
--------正确如下:
create trigger tg_student1 on tb_student
for delete
as
begin
   set IDENTITY_INSERT tb_student_clone on
   insert into tb_student_clone(id,s_name,sex,age,tno,d_no)
   select id,s_name,sex,age,tno,d_no from deleted
end
-------------------------插入触发器
create trigger tg_student2 on tb_student
for insert
as
begin
   set IDENTITY_INSERT tb_student_clone on
   insert into tb_student_clone(id,s_name,sex,age,tno,d_no)
   select id,s_name,sex,age,tno,d_no from inserted
end
--触发器的调用
insert into tb_student(s_name,sex,age,tno,d_no)
 values('','',25,'1206',9)
select * from tb_student_clone
--删除触发器
drop trigger tg_student
--禁用触发器
disable trigger tg_student on tb_student
disable trigger tg_student2 on tb_student
--启用触发器
enable trigger tg_student on tb_student
--修改触发器 只需要在创建触发器的脚本中将create改为alter
alter trigger tg_student on tb_student
for delete,update
as
begin
   set IDENTITY_INSERT tb_student_clone on
   insert into tb_student_clone(id,s_name,sex,age,tno,d_no)
   select id,s_name,sex,age,tno,d_no from deleted
end
---------------------------------------------------------------------------------------
--创建触发器,使得在向OrderDetails表中添加数据时,货仓表中的货物数量UnetislnStock
--则要减去相应的订单表中预定的数量Quantity
create trigger tg_order on OrderDetails
for insert
as
   begin
      --@a是订单中产品数量
      declare @a int
      --@b是订单中产品号码
      declare @b int
          select @a=Quantity from inserted
          select @b=ProductD from inserted
          update Products  set UnetislnStock=(UnetislnStock-@a) where ProductD=@b
   end
--禁用触发器
disable trigger tri_order on OrderDetails
--启用触发器
enable trigger tri_order on OrderDetails
--调用触发器
insert into OrderDetails(OrderD, ProductD, UnitPrice, Quantity) values(10527,4,19.00,10)
select * from Products
select * from OrderDetails;
-------------------------------------------------------------------------------------------
--创建无参存储过程
create proc pro_2 as
begin
  insert into tb_student( s_name, sex, age, tno, d_no) values('李刚','男',35,'1207',8)
end
--调用存储过程
exec pro_2
--创建带参数的存储过程,输入和输出参数(用output定义的参数为输出参数)
--用in定义的参数是输入参数(in可以不写)
create proc pro_4(@name varchar(20),@sex varchar(2),
                  @age int,@tno varchar(50),@d_no int,@c int output) as
begin
  insert into tb_student(s_name, sex, age, tno, d_no)
              values(@name,@sex,@age,@tno,@d_no)
  set @c=@@rowcount
  print '添加'+convert(varchar(5),@c)+'条数据'
end
--调用存储过程
declare @a int
exec pro_4 '凤姐','女',30,'1203',250,@a
--修改存储过程 只需要在创建存储过程的脚本中将create改为alter
alter proc pro_4(@name varchar(20),@sex varchar(2),
                  @age int,@tno varchar(50),@d_no int,@c int output) as
begin
  if @name=''
  begin
    raiserror('姓名不能为空',17,1)
    return
  end
  insert into tb_student(s_name, sex, age, tno, d_no)
              values(@name,@sex,@age,@tno,@d_no)
  set @c=@@rowcount
  print '添加'+convert(varchar(5),@c)+'条数据'
end
--调用存储过程
declare @a int
exec pro_4 '','女',30,'1207',250,@a
declare @a int
exec pro_4 '伏羲','女',30,'1207',250,@a
--删除存储过程
drop proc pro_2
----------------------------------------------------------------------------------------
--存储过程:查询数据库中指定开始行到结束行记录
create proc pro_zuoye(@a int,@b int)
as
begin
     select * from tb_student where id not in(select top (@a-1) id from tb_student)
     intersect
     select * from tb_student where id in(select top (@b) id from tb_student )
end
--调用存储过程
exec pro_zuoye 5,8
--------------------简单的分页,使用存储过程
USE [j1216]
GO
/****** Object:  StoredProcedure [dbo].[Sp_PapeView]    Script Date: 07/24/2013 15:45:31 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
create proc Sp_PageView
(
  @RecordCount  int OUTPUT, --总记录数
  @PageSize  int=4,            --每页的大小(记录数)
  @PageCurrent int=1,              --要显示的页码
  @PageCount  int OUTPUT        --总页数  
)
as
begin
   select @RecordCount=COUNT(*) from tb_student
   select @PageCount=@RecordCount/@PageSize+1
   select top (@PageSize) * from tb_student where id not in (select top ((@PageCurrent-1)*@PageCount) id from tb_student)
end
declare @RecordCount int,@PageCount int
exec sp_pageView @RecordCount,5,1,@PageCount
-----------------------------------------------------------------------------------------
--备注:凡是使用create函数创建的数据库元素,全部都用drop来删除