一、创建数据库
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 employees\G;*************************** 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` varch<i>本文来源gaodai$ma#com搞$代*码网2</i>ar(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)
如果您们在尝试的过程中遇到什么问题或者我的代码有错误的地方，请给予指正，非常感谢！
联系方式：[email protected]
版权＠：转载请标明出处！
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：MySQL数据表的基本操作三：综合示例_MySQL
转载请注明原文链接：MySQL数据表的基本操作三：综合示例_MySQL
