MySQL中查看表命令
1 创建表命令
#创建数据库
MariaDB [mysql]> create database LJW;
#进入数据库
MariaDB [mysql]> use LJW;
#创建一个t1表格
MariaDB [LJW]> create table t2 (id int,name varchar(20));
Query OK, 0 rows affected (0.00 sec)
#再创建一个表格
MariaDB [LJW]> create table t1 ( id int auto_increment primary key,name varchar(20),age tinyint unsigned);
#后面的参数暂时不考虑，后面会细说；
#创建表命令
MariaDB [linux]> create table t1(id int(10) auto_increment primary key,name varchar(20),job varchar(10));
Query OK, 0 rows affected (0.00 sec)
2 查看数据表命令
#简单查看表结构
MariaDB [LJW]> show tables;
+---------------+
| Tables_in_LJW |
+---------------+
| t1 |
| t2 |
+---------------+
2 rows in set (0.00 sec)
#然后查看里面的内容-->desc命令
MariaDB [LJW]> desc t1;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
#查看表结构
MariaDB [LJW]> show table status \G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 10485760
Auto_increment: 1
Create_time: 2024-10-21 11:12:40
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
.................
#查看一下具体的表格内容
MariaDB root@127.0.0.1:zabbix> show table status like "user"\G
0 rows in set
Time: 0.002s
MariaDB root@127.0.0.1:zabbix> show table status from zabbix like "lala"\G
0 rows in set
Time: 0.001s
#查看sql语句
MariaDB [LJW]> show create table mysql.user\G
#选定数据库
MariaDB [linux]> use linux;
Database changed
#查看表格
MariaDB [linux]> show tables;
+-----------------+
| Tables_in_linux |
+-----------------+
| t1 |
+-----------------+
1 row in set (0.00 sec)
#也可以使用其他的
MariaDB [linux]> show tables from mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
..........
#查看表的详细信息
MariaDB [linux]> show table status\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
.......
#或者是直接查看数据库中的表格信息
MariaDB [linux]> show table status from mysql\G
#查看具体某个数据表的信息
MariaDB [linux]> show table status like "t1"\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
.......
#或者直接指定某个数据库中的
MariaDB [linux]> show table status from mysql like "user"\G
*************************** 1. row ***************************
Name: user
Engine: MyISAM
Version: 10
Row_format: Dynamic
......
#查看表结构
MariaDB [linux]> desc t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| job | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
#查看创建表中的sql语句
MariaDB [linux]> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`job` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
3 修改表结构相关的命令
表的增删改查
#添加表字段-->添加一个的是一个job工作字段选项
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
#查看一下表结构
MariaDB [ljw]> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| job | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#默认的添加是添加到末行的，如果想要改变位置，可以进行制定，例如添加到第1行
MariaDB [ljw]> alter table t2 add class varchar(20) first;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#再次查看一下表结构
MariaDB [ljw]> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| class | varchar(20) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| job | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#制定添加到哪儿的--比如说制定添name前面
MariaDB [ljw]> alter table t2 add time varchar(20) after id;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#再次查看一下这个表结构
MariaDB [ljw]> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| class | varchar(20) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| time | varchar(20) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| job | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#修改字段名，需要将字段属性写全
#change：修改表明
#father前面是原有的字段
#school：是后面的改名后的字段
MariaDB [ljw]> alter table t2 change time school int(5);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
#再次查看情况
MariaDB [ljw]> desc t2;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| class | varchar(20) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| school | int(5) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| job | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#修改字段的属性，并且进行字段排序
MariaDB [ljw]> alter table t2 modify job varchar(20) first;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
#查看表字段-->发现job已经改变成第一个位置了
MariaDB [ljw]> desc t2;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| job | varchar(20) | YES | | NULL | |
| class | varchar(20) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| school | int(5) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#删除表字段
MariaDB [ljw]> alter table t2 drop class;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
#再次查看发现这个class就已经不见了
MariaDB [ljw]> desc t2;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| job | varchar(20) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| school | int(5) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#添加表格字段-->默认在末行添加新的字段
MariaDB [linux]> alter table t3 add job varchar(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#如果想要改变添加到第一行-->这里添加一个first
MariaDB [linux]> alter table t3 add class varchar(20) first;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
#如果指定在name后面添加一个字段
MariaDB [linux]> alter table t3 add age varchar(20) after name;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
#如果想要修改字段名，
#id：是原字段，address是新修改后的字段
MariaDB [linux]> alter table t3 change id address varchar(30);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
#如果你想要修改字段的属性或者是未知
MariaDB [linux]> alter table t3 modify name varchar(20) first;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
#删除表格字段
MariaDB [linux]> alter table t3 drop class;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
分享环节：
hi，亲爱的朋友们：
- 感谢你们耐心完这个笔记，如果笔记中出现的一些软件包、资源找不到的可以直接留言&私聊，我看见了就回复；
- 资源免费共享；有需要滴滴，（仅仅是我有的）
我的坚持初衷：💕立志要成为一名架构师
- 不断地去坚持学，其中的各种各样的难度，不言而喻~！
- 坚持不是一件容易的事情，但它却是成功的关键。做起来吧~！
如果你也想要坚持：那么组团吧，咋们一块互相监督；一天一点分享也是进步；最怕就是孤军奋战！加油吧，追梦人~！
