数据库MySQL之第一章基础_通过sql可以管理数据库系统-CSDN博客

本文链接：https://blog.csdn.net/dhcgffhd/article/details/125155172

数据库MySQL

一、数据库相关概念

1. MySQL概述
（1）数据库相关概念
在这里插入图片描述通过SQL可以操作数据库管理系统，通过数据库管理系统进而操作数据库以及数据库中的数据。

（2）MySQL安装及启动

MySQL的启动与停止

启动MySQL：

net start mysql80

停止MySQL：

net stop mysql80

客户端连接
方式一：MySQL提供的客户端命令行工具。输入密码即可。
方式二：系统自带的命令行工具执行指令。但需要配置PATH环境变量。

（3）数据模型

关系型数据库（RDBMS）
概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
特点：使用表存储数据，格式统一，便于维护；使用SQL语言操作，标准统一，使用方便。

二、 SQL基础

1. SQL通用语法

SQL语句可以单行或多行书写，以分号结尾。
SQL语句可以使用空格/缩进来增强语句的可读性。
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
注释：单行注释： —注释内容或#注释内容；多行注释/* 注释内容 */

2. SQL分类
在这里插入图片描述
3. DDL语句

(1) 数据库操作

查询

查询所有数据库  SHOW DATABASE;
查询当前数据库  SELECT DATABASE();

创建

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

删除

DROP DATABASE [IF EXISTS] 数据库名;

使用

USE 数据库名;

（2）表操作

查询

查询当前数据库所有表		SHOW TABLES;
查询表结构				DESC 表名;
查询指定表的建表语句		SHOW CREATE TABLE 表名;

创建

CREATLE TABLE 表名(
	字段1	字段1类型[COMMENT 字段1注释],
	字段2	字段2类型[COMMENT 字段2注释],
	字段3	字段3类型[COMMENT 字段3注释],
	...
	字段n	字段n类型[COMMENT 字段n注释]
	)[COMMENT 表注释];

修改

1. 添加字段	ALTER TABLE 表名 ADD 字段名 类型(长度)[COMMENT 注释][约束];

2. 修改字段名和字段类型		ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 注释][约束];

3. 删除字段	ALTER TABLE 表名 DROP 字段名;

4. 修改表名	ALTER TABLE 表名 RENAME TO 新表名;

5. 删除表	DROP TABLE [IF EXISTS] 表名;

6. 删除指定表，并重新创建该表	TRUNCATE TABLE 表名;

4. DML语句

（1）添加数据

1. 给指定的字段添加数据	INSERT INTO 表名(字段1, 字段2, ...) VALUES(值1, 值2, ...);

2. 给全部字段添加数据	INSERT INTO 表名 VALUES(值1, 值2, ...);

3. 批量添加数据		INSERT INTO 表名(字段1, 字段2, ...) VALUES(值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
					INSERT INTO 表名 VALUES(值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

(2) 修改数据

UPDATE 表名 SET 字段名1 = 值1, 字段名 = 值2, ...[WHERE 条件];

(3) 删除数据

DELETE FROM 表名[ WHERE 条件];

3. DQL

(1) 基本查询

查询多个字段

SELECT 字段1, 字段2, 字段3, ... FROM 表名;
SELECT * FROM 表名;

设置别名

SELECT 字段1[AS 别名1], 字段2[AS 别名2]... FROM 表名;

去除重复记录

SELECT DISTINCT 段列表 FROM 表名;

(2) 条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表;

(3) 聚合函数

聚合函数
count：统计数量；max：最大值；min：最小值；avg：平均值；sum：求和。

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

(4) 分组查询

SELECT 字段列表 FROM 表名[WHERE 条件] GROUP BY 分组字段名[HAVING 分组后过滤条件];

where与having的区别
执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以。
执行顺序
where > 聚合函数 > having。

（5）排序查询

SELECT 字段列表	FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

排序方式：ASC（升序）；DESC（降序）。

(6) 分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

起始索引从0开始，起始索引 = （查询页码 - 1） * 每页显示记录数。

4. DCL
（1）用户管理

查询用户

USE mysql;
SELECT * FROM user;

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';      // 所有主机使用 % 代替

修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '	新密码';

删除用户

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

（2）权限控制

查询权限

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

授予权限

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

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

三、函数

1. 字符串函数

采用的字符串函数

2. 数值函数
常用的数值函数

3. 日期函数
常见的日期函数

4. 流程函数
常见的流程函数

四、约束

1. 概述

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
目的：保证数据库中数据的正确、有效性和完整性。

分类：
在这里插入图片描述
2. 外键约束
（1）概念：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
（2）语法

添加外键

CREATE TABLE 表名(
	字段名 数据类型,
	...
	[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCE 主表(主表列表）
);

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCE 主表(主表列表);

删除/更新行为

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCE 主表(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

五、多表查询

1. 多表关系

一对一：用户与用户详情的关系。

在任意一方加入外键，关联另一方的主键，并设置外键为唯一的。

一对多：一个部门对应对个员工，一个员工对应一个部门。

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

多对多：一个学生可以选修多门课程，一门课程也可以供多个学生选择。

建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

2. 多表查询

（1）连接查询

内连接
相当于查询A、B交集部分数据。

- 隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 条件...;

- 显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

外连接
左外连接：查询左表所有数据，以及两张表交集部分的数据。
```
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
```
右外连接：查询右表所有数据，以及两张表交集部分的数据。
```
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;
```
自连接
当前表与自身的连接查询，自连接必须使用表别名。
```
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
```
联合查询
把多次查询结果合并起来，形成一个新的查询结果集。
```
SELECT 字段列表 FROM 表A...
UNION[ALL]
SELECT 字段列表 FROM 表B...;
```

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
UNION ALL 会将全部的数据直接合并在一起，UNION会对合并之后的数据去重。

（2）子查询
SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

标量子查询(子查询结果为单个值)
常用的操作符： = <> > >= < <=
列子查询(子查询结果为一列)
常用的操作符： IN、 NOT IN、 ANY、 SOME、 ALL
行子查询(子查询结果为一行)
常用操作符：=、<>、IN、NOT IN
表子查询(子查询结果为多行多列)
常用操作符：IN

六、事务

1. 事务简介

事务是一组操作的集合，它是不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

2. 事务操作

（1）查看/设置事务提交方式

SELECT @@autocommit;		// 查看事务提交方式，为1是自动提交，为0是手动提交
SET @@autocommit = 0;

（2）提交事务

COMMIT;

（3）回滚事务

ROLLBACK;

（4）开启事务

START TRANSACTION 或 BEGIN;

事务的控制有两种方式：

第一种，关闭掉事务的自动提交，通过COMMIT去提交事务，通过ROLLBACK去回滚事务。
第二种，通过指令START TRANSACTION或BEGIN显示地来开启事务，事务执行完成操作之后，如果事务执行成功，执行COMMIT指令；若失败，执行ROLLBACK。

3. 事务的四大特性

（1）原子性
事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

（2）一致性
事务完成时，必须使所有的数据都保持一致状态。

（3）隔离性
数据库系统提供隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

（4）持久性
事务一旦提交或回滚，它对数据库中的数据改变就是永久的。

4. 并发事务问题
在这里插入图片描述
5. 事务隔离级别

// 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;

// 设置事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}