MySQL数据库初级

最新推荐文章于 2025-08-05 17:47:02 发布

宴客

最新推荐文章于 2025-08-05 17:47:02 发布

阅读量318

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/fytvewrewr/article/details/110716302

MySQL 专栏收录该内容

2 篇文章

订阅专栏

MySQL语句分类

分类	名字	应用
DDL(Data Definition Language)	数据定义语言	建库, 建表
DML(Data Manipulation Language)	数据操纵语言	增, 删, 改
DQL(Data Query Language)	数据查询语言	查询
DCL(Data Control Language)	数据控制语言	用户权限管理

0 SQL其他语句

0.1 SQL注释语句

-- 单行注释
# 注释(MySQL特有)
-- 注释

-- 多行注释
/*
	多行注释
	多行注释
*/

0.2 SQL备份与还原

# DOS中
# 备份
mysqldump -u 用户名 -p 用户密码 数据库 > 数据库文件路径

-- 还原
USE 数据库名;
SOURCE 文件路径;

1 DDL(Data Definition Language)

1.1 DDL操作数据库

1.1.1 创建数据库

-- 直接创建
CREATE DATABASE 数据库名;

-- 判断数据库是否已经存在,若不存在,则创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;

-- 直接创建数据库,并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;

1.1.2 查看数据库

-- 查看所有数据库
SHOW DATABASES;

-- 查看某一数据库的定义信息
SHOW CREATE DATABASE 数据库名;

1.1.3 修改数据库

-- 修改默认字符集
ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;

1.1.4 删除数据库

-- 直接删除
DROP DATABASE 数据库名;

1.1.5 使用数据库

-- 查看当前使用的数据库
SELECT DATABASE();

-- 使用/切换数据库
USE 数据库名;

1.2 DDL操作数据表

1.2.1 创建数据表

创建语句

-- 创建表格式
CREATE TABLE 数据表名 (
	字段名1 字段类型1 约束条件列表1,
	字段名2 字段类型2 约束条件列表2,
	...
);

-- 创建一个与旧数据表结构相同的新数据表
CREATE TABLE 新数据表名 LIKE 旧数据表名;

数据类型

MySQL数据类型
分类	类型名称	取值范围	类型说明
整数	TINIINT	1个字节	微整型
	SMALLINT	2个字节	小整型
	MEDIUMINT	3个字节	中整型
	INT(INTEGER)	4个字节	整型
	BIGINT	8个字节	大整型
小数	FLOAT	4字节	单精度浮点数
小数	DOUBLE	8字节	双精度浮点数
日期	TIME	3	时间类型
	DATA	3	日期类型
	DATATIME	8	日期类型+时间类型
字符串	CHAR(m)	m个字节, m在[0, 255]	固定长度的字符串
字符串	VARCHAR(m)	m个字节, m在[0, 65535]	固定长度的字符串
大二进制	TINYBLOB	[0, 255]字节	微型大二进制文件, 最大255B
	BLOB	[0, 65535]字节	大二进制文件, 最大65KB
	MEDIUMBLOB	[0, 167772150]字节	中型大二进制文件, 最大167MB
	LONGBLOB	[0, 4294967295]字节	大二进制文件, 最大4GB
大文本	TINYTEXT	[0, 255]字节	微型文本
	TEXT	[0, 65535]字节	大二进制文本, 最大65KB
	MEDIUMTEXT	[0, 167772150]字节	中型大二进制文本, 最大167MB
	LONGTEXT	[0, 4294967295]字节	大二进制文本, 最大4GB

1.2.2 查看数据表

-- 查看当前数据库中所有表
SHOW TABLES;

-- 查看数据表结构
DESC 数据表名;

-- 查看创建数据表的SQL语句
SHOW CREATE TABLE 数据表名;

1.2.3 修改数据表

-- 添加字段
ALTER TABLE 数据表名 ADD 字段名 字段类型;

-- 修改字段类型
ALTER TABLE 数据表名 MODIFY 要修改字段名 新字段类型;

-- 修改字段名
ALTER TABLE 数据表名 CHANGE 旧字段名 新字段名 字段类型;

-- 删除字段
ALTER TABLE 数据表名 DROP 字段名;

-- 修改表名
ALTER TABLE 旧数据表名 TO 新数据表名;

-- 修改数据表字符集
ALTER TABLE 数据表名 CHARACTER SET 字符集;

-- 修改主键自增初始值
ALTER TABLE 数据表名 AUTO_INCREMENT = 初始值;

1.2.4 删除数据表

--	直接删除
DROP TABLE 数据表名;

--	条件删除
DROP TABLE IF EXISTS　数据表名；

1.2.5 约束

约束名	关键字	作用
主键	PRIMARY KEY	确定主键, 且每张表只可以有一个主键,自增也只能用在主键上; 相当于非空+唯一
唯一	UNIQUE	数据唯一不可重复, NULL除外
非空	NOT NULL	不可以为NULL
默认值	DEFAULT	设置默认值
外键	FORIGEN KEY	约束其他数据表的是主表, 被约束的是从表
约束检查	CHECK	MySQL中不包含该约束

a 主键自增

-- 主键自增,自增默认初始值为1
-- 需要设置初始值,可以在末尾添加设置初始值语句
CREATE TABLE 数据表名 (
	字段名 数据类型 PRIMARY KEY AUTO_INCREMENT
) AUTO_INCREMENT = 初始值;

b 外键

-- 创建数据表时添加外键
CONSTRAINT 外键约束名称 FOREIGN KEY (外键字段名) REFERENCES 主表名(主键字段名) [联级更新 联级删除];
-- 单独添加外键
ALTER TABLE 从表 ADD CONSTRAINT 外键约束 FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

-- 删除外键
ALTER TABLE 从表 DROP FOREIGN KEY 外键字段名;

-- 联级更新
ON UPDATE CASCADE;

-- 联级删除
ON DELETE CASCADE;

1.2.6 表关系

关系	如何维护
一对一	主表主键与从表主键或唯一外键添加外键约束
一对多(多对一)	主表主键与从表外键添加外键约束
多对多	建立一张中间表来维护, 中间表的外键为多, 两张主表的主键为一

1.2.7 数据库三大范式

范式	特点
第一范式	原子性, 每个字段不可再分割为更小的组成部分
第二范式	不产生局部依赖,每个字段都必须且仅依赖主键
第三范式	不产生依赖传递, 每个字段都必须直接依赖主键

2 DML(Data Manipulate Language)

2.1 数据插入

-- 插入记录
INSERT INTO 数据表名 (字段名1, 字段名2, ...) VALUES (字段1值1, 字段2值1, ...), (字段1值2, 字段2值2, ...), ...;

-- 不写字段
-- 所有的字段都要有值
INSERT INTO 数据表名 VALUES (字段1值1, 字段1值2, ...), (字段2值1, 字段2值2, ...), ...;

-- 复制源数据表数据到目标数据表中
-- 复制全部字段
INSERT INTO 目标数据表 SELECT * FROM 源数据表;

-- 复制部分字段
INSERT INTO 目标数据表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 源数据表;

2.2 数据删除

-- 删除数据表数据
-- 含条件
DELETE FROM 数据表名 WHERE 字段名1 = 字段值1, 字段名2 = 字段值2, ...;
-- 不含条件,清空数据表数据
DELETE FROM 数据表名;

-- 清空数据表(先删除表,再创建空数据表,原有字段保留)
TRUNCATE TABLE 数据表名;

DELETE 和TRUNCATE 的区别
DELETE	1. DELETE对数据进行删除操作 2. 可以指定删除条件 3. AUTO_INCREMENT保留, 继续在之前的主键基础上增长
TRUNCATE	1. TRUNCATE先查询创建数据表语句, 删除整张表后, 再重新创建新表 2. 整张表全部删除 3. AUTO_INCREMENT从初始值开始

2.3 数据修改

-- 不含条件的修改, 所有数据行的该字段都修改
UPDATE 数据表名 SET 字段名1 = 字段值1, 字段名2 = 字段值2, ...;

-- 含条件的修改
UPDATE 数据表名 SET 修改字段名1 = 修改字段值1, 修改字段名2 = 修改字段值2, ... WHERE 条件字段名1 = 条件字段值1, 条件字段名2 = 条件字段值2, ...;

3 DQL(Data Query Language)

3.1 简单查询

3.1.1 查询语句

-- 全部查询
SELECT * FROM 数据表名;

-- 指定字段查询
SELECT 字段名1, 字段名2, ... FROM 数据表名;

3.1.2 指定别名

-- 可以起别名,可以不起
SELECT 字段名1 AS 字段别名1, 字段名2 AS 字段别名2, ... FROM 数据表名 AS 数据表别名;

3.1.3 清除重复值

-- 排除查询结果重复值
SELECT DISTINCT 字段名 FROM 数据表名;

3.1.4 查询运算结果

-- 算式可以包含字段名, 运算符, 数字
SELECT 算式1, 算式2, ... FROM 数据表名;

-- 算式可以作为独立的字段, AS可以省略
SELECT 字段1, 字段2, ..., 算式1 AS 算式别名1, 算式2 AS 算式别名2, ... FROM 数据表名;

3.1.5 条件查询

SELECT 字段名 FROM 数据表名 WHERE 条件;

条件运算
分类	运算符	说明
比较运算符	>, <, >=, <=, =, <>	<>为不等于,MySQL可以使用!=替代
	BETWEEN 数值1 AND 数值2	全闭区间, 相当于数值1 <= 字段值 >= 数值2
	IN(数值1, 数值2, ...)	字段值在IN中即可
	LIKE'_数值%'	模糊查询, %匹配多个字符, _匹配1个字符
	IS NULL	为空
逻辑运算符	&& 或者 AND	与
	\|\| 或者 OR	或者
	! 或者 NOT	非
比较运算符	>, <, >=, <=, =, <>	<>为不等于,MySQL可以使用!=替代

3.1.6 排序

-- 单列排序
-- 升序(默认)
SELECT 字段名 FROM 数据表名 ORDER BY 字段名 ASC;
-- 降序
SELECT 字段名 FROM 数据表名 ORDER BY 字段名 DESC;

-- 组合(多列)排序
-- 字段1升序, 字段1值相等时,字段2按照降序
SELECT 字段名 FROM 数据表名 ORDER BY 字段名1 ASC, 字段名2 DESC, ...;

3.1.7 聚合函数

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

聚合函数名	功能	说明
max(字段名)	求最大值	如果值为NULL, 则不纳入函数
min(字段名)	求最小值
avg(字段名)	求平均值
count(字段名)	计数
sum(字段名)	求和
IFNULL(字段名, 默认值)	处理NULL	如果字段值为null, 则使用默认值替代

3.1.8 分组

SELECT 字段 FROM 数据表名 GROUP BY 分组字段 HAVING 分组条件;

WHERE与HAVING区别
子名	作用
WHERE	1. 对查询结果进行分组前，将不符合where条件的行去掉, 即在分组之前过滤数据，即先过滤再分组。 2. where后面不可以使用聚合函数
HAVING	1. having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤 2. having后面可以使用聚合函数

3.1.9 LIMIT

-- 通常用于实现分页功能
SELECT 字段名列表 FROM 数据表名 WHERE 条件 GROUP BY 分组依据 HAVING 条件 ORDER BY 排序关键词 LIMIT 数据起始, 数据条数;

3.2 多表查询

3.2.1 内连接

-- 内连接
用左表的记录去匹配右表的记录

-- 隐式内连接(看不到JOIN的相关关键词,呗隐藏了起来,因此被叫做隐式)
SELECT 字段名 FROM 左表, 右表 WHERE 条件;

-- 显式内连接(使用了INNER JOIN...ON,没有呗隐藏, 所以是显式)
SELECT 字段名 FROM 左表 INNER JOIN 右表 ON 条件;

3.2.2 外连接

-- 外连接

-- 左外连接(左表记录去匹配右表记录, 左表数据全部显示)
SELECT 字段名 FROM 左表 LEFT OUTER JOIN 右表 ON 条件;

-- 右外连接(右表记录去匹配左表记录, 右表数据全部显示)
SELECT 字段名 FROM USER 左表 OUTER JOIN 右表 ON 条件;

3.2.3 内连接和外连接的区别

内连接: 只显示符合匹配条件的
外连接: 左表(左外连接)或者右表(右外连接)中全部数据都展示,若没有匹配的数据, 则显示为NULL

3.3 子查询

3.3.1 子查询的概念

一个查询结果作为另一个查询语句的条件
查询的嵌套, 内部的查询就是子查询
子查询需要使用括号与调用的查询语句做区分

3.3.2 子查询为单行单列

SELECT 字段名 FROM 数据表 WHERE 字段名 = (子查询);

3.3.3 子查询为多行单列

SELECT 字段名 FROM 数据表 WHERE 字段名 IN (子查询);

3.3.4 子查询为多行多列

SELECT 字段名 FROM (子查询) AS 表别名 WHERE 条件;

4 DCL(Data Control Language)

4.1 用户管理

4.1.1 创建用户

-- 如果是任意主机都可以登录,则主机名使用%通配符
CREATE USER '用户名'@ '主机名' IDENTIFIED BY '密码';

4.1.2 删除用户

DROP USER '用户名'@'主机名';

4.2 权限管理

4.2.1 授予权限

-- 权限包括:CREATE, ALTER, SELECT, INSERT, UPDATE, ALL
-- 匹配所有数据库的所有表,可以使用*.*

GRANT 权限1, 权限2, ... ON 数据库.数据表 TO '用户名'@'主机名';

4.2.2 查询权限

SHOW GRANTS FOR '用户名'@'主机名';

4.2.3 删除权限

-- 规则与3.2.1类似
REVOKE 权限1, 权限2, ... ON 数据库.数据表 FROM '用户名'@'主机名';

4.3 密码管理

4.3.1 管理员密码

# 未登录状态下
-> MYSQLADMIN -uroot -p password 新密码
-> 旧密码

4.3.2 普通用户密码

SET PASSWORD FOR '用户名'@'主机名' = password('新密码');

5 事务

5.1 什么是事务(Transaction)

多个SQL语句组成的一个整体操作被称为事务,其中每条语句执行成功或者失败,都需要继续执行其他SQL语句,以确保数据正确.

5.2 事务提交

5.2.1 手动提交事务

-- 开启事务
START TRANSACTION;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

事务流程

5.2.2 自动提交事务

MySQL中默认每一条DML语句都是一个单独的事务. 开始执行时会开启事务, 语句完成自动提交事务.

-- 查看自动提交事务
SELECT @@AUTOCOMMIT;

-- 取消自动提交(1为自动提交,0为非自动)
SET @@AUTOCOMMIT = 0;

5.3 事务原理

事务原理

客户端连接数据库服务器，创建连接时服务端创建此用户临时日志文件
开启事务以后，所有的操作都会先写入到临时日志文件中
所有的查询操作从表中查询，但会经过日志文件加工后才返回
如果事务提交则将日志文件中的数据写到表中，回滚则清空日志文件。

5.4 回滚点

-- 设置回滚点
SAVEPOINT 回滚点名;

-- 回到回滚点
ROLLBACK TO 回滚点名;

5.4 事务隔离

5.4.1 事务特性

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

5.4.2 事务隔离级别

并发访问产生的问题	含义
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这是事务update时引发的问题
幻读	一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致的，这是insert或delete时引发的问题

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	Read Uncommitted	√	√	√	\
2	读已提交	Read Committed	×	√	√	Oracle&SQL Server
3	可重复读	Repeatable Read	×	×	√	MySQL
4	串行化	Serializable	×	×	×	\

-- 查询MySQL全局事务隔离级别
SELECT @@tx_isolation;

-- 设置MySQL全局事务处理级别
SET GLOBAL TRANSACTION ISOLATION LEVEL 事务级别名;