国内最全IT社区平台 联系我们 | 收藏本站
阿里云优惠2流量王
您当前位置:首页 > 数据库 > MySql > MySQL数据表的基本操作三:综合示例

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

来源:程序员人生   发布时间:2014-10-08 08:00:01 阅读次数:2546次

一、创建数据库

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 employeesG; *************************** 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

版权@:转载请标明出处!
生活不易,码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
程序员人生
------分隔线----------------------------
分享到:
------分隔线----------------------------
关闭
程序员人生