《MySQL必知必会》代码总结

本文主要总结了Ben Forta著的《MySQL必知必会》的代码，可以用作初学者快速查找代码。

1. 检索列中数据的种类数

# 假设products表中列vend_id存在很多重复的数据
select vend_id from products;
/*输出
* vend_id
* 1001
* 1001
* 1001
* 1002
* 1002
* 1003
* 1003
* 1004
* 1004
*/

# 使用DISTINCT关键字，返回该列数据的集合
select distinct vend_id from products;
/*输出
* vend_id
* 1001
* 1002
* 1003
* 1004
*/

2. 限制结果

# 返回前5行
select prod_name from products limit 5;

# 返回第1行开始的3行数据
select prod_name from products limit 0,3;

3. 排列数据

# 按prod_name中的字母升序（默认为升序）来排序
select prod_name from products order by prod_name;

# 按prod_name中的字母降序来排序
select prod_name from products order by prod_name desc;

# 先按价格、再按商品名称进行排序。仅在多个相同的prod_price中，再按prod_name排序。如果prod_price所有值都是不同的，则不会按prod_name排序。
select prod_id, prod_price, prod_name from products order by prod_price, prod_name;

# 先按价格降序，再按商品升序排列。其中desc直接作用于其前面的列名
select prod_id, prod_price, prod_name from products order by prod_price desc, prod_name;

# 结合order by 和 limit，找出最大值
select prod_price from products order by prod_price desc limit 1;

4. 过滤数据

# where 语句
select prod_name, prod_price from products where prod_price<10;

# where的between语句
select prod_name, prod_price from products where prod_price between 5 and 10;

# where的and语句
select prod_id, prod_price, prod_name from products where vend_id=1003 and prod_price<=10;

# where的or语句
select prod_id, prod_price, prod_name from products where vend_id=1002 or vend_id=1003;

# in语句：返回供应商1002和1003制造的所有产品
select prod_name, prod_price from products where vend_id in (1002,1003) order by prod_name;

# not in语句：返回除1002和1003之外的所有供应商的产品
select prod_name, prod_price from products where vend_id not in (1002,1003) order by prod_name;

# 通配符%：找出以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%';

# 通配符_：只匹配单个字符，结果只返回'1 ton anvil'和'2 ton anvil'，而不返回'.5 ton anvil'
select prod_id, prod_name from products where prod_name like '_ ton anvil';

5. 正则表达式

# 返回prod_name包含文本1000的结果
select prod_name from products where prod_name regexp '1000' order by prod_name;
/*输出
* prod_name
* JetPack 1000
*/

# 返回任何以'000'为结尾的结果
select prod_name from products where prod_name regexp '.000' order by prod_name;
/*输出
* prod_name
* JetPack 1000
* JetPack 2000
*/

# 返回包含'1000'或'2000'的结果
select prod_name from products where prod_name regexp '1000|2000' order by prod_name;

# 返回任何包含'2 Ton'或'5 Ton'字符的结果
select prod_name from products where prod_name regexp '[2 5] Ton' order by prod_name;
/*输出
* prod_name
* 2 ton anvil
* .5 ton anvil
*/

# 返回包含数字1~5的结果
select prod_name from products where prod_name regexp '[1-5] Ton' order by prod_name;
/*输出
* prod_name
* .5 ton anvil
* 1 ton anvil
* 2 ton anvil
*/

# 匹配包含特殊字符'.'或'-'等，需要使用'\\'作为转义符
select vend_name from vendors where vend_name regexp '\\.' order by vend_name;
/*输出
* prod_name
* Furball Inc.
*/

# 重复元字符?：?匹配它前面单个字符0次或1次
select prod_name from products where prod_name regexp '\\([0-9] colou?r\\)'
/*输出
* prod_name
* TNT (1 color)
* TNT (5 colour)
*/

# 重复元字符{n}：匹配连续4个数字字符
select prod_name from products where prod_name regexp '[0-9]{4}'
/*输出
* prod_name
* JetPack 1000
* JetPack 2000
*/

# 定位元字符^：只返回'0-9'或'.'在匹配串开头的结果
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
*/

6. 计算字段

# 将列vend_name和vend_country的信息以'vend_name(vend_country)'的形式返回，并将返回结果命名为新的列vend_title
select concat(vend_name, '(', vend_country, ')') as vend_title from vendors order by vend_name;
/*输出
* vend_title
* ACME (USA)
* Furball Inc. (USA)
*/

# 可将列的信息直接进行 加减乘除 后返回
select 
    prod_id, 
    quantity, 
    item_price, 
    quanttty*item_price as expanded_price
from orderitems where order_num=20005;
/*输出
* prod_id    quantity    item_price    expanded_price    
* ANV01         10          5.99           59.90
* ANV02         3           9.99           29.97
*/

7. 文本处理函数

函数	说明
Left(s,k)	截取串s左边的k个字符,例如select left(‘12345’,2) 结果就是 12
Length(s)	返回字符串s的长度
Locate(s,ss)	返回子串ss在串s的位置，例如select locate(‘bar’, ‘foobarbar’)的结果为4
Lower(s), Upper(s)	转为为小写、大写
LTrim(s)	去除串s左边的空格
Soundex(s)	返回串的SOUNDEX值，即发音值

# 将列的数据转为为大写
select vend_name, Upper(vend_name) as vend_name_upcase from vendors order by vend_name;
/*输出
* vend_name    vend_name_upcase     
*  anvils          ANVILS  
*/

# 返回英语发音相似的结果
select cust_name, cust_contact from customers where soundex(cust_contact) = soundex('Y Lie');
/*输出
* cust_name    cust_contact     
* Coyote Inc.     Y Lee  
*/

8. 日期和时间处理函数

函数	说明
Date()	返回日期部分
Day()	返回天数部分
Hour()	返回小时部分
Minute	返回分钟部分

# 如果列order_date的格式为'2005-09-01 11:30:05'，则可以用函数Date()只对日期进行搜索
select cust_id, order_num from orders where Date(order_data) = '2005-09-01';

# 如果列order_date的格式为'2005-09-01 11:30:05'，这时需要检索2005年9月份的所有记录，则可以：
select cust_id, order_num from orders where Year(order_data) = 2005 and Month(order_data) = 9;

9. 聚集函数

函数	说明
AVG()	求平均
COUNT()	count(*)返回该表的行数，count©计算列c中非NULL的数量
MAX()	求最大值
MIN()	求最小值

# 求平均值
select AVG(prod_price) as avg_price from products;

# 求表customers的行数
select COUNT(*) as num_cust from customers;

# 汇总统计
select COUNT(*) as num_items,
    MIN(prod_price) as price_min,
    MAX(prod_price) as price_max,
    AVG(prod_price) as price_AVG,
    SUM(prod_price*quantity) as total_price
from products;
/*输出
* num_items    price_min    price_max    price_AVG    total_price  
*    14           2.50         55.00     16.133571       260
*/

10. 分组数据

  having针对分组进行过滤，where针对行进行过滤

# 以cust_id进行分组，并计算每个分组内的行数，返回行数>=2的分组
select cust_id, count(*) as orders from orders group by cust_id having count(*)>=2;

# 先选出prod_price>=10的数据，然后以vend_id进行分组，并计算每个分组内的行数，返回行数>=2的分组
select vend_id, count(*) as num_prods from products where prod_price >=10 group by vend_id having count(*) >= 2;

# 以order_num进行分组，并计算每个分组内的总价，返回总价>=50的数据
/*表ordertiems的格式如下：
* order_num    order_item    item_price    quantity  
*    20005         6           55.00          16    
*    20005         7           10.00          8    
*    20005         8           20.00          20    
*                       ...     
*                       ...     
*                       ...     
*/
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  
*   20006         55.00
*   20008         125.00
*   20005         149.87
*   20007         1000.00
*/

11. 子查询

# 查询包含物品TNT2的订单编号，然后查询这些订单的用户编号，最后返回这些用户的信息
select cust_name, cust_contact from customers where cust_id in 
    (select cust_id from orders where order_num in 
        (select order_num from orderitems where prod_id = 'TNT2')
    );
    
# 先从customers表检索用户列表，然后统计每个用户在orders表中的订单数目
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
*/

12. 内部联结

  products表只存储产品信息，除了存储供应商ID（vendors表的主键）外不存储供应商的其他信息。vendors表的主键又叫做products表的外键，它将vendors表与products表关联。

  将数据分开两个表存储的好处为：

1. 在products表中不需存储大量重复的供应商信息
2. 如果供应商信息变动，只需修改vendors表中的单个记录，而不需修改products表

# 通过供应商id联结查询表vendors和表products
select vend_name, prod_name, prod_price
from vendors, products
where vendors.vend_id = products.vend_id
order by vend_name, prod_name;

# 上面语句，采用inner join语法，实现一样的结果
select vend_name, prod_name, prod_price
from vendors inner join products
on vendors.vend_id = products.vend_id;

13. 自联结

  针对同一个表中，需要先查到该表生产ID为DTNTR的物品的供应商ID，再根据该供应商ID查询其生产的其他物品。这种情况为需要对同一个表查询两次，需要用到子查询或自联结。

# 子查询：先查到该表生产ID为DTNTR的物品的供应商ID，再根据该供应商ID查询其生产的其他物品。
select prod_id, prod_name from products where vend_id = (select vend_id from products where prod_id = 'DTNTR');

# 自联结：先查到该表生产ID为DINTER的物品的供应商ID，再根据该供应商ID查询其生产的其他物品。
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 = 'DTNTR';

14. 外部联结

  当需要查询全部用户的订单情况，需要联结查询用户表customers和订单表orders。

# 内联结：不包括那些没有订单的客户
select customers.cust_id, orders.order_num
from customers inner join orders
on customers.cust_id = orders.cust_id;
/*输出
* cust_id   order_num
*  10001      20005    
*  10003      20007    
*/

# 外联结：包括那些没有订单的客户
select customers.cust_id, orders.order_num
from customers left outer join orders
on customers.cust_id = orders.cust_id;
/*输出
* cust_id   order_num
*  10001      20005
*  10002      NULL 
*  10003      20007    
*/

# 聚集函数+外联结：查询全部用户及所下订单数
select customer.cust_name,
       customer.cust_id,
       count(orders.order_num) as num_ord
from customers left outer join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id;
/*输出
* cust_name  cust_id    num_ord  
*  Coyote     10001        2
*   Mouse     10002        0
*   House     10003        1
*/

15. 组合查询

  虽然使用where语法更简洁，但是面对复杂条件时union语法是更简单的方式。

# where语法：查询vend_id为1001和1002的商品，和价格小于5的商品
select vend_id, prod_id, prod_price
from products
where prod_price<=5 or vend_id in (1001,1002);

# union语法：查询vend_id为1001和1002的商品，和价格小于5的商品
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);

16. 使用全文本搜索

# 在建表时，启动全文本搜索功能
create tabel rpoductnotes
(
    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;

# 使用全文本搜索功能，查询包含'rabbit'的记录
select note_text
from productnotes
where match(note_text) against('rabbit');

# 检索包含单词'heavy',且排除任何以'rope'开头的单词的记录
select note_text from productnotes where match(note_text) against ('heavy -rope*' 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 ('>rabbit <carrot' in boolean mode);

# 匹配词'safe'和'combination'，降低后者的等级
select note_text from productnotes where match(note_text) against ('+safe +(<combination)' in boolean mode);

17. 插入行

# 一次insert语句插入多个行
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'
    );
    
# 插入由select语句检索到的数据
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_country  
from custnew;

18. 更新和删除数据

# 更新数据
update customers
set cust_name = 'The Fudds',
    cust_email = 'elmer@fudd.com'
where cust_id = 10005;

# 删除数据
delete from customers
where cust_id = 10006;

19. 表的操作

# 创建表，其中列order_num必须为非空（如果为空值则插入失败，其中''不算空值），列quantity允许为空值，主键为order_num和order_item
create table orderitems
(
    order_num int not null,
    order_item int not null,
    quantity int null,
    primary key (order_num, order_item)
)

# 新增一列
alter table vendors add vend_phone char(20);

# 删除一列
alter table vendors drop column vend_phone;

# 定义外键: alter table 表名 add constraint 约束名称 约束类型 (列名) references 被引用的表名称 （列名）
alter table orderitems add constraint
fk_orderitems_orders foreign key (order_num)
references orders (order_num);

# 删除表
drop table customers;

# 重命名表
rename table customers2 to customers;

20. 视图

  视图可以理解为对select语句进行函数封装，以后调用该视图就是调用该select语句。

# 创建视图vendorlocations
create view vendorlocations as 
select concat(RTrim(vend_name),'(',RTrim(vend_country),')')
    as vend_title
from vendors
order by vend_name;

# 使用视图vendorlocations
select * from vendorlocations;
/*输出
* vend_title
* ACME (USA)
* Anvils R Us (USA)
*/

21. 存储过程

  存储过程可以理解为函数。

例子1：

# 创建存储过程ordertotal，能根据输入的订单号onumber，计算该订单的总价并返回到变量ototal
create procedure ordertotal(
    in onumber int,
    out ototal decimal(8,2)
)
begin 
    select sum(item_price*quantity)
    from ordertiems
    where order_num = onumber
    into ototal;
end;

# 调用存储过程ordertotal
call ordertotal(20005, @ototal);
select @ototal;
/*输出
* @ototal
* 149.87
*/

例子2：

# 创建存储过程ordertotal，能根据输入的订单号onumber，计算该订单在有或无税收的情况下的总价并返回到变量ototal
create procedure ordertotal(
    in onumber int,
    in taxable boolean,
    out ototal decimal(8,2)
)
begin
    -- 定义局部变量
    declare total decimal(8,2);
    declare taxrate int default 6;
    
    select sum(item_price*quantity)
    from orderitems
    where order_num = onumber
    into total;
    
    if taxable then
        select total+(total/100*taxrate) into total;
    end if;
    
    select total into ototal;
end;

# 调用，并计算为无税收的总价
call ordertotal(20005,0,@ototal)
select @ototal;
/*输出
* @ototal
* 149.87
*/

# 调用，并计算为有税收的总价
call ordertotal(20005,1,@ototal)
select @ototal;
/*输出
* @ototal
* 158.862200000
*/

# 显示存储过程ordertotal的内部代码
show create procedure ordertotal;

# 删除存储过程ordertotal
drop procedure ordertotal;

22. 游标

  游标相当于指针，其指向select语句查询结果的第一行，每次进行读取(fetch)后，自动跳到下一行。

create procedure processorders()
begin
    -- 定义局部变量
    declare done boolean default 0;
    declare o int;
    declare t decimal(8,2);
    
    -- 定义游标
    declare ordernumbers cursor
    for
    select order_num from orders;
    
    -- 定义continue handler，当循环repeat没有更多的行时出现错误代码'02000'时，设置停止信号done=1
    declare continue handler for sqlstate '02000' set done=1;
    
    -- 创建table用于存储结果
    create table if not exists ordertotals
        (order_num int, total decimal(8,2));
        
    -- 打开游标
    open ordernumbers;
    
    -- 循环
    repeat
        -- 游标读取一行数据
        fetch ordernumbers into o;
        
        -- 上一节定义的procedure，根据给定的订单编号o，在算上税的情况下(1)，计算订单总价t
        call ordertotal(o,1,t);
        
        insert into ordertotals(order_num, total) values(o,t);
        
    until done end repeat;
    
    --关闭游标
    close ordernumbers;
end;


-- 查看运行结果
select * from ordertotals;
/*输出
* order_num    total
*   20005      158.86
*   20006      58.30
*/

23. 触发器

# 每插入一行，都显示一次文本'Product added'
create trigger newproduct after insert on products
for each row select 'product added';

# 每删除一行信息，都将该信息(在old变量中)插入另一个表中
create trigger deleteorder before delete on orders
for each row
begin
    insert into archive_orders(order_num, order_date, cust_id)
    values(old.order_num, old.order_date, old.cust_id)
end;

# 每更新一条信息前，都将该信息改为全部大写
create trigger updatevendor before update on vendors
for each row set new.vend_state = upper(new.vend_state);

24. 事务处理

# 当顺利执行delete后，进行事务回退，即回退至'start transaction'前的状态
select * from ordertotals;
start transaction
delete from ordertotals;
select * from ordertotals;
rollback;
select * from ordertotals;

# 当完全顺利执行两条delete后，才将最终结果写入数据库
start transaction;
delete from orderitems where order_num = 20010;
delete from orders where order_num = 20010;
commit;

# 回退至存档点
savepoint delete1;
rollback to delete1;

25. 管理用户

# 创建用户
create user ben identified by 'p@$$w0rd';

# 修改用户已名
rename user ben to bforta;

# 删除用户
drop user bforta;

# 赋予权限：允许用户bforta在数据库crashcourse中的全部表中，有select权限
grant select on crashcourse.* to bforta;
show grants for bforta;

# 撤销权限：撤销用户bforta在数据库crashcourse中的全部表中所具有的select权限
revoke select on crashcourse.* from bforta;

# 更改用户密码
set password for bforta = Password('n3w p@$$w0rd');