MySQL学习笔记

黑黑的脚后跟

已于 2022-01-20 11:42:36 修改

阅读量255

点赞数

分类专栏：面试文章标签： mysql 数据库 sql

于 2022-01-12 19:22:11 首次发布

本文链接：https://blog.csdn.net/qq_41658995/article/details/122459974

版权

面试专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL

1.SQL语句分类

数据库查询语言（DQL）
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE 子句组成的查询块，简称DQL，Data Query Language。代表关键字为select。
数据库操作语言（DML）
用户通过它可以实现对数据库的基本操作。简称DML，Data Manipulation Language。代表关键字为insert、delete 、update。
数据库定义语言（DDL）
数据定义语言DDL用来创建数据库中的各种对象，创建、删除、修改表的结构，比如表、视图、索引、同义词、聚簇等，简称DDL，Data Denifition Language。代表关键字为create、drop、alter。和DML相比，DML是修改数据库表中的数据，而 DDL 是修改数据中表的结构。
事务控制语言（TCL）
TCL经常被用于快速原型开发、脚本编程、GUI和测试等方面，简称：TCL，Trasactional Control Languag。代表关键字为commit、rollback。
数据控制语言（DCL）
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。简称：DCL，Data Control Language。代表关键字为grant、revoke。

2.SELECT语句

2.1语法

表中所有字段

	SELECT * FROM 表名称;

表中的若干列

    SELECT 列名称 FROM 表名称;

2.2 列的别名

AS:alias(别名)，可省略

	SELECT 列名称 AS "别名" FROM 表名称;

注：列的别名用双引号，字符串用单引号

2.3 去除重复行

DISTINCT

    SELECT  DISTINCT 列名称 FROM 表名称;

2.4 空值参与运算

空值：null，不等同于0/’’/‘null’
null参与运算后结果为null

2.5 着重号

``
当与关键字重名时，加着重号表示不是关键字

	SELECT * FROM `order`;

2.6 显示表结构

DESCRIBE

	DESCRIBE employees;

2.7 过滤条件

WHERE 声明在FROM后面

	SELECT * 
	FROM employees 
	WHERE id=1;

3.运算符

3.1 算术运算符

加 + 减 - 乘 * 除 /（或DIV）取模 %

3.2 比较运算符

等于= 安全等于<=> 不等于<>(!=) 大于> 小于< 大于等于>= 小于等于<=

  注：字符串存在隐式数值转换，若转换不成功则为0
  	 = 中只要有NULL参与判断，结果为NULL
  	 <=>安全等于可以用于判断是否为NULL

IS NULL / IS NOT NULL / ISNULL(列名)
LEAST(列名,列名…) / GREATEST(列名,列名…)
BETWEEN … AND …
IN (set) / NOT IN (set)
LIKE

	SELECT * FROM employees WHERE last_name LIKE '%a%'; #包含a
	SELECT * FROM employees WHERE last_name LIKE '_a%'; #第二个字符是a

3.3 逻辑运算符

NOT 或 !
AND 或 &&
OR 或 ||
XOR
```
  注:AND的优先级高于OR
```

3.3 位运算符

按位与 &
按位或 |
按位异或 ^
按位右移 >>
按位左移 <<

4.排序与分页

4.1 排序

ORDER BY
升序 ASC（ascend）
降序 DESC （descend）

	SELECT * 
	FROM employees
	ORDER BY salary (ASC/DESC);
	 
	SELECT  salary * 12 AS "annual_sal"
	FROM employees
	ORDER BY annual_sal (ASC/DESC);

注：列的别名只能在ORDER BY中使用，不能在WHERE中使用，执行顺序为 from where->select->order by
   FROM声明在WHERE后ORDER BY之前

二级排序

	SELECT  salary * 12 AS "annual_sal"
	FROM employees
	ORDER BY annual_sal DESC,department_id ASC;

4.2 分页

LIMIT

	SELECT * 
	FROM employees
	LIMIT 0,100;

	注：WHERE ... ORDER BY ... LIMIT ...

5.多表查询

需要连接条件

	SELECT
		* 
	FROM
		employees,
		departments 
	WHERE
		employees.DEPARTMENT_ID = departments.DEPARTMENT_ID;

注：若查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表

可以给表取别名

	SELECT
		* 
	FROM
		employees e,
		departments d 
	WHERE
		e.DEPARTMENT_ID = d.DEPARTMENT_ID;

注：若给表起了别名，则必须在SELECT和WHERE中必须使用（与执行顺序有关）

5.1 多表查询的分类

等值连接VS非等值连接
自连接VS非自连接
内连接VS外连接

5.2 等值连接VS非等值连接

	SELECT
		e.LAST_NAME,
		e.SALARY,
		j.grade_level 
	FROM
		employees e,
		job_grades j 
	WHERE
		e.SALARY >= j.lowest_sal 
		AND e.SALARY <= j.highest_sal

5.3 自连接VS非自连接

	SELECT
		e.LAST_NAME,
		e.EMPLOYEE_ID,
		m.LAST_NAME,
		m.EMPLOYEE_ID 
	FROM
		employees e,
		employees m 
	WHERE
		e.MANAGER_ID = m.EMPLOYEE_ID

5.4内连接VS外连接

内连接（交集）：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
外连接：合并具有同一列的两个以上的表的行，结果集中包含一个表与另一个表不匹配的行（左外/右外/满外）
SQL99内连接–JOIN ON

	SELECT
		e.LAST_NAME,
		e.EMPLOYEE_ID,
		m.LAST_NAME,
		m.EMPLOYEE_ID 
	FROM
		employees e
	JOIN
		employees m 
	ON
		e.MANAGER_ID = m.EMPLOYEE_ID

SQL99外连接–JOIN ON

	SELECT
		e.LAST_NAME,
		d.DEPARTMENT_NAME 
	FROM
		employees e
		LEFT/RIGHT (OUTER) JOIN departments d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID

UNION使用

  UNION---合并查询结果并去重
  UNION---合并查询结果不去重

MySQL实现FULL OUTER JOIN

	SELECT
		e.EMPLOYEE_ID,
		d.DEPARTMENT_NAME 
	FROM
		employees e
	LEFT JOIN departments d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
	UNION
	SELECT
		e.EMPLOYEE_ID,
		d.DEPARTMENT_NAME 
	FROM
		employees e
	RIGHT JOIN departments d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
	WHERE e.DEPARTMENT_ID IS NULL

5.5 SQL99语法新特性

自然连接

  NATURAL JOIN自动连接表中所有相同字段

	SELECT
		e.EMPLOYEE_ID,
		d.DEPARTMENT_NAME 
	FROM
		employees e
	JOIN departments d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
	AND e.MANAGER_ID= d.MANAGER_ID
	
	
	SELECT
		e.EMPLOYEE_ID,
		d.DEPARTMENT_NAME 
	FROM
		employees e
	NATURAL JOIN departments d

USING

	SELECT
		e.EMPLOYEE_ID,
		d.DEPARTMENT_NAME 
	FROM
		employees e
	JOIN departments d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
	
	
	
	SELECT
		e.EMPLOYEE_ID,
		d.DEPARTMENT_NAME 
	FROM
		employees e
	JOIN departments d
	USING (department_id)

6. 单行函数

6.1 数值函数

6.2 字符串函数

6.3 日期时间函数

6.4 流程控制函数

IF(v,v1,v2)
```
  类似 v？v1：v2
```
IFNULL(v1,v2)
```
  若v1不为null则返回v1
```
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2…ELSE 结果n END
```
  相当于if...else if...else if...else...
```
CASE expr WHEN 常量1 THEN 值1 WHEN 常量2 THEN 值2…ELSE 结果n END
```
  相当于switch...case...
```

6.5 加密与解密函数

6.6 MySQL信息函数

6.7 其他函数

7. 聚合函数

多行函数，多条出一条，如max，不可嵌套

7.1 常见聚合函数

AVG/SUM
MAX/MIN
COUNT
```
  AVG/SUM/COUNT不计算NULL
```

7.2 GROUP BY

	SELECT AVG(salary)
	FROM employees
	GROUP BY department_id
	#按多条件分组
	SELECT AVG(salary)
	FROM employees
	GROUP BY department_id,job_id

非组函数的字段必须出现在GROUP BY中

	#反例
	SELECT department_id,job_id,AVG(salary)
	FROM employees
	GROUP BY department_id

GROUP BY声明在FROM后面 WHERE后面 ORDER BY前面 LIMIT前面

GROUP BY…WITH ROLLUP

  在group分组字段的基础上再进行统计数据，不可与ORDER BY合用

7.3 HAVING的使用

	用来过滤数据，声明在group by后面

	#错误写法，有聚合函数不能用where
	SELECT DEPARTMENT_ID,MAX(SALARY)
	FROM EMPLOYEES
	WHERE MAX(SALARY)>10000
	GROUP BY DEPARTMENT_ID

	SELECT DEPARTMENT_ID,MAX(SALARY)
	FROM EMPLOYEES
	GROUP BY DEPARTMENT_ID
	HAVING MAX(SALARY)>10000

	SELECT DEPARTMENT_ID,MAX(SALARY)
	FROM EMPLOYEES
	WHERE DEPARTMENT_ID IN(10,20,30,40)
	GROUP BY DEPARTMENT_ID
	HAVING MAX(SALARY)>10000
	
	SELECT DEPARTMENT_ID,MAX(SALARY)
	FROM EMPLOYEES
	GROUP BY DEPARTMENT_ID
	HAVING MAX(SALARY)>10000 AND DEPARTMENT_ID IN(10,20,30,40)

	条件中没有聚合函数，having where均可，但建议使用where（效率高）

8. SQL语句的执行过程

	SELECT ...,...
	FROM ...JOIN...ON...
	WHERE ...
	GROUP BY ...
	HAVING ...
	ORDER BY ... (ASC/DESC)
	LIMIT ...,...

8.1 执行过程

在这里插入图片描述

	方框内按顺序执行，显然where先于having执行，更早的进行数据过滤，因此执行效率更高

9. 子查询

9.1 单行子查询

	SELECT
		LAST_NAME,
		SALARY 
	FROM
		employees 
	WHERE
		SALARY >(
		SELECT
			SALARY 
		FROM
			employees 
		WHERE
			LAST_NAME = 'Abel')

9.2 多行子查询

	操作符 IN ANY ALL SOME

解决聚合函数不可嵌套问题

	SELECT
		MIN( avg_salary ) 
	FROM
		( SELECT AVG( SALARY ) avg_salary 
		  FROM employees 
		  GROUP BY DEPARTMENT_ID ) avg

9.3 相关子查询

	SELECT LAST_NAME,SALARY,DEPARTMENT_ID
	FROM employees e1
	WHERE SALARY>(SELECT AVG(SALARY)
								FROM employees e2
								WHERE DEPARTMENT_ID=e1.DEPARTMENT_ID)

9.4 EXISTS与NOT EXISTS

	#子查询方式	
	SELECT
		* 
	FROM
		employees 
	WHERE
		EMPLOYEE_ID IN (
			SELECT
				MANAGER_ID 
			FROM
			employees 
		)
	#EXISTS
	SELECT
	* 
	FROM
		employees e1 
	WHERE
		EXISTS (
		SELECT
			* 
		FROM
			employees e2 
		WHERE
		e1.EMPLOYEE_ID = e2.MANAGER_ID 
		)

10. 创建和管理

10.1 创建和管理数据库

CREATE DATABASE (IF NOT EXISTS) 数据库名 (CHARACTER SET 字符集)
SHOW DATABASES
```
  查看所有数据库
```
USE 数据库名
SHOW TABLES (FROM 数据库名)
```
  查看所有表
```
ALTER DATABASE 数据库名 CHARACTER SET 字符集
DROP DATABASE (IF EXISTS) 数据库名

10.2 创建表

	CREATE TABLE (IF NOT EXISTS) 表名 (
		列名 类型,
		...
	)
	#基于现有表，同时导入数据
	CREATE TABLE (IF NOT EXISTS) 表名 AS
	SELECT...FROM...

10.3 管理表

ALTER TABLE 表名 ADD 字段名类型
ALTER TABLE 表名 MODIFY 字段名类型
ALTER TABLE 表名 CHANGE 字段名新字段名类型
ALTER TABLE 表名 DROP COLUM 字段名
RENAME TABLE 表名 TO 新表名/ALTER TABLE 表名 RENAME TO 新表名
DROP TABLE (IF EXISTS) 表名
TRUNCATE TABLE 表名

11. 数据的增删改

11.1 数据的添加

INSERT INTO 表名 VALUES(值1，值2...),(值1，值2...),(值1，值2...);.
INSERT INTO 表名(字段1，字段2) VALUES(值1，值2),(值1，值2),(值1，值2);
INSERT INTO 表名(字段1，字段2) (SELECT...FROM...)

11.2 数据的更新

UPDATE...SET...WHERE...

11.3 数据的删除

DELETE..FROM...WHERE...

11.4 计算列

	CREATE TABLE test(
	a INT,
	b INT,
	c INT GENERATED ALWAYS AS (a+b) VIRTUAL
	)

12. 约束

	对表中字段的限制，为了保证数据完整性，在CREATE TABLE或ALTER TABLE时添加约束

12.1 约束的分类

（约束的字段个数）单列约束VS多列约束
（约束的作用范围）列级约束VS表级约束
（约束的功能）非空/唯一/主键/外键/检查/默认值

12.2 查看表中约束

	SELECT
	* 
	FROM
		information_schema.TABLE_CONSTRAINTS 
	WHERE
		TABLE_NAME = 'employees'

12.3 删除约束

	ALTER TABLE 表名
	DROP INDEX 约束索引名

13. 视图

	存储起来的SELECT语句，视图与基表中数据相关联

13.1 创建视图

	CREATE VIEW 视图名称
	AS 查询语句

13.2 查看视图

SHOW TABLES
DESCRIBE 视图名
SHOW TABLE STATUS LIKE 视图名
SHOW CREATE VIEW 视图名

13.3 修改和删除视图

CREATE OR REPLACE 视图名 AS 查询语句
ALTER VIEW 视图名 AS 查询语句
DROP VIEW 视图名

14. 变量

14.1 系统变量

	以两个@开头

GLOBAL 全局变量
SESSION 会话变量

14.2 用户自定义变量

会话用户变量
```
  以一个@开头
```

局部变量

  使用DECLARE声明，且必须声明在BEGIN...END中的首行

15. 流程控制

15.1 分支结构IF

	IF 表达式1 THEN 操作1
	ELSEIF 表达式2 THEN 操作2...
	ELSE 操作N
	END IF

15.2 分支结构CASE

	CASE 表达式
	WHEN 值1 THEN 结果1或语句1
	WHEN 值2 THEN 结果2或语句2
	...
	ELSE 结果N或语句N
	END [CASE](在begin end 中需要加case)

	CASE 
	WHEN 条件1 THEN 结果1或语句1
	WHEN 条件
	2 THEN 结果2或语句2
	...
	ELSE 结果N或语句N
	END [CASE](在begin end 中需要加case)

15.3 循环结构LOOP

	[LOOP_LABLE:]LOOP
		循环体
		跳出循环需要LEAVE[LOOP_LABLE]
	END LOOP[LOOP_LABLE]

15.4 循环结构WHILE

	[WHILE_LABLE:]WHILE 循环条件 DO
		循环体
	END WHILE[WHILE_LABLE]

15.5 循环结构REPEAT

	[REPEAT_LABLE:]REPEAT
		循环体
	UNTIL  结束循环的条件表达式
	END REPEAT[REPEAT_LABLE]

15.6 LEAVE和ITERATE

LEAVE label–break
ITERATE label–continue
```
  ITEATE只用在循环语句内
```

16. 游标

作为指针定位某一条数据

16.1 声明游标

DECLARE 游标名 CURSOR FOR(或IS) 语句

16.2 开启游标

OPEN 游标名

16.3 使用游标

FETCH 游标名 INTO 变量名1,变量名2...

16.4 关闭游标

CLOSE 游标名

17. 触发器

黑黑的脚后跟

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL学习笔记

MySQL1.SQL语句分类数据库查询语言（DQL）数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE 子句组成的查询块，简称DQL，Data Query Language。代表关键字为select。数据库操作语言（DML）用户通过它可以实现对数据库的基本操作。简称DML，Data Manipulation Language。代表关键字为insert、delete 、update。数据库定义语言（DDL）数据定义语言DDL用来创建数据库中的各种对象，创建、删除
复制链接

扫一扫

专栏目录