MySQL 常用 SQL 语句操作指南：DDL/DCL/DML 核心语法

1. 连接本地与远程数据库

在开始操作前，首先需要建立与 MySQL 服务器的连接。以下是连接本地数据库和指定 IP 端口远程数据库的标准命令：

连接本地数据库：

mysql -h localhost -u root -p123 

连接远程数据库（例如 IP: 172.16.xx.xx，端口: 3306）：

mysql -h 172.16.xx.xx -P 3306 -u root -p 

2. DDL 数据定义语言：创建与管理表结构

数据定义语言（DDL）用于定义或变更数据库的模式结构，主要涉及 CREATE、ALTER、DROP、TRUNCATE 等操作。

第 1 步：创建表 (CREATE)

使用 CREATE TABLE 语句创建新表，需定义字段名、类型、约束及注释。以下示例创建一个设备标注表：

CREATE TABLE `device_label` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键-自增长ID',
`origin_model` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '收集机型',
`origin_vendor` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '收集厂商',
`vendor` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '标注厂商',
`model` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '标注品牌',
PRIMARY KEY (`id`),
UNIQUE KEY `device_key` (`origin_model`,`origin_vendor`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE utf8_bin COMMENT='设备标注表'; 

第 2 步：修改表结构 (ALTER)

使用 ALTER TABLE 可以灵活调整现有表的结构，包括修改列类型、重命名列、追加列及调整主键。

修改列类型：

alter table device_label modify origin_model varchar(32); 

重命名列并修改类型：

alter table device_label change origin_model device_model varchar(16); 

在指定位置追加新列：

alter table device_label add os_type varchar(8) COLLATE utf8_bin NOT NULL COMMENT '操作系统' after id; 

调整列的顺序：

alter table device_label modify os_type varchar(8) after origin_model; 

修改主键：

alter table device_label drop primary key, add primary key (`origin_model`,`origin_vendor`); 

第 3 步：清空表数据 (TRUNCATE)

TRUNCATE 用于快速清空表中所有数据并重置自增 ID，其效果等同于不带 WHERE 条件的 DELETE，但效率更高且不可回滚。

truncate device_label; 

3. DCL 数据控制语言：用户权限管理

数据控制语言（DCL）用于管理数据库用户的访问权限。MySQL 提供了细致的权限粒度，支持针对数据库、表甚至列进行控制，并支持通过通配符限制访问主机（Host）。

第 1 步：创建用户并授权

可以使用一条命令同时创建新用户并赋予其对特定数据库的所有权限：

CREATE USER 'hive'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'hive'@'localhost';
-- 或者简写为：
GRANT ALL ON db1.* TO 'hive'@'localhost' IDENTIFIED BY 'mypass'; 

第 2 步：授予细粒度权限

若只需授予特定表的只读权限，可使用 SELECT 权限：

GRANT SELECT ON db2.invoice TO 'hive'@'localhost'; 

4. DML 数据操纵语言：数据的增删改查

数据操纵语言（DML）主要用于对表中的数据进行插入、更新、删除和查询操作，核心命令包括 INSERT、UPDATE、DELETE 和 SELECT。

第 1 步：新增数据 (INSERT)

插入单行数据：

insert into device_label (origin_model, origin_vendor, vendor, model)
values ('h9', 'bbk', '步步高', 'H9'); 

将查询结果复制到另一张表：

insert into device_label_copy (`origin_model`, `origin_vendor`, `vendor`, `model`)
select `origin_model`, `origin_vendor`, `vendor`, `model`
from device_label; 

从文件批量导入数据：

MySQL 支持通过 LOAD DATA 导入结构化文本文件：

load data local infile 'dvc-label.csv'
into table device_label
fields terminated by ','
ignore 1 lines
(origin_model, origin_vendor, vendor, model); 

注意：若在导入时遇到 ERROR 1148 (42000) 错误，可能是因为服务器禁止了本地文件加载功能。此时需在客户端启动时添加 --local-infile 参数：
mysql --local-infile -u user -ppasswd

第 2 步：更新数据 (UPDATE)

根据条件更新表中特定行的值。请务必检查 WHERE 条件，以免误更新全部数据：

update device_label 
set origin_model = 't2', origin_vendor = 'xiami'
where vendor = '锤子'; 

第 3 步：删除数据 (DELETE)

根据指定条件删除行：

delete from device_label where origin_vendor = 'alps'; 

第 4 步：查询数据 (SELECT)

使用 SELECT 结合 GROUP BY 和 HAVING 可以进行聚合统计查询。例如，查询标注机型数量超过 10 个的厂商：

select vendor, count(distinct model) as models
from device_label
group by vendor
having models > 10

以上就是 MySQL 常用基本 SQL 语句的详细总结，更多关于数据库管理与优化的资料请关注本站其它相关文章！