SQL-學(xué)習(xí)筆記（第二&三周）

mosh SQL 視頻課程
視頻鏈接：【中英字幕】一口氣看完！耗時(shí)100天打造SQL數(shù)據(jù)庫(kù)零基礎(chǔ)入門(mén)到精通全套教程_嗶哩嗶哩_bilibili

大神整理的視頻中使用到的數(shù)據(jù)庫(kù)：https://pan.baidu.com/s/1KMrI0NqCbWagKI_oK_Rndw?pwd=g5rp 提取碼: g5rp

知識(shí)點(diǎn)：

1、內(nèi)連接JOIN
2、外連接LEFT JOIN ON ,RIGHT JOIN ON,
JOIN USING (兩表中相同id)
3、交叉連接CROSS JOIN
UNION
4、新增數(shù)據(jù)INSERT INTO ... VALUES ...
5、更新數(shù)據(jù)UPDATE ... SET ...
6、刪除數(shù)據(jù)DELETE FROM ...WHERE

內(nèi)外連接效果對(duì)比
--內(nèi)連接，只返回符合條件的記錄
USE  sql_store;


--


SELECT 

o.order_id,
c.customer_id,
c.first_name


FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
ORDER BY c.customer_id
外連接

--left join right join 
--結(jié)果中會(huì)顯示沒(méi)有訂單的 用戶信息 此時(shí)order_id 為null

USE  sql_store;

SELECT 

c.customer_id,
c.first_name,
o.order_id

FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
ORDER BY c.customer_id
練習(xí)

USE  sql_store;

SELECT 

p.product_id,
p.name,
oi.quantity

FROM products p
LEFT JOIN order_items oi
ON p.product_id= oi.product_id
ORDER BY p.product_id
兩次 left join
--使用建議，多單一的使用left join，避免與right join混用

USE  sql_store;

SELECT 

c.customer_id,
c.first_name,
o.order_id,
sh.name AS shipper


FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
LEFT JOIN shippers sh
  ON o.shipper_id = sh.shipper_id

ORDER BY c.customer_id
練習(xí)
USE  sql_store;

SELECT 

o.order_date,
o.order_id,
c.first_name,
sh.name AS shipper,
os.name
FROM orders o
JOIN customers c 
 ON o.customer_id =c.customer_id
 LEFT JOIN shippers sh
  ON o.shipper_id= sh.shipper_id
LEFT JOIN order_statuses os
    ON o.status =os.order_status_id
ORDER BY os.name
自連接
USE  sql_hr;

SELECT 
e.employee_id,
e.first_name,
m.first_name AS manager

FROM employees e
LEFT JOIN employees m
 ON e.reports_to = m.employee_id
using (條件字段名)
--當(dāng)兩表格的條件字段相同時(shí)，使用此方法更簡(jiǎn)潔
--等同于 表1.條件字段名 = 表2.條件字段名

USE  sql_store;
SELECT 

o.order_id,
c.customer_id,
c.first_name

FROM orders o
JOIN customers c
-- ON o.customer_id = c.customer_id
USING (customer_id)

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

---更改為
USE  sql_store;


SELECT 
*
FROM order_items oi
 JOIN order_item_notes oin
USING (order_id,product_id)

練習(xí)


USE sql_invoicing;

SELECT 

p.date,
c.name AS client,
p.amount,
pm.name AS payment_method

FROM payments p
JOIN  clients c
USING (client_id)

JOIN payment_methods pm
ON p.payment_method=pm.payment_method_id
NATURAL JOIN
--根據(jù)相同id自動(dòng)連接，易出錯(cuò)
USE  sql_store;

SELECT 
*
FROM orders o
 NATURAL JOIN customers c
交叉連接
 --應(yīng)用場(chǎng)景，尺碼S、M衣服，對(duì)應(yīng)黑色，白色
 --顯示語(yǔ)法，推薦，更明確
 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
-- CROSS JOIN products p,orders o
ORDER BY c.first_name
練習(xí)
 --顯示連接
 USE  sql_store;

SELECT 
sh.name AS shipper,
p.name AS product
FROM products p
 CROSS JOIN shippers sh
 ORDER BY sh.name

--隱式連接
 USE  sql_store;

SELECT 
sh.name AS shipper,
p.name AS product
FROM products p,shippers sh
ORDER BY sh.name

USE  sql_store;

SELECT 
order_id,
order_date,
'Active' as status
FROM orders
WHERE order_date >='2019-01-01'

SELECT 
order_id,
order_date,
'Archived' as status
FROM orders
WHERE order_date <'2019-01-01';
union
-- union
USE  sql_store;

SELECT 
order_id,
order_date,
'Active' as status
FROM orders
WHERE order_date >='2019-01-01'

UNION 

SELECT 
order_id,
order_date,
'Archived' as status
FROM orders
WHERE order_date <'2019-01-01'


--union不同表
USE  sql_store;

SELECT first_name
FROM 
customers

UNION 

SELECT name
FROM shippers
練習(xí)
USE  sql_store;

SELECT 
    customer_id,
    first_name,
    points,
    'Bronze' AS type
FROM 
customers
WHERE points<2000

UNION 
SELECT 
    customer_id,
    first_name,
    points,
    'Silver' AS type
FROM customers
WHERE points BETWEEN 2000 AND 3000


UNION 
SELECT 
    customer_id,
    first_name,
    points,
    'Gold' AS type
FROM customers
WHERE points>3000

ORDER BY first_name
-- ORDER BY只需要寫(xiě)一遍，來(lái)排序
INSERT
#根據(jù)建表時(shí)設(shè)置的屬性來(lái)插入數(shù)據(jù)

USE  sql_store;

INSERT INTO
customers

VALUES ( 
DEFAULT,
'fan',
'yx',
'1996-01-01',
NULL,
'adress',
'city',
'DZ',
DEFAULT

)
有默認(rèn)值的不賦值


use sql_store;

INSERT INTO customers 
(
first_name,
last_name,
birth_date,
address,
city,
state

)
VALUES (
'aaa',
'bc',
'1990-01-01',
'sd',
'dz',
'la'

)

#一次性插入多條記錄
use sql_store;

INSERT INTO shippers
(
name
)
VALUES
('shipper1'),
('shipper2'),
('shipper3')
練習(xí)

#產(chǎn)品表中插入三條記錄

use sql_store;

INSERT INTO products

(
name,
quantity_in_stock,
unit_price
)

VALUES('aaa',2030,2.1),
('bbb',2000,1.2),
('ccc',3000,2.8)
將數(shù)據(jù)插入多個(gè)表


#場(chǎng)景：訂單表為父表，訂單明細(xì)表為子表
use sql_store;

INSERT INTO orders (customer_id,order_date,status)
VALUES
(1,'1992-01-01',1);

-查詢表中插入的最后一條記錄的ID
-SELECT  LAST_INSERT_ID()


-為最后一條訂單新增2條訂單明細(xì)記錄
INSERT INTO order_items(order_id,product_id,quantity,unit_price)

VALUES(LAST_INSERT_ID(),1,1,1.2),
(LAST_INSERT_ID(),2,1,1.3)
新建副本，清空副本數(shù)據(jù)

use sql_store;
-- 創(chuàng)建表的副本

CREATE TABLE order_archived AS 
SELECT * FROM orders

-- 該表無(wú)主鍵
-- 點(diǎn)擊刪除該表中的所有數(shù)據(jù)truncate table 


#查詢符合條件的orders表數(shù)據(jù)，插入到附表中
use sql_store;

INSERT INTO order_archived
SELECT * FROM 
orders 
WHERE order_date<'2019-01-01'

練習(xí)

use sql_invoicing;


-- 創(chuàng)建表，并插入符合條件數(shù)據(jù)
CREATE TABLE  invoice_archived
SELECT  

i.invoice_id,
i.number,
c.name AS client,
i.invoice_total,
i.payment_total,
i.invoice_date,
i.due_date,
i.payment_date
-- i.invoice_date,i.due_date,i.payment_date發(fā)票表里僅有的字段，可省去i.以簡(jiǎn)化代碼

FROM
invoices i
JOIN clients c 
USING (client_id)

WHERE payment_date IS NOT NULL 

-- 運(yùn)行一次，運(yùn)行第二次會(huì)報(bào)錯(cuò)，提示表格已存在，可先drop table 再運(yùn)行以上代碼
更新表格中的記錄，一條
use sql_invoicing;

UPDATE invoices 
SET payment_total='10',payment_date='2019-03-01'
WHERE invoice_id =1

記錄中將字段設(shè)置默認(rèn)值，設(shè)置空值

use sql_invoicing;

UPDATE invoices 
SET payment_total=DEFAULT ,payment_date=NULL
WHERE invoice_id =1

算式更新記錄中的字段

use sql_invoicing;

UPDATE invoices 
SET payment_total= invoice_total *0.5,
payment_date=due_date
WHERE invoice_id =3
更新多行記錄
#客戶id的記錄均更新

use sql_invoicing;

UPDATE invoices 
SET payment_total= invoice_total *0.5,
payment_date=due_date
WHERE client_id =3

必須設(shè)置-以免后續(xù)報(bào)錯(cuò)

MySQL workbench設(shè)置

edit preferences-sql editor 取消safe updates選中

--where 中使用in
use sql_invoicing;

UPDATE invoices 
SET payment_total= invoice_total *0.5,
payment_date=due_date
WHERE client_id in (3,4)
練習(xí)
use sql_store;

UPDATE customers 
set points= points +50
WHERE birth_date < '1990-01-01'

--子查詢語(yǔ)句設(shè)置
use sql_invoicing;


UPDATE invoices 
SET payment_total= invoice_total *0.5,
payment_date=due_date
WHERE client_id =

(SELECT client_id 
FROM clients
WHERE name='Myworks')

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

use sql_invoicing;


UPDATE invoices 
SET payment_total= invoice_total *0.5,
payment_date=due_date
WHERE payment_date IS NULL
練習(xí)
use sql_store;

UPDATE  orders 
SET comments='Gold' 

WHERE  customer_id IN 
(SELECT customer_id 
FROM 
customers
WHERE points >3000

)

--

刪除
USE sql_invoicing;


DELETE FROM invoices
WHERE client_id =

(
SELECT client_id
FROM clients
WHERE name='Myworks'
)
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容