MySQL数据库：对库和表的基本操作
目录
一.?对数据库的基本认识
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)
四.?总结
- 数据库是一对数据进行存储、管理的工具，数据库中的数据是以表的形式存储的。
- 通过特定的指令，可以实现对于数据库和表结构的增删查改工作。
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！