資料庫筆試面試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 | |+-------+-------------+------+-----+---------+-------+