MySql: 常见sql语句-优快云博客

本文介绍了MySQL中如何查看和修改表结构，包括使用SHOW CREATE TABLE命令查看表定义，ALTER TABLE命令修改列属性，并展示了sql_mode的查询方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. show create table

mysql> show create table t \G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`a` int(10) unsigned DEFAULT NULL,
`b` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

2. 修改列: alter table [table_name] change column [original_column_name] [new_column_name] [data_type]:

mysql> alter table t change column a a int(4) unsigned zerofill;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0

zerofill: 自动填充0到指定宽度,

mysql> select * from t;
+------+------+
| a | b |
+------+------+
| 0006 | 2 |
| 0008 | 5 |
| 0009 | 4 |
+------+------+
3 rows in set (0.00 sec)

这个只是显示效果，mysql后台存储并不变

mysql> select a, hex(a) from t;
+------+--------+
| a | hex(a) |
+------+--------+
| 0006 | 6 |
| 0008 | 8 |
| 0009 | 9 |
+------+--------+
3 rows in set (0.15 sec)

3. select @@global.sql_mode, select @@session.sql_mode

mysql> select @@global.sql_mode;
+--------------------------------------------+
| @@global.sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@session.sql_mode;
+--------------------------------------------+
| @@session.sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql>