mysql基本使用

标签：基本 返回 mysql Select 使用 table null 主键 select

MySQL常用图形化工具：

Navicat

Sqlyog

Mysql workbend(msi自动安装)

 

//////////////////////////////////////////////////////////

Mysql数据库基本操作

1、 ddl数据定义语言

1. 对数据库的常用操作

 

l  查看所有的数据库：show databases;

l  创建数据库: create database [if not exists] mydb1 [charset=utf8];

l  切换（选择要操作的）数据库: use mydb1;

l  删除数据库: drop database [if not exists] mydb1;

l  修改数据库: alter database mydb1 character set utf8;

 

1. 对表结构的常用操作

创建表格式

Create table [if not exists] 表名(

        字段名1 类型[(宽度)] [约束条件] [comment ‘字段说明’],

        字段名2 类型[(宽度)] [约束条件] [comment ‘字段说明’],

        字段名3 类型[(宽度)] [约束条件] [comment ‘字段说明’]

)[表的一些设置];

 

如：

        Use mydb1; -- 先打开需要操作的数据库

DROP TABLE IF EXISTS `table_name`; -- 先判断表是否存在，存在就先删除表

CREATE TABLE if not exists `table_name` (

   `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主表id',

   `name` varchar(20) NOT NULL COMMENT '名称',

   ‘birth’ date,

Address varchar(20),

Score double,

Gender tinyint unsigned,  # 无符号小整数

Bodyheigt Decimal(5,2) #decimal(M,D) M有效位数包含小数位，D小数位数

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

其他操作：

l  查看当前数据库的所有表名称：show tables;

l  查看指定某个表的创建语句：show create table 表名称;(可以看到创建表的详细信息，约束，引擎编码等)

l  查看表结构：desc 表名称;

l  删除表：drop table 表名称;

 

1. 修改表结构

添加列：

        Alter table 表名称 add 列名称 类型(长度) [约束];

        Alter table student add dept varchar(20);

修改列名和类型：

        Alter table 表名称 change 旧列名称 新列名称 类型(长度) [约束];

Alter table student change dept department varchar(30);

              删除列：

                     Alter table 表名称 drop 列名称;

                     Alter table student drop department;

              修改表名称：

                     Rename table 表名称 to 新表名称;

                     Rename table student to stu;

1. 数据类型

1) 数值类型

整数类型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，

浮点数类型包括 FLOAT 和 DOUBLE，

定点数类型为 DECIMAL。

#decimal(M,D) M有效位数包含小数位数，D小数位数，小数点不占位数

2) 日期/时间类型

包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。

类型格式：

# DATE : YYYY-MM-DD

# TIME : HH:MM:SS

# YEAR : YYYY

# DATETIME : YYYY-MM-DD HH:MM:SS

# TIMESTAMP : YYYY-MM-DD HH:MM:SS 自动填充时间,和当地时区有关

3) 字符串类型

包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。# 字符串类型赋值时用单引号括起来，如：‘张三’

4) 二进制类型

包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

2、 dml数据操作语言—表数据操作（数据库.表名也可以打开数据库然后直接使用表名）

插入insert格式：

Insert into 表名 (列1,列2,列3…) values (值1, 值2, 值3…);# 向表中插入某些列

Insert into 表名 (列1,列2,列3…) values (值1, 值2, 值3…)[, (值1, 值2, 值3…)];

# 向表中插入多行，未赋值的列的值未默认值或者null

Insert into 表名 values (值1, 值2, 值3…);# 向表中插入所有列,也可一次多行

删除delete格式：

Delete from 表名 [where 条件]；

Truncate table 表名; 或者 truncate 表名;

# 注意：delete和truncate原理不同，delete只删除内容，而truncate类似drop table,可以理解为是将整个表删除，然后再创建该表；

如果表主键id自增类型时：（delete和truncate主键约束和事务不同）

Delete删除记录后，增加新记录时id字段值继续上次值自增；

Truncate 重建表后，增加记录时id字段值会从起始值重新开始自增。

更新update格式：

Update 表名 set 字段名=值, 字段名=值…[ where 条件];

Update employee set salary=salary+1500 where name=’王五’; -- 原字段上直接更新

 

///////////////////////////////////////////////////////////////////

Mysql约束（constraint）: 表中数据的限制条件

1、作用：表设计时加入约束的目的是为了保证表中的记录完整性和有效性，如不能为空或不能重复等。

2、分类：

• 主键约束（primary key）PK
• 自增长约束（auto_increment）
• 非空约束(not null)
• 唯一性约束（unique）
• 默认值约束(default)
• 零填充约束（zerofill）
• 外键约束(foreign key) FK
• 检查约束CHECK(age>17 AND age<30)：如大学生年龄（18—30岁），可以减少无效输入

3、 主键约束（primary key）PK

• 主键约束是一个列或者多个列的组合，其能唯一的标识表中的每一行；
• 主键约束相当于唯一约束+非空约束的组合，主键约束不允许重复，也不许出现空值；
• 每个表最多允许一个主键；
• 关键字：primary key;
• 创建主键约束时，系统默认会在所在的列或列组合（联合主键）上建立对应的唯一索引；

添加单列主键：

创建点列主键有两种方式，一种是定义字段是指定主键，一种是定义字段后指定主键。

方法一：

Create table 表名(

…

       <字段名> <数据类型> primary key,

       …

);

Create table emp1(

Eid int primary key,

Name varchar(20),

Deptid int,

Salary double

);

方法二：

Create table 表名(

…

       [constraint <约束名>] primary key [字段名]

);

Create table emp2(

Eid int,

Name varchar(20),

Deptid int,

Salary double,

Constraint pk1 primary key(Eid) -- Constraint pk1可以省略

);

 

联合主键：主键是由一张表中多个字段组成的。

注意：当主键是由多个字段组成时，不能直接在字段名后面声明主键约束；

一张表只能有一个主键，联合主键也是一个主键；

联合主键的每一列都不能为空null;

Create table table_name(

        …

        [constraint <约束名>] Primary key (字段1,字段2,…,字段n)

);

Create table emp3(

        Name varchar(20),

        Deptid int,

        Salary double,

        Primary key(name,deptid)

);

 

通过修改表结构添加主键：（可以添加单例主键和联合主键）

Alter table table_name add primary key (字段列表);

Alter table emp4 add primary key (eid);

Alter table emp5 add primary key (name,deptid);

删除主键约束：

Alter table <table_name> drop primary key;

Alter table emp5 drop primary key;

 

4、 自增长约束（auto_increment）:

字段名 数据类型 auto_increment

• 默认情况下，auto_increment的初始值是1，每新增一条记录，字段值自动加1；
• 一个表中只能有一个字段使用auto_increment约束，且该字段必须有唯一索引，以避免需要重复（即为主键或主键的一部分）；
• auto_increment约束的字段必须具备not null属性；
• auto_increment约束的字段只能是整数类型（tinyint,smallint,int,bigint）；
• auto_increment约束的字段的最大值受该字段的数据类型约束，如果达到上限，auto_increment就会失效；
• 指定自增长字段的初始值：如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。
• 创建表时指定初始值：

Create table t_user1(

Id int primary key auto_increment,

Name varchar(20)

)auto_increment=100;

• 创建表之后指定初始值：

Alter table t_user3 auto_increment=200;

• Delete数据之后自动增长从断点开始；
• Truncate数据之后自动增长从默认起始值开始；

5、 非空约束（not null）:

字段值不能为空，如果添加数据时没有指定值，数据库系统会报错。

方式1：<字段名><数据类型> not null; -- 创建标识指定字段非空约束

方式2：<字段名><数据类型> ; -- 创建标识指定字段不指定非空约束

Alter table 表名 modify 字段 类型 not null; -- 创建表后修改字段非空约束

Alter table 表名 modify 字段 类型; -- 创建表后修改字段去掉非空约束

       Null,’null’,’’三者不同。

6、 唯一约束（unique）：

唯一约束（unique key）指所有记录中字段的值不能重复出现（值可以为Null）。

--在mysql中null和任何值都不相同，甚至和自己都不相同。

方式1：<字段名> <数据类型> unique

Create table t_user8(

        Id int,

        Name varchar(20),

        Phone_number varchar(20) unique –指定唯一约束

);

Insert into t_user8 values(1001,’张三1’,138);

Insert into t_user8 values(1002,’张三2’,null);

Insert into t_user8 values(1003,’张三3’,null);

--在mysql中null和任何值都不相同，甚至和自己都不相同。

方式2：alter table 表名 add constraint 约束名unique(列);-- 约束名便于删除使用

Alter table t_user9 add constraint unique_pn unique(phone_number);

删除唯一约束：

格式：alter table <表名> drop index <唯一约束名>;

Alter table t_user9 drop index unique_pn;

7、 默认约束（default）：指定某列的默认值。

方式1：<字段名> <数据类型> default <默认值>

Address varchar(20) default ‘北京’  

Insert into t_user10(address) values(null); --可以给具有默认值字段的值赋null值。

方式2：alter table 表名 modify 列名 类型 default 默认值 ;

 

删除默认约束：

格式：alter table 表名 modify 列名 类型 default null;

 

8、 零填充约束（zerofill）：

• 插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应位数的0；
• Zerofill默认为int(10); --使长度固定，数据显示效果 0000000020=20
• 当使用zerofill时，默认会自动加unsigned(无符号)属性，使用unsigned属性后，数值范围是原值的2倍，例如：有符号为-128~+127，无符号为0~256
• 操作：create table t_user12(

Int int zerofill,

Name varchar(10)

);

• 删除：

Alter table t_user12 modify in int;

 

/////////////////////////////////////////////////////////////////

DQL基本查询

语法格式：

Select

[all|distinct]

<目标列的表达式1> [别名],

<目标列的表达式2> [别名]…

From <表名或视图名> [别名], <表名或视图名> [别名]…

[Where<条件表达式>]

[group by <列名>]

[having <条件表达式>]  # 对分组之后的结果查询

[order by <列名> [asc|desc]]

[limit <数字或者列表>];  # 对最终结果筛选

 

# 执行顺序：fromàwhereàgroup byàcount(pid)àhavingàselectàorder byàlimit

（8）Select

（9）distinct 字段名1,字段名2，

（6）[fun(字段名)] 

（1）from 表1

（3）<join类型>join 表2

（2）on <join条件>

（4）where <where条件>

（5）group by <字段>

（7）having <having条件>

（10）order by <排序字段>

（11）limit <起始偏移量,行数>

 

创建实例数据库和表

Create database if not exists mydb2;

Use mydb2;

Create table product(

       Pid int primary key auto_increment,

       Pname varchar(20),

       Price double,

       Category_id varchar(20)

);

添加实例数据

Insert into product values(null,’海尔洗衣机’,5000,’c001’);

Insert into product values(null,’美的空调’,3000,’c001’);

Insert into product values(null,’格力空调’,5000,’c001’);

Insert into product values(null,’九阳电饭煲’,5000,’c001’);

 

Insert into product values(null,’啄木鸟衬衣’,300,’c002’);

Insert into product values(null,’恒源祥西裤’,800,’c002’);

Insert into product values(null,’花花公子夹克’,440,’c002’);

Insert into product values(null,’劲霸休闲裤’,266,’c002’);

Insert into product values(null,’海澜之家卫衣’,180,’c002’);

Insert into product values(null,’杰克琼斯运动裤’,430,’c002’);

 

Insert into product values(null,’兰蔻面霜’,300,’c003’);

Insert into product values(null,’雅诗兰黛精化水’,200,’c003’);

Insert into product values(null,’香奈儿香水’,350,’c003’);

Insert into product values(null,’sk-ii神仙水’,350,’c003’);

Insert into product values(null,’资生堂粉底液’,180,’c003’);

 

Insert into product values(null,’老北京方便面’,56,’c004’);

Insert into product values(null,’良品铺子海带丝’,17,’c004’);

Insert into product values(null,’三只松鼠坚果’,88,’c004’);

 

• 关键字as 定义列别名或表别名，as可以省略

Select pname as pn from product as p;

• 去掉重复值

Select distinct price from product; -- 去重某一列，去除重复列

Select distinct * from product; -- 记录行去重，去除重复行

• 查询结果是表达式

Select pname,price+10 new_price from product;

• 运算符

算数运算符：

+，-，*，/或者div（返回商）, %或者mod（返回余数）；

Select * from product where price!=800; # price<>800,not(price=800)

 

比较运算符：

=，<，<=,>,>=,

<=>（安全的等于，两个操作码均为null时，其所得值为1，而当一个操作码为null时，其所得值为0）,

<>或!= （不等于）,

is null或isnull（判断一个值是否为null）,

is not null（判断一个值是否不为null）,

least（当两个或多个参数时，返回最小值）,

greatest（当两个或多个参数时，返回最大值）,

between and（判断一个值是否落在两个值之间，包含两个端点值）,

in（判断一个值是in列表中的任意一个值）,

not in（判断一个值不是in列表中的任意一个值）,

like（通配符匹配）,

regexp(正则表达式匹配);

主要通配符

通配符    描述

_             匹配单个字符

%            匹配任意个字符

.             匹配任意单个字符

[]            匹配[]中的任意一个字符

*             匹配零个或多个在它前面的东西

^            以什么开始

$            以什么结尾

select * from product where price between 200 and 1000;

# price between 200 and 1000等价于 price>=200 and（&&） price<=800

Select * from product where pname like ‘_海%’；

# 下划线匹配一个字符，%匹配任意字符

Select * from product where category_id is null;

# 判断是否为Null时不能用等号，只能用 is null,或 is not null

Select least(10,5,20) as small_number;

Select least(10,null,20) as small_number;

# 如果求最小值时，有个值为null,则不会进行比较，结果直接返回null;

Select least(20,30,40) as big_number;

Select least(20,null,40) as big_number;

# 如果求最大值时，有个值为null,则不会进行比较，结果直接返回null;

 

逻辑运算符：

Not或!（逻辑非）,

And或&&（逻辑与）,

Or或||（逻辑或），

Xor(逻辑异或);

 

位运算（了解）：是在二进制数上进行计算的运算符，位运算会先将操作数变成二进制数，进行位运算，软后再将计算结果从二进制数变回十进制数。

|（按位或），

&(按位与),

^(按位异或),

<<(按位左移),

>>(按位右移),

~(按位取反，反转所有比特);

Select 3&5; # 0011&0101=0001

0011

0101

0001

Select 3|5; # 0011&0101=0111

0011

0101

0111

Select 3^5;

0011

0101

0110

Select 3>>1; # 右移位

0011>>1--à0001 #向右移位，左边空出位补零

Select 3<<1; # 左移位

0011<<1--à0110 #向左移位，右边空出位补零

Select ~3；

0011---》11111111111…11111100

 

• 排序查询

Order by 字段1 [asc|desc], 字段2 [asc|desc]

Asc代表升序，desc代表降序，如果不写默认升序

可以支持单个字段，多个字段，表达式，函数，别名

放在查询语句的最后面。Limit子句除外。

# 排序字段为数值字段或英文和数字字符类型（按值-字典顺序），如：‘beijing’,’2023-01-18’,日期以字符串处理。

# 对于升序排序，数字按照从小到大的顺序排列，字符按照编码的顺序排列，日期时间按照从早到晚的顺序排列；降序排序正好相反。

Select distinct price from product order by price desc; # 去重后排序

select * from product order by CONVERT(pname USING gbk); # 中文排序

对于中文内容字段排序需要进行转换后，实际按中文拼音排序。

转换函数：CONVERT(chineseColumnName USING gbk)

# 对于包含中文的字段加上”binary”属性，使之作为二进制比较，例如将”name char(10)”改成”name char(10)binary”。

# 如果你使用源码编译MySQL，可以编译MySQL时使用 –with–charset=gbk 参数，这样MySQL就会直接支持中文查找和排序了(默认的是latin1)。也可以用 extra-charsets=gb2312,gbk 来加入多个字符集。

# 如果不想对表结构进行修改或者重新编译MySQL，也可以在查询语句的 order by 部分使用 CONVERT 函数。

# 多列排序：先按字段1排序，如果字段1值相同，则按字段2来排序。

 

# 查询姓名第二个字母不是“A”且薪水大于1000元的员工信息，按年薪降序排列

# 年薪：12*月薪+奖金

# ifnull(comm,0) 如果comm的值为null,则当作0，不为null,则还是原来的值。

# select * from emp where ename not like ‘_A%’ and sal>1000 order by (12*sal+ifnull(comm,0));

 

• 聚合查询

聚合函数查询是纵向查询，对一列的值进行计算，然后返回一个单一的值，另外聚合函数查询会忽略空值。

Count()：统计指定列不为null的记录行数；

Sum()：计算指定列的数值和，如果指定列数据类型不是数值类型，那么结果为0；

Max()：计算指定列的最大值，如果指定列是字符类型，那么使用字符串排序运算；

Min()：计算指定列的最小值，如果指定列是字符类型，那么使用字符串排序运算；

Avg()：计算指定列的平均值，如果指定列数据类型不是数值类型，那么结果为0；

Select max(price),min(price),avg(price) from product;

聚合函数对Null值的处理：

如果count函数的参数为星号，则统计所有的记录个数，而如果参数为某字段，不统计含null值的记录个数；

Sum和avg函数忽略null值的存在，就好像该记录不存在一样（计算平均值时分母也不包含Null记录行，如果字段值为0时会考虑记录存在）；

Max和min函数同样忽略null值的存在。

 

• 分组查询

格式：select 字段1，字段2… from 表名 group by 分组字段 having 分组条件;

Select category_id,count(*) sl from product group by category_id having sl>4 order by sl;

如果要进行分组的话，则select子句之后，只能出现分组的字段和聚合操作，其他字段不能出现。

# 注意，一般情况下，聚合函数和分组会结合在一起用。

分组之后对统计结果进行筛选的话必须使用having,不能使用where;

Where子句用来筛选from子句中指定的操作所产生的行；

Group by 子句用来分组where 子句的输出：

Having 子句用来从分组的结果中筛选行。

# 分组可以根据一个字段，也可以根据多个字段，如果是一个字段，则该字段相同就会分到同一组，如果是多个字段，则多个字段都相同才能分到同一组。

 

• 分页查询-limit：

格式1：select 字段1，字段2… from 表名 limit n;

# (m=0,)n=5表示从第1（索引为0）条查询前5条记录

格式2：select 字段1，字段2… from 表名 limit m,n;

# m=3,n=5表示从第4（索引为3）条开始查询前n条记录

M : 整数，表示从第几条索引开始，计算方式：（当前页-1）*每页显示条数；

N : 整数，表示查询多少条数据；

# SELECT 列名 FROM 表名 LIMIT 偏移量,条数

# 当LIMIT 后面的参数只有一个时为条数，而默认偏移量为0

 

• Insert into select语句：将一张表的数据导入另一张

格式1：insert into table2(field1,field2…) select value1,value2… from table1

# 字段和值要求一致

格式2：insert into table2 select * from table1

# 表1和表2列要求一致

# 要求目标表Table2必须存在

Create table product3(

       Category_id varchar(30),

       Product_count int

);

Insert into product3 select category_id,count(*) from product group by category_id;

 

• 正则表达式：描述了一种字符串匹配的规则，正则表达式本身就是一个字符串，使用这个字符串来描述、用来定义匹配规则，匹配一系列符合某个句法规则的字符串。开发中，正则表达式通常被用来检索、替换那些符合某个规则的文本。

MySQL通过REGEXP关键字支持正则表达式进行字符匹配

选项       说明       例子       匹配值示例

^ 匹配文本的开始字符 '^b' 匹配以字母b开头的字符串book

$ 匹配文本的结束字符 'st$’ 匹配以st结尾的字符串test

. 匹配除‘\n’之外的任何单个字符 'b.t’ 匹配任何b和t之间有一个字符 bit

* 匹配零个或多个在它前面的字符 'f*n’ 匹配字n前面有任意个字符fan、faan

+ 匹配前面的字符1次或多次 'ba+’ 匹配b开头，后面至少紧跟一个 a  ba

<字符串> 匹配包含指定字符的文本 'fa’   fan、afa、faad

[字符集合] 匹配字符集合中的任何一个字符'[xz]'匹配x或者z      dizzy

[^] 匹配不在括号中的任何字符 '[^abc]’ 匹配任何不包含a、b或 c 的字符串

字符串{n,} 匹配前面的字符串至少n次 b{2} 匹配2个或更多的字符串

{n,m} 匹配前面的字符串至少n次,至多m次 b{2,4} 匹配最少2个,最多4个

模式       描述

^     匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。

$     匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。

.      匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用像 '[.\n]' 的模式。

[...]   字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。

[^...] 负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。

p1|p2|p3 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。

？    匹配前面的子表达式零次或1次。例如，zo* 能匹配 "z" 以及 "zo"。？ 等价于{0,1}。

*      匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。

+     匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。

{n}   n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。

{n,m}      m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

 

查找name字段中以'st'为开头的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以'ok'为结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含'mar'字符串的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

 

///////////////////////////////////////////////////////////

多表查询-多表关系：

• 一对一关系：

一般一对一关系很少见，遇到一对一关系的表最好是合并表。

• 一对多/多对一关系：

实现原则：在多的一方建立外键，指向一的一方的主键。

部门dept (1)—>员工emp(n);

• 多对多关系：

多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，将多对多的关系，拆成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来的那两张表的主键。

学生表student(m)ßà课程表course(n)

学生表student(1)à中间表t_student_course(n)ß课程表course(1)

• 外键约束：

Mysql外键约束（foreign key）是表的一个特殊字段，经常与主键约束一起使用，对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。

【主表】部们表dept (1)<主键列deptno>—>【从表】员工表emp(n)<外键列dept_id>

定义一个外键时，需要遵循下列规则：

²  主表必须已经存在于数据库中，或者是当前正在创建的表；

²  必须为主表定义主键；

²  主键不能包含空值，但允许在外键中出现空值。即，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

²  在主表的表名后面指定列名的组合。这个列或列的组合必须是主表的主键或候选键。

²  外键中列的数目必须和主表的主键中列的数目相同。

²  外键中列的数据类型必须和主键中对应列的数据类型相同。

• 创建外键约束：

方式1：创建表时设置外键约束

在create table语句中，通过foreign key关键字来指定外键，语法如下：

[constraint <外键名>] foreign key 字段名1[,字段名2,…] references <主表名> 主键列1[,主键列2,…]

# 创建主表、主键：

       Create table if not exists dept(

              Deptno varchar(20) primary key, #主表主键，部门编号

              Name varchar(20)

);

              # 创建从表、外键：

                     Create table if not exists emp(

                            Eid varchar(20) primary key, # 从表主键，员工编号

                            Ename varchar(20),

                            Age int,

                            Dept_id varchar(20), # 员工所属部门编号

                            Constraint emp_fk foreign key(dept_id) references dept(detpno) # 外键

);

       方式2：在修改表时创建外键约束

外键约束也可以在修改表时添加，但是添加外键约束的前提是：从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

Alter table <数据表> add constraint <外键名> foreign key(<列名>) references <主表名>(<列名>);

Alter table emp2 add constraint dept_id_fk foreign key(dept_id) references dept2(detpno);

 

• 外键约束对操作影响：主要限制数据的增、删、改操作

必须先给主表添加数据，给从表添加数据时，外键列的值必须依赖主表的主键列。

根据主键à外键，从表中的外键必须存在于主表的主键中。

主表的数据被从表依赖时，不能删除，否则可以删除。

从表的数据可以随意删除。

• 删除外键约束

当一个表中不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会解除主表和从表间的关系。

Alter table <从表名> drop foreign key <外键约束名>;

Alter table emp2 drop foreign key dept_id_fk;

• 多对多关系：

在多对多关系中，A表（主表）的一行对应B表（主表）的多行，B表（主表）的一行对应A表（主表）的多行，我们要新增加一个中间表（从表），来建立多对多关系。中间表至少要有两个外键，这两个外键分别指向原来的那两张表的主键。

              # 创建学生表Student(左侧主表)

              Create table if not exists student(

              Sid int primary key auto_increment,

              Name varchar(20),

              Age int,

              Gender varchar(20)

);

# 创建课程表course(右侧主表)

              Create table if not exists student(

              cid int primary key auto_increment,

              cname varchar(20)

);

# 创建中间表student_course(从表)

              Create table score (

              Sid int,

              cid int,

              score double

);

#建立外键约束（2次）

Alter table score add foreign key(sid) references student(sid);

Alter table score add foreign key(cid) references course(cid);

# 先给主表添加数据再给从表添加数据，修改和删除时，中间从表可以随便删除和修改，但是两边的主表受从表依赖的数据不能删除和修改。

# 外键约束对多表查询并无影响。

//////////////////////////////////////////////////////////////////////////////

多表联合查询：外键约束对多表查询并无影响

多表查询就是同时查询两个或两个以上的表，因为有的时候用户在查看数据的时候，需要显示的数据来源自多张表，多表查询可以有一下分类：

• 交叉连接查询（产生笛卡尔积，了解:A*B）

Select * from A,B;

# 笛卡尔积会产生很多冗余数据，后期的其他查询可以在该集合的基础上进行条件筛选。

 

• 内连接查询（关键字inner join –inner可以省略：A与B重合部分）

隐式内联接（sql92标准）：select * from A,B where 条件;

显式内联接（sql99标准）：select * from A inner join B on 条件 [inner join C on 条件];

# 内连接查询求多张表的交集。

Select a.deptno,a.name,count(1) as total_cnt

from dept3 a join emp3 b

• on a.deptno=b.dept_id

group by a.deptno[,a.name]

having total_cnt>=3

• order by total_cnt desc;

# group by中[,a.name]可以省略，因为a.deptno和a.name代表相同内容时。

 

• 外连接查询（关键字outer join –outer可以省略）

左外连接：left outer join(以A表为主，B表中数据内容没有的字段数据为null)

       Select * from A left outer join B on 条件 [left outer join C on 条件];

右外连接：right outer join(以B表为主，A表中数据内容没有的字段数据为null)

       Select * from A right outer join B on 条件;

满外连接：full outer join(A+B)

       Select * from A full outer join B on 条件;

# 注意：oracle里面有full join,可是在mysql对full join支持的不好，可以用union达到目的。

# union是将两个查询结果上下拼接，并去重，

# union all 是将两个查询结果上下拼接，不去重.

Select * from dept3 a left join emp3 b on a.deptno=b.dept_id

Union [all]

Select * from dept3 a right join emp3 b on a.deptno=b.dept_id

 

• 子查询

Select的嵌套：子查询即是指在一个完整的查询语句中，嵌套若干个不同功能的小查询，从而一起完成复杂的查询的一种编写形式。

# 连接查询和子查询可实现同样功能时，连接查询效率更高。

子查询可以返回的数据类型一共分为四种：

# 单行单列：返回的是一个具体列的内容，即一个单值数据；

Select * from emp3 where age=(select max(age) from emp3);

# 单行多列：返回一个行数据中多个列的内容；

# 多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围；

Select * from dept3 a join emp3 b on a.deptno=b.dept_id in (name=’研发部’ or name=’销售部’);

Select * from emp3 where dept_id in (select deptno form dept3 where name=’研发部’ or name=’销售部’);

 

# 多行多列：查询返回的结果是一张临时表；

Select * from dept3 a join emp3 b on a.deptno=b.dept_id and (name=’研发部’ and age<20)；

Select * from (select * from dept3 where name=’研发部’) t1 join (select * from emp3 where age<30) t2 on t1.deptno=t2.dept_id;

#子查询中常用逻辑关键字，可以提供丰富的查询功能：

²  All关键字：

Select * from … where c>all(查询语句);

等价于

Select * from … wehre c>result1 and c>result2 and c>result3;

# all:与子查询返回的所有值比较为true则返回true;

# all可以与=、>、>=、<、<=、<>结合使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的所有数据。

# all表示指定列中的值必须要大于子查询集的每一个值，即必须要大于子查询集的最大值；如果是小于号即小于子查询集的最小值。同理可以推出其他。

Select * from emp3 where age>all(select age from emp3 where dept_id=’1003’);

Select * from emp3 where dept_id!=all(select deptno from dept3);

 

²  Any关键字：

Select * from … where c>any(查询语句);

等价于

Select * from … wehre c>result1 or c>result2 or c>result3;

# any:与子查询返回的任何值一个比较为true则返回true;

# any可以与=、>、>=、<、<=、<>结合使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。

# any表示指定列中的值必须要大于子查询集的任意一个值，即必须要大于子查询集的最小值；如果是小于号即小于子查询集的最大值。同理可以推出其他。

# some和any是一样的，some可以理解为any的别名；

Select * from emp3 where age> any (select age from emp3 where dept_id=’1003’);

Select * from emp3 where dept_id!= any (select deptno from dept3);

 

²  Some=Any关键字：

 

²  In 关键字：

Select * from … where c in(查询语句);

等价于

Select * from … wehre c=result1 or c=result2 or c=result3;

# in:用于判断某个记录的值，是否在指定的集合中;

# 在in关键字前面加上not可以将条件反过来。

Select * from emp3 where dept_id in(select deptno from dept3 where name=’研发部’ or name=’销售部’);

 

²  Exists 关键字：

Select * from … where exists(查询语句);

# 该子查询如果“有数据结果”（至少返回一行数据），则该exists()的结果为true,外层查询执行；

# 该子查询如果“没有数据结果”（没有任何数据返回），则该exists()的结果为false,外层查询不执行 ；

# exists后面的子查询不返回任何实际数据，只返回真和假，当返回真时where条件成立；

# 注意，exists关键字，比in关键字的运算效率高，因此，实际开发中，特别是大量数据时，推荐使用exists关键字。

# 子查询语句可以引用上层查询中表的信息

# 查询公司是否有大于60岁的员工，有则输出

Select * from emp3 a where exists(select * from b where a.age>60);

Select * from emp3 a where dept_id in(select * from b where a.age>60);

# 查询有所属部门的员工信息

Select * from dept3 a where exists(select * from emp3 b where a.deptno=b.dept_id);

Select * from dept3 a where dept_id in(select * from emp3 b where a.deptno=b.dept_id);

 

• 表自关联

将一张表当成多张表来用:mysql有时在信息查询时需要进行对表自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用。自关联时必须给表起别名。

Select * from 表1 a,表1 b where 条件;

或者

Select * from 表1 a [left] join 表1 b on 条件;

# 创建表，并建立自关联约束

Create table t_sanguo(

       Eid int primary key, --员工编号（包含领导）

       Ename varchar(20),

       Manager_id int, --上级领导的编号，外键列

       Foreign key (manager_id) references t_sanguo (eid) –添加自关联约束

);

Insert into t_sanguo values(1,’刘协’,null);

Insert into t_sanguo values(2,’刘备’,1);

Insert into t_sanguo values(3,’关羽’,2);

Insert into t_sanguo values(4,’张飞’,2);

Insert into t_sanguo values(5,’曹操’,1);

Insert into t_sanguo values(6,’许诸’,5);

Insert into t_sanguo values(7,’典韦’,5);

Insert into t_sanguo values(8,’孙权’,1);

Insert into t_sanguo values(9,’周瑜’,8);

Insert into t_sanguo values(10,’鲁肃’,8);

 

# 查询每个三国人物及他的上级信息，如：关羽 刘备

Select * from t_sanguo a,t_sanguo b where a.manager_id=b.edi;

# 查询所有人物及上级

Select a.ename,b.ename from t_sanguo a left join t_sanguo b on a.manager_id=b.edi;

# 查询所有人物及上级、上上级

Select a.ename,b.ename

from t_sanguo a

left join t_sanguo b on a.manager_id=b.edi;

left join t_sanguo c on b.manager_id=c.edi;

 

//////////////////////////////////////////////////////////////

Mysql函数

在mysql中，为了提高代码重用性和隐藏实现细节，mysql提供了很多函数。可以理解为别人封装好的模板代码。

在Mysq中，函数非常多，主要可以分为一下几类：

• 聚合函数

主要聚合函数：count,sum,min,max,avg,group_concat();

group_concat()函数首先根据group by 指定的列进行分组，并且用分隔符进行分隔，将同一个分组中的值连接起来，返回一个字符串结果。

Group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator ‘分隔符’])

# 使用distinct可以排除重复值；

# 如果要对结果中的值排序，可以使用order by子句；

# separator是一个字符串值，默认为逗号,分隔符是函数最后一个参数；

如：

--将所有员工的名字合成一行

Select group_concat(emp_name) from emp;

--指定分隔符合并

Select group_concat(emp_name separator ‘;’) from emp;

--指定排序方式和分隔符

Select department,group_concat(emp_name order by salary desc separator ‘;’)

from emp

group by department;

 

• 数学函数

Abs(x)：返回x的绝对值，select abs(-1) –返回1；

Ceil(x)：返回大于或等于x的最小整数，select ceil(1.5) –返回2

Floor(x)：返回小于或等于x的最大整数，select floor(1.5) –返回1

Greatest(expr1,expr2,expr3,…)：返回列表中最大值，

Select greatest(3,34,12,8,25); --返回34

Select greatest(‘google’,’runoob’,’apple’); --返回runoob

Least(expr1,expr2,expr3,…)：返回列表中最小值，

Select lest(3,34,12,8,25); --返回3

Select lest(‘google’,’runoob’,’apple’); --返回apple

Max(expression)：返回字段expression中的最大值 select max(price) from products;

Min(expression) ：返回字段expression中的最小值 select min(price) from products;

Mod(x,y) ：返回x除以y以后的余数 select mod(5,2); --返回1

Pi()：返回园中率（3.141593）select pi();

Pow(x,y) ：返回x的y次方 select pow(2,3); --返回8

Rand()：返回0到1的随机数 select floor(rand()*100) –返回1到100间的随机整数

Round(x)：返回离x最近的整数（四舍五入）select round(1.23456) –返回1

Round(x,y)：返回指定位数的小数（四舍五入）select round(1.23456，3) –返回1.235

Select category_id,round(avg(price),2) from product group by category_id;

Truncate(x,y)：返回数值x保留到小数点后y位的值（与round区别是不进行四舍五入）

select truncate(1.23456，3) –返回1.234

 

• 字符串函数

Char_length(s)：返回字符串s的字符数

select char_length(‘runoob’); –返回6

select char_length(‘你好吗’); –返回3

length(s)：取长度，返回的单位是字节

select char_length(‘runoob’); –返回6

select char_length(‘你好吗’); –返回9；unicode每个中文字符3个字节，所以是9

Chararacter_length(s)：返回字符串s的字符数

select Chararacter_length (‘runoob’); –返回6

concat(s1,s2,…sn)：字符串s1,s2等多个字符串合并为一个字符串

       select concat(‘sql’,’runoob’,’google’,’facebook’);

concat_ws(x,s1,s2,…sn)：同concat()函数，但是每个字符串之间要加上x,x可以是分隔符

       select concat(‘-’,‘sql’,’runoob’,’google’,’facebook’);

返回'sql-runoob-google-facebook'

       Field(s,s1,s2…)：返回第一个字符串s在字符串列表(s1,s2…)中第一次出现的位置

              Select field(‘c’,’a’,’b’,’c’,’d’,’e’); 返回3

       Ltrim(s)：去掉字符串s开始处的空格

rtrim(s)：去掉字符串s结尾处的空格

trim(s)：去掉字符串s两端的空格

       mid(s,n,len)：从字符串s的n位置截取长度为len的字符串，同substring(s,n,len)

              select mid(“runoob”,2,3); --返回uno

position(s1 in s)：从字符串s中获取s1的开始位置

       select position(‘b’ in ‘abc’); --返回2

replace(s,s1,s2)：将字符串s2替代字符串s中的字符串s1

       select replace(‘abc’,’a’,’x’); --返回‘xbc’

reverse(s)：将字符串s的顺序反过来 select reverse(‘abc’); --返回cba

right(s,n)：返回字符串s的后n个字符 select right(runoob,2); =>ob

strcmp(s1,s2)：比较字符串s1和s2,如果s1=s2=> 0, s1>s2=>1,s1<s2=>-1

       select strcmp(‘runoob’,’runoob’); =>0

substr(s,start,length)：从字符串s的start位置截取长度为length的字符串

substring(s, start,length)：同substr(s,start,length)

       select substring(‘runoob’,2,3); =>uno

ucase(s)：将字符串转换为大写 select ucase(‘runoob’); =>RUNOOB

upper(s)：同ucass(s)

lcase(s)：将字符串转换为小写select ucase(‘RUNOOB’); => runoob

lower(s)：同lcase(s)

 

• 日期函数

Unix_timestamp()：返回从1970-01-01 00:00:00到当前毫秒值

       Select unix_timestamp(); =>1632729256

Unix_timestamp(date_string)：将指定日期转为毫秒值时间戳

       Select unix_timestamp(‘2011-12-07 13:01:03’); => '1323234063'

From_unixtime (bigint unixtime[,String format])：将毫秒值时间戳转换为指定日期

       Select From_unixtime(1598079966,’%Y-%m-%d %H:%i:%s’);=> '2020-08-22 15:06:06'

Curdate()：返回当前日期 select curdate();=>'2023-01-31'

Current_date()：返回当前日期,同Curdate()

Current_time()：返回当前时间 select current_time();=>'17:17:23'

Curtime()：返回当前时间 select curtime();=>'17:17:23'

Current_timestamp()：返回当前日期和时间

select Current_timestamp();=>'2023-01-31 17:33:00'

Date()：从日期或日期时间表达式中提取日期值

       Select date(‘2017-06-15’);=> ‘2017-06-15’

Datediff(d1,d2)：计算日期d1>d2之间相隔的天数

       Select datediff(‘2001-01-01’,’2001-02-02’);=>32

timediff(time1,time2)：计算时间差值

       Select timediff(’13:10:11’,’13:10:10’);=>00:00:01

Date_format(d,f)：按表达式f的要求显示日期d

       Select date_format(‘2011-1-1 1: 1: 1’,’ ’%Y-%m-%d %r’);

=>2011-01-01 01:01:01 AM

Str_to_date(string,format_mask)：将字符串转变为日期

       Select str_to_date(‘august 10 2017’,’ %Y-%m-%d’);

       =>2017-08-10

Date_sub(date,interval expr type)：函数从日期减去指定的时间间隔,日期向前跳转

       Select orderid,date_sub(orderdate,interval 2 day) from orders;

       Orders表中orderdate字段减去2天

Date_add(date,interval expr type)：函数从日期减去指定的时间间隔,日期向后跳转

       Select orderid,date_add(orderdate,interval 2 day) from orders;

       Orders表中orderdate字段加上2天

       # type值可以是：MICROSECOND,SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,YEAR,DAY_MINUTE,DAY_HOUR,YEAR_MONTH

Extract(type from d)：从日期d中获取指定的值，type指定返回的值（type取值同上），

       Select extract(minute from ‘2011-11-11 11:11:11’);=>11

Last_day(d)：返回给定日期的那个月的最后一天的日期

       Select last_day(‘2017-06-20’);=> 2017-06-30

Makedate(year,day-of-year)：基于给定参数年份year和所在年中的天数序号day-of-year返回一个日期

       Select makedate(2017,3);=>2017-01-03

Year(d)：返回年份

Month(d)：返回月份值，1到12 month('2017-02-02');=>2

Day(d)：返回日期值d的日期部分 day('2017-02-02');=>2

Hour(t)：返回t中的小时值 select hour(’01:02:03’);=>1

Minute(t)：返回t中的分钟值

second(t)：返回t中的秒钟值

quarter(d)：返回日期d是第几季节，1到4

monthname(d)：返回日期当中的月份名称，如november

dayname(d)：返回日期d是星期几，如Monday,tuesday

dayofmonth(d)：计算日期d是本月的第几天

       select dayofmonth(‘‘2011-11-11 11:11:11’);=>11

dayofweek(d)：日期d今天是星期几，1是星期日，2是星期一，依次类推

       select dayofweek(‘‘2011-11-11 11:11:11’);=>6

dayofyear(d)：计算日期d是本年的第几天

       select dayofyear(‘‘2011-11-11 11:11:11’);=>315

week(d)：计算日期d是本年的第几个星期，(0到53)

weekday(d)：日期d是星期几，0表示星期一，1表示星期二

weekofyear(d)：计算日期d是本年的第几个星期，(0到53)，同week(d)

yearweek(date,mode)：返回年份及第几周(0到53),mode中0表示周天,1表示周一,依次类推

now()：返回当前日期和时间

 

• 控制流函数

If逻辑判断语句：

       If(expr,v1,v2)：如果表达式expr成立，返回结果v1,否则，返回结果v2

              Select *,if(score>=85,’优秀’,’及格’) flag from score;

       Ifnull(v1,v2)：如果v1的值不为null,则返回v1,否则返回v2

              Select ifnull(null,’hello world’); =>hello world

       Isnull(expression)：判断表达式是否为null

              Select isnull(null);=>1

Nullif(expr1,expr2)：比较两个字符串，如果字符串expr1与expr2相等返回null,否则返回expr1

       Select nullif(25,25);=>null

Case when语句：

       Case [expression]

              When condition1 then result1

When condition2 then result2

…

When conditionn then result

Else result

              End

Case 表示函数开始，end表示函数结束。如果condition1成立，则返回result1, 如果condition2成立，则返回result2,当全部不成立则返回result,而当有一个成立之后，后面的就不执行了。

Select case 100 when 50 then ‘tom’ when 100 then ‘mary’ else ‘tim’ end;

Select case when 1=2 then ‘tom’ when 2=2 then ‘mary’ else ‘tim’ end;

Select *,

case paytype

when 1 then ‘微信支付’

when 2 then ‘支付宝支付’

when 3 then ‘银行卡支付’

else ‘其他支付方式’

end as paytipestr

from orders;

Select *,

case

when paytype =1 then ‘微信支付’

when paytype =2 then ‘支付宝支付’

when paytype =3 then ‘银行卡支付’

else ‘其他支付方式’

end as paytipestr

from orders;

 

• 窗口函数（mysql8.0版本之后才有）

# Mysql8.0新增窗口函数，窗口函数又被称为开窗函数，与oracle窗口函数类似；

# 非聚合窗口函数是相对于聚合函数来说的，聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。

# 另外还有开窗聚合函数：sum,avg,min,max

²  序号函数：

Row_number()：排序：1，2，3

Rank()：排序：1，1，3

Dense_rank()：排序：1，1，2

 

²  分布函数：

Percent_rank()：(rank-1)/(row-1)

Cume_dist()：<=当前rank值的行数/总行数

 

²  前后函数：

Lag(expr,n)：返回当前行的前n行的expr的值

lead(expr,n)：返回当前行的后n行的expr的值

 

²  头尾函数：

First_value(expr)：返回第一个expr的值

last_value(expr)：返回最后一个expr的值

 

²  其他函数：

Nth_value(expr,n)：返回第n个expr的值

ntile(n)：将有序数据分为n个桶，记录等级数

 

²  语法结构：

Window_function (expr) over (

       Partition by …

       Order by …

       Frame_clause

)

# 其中，window_function是窗口函数的名称；expr是参数，有些函数，不需要参数；over子句包含三个选项：

1、 分区（partition by）

Partition by选项用于将数据行拆分成多个区（组），他的作用类似于group by分组，如果省略了partition by,所有的数据作为一个组进行计算。

2、 排序（order by）

Over子句中的order by选项用于指定分区内的排序方式，与order by子句的作用类似。

3、 窗口大小（frame_clause）

Frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集。

 

u  序号函数有三个:row_number(),rank(),dense_rank()可以实现分组排序，并添加序号。

Row_number()|rank()|dense_rank() over (

       Partition by …

       Order by …

)

Create table employee(

       Dname varchar(20), # 部门名

       eid varchar(20), #

ename varchar(20), #

       hiredate date, # 入职日期

       salary double # 薪资

);

Insert into employee values(‘研发部’,’1001’,’刘备’,’2021-11-01’,3000);

Insert into employee values(‘研发部’,’1002’,’关羽’,’2021-11-02’,5000);

Insert into employee values(‘研发部’,’1003’,’张飞’,’2021-11-03’,7000);

Insert into employee values(‘研发部’,’1004’,’赵云’,’2021-11-04’,7000);

Insert into employee values(‘研发部’,’1005’,’马超’,’2021-11-05’,4000);

Insert into employee values(‘研发部’,’1006’,’黄忠’,’2021-11-06’,4000);

 

Insert into employee values(‘销售部’,’1007’,’曹操’,’2021-11-01’,2000);

Insert into employee values(‘销售部’,’1008’,’许诸’,’2021-11-02’,3000);

Insert into employee values(‘销售部’,’1009’,’典韦’,’2021-11-03’,5000);

Insert into employee values(‘销售部’,’1010’,’张辽’,’2021-11-04’,6000);

Insert into employee values(‘销售部’,’1011’,’徐晃’,’2021-11-05’,9000);

Insert into employee values(‘销售部’,’1012’,’曹洪’,’2021-11-06’,6000);

# 对每个部门的员工按照薪资排序，并给出排名

Select

dname,ename,salary,

row_number() over(partition by dname order by salary desc) as rn1,

rank() over(partition by dname order by salary desc) as rn2,

dense_rank() over(partition by dname order by salary desc) as rn3,

from employee;

# 三个函数排序字段出现相同的记录时显示的序号不同；

Row_number()：排序：（7000）1，（7000）2，（5000）3

Rank()：排序：（7000）1，（7000）1，（5000）3

Dense_rank()：排序：（7000）1，（7000）1，（5000）2

# 求出每个部门薪资排在前三名的员工，即分组求top n

Select * from (

       Select

dname,ename,salary,

dense_rank() over(partition by dname order by salary desc) as rn,

from employee;

) t

Where t.rn>3

# 对所有员工进行全局排序（不分组），去掉partition by dname即可

Select

dname,ename,salary,

dense_rank() over(order by salary desc) as rn,

from employee;

 

u  开窗聚合函数：sum,avg,min,max

在窗口中每条记录动态的应用聚合函数（sum,avg,min,max，count），可以动态计算在指定的窗口内的各种聚合函数值.

Select

dname,ename,salary,

sum(salary) over(partition by dname order by hiredate) as pv1

from employee;

# pv1列组内随记录累加

select cookieid,createtime,pv,

       sum(pv) over(partition by cookieid) as pv3

from itcast_t1;

# pv3如果没有order by排序语句，默认把分组内的所有数据进行sum操作。

Select

dname,ename,hiredate,salary,

sum(salary) over(partition by dname order by hiredate rows between unbounded preceding and current row) as pv1

from employee;

# pv1列组内随记录累加

# rows between unbounded preceding and current row 从开始到当前行累加

Select

dname,ename, salary,

sum(salary) over(partition by dname order by hiredate rows between 3 preceding and current row) as pv1

from employee;

# pv1列组内随记录累加

#rows between 3 preceding and current row从当前行的前三行到当前行(4行)累加，不足4行按实际行累加

Select

dname,ename, salary,

sum(salary) over(partition by dname order by hiredate rows between 3 preceding and 1 following) as pv1

from employee;

# pv1列组内随记录累加

#rows between 3 preceding and current row从当前行的前三行到当前行的下一行(5行)累加，不足5行按实际行累加

Select

dname,ename, salary,

sum(salary) over(partition by dname order by hiredate rows between current row and unbounded following) as pv1

from employee;

# pv1列组内随记录累加

#rows between 3 preceding and current row从当前行到最后行累加

 

u  分布函数：cume_dist,percent_rank

Cume_dist:

用途：分组内小于、等于当前值(rank)的行数/分组内总行数,结果是比例

应用场景：查询小于等于当前薪资（salary）的比例

Select

dname,ename, salary,

cume_dist() over(order by salary) as rn1,

--没有partition by语句所有的数据位于一组

cume_dist() over(partition by dname order by salary) as rn2

from employee;

# rn1:没有partition,所有数据均为一组，总行数12，

# 第一行：小于等于3000的行数为3，因此，3/12=0.25

# 第二行：小于等于4000的行数为5，因此，5/12=0.416666…

# rn2:按部门分组，‘研发部’的行数为6，

# 第一行：‘研发部’小于等于3000的行数为1，因此，1/6=0.1666…

 

Percent_rank:

用途：每行按公式(rank-1)/(rows-1)进行计算，其中，rank为rank()函数产生的序号，rows为当前窗口的记录总行数,分组时为组内行数

应用场景：不常用

Select

dname,ename, salary,

rank() over(partition by dname order by salary desc) as rn1,

percent_rank() over(partition by dname order by salary desc) as rn2

from employee;

# rn2:

# 第一行：(1-1)/(6-1)=0;

# 第二行：(1-1)/(6-1)=0;

# 第三行：(3-1)/(6-1)=0.4;

 

u  前后函数：lag和lead

用途：返回位于当前行的前第n行(lag(expr,n))或后第n行(lead(expr,n))的expr的值

应用场景：查询前一名同学的成绩和当前同学成绩的查（取值后相减即可）

Select

dname,ename,firedate, salary,

lag(hiredate,1,’2000-01-01’) over(partition by dname order by hiredate) as last_1_time, --没有值时为默认值’2000-01-01’

lag(hiredate,2) over(partition by dname order by hiredate) as last_2_time

from employee; 没有值时为null

 

u  头尾函数：first_value,last_value

用途：返回第一个(first_value(expr))或最后一个(last_value(expr))expr的值

应用场景：截至到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资

# 注意：如果不指定order by,则排序混乱，会出现错误

Select

dname,ename,firedate, salary,

first_value(salary) over(partition by dname order by hiredate) as first,

last_value(salary) over(partition by dname order by hiredate) as last

from employee;

 

u  其他函数：nth_value(expr,n),ntile(n)

Nth_value(expr,n):

用途：

应用场景：

 

标签：基本,返回,mysql,Select,使用,table,null,主键,select
From： https://www.cnblogs.com/qingzhen/p/17823712.html