MySQL数据表的基本操作三：综合示例 - 数据库编程

mysql> alter table employees MODIFY sex CHAR(1) NOT NULL;
mysql> desc employees;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employeeNumber | int(11)      | NO   | PRI | NULL    | auto_increment |
| lastName       | varchar(50)  | NO   |     | NULL    |                |
| firstName      | varchar(50)  | NO   |     | NULL    |                |
| officeCode     | int(10)      | NO   | MUL | NULL    |                |
| mobile         | varchar(25)  | YES  |     | NULL    |                |
| jobTitle       | varchar(50)  | NO   |     | NULL    |                |
| employee_birth | datetime     | YES  |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | char(1)      | NO   |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

4. 删除字段note

mysql> alter table employees DROP note;
mysql> desc employees;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| employeeNumber | int(11)     | NO   | PRI | NULL    | auto_increment |
| lastName       | varchar(50) | NO   |     | NULL    |                |
| firstName      | varchar(50) | NO   |     | NULL    |                |
| officeCode     | int(10)     | NO   | MUL | NULL    |                |
| mobile         | varchar(25) | YES  |     | NULL    |                |
| jobTitle       | varchar(50) | NO   |     | NULL    |                |
| employee_birth | datetime    | YES  |     | NULL    |                |
| sex            | char(1)     | NO   |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+

5. 增加字段名favoriate_activity, 数据类型为VARCHAR(100)

mysql> alter table employees ADD favoriate_activity varchar(100);
mysql> desc employees;
+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| employeeNumber     | int(11)      | NO   | PRI | NULL    | auto_increment |
| lastName           | varchar(50)  | NO   |     | NULL    |                |
| firstName          | varchar(50)  | NO   |     | NULL    |                |
| officeCode         | int(10)      | NO   | MUL | NULL    |                |
| mobile             | varchar(25)  | YES  |     | NULL    |                |
| jobTitle           | varchar(50)  | NO   |     | NULL    |                |
| employee_birth     | datetime     | YES  |     | NULL    |                |
| sex                | char(1)      | NO   |     | NULL    |                |
| favoriate_activity | varchar(100) | YES  |     | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+

6. 删除表offices

1) 创建表时设置了表的外键，所以不能直接删除

mysql> drop table offices;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

2) 删除employees表的外键约束

mysql> alter table employees drop foreign key office_fk;

3) 删除offices表

mysql> drop table offices;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
+-------------------+

7. 修改employees表的存储引擎为MyISAM

mysql> alter table employees ENGINE=MyISAM;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table employees\G;
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TA

MySQL数据表的基本操作三：综合示例(二)