MySQL資料表的基本操作三：綜合樣本，mysql基本操作

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: 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` 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)

如果您們在嘗試的過程中遇到什麼問題或者My Code有錯誤的地方，請給予指正，非常感謝！

連絡方式：david.louis.tian@outlook.com

著作權＠：轉載請標明出處！

助：mapreduce 操作mysql資料庫的是執行個體，使用新的API？

1、樓主應該再查看下map/reduce的基本概念，以及hadoop相關的基礎知識。
2、map/reduce是個計算架構，跟操作mysql沒直接關係，如果你想操作mysql，可以用jdbc或是Hive向其傳值就可以了。map/reduce不會去操作mysql的執行個體，那樣也不符合map/reduce的設計原則。
3、也不是絕對不能操作，只是沒有這麼設計和編程吧，再查查資料吧。
 
幫忙做一個MySQL資料庫操作的作業

一、資料庫操作樣本：
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表的內容，複製到相應檔案中，每......餘下全文>>