mysql建库sql语句_mysql建库和建表的sql语句

需求描述：

在用户提交酒店订单时，需要记录订单信息和订单日志；订单信息包括用户名、订单号、入住时间和离店时间；

订单日志包括谁在什么时候操作了该订单，下订单时需要记录的订单日志为系统在下订单的时间点创建了新订单；

请实现提交酒店订单的功能。

完成数据库设计，给出建库和建表的sql语句：

mysql> create database orders;

Query OK, 1 row affected (0.00 sec)

mysql> use orders

Database changed

mysql> create table `order`(`id` int not null,`username` varchar(60),`startdate` date,`enddate` date, primary key(`id`),unique key(`id`));

Query OK, 0 rows affected (0.09 sec)

mysql> show tables;

+------------------+

| Tables_in_orders |

+------------------+

| order            |

+------------------+

1 row in set (0.00 sec)

mysql> show create table `order`;

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                                                                 |

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| order | CREATE TABLE `order` (

`id` int(11) NOT NULL,

`username` varchar(60) DEFAULT NULL,

`startdate` date DEFAULT NULL,

`enddate` date DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `id` (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> alter table `order` type=InnoDB;

Query OK, 0 rows affected, 1 warning (0.16 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table `order` CHARACTER SET utf8;

Query OK, 0 rows affected (0.28 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table `order`;

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                                                                                    |

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| order | CREATE TABLE `order` (

`id` int(11) NOT NULL,

`username` varchar(60) CHARACTER SET latin1 DEFAULT NULL,

`startdate` date DEFAULT NULL,

`enddate` date DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `id` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)