一道练习题引申出来的知识点（五） 常用的sql语句 以及相关概念

文接上回。
上回说到随机生成姓氏与名字以后用字符串处理函数将他们连接起来
现在我们名字有了，性别有了，年龄也有了 那么好 就该放去数据库了
放去数据库我做了一下几个步骤
1.在 MS SQl Server 2008 中创建一个数据库 sql语句如下
if db_id('stuDataBase') is null-- 检查 stuDataBase 这个数据库书否存在
create database stuDataBase -- 如果不存在就新建一个名为 stuDataBase 的数据库

2.在数据库创建一个存放学生的表
use stuDataBase -- 规定下面的操作
if OBJECT_ID('T_student','u') is not null -- 检查T_student是否存在
drop table T_student -- 如果存在 就先查处掉这个表
go -- go 关键字表示以上语句是一个语句块
create table T_student -- 创建新表
(
 FId int identity(1,1) primary key,-- 设置FId是主键 每次从1自动增长1
 FName nvarchar(10), -- FName 的类型是 nvarchar n表示带有汉字的字符串 varchar表示可变的字符串
 FGender nvarchar(10),
 FAge int
)

3.填写sql语句 插入学生数据（插入一条）
insert into T_student(FName,FGender,FAge)
values('宋方舟','男',21)-- 因为FId是自动增长的字段 所以无需手动添加FId

4.SQL注入漏洞攻击
 *.什么是SQL注入漏洞攻击
 在登陆时 密码框输入 1' or '1' = '1 造成密码永远为真的情况
 在编码时没有使用参数化查询 所以会造成一个bug
 为了避免这种情况 我们使用  “参数化查询”  来避免sql漏洞攻击
 *.所以上面的SQL语句就不能写成那个样子了 应该使用参数化查询
 要写成下面的样子：
 insert into T_student(FName,FGender,FAge)
 values(@name,@gender,@age)-- 使用占位符来限制参数的个数 避免漏洞攻击
 
//

列出一些常用的SQL语句以及一些相关的概念 上笔记
什么是sql语句
（Structured Query Language)结构化查询语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

Sql sever 2008 的结构
实例
 构架（设定权限）
  数据库
   表

创建/删除数据库 create database / drop database
create database firsDateBase;
go
--添加数据库firsDateBase

drop database MyfirsDateBase;
go
--删除数据库MyfirsDateBase

指定在那个数据库中操作 use
use MyDataBase;
go
判断指定的字符串是否存在 DB_ID
if DB_ID ('Itcast20120601') is null
create database new_database
--如果Itcast20120601数据库不存在 那么就增加新的数据库

判断指定的表是否存在 OBJECT_ID
if OBJECT_ID('T_Person','u') is not null
drop table T_Person
--判断T_Person表是否存在 如果存在 就删除这个表
注意：在使用时 如果要判断这个表是否存在 那么必须先删除以后才能创建这个表

建立一个空表Create table
create table T_Person
(
FId int identity(1,1) primary key,
FName varchar(10) not null,
FSex varchar(5) not null ,
)
--新建一个表T_Person 字段有 Fid，FName，FSex，Fid是主键
注释：
Create table：建立空表的关键字
T_Person：建立空表的名字
FId int：建立空表的字段以及类型
not null：非空约束（此字段不能为空）
primary key:定义主键 使用时 在建立表的最后填写
identity(1,1)：自动增长 从1开始 每次增加1
删除一个表Drop table
Drop table T_Person
--将T_Person表删除
注释：
Drop table：删除表的关键字
T_Person：要删除的表
增加字段Add
alter table T_Person Add FNumber varchar(20)
--在T_Person表中增加一个字段
注释：
alter table：要操作表的关键字
T_Person：要在哪个表中增加字段
Add FNumber varchar(20)：增加字段的关键字和要增加字段的名字和类型

删除字段Drop
alter table T_Student drop column FSalary
--删除T_Person表中的Fid字段 一定要加上 column 因为是删除一个列

修改字段 alter column
alter table T_Student alter column FMath float;
--删除T_ Student表中的FMath字段类型修改为float

数据的插入insert into
insert into T_Person(FId,FName,FSex) values(1001, N '宋方舟', N '男')
--在T_Person表中插入一条数据
insert into T_Person values(1002, N '臭京京', N '女')
--在T_Person表中插入一条数据
（省去了前面的字段名 但是这种方法不推荐）

注释：
insert into：插入数据的关键字
T_Person(FId,FName,FSex)：要操作的表名和字段的列表
values(1001, N '宋方舟', N '男')：要插入的数据（和字段的个数和类型要一致）

数据的删除delete
delete from T_Person where FName= N '宋方舟'
--表中FName字段中  如果值是’宋方舟’,那么就将整行数据删除
注释：
delete from：删除数据的关键字
T_Person：要从哪个表中删除
where FName= N '宋方舟'：要删除那一项数据（整行都删除）

清空表中的数据 truncate
truncate table T_Student
注意 本句没有where字句 不生成日志 删除后无法挽回数据

将某个字段中的所有数据全部更改 update
update T_Person Set FName=N'新名字'
注释：
Update：更新的关键字
FName=N'新名字'：将FName字段中的所有数据改变为’新名字’
将某个字段中的某个值改变 update
update T_Person Set FName=N'新名字' where FName=N'旧名字'
--表中FName字段中  如果值是’旧名字’,那么就更换为’新名字’
注释：
where FName=N'旧名字'：过滤条件

更改多个值 update
update T_Person set FEnglish -=1 ,FMath-=1;

字段的追加 Fname+='加'
update T_Datebase set Fname+='加' where Fage=20
--表中Fname字段中  名字后面添加一个‘加’字

查看全表
select * from T_Person
--查看T_Person全表
注释：
*：代表所有的字段
T_Person：在哪一张表中查看

表的备份(修改表时做的一个备份)
select * into T_New from T_Student
T_New：新表名
T_Student：旧表名

检索表中某个字段的所有内容select
select FId as 编号,FName as 姓名 from T_Person
--在T_Person表中查找Fid和FName字段
注释：
FId,FName：要检索的字段名列表（可以使一个 可以使多个）
as 编号:给字段起的别名（方便使用者查看）

子查询
将查询到的结果集作为数据源
单值结果集：只有一行一列的 比如聚合函数的查询结果
行结果集：只有一行的 比如查询某条数据时（一条完整的数据）
列结果集：只有一列的 比如查询某个字段的值时（同一种类数据）
表结果集：多行多列 比如select * from 表名
数据源：（数据的来源）就是from后面的东西

分页
方法一：
select top 5 * from T_Persons -- 控制页输出几条数据
where FId not in
( -- 控制第几页0是第一页
 select top (5*0) FId from T_Persons order by FId
)
order by Fid

方法二：ROW_NUMBER()排名函数
select * from
(
 select ROW_NUMBER() over(order by FId ) as num,
 * from T_Persons
) as t
where num
between (10) and (15)

临时表
insert into T_Persons(FName,FGender,FAge) output inserted.FId values('s','d',20)
--返回新插入的数据的id号  因为系统没操作一条语句 就将数据先放入临时表

空值检测is NULL
select * from T_Person
where FName is NULL
--在T_Person表中查找FName为空的数据
注释：
is NULL：空值检测

检索过滤的数据where 
select FID as 编号 ,FName as 姓名 from T_Person
where  FId=1002 or FId=1003
--表中T_Person表中  检索出Fid字段的值是1002和1003的那条数据 并显示出编号和姓名

注释：
where  FId=1002：过滤条件
or：或者
select FName from T_Employee where FSalary =(Select MAX(FSalary) from T_Employee)
--表中T_Person表中  检索出工资最高的姓名 涉及到子查询

select * from T_Persons where FAge in(60,45)
--查找年龄是或者的同学

select * from T_Persons where FAge between 20 and 50
--查找年龄是~50的同学

通配符过滤（模糊查询）like
通配符过滤会在全表中依次遍历 效率较慢 数据多时 不建议使用
select * from T_Person  where FName like '_月娇'
--表中T_Person表中  查找名字为三个字且名字中含有’月娇’的信息
注释：
T_Person：要在哪个表中查询
FName：要过滤的字段
Like：关键字
'_月娇'：过滤单个字的通配符（_ 符号是过滤单个字的）

select * from T_Persons where FName like '俞%'
--查找名字中第一个字是‘俞’的同学
select * from T_Persons where FName like '俞_'
--查找名字是两个字的且第一个字是‘俞’的同学

select * from T_Person
where FName like '%娇%' or FName like '%舟%'
--表中T_Person表中  查找名字中含有’娇’和’舟’的信息
注释：
'%娇%'、'%舟%'：过滤多个字的通配符

select * from T_Person where FName like '[宋吴]%'
--表中T_Person表中  查找名字以’娇’和’舟’开头的信息
该方法只适用于 MS SQL Sever
'[宋吴]%':集合匹配

Case语句
// 相当于switch case
select 
case 字段
 when 值 then 替换值
 when 值 then 替换值
 ...
 else 值
end
from
// 使用例子
select
case FGender
 when '女' then 'women'
 when '男' then 'man'
 else '未知'
end as 性别
from T_Perons

// 相当于if else

select
case
 when 表达式 then 值
 else 值
end
from T_Perons
// 使用例子
select
case
 when FAge>18 then '成年人'
 else '未成年人'
end
from T_Perons

while语句
-- 计算1~100的累加和
while @i<=100
 begin
 
  set @s=@s+@i
  set @i=@i+1
 
 end
  select @s

if else 语句
-- 查看是否有学号是1 的学生
 declare @id int
 set @id= 1
 
 declare @res int
 select @res= COUNT(*) from Student where sId=@id
 
 if @id=0
 begin
  select '没有这个人'
 end
 else
 begin
  select * from Student where sId=@id
 end

SQl中的数值函数
Max  计算字段最大值
Min  计算字段最小值
Avg  计算字段平均值
使用avg的时候 如果有一条数据为null 那么就不计算
Sum  计算字段合计值
Count  统计数据条数
COUNT(*)和COUNT(FName)的区别：
COUNT(*)计算的是表的总的条数，NULL值也计算
COUNT(字段名)计算字段内的数据条数，NULL值不计算
Abs    求绝对值

SQl中的字符串函数
LTrim     去掉字符串左边的空格
RTrim   去掉字符串右边的空格
LTRIM(RTRIM('     abc   ') 去掉字符串两边的空格
Len    字符串的长度
DataLen   字符串字节的长度
Left(‘abc’,2)  取字符串左边1个字符
Reght(‘abc’,2) 取字符串右边1个字符
     如果取得长度大于字符串的长度 那么就将字符串全部输出
ASCII('a')   将字符串转换成ascii码
SUBSTRING('abcdef',1,5) 字符串的截取
     从第一个字符串开始截取，截取5个字符串
     字符串从1开始截取，不是0

SQl中的日期函数
Getdate  获得系统当前时间
YEAR(GETDATE()) 获得当前的年份
MONTH(GETDATE()) 获得当前的月份
DAY(GETDATE()) 获得当前天数
DATEADD(在哪个部分增加,增加多少,从哪个日期开始增加) 
在当前时间加上一个时间间隔
在哪个部分增加：可以是Year(年)、month(月)、day(天)、hour(小时), Minute(分)、Second(秒)、Millisecond(毫秒)
DATEDIFF(比较哪个时间单位,开始时间,结束时间)
     判断两个日期的间隔
比较哪个时间单位：可以是Year(年)、month(月)、day(天)、hour(小时), Minute(分)、Second(秒)、Millisecond(毫秒)
DATEPART(返回日期的哪一部分，日期)
       返回日期的特定部分
返回日期的哪一部分：可以是Year(年)、month(月)、WEEKDAY (一周的第几天，周日是第一天),day(天)、hour(小时), Minute(分)、Second(秒)、Millisecond(毫秒)、QUARTER(季度)

数据的排序order by
select * from T_Employee
where FSalary>5000
order by FSalary Desc
--表中T_Person表中  对工资大于5000的进行排序
注释：
where FSalary>5000：工资大于5000的条件
order by FSalary Desc：对字段FSalery进行降序排列（Asc为升序 系统默认排序方式）
注意 order by 必须写在where之后

数据的分组 group by
select FAge from T_Persons group by FAge
--将年龄字断中的值分组 将字段相同的数据放在一起
如果使用分组 只能使用分组的字段 和聚合函数

having语句having
select FAge as 年龄
 from T_Persons
group by FAge --将年龄分组
 having(FAge>20)--分组后过滤年龄大于岁的人数
--筛选出年龄大于20岁的同学
? -> having 是group by的条件对分组后的数据进行筛选，与where类似，都是筛选，只不过having是用来筛选分组后的组的，where是查询前过滤
? -> 在where中不能使用聚合函数，必须使用having，having要位于group by之后，
? -> 注意having中不能使用未参与分组的列，having不能替代where。作用不一样，having是对组进行过滤

联合结果集union
select '学习c++的人数',COUNT(*) from Student where sClass='c++'
union all
select '学习java的人数',count(*) from Student where sClass='java';
--将两个查询的结果集合并在一张表中
union all ：不合并重复的数据 我们一般使用这个
union：自动合并重复值

表链接 join
select ts.tId,ts.tName,tc.cDesc from
 Student123 as ts -- Student123 是外键表
 inner join
 Class123 as tc -- Class123 是主键表
 on ts.tClass = tc.cId -- 将主键表的tClass 字段和外键表的cId 连接起来

数值类型的转换cast / convert
select '你的成绩是'+cast(95 as nvarchar(10));
select '你的成绩是'+CONVERT(nvarchar(10),95);
select '今天的日期是'+CONVERT(nvarchar(10),GETDATE())
--111是得到中国的日期格式 国家格式具体查看联机丛书
select CONVERT(nvarchar(10),GETDATE(),111)

创建架构 schema
构架：表的命名空间
create schema SFZ

数据的约束
1. 什么是数据约束
数据约束是为了保证数据的完整性和正确性而实现的一套机制
就是不允许输入非法的值

2. 约束的类型
a) 非空约束：不允许该字段的值为空，不允许输入null值
b) 主键约束 (PK)：一张表中只能有一个、且不为空的字段
c) 唯一约束（UNQ）：值允许这个数据出现一次、可以为空的字段
d) 默认约束（DF）：不填写字段内容时，具有默认值
e) 检查约束（CK）：范围以及格式的限制
f) 外键约束（FK）：主键表和外键表之间的关系

3. 手动添加约束
添加唯一约束
a. 在表的设计界面空白处右键“索引/键”
b. “添加”
c. 类型改为“唯一键”
d. 列中选择要设置唯一的字段
e. 更改表示名称 UNQ_表名_设置唯一约束的字段名

添加检查约束
a. 在表的设计界面空白处右键“CHECK约束”
b. “添加”
c. 在表达式中添加字段具备的要求 比如 ( age>0 and age<120 ) 
d. 更改名字 CK_表名_设置检查约束的字段名

4. 代码实现约束 使用场合：在创建表之后 发现需要有字段添加约束 使用下面的语句
添加语法
alter table 表名 add constraint 约束名 结束类型与条件
添加约束
  主键约束：primary Key (字段)
 alter table T_DateBase add constraint PK_T_DataBase_Fid primary key(Fid)

  唯一约束：unique (字段)
alter table T_DateBase add constraint UNQ_T_DateBase_Fname unique(Fname)

  Check约束：check (约束表达式)
alter table T_DateBase add constraint CK_T_DateBase_Fage check(Fage>0 and Fage<120)

默认约束：default (值) for 字段名
alter table T_DateBase add constraint DF_T_DateBase_Fgender default ('男')for Fgender

5. 批量添加约束
alter table 表名
add
constraint...,
constraint...,
constraint...,
constraint...,
constraint...;

6. 删除约束
alter table 表名 drop constraint 约束名
也可以批量删除约束，约束名之间用‘，’分隔即可

7. 创建表时添加约束
主键：primary Key
非空：not null
唯一：unique
检查：check(约束表达式)
默认：default(值)

添加索引
在使用while语句时 字符while查询索引的字段 那么系统会自动优化查询
1.手动添加索引
  A.在要添加的表‘设计’界面中 选择’索引\键’
B.点击‘添加’ 类型选为‘索引’
C.选择要添加索引的字段名
D.改成字段的表示名

2.代码添加索引
 查看系统代码
  索引->编写索引脚本为->create到->新查询编辑窗口

聚集索引：存储的物理信息 比如Id、 插进去的顺序
非聚集索引：存储的非物理信息
事物 begin transaction / rollback
什么是回滚：如果sql语句中其中有一天错误 那么前面所有的语句全部恢复修改之前的
什么是事物：一条错误 全部不执行 ； 一条错误就回滚

异常的处理
begin transaction -- 声明是一个事物
 begin try
  -- 执行的语句
 end try
 begin
  -- 如果上面的代码错误了
  -- 错误的执行方式
  rollback -- 回滚
 end catch

视图 view
什么是视图：就是将sql语句打包起来

--定义一个视图
create view show
as
  select * from Student where sAge=50
 
--执行视图
select COUNT(*) from show as tab

--执行视图相当于执行下面的sql语句
select COUNT(*) from  (select * from Student where sAge=50) as tab
  
触发器 trigger
什么是触发器：当执行某条sql语句时 就会执行另外一条sql语句 和winfom中的时间类似
--定义一个触发器当在表Student 中执行插入语句的时候就显示系统时间
create trigger showTime on  Student  --showTime 是触发器名Student 是表名
for insert --  insert 这里是一个执行的动作
as
 begin
  select GETDATE()
 end 

--定义一个触发器当在表Student 中执行删除语句的时候就显示‘删除’
create trigger deleted on  Student  --showTime 是触发器名Student 是表名
instead of  delete -- delete 这里是一个执行的动作
as
 begin
  select '删除'
 end                

 

 

 

下一篇文章我会介绍 ADO.NET常用命令 以及相关概念

(作者注释：由于我的笔记是.doc格式的 所有的标题都有超链接以及标题颜色 如果直接复制过来的话会有格式不支持的情况 为了统一 我在全部转换成了txt  所以会没有颜色 希望大家见谅)