Mysql笔记

/*
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
*/