一.数据库字符串的特性:
(1).---char 可以存储字符串,如果存储的字符串走出了指定的长度就报错,如果少于就会:char类型长度一旦确定就不会再更改
select LEN(char) from ChatTest --LEN得到存储的字符的个数,与中英文无关
select DATALENGTH(char) from ChatTest --DATALENGTH得到占据的字节长度
(2).--varchar 可以存储字符串,如果存储的字符串走出了指定的长度就报错,如果少于就会:varchar类型长度可以自动收缩
select LEN(varchar) from ChatTest --LEN得到存储的字符的个数,与中英文无关
select DATALENGTH(varchar) from ChatTest --DATALENGTH得到占据的字节长度
(3).---当有中文的时候才需要考虑判断编码----也就意味着如果值中可能含有中文,那么就应该使用n
--nchar 可以存储字符串,如果存储的字符串走出了指定的长度就报错,如果少于就会:ncharr类型长度一旦确定就不会再更改 n---unicode:不管什么字符都需要占据两个字节
select LEN(nchar) from ChatTest --LEN得到存储的字符的个数,与中英文无关
select DATALENGTH(nchar) from ChatTest --DATALENGTH得到占据的字节长度
(4).--nvarchar 可以存储字符串,如果存储的字符串走出了指定的长度就报错,如果少于就会:nvarchar类型长度可以自动收缩 n---unicode:不管什么字符都需要占据两个字节
select LEN(nvarchar) from ChatTest --LEN得到存储的字符的个数,与中英文无关
select DATALENGTH(nvarchar) from ChatTest --DATALENGTH得到占据的字节长度
二.创建数据库
use master --切换当前使用的数据库
if exists( select * from sysdatabases where name='MyTest')
drop database MyTest --删除数据库
go
create database MyTest
on primary
(
name='MyTest_data', --逻辑名称,数据文件一般会添加data做为后缀
size=3mb,--初始大小,08版本最小是3MB
fileGrowth=5%, --文件增长,每次递增上一次大小的5%
fileName='D:\Classes\九期 .net加强+sql server增加\2013-10-15 sql server(1)\案例\MyTest_data.mdf', --文件的全路径,需要添加扩展名
maxSize=100mb
),
(
name='MyTest_data1', --逻辑名称,数据文件一般会添加data做为后缀
size=3mb,--初始大小,08版本最小是3MB
fileGrowth=5%, --文件增长,每次递增上一次大小的5%
fileName='D:\Classes\九期 .net加强+sql server增加\2013-10-15 sql server(1)\案例\MyTest_data1.ndf', --文件的全路径,需要添加扩展名
maxSize=100mb
)
log on
(
name='MyTest_log', --逻辑名称,数据文件一般会添加data做为后缀
size=1mb,--初始大小,08版本最小是3MB
fileGrowth=5%, --文件增长,每次递增上一次大小的5%
fileName='D:\Classes\九期 .net加强+sql server增加\2013-10-15 sql server(1)\案例\MyTest_log.ldf', --文件的全路径,需要添加扩展名
maxSize=100mb
),
(
name='MyTest_log1', --逻辑名称,数据文件一般会添加data做为后缀
size=1mb,--初始大小,08版本最小是3MB
fileGrowth=5%, --文件增长,每次递增上一次大小的5%
fileName='D:\Classes\九期 .net加强+sql server增加\2013-10-15 sql server(1)\案例\MyTest_log1.ldf', --文件的全路径,需要添加扩展名
maxSize=100mb
)
三.数据库创建表:
use MSJDevice
if exists (select * from sysobjects where name='[DevSORetDetail]')
drop table [DevSORetDetail]
create table [DevSORetDetail]
(
[SORDtlID] [varchar](50) NOT NULL,
[SORID] [varchar](50) NULL,
[TraceNo] [varchar](30) NULL,
[DevKey] [int] NULL,
[RecQty] [numeric](22, 2) NULL,
[Qty] [numeric](22, 2) NULL,
[Price] [numeric](22, 2) NULL,
[SaleRate] [numeric](22, 2) NULL,
[RateAmount] [numeric](22, 2) NULL,
[SaleAmount] [numeric](22, 2) NULL,
[MoneyKey] [int] NULL,
[Exchange] [numeric](22, 4) NULL,
[ResonDescs] [nvarchar](200) NULL,
[CreateOrgNo] [varchar](20) NULL,
[CreateDeptNo] [varchar](20) NULL,
[CreateUserNo] [varchar](20) NULL,
[CreateTime] [datetime] NULL,
[EditOrgNo] [varchar](20) NULL,
[EditDeptNo] [varchar](20) NULL,
[EditUserNo] [varchar](20) NULL,
[EditTime] [datetime] NULL,
)
四.插入数据(insert)
----语法: insert into 表名 (列名列表) values( 值列表) 列名列表相当于形参,值列表相当于实参,三个对应(类型对应,数量对应,顺序对应)在这里一样存在
use MyTest
--1.插入所有列的值
insert teacher (name,gender,age,salary,birthday) values('aa',1,20,3000,'1990-9-9')
--如果没有指定列名那么就会默认需要为所有的列添加值 :列名或所提供值的数目与表定义不匹配。
insert teacher values('aa',1,20,3000)
--2.标识列不能人为添加值 "当 IDENTITY_INSERT 设置为 OFF 时,不能为表 'Teacher' 中的标识列插入显式值。"
insert teacher (id,name,gender,age,salary,birthday) values(100,'aa',1,20,3000,'1990-9-9')
--3. 插入的值数量需要和指定的列的数量一致:INSERT 语句中列的数目小于 VALUES 子句中指定的值的数目。VALUES 子句中值的数目必须与 INSERT 语句中指定的列的数目匹配。
--4.如果列的属性是非空,那么就必须插入一个值 :不能将值 NULL 插入列 'Birthday',表 'MyTest.dbo.Teacher';列不允许有 Null 值。INSERT 失败。
insert teacher (name,gender,age,salary) values('aa',1,20,3000)
--5.如果列的属性标记为可以为null,那么就可以不插入值:如果不想为可以为null的列添加值:要么不写,要么写null
insert teacher (name,gender,age,salary,Birthday) values('aa',1,20,null,'1900-8-8')
--6.如果列有默认值:要么不写这一列,那么就会添加默认值,要么可以在值列表中写default
insert teacher (name,gender,age,salary,Birthday) values('aa',1,-100,null,'1900-8-8')
--7.插入的值需要满足表的约束 INSERT 语句与 CHECK 约束"CK_Age"冲突。该冲突发生于数据库"MyTest",表"dbo.Teacher", column 'Age'。
insert teacher (name,gender,age,salary,Birthday) values('aa',1,-100,null,'1900-8-8')
--8.当插入的值里面有日期的时候:如果没有添加'',那么得到的值是系统计算出来的默认值
insert teacher (name,gender,age,salary,Birthday) values('aa',1,20,null,'1990-8-8')
--9.在sql语言中,类型其实不那么严格,任何类型都能添加'',没有错误,除了null,default之外,但是如果是字符串没有添加''就会报错
--因为系统会将这个值转换为目标类型,如果可以转换则进行赋值,如果不可以转换就报错
insert teacher (name,gender,age,salary,Birthday) values('aa','1','20',null,'1990-8-8')
五.删除数据(delete和truncate)
---删除操作也是不可逆的,所以一定需要询问用户是否真的需要删除,同时往往并非真正的删除,而只是做一个删除过后的标记
--能够做为条件的一般都是主键或者是标识列或者是唯一键值
---语法:delete 【from】 表 where 条件
delete from Teacher where Id=14 ---delete 后面不能接字段,是因为删除是一行一行删除的
delete from Teacher where Gender=1 and Salary>5000
---truncate:一次性删除表中的所有记录
--语法:truncate table 表名
truncate table teacher
---delete 是一条一条删除,它每次删除都会将操作写入到日志文件中。 而truncate不会
---delete可以写条件,但是truncate不会
--delete是一条一条删除,效率低下
---delete删除后的表的标识列不会重新从1开始计算,而truncate会重置标识列到初始值
delete from Teacher
六.更新操作
----更新操作 一定要注意添加条件,多字段之间用, 多条件使用not and or
---语法 :update 表 set 字段=新值, 字段2=新值2。。。 where 条件
update Teacher set Age=30 where Id='14'
--多字段修改
update Teacher set Name='张三' ,Age=18 ,Salary='6000' where Id=17
--修改性别是男,同时工资在3500以下,为其加1000块工资
update Teacher set Salary=Salary+1000 where Gender=1 and Salary<3500
update Teacher set Birthday=2000-2-2 where Id=15
七.查询操作
---语法说明 :select *代码所有的列/指定某一些列名 from 表(可以多张表),查询得到虚拟表
use MySchoolMoreData
--查询所有学员信息
select * from Student
--查询某一些列
select StudentNo,StudentName,sex,borndate from Student
--查询男学员的信息-
--语法:select 列 from 表 where 条件
select * from Student where Sex='男' and ClassId=6 ---where是来做数据源的筛选,得到过滤掉不满足条件的记录,而得到满足条件的结果集
---得到6期班所有女生的姓名和学号,及电子邮箱
select StudentNo,StudentName,Email from Student where ClassId=6 and Sex='女'
--select 的作用:1.查询得到结果集 2.输出 除了select into 是来做数据的迁移的
select '1'+'a' --+在数据库里面本质来说是一个运算符,除非两边都是字符类型,那么+就是连接符,如果有一边是数值那么系统会将另外的值也强制转换为数值,如果可以转换就相加,如果不可以转换就报错
八.as 、top 、Distinct关键字
---修改列的中文显示方式 as
select StudentNo as 学号,StudentName 姓名,电子邮箱=Email from Student where ClassId=6 and Sex='女'
--可以在结果集中添加常量列
select StudentNo as 学号,StudentName 姓名,电子邮箱=Email,学校='传智播客' from Student where ClassId=6 and Sex='女'
---通过top关键字来获取指定数量的记录,1.可以指定具体的条数
select top 5 StudentNo as 学号,StudentName 姓名,电子邮箱=Email,学校='传智播客' from Student where ClassId=6 and Sex='女'
--2.也可以指定百分比 ceiling:得到比当前数大的所有整数中的最小值 天花板函数
select top 90 percent StudentNo as 学号,StudentName 姓名,电子邮箱=Email,学校='传智播客' from Student where ClassId=6 and Sex='女'
---Distinct 可以去除重复记录:什么叫重复记录:在你指定的列列表中没有任何一列是重复的值
select distinct Sex,Address,Email from Student order by sex
九.聚合函数
--1.得到班级总人数
select COUNT(0) from Student --count里面不需要指定列名,是因为它是对整条记录而言的,它是得到满足条件的记录数
--2.计算6期 班的总人数
select COUNT(0) from Student where ClassId=6
---得到6期 班年龄最大的学员信息
select MIN(BornDate) from Student where ClassId=6 --如果对于非数值列会按照它们的拼音来排序,再取最大值 最小的是空格 ,然后数字,再是英文字符,最后是中文
select max(BornDate) from Student where ClassId=6
---得到C#考试的平均分
select AVG(StudentResult) from Result where SubjectId=3 ---只能对数值类型的列来做计算
select SUM(studentresult) from Result where SubjectId=3 ---只能对数值类型的列来做计算
select sum(BornDate) from Student --日期类型的值不能做sum和avg聚合运算
--警告: 聚合或其他 SET 操作消除了 Null 值。 说明聚合函数不能计算null. 判断null需要使用is null is not null
select SUM(StudentResult) from Result where StudentResult is not null
十.带条件查询
---带条件的查询 > >= < <= = <> != + - * / %
select * from Result where StudentResult>60 and StudentResult<70
select * from Result where StudentResult between 60 and 70 --相当于大于等于前面一个值小于等于后面这个值
---尽量使用between...and,做过优化
select * from Result where StudentResult between 80 and 70 --如果查询不到数据,就返回一个空的结果集
--select * from Result where 60<StudentResult<70
--Regex(str,@"[深圳]")
---in 可以指定一个具体的范围
---查询班级号为1 ,4 ,6的学员信息
select * from Student where ClassId=1 or ClassId=4 or ClassId=6
select * from Student where ClassId in(1,4,6)
--查询地址是 “广州”或者 “深圳”的学员信息
select * from Student where Address ='广州' or Address='深圳'
--in里面的值需要可以相互转换
select * from Student where Address in('123','广州')
十一.创建约束
---约束名称 约束类型(check,默认值(default),主键 ,唯一键,外键) 约束说明(字段或者表达式)
--check--CK --表达式
--primary key --PK --键
--Unique--UQ--字段名
--Default--DF --为某个字段添加默认值
--foreign key--FK --引用
---1,密码的长度大于6位 删除约束和添加约束都需要去指定表名
if exists(select * from sysobjects where name='CK_LoginPwd')
alter table student drop constraint CK_LoginPwd
alter table student with nocheck
add constraint CK_LoginPwd check(len(loginPwd)>=6),
constraint CK_Phone check(Phone is not null) --可以一次创建多条约束
--默认约束
alter table student
add constraint DF_Addres default(N'没有填写') for address --为某全字段来添加默认值
--主键约束
alter table student
add constraint PK_StudentNo primary key(StudentNo)
--唯一键约束
alter table student
add constraint UQ_Email unique(Email)
--主外键约束
alter table student
add constraint FK_student_grade_gradeid foreign key(gradeId) references Grade(GradeId)
--知道可以级联,但是一般情况下不要这么处理,因为太危险
on delete set default
--[ ON DELETE { NO ACTION --不做任何处理,该报错就报错| CASCADE 级联,删除主表也会删除从表的记录 | SET NULL 将从表的对应列的值设置为null | SET DEFAULT 为从表的列设置默认值 } ]
--[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
十二.分组统计信息
--查询学员表中男女生的人数
select COUNT(*) from Student where Sex='男'
select COUNT(*) from Student where Sex='女'
select * from Student ---先得到整个数据源
select * from Student where Sex='男' --使用where 对数据源进行筛选
select COUNT(*) from (select * from Student where Sex='男' ) as temp --再对筛选后的结果集做统计
select * from Student order by sex
select sex, COUNT(*) from Student group by sex
select MAX(StudentResult),MIN(StudentResult) from Result
--需要统计每个班级的学员数量
select ClassId,COUNT(*) from Student group by ClassId
--请从学生表中查询出每个班的班级Id和班级中男同学的人数--
---与聚合函数一起出现在select查询中的列,要和么被聚合,要么被分组
select ClassId,Sex,COUNT(*) from Student group by ClassId,Sex
---查询出6期班男女生的数量
select * from Student where ClassId=6
select COUNT(*) from (select * from Student where ClassId=6) as temp group by Sex
--4 查询6期班的男女生的数量 1 2 3
select sex,COUNT(*) from Student where ClassId=6 group by Sex
---5.查询6期班的男女生的数量,只显示人数超过6人的信息
--不能使用where 来判断聚合函数的条件,原因有二:1.先where 再聚合,所以使用where 来做条件判断的时候,聚合的值还没有出来 2.根据语法规则,聚合函数的条件不能在where 子句中写
--5 1 2 3 4 6
select sex,COUNT(*) As cnt from Student where ClassId=6 group by Sex having COUNT(*)>=5 order by cnt desc
---看看刚刚的sql语句具体是如何来执行的:
1.select * from Student
2.select * from Student where ClassId=6
3.分组得到两个子结果集----分组
select * from Student where ClassId=6 and Sex='男'
select * from Student where ClassId=6 and Sex='女'
4.having--是对分组统计的结果集来做筛选的
5.显示结果
6.order by 是对得到了结果集再做显示的控制 ,它不可能去改变结果集的数据
select sex,COUNT(*) As cnt from Student where ClassId=6 group by Sex having COUNT(*)>=5 and Sex='男' order by cnt desc
--查询班级人数超过三个人的班级
select ClassId,COUNT(0) from Student group by ClassId having COUNT(*) in (3,4)
--查询每个参加考试的学员的平均分
select StudentNo,AVG(StudentResult) from Result where StudentResult is not null group by StudentNo order by AVG(StudentResult)
十三.模糊查询
---模糊查询:是对字符串类型的值而言---
--=是完全的严格的匹配
select * from Student where StudentName ='张莉'
--- %:代码任意个任意字符
--- _代表单个字符
---[]:代表单个字符
select * from Student where StudentName like '张__' -- like:喜欢 像。。。。一样
--总结:1.模糊查询必须使用关键字:like 2.不能使用=,否则就是完全的匹配
select * from Student where StudentNo not like '1[369]' --如果有多个范围也可以一起来指定啰
select * from Student where StudentName like '张[^丽12345]'
---得到没有填写电子邮箱的学员信息
select * from Student where Email='null'
select * from Student where Email is null
---isnull 函数:如果查询的字段值是null值,那么就可以使用指定的值来替代,注意指定的值的类型需要和原始字段的类型一致
select StudentName,ISNULL(Email,'她没有写,打电话吧') from Student where ClassId=4
select StudentNo,ISNULL(StudentResult,'0') from Result where SubjectId=2
----排序:可以选择升充或者降序排序 如果有多个关键字,那么就先按第一个关键字排序,相同的记录再按第二个关键字排序,排序必须写在最后:因为排序是对结果集再进行数据的重新显示 ,排序默认就是升序排序 asc--升序 desc 降序
select * from Result order by StudentResult Desc,StudentNo asc
--1.查询6期 班所有姓 陈 的学员
select * from Student where ClassId=4 and StudentName like '张%'
--2.查询所有科目中包含 c 字符的科目信息
select * from Subject where SubjectName like '%c%'
--3.查询office最逝一次考试时间
select top 1 ExamDate from Result where SubjectId=1 order by ExamDate desc --升序
select SubjectId from Subject where SubjectName='office'
select MAX(ExamDate) from Result where SubjectId=(select SubjectId from Subject where SubjectName='office')
十四.Union
----联合查询:将多个结果集组成一个结果集:每一个select操作都会生成一个新的结果集(除了select into from )
---列数,与列的类型需要对应一致,如果类型不一致,也需字段的值可以相互的隐式转换,最终的列名由第一个结果集来决定
--不能在最后一个结果集以外的任何结果集查询语句中添加order by 排序
select ' '+cast(StudentNo as CHAR(3)) as 学号,StudentName 姓名 from Student
union --去除重复,效率不高,因为需要做是否重复的判断操作
select cast(ClassId as CHAR(2)) as 班级ID,ClassName as 班级名称 from Grade ---order by ClassName 为什么报错:因为结果集由第一个结果集的列来决定,所以只有学号和姓名,同时order by是得到结果集之后再对结果集的数据做重排的,所以它不能使用第一个结果集以外的列
select * from Student where ClassId=3
union all
select * from Student where ClassId=3
select MAX(StudentResult),MIN(StudentResult),AVG(StudentResult) from Result --可以在同一行显示
select '最高分',MAX(StudentResult) from Result --分多行显示,但是在是同一个结果集中
union
select '最低分',min(StudentResult) from Result
union
select '平均分',avg(StudentResult) from Result
---查询每个学员的学号和成绩同时在最后一行显示最高分和最低分
select ' '+cast (StudentNo as CHAR(4)),cast (StudentResult as CHAR(3)) from Result --空格是最小的,在排序的时候如果是升序排序,那么就会排在最前面
union
select ' 最高分','最低分'
union
select cast (MAX(StudentResult) as CHAR(3)),cast (MIN(StudentResult) as CHAR(3)) from Result
----一次插入多条数据
--1.select 字段列表 into 新的目标表中 from 源表 目标表是系统自动创建的,不能先存在 ,如果已经存在 就会给你报错
--说明:通过这种方式可以创建一个表结构,同时复制表数据,但是需要注意的是:表结构仅仅有标识列属性,其它的,如(主键,唯一键,check约束,关系。。)都没复制,需要自己再重新创建
select LoginPwd,StudentName,Sex,ClassId,Phone,Address,BornDate,Email,isDel into newstudent from Student
--select LoginPwd,StudentName,Sex,ClassId,Phone,Address,BornDate,Email,isDel into student from newStudent
--将数据复制到一个已经存在 的表结构中。注意类型需要对应,列的数量也需要对应,还需要遵守目标表的约束及相应的规范
--insert into 目标表中 select 字段列表 from 源表 ---目标表需要先存在 ,且与数据源(字段数量,字段的类型)能够对应
insert into Student select * from newstudent where email is not null
truncate table admin
---没有数据源也可以一次性插入多条记录 ---从来不用的
insert into Admin
select 'a','a' union all ---union all不会去除重复的记录 union会去除重复项
select 'b','b' union
select 'a','a' union
select 'a','a' union
select 'a','a' union all
select 'a','a' --最后一句不需要添加union
十五.类型转换
--CAST ( expression AS data_type) 将指定的表达式expression强制转换为data_type所规定的类型,如果可以转换就转换,如果不可以转换就报错
select '我的成绩是:'+cast(90 as CHAR(2))
--CONVERT ( data_type, expression,[style]) 第一个参数是目标类型,第二个参数是需要转换的表达式,第三个参数是:日期类型
select '我的成绩是:'+CONVERT(char(2),90)
十六.日期类型
select GETDATE()
--DATEADD 可以来添加一个指定的值 DATEADD (datepart , number, date ):第一个参数指定你需要中的值的单位(年,月日,时分秒。。。),第二个参数是你需要添加的具体的值,第三个参数是对那个日期值来做运算
select DATEADD(MM,-1,GETDATE())
--查询年龄超过18岁的学员信息
select * from Student where BornDate<DATEADD(yyyy,-18,getdate())
---需要查询学员的年龄
--DATEDIFF 得到两个日期的差异值
select DATEDIFF(yy,'1990-1-1',getdate())
--查询年龄超过18岁的学员信息
select * from Student where DATEDIFF(yyyy,borndate,getdate())>=25
--DATENAME可以得到日期的字符串表现形式
select DATENAME(w,getdate())
--DATEPART 得到具体的日期部分 2013-10-18
select cast(DATEPART(yyyy,getdate()) as CHAR(4))+'-'+cast(DATEPART(mm,getdate()) as CHAR(2))+'-'+cast(DATEPART(dd,getdate()) as char(2))
十七.数字函数
--RAND 可以生成随机数 永远只能生成一个0~1之间的随机数,理论上包含0但是不包含1
select RAND()
--ABS取绝对值
select ABS(-100)
--CEILING 可以获取比当前指定的数大的所有整数中的最小值
select CEILING(0.01)
--floor:地板:可以得到比当前指定的数小的所有整数中的最大值
select FLOOR(99.9)
--POWER 幂
select POWER(4,2)
--SQRT 求 开平方--根
select SQRT(10)
---ROUND 四舍五入
select ROUND(5.557245678,2) --多余的位数会补0
--SIGN 如果是正值,就是1,如果是负值就是-1.如果是0就是0
select SIGN(0)
select CONVERT(char(20),GETDATE(),1000) --style是对日期或者日间格式的值而言的。
十八.case ..and..
--case---end 相当于switch...case
--1.做等值判断 ,,它会生成一个列 2.then后面的数据类型需要对应(互相转换) 3.如果判断范围,那么when后面的表达式不一定判断同一列的值 4.不能捕获到null值
select StudentName,
case ClassId --如果在case后面添加了表达式,那么这个case就只能进行等值判断
when 1 then '一期班'
when 2 then '2期班'
when 3 then '3期班'
when 4 then '4期班'
when 5 then '5期班'
when 6 then '6期班'
--when null then 'dsafs'
else '不知道'
end as 班级名称
from Student
---2.也可以判断范围
select StudentName,sex,
case --如果case没有接表达式,那么也可以进行范围的判断,相当于if
when BornDate>'2000-1-1' then '小姑娘'
when BornDate>'1990-1-1' and BornDate<'2000-1-1' then '少女'
when BornDate>'1980-1-1' and BornDate<'1990-1-1' then '×××'
when sex='女' then '1sdfsfsdafasd00'
end as 级别
from Student where Sex ='女'
--案例:百分制--转素质教育
select StudentNo,
case
when StudentResult >=90 then 'A'
when StudentResult between 80 and 89 then 'B'
when StudentResult between 70 and 789 then 'C'
when StudentResult between 60 and 69 then 'D'
when StudentResult <60 then 'E'
when StudentResult IS null then '没有参加考试' ---只有这种形式才能判断空值
end,
ExamDate from Result
--表中有A B C三列,用SQL语句实现:当A列大于B列时选择A列否则选择B列,当B列大于C列时选择B列否则选择C列
select
case
when StudentNo>SubjectId then StudentNo
else SubjectId
end,
case
when SubjectId>StudentResult then SubjectId
else StudentResult
end
from Result
select eName,
case
when eSalary>=10000 then '王牌'
when eSalary> 8000 and eSalary<10000 then '铁牌'
else '纸牌'
end
from employee
十九.连表查询
---表连接的传统实现方式
--select 字段 from 表列表
--查询学员基本信息,班级需要显示为班级名称
--如果从多个表中取得数据,那么一定会有两个表的连接表达式,否则会取出两个表记录数乘积条记录,而能够建立连接的字段,一般就是主外键
select * from grade ,Student where Student.ClassId=Grade.ClassId
----查询学员参加的考试信息---学号,姓名,考试科目,考试成绩和考试日期,以及他所属班级名称
select Student.StudentNo,Student.StudentName, Grade.ClassId, Subject.SubjectName, Result.StudentResult,Result.ExamDate
from Student,Result,Subject,Grade --排名不分先后
where Student.StudentNo=Result.StudentNo
and Result.SubjectId=Subject.SubjectId
and Student.ClassId=Grade.ClassId
---查询每个学员需要参加的考试科目名称
select Student.StudentNo,Student.StudentName,Subject.SubjectName
from Student,Subject,Result
where Student.StudentNo=Result.StudentNo and Result.SubjectId=Subject.SubjectId
select Student.StudentNo,Student.StudentName,Subject.SubjectName
from Student,Subject
where Student.ClassId=Subject.ClassId
--多表连接
--查询学员基本信息,班级需要显示为班级名称
--inner join :相当于from多表,只会取出建立关联的字段相等的记录
select Student.StudentNo,Student.StudentName,Grade.ClassName
from Student
inner join Grade on Student.ClassId=Grade.ClassId --on是说明在那个字段上面建立关联,一样的原则,一般是主外键,只有两个classId值相等,这条记录才会被取出
----查询学员参加的考试信息---学号,姓名,考试科目,考试成绩和考试日期,以及他所属班级名称
select Student.StudentNo,Student.StudentName, Grade.ClassId, Subject.SubjectName, Result.StudentResult,Result.ExamDate
from Student
inner join Result on Student.StudentNo=Result.StudentNo
inner join Grade on Student.ClassId=Grade.ClassId
inner join Subject on Grade.ClassId=Subject.ClassId
where Student.Sex='男'
---查询所有学生的姓名、年龄及所在班级
select Student.StudentName,DATEDIFF(yyyy,borndate,getdate()) 年龄,Grade.ClassName
from Student
inner join Grade on Student.ClassId=Grade.ClassId
--查询年龄超过20岁的学生的姓名、年龄及所在班级
select Student.StudentName,DATEDIFF(yyyy,borndate,getdate()) 年龄,Grade.ClassName
from Student
inner join Grade on Student.ClassId=Grade.ClassId where DATEDIFF(yyyy,borndate,getdate())>=20
--案例3:查询学生姓名、年龄、班级及成绩
select Student.StudentName,DATEDIFF(yyyy,borndate,getdate()),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.StudentResult is null and Result.StudentNo is null
---左连接和右连接
use MyTest
--查询参加了考试的学员信息
select * from Student inner join StuScore on Student.StuNo=StuScore.StuId
--查询学员考试信息,参加了和没有参加的都要显示
select * from Student right join StuScore on Student.StuNo=StuScore.StuId
---交叉连接
select * from Student cross join grade
use MySchoolMoreData
--查询所有学生(参加和未参加考试)的学生姓名、年龄、成绩,如果没有参加考试显示缺考,如果小于60分显示不及格
select Student.StudentName,DATEDIFF(YYYY,borndate,GETDATE()),
case
when StudentResult>=60 then cast(StudentResult AS CHAR(3))
when StudentResult<60 then '不及格'
else '缺考'
end
from Student
left join Result on Student.StudentNo=Result.StudentNo
order by StudentResult Desc
二十.子查询
--子查询有两种:
--独立子查询:子查询本身也是一个完整的查询,可以独立执行的
--相关子查询:子查询中使用了父查询中定义的列
--查询参加了考试的学员信息--
select distinct studentno from Result
select * from Student where StudentNo in(select distinct studentno from Result)
--查询和删除delete一样,都是一条一条遍历
select * from Student where StudentNo=(select distinct StudentNo from Result where StudentNo=student.StudentNo)
--没有参加考试的学员信息
select * from Student where StudentNo not in(select distinct studentno from Result)
---子查询做为虚拟表--必须添加别名
---得到不同班级的男学员的信息
select * from Student where Sex='男'
select COUNT(*) from Student where Sex='男' group by ClassId
select COUNT(*) from (select * from Student where Sex='男') temp group by ClassId
--要求在一个表格中查询出学生的英语最高成绩、最低成绩、平均成绩
select MAX(StudentResult),MIN(StudentResult),AVG(StudentResult) from Result
select MAX(StudentResult) from Result
select MIN(StudentResult) from Result
select AVG(StudentResult) from Result
--子查询做为查询的输出列
select (select MAX(StudentResult) from Result),(select MIN(StudentResult) from Result),(select AVG(StudentResult) from Result)
---子查询如何实现分页
select top 5 * from Student --第一页
select top 5 * from Student where StudentNo not in (select top 5 StudentNo from Student order by StudentNo) --第二页
select top 5 * from Student where StudentNo not in (select top 10 StudentNo from Student order by StudentNo) --第二页
--因为记录有可能删除过,而标识列不会自动更新到合理的值
select * from Student where StudentNo between 1 and 5
select * from Student where StudentNo between 6 and 10
--row _number over(order by)
select *,ROW_NUMBER() over(order by studentno) as id from Student
---它并没有真正修改表的结构,只是临时生成一个不间断的数据列,类似于没有间断的标识列值
select * from (select *,ROW_NUMBER() over(order by studentno) as id from Student) as temp where temp.id between 1 and 5
select * from (select *,ROW_NUMBER() over(order by studentno) as id from Student) as temp where temp.id between 6 and 10
select * from (select *,ROW_NUMBER() over(order by studentno) as id from Student) as temp where temp.id between 11 and 15
二十一.视图
----视图可以认为就是一个查询语句,
select * from Student where sex='女'
select StudentNo,StudentName from Student
---家长需要学员姓名,考试科目及考试成绩
select * from 妈妈要看的
--视图就相当于一个虚拟表,我们可以像操作表一样操作视图,但是一般不要去对其进行修改和删除的操作,因为有可能影响到其它的视图
--1.对视图进行删除:视图或函数 '妈妈要看的' 不可更新,因为修改会影响多个基表。这是因为你的数据来自于多和表,删除视图会影响到多张表的结果
delete from 妈妈要看的 where studentname='张3'
select * from 测试是否可以删除
delete from 测试是否可以删除 where studentno=25
---使用代码创建视图 视图中是只是select查询操作, 不能update,delete.insert,视图只能得到一个结果集,所以不能创建多个select语句
if exists(select * from sysobjects where name='vw_getinfo')
drop view vw_getinfo
go
create view vw_getinfo
as
select * from Student
--select * from Student
go
二十二.sql的设值
----变量的使用
--语法:declare @变量的名称 变量的类型
declare @name nvarchar(20)='没有名字'
--赋值--共同点:1.都可以直接赋值一个确定的值 2.如果使用select查询语句赋值,如果=号右边是一个完整的子查询,那么它们两个赋值方式一样
--1.如果赋值默认值
--declare @name nvarchar(20)='没有名字' --默认值是写在类型的后面哦
--使用set赋值
set @name='aa'
--set @name=(select StudentName from Student where StudentNo=7)
--set @name=(select StudentName from Student) -子查询返回的值不止一个,这种情况是不允许的。
--使用select 可以赋值
select @name='bb'
select @name=(select StudentName from Student)
print @name --文本的形式来输出
select @name --结果集的方式来输出
go
---这两种方式赋值的区别:
--1.set只能一次为一个变量赋值,而select可以一次为多个变量赋值
declare @name nvarchar(20)='aa',@age int=100 --一次可以定义多个变量
--set @name='aa',@age=20 --set不能一次为多个变量赋值
--select @name='aa',@age=20 --select可以同时为多个变量赋值
--print @name
--print @age
--2.如果=号右边的sql语句(不是完整的子查询)返回多个值,那么set会报错(语法有错误),而select会得到最后一次值-,set后面必须接完整的sql语句
--select @name=StudentName from Student --会依次取出每一个得到的值,赋值给当前变量,相当于覆盖,所以会得到最后一个值
----set @name=StudentName from Student --错误,只能写完整的子查询语句,且放在()以内
--print @name
--print @age
--3.使用select赋值的时候,如果查询语句没有返回值,那么变量也会保留默认值,而set会得到null值
--select @name=StudentName from Student where StudentNo=123456
set @name=(select StudentName from Student where StudentNo=123456) ---/默认值会被清除
print @name
print @age
go
--查询学号是"7“的学生参加”最近一次“的“office”课程考试的成绩,要求输出学生姓名和成绩
declare @subjectname varchar(20)='office' --科目名称
declare @studentName nvarchar(20) --学员姓名
set @studentName=(select studentname from Student where StudentNo=7)
declare @subjectId int ---科目ID
select @subjectId=SubjectId from Subject where SubjectName=@subjectname
declare @date datetime
set @date=(select MAX(examdate) from Result where StudentNo=7 and SubjectId=@subjectId)
--下面是查询语句
select @studentName,Result.StudentResult from Result
where StudentNo='7' and SubjectId=@subjectId and ExamDate=@date
二十三.While循环
declare @num int=0
while(1=1) --在sql里面,其实是没有bool这个类型的,bit是二进制数据1和0,所以,它不支持所谓的true和false,在sql里面,如果需要得到true或者false,需要使用关系运算符 > < >= <= = != <>
begin
set @num=@num+1
if(@num=10)
break
print 'a'
end
go
--计算1-100之间所有奇数的和
declare @sum int=0,@num int=0
while(@num<100)
begin
set @num=@num+1
if(@num%2=0)
continue
set @sum=@sum+@num
end
print @sum
go
-- 学校没有钱用了,想收office补考费,如果及格人数超过一半,就减分,每次减2分,直到需要补考的人超过一半。
--需要得到总人数,再计算通过考试的人数,做出判断,如果满足条件,就减分,再判断是否需要减分(循环)
declare @subjectname varchar(20)='office' ---科目名称
declare @subjectid int =(select subjectid from Subject where SubjectName=@subjectname) --科目ID
declare @totalNum int=(select COUNT(*) from Result where SubjectId=@subjectid) ---需要考试这一科考试的总人数
declare @passNum int=(select COUNT(*) from Result where SubjectId=@subjectid and StudentResult>=60) --这一科的通过人数
--if(@passNum>@totalNum/2)
-- begin
print '减分之前'
select * from Result where SubjectId=@subjectid
while(@passNum>@totalNum/2)
begin
update Result set StudentResult-=2 where SubjectId=@subjectid and StudentResult>2
set @passNum=(select COUNT(*) from Result where SubjectId=@subjectid and StudentResult>=60) --这一科的通过人数
end
--end
print '减分之后'
select * from Result where SubjectId=@subjectid
go
--使用另外一种方式来实现
declare @subjectname varchar(20)='office' ---科目名称
declare @subjectid int =(select subjectid from Subject where SubjectName=@subjectname) --科目ID
declare @totalNum int=(select COUNT(*) from Result where SubjectId=@subjectid) ---需要考试这一科考试的总人数
declare @passNum int=(select COUNT(*) from Result where SubjectId=@subjectid and StudentResult>=60) --这一科的通过人数
--if(@passNum>@totalNum/2)
-- begin
print '减分之前'
select * from Result where SubjectId=@subjectid
while(1=1)
begin
if(@passNum>@totalNum/2)
update Result set StudentResult-=2 where SubjectId=@subjectid and StudentResult>2
else
break;
set @passNum=(select COUNT(*) from Result where SubjectId=@subjectid and StudentResult>=60) --这一科的通过人数
end
--end
print '减分之后'
select * from Result where SubjectId=@subjectid
--检查学生“office”课最近一次考试是否有不及格(60分及格)的学生。如有,每人加2分,高于95分的学生不再加分,直至所有学生这次考试成绩均及格(注意加分过程不要违反约束)
go
declare @subjectName varchar(20)='office' --科目名称
declare @subjectid int=(select subjectid from Subject where SubjectName=@subjectName) --科目ID
declare @time datetime=(select MAX(examdate) from Result where SubjectId=@subjectid) --这一科的最近一次考试时间
--有没有不及格的人呢?
declare @count int=(select COUNT(*) from Result where SubjectId=@subjectid and ExamDate=@time and StudentResult<60)
while(@count>0) --说明有不及格的人
begin
update Result set StudentResult+=2 where SubjectId=@subjectid and ExamDate=@time and StudentResult<=95
set @count=(select COUNT(*) from Result where SubjectId=@subjectid and ExamDate=@time and StudentResult<60)
end
二十四.If ...Else
---if else 也有多重和嵌套 {}----begin..and
--计算平均分数并输出,如果平均分数超过60分输出成绩最高的三个学生的成绩,否则输出后三名的学生
declare @avg int
declare @subjectname varchar(20)='office'
declare @subjectid int=(select subjectid from Subject where SubjectName=@subjectname) --也可以使用一个sql语句来赋值默认值
select @avg=AVG(studentresult) from Result where SubjectId=@subjectid
print @avg
if @avg>60
begin
select top 3 * from Result where SubjectId=@subjectid order by StudentResult desc
end
else
begin
select top 3 * from Result where SubjectId=@subjectid order by StudentResult asc
end
二十五.全局变量
update Result set StudentResult=80 where StudentNo=7
print @@error --得到当前执行的语句的错误号,如果没有错误,就返回0,如果有错误,就是一个非0的值
print @@SERVERNAME --服务器名称
print @@servicename---服务名称
update Result set StudentResult=80 where StudentNo=7
select @@ROWCOUNT
select * from Student
select @@ROWCOUNT --所有sql语句都会返回一个受影响的行数
print @@version
---sql却没有要求变量的名称不能以@开头,所以全局变量的样子,但是本质还是一个局部变量
declare @@num int=100
print @@num
二十六.事务
-在sql中,每一条语句默认都是一条单独的批处理语句
update bank set cmoney-=1000 where name='aa'
update bank set cmoney+=1000 where name='bb'
go
--事务:将多和可能修改表数据的操作语句做为一个统一的整体单元来执行,这个语句块,要么都执行,要么都不执行
---事务是对有可能修改表数据的操作而言:新增,删除,修改操作。对查询没有用
--1.原子性:事务必须是原子工作单元;对于其数据修改,要么全都执行,要么全都不执行。--
--2.一致性:事务处理前后,数据必须对应
--3.隔离性:一个事务与另外一个事务是相隔离了,两者之间没有任何的关联
--4.持久性:事务完成之后,它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持
--语法步骤: 如果需要使用事务,就只需要将需要执行的sql命令包含在事务在开始和提交之间或者开始和回滚之间
--开始事务:BEGIN TRANSACTION 开启事务
--事务提交:COMMIT TRANSACTION --提交操作
--事务回滚:ROLLBACK TRANSACTION --取消操作
go
declare @error int=0
begin transaction
update bank set cmoney-=1000 where name='aa'
set @error=@error+@@ERROR --不去做判断,只是记住有没有相应的错误号,如果有,就做累加
--if(@@ERROR<>0) ---不能这样处理:因为如果再回滚了,那么其它的语句就没有包含在事务以内,就是受事务的控制了,那么就达不到使用事务的初衷
-- rollback transaction
update bank set cmoney+=1000 where name='bb'
set @error=@error+@@ERROR
--执行完所有的语句后再统一做出判断 ---秋后算总帐
select * from bank
if(@error<>0) --说明其中一句有错误
rollback transaction
else --没有错误
commit transaction
select * from bank
二十七.存储过程
--存储过程:相当于C#中的方法,可以传递参数,也可以返回值
--1.存储过程的分类:
--excute xp_cmdshell Extends procedure 系统扩充存储过程
--sp---system procedure 系统存储过程
--usp:user system procedure 用户自定义的存储过程
--2.系统存储过程:
execute sp_databases
execute sp_renamedb 'mytest','newDB'
public void Show(int age)
{
string name;
}
自定义存储过程:
--usp_getAllStudentInfo 如果存储过程是sql语句的第一句,那么也不需要使用exec,直接写名称就可以了
---自定义的存储过程
--语法:
--go
--create proc 存储过程的名称
----参数列表 (输入参数,输出参数)
--as
-- begin
-- --模块化sql语句块
-- end
--go
--1.没有返回值,没有参数,没有多逻辑结构的存储过程
if exists(select * from sysobjects where name='usp_getAllStudentInfo')
drop proc usp_getAllStudentInfo
go
create proc usp_getAllStudentInfo
as
select * from Student
go
--调用存储过程得到所有学员信息
execute usp_getAllStudentInfo
--public void Show(int age)
--{
--string name;
--}
--2.创建带一个参数的存储过程
if exists(select * from sysobjects where name='usp_getAllStudentInfobySex')
drop proc usp_getAllStudentInfobySex
go
create proc usp_getAllStudentInfobySex
@sex char(2) ---相当于方法()里面的参数,可以由外部传入值,由存储过程来使用,不需要使用declare 来声明
as
declare @age int ---局部变量,只有存储过程内部可以使用,相当于方法体内声明的变量
select * from Student where Sex=@sex
go
--获取指定性别的学员信息
exec usp_getAllStudentInfobySex '男'
--创建带多个参数的存储过程
if exists(select * from sysobjects where name='usp_getAllStudentInfobySexandclassid')
drop proc usp_getAllStudentInfobySexandclassid
go
create proc usp_getAllStudentInfobySexandclassid
@sex char(2),
@classId int
as
select * from Student where Sex=@sex and ClassId=@classId
go
exec usp_getAllStudentInfobySexandclassid '女',6
--创建带多个参数的存储过程,其中一个参数有默认值
if exists(select * from sysobjects where name='usp_getAllStudentInfobySexandclassid')
drop proc usp_getAllStudentInfobySexandclassid
go
create proc usp_getAllStudentInfobySexandclassid
@classId int,
@sex char(2)='男'--1.当参数有默认值的时候,最好能够将有默认值的参数与在参数列表的最后,这样做的好处就是你可以不为其赋值
as
select * from Student where Sex=@sex and ClassId=@classId
go
--1.调用存储过程的时候,参数是一一对应的,类型对象(至少需要可以相互转换) 个数需要对应(如果有默认值,就可以不传入实参值),顺序对应(第一个实参值永远为赋值给第一个形参,不管类型是否OK,如果为OK,报错),这里需要注意是 特别是顺序
--2.如果不想考虑顺序,那么就可以使用参数=值的方式来调用存储过程,但是需要注意的是,如果有一个使用了参数=值的方式,那么建议所有的调用都使用参数=值的方式
--3.一定需要注意的是:在调用的时候,参数的名称一定需要和存储过程中定义的一样,如果不一样,会给出错误的结果
exec usp_getAllStudentInfobySexandclassid @classid='6',@sex='女'
--创建存储过程得到结果集同时返回数量--返回值参数
if exists(select * from sysobjects where name='usp_getAllStudentcountbySexandclassid')
drop proc usp_getAllStudentcountbySexandclassid
go
create proc usp_getAllStudentcountbySexandclassid
--一般情况下:先写输出参数,再写没有默认的输入参数再创建有默认值的输入参数
@count int output, ---output相当于c#里面有out,它会返回一个值,什么值都没有所谓
@classId int ,
@sex char(2)='男'
as
select * from Student where Sex=@sex and ClassId=@classId
set @count=(select COUNT(*) from Student where Sex=@sex and ClassId=@classId)
go
--补充:
--1.如果接收返回值的变量有默认值,同时在存储过程中没有对输出参数重新赋值那么就终就得到默认值
--2.但是如果是在存储过程中为输出参数给默认值,那么这个值是无效的
declare @cnt int
--返回值必须使用变量去做接收,同时接收的时候必须声明接收变量也是output,不然不会将值正确的传递回来
--exec usp_getAllStudentcountbySexandclassid @classid=6,@count=@cnt output
exec usp_getAllStudentcountbySexandclassid @cnt output, 6, default--没有指定名称就是一一对应
print @cnt
print 'aaaaaaaaaaaa'
----创建通过return返回值的存储过程 return只能reutrn整型类型的数据
if exists(select * from sysobjects where name='usp_getNum')
drop proc usp_getNum
go
create proc usp_getNum
@maleCount int output,
@femaleCount int output
as
declare @totalCount int
select @maleCount=(select COUNT(*) from Student where Sex='男')
select @femaleCount=(select COUNT(*) from Student where Sex='女')
select @totalCount=(select COUNT(*) from Student)
return @totalCount
go
declare @maleNum int,@femaleNum int,@TotalNum int
--set @TotalNum=(exec usp_getNum @maleNum output,@femaleNum output)
exec @TotalNum=usp_getNum @maleNum output,@femaleNum output ---接收return的返回值
print @maleNum
print @femaleNum
print @TotalNum
二十八.触发器
--在grade表是面创建了个触发器,在你对grade表进行插入操作后触发,触发的操作是查询grade表的数据
if exists(select * from sysobjects where name='tr__insert')
drop trigger tr__insert
go
create trigger tr__insert on grade
after insert
as
select * from grade
go
--可以为同个操作创建多个after触发器,但是在触发的时候只会触发第一个创建的,同时需要注意:instead of 触发器每种操作只有一个
if exists(select * from sysobjects where name='tr__insert1')
drop trigger tr__insert1
go
create trigger tr__insert1 on grade
after insert
as
select * from student
go
---
insert into Grade values ('触发器有没有触发?')
--inserted表与deleted表
select * from inserted
if exists(select * from sysobjects where name='tr_insert')
drop trigger tr_insert
go
create trigger tr_insert
on grade after insert
as
select * from inserted --操作之后的表--系统会将数据插入到表中,同时也将需要插入的数据存储到这个内部表中
select * from deleted --操作之前,插入数据之前这个表是空的
go
insert into Grade values ('触发器有没有触发?')
if exists(select * from sysobjects where name='tr_update')
drop trigger tr_update
go
create trigger tr_update on grade after update
as
select * from inserted --操作之后的表--系统会将数据插入到表中,同时也将需要插入的数据存储到这个内部表中
select * from deleted --操作之前,插入数据之前这个表是空的
go
update Grade set ClassName='abccbaabcdefg' where ClassId=65
if exists(select * from sysobjects where name='tr_delete')
drop trigger tr_delete
go
create trigger tr_delete on grade after delete
as
select * from inserted --操作之后的表--系统会将数据插入到表中,同时也将需要插入的数据存储到这个内部表中
select * from deleted --操作之前,插入数据之前这个表是空的
go
delete from Grade where ClassId=65
delete from Grade where ClassId>8
--使用触发器实现唯一键约束
go
create trigger tr_unique
on grade after insert
as
declare @id int,@name varchar(50)
select @id=(select classid from inserted),@name=(select classname from inserted)
if((select count(*) from grade where classname=@name)>1)
--将已经插入的记录删除
begin
print '已经插入成功了,但是我会将它删除掉,不要心痛'
delete from grade where classid=@id
end
else
print 'ok'
go
insert into Grade values('10期班')
三十一.临时表
--只在当前会话中可以使用,离开这个会话就失效
create table #aa
(
id int not null,
name varchar(50) not null
)
select * into #aa from grade
insert into #aa select * from grade
select * from #aa
truncate table grade
insert into grade select name from #aa
create table ##aaa
(
id int not null,
name varchar(50) not null
)
insert into ##aaa select * from grade
select * from ##aaa
三十二.索引
if exists(select * from sysindexes where name='IX_StudentName')
drop index student. IX_StudentName
go
create clustered index IX_StudentName
on student(studentname)
三十三.分页的存储过程
if exists(select * from sysobjects where name='usp_getPageData')
drop proc usp_getPageData
go
create proc usp_getPageData
@totalPage int output,
@pageIndex int=1,
@pageCount int=5
as
select * from (select *,ROW_NUMBER() over(order by studentno) as id from Student) temp where id between (@pageIndex-1)*@pageCount+1 and @pageCount*@pageIndex
set @totalPage=CEILING((select COUNT(*) from Student)*1.0/@pageCount)
go
exec usp_getPageData 3
转载于:https://blog.51cto.com/6588779/1360287