共计 7348 个字符，预计需要花费 19 分钟才能阅读完成。
本篇内容主要讲解“MySQL 管理数据库、数据表、数据的基本操作是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“MySQL 管理数据库、数据表、数据的基本操作是什么”吧!
注意：MySQL 数据库命令不区分大小写，但在 Mac 端如果想使用 tab 键自动补全命令，就必须使用大写。
1、数据库管理
1.1 create 创建数据库
mysql create database test;
Query OK, 1 row affected (0.50 sec)1.2 show 查看所有数据库
mysql show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.32 sec)1.3 alter 修改数据库
alter 命令修改数据库编码：
一般我们设置的字符集使用的都是 UTF8，若发现数据库编码不是 UTF8，可使用该语句更改数据库编码：
mysql alter database test character set utf8;
Query OK, 1 row affected (0.01 sec)1.4 use 使用数据库
mysql use test;
Database changed1.5 查看当前使用的数据库
mysql select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)1.6 drop 删除数据库
mysql drop database test;
Query OK, 0 rows affected (0.00 sec)2、数据库表管理
我们先创建一个数据库，以供我们后面使用：
mysql create database test;
Query OK, 1 row affected (0.06 sec)创建后，使用 use 命令进入数据库：
mysql use test;
Database changed2.1 create 创建表
mysql create table test(id int auto_increment primary key,
- name varchar(20) not null,
- age int not null,
- birthday datetime);
Query OK, 0 rows affected (0.14 sec)2.2 show 显示表
mysql show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)2.3 desc 查看表结构
mysql desc test;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
| birthday | datetime | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)2.4 alter 修改表结构（增、删、改）
2.4.1 add 在表中添加字段（列）
mysql alter table test add star bool;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0注意：在 MySQL 中布尔类型会自动转变为 tinyint(1) 类型
用 desc 查看可得到：
mysql desc test;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
| birthday | datetime | YES | | NULL | |
| star | tinyint(1) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)2.4.2 modify 修改表字段（列）
mysql alter table test modify star int;
Query OK, 0 rows affected (0.43 sec)
Records: 0 Duplicates: 0 Warnings: 0注意：int 可以指定长度，也可以不指定，不指定时默认长度为 int(11)，修改后再次用 desc 查看：
mysql desc test;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
| birthday | datetime | YES | | NULL | |
| star | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)2.4.3 drop 删除表字段（列）
mysql alter table test drop column star;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0查看表结构可以看到没有 star 字段了：
mysql desc test;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
| birthday | datetime | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)2.4.4 rename 重命名表名
mysql rename table test to people;
Query OK, 0 rows affected (0.01 sec)再次查看表格，发现表名已改变：
mysql show tables;
+----------------+
| Tables_in_test |
+----------------+
| people |
+----------------+
1 row in set (0.00 sec)2.5 create 利用已有数据创建新表
mysql create table newpeople select * from people;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0查看数据库存在的表发现已经有了：
mysql show tables;
+----------------+
| Tables_in_test |
+----------------+
| newpeople |
| people |
+----------------+
2 rows in set (0.00 sec)3、数据的操作及管理
3.1 增加数据（增）
mysql insert into people values(null, Anny ,22, 1990-09-09
Query OK, 1 row affected (0.00 sec)用 select 查询：
mysql select * from people;
+----+------+-----+---------------------+
| id | name | age | birthday |
+----+------+-----+---------------------+
| 1 | Anny | 22 | 1990-09-09 00:00:00 |
+----+------+-----+---------------------+
1 row in set (0.00 sec)3.2 删除数据（删）
mysql delete from people where name= Anny
Query OK, 1 row affected (0.00 sec)再用 select 查询，发现没有删除的数据了：
mysql select * from people;
+----+------+-----+---------------------+
| id | name | age | birthday |
+----+------+-----+---------------------+
| 2 | Lisa | 20 | 1992-09-09 00:00:00 |
+----+------+-----+---------------------+
1 row in set (0.00 sec)3.3 修改数据（改）
mysql update people set name= Anny where name= Lisa
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0再查数据可以看到 Lisa 名字变为 anny 了：
mysql select * from people;
+----+------+-----+---------------------+
| id | name | age | birthday |
+----+------+-----+---------------------+
| 2 | Anny | 20 | 1992-09-09 00:00:00 |
+----+------+-----+---------------------+
1 row in set (0.00 sec)3.4 查询数据（查）
最简单的就是下面的语句：
mysql select * from people;
+----+------+-----+---------------------+
| id | name | age | birthday |
+----+------+-----+---------------------+
| 2 | Anny | 20 | 1992-09-09 00:00:00 |
+----+------+-----+---------------------+
1 row in set (0.00 sec)* 表示所有字段，也可以指定字段查询，如下：
mysql select name from people;
+------+
| name |
+------+
| Anny |
+------+
1 row in set (0.00 sec)4、管理视图
4.1 创建视图
mysql create view people_view(name,age) as select name,age from people;
Query OK, 0 rows affected (0.00 sec)创建成功后可以查看视图：
mysql select * from people_view;
+------+-----+
| name | age |
+------+-----+
| Anny | 20 |
+------+-----+
1 row in set (0.00 sec)可以用 desc 查看视图字段：
mysql desc people_view;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)4.2 替换视图
创建或替换原有视图：
mysql create or replace view people_view (id,name,age) as select id,name,age from people;
Query OK, 0 rows affected (0.00 sec)查看现有视图：
mysql select * from people_view;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 2 | Anny | 20 |
| 3 | Lisa | 22 |
+----+------+-----+
2 rows in set (0.00 sec)4.3 操作视图
当视图有变化时，对应的表格也会跟着改变，即操作视图就是操作表格，我们在视图中插入一条数据：
mysql insert into people_view values(Lisa ,22);
Query OK, 1 row affected (0.08 sec)插入成功后，我们查看视图数据：
mysql select * from people_view;
+------+-----+
| name | age |
+------+-----+
| Anny | 20 |
| Lisa | 22 |
+------+-----+
2 rows in set (0.00 sec)可以看到视图中已经有了，再查看我们表格数据：
mysql select * from people;
+----+------+-----+---------------------+
| id | name | age | birthday |
+----+------+-----+---------------------+
| 2 | Anny | 20 | 1992-09-09 00:00:00 |
| 3 | Lisa | 22 | NULL |
+----+------+-----+---------------------+
2 rows in set (0.00 sec)发现也有了对应的数据
4.4 删除视图：
mysql drop view people_view;
Query OK, 0 rows affected (0.00 sec)到此，相信大家对“MySQL 管理数据库、数据表、数据的基本操作是什么”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
正文完
