前言
本环境是基于 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)