Mysql数据库操作

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 类型[(宽度) 约束条件]

）；

注意事项：

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

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'