MySQL从入门到快速上手

03-03 5565阅读 0评论

文章目录

第一章了解SQL
第二章检索数据
第三章排序
第四章过滤数据
第五章高级数据过滤
第六章多表查询

内连接（交集）
外连接
多表连接
UNION
UNION ALL
第七章单行函数

日期和时间函数

获取日期、时间
日期与时间戳的转换
获取月份、星期、星期数、天数等函数
日期的操作函数
时间和秒钟的转换函数
计算日期和时间的函数
日期的格式与解析
流程控制函数
第八章聚合函数

AVG()
SUM()
MAX()
MIN()
COUNT()
GROUP BY
HAVING
插入（特殊）

插入检索出来的数据
复制表
删除表
SELECT 的执行过程

查询的结构
SELETC 执行顺序
关键字顺序
语句执行顺序
SQL 执行原理
第九章子查询
第十章修改表结构

追加一个列
修改一个列
重命名一个列名
删除一个列
使用经验

创建时间获取当前时间
更新时间获取当前时间
SELECT 出现在其他位置
Other

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

数据导入

第一章了解SQL

主键：表中每一行都应该有一列（或几列）可以唯一标识自己。

主键可以是一列构成，也可以是几列共同构成。

唯一非空不允许修改

第二章检索数据

关于通配符：一般而言，除非你确实需要表中的每一列，否则最好别使用 * 通配符。

检索不同的值：DISTINCT

不能部分使用 DISTINCT , DISTINCT 关键字作用于所有的列，不仅仅是跟在其后的那一列。
DISTINCT 需要放到所有列名的前面，如果写成 SELECT salary, DISTINCT department_id
FROM 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 IS NULL

SELECT * FROM `om_fnd_company_user` where phone IS NOT NULL

第三章排序

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

第四章过滤数据

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

SELECT id, name, age FROM student WHERE id BETWEEN 5 AND 10;

空值

SELECT id, name, age FROM student WHERE age IS NULL;

非空值

SELECT id, name, age FROM student WHERE age IS NOT NULL;

第五章高级数据过滤

注意：任何时候具有 AND 和 OR 操作符的 WHERE 子句，都应该使用圆括号将加以区分。

NOT：在 WHERE 子句中，用来否定其后条件的顺序。

SELECT id, name, age FROM student WHERE NOT id = 8;

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

WHERE NOT

SELECT name FROM stu WHERE NOT age

不要过度使用通配符，不得已了也尽量不要让模糊查询位于开始处。
分组函数 AVG(), MAX(), MIN(), SUM() 会忽略 NULL 值。

COUNT(*) 与 COUNT(字段)

COUNT(*) 会统计所有列

COUNT(字段) 会统计非 NULL

第六章多表查询

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

内连接（交集）

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

基本语法：

SELECT table1.column, table2.column,table3.column
FROM 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
LEFT OUTER JOIN departments d 	# OUTER可以省略
ON (e.department_id = d.department_id) ;

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

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

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

多表连接

UNION

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

SELECT * FROM user WHERE did = 2
UNION
SELECT * FROM user WHERE 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…SUNDAY
WEEKDAY(date)	返回周几，注意，周1是0，周2是1…周日是6
QUARTER(date)	返回日期对应的季度，范围为1～4
WEEK(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，否则返回value2
IFNULL(value1, value2)	如果value1不为NULL，返回 value1，否则返回value2
CASE 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

分组

SELECT AVG(salary)
FROM employees
GROUP BY department_id ;

多列进行分组

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

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

HAVING

过滤分组

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(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 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。

插入（特殊）

插入检索出来的数据

INSERT INTO stu(name, age) SELECT name, age FROM user;

复制表

CREATE TABLE user AS SELECT * 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

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7

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

SQL 执行原理

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

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

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

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

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

第九章子查询

自连接效率高于子查询

第十章修改表结构

追加一个列

ALTER TABLE sys_log ADD `before_params` varchar(2000) 
	DEFAULT NULL COMMENT '请求前的参数' AFTER `class_method`;

修改一个列

ALTER TABLE sys_log MODIFY `before_params` varchar(2000) 
	DEFAULT NULL COMMENT '请求前的参数' AFTER `class_method`;

重命名一个列名

ALTER TABLE uesr CHANGE department_name dept_name varchar(15);

删除一个列

ALTER TABLE sys_log DROP 字段名

使用经验

创建时间获取当前时间

create_time	datetime DEFAULT CURRENT_TIMESTAMP() COMMENT '创建时间'
create_time	datetime DEFAULT CURRENT_DATE() COMMENT '创建日期'

更新时间获取当前时间

update_time datetime DEFAULT CURRENT_TIMESTAMP() 
	ON UPDATE CURRENT_TIMESTAMP() COMMENT '更新时间'

SELECT 出现在其他位置

出现在 FROM 后 (可以将子查询的结果当成是一个临时表)
```
SELECT e.ename 
FROM EMP e
JOIN (SELECT MAX(sal) AS msal, deptno FROM emp GROUP BY 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