Markdown 是个好东西，用了一段时间，这次把自己一点数据库课的笔记放上来，练习下 Markdown 。
函数介绍
数据语句
- DECLARE 声明若干局部变量
- SET 一个变量赋值
- SELECT 多个变量赋值
- PRINT 返回用户自定义信息
循环控制语句
-
BEGIN END 语句块
-
GOTO 跳转到标签
BEGIN GOTO skip select * from student skip: PRINT 'hello' END -
IF ELSE 条件判断
-
CASE 多分支选择
select score, (case when score > 90 then 'excellent' when score > 80 then 'good' when score > 60 then 'ok' else 'not well' end) as rate from student -
WHILE 循环
while begin --code here end -
WAITFOR 暂停
WAITFOR DELAY '11:00' WAITFOR TIME '01:00'
聚合函数
- AVG
- COUNT
- MAX
- MIN
- SUM
- DISTINCT
数学函数
- ABS 绝对值
- ceiling 大于或等于
- floor 小于或等于
- rand 返回0-1的随机数
- round 四舍五入到指定精度
字符串函数
- ascii 第一字符的ascii值
- char 返回ascii对应字符
- charindex 返回匹配位置
- itrim 去除左空格
- rtrim 去除右空格
- left 截断左侧指定长度字符
- right 截断右侧指定长度字符
- len 返回长度
- lower 小写
- upper 大写
- reverse 转置
- replace 指定字符替换
- space 指定空格数
- stuff 替换字符串的指定范围
- substring 返回指定范围字符串
日期和时间函数
- dateadd 给指定日期添加一段时间
- datediff 两日期相减
- datename 返回指定日期的部分
- day 指定日期的天数
- dayofyear 年内天数
- month 返回日期的月份
- year 返回日期的年份
- getdate 返回系统时间
- datepart 返回指定部分整数
- isDate 检测日期有效性
语句提升
约束与规则
规则
- 规则只允许在当前数据库创建
- 规则不能绑定到数据类型 char、 int、 image、 text 中
- 创建规则
create rule 规则名
as 规则
/* 规则可以是where语句任何有效的表达式 */
- 绑定规则
use 数据库名
exec sp_bindrule '规则名','数据库表字段'
- 解绑规则
use 数据库名
exec sp_unbindrule '数据库表字段'
- 删除规则
drop rule '规则名'
约束
- 添加check约束
alter table 表名
add [constraint 约束名] check(约束)
- 删除check约束
alter table 表名
drop constraint 约束名
区别
- 约束和表的定义联系，删除表的同时约束也删除。规则是单独存储的数据库对象，独立于表外，删除表时并不能删除规则。
- 一个字段可有多个约束，但只能有一个规则。
SELECT高级查询
IN, NULL
- IN 查询符合列表中任何一个值的记录
select * from table1 where score in (70,80,90);
select * from table2 where score in (select score from table1);
- NULL | NOT NULL 字段是否为空
select * from table1 where items in null;
SELECT
用于将查询结果存储到另一个表
select top 5 *
into table3
from table2;
GROUP BY
用于数据汇总
select name,avg(age) as avg_age
from student
group by name;
嵌套查询
- 子查询作为新增列 作为外层select语句的列值
select avg_score = (
select avg(score)
from score
)from score
order by score.id;
- 使用IN关键字 主要用于where子句后面的子查询。
select a.name
from student as a
where a.id in(
select b.id
from score as b
where b.score = 80
)order by a.id;
- 比较运算符
select a.name
from student as a
where a.id in(
select b.id
from score as b
where b.score <= 80
) order by a.id;
- BETWEEN
select a.name
from student as a
where a.id in(
select b.id
from score as b
where b.score
between 80 and 90
) order by a.id;
- EXISTS
select *
from table1
where exists(
select score
from table1
where score = 80)
/* IN和EXISTS都代表的是子查询存在某个值，但是IN用的时候，子查询只能是一个字段，但是EXISTS可以用多个字段。 */
多表连接
JOIN…ON
举例
- 表A
| snum | name | age | sex |
|---|---|---|---|
| 1 | AA | 12 | M |
| 2 | BB | 13 | F |
| 3 | CC | 24 | M |
| 4 | DD | 21 | F |
- 表B
| snum | score |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 5 | 40 |
inner join
| snum | name | age | sex | snum | score |
|---|---|---|---|---|---|
| 1 | AA | 12 | M | 1 | 10 |
| 2 | BB | 13 | F | 2 | 20 |
left join
| snum | name | age | sex | snum | score |
|---|---|---|---|---|---|
| 1 | AA | 12 | M | 1 | 10 |
| 2 | BB | 13 | F | 2 | 20 |
| 3 | CC | 24 | M | null | null |
| 4 | DD | 21 | F | null | null |
right join
| snum | score | snum | name | age | sex |
|---|---|---|---|---|---|
| 1 | 10 | 1 | AA | 12 | M |
| 2 | 20 | 2 | BB | 13 | F |
| 5 | 40 | null | null | null | null |
full join
| snum | name | age | sex | snum | score |
|---|---|---|---|---|---|
| 1 | AA | 12 | M | 1 | 10 |
| 2 | BB | 13 | F | 2 | 20 |
| 3 | CC | 24 | M | null | null |
| 4 | DD | 21 | F | null | null |
| null | null | null | null | 5 | 40 |
UNION
拼接字段相同的表
select * from student as a
union
select * from student as b
数据操纵进阶
insert
插入多行
insert into table2
select name,sex,phone,email
from table2
select … into
select * into table1 from table2
update
基于级联
update table1 set table1.c = table2.c
from table1 inner join table2
on table1.a = table2.a
where table1.c is null
带有output
-- 查询新行的属性
insert into table1(a) output inserted.a values('123')
-- 查询旧行的属性
delete into table1 output deleted.a where a = '123'
-- 返回修改后的值
update table1 set a = 'b' output inserted.a where a = '123'
-- 返回修改前的值
update table1 set a = 'b' output deleted.a wherer a = '123'
视图、索引、触发器
视图
- 创建视图
create view 视图名
as
select语句
- 视图结果集排序
create view stu1
as
select top 3 * from table2
order by id
- 多张表进行视图查询
create view stu2
as
select b.score,b.name,a.name,sex,age
from student as a
inner join
score as b
on a.id = b.id
- 修改视图
alter view 视图名
as
select语句
- 删除视图
drop view 视图名
增删改
- 视图可以对基本表的数据进行查询，还可以向基本表增删改
- select 子句不可用聚合函数
- 不能包含算式表达式结果的列
- 视图引用多表，无法使用delete
索引
优点
- 通过创建唯一索引，可以保证数据记录的唯一性。
- 可以大大加快数据检索速度。
- 可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。
- 在使用order by和group by子句中进行检索数据时，可以显著减少查询中分组和排序的时间。
- 使用索引可以在检索数据的过程中使用优化隐藏，提高系统性能。
缺点
- 创建索引要花费时间和占用存储空间。
- 建立索引加快了数据检索速度，但是减慢了数据修改速度。
不应创建索引列的情况
- 很少或从来不在查询中引用的列，因为系统很少或从来不根据这个列的值去查找数据行。
- 只有两个或很少几个值的列，例如性别。
- 以bit、text、image数据类型定义的列。
- 数据行输很少的表一般也没有必要创建索引。
触发器
DML触发器
- after触发器 记录改变后才激活触发器
after触发器包括 insert、delete、update触发器
after insert
as
begin
SET NOCOUNT ON;
/* process */
end
go
- instead of 触发器 直接执行触发器，不执行sql语句
instead of 触发器包括insert、delete、update触发器
instead of insert
as
begin
SET NOCOUNT ON;
/* process */
if @age > 25
print 'Too old!'
else
insert into score(name,age) values(@name,@age)
end
go