数据库基础练习

 

1.创建数据库company，在库中创建两个表offices和employees。

创建数据库

CREATE DATABASE company;

创建表office

CREATE TABLE offices(

officeCode INT(10) NOT NULL,

city VARCHAR(50) NOT NULL,

address Varc`offices`har(50),

country VARCHAR(50) NOT NULL,

postalCode VARCHAR(15) UNIQUE,

PRIMARY KEY(officeCode)

);

创建表employees

CREATE TABLE employees(

employeeNumber INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,

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),

FOREIGN KEY(officeCode) REFERENCES offices(officeCode)

);

2.通过desc offices/employees命令查看表结构。

mysql> desc offices;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| officeCode | int(10)     | NO   | PRI | NULL    |       |
| city       | varchar(50) | NO   |     | NULL    |       |
| address    | varchar(50) | YES  |     | NULL    |       |
| country    | varchar(50) | NO   |     | NULL    |       |
| postalCode | varchar(15) | YES  | UNI | 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    |                |
| 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.02 sec)

3.将表employees的mobile字段修改到officeCode字段后面。

mysql> alter table employees modify mobile VARCHAR(25) after officeCode;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

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  | 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.02 sec)

4.将表employees的birth字段改名为employee_birth。

mysql> alter table employees change birth employee_birth datetime not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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  | UNI | NULL    |                |
| jobTitle       | varchar(50)  | NO   |     | NULL    |                |
| employee_birth | datetime     | NO   |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | varchar(5)   | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.02 sec)

5.修改sex字段，数据类型为CHAR(1)，非空约束。

mysql> alter table employees modify sex char(1) not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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  | UNI | NULL    |                |
| jobTitle       | varchar(50)  | NO   |     | NULL    |                |
| employee_birth | datetime     | NO   |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | char(1)      | NO   |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)

6.删除字段note。

mysql> alter table employees drop note;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

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  | UNI | NULL    |                |
| jobTitle       | varchar(50) | NO   |     | NULL    |                |
| employee_birth | datetime    | NO   |     | NULL    |                |
| sex            | char(1)     | NO   |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

7.增加字段名favoriate_activity，数据类型为VARCHAR(100)。

mysql> alter table employees add favoriate_activity varchar(100);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

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  | UNI | NULL    |                |
| jobTitle           | varchar(50)  | NO   |     | NULL    |                |
| employee_birth     | datetime     | NO   |     | NULL    |                |
| sex                | char(1)      | NO   |     | NULL    |                |
| favoriate_activity | varchar(100) | YES  |     | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.02 sec)

8.删除表offices。

先删除表employees中officeCode字段的外键约束

mysql> alter table employees drop foreign key employees_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

之后在进行表offices的删除

mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
+-------------------+
1 row in set (0.00 sec)

9.将表employees名称改为employees_inf

mysql> alter table employees rename as employees_inf;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employees_inf     |
+-------------------+
1 row in set (0.00 sec)