MySQl 数据库操作

发布于：2025-05-12 ⋅ 阅读:(53) ⋅ 点赞:(0)

目录

一、MySQL 数据库介绍

二、MySQl 库操作

1. 系统数据库

2. 数据库操作

（1）创建数据库

 （2）数据库命名规则

（3）选择数据库

（4）查看数据库 

（5）删除数据库

 （6）修改数据库

三、MySQL 表操作

1. 表介绍

2. 查看表

3. 创建表

4. 查看表结构

5. 修改表

（1）修改表名

（2）增加字段

（3）修改字段

（4）删除字段

6. 复制表

7. 删除表

四、MySQL 数据操作

1. 介绍

2. 插入数据

顺序插入

指定字段插入

批量插入

3. 删除数据 

4. 更新数据 

5. 查询数据 

（０）关键字执行的优先级

（１）单表查询

（２）多表查询

（３）例子

总结与最佳实践

五、MySQL 数据库用户授权

1. 创建用户

2. 授权操作

3. 查看权限

4. 撤销权限

（一）DROP 与 DELETE 核心区别​

（二）DELETE 与 TRUNCATE 对比​

一、MySQL 数据库介绍

  • MySQL 是一款开源的关系型数据库管理系统（RDBMS），由瑞典公司 MySQL AB 开发，后被 Oracle 收购。它是目前最流行的数据库之一，广泛应用于 Web 开发、企业级应用、数据分析等领域。
  • SQL 语言的组成：
    • DDL（数据定义语言）：用来建立数据库、数据库对象和定义字段。如CREATE！、ALTER、DROP。
    • DML（数据操纵语言）：用来插入、删除和修改数据库中的数据。如INSERT、DELETE、UPDATE。
    • DQL（数据查询语言）：用来查询数据库中的数据，如SELECT。
    • DCL（数据控制语言）：用来控制数据库组件的存取许可、存取权限等，如 COMMIT、ROLLBACK、GRANT、REVOKE。

二、MySQl 库操作

1. 系统数据库

MySQL 安装后默认包含以下系统数据库：

数据库名 作用
mysql 存储用户权限、角色、密码等系统级信息。
information_schema 提供数据库元数据（如表、字段、权限等）的只读视图。
performance_schema 监控 MySQL 服务器性能指标（如查询耗时、锁等待）。
sys 基于 performance_schema 的简化视图，方便用户查看性能数据。

注意：

  • 禁止直接修改系统数据库的表（如 mysql.user），需使用专用 SQL 命令（如 CREATE USER）。

  • 通过 SHOW DATABASES; 查看所有数据库。

2. 数据库操作

（1）创建数据库
-- 基础语法  
CREATE DATABASE  数据库名； 

-- 示例：创建名为 `db1` 的数据库，
 create database db1;
 （2）数据库命名规则
  • 可以由字母、数字、下划线、@、#、￥
  • 区分大小写
  • 唯一性
  • 不能使用关键字如 create select
  • 不能单独使用数字
  • 最长128位
（3）选择数据库
--语法
USE 数据库名；

--示例：切换到db1库
mysql> use db1;
Database changed
（4）查看数据库 
--查看当前数据库中有哪些表
show databases; 
--显示创建名为db1的数据库时所使用的SQL语句
show create datavase db1;
--返回当前选中的数据库的名称
select database();
（5）删除数据库
--语法
DROP DATABASE 数据库名；
--示例：删除db1库
drop database db1;
 （6）修改数据库
--语法
ALTER DATABASE 数据库名； 
--示例：修改数据库 test_db 的字符集为 utf8mb4
ALTER DATABASE test_db CHARACTER SET utf8mb4;

三、MySQL 表操作

1. 表介绍

  • 表（Table） 是关系数据库的核心组件，用于存储结构化数据。

  • 结构：由 列（字段） 和 行（记录） 组成。

  • 表的本质：数据库中存储数据的二维结构。

  • 表引擎：决定表的存储和处理方式（常用：InnoDB（支持事务、外键）、MyISAM（性能高，不支持事务））。​

  • 表约束：保证数据完整性（如主键PRIMARY KEY、非空NOT NULL、唯一UNIQUE、默认值DEFAULT）。

2. 查看表

#查看前提是先USE进入这个数据库内，或者在命令中IN指定查询的数据库
-- 查看当前数据库中的所有表  
SHOW TABLES;  

-- 查看表详细信息（存储引擎、字符集等）  
SHOW TABLE STATUS LIKE '表名';

3. 创建表

-- 基础语法  
CREATE TABLE [IF NOT EXISTS] 表名 (  
    列名1 数据类型 [约束],  
    列名2 数据类型 [约束],  
    ...  
    [PRIMARY KEY (列名)]  
) [ENGINE=存储引擎] [DEFAULT CHARSET=字符集];  

-- 示例：创建学生表  
CREATE TABLE IF NOT EXISTS students (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(50) NOT NULL,  
    age TINYINT UNSIGNED,  
    class_id INT,  
    FOREIGN KEY (class_id) REFERENCES classes(id)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

注意：

  1. 在同一个表中，字段名是不能相同
  2. 宽度和约束条件可选
  3. 字段名和类型是必须的，字段的类型宽度和约束条件是可选项
  4. 表中最后一个字段不要加逗号

  • 数据类型：INT, VARCHAR, DATE, TEXT 等。

    • （一）数值类型​

      类型​

      存储大小​

      范围（有符号）​

      范围（无符号）​

      说明及示例​

      TINYINT​

      1 字节​

      -128 ~ 127​

      0 ~ 255​

      适用于枚举值（如状态码：0/1）​

      SMALLINT​

      2 字节​

      -32768 ~ 32767​

      0 ~ 65535​

      短整型数据（如年龄字段）​

      INT/INTEGER​

      4 字节​

      -2147483648 ~ 2147483647​

      0 ~ 4294967295​

      标准整数类型（主键常用）​

      BIGINT​

      8 字节​

      ±9.2e18​

      0 ~ 1.8e19​

      超大整数（如时间戳毫秒级）​

      FLOAT​

      4 字节​

      单精度浮点（7 位有效数字）​

      -​

      科学计算（需注意精度丢失）​

      DOUBLE​（M，D）

      8 字节，M表示长度，D表示小数位数​

      双精度浮点（15 位有效数字）​

      -​

      高精度计算（如金额存储）
      DECIMAL（M，D） DECIMAL（M，D） 依赖于M和D的值，M最大值为65 依赖于M和D的值，M最大值为65 小数值

      ​注意：数值类型可加 UNSIGNED 修饰符设置无符号类型，INT (M) 中的 M 表示显示宽度，需配合 ZEROFILL 使用才生效​

      ​（二）日期时间类型​

      类型​

      存储大小​

      格式​

      范围​

      特性说明​

      DATE​

      3 字节​

      YYYY-MM-DD​

      1000-01-01 ~ 9999-12-31​

      仅存储日期​

      TIME​

      3 字节​

      HH:MM:SS​

      -838:59:59 ~ 838:59:59​

      可存储时间间隔​

      YEAR​

      1 字节​

      YYYY 或 YY​

      1901 ~ 2155​

      简化年存储​

      DATETIME​

      8 字节​

      YYYY-MM-DD HH:MM:SS​

      1000-01-01 00:00:00 ~ 9999-12-31 23:59:59​

      无时区概念​

      TIMESTAMP​

      4 字节​

      同上​

      1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC​

      自动转换时区，占用空间小​

      最佳实践：

    • ​TIMESTAMP 适合记录数据变更时间（支持自动更新）​

    • DATETIME 适合存储固定时区的时间（如业务系统统一用北京时间）​一个汉字在 UTF-8 中占 3 字节，GBK 中占 2 字节​VARCHAR 的最大长度受限于表的行大小（InnoDB 默认 65535 字节

        （三）字符串类型​

类型​

存储方式​

最大长度​

存储需求​

典型应用场景​

CHAR(M)​

固定长度（不足补空格）​

M=0~255​

空间 = M 字节​

短字符串（如 MD5 哈希值）​

VARCHAR(M)​

可变长度（前缀 + 数据）​

M=0~65535​

空间 = 1/2 字节前缀 + 实际长度​

动态文本（如用户昵称）​

TEXT​

长文本存储（无字符集前缀）​

65535 字符​

按实际内容存储​

文章内容、详情描述​

BLOB​

二进制大对象​

同上​

二进制数据存储​

图片、文件二进制流​

NVARCHAR​

统一字符集存储（UTF-8）​

取决于字符集​

每个字符占 3 字节（UTF-8）​

多语言支持场景​

  • 约束条件：

    约束 作用 示例
    PRIMARY KEY 主键约束，唯一标识记录（非空且唯一） id INT PRIMARY KEY
    AUTO_INCREMENT 自增（仅限整数类型），常用于主键 id INT PRIMARY KEY AUTO_INCREMENT
    UNIQUE 唯一约束，字段值不可重复 email VARCHAR(50) UNIQUE
    NOT NULL 非空约束，字段值不能为 NULL name VARCHAR(20) NOT NULL
    DEFAULT 默认值，插入数据时未指定则使用默认值 status INT DEFAULT 0
    FOREIGN KEY 外键约束，关联其他表的主键 FOREIGN KEY (dept_id) REFERENCES department(id)

  • 存储引擎：InnoDB（支持事务）、MyISAM（高性能读）。

4. 查看表结构

##查看表字段及属性（字段名 + 类型 + 约束）
DESCRIBE 表名;  #可简写为DESCR 表名
-- 示例输出：
+------------+------------------+------+-----+---------+-------------------+
| Field      | Type             | Null | Key | Default | Extra             |
+------------+------------------+------+-----+---------+-------------------+
| id         | int              | NO   | PRI | NULL    | auto_increment    |
| name       | varchar(50)      | NO   |     | NULL    |                   |
+------------+------------------+------+-----+---------+-------------------+

#查看完整的建表信息
SHOW CREATE TABLE 表名\G;  #\G 表示以长格式显示结果

5. 修改表

（1）修改表名
--语法
ALTER TABLE 表名 RENAME 新表名；  
--示例:t1改为t2
ALTER TABLE t1 RENAME t2;
（2）增加字段
--语法
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件...],ADD 字段名 数据类型 [完整性约束条件...]

ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件...]  FIRST;

ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件...]  AFTER 字段名；

-
（3）修改字段
ALTER TABLE 表名 MODIFY 字段名	数据类型 [完整性约束条件……]；
ALTER TABLE 表名 CHANGE 旧字段名	新字段名	旧数据类型 [完整性约束条件…]；
ALTER TABLE 表名 CHANGE 旧字段名	新字段名	新数据类型 [完整性约束条件…]；

例子：alter table t2 modify name char(50);
Query OK, 0 rows affected (0.02 sec)Records:0 Duplicates:0 Warnings:0
mysql> alter table t2 change name user name varchar(50);
Query OK, 0 rows affected (0.02 sec)Records:0 Duplicates:0 Warnings:0
（4）删除字段
ALTER TABLE students DROP COLUMN email;

6. 复制表

复制表只复制表结构，不复制表中数据
mysql> create table t3 like t2;

复制表结构+记录（key不会复制：主键、外键和索引）
mysql> create table t4 select* from t2;

7. 删除表

-- 删除表（不可恢复！）  
DROP TABLE　表名;  

DROP TABLE　数据库名．表名；

四、MySQL 数据操作

1. 介绍

数据操作语言（DML）用于对表中的数据进行 增（INSERT）、删（DELETE）、改（UPDATE）、查（SELECT）。

2. 插入数据

顺序插入

语法：INSERT　INTO　表名　VALUES（值１，值２，值３．．值ｎ）

INSERT INTO students  
VALUES (1, '张三', '男', '2003-05-20', 101);
指定字段插入

语法：INSERT　INTO　表名（字段１，字段２．．字段ｎ）VALUES（值１，值２，值３．．值ｎ）

INSERT INTO students (name, class_id)  
VALUES ('李四', 102);  -- 未指定字段使用默认值或 NULL
批量插入

语法：INSERT　INTO　表名　VALUES（值１，值２，值３．．值ｎ），（值１，值２．．值ｎ）

INSERT INTO students (name, gender, class_id)  
VALUES  
    ('王五', '女', 101),  
    ('赵六', '男', 102);

3. 删除数据 

删除所有数据
DELETE FROM students;  -- 逐行删除，可回滚  
TRUNCATE TABLE students;  -- 快速清空表，不可回滚  

条件删除
DELETE FROM 表名 WHERE 条件表达式;
－－例
DELETE FROM students WHERE id = 3;

4. 更新数据 

UPDATE 表名 SET 字段1=值1, 字段2=值2, ... [WHERE 条件];
-- 更新单个字段  

－示例：
UPDATE students SET name = '张三丰' WHERE id = 1;  

-- 更新多个字段  

－示例：

UPDATE students SET gender = '女', class_id = 103 WHERE name = '李四';

5. 查询数据 

（０）关键字执行的优先级

        执行顺序：

        FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY →         LIMIT

（１）单表查询

准备数据

create database test;
use test;
create table renyuan(
id int not null unique auto_increment, #员工id
name varchar(20) not null,     #姓名
sex enum('male','female') not null default 'male', #性别，大部分是男的
age int(3) unsigned not null default 28,  #年龄
hire_date date not null,    #入职时间
post varchar(50),       #岗位
post_comment varchar(100),  #职位描述
salary double(15,2),    #薪资
office int, #办公室，一个部门一个屋子
depart_id int  #部门编号
);
insert into renyuan(name,sex,age,hire_date,post,salary,office,depart_id) values
('zhangsan','male',18,'20170301','teacher',7300.33,401,1), #教学部
('lisi','male',78,'20150302','teacher',1000000.31,401,1),
('wangwu','male',81,'20130305','teacher',8300,401,1),
('zhaoliu','male',73,'20140701','teacher',3500,401,1),
('suqi','male',28,'20121101','teacher',2100,401,1),
('zhuba','female',18,'20110211','teacher',9000,401,1),
('洪金宝','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),
 
('歪歪','female',48,'20150311','sale',3000.13,402,2),#销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),
 
('张野','male',28,'20160311','operation',10000.13,403,3), #运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;

  • 简单查询

    查询所有字段：SELECT * FROM 表名;

查询指定字段：SELECT 字段1, 字段2 FROM 表名;

使用别名：SELECT 字段 AS 别名 FROM 表名;

去重查询：SELECT DISTINCT 字段 FROM 表名;

  • where条件

    比较运算符：=, <>, >, <, >=, <=

逻辑运算符：AND, OR, NOT

BETWEEN：WHERE 字段 BETWEEN 值1 AND 值2

IN：WHERE 字段 IN (值1, 值2)

LIKE：WHERE 字段 LIKE '模式' (%匹配任意字符，_匹配单个字符)

IS NULL：WHERE 字段 IS NULL

  • group　ｂｙ　分组 

    将结果集按一个或多个列分组：

SELECT 分组字段, 聚合函数(字段)
FROM 表名
GROUP BY 分组字段;

常用聚合函数：

COUNT()：计数, SUM()：总和, AVG()：平均值, MAX()：最大值, MIN()：最小值

  • order　ｂｙ　排序（对结果集排序）

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

    • ASC：升序（默认）

    • DESC：降序

  • limit　限制结果条目

    限制返回的记录数：
SELECT 字段 FROM 表名 LIMIT 数量;
SELECT 字段 FROM 表名 LIMIT 偏移量, 数量;

  • 正则匹配

    SELECT 字段 FROM 表名 WHERE 字段 REGEXP '正则表达式';

    常用正则符号：

    • ^：匹配开头

    • $：匹配结尾

    • .：匹配任意单个字符

    • [字符集]：匹配字符集中的任意一个字符

    • *：匹配前面的子表达式零次或多次

（２）多表查询

准备数据

create database test2;
use test2;
create table bumen(
id int,  
name varchar(20) 
);
create table renyuan(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int  #部门ID
);
insert into bumen values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into renyuan(name,sex,age,dep_id) values
('zhangsan','male',18,200),
('lisi','female',48,201),
('wangwu','male',38,201),
('zhaoliu','female',28,202),
('sunqi','male',18,200),
('zhuba','female',18,204)
;

  • 子查询

    • 定义：在一个SQL查询语句中嵌套另一个查询语句

    • 语法：＜表达式＞［ＮＯＴ］ＩＮ　＜子查询＞

    • 特点：

      • 可以出现在SELECT、FROM、WHERE等子句中

      • 通常用括号括起来

      • 可以作为表达式、条件或数据源使用

        －－示例
＃查询平均年龄在２５岁以上的部门名
mysql> select id, name from bumen where id in (select dep id from renyuan group by dep id having avg(age) >25);

#查看技术部员工姓名
mysql> select name from renyuan where dep id in (select id from bumen where name=’技术’)；

#查看不足1人的部门名（子查询得到的是有人的部门 id）
mysql> select name from bumen where id not in (select distinct dep id from renyuan);

  • 多表连接查询

    • 内连接（只连接匹配的行）

      • 特点：

        • 只返回两个表中匹配的行

        • 不匹配的行不会出现在结果中

        • 是最常用的连接类型

    • 左连接（优先显示左表全部记录）

      • 特点：

        • 返回左表的所有行，即使右表中没有匹配

        • 右表无匹配时显示NULL值

        • 优先显示左表全部记录

    • 右连接（优先显示右表全部记录）

      •  特点：

        • 优先显示右表全部记录

        • 左表无匹配时显示NULL值

        • 返回右表的所有行，即使左表中没有匹配

（３）例子
基础查询

-- 查询所有字段  
SELECT * FROM students;  

-- 查询指定字段  
SELECT name, gender FROM students;  

-- 去重查询  
SELECT DISTINCT class_id FROM students;  
条件查询

-- 比较运算符（=, !=, >, <, >=, <=）  
SELECT * FROM students WHERE class_id > 100;  

-- 逻辑运算符（AND, OR, NOT）  
SELECT * FROM students  
WHERE gender = '女' AND class_id = 101;  

-- IN 运算符  
SELECT * FROM students WHERE class_id IN (101, 102);  

-- BETWEEN 范围查询  
SELECT * FROM students  
WHERE birth_date BETWEEN '2000-01-01' AND '2005-12-31';  

-- LIKE 模糊查询  
SELECT * FROM students WHERE name LIKE '张%';  -- 以“张”开头  
排序与分页

-- 按生日升序排序（ASC 可省略）  
SELECT * FROM students ORDER BY birth_date ASC;  

-- 按班级降序、姓名升序排序  
SELECT * FROM students  
ORDER BY class_id DESC, name;  

-- 分页查询（LIMIT 偏移量, 数量）  
SELECT * FROM students LIMIT 5;       -- 前 5 条  
SELECT * FROM students LIMIT 5, 10;   -- 第 6~15 条

总结与最佳实践

操作类型 核心语法 注意事项
创建表 CREATE TABLE ... 合理选择数据类型，设置主键和约束
插入数据 INSERT INTO ... VALUES ... 批量插入提升效率，避免频繁单条插入
删除数据 DELETE FROM ... WHERE ... 先备份再操作，使用事务保证数据安全
更新数据 UPDATE ... SET ... WHERE ... 明确 WHERE 条件，防止误更新全表
查询数据 SELECT ... FROM ... WHERE ... ORDER BY ... 避免 SELECT *，按需选择字段；索引

五、MySQL 数据库用户授权

1. 创建用户

-- 基本语法
CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';
 
-- 示例
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password123';
CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'securepass';  -- %表示任意主机

2. 授权操作

-- 基本语法
GRANT 权限类型 ON 数据库.表 TO '用户名'@'主机';
 
-- 常用权限类型
ALL PRIVILEGES -- 所有权限
SELECT, INSERT, UPDATE, DELETE -- 特定操作权限
CREATE, DROP, ALTER -- 数据库结构权限
 
-- 示例
GRANT ALL PRIVILEGES ON mydb.* TO 'user1'@'localhost';
GRANT SELECT, INSERT ON mydb.employees TO 'user2'@'%';
GRANT CREATE, DROP ON *.* TO 'admin'@'localhost';

3. 查看权限

-- 查看当前用户权限
SHOW GRANTS;
 
-- 查看特定用户权限
SHOW GRANTS FOR '用户名'@'主机';
 
-- 示例
SHOW GRANTS FOR 'user1'@'localhost';

4. 撤销权限

-- 基本语法
REVOKE 权限类型 ON 数据库.表 FROM '用户名'@'主机';
 
-- 示例
REVOKE INSERT ON mydb.* FROM 'user1'@'localhost';
REVOKE ALL PRIVILEGES ON *.* FROM 'admin'@'localhost';
 
-- 删除用户
DROP USER '用户名'@'主机';
（一）DROP 与 DELETE 核心区别​

特性​

DROP​

DELETE​

操作对象​

删除整张表（包括表结构、数据、索引）​

删除表中的数据（保留表结构）​

语法格式​

DROP TABLE [IF EXISTS] 表名；​

DELETE FROM 表名 [WHERE 条件];​

日志记录​

不记录事务日志（DDL 操作）​

记录事务日志（DML 操作）​

执行性能​

极快（直接删除物理文件）​

较慢（逐行删除，需维护事务日志）​

事务支持​

不可回滚​

可回滚（未提交事务时）​

索引处理​

同时删除所有索引​

仅删除数据，索引结构保留​

空间释放​

完全释放表空间​

空间标记为可重用，不立即释放​

使用场景​

永久删除无用表​

按条件删除数据或清空表（带事务需求）​
（二）DELETE 与 TRUNCATE 对比​
  1. 功能差异​
  • DELETE：支持 WHERE 条件过滤，清空数据表；delete记录日志，可以实现恢复
  • TRUNCATE：仅能清空全表数据；重置自增主键计数器（从 1 开始）​
  1. 技术特性​

TypeScript

取消自动换行复制

-- DELETE示例（带条件删除）​

DELETE FROM users WHERE create_time < '2023-01-01';​

​

-- TRUNCATE示例（快速清空表）​

TRUNCATE TABLE users;​

​

  • 事务支持：DELETE 在 InnoDB 中受事务控制，TRUNCATE 会隐式提交事务​
  • 锁机制：DELETE 使用行锁（InnoDB），TRUNCATE 使用表锁​
  • 触发器：DELETE 会触发 AFTER DELETE 触发器，TRUNCATE 不触发

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布