1 MySQL基础
1.1 常用命令
- 启动服务：net start 服务名
- 停止服务：net stop 服务名
- 启动MySQL服务器：MySQL 【-h主机名 -P端口号 】-u用户名 -p密码
- 查看MySQL版本：MySQL --version 或 MySQL –V 或 登录到MySQL服务端使用select version();
1.2 语法规范
- 每条命令最好用分号结尾
- 每条命令根据需要，可以进行缩进或换行，同时也是为了更好的可读性
- 单行注释：# 注释文字 或者 -- 注释文字
- 多行注释：/* 注释文字 */
1.3 SQL语言分类
- DQL（Data Query Language）：数据查询语言，如select
- DML（Data Manipulate Language）：数据操作语言，如insert 、update、delete
- DDL（Data Define Languge）：数据定义语言，如create、drop、alter
- TCL（Transaction Control Language）：事务控制语言，如commit、rollback
2 常见基础问题
2.1 什么是数据库三大范式
- 第一范式：确保字段的原子性，即字段不可分。例如有一个学生表，主键是学号，还有学生姓名和联系方式两个字段，由于联系方式既可以是办公电话号码也可以是移动电话号码，存在二义性。因此，根据第一范式的要求，需要将联系方式拆分为办公电话和移动电话。（学生表（学号、学生姓名、联系方式）--> 学生表（学号，学生姓名，办公电话，移动电话））
- 第二范式：确保字段和主键完全依赖，而不是部分依赖。例如有一个成绩表，主键是学号和课程编号的联合主键，还有成绩和学分两个字段。其中成绩是完全依赖于联合主键，但学分仅依赖于课程编号，不满足第二范式。因此，需要需要将成绩表拆分为成绩表和课程表。（成绩表（学号，课程编号，成绩，学分）--> 成绩表（学号，课程编号，成绩）和课程表（课程编号，学分））
- 第三范式：确保字段和主键直接依赖，而不是间接依赖。例如有一个学生表，主键是学号，还有学生姓名，学院编号（学院编号表示学生所属学院的编号）和学院地点三个字段。其中学生姓名和学院编号是直接依赖于主键的，但是学院地点是直接依赖于学院编号而间接依赖于主键的。因此，这个表结构不满足第三范式。（学生表（学号，学生姓名，学院编号，学院地点）--> 学生表（学号，姓名，学院编号）和学院表（学院编号，学院地点））
反范式的优点：减少了数据库查询时表之间的连接次数，提高了查询速度。在实际的项目中，数据库通常不会完全遵循三大范式，可以牺牲部分存储空间来换取更快的执行速度！ 反范式的缺点：数据存在重复，导致增加空间存储开销。此外，为了保持数据的一致性，必须维护冗余数据。
2.2 MySQL大小写区分吗
在MySQL中，一个数据库会对应一个文件夹，数据库里的表会以文件的方式存放在文件夹内，所以，操作系统对大小写的敏感性决定了数据库和表的大小写敏感性。因此，MySQL在Windows下是不区分大小写的。而在Linux中是部分区分大小写的：
- 数据库名、表名和表的别名是严格区分大小写的
- 变量名是严格区分大小写的
- 列名与列的别名是忽略大小写的
- 关键字、函数名是忽略大小写的
可以通过如下命令查询当前数据库对于大小写的敏感性：
SHOW VARIABLES LIKE '%lower_case_table_names%';
- 默认为0，大小写敏感 。
- 若设置为1，表示大小写不敏感。用户创建的数据库和数据表都是以小写形式存放在磁盘上，对于查找语句都是转换为小写对表和数据库进行查找。
- 若设置为2，不对用户创建的数据库和数据表进行小写转化，但是查找语句仍是转换为小写进行查找。
值得注意的是，在MySQL 8.0之前，用户可以通过修改my.cnf的[mysqld] 中加入lower_case_table_names=1 ，然后重启服务器。但是，MySQL 8.0禁止修改该属性！
2.3 一条DQL语句的执行顺序
SELECT DISTINCT 查询列表
FORM 表
【JOIN 表2】
ON 连接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组后的筛选
ORDER BY 排序的字段
LIMIT【offset,】size;
- FORM: 对FROM的所有表计算笛卡尔积。
- JOIN：如果指定了left join或者right join等连接操作，进行表的连接
- ON：指定表连接的条件
- WHERE：对表的连接结果进行WHERE条件过滤
- GROUP BY: 根据group by子句中的列，进行分组操作
- HAVING：应用having过滤，只有符合的记录才会被保留
- SELECT：执行select操作，选择指定的列
- DISTINCT：对记录进行去重
- ORDER BY: 对记录进行排序
- LIMIT：取出指定行的记录，并将结果返回
2.4 Drop vs delete vs truncate
| drop | delete | truncate | |
|---|---|---|---|
| 速度 | 最快 | 逐行删除，慢 | 较快 |
| 类型 | DDL | DML | DDL |
| 回滚 | 不可回滚 | 可回滚 | 不可回滚 |
| 删除内容 | 删除整个表，表结构消失 | 删除所有或部分数据，表结构还在 | 删除整个表，表结构还在 |
3 MySQL数据类型
3.1 数据类型
MySQL的数据类型可以分为三大类：
- 数值型：整型、小数：定点数/浮点数
- 字符型：较短的文本包括char、varchar，较长的文本包括text、blob（较长的二进制数据）
- 日期型
3.1.1 整型
| 类型 | tinyint | Smallint | Mediumint | Int | bigint |
|---|---|---|---|---|---|
| 占用空间 | 1字节 | 2字节 | 3字节 | 4字节 | 8字节 |
- 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字。
- 如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值。
- 如果不设置长度，会有默认的长度。长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！
3.1.2 小数
| 关键字 | 类型 | 格式 |
|---|---|---|
| float | 浮点型 | float(M,D) |
| double | 浮点型 | double(M,D) |
| dec | 定点型 | dec(M,D) |
| decimal | 定点型 | decimal(M,D) |
- M：整数部位+小数部位，D：小数部位。如果超过范围，则插入临界值
- M和D都可以省略，如果是decimal，则M默认为10，D默认为0。如果是float和double，则会根据插入的数值的精度来决定精度
- 定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用
3.1.3 字符型
- 较短的文本：char/varchar
- 较长的文本：text/blob(较大的二进制)
- binary和varbinary用于保存较短的二进制
- enum用于保存枚举
- set用于保存集合
| 写法 | M的意思 | 特点 | 空间的耗费 | 效率 |
|---|---|---|---|---|
| char(M) | 最大的字符数，可以省略，默认为1 | 固定长度的字符 | 比较耗费 | 高 |
| varchar(M) | 最大的字符数，不可以省略 | 可变长度的字符 | 比较节省 | 低 |
3.1.4 日期型
- date只保存日期
- time 只保存时间
- year只保存年
- datetime保存日期+时间
- timestamp保存日期+时间
3.2 varchar、char、int
varchar和char均用来存储字符数据，其中varchar用来存储变长字符串，char用来存储长度固定的字符串。
- 存储上限不同。对于char来说，最多能存放255个字符。对于varchar，最多能存放65535个字符。
- 存储速度不同。char长度固定，存储速度会比varchar快一些，但在空间上会占用额外的空间，属于一种空间换时间的策略。
varchar和int均可以指定一个数字，但其含义不同：
- varchar(10)中的10代表字符串最长宽度为10，字符串长度超过10将报错无法进行存储。
- int(10)表示的是存储数字宽度，如果宽度不够10就会按照0进行填充。