--创建数据库命令create database j1216on(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_studentselect * from tb_userselect * from OrderDetails;select * from tb_sc;drop table tb_scdrop table tb_student_clonedrop 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 intalter 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 NOCHECKalter table tb_student with check add constraint pk_tnoforeign key(tno) references tb_sc(tno) --给表OrderDetails添加外键alter table OrderDetails with nocheck add constraint pk_ProductDforeign 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 intdeclare @a intdeclare @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+1endgo 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<7update tb_student set d_no=2222 where id<23 and id>=21-----------------------------------------------------------------------------------删除整张表的信息delete from tb_studentdelete from tb_student_clonedelete from tb_student_clone2delete from tb_scdelete from tb_gradedelete from OrderDetailsdelete from Products--删除部分数据delete from tb_student where id=1delete from tb_student where s_name like '欧阳%'-----------------------------------------------------------------------------------查询语句select * from tb_studentselect * from tb_scselect * from tb_courseselect * from tb_gradeselect * from tb_student_cloneselect * from tb_student_clone2select * from Productsselect * 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<25select * 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='男'unionselect 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+1end--循环修改表中的数据declare @t int --声明一个变量declare @s int set @t=1197 --给整型变量赋值 set @s=31while(@t<1204) --循环begin --循环体 update tb_student set d_no=@t where id=@s set @t=@t+1 set @s=@s+1end------------------------------------------------------------------------------------复制表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_studentselect 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 intset @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+1end---------------------------------------------------------------------------------------每页显示m条数据,查询第n页结果declare @m int ---每页显示多少条数据declare @n int ---set @m=5set @n=(cast((select count(*) as count from tb_student) as int)/5)+1;print @nif (@n-1)>0begin select top (@m) * from tb_student where id not in(select top (@m*(@n-1)) id from tb_student)end--------------------------------------------------------------------------------------求交集,可以直接用andselect * from tb_student where sex='女'intersectselect * from tb_student where age>35--求并集 系统会自动将重复的元组去掉select * from tb_student where sex='男'unionselect * from tb_student where age>21----求交集 系统保留重复元素组select * from tb_student where sex='男'union allselect * from tb_student where age>21--求补集select * from tb_student where sex='男'exceptselect * 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_studentselect * from tb_studentselect * 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 intselect @t2=count(*) from tb_studentprint @t2;--打印变量----print 直接返回一个值----select 返回一个带有结构的值，比如说有列名print @t2select @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 endelse begin print '平均成绩:差' select top 3 g_no, s_no, w_grade from tb_grade order by w_grade asc endgo-----------------------------------------------------------------------------------while循环语句declare @a1 floatwhile(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 endgoselect * from tb_grade;--变量保存小于60分的学生人数declare @c int select @c=count(*) from tb_grade where w_grade<60while(@c>0)begin update tb_grade set w_grade=w_grade+2 select @c=count(*) from tb_grade where w_grade<60endgo---------------------------------------------------------------------------------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_gradego---------------------------------------------------------------------------------触发器的语法---- 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_studentfor deleteasbegin insert into tb_student_clone(id,s_name,sex,age,tno,d_no) select id,s_name,sex,age,tno,d_no from deletedend----调用触发器delete from tb_student where id=25 ---报错: 当 IDENTITY_INSERT 设置为 OFF 时，不能为表 'tb_student_clone' 中的标识列插入显式值。--------正确如下:create trigger tg_student1 on tb_studentfor deleteasbegin 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 deletedend-------------------------插入触发器create trigger tg_student2 on tb_studentfor insertasbegin 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 insertedend--触发器的调用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_studentdisable trigger tg_student2 on tb_student--启用触发器enable trigger tg_student on tb_student--修改触发器 只需要在创建触发器的脚本中将create改为alteralter trigger tg_student on tb_studentfor delete,updateasbegin 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 deletedend-----------------------------------------------------------------------------------------创建触发器,使得在向OrderDetails表中添加数据时,货仓表中的货物数量UnetislnStock--则要减去相应的订单表中预定的数量Quantitycreate trigger tg_order on OrderDetailsfor insertas 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 Productsselect * from OrderDetails;---------------------------------------------------------------------------------------------创建无参存储过程create proc pro_2 asbegin 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) asbegin 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 intexec pro_4 '凤姐','女',30,'1203',250,@a--修改存储过程 只需要在创建存储过程的脚本中将create改为alteralter proc pro_4(@name varchar(20),@sex varchar(2), @age int,@tno varchar(50),@d_no int,@c int output) asbegin 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 intexec pro_4 '','女',30,'1207',250,@adeclare @a intexec pro_4 '伏羲','女',30,'1207',250,@a--删除存储过程drop proc pro_2------------------------------------------------------------------------------------------存储过程:查询数据库中指定开始行到结束行记录create proc pro_zuoye(@a int,@b int)asbegin 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 OFFGOSET QUOTED_IDENTIFIER ONGOcreate proc Sp_PageView( @RecordCount int OUTPUT, --总记录数 @PageSize int=4, --每页的大小(记录数) @PageCurrent int=1, --要显示的页码 @PageCount int OUTPUT --总页数 )asbegin 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)enddeclare @RecordCount int,@PageCount intexec sp_pageView @RecordCount,5,1,@PageCount-------------------------------------------------------------------------------------------备注:凡是使用create函数创建的数据库元素,全部都用drop来删除 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】
【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】 【少于150字的随笔不允许发布到网站首页】