目录
一、蓝云飘飘的mysql文章地址汇总
以下均为蓝云飘飘的文章：
MySQL数据库（一）_写出sql语句,列出薪资比‘王海涛’的薪资高的所有员工,显示姓名,薪资-CSDN博客
MySQL数据库（二）_sql里的性别是什么代表-CSDN博客 ★★★★★
图灵第4期MySQL调优专题学习笔记_图灵学院索引优化资料下载-CSDN博客 ★★★★★
MySQL日期类型及默认值_mysql默认日期8位-CSDN博客
MySQL更改表结构语句_mysql 修改表结构语句-CSDN博客
-------------------------------------------------------------------------------------------------------------------------
二、官网学习
最好的学习资料在官网！！！
- EXISTS——略。
- INSERT——两种语法都行，其中一种是insert into table select......;
- EXCEPT——含义与minus相同，但不支持MySQL和Oracle，故忽略；
- MINUS——检索表1中的所有记录，然后从结果中删除与第2张表相同的所有记录，最后返回结果（只能Oracle）；
- INTERSECT——取两张表的交集；
- TRUNCATE TABLE——用于从表中删除所有记录【特点是删除速度快，但无法回滚表记录】。它执行与不带where子句的delete语句相同的功能。
- UPDATE——看看不同的更新语法；
- ALTER TABLE——添加列、修改列、删除列、重命名列或重命名表，一列或多列都有示例，Oracle与MySQL的不同均有说明；
- CREATE TABLE——创建定义表（constraint关键字创建主键，可省略；每列必须具有数据类型，该列应定义为 NULL 或 NOT NULL，如果此值留空，则数据库假定 NULL 为默认值）；
- CREATE TABLE AS——通过复制现有表的列，从现有表创建表；、
- INDEXES——如何创建/删除索引；
- PRIMARY KEY——如何创建/删除主键（一张表只能有一个主键，主键可以添加在一个或多个字段上）；
- VIEWS——视图，虚拟表。
2.1 MySQL官方文档★★★
网址：MySQL Tutorial (techonthenet.com)
MySQL创建表的完整语法（来源于官网）：
CREATE [ TEMPORARY ] TABLE [IF NOT EXISTS] table_name
(
column1 datatype [ NULL | NOT NULL ]
[ DEFAULT default_value ]
[ AUTO_INCREMENT ]
[ UNIQUE KEY | PRIMARY KEY ]
[ COMMENT 'string' ],
column2 datatype [ NULL | NOT NULL ]
[ DEFAULT default_value ]
[ AUTO_INCREMENT ]
[ UNIQUE KEY | PRIMARY KEY ]
[ COMMENT 'string' ],
...
| [CONSTRAINT [constraint_name]] PRIMARY KEY [ USING BTREE | HASH ] (index_col_name, ...)
| [INDEX | KEY] index_name [ USING BTREE | HASH ] (index_col_name, ...)
| [CONSTRAINT [constraint_name]] UNIQUE [ INDEX | KEY ]
[ index_name ] [ USING BTREE | HASH ] (index_col_name, ...)
| {FULLTEXT | SPATIAL} [ INDEX | KEY] index_name (index_col_name, ...)
| [CONSTRAINT [constraint_name]]
FOREIGN KEY index_name (index_col_name, ...)
REFERENCES another_table_name (index_col_name, ...)
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE { RESTRICT | CASCADE | SET NULL | NO ACTION } ]
[ ON UPDATE { RESTRICT | CASCADE | SET NULL | NO ACTION } ]
| CHECK (expression)
{ENGINE | TYPE} = engine_name
| AUTO_INCREMENT = value
| AVG_ROW_LENGTH = value
| [DEFAULT] CHARACTER SET = charset_name
| CHECKSUM = {0 | 1}
| [DEFAULT] COLLATE = collation_name
| COMMENT = 'string'
| DATA DIRECTORY = 'absolute path'
| DELAY_KEY_WRITE = { 0 | 1 }
| INDEX DIRECTORY = 'absolute path'
| INSERT_METHOD = { NO | FIRST | LAST }
| MAX_ROWS = value
| MIN_ROWS = value
| PACK_KEYS = {0 | 1 | DEFAULT}
| PASSWORD = 'string'
| RAID_TYPE = { 1 | STRIPED | RAIDO }
RAID_CHUNKS = value
RAID_CHUNKSIZE = value
| ROW_FORMAT = {DEFAULT | DYNAMIC | FIXED | COMPRESSED}
| UNION = (table1, ... )
);其实，下面这个网址才是MySQL官网文档，只是看起来有点费劲。
MySQL官网文档：MySQL :: MySQL 8.0 Reference Manual :: 15 SQL Statements
2.2 Oracle官方文档
网址同上，左侧栏切换一下即可。
三、部分面试题
题目一：B+树和B树的区别？ 为什么MYSQL要用B+ 树而不用B树？
- 首先B树的所有节点都存储数据信息，而B+ 树的所有数据都存储在叶子节点
- B+ 树是在B树的基础上的一种优化，使其更加适合外存储索引结构，InnoDB存储引擎及时B+ 树实现其索引结构
- 从B树结构图中可以看到每个节点中不仅包含数据的Key值，还有data值，而每一页的存储空间是有限的，如果data数据较大时会导致每一个节点（也就是每一页）能存储的key的数量很小，当存储的数据量很大时同时会导致B树的深度很深，高度很高，增大磁盘的IO次数，进而影响查询效率，在B+树中，所有数据节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储Key值信息，这样可以大大增加每个节点存储的key值数量，降低B+树的高度
题目二：mysql如何创建索引？
题目三：SQL语句的执行顺序？
题目四：SQL的优化方法？
- 见上面第二个链接的文章中。
- 8种专坑同事的 SQL 写法，性能降低100倍，不来看看？ (qq.com) ★★★★★
- SQL查找是否"存在"，别再count了！ (qq.com)
题目五：使用explain分析SQL执行计划
1、type类型
- system：表仅有一行，基本用不到；
- const：表最多一行数据配合，主键查询时触发较多；
- eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型；
- ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取；
- range：只检索给定范围的行，使用一个索引来选择行。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range；
- index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小；
- all：全表扫描；
- 性能排名：system > const > eq_ref > ref > range > index > all。
实际sql优化中，最后达到ref或range级别。
2、Extra常用关键字
- Using index：只从索引树中获取信息，而不需要回表查询；
- Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。需要回表查询。
- Using temporary：mysql常建一个临时表来容纳结果，典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时；
-------------------------------------------------------------------------------------------------------------------------
四、张慎政老师打印的讲义
1. 完整连接mysql服务器的命令：
mysql -uroot -p -h 192.168.6.105 -P 3306
2. 常用sql语句
show create database 库名; -- 查看建库语句
show create table 表名; -- 查看建表语句
drop database if exists cscardata; -- 如果存在的话删除cscardata数据库（不报错）
drop table if exists CCMS_CALL_LOG; # 如果存在的话删除CCMS_CALL_LOG表（不报错）
desc 表名; -- 查看表结构
delete from 表名 where 条件；-- 删除表记录
3. 一些日常开发规范
mysql推荐使用单引号，有些版本中双引号会报错。
insert into CCMS_CALL_LOG value/values的区别；
null与任何值都不相等，包括它自己，所以不用等于号，而是用is；
null与任何数据运算结果都是null，故将null的处理使用函数：ifnull(列名,值)；
4. CRUD中的重点是查询
1）多行(聚合)函数查询：默认过滤null
- count(*)：统计结果中有多少行记录，统计null值；
- count(列名)：统计结果中有多少行记录，不统计null值；
- max(列名)
- min(列名)
- sum(列名)：求和时，如果有null值会自动剔除；
- avg(列名)：这一列所有值的和除以不是null值的个数。
2）分组查询
分了组使用聚合函数和不分组使用聚合函数的区别是什么？
- 没分组使用聚合函数时，不要显示额外的列，因为没有任何意义，如下所示。下面的sql语句只会将第一行的dept显示出来，和前面统计的人数没有任何关系：
错误示例：select count(*),dept from emp;
- 分了组使用多行函数时，可以将进行分组的列和聚合函数一起显示，如下所示：
正确：select count(*),dept from emp group by dept;
3）排序查询
默认是顺序asc，降序是desc；
order by bonus desc,sal desc; -- 按照奖金bonus降序排，如果奖金相同就按照薪资降序排；
4）分页查询
查询公式：limit(页码-1)*每页显示记录数，每页显示记录数
limit后面第一个参数+1：表示从第几个数据开始查询
-- limit a,b; 其中b表示每页显示条数，查询第a/b+1页
select * from trade_goods limit 0,3; -- 每页显示3条，查询第1页
select * from trade_goods limit 3,3; -- 每页显示3条，查询第2页
select * from trade_goods limit 6,3; -- 每页显示3条，查询第3页
select * from trade_goods limit 9,3; -- 每页显示3条，查询第4页5）其他函数
获取当前年月日时分秒的各种函数：
sysdate()/now() -- 年月日时分秒
curdate() -- 年月日
curtime() -- 时分秒拼接字符串：
-- 拼接字符串
select concat(s1,s2,...sn) from trade_goods;
-- 拼接字符串，并用逗号‘,’分隔
select concat_ws(',',s1,s2,...sn) from trade_goods;
5. 补充内容
5.1 mysql的数据类型
1）数值类型：常用的有哪几个？
tinyint ：大小1byte，相对于java中的byte，用于小整数值；
smallint ：大小2bytes，相对于java中的short，用于大整数值；
int ：大小4bytes，相对于java中的int，用于大整数值；
bigint ：大小8bytes，相对于java中的long，用于极大整数值；
float ：大小4bytes，相对于java中的float，用于单精度浮点数值；
double ：大小8bytes，相对于java中的double，用于双精度浮点数值；
decimal ：存储精确的小数值；
- 一句话总结，存储小数的有float、double、decimal，存储整数的有4个。
- amount decimal(6,2) ：表示amount列最多可以存6位数字，小数位数为2位，所以amount列的范围是-9999.99~9999.99。
2）字符串类型：
- char 定长字符串，char(n)，n的范围是0-255个字符，存不满时会用空格补齐从而会浪费空间；
- varchar 变长字符串，varchar(n)，n的范围是0-65535个字节，不浪费空间。
说明：latin1编码中，1个字符对应1个字节；gbk编码中，1个字符对应2个字节；utf8编码中，1个字符对应3个字节。
面试题：varchar与char有什么区别？
除了char和varchar以外，还有
- mediumtext ：存储大文本，约16M；
- mediumblob ：存储二进制，约16M；
- longtext : 存储极大文本，约4G；
- longblob : 存储极大二进制，约4G；
3）日期类型：
date ：日期类型，格式是年月日；
time ：事件类型，格式是时分秒；
datetime ：日期+时间，格式是年月日时分秒；
timestamp ：时间戳，格式和datetime相同，也是年月日时分秒；和datetime不同的是：
- 范围上：datetime范围是1000~9999(年份)，timestamp范围是1970~2038年；
- 实际存的数据：datetime实际存的就是一个‘年月日时分秒’格式的日期+时间，而timestamp实际存储的是从1970.1.1日到这个日期+时间的时间毫秒值；
- 在使用上：timestamp可以设置自动获取当前时间作为值插入到表中，而datetime不可以。
5.2 mysql的字段约束
主键约束：非空且不能重复
创建表时指定ID字段为主键约束，如
id int primary key，
id int primary key auto_increment，（id是数值类型时可设置主键自增）
primary key(stu_id,tea_id) -- 设置联合主键，防止数据重复（见张慎政老师讲义45页）非空约束：不能为空(null)，但可以重复
如创建表时指定gender字段为非空约束
gender varchar(10) not null,唯一约束：不能重复，但可以为null
添加唯一约束的格式
email varchar(50) unique, -- 唯一即可，可以为null
email varchar(50) unique not null, -- 既不能重复，也不能为空5.3 mysql的外键约束
外键约束是用于表示两张表的对应关系。如，现有部门表（dept）和员工表（emp），如何保存部门和员工的对应关系呢？我们可以在emp表中添加一列dept_id用于保存员工所在的部门。
- 如果将dept_id设置为外键，则在emp表中增加或删除员工时，dept_id列的值受到dept表中值的制约；
- 如果没有将dept_id设置为外键时，操作emp表则不会受到制约。
5.4 表关系、多表查询&子查询
- 1对多（多对1）：在这种关系中，通常会在多的一方添加列，保存一的一方的主键（可以设置外键，也可以不设置外键，看需求）；
- 1对1：在这种关系中，可以在任何一方保存另一方的主键（可以设置外键，也可以不设置外键，看需求）；
- 多对多：在这种关系中，在任何一方保存另一方的主键都不合适，此时可以再创建一张表，在该表中添加两列分别保存那两张表的主键。
inner join、left join、right join，小表驱动大表，连接条件数量至少是表数量减去1，否则都是笛卡尔积查询；
union 将两个查询结果上下合并在一起显示，且去除重复记录；
union all 不去除重复记录；
子查询：将一条sql的查询结果作为另一个sql的查询条件；
自查询：同一张表通过起别名的方式当两张表使用（如员工表中既有员工又有上级领导，where e1.topid=e2.id; 其中e1表示员工表，e2表示上级表）。
面试题：where和having的区别？
①where用于分组前对数据的过滤，having用于分组后对数据的过滤；
②where子句中可以使用表别名，但不能使用聚合函数和列别名；而having子句中聚合函数、列别名、表别名却都可以使用。
5.5 数据库的备份与恢复
1. 备份单个数据库
语法：mysqldump -uroot -p 库名 > d:/1.sql
注意事项：在未登录、未连接到mysql服务器的cmd窗口中执行mysqldump命令哦！
2. 恢复数据库
使用source命令在已连接mysql的cmd窗口中执行。
6. 其他小点
Windows系统中mysql安装好时，记得配置环境变量Path
可以在cmd窗口中使用 echo %path% 命令检查Path环境变量的值。
mysql中不支持横杠（-），所以utf-8要写成utf8。
新增列：ALTER TABLE tablename ADD col_name datatype;
修改列：ALTER TABLE tablename MODIFY col_name datatype;
删除列：ALTER TABLE tablename DROP col_name datatype;
-- 将student表的id设置为主键自增
ALTER TABLE student MODIFY id int primary key auto_increment;
-- 想要删除主键自增时，先删除自增，再删除主键约束(如student表的id字段)
ALTER TABLE student MODIFY id int;
ALTER TABLE student DROP primary key;修改字段名用change(见下面)：ALTER TABLE emp CHANGE name emp_name varchar(100);
添加外键约束方式一：建表时添加外键
-- 创建部门表
create table dept
(
id int primary key auto_increment, /*部门编号*/
name varchar(20) /*部门名称*/
) charset utf8;
-- 创建员工表（要求：在员工表中添加外键关联部门主键）
create table emp
(
id int primary key auto_increment,
name varchar(30),
dept_id int, -- 部门编号
foreign key(dept_id) references dept(id) -- 指定dept_id为外键
) charset utf8;添加外键约束方式二： 建表后添加外键
-- 创建部门表
create table dept
(
id int primary key auto_increment, /*部门编号*/
name varchar(20) /*部门名称*/
) charset utf8;
-- 创建员工表
create table emp
(
id int primary key auto_increment,
name varchar(30),
dept_id int -- 部门编号,先不添加外键约束
) charset utf8;
-- 建表后，指定dept_id为外键
ALTER TABLE emp ADD CONSTRAINT fk_dept_id FOREIGN KEY(dept_id) REFERENCES dept(id);
desc emp;其中，fk_dept_id为外键约束的名字，由自己指定，也可以将【CONSTRAINT fk_dept_id】省略，mysql会自动分配一个外键名称。将来可以通过外键名称删除外键。通过查看建表语句（show create table emp;）可以查询到有没有外键以及外键名。
删除emp表的名字为fk_dept_id的外键：
ALTER TABLE emp DROP FOREIGN KEY fk_dept_id;7. JDBC概述
JDBC（Java DateBase Connectivity）Java数据库连接，就是利用Java语言访问数据库的一门技术。JDBC规范是由sun公司提供的一套规范，本质是一堆接口，需要不同的数据库厂商提供的驱动去实现接口。
JDBC主要有两个包组成（分别是java.sql和javax.sql），并且Java中已经包含这两个包了，但除此之外，我们只需要在Java程序中导入需要操作数据库对应的驱动包(jar包)就可以操作数据库了。
JDBC操作数据库的6个步骤：
1.注册数据库驱动；
2.获取数据库连接；
3.获取传输器；
4.发送sql到服务器执行并返回执行结果；
5.处理结果；
6.释放资源。
问题一：JDBC连接数据库的优缺点？
优点：底层方法，较第三方框架连接访问数据库更快速。
缺点：有大量重复代码；JDBC自身没有连接池（MyBatis自带连接池）；JDBC执行select语句的结果需要开发人员手动处理。
8. 事务（Transaction）
事务的四大特性是什么？
在默认情况下，mysql每执行一条sql语句，都是一个单独的事务。如果需要再一个事务中包含多条sql语句，那么需要手动显式开启/结束事务。
开启事务：begin/start transaction;
提交事务：commit;
回滚事务：rollback;
9. maven介绍
maven是一款开源的项目管理工具，是一款绿色软件，解压后就可以使用。
新下载的maven需要在conf/setting.xml文件中配置本地仓库（<localRepository>标签）、远程仓库（<mirror>标签）、jdk版本。
打包方式选择：
- jar→表示java项目，不能包含html、css、jsp、servlet等。
- war→表示web项目（可以包含Java项目）
maven项目的部分目录介绍：
- src/main/webapp : 相当于web应用的目录，将来这个目录中可以存放web资源文件（html、css、js、jsp、图片等）；
- src/main/webapp/WEB-INF/web.xml : 它是web应用的核心配置文件，其中可以做很多配置，如配置Servlet，配置session，配置监听器，配置过滤器。
引入到maven项目中的依赖（jar包）存放在什么位置？
存放在本地仓库中。具体位置为：【本地仓库位置+当前jar的坐标】
maven三种仓库之间的联系？
- 本地仓库：就是本地磁盘上的一个目录，存放从远程仓库或中央仓库下载的jar包；
- 远程仓库：通常由团队或公司搭建；
- 中央仓库：由maven团队维护。
10. MyBatis介绍
MyBatis是apache下的一个持久层开源框架，对JDBC访问数据库进行了简化和封装。
MyBatis的优势：
- JDBC连接数据库有大量重复的代码，MyBatis可以极大的简化代码；
- MyBatis自带连接池，JDBC没有；
- JDBC中是将sql语句和连接参数写死在程序中，MyBatis则写在配置文件中；
- JDBC执行查询后得到的结果集ResultSet需程序员手动处理，MyBatis不需要。
Java程序中操作mysql数据库时，需要导入mysql数据库依赖、MyBatis框架的依赖：
<!-- mybatis依赖 -->
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<!-- mysql数据库依赖 -->
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
在maven项目中，可以创建mybatis-config.xml配置文件，配置事务管理方式、连接数据库的基本信息、是否是否连接池、mapper文件位置等（详见张老师讲义）★★★
若如下配置时，resource属性会直接到类目录（classes）下去找指定位置的文件：
<mappers>
<mapper resource="EmpMapper.xml"/>
</mappers>在EmpMapper.xml中有：
- 头信息（统一的）；
- mapper标签：该标签中有属性namespace；该标签内部包含<select id="selectById">、<update>、<delete>、<insert>子标签，mybatis通过namespace+id这两个属性定位到唯一的sql来执行。
- <select>子标签中，resultType属性用于指定查询的结果要存放在哪个类型的对象中；
启用log4j日志框架及解读：略。
mybatis中有占位符有两种：
- #{} : 如果sql中只有一个#{}占位符，参数可以不用封装，直接传递即可；
- ${} : 即使sql中只有一个${}占位符，参数也必须先封装到Map或者pojo对象中再传递；
- ${}拼接的是sql片段，会引发sql注入攻击；
- mybatis底层在执行sql时，使用的就是PrepareStatement对象来传输sql语句。
mysql的动态标签：
- if标签
- where标签：动态生成where关键字，且自动去掉多余的连接词；
- foreach标签：item属性指定占位符的名称；如果传过来的只有一个数组（或者list集合）时，collection属性指定的值为array（或list）；如果传过来的是一个map集合，将数组或集合作为value封装到map中，collection属性的值就是数组或集合在map中的key。
mybatis的Mapper接口开发 & mybatis的注解开发：略。
11. tomcat，maven，servlet补充内容
略。
717
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
