mysql crash course 练习语句

-- select prod_name from products where prod_name like '1000' order by prod_name;
-- select prod_name from products where prod_name REGEXP '.000' order by prod_name;
/*select prod_name from products where prod_name REGEXP '1000' order by prod_name;*/
/*select vend_name from vendors where vend_name regexp '\\.';*/
#select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)' order by prod_name;

-- 和下面那句等价
#select prod_name from products where prod_name regexp '[[:digit:]]{4}'； 

-- 连在一起的任意4位数
#select prod_name from products where prod_name regexp '[0-9]{4}'; 
#select concat(vend_name ,'(',vend_country,')') as title from vendors;

-- 包含多个聚集函数例子：
#select count(*) as num_items, avg(prod_price) as avg_price,min(prod_price) as min_price ,max(prod_price) as max_price from products

-- 分组
#select vend_id, count(*) as num_prods from products group by vend_id;

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

-- 子查询
#select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2');

-- 联结
#select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id = products.vend_id;
#select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id;

-- 用联结语句代替子查询
#select cust_name ,cust_contact from customers,orders,orderitems 
#where customers.cust_id = orders.cust_id and orders.order_num = orderitems.order_num and orderitems.prod_id = 'TNT2';

-- 内部联结
#select customers.cust_id , orders.order_num from customers inner join orders on customers.cust_id = orders.cust_id;

-- 外部联结
#select customers.cust_id , orders.order_num from customers left outer join orders on customers.cust_id = orders.cust_id;

-- 带聚集函数的联结
#select customers.cust_name,customers.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; 

-- 组合查询
#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 note_text from productnotes where match(note_text) Against('rabbit');

-- 列出 列名
#INSERT INTO customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact)
# VALUES (NULL,'xie','china','shanghai','SH','86021','CHA','Y Lee')

-- 省略列名
#INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact)
# VALUES ('xie','china','shanghai','SH','86021','CHA','Y Lee')

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

-- 创建表
/*create table orderitems2
(
order_num int not null,
order_item intnot null,
prod_id char(10) not null,
quantity int not null,
item_pirce decimal(8,2) not null,
primary key(order_num,order_item)
) engine = InnoDB;*/

-- 创建一条视图
/*create view orderitemsexpanded as
 select order_num,prod_id,quantity,item_price, quantity*item_price as expanded_price
from orderitems;*/

-- 使用视图
#select * from orderitemsexpanded;
#select * from orderitemsexpanded where order_num = 20005;

-- 删除视图
#drop view orderitemsexpanded;

-- 创建简单的存储过程
/*delimiter // -- 使// 作为语句结束分割符
create procedure productpricing()
begin
select avg(prod_price) as priceaverage from products;
end//
delimiter ; */ -- 使; 作为语句结束分隔符

-- 存储过程 oredertotal
-- name: oredertotal
-- parameters: number = order number
-- taxable= 0 if not taxable ,1 if taxable
-- ototal = order total vaiable
-- 
/*create procedure ordertotal(
in onnumber int,
in taxable bool,
out ototal decimal(8,2)
)comment 'Obtain order total ,optionally adding tax'
begin
-- declare varibale for total
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;
end;*/

-- 使用存储过程
#call productpricing();
-- 查看存储过程详细信息
#show create procedure productpricing;
-- 查看所有的存储过程
#show procedure status;
-- 删除存储过程，不存在时会返回一个错误
#drop procedure productpricing;
-- 如果存在则删除，不存在也不会返回错误
#drop procedure if exists productpricing;

--  创建，打开，使用，关闭游标
/*Create procedure processorders()
begin
-- 定义局部变量 o
declare o INT;
-- 定义游标
declare ordernumbers cursor for select order_num from orders;
-- 打开游标
open ordernumbers;
-- 获取结果集指针
fetch ordernumbers into o;
-- 关闭游标
close ordernumbers;
end;*/

-- 简单的游标应用，把order表的order_num,cust_id填充到新表ordertotals中
/*Create procedure processorders()
begin

declare done bool default 0;
declare r INT;
declare t decimal(8,2);
declare ordernumbers cursor for select order_num,cust_id from orders;
declare continue handler for sqlstate '02000' set done = 1;
create table if not exists ordertotals(order_num int,total decimal(8,2),primary key(order_num));
open ordernumbers;
repeat
fetch ordernumbers into r,t;
insert into ordertotals(order_num,total) values(r,t);
until done end repeat;
close ordernumbers;
end;
call processorders();*/

-- 删除记录时的触发器
/*create trigger trg_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*/

-- 查看创建的触发器
-- show triggers;
-- 删除表orders的一条记录来测试触发器
-- delete from orders where order_num = 20011;
-- 删除触发器
-- drop trigger tgr_neworder;

-- 控制事务处理 实例
/*select * from ordertotals;

start transaction; -- 开始事务
delete from ordertotals;
select * from ordertotals;
rollback; -- 回滚

select * from ordertotals;*/

-- 普通mysql语句是隐含提交的，处理事务语句块时不会隐含提交，必须显示提交
/*start transaction;
delete from orderitems where order_num = 20010;
delete from orders where order_num = 20010;
commit;*/

-- 显示默认字符集和描述
-- show character set;

--  查看所有用户
-- select user from user;

-- 查看root用户的权限
-- show grants for root;

-- 创建用户guest_usr,密码为123
-- create user guest_usr identified by '123';

-- 对guest_usr用户授予select，insert权限 ,多个权限使用 逗号分隔
-- grant select,insert on test.* to guest_usr;

-- 撤销guest_usr 的 insert 权限
-- revoke insert on test.* from guest_usr;

-- 授予test全部权限，除了 grant权限
-- grant all on test.* to guest_usr;

-- 删除用户
-- drop user guest_usr;

-- 修改用户的密码，第二个password是个函数，修改密码必须通过password函数加密。
-- set password for guest_usr = password('12345');

-- 检查orders表是否正确
-- analyze table orders;

-- 检查orderitems表
-- check table orderitems;