【技术专题】Mysql8 数据库 - Mysql8 数据库表基本操作 & 查询数据

小锋java1234
0 阅读11分钟

大家好，我是锋哥。最近连载更新Mysql8，数据库开发技术专题。

mysql8-cover-16x9.jpg

本课程主要介绍和讲解 Mysql8简介，安装以及配置，客户端sqlyog安装以及配置，数据库基本操作，数据库表基本操作，查询数据，添加，修改，删除数据，索引，视图，触发器，常用函数，存储过程和函数。同时也配套视频教程 《一天学会 Mysql8 数据库 视频教程》

Mysql8 数据库表基本操作

1，创建表

表是数据库存储数据的基本单位。一个表包含若干字段或记录。

1.1 基本语法

CREATE TABLE 表名( 
    属性名 数据类型 [完整性约束条件],
    属性名 数据类型 [完整性约束条件],
    ...
    属性名 数据类型 [完整性约束条件]
);

1.2 常见完整性约束条件

约束条件说明
PRIMARY KEY标识该属性为该表的主键，可以唯一的标识对应的记录
FOREIGN KEY标识该属性为该表的外键，与某表的主键关联
NOT NULL标识该属性不能为空
UNIQUE标识该属性的值是唯一的
AUTO_INCREMENT标识该属性的值自动增加
DEFAULT为该属性设置默认值

1.3 创建表实例

示例 1：创建图书类别表 t_bookType

CREATE TABLE t_booktype(
    id INT PRIMARY KEY AUTO_INCREMENT,
    bookTypeName VARCHAR(20),
    bookTypeDesc VARCHAR(200)
);

示例 2：创建图书表 t_book（包含外键关联）

CREATE TABLE t_book(
    id INT PRIMARY KEY AUTO_INCREMENT,
    bookName VARCHAR(20),
    author VARCHAR(10),
    price DECIMAL(6,2),
    bookTypeId INT,
    CONSTRAINT `fk` FOREIGN KEY (`bookTypeId`) REFERENCES `t_bookType` (`id`)
);

2，查看表结构

  1. 查看基本表结构：

    DESCRIBE(DESC) 表名;

    示例：DESC t_book;

  2. 查看表详细结构：

    SHOW CREATE TABLE 表名;

    示例：SHOW CREATE TABLE t_book;

3，修改表

  1. 修改表名

    ALTER TABLE 旧表名 RENAME 新表名;

  2. 修改字段

    ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型;

  3. 增加字段

    ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST|AFTER 属性名2];

    注：FIRST 用于将新字段添加到第一位，AFTER 属性名2 用于将其添加到指定字段之后。

  4. 删除字段

    ALTER TABLE 表名 DROP 属性名;

4，删除表

  1. 删除表

    DROP TABLE 表名;

    示例：DROP TABLE t_book;

Mysql8 查询数据

首先初始化下数据：

-- 插入图书类别
INSERT INTO t_booktype (id, bookTypeName, bookTypeDesc) VALUES 
(1, '软件工程类', '涵盖软件开发、项目管理、软件测试等'),
(2, '计算机网络类', '涵盖网络协议、网络安全、网络架构等'),
(3, '计算机人工智能', '涵盖机器学习、深度学习、自然语言处理等');
​
-- 插入图书数据
INSERT INTO t_book (id, bookName, author, price, bookTypeId) VALUES 
(1, '代码大全', '史蒂夫·迈克康奈尔', 128.00, 1),
(2, '人月神话', '弗雷德里克·布鲁克斯', 59.00, 1),
(3, '计算机网络：自顶向下方法', '詹姆斯·库罗斯', 89.00, 2),
(4, 'TCP/IP详解', 'W.理查德·史蒂文斯', 108.00, 2),
(5, '人工智能：一种现代的方法', '斯图尔特·罗素', 168.00, 3),
(6, '机器学习', '周志华', 88.00, 3);

1， 单表查询

1.1 查询所有字段

  • SELECT 字段1, 字段2, 字段3... FROM 表名;

  • SELECT * FROM 表名;

  • 实例：查询所有图书信息

    SELECT * FROM t_book;

1.2 查询指定字段

  • SELECT 字段1, 字段2, 字段3... FROM 表名;

  • 实例：查询所有图书的书名和作者

    SELECT bookName, author FROM t_book;

1.3 Where 条件查询

  • SELECT 字段1, 字段2, 字段3... FROM 表名 WHERE 条件表达式;

  • 实例：查询价格大于50元的图书

    SELECT * FROM t_book WHERE price > 50;

1.4 带 IN 关键字查询

  • SELECT 字段1, 字段2, 字段3... FROM 表名 WHERE 字段 [NOT] IN (元素1, 元素2, 元素3);

  • 实例：查询类别为1或2的图书

    SELECT * FROM t_book WHERE bookTypeId IN (1, 2);

1.5 带 BETWEEN AND 的范围查询

  • SELECT 字段1, 字段2, 字段3... FROM 表名 WHERE 字段 [NOT] BETWEEN 取值1 AND 取值2;

  • 实例：查询价格在40到80元之间的图书

    SELECT * FROM t_book WHERE price BETWEEN 40 AND 80;

1.6 带 LIKE 的模糊查询

  • SELECT 字段1, 字段2, 字段3... FROM 表名 WHERE 字段 [NOT] LIKE '字符串';

  • % 代表任意字符；_ 代表单个字符；

  • 实例：查询书名中包含“Java”的图书

    SELECT * FROM t_book WHERE bookName LIKE '%Java%';

1.7 空值查询

  • SELECT 字段1, 字段2, 字段3... FROM 表名 WHERE 字段 IS [NOT] NULL;

  • 实例：查询没有填写描述信息的图书类别

    SELECT * FROM t_bookType WHERE bookTypeDesc IS NULL;

1.8 带 AND 的多条件查询

  • SELECT 字段1, 字段2... FROM 表名 WHERE 条件表达式1 AND 条件表达式2 [...AND 条件表达式n]

  • 实例：查询类别为1且价格大于50元的图书

    SELECT * FROM t_book WHERE bookTypeId = 1 AND price > 50;

1.9 带 OR 的多条件查询

  • SELECT 字段1, 字段2... FROM 表名 WHERE 条件表达式1 OR 条件表达式2 [...OR 条件表达式n]

  • 实例：查询类别为1或者价格低于40元的图书

    SELECT * FROM t_book WHERE bookTypeId = 1 OR price < 40;

1.10 DISTINCT 去重复查询

  • SELECT DISTINCT 字段名 FROM 表名;

  • 实例：查询当前图书涉及了哪些不同的类别ID

    SELECT DISTINCT bookTypeId FROM t_book;

1.11 对查询结果排序

  • SELECT 字段1, 字段2... FROM 表名 ORDER BY 属性名 [ASC|DESC]

  • 实例：按价格降序排列图书信息

    SELECT * FROM t_book ORDER BY price DESC;

1.12 GROUP BY 分组查询

  • 基本语法：GROUP BY 属性名 [HAVING 条件表达式][WITH ROLLUP]

  • 使用场景说明：

    1. 单独使用（毫无意义）；
    2. 与 GROUP_CONCAT() 函数一起使用；
    3. 与聚合函数一起使用；
    4. 与 HAVING 一起使用（限制输出的结果）；
    5. 与 WITH ROLLUP 一起使用（最后加入一个总和行）；

  • 实例：统计每个类别的图书数量

    SELECT bookTypeId, COUNT(*) AS bookCount FROM t_book GROUP BY bookTypeId;

1.13 LIMIT 分页查询

  • SELECT 字段1, 字段2... FROM 表名 LIMIT 初始位置, 记录数;

  • 实例：查询前3条图书数据（从第0条开始，查3条）

    SELECT * FROM t_book LIMIT 0, 3;

2，使用聚合函数查询

2.1 COUNT() 函数

  • 用来统计记录的条数，通常与 GROUP BY 一起使用。

  • 实例：统计图书总数

    SELECT COUNT(*) FROM t_book;

2.2 SUM() 函数

  • 求和函数，与 GROUP BY 一起使用。

  • 实例：统计所有图书的总价格

    SELECT SUM(price) FROM t_book;

2.3 AVG() 函数

  • 求平均值的函数，与 GROUP BY 一起使用。

  • 实例：求所有图书的平均价格

    SELECT AVG(price) FROM t_book;

2.4 MAX() 函数

  • 求最大值的函数，与 GROUP BY 一起使用。

  • 实例：查询最贵的图书价格

    SELECT MAX(price) FROM t_book;

2.5 MIN() 函数

  • 求最小值的函数，与 GROUP BY 一起使用。

  • 实例：查询最便宜的图书价格

    SELECT MIN(price) FROM t_book;

3，连接查询

连接查询是将两个或两个以上的表按照某个条件连接起来，从中选取需要的数据。

3.1 内连接查询

  • 内连接查询是一种最常用的连接查询，可以查询两个或者两个以上的表。

  • 实例：查询图书名称及其对应的类别名称

    SELECT b.bookName, t.bookTypeName 
FROM t_book b 
INNER JOIN t_bookType t ON b.bookTypeId = t.id;

3.2 外连接查询

  • 外连接可以查出某一张表的所有信息。
  • 基本语法： SELECT 属性名列表 FROM 表名1 LEFT|RIGHT JOIN 表名2 ON 表名1.属性名1=表名2.属性名2;
3.2.1 左连接查询

  • 可以查询出“表名1”的所有记录，而“表名2”中，只能查询出匹配的记录。

  • 实例：以图书表为主，显示所有图书及其类别（即使某些图书类别ID有误查不到类别）

    SELECT b.bookName, t.bookTypeName 
FROM t_book b 
LEFT JOIN t_bookType t ON b.bookTypeId = t.id;
3.2.2 右连接查询

  • 可以查询出“表名2”的所有记录，而“表名1”中，只能查询出匹配的记录。

  • 实例：以类别表为主，显示所有类别及类别下的图书（即使某些类别下没有图书）

    SELECT b.bookName, t.bookTypeName 
FROM t_book b 
RIGHT JOIN t_bookType t ON b.bookTypeId = t.id;

3.3 多条件连接查询

  • 根据多个条件连接查询表数据。

  • 实例：查询“计算机”类别下价格大于60的图书

    SELECT b.bookName, t.bookTypeName, b.price 
FROM t_book b 
JOIN t_bookType t ON b.bookTypeId = t.id 
WHERE t.bookTypeName = '计算机' AND b.price > 60;

4，子查询

4.1 带 In 关键字的子查询

  • 一个查询语句的条件可能落在另一个 SELECT 语句的查询结果中。

  • 实例：查询“计算机”类别的所有图书（不借助连接查询）

    SELECT * FROM t_book 
WHERE bookTypeId IN (SELECT id FROM t_bookType WHERE bookTypeName = '计算机');

4.2 带比较运算符的子查询

  • 子查询可以使用比较运算符。

  • 实例：查询价格高于平均价格的图书

    SELECT * FROM t_book 
WHERE price > (SELECT AVG(price) FROM t_book);

4.3 带 Exists 关键字的子查询

  • 假如子查询查询到记录，则进行外层查询，否则，不执行外层查询。

  • 实例：查询有图书的类别信息

    SELECT * FROM t_bookType t 
WHERE EXISTS (SELECT 1 FROM t_book b WHERE b.bookTypeId = t.id);

4.4 带 Any 关键字的子查询

  • ANY 关键字表示满足其中任一条件。

  • 实例：查询价格大于任意一本“文学”类图书价格的书籍（比最便宜的文学书贵即可）

    SELECT * FROM t_book 
WHERE price > ANY (SELECT price FROM t_book WHERE bookTypeId = 2);

4.5 带 All 关键字的子查询

  • ALL 关键字表示满足所有条件。

  • 实例：查询价格大于所有“文学”类图书价格的书籍（比最贵的文学书还要贵）

    SELECT * FROM t_book 
WHERE price > ALL (SELECT price FROM t_book WHERE bookTypeId = 2);

5，合并查询结果

5.1 UNION

  • 使用 UNION 关键字时，数据库系统会将所有的查询结果合并到一起，然后去掉相同的记录。

  • 实例：查询价格大于80或类别为2的图书（去重）

    SELECT * FROM t_book WHERE price > 80
UNION
SELECT * FROM t_book WHERE bookTypeId = 2;

5.2 UNION ALL

  • 使用 UNION ALL，不会去掉相同的记录。

  • 实例：合并查询，保留重复记录

    SELECT * FROM t_book WHERE price > 80
UNION ALL
SELECT * FROM t_book WHERE bookTypeId = 2;

6，为表和字段取别名

6.1 为表取别名

  • 格式：表名 表的别名

  • 实例：赋予 t_book 别名为 b，t_bookType 别名为 t

    SELECT b.bookName, t.bookTypeName 
FROM t_book b, t_bookType t 
WHERE b.bookTypeId = t.id;

6.2 为字段取别名

  • 格式：属性名 [AS] 别名

  • 实例：在查询结果中规范化列名

    SELECT bookName AS '书名', author AS '作者', price AS '价格' FROM t_book;

Mysql8 添加，更新，删除数据

1，插入数据 (INSERT)

1.1 给表的所有字段插入数据

格式： INSERT INTO 表名 VALUES(值1, 值2, 值3, ..., 值n);

示例： （由于主键 id 设置了 AUTO_INCREMENT，全字段插入时通常使用 NULL 或 DEFAULT 占位，由数据库自动生成）

INSERT INTO t_bookType VALUES(NULL, '计算机科学', '计算机编程与理论相关书籍');

1.2 给表的指定字段插入数据

格式： INSERT INTO 表名(属性1, 属性2, ..., 属性n) VALUES(值1, 值2, 值3, ..., 值n);

示例：

INSERT INTO t_bookType(bookTypeName, bookTypeDesc) 
VALUES('文学小说', '经典文学作品与当代小说');

1.3 同时插入多条记录

格式：

INSERT INTO 表名 [(属性列表)] 
VALUES(取值列表1), 
      (取值列表2),
      ...,
      (取值列表n);

示例：

INSERT INTO t_book(bookName, author, price, bookTypeId) 
VALUES 
('MySQL基础教程', '张三', 59.90, 1),
('Java编程思想', '李四', 108.00, 1);

💡 提示（外键约束） ：向 t_book 插入数据时，bookTypeId 的值必须在 t_bookType 表中已存在，否则会触发外键约束报错。

2，更新数据 (UPDATE)

格式：

UPDATE 表名
SET 属性名1=取值1, 属性名2=取值2,
    ...,
    属性名n=取值n
WHERE 条件表达式;

2.1 更新特定记录（单列）

UPDATE t_book 
SET price = 65.00 
WHERE bookName = 'MySQL基础教程';

2.2 更新特定记录（多列）

UPDATE t_book 
SET price = 50.00, author = '余华' 
WHERE id = 3;

⚠️ 警告：UPDATE 语句必须带有 WHERE 条件表达式，否则将更新表中所有记录的对应字段！ 💡 提示：如果更新的是外键列（如 bookTypeId），更新后的值同样必须在父表 t_bookType 中存在。

3 删除数据 (DELETE)

格式：

DELETE FROM 表名 [WHERE 条件表达式];

3.1 删除特定记录

DELETE FROM t_book 
WHERE id = 2;

3.2 删除所有记录（清空表）

DELETE FROM t_book;

💡 提示：使用 DELETE FROM 表名; 清空表后，自增主键（AUTO_INCREMENT）的计数器不会重置。如果需要重置自增计数器且清空表，可以使用 TRUNCATE TABLE 表名;。

3.3 带有外键约束的删除注意事项

由于 t_book 表通过外键 fk 依赖于 t_bookType 表，如果直接删除父表（t_bookType）中已被子表引用的数据，MySQL 会拒绝执行并报错。

错误示例： （假设 id=1 的类别已被图书引用）

DELETE FROM t_bookType WHERE id = 1; -- 报错：Cannot delete or update a parent row: a foreign key constraint fails

正确做法： 必须先删除子表（t_book）中依赖该类别数据的记录，然后再删除父表（t_bookType）中的记录。

-- 1. 先删除对应的图书
DELETE FROM t_book WHERE bookTypeId = 1;
-- 2. 再删除图书类别
DELETE FROM t_bookType WHERE id = 1;
avatar
文章
阅读
粉丝