【MySQL】MySQL 数据库操作与设计

博主：👍不许代码码上红
欢迎：🐋点赞、收藏、关注、评论。
格言： 大鹏一日同风起，扶摇直上九万里。

文章目录

• • • • 数据库相关概念
      • 数据模型
      • 通用语法及分类
      • DDL语句
      • DML语句
      • DQL语句
      • DCL语句

数据库相关概念

数据库(DB)：存储数据的仓库，按照数据结构组织、存储和管理数据。

数据库管理系统(DBMS)：管理数据库的软件系统，如MySQL、Oracle等。

SQL：结构化查询语言，用于操作数据库的标准语言。

数据模型

关系型数据库(RDBMS)：

• 基于关系模型，由多张相互关联的二维表组成。
• 特点：
  • 使用表进行存储数据，表格式统一，便于维护。
  • 使用SQL语言操作，标准统一，使用方便。

通用语法及分类

通用语法：

• SQL语句可单行或多行书写，以分号结尾。
• 可使用空格或缩进增强可读性。
• MySQL不区分大小写，但建议关键字大写。
• 注释：
  • 单行注释：-- 注释内容 或 # 注释内容（MySQL特有）。
  • 多行注释：/* 注释内容 */。

分类：

• DDL：数据定义语言(Data Definition Language)，用于定义数据库对象（数据库、表、字段）。
• DML：数据操作语言(Data Manipulation Language)，用于增删改查数据。
• DQL：数据查询语言(Data Query Language)，用于查询数据。
• DCL：数据控制语言(Data Control Language)，用于管理用户和权限。

DDL语句

查询数据库：

SHOW DATABASES;
SELECT DATABASE();

创建数据库：

CREATE DATABASE [IF NOT EXISTS] 数据库名 
[DEFAULT CHARSET 字符集] [COLLATE 排序规则];

删除数据库：

DROP DATABASE [IF EXISTS] 数据库名;

使用数据库：

USE 数据库名;

查询表：

SHOW TABLES;
DESC 表名;
SHOW CREATE TABLE 表名;

创建表：

CREATE TABLE 表名(字段1 字段1类型 [COMMENT 字段1注释],字段2 字段2类型 [COMMENT 字段2注释],...
)[COMMENT 表注释];

数据类型：

• 数值类型：TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL。
• 字符串类型：CHAR、VARCHAR、TINYBLOB、TINYTEXT。
• 日期时间类型：DATE、TIME、YEAR、DATETIME、TIMESTAMP。

修改表：

# 添加字段：
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
# 修改字段：
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
# 删除字段：
ALTER TABLE 表名 DROP 字段名;
# 修改表名：
ALTER TABLE 表名 RENAME TO 新表名;
# 删除表：
DROP TABLE [IF EXISTS] 表名;
TRUNCATE TABLE 表名;

DML语句

插入数据：

# 给指定字段添加数据：
INSERT INTO 表名(字段1, 字段2, ...) VALUES (值1, 值2, ...);
# 给全字段添加数据：
INSERT INTO 表名 VALUES (值1, 值2, ...);

修改数据：

UPDATE 表名 SET 字段1=值1, 字段2=值2, ... [WHERE 条件];

删除数据：

DELETE FROM 表名 [WHERE 条件];

DQL语句

基本查询：

# 查询返回多个字段：
SELECT 字段1, 字段2, ... FROM 表名;
SELECT * FROM 表名;
# 设置别名，as可省略：
SELECT 字段1 AS 别名1, 字段2 AS 别名2, ... FROM 表名;
# 去除重复记录：
SELECT DISTINCT 字段列表 FROM 表名;

条件查询：

SELECT 字段列表 FROM 表名 WHERE 条件列表;

聚合函数：

# 常见聚合函数：
SELECT COUNT(字段) FROM 表名;
SELECT MAX(字段) FROM 表名;
SELECT MIN(字段) FROM 表名;
SELECT AVG(字段) FROM 表名;
SELECT SUM(字段) FROM 表名;
# 所有的null值不参与聚合函数计算

分组查询：

SELECT 字段列表 FROM 表名 
[WHERE 条件] 
GROUP BY 分组字段名 
[HAVING 分组后过滤条件];

• where与having的不同：
  1、执行时机不同：where是分组之前进行过滤，不满足where条件，不能参与分组；而having是分组之后对结果进行过滤。
  2、判断条件不同：where不能对聚合函数进行判断，而having可以。
  3、执行顺序（where>聚合函数>having）
  4、分组之后，查询的字段一般为聚合函数和分组字段，查询其它字段毫无意义。

排序查询：

SELECT 字段列表 FROM 表名 
ORDER BY 字段1 ASC|DESC, 字段2 ASC|DESC;

• 多字段排序：先按照第一个字段排序，如果第一个字段相同在按照第二个字段排序

分页查询：

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
起始索引 = (页码 - 1) * 每页记录数;

• 分页查询是数据库的方言，不同数据库有不同的表现，MYSQL中是LIMIT
• 如果查询的是第一页数据，起始索引可以省略，直接简写为LIMIT 10

执行顺序：

• 编写顺序：SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT。
• 执行顺序：FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT。

DCL语句

用户管理：

# 查询用户：
USE mysql;
SELECT * FROM user;
# 创建用户：
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
# 修改用户：
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
#删除用户：
DROP USER '用户名'@'主机名';

权限控制：

SHOW GRANTS FOR '用户名'@'主机名';
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

常见权限：

• ALL：所有权限。
• SELECT：查询数据。
• INSERT：插入数据。
• UPDATE：修改数据。
• DELETE：删除数据。
• ALTER：修改表。
• DROP：删除数据库/表/视图。
• CREATE：创建数据库/表。