--+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| type_name | varchar(10) | YES | | NULL | |
| description | varchar(50) | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
#增加列
mysql> alter table mytest add extra_name varchar(20) not null default '豆豆';
mysql> desc mytest;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| type_name | varchar(10) | YES | | NULL | |
| description | varchar(50) | YES | | NULL | |
| extra_name | varchar(20) | NO | | 豆豆 | |
+-------------+-------------+------+-----+---------+----------------+
#删除列
mysql> alter table mytest drop extra_name;
mysql> desc mytest;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| type_name | varchar(10) | YES | | NULL | |
| description | varchar(50) | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
#修改表名
mysql> alter table mytest rename to test01;
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| test01 |
+----------------+
mysql> desc test01;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| type_name | varchar(10) | YES | | NULL | |
| description | varchar(50) | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
#修改指定列名
mysql> alter table test01 modify column type_name char(10);
mysql> desc test01;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| type_name | char(10) | YES | | NULL | |
| description | varchar(50) | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
#查看test01表中以t开头的字段信息。
mysql> desc test01 't%';
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| type_name | char(10) | YES | | NULL | |
+-----------+----------+------+-----+---------+-------+
#创建临时表：临时表是指创建后只在当前数据库内有效，断开与数据库的连接后表就自动删除
create temporary table tb_temp(
id integer,
name varchar(100),
primary key(id)
);
10）查看数据库的编码方式
#查看当前数据库的编码方式
mysql> show variables like 'character%';
+--------------------------+-----------------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | D:\MySQL\MySQLServer