一点数据库笔记

Markdown 是个好东西,用了一段时间,这次把自己一点数据库课的笔记放上来,练习下 Markdown 。

函数介绍

数据语句

  • DECLARE 声明若干局部变量
  • SET 一个变量赋值
  • SELECT 多个变量赋值
  • PRINT 返回用户自定义信息

循环控制语句

  • BEGIN END 语句块

  • GOTO 跳转到标签

    1
    2
    3
    4
    5
    6
    BEGIN
    GOTO skip
    select * from student
    skip:
    PRINT 'hello'
    END
  • IF ELSE 条件判断

  • CASE 多分支选择

    1
    2
    3
    4
    5
    6
    7
    8
    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 _循环_

    1
    2
    3
    4
    while
    begin
    --code here
    end
  • WAITFOR _暂停_

    1
    2
    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 检测日期有效性

语句提升

约束与规则

规则

  1. 规则只允许在当前数据库创建
  2. 规则不能绑定到数据类型 char、 int、 image、 text 中
  • 创建规则
1
2
3
create rule 规则名
as 规则
/* 规则可以是where语句任何有效的表达式 */
  • 绑定规则
1
2
use 数据库名
exec sp_bindrule '规则名','数据库表字段'
  • 解绑规则
1
2
use 数据库名
exec sp_unbindrule '数据库表字段'
  • 删除规则
1
drop rule '规则名'

约束

  • 添加check约束
1
2
alter table 表名
add [constraint 约束名] check(约束)
  • 删除check约束
1
2
alter table 表名
drop constraint 约束名

区别

  1. 约束和表的定义联系,删除表的同时约束也删除。规则是单独存储的数据库对象,独立于表外,删除表时并不能删除规则。
  2. 一个字段可有多个约束,但只能有一个规则。

SELECT高级查询

IN, NULL

  • IN 查询符合列表中任何一个值的记录
1
2
select * from table1 where score in (70,80,90);
select * from table2 where score in (select score from table1);
  • NULL | NOT NULL 字段是否为空
1
select * from table1 where items in null;

SELECT

用于将查询结果存储到另一个表

1
2
3
select top 5 *
into table3
from table2;

GROUP BY

用于数据汇总

1
2
3
select name,avg(age) as avg_age
from student
group by name;

嵌套查询

  • 子查询作为新增列 作为外层select语句的列值
1
2
3
4
5
select avg_score = (
select avg(score)
from score
)from score
order by score.id;
  • 使用IN关键字 主要用于where子句后面的子查询。
1
2
3
4
5
6
7
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;
  • 比较运算符
1
2
3
4
5
6
7
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
1
2
3
4
5
6
7
8
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
1
2
3
4
5
6
7
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

拼接字段相同的表

1
2
3
select * from student as a
union
select * from student as b

数据操纵进阶

insert

插入多行

1
2
3
insert into table2
select name,sex,phone,email
from table2

select … into

1
select * into table1 from table2

update

基于级联

1
2
3
4
update table1 set table1.c = table2.c
from table1 inner join table2
on table1.a = table2.a
where table1.c is null

带有output

1
2
3
4
5
6
7
8
-- 查询新行的属性
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'

视图、索引、触发器

视图

  • 创建视图
1
2
3
create view 视图名
as
select语句
  • 视图结果集排序
1
2
3
4
create view stu1
as
select top 3 * from table2
order by id
  • 多张表进行视图查询
1
2
3
4
5
6
7
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
  • 修改视图
1
2
3
alter view 视图名
as
select语句
  • 删除视图
1
drop view 视图名

增删改

  1. 视图可以对基本表的数据进行查询,还可以向基本表增删改
  2. select 子句不可用聚合函数
  3. 不能包含算式表达式结果的列
  4. 视图引用多表,无法使用delete

索引

优点

  1. 通过创建唯一索引,可以保证数据记录的唯一性。
  2. 可以大大加快数据检索速度。
  3. 可以加速表与表之间的连接,这一点在实现数据的参照完整性方面有特别的意义。
  4. 在使用order by和group by子句中进行检索数据时,可以显著减少查询中分组和排序的时间。
  5. 使用索引可以在检索数据的过程中使用优化隐藏,提高系统性能。

缺点

  1. 创建索引要花费时间和占用存储空间。
  2. 建立索引加快了数据检索速度,但是减慢了数据修改速度。

不应创建索引列的情况

  1. 很少或从来不在查询中引用的列,因为系统很少或从来不根据这个列的值去查找数据行。
  2. 只有两个或很少几个值的列,例如性别。
  3. 以bit、text、image数据类型定义的列。
  4. 数据行输很少的表一般也没有必要创建索引。

触发器

DML触发器

  • after触发器 记录改变后才激活触发器

after触发器包括 insert、delete、update触发器

1
2
3
4
5
6
7
after insert
as
begin
SET NOCOUNT ON;
/* process */
end
go
  • instead of 触发器 直接执行触发器,不执行sql语句

instead of 触发器包括insert、delete、update触发器

1
2
3
4
5
6
7
8
9
10
11
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