Mysql

2020-08-04 本文已影响0人瞳师

一、 Mysql

1. Install

(1) 安装

(2) 卸载

去mysql的安装目录找到my.ini文件复制 datadir = "文件路径"
卸载mysql
删除路径下的MySQL文件夹

(3) 配置

mysql服务启动
1. 手动
2. cmd --> services.msc 打开服务的窗口
3. 使用管理员打开cmd
  - net start mysql : 启动mysql服务
  - net stop mysql : 关闭mysql服务
mysql登录
1. mysql -uroot -proot
mysql退出
1. exit
2. quit

2. SQL

(1) 概念

Structured Query Language: 结构化查询语言

(2) SQL通用语法

SQL语句不区分大小写,关键字建议使用大写
注释方式
- 单行注释: -- 注释内容 || # 注释内容(mysql特有)
- 多行注释: /* 注释 */

(3) SQL分类

DDL(Data Definition Language，数据定义语言): 定义数据库对象: 数据库,表,列 create,drop,alter
DML(Data Manipulation Language，数据操作语言): 增删改表中数据 insert,delete,update
DQL(Data Query Language，数据查询语言): 查询表中数据 select
DCL(Data Control Language，数据控制语言): 定义数据库访问权限 grant,revoke
TCL:(Transaction Control Language，事务控制语言)

3. DDL

(1) 操作数据库

1. C(Create) - 创建

创建数据库 - create database if not exists X character set gbk;

2. R(Retrieve) - 查询

查询所有数据库名称 - show databases;
查询数据库创建信息(字符集) - show create database X;

3. U(Update) - 更新

修改数据库字符集 - alter database 数据库名称 character set 字符集名称;

4. D(Delete) - 删除

删除数据库 - drop database X;

(2) 操作表

1. C(Create) - 创建

create table 表名(
    列名 数据类型
    ...
);

复制表 - create table 新表 like 被复制表;

2. R(Retrieve) - 查询

查询所有表名称 - show tables;
查询表结构 - desc 表名;

3. U(Update) - 更新

修改表名 - alter table 表名 rename to 新表名;
修改表字符集 - alter table character set 字符集;
添加一列 - alter table 表名 add 列名数据类型;
修改列名称/类型 - alter table 表名 change 旧列名新列名新数据类型;
删除一列 - alter table 表名 drop 列名;

4. D(Delete) - 删除

drop table if exists 表名;

4. DML

(1) 添加数据

insert into 表名(列名1,列名2...列名n) values (值1,值2,...值n);

除了数字类型,其他类型需要添加引号

(2) 删除数据

delete from 表名 [where 条件]
TRUNCATE TABLE 表名 删除表,再创建一张一模一样的空表

(3) 修改数据

update 表名 set 列名1 = 值1, 列名2 = 值2,...[where 条件]

5. DQL

-- 完整语法
select
    字段列表
from
    表名列表
where
    条件列表
group by
    分组字段
having
    分组之后条件
order by
    排序
limit
    分页限定

(1) 基础查询

去重复结果集: select distinct 列名 from 表名;
起别名: select math + englist as total from 表名;

IFNULL(exper1 , exper2) - 不为null,返回exper1,否则返回exper2

(2) 运算符

<> 不等号相当于 !=
between 左范围 and 右范围;(左右都为闭区间)
in(数据1,数据2)
is null / is not null

(3) 模糊查询

占位符: _: 单个任意字符; %: 多个任意字符
SELECT * FROM student WHERE NAME LIKE '小_';

(4) 排序查询

order by
排序方式
- ASC -> 升序
- DESC -> 降序
多个排序条件
- select * from student order by math DESC , english DESC;

(5) 聚合函数

将一列数据作为一个整体,进行纵向的计算

count - 计数
max - 最大值
min - 最小值
sum - 和
avg - 平均值

注意: 聚合函数的计算,排除null值

选择不包含非空的列进行计算: 主键

IFNULL函数

(6) 分组查询

语法: group by 分组字段;
- 分组之后查询的字段: 分组字段/聚合函数
where与having的区别?
- where在分组之前进行限定,如果不满足条件,则不参加分组.
- having在分组之后进行限定,如果不满足条件,则不会被查询出来.
- where后不可跟聚合函数,having可以进行聚合函数判断

(7) 分页查询

语法: limit开始的索引,每页查询的条数;
公式: 开始的索引 = (当前页码 - 1) * 条数
limit只在mysql中适用

6. 约束

对表中的数据进行限定,保证数据的正确性,有效性,完整性

(1) 主键约束(primary key)

primary key --> 自动增长(auto_increment) modify
删除: alter table 表名 drop primary key;
添加: alter table 表名 modify 列名数据类型 primary key

(2) 非空约束(not null)

值不能为空
增删: ALTER TABLE 表名 MODIFY 列名数据类型;

(3) 唯一约束(unique)

值不能重复, null 可以重复

删除: alter table 表名 drop index 列名
添加: alter table 表名 modify 列名数据类型 unique

(4) 外键约束(foreign key)

创建表时: constraint 外键名称 foreign key (外键字段名称) reference 主表名称(主表列名称)
删除: alter table 主表名称 drop foreign key 外键名称
添加: alter table 主表名称 add constraint 外键名称 foreign key (外键字段名称) reference 外键表名称(外键表列名称)

(5) 级联

谨慎使用

删除: 外键约束添加 --> on delete cascad
添加: 外键约束添加 --> on update cascad

7. 多表查询

笛卡尔积 --> 消除无用数据

(1) 内连接

隐式内连接
显示内连接
- select 字段列表 from table1 [inner] join table2 on 条件

(2) 外连接

左外连接/右外连接
- select 字段列表 from table1 left/right [outer] join table2 on 条件

(3) 子查询

查询中嵌套查询,嵌套查询为子查询

8. 多表间的关系

(1) 分类

一对一(唯一外键)
一对多(在多的表中,添加外键)
多对多(采用中间表记录)

(2) 数据库范式

1. 分类

第一范式(1NF): 每一个列都是不可拆分的原子项
第二范式(2NF): 建立在第一范式基础上，消除部分依赖
第三范式(3NF): 建立在第二范式基础上，消除传递依赖。

BCNF:

1.所有非主属性对每一个码都是完全函数依赖。

2.所有的主属性对每一个不包含它的码,也是完全函数依赖。

3.没有任何属性完全函数依赖于非码的任何一组属性。

2. 概念

完全函数依赖: X’是X的真子集，存在X→Y，但对每一个X’都有X’!→Y，则称Y完全函数依赖于X
部分函数依赖：存在X→Y，若X’是X的真子集，存在X’→Y，则称Y部分函数依赖于X
传递函数依赖: 设X,Y,Z是关系R中互不相同的属性集合，存在X→Y(Y !→X),Y→Z，则称Z传递函数依赖于X
码：表中可以唯一确定一个元组的某个属性（或者属性组），如果这样的码有不止一个，那么大家都叫候选码，我们从候选码中挑一个出来做老大，它就叫主码
全码：如果一个码包含了所有的属性，这个码就是全码。
主属性：一个属性只要在任何一个候选码中出现过，这个属性就是主属性。
非主属性：与上面相反，没有在任何候选码中出现过，这个属性就是非主属性。
外码：一个属性（或属性组），它不是码，但是它别的表的码，它就是外码。

(3) 数据库的备份与还原

1. 命令行:

备份: mysqldump -u用户名 -p密码数据库名称 > 保存路径.sql
还原: sourse 文件路径

9. 事务

Transaction

(1) 基本介绍

如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败

开启事务: start transaction
回滚: rollback
提交: commit
- 查看事务的默认提交方式: select @@autocomimt;(1为自动,0为手动)
- 修改事务的默认提交方式: set @@autocomimt = x;

(2) 四大特征

原子性(Atomicity): 事务是不可分割的最小操作单位,要么同时成功,要么同时失败
持久性(Durability): 当事务提交或回滚后,数据库会持久化的保存数据
隔离性(Isolation): 多个事务之间,互相独立
一致性(Consistency): 事务操作前后,数据总量不变

(3) 隔离级别

多个事务之间是隔离的,互相独立的.如果多个事务操作同一批数据,会引发一些问题,设置不同的隔离级别可以解决这些问题

存在问题
1. 脏读: 一个事务,读取到另一个事务中没有提交的数据
2. 不可重复读(虚读): 在同一个事务中,两次读取到的数据不同
3. 幻读: 一个事务(DML)数据库中的所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改
隔离级别
1. read unccommitted: 读未提交
  问题: 脏读/虚读/幻读
2. read committed: 读已提交(Oracle默认)
  问题: 虚读/幻读
3. repeatable read: 可重复读(MySQL默认)
  问题: 幻读
4. serializable: 串行化
注意: 隔离级别从小到大安全性越来越高,效率越来越低

(4) 具体操作

查询隔离级别

SELECT @@tx_isolation;

修改隔离级别

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

10. 数据类型

(1) 数据对应

类型名称	显示长度	数据库类型	JAVA类型	JDBC类型索引(int)

VARCHAR	L+N	VARCHAR	java.lang.String	12
CHAR	N	CHAR	java.lang.String	1
BLOB	L+N	BLOB	java.lang.byte[]	-4
TEXT	65535	VARCHAR	java.lang.String	-1

INTEGER	4	INTEGER UNSIGNED	java.lang.Long	4
TINYINT	3	TINYINT UNSIGNED	java.lang.Integer	-6
SMALLINT	5	SMALLINT UNSIGNED	java.lang.Integer	5
MEDIUMINT	8	MEDIUMINT UNSIGNED	java.lang.Integer	4
BIT	1	BIT	java.lang.Boolean	-7
BIGINT	20	BIGINT UNSIGNED	java.math.BigInteger	-5
FLOAT	4+8	FLOAT	java.lang.Float	7
DOUBLE	22	DOUBLE	java.lang.Double	8
DECIMAL	11	DECIMAL	java.math.BigDecimal	3
BOOLEAN	1	同TINYINT

ID	11	PK (INTEGER UNSIGNED)	java.lang.Long	4

DATE	10	DATE	java.sql.Date	91
TIME	8	TIME	java.sql.Time	92
DATETIME	19	DATETIME	java.sql.Timestamp	93
TIMESTAMP	19	TIMESTAMP	java.sql.Timestamp	93
YEAR	4	YEAR	java.sql.Date	91

二、Mysql高级

(1) 索引

1. 概述

帮助MySQL高效获取数据的数据结构(有序)

2. 优势劣势

优势

类似于书籍的目录索引,提高数据检索的效率,降低数据库的IO成本。
通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗

劣势

实际上索引也是一张表,该表中保存了主键与索引字段,并指向实体类的记录,所以索引列也是要占用空间的
虽然索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行NERT、 URDATE、 DELETE.因为更新表时,MSQL不仅要保存数据,还要保存一下索引文件,每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息

3. 结构

BTree

BTree又叫多路平衡搜索树,一颗m叉的 BTree特性如下
- 树中每个节点最多包含m个孩子
- 除根节点与叶子节点外,每个节点至少有[ceil(m / 2)]个孩子
- 若根节点不是叶子节点,则至少有两个孩子
- 所有的叶子节点都在同一层
- 每个非叶子节点由n个key与n + 1个指针组成,其中[ceil(m / 2) - 1] < n < m - 1

4. 分类

单值索引：
唯一索引：
复合索引：

5. 语法

创建索引：CREATE INDEX (index_name) ON (table_name(column_name))
删除索引：DROP INDEX (index_name) ON (table_name)
查看索引：SHOW INDEX FROM (table_name) [\G]
ALTER：
- alter table tb_name add primary key (column_list):
  该语句添加一个主键,这意味着索引值必须是一的,且不能为NULL
- alter table tb_name add unique index_name(column_list)
  这条语句创建索引的值必须是唯一的(除了NUL外,NULL可能会出现多次)
- alter table tb_name add index index_name(column_list):
  添加通索引,索引值可以出现多次
- alter table tb_name add fulltext index_name (column_list):
  该语句指定了索引为 FULL TEXT,用于全文索引

6. 设计原则

(2) 视图

1. 创建/修改

CREATE [OR REPLACE] [ALGORITHM [UNDEFINED I MERGE I TEMPTABLE]
VIEW view_name [(column_list)]
AS select statement
[WITH [CASCADED I LOCAL] CHECK OPTION]

2. 查看

show tables;

3. 删除

drop view if exists (view_name);

(3) 存储过程/函数

存储过程

1. 创建

delimiter $

create procedure procedure_name(参数列表)

begin

        存储过程体（一组合法的SQL语句）

end $

delimiter ;

2. 查看

show procedure status[\G];

3. 删除

drop procedure [if exists] procedure_name;

函数

(4) 触发器

1. 创建

create trigger trigger-name
before/after insert/update/delete
on table_name
[for each row]
trigger_stmt;

(5) 存储引擎

1. Innodb

2. MyISAM

3. MEMORY

4. MERGE

(6) 优化SQL步骤

一般步骤概要：

通过 show status 命令了解各种sql的执行频率

show [session | global] status like 'Com_______;

定位执行效率较低的sql语句
通过explain分析低效sql的执行计划

id - 执行优先级数字越大，优先级越高
select_type

通过 show profile 分析sql
通过trace分析优化器如何选择执行计划
确定问题并采取相应的优化措施

Mysql

一、 Mysql

1. Install

2. SQL

3. DDL

(1) 操作数据库

(2) 操作表

4. DML

(1) 添加数据

(2) 删除数据

(3) 修改数据

5. DQL

(1) 基础查询

(2) 运算符

(3) 模糊查询

(4) 排序查询

(5) 聚合函数

(6) 分组查询

(7) 分页查询

6. 约束

(1) 主键约束(primary key)

(2) 非空约束(not null)

(3) 唯一约束(unique)

(4) 外键约束(foreign key)

(5) 级联

7. 多表查询

(1) 内连接

(2) 外连接

(3) 子查询

8. 多表间的关系

(1) 分类

(2) 数据库范式

(3) 数据库的备份与还原

9. 事务

(1) 基本介绍

(2) 四大特征

(3) 隔离级别

(4) 具体操作

10. 数据类型

(1) 数据对应

二、Mysql高级

(1) 索引

1. 概述

2. 优势劣势

3. 结构

4. 分类

5. 语法

6. 设计原则

(2) 视图

1. 创建/修改

2. 查看

3. 删除

(3) 存储过程/函数

1. 创建

2. 查看

3. 删除

(4) 触发器

1. 创建

(5) 存储引擎

1. Innodb

2. MyISAM

3. MEMORY

4. MERGE

(6) 优化SQL步骤

猜你喜欢

热点阅读