sql知识整理

一.基础

1.ddl语句

• 模式
  • 级联或拒绝：Create schema st cascade或restrict

• 表
  • 添加列：alert table x add x int
  • 修改类型：alert column x int
  • 删除：drop x cascade或drop constraint x
  • 添加约束：add Contraint c2 not null

• 索引和数据字典
  • Create index x on sc（sno）

• 完整性
  • constraint c1 -drop c2方便删除
  • 增删时参考表修改

• 主键
  • primary key（sno）
• 外键
  • foreignkey c1 reference sc（cno）
  • on delete cascade会删除外键约束数据，默认拒绝。
• 自定义
  • not null、unique
  • check（ grade between 1 and 1200）或（sname not like ＇%学＇）

• 断言和触发器
  • assertion a1 check
  • Create trigger t1 before update on table x
  • referencing new row as change1
  • for each row
  • when（。。）。。

• 存储过程
  • Create or replace procedure transfer（age int，name varchar）
  • mysql：CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN...END
    • declare xx float
    • set xx=8;
    • RETURN(sql)

2.数据类型

smallint、decimal（p，d）、numeric同左

date和time、datetime

text文本数据、nvarchar（sqlserver独有）、

3.权限和角色

• 创建所有权限：grant all privileges on sc to u1
  • 权限分发：with grant option
  • 预设权限：grant user u2 with resouce/dba/connection

• 创建角色：Create role r1
  • 授权：grant xx to r1
  • 分配：grant r1 to u1

• audit alert on sc

二.查询

1.单表查询

• 表达式函数别名
  • select lower（sdept） sde
  • select lower（sdept）as sde

2.分组查询+消重

• group：此时数据表只有一行，并能让聚集函数出现在select中
  • 单group：having和select只能一行。
    • 例子：select id ，count（*），top(ino) from x group by id order by id  having count（*）<3
  • 多group：having和select能多行。
    • 例子：select examiner,count(*),ino from SG_TrainingResult_hh group by ino,examiner，此时的count为满足两个列都相等的数量

• 消重复行值可选distinct默认是all
  • 消重方面：distinct>group by
  • 优先级：union all（不消重）>distinct>union>or（也是并集）
  • union:同表并集常使用，但必须列是一样的，条件不一样。
  • 强制索引：with （index（索引名））

2.连接查询（1对多）

• 等值连接和内连接
  • 可读性上，连接用on条件限制好，where再对具体表的范围做限制。
  • 例子：Student s2 inner join SC s1 on s1.Sid=s2.Sid

• 外连接
  • 以其中一表为准列出所有数据，被连接表符合条件的则有值，没有的则全为null
  • 例子：Student s2 left join SC s1 on s1.Sid=s2.Sid

• full连接
  • 双方条件互相不符合的列也都以null值列出

• from子查询
  • 有时需要合并同表的同列数据用：from子查询+left join
  • 例子：
    • select y.*,t.考了几次sg4 from (select examiner 测试者,COUNT(examiner) 考了几次sg3 from SG_TrainingResult_hh group by examiner,ino having ino like 'sg003') as y
      full join (select examiner 测试者,COUNT(examiner) 考了几次sg4 from SG_TrainingResult_hh group by examiner,ino having ino like 'sg004') as t on y.测试者=t.测试者

• 自连接
  • 自己表的多个行绑定在一起，可添加条件判断查询
  • 需求：需要查满足连续子行、存在数学关系的子行条件、查出头几个最高数据
  • 只有一个表的多对多关系表（冗余表）

3.范围查询

• mysql按序的头几个：order by id desc limit 0,5个数
• 选不重复的头几个：
• 选同部门的某值排名头几名：自表连接限制数量
  • select * from e1 where 3<select count(*) from e2 where e2.id=e1.id and e2.salary>e1.salary
• 选同部门的最大：max()
• 选第n个：先排名再从第几个开始选一个
  select distinct salary SecondHighestSalary from  Employee order by salary desc limit m,1
• 选分组后条件选择数量：select department,SUM(IF(t1.age <20,0,1))  as 部门年龄小的

• 规定上下限：where x bettewn 0 and 9
• sql server选取头几个：select top 3
• 存量查询：where x in（1，2）等于x=1 or x=2的缩写
• 选空：where x is null
• in（嵌套查到的集合）——in的意思就是=
• >8单个值或存在
• >any（嵌套集合）
• >all（嵌套集合）
  • 可用于找最值：count(*) >=all(select count(order_number) from orders group by customer_number)

4.嵌套查询

• 相关性:
  • in和any相关：子select与x in必须一致
  • exist相关子查询：查的值依赖于父的值和表的连接成功有关，但和子select无关。

• 子查询返回值:
  • between=exist>in>any
  • x in（select x）一定是单列分量集合
  • x = 一定是单值
  • x =any（）相当于in
  • where not exist（）反选必备。
  • exist（嵌套相关连接子查询）返回Boolean值
    • 空：什么都不做
    • 有数据：子查询的多表把连接成功的结果查出，父查询根据连接查询单表的数据。
    • 有数据：不连接，直接无条件查出数据
  • 多层exist：用在多表查询
    • 全称：双否定+双连接

5.mysql函数

• 生成不存在的值
  • ifNull（a，b）,a为null返回b，否则为a。
  • IF(expr,v1,v2)，也可以作为条件
  • COALESCE(s2.student, s1.student)返回第一个非空的数
  • 开窗函数：12345
    • lag(student,1,student) over() last选取上一个01234
    • lead(student,1,student) over() next选取下一个23456
  • ^1：偶+奇-
  • 排序：
    • rank() over (排序列)：生成消重排名11345
    • dense_rank() over （order by score desc）：生成不消重排名1123345
      • 用例1：当group by使用，计算出每个重复行数量的排名
    • ROW_NUMBER()，12345

• 日期：
  • DATEDIFF(d1,d2)日期差
  • PERIOD_DIFF(period1, period2)月份差
  • TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)指定月份或者日期差
  • DATE_ADD(d，INTERVAL expr type)加上指定类型后的日期值

• 数学
  • Min、Max（）：极值可以在日期使用
  • sum、svg()：经常用的
  • + -
  • case
    • when xx=  then 值(可取某列值或单独给)
    • else 值
    • end as xx 作为新列