MYSQL学习笔记（自用）第一章

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

前言

学习资料下载

1. 视频学习网址：B站：BV1UE41147KC
2. 学习使用数据库下载：链接：link.
   提取码：abcd

第一章

第一节:选择语句|The Select Statement(在单一表格中检索数据）

• USE
• SELECT
• FROM（如何在表格中找到想要的内容）

USE sql_store;
Select *
From customers
-- where customer_id = 1

第二节.选择子句|The Select Clause

• 在customers表格中查询
• first_name
• last_name
• points
• (points * 10) + 100 AS ‘discount_factor’

Select 
    first_name, 
    last_name, 
	points,
	(points * 10) + 100 AS 'discount_factor'
From customers

• 在customers表格中查找“州”（不重复出现州的名字）

Select Distinct state
From customers

*Distinct: 代表不重复参数

第二节.作业练习

• 在products的表格中查找
• name
• unit_price
• unit_price * 1.1 并命名为 new_price

Select
      name,
	  unit_price,
      unit_price * 1.1 AS new_price
From products

第三节.WHERE子句| The WHERE Clause

• 在customers的表格中查找points大于3000的客户

Select *
From customers
Where points > 3000

• 在customers表格中查找非’VA’的州

SELECT *
From Customers
Where state != 'va'

• 在custoemrs表格中查找生日大于’1990-01-01’的客户

SELECT *
From Customers
Where birth_date > "1990-01-01"

第三节.作业练习

• 在orders表格中查找order_date大于’2019-01-01’的客户

Select *
From orders
Where order_date >= '2019_01_01'

第四节. AND, OR, NOT运算符| The AND, OR and NOT Operators

SELECT *
FROM Customers
Where birth_date > '1990-01-01' AND points > 1000

SELECT *
FROM Customers
Where birth_date > '1990-01-01' OR
      (points > 1000 AND State = 'VA')

SELECT *
FROM Customers
Where NOT (birth_date > '1990-01-01' OR points > 1000)
## 上式等于（Where birth_date <= '1990-01-01' AND points <= 1000)

第四节.作业练习

Select *
From order_items
Where order_id = 6 AND unit_price*quantity >30 

第五节. IN运算符|The IN Operator

Select *
From Customers
Where state = 'VA' OR state = 'GA' OR state='FL'
## 上式等于 （Where state = 'VA' OR 'GA' OR 'FL)'
### 上式同等于  （Where state IN ('VA', 'FL', 'GA')）

Select *
From Customers
Where state NOT IN ('VA', 'FL', 'GA')

第五节.作业练习

Select *
From products
Where quantity_in_stock IN ('49', '38', '72')

第六节. BETWEEN运算符| The BETWEEN Operator

Select *
From customers
Where points >= 1000 AND points <= 3000

Select *
From customers
Where points Between 1000 AND 3000

第六节.作业练习

Select *
From customers
Where birth_date Between '1990-01-01' AND '2000-01-01'

第七节. LIKE运算符| The LIKE Operator

Select *
From customers
Where last_name LIKE 'b%'
## b%代表任何开头是b后面无限字符的单词

Select *
From customers
Where last_name LIKE 'brush%'

Select *
From customers
Where last_name LIKE '%b%'
## %b%表示任何含有b的单词

Select *
From customers
Where last_name LIKE 'b____y'
## % any number of characters
## _ single characters

第七节.作业练习

Select*
From customers
Where address LIKE '%trail%' OR '%AVENUE%' 

Select*
From customers 
Where phone LIKE '%9'

第八节.REGEXP运算符| The REGEXP Operator

Select *
From customers
Where last_name REGEXP 'field'
## ^field 代表必须是field开头
## field$ 代表必须是field结尾

Select *
From customers
Where last_name REGEXP 'field|mac|rose'

Select *
From customers
Where last_name REGEXP '[gim]e'
## [gim]e 代表 ge ie me任意之一

Select *
From customers
Where last_name REGEXP '[a-h]e'
-- ^ begginning
-- $ end
-- | logical or
-- [abcd]
-- [a-f]

第八节.作业练习

Select *
From customers
Where first_name REGEXP 'ELKA' or 'AMBUR'

Select *
From customers
Where last_name REGEXP 'ey$' or 'on$'

Select *
From customers
Where last_name REGEXP '^my' or '^se'

Select *
From customers
Where last_name REGEXP 'b[ru]'

第九节. IS NULL运算符|The IS NULL Operator

Select *
From customers
Where phone IS  NOT NULL 

第九节.作业练习

Select *
From orders
Where shipped_date IS NULL

第十节. ORDER BY子句| The ORDER BY Clause

Select *
From customers
ORDER BY first_name DESC
## DESC 降序

Select *
From customers
ORDER BY  state DESC, first_name DESC

Select first_name, last_name, 10 AS points
FROM customers
ORDER BY 1, 2
## 1代表first_name, 2代表last_name

第十节.作业练习

Select *, quantity*unit_price AS total_price
From order_items
Where order_id = 2
ORDER BY quantity*unit_price DESC

第十一节. LIMIT子句| The LIMIT Clause

SELECT *
FROM customers
LIMIT 6, 3
## LIMIT 6, 3代表跳过前6个结果找后3个结果

第十一节.作业练习

Select*
FROM customers
ORDER BY points DESC
LIMIT 3