《MySQL必知必会》 1300行代码凝练

use book_system;
show databases;
show columns from courses;
describe courses;
show status;
show create database book_system;
show create table courses;

show grants;
show errors;
show warnings;
select id,name from courses;
select * from courses;

select distinct id from courses;
select distinct id from courses
limit 3;

select distinct id from courses
limit 7,3; -- 表示从行7开始的 三个数字

select courses.id from courses
order by id desc
limit 0,5;
select courses.name from courses
order by name asc;

select id,name,student_count,created_at
from courses
order by created_at asc;
show warnings;

select id,name ,student_count,created_at
from courses
order by id asc, student_count desc;

# order by 与 limit 1结合可以达到找到最大值、最小值的目的
select student_count from courses
order by student_count
limit 1;

select student_count from courses
order by student_count desc
limit 1;

select id , name from courses
where id = 1 or id = 2
order by id desc
limit 0,2;
# = <> != < <= > >= between

select id,name , student_count from courses
where name = 'system design';
-- mysql 不区分大小写
select * from courses
where id >5;

-- 不匹配

select * from courses
where id <> 1;

select * from courses
where id != 1;

select id , name ,student_count from courses
where student_count between 500 and 1000
order by student_count desc
limit 0,10;

create table if not exists stocks (
    `id` int signed not null auto_increment ,
    `name` varchar(20) default null,
    `stack_number` varchar(10) default null,
    `new_price` decimal default null,
    `up_down` varchar(20) default null,
    primary key(id)
);

insert into stocks
( name, stack_number, new_price, up_down)
values
( '拼多多','PDD.O','95.000','2.20%'),
( '京东','JD.O','85.950','2.08%'),
( '百度','BIDU.O','170.570','1.98%'),
( '阿里巴巴','BABA.O','166.810','-0.62%'),
( '腾讯控股','00700.HK','485.200','1.59%'),
( '沪深300','1B0300.SH','488.38','-0.21%'),
( '纳斯达克','NDX.USI','16199.88','1.00%'),
( '微软','MSFT.O','336.720','1.29%'),
( '苹果','AAPL.O','149.990','1.43%'),
( '道琼斯','DJI.USI','36100.31','0.50%'),
( '创业指板','399006.SZ','3440.14','-0.09%');
SELECT * from stocks;

alter table stocks change stack_number stock_number varchar(20);
select id,name as 股票名字,stock_number as 股票代号,new_price as 最新价 , up_down as 涨跌幅
from stocks;

select name from stocks
where stock_number is null;

select * from stocks
where stock_number  is null;

-- 数据过滤 为了加强过滤 MySQL允许给出多个where子句 以and 或者 or进行连接
select * from stocks
    where
    new_price >=180
    and
    up_down > 0;

select name from stocks
where id = 10 or id = 1;
-- 股价在100 - 1000 之间并且涨幅》1 或跌幅》-1
select * from stocks
    where
          (new_price between 100 and 1000 and  up_down>=1)
    or
          (new_price between 100 and 1000 and up_down<=-1);


 -- 注意运用圆括号的高优先级

 select * from stocks
where id in (1,5,2); -- id为括号内的数即可输出

-- not 的使用

select * from stocks
where id not in(1,2,3);

select * from stocks
where name not like "腾讯%";


select name from stocks
where name like '%多'
          or name like '%果'
          or name like '%达%'
          or name like '%东'
          or name like  '%深%'; -- 通配符

-- 下划线_ 通配符 只能通配一个字符的长度
select * from stocks;

select new_price from stocks
where new_price like '%440'; -- 3440 and 33440


select new_price from stocks
where new_price like '_440'; -- 3440

# 通配符的代价是它的运行时间比较久 尽量不要过度使用通配符

# 正则表达式是用来匹配文本的特殊的串（字符集合）
-- 正则表达式有自己的正则表达式语言
select name from stocks
where name regexp '腾'
order by name ;
-- 他非常像like的用法
-- 正则表达式里'.' 可以代表任何字符
select name from stocks
where name regexp '.讯';
-- 当然这个也可以用通配符完成

# -------------------------------------------------------------------------------
select name from stocks
where name like '_讯%';
-- regexp正则表达式中的 or 用法
select * from stocks
where name regexp '里|腾'; -- 表示名字里有 里或腾的数据 展示出来
-- 还可以给出三个
select * from stocks
where name regexp '百|阿|腾'; -- bat

select * from stocks
where new_price regexp '[123]00';

select * from stocks
where name regexp '[123] ton';-- 1 ton 或2 ton 或3 ton

select * from stocks
where name regexp '1|2|3 ton';-- 如果不框起来 他们将作用于整个串
-- 1或 2 或3 ton
select * from stocks
where name regexp '[1-5] ton';
-- 名字包含1 ton 或2 ton 或3 ton 或4 ton 或5 ton都将被找到
-- 由于'.'在正则表达式内表示任意单个字符串
-- 所以要找到"."字符就看起来很麻烦 我们可以使用
-- \\. 找到.    \\- 找到 -  \\\ 找到 \

select name from stocks
where name regexp '\\([0-9] sticks?\\)';
-- \\(代表左括号(
-- [0-9]代表0-9任意一个数
-- sticks?代表可以是stick也可以是sticks  ‘?’使得前面的s可选
-- \\)代表 )
-- 即通过正则表达式来搜索名字里有 (0 sticks) 注：0可以是0-9 sticks也可以是stick

select new_price from stocks
where new_price regexp '[[:digit:]]{5}'
order by new_price desc;
-- 表示含有五个连续数字 [ [:digit:] ]表示0-9 {5}代表连续五个
-- 也可以这么表示

select new_price from stocks
where new_price regexp '[0-9][0-9][0-9][0-9][0-9]'
order by new_price desc;-- 和上面等价

-- 定位符^的使用
select up_down from stocks
where up_down regexp '^0\\.';

/**
  ----------------------------------------------------MySQL必知必会 p72 2021.11.15---------------------------------------------------
 */


use book_system;
select * from stocks;

select up_down from stocks
where up_down regexp '^[+|-][[:digit:]].';
-- 输出-0.62% -0.21% -0.09%
-- ^匹配串的开始
/*
regexp与like起相同作用，不同在于:like匹配整个串而regexp可以匹配子串
利用定位符^ 表示开始 $表示结尾 ，可以使得regexp的作用与like一样
 */

-- ------------------10计算字段---------------------
# 字段field 基本上与列的意思相同

# 拼接 concatenate 可以用concat()函数拼接两个列

select concat(name, '(',new_price,')')
from stocks
order by new_price desc;
-- concat函数可以把多列拼接在一起 乾坤控股(10000000) 道琼斯(36100)


select concat(name,'_',new_price)
from stocks
order by new_price desc;
-- 乾坤控股_10000000

select concat(rtrim(name),'(',new_price,')') as StockPrice
from stocks
order by new_price desc;

-- rtrim去空格 修饰作用 as则修改列名 可以选一个得体的名字

alter table stocks add column 我持有 decimal after up_down;

update stocks set 我持有=100.00 where name is not null;

select * from stocks;

 -- 此时就可以执行算术运算 来计算我持有多少钱了 股数x股价
 select name,
        stock_number,
        new_price,
        `我持有`,
        new_price*我持有 as sumPrice
        from stocks
        where new_price is not null;
-- + - * / 都支持，列之间的算术运算

# -----------------------11.使用数据处理函数--------------------

-- 如果使用函数请做好注释 以便以后阅读代码能快速明白

select name ,
       upper(name),
       lower(name)
       from stocks
where id between 14 and 18;
-- 有三列 一列是原来的name  另一列是全部大写的name 还有是全部小写的
-- upper() 函数 还有 lower函数

select name ,
#        left(),
#        right(),
       length(name),
       substring(0,3)
        from stocks
where id between 14 and 18;

select * from stocks
where soundex(name) = 'pa ky';

alter table stocks add column date_time date after 我持有;
update stocks
    set date_time = now()
where up_down is not null;
select name ,id
from stocks
where date_time = '2021-11-15';
-- 如果日期date_time是 2021-11-15 20：00：00的话 我们可以使用
-- date() 函数 做到只提取年月日
# 更可靠的语句如下
select name , id
from stocks
where date(date_time) = '2021-11-15';

select name , id
from stocks
where year(date_time) = '2021';
-- 也可以这么表示 相应的还有month()  day()

-- 如果像索引出 2021 年 11 月的所有数据
# 那么你可以
select * from stocks
where year(date_time) = '2021'
and month(date_time) = '11';

-- 你也可以
select * from stocks
where date(date_time) between '2021-11-01' and '2021-11-30';

# 你还可以
select * from stocks
where date_time regexp '2021-11';

# 你还可以
select * from stocks
where date_time like '2021-11%';
# 数值处理函数 abs()  、cos() 、exp()、mod()、pi()、sin()、sqrt()、tan()
-- mod() 是取余数 其他和java一致

# ---------------------12.汇总数据--------------------------
# 这种汇总类型有以下几种
/*
 1.确定表中行数
 2.获得表中行组的和
 3.找出表列
 */
select sum(id) from stocks;
select count(id) from stocks;

/*
 avg() 返回某列的平均值
 count()返回某列的行数（共有多少行数据
 max() 返回某列的最大值
 min() 返回某列的最小值
 sum() 返回某列值的总和

 */

 select avg(new_price) from stocks; --  平均值
select count(new_price) from stocks; -- 行计数 多少行
select max(new_price) from stocks; -- 最大值
select min(new_price) from stocks; -- 最小值
select sum(new_price) from stocks; -- 总和

-- max() min()  avg() sum()函数忽略null值
-- count()如果指定列名则忽略 如果select * from 则不忽略

-- 聚集不同值（刨除重复部分）

select avg(distinct new_price) from stocks;
select count(distinct new_price) from stocks;
select max(distinct new_price) from stocks;
select min(distinct new_price) from stocks;
select sum(distinct new_price) from stocks;
-- distinct 反义词是all 而all是默认的 不需要强调
#  distinct 必须用于列名 不能用于计算或表达式

# 组合一下聚合函数
select count(*),
       avg(new_price) as average_price,
       max(new_price) as max_price,
       min(new_price) as min_price,
       sum(new_price) as sum_price
from stocks;
-- 这些聚合函数是高效设计的 一般比你在自己客户机应用程序中要快得多


# --------------------------13.分组数据--------------------
# 本章涉及如何分组数据 主要是group by 与 having
select count(*) as num_date from stocks ;

select id ,count(*) as num_date from stocks;
-- 这事不能运行的 因为id是多行 而count(*)仅仅为1行

select id ,count(*) as num_date from stocks
group by id;
-- 用了group by以后是可以运行的

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


-- 这是按照农产品id来进行分类 列表
-- 这里的count(*)就从计算行数 变成了 计算vend_id不同时候的不同行数
-- 从原先的一组数据变成了 和vend_id一样多的数据了
-- 如果分组有null则视为一组 若有多个null 则多个null归为一组
-- group by___不允许用别名

# 过滤分组——除了分组之外还可以指定 包括哪些分组 排除哪些分组
 -- 过滤分组不能用where 因为where过滤行 而having过滤分组
-- having支持所有where支持的操作符
select id,count(*)
from stocks
group by id
having id<3 and count(*)<=2;
 -- 用having过滤 id<3 并且数量<=2的行
#  那么having与where的差别是？
-- 可以这么理解 where在分组前进行过滤 having在分组后进行过滤

# select vend_id,count(*) as num_prods
# from products
# where prod_price>=10
# group by vend_id
# having count(*)>=2;
-- 挑选出农作物价格大于10 的作物对vend_id进行分组，分组后留下count(*)大于等于2的农作物数据

-- 对于分组后的排序 不要仅仅依赖于group by自带的排序 尝试也给出order by 能保证数据的正确排序
select id , count(*)
from stocks
group by id
order by id desc; -- 像这样用order by加一层保险

# 所见总计订单价格大于等于50的订单的订单号和订单价格
# select order_num , sum(quantity*item_price) as order_total
# from orderitems
# group by order_num
# having sum(quantity*item_price) >=50
# order by ordertotal desc;

# --------------------------14.子查询------------------------
# 任何SQL语句都是查询 但是此术语一般指select语句
# SQL还允许创建子查询（subquery） 即嵌套在其他查询中的查询

use book_system;
drop table if exists cusmer_order;
create table if not exists consumer_order(
    `id` int not null auto_increment,
    `consumer_id` int default null,
    primary key(id)
);insert into consumer_order
  (consumer_id)
  values
  ('1001'),
  ('1002'),
  ('1003'),
  ('1004'),
  ('1005'),
  ('1006'),
  ('1007'),
  ('1008'),
  ('1009'),
  ('1010');

select * from consumer_order;

create table if not exists consumer(
    `id` int(4) not null auto_increment,
    `name` varchar(50) default null,
    `comsumer_address` varchar(50) default null,
    `consumer_num` int(4) default null,
    `consumer_tel` varchar(11) default null,
    `commodity_id` int(4) default null,
    primary key(id)
);
insert into consumer
( name, comsumer_address, consumer_num, consumer_tel, commodity_id)
values
('熊乾坤','广东深圳','110','15986776923','1001'),
('张三','广东珠海','123','15986776923','1002'),
('李四','广东佛山','1240','15986776923','1003'),
('王五','广西','1132','13s86776923','1004'),
('周综述','河南洛阳','110','15986776923','1005'),
('李时珍','北京','10','15986776923','1001'),
('唐河三','江苏','10','15986776923','1002'),
('朱桢','浙江','150','15986776923','1003'),
('杜氏耗','宁波','160','15986776923','1004'),
('李丽芬','武汉','410','15986776923','1005');
update consumer set id =1000+id;

select * from consumer;

create table if not exists commodity(
    `commodity_id` int(4) not null auto_increment,
    `commodity_price` int(10) default null,
    `commodity_name` varchar(50) default null,
    primary key(commodity_id)
);
select * from commodity;

insert into commodity
( commodity_price, commodity_name)
values
('500','键盘'),
('200','鼠标'),
('1200','显示器'),
('7500','笔记本'),
('6300','iphone11'),
('1200','耳机'),
('20','鼠标垫'),
('30','钢笔'),
('250','书包'),
('69','《MySQL必知必会》');
select * from commodity;

update commodity set commodity_id = commodity_id+1000;
create table if not exists order_count(
    `order_id` int not null auto_increment,
    `1001` int(1) default null,
    `1001_num` int(10) default null,
    `1002` int(1) default null,
    `1002_num` int(10) default null,
    `1003` int(1) default null,
    `1003_num` int(10) default null,
    `1004` int(1) default null,
    `1004_num` int(10) default null,
    `1005` int(1) default null,
    `1005_num` int(10) default null,
    primary key(order_id)


);
insert into order_count
(`1001`, `1001_num`, `1002`, `1002_num`, `1003`, `1003_num`, `1004`, `1004_num`, `1005`, `1005_num`)
values
('1','100','1','10' ,'1','500','0','0'  ,'1','30'),
('0','0'  ,'1','120','1','200','0','0'  ,'1','30'),
('0','0'  ,'1','140','1','10' ,'0','0'  ,'1','30'),
('0','0'  ,'1','160','1','100','0','0'  ,'1','30'),
('0','0'  ,'1','170','0','0'  ,'0','0'  ,'1','30'),
('0','0'  ,'1','180','0','0'  ,'0','0'  ,'1','30'),
('0','0'  ,'0','0'  ,'0','0'  ,'1','20' ,'1','30'),
('0','0'  ,'0','0'  ,'0','0'  ,'1','5'  ,'1','30'),
('1','100','0','0'  ,'1','200','1','60' ,'1','30'),
('1','100','0','0'  ,'1','30' ,'1','90' ,'1','30');

select name from consumer
where commodity_id = (

                        select commodity_id
                        from commodity
                        where commodity_name = '键盘'

                        );
-- 相当于把两个动作合并
# 第一个动作找到键盘的id号  第二个动作取consumer表里找商品id为第一次查找出来的 那些客户名
-- 可见where的嵌套能力很强 可以构造出很灵活的查找语句
-- 但是由于系统的运行速度不宜过长 因此嵌套不宜过多

select count(*) as orders
from consumer_order
where consumer_id = 1001;


# ---------------------15.联结表-----------------------------
#  MySQL最强大的功能之一就是查询时联结（join）表
-- 外键（foreign key）——外键为某个表的一列 它包含了另一张表的主键 定义了两张表的关系
-- 好处是 不用重复存储数据 就拿一个外键作为连接即可 有需要的话通过外键进入联接表去查询
-- 数据库的可伸缩性——能适应不断增加的工作量而不失效的性能。
-- 关系型数据库的可伸缩性就很不错。

select vend_name ,prod_name,prod_price
from vendrs, products
where vendors.vend_id = products.vend_id
order by vend_name,prod_name;
-- 联结时不要忘了where 不然的话出现笛卡尔积 会呈现表1行数×表2列数 个行数据

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

-- inner join...on...使用了这个内联结语法

#  连接多个表
select prod_name,vend_name,prod_price,quantity
from orderitems,products,vendors
where products.vend_id = vendors.vend_id
and orderitems.prod_id = products.prod_id
and order_num = 20005;
-- 联结的表越多 性能下降越厉害 要尽可能联结尽量少的表实现目标功能

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'
                                        )
                    );

/*
 编写一个 SQL 语句，获取球员 (players) 表中第二高的身高 (height)

表定义: players (球员表)

列名	类型	注释
id	int unsigned	主键
height	int	球员身高
 */
select ifnull(
            (
                select max(height) from players
                where height != (
                                select max(height) from players
                                )
                ),
                null

           ) as second_height;
方法二
select distinct max(height) as second_height from players
where height!= (
                select max(height) from players
                )  ;

/*
 -----------------------------------------------MySQL必知必会 p120 2021.11.16--------------------------------------------
 */
# --------------------------16.创建高级联结---------------------------
select prod_id ,prod_name
from products
where vend_id = (
                 select vend_id
                 from products
                 where prod_id ='DTNTR'
                ) ;
-- 子查询
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';
-- 联结查询
-- 对于自联结 因为存在二义性 所以通产都会取个别名 上例为 p1 ， p2
-- 用自联结而不用子查询 自联结的性能要优一点

select c.* ,o.order_num,o.order_date
oi.prod_id,oi.quantity,OI.item_price
from customers as c ,orders as o ,orderitems as oi
where c.cust_id = o.cust_id
and oi.order_num = o.order_num
and prod_id = 'FB';
-- 通配符* 只对第一个表作用


select customers.cust_id ,order.order_num
from customer
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;

-- outer外部联结 包括了还没有关联行的行
-- outer必须前面接left or right 表明左右

# 带有聚合函数的联结
select customers.cust_name ,
       customers.cust_id,
       count(orders.order_num) as num_ord
from customers inner join orders
on customers.cust_id = orders.cust_id
group by customers.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;

-- 应该总是提供联结条件 否则会出现笛卡尔积
-- 一个联结中可以包含多个表，甚至每个联结可以采用不同的连接类型

# ------------------------------17.组合查询-----------------
# 多数SQL查询只包含从一个或多个表中返回数据的单条select语句
# MySQL允许进行多个查询（多条select语句——称为  并 或 符合查询

-- 使用union来组合数条SQL查询，用union给出多条select语句，将结果组合成单个结果集
select name,new_price from stocks
where new_price>1000;
-- 找到股价高于100的股票

select name,new_price from stocks
where id between 5 and 10;
-- 找到id在5-10之间的股票

-- 组合上述两条语句
select name,new_price from stocks
where new_price>1000
union
select name,new_price from stocks
where id between 5 and 10;

-- 实际上他的意思就是
select name,new_price from stocks
where new_price > 1000
or id between 5 and 10;

-- 简单的例子里union似乎会比where复杂 但是涉及到多表查询 还是union更便捷
select name , id, consumer_num   from consumer
where id between 1005 and 1010
union
select name ,id ,new_price as price from stocks
where new_price >1000;

# union 规则
/*
 1.union必须由两条及以上select语句组成 并用union分割
 2.union中每个查询必须包含相同地列、表达式和聚集函数
 3.列数据类型必须兼容
 */
-- union是默认去重的比如第一条select出了4个结果 第二条select出了5个结果
# 而其中有一条结果是重复出现在第一次select和第二次select之间
# 则union去自动去掉那个重复的 打印出8条结果而不是9条
# 当然如果你希望重复的的结果出现 你可以使用 "union all" 此时出现9条结果

select id,name from stocks
where new_price < 300
union all
select id ,name from stocks
where id < 5; -- 9条结果（多条重复）

select id,name from stocks
where new_price < 300
union
select id ,name from stocks
where id < 5; -- 5条结果

-- 使用union all 不取消重复的行

-- 组合查询结果排序 仅使用一条order by语句 并且放在最后
select id,name,new_price from stocks
where new_price < 300
union all
select id ,name,new_price from stocks
where id < 5
order by new_price desc; -- 9条结果（多条重复）

-- union可以极大的简化复杂的where句子 简化从多个表的索引工作

# -----------------------------18.全文本搜索---------------
#  并非所有引擎都支持全文本搜索
#  两个最常用的引擎为 innodb && myisam
# myisam支持全文本搜索 innodb不支持全文本搜索★
-- 如果你应用中需要全文本搜索 使用myisam
# 通配符和正则表达式对所有行都进行检索，当行数不断增加，这些搜索可能非常耗时
# 使用通配符和正则表达式regexp很难明确控制匹配什么不匹配什么
#
# 以上这些通配符和正则表达式的限制可以用——全文本搜索来解决
# 全文本所搜可以不需要查看每个行，不需要分别分析和处理每个词
# MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。
# 这样，MySQL可以快速有效地决定那些词匹配，那些词不匹配，匹配的频率等


-- 启用全文本搜索支持
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;

insert into productnotes
( prod_id, note_date, note_text)
values
('1',now(),'华为技术有限公司是一家生产销售通信设备的民营通信
科技公司，总部位于中国广东省深圳市龙岗区坂田华为基地。
华为的产品主要涉及通信网络中的交换网络、传输网络、无
线及有线固定接入网络和数据通信网络及无线终端产品，为世
界各地通信运营商及专业网络拥有者提供硬件设备、软件、服
务和解决方案');

select * from productnotes;

-- 进行全文本搜索
select note_text
from productnotes
where Match(note_text) against('rabbit');
/*
 解释一下：此select语句检索单个列note_text 。由于where子句，一个全文本搜索被执行。
 match(note_text)指示MySQL针对指定的列进行搜索，
 against('tabbit')指定词rabbit作为搜索文本。由于有两行包含词rabbit
 这两个行被返回。
 注：全文本搜索不区分大小写 （除非用binary方式
 */

 -- 刚刚地搜索可以简单地使用like子句完成
select note_text
from productnotes
where note_text like '%rabbit%';

-- 前者的Match() against() 以文本匹配的良好程度进行排序
-- 全文本搜索的一个重要特性就是对结果进行排序。具有较高优先级地行先返回

-- 为了演示如何工作 可以这样
select note_text ,match(note_text) against('rabbit') as rank
from productnotes;

-- 可以知道 全文本搜索中 ： 文本中词靠前的行的等级比词靠后的行等级值高
# 因此 1.全文本搜索提供了like没有地功能（按匹配度排序） 2.全文本搜索还很快

# -----查询扩展功能
-- 先进行一个简单的例子：没有查询扩展功能的索引
select note_text
from productnotes
where match(note_text) against('anvils');-- 全文搜索anvils这个词

-- 这次使用查询扩展
select note_text
from productnotes
where match(note_text) against('anvils' with query expansion);
-- 从结果来看 扩展查询极大的增加了返回的行数，（有好有坏

-- 布尔文本搜索boolean mode
/*
 功能：
 1.要匹配的词
 2.要排斥的词（若某行包含这个词，则不返回
 3.排列提示（指定某些词的优先级更高，更重要
 4.表达式分组
 5.另外一些内容
 */
-- 即使没有定义fulltext() 我们也可以使用Boolean mode布尔搜索
select note_text
from productnotes
where match(note_text) against('华为' in boolean mode);

-- 匹配包含heavy但不包含任意以rope开始的词 可以用以下查询
select note_text
from productnotes
where match(note_text) against('heavy -rope*' in boolean mode);
#  -表示排除一个词 而*是截断操作符
/*
 + 包含，词必须存在
 - 排除，词必须不存在
 > 包含，而且增加等级值
 < 包含，且减少等级值
 () 把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等
 ~取消一个词的排序值
 *词尾的通配符
 "" 定义一个短语
 */
-- 举例
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('tabbit bait' in boolean mode);
-- 没有指定操作符 这个搜索匹配包含rabbit和bai中只杀一个词的行

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 <carrot' in boolean mode);
-- 匹配rabbit和carrot，增加前者的等级，降低后者的等级

select note_text
from productnotes
where  match(note_text) against('+safe +(<combination)' in boolean mode);
-- 搜素safe和combination 但是降低combination的等级

-- 最后重申：innodb myisam搜索引擎的区别：myisam支持全文本搜索

# --------------------------19.插入数据-----------------
insert into stocks
( name, stock_number, new_price, up_down, 我持有, date_time)
values
('宁德时代','30750','614.97','-0.81%','0',now());
-- 就这样在表中插入一行数据，也可以一次性插入多行
-- 一般不要使用没有明确给出列的列表的insert语句

create table if not exists custnew (
    `cust_id` int not null auto_increment,
    `cust_contact` varchar(50) default null,
    `cust_email` varchar(50) default null,
    `cust_name` varchar(50) default null,
    `cust_address` varchar(50) default null,
    primary key(cust_id)
);
create table if not exists customers (
    `cust_id` int not null auto_increment,
    `cust_contact` varchar(50) default null,
    `cust_email` varchar(50) default null,
    `cust_name` varchar(50) default null,
    `cust_address` varchar(50) default null,
    primary key(cust_id)
);
insert into custnew
(cust_contact, cust_email, cust_name, cust_address)
values
('无','1355818157','熊乾坤','广东深圳');
select * from custnew;
select * from customers;

insert into customers
(cust_id,cust_contact,cust_email,cust_name,cust_address)
select
cust_id,cust_contact,cust_email,cust_name,cust_address
from custnew;
-- 遍历一遍custnew 再把它所有行插入到custiomers里去


# -------------------20.更新和删除数据--------------------
-- 更新数据update table_name set ..
update customers set cust_email = '135581815@qq.com'
where cust_name = '熊乾坤';

-- 删除数据 delete from table_name where ..
delete from customers
where cust_id = 10006;
-- 删除 customers表内 cust_id为1006的那一行数据

-- 更快的删除 truncate table_name
truncate custnew; -- 这个操作会让custnew这张表所有数据归零
-- 要小心使用这个全表删除操作 因为MySQL没有撤销键（undo）

# --------------------------21.创建表---------------------
 -- null是默认设置 如果没有指定not null 一般默认值都是null
# 主键 primary key() 表中的主键必须唯一
create table if not exists new_table(
    `table_id`       int         not null auto_increment,
    `table_name`     varchar(20) not null,
    `table_price`    int(10)     not null,
    `table_quantity` int         not null,
     primary key(table_id)
)engine=innodb;
-- 由于主键为其值唯一标识表中每个行的列 因此主键中 必须not null
-- 反之，允许null值的不能作为唯一标识，即不能作为主键

-- auto_increment自增，它告诉MySQL本列每当增加一行时，该列数值自动＋1
-- 这样可以给每个行分配一个唯一的cust_id（注：每张表只允许一个auto_increment列存在，且它必须被索引（使他称为主键

-- 有关引擎
/*
 1.innodb可靠的事务处理引擎，但不支持全文搜索
 2.memory功能等同myisam，但由于数据存储在内存中（不是磁盘），速度很快（适用临时表
 3.myisam 性能极高，支持全文搜索，但不支持事务（transaction）处理
 */

alter table vendors
add column vend_phone char(20) after vend_id;-- 给表增加一行数据

alter table vendors
drop column vend_phone; -- 删除一列
-- 谨慎使用alter table 操作之前做好备份

-- 重命名表
rename table customers to customers1;

# --------------------------22.使用视图---------------------

create view course_teacher as
    select teacher.num , courses.name,teacher.teacher_email
from teacher , courses
where teacher.num = courses.teacher_id ;
-- 创建了一个联合courses 与 teacher 双表的视图

select num,name,teacher_email
from course_teacher;
-- 用select语句查询视图
-- 视图极大的简化了SQL语句的使用。 利用视图可以一次性编写基础的SQL，然后根据需要多次使用


-- 视图的另一个重点是重新格式化检索出的数据
select concat(rtrim(name) , '(',rtrim(teacher_email),')')
as info
from course_teacher
order by num;
-- 利用视图把课程名字和老师邮箱绑定，简化了SQL语句，提高了性能

-- 用视图过滤不想要的数据
create view courseInfo_view as
    select courses.name as course_name, teacher.name as teacher_name,teacher.teacher_email as teacher_email
from courses , teacher
where teacher.teacher_sex = 1 -- 视图过滤的语法和查询一致
and courses.id = teacher.num -- 不加限制条件会得到 nxm个重复数据
;

drop view if exists courseInfo_view;

select * from courseInfo_view;

-- 视图的更新 insert update delete(少用
-- 一般来说视图用来检索（select） 而不是用于更新（update delete insert）

-- 视图就是虚拟的表 它包含的不是数据而是根据需要检索数据的查询


/*
 -----------------------------------------------MySQL必知必会 p175 2021.11.17--------------------------------------------
 */
# ----------------------------------23.使用存储过程-------------------------
call productpricing(
                    @pricelow,
                    @pricehigh,
                    @priceaverage
                    );
-- 创建存储过程 一个返回产品平均价格的存储过程
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 p1 decimal(8,2),
    out ph decimal(8,2),
    out pa decimal(8,2)
)
begin
    select Min(prod_price)
        into p1
    from products;

    select max(prod_price)
        into ph
    from products;

    select avg(prod_price)
        into pa
    from products;
end;


create procedure productpricing(
    out plow decimal(8,2),
    out phigh decimal(8,2),
    out paverage decimal(8,2)
)
begin
    select min(new_price)
        into plow
    from stocks;

    select max(new_price)
        into phigh
    from stocks;

    select avg(new_price)
        into paverage
    from stocks;

end;

call productpricing();
-- 此存储过程接受三个参数plow、phigh、paverage
# 关键字out指出相应的参数用来从存储过程传出一个值（返回给调用者）
# MySQL支持in（传递给存储过程） 、out（从存储过程种传出，如这里所用）、inout（对存储过程传入和传出）
# 存储的过程代码位于 begin与end语句内，内部是一系列select语句（上面是三个select）

call productpricing(
                    @pricelow,
                    @pricehigh,
                    @priceaverage
    );

-- 为了检索出的产品平均价格
select @phigh;

-- 为了获得三个值
select @phigh,@plow,@paverage;

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

call ordertotal(200005,@total);


-- name:ordertotal
-- prameters: onumber = order number
--            taxable = 0 if not taxable, 1 if taxable
--            ototal = order total variable

create procedure ordertotal (
    in onumber int,
    in taxable boolean,
    out ototal decimal(8,2)

)comment 'obtain order total,optionally adding tax'
begin
    -- declare variable for total
    declare total decimal(8,2);
    -- declare tax percentage
    declare taxrate int default 6;

    -- get the order total
    select sum(item_price*quantity)
    from orderitems
    where order_num = onumber
    into total;

    -- is this taxable ?
    if taxable then
    -- yes, so add taxrate to the total
        select total+(total/100*taxrate) into total;

    end if;
    -- and finally ,save to out variable
    select total into ototal;
end;

# taxable布尔值 如果要加税 则taxable为真 反之为假

# ---------------------------24.使用游标-------------------------
# 有时，需要在检索出来的行中前进或后退一行或多行，这时候可以用游标（cursor）

-- 创建游标
create procedure processorders()
begin
    declare stock_info cursor
    for
    select name,new_price from stocks;
end;
-- 用declare语句来定义和命名游标

-- 打开和关闭游标
open stock_info;

close stock_info;
-- 如果你不明确关闭游标,MySQL会在到达end时自动关闭它



# --------------------------------25.使用触发器--------------------------
-- MySQL语句在被需要时执行,存储过程也是如此.但是如果你要某条语句(某些)在事件发生时自动执行,怎么办呢?
/*
    例如:
    1.每增加一个顾客到表中,都检查其电话号码格式是否正确/州的缩写是否为大写
    2.每订购一个产品,都从库存数量中减去订购的数量
    3.无论何时删除一行,都在某个存档表中保留一个副本.

 */
# 以上的例子都需要某个表发生更改时,自动处理. 确切的说,就是触发器 ---
# 触发器是MySQL响应以下任一语句而自动执行的一条MySQL语句
# 1.delete 2.insert 3.update(其他的语句不支持触发器)

-- 创建触发器
/*
    创建触发器,需要给出4条信息
    1.唯一的触发器名字
    2.触发器关联的表
    3.触发器应该响应的活动(delete/update/insert)
    4.触发器何时执行(处理之前或之后
 */
-- 触发器用create trigger语句创建
create trigger newproduct after insert on products
    for each row select 'Product added';
-- 以上句子:创建了一个名为newproduct的新触发器.after insert表明触发器在执行insert之后开始执行
-- 这个触发器还指定了each row,因此代码对每个插入行都执行.
-- 在这个例子中,文本'Product added'将对每个插入的行显示一次

-- 另外:只有表才支持触发器 视图不支持触发器(每个表最多有6个触发器,分别是每条insert/update/delete的之前与之后

-- 如果before触发器失败,则MySQL将不执行请求的操作 ,并且也不会执行after触发器(如果有的话



-- 触发器的删除
drop trigger newproduct; -- drop trigger + trigger_name
-- 触发器不可修改和重写 所以应该删除它 再重新创建


# --------------------------------26.管理事务处理--------------------
-- 并非所有引擎都支持事务transaction处理
-- innodb支持事务 但没有全文搜索
-- myisam不支持事务 担有全文搜索

-- 事务处理保证了某一行数据的完整性,要么完成,要么不完成 没有完成一半的说法 ,这就是事务处理的特点

/*
 关于事务处理的术语
 事务transaction   指一组SQL语句
 回退rollback      指撤销指定SQL语句的过程
 提交commit        指将未存储的SQL语句结果写入数据库表
 保留点savepoint   指事务处理中设置的临时占位符(place-holder),你可以对他发布回退

 */

select * from stocks;

start transaction; -- 开始事务
delete from stocks; -- 删干净这张表
select * from stocks; -- 一张没有数据的表
rollback ; -- 回退上述操作
select * from stocks; -- 数据又回来了
#  显然rollback只能在一个事务处理内使用(执行一条start transaction后)
# 哪些语句可以回退? insert update delete
# 但是不能回退select(也没有意义) ,不能回退create 或 drop

-- 使用commit
start transaction ; -- 开始事务
delete from orderitems where order_num = 20010; -- 删掉符合条件的行
delete from orders where order_num = 20010; -- 删掉符合条件的行
commit; -- 提交你的操作
# 在这个例子中 系统中完全删除订单20010 .因为涉及到两张表orders/orderitems,因此我们使用食物来保证订单不被部分删除
#

#  隐含事务关闭 当commit 或 rollback语句执行之后,事务会自动关闭


-- 为了支持回退部分事务处理,必须能在事务处理块中合适的位置放置占位符.这样如果需要回退,可以回退到某个占位符.
-- 这些占位符称为保留点.

-- 以下是创建占位符
savepoint delete1;
# 每个保留点都取标识它的唯一名字,以便在回退时,MySQL知道要回退到何处.为了回退到本例给出的保留点,可如下进行.
rollback to delete1;
-- 保留点越多越好,回退越细腻
-- 释放保留点 ,保留点在事务处理完成(执行一条rollback或commit后自动释放.
-- 也可以用release savepoint 明确地释放保留点

# 更改默认提交行为
# 为指示MySQL不自动提交更改,需要用以下语句
set autocommit=1; -- 默认为1(自动提交 -- autocommit标志为连接专用,而不是服务器


# -----------------------------------27.全球化和本地化---------------------
show character set;

# --------------------------28.安全管理------------------------
# --------------------------29.数据库维护------------------------
# --------------------------30.改善性能------------------------

# 你的select有一系列复杂的or条件吗？如果有的话尝试union 也许会得到极大的性能改善
# 不要过度使用select * from （如果你真的需要每个列地话
# like索引很慢
# 总有不止一种MySQL语句能得到目的
# 最重要的规则是：每条规则在某些条件下都会被打破