title: mysql review
top: 43
date: 2021-09-09 20:04:25
tags:
- mysql
categories:
- Databases
数据库基础概念
- 什么是数据库？
DB（database），按照一定格式存储数据的一些文件的组合。
- 数据库管理系统
DBMS（database management system）,专门用来管理数据库中数据的，可以对数据库当中的数据进行增删改查。
常见的数据库管理系统：MySQL、Oracle、MS SqlServer、DB2、sybase等…
- SQL：结构化查询语言
程序员需要学习SQL语句，程序员通过编写SQL语句，然后DBMS负责执行SQL
语句，最终来完成数据库中数据的增删改查操作。SQL是一套标准，程序员主要学习的就是SQL语句，这个SQL在mysql中可以使用，同时在Oracle中也可以使用，在DB2中也可以使用。
- 三者之间的关系
DBMS–执行–> SQL --操作–> DB
- 在windows操作系统当中，怎么使用命令来启动和关闭mysql服务呢？
net stop 服务名称;
net start 服务名称;
- windows系统下，登陆mysql数据库:
mysql -uroot -p******;退出mysql：exit - 修改密码：
set password for 用户名@localhost=password('新密码') - 忘记root密码
- 关闭正在运行的MySQL服务。
- 打开DOS窗口，转到mysql\bin目录。
- 输入mysqld --skip-grant-tables 回车。–skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。
- 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），输入mysql回车，如果成功，将出现MySQL提示符 >。
- 连接权限数据库： use mysql;
- 改密码：update user set password=password(“新密码”) where user=“root”;
- 刷新权限（必须步骤）：flush privileges;
- 退出mysql quit；
--解决mysql中文显示错误问题
mysql --default-character-set=latin1 -uroot -p***
--在登录mysql时输入以下命令，解决对齐问题
show character set;
--查看所有字符集
show variables like 'character_set%';
--查看服务器默认对外处理的字符集
set character_set_client=gbk;
--修改服务器默认对外客户端的字符集为gbk
set character_set_results=gbk;
--修改服务器给定数据的字符集为gbk
set names gbk;
--快捷设置字符集DDL、DML和DCL
- DDL( data definition language ) 数据定义语言，用于操作对象和对象的属性 ,这种对象包括数据库本身，以及数据库对象，像：表、视图等等
操作语句：
1.CREATE - 创建
2.ALTER - 修改
3.DROP - 删
4.TRUNCATE - 移除
5.COMMENT - 注释
6.RENAME - 重命名
- DML( Data Manipulation Language ) 数据操作语言，SQL中处理数据等操作统称为数据操纵语言
操作语句：
1.SELECT - 查询
2.INSERT - 添加
3.UPDATE - 更新
4.DELETE - 删除
5.CALL - 调用
6.EXPLAIN - 解释
7.LOCK TABLE - 锁，用于控制并发
- DCL( Data Control Language ) 数据控制语言，用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等
操作语句：
1.GRANT - 授权 允许对象的创建者给某用户或某组或所有用户(PUBLIC)某些特定的权限。
2.REVOKE - 收回已经授予的权限
3.COMMIT - 提交
4.SAVEPOINT - 保存点
5.ROLLBACK - 回滚
6.SET TRANSACTION - 设置当前事务的特性，它对后面的事务没有影响
- DQL：数据查询语言（凡是带有select关键字的都是查询语句）
- TCL：事务控制语言。包括：事务提交：commit;事务回滚：rollback;
mysql的数据类型
各数据类型及字节长度一览表：
数据类型 | 字节长度 | 范围或用法 |
Bit | 1 | 无符号[0,255]，有符号[-128,127]，备注：BIT和BOOL布尔型都占用1字节 |
TinyInt | 1 | 整数[0,255] |
SmallInt | 2 | 无符号[0,65535]，有符号[-32768,32767] |
MediumInt | 3 | 无符号[0,224-1]，有符号[-223,2^23-1]] |
Int | 4 | 无符号[0,232-1]，有符号[-231,2^31-1] |
BigInt | 8 | 无符号[0,264-1]，有符号[-263 ,2^63 -1] |
Float(M,D) | 4 | 单精度浮点数。提醒这里的D是精度，如果D<=24则为默认的FLOAT，如果D>24则会自动被转换为DOUBLE型。 |
Double(M,D) | 8 | 双精度浮点。 |
Decimal(M,D) | M+1或M+2 | 未打包的浮点数，用法类似于FLOAT和DOUBLE，如果在ASP中使用到Decimal数据类型，直接从数据库读出来的Decimal可能需要先转换成Float或Double类型后再进行运算。 |
Date | 3 | 以YYYY-MM-DD的格式显示，比如：2009-07-19 |
Date Time | 8 | 以YYYY-MM-DD HH:MM:SS的格式显示，比如：2009-07-19 11：22：30 |
TimeStamp | 4 | 以YYYY-MM-DD的格式显示，比如：2009-07-19 |
Time | 3 | 以HH:MM:SS的格式显示。比如：11：22：30 |
Year | 1 | 以YYYY的格式显示。比如：2009 |
Char(M) | M | 定长字符串。 |
VarChar(M) | M | 变长字符串，要求M<=255 |
Binary(M) | M | 类似Char的二进制存储，特点是插入定长不足补0 |
VarBinary(M) | M | 类似VarChar的变长二进制存储，特点是定长不补0 |
Tiny Text | Max:255 | 大小写不敏感 |
Text | Max:64K | 大小写不敏感 |
Medium Text | Max:16M | 大小写不敏感 |
Long Text | Max:4G | 大小写不敏感 |
TinyBlob | Max:255 | 大小写敏感 |
Blob | Max:64K | 大小写敏感 |
MediumBlob | Max:16M | 大小写敏感 |
LongBlob | Max:4G | 大小写敏感 |
Enum | 1或2 | 最大可达65535个不同的枚举值 |
Set | 可达8 | 最大可达64个不同的值 |
增删改查操作
- 数据库命令以英文的分号结尾
- sql对大小写不敏感
- 库操作
# 创建数据库
create database news;
# 显示已有数据库
show databases;
# 选用数据库
use news;
# 删除数据库
drop news;- 表创建
# 选用数据库后，创建表
use databases;
ceate table news_table(
id int primary key,
title varchar(100) NOt NULL,
category varchar(10) NOT NULL,
content text,
tags tinyint
);
# 显示表结构
desc news_table;
# 查看表建立语句
show create table news_table;
# 删除表
drop news_table;
# 清空表数据，保留表结构
truncate news_table;
# 添加唯一约束
alter table news_table add unique(title);
# 删除唯一约束
alter table news_table drop index title;
# 设置主键自增
alter table news_tale change id id auto_incrament;
# 设置缺省值
alter table news_table alter column tag set default 0;
# 复合主键
CREATE TABLE `w_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;表/增(insert)
# 1. 不指定插入数据的列名，直接插入值
insert into news_table values ('title1','category1','content1',1);
# 2. 指定列名插入值
insert into news_table (title,category,content) values('title2','category2','content2');
3. 批量插入值
insert into news_table values ('title1','category1','content1',1),
('title2','category1','content1',1),
('title3','category1','content1',1),
('title4','category1','content1',1);表/查(select)
# 基本语法
select column_name,column_name from table_name [where clause] [limit n][offset M]
# 查询表中所有列
select * from news_table;
# 查询表中指定列
select news_table.title,content,tag form news_table;
# 指定列别名
select title as T,content as C from news_table;
# 对字段做四则运算
select tag+10 form news_table;
# 显示表中前3行
select top 3 *from news_table;
# 显示表中前30%的行数据
select 30 percent *from news_table;
# 消除指定列内重复数据
select distinct title from news_table;# 排序，asc为升序（默认），desc为降序
select *from news_table order by tag asc,title desc;
# 条件查询中可以使用的运算符
# 比较运算符 <=，<，=，>，>=，!=，<>，!>，!<
# 范围运算符 between... and，not between... and
# 列举运算符 in，not in
# 模糊匹配运算符 like，not like
# 空值运算符 is null，is not null
# 逻辑运算符 and，or，not
# 取表中id值在10到20的数据
select *from news_table where id>=10 and id<=20;
select *from news_table where id between 10 and 20;
# 显示表中id值为3,12,21的数据
select *from news_table where id in (3,12,21);
select *from news_table where id=3 or id=12 or id=21;
# 显示表中id值不为3,12,21的数据//逻辑运算符：and, or,not...
select *from news_table where id not in (3,12,21);# like模糊查询，%任意位，_一位，^取反
# 查询某字段中以1结尾的数据
select *from news_table where title like '%1';
# 查询id字段值第二个字符为2的数据
select *from news_table where id like '_2%';
# 查询id字段值不在10-20之间的数据
select *from news_table where id like[^10-20];# join联合查询.inter join..on..内连接，将两表连接查询，显示交集//left join..on..左外连接，匹配两表数据显示左表数据//right join..on..右外连接，类左外//full join..on..全连接，保留两表数据（mysql不支持全链接）
seletc *from table_a a inner join table_b b on a.id=b.id;# 取a,b交集
seletc *from table_a a left join table_b b on a.id=b.id;# 取a表
seletc *from table_a a right join table_b b on a.id=b.id;# 取b表
seletc *from table_a a left join table_b b on a.id=b.id where b.id is null;# 取a表,且去除b表数据；仅在a表
# ---实现全连接---利用union查询合并实现全链接
# 取a,b表所有数据
seletc *from table_a as a left join table_b b on a.id=b.id
union
seletc *from table_a a right join table_b b on a.id=b.id;
# 取a,b表无交集外的所有数据
seletc *from table_a a left join table_b b on a.id=b.id where b.id is null
union
seletc *from table_a a right join table_b b on a.id=b.id where a.id is null;# limit基本语法
select *from table limit [offset,] rows|rows offset offsetv;
# 显示查找记录11-25
select *from news_table limit 11,25;
select *from news_table limit 15 offset 10;
# 显示查找记录11以后数据,指定第二个参数为-1
select *from news_table limit 11,-1;# 单行处理函数：一个输入对应一个输出
# lower 转换小写
select lower(ename) as ename from emp;
# upper 转换大写
select upper(name) as name from t_student;
# substr 取子串（substr( 被截取的字符串, 起始下标,截取的长度)）注意：起始下标从1开始，没有0.
select substr(ename, 1, 1) as ename from emp;
# concat函数进行字符串的拼接
select concat(empno,ename) from emp;
# length 取长度
select length(ename) enamelength from emp;
# trim 去空格
select * from emp where ename = trim(' KING');
# str_to_date 将字符串转换成日期
# date_format 格式化日期
# format 设置千分位
# round 四舍五入
select round(1236.567, 0) as result from emp; //保留整数位。
# rand() 生成随机数
select round(rand()*100,0) from emp; // 100以内的随机数
# ifnull 可以将 null 转换成一个具体值。ifnull是空处理函数。专门处理空的。在所有数据库当中，只要有NULL参与的数学运算，最终结果就是NULL。
select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp;# 分组函数。分组函数不能写在where之后（where执行时，数据还未分组）
# 统计数据条数count
select count(*) as dn from news_table where tag=1;
# 取列最大值max//min为最小值
select max(id) as mxid from news_table;
# 获取查询行的id平均值avf//sum为求和
select avg(id) as avgid from news_table;# group by语句根据一个或多个列对结果进行分组。在分组的列上可以使用count，sum等函数
select ... from ... where ... group by ...
SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+--------+----------+
| name | COUNT(*) |
+--------+----------+
| 小丽 | 1 |
| 小明 | 3 |
| 小王 | 2 |
+--------+----------+
3 rows in set (0.01 sec)# where与having
# 1. where和having都可以使用的场景
select goods_price,goods_name from sw_goods where goods_price > 100
select goods_price,goods_name from sw_goods having goods_price > 100
# 2. 只可以用where，不可以用having的情况
select goods_name,goods_number from sw_goods where goods_price > 100
select goods_name,goods_number from sw_goods having goods_price > 100 # 报错！！！因为前面并没有筛选出goods_price 字段
# 只可以用having，不可以用where情况
select goods_category_id , avg(goods_price) as ag from sw_goods group by goods_category having ag > 1000
select goods_category_id , avg(goods_price) as ag from sw_goods where ag>1000 group by goods_category # 报错！！因为from sw_goods 这张数据表里面没有ag这个字段
# 利用having过滤分组查询后的数据# 子查询：select语句中嵌套select语句，被嵌套的select语句称为子查询
select ...(select)... from ... (select) ... where ... (select)
select title from news_table where id> (select count(tag) from news_table);
select text from (select *from news_table where tag>0);union与union all
- UNION 的语法如下：
[SQL 语句 1]
UNION
[SQL 语句 2]- UNION ALL 的语法如下：
[SQL 语句 1]
UNION ALL
[SQL 语句 2]
- 对重复结果的处理：UNION在进行表链接后会筛选掉重复的记录，Union All不会去除重复记录。
- 对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。
从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。
表/删(delete)
# 删除指定行
delete from news_table where id=3;
# 删除所有行
delete *from news_table; // delete from nes_table;表/改(update)
Update 语句用于修改表中的数据
update news_table set title='update' where id=1;视图
视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。
为什么要使用视图？
1）简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
2）安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
3）数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。
# 创建视图
create view vnews as select *from news_table [with check option];# []中为可选项，作用是保证数据的安全性
# 查看视图
show create view;
# 修改视图
alter view vnews as select *from news_table where tag=1 [with check option];
# 删除视图
drop view [if exists] vnews [, view_name];索引
索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。
一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
在mysql数据库当中索引也是需要排序的，并且这个索引的排序和TreeSet数据结构相同。TreeSet（TreeMap）底层是一个自平衡的二叉树！在mysql当中索引是一个B-Tree数据结构。
遵循左小右大原则存放。采用中序遍历方式遍历取数据。
- 提醒1：在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，因为id是PK。另外在mysql当中，一个字段上如果有unique约束的话，也会自动创建索引对象。因此，通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。
- 提醒2：在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
- 提醒3：在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。（自平衡二叉树：B-Tree）
索引失效
使用explain [查询语句]可查看是否使用了索引
-
select * from emp where ename like '%T';原因是因为模糊匹配当中以“%”开头了！尽量避免模糊查询的时候以“%”开始。这是一种优化的手段/策略。 - 使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会实现。所以这就是为什么不建议使用or的原因。
- 使用复合索引的时候，没有使用左侧的列查找，索引失效
- 在where当中索引列参加了运算，索引失效。
- 在where当中索引列使用了函数
# 创建索引：
create index emp_ename_index on emp(ename); # 给emp表的ename字段添加索引，起名：emp_ename_index
# 删除索引：
drop index emp_ename_index on emp; # 将emp表上的emp_ename_index索引对象删除。什么条件下，我们会考虑给字段添加索引呢
- 条件1：数据量庞大
- 条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
- 条件3：该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）
存储引擎
- 什么是存储引擎，有什么用呢？
存储引擎是MySQL中特有的一个术语，其它数据库中没有。（Oracle中有，但是不叫这个名字）实际上存储引擎是一个表存储/组织数据的方式。不同的存储引擎，表存储数据的方式不同。mysql默认的存储引擎是：InnoDB；mysql默认的字符编码方式是：utf8
- 可以在建表的时候给表指定存储引擎。
CREATE TABLE `t_student` (
`no` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`cno` int(11) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `cno` (`cno`),
CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8- MyISAM存储引擎
它管理的表具有以下特征：
使用三个文件表示每个表：
格式文件 — 存储表结构的定义（mytable.frm）
数据文件 — 存储表行的内容（mytable.MYD）
索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。对于一张表来说，只要是主键，或者加有unique约束的字段上会自动创建索引。
MyISAM存储引擎特点：
可被转换为压缩、只读表来节省空间
- InnoDB存储引擎
这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。
InnoDB支持事务，支持数据库崩溃后自动恢复机制。
InnoDB存储引擎最主要的特点是：非常安全。它管理的表具有下列主要特征：
- 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
- InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。）
- 提供一组用来记录事务性活动的日志文件
- 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
- 提供全 ACID 兼容
- 在 MySQL 服务器崩溃后提供自动恢复
- 多版本（MVCC）和行级锁定
- 支持外键及引用的完整性，包括级联删除和更新
InnoDB最大的特点就是支持事务：
- 以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读
- 不能很好的节省存储空间
- MEMORY存储引擎
使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得 MEMORY 存储引擎非常快。
MEMORY 存储引擎管理的表具有下列特征：
- 在数据库目录内，每个表均以.frm 格式的文件表示。
- 表数据及索引被存储在内存中。（目的就是快，查询快！）
- 表级锁机制。
- 不能包含 TEXT 或 BLOB 字段。
MEMORY引擎优点:
- 查询效率是最高的。不需要和硬盘交互。
- 不安全，关机之后数据消失。因为数据和索引都是在内存当中。
事务
一个事务其实就是一个完整的业务逻辑。是一个最小的工作单元。不可再分。本质是批量的DML语句同时成功，或者同时失败！
什么是一个完整的业务逻辑？
假设转账，从A账户向B账户中转账10000.
将A账户的钱减去10000（update语句）
将B账户的钱加上10000（update语句）
这就是一个完整的业务逻辑。
以上的操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分。这两个update语句要求必须同时成功或者同时失败，这样才能保证钱是正确的。
- 事务的4个特性
- 原子性 | 说明事务是最小的工作单元。不可再分。
- 一致性 | 所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。
- 隔离性 | A事务和B事务之间具有一定的隔离。
- 持久性 | 事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上！
- 事务是怎么做到多条DML语句同时成功和同时失败的
InnoDB存储引擎：提供一组用来记录事务性活动的日志文件
事务开启了： insert insert insert delete update update update 事务结束了！
在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。在事务的执行过程中，我们可以提交事务，也可以回滚事务。
提交事务
清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。
提交事务标志着，事务的结束。并且是一种全部成功的结束。回滚事务
将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件
回滚事务标志着，事务的结束。并且是一种全部失败的结束。
- 怎么提交事务，怎么回滚事务
提交事务：commit; 语句
回滚事务：rollback; 语句（回滚永远都是只能回滚到上一次的提交点！）
事务：transactionmysql默认情况下是支持自动提交事务的。（自动提交：每执行一条DML语句，则提交一次！）
关闭自动提交机制：start transaction;
数据库设计三范式
设计数据库表的时候，按照三范式进行，可以避免表中数据的冗余，空间的浪费。
实践和理论有的时候有偏差。
最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。因为在sql当中，表和表之间连接次数越多，效率越低。（笛卡尔积）有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql语句的编写难度也会降低。- 第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
- 第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，
不要产生部分依赖。 - 第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，
不要产生传递依赖。
表设计总结：
- 一对多：一对多，两张表，多的表加外键
- 多对多：多对多，三张表，关系表两个外键
- 一对一：单表
其他
-
set statistics time on;打开消息栏显示详细执行时间，off为关闭 -
format(name,'$n')格式化数据显示方式 -
str_to_date('字符串日期','日期格式')将字符串转换成date对应的日期类型。str_to_date('01-10-2021','%d-%m-%y');在mysql中若字符串格式为’%y-%m-%d’(‘2021-10-01’)，则可以直接作插入操作，会进行自动格式转换 -
date_format('日期类型数据','日期格式')；在查询显示时，若不使用日期格式话，则显示为默认格式%y-%m-%d(2021-10-01) -
date：段日期，包括年月日；datetime：长日期，包括年月日时分秒 -
show engines \G;查看mysql支持的所有存储引擎的类型 - 数据导出：
mysqldump [数据库名] [存储路径] -uroot -p...-->在命令行执行即可，不需登录mysql数据库。 - 数据导入：1.登录mysql数据库服务；2.创建数据库；3.进入该数据库；4.加载数据：
source [文件路径]
参考链接
[1] MySQL基础 — 常用命令
[2] MYSQL中数据类型介绍
[3] MySQL 教程
[4] 老杜带你学_mysql入门基础
>
