一 登录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 |