MySQL数据库中，数据库及表的创建和管理

一、底子知识

（一）、标志符的定名规则

1.数据库名、表名不得超过30个字符，变量名限定为29个
2.必须只能包含 A–Z, a–z, 0–9, _共63个字符
3.数据库名、表名、字段名等对象名中间不要包含空格
4.同一个MySQL软件中，数据库不能同名；同一个数据库中，表不能重名；同一个表中，字段不能重名
5.必须保证你的字段没有和生存字、数据库系统或常用方法辩论。如果有辩论并要坚持使用，则需要在SQL语句中使用`（着重号）引起来
6.保持字段名和范例的一致性：在定名字段并为其指定数据范例的时间肯定要保证一致性，如果数据范例在一个表里是整型，那么在另一个表中也要保持为整型
（二）、MySQL中的数据范例

整型：INT、BIGINT
浮点范例：FLOAT、 DOUBLE
定点数范例：DECIMAL
位范例：BIT
日期时间范例：YEAR、TIME、DATE、DATETIME
文本字符串范例：CHAR、 VARCHAR
摆列范例：ENUM
聚集范例：SET
二进制字符串范例：BINARY、 VARBINARY
JSON范例：JSON对象、JSON数组
对常用数据范例的描述 ：
INT：存储大小为 4个字节 。
CHAR（size)：定长字符数据。可不指定size大小，若未指定，默认为1个字符。若指定，size最大值为255。
VARCHAR(size) ：可变长字符数据，根据字符串实际长度生存，但必须指定长度。
FLOAT(M,D) 单精度：占用4个字节，M=该数值的总位数（整数位数+小数位数），D=小数位数。
DOUBLE(M,D) 双精度：占用8个字节，M=该数值的总位数（整数位数+小数位数），D=小数位数。
DECIMAL(M,D) 高精度小数：占用M+2个字节。
DATE 日期型数据：格式’YYYY-MM-DD’。
二、数据库的创建与管理

（一）、创建数据库

方法1：create database 数据库名；

1. mysql> create database test01;
2. Query OK, 1 row affected (0.01 sec)

复制代码

方法2：create database 数据库名 character set 字符集；

1. mysql> create database test02 character set 'utf8mb4';
2. Query OK, 1 row affected (0.02 sec)

复制代码

方法3：create database if not exists 数据库名；

1. mysql> create database if not exists test03;
2. Query OK, 1 row affected (0.01 sec)

复制代码

（二）、使用数据库

1.查察当前连接中的所有数据库
格式：show databases;

1. mysql> show databases;
2. +--------------------+
3. | Database           |
4. +--------------------+
5. | information_schema |
6. | mysql              |
7. | performance_schema |
8. | sys                |
9. | test01             |
10. | test02             |
11. | test03             |
12. +--------------------+
13. 7 rows in set (0.00 sec)

复制代码

2.查察当前正在使用的数据库
格式：select database() from dual;

1. mysql> use test01;
2. Database changed
3. mysql> select database() from dual;
4. +------------+
5. | database() |
6. +------------+
7. | test01     |
8. +------------+
9. 1 row in set (0.00 sec)

复制代码

3.查察指定库中的所有表
格式：show tables from 数据库名；

1. mysql> use test01;
2. Database changed
3. mysql> show tables from mysql;
4. +------------------------------------------------------+
5. | Tables_in_mysql                                      |
6. +------------------------------------------------------+
7. | columns_priv                                         |
8. | component                                            |
9. | db                                                   |
10. | default_roles                                        |
11. | engine_cost                                          |
12. | func                                                 |
13. | general_log                                          |
14. | global_grants                                        |
15. | gtid_executed                                        |
16. | help_category                                        |
17. | help_keyword                                         |
18. | help_relation                                        |
19. | help_topic                                           |
20. | innodb_index_stats                                   |
21. | innodb_table_stats                                   |
22. | ndb_binlog_index                                     |
23. | password_history                                     |
24. | plugin                                               |
25. | procs_priv                                           |
26. | proxies_priv                                         |
27. | replication_asynchronous_connection_failover         |
28. | replication_asynchronous_connection_failover_managed |
29. | replication_group_configuration_version              |
30. | replication_group_member_actions                     |
31. | role_edges                                           |
32. | server_cost                                          |
33. | servers                                              |
34. | slave_master_info                                    |
35. | slave_relay_log_info                                 |
36. | slave_worker_info                                    |
37. | slow_log                                             |
38. | tables_priv                                          |
39. | time_zone                                            |
40. | time_zone_leap_second                                |
41. | time_zone_name                                       |
42. | time_zone_transition                                 |
43. | time_zone_transition_type                            |
44. | user                                                 |
45. +------------------------------------------------------+
46. 38 rows in set (0.01 sec)

复制代码

  若想查察当前数据库中的所有表，可不消指明数据库名直接写为：show tables;

1. mysql> use test01;
2. Database changed
3. show tables;
4. Empty set (0.00 sec)

复制代码

4.查察数据库的创建信息
方法1：show create database 数据库名；

1. mysql> show create database test01;
2. +----------+----------------------------------------------------------------------------------------------------------------------------------+
3. | Database | Create Database
4.                       |
5. +----------+----------------------------------------------------------------------------------------------------------------------------------+
6. | test01   | CREATE DATABASE `test01` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
7. +----------+----------------------------------------------------------------------------------------------------------------------------------+
8. 1 row in set (0.00 sec)

复制代码

方法2：show create database 数据库名\G

1. mysql> show create database test01\G
2. *************************** 1. row ***************************
3.        Database: test01
4. Create Database: CREATE DATABASE `test01` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
5. 1 row in set (0.00 sec)

复制代码

5.切换数据库
格式：use 数据库名；

1. mysql> use test02;
2. Database changed

复制代码

（三）、修改数据库

   数据库的修改一样平常是修改数据库的字符集 字符集一样平常有：utf8、gbk等.
  格式：alter database 数据库名 character set 字符集;

1. alter database test01 character set 'utf8';
2. Query OK, 1 row affected, 1 warning (0.01 sec)

复制代码

（四）、删除数据库.

方法1：
drop database 数据库名；

1. mysql> drop database test02;
2. Query OK, 0 rows affected (0.01 sec)

复制代码

方法2：
drop database if exists 数据库名；

1. drop database if exists test01;
2. Query OK, 0 rows affected (0.01 sec)

复制代码

三、表的创建与管理

（一）、表的创建

方式1：
create table if not exists 表名(
字段1 数据范例 [约束条件] [默认值],
字段2 数据范例 [约束条件] [默认值],
字段3 数据范例 [约束条件] [默认值],
……
[表约束条件]
);

1. create table if not exists emp01
2.     -> (id int,name varchar(20),sex char(1));
3. Query OK, 0 rows affected (0.04 sec)

复制代码

方式2：
create table 表名(
字段1 数据范例 [约束条件] [默认值],
字段2 数据范例 [约束条件] [默认值],
字段3 数据范例 [约束条件] [默认值],
……
[表约束条件]
);

1. mysql> create table emp02
2.     -> (id int,name varchar(20),sex char(1));
3. Query OK, 0 rows affected (0.02 sec)

复制代码

  加上了if not exists，则表现：如果当前数据库中不存在要创建的数据表，则创建数据表；如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。
创建 表时，表名，表中各个字段名，以及字段相应的数据范例（长度）肯定要指明。
数据范例int后面也可以指定长度，表现的是数据的显示宽度，默认显示宽度为11.一样平常情况下推荐int范例数据指定长度。
  方式3：通过其他表的查询结果来创建表
根据查询结果创建新表（导入了查询的数据）
格式1：
create table 表名
as
select * from employees;
若想创建的仅仅是空表则可以通过条件where 1=2得到空数据，以次实现创建空表
格式：
create table 表名
as
select * from employees
where 1=2；

1. desc emp01;
2. +-------+-------------+------+-----+---------+-------+
3. | Field | Type        | Null | Key | Default | Extra |
4. +-------+-------------+------+-----+---------+-------+
5. | id    | int         | YES  |     | NULL    |       |
6. | name  | varchar(20) | YES  |     | NULL    |       |
7. | sex   | char(1)     | YES  |     | NULL    |       |
8. +-------+-------------+------+-----+---------+-------+
9. 3 rows in set (0.01 sec)
11. mysql> create table emp03
12.     -> as
13.     -> select * from emp01
14.     -> where 1=2;
15. Query OK, 0 rows affected (0.02 sec)
16. Records: 0  Duplicates: 0  Warnings: 0
18. mysql> desc emp03;
19. +-------+-------------+------+-----+---------+-------+
20. | Field | Type        | Null | Key | Default | Extra |
21. +-------+-------------+------+-----+---------+-------+
22. | id    | int         | YES  |     | NULL    |       |
23. | name  | varchar(20) | YES  |     | NULL    |       |
24. | sex   | char(1)     | YES  |     | NULL    |       |
25. +-------+-------------+------+-----+---------+-------+
26. 3 rows in set (0.00 sec)
28. select * from emp03;
29. Empty set (0.00 sec)

复制代码

  如果要查询的表不在当前地点的库中，可以使用库名.表名在当前库中查询其他库中的表。
  （二）、查察创建的表结构

方法1；
describe/desc 表名；

1. mysql> desc emp01;
2. +-------+-------------+------+-----+---------+-------+
3. | Field | Type        | Null | Key | Default | Extra |
4. +-------+-------------+------+-----+---------+-------+
5. | id    | int         | YES  |     | NULL    |       |
6. | name  | varchar(20) | YES  |     | NULL    |       |
7. | sex   | char(1)     | YES  |     | NULL    |       |
8. +-------+-------------+------+-----+---------+-------+
9. 3 rows in set (0.00 sec)
11. mysql> describe emp01;
12. +-------+-------------+------+-----+---------+-------+
13. | Field | Type        | Null | Key | Default | Extra |
14. +-------+-------------+------+-----+---------+-------+
15. | id    | int         | YES  |     | NULL    |       |
16. | name  | varchar(20) | YES  |     | NULL    |       |
17. | sex   | char(1)     | YES  |     | NULL    |       |
18. +-------+-------------+------+-----+---------+-------+
19. 3 rows in set (0.00 sec)

复制代码

方法2：
show create 表名；或 show create 表名\G

1. mysql> show create table emp01;
2. +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3. | Table | Create Table
4.                                                                      |
5. +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6. | emp01 | CREATE TABLE `emp01` (
7.   `id` int DEFAULT NULL,
8.   `name` varchar(20) DEFAULT NULL,
9.   `sex` char(1) DEFAULT NULL
10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
11. +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12. 1 row in set (0.00 sec)
14. mysql> show create table emp01\G
15. *************************** 1. row ***************************
16.        Table: emp01
17. Create Table: CREATE TABLE `emp01` (
18.   `id` int DEFAULT NULL,
19.   `name` varchar(20) DEFAULT NULL,
20.   `sex` char(1) DEFAULT NULL
21. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
22. 1 row in set (0.00 sec)

复制代码

  使用show create table语句不但可以查察表创建表时的详细语句，还可以查察存储引擎和字符编码。
  （三）、修改表

   修改表指的是修改数据库中已经存在的数据表的结构，使用 alter table 语句可以实现
  1.追加一个列
格式：
alter table 表名
add 【column】 字段名 字段范例 【first/after 字段名】；

1. desc emp01;
2. +-------+-------------+------+-----+---------+-------+
3. | Field | Type        | Null | Key | Default | Extra |
4. +-------+-------------+------+-----+---------+-------+
5. | id    | int         | YES  |     | NULL    |       |
6. | name  | varchar(20) | YES  |     | NULL    |       |
7. | sex   | char(1)     | YES  |     | NULL    |       |
8. +-------+-------------+------+-----+---------+-------+
9. 3 rows in set (0.00 sec)
11. mysql> alter table emp01
12.     -> add column age int after name;
13. Query OK, 0 rows affected (0.05 sec)
15. mysql> desc emp01;
16. +-------+-------------+------+-----+---------+-------+
17. | Field | Type        | Null | Key | Default | Extra |
18. +-------+-------------+------+-----+---------+-------+
19. | id    | int         | YES  |     | NULL    |       |
20. | name  | varchar(20) | YES  |     | NULL    |       |
21. | age   | int         | YES  |     | NULL    |       |
22. | sex   | char(1)     | YES  |     | NULL    |       |
23. +-------+-------------+------+-----+---------+-------+
24. 4 rows in set (0.00 sec)

复制代码

**2.修改一个列：**可以修改列的数据范例，长度、默认值和位置
格式：
alter table 表名
modify 字段名1 字段范例 【default 默认值】【first /after 段名2】;

1. mysql> desc emp01;
2. +-------+-------------+------+-----+---------+-------+
3. | Field | Type        | Null | Key | Default | Extra |
4. +-------+-------------+------+-----+---------+-------+
5. | id    | int         | YES  |     | NULL    |       |
6. | name  | varchar(20) | YES  |     | NULL    |       |
7. | age   | int         | YES  |     | NULL    |       |
8. | sex   | char(1)     | YES  |     | NULL    |       |
9. +-------+-------------+------+-----+---------+-------+
10. 4 rows in set (0.00 sec)
12. mysql> alter table emp01
13.     -> modify name varchar(10);
14. Query OK, 0 rows affected (0.07 sec)
15. Records: 0  Duplicates: 0  Warnings: 0
17. mysql> desc emp01;
18. +-------+-------------+------+-----+---------+-------+
19. | Field | Type        | Null | Key | Default | Extra |
20. +-------+-------------+------+-----+---------+-------+
21. | id    | int         | YES  |     | NULL    |       |
22. | name  | varchar(10) | YES  |     | NULL    |       |
23. | age   | int         | YES  |     | NULL    |       |
24. | sex   | char(1)     | YES  |     | NULL    |       |
25. +-------+-------------+------+-----+---------+-------+
26. 4 rows in set (0.00 sec)
28. mysql> alter table emp01
29.     -> modify name varchar(20) after sex;
30. Query OK, 0 rows affected (0.04 sec)
31. Records: 0  Duplicates: 0  Warnings: 0
33. mysql> desc emp01;
34. +-------+-------------+------+-----+---------+-------+
35. | Field | Type        | Null | Key | Default | Extra |
36. +-------+-------------+------+-----+---------+-------+
37. | id    | int         | YES  |     | NULL    |       |
38. | age   | int         | YES  |     | NULL    |       |
39. | sex   | char(1)     | YES  |     | NULL    |       |
40. | name  | varchar(20) | YES  |     | NULL    |       |
41. +-------+-------------+------+-----+---------+-------+
42. 4 rows in set (0.00 sec)

复制代码

3. 重定名一个列
格式：
alter table 表名
change【column】 字段名 新字段名 新字段数据范例;

1. mysql> desc emp01;
2. +-------+-------------+------+-----+---------+-------+
3. | Field | Type        | Null | Key | Default | Extra |
4. +-------+-------------+------+-----+---------+-------+
5. | id    | int         | YES  |     | NULL    |       |
6. | age   | int         | YES  |     | NULL    |       |
7. | sex   | char(1)     | YES  |     | NULL    |       |
8. | name  | varchar(20) | YES  |     | NULL    |       |
9. +-------+-------------+------+-----+---------+-------+
10. 4 rows in set (0.00 sec)
12. mysql> alter table emp01
13.     -> change name name_info varchar(30);
14. Query OK, 0 rows affected (0.01 sec)
15. Records: 0  Duplicates: 0  Warnings: 0
17. mysql> desc emp01;
18. +-----------+-------------+------+-----+---------+-------+
19. | Field     | Type        | Null | Key | Default | Extra |
20. +-----------+-------------+------+-----+---------+-------+
21. | id        | int         | YES  |     | NULL    |       |
22. | age       | int         | YES  |     | NULL    |       |
23. | sex       | char(1)     | YES  |     | NULL    |       |
24. | name_info | varchar(30) | YES  |     | NULL    |       |
25. +-----------+-------------+------+-----+---------+-------+
26. 4 rows in set (0.00 sec)

复制代码

4.删除一个列
格式：
alter table 表名
drop 【column】要删除的字段名

1. desc emp01;
2. +-----------+-------------+------+-----+---------+-------+
3. | Field     | Type        | Null | Key | Default | Extra |
4. +-----------+-------------+------+-----+---------+-------+
5. | id        | int         | YES  |     | NULL    |       |
6. | age       | int         | YES  |     | NULL    |       |
7. | sex       | char(1)     | YES  |     | NULL    |       |
8. | name_info | varchar(30) | YES  |     | NULL    |       |
9. +-----------+-------------+------+-----+---------+-------+
10. 4 rows in set (0.00 sec)
12. mysql> alter table emp01
13.     -> drop sex;
14. Query OK, 0 rows affected (0.04 sec)
15. Records: 0  Duplicates: 0  Warnings: 0
17. mysql> desc emp01;
18. +-----------+-------------+------+-----+---------+-------+
19. | Field     | Type        | Null | Key | Default | Extra |
20. +-----------+-------------+------+-----+---------+-------+
21. | id        | int         | YES  |     | NULL    |       |
22. | age       | int         | YES  |     | NULL    |       |
23. | name_info | varchar(30) | YES  |     | NULL    |       |
24. +-----------+-------------+------+-----+---------+-------+
25. 3 rows in set (0.00 sec)

复制代码

5.重定名表
方法1：
rename table 表名
to 新表名；

1. mysql> show tables;
2. +------------------+
3. | Tables_in_test03 |
4. +------------------+
5. | emp01            |
6. | emp02            |
7. | emp03            |
8. +------------------+
9. 3 rows in set (0.00 sec)
11. mysql> rename table emp01
12.     -> to emp01_info;
13. Query OK, 0 rows affected (0.03 sec)
15. mysql> show tables;
16. +------------------+
17. | Tables_in_test03 |
18. +------------------+
19. | emp01_info       |
20. | emp02            |
21. | emp03            |
22. +------------------+
23. 3 rows in set (0.00 sec)

复制代码

方法2：
alter table 表名
rename to 新表名；

1. mysql> show tables;
2. +------------------+
3. | Tables_in_test03 |
4. +------------------+
5. | emp01_info       |
6. | emp02            |
7. | emp03            |
8. +------------------+
9. 3 rows in set (0.00 sec)
11. mysql> alter table emp01_info
12.     -> rename to emp01;
13. Query OK, 0 rows affected (0.02 sec)
15. mysql> show tables;
16. +------------------+
17. | Tables_in_test03 |
18. +------------------+
19. | emp01            |
20. | emp02            |
21. | emp03            |
22. +------------------+
23. 3 rows in set (0.00 sec)

复制代码

**6.删除表—drop **(在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除。)
数据和结构都被删除，
开释表的存储空间，
所有正在运行的相关事务被提交，
所有相关索引被删除，
因此drop table 语句不能回滚。
格式：
drop table 【if exists】 表名1 [, 表名2, …, 表名n];
   if exists 的寄义为：如果当前数据库中存在相应的数据表，则删除数据表；如果当前数据库中不存在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。

1. mysql> show tables;
2. +------------------+
3. | Tables_in_test03 |
4. +------------------+
5. | emp01            |
6. | emp02            |
7. | emp03            |
8. +------------------+
9. 3 rows in set (0.00 sec)
11. mysql> drop table if exists emp02;
12. Query OK, 0 rows affected (0.04 sec)
14. mysql> show tables;
15. +------------------+
16. | Tables_in_test03 |
17. +------------------+
18. | emp01            |
19. | emp03            |
20. +------------------+
21. 2 rows in set (0.00 sec)

复制代码

7.清空表
删除表中所有的数据（只能全部清空）
但表结构生存，表还在
开释表的存储空间
truncate 语句不能回滚
格式：
truncate table 表名；
8.删除表—delete
删除表中的数据（不肯定全部清空，可以通过条件来指明要删除是哪部分数据）
表结构生存，表还在
开释表的存储空间
可以回滚
格式：
delete from 表名
where 条件；
   commit ：提交数据，一旦执行，则数据就被永久生存在数据库中，意味着数据不可回滚
rollback:回滚数据，一旦执行，可以实现数据的回滚，回滚到最近一次commit之后。
默认情况下 delete语句也是不可以实现回滚的，由于默认情况下会执行commit语句，但是在执行delete语句前，可以通过使用autocommit=false语句来实现不执行commit，从而实现回滚。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。