/*
USE sql_store; #使用 name数据库
SELECT * # （明确获得的列） （*）所有列 customer_id, first_name （选这两列）
FROM customers # （明确查询的表） 从 custermers 表中选择
WHERE customer_id = 1 #（按何种结果筛选） 只会得到 cusromer_id = 1 的数据
ORDER BY first_name # （根据 xx 排序 明确列）
*/
/*
SELECT
last_name,
first_name,
points,
(points + 10) * 100 AS "discount factor"
#讲运算得到的列取别名（AS）用' '或者" " 这样就可以看作字符串，可以中间加空格
FROM customers
*/
/*
SELECT DISTINCT state #数据中本应该有两条数据含有"VA",使用关键字（DISTINCT）可以去重
FROM customers
*/
/*
题目：
Return all the products
show ther column
name
unit price
new price (unit price * 1.1)
USE sql_store;
SELECT
name,
unit_price,
(unit_price * 1.1) AS "new price"
FROM products;
*/
/*
SELECT *
FROM customers
WHERE birth_date >= "1990-01-01" AND points > 1000# AND OR NOT
ORDER BY birth_date
*/
/*
From the order_items table, get the items
for order#6
where the total price is greater than 30
*/
/*
SELECT *
FROM order_items
WHERE order_id = 6 AND quantity * unit_price > 30
*/
/*
SELECT *
FROM Customers
WHERE state = "VA" OR state = "FL" OR state = "GA"
# WHERE state IN ("VA", "FL", "GA") 关键字 IN (可以包含若干个 OR)
# WHERE state NOT IN ("VA", "FL", "GA")
*/
/*
Return products with
quantity in stock equal to 49, 38, 72
*/
/*
SELECT *
FROM products
WHERE quantity_in_stock IN (49, 38, 72)
*/
/*
Return customers born
between 1/1/1990 and 1/1/2000
*/
/*
SELECT *
FROM customers
WHERE birth_date BETWEEN "1990-01-01" AND "2000-01-01" # 关键字 (BETWEEN) 代替 >= AND <=
# WHERE birth_date >= "1990-01-01" AND birth_date <= "2000-01-01"
*/
/*
SELECT *
FROM customers
WHERE last_name LIKE "b%_y"
# % any number of characters
# _ single characters
# 关键字 (LIKE)
*/
/*
Get the customers whose
addresses contain TRAIL or AVENUE
hone numbers end with 9
*/
/*
SELECT *
FROM customers
WHERE address LIKE "%trail%" OR address LIKE "%avenue%";
SELECT *
FROM customers
WHERE phone LIKE "%9";
SELECT *
FROM customers
WHERE phone NOT LIKE "%9";
*/
/*
SELECT *
FROM customers
WHERE lats_name REGEXP "[a-h]e"*/
/*
-- ^ begining "%f"
-- $ end "f%"
-- | or "ni | ss | qq"
-- [abc]f "af | bf | cf"
-- [a-c]f "af | bf | cf"
-- "as" just contains "as" "%as%"
*/
/*
Ger the customers whose
first name are ELKA or AMBUR
last names end with RY or ON
last name start with MY or contains SE
last name contain B followed by R or U
*/
SELECT *
FROM customers
WHERE first_name IN("ELKA", "AMBUR");
SELECT *
FROM customers
WHERE last_name REGEXP "EY$|ON$";
SELECT *
FROM customers
WHERE last_name REGEXP "^MY|SE";
SELECT *
FROM customers
WHERE last_name REGEXP "B[ru]";
/*
USE sql_store; #使用 name数据库
SELECT * # （明确获得的列） （*）所有列 customer_id, first_name （选这两列）
FROM customers # （明确查询的表） 从 custermers 表中选择
WHERE customer_id = 1 #（按何种结果筛选） 只会得到 cusromer_id = 1 的数据
ORDER BY first_name # （根据 xx 排序 明确列）
*/
/*
SELECT
last_name,
first_name,
points,
(points + 10) * 100 AS "discount factor"
#讲运算得到的列取别名（AS）用' '或者" " 这样就可以看作字符串，可以中间加空格
FROM customers
*/
/*
SELECT DISTINCT state #数据中本应该有两条数据含有"VA",使用关键字（DISTINCT）可以去重
FROM customers
*/
/*
题目：
Return all the products
show ther column
name
unit price
new price (unit price * 1.1)
USE sql_store;
SELECT
name,
unit_price,
(unit_price * 1.1) AS "new price"
FROM products;
*/
/*
SELECT *
FROM customers
WHERE birth_date >= "1990-01-01" AND points > 1000# AND OR NOT
ORDER BY birth_date
*/
SELECT
order_id,
o.customer_id,
first_name,
last_name
FROM orders o
INNER JOIN customers c
ON o.customer_id = c.customer_id;
-- 关键字 (INNER JOIN) 内部链接！
-- 关键字 （ON） 基于两个表的customer_id相等来连接成一行！
-- order o 取别名 （注意：一个地方用了别名，则其他地方也得用别名）
-- o.customer_id 两张表都有该列，则得指定一个表的列
SELECT *
FROM orders o
INNER JOIN customers c
ON o.customer_id = c.customer_id;
SELECT
oi.order_id,
oi.product_id,
oi.quantity,
oi.unit_price
FROM order_items oi
INNER JOIN products p
ON oi.product_id = p.product_id;
/*
USE sql_inventory;
-- 连接两个数据库
SELECT *
FROM sql_store.order_items oi
INNER JOIN sql_inventory.products p
ON oi.product_id = p.product_id;
*/
/*
-- 自己数据库中的表相连
USE sql_hr;
SELECT
e.employee_id,
e.first_name,
m.first_name AS manager
FROM employees e
INNER JOIN employees m
ON e.reports_to = m.employee_id
*/
/*
USE sql_store;
-- 连接三个数据库
SELECT
o.order_id,
o.order_date,
c.first_name,
c.last_name,
os.name AS status
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_statuses os ON o.status = order_status_id
*/
/*
USE sql_invoicing;
SELECT
p.date,
p.invoice_id,
p.amount,
c.name,
pm.name
FROM payments p
INNER JOIN clients c ON p.client_id = c.client_id
INNER JOIN payment_methods pm ON p.payment_method = pm.payment_method_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 orders o, customers c
WHERE o.customer_id = c.customer_id
*/