Mysql数据库操作

目录

数据库操作

一、系统数据库

二、 基本数据库操作

 表操作

MySQL数据操作

一、MySQL部署与连接命令

1. 服务管理（Linux系统）

2. 连接数据库

3. 初始化操作

二、数据表操作

1. 创建与删除

2. 修改表结构

三、数据增删改查（CRUD）

1. 插入数据

2. 查询数据

3. 更新数据

4. 删除数据

四、高级查询操作

1. 多表关联查询

2. 分组与过滤

五、实用管理命令

六、关键注意事项

MySQL数据库用户授权

二、用户管理与授权操作命令

1. 用户创建与删除

2. 权限授予（GRANT）

3. 权限回收（REVOKE）

4. 权限查看

三、进阶授权策略

1. 角色管理（MySQL 8.0+）

2. 权限继承（WITH GRANT OPTION）

3. 有效期控制（MySQL 8.0+）

四、安全部署规范

1. 初始化安全配置

2. 权限审计命令

3. 密码策略强化

五、权限生效与调试

1. 手动刷新权限

2. 连接测试命令

3. 错误日志检查

---

数据库操作

一、系统数据库

    经过初始化后的MySQL服务器，会自动创建四个数据库，分别为：sys、mysql、information_schema和performance_schema。

    以下为各数据库的作用：

• information_schema:虚拟库，不占磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等；
• perfomance_schema:从Mysql 5.5开始便新增了这个数据库，主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象；
• mysql:授权表，主要存储系统用户的权限信息；
• sys:主要用于存储系统性能信息和监控数据，对数据库的性能优化和故障排除具有关键作用；

二、 基本数据库操作

1、创建数据库

语法：mysql>create database 数据库名;

2、数据库命名规则

• 可以由字母、数字、下划线、@、#、$
• 区分大小写
• 唯一性
• 不能使用关键字（如：create、select）
• 不能单独使用数字
• 最长128位 

3、选择数据库 

语法：use 数据库名;

4、查看数据库

mysql>show databases;    //查看当前数据库有那些表
mysql>show create database 数据库名;    //显示创建的数据库时所使用的SQL语句
mysql>select database();    //返回当前选中的数据库的名称

5、删除数据库

语法：mysql>drop database 数据库名； 

 表操作

1、查看表

语法：mysql>use mysql;

           Database changed

           mysql>show tables;

2、创建表

语法： mysql>create table 表名（

字段名1 类型[(宽度) 约束条件]，

字段名2 类型[(宽度) 约束条件]，

字段名3 类型[(宽度) 约束条件]

）；

注意事项：

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

    Mysql支持多种数据类型，大致分为三类：数值、日期/时间、字符串（字符）类型。这可以让我们对于约束数据类型有很好的帮助。以下表格是各数据类型的详细介绍：

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
INT	4字节	（-2 147 483 648，2 147 483 647）	（0， 4 294 967 295 ）	大整数值
DOUBLE	8字节	（-1.797E+308，-222E-308）	（0，2.22E-308，1.797E+308）	双精度浮点数值
DOUBLE(M,D)	8字节，M 表示长度，D表示小数位数	同上，受M和D的约束DOUBLE(5,2)-999.99-999.99	同上，受M和D的约束	双精度浮点数值
DECIMAL(M,D)	DECIMAL(M,D)	依赖于M和D的值，M最大值为65	依赖于M和D的值，M最大值为65	小数值

日期类型

类型	大小	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	随缘吧，也没多少人感兴趣吧。	YYYYMMDDHHMMSS	混合日期和时间值，时间值

字符串类型

类型	大小	用途
CHAR	0-255字节	定长字符串char(10) 10个字符
VARCHAR	0-65535字节	变长字符串varchar(10) 10个字符
BLOB(binary large object)	0-65535字节	二进制形式的长文本数据
TEXT	0-65535字节	长文本数据

约束条件

     约束条件与数据类型的宽度一样，都是可选参数，类似于使用execl存储数据时，可以利用execl的公式限制员工ID列：禁止重复值，且不能为空。

    约束条件作用：用于保证数据的完整性和一致性，详细如下：

约束条件	说明
PRIMARY KEY (PK)	标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK)	标识该字段为该表的外键
NOT NULL	标识该字段不能为空
UNIQUE KEY (UK)	标识该字段的值是唯一的
AUTO_INCREMENT	标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT	为该字段设置默认值
UNSIGNED	无符号
ZEROFILL	使用0填充
ENMU ( )	限制字段可以存储的值集合

3、创建表

语法：mysql>create database 数据库名；

           mysql>use 数据库名；

           mysql>create table 表名（相关参数）；

           mysql>show tables;

4、查看表结构

    DESCRIBE 语句:用于显示表的结构，即组成表的各字段(列)的信息。需要指定“数据库名.表名”作为参数;若只指定表名参数，则需先通过 USE 语句切换到目标数据库。

语法：mysql describe 表名；        //describe可简写为desc

     查看详细表结构或者创建表所使用的语句可以执行 show create table tl\G;#查看表详细结构，可加\G。\G表示以长格式展示结果。

 5、修改表

（1）修改表名

语法：mysql>alter table 表名 rename 新表名；

（2）增加字段

语法：

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

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

 （3）删除字段

语法：mysql>alter table 表名 drop 字段名；

 （4）修改字段

语法：

ALTER TABLE 表名 MODIFY 字段名 数据类型[完整性约束条件…];
ALTER TABLE表名CHANGE旧字段名 新字段名 旧数据类型[完整性约束条件…];
ALTER TABLE表名 CHANGE 旧字段名 新字段名 新数据类型[完整性约束条件…];

复制表

只复制表结构，不复制表中数据

语法：mysql>create table 表名1 like 表名2；

复制表结构+记录 （key不会复制：主键、外键和索引）

语法：create table 表名1 select * from 表名2；

删除表

语法：mysql>drop table 表名； 

MySQL数据操作

一、MySQL部署与连接命令

1. 服务管理（Linux系统）

# 启动MySQL服务 
sudo systemctl start mysql # :ml-citation{ref="6" data="citationList"} 
# 停止MySQL服务 
sudo systemctl stop mysql # :ml-citation{ref="6" data="citationList"} 
# 查看服务状态 
sudo systemctl status mysql # :ml-citation{ref="6" data="citationList"} 

2. 连接数据库

# 本地连接（需输入密码） 
mysql -u root -p # :ml-citation{ref="4,7" data="citationList"} 

# 远程连接（指定IP和端口） 
mysql -h 192.168.1.100 -P 3306 -u username -p # :ml-citation{ref="7" data="citationList"} 

3. 初始化操作

-- 创建数据库 
CREATE DATABASE mydb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; # :ml-citation{ref="5,8" data="citationList"} 
-- 选择数据库 
USE mydb; # :ml-citation{ref="5,8" data="citationList"} 

---

二、数据表操作

1. 创建与删除

-- 建表（包含字段定义） 
CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(50) NOT NULL,
 email VARCHAR(100) UNIQUE,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
); # :ml-citation{ref="4,5" data="citationList"} 

-- 删除表 
DROP TABLE users; # :ml-citation{ref="5" data="citationList"} 

2. 修改表结构

-- 添加字段 
ALTER TABLE users ADD age INT AFTER name; # :ml-citation{ref="5,8" data="citationList"} 
-- 修改字段类型 
ALTER TABLE users MODIFY email VARCHAR(150); # :ml-citation{ref="5" data="citationList"} 
-- 删除字段 
ALTER TABLE users DROP COLUMN age; # :ml-citation{ref="5" data="citationList"} 

---

三、数据增删改查（CRUD）

1. 插入数据

-- 插入单条数据 
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'); # :ml-citation{ref="5,7" data="citationList"} 
-- 批量插入 
INSERT INTO users (name, email) VALUES 
('Bob', 'bob@test.com'), 
('Charlie', 'charlie@test.com'); # :ml-citation{ref="7" data="citationList"} 

2. 查询数据

-- 基础查询 
SELECT * FROM users WHERE name LIKE 'A%'; # :ml-citation{ref="5,7" data="citationList"} 
-- 排序与分页 
SELECT id, name FROM users ORDER BY created_at DESC LIMIT 10; # :ml-citation{ref="1,5" data="citationList"} 
-- 聚合统计 
SELECT COUNT(*) AS total_users FROM users; # :ml-citation{ref="5,7" data="citationList"} 

3. 更新数据

-- 单字段更新 
UPDATE users SET email = 'alice_new@example.com' WHERE id = 1; # :ml-citation{ref="5,7" data="citationList"} 
-- 多字段更新 
UPDATE users SET name = 'Bob Smith', age = 30 WHERE id = 2; # :ml-citation{ref="5" data="citationList"} 

4. 删除数据

-- 条件删除 
DELETE FROM users WHERE id = 3; # :ml-citation{ref="5,7" data="citationList"} 
-- 清空表 
TRUNCATE TABLE users; # :ml-citation{ref="5" data="citationList"} 

---

四、高级查询操作

1. 多表关联查询

-- 内连接查询 
SELECT u.name, o.order_id 
FROM users u 
INNER JOIN orders o ON u.id = o.user_id; # :ml-citation{ref="1,7" data="citationList"} 

-- 左外连接 
SELECT u.name, o.order_id 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id; # :ml-citation{ref="1" data="citationList"} 

2. 分组与过滤

-- 分组统计 
SELECT department, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 5000; # :ml-citation{ref="1,5" data="citationList"} 

---

五、实用管理命令

-- 查看所有数据库 
SHOW DATABASES; # :ml-citation{ref="5,6" data="citationList"} 
-- 查看表结构 
DESCRIBE users; # :ml-citation{ref="5" data="citationList"} 
-- 查看数据库版本 
SELECT VERSION(); # :ml-citation{ref="6" data="citationList"} 

---

六、关键注意事项

权限控制

• 使用GRANT语句分配用户权限，避免直接使用root账户操作业务数据
• 示例：GRANT SELECT, INSERT ON mydb.* TO 'user1'@'localhost' IDENTIFIED BY 'password';

MySQL数据库用户授权

    MySQL通过用户名@主机地址组合标识账户身份，权限分为全局、数据库、表、列四个层级。授权操作需通过GRANT命令实现，遵循最小权限原则。

---

二、用户管理与授权操作命令

1. 用户创建与删除

-- 创建用户（需指定主机地址） 
CREATE USER 'dev_user'@'192.168.1.%' IDENTIFIED BY 'SecurePass123!'; -- 允许192.168.1网段访问 

-- 删除用户 
DROP USER 'old_user'@'localhost'; -- 必须指定完整账户名 

2. 权限授予（GRANT）

-- 全局权限（所有数据库） 
GRANT SHOW DATABASES, PROCESS ON *.* TO 'admin'@'%'; -- 允许查看数据库列表和进程 

-- 数据库级权限 
GRANT SELECT, INSERT, UPDATE, DELETE ON sales_db.* TO 'app_user'@'localhost'; 

-- 表级权限（精确控制） 
GRANT SELECT (name, email), UPDATE (phone) ON hr_db.employees TO 'auditor'@'10.0.%.%'; 

-- 存储过程权限 
GRANT EXECUTE ON PROCEDURE inventory_db.update_stock TO 'service_account'@'api_server'; 

3. 权限回收（REVOKE）

-- 移除特定权限 
REVOKE DELETE ON sales_db.* FROM 'app_user'@'localhost'; 

-- 移除所有权限 
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'temp_user'@'%'; 

4. 权限查看

-- 查看当前用户权限 
SHOW GRANTS; 

-- 查看指定用户权限 
SHOW GRANTS FOR 'app_user'@'localhost'; 

-- 查询mysql.user表 
SELECT host, user, Select_priv, Insert_priv FROM mysql.user WHERE user = 'dev_user'; 

---

三、进阶授权策略

1. 角色管理（MySQL 8.0+）

-- 创建角色 
CREATE ROLE 'read_only', 'data_analyst'; 

-- 给角色授权 
GRANT SELECT ON analytics.* TO 'read_only'; 
GRANT SELECT, CREATE TEMPORARY TABLES ON analytics.* TO 'data_analyst'; 

-- 将角色授予用户 
GRANT 'read_only' TO 'report_user'@'%'; 
GRANT 'data_analyst' TO 'bi_tool'@'10.200.0.5'; 

-- 激活角色 
SET DEFAULT ROLE ALL TO 'report_user'@'%'; 

2. 权限继承（WITH GRANT OPTION）

GRANT SELECT ON customer_db.* TO 'manager'@'%' WITH GRANT OPTION; -- 允许授予他人权限 

3. 有效期控制（MySQL 8.0+）

ALTER USER 'contractor'@'%'
 PASSWORD EXPIRE INTERVAL 90 DAY -- 密码90天过期
 ACCOUNT LOCK; -- 初始锁定状态 

---

四、安全部署规范

1. 初始化安全配置

# 运行安全向导 mysql_secure_installation 

向导包含以下关键操作：

• 删除匿名账户
• 禁用远程root登录
• 删除测试数据库（test）
• 重载权限表

2. 权限审计命令

-- 检查全局权限用户 
SELECT user, host FROM mysql.user WHERE Super_priv = 'Y'; 

-- 查找有DROP权限的用户 
SELECT user, host FROM mysql.user WHERE Drop_priv = 'Y'; 

3. 密码策略强化

-- 设置全局密码策略（MySQL 8.0） 
SET GLOBAL validate_password.policy = STRONG; -- 强制使用特殊字符+数字组合 
ALTER USER 'critical_user'@'%' REQUIRE SSL; -- 强制SSL连接 

---

五、权限生效与调试

1. 手动刷新权限

FLUSH PRIVILEGES; -- 修改权限后必须执行（使用GRANT/REVOKE会自动刷新） 

2. 连接测试命令

# 模拟用户连接测试 
mysql -u app_user -p -h 127.0.0.1 -e "SHOW DATABASES;" 

3. 错误日志检查

# 查看权限拒绝日志 
tail -f /var/log/mysql/error.log | grep 'Access denied'