- 1-数据库基础
- 基本概念
- 2-准备数据
- 导入数据库
- 使用数据库和表
- 3-常用语法
- SELECT语句
- 排序检索
- 最高值
- 使用WHERE过滤数据
- 数据过滤
- 用通配符进行过滤
- 4-使用正则表达式
- 基本字符匹配
- 进行OR匹配
- 匹配几个字符之一
- 匹配范围
- 匹配特殊字符
匹配字符类- 匹配多个实例
- 定位符
- 5-创建计算字段
- 计算字段
- 拼接字段
- 使用别名
- 执行算术计算
- 6-使用数据处理函数
- 文本处理函数
- 日期和时间处理函数
- MySQL使用的日期格式
- 数值处理函数
- 7-聚合数据
- 8-分组数据
- 创建分组
- 过滤分组
- 分组和排序
- SELECT子句顺序
- 9-使用子查询
- 作为计算字段使用子查询
- 11-组合查询
- 组合查询
- 创建组合查询
- 12-全文本搜索
- 使用全文本搜索
- 启用全文本搜索支持
- 进行全文本搜索
- 使用扩展查询
- 布尔文本搜索
- 全文本搜索的使用说明
- 13-插入、删除、修改数据
- 插入完整的行
- 插入多个行
- 插入检索出的数据
- 更新数据
- 删除数据
- 更新和删除的指导原则
- 14-创建和操纵表
- 创建表\`create\`
- 更新表\`alter\`
- 删除表\`drop\`
- 重命名表\`rename\`
- 注释
1-数据库基础
SQL（发音为字母S-Q-L或sequel）是结构化查询语言（Structured Query Language）的缩写。SQL是一种专门用来与数据库通信的语言。
基本概念
- 数据库（
database）保存有组织的数据的容器（通常是一个文件或一组文件）。 - 表（
table）某种特定类型数据的结构化清单。 - 模式（
schema）关于数据库和表的布局及特性的信息。 - 列（
column）表中的一个字段。所有表都是由一个或多个列组成的。 - 数据类型（
datatype）所容许的数据的类型。每个表列都有相应的数据类型，它限制（或容许）该列中存储的数据。 - 行（
row）表中的一个记录 - 主键（
primary key）一一列（或一组列：单个列的值或者一组列的组合需要保证唯一），其值能够唯一区分表中每个行。
我的理解可以将 数据库 比作一个 书柜 ，然后里面的 表 看作一本本 书，每本书的名字是不同的。 模式 可以看作你在书柜里面摆放书的方式不同，而 列 你可以看作书的每一 章， 数据类型 比作书的 页面，是 文字还是插图， 行就是书的 内容， 主键，可以看作是找到一段话的方法，常见的是 页码。
2-准备数据
$ mysql -u root -p
-u 用户名，-p 输入密码， -h 主机名， -P 端口，注意此时的大小写。mysql --help 命令行选项和参数列表
连接到数据库需要：主机名（本地为localhost）、端口（如果使用默认端口3306之外的端口）、合法的用户名、用户口令（如果需要）
导入数据库
下载create.sql和populate.sql两个sql脚本文件，其中，create.sql包含创建6个数据库表的MySQL语句，populate.sql包含用来填充这些表的INSERT语句。执行下列操作：
-- 创建数据库
CREATE DATABASE testdb；
-- 使用数据库
-- 必须先使用USE打开数据库，才能读取其中的数据。
USE testdb;
-- 执行sql脚本
以上为准备工作。
create.sql
########################
# Create customers table 可以直接复制到SQL编译器下面运行脚本运行
########################
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);
数据库ER图
populate.sql
##########################
# 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', 'ylee@coyote.com');
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', 'rabbit@wascally.com');
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', 'sam@yosemite.com');
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.'
);
使用数据库和表
使用数据库
use testdb;显示可用的数据库列表
SHOW DATABASES;获得一个数据库内的表的列表
SHOW TABLES;用来显示表列
SHOW COLUMNS FROM customers; DESCRIBE customers;其他SHOW语句
SHOW STATUS -- 用于显示广泛的服务器状态信息 SHOW CREATE DATABASE -- 显示创建特定数据库的MySQL语句 SHOW CREATE TABLE -- 显示创建特定表的语句 SHOW GRANTS -- 显示授予用户（所有用户或特定用户）的安全权限 SHOW ERRORS -- 显示服务器错误 SHOW WARNINGS -- 警告信息
3-常用语法
SELECT语句
检索单个列
-- 检索products表中的prod_name列 SELECT prod_name FROM products;检索多个列
-- 检索products表中的prod_id，prod_name和prod_price列 SELECT prod_id, prod_name, prod_price FROM products;检索所有列
-- 检索products表中的所有列 SELECT * FROM products;检索不同的行
-- DISTINCT关键字必须直接放在列名的前面， -- 不能部分使用DISTINCT，DISTINCT关键字应用于所有列而不仅是前置它的列。【重点】 SELECT DISTINCT vend_id FROM products;限制结果，指定返回前几行
-- 返回不多于5行 SELECT prod_name FROM products LIMIT 5; -- 返回从第5行开始的5行 SELECT prod_name FROM products LIMIT 5,5;-
- *
检索出来的第一行为行0，因此LIMIT 1,1检索出来的是第二行而不是第一行
MySQL 5 支持LIMIT的另一种替代语法LIMIT 4 OFFSET 3为从行3开始取4行，同LIMIT 3,4
-- 使用完全限定的表名
SELECT products.prod_name FROM products;
SELECT products.prod_name FROM crashcoures.products;
排序检索
-- 排序数据
SELECT prod_name
FROM products
ORDER BY prod_name;
-- 按多个列排序
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name;
对于上述例子中的输出，仅在多个行具有相同的`prod_price`值时才对产品按`prod_name`进行排序。如果`prod_price`列中所有的值都是唯一的，则不会按`prod_name`排序。
-- 指定排序方向
-- 默认升序排序，降序使用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;
`DESC`关键字只应用到直接位于其前面的列名。上例中，只对`prod_price`列指定`DESC`，对`prod_name`列不指定。升序关键字`ASC`，可省略
### 最高值
SELECT prod_proce FROM products
ORDER BY prod_price DESC LIMIT 1;
\*\*【注意】\*\*给出`ORDER BY`句子时，应保证位于`FROM`句子之后，如果使用`LIMIT`，应位于`ORDER BY`之后。
`order by` —— `limit`
### 使用WHERE过滤数据
-- 返回prod_price为2.50的行
SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50
* WHERE子句操作符
| 符号 | 说明 |
| --- | --- |
| \= | 等于 |
| <> | 不等于 |
| ！= | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| \> | 大于 |
| \>= | 大于等于 |
| BETWEEN | 在指定的两个值之间 |
-- 检查单个值
-- 返回prod_name为Fuses的一行（匹配时默认不区分大小写）
SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses';
-- 列出小于10美元的所有产品
SELECT prod_name, prod_price FROM products WHERE prod_price < 10;
-- 列出小于等于10美元的所有产品
SELECT prod_name, prod_price FROM products WHERE prod_price <= 10;
-- 不匹配检查
-- 列出不是1003的所有产品
SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;
SELECT vend_id, prod_name FROM products WHERE vend_id ！= 1003;
-- 范围值检查
-- 检索价格在5-10美元之间的所有产品
SELECT prod_name, prod_price FROM products
WHERE prod_price BETWEEN 5 AND 10;
-- 空值检查
-- 返回价格为空的所有产品
SELECT prod_name FROM products WHERE prod_price IS NULL;
### 数据过滤
| 优先级 | **运算符** |
| --- | --- |
| 1 | ! |
| 2 | \-(负号)、~(按位取反) |
| 3 | ^(按位异或) |
| 4 | \*、/(DIV)、%(MOD) |
| 5 | +、- |
| 6 | \>>、<< |
| 7 | & |
| 8 | | |
| 9 | \=(比较运算)、<=>、<、<=、>、>=、!=、<>、IN、IS NULL、LIKE、REGEXP |
| 10 | BETWEEN AND、CASE、WHEN、THEN、ELSE |
| 11 | NOT |
| 12 | &&、AND |
| 13 | XOR |
| 14 | ||、OR |
| 15 | \=(赋值运算)、:= |
-- AND操作符
-- 检索由1003制造且价格小于等于10美元的所有产品的名称和价格
SELECT prod_id, prod_price, prod_name FROM products
WHERE vend_id = 1003 AND prod_price <= 10;
-- OR操作符
-- 检索由1002和1003制造的产品的名称和价格
SELECT prod_name, prod_price FROM products
WHERE vend_id = 1002 or vend_id = 1003;
-- 计算次序
-- AND的优先级高于OR【见上表】
SELECT prod_name, prod_price FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
-- IN操作符
-- 用来指定条件范围，取合法值的由逗号分隔的清单全部在圆括号中。
-- IN比OR执行更快，最大的优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句
SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003)
ORDER BY prod_name;
-- NOT操作符
-- 列出除1002，1003之外所有供应商供应的产品
SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;
**【注意】**
* `NULL`和 0 空字符串 或者空格 是不同的
* `OR` 是匹配任意条件，不是所有条件
* `AND`的优先级是比`OR`比较高的
* 支持 `NOT in` `NOT between` `NOT EXISTS`;甚至 `NOT where`
* 计算大于逻辑运算符优先级
### 用通配符进行过滤
**LIKE操作符**
LIKE指示MYSQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。
-- 百分号（%）通配符【多个】
-- 表示任何字符出现任意次数
-- 例：找出所有jet起头的产品
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';
-- 例：使用多个通配符,匹配任何位置包含anvil的值，不论它之前或之后出现什么字符
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';
-- 例：找出s起头e结尾的所有产品
SELECT prod_name FROM products WHERE prod_name LIKE 's%e';
`%`可以匹配`0`个字符，`%`代表搜索模式中给定位置的`0`个、`1`个或`多个`字符 尾空格可能会干扰通配符，例如，在保存词anvil时，如果它后面有一个或多个空格，则子句`WHERE prod_name LIKE '%anvil'`将不会匹配它们，因为在最后的l后有多余的字符。解决这个问题的一个简单的办法是在搜索模 式最后附加一个%。一个更好的办法是使用函数去掉首尾空格。
-- 下划线(_)通配符
-- 只匹配单个字符而不是多个字符【单个】
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';
**使用技巧**
* **不要过度使用通配符**，如果其他操作符能够达到目的应该使用其他操作符
* 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索的开始处。把通配符置于搜索模式的开始处搜索起来是**最慢**的。
* 仔细注意通配符的位置
* 【总结】可以用但是效率很慢
4-使用正则表达式
---------
### 基本字符匹配
-- 例：检索prod_name包含文本1000的所有行
-- REGEXP后所跟的东西作为正则表达式处理
SELECT prod_name FROM products WHERE prod_name REGEXP '1000'
ORDER BY prod_name;
-- .表示匹配任意一个字符
SELECT prod_name FROM products WHERE prod_name REGEXP '.000'
ORDER BY prod_name;
* LIKE和REGEXP的区别：LIKE '1000'匹配整个列值，等于'1000'时才会返回相应行，而REGEXP '1000'在列值内进行匹配，如果包含'1000'则会返回相应行。
-- 区分大小写
-- 使用关键字BINARY,例如
WHERE prod_name REGEXP BINARY 'JetPack .000';
### 进行OR匹配
-- |为正则表达式的OR操作符，表示匹配其中之一
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;
-- 可以给出两个以上的OR条件1000|2000|3000
### 匹配几个字符之一
`[]`可以认为是另一种的`or`语句
-- []表示匹配[]中的任意一个字符，例如[123]是[1|2|3]的缩写
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;
| -- output |
|---|
| prod_name |
| 1 ton anvil |
| 2 ton anvil |
-- 和直接使用OR的区别：
SELECT prod_name FROM products WHERE prod_name REGEXP '1|2|3 Ton'
ORDER BY prod_name
-- 匹配的是1 OR 2 OR 3 Ton，应该使用'[1|2|3] Ton'
| -- output |
|---|
| prod_name |
| 1 ton anvil |
| 2 ton anvil |
| JetPack 1000 |
| JetPack 2000 |
| TNT (1 stick) |
字符集合也可以被否定，为否定一个字集，在集合的开始处放置`^`，例如`[^123]`匹配除这些字符的任何东西
### 匹配范围
-- [0123456789]可以写成[0-9]，其他范围如[a-z]
SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name
| -- output |
|---|
| prod_name |
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
### 匹配特殊字符
-- 匹配'.'字符，如果使用
SELECT vend_name FROM vendors WHERE vend_name REGEXP '.'
ORDER BY vend_name;
| -- output |
|---|
| vend_name |
| ACME |
| Anvils R Us |
| Furball Inc. |
| Jet Set |
| Jouets Et Ours |
| LT Supplies |
-- 因为'.'为匹配任意字符，因此匹配特殊字符，必须用'\'为前导
SELECT vend_name FROM vendors WHERE vend_name REGEXP '\.'
ORDER BY vend_name;
| -- output |
|---|
| vend_name |
| Furball Inc. |
正则表达式中具有特殊意义的所有字符都要通过这种方式转义`escaping``\\`也用来引用元字符
| 元字符 | 说明 |
| --- | --- |
| `\\f` | 换页 |
| `\\n` | 换行 |
| `\\r` | 回车 |
| `\\t` | 制表 |
| `\\v` | 纵向制表 |
| `\\.` | 表示查找`.` |
为了匹配`\`本身，需要使用`\\\`
### 匹配字符类
| 类 | 说明 |
| --- | --- |
| \[:alnum:\] | 任意字母和数字（同\[a-zA-Z0-9\]） |
| \[:alpha:\] | 任意字符（同\[a-zA-Z\]） |
| \[:cntrl:\] | ASCII控制字符（ASCII）0到31和127 |
| \[:digit:\] | 任意数字（同\[0-9\]） |
| \[:graph:\] | 同\[:print:\] 不包括空格 |
| \[:lower:\] | 任意小写字母（同\[a-z\]） |
| \[:print:\] | 任意可打印字符 |
| \[:punct:\] | 既不在\[:alnum:\]又不在\[:cntrl:\]中的任意字符 |
| \[:space:\] | 包括空格在内的任意空白字符（同\[\\\\f\\\\n\\\\r\\\\t\\\\v\]） |
| \[:upper:\] | 任意大写字母（同\[A-Z\]） |
| \[:xdigit:\] | 任意十六进制数字（同\[a-fA-F0-9\]） |
下面三个等价
* `[:digit:]{4}`
* `[0-9]{4}`
* `[0-9][0-9][0-9][0-9]`
### 匹配多个实例
| 元字符 | 说明 |
| --- | --- |
| \* | 0个或多个匹配 |
| + | 1个或多个匹配（等于{1，}） |
| ？ | 0个或1个匹配（等于{0，1}） |
| {n} | 指定数目的匹配 |
| {n,} | 不少于指定数目的匹配 |
| {n.m} | 匹配数目的范围（m不超过255） |
例：
SELECT prod_name FROM products WHERE prod_name REGEXP '\([0-9] sticks?\)'
ORDER BY prod_name
| -- output |
|---|
| prod_name |
| TNT (1 stick) |
| TNT (5 sticks) |
-- '\('匹配'('
'[0-9]'匹配任意数字
'stick?'匹配'stick'和'sticks'
'\)'匹配')'
例：匹配连在一起的4位数字
SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name;
| -- output |
|---|
| prod_name |
| JetPack 1000 |
| JetPack 2000 |
-- 也可以写成 '0-90-9'
### 定位符
| 元字符 | 说明 |
| --- | --- |
| ^ | 文本的开始 |
| $ | 文本的结尾 |
| \[:<:\] | 词的开始 |
| \[:>:\] | 词的结尾 |
例：找出以一个数（包括小数点开头）开始的所有产品
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\.]'
ORDER BY prod_name;
| -- output |
|---|
| prod_name |
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
5-创建计算字段
--------
### 计算字段
应用程序需要的数据需要通过从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据，然后再在客户机应用程序或报告程序中重新格式化。
字段：基本上与列的意思相同，经常互换使用，不过数据库一般称为列，而属于字段通常用在计算字段的连接上。
### 拼接字段
拼接：将值联结到一起构成单个值
在SELECT语句中，可使用Concat()函数来拼接两个列。 `Concat()`函数需要一个或多个指定的串，各个串之间用逗号分隔。
SELECT Concat(vend_name, ' (',vend_country,')') FROM vendors
ORDER BY vend_name;
output
| Concat(vendname,' (',vend_country,')') |
|---|
| ACME (USA) |
| Anvils R Us (USA) |
| Furball Inc. (USA) |
| Jet Set (England) |
| Jouets Et Ours (France) |
| LT Supplies (USA) |
使用 RTrim()函数可以删除右侧多余的空格来整理数据，例：
SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country), ')')
FROM vendors
ORDER BY vend_name;
| 函数 | 说明 |
| --- | --- |
| Trim() | 去掉两边的空格 |
| LTrim() | 去掉左边的空格 |
| RTrim() | 去掉右边的空格 |
### 使用别名
拼接的结果只是一个值，未命名。可以用`AS`关键字赋予别名
常见的用途包括在实际的表列名包含不符合规定的字符(如空格)时重新命名它，在原来的名字含混或容易误解时扩充它等等。别名有时也称为导出列（derived column）
SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;
output
| vend_name |
|---|
| ACME (USA) |
| Anvils R Us (USA) |
| Furball Inc. (USA) |
| Jet Set (England) |
| Jouets Et Ours (France) |
| LT Supplies (USA) |
指示SQL创建一个包含指定计算的名为vend_title的计算字段
### 执行算术计算
例：汇总物品的价格（单价乘以订购数量）
SELECT prod_id,
quantity,
item_price,
quantity * item_price AS expanded_priceFROM orderitems
WHERE order_num = 20005;
output
| prod_id | quantity | item_price | expanded_price |
|---|---|---|---|
| ANV01 | 10 | 5.99 | 59.90 |
| ANV02 | 3 | 9.99 | 29.97 |
| TNT2 | 5 | 10.00 | 50.00 |
| FB | 1 | 10.00 | 10.00 |
| 操作符 | 说明 |
| --- | --- |
| + | 加 |
| \- | 减 |
| \* | 乘 |
| / | 除 |
`SELECT Now()`利用 Now()函数返回当前日期和时间
6-使用数据处理函数
----------
函数没有SQL的可移植性强
大多数SQL实现支持以下类型的函数
* 用于处理文本串的**文本函数**
* 在数值数据上进行算术操作的**数值函数**
* 处理日期和时间值并从这些值中提取特定成分的日期和**时间函数**
* 返回DBMS正是用的特殊信息的系统函数
### 文本处理函数
常用的文本处理函数
| 函数 | 说明 |
| --- | --- |
| Left() | 返回串左边的字符 |
| Length() | 返回串的长度 |
| Locate() | 找出串的一个子串 |
| Lower() | 将串转换为小写 |
| LTrim() | 去掉串左边的空格 |
| Right() | 返回串右边的字符 |
| RTrim() | 去掉串右边的空格 |
| Soundex() | 返回串的SOUNDEX值 |
| SubString() | 返回子串的字符 |
| Upper() | 将串转换为大写 |
SOUNDEX是一个将任何文本转换为描述其语音表示的字母数字模式的算法，使得能对串进行发音比较而不是字母比较。MySQL提供对SOUNDEX的支持。
例：联系人Y.Lie输入错误为Y.Lee，使用SOUNDEX检索，匹配发音类似于Y.Lie的联系名
SELECT cust_name, cust_contact FROM customers
WHERE Soundex(cust_contact）= Soundex('Y Lie');
output
| cust_name | cust_contact |
|---|---|
| Coyote Inc. | Y Lee |
### 日期和时间处理函数
| 函数 | 说明 |
| --- | --- |
| AddDate() | 增加一个日期（天、周等） |
| AddTime() | 增加一个时间（时、分等） |
| CurDate() | 返回当前日期 |
| CurTime() | 返回当前时间 |
| Date() | 返回日期时间的日期部分 |
| DateDiff() | 计算两个日期之差 |
| Date\_Add() | 高度灵活的日期计算函数 |
| Date\_Format() | 返回一个格式化的日期或时间串 |
| Day() | 返回一个日期的天数部分 |
| DayOfWeek() | 对于一个日期，返回对应的星期几 |
| Hour() | 返回一个时间的小时部分 |
| Minute() | 返回一个时间的分钟部分 |
| Month() | 返回一个日期的月份部分 |
| Now() | 返回当前日期和时间 |
| Second() | 返回一个时间的秒部分 |
| Time() | 返回一个日期时间的时间部分 |
| Year() | 返回一个日期的年份部分 |
### MySQL使用的日期格式
日期必须为格式yyyy-mm-dd 支持2位数字的年份，MySQL处理00-69为2000-2069，70-99为1970-1999，但使用4为数字年份更可靠。例：
SELECT cust_id, order_num FROM orders
WHERE order_date = '2005-09-01';
`order_date`类型为`datetime`，样例表中的值全部具有时间值00：00：00，但是如果order\_date的值为2005-09-01 11：30：05则上面的WHERE order\_date = '2005-09-11'不会检索出这一行，因此必须使用Date()函数。
SELECT cust_id, order_num FROM orders
WHERE Date(order_date) = '2005-09-01';
例：检索出2005年9月下的所有订单
SELECT cust_id, order_num FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
BETWEEN把2005-09-01和2005-09-30定义为一个要匹配的日期范围。
另一种方法
SELECT cust_id, order_num FROM orders
WHERE Year(roder_date) = 2005 AND Month(order_date) = 9;
### 数值处理函数
| 函数 | 说明 |
| --- | --- |
| Abs() | 返回一个数的绝对值 |
| Cos() | 返回一个角度的余弦 |
| Exp() | 返回一个数的指数值 |
| Mod() | 返回除操作的余数 |
| Pi() | 返回圆周率 |
| Rand() | 返回一个随机数 |
| Sin() | 返回一个角度的正弦 |
| Sqrt() | 返回一个数的平方根 |
| Tan() | 返回一个角度的正切 |
7-聚合数据
------
| 函数 | 说明 |
| --- | --- |
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行数 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值之和 |
#### AVG()函数
例：返回products表中所有产品的平均价格
SELECT AVG(prod_price) AS avg_price FROM products;
例：返回特定供应商所提供产品的平均价格
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
#### COUNT()函数
例：返回customer表中客户的总数
SELECT COUNT(*) AS num_cust FROM customers;
例：只对具有电子邮件地址的客户计数
SELECT COUNT(cust_email) AS num_cust
FROM customers;
#### MAX()函数
例：返回products表中最贵的物品价格
SELECT MAX(prod_price) AS max_price
FROM products;
对非数值数据使用MAX() MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。MAX()函数忽略列值为NULL的行。
#### MIN()函数
例：
SELECT MIN(prod_price) AS min_price FROM products;
#### SUM()函数
返回指定列值的和（总计） 例：检索所订购物品的总数
SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005;
例：合计计算值，合计每项物品item\_price\*quantity，得出订单总金额
SELECT SUM(item_price*quantity) AS total_price
FORM orderitems
WHERE order_num = 20005;
#### 聚集不同值（适用于5.0.3后的版本）
上述五个聚集函数都可以如下使用：
* 对所有的行执行计算，指定ALL参数或不给参数（ALL为默认）
* 只包含不同的值，指定DISTINCT参数
例：
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
注意：如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(\*)，因此不允许使用COUNT（DISTINCT）， 否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。
#### 组合聚集函数
SELECT语句可根据需要包含多个聚集函数
SELECT COUNT(*) AS num_items;
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avgFROM products;
output
| num_items | price_min | price_max | price_avg |
|---|---|---|---|
| 14 | 2.50 | 55.50 | 16.133571 |
8-分组数据
------
### 创建分组
例：根据vend\_id分组，对每个分组分别计算总数
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;
output
| vend_id | num_prods |
|---|---|
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
* GROUP BY 子句可以包含任意数目的列，使得能对分组进行嵌套，为数据分组提供更细致的控制
* 如果GROUP BY子句中中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
* GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。**如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。**
* 除**聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。**
* 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
* GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。【详细信息，见下表】
使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值，如下所示：
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP;
### 过滤分组
WHERE指定的是行，不是分组，WHERE没有分组的概念
使用HAVING过滤分组
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
output
| cust_id | orders |
|---|---|
| 10001 | 2 |
WHERE不起作用，因为过滤是基于分组聚集值而不是特定行值的。
例：列出具有2个（含）以上、价格为10（含）以上的产品的供应商
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >=2
output
| vend_id | num_prods |
|---|---|
| 1003 | 4 |
| 1005 | 2 |
### 分组和排序
例：检索总计订单价格大于等于50的订单的订单号和总计订单价格
SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertital;
### SELECT子句顺序
SELECT子句及其顺序
| 子句 | 说明 | 是否必须使用 |
| --- | --- | --- |
| SELECT | 要返回的列或表达式 | 是 |
| WHERE | 从中检索数据的表 | 仅在从表选择数据时使用 |
| GROUP BY | 分组说明 | 尽在按组计算聚集是使用 |
| HAVING | 组级过滤 | 否 |
| ORDER BY | 输出排序顺序 | 否 |
| LIMIT | 要检索的行数 | 否 |
上述子句使用时必须遵循该**顺序**
> 这个是书写的时候需要注意的，但是实际数据库在运行的时候是这样的吗？
>
> * 先执行`from`关键字后面的语句，明确数据的来源，它是从哪张表取来的。
>
> * 接着执行`where`关键字后面的语句，对数据进行筛选。
>
> * 再接着执行`group by`后面的语句，对数据进行分组分类。
>
> * 然后执行`select`后面的语句，也就是对处理好的数据，具体要取哪一部分。
>
> * 最后执行`order by`后面的语句，对最终的结果进行排序。
>
9-使用子查询
-------
**要求4.1以上版本**
例：列出订购物品TNT2的所有客户
1. 检索包含物品TNT2的所有订单的编号
2. 检索具有前一步骤列出的订单编号的所有客户的ID
3. 检索前一步骤返回的所有客户ID的客户信息
(1)
SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
output
| order_num |
|---|
| 20005 |
| 20007 |
(2)
| SELECT cust_id FROM orders WHERE order_num IN (20005,20007); |
|---|
| cust_id |
| 10001 |
| 10004 |
(1)+(2)
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');
| order_num |
|---|
| 20005 |
| 20007 |
(3)
SELECT clust_name. cust_contact FROM customers WHERE cust_id IN (10001, 10004)
(1)+(2)+(3)
SELECT cust_name, cust_contact FROM customers
WHERE cust_id IN(SELECT cust_id FROM orders
WHERE order_name IN(SELECT order_num FROM orderitems
WHERE prod_id ='TNT2'));output
| cust_name | cust_contact |
|---|---|
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
在WHERE子句中使用子查询应保证SELECT语句有与WHERE子句中相同数目的列。
### 作为计算字段使用子查询
需要显示customers表中每个客户的订单总数，订单与相应的客户ID存储在orders表中
1. 从customers表中检索客户列表
2. 对于检索出的每个客户，统计其在orders表中的订单数目
对客户10001的订单进行计数
SELECT COUNT (*) AS orders FROM orders WHERE cust_id = 10001;
为了对每个客户执行COUNT()计算，应该将COUNT()作为一个子查询
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders
WHERE orders.cust_id = customers.cust_id) AS ordersFROM customers ORDER BY cust_name;
**相关子查询**：涉及外部查询的子查询 在任何时候只要列明可能有多义性，就必须使用这种语法（表明和列名由一个句点分隔）
11-组合查询
-------
### 组合查询
MySQL允许执行多个查询并将结果作为单个查询结果返回。两种情况：
* 在单个查询中从不同的表返回类似结构的数据
* 对单个表执行多个查询，按单个查询返回数据
### 创建组合查询
#### 使用UNION
给出每条SELECT语句，在各条语句之间放上关键字UNION 例：需要价格小于等于5的所有物品的一个列表，并且包含供应商1001和1002生产的所有物品
单条语句
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5;
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);
组合上述语句
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);
等于
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
OR vend_id IN (1001, 1002);
#### UNION规则
* UNION 必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔
* UNION中的每个查询必须包含先沟通的列、表达式或聚集函数
* 列数据类型必须兼容：类型不必完全向东，但必须是DBMS可以隐含地转换的类型
#### 包含或取消重复的行
* `UNION`从查询结果集中自动去除了重复的行，
* 如果需要返回所有行，可以使用`UNION ALL`
#### 对组合查询结果排序
使用ORDER BY子句排序，只能使用一条ORDER BY子句，必须在最后一条SELECT语句之后。
* 自定义排序
> 使用order by排序，有时候不是根据字符或数字顺序，而是根据实际要求排序。
>
> 例如有客户A,B,C，我希望排序结果是B,C,A，那么就要通过自定义的规则排序。
>
> 第一种方法，可以构造一张映射表，将客户映射到所需要的顺序。
>
> 第二种方法，如果要排序的客户不多，可以直接写出，那就使用如下方法：
>
> ```
> order by
> case customer when B then 1
> when C then 2
> when A then 3
> end;
>
> ```
12-全文本搜索
--------
**并非所有引擎都支持全文本搜索**
> `mysql`5.6.4之前只有 `Myisam`支持，5.6.4之后则`Myisam`和`innodb`都支持，不过`mysql`中的全文索引目前只支持英文(不支持中文)
>
> 其实根本原因是因为英文检索是用空格来对分词进行分隔，而中文肯定不能用空格来分隔，只能通过语义进行分词，用空格的话是肯定检索不出某些词汇的。
>
> 良心的是，在Mysql5.7版本时，`MySQL`内置了`ngram`全文检索插件，用来支持中文分词，但是仅对`MyISAM`和`InnoDB`引擎有效。
`MyISAM`支持，`InnoDB`不支持
LIKE、正则表达式的限制
* 性能：由于被搜索行不断增加，这些搜索可能非常耗时
* 明确控制：很难明确控制匹配什么和不匹配什么
* 智能化的结果：不能提供一种智能化的选择结果的方法，例如：一个特殊词的搜索将会返回包含该词的所有行而不区分包含单个匹配的行和多个匹配的行。
### 使用全文本搜索
为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在对表列进行适当设计后，MySQL会自动进行所有的索引和重新索引。在索引之后，`SELECT`可与`Match()`和`Against()`一起使用以实际执行搜索。
### 启用全文本搜索支持
一般在创建表时启用全文本搜索 CREATE TABLE语句接收`FULLTEXT`子句，它给出被索引列的一个逗号分隔的列表 例：
CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
pord_id char(10) NOT NULL,
note_date datetime NOT NULL.
note_text text NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text))ENGINE=MyISAM;
可以在创建表时指定`FULLTEXT`或在稍后指定（所有数据必须立即索引）**不要在导入数据时使用FULLTEXT** 应线导入所有数据再修改表，定义FULLTEXT
### 进行全文本搜索
Match() 指定被搜索的列 Against() 指定要使用的搜索表达式
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');
**传递个`Match()`的值必须与`FULLTEXT()`定义中的相同。**
除非使用`BINARY`方式，否则全文本搜索不区分大小写
全文本搜索对结果排序，具有较高等级的行先返回
SELECT note_text,
Match(note_text) Against('rabbit') AS rank
FROM productnotes;
显示所有行及他们的等级
### 使用扩展查询
（MySQL 4.1.1及更高版本） 例如找出所有提到anvils的注释，和与搜索有关的其他行，即使它们不包含这个词
不使用扩展查询
SELECT note_text
FROM productnotes
| WHERE Match(note_text) Against('anvils'); |
|---|
| note_text |
| Multiple custoer returns, anvils failing to drop fast enough or falling |
| backwords on purchaser, Recomend that customer considers using havier |
| anvils. |
使用扩展查询
SELECT note_text
FROM productnotes
| WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION); |
|---|
| note_text |
| Multiple custoer returns, anvils failing to drop fast enough or falling |
| backwords on purchaser, Recomend that customer considers using havier |
| anvils. |
| Customer complaint: Sticks not individually wrapped, too easy to mistakenly |
| detonate all at once. Recommend individual wrapping. |
| Customer compliant: Not heavy enouth to generate flying stars around heardof |
| victim. If veing purchased for dropping, recommend ANV02 or ANV03 instead. |
| Please note that no returns will be accepted if safe opened using explosives. |
| Customer complaint: rabbit has been able to detect trap, food apparently |
| less effective now. |
| Customer complaint: Circular hole in safe floor can apparently be easily cut |
| with handsaw. |
| Matches not include, recomend purchase of matches or detonator (item DTNTR) |
返回7行，第一行包含anvils，因此等级最高，第二行与anvils无关，但包含第一行中的两个次，因此被检索出来。
### 布尔文本搜索
可以提供如下内容的细节：
* 要匹配的词
* 要排斥的次
* 排列提示
* 表达式分组
* 另外一些内容
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);
例：匹配包含heavy但不包含任意以rope开始的词的行
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
**MySQL4.x版本中使用-ropes而不是-rope**
全文本布尔操作符
| 布尔操作符 | 说明 |
| --- | --- |
| + | 包含，词必须存在 |
| \- | 排除，词必须不出现 |
| \> | 包含，而且增加等级值 |
| < | 包含，且减少等级 |
| () | 把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等） |
| ~ | 取消一个词的排序值 |
| \* | 取消一个词的排序值 |
| "" | 定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语） |
例：
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);
匹配包含词rabbit和bait的行。
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);
匹配包含rabbit和bait中的至少一个词的行
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);
匹配rabbit bait而不是匹配两个词
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('>rabbit <bait' IN BOOLEAN MODE);
匹配rabbit和carrot，增加前者的等级，降低后者的等级
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);
匹配词safe和combination，降低后者的等级
### 全文本搜索的使用说明
* 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为 那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）。
* MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表（请参阅MySQL文档以了解如何完成此工作）。
* 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上 的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。
* 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词 或者不出现，或者至少出现在50%的行中）。
* 忽略词中的单引号。例如，don't索引为dont。
* 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文 本搜索结果。
* 如前所述，仅在MyISAM数据库引擎中支持全文本搜索。
13-插入、删除、修改数据
-------------
**INSERT**
* 插入完整的行
* 插入行的一部分
* 插入多行
* 插入某些查询的结果
### 插入完整的行
INSERT INTO Customers
VALUES(NULL,
'Pep E. LaPew',
'100 Main Street',
'Los Angles',
'CA',
'90046',
'USA',
NULL,
NULL);
语法简单但不安全。更安全的方法为：
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)VALUES('Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046'
'USA'
NULL,
NULL);下面的INSERT语句填充所有列（与前面的一样），但以一种不同的次序填充。
因为给出了列名，所以插入结果仍然正确：
INSERT INTO customers(cust_name,
cust_contact,
cust_email,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)VALUES('Pep E. LaPew',
NULL,
NULL,
'100 Main Street',
'Los Angles',
'CA',
'90046',
'USA');
不管哪种INSSERT语法，都必须给出**VALUES的正确数目**，如果不提供列名，则必须给每个表提供一个值。
如果提供列名，则必须对每个列出的列值给出一个值。
列名被明确列出时，可以省略列,如果表的定义允许则可以省略列
* 该列定义为允许NULL值（无值或空值）
* 在表定义中给出默认值。
### 插入多个行
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)VALUES('Pep E. LaPew',
'100 Main Street'
'Los Angeles',
'CA',
'90046',
'USA');INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)VALUES('M. Martian',
'42 Galaxy Way'
'New York',
'NY',
'11213',
'USA');
使用组合句
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)VALUES('Pep E. LaPew',
'100 Main Street'
'Los Angeles',
'CA',
'90046',
'USA')，
('M. Martian',
'42 Galaxy Way'
'New York',
'NY',
'11213',
'USA');
单条INSERT语句有多组值，每组值用一对圆括号括起来，用逗号分隔。
### 插入检索出的数据
INSERT INTO customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_countryFROM custnew;
### 更新数据
UPDATE
* 更新表中特定行
* 更新表中所有行 例：客户10005更新电子邮件
UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
例：更新多个列
UPDARTE customers
SET cust_name = 'The Fudds',
cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
在更新多个列时，只需要使用单个SET命令，每个“列=值”对之间 用逗号分隔（最后一列之后不用逗号）。在此例子中，更新客户10005的cust\_name和cust\_email列。
IGNORE关键字:如果用UPDATE语句更新多行，并且在更新这些 行中的一行或多行时出一个现错误，则整个UPDATE操作被取消 （错误发生前更新的所有行被恢复到它们原来的值）。为即使是发生错误，也继续进行更新，可使用IGNORE关键字，如下所示：`UPDATE IGNORE customers…`
为了删除某列的值，可以设置为NULL
UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;
### 删除数据
使用DELETE语句
* 从表中删除特定的行
* 从表中删除所有的行
DELETE FROM customers
WHERE cust_id = 10006;
### 更新和删除的指导原则
下面是许多SQL程序员使用UPDATE或DELETE时所遵循的习惯。
* 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
* 保证每个表都有主键，尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。
* 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
* 使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。
14-创建和操纵表
---------
### 创建表`create`
`CREATE TABLE`
* 新表的名字，在关键字CREATE 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;
在创建新表时，指定的表名**必须不存在**，否则将出错。如果要防止意外覆盖已有的表，SQL要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它。
如果你仅想在一个表不存在时创建它，应该在表名后给出`IF NOT EXISTS`。这样做不检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在，并且仅在表名不存在时创建它。
CREATE TABLE IF NOT EXISTS 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;
#### `NULL`值
每个表列或者是NULL列或者是NOT NULL列，这种状态在创建时由表的定义规定 例：
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;
例：混合了NULL和NOT NULL列的表
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;
#### 主键`PRIMARY KEY`
主键值必须唯一。**可以由一个或者多个**。
* 如果主键使用单个列，则它的值必须唯一。
* 如果使用多个列，则这些列的组合值必须唯一。
* `PRIMARY KEY (order_num, order_item)`
例：创建多个列组成的主键
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;
#### 使用`AUTO_INCREMENT`
`AUTO_INCREMENT`告诉`MySQL`，本列每当增加一行时自动增量。每次 执行一个`INSERT`操作时，`MySQL`自动对该列增量（从而才有这个关键字`AUTO_INCREMENT`），给该列赋予下一个可用的值。这样给每个行分配一个唯一的`cust_id`，从而可以用作主键值。
覆盖`AUTO_INCREMENT`:如果一个列被指定为`AUTO_INCREMENT`，则它需要使用特殊的值吗？你可以简单地INSERT语句中指定一个值，只要它是唯一的（至今尚未使用过）即可，该值将被用来替代自动生成的值。
后续的增量将开始使用该**手工插入**的值。
#### 指定默认值`DEFAULT`
CREATE TABLE orderitems
(
order_num int NOT NUL,
order_item int NOT NULL,
prod_id char(10) NOT NULL,
quantity int NOT NULL DEFAULT 1,
item_price decimal(8,2) NOT NULL,
PRIMARY KEY (order_num,order_item)) ENGINE = InnoDB;
`MySQL`不允许使用函数作为默认值，只支持常量
#### 引擎类型
* `InnoDB`是一个可靠的事务处理引擎，它不支持全文本搜索；【后面版本已经支持了~】
> `mysql`5.6.4之前只有 `Myisam`支持，5.6.4之后则`Myisam`和`innodb`都支持，不过`mysql`中的全文索引目前只支持英文(不支持中文)
>
> 其实根本原因是因为英文检索是用空格来对分词进行分隔，而中文肯定不能用空格来分隔，只能通过语义进行分词，用空格的话是肯定检索不出某些词汇的。
>
> 良心的是，在Mysql5.7版本时，`MySQL`内置了`ngram`全文检索插件，用来支持中文分词，但是仅对`MyISAM`和`InnoDB`引擎有效。
* `MEMORY`在功能等同于`MyISAM`，但由于数据存储在内存（不是磁盘） 中，速度很快（特别适合于临时表）；
* `MyISAM`是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。
* 引擎类型可以混用。
> 外键不能跨引擎 混用引擎类型有一个大缺陷。
>
> 外键（用于强制实施引用完整性）不能跨引擎，
>
> **即使用一个引擎的表不能引用具有使用不同引擎的表的外键。**
### 更新表`alter`
使用`ALTER TABLE`更改表的结构，必须给出以下信息：
* 在`ALTER TABLE`之后给出要更改的表名（该表必须存在，否则将出错）；
* 所做更改的列表。
例：增加列
ALTER TABLE vendors
ADD vend_phone CHAR(20);
例：删除刚增加的列
ALTER TABLE vendors
DROP COLUMN vend_phone;
为了对单个表进行多个更改，可以使用单条`ALTER TABLE`语句，每个更改用逗号分隔
复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：
* 用新的列布局创建一个新表；
* 使用`INSERT SELECT`语句从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；
* 检验包含所需数据的新表；
* 重命名旧表（如果确定，可以删除它）；
* 用旧表原来的名字重命名新表；
* 根据需要，重新创建触发器、存储过程、索引和外键。
使用`ALTER TABLE`要极为小心，应该在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。
### 删除表`drop`
DROP TABLE customers2;
### 重命名表`rename`
RENAME TABLE customers2 TO customers;
对多个表重命名
RENAME TABLE backup_customers TO customers,
backup_vendors TO vendors,
backup_products TO products;
注释
--
MySQL的注释方法 一共有三种，分别为
单行注释可以使用"#"
-- 单行注释也可以使用"--"，注意与注释之间有空格
/*
用于多行注释
*/
