前言

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。

MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。
由于其社区版的性能卓越，搭配 PHP 和 Apache 可组成良好的开发环境。

一、检索数据

1. 检索所有列

select * from products;

2. 检索单个列

select prod_id from products;

3. 检索多个列

select prod_id, prod_name from products;

4. 检索不同行

select vend_id from products;

5. 限制结果

Eg：检索的结果不超过5行。

select prod_name from products limit 5;

Eg：从第6行开始检索prod_name，检索5行，也就是检索6~11行的数据。（注意：limit 0是第一行）

select prod_name from products limit 5,5;

6. 使用完全限定的表名

select products.prod_name from crashcourse.products;

等同于：

select prod_name from products;

二、排序检索数据

排序数据

select prod_name from products;

上面这条mysql语句是没有特定的顺序排列的。

Eg：使用order by（默认从小到大）按照prod_name顺序排列。

select prod_name from products order by prod_name;

按多个列排序

Eg：使用oder by按照prod_price, prod_name多个列排序。

select prod_id, prod_price, prod_name from products order by prod_price, prod_name;

按指定排序方向

Eg：使用oder by按照prod_price, prod_name多个列降序排序。

select prod_id, prod_price, prod_name from products order by prod_price desc, prod_name desc;

三、过滤数据

使用where子句

Eg:查找prod_price = 2.50的匹配结果。

select prod_name, prod_price from products where prod_price = 2.50;

where子句操作符

1.检查单个值

select prod_name, prod_price from products where prod_name = 'fuses';

select prod_name, prod_price from products where prod_price > 5;

select prod_name, prod_price from products where prod_price >= 5;

2. 不匹配检查

Eg：查找不是由供应商1003制造的所有产品。

法一（<>）：

select vend_id, vend_name from products where vend_id <> 1003;

法二（!=）：

select vend_id, vend_name from products where vend_id != 1003;

3. 范围值检查

Eg:检索价格在5~10之间的所有产品。

select prod_name, prod_price from products where prod_price between 5 and 10;

4. 空值检查

Eg：检索顾客的邮件(cust_email)为空的匹配项。

select cust_id from customers where cust_email is null;

三、数据过滤

组合where子句

1. and操作符

Eg：找出vend_id=1003 and prod_price <= 10所有产品的prod_id,prod_price,prod_name。

select prod_id, prod_price, prod_name from products where vend_id = 1003 and prod_price <= 10;

2. or操作符

Eg：找出vend_id=1002 or vend_id=1003所有产品的prod_name,prod_price。

select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003;

3. 计算次序

or和and一起的时候，优先处理and操作符

select prod_name, prod_price from products where vend_id = 1002 or vend = 1003 and prod_price >= 10;

上面这条mysql语句，优先执行and操作符。

正确的做法(mysql语句)：

select prod_id,prod_price from products where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;

in操作符

Eg：找出vend_id在1001~1003范围内的所有prod_name,prod_price匹配的结果。

select prod_name, prod_price from products where vend_id in (1001,1003) order by prod_name;

not操作符

Eg：找出vend_id不在1001~1003范围内的所有prod_name,prod_price匹配的结果。

select prod_name, prod_price from products where vend_id not in (1001,1003) order by prod_name;

四、用通配符进行数据过滤

like操作符

1. 百分号(%)通配符

Eg：找出所有jet开头的产品。

select prod_id, prod _name from products where prod_name like 'jet%';

Eg：找出包含jet的产品。

select prod_id, prod _name from products where prod_name like '%jet%';

2. 下划线(_)通配符

Eg:找出后面跟有两个通配符ton anvil。

select prod_id, prod_name from products where prod_name like '_ton anvil';

结果：

prod_id        prod_name
ANV02        1 ton anvil
ANV03        2 ton anvil

对比，来看看使用%ton anvil匹配的结果：

select prod_id, prod_name from products where prod_name like '%ton_anvil';

结果：

prod_id        prod_name
ANV01        .5 ton anvil
ANV02        1 ton anvil
ANV03        2 ton anvil

思考

为什么使用_ton anvil和%ton_anvil返回的结果不一样？少了ANV01的结果？

解答：因为下划线(_)只可以匹配一个字符，不能多，也不能少！而百分号(%)就不一样了，它没有限制！

五、使用MySQL正则表达式

正则表达式查询REGEXP

1. 基本字符匹配

Eg：检索匹配.000的字符。

select prod_name from products where prod_name regexp '.000' order by prod_name;

结果：

prod_name
JetPack 1000
JetPack 2000

2. 进行or匹配

Eg：检索prod_name为1000或2000的匹配结果，并按prod_name默认升序。

select prod_name from products regexp '1000|2000' order by prod_name;

结果：

prod_name
JetPack 1000
JetPack 2000

3. 匹配几个字符之一

Eg：检索prod_name的匹配字符为1、2的符合结果。

select prod_name from products where prod_name regexp '[12] Ton' order by prod_name;

结果：

prod_name
1 ton anvil
2 ton anvil

4. 匹配范围

Eg：检索prod_name的匹配字符为1-5的结果。

select prod_name from products where regexp '[1-5] Ton' order by prod_name;

结果：

prod_name
.5 ton anvil
1 ton anvil
2 ton anvil

注意：因为.5也是符合1-5的，所以会被检索。

5. 匹配特殊字符

Eg：返回vend_name含有.的匹配结果。

select vend_name from vendors where vend_name regexp '\\.' order by vend_name;

结果：

vend_name
Furball Inc

6. 匹配字符类

7. 匹配多个实例

Eg1：

\\([0-9]匹配prod_name的0-9的任意数字；
sticks?\\使s可选，没有？，匹配stick和sticks非常困难。

mysql语句：

select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)' order by prod_name;

结果：

prod_name
TNT (1 stick)
TNT (5 stick)

Eg2：匹配林仔一起的四位数字。

select prod_name from products where prod_name regexp '[[:digit:]]{4}' order by prod_name;

结果：

prod_name
JetPack 1000
JetPack 2000

说明：

[:digit:]匹配任意数字，因而它为数字的一个集合。

{4}确切地要求它前面的字符（任意数字）出现四次。

所以，[[:digit:]]{4}匹配连在一起的任意四位数字。

Eg2的等同写法：

select prod_name from products where prod_name regexp '[0-9][0-9][0-9][0-9]' order by prod_name;

8. 定位符

定位元字符

Eg：找出以一个数（包括小数点开始的数）开始的所有产品。

select prod_name from products where prod_name regexp '^[0-9\\.]' order by prod_name;

结果：

prod_name
.5 ton anvil
1 ton anvil
2 ton anvil

六、创建计算字段

拼接字段

普通拼接

Eg1：

select concat(vend_name, '(', vend_country, ')') from vendors order by vend_name;

结果：

concat(vend_name, '(', vend_country, ')')
ACME (USA)
Anvils R Us (USA)
Furball Inc. (USA)
Jet Set (England)
Jouets Et Ours (France)
LT Supplies (USA)

rtrim()函数

Eg2：rtrim()函数去掉右边的所有空格。

select concat(rtrim(vend_name, '(', vend_country, ')') from vendors order by vend_name;

使用别名

别名（alias），使用as关键字赋予。

Eg3：

select concat(rtrim(vend_name), '(', rtrim(vend_country), ')') as vend_title from vendors order by vend_name;

结果：

vend_title
ACME (USA)
Anvils R Us (USA)
Furball Inc. (USA)
Jet Set (England)
Jouets Et Ours (France)
LT Supplies (USA)

执行算术计算

select prod_id, quantity, item_price from orderitems where order_num = 2005;

结果：

prod_id        quantity    item_price
ANV01            10            5.99
ANV02             3            9.99
TNT2            10            10.00
FB                 1            10.00

Eg：汇总物品价格（单价乘以订购数量）。

select prod_id,
       quantity,
       item_price,
       quantity*item_price as expanded_price
from orderitems
where order_num = 2005;

结果：

prod_id     quantity  item_price  extended_price
ANV01        10          5.99            59.90
ANV02         3          9.99            29.97
TNT2        10          10.00            50.00
FB             1          10.00            10.00

七、使用数据处理函数

1. 文本处理函数

Eg：

select vend_name, upper(vend_name) as vend_upcase from vendors order by vend_name;

结果：

vend_name        vend_name_upcase
ACME                ACME
Anvils R Us            ANVILS R US
Furball Inc.        FURBALL INC
Jet Set                JET SET
Jouets Et Ours        JOUETS ET OURS
LT Supplies            LT SUPPLIES

2. 日期和时间处理函数

Eg：

select cust_id, order_num from orders where order_date = '2005-09-01';

结果：

cust_id        order_num
1001            2005

3. 数值处理函数

八、创建和操纵表

创建表

Eg：

create table customers
(
    cust_id            int            not null    auto_increment
    cust_name        char(50)    not 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;

• ENGINE=InnoDB：表的引擎。
• auto_increment：自动增长。

更新表

• 给表增加一列：

  alter table vendors add vend_phone char(20);
  

删除表

drop table customers;

重命名表

• 重命名单个表：

  rename table customers to cust;
  

• 重命名多个表：

  rename table backup_customers to cust,
               backup_vendors to vendors,
               backup_products to products;
  

九、插入数据

1. 插入完整的行

Eg：直观的插入数据。

insert into customers
(
    cust_name,
    cust_city,
    cust_zip,
    cust_country,
    cust_contact,
    cust_email
)values(
    'Pep E. LaPew',
    '100 Main Street',
    'Los Angeles',
    'CA',
    '90046',
    'USA'
    null,
    null;
);

2. 插入多个行

Eg：

insert into customers
(
    cust_name,
    cust_city,
    cust_zip,
    cust_country,
    cust_contact,
    cust_email
)values(
    'Pep E. LaPew',
    '100 Main Street',
    'Los Angeles',
    'CA',
    '90046',
    'USA'
),
(
    'M. Martian',
    '42 Galaxy Way',
    'New York',
    'NY',
    '11213',
    'USA'
);

十、汇总数据

聚集函数

1. avg()函数：

Eg：使用avg()函数返回products表中所有产品的平均值。

select avg(prod_price) as avg_price from products;

结果：

avg_price
16.133571

2. count()函数：

Eg1：返回customers表中客户的总数。

select count(*) as num_cust from customers;

结果：

num_cust
5

Eg2：返回具有电子邮件的客户计数。

select coun(cust_email) as num_cust from customers;

结果：

num_cust
3

Eg3：

select cust_id, count(*) as orders from orders group by cust_id having count(*) >= 2;

结果：

cust_id        orders
1000            2

3. max()函数：

Eg：

select max(prod_price) as max_price from products;

结果：

max_price
55.00

4. min()函数：

g：

select min(prod_price) as min_price from products;

结果：

min_price
2.50

5. sum()函数：

Eg：计算总的订单金额。

select sum(item_price*quantity) as total_price from orderitems where order_num = 2005;

结果：

total_price
149.87

十一、分组数据

1. 创建分组

Eg：使用group by来进行分组。

select vend_id, count(*) as num_prods from products group by vend_id;

结果：

vend_id        num_prod
1001            3
1002            2
1003            7
1005            2

2. 过滤分组

使用having来过滤分组，实际上，可以用having来替代where。

唯一的区别就是，where过滤行，having过滤分组。

Eg：

select cust_id, count(*) as orders from orders group by cust_id having count(*) >= 2;

结果：

cust_id        orders
1000            2

3. 分组和排序

Eg1：检索订单价格大于等于50的订单和总计订单价格。

select order_num, sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price) >= 50;

结果：

order_num    ordertotal
2005            149.87
2006            55.00
2007            1000.00
2008            125.00

Eg2：

select order_num, sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price) >= 50 order by ordertotal;

结果：

order_num        ordertotal
2006            55.00
2008            125.00
2005            149.87
2007            1000.00

十二、使用子查询

1. 使用子查询进行过滤

select order_num from orderitems where prod_id = 'TNT2';

结果：

order_num
2005
2007

select cust_id from orders where order_num in (2005,2007)

结果：

cust_id
1001
1004

使用子查询：

Eg:

select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2');

结果：

cust_id
1001
1004

2. 作为计算字段使用子查询

Eg：

select cust_name,
       cust_state,
       (select count(*)
       from orders
       where orders.cust_id = customers.cust_id) as orders from customers order by cust_name;

结果：

cust_name    cust_state    orders
Coyote Inc        MI          2
E Fudd            IL          1
Mouse House        OH          0
Wascals            IN          1
Yosemite Place    AZ          1

十三、联结表

如果数据储存在多个表中，如何用单个select检索出来？

创建联结

Eg:

select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id order by vend_name, prod_name;

内部联结

select vend_name, prod_name, prod_price from vendors inner join products on vendors.vend_id = products.vend_id;

联结多个表

Eg:

select prod_name, vend_name, prod_price, quantity from orderitems, products, vendors where products.vend_id = vendors.vend_id and orderitems.prod_id = products.prod_id and order_num = 2005;

结果：

prod_name        vend_name        prod_price    quantity
.5 ton anvil    Anvils R Us            5.99        10
1 ton anvil        Anvils R Us            9.99         3
TNT (5 staicks)    ACME               10.00         5    Bird seed        ACME               10.00         1

十四、创建高级联结

十五、组合查询

1. 使用union

单个查询：

select vend_id, prod_id, prod_price from products where prod_price <= 5;

vend_id        prod_id        prod_price
1003        FC            2.50
1002        FU1            3.42
1003        SLING        4.49
1003        TNT1        2.50

select vend_id, prod_id, prod_price from products where vend_id in (1001,1002);

vend_id        prod_id        prod_price
1001        ANV01        5.99
1001        ANV02        9.99
1001        ANV03       14.99
1002        FU1            3.42
1002        OL1            8.99

思考：我是否可以使用一条mysql语句来组合她们？

Eg：

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);

结果：

vend_id        prod_id        prod_price
1003        FC            2.50
1002        FU1            3.42
1003        SLING        4.49
1003        TNT1        2.50
1001        ANV01        5.99
1001        ANV02        9.99
1001        ANV03       14.99
1002        OL1            8.99

2. 包含或取消重复的行

Eg：

select vend_id, prod_id, prod_price from products where prod_price <= 5
union all
select vend_id, prod_id, prod_price from products where vend_id in (1001,1002);

结果：

vend_id        prod_id        prod_price
1003        FC            2.50
1002        FU1            3.42
1003        SLING        4.49
1003        TNT1        2.50
1001        ANV01        5.99
1001        ANV02        9.99
1001        ANV03       14.99
1002        FU1            3.42
1002        OL1            8.99

这里注意看下面这行↓

1002        FU1            3.42

是不是多了一行，这就是union all

3. 对组合查询结果排序

Eg：

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)
order by vend_id, prod_price;

结果：

vend_id        prod_id        prod_price
1001        ANV01        5.99
1001        ANV02        9.99
1001        ANV03       14.99
1002        FU1            3.42
1002        OL1            8.99
1003        FC            2.50
1003        TNT1        2.50
1003        SLING        4.49

十六、全文本搜索

注意：

MyISAM引擎支持全文本搜索；

InnoDB引擎不支持全文本搜索。

1. 启用全文本搜索

Eg：首先要创建一个MyISAM引擎的表。

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),
    fulltest(note_text)
)engine=MyISAM;

MySQL根据子句fulltest(note_text)的指示对它进行全文索引。

注意：这里的fulltext索引单个列！

2. 使用全文本搜索

Eg：

select note_text
from productnotes
where math(note_text) against('rabbit');

返回的结果只是包含rabbit的这一行。

注意：这里的math()里面的值必须与fulltext()里面的值相同；搜索不区分大小写。

使用like语句也是可以达到相同的效果的

select note_text
from productnotes
where math(note_text) like '%rabbit%';

2. 使用查询扩展

Eg：

select note_text from productnotes where math(note_text) against('anvils' with query expansion);

返回的结果是很多行，还包含了rabbit。

3. 布尔文本搜索

Eg:

select note_text from productnotes where math(note_text) against('heavy' in boolean mode);

十七、安全管理

管理用户

mysql>use mysql;
mysql>select user from user;

结果：

user
root

1. 创建用户账号

create user ben identified 'password';

2. 重命名用户账号

rename user ben to bforta;

3. 删除用户账号

drop user bforta;

4. 设置访问权限

查看赋予用户账号的权限

show grants for;

Eg：允许用户在crashcourse这个数据库中的所有表中使用select。

grant select on crashcourse.* to bforta;

• 整个服务器：grant all、revoke all
• 整个数据库，on database.*
• 特定的表，on database.table
• 特定的列
• 特定的储存过程

十八、数据库维护

1. 备份数据库

2. 进行数据库维护

Eg1：检查表键是否正确。

analyze table orders;

Eg2: 针对性地检查表。

check table orders, ordertems;

3. 诊断启动问题

mysql
    --help
    --safe-mode    //装载减去某些最佳配置的服务器
    --verbose    //显示全文本消息
    --version

4. 查看日志文件

• 错误日志：data目录→hostname.err
• 查询日志：data目录→hostname.log
• 二进制文件：data目录→hostname-bin
• 缓慢查询日志：data目录→hostname-slow.log

十九、改善性能

• 建议将MySQL运行在自己专用服务器上。
• MySQL的预设值可以调整: 比如内存分配, 缓冲区大小。
• MySQL是一个多线程的DBMS, 经常同时执行多个任务, 可能会被其中一个执行缓慢的任务影响, 使用 SHOW PROCESSLIST 显示活动进程来分析, 使用KILL杀死进程。
• SELECT语句的多种尝试: 联结, 并, 子查询等, 找出最佳方法。
• 使用EXPLAIN让MySQL解释如何执行一个查询。
• 存储过程执行比一条一条的执行个SQL语句快。
• 应该总是使用正确的数据类型。
• 少用SELECT * : 也就是不要检索超出需求的数据。
• 必须索引数据库表以改善数据检索的性能。
• 使用多条SELECT 和连接他们的UNION语句代替OR条件。
• 索引改善检索数据的性能, 但是影响增删改, 如果一个表不经常被检索, 则不要索引他们。
• Like很慢, 最好使用FULLTEXT。
• 数据库是不断变化的, 一组优化良好的表可能一会就面目全非了, 所以理想优化也会改变。