数据库语法

数据库

一、数据库资料#

1.数据库分为关系型数据库和非关系型数据库，关系型数据库的资料会储存成表格的形式，表格内的资料有各种属性，以此来进行区分，属性可以被设置成primary key（主键），它可以唯一的表示每一笔资料，比如：

2.属性还可以被设置成foreign key（外键），这样的属性会和另外一个表格或所在的表格的属性产生联系，比如：

3.除此之外，如果一个primary key无法区分每一笔资料，则可以在一张表格上为多个属性设置成primary key

4.当然一个属性既可以是primary key也可以是foreign key。

二、基础#

1.基础数据库的介绍：#

2.各个字段的关系：#

Copy
{数据库【表格<字段或属性（内容）>】}

比如： 
数据库 TEST 【表1、表2、...表n】
表1【字段、内容】 表2【字段、内容】 ....

三.MYSQL语法介绍：#

（前言：关系数据库一般有mysql，oracle，postgrasql，sql，server等，我们主要学习mysql相关的内容。）

基础数据类型

数据类型#

注释：#

	MySQL	Oracle	MSSQL
注释符	/**/、#、/!/、/!50000xx/、–、-- - 、–+	–、/**/ --%0a-	–、/**/、–%0a-
空白字符	%09%0A%0B%0C%0D%20	%00%09%0A%0B%0C%0D%20	%00-%20

Copy
1.注释符号：
--（空格）和--+
URL传参时，参数包含有特殊字符（%、#、&）的处理方法
其中+表示空格，也就是可以用--+代替--（空格）

#
url中#号是用来指导浏览器动作的（例如锚点），对服务器端完全无用。所以，HTTP请求中不包括#
解决方法：直接在url中使用#号有问题，就可以把#号转换成url编码（%23）就可以执行了

2.表注释
创建表的时候写注释，在使用comment关键字
create table 表名
 (
字段名 类型 comment '字段的注释'
 )comment='表的注释';

基础语法:#

1.增：#

Copy
-- 建库
create database ``

-- 建表
create table ``（）; -- 括号里依次填写属性的名称、类型、（可选）参数（如图，主键和外键也是类型，也可以另起一行写primary key（属性名）），每填完一个属性用逗号隔开
    CREATE TABLE table_name(
    column_name1 data_type(size),
    column_name2 data_type(size),
    column_name3 data_type(size),
    ....
    );
可选参数：
1.AUTO_INCREMENT
这是MySQL在创建表的过程中定义的自增列，自增长列的一个作用就是定义的列可以自动按指定步长进行整数增长，默认是按1增长。
2.DEFAUL
这是在创建表过程中指定默认值，通常是NULL，但也可以指定为其它值，其它值的类型必须与定义的列的类型一致。
3.PRIMARY KEY
这是创建表过程中指定的主键，主键具有唯一不能为NULL的属性。

Copy
-- alter和insert
alter table `表名` add/(drop column+属性名) +属性名+类型;----为表格增添（删除）属性

insert into `表名` values（）；----在表格里填写内容，括号内按照各个属性*依次*填写一整行内容（如果不想依次填写，可以在表名后加括号在括号里填写想要的属性顺序，再按照自己设定的顺序填写即可）
例如：insert into student values(1,'小马',50,'数学'),(2,'小马',60,'物理');

-- ⑤避免重复内容添加（注意要有主键）
INSERT IGNORE INTO 表名 (列 1，列 2， ...)
VALUES (值 1，值 2， ...);

2.删：#

Copy
drop database ``;----删除一个资料库

drop table ``;----删除一个表格

delete from ‘表名’
[可选]where ‘属性’=内容;--------删除这一行的资料(可以写多个判断中间用and连接)
[可选]order by ‘属性’ ------------按照指定顺序删除
[可选]limit num--------------------用于告知服务器在控制命令被返回到客户端前被删除行的最大值

3.查：#

Copy
-- describe查询
查表和列
describe '表格'；----查看表格（只能看到属性和表名）
desc ... ---简写

-- show查询
查所有数据库
show databases;
查表
show tables (form database) 
查列
show columns from `words`;

-- select查询
查字段
select ‘属性’ from ‘表格’;-----只查看表格里的某个属性下的资料

select * from '表格';----查看表格里的全部资料

-- 与order by和where和limit组合
select * 
from '表格'----选取表格
order by ‘属性’ -----按照某些属性排序（默认由小到大，后面加上desc表示由大到小）
where ‘属性’=‘内容’（或者‘属性’ like ‘内容’）------显示符合的内容（可以写多个中间用and连接，如果用or连接则表示只要有一个成立即可输出）
limit n，m；------条件语句，表示从第n行开始显示前m行；

-- as：重新指定字段或者表的别名
1.字段名：
select table.a as '课程号', table.b as '课程名称', COUNT(table.c) as '选课人数' from table
2.表别名：（就是我把select a.mycol from table a 查询出来的集合命名成表t）
select t.name from (select a.mycol from table a) as t

 -- 函数：
 			avg('字段')----平均数

			sum（'字段'）------求和

             max/min（'字段'）------求最值

             distinct ('字段')-----去除重复项
             
             count('字段')----计算字段下的行数

-- 万用字元："%"可以代表多个字元，比如说，表示结尾是648的所有资料：%648，表示开头是233的所有资料：233%

4.改：#

Copy
# 改字段
update ‘表格’-------确定要更改的表格
set ‘属性’=内容------想要将目标更改成的样子（可以改多个，逗号隔开）
where ‘属性’=内容;------确定更改的具体目标（所在的行）
-- 基础语法
alter table <表名> [改变方式]
改变方式包含： add ,change,drop,modify

-- 修改表名
alter table old_table rename to new_table;

-- 改注释
-- 改表注释
alter table 表名 comment '修改后的表的注释';
-- 改字段注释
alter table 表名 modify column 字段名 字段类型 comment '修改后的字段注释';

-- 改字段类型
alter table `表名` modify column(可略写) `字段名` 字段类型

-- 改为自增主键
alter table t_app modify column aid int(5) auto_increment ;

-- 改字段名字(要重新指定该字段的类型)
alter table t_app change old_name new_name varchar(20) not null;

-- 增添
-- 增主键
alter table t_app add aid int(5) not null ,add primary key (aid);
alter table t_app add aid int(5) not null auto_increment ,add primary key (aid); （自增主键）
-- 删主键
alter table table_name drop primary key;

-- 删除字段
alter table t_app drop aid;

-- 单独使用rename
-- 改表名（等价于alter）
rename table old_table to new_table; 

限制语句#

Copy
  为属性添加限制语句可以对属性下的内容信息进行限制
  比如，在属性’name‘ varchar（13）后填上not null，则为表格增添内容时不能在name属性下填null，否则会报错。
  
  其他限制语句还有：
  unique（让内容不能重复）
  default ’ ‘（预设，代替null）
  auto_increment（让类型为int的属性自动填写值如1.2.3...从而不必再填写内容）

逻辑与运算符：#

Copy
①不等于----<>    

②大于等于---->=     

③小于等于-----< =    

④或者-----or  ||

⑤且-----and   &&

⑥非-----not   !

⑦等于其中之一-----in（a，b，c...）【类似于or语句】

1.逻辑异或----xor
表示当任意一个操作数为null时，返回值为null；
对于非null的操作数，如果两个操作数都是非0值或者都是0值，则返回结果为0；
如果一个为0值，另一个为非0值，返回结果为1。

2.位或----|
规则：与0异或位不变，与1异或位翻转
位或运算的实质是将参与运算的几个数据按照对应的二进制数作为进行逻辑或运算。对应的二进制位有一个或两个为1则该位的运算结果为1，否则为0。

3.位与-----&
位与运算的实质是将参与运算的几个操作数按照对应的二进制数逐位进行逻辑与运算。对应的二进制位都是1或1则该位的运算结果为1，否则为0。

3.位异或----^
位与运算的实质是将参与运算的几个操作数按照对应的二进制数逐位进行逻辑与运算。对应的二进制位都是1或1则该位的运算结果为1，否则为0。

windows前端open语句：mysql -uroot -pab2532394262c

特殊符号：#

Copy
1.``（勾号）：为了防止sql报错。 因为sql中有一些保留字,当用户的字段名是它的保留字时,这个时候必须在sql语句中加`,否则sql就会报错。

2.~（位非）
运算法则：0变1，1变0
~170 的二进制：1111 1111 0101 0101 十进制为：-171
~75 的二进制：1111 1111 1011 0100 十进制为：-76
双写用法：~~1000=1000（相当于没写）

3.&（位与）
上下运算,按照与的运算规则：0&0=0 ；0&1=0；1&1=1
例如：
170 & 75 结果为：0000 0000 0000 1010
再把二进制转换为十进制：10
ex. select 170 & 75 = 10

4.|(位或)
上下运算,按照与的运算规则：0|0=0 ；0|1=1；1|1=1
例如：
170 & 75 结果为：0000 0000 1110 1011
再把二进制转换为十进制：235

5.^(位异或)
上下运算,按照与的运算规则：0&0=0 ；0&1=1；1&1=0
例如：
170 & 75 结果为：0000 0000 1110 0001
再把二进制转换为十进制：225

6.%%
双百分号：伪字段引用变量关键字的前缀：%%CLASSNAME、%%CLASSNAMEQ、%%ID和%%TABLENAME，在ObjectScript计算字段代码和触发器代码中使用。

https://blog.csdn.net/yaoxin521123/article/details/119980961

外键约束#

网站笔记

https://greenhathg.github.io/2019/06/20/Mysql外键与级联操作/

Copy
CREATE TABLE students(
	id INT,
    class_id INT,
    name VARCHAR(10),
    INDEX(class_id),
    PRIMARY KEY(id),
    FOREIGN KEY (class_id) REFERENCES classes (id)
)
# 以上父表是：classes 表
# 字表是：students 表
# 创建表：先创建父表，再创建子表；
# 删除数据：先删除子表中的数据，再删除父表中的数据；
# 插入数据：先插入父表中的数据，再插入子表中的数据。
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);
# 外键约束的名称 fk_class_id 可以任意，不用名字的话可以去掉 CONSTRAINT fk_class_id
# FOREIGN KEY (class_id) 指定了 class_id 作为外键
# REFERENCES classes (id) 指定了这个外键将关联到 classes 表的 id 列（即 classes 表的主键）

级联操作#

Copy
CREATE TABLE students(
	id INT,
    class_id INT,
    name VARCHAR(10),
    INDEX(class_id),
    PRIMARY KEY(id),
    FOREIGN KEY (class_id) REFERENCES classes (id)
    ON DELETE CASCADE ON UPDATE CASCADE
);
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id)
ON DELETE CASCADE ON UPDATE CASCADE;
# 在删除父表中的数据的时候，级联删除子表中的数据 on delete cascade
# 在更新父表中的数据时候，级联更新子表中的数据 on update cascade
# 级联操作在外键约束后面添加

通配符#

Copy
SQL 通配符必须与 LIKE 运算符一起使用。
用法：
select *from tb where td like 'c%'; -- 查询tb表中td字段下以c开头的内容

结构优先级#

Copy
sql 执行顺序优先级由高到低依次是：from 关键字后面的语句、where 关键字后面的语句、“ group by ”后面的语句、select 后面的语句、“ order by ”后面的语句。

变量#

Copy
-- 变量的分类 : 局部变量和全局变量，局部变量用@来标识，全局变量用@@来标识(常用的全局变量一般都是已经定义好的)
-- 声明变量 : 变量在使用前必须先声明才能够使用。(一些环境不能声明，只能直接赋值)
-- 申明局部变量语法
DECLARE @变量名 数据类型;
例如： declare @a int;

-- 局部变量赋值
    SET 变量名=值
    SELECT  变量名1:=值1,变量名2=值2
-- sql中':='符号为对变量赋值；
例如：设置排名
SET @rank=0;
SELECT @rank :=(@rank + 1) AS rank,s_id, s_name, sum_score  
FROM (
    SELECT s.s_id, s.s_name, SUM(c.s_score) AS sum_score
    FROM student s
    INNER JOIN score c ON s.s_id = c.s_id
    GROUP BY s.s_id, s.s_name
) as a
ORDER BY sum_score DESC;

四、结构语法#

1.show#

列出所有数据库#

Copy
-- 列出所有数据库
show databases;

列出所有表#

Copy
-- 列出当前数据库的所有表
show tables;

查某数据库下又那些表#

第一种：直接查

Copy
show tables from information_schema;-- 以查information_schema下的表为例

第二种：先使用再查

Copy
use information_schema;
show tables;

来源验证

Copy
-- show databases和show tables的结果来源

-- databases
select *from information_schema.schemata;  #该表中储存了所有数据库的名字

select schema_name from information_schema.schemata; #输出所有数据库的名字
show databases; # 结果和上面一样

-- tables
select *from information_schema.tables; #该表储存了所有数据库的表名

select table_name from information_schema.tables; #where table_schema='原神';
show tables from information_schema;

2. select#

查询时间#

Copy
-- 查询时间：（随选择的时区和时间的变化而变化）
select now();

当前数据库#

Copy
-- 查看当前选择的数据库:(随use语句的变化而变化)
select database();

版本#

Copy
select version();

查看系统#

Copy
-- 以mysql为例
select @@version_compile_os;

当前用户#

Copy
select user();

简单计算#

Copy
select 1+1; #返回一个表，计算结果是表的内容

查看数据路径#

Copy
-- 以mysql数据存放文件和mysql安装目录为例
select @@datadir;
select @@basedir;

查询表中的数据#

Copy
-- 以查询`mysql`数据库中的`user`表为例(*表示全部，如果想查部分字段的内容，可以将*改为字段名，前提是你知道字段名，你可以先查全部找到想看的字段名记下，再进行输入，使结果只显示你要的部分内容，相邻字段用逗号隔开)
select * from mysql.user;
select host,user from mysql.user;

3. describe#

查看字段和字段类型#

Copy
-- 以mysql数据库中user表格为例
describe mysql.user;

简写#

Copy
-- describe可以简写成disc
desc mysql.user

4.条件语句#

where#

Copy
-- where相当if的判断，用来筛选符合要求的信息:以查询mysql数据库的user表格中，满足字段user下的内容为root，且字段host下的内容为localhost的资料
select host,user from mysql.user where user ='root' and host='localhost';

order by#

Copy
-- 除此之外条件语句还有order by+字段名（或者数字表示第几列），表示按指定的列排序（下面为，按表t1中，字段为id的列排序）
select * from t1 order by id ASC;
-- 升序/倒序排列 ASC/DESC

group by#

Copy
-- 根据group by后面的字段分类整个表中的内容，也就是字段相同的归为一类
-- 根据name字段将student表中的信息分类，然后同类的score字段下的数据求和，最后显示name和sum(score)的结果（每一类显示一行）
select name,sum(score) from Student group by name
-- 使用 WITH ROLLUP，此函数是对聚合函数进行求和，注意 with rollup是对 group by 后的第一个字段，进行分组求和。

limit#

Copy
limit n，m；------条件语句，表示从第n行开始显示前m行；
limit m -----只返回前m行内容

having#

Copy
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
WHERE 搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。 

例子：现在，我们希望查找订单总金额少于 2000 的客户。
我们使用如下 SQL 语句：
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

5.特殊条件#

is和in条件#

Copy
IS NULL 是SQL中一种用于在条件语句中检查列是否为空（NULL）的特殊条件操作符。

IS NULL 可以用于任何数据类型的列，包括数字、字符串、日期等。
IS NULL 用于判断某个列的值是否为 NULL。
IS NULL 返回一个布尔值，如果列的值为 NULL，则返回真（true），否则返回假（false）。
IS NULL 通常与其他条件运算符（如 WHERE 子句中的等号操作符）一起使用，以进一步筛选结果集。

其他：
is null			是否为空；
is not null      是否不为空；
in   			判断一个值是in列表中的任意一个值；
not in   		判断一个值不是in列表中的任意一个值；
like    		通配符匹配；
between and      判断一个值是否在两个值之间。

逻辑符号#

Copy
 > 大于; 
 >= 大于等于; 
 < 小于; 
 <= 小于等于; 
 = 等于; 
 !=或<> 不等于;
 
 || 或;
 && 且;

6.union联合查询#

介绍#

Copy
-- union联合查询命令：同时查询多个表格并将查询结果汇总到同一张表格中,主语表格间的字段数量要一致，字段名字可以不一致，完全相同的信息只会显示一次

基础用法：联合查询test.t1和test.t2(没有就当场创建)
create table test.t2 (id int,nbme varchar(10));
insert into test.t2 values (7,"giegie");
select * from test.t1 union select * from test.t2;

判断表格的字段数（sql注入）#

Copy
select * from test.t1 union select 1,1; #这里输入了1,1输出了值则表示表格t1有两个字段，如果不输出值那就改成1,1,1以此类推

视图#

Copy
-- 视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据
语法：CREATE VIEW 视图名 AS 查询数据源表语句;
例子：
create view student_id as select id,name from student;
-- 使用视图其实和使用表没啥区别，正常查询就可以了
select *from studeny_id;
-- 视图的修改
语法一： CREATE OR REPLACE VIEW 视图名 AS 查询语句；# 兼具视图创建和修改的功能
语法二： ALTER VIEW 视图名 AS 查询语句；# 只有修改功
-- 视图的删除
语法：DROP VIEW 视图名
例如： drop view student_id;

-- 注意：
● 视图不会独立存储数据，原表发生改变，视图也发生改变。没有优化任何查询性能。
● 如果视图包含以下结构中的一种，则视图不可更新。
● 聚合函数的结果。
● DISTINCT 去重后的结果。
● GROUP BY 分组后的结果。
● HAVING 筛选过滤后的结果。
● UNION、UNION ALL 联合后的结果。

7. except联合查询#

​ SQL EXCEPT子句/操作符用于合并两个SELECT语句，并从那些没有被第二个SELECT语句返回的第一个SELECT语句返回行。这意味着EXCEPT仅返回行，在第二个SELECT语句不可用。

​ 正如使用UNION操作，同样的规则时，使用EXCEPT操作符适用。MySQL不支持EXCEPT运算符。

8. 连结(JOIN)#

Copy
如果想要从多个表获取信息,例如, 如果我们想要找出某个商店里的衣服类商品的名称,数量及价格等信息, 则必须分别从 ShopProduct 表和 Product 表获取信息。

Copy
内连结(INNER JOIN)
语法：from <tb_1> inner join <tb_2> on <condition(s)>

Product 表保存了 商品编号, 商品名称 ,商品种类 等信息,这个表可以提供关于衣服种类的衣服的详细信息, 但是不能提供商店信息。
ShopProduct 表, 这个表里有 商店编号名称, 商店的商品编号及数量. 但要想获取 商品的种类及名称售价 等信息,则必须借助于 Product 表。
找出一个类似于"轴"或者"桥梁"的公共列, 将两张表用这个列连结起来。这就是连结运算所要作的事情。
-- 商品编号列是一个公共列,因此很自然的事情就是用这个商品编号列来作为连接的“桥梁”，将Product和ShopProduct这两张表连接起来。
	按照内连结的语法, 在 FROM 子句中使用 INNER JOIN 将两张表连接起来, 并为 ON 子句指定连结条件为 ShopProduct.product_id=Product.product_id, 就得到了如下的查询语句:
SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
from ShopProduct AS SP
inner join Product AS P
on SP.product_id = P.product_id;

要点：1. select 子句中的列最好按照 表名.列名 的格式来使用。（当两张表的列除了用于关联的列之外, 没有名称相同的列的时候, 也可以不写表名）

Copy
结合 WHERE 子句使用内连结
如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选, 则需要把 WHERE 子句写在 ON 子句的后边。

第一种增加 WEHRE 子句的方式, 就是把上述查询作为子查询, 用括号封装起来, 然后在外层查询增加筛选条件
SELECT *
  FROM (-- 第一步查询的结果
        SELECT SP.shop_id
               ,SP.shop_name
               ,SP.product_id
               ,P.product_name
               ,P.product_type
               ,P.sale_price
               ,SP.quantity
          FROM ShopProduct AS SP
         INNER JOIN Product AS P
            ON SP.product_id = P.product_id) AS STEP1
 WHERE shop_name = '东京'
   AND product_type = '衣服' ;
   

但实际上, 如果熟知 WHERE 子句将在 FROM 子句之后执行, 也就是说, 在做完 INNER JOIN ... ON 得到一个新表后, 才会执行 WHERE 子句, 那么就得到标准的写法:
SELECT  SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM ShopProduct AS SP
 INNER JOIN Product AS P
    ON SP.product_id = P.product_id
 WHERE SP.shop_name = '东京'
   AND P.product_type = '衣服' ;

Copy
自连结(SELF JOIN)
 一张表也可以与自身作连结, 这种连接称之为自连结. 需要注意, 自连结并不是区分于内连结和外连结的第三种连结, 自连结可以是外连结也可以是内连结, 它是不同于内连结外连结的另一个连结的分类方法。

自然连结(NATURAL JOIN)
 自然连结并不是区别于内连结和外连结的第三种连结, 它其实是内连结的一种特例--当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件。
 SELECT *  FROM shopproduct NATURAL JOIN Product
上述查询得到的结果, 会把两个表的公共列(这里是 product_id, 可以有多个公共列)放在第一列, 然后按照两个表的顺序和表中列的顺序, 将两个表中的其他列都罗列出来。

Copy
外连结(OUTER JOIN)
左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值; 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值; 而全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充。

三种外连结的对应语法分别为:

-- 左连结     
FROM <tb_1> LEFT  OUTER JOIN <tb_2> ON <condition(s)>
-- 右连结     
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
-- 全外连结
FROM <tb_1> FULL  OUTER JOIN <tb_2> ON <condition(s)>

例子：
SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
  FROM Product AS P
  LEFT OUTER JOIN ShopProduct AS SP
    ON SP.product_id = P.product_id;

9. 函数#

String类#

Copy
-- length('xxx'):查询字符串长度
select length('abcde');

-- ascii('x'):返回一个字符的ascii码（替代函数：ord('x')）
select ascii('a');

-- count(*):查询一个表中一共有几条数据
select count(*) from test.t1;# 一共有四条数据
select * from test.t1 where name='zwh' or 1=1#'

-- concat(string1, string2, ...) 连接多个字符串

-- 在mysql中，load_file(file_name)函数读取一个文件并将其内容作为字符串返回。其中file_name是文件的完整路径。
select load_file('D:\\www\a.txt');

-- substr('xxx',i,n) 分割字符串函数:（替代函数mid('xxx',i,n)）（类似函数left('xxx',n)从左往右截取n个字符）
用法：函数表示从字符串第i个字符开始，取n个字符然后输出，如果不写n的值则默认输出到最后
select substr('abcdef',1,3);# 从第1个字符a开始取3个字符，即输出abc
select substr(database(),1,1)='a';# database()输出结果一定是字符串，那么我们可以用来判断第数据库名字的第一个字符是否等于xxx，输出1为真，0为假

IF#

Copy
-- if(expr1,expr2,expr3) 
expr1值为TRUE，则返回值为expr2
expr1值为FALSE，则返回值为expr3

-- ifnull( expr1 , expr2 )
判断第一个参数expr1是否为NULL：
如果expr1不为空，直接返回expr1；
如果expr1为空，返回第二个参数 expr2  

-- nullif(expr1,expr2)：如果两个参数相等则返回NULL，否则返回第一个参数的值expr1

case#

Copy
Case具有两种格式。简单Case函数和Case搜索函数。
--简单Case函数
CASE sex
         WHEN '1' THEN '男'
         WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
         WHEN sex = '2' THEN '女'
ELSE '其他' END

rank#

Copy
1.rank() over的用法
作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。
例如：成绩排名，使用这个函数，成绩相同的两名是并列,其他依次从分数高到低排名
select id,name,score,rank() over(order by score desc) 'rank' from student_score;

2.dense_rank() over
和rank() over 的作用相同，区别在于dense_rank() over 排名是密集连续的。例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学接着下一个名次，而不会跳过一个名次。
select id,name,score,dense_rank() over(order by score desc) 'rank' from student_score;

3.row_number() over
这个函数不需要考虑是否并列，即使根据条件查询出来的数值相同也会进行连续排序

注意:rank本身是个函数所以当我们取别名时要加''引号