MySQL从入门到快速上手

文章目录

第一章 了解SQL

• 主键：表中每一行都应该有一列（或几列）可以唯一标识自己。 - 主键可以是一列构成，也可以是几列共同构成。- 唯一 非空 不允许修改

第二章 检索数据

• 关于通配符：一般而言，除非你确实需要表中的每一列，否则最好别使用 * 通配符。
• 检索不同的值：DISTINCT - 不能部分使用 DISTINCT , DISTINCT 关键字作用于所有的列，不仅仅是跟在其后的那一列。- DISTINCT 需要放到所有列名的前面，如果写成 SELECT salary, DISTINCT department_idFROM employees 会报错。- DISTINCT 其实是对后面所有列名的组合进行去重，你能看到最后的结果是 74 条，因为这 74 个部门id不同，都有 salary 这个属性值。如果你想要看都有哪些不同的部门（department_id），只需要写 DISTINCT department_id 即可，后面不需要再加其他的列名了。
• 限制结果：LIMIT - 注意：LIMIT 2 OFFSET 3 等价于 LIMIT 3, 2
• 空值参与运算 - 所有运算符或列值遇到 null 值，运算的结果都为null- 这里一定要注意，在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的 长度是空。而且，在 MySQL 里面，空值是占用空间的。
• 空运算符 和 非空运算符

SELECT*FROM`om_fnd_company_user`where phone ISNULL

SELECT*FROM`om_fnd_company_user`where phone ISNOTNULL

第三章 排序

• 排序：如果想在多个列上进行降序排序，必须对 **每一列 **指定 DESC 关键字。

第四章 过滤数据

• 范围：BETWEEN 5 AND 10 表示闭区间

SELECT id, name, age FROM student WHERE id BETWEEN5AND10;

• 空值

SELECT id, name, age FROM student WHERE age ISNULL;

• 非空值

SELECT id, name, age FROM student WHERE age ISNOTNULL;

第五章 高级数据过滤

• 注意：任何时候具有 AND 和 OR 操作符的 WHERE 子句，都应该使用圆括号将加以区分。
• NOT：在 WHERE 子句中，用来否定其后条件的顺序。

SELECT id, name, age FROM student WHERENOT id =8;

• IN 操作符 一般比一组 OR 执行的更快。
• WHERE NOT

SELECT name FROM stu WHERENOT age <50;

• 不要过度使用通配符，不得已了也尽量不要让模糊查询位于开始处。
• 分组函数 AVG(), MAX(), MIN(), SUM() 会忽略 NULL 值。
• COUNT() 与 COUNT(字段)- COUNT() 会统计所有列- COUNT(字段) 会统计非 NULL

第六章 多表查询

要避免笛卡尔积。
对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或 表名）进行限定。

内连接（交集）

合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

基本语法：

SELECT table1.column, table2.column,table3.columnFROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件（有疑惑）

举例1：

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e JOIN departments d
ON(e.department_id = d.department_id);

举例2：

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;

外连接

两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

• 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFTOUTERJOIN departments d     # OUTER可以省略ON(e.department_id = d.department_id);

• 如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHTOUTERJOIN departments d # OUTER可以省略ON(e.department_id = d.department_id);

注意：我们要 控制连接表的数量 。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重（建议不要超过 3 个），因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。

多表连接

UNION

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

SELECT*FROMuserWHERE did =2UNIONSELECT*FROMuserWHERE did =1

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL

UNION ALL 操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

第七章 单行函数

日期和时间函数

获取日期、时间

函数****用法CURDATE() ，CURRENT_DATE()返回当前日期，只包含 年、月、日CURTIME() ， CURRENT_TIME()返回当前时间，只包含 时、分、秒NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()返回当前系统日期和时间UTC_DATE()返回UTC（世界标准时间）日期UTC_TIME()返回UTC（世界标准时间）时间
图文并茂的举个栗子：

日期与时间戳的转换

函数****用法UNIX_TIMESTAMP()以UNIX时间戳的形式返回当前时间。UNIX_TIMESTAMP(date)将时间date以UNIX时间戳的形式返回。FROM_UNIXTIME(timestamp)将UNIX时间戳的时间转换为普通格式的时间

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

函数****用法YEAR(date) / MONTH(date) / DAY(date)返回具体的日期值HOUR(time) / MINUTE(time) / SECOND(time)返回具体的时间值MONTHNAME(date)返回月份：January，…DAYNAME(date)返回星期几：MONDAY，TUESDAY…SUNDAYWEEKDAY(date)返回周几，注意，周1是0，周2是1…周日 是6QUARTER(date)返回日期对应的季度，范围为1～4WEEK(date) ， WEEKOFYEAR(date)返回一年中的第几周DAYOFYEAR(date)返回日期是一年中的第几天DAYOFMONTH(date)返回日期位于所在月份的第几天DAYOFWEEK(date)返回周几，注意：周日是1，周一是2…周六 是7

日期的操作函数

函数****用法EXTRACT(type FROM date)返回指定日期中特定的部分，type指定返回的值
EXTRACT(type FROM date)函数中type的取值与含义：

举个栗子：

时间和秒钟的转换函数

函数说明TIME_TO_SEC(time)将 time 转化为秒并返回结果值。转化的公式为： 小时*3600+分钟 *60+秒SEC_TO_TIME(seconds)将 seconds 描述转化为包含小时、分钟和秒的时间

计算日期和时间的函数

函数用法DATE_ADD(datetime, INTERVAL expr type)， ADDDATE(date,INTERVAL expr type)返回与给定日期时间相差INTERVAL 时间段的日期时间DATE_SUB(date,INTERVAL expr type)， SUBDATE(date,INTERVAL expr type)返回与date相差INTERVAL时间间隔 的日期
上述函数中type的取值：

函数说明ADDTIME(time1,time2）返回time1加上time2的时间。当time2为一个数字时，代表 的是 秒 ，可以为负数SUBTIME(time1,time2)返回time1减去time2后的时间。当time2为一个数字时，代 表的是 秒 ，可以为负数DATEDIFF(date1,date2)返回date1 - date2的日期间隔天数TIMEDIFF(time1, time2)返回time1 - time2的时间间隔FROM_DAYS(N)返回从0000年1月1日起，N天以后的日期TO_DAYS(date)返回日期date距离0000年1月1日的天数LAST_DAY(date)返回date所在月份的最后一天的日期MAKEDATE(year,n)针对给定年份与所在年份中的天数返回一个日期MAKETIME(hour,minute,second)将给定的小时、分钟和秒组合成时间并返回PERIOD_ADD(time,n)返回time加上n后的时间

日期的格式与解析

函数说明DATE_FORMAT(date,fmt)按照字符串fmt格式化日期date值TIME_FORMAT(time,fmt)按照字符串fmt格式化时间time值GET_FORMAT(date_type,format_type)返回日期字符串的显示格式STR_TO_DATE(str, fmt)按照字符串fmt对str进行解析，解析为一个日期
上述

非GET_FORMAT 

函数中fmt参数常用的格式符：

GET_FORMAT函数中date_type和format_type参数取值如下：

流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。 MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
函数说明IF(value,value1,value2)如果value的值为TRUE，返回 value1，否则返回value2IFNULL(value1, value2)如果value1不为NULL，返回 value1，否则返回value2CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结 果2 … [ELSE resultn] END相当于Java的if…else if…else…CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END相当于Java的switch…case…
图文并茂的举个栗子：
//todo

第八章 聚合函数

AVG()

SUM()

MAX()

MIN()

COUNT()

问题：用 count(*)，count(1)，count(列名) 谁好呢?

其实，对于 MyISAM 引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。
**但是 *InnoDB 引擎的表用count(),count(1)直接读行数，复杂度是O(n)，因为 InnoDB 真的要去数一遍。 但好于具体的count(列名)。

问题：能不能使用 count(列名) 替换 count(*) ?

不要使用 count(列名)来替代 count() ， count() 是 SQL92 定义的标准统计行数的语法，跟数 据库无关，跟 NULL 和非 NULL 无关。 说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

GROUP BY

分组

SELECTAVG(salary)FROM employees
GROUPBY department_id ;

多列进行分组

SELECT department_id dept_id, job_id,SUM(salary)FROM employees
GROUPBY department_id, job_id ;

除聚合函数语句外，SELECT 语句中的每一列都必须在 GROUP BY 字句中给出。

HAVING

过滤分组

SELECT department_id,MAX(salary)FROM employees
GROUPBY department_id
HAVINGMAX(salary)>10000;

注意： 不能在 WHERE 子句中使用聚合函数。
参考

WHERE 和 HAVING 的对比

区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条 件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。另外，WHERE 排除的记录不再包括在分组中。
区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连 接后筛选。
这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选， 用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这 样占用的资源就比较多，执行效率也较低。

开发中的选择： WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含 分组统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。

插入（特殊）

插入检索出来的数据

INSERTINTO stu(name, age)SELECT name, age FROMuser;

复制表

CREATETABLEuserASSELECT*FROM student;

删除表

1.delete:删除表的数据，支持条件过滤，支持回滚。这个删除操作会记录在日志中，所以比较慢。

delete from table_stu;

2.truncate:删除表的所有数据，不支持条件过滤，也不支持回滚，不记录日志，效率比delete高。

truncate table table_stu;

3.drop：删除表的数据的同时，还删除表结构。将表所占的空间都释放掉。他的删除效率最高。

drop table table_stu;

SELECT 的执行过程

查询的结构

#方式1：
SELECT …,…,…
FROM …,…,…
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY …,…
HAVING 包含组函数的过滤条件
ORDER BY … ASC/DESC
LIMIT …,…

#方式2：
SELECT …,…,…
FROM …
JOIN …
ON 多表的连接条件
JOIN …
ON …
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY …,…
HAVING 包含组函数的过滤条件
ORDER BY … ASC/DESC
LIMIT …,…

#其中： #（1）from：从哪些表中筛选 #（2）on：关联多表查询时，去除笛卡尔积 #（3）where：从表中筛选的条件 #（4）group by：分组依据 #（5）having：在统计结果中再次筛选 #（6）order by：排序 #（7）limit：分页

SELETC 执行顺序

关键字顺序

SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT…

语句执行顺序

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

SELECTDISTINCT player_id, player_name,count(*)as num # 顺序 5FROM player JOIN team ON player.team_id = team.team_id # 顺序 1WHERE height >1.80# 顺序 2GROUPBY player.team_id # 顺序 3HAVING num >2# 顺序 4ORDERBY num DESC# 顺序 6LIMIT2# 顺序 7

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个 虚拟表 ，然后将这个虚拟表传入下一个步 骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

SQL 执行原理

SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

1. 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
2. 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。

当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。

1. 当我们拿到了查询数据表的原始数据，也就是最终的虚拟表 vt1 ，就可以在此基础上再进行 WHERE 阶 段 。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表 vt2 。
2. 然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段 。在这个阶段中，实际上是在虚拟表 vt2 的 基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4 。
3. 当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT 阶段 。 首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟 表 vt5-1 和 vt5-2 。
4. 当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段 ，得到虚拟表 vt6 。 最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段 ，得到最终的结果，对应的是虚拟表 vt7 。

当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。
同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的 关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。

第九章 子查询

自连接 效率 高于 子查询

第十章 修改表结构

追加一个列

ALTERTABLE sys_log ADD`before_params`varchar(2000)DEFAULTNULLCOMMENT'请求前的参数'AFTER`class_method`;

修改一个列

ALTERTABLE sys_log MODIFY`before_params`varchar(2000)DEFAULTNULLCOMMENT'请求前的参数'AFTER`class_method`;

重命名一个列名

ALTERTABLE uesr CHANGE department_name dept_name varchar(15);

删除一个列

ALTERTABLE sys_log DROP 字段名

使用经验

创建时间获取当前时间

create_time    datetimeDEFAULTCURRENT_TIMESTAMP()COMMENT'创建时间'

create_time    datetimeDEFAULTCURRENT_DATE()COMMENT'创建日期'

更新时间获取当前时间

update_time datetimeDEFAULTCURRENT_TIMESTAMP()ONUPDATECURRENT_TIMESTAMP()COMMENT'更新时间'

SELECT 出现在其他位置

• 出现在 FROM 后 (可以将子查询的结果当成是一个临时表)

SELECT e.ename 
FROM EMP e
JOIN(SELECTMAX(sal)AS msal, deptno FROM emp GROUPBY deptno) s
ON e.deptno = s.deptno AND e.sal = s.msal;

• 出现在 SELECT 后 - 显示用户姓名和其部门名称

SELECT e.ename,(SELECT dname FROM dept WHERE deptno = e.deptno)FROM  emp e;

— 分隔线 —

Other

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

• DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视 图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。

主要语句关键字包括 CREATE 、 DROP 、 ALTER 等。

• DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库 记录，并检查数据完整性。

主要语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。SELECT是SQL的基础，最为重要。

• DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权 限和安全级别。

主要语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。

数据导入

在命令行客户端登录mysql，使用source指令导入

mysql> source d:\mysqldb.sql