最近都在忙毕业设计，所幸做的差不多了。蛋碎的是尼玛要写文档呀。昨天突然想起来SQL有些东西忘记了，在此整理一下学过的知识。希望给大家有所帮助。
/*SQL 基础*/
/*创建数据库格式*/
1 /*创建数据库*/ 2 create database blackhourse 3 on primary 4 ( 5 name=‘黑马‘ 6 ,filename=‘E:\新建文件夹\黑马.mdf‘ 7 ,size=10MB 8 ,maxsize=100MB 9 ,filegrowth=10% 10 )log on 11 ( 12 name=‘黑马_log‘ 13 ,filename=‘E:\新建文件夹\黑马_log.ldf‘ 14 ,size=5MB 15 ,maxsize=50MB 16 ,filegrowth=10% 17 ); 18 19 /* 20 drop database blackhourse 21 */
1 /*调用存储过程，分离数据库*/ 2 3 exec sp_detach_db ‘blackhourse‘; 4 exec sp_detach_db ‘archive‘; 5 exec sp_detach_db ‘zdp‘; 6 exec sp_detach_db ‘zdpdsy‘; 7 exec sp_detach_db ‘archive1‘; 8 9 /*附加数据库*/ 10 create database archive 11 on( 12 filename=‘E:\新建文件夹\黑马.mdf‘ 13 )log on( 14 filename=‘E:\新建文件夹\黑马_log.ldf‘ 15 ) 16 for attach; 17 18 create database archive1 19 on( 20 filename=‘E:\新建文件夹\heima.mdf‘ 21 ) 22 log on 23 ( 24 filename=‘E:\新建文件夹\heima_log.ldf‘ 25 ) 26 for attach;
1 /*创建架构,表*/ 2 use archive; 3 go 4 create schema Mydounaifen authorization dbo; 5 go 6 7 create table Mydounaifen.TlStudent 8 ( 9 /*列名，数据类型，约束(自动编号，主键)*/ 10 stuName char(10), 11 stuAge int, 12 stuSex char(2) 13 );
1 /*常用数据类型*/ 2 ->数字类型：int,tinyint 3 ->时间类型：datatime 格式:”yyyy-MM-dd hh:mm:ss.sss” 4 ->二进制数据类型 image (存储图片) 5 -> bit 类型 1 0 ‘true’ ‘false’ 6 ->字符串类型： 7 Char,nchar,varchar,nvarchar 8 //可以存储大量的字符串 9 varchar(max) 10 nvarchar(max) 11 //不建议使用下面这两个，因为被淘汰了 12 text 13 ntext 14 Varchar 与char的区别 15 1可变长度 varchar(节省空间，不过效率相对来说慢一点) 16 2 固定长度 char (比较浪费空间) 17 Nchar(10),char(10)的区别 18 Char 存储的是字节个数 19 Nchar 存储的是字符个数，用来存储中文
1 /*约束*/ 2 -- 保护数据完整性的机制：让存储的数据有意义的条件 3 /* 4 分类 5 1.主键约束 6 2.唯一约束 7 3.检车约束 8 4.默认约束 9 5.非空约束 10 6.主外键约束 11 12 */ 13 -- 基本语法 14 /* 15 alter table 架构.表名 16 add constraint 约束名 约束类 17 */ 18 19 --添加检查约束 20 alter table Mydounaifen.TlStudent 21 add constraint CK_TlStudent_stuSex 22 check(stuSex=‘男‘or stuSex=‘女‘); 23 --其他约束类似 24 25 --删除多个约束，约束名用,隔开 26 alter table Employees 27 drop constraint 28 DF_Employees_EmpGender, 29 CK_Employees_EmpAge 30 31 --增加多个约束 32 /*添加非空约束 33 为年龄与性别添加检查约束 34 为邮箱添加唯一约束 35 */ 36 alter table Student 37 add constraint ck_Student_stuAge 38 check(stuAge>0 and stuAge<30), 39 constraint ck_Student_stuSex 40 check( stuSex=‘男‘or stuSex=‘女‘), 41 constraint uq_Student_stuEmail 42 unique(stuEmail); 43 44 --增加一列 45 alter table Mydounaifen.TlStudent 46 add email varchar(20) null; 47 48 --删除一列 49 alter table Mydounaifen.TlStudent 50 drop column email; 51 52 --修改某个列数据类型 53 alter table Mydounaifen.TlStudent 54 alter column email nvarchar(20) null; 55 56 create table tblCourseT_SQL2 57 ( 58 cId int identity(1,1) not null 59 , cName nvarchar(10) not null 60 , cDesc nvarchar(100) null -- description 61 ); 62 alter table tblCourseT_SQL2 63 add constraint PK_tblCourseT_SQL2_cId primary key(cId); 64 65 -- 学生 66 create table tblStudentT_SQL2 67 ( 68 stuId int identity(1,1) not null 69 , stuName nvarchar(10) not null 70 , cId int null -- 外键stuTocId 71 ); 72 73 --创建外键约束主键表中必须有个主键 74 alter table tblStudentT_SQL2 75 add constraint [FK_tblStudentT_SQL2_tblCourseT_SQL2_cId] 76 foreign key(cId) references tblCourseT_SQL2(cId); 77 78 /* 79 补充说明 80 主外键表中，关联主键表的字段必须是主键和唯一键 81 82 */
1 /*增删改查*/ 2 3 /*插入 */ 4 5 --insert 架构名.表名(列,列,.....) values(值,值,....) 6 --基本插入 7 insert into Mydounaifen.TlStudent(stuName,stuAge,stuSex) 8 values(‘豆奶粉‘,22,‘女‘); 9 insert into Mydounaifen.TlStudent(stuName,stuAge,stuSex) 10 values(‘大鹏‘,22,‘男‘); 11 这种写法每次只能插入一条数据 12 13 --为自动编号插入数据(tid为自动编号) 14 set IDENTIY_INSERT TblTeacher on 15 insert into TblTeacher(tid,tname,tsalary) 16 values(100,‘bob‘,50000) 17 18 --当向表中的除自动编号外的所有其他列插入数据的时候，这是可以省略列名。 19 insert into T_Seats values(LoginId,RealName,Pwd); 20 21 --向一个已经存在的表中插入数据，数据的来源是另外一张表 22 insert into NewTblTeacher (tname,tage) 23 select tname,tage from TblTeacher 24 --返回刚插入数据的自动增长编号 25 insert into Exe2.LoginTbl output inserted.id values(‘zdpdsy123‘,‘123‘); 26 --表值函数 27 /* 28 格式：insert into 表名(列1,列2,…) values(值1,值2,…), (值1,值2,…), 29 (值1,值2,…); 30 */ 31 insert into StudentTest(stuName,stuSex,StuAge) 32 values(‘牛亮亮‘,‘m‘,30),(‘王成伟‘, ‘m‘, 28), 33 (‘赵晓虎‘, ‘m‘, 29),(‘李艳茹‘, ‘f‘, 19), 34 (‘牛亮亮‘,‘f‘,22),(‘苏坤‘,‘m‘,30),(‘苏坤‘,‘f‘,27); 35 36 /*修改 */ 37 38 --update 架构名.表名set 字段=值，字段=值.... where 条件 39 update Mydounaifen.TlStudent set stuAge=20 where stuName=‘豆奶粉‘; 40 41 --/*删除数据 */ 42 -- delete from 表名where 条件; 43 -- drop database|table|schema 名字; 44 -- truncate table 表名 45 drop table Student; 46 47 delete from Mydounaifen.TlStudent where stuName=‘大鹏‘; 48 49 truncate table TblTeacher 50 =========================== 51 两者区别 52 1.delete删除的时候,自动编号没有恢复到默认值,而truncate 可以. 53 2.truncate 删除数据的时候,只能一次性都清除,不能根据条件来删除 则delete可以. 54 3.truncate删除数据的速度比delete快的多 55 4.truncate语句不会触发delete触发器 56 57 --/*查询 */ 58 /*格式 59 select distinct |top 数字[percent] 60 字段as常量 61 ,包含字段表达式 62 ，函数 Sum,max 63 ,常量 64 from 65 表或结果集 66 where 67 条件： 逻辑|空值|多条件|模糊|范围 68 group by 69 字段 70 having 71 筛选条件 72 order by 73 字段 desc | asc 74 执行顺序 75 from -> where -> group by -> having -> select -> order by 76 */ 77 select 78 * 79 from MyStudent --1>先从MyStudent表中拿到数据 80 where fage>30 --2>从MyStudent的数据中筛选出所有年龄大于30的人的信息 81 group by fgender --3>按照性别分组，分完组以后又得到一个新结果集 82 having count(*)>500 --4>基于分组以后的结果集，然后再筛选，筛选出人数>500的记录 83 84 85 /* 86 from 子句 87 寻找数据源，后面可以跟表，视图，表值函数，结果集等 88 where子句 89 对from所得到的临时表做一次筛选，是直接在结果中将筛选到的结果组成一个临时表 90 null值处理 91 判断一个字段是否为null使用 92 字段 is [not] null 93 null表示的是不知道，凡是与null参与的运算得到的结果都是null和不知道 94 95 */ 96 --查看整个表 97 select * from TblScore; 98 --考试期中及格 99 select * from TblScore where ScoreNum>=60; 100 --期末及格考试 101 select * from TblScore where ScoreLast>=60; 102 --按照姓名分组 103 select stuName from StudentTest 104 group by stuName; 105 --取成绩前五名 106 select top 5 * from MyStudent 107 order by fag desc 108 /*模糊查询 */ 109 通配符; 110 1. % //匹配多个字符 111 select 112 * 113 from Mystudent 114 where fname like ‘%敏%‘ 115 2. -//匹配一个字符 116 --查询姓赵的同学，且长度为三个 117 select * from MyStudent 118 where fanme like ‘赵__‘ 119 120 select * from MyStudent 121 where fanme like ‘赵%‘ and len(fname)=3 122 123 3. [] 124 --查询出姓名包含‘雷‘或‘伟‘的人的姓名 125 126 select * from MyStudent 127 where fname like ‘%[雷伟]%‘ 128 用[]括起来，表示转义字符 如[%] 129 130 --聚合函数 131 --count ,max,min ,avg,sum 132 --聚合函数默认忽略null值 133 --基本语法：函数名（字段） 134 135 select count(*) from StudentTest; 136 select stuName,count(*),avg(stuAge) from StudentTest 137 group by stuName; 138 select avg(scoreNum) from ScoreAgg; 139 select count(scoreNum) from ScoreAgg; 140 141 -- 别名 142 -- select列 as 别名 (推荐) 143 -- select列 别名 (不推荐) 144 -- 别名=select列 (推荐) 145 select 146 custId as "用户ID" --(推荐用) 147 ,count(*) as [用户订单数] 148 , 149 case 150 when count(*)>1 then ‘高级用户‘ 151 else ‘第一次访问用户‘ 152 end as ‘用户等级‘ 153 ,sum(unitprice * quantity * discount) as ‘用户总金额‘ 154 from OrdersTable 155 group by custId 156 having sum(unitprice * quantity * discount)>120; 157 158 --按英语成绩排序,如果英语一样，则按数学成绩排序 159 select * from MyStudent 160 order by FEnligsh desc,FMath desc; 161 --order by 后面可以跟一个表达式 162 --order by 永远是在语句最后面执行 163 164 /*select into */ 165 /* 166 select into 语法 167 168 select 169 字段等 170 into 表名 171 from 172 数据源 173 其他子句 174 */ 175 select * into 176 FormSelect 177 from StuSetInto; 178 179 select * from FormSelect; 180 /* 181 select * into 新表名from 数据源where 1>2; 182 只会把表的结构复制过来， 183 但是键和约束索引等不会复制过来。 184 除了自动增长的 185 */ 186 select * into NewTable from FormSelect where 1>2;
1 /*Union all 结果集 */ 2 两个集合必须有相同的列,才能union 3 --union联合的作用就是多个结果集并到一起 4 --联合结果集的列名由第一个结果集确定 5 --对应的数据类型的个数要一致---------------------------------- 6 select fname,fage from mystudent where fid>999 7 union all 8 select tsname,tsage tblstudent where tsid>12 9 练习=============================== 10 11 请查询出每个销售员的销售总金额以及总的销售金额 12 select 13 销售员， 14 销售金额=sum(销售数量*销售价格) 15 from Myorders 16 gropu by 销售员 17 union all 18 select ‘总的销售金额:‘,sum(销售数量*销售价格) 19 from sum(销售数量*销售价格) 20 21 22 23 insert into TblTeacher 24 select ‘Chiris‘,1,20,2000000,‘1999-9-9‘ union 25 select ‘Chiris‘,1,20,2000000,‘1999-9-9‘ union 26 select ‘James‘,1,21,2000000,‘1990-9-9‘ union 27 select ‘James‘,1,21,2000000,‘1990-9-9‘ 28 ======================================== 29 [union关键字本身就具有去掉重复的意思,如果不想去掉重复用union all ]
1 --/*常用函数（重点）*/ 2 --数学函数，字符串函数，时间函数，转换函数，聚合函数 3 4 5 --时期函数 6 --获的当前时间 7 select getdate(); 8 --简写形式 9 --year(时间),month(时间),day(时间） 10 select year(getdate()),month(getdate()),day(getdate()); 11 /* 12 dateadd 13 在指定时间上的指定部分加上一个数字 14 dateadd(部分，数字，时间对象) 15 具体查看联机丛书 16 在指定位置上加上指定的书，可以获取午夜时间 17 dateadd(month,1,‘2013-8-7‘) 18 返回的是一个时间对象 19 */ 20 select dateadd(day,10,getdate()); 21 select ‘2013-8-7 11:50‘,dateadd(day,-10,‘2013-8-7 11:50‘); 22 23 select dateadd(month,1,‘2013-8-7‘); 24 25 /* 26 datediff 计算两个时间特定的差比如说差几年,差多少天 27 语法DATEDIFF (part , startdate , enddate ) 28 part 表示年，月，日，星期这些。 29 datediff(year,‘1999-9-9‘,getdate()) 30 */ 31 select datediff(year,‘1999-9-9‘,getdate()); 32 33 select datediff(month,‘2013-7-31‘,‘2013-8-1‘); 34 35 --获取上一个月的最后一天 36 select dateadd(day,-1*day(getdate()),getdate()); 37 38 /* 39 datepart 截取日期的特定部分 40 datepart(部分，日期) 41 */ 42 43 select datepart(weekday,‘2013-8-7‘); 44 45 46 --转换函数cast,convert 47 /* 48 语法 49 cast ( 数据或字段as 数据类型); 50 51 convert(数据类型,字段); 52 53 */ 54 /* 55 select cast(1 as varchar(5))+‘a‘; 56 57 select convert(varchar(5),3)+‘a‘; 58 */ 59 60 61 --数学函数 62 --round 四舍五入函数 63 --语法(源数字，保留位数，截断标志) 64 select 122.56,round(122.56,1); 65 66 select 133.59,round(133.59,1),round(133.59,1,2); 67 68 select 1.2,ceiling(1.2),1.8,floor(1.5); 69 70 select sqrt(2); 71 72 73 --SIGN 返回符号. 等价于：|x|/x 74 -- x的值为正数则sign(x) = 1 75 -- x的值为负数则sign(x) = -1 76 -- x的值为 则sign(x) = 0 77 78 select sign(-27),sign(27),sign(0); 79 80 select power(2,30); 81 82 83 84 --字符串函数 85 -- len() 字符长度 86 -- datalength() 字节长度 87 88 select ‘1二‘,len(‘1二‘),datalength(‘1二‘); 89 90 --ltrim rtrim 91 92 select ltrim(‘ 123‘),rtrim(‘123 ‘); 93 select ltrim(rtrim(‘ 123 ‘)); 94 95 -- left() right() 96 -- left 97 -- 字符串left(字符串, 数字) 98 -- 从源字符串的左边开始，截取指定数字个字符，返回 99 select LEFT(‘1234567890‘, 5); 100 -- right 与left刚好相反 101 select right(‘1234567890‘, 6);
/*SQL 高级(提升) */
1 -- case表达式 相当于选择结构表达式 2 /* 3 case 4 when 条件1 then 值1 5 when 条件2 then 值2 6 ... 7 else 值n 8 end 9 */ 10 11 /* 12 case 13 when 条件then 值 14 when 条件then 值 15 ... 16 else 值n 17 18 end 19 */ 20 21 select * from Score where stuID=2; 22 23 select *, 24 case 25 when testBase>=90 then ‘A‘ 26 when testBase>=80 then ‘B‘ 27 when testBase>=70 then ‘C‘ 28 else ‘E‘ 29 end as ‘等级‘ 30 from Score where stuID=2; 31 用法1 //相当于switch case 32 33 Select 34 tscoreId, 35 tsid, 36 tenglish, 37 评级= 38 Case tenglish 39 When 100 then ‘满分’ 40 End 41 From TblScore 42 43 /*纵表变横表*/ 44 create table Score 45 ( 46 学号 nvarchar(10), 47 课程 nvarchar(10), 48 成绩 int 49 ) 50 51 insert into Score values(‘0001‘,‘语文‘,87); 52 insert into Score values(‘0001‘,‘数学‘,79); 53 insert into Score values(‘0001‘,‘英语‘,95); 54 insert into Score values(‘0002‘,‘语文‘,69); 55 insert into Score values(‘0002‘,‘数学‘,84); 56 57 select * from Score; 58 59 select 学号, 60 sum(case when 课程 =‘数学‘ then 成绩 end )as ‘数学‘ 61 , 62 sum(case when 课程 =‘英语‘ then 成绩 else 0 end )as ‘英语‘ 63 , 64 sum(case when 课程 =‘语文‘ then 成绩 end ) as ‘语文‘ 65 from Score 66 group by 学号 67 68 select * from Exe2.LoginTbl;
1 /* 2 -- 表连接 3 -- 作用：将多张表变成一张表 4 -- 用法与分类(案例) 5 -- 分类：交叉连接、内连接、外连接 6 */ 7 8 create table joinPerson 9 ( 10 pId int identity(1,1) not null 11 , pName nvarchar(10) not null 12 , titleId int null 13 ); 14 alter table joinPerson 15 add constraint PK_joinPerson_pId primary key(pId); 16 17 create table joinTitle 18 ( 19 titleId int identity(1,1) not null 20 , titleName varchar(10) not null 21 ); 22 alter table joinTitle 23 add constraint PK_joinTitle_titleId primary key(titleId); 24 25 insert into joinTitle(titleName) values(‘Teacher‘),(‘Master‘); 26 insert into joinPerson(pName, titleId) values(‘牛亮亮‘, 1),(‘苏坤‘, 2),(‘杨中科‘, NULL); 27 28 29 select * from joinPerson; 30 select * from joinTitle;
/* 交叉连接 将第一张表的每一行与第二张表匹配一次，得到的结果 作为结果。 语法 from 表cross join 表 */ select * from joinPerson cross join joinTitle; -- 如果两章表中有重名的字段，就会出问题，就需要给表加别名 select t1.pName,t2.titleName from joinPerson as t1 cross join joinTitle as t2;
1 /* 2 内连接：先交叉，在筛选，最后将重要的数据在家回来 3 inner join 4 语法 5 from 表as 别名 6 inner join 7 表as 别名 8 on 别名.列名=别名.列名(条件) 9 */ 10 select * from 11 joinPerson as t1 12 inner join 13 joinTitle as t2 14 on t1.titleId=t2.titleId;
1 --左外链接 2 - 3 /* 4 把左边中的全部记录都显示出来，对于那些在右表中能找到匹配的记录全部显示，不能匹配的显示为null 5 */ 6 7 select * 8 from 9 joinPerson as t1 10 left join 11 joinTitle as t2 12 on t1.titleId=t2.titleId
1 --右外链接 2 /* 3 把右边中的全部记录都显示出来，对于那些在左表中能找到匹配的记录全部显示，不能匹配的显示为null 4 */ 5 6 insert into joinTitle(titleName) values(‘班主任‘); 7 8 select 9 * 10 from 11 joinPerson as t1 12 right join 13 joinTitle as t2 14 on t1.titleId = t2.titleId;
1 -- 视图 2 /* 3 视图-里面存储的是查询语句 4 就是将查询的语句封装成一个对象，每次查询的时候直接操作这个对象即可 5 本质是虚拟表,即一个查询过程 6 语法： 7 create view vw_视图名(一般名字以vw_开头) 8 as 9 select 语句 10 11 */ 12 create view vw_StuInfo 13 as 14 select 15 ROW_NUMBER() over(order by t1.stuId) as n 16 , t1.stuId 17 , t1.stuName 18 , case t1.stuSex when ‘f‘ then ‘女‘ else ‘男‘ end as stuSex 19 , datediff(year, t1.stuBirthdate, GETDATE()) as stuAge 20 , t1.stuEmail 21 , t1.stuAddress 22 , t1.stuPhone 23 , t2.className 24 , t3.testBase 25 , t3.testBeyond 26 , t3.testPro 27 from 28 TestDataBase..Student as t1 29 inner join 30 TestDataBase..Course as t2 31 on t1.classId = t2.classId 32 inner join 33 TestDataBase..Score as t3 34 on t1.stuId = t3.stuId 35 ; 36 go 37 /*视图是一个虚拟表，存储的只是查询语句，并不是数据。 38 如果把真实表的数据修改之后，视图查询的结果也会随之改变 39 用Vw_开头 40 优点1：降低数据库的复杂程度 41 优点2：防止未经许可的用户访问敏感数据（就是视图把表隐藏起来，让你看不到表） 42 */ 43 -- 内联表值函数 44 -- 带有参数的视图 45 -- 作用： 将一个可变条件的查询封装成一个函数对象，执行结果是一张表 46 /* 47 create function fn_函数名 48 (@ 参数名as 类型，) 49 return table 50 as 51 return 查询语句; 52 53 */
1 -- 公用表表达式(CTE) 2 -- 语法 3 /* 4 with 别名 5 as 6 ( 7 结果集 8 ) 9 使用别名的一个查询; 10 */ 11 with t 12 as 13 ( 14 select 15 t1.stuId 16 , t1.stuName 17 , case t1.stuSex when ‘f‘ then ‘女‘ else ‘男‘ end as stuSex 18 , datediff(year, t1.stuBirthdate, GETDATE()) as stuAge 19 , t1.stuEmail 20 , t1.stuAddress 21 , t1.stuPhone 22 , t2.className 23 , t3.testBase 24 , t3.testBeyond 25 , t3.testPro 26 from 27 TestDataBase..Student as t1 28 inner join 29 TestDataBase..Course as t2 30 on t1.classId = t2.classId 31 inner join 32 TestDataBase..Score as t3 33 on t1.stuId = t3.stuId 34 ) 35 select * from t where t.stuName = ‘濮阳语儿‘; 36 37 -- t可以重用 38 -- 自交差 39 -- 生成一个数字表 40 41 select num from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(num); 42 43 -- 自交差 10000 44 select 45 t1.num * 10 + t2.num + 1 46 from 47 (select num from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(num)) as t1 48 cross join 49 (select num from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(num)) as t2 50 ; 51 -- 用公用表表达式 52 with t 53 as 54 ( 55 select num from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as tt(num) 56 ) 57 select 58 t1.num * 1000 + t2.num * 100 + t3.num * 10 + t4.num + 1 as orderId 59 from 60 t as t1 61 cross join 62 t as t2 63 cross join 64 t as t3 65 cross join 66 t as t4 67 order by 68 orderId;
1 -变量声明 2 /* 3 变量 4 声明 赋值 使用 5 declare @变量名类型 6 set @变量名=值 7 8 */ 9 10 declare @num int; 11 set @num=10; 12 select @num; 13 14 --局部变量 15 --系统变量@@开头 16 17 --@@error 最近一次执行Sql 语句的错误码 18 select @@error; 19 20 --@@identity 最近一次插入数据的自动增长编号 21 22 select @@identity 23 24 insert into Exe2.LoginTbl output inserted.id values(‘zdpdsy123‘,‘123‘); 25 26 27 28 declare @sum int=0; 29 declare @i int =0; 30 31 while @i<=100 32 begin 33 set @sum+=@i; 34 set @i +=1; 35 end 36 ; 37 select @sum;
1 -- 事务 2 /* 3 事务 4 最基本的操作以事务为单位 5 将一个已经完成的数据库操作行为规定为一个事务 6 特点： 7 原子性 8 执行的结果是特征（要么完成，要么失败） 9 持久性 10 执行的结果不可逆转 11 一致性 12 一旦完成事务，各个版本的结果都一样。 13 隔离性 14 事务与事务之间不冲突 15 严格定义： 16 17 18 */ 19 20 21 --事务 22 create table bank 23 ( 24 cId char(4) primary key, 25 balance money, --余额 26 ) 27 28 alter table bank 29 add constraint CH_balance check(balance >=10) 30 31 go 32 --delete from bank 33 insert into bank values(‘0001‘,1000) 34 insert into bank values(‘0002‘,10) 35 go 36 37 select * from bank; 38 -- 0001 -> 0002 1000元 39 40 -- 默认的事务方式——隐式事务 41 update bank set balance=balance - 1000 where cid=‘0001‘; 42 update bank set balance=balance + 1000 where cid=‘0002‘; 43 44 /* 45 手动执行事务 46 开启事务->执行语句->判断满足与否->提交或回滚 47 语法 48 开启 begin transaction 49 提交 commit 50 回滚 rollback 51 */ 52 select @@TRANCOUNT; 53 54 begin transaction 55 56 delete from TestDataBase..Score; 57 delete from TestDataBase..Student; 58 59 60 select * from HeiMa8.Exe3.vw_StuInfo; 61 62 63 rollback 64 65 -- 66 select * from bank; 67 68 begin tran 69 declare @myError int; 70 update bank set balance=balance - 900 where cid=‘0001‘; 71 set @myError = @@ERROR; 72 update bank set balance=balance + 900 where cid=‘0002‘; 73 set @myError += @@ERROR; 74 if @myError > 0 75 rollback 76 else 77 commit 78 ; 79 80 /* try-catch 81 begin try 82 end try 83 begin catch 84 end catch 85 */ 86 87 begin tran 88 begin try 89 update bank set balance=balance - 1000 where cid=‘0001‘; 90 update bank set balance=balance + 1000 where cid=‘0002‘; 91 commit; 92 end try 93 begin catch 94 rollback; 95 end catch 96 ;
1 -- 存储过程 2 /* 3 存储过程 4 简单的看成数据库中的方法 5 6 函数，视图，存储过程 7 系统中常用的存储过程sp_ stored procedure 8 执行语法 9 exec 存储过程名字参数; 10 */ 11 --修改数据库名字 12 exec sp_renamedb ‘archive1‘,‘heima‘ 13 14 /* 15 存储过程的语法 16 create proc[edure] usp_存储过程名 17 参数名类型名[=初始值] output 18 ,.... 19 as 20 begin 21 脚本(SQL语句) 22 end 23 */ 24 25 --无参数无返回值的存储过程 26 create proc usp_PersonInfo 27 as 28 select * from joinPerson; 29 go 30 exec usp_PersonInfo; 31 --有参数有默认值的存储过程 32 --带参数的 33 create proc usp_PersonSearch 34 @PersonName nvarchar(20) 35 as 36 select * from joinPerson where pName=@PersonName; 37 go 38 exec usp_PersonSearch @PersonName=‘杨中科‘; 39 40 --带默认值的 41 create proc usp_FenYe 42 @pageIndex int=1, 43 @pageSize int =10 44 as 45 begin 46 select ‘今天天气还行‘; 47 select * from 48 ( 49 select row_number() over(order by stuId) as num,* from 50 Student 51 )as t 52 where t.num between (@pageIndex-1)*@pageSize+1 and @pageIndex * @pageSize; 53 end 54 go 55 56 exec usp_FenYe 2,5; 57 exec usp_FenYe @pageSize=11,@pageIndex=5; --推荐使用 58 59 --有参数有返回值的存储过程 60 create proc usp_Bank 61 @from char(4), 62 @to char(4), 63 @money money, 64 @isSuccess int output 65 as 66 begin 67 begin tran 68 begin try 69 update bank set balance=balance-@money where cid=@from; 70 update bank set balance=balance+@money where cid=@to; 71 commit; 72 set @isSuccess = 1; 73 end try 74 begin catch 75 rollback; 76 set @isSuccess = 0; 77 end catch 78 end 79 go 80 81 declare @isSuccess1 int; 82 83 -- exec Exe3.usp_ZhuanZhangExt ‘0001‘, ‘0002‘, 500, @isSuccess output; 84 exec usp_Bank 85 @from = ‘0001‘, 86 @to = ‘0002‘, 87 @money = 500, 88 @isSuccess = @isSuccess1 output; 89 select @isSuccess1; 90 select * from bank; 91 92 --创建一个登陆的存储过程 93 /* 94 create proc usp_Login 95 @uid varchar(20), 96 @pwd varchar(20), 97 @isOK int output 98 as 99 select @isOK=count(*) from Exe2.LoginTbl 100 where uid=@uid and pwd=@pwd; 101 go 102 select * from Exe2.LoginTbl; 103 104 declare @OK int; 105 exec usp_Login ‘zdp‘,‘123‘,@isOK=@OK output; 106 select @OK; 107 */ 108 109 110 create proc usp_Login 111 @uid varchar(20), 112 @pwd varchar(20), 113 @isOK int output 114 as 115 select @isOK=count(*) from Exe2.LoginTbl 116 where uid=@uid and pwd=@pwd; 117 go 118 119 select * from Exe2.LoginTbl; 120 121 declare @isLogin int; 122 exec usp_Login ‘zdp‘,‘zdp140‘,@isOK=@isLogin output; 123 select @isLogin;
以上是个人的SQL学习总结，有错的地方还望众大神海涵啊
SQL基础和高级总结.zip