MySQL学习笔记

DAY 1

1. MySQL 的登录

1.1 服务的启动与停止

方式一：使用图形界面工具

方式二：使用命令行工具（需要以管理员身份运行）

启动 MySQL 服务命令

net start MySQL服务名

 停止 MySQL 服务命令

net stop MySQL服务名

1.2 自带客户端的登录与退出

1.2.1 登录方式

方式一： MySQL 自带客户端

方式二： Windows 命令行

在命令行输入语句

mysql -uroot -p

之后输入密码

1.2.2 退出方式

输入 quit 或 exit

1.3 登录不同版本的 MySQL

方式一：使用 MySQL 版本号 Command Line Client

方式二：在 Windows 命令行中使用命令

mysql -uroot -P端口号 -p

其中每个版本的 MySQL 对应一个不同的端口号

DAY 2

1. MySQL 基本指令

显示数据库

show datadases;

创建数据库

create database 数据库名;

选择数据库

ues 数据库名：

查看数据库中存在的表

show tables;    #要求前面有use语句

创建表

create table 表名(字段名1 数据类型，字段名2 数据类型...);    #要求前面有use语句

查看某表中的数据

select * from 表名;

查看数据库的创建信息

show create database 数据库名;

查看表的创建信息

show create table 表名;

向表中插入数据

insert into 表名 values(字段名1，字段名2...)

        若插入字符串、日期时间类型的变量，则需使用 ' ' 表示

2. SQL 分类

SQL 语言在功能上主要分为 3 大类：

2.1 DDL(数据定义语言)

        这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。 主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。

2.2 DML(数据操作语言)

        用于添加、删除、更新和查询数据库记录，并检查数据完整性。 主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。

2.3 DCL(数据控制语言)

        用于定义数据库、表、字段、用户的访问权限和安全级别。 主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。

3. SQL 语言的规则与规范

3.1 基本规则

① SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进

② 每条命令以 ; 或 \g 或 \G 结束

③ 关键字不能被缩写也不能分行

④ 关于标点符号：(1) 必须保证所有的()、单引号、双引号是成对结束的

                              (2) 必须使用英文状态下的半角输入方式

                              (3) 字符串型和日期时间类型的数据可以使用单引号（' '）表示

                              (4) 列的别名，尽量使用双引号（" "），而且不建议省略as

3.2 大小写规范(推荐)

数据库名、表名、表别名、字段名、字段别名等都小写；

SQL 关键字、函数名、绑定变量等都大写。

3.3 注释

单行注释：#注释文字(MySQL特有的方式)

单行注释：-- 注释文字(--后面必须包含一个空格)

多行注释：/* 注释文字 */

DAY 3

3.1 Navicat 的使用

3.2 数据的导入

        通过在命令行输入语句从而导入数据库

source sql文件路径;

3.3 SELECT ... FROM 表名

        含义：展示某表中的某些信息，例如

SELECT * FROM 表名;

 其中 * 代表表中的所有字段（列）；

        若想选定特定的某些列，可使用如下语句

SELECT 字段1,字段2,...,字段n FROM 表名;

 3.4 列的别名（为结果集中的字段起别名）

        注意：列的别名只能在 ORDER BY 中使用，不能在 WHERE 中使用

方式一：字段名后加空格与别名

SELECT 字段1 别名1, 字段2 别名2,... FROM 表名;

方式二：使用关键字 AS

SELECT 字段1 AS 别名1, 字段2 AS 别名2,... FROM 表名;

方式三：列的别名可以使用一对 " " 引起来（不要使用单引号）

SELECT 字段1 "别名1", 字段2 "别名2",... FROM 表名;

3.5 去除值重复的行

SELECT DISTINCT 字段名 FROM 表名;

        在结果集中，一个值仅出现一次

3.6 空值 null

        ① 空值不等同于 0，' '，'null'

        ② 空值参与运算，且结果也为 null

 3.7 着重号 ` `

        若表名、字段名与关键字、保留字重名冲突，则需要使用着重号 ` ` 将表名、字段名括起

错误示范：

改正：

 3.8  显示表结构

DESCRIBE 表名;

  或

DESC 表名;

        显示某表中字段的详细信息

 3.9 过滤数据（WHERE）

筛选满足特定条件的数据：

SELECT ... FROM 表名 WHERE (筛选条件);

        注意：列的别名不能在筛选中使用

DAY 4

4.1 算数运算符

4.1.1 加法

        ① 在SQL中，+ 没有连接的作用，仅表示加法运算，此时会将字符串转换为数值(隐式转换)

        ② NULL 参与运算，且运算结果为 NULL

4.1.2 除法（/ 或 DIV）

        ① 在 SQL 中的除法运算，不论是否能整除，结果都为浮点型

        ② 若除法分母为0，结果为 NULL

4.1.3 取模（% 或 mod）

        ① 结果的符号与被模数的符号相同，如：12 % -5 结果为 2；-12 % 5 结果为 -2

4.2 比较运算符

        比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。

        比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。

4.2.1 等于运算符（=）

        等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回 0。在使用等号运算符时，遵循如下规则：

        ① 如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。

        ② 如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。

        ③ 如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较，如果转换数值不成功，则看作0（字母等）

        ④ 如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。       

4.2.2 安全等于（<=>）

         与等于运算符的区别：NULL参与运算时，不再返回NULL，而是返回0或1，NULL与NULL运算时，返回结果为1。

 4.2.3 不等号（<> 或 !=）

        不等于运算符用于判断两边的数字、字符串或者表达式的值是否不相等， 如果不相等则返回1，相等则返回0。不等于运算符不能判断NULL值。

        如果两边的值有任意一个为NULL， 或两边都为NULL，则结果为NULL。

4.2.4 空运算符与非空运算符（IS NULL 或 ISNULL，IS NOT NULL ）

        非空运算符（IS NOT NULL）判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0。

4.2.5 最小运算符（LEAST）、最大运算符（GREATEST）

         LEAST 语法格式为：

LEAST(值1，值2，...，值n)

其中，“值n” 表示参数列表中有n个值。在有两个或多个参数的情况下，返回最小值。注意：

        ① 当参数是整数或者浮点数时，LEAST 将返回其中最小的值

        ② 当参数为字符串时，返回字母表中顺序最靠前的字符

        ③ 当比较值列表中有NULL时，不能判断大小，返回值为NULL

        GREATEST 语法格式为：

GREATEST(值1，值2，...，值n)

其中，n表示参数列表中有n个值。当有两个或多个参数时，返回值为最大值。注意：

        ① 假如任意一个自变量为NULL，则GREATEST()的返回值为NULL

        ② 当参数中是整数或者浮点数时，GREATEST将返回其中最大的值

        ③ 当参数为字符串时， 返回字母表中顺序最靠后的字符

4.2.6 BETWEEN ... AND 运算符

        使用的格式通常为

SELECT 字段名 
FROM 表名 
WHERE 筛选条件 BETWEEN A AND B;

此时，当筛选条件大于或等于A，且小于或等于B时，结果为1，否则结果为0。

4.2.7 IN \ NOT IN 运算符

        IN运算符用于判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给定的值为NULL，或者IN列表中存在NULL，则结果为NULL。

        NOT IN运算符用于判断给定的值是否不是IN列表中的一个值，如果不是IN列表中的一 值，则返回1，否则返回0。

        使用格式为

SELECT 字段名 
FROM 表名 
WHERE 筛选的字段 IN/NOT IN (A,B...);

4.2.8 模糊查询（LIKE）

        主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回 0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。使用格式为

SELECT 字段名 
FROM 表名 
WHERE 筛选的字段 LIKE '%字符串%';

其中，% 代表不确定个数的字符，如:

        ① a% 代表以字符 a 开头的数据

        ② %a 代表以字符 a 结尾的数据

        ③ %a% 代表包含字符 a 的数据

此外，一个 _ 代表一个不确定的字符（不是下划线，若想匹配下划线，则需使用转义字符 \ ），如：

        ① _a 代表第二个字符是 a 的数据

        ② a_ 代表倒数第二个字符是 a 的数据

        ③ __a 代表第三个字符是 a 的数据

        ④ _\_a 代表第二个字符是 _ ，且第三个字符是 a 的数据

DAY 5

5.1 逻辑运算符

        使用时注意运算符两侧条件关系式写完整

5.1.1 逻辑或（OR || ）

        当给定的值都不为NULL，并且任何一个值为非0值时，则返回1，否则返回0；当一个值为NULL，并且另一个值为非0值时，返回1，否则返回NULL；当两个值都为NULL时，返回NULL。 

        有一侧满足则被选中。

5.1.2 逻辑与（AND &&）

        当给定的所有值均为非0值，并且都不为NULL时，返回1；当给定的一个值或者多个值为0时则返回0；否则返回NULL。

        两侧都满足才被选中。

 5.1.3 逻辑异或（XOR）

        是当给定的值中任意一个值为NULL时，则返回NULL；如果两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1。

        一侧满足，另一侧不满足才被选中。

 5.1.4 注意

        AND 运算级高于 OR

5.2 排序

5.2.1 排序规则

        ① 使用 ORDER BY 子句排序，升序为 ASC（ascend），降序为 DESC（descend），默认为升序

        ② ORDER BY 子句在SELECT语句的结尾

        ③ ORDER BY 排序字段不一定是查询字段

        ④ 若 WHERE 和 ORDER BY 同时出现，WHERE 需要声明在 FROM 之后，ORDER BY 之前

        ⑤ ORDER BY 可以使用列的别名

SELECT 字段1,字段2, ...
FROM 表名
ORDER BY 排序字段名 ASC(或 DESC);

5.2.2 二级排序（多列排序）

        在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

        使用方法如下：

SELECT 字段1,字段2, ...
FROM 表名
ORDER BY 排序字段名1 ASC(或 DESC), 排序字段名2 ASC(或 DESC);

        多级排序的情况可以类推

5.3 分页

5.3.1 背景

        查询返回的记录太多，查看起来很不方便

5.3.2 规则

        ① 在一页显示指定条数据：

LIMIT 位置偏移量, 行数

        其中位置偏移量指从第几条数据开始

        ② 公式：每页显示pageSize条记录，此时显示第pageNo页
                LIMIT (pageNo - 1)*pageSize, pageSize

5.3.3 WHERE 、ORDER BY、LIMIT 声明顺序

        先 WHERE，后 ORDER BY，最后 LIMIT

5.3.4 OFFSET 

        MySQL8.0新特性，使用方法为

LIMIT 行数 OFFSET 位置偏移量

DAY 6

6.1 多表查询（关联查询）

6.1.1 前提

        这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

6.1.2 连接条件

        进行多表查询时需要有连接条件（即相同的字段），否则会出现笛卡尔积错误（将任意表进行连接，即使这两张表不相关）

        如果有n个表实现多表查询，则至少需要有 n-1 个连接条件，若少于 n-1，则必然会出现笛卡尔积的错误

6.1.3 出现笛卡尔积的错误的情况

        ① 省略多个表的连接条件（或关联条件）

        ② 连接条件（或关联条件）无效

        ③ 所有表中的所有行互相连接

6.1.4 查询方法

        为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件，语法如下

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; #连接条件

6.1.5 查询语句中的字段存在于多表中的情况

        此情况必须指明此字段所在的表，语法为

SELECT 字段1, 表名.字段2;

        建议：多表查询时，每个字段前都指明其所在的表

DAY 7

7.1 多表查询的分类

7.1.1 等值连接与非等值连接

等值连接

SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

 非等值连接

SELECT last_name,salary,grade_level
FROM employees e,job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

7.1.2 自连接与非自连接

非自连接

之前做的多表查询均为非自连接（多个表进行查询）

自连接

#查询员工id，员工姓名及其管理者的id和姓名
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp,employees mgr
WHERE emp.manager_id = mgr.employee_id;

7.1.3 内连接和外连接

内连接

        合并具有同一列的两个以上的表的行，只将满足连接条件的数据查出来，结果集中不包含一个表与另一个表不匹配的行（上述的多表查询分类均为内连接）

语法：

SELECT 字段列表
FROM A表 JOIN B表
ON 关联条件
JOIN C表    #多一个表多一个JOIN ...ON
ON 关联条件
...
WHERE 等其他子句;

外连接（在多表查询的基础上，通常会出现“所有的”等词）

          合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表或右表中不匹配的行

        外连接又分为：左外连接，右外连接，满外连接

左外连接：两个表在连接过程中除了返回满足条件的行以外还返回左表中不满足条件的行

        语法：

#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
...
WHERE 等其他子句;

右外连接：两个表在连接过程中除了返回满足条件的行以外还返回右表中不满足条件的行

        语法：

#实现查询结果是A
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

满外连接：两个表在连接过程中同时返回满足条件与不满足条件的行（SQL99不支持）

        使用 UNION 对两个查询结果进行连接

        语法：

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

7.2 七种 JOIN 查询

DAY 8

8.1 单行函数

特点：操作数据对象、接受参数返回一个结果、只对一行进行变换、每行返回一个结果、可以嵌套 参数可以是一列或一个值

8.2 数值函数

8.2.1 基本函数

8.2.2 角度与弧度互换函数

8.2.3 三角函数

8.2.4  指数和对数

8.2.5 进制转换

8.3 字符串函数

注意：字符串的索引是从1开始的

8.4 日期和时间函数

8.4.1 获取日期和时间

8.4.2 日期与时间戳的转换

8.4.3 获取月份、星期、星期数、天数等函数

8.4.4 日期的操作函数

其中，EXTRACT(type FROM date)函数中type的取值与含义如下表：

8.4.5 时间和秒钟转换的函数

8.4.6 计算日期和时间的函数

① (如下函数中，相差的时间为expr)

上述函数中type的取值：

DAY 9

9.1 流程控制函数

9.1.1 IF(value,value1,value2)

用法

如果value的值为TRUE，返回value1，否则返回value2

9.1.2 IFNULL(value1, value2)

用法

如果value1不为NULL，返回value1，否则返回value2

9.1.3 CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ... ELSE 结果n END

用法

相当于 if-else 语句