USE sql_store;-- 下面这三个句子顺序不可颠倒SELECT*FROM customers
-- WHERE customer_id = 1ORDERBY first_name
-- 和上面效果一样SELECT*FROM sql_store.customers
USE sql_store;SELECT
name,
unit_price,
unit_price *1.1AS'new price'-- 重命名 FROM products
USE sql_store;SELECT*FROM order_items
WHERE order_id =6AND(quantity * unit_price)>=30
USE sql_store;SELECT*FROM products
WHERE state IN('VA','FL','GA')
USE sql_store;SELECT*FROM customers
WHERE points BETWEEN1000AND3000
USE sql_store;SELECT*FROM customers
WHERE last_name LIKE'b%'-- %代表任意长度字符串,相当于模糊匹配(不区分大小写)-- WHERE last_name REGEXP 'b' 直接模糊匹配含有b的字符串,'^b'表示首字母为b,'b$'表示尾字母为b--WHERE last_name REGEXP '^b|a' 首字母为b或者包含a-- WHERE last_name REGEXP '[aim]b' 包含ab或ib或mb-- WHERE last_name REGEXP '[a-h]b'
USE sql_store;SELECT*FROM customers
ORDERBY first_name -- 以fiest_name为第一关键字升序排序(末尾加上DESC为降序)
USE sql_store;SELECT*FROM customers
ORDERBY points DESCLIMIT3-- 取前三个
-- JOIN均默认为内连接(INNER JOIN)USE sql_store;SELECT order_id, o.customer_id, first_name, last_name FROM orders o -- 给orders取别名oJOIN customers c -- 连接不同的表ON o.customer_id = c.customer_id -- 以order_id 相等为标准连接-- USING (customer_id) 当表名相同时,可用此句替换上面的
-- 多个表的数据连接SELECT
clients.name,
payment_methods.payment_method_id,date,
amount
FROM payments
JOIN clients
ON payments.client_id = clients.client_id
JOIN payment_methods
ON payments.payment_id = payment_methods.payment_method_id
USE sql_store;SELECT
customers.customer_id,
first_name,
order_id
FROM customers
LEFTJOIN orders -- left join 表示customer_id全部输出,即便等式不成立ON customers.customer_id = orders.customer_id
ORDERBY customers.customer_id
USE sql_store;INSERTINTO customers
VALUE(DEFAULT,'Joun','Smith','1990-01-01',NULL,'address','city','CA',200);
USE sql_store;INSERTINTO orders (customer_id, order_date,status)VALUES(1,'2020-01-01',1);INSERTINTO order_items
VALUES(last_insert_id(),1,1,2.59),-- 返回新插入行时生成的Id(last_insert_id(),2,1,3.59)
USE sql_invoicing;-- 更新数据库UPDATE invoices
SET payment_total =10, payment_date ='2020-01-01'WHERE invoice_id =1
USE sql_store;-- 子查询UPDATE orders
SET comments ='Golden customer'WHERE customer_id IN(SELECT customer_id
FROM customers
WHERE points >=3000)
USE sql_store;-- 删除DELETEFROM invoices
WHERE client_id =1
USE sql_invoicing;SELECTMAX(invoice_total)AS highest,MIN(invoice_total)AS lowest,COUNT(DISTINCT client_id)AS total_record -- DISTINCT指取client_id不重复的个数FROM invoices
WHERE invoice_date >'2019-07-01'
USE sql_invoicing;-- 注意语句顺序SELECT
client_id,SUM(invoice_total)AS total_sales
FROM invoices
WHERE invoice_date >='2019-01-01'GROUPBY client_id -- 分组聚合ORDERBY total_sales DESC
USE sql_invoicing;SELECT
client_id,SUM(invoice_total)AS total_sales,COUNT(*)AS number_of_invoices
FROM invoices
GROUPBY client_id WITH ROLLUP-- 对每组的分类合并求结果HAVING total_sales >=500-- 对分组后的数据进行操作-- 不能写HAVING payment_date,因为SELECT中没有payment_date
USE sql_hr;-- 相关子查询SELECT*FROM employees e
WHERE salary >(SELECTAVG(salary)FROM employees
WHERE e.office_id = employees.office_id
)
USE sql_invoicing;SELECT*FROM clients
WHEREEXISTS(-- 只返回是否存在下面WHERE的情况SELECT client_id
FROM invoices
WHERE client_id = clients.client_id
)
USE sql_invoicing;-- SELECT 语句中的子查询SELECT
invoice_id,
invoice_total,(SELECTAVG(invoice_total)FROM invoices)AS'invoice_avg',
invoice_total -(SELECT invoice_avg)AS difference
FROM invoices