“数据库”只是一个外壳,除了有个数据库名称和字符集设定,基本就没有别的信息了。数据表才是存储(装载)数据的具体“容器”。我们需要创建不同的表来存储不同的数据。

创建数据表初步

语句形式:

create table 数据表名 (字段1, 字段2, ... )[charset=字符集] [engine=表类型];

mysql> use demo;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table table1 (id  int, name varchar(10),  sex  char(1), age  int );
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| table1         |
+----------------+
1 row in set (0.00 sec)

其中:

  • 字段的形式为: 字段名 字段类型 [字段属性...]
  • 字符集包括:utf,gbk,gb2312,big5等等,默认是数据库的字符集,可以不写。
  • 表类型包括:InnoDB, MyIsam,BDB,等,默认是InnoDB,可以不写。

查看所有数据表

语句形式:show tables;

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| table1         |
+----------------+
1 row in set (0.00 sec)

查看数据表结构

语句形式:desc 表名;

mysql> desc table1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.04 sec)

所谓数据表的结构,其实就是一个表的每个字段的具体信息。

在来一个:

mysql> create  table  info (
    ->  id  int,
    ->  title  varchar(50),
    ->  email varchar(20),
    ->  content  text
    ->  )
    ->  charset utf8
    ->  engine MyIsam ;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| info           |
| table1         |
+----------------+
2 rows in set (0.00 sec)

查看数据表的创建语句

语句形式:show create table 表名;

mysql> show create table table1;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table
                                                                           |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table1 | CREATE TABLE `table1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(在phpmyadmin的界面执行的结果)

TableCreate Table
typecho_commentsCREATE TABLE typecho_comments ( coid int(10) unsigned NOT NULL AUTO_INCREMENT, cid int(10) unsigned DEFAULT '0', created int(10) unsigned DEFAULT '0', author varchar(200) DEFAULT NULL, authorId int(10) unsigned DEFAULT '0', ownerId int(10) unsigned DEFAULT '0', mail varchar(200) DEFAULT NULL, url varchar(255) DEFAULT NULL, ip varchar(64) DEFAULT NULL, agent varchar(511) DEFAULT NULL, text text, type varchar(16) DEFAULT 'comment', status varchar(16) DEFAULT 'approved', parent int(10) unsigned DEFAULT '0', stars int(10) DEFAULT '0', likes int(10) DEFAULT '0', dislikes int(10) DEFAULT '0', receiveMail int(10) DEFAULT '1', PRIMARY KEY (coid), KEY cid (cid), KEY created (created) ) ENGINE=MyISAM AUTO_INCREMENT=786 DEFAULT CHARSET=utf8

删除数据表

语句形式:drop table 表名;

mysql> drop table table1;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| info           |
+----------------+
1 row in set (0.00 sec)

修改数据表

修改数据表主要是修改表名,添加字段,修改字段,删除字段,修改表的字符集;

添加字段:

语句形式:alter table 表名 add 字段名 字段类型 [字段属性...] [after 某字段名 或 first];

  • after 某字段名:意思是,新加的字段,放在该现有字段的后面;
  • first:表示新加的字段放在第一位(最前面)
-- alter table 表名 add 字段名 字段类型 [字段属性...] [after 某字段名 或 first];
mysql> desc table1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.04 sec)

mysql> alter table  `table1` add  salary float;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc table1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(10) | YES  |     | NULL    |       |
| sex    | char(1)     | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.04 sec)

mysql> alter table  table1 add edu varchar(5) after age;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc table1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(10) | YES  |     | NULL    |       |
| sex    | char(1)     | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| edu    | varchar(5)  | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.04 sec)

修改字段:

语句形式:

alter table 表名 change 旧字段名 新字段名 字段类型 [字段属性...];

如果不修改字段名,而只修改字段的其他信息,则可以使用:

alter table 表名 modify 要修改的字段名 字段类型 [字段属性...];

mysql> desc table1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(10) | YES  |     | NULL    |       |
| sex    | char(1)     | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| edu    | varchar(5)  | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.04 sec)

mysql> -- 修改salary 字段:
mysql> alter  table table1  change salary gongzi int default 0;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> -- 修改edu  字段(只该其特性,不改名字)
mysql> alter  table  table1  modify edu varchar(10) after gongzi;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc table1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(10) | YES  |     | NULL    |       |
| sex    | char(1)     | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| gongzi | int(11)     | YES  |     | 0       |       |
| edu    | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.04 sec)

删除字段:

语句形式:

alter table 表名 drop 要删除的字段名;

修改表名:

语句形式:

alter table 表名 rename 新的表名;

mysql> alter table  table1  rename user;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| info           |
| user           |
+----------------+
2 rows in set (0.00 sec)

修改字符集:

语句形式:

alter table 表名 charset=新的字符集;

修改字符集:

alter table 表名 charset=新的字符集;