MySQL 案例实战--MySQL 数据库表操作

最新推荐文章于 2022-08-25 09:26:23 发布

Wan@Technology Stack

最新推荐文章于 2022-08-25 09:26:23 发布

阅读量216

点赞数 1

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

本文链接：https://blog.csdn.net/XY0918ZWQ/article/details/109251424

版权

MySQL 专栏收录该内容

31 篇文章 3 订阅

订阅专栏

MySQL 数据库表操作

前言
一、查看表
二、创建表
三、修改表

前言

本环境是基于 Centos 7.8 系统构建MySQL-5.7.14
具体构建，请参考 MySQL-5.7.14 环境构建

一、查看表

查看当前数据库中的表

mysql> use sys;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| host_summary                                  |
| host_summary_by_file_io                       |
| host_summary_by_file_io_type                  |
| host_summary_by_stages                        |
| host_summary_by_statement_latency             |
| host_summary_by_statement_type                |
| innodb_buffer_stats_by_schema                 |
| innodb_buffer_stats_by_table                  |
| innodb_lock_waits                             |
| io_by_thread_by_latency                       |
| io_global_by_file_by_bytes                    |
| io_global_by_file_by_latency                  |
| io_global_by_wait_by_bytes                    |
| io_global_by_wait_by_latency                  |
| latest_file_io                                |
| memory_by_host_by_current_bytes               |
| memory_by_thread_by_current_bytes             |
| memory_by_user_by_current_bytes               |
| memory_global_by_current_bytes                |
| memory_global_total                           |
| metrics                                       |
| processlist                                   |
| ps_check_lost_instrumentation                 |
| schema_auto_increment_columns                 |
| schema_index_statistics                       |
| schema_object_overview                        |
| schema_redundant_indexes                      |
| schema_table_lock_waits                       |
| schema_table_statistics                       |
| schema_table_statistics_with_buffer           |
| schema_tables_with_full_table_scans           |
| schema_unused_indexes                         |
| session                                       |
| session_ssl_status                            |
| statement_analysis                            |
| statements_with_errors_or_warnings            |
| statements_with_full_table_scans              |
| statements_with_runtimes_in_95th_percentile   |
| statements_with_sorting                       |
| statements_with_temp_tables                   |
| sys_config                                    |
| user_summary                                  |
| user_summary_by_file_io                       |
| user_summary_by_file_io_type                  |
| user_summary_by_stages                        |
| user_summary_by_statement_latency             |
| user_summary_by_statement_type                |
| version                                       |
| wait_classes_global_by_avg_latency            |
| wait_classes_global_by_latency                |
| waits_by_host_by_latency                      |
| waits_by_user_by_latency                      |
| waits_global_by_latency                       |
| x$host_summary                                |
| x$host_summary_by_file_io                     |
| x$host_summary_by_file_io_type                |
| x$host_summary_by_stages                      |
| x$host_summary_by_statement_latency           |
| x$host_summary_by_statement_type              |
| x$innodb_buffer_stats_by_schema               |
| x$innodb_buffer_stats_by_table                |
| x$innodb_lock_waits                           |
| x$io_by_thread_by_latency                     |
| x$io_global_by_file_by_bytes                  |
| x$io_global_by_file_by_latency                |
| x$io_global_by_wait_by_bytes                  |
| x$io_global_by_wait_by_latency                |
| x$latest_file_io                              |
| x$memory_by_host_by_current_bytes             |
| x$memory_by_thread_by_current_bytes           |
| x$memory_by_user_by_current_bytes             |
| x$memory_global_by_current_bytes              |
| x$memory_global_total                         |
| x$processlist                                 |
| x$ps_digest_95th_percentile_by_avg_us         |
| x$ps_digest_avg_latency_distribution          |
| x$ps_schema_table_statistics_io               |
| x$schema_flattened_keys                       |
| x$schema_index_statistics                     |
| x$schema_table_lock_waits                     |
| x$schema_table_statistics                     |
| x$schema_table_statistics_with_buffer         |
| x$schema_tables_with_full_table_scans         |
| x$session                                     |
| x$statement_analysis                          |
| x$statements_with_errors_or_warnings          |
| x$statements_with_full_table_scans            |
| x$statements_with_runtimes_in_95th_percentile |
| x$statements_with_sorting                     |
| x$statements_with_temp_tables                 |
| x$user_summary                                |
| x$user_summary_by_file_io                     |
| x$user_summary_by_file_io_type                |
| x$user_summary_by_stages                      |
| x$user_summary_by_statement_latency           |
| x$user_summary_by_statement_type              |
| x$wait_classes_global_by_avg_latency          |
| x$wait_classes_global_by_latency              |
| x$waits_by_host_by_latency                    |
| x$waits_by_user_by_latency                    |
| x$waits_global_by_latency                     |
+-----------------------------------------------+
101 rows in set (0.00 sec)

mysql>

查看其它数据库中的表

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables from sys;
+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| host_summary                                  |
| host_summary_by_file_io                       |
| host_summary_by_file_io_type                  |
| host_summary_by_stages                        |
| host_summary_by_statement_latency             |
| host_summary_by_statement_type                |
| innodb_buffer_stats_by_schema                 |
| innodb_buffer_stats_by_table                  |
| innodb_lock_waits                             |
| io_by_thread_by_latency                       |
| io_global_by_file_by_bytes                    |
| io_global_by_file_by_latency                  |
| io_global_by_wait_by_bytes                    |
| io_global_by_wait_by_latency                  |
| latest_file_io                                |
| memory_by_host_by_current_bytes               |
| memory_by_thread_by_current_bytes             |
| memory_by_user_by_current_bytes               |
| memory_global_by_current_bytes                |
| memory_global_total                           |
| metrics                                       |
| processlist                                   |
| ps_check_lost_instrumentation                 |
| schema_auto_increment_columns                 |
| schema_index_statistics                       |
| schema_object_overview                        |
| schema_redundant_indexes                      |
| schema_table_lock_waits                       |
| schema_table_statistics                       |
| schema_table_statistics_with_buffer           |
| schema_tables_with_full_table_scans           |
| schema_unused_indexes                         |
| session                                       |
| session_ssl_status                            |
| statement_analysis                            |
| statements_with_errors_or_warnings            |
| statements_with_full_table_scans              |
| statements_with_runtimes_in_95th_percentile   |
| statements_with_sorting                       |
| statements_with_temp_tables                   |
| sys_config                                    |
| user_summary                                  |
| user_summary_by_file_io                       |
| user_summary_by_file_io_type                  |
| user_summary_by_stages                        |
| user_summary_by_statement_latency             |
| user_summary_by_statement_type                |
| version                                       |
| wait_classes_global_by_avg_latency            |
| wait_classes_global_by_latency                |
| waits_by_host_by_latency                      |
| waits_by_user_by_latency                      |
| waits_global_by_latency                       |
| x$host_summary                                |
| x$host_summary_by_file_io                     |
| x$host_summary_by_file_io_type                |
| x$host_summary_by_stages                      |
| x$host_summary_by_statement_latency           |
| x$host_summary_by_statement_type              |
| x$innodb_buffer_stats_by_schema               |
| x$innodb_buffer_stats_by_table                |
| x$innodb_lock_waits                           |
| x$io_by_thread_by_latency                     |
| x$io_global_by_file_by_bytes                  |
| x$io_global_by_file_by_latency                |
| x$io_global_by_wait_by_bytes                  |
| x$io_global_by_wait_by_latency                |
| x$latest_file_io                              |
| x$memory_by_host_by_current_bytes             |
| x$memory_by_thread_by_current_bytes           |
| x$memory_by_user_by_current_bytes             |
| x$memory_global_by_current_bytes              |
| x$memory_global_total                         |
| x$processlist                                 |
| x$ps_digest_95th_percentile_by_avg_us         |
| x$ps_digest_avg_latency_distribution          |
| x$ps_schema_table_statistics_io               |
| x$schema_flattened_keys                       |
| x$schema_index_statistics                     |
| x$schema_table_lock_waits                     |
| x$schema_table_statistics                     |
| x$schema_table_statistics_with_buffer         |
| x$schema_tables_with_full_table_scans         |
| x$session                                     |
| x$statement_analysis                          |
| x$statements_with_errors_or_warnings          |
| x$statements_with_full_table_scans            |
| x$statements_with_runtimes_in_95th_percentile |
| x$statements_with_sorting                     |
| x$statements_with_temp_tables                 |
| x$user_summary                                |
| x$user_summary_by_file_io                     |
| x$user_summary_by_file_io_type                |
| x$user_summary_by_stages                      |
| x$user_summary_by_statement_latency           |
| x$user_summary_by_statement_type              |
| x$wait_classes_global_by_avg_latency          |
| x$wait_classes_global_by_latency              |
| x$waits_by_host_by_latency                    |
| x$waits_by_user_by_latency                    |
| x$waits_global_by_latency                     |
+-----------------------------------------------+
101 rows in set (0.00 sec)

mysql>

二、创建表

mysql> use db2;
Database changed
mysql> create table tb1(
    -> id int,
    -> name char(20)
    -> );
Query OK, 0 rows affected (0.35 sec)

mysql> 

#查看结果
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| tb1           |
+---------------+
1 row in set (0.00 sec)

mysql> 

#查看表结构
mysql> desc tb1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

表的约束

NOT NULL 非空

#创建数据库，且id 不能不为空
mysql> create table tb2(
    -> id int not null,
    -> name char(20)
    -> );
Query OK, 0 rows affected (0.36 sec)

#插入数据，当id为空时，插入数据失败
mysql> insert into tb2(name) values('tom');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

#插入数据，当id不为空时，插入数据成功
mysql> insert into tb2(id,name) values(01,'tom');
Query OK, 1 row affected (0.00 sec)

UNIQUE Key 唯一键

mysql> create table tb3(id int,name char(20) unique);
Query OK, 0 rows affected (0.36 sec)

mysql> insert into tb3(name) values('tom');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb3(name) values('tom');
ERROR 1062 (23000): Duplicate entry 'tom' for key 'name'
mysql> insert into tb3(name) values('bob');
Query OK, 1 row affected (0.00 sec)

mysql>

PRIMARY KEY主键（不能为空，且唯一）

mysql> create table tb4(
    -> id int primary key,
    -> name char(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

FOREIGN KEY外键（参照完整性）

CREATE TABLE tb_dept(
dept_id INT PRIMARY KEY,
NAME VARCHAR(18),
description VARCHAR(255)
);
CREATE TABLE tb_employee(
employee_id INT PRIMARY KEY,
NAME VARCHAR(18),
gender VARCHAR(10),
dept_id INT REFERENCES tb_dept(dept_id),
address VARCHAR(255)
);

CHECK检查
某些MySQL版本不支持检查

mysql> create table tb5(
    -> name char(12),
    -> age tinyint unsigned,
    -> check (age<=120)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tb5
    -> values('alice',140);
Query OK, 1 row affected (0.00 sec)

mysql>

默认值（采用枚举类型实现）

mysql> create table tb6(
    -> name char(12),
    -> gender enum('M','F') default'M'
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb6(name) values('tom');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb6;
+------+--------+
| name | gender |
+------+--------+
| tom  | M      |
+------+--------+
1 row in set (0.00 sec)

mysql> insert into tb6(name,gender) values('aclice','F');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb6;
+--------+--------+
| name   | gender |
+--------+--------+
| tom    | M      |
| aclice | F      |
+--------+--------+
2 rows in set (0.00 sec

数值类型自增长

mysql> create table tb7(
    -> id int primary key auto_increment,
    -> name char(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tb7(name) values('tom');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb7(name) values('bob');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb7(name) values('alice');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb7;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | bob   |
|  3 | alice |
+----+-------+
3 rows in set (0.00 sec)

三、修改表

修改列的类型

mysql> desc tb1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table tb1
    -> modify id tinyint;
Query OK, 0 rows affected (0.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

增加列

mysql> alter table tb1
    -> add address varchar(100);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | tinyint(4)   | YES  |     | NULL    |       |
| name    | char(20)     | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

增加列到第一列

mysql> alter table tb1 add age int first;
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

增加列到第三列后面

mysql> alter table tb1 add num char(10) after name;
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| age     | int(11)      | YES  |     | NULL    |       |
| id      | tinyint(4)   | YES  |     | NULL    |       |
| name    | char(20)     | YES  |     | NULL    |       |
| num     | char(10)     | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除列

mysql> alter table tb1 drop num;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

该列名

mysql> alter table tb1 change id u_id int;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| age     | int(11)      | YES  |     | NULL    |       |
| u_id    | int(11)      | YES  |     | NULL    |       |
| name    | char(20)     | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

该表名

#方法一
mysql> alter table tb1 rename table1;
Query OK, 0 rows affected (0.01 sec)

方法二
mysql> rename table table1 to table2;
Query OK, 0 rows affected (0.00 sec)

复制表结构

mysql> create table t1 like tb1;
Query OK, 0 rows affected (0.01 sec)

mysql> desc t1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| age     | int(11)      | YES  |     | NULL    |       |
| u_id    | int(11)      | YES  |     | NULL    |       |
| name    | char(20)     | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> desc tb1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| age     | int(11)      | YES  |     | NULL    |       |
| u_id    | int(11)      | YES  |     | NULL    |       |
| name    | char(20)     | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

复制变结构（携带表信息）

mysql> create table t2 
    -> select * from tb2;
Query OK, 1 row affected (0.41 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
+----+------+
1 row in set (0.00 sec)

mysql> select * from tb2;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
+----+------+
1 row in set (0.00 sec)

Wan@Technology Stack

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL 案例实战--MySQL 数据库表操作

MySQL 数据库表操作前言一、查看表二、创建表一级目录一级目录一级目录一级目录前言本环境是基于 Centos 7.8 系统构建MySQL-5.7.14具体构建，请参考 MySQL-5.7.14 环境构建一、查看表查看当前数据库中的表mysql> use sys;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker
复制链接

扫一扫