MySQL 学习笔记

__Rain

已于 2024-01-06 09:48:20 修改

阅读量1.4k

点赞数 3

文章标签： mysql 学习笔记

于 2023-11-09 09:58:35 首次发布

本文链接：https://blog.csdn.net/cosx_/article/details/134304445

版权

b站1小时学MySQL

1.安装 MySql

安装命令: sduo apt install mysql-server
安装完成之后会自动启动 MySql 的服务, 可以使用命令 systemctl status mysql 查看服务的状态. 如果没有启动, 可以使用 systemctl start mysql 启动服务
root 用户密码问题, linux 下安装完是有密码的, 使用命令 sudo cat /etc/mysql/debian.cnf 来查看默认用户名和密码. 使用命令 mysql -u debian-sys-maint -p 来连接 MySql, 复制一下 [mysql_upgrade] 里边的密码进行连接
修改 root 用户密码

1)直接使用 root 用户来连接 alter user 'root'@'%' identified with mysql_native_password by '3333';

有可能显示 error 1396(HY000) , 这时可以先将 user 表中的 root, localhost 改成 %

# 错误是由于root 用户默认是没有开放远程权限的
# localhost 表示这个用户只能在本地访问, 改成 % 后就可以在任何地方访问了
use mysql;
select user, host from user;
update user set host = '%' where user = 'root';
flush privileges;

这时我们可以直接使用 mysql -u root -p, 然后输入修改后的密码, 进行连接 MySQL

2)修改密码策略

show variables like 'validate_password%';
# 根据显示表格里的变量名进行修改即可, mysql更新变量名形式可能会稍微变动
set global validate_password.policy=LOW;
set global validate_password.length = 4;

MySql 服务的监听地址

1)无法连接 MySql 服务器, 把服务器放开 3306 端口地址或者在开发环境下关闭防火墙

2)配置文件的监听地址问题, 默认 MySQL 服务只监听本地 IP 地址, 外部是无法连接 MySQL 服务的, 把配置文件的 bind-address 和 mysqlx-bind-addess 都修改为 0.0.0.0 即可. 重启服务 systemctl restart mysql

SQL 基础

DDL 数据定义语言: 操作对象是数据库对象，字段 CREATE 、 DROP 、ALTER 、TRUNCATE

DML 数据操作语言: 对表中的记录进行增删改 (INSERT INTO)、 UPDATE、 (DELETE FROM) 、CALL

DQL 数据查询语言: 查询表中的某些记录 SELECT

DCL 数据控制语言: COMMIT 、 SAVEPOINT 、GRANT 、REVOKE

常见的数据库对象

对象	描述
表(TABLE)	表是存储数据的逻辑单元，以行和列的形式存在，列就是字段，行就是记录
数据字典	就是系统表，存放数据库相关信息的表。系统表的数据通常由数据库系统维护，程序员通常不应该修改，只可查看
约束(CONSTRAINT)	执行数据校验的规则，用于保证数据完整性的规则
视图(VIEW)	一个或者多个数据表里的数据的逻辑显示，视图并不存储数据
索引(INDEX)	用于提高查询性能，相当于书的目录
存储过程(PROCEDURE)	用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境
存储函数(FUNCTION)	用于完成一次特定的计算，具有一个返回值
触发器(TRIGGER)	相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理

数据库

MySQL 数据大类型: 数值, 日期, 时间类型, 字符串类型, JSON 类型, 空间类型

创建数据库

CREATE DATABASE 数据库名;
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
CREATE DATABASE IF NOT EXISTS 数据库名;

注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删
旧库完成的。

使用数据库

SHOW DATABASES;					# 查看当前已经存在的数据库
SHOW CREATE DATABASE 数据库名;	# 查看数据库的创建信息
USE game;						# 切换game数据库
SELECT DATABASE();  			# 查看当前正在使用的数据库

# 更改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集;

# 删除数据库
DROP DATABASE 数据库名; 			
DROP DATABASE IF EXISTS 数据库名; 	# 推荐

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数
据库名.”。

连接远程服务器

# 修改mysql数据库里的user表，修改root用户的host为 %
use mysql;
# 查看用户的host
select host, user from user;
update user set host = '%' where user = 'root';
# 查看root用户的host是否修改成功
select host, user from user;
#将当前操作刷新
flush privileges;

# 连接远程的mysql数据库
mysql -h 远程数据库的ip -u root -p

数据库导入导出

在哪个目录下运行命令，就在哪导出数据库

导出数据: mysqldump -u root -p game player > game.sql, -u 指定用户名, -p 指定密码, game 数据库名, player 表名, > gmae.sql 指定要导入数据的文件名. 其中表名可以省略, 如果省略就会导出整个数据库的所有数据。

导出的数据本质就是创建表，插入记录的代码，导入时就是运行这段代码创建。

导入数据: mysql -u root -p game < game.sql, 把 game.sql 数据导入 game
导入sql文件详细教程

# 查看数据库的字符集和排序规则
SELECT 
    DEFAULT_CHARACTER_SET_NAME,
    DEFAULT_COLLATION_NAME
FROM 
    information_schema.SCHEMATA 
WHERE 
    SCHEMA_NAME = 'wuye_cpoy';

# 创建数据库，并设定字符串和排序规则
CREATE DATABASE wuye CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# 命令行导入数据库文件
				 #数据库名
mysql -u root -p wuye --default-character-set=utf8mb4 < wuye.sql

表

创建表

方式一

CREATE TABLE [IF NOT EXISTS] 表名(
	字段1, 数据类型 [约束条件] [默认值],
	字段2, 数据类型 [约束条件] [默认值],
	字段3, 数据类型 [约束条件] [默认值],
	……
	[表约束条件]
);

CREATE TABLE player(
    id INT,
    name VARCHAR(100),
    level INT,
    exp INT,
    gold DECIMAL(10, 2)
);

加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；
如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

方式二：将创建表和插入数据结合起来

CREATE TABLE emp1 AS SELECT * FROM employees;

CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

使用表

SHOW TABLES:	# 查看当前数据库中的表
SELECT * FROM player;	# 查看表的所有列, * 可以表示 player 的所列
DESC player;	# 查看表的结构, DESC 是 description 的缩写

对记录进行操作

# INSERT IGNORE INTO，这将尝试插入行，如果行已存在，则会被忽略
INSERT INTO player(id, name, level, exp, gold) VALUES(1, '张三', 1,1,1);	#向 player 表中插入一条数据
INSERT INTO player(id, name) VALUES(3, '王五');  
UPDATE player SET level = 1 WHERE name = '王五';    # 修改表中的记录信息
UPDATE player SET level = 1, exp = 0, gold = 0;	# 全部进行修改
DELETE FROM player where gold = 0; # 删除gold为0的记录，delete也是需要跟 from 的

对字段进行操作

使用 ALTER TABLE 语句可以实现：

向已有的表中添加列
修改现有表中的列
删除现有表中的列
重命名现有表中的列

# 对字段进行操作
# ALTER TABLE 表名 ADD [COLUMN] 字段名 字段类型 [FIRST|AFTER 字段名];
ALTER TABLE player ADD COLUMN last_login DATETIME;	# 增加 last_login 列

# ALTER TABLE 表名 DROP [COLUMN] 字段名  要删除的列
ALTER TABLE player DROP COLUMN last_login;	# 删除 last_login 列

# ALTER TABLE 表名 MODIFY [COLUMN] 字段名 字段类型 [DEFAULT 默认值] [FIRST|AFTER 字段名];
ALTER TABLE player MODIFY COLUMN level INT DEFAULT 1;	#  DEFAULT 设置默认等级设为 1
ALTER TABLE player MODIFY COLUMN name VARCHAR(200);	 # 修改列的数据类型

ALTER TABLE player RENAME COLUMN name to nick_name; # 修改列(field)的列名
# ALTER TABLE 表名 CHANGE [COLUMN] 列名 新列名 新数据类型; 可以顺带修改数据类型

重命名表

# 对表进行操作
RENAME TABLE old_name TO new_name;		# 重命名表
ALTER table dept RENAME [TO] detail_dept; # 重命名表

删除表

在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除。
数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除
语法格式：

# DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
DROP TABLE dept80;

IF EXISTS 的含义为：如果当前数据库中存在相应的数据表，则删除数据表；如果当前数据库中不存
在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。
DROP TABLE 语句不能回滚

清空表

TRUNCATE TABLE语句：

删除表中所有的数据
释放表的存储空间

TRUNCATE TABLE detail_dept;

TRUNCATE 语句不能回滚，而使用 DELETE 语句删除数据，可以回滚

常用语句

select * 是显示所有列，所有 field
select level 是只显示等级列

注意: NOT 可以加在任何一个条件语句前面

SELECT执行顺序

1.关键字的顺序是不能颠倒的：

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

2.SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：

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

where 限定条件

1.逻辑判断语句

逻辑运算符有 AND, OR 和 NOT, 优先级: NOT > AND > OR

SELECT * FROM player WHERE level = 1;
SELECT * FROM player WHERE level >= 1 AND level <= 5;

2.IN ， EXISTS

IN ：枚举

SELECT * FROM player WHERE level IN(1, 3, 5);	# 查找等级为1 或 3 或 5 的玩家

EXISTS ：检查子查询是否至少返回一行数据。返回值是布尔类型

如果在子查询中不存在满足条件的行：条件返回 FALSE；继续在子查询中查找
如果在子查询中存在满足条件的行：不在子查询中继续查找；条件返回 TRUE

NOT EXISTS 关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

示例：查询公司管理者的 employee_id，last_name，job_id，department_id 信息

SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS ( 
	SELECT *
	FROM employees e2
	WHERE e2.manager_id = e1.employee_id
);

示例：利用 IF EXISTS 删除多个表

DROP TABLE IF EXISTS exam_record_2011, exam_record_2012, exam_record_2013, exam_record_2014;

性能差异：EXISTS 通常在子查询返回较大结果集时更为高效，因为它只需要判断是否存在满足条件的记录，而不需要获取全部匹配的记录。

NULL 处理：IN 语句在处理包含 NULL 值的情况时可能需要格外小心，而 EXISTS 不受此影响。

EXISTS 与 IN 的区别

IN 是在括号中子查询的结果，放到一个临时表中，然后遍历临时表去主表中进行查询，也就是临时表驱动主表来进行查询。
EXISTS 是将主表每一行数据，带入到EXISTS括号中的语句去进行查询判断其是否存在，也就是以主表驱动子查询。

结论：避免循环的次数多，则可以到达性能优化的目的。
当主表数据多，子查询的结果集小时，采用 IN。
当主表数据少，子查询到结果集大时，采用EXISTS

NOT EXISTS 与 NOT IN
在有索引的情况下，NOT IN 会使索引失效，而 NOT EXISTS 依然是可以使用到索引的。在恰当的使用索引时，NOT EXISTS 是比 NOT IN 效率高的

3.BETWEEN AND

SELECT * FROM player WHERE level BETWEEN 1 AND 10;	# 查找等级 1 到 10 级的玩家
SELECT * FROM player WHERE level NOT BETWEEN 1 AND 10;	# 查找除等级为 1 到 10 级以外的玩家

4.LIKE, 模糊查询, 通配符: % 匹配任意个字符, _(下划线) 匹配任意一个字符

SELECT * FROM player WHERE name LIKE '王%';	# 姓王
SELECT * FROM player WHERE name LIKE '%王%';	# 名字中包含王
SELECT * FROM player WHERE name LIKE '王_';	# 王某
SELECT * FROM player WHERE name LIKE '王__';	# 王某某
1.否定为  NOT LIKE
2.并列两个 like ： WHERE name LIKE '王%' and name LIKE '涵%';   查找'王'开头，'涵'结尾的名字
这种查询方式对 '王' ,'涵' 两个字的顺序没有要求，如果要求固定顺序，用一个 like 就可以了

5.使用 REGEXP 来匹配(正则表达式)

^ 匹配开头
$ 匹配结尾
. 匹配任意一个字符
* 匹配零个或多个在它前面的字符；'f*n' 匹配字符 n 前面有任意个字符 f；[0-9]*$ 结尾任意个数字，0个或多个
+ 匹配前面的字符 1 次或多次；'^[0-9]+$'，纯数字
[字符集合] 匹配字符集合中的任何一个字符；'[acdg]' 匹配 a 或 c 或 d 或 g 其中一个字符；'[a-z]' 任意一个小写字母；
<字符串> 匹配包含指定字符的文本； 'fa' 匹配包含 ‘fa’ 的文本
A|B 匹配 A 或 B
[^] 匹配不在括号中的任何字符； '[^abc]' 匹配任何不包含 a、b 或 c 的字符串
字符串{n,} 匹配前面的字符串至少 n 次；'b{2}' 匹配 2 个或更多的 b
{n,m} 匹配前面的字符串至少 n 次，至多 m 次； 'b{2,4}' 匹配最少 2 个，最多 4 个 b

SELECT * FROM player WHERE name REGEXP '王';	# 名字中包含王
SELECT * FROM player WHERE name REGEXP '^王.$'; # 王某
SELECT * FROM player WHERE name REGEXP '^王..$'; # 王某某
SELECT * FROM player WHERE name REGEXP '[王张]';	# 名字中包含王或张
SELECT * FROM player WHERE name REGEXP '王|张';	# 名字中包含王或张
# 练习题
# 1) 查找邮件地址以 zhangsan 开头的玩家
SELECT * FROM player WHERE email LIKE 'zhangsan%';
SELECT * FROM player WHERE email REGEXP '^zhangsan';
# 2) 查找邮件地址以 a/b/c 开头的玩家
SELECT * FROM player WHERE email REGEXP '^[abc]';
SELECT * FROM player WHERE email REGEXP '^[a-c]';
# 3) 查找邮件地址以 net 结尾的玩家
SELECT * FROM player WHERE email LIKE '%net';
SELECT * FROM player WHERE email REGEXP 'net$';
# 5. 查找某个列的值为空(NULL)的数据
SELECT * FROM player WHERE email is null;
SELECT * FROM player WHERE email is NOT null;	# 查找email不为空的数据
SELECT * FROM player WHERE email is null OR email = '';	# null数据和空串不是一个概念

6. ORDER BY 排序

SELECT * FROM player ORDER BY level;   # 按等级升序排列
SELECT * FROM player ORDER BY level DESC;   # 按等级降序排列, descrease
SELECT * FROM player ORDER BY 5 DESC;   # 按第五列降序排列, 第五列是 level
SELECT * FROM player ORDER BY level DESC, exp; # 按等级降序, 经验升序排列
# 也可以在exp后加ASC表示升序, 不加默认就是 ASC

7.聚合函数和分组（GROUP BY）

聚合函数：对某一列进行一些计算, 并对这组数据返回一个值

# 常用聚合函数: AVG()  返回集合的平均值, SUM() 求和
# COUNT()  返回集合中的项目数, MAX() 返回最大值, MIN() 返回最小值
# group_concat() 拼接组内的字符串
SELECT COUNT(*) FROM player;	# 玩家总人数
SELECT AVG(level) FROM player;	# 所有玩家平均等级
SELECT SUM(gold) FROM player;  	# 所有玩家总金币
SELECT MAX(gold) FROM player;
SELECT MIN(gold) FROM player;


# 1.当需要对某个列的相同名字的数据进行聚合时就要使用 group by
# 2.GROUP BY, 对查询结果进行分组, 后边跟一个或多个列名, 表示按照这些列来分组
# 3.第二列一般跟一个聚合函数，来表示对同一分组内的某些数据如何处理，以id相同的数据合并为例， 假设还有price和num字段，
# sum(price * num) as total_price ，这样就是对每一条数据的price和num乘起来，然后id相同的所有再加起来
# 4.过滤组的条件用 having，紧跟在group by 语句之后
SELECT sex, COUNT(*) FROM player GROUP BY sex;
SELECT level, COUNT(level) FROM player GROUP BY level;
SELECT level, COUNT(level) FROM player GROUP BY level HAVING COUNT(level) > 4;
SELECT level, COUNT(level) FROM player GROUP BY level HAVING level > 80;
SELECT level, COUNT(level) FROM player GROUP BY level HAVING level > 80 ORDER BY COUNT(level) DESC;
SELECT level, COUNT(level) FROM player GROUP BY level HAVING level > 80 ORDER BY level DESC;
# 练习题
# 1.统计每个姓氏玩家的数量, 并将结果按降序显示, 只显示数量大于等于 5 的姓氏
SELECT SUBSTR(name, 1, 1), count(SUBSTR(name, 1, 1)) FROM player 
GROUP BY SUBSTR(name, 1, 1)				# 按SUSSTR(name, 1, 1) 的结果分组
HAVING COUNT(SUBSTR(name, 1, 1)) >= 5	# 只统计数量大于 5 的
ORDER BY COUNT(SUBSTR(name, 1, 1)) DESC	# 降序排列
LIMIT 3;				# 限制只显示3个数据, 也就是前3名.
# 关于LIMIT， 如果写成 3,3 , 就显示 4 到 6 名
# 第一个参数是偏移量，就是从第一个数据往后偏移多少个，1+偏移量就是第一个数据的序号
# 第二个参数是行数，就是往后显示多少行
# 2. 表中字段(列)有 vend_id, vend_price, 要求返回名为 cheapest_item 的字段
# 该字段包含每个供应商成本最低的产品(vend_id) , 然后从最低成本到最高成本对结果进行升序排序
select vend_id, min(prod_price) as cheapest_item
from Products
group by vend_id
order by cheapest_item;

GROUP BY 多字段（多列）

group by 多字段，两个字段分组就是要同时考虑两个列，两个列中都是一模一样的数据则分在同一个组中

WITH ROLLUP（记录总和）

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和
注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

WHERE和HAVING的对比

区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。

8.DISTINCT, 去重

SELECT DISTINCT sex FROM player;
# 重点注意：不能部分使用DISTINCT，DISTINCT关键字应用于所有列，而不仅是前置它的列。
# 例如 SELECT DISTINCT vend_id, prod_price，它会检索所有不同的 vend_id 和 prod_price 组合（检索出的结果一定是一个集合）。
# 这意味着如果有两行（或更多行）具有相同的 vend_id 和 prod_price 值，只有其中的一行会出现在查询结果中

9.AS 取别名

SELECT prod_id, prod_price, prod_price * 0.9 as sale_price FROM Products; 
# 1.打9折出售，查询的时候创建一个售价列，且值为 prod_price 的90%
# 2.假设后边还有where等句子，这个别名是可以直接使用的

10. 查询的集合操作（取并集等）

# UNION, 合并两个查询的结果, 取并集, 会自动去重
# UNION ALL, 不进行去重操作
SELECT * FROM player WHERE level BETWEEN 1 AND 3
UNION
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;

# INTERSECT, 取查询结果的交集
SELECT * FROM player WHERE level BETWEEN 1 AND 3
INTERSECT
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;

# EXCEPT, 取合并结果的差集, 差集是相对的
SELECT * FROM player WHERE level BETWEEN 1 AND 3
EXCEPT
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;
# 查询结果1相对于查询结果2的差集, 也就是等级在1到3级, 但是经验不在1到3之间的

11. WITH AS

用于创建临时表（也称为子查询结果集），并在后续的查询中引用这些临时表。
with as 等同于一次性视图，只会持续到下一个查询。在之后就不能再被引用

# 把子查询存一下，名为 t
with t as (
select * from consumer
)
select * from t

字符串常用函数

LENGTH() 函数，计算字符串的长度，其实本质是计算字符串字节数，只有中文字符（汉字，中文标点）不是一个字节的，因此可以用来检验是否含有中文字符。
CHAR_LENGTH() 返回字符串s的字符数。作用与CHARACTER_LENGTH()相同

SUBSTRING(str, pos , len) 函数，从 pos 开始截取长度为 len 的字串，pos起点为 1，而不是 0; 只设置 pos 参数的话，就是从 pos 位置开始往后截取全部的子串。

substring_index(str,delim,count)，str：要处理的字符串，delm：分隔符，count：计数

如果count是正数，那么就是从左往右数，第N个分隔符的`左边`的全部内容
str=www.wikidm.cn
substring_index(str,'.',1)
  # 结果是：www
  
substring_index(str,'.',2)
  # 结果是：www.wikidm
如果是负数，那么就是从右边开始数，第N个分隔符`右边`的所有内容，
substring_index(str,'.',-2)
  # 结果为：wikidm.cn

取`中间`的某个值
substring_index(substring_index(str,'.',-2), '.', 1);
 结果为：wikidm

CONCAT(str1,str2,…)函数，按顺序拼接字符串

UPPER(str)和UCASE(str)函数，将字符串转换为大写形式

日期常用函数

DATEDIFF(date1,date2)，返回date1和date2的相差天数（注：该函数是date1-date2,可以为负数）

TIMEDIFF(time1, time2)，返回两个时间之间的差（time1-time2），返回值是 “时：分：秒” 的格式

TIMESTAMPDIFF(type, time1, time2)，返回两个时间段之差（time2-time1），返回值类型为type，可选：frac_second 毫秒（低版本不支持，用second，再除于1000），second 秒，minute 分钟，hour 小时，day 天，week 周，month 月，quarter 季度，year 年
（注：这里的返回值都是向下取整的整数，例如6.5个月，只会返回6个月）

流程控制函数

IFNULL(x, y) 函数，判断第一个表达式是否为 NULL，如果 x 为 null，则返回 y，否则返回 x

COALESCE 函数，coalesce (expression_1, expression_2, ...,expression_n) ，依次检验，返回第一个不是 null 的值，比 ifnull 参数多点。SQL150可以用到这个

CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END

SELECT
CASE WHEN SEX = 'WOMAN' THEN '女' 
	 WHEN SEX = 'MAN' THEN '男' END  as sex
FROM
	 T_SYS_USER
GROUP BY sex # 按照自定义的sex进行分类

子查询

有的时候我们需要使用一个查询的结果作为另一个查询的条件, 这个时候就可以使用子查询了.

用 in 枚举得到的子查询

可以通过 filed 嵌套查询

# 查找等级大于平均等级的玩家
SELECT * FROM player WHERE level > (SELECT AVG(level) FROM player);
# 所有玩家的等级和平均等级的差值, AS 关键字能给列起别名, 在查询出的表中就以别名显示
SELECT level, ROUND((SELECT AVG(level) FROM player)) as average,
level - ROUND((SELECT AVG(level) FROM player)) as diff
FROM player;

# 使用子查询创建一个新表
CREATE TABLE new_player SELECT * FROM player WHERE level < 5;	
SELECT * FROM new_player;
# 使用子查询向表中插入数据
INSERT INTO new_player SELECT * FROM player WHERE level BETWEEN 6 AND 10;
# EXISTS 返回查询是否有结果, 返回值只有0 和 1两种
SELECT EXISTS 返回查询(SELECT * FROM player WHERE level > 90);

表关联（多刷点题）

用来查询多个表中的数据，关联的表之间必须有相同的字段(列, Field)，一般使用表的主键和外键来关联，分为以下几种类型 :

内连接，也叫等值连接（INNET JOIN）: 返回两个表中关联字段匹配的行，假设左边数据很多，且有重复的关联字段，右表数据比较少，查询的结果是：只要左表中关联字段在右表中有匹配的，对于左表的任意一行数据，都会显示与之匹配的右表中的行（看起来就像右表中的数据复制了很多份来匹配左表中的数据）。右表数据比左表的情况也类似，因此没有顺序问题。

左连接（LEFT JOIN）: 返回左表中所有的行，对于有匹配的数据，显示起来和内连接一样；对于没有匹配的数据，右表中对应的显示 NULL

右连接（RIGHT JOIN）同左连接相似

# 内连接查询
SELECT *   # 要查询的字段，player 和 equip 里的都可以使用
FROM player as p
INNER JOIN equip as e  # player 和 equip进行内连接
ON player.id = equip.player_id;	# ON 指定关联字段，使用这个字段关联起来

# 多表查询，效果和内连接
SELECT * 
FROM player p, equip e		# 指定别名, 下边代码可以直接使用
WHERE p.id = e.player_id;	  # 用 WHERE 指定关联字段

# 左连接查询   右连接改为RIGHT即可
SELECT * 
FROM player
LEFT JOIN equip
ON player.id = equip.player_id;

如果连接没有指定条件或者条件不正确, 就会产生笛卡尔积, 表连接的本质就是笛卡尔积再加上条件过滤。

如果要查询的列是公共列，必须指明是哪个表的列，因为两边都有
随便指明 a 或 b 都无所谓，但是必须做这个指明操作
select a.id from a, b where a.id = b.id; 或者 select b.id from a, b where a.id = b.id;

实例：

# 1（多表查询）. 表OrderItems:order_num(有重复的),item_price,quantity; 表Orders:order_num,cust_id;
# 题目：返回顾客 ID（Orders 表中的 cust_id）
# 并使用子查询返回total_ordered 以便返回每个顾客的订单总数，将结果按金额从大到小排序
select cust_id, total_ordered   #total_ordered是tot表的列名
from Orders, (
    select order_num, sum(item_price * quantity) as total_ordered
    from OrderItems
    group by order_num
    ) as tot  # 给子查询得到的表起别名，这个表中有两个列（字段）
where Orders.order_num = tot.order_num
order by total_ordered desc;

# 2.SQL 97
# 3.SQL 100

约束

根据约束起的作用，约束可分为：
NOT NULL 非空约束，规定某个字段不能为空
UNIQUE 唯一约束，规定某个字段在整个表中是唯一的，可多个列组合
PRIMARY KEY 主键(非空且唯一)约束
FOREIGN KEY 外键约束
CHECK 检查约束
DEFAULT 默认值约束

注意： MySQL8.0以下不支持check约束，但可以使用check约束，而没有任何效果

约束从作用上可以分为两类：
（1）列级约束：是对某一特定列的约束
（2）表级约束：用于对多个列一起的约束

列级约束有六种：主键Primary key、外键foreign key 、唯一 unique、检查 checck 、默认default 、非空/空值 not null/ null
表级约束有四种：主键、外键、唯一、检查

表级约束和列级约束的区别：
（1）列级约束：只能应用于一列上；表级约束：可以应用于一列上，也可以应用在一个表中的多个列上。

（2）列级约束：包含在列定义中，直接跟在该列的其它定义之后，用空格分隔，不必指定列名；表级约束：与列定义相互独立，不包含在列定义中，与定义用 ‘,’分隔，必须指出要约束的列的名称

表级约束（多列组合）基本语法格式为：[CONSTRAINT <约束名>] <约束类型> (字段名)，中括号括起来的意思是可以省略
约束名：约束不指定名称时，系统会给定一个名称。

查看某个表已有的约束

#information_schema数据库名（系统库）
#table_constraints表名称（专门存储各个表的约束）
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称'; # 这里的 '' 不要删掉，表明一定要放到引号里

`NOT NULL` 非空约束，规定某个字段不能为空

CREATE TABLE student(
	sid int,
	sname varchar(20) not null,
	tel char(11) ,
	cardid char(18) not null
);

# 建表后
alter table 表名称 modify 字段名 数据类型 not null; # 如果之前有default需要指明

# 删除约束
alter table 表名称 modify 字段名 数据类型;  # 啥约束也不指明，就都没了
alter table 表名称 modify 字段名 数据类型 NULL; # 改成null当然也就没有 not null 约束啦~

`UNIQUE` 唯一键，可多个列组合，有索引

用来限制某个字段/某列的值不能重复

同一个表可以有多个唯一约束。
唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
唯一性约束允许列值为空。
在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
MySQL会给唯一约束的列上默认创建一个唯一索引。

CREATE TABLE t_course(
	cid INT UNIQUE,
	cname VARCHAR(100) UNIQUE,
	description VARCHAR(200)
);

CREATE TABLE USER(
	id INT NOT NULL,
	NAME VARCHAR(25),
	PASSWORD VARCHAR(16),
	# 使用表级约束语法，不给约束起名可以省略constraint
	CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)  # 表示用户名和密码组合不能重复
);

# 建表后，对key进行修改
alter table 表名称 add unique key(字段列表)   # 复合唯一约束，注意用的 add
alter table 表名称 modify 字段名 字段类型 unique;

删除唯一约束

添加唯一性约束的列上也会自动创建唯一索引。
删除唯一约束只能通过删除唯一索引的方式删除。
删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名'; #查看都有哪
些约束
show index from 表名称; #查看表的索引
ALTER TABLE USER DROP INDEX uk_name_pwd; #用唯一索引名删除唯一约束

`PRIMARY KEY` 主键(非空且唯一)约束，可多列组合，有主键索引

用来唯一标识表中的一行记录

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值

关键字：PRIMARY KEY，不能省略key

一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。但可以有多个 unique not null 的列
主键约束对应着表中的一列或者多列（复合主键）
如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。
需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的
值，就有可能会破坏数据的完整性。

create table tmp(
	id int primary key,  # 列级约束
	name varchar(20)
);

create table tmp(
	id int,
	name varchar(20)
	primary key(id, name) # 表级约束，省略约束名的写法
);


# 建表后
alter table 表名 add primary key(字段1,字段2,...); # 添加多列的主键约束，注意用的 add
alter table 表名称 add primary key(字段名); #单列主键
alter table 表名称 modify 字段名 字段类型 primary key; #单列主键

create table tmp(
	id int,
	name varchar(20)
);
alter table tmp add primary key(id);        #单列主键
insert into tmp values(1, 'a1'), (2, 'a2')

create table tmp(
	id int,
	name varchar(20)
);
alter table tmp add primary key(id, name); #多列主键
insert into tmp values(1, 'a1'), (1, 'a2')


# 删除主键（没什么用，一般也不删除主键
alter table 表名称 drop primary key;
# 说明：删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空约束还存在

`FOREIGN KEY` 外键约束（待更

限定某个表的某个字段的引用完整性

关键字：FOREIGN KEY，不能省略key

主表和从表/父表和子表

主表（父表）：被引用的表，被参考的表
从表（子表）：引用别人的表，参考别人的表

从表的外键列，必须引用 / 参考主表的主键或唯一约束的列，因为被依赖 / 被参考的值必须是唯一的
在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如student_ibfk_1;），也可以指定外键约束名。
创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
删表时，先删从表（或先删除外键约束），再删除主表
当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
在“从表”中指定外键约束，并且一个表可以建立多个外键约束
从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can’t createtable’database.tablename’(errno: 150)”。例如：都是表示部门编号，都是int类型。
当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是列名，不是外键的约束名。（根据外键查询效率很高）
删除外键约束后，必须手动删除对应的索引

create table dept( #主表
	did int primary key, #部门编号
	dname varchar(50) #部门名称
);

create table emp(#从表
	eid int primary key, #员工编号
	ename varchar(5), #员工姓名
	deptid int, #员工所在的部门
# [CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段名) references 主表名(被参考字段名)
	foreign key (deptid) references dept(did) #在从表中指定外键约束
	#emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样
);

# 说明：
#（1）主表dept必须先创建成功，然后才能创建emp表，指定外键成功。
#（2）删除表时，先删除从表emp，再删除主表dept


# 建表后
# ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段名) REFERENCES 主表名(被引用字段名) [on update xx][on delete xx];
create table dept(
	did int primary key, #部门编号
	dname varchar(50) #部门名称
);
create table emp(
	eid int primary key, #员工编号
	ename varchar(5), #员工姓名
	deptid int #员工所在的部门
);
#这两个表创建时，没有指定外键的话，那么创建顺序是随意
alter table emp add foreign key (deptid) references dept(did);


insert into dept values(1001,'教学部');
insert into dept values(1002, '财务部');
insert into dept values(1003, '咨询部');
insert into emp values(1,'张三',1001); #在添加这条记录时，要求部门表有1001部门
insert into emp values(2,'李四',1001);
insert into emp values(3,'王五',1002);

总结：约束关系是针对双方的

添加了外键约束后，主表的修改和删除数据受约束
添加了外键约束后，从表的添加和修改数据受约束
在从表上建立外键，要求主表必须存在
删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除

约束等级

Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
No action方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
Restrict方式：同no action，都是立即检查外键约束
Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别

如果没有指定等级，就相当于Restrict方式。

对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式

删除外键约束

# 1.第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称'; # 这里的 '' 不要删掉，表明一定要放到引号里
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
# 实例
SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';
alter table emp drop foreign key emp_ibfk_1; # 外键约束名

# 2.第二步查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM 表名称;   #查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;
#实例
show index from emp;
alter table emp drop index deptid; # 对应的索引名

面试

1、为什么建表时，加 not null default ‘’ 或 default 0
不想让表中出现null值。

2、为什么不想要 null 的值
（1）不好比较。null是一种特殊值，比较时只能用专门的is null 和 is not null来比较。碰到运算符，通常返回null。
（2）效率不高。影响提高索引效果。因此，我们往往在建表时 not null default ‘’ 或 default 0

3、带AUTO_INCREMENT约束的字段值是从1开始的吗？
在MySQL中，默认AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。设置自增属性（AUTO_INCREMENT）的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一条记录，同时指定id值为5，则以后插入的记录的id值就会从6开始往上增加。添加主键约束时，往往需要设置字段自动增加属性。

4、并不是每个表都可以任意选择存储引擎？外键约束（FOREIGN KEY）不能跨引擎使用。
MySQL支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。

`CHECK` 检查约束

检查某个字段的值是否符合 xx 要求，一般指的是值的范围

create table employee(
	eid int primary key,
	ename varchar(10),
	gender char check(gender in ('男', '女'))
);
insert into employee values(1, '张三', '男')


create table temp(
	id int auto_increment,
	name varchar(20),
	age int check(age > 20),
	primary key(id)
);
insert into temp values(null, '李四', 21)

`DEFAULT` 默认值约束

给某个字段/列指定默认值，一旦设定默认值，在插入数据时，如果此段没有显式赋值，则赋值为默认值。
默认值约束一般不在唯一键和主键列上加（不能多个默认值，只能有一个值）

create table employee(
	eid int primary key,
	ename varchar(20) not null,
	gender char default '男',
	tel char(11) not null default ''   # 默认是空串
);


# 建表后
alter table 表名 modify 字段名 数据类型 default 默认值; # 这样改完约束只剩下 default 了
alter table 表名 modify 字段名 数据类型 default 默认值 not null; # 如果之前有not null，需要在这指明继续保留not null
# 当然，修改为not null时也一样，如果之前有default，不指明default的话也会被清除


# 删除默认约束
alter table 表名 modify 字段名 数据类型; # 删除默认值约束，也不保留非空约束
alter table 表名 modify 字段名 数据类型 not null; # 删除默认值约束，保留非空约束

`AUTO_INCREMENT` 自增约束

一个表最多只能有一个自增长列
当需要产生唯一标识符或顺序值时，可设置自增长
自增长列约束的列必须是键列（主键列，唯一键列）
自增约束的列的数据类型必须是整数类型
如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

create table employee(
	eid int primary key auto_increment,
	ename varchar(20)
);

# 建表后
create table employee(
	eid int primary key ,
	ename varchar(20)
);
alter table employee modify eid int auto_increment; # primary key 并不会消失
insert into employee values(null, '张三'), (null, '李四'), (null, '王五')


# 删除自增约束
alter table 表名称 modify 字段名 数据类型;

索引

当数据非常大的时候, 遍历寻找数据的方法效率太低, 索引应运而生

索引的分类

MySQL 的索引可以分为以下几类：

普通索引和唯一索引

普通索引是 MySQL 中的基本索引类型，允许在定义索引的列中插入重复值和空值。
唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

单列索引和组合索引

单列索引即一个索引只包含单个列，一个表可以有多个单列索引。
组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。

全文索引

全文索引类型为 fulltext，在定义索引的列上支持值的全文查找，允许在这些列上插入重复值和空值。全文索引可在 char、varchar或者 text 类型的列上创建。
MySQL 中只有 MyISAM 存储引擎支持全文索引。

空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL 中的空间数据类型有 4 种：geometry、point、linestring、polygon。MySQL 使用 spatial 关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为 not null。
空间索引只能在存储引擎为 MyISAM 的表中创建。

# 建表时创建索引
CREATE TABLE table_name (
    eid INT NOT NULL,
    ename VARCHAR (16) NOT NULL,
    INDEX index_id (eid)
);
show index from t_emp; # 查看索引名称


# 建表后，再创建索引
#      唯一索引   全文索引   空间索引   中括号里的表示可选的项
CREATE [UNIIQUE][FULLTEXT][SPATIAL] INDEX index_name ON 表名(字段名)...	# 创建索引，不指明可选项就是普通索引
DROP INDEX 索引名 ON 表名;	# 删除索引，只需给出索引名和表名即可
# fast 和 slow 两张数量庞大的表
CREATE INDEX email_index ON fast(email);	# 对email创建索引
DROP INDEX email_index ON fast;	# 删除索引，只需给出索引名和表名即可
SHOW INDEX FROM fast;	# 查询索引
SELECT * FROM slow WHERE email LIKE 'abcd%' ORDER BY id;
SELECT * FROM fast WHERE email LIKE 'abcd%' ORDER BY id;

# 修改表结构时创建索引
ALTER TABLE fast ADD INDEX name_index (name);
# 还可以在创建表的时候创建索引

视图

视图是一种虚拟表，它本身并不包含数据，占用很少的内存空间，而是作为一个查询语句，保存在数据字典中，当我们查询视图的时候，它会根据查询语句的定义，来动态地生成数据。
视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。
向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句
当我们创建好一张视图之后，还可以在它的基础上继续创建视图。

创建视图

CREATE [OR REPLACE]  # 类似if exists
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] # 算法
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]

精简

CREATE VIEW 视图名称
AS 查询语句

在创建视图时，没有在视图名后面指定字段列表，则视图中字段列表默认和SELECT语句中的字段列表一致。如果SELECT语句中给字段取了别名，那么视图中的字段名和别名相同。

查看视图

# 查看数据库的表对象、视图对象
SHOW TABLES;

# 查看视图的结构
DESC / DESCRIBE 视图名称;

# 查看视图的属性信息
# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'

# 查看视图的详细定义信息
SHOW CREATE VIEW 视图名称;

更新视图

MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然。

不可更新的视图

要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一 的关系。另外当视图定义出现如下情况时，视图不支持更新操作：

在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；
视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；
在定义视图的SELECT语句中使用了 JOIN联合查询 ，视图将不支持INSERT和DELETE操作；
在定义视图的SELECT语句后的字段列表中使用了 数学表达式 或 子查询 ，视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值；
在定义视图的SELECT语句后的字段列表中使用 DISTINCT 、 聚合函数 、 GROUP BY 、 HAVING 、UNION 等，视图将不支持INSERT、UPDATE、DELETE；
在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持INSERT、UPDATE、DELETE；
视图定义基于一个 不可更新视图 ；
常量视图。

修改视图

方式1：使用CREATE OR REPLACE VIEW 子句修改视图

CREATE OR REPLACE VIEW empvu80(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;

方式2：ALTER VIEW

ALTER VIEW 视图名称
AS 查询语句

删除视图

删除视图只是删除视图的定义，并不会删除基表的数据。

DROP VIEW IF EXISTS 视图名称;
DROP VIEW IF EXISTS 视图名称1, 视图名称2, 视图名称3,...;

说明：基于视图 a、b 创建了新的视图 c，如果将视图 a 或者视图 b 删除，会导致视图c 的查询失败。这样的视图 c 需要手动删除或修改，否则影响使用。

预处理

【数据库优化-预处理】MySQL预处理技术

__Rain

关注

3
点赞
踩
1

收藏

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

视图是一种虚拟存在的表, 它本身并不包含数据, 而是作为一个查询语句, 保存在数据字典中, 当我们查询视图的时候, 它会根据查询语句的定义, 来动态地生成数据。2)配置文件的监听地址问题, 默认 MySQL 服务只监听本地 IP 地址, 外部是无法连接 MySQL 服务的, 把配置文件的。数据大类型: 数值, 日期, 时间类型, 字符串类型, JSON 类型, 空间类型。有的时候我们需要使用一个查询的结果作为另一个查询的条件, 这个时候就可以使用子查询了.
复制链接

扫一扫