Mysql操作教学之数据库（database）和数据表（table）的增、删、改、查，介绍运算符和表达式的应用，万字教学，超详细！！！（2）

本文详细介绍了MySQL数据库和数据表的基本操作，主要内容包括：数据库操作：创建（CREATE DATABASE）、删除（DROP DATABASE）、修改（ALTER DATABASE）和查询（SHOW DATABASES）命令，涵盖字符集设置、安全删除等实用技巧。数据表操作：重点讲解CREATE TABLE语法，包括列定义、数据类型（整数、小数、字符串、日期时间等）、约束条件（主键、唯一、

盲敲代码的阿豪

1363人浏览 · 2025-05-26 10:18:40

盲敲代码的阿豪 · 2025-05-26 10:18:40 发布

前言

本篇文章主要讲解了数据库（database）和数据表（table）的增（create）、删（drop）、改（alter）、查（show）相关命令，并介绍了Mysql中运算符和表达式的应用，通过多个案例详细教学，帮助大家快速掌握对数据库和数据表的操作。

1、Mysql之数据库的基本操作

1.1 创建数据库（create）

在MySQL中，对数据库(database)的创建操作主要通过create database语句实现。以下是详细使用方法：

基本创建语法

create database 数据库名称;

示例：

create database mydb;

带字符集和排序规则的创建

create database 数据库名称 character set 字符集名称 collate 排序规则名称;

示例（创建使用utf8mb4字符集的数据库）：

create database mydb character set utf8mb4 collate utf8mb4_unicode_ci;

检查数据库是否已存在

create database if not exists 数据库名称;

示例：

create database if not exists mydb;

查看所有数据库

创建后可以查看所有数据库：
```
show databases;
```

选择使用数据库

创建后要使用数据库：
```
use 数据库名称;
```

完整示例

-- 创建数据库(如果不存在)
create database if not exists company_db
character set utf8mb4
collate utf8mb4_unicode_ci;

-- 查看所有数据库
show databases;

-- 使用新创建的数据库
use company_db;

注意事项：

创建数据库需要相应的权限
数据库名称在MySQL服务器实例中必须唯一
建议使用小写字母命名数据库，避免大小写问题
在生产环境中，通常需要指定字符集以避免乱码问题

1.2 删除数据库（drop）

在 MySQL 中，删除数据库使用 drop database 命令。以下是相关操作的详细说明：

基本删除数据库命令
```
drop database 数据库名;
```
例如删除名为 testdb 的数据库：
```
drop database testdb;
```

安全删除（先检查是否存在）

为了避免尝试删除不存在的数据库时出现错误，可以使用：
```
drop database if exists 数据库名;
```
例如：
```
drop database if exists testdb;
```

扩展：删除数据库中的所有表（而不删除数据库本身）

如果需要保留数据库但删除所有表：

-- 首先选择要操作的数据库
use 数据库名;

-- 获取所有表名并生成删除语句
select concat('drop table if exists ', table_name, ';') 
from information_schema.tables 
where table_schema = '数据库名';

然后执行生成的删除语句。

注意事项

删除数据库是不可逆操作，所有数据将永久丢失
执行此操作需要足够的权限（通常是 root 或具有 drop 权限的用户）
在生产环境中操作前务必做好备份
删除数据库会同时删除该数据库中的所有表、视图、存储过程等对象

1.3 修改数据库（alter）

Mysql 中使用 alter database 命令来修改数据库的属性。以下是常见的数据库修改操作：

修改数据库字符集和排序规则

alter database 数据库名 character set 字符集名称 collate 排序规则名称;

示例：

alter database testdb character set utf8mb4 collate utf8mb4_unicode_ci;

仅修改数据库字符集

alter database 数据库名 character set 字符集名称;

示例：

alter database testdb character set utf8;

仅修改数据库排序规则

alter database 数据库名 collate 排序规则名称;

示例：

alter database testdb collate utf8_general_ci;

修改数据库名称

Mysql 没有直接的 alter database 命令来重命名数据库，通常需要：
1. 创建新数据库
2. 导出旧数据库数据
3. 导入到新数据库
4. 删除旧数据库
或者使用工具如 mysqldump 或直接重命名数据目录（不推荐生产环境使用）。

注意事项
5. 执行 alter database 需要相应的权限（通常是 alter 权限）

修改字符集不会自动转换已有表中的数据，只会影响新创建的表
某些旧版本 mysql 可能使用 alter schema 作为 alter database 的同义词

1.4 查询数据库（show）

以下是 Mysql 中用于查询数据库(database)的相关 show 操作命令：

1、基本数据库查询

显示所有数据库：
```
show databases;
```
显示当前选择的数据库：
```
show database();
```
显示创建特定数据库的 sql 语句：
```
show create database 数据库名;
```

2、与当前数据库相关的查询

需要先选择数据库 use 数据库名，然后执行下面语句进行查询：

显示当前数据库中的表：
```
show tables;
```

显示当前数据库中符合某种模式(like)的表：

show tables like '模式';

示例：

-- 显示所有以"user"开头的表
SHOW TABLES LIKE 'user%';

-- 显示所有包含"log"的表
SHOW TABLES LIKE '%log%';

-- 显示名为"orders_2023"后跟一个字符的表（如orders_20231, orders_2023a等）
SHOW TABLES LIKE 'orders_2023_';

显示当前数据库中的表状态信息：
```
show table status;
```

显示当前数据库中的视图：

show full tables where table_type = 'view';

3、其他相关查询

显示数据库的字符集和排序规则：
```
show charset;
show collation;
```
显示数据库权限：
```
show grants;
```
显示数据库进程列表：
```
show processlist;
```
显示数据库系统变量：
```
show variables;
```
显示数据库状态信息：
```
show status;
```

注意：所有命令都以分号(;)结尾，这是 Mysql 命令行客户端的语句结束符。

2、Mysql之数据表的基本操作

注意： 以下关于数据表的操作，都必须在数据库内执行，使用命令：use 数据库名 选择要操作数据表。

2.1 创建数据表（create）

以下是 Mysql 中创建数据表(create table)的相关操作命令

1、基本创建表语法

create table [if not exists] 表名 (
    列名1 数据类型 [约束条件] [comment '注释'],
    列名2 数据类型 [约束条件] [comment '注释'],
    ...
    [表级约束条件]
) [engine=存储引擎] [default charset=字符集] [comment='表注释'];

常用数据类型：

整数类型：int, tinyint, smallint, mediumint, bigint
小数类型：float, double, decimal(m,d)
字符串类型：char(n), varchar(n), text, blob
日期时间：date, time, datetime, timestamp, year

常用约束条件：

default 值 ：表示该列的默认值，不指定默认为 null
auto_increment：自动递增，该属性每个表只能有一个，且拥有该属性的列必须是数值类型
primary key：主键约束，一个表只有一个主键
unique：唯一约束，表示该列的值唯一，不能重复，但可以为 null
not null：非空约束，表示插入数据时该列的值不能为空，不写默认为 null
comment：描述信息，注释
zerofill：填充，当一个字段数据位数为5，赋予 zerofill 属性后，若插入的数据只有 3位，剩余2位数用 0填充到数据前面，一般用于让 int类型数据显示宽度一致，如：00001，00011，01123
foreign key: 外键约束

表选项：

engine：存储引擎（默认InnoDB）
default charset：字符集（推荐utf8mb4）
collate：排序规则
comment：表注释
row_format：行存储格式

2、使用示例

创建简单表

create table users (
    id int primary key auto_increment,
    username varchar(50) not null,
    password varchar(50) not null,
    email varchar(100) unique,
    created_at datetime default current_timestamp
);

创建带外键的表

create table orders (
    order_id int primary key auto_increment,
    user_id int not null,
    amount decimal(10,2),
    order_date datetime,
    constraint foreign_name foreign key (user_id) references users(id)
);

创建表时添加索引

create table products (
    id int primary key auto_increment,
    name varchar(100) not null,
    price decimal(10,2),
    category_id int,
    index idx_name (name),
    index idx_category (category_id)
);

从现有表创建新表（复制表格）

-- 只复制结构
create table new_table like old_table;

-- 复制结构和数据
create table new_table as select * from old_table;

创建临时表

create temporary table temp_users (
    id int,
    name varchar(50)
);

创建表时指定存储引擎和字符集

create table logs (
    id int primary key auto_increment,
    message text,
    created_at datetime
) engine=innodb default charset=utf8mb4;

–
注意事项

表名和列名可以使用反引号`包围，避免使用保留字时出错
创建表前最好检查表是否已存在：show tables like '表名'

可以使用 if not exists 避免表已存在时报错：

create table if not exists users (
id int primary key
);

2.2 删除数据表（drop）

以下是 MySQL 中删除数据表(drop table)相关操作：

1、基本删除表操作

删除单个表：
```
drop table 表名;
```
删除多个表（用逗号分隔）：
```
drop table 表1, 表2, 表3;
```

2、带条件的删除操作

如果表存在才删除（避免表不存在时报错）：
```
drop table if exists 表名;
```
删除临时表：
```
drop temporary table 表名;
```

3、删除表相关对象

删除视图：
```
drop view 视图名;
```
删除触发器：
```
drop trigger 触发器名;
```

4、扩展：解决MySQL无法删除表因外键约束的问题

当我们尝试删除MySQL中的表时遇到错误，如："Cannot drop table 'users' referenced by a foreign key constraint 'orders_ibfk_1' on table 'orders'"，这是因为存在外键约束阻止了删除操作。

解决方案

方法1：先删除外键约束再删除表（推荐）

-- 1. 先删除外键约束
alter table orders drop foreign key orders_ibfk_1;

-- 2. 然后删除表
drop table users;

方法2：临时禁用外键检查

-- 1. 禁用外键检查
set foreign_key_checks = 0;

-- 2. 删除表
drop table users;

-- 3. 重新启用外键检查
set foreign_key_checks = 1;

注意：方法2虽然简单，但可能会破坏数据完整性，建议仅在开发环境使用。

方法3：级联删除（如果确实需要删除关联数据）

-- 这将删除users表及其所有关联的orders记录
drop table users cascade;

注意事项

删除表操作会永久删除表结构和表中的所有数据
执行前应确保已备份重要数据
需要有足够的权限才能执行删除操作
在生产环境中使用时要特别谨慎

如需查看表是否存在再删除，可以先使用：

show tables like '表名';

2.3 修改数据表（alter）

Mysql 中使用 alter table 语句来修改表结构，以下是常用的 alter 操作示例：

添加列

alter table 表名 add column 列名 数据类型 [约束条件];

示例：

alter table users add column age int not null default 0;

修改列定义

alter table 表名 modify column 列名 新数据类型 [约束条件];

示例：

alter table users modify column username varchar(50) not null;

重命名列

alter table 表名 change column 旧列名 新列名 数据类型 [约束条件];

示例：

alter table users change column email user_email varchar(100);

删除列

alter table 表名 drop column 列名;

示例：

alter table users drop column age;

添加主键

alter table 表名 add primary key (列名);

示例：

alter table users add primary key (id);

删除主键
```
alter table 表名 drop primary key;
```

添加外键

alter table 表名 add constraint 约束名 foreign key (列名) references 主表名(主键列);

示例：

alter table orders add constraint fk_user_id foreign key (user_id) references users(id);

删除外键

alter table 表名 drop foreign key 约束名;

添加索引

alter table 表名 add index 索引名 (列名);

示例：

alter table users add index idx_username (username);

删除索引

alter table 表名 drop index 索引名;

重命名表

alter table 旧表名 rename to 新表名;
rename table 旧表名 to 新表名;

示例：

alter table old_users rename to new_users;
rename table old_users to new_users;

将表移动到其它数据库

alter table 旧表名 rename to 新数据库.新表名;
rename table 旧表名 to 新数据库.新表名;

示例：

alter table old_users rename to new_db.new_users;
rename table old_users to new_db.new_users;

若 新表名 与 旧表名 相同，移动后表名不变，若不同，移动后表名更改。

修改表字符集

alter table 表名 convert to character set 字符集名;

示例：

alter table users convert to character set utf8mb4;

添加自增属性

alter table 表名 modify column 列名 数据类型 auto_increment;

删除自增属性

alter table 表名 modify column 列名 数据类型;

2.4 查询数据表（show）

在 mysql 中，查询数据表(show tables)及相关信息的操作命令如下：

1、基本表查询

显示当前数据库中的所有表：
```
show tables;
```
显示另一个数据库中的所有表：
```
show tables from database_name;
```

2、表结构查询

显示表的结构(字段信息)：
```
show columns from table_name;
desc table_name;
explain table_name;
```
以上3种方法的效果相同，若想查询其它数据库中的表，可在表名前加上数据库名，如：
```
show columns from db_name.table_name;
desc db_name.table_name;
```
也可以查询表中指定列的信息，在表名后加上列名即可，如：
```
show columns from table_name 列名1,列名2;
desc table_name 列名1,列名2;
```
显示表的创建语句：
```
show create table table_name;
```

3、表状态信息

显示表的详细信息(存储引擎、行数等)：
```
show table status like 'table_name';
```
显示数据库中所有表的状态：
```
show table status from database_name;
```

4、过滤查询

使用模式匹配查询表：
```
show tables like 'pattern';
```
例如查询以"user"开头的表：
```
show tables like 'user%';
```

5、其他相关查询

显示数据库中的视图：

show full tables where table_type = 'view';

显示表索引：
```
show index from table_name;
```

注意： 所有命令都可以使用小写，Mysql 不区分关键字的大小写。

3、运算符和表达式

3.1 运算符

算术运算符

+：加法
```
select 10 + 5 as sum;  -- 结果：15
```
-：减法
```
select 20 - 7 as diff;  -- 结果：13
```

*：乘法

select 3 * 4 as product;  -- 结果：12

/：除法（返回浮点数）
```
select 10 / 3;  -- 结果：3.3333
```
div：整除（返回整数）
```
select 10 div 3;  -- 结果：3
```
% 或 mod：取模
```
select 10 % 3;  -- 结果：1
```

比较运算符

=：等于
```
select * from users where age = 25;
```

!= 或 <>：不等于

select * from products where price != 100;

>、<、>=、<=：大小比较

select * from orders where total > 500;

<=>：安全等于（可处理null）

select null <=> null;  -- 结果：1（true）

is null / is not null：判断null

select name from students where score is not null;

between ... and ...：范围判断（闭区间）

select * from products where price between 50 and 100;

in：匹配列表中的值

select * from employees where id in (1, 3, 5);

like：模糊匹配（%匹配任意字符，_匹配单个字符）
```
select * from users where name like 'j%';
```

regexp：正则表达式匹配

select * from logs where message regexp '^error';

逻辑运算符

and：逻辑与

select * from orders where total > 100 and status = 'shipped';

or：逻辑或

select * from users where age < 18 or age > 60;

not：逻辑非

select * from tasks where not is_completed;

xor：逻辑异或（仅一个条件为真时成立）

select * from votes where user_voted xor admin_override;

位运算符

&：按位与

select 5 & 3;  -- 结果：1（二进制 101 & 011 = 001）

|：按位或

select 5 | 3;  -- 结果：7（101 | 011 = 111）

^：按位异或

select 5 ^ 3;  -- 结果：6（101 ^ 011 = 110）

~：按位取反

select ~1;  -- 结果：-2（取决于位数）

<<、>>：左移/右移

select 4 << 1;  -- 结果：8（二进制 100 左移1位为1000）

3.2 表达式

表达式由运算符和操作数组成，返回一个值：

算术表达式

select salary * 1.1 as new_salary from employees;

条件表达式

select name, if(score >= 60, '及格', '不及格') as result from students;

case表达式

select name, 
       case 
           when score >= 90 then 'A' 
           when score >= 80 then 'B' 
           else 'C' 
       end as grade 
from students;

3.3 注意事项

运算符优先级

优先级从高到低：括号 () > 算术运算符 > 比较运算符 > 逻辑运算符（not > and > or）。
示例：
```
select * from table where (a > 5 or b < 10) and c = 1;
```

null处理

与null运算时结果通常为null：
```
select 5 + null;  -- 结果：null
```

使用函数处理null：

select ifnull(salary, 0) as adjusted_salary from employees;
select coalesce(address, phone, 'N/A') from contacts;  -- 返回第一个非null值

类型转换

Mysql会自动转换类型（如字符串转数字），但需注意潜在问题：

select '100' + 5;  -- 结果：105（字符串'100'转为数字）
select 'abc' + 5;  -- 结果：5（'abc'转为0）

字符串连接

使用 concat 函数而非运算符：

select concat(first_name, ' ', last_name) as full_name from users;

通过结合运算符和表达式，可实现复杂的数据查询与计算。实际使用中需注意优先级、null处理及类型转换问题。

DAMO开发者矩阵

DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。

更多推荐

KingbaseES数据库：医疗信创潮涌沅江，国产化信创打造医疗新质生产力

DAMO开发者矩阵

告别迁移焦虑：金仓KDMS V4带你轻松搞定数据库国产化替代

DAMO开发者矩阵

KingbaseES数据库在常德二院全栈国产化信创中产生新质生产力,医疗信创的部署如何实现，如何操作？

DAMO开发者矩阵

所有评论(0)

查看更多评论

盲敲代码的阿豪

@m0_59470317

已为社区贡献3条内容