[MySQL实践] MySQL中的数据库与表操作

写代码的老书生

已于 2024-03-11 16:57:54 修改

阅读量1.3k

点赞数 23

分类专栏： MySQL学习文章标签： mysql

于 2024-03-07 16:02:39 首次发布

本文链接：https://blog.csdn.net/xu7382/article/details/136492450

版权

MySQL学习专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一、环境说明

操作系统：win10专业版
MySQL版本：8.0.19

二、操作数据库

2.1、查看所有数据库

在命令行终端或是客户端，执行下面的脚本：

# MySQL中的所有数据库
SHOW DATABASES;

打印所有的数据库如下：

+-----------------------+
| Database              |
+-----------------------+
| information_schema    |
| mysql                 |
| nacos                 |
| performance_schema    |
| seata                 |
| sys                   |
+-----------------------+

这里许多人会产生误区：

认为这条命令会返回MySQL中所有的数据库名。

其实这里需要考虑当前登录账号的权限因素。

因为如果账号授权时只允许访问一个数据库，那么该语句执行后，也只会显示一个数据库，虽然可能MySQL中实际还有其他数据库。

2.2、创建数据库

使用下面的脚本，可以创建自己的数据库：

-- 数据库的名称为：my_database
CREATE DATABASE my_database;

能创建数据库的前提，是当前账号被授予CREATE权限。

查看数据库的定义：

-- 在创建数据库前增加了SHOW
SHOW CREATE DATABASE my_database;

结果如下：

+-------------+---------------------------------------------------------------------------------------------------------+
| Database    | Create Database                                                                                         |
+-------------+---------------------------------------------------------------------------------------------------------+
| my_database | CREATE DATABASE `my_database` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+-------------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

数据库的字符集和排序规则如下：
在这里插入图片描述

2.3、使用数据库

切换到新建的数据库：my_database：

-- 切换到新建的数据库
USE my_database;

2.3.1、查看当前库的所有表

可以通过命令查看当前库中的所有表：

-- 查看当前库中的所有表
SHOW TABLES;

-- 没有表
Empty set

我们可以看出这是一个全新的数据库，尚未创建任何数据表。那么，我们是不是，可以尝试创建一张表格。

2.3.1、创建表

1.数据表的创建
可以使用如下语句创建数据表：

CREATE TABLE `t_user` (
  `id`     bigint NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '用户id',
  `name`   varchar(50) COMMENT '姓名',
  `phone`  varchar(20) COMMENT '电话',
  `status` int DEFAULT '10' COMMENT '状态 10启用/20禁用',
  `valid`  int DEFAULT '1' COMMENT '是否有效'
);

重点是字段id，它满足如下条件：

主键：字段定义时使用PRIMARY KEY
自增列：字段定义时使用AUTO_INCREMENT
非空列：字段定义时使用NOT NULL

如果一个列有默认值，需要在字段类型后增加DEFAULT ${默认值}。

2.查看数据表的定义

如果你想知道一张表的创建脚本，可以这样获得：

-- 需要切换到my_database数据库
SHOW CREATE TABLE t_user;

结果中的Create Table字段就是我们需要的值：
查看表的定义
复制出来是这样的：

CREATE TABLE `t_user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `phone` varchar(20) DEFAULT NULL COMMENT '电话',
  `status` int DEFAULT '10' COMMENT '状态 10启用/20禁用',
  `valid` int DEFAULT '1' COMMENT '是否有效',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

和我们当时执行的脚本还是有一点区别的。

2.3.1、查看表结构

所谓表结构，就是指表有哪些字段，类型是什么，是否可空，有无默认值，是否主键…

可通过如下脚本获得：

DESCRIBE my_database.t_user;
-- or
DESC my_database.t_user;

结果如下：

+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | bigint      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(50) | YES  |     | NULL    |                |
| phone  | varchar(20) | YES  |     | NULL    |                |
| status | int         | YES  |     | 10      |                |
| valid  | int         | YES  |     | 1       |                |
+--------+-------------+------+-----+---------+----------------+
5 rows in set (0.04 sec)

如果希望查看表结构的同时，能够查看字段的注释，使用下面的命令：

-- 查看表结构和注释
SHOW FULL FIELDS FROM t_user;
-- or
SHOW FULL COLUMNS FROM t_user;

结果如下：

+--------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+--------------------+
| Field  | Type        | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment            |
+--------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+--------------------+
| id     | bigint      | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references | 用户id             |
| name   | varchar(50) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references | 姓名               |
| phone  | varchar(20) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references | 电话               |
| status | int         | NULL            | YES  |     | 10      |                | select,insert,update,references | 状态 10启用/20禁用 |
| valid  | int         | NULL            | YES  |     | 1       |                | select,insert,update,references | 是否有效           |
+--------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+--------------------+
5 rows in set (0.04 sec)

2.3.1、修改表结构

1).修改表名

将表名由t_user改为t_account

-- 表名：t_user改为t_account
ALTER TABLE t_user rename t_account;
-- 查看表结构
DESC t_account;
-- 名称再改回来
ALTER TABLE t_account rename t_user;

2).修改表的注释

ALTER TABLE t_user comment '用户表';

3).增加字段

ALTER TABLE t_user ADD added_by int(11) COMMENT '新增人';

如果需要将字段增加到指定列的后面，比如：现在需要给表增加一个字段remark，希望将其放在status的前面，则可以这样编写脚本：

ALTER TABLE my_database.t_user ADD remark VARCHAR(500) COMMENT '备注' AFTER phone;
-- or
ALTER TABLE my_database.t_user ADD COLUMN remark VARCHAR(500) COMMENT '备注' AFTER phone;

after 列名 需要放在末尾，下面的写法是错误的：

ALTER TABLE t_user ADD remark VARCHAR(500) AFTER PHONE COMMENT '备注';

4).增加自增列

适用于建表时没有自增列，后期需要增加的场景：

-- 增加自增列，并指定其为第一列
ALTER TABLE my_database.t_user ADD COLUMN pid int(14) PRIMARY KEY AUTO_INCREMENT AFTER FIRST;

说明：

该方法新增的自增列，会追加为表的最后一列。
自增列的字段，需要同时设置为主键

如果表中已经有自增列，再次执行上面的语句，会报如下异常：

>1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

意思是：

不正确的表定义；表中只能有一个自增列，并且自增列必须定义为主键。

更通常的情况，是需要将 已经存在的列 设置为自增列：

表t_user的表结构为：t_user(id, name,phone,status,valid)

如果在创建表的时候，id没有设置为主键，也不是自增列

此时可这样处理：

-- 1.将已存在的列id设为主键
ALTER TABLE my_database.t_user ADD PRIMARY KEY(id);

-- 2.设置主键为增长
ALTER TABLE my_database.t_user MODIFY id bigint AUTO_INCREMENT;

5).修改字段的名称和类型

字段名added_by重命名为created_by，数据类型：int -> bigint。

ALTER TABLE t_user CHANGE added_by created_by 
bigint(0) COMMENT '新增人';

6).修改字段的注释和类型

ALTER TABLE t_user MODIFY created_by bigint(0) COMMENT '创建人';

7).删除列

-- 删除created_by列
ALTER TABLE t_user DROP COLUMN created_by;

2.3.1、删除表结构

当某张表不再需要时，可以使用drop命令进行删除：

DROP TABLE t_user;

2.4、删除数据库

当某个库不再需要时，也可以使用drop命令进行删除：

DROP DATABASE my_database;

三、DML操作

3.1、新增数据

新增一行数据：

INSERT INTO t_user(`name`,phone)
values('lucy','15210023001');

新增多行数据：

INSERT INTO t_user(`name`,phone) values('lily','15210023002'),
('zhang_san','15210023003'),('li_si','15210023004');

3.2、查询数据

select * from my_database.t_user;

结果如下：

+----+-----------+-------------+--------+-------+
| id | name      | phone       | status | valid |
+----+-----------+-------------+--------+-------+
|  1 | lucy      | 15210023011 |     10 |     1 |
|  2 | lily      | 15210023002 |     10 |     1 |
|  3 | zhang_san | 15210023003 |     10 |     1 |
|  4 | li_si     | 15210023004 |     10 |     1 |
+----+-----------+-------------+--------+-------+
4 rows in set (0.03 sec)

where语句
where语句后面的称之为条件判断，而其中连接字段和值的称之为操作符；多个条件之间用and或是or进行连接。
例子：

select * from my_database.t_user
where valid=1 and `status` = 10;

常见操作符如下：

>、<、>=、<=、=、!=、between...and...、like、not null、is null

条件逻辑操作符如下：

and、 or

排序

-- 按id升序排列
select * from my_database.t_user order by id;

-- 按id降序排列
select * from my_database.t_user order by id desc;

取指定条数的记录

其中使用到LIMIT关键字，可用于实现真分页：

-- 按id升序排列，取前10条记录
select * from my_database.t_user order by id limt 10;

-- 跳过前10条记录，取第二个10条数据
select * from my_database.t_user order by id limit 10,10;

真分页的原理：

-- 跳过前10条记录，取第二个10条数据
select * from my_database.t_user 
order by id limit $page_size*($current_page-1),$page_size;

3.3、修改数据

将lucy的电话号码改为：15210023011

-- 为了防止误删，都是使用主键作为where条件
update t_user set phone = '15210023011'
where id=1 and name='lucy';

因为where语句的原因，update语句可以一次更新多条记录。

3.4、删除数据

删除表中数据有两种方法：

delete 语句
truncate 语句

1.删除单行数据
删除用户表中 li_si 的数据：

-- 为了防止误删，都是使用主键作为where条件
delete from t_user where id=4 and name='li_si';

2.使用delete删除表
删除整表的数据：

delete from t_user;

3.使用truncate 删除表

truncate会直接删除全表数据，且不可恢复：

truncate t_user;

两种方式的区别：

delete仅删除内容，并且会在事务日志中增加一条记录，所以可以通过事务日志恢复数据；但是此方法非常占用空间。
删除数据后不可恢复（通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放）。执行速度比delete方式快，占用空间少。

四、问题

4.1、如何查看MySQL的版本？

答：可以使用如下命令：

select version();

4.2、ALTER TABLE CHANGE 和 ALTER TABLE MODIFY有什么不同？

答：前一个功能可用于重命名列名；后一个只改列的属性，不改列名；
如果是修改列的注释，建议用第二个。

参考资料：

https://dev.mysql.com/doc/refman/8.0/en/alter-table-examples.html

数据库的字符集可以修改
创建数据表时，可以指定其字符集，可以与数据库默认的字符集不同。

五、总结

本文讲解了关于数据库的常见操作：库的创建，库中表的查看，表的创建，表结构的修改与查看，表的删除和库的删除。这些都属于DDL的范畴。

然后又讲了针对数据表的常用操作（DML）：数据的新增、查询、修改和删除。

相关内容
[MySQL实践] MySQL中的用户创建与授权

参考资料

写代码的老书生

关注

23
点赞
踩
29

收藏

觉得还不错? 一键收藏
0
评论
[MySQL实践] MySQL中的数据库与表操作

本文讲解了关于数据库的常见操作：库的创建，库中表的查看，表的创建，表结构的修改与查看，表的删除和库的删除。这些都属于DDL的范畴。然后又讲了针对数据表的常用操作（DML）：数据的新增、查询、修改和删除。
复制链接

扫一扫