Mysql练习-数据表结构操作
程序员文章站
2022-03-03 18:40:25
...
一、练习要求
① 登录MySQL数据库
② 创建数据库company
③ 创建表offices
④ 创建表employees
⑤ 将表employees的mobile字段调整到officeCode字段后面
⑥ 将表employees的birth字段改名为employ_birth
⑦ 修改sex字段，数据类型为CHAR(1)，非空约束
⑧ 删除字段note
⑨ 增加字段名favoriate_activity，数据类型为VARCHAR(100)
⑩ 删除表offices
⑪ 修改表employees的存储引擎为MyISAM
⑫ 将表employees的名称改为employees_info
offices表结构
| 字段名 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
|---|---|---|---|---|---|---|
| officeCode | INT(10) | 是 | 否 | 是 | 是 | 否 |
| city | INT(11) | 否 | 否 | 是 | 否 | 否 |
| address | VARCHAR(50) | 否 | 否 | 否 | 否 | 否 |
| country | VARCHAR(50) | 否 | 否 | 是 | 否 | 否 |
| postalCode | VARCHAR(25) | 否 | 否 | 否 | 是 | 否 |
employees表结构
| 字段名 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
|---|---|---|---|---|---|---|
| employeeNumber | INT(11) | 是 | 否 | 是 | 是 | 否 |
| lastName | VARCHAR(50) | 否 | 否 | 是 | 否 | 否 |
| firstName | VARCHAR(50) | 否 | 否 | 是 | 否 | 否 |
| mobile | VARCHAR(25) | 否 | 否 | 否 | 是 | 否 |
| officeCode | INT(10) | 否 | 是 | 是 | 否 | 否 |
| jobTitle | VARCHAR(50) | 否 | 否 | 是 | 否 | 否 |
| birth | DATETIME | 否 | 否 | 是 | 否 | 否 |
| note | VARCHAR(255) | 否 | 否 | 否 | 否 | 否 |
| sex | VARCHAR(5) | 否 | 否 | 否 | 否 | 否 |
二、操作记录
创建数据库company
mysql> CREATE DATABASE company;
Query OK, 1 row affected (0.00 sec)
mysql> use company;
Database changed
mysql> show tables;
Empty set (0.00 sec)
创建表offices
mysql> CREATE TABLE offices
-> (
-> officeCode INT(10) PRIMARY KEY NOT NULL UNIQUE,
-> city INT(11) NOT NULL,
-> address VARCHAR(50),
-> conutry VARCHAR(50) NOT NULL,
-> postalCode VARCHAR(25) UNIQUE
-> );
Query OK, 0 rows affected (0.34 sec)
创建表employees
mysql> CREATE TABLE employees
-> (
-> employeeNumber INT(11) PRIMARY KEY NOT NULL UNIQUE,
-> lastName VARCHAR(50) NOT NULL,
-> firstName VARCHAR(50) NOT NULL,
-> mobile VARCHAR(25) UNIQUE,
-> officeCode INT(10) NOT NULL,
-> jobTitle VARCHAR(50) NOT NULL,
-> birth DATETIME NOT NULL,
-> note VARCHAR(255),
-> sex VARCHAR(5),
-> CONSTRAINT fk_office FOREIGN KEY (officeCode) REFERENCES offices(officeCode)
-> );
Query OK, 0 rows affected (0.61 sec)
mysql> desc employees;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11) | NO | PRI | NULL | |
| lastName | varchar(50) | NO | | NULL | |
| firstName | varchar(50) | NO | | NULL | |
| mobile | varchar(25) | YES | UNI | NULL | |
| officeCode | int(10) | NO | MUL | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| birth | datetime | NO | | NULL | |
| note | varchar(255) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
将表employees的mobile字段调整到officeCode字段后面
mysql> ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode;
Query OK, 0 rows affected (0.46 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employees;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11) | NO | PRI | NULL | |
| lastName | varchar(50) | NO | | NULL | |
| firstName | varchar(50) | NO | | NULL | |
| officeCode | int(10) | NO | MUL | NULL | |
| mobile | varchar(25) | YES | UNI | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| birth | datetime | NO | | NULL | |
| note | varchar(255) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
将表employees的birth字段改名为employ_birth
mysql> ALTER TABLE employees CHANGE birth employ_birth DATETIME;
Query OK, 0 rows affected (0.48 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employees;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11) | NO | PRI | NULL | |
| lastName | varchar(50) | NO | | NULL | |
| firstName | varchar(50) | NO | | NULL | |
| officeCode | int(10) | NO | MUL | NULL | |
| mobile | varchar(25) | YES | UNI | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| employ_birth | datetime | YES | | NULL | |
| note | varchar(255) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
修改sex字段，数据类型为CHAR(1)，非空约束
mysql> ALTER TABLE employees CHANGE sex sex CHAR(1) NOT NULL;
Query OK, 0 rows affected (0.80 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employees;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11) | NO | PRI | NULL | |
| lastName | varchar(50) | NO | | NULL | |
| firstName | varchar(50) | NO | | NULL | |
| officeCode | int(10) | NO | MUL | NULL | |
| mobile | varchar(25) | YES | UNI | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| employ_birth | datetime | YES | | NULL | |
| note | varchar(255) | YES | | NULL | |
| sex | char(1) | NO | | NULL | |
+----------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
删除字段note
mysql> ALTER TABLE employees DROP note;
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employees;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employeeNumber | int(11) | NO | PRI | NULL | |
| lastName | varchar(50) | NO | | NULL | |
| firstName | varchar(50) | NO | | NULL | |
| officeCode | int(10) | NO | MUL | NULL | |
| mobile | varchar(25) | YES | UNI | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| employ_birth | datetime | YES | | NULL | |
| sex | char(1) | NO | | NULL | |
+----------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
增加字段名favoriate_activity，数据类型为VARCHAR(100)
mysql> ALTER TABLE employees ADD favoriate_activity VARCHAR(100);
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employees;
+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11) | NO | PRI | NULL | |
| lastName | varchar(50) | NO | | NULL | |
| firstName | varchar(50) | NO | | NULL | |
| officeCode | int(10) | NO | MUL | NULL | |
| mobile | varchar(25) | YES | UNI | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| employ_birth | datetime | YES | | NULL | |
| sex | char(1) | NO | | NULL | |
| favoriate_activity | varchar(100) | YES | | NULL | |
+--------------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
mysql> SHOW TABLES;
+-------------------+
| Tables_in_company |
+-------------------+
| employees |
| offices |
+-------------------+
2 rows in set (0.00 sec)
删除表offices
mysql> DROP TABLE IF EXISTS offices;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> ALTER TABLE employees DROP FOREIGN KEY fk_office;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employees;
+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11) | NO | PRI | NULL | |
| lastName | varchar(50) | NO | | NULL | |
| firstName | varchar(50) | NO | | NULL | |
| officeCode | int(10) | NO | MUL | NULL | |
| mobile | varchar(25) | YES | UNI | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| employ_birth | datetime | YES | | NULL | |
| sex | char(1) | NO | | NULL | |
| favoriate_activity | varchar(100) | YES | | NULL | |
+--------------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
mysql> DROP TABLE IF EXISTS offices;
Query OK, 0 rows affected (0.12 sec)
mysql> show create table employees;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
`employeeNumber` int(11) NOT NULL,
`lastName` varchar(50) NOT NULL,
`firstName` varchar(50) NOT NULL,
`officeCode` int(10) NOT NULL,
`mobile` varchar(25) DEFAULT NULL,
`jobTitle` varchar(50) NOT NULL,
`employ_birth` datetime DEFAULT NULL,
`sex` char(1) NOT NULL,
`favoriate_activity` varchar(100) DEFAULT NULL,
PRIMARY KEY (`employeeNumber`),
UNIQUE KEY `employeeNumber` (`employeeNumber`),
UNIQUE KEY `mobile` (`mobile`),
KEY `fk_office` (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
修改表employees的存储引擎为MyISAM
mysql> ALTER TALBE employees ENGINE=MyISAM;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TALBE employees ENGINE=MyISAM' at line 1
mysql> ALTER TABLE employees ENGINE=MyISAM;
Query OK, 0 rows affected (0.42 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table employees;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
`employeeNumber` int(11) NOT NULL,
`lastName` varchar(50) NOT NULL,
`firstName` varchar(50) NOT NULL,
`officeCode` int(10) NOT NULL,
`mobile` varchar(25) DEFAULT NULL,
`jobTitle` varchar(50) NOT NULL,
`employ_birth` datetime DEFAULT NULL,
`sex` char(1) NOT NULL,
`favoriate_activity` varchar(100) DEFAULT NULL,
PRIMARY KEY (`employeeNumber`),
UNIQUE KEY `employeeNumber` (`employeeNumber`),
UNIQUE KEY `mobile` (`mobile`),
KEY `fk_office` (`officeCode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
将表employees的名称改为employees_info
mysql> ALTER TABLE employees RENAME employees_info;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employees_info |
+-------------------+
1 row in set (0.00 sec)
mysql>