mysql global sql_MySql: 常见sql语句

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>