MySQL——基础篇

2023-12-21 06:28:37

学习视频链接:https://www.bilibili.com/video/BV1Kr4y1i7ru/?spm_id_from=333.999.0.0&vd_source=619f8ed6df662d99db4b3673d1d3ddcb

前言??

基础篇——MySQL概述、SQL、函数、约束、多表查询、事务

进阶篇——存储引擎、索引、SQL优化、视图/存储过程/触发器、锁、InnoDB核心、MySQL管理

运维篇——日志、主从赋值、分库分表、读写分离

一、基础篇

1. MySQL概述🍊

数据库(DataBase, DB):存储数据的仓库,数据在其中是有组织存储的

|

数据库管理系统(DataBase Management System, DBMS):操作和管理数据库的软件

|

SQL(Structured Query Language):操作数据库的语言,一套标准

主流的关系型数据库

在这里插入图片描述

1.1 安装及启动

安装

在这里插入图片描述

启动及停止

在这里插入图片描述

客户端进行连接

  • 开始菜单——MySQL Client
  • 命令行——mysql [-h 127.0.0.1] [-P 3306] -u root -p

数据模型

在这里插入图片描述

数据库分类

  • 关系型数据库(RDBMS):建立在关系模型基础上,是多张相互连接的二维表组成的数据库
  • 非关系型数据库:不是表结构存储的

1.2 SQL🍌

1.2.1 通用语法

注意事项:

  • SQL语句单行或多行书写,以分号结尾
  • SQL语句可以使用空格或缩进来增强语句的可读性,空格和缩进不限个数
  • MySQL数据库的SQL语句不区分大小写,关键字一般大写
  • 注释 --注释内容 #注释内容 /注释内容/

分类:

  • DDL(Data Definition Language, 数据定义语言)——定义/创建数据库对象(数据库,表,字段
  • DML(Data Manipulation Language, 数据操作语言)——增删改表中的数据
  • DQL(Data Query Language, 数据查询语言)——查询数据库中表的记录
  • DCL(Data Control Language, 数据控制语言)——创建数据库用户、控制数据库的访问权限
1.2.2 DDL

1)DDL-数据库操作

-- 查询所有的数据库
SHOW DATABASES;
-- 查询当前数据库
SELECT DATABASE();
# 创建  utf8mb4
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
# 删除
DROP DATABASE [IF EXISTS] 数据库名;
# 使用
USE 数据库名

2)DDL-表查询

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

3)DDL-表创建

 create table tb_user(
    -> id int comment '编号',
    -> name varchar(50) comment '姓名',
    -> age int comment '年龄',
    -> gender varchar(1) comment '性别'
    -> )comment '用户表';
  
creat table 表名(
 字段名 字段类型 [comment 字段注释],
 ……
 字段名 字段类型 [comment 字段注释]
)[comment 表注释];

字段类型

数值类型

在这里插入图片描述

字符类型

在这里插入图片描述

日期时间类型

在这里插入图片描述

4)DDL-表操作-修改

# 字段
-- 添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
-- 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
-- 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
-- 删除
ALTER TABLE 表名 DROP 字段名;
# 表
-- 修改表名
ALTER TABLE 表名 RENAME TO 新表名;
-- 删除
DROP TABLE [IF EXISTS] 表名;
-- 删除指定表,并重新创建该表,保留表结构
TRUNCATE TABLE 表名;
1.2.3 DML
-- 添加数据 字符串和日期类型要用引号引起来
INSERT
# 给指定字段添加数据
INSERT INTO 表名(字段名1, 字段名2,……) VALUES (值1, 值2, ……);
# 给全部数据添加字段
INSERT INTO 表名 VALUES (值1, 值2, ……);
# 批量添加数据
INSERT INTO 表名(字段名1, 字段名2,……) VALUES (值1, 值2, ……), (值1, 值2, ……), (值1, 值2, ……);
INSERT INTO 表名 VALUES (值1, 值2, ……), (值1, 值2, ……), (值1, 值2, ……);


-- 修改数据;如果不加条件,就会修改整张表的所有数据。
UPDATE
UPDATE 表名 SET 字段名1=值1, 字段名2=值2, … [WHERE 条件];
-- 删除数据
DELETE
DELETE FROM 表名 [where 条件];

DELETE不能删除某个字段的值,可以使用UPDATE,使用UPDATA修改表中数据为NULL

1.2.4 DQL

语法

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

DQL-基本查询

# 查询多个字段
SELECT 字段1, 字段2, …… FROM 表名;
SELECT * FROM 表名;  # 效率低,不直观

# 设置别名
SELECT 字段1 [AS 别名1], 字段1 [AS 别名1], …… FROM 表名;

# 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;

DQL-条件查询

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

在这里插入图片描述

聚合函数

  • 概念: 将一列数据作为一个整体,进行纵向计算

  • 常见聚合函数: count——统计数量

    ? max——最大值

    ? min——最小值

    ? avg——平均值

    ? sum——求和

    注意:所有聚合函数不计算null值

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

分组查询

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
/*
where 和 having 的区别
1. 执行时机:where是分组之前进行过滤,不满足where条件,不参与分组;having是分组之后对结果进行过滤
2. 判断条件不同:where不能对聚合函数进行判断,having可以
*/

-- 注意
-- 执行顺序:where > 聚合函数 > having
-- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无意义

排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2, ……;
# 排序方式
ASC  升序
DESC  降序
# 第一个字段排序后相等,才会执行第二个字段的排序

分页查询

# 语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
# 起始索引是0开始, 起始索引=(查询页码-1)*每页显示记录数
# 如果查询的是第一页的数据,起始索引可以省略

DQL-执行顺序

在这里插入图片描述

起别名要在执行顺序之后才能使用

1.2.5 DCL

哪些用户可以访问,每个用户有访问数据库的什么权限

DCL-管理用户:SQL开发人员操作少,主要是DBA(Database Administrator 数据库管理员)使用

# 1. 查询用户
USE mysql;
SELECT * FROM user;

# 2. 创建用户  %表示任意主机,localhost表示本机
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

# 3. 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

# 4. 删除用户
DROP USER '用户名'@'主机名';

DCL-权限控制

在这里插入图片描述

# 1. 查询权限
SHOW GRANTS FOR '用户名'@'主机名';

# 2. 授予权限  所有数据库的表*.*
# 权限之间用逗号分隔
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

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

1.3 函数🥭

概念: 一段可以直接被调用的代码或程序

1.3.1 字符串函数

在这里插入图片描述

1.3.2 数值函数

在这里插入图片描述

1.3.3 日期函数

在这里插入图片描述

select now();  # 2023-12-20 08:57:50
select curtime();  # 08:59:47
select YEAR(now());  # 2023
select MONTH(now());  # 12
select day(now());  #20

select date_add(now(), INTERVAL 70 YEAR);  # 2093-12-20 09:02:30
select date_add(now(), INTERVAL 70 MONTH);  # 2029-10-20 09:02:42
select date_add(now(), INTERVAL 70 DAY);  # 2024-02-28 09:02:53

select datediff('2023-1-10', '2023-1-1');  # 9
1.3.4 流程函数

实现条件筛选,从而提高语句的效率

在这里插入图片描述

create table score
(
    id int comment '编号',
    name varchar(50) comment '姓名',
    math double comment '数学成绩',
    english double comment '英语成绩',
    chinese double comment '语文成绩'
) comment '学员成绩表';

insert into score values (1, 'TOM', 67, 88, 95), (2, 'Rose', 23, 66, 90), (3, 'Jack', 56, 98, 76);

show create table score;
show tables;

select * from score;

select
    id,
    name,
    (case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end) as 'math',
    (case when english >= 85 then '优秀' when english >= 60 then '及格' else '不及格' end) as 'english',
    (case when chinese >= 85 then '优秀' when chinese >= 60 then '及格' else '不及格' end) as 'chinese'
from score;

1.4 约束🍓

概念: 作用于表中字段上的规则,用于限制存储在表中的数据

目的: 保证数据库中数据的正确、有效和完整

时机: 创建表/修改表的时候添加约束

分类: 在这里插入图片描述

自动增长:AUTO_INCREMENT

create table user (
  id int primary key auto_increment comment '主键',
  name varchar(10) not null unique comment '姓名',
  age int check ( age >0 and age <= 120 ) comment '年龄',
  status char(1) default '1' comment '状态',
  gender char(1) comment '性别'
) comment '用户表';

-- 什么情况下会使用那个默认值呢??  不写要插入这个字段
-- 插入数据
insert into user(name, age, status,gender) values ('Tom1', '19', '1', '男'), ('Tom2', '20', '2', '男');
insert into user(name, age,status,gender) values('Tom3','80','1','男');  # 上面语句执行了两次,这条记录的编号是从5开始的
insert into user(name, age,status,gender) values(null,'80','1','男');  # null不占用编号
insert into user(name, age,status,gender) values('Tom4','80','1','男');
# 外键约束
CREATE TABLE 表名(
    字段名 数据类型,
    ……
    [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名);
);

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

在这里插入图片描述

1.5 多表查询🍒

1.5.1 多表关系

一对多(多对一): 在多的一方建立外键,与少的一方的主键连接

多对多: 建立第三张中间表,中间表至少包含两个外键,分别关联两个主键

一对一: 用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,提升操作效率。在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)

1.5.2 多表查询概述

从多张表中查询数据——消除无效的笛卡尔积

需要限定条件where

1.5.3 内连接

查询A、B交集部分数据

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

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

-- 显式内连接减少扫描,速度更快

1.5.4 外连接

左外连接:查询左表所有数据,以及两张表交集部分数据

右外连接:查询右表所有数据,以及两张表交集部分数据

# 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件……;

# 右外连接
SELECT 字段列表 FROM 表1 RIGHT[OUTER] JOIN 表2 ON 条件……;

-- 换一下表的位置就无所谓左外还是右外连接

1.5.5 自连接

当前表与自身的连接查询,自连接必须使用表别名

SELECT 字段列表 FROM 表1 别名1 JOIN 表1 别名2 ON 条件……;

1.5.6 联合查询

把多次查询的结果合并起来,形成一个新的查询结果集

# UNION 去重 UNION ALL 不去重
# 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致
SELECT 字段列表 FROM 表A……
UNION/UNION ALL 
SELECT 字段列表 FROM 表B……;
/*
* 为什么不用or
  1. 联合查询效率更高,会使索引失效?
  2. 一张表or方便,多张呢?
*/

1.5.7 子查询

概念: SQL语句中嵌套SELECT语句,称为嵌套查询 ,又称子查询

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

根据子查询结果分类

  • 标量子查询(子查询结果是单个值)

    # 子查询返回的结果是单个值(数字、字符串、日期等)
    # 常用操作符
    > >= < <= <> =
    
  • 列子查询(子查询结果为一列)

    # 常用操作符
    IN——在指定的集合范围之内
    NOT IN——不在指定的集合范围之内
    ANY——子查询返回列表中,有任意一个满足即可
    SOME——和ANY同
    ALL——子查询返回列表的所有值都必须满足
    
  • 行子查询(子查询结果为一行)

    # 常用操作
    =
    <>
    IN
    NOT IN
    SELECT * FROM emp WHERE (列1,列2) = (SELECT 列1, 列2 FROM ……)
    
  • 表子查询(子查询结果为多行多列)

    # 常用操作
    IN
    

根据子查询位置

  • WHERE之后
  • FROM之后
  • SELECT之后

1.6 事务🫐

1.6.1 事务简介

一组操作的集合,不可分割,要么同时成功,要么同时失败。——事务会把所有的操作作为一个整体一起向系统提交或撤销操作

默认MySQL的事务是自动提交的,也就是当执行一条DML语句,MySQL会立即隐式地提交事务

1.6.2 事务操作

方式一:修改事务的提交方式

# 查看/设置事务提交方式
SELECT @@autocommit;  # 查看当前事务的提交方式
SET @@autocommit=0;  # 0表示手动提交;1表示自动提交
# 提交事务
COMMIT;
# 回滚事务
ROLLBACK;
# 无论正确执行还是错误执行,提交了之后就不能回滚回去了;只要没提交就能回滚。
# 一般执行没问题就提交,执行出错就回滚。

方式二:手动开启事务

# 开启事务
START TRANSACTION 或 BEGIN;
# 提交事务
COMMIT;
# 回滚事务
ROLLBACK;
-- ---------------------------- 事务操作 ----------------------------
-- 数据准备
create table account(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    money int comment '余额'
) comment '账户表';
insert into account(id, name, money) VALUES (null,'张三',2000),(null,'李四',2000);


-- 恢复数据
update account set money = 2000 where name = '张三' or name = '李四';
#
#
select @@autocommit;
#
set @@autocommit = 0; -- 设置为手动提交

-- 转账操作 (张三给李四转账1000)
-- 1. 查询张三账户余额
select * from account where name = '张三';

-- 2. 将张三账户余额-1000
update account set money = money - 1000 where name = '张三';

...  # 不知道这里为什么加.就会程序出错,希望以后学完回来能补充起来

-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';


-- 提交事务
commit;

-- 回滚事务
rollback ;



-- 方式二
-- 转账操作 (张三给李四转账1000)
start transaction ;

-- 1. 查询张三账户余额
select * from account where name = '张三';

-- 2. 将张三账户余额-1000
update account set money = money - 1000 where name = '张三';

程序执行报错 ...

-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';


-- 提交事务
commit;

-- 回滚事务
rollback;

1.6.3 事务四大特性

事务的四大特性:

  • 原子性(Atomicity): 事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
  • 一致性(Consistency): 事务完成时,必须使所有的数据都保持一致状态。
  • 隔离性(Isolation): 数据库系统提供的隔离机制 ,保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性(Durability): 事务一旦提交或回滚,它对数据库中数据的改变是永久的。

1.6.4 并发事务问题

问题:

  • 脏读: 一个事务读到另一个事务还没提交的数据
  • 不可重复读: 一个事务先后读取同一条记录,但两次读取的数据不同
  • 幻读: 一个事务按照条件查询数据时,没有对应行,但是插入数据时,又发现这行数据已经存在

1.6.5 事务隔离级别

解决并发事务问题

级别分类:

在这里插入图片描述

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

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

数据隔离级别越高,数据越安全,性能越低。

总结

在这里插入图片描述

文章来源:https://blog.csdn.net/qq_44689664/article/details/135115731
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。