运维必须要掌握的几种MySQL数据表基本操作

一、创建

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: 0mysql> show create table employeesg;*************************** 1. row *************************** table: employeescreate 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=latin11 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)