MySQL必知必会笔记(2)

# 1.支持全文本搜索
# 建表时指定要进行索引的列以支持全文本搜索
# MySQL根据子句FULLTEXT(note_text)的指示对它进行索引。
# 这里的FULLTEXT索引单个列，如果需要也可以指定多个列。
# 不要在导入数据时使用FULLTEXT，更新索引需要时间，可以在导入数据后再修改表
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),
    FULLTEXT(note_text)
) ENGINE=MyISAM;

# 2.进行全文本搜索
# 在索引之后，使用两个函数Match()和Against()执行全文本搜索
# Match()指定被搜索的列
# Against()指定要使用的搜索表达式。Against('rabbit')指定词rabbit作为搜索文本
select note_text from productnotes where match(note_text) against('rabbit');

# 上面的简单任务也可以使用like语句完成
select note_text from productnotes where note_text like '%rabbit%';

# rank是MySQL版本8.0.2中定义的MySQL保留字！！！不能使用rank作为列名
# 当字段名与MySQL保留字冲突时,可以用字符''将字段名括起来
# 或者改为其他名字，比如as rank1等
# 计算全文本搜索结果的等级值
select note_text, match(note_text) against('rabbit') as 'rank' from productnotes; 
# 全文本搜索排除等级为0的行，并且搜索结果按照等级值降序排列

# 3.查询拓展
# 进行一个简单的全文本搜索，没有查询扩展
select note_text from productnotes where match(note_text) against('anvils');
# 相同的搜索，这次使用查询扩展
select note_text from productnotes where match(note_text) against('anvils' with query expansion);

# 全文本搜索检索包含词heavy的所有行
# 关键字IN BOOLEAN MODE，实际上没有指定布尔操作符，其结果与没有指定布尔方式的结果相同
select note_text from productnotes where match(note_text) against('heavy' in boolean mode);
# -rope* 排除包含以rope开始的词(如ropes)的行
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而不是匹配两个词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);

-- 插入完整的行 
# 插入一个新客户到customers表
# 简单但不安全，如果原来表列结构调整，会有问题 
insert into customers values(null,'Pep E. LaPew','100 Main Street','Los Angeles','CA','90046','USA',NULL,NULL);
# 表明括号内明确列名，更安全，稍繁琐 
# 可以忽略某些列，比如cust_id
# 忽略的列必须满足下面某个条件
# 1.该列定义为允许NULL值（无值或空值)。
# 2.在表定义中给出默认值。这表示如果不给出值，将使用默认值。
insert into customers (cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
values ('Pep E. LaPew','100 Main Street','Los Angeles','CA','90046','USA',NULL,NULL);

-- 插入多个行 
# 方法1： 提交多个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');
insert into customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
values('M. Martian','42 Galaxy Way','New York','NY','11213','USA');
# 方法2： 只要每条INSERT语句中的列名（和次序）相同，可以如下组合各语句
# 单条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');

-- 插入检索出的数据
-- INSERT还存在另一种形式，可以利用它将一条SELECT语句的结果插入表中。
-- 建立一张custom_new表
CREATE TABLE `custnew` (
    `cust_id` int(11) NOT NULL AUTO_INCREMENT,
    `cust_name` char(50) NOT NULL,
    `cust_address` char(50) DEFAULT NULL,
    `cust_city` char(50) DEFAULT NULL,
    `cust_state` char(5) DEFAULT NULL,
    `cust_zip` char(10) DEFAULT NULL,
    `cust_country` char(50) DEFAULT NULL,
    `cust_contact` char(50) DEFAULT NULL,
    `cust_email` char(255) DEFAULT NULL,
    PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB;
# 将customers表的数据导入custom_new表中
# 这里省略了custs_id,这样MySQL就会重新生成新值。
insert into custnew (cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
select cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country from customers;

-- update语句 : 删除或更新指定列 
# 更新某个列，比如客户10005现在有了电子邮件地址
update customers set cust_email = 'elmer@fudd.com' where cust_id = 10005;
# 更新多个列 
UPDATE customers 
SET cust_name = 'The Fudds',
    cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

# 删除某个列的值，可设置它为NULL（假如表定义允许NULL值）
update customers set cust_email = null where cust_id = 10005;

-- delete 语句： 删除整行而不是某列 
# 从customers表中删除一行
delete from customers where cust_id = 10006;

-- 删除custnew表中所有行
delete from custnew;

-- truncate table语句 
# 如果想从表中删除所有行，不要使用DELETE，可使用TRUNCATE TABLE语句
# TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据
# 删除cust_new表
TRUNCATE TABLE custnew; 

CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL DEFAULT 1,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

# 查看默认使用的引擎类型
SHOW VARIABLES LIKE 'default_storage_engine%';

-- 新建customers表
CREATE TABLE IF NOT EXISTS customers 
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  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;

-- 更新表 alter table 
# 给vendors表增加一个名为vend_phone的列
alter table vendors 
add vend_phone char(20);
# 删除刚刚添加的列
alter table vendors
drop column vend_phone;

# ALTER TABLE的一种常见用途是定义外键
# 以下为书本配套文件create.sql中定义外键的语句 
# 使用ALTER TABLE语句前记得做好备份! ! !
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

-- 删除表
# 删除customers2表(假设它存在),删除整张表而不单单是其内容
drop table customers2;

-- 重命名表 
# 使用RENAME TABLE语句可以重命名一个表 (假设存在下述表)
rename table customers2 to customers;
# 对多个表重命名(假设存在下述表)
rename table backup_customers to customer,
			 backup_vendors to vendors,
             backup_products to products;

/*视图提供了一种MySQL的SELECT语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据。 */ 

-- 创建视图 create view
-- 查看已创建视图的语句 show create view viewname
-- 删除视图 drop view viewname
-- 更新视图(有下面两种方式)
   -- 1. 先drop后create 
   -- 2. 直接使用create or repalce view

# 创建一个名为productcustomers的视图
# 保存了订购了任意产品的所有客户数据
create view productcustomers as
select cust_name,cust_contact,prod_id
from customers,orders,orderitems
where customers.cust_id = orders.cust_id
and orders.order_num = orderitems.order_num;
# 检索订购了产品TNT2的客户
select cust_name,cust_contact from productcustomers where prod_id = 'TNT2';

# 用视图重新格式化检索出的数据
# (来自第10章）在单个组合计算列中返回供应商名和位置
select concat(rtrim(vend_name),' (',rtrim(vend_country),')') as vend_title from vendors order by vend_name;
# 若经常使用上述格式组合，可以创建视图 
create view vendorlocations as
select concat(rtrim(vend_name),' (',rtrim(vend_country),')') as vend_title from vendors order by vend_name;
# 检索出以创建所有邮件标签的数据
select * from vendorlocations;

# 用视图过滤不想要的数据
# 定义customeremaillist视图，它过滤没有电子邮件地址的客户
create view customeremaillist as 
select cust_id,cust_name,cust_email from customers
where cust_email is not null;
select * from customeremaillist;

# 使用视图与计算字段
# (来自第10章）检索某个特定订单中的物品，计算每种物品的总价格
select prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems where order_num = 20005;
# 将其转换为一个视图
create view orderitemsexpanded as 
select order_num,prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems;
# 创建视图的时候select添加了列名order_num,否则无法按照order_num进行过滤查找 
select * from orderitemsexpanded where order_num = 20005;

DELIMITER // # 临时修改语句分隔符
CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
    FROM products;
END //
DELIMITER ; # 修改回去

 # 存储过程定义
 # ()中可以定义传入的参数
CREATE PROCEDURE productpricing()
# 存储过程体
BEGIN 
	SELECT Avg(prod_price) AS priceaverage
    FROM products;
END

call productpricing();

-- 删除存储过程,请注意:没有使用后面的()，只给出存储过程名。
drop procedure productpricing;
-- 当存储过程不存在进行删除时不报错可以使用下面的语句
drop procedure if exists productpricing;

create procedure productpricing(
	out pl decimal(8,2), # 返回的结果保留两位小数
	out ph decimal(8,2), 
	out pa decimal(8,2)
)
begin
	select min(prod_price) into pl from products;
    select max(prod_price) into ph from products;
	select avg(prod_price) into pa from products;
end
# 所有MySQL变量必须以@开头
# 使用三个变量保存调用传出的结果
call productpricing(@pricelow,@pricehigh,@priceaverage);

# 查看结果
select @pricelow,@pricehigh,@priceaverage;

# 使用IN和OUT参数,存储过程ordertotal接受订单号并返回该订单的合计

create procedure ordertotal(
	in onumber int,   				# onumber定义为IN，因为订单号被传入存储过程
    out ototal decimal(8,2)			# ototal为OUT，因为要从存储过程返回合计
)
begin
	select sum(item_price*quantity) from orderitems 
    where order_num = onumber
    into ototal;
end
# 传入订单号获得总价
call ordertotal(20005,@total);
# 显示总价
select @total;

-- 存储过程名:ordertotal
-- 参数: onumber： 订单号
-- 		taxable：值为1时需要纳税，为0时不需要
-- 		ototal：最后的合计

create procedure ordertotal(
	in onumber int,
	in taxable boolean,
    out ototal decimal(8,2)
) comment 'obtain order total, optionally adding tax'
begin
	-- 定义局部变量total
    declare total decimal(8,2);
    -- 定义局部变量税率(局部变量支持默认值)
    declare taxrate int default 6;
    -- 根据输入变量onumber获得订单合计,传递给局部变量total
	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;
	--  最后，传递给输出变量ototal 
	SELECT total INTO ototal;
end

# 调用上述存储过程，不加税 
call ordertotal(20005,0,@total);
select @total;
# 调用上述存储过程，加税 
call ordertotal(20005,1,@total);
select @total;

# 显示创建某个存储过程的CREATE语句
show create procedure ordertotal;

# 获得包括何时、由谁创建等详细信息的存储过程列表
# 该语句会列出MySQL中所有存储过程
show procedure status;

# 过滤模式：只展示ordertotal这个存储过程
show procedure status like 'ordertotal';

-- 创建、打开、关闭游标 
# 定义名为ordernumbers的游标，检索所有订单
# 游标定义在存储过程中，存储过程处理完成之后，游标就消失了
create procedure processorders()
begin
	-- decalre the cursor 声明游标 
	declare ordernumbers cursor
    for
    select order_num from orders;

	-- open the cursor 打开游标
	open ordernumbers;
	-- close the cursor 关闭游标
	close ordernumbers;
end

-- 使用游标数据的几个例子
# 例1：检索当前行的order_num列，对数据不做实际处理
create procedure processorders()
begin
	--  声明局部变量
    declare o int;
    -- 声明游标 
	declare ordernumbers cursor
    for
    select order_num from orders;
	-- 打开游标
	open ordernumbers;
    -- 获得订单号 
    fetch ordernumbers into o;
    /*fetch检索当前行的order_num列（将自动从第一行开始）到一个名为o的局部声明变量中。
    对检索出的数据不做任何处理。*/
	-- 关闭游标
	close ordernumbers;
end

# 例2：循环检索数据，从第一行到最后一行，对数据不做实际处理
create procedure processorders()
begin
	-- 声明局部变量
    declare done boolean default false;
    declare o int;
	--  声明游标 
	declare ordernumbers cursor
	for
    select order_num from orders;
  	-- 
    declare continue handler for sqlstate '02000' set done = true;
    -- SQLSTATE '02000'是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。
    -- 打开游标
	open ordernumbers;
    -- 遍历所有行 
    repeat
    -- 获得订单号 
    	fetch ordernumbers into o;
    -- FETCH在REPEAT内，因此它反复执行直到done为真
    until done end repeat;
  	-- 关闭游标
	close ordernumbers;
end 

# 例3：循环检索数据，从第一行到最后一行，对取出的数据进行某种实际的处理
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;
    -- declare continue handler
    declare continue handler for sqlstate '02000' set done = 1;
    -- 新建表以保存数据(订单号和总价)
    create table if not exists ordertotals
    (order_num int,total decimal(8,2));
    -- 打开游标
    open ordernumbers;
    -- 遍历所有行
    repeat
    -- 获取订单号
    fetch ordernumbers into o;
    -- 计算订单金额
    call ordertotal(o,1,t);  # 直接调用之前创建的ordertotal存储过程
    -- 将订单号、金额插入表ordertotals内
    insert into ordertotals(order_num,total) values(o,t);
	-- end of loop
    until done end repeat;
	--  关闭游标
	close ordernumbers;
end 
# 调用存储过程 precessorders()
call processorders();
# 输出结果
select * from ordertotals;

create trigger newproduct after insert on products 
for each row 
begin
	select 'product added' into @new_pro;
end

drop trigger newproduct;

# order表的order_num列是AUTO_INCREMENT列
create trigger neworder after insert on orders
for each row 
begin
	select new.order_num into @order_num;
end

# 创建一个备份表
CREATE TABLE archive_orders
(
  order_num  int      NOT NULL ,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;

# 使用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); # 引用一个名为OLD的虚拟表，访问被删除的行
end 

delete from orders where order_num=20005

# update触发器
# 在更新vendors表中的vend_state值时，插入前先修改为大写格式 
create trigger updatevendor before update on vendors 
for each row set new.vend_state = upper(new.vend_state);
# 更新1001供应商的州为china
update vendors set vend_state = 'china' where vend_id =1001;
# 查看update后数据，1001供应商对应的vend_state自动更新为大写的CHINA
select vend_state from vendors where vend_id=1001;

-- 控制事务处理
# 开始事务及回退 
select * from ordertotals;   # 查看ordertotals表显示不为空
start transaction;  		 # 开始事务处理 
delete from ordertotals;     # 删除ordertotals表中所有行
select * from ordertotals;   # 查看ordertotals表显示为空
rollback;				     # rollback语句回退 
select * from ordertotals;   # rollback后，再次查看ordertotals表显示不为空

# commit 提交 
start transaction;
delete from orderitems where order_num = 20010;
delete from orders where order_num = 20010;
commit;   # 仅在上述两条语句不出错时写出更改 

# savepoint 保留点 
# 创建保留点
savepoint delete1;
# 回退到保留点 
rollback to delete1;
# 保留点在执行一条ROLLBACK或COMMIT语句后会自动释放
# 也可使用release savepoint手动释放保留点
release savepoint delete1;

-- 更改默认的提交行为 
set autocommit = 0;  # 设置autocommit为0（假）指示MySQL不自动提交更改，不管有没有COMMIT语句

-- 字符集和校对顺序
# 查看所支持的字符集完整列表
show character set;
# 查看所支持校对的完整列表,以及它们适用的字符集
show collation;
# 确定所用系统的字符集和校对
show variables like 'character%';
show variables like 'collation%';
# 以_ci结尾的校对不区分大小写，以_cs结尾的校对区分大小写
# 使用带子句的CREATE TABLE，给表指定字符集和校对
create table mytable
(
	column1 int,
    column2 varchar(10)
)default character set hebrew 
collate hebrew_general_ci;
# 除了能指定字符集和校对的表范围外，MySQL还允许对每个列设置它们
create table mytable
(
	column1 int,
    column2 varchar(10),
    column3 varchar(10) character set latin1 collate latin1_general_ci
)default character set hebrew 
collate hebrew_general_ci;
# 校对collate在对用ORDER BY子句排序时起重要的作用
# 如果要用与创建表时不同的校对顺序排序,可在SELECT语句中指定备用的校对顺序
select * from customers order by lastname,firstname collate latin1_general_cs;

-- 管理用户
# 需要获得所有用户账号列表时
# mysql数据库有一个名为user的表，它包含所有用户账号。user表有一个名为user的列
use mysql;
select user from user;

-- 创建用户账号 
# 使用create user
# 用户名: ben 密码: passwd
create user ben identified by 'passwd';
# 重命名一个用户账号
rename user ben to bforta;
# 删除用户账号 
drop user bforta;
# 查看赋予用户账号的权限
show grants for bforta;
# 允许用户在（crashcourse数据库的所有表）上使用SELECT，只读
grant select on crashcourse.* to bforta;
# 重新查看赋予用户账号的权限，发生变化 
show grants for bforta;
# 撤销特定的权限
revoke select on crashcourse.* from bforta;
# 简化多次授权
grant select,insert on crashcourse.* to bforta;

-- 更改口令,新口令必须传递到Password()函数进行加密。
set password for bforta = 'newpasswd'; 
# 原来课本中使用的password()加密函数，在8.0版本中已经移除 
# password() :This function was removed in MySQL 8.0.11.
-- 如果不指定用户名，直接修改当前登录用户的口令 
set password = 'newpasswd';

# 导出db数据库下的db_table表到test.sql
mysqldump -u root -p db db_table > test.sql
# 导出db数据库下的所有表到test.sql
mysqldump -u root -p db > test.sql
# 导出db1、db2数据库到test.sql
mysqldump -u root -p --databases db1 db2 > test.sql
# 导出db1的table_1表和table_2表到test.sql
mysqldump -u root -p --databases db1 --tables table_1 table_2 > test.sql
# 只导出db1数据库的所有表结构不导出数据
mysqldump -u root -p --databases db1 --no-data > test.sql
# 导出db1的表+视图+存储过程+表结构
mysqldump -u root -p --databases db1 -R > test.sql

source ~/test.sql

# 用于检查表的键状态是否正确
analyze table orders;
# 检查表是否存在错误 
check table orders,orderitems;
# 回收删除大量数据表的空间
optimize table orders;

IF(condition, statement_if_true, statement_if_false)

SELECT name, age,
    IF(age < 18, 'Underage', IF(age < 65, 'Adult', 'Senior')) AS age_group
FROM users;

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE else_result
END

SELECT NAME,
	( CASE sex WHEN 'f' THEN '女性' WHEN 'm' THEN '男性' END ) AS sex
FROM
	people

CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE else_result
END

SELECT amount, date,
    CASE 
        WHEN amount < 1000 THEN 'Low'
        WHEN amount >= 1000 AND amount <= 5000 THEN 'Medium'
        ELSE 'High'
    END AS order_level
FROM orders;