按键
F1 查看函数
Ctrl+K 注释
快捷键
环境配置
查看字符集
SELECT COLLATIONPROPERTY('Chinese_PRC_Stroke_CI_AI_KS_WS', 'CodePage');
936 简体中文GBK
950 繁体中文BIG5
437 美国/加拿大英语
932 日文
949 韩文
866 俄文
65001 unicode UFT-8
修改字符集
ALTER DATABASE db COLLATE Chinese_PRC_CS_AI_WS;
前半部份：指 UNICODE 字符集， Chinese_PRC_ 指针对大陆简体字 UNICODE 的排序规则。 排序规则的后半部份即后缀 含义：
_BIN 二进制排序
_CI(CS) 是否区分大小写， CI 不区分， CS 区分
_AI(AS) 是否区分重音， AI 不区分， AS 区分
_KI(KS) 是否区分假名类型 ,KI 不区分， KS 区分
_WI(WS) 是否区分宽度 WI 不区分， WS 区分
区分大小写 : 如果想让比较将大写字母和小写字母视为不等，请选择该选项。
区分重音 : 如果想让比较将重音和非重音字母视为不等，请选择该选项。如果选择该选项，比较还将重音不同的字母视为不等。
区分假名 : 如果想让比较将片假名和平假名日语音节视为不等，请选择该选项。
区分宽度 : 如果想让比较将半角字符和全角字符视为不等，请选择该选项。
数据库概念
主键（PrimaryKey）主键就是数据行的唯一标识。不会出现重复数据的列才能当主键。一个表可以没有主键，但是会非常难以处理，因此没有特殊理由表都要设定主键
主键有两种选用策略:业务主健和逻辑主键。业务主键是使用有业务意义的字段做主键，比如身份证号、银行账号等，逻辑主键是使用没有任何业务意义的字段做主键，完全给程序看的，业务人员不会看的数据。因为很难保证业务主键不会重复(身份证号重复)、不会变化(帐号升位）如电话号码是为唯一的，但一天会跟许多人打电话，电话号码变的不唯一，同理还有银行卡号，一天交易多次，银行卡号变的不唯一，当因此推荐用逻辑主键（如学号，订单号）。
外键（Foreign Key）
为什么要有外键，如上图生产厂家、地址、电话都是重复多次出现的字段。没必要浪费存储空间，多次反复存取。用int代替冗余，冗余是不可避免，只能降到最低，如上图使用int 厂家编号。
varchar，nchar的区别
--Len():得到当前指定字符串的个数，与中英文无关
--字符串变量.方法()
--字符串函数(字符串变量)
--DataLength()：得到当前字符串占据的字节数，与字符类型有关
select LEN('abcdefg')
select DataLength('中华人民共和国')
--char:char类型空间一旦分配，就不会做自动收缩，就算没有存储满也需要占据指定分配的空间，如果存储过多，就会报错--二进制数据截断的错误,不能越界
select LEN(Char) from chartest
select DataLength(Char) from chartest
--VarChar它会根据存储的内容的长度自动收缩，如果存储的内容小于指定的空间范围，那么多余的空间会收回。所以当存储的内容的长度波动较大的时候就考虑使用VarChar，varchar要判断字符串长度，节省了空间，要消耗时间
select LEN(VarChar) from chartest
select DataLength(VarChar) from chartest
--n--代表是unicode字符,任何字符都占据两个字节空间 。当以后有中文字符的时候，就使用它。
select LEN(NChar) from chartest
select DataLength(NChar) from chartest
--NVarChar 是一个可变unicode字符
select LEN(NVarChar) from chartest
select DataLength(NVarChar) from chartest
一些特性
--1.在sql中没有“”，所以的字符值都使用‘’包含
print '1'
select 1
--2.任何类型的值都可以使用‘’包含
print 0+'1'+'2' --+首先是一个算术运算符，只有+两边都是字符串+才是是一个连接符，如果有一边是数值类型，那么系统会：将另外一个值做隐式的类型转换，如果可以转换就进行转换，如果不可以转换就报错
--3.不区别大小写--对关键字,对于值也不区分
pRiNt 'aaa'
Select * from Admin where name='a'
--4.没有==，赋值和逻辑相等都是使用=
if(1=2)
print 'aaaa'
--使用sql语句创建数据库和表
--语法
--create database 数据库名称
--on primary --在那个文件组上创建.默认是在主文件组上创建 主数据文件
--(
-- --当它不是一句可以独立执行的sql命令的时候，同时它是一个结构中的某一句。。就需要添加,
-- name='逻辑名称_data'，--逻辑名称一般会有一个后缀，数据文件--data 日志文件--log
-- size=初始大小 ，数值不应该包含在’‘以内
-- fileGrowth=增长方式 ，--也不能添加’‘包含
-- maxsize=最大容量，
-- filename='全路径' --最后一句不添加，扩展名：mdf
--)
--log on --日志文件
--(
-- name='逻辑名称_log'，--逻辑名称一般会有一个后缀，数据文件--data 日志文件--log
-- size=初始大小 ，数值不应该包含在’‘以内
-- fileGrowth=增长方式 ，--也不能添加’‘包含
-- maxsize=最大容量，
-- filename='全路径' --最后一句不添加，扩展名：ldf
--)
--先切换当前数据库
use master
--先判断数据库是否存在，如果存在就先删除 exists就是判断（）里面的语句是否返回值，如果有值就返回true,否则就是false
if exists(select * from sysdatabases where name='TestSchool')
--删除数据库
drop database TestSchool
go
--自动创建文件夹 调用存储过程xp_cmdshell，让其帮助我们创建一个文件夹 d:\mydir\database
execute sp_configure 'show advanced options' ,1
RECONFIGURE
execute sp_configure 'xp_cmdshell',1
RECONFIGURE
go
execute xp_cmdshell 'mkdir d:\mydir\database'
create database TestSchool
on primary --primary可以省略
(
name='TestSchool_data',
size=3mb,
filegrowth=10%,
maxsize=100mb,
filename='d:\mydir\database\TestSchool_data.mdf'
),
filegroup userDe
(
name='TestSchool_data1',
size=3mb,
filegrowth=10%,
maxsize=100mb,
filename='E:\aa\TestSchool_data1.ndf' --次数据文件
)
log on
(
name='TestSchool_log',
size=1mb,
filegrowth=10%,
--maxsize=100mb,--日志文件一般不限制最大容量
filename='d:\mydir\database\TestSchool_log.ldf'
),
(
name='TestSchool_log1',
size=1mb,
filegrowth=10%,
--maxsize=100mb,--日志文件一般不限制最大容量
filename='d:\mydir\database\TestSchool_log1.ldf'
)
--创建数据表
--语法：
--create table 表名
--(
-- 字段名称 字段类型 字段特征(是否非空 标识列 默认值 主键 唯一键 check 约束 ),
-- 字段名称 字段类型 字段特征(是否非空 标识列 默认值 主键 唯一键 check 约束 )
--)
----创建老师表Teacher Id、Name、Gender、Age、Salary、Birthday
use TestSchool
if exists(select * from sysobjects where name='Teacher')
drop table Teacher
go
create table Teacher
(
Id int identity(1,1),--设置标识列 identity(标识种子,标识增量)
Name nvarchar(50) not null, --not null标记字段不能为null值.字符类型如果没有指定长度，那么默认就是1
Gender bit not null,
ClassId int ,
Age int,
Salary money, --当一个字段可以为null的时候可以不写也可以写null
Birthday datetime not null
)
练习
--自动创建文件夹 xp---extends procedure
exec xp_cmdshell 'mkdir d:\project'
go
--判断数据库是否已经存在
use master
if exists (select * from sysdatabases where name='mySchool')
drop database mySchool
go --批处理结束的标记
create database mySchool
on primary
(
name='mySchool_data',--逻辑名称
size=3mb,--初始大小.不要使用‘’包含
filegrowth=10%,--文件增长 不要使用‘’包含
MaxSize=1000mb,--最大容量
filename='d:\project\mySchool_data.mdf' --物理路径
)
log on
(
name='mySchool_log',--逻辑名称
size=1mb,--初始大小
filegrowth=2mb,--文件增长
--MaxSize=1000mb,--最大容量,日志一般不限制最大容量
filename='d:\project\mySchool_log.ldf' --物理路径
)
go
use mySchool --切换当前操作的数据库，因为表是这当前数据库而创建的
go
if exists(select * from sysobjects where name='Grade')
drop table Grade
go
create table Grade
(
--字段名称 字段类型 字段特征(标识列 主键 非空 唯一键 主外键 check约束 默认值)
GradeId int identity(1,1),
GradeName nvarchar(50) not null
)
if exists(select * from sysobjects where name='Student')
drop table Student
go
create table Student
(
StudentNo int identity(1,1),
LoginPwd varchar(16) not null,
StudentName nvarchar(50) not null,
Gender bit not null,
GradeId int not null,
Phone varchar(20) null,
[Address] nvarchar(50),
Birthday datetime not null,
Email varchar(50) null
)
--添加约束：
--语法：alter table 表名 add constraint 约束名称 约束类型 约束的说明(字段 表达式 值)
--sysobjects 存储了这一个数据库中所有约束，所有的表，所有的存储过程，所有的视图
if exists(select * from sysobjects where name='PK_Grade_GradeId' )
alter table Grade drop constraint PK_Grade_GradeId
alter table grade
add constraint PK_Grade_GradeId primary key (GradeId)
-- 密码loginPwd的长度大于等于6位
if exists(select * from sysobjects where name='CK_Student_LoginPwd' )
alter table Student drop constraint CK_Student_LoginPwd
alter table Student add constraint CK_Student_LoginPwd check(len(LoginPwd)>6)
-- studentNo学号是主键
-- Gender性别只能取1和0，1代表男，0代表女
-- GradeId是grade表的外键
-- Address有默认值：“未填写”
-- Email:默认值 匿名@未知.com
alter table Student add
constraint PK_Student_StudentNo primary key(studentNo),
constraint DF_Student_Address default('未填写') for Address,
constraint DF_Student_Email default('匿名@未知.com') for Email,
constraint FK_Student_GradeId foreign key(Gradeid) references Grade(GradeId)
--on delete set null
--插入记录
insert into Student values('1234567','张三',1,1,'110','广州',GETDATE(),null)
数据库分离和附加
分离
附加
还有脱机联机
分离和脱机都可以使数据库不能再被使用，但是分离后需要附加才能使用，而脱机后只需联机就可以用了
脱机与联机是相对的概念，它表示数据库所处的一种状态，脱机状态时数据库是存在的，只是被关闭了，用户不能访问而已，要想访问可以设为联机状态
分离与附加是相对的两个概念，分离后，数据库不存在，只存在数据库对应的安装地址
导入导出数据
数据完整性
--数据完整性:
--实体完整性:表的每一行数据就称为一个实体.实体完整性是指 每一行记录是唯一的,不重复的
-- 标识列:系统自动生成，永远不会重复
-- 主键:唯一 非空. 一个表的主键只有一个
-- 唯一键：唯一 但是可以为null,只能空一次。一个表的唯一键可以有多个 右键--索引/键--添加--修改名称，修改类型，确定字段
--域完整性：域就是指字段，域完整性就是为了保证字段的值是合理和准确的
-- 非空，类型，check约束，默认值，关系(主外键约束)
--自定义完整性：用户自己定义的约束规则：
-- check约束 存储过程 触发器
--引用完整性：一个一表的某个字段的值引用自另外一个表的某一个字段。被引用的表就称为主表，。引用表就是称为从表或者外键表
-- 1.选择外键表去创建主外键关系
-- 2.建立主外键关系的字段类型和意义必须一致
-- 3.建立关系的字段 主表中必须是主键或者唯一键
-- 4.添加数据的时候先添加主表数据,再添加外键表
-- 5.删除数据的时候先删除外键表数据再删除主表 数据
--关系建立的表的级联操作:
--1.不执行任何操作:该报错就报错,能删除就删除
--2.级联:删除主表记录,对应的从表记录也将被删除
--3.set null:删除主表，从表对应记录的字段值=null.前提是这个字段可以设置为null
--4.set default:删除主表，从表对应记录的字段值=设置的默认值.前提是这个字段已经设置了默认值了
---使用代码创建约束：
--种类：主键约束（primary key PK） 唯一键约束(unique UQ) 检查约束(check CK) 默认值约束(default DF) 外键约束(foreign key FK)
----创建约束的语法：
--alter table 表名
--add constraint 约束的名称(以简写做为前缀) 约束的类型 约束的说明(字段 表达式 值)
--1.将id设置为主键：
alter table Teacher
add constraint PK_Teacher_id primary key(Id)
--2设置name为唯一键
if exists(select * from sysobjects where name='UQ_Teacher_Name')
alter table teacher drop constraint UQ_Teacher_Name
alter table teacher
add constraint UQ_Teacher_Name unique(name)
--3.设置年龄0~100之间
alter table teacher
add constraint CK_Teacher_Age check(age>0 and age <=100)
--4.为birthday添加默认值约束
alter table teacher
add constraint DF_Teacher_Birthday default('1990-9-9') for birthday --for是说明为那一个字段添加默认值
--5.为classid添加外键约束
if exists(select * from sysobjects where name='FK_Teacher_classid')
alter table teacher drop constraint FK_Teacher_classid
alter table teacher --从表的某一个字段引用主表的某一个字段
with nocheck --不检查现有数据
add constraint FK_Teacher_classid foreign key(classId) references classes(cid)
on delete set null
on update set default
插入数据
--数据插入--
--语法： 方法调用（一 一 对应 顺序对应，数量对应，类型对应）
--insert [into] 表名（字段列表） values(值列表)
--说明：标识列值不管什么时候都不可能插入值,同时插入的值需要满足表的所有完整性约束
--1.为表的所有字段添加值--如果不指定字段列表，那么就默认需要为所有字段添加值
insert into Teacher values('张感动1',1,1,20,5000,'1990-8-15')
insert into Teacher values('张感动2',1,1,20,5000,'1990-8-15')
insert into Teacher values('张感动3',1,1,20,5000,'1990-8-15')
--2.值不能违反表的约束
insert into Teacher values('张三',1,5,200,5000,'1990-8-15')
--3.1.也可以指定为那一些列插入值 -列名或所提供值的数目与表定义不匹配。
insert into Teacher(Name,Gender,ClassId,Birthday) values('张四',1,6,'1990-8-15')
--3.2 INSERT 语句中列的数目大于 VALUES 子句中指定的值的数目。VALUES 子句中值的数目必须与 INSERT 语句中指定的列的数目匹配
insert into Teacher(Name,Gender,ClassId,Birthday) values('张四',1,6)
--4.非空字段一定需要插入值，除非它有默认值
insert into Teacher(Name,ClassId) values('张五',6)
--如果一个字段可以为null或者有默认值，那么在插入的时候也可以:可以为空字段赋值null,默认值字段赋值default
insert into Teacher values('张6',1,6,null,null,'1990-8-15')
insert into Teacher values('张7',1,6,null,null,default)
--所有值都可以使用’‘包含，如果字段的类型是数值，那么系统会自动的类型转换
insert into Teacher values(N'张8','1','6','30','3000','1990-8-15')
--如果字符类型的字段值没有使用’‘，就：1.如果是非数值字符==报错，如果纯数字字符串--OK
insert into Teacher values(8,'1','6','30','3000','1990-8-15')
--如果是日期值没有使用’‘包含，那么就会得系统默认日期
insert into Teacher values('张9','1','6','30','3000',1990-8-15)
--- select into from:可以将from数据源表中的select指定的列的数据into到新表中，新表是系统自行生成的，不能先人为创建，也就不能先存在.新表中列的属性只保留标识列，其余都消失
select * into newtable from Student
delete from newtable
--insert into 表 select from :可以将select查询语句中获取的数据into到指定的表中。表需要先存在
insert into newtable select LoginPwd,StudentName,Sex,ClassId,Phone,Address,BornDate,Email,isDel from Student
更新数据
--数据更新:更新后的数据不能违反表的约束
--语法：
--update 表名 set 字段=值（表达式），字段=值 where 条件(一般能够做条件的是主键，唯一键，标识列)
--修改张8的班级为7
update Teacher set ClassId=6 where Name='张8'
--修改张8 性别 修改为女，同时将年龄修改25 将工资加1000蚊
update Teacher set Gender=0,Age=250,Salary+=1000 where Name='张8'
--判断多条件 not and or 修改性别是男同时是7班，将工资+500
update Teacher set Salary+=500 where Gender=1 and ClassId=7
删除数据
--数据删除 不能删除某一列，因为删除是对记录而言
--1.删除是一条一条删除，每一次删除都会将操作写入到日志文件--效率低
--2.标识列的值不会从种子重新计算
--3.可以触发触发器
--语法：
--delete [from] 表 where 条件
--删除姓名为8的人
delete from Teacher where Name='8'
--多条件删除
delete from Teacher where ClassId=6 and Gender=0 and Age>20
--删除所有数据
delete from Teacher
--truncate
--一次性删除所有记录，日志文件以最小化的方式写入。效率更高
--标识列从种子值重新计算
--不可以触发触发器
--语法：
--truncate table 表名 （不能添加条件，因为它不是一条一条删除，而是一次性删除所有记录，不关注删除的条数）
truncate table teacher
查询数据
--数据检索-
--语法：
--select 字段列表/* from 表列表 where 条件
--查询所有信息
select * from Teacher,Classes where Teacher.ClassId=Classes.CId
--查询指定的列
select Id,Name,Salary from Teacher
--指定查询的条件 查询女老师
select * from Teacher where Gender=0 and Age<30
--为列指定中文别名 --只是结果集的显示。不会修改原始的表结构
select Id as 工号,Name 姓名,工资=Salary,公司='it' from Teacher where Gender=0 and Age<30
--select 可以输出，只不过输出是结果集--以表的形式显示
select 1+1,2,3,4,5
top、distinct
--Top、Distinct
--使用top可以只返回结果集的前N条记录
select top 10 * from Student
--也可以限百分比
select top 10 percent * from Student --不是四舍五入，而是取Ceiling
--去除重复值。它的作用与原始的数据表的记录无关，只与当前结果集有关系：处理查询得到的结果集
select Sex,Address from Student
select distinct Sex,Address from Student
--聚合函数
--MAX（最大值）、MIN（最小值）、AVG （平均值）、SUM （和）、COUNT（数量:记录的条数。）
--查询年龄最小的学员信息
select MAX(BornDate) from Student
--查询年龄最大的学员信息
select min(BornDate) from Student
--如果是字符串，那么就按字符串的拼音进行排序，得到最大和最小值 a-z
select min(StudentName) from Student
select MAX(StudentName) from Student
--count：得到满足条件的记录数 ,会过滤空值
--得到总人数
select COUNT(*) from Student where Sex='女'
--带条件的查询 where
select * from Student where Sex='女'
--查询年龄在15~20之间的女生信息
select * from Student where Sex='女' and BornDate>'1995-1-1' and BornDate<'1999-1-1'
--between ...and 是大于前面前面的值 小于等于 后面的值
select * from Student where Sex='女' and BornDate between '1999-1-1' and '1995-1-1'
--查询1，2，3，4班的学员信息
select * from Student where ClassId=1 or ClassId=2 or ClassId=3 or ClassId=4
--in:可以指定一个具体的范围,它可以取其中的任意值. 要求值的类型是一致(值可以相互转换)
select * from Student where ClassId in (1,2,'3','4')
--查询1，2，3，4班的学员信息
select * from Student where ClassId=1 or ClassId=2 or ClassId=3 or ClassId=4
--in:可以指定一个具体的范围,它可以取其中的任意值. 要求值的类型是一致(值可以相互转换)
select * from Student where ClassId in (1,2,'3','4')
--带条件的查询-模糊查询
select * from Student where StudentName='林'
--通配符：
--%：代码任意个任意字符
--_:代表任意一个字符
--[]:代表一个具体的范围，能够匹配其中一个字符
--^代表取反值.只有一[]中才有意义
--=号代表严格的字符串匹配，所以%只是一个字符串%，如果需要它是通配符必须使用 like 像。。。一样
select * from Student where StudentName like '林%'
select * from Student where StudentName like '林__'
select * from Student where StudentName not like '林%'
--查询学号在11~15号的学员信息
select * from Student where StudentNo like '1[12345]'
select * from Student where StudentNo like '1[1-5]'
select * from Student where StudentNo like '[11-15]' --1 1-1 5 0-9 a-z A-Z
select * from Student where StudentNo like '1[^1-5]'
练习
--1.查询七期班所有姓 周 的学员
select * from Student where StudentName like '周%' and ClassId=(select classid from grade where classname='七期班')
--2.查询所有科目中包含c 字符的科目信息
select * from Subject where SubjectName like '%c%'
--3.查询office最近一次考试时间
SELECT MAX(ExamDate) FROM Result WHERE SubjectId=(SELECT SubjectId FROM Subject WHERE SubjectName='office')
空值处理
---空值处理
--查询没有电子邮箱的学员信息
select * from Student where Email is not null
--ISNULL如果发现对应的值是null值，则以指定的字符串文本进行替换
select StudentNo,StudentName,ISNULL(Email,'没有填写') from Student
排序
select * from Student where Sex='女' order by BornDate desc,StudentNo asc
默认 ASC 升序 a-z 1-100
DESC 降序
分组统计
--统计男女人数
select sex, COUNT(*) from Student group by sex
--查询每一个班级的总人数
--与聚合函数一起出现在select后面进行查询的列，只有两种可能性：被聚合 被分组
--ClassId是列数据，COUNT(*）是单个数据 要用分组
select ClassId,COUNT(*) from Student group by ClassId
多字段分组
--查询每一个班级男女生的人数
select ClassId,sex,COUNT(*) from Student group by ClassId,sex order by ClassId
查询语句关键字
--查询每一个班级男女生的人数, 同时只需要显示人数数量超过3人的记录
--1.where里面不能出现聚合函数做为条件--语法
--2.先执行了where，过滤原数据，再执行了select ,所以别名where不能使用
--3.having:是对分组统计得到的结果集做筛选的。
--4.执行顺序，一共6个关键字，不重复
5.选择 1.拿数据 2.过滤
select top 1 ClassId,sex,COUNT(*) as cnt from Student where Email is not null
3.分组 4.字句，对操作过的数据，再进行操作 6.排序 7.top
group by ClassId,sex having COUNT(*)>3 order by cnt
select top 2 ClassId,sex,COUNT(*) as cnt from Student where Email is not null group by ClassId,sex order by ClassId
练习
--1.查询每个班级的总学时数，并按照升序排列
select classid, SUM(ClassHour) as 总学时 from Subject group by ClassId order by SUM(ClassHour)
--2.查询每个参加考试的学员的平均分
select StudentNo, AVG(StudentResult) from Result group by StudentNo
--3.查询每门课程的平均分，并按照降序排列
SELECT (SELECT SubjectName FROM Subject WHERE SubjectId=Result.SubjectId),AVG(StudentResult) AS '平均分' FROM Result group by SubjectId ORDER BY '平均分' DESC
union 联合
--union:是用来联合多个结果集的
--1.要求联合的多个结果集有相同数量的字段
--2.要求联合的多个结果集对应的列的类型需要一致：所谓的类型一致是指他们可以互相转换
select 100, COUNT(*) from Student where Sex='男'
union all
select 100, COUNT(*) from Student where Sex='男'
--union默认是去除重复值的,效率低， 是因为需要为你做是否重复的判断
--union all就是不去除重复
用union 插入数据
--使用union一次插入多条记录
--union还是可以去除重复记录，只有全部都使用union all才不考虑重复值
insert into Admin
select 'a','bb' union all
select 'a','cc' union all
select 'b','b'
类型转换函数
print '我的分数是：'+100
--cast(源值 as 目标类型)
print '我的分数是：'+cast(100 as char(3))
--convert(目标类型,源数据，[格式]) --格式是对日期值而言
print '我的分数是：'+convert(char(3),100)
--输出生日
print '我的生日是：'+getdate()
print '我的生日是：'+convert(char(20),getdate(),112)
字符串函数
--字符串函数---
--CHARINDEX --IndexOf:查询指定的字符在源字符串的起始位置:位置从1开始计算，如果找不到就返回0
--第一个参数是指你需要查询的字符串 第二个参数是源字符串，第三个参数是指定的查询起始位置
select CHARINDEX('人民','中华人民共和国',4)
--LEN:得到指定的字符串的个数
select LEN('中华人民共和国')
--UPPER()将英文小写字符转换为大写 toUpperCase() ToUpper() Upper() LOWER():转小写
select LOWER(UPPER('sdfSDFGDS'))
--LTRIM():去除左空格 ---RTRIM()去除右空格
select RTRIM(LTRIM(' sdfsadf '))+'123'
--RIGHT()截取，可以从指定的字符串右边开始返回指定数目的字符串.数量可以指定任意的正值，但是不可是负值
select RIGHT('中华人民共和国',-3);
select LEFT('中华人民共和国',30);
--SUBSTRING()
select SUBSTRING('中华sdfdsfsdf人民共和国',CHARINDEX('人民','中华sdfdsfsdf人民共和国'),2)
--REPLACE()
select REPLACE('中华人民共和国','人民','person')
--STUFF:
select STUFF('中华人民共和国',3,2,'person')
数学函数
--数学函数
--RAND可以生成一个0~1之前的随机数，包含0，但是不包含1
select RAND()
--ABS absolute
select ABS(100)
--CEILING:获取比当前数大的最小整数--分页
select CEILING(0.0001)
--FLOOR:获取比当前数小的最大整数
select FLOOR(0.9999999)
--POWER:
select POWER(2,3)
--ROUND 四舍五入.只关注你指定的小数位后一位数值
select ROUND(1.449,2)
--SIGN 正值=1 负值=-1 0=0
select SIGN(-100)
--SQRT 开平方
select SQRT(8)
CASE
--CASE函数用法:
--1.case用来做结果集字段值的替换的
--2.它可以生成一个新列
--3.相当于switch...case 和 if..else
-----第一种使用的语法：
--case 表达式/字段 --如果case后面有接表达式或者字段，那么这种结构只能做等值判断.不能判断null值
-- when 值.then..自定义值
-- 。。。
-- else 如果上面的when都不满足就满足else
--end
select StudentNo,StudentName,Sex,
case ClassId
when 1 then '一期班'
when 2 then '2期班'
when 3 then '3期班'
when '4' then '4期班'
else '我不知道'
end as 班级名称
from Student
--第二种使用方式：
--Case --如果case后面没有接表达式或者值，那么这种结构就相当于if...else可以做范围判断.它可以做null值判断
-- when 条件表达式 then 自定义值
-- 。。。。
-- else 值
--end
select StudentNo,StudentName,
case
when BornDate>'2000-1-1' then '小屁孩'
when BornDate >'1990-1-1' then '小青年'
when BornDate >'1980-1-1' then '大叔'
when BornDate is null then '不知道'
when Sex='男' then '我是男的'
else '中年'
end
from Student
--百分制转换为素质教育
select StudentNo,StudentResult,
case
when StudentResult>=90 then 'A'
when StudentResult>=80 then 'B'
when StudentResult>=70 then 'C'
when StudentResult>=60 then 'D'
when StudentResult IS NULL then '没有参加考试'
else 'E'
end
from Result
SELECT bookid，bookname
CASE
WHEN price is null then '价格未知'
WHEN price between 11 and 19 then '价格在10至20之间'
WHEN price between 20 and 30 then '价格适中'
ELSE convert(varchar(10).price)
END AS 价格
子查询
--子查询--在一个查询中包含着另外一个查询。被包含的查询就叫了查询，包含子查询的查询就叫父查询
--查询比冯晨旭年龄大的学员信息
select borndate from Student where StudentName='冯晨旭'
select * from Student where BornDate<(select borndate from Student where StudentName='冯晨旭')
--查询七期班的学员信息
select classid from grade where classname='七期班'
select * from Student where ClassId=(
select classid from grade where classname='七期班'
)
子查询基本分类：
独立子查询
子查询可以独立运行
相关子查询
子查询中引用了父查询中的结果
--子查询--在一个查询中包含着另外一个查询。被包含的查询就叫了查询，包含子查询的查询就叫父查询
--查询比冯晨旭年龄大的学员信息
select borndate from Student where StudentName='冯晨旭'
select * from Student where BornDate<(select borndate from Student where StudentName='冯晨旭')
--查询七期班的学员信息
select classid from grade where classname='七期班'
select * from Student where ClassId=(
select classid from grade where classname='七期班'
)
子查询基本分类：
独立子查询
子查询可以独立运行
相关子查询
子查询中引用了父查询中的结果
select * from Student where StudentNo=(select distinct studentno from Result where StudentNo=Student.StudentNo)
--子查询的使用：
1.做为条件：当查询需要一个外部值做为条件的时候，可以使用一个独立的查询先得到这个值，再进行条件的判断
注意1：使用子查询做为条件的时候只能返回 一个字段的值
select * from Student where BornDate<(select * from Student where StudentName='冯晨旭')
注意2：子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的.可以使用in
select * from Student where BornDate in(select BornDate from Student)
注意3：如果返回多列值，那么只能使用exists
if exists (select * from sysdatabases)
--做为结果集：记得一定要为子查询获取的结果集添加 别名
--查询七期班的男生信息
select * from Student where ClassId=6 and Sex='男'
select * from Student where ClassId=6
select * from (select * from Student where ClassId=6) temp where Sex='男'
--做为列的值
select (select studentname from student where StudentNo=Result.StudentNo),StudentResult from Result
子查询分页
--使用子查询分页
--第一页
select top 5 * from Student
--第二页
select top 5 * from Student where StudentNo not in(select top 10 Studentno from Student)
--05之后，提供一个专门用于分页的函数
--ROW_NUMBER() over(排序字段)：可以根据指定的字段排序。，对排序之后的结果集的每一行添加一个不间断的行号
--注意：如果使用生成行号的结果集做为子查询，那么必须为结果集命别名，同时为行号添加名称
select ROW_NUMBER() over(order by studentno), * from Student
select * from (select ROW_NUMBER() over(order by studentno) id, * from Student) temp where id>=6 and id <=10
--学好语文，与出查询的框架: 再填空
--查询年龄比“廖杨”大的学员，显示这些学员的信息
select * from Student where BornDate<(select BornDate from Student where StudentName='廖杨')
--查询二期班开设的课程
select * from Subject where ClassId=(select ClassId from grade where classname='二期班')
--查询参加最近一次“office”考试成绩最高分和最低分
select subjectid from Subject where SubjectName='office'
select MAX(examdate) from Result where SubjectId=(select subjectid from Subject where SubjectName='office')
select MAX(StudentResult) 最高分,MIN(StudentResult) 最低分 from Result where SubjectId=(
select subjectid from Subject where SubjectName='office'
) and ExamDate=(
select MAX(examdate) from Result where SubjectId=
(select subjectid from Subject where SubjectName='office'
)
)
子查询复习
子查询:一个查询由还包含着另外N个查询
分类:独立子查询（直接运行子查询语句）相关子查询(子意询中使用了父查询虫的结果)
使用方法:
1.子查询做为条件:如果跟在><>=.<= ..后面要保证子查询只返回了一个值
A。如果查询返回了一行多列值，可以使用exists /not exists,否则报错
B。如果返回了多行一列，可以使用in /not in
2.子查询可以做结果集:
A。如果需要使用到子查询中的字段，必须保证字段有名字（需要为新添加的列命名)B。子查询做结果集必须命名
3.子查询做为列的值
--2.查询学号是"19"的学生参加”最近一次“的“office”课程考试的成绩，要求输出学生姓名和成绩
-- 1.使用子查询
-- 2.如果没有相应的数据则可以更改数据表中的数据
写子查询方法，1.先写需要的字段，没有就连接表。2.过滤条件
select subjectid from Subject where SubjectName='office'
select MAX(ExamDate) from Result where SubjectId=(select subjectid from Subject where SubjectName='office') and StudentNo=19
select Student.StudentNo, Student.StudentName,Result.StudentResult
from Student
inner join Result on Student.StudentNo=Result.StudentNo
where Result.StudentNo=19 and SubjectId=(
select subjectid from Subject where SubjectName='office'
) and ExamDate=(
select MAX(ExamDate) from Result where SubjectId=(
select subjectid from Subject where SubjectName='office'
)
and StudentNo=19)
--使用变量
declare @subjectName nvarchar(50)='office' --科目名称
declare @subjectId int =(select subjectid from Subject where SubjectName=@subjectName) --科目 ID
declare @time datetime --考试日期
set @time=(select MAX(ExamDate) from Result where SubjectId=@subjectId and StudentNo=19)
select Student.StudentNo, Student.StudentName,Result.StudentResult
from Student
inner join Result on Student.StudentNo=Result.StudentNo
where Result.StudentNo=19 and SubjectId=@subjectId and ExamDate=@time
--3.检查学生“office”课最近一次考试是否有不及格（60分及格）的学生。如有，每人加2分，高于95分的学生不再加分，直至所有学生这次考试成绩均及格(注意加分过程不要违反约束)
go
declare @subjectName nvarchar(50)='office' --科目名称
declare @subjectId int =(select subjectid from Subject where SubjectName=@subjectName) --科目 ID
declare @time datetime --考试日期
set @time=(select MAX(ExamDate) from Result where SubjectId=@subjectId)
while(1=1)
begin
if exists(select * from Result where SubjectId=@subjectId and ExamDate=@time and StudentResult<60)
update Result set StudentResult+=2 where SubjectId=@subjectId and ExamDate=@time and StudentResult<=95
else
break;
end
表连接JION
连接： join 需要连接的表 …on 这两个表如果建立关联,意味着需要指定在那一个字段上建立关联
--查询所有学生的姓名、年龄及所在班级--
以前的查询方式，但是会表A条*表B条记录，用表A的一行和表B的每一行进行对比。效率低
select Student.StudentName,Student.BornDate,grade.classname
from Student,grade
where Student.ClassId=grade.ClassId
使用内连接:inner join :如果没有添加inner关键字，默认就是内连接:它可以得到两个表中建立关联字段值相等的记录
select Student.StudentName,Student.BornDate,grade.classname
from Student
inner join grade on Student.ClassId=grade.ClassId;
SELECT PhoneNum.pId,PhoneType.ptName,PhoneNum.pName,PhoneNum.pCellPhone,PhoneNum.pHomePhone
FROM PhoneNum INNER JOIN PhoneType on PhoneNum.pTypeId=PhoneType.ptId;
外连接（OUTER） 分为左外连接（LEFT）、右外连接（RIGHT)、完全外连接（FULL）
--左表是指写在连接关键字前面的表。左连接可以得到左表的所有记录，如果建立关联的字段值在右表中不存在,那么右表中的字段值就是null值替代
select * from PhoneNum
left join PhoneType on PhoneNum.pTypeId=PhoneType.ptId
--右表是指写在连接关键字后面的表。右连接可以得到右表的所有记录，如果建立关联的字段值在左表中不存在,那么左表中的字段值就是null值替代
select * from PhoneNum
right join PhoneType on PhoneNum.pTypeId=PhoneType.ptId
不经常使用交叉连接、全连接
--交叉连接:
相当于from多表但是没有写条件
select * from PhoneNum
cross join PhoneType
一共有A*B条记录
--全连接：相当于同时左右连接
select * from PhoneNum
full join PhoneType on PhoneNum.pTypeId=PhoneType.ptId
练习
--案例1：查询所有学生的姓名、年龄以及所在班级
SELECT Student.StudentName,DATEDIFF(yyyy,Student.borndate,getdate())as '年龄',grade.classname
FROM Student INNER JOIN grade on Student.ClassId=grade.ClassId;
----案例2：查询年龄超过20岁的学生的姓名、年龄及所在班级
SELECT Student.StudentName,DATEDIFF(yyyy,Student.borndate,getdate()) as '年龄',grade.classname FROM Student INNER JOIN grade on Student.ClassId=grade.ClassId WHERE (DATEDIFF(yyyy,Student.borndate,getdate())>=20);
--案例3:查询学生姓名、年龄、班级及成绩
SELECT Student.StudentName,DATEDIFF(yyyy,Student.BornDate,GETDATE()) AS '年龄',grade.classname ,Result.StudentResult
FROM Student
INNER JOIN grade ON Student.ClassId=grade.ClassId
INNER JOIN Result ON Student.StudentNo=Result.StudentNo;
--案例4:查询所有学生(参加及未参加考试的都算)及成绩
SELECT Student.StudentNo,Student.StudentName,Result.StudentResult
FROM Student
LEFT JOIN Result ON Student.StudentNo=Result.StudentNo;
;
--案例5:请查询出所有没有参加考试(在成绩表中不存在的学生)的学生的姓名
SELECT Student.StudentNo,Student.StudentName,Result.StudentResult
FROM Student
LEFT JOIN Result ON Student.StudentNo=Result.StudentNo
WHERE Result.StudentNo is null;
;
--练习3：查询所有学生(参加和未参加考试)的学生姓名、年龄、成绩，如果没有参加考试显示缺考,如果小于60分显示不及格
SELECT Student.StudentName,DATEDIFF(yyyy,Student.BornDate,GETDATE()) AS '年龄',
CASE
WHEN Result.StudentResult<60 THEN '不及格'
WHEN Result.StudentResult IS NULL THEN '缺考'
ELSE CAST(Result.StudentResult AS CHAR(3))
END AS 考试成绩
FROM Student
INNER JOIN Result ON Student.StudentNo=Result.StudentNo
C#连接数据库
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
namespace MySql
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
}
private void btnAdd_Click(object sender, EventArgs e)
{
//创立连接通道
SqlConnection conn = new SqlConnection();
//2.告诉连接通道如何进行连接
string conStr = "server=localhost;uid=sa;pwd=123456;database=TestSchool";
//3.指定通道进行连接
conn.ConnectionString = conStr;
//4.因为连接对象只是个对象，还需要打开
conn.Open();
//5.创建你需要执行的sql语句
string sql = string.Format("insert into grade values('{0}')", textClassName.Text.Trim());
MessageBox.Show(sql);
//创建“执行”命令的对象；这只是一个命令的传递者
SqlCommand comm = new SqlCommand();
//指定命令由谁执行;
comm.Connection = conn;
//指定要执行的sql语句
comm.CommandText = sql;
//执行sql语句，接受从服务器返回的值
int num = comm.ExecuteNonQuery();
//对返回值进行判断
if (num == 1)
{
MessageBox.Show("增加成功");
}
else {
MessageBox.Show("增加失败");
}
}
private void Form1_Load(object sender, EventArgs e)
{
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btnAdd_Click(object sender, EventArgs e)
{
//1.创建连接字符串
string connStr = "server=.;user id=sa;pwd=123;database=MySchoolMoreData";
//2.创建连接通道
using(SqlConnection conn=new SqlConnection(connStr))
{
//3.打开连接
conn.Open();
//4.创建命令语句
string sql = string.Format("insert into grade values('{0}');select studentname from student;select classname from grade; ", txtCName.Text.Trim());
//5.创建“执行"命令的对象：只是一个传递命令的对象同时接收从服务器返回的数据
SqlCommand command = new SqlCommand(sql, conn);
//6.让命令对象执行方法. 命令语句不是由命令对象来进行执行的，因为命令语句是由服务器来执行的。服务器会执行完传递过来的所有命令，同时也会返回所有的返回值。但是方法只能返回某一种值。意味着方法的本质作用就是：接收某一种用户需要的返回值
//int num = command.ExecuteNonQuery();
//MessageBox.Show(num == 1 ? "ok" : "no");
string str = command.ExecuteScalar().ToString();
MessageBox.Show(str);
}
}
}
}
几种创建连接字符串的方式
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btnConnStr_Click(object sender, EventArgs e)
{
//1.记忆 服务器名称 数据库名称
string connStr1 = "server=.;uid=sa;pwd=123;database=MySchoolMoreData";
// 服务器名称 数据库名称
string connStr2 = "Data Source=.;Initial Catalog=MySchoolMoreData;User ID=sa;Password=***********";
// 服务器名称 数据库名称 windows身份验证
string connStr3 = "Data Source=.;Initial Catalog=MySchoolMoreData;Integrated Security=True";
}
private void btnConnectionStringBuilder_Click(object sender, EventArgs e)
{
SqlConnectionStringBuilder ssb = new SqlConnectionStringBuilder();
ssb.DataSource = ".";
ssb.InitialCatalog = "MySchoolMoreData";
ssb.IntegratedSecurity = true;
//通过它的内置属性获取连接串
string connStr = ssb.ConnectionString;
MessageBox.Show(connStr);
}
private void btnGet_Click(object sender, EventArgs e)
{
SqlConnectionStringBuilder ssb = new SqlConnectionStringBuilder();
propertyGrid1.SelectedObject = ssb;
}
}
}
SqlDataReader
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Lv
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void listView1_SelectedIndexChanged(object sender, EventArgs e)
{
}
private void Form1_Load_1(object sender, EventArgs e)
{
string connStr = "Data Source=localhost;Initial Catalog=TestSchool;Integrated Security=True";
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
string sql = "select classid,classname from grade";
SqlCommand comm = new SqlCommand(sql, conn);
//创建一个读取器对象，这个对象可以从服务器每一次只读取一行数据
SqlDataReader reader = comm.ExecuteReader();
//数据还需要去循环读取
while (reader.Read())
//判断有没有下一行记录，如果有就将指针移动到下一行记录。而且还将数据读取到读取器对象
// private Sql Buffer[]_data数组中
{
//先添加主项，再为主项添加子项
ListViewItem lv = new ListViewItem(reader[0].ToString());
lv.SubItems.Add(reader["classname"].ToString());
//将主项添加到控件的Items集合
lVclasset.Items.Add(lv);
}
}
}
}
}
第一个对象Connection,连接池
连接池:存储连接通道对象，当一个连接通道使用完之后，系统并不会卖正的将其释放。而是将其存储在一个连接油里。当你下一次需要使用连接通道的时候，系统会判断连接池中没有空闲的连接通道对象，如果有就直接使用。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string connStr = "Data Source=.;Initial Catalog=MySchoolMoreData;Integrated Securit=True";
SqlConnection conn = null;
try
{
conn = new SqlConnection(connStr);
conn.Open();
MessageBox.Show("打开连接了1");
//conn.Close();
conn.Dispose(); //释放连接通道。其实没有真正释放这个对象，只是将状态关闭，同时将连接字符串清空
MessageBox.Show("打开关闭了");
conn.ConnectionString = connStr;
conn.Open();
MessageBox.Show("打开连接了2");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if(conn!=null)
{
conn.Close();
}
}
}
private void btnPool_Click(object sender, EventArgs e)
{
//pooling=false不使用连接池
string connStr = "Data Source=.;Initial Catalog=MySchoolMoreData;Integrated Security=True;";
//秒表
Stopwatch sw = new Stopwatch();
sw.Start();
for (int i = 0; i < 1000; i++)
{
SqlConnection conn = new SqlConnection(connStr);
string sql = "select * from grade";
conn.Open();
SqlCommand comm = new SqlCommand(sql,conn);
comm.ExecuteNonQuery();
conn.Close();
}
sw.Stop();
MessageBox.Show(sw.Elapsed.ToString());
}
private void button2_Click(object sender, EventArgs e)
{
string connStr = "Data Source=.;Initial Catalog=MySchoolMoreData;Integrated Security=True";
SqlConnection conn = new SqlConnection(connStr);
conn.StateChange += AAA;
conn.Open();
conn.Close();
conn.Open();
}
void AAA(object sender, StateChangeEventArgs e)
{
MessageBox.Show(e.OriginalState.ToString()+" "+e.CurrentState);
}
}
}
第二个连接对象command
操作Sql Server数据库使用sql command对象，sql command表示向服务器提交的一个命令（SQL语句等），CommandText属性为要执行的SQL语句。
创建sql command对象
1.通过new关键字
2.通过IDbConnection。CreateCommand()方法创建（编写通用代码的时候使用（多态））
常用的三个方法
ExecuteNonQuery() 执行对数据、库的增删改，返回受影响的行数，适合insert、delete、update（对其他语句返回-1）
句返回-1）
ExecuteScalar() 执行查询，返回结果集的首行首列
如果只是返回满足条件的个数或者返回第一行第一列的值,那一般就会使用SalCommand 的ExecuteScalar ()方法，例如SQL语句里面写了聚合函数等。它的返回类型是object,需要做强制类型转换,因为有可能将object转换为其它类型，所以一般使用Convert进行转换。(数据库中的所有数据都是值类型，所以读取过来后需要做装箱,在做拆箱只能拆到原始的数据类型）
ExecuteReader（）执行查询，返回DataReader对象
标识列值
select @@identity --获取最后—次插入操作的标识列值
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btnAdd_Click(object sender, EventArgs e)
{
string connStr = "Data Source=.;Initial Catalog=MySchoolMoreData;Integrated Security=True";
using(SqlConnection conn=new SqlConnection(connStr))
{
conn.Open();
string sql = string.Format("select count(*) from [user] where username='{0}' and pass='{1}',",txtClassName.Text.Trim());
SqlCommand comm = new SqlCommand(sql, conn);
string str=comm.ExecuteScalar().ToString();
MessageBox.Show("标识列值是："+str);
}
}
}
}
登录注册
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Register
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string connStr = "Data Source=.;Initial Catalog=TestSchool;Integrated Security=True";
using (SqlConnection conn = new SqlConnection(connStr)) {
conn.Open();
string sql = string.Format("insert into [user] values('{0}','{1}','{2}','{3}')",TbName.Text.Trim(),TbPwd.Text.Trim(),Tbmail.Text.Trim(),TbPhone.Text.Trim());
SqlCommand comm = new SqlCommand(sql, conn);
int num = comm.ExecuteNonQuery();
if (num == 1)
{
MessageBox.Show("ok");
}
else
{
MessageBox.Show("no");
}
}
}
private void BtLogin_Click(object sender, EventArgs e)
{
string connStr = "Data Source=.;Initial Catalog=TestSchool;Integrated Security=True";
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
string sql = string.Format("insert into [user] values('{0}','{1}','{2}','{3}')", TbName.Text.Trim(), TbPwd.Text.Trim(), Tbmail.Text.Trim(), TbPhone.Text.Trim());
SqlCommand comm = new SqlCommand(sql, conn);
int num = (int)comm.ExecuteScalar();
if (num > 0)
{
MessageBox.Show("ok");
}
else
{
MessageBox.Show("no");
}
}
}
}
}
NextResult
//判断是否有下一个结果集，如果有就移动指针读取
if(reader.NextResult()){
while(reader.Reader()){
}
}
if(!reader.IsDBNull(3)){
//数据库中的null读取到c#后被封装为DBNull类型
int cid=Convert.ToInt32（reader["classid"]）;
}
if(!(reader["classid"] is DBNull)){
int cid=Convert.ToInt32（reader["classid"]）;
}
参数
防止sql注入，原理
declare @name nvarchar(50)='wei'
declare @pwd nvarchar(50)='123456'
select COUNT(*) from [User] where UserName=@name and Pass=@pwd
string connStr = "Data Source=.;Initial Catalog=TestSchool;Integrated Security=True";
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
//1.定义参数占位符@参数相当于在sql中创建一个变量，所以也不需要使用“包含
string sql = "select count(*) from [user] where username=@Name and pass=@pwd"；
//2.在c#中创建对应的参数对象，参数名称不区分大小写
SqlParameter p=new SqlParameter（“@Name”，txtName.Text.Trim（））；
SqlParameter p1=new SqlParameter（“@pwd”，txtPwd.Text.Trim（））；
//3.还需要将创建好的参数传递给服务器让其使用。所以让comm对象将参数传递过去
comm.Parameters.Add（p）；
comm.Parameters.Add（p1）；
SqlCommand comm = new SqlCommand(sql, conn);
int num = (int)comm.ExecuteScalar();
if (num > 0)
{
MessageBox.Show("ok");
}
else
{
MessageBox.Show("no");
}
}
重点，将C#中的‘’空字符串变成数据库中的NULL
SqlParameter p1=new SqlParameter（“@pwd”，string.IsNullOrEmpty（txtPwd.Text.Trim（）？DBNull.Value：（object）txtPwd.Text.Trim（）））；
C#导入导出
string connStr = "Data Source=.;Initial Catalog=TestSchool;Integrated Security=True";
using (SqlConnection conn = new SqlConnection(connStr))
{ using（StreamWriter sw=new StreamWriter("grade.txt")）{
conn.Open();
string sql="select classid,classname from grade";
SqlCommand comm = new SqlCommand(sql, conn);
SqlDataReader reader=comm.ExecuteReader();
while(reader.Read()){
sw.WriteLine(reader["classid"]+"|"+reader["classname"]);
}
}
}
using (StreamReader sr = new StreamReader("grade.txt"))
{
string str = sr.ReadLine();//先读取出一行
while (!string.IsNullOrEmpty(str))//如果这一行不是空字符串或者null,就应该做相应的处理
{
string[] split = str.Split('|');
InsertClassName(split[1]);
//一定要记得读取下一行
str = sr.ReadLine();
}
MessageBox.Show("ok");
}
void InsertClassName(string name)
{
string connStr = "Data Source=.;Initial Catalog=MySchoolMoreData;Integrated Security=True";
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
string sql = "insert into grade values(@name)";
SqlParameter p = new SqlParameter("name", name);
SqlCommand comm = new SqlCommand(sql, conn);
comm.Parameters.Add(p);
comm.ExecuteNonQuery();
}
}
SqlDataAdapte
private void Form1_Load(object sender, EventArgs e)
{
string connStr = "Data Source=.;Initial Catalog=MySchoolMoreData;Integrated Security=True";
string sql = "select * from student;select * from grade";
//创建数据适配器：它里面封装了SqlConnection和SqlCommand对象，Reader对象。也就意味着我们不需要显示的创建这两个对象和显示的打开连接通道
SqlDataAdapter da = new SqlDataAdapter(sql, connStr);
//那么数据适配器就会根据用户的请求去运送数据回来。你运送回来是一个结果集那么我们就需要创建一个对应的结构做接收
//DataTable dt = new DataTable();
//如果有多个结果集就可以创建数据集做接收
DataSet ds = new DataSet();
//数据适配器将数据运送回来，存储到你指定的表中.它会根据查询语句生成多个DataTable对象
da.Fill(ds);
//现在就可以指定数据源啦。。数据源可以是集合，表,数组
//this.dgvList.DataSource = ds.Tables[0];
//this.dataGridView1.DataSource = ds.Tables[1];
}
p71-p90要学
索引
索引的简介：
索引分为聚集索引和非聚集索引，数据库中的索引类似于一本书的目录，在一本书中通过目录可以快速找到你想要的信息，而不需要读完全书。
索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间 。
但是索引对于提高查询性能也不是万能的，也不是建立越多的索引就越好。索引建少了，用 WHERE 子句找数据效率低，不利于查找数据。索引建多了，不利于新增、修改和删除等操作，因为做这些操作时，SQL SERVER 除了要更新数据表本身，还要连带立即更新所有的相关索引，而且过多的索引也会浪费硬盘空间。
索引的分类：
索引就类似于中文字典前面的目录，按照拼音或部首都可以很快的定位到所要查找的字。
唯一索引（UNIQUE）：每一行的索引值都是唯一的（创建了唯一约束，系统将自动创建唯一索引）
主键索引：当创建表时指定的主键列，会自动创建主键索引，并且拥有唯一的特性。
聚集索引（CLUSTERED）：聚集索引就相当于使用字典的拼音查找，因为聚集索引存储记录是物理上连续存在的，即拼音 a 过了后面肯定是 b 一样。
非聚集索引（NONCLUSTERED）：非聚集索引就相当于使用字典的部首查找，非聚集索引是逻辑上的连续，物理存储并不连续。
PS：聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> ( column_name [ ASC | DESC ] [ ,...n ] )
[ WITH <backward_compatible_index_option> [ ,...n ] ]
[ ON { filegroup_name | "default" } ]
<object> ::=
{
[ database_name. [ owner_name ] . | owner_name. ]
table_or_view_name
}
<backward_compatible_index_option> ::=
{
PAD_INDEX
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB
| IGNORE_DUP_KEY
| STATISTICS_NORECOMPUTE
| DROP_EXISTING
}
UNIQUE：为表或视图创建唯一索引。 唯一索引不允许两行具有相同的索引键值。 视图的聚集索引必须唯一。如果要建唯一索引的列有重复值，必须先删除重复值。
CLUSTERED：表示指定创建的索引为聚集索引。创建索引时，键值的逻辑顺序决定表中对应行的物理顺序。 聚集索引的底层（或称叶级别）包含该表的实际数据行。
NONCLUSTERED：表示指定创建的索引为非聚集索引。创建一个指定表的逻辑排序的索引。 对于非聚集索引，数据行的物理排序独立于索引排序。
index_name：表示指定所创建的索引的名称。
database_name：表示指定的数据库的名称。
owner_name：表示指定所有者。
table：表示指定创建索引的表的名称。
view：表示指定创建索引的视图的名称。
column：索引所基于的一列或多列。 指定两个或多个列名，可为指定列的组合值创建组合索引。
[ ASC | DESC]：表示指定特定索引列的升序或降序排序方向。 默认值为 ASC。
on filegroup_name：为指定文件组创建指定索引。 如果未指定位置且表或视图尚未分区，则索引将与基础表或视图使用相同的文件组。 该文件组必须已存在。
on default：为默认文件组创建指定索引。
PAD_INDEX = {ON |OFF }：指定是否索引填充。默认为 OFF。
ON 通过指定的可用空间的百分比fillfactor应用于索引中间级别页。
OFF 或 fillfactor 未指定，考虑到中间级页上的键集，将中间级页填充到接近其容量的程度，以留出足够的空间，使之至少能够容纳索引的最大的一行。
PAD_INDEX 选项只有在指定了 FILLFACTOR 时才有用，因为 PAD_INDEX 使用由 FILLFACTOR 指定的百分比。
FILLFACTOR = fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor 的值为1到100。
SORT_IN_TEMPDB = {ON |OFF }：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。 默认为 OFF。
ON 用于生成索引的中间排序结果存储在tempdb。 这可能会降低仅当创建索引所需的时间tempdb位于不同的与用户数据库的磁盘集。
OFF 中间排序结果与索引存储在同一数据库中。
IGNORE_DUP_KEY = {ON |OFF }：指定在插入操作尝试向唯一索引插入重复键值时的错误响应。默认为 OFF。
ON 向唯一索引插入重复键值时将出现警告消息。 只有违反唯一性约束的行才会失败。
OFF 向唯一索引插入重复键值时将出现错误消息。 整个 INSERT 操作将被回滚。
STATISTICS_NORECOMPUTE = {ON |OFF}：用于指定过期的索引统计是否自动重新计算。 默认为 OFF。
ON 不会自动重新计算过时的统计信息。
OFF 启用统计信息自动更新功能。
DROP_EXISTING = {ON |OFF }：表示如果这个索引还在表上就 drop 掉然后在 create 一个新的。 默认为 OFF。
ON 指定要删除并重新生成现有索引，其必须具有相同名称作为参数 index_name。
OFF 指定不删除和重新生成现有的索引。 如果指定的索引名称已经存在，SQL Server 将显示一个错误。
ONLINE = {ON |OFF}：表示建立索引时是否允许正常访问，即是否对表进行锁定。默认为 OFF。
ON 它将强制表对于一般的访问保持有效，并且不创建任何阻止用户使用索引和/表的锁。
OFF 对索引操作将对表进行表锁，以便对表进行完全和有效的访问。
-- 创建唯一非聚集索引
create unique nonclustered --表示创建唯一非聚集索引
index UQ_NonClu_StuNo --索引名称
on Student(S_StuNo) --数据表名称（建立索引的列名）
with
(
drop_existing=on --先删除原来，创建一个新的，off不删除原有的，提示一个错误
pad_index=on, --表示使用填充
fillfactor=50, --表示填充因子为50%，指定创建索引时，每个索引页的数据占索引页大小的百分比
--读写比：100:1 100
--读<写： 50-70
--读写对半：80-90
ignore_dup_key=on, --表示向唯一索引插入重复值会忽略重复值
statistics_norecompute=off --表示启用统计信息自动更新功能
)
--创建非聚集复合索引
create nonclustered index Index_StuNo_SName
on Student(S_StuNo,S_Name)
with(drop_existing=on)
查看索引
EXEC Sp_helpindex Student
视图
视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。
视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。
一、视图的作用
（1）简化了操作，把经常使用的数据定义为视图，可以将复杂的SQL查询语句进行封装。
如在实际工作中，不同的人员只关注与其相关的数据，而与他无关的数据，对他没有任何意义。根据这一情况，可以专门为其创建一个视图，定制用户数据，聚焦特定的数据。此后当他查询数据时，只需 select * from view_name; 就可以了。
（2）安全性，用户只能查询和修改能看到的数据。
使用视图，基表中的数据就有了一定的安全性。因为视图是虚拟的，物理上是不存在的，只是存储了数据的集合，我们可以不通过视图将基表中重要的字段信息给用户。视图是动态的数据的集合，数据是随着基表的更新而更新的。同时，用户对视图，不可以随意的更改和删除，可以保证数据的安全性。
方便了权限管理，让用户对视图有权限而不是对底层表有权限进一步加强了安全性
（3）逻辑上的独立性，屏蔽了真实表的结构带来的影响。
视图的存在: 主要是为了对外提供数据支持(外部系统)；隐藏了基表字段(隐私)；保证了数据库的数据安全(保护数据库内部的数据结构)；可以灵活的控制对外的数据: 保证针对每个接口都有一个单独的数据支持，增强了用户友好性。
二、视图的缺点:
（1）性能差
数据库必须把视图查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么即使是视图的一个简单查询，数据库也要把它变成一个复杂的结合体，需要花费一定的时间。
（2）修改限制
当用户试图修改视图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于简单的视图来说，这是很方便的，但是，对于比较复杂的试图，可能是不可修改的。
在定义数据库对象时，不能不加选择地来定义视图，应该权衡视图的优点和缺点，合理地定义视图。
对视图的修改：（1）单表视图操作: 可以进行增删改, 但是要实现新增: 前提是视图必须包含基表的所有不能为空的字段。（2）多表视图(基表来源两个以上)不能插入数据, 也不能删除数据，但是可以修改数据。
使用视图还是有很多局限性的，并没有像直接使用表那么方便。如果视图定义中包含了group by、union、聚合函数以及其他一些特殊情况，就不能被更新了；更新视图的查询也可以是一个关联语句，但是被更新的列必须来自同一张表；而且所有使用临时表算法实现的视图都无法被更新。
--使用代码创建视图
--create view vw_视图名称
--WITH ENCRYPTION --对视图进行加密
--as
-- 你需要查询的命令
--go
修改视图
ALTER VIEW vw_studentinfo
AS
SELECT * FROM studentinfo；
删除视图
DROP 视图名
if exists(select * from sysobjects where name='vw_getStudentBySex')
drop view vw_getStudentBySex
go
create view vw_getStudentBySex --这个创建语句必须是批处理的第一句
as
--视图里面不能使用order by ,除非你同时使用了top
select top 99.99999 percent * from Student order by StudentName
--select * from grade 视图中只能创建一个select查询
--update grade --不能在视图中创建update delete 和insert
go
--使用视图
--对视图一般不去执行增加删除和修改操作，但是一定需要注意的是，对视图的这些操作会直接影响原始的物理表.但是这些操作仅仅局限于当个表的操作，如果操作涉及到多个表就不会成功。
select * from vw_getStudentBySex
--会成功，因为是单表
update vw_getStudentBySex set phone='123456' where Studentno=1
--会失败，涉及多表
update vw_getinfo set address='广州',studentresult=95 where Studentno=3
变量
--局部变量
语法：
--declare @变量的名称 变量的类型【=默认值】
declare @name nvarchar(50)='wei'
print @name+'123'
go
declare @name nvarchar(50)
--赋值 set select:如果后面是完整的子查询那么两个没有任何的区别
set @name='aa'
select @name='aa'
--查询比刘健大的学员信息
--声明变量
declare @time datetime
select @time=(select borndate from Student where StudentName='刘健')
set @time=(select borndate from Student where StudentName='刘健')
select * from Student where BornDate<@time
go
--两种赋值方式的区别：
--1.set一次只能为一个变量赋值，而select支持一次为多个变量赋值
declare @name nvarchar(10),@age int=20
--set @name='aa',@age=30
select @name='aa',@age=30
--2.如果=号右边是不完整sql语句，那么当语句返回多行一列值的时候，select会得到最后一个值
declare @name nvarchar(10),@age int=20
--当没有用 EXISTS 引入子查询时，在选择列表中只能指定一个表达式
--子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许
-- 多个值不可以，set @name=(select StudentName from Student)
--多个值不可以，select @name=(select StudentName from Student)
3.
--如果=号右边的查询没有返回任何值，那么selcet会保留原始拥有的默认值，set取‘’
go
declare @name nvarchar(10)='aaaaa',@age int=20
set @name=(select Studentname from Student where StudentNo=100)
print @name
--print 'asfasd'
--select @name=Studentname from Student where StudentNo=100
--print @name
print '-----'
----查询参加最近一次“office”考试成绩最高分和最低分
GO
DECLARE @subjectName nvarchar(50)='office' --科目名称
DECLARE @subjectId int --科目ID
SET @subjectId=(SELECT SubjectId FROM Subject WHERE SubjectName=@subjectName) --获取科目ID
DECLARE @time datetime --最近一次考试时间
SELECT @time=MAX(ExamDate) FROM Result WHERE SubjectId=@subjectId --获取科目最近考试时间
SELECT MAX(StudentResult),MIN(StudentResult) FROM Result WHERE SubjectId=@subjectId AND ExamDate=@time
@@全局变量
@@ERROR 最后一个T-SQL 错误的错误号
@@IDENTITY 最后一次插入的标示值
@@LANGUAGE 当前使用的语言名称
@@ROWCOUNT 受上一个SQL语句影响的行数
@@SERVERNAME 本地服务器的名称
@@TRANSCOUNT 当前连接打开的事务数
@@VERSION SQL Server的版本信息
print @@error
select @@ROWCOUNT
select @@SERVERNAME
Transact-SQL语言
给变量赋值
DECLARE @name varchar(20)
DECLARE @age int
SET @name='张三'
SELECT @age=20
PRINT @name
PRINT @age
DECLARE @name varchar(20)
SELECT @name=stuname
FROM studentinfo
WHERE stuno=1
PRINT @name
@@CONNECTIONS --自上次启动以来的连接数（无论失败成功）
@@CPU_BUSY --自上次启动cpu 使用时间
/*
cpu_busy是简单累计运行时间。如果计算机上有多个CPU（多核），有可能每个CPU简单累计值，会超过已知的计算机运行时间。例如，一个四核计算机从开机到现在运行了24小时，但@@CPU_BUSY的值也可能会超过24小时
*/
@@CURSOR_ROWS --它返回在连接上打开的上一个游标中当前拥有的限定行的数目。
@@DATEFIRST
/*SET DATEFIRST n 指定一周的第一天（星期日、星期一、星期二等）。 n 值的范围为 1 到 7。
*/
SET DATEFIRST 3;
GO
SELECT @@DATEFIRST; -- 3 (Wednesday)
GO
@@ERROR:当前一个语句遇到错误，则返回错误号，否则返回0。需要注意的是@ERROR在每一条语句执行后会被立刻重置，因此应该在要验证的语句执行后检查数值或者是将它保存到局部变量中以备将来使用。
DECLARE @num INT,@temp INT
DECLARE @err INT
SELECT @temp=0;
SET @err=0
SET @num =2/@temp;
--执行后@@ERROR为8134
PRINT @@ERROR
--执行后@@ERROR为0
IF @@ERROR = 0
BEGIN
PRINT @@ERROR
END
@@IDLE (Transact-SQL)
返回 SQL Server 自上次启动后的空闲时间。 结果以 CPU 时间增量或“时钟周期”表示，并且是所有 CPU 的累积，因此该值可能超过实际经过的时间。 乘以 @@TIMETICKS 可转换为微秒。
@@IO_BUSY
返回自从 SQL Server 最近一次启动以来，SQL Server 已经用于执行输入和输出操作的时间。 其结果是 CPU 时间增量（时钟周期），并且是所有 CPU 的累积值，所以，它可能超过实际消逝的时间。 乘以 @@TIMETICKS 可转换为微秒。
@@LOCK_TIMEOUT
返回当前会话的当前锁定超时设置（毫秒）。
@@MAX_CONNECTIONS
返回 SQL Server 实例允许同时进行的最大用户连接数。 返回的数值不一定是当前配置的数值。
@@OPTIONS
返回有关当前 SET 选项的信息。
@@VERSION
返回 SQL Server 的当前安装的系统和生成信息。
@@ROWCOUNT
返回受上一语句影响的行数。 如果行数大于 20 亿，请使用 ROWCOUNT_BIG。
SELECT *FROM studentinfo
PRINT '一共查询了' +CAST(@@ROWCOUNT AS VARCHAR(5))+'条记录'
--CAST 转化格式
IF
-- 计算Office科目平均分并输出，如果平均分数超过60分输出成绩最高的三个学生的成绩，否则输出后三名的学生
GO
DECLARE @subjectName nvarchar(50)='office' --科目名称
DECLARE @subjectId int=(SELECT Subject.SubjectId FROM Subject WHERE SubjectName=@subjectName) --科目ID
DECLARE @avg int --平均分
SET @avg=(SELECT AVG(StudentResult) FROM Result WHERE SubjectId=@subjectId and StudentResult is not null)
IF(@avg>=60)
BEGIN
PRINT '考的不错,输出前三名'
SELECT TOP 3 * FROM Result WHERE SubjectId=@subjectId ORDER BY StudentResult DESC
END
ELSE
BEGIN
PRINT '考的不错，输出后三名'
SELECT TOP 3 * FROM Result WHERE SubjectId=@subjectId ORDER BY StudentResult
END
WHILE
可以嵌套
--没有｛｝，使用begin..end
--不能使用true/false,只能使用条件表达式
--如果里面只有一句就可以不使用begin..and包含
--可以使用contiune/break
--
--如果office不及格的人超过半数(考试题出难了)，则给每个人增加2分,循环加，直到不及格的人数少于一半
GO
DECLARE @subjectName nvarchar(50)='office' --科目名称
DECLARE @subjectId int =(SELECT Subject.SubjectId FROM Subject where SubjectName=@subjectName) --科目ID
DECLARE @classId int =(SELECT classid FROM Subject WHERE SubjectName=@subjectName) --这一科目所属的班级ID
DECLARE @totalNum int --这一科目考试总人数
SET @totalNum=(SELECT COUNT(*) FROM Student WHERE ClassId=@classId) --需要考试这一科目的总人数
DECLARE @unpassNum int =(select COUNT(*) from Result where SubjectId=@subjectId and StudentResult<60) --这一科目考试通过的学员人数
while(@unpassNum>@totalNum/2) --如果不及格人数超过总人数一半
begin
update Result set StudentResult+=2 where SubjectId=@subjectId and StudentResult<=98
--重新计算不及格人数
set @unpassNum=(select COUNT(*) from Result where SubjectId=@subjectId and StudentResult<60)
end
事务
--事务
--aa转1000给bb
update bank set cmoney=cmoney-1000 where name='aa'
update bank set cmoney=cmoney+1000 where name='bb'
--上面这种情况要求我们需要 使用某一种方式进行处理 ，这种方式就是事务
--事务：就是指这些语句要么都能成功执行，要么都不执行---事务只是一种处理机制。
--事务是对 有可能对表数据进行更改的操作而言--增加删除和修改。对查询没用。
---事务的特点’：CUID
--1.原子性:事务不可以再分，事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行
--2.一致性:事务处理前后。，数据需要保持某种程度的一致
--3.隔离性：每一个事务是独立的，不受其它事务影响
--4.持久性:事务一旦提交，对数据的修改永久保留
--如何使用事务：将你执行的增加删除和修改命令包含在 事务的 开启和提交 或者 开启和回滚 之间。
--事务开启：begin transaction
--事务提交：commit transaction
--事务回滚：rollback transaction
--使用事务进行转帐
--不能一发生错误就回滚
declare @cuowuhao int =0 --记录执行语句过程中有可能出现的错误号
begin transaction
update bank set cmoney=cmoney-1000 where name='aa'
set @cuowuhao=@cuowuhao+@@ERROR --只是做一个错误号的累加
update bank set cmoney=cmoney+1000 where name='bb'
set @cuowuhao+=@@ERROR
select * from bank
if(@cuowuhao<>0) --有错误 只有全部语句执行完之后，再进行整体的判断
rollback transaction
else
commit transaction
存储过程
存储过程—就像数据库中运行方法（函数）
和C#里的方法一样，有存储过程名、存储过程参数组成/可以有返回结果
优点：
1.执行速度更快-在数据库中保存的存储过程SQL语句都是编译过的允许模块化程序设计–类似方法的复用
2.提高系统安全性-防止SQL注入
3.减少网络流通量-只要传输存储过程的名称
系统存储过程
由系统定义，存放在master数据库中
名称以“sp”开头或”xp”开头,
自定义存储过程，自定义的存储过程以usp_开头。
存储过程
方法说明：
方法名称 参数 调用 返回值
参数主要是 一 一对应的原则
1.类型对应--子类可以替换父类 int--double
2.数量对应--默认值、可变参数
3.顺序对应：可以使用 参数：值的方法调用
返回值：通过return 返回值。但是只能返回单个值
可以通过Ref/out扩展方法的”返回值"
void Show(形参)
{
自定义局部变量
逻辑语句
}
创建存储语法：
go
create procedure usp_存储过程名称
(形参) 可以以这个里面定义参数
as --相当于方法体
--{
-- 自定义局部变量
-- 逻辑语句
--}
go
1.创建查询所有学员信息存储过程
if exists(select * from sysobjects where name='usp_getAllStuInfo')
drop proc usp_getAllStuInfo
go
create procedure usp_getAllStuInfo
as
select * from Student
go
go
调用存储过程，获取所有学员信息
exec usp_getAllStuInfo
execute usp_getAllStuInfo
--创建查询指定性别的学员信息存储过程
if exists(select * from sysobjects where name='usp_getAllStuInfoBySex')
drop proc usp_getAllStuInfoBySex
go
create procedure usp_getAllStuInfoBySex
@sex char(2) --形参只是声明，不是定义，所有不需要declare
as
print @sex
select * from Student where Sex=@sex
go
--调用存储过程。返回指定性别的学员信息
Exec usp_getAllStuInfoBySex '女'
--获取指定性别和班级名称的学员信息
if exists(select * from sysobjects where name='usp_getAllStuInfoBySexAndClassName')
drop proc usp_getAllStuInfoBySexAndClassName
go
create proc usp_getAllStuInfoBySexAndClassName
@sex char(2),
@className nvarchar(50)
as
declare @classId int --科目 ID
set @classId=(select classid from grade where classname=@className)
select * from Student where Sex=@sex and ClassId=@classId
go
--执行存储过程，返回指定班级名称和性别的学员信息
exec usp_getAllStuInfoBySexAndClassName '女','七期班'
--创建有默认值的存储过程
if exists(select * from sysobjects where name='usp_getAllStuInfoBySexAndClassName')
drop proc usp_getAllStuInfoBySexAndClassName
go
create proc usp_getAllStuInfoBySexAndClassName
@className nvarchar(50)
@sex char(2)='男', --往往会将有默认值的参数写在所有参数列表的最后,调用方法可以不写这个参数
as
declare @classId int --科目 ID
set @classId=(select classid from grade where classname=@className)
select * from Student where Sex=@sex and ClassId=@classId
go
--参数传递顺序一致：第一个实参默认就是传递给第一个形参。。依次累推
--如果有默认值，那么 可以使用default
exec usp_getAllStuInfoBySexAndClassName '七期班',default
--往往会将有默认值的参数写在所有参数列表的最后，这样调用
exec usp_getAllStuInfoBySexAndClassName '七期班'
--也可以使用 参数 = 值 的方式调用存储过程，这样就与顺序没有关系了。
--一旦使用了 '@name = value' 形式之后，所有后续的参数就必须以 '@name = value' 的形式传递。前面没有限制
exec usp_getAllStuInfoBySexAndClassName @className='七期班',@sex='女'
--创建带输出参数的存储过程
--根据班级和性别查询学员，同时返回总人数和指定性别的人数
if exists(select * from sysobjects where name='usp_getSInfoAndCount')
drop proc usp_getSInfoAndCount
go
create proc usp_getSInfoAndCount
@totalNum int output, --如果一个参数添加了output修饰，那么说明：它是一个输出参数。标明了output说明了你会向服务器请求返回这个参数的值。而服务器也知道标识了output的参数在以后需要返回
@claNum int output, --指定班级和性别的总人数
@ClassName nvarchar(50), --输入参数：需要用户传入值
@sex char(2)='男'
as
declare @cid int=(select classid from grade where classname=@ClassName) --根据班级名称获取班级ID
select * from Student where ClassId=@cid and Sex=@sex
set @totalNum=(select COUNT(*) from Student )--总人数
set @claNum =(select COUNT(*) from Student where ClassId=@cid and Sex=@sex)
return 10000
go
--调用在输出参数的存储过程
--服务器向你返回值，用户就需要创建对应的变量做接收
--标明了output说明了你会向服务器请求返回这个参数的值。而服务器也知道标识了output的参数在以后需要返回
declare @tnum int,@cnum int
exec usp_getSInfoAndCount @ClassName='七期班' ,@totalNum=@tnum output,@claNum=@cnum output
print @tnum
print @cnum
print 'ok'
--创建有返回值的存储过程
---返回指定人数
if exists(select * from sysobjects where name='usp_getNameByNo')
drop proc usp_getNameByNo
go
create proc usp_getNameByNo
@cid int
as
declare @cnt int
set @cnt=(select COUNT(*) from Student where ClassId=@cid)
--return只能返回 整数值
return @cnt
go
--调用存储过程，返回指定学号的学员名字
declare @count int
exec @count= usp_getNameByNo 6
print @count
C#中使用存储过程，直接调用存储过程名字即可。
如果有参数则进行如下操作：
SqlDataAdapter da =new SqlDataAdapter("usp___",connStr);
//1.确定命令类型是存储过程，有参数必须设置命令类型，否则当sql语句处理，里面没有参数占位符，不会使用参数。
da。SelectCommand.CommandType=CommandType.StoreProcedure;
//2.声明定义参数
SqlParameter P =new SqlParameter(@SEX,'女')；
da.SelectCommand.Parameters.Add(p);
DataTable dt=new DataTable（）；
da.Fill(dt);
this.dgvList.DataSource=dt;
//调用多个参数存储过程
SqlDataAdapter da =new SqlDataAdapter("usp___",connStr);
da。SelectCommand.CommandType=CommandType.StoreProcedure;
//2.声明定义参数
SqlParameter[] P ={
new SqlParameter(@SEX,'女')；
new SqlParameter(@className,'');
}
da.SelectCommand.Parameters.AddRange(p);
DataTable dt=new DataTable（）；
da.Fill(dt);
this.dgvList.DataSource=dt;
//调用有输出参数的存储过程
SqlDataAdapter da =new SqlDataAdapter("usp___",connStr);
da。SelectCommand.CommandType=CommandType.StoreProcedure;
SqlParameter[] P ={
new SqlParameter(@num1,SqlDbType.Int)；
new SqlParameter(@num2,SqlDbType.Int);
}
da.SelectCommand.Parameters.AddRange(p);
//4.对于 output参数，一定需要设置参数的方向
ps[0].Direction = ParameterDirection.Input; //默认值，没有设置也是输入参数
//如果参数的方向是output,那么就说明它是向服务器请求参数中对应名称的变量值。那么服务器就会将对应的变量值返回存储到参数对象的Value属性中
ps[1].Direction = ParameterDirection.Output;
ps[2].Direction = ParameterDirection.Output;
ps[3].Direction = ParameterDirection.Input;
ps[4].Direction = ParameterDirection.ReturnValue;
DataTable dt = new DataTable();
da.Fill(dt);
this.dgvList.DataSource = dt;
MessageBox.Show(ps[1].Value + " " + ps[2].Value + " " + ps[4].Value);
//分页
private void btnPre_Click(object sender, EventArgs e)
{
if (this.pageIndex == 1)
{
MessageBox.Show("客官没有了");
return;
}
pageIndex--;
SqlParameter[] ps ={
new SqlParameter("@pageIndex",this.pageIndex),
new SqlParameter("@pageCount",this.pageCount),
new SqlParameter("@toPage",SqlDbType.Int)
};
ps[2].Direction = ParameterDirection.Output;
this.dgvList.DataSource = SqlHelper.ExecuteTable("usp_getPageData", CommandType.StoredProcedure, ps);
toalpage = (int)ps[2].Value;
}
#endregion
}
临时表
--临时表
--局部临时表:只能在当前创建临时表的会话中使用。如果关闭了这么会话，那么临时表就自动消失
create table #temp
(
--字段名称 类型 特征
cid int ,
cname nvarchar(50)
)
select * into #newtemp from grade
select * from #newtemp
truncate table grade
insert into grade select classname from #newtemp
---全局临时表只要不关闭当前会话，那么在其它会话中还是可以使用，但是如果关闭当前会话，那么临时也会消失
create table ##temp
(
--字段名称 类型 特征
cid int ,
cname nvarchar(50)
)
insert into ##temp select * from grade
select * from ##temp
truncate table grade
insert into grade select cname from ##temp
触发器
一种特殊类型的存储过程。
不同;存储过程调用名称执行
触发器的调用通过事件触发，自动调用执行。
对表进行插入、更新、删除自动执行
又是一种特殊的事务，可以进行回滚
游标
--1.定义游标
DECLARE user_curor CURSOR LOCAL -- GLOBAL 全局游标
-- STATIC 静态游标 更改不会马上执行，需要关闭游标
--dynamic 动态游标
FOR
-- 查询SQL
SELECT UserId,UserName,Age FROM UserInfos
WHERE UserId>2
--2.打开
OPEN user_curor
--varUser 存放单行数据
DECLARE @varUser cursor,@uId int,@uName varchar(50),@age int
SET @varUser=user_curor
-- next 向下 prior 向上 first 第一个 last 最后一个
-- absolute 绝对位置 relative n 从当前位置开始第n个
FETCH NEXT FROM @varUser INTO @uId,@uName,@age --游标前进赋值
WHILE @@FETCH_STATUS=0 --判断是否成功
BEGIN
PRINT CAST(@uId AS VARCHAR)+','+@uName+','+CONVERT(VARCHAR,@age);
update UserInfo set Age=29 WHERE CURRENT OF
FETCH NEXT FROM @varUser INTO @uId,@uName,@age
END
--关闭游标
close user_cursor
--释放游标
deallocate user_cursor
函数
多声明表值函数
SQL优化
统计运行时间等
SET STATISTICS PROFILE ON
--SET STATISTICS IO ON
--SET STATISTICS TIME ON
GO
--SET STATISTICS PROFILE OFF
--SET STATISTICS IO OFF
--SET STATISTICS TIME OFF
GO
2674
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
