当前位置：Gxlcms > mysql > MySQL数据表的基本操作三：综合示例_MySQL

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

时间：2021-07-01 10:21:17 帮助过：63人阅读

一、创建数据库

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

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

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！