MySQL 超详细学习教程（附带有保姆级图文安装配置教程）

Augenstern K

已于 2023-12-14 13:56:37 修改

阅读量729

点赞数 2

分类专栏： MySQL 初入职场的程序员必会的技能文章标签：数据库 mysql

于 2023-08-29 09:19:25 首次发布

本文链接：https://blog.csdn.net/weixin_57367513/article/details/132554112

版权

初入职场的程序员必会的技能同时被 2 个专栏收录

10 篇文章 1 订阅

订阅专栏

MySQL

1 篇文章 0 订阅

订阅专栏

数据库

一、分类
- 1、关系型数据库管理系统：RDBMS
- 2、非关系型数据库管理系统：NoSQL
二、数据库的相关概念
三、MySQL的安装
四、连接MySQL服务
五、使用到一款工具 - 数据库可视化工具
六、SQL语言

一、分类

1、关系型数据库管理系统：RDBMS

存储结构化数据
MySQL
Oracle
SQL Server

2、非关系型数据库管理系统：NoSQL

存储非结构化数据
Redis
mongodb
hbase

二、数据库的相关概念

1、DB

database：数据库，存储数据的仓库，存储一系列有组织的数据，是二维表的集合。

2、DBMS

数据库管理系统（database management system），数据库是通过DBMS创建管理的数据容器。

常见的数据库管理系统：MySQL、Oracle、SqlServer（只能安装在windows上）。

分类：

基于共享文件系统的DBMS比如access
基于客户端和服务端的DBMS，比如Mysql,安装数据库一般指安装服务端

3、Table表

二维表由行(记录)和列(字段)构成，行中的记录就是数据，列中对应字段，所以表是行和列的集合。

4、SQL语言

结构化查询语言：Structure Qurey language、专门用来和数据库通信的语言。

优点：

不是某个数据库供应商特有的，几乎所有的DBMS都支持
简单易学
虽然简单，但是可以使用sql语言提供复杂的数据库操作

分类：

DQL数据查询语言
DML数据操纵语言
DDL数据定义语言
DCL数据库控制语言

三、MySQL的安装

在官网下载安装包，双击进行安装。

配置MySQL环境变量：

四、连接MySQL服务

mysql -uusername -ppassword -Pport

五、使用到一款工具 - 数据库可视化工具

给数据库提供了一个可视化操作的界面。
navicat、sqlyog、dbeaver…

六、SQL语言

SQL语言全称是Structured query language，结构化查询语言，是RDBMS提供的用来管理数据库、数据表、表数据的一个工具。

1、DDL语言：数据定义语言-库和表的管理语言

负责创建、删除、修改、查询数据库和数据表

库的管理操作：

创建数据库：create database if not exists database_name charset “编码集”;
修改数据库：改数据库的编码集。
alter database database_name character set “编码集”
查询MySQL中数据库的相关信息
- show databases;
- show create database database_name;
删除数据库
- drop database if exists database_name;
- 数据库一旦删除，数据库中表全部删除了。

-- 1、创建数据库
create database if not exists school_zb  charset 'utf8';
-- 2、修改数据库的编码集
alter database school_zb character set 'gbk';
-- 3、查看数据库的创建细节
show databases;
show create database school_zb;
-- 4、删除数据库
drop database if exists school_zb;

表的管理操作：

创建数据表
- ```
create table if not exists  table_name(
      字段名  字段类型  字段约束   comment  "注释"，
      字段名  字段类型  字段约束   comment  "注释"，
      .......,
      consistent 约束名  foreign key(表字段)  references 表名(字段)
)engine="存储引擎",charset="编码集"
```
- show engines;
- mysql常见的字段类型
  - 整数型
    - tinyint
    - smallint
    - mediumint
    - int/integer
    - bigint
  - 小数型
    - float
    - double(num,num)
  - 字符串类型
    - char(num)
    - varchar(num)
    - text
    - blob/tinyblob
  - 时间日期类型
    - datetime
    - timestrap —— 有取值范围
  - 两种特殊的类型
    - enum
    - set
    - ```
    create table demo(
    	sex enum('男','女'),
    	hobby set('唱歌','jump','rap'),
    	time datetime
    )
    
    insert into demo values('男','jump,rap','2023-10-01');
```
- mysql字段约束：限制数据的格式
  - 外键约束: 从来不加
    - 外键有个特点：外键两张表的关联关系
      主表，从表，主表的主键是从表中外键
    - 如果主表的数据不存在，那么从表无法添加数据
    - 如果从表的数据不删除，那么主表的数据也不能删除
    - foreign key
  - 主键约束：primary key
  - 唯一约束：unique 限制数据再当前列只能出现一次
  - 非空约束：not null 限制数据在添加时，必须添加数据
  - 默认约束： default 值如果在添加数据时，没有添加改字段值，会给我赋予一个默认值
  - 检查约束（mysql8版本以后才生效）：check(条件) 限制数据的填充规范
- 数据库的设计三范式
  - 创建数据表的时候，表的设计规则–业界默认规则。
  - 第一范式1NF：原子性，设计表的时候，表中的字段满足不可再被分割的原则。
  - 第二范式2NF：唯一性，表中的每一行数据在数据表中是唯一的，设置主键，给每一行数据必须得有一个主键来维护它的唯一性，每一行数据的非主键字段必须依赖于主键字段，主键字段一般是和业务字段无关的字段，主键在表中唯一的。
    因为主键是和业务字段无关的，因此主键的值没有特殊要求的，一般唯一即可，所以主键一般都是整数类型的自增字段（标识列）。
  - 第三范式3NF：消除冗余，表中非主键字段必须直接依赖于主键字段，设置外键。
- ```
use school_zb;
-- 6、创建一个院系表：院系id、院系名字、院系领导、院系地址、院系简介、院系专业个数
create table if not exists department(
	department_id int primary key auto_increment,
	department_name varchar(255) not null unique comment "院系名",
	department_leader varchar(255) default "",
	department_address varchar(255) ,
	department_intro text,
	major_number int not null check(major_number > 0)
)engine="innodb",charset='utf8';
-- 7、学生表：学生id、学生姓名、学生年龄、学生学号、学号手机号、学生院系id
create table if not exists student(
	student_id int primary key auto_increment,
	student_name varchar(255) not null,
	student_age int not null check(student_age > 15 and student_age < 150),
	student_sno varchar(255) not null unique,
	stdudent_phone varchar(255) unique,
	student_department_id int,
	constraint fk1 foreign key(student_department_id) references department(department_id)
)engine="innodb",charset='utf8';
```
数据表的查询
- 1、查询某个数据库下的所有数据表：show tables;
- 2、查询表结构： desc table_name;
- 3、查询数据表的创建细节：show create table table_name;
数据表的修改
- alter table table_name rename/change/modify/add/drop …

数据表的删除

drop table if exists table_name;

-- 2、修改表名
alter table department rename dept;
-- 3、修改列名
alter table dept change department_leader leader varchar(255);
-- 4、修改列的约束和列的类型
alter table dept modify department_intro varchar(255) not null;
-- 5、增加一个新列
alter table dept add dept_create_time datetime not null;
-- 6、删除一列
alter table dept drop dept_create_time;

2、DML语言：数据操纵语言–表数据的增加、删除、修改操作

DML主要负责表数据的增加、删除和修改，DML语言执行完成会给我返回一个整数类型的结果，这个结果代表的是数据表受影响的行数。

增加数据语法

1、insert into  table_name(表字段列表)  values(值列表),(值列表)

-- 8、院系表增加数据
insert into department(department_name,department_leader,department_address,department_intro,major_number) values("软件学院","软书记","学院路3号","软件学院是个好学院",1);
insert into department(department_name,department_leader,department_address,department_intro,major_number) values("理学院","理书记","学院路3号","理学院是个好学院",3),("体育学院","体书记","学院路3号","体育学院是个好学院",5);

insert into student() values(1,"kl",21,"00001","1558888888",1);
insert into student() values(2,"hyf",18,"00002","157999999",1);

insert into table(字段列表)   select查询语句

insert into dept(department_name,leader,department_intro,major_number) select department_name,leader,department_intro,major_number from dept;

更改数据语法

update  table_name set  字段名=字段值，字段名=字段值  where 筛选条件

update dept set department_address="学院路4号";//更新所有数据
update dept set department_address="学院路13号" where department_name="软件学院";

删除数据语法
- ```
delete  from table_name  where 筛选条件
truncate table_name  截断表  ————不能用在有外键的表中
```
- 截断表，增加数据时，自增列会从头开始重新自增，delete from table_name会从上一次删除的位置继续自增

3、DQL语言：数据查询语言–查询表数据

单表查询
- ```
select  查询列表
[from table_name]
[where 筛选条件]
[group by 分组字段] [having  分组后的筛选条件]
[order by 排序字段  asc|desc]
[limit   数字,数字]
```
- 1、select子语句中的查询列表：常量、函数、表字段、表达式。
  - ```
  select 1 as "常量",1+1 as "表达式",version() as "函数";
```
- 2、from table_name 从指定的数据表中查询数据，from子语句一旦出现，select子语句中就可以有表字段
  如果要查询表中的所有字段可以在select 后加上一个 * 号，* 号慎用， * 号的效率是最低的。
  - ```
  select department_id,leader, 1 as "常量" from dept;
  select * from dept;
```
- 3、where子语句
  - 一般是和from子语句结合使用，代表从指定的数据表中筛选指定的行数据。
  - 筛选条件：需要是一个返回值为boolean类型的表达式。
    - 条件表达式
      - < > <= >= = !=
      - 字段名条件表达式符号值
    - 逻辑表达式
      - and or in not in is null is not null
        between a and b
    - 模糊查询
      - 筛选数据时，我们不清楚数据的完整的内容，只知道数据中部分内容，使用到模糊查询来筛选指定的数据。
      - 字段名 like “模糊条件”
      - 两个特殊的字符： %0个或者多个字符 _一个字符
      - 自定义转义字符 escape
  - ```
  select * from dept where major_number >= 3;
  select * from dept where department_name="软件学院";
  select * from dept where department_name="软件学院" or department_name="艺术学院";
  select * from dept where department_name in ("软件学院","体育学院");
  select * from dept where department_address="学院路13号" and major_number=4;
  select * from dept where leader is null;
  select * from dept where leader is not null;
  select * from dept where department_name like "%a%";
  select * from dept where department_name like "_u%";-- 院系第二个字符为u
  select * from dept where department_name like "___";-- 院系由三个字符组成
  select * from dept where department_name like "_a_%"escape"a";-- 院系第二个字符是下划线
```
- 4、group by分组查询
  - 就表中的每一行的数据以指定的字段划分为不同的数据组，当指定的字段值一致时候，划分为同一个组中。
  - 一旦在MySQL中使用了分组查询，那么select的查询列表就不能随便写，只能写分组字段、常量、聚合函数。
  - ```
  select sex,"1" as "常量",count(1) from user group by sex;
  select sex,max(age),count(1) as "数量"  from user group by sex;
```
- 5、having分组后的筛选
  - 筛选结果数据，用来表示哪些数据需要展示，哪些数据不需要展示。
  - 分组前的筛选having都能做，只不过having使用场景更加倾向于只有分组后才能筛选的情况。
  - ```
  select sex,max(age),count(1) as "人数" from user group by sex having max(age) >28;
```
- 6、order by排序
  - order by 排序字段 asc|desc，排序字段 asc|desc
  - ```
  select * from user order by age asc,user_name asc;
```
- 7、limit 分页查询
  - limit n
  - limt offset,n —— offset=(页码-1)*n
- 【注意】
  - 1、select查询语句查询回来的是一个虚拟二维表格，二维表格也是有表头的，表头我们是可以起别名的
  - 2、单表查询的SQL语句的编写顺序select—where–group by–having–order by --limit
    单表查询的SQL语句的执行顺序from—where—group by–having–select—order by —limit
函数
- 查看MySQL自带的所有函数
  - show function status;
- UDF：一对一函数，输入一个数据，输出一个数据
  - 字符函数
    - substring/substr —— 字符串截取函数
    - concat(str…) concat如果拼接了null值，返回结果也是null —— 字符串拼接
    - lpad(str,length,padstr)|rpad() —— 左填充|右填充
    - trim(str) —— 去除字符串两端的空格
    - length(str) —— 返回字符串长度
    - upper|lower —— 转字符串为全大写或全小写
    - replace(str，需要替换的字符串，替换之后的字符串)
    - ```
    select substring("zs123",2,3);
    select concat("zs","ls");
    select rPAD("zs",5,"ws");
    select trim("   zs   ls    ");
    select length("asdfghjklzxvbnm");
    select user_name,length(user_name) as "length" from user;
    select upper("cat");
    select lower("CAT");
    select replace("zs","s","l");
```
- 数学函数
  - ceil(数字) —— 向上取整
  - floor(数字) —— 向下取整
  - abs(数字) —— 取绝对值
  - round(数字，n) —— 四舍五入
  - sqrt(数字) —— 求开平方
  - pow(数字，n次方) —— 求平方
  - ```
  select ceil(1.5);
  select floor(1.5);
  select abs(-1);
  select round(3.1415926,2);
  select sqrt(9);
  select pow(3,2);
```
  - 时间日期函数
    - now(); —— 获取当前月日时分秒
    - current_date(); —— 获取当前年月日
    - current_time(); —— 获取当前时分秒
    - YEAR(时间) —— 获取年份
    - str_to_date(str,formatstr) —— 将字符串时间转换为date的时间
    - DATE_FORMAT(date,formatstr) —— 将date以指定的格式转为想要的格式
    - datediff（date，date）—— 日期相减
    - ```
    select now();
    select current_date();
    select current_time();
    select year(now());
    select str_to_date("2023/10/11","%Y/%m/%d");
    select DATE_FORMAT(now(),"%Y-%m-%d");
```
- 其他函数
  - version() —— 显示当前MySQL的版本
  - user() —— 显示当前登录的用户
  - database() —— 显示当前使用的数据库
  - ```
  select version(),user(),database();
```
  - 流程函数
    - if(判断条件，表达式或者值，表达式或者值)
    - case 要匹配的值
      when 值 then 返回的结果
      …
      else 默认的返回
      end as “别名”
    - ```
    select user_id,user_name,password,age,if(sex='1','男','女') as "sex" from user;
    
    select user_id,user_name,password,age,
    CASE sex
    	WHEN '0' THEN'女'
    	WHEN '1' THEN'男'
    	ELSE '未知'
    	END as "sex"
    from user;
```
- UDAF：聚合函数，输入多个数据，输出一个数据
  - sum
  - count
  - avg
  - max
  - min
- UDTF：输入一个数据，输出多个数据
联合查询
- 将多条查询语句的结果整合到一个结果集当中。
- 查询语句1 union|union all 查询语句2 …
- 注意事项：
  - 联合查询要求多个查询语句的返回的列数必须是相同的。
  - 联合查询最后得到的结果集的表头是第一个查询语句的表头。
  - union不会保留重复性的数据，union all会保留重复性的数据。
- ```
select "男" as sex,count(1) as count_people from user where sex='1'
union
select "女",count(1) as count_people from user where sex='0'
union
select "未知",count(1) as count_people from user where sex='2';

select * from user where age<30
union all
select * from user where sex='0';
```
多表查询(连接查询)
- 查询的数据来自于多张表，而且多张表一般都是有外键关系的。
- 语法：
  - ```
  select  查询列表
  from table_nameA
  inner join|left join|right join|full join   table_nameB
  on  a表和b表的连接条件（a表和b表的关联字段）
  ..........
  where   group by  having  order by  limit
```
- 连接查询分类
  - 内连接查询：只会保留两张表的匹配数据，不匹配数据全部删除。
  - 外连接查询：
    - 左外连接查询：会保留table_nameA表的所有数据，右表只会保留匹配数据。左表中不存在右表信息会使用null值填充
    - 右外连接查询：会保留table_nameB表的所有数据，左表只会保留匹配数据。
    - 全外连接查询：会保留左表和右表的所有数据。在MySQL中无法实现。
- ```
-- 内连接查询 查询部门存在的员工信息和员工的部门名
select e.*,d.department_name from employees as e inner join departments as d on e.department_id = d.department_id;
```
- 【注意】笛卡尔乘积问题，连接查询的时候忘记写连接条件

子查询

查询里面有嵌套了一个查询，其中嵌套的查询称之为子查询。
子查询可以出现select、where、from子语句当中。

-- 查询部门名为IT的所有员工信息
select employees.* from employees where department_id in (select department_id from departments where department_name="IT");

4、DCL – TCL语言：数据控制语言–负责数据库的事务操作

事务

含义：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。
官方解释：事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行。
事务的ACID属性和特点
- 原子性（Atomicity）
  原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
- 一致性（Consistency）
  事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
- 隔离性（Isolation）
  事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
- 持久性（Durability）
  持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

事务的创建

分类
- 隐式事务—自动提交事务
  - MySQL中默认所有的事物都是隐式事物
    隐式事物的特点：
    1、事务没有明显的开启和关闭的标记
    2、默认情况一个SQL语句就是一个事物
  - 事务没有明显的开启和关闭的标记
    比如insert、update、delete
  - show VARIABLES like ‘%autocommit%’
- 显示事务—手动提交事务
  - 事务有明显的开启和结束的标记
    前提：关闭自动提交事务
  - set autocommit = 0 仅针对于本次会话有效
步骤
- 1、set autocommit = 0;
- 2、start transaction;可选的
- 3、sql语句(包含select insert update delete)
- 4、commit;关闭事务
  5、rollback;回滚事务（二选1）
【补充】：事务回滚对delete和truncate的区别：delete可以回滚，truncate不能回滚
【补充】：savepoint保存回滚点的演示
设置方式：savepoint 节点名；
使用方式：rollback to 节点名

事务的隔离级别

原因
- 对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题。
- 脏读
- 幻读
- 不可重复读
解决方法：使用事务的隔离机制
- 概念：
  数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。
  一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。
- 分类：
  - Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ。
  - 查看隔离级别的命令：
    旧版本：select @@tx_isolation
    新版本：@@transaction_isolation
  - 设置当前数据库的隔离级别：set session transaction isolation level 隔离级别。
  - 设置数据库系统的全局的隔离级别: set global transaction isolation level read committed;
- 通过cmd命令行开启多个事务演示