Mysql数据库

最新推荐文章于 2024-04-12 21:08:19 发布

章鱼哥TuNan&Z

最新推荐文章于 2024-04-12 21:08:19 发布

阅读量1.3k

点赞数

分类专栏： # MySQL 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/qq_43528451/article/details/114380910

版权

MySQL 专栏收录该内容

21 篇文章 2 订阅

订阅专栏

Mysql数据库

1.登录MySQL数据库

方式1：

mysql -u用户名 -p密码
mysql -u用户名 -p 回车输入密码

方式2：

mysql --host=ip地址 --user=用户名 --password=密码

2、MySQL图形化开发工具-DataGrip

DataGrip连接Mysql

如果自动下载驱动没有成功，则可以手动设置驱动文件

3、SQL语句

3.1SQL语句分类

SQL分类;
- 数据定义语言：简称DDL(Data Definition Language)，用来定义数据库对象：数据库，表，
  
  列等。关键字：create，alter，drop等
- 数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键字：insert，delete，update等
- 数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户。
- 数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字：
  
  select，from，where等

3.2、SQL通用语法

SQL语句可以单行或多行书写，以分号结尾

可使用空格和缩进来增强语句的可读性

MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

例如：SELECT * FROM user。

同样可以使用/**/的方式完成注释 – #

MySQL中的我们常使用的数据类型如下

4、DDL之数据库操作：database

4.1创建数据库

格式：

create database 数据库名；#直接创建数据库，如果存在则报错
create database if not exists bigdata_db；#如果数据库不存在则创建
create database 数据库名 character set 字符集；#创建数据库时设置字符集

实例：

create database bigdata_db

4.2查看MySQL服务器中所有的数据库：

show databases;

4.3删除数据库

格式：

drop database 数据库名称

实例:

drop database bigdata_db;

4.4使用数据库

选择数据库

格式：

use 数据库名字

实例:

use bigdata_db;

查看正在使用的数据库

select database();

5、DDL之表操作：table

5.1创建表

格式：

create table if not exists 表名（
	字段名 类型（长度）[约束]，
	字段名 类型（长度）[约束]，
	...
）;
类型： 
	varchar(n)字符串
	int 整形
double浮点
	data 时间
	timestamp时间戳
结束语：
primary key 主键，被主键修饰字段中的数据，不能重复、不能为null。

实例:

###创建分类表
create table category(
    cid varchar(20) primary key,#分类ID
    cname varchar(100) #分类名称
)

5.2查看表

查看数据库中的所有表：

格式:show tables;

查看表结构：

格式：desc 表名；

例如：desc sort；

5.3删除表

格式：drop table 表名；

实例：

drop table category;

5.4修改表结构格式:

alter table 表名 add 列名类型（长度）[约束]；

作用：修改表添加列.

例如：

#为分类表添加一个新的字段为分类描述 varchar(20)
alter table category add 'leixing' varchar(20)

alter table 表名 change 旧列名新列名类型（长度）约束；

作用：修改表修改列名.

例如;

#为分类表的分类描述字符段更换为description varchar(30)
alter table category change 'leixing' description Varchar(30)

alter table 表名 drop 列名；

作用：修改表删除列

例如：

#删除分类表中description这列
alter table category drop description;

6. DML数据操作语言

6.1插入表记录：insert

语法：

--向表中插入某些字段
insert into 表（字段1，字段2，字段3...） values (值1，值2，值3);
--向表中插入所有字段，字段的顺序为创建表时的顺序
insert into 表 values（值1，值2，值3）

INSERT INTO category(cid,cname) VALUES('c002','服饰');

6.2更新表记录：update

用来修改指定条件的数据，将满足条件的记录指定列修改为指定值

语法：

--更新所有记录的指定字段
update 表名 set 字段名=值，字段名=值，，，；
--更新符号条件记录的指定字段
update 表名 set 字段名=值，字段名=值 where 条件；

实例：

update category set cname = '家电'；#将所有行的cname改为'家电'
update category set cname = '水果' where cid ='c001';#将cid为c001的cname修改为水

6.3删除记录：delete

逻辑删除

磁盘删除

语法

delete from 表名[where 条件];
或则
truncate table 表名；

实例：

delete from category where cid = '005';#删除cid 为005的记录
truncate category;#清空表数据

注意：

delete 一条一条删除，不清空auto_increment记录数。

truncate 直接将表删除，重新建表，auto_increment将置为零，从新开始

7、SQL约束

7.1主键约束

PRIMARY KEY 约束唯一标识数据库中的每一条记录。

主键必须包含唯一的值。

主键列不能包含NULL值。

每个表都应该有一个主键，并且每个表只能有一个主键。

7.1.1添加主键约束

方式一：创建表时，在字段描述处，声明指定字段为主键；

create table persons1
(
	Id int PRIMARY KEY,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)     
)

方式二：创建表时，在constraint约束区域，声明指定字段为主键：
- 格式：[constraint 名称]primary key (字段列表)
- 关键字constraint可以省略，如果需要为主键命名，constraint不能省略，主键名称一般没用。
- 字段类表需要使用小括号住，如果有多字段需要使用逗号分隔。声明两个以上字段为主键，我们称为联合主键。

create table persons2
(
FirstName varchar(255), 
    LastName varchar(255), 
    Address varchar(255), 
    City varchar(255),
	CONSTRAINT pk_PersonID PRIMARY KEY(FirstName,LastName)
)

或

create table persons3
(
FirstName varchar(255),
    LastName varchar(255),
    Address varchar(255),
    City varchar(255) 
)

7.1.2删除主键约束

如果撤销PRIMARY KEY 约束，请使用下面的SQL:

ALTER TABLE Persons DROP PRIMARY KEY;

7.1.3自动增长列

我们通过希望在每次插入新记录时，数据库自动生成字段的值。

我们可以在表中使用auto_increment（自动增长列）关键字，自动增长列类型必须是整形，自动增长列必须为键（一般是主键）。

下列SQL语句把"persons"表中的“id”列定义为auto——increment主键

create table persons4
(
id int primary key auto_increment,
    LastName varchar(255),
    FirstName varchar(255), 
    Address varchar(255),
    City varchar(255)
)

向persons添加数据时，可以不为Id字段设置值，也可以设置成null，数据库将自动维护主键值：

insert into persons(FirstName,LastName) VALUES ('Bill','Gates')
insert into Persons(id,FirstName,LastName) VALUES(NULL,'Bill','Gates')

扩展：默认AUTO_INCREMENT的开始值是1，如果希望修改起始值，请使用下列SQL语法：

alter table Persons auto_increment=100

7.2非空约束

NOT NULL 约束强制列不接受NULL值

7.3唯一约束

unique约束唯一标识数据库表中的每条记录。

unique和primary key约束均为列或列集合提供了唯一的保证。

primary key 拥有自定义的UNIQUE约束。

每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束

添加唯一约束

创建表时，在字段描述处，声明唯一：

create table person
(
Id int UNIQUE, 
    LastName varchar(255) NOT NULL,
    FirstName varchar(255), 
    Address varchar(255), 
    City varchar(255)
)

7.4外键约束

foreign key 表示外键约束，将在多表中学习。

8.DQL数据查询语言

8.1准备工作

create table priduct(
pid int primary key,
    pname varchar(20),
    price double,
    category_id varchar(32)
);
INSERT INTO product(pid,pname,price,category_id) values(1,'联想'，5000，'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001'); 
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'杰克琼斯',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002'); INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002'); 
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003'); 
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003'); INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003'); 
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004'); INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005'); INSERT INTO product(pid,pname,price,category_id) VALUES(13,'海澜之家',1,'c002');

8.2语法：

select [distinct]
*| 列名，列名
from 表
where 条件

8.3简单查询

#表别名
select pname as pn from product;
#去掉重复值
select distinct price from product;
#查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示
select pname,price +10 from product;

8.4.条件查询

实例：

#查询商品名称为"花花公子"的商所有信息；
select * from product where pname ='花花公子';
#查询价格不是800的所有商品
select * from product where price!=800;
select * from product where price<> 800;
select * from producr where NOT(price=800);

#查询商品价格在200到1000之间所有商品
SELECT * FROM product WHERE price >= 200 AND price <=1000; 
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
#查询商品价格是200或800的所有商品
SELECT * FROM product WHERE price = 200 OR price = 800;
SELECT * FROM product WHERE price IN (200,800);
#查询含有'霸'字的所有商品
select * from product where pname like '%霸%'
#查询以'香'开头的所有商品
select * from product where pname like '香%';
#查询第二个字为'想'的所有商品
select * from product where pname like '_想%';
#查询有分类的商品
select *from product where category_id is not null;

8.5排序查询

通过order by语句，可以将查询出的结果进行排序。暂时放置在select语句的最后。

select * from 表名 order by 排序字段 asc|desc;
ASC 升序（默认）
DESC 降序
#使用价格排序（降序）
select * from product order by price desc;
#在价格排序（降序）的基础上，以分类排序（降序）
select * from product order by price desc ,category_id desc;
#显示商品的价格(去重)，并排序（降序）
select distinct price from product order by price desc;

8.6聚合查询

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查

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

实例：

#查询商品的总条数
select count(*) from product;
#查询价格大于200商品的总条数
SELECT COUNT(*) FROM product WHERE price > 200;
#查询分类为'c001'的所有商品的总和
select SUM(price) From product where category_id ='c001';
# 查询分类为'c002'所有商品的平均价格
select AVG(price) from product where category_id = 'c002';
查询商品的最大价格和最小价格
select MAX(price),MIN(price) from product;

limit语句

limit子句来限制select语句返回的行数

要检索查询返回值的行的一部分，请使用Limit 和OFFSET子句

语法：

select 
	column_list
from
	table1
order by column_list 
limit row_count OFFSET offset;
#row_count确定将返回的行数。
#OFFSET子句开始返回行之前跳过偏移行。OFFSET子句是可选的。如果同时使用LIMIT和OFFSET子句，OFFSET会在LIMIT约束行数之前先跳过偏移行。

8.7分组查询

分组查询是指使用group by 子句对查询信息进行分组。

格式：

select 字段1，字段2…from 表名 GROUP BY 分组字段 HAVING 分组条件；

分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。

hiving与where的区别：

1）.having 是在分组后对数据进行过滤，where是在分组前对数据进行过滤。

2）.having 后面可以使用分组函数（统计函数），where后面不可以使用分组函数。

实例：

#1统计各个分类商品的个数
SELECT category_id,COUNT(*) FROM prodcut GROUP BY category_id;
#2统计各个分类商品的个数，且只显示个数大于1的信息	
select category_id,count(*) from product group by category_id having count(*)>1;

8.8分页查询

分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示

方式。例如数据共有30条，每页显示5条，第一页显示1-5条，第二页显示6-10条。

格式：

select 字段1，字段2... FROM 表名 LIMIT M,N
M:整数，表示从第几条索引开始，计算方式（当前页-1）*每页显示条数
N:整数，表示查询多少条数据
SELECT 字段1，字段2... FROM 表明 LIMIT 0,5
SELECT 字段1，字段2... FROM 表明 LIMIT 5,5
#查询product表的前5条记录
SELECT * FROM product LIMIT 0,5

8.9insert into select 语句

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已经存在的表中。

基本语法：

INSERT INTO table2
SELECT column_name(s)
FROM table;

实例：

create table product2(
	pid int primary key,
    pname varchar(20),
    price double
);
insert into product2 select pid,pname,price from product where category_id ='c001';

9.多表操作

实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要分类表（category）、商品表（products）、订单表（orders）等多张表。且这些表的数据之间存在一定的关系，接下来我们将在单表的基础上，一起学习多表方面的知识。

9.1表与表之间的关系

一对多关系：
- 常见实例：客户与订单，分类和商品，部门和员工
- 一对多建表原则：在从表（多方）创建一个字段，字段作为外键指向主表（一方）的主键

9.2外键约束

现在我们有两张“分类表”和“商品表”，为了表明商品属于哪个分类，通常情况下，我们将在商品表上添加一列，用于存放分类cid的信息，此列称为：外键

此时“分类表category”称为：主表，“cid”我们称为主键。“商品表products”称为：从表，category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系，呈现就是一对多关系。

外键特点：

从表外键的值是对主表主键的引用
从表外键类型，必须与主表主键类型一致

声明外键约束

语法：

alter table 从表 add [constraint][外键名称] foreign key (从表外键字段名) references 主表（主表的主键）;

[外键名称] 用于删除外键约束的，一般建议“_fk”结尾

alter table 从表 drop foreign key 外键名称

使用外键目的：
- 保证数据完整性

9.3一对多操作

9.3.1分析

category分类表，为一方，也就是主表，必须提供主键cid
products商品表，为多方，也是就是从表，必须提供外键 category_id

9.3.2实现：分类和商品

###创建分类表
create table category(
	cid varchar(32) PRIMARY KEY,
    cname varchar(100)#分类名称
);
#商品表
CREATE TABLE products(
pid varchar(32) PRIMARY KEY,
    name varchar(40),
    price double,
    category_id varchar(32)
);
#添加约束
alter table products add constraint product_fk foreign key (category_id) references
category(cid);

9.3.3操作

#1向分类表中添加数据
INSERT INTO category(cid,cname) values('c001','服装');
#2向商品表添加普通数据，没有外键数据，默认为null
INSERT INTO products(pid,pname) values('p001','商品名称');
#3向商品表添加普通数据，含有外键信息（category表中存在这条数据）
INSERT INTO products (pid,pname,category_id) values('p002','商品名称2','c001');
#4向商品表添加普通数据，含有外键信息(category表中不存在这条数据)--失败，异常
INSERT INTO product(pid,pname,category_id)	values('p003','商品名称2','c999');
#5 删除指定分类（分类被商品使用）--执行异常
DELETE FROM category where cid = 'c001';

10.多表查询

create table category(
	cid varchar(32) primary key,
    cname varchar(50)
);
create table products(
	pid varchar(32) primary key,
    pname varchar(50),
    price int,
    flag varchar(2),  #是否上架标记为：1表示上架、0表示下架
    category_id varchar(32),
    constraint products_fk foreign key (category_id) references category(cid)
)

10.1初始化数据

#分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品')
#商品
INSERT INTO products(pid,pname,price,flag,category_id)VALUES('P001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔 ',3000,'1','c001'); 
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神 ',5000,'1','c001');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002'); 
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯 ',200,'1','c002'); 
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子 ',440,'1','c002'); 
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸 ',2000,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿 ',800,'1','c003'); 
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草 ',200,'1','c003');

10.2多表查询

1.交叉连接查询（基本不会使用-得到的是两个表的乘积）[了解]

语法：select * from A,B;

2.内连接查询（使用的关键字inner join --inner可以省略）

隐式内连接：select * from A,B where 条件；
显示内连接：select * from A inner join B on 条件；

3、外连接查询（使用的关键字outer join --outer 可以省略）

左外连接：left outer join
- select * from A left outer join B on 条件；
右外连接：right outer join
- select * from A right outer join B on 条件；

#1.查询哪些分类的商品已经上架
#隐式内连接
select distinct c.cname from category c,product p
where c.cid = p.category_id AND p.flag = '1';
#内连接
select distinct c.name from category c
INNER JOIN products p ON c.cid =p.category_id
where p.flag = '1';
#2查询所有分类商品的个数
#左外连接
INSERT INTO category(cid,cname) VALUES('c004','奢侈品');
SELECT cname,COUNT(category_id) FROM category c 
LEFT OUTER JOIN products p 
ON c.cid = p.category_id 
GROUP BY cname;

下面通过一张图说明连接的区别：

10.3子查询

子查询：一条select语句结果作为另一条select语法的一部分（查询条件，查询结果，表等）

select …查询字段 …from …表… where …查询条件

#3 子查询，查询“化妆品” 分类上架商品详情
#隐式内连接
select p.*
from products p,category c 
where p.category_id=c.cid AND c.cname = '化妆品';
#子查询
##作为查询条件
SELECT * FROM products p
WHERE p.category_id =
(
SELECT c.cid 
    FROM category c
    where c.cname='化妆品'
);
## 作为另一张表
SELECT * FROM products p,
(SELECT * FROM category where cname='化妆品') c
where p.category_id = c.cid;

#查询"化妆品"和"家电"两个分类上架商品详情
select * from products p
where p.category_id in 
(
select c.cid from category c
    where c.cname='化妆品' or c.cname ='家电'
)

11、MySQL索引（书本目录）

11.1概述

索引是 MySQL 中一种十分重要的数据库对象。它是数据库性能调优技术的基础，常用于实现

数据的快速检索。

索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质

上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

1）顺序访问

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。这种方式实现比较简单，但是当表中有大量数据的时候，效率非常低下。

2）索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

例如，在学生基本信息表students中，如果基于student_id建立了索引，系统就建立了一张索引列到实际记录的映射表，当用户需要查找student_id 为12022的数据的时候，系统先在student_id索引上找到该记录，然后通过映射表找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。

11.2.索引的分类

根据存储方式的不同，MySQL中常用的索引在物理上分为以下两类。

B-树索引

B-树索引又称为 BTREE 索引，目前大部分的索引都是采用B-树索引来存储的。B-树索引是一个典型的数据结构，基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

哈希索引

哈希（Hash）一般翻译为“散列”，也有直接音译为“哈希”的，就是把任意长度的输入（又叫做预映射，pre-image）通过散列算法变换为固定长度的输出，该输出就是散列值。

HASH索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。

根据索引的具体用途，MySQL中的索引在逻辑上分为以下3类：

普通索引

普通索引是最基本的索引类型，唯一任务是加快对数据的访问速度，没有任何限制。创建普通索引时，通常使用的关键字是INDEX或KEY.

唯一性索引

唯一性索引是不允许索引列具有相同索引的索引。如果能确定某个数据列只包含彼此各个不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一性索引。创建唯一性索引的目的往往不是为了提高访问速度，而是为了避免数据出现重复。

主键索引

主键索引是一种唯一性索引，即不允许重复或者值为空，并且每个表只能有一个主键。主键可以在创建表的时候指定，也可以通过修改表的方式添加，必须指定关键字PRIMARY KEY.

11.3索引的操作

11.3.1普通索引：

11.3.1.1创建索引：

方式1-直接创建

CREATE INDEX indexName ON mytable(username([length]));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定length。

方式2-修改表结构（添加索引）

ALTER table tableName ADD INDEX indexName(columnName)

方式3-创建表的时候直接指定

CREATE TABLE mytable(
	ID INT NOT NULL,
    username VARCHAR(16) NOT NULL,
    INDEX indexName(username(length))
);

11.3.1.2查询索引

#1、查看表中所有索引
SHOW INDEX FROM table_name;
#2、查看数据库所有索引
SELECT * FROM mysql.'innodb_index_stats' a WHERE a.'database_name'='数据库名';

#2查看某一表索引
SELECT * FROM mysql.'innodb_index_stats' a WHERE a.'database_name' ='数据库名'
and a.table_name like '%表名%';

11.3.1.3删除索引

DROP INDEX [indexName] ON mytable；

alter table mytable drop index indexName；

11.3.2唯一索引

它与之前的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有一下几种创建方式：

11.3.2.1创建索引：

方式1-直接创建

CREATE UNIQUE INDEX indexName ON mytable(username(length))

方式2-修改表结构（添加索引）

ALTER table mytable ADD UNIQUE [indexName] （usernam（length））

方式3-创建表的时候直接指定

CREATE TABLE mytable
(
	ID INT NOT NULL,
    username VARCHAR(16) NOT NULL,
    UNIQUE [indexName](username(length))
);

11.3.2.2删除索引

DROP INDEX [indexName] ON mytable;

alter table mytable drop index indexName;

11.3.3.主键索引

主键索引的操作就是主键约束的操作，

11.4.索引的使用原则和注意事项

虽然索引可以加快查询速度，提高 MySQL 的处理性能，但是过多地使用索引也会造成以下弊端：

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需

要的空间就会更大。
当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护

速度。
对于那些在查询中很少使用或参考的列不应该创建索引。因为这些列很少使用到，所以有索引或

者无索引并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度，并增大

了空间要求。

12.MySql开窗函数

12.1.概述

MySql在8.0的版本增加了对开窗函数的支持，终于可以在MySql使用开窗函数了。

开窗函数的语法结构：

#key word :partition by & order by
<开窗函数>over（[RARTITION by<列清单>]
Order by <排序用列清单>）

Mysql中支持的开窗函数有很多，这里重点给大家介绍三个：row_number(),rank()

,dense_rank()

12.2.开窗函数介绍

row_number(),rank(),dense_rank()这三个函数都是用于返回结果集的分组内每行的排名

三者区别:

**row_number:**不管排名是否有相同的，都按照顺序1，2，3……n

**rank:**排名相同的名次一样，同一排名有几个，后面排名就会跳过几次

dense_rank:排名相同的名次一样，且后面名次不跳跃

12.3.案例

数据准备：

create table employee (empid int,ename varchar(20) ,deptid int ,salary decimal(10,2)); insert into employee values(1,'刘备',10,5500.00);
insert into employee values(2,'赵云',10,4500.00); 
insert into employee values(2,'张飞',10,3500.00); 
insert into employee values(2,'关羽',10,4500.00); 
insert into employee values(3,'曹操',20,1900.00); 
insert into employee values(4,'许褚',20,4800.00); 
insert into employee values(5,'张辽',20,6500.00); 
insert into employee values(6,'徐晃',20,14500.00); 
insert into employee values(7,'孙权',30,44500.00); 
insert into employee values(8,'周瑜',30,6500.00); 
insert into employee values(9,'陆逊',30,7500.00);

对employee表中按照deptid进行分组，并对每一组的员工按照薪资进行排名

SELECT empid, ename, deptid, salary,
row_number() over (PARTITION BY deptid ORDER BY salary DESC) AS row_number1, 
rank() OVER (PARTITION BY deptid ORDER BY salary desc) AS rank2, 
dense_rank() OVER (PARTITION BY deptid ORDER BY salary desc) AS dense_rank3 
FROM 
employee;

章鱼哥TuNan&Z

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
4
评论
Mysql数据库

Mysql数据库1.登录MySQL数据库方式1：mysql -u用户名 -p密码mysql -u用户名 -p 回车输入密码方式2：mysql --host=ip地址 --user=用户名 --password=密码2、MySQL图形化开发工具-DataGripDataGrip连接Mysql[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TZiNRdUw-1614873125781)(https://gitee.com/TuNan86/mapdeport2/
复制链接

扫一扫