本学习笔记参考《MySQL必知必会》和官方手册MySQL 5.6 Reference Manual
MySQL入门（二）
MySQL入门（三）
本文内容：
- MySQL初体验
- MySQL创建和操作表
- MySQL插入更新删除数据
一、MySQL初体验
我的 mysql 版本是5.6.24，利用 apt-get 安装在 Ubuntu-12.04LTS上。
$ sudo service mysql stop$ sudo service mysql start$ sudo service mysql status$ mysql --help$ mysql -h host -u user -p连接到服务器上，host为主机名，user为用户名，如果服务器所在的机器是现在所登陆的机器，则不需要指定主机名。
登陆后的提示符为 mysql>，每条 SQL 语句以分号 ; 结束。
mysql> SHOW DATABASES;
显示当前存在在服务器上的数据库。
mysql> USE [database-name]
访问或进入指定的数据库，该条语句可以像 QUIT 一样不加分号（加了也可以）。
mysql> CREATE DATABASE [ database-name ];
mysql> USE [ database-name ]
$ mysql -h host -u user -p [ database-name ]
mysql> SHOW TABLES;
采用 MySQL必知必会 一书的例子进行学习。下面是本例子采用的数据库的生成脚本。
create.sql
######################################### MySQL Crash Course# http://www.forta.com/books/0672327120/# Example table creation scripts################################################################# Create customers table########################CREATE TABLE customers( cust_id int NOT NULL AUTO_INCREMENT, cust_name char(50) NOT NULL , cust_address char(50) NULL , cust_city char(50) NULL , cust_state char(5) NULL , cust_zip char(10) NULL , cust_country char(50) NULL , cust_contact char(50) NULL , cust_email char(255) NULL , PRIMARY KEY (cust_id)) ENGINE=InnoDB;########################## Create orderitems table#########################CREATE TABLE orderitems( order_num int NOT NULL , order_item int NOT NULL , prod_id char(10) NOT NULL , quantity int NOT NULL , item_price decimal(8,2) NOT NULL , PRIMARY KEY (order_num, order_item)) ENGINE=InnoDB;###################### Create orders table#####################CREATE TABLE orders( order_num int NOT NULL AUTO_INCREMENT, order_date datetime NOT NULL , cust_id int NOT NULL , PRIMARY KEY (order_num)) ENGINE=InnoDB;######################## Create products table#######################CREATE TABLE products( prod_id char(10) NOT NULL, vend_id int NOT NULL , prod_name char(255) NOT NULL , prod_price decimal(8,2) NOT NULL , prod_desc text NULL , PRIMARY KEY(prod_id)) ENGINE=InnoDB;####################### Create vendors table######################CREATE TABLE vendors( vend_id int NOT NULL AUTO_INCREMENT, vend_name char(50) NOT NULL , vend_address char(50) NULL , vend_city char(50) NULL , vend_state char(5) NULL , vend_zip char(10) NULL , vend_country char(50) NULL , PRIMARY KEY (vend_id)) ENGINE=InnoDB;############################ Create productnotes table###########################CREATE TABLE productnotes( note_id int NOT NULL AUTO_INCREMENT, prod_id char(10) NOT NULL, note_date datetime NOT NULL, note_text text NULL , PRIMARY KEY(note_id), FULLTEXT(note_text)) ENGINE=MyISAM;###################### Define foreign keys#####################ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);populate.sql
######################################### MySQL Crash Course# http://www.forta.com/books/0672327120/# Example table population scripts################################################################### Populate customers table##########################INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', [email protected]');INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', [email protected]');INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', [email protected]');INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');######################### Populate vendors table########################INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');########################## Populate products table#########################INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');######################## Populate orders table#######################INSERT INTO orders(order_num, order_date, cust_id)VALUES(20005, '2005-09-01', 10001);INSERT INTO orders(order_num, order_date, cust_id)VALUES(20006, '2005-09-12', 10003);INSERT INTO orders(order_num, order_date, cust_id)VALUES(20007, '2005-09-30', 10004);INSERT INTO orders(order_num, order_date, cust_id)VALUES(20008, '2005-10-03', 10005);INSERT INTO orders(order_num, order_date, cust_id)VALUES(20009, '2005-10-08', 10001);############################ Populate orderitems table###########################INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20005, 1, 'ANV01', 10, 5.99);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20005, 2, 'ANV02', 3, 9.99);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20005, 3, 'TNT2', 5, 10);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20005, 4, 'FB', 1, 10);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20006, 1, 'JP2000', 1, 55);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20007, 1, 'TNT2', 100, 10);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20008, 1, 'FC', 50, 2.50);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20009, 1, 'FB', 1, 10);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20009, 2, 'OL1', 1, 8.99);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20009, 3, 'SLING', 1, 4.49);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20009, 4, 'ANV03', 1, 14.99);############################## Populate productnotes table#############################INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(101, 'TNT2', '2005-08-17','Customer complaint:Sticks not individually wrapped, too easy to mistakenly detonate all at once.Recommend individual wrapping.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(102, 'OL1', '2005-08-18','Can shipped full, refills not available.Need to order new can if refill needed.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(103, 'SAFE', '2005-08-18','Safe is combination locked, combination not provided with safe.This is rarely a problem as safes are typically blown up or dropped by customers.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(104, 'FC', '2005-08-19','Quantity varies, sold by the sack load.All guaranteed to be bright and orange, and suitable for use as rabbit bait.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(105, 'TNT2', '2005-08-20','Included fuses are short and have been known to detonate too quickly for some customers.Longer fuses are available (item FU1) and should be recommended.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(106, 'TNT2', '2005-08-22','Matches not included, recommend purchase of matches or detonator (item DTNTR).');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(107, 'SAFE', '2005-08-23','Please note that no returns will be accepted if safe opened using explosives.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(108, 'ANV01', '2005-08-25','Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(109, 'ANV03', '2005-09-01','Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(110, 'FC', '2005-09-01','Customer complaint: rabbit has been able to detect trap, food apparently less effective now.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(111, 'SLING', '2005-09-02','Shipped unassembled, requires common tools (including oversized hammer).');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(112, 'SAFE', '2005-09-02','Customer complaint:Circular hole in safe floor can apparently be easily cut with handsaw.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(113, 'ANV01', '2005-09-05','Customer complaint:Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(114, 'SAFE', '2005-09-07','Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.Comment forwarded to vendor.');mysql> SHOW TABLES;
+———————–+
| Tables_in_crashcourse |
+———————–+
| customers |
| orderitems |
| orders |
| productnotes |
| products |
| vendors |
+———————–+
6 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM products; 
mysql> SELECT prod_id, vend_id, prod_name, prod_price FROM products; 
二、MySQL创建和操作表
创建和操作表的代码参考上面 create.sql
2.1 NULL
NULL 代表允许在插入行时不给出该列的值，NOT NULL 代表该列不接受没有值的行。
2.2 主键
主键可是是一列，也可以是多列，如果使用多列，则这些列的组合必须唯一，比如：
PRIMARYKEY (order_num, order_item)
2.3 关于 AUTO_INCREMENT
如果某列被指定为 AUTO_INCREMENT，每插入一行该列自动增长，也可以简单的在 INSERT 语句中指定一个值，只要它是唯一的即可，该值将被用来替代自动生成的值，后续的增量将开始使用该手工插入的值。在使用 AUTO_INCREMENT 列时可使用 last_insert_id() 函数获得最后一个 AUTO_INCREMENT 值。
2.4 default
使用 DEFAULT 指定某一列的默认值，默认值不能使用函数，只能使用常量。
2.5 引擎类型
目前所创建的表都以 ENGINE=InnoDB 结尾，与其他 DBMS 一样，MySQL 有一个具体管理和处理数据的内部引擎。在你使用 CREATE TABLE 语句时，该引擎具体创建表，而在你使用 SELECT 语句或其他语句进行数据处理时，该引擎在内部处理你的请求。MySQL 具有多种引擎，它打包多个引擎，这些引擎隐藏在MySQL服务器内，全都能执行SQL语句，各个引擎的功能和特性不同。
InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；
MEMORY在功能上等同于MyISAM，但由于数据存储在内存中，速度很快（特别适合于临时表）；
MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。
引擎类型可以混用，但是外键不能跨引擎。
2.6 更新表
使用 ALTER TABLE 语句，一般情况下，当表中存储数据后就不应该被更新。使用 ALTER TABLE 语句要小心。
给表添加一个列：
mysql> ALTER TABLE mytable -> ADD myaddress char(50);删除刚刚添加的列：
mysql> ALTER TABLE mytable -> DROP COLUMN myaddress;ALTER TABLE 常见的用途是来定义外键，如上面的代码所示。
2.7 删除表和重命名表
mysql> DROP TABLE mytable2;
mysql> RENAME TABLE mytable2 TO mytable;
这条语句删除 mytable2 表，删除表没有确认，也不能撤销，执行这条语句将永久删除该表。
三、插入更新删除数据
插入更新删除数据的代码参考上面 populate.sql
3.1 数据插入
插入完整的行
mysql> INSERT INTO mytable VALUES ( 1001,'Evan','male','ShangHai','12345');这种方式必须按次序给出所有列的值，如果不想给值的可以给出 NULL ，这种方式依赖于表中的列次序，不可靠。
可靠的方式是给出每列的名字（可以不按照表中列的次序）：
mysql> INSERT INTO mytable(name, address, phone, gender) VALUES('Ive', 'LuoYang', '887631', 'female');也可以一次插入多行，像这样：
mysql> INSERT INTO mytable(name, address, phone, gender) -> VALUES('Unn', 'LuoYang', '887631', 'female'), -> ('Angle', 'QingDao', '23667', 'female');插入检索出的数据
新建一个表 mytable_new ，把 mytable 里的数据导入到 mytable_new 中：
mysql> INSERT INTO mytable_new( myid, name, gender) -> SELECT myid, name, gender -> FROM mytable;3.2 数据更新
基本的 UPDATE 语句由三部分构成：要更新的表，列名和他们的新值，确定要更新行的过滤条件。
更新某一行：
mysql> UPDATE mytable_new -> SET name = 'Lucy' -> WHERE myid = 1002;一定要加上 WHERE 的过滤条件，否则此条语句会更新表中的所有行的 name 字段。
更新多个列：
mysql> UPDATE mytable_new -> SET name = 'Ann', -> myphone = '1324576' -> WHERE myid = 1002;在用 UPDATE 语句进行更新多行的操作时，如果发生错误，则整个 UPDATE 更新操作将被取消，发生错误前已经更新过的列将恢复原来的值。为了在发生错误时继续进行更新可使用 IGNORE 关键字：
mysql> UPDATE IGNORE mytable_new ...为了删除某个列的值，可设置为 NULL（假设允许为NULL）:
mysql> UPDATE IGNORE mytable_new -> SET name = NULL -> WHERE myid = 1003;3.3 数据删除
mysql> DELETE FROM mytable_new -> WHERE myid = 1004;记得加上 WHERE 过滤条件，否则会删除表中所有的行。
DELETE 语句删除表中的某一行或者所有行，但不会删除表本身。
如果想删除表中的所有行可以使用更快的语句：
TRUNCATE TABLE该语句实际上是删除表，然后在新建一个表。
使用插入更新删除语句时要特别小心，因为 MySQL 没有 undo 操作。