标准SQL语法及使用技巧

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提供分窗特性用于支持这样的查询.