MySQL数据库：对库和表的基本操作

最新推荐文章于 2024-07-23 14:05:29 发布

【Shine】光芒

最新推荐文章于 2024-07-23 14:05:29 发布

阅读量959

点赞数 22

分类专栏： MySQL数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_43908419/article/details/134957169

版权

MySQL数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

一. 对数据库的基本认识

1.1 数据库的概念

数据库，是对数据进行存储、管理的一种工具，可以被视为一种电子化的文档柜，能够高效的支持数据的插入、删除、查询等操作。

在我们一般的认识中，数据是存储在文件中的，我们对文件进行操作就可以对数据进行管理，那么，既然有了文件为什么还要有数据库呢？

使用文件保存数据，具有以下缺陷：

文件存储数据的安全性无法保证。
文件不利于对数据进行查询、修改等操作。
文件不利于管理海量数据。
文件在被程序读和写的时候不够方便。

针对文件管理数据的上述缺点，数据库作为一种高效、便捷的数据管理工具被开发了出来，用于对数据进行高效便捷的管理。

1.2 主流的数据库

SQL Sever：微软开发的数据库，.Net程序员经常使用。
Oracle：甲骨文公司的产品，适用于大型项目，并发性不如MySQL。
MySQL：最受欢迎、应用最广泛的关系型数据库，开源、免费。
PostgreSQL：加州伯克利大学计算机系开发的数据库，可以免费使用并且修改分发。
SQLite：是一款轻量级关系型数据库，占用资源很小，常用于嵌入式设备中。

1.3 数据存储逻辑

数据库是通过“表”的方式来对数据进行管理的，在每一个database中，可能存有若干张表，图1.1就是典型的数据库表结构，这是一张存储学生信息的表，其中包含学生学号、年龄、性别等信息。

如图1.2所示，MySQL数据库是一种网络服务，本地客户端通过网络连接到远端服务器上的MySQL服务进程进行操作，在一台服务器的MySQL服务中，可能存在多个库(database)，每个database中有多张表，记录着不同的数据。

1.4 MySQL的架构

MySQL是一款可以在不同平台上进行移植的数据库，可以运行于Linux、Windows、Mac等多种操作系统中，不同系统中的MySQL底层实现略有不同，但是整体框架都符合图1.3的架构。

在MySQL中，最核心的是存储引擎，通过指令show engines，可以查看MySQL数据可所支持的存储引擎，如果1.4就是通过指令查看MySQL所支持的存储引擎的结果，其中最常用的存储引擎是InnoDB和MyISAM，这两种引擎的底层都是通过B树来实现检索的。

1.5 MySQL指令分类及运行登录

根据MySQL指令可以实现的不同功能，可以将指令分为四类：

DDL：数据定义语言，用于操作和维护结构，代表指令：create、drop、alter。
DML：数据操纵语言，用于对数据本身进行操作维护，代表指令：insert、delete、update。
DQL：数据查询语言，代表指令有：select。
DCL：数据控制语言，主要负责权限管理和事务，代表指令：grant、revoke、commit。

如果当前的主机上安装有MySQL服务进程，那么通过下面的三条指令，可分别实现对MySQL服务进程的启动、停止和重新启动：

启动MySQL服务：systemctl start mysqld
暂停MySQL服务：systemctl stop mysqld
重启MySQL服务：systemctl restart mysqld

在启动MySQL服务进程后，就可以登录MySQL进行对应的操作。

MySQL登录指令为：mysql -h 主机IP -P 端口号 -u 用户 -p

如果是在本地主机进行登录，那么主机IP和端口号可以直接省略，相当于进行本地环回。

MySQL的登录和启动指令中，涉及到了mysqld和mysql，其中mysqld表示MySQL服务端，mysql表示客户端。

二. 对库的基本操作

2.1 库的创建、删除和查看

查看当前MySQL中所有的数据库：show databases;
创建数据库：create database 数据库名称;
删除数据库：drop database 数据库名称;

mysql> create database test1;    -- 创建名为test1的数据库
Query OK, 1 row affected (0.00 sec)

mysql> show databases;  -- 此时可以查看到新创建的test1数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database test1;  -- 删除test1数据库
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;  -- 此时查不到test1数据库了
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

每当通过create database创建一个数据库后，都会在/var/lib/mysql路径下创建一个与数据库名称相同的文件夹，这个文件夹中就会存有这个数据库将来插入的数据。

注意：如果我们直接对/var/lib/mysql路径下的文件夹进行删除、创建等操作，也可以实现对数据库的创建和删除，但是这么做十分不推荐，通过mysql中的指令创建和删除数据库才是正确的。

显示创建语句：show create database 数据库名称;

数据库名称加双引号' '，是为了防止数据库名称和关键字发生冲突。
/*!40100 DEFAULT ...*/ 并不是注释，而是代表当前的MySQL数据库大于4.01版本。

mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

mysql> show create database test1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

2.2 数据库的使用

如果我们希望对特定的某个数据可进行操作，如创建表示、查询等，就需要确定所要进行操作的数据库，正是当前所使用的数据库。

使用某个数据库：use 数据库名称;
查看当前数据库：select database();

mysql> use test1;  -- 使用test1数据库
Database changed
mysql> select database();  -- 查看当前数据库，为test1
+------------+
| database() |
+------------+
| test1      |
+------------+
1 row in set (0.00 sec)

2.3 数据库字符集和校验集

创建每一个数据库时，都有两个编码集：

数据库字符集：数据库将来要存储的数据类型的编码集。
数据库校验集：数据库将来进行比较、查询操作所使用的编码集。

在数据库中，对数据进行的任何操作，就必须要保证操作和编码一致，以防出现不可预知的错误。

下面两条指令，可以查看MySQL所支持的数据库字符集和数据库校验集：

查看MySQL支持的数据库字符集：show charset;
查看MySQL支持的数据库校验集：show collation;

以下两条指令，可以查看当前数据库所使用的字符集和校验集：

查看当前数据库使用的字符集：show variables like 'character_set_database';
查看当前数据库使用的校验集：show variables like 'collation_database';

如果在创建数据库的时候不显示指出其使用的字符集和校验集，那么就采用默认的，MySQL数据库默认的字符集为utf8，默认的校验集为utf8_general_database。

mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'collation_database';
+--------------------+-----------------+
| Variable_name      | Value           |
+--------------------+-----------------+
| collation_database | utf8_general_ci |
+--------------------+-----------------+
1 row in set (0.00 sec)

创建数据库的时候可以显示给出字符集和校验集，下面两种语法都可以显示给定字符集：

create database 数据库名 charset=字符集;
create database 数据库名 character set 字符集;

显示给定校验集：create database 数据库名 collate 校验集

同时给出字符集和校验集：create database 数据库名 charset=字符集 character set 校验集;

mysql> create database test_database charset=gbk collate gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)

mysql> use test_database;
Database changed
mysql> show variables like 'character_set_database'
    -> ;
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | gbk   |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'collation_database';
+--------------------+----------------+
| Variable_name      | Value          |
+--------------------+----------------+
| collation_database | gbk_chinese_ci |
+--------------------+----------------+
1 row in set (0.01 sec)

还可以通过alter指令，来显示的修改数据库的字符集和校验集。

修改数据库字符集和编码集：alter database 数据库名称 charset=新字符集 collate 新校验集;

如下代码所示，原来数据的字符集和校验集分别为gbk和gbk_chinese_ci，将字符集更改为utf8，校验集随之变为了utf8_general_ci，这表明更改字符集操作，校验集也会随之改变，同理更改校验集，字符集也会随之改变。

mysql> alter database test_database charset=utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'collation_database';
+--------------------+-----------------+
| Variable_name      | Value           |
+--------------------+-----------------+
| collation_database | utf8_general_ci |
+--------------------+-----------------+
1 row in set (0.00 sec)

2.4 数据库的备份和恢复

在实际项目中，删除数据库是十分不推荐的，如果要删除，那就必须预先进行备份，以便将来需要的时候将数据库恢复出来。

数据库备份语法：mysqldump -P 端口号 -u 用户 -p 密码 -B 数据库名称 > 备份路径/xxx.sql
数据库恢复语法：mysql > source 备份路径;

如何在数据库备份的时候不携带-B选项，那么在恢复数据库的时候，就必须预先创建空数据库，然后进行恢复。

备份多个数据库：mysqldump -P端口号 -u用户 -p密码 -B 数据库1 数据库2 ... > 备份路径
对数据库中的表备份：mysqldump -P端口号 -u用户 -p密码 -B 数据库表1 表2 ... > 备份路径

数据库备份：

[root@VM-8-5-centos MySQL]# mysqldump -P3306 -uroot -p -B test_database > /root/MySQL/test.sql  -- 将test_database数据库备份
Enter password: 
[root@VM-8-5-centos MySQL]# ll
total 36
-rw-r--r-- 1 root root 25680 Dec  4 14:16 mysql57-community-release-el7.rpm
-rw-r--r-- 1 root root  1418 Dec 12 22:08 test.sql   -- test_database数据库备份文件   
-rw-r--r-- 1 root root  2036 Dec 12 08:58 users.sql
[root@VM-8-5-centos MySQL]#

数据库恢复：

mysql> source /root/MySQL/test.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

三. 对表的基本操作

3.1 表的创建、删除和查看

在MySQL数据库中，有效数据是存储在表中的，那么我们在向数据库中插入数据前，就必须保证数据库中有表的存在。

删除表语法：drop table 表名称;

创建表的语法：

create table 表名称 {

成员名1 成员类型1 comment 'XXX1',

成员名2 成员类型2 comment 'XXX2',

.... ....

} charset=字符集 collate 校验集 engine 存储引擎;

在上述创建表的语法中，comment 'XXX'和编码集的指定可以省略，如果省略编码集，那么字符集和校验集，都会采用默认的。

下面是创建表的案例，创建名为student的表，成员包括name、age、id、gender。

mysql> create table student (
    -> name varchar(20) comment '学生姓名',
    -> age tinyint comment '学生年龄',
    -> id varchar(15) comment '学生学号',
    -> gender char(2) comment '学生性别'
    -> )charset=utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.11 sec)

查看当前数据库中的所有表：show tables;
查看表的详细信息：desc 表名称;

mysql> show tables;
+-------------------------+
| Tables_in_test_database |
+-------------------------+
| student                 |
+-------------------------+
1 row in set (0.00 sec)

mysql> desc student;   -- 查看表student的属性信息
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(20) | YES  |     | NULL    |       |
| age    | tinyint(4)  | YES  |     | NULL    |       |
| id     | varchar(15) | YES  |     | NULL    |       |
| gender | char(2)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

下面两条指令可以查看表的创建语句：

show create table 表名称;
show create table 表名称 \G

用'\G'替换';'，可以实现格式化的显示输出。

mysql> show create table student \G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `name` varchar(20) DEFAULT NULL COMMENT '学生姓名',
  `age` tinyint(4) DEFAULT NULL COMMENT '学生年龄',
  `id` varchar(15) DEFAULT NULL COMMENT '学生学号',
  `gender` char(2) DEFAULT NULL COMMENT '学生性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3.2 向表中插入数据

向表中插入数据语法：insert into 表名称 (成员1, 成员2, ... ...) values ( 值1,值2, ... ... );
查看表中全部数据：select * from 表名称;

向表中插入数据是以行为单位的，如果要给每个成员都赋一个值，那么可以在插入语句中省略对于成员名的指定。在查看表中全部数据信息的语法中，*表示通配符。

mysql> insert into student values ('张三', 20, '1223', '男');
Query OK, 1 row affected (0.05 sec)

mysql> insert into student (name, age, gender) values ('王五', 22, '女');
Query OK, 1 row affected (0.03 sec)

mysql> select * from student;
+--------+------+------+--------+
| name   | age  | id   | gender |
+--------+------+------+--------+
| 张三   |   20 | 1223 | 男     |
| 王五   |   22 | NULL | 女     |
+--------+------+------+--------+
2 rows in set (0.00 sec)

如果希望筛选出表中特定的信息，那么也可以通过select指令来进行，如下代码所示，筛选出表中age=20的学生信息，通过 select + where 可以实现。

mysql> select * from student where age=20;
+--------+------+------+--------+
| name   | age  | id   | gender |
+--------+------+------+--------+
| 张三   |   20 | 1223 | 男     |
+--------+------+------+--------+
1 row in set (0.01 sec)

3.3 对表的更改

修改表的名称：alter table 表名称 rename to 新名称;

将原来数据库中的表student更改为Stu，在更改前后通过show tables指令查看全部的表，可以发现更名成功了。

mysql> show tables;
+-------------------------+
| Tables_in_test_database |
+-------------------------+
| student                 |
+-------------------------+
1 row in set (0.00 sec)

mysql> alter table student rename to Stu;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-------------------------+
| Tables_in_test_database |
+-------------------------+
| Stu                     |
+-------------------------+
1 row in set (0.00 sec)

在表中添加新的列：alter table 表名称 add 新列名新列类型 comment 'XXX' after 添加位置;

在添加新列的语法中，comment和after可以省略，如果after，那么新列的位置默认会在原来的表的最后一列的后面。下面的代码在上面Stu表的id列后面添加address列，在最后添加sources列，并在添加新列前后通过desc Stu 查看表结构。

mysql> desc Stu;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(20) | YES  |     | NULL    |       |
| age    | tinyint(4)  | YES  |     | NULL    |       |
| id     | varchar(15) | YES  |     | NULL    |       |
| gender | char(2)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table Stu add address varchar(20) comment '地址' after id;
Query OK, 0 rows affected (0.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table Stu add source int;
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc Stu;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| age     | tinyint(4)  | YES  |     | NULL    |       |
| id      | varchar(15) | YES  |     | NULL    |       |
| address | varchar(20) | YES  |     | NULL    |       |
| gender  | char(2)     | YES  |     | NULL    |       |
| source  | int(11)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

更改列名称：alter table 表名称 change 原列名新列名新类型;

更改列类型：alter table 表名称 modify 列名称新类型;

mysql> alter table Stu change id stuId varchar(20); -- 将id更名为stuId并更改类型
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table Stu modify gender varchar(5); -- 更改gender的类型为varchar(5)
Query OK, 2 rows affected (0.50 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc Stu;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| age     | tinyint(4)  | YES  |     | NULL    |       |
| stuId   | varchar(20) | YES  |     | NULL    |       |
| address | varchar(20) | YES  |     | NULL    |       |
| gender  | varchar(5)  | YES  |     | NULL    |       |
| source  | int(11)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)