MySQL基本命令

发布于：2025-08-03 ⋅ 阅读:(14) ⋅ 点赞:(0)

MySQL基本命令

前言

废话不多说 直接开始就好了

‍

7大主干命令：

‍

1. DDL（数据定义语言）

用于创建、修改、删除数据库对象（如数据库、表、索引等）。

  • ​CREATE：创建数据库或表

    create database book; -- 创建数据库
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50)); -- 创建表

  • ​ALTER：修改表结构

    alter table mybok add column age char(10);; -- 新增列
--在 "mybok" 新添加 "age" 一列

  • ​DROP：删除数据库或表

    DROP TABLE users; -- 删除表

2. DML（数据操纵语言）

用于对表中的数据进行增删改操作。

  • ​INSERT：插入数据
    sql

    INSERT INTO users (id, name) VALUES (1, 'Alice');

  • ​UPDATE：更新数据
    sql

    UPDATE users SET name = 'Bob' WHERE id = 1;
--id 是主键 根据条件 "id=1" 修改 "name" 参数

  • DELETE：删除数据
    sql

DELETE FROM users WHERE id = 1;
--id 是主键 根据条件 "id=1" 删除

3. DQL（数据查询语言）

用于查询表中的数据，核心命令是SELECT。

sql

-- 基础查询
SELECT name, age FROM users WHERE age > 18;
-- 查询数据库中 "age" 大于18的 并输出 "name" 和 "age"

select name,age from mybok where age="20";
+------+------+
| name | age  |
+------+------+
| ws   | 20   |
+------+------+
1 row in set (0.01 sec)


-- 排序和限制
SELECT * FROM users ORDER BY age DESC LIMIT 10;

‍

4. DCL（数据控制语言）

用于管理数据库权限和安全。

  • ​GRANT：授予权限
    sql

    GRANT SELECT ON mydb.users TO 'user1'@'localhost';

    ‍

  • ​REVOKE：撤销权限
    sql

    REVOKE SELECT ON mydb.users FROM 'user1'@'localhost';

‍

5. TCL（事务控制语言）

用于管理数据库事务，确保数据一致性。

  • ​COMMIT：提交事务（确认修改）
    sql

    COMMIT;

  • ​ROLLBACK：回滚事务（取消未提交的修改）
    sql

    ROLLBACK;

  • ​START TRANSACTION：开始事务
    sql

    START TRANSACTION;

‍

6. 数据库连接与管理命令

用于连接数据库、切换数据库等基础操作。

  • ​CONNECT/USE：连接或切换数据库
    sql

    USE mydb; -- 切换到mydb数据库

  • ​EXIT/QUIT：退出 MySQL 客户端
    sql

    EXIT;

    ‍

‍

7. 辅助命令

用于查看数据库信息、调试等。

  • ​SHOW：查看数据库、表、列等信息
    sql

    SHOW DATABASES; -- 显示所有数据库
SHOW TABLES; -- 显示当前数据库的表

    ‍

  • ​DESCRIBE：查看表结构
    sql

    DESCRIBE users; -- 等价于 DESC users

‍

数据类型

‍

数字：

整数类型（Integer Types）

用于存储没有小数部分的整数，支持有符号（默认）和无符号（UNSIGNED）两种模式。

​TINYINT​ 1 ​-128 ~ 127​ ​0 ~ 255​ 状态标识（如 0/1）、小数量
​SMALLINT​ 2 ​-32768 ~ 32767​ ​0 ~ 65535​ 小范围计数（如商品库存）
​MEDIUMINT​ 3 ​-8388608 ~ 8388607​ ​0 ~ 16777215​ 中等范围数值（如用户积分）
​INT/INTEGER​ 4 ​-2147483648 ~ 2147483647​ ​0 ~ 4294967295​ 通用整数（如 ID、年龄）
​BIGINT​ 8 ​-9223372036854775808 ~ 9223372036854775807​ ​0 ~ 18446744073709551615​ 大数值（如订单号、海量数据 ID）

小数类型（Decimal Types）

精确小数（定点数）

  • ​DECIMAL(M, D) / NUMERIC(M, D)（两者功能相同）

    • ​M：总位数（整数 + 小数部分，1 ≤ M ≤ 65，默认 10）
    • ​D：小数位数（0 ≤ D ≤ M，默认 0）
    • 特点：精确存储，无精度损失，适合财务、金额等场景。

‍

近似数值类型

MySQL 提供了专门用于存储近似数值的数据类型，适合表示小数或需要较大取值范围的数值：

  • ​​FLOAT​：单精度浮点数，占用 4 字节，精度约 6-7 位有效数字。
  • ​​DOUBLE​：双精度浮点数，占用 8 字节，精度约 15-17 位有效数字。

示例：

sql

CREATE TABLE measurements (
    id INT,
    temperature FLOAT,  -- 单精度存储
    weight DOUBLE       -- 双精度存储
);

注意：浮点数存在精度误差（如0.1无法被精确表示），若需精确计算（如货币），建议使用DECIMAL类型。

近似计算函数

MySQL 提供了多个函数用于近似数值的处理：

  • ​​ROUND(x, d)​ ：将x四舍五入到d位小数（d默认 0）。
    sql

    SELECT ROUND(3.14159, 2);  -- 结果：3.14

  • ​​FLOOR(x)​ ：向下取整（截断小数部分）。
    sql

    SELECT FLOOR(3.999);  -- 结果：3

  • ​​CEIL(x)​ / ​CEILING(x)​ ：向上取整。
    sql

    SELECT CEIL(2.1);  -- 结果：3

  • ​​TRUNCATE(x, d)​ ：截断到d位小数（不四舍五入）。
    sql

    SELECT TRUNCATE(3.14159, 2);  -- 结果：3.14

  • ​​ABS(x)​ ：取绝对值。
    sql

    SELECT ABS(-5.2);  -- 结果：5.2
科学计数法表示

对于极大或极小的近似数值，MySQL 支持科学计数法：

sql

INSERT INTO data (value) VALUES (1.23e5);  -- 等价于123000
INSERT INTO data (value) VALUES (4.56e-3); -- 等价于0.00456
精度与舍入模式

  • 浮点数精度限制：FLOAT和DOUBLE的精度有限，超出范围会自动舍入。
    sql

    SELECT 0.1 + 0.2;  -- 结果：0.30000000000000004（因二进制存储误差）

  • 避免直接比较浮点数：由于精度误差，建议用ROUND()或范围判断：
    sql

    -- 不推荐：可能因误差导致判断失效
SELECT * FROM table WHERE value = 0.3;

-- 推荐：通过四舍五入或范围比较
SELECT * FROM table WHERE ROUND(value, 2) = 0.3;
SELECT * FROM table WHERE value BETWEEN 0.299 AND 0.301;

‍

字符串型

​CHAR​ 类型
  • 特点：固定长度字符串，长度范围为 0~255 个字符。
  • 存储方式：无论实际存储的字符串长度如何，都会占用指定长度的空间（空格填充）。
  • 适用场景：存储长度固定的数据，如身份证号、手机号、性别等。

示例：

CREATE TABLE users (
    id INT,
    gender CHAR(1),  -- 存储'男'或'女'，固定占用1个字符空间
    phone CHAR(11)   -- 存储11位手机号，固定长度
);
​VARCHAR​ 类型
  • 特点：可变长度字符串，长度范围为 0~65535 个字符（实际受表的总字段长度限制）。
  • 存储方式：仅占用实际字符串长度 + 1~2 字节（用于记录长度），节省空间。
  • 适用场景：存储长度不固定的文本，如姓名、地址、描述等。

示例：

CREATE TABLE products (
    id INT,
    name VARCHAR(100),  -- 产品名称，最长100字符
    description VARCHAR(2000)  -- 产品描述，最长2000字符
);
长文本类型

当需要存储超过VARCHAR最大长度的文本（如文章内容、日志等）时，可使用以下类型：

  • ​​TEXT​：最大长度约 65535 字符（64KB）。
  • ​​MEDIUMTEXT​：最大长度约 1677 万字符（16MB）。
  • ​​LONGTEXT​：最大长度约 42 亿字符（4GB）。

示例：

sql

CREATE TABLE articles (
    id INT,
    title VARCHAR(200),
    content LONGTEXT  -- 存储长文章内容
);
4. 二进制字符串类型

用于存储二进制数据（如图片、文件等，通常不推荐直接存储，建议存储文件路径）：

  • ​​BINARY​：固定长度二进制字符串。
  • ​​VARBINARY​：可变长度二进制字符串。
  • ​​BLOB​​ /​​MEDIUMBLOB​​ /​​LONGBLOB​：对应二进制的长文本类型。
5. 字符串处理函数

MySQL 提供了丰富的字符串函数，常用的有：

  • ​​CONCAT(s1, s2, ...)​ ：拼接字符串。
    sql

    SELECT CONCAT('Hello', ' ', 'MySQL');  -- 结果：'Hello MySQL'

  • ​​LENGTH(s)​ ：返回字符串字节长度（注意：中文在 UTF-8 编码下占 3 字节）。
    sql

    SELECT LENGTH('测试');  -- 结果：6（UTF-8编码下2个中文字符占6字节）

  • ​​CHAR_LENGTH(s)​ ：返回字符串字符个数（与编码无关）。
    sql

    SELECT CHAR_LENGTH('测试');  -- 结果：2

  • ​​SUBSTRING(s, pos, len)​ ：截取子串（pos 为起始位置，len 为长度）。
    sql

    SELECT SUBSTRING('MySQL', 2, 3);  -- 结果：'SQL'

  • ​​UPPER(s)​ ​ /​​LOWER(s)​ ：转换为大小写。
    sql

    SELECT UPPER('mysql');  -- 结果：'MYSQL'

  • ​​TRIM(s)​ ：去除字符串首尾空格。
    sql

    SELECT TRIM('  MySQL  ');  -- 结果：'MySQL'
6. 选择建议
  • 长度固定且较短 → 优先用CHAR（查询效率略高）。
  • 长度不固定或较长 → 用VARCHAR（节省空间）。
  • 超长大文本 → 用TEXT系列类型。
  • 注意字符集：创建表时建议指定CHARACTER SET utf8mb4，支持包括 emoji 在内的所有 Unicode 字符。

‍

unicode字符串型（简单说一下）

unicode国际通用型字符串，将所有字符都改为双字节存储。

nchar() 固定长度的字符串，最大4000字符。

nvarchar() 不固定长度的字符串，最大4000字符。

ntext 存储大文本，10亿字符

‍

二进制字符串型

存储图片或视频类文件，存储由二进制直接构成的图片或视频，或者转换格式后的图片 .bmp .avi .jpg .gif

binary 固定长度

varbinary 不固定长度

image 存储大型图片类文件

‍

日期时间型

date 日期 2019-07-16

time 时间 9:57:22

datetime 日期时间 2019-07-16 9:57:50

‍

创建表

create table 表名
(
字段名1  数据类型  约束,
字段名2  数据类型  约束,
...
)

--例
create table mybok(id int  PRIMARY KEY,name varchar(10));

‍

查看表结构

DESC users;
-- 输出结果
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | char(10)    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
--输出说明：

Field：字段名
Type：数据类型（如INT、VARCHAR(50)）
Null：是否允许为NULL（YES/NO）
Key：是否为键（PRI表示主键，UNI表示唯一键，MUL表示可重复的索引）
Default：默认值
Extra：额外信息（如auto_increment自增）

‍

查询数据

select * from mybok；
--输出结果
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | ws   | 20   |
|    1 | hehe | 30   |
|    1 | haha | 10   |
+------+------+------+
3 rows in set (0.00 sec)

‍

插入数据

insert into mybok values(10,"make","10");
--验证结果 
select * from mybok;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | ws   | 20   |
|    1 | hehe | 30   |
|    1 | haha | 10   |
|   10 | make | 10   |
+------+------+------+
4 rows in set (0.00 sec)

‍

约束

主键约束
--标记一张表中，主要查询字段，具有唯一性和非空性。每张表中只能有一个主键，也可以没有主键。
primary key

create table mybok
id  int  primary key,
name char(20) 
)；

--PS：引用键：多张表建立关系时，指定的同类型字段称为引用键。
--PS：外键：被指定外键的字段中添加数据时，必须参照所选表主键的值。

‍

唯一性约束
--指定字段中的数据不能重复，但可以为空。表中可以有多个唯一性约束
unique

create table mybok 
(
id int primary key,
name char(20) unique
);

--PS：null是一个标记，不是具体值。

‍

默认约束
--当用户对字段不添加数据时，默认约束会自动添加一个以指定的值。 
default

create table mybok 
(
id int,
name char(20),
sex char(4) default '男'
);

‍

检查约束
--对指定字段中的数值范围进行检查，超出范围的进行报错。
check

create table mybok 
(
id int,
name char(20),
sex char(20) check (sex='男' or sex='女')
);

create table mybok
(
id int,
name char(20),
age int check (age>=18 and age<=50)
);

‍

外键约束
-- 是用于建立两个表之间关联关系的规则，它通过引用另一个表的主键（或唯一键），确保从表（子表）中的数据与主表（父表）中的数据保持一致性和完整性。
create table t201
(
id int primary key, --创建主键
name char(10)
) engine=innodb; --允许外键的链接

CREATE TABLE biao6 (
  id INT,
  nian INT,
  yue INT,
  gongzi INT,
  -- 定义外键约束
  CONSTRAINT fk_biao6_biao5  -- 外键名称（自定义，建议有意义）
  FOREIGN KEY (id)  -- 子表中作为外键的字段
  REFERENCES biao5(id)  -- 关联主表的主键字段
  ON DELETE CASCADE  -- 主表记录删除时，子表关联记录也删除（可选）
  ON UPDATE CASCADE  -- 主表主键更新时，子表外键同步更新（可选）
) ENGINE=InnoDB;

‍

非空约束
--指定字段中不能为空。
not null


create table mybik
(
id int not null,
name char(20) not null,
)

‍

修改表

添加字段


alter table mybik
add sex char(4)

alter table mybik
add age int check(age>=18 and age<=50)

--语法格式：

alter table 表名
add 新字段名  数据类型  约束

--如果表是空表，新添加的字段可以设置约束非空，如果表中有数据，则不能设置非空约束。

‍

删除字段
alter table mybik
drop column sex

--语法格式：

alter table 表名 drop column  字段名

--PS：被删除的字段不能被其他表引用，不能被检查约束引用。被删除的字段中的数据将会丢失，如果没有做备份，则无法恢复。

‍

修改字段数据类型
alter table biao1
alter column  sex char(4)

--语法格式:

alter table 表名
alter column  字段名  数据类型  约束

--PS：没有数据时，字段类型可以随意修改，当有数据时，必须保证数据的兼容性。任何类型数据都能转换为字符串，而字符串不能转换为其他数据类型。

‍

1.修改默认值约束（DEFAULT）

  • 添加默认值：
    sql

    ALTER TABLE 表名 MODIFY COLUMN 字段名 数据类型 DEFAULT 默认值;

    示例：给myboy表的age字段添加默认值18
    sql

    ALTER TABLE myboy MODIFY COLUMN age INT DEFAULT 18;

  • 删除默认值：
    将默认值设为NULL（需确保字段允许为NULL）
    sql

    ALTER TABLE myboy MODIFY COLUMN age INT DEFAULT NULL;
2. 修改唯一约束（UNIQUE）

  • 添加唯一约束：
    sql

    ALTER TABLE 表名 ADD UNIQUE (字段名);
-- 或指定约束名（推荐，便于后续删除）
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名);

    示例：给myboy表的phone字段添加唯一约束
    sql

    ALTER TABLE myboy ADD CONSTRAINT uk_phone UNIQUE (phone);

  • 删除唯一约束：
    需要先知道约束名（可通过SHOW CREATE TABLE 表名;查看）
    sql

    ALTER TABLE 表名 DROP INDEX 约束名;  -- 唯一约束本质是一种索引

    示例：删除上面创建的uk_phone约束
    sql

    ALTER TABLE myboy DROP INDEX uk_phone;
3. 修改主键约束（PRIMARY KEY）

  • 添加主键（表中没有主键时）：
    sql

    ALTER TABLE 表名 ADD PRIMARY KEY (字段名);

    示例：给myboy表的id字段添加主键
    sql

    ALTER TABLE myboy ADD PRIMARY KEY (id);

  • 删除主键（需先删除依赖它的外键）：
    sql

    ALTER TABLE 表名 DROP PRIMARY KEY;

    注意：如果主键是自增（AUTO_INCREMENT），需先去掉自增属性才能删除主键。

4. 修改外键约束（FOREIGN KEY）

  • 添加外键：
    sql

    ALTER TABLE 子表名 ADD CONSTRAINT 外键名 
FOREIGN KEY (子表字段) REFERENCES 主表名(主表字段)
[ON DELETE 规则] [ON UPDATE 规则];

    示例：给orders表添加外键关联users表的id
    sql

    ALTER TABLE orders ADD CONSTRAINT fk_orders_user 
FOREIGN KEY (user_id) REFERENCES users(id) 
ON DELETE CASCADE;

  • 删除外键：
    需要先知道外键名（通过SHOW CREATE TABLE 子表名;查看）
    sql

    ALTER TABLE 子表名 DROP FOREIGN KEY 外键名;

    示例：删除fk_orders_user外键
    sql

    ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;
5. 修改非空约束（NOT NULL ↔ NULL）

  • 设置为非空（NOT NULL） ：
    sql

    ALTER TABLE 表名 MODIFY COLUMN 字段名 数据类型 NOT NULL;

  • 允许为空（NULL） ：
    sql

    ALTER TABLE 表名 MODIFY COLUMN 字段名 数据类型 NULL;

    ‍

    示例：允许myboy表的name字段为空
    sql

    ALTER TABLE myboy MODIFY COLUMN name VARCHAR(10) NULL;

‍

数据修改

‍

基本语法

sql

UPDATE 表名
SET 字段1 = 新值1, 字段2 = 新值2, ...
[WHERE 条件];  -- 可选，不写则更新表中所有记录
关键说明
  • ​SET 子句：指定要修改的字段及对应新值（多个字段用逗号分隔）。
  • ​WHERE 子句：指定更新哪些记录（强烈建议添加，否则会更新表中所有行）。
  • 可以结合函数、表达式或子查询生成新值。
常用示例
1. 更新单条记录

更新 users 表中 id=1 的用户姓名和年龄：

sql

UPDATE users
SET name = '张三', age = 25
WHERE id = 1;
2. 更新多条符合条件的记录

将 products 表中所有分类为 '手机' 的商品价格提高 10%：

sql

UPDATE products
SET price = price * 1.1  -- 使用表达式计算新值
WHERE category = '手机';
3. 根据其他表的数据更新

将 orders 表中用户 id=5 的订单状态，同步为 user_status 表中该用户的最新状态：

sql

UPDATE orders o
JOIN user_status us ON o.user_id = us.user_id
SET o.status = us.new_status
WHERE o.user_id = 5;
4. 不使用 WHERE 子句（谨慎！）

会更新表中所有记录，例如将 employees 表中所有人的部门统一改为 '行政部'：

sql

UPDATE employees
SET department = '行政部';  -- 无WHERE，影响所有行
注意事项

  1. 备份数据：执行批量更新前，建议先备份数据（如 CREATE TABLE 备份表 SELECT * FROM 原表;）。

  2. 事务保护：重要更新可放在事务中，出错时回滚：
    sql

    START TRANSACTION;  -- 开始事务
UPDATE ... ;        -- 执行更新
-- 确认无误后提交，否则执行 ROLLBACK;
COMMIT;

    ‍

  3. 性能影响：更新大量数据时可能锁表，建议分批次更新（配合 LIMIT）：
    sql

    UPDATE large_table
SET status = 1
WHERE status = 0
LIMIT 1000;  -- 每次更新1000条

    ‍

  4. 约束检查：更新后的数据需符合表的约束（如外键、唯一键、非空等），否则会报错。

‍

删除数据

‍

基本语法

sql

DELETE FROM 表名
[WHERE 条件];  -- 可选，不写则删除表中所有记录
关键说明
  • ​WHERE 子句：指定要删除的记录（强烈建议强烈建议添加，否则会删除表中所有行）。
  • 可以结合复杂条件（如 AND/OR、子查询等）精准删除目标记录。
常用示例
1. 删除单条记录

删除 users 表中 id=5 的用户：

sql

DELETE FROM users
WHERE id = 5;
2. 删除多条符合条件的记录

删除 orders 表中 status='已取消' 且 create_time 在 2023 年之前的订单：

sql

DELETE FROM orders
WHERE status = '已取消' 
  AND create_time < '2023-01-01';
3. 根据通过子查询删除关联数据

删除 products 表中，分类表 categories 里标记为 '已废弃' 的商品：

sql

DELETE FROM products
WHERE category_id IN (
  SELECT id FROM categories WHERE status = '已废弃'
);
4. 删除所有记录（谨慎！）

清空表中所有数据（保留表结构）：

sql

DELETE FROM employees;  -- 无WHERE，删除所有行（逐行删除，速度较慢）

替代方案：若需清空表且不保留事务日志（速度更快），可使用 TRUNCATE（但无法回滚，且会重置自增主键）：

sql

TRUNCATE TABLE employees;  -- 清空表，相当于保留表结构
注意事项

  1. 备份数据：删除前建议备份（如 CREATE TABLE 备份表 SELECT * FROM 原表 WHERE 条件;）。

  2. 事务保护：重要删除操作可放在事务中，出错时回滚：
    sql

    START TRANSACTION;  -- 开始事务
DELETE FROM ... ;   -- 执行删除
-- 确认无误后提交，否则执行 ROLLBACK;
COMMIT;

    ‍

  3. 外键约束：若表有外键关联，删除主表记录可能受外键规则限制（如 ON DELETE RESTRICT 会阻止删除），需先处理子表关联数据。

  4. 性能影响：删除大量数据时可能锁表，建议分批次删除（配合 LIMIT）：
    sql

    DELETE FROM logs
WHERE create_time < '2023-01-01'
LIMIT 1000;  -- 每次删除1000条

    ‍

  5. 索引利用：删除时尽量使用索引字段（如主键、唯一键）作为 WHERE 条件，提高效率。

‍

总结

好了 我觉得够了 本来想着吧select的东西讲完 但发现写的有点多了 并且我觉得我写的很详细了 大部分基本用法都解释的差不多了 至于select 我单独那一篇文章去说吧 它的内容还是蛮多的 我也累了 码字一上午

回应上一篇 去努力 生活还是提升 还是为了家或者自己

网站公告

今日签到

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

热门文章

最新发布