mysql综合案例 数据表的基本操作
创建修改删除表，掌握数据表的基本操作
创建数据库company，按下表创建两个table名为offices和employees
offices表结构
| 字段名 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
|---|---|---|---|---|---|---|
| officeCode | int(10) | 是 | 否 | 是 | 是 | 否 |
| city | varchar(50) | 否 | 否 | 是 | 否 | 否 |
| address | varchar(50) | 否 | 否 | 否 | 否 | 否 |
| country | varchar(50) | 否 | 否 | 是 | 否 | 否 |
| postalCode | varchar(15) | 否 | 否 | 否 | 是 | 否 |
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) | 否 | 否 | 否 | 否 | 否 |
操作过程：
1.创建库
mysql> create database company;
2.创建表
创建表offices
mysql> use company;
mysql> create table offices(
-> officeCode int(10) primary key not null unique,
-> city varchar(50) not null,
-> address varchar(50),
-> conutry varchar(50) not null,
-> postalCode varchar(15) unique
-> );
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 | |
| conutry | varchar(50) | NO | | NULL | |
| postalCode | varchar(15) | YES | UNI | NULL | |
+------------+-------------+------+-----+---------+-------+
创建表employees
mysql> create table employees(
-> employeeNumber int(11) primary key not null unique 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),
-> constraint office_fk foreign key(officeCode) references offices(officeCode)
-> );
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 | |
+----------------+--------------+------+-----+---------+----------------+
3.将表employees的mobile字段修改到officeCode后面
mysql> alter table employees modify mobile varchar(25) not null unique after officeCode;
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) | NO | UNI | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| birth | datetime | NO | | NULL | |
| note | varchar(255) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
4.将表employees的birth字段改名为employee_birth
mysql> alter table employees change birth employee_birth datetime 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) | NO | UNI | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| employee_birth | datetime | NO | | NULL | |
| note | varchar(255) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
5.修改sex字段，数据类型为char(1)，非空约束
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) | NO | UNI | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| employee_birth | datetime | NO | | NULL | |
| note | varchar(255) | YES | | NULL | |
| sex | char(1) | NO | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
6.删除字段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) | NO | UNI | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| employee_birth | datetime | NO | | NULL | |
| sex | char(1) | NO | | NULL | |
+----------------+-------------+------+-----+---------+----------------+
7.增加字段名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) | NO | UNI | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| employee_birth | datetime | NO | | NULL | |
| sex | char(1) | NO | | NULL | |
| favoriate_activity | varchar(100) | YES | | NULL | |
+--------------------+--------------+------+-----+---------+----------------+
8.删除表office
在创建表employess时，设置了表的外键，该表关联了其父表的officeCode主键。
删除关联表时，要先删除子表employees的外键约束，才能删除父表。
所以先要删除employees表的外键约束。
下面是当时创建employees的语句：
employees | 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) NOT NULL,
`jobTitle` varchar(50) NOT NULL,
`employee_birth` datetime NOT NULL,
`sex` char(1) NOT NULL,
`favoriate_activity` varchar(100) DEFAULT NULL,
PRIMARY KEY (`employeeNumber`),
UNIQUE KEY `employeeNumber` (`employeeNumber`),
UNIQUE KEY `mobile` (`mobile`),
UNIQUE KEY `mobile_2` (`mobile`),
KEY `office_fk` (`officeCode`),
CONSTRAINT `office_fk` FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
8.1 先删除外键约束
mysql> alter table employees drop foreign key office_fk;
8.2 删除表office
mysql> drop tables offices;
9.修改表employees的存储引擎为myisam
先查看一下修改前的引擎
mysql> show create table employees;
mysql> alter table employees engine=myisam;
10.将表employees改名为employees_info
mysql> alter table employees rename employees_info;
11.习题练习
11.1 创建数据库market，在market下建表customers如下：
| 字段名 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
|---|---|---|---|---|---|---|
| c_num | int(11) | 是 | 否 | 是 | 是 | 是 |
| c_name | varchar(50) | 否 | 否 | 否 | 否 | 否 |
| c_contact | varchar(50) | 否 | 否 | 否 | 否 | 否 |
| c_city | varchar(50) | 否 | 否 | 否 | 否 | 否 |
| c_birth | datetime | 否 | 否 | 是 | 否 | 否 |
（1）创建数据库market
（2）创建数据表customer，在c_num字段上添加主键约束和自增约束，在c_birth字段添加非空约束
以下操作要在建完表之后进行
（3）将c_contact字段插入到c_birth字段后面
（4）将c_name字段数据类型改为varchar(70)
（5）将c_contact字段改名为c_phone
（6）增加c_gender字段，数据类型为char(1)
（7）将表名修改为customers_info
（8）删除字段c_city
（9）修改数据表的存储引擎为myisam
操作如下：
（1）创建数据库market
（2）创建数据表customer，在c_num字段上添加主键约束和自增约束，在c_birth字段添加非空约束
mysql> create table customers(
-> c_num int(11) primary key not null unique auto_increment,
-> c_name varchar(50),
-> c_contact varchar(50),
-> c_city varchar(50),
-> c_birth datetime not null
-> );
以下操作要在建完表之后进行,练习嘛
（3）将c_contact字段插入到c_birth字段后面
mysql> alter table customers modify c_contact varchar(50) after c_birth;
（4）将c_name字段数据类型改为varchar(70)
#下面两种方式均可
mysql> alter table customers change c_name c_name varchar(70);
mysql> alter table customers modify c_name varchar(100);
（5）将c_contact字段改名为c_phone
mysql> alter table customers change c_contact c_phone varchar(50) not null;
（6）增加c_gender字段，数据类型为char(1)
mysql> alter table customers add c_gender varchar(1);
（7）将表名修改为customers_info
mysql> alter table customers rename customers_info;
（8）删除字段c_city
mysql> alter table customers_info drop c_city;
（9）修改数据表的存储引擎为myisam
mysql> show create table customers_info\G;
mysql> alter table customers_info engine=myisam;
11.2 在market中创建表orders，如下：
| 字段名 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
|---|---|---|---|---|---|---|
| o_num | int(11) | 是 | 否 | 是 | 是 | 是 |
| o_date | date | 否 | 否 | 否 | 否 | 否 |
| c_id | varchar(50) | 否 | 是 | 否 | 否 | 否 |
（1）建表orders，在o_num字段上添加主键约束和自增约束，在c_id字段上添加外键约束，关联customers_info中的主键c_num
（2）删除orders表的外键约束，然后删除表customers_info
操作如下：
（1）建表orders，在o_num字段上添加主键约束和自增约束，在c_id字段上添加外键约束，关联customers_info中的主键c_num
务必注意两键之间的数据类型一致！！！
mysql> create table orders(
-> o_num int(11) primary key not null unique auto_increment,
-> o_date date,
-> c_id int(11) not null,
-> constraint customers_info_fk foreign key(c_id) references customers_info(c_id)
-> );
mysql> desc orders;
+--------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+----------------+
| o_num | int(11) | NO | PRI | NULL | auto_increment |
| o_date | date | YES | | NULL | |
| c_id | int(11) | NO | MUL | NULL | |
+--------+---------+------+-----+---------+----------------+
（2）删除orders表的外键约束，然后删除表customers_info
#先查看以下外键名
mysql> show create table orders;
#删除外键
mysql> alter table orders drop foreign key customers_info_fk;
669
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
