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

一、分类

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;
1
2
3
4
5
6
7
8
9

表的管理操作：

• 创建数据表

  • create table if not exists  table_name(
          字段名  字段类型  字段约束   comment  "注释"，
          字段名  字段类型  字段约束   comment  "注释"，
          .......,
          consistent 约束名  foreign key(表字段)  references 表名(字段)
    )engine="存储引擎",charset="编码集"
    1
    2
    3
    4
    5
    6

  • 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');
        1
        2
        3
        4
        5
        6
        7

  • 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
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20

• 数据表的查询

  • 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;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

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

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

• 增加数据语法

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

  • -- 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);
    1
    2
    3
    4
    5
    6

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

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

• 更改数据语法

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

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

• 删除数据语法

  • delete  from table_name  where 筛选条件
    truncate table_name  截断表  ————不能用在有外键的表中
    1
    2

  • 截断表，增加数据时，自增列会从头开始重新自增，delete from table_name会从上一次删除的位置继续自增

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

• 单表查询

  • select  查询列表
    [from table_name]
    [where 筛选条件]
    [group by 分组字段] [having  分组后的筛选条件]
    [order by 排序字段  asc|desc]
    [limit   数字,数字]
    1
    2
    3
    4
    5
    6

  • 1、select子语句中的查询列表：常量、函数、表字段、表达式。

    • select 1 as "常量",1+1 as "表达式",version() as "函数";
      1

  • 2、from table_name 从指定的数据表中查询数据，from子语句一旦出现，select子语句中就可以有表字段
    如果要查询表中的所有字段 可以在select 后加上一个 * 号，* 号慎用， * 号的效率是最低的。

    • select department_id,leader, 1 as "常量" from dept;
      select * from dept;
      1
      2

  • 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";-- 院系第二个字符是下划线
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11

  • 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;
      1
      2

  • 5、having分组后的筛选

    • 筛选结果数据，用来表示哪些数据需要展示，哪些数据不需要展示。

    • 分组前的筛选having都能做，只不过having使用场景更加倾向于只有分组后才能筛选的情况。

    • select sex,max(age),count(1) as "人数" from user group by sex having max(age) >28;
      1

  • 6、order by排序

    • order by 排序字段 asc|desc，排序字段 asc|desc

    • select * from user order by age asc,user_name asc;
      1

  • 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");
        1
        2
        3
        4
        5
        6
        7
        8
        9

    • 数学函数

      • 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);
        1
        2
        3
        4
        5
        6

    • 时间日期函数

      • 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");
        1
        2
        3
        4
        5
        6

    • 其他函数

      • version() —— 显示当前MySQL的版本

      • user() —— 显示当前登录的用户

      • database() —— 显示当前使用的数据库

      • select version(),user(),database();
        1

    • 流程函数

      • 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;
        1
        2
        3
        4
        5
        6
        7
        8
        9

  • 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';
    1
    2
    3
    4
    5
    6
    7
    8
    9

• 多表查询(连接查询)

  • 查询的数据来自于多张表，而且多张表一般都是有外键关系的。

  • 语法：

    • 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
      1
      2
      3
      4
      5
      6

  • 连接查询分类

    • 内连接查询：只会保留两张表的匹配数据，不匹配数据全部删除。
    • 外连接查询：
      • 左外连接查询：会保留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;
    1
    2

  • 【注意】笛卡尔乘积问题，连接查询的时候忘记写连接条件

• 子查询

  • 查询里面有嵌套了一个查询，其中嵌套的查询称之为子查询。

  • 子查询可以出现select、where、from子语句当中。

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

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命令行开启多个事务演示

​