mysql的学习

p29  cross join(笛卡尔积)有显示与隐式
p30   union 联合起来的列数要相同，且列名取决于第一个select

SELECT  cus.customer_id,
cus.first_name,
cus.points,
'Bronze' AS type
FROM sql_store.customers cus
where cus.points<1000
union
SELECT  cus.customer_id,
cus.first_name,
cus.points,
'Sliver' AS type
FROM sql_store.customers cus
##where cus.points<2000 and cus.points>=1000
where points between 1000 and 2000
union
SELECT  cus.customer_id,
cus.first_name,
cus.points,
'Gold' AS type
FROM sql_store.customers cus
where cus.points>=2000
order by first_name

使用where查询时，小的值放前面，列如：between 1000 and 2000

获取最新插入的数据的id   LAST_INSERT_ID()

create table 'name' AS
select * from 'table'
USE sql_invoicing;
##create table invoices_archived AS
insert into invoices_archived
select *
from invoices ies
left join clients cs
using (client_id)
where ies.payment_date is not null;

insert into 'tablename'
select * from 'table'

update single row 
update multiple row

update set

USE sql_store;
update customers
set points=points+50
where birth_date<'1990-01-01'

在更新中使用子查询
USE sql_store;
update orders
set comments='Gold'
where customer_id in
(
select customer_id
from customers
where customers.points>3000
)
恢复数据库
file->open sql script

//第五章聚合函数
aggregate Function must USE （）进行调用或者执行
distinct  表示唯一

例题使用到了 union
SELECT 
        'First half of 2019' AS date_range,
        sum(invoice_total) AS total_sales,
        sum(payment_total) AS total_payments,
        sum(invoice_total-payment_total) AS what_we_expect
FROM sql_invoicing.invoices
where invoice_date 
between '2019-01-01' and '2019-06-30'
union
SELECT 
        'Second half of 2019' AS date_range,
        sum(invoice_total) AS total_sales,
        sum(payment_total) AS total_payments,
        sum(invoice_total-payment_total) AS what_we_expect
FROM sql_invoicing.invoices
where invoice_date between '2019-07-01' and '2019-12-31'
union
SELECT 
        'Tataol' AS date_range,
        sum(invoice_total) AS total_sales,
        sum(payment_total) AS total_payments,
        sum(invoice_total)-sum(payment_total) AS what_we_expect
FROM sql_invoicing.invoices
where invoice_date between '2019-01-01' and '2019-12-31'
;

sum() 函数两种写法都可以

GROUP BY 子句
先分组再排序
SELECT 
    pay.date,
    paym.name,
    sum(amount) AS total_payments
FROM sql_invoicing.payments pay
join payment_methods paym
on pay.payment_method=paym.payment_method_id
group by pay.date,paym.name
order by date;

having 子句（是group by 后面的条件查询====数据筛选）
where 可以使用原表中的列，但是having只能使用select使用到的列，因为having 是服务group by的

SELECT 
cs.customer_id ,
cs.first_name,
cs.last_name,
sum(oits.quantity*oits.unit_price) as totalPay
FROM sql_store.customers cs
join orders os
using (customer_id)
join order_items oits
using (order_id)
where state='VA'
group by cs.customer_id ,
cs.first_name,
cs.last_name
having totalPay>100

with rollup（应用于聚合列----对每一组的分组做统计  使用时 不能用别名）
SELECT 
pay.name as payment_method,
sum(amount) as total
FROM sql_invoicing.payments  invpay
join payment_methods pay
on  invpay.payment_method=pay.payment_method_id
group by pay.name with rollup
order by total

编写复杂查询
subqueries

SELECT *
FROM sql_hr.employees
where salary>(
                select avg(salary)
                from sql_hr.employees
                );
                
IN Operator