[大数据]MySQL学习一条龙

目录

MySQL安装和配置

图形化工具Navicat安装和使用

数据库的创建和管理

表的创建和管理

数据完整性约束

数据库设计和规范化

数据操作

数据查询

数据视图

MySQL索引

事务

MySQL存储引擎

MySQL用户管理

?

MySQL安装和配置

看视频教程就🆗了

图形化工具Navicat安装和使用

看视频教程就🆗了

数据库的创建和管理

1.创建数据库：create database 数据库名;

2.使用指定数据库：use 数据库名;

3.查看所有数据库：show databases;

4.查看当前使用数据库：select database();

5.删除数据库：drop database数据库名;

表的创建和管理

1.创建表：create table 表名(字段名1 数据类型,字段名2 数据类型……)；

2.查看当前数据库所有表：show tables;

3.导入表：souce+sql文件路径;（得先创建数据库）

4.删除表：drop table 表名;??? drop table if exists 表名;(先判断表是否存在，存在就删除);

5.查询所有字段：select * from 表名;（*表示所有字段）

表的列类型

累了

数据完整性约束

内容很多累了

数据库设计和规范化

????????????????????????????????????????????????????????????????? 第一范式：列不可再分?????

1.每一列属性都是不可再分的属性值，确保每一列的原子性

2.两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据

第二范式：属性完全依赖于主键

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主键

???? 第三范式：属性不依赖于其它非主属性 ???属性直接依赖于主键

数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。

数据操作

数据插入

1.插入一行数据：insert into 表名(字段名1,字段名2,字段名3…) values(值1,值2,值3…);

2.插入多行数据：insert into 表名(字段名1,字段名2,字段名3…) values(值1,值2,值3…),(值1,值2,值3…);

3.不加字段名表示按字段名顺序插入。

数据修改

update 表名 set 字段名1 = 值1,字段名2 = 值2… where 条件;

注意：不加条件会修改掉整张表的数据

数据删除

delete from 表名 where 条件;

注意：不加条件会删除掉整张表的数据

快速删除表中数据：truncate table 表名;(删除效率高)

数据查询

单表查询

1.查询表中字段下面的数据：select 字段名1,字段名2…from 表名;

2.条件查询：select 字段名1,字段名2…from 表名 where 条件;

3.查询时字段可以进行数学运算：select 字段名1*字段名2 from 表名 where 条件;

4.查询两值之间：select 字段名1,字段名2…from 表名 where 字段1 between A and B;

5.查询条件是某个值为空时，不能用字段名=null，要用字段名 is null

6.and和or查询：select 字段名1,字段名2…from 表名 where 条件1 and 条件2;(注意，条件2字段名不能少)

7.in和not in查询：select 字段名1,字段名2…from 表名 where 字段 in (值1,值2);表示包含值1和值2的数据

8.like模糊查询：

(1)’%字符%’，表示含有这个字符。

(2)’%字符’，表示以这个字符结尾。

(3)’字符%’，表示以这个字符开头。

(4)’%_A%’,表示第二个字符是A(_表示一个任意字符)

(5)’%\_%’，表示含有下划线

9.把相应的数据进行查询后修改

select 字段名1,

case

???? when 字段名2 = '在馆' then '1'

???? when 字段名2 = '借出' then '0'?

???? when 库存状态 = '丢失' then '-1'

???? end as 重命名

from 表名;

10.单行处理函数：lower转换小写，upper转换大写，substr取子串，concat字符串拼接，length取长度，frim去空格，round四舍五入。(语法自己查吧)

多表查询

1.连接查询(又称多表查询)分类：内连接有等值连接，非等值连接，自连接。???? 外连接有左连接和右连接。

2.等值连接：找表之间相等的字段作为where条件。例如：

select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;（1.表名后面接一个字母表示重命名(中间的as可以省略)，主要是简化代码。2. e.字段名表示哪一个表中的字段，这样写有助于提高检索效率。3.用on来表示等值连接这是SQL99语法，主要是为了区分进一步检索时在后面添加的where条件）

3.非等值连接：就是连接条件不是一个等值关系

4.自连接：一张表连接自己，把自己看成两个表。例如：select a.ename,b.ename from emp a join emp b where条件;

5.外连接：查询两张表条件匹配的数据之外，额外查询出其中一个表条件不匹配的数据,不匹配的数据会用null表示。(左右连接可以相互转换)例如：

select e.ename,d.dname from emp e right join dept d where条件;

分类汇总与排序

1.分组函数(多行处理函数)：count(计数)，sum(求和)，avg(平均值)，max(最大值)，min(最小值)。基本语法：select sum(字段名)……

注意事项：（1）分组函数自动忽略null的数据，例如你用count统计有多少行时，null的数据行不被计算进去

????????????? ?（2）分组函数不能直接使用在where子句中

2.分组查询：把字段名下各个相同的数据进行分组

语法：select…from…group by 字段名;（group by可接多个字段，表示联合分组）

3.having数据过滤：与group by联合使用。例如:select…from…group by 字段名 having max(工资) > 1000；它可以接分组函数，表示查询每个组中最大工资大于1000的数据。当然也可以用where进行过滤后再分组

4.distinct(去除查询结果中的重复记录)，语法：select distinct 字段名1，字段名2…(多个字段名表示联合去重)

5.order by排序：order by 字段名 asc/desc;(默认是升序可以省略asc，desc表示降序，order by也可以加多个字段，最前的字段起主导，只有当前面的字段因数据相同无法排序时才会按下一个字段排)

6.limit取出查询结果的一部分，语法limit startIndex(起始下标，0开始),length(长度)；例如：

select…from…order by…limit 5;表示取前5条数据。

而分页查询，每页显示pageSize条数据，第pageNo页：limit(pageNo-1)*pageSize,pageSize

7.union：将两个查询结果合并，select…… union select……;合并并且去除重复数据行，两个select语句查询字段名要相对应。

子查询

1.where子查询：在where子句中嵌套select语句。经典案例：查最低工资大于3000的员工(单表)

select * from 表名 where sal > (select min(sal) from 表名);

where子查询在要用到多个表时也可以用连接查询查出一样的结果，不过在要查的字段名都在一个表时可以用where子查询吧（其实连接查询更快，这样是调优的一种方式）

2.from子查询：将子查询的查询结果当作一张临时表。

案例：找出每个岗位的平均工资的薪资等级(工资和薪资等级不在同一个表)

思路：1.找出每个岗位的平均工资(按照岗位分组求平均值)

select job,avg(sal) from emp group by jop;

2.把上面的查询结果当成临时表

select t.*,s.grade

from (select job,avg(sal) as avgsal from emp group by jop)t

join salgrade s

on t.avg(sal) between s.losal and s.hisal;//一定要给分组函数后的字段起别名

join也可以接临时表

3.select子查询(感觉没必要掌握)

查询关键字语法顺序和执行顺序

1.语法书写顺序：select…from…where…group by…order by…

2.关键字执行顺序：1.fromà 2.where à3.group byà 4.selectà 5.order by（这其实就是分组函数不能直接写在where子句中的原因）

数据视图

基本概念

视图是一个虚拟表，是从数据库中一个或多个表中映射出来的表，其内容由查询定义。同真实表一样，视图包含一系列带有名称的列和行数据。但是，数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变，对视图进行操作也会影响到原表数据。

视图基本操作

1.创建视图：create view 视图名as select语句;

2.删除视图：drop view 视图名;

3.修改视图，即从新定义：alter view 视图名 as select语句;

4.查看创建视图时所用的指令：SHOW CREATE VIEW 视图名;

5.其他操作和表操作一样

视图的应用

1.安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样用户就可以查询自己需要的字段，不能查看保密的字段。

2.性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接join。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用join查询数据。

3.灵活。如果系统中有一张旧的表，这张表由于设计的问题即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样就可以少做很多改动，也达到了升级数据表的目的。

MySQL索引

基本概念

索引相当于一本书的目录，当数据库的数据量很大时，创建索引能加快检索速度，提高效率。索引有两种类型，即B-树索引和哈希索引。不同的存储引擎采用的索引类型等会有所不同。

索引分类

1.单一索引：一个字段上添加索引

2.复合索引：两个字段或更多的字段组合添加的索引

3.主键索引：主键上添加索引

4.唯一索引：具有unique约束的字段上添加的索引

索引的使用

1.创建索引：create index 索引名 on 表名(字段名)，例如：create index emp_ename_index on emp(ename);

2.删除索引：drop index emp_ename_index on emp;

3.查看SQL语句是否使用了索引：explain+select语句。例子：explain select * from 表名 where 字段名='xxx';

索引失效

1.select * from emp where ename like '%T';(以%开头是不会走索引)

2.使用or的时候会失效，要用的话or两边的字段都得有索引

3.使用复合索引的时候，没有使用左侧的列查找，索引失效

例子：create index emp_jop_sal_index on emp(job,sal);

????? select * from emp where job='manager';(用索引)

?? ???select * from emp where sal=800;(索引失效)

4.在where当中索引列参加了运算，索引失效，例如where sal+1=800;

5. 在where当中索引列使用了函数

注意事项

1. 主键和unique字段会自动添加索引

2. 不要随便添加索引，索引需要维护。索引是为了提高查询效率。(数据量大的时候使用)

3. 唯一性比较弱的字段上添加索引用处不大。

4. 索引底层原理是（平衡二叉树）甚至更复杂

事务

事务概念

一个事务其实就是一个完整的业务逻辑，只有增删改三个语句和事务有关。事务本质上其实就是DML语句同时成功，同时失败。

事务是怎么做到多条DML语句同时成功或同时失败的呢

1.InnoDB存储引擎提供一组用来记录事务性活动的日志文件，在执行事务的过程中，每一条DML语句的操作都会被记录到“事务性活动的日志文件”中。

2.在执行事务的过程中我们可以提交事务，也可以回滚事务。

3.提交事务即清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。提交事务标志着事务的结束。并且是一种全部成功的结束。

4.回滚事务即将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件。回滚事务标志着事务结束。并且是一种全部失败的结束。

事务特性

1. 事务四个特性：

A原子性：说明事务是最小的工作单元，不可再分。

B一致性：所有事务要求，在同一个事务当中，所有的操作必须同时成功或同时失败，以保证数据的一致性

C隔离性：A事务和B事务之间具有一定的隔离。

D持久性：事务最终结束的一个保障。事务提交，就相当于没有保存到硬盘上的数据保存到硬盘上

事务隔离级别

2.事务隔离级别：

（1）读未提交：read uncommitted(最低的隔离级别)，事务A可以读取到事务B未提交的数据（真实的）

这样出现的问题是：脏读现象。这种隔离级别一般的是理论上的，大多数大都是二档起步

（2）读已提交：read committed，事务A只能读取到事务B提交之后的数据

这样出现的问题是：解决了脏读，但是不可重复读取数据

（3）可重复读：repeatable read，事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变

这样出现的问题是：每次读取到的数据都是幻象，不够真实。(MySQL默认级别)

（4）序列化：serializable(最高的隔离级别)，表示事务排队，不能并发。效率最低。

事务基本操作

1. 查看当前事务隔离级别：select @@transaction_isolation;
2. 设置事务隔离级别：set global transaction isolation level read uncommitted（level后面加的是事务级别）；
3. 开始事务，关闭默认执行一条语句提交一次事务(实际开发中不合理)：start transaction；
4. 提交事务：commit；
5. 回滚事务：rollback；

MySQL存储引擎

基本概念

存储引擎是一个存储组织数据的方式。不同的存储引擎，表存储数据的方式不同。存储引擎是MySQL特有的一个概念。

常用存储引擎

1.MySQL默认存储引擎InnoDB：

???? （1）重量级存储引擎，最大的特点是支持事务。支持数据库崩溃后自动恢复，安全性高

???? （2）每个InnoDB表在数据库目录中以.frm格式文件表示

???? （3）InnoDB表空间tablespace被用于存储表的内容

???? （4）支持外键及引用的完整性，包括数据联合删除和更新

（5）多版本和行级锁定

优点：安全性高

2.MyISAM存储引擎：

???? （1）它使用散个文件表示每个表，格式文件存储表结构的定义(mytable.frm)，数据文件存储表行的内容(mytable.MYD)，索引文件存储表上的索引(mytable.MYI)

???? （2）MyISAM存储引擎的特点是可被转换为压缩、只读表来节省空间

优点：节省空间

3.MEMORY存储引擎：

???? （1）使用这种引擎的表，其数据存储在内存中，且行的长度固定，这使得MEMORY存储引擎非常快

（2）每个表在数据库目录中以.frm格式文件表示

（3）表数据及索引存储在内存中，查询数据快

（4）表级锁机制

（5）不能包含TEXT或BLOB字段

优点：查询效率最高，不需要和硬盘交互。?? 缺点：不安全，关机之后数据消失。

MySQL用户管理

自己查吧