SQL数据类型
以标准SQL为主, 各数据库实现会有所不同.
char(n) : 固定长度字符串
varchar(n) : 可变长度字符串(oracle中为varchar2(n))
int : 整数类型 , 全称为integer
smallint : 小整数类型
number(p,d) : 定点数, 精度由用户指定. 这个数字有p为数字(加上一个符号位), 其中d位小数再小数点右边(MySQL无此类型, 由 float/double代替)
real, double precision : 浮点数与双精度浮点数, 精度与机器相关.
float : 精度至少为n为的浮点数.
每种类型都可能包含一个被称为空置的特殊值.
当char类型和varchar类型比较时, 结果有可能为true , 也有可能为 false , 取决于具体数据库系统. 建议统一使用varchar类型.
SQL语法
创建一张表
create table student (xxx,xxx,xxx);
表复制 (创建一张表结构一样的表, 并且复制数据)
create table student_test as select * from student;
复制表结构, 但不复制数据
create table student_test as select * from student where 1=2;
插入数据
insert into student values(xxx,xxx);
insert into student select * from student2 ;
删除数据
delete from student;
删除表
drop table student;
新增属性或删除属性
alter table student add stu_name varchar(32);
alter table student drop stu_name ;
SQL查询的基本结构
完整SQL查询
select distinct xxx from A a where ... group by ... having ... order by ...
执行顺序
from > where > group by > select > order by
SQL关联查询
笛卡尔积
Student表
id | name |
1 | 张三 |
2 | 李四 |
3 | 王五 |
4 | 赵六 |
5 | 学生1 |
Course表
id | name | stuId |
1 | 课程1 | 1 |
1 | 课程1 | 2 |
2 | 课程2 | 2 |
2 | 课程2 | 3 |
3 | 课程3 | 3 |
3 | 课程3 | 4 |
4 | 课程4 | 6 |
笛卡尔积 : 行数 = Student行数 * Course行数
列数 = Student列数 + Course列数
SQL: select * from Student , Course 或者 select * from Student join Course
select * from Student inner join Course(不带on 条件)
id | name | id | name | stuId |
1 | 张三 | 1 | 课程1 | 1 |
2 | 李四 | 1 | 课程1 | 1 |
3 | 王五 | 1 | 课程1 | 1 |
4 | 赵六 | 1 | 课程1 | 1 |
5 | 学生1 | 1 | 课程1 | 1 |
1 | 张三 | 1 | 课程1 | 2 |
2 | 李四 | 1 | 课程1 | 2 |
3 | 王五 | 1 | 课程1 | 2 |
4 | 赵六 | 1 | 课程1 | 2 |
5 | 学生1 | 1 | 课程1 | 2 |
1 | 张三 | 2 | 课程2 | 2 |
2 | 李四 | 2 | 课程2 | 2 |
3 | 王五 | 2 | 课程2 | 2 |
4 | 赵六 | 2 | 课程2 | 2 |
5 | 学生1 | 2 | 课程2 | 2 |
1 | 张三 | 2 | 课程2 | 3 |
2 | 李四 | 2 | 课程2 | 3 |
3 | 王五 | 2 | 课程2 | 3 |
4 | 赵六 | 2 | 课程2 | 3 |
5 | 学生1 | 2 | 课程2 | 3 |
1 | 张三 | 3 | 课程3 | 3 |
2 | 李四 | 3 | 课程3 | 3 |
3 | 王五 | 3 | 课程3 | 3 |
4 | 赵六 | 3 | 课程3 | 3 |
5 | 学生1 | 3 | 课程3 | 3 |
1 | 张三 | 3 | 课程3 | 4 |
2 | 李四 | 3 | 课程3 | 4 |
3 | 王五 | 3 | 课程3 | 4 |
4 | 赵六 | 3 | 课程3 | 4 |
5 | 学生1 | 3 | 课程3 | 4 |
1 | 张三 | 4 | 课程4 | 6 |
2 | 李四 | 4 | 课程4 | 6 |
3 | 王五 | 4 | 课程4 | 6 |
4 | 赵六 | 4 | 课程4 | 6 |
5 | 学生1 | 4 | 课程4 | 6 |
自然连接
Student表
sid | sname |
1 | 张三 |
2 | 李四 |
3 | 王五 |
4 | 赵六 |
5 | 学生1 |
Course表
cid | cname | sid |
1 | 课程1 | 1 |
1 | 课程1 | 2 |
2 | 课程2 | 2 |
2 | 课程2 | 3 |
3 | 课程3 | 3 |
3 | 课程3 | 4 |
4 | 课程4 | 6 |
自然连接 : 取两表中相同的属性 sid 进行连接, 并只显示一个sid属性
SQL : select * from Student natural join course;
等同于 select s.sid,s.sname,c.cid,c.cname from Student s , Course c where s.sid = c.sid
sid | sname | cid | cname |
1 | 张三 | 1 | 课程1 |
2 | 李四 | 1 | 课程1 |
2 | 李四 | 2 | 课程2 |
3 | 王五 | 2 | 课程2 |
3 | 王五 | 3 | 课程3 |
4 | 赵六 | 3 | 课程3 |
附加的基本运算
更名运算
关键字as 取别名, 可以出现在select子句 , 和 from 子句中.
字符串运算
SQL使用一堆单引号来标示字符串. 如果单引号是字符串的组成部分, 那就用两个单引号字符来表示.
例如: "it's right" sql中表示为 "it''s right" , t 与s 之间使用两个单引号.
在SQL标准中, 字符串的相等运算时大小写敏感的, 但是 MySQL 和 SQL Server 不区分大小写.
字符串函数: 串联 ("||"), 提取子串, 计算字符串长度,
大小写转换 upper(s) / lower(s),
去空格 trim(s) 等等.
不同数据库系统所提供的字符串函数集是不同的, 以具体数据库为准.
字符串匹配: like % : 匹配任意字符 _ : 匹配任意一个字符 大小写敏感
'---%' : 匹配至少含三个字符的字符串.
escape : 为了能匹配特殊字符(% , _), 需要使用转义关键字
like 'ab\%cd%' escape '\' : 匹配所有以"ab%cd" 开头的字符串
like 'ab\\cd%' escape '\' : 匹配所有以"ab\cd" 开头的字符串
SQL还允许使用 not like 比较运算符搜寻不匹配项.
集合运算
并 : union 自动去重 若不去重 union all
交 : intersect 自动去重 , 不去重 intersect all
差 : except 自动去重, 不去重 except all
空值
空值给关系运算带来了特殊的问题, 包括算术运算, 比较运算, 集合运算.
算术运算 : 如果算术运算表达式的任一输入为空, 则该算术表达式结果为空.
比较运算 : 涉及空值的比较问题更多, 例如 : 考虑比较运算 "1 < null" . 因为我们不知道空值代表什么, 所以上述比较可能为真,也可能为假, 因而SQL将涉及空值的任何比较运算的结果视为unknown(既不是谓词 is null , 也不是 is not null), 这创建了除 true , false 之外的第三个逻辑值.
由于在where子句的谓词中可以对比较结果使用诸如and , or , not 的布尔运算, 所以这些布尔运算的定义也被扩展到可以处理unknown值.
and : true and unknown 的结果是unknown , false and unknown 结果是false, unknown and unknown 的结果是 unknown.
or : ture or unknown 结果是ture , false or unknown 结果是unknown , unknown or unknown 的结果是 unknown.
not : not unknown 结果是 unknown.
如果where 子句谓词对一个元组计算出false 或 unknown , 那么该元组不能被加入到结果集中.
在select distinct子句中, 如果两个值都是非空并且值相等, 或者都是空, 那么它们是相同的.
注意上述对待空值的方式与谓词中对待空值的方式是不同的.
在谓词中"null = null" 会返回 unknown, 而不是true.
如果元组在所有属性上的取值相等, 那么它们就被当作相同元组, 即使某些值为空.
上述方式还应用于集合的并,交和差运算.
聚集函数
聚集函数是以值得一个集合为输入, 返回单个值得函数.
平均值 : avg.
最小值 : min.
最大值 : max.
总合 : sum.
计数 : count.
使用说明 : 有些情况下使用聚集函数需要先去重, 可以使用distinct 关键字
select count(distinct id) from Studnet;
sql不允许在使用count(*) 时使用distinct.
count 函数效率 : count(*) ~ count(1) > count(ID) > count(字段)
分组聚集 : group by
注意: 没有出现在group by 子句中的属性如果出现在select 子句中的话, 它只能出现在聚集函数中.
having 子句 :
该条件不是针对单个元组, 而是针对group by 子句构成的分组.
having子句的谓词在形成分组后才起作用, 因此可以使用聚集函数.
任何出现在having子句中, 但没有被聚集的属性必须出现在group by 子句中, 否则会报错.
对空值和布尔值的聚集
聚集函数根据以下原则处理空值: 除了count(*) 外所有的聚集函数都忽略输入集合中的空值.
集合成员资格
in / not in
exists / not exists
集合的比较
some : 至少比某一个要大 > some
all : 比所有都大 > all
重复元组存在性测试(unique 并未被广泛实现)
unique : 作为参数的子查询中没有重复元组, 返回true ;
select t.course_id from course as T where unique (select R.course_id from section as R where T.course_id = R.course_id and R.year = 2009)
with子句 : 提供定义临时关系的方法 (并非所有数据库支持)
with max_budget (value) as (select max(budget) from department)
select budget from department , max_budget where department.budget = max_budget.value;
多表关联
on : 关键字
引入on关键字的两个优点: 1. 对于外连接这类连接来说,on条件的表现与where条件是不同的.
2. 如果在on子句中指定连接条件, 并在where子句中出现其余条件, 这样SQL更易于读
时间日期
SQL标准中
date : 日历日期 '2020-07-20'
time : 时间 时分秒 '09:30:00'
timestamp : date和time的组合 '2020-07-10 10:29:00'
我们可以利用cast e as t 形式表达式来将一个字符串(或字符串表达式) e 转换成类型 t , 其中 t 是char, date, time, timestamp 等等中一种. 字符串必须符合中确的格式.
CAST('2020-07-19' as date)
CAST(1234 as char)
rank() 排序 : select id , rank() over(order by score desc) as s_rank
from score order by s_rank
分窗 : 趋势分析是分窗的应用案例之一.
要写查询来计算一个窗口的聚集值, 用我们已经学到的那些特性是相对简单的. 例如 :计算一个固定的三天时间区间的销售量. 但是,如果我们想对每隔三天时间区间都如此计算, 那么查询就变得复杂了.
SQL提供分窗特性用于支持这样的查询.
