概述
SQL语言分类
1、DML（Data Manipulation Language):数据操纵语句，用于添加、删除、修改、查询数据库记录，并检查数据完整性
- INSERT：添加数据到数据库中
- DELETE：删除数据库中的数据
- UPDATE：修改数据库中的数据
- SELECT：选择（查询）数据
2、DDL（Data Definition Language):数据定义语句，用于库和表的创建、修改、删除。
- CREATE ：创建数据库表
- DROP ：删除表
- ALTER ：更改表结构、添加、删除、修改列长度
3、DCL（Data Control Language):数据控制语句，用于定义用户的访问权限和安全级别。
- GRANT：授予访问权限
- REVOKE：撤销访问权限
- COMMIT：提交事务处理
- ROLLBACK：事务处理回退
- SAVEPOINT：设置保存点
- LOCK：对数据库的特定部分进行锁定
@面：Mysql服务器默认端口是
Mysql服务器的默认端日是3306
表数据
1、表一
beauty
boys
2、表二
departments
employees
jobs
locations
job_grades
安装与使用
启动和停止MySQL服务
方式一：通过计算机管理方式
右击计算机—管理—服务—启动或停止MySQL服务
方式二：通过命令行方式
启动：net start mysql服务名
停止：net stop mysql服务名
登录
mysql –h 主机名 –u 用户名 –p 密码
退出
exit
查询
运行顺序：
2 3 4 5 → 1 → (窗口函数)→ 6 7
（各部分内子查询优先）
1 select 查询结果
2 from 从哪张表中查找数据（联结）
3 where 查询条件（运算符、模糊查询）
4 group by 分组（每个）
5 having 对分组结果指定条件
6 order by 对查询结果排序
7 limit 从查询结果中取出指定行
条件判断的先后顺序：
（） not and or
基本的SELECT语句
1、选择全部列
SELECT *
FROM departments;
2、选择特定的列
SELECT department_id, location_id
FROM departments;
3、列的别名
SELECT last_name AS name, commission_pct comm
FROM employees;
或者
SELECT last_name 'Name', salary*12 'Annual Salary'
FROM employees;
4、字符串
字符串可以是SELECT列表中的一个字符,数字,日期。
日期和字符只能在单引号中出现。
每当返回一行时，字符串被输出一次。
5、显示表结构
DESCRIBE employees
汇总分析：
1.查询课程编号为“0002”的总成绩
select 课程号,sum(成绩) as 总成绩 from score where 课程号='0002';
2.查询选了课程的学生人数
select COUNT(DISTINCT 学号) as 选课人数 from score;
3.查询各科成绩最高和最低的分
select 课程号,max(成绩) as 最高分,min(成绩) as 最低分 from score group by 课程号;
4.查询每门课程被选修的学生数
select 课程号,count(学号) as 选修人数 from score group by 课程号;
5.查询男生、女生人数
select sum(case when 性别='男' then 1 else 0 end) as 男生人数,sum(case when 性别='女' then 1 else 0 end) as 女生人数 from student;
或
select 性别,count(学号) as 人数 from student group by 性别;
6.查询平均成绩大于60分学生的学号和平均成绩
select 学号,avg(成绩) as 平均成绩 from score group by 学号 having avg(成绩)>60;
7.查询至少选修两门课程的学生学号
select 学号, count(课程号) as 选修课程数 from score group by 学号 having count(课程号)>=2;
8.查询同名同姓学生名单并统计同名人数
select 姓名,count(姓名) as 同名人数 from student group by 姓名 having count(姓名)>1;
9.查询不及格的课程及按课程号从大到小排列
select 课程号 from score where 成绩<60 order by 课程号 desc;
10.查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
select 课程号,avg(成绩) as 平均成绩 from score group by 课程号 order by 平均成绩,课程号 desc;
11.检索课程编号为“0004”且分数小于60的学生学号，结果按分数降序排列
select 学号,成绩 from score where 课程号='0004' and 成绩<60 order by 成绩 desc;
12.统计每门课程的学生选修人数(超过2人的课程才统计)
要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
select 课程号,count(学号) as 选修人数 from score group by 课程号 having count(学号)>2 order by 选修人数 desc,课程号;
13.查询两门以上不及格课程的同学的学号及其平均成绩。同时存在where和having，注意区别
select 学号,avg(成绩) as 平均成绩 from score where 成绩<60 group by 学号 having count(成绩)>=2;
复杂查询：
子查询
1.查询所有课程成绩小于60分学生的学号、姓名
select A.学号,A.姓名
from student as A
left join score as B
on A.学号=B.学号
group by B.学号
having max(B.成绩<60);
或
select 学号,姓名 from student where 学号 in (select 学号 from score group by 学号 having max(成绩<60));
子查询+in
2.查询没有学全所有课的学生的学号、姓名
select 学号,姓名 from student where 学号 in(select 学号 from score group by 学号 having count(课程号)<(select count(课程号) from course));
3.查询出只选修了两门课程的全部学生的学号和姓名
select 学号,姓名 from student where 学号 in(select 学号 from score group by 学号 having count(课程号)=2);
日期函数
4.1990年出生的学生名单
select 学号,姓名 from student where year(出生日期)='1990';
topN问题 ！！！隐藏分组，重点掌握！！！
5.按课程号分组取成绩最大值所在行的数据（分组取每组最大值）
select * from score as A where 成绩=(select max(成绩) from score as B where A.课程号=B.课程号);
6.查询各科成绩前两名的记录（每组最大的N条记录） ！！！表的加法、自定义变量！！！
(select * from score where 课程号='0001' order by 成绩 desc limit 2)
union all
(select * from score where 课程号='0002' order by 成绩 desc limit 2)
union all
(select * from score where 课程号='0003' order by 成绩 desc limit 2);
每门功成绩最好的前两名
SELECT s1.*
FROM score s1
WHERE
(
SELECT COUNT(1)
FROM score s2
WHERE s1.c_id=s2.c_id AND s2.s_score>=s1.s_score
)<=2
ORDER BY s1.c_id,s1.s_score DESC;
多表查询：
1.查询所有学生的学号、姓名、选课数、总成绩
select A.学号,A.姓名,count(B.课程号) as 选课数,sum(B.成绩) as 总成绩
from student as A
left join score as B
on A.学号=B.学号
group by B.学号;
2.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select A.学号,A.姓名,avg(B.成绩) as 平均成绩
from student as A
left join score as B
on A.学号=B.学号
group by B.学号
having avg(B.成绩)>85;
3.查询学生的选课情况：学号，姓名，课程号，课程名称
select A.学号,A.姓名,B.课程号,C.课程名称
from student as A
left join score as B on A.学号=B.学号
inner join course as C on B.课程号=C.课程号;
4.查询出每门课程的及格人数和不及格人数
select 课程号,
sum(case when 成绩>=60 then 1 else 0 end) as 及格人数,
sum(case when 成绩<60 then 1 else 0 end) as 不及格人数
from score group by 课程号;
5.使用分段[100-85],[85-70],[70-60],[‹60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称
select A.课程号,A.课程名称,
sum(case when B.成绩 between 85 and 100 then 1 else 0 end) as [100-85],
sum(case when B.成绩<85 and B.成绩>=70 then 1 else 0 end) as [85-70],
sum(case when B.成绩<70 and B.成绩>=60 then 1 else 0 end) as [70-60],
sum(case when B.成绩<60 then 1 else 0 end) as [<60]
from course as A
left join score as B on A.课程号=B.课程号
group by A.课程号,A.课程名称;
6.查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名
select 学号,姓名
from student as A
left join score as B on A.学号=B.学号
where 课程号='0003' and 成绩>80;
过滤和排序数据
1、WHERE子句
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90 ;
WHERE一定放在FROM后面
2、比较运算
SELECT last_name, salary
FROM employees
WHERE salary <= 3000;
3、BETWEEN
使用 BETWEEN 运算来显示在一个区间内的值(包含边界)
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;
4、IN
显示列表中的值
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201);
5、LIKE
使用 LIKE 运算选择类似的值
选择条件可以包含字符或数字，可以同时使用:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';
6、NULL
使用 IS (NOT) NULL 判断空值。
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;
7、AND
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >=10000
AND job_id LIKE '%MAN%';
8、OR
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
OR job_id LIKE '%MAN%';
9、NOT
SELECT last_name, job_id
FROM employees
WHERE job_id
NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');
10、【ORDER BY】
用在SELECT语句的结尾
ASC（ascend）: 升序
DESC（descend）: 降序
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;
降序排序
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC ;
按别名排序
SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal;
【多个列排序】
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;
分组函数
分组函数作用于一组数据，并对一组数据返回一个值。
1、AVG（平均值）和 SUM （合计）
可以对【数值型】数据使用
SELECT AVG(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
2、MIN（最小值）和 MAX（最大值）
可以对【任意数据类型】的数据使用
SELECT MIN(hire_date), MAX(hire_date)
FROM employ
3、COUNT（计数）
COUNT(expr) 返回expr不为空的【记录总数】
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;
4、GROUP BY
可以使用GROUP BY子句将表中的数据分成若干组
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
a.求出EMPLOYEES表中各部门的平均工资
1）在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中。
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;
2）包含在GROUP BY子句中的列不必包含在SELECT列表中
SELECT AVG(salary)
FROM employees
GROUP BY department_id ;
b.使用多个列分组
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;
5**、过滤分组： HAVING**
使用 HAVING 过滤分组:
- 行已经被分组。
- 使用了组函数。
- 满足HAVING 子句中条件的分组将被显示。
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;
非法使用组函数：
- 不能在 WHERE 子句中使用组函数。
- 可以在 HAVING 子句中使用组函数。
多表查询
1、内连接：等值连接、非等值连接、自连接 -------------- 取交集
select 查询列表
from 表1 别名
join 表2 别名
on 连接条件;
特点：
①添加排序、分组、筛选
②inner可以省略
③筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
1）等值连接
1）多表等值连接的结果为多表的交集部分
2）n表连接，至少需要n-1个连接条件
3）多表的顺序没有要求
4）一般需要为表起别名
5）可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
案例1：查询女神名称和对应男神名称
SELECT beauty.id,NAME,boyname
FROM beauty ,boys
WHERE beauty.`boyfriend_id`=boys.id; #条件是等号为等值连接
区分重复的列名
- 使用表名前缀在多个表中区分相同的列。
- 在不同表中具有相同列名的列可以用表的别名加以区分。
- 如果使用了表别名，则在select语句中需要使用表别名代替表名
表的别名
- 使用别名可以简化查询。
- 使用表名前缀可以提高执行效率。
SELECT bt.id,NAME,boyname
FROM beauty bt,boys b;
WHERE bt.`boyfriend_id`=b.id ;
2）非等值连接
案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';
3）自连接
#案例：查询 员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
2、外连接：左外连接、右外连接、全外连接。
用于查询一个表中有，另一个表没有的记录
特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
@面： 外连接、内连接与自连接区别
交叉连接:
交叉连接又叫笛卡尔积，它是指不使用任何条件，直接将一个表的所有记录和另一个表中的所有记录一一匹配。
内连接
则是只有条件的交叉连接，根据某个条件筛选出符合条件的记录，不符合条件的记录不会出现在结果集中，即内连接只连接匹配的行。
外连接
其结果集中不仅包含符合连接条件的行，而且还会包括左表、右表或两个表中的所有数据行，这三种情况依次称之为左外连接，右外连接，和全外连接。
左外连接
也称左连接，左表为主表，左表中的所有记录都会出现在结果集中，对于那些在右表中并没有匹配的记录，仍然要显示，右边对应的那些字段值以NULL来填充。
右外连接
也称右连接，右表为主表，右表中的所有记录都会出现在结果集中。左连接和右连接可以互换，MySQL目前还不支持全外连接。
1）左外连接
查询男朋友不在男神表的的女神名
#左外连接
SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boy bo
ON b.`boyfriend_id` = bo.`id`
WHERE b.`id` IS NULL;
2）右外连接
SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
3）全外连接
USE girls;
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.id;
3、交叉连接
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
4、使用ON 子句创建连接
- 自然连接中是以具有相同名字的列为连接条件的。
- 可以使用 ON 指定额外的连接条件。
- 这个连接条件是与其它条件分开的。
- ON子句使语句具有更高的易读性。
5、多表连接
连接多个表
连接n个表,至少需要n-1个连接条件
SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;
6、John连接总结
常见函数
字符函数
#1.length 获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha');
#2.concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名
FROM employees;
#3.upper、lower
SELECT UPPER('john');
SELECT LOWER('joHn');
#4.substr、substring
注意：索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;
#案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put
FROM employees;
#5.instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;
#6.trim
SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;
#7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',2,'*') AS out_put;
#8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'ab') AS out_put;
#9.replace 替换
SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
数学函数
#round 四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);
#ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.02);
#floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99);
#truncate 截断
SELECT TRUNCATE(1.69999,1);
#mod取余
SELECT MOD(10,-3);
SELECT 10%3;
日期函数
#now 返回当前系统日期+时间
SELECT NOW();
#curdate 返回当前系统日期，不包含时间
SELECT CURDATE();
#curtime 返回当前时间，不包含日期
SELECT CURTIME();
#可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
#str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
#查询入职日期为1992--4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3';
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
#date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;
控制函数
#1.if函数：
SELECT IF(10<5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注
FROM employees;
#2.case函数的使用一： switch case 的效果
/*
mysql中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end
*/
/*案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
*/
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;
#3.case 函数的使用二：类似于 多重if
/*
mysql中：
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end
*/
#案例：查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000，显示C级别
否则，显示D级别
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
@面：通用SQL函数
1、CONCAT(A, B) 连接两个字符串值以创建单个字符串输出。通常用于将两个
或多个字段合并为一个字段。
2、FORMAT(X, D) 格式化数字 X 到 D 有效数字。
3、CURRDATE(), CURRTIME() 返回当前日期或时间。
4、NOW（） 将当前日期和时间作为一个值返回。
5、MONTH（），DAY（），YEAR（），WEEK（），WEEKDAY（） 从日期值中提取给定数据。
6、HOUR（），MINUTE（），SECOND（） 从时间值中提取给定数据。
7、DATEDIFF（A，B） 确定两个日期之间的差异，通常用于计算年龄
8、SUBTIMES（A，B） 确定两次之间的差异。
9、FROMDAYS（INT） 将整数天数转换为日期值。
创建和管理表
库的管理
1、库的创建
create database [if not exists] 库名;
案例：创建库Books
CREATE DATABASE IF NOT EXISTS books ;
2、库的修改
RENAME DATABASE books TO 新库名;
#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;
3、库的删除
DROP DATABASE IF EXISTS books;
表的管理
1.表的创建
语法：
create table 表名(
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
...
列名 列的类型【(长度) 约束】
)
#案例：创建表Book
CREATE TABLE book(
id INT,#编号
bName VARCHAR(20),#图书名
price DOUBLE,#价格
authorId INT,#作者编号
publishDate DATETIME#出版日期
);
DESC book;
#案例：创建表author
CREATE TABLE IF NOT EXISTS author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
)
DESC author;
#案例：使用子查询创建表
CREATE TABLE dept80
AS
SELECT employee_id, last_name,
salary*12 ANNSAL,
hire_date
FROM employees
WHERE department_id = 80;
2.表的修改
语法
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
#①修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
#②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
#③添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
存在actor表，包含如下列信息：
CREATE TABLE actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update datetime NOT NULL);
在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'2020-10-01 00:00:00'
alter table actor
add create_date datetime not null default '2020-10-01 00:00:00';
#④删除列
ALTER TABLE book_author DROP COLUMN annual;
#⑤修改表名
ALTER TABLE author RENAME TO book_author;
3.表的删除
DROP TABLE IF EXISTS book_author;
#清空表
TRUNCATE TABLE #不能回滚
4.表的复制
INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');
SELECT * FROM Author;
SELECT * FROM copy2;
#1.仅仅复制表的结构
CREATE TABLE copy LIKE author;
#2.复制表的结构+数据
CREATE TABLE copy2 SELECT * FROM author;
#只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';
#仅仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;
增删改
增加语句
1、方式一
insert into 表名(列名,...) values(值1,...);
#1.插入的值的类型要与列的类型一致或兼容
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);
#2.不可以为null的列必须插入值。可以为null的列如何插入值？
#方式一：
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);
#方式二：
INSERT INTO beauty(id,NAME,sex,phone)
VALUES(15,'娜扎','女','1388888888');
#3.列的顺序是否可以调换?
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('xx','女',16,'110');
#4.列数和值的个数必须一致
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('关晓彤','女',17,'110');
#5.可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
INSERT INTO beauty
VALUES(18,'张飞','男',NULL,'119',NULL,NULL);
2、方式二
insert into 表名
set 列名=值,列名=值,...
INSERT INTO beauty
SET id=19,NAME='刘涛',phone='999';
3、两种方式比较
#1、方式一支持插入多行,方式二不支持
INSERT INTO beauty
VALUES(23,'唐艺昕1','女','1990-4-23','1898888888',NULL,2)
,(24,'唐艺昕2','女','1990-4-23','1898888888',NULL,2)
,(25,'唐艺昕3','女','1990-4-23','1898888888',NULL,2);
#2、方式一支持子查询，方式二不支持
INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','11809866';
INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname,'1234567'
FROM boys WHERE id<3;
删除语句
1、方式一：delete
1、单表的删除
delete from 表名 where 筛选条件
#案例：删除手机号以9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9';
2、多表的删除【补充】
sql92语法：
delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;
sql99语法：
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;
#案例：删除张无忌的女朋友的信息
DELETE b
FROM beauty b
INNER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName`='张无忌';
#案例：删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM beauty b
INNER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';
#删除emp_no重复的记录，只保留最小的id对应的记录。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL
);
insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
delete from titles_test
where id not in
(select * from (select min(id) from titles_test group by emp_no) a);
2、方式二：truncate
TRUNCATE TABLE boys ; 全删除
@面：delete和truncate区别
- delete 可以加where条件，truncate不能加
- truncate删除，效率高一丢丢
- 假如要删除的表中有自增长列。如果用delete删除后，再插入数据，自增长列的值从断点开始；而truncate删除后，再插入数据，自增长列的值从1开始。
- truncate删除没有返回值，delete删除有返回值
- truncate删除不能回滚，delete删除可以回滚.
@面：truncate、delete、drop区别
drop(DDL语句):是不可逆操作，会将表所占用空间全部释放掉
truncate(DDL语句):只针对于删除表的操作，在删除过程中不会激活与表有关的删除触发器并且不会把删除记录放在日志中;当表被truncate后，这个表和索引会恢复到初始大小;
delete(DML语句):可以删除表也可以删除行，但是删除记录会被计入日志保存，而且表空间大小不会恢复到原来;
执行速度:drop>truncate>deleteo
修改语句
1.修改单表的记录
语法：
update 表名
set 列=新值,列=新值,...
where 筛选条件;
#1.修改单表的记录
#案例1：修改beauty表中姓唐的女神的电话为13899888899
UPDATE beauty
SET phone = '13899888899'
WHERE NAME LIKE '唐%';
#案例2：修改boys表中id好为2的名称为张飞，魅力值 10
UPDATE boys
SET boyname='张飞',usercp=10
WHERE id=2;
将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现，直接使用update会报错。
CREATE TABLE titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL
);
insert into titles_test values
('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
update titles_test
set emp_no = replace(emp_no,10001,10005)
where id = 5;
2.修改多表的记录
sql92语法：
update 表1 别名,表2 别名
set 列=值,...
where 连接条件
and 筛选条件;
sql99语法：
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件;
#2.修改多表的记录
#案例 1：修改张无忌的女朋友的手机号为114
UPDATE boys bo
INNER JOIN beauty b
ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='119',bo.`userCP`=1000
WHERE bo.`boyName`='张无忌';
#案例2：修改没有男朋友的女神的男朋友编号都为2号
UPDATE boys bo
RIGHT JOIN beauty b
ON bo.`id`=b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;
子查询
出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询
在查询时基于未知的值时，应使用子查询
分类
1、按子查询出现的位置：
- select后面：仅仅支持标量子查询
- from后面：支持表子查询
- where或having后面：标量子查询(单行)；列子查询(多行)；行子查询
- exists后面( 相关子查询)：表子查询
2、按结果集的行列数不同：
- 标量子查询（结果集只有一行一列）
- 列子查询（结果集只有一列多行）
- 行子查询（结果集有一行多列）
- 表子查询（结果集一般为多行多列）
where或having后面
特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用 > < >= <= = <>
列子查询，一般搭配着多行操作符使用 in、any/some、all
④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
1.标量子查询
#案例1：谁的工资比 Abel 高?
#①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel'
#②查询员工的信息，满足 salary>①结果
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
#案例2：返回公司工资最少的员工的last_name,job_id和salary
#①查询公司的 最低工资
SELECT MIN(salary)
FROM employees
#②查询last_name,job_id和salary，要求salary=①
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
#案例3：查询最低工资大于50号部门最低工资的部门id和其最低工资
#子查询中的 HAVING 子句，首先执行子查询；向主查询中的HAVING子句返回结果。
#①查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
#②查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
#③ 在②基础上筛选，满足min(salary)>①
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
#案例4：查询每门课程前两名的学生以及成绩
select *
from test1 a
where 2>(select count(*) from test1 where course=a.course and score>a.score)
order by a.course,a.score desc;
2.列子查询（多行子查询）
案例1：返回location_id是1400或1700的部门中的所有员工姓名
#①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
#②查询员工姓名，要求部门号是①列表中的某一个
SELECT last_name
FROM employees
WHERE department_id IN (
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
#①查询job_id为‘IT_PROG’部门任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
#②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
3、行子查询（结果集一行多列）
案例：查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
#①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees
#②查询最高工资
SELECT MAX(salary)
FROM employees
#③查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
)AND salary=(
SELECT MAX(salary)
FROM employees
);
select后面
仅仅支持标量子查询
案例：查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
) 个数
FROM departments d;
#案例2：查询员工号=102的部门名
SELECT (
SELECT department_name,e.department_id
FROM departments d
INNER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id=102
) 部门名;
from后面
将子查询结果充当一张表，要求必须起别名
案例：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
SELECT * FROM job_grades;
#②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
SELECT ag_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
exists后面
语法：
exists(完整的查询语句)
结果：
1或0
案例2：查询没有女朋友的男神信息
#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
SELECT boyfriend_id
FROM beauty
)
#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE bo.`id`=b.`boyfriend_id`
);
数据类型
数值型：
整型
小数：
定点数
浮点数
字符型：
较短的文本：char、varchar
较长的文本：text、blob（较长的二进制数据）
日期型：
整型
分类：
tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 8字节
特点：
① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
③ 如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！
1.如何设置无符号和有符号
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
t1 INT(7) ZEROFILL,
t2 INT(7) ZEROFILL
);
小数
分类：
1.浮点型：float(M,D)、double(M,D)
2.定点型：dec(M，D)、decimal(M,D)
特点：
①M：整数部位+小数部位 D：小数部位
如果超过范围，则插入临界值
②M和D都可以省略
如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度
③定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用
@面：区分float和double
浮点数以8位精度存储在float中，并且有四个字节。
浮点数存储在double中，精度为18位，有八个字节。
字符型
较短的文本：char、varchar
较长的文本：text、blob(较大的二进制)
其他：binary和varbinary用于保存较短的二进制；enum用于保存枚举，是一个字符串对象，用于指定一组预定义的值，并可在创建表时使用；set用于保存集合
写法 M的意思 特点 空间的耗费 效率
char char(M) 最大的字符数，可以省略，默认为1 固定长度的字符 比较耗费 高
varchar varchar(M) 最大的字符数，不可以省略 可变长度的字符 比较节省 低
CREATE TABLE tab_char(
c1 ENUM('a','b','c')
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('m');
INSERT INTO tab_char VALUES('A');
CREATE TABLE tab_set(
s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('A,B');
INSERT INTO tab_set VALUES('a,c,d');
@面：char和varchar的区别
char和varchar类型在存储和检索方面不同
char列长度固定为创建表时声明的长度，长度值范围是1到255
当char值被存储时，它们被用空格填充到特定长度，检索char值时需删除尾随空格。
@面：blob和text有什么区别
blob是一个二进制对象，可以容纳可变数量的数据。
有四种类型的BLOB：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB
它们只能在所能容纳价值的最大长度上有所不同。
text是一个不区分大小写的BLOB。
四种text类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT
它们对应于四种BLOB类型，并具有相同的最大长度和存储要求。
blob和text类型之间的唯一区别在于对blob值进行排序和比较时区分大小写，对text值不区分大小写。
@面：什么是非标准字符串类型
1、TINYTEXT
2、TEXT
3、MEDIUMTEXT
4、LONGTEXT
日期型
分类：
date只保存日期、time 只保存时间、year只保存年
datetime保存日期+时间、timestamp保存日期+时间
特点：
字节 范围 时区等的影响
datetime 8 1000——9999 不受
timestamp 4 1970-2038 受
CREATE TABLE tab_date(
t1 DATETIME,
t2 TIMESTAMP
);
INSERT INTO tab_date VALUES(NOW(),NOW());
SELECT * FROM tab_date;
SHOW VARIABLES LIKE 'time_zone';
SET time_zone='+9:00';
@面：NOW()和CURRENT DATE()区别
NOW()命令用于显示当前年份，月份，日期，小时，分钟和秒。
CURRENT DATE()仅显示当前年份，月份和日期。
常见约束
一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
#六大约束：
NOT NULL：非空，用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认，用于保证该字段有默认值
比如性别
PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
比如学号、员工编号等
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号
CHECK:检查约束【mysql中不支持】
比如年龄、性别
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束，用于引用主表中某列的值
比如学生表的专业编号，员工表的部门编号，员工表的工种编号
添加约束的时机：1.创建表时 2.修改表时
约束的添加分类：
列级约束：六大约束语法上都支持，但外键约束没有效果
表级约束：除了非空、默认，其他的都支持
主键和唯一的大对比：
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ × 至多有1个 √，但不推荐
唯一 √ √ 可以有多个 √，但不推荐
外键：
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、插入数据时，先插入主表，再插入从表；删除数据时，先删除从表，再删除主表
CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
)
CREATE DATABASE students;
@面：主键和候选键区别
表格的每一行都由主键唯一标识,一个表只有一个主键。
主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。
@面：完整性约束包括哪些
1、实体完整性：规定表的每一行在表中是惟一的实体。
2、域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。
3、参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。
4、用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。
与表有关的约束：包括列约束(NOT NULL（非空约束）)和表约束(PRIMARY KEY、foreign key、check、UNIQUE) 。
创建表时添加约束
1.添加列级约束
语法：
直接在字段名和类型后面追加 约束类型即可。
只支持：默认、非空、主键、唯一
USE students;
DROP TABLE stuinfo;
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主键
stuName VARCHAR(20) NOT NULL UNIQUE,#非空
gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
seat INT UNIQUE,#唯一
age INT DEFAULT 18,#默认约束
majorId INT REFERENCES major(id)#外键
);
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
#查看stuinfo中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;
2.添加表级约束
语法：在各个字段的最下面
【constraint 约束名】 约束类型(字段名)
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
CONSTRAINT pk PRIMARY KEY(id),#主键
CONSTRAINT uq UNIQUE(seat),#唯一键
CONSTRAINT ck CHECK(gender ='男' OR gender = '女'),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);
SHOW INDEX FROM stuinfo;
通用的写法：
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20),
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
修改表时添加约束
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT
)
DESC stuinfo;
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#4.添加唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
#5.添加外键
ALTER TABLE stuinfo
ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid)
REFERENCES major(id);
在audit表上创建外键约束，其emp_no对应employees_test表的主键id。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMP_no INT NOT NULL,
create_date datetime NOT NULL
);
alter table audit
add constaint foreign key(emp_no)
references employees_test(ID);
修改表时删除约束
#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
SHOW INDEX FROM stuinfo;
分页查询
当要显示的数据，一页显示不全，需要分页提交sql请求（相当于SQL中的top）
语法：
select 查询列表
from 表
[join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段]
limit [offset,]size; #范围不包含offset
offset要显示条目的起始索引（默认起始索引从0开始）
size 要显示的条目个数
特点：
①limit语句放在查询语句的最后
②公式
要显示的页数 page，每页的条目数size
select 查询列表
from 表
limit (page-1)*size,size;
#案例1：查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;
#案例2：查询第11条——第25条
SELECT * FROM employees LIMIT 10,15;
#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;
@面：如何显示前50行
SELECT *
FROM
LIMIT 0,50；
事务
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。
概念
1、事务的特性（ACID）
- 原子性：一个事务不可再分割，要么都执行要么都不执行
- 一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
- 隔离性：一个事务的执行不受其他事务的干扰
- 持久性：一个事务一旦提交，则会永久的改变数据库的数据.
@面：ACID的特性
原子性
是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性
指事务前后数据的完整性必须保持一致。
隔离性
指多个用户并发访问数据库时，一个用户的事务不能被其他用户的事务所干扰，多个并发事务之间数据要相互隔离。
持久性
是指一个事务一旦提交，它对数据库中数据的改变就是永久性的，即便数据库发生故障也不应该对其有任何影响。
@面：数据库中的事务是什么
事务是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。
2、事务的使用
以第一个 DML 语句的执行作为开始
以下面的其中之一作为结束:
- COMMIT 或 ROLLBACK 语句
- DDL 或 DCL 语句（自动提交）
- 用户会话正常结束
- 系统异常终了
3、隔离级别
1）对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.
幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行之后, 如果 T1 再次读取同一个表, 就会多出几行.
2）数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题
3）一个事务与其他事务隔离的程度称为隔离级别。 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱
4）4 种事务隔离级别:
Mysql 默认的事务隔离级别为: REPEATABLE READ
@面：数据库的隔离级别
5）设置隔离级别
每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别.
查看当前的隔离级别: SELECT @@tx_isolation;
设置当前 mySQL 连接的隔离级别: set transaction isolation level read committed;
设置数据库系统的全局的隔离级别: set global transaction isolation level read committed;
@面：MySQL 支持事务吗
在缺省模式下，MySQL是autocommit模式的，所有的数据库更新操作都会即时提交，所以在缺省情况下，MySQL是不支持事务的。
但是如果你的MySQL表类型是使用InnoDB Tables的话，你的MySQL就可以使用事务处理,使用 SET AUTOCOMMIT=0 就可以使 MySQL允许在非autocommit模式，在非autocommit模式下，你必须使用COMMIT来提交你的更改，或者用ROLLBACK来回滚你的更改。
@面：事务回滚机制概述
事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位，事务回滚是指将该事务已经完成的对数据库的更新操作撤销。
要同时修改数据库中两个不同表时，如果它们不是一个事务的话，当第一个表修改完，可能第二个表修改过程中出现了异常而没能修改，此时就只有第二个表依旧是未修改之前的状态，而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候，当第一个表修改完，第二表修改出现异常而没能修改，第一个表和第二个表都要回到未修改的状态，这就是所谓的事务回滚
事务的创建
1、**隐式事务：**事务没有明显的开启和结束的标记。比如insert、update、delete语句
delete from 表 where id =1;
2、**显式事务：**事务具有明显的开启和结束的标记。前提：必须先设置自动提交功能为禁用，set autocommit=0;
3、步骤：
-
步骤1：开启事务。 set autocommit=0; start transaction;可选的
-
步骤2：编写事务中的sql语句(select insert update delete) 语句1; 语句2; …
-
步骤3：结束事务 commit;提交事务 rollback;回滚事务
savepoint 节点名; 设置保存点
事务案例
案例：转账
张三丰 1000
郭襄 1000
开启事务的语句;
update 表 set 张三丰的余额=500 where name='张三丰'
update 表 set 郭襄的余额=1500 where name='郭襄'
结束事务的语句;
#1.演示事务的使用步骤
#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance = 1000 WHERE username='张无忌';
UPDATE account SET balance = 1000 WHERE username='赵敏';
#结束事务
ROLLBACK;
#commit;
SELECT * FROM account;
#2.演示事务对于delete和truncate的处理的区别
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
#3.演示savepoint 的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点
SELECT * FROM account;
@面：JDBC中如何进行事务处理
Connection提供了事务处理的方法，通过调用setAutoCommit(false)可以设置手动提交事务；当事务完成后用commit()显式提交事务；如果在事务处理过程中发生异常则通过rollback()进行事务回滚。
除此之外，从JDBC3.0中还引入了Savepoint（保存点）的概念，允许通过代码设置保存点并让事务回滚到指定的保存点。
视图
一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。
应用场景：
- 多个地方用到同样的查询结果
- 该查询结果使用的sql语句较复杂
视图和表的区别：
#案例：查询姓张的学生名和专业名
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`
WHERE s.`stuname` LIKE '张%';
CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`;
SELECT * FROM v1 WHERE stuname LIKE '张%';
@面：什么叫视图、游标
视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。
视图能够简化用户的操作
(2) 视图使用户能以多种角度看待同一数据；
(3) 视图为数据库提供了一定程度的逻辑独立性；
(4) 视图能够对机密数据提供安全保护。
创建视图
语法：
create view 视图名
as
查询语句;
#1.查询姓名中包含a字符的员工名、部门名和工种信息
#①创建
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id = e.job_id;
#②使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
#2.查询各部门的平均工资级别
#①创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;
#②使用
SELECT myv2.`ag`,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#3.查询平均工资最低的部门信息
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
#4.查询平均工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
SELECT d.*,m.ag
FROM myv3 m
JOIN departments d
ON m.`department_id`=d.`department_id`;
视图的修改
#方式一：
/*
create or replace view 视图名
as
查询语句;
*/
SELECT * FROM myv3
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
#方式二：
/*
语法：
alter view 视图名
as
查询语句;
*/
ALTER VIEW myv3
AS
SELECT * FROM employees;
删除视图
/*
语法：drop view 视图名,视图名,...;
*/
DROP VIEW emp_v1,emp_v2,myv3;
查看视图
DESC myv3;
SHOW CREATE VIEW myv3;
视图的更新
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"
FROM employees;
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;
SELECT * FROM myv1;
SELECT * FROM employees;
具备以下特点的视图不允许更新
#1 包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;
SELECT * FROM myv1;
#更新
UPDATE myv1 SET m=9000 WHERE department_id=10;
#2 常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;
SELECT * FROM myv2;
#更新
UPDATE myv2 SET NAME='lucy';
#3 Select中包含子查询
CREATE OR REPLACE VIEW myv3
AS
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;
#更新
SELECT * FROM myv3;
UPDATE myv3 SET 最高工资=100000;
#4 join
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
#更新
SELECT * FROM myv4;
UPDATE myv4 SET last_name = '张飞' WHERE last_name='Whalen';
INSERT INTO myv4 VALUES('陈真','xxxx');
#5 from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;
#更新
SELECT * FROM myv5;
UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;
#6 where子句的子查询引用了from子句中的表
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
);
#更新
SELECT * FROM myv6;
UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';
存储过程
类似于java中的方法
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
@面：什么是存储过程，用什么调用
存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。
创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体（一组合法的SQL语句）
END
#注意：
1、参数列表包含三部分
参数模式 参数名 参数类型
举例：
in stuname varchar(20)
参数模式：
in：是默认模式,该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
2、如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用delimiter重新设置
语法：
delimiter 结束标记
案例：
delimiter $
调用语法
CALL 存储过程名(实参列表);
#1.空参列表
#案例：插入到admin表中五条记录
SELECT * FROM admin;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,`password`)
VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $
#调用
CALL myp1()$
#2.创建带in模式参数的存储过程
#案例1：创建存储过程实现 根据女神名，查询对应的男神信息
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name=beautyName;
END $
#调用
CALL myp2('柳岩')$
#案例2 ：创建存储过程实现，用户是否登录成功
CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;#声明并初始化
SELECT COUNT(*) INTO result#赋值
FROM admin
WHERE admin.username = username
AND admin.password = PASSWORD;
SELECT IF(result>0,'成功','失败');#使用
END $
#调用
CALL myp3('张飞','8888')$
#3.创建out 模式参数的存储过程
#案例1：根据输入的女神名，返回对应的男神名
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyname INTO boyname
FROM boys bo
RIGHT JOIN
beauty b ON b.boyfriend_id = bo.id
WHERE b.name=beautyName ;
END $
#案例2：根据输入的女神名，返回对应的男神名和魅力值
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT)
BEGIN
SELECT boys.boyname ,boys.usercp INTO boyname,usercp
FROM boys
RIGHT JOIN beauty b ON b.boyfriend_id = boys.id
WHERE b.name=beautyName ;
END $
#调用
CALL myp7('小昭',@name,@cp)$
SELECT @name,@cp$
#4.创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回
CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END $
#调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$
删除存储过程
#语法：
drop procedure 存储过程名
DROP PROCEDURE p1;
查看存储过程的信息
SHOW CREATE PROCEDURE myp2;
函数
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
@面：存储过程和函数区别
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1个返回，适合做处理数据后返回一个结果
创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
注意：
1.参数列表 包含两部分：
参数名 参数类型
2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议return值;
3.函数体中仅有一句话，则可以省略begin end
4.使用delimiter语句设置结束标记
调用语法
SELECT 函数名(参数列表)
#1.无参有返回
#案例：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0; #定义局部变量
SELECT COUNT(*) INTO c FROM employees; #赋值
RETURN c;
END $
SELECT myf1()$
#2.有参有返回
#案例1：根据员工名，返回它的工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0; #定义用户变量
SELECT salary INTO @sal #赋值
FROM employees
WHERE last_name = empName;
RETURN @sal;
END $
SELECT myf2('k_ing') $
#案例2：根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE sal DOUBLE;
SELECT AVG(salary) INTO sal
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name=deptName;
RETURN sal;
END $
SELECT myf3('IT')$
查看函数
SHOW CREATE FUNCTION myf3;
删除函数
DROP FUNCTION myf3;
#案例：创建函数，实现传入两个float，返回二者之和
CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM=num1+num2;
RETURN SUM;
END $
SELECT test_fun1(1,2)$
流程控制结构
顺序、分支、循环
分支结构
1、if函数
语法：if(条件,值1，值2)
功能：实现双分支
应用在begin end中或外面
2、case结构
语法：
情况1：类似于switch
case 变量或表达式
when 值1 then 语句1;
when 值2 then 语句2;
...
else 语句n;
end
情况2：
case
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句n;
end
应用在begin end 中或外面
#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
CASE
WHEN score>90 THEN SET ch='A';
WHEN score>80 THEN SET ch='B';
WHEN score>60 THEN SET ch='C';
ELSE SET ch='D';
END CASE;
RETURN ch;
END $
SELECT test_case(56)$
3、if结构
语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
....
else 语句n;
end if;
功能：类似于多重if
只能应用在begin end 中
#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
IF score>90 THEN SET ch='A';
ELSEIF score>80 THEN SET ch='B';
ELSEIF score>60 THEN SET ch='C';
ELSE SET ch='D';
END IF;
RETURN ch;
END $
SELECT test_if(87)$
#案例2：创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500
CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;
ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;
ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;
END IF;
END $
CALL test_if_pro(2100)$
循环结构
分类：
while、loop、repeat
循环控制：
iterate类似于continue，继续，结束本次循环，继续下一次
leave类似于break，跳出，结束当前所在的循环
1、while
语法：
【标签:】while 循环条件 do
循环体;
end while【标签】;
#1.没有添加循环控制语句
#案例：批量插入，根据次数插入到admin表中多条记录
DROP PROCEDURE pro_while1$
CREATE PROCEDURE pro_while1(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 $
CALL pro_while1(100)$
#2.添加leave语句
#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END $
CALL test_while1(100)$
#3.添加iterate语句
#案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i<=insertCount DO
SET i=i+1;
IF MOD(i,2)!=0 THEN ITERATE a;
END IF;
INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
END WHILE a;
END $
CALL test_while1(100)$
/*
int i=0;
while(i<=insertCount){
i++;
if(i%2==0){
continue;
}
插入
}
*/
2、loop
语法：
【标签:】loop
循环体;
end loop 【标签】;
可以用来模拟简单的死循环
3、repeat
语法：
【标签：】repeat
循环体;
until 结束循环的条件
end repeat 【标签】;
锁
@面：什么是锁
答：数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。
加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。
基本锁类型：锁包括行级锁和表级锁
@面：MySQL 中有哪几种锁
1、表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
2、页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
3、行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
@面：锁的优化策略
1、读写分离
2、分段加锁
3、减少锁持有的时间
4.多个线程尽量以相同的顺序去获取资源
不能将锁的粒度过于细化，不然可能会出现线程的加锁和释放次数过多，反而效率不如一次加一把大锁。
@面：数据库乐观锁和悲观锁
悲观锁
悲观锁，就是每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。
悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。
Java synchronized就属于悲观锁的一种实现，每次线程要修改数据时都先获得锁，保证同一时刻只有一个线程能操作数据，其他线程则会被block。
乐观锁
乐观锁，就是每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
乐观锁一般来说有以下2种方式：
1）使用数据版本记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的version字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。
2）使用时间戳。乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。
索引
创建索引
针对如下表actor结构创建索引：
(注:在SQLite中,除了重命名表和在已有的表中添加列,ALTER TABLE命令不支持其他操作，mysql支持ALTER TABLE创建索引)
CREATE TABLE actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update datetime NOT NULL);
对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname
alter table actor add unique index uniq_idx_firstname(first_name);
alter table actor add index idx_lastname(last_name)
或
create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);
针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`)
);
create index idx_emp_no on salaries(emp_no);
select * from salaries force index(idx_emp_no)
where emp_no = 10005;
@面：索引，主键，唯一索引，联合索引的区别，对性能影响
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。
普通索引允许被索引的数据列包含重复的值。
如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。
主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字PRIMARY KEY来创建。
索引可以覆盖多个数据列，如像 INDEX(columnA, columnB)索引，这就是联合索引。
索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。
@面：索引
1、索引的目的是什么？
快速访问数据表中的特定信息，提高检索速度
创建唯一性索引，保证数据库表中每一行数据的唯一性。
加速表和表之间的连接
使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间
2、索引对数据库系统的负面影响是什么？
负面影响：
创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。
3、为数据表建立索引的原则有哪些？
在最频繁使用的、用以缩小查询范围的字段上建立索引。
在频繁使用的、需要排序的字段上建立索引
4、什么情况下不宜建立索引？
对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。
对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等
@面：主键、外键和索引
定义：
主键–唯一标识一条记录，不能有重复的，不允许为空
外键–表的外键是另一表的主键, 外键可以有重复的, 可以是空值
索引–该字段没有重复值，但可以有一个空值
作用：
主键–用来保证数据完整性
外键–用来和其他表建立联系用的
索引–是提高查询排序的速度
个数：
主键–主键只能有一个
外键–一个表可以有多个外键
索引–一个表可以有多个唯一索引
@面：索引的底层实现原理和优化
B+树，经过优化的 B+树
主要是在所有的叶子结点中增加了指向下一个叶子节点的指针，因此InnoDB建议为大部分表使用默认自增的主键作为主索引。
@面：设置了索引但无法使用
1、以“%”开头的 LIKE 语句，模糊匹配
2、OR 语句前后没有同时使用索引
3、数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）
触发器
构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);
create trigger audit_log
after insert on employees_test
for each row
begin
insert into audit values(new.id,new.name);
end
优化
@面：优化数据库的方法
1、选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置 NOTNULL，例如’省份’、’性别’最好适用ENUM
2、使用连接(JOIN)来代替子查询
3、适用联合(UNION)来代替手动创建的临时表
4、事务处理
5、锁定表、优化事务处理
6、适用外键，优化锁定表
7、建立索引
8、优化查询语句
@面：SQL 语句优化有哪些方法
1、Where 子句中：where 表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在 Where 子句的末尾.HAVING最后。
2、用 EXISTS 替代 IN、用 NOT EXISTS 替代 NOT IN。
3、避免在索引列上使用计算
4、避免在索引列上使用 IS NULL 和 IS NOT NULL
5、对查询进行优化，应尽量避免全表扫描，首先应考虑在where及orderby涉及的列上建立索引。
6、应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描
7、应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
引擎
MyISAM 和 InnoDB
对比：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DWN6sUGB-1648184242691)(Pic/1645753991899.png)]
关注点：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1WxLyVhl-1648184242692)(Pic/1645754016806.png)]
show engines:查看所有的数据库引擎
show variables like '%storage_engine%' 查看默认的数据库引擎
@面：MyISAM 和 InnoDB 的区别
MyISAM：
不支持事务，但是每次查询都是原子的；
支持表级锁，即每次操作是对整个表加锁；
存储表的总行数；
一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；
采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。
InnoDb：
支持 ACID 的事务，支持事务的四种隔离级别；
支持行级锁及外键约束：因此可以支持写并发；
不存储总行数；
一个 InnoDb引擎存储在一个文件空间共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里，也有可能为多个设置为独立表空，表大小受操作系统文件大小限制，一般为 2G，受操作系统文件大小的限制；
主键索引采用聚集索引，索引的数据域存储数据文件本身，辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持 B+树结构，文件的大调整。
@面：MyISAMStatic 和 MyISAMDynamic区别
在 MyISAM Static上的所有字段有固定宽度。动态MyISAM表将具有像TEXT，BLOB等字段，以适应不同长度的数据类型。
MyISAM Static在受损情况下更容易恢复。
@面：MyISAM表格在哪里存储，其存储格式
每个 MyISAM 表格以三种格式存储在磁盘上：
“.frm”文件存储表定义
数据文件具有“.MYD”（MYData）扩展名
索引文件具有“.MYI”（MYIndex）扩展名
@面：MySQL中有哪些不同的表格
1、MyISAM
2、Heap
3、Merge
4、INNODB
5、ISAM
其他
@面：mysql的主从复制
主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。
MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。
MySQL主从复制的两种情况：同步复制和异步复制。
复制的基本过程如下：
1）Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置之后的日志内容。
2）Master接收到来自Slave的IO进程的请求后，负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置。
3）Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”。
4）Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。
@面：三个范式
第一范式（1NF）
强调的是列的原子性，即列不能够再分成其他几列。
第二范式（2NF）
首先是1NF；表必须有一个主键；没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
第三范式（3NF）
满足第三范式必须满足第二范式。
首先是2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖(即不能存在非主键列A依赖于非主键列B，非主键列B依赖于主键的情况)。
范式化设计优缺点
优点:可以尽量得减少数据冗余，使得更新快，体积小
缺点:对于查询需要多个表进行关联，减少写得效率，增加读得效率，更难进行索引优化
@面：数据库水平切分与垂直切分
水平拆分行，行数据拆分到不同表中，水平切分就是要把一个表按照某种规则把数据划分到不同表或数据库里。
垂直拆分列，列数据拆分到不同表中，垂直拆分就是要把表按模块划分到不同数据库表中。
垂直拆分：单表大数据量依然存在性能瓶颈
@面：Statement 和 PreparedStatement 的区别
与Statement相比
1）PreparedStatement接口代表预编译的语句，它主要的优势在于可以减少SQL的编译错误并增加SQL的安全性；
2）PreparedStatement中的SQL语句是可以带参数的，避免了用字符串连接拼接SQL语句的麻烦和不安全；
3）当批量处理SQL或频繁执行相同的查询时，PreparedStatement有明显的性能上的优势，由于数据库可以将编译优化后的SQL语句缓存起来，下次执行相同结构的语句时就会很快。
如果一个表有一列定义为TIMESTAMP，将发生什么？
每当行被更改时，时间戳字段将获取当前时间戳。
列设置为AUTO INCREMENT时，如果在表中达到最大值，会发生什么情况？
它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用。
怎样才能找出最后一次插入时分配了哪个自动增量？
LAST_INSERT_ID 将返回由 Auto_increment 分配的最后一个值，并且不需要指定表名称。
京公网安备 11010502036488号