MySQL数据表的基本操作三：综合示例，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: 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
版权＠：转载请标明出处！1、楼主应该再查看下map/reduce的基本概念，以及hadoop相关的基础知识。
2、map/reduce是个计算架构，跟操作mysql没直接关系，如果你想操作mysql，可以用jdbc或是Hive向其传值就可以了。map/reduce不会去操作mysql的实例，那样也不符合map/reduce的设计原则。
3、也不是绝对不能操作，只是没有这么设计和编程吧，再查查资料吧。
一、数据库操作示例：
1、创建数据库和表
drop database if exists mysqlDB;
create database mysqlDB;
use mysqlDB;
create table dept
(
deptno int primary key auto_increment,
dname varchar(14) unique not null,
loc varchar(10)
)ENGINE = innodb CHARSET=gbk;create table emp
(
empno int primary key auto_increment,
ename varchar(10) unique,
job varchar(9) not null,
mgr int,
hiredate datetime,
sal float(7,2) not null,
comm float(7,2),
deptno int,
index par_ind (deptno),
foreign key(deptno) references dept(deptno)
)ENGINE = innodb CHARSET=gbk;2、插入数据：(中文乱码:MY.INI文件)
insert into dept(dname,loc) values ('财务部','美国'),('业务部','中国');
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(1001,'小明','员工',1002,now(),1200,200,1),
(1002,'苍天','主管',1002,now(),1500,500,1),
(1003,'大地','经理',1002,now(),1800,1000,2),
(1004,'无声','文秘',1002,now(),1200,200,2);
或：
insert into tablename(list....)
select <list...> from tablename3、MYSQL脚本批处理执行：
将以上命令写入一个文本文件中，假设为DBscript.sql，命令行下执行：
mysql -u root -p密码 < c:/DBscript.sql;
或者进入命令行后使用
mysql> source c:/DBscript.sql; 。4、批量导入数据：
下列数据分别是dept、emp表的内容，复制到相应文件中，每......余下全文>>
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。