MySQL数据表的基本操作三：综合示例_MySQL

来源：懂视网责编：小采时间：2020-11-09 19:43:24

MySQL数据表的基本操作三：综合示例_MySQL

MySQL数据表的基本操作三：综合示例_MySQL:一、创建数据库 mysql> create database company; mysql> use company; 二、创建表 1. 创建表offices mysql> create table offices -> ( -> officeCode int(10) NOT NULL UNIQUE, -> city varchar(5

推荐度：

点击下载本文 文档为doc格式

导读MySQL数据表的基本操作三：综合示例_MySQL:一、创建数据库 mysql> create database company; mysql> use company; 二、创建表 1. 创建表offices mysql> create table offices -> ( -> officeCode int(10) NOT NULL UNIQUE, -> city varchar(5

一、创建数据库

mysql> create database company;
mysql> use company;

二、创建表

1. 创建表offices

mysql> 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)
 -> );

2. 创建表employees

mysql> 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)
 -> );

3. 查看数据库已创建的表

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

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 | |
+------------+-------------+------+-----+---------+-------+

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 | |
+----------------+--------------+------+-----+---------+----------------+

三、表的基本操作

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

mysql> alter table employees MODIFY mobile varchar(25) 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) | YES | | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| birth | datetime | YES | | NULL | |
| note | varchar(255) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+

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

mysql> alter table employees CHANGE birth employee_birth DATETIME;
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 | |
+----------------+--------------+------+-----+---------+----------------+

3. 修改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) | 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 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=latin1
1 row in set (0.01 sec)

8. 将表employees表名改为employees_info

mysql> alter table employees rename employees_info;
Query OK, 0 rows affected (0.00 sec)

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

如果您们在尝试的过程中遇到什么问题或者我的代码有错误的地方，请给予指正，非常感谢！

联系方式：david.louis.tian@outlook.com

版权＠：转载请标明出处！

声明：本网页内容旨在传播知识，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。TEL:177 7030 7066 E-MAIL:11247931@qq.com