mysql练习之一：数据表的基本 *** 作_mysql

概述mysql练习之一：数据表的基本 *** 作

已经学习了MysqL的各种 *** 作，如创建表、添加各种约束、产看表结构、以及修改和删除表。给出一个实战演练，全面复习一下数据表的基本 *** 作基础。

案例:创建数据库company，按照下面两个表给出的表结构在company数据库中创建两个数据表offices和employees，按照 *** 作过程完成数据表的基本 *** 作。

（免费学习推荐：mysql视频教程）

 *** 作过程如下：

(1)：登录MysqL。

MysqL -h localhost -u root -p

打开@R_403_5087@命令行，输入登录用户名和密码：

C:\Users\HudIE>MysqL -h localhost -u root -pEnter password: ********Welcome to the MysqL monitor.  Commands end with ; or \g.Your MysqL connection ID is 19Server version: 8.0.16 MysqL Community Server - GPLcopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered Trademark of Oracle Corporation and/or itsaffiliates. Other names may be Trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MysqL>_

登录成功，可以输入SQL语句进行 *** 作。

(2)：创建数据库company。

create database company;

MysqL> create database company;query OK, 1 row affected (0.06 sec)

创建成功后，在company数据库中创建数据表，必须先选择该数据库。SQL语句如下：

MysqL> use company;Database changed

(3):创建表offices。

create table 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)    -> );query OK, 0 rows affected (0.14 sec)MysqL> show tables;+-------------------+| tables_in_company |+-------------------+| offices           |+-------------------+1 row in set (0.00 sec)

(4)：创建表enployees。

create table employees

MysqL> create table employees    -> (    -> employeeNumber int(11) not null primary key auto_increment,    -> lastnamee 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,    -> noth varchar(25),    -> sex varchar(5),    -> constraint office_fk foreign key(officeCode) references offices(officeCode)    -> );query OK, 0 rows affected (0.14 sec)MysqL> show tables;+-------------------+| tables_in_company |+-------------------+| employees         || offices           |+-------------------+2 rows in set (0.01 sec)

创建成功，查看两个表的结构:

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    |       |+------------+-------------+------+-----+---------+-------+5 rows in set (0.06 sec)MysqL> desc employees;+----------------+-------------+------+-----+---------+----------------+| FIEld          | Type        | Null | Key | Default | Extra          |+----------------+-------------+------+-----+---------+----------------+| employeeNumber | int(11)     | NO   | PRI | NulL    | auto_increment || lastnamee      | 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    |                || noth           | varchar(25) | YES  |     | NulL    |                || sex            | varchar(5)  | YES  |     | NulL    |                |+----------------+-------------+------+-----+---------+----------------+9 rows in set (0.01 sec)

(5)：将表employees的mobile字段修改到officeCode字段后面。

alter table employees modify mobile varchar(25) after officeCode;

MysqL> alter table employees modify mobile varchar(25) after officeCode;query OK, 0 rows affected (0.18 sec)Records: 0  Duplicates: 0  Warnings: 0MysqL> desc employees;+----------------+-------------+------+-----+---------+----------------+| FIEld          | Type        | Null | Key | Default | Extra          |+----------------+-------------+------+-----+---------+----------------+| employeeNumber | int(11)     | NO   | PRI | NulL    | auto_increment || lastnamee      | 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    |                || noth           | varchar(25) | YES  |     | NulL    |                || sex            | varchar(5)  | YES  |     | NulL    |                |+----------------+-------------+------+-----+---------+----------------+9 rows in set (0.01 sec)

(6)：将表employees的birth字段改名为employee_birth。

alter table employees change birth employee_birth datetime;

MysqL> alter table employees change birth employee_birth datetime;query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0MysqL> desc employees;+----------------+-------------+------+-----+---------+----------------+| FIEld          | Type        | Null | Key | Default | Extra          |+----------------+-------------+------+-----+---------+----------------+| employeeNumber | int(11)     | NO   | PRI | NulL    | auto_increment || lastnamee      | 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    |                || noth           | varchar(25) | YES  |     | NulL    |                || sex            | varchar(5)  | YES  |     | NulL    |                |+----------------+-------------+------+-----+---------+----------------+9 rows in set (0.00 sec)

(7)：修改sex字段，设置数据类型为char(1),非空约束。

alter table employees modify sex char(1) not null;

MysqL> alter table employees modify sex char(1) not null;query OK, 0 rows affected (0.20 sec)Records: 0  Duplicates: 0  Warnings: 0MysqL> desc employees;+----------------+-------------+------+-----+---------+----------------+| FIEld          | Type        | Null | Key | Default | Extra          |+----------------+-------------+------+-----+---------+----------------+| employeeNumber | int(11)     | NO   | PRI | NulL    | auto_increment || lastnamee      | 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    |                || noth           | varchar(25) | YES  |     | NulL    |                || sex            | char(1)     | NO   |     | NulL    |                |+----------------+-------------+------+-----+---------+----------------+9 rows in set (0.01 sec)

(8)：删除字段noth。

alter table employees drop noth;

MysqL> alter table employees drop noth;query OK, 0 rows affected (0.15 sec)Records: 0  Duplicates: 0  Warnings: 0MysqL> desc employees;+----------------+-------------+------+-----+---------+----------------+| FIEld          | Type        | Null | Key | Default | Extra          |+----------------+-------------+------+-----+---------+----------------+| employeeNumber | int(11)     | NO   | PRI | NulL    | auto_increment || lastnamee      | 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    |                |+----------------+-------------+------+-----+---------+----------------+8 rows in set (0.01 sec)

(9):增加字段名favoriate_activity,数据类型为varchar(100)

alter table employees add favoriate_activity varchar(100);

MysqL> alter table employees add favoriate_activity varchar(100);query OK, 0 rows affected (0.09 sec)Records: 0  Duplicates: 0  Warnings: 0MysqL> desc employees;+--------------------+--------------+------+-----+---------+----------------+| FIEld              | Type         | Null | Key | Default | Extra          |+--------------------+--------------+------+-----+---------+----------------+| employeeNumber     | int(11)      | NO   | PRI | NulL    | auto_increment || lastnamee          | 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    |                |+--------------------+--------------+------+-----+---------+----------------+9 rows in set (0.00 sec)

(10):删除主表offices

①删除表的外键约束:alter table employees drop foreign key office_fk;
②删除表offices:drop table offices;

MysqL> alter table employees drop foreign key office_fk;query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0MysqL> drop table offices;query OK, 0 rows affected (0.03 sec)MysqL> show tables;+-------------------+| tables_in_company |+-------------------+| employees         |+-------------------+1 row in set (0.06 sec)

(11):修改表employees存储引擎为MyISAM。

alter table employees ENGINE=MyISAM;

MysqL> alter table employees ENGINE=MyISAM;query OK, 0 rows affected (0.17 sec)Records: 0  Duplicates: 0  Warnings: 0MysqL> show create table employees \G*************************** 1. row ***************************       table: employeesCreate table: CREATE table `employees` (  `employeeNumber` int(11) NOT NulL auto_INCREMENT,  `lastnamee` 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=utf8mb4 ColLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)

(12)将表employees名称修改为employees_info。

alter table employees rename employees_info;

MysqL> alter table employees rename employees_info;query OK, 0 rows affected (0.07 sec)MysqL> show tables;+-------------------+| tables_in_company |+-------------------+| employees_info    |+-------------------+1 row in set (0.00 sec)