• Mysql入门

    文章目录

    Mysql 入门

    0. 前言

    学习目标：

    1. 安装数据库，以 Windows OS 的Mysql 为例；
    2. 了解数据库的一些基本概念；
    3. SQL语句的学习与练习；
    4. 了解事务、索引、SQL优化。

    1. 准备工作

    正式学习之前，我们要了解数据库的一些相关概念、将学习过程中所需的Mysql 准备好。

    1.1 相关概念

    名称作用
    数据库DataBase（DB）存储数据的仓库，数据是有组织的进行存储
    数据库管理系统DataBase Management System（DBMS）操纵和管理数据库的大型软件
    结构化查询语言Structured Query Language（SQL）操作关系型数据库的编程语言，定义了一套操作关系型数据库的同一标准

    数据库的种类：
    层次式数据库（已过时）、网络型数据库（已过时）、关系型数据库、非关系型数据库；

    常见的数据库：
    SQL Server、MySQL、Oracle、DB2、Sqlite、redis（非关系型数据库）、mongodb（非关系型数据库）

    Mysql是关系型数据库，本文我们侧重学习Mysql。

    推荐教材：《数据库系统概论》

    1.2 Mysql 安装与启动

    （1）下载与安装：

    下载：下载地址

    安装：安装教程，转载自博客园

    （2）启动：

    安装完毕后，我们可以在 windows 的 dos 窗口启动Mysql、连接客户端

    	net start mysql

    • 1

    （3）连接客户端

    	mysql [-h 127.0.0.1] [-P 3306] -uroot -p
参数：
	-h：mysql服务所在的主机IP
	-P：mysql服务端口号，默认是3306
	-u：mysql数据库用户名，一般为约定俗成的root
	-p：mysql数据库用户名对应的密码
注意：[]内的参数是可选的，如果需要连接远程的mysql，就需要提供主机IP和端口号，如果是连接本地的mysql，就不用提供这两个参数；注意大小写。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    1.3 常用工具

    我们常用一些可视化工具来辅助我们操作数据库。对于mysql，此处推荐： navicat，sqlyog等。

    下载地址：
    navicat：navicat官网
    sqlyog: sqlyog官网

    2. Mysql 入门

    2.1 数据模型

    在上一个小节我们提到过，Mysql是一种关系型数据库（Relational Database Management System，RDBMS），是建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

    二维表：类似于excel，由行和列组成的表。

    2.2 SQL语言分类

    我们使用SQL来操作数据库。SQL分为5类：

    语言作用关键词举例
    DDL（Data Definition Language）数据定义语言定义数据，如创建表、修改表字段等CREATE，ALTER，DROP
    DQL（Data Query Language）数据查询语言查询数据SELECT
    DML（Data Manipulation Language）数据操纵语言操作数据，增删改等INSERT，UPDATE，DELETE
    DTL(Data Transaction Language) 事务控制语言事务提交与回滚COMMIT，ROLLBACK
    DCL(Data Control Language)数据控制语言权限授予与回收GRANT，REVOKE

    此阶段我们重点学习DDL、DQL与DML。

    2.3 Mysql数据类型

    Mysql支持的数据类型从大类可以分为以下几类：数值类型、字符串类型、日期、其他数据类型。

    类别常见类型
    数值类型int, tinyint, smallint, bigint, float, double, bool 等
    字符串类型varchar, char, text 等
    日期date, time, datetime, year 等
    其他set, point 等

    现阶段我们应学习常见的数值、字符串、日期类型，至于其他类型，可以在实际业务中用到时再去学习或研究。

    2.4 DDL数据库操作

    在了解了上面的SQL语言分类、数据类型之后，我们就可以学习并使用这些SQL语句了。

    Mysql 既是一个DB，也是一个DBMS。所以当我们连接了Mysql客户端后，可以进行数据库操作。

    1. 查询所有数据库：
    show databases;

    • 1
    1. 创建数据库：
    create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ];

    • 1
    1. 删除数据库：
    drop database [ if exists ] 数据库名;

    • 1
    1. 切换数据库：
    use 数据库名;

    • 1

    2.5 DDL表操作

    当我们切换至某个数据库后，可以在这个数据库内进行表操作。

    1. 查询当前数据库所有表
    show TABLES;

    • 1
    1. 查看指定表结构
    desc 表名;

    • 1
    1. 查询指定表的建表语句
    show create table 表名;

    • 1
    1. 创建表结构
    create table 表名(
	字段1 字段类型 [ COMMENT 注释1 ],
    字段2 字段类型 [ COMMENT 注释2 ],
    字段3 字段类型 [ COMMENT 注释3 ],
    .......
    字段N 字段类型 [ COMMENT 注释N ],
) [ COMMENT 表注释 ];

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    1. 添加表字段
    ALTER TABLE 表名 ADD 字段名 数据类型(长度) [COMMENT 注释] [ 约束 ];

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

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

    • 1
    1. 删除字段
    ALTER TABLE 表名 DROP 字段名;

    • 1
    1. 修改表名
    ALTER TABLE 表名 RENAME TO 新表名;

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

    • 1
    1. 删除并重新创建表
    TRUNCATE TABLE 表名;

    • 1

    2.6 DML 数据操作

    1. 添加数据

      // 指定字段名
INSERT INTO 表名(字段1,字段2,....) VALUES(值1,值2,....);
// 不指定字段名，值列表的顺序必须与字段的顺序一致
INSERT INTO 表名 VALUES(值1,值2,....);
// 批量添加数据
INSERT INTO 表名(字段1,字段2,....) VALUES(值1,值2,....),(值1,值2,....),(值1,值2,....);

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6

    2. 修改数据

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

      • 1

    3. 删除数据

      DELETE FROM 表名 [ WHERE 条件 ];

      • 1

    2.7 DQL 数据查询

    基本语法：

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 基础查询（不带任何条件）
    • 条件查询（WHERE）
    • 聚合函数（count、max、min、avg、sum）
    • 分组查询（GROUP BY）
    • 排序查询（ORDER BY）：排序规则 DESC（降序） || ASC （升序，默认）
    • 分页查询（LIMIT）：参数 （index，pageSize）index：记录的索引，从0开始，pageSize：每次查询多少条记录

    2.8 综合练习

    学习完上述语句后，我们来做一组综合练习来巩固一下：

    1. 准备数据库表：

      DROP DATABASE IF EXISTS test1;
CREATE DATABASE test1;
USE test1;
##部门表
#DROP IF EXISTS TABLE DEPT;
CREATE TABLE DEPT(
DEPTNO int PRIMARY KEY,##部门编号
DNAME VARCHAR(14) , ##部门名称
LOC VARCHAR(13) ##部门地址
) ;
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
##员工表
#DROP IF EXISTS TABLE EMP;
CREATE TABLE EMP(
EMPNO int PRIMARY KEY, #员工编号
ENAME VARCHAR(10), #员工姓名
JOB VARCHAR(9), #员工工作
MGR int, #员工直属领导编号
HIREDATE DATE, #入职时间
SAL double, #工资
COMM double, #奖金
DEPTNO int #对应dept表的外键
);
## 添加 部门 和 员工 之间的主外键关系

INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20);
INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20);
INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10);
#工资等级表
#DROP IF EXISTS TABLE SALGRADE;
CREATE TABLE SALGRADE(
GRADE int, #等级
LOSAL double, #最低工资
HISAL double ); #最高工资
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      • 17
      • 18
      • 19
      • 20
      • 21
      • 22
      • 23
      • 24
      • 25
      • 26
      • 27
      • 28
      • 29
      • 30
      • 31
      • 32
      • 33
      • 34
      • 35
      • 36
      • 37
      • 38
      • 39
      • 40
      • 41
      • 42
      • 43
      • 44
      • 45
      • 46
      • 47
      • 48
      • 49
      • 50
      • 51
      • 52
      • 53

    2. 题目：

      #1、查找部门30中员工的详细信息。

#2、找出从事clerk工作的员工的编号、姓名、部门号。

#3、检索出奖金多于基本工资的员工信息。

#4、检索出奖金多于基本工资60%的员工信息。

#5、找出10部门的经理、20部门的职员 的员工信息。

#6、找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。

#7、找出获得奖金的员工的工作。

#8、找出奖金少于100或者没有获得奖金的员工的信息。

#9、找出姓名以A、B、S开始的员工信息。

#10、找到名字长度为6个字符的员工信息。

#11、名字中不包含R字符的员工信息。

#12、返回员工的详细信息并按姓名排序。

#13、返回员工的信息并按工作降序工资升序排列。

#14、计算员工的日薪(按30天)。

#15、找出姓名中包含A的员工信息。

多表查询

#1、返回拥有员工的部门名、部门号。

#2、工资水平多于smith的员工信息。

#3、返回员工和所属经理的姓名。

#4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。

#5、返回员工姓名及其所在的部门名称。

#6、返回从事clerk工作的员工姓名和所在部门名称。

#7、返回部门号及其本部门的最低工资。

#8、返回销售部(sales)所有员工的姓名。

#9、返回工资水平多于平均工资的员工。

#10、返回与SCOTT从事相同工作的员工。

#11、返回与30部门员工工资水平相同的员工姓名与工资。

#12、返回工资高于30部门所有员工工资水平的员工信息。

#13、返回部门号、部门名、部门所在位置及其每个部门的员工总数。

#14、返回员工的姓名、所在部门名及其工资。

#15、返回员工的详细信息。(包括部门名)

#16、返回员工工作及其从事此工作的最低工资。

#17、计算出员工的年薪，并且以年薪排序。

#18、返回工资处于第四级别的员工的姓名。

#19、返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资

#20.工资等级多于smith的员工信息。

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      • 17
      • 18
      • 19
      • 20
      • 21
      • 22
      • 23
      • 24
      • 25
      • 26
      • 27
      • 28
      • 29
      • 30
      • 31
      • 32
      • 33
      • 34
      • 35
      • 36
      • 37
      • 38
      • 39
      • 40
      • 41
      • 42
      • 43
      • 44
      • 45
      • 46
      • 47
      • 48
      • 49
      • 50
      • 51
      • 52
      • 53
      • 54
      • 55
      • 56
      • 57
      • 58
      • 59
      • 60
      • 61
      • 62
      • 63
      • 64
      • 65
      • 66
      • 67
      • 68
      • 69
      • 70
      • 71

    3. 事务

    3.1 事务的简介

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

    3.2 事务的特性：ACID

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

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

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

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

    3.3 并发事务问题

    1. 脏读：一个事务读到另一个事务还没有提交的数据。
    2. 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不相同，称之为不可重复读。
    3. 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻觉”。

    3.4 事务的隔离级别

    为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下四种：

    隔离级别脏读不可重复读幻读
    读未提交（Read uncommitted）√√√
    读已提交（Read committed）×√√
    可重复读（Repeatable Read）××√
    串行化（Serializable）×××

    MySQL数据库的默认事务隔离级别为：可重复读（Repeatable Read）

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

    • 1
    • 2

    注意：事务隔离级别越高，数据越安全，但是性能越低

    4. 索引

    索引是一个单独的，存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。

    MySQL中索引的存储类型有两种：BTree和Hash

    索引是在存储引擎中实现的。（InnoDB、MyISAM、Memory、Heap）

    InnoDB/MyISAM只支持 BTree 索引

    Memory/Heap 支持 BTree 和 Hash 索引

    索引的优缺点：

    优点：

    • 提高数据的查询效率（类似于书的目录）
    • 可以保证数据库表中每一行数据的唯一性（唯一索引）
    • 减少分组和排序的时间（当使用分组和排序进行数据查询时）
      • 被索引的列会自动进行分组和排序

    缺点：

    • 占用磁盘空间
    • 降低更新表的效率（不仅要更新表中的数据，还要更新相应的索引文件）

    4.1 索引的分类

    1. 普通索引和唯一索引
    • 普通索引：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值
    • 唯一索引：要求索引列的值必须唯一，但允许有空值
      • 如果是组合索引，则列值的组合必须唯一
      • 主键索引是一种特殊的唯一索引，不允许有空值
    1. 单列索引和组合索引

    • 单列索引：一个索引只包含单个列，一个表可以有多个单列索引

    • 组合索引：在表的多个字段组合上创建的索引

      • 只有在查询条件中使用了这些字段的左边字段时，索引才会被使用（最左前缀原则）

      例如：表中有 gender,stuno,stuname,age,tel,… 字段，将stuno和stuname作为组合索引，在查询条件中如果使用了stuno字段，组合索引就会被使用。

    1. 全文索引
    • 全文索引的类型为 fulltext
    • 在定义索引的列上支持 值的全文查找，允许在这些索引列中插入重复值和空值
    • 全文索引可以在 char、varchar 和 text 类型的列上创建
    1. 空间索引
    • 空间索引是对空间数据类型的字段建立的索引
    • MySQL中的空间数据类型有4种，分别是 Geometry、Point、Linestring和 Polygon
    • MySQL 使用 Spatial关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引
    • 创建空间索引的列，不允许为空值，且只能在 MyISAM 的表中创建
    1. 前缀索引
    • 在char、varchar和text类型的列上创建索引时，可以指定索引列的长度

    4.2 MySQL索引的使用

    1. 索引的基本语法

    定义主键约束、外键约束、唯一约束 等约束时，相当于同时在指定的列上创建了一个索引

    1. 判断是否要添加索引

    加索引：

    • 数据本身具有某种性质，例如：唯一性，非空性…
    • 要频繁进行分组或排序的列，例如：经常要按类别、价格、销量等字段排序
    • 如果待排序的列有多个，可以建立组合索引

    不加索引：

    • 经常更新的列
    • 列的值类型很少，例如：性别字段
    • where条件中用不到的列（很少使用该列作为查询条件）
    • 参与计算的列
    • 数据量小的表
    1. 只要创建了索引，就一定会生效吗？

    不一定，当使用组合索引时，如果没有遵循最左匹配原则，索引不生效

    例如：创建 id、name、age 组合索引

    • id、（id、name）、（id、name、age）查询，索引生效
    • age、（age、name）查询，索引不生效
    1. 怎样判断索引是否生效？

    使用 explain 关键字

    • possible_keys：MySQL在搜索数据记录时可选用的各个索引
    • key：MySQL实际选用的索引
    1. 怎么避免索引失效？
    • 使用组合索引时，遵循最左匹配原则
    • 不在索引列上进行任何操作，操作有：计算、函数、类型转换
    • 尽量使用覆盖索引
    • 索引列尽量不使用 不等于条件，通配符开头的模糊查询
    • 字符串加单引号（不加可能会发生索引列的隐式转换，导致索引失效）

    5. MySQL性能优化

    记得比较零散：

    选择最合适的字段属性，因为MySQL是一种关系型数据库，可以很好地支持大数据量的存储，一般来说，表越小，查询越快，所以在创建表的时候，字段的宽度尽可能小；

    尽量把字段设置为非空（not null），在执行数据库查询时，不用去比较null值；

    对于某些文本字段来说，例如省份或者性别，可以将它们定义为枚举（enum）类型，该类型会被当做数值类型来处理，比文本类型的处理速度要快；

    使用连接（join）来代替子查询，使用连接不需要在内存中创建临时表，如果你的应用程序有很多join查询，你应该确认被连接的两个表的字段上是有索引的，这样存储引擎再执行查询时会启动查询优化

    当执行多表连接查询时，select * from a,b，返回的是笛卡尔乘积，不建议使用该写法，都使用join连接，当使用左连接时，left join 左边的表为主表，右边的表为从表，返回主表的所有记录，如果从表中没有对应的记录，则返回null，右连接反之；

    不使用子查询；

    避免函数索引，尽量不要在索引列上使用函数，如果使用函数或进行计算，会直接全表扫描；

    使用in来替换or，select * from emp where id in(1,2,3)的性能要优于select * from emp where id=1 or id=2 or id=3；

    like 双百分号无法使用到索引，select * from emp where name like ‘%章%’；

    大偏移量的查询，如：select * from emp limit 90000000,10 其查询性能就不如：select * from emp where id > 90000000 limit 10，先过滤id（因为id使用了索引）再limit；

    避免数据类型不一致；

    分组统计可以禁止排序，默认情况下mysql对所有group by的字段进行排序，可以指定order by null 禁止排序；

    连接配置优化：服务端配置提高，增加服务端可用连接数；客户端优化：程序连接数据库，使用第三方数据源连接池，比较好的阿里的druid，springboot内置的hikari；

    连接池的设置的官方建议公式：cpu核心数*2+1，假设服务器的cpu核心数是8，将连接池设置为17就可以了；

    架构优化：

    • 使用缓存
    • 读写分离（集群，主从复制）
    • 分库分表

    不要使用外键，触发器和视图功能。降低了可读性，检查代码的同时还得查看数据库的代码；

    把计算的工作交给程序，数据库只做好存储的工作，

    数据的完整性校验工作交给开发人员完成，而不是依赖于外键关系；

    图片、音频、视频的存储，不要直接存储大文件，而是存储大文件的访问地址；

    大字段的拆分和数据冗余；

    6. 总结

    通过本文，我们初步学习了Mysql的基本概念、SQL语句，提供了一个练习题来练习。我们还学习了Mysql的索引、性能优化。

    本文部分内容来自于一些参考资料与学习笔记（作者：Jack，Kevin），侵删。

  • 相关阅读:
    使用Jenkins进行持续集成与部署
    FPGA UDP RGMII 千兆以太网（2）IDDR
    [配置] 安卓 | 将微信公众号文章保存到Notion
    UI自动化测试单例实现报错：AttributeError: ‘NoneType‘ object has no attribute ‘get_driver‘
    基于Cucumber的行为驱动开发(BDD)实例
    【零基础入门SpringMVC】第一期——开篇导论
    Jenkins 安装
    【Vue简介+搭建Vue开发环境+Hello小案例】
    python创建虚拟环境及相关配置（windows环境下）
    【Java从入门到精通 08】：面向对象编程（进阶部分）
  • 原文地址：https://blog.csdn.net/weixin_43779187/article/details/127969322