MySQL distinct用法排序 mysql decimal排序

转载

mob64ca1404baa2 2024-05-30 00:26:56

文章标签 MySQL distinct用法排序 mysql 数据库 sql 主键 文章分类 MySQL 数据库

文章目录

DQL操作单表

排序
聚合函数
limit关键字

SQL约束

主键约束
非空约束
唯一约束
默认值

数据库事务

MySQL事务操作
事务的四大特性 ACID
MySQL 事务隔离级别
隔离级别相关命令

DQL操作单表

排序

排序：通过 ORDER BY 子句,可以将查询出的结果进行排序(排序只是显示效果,不会影响真实数据)

语法结构：SELECT 字段名 FROM 表名 [WHERE 字段 = 值] ORDER BY 字段名 [ASC / DESC]

ASC 表示升序排序(默认)
DESC 表示降序排序

排序方式：

单列排序：只按照某一个字段进行排序, 就是单列排序
组合排序：同时对多个字段进行排序, 如果第一个字段相同就按照第二个字段进行排序,以此类推

使用 salary 字段,对emp 表数据进行排序 (升序/降序)
-- 默认升序排序 ASC
SELECT * FROM emp ORDER BY salary;

-- 降序排序
SELECT * FROM emp ORDER BY salary DESC;

-- 组合排序
SELECT * FROM emp ORDER BY salary DESC, eid DESC;

聚合函数

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

语法结构：SELECT 聚合函数(字段名) FROM 表名；
常见的聚合函数：

聚合函数	作用
count(字段)	统计指定列不为NULL的记录行数
sum(字段)(字段)	计算指定列的数值和
max(字段)	计算指定列的最大值
min(字段)	计算指定列的最小值
avg(字段)	计算指定列的平均值

分组查询指的是使用 GROUP BY 语句,对查询的信息进行分组,相同数据作为一组

语法格式：SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 [HAVING 条件];
分组的目的就是我为了统计，所以，一般分组会和聚合函数一起使用，单独进行分组，没有意义
分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作, 查询其他字段没有意义

需求: 通过性别字段 进行分组,求各组的平均薪资
SELECT sex, AVG(salary) FROM emp GROUP BY sex;

#1. 查询有几个部门
SELECT dept_name AS '部门名称' FROM emp GROUP BY dept_name;

#2.查询每个部门的平均薪资
SELECT
dept_name AS '部门名称',
AVG(salary) AS '平均薪资'
FROM emp GROUP BY dept_name;

#3.查询每个部门的平均薪资, 部门名称不能为null
SELECT
	dept_name AS '部门名称',
	AVG(salary) AS '平均薪资'
FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name;

# 查询平均薪资大于6000的部门.
1) 需要在分组后,对数据进行过滤,使用 关键字 hiving
2) 分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。
# 查询平均薪资大于6000的部门
-- 需要在分组后再次进行过滤,使用 having
SELECT
	dept_name ,
	AVG(salary)
	FROM emp WHERE dept_name IS NOT NULL 
	GROUP BY dept_name HAVING AVG(salary) >6000 ;

where 与 having的区别

过滤方式	特点
where	where 进行分组前的过滤，where 后面不能写聚合函数
having	having 是分组后的过滤,having 后面可以写聚合函数

limit关键字

limit 关键字的作用:

limit是限制的意思,用于限制返回的查询结果的行数 (可以通过limit指定查询多少行数据)
limit 语法是 MySql的方言,用来完成分页

语法结构:SELECT 字段1,字段2... FROM 表名 LIMIT offset , length;
limit offset , length; 关键字可以接受一个或者两个为0 或者正整数的参数，offset 起始行数, 从0开始记数, 如果省略则默认为 0.。length 返回的行数

# 查询emp表中的前 5条数据
-- 参数1 起始值,默认是0 , 参数2 要查询的条数
SELECT * FROM emp LIMIT 5;
SELECT * FROM emp LIMIT 0 , 5;

# 查询emp表中 从第4条开始,查询6条
-- 起始值默认是从0开始的.
SELECT * FROM emp LIMIT 3 , 6;

-- 分页操作 每页显示3条数据
SELECT * FROM emp LIMIT 0,3; -- 第1页
SELECT * FROM emp LIMIT 3,3; -- 第2页 2-1=1 1*3=3
SELECT * FROM emp LIMIT 6,3; -- 第三页

-- 分页公式 起始索引 = (当前页 - 1) * 每页条数

SQL约束

约束的作用:对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性.,违反约束的不正确数据,将无法插入到表中
常见的约束:

约束名	约束关键字
主键	primary key
唯一	unique
非空	not null
外键	foreign key

主键约束

主键约束特点：不可重复唯一非空
主键约束作用：用来表示数据库中的每一条记录
添加主键约束：语法格式 字段名字段类型 primary key

# 方式1 创建一个带主键的表
CREATE TABLE emp2(
	-- 设置主键 唯一 非空
	eid INT PRIMARY KEY,
	ename VARCHAR(20),
	sex CHAR(1)
);

-- 方式2 创建一个带主键的表
CREATE TABLE emp2(
	eid INT ,
	ename VARCHAR(20),
	sex CHAR(1),
	-- 指定主键为 eid字段
	PRIMARY KEY(eid)
);

-- 方式3 创建一个带主键的表
CREATE TABLE emp2(
	eid INT ,
	ename VARCHAR(20),
	sex CHAR(1)
)
-- 创建的时候不指定主键,然后通过 DDL语句进行设置
ALTER TABLE emp2 ADD PRIMARY KEY(eid);

通常针对业务去设计主键,每张表都设计一个主键id
主键是给数据库和程序使用的,跟最终的客户无关,所以主键没有意义没有关系,只要能够保证不重复就好,比如身份证就可以作为主键.

# 正常插入一条数据
INSERT INTO emp2 VALUES(1,'宋江','男');
# 插入一条数据,主键为空
-- Column 'eid' cannot be null 主键不能为空
INSERT INTO emp2 VALUES(NULL,'李逵','男');
# 插入一条数据,主键为 1
-- Duplicate entry '1' for key 'PRIMARY' 主键不能重复
INSERT INTO emp2 VALUES(1,'孙二娘','女');

删除主键约束：

-- 使用DDL语句 删除表中的主键
ALTER TABLE emp2 DROP PRIMARY KEY;

主键的自增：主键如果让我们自己添加很有可能重复,通常希望在每次插入新记录时,数据库自动生成主键字段的值.

关键字:AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)
创建主键自增的表：

-- 创建主键自增的表
CREATE TABLE emp2(
-- 关键字 AUTO_INCREMENT,主键类型必须是整数类型
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
);

修改主键自增的起始值：默认地 AUTO_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下面的方式

-- 创建主键自增的表,自定义自增其实值
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
)AUTO_INCREMENT=100;

DELETE和TRUNCATE对自增长的影响

删除表中所有数据有两种方式

清空表数据的方式	特点
DELETE	只是删除表中所有数据,对自增没有影响
TRUNCATE	truncate 是将整个表删除掉,然后创建一个新的表自增的主键,重新从 1开始

非空约束

非空约束的特点: 某一列不予许为空
语法格式：字段名字段类型 not null

为 ename 字段添加非空约束
CREATE TABLE emp2(
	eid INT PRIMARY KEY AUTO_INCREMENT,
	-- 添加非空约束, ename字段不能为空
	ename VARCHAR(20) NOT NULL,
	sex CHAR(1)
);

唯一约束

唯一约束的特点: 表中的某一列的值不能重复( 对null不做唯一的判断 )
语法格式：字段名字段值 unique

#创建emp3表 为ename 字段添加唯一约束
CREATE TABLE emp3(
	eid INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(20) UNIQUE,
	sex CHAR(1)
);

主键约束与唯一约束的区别:

主键约束唯一且不能够为空
唯一约束,唯一但是可以为空
一个表中只能有一个主键 , 但是可以有多个唯一约束

默认值

默认值约束用来指定某列的默认值
语法格式：字段名字段类型 DEFAULT 默认值

-- 创建emp4表, 性别字段默认 女
CREATE TABLE emp4(
eid INT PRIMARY KEY AUTO_INCREMENT,
-- 为ename 字段添加默认值
ename VARCHAR(20) DEFAULT '奥利给',
sex CHAR(1)
);

数据库事务

事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有一条SQL出现异常,整个操作就会回滚,整个业务执行失败
回滚：即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，滚回到事务开始时的状态。（在提交之前执行）

MySQL事务操作

MYSQL 中可以有两种方式进行事务的操作：手动提交事务和自动提交事务

手动提交事务

功能	语句
开启事务	start transaction; 或者 BEGIN;
提交事务	commit;
回滚事务	rollback;

START TRANSACTION ：显式地标记一个事务的起始点。
COMMIT：表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。
ROLLBACK：表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态

手动提交事务流程

执行成功的情况：开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
执行失败的情况：开启事务 -> 执行多条 SQL 语句 -> 事务的回滚

自动提交事务

MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕自动提交事务，
MySQL 默认开始自动提交事务

事务的四大特性 ACID

特性	含义
原子性	每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功，要么都失败。
一致性	事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的总金额是 2000，转账后 2 个人总金额也是 2000.
隔离性	事务与事务之间不应该相互影响，执行时保持隔离的状态.
持久性	一旦事务执行成功，对数据库的修改是持久的。就算关机，数据也是要保存下来的.

MySQL 事务隔离级别

数据并发访问：一个数据库可能拥有多个访问客户端,这些客户端都可以并发方式访问数据库. 数据库的相同数据可能被多个事务同时访问,如果不采取隔离措施,就会导致各种问题, 破坏数据的完整性
并发访问会产生的问题：事务在操作时的理想状态：所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题

并发访问的问题	说明
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中两次读取的数据内容不一致, 要求的是在一个事务中多次读取时数据是一致的. 这是进行 update 操作时引发的问题
幻读	一个事务中,某一次的 select 操作得到的结果所表征的数据状态, 无法支撑后续的业务操作. 查询得到的数据状态不准确,导致幻读

四种隔离级别：通过设置隔离级别,可以防止上面的三种并发问题，MySQL数据库有四种隔离级别上面的级别最低，下面的级别最高。✔ 会出现问题.✘ 不会出现问题

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库的默认隔离级别
1	读未提交	read uncommitted	✔	✔	✔
2	读已提交	read committed	✘	✔	✔	Oracle和SQLServer
3	可重复读	repeatable read	✘	✘	✔	MySql
4	串行化	serializable	✘	✘	✘

隔离级别相关命令

查看隔离级别：select @@tx_isolation;
MySql的默认隔离级别为可重复读（repeatable read），可以防止脏读和不重复读，但是不能防止幻读
设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化

set global transaction isolation level 级别名称;
read uncommitted 读未提交
read committed 读已提交
repeatable read 可重复读
serializable 串行化

修改隔离级别为 读未提交
set global transaction isolation level read uncommitted;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。