18年整理了一半，因为文章无法编辑，所以开了新的更全部——2019-12-12，木言
——————————————————————————————————————
一、基本认识
命令在用；或者/g结束，换句话说，仅按ENTER不执行命令
输入help或者h获得帮助
输入quit或exit退出
MySql不区分大小写，但是很多人愿意对重要语句用大写，用表名等用小写，便于阅读
在 MySQL 中，事务通常以 BEGIN WORK 语句开始，以 COMMIT 或 ROLLBACK（只取其一） 语句结束。在开始与结束声明之间的 SQL 命令就构成了事务的主体。
COMMIT 与 ROLLBACK
MySQL事务主要用到两个关键字 COMMIT 与 ROLLBACK：
成功完成一个事务后，就会执行 COMMIT 命令，从而使施加于所涉及的表上的改变生效。如果事务失败，就会执行 ROLLBACK 命令，将事务中所引用的每一个表都回撤到之前的状态。
通过设定会话变量 AUTOCOMMIT 可以控制事务行为。如果 AUTOCOMMIT 被设为1（默认值），则每一个 SQL 语句（无论是否在事务中）都会被认为是一个完成的事务，则默认当它结束时予以提交。当 AUTOCOMMIT 被设为0（通过命令 SET AUTOCOMMIT=0）时，后续一系列语句就像是一个事务，直到 COMMIT 语句执行为止，不再提交任何行为。
有很多种支持事务表可供选择，但其中最常见的是 InnoDB，TYPE = InnoDB
create table tcount_tbl -> ( -> tutorial_author varchar(40) NOT NULL, -> tutorial_count INT -> ) TYPE=InnoDB;
二、数据库
1. 选择数据库
USE xxx;
2. 调用数据库列表和数据
# 显示可用数据库
SHOW DATABASES;
# 显示可用表
SHOW TABLES;
# 获取customers表的一个列
SHOW COLUMNS FROM customers;
3. 数据类型
三、检索数据
1. 检索列
# 从products表中获取prod_name这一列
SELECT prod_name
FROM products;
# 检索多个列
SELECT prod_name, prod_id, prod_price
FROM products;
# 检索所有列
SELECT * FROM products;
2. 检索不同的行
# 重复的行只显示一次
SELECT DISTINCT vend_id
FROM products;
3. 限定检索
注意，行0开始数，检索5行是:0,1,2,3,4行，第6行同理从行0开始数
# 只检索5行
SELECT prod_id
FROM products
LIMIT 5;
# 检索第6行开始的5行
SELECT prod_id
FROM products
LIMIT 6, 5;
# 限定表名的列
SELECT products.prod_id
FROM products;
四、排序检索数据
1. 排序
# 用prod_name的字母顺序排序
SELECT prod_name FROM products
ORDER BY prod_name;
# 先用价格排序，再用名称排序
SELECT prod_id, prod_price, prod_name FROM products
ORDER BY prod_price, prod_name;
2. 降序
无说明默认升序，降序需要用DESC(descrease)加以说明
如果要在多个列上进行降序，需要对每个列都进行DESC说明
# 价格降序
SELECT prod_id, prod_price, prod_name FROM products
ORDER BY prod_price DESC;
# 先对价格降序，再用产品名称排序（无说明默认升序）
SELECT prod_id, prod_price, prod_name FROM products
ORDER BY prod_price DESC, prod_name;
3. 语法顺序
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
五、过滤数据-使用WHERE子句
1. WHERE子句操作符
= 等于，！= 不等于，< 小于， > 大于， <= 小于等于， >= 大于等于， BETWEEN AND 在指定的两个值之间
2. 检查单个值
# 名字等于fuses
SELECT prod_id, prod_price, prod_name
FROM products
WHERE prod_mane = 'fuses';
# 价格大于10
SELECT prod_id, prod_price, prod_name
FROM products
WHERE prod_price > 10;
#供应商ID不是1003
SELECT vend_id, prod_price, prod_name
FROM products
WHERE vend_id != 1003;
# 范围值检查
SELECT vend_id,prod_name,prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;
3. 空值检查
# NULL表示空值，no value
SELECT vend_id,prod_name,prod_price
FROM products
WHERE prod_price IS NULL;
IS NULL：如果列值为 NULL，则该运算符返回 true。
IS NOT NULL：如果列值不为NULL，则该运算符返回 true。
<=>：该运算符用于两个值的对比，当两个值都为 NULL 时（这一点与 = 运算符不同），返回 true。
包含 NULL 的条件都是比较特殊的。不能在列中使用 = NULL 或 ! = NULL 来寻找 NULL 值。这样的比对通常都是失败的，因为不可能得知这样的比对是否为真。
六、 过滤数据-AND&OR
1. AND表示同时满足所有条件
# 供应商1003，价格小于等于10的产品
SELECT vend_id, prod_name, prod_price
FROM prodcuts
WHERE vend_id = 1003 AND prod_price <= 10;
2. OR表示满足所有的单个条件
# 是1002供应商，或者是价格等于10
SELECT vend_id, prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR prod_price = 10;
3. 次序计算
# MySql优先处理AND, 后处理OR,先满足vend_id = 1003 AND prod_price = 10
SELECT vend_id, prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price = 10;
# 要先处理OR，应该加（）
SELECT vend_id, prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price = 10;
4. IN指定条件范围
# 检索1002，1003供应商并用名称排序
SELECT vend_id, prod_name, prod_price
FROM products
WHERE vend_id IN (1002, 1003)
ORDER BY prod_name;
5. NOT表在WHERE子句中用来否定后跟的条件
NOT IN可以用来取反
# 检索不是1002，1003供应商并用名称排序
SELECT vend_id, prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;
七、 数据过滤-LIKE子句+通配符 %, _
1. %表示任何字符串出现任意次数，区分大小写
# 检索以jet起头的产品名字（和JET起头不匹配）
SELECT prod_name, prod_price
FROM products
WHERE prod_name LIKE 'jet%';
# 检索产品名字中任意位置有care字符
SELECT prod_name, prod_price
FROM products
WHERE prod_name LIKE '%care%';
# 检索产品名字中以s开头e结尾的字符，长度不限
SELECT prod_name, prod_price
FROM products
WHERE prod_name LIKE 's%e';
2. _表示任何字符串出现单次，指一个字符，其他功能和%一样
八、正则表达式进行搜索REGEXP
正则表达式是用来匹配文本的特殊的串（字符集合）
1. 基本字符匹配
# 检索列prod_name包含文本1000的所有行,和LIKE类似
SELECT prod_name, prod_price
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;
# .是正则表达式中一个特殊的字符，表示匹配任意一个字符，检索列prod_name包含文本.000的所有行（比如1000，2000，3000），和LIKE不同
SELECT prod_name, prod_price
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;
2. OR的匹配, |就是正则表达式中的OR
# 检索列prod_name包含文本1000，2000的行
SELECT prod_name, prod_price
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;
3. 匹配几个字符之一
# 匹配1TON, 2TON, 3TON的组合方式, [123]TON = [1|2|3]TON，如果输1|2|3 TON是指1，2，3 TON，不是1TON, 2TON, 3TON.
SELECT prod-name, prod_price
FROM products
WHERE prod_name REGEXP '[1|2|3]TON'
ORDER BY prod_name;
4. 匹配范围
[1-3]是一个范围，[a-z]匹配任意字母字符
5. 匹配特殊字符，用为前导来匹配，如., _
# 匹配带.的特殊字符
SELECT prod-name, prod_price
FROM products
WHERE prod_name REGEXP '.'
ORDER BY prod_name;
6. 空白元字符
f 换页， n 换行， r 回车，t 制表，v 纵向制表
7. 重复元字符
* 0个或多个字符，+ 1个或多个字符，？0个或1个字符，{n} 指定n个字符，{n,} 指定不少于 n个字符，{n,m}指定n-m个字符
8. 定位元字符
^xx 以xx开头，xx$ 以xx结束，[[:<:f]] 词的开始，[[:>:]] 词的结尾
寻找以 'st' 开头的名称，查询如下：
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
寻找以 'ok' 结尾的名称，查询如下：
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
九、文本数值时间计算处理
1. Concat() 拼接，将值联结到一起构成单个值
字符串拼接
select concat(str1,str2...)
把两列合并为一列
select concat(vender, country) as vend
from test;
2. 使用别名
# AS赋予联结后的单个词一个别名
SELECT Contact(vend_name, '(',vend_country,')') AS vend_title
FROM vendors
ORDER BY vend_name;
3. 文本字符串处理
Left() 返回左边的字符串， Right() 返回右边的字符串，Length() 返回字符串的长度，Locate() 找出串的一个子串，SubString() 返回子串的字符，Soundex() 返回字符串的SOUNDEX值(类似发音的字符串)，RTrim删除右边多余的空格，LTrim删除左边多余的空格， Upper() 转为大写，Lower() 转为小写，
# 删除右边空格
SELECT RTim(vend_name), RTrim(vend_country)
FROM vendors
ORDER BY vend_name;
# Upper()转为大写，Lower()转为小写
SELECT vend_name, Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;
4. 日期和时间处理函数
Year() 返回一个时间的年份，Month() 返回一个时间的月份，Date() 返回一个时间的日期，Day() 返回一个时间的天数，Hour() 返回一个时间的小时，Minute() 返回一个时间的分钟，Second() 返回一个时间的秒钟，Now() 返回当前日期和时间，Time() 返回一个日期的时间，AddDate() 增加一个日期，AddTime() 增加一个时间
# 检索一个特定日期的订单
SELECT prod_id, prod_name
FROM products
WHERE Date(order_date) = '2018-12-21'
ORDER BY prod_name;
# 检索特定的时间段
SELECT prod_id, prod_name
FROM products
WHERE Date(order_date) BETWEEN '2017-12-01' AND '2018-12-21';
# 检索特定年份和月份
SELECT prod_id, prod_name
FROM products
WHERE Year(order_date) = 2018 AND Month(order_date) = 9;
5. 算数计算 + - * /
# 检索订单号是2005的产品数量和价格
SELECT prod_id, prod_qty, prod_price
FROM products
WHERE order_num = 2005;
# 上述 检索后计算总价值（数量*价格）
SELECT prod_id, prod_qty, prod_price, prod_qty*prod_price AS expanded_price
FROM products
WHERE order_num = 2005;
6. 数值处理函数
Abs() 返回一个数的绝对值，Sqrt() 返回一个数的平方根， Rand() 返回一个随机数，Pi() 返回圆周率Exp()，返回一个数的指数值，Mod() 返回除操作的余数，Cos() 返回一个角度的余弦值 Sin() 返回一个角度的正弦值，Tan() 返回一个角度的正切值
十、 数据处理
1. AVG() 平均值
# 求产品平均值
SELECT AVG(prod_price) AS avg_price
FROM products;
# AVG(DISTINCT), 相同价格只出现一次，计算平均值
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products;
2. COUNT() 计数
COUNT(*) AS cust_num, 对所有列进行计数的，但是只返回cust_num里的计数结果
COUNT(*)对所有进行计数，COUNT(column)对除掉NULL的列进行计数
# 求客户数量
SELECT COUNT(*) AS num_cust
FROM customers;
# 求有邮箱的客户数量
SELECT COUNT(cust_email) AS num_cust
FROM customers;
3. 最值
# MAX() 最大值
SELECT MAX(prod_price) AS max_price
FROM products;
# MIN() 最小值
SELECT MIN(prod_price) AS min_price
FROM products;
4. SUM() 求和
# 订单中物品为2005的所有数量
SELECT SUM(qty) AS total_items
FROM orderitems
WHERE item_name = 2005;
# 订单中物品为2005的全部金额
SELECT SUM(qty*item_price) AS total_amount
FROM orderitems
WHERE item_name = 2005;
5. SELECT 可以包含多个聚集函数
SELECT COUNT(*) AS item_num
MIN(prod_price) AS min_price
MAX(prod_price) AS max_price
AVG(prod_price) AS avg_price
FROM products;
十一、分组
1. 创建分组
# 不同供应商包含的产品计数并分组
SELECT vend_id, COUNT(*) AS prod_num
FROM products
GROUP BY vend_id;
2. 过滤分组，WHERE针对特定值（每个值，原值），HAVING针对分组过滤后的值
# 检索买了2次以上的客户,此处不能用WHERE
SELECT cust_id, COUT(*) AS order_num
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
# 价格为10以上，具有2个以上产品的供应商
SELECT vend_id, COUNT(*) AS prod_num
FROM vendors
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;
3. 语法顺序
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
十二、子查询
1. 利用子查询过滤
在几个关系表中，检索TNT2物品的客户信息，但是没有直接一个表体现这个信息，一步一步过滤，方式如下：
# 检索包含物品TNT2的所有订单编号
SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';
输出结果（2005，2007）
# 检索该订单编号的所有客户ID
SELECT cust_id
FROM orders
WHERE oder_num IN (2005,2007);
输出结果（1001，1004）
# 检索该客户ID的所有客户信息
SELECT cust_info
FROM customers
WHERE cust_id IN (1001, 1004);
输出结果
利用子查询，方式如下：
SELECT cust_info
FROM customers
WHERE cust_id IN (
SELECT cust_id
FROM orders
WHERE oder_num IN(
SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2'));
个人认为子查询不是最好的方式，看起来很累，所以不推荐使用，也不展开多写
十三、联结表
1. 创建联结 JOIN
# 从2个关系表中导出数据
SELECT prod_price, prod_name, vend_name
FROM products, vendors
WHERE products.vend_id = vendors.vend_id
ORDER BY vend_name, prod_name;
# INNOR JOIN...ON内部联结-上述的第二种写法
SELECT prod_price, prod_name, vend_name
FROM products
INNER JOIN vendors ON products.vend_id = vendors.vend_id;
2. 联结多个表
SELECT prod_price, prod_name, vend_name, order_num
FROM products, vendors, orders
WHERE products.vend_id = vendors.vend_id
AND products.prod_id = orders.prod_id
AND order_num = 2005； # AND起过滤作用
3. 自联结
# 先找到物品ID是TNT2的供应商，再找到此供应商ID下的其他物品，把1个表别名成2个表，p1输出物品名字和ID，p2用作关联TNT2的语法和结果输出
SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'TNT2';
4. 自然联结
关系表中有一样的列，通过表别名和筛选，使每个列只返回一次
# *通配符只对c表使用，其他表的重复列没有被检索出来
SELECT c*, o.order_item, o.order_date, oi.prod_id, oi.prod_qty, oi.prod_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND p.prod_id = oi.prod_id
AND prod_id = 'TNT2';
5. 外部联结
# 为了检索所有客户的下单数量，包括没有订单的客户, LEFT OUTER JOIN...ON表示从左边的表(customers)中选择所有行
SELECT customers.cust_id, orders.order_num
FROM customers
LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
6. 使用带聚集函数的联结
# 内部联结，表之间相等的行联结
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS ord_num
FROM customers INNER JOIN orders
ON customers_cust.id = orders.cust_id
GROUP BY customers_cust.id;
# 外部联结，表之间有不相关联的行联结
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS ord_num
FROM customers LEFT OUTER JOIN orders
ON customers_cust.id = orders.cust_id
GROUP BY customers_cust.id;
十四、更新更改
1. 更新查询Update
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
# 把李四的地址改为上海（默认Beijing)
Update employee set city = 'Shanghai' where id = 2;
2. 更改表格
在表employee增加一列addr
Alter table employee add colunm addr varchar(40);
更新表名
Rename Table 表名 to 新表名；
十五、Alter选择
1.删除、添加列或对其重新定位
从表中删除 i 这一列
ALTER TABLE testalter_tbl DROP i; # 如果表中只有一列，则 DROP 子句不起作用
下面我们再把 i 这一列恢复到 testalter_tbl 中，使用 ADD 并指定列定义：
ALTER TABLE testalter_tbl ADD i INT;
要想把列放到一个特定位置，可以使用两种方法，第一种方法是使用 FIRST，让指定列成为第一列；第二种则采用 AFTER 后跟给定列名的方式，指示新列应该放到给定列名的后面。
ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT FIRST; ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT AFTER c;
标识符 FIRST 和 AFTER 只能和 ADD 子句一起使用。这也意味着，如果要重新定位一列，就必须先用 DROP 删除它，然后再用 ADD 将它添加到新的位置。
2. 更新列名和数据类型
更改数据类型，把列 c 从 CHAR(1) 变为 CHAR(10)：
ALTER TABLE testalter_tbl MODIFY c CHAR(10);
CHANGE 的语法稍有不同。必须把所要改变的列名放到 CHANGE 关键字的后面，然后指定新的列定义：
ALTER TABLE testalter_tbl CHANGE 原列名 新列名 列定义;
如果想利用 CHANGE 将 j 从 BIGINT 转为 INT，并且不改变列名，则语句如下：
ALTER TABLE testalter_tbl CHANGE j j INT;
3.ALTER TABLE 对 Null 及默认值属性的作用
在利用 MODIFY 或 CHANGE 修改列时，还可以指定该列是否能有 NULL 值，以及它的默认值。如果我们不这样处理，MySQL 会自动为这些属性指定相关值。
# NOT NULL 列默认值为100：
MODIFY j BIGINT NOT NULL DEFAULT 100;
# 使用 ALTER 命令可以改变任何列的默认值
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
# 使用 DROP 子句与 ALTER 命令，可以去除任何列中的默认限制
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
4. 改变表类型
结合使用 TYPE 子句与 ALTER 命令，可以使用表类型
ALTER TABLE testalter_tbl TYPE = MYISAM;
5. 对表进行重命名
使用 ALTER TABLE 语句的 RENAME 选项可以对表进行重命名
ALTER TABLE testalter_tbl RENAME TO alter_tbl;
十六、Index索引
1.简单而唯一的索引
可以为表创建唯一索引，唯一索引要求任意两行的索引值不能相同
CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);
# 可以使用一或多个列来创建索引
CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author)
# 降序在列中索引数值，可以在列名后添加保留字 DESC(Descending)
CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author DESC)
2. 添加与删除 INDEX 的ALTER 命令
（1）为表添加索引，可以采用4种语句
# 该语句添加一个主键。意味着索引值必须是唯一的，不能为 NULL
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
# 该语句为必须唯一的值（除了 NULL 值之外，NULL 值可以多次出现）
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
# 语句为可能多次出现的值创建一般索引
ALTER TABLE tbl_name ADD INDEX index_name (column_list)
# 语句创建专用于文本搜索的 FULLTEXT 索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)
（2）为现有表添加索引
ALTER TABLE testalter_tbl ADD INDEX (c);
# 可以使用 DROP 子句以及 ALTER 命令删除索引
ALTER TABLE testalter_tbl DROP INDEX (c);
3. 利用 ALTER 命令来添加与删除主键
添加主键也采用类似方式，但要保证主键一定在列上，是 NOT NULL
# 在现有表中添加主键，先使列为 NOT NULL，然后再将其作为主键
ALTER TABLE testalter_tbl MODIFY i INT NOT NULL; ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
# 删除一个主键
ALTER TABLE testalter_tbl DROP PRIMARY KEY;
十七、表
1. 创建表
CREATE TABLE SalesSummary (
product_name VARCHAR(50) NOT NULL ,
total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 ,
avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 ,
total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 );
# 通常设置：ENGINE=InnoDB default CHARSET=utf8；
# 插入值
INSERT INTO SalesSummary
(product_name, total_sales, avg_unit_price, total_units_sold)
VALUES ('cucumber', 100.25, 90, 2);
2. 查看表
SELECT * FROM SalesSummary;
利用 SHOW TABLES 命令显示表时，临时表不会出现在结果列表中。如果退出 MySQL 会话，就会执行 SELECT 命令，那么数据库中将没有任何数据，甚至临时表也不存在了。
3. 删除表
默认情况下，当与数据库的连接终止时，临时表就不再存在。不过如果想在数据库处于连接时就删除它们，可以用 DROP TABLE 命令来删除。
DROP TABLE SalesSummary;
删除程度可从强到弱如下排列：
drop table tb;
drop 是直接将表格删除，无法找回。例如删除 user 表：
drop table user;
truncate （table） tb;
truncate 是删除表中所有数据，但不能与where一起使用；
delete from tb (where);
delete 也是删除表中数据，但可以与where连用，删除特定行；
-- 删除表中所有数据
delete from user;
-- 删除指定行
delete from user where username ='Tom';
4. 复制表
可以采用如下步骤来处理这种情况。
使用 SHOW CREATE TABLE 或 CREATE TABLE 语句指定源表的结构、索引以及所有的内容。
调整语句，将表名改为克隆表的名称，执行语句。这样就对表进行了克隆。另外，如果想要克隆表的全部内容，也可以使用 INSERT INTO ... SELECT 语句。
步骤1：获取表的完整结构
步骤2：重新命名该表，创建另一个表
步骤3：执行完步骤2后，就在数据库中创建了一个克隆表。如果想从旧表中复制数据，可以使用 INSERT INTO... SELECT 语句。
十八、 Using Sequences序列
1. 使用 AUTO_INCREMENT 列
先创建一个表，然后插入一些行，不需要提供记录ID，因为这是由 MySQL 自动增加的
CREATE TABLE insect (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name VARCHAR(30) NOT NULL, # type of insect
date DATE NOT NULL, # date collected
origin VARCHAR(30) NOT NULL # where collected );
# 插入值
INSERT INTO insect (id,name,date,origin)
VALUES (NULL,'housefly','2001-09-10','kitchen'),
(NULL,'millipede','2001-09-10','driveway'),
(NULL,'grasshopper','2001-09-10','front yard');
2.对已有序列进行重新编号
如果一定要对 AUTO_INCREMENT 列进行重新排序，那么正确的方式是将该列从表中删除，然后再添加它。下面这个范例中就用了这个技巧，在 insect 表中对 id 值重新排序。
ALTER TABLE insect DROP id;
ALTER TABLE insect
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);
3. 以特定值作为序列初始值
MySQL 默认以 1 作为序列初始值，但你也可以在创建表时指定其他的数字，以 100 作为序列初始值
CREATE TABLE insect (
id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
PRIMARY KEY (id),
name VARCHAR(30) NOT NULL, # type of insect
date DATE NOT NULL, # date collected
origin VARCHAR(30) NOT NULL # where collected );
十九、Handling Duplicates 重复处理
1. 防止表中出现重复记录
# 可以在表中正确的字段内使用 PRIMARY KEY 或 UNIQUE 索引来终止重复记录。比如下面这张表，由于没有这样的索引或主键，因此 first_name与last_name 就被重复记录了下来。
CREATE TABLE person_tbl (
first_name CHAR(20),
last_name CHAR(20),
sex CHAR(10) );
# 为了防止表中出现同样姓名的值，为其添加一个 PRIMARY KEY。同时要注意将索引列声明为 NOT NULL，这是因为 PRIMARY KEY 不允许出现空值
CREATE TABLE person_tbl (
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
PRIMARY KEY (last_name, first_name) );
# 不要使用 INSERT ，使用 INSERT IGNORE。如果该记录与现存的某个记录重复，IGNORE 关键字就会让 MySQL 默默地将其摒弃，不会产生任何错误。
下面这个范例不会产生任何错误，不会插入会产生重复的记录。
INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES ( 'Jay', 'Thomas');
# 使用 REPLACE 而不是 INSERT。如果是一个重复记录，新的记录将会替换旧有记录。
REPLACE INTO person_tbl (last_name, first_name) VALUES ( 'Ajay', 'Kumar');
应该根据想要达到的重复处理行为来选择INSERT IGNORE 和 REPLACE。INSERT IGNORE 会保存重复记录的第一个，抛弃其余的记录；REPLACE 保存最后一个记录，去掉在其之前的所有记录。
# 强制唯一性的另一种办法是为表添加 UNIQUE 索引而不是主键。
CREATE TABLE person_tbl (
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10)
UNIQUE (last_name, first_name) );
2. 确认重复记录，并计算重复记录数
# 下面是计算表中姓名记录重复的查询：
SELECT COUNT(*) as repetitions, last_name, first_name
FROM person_tbl GROUP BY last_name, first_name
HAVING repetitions > 1;
该查询返回表 person_tbl 中所有的重复记录。一般来说，要想确认重复记录，需要采取以下步骤：
- 确定可能产生重复记录的列。
- 在列选择列表中显示所有列，利用 COUNT(*) 。
- 利用 GROUP BY 子句列出列。
- 加入 HAVING 子句排除唯一值。需要让组计数大于1。
3. 从查询结果中消除重复记录
# 使用DISTINCT(独的) 和 SELECT 语句来查找表中的重复记录。
SELECT DISTINCT last_name, first_name
FROM person_tbl
ORDER BY last_name;
# 另一种办法是添加 GROUP BY 子句，命名选择的列。消除重复记录并只选择指定列中的唯一值组合。
SELECT last_name, first_name
FROM person_tbl
GROUP BY (last_name, first_name);
4.使用表替换去除重复记录
下面这种技巧也可以消除表中存在的所有重复记录。
CREATE TABLE tmp
SELECT last_name, first_name, sex
FROM person_tbl;
GROUP BY (last_name, first_name);
DROP TABLE person_tbl;
ALTER TABLE tmp RENAME TO person_tbl;
为表加入 INDEX 或 PRIMARY KEY 。即使该表已经存在，你也可以利用这种技巧消除重复记录，这种做法将来也依然保险。
ALTER IGNORE TABLE person_tbl ADD PRIMARY KEY (last_name, first_name);
二十、数据导入
1. excel文件数据导入
（1）excel文件另存为 "CSV(逗号分隔)(*.csv)"
只保留需要的数据，不需要表头
默认保存的文件编码是ANSI，如果你的数据库(数据表)使用UTF-8编码，那么一定要将这个csv文件另存为UTF-8格式！
（2）在数据库中创建一个表，表头对应excel数据的表头
（3）导入
导入错误原因：
excel表的格式不对，.csv UTF-8，修改下excel表格式
创建的表头类型和实际数据不符，如文本创建为数字，如长度超出创建的范围，修改下创建的表
注：导出用Data Export就可以
2. 利用 LOAD DATA 导入数据
MySQL 利用 LOAD DATA 语句作为批量数据加载器，从当前目录中读取 dump.txt 文件，然后把它加载进当前数据库的表 mytbl 中。
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
# 为了明确指定文件格式，使用 FIELDS 子句来描述行内字段特征， LINES 子句指定行末尾序列。下例中的 LOAD DATA 语句表明，数据文件中的值由冒号（:）分隔，每行由换行符及回车符所终止。
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl FIELDS TERMINATED BY ':' LINES TERMINATED BY 'rn';
# LOAD DATA 假定数据文件中的列的顺序与表中列的顺序相同。如果不为真，可以指定一个列表来指示数据文件中具体表列的加载方式。假如表有3个列：a、b和c，但数据文件中对应的是列b、c与a，则可以这样加载。
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl (b, c, a);
转播
