第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