《MySQL必知必会》读书笔记

第5章 排序检索数据

select prod_name from products order by prod_name;

对名称以字母序排序。

select prod_name from products order by prod_price,prod_name;

先按照价格排序，若由相同则按名称排序。

select prod_name from products order by prodprice desc;  

 降序排序，缺省升序（asc）。

select prod_name from products order by prod_price desc limit 1; 

选出价格最高的商品名称。

• order by 子句位置位于from之后，limit之前。

第6章 过滤数据 

select prod_name from producs where prod_price=2.50;

选出价格为2.50的商品名称。

• where 子句位置位于from之后，order by之前。
• where子句操作符：=、<>、!=、<、<=、>、>= 、BETWEEN、IS NULL 。
• 比较字符串类型时使用单引号。

第7章 数据过滤

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

and操作符表示同时匹配多个条件。

select prod_id from products where vend_id=1003 and vend_id=1003;   

or操作符表示匹配任一条件。

• and 操作符的优先级大于 or 操作符，使用()可改变计算次序。

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

in操作符表示范围，取值用逗号分隔，功能与or相当但更快。

select prod_id from products where vend_id not in (1002,1003); 

not操作符表示不在范围内，支持in、between、exists。

第8章 用通配符进行过滤 

select prod_id from products where prod_name like 'jet%';  

like操作符表示使用通配符%进行匹配。

select prod_id from products where prod_name like '_jet%';  

%匹配任意字符（包括0个），_匹配一个字符。

第9章 用正则表达式进行搜索 

select prod_name from products where prod_name regexp 'jet';  

regexp操作符表示使用正则表达式，匹配子串。

第10章 创建计算字段

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

concat()函数用来拼接两个列。 

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

rtrim()函数删除数据右侧空格,ltrim()左侧，trim()全部。 

select concat(vend_name ,'(',vend_country,')') as  from vendors;  

as关键字赋予列别名。

select quantity*item_price as expanded_price from orderitems;  

执行算术计算。

第11章 使用数据处理函数

• 函数类型包括：文本处理函数、算术运算函数、日期和时间函数、DBMS系统函数。

第12章 汇总数据

• avg()平均值、count()行数、max()最大值、min()最小值、sum()和。

第13章 分组数据

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

group by 子句用于分组。

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

having子句用于过滤分组。

• where在分组之前过滤行，having在分组之后过滤分组。
• 查询子句顺序：select、from、where、group by、having、order by、limit 。

第14章 使用子查询

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

子查询：查询每个顾客的订单数。

• 子查询比较耗费性能。

第15章 联结表

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

等价于select vend_name,prod_name where vendors.vend_id=products.vend_id;    inner join 等值（内部）联结。

第16章 创建高级联结

select p1.prod_id from products as p1,products as p2 where p1.vend_id=p2.vend_id and p2.prod_id='DTNTR'; 

自联结：代替从一个表中检索数据时使用的子查询，速度更快。

• 自然联结：排除相同的列的联结，一般的内部联结属于自然联结。

select c.cust_id,o.orders_num from customers as c left outer join orders as o on c.cust_id=o.cust_id;

left(right) outer join 表示外部联结，左联结以左表为基准（选出所有行）关联右表（结果返回所有顾客的订单信息，也包括没有订单的顾客，内部联结则不会包括）；右联结相反。

第17章 组合查询

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

union关键字用于组合两条select语句成单个结果集。

• union会自动取消重复的行，union all不会。

第18章 全文本搜索

• MyISAM支持全文搜索，InnoDB不支持。
• sql全文搜索不常用，略。

第19章 插入数据

insert into customers(cust_name) values('vansl'); 

insert语句用于插入行，指定列名使得在表结构改变后仍能工作。

第20章 更新和删除数据 

update customers set cust_name='vansl',cust_emali='google@gmail.com' where cust_id=10005;  

update语句用于更新列。

delete from customers where cust_id=10005;   

delete语句用于删除行。

第21章 创建和操纵表

create table customers(cust_id int not null auto_increment);   

create语句用于创建表。

• not null：字段插入值不能未空；auto_increment：字段自增长，每个表只能有一个，一般用于主键；default：字段默认值；engine：引擎类型。

alter table vendors add vend_phone char(20);   

alter table用于更新表，add关键字用于添加列，drop column用于删除列。

alter table orderitems add constraint fk_orderitems_orders foreign key (order_num) references orders (order_num); 

add constraint...foreign key...references...用于添加外键约束。

drop table customers; 

drop语句用于删除表。

rename table customers to customers_bak;  

rename语句用于重命名表。

第22章 使用视图

• 视图相当于一张虚拟的表，用于简化查询
  

create view vendors2 as select concat(vend_name ,'(',vend_country,')') as vends_info from vendors;  

create view...as...语句用于创建视图。

select * from vendors2;  

使用视图。

第23章 使用存储过程

• 存储过程是一组sql语句的批处理。

call productpricing();  

call语句用于执行存储过程。

create procedure productpricing() 
begin
    select Min(prod_price) as pricehigh; 
end;  

create procedure...begin...end语句用于创建存储过程。

drop procedure productpricing; 

drop procedure语句用于删除存储过程。

cretae procedure ordertotal(
    in onumber int,
    out ototal decimal(8,2) 
) 
begin 
    select Sum(item_price*quantity) 
    from orderitems 
    where order_num=onumber 
    into ototal;
end;

create procedure ...(in...,out,) begin...into ...; end;语句用于创建带自定义输入参数(in)、输出(out)的存储过程。

call ordertotal(20005,@total);

调用上述存储过程，第一个参数为订单号，第二个参数为存储过程计算得出的变量名，不返回值。

select @total;

得到上述存储过程执行结果。

第24章 使用游标

• 游标用来存储结果集中的记录，MySql中游标只能用于存储过程和函数。

create procedure processorders()
begin
	--declare local varibles
	declare done boolean default 0;
	declare o int;
	declare t decimal(8,2);

	--declare the cursor
	declare ordernumbers cursor;
	for 
	select order_num from orders;
	--declare continue handler
	declare continue handler for sqlstate '02000' set done=1;

	--create a table to store the resutls;
	create table if not exists ordertotals(
		order_num int,
		total decimal(8,2)
	);

	--open cursor
	open ordernumbers;

	--loop through all rows
	repeat	

		--get order numbers
		fetch ordernumbers into o;

		--get the total for this order
		call ordertotal(o,1,t);

		--insert order and total into ordertotals
		insert into ordertotals(order_num,total) values(o,t);

	--end of loop
	until done end repeat;

	--close the cursor
	close ordernumbers;

end;

存储过程ordertotal见上一章。

declare...cursor，open...，close...：声明、打开、关闭游标。

fetch...into...：取游标中的数据并存储到变量中，然后移动游标指针到下一行。

第25章 使用触发器

• 触发器用于在事件发生时自动执行语句，只响应delete、insert、update。

第26章 管理事务处理

• 引用资料：事务处理的四个特性：1、一致性（最重要，其他三个特性为其服务）；2、原子性（一组操作要么全部执行要么全部不执行）；3、隔离性（事务之间不会相互影响）；4、持久性（数据从内存到硬盘）。

select * from ordertotals;
start transaction;
delete from ordertotals;
select * from ordertotals;
rollback;
select * from ordertotals;

start transaction语句创建事务，rollback语句用于回退，commit用于提交事务。

第27章 全球化和本地化

• 字符集为字母和字符的集合；编码为某个字符集成员的内部表示；校对为规定字符如何比较的指令。

第28章 安全管理

use mysql;
select user from user;

查看用户。

第29章 数据库维护

• 常用的数据库维护：备份数据、检查表、诊断启动问题、查看日志文件。

第30章 改善性能

• 性能优化：硬件、设置（show status）、进程、sql语句、使用存储过程、少用select * 等。

2018年4月4日 20:45