一 登录MySQL数据库
打开windows命令行,输入登录用户名和密码:
C:\Users\cakin>mysql -h localhost -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
登录成功,可以输入SQL语句进行操作。
二 创建数据库company
创建数据库company的语句如下:
mysql> CREATE DATABASE company;
Query OK, 1 row affected (0.00 sec)
结果显示创建成功,在company数据库中创建表,必须先选择该数据库,输入语句如下:
mysql> USE company;
Database changed
结果显示选择数据库成功。
三 创建表offices
创建表offices的语句如下:
CREATE TABLE offices
(
officeCode INT(10) NOT NULL UNIQUE,
city VARCHAR(50) NOT NULL,
address VARCHAR(50) NOT NULL,
country VARCHAR(50) NOT NULL,
postalCode VARCHAR(15) NOT NULL,
PRIMARY KEY (officeCode)
);
执行成功之后,使用SHOW TABLES;语句查看数据库中的表,语句如下:
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| offices |
+-------------------+
1 row in set (0.00 sec)
可以看到,数据库中已经有了数据表offices,创建成功。
四 创建表employees
创建表employees的语句如下:
CREATE TABLE employees
(
employeeNumber INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
lastName VARCHAR(50) NOT NULL,
firstName VARCHAR(50) NOT NULL,
mobile VARCHAR(25) NOT NULL,
officeCode INT(10) NOT NULL,
jobTitle VARCHAR(50) NOT NULL,
birth DATETIME,
note VARCHAR(255),
sex VARCHAR(5),
CONSTRAINT office_fk FOREIGN KEY(officeCode) REFERENCES offices(officeCode)
);
执行成功之后,使用SHOW TABLES;语句查看数据库中的表,语句如下:
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employees |
| offices |
+-------------------+
2 rows in set (0.00 sec)
可以看到,现在数据库中已经创建好了employees和offices两个数据表。要检查表的结构是否按照要求创建,可使用DESC分别查看两个表的结构,如果语句正确,则显示结果如下:
mysql> DESC offices;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| officeCode | int(10) | NO | PRI | NULL | |
| city | varchar(50) | NO | | NULL | |
| address | varchar(50) | NO | | NULL | |
| country | varchar(50) | NO | | NULL | |
| postalCode | varchar(15) | NO | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
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 | |
| mobile | varchar(25) | NO | | NULL | |
| officeCode | int(10) | NO | MUL | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| birth | datetime | YES | | NULL | |
| note | varchar(255) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
五 将表employees的mobile字段修改到officeCode字段后面
修改字段位置,需要用到ALTER TABLE语句,输入语句如下:
mysql> ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode;
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
结果显示执行成功,使用DESC查看修改后的结果如下:
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 | |
| birth | datetime | YES | | NULL | |
| note | varchar(255) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
可以看到,mobile字段已经插入到officeCode字段的后面。
六 将表employees的birth字段改名为employee_birth
修改字段名,需要用到ALTER TABLE语句,输入语句如下:
mysql> ALTER TABLE employees CHANGE birth employee_birth DATETIME;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
结果显示执行成功,使用DESC查看修改后的结果如下:
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 | varchar(5) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
可以看到,表中只有employee_birth字段,已经没有名称为birth的字段了,修改名称成功。
七 修改sex字段,数据类型为CHAR(1),非空约束
修改字段数据类型,需要用到ALTER TABLE语句,输入语句如下:
mysql> ALTER TABLE employees MODIFY sex CHAR(1) NOT NULL;
Query OK, 0 rows affected (0.46 sec)
Records: 0 Duplicates: 0 Warnings: 0
结果显示执行成功,使用DESC查看修改后的结果如下:
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 | |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
执行结果可以看到,sex字段的数据类型由前面的VARCHAR(5)修改为CHAR(1),且其Null列显示为NO,表示该列不允许空值,修改成功。
八 删除字段note
删除字段,需要用到ALTER TABLE语句,输入语句如下:
mysql> ALTER TABLE employees DROP note;
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
结果显示执行语句成功,使用DESC employees;查看语句执行后的结果:
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 | |
+----------------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
可以看到,DESC语句返回了8个列字段,note字段已经不在表结构中,删除字段成功。
九 增加字段名favoriate_activity,数据类型为VARCHAR(100)
增加字段,需要用到ALTER TABLE语句,输入语句如下:
mysql> ALTER TABLE employees ADD favoriate_activity VARCHAR(100);
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
结果显示执行语句成功,使用DESC employees;查看语句执行后的结果:
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 | |
+--------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
可以看到,数据表employees中增加了一个新的列favoriate_activity,数据类型为VARCHAR(100),允许空值,添加新字段成功。
十 删除表offices
在创建表employees时,设置了表的外键,该表关联了其父表的officeCode主键。删除关联表时,要先删除子表employees的外键约束,才能删除父表。因此,必须先删除employees表的外键约束。
1)删除employees表的外键约束,输入如下语句:
mysql> ALTER TABLE employees DROP FOREIGN KEY office_fk;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
其中office_fk为employees表的外键约束的名称,即创建外键约束时CONSTRAINT 关键字后面的参数,结果显示语句执行成功,现在可以删除offices父表。
2)删除表offices,输入如下语句:
mysql> DROP TABLE offices;
Query OK, 0 rows affected (0.11 sec)
结果显示执行删除操作成功,使用SHOW TABLES;语句查看数据库中的表,结果如下:
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employees |
+-------------------+
1 row in set (0.00 sec)
可以看到,数据库中已经没有名称为offices的表了,删除表成功。
十一 修改表employees存储引擎为MyISAM
修改表存储引擎,需要用到ALTER TABLE语句,输入语句如下:
mysql> ALTER TABLE employees ENGINE=MyISAM;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
结果显示执行修改存储引擎操作成功,使用SHOW CREATE TABLE 语句查看表结构,结果如下:
mysql> show CREATE TABLE employees\G
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`employeeNumber` int(11) NOT NULL AUTO_INCREMENT,
`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,
`employee_birth` datetime DEFAULT NULL,
`sex` char(1) NOT NULL,
`favoriate_activity` varchar(100) DEFAULT NULL,
PRIMARY KEY (`employeeNumber`),
KEY `office_fk` (`officeCode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
可以看到,倒数第2行中的ENGINE后面的参数已经修改为MyISAM,修改成功。
十二 将表employees名称修改为employees_info
修改数据表名,需要用到ALTER TABLE语句,输入语句如下:
mysql> ALTER TABLE employees RENAME employees_info;
Query OK, 0 rows affected (0.00 sec)
结果显示执行语句成功,使用SHOW TABLES;语句查看执行结果:
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employees_info |
+-------------------+
1 row in set (0.00 sec)