MySQL数据库知识总结（配合操作更快上手）

0：序

秋招之际，对于应届大学生而言，如果简历上能够增加上关于数据库的一些相关知识，无疑是锦上添花，而不论是网上的一些课程还是书籍看起来都过于繁琐，这篇知识总结也是我自己学习数据可路程上所进行的总结和积累，希望帮助到更多的同伴，秋招加油！（可以收藏起来分几天时间看）

1. 初试数据库

守护进程：
在我们查看mysql端口的时候会发现，会存在一个mysql_safe的存在，这也就是我们所说到的的守护进程，守护进程在判断mysql服务器是否运行正常，如果mysql使用过程当中，服务端挂掉了，守护进程就会重新拉起来mysql服务端，保证muysql的正常工作。

mysql服务端：

对于域套接字的话，其本质上就是本地机器当中创建一个文件，客户端和服务端通过文件来进行通信，本质上就是进程间通信，这个进程间通信会走网络协议栈。（代码如下）

数据库就像是一个文件夹一般，管理着诸多的文件，也就是我们的数据表。

• 查看mysql当中有多少个数据库：show databases;
• 查看当前数据库当中的数据表：show tables;
• 切记sql语句的结尾必须加上分号；

2. 数据库基础操作

字符集：

校对规则：

• 影响了用户对数据查询的一个排序
• 后缀为_cs：大小写敏感的校对规则，后缀为_ci：大小写不敏感的校对规则，后缀为_bin：二进制校对规则，大小写敏感
• 校对规则的特征：不同的字符集有着不同的校对规则，每一个字符集也都有着自己一个默认的校对规则，如utf8的校对规则是 utf_general_ci

1. 创建数据库
   create database helloworld charset=utf8 collate utf8_general_ci;
   创建一个使用utf字符集，并带校对规则的 db3 数据库。

• 为何要使用指定字符集呢？
  这也是为了避免乱码的出现，乱码的来源就是由于前后端字符集不匹配的原因造成的，因此在我们创建数据库的时候尽可能地指定字符集，尽量数据的字符集设置成utf8！！
  

• 查看已经创建好的数据库的创建语句
  show create database helloworld；

2. 使用数据库
   use helloworld;
3. 删除数据库
   删除连带数据库当中的表也删除掉drop database；
4. 创建表
   创建表的时候，设置列的名称以及列的属性：create table [表名称](列的名称 列的类型 comment ' 注释'， ...)
   插入数据的时候，插入一行数据，包含了多个列的属性，而注释则是方便后来者查看数据时知道此数据表示什么。
5. 查看和修改表字段
   查看表字段：desc [表字段]
   
   修改表字段

• 新增： alter table [表名称] add [字段的名称][ 字段的类型]aftre[字段的名称];
• 删除： alter table [表的名称]drop [字段的名称];如果删除某一个列，则该列的数据也随之删除
• 修改： alter table [表名称] modify [字段的名称][修改后的字段类型]
• 表的重命名：alter table [待命名的表名称]rename to [重命名后的名字]；
• 列名称的重命名：alter table[ 表名称] change [字段名][修改后的名称][字段属性]；
• 删除表： drop table 表名称；

6. 表的基本操作

• 增加数据
  全列增加：insert into 表名称 values （表字段对应的值，....）
  指定列插入：insert into 表名称 （表中的列名称）values（指定表字段对应的值，....）
  一次插入多行数据：insert into 表名称 values （表字段对应的，...） ，（表字段对应的值，....）
  一次插入一列的多行数据：insert into 表名称 （表中的列名称）values（指定表字段对应的值，...），（指定表字段对应的值，...）
  更新数据：update 表名称 set 字段=‘ 内容’；
• 简单查询
  全列查询： select * from [表名称]；
  指定列查询：select 列名称1，列名称2 ，列名称3 from 表名称；
  表达式当中不包含列字段：select 列名称1，列名称2，表达式 from 表名称
  表达式中包含多个字段：select 列名称1+列名称2+列名称3+...from 表名称
  为查询结果定义别名：select 列名称1+列名称2+列名称3 别名...from 表名称

7. 备份和恢复（此操作在数据库外操作）
   数据库备份：mysqldump -p 端口 -u 用户 -p 密码 -B 数据库的名称 > 文件
   数据表备份：mysqldump -p 端口 -u 用户 -p 密码 -B 数据库的名称 表名称1 表名称2 > 文件
   

3. 数据类型

1. tinyint类型 ：占用1哥字节，表示范围（-128~127）

• 在my.cnf当中mysqld标签下增加“sql_mode=STRICT_ALL_TABLES”，增强数据库对插入数据的类型范围的校验
• 如果在设计数据库表字段的时候，涉及到整数数据，尽量不要用tinyint（根据实际情况），而是用表示范围大的整型类型
• 对于整形数据，在设计的时候，尽量少用unsigned，因为一个小的数字，减去一个大的数字，可能会导致结果出错。

2. BIT类型

• 如果设置BIT类型，它的值时按照ASCII码来表示的
• 如果只想存储0和1，则BIT类型的比特位数设置成1

3. 小数 float和decimal两种

• float（4，2） 表示的范围是-99.99~99.99 MySQL在保存值是会自动进行四舍五入，如果是无符号的话，float（4，2）unsigned则只能够表示0~99.99，float精度大约为7位
• decimal的精度更加准确，因此如果我们希望某个数据表示高精度，则选择decimal，它的整数位65，小数位30.

4. 字符串类型

• char(L) 固定长度的字符串，L不是占用字节的大小，而是字符串的长度，字符占用字节的数量和数据库的编码格式息息相关，LATIN1：1个字符占用一个字节；utf8L一个字符占用3个字节 ==》 char(4)： 4*3=12字节
• varchar(L)：可变长度的字符串，L表示字符长度，到底可以存储多少个字节，和数据库的编码格式息息相关，理论上可以存储65535个字节，如果编码格式表示字符不用1个字节表示，而用n个字符来表示，则字符串长度位（65535-（1~3））/n； 如：utf8 则是（65535-3）/3；
• 对于char而言定义后就自动开辟号空间，而varchar的话，在定义的范围内，存储多少字符串，就开辟多大的空间，但是并不刻意超过固定的字符串长度，且它需要1~3个字节来保存字符串长度。

5. 日期和时间类型

• datatime：时间日期类型：YYYY-MM-DD HH:mm:SS 需要自己插入时间
• date ：只表示年月日
• timestamp：时间戳类型，可以自己计算年月日时分秒

6. enum和set

• enum枚举，也就是单选，如果插入的不是枚举出来的值，则就会报错
• set集合，多选，可以选择其中任意多个值机型组合保存，但是如果不在列举出来的值，就会报错。
• 查询语句：select * from 表名称 where 表字段= ‘内容’在这个表中查找此内容的信息。

4. 表的约束

定义字段的时候，需要有一个类型，这样的一个类型，有时候并不能满足我们对列数据的一个约束；例如表字段是否可以为NULL，有没有默认值，表字段的解释能不能加上，对于数字类型的字段可以不可以指定默认表示的位数，可不可以将这个字段设置成唯一标识该行数据的呢？因此也就产生了表的约束！
对于字段的约束：

1. 空属性
   NULL：表示当前字段可以为NULL
   NOT NULL：表示当前字段不可以为NULL，列的字段指定该属性之后，在插入数据的时候如果不插入该列数据，会报错。
2. 默认值
   default：可以再创建表字段的时候，制定一个默认值，当我们插入数据的时候，没有插入该字段的时候，就采用默认属性。
3. 列描述 comment ‘ 注释’
   相当于列的注释，告诉查看该表的用户，该字段是什么含义，对于存储数据而言，并没有实质的约束。
4. zerofill
   约束了数字的宽度，如果宽度不够则用0来填充 ，会默认加上unsigned无符号的属性，在数据库当中存储的还是原本的数值，只不过打印的时候会按照宽度进行输出（如：1 宽度为3 则表示为001 ）
5. 主键：不是重新开辟一个新的列，而是表当中的一列被设置属性，这种属性被称之为主键。

• 主键的属性保证了该列数据不能为空，不能重复
• 一个表当中只能够有一个主键，不能由多个主键
• 主键在设置的时候，都是整型类型，主键的这一列数据可以唯一标识一行数据
• 主键列的值可以唯一标识该行的数据，因为逐渐不能为空，不能重复的原因
  如何设置主键：
• 在创建表的时候，直接在字段的后面加上“primary key” createtable t(id int primary key, name varchar(20));
• 在创建表的时候，在所有字段全部指定完毕之后，在所有字段后面加上设置的逐渐关键词，并加上指定字段的名称createtable t(id int, name varchar(20),primary key(id));
• 在创建表可以不指定，完成后通过alter关键字来增加主键alter table t add primary key(id);

如何删除主键：
使用alter关键字加上drop可以删除 ，alter table t drop primary key;

6. 自增长 auto_increment
   当我们设置一个字段为自增长的时候，如果插入数据的时候，不给该列数据，则系统会自动填写一个比上一行数值加1的数值填写进去，通常自增长是搭配主键来进行使用的，主键不可重复每次自增长都会给新的数据分配比上一行+1的数值；
   自增长的条件：

• 首先字段必须是一个整数，字段类型需要整数类型
• 一张表当中最多只能够有一个自增长的列
• 搭配主键使用

mysql> create table tt21(
-> id int unsigned primary key auto_increment,
-> name varchar(10) not null default ''
-> );
获取上次插入的自增长的值
mysql > select last_insert_id();

7. 唯一键 unique 给某个字段设置一个属性，该属性约束字段的内容不能重复但可以为空NULL
   主键比唯一键多了一个约束字段内容不能NULL，但是一张表当中可以多个字段被定义为unique

mysql> create table student (
-> id char(10) unique comment '学号，不能重复，但可以为空',
-> name varchar(10)
-> );

9. 外键用于定义主表和从表的一个约束关系
   主表：存放基础属性的表，当前表中的数据用来描述基础属性
   班级表即是主表，而学生表则是从表，存放具体的用户产生的信息
   

• 定义主表当中可能成为其他表外键的字段，一定需要将该字段设置成为主键或者唯一键，因为主键不能够重复，不能够为空，可以唯一标识一行数据
• 定义从表的时候，设置外键其实就是设置一个关系，当前从表的字段和住表当中字段的关系，在从表当中插入数据的时候，会对插入数据进行校验，校验的标准插入的数据是否存在主表列字段当中。

创建主表
create table myclass (
id int primary key,
name varchar(30) not null comment'班级名'
);
创建从表
create table stu (
id int primary key,
name varchar(30) not null comment '学生名',
class_id int,
foreign key (class_id) references myclass(id)
);

5. 表的增删改查

1. 替换 replace into
   如果要替换的数据不存在主键或者唯一键冲突，表的数据没有冲突，则插入一行数据，表当中有冲突的话，则删除之后在插入。
   replace into 表名称 字段名称 values （字段对应的值，字段对应的值,...）；
   对于一些在插入数据时时是整形插入的，但是后期有可能需要进行计算的数据，在定义字段的类型时定义为浮点数。
2. 查询

• 全列查询 select * from *代表当前表的所有列
• 指定列查询 select 字段名称 字段名称 from 表名称
• 查询时候，表达式中包含一个字段：select 字段名称，字段名称，表达式（字段名称） from 表名称
• 去重：关键字distinct select distinct 字段名称，字段名称 from 表名称

3. where条件 对查询结果进行约束
4. 运算符

• 任意一个结果成立：or => ’ || ’

• 多个结果一起成立：and => ‘&&’

• in（option…） 如果是选择之中任意一个，则成立
  SELECT name, shuxue FROM exam_result WHERE shuxue IN (58, 59, 98, 99);

• 范围匹配： between a0 and a1 则表示[a0,a1]；
  SELECT name, yuwen FROM exam_result WHERE yuwen BETWEEN 80 AND 90;

• 模糊匹配：like ‘%’表示任意多个字符；‘_’表示一个字符
  SELECT name FROM exam_result WHERE name LIKE '孙%';
SELECT name FROM exam_result WHERE name LIKE '孙_';
  

5. NULL查询
   IS NOT：不是xxx
   是否为空：IS NULL 是空 ; IS NOT NULL 不是空
   = 和<=>都表示等于运算符，但是区别在于：
   =在数据库查询的时候并不是安全的，空和空使用“=”进行比较的时候，结果还是为NULL
   <=>在数据库查询的时候是安全的，空和空使用“<=>”进行比较，结果为1
6. 对查询结果进行排序 order by 字段名称
   规则：按照order by 后面的字段内容进行排序后输出 默认是升序（ASC）降序的话是(DESC)
   SELECT name, shuxue FROM exam_result ORDER BY shuxue;
7. 筛选分页结果

• 方式1：limit n :从0开始，筛选n条
• 方式2：limit s，n 从s开始，筛选n条
• 方式3：limit n offset s：从s开始，筛选n条
  SELECT id, name, shuxue, yingyu, yuwen FROM exam_result ORDER BY id LIMIT 3 OFFSET 0;

8. 更新表数据 update
   更改一行数据时候，一定要加上约束条件，否则叫全表更新了update 表名称 set 字段名称=更改后的值 where 约束条件
UPDATE exam_result SET shuxue = 80 WHERE name = '孙悟空';
9. 删除表当中数据
   如果不加上约束条件，表示删除整张表当中的数据；如果又自增长的属性不会进行情况，不会对表结构造成影响。DELETE FROM exam_result WHERE name = '孙悟空';
10. 截断表 truncate table 表名称
    不能针对某一行数据进行操作，而是针对整个表的数据进行操作的。
11. 聚合函数

• count函数 ：查询表当中数据的个数SELECT COUNT(*) FROM students;
• sum函数：查询数据的综合SELECT SUM(shuxue) FROM exam_result;
• avg函数：计算数据平均值SELECT AVG(yuwen + shuxue + yingyu) 平均总分 FROM exam_result;
• max函数：计算最大值SELECT MAX(yingyu) FROM exam_result;
• min函数：计算最小值SELECT MIN(shuxue) FROM exam_result WHERE shuxue > 70;

12. 分组查询 group by 子句
    是将我们所需要查询的结果先进行分组，之后再进行计算
    查询每个部门的平均工资和最高工资：select deptno， avg(asl),max(sal) from emp group by deptno
    查询每个部门的不同岗位的平均工资和最低工资：select deptno , job,avg(sal),min(sal) from emp group by deptno ,job先按照部门进行分，之后再按照岗位来进行划分
    显示平均工资低于2000的部门和它的平均工资：select deptno ,avg(sal), from emp group by debtno having agv(sal)<2000;
    一般情况下group by和having 配套使用，进行过滤使用。

6. 内置函数

1. 日期函数

• 获取当前日期： select current_date();
• 获取当前时间：select current_time();
• 获取当前时间戳：select current_timestamp();
• 返回datetime当中的日期部分date(datetime)
• 对日期进行减操作，单位可以为year，month，day select date_sub('2017-10-1', interval 2 day);
• 对日期进行加操作，单位可以为year，month，day select date_add('2017-10-28', interval 10 day);
• 计算两个时间的差值，单位是天：select datediff('2017-10-10', '2016-9-1'); 计算方式是date1-date2
• 返回当前的日期时间：now()

2. 字符串函数

• 获取str字符集select charset(ename) from EMP;
• 将字符串连接起来select concat(name, '的语文是',chinese,'分，数学是',math,'分') as '分数' from student;
• 返回substing在string当中的位置，没有则返回0 instr(string ,substring)
• 将str转换成大写ucase(str)
• 将str转换成小写lcase(str)
• 从str当中的左边开始取len个字符left(str,len)
• 计算str的长度，并非是字节数量select length(name), name from student;
• 替换select replace(ename, 'S', '上海') ,ename from EMP;
• 比较字符串strcmp(str1,str2);
• 从str的pos位置开始取len个字符select substring(ename, 2, 2), ename from EMP;

3. 数学函数

• 绝对值函数select abs(-100.2);
• 向上取整select ceiling(23.04);
• 向下取整select floor(23.7);
• 获取随机数select rand();
• 精确，超过的部分会自动四舍五入select format(12.3456, 2);

4. 其他函数

• 获取当前用户select user();
• 获取当前正在使用的数据库select database();
• 计算MD5（一般在工作时候检验是否相同的话是比较md5）select md5('admin')
• 查询用户密码select password('root');

7. 复合查询与内外连接

1. 多表查询：往往数据来自不同的表，所以需要多表查询
   
   

• 显示部门号为10的部门名， 员工名和工资 select ename, sal,dname from EMP, DEPT where EMP.deptno=DEPT.deptno and DEPT.deptno = 10;
• 显示各个员工的姓名，工资及工资级别select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and hisal;

2. 自连接：指的是在同一张表当中查询
   
3. 子查询：第二个select语句写到where的子句当中，也就是嵌套查询

• 单行子查询：子查询当中返回了单列，单行的数据
• 多行子查询：子查询当中返回了单列，多行数据，两个表的结果如果不在where后面加约束条件，会形成笛卡尔积
• 多列子查询：子查询当中但会多列数据 只需要在where语句后面使用“（a,b）=（子查询语句）”将多个列的名称括起来
  例子：查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人
  mysql> select ename from EMP where (deptno, job)=(select deptno, job from EMP where ename='SMITH') and ename <> 'SMITH';
  

4. 在from语句当中使用子查询，是将其中一个表当作一个临时表
   查找每个部门工资最高的人的姓名，工资，部门，最高工资select EMP.ename, EMP.sal, EMP.deptno, ms from EMP,(select max(sal) ms, deptno from EMP group by deptno) tmp where EMP.deptno=tmp.deptno and EMP.sal=tmp.ms;
5. 合并查询

• union：作用是将两个查询结果集合进行合并，会自动去除结果当中重复的行
• union all：作用是将两个查询的结果进行合并，不会自动去除结果当中重复的行，相当于将结果1和结果2直接拼接
  

6. 外连接：连接是指要在多个表当中去查询
   只需要关心sql语句当中select查询的位置来决定左右的

• 左外连接：左侧的表完全显示 from 表1 left join 表2 on 约束条件 完全显示表1
• 右外连接：右侧的表完全显示from 表1 right join 表2 on 约束条件完全显示表2

8. 索引特性

1. 常见的索引：

• 主键索引：在一个表当中设置了主键，就会创建一个主键索引
  主键索引遵循主键的特性，不重复，不能为NULL，按照主键的值构建一颗二叉树，准确的说是b+树
• 唯一索引：在一个表当中设置了唯一键，就会创建一个唯一索引
• 普通索引
• 全文索引：前替是存储引擎需要MyISAM ,INNODE存储引擎支持事务，而MyISAM不支持

2. 建立了一个二叉树的结构，将设置成为索引的字段的内短通过二叉树维护起来
   也就是说如果一张表当中创建了索引，也就是创建了一个二叉树结构
   
3. 增删改查的效率是否有影响

• 增：有影响，因序更新二叉树
• 删：有影响，因序更新二叉树
• 改：有影响，因序更新二叉树
• 查：查询时如果使用索引字段值，效率会大大增加

4. 索引查看和删除
   查看索引：show keys from 表名称
   删除索引：alter table 表名称 drop primary key； 或者alter table 表名称 drp index 索引名称

9. 事务管理

相当于多线程当中的线程互斥问题

1. 保证多个客户端访问数据库的同一张表的时候不会产生数据二义性的问题；
2. 事务的基本操作：

• 开始事务：start transaction；
• 创建一个保存点：savepoint 保存点名称
• 回滚：rollback to 保存点名称
• 提交事故：commit
  
  当提交事务完成后，不能再回到之前的保存点了。
  INNODE存储引擎支持事务，而MyISAM不支持

3. 事务的隔离级别
   无隔离级别：

• 脏读：读到数据并不是有效的数据
• 不可重复读：前后两次读到的结果是不一致的
• 幻读：前后读到的数据条目都是不一致的
  
  设置隔离级别：set session transaction islation level 事务的隔离级别

10. 视图特性

• 视图就是一个虚拟的表，虚拟表当中的数据来源于真实的表；
• 视图也可以当作是一个表来进行操作；
• 修改视图当中的内容也会同步修改真实表中的数据，当然修改真实表也会更新视图当中的内容。
• 创建一个视图：create view 视图名称 as select 语句 视图数目是没有限制的
• 删除一个视图：drop view 视图名称

11. 用户管理

MySQL的用户管理相当于linux操作系统的多用户管理 因为不想给数据库操作人员，所有数据库的权限，所以需要分用户，可以设置用户只能够看到某一个具体的数据库！

1. 创建用户 :create user '用户名'@‘允许从哪里链接上来’ identidied by 密码；
2. 删除用户：drop user '用户名'@'允许从哪里连接上来';
3. 修改用户密码

• root用户可以修改其他用户：set password for '用户名'@'允许从哪里连接上来=password(’新密码‘)；
• 其他用户可以修改自己的密码：set password=password('新密码')；

4. 如何给用户创建权限

• 刚刚创建出来的用户是没有任何权限的
• 语法：grant 权限列表 on 库.对象名 to '用户名'@'登陆位置' [identified by '密码']
• 权限列表：多个权限用，隔开
• *.*代表本系统中所有数据库的所有对象
• 库.*表示某个数据库中的所有数据对象

5. 如果删除一个用户的权限
   revoke 权限列表 on 库.对象名 from '用户名'@'登陆位置'；

对于这些基本的操作和相关概念，我们能够做到了解甚至熟练的话，那么对于我们秋招来说无疑是一次很大的提升，也可以让我们的简历更加的丰富，一起加油吧！（给个赞呗）