数据库笔试面试91——在MySQL中，创建一个存储引擎为InnoDB，字符集为GBK的表TEST

创建一个存储引擎为InnoDB，字符集为GBK的表TEST，字段为ID和NAMEVARCHAR(16)，并查看表结构完成下列要求：

①　插入一条数据：1,newlhr

②　批量插入数据：2,小麦苗；3,ximaimiao。要求中文不能乱码

③　首先查询名字为newlhr的记录，然后查询ID大于1的记录

④　把数据ID等于1的名字newlhr更改为oldlhr

⑤　在字段NAME前插入AGE字段，类型TINYINT(4)

答案如下所示：

答案：

mysql> CREATE TABLE `TEST`(`ID` INT(4) NOT NULL, `NAME` VARCHAR(20) NOT NULL)ENGINE=InnoDB DEFAULT CHARSET=GBK;Query OK, 0 rows affected (0.67 sec)mysql> DESC TEST;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(4) | NO | | NULL | || name | varchar(20) | NO | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.09 sec)

插入一条数据：1,newlhr

mysql> INSERT INTO TEST(ID,NAME) VALUES(1,"newlhr");Query OK, 1 row affected (0.09 sec)mysql> SELECT * FROM TEST;+----+--------+| id | name |+----+--------+| 1 | newlhr |+----+--------+1 row in set (0.00 sec)

批量插入数据：2,小麦苗；3,ximaimiao。要求中文不能乱码。

mysql> INSERT INTO TEST VALUES(2,"小麦苗"),(3,"ximaimiao");Query OK, 2 rows affected (0.27 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> SELECT * FROM TEST;+----+-----------+| id | name |+----+-----------+| 1 | newlhr || 2 | 小麦苗 || 3 | ximaimiao |+----+-----------+

首先查询名字为newlhr的记录，然后查询ID大于1的记录。

mysql> SELECT * FROM TEST WHERE NAME="newlhr";+----+--------+| id | name |+----+--------+| 1 | newlhr |+----+--------+mysql> SELECT * FROM TEST WHERE ID>1;+----+-----------+| id | name |+----+-----------+| 2 | 小麦苗 || 3 | ximaimiao |+----+-----------+

把数据ID等于1的名字newlhr更改为oldlhr。

mysql> UPDATE TEST SET NAME="oldlhr" WHERE ID=1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM TEST;+----+-----------+| id | name |+----+-----------+| 1 | oldlhr || 2 | 小麦苗 || 3 | ximaimiao |+----+-----------+

在字段NAME前插入AGE字段，类型TINYINT(4)。

mysql> ALTER TABLE TEST ADD AGE TINYINT(4) AFTER ID;Query OK, 3 rows affected (0.04 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> DESC TEST;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(4) | NO | | NULL | || age | tinyint(4) | YES | | NULL | || name | varchar(20) | NO | | NULL | |+-------+-------------+------+-----+---------+-------+