MOSH10小时学会sql笔记大全（数据分析零基础学习笔记—2.1_sql）

USE sql_invoicing;

select p.payment_id,c.client_id,p.invoice_id,p.date,p.payment_method,p.amount,pm.name as method_name

FROM clients c

JOIN payments p

on c.client_id=p.client_id

JOIN payment_methods pm

ON p.payment_method=pm.payment_method_id

COMPOUND JOIN CONDITIONS-复合主键

USE sql_store;

select *

FROM order_items oi

JOIN order_item_notes oin

ON oi.order_id=oin.order_id

and oi.product_id=oin.product_id

IMPLICT JOIN SYNTAX-隐式联合语法

select *

FROM orders o

JOIN customers c

ON o.customer_id= c.customer_id

==implicit join syntax

select *

from orders o,customers c

where o.customer_id= c.customer_id

OUTER JOINS

SELECT

c.customer_id,

c.first_name,

o.order_id

FROM customers c

LEFT JOIN orders o

ON c.customer_id = o.customer_id

ORDER BY c.customer_id

LEFT 会让 左面的表customers全量显示，无论是否满足布尔运算的条件

LEFT 会让 右面的表orders全量显示，无论是否满足布尔运算的条件

SELECT

c.customer_id,

c.first_name,

o.order_id

FROM customers c

RIGHT JOIN orders o

ON c.customer_id = o.customer_id

ORDER BY c.customer_id

Outer JOINS BETWEEN MULTIPLE TABLES

USE sql_store;

SELECT o.order_date,o.order_id,c.first_name,sh.name as shipper,os.name as status

FROM orders o

JOIN customers c

ON c.customer_id = o.customer_id

LEFT JOIN shippers sh

ON o.shipper_id=sh.shipper_id

JOIN order_statuses os

ON o.status=os.order_status_id

ORDER BY o.order_id

多个表左连接查询，应该将所查主表放在最左边，而且后面的表都应该和它作为连接条件

SELF OUTER JOINS

USE sql_hr;

select

e.employee_id,

e.first_name,

m.first_name manager

FROM employees e

LEFT JOIN employees m

ON e.reports_to=m.employee_id

THE USING CLAUSE

use sql_store;

select

o.order_id,

c.first_name

FROM orders o

JOIN customers c

ON o.customer_id = c.customer_id

==========

use sql_store;

select

o.order_id,

    c.first_name,

    sh.name AS shipper

FROM orders o

JOIN customers c

    USING (customer_id)

LEFT JOIN shippers sh

USING (shipper_id)

__________________________________________________________________________

组合主键

select*

FROM order_items oi

JOIN order_item_notes oin

ON oi.order_id=oin.order_id AND

oi.product_id=oin.product_id

============================

select*

FROM order_items oi

JOIN order_item_notes oin

USING(order_id, product_id)

NATURAL JOINS

不需要指定列，系统自动判断连接2个table

USE sql_store;

select customer_id,order_id

FROM orders o

natural join customers c

CROSS JOINS

把select的两列做笛卡尔积

显性

USE sql_store;

select c.first_name as customer,

p.name as product

FROM customers c

CROSS JOIN products p

ORDER BY c.first_name

隐形

USE sql_store;

select c.first_name as customer,

p.name as product

FROM customers c,products p

ORDER BY c.first_name

UNIOS

USE sql_store;

select order_id,

   order_date,

       'ACTIVE' as status

FROM orders o

where order_date>='2019-01-01'

UNION

select order_id,

   order_date,

       'ARCHIVED' as status

FROM orders o

where order_date < '2019-01-01'

USE sql_store;

SELECT first_name

FROM customers

UNION

SELECT name

FROM shippers

COLUMN ATTRIBUTE

INSERT INTO customers(

first_name,

last_name,

birth_date,

address,

city,

state

)

values(

'John',

'Smith',

'1999-12-25',

'ADDRESS',

'CITY',

'CA'

)

INSETING MULTIPLE ROWS

INSETING HIERARCHIAL ROWS

INSERT INTO orders (customer_id,order_date,status)

values(1,'2019-01-01',2);

select last_insert_id()

INSERT INTO orders (customer_id,order_date,status)

values(1,'2019-01-01',2);

INSERT INTO order_items

values(last_insert_id(),1,1,2.64),

(last_insert_id(),2,5,4.65);

CREATING A COPY OF A TABLE

INSERT INTO order_archieved

SELECT *

from ORDERS

WHERE order_date<'2019-01-01'

USE sql_invoicing;

CREATE TABLE invoices_archieved

SELECT name as client,

i.client_id,

invoice_id,

    number,

    invoice_total,

    payment_total,

    due_date,

    payment_date

FROM invoices i

JOIN clients c

USING (client_id)

WHERE payment_date is not null

UPDATING A SINGLE ROW

UPDATE invoices

SET payment_total=invoice_total*0.5,

payment_date=due_date

WHERE invoice_id=3

UPDATING MUTIPLE ROW

在默认情况，MYSql工作台在安全模式下运行，它仅允许更新一条记录。可以进入edit的perference取消safe updates

UPDATE invoices

SET payment_total=invoice_total*0.5,

payment_date=due_date

WHERE client_id IN (3,4)

USING Subqueries in Updates

USE sql_invoicing;

UPDATE invoices

SET

payment_total=invoice_total*0.5,

payment_date=due_date

WHERE client_id IN(

SELECT client_id

FROM clients

where state in ('CA','NY'))

UPDATE orders

SET comments='gold customer'

where customer_id in

(

    select customer_id

    from customers

    where points>3000)