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

# 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);
复制代码