MySql单表&约束&事务_mysql查看部门编号为 03 的部门名称,要求列名用中文显示-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_46976404/article/details/117757016

这篇博客详细介绍了MySQL中的DQL操作，包括排序、聚合函数、分组和LIMIT关键字。接着讲解了SQL约束，如主键、非空、唯一和外键约束，并详细阐述了主键自增的设置和管理。此外，还深入讨论了数据库事务的概念，包括其四大特性，并展示了如何在MySQL中手动和自动提交事务。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.DQL操作单表

1.1排序

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

SELECT 字段名 FROM 表名 [WHERE 字段 = 值] ORDER BY 字段名 [ASC / DESC]
ASC表示升序(默认)  desc表示降序排序

排序方式

只按照某一个字段进行排序, 就是单列排序

  -- 默认升序排序 ASC 
  SELECT * FROM emp ORDER BY salary; 

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

组合排序同时对多个字段进行排序, 如果第一个字段相同就按照第二个字段进行排序,以此类推
```
  -- 组合排序 
  SELECT * FROM emp ORDER BY salary DESC, eid DESC;
```

1.2聚合函数

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对某一列的值进行计算，然后返回一个单一的值(另外聚合函数会忽略null空值。)；

SELECT 聚合函数(字段名) FROM 表名;

count(字段) 统计指定列不为NULL的记录行数
-- 统计表中的记录条数 使用 count() 
SELECT COUNT(eid) FROM emp; 
-- 使用某一个字段 
SELECT COUNT(*) FROM emp; 
-- 使用 * 
SELECT COUNT(1) FROM emp; 
-- 使用 1,与 * 效果一样
sum(字段) 计算指定列的数值和
max(字段) 计算指定列的最大值
min(字段) 计算指定列的最小值
avg(字段) 计算指定列的平均值
-- sum函数求和, max函数求最大, min函数求最小, avg函数求平均值 
SELECT
	SUM(salary) AS '总薪水',
	MAX(salary) AS '最高薪水', 
	MIN(salary) AS '最低薪水', 
	AVG(salary) AS '平均薪水' 
FROM emp;

1.3分组

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

SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 [HAVING 条件];

#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;

#4.查询平均薪资大于6000的部门.
SELECT
	dept_name , 
	AVG(salary) 
FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name HAVING AVG(salary) > 6000 ;

where 与 having的区别

  where 进行分组前的过滤
  	   后面不能写 聚合函数
  having 是分组后的过滤
  	   后面可以写 聚合函数

1.4limit关键字

limit 关键字的作用

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

语法

SELECT 字段1,字段2... FROM 表名 LIMIT offset , length;

参数

SELECT 字段1,字段2… FROM 表名 LIMIT offset , length;

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) * 每页条数 

-- limit是MySql中的方言

SQL约束

1.约束的作用

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

2.常见的约束

主键 primary key
唯一 unique
非空 not null
外键 foreign key

2.1主键约束

不可重复唯一非空，用来表示数据库中的每一条记录

####2.1.1添加主键约束 ####
字段名字段类型 primary key

# 方式1 创建一个带主键的表 
CREATE TABLE emp2( 
	-- 设置主键 唯一 非空 
	eid INT PRIMARY KEY,
	ename VARCHAR(20), 
	sex CHAR(1) 
);
-- 删除表 
DROP TABLE emp2; 
-- 方式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);

DESC 查看表结构

-- 查看表的详细信息 
DESC emp2;

哪些字段可以作为主键 ?

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

2.1.2删除主键约束

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

2.1.3主键的自增

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

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

创建主键自增的表

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

INSERT INTO emp2(ename,sex) VALUES('张三','男'); 
INSERT INTO emp2(ename,sex) VALUES('李四','男'); 
INSERT INTO emp2 VALUES(NULL,'翠花','女'); 
INSERT INTO emp2 VALUES(NULL,'艳秋','女');

2.1.4修改自增主键的起始值

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

-- 创建主键自增的表,自定义自增其实值 
CREATE TABLE emp2( 
	eid INT PRIMARY KEY AUTO_INCREMENT, 
	ename VARCHAR(20), 
	sex CHAR(1) 
)AUTO_INCREMENT=100; 
-- 插入数据,观察主键的起始值 
INSERT INTO emp2(ename,sex) VALUES('张百万','男'); 
INSERT INTO emp2(ename,sex) VALUES('艳秋','女');

2.1.5 DELETE和TRUNCATE对自增长的影响

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

DELETE 只是删除表中所有数据,对自增没有影响

truncate 是将整个表删除掉,然后创建一个新的表自增的主键,重新从 1开始

2.2非空约束

非空约束的特点: 某一列不予许为空

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

2.3唯一约束

唯一约束的特点: 表中的某一列的值不能重复( 对null不做唯一的判断 )

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

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

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

2.4外键约束

外键指的是在从表中与主表的主键对应的那个字段
使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性
多表关系中的主表和从表
主表: 主键id所在的表, 约束别人的表
从表: 外键所在的表多, 被约束的表

创建外键约束

--新建表时添加外键
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
--已有表添加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名);
--已有表添加外键  简化 不写外键约束名自动添加外键约束名称
ALTER TABLE 从表 ADD FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名);

删除外键约束

alter table 从表 drop foreign key 外键约束名称

注意:

从表外键类型必须与主表主键类型一致否则创建失败.
报错 Connot add foreign key constraint
添加数据时, 应该先添加主表中的数据
删除数据时,应该先删除从表中的数据
报错 Cannot delete or update a parent row: a foreign key constraint fails不能删除主表的这条数据,因为在从表中有对这条数据的引用

级联删除操作

如果想实现删除主表数据的同时,也删除掉从表数据,可以使用级联删除操作

级联删除 
ON DELETE CASCADE

级联删除如果要在删除时生效需要在创建表时添加级联删除，删除主表中数据的同时会把从表中数数据删除

2.5 默认值

默认值约束用来指定某列的默认值

字段名 字段类型 DEFAULT 默认值
-- 创建带有默认值的表 
CREATE TABLE emp4( 
	eid INT PRIMARY KEY AUTO_INCREMENT, 
	-- 为ename 字段添加默认值 
	ename VARCHAR(20) DEFAULT '奥利给', 
	sex CHAR(1) 
);

数据库事务

3.1什么是数据库事务

事务是一个整体,由一条或者多条SQL语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有一条SQL出现异常,整个操作就会回滚,整个业务执行失败

回滚

即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，滚回到事务开始时的状态。（在提交之前执行）

3.2MySql的事务操作

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

3.2.1手动提交事务

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

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

手动提交事务流程

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

开启事务(start transaction)后，执行常规的语句比如新增、修改或者删除的操作。如果没有提交(commit)事务，数据库的内容是不会发生改变的，只有commit之后才会变更数据库的内容，如果在提交事务之前发生异常可以通过rollback回滚到事务开始的地方

注意:

如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。如果事务中 SQL 语句有问题，rollback 回滚事务，会回退到开启事务时的状态。

3.3.2自动提交事务

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

MySQL默认是自动提交事务

取消自动提交

MySQL默认是自动提交事务,设置为手动提交.
1)登录mysql，查看autocommit状态
```
  SHOW VARIABLES LIKE 'autocommit';
```

把 autocommit 改成 off
```
 SET @@autocommit=off;
```

修改之后的执行DML操作需要手动提交

3.4事务的四大特性

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

各个事务之间的隔离的，如果多个事务对数据库的同一批数据进行并发访问的时候，可以通过设置不同的隔离级别来解决对应的问题

查看隔离级别

select @@tx_isolation;

并发访问的问题

脏读：一个事务读取到了另一个事务没有提交的数据
不可重复读: 一个事务两次读取到的数据不一致
幻读：一个事务，一次查询的结果无法支持后续的业务操作

设置隔离级别

set global transaction isolation level 级别名称;
read uncommitted 读未提交  无
read committed 读已提交   可以防止:脏读 （Orace默认）
repeatable read 可重复读 可以防止:脏读，不可重复读 （MySql默认）
serializable 串行化      可以防止:脏读，不可重复读，幻读

注意:隔离级别从小到大安全性是越来越高的，但是效率是越来越低的，根据不同的情况选择对应的情况