本文详细介绍了数据库系统的基本概念、关系数据库操作，重点讨论了SQL语言的使用，包括DDL（数据定义语言）、DML（数据操纵语言）、DCL（数据控制语言）以及并发控制。还探讨了存储过程、索引、视图、事务隔离级别和锁的概念，并提供了SQL查询优化的技巧和案例。此外，文章还提到了主从复制和数据库中间件Mycat的应用。
一、数据库基本概念
考点：
- DB：存放数据和数据之间的关系
- DBMS：五大功能，对DB进行管理和操作。这个操作要通过操作系统OS实现
- DBS：包含了硬件、软件、人
- 总结：DBS包含DBMS&DB
- 数据库发展的三个阶段：人工管理、文件管理、数据库系统阶段。最重要的特点：数据的共享度高、独立性高。
- 数据模型：实体—<抽象>—概念模型—<赋值>—数据模型（逻辑模型和物理模型），对学生表（数据结构）进行插入（数据操作）学生信息（要符合数据完整性约束条件）
二、关系数据库及其操作
- 关系操作之查询操作：
(1). 传统的集合操作：（并、交、差）属性个数相同、域相同、笛卡尔积
(2). 专门的关系运算：选择、投影、连接、除
a. 选择：对行进行操作
注意：要加上引号。
b. 投影（选取满足条件的属性）：对列进行操作
(3). 连接：先笛卡尔积再选择
a. 内连接之等值连接
代码示例：
#连接
语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
where 筛选条件
内连接之等值连接：inner
等值连接：查询员工名、部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;
查询名字中包含e的员工名和工种名
SELECT last_name,job_title
FROM employees e INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE e.last_name LIKE '%e%';
查询部门个数>3的城市名称和部门个数
查询每个城市的部门个数，再进行筛选。
SELECT city,COUNT(*)
FROM departments d INNER JOIN locations l
ON d.`location_id` = l.`location_id`
GROUP BY city
HAVING COUNT(*)>3
查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降序排序
查询每个部门的员工个数
筛选个数>3的
将个数降序排序
SELECT department_name,COUNT(*)
FROM departments d INNER JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC
查询员工名、部门名、工种名、并按部门名降序
SELECT last_name,department_name,job_title
FROM employees e INNER JOIN departments d
ON e.`department_id`=d.`department_id`
INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
ORDER BY department_name DESC
内连接之自然连接：
代码：
#自然连接：
SELECT *
FROM `departments` NATURAL JOIN `employees`
结果：32条
番外：和等值连接的比较图：
代码：等值连接
SELECT *
FROM `departments` d INNER JOIN `employees` e
ON d.`department_id`=e.department_id
结果：106条
代码：等值连接2
SELECT *
FROM `departments` d INNER JOIN `employees` e
ON d.`department_id`=e.department_id
AND d.`manager_id`=e.manager_id
结果：32条 此时和自然连接的效果一样。
外连接之左外连接：
左外连接代码：
#查询哪个部门没有员工
SELECT d.*,e.`employee_id`
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE `employee_id` IS NULL
结果：
外连接之右外连接（限制左表）：
外连接之全外连接：
连接代码：
连接
语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
where 筛选条件
内连接：inner
外连接:
左外：left 【outer】
右外：right 【outer】
全外：full 【outer】
交叉连接：cross
内连接：
等值连接
自然连接
等值连接：查询员工名、部门名
SELECT *
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;
查询名字中包含e的员工名和工种名
SELECT last_name,job_title
FROM employees e INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE e.last_name LIKE '%e%';
查询部门个数>3的城市名称和部门个数
查询每个城市的部门个数，再进行筛选。
SELECT city,COUNT(*)
FROM departments d INNER JOIN locations l
ON d.`location_id` = l.`location_id`
GROUP BY city
HAVING COUNT(*)>3
查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降序排序
查询每个部门的员工个数
筛选个数>3的
将个数降序排序
SELECT department_name,COUNT(*)
FROM departments d INNER JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC
查询员工名、部门名、工种名、并按部门名降序
SELECT last_name,department_name,job_title
FROM employees e INNER JOIN departments d
ON e.`department_id`=d.`department_id`
INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
ORDER BY department_name DESC
非等值连接(>、=、<...) eg:查询员工的工资级别
SELECT salary,grade_level
FROM employees e INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
自然连接：
SELECT *
FROM `departments` d NATURAL JOIN `employees` e
#等值连接
SELECT *
FROM `departments` d INNER JOIN `employees` e
ON d.`department_id`=e.department_id
AND d.`manager_id`=e.manager_id
#左外连接
#查询男朋友不在男神表的女神名。
分析：最终要的是女神名，所以将女神表作为主表。
要将男朋友不在男神表的女神名做上标记，男朋友制空
最后去null
*/
SELECT b.name
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL;
#查询哪个部门没有员工
SELECT d.*,e.`employee_id`
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE `employee_id` IS NULL
#查询编号>3的女神的男朋友信息，如果有则详细列出，如果没有，用null填充
SELECT b.id,b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE b.id>3;
#查询哪个城市没有部门
SELECT l.city,`department_name`
FROM locations l
LEFT OUTER JOIN departments d
ON l.`location_id`=d.`location_id`
WHERE `department_id`IS NULL
#查询部门名为SAL或IT的员工信息
SELECT e.*,d.`department_name`
FROM employees e
RIGHT OUTER JOIN departments d
ON e.department_id = d.`department_id`
WHERE d.`department_name` IN('SAL','IT')
连接总结
子查询相关案例：
#子查询示例：where或者having后面
#1.标量子查询，案例：查询最低工资的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE salary =(
SELECT MIN(salary) FROM employees
);
#2.列子查询，案例：查询所有是领导的员工姓名
SELECT last_name
FROM employees
WHERE employee_id IN (
SELECT manager_id
FROM employees
)
#子查询经典案例：
#1. 查询工资最低的员工信息：last_name,salary
SELECT last_name,salary
FROM employees
WHERE salary=(
SELECT MIN(salary) FROM employees
)
#2. 查询平均工资最低的部门信息
#第一步：计算每个部门的平均薪水
SELECT department_id
FROM employees
GROUP BY department_id
#第二步：求出最低平均工资的部门编号
ORDER BY AVG(salary)
LIMIT 1;
SELECT *
FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
)
#3. 表子查询---查询平均工资最低的部门信息和该部门的平均工资
SELECT d.* ,ag
FROM departments d
INNER JOIN(
SELECT department_id,AVG(salary) ag
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
) ag_table
ON d.department_id=ag_table.department_id
#4.查询平均工资最高的job信息
找出平均工资最高的工作`jobs`
SELECT*
FROM jobs
WHERE job_id=(
SELECT job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
)
#5.查询平均工资高于公司平均工资的部门有哪些
找出每个部门的平均工资
求出公司的平均工资
SELECT DISTINCT department_name
FROM departments d
INNER JOIN employees e
#on d.department_id=e.`department_id`
WHERE d.`department_id` IN (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
SELECT AVG(salary)
FROM employees
)
)
#6. 查询公司中所有manager的详细信息
SELECT *
FROM employees
WHERE employee_id IN ( #“in”可以换成“=any”
SELECT DISTINCT manager_id
FROM employees
)
#7. 各个部门中最高工资中最低的那个部门的最低工资是多少？
#找出各个部门的工资
SELECT MIN(salary),department_id
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
)
#8. 查询平均工资最高的部门的manager的详细信息：last_name,department_id,email,salary
SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id IN(
SELECT DISTINCT manager_id
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
)
)
三、SQL
- DDL之模式
(1) 创建模式：
eg：给用户wang创建模式AB
create schema ‘AB’ authorization Wang
(2)删除模式：
drop schema ‘AB’
- DDL之表
创建表：
create table <表名>
（
<列名><数据类型>【完整性约束】，
…
);
eg：创建一张学生表，学号、姓名、班级
create table Student
（
Sno int,
Sname char(10),
class char,
age int,
primary key(Sname,class) as A //取别名
）;
//单属性主键直接加到后面 Sno int primary key
//多属性主键在最下面一行写 primary key(Sname,class)
注意：
- （）不可省略，【】可省略
- 列级完整性：单属性 表级完整性：可单属性可多属性。多属性一定要用表级完整性。
(4) 修改表：alter table <表名>
添加：add (列名)（数据类型）[约束]
alter table student
add sex char(2);
删除：drop column <列名>
alter table student
drop column age;
删除约束：drop constraint <列名>|<表别>
alter table student
drop constraint Sno; 或者 drop constraint A
删除表：drop table <表名>
drop table Student;
修改：
属性名：不可以修改属性名，只能通过删除添加的方式。但是此时值也会被清除。
数据类型：alter column <列名><新的数据类型>
alter table student
alter column sex char;
数据类型：
4. DDL之索引
(1) 目的：提高查询效率。
(2) 创建索引：create index <索引名> on <表名> （ASC(默认升序)、DESC(降序)）
create index IX_EMP on Employee(Dno,Eno DESC);
(3) 删除索引：drop index <索引名>
drop index IX_EMP;
(4) 适合场景：经常被查询的属性 eg：where… order by… 连接… 唯一值 eg：主码/外码
- DDL之视图：
(1) 外模式
(2) 虚表（不存储数据，来自基本表或者其他视图）
(3) 安全机制（主要用于查询，增删改限制严重）
(4) 创建视图：
create view <视图名>
as <子查询>
[with check option]; ---拒绝执行 有权限可以执行
(5) 删除视图：drop view <视图名>
(6) 更新视图：通过视图来插入(insert)、删除(delete)、修改(update)数据 <转成对基本表的增删改>
(7) 视图特点：简单、安全(因为修改限制严重)
DML之查询select
- 单表：select <目标列表达式> from <表名> [where+条件表达式]
(1) 比较运算符：
select * from sc
where grade>70;
(2) 确定范围
select * from sc
where grade between 60 and 80; 包括60和80
select * from sc
where grade not between 60 or 80; <60或者>80
(3) 确定集合（in）
select * from sc
where grade in (60,80);
(4) 涉及空值：is (not) null
select * from SC
where grade is null;
注意两点：null指的是不确定的值。不是0、空格、空字符串
不存在=null的说法
(5) 字符串匹配：[not] like + <字符串>
通配符：‘_’单个(一个字节)，‘%’任意
eg: 查询学生表中姓‘王’同学的名字
select sname from student
where sname like ‘王%’; ‘王__%’ 至少有一个汉字 要用两个下划线表示
(6) 若需要用到排序：order by <列> [ASC|DESC]
select * from sc
order by grade desc;
(7) 分组：group by + <列> 按某一字段对记录进行分组
eg: 查询每个学生选修了多少门课。
select Sno, Count(Cno) from sc
group by Sno; // group by Sno having sno=1001 只要sno=1001的这一行
条件：
where 和 having
where不允许跟聚集函数：对属性进行操作（sum、max、min、avg），对元组进行操作（count）
having：不允许单独使用 在前面要加上group by
- 多表查询
(1) 等值连接查询（=）
select S.*,SC.* from S,SC
where S.sno=SC.sno
(2) 自身连接：一个表和自己进行连接。
(3) 复合条件连接：and,not,or
(4) 嵌套连接：
select <列表达式>
from <表> ---父查询
where (select ...) ---子查询 把子查询的结果当成父查询的条件
基本查询：
#要加上库名
USE myemployees;
#1. 查询表中单个字段
SELECT last_name FROM employees;
#2. 查询表中多个字段
SELECT last_name,salary,email FROM employees;
#3. 查询表中所有字段
SELECT * FROM employees;
#4. 查询常量值
SELECT 100;
SELECT 'weichen'
#5. 查询表达式
SELECT 100%98;
#6. 查询函数 调用函数，获得其返回值
SELECT VERSION()
#7. 起别名
1.便于理解
2.如果要查询的字段有重名的情况，使用别名可以区分开
3.若起的别名中有特殊符号，则出错
*/
#方式一：使用as
SELECT 100%98 AS 结果;
SELECT last_name AS 'out put' FROM employees;
#方式二：使用空格
SELECT last_name 姓,first_name 名 FROM employees;
#8.去重(在字段名前加上distinct)
#案例：查询员工表中涉及到的所有的部门编号
SELECT department_id FROM employees;
#9.+号的作用
java中的+号：
1.运算符，两个操作数都为数值型
2.连接符，只要有一个操作数为字符串
mysql中的+号：
仅仅只有一个功能：运算符
*/
SELECT '100'+90;
#结果：190 将字符型数值转换成数值型 转换成功，继续做加法运算
SELECT 'john'+90;
#结果：90 转换失败，将字符型数值转换成0
SELECT NULL+10;
#结果：(NULL) 只要有其中一方为null，则结果肯定为null
#实例：查询员工名和姓连接成一个字段，并显示为"姓名"
SELECT CONCAT('a','b','c')AS 结果;
#结果：abc
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees
#10.显示表departments的结构，并查询其中的全部数据
DESC departments
#字段、类型、数据等信息
#11.显示表employees的全部列，各个列之间用逗号连接，列头显示为out_put
SELECT CONCAT(first_name,',',last_name,',',commission_pct) AS out_put
FROM employees
#显示结果(NULL),因为其中有一个字段值为null，null和任意字符串连接结果都为null
#解决办法：用ifnull函数
SELECT IFNULL(commission_pct,0) AS 奖金率
FROM employees
SELECT CONCAT(first_name,',',last_name,',',IFNULL(commission_pct,0)) AS out_put
FROM employees
#结果：Steven,K_ing,0.00
条件查询：
面试题之sql分页查找
- 应用场景：要显示的数据过多，一页装不下，显示不全
- 语法：
select 查询列表
from 表
【【join type】 join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 要排序的字段】
limit offset,size;
**offset:要显示条目的起始索引（起始索引从0开始）
**size:要显示的条目个数
eg:
//查询前五条员工信息
select *
from employees
limit 0,5;
//查询第11-25条
select *
from employees
limit 10,15;
//有奖金的员工信息，将工资较高的前十名显示
select *
from employees
where commission_pct is not null
order by salary desc
limit 0,10;
- 特点：
(1) limit语句放在查询语句最后，执行顺序也是最后
(2) 要显示的页数page，每页条目数size
select 查询列表
from 表
limit (page-1)*size,size;
面试题之sql通配符匹配
- 特殊：
若在模式匹配(Pattern)中使用的字符:’^’、’-’,’]’，它们本来就包含在’[]'中，eg：
- 在mysql中，使用REGEXP和NOT REGEXP操作符
1.为了找出以“b”开头的名字，使用“^”匹配名字的开始：
mysql> SELECT * FROM pet WHERE name REGEXP '^b';
2.如果你想强制使REGEXP比较区分大小写，使用BINARY关键字使其中一个字符串变为二进制字符串。该查询只匹配名称首字母的小写‘b’：
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';
3.为了找出以“fy”结尾的名字，使用“$”匹配名字的结尾：
mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';
4.为了找出包含一个“w”的名字，使用以下查询：
mysql> SELECT * FROM pet WHERE name REGEXP 'w';
5.为了找出包含正好5个字符的名字，使用“^”和“$”匹配名字的开始和结尾，和5个“.”实例在两者之间：
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
6.你也可以使用“{n}”“重复n次”操作符重写前面的查询：
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';
区别
LIKE匹配的是整个字段的值，比如，不使用 “" 或 “%” 的话，使用上面的sql查询包含一个 “w” 的名字，LIKE “w” 是查不出来的，而REGEXP可以；
LIKE只能使用 "” 和 “%” ,不支持正则表达式。
DML之插入
- 插入语句：insert into <表名> values (<常量清单>)
insert into SC
values(104,'A',68)
DML之修改
- 修改语句：update <表名> set <目标列表达式>
update SC set grade=86
where sno=104;
DDL之删除
- 删除语句：delete from <表名>
delete from SC ---删除所有元组
where sno=102; ---删除sno=102的元组
DCL之授权
- 授权命令：grant <权限类型> on <类型><对象名> to <用户>
eg:给用户wang查询学生表的权限，并允许把权限传播
grant select on table Student to wang
grant all privileges on table Student to public ---把所有权限给所有用户
权限类型：
connect(创建表、用户时给的授权，对表可以增删改查，但是有限制)
resource(可以对表进行增删改查,也可以创表)
DBA(可以创表、创视图、增删改查等，权限最大)
DCL之收回权限
- 回收权限：revoke <权限> on <类型><对象> from <用户>
revoke select on table student from wang
revoke select,update(sno---属性名) on table student from wang ---要不停的回收权限并修改
并发控制：
- 不一致原因
(1) 丢失修改：不同事务都对同一数据进行修改，最终导致数据的不一致性。
(2) 不可重复读：
同一事务对同一数据多次读，但是得到的结果不一样。（由于其他事务对该值已经进行了修改）
(3) 读“脏”数据：读到错误的数据
面试题之数据库的隔离级别
事务的隔离级别相关内容
- 利用cmd开启sql服务：D:\MySQL\bin\mysql -uroot -p777777
- 查看事务隔离级别：
--------------------隔离级别：读未提交数据------------------------------
操作1：开启事务1和2，并设置隔离级别为未提交数据（read uncommitted）
操作2：事务1对数据库内容进行更新，但并未提交事务；
操作3：事务2查询数据库，查到的是事务1更改后但是还未提交的数据。故出现了“脏读”现象。
操作4：事务1对事务进行提交或者回滚；commit or rollback；
该级别问题：会出现“脏读”现象。
解决办法：提高隔离级别，设置为read committed；
-------------------------读已提交数据级别（read committed）---------------
操作1：给两个事务都设置隔离级别为read committed；
操作2：事务1对第一行数据进行修改操作，但并未提交；
操作3：事务2对数据库内容进行查询；
解释：查询到的依然是未进行更改前的内容，故解决了“脏读”问题。
出现问题：会出现不可重复读现象，即在一次事务中，事务2两次查询到的数据不一致。
操作4：事务1提交更改操作；
操作5：此时事务2再次对数据库进行查询
解释：此时读到的数据出现了不一致情况
解决办法：提高隔离级别，设置为可重复读（repeatable read）级别
-------------------可重复读（repeatable read）级别--------------------
操作1：更改事务1，2的隔离级别
操作2：对事务1进行更改操作
操作3：事务2查询数据库
操作4：事务1提交
操作5：事务2再次查询
解释：事务1进行更改操作的前后，事务2对数据库两次查询操作结果一致。解决了不可重复读问题。保证在一次事务中，事务2所读到的内容是完全一致的。
操作6：事务2提交，并再次读取数据库中的数据。
解释：事务2提交之后，再次查询即能查询到最新数据。
问题：会出现“幻读”情况。
----幻读：
操作1：事务1和事务2都开启新的事务；
操作2：事务2进行插入操作并提交。
操作3：事务1对数据库进行更新操作；
问题：“幻读”–原本是两行，但是却显示3行数据改变。
解决办法：提高隔离级别，设置为serializable
-----------------------可串行（serializable）--------------------
操作1：设置事务1和事务2的隔离级别为可串行化。
操作2：对事务1进行更新操作
操作3：对事务2进行插入操作
解释：此时出现错误
操作4：提交事务1
操作5：对事务2进行插入操作
解释：此时可以正常操作。相当于是加锁，等一个事务完成后，才能进行插入操纵。
自己实验：当两个事务都开启并且都未提交的情况下，给其中一个事务加锁，此时另外一个事务不能做任何操作。当一个事务提交后，才释放锁。
-----------------存储过程和函数----------------
面试题之存储过程的优缺点
优点
-
运行速度：对于很简单的sql，存储过程没有什么优势。对于复杂的业务逻辑，因为在存储过程创建的时候，数据库已经对其进行了一次解析和优化。存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用，所以执行速度会比普通sql快。
-
减少网络传输：存储过程直接就在数据库服务器上跑，所有的数据访问都在数据库服务器内部进行，不需要传输数据到其它服务器，所以会减少一定的网络传输。但是在存储过程中没有多次数据交互，那么实际上网络传输量和直接sql是一样的。而且我们的应用服务器通常与数据库是在同一内网，大数据的访问的瓶颈会是硬盘的速度，而不是网速（可使用Redis缓存解决）。
-
可维护性：的存储过程有些时候比程序更容易维护，这是因为可以实时更新DB端的存储过程。 有些bug，直接改存储过程里的业务逻辑，就搞定了。
-
增强安全性：提高代码安全，防止 SQL注入。这一点sql语句也可以做到。
-
可扩展性：应用程序和数据库操作分开，独立进行，而不是相互在一起。方便以后的扩展和DBA维护优化。
缺点
-
SQL本身是一种结构化查询语言，但不是面向对象的的，本质上还是过程化的语言，面对复杂的业务逻辑，过程化的处理会很吃力。同时SQL擅长的是数据查询而非业务逻辑的处理，如果如果把业务逻辑全放在存储过程里面，违背了这一原则。
-
如果需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新调用，等等，这时候估计会比较繁琐了。
-
不便于调试。开发调试复杂，由于IDE的问题，存储过程的开发调试要比一般程序困难。
-
没办法应用缓存。虽然有全局临时表之类的方法可以做缓存，但同样加重了数据库的负担。如果缓存并发严重，经常要加锁，那效率实在堪忧。
-
无法适应数据库的切割（水平或垂直切割）。。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。
总结
-
适当的使用存储过程，能够提高我们SQL查询的性能，
-
存储过程不应该大规模使用，滥用。
-
随着众多ORM 的出现，存储过程很多优势已经不明显。
-
SQL最大的缺点还是SQL语言本身的局限性——SQL本身是一种结构化查询语言，我们不应该用存储过程处理复杂的业务逻辑——让SQL回归它“结构化查询语言”的功用。复杂的业务逻辑，还是交给代码去处理吧。
存储过程具体操作：
- 创建：
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的sql语句)
END
参数列表：参数模式 参数名 参数类型
参数模式：
IN
OUT
INOUT
删除存储过程（一次只能删除一个）
DROP PROCEDURE 存储过程名;
查看存储过程的信息
SHOW CREATE PROCEDURE myq14
无法修改存储过程中的sql语句
IN模式案例1：创建存储过程实现，根据女神名，查询对应的男神信息
#创建
DELIMITER $
CREATE PROCEDURE myp2(IN name_girl VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty be
ON bo.id=be.boyfriend_id
WHERE be.name=name_girl;
END $
#调用
CALL myp2('周冬雨')$
IN模式案例2：创建存储过程实现，用户是否登录成功
DELIMITER $
CREATE PROCEDURE myp4(IN uname VARCHAR(20),IN upassword VARCHAR(20))
BEGIN
DECLARE result VARCHAR(20) DEFAULT ''; #声明并初始化局部变量，存放返回结果
SELECT COUNT(*) INTO result #局部变量的赋值
FROM admin
WHERE admin.`username`=uname
AND admin.`password`=upassword;
SELECT IF(result>0,'登陆成功','登录失败');
END $
OUT模式：
OUT模式案例1：根据女神名，返回对应的男神名
#创建
DELIMITER $
CREATE PROCEDURE myp7(IN bname VARCHAR(20),OUT uname VARCHAR(20))
BEGIN
SELECT bo.boyName INTO uname
FROM boys bo
RIGHT JOIN beauty be
ON bo.id=be.boyfriend_id
WHERE be.name=bname;
SELECT uname;
END $
#调用
SET @bName #定义用户变量---可以在begin end以外使用的变量
CALL myp7('热巴'，@bName)$
OUT模式案例二：根据女神名，返回对应的男神名和男神魅力值
DELIMITER $
CREATE PROCEDURE myq8(IN bname VARCHAR(20),OUT boname VARCHAR(20),OUT cp INT)
BEGIN
SELECT bo.boyName,bo.userCP INTO boname,cp
FROM beauty b
LEFT JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE b.name=bname;
END $
CALL myq8('热巴',@name,@cpu)$
INOUT模式案例1：传入a和b两个值，最终a和b都翻倍并返回
#创建：
DELIMITER $
CREATE PROCEDURE myq10(INOUT a INT,INOUT b INT)
BEGIN
SELECT 2*a,2*b INTO a,b;
END $
错误示例：call myq10(2,3)$ 这样的话返回值没地方放，所以要传入两个已经被赋值过的变量。
首先要定义两个用户变量，来最终显示所要的值
#调用
SET @m=10$
SET @n=11$
CALL myq10(@m,@n)$
课后案例一：创建存储过程实现传入用户名和密码，插入到admin表中。
DELIMITER $
CREATE PROCEDURE myq11(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
INSERT
INTO admin(`username`,`password`)
VALUES(username,PASSWORD);
END $
课后案例二：创建存储过程或函数实现传入女神编号，返回女神名称和女神电话
DELIMITER $
CREATE PROCEDURE myq13(IN gid INT,OUT gname VARCHAR(50),OUT gphone VARCHAR(11))
BEGIN
SELECT NAME,phone INTO gname,gphone
FROM beauty
WHERE id=gid;
END $
CALL myq13(1,@gname,@gphone)$
课后案例三：创建存储过程或函数实现传入两个女神生日，返回大小
DELIMITER $
CREATE PROCEDURE myq14(IN gone DATETIME,IN gtwo DATETIME,OUT bs VARCHAR(11))
BEGIN
DECLARE result INT DEFAULT 0;
SELECT DATEDIFF(gone,gtwo) INTO result;
SELECT IF(result>0,'女神一比女神二大','女神一比女神二小') INTO bs;
END $
CALL myq13(1,@gname,@gphone)$
课后案例四：创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回。
DELIMITER $
CREATE PROCEDURE myq15(IN gone DATETIME,OUT bs VARCHAR(50))
BEGIN
#set bs=date_format(gone,'%y年%m月%d日');
SELECT DATE_FORMAT(gone,'%y年%m月%d日') INTO bs;
END $
课后案例五：创建存储过程或函数实现传入女神名称，返回：女神 AND 男神格式的字符串
DELIMITER $
CREATE PROCEDURE myq17(IN gName VARCHAR(20),OUT gb VARCHAR(50))
BEGIN
SELECT CONCAT(be.name,'and',IFNULL(bo.boyName,'null')) INTO gb
FROM beauty be
LEFT JOIN boys bo
ON be.boyfriend_id=bo.id
WHERE be.name=gName;
#set gb=concat(be.name,bo.boyName);
END $
课后案例六：创建存储过程或者函数，根据传入的条目数和起始索引，查询beauty表的记录
DELIMITER $
CREATE PROCEDURE myq17(IN b_index INT,IN size INT)
BEGIN
SELECT * FROM beauty LIMIT b_index,size;
END $
流程控制：
- 分支结构
(1) if函数 if(表达式1，表达式2，表达式3)
(2) case结构
/*
类似java中switch,语法：
case 变量|表达式|字段
when 要判断的值 then 返回的值1或语句1;
when 要判断的值 then 返回的值2或语句2;
...
else 要返回的值n或语句n;
end case;
类似java中多重if,语法：
case 变量|表达式|字段
when 要判断的条件1 then 返回的值1或语句1
when 要判断的条件2 then 返回的值2或语句1
...
else 要返回的值n
end
*/
示例：
创建存储过程，根据传入的成绩，显示等级，比如传入的成绩：90-100，A。80-90，B。60-80，C。其他，D。
DELIMITER $
CREATE PROCEDURE test1(IN grade INT)
BEGIN
CASE
WHEN grade>=90 AND grade <=100 THEN SELECT 'A';
WHEN grade>=80 THEN SELECT 'B';
WHEN grade>=60 THEN SELECT 'C';
ELSE SELECT 'D';
END CASE;
END $
(3) if结构
if结构
/*
语法：
if 条件1 then 语句1；
elseif 条件2 then 语句2；
...
end if;
*/
if结构案例：
if结构案例
根据传入的成绩返回等级
DELIMITER $
CREATE FUNCTION test2(grade INT) RETURNS VARCHAR(20)
BEGIN
IF grade>=90 THEN RETURN'A';
ELSEIF grade>=80 THEN RETURN 'B';
ELSEIF grade>=60 THEN RETURN 'C';
ELSE RETURN 'D';
END IF;
END $
- 循环结构
WHILE 先判断后执行
/*
语法：
【标签】while 循环条件
do
循环体;
end while【标签】;
java中：
while(循环条件)(
循环体;
)
*/
案例1：
批量插入，根据次数插入到admin表中多条记录
CREATE PROCEDURE test2(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=insertCount DO
INSERT INTO admin(`username`,`password`) VALUES(CONCAT('Rose',i),'666');
SET i=i+1;
END WHILE;
END $
添加leave语句：
案例：批量插入，根据次数插入到admin表中多条记录
CREATE PROCEDURE test2(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=insertCount DO
INSERT INTO admin(`username`,`password`) VALUES(CONCAT('Rose',i),'666');
SET i=i+1;
END WHILE;
END $
添加iterate语句：
TRUNCATE TABLE admin$
CREATE PROCEDURE test5(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
b:WHILE i<=insertCount DO
SET i=i+1;
IF i%2!=0 THEN ITERATE b;
END IF;
INSERT INTO admin(`username`,`password`) VALUES(CONCAT('weichen',i),'666');
END WHILE b;
END $
CALL test5(20)$
WHILE 先判断后执行
/*
语法：
【标签】while 循环条件
do
循环体;
end while【标签】;
java中：
while(循环条件)(
循环体;
)
*/
LOOP 没有条件的死循环
/*
语法：
【标签】loop
循环体;
end loop 【标签】;
可以用来模拟简单的死循环
*/
REPEAT 先执行后判断 类似于java中的do WHILE
DO{
循环体
}WHILE(循环条件)
当满足循环条件的时候继续执行
/*
语法：
【标签】repeat
循环体;
until 结束循环的条件
end repeat 【标签】;
*/
LOOP 没有条件的死循环
```sql
/*
语法：
【标签】loop
循环体;
end loop 【标签】;
可以用来模拟简单的死循环
*/
REPEAT 先执行后判断 类似于java中的do WHILE
DO{
循环体
}WHILE(循环条件)
当满足循环条件的时候继续执行
/*
语法：
【标签】repeat
循环体;
until 结束循环的条件
end repeat 【标签】;
*/
循环结构案例：
面试题之sql分页查找
- 应用场景：要显示的数据过多，一页装不下，显示不全
- 语法：
select 查询列表
from 表
【【join type】 join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 要排序的字段】
limit offset,size;
**offset:要显示条目的起始索引（起始索引从0开始）
**size:要显示的条目个数
eg:
//查询前五条员工信息
select *
from employees
limit 0,5;
//查询第11-25条
select *
from employees
limit 10,15;
//有奖金的员工信息，将工资较高的前十名显示
select *
from employees
where commission_pct is not null
order by salary desc
limit 0,10;
- 特点：
(1) limit语句放在查询语句最后，执行顺序也是最后
(2) 要显示的页数page，每页条目数size
select 查询列表
from 表
limit (page-1)*size,size;
面试题之sql通配符匹配
Mysql高级
索引
-
索引存储在文件中，在磁盘中，寻道、磁盘、磁头读取数据，读取到一个数据称为一次I/O操作。
-
Explain：作用1，表的读取顺序
-
id相同：
id不同，子查询
id有相同有不同
type：
system：表中只有一行数据，const：查出来一条记录
eq_ref:
唯一性索引扫描，对于每一个索引键，表中只有一条记录与之匹配，常见于主键或者唯一索引扫描. -
在t2表中搜索id，所以是all
-
根据t2.id去t1里面搜索对应的记录
ref:
非唯一性索引扫描，返回匹配某个单独值的所有行。比如：一个部门有很多个程序员。在员工表中，根据部门号查程序员，能查到好多程序员，此时员工表的type就为ref.
1.根据条件col=‘ac’在表1中查询，由于可以查到多条记录，所以type为ref.
range:
只检索给定范围的行，使用一个索引来选择行。
一般是在where语句中出现between、<、>、in等的查询
只需要开始于索引的某一点，结束于另一点。
index:
all:
ref
解释：t1中的col1匹配t2表的col1【库.表.字段】。t1中的col2匹配一个常量。
rows
没有建立索引需要查询的行数：
建立索引之后需要查询的行数：
Using filesort：
mysql无法利用已有索引完成排序工作，从而对数据使用一个外部的索引排序
using temporary:使用了临时表保存中间结果，Mysql在对查询结果排序时使用临时表。
常见于排序order by和分组查询group by.【临时表的创建很伤性能】
所以当使用order by和group by的时候，尽量和索引列的顺序和个数保持一致。
比喻：索引是一楼二楼，现在让直接到二楼，则需要自备梯子，利用文件排序
using index：
热身：
4:select name,id from t2。
3:select id,name from t1 where other_column=’’
2:select id from t3
1:select d1.name,d2 from d1
null:整个语句
单表
查询单表：
- 未加索引：
结果：
- 给最终查询中需要的字段都添加索引
查询所添加索引：
结果：
分析：extra中使用的using filesort 无法接受。
按照BTree索引的工作原理，先对category_id进行排序。若category_id相同，则排序comments。若comments相同，则再排序views。由于comments>1条件是一个范围值，MySQL无法利用索引在对后面的views部分进行检索，即range类型查询字段后面的索引无效。
尝试更改索引：
结果：
。
两表
左连接：
- 未加索引：
- 给左表的字段添加索引：
执行语句：
结果：
- 给右表的字段添加索引：
执行语句：
结果：
结论：相反加。左连接的时候将索引添加到右表上，右连接将索引添加到左表上。
三表
三表左连接
- 未加索引：
- 给book表和phone表分别加上索引。
添加索引：
结果：
结论：join语句的优化 - 尽可能减少join语句中的NestedLoop的循环总次数：“永远用小结果集驱动大的结果集”。比如class(书的类别)，book(书)，此时就需要给书添加索引。
- 优先优化NestedLoop的内层循环；
- 保证Join语句中被驱动表上Join条件字段已经被索引；eg：保证book被索引
- 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置。
索引失效的原因：
-
全值匹配我最爱。所建立的索引字段个数个顺序和将要查询的完全吻合。
前提：建立的索引为nameagepos
情况1：查找字段为name
情况2：查找字段为name和age
情况3：查找字段为name、age、pos
key_len:越长表示精度越高，但相应其他消耗也会多。 -
最左前缀匹配：如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。
情况1：查询字段为pos。
情况2：查询字段为age、pos。
情况3：查询字段为name。
-
不在索引列上做任何操作（计算、函数、（自动 or 手动）类型转换），会导致索引失效而转向全表扫描。
情况一：正常
情况二：对索引列加上操作。
-
存储引擎不能使用索引中范围条件右边的列：
情况一：当是具体值的时候：
情况二：当是范围的时候：
解释：此时对name着重查找，对age着重排序。此时对pos字段的索引失效。
解释：此时用上了using index，。。不太懂。
-
尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），尽量避免select *。
前言：建立索引时，不管索引建了哪个字段。select id都能和其搭配，最终用上unsing index。若索引建了所有字段，此时select 也可以用上索引。
情况1：用select *
情况2：用什么取什么（疑问：若用的只有一个字段，而不是和索引字段重复的呢？）
解释：多了一个using index，好。 using index应该是针对主键索引来说的，当查询字段（select）在索引范围之内，则可以用上主键索引。
解决问题：用和用所需字段名的区别。
结论：不管查询字段有几个，区别都是extra中是否有using index。
-
mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描。
使用等号：
使用不等号：
此时索引失效。 -
is not null 也无法使用索引,但是is null是可以使用索引的。问题(下面两种情况怎么回事儿 )
-
like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作。
解决办法：
a. 把%放在右边。
b. 建立复合索引。
case1：%july%
case2：%july
case3：july%
面试：
解决like‘%字符串%’时索引不被使用的方法。
建立复合索引：
case1：
case2：
case3：？？？有疑问，id自带主键索引，为什么会用上…
case4：
case5：
总结：当建立了复合索引之后，select后面跟的字段只要是在索引范围内，或者是主键，单个或者多个都可以用上建立的索引using index。
case 6：
case 7：
case 8：
case 9：
case10：
case9和case10总结：
查询字段可以在索引范围之内，但是不能超过索引范围。
- 字符串不加单引号索引失效。
Mysql进行了自动的隐式类型转换操作
- 少用or，用它连接时会导致索引失效。但是自己试的时候没有失效。。。
题目：
创建索引idx_test03_c1234
表：
order by：
a. order by后面的字段顺序需要和索引字段的一致（常数除外）
b. key_len不计数。
case 1：
解释：所建立的索引字段为c1,c2,c3,c4,但是表中一共有五个字段，select*超过了索引范围，故此处索引失效。
case 2：
按照索引顺序来。
ca’se 3：
总结：mysql会先排序。
case 4：
总结：范围之后全失效，第一个c3>‘a3’，只能勉强用上一部分，用上了排序。第二个首先要排序。ref=const是最确定的情况。
case 5:
总结：c3也用到了，但是只用到了排序，没有统计到里面。。。为什么呢…和范围的区别，此处的类型是ref类型。
case 6:
总结：range还能沾上点边，到了order by之后，就自己排序去了，和c4也一点关系都没有了。
case 7:
总结：order by后面的字段如果和建立的索引项字段顺序不一致，一般情况下都会出现using filesort。
特例：
总结：若前面已经有c2=‘c2’,然后后面还对c2进行了order by排序，此时的c2相当于一个常数。
like：
总结：可以为后面的铺路，比如26个英文字母，开头指定了一个字母，那就在这个字母后面找。但是如果是大于或者小于号的时候，范围太广了。所以范围后面的就失效了。
case1：
case 2：
group by：分组
总结：基本上都要排序，当排序错乱的时候，需要建立临时表。
分组之前必排序
case 1：
总结：按顺序来的时候和order by效果一样
case 2：
索引优化总结：
- 对于单键索引，尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠左越好。（最左前缀匹配）
- 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引（全局匹配我最爱）
- 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面.(范围后面的索引失效)
- 书写sql语句时，尽量避免造成索引失效的情况
总结
优化步骤：
- 慢查询的开启并捕获。
- explain+慢SQL分析。
- show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况。
- SQL数据库服务器的参数调优。
查询优化：
- 优化原则：小表驱动大表，即小的数据集驱动大的数据集。自己理解：先执行谁。
eg：
select * from A where id in (select id from B)
等价于：
for select id from B
for select * from A where A.id=B.id
当A的数据集>B的数据集时，用in比用exists好
select * from A where exists(select 1 from B where B.id=A.id)
等价于
for select * from A
for select * from B where B.id=A.id
#额外：
exists语法：select... from table where exists(subquery)
理解：将主查询的数据放到子查询中做条件验证，根据验证结果（True 或者 False）决定主查询的数据结果得以保留。
a. exists(subquery)只返回True 或者False，所以子查询中的select *可以是select 1或者select ‘X’
b. exists子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，若担心效率问题，可进行实际检验。
c. exists子查询往往也可以用条件表达式、其他子查询或者JOIN代替，何种最优需要具体问题具体分析。
当A表的数据集小于B表的数据集时，用exists比用in好。
order by优化：
- order by后面按序，不会产生using filesort
- order by后面字段没有按序，故产生了using filesort
3.
解释：此处的using index是select *中使用的，因为此处所有字段都建立的有索引。
自己小结：只要order by后面和所建索引项顺序一致，就不会产生using filesort。若不一致，则会产生。和where后面的语句无关。 - 带头大哥在，默认升序
排序索引优化小结：
case 1： key age_birth(age,birth)
order by能使用索引最左前缀
case 2：
若where使用索引的最左前缀定义为常量，则order by能使用索引。
case 3：不能使用索引进行排序
—有升序有降序
—丢失最左索引
—大哥只要不是常量定值，其他都不好用
group by关键字优化
面试题之sql慢可能存在哪些问题
一共两个方面：
一、大多数情况是正常的，只是偶尔会出现很慢的情况。
- 数据库刷新脏页
当我们要进行插入或者更新操作时，数据库会在内存将这些字段进行更新。但并不会马上持久化到磁盘中，而是将这些记录写到redo log日志中，空闲时，通过redo log里面的日记把最新的数据同步到磁盘、但redo log日志容量有限，当被写满，就没办法等到空闲时把数据库同步到磁盘，所以暂停其他操作，专心同步，此时就会导致正常的SQL语句突然执行很慢。 - 拿不到锁
我们要执行的这条语句，刚好这条语句涉及到的表，别人在用，并且加锁了，我们拿不到锁，只能慢慢等待别人释放锁了。
二、在数据量不变的情况下，这条SQL语句一直以来都执行的很慢。
3. 没有建立索引
4. 索引失效
慢查询日志
- 查看和开启，以及系统默认响应时间阈值。
注：当long_query_time的值为10的时候，并不会被记录下来，当大于long_query_time时，才会被记录下来。
- 查看慢日志内容。
[mysqld]配置版：
slow_query_log=1;
slow_query_log_file=/var/lib/mysql/…-slow.log
long_query_time=3
log_output=file
show profile(比全局查询日志功能更强大)
- 执行步骤
a. show variables like ‘profiling’;
b. set profiling=on
c. 执行sql语句
d. show profiles
e. show profile cpu,block io for query 。
全局查询日志
- 作用：记录查询的所有sql语句。
- 使用步骤：
a.
b.
c.
d.
e.
加读锁
- 加锁：
- 查看锁
- 需要注意的操作：
当session 1对表admin加上了读锁之后。
session 1可以进行的操作：
case 1:对表admin进行读操作 【可】
case 2：对admin进行写操作 【不可】
case 3：读别的表 【不可】
解释：session 1已经把admin表锁上了，需要给一个时间，得把当前的栈清空了才能干别的事情。把它锁上之后要把自己要做的事情做了之后进行解锁，然后才能继续干别的事情。
session 2执行的操作：
case 1：对admin表进行读操作
case 2：对admin表进行写操作。
解释：被阻塞。在等待session 1释放锁后执行。
4. 加写锁：
session 1：
session 2：
总结：
读锁：session1加了对表1加了读锁之后，只能专心的对表一进行读操作，其它任何操作都不行。因为读锁是共享锁，所以session 2可对表一进行读操作，但是写操作就会被堵塞了，因为人家session 1把它锁起来肯定是有用的，你只能等它完事儿之后才能对表进行操作。
写锁：session 1对表1加了写锁之后，可对表1进行读写，同样只能专心干这件事儿，不能转去看别的表。因为写锁是排他锁，所以session 2自然不能对表1进行读写操作，只能被阻塞起来。
行锁：
- session 1更新但不提交。
- session 2同样对同行进行修改操作。
- session 1提交修改
- session 2在session 1提交修改后可执行修改操作。
- 最终session 1和session 2都提交，哪个后提交
- ，读取到的就是哪个session更改后的数据。若session 1对数据进行修改但并未提交，此时在session 2中读取到的数据依然是原汁原味的数据。此处避免了脏读。 当session 1提交之后，若session 2也未提交，则读取到的依然是原汁原味的数据。此处避免了不可重复读的问题。
- 若session 1和session 2更改的不是同一行的数据，则相互之间无影响。
- 此处的前提是，为了演示行锁的特征，将两个session都自动提交取消，改为手动提交。若session 3未更改自动提交的设置，则当session 1更改完数据并提交后，session 3不用手动提交就可以看到更改后的数据。
索引失效会使行锁变成表锁。比如讲password=‘qqq’写成password=qqq。此时索引失效。session 1和session 2更改的是同一行的数据时相互之间有影响。
间隙锁：
session 1：
session 2：
被阻塞----
未被阻塞。
只要session 1进行写操作，InnoDB自动加上写锁。
面试题–给某一行加锁：
- 步骤：
begin;
select* from table_name where a=8 for update;
commit
- 演示：
session 2：
行锁分析：
状态量解释： - 等待总时长。
- 等待平均时长
- 系统启动后到现在总共等待的次数。
若等待次数高，时间长，要用show profile进行优化。
优化建议：
a. 尽可能让所有数据检索都通过索引完成，避免无索引行锁升级为表锁
b. 合理设计索引，尽量缩小锁的范围
c.尽可能较少检索条件，避免间隙锁。
d. 尽量控制事务大小，减少锁定资源量和时间长度。
e. 尽可能低级别事务隔离。
主从复制配置：
主：
从：
查看从机配置状态：
MySQL面试题汇总：
-
为什么要使用数据库
数据保存在内存
优点： 存取速度快
缺点： 数据不能永久保存
数据保存在文件
优点： 数据永久保存
缺点：1）速度比内存操作慢，频繁的IO操作。2）查询数据不方便
数据保存在数据库
1）数据永久保存
2）使用SQL语句，查询方便效率高。
3）管理数据方便 -
什么是SQL？
结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询语言。 -
什么是MySQL?
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。 -
数据库三大范式是什么
第一范式：每个分量不可再分
第二范式：消除部分依赖
第三范式：消除传递依赖
在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。 -
mysql有关权限的表都有哪几个
MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：
- user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
- db权限表：记录各个帐号在各个数据库上的操作权限。
- table_priv权限表：记录数据表级的操作权限。
- columns_priv权限表：记录数据列级的操作权限。
- host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。
- MySQL的binlog有有几种录入格式？分别有什么区别？
MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。
- binlog 的主要目的是复制和恢复。
- Binlog日志的两个最重要的使用场景:
a. MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
b. 数据恢复：通过使用 mysqlbinlog工具来使恢复数据 - Binlog的日志格式：
a. STATEMENT：基于SQL语句的复制（statement-based replication, SBR）
b. ROW：基于行的复制（row-based replication, RBR）
c. MIXED：混合模式复制（mixed-based replication, MBR）
—a. statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行的时候相同的结果。另外mysql的复制，像一些特定函数的功能，slave与master要保持一致会有很多相关问题。
–b. row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。[新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。]
—c. mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。
主从复制
Mycat(数据库中间件)
1. 连接数据库和java程序
java把mycat看成数据库
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
