mysql知识点总结

一.基础

1.1.终端连接数据库

# 方式一：
mysql -uroot -pxxxxxx
# 方式二：
mysql -uroot -p
Enter password: your password

1.2.SQL语句分类

• DDL（Data Definition Language）：数据定义语言
  • 可以通过DDL语句对数据库或者表进行：创建、删除、修改等操作
• DML（Data Manipulation Language）：数据操作语言
  • 可以通过DML语句对表进行：添加、删除、修改等操作
• DQL（Data Query Language）：数据查询语言
  • 可以通过DQL从数据库中查询记录；（重点）
• DCL（Data Control Language）：数据控制语言
  • 对数据库、表格的权限进行相关访问控制操作；

1.3.数据库操作

MySQL默认的数据库

• infomation_schema：信息数据库，其中包括MySQL在维护的其他数据库、表、列、访问 权限等信息
• performance_schema：性能数据库，记录着MySQL Server数据库引擎在运行过程中的一 些资源消耗相关的信息
• mysql：用于存储数据库管理者的用户信息、权限信息以及一些日志信息等
• sys：相当于是一个简易版的performance_schema，将性能数据库中的数据汇总成更容易 理解的形式

数据库操作命令

# 显示数据库
SHOW DATABASES;

# 创建数据库
CREATE DATABASE test;
CREATE DATABASE IF NOT EXISTS test;
CREATE DATABASE IF NOT EXISTS test
			 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
			 
# 删除数据库
DROP DATABASE test;
DROP DATABASE IF EXIT test;

# 修改数据库的字符集和排序规则
ALTER DATABASE test CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

# 使用数据库
use test;

# 查看当前正在使用的数据库
SELECT DATABASE();

1.4.数据表操作

1.4.1.数据类型

MySQL支持的数据类型有：数字类型，日期和时间类型，字符串（字符和字节）类型，空间类型和 JSON数据类型。

数字类型，参考(https://dev.mysql.com/doc/refman/8.0/en/integer-types.html)

• 整数数字类型：TINYINT（1B），SMALLINT（2B），MEDIUMINT（3B），INTEGER，INT（4B），BIGINT（8B）
• 浮点数字类型：FLOAT，DOUBLE（FLOAT是4个字节，DOUBLE是8个字节）
• 精确数字类型：DECIMAL，NUMERIC（DECIMAL是NUMERIC的实现形式）

日期类型

• YEAR以YYYY格式显示值

  • 范围 1901到2155，和 0000

• DATE类型用于具有日期部分但没有时间部分的值

  • DATE以格式YYYY-MM-DD显示值
  • 支持的范围是 ‘1000-01-01’ 到 ‘9999-12-31’

• DATETIME类型用于包含日期和时间部分的值

  • DATETIME以格式’YYYY-MM-DD hh:mm:ss’显示值
  • 支持的范围是1000-01-01 00:00:00到9999-12-31 23:59:59

• TIMESTAMP数据类型被用于同时包含日期和时间部分的值

  • TIMESTAMP以格式’YYYY-MM-DD hh:mm:ss’显示值

  • 但是它的范围是UTC的时间范围：’1970-01-01 00:00:01’到’2038-01-19 03:14:07’;

• 另外：DATETIME或TIMESTAMP 值可以包括在高达微秒（6位）精度的后小数秒一部分（了解）

  • 比如DATETIME表示的范围可以是’1000-01-01 00:00:00.000000’到’9999-12-31 23:59:59.999999’;

字符串类型

• CHAR类型在创建表时为固定长度，长度可以是0到255之间的任何值

  • 在被查询时，会删除后面的空格

• VARCHAR类型的值是可变长度的字符串，长度可以指定为0到65535之间的值

  • 在被查询时，不会删除后面的空格

• BINARY和VARBINARY 类型用于存储二进制字符串，存储的是字节字符串

  • https://dev.mysql.com/doc/refman/8.0/en/binary-varbinary.html

• BLOB用于存储大的二进制类型

• TEXT用于存储大的字符串类型

1.4.2.表约束

主键：PRIMARY KEY

• 一张表中，为了区分每一条记录的唯一性，必须有一个字段是永远不会重复，并且不会为空的，这个字段通常会将它设置为主键
• 主键是表中唯一的索引
• 并且必须是NOT NULL的，如果没有设置 NOT NULL，那么MySQL也会隐式的设置为NOT NULL
• 主键也可以是多列索引，PRIMARY KEY(key_part, ...)，一般称之为联合主键
• 建议：开发中主键字段应该是和业务无关的，尽量不要使用业务字段来作为主键

唯一：UNIQUE

• 某些字段在开发中希望是唯一的，不会重复的，比如手机号码、身份证号码等，这个字段可以使用UNIQUE来约束
• 使用UNIQUE约束的字段在表中必须是不同的
• UNIQUE 索引允许NULL包含的列具有多个值NULL

不能为空：NOT NULL

• 某些字段要求用户必须插入值，不可以为空，这个时候可以使用 NOT NULL 来约束

默认值：DEFAULT

• 某些字段希望在没有设置值时给予一个默认值，这个时候可以使用 DEFAULT来完成

自动递增：AUTO_INCREMENT

• 某些字段希望不设置值时可以进行递增，比如用户的id，这个时候可以使用AUTO_INCREMENT来完成

外键约束也是最常用的一种约束手段，详见后续多表关系

1.4.3.数据表的操作

表结构的操作

# 创建一张表
CREATE TABLE IF NOT EXISTS `users`(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  age INT DEFAULT 0,
  telPhone VARCHAR(20) DEFAULT '' UNIQUE NOT NULL
);

# 删除数据表
DROP TABLE users;
DROP TABLE IF EXISTS users;

# 1.修改表名
ALTER TABLE `moments` RENAME TO `moment`;
# 2.添加一个新的列
ALTER TABLE `moment` ADD `publishTime` DATETIME;
ALTER TABLE `moment` ADD `updateTime` DATETIME;
# 3.删除一列数据
ALTER TABLE `moment` DROP `updateTime`;
# 4.修改列的名称
ALTER TABLE `moment` CHANGE `publishTime` `publishDate` DATE;
# 5.修改列的数据类型
ALTER TABLE `moment` MODIFY `id` INT;

表数据记录的操作

#  创建一张新的表
CREATE TABLE IF NOT EXISTS `products`(
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `title` VARCHAR(20),
  `description` VARCHAR(200),
  `price` DOUBLE,
  `publishTime` DATETIME
);

# 插入数据
INSERT INTO `products` (`title`, `description`, `price`, `publishTime`) 
  VALUES ('iPhone', 'iPhone12只要998', 998.88, '2023-06-01'); 
INSERT INTO `products` (`title`, `description`, `price`, `publishTime`) 
  VALUES ('huawei', 'iPhoneP40只要888', 888.88, '2023-06-01');

# 删除数据
# 会删除表中所有的数据
DELETE FROM `products`;
# 会删除符合条件的数据
DELETE FROM `products` WHERE `title` = 'iPhone';

# 修改数据
# 会修改表中所有的数据
UPDATE `products` SET `title` = 'iPhone12', `price` = 1299.88;
# 会修改符合条件的数据
UPDATE `products` SET `title` = 'iPhone12', `price` = 1299.88 WHERE `title` = 'iPhone';

# 修改表结构，在每次修改完数据后，直接可以显示最新的更新时间
ALTER TABLE `products` ADD `updateTime` TIMESTAMP 
  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 

1.5.DML语句

DQL：Data Query Language（数据查询语言）

• SELECT用于从一个或者多个表中检索选中的行（Record）

查询格式

数据表结构

CREATE TABLE IF NOT EXISTS `products` (
  id INT PRIMARY KEY AUTO_INCREMENT,
  brand VARCHAR(20),
  title VARCHAR(100) NOT NULL,
  price DOUBLE NOT NULL,
  score DECIMAL(2,1),
  voteCnt INT,
  url VARCHAR(100),
  pid INT
);

基本查询

# 查询所有的数据并且显示所有的字段
SELECT * FROM `products`;

# 查询title、brand、price
SELECT title, brand, price FROM `products`;

# 给字段起别名：别名一般在多张表或者给客户端返回对应的key时会使用到
SELECT title as t, brand as b, price as p FROM `products`;

WHERE条件查询（比较运算符）

# 查询价格小于1000的手机
SELECT * FROM `products` WHERE price < 1000;
# 查询价格大于等于2000的手机
SELECT * FROM `products` WHERE price >= 2000;
# 价格等于3399的手机
SELECT * FROM `products` WHERE price = 3399;
# 价格不等于3399的手机
SELECT * FROM `products` WHERE price != 3399;
# 查询华为品牌的手机
SELECT * FROM `products` WHERE `brand` = '华为';

WHERE条件查询（逻辑运算符）

# 查询品牌是华为，并且小于2000元的手机
SELECT * FROM `products` WHERE `brand` = '华为' and `price` < 2000;
SELECT * FROM `products` WHERE `brand` = '华为' && `price` < 2000;
# 查询1000到2000的手机（不包含1000和2000）
SELECT * FROM `products` WHERE price > 1000 and price < 2000;
# OR: 符合一个条件即可
# 查询所有的华为手机或者价格小于1000的手机
SELECT * FROM `products` WHERE brand = '华为' or price < 1000;
# 查询1000到2000的手机（包含1000和2000）
SELECT * FROM `products` WHERE price BETWEEN 1000 and 2000;
# 查看多个结果中的一个
SELECT * FROM `products` WHERE brand in ('华为', '小米');

WHERE条件查询（模糊查询）

• 模糊查询使用LIKE关键字，结合两个特殊的符号
  • %表示匹配任意个的任意字符
  • _表示匹配一个的任意字符

# 查询所有以v开头的title
SELECT * FROM `products` WHERE title LIKE 'v%';
# 查询带M的title
SELECT * FROM `products` WHERE title LIKE '%M%';
# 查询带M的title必须是第三个字符
SELECT * FROM `products` WHERE title LIKE '__M%';

查询结果排序

• 当查询到结果的时候，希望将结果按照某种方式进行排序，这个时候使用的是ORDER BY
• RDER BY有两个常用的值
  • ASC：升序排列
  • DESC：降序排列

SELECT * FROM `products` WHERE brand = '华为' or price < 1000 ORDER BY price ASC;

分页查询

• 数据库中的数据非常多时，一次性查询到所有的结果进行显示是不太现实的
• 在真实开发中，都会要求用户传入offset、limit或者page等字段
• 目的是可以在数据库中进行分页查询
• 用法有[LIMIT {[offset,] row_count | row_count OFFSET offset}]

SELECT * FROM `products` LIMIT 30 OFFSET 0;
SELECT * FROM `products` LIMIT 30 OFFSET 30;
SELECT * FROM `products` LIMIT 30 OFFSET 60;
# 另外一种写法：offset, row_count
SELECT * FROM `products` LIMIT 90, 30;

二.进阶

products表结构，在本章节案例使用

CREATE TABLE IF NOT EXISTS `products` (
  id INT PRIMARY KEY AUTO_INCREMENT,
  brand VARCHAR(20),
  title VARCHAR(100) NOT NULL,
  price DOUBLE NOT NULL,
  score DECIMAL(2,1),
  voteCnt INT,
  url VARCHAR(100),
  pid INT
);

2.1.MySQL聚合函数

聚合函数表示对值的集合进行操作的组（集合）函数

# 华为手机价格的平均值
SELECT AVG(price) FROM `products` WHERE brand = '华为';
# 计算所有手机的平均分
SELECT AVG(score) FROM `products`;
# 手机中最低和最高分数
SELECT MAX(score) FROM `products`;
SELECT MIN(score) FROM `products`;
# 计算总投票人数
SELECT SUM(voteCnt) FROM `products`;
# 计算所有条目的数量
SELECT COUNT(*) FROM `products`;
# 华为手机的个数
SELECT COUNT(*) FROM `products` WHERE brand = '华为';

Group By

• 事实上聚合函数相当于默认将所有的数据分成了一组
• 前面使用avg还是max等，都是将所有的结果看成一组来计算的
• 如果希望划分多个组：比如华为、苹果、小米等手机分别的平均价格，应该怎么来做呢？
  • 这个时候可以使用 GROUP BY
  • GROUP BY通常和聚合函数一起使用：表示先对数据进行分组，再对每一组数据，进行聚合函数的计算

# 需求
# 1.根据品牌进行分组
# 2.计算各个品牌中：商品的个数、平均价格
# 3.也包括：最高价格、最低价格、平均评分

SELECT brand, 
  COUNT(*) as count, 
  ROUND(AVG(price), 2) as avgPrice,
  MAX(price) as maxPrice,
  MIN(price) as minPrice,
  AVG(score) as avgScore
FROM `products` GROUP BY brand;

Group By的约束条件

如果希望给Group By查询到的结果添加一些约束，那么可以使用：HAVING。

# 希望筛选出平均价格在4000以下，并且平均分在7以上的品牌
SELECT brand, 
  COUNT(*) as count, 
  ROUND(AVG(price), 2) as avgPrice,
  MAX(price) as maxPrice,
  MIN(price) as minPrice,
  AVG(score) as avgScore
FROM `products` GROUP BY brand 
HAVING avgPrice < 4000 and avgScore > 7;

2.2.MySQL的外键约束

• 假如在上面的商品表中，对应的品牌还需要包含其他的信息
  • 比如品牌的官网，品牌的世界排名，品牌的市值等等
• 如果直接在商品中去体现品牌相关的信息，会存在一些问题
  • 一方面，products表中应该表示的都是商品相关的数据，应该又另外一张表来表示brand的数据
  • 另一方面，多个商品使用的品牌是一致时，会存在大量的冗余数据
• 所以，可以将所有的品牌数据，单独放到一张表中，创建一张品牌的表

# 创建brand表
CREATE TABLE IF NOT EXISTS `brand`(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  website VARCHAR(100),
  worldRank INT
);

# 添加数据
INSERT INTO `brand` (name, website, worldRank) VALUES ('华为', 'www.huawei.com', 1);
INSERT INTO `brand` (name, website, worldRank) VALUES ('小米', 'www.mi.com', 10);
INSERT INTO `brand` (name, website, worldRank) VALUES ('苹果', 'www.apple.com', 5);
INSERT INTO `brand` (name, website, worldRank) VALUES ('oppo', 'www.oppo.com', 15);

将两张表联系起来，可以将products中的brand_id关联到brand中的id

# 如果是创建表添加外键约束，需要在创建表的()最后添加如下语句
FOREIGN KEY (brand_id) REFERENCES brand(id)

# 如果是表已经创建好，额外添加外键
ALTER TABLE `products` ADD `brand_id` INT;
ALTER TABLE `products` ADD FOREIGN KEY (brand_id) REFERENCES brand(id);

将products中的brand_id关联到brand中的id的值

# 注意：在brans表中，华为的id为1，小米的id为2，苹果的id为3，oppo的id为4
UPDATE `products` SET `brand_id` = 1 WHERE `brand` = '华为';
UPDATE `products` SET `brand_id` = 4 WHERE `brand` = 'OPPO';
UPDATE `products` SET `brand_id` = 3 WHERE `brand` = '苹果';
UPDATE `products` SET `brand_id` = 2 WHERE `brand` = '小米';

修改或者删除brand表的的id，让products表中的bread_id同步更新

• 如果希望products的brand_id同步更新，需要修改on delete或者on update的值

• on delete或者on update的取值如下：

  • RESTRICT（默认属性）：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话会报错的，不允许更新或 删除

  • NO ACTION：与RESTRICT是一致的，是在SQL标准中定义的

  • CASCADE：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话：

    • 更新：那么会更新对应的记录
    • 删除：那么关联的记录会被一起删除掉

  • SET NULL：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话，将对应的值设置为NULL

# 可以查看到创建表时的sql语句，找到外键的名称，products_ibfk_1
SHOW CREATE TABLE `products`;

# 删除外键约束
ALTER TABLE `products` DROP FOREIGN KEY products_ibfk_1;

# 重新添加外键，并且设置在on delete或者on update的值为CASCADE，同步更新
ALTER TABLE `products` ADD FOREIGN KEY (brand_id) REFERENCES brand(id) 
  ON UPDATE CASCADE
  ON DELETE CASCADE

2.3.MySQL的多表查询

默认多表查询的结果

SELECT * FROM `products`, `brand`

• 假如第一张表有108条数据， 第二张表有6条数据，那么一共查询到的数据记录个数为108 * 6
• 第一张表中每一个条数据，都会和第二张表中的每一条数据结合一次
• 这个结果称之为笛卡尔乘积，也称之为直积，表示为 X*Y

where条件查询

• 表示查询到笛卡尔乘积后的结果中，符合products.brand_id = brand.id条件的数据过滤出来
• 如果有的在products表中，但在brand表中没有与之相关联的数据，那么那些数据也无法展示出来

SELECT * FROM `products`, `brand` WHERE `products`.brand_id = `brand`.id;

2.4.表和表间的连接方式

事实上想要的效果并不是上述那样的，而且表中的某些特定的数据，这个时候可以使用 SQL JOIN 操作，连接方式有以下四种。

左连接

• 如果希望获取到的是左边所有的数据（以左表为主）
  • 这个时候就表示无论左边的表是否有对应的brand_id的值对应右边表的id，左边的数据都会被查询出来
  • 这个也是开发中使用最多的情况，它的完整写法是LEFT [OUTER] JOIN，但是OUTER可以省略的

SELECT * FROM `products` LEFT JOIN `brand` ON `products`.brand_id = `brand`.id;

SELECT * FROM `products` LEFT JOIN `brand` ON `products`.brand_id = `brand`.id
  WHERE brand.id IS NULL;

右连接

• 如果希望获取到的是右边所有的数据（以由表为主）：
  • 这个时候就表示无论左边的表中的brand_id是否有和右边表中的id对应，右边的数据都会被查询出来
  • 右连接在开发中没有左连接常用，它的完整写法是RIGHT [OUTER] JOIN，但是OUTER可以省略的

SELECT * FROM `products` RIGHT JOIN `brand` ON `products`.brand_id = `brand`.id;

SELECT * FROM `products` RIGHT JOIN `brand` ON `products`.brand_id = `brand`.id
  WHERE products.id IS NULL

内连接

• 事实上内连接是表示左边的表和右边的表都有对应的数据关联
  • 内连接在开发中偶尔也会有一些场景使用，看自己的场景
  • 内连接有其他的写法：CROSS JOIN或者 JOIN都可以

SELECT * FROM `products` INNER JOIN `brand` ON `products`.brand_id = `brand`.id;

SELECT * FROM `products`, `brand` WHERE `products`.brand_id = `brand`.id

• 会发现他们不同写法实现的效果是一样的
• 但是他们代表的含义并不相同
  • SQL语句一：内连接，代表的是在两张表连接时就会约束数据之间的关系，来决定之后查询的结果
  • SQL语句二：where条件，代表的是先计算出笛卡尔乘积，在笛卡尔乘积的数据基础之上进行where条件的帅选

全连接

• SQL规范中全连接是使用FULL JOIN，但是MySQL中并没有对它的支持，我们需要使用 UNION 来实现

(SELECT * FROM `products` LEFT JOIN `brand` ON `products`.brand_id = `brand`.id)
UNION
(SELECT * FROM `products` RIGHT JOIN `brand` ON `products`.brand_id = `brand`.id);

(SELECT * FROM `products` LEFT JOIN `brand` ON `products`.brand_id = `brand`.id WHERE `brand`.id IS NULL)
UNION
(SELECT * FROM `products` RIGHT JOIN `brand` ON `products`.brand_id = `brand`.id WHERE `products`.id IS NULL);

2.5.多对多数据查询语句

多对多的关系的案例

• 比如学生可以选择多门课程，一个课程可以被多个学生选择

建立两张表

# 创建学生表
CREATE TABLE IF NOT EXISTS `students`(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  age INT
);

# 创建课程表
CREATE TABLE IF NOT EXISTS `courses`(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  price DOUBLE NOT NULL
);

INSERT INTO `students` (name, age) VALUES('jack', 18);
INSERT INTO `students` (name, age) VALUES('tom', 22);
INSERT INTO `students` (name, age) VALUES('lilei', 25);
INSERT INTO `students` (name, age) VALUES('lucy', 16);
INSERT INTO `students` (name, age) VALUES('lily', 20);

INSERT INTO `courses` (name, price) VALUES ('英语', 100);
INSERT INTO `courses` (name, price) VALUES ('语文', 666);
INSERT INTO `courses` (name, price) VALUES ('数学', 888);
INSERT INTO `courses` (name, price) VALUES ('历史', 80);

创建关系表

# 创建关系表
CREATE TABLE IF NOT EXISTS `students_select_courses`(
  id INT PRIMARY KEY AUTO_INCREMENT,
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE CASCADE
);

# jack选修了 英文和数学
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 1);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 3);
# lilei选修了 语文和数学和历史
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 2);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 3);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 4);

查询多对多数据

# 查询所有的选课学生选择的所有课程
SELECT 
  stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
JOIN `students_select_courses` ssc
  ON stu.id = ssc.student_id
JOIN `courses` cs 
  ON ssc.course_id = cs.id; 
  
# 查询所有的学生选课情况（包括没有选课的学生）
SELECT 
  stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
LEFT JOIN `students_select_courses` ssc
  ON stu.id = ssc.student_id
LEFT JOIN `courses` cs 
  ON ssc.course_id = cs.id; 

# jack同学选择了哪些课程
SELECT 
  stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
JOIN `students_select_courses` ssc
  ON stu.id = ssc.student_id
JOIN `courses` cs 
  ON ssc.course_id = cs.id
WHERE stu.id = 1;

# lily同学选择了哪些课程(注意，这里必须用左连接，事实上上面也应该使用的是左连接)
SELECT 
  stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
LEFT JOIN `students_select_courses` ssc
  ON stu.id = ssc.student_id
LEFT JOIN `courses` cs 
  ON ssc.course_id = cs.id
WHERE stu.id = 5;

# 哪些学生是没有选课的
SELECT 
  stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
LEFT JOIN `students_select_courses` ssc
  ON stu.id = ssc.student_id
LEFT JOIN `courses` cs
  ON ssc.course_id = cs.id
WHERE cs.id IS NULL;

# 查询哪些课程没有被学生选择
SELECT 
  stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
RIGHT JOIN `students_select_courses` ssc
  ON stu.id = ssc.student_id
RIGHT JOIN `courses` cs
  ON ssc.course_id = cs.id
WHERE stu.id IS NULL;