一 基本知识点
-- 一、SQL关键字
数据查询:select
数据定义:create创建,drop删除,alter更改
数据操纵:insert插入,update更新,delete删除
数据控制:grant授权,revoke收权,deny拒权
事物管理:begin开始,commit提交,rollback撤销
关键字:GO 结束语句标志 ,use使用数据库标志
-- 二、 变量语句
局部变量@ 全局变量@@
声明变量: declare@名,类型[ ]
赋值变量: set@名=值或表达式
输出变量: pring@名
-- 三、建表数据类型:
数字型:整形: 8字节 bigint
4字节 int
2字节 smallint
1字节 tinyint
非整形:精确性数字decimal( 6.2 ):小数点后有两位,前有四位数字
numeric( 6.2 );小数点后有两位,前有四位数字
近似数字float( )
Real
字符型:ascll码:普通编码 char( 1-8000 )
varchar( 1-8000 )
Unicode:统一编码 nchar( 1-4000 )
nvarchar( 1-4000 )
货币型:8字节 money
4字节 smallmoney
日期型:8字节 datetime
4字节 smalldatetime
3字节 date
5字节 time
二进制型:binary( 1-8000 )
varbinary( 1-8000 )
Image
-- 四、建表约束关键字:
unique 指明本列数据不重
Not null 指明本列值非空
Primary key指明本列为主键
Foreign key外码(列名) references 主表名(列名) 指明本列为外键
Default 向表中插入数据时,没有为default供值,此列使用默认值
Check 通过限制输入到列中的值来强制域的完整性
建表过程:先建立被参照表,被参照表就是主键表
-- 五、SQL函数功能
-- 1、聚合函数
COUNT( * ):统计表中元组个数
COUNT(<列名>):统计某一列中非空值个数
SUM(<列名>):计算列值总和(必须是数值型列)
AVG(<列名>):计算列值平均值(必须是数值型列)
MAX(<列名>):求列值最大值(可以是非数值列)
MIN(<列名>):求列值最小值 (可以是非数值列)
-- 2、字符串函数
Charindex(‘ghe’,’abieghed’):前字符串在后字符串中的位置
Left(‘ 张海洋’,1):返回张海洋的姓
Right(‘中华人民共和国’,3):返回字符串后三位字符
Len(‘中华人民共和国’):返回有多少个字符
Substking(‘北京信息科技大学’,5,2):从第5个字符开始,长度为2的字符串
Ltrim:删除前导空格
Rtrim:删除尾随空格
Replace( ‘我的口令为:123456’ , ’123456’ , ’******’ ):星号替换123456
Lower:转小写字母
Upper:转大写字母
二 流程控制语句
-- 流程控制语句
思路原则:复杂问题先建中间表-视图,简化思考过程
1、2、3原则:三种语句结构,现实问题都用这三种结果描述,然后转换成语句
顺序:先做1,再做2,最后做3
判断:如果1,那么2,否则3
循环:按照1,循环2,最后3
编程层次原则:缩进后的语句都是从属于最开头语句的。
-- 1、两个或两个以上语句一起执行时用
Beg in
语句1
语句2
end
-- 2、如果布表是true,执行语句1,否则执行语句2
If 布尔表达式
语句1
Else
语句2
-- 3、每次循环后重新测试布表值,如果是true,执行循环,否则退出循环执行以后的语句
while 布尔表达式
循环语句块
-- 4、搜索查询:
执行过程:
1、从上向下依次测试每个when子句的布尔表达式。
2、如果测试某个when子句的布尔表达式为true,那么返回其后then子句的结果表达式;
3、如果全部when子句的布尔表达式都是false,那么检查是否有else子句,
4、如果有,返回else子句的结果表达式值;否则返回null;
5、一个搜索CASE语句只能返回一个结果表达式的值。
6、如果有多个when子句的布尔表达式为真,那么返回第一个布尔表达式为真
的when子句后的结果表达式;
Case
When 布尔表达式1 then结果表达式1
When 布尔表达式2 then结果表达式2
When 布尔表达式3 then结果表达式3
Else 结果表达式n +1
end
-- 5、简单查询:
执行过程:
1、用测试表达式的值与每个when子句中的测试值进行比较,
2、找到第一个与测试表达式的值相同的测试值时,返回其后的then子句的结果表达式。
3、如果没有任何一个when子句的测试值与测试表达式的值相同,则返回else子句后的结果表达式;
4、如果没有else子句,则返回null;
5、一次只能返回一个when子句其后的结果表达式;
6、若同时有多个测试值与测试表达式的值相同,则只返回第一个与测试表达式的值相同的when子句对应的结果表达式;
-- CASE 测试表达式
WHEN 测试值1 THEN 结果表达式1
WHEN测试值2 THEN 结果表达式2
ELSE 结果表达式n +1
END
三 建表语句
-- 建表语句
新建表 : create table 表名(
列名 数据类型(长度) 约束关键字 主键
列名 数据类型(长度) 约束关键字 外键
)
建表时添加主键:constraint约束名 --如果不写,系统默认起约束名
列级添加:create table 表 (
列名 类型 constraint约束名primary key
)
表级添加:create table 表(
列名 类型
列名 类型
Constraint约束名 primary key(列1,列2)
)
修改表添加主键:alter table 表名
Addconstraint约束名 primary key(列1,列2)
修改表删除主键:alter table 表名
Drop constraint约束名
建表时添加外键:constraint约束名 foreign key referenes表.列
--后面的表。列是被参照(设置主键的表),前面的是设置外键的表
列级添加:create table表(
列名 类型名 constraint约束名 foreign key referenes 表.列
)--被参照(设置主键的表)
表级添加:create table表(
列表 类型
列表 类型)
constraint约束名 foreign key referenes 表.列)
)
修改表外键:alter table
添加:add constraint约束名 foreign key referenes 表.列
删除:drop constraint约束名
-- 主外键注意事项:
1:外键的列名和主键的列名 数据类型必须一致
2:给表设置外键,要先在列上设置主键,再设置外键,建立联系。
3:设置的时候其他表都关闭
4:表中左边是参考表,右边是被参考表
-- 建表误区:
1每列后加,号
2单词输入错
3主键外键后的列用( )
添加列 : Alter table表名 add ‘列名’ 数据类型
修改属性:alter table 表名
Alter column 列名 数据类型
删除列:alter table 列名
Drop column 列名
删除表:drop table
单行数据插入:insert into 表名
Values (‘1’ , ’2’ , ’3’) --值类型和顺序和原列一致
多行插入:insert into 表名
Select 语句 --语句查询的结果插入表中,过程:先建表,从原数据库中查询出数据,再插入新表中
完整性要求:主键不为空,外键值在主键中存在,列值符合语义。
有条件删除行:delete from 表名 无条件删除:没有where子句
Where --本表,外表:有子查询和连接查询
-- 更新表数据:up date 表
Set 列名 列值或表达式 列+1
Where 更新条件
四 查询语句
-- 1、基本结构
select 查询列
from 查询表
into<表名> 查询结果保存新表
where 查询条件
Group by 依据哪列分组 不常用
having 分组条件 不常用
Order by 排序条件 不常用
-- 2、select子句汇总 处理各列的值
Select 2018-年龄列 :可以直接对应全列结果
Select 2018-年龄列 as 新列名 :结果显示新列名
Select distinct 列 :所在列去重
Select 列,列 :中间用,号隔开
select 函数(列名)
-- 3、where子句汇总 查询满足条件的行
Where 列名<60 : 比较 对数字进行比较大小,规定范围 大于,大于等于,小鱼,小于等于 等于 不等于
where 列名 between 20 and 30 :确定范围 对数字类型
Where 列名 in (‘1’,’2’,’3’) : 确定集合
Where 列名 like ‘张%’:字符匹配 通配符: 按字符查找
%(百分号):匹配0个或多个字符。
_(下划线):匹配一个字符。
[ ]:匹配括号中的字符
[^ ]:不匹配括号中的字符
Where 列名 like ‘%[50%]%’
Where 列名 like ‘%50*%%’
Escape ’*’ :转意字符,把%通配符的含义转成普通的百分比
Where 列名 is not null 空值查询 查询和去掉空值
Where 列名=‘表’and 列名<20 多重条件连接字符 and or 多条件链接查询
-- 4、不常用子句汇总
Group by 例名 : 按照一列进行分组 --遇到每一个的字眼的时候就是分组
Having count(*)>3 对分组的结果按照条件筛选
Order by 列名 ,列名 desc :前按升序,后按降序
-- 5、子查询
WHERE 列名 [NOT] IN (子查询)
Where 表达式 = 比较运算符(子查询)
WHERE [NOT] EXISTS存在 (子查询)
1 带in 子句查询 :子查询的列名都是和父表有关的
Select 列名
From 表名
Where 列名 in
(Select 列名
From 表名
Where 列名 )
2带比较运算符的子查询
Select 列名
From 表名
Where 列名 =
(Select 列名
From 表名
Where 列名 )
3带exists():关联外部表时用此函数引导,子句不反回结果数据,产生真值和假值
Select 列名
From 表名
Where exitsts
(Select 列名
From 表名
Where 列名 )
-- 6、多表连接查询
1内连接
From 表名 inner join 表名
On 表名。列名 = 表名。列名 列名是两张表中相同列,连接用 =
2左外连接
FROM 表1 left outer join 表2
ON 连接条件
返回表1中的全部记录,和表2中满足连接条件的记录
3右外连接
FROM 表1 right outer join 表2
ON 连接条件
返回表2中的全部记录,和表1中满足连接条件的记录
-- 7、合并多个结果集 :all包含所有行,包括重复行
查询语句1
Union[all]
查询语句2
Union[all]
-- 8、TOP限制结果集:Top n percent with ties
-- 查询语句总结:
1select 后的关键字、函数都是直接处理对应列内容的。
2where后的表达式都是处理行数的
3group by 也是对应列的,按照那一列分组
4having 对应的是where 的条件表达式 --having对分组后的数据加条件,where是对整个的数据加条件
5select 列名,COUNT(*)
from 表名
group by 列名 --固定格式,按照列名分组,其他列进行分组后的数量
6子查询中:父子句 where 列 in 都和子子句 select 列 对应的
From 是两个不一样的表
7连接查询:on 后的表。列 必须是两个表对应一样的列
-- 8主键外键思路汇总:
1每一组数据系统都是几张表,每张表都有主键和外键的列,
3外键表中的外键列都对应其他主键表有相同列。
4、group by 分组的列,基本都选择主键的列,因为主键有唯一表示
5父查询条件在in前的列,是主键,和子句中的查询后的列相同,唯一表示好对比
6连表接on后的列基本都是主键列
五 建立视图
-- 1、例题
Create view age_view
as
Select *
From students
Where sage between 18 and 22
With check option --可以不加,意思是给存储过程加密
-- 1说明
查询语句中通常不包含order by子句,除非使用了top子句;不能包含into子句,不能包含临时表和临时变量;
with check option选项是指利用所创建的视图对基本表进行更新、插入、删除的时候,操作的目标记录必须满足定义视图时where子句给出的条件,这样仍可以通过视图看到修改后的数据;如果在子查询中使用了top,则不能指定with check option;
-- 2使用视图的原因
A. 重用SQL语句。
B. 简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道其基本查询。
C. 使用表的一部分而不是整个表。
D. 保护数据。可以授予用户访问表的特定部分的权限,而不是整个表的访问权限。
E. 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
-- 3语句调用视图:和调用表是一样的,只是是一个临时表,正常表中的数据映射
SELECT * FROM v_Test_View1
六 建立存储过程
-- 1 、例题
create procedure student_grade1 --默认可以简写 proc
AS
SELECT Sname, Cname,Grade
FROM Student s INNER JOIN sc
ON s.sno = sc.sno
INNER JOIN course c
ON c.cno = sc.cno
执行:EXEC student_grade1 --exec也是简写的
-- 2 、说明:输入参数说明
create proc 存储过程名
[ { @参数名 数据类型 }][=default][OUTPUT][,…n]
[with encryption]
AS
default是为变量指定默认值,必须是常量和null;
OUTPUT指出变量是输出变量,返回给调用此存储过程的语句;
-- 3、视图和过程总结:一个是临时图,为复杂查询做参考,一个是打包查询功能语句,方便功能调用
输入参数的过程:通过调整查询参数,显示查询语句中对应的信息。
-- 4、输出参数种类:
一个输出参数:create procedure 名
@dept char(20)
as 查询语句
where sdept = @dept
执行:Execute students_grade2 ‘计算机系’
两个输出参数:Create procedure student_grade3
@student_id char(10), @course_name char(20)
AS
WHERE sname = @student_i AND cname = @course_name
-- 执行:
1. 按参数位置传递值(按照存储过程中参数定义的顺序赋值)
EXEC student_grade2 '刘晨', '数据库'
2. 按参数名传递值(与位置无关)
EXEC Student_grade2 @student_name = '刘晨',
@course_name='数据库'
-- 多个参数,参数带默认值: CREATE PROCEDURE student_grade4
@student_id char(10), @course_name char(20) =‘vb’
AS
WHERE sname = @student_id AND cname = @course_name
执行:execute student_grade4 '9521102'
Execute student_grade4 '9521102','数据库基础'
Execute student_grade4 @course_name = '数据库基础' , @students_id = '9512101'
execute student_grade4 @students_id = '9512101' , @course_name = '数据库基础'
-- 4多个参数,都有默认值:CREATE PROCEDURE student_grade5
@dept char(20) = ‘计算机系’, @sex char(2) = ‘男’, @age int = 20
AS
where sdept = @dept and ssex = @sex and sage > @age
-- 执行:
--不指定参数值 :execute student_grade5
--指定其中一个参数值(只能用参数名进行传递) :execute student_grade5 @age=15
--提供全部参数值:execute student_grade5 @age=15,@dept='信息系',@sex='女'
-- 5特例:带有输出参数的存储过程。计算两个数的积并把计算结果返回给调用者
-- 6、带有多个输出参数的存储过程。计算两个数的积和和并把计算结果返回给调用者
七 建立游标
-- 牢记五个步骤
-- 声明游标:
Declare 游标名 insensitive或scroll cursor
For select语句
For read only或up date of 列名,列名
-- 说明:
1游标名:为声明游标所取得的名字,游标名必须遵循命名规范。
2 insensitive:使用insensitive定义的游标,游标的结果集存入tempdb数据库的临时表中。任何通过这个游标进行的操作,都在这个临时表中进行。对于基本表的修改,并不反映到已经打开的INSENSITIVE游标结果集中。若不使用INSENSITIVE,那么对基本表所作的修改,都将在游标结果集中体现。
3 scroll:(滚来滚去)可以用所有方法提取游标。
4 For read_only:游标为只读,禁止通过游标进行数据更新。
5 For update [OF<列名>[,…n]],游标是可修改的,定义在这个游标中可以更新的列。
如果定义了[OF<列名>[,…n]],则只有其中指定的列可以利用此游标进行修改;
如果没有定义[OF<列名>[,…n]],则游标中的所有列都可以进行修改。
-- 打开游标:OPEN 游标名
-- 说明:
1 如果声明了游标,在使用游标前必须打开它。
2 执行了该句,则执行了游标定义语句中的select查询,并使游标指针指向查询结果集的第一条记录。
-- 提取游标:
Fetch next--下一条记录 --next如果是第一条记录不下移,先读第一条
prior --前移|first--获取第一条
last --获取最后一条
absolute--从头往后数 { n | @变量名 }
relative --当前记录基础上移或下移{ n | @变量名}
From 游标名
实验: 有单独的例题和语句说明
--声明游标
Declare cur4 scroll cursor for select * from bankt
--打开游标
open cur4 --查询记录--打开游标
--提取记录
fetch next from cur4
declare @a int ---不知道啥意思
set @a = 6 -- @a等于6所以从第6行提取
-- 关闭游标:
CLOSE 游标名
说明:在使用CLOSE语句关闭某游标后,系统并没有完全释放游标的资源,并且也没有改变游标的定义。当再次使用OPEN语句时可以重新打开此游标。
-- 释放游标:释放分配给游标的所有资源。
deallocate 游标名
说明:释放游标就释放了与该游标有关的一切资源,包括游标的声明,以后就不能再使用OPEN语句打开此游标了。若想再次使用此游标,需要重新执行声明游标的语句。
@@fetch_status
可以使用@@fetch_status全局变量判断数据提取的状态。
@@fetch_status返回 FETCH 语句执行后的游标最终状态。
-- 写复杂程序总结:
先写语句,再对应游标
先确定查询表,再确定目标列,分析分解功能,对应相应功能的语句,处理关键字和函数
熟悉每种语句功能的框架,然后根据目标,往一起拼装
每个模块自己加注释
-- 游标的数据更新和删除
-- 游标总结说明:
1为什么用游标:对查询结果中的个别记录进行查询等操作,
2 open以后得到查询结果,游标放在第一条记录上,
3 fetch定义游标的位置。 fetch语句在wilh循环外侧一个,内侧一个。
4 SCROLL滚来滚去游标,哪个都能用,
5 全局变量@@的读取。
6 任何游标都指向一个查询
7 update 和delete 都是删除一组数据,或者一个单元。用游标可以对有选择性的记录进行操作。
-- 更新和删除语句
--只要在游标的声明语句中没有使用,那么就可以使用这个游标进行数据的更新和删除。
For read only
--作用:修改当前游标当前记录的属性值。
update 表名 set 字段名=表达式[,…n]
where current of 游标名
--作用:删除游标当前记录
delete from 表名
where current of 游标名