黑马程序员_学习日记46_614数据库开发及ADO.Net（约束、数据检索）

一、约束

（一）通过设计器创建约束

索引/键

CHECK约束（检查约束）

默认约束

主外键关系

（二）通过SQL语句创建约束

--增加主键约束

alter table Employees

add constraint PK_Employees_EmpId primary key(EmpId)

--增加非空约束

alter table Employees

alter column EmpName varchar(50) not null

--增加唯一约束

alter table Employees add constraint

UQ_Employees_EmpName unique(EmpName)

--增加默认约束

alter table Employees add constraint

DF_Employees_EmpGender default(‘男’) for EmpGender

--检查约束

alter table Employees add constraint

CK_Employees_EmpAge check(empage>=0 and empage<=120)

--增加外键约束

alter table Employees add constraint

FK_Employees_Department foreign key(DeptId)

references Department(DepId) on delete cascade  --级联删除

--删除外键

alter table Employees drop constraint

FK_Employees_Department

--一条语句删除多个约束，约束名用“，”隔开

alter table Employees drop constraint

FK_Employees_Departmant,

CK_Employees_EmpAge,

UQ_Employees_EmpName

--用一条语句增加多个约束

alter table Employees add

constraint UQ_Employees_EmpName unique(EmpName),

constraint CK_Employees_EmpAge check(EmpAge>=0 and EmpAge<=120)

 

二、数据检索

（一）给列起别名

Fid as 学号,

fname as 姓名,

fage 年龄,

性别=fgender,

数学成绩=fmath,

英语成绩=fenglish

（二）关键字

1、select

--获取系统当前时间

select getdate()

2、top

--top数字后加percent表示百分比，如果有小数则直接进位。

select top 30 percent * from MyStudent order by fage desc

3、distinct去除重复数据，针对查询出的数据

select distinct * from Test2

select distinct * from Test2

（三）聚合函数

--聚合函数把多条汇总成一条，不能和fname匹配

--错误：fname没有出现在聚合函数或group by语句中

select fname,max(fMath) as 数学成绩最高分 from MyStudent

--计算平均分对空值不处理

--count统计列也不考虑空值

（四）带条件的查询

1、select筛选列，where筛选行

--用in替换多个or

--在1,2,3中选择任意一个班级都返回true

select * from TblStudent

where tsclassid in(1,2,3)

2、模糊查询

--查询所有姓赵的同学，通配符%表示任意多个任意字符

select * from Mystuent

where fname like ‘赵%’

--查询姓赵且姓名总字符个数为3，通配符_表示任意单个字符

select * from Mystudent

where fname like ‘赵__’

--通配符[]，表示[]中的任意字符只选一个匹配

--查询出姓名中包含‘磊’或‘伟’的

select * from Mystudent

where fname like ‘%[磊伟]%’

--表示x与y之间只要不是‘磊’或‘伟’的任意单个字符都可以

select * from MyStudent

where fname like ‘x[^磊伟]y’

--查询包含百分号的字符串，用[]括起来表示转义

select * from Mystudent

where fname like ‘%[%]%’

3、空值处理

--通过sql语句将表中的数据修改为null

update Mystudent set fMath=null

where fname like ‘赵%’

--null在数据库中表示unknown，判断一个值是否为null，也就不能用=或<>判断

--查询所有fmath为null的值。null与任何数据运算还是null

select * from Mystudent where fmath is null

--将成绩null换为“缺考”，这里的“缺考”只存在于查询结果中，表中数据不变

select

         fname,

         fage,

         数学成绩=isnull(cast(fmath as varchar(50)),’缺考’)

from Mystudent

4、排序（order by在最后执行）

select * from (select * from Mystudent where fage>20) as tbl

select * from (select top * from Mystudent where fage>20 order by fage desc) as tbl

--先按英语成绩排序，再按数学成绩排序

select * from Mystudent

order by FEnglish desc,FMath desc

--不写排序方式，默认是asc

5、数据分组

--统计男同学个数和女同学个数

--一般分组语句都和聚合函数配合使用

select

         fgender as 性别 --当fgender出现在group语句中时，可以和聚合函数一起使用

count(*) as 人数

from Mystudent

group by fgender

6、having语句：对组的筛选，哪些组显示，哪些组不显示

         where与having的区别：where在分组前筛选，having在分组后筛选

--having语句后能跟什么列，主要看分组后的结果集中包含什么列

select

         tsclassId as 班级Id,

         count(*) as 班级人数

from TblStudent

where tsgender=’男’

group by tsclassId

having 班级人数>2 --这里不能使用别名“班级人数”，因为当程序执行到having时，select语句还没有被执行，所以这里还没有创建别名。

7、Sql语句执行顺序

5>  Select 5-1>选择列,5-2>distinct,5-3>top

1>     From 表

2>     Where 条件

3>     Group by 列

4>     Having 筛选条件

6>  Order by 列

例：

（五）数据转换（cast与convert）

select cast(100 as varchar(10))+’hello’

select convert(varchar(10),100)+’hello’

（六）联合查询

注意：1、多个额结果集中的的数据类型必须一一对应

           2、列的个数必须一样

union 去除重复数据

union all 不去除重复数据

 

（七）复制表结构（不要数据）

select * into NewStuents from MyStudent where 1<>1

或

select top 0 * into NewStudents from MyStudent

（八）字符串函数

len(‘哈哈hello’)  --返回字符个数

datalength(‘哈哈hello’)  --返回字节数

left(‘hello welcome to China.’,10)  --从左数，截取10个字符

right(‘hello welcome to China.’,10)  --从右数，截取10个字符