MySQL入门与面试要点

写在文前，本文是学习过程的抄录与总结。PS：学习MySQL基础可以搜索狂神说博客。

1、MySQL

• 关系型数据库通过外键关联来建立表与表之间的关系

• 非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定

1.1 结构化查询语句分类

阿里巴巴规范

/*
id          主键
version     乐观锁
is_delete   伪删除
gmt_create  创建时间
gmt_update  修改时间
*/

创建表

CREATE TABLE IF NOT EXISTS `teacher` (
	`id` INT(8) NOT NULL AUTO_INCREMENT COMMENT '职工号',
	`name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(10) NOT NULL DEFAULT '12345' COMMENT '密码',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

常用指令

SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看表的创建语句
DESC student -- 显示表结构

数据表类型

• 适用 MyISAM : 节约空间及相应速度
• 适用 InnoDB : 安全性 , 事务处理及多用户操作数据表

修改表

ALTER TABLE student RENAME student1 -- 修改表名
ALTER TABLE teacher ADD age INT(3) -- 增加表的字段
ALTER TABLE teacher MODIFY age VARCHAR(11) -- 修改表字段的约束
ALTER TABLE teacher CHANGE age age1 INT(3) -- 字段重命名

删除表

DROP TABLE IF EXISTS demo -- 删除表

MySQL数据管理

1.外键（了解即可）

CREATE TABLE IF NOT EXISTS `grade` (
 `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
 `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
 PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '生日',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
  `gradeid` INT(10) NOT NULL COMMENT '学生年级',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
  KEY `FK_gradeid` (`gradeid`),
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

ALTER TABLE `student` 
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`); -- 创建表后添加外键约束

1.2 DML语言

数据存储，数据管理

• insert
• update
• delete

添加：

INSERT INTO `student`(`name`,`sex`,`address`) VALUES ('张三','女', '广州'),('李四','男', '深圳') -- 插入数据

修改：

UPDATE `student` SET `name` = '李白',`email` = '1234' WHERE id = 1; -- 修改多个属性，逗号隔开

UPDATE `student` SET `name` = '李黑',`email` = '1235' WHERE id <> 1; -- id不等于1

UPDATE `student` SET `name` = '李刚',`email` = '1235' WHERE id BETWEEN 2 AND 3; -- id在2和3，包括2和3

UPDATE `student` SET `name` = '李白',`email` = '1234' WHERE `name` = '李刚' AND sex = '女'; -- 多个条件

`xxx` = CURRENT_TIME -- 现在的时间

删除：

DELETE FROM `student` WHERE id = 3; -- 删除指定数据
TRUNCATE `student` -- 清空表，表结构索引不变

delete与truncate：

1.都能删除数据，都不会删除表结构

2.truncate重新设置，自增列计数器会归零，不影响事务

3.delete删除，重启数据库，InnoDB自增列从1开始（存在内存中），MyISAM继续从上一个自增量开始（存在文件中）

1.3 DQL数据查询语言

查询：

SELECT `name`,`address` FROM student WHERE NAME='李白' -- 查询指定字段

SELECT `name` AS '姓名',address AS '地址' FROM student -- 给字段起别名

SELECT CONCAT('姓名：',`name`) AS '学生姓名' FROM student -- 函数，连接

SELECT DISTINCT `name` FROM student -- distinct去除重复数据

SELECT VERSION() -- 查询系统版本

SELECT `id`+1 AS 'id+1' FROM student -- 值+1

where:

SELECT `StudentNo`,`StudentRusult` FROM result
WHERE StudentRusult>50 AND StudentRusult<75

SELECT `StudentNo`,`StudentRusult` FROM result
WHERE StudentRusult BETWEEN 50 AND 75 -- 模糊查询

SELECT id,`name` FROM student
WHERE `name` LIKE '李%' -- 查询姓名是李姓的同学

SELECT id,`name` FROM student
WHERE `name` LIKE '李_' -- 查询姓名姓李两个字的同学

SELECT id,`name` FROM student
WHERE `name` LIKE '%白%' -- 查询名字有白字的同学

SELECT id,`name` FROM student
WHERE id IN (1,2,3)

SELECT id,`name` FROM student
WHERE sex IN ('男') -- 查询性别为男的学生，in里面需要准确匹配才能查到

1.4 联表查询

SELECT s.studentNo,`name`,sex,StudentRusult
FROM student s
INNER JOIN result r
WHERE s.studentNO=r.studentNo

-- right join
SELECT s.studentNo,`name`,sex,StudentRusult
FROM student s
RIGHT JOIN result r
ON s.studentNO=r.studentNo

-- left join
SELECT s.studentNo,`name`,sex,StudentRusult
FROM student s
LEFT JOIN result r
ON s.studentNO=r.studentNo

操作	描述
inner join	表中至少有一个匹配，就返回行
left join	从左表中返回所有值，即使右表中没有匹配
right join	从右表中返回所有值，即使左表中没有匹配

自连接：

-- 自连接
SELECT a.`name` AS '父',b.`name` AS '子'
FROM student a,student b
WHERE a.studentNo = b.studentNo

分页和排序：

-- 升序 ASC，降序DESC
SELECT a.`name` AS '父',b.`name` AS '子'
FROM student a,student b
WHERE a.studentNo = b.studentNo
ORDER BY a.studentNO DESC


-- limit 起始值，页面大小[(n-1)*pageSize]n为页码
SELECT a.`name` AS '父',b.`name` AS '子'
FROM student a,student b
WHERE a.studentNo = b.studentNo
ORDER BY a.studentNO DESC
LIMIT 2,3

子查询与嵌套查询：

-- 方式一：连接查询
SELECT s.studentNo,`name`,sex,StudentRusult
FROM student s
INNER JOIN result r
ON s.studentNO=r.studentNo
WHERE `name`='张三'
ORDER BY StudentRusult DESC

-- 方式二：子查询，由里及外
SELECT StudentNo,`name`,sex 
FROM student
WHERE studentNo = ANY(
 SELECT studentNo FROM result 
 WHERE SubjectNo=2
)
ORDER BY StudentNo DESC

-- 嵌套查询
SELECT s.studentNo,`name`,sex,StudentRusult
FROM student s
INNER JOIN result r
ON s.studentNO=r.studentNo
WHERE `name`='张三' AND SubjectNo = (
 SELECT xxx FROM xxx
 WHERE xxx=33
)
ORDER BY StudentRusult DESC

常用函数：

-- 数学运算
SELECT ABS(-8) -- 返回绝对值
SELECT CEILING(5.4) -- 向上取整
SELECT FLOOR(5.4) -- 向下取整
SELECT RAND() -- 0-1的随机数
SELECT SIGN(7) -- 返回符号，0为0，负数为-1，正数为1

-- 字符串函数
SELECT CHAR_LENGTH('sgjkklsjg') -- 字符串长度
SELECT CONCAT('hello ','world') -- 合并字符串
SELECT LOWER('FKkdjk') -- 转小写
SELECT UPPER('sgfnsg') -- 转大写
SELECT REPLACE('你好','你','我') -- 替代

聚合函数：

-- 聚合函数
SELECT COUNT(studentNO) FROM student -- count(字段)，会忽略所有null值，去重
SELECT COUNT(*) FROM student -- count(*)，不会忽略null值，计算行数

SELECT SUM(StudentRusult) AS 总分 FROM result -- 总和
SELECT AVG(StudentRusult) AS 平均分 FROM result -- 平均
SELECT MAX(StudentRusult) AS 最高分 FROM result -- 最大值
SELECT MIN(StudentRusult) AS 最低分 FROM result -- 最小值

SELECT SubjectNo,AVG(StudentRusult),MAX(StudentRusult),MIN(StudentRusult)
FROM result
GROUP BY StudentRusult
HAVING AVG(StudentRusult) >= 60

1.5 事务

要么都成功，要么都失败。

事务原则ACID：

原子性Atomicity：要么都成功，要么都失败。

一致性Consistency：事务前后的数据完整性保持一致。

隔离性Isolation：多个用户并发访问数据库时，不能被其他事务的操作所干扰。

持久性Durability：事务一旦提交则不可逆，被持久化到数据库中。

脏读：指一个事务读取了另一个事务为提交的数据

不可重复读：在一个事务读取表中某一行的数据时，多次读取的结果不一样

虚读：指一个事务读到别的事务插入的数据，导致前后读取不一样

事务测试：

SET autocommit = 0 -- 关闭事务自动提交

START TRANSACTION -- 事务开启

COMMIT -- 提交，持久化

ROLLBACK -- 回滚

SET autocommit = 1 -- 开启自动提交

SAVEPOINT 保存点 -- 设置事务保存点
ROLLBACK TO SAVEPOINT 保存点 -- 回滚到保存点
RELEASE SAVEPOINT 保存点 -- 撤销保存点

1.6 索引

• 主键索引 (Primary Key)

  主键 : 某一个属性组能唯一标识一条记录

  特点 :

  • 最常见的索引类型
  • 确保数据记录的唯一性
  • 确定特定数据记录在数据库中的位置

• 唯一索引 (Unique)

  作用 : 避免同一个表中某数据列中的值重复

  与主键索引的区别

  • 主键索引只能有一个
  • 唯一索引可能有多个

• 常规索引 (Index)

  作用 : 快速定位特定数据

  注意 :

  • index 和 key 关键字都可以设置常规索引
  • 应加在查询找条件的字段
  • 不宜添加太多常规索引,影响数据的插入,删除和修改操作

• 全文索引 (FullText)

  百度搜索：全文索引

  作用 : 快速定位特定数据

  注意 :

  • 只能用于MyISAM类型的数据表
  • 只能用于CHAR , VARCHAR , TEXT数据列类型
  • 适合大型数据集

msql索引原理

索引测试：

SHOW INDEX FROM student -- 读取索引所有信息

ALTER TABLE school.student ADD FULLTEXT INDEX `name`(`name`) -- 增加一个全文索引

-- EXPLAIN  分析sql执行情况
EXPLAIN SELECT * FROM student -- 非全文索引

EXPLAIN SELECT * FROM WHERE MATCH(`name`) AGAINST('白') -- 全文索引

1.7 MySQL数据结构与原理

索引的本质：索引是数据结构

• 目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。
• 一般来说，B+Tree比B-Tree更适合实现外存储索引结构。
• MyISAM与InnoDB
  • MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。
  • 第一个重大区别是InnoDB的数据文件本身就是索引文件。叶节点包含了完整的数据记录。这种索引叫做聚集索引。
  • 第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。
• 请永远使用一个与业务无关的自增字段作为主键。

1.7.1 索引原则

1.索引不是越多越好

2.不要对经常变动数据加索引

3.小数据量的表不需要加索引

4.索引一般加在常用来查询的字段上

B+tree：InnoDB的默认数据结构

1.7.2 数据库用户管理

-- 创建用户
CREATE USER yanghai IDENTIFIED BY PASSWORD('12345')

-- 修改密码
SET PASSWORD=PASSWORD('123456')

-- 重命名
RENAME USER yanghai TO muziyanghai

-- 用户授权，除给别人授权外的所有权限
GRANT ALL PRIVILEGES ON *.* TO muziyanghai

-- 查询权限
SHOW GRANTS FOR muziyanghai

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM muziyanghai

-- 删除用户
DROP USER muziyanghai

1.8 MySQL备份

数据库备份必要性

• 保证重要数据不丢失
• 数据转移

MySQL数据库备份方法

• mysqldump备份工具
• 数据库管理工具,如SQLyog
• 直接拷贝数据库文件和相关配置文件
  

1.9 数据库设计

1.10 数据库连接池

池化技术：准备一些预先的资源，过来就连接预先准备好的。

开源数据源实现：

DBCP

C3P0

Druid

结论： 无论使用什么数据源，本质还是是一样的，DataSource接口不会变。

2、面试

2.1 三大范式

第一范式：第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式。

第二范式：第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，消除非主属性对码的部分依赖，即其他字段都要依赖于主键。

第三范式：在第二范式的基础上，消除非主属性对码的传递依赖，也可以说是消除冗余。如一个表有学号，系名，系主任，就有【学号->系名，系名->系主任】不符合（需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关）。

2.2 mysql有关权限的表都有哪几个

这些权限表分别user，db，table_priv，columns_priv和host

• user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
• db权限表：记录各个帐号在各个数据库上的操作权限。
• table_priv权限表：记录数据表级的操作权限。
• columns_priv权限表：记录数据列级的操作权限。
• host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

2.3 创建索引的原则（重中之重）

1） 最左前缀匹配原则

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列，性别，男女未知

5）尽量的扩展索引，不要新建索引。

6）定义有外键的数据列一定要建立索引。

7）对于定义为text、image和bit的数据类型的列不要建立索引。

B+tree性质

1.）n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。

2.）所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3.）所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。

4.）B+ 树中，数据对象的插入和删除仅在叶节点上进行。

5.）B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。

Hash Hash索引只能用于对等比较，例如=,<=>（相当于=）操作符。

2.4 B树和B+树的区别

• 在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。
• B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。

2.5 Hash索引和B+树索引有什么区别或者说优劣呢?

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。

• hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。

• hash索引不支持使用索引进行排序，原理同上。

• hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。

• hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。

• hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

2.6 数据库为什么使用B+树而不是B树

• B树只适合随机检索，而B+树同时支持随机检索和顺序检索；

• B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。

• B+树的查询效率更加稳定。

• B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。

• 增删文件（节点）时，效率更高。

2.7 隔离级别与锁的关系

• 在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突。
• 在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁。
• 在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。
• SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。

2.8 什么是死锁？怎么解决？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

1. 事务之间对资源访问的顺序交替
2. 并发修改同一数据

常见的解决死锁的方法：

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

如果业务处理不好可以用分布式事务锁或者使用乐观锁

2.9 数据库的乐观锁和悲观锁是什么？怎么实现的？

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：一般会使用版本号机制或CAS算法实现。

• CAS:Compare and Swap, 比较并交换

乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

一般多写的场景下用悲观锁就比较合适。

欢迎来讨论与指出问题。本文图片来自网络，如有侵权请联系。转发请标明出处。