美文网首页
SQL-学习笔记（第二&三周）

SQL-学习笔记（第二&三周）

作者: Elena_fan | 来源:发表于2022-03-22 20:13 被阅读0次

mosh SQL 视频课程
视频链接：【中英字幕】一口气看完！耗时100天打造SQL数据库零基础入门到精通全套教程_哔哩哔哩_bilibili

大神整理的视频中使用到的数据库：https://pan.baidu.com/s/1KMrI0NqCbWagKI_oK_Rndw?pwd=g5rp 提取码: g5rp

知识点：

1、内连接JOIN
2、外连接LEFT JOIN ON ,RIGHT JOIN ON,
JOIN USING (两表中相同id)
3、交叉连接CROSS JOIN
UNION
4、新增数据INSERT INTO ... VALUES ...
5、更新数据UPDATE ... SET ...
6、删除数据DELETE FROM ...WHERE

内外连接效果对比
--内连接，只返回符合条件的记录
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 
--结果中会显示没有订单的 用户信息 此时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
练习

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
练习
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 (条件字段名)
--当两表格的条件字段相同时，使用此方法更简洁
--等同于 表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)

练习


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
--根据相同id自动连接，易出错
USE  sql_store;

SELECT 
*
FROM orders o
 NATURAL JOIN customers c
交叉连接
 --应用场景，尺码S、M衣服，对应黑色，白色
 --显示语法，推荐，更明确
 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
练习
 --显示连接
 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
练习
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只需要写一遍，来排序
INSERT
#根据建表时设置的属性来插入数据

USE  sql_store;

INSERT INTO
customers

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

)
有默认值的不赋值


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

#产品表中插入三条记录

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)
将数据插入多个表


#场景：订单表为父表，订单明细表为子表
use sql_store;

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

-查询表中插入的最后一条记录的ID
-SELECT  LAST_INSERT_ID()


-为最后一条订单新增2条订单明细记录
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)
新建副本，清空副本数据

use sql_store;
-- 创建表的副本

CREATE TABLE order_archived AS 
SELECT * FROM orders

-- 该表无主键
-- 点击删除该表中的所有数据truncate table 


#查询符合条件的orders表数据，插入到附表中
use sql_store;

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

练习

use sql_invoicing;


-- 创建表，并插入符合条件数据
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发票表里仅有的字段，可省去i.以简化代码

FROM
invoices i
JOIN clients c 
USING (client_id)

WHERE payment_date IS NOT NULL 

-- 运行一次，运行第二次会报错，提示表格已存在，可先drop table 再运行以上代码
更新表格中的记录，一条
use sql_invoicing;

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

记录中将字段设置默认值，设置空值

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

必须设置-以免后续报错

MySQL workbench设置

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)
练习
use sql_store;

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

--子查询语句设置
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
练习
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'
)

相关文章

网友评论

      本文标题：SQL-学习笔记（第二&三周）

      本文链接：https://www.haomeiwen.com/subject/jkqqjrtx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|SQL-学习笔记（第二&三周）|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！