MySql库与表的基础操作大全

目录

• 一、库的操作
• 1.1创建数据库
• 基础语法
• 创建数据库案例：
• 1.2 查看数据库状态
• 1.php3字符集和校验规则
• 实操验证:
• 1.4 修改、删除与备份数据库（需谨慎，避免风险）
• 1.5查看连接情况
• 二、表的操作
• 2.1 创建表
• 不同存储引擎的文件差异
• 2.2 查看表结构
• 2.3 修改表
• 实操案例：
• 2.4 删除表
• 结语：

在数据库管理的日常工作中，mysql 作为一款广泛使用的关系型数据库，其库与表的操作是基础且关键的技能。无论是创建数据库以存储业务数据，还是设计表结构来组织信息，每一步操作都直接影响着后续数据管理的效率与安全性

一、库的操作

数据库（库）是 MySQL 中存储数据的顶层容器，所有的表和数据都依赖于库存在。掌握库的操作，是搭建数据存储框架的第一步。

1.1创建数据库

基础语法

CREATE DATABASE (if not exists) db_name (create_specification (,create_specification) ...)
create_specification: 
[DEFAULT] CHARACTER SET charset_name 
[DEFAULT] COLLATE collation_name

• 语法中不看是否是大小写，大写的是关键字，一定要写的
• if not exists是可选项
• CHARACTER SET:指定数据库采用的字符集
• COLLATE：指定数据库字符集的检验规则

创建数据库案例：

• 创建名为db1的数据库

create database db1;

• 创建一个使用utf8字符集的DB2数据库

create database db2 charset=utf8;

• 创建一个使用utf8字符集，并带校对规则的db3数据库

create database db3 
charset = utf8 
collate utf8_general_ci;

1.2 查看数据库状态

创建数据库后，可通过指令查看系统中所有数据库、单个数据库的创建语句，以及字符集、校验规则等配置，确保数据库符合预期

• 常用指令：

show databases;

• 查看单个数据库的创建语句（含字符据、版本兼容配置）

show create database db_name

示例：查看我们刚刚创建的数据库：

• 查看系统默认字符集与校验规则：

show variables like 'character_set_database';
show variables like 'collation_database';

1.3字符集和校验规则

校验规则直接影响数据查询结果和排序逻辑，以utf8_general_ci（不区分大小写）和utf8_bin（区分大小写）为例：

操作场景	utf8_general_ci(不区分大小写)	utf8_bin(区分大小写)
查询name='a'的结果	返回a和A两条数据	仅返回a一条数据
按name排序结果	顺序为a,A,b,B（忽略大小写排序）	顺序为A,B,a,b(按ASCII码排序)

实操验证:

• 创建区分大小写的数据库test2并插入数据：

create database test2 collate utf8_bin; #创建数据库test2
use test2;  #使用test2
create table person(name varchar(20));  #在test2创建表person
insert into person values ('A'),('a'),('b'),('B');  #在表person内插入数据
select * from person where name='a';  #查询表person内name='a'的数据

• 创建不区分大小的数据库tepythonst3并插入数据

create database test2 collate utf8_general_ci;
use test2;
create table person(name varchar(20));
insert into person values ('A'),('B'),('a'),('b');
select * from person where name='a';

• 还可以看一下这两种校验规则插入后，得到的结果排序：

select * from peison order by name;

1.4 修改、删除与备份数据库（需谨慎，避免风险）

数据库创建后，可能需要调整配置、清理无用数据库，或备份数据以防丢失，这些操作需谨慎执行。

1. 修改数据库：仅支持修改字符集和校验规则，javascript语法如下：

ALTER DATABASE db_name [alter_spacification...];

示例：将test2库的字符集改为gbk

alter database test2 charset=gbk

2. 删除数据库：谨慎操作，数据不可恢复，语法如下：

 DROP DATABASE (if exists) dbname;

【注】删除数据库会级联删除所有的表和数据，且无法恢复，生产环境需提前备份。

3.数据库的备份与恢复：为了保障数据安全

备份与恢复是数据库运维的核心，需掌握单库、多库、单表的备份方式

操作类型	语法命令	说明
备份单库	mysqldump -P3306 -u root -p密码 -B dbname > 数据库备份存储的文件路径	例子：mysqldump -P3306 -u root -p123456 -B test1 > ~/backup/test3.sql
备份多库	mysqldump -u root -p密码 -B db1 db2... &javascriptgt; 路径/dbs.sql	同时备份多个数据库
备份单表	mysqldump -u root -p密码 db_name table1 table2 > 路径/table.sql	仅备份数据库中的指定表
恢复数据	mysql> source 路径/db.sql;	登录 MySQL，再执行恢复命令

【注】前三个是shell命令,后一个是MySQL命令；备份单库时，-P3306是MySQL的端口；若备份时未加-B参数，恢复前需创建空数据库并切换到该数据库，再执行source命令。

1.5查看连接情况

查看连接情况http://www.devze.com可以告诉我i们当前有哪些用户连接到我们的MySQL，如果查出某个用户不是正常登录的，很可能是数据库被入侵了。通过下面的指令可以查看数据的连接情况

• 语法：

show processlist;

输出结果包括连接ID、用户名、主机、操作的数据库、命令状态信息，如：

二、表的操作

表是数据库中实际存储数据的载体，其结构设计（字段类型、长度、约束等）直接影响数据存储效率和查询性能。

2.1 创建表

创建表时需定义字段名、字段类型（如int、varchar、date），并可指定表的字符集、校验规则和存储引擎（如MyISAM、InnoDB）

• 语法

CREATE TABLE table_name ( 
	field1 datatype [comment '字段说明'], 
	field2 datatype [comment '字段说明'], 
	... 
) character set 字符集 collate 校验规则 engine 存储引擎;

• 实操案例：

create table users (
  id int,
  name varchar(20) comment '用户名',
  password char(32) comment '密码是32位的md5值',
  birthday date comment '生日'
) character set utf8 engine MyISAM;

不同存储引擎的文件差异

• MyISAM 引擎：生成3个文件（users.frm表结构、users.MYD表数据、users.MYI表索引）
• InnoDB 引擎：生成2个文件（users.frm表结构、users.ibd表数据 + 索引）

2.2 查看表结构

创建好表后，可通过desc命令快速查看表的字段信息：

desc users;

输出结果是：

2.3 修改表

在项目实际开发中，表结构常需调整（如添加字段、修改字段类型、重命名表），可通过ALTER TABLE命令实现。

• 语法：

ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column datatype]...); ALTER TABLE tablename MODIfy (column datatype [DEFAULT expr][,column   datatype]...); 
ALTER TABLE tablename DROP (column);

实操案例：

• 在users表添加二条记录

insert into users values(1,'a','b','1982-01-04'),(2,'b','c','1984-01-04');

• 在users表中添加一个字段，用于保存图片路径

alter table users add assets varchar(100) comment '图片路径' after birthday;

修改需求	命令	说明
添加字段（图片路径）	alter table users add assets varchar(100) comment '图片路径' after birthday;	after birthday：指定字段插入在birthday后
修改字段长度（用户名改为 60 位）	alter table users modify name varchar(60);	modify需完整指定字段类型
删除字段（密码列）	alter table users drop password;	删除后字段及数据不可恢复，需谨慎
重命名表（users改为employee）	alter table users rename to employee;	to可省略，但是命令不熟悉时最好是写全命令
重命名字段（name改为xingming）	alter table employee change name xingming varchar(60);	change需同时指定新字段名和完整类型

2.4 删除表

• 语法：

DROP (TEMPORARY) TABLE (IF EXISTS) tbl_name [, tbl_name] ...

实操案例：删除表t1:

结语：

本文详细讲解了MySQL数据库与表的核心操作，掌握这些基础操作后，可进一步学习 MySQL 的索引优化、事务管理等高级知识，提升数据库操作的效率与安全性。

到此这篇关于MySql库与表的基础操作的文章就介绍到这了,更多相关mysql库与表操作内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！