MySQL

惊落一身雪  论坛元老 | 2022-11-4 19:45:27 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 1002|帖子 1002|积分 3006

MySQL

MySQL基础篇

MySQL概述

数据库相关概念

名称概念简称数据库存储数据的仓库，数据是有组织的进行存储DataBase(DB)数据库管理系统操纵和管理数据库的大型软件DataBase Management System(DBMS)SQL操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准Structured Query Language(SQL)

  • 主流的关系型数据库管理系统
MySQL数据库


  • 版本
MySQL官方提供了两种不同的版本：

  • 社区版（MySQL Community Server）
​                免费，MySQL不提供任何技术支持

  • 商业版（MySQL Enterprise Edition）
​                收费，可以试用30天，官方提供技术支持

  • 下载
​                下载地址：https://dev.mysql.com/downloads/installer


  • 启动与停止

    • 启动
    1. net start mysql
    复制代码

    • 停止
    1. net stop mysql
    复制代码
  • 客户端连接
方式一：MySQL提供的客户端命令行工具
方式二：系统自带的命令行工具执行指令
  1. mysql [-h 127.0.0.1] [-P 3306] -u root -p
复制代码

  • 关系型数据库
概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
特点：

  • 使用表存储数据，格式统一，便于维护
  • 使用SQL语言操作，标准统一，使用方便


  • 数据模型
SQL

SQL通用语法


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


  • 单行注释：-- 注释内容 或 # 注释内容（MySQL特有）
  • 多行注释：/* 注释内容 */
SQL分类

分类全称说明DDLData Definition Language数据定义语言，用来定义数据库对象（数据库，表，字段）DMLData Manipulation Language数据操作语言，用来对数据库表中的数据进行增删改DQLData Query Language数据查询语言，用来查询数据库中表的记录DCLData Control Language数据控制语言，用来创建数据库用户、控制数据库的访问权限DDL


  • DDL-数据库操作

    • 查询
    查询所有数据库
    1. SHOW DATABASES;
    复制代码
    查询当前数据库
    1. SELECT DATABASE();
    复制代码

    • 创建
    1. CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
    复制代码

    • 删除
    1. DROP DATABASE [IF EXISTS] 数据库名;
    复制代码

    • 使用
    1. USE 数据库名;
    复制代码
  • DDL-表操作-查询

    • 查询当前数据库所有表
    1. SHOW TABLES;
    复制代码

    • 查询表结构
    1. DESC 表名;
    复制代码

    • 查询指定表的建表语句
    1. SHOW CREATE TABLE 表名;
    复制代码
  • DDL-表操作-创建
  1. CREATE TABLE 表名(
  2.         字段1 字段1类型 [COMMENT 字段1注释],
  3.     字段2 字段2类型 [COMMENT 字段2注释],
  4.     字段3 字段3类型 [COMMENT 字段3注释],
  5.     ...
  6.     字段n 字段n类型 [COMMENT 字段n注释]
  7. ) [COMMENT 表注释];
复制代码

  • DDL-表操作-数据类型
MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。
数值类型：
            分类        类型        大小        有符号(SIGNED)范围        无符号(UNSIGNED)范围        描述                        数值类型        TINYINT        1 byte        (-128, 127)        (0, 255)        小整数值                    SMALLINT        2 bytes        (-32768, 32767)        (0, 65535)        大整数值                    MEDIUMINT        3 bytes        (-8388608, 8388607)        (0, 16777215)        大整数值                    INT或INTEGER        4 bytes        (-2147483648, 2147483647)        (0, 4294967295)        大整数值                    BIGINT        8 bytes        (-2^63, 2^63-1)        (0, 2^64-1)        极大整数值                    FLOAT        4 bytes        (-3.402823466 E+38, 3.402823466351 E+38)        0 和 (1.175494351 E-38, 3.402823466 E+38)        单精度浮点数值                    DOUBLE        8 bytes        (-1.7976931348623157 E+308, 1.7976931348623157 E+308)        0 和 (2.2250738585072014 E-308, 1.7976931348623157 E+308)        双精度浮点数值                    DECIMAL                依赖于M(精度)和D(标度)的值        依赖于M(精度)和D(标度)的值        小数值(精确定点数)    字符串类型：
        分类        类型        大小        描述                    字符串类型        CHAR        0-255 bytes        定长字符串                VARCHAR        0-65535 bytes        变长字符串                TINYBLOB        0-255 bytes        不超过255个字符的二进制数据                TINYTEXT        0-255 bytes        短文本字符串                BLOB        0-66 535 bytes        二进制形式的长文本数据                TEXT        0-66 535 bytes        长文本数据                MEDIUMBLOB        0-16 777 215 bytes        二进制形式的中等长度文本数据                MEDIUMTEXT        0-16 777 215 bytes        中等长度文本数据                LONGBLOB        0-4 294 967 295 bytes        二进制形式的极大文本数据                LONGTEXT        0-4 294 967 295 bytes        极大文本数据    日期时间类型：
            分类        类型        大小        范围        格式        描述                    日期时间类型        DATE        3        1000-01-01 至 9999-12-31        YYYY-MM-DD        日期值                TIME        3        -838:59:59 至 838:59:59        HH:MM:SS        时间值或持续时间                YEAR        1        1901 至 2155        YYYY        年份值                DATETIME        8        1000-01-01 00:00:00 至 9999-12-31 23:59:59        YYYY-MM-DD HH:MM:SS        混合日期和时间值                TIMESTAMP        4        1970-01-01 00:00:01 至 2038-01-19 03:14:07        YYYY-MM-DD HH:MM:SS        混合日期和时间值，时间戳    案例：
根据需求创建表（设计合理的数据类型、长度）
设计一张员工信息表，要求如下：

  • 编号（纯数字）
  • 员工工号（字符串类型，长度不超过10位）
  • 员工姓名（字符串类型，长度不超过10位）
  • 性别（男/女，存储一个汉字）
  • 年龄（正常人年龄，不可能存储负数）
  • 身份证号（二代身份证号均为18位，身份证中有X这样的字符）
  • 入职时间（取值年月日即可）
  1. create table emp(
  2.         id int comment '编号',
  3.     workno varchar(10) comment '工号',
  4.     name varchar(10) comment '姓名',
  5.     gender char(1) comment '性别',
  6.     age tinyint unsigned comment '年龄',
  7.     idcard char(18) comment '身份证号',
  8.     entrydate date comment '入职时间'
  9. ) comment '员工表';
复制代码

  • DDL-表操作-修改

    • 添加字段
    1. ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
    复制代码
    案例：
    为emp表增加一个新的字段"昵称"为nickname，类型为varchar(20)
    1. alter table emp add nickname varchar(20) comment '昵称';
    复制代码

    • 修改数据类型
    1. ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
    复制代码

    • 修改字段名和字段类型
    1. ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
    复制代码
    案例：
    将emp表的nickname字段修改为username，类型为varchar(30)
    1. alter table emp change nickname username varchar(30) comment '用户名';
    复制代码

    • 删除字段
    1. ALTER TABLE 表名 DROP 字段名;
    复制代码
    案例：
    将emp表的字段username删除
    1. alter table emp drop username;
    复制代码

    • 修改表名
    1. ALTER TABLE 表名 RENAME TO 新表名;
    复制代码
    案例：
    将emp表的表名修改为employee
    1. alter table emp rename to employee;
    复制代码
  • DDL-表操作-删除

    • 删除表
    1. DROP TABLE [IF EXISTS] 表名;
    复制代码

    • 删除指定表，并重新创建该表
    1. TRUNCATE TABLE 表名;
    复制代码
    注意：在删除表时，表中的全部数据也会被删除

DML


  • DML-介绍
DML英文全称是Data Manipulation Language（数据操作语言），用来对数据库中表的数据记录进行增删改操作。
添加数据（INSERT）
修改数据（UPDATE）
删除数据（DELETE）

  • DML-添加数据

  • 给指定字段添加数据
  1. INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,...);
复制代码

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

  • 批量添加数据
  1. INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);
复制代码
  1. INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);
复制代码
注意：

  • 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
  • 字符串和日期型数据应该包含在引号中。
  • 插入的数据大小，应该在字段的规定范围内。
  • DML-修改数据
  1. UPDATE 表名 SET 字段名1=值1,字段名2=值2,... [WHERE 条件];
复制代码
注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

  • DML-删除数据
  1. DELETE FROM 表名 [WHERE 条件];
复制代码
注意：

  • DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
  • DELETE语句不能删除某一个字段的值（可以使用UPDATE）。
DQL


  • DQL-介绍
DQL英文全称是Data Query Language（数据查询语言），用来查询数据库中表的记录。
查询关键字：SELECT

  • DQL-语法
  1. SELECT
  2.         字段列表
  3. FROM
  4.         表名列表
  5. WHERE
  6.         条件列表
  7. GROUP BY
  8.         分组字段列表
  9. HAVING
  10.         分组后条件列表
  11. ORDER BY
  12.         排序字段列表
  13. LIMIT
  14.         分页参数
复制代码

  • DQL-基本查询

  • 查询多个字段
  1. SELECT 字段1,字段2,字段3,... FROM 表名;
复制代码
  1. SELECT * FROM 表名;
复制代码

  • 设置别名
  1. SELECT 字段1 [AS 别名1],字段2 [AS 别名2],... FROM 表名;
复制代码

  • 去除重复记录
  1. SELECT DISTINCT 字段列表 FROM 表名;
复制代码

  • DQL-条件查询

  • 语法
  1. SELECT 字段列表 FROM 表名 WHERE 条件列表;
复制代码

  • 条件
比较运算符功能>大于>=大于等于</tdtd小于/td/trtrtd=/tdtd小于等于/td/trtrtd=/tdtd等于/td/trtrtd 或 !=/tdtd不等于/td/trtrtdBETWEEN ... AND .../tdtd在某个范围之内（含最小、最大值）/td/trtrtdIN(...)/tdtd在in之后的列表中的值，多选一/td/trtrtdLIKE 占位符/tdtd模糊匹配（_匹配单个字符，%匹配任意个字符）/td/trtrtdIS NULL/tdtd是NULL/td/tr/tbody/tabletabletheadtrth逻辑运算符/thth功能/th/tr/theadtbodytrtdAND 或 &&/tdtd并且（多个条件同时成立）/td/trtrtdOR 或 ||/tdtd或者（多个条件任意一个成立）/td/trtrtdNOT 或 !/tdtd非，不是/td/tr/tbody/tableulliDQL-聚合函数/li/ulolli介绍/li/olp将一列数据作为一个整体，进行纵向计算。/pol start="2"li常见聚合函数/li/oltabletheadtrth函数/thth功能/th/tr/theadtbodytrtdcount/tdtd统计数量/td/trtrtdmax/tdtd最大值/td/trtrtdmin/tdtd最小值/td/trtrtdavg/tdtd平均值/td/trtrtdsum/tdtd求和/td/tr/tbody/tableol start="3"li语法/li/ol
  1. SELECT 聚合函数(字段列表) FROM 表名;
复制代码
pstrong注意：null值不参与所有聚合函数运算。/strong/pulliDQL-分组查询/li/ulolli语法/li/ol
  1. SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分段字段名 [HAVING 分组后过滤条件];
复制代码
ol start="2"liwhere和having区别/li/olulli执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。/lili判断条件不同：where不能对聚合函数进行判断，而having可以。/li/ulpstrong注意：/strong/pullip执行顺序：where  聚合函数 > having</p>分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
DQL-排序查询
</ul>

  • 语法
  1. SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2,...;
复制代码

  • 排序方式


  • ASC: 升序（默认值）
  • DESC: 降序
注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

  • DQL-分页查询

  • 语法
  1. SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
复制代码
注意：

  • 起始索引从0开始，起始索引 = (查询页码 - 1) * 每页显示记录数。
  • 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
  • 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 查询记录数。
  • DQL-执行顺序
  1. FROM
  2.         表名列表
  3. WHERE
  4.         条件列表
  5. GROUP BY
  6.         分组字段列表
  7. HAVING
  8.         分组后条件列表
  9. SELECT
  10.         字段列表
  11. ORDER BY
  12.         排序字段列表
  13. LIMIT
  14.         分页参数
复制代码
DCL


  • DCL-介绍
DCL英文全称是Data Control Language（数据控制语言），用来管理数据库用户、控制数据库的访问权限。

  • DCL-管理用户

  • 查询用户
  1. USE mysql;
  2. SELECT * FROM user;
复制代码

  • 创建用户
  1. CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
复制代码

  • 修改用户密码
  1. ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
复制代码

  • 删除用户
  1. DROP USER '用户名'@'主机名';
复制代码
注意：

  • 主机名可以使用%通配。
  • 这类SQL开发人员操作的比较少，主要是DBA（Database Administrator 数据库管理员）使用。
  • DCL-权限控制
MySQL中定义了很多种权限，都是常用的就以下几种：
[table]权限说明ALL, ALL PRIVILEGES所有权限SELECT查询数据INSERT插入数据UPDATE修改数据DELETE删除数据ALTER修改表DROP删除数据库/表/视图CREATE创建数据库/表

  • 查询权限
  1. SHOW GRANTS FOR '用户名'@'主机名';
复制代码

  • 授予权限
  1. GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
复制代码

  • 撤销权限
  1. REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
复制代码
注意：

  • 多个权限之间，使用逗号分隔。
  • 授权时，数据库名和表名可以使用 * 进行通配，代表所有。
函数

函数是指一段可以直接被另一段程序调用的程序或代码。
字符串函数

MySQL中内置了很多字符串函数，常用的几个如下：
函数功能CONCAT(S1,S2,...,Sn)字符串拼接，将S1, S2, ... Sn拼接成一个字符串LOWER(str)将字符串str全部转为小写UPPER(str)将字符串str全部转为大写LPAD(str,n,pad)左填充，用字符串pad对str的左边进行填充，达到n个字符串长度RPAD(str,n,pad)右填充，用字符串pad对str的右边进行填充，达到n个字符串长度TRIM(str)去掉字符串头部和尾部的空格SUBSTRING(str,start,len)返回字符串str从start位置起的len个长度的字符串

  • 根据需求完成以下SQL编写
由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如：1号员工的工号应该为00001。
  1. update emp set workno = lpad(workno, 5, '0');
复制代码
数值函数

常见的数值函数如下：
函数功能CEIL(x)向上取整FLOOR(x)向下取整MOD(x)返回x/y的模RAND()返回0~1内的随机数ROUND(x,y)求参数x的四舍五入的值，保留y位小数

  • 根据需求完成以下SQL编写
通过数据库的函数，生成一个六位数的随机验证码
  1. select lpad(round(rand()*1000000, 0), 6, '0');
复制代码
日期函数

常见的日期函数如下：
函数功能CURDATE()返回当前日期CURTIME()返回当前时间NOW()返回当前日期和时间YEAR(date)获取指定date的年份MONTH(date)获取指定date的月份DAY(date)获取指定date的日期DATE_ADD(date, INTERVAL expr type)返回一个日期/时间值加上一个时间间隔expr后的时间值DATEDIFF(date1, date2)返回起始时间date1和结束时间date2之间的天数

  • 根据需求完成以下SQL编写
查询所有员工的入职天数，并根据入职天数倒序排序
  1. select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays desc;
复制代码
流程函数

流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。
函数功能IF(value, t, f)如果value为true，则返回t，否则返回fIFNULL(value1, value2)如果value1不为空，返回value1，否则返回value2CASE WHEN [val1] THEN [res1] ... ELSE [default] END如果val1为true，返回res1，...，否则返回default默认值CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END如果expr的值等于val1，返回res1，...，否则返回default默认值

  • 根据需求完成以下SQL编写
统计班级各个学员的成绩，展示的规则如下：

  • 大于等于85，展示优秀
  • 大于等于60，展示及格
  • 否则，展示不及格
  1. select
  2.         id,
  3.         name,
  4.         (case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end) '数学',
  5.         (case when english >= 85 then '优秀' when english >= 60 then '及格' else '不及格' end) '英语',
  6.         (case when chinese >= 85 then '优秀' when chinese >= 60 then '及格' else '不及格' end) '语文'
  7. from score;
复制代码
约束

概述


  • 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
  • 目的：保证数据库中数据的正确性、有效性和完整性。
  • 分类：
约束描述关键字非空约束限制该字段的数据不能为nullNOT NULL唯一约束保证该字段的所有数据都是唯一、不重复的UNIQUE主键约束主键是一行数据的唯一标识，要求非空且唯一PRIMARY KEY默认约束保存数据时，如果未指定该字段的值，则采用默认值DEFAULT检查约束(8.0.16版本之后)保证字段值满足某一个条件CHECK外键约束用来让两张表的数据之间建立连接，保证数据的一致性和完整性FOREIGN KEY注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。
约束演示

根据需求，完成表结构的创建
字段名字段含义字段类型约束条件约束关键字idID唯一标识int主键，并且自动增长PRIMARY KEY, AUTO_INCREMENTname姓名varchar(10)不为空，并且唯一NOT NULL, UNIQUEage年龄int大于0，并且小于等于120CHECKstatus状态char(1)如果没有指定该值，默认为1DEFAULTgender性别char(1)无
  1. create table user(
  2.         id int primary key auto_increment comment '主键',
  3.     name varchar(10) not null unique comment '姓名',
  4.     age int check(age > 0 && age <= 120) comment '年龄',
  5.     status char(1) default '1' comment '状态',
  6.     gender char(1) comment '性别'
  7. ) comment '用户表';
复制代码
注意：

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


  • 概念：SQL语句中嵌套SELECT语句，称为嵌套语句，又称子查询。
  1. CREATE TABLE 表名(
  2.         字段名 数据类型,
  3.     ...
  4.     [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
  5. );
复制代码
子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个。

  • 根据子查询结果的不同，分为：

    • 标量子查询（子查询结果为单个值）
    • 列子查询（子查询结果为一列）
    • 行子查询（子查询结果为一行）
    • 表子查询（子查询结果为多行多列）

  • 根据子查询的位置，分为：WHERE之后、FROM之后、SELECT之后。
  • 标量子查询
子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。

常用的操作符：=                >        >=        <         (select entrydate from emp where name = '方东白');[/code]

  • 列子查询
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常见的操作符：IN、NOT IN、ANY、SOME、ALL
操作符描述IN在指定的集合范围之内，多选一NOT IN不在指定的集合范围之内ANY子查询返回列表中，有任意一个满足即可SOME与ANY等同，使用SOME的地方都可以使用ANYALL子查询返回列表的所有值都必须满足
  1. ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);
复制代码

  • 行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符：=                IN        NOT IN
  1. ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
复制代码

  • 表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符：IN
  1. ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
复制代码
多表查询案例

根据需求，完成SQL语句的编写

  • 查询员工的姓名、年龄、职位、部门信息。
  • 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
  • 查询拥有员工的部门ID、部门名称。
  • 查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来。
  • 查询所有员工的工资等级。
  • 查询"研发部"所有员工的信息及工资等级。
  • 查询"研发部"员工的平均工资。
  • 查询工资比"张三"高的员工信息。
  • 查询比平均薪资高的员工信息。
  • 查询低于本部门平均工资的员工信息。
  • 查询所有的部门信息，并统计部门的员工人数。
  • 查询所有学生的选课情况，展示出学生名称、学号、课程名称。
  1. SELECT 字段列表 FROM 表1, 表2 WHERE 条件...;
复制代码
事务

事务简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。
事务操作


  • 查看/设置事务提交方式
  1. SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;
复制代码

  • 开启事务
  1. -- 内连接演示
  2. -- 1.查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）
  3. select e.name, d.name from emp e, dept d where e.dept_id = d.id;
  4. -- 2.查询每一个员工的姓名，及关联的部门的名称（显式内连接实现）
  5. select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
复制代码

  • 提交事务
  1. SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
复制代码

  • 回滚事务
  1. SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;
复制代码
事务四大特性


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

问题描述脏读一个事务读到另外一个事务还没有提交的数据。不可重复读一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。幻读一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影“。事务隔离级别

隔离级别脏读不可重复读幻读Read uncommitted√√√Read committed×√√Repeatable Read(默认)××√Serializable×××
  1. -- 外连接演示
  2. -- 1.查询emp表的所有数据，和对应的部门信息（左外连接）
  3. select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
  4. -- 2.查询dept表的所有数据，和对应的员工信息（右外连接）
  5. select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;
复制代码
注意：事务隔离级别越高，数据越安全，但是性能越低。
MySQL进阶篇

存储引擎

MySQL体系结构



  • 连接层
最上层是一些客户端和连接服务，主要完成一些类似于连接处理、授权认证，及相关的安全方案。服务器也会为安全接入的每个用户端验证它所具有的操作权限。

  • 服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。

  • 引擎层
存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。

  • 存储层
主要是将数据存储在文件系统之上，并完成与存储引擎的交互。
存储引擎简介

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可以被称为表类型。

  • 在创建表时，指定存储引擎
  1. SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
复制代码

  • 查看当前数据库支持的存储引擎
  1. -- 自连接
  2. -- 1.查询员工及其所属领导的名字
  3. select a.name '员工', b.name '领导' from emp a join emp b on a.manager_id = b.id;
  4. -- 2.查询所有员工及其领导的名字，如果员工没有领导，也需要查询出来
  5. select a.name '员工', b.name '领导' from emp a left outer join emp b on a.manager_id = b.id;
复制代码
存储引擎特点


  • InnoDB

    • 介绍
    InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎。

    • 特点
    DML操作遵循ACID模型，支持事务；
    行级锁，提高并发访问性能；
    支持外键FOREIGN KEY约束，保证数据的完整性和正确性。

    • 文件
    xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。
    参数：innodb_file_per_table

  • MyISAM

    • 介绍
    MyISAM是MySQL早期的默认存储引擎。

    • 特点
    不支持事务，不支持外键；
    支持表锁，不支持行锁；
    访问速度快。

    • 文件
    xxx.sdi：存储表结构信息
    xxx.MYD：存储数据
    xxx.MYI：存储索引

  • Memory

    • 介绍
    Memory引擎的表数据是存储在内存中的，由于受到硬件问题或断电问题的影响，只能将这些表作为临时表或缓存使用。

    • 特点
    内存存放；
    hash索引（默认）。

    • 文件
    xxx.sdi：存储表结构信息

特点InnoDBMyISAMMemory存储限制64TB有有事务安全支持--锁机制行锁表锁表锁B+tree索引支持支持支持Hash索引--支持全文索引支持（5.6版本之后）支持-空间使用高低N/A内存使用高低中等批量插入速度低高高支持外键支持--存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

  • InnoDB：是MySQL的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
  • MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
  • MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。
索引

索引概述


  • 介绍
索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

  • 优缺点
优势劣势提高数据检索的效率，降低数据库的IO成本索引列也是要占用空间的通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗索引大大提高了查询效率，同时却也降低了更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：
索引结构描述B+tree索引最常见的索引类型，大部分引擎都支持B+树索引Hash索引底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询R-tree（空间索引）空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少Full-text（全文索引）是一种通过建立倒排索引，快速匹配文档的方式，类似于Lucene,Solr,ES索引InnoDBMyISAMMemoryB+tree索引支持支持支持Hash索引不支持不支持支持R-tree索引不支持支持不支持Full-text5.6版本之后支持支持不支持我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

  • B+Tree
MySQL索引数据结构对经典的B+Tree进行了优化，在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

  • Hash
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，任何存储在hash表中。
如果两个（或多个）键值，映射到一个相同的槽位上，它们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。
Hash索引特点：

  • Hash索引只能用于对等比较(=, in)，不支持范围查询(between, >, ，= 85分，等级为优秀。
  • score >= 60分且score < 85分，等级为及格。
  • score < 60分，等级为不及格。
  1. SELECT 字段列表 FROM 表A ...
  2. UNION [ALL]
  3. SELECT 字段列表 FROM 表B ...;
复制代码

  • 参数
类型含义备注IN该类参数作为输入，也就是需要调用时传入值默认OUT该类参数作为输出，也就是该参数可以作为返回值INOUT既可以作为输入参数，也可以作为输出参数用法：
  1. -- 联合查询
  2. -- 1.将薪资低于5000的员工和年龄大于50岁的员工全部查询出来
  3. select * from emp where salary < 5000
  4. union
  5. select * from emp where age > 50;
复制代码
根据存储过程，完成如下需求

  • 根据传入参数score，判定当前分数对应的分数等级，并返回。
① score >= 85分，等级为优秀。
② score >= 60分且score < 85分，等级为及格。
③ score < 60分，等级为不及格。
  1. SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
复制代码

  • 将传入的200分制的分数，进行换算，换算成百分制，然后返回。
  1. -- 标量子查询
  2. -- 1.查询"销售部"的所有员工信息
  3. select * from emp where dept_id = (select id from dept where name = '销售部');
  4. -- 2.查询在"方东白"入职之后的员工信息
  5. select * from emp where entrydate > (select entrydate from emp where name = '方东白');
复制代码

  • case
语法一：
  1. -- 列子查询
  2. -- 1.查询"销售部"和"市场部"的所有员工信息
  3. select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
  4. -- 2.查询比财务部所有人工资都高的员工信息
  5. select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部'));
  6. -- 3.查询比研发部其中任意一人工资高的员工信息
  7. select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研发部'));
复制代码
语法二：
  1. -- 行子查询
  2. -- 1.查询与"张三"的薪资及直属领导相同的员工信息
  3. select * from emp where (salary, manager_id) = (select salary, manager_id from emp where name = '张三');
复制代码
定义存储过程，完成如下需求
根据传入的月份，判定月份所属的季节（要求采用case结构）。

  • 1-3月份，为第一季度
  • 4-6月份，为第二季度
  • 7-9月份，为第三季度
  • 10-12月份，为第四季度
  1. -- 表子查询
  2. -- 1.查询与"张三"，"李四"的职位和薪资相同的员工信息
  3. select * from emp where (job, salary) in (select job, salary from emp where name = '张三' or name = '李四');
  4. -- 2.查询入职日期是"2006-01-01"之后的员工信息，及其部门信息
  5. select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left outer join dept d on e.dept_id = d.id;
复制代码

  • while
while循环是有条件的循环控制语句，满足条件后，再执行循环体中的SQL语句，具体语法为：
  1. -- 1. 查询员工的姓名、年龄、职位、部门信息。
  2. select e.name, e.age, e.job, d.* from emp e, dept d where e.dept_id = d.id;
  3. -- 2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
  4. select e.name, e.age, e.job, d.* from emp e inner join dept d on e.dept_id = d.id where e.age < 30;
  5. select e.name, e.age, e.job, d.* from (select * from emp where age < 30) e, dept d where e.dept_id = d.id;
  6. -- 3. 查询拥有员工的部门ID、部门名称。
  7. select distinct d.id, d.name from emp e, dept d where e.dept_id = d.id;
  8. -- 4. 查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来。
  9. select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40;
  10. -- 5. 查询所有员工的工资等级。
  11. select e.*, s.grade from emp e, salgrade s where e.salary between s.losal and s.hisal;
  12. -- 6. 查询"研发部"所有员工的信息及工资等级。
  13. select e.*, s.grade from emp e, dept d, salgrade s where e.dept_id = d.id and (e.salary between s.losal and s.hisal) and d.name = '研发部';
  14. -- 7. 查询"研发部"员工的平均工资。
  15. select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';
  16. -- 8. 查询工资比"张三"高的员工信息。
  17. select * from emp where salary > (select salary from emp where name = '张三');
  18. -- 9. 查询比平均薪资高的员工信息。
  19. select * from emp where salary > (select avg(salary) from emp);
  20. -- 10. 查询低于本部门平均工资的员工信息。
  21. select * from emp e2 where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);
  22. -- 11. 查询所有的部门信息，并统计部门的员工人数。
  23. select d.*, (select count(*) from emp e where e.dept_id = d.id) from dept d;
  24. -- 12. 查询所有学生的选课情况，展示出学生名称、学号、课程名称。
  25. select s.name, s.no, c.name from student s, student_course sc, course c where s.id = sc.student_id and sc.course_id = c.id;
复制代码
定义存储过程，完成如下需求
计算从1累加到n的值，n为传入的参数值。
  1. SELECT @@autocommit;
  2. SET @@autocommit = 0;-- 设置为手动提交事务
复制代码

  • repeat
repeat是有条件的循环控制语句，当满足条件的时候退出循环，具体语法为：
  1. START TRANSACTION 或 BEGIN;
复制代码
定义存储过程，完成如下需求
计算从1累加到n的值，n为传入的参数值。
[code]create procedure p(in n int)begin        declare total int default 0;        repeat                set total := total + n;                set n := n - 1;        until n

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

惊落一身雪

论坛元老
这个人很懒什么都没写!

楼主热帖

标签云

AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表