2020년 2월 27일 복기(MYSQL)

Creating the Databases for this Course

USE sql_store;

SELECT *
from customers
-- where customer_id = 1
order by first_name

--

SELECT 
	first_name, 
	last_name, 
   	points, 
	(points *10) +100 AS 'discount factor'
FROM customers
---
UPDATE `sql_store`.`customers` 
SET `state` = 'VA' 
WHERE (`customer_id` = '1');
--
SELECT DISTINCT state
FROM customers

The SELECT Clause

-- Return all the products
-- name
-- unit price
-- new price (unit price * 1.1)

SELECT name, unit_price, unit_price * 1.1 AS new_price
FROM products

The WHERE Clause

SELECT *
FROM Customers
WHERE state ='VA'
-> 맞는것
SELECT *
FROM Customers
WHERE state <>'VA'

-> 아닌것

SELECT *
FROM Customers
WHERE birth_date >'1990-01-01'

-- Get the order placed this year

SELECT *
FROM orders
WHERE order_date >= '2019-01-01'

The AND, OR, and NOT Operators

SELECT *
FROM Customers
WHERE NOT (birth_date >= '1990-01-01' OR points >1000)

SELECT *
FROM Customers
WHERE birth_date <= '1990-01-01' AND points <= 1000

From the order_itmes tabl, get the items for order #6
Where the total price is greater than 30

SELECT *
FROM order_items
WHERE order_id =6 AND unit_price * quantity >30

The IN Operator

SELECT *
FROM Customers
WHERE state = 'VA' OR state ='GA' OR state ='FL'

를 변경할 수 있다.

SELECT *
FROM Customers
WHERE state NOT IN ('VA', 'GA' ,'FL' )

Return products with quantity in stock equal to 49,38,72

SELECT *
FROM products
WHERE quantity_in_stock IN (49,38,72)

The LIKE Operator

SELECT *
FROM customers
WHERE last_name LIKE 'b____y'
-- % any number of characters
-- _ single character

--Get the customers whose
--addressed contain TRAIL or AVENUE

SELECT *
FROM customers
WHERE address LIKE '%trail%' OR
	address LIKE '%avenue%'

The REGEXP Operator

SELECT * 
FROM customers
WHERE last_name REGEXP 'field$' -> 끝자리


SELECT * 
FROM customers
WHERE last_name REGEXP '^field|mac|rose' ->앞자리