MySQL 从入门到实践，万字详解！

# 创建mysql容器
docker run -d -p 3307:3306 -e MYSQL_ROOT_PASSWORD=888888 \
-v /Users/sherlocked93/Personal/configs/mysql.d:/etc/mysql/conf.d \
-v /Users/sherlocked93/Personal/configs/data:/var/lib/mysql \
--name localhost-mysql mysql

# 创建好之后进入 mysql 容器：
docker exec -it localhost-mysql bash

# 登录 mysql
mysql -u root -p888888

mysql -u root -p      # –h 主机名 –u 用户名 -P 端口号 –p密码，注意-p跟密码之间不能加空格其他可以加可以不加
select version();     # 查看 mysql 服务版本
show databases;       # 查看所有数据库，注意最后有 s

create database [库名]; # 创建库
use [库名];             # 打开指定的库

show tables;           # 查看当前库的所有表        
show tables from [库名];   # 查看其他库的所有表               
desc [表名];               # 查看表结构   

create table [表名] (      # 创建表            
    列名 列类型,
    列名 列类型,
);

drop database [库名];     # 删除库  
drop table [表名];        # 删除表   

exit;                    # 退出

mysql -u root -p888888     # 输入用户名密码，进入mysql命令行

# 基本语法
select [查询列表] from [表名];

# 查询单个/多个/所有字段
select cust_name from customers;
select cust_name,cust_city,cust_address from customers;
select `select` from customers;               # 如果某字段是关键字，可以用 ` 符号包起来
select * from customers;                      # * 表示所有                

# 查询常量值/表达式/函数
select 100;
select 'zhangsan';
select 100%98;
select version();

select order_num from orderitems;           # 有一些重复值
select distinct order_num from orderitems;  # 将重复值去重

select orderitems.order_num from mysql_demo1.orderitems;
# 上面这句等价于
select order_num from orderitems;

select * from orderitems order by item_price;           # 按item_price升序排列

# 先按 quantity 升序排列，quantity 的值一样时按 item_price 的值升序排列
select * from orderitems order by quantity,item_price;  

# 先按 quantity 降序排列，quantity 的值一样时按 item_price 的值升序排列
select * from orderitems order by quantity desc,item_price;  

# 找到最贵订单
select * from orderitems order by item_price desc limit 1;

# 基本语法
select [查询列表] from [表名] where [筛选条件] order by [排序条件];

# 找出产品价格为 2.5 的产品名字
select prod_name, prod_price from products where prod_price=2.5;

# 找出产品价格小于等于 10 的产品名字，并按产品价格降序排列
select prod_name, prod_price from products where prod_price <= 10 order by prod_price desc;

# 找到供应商 id 不为 1003 的产品，!= 和 <> 含义一样，都是不等于
select vend_id, prod_name from products where vend_id <> 1003;

# 查询产品价格在 3 到 10 内的产品
select prod_name, prod_price from products where prod_price between 3 and 10;

# 单独使用 and 也可以打到这个效果
select prod_name, prod_price from products where prod_price <= 10 and prod_price >= 3;

# 找出顾客中邮箱不是 null 的顾客信息
select * from customers where cust_email is not null;

# 使用安全等于号来判断 null 也是可以的
select * from customers where cust_email <=> null;

# 找出供应商为 1003 提供的价格小于等于 10 的产品
select * from products where vend_id = 1003 and prod_price <= 10;

# 找出id为 1003 或 1001 的供应商
select * from products where vend_id = 1003 or vend_id = 1001;

select * from products where vend_id = 1001 or vend_id = 1003 and prod_price >= 10;

select * from products where (vend_id = 1001 or vend_id = 1003) and prod_price >= 10;

# 找出id为 1003 或 1001 的供应商
select * from products where vend_id in (1001, 1003);

# 找出id不为 1001 1003 的产品
select * from products where vend_id not in (1001, 1003);

# 选择产品价格不在 5 到 15 之间的产品
select * from products where prod_price not between 5 and 15;

# 找出产品名字以 jet 开头的产品
select * from products where prod_name like 'jet%';

# 找出产品名中含有 on 的产品
select * from products where prod_name like '%on%';

# 找出产品名以 s 开头，以 e 结束的产品
select * from products where prod_name like 's%e';

select * from products where prod_name like '_ ton anvil';

# 找到描述中有 % 的产品
select * from products where prod_desc like '%\%%';

# 找到产品名以 1000 或 anvil 结尾的产品
select * from products where prod_name regexp '1000|anvil$';

# 找到产品名以 . 字符开头的产品
select * from products where prod_name regexp '^\\.';

# 使用 as 关键字
select cust_name as name from customers;

# as 关键字也可以直接省略
select cust_name name from customers;

# 可以给不同字段分别起别名
select cust_name name, cust_city location from customers;

# 将供应商的名字和地点拼接好后返回，并命名为 vend
select concat(vend_name, '(', vend_country, ')') vend from vendors;

# 将顾客信息拼接起来
select concat(cust_name, '(', ifnull(cust_email, '-'), ')') customerInfo from customers;

# 将顾客信息处理后拼接起来
select concat(rtrim(vend_name), '(', trim(vend_country), ')') vend from vendors;

# 计算订单每种总额，并按照总金额降序排列
select prod_id as id, quantity, quantity*item_price as totalPrice 
from orderitems order by totalPrice desc;

# upper、lower 将姓变大写，名变小写，然后拼接
select concat(upper(last_name), lower(first_name)) 姓名 from employees;

# 姓名中首字符大写，其他字符小写然后用_拼接，显示出来
select concat(upper(substr(last_name, 1, 1)), '_', lower(substr(last_name, 2))) from employees;

# substr 截取字符串，sql 中索引从 1 开始而不是0
select substr('hello world', 3);      # llo world
select substr('hello world', 2, 3);   # ell

# instr 返回子串第一次出现的索引，没找到返回 0
select instr('abcdefg', 'cd');        # 3

# trim 减去字符串首尾的空格或指定字符
select trim('   hello    ');              # hello
select trim('aa' from 'aaabaabaaaaaa');   # abaab

# lpad 用指定的字符实现左填充指定长度
select lpad('he', 5, '-');   # ---he

# rpad 用指定的字符实现左填充指定长度
select rpad('he', 5, '-*');   # he-*-

# replace 替换
select replace('abcabcabc', 'bc', '--');   # a--a--a--

# round 四舍五入，第二个参数是小数点后保留的位数
select round(-1.55);      # -2
select round(1.446, 2);   # 1.45

# ceil 向上取整
select ceil(1.001);   # 2
select ceil(1.000);   # 1
select ceil(-1.001);  # -1

# floor 向下取整
select floor(1.001);   # 1
select floor(1.000);   # 1
select floor(-1.001);  # -2

# truncate 小数点后截断几位
select truncate(1.297, 1);  # 1.2
select truncate(1.297, 2);  # 1.29

# mod 取余和%同理，符号与被除数一样
select mod(10, -3);  # 1
select mod(-10, -3); # -1
select mod(-10, 3);  # -1
select 10%3;         # 1

# now 返回当前系统日期和时间
select now();    # 2020-07-08 12:29:56

# curdate,current_date 返回当前系统日期，不包括时间
select curdate();  # 2020-07-08

# curtime,current_time 返回当前时间，不包括日期
select curtime();  # 12:29:56

# year... 获取时间指定部分，年、月、日、小时、分钟、秒
select year(now());      # 2020
select month(now());     # 7
select monthname(now()); # July
select day(now());       # 8
select dayname(now());   # Wednesday
select hour(now());      # 12
select minute(now());    # 29
select second(now());    # 56
select month(order_date) from orders;

# str_to_date 将日期格式的字符转换成指定格式的日期
select str_to_date('1-9-2021', '%d-%c-%Y');    # 2020-09-01
select * from orders where order_date = str_to_date('2005-09-01', '%Y-%m-%d');

# date_format 将日期转换成指定格式的字符
select date_format(now(), '%Y年%m月%d日');   # 2020年09月01日
select order_num orderId,date_format(order_date, '%Y/%m') orderMonth from orders;

# 计算产品价格平均值
select avg(prod_price) as avgPrice from products;

# 计算供应商id为 1003 提供的产品的平均价格
select avg(prod_price) as avgPrice from products where vend_id = 1003;

# 计算价格最大的产品价格
select max(prod_price) as maxPrice from products;

# 计算顾客总数
select count(*) from customers;

# 计算具有 email 的顾客数
select count(cust_email) from cutomers;

# 计算产品价格总和
select sum(prod_price) from products;

# 计算订单为 20005 的订单总额 
select sum(item_price * quantity) totalPrice from orderitems where order_num = 20005;

# 计算产品具有的不同的价格的平均数
select avg(distinct prod_price) avgPrice from products where vend_id = 1003;

# 同时计算产品总数、价格最小值、最大值、平均数
select count(*) prodNums, min(prod_price) priceMin, max(prod_price) priceMax, avg(prod_price) priceAvg from products;

# 分别查询每个供应商提供的产品种类数
select vend_id, count(*) num_prods from products group by vend_id;

# 查询每个供应商提供的产品的平均价格
select vend_id, avg(prod_price) avg_price from products group by vend_id;

# 语法顺序
select [查询列表] from [表名] where [筛选条件] group by [分组列表] having [分组后筛选] order by [排序列表] limit [要检索行数];

# 找到提供大于 2 个产品的供应商，并列出其提供的产品数量，这里使用 having 来过滤掉产品数不大于2的供应商
select vend_id, count(*) prodCount from products group by vend_id having prodCount > 2;

# 找到供应商提供的商品平均价格大于 10 的供应商，并且按平均价格降序排列
select vend_id, avg(prod_price) avgPrice from products group by vend_id having avgPrice > 10 order by avgPrice desc;

# 首先在 orderitems 表中找到产品 TNT2 对应的订单编号
select order_num from orderitems where prod_id = 'TNT2'

# 然后在 orders 表中找到订单编号对应的顾客 id
select cust_id from orders where order_num in (
  select order_num from orderitems where prod_id = 'TNT2');
  
# 然后去 customers 表中找到顾客 id 对应的顾客名字
select cust_id, cust_name from customers where cust_id in ( 
  select cust_id from orders where order_num in (  
    select order_num from orderitems where prod_id = 'TNT2'));

# 首先找到用户 ID 对应的订单数量
select count(*) from orders where cust_id = 10003;

# 然后将其作为一个 select 子句，将用户 id 
select cust_name, cust_state, (
  select count(*) from orders where orders.cust_id = customers.cust_id) as order_count 
  from customers order by order_count desc;

# 列出产品的供应商及其价格
select vend_name, prod_name, prod_price from vendors, products 
where vendors.vend_id = products.vend_id order by prod_price desc;

# 返回两个表长度乘积行
select vend_name, prod_name, prod_price from vendors, products;

# 将订单 20005 的产品信息、订单信息、供应商信息找出来
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;

# 使用联结来实现 9.1 的例子
select customers.cust_id, cust_name
from orders, customers, orderitems
where orders.order_num = orderitems.order_num
  and customers.cust_id = orders.cust_id
  and prod_id = 'TNT2';                                # 由于三个表中只有一个表有 prod_id，所以不需要限定表名

# 把前面这个句子起别名
select c.cust_id, cust_name
from orders o, customers c, orderitems oi
where o.order_num = oi.order_num
  and c.cust_id = o.cust_id
  and prod_id = 'TNT2';

# 列出产品的供应商及其价格
select vend_name, prod_name, prod_price
from vendors
         inner join products
                    on vendors.vend_id = products.vend_id;

# 先找到产品 ID 为 TNT1 的供应商 ID，然后找到对应供应商 ID 提供的产品列表
select prod_id, prod_name, vend_id
from products
where vend_id in (
    select vend_id
    from products
    where prod_id = 'TNT1'
);

# 自联结
select p1.prod_id, p1.prod_name, p1.vend_id
from products p1,
     products p2
where p1.vend_id = p2.vend_id
  and p2.prod_id = 'TNT1';

# 自选择唯一的通配符只对第一个表使用。所有其他列明确列出，所以没有重复的列被检索出来。
select v.*, p.prod_id
from vendors v,
     products p
where v.vend_id = p.vend_id;

# 内部联结，查找用户对应的订单
select c.cust_id, o.order_num
from customers c
         inner join orders o
                    on c.cust_id = o.cust_id;
                    
# 左外部联结，将没有下单过的顾客行也列出来
select c.cust_id, o.order_num
from customers c
         left outer join orders o
                         on c.cust_id = o.cust_id;
                         
# 右外部联结，列出所有订单及其顾客，这样没下单过的顾客就不会被列举出来
select c.cust_id, o.order_num
from customers c
         right outer join orders o
                          on c.cust_id = o.cust_id;

# 找到每个顾客所下订单的数量，并降序排列
select c.cust_id, c.cust_name, count(o.order_num) count_orders
from customers c
         left outer join orders o on c.cust_id = o.cust_id
group by c.cust_id
order by count_orders desc;

# 比如需要列出商品价格小于等于 10 而且是供应商 ID 为 1005 或 1003 的产品信息
select prod_id, prod_name, prod_price, vend_id from products where prod_price <= 10
union
select prod_id, prod_name, prod_price, vend_id from products where vend_id in (1005, 1003);

# 实际上这句也可以通过 where 语句代替
select prod_id, prod_name, prod_price from products where prod_price <= 10 or vend_id in (1005, 1003);

# 不去重重复行
select prod_id, prod_name, prod_price, vend_id from products where prod_price <= 10
union all
select prod_id, prod_name, prod_price, vend_id from products where vend_id in (1005, 1003);

# 插入一条数据到顾客表中
insert into customers
values (null, 'Zhang San', '001 street', 'ShangHai', 'SH', '666666', 'ZH', null, null);

# 安全但繁琐的插入方式
insert into customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
values ('Zhang San', '001 street', 'ShangHai', 'SH', '666666', 'ZH', null, null);

# 插入多个行
insert into customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
values ('Zhang San', '001 street', 'ShangHai', 'SH', '666666', 'ZH', null, null),
       ('Li Si', '002 street', 'BeiJing', 'BJ', '878787', 'ZH', null, '123123@163.com');

# 从别的表中找出数据，并插入 customers 表中
insert into customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
select id, name, address, city, state, zip, country, contact, email
from custnew;

# 更新 id 为 10005 的用户的信息
update customers set cust_email = '888@qq.com' where cust_id = 10005;

# 更新多个字段
update customers
set cust_email   = '666@qq.com',
    cust_contact = 'S Zhang'
where cust_id = 10005;

# 删除顾客表中顾客 id 为 10008 的行
delete from customers where cust_id = 10008;

CREATE TABLE 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 DEFAULT 'ZH',  # 指定默认值
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)          # 指定主键
) ENGINE=InnoDB;

# 给供应商表增加一列 vend_phone 字段
alter table vendors
    add vend_phone char(20) default 12212341234;
    
# 删除这个添加的 vend_phone 字段
alter table vendors
    drop column vend_phone;

# 将订单信息表的 order_num 设置为订单表的外键
alter table orderitems
    add constraint fk_orderitems_orders foreign key (order_num) references orders (order_num);

# 直接删除外键行报错，不允许删
delete from orders where order_num = 20009;

# 先删除 orderitems 中的关联行再删除 orders 中的外键行，就可以删了
delete from orders where order_num = 20009;
delete from orderitems where order_num = 20009;

drop table customers2;

# 删除一个表，如果没加 if exists 表又不存在则会报错
drop table if exists customers2;

# 重命名一个表
rename table customers to customers2;

# 重命名多个表
rename table customers to customers2,
             vendors to vendors2;

# 找到购买了 TNT2 的顾客信息
select cust_name, cust_contact, cust_email, prod_id
from customers c,
     orders o,
     orderitems oi
where c.cust_id = o.cust_id
  and o.order_num = oi.order_num
  and prod_id = 'TNT2';

# 创建视图
create view prod_cust as
select cust_name, cust_contact, cust_email, prod_id
from customers c,
     orders o,
     orderitems oi
where c.cust_id = o.cust_id
  and o.order_num = oi.order_num;
  
# 使用视图查询购买了产品 TNT2 的顾客信息
select cust_name, cust_email from prod_cust
where prod_id = 'TNT2';

create view prod_cust  as ...;       # 创建视图
show create view prod_cust;          # 查看创建视图的语句
drop view prod_cust;                 # 删除视图
create or replace view prod_cust  as ...;     # 更新视图

# 显示当前database中不包括视图的所有基表
select table_name
from information_schema.tables
where table_schema = 'mysql_demo1'
  and table_type = 'BASE TABLE';

# 显示当前database中的所有视图
select table_name
from information_schema.tables
where table_schema = 'mysql_demo1'
  and table_type = 'VIEW';

# 经常用到的供应商信息，可以先组装成视图
create or replace view vend_infos as
select vend_id, concat(vend_name, '(', vend_city, ', ', vend_country, ')') vend_info
from vendors;

# 使用视图直接拿到拼好的供应商信息
select prod_id, prod_name, vend_info
from products, vend_infos
where products.vend_id = vend_infos.vend_id;

# 找到 email 不是 null 的顾客下的订单
select order_num, cust_name, cust_email
from customers, orders
where customers.cust_id = orders.cust_id
  and cust_email is not null;

# 创建邮箱地址不为 null 的顾客的视图
create view cust_has_email as
select * from customers
where cust_email is not null;

# 找到 email 不是 null 的顾客下的订单
select order_num, cust_name, cust_email
from cust_has_email c, orders o
where c.cust_id = o.cust_id;

# 找到 email 不是 null 的顾客购买的所有商品列表
select c.cust_name, c.cust_email, c.cust_name, prod_name
from cust_has_email c, orders o, orderitems oi, products p
where c.cust_id = o.cust_id
  and oi.order_num = o.order_num
  and p.prod_id = oi.prod_id;

# 查找 20008 订单的订单总额
select order_num, sum(quantity * item_price) sum_price
from orderitems
where order_num = 20008;

# 查找订单总额视图
create or replace view sum_orders as
select order_num, sum(quantity * item_price) sum_price
from orderitems group by order_num;

# 找另一个订单的总金额
select order_num, sum_price
from sum_orders
where order_num = 20009;

# 比如给上面的 email 不为 null 的视图添加一行数据，插入会成功
insert into cust_has_email(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
values (10010, 'Zhang San', '001 street', 'ShangHai', 'SH', '9999', 'ZH', 'Li S');

# 给查找订单总额视图增加一行会失败，因为这里有分组，数据库不知道在哪插入
insert into sum_orders(order_num, order_item, prod_id, quantity, item_price)
values (20009, 5, 'OL1', 2, 8.99);

# 创建一个计算平均价格的存储过程
create procedure product_pricing(vend_id int)
begin
    select avg(prod_price) as priceaverage
    from products
    where products.vend_id = vend_id;
end;

# 查看创建存储过程的语句
show create procedure product_pricing;

# 调用存储过程查询平均价格
call product_pricing(1002);

# 删除存储过程    
drop procedure product_pricing;

# 先检查再删除
drop procedure if exists product_pricing;

# 存储过程输入输出参数
create procedure product_pricing(
    in vend_id int,
    out min_price decimal(8, 2),
    out max_price decimal(8, 2),
    out avg_price decimal(8, 2)
)
begin
    select min(prod_price)
    into min_price
    from products where products.vend_id = vend_id;
    select max(prod_price)
    into max_price
    from products where products.vend_id = vend_id;
    select avg(prod_price)
    into avg_price
    from products where products.vend_id = vend_id;
end;

# 调用存储过程查询产品平均价格
call product_pricing(1002, @minprice, @maxprice, @avgprice);

# 查询刚刚输出的变量
select @minprice, @maxprice, @avgprice;

# 计算指定订单号的总价格，并输出到变量中
create procedure order_pricing(
    in order_num int,
    out total_price decimal(8, 2)
)
begin
    select sum(quantity * item_price)
    into total_price
    from orderitems
    where orderitems.order_num = order_num;
end;

# 计算订单 20005 的总价
call order_pricing(20005, @totalprice);

# 查询总价
select @totalprice;

# 首先为了方便后面计算订单总金额，创建一个查询订单总金额的视图
create or replace view sum_order_price as
select sum(quantity * item_price) as total_price, order_num
from orderitems group by order_num;

# 总商品数量 3 件 8 折，4 件 7 折,计算折扣后的产品价格，
create procedure total_discount_price(
    in order_num int,
    out discount_price decimal(8, 2)
) 
begin
    # 创建一个变量保存商品总数
    declare prod_count int;

    # 计算该订单号的商品总件数
    select sum(quantity)
    into prod_count
    from orderitems where orderitems.order_num = order_num;

    # 小于 3 件无折扣
    if prod_count < 3 then
        select total_price
        into discount_price
        from sum_order_price o
        where o.order_num = order_num;
    # 等于 3 件 8 折
    elseif prod_count = 3 then
        select total_price * 0.8
        into discount_price
        from sum_order_price o
        where o.order_num = order_num;
    # 大于等于 3 件 7 折
    else
        select total_price * 0.7
        into discount_price
        from sum_order_price o
        where o.order_num = order_num;
    end if;
end;

# 调用存储过程查询折扣后的金额
call total_discount_price(20005, @discountprice);

select @discountprice;

drop procedure if exists process_orders;

# 使用游标将每个订单的实际价格填写到一个表中
create procedure process_orders()
begin
    # 定义本地变量
    declare o_num int;                    # 循环中的订单号变量
    declare d_price decimal(8, 2);        # 循环中的实际价格变量
    declare done boolean default false;   # 终止条件布尔值

    # 定义游标
    declare order_numbers cursor for
        select order_num from orders;

    # 终止条件，当没有更多行供循环时满足 not found，此时给 done 赋值 true
    declare continue handler for not found set done = true;

    # 没有则创建一个新的表，用来存订单的实际价格
    create table if not exists ordertotals
    (
        order_num   int,
        order_price decimal(8, 2),
        primary key (order_num)
    );

    open order_numbers;         # 打开游标

    # 在循环中依次给表 ordertotals 填充行
    repeat
        fetch order_numbers into o_num;
        call total_discount_price(o_num, d_price); 
        insert into ordertotals(order_num, order_price)
        values (o_num, d_price);
    until done end repeat;              # 满足 done 为真值则跳出循环

    close order_numbers;
end;

call process_orders();

select * from ordertotals;

# 语法
create trigger [触发器名]
[after/before] [insert/update/delete] on [表名]
for each row 
begin
 [sql语句]
end;

# 删除
drop trigger [if exists] [触发器名];

# 创建一个触发器，在新的产品插入时给临时变量赋值
create trigger newproduct
    after insert
    on products
    for each row select 'Product added' into @newprod;

# 插入一条语句试试
insert into products(prod_id, vend_id, prod_name, prod_price, prod_desc)
values ('XP2000', 1005, 'JetPack 200', 55, 'JetPack 200, multi-use');

select @newprod;

# 插入用户后获取这个新用户自动生成的的 ID 并且赋值给临时变量
create trigger newcust
    after insert
    on customers
    for each row select new.cust_id
                 into @newcust_after;

# 插入用户
insert into customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
values ('Zhang San', '001 street', 'ShangHai', 'SH', '666666', 'ZH', null, null);

# 查看新值
select @newcust_after;

drop trigger if exists deletecustomer;

# 创建触发器，当从顾客表中删除时将删除的数据插入到另一个存档表中
create trigger deletecustomer
    before delete on customers for each row
begin
    insert into customers2(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
    values (old.cust_id, old.cust_name, old.cust_address, old.cust_city, old.cust_state, old.cust_zip, old.cust_country, old.cust_contact, old.cust_email);
end;

# 删除刚刚创建的顾客数据
delete from customers where cust_id = 10013;

# 查询一下存档表中的顾客数据是否存在
select * from customers2;

drop trigger if exists updatecustomer;

# 使用触发器，将每次更新的 cust_country 转化为大写
create trigger updatecustomer
    before update
    on customers
    for each row
begin
    set new.cust_country = upper(new.cust_country);
end;

# 更改数据
update customers set cust_country ='zh' where cust_id = 10005;

# 开始事务
start transaction; 
delete from customers where cust_id > 10005;   # 删除几个行
select * from customers;

# 使用 rollback 回滚 delete 语句
rollback;

# 删除订单详情表总中 20007 相关订单详情，再删除订单表中的 20007
start transaction;
delete from orderitems where order_num = 20007;
delete from orders where order_num = 20007;
commit;

# 创建保留点
savepoint sav1;

# 回滚到保留点
rollback to sav1;

# 查看用户列表
use mysql;
select user from user;

# 创建用户及其密码
create user zhangsan identified by '888888';

# 更改用户名
rename user zhangsan to lisi;

# 删除用户
drop user if exists lisi;

# 显示用户张三的权限
show grants for zhangsan;

# 设置权限，允许张三在 mysql_demo1 库上使用 select
grant select on mysql_demo1.* to zhangsan;

# 撤销权限，撤销张三在 mysql_demo1 库上使用 select 的权限
revoke select on mysql_demo1.* from zhangsan;