Ray_YC's Blog

mysql 学习记录

 
  • 2019年 09月08日

视频已搬运到B站

  • 视频链接

YouTube链接 B站链接

SELECT

SELECT fist_name,last_name

FROM customers

  • 从 customers 表中查找 fist_name,last_name

SELECT last_name,fist_name,points

FROM customers

  • 从 customers 表中查找 last_name, fist_name,points

SELECT last_name,fist_name,points

FROM customers

  • 从 customers 表中查找 last_name, fist_name,points

SELECT last_name,fist_name,points,(points + 100) * 10 AS discount_factor

FROM customers

  • 从 customers 表中查找 last_name, fist_name,points,discount_factor

SELECT name,unit_price,unit_price * 1.1 AS new_price

FROM products

  • 从 products 表中查找 name, unit_price, new_price

WHERE

SELECT *

FROM Customers

WHERE points > 3000

  • 从 customers 表中查找 所有 points大于3000的数据

SELECT *

FROM orders

WHERE order_data >= ‘2019-01-01’

  • 从 orders 表中查找 所有 order_data >= ‘2019-01-01’的数据

SELECT *

FROM Customers

WHERE points > 3000 AND brith_data > ‘1990-01-01’

  • 从 customers 表中查找 所有 points大于3000 且 brith_data > ‘1990-01-01 的数据

SELECT *

FROM Customers

WHERE points > 1000 OR brith_data > ‘2019-01-01’

  • 从 customers 表中查找 所有 points大于1000 或 brith_data > ‘1990-01-01 的数据

SELECT *

FROM Customers

WHERE NOT (points > 1000 OR brith_data > ‘2019-01-01’)

  • 从 customers 表中查找 对上面条件取反后的数据

SELECT *

FROM odrer_items

WHERE order_id = 6 AND unit_price * quantity > 30

  • 从 odrer_items 表中查找 order_id = 6 AND unit_price * quantity > 30 的数据

SELECT *

FROM Customers

WHERE state IN(‘VA’,’FL’,’GA’)

  • 从 customers 表中查找 state 等于 ‘VA’或’FL’或’GA’ 的数据

BETWEEN

SELECT *

FROM Customers

WHERE points BETWEEN 1000 AND 3000

  • 从 customers 表中查找 所有 points大于等于1000 或小于等于3000的数据

SELECT *

FROM Customers

WHERE brith_data BETWEEN ‘1990-01-01’ AND ‘2000-01-01’

  • 从 customers 表中查找 所有 brith_data大于等于’1990-01-01’ 或小于等于’2000-01-01’的数据

LIKE

SELECT *

FROM Customers

WHERE last_name NOT LIKE ‘b%’

  • 从 customers 表中查找 last_name 不以b开头（大小写都行） 的数据

REGEXP

  • ^ beginning
  • $ end
  • | 逻辑或
  • [abcd]
  • [a-z]

SELECT *

FROM Customers

WHERE last_name REGEXP ‘b’

  • 从 customers 表中查找 last_name 包含b（大小写都行） 的数据

SELECT *

FROM Customers

WHERE last_name REGEXP ‘^field|mac|rose’

  • 从 customers 表中查找 last_name 以field开头 或包含 mac或rose（大小写都行） 的数据

SELECT *

FROM Customers

WHERE last_name REGEXP ‘[gim]e’

  • 从 customers 表中查找 last_name 包含 ge、ie、me（大小写都行） 的数据

IS NULL

SELECT *

FROM Customers

WHERE phone IS NOT NULL

  • 从 customers 表中查找 phone 不为空的数据

ORDER BY

  • mysql 默认使用创建表时指定的主键（primary key）排序

SELECT *

FROM Customers

ORDER BY first_name DESC

  • 从 customers 表中查找 全部数据 以first_name排序后的倒序(DESC)方式输出

SELECT *

FROM Customers

ORDER BY state DESC,first_name DESC

  • 从 customers 表中查找 全部数据 先以state排序后的倒序(DESC),后用first_name排序后的倒序(DESC)方式输出

SELECT *,quantity * unit_price AS total_price

FROM Customers

WHERE order_id = 2

ORDER BY tota_price DESC

  • 从 customers 表中查找order_id = 2 全部数据 和以 quantity * unit_price排序后的倒序(DESC)数据方式输出

LIMIT

SELECT *

FROM Customers

LIMIT 6,3

  • 从 customers 表中查找 全部 跳过（6）条数据 输出前3条数据 【6叫做偏移量】

JOIN

  • 用于在多个表之间建立联系。输出想要的数据。

SELECT order_id,o.customer_id,first_name,last_name

FROM orders o

JOIN customers c

ON o.customers_id = c.custormer_id

  • 从orders 表格中查找 customers_id等于customers表中的custormer_id的数据
  • 在select中 如果两个表中有相同的列名(customer_id) 必须指定列所属的表名，否则mysql无法定位列所处的表到底是哪个表，会报错。
  • 在查询中为表指定别名，简化书写(orders o)(customers c)

SELECT order_id,oi.product_id,quantity,oi.unit_price

FROM orders_items oi

JOIN products p

ON oi.product_id = p.product_id

  • 从orders 表格中查找 product_id等于products表中的product_id的数据
  • 在select中 如果两个表中有相同的列名(customer_id) 必须指定列所属的表名，否则mysql无法定位列所处的表到底是哪个表，会报错。
  • 在查询中为表指定别名，简化书写(orders o)(customers c)
  • 在产品表(product)和订单表(orders_items)中都有unit_price 需要根据实际情况选择输出的具体是哪个unit_price (例子中为了输出当前价格使用的是orders 表的数据)

JOIN to accross databases

  • 跨数据库表链接，类似linux系统中的绝对路径，在查询时必须使用绝对路径 数据库名.表名.列名 来定位数据。

JOIN to it’self 自链接

  • use sql_ht;

SELECT e.employee_id,e.first_name,m.first_name AS manager

FROM employees e

JOIN employees m

ON e.reports_to = m.employee_id

  • 从employees 表格中查找 reports_to等于employees表中的employee_id的数据

JOIN 两个以上表格的链接

SELECT o.order_id,o.order_date,c.first_name,c.last_name

FROM orders o

JOIN customers c

ON o.customer_id = c.customer_id JOIN order_statuses os ON o.status = os.order_status_id

  • 从orders 表格中查找 o.customer_id等于customers表中的c.customer_id的数据。之后， 查找order_statuses表中 os.order_status_id（订单状态id） = 订单状态 的数据

SELECT p.date,p.invoice_id,p.amount,c.name,pm.name

FORM payments p

JOIN clients c

ON p.client_id = c.client_id JOIN payment_methods pm ON p.payment_method = pm.payment_method_id

  • 从payments 表中查找client_id = clients表中client_id的数据。之后， 查找payments_methods 表中支付方式id = payments表中订单支付方式的数据
  • 最终返回 订单时间（from payments），订单编号（from payments）、订单价格（from payments） 客户姓名(from client)、支付方式(from payment_methods) 数据。

复合主键 JOIN

  • 当表格中存在不只一个主键的时候，使用 JOIN ON (1) AND (2) 来进行JOIN 操作。 1、2 分别使用 复合主键的主键1、主键2、进行条件匹配。

SELECT *

FROM order_items oi

JOIN order_items_notes oin

ON oi.order_id = oin.order_id AND oi.product_id = oin.product_id

隐式JOIN语法(implicit join)

SELECT *

FROM orders o

JOIN customers c

ON o.customer_id = c.customer_id

SELECT *

FROM orders o,customer c

WHERE o.customer_id = c.customer_id

  • 上面两个sql语句是相等的。下面的这个sql语句就使用了隐式join ，在隐式join中必须指定WHERE (1) = (2) 条件，否则将生产两张表的交叉数据。

(INNER JOIN) AND (OUTER JOIN)

  • JOIN 默认使用INNER JOIN

  • OUTER JOIN 例子

SELECT c.customer_id,c.first_name,o.order_id

FROM customers c

JOIN orders o

ON c.customer_id = o.customer_id ORDER BY c.customer_id

  • 上面的例子将看到所有有订单的customer_id 和first_name 还有order_id 的数据

  • 上面的结果 是因为JOIN ON 的条件 将会只返回orders表中存在customer_id的数据。那些在orders表中没有的customer 数据将不会返回，所以就看不到全部customer数据。

  • 为了看到所有的customer 的订单信息，不管customer是否有order_id 可以使用OUTJOIN。

  • OUTJOIN 有两种：1、 LEFT JOIN；2、RIGHT JOIN。

  • 在上面的例子中，如果使用LEFT JOIN 那么 不论数据是否满足ON 的条件（c.customer_id = o.customer_id）select语句中选中的左侧表的数据都将被返回。这样就能看到所有customer的订单信息了。sql语句如下：

SELECT c.customer_id,c.first_name,o.order_id

FROM customers c

LEFT JOIN orders o

ON c.customer_id = o.customer_id ORDER BY c.customer_id

  • 同理使用RIGTH JOIN 不论数据是否满足ON 的条件（c.customer_id = o.customer_id）select语句中选中的右侧表的数据都将被返回。

  • 也可以总结为 LEFT JOIN 将返回FROM 后面表格的全部数据 RIGHT JOIN 将返回JOIN 后面表格的全部数据。这样的话只要将两个表格的顺序互换就能产生不一样的数据。

  • JOIN 代表inner join left join or right join 代表outer join。

OUTER JOIN 两个以上表格的链接

  • 与inner join 类似的 outer join 也可以用作多个表中

SELECT c.customer_id,c.first_name,o.order_id,sh.name AS shipper

FROM customers c

LEFT JOIN orders o

ON c.customer_id = o.customer_id LEFT JOIN shippers sh ON o.shipper_id = sh.shipper_id ORDER BY c.customer_id

SELECT o.order_id,o.order_date,c.first_name AS customer,sh.name AS shipper,os.name AS status

FROM order o

JOIN customer c

ON o.customer_id = c.customer_id LEFT JOIN shippers sh ON o.shipper_id = sh.shipper_id JOIN order_status os ON o.status = os.status_id ORDER BY c.customer_id

OUTER JOIN to it’self OUTER自链接

  • 与前面join的内链接一样，

SELECT e.employee_id,e.first_name,m.first_name AS manager

FROM employees e

LEFT JOIN employees m

ON e.reports_to = m.employee_id

USING

  • 如果on 条件中 列的名称相同，可以使用USING() 语句来简化 ON 语句

SELECT order_id,o.customer_id,first_name,last_name

FROM orders o

JOIN customers c

ON o.customers_id = c.custormer_id

上面的sql语句可以写成

SELECT order_id,o.customer_id,first_name,last_name

FROM orders o

JOIN customers c

USING (custormer_id)

  • 两者功能相同。

SELECT c.customer_id,c.first_name,o.order_id,sh.name AS shipper

FROM customers c

LEFT JOIN orders o

USING (customer_id) LEFT JOIN shippers sh USING (shipper_id) ORDER BY c.customer_id

多主键join using 语句

SELECT *

FROM order_items oi

JOIN order_items_notes oin

ON oi.order_id = oin.order_id AND oi.product_id = oin.product_id

  • 可以写成：

SELECT *

FROM order_items oi

JOIN order_items_notes oin

USING (order_id,product_id)

– 返回顶部 –

搜索