数据库及ADO.NET学习（二）

练习

题目：
练习1：给studentId是1的英语成绩加10分
练习2：考试题偏难，所有人的成绩加5分，如果加5分后大于100则不加分
练习3：所有女学生的年龄减1岁
删除工资大于2000的老师
将老师表清空
删除所有老师
删除数据时候 把自增长列的值还原成种子
代码：

update TblScore set tEnglish=tEnglish+10 where tSId=1
update TblScore set tEnglish=tEnglish+5 where tEnglish<96
update TblScore set tMath=tMath+5 where tMath<96
update TblStudent set tSAge=tSAge-1 where tSGender='女'
delete from TblTeacher where tTSalary>2000
delete from TblTeacher
truncate table TblTeacher

约束（设计器创建）

数据库约束是用来保证数据的完整性而实现的一套机制，但是数据库约束应当是最后一道防线，程序有多层的机制保证数据完整性。

唯一约束

只能出现一次，可以有一个空值

保存后，如果强行插入会出错

检查约束

gender列必须是男或者女

主键约束

外键约束

注意，表A中的主键在B中为外键，右键关系，设置主外键，建立主外键的关系后，B中的外键内容A的主键必须要有。B中引用了A的数据，主键表A不能删除那一条数据，除非设置主外键的时候设置为级联，主外键涉及这一条的数据都会被删除，操作如下图所示。

约束（代码操作）

select * from Employees
drop table Employees
drop table Department
create table Employees
(
	EmpId int identity(1,1),
	EmpName varchar(50),
	EmpGender char(2),
	EmpAge int,
	EmpEmail varchar(100),
	EmpAddress varchar(500)
)
create table Department
(
	DepId int identity(1,1) primary key,
	DepName varchar(50) unique not null	
)
select * from Employees
select * from Department
use TestSchool;
--手动删除一列
alter table Employees drop column EmpAddress
--手动增加一列
alter table Employees add EmpAddress varchar(500)
--修改一列的数据类型
alter table Employees alter column EmpAddress varchar(200)
--为EmpId增加主键约束
alter table Employees add constraint PK_Employees_EmpId 
primary key(EmpId)
--为EmpName增加非空约束（修改列）
alter table Employees alter column EmpName varchar(50) not null
--为EmpName增加唯一约束
alter table Employees add constraint UQ_Employees_EmpName unique(EmpName) 
--为EmpGender增加默认约束‘男’
alter table Employees add constraint DF_Employees_EmpGender default('男') for EmpGender
--为EmpAge增加检查约束
alter table Employees add constraint CK_Employees_EmpAge check(EmpAge>=0 and EmpAge<=120)
--增加外键约束，先增加列EmpDeptId，再设定
alter table Employees add EmpDeptId int not null
alter table Department add constraint PK_Department_DepId primary key(DepId)
alter table Employees add constraint FK_Employees_Department foreign key(EmpDeptId) references Department(DepId) on delete cascade
--增加外键约束时的级联设置
--[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
--[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
--外键删除
alter table Employees drop constraint FK_Employees_Department
--删除多个约束
alter table Employees drop constraint CK_Employees_EmpAge,UQ_Employees_EmpName,DF_Employees_EmpGender
--增加多个约束
alter table Employees add constraint UQ_Employees_EmpName unique(EmpName) , 
constraint DF_Employees_EmpGender default('男') for EmpGender, 
constraint CK_Employees_EmpAge check(EmpAge>=0 and EmpAge<=120)

数据检索

简单检索与聚合函数

--==========检索=============
select * from Employees
use TestSchool
select EmpName as a from Employees
--还可以检索不与任何表关联的数据
select 1+1
select '很好','哈哈','吼吼'
--获得系统时间
select getdate()
--============top================
--排序，取前五个,order by 升序，desc降序
select top 5 * from Employees order by EmpAge desc
--取百分比多少percent，如果计算出的条数有小数，直接进位
select top 5 percent * from Employees order by EmpAge desc
--============distinct================
--根据查询出的结果进行数据重复处理
select distinct * from Employees
select distinct EmpName,EmpAge from Employees
--聚合函数，要记的5个，sum min max avg count
--聚合，把多条数据汇总成为一条
select * from Employees
select max(EmpAge) from Employees
select min(EmpAge) from Employees
select sum(EmpAge) from Employees
select avg(EmpAge) from Employees
select count(*) from Employees
--汇总
select 
	最大年龄=(select max(EmpAge) from Employees),
	最小年龄=(select min(EmpAge) from Employees),
	平均年龄=(select avg(EmpAge) from Employees)
--计算平均值需要注意，对空值不做处理
select * from Department
--count对于空值也不做处理
select count(score) from Department

带条件查询

--带条件查询
select * from TblStudent
select * from TblScore
select tSId from TblScore where tMath<60
select * from TblStudent where tSAge>=20 and  tSAge<=30 and tSGender='男'
--相当于
select * from TblStudent where tSAge between 20 and 30 and tSGender='男'
--优先建议使用between and，做过优化处理，效率高
select * from TblStudent where tSClassId=1 or tSClassId=2 or tSClassId=3
--相当于in()语法替换or
select * from TblStudent where tSClassId in(1,2,3)

模糊查询

--============================模糊查询============================
select * from TblStudent where tSName like '赵%'
--通配符表示：任意多个任意字符
select * from TblStudent where tSName like '%敏%'
--通配符_表示任意的单个字符
select * from TblStudent where tSName like '赵__'
select * from TblStudent where tSName like '赵' and len(tSName)=3
--[]只匹配一个字符，并且这个字符必须是[]范围内的
--查询包含伟或敏的人姓名
select * from TblStudent where tSName like '%[伟敏]%'
--没有伟或敏
select * from TblStudent where tSName like '%[^伟敏]%'
--查询姓名包含百分号的，[]表示转义
select * from TblStudent where tSName like '%[%]%'
select * from TblStudent where tSName not like '赵%'

空值处理

--select * from TblScore where tEnglish=null
--select * from TblScore where tEnglish<>null
--true false unknow
--null在数据库表示unknow，不知道，因此判断一个值是否为null，也就不能用=或<>来判断
--null与null比较结果还是null
--null与任何数据运算还是null
select * from TblScore where tEnglish is null
select * from TblScore where tEnglish is not null
update TblScore set tMath = null where tMath<30
select * from TblStudent,TblScore where TblScore.tMath is null and TblStudent.tSId=TblScore.tSId
select 0/0		--遇到以零作除数错误
select 0/null	--NULL
--CAST函数进行数据类型转换，将一种数据类型的表达式转换为另一种数据类型的表达式
select tSId,tEnglish=isnull(cast(tEnglish as varchar(50)) ,'缺考') from TblScore

数据排序

--默认不写排序方式，则认为是asc
select * from TblScore order by tMath desc
--先按照英语成绩排序，再按照数学成绩排序
select * from TblScore order by tEnglish desc,tMath desc
select tMath 数学,tEnglish 英语, avscore=(tMath+tEnglish)/2 from TblScore
order by avscore desc
--order by后也可以跟表达式，一般放到所有语句的后面

数据分组

select * from(select * from TblStudent where tSAge>20) as tb1
--每组记录条数
select tSGender as 性别, count(*) as 人数 from TblStudent group by tSGender
--group by分组，分组以后，分出来几个组，那么count(*)就统计几次
select tSClassId,count(*) as 班级人数 from TblStudent group by tSClassId
select tSClassId,count(*) as 男同学人数 from TblStudent where tSGender='男' group by tSClassId
--当在select查询语句中出现聚合函数时，不能再在select查询语句中出现其它列
--除非该列也在group by子句中出现或者也在聚合函数中出现

Having语句

--having是对组的筛选，对分组后的数据进行筛选
--having后能跟什么列，主要看分组后的结果集中包含什么列
select tSClassId,count(*) as 男同学人数 from TblStudent where tSGender='男'
group by tSClassId having count(*)>2
--与where差不多，也可以使用in语句
select tSClassId,count(*) as 男同学人数 from TblStudent where tSGender=1
group by tSClassId having tSClassId>2

执行顺序

执行顺序
select 
	distinct/top
from
where
group by 
having
order by

类型转换函数

select 100+'100'
select cast(100 as varchar(10))+'hello'
select convert(varchar(10),100)+'hello'
select getdate()
select convert(datetime,getdate())
--2022-07-22 15:28:45.383
select convert(varchar(50),getdate())
--07 22 2022  3:29PM
--https://docs.microsoft.com/zh-cn/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#date-and-time-styles
select convert(varchar(50),getdate(),114)
select convert(varchar(20),getdate(),126)
--不同的世纪数位有不同的日期格式，如往网址
create table t2
(
	id int identity(1,1) primary key,
	uname varchar(50),
	myid varchar(50)
)
select * from t2 order by cast(myid as int) desc
select * from t2 order by convert(int,myid) desc

联合结果集

--union联合，将多个结果集并到一起
--1、多个结果集中的列数据类型必须一一对应
--2、列的个数必须一样
--union去除重复数据 union all不会去除重复数据
select max(tEnglish),min(tEnglish),avg(tEnglish) from TblScore
select max(tEnglish) from TblScore
union all
select min(tEnglish) from TblScore
union all
select avg(tEnglish) from TblScore

拷贝表

--拷贝表结构，但是没有数据
select top 0 * into newStudents from TblStudent
select * into newStudents1 from TblStudent where 1<>1
select * from newStudents
select * from newStudents1
--=====跨数据库访问：数据库..表名，前提要有权限
select * into School..TblTeacher from TestSchool..TblTeacher
当表目标表存在时：insert into 目的数据库..表 select * from 源数据库..表  
当目标表不存在时：select * into 目的数据库..表 from 源数据库..表

字符串函数

--字符串函数
select len('哈哈hello')			--字符个数
select datalength('哈哈hello')	--字节个数
select lower('AaBb')
select upper('AaBb')
select ltrim('           aaa        ')
select rtrim('           aaa        ')
select left('welcome to China',10)
select right('welcome to China',10)
--索引从1开始
select substring('welcome to China',1,5)
select replace('abababcdhacdshc','a','★')