SQL入门学习

Youtube三小时速成SQL

MySQL Tutorial for Beginners [Full Course]

什么是SQL

• DB里面，数据都按一定的顺序排放，大家通过DB的管理软件（database management）来访问数据库并得到结果
  • 有两种种类
    • ralational:table分成不同的表，之间是有关系的
      • 比如MySQL，Server SQL等等。MySQL是开源的，用的人最多
    • NoSQL：没有表，没有关系，和SQL的语法不共通

安装

• 本地默认端口3306，注意有的时候可能会被占用
  • sudo lsof -i:3306
  • kill （番号)
• Connection错误：
  • 需要在设置里启动MySQL Server才行

创建Database

• 上面的小闪电：运行
• 会有创建database的.sql文件，使用他之后就会在schemas里面出现创建好的database

database组成

• Table
  • 一般会在其他相关table里面只存储id，这样避免主体的其他地方改变
  • relational databases：各个表之间有关系，共享一些相同的数据（比如id）
• View：组合很多table
• Function：写具体的功能（比如找出来地方相同的人等）

SELECT(以及一些基本命令)

• 大小写不敏感哪里都不敏感，但是一般来说要用大写来写命令。对换行不敏感，但是分开写比较容易看
• USE表示访问哪个database
• SELECT用来访问column，也可以用星号表示所有的。
  • 可选内容： FROM一个表。WHERE来定义具体的要求，必须这一项等于什么。ORDER BY可以设置排序。
• Comment:两个横杠

  SELECT *
  FROM customers
  WHERE customer_id = 1
  ORDER BY first_name

SELECT本身

• 可以在后面接任意列的名字，如果调换顺序的话显示出来的顺序也会改变
• 数学运算可以直接加在数字的列上面

• AS可以把表头改成一个新的名字。改完之后的名字可以在后面直接用

  SELECT  
  	last_name, 
      first_name, 
      points * 10 + 10 AS "discount factor"
  FROM customers

• DISTINCT可以删除重复的元素

  SELECT  DISTINCT state
  FROM customers

WHERE

• 用来写条件，比如比大小
• 注意等于的符号是一个等号。<>也可以表示不等于
• string的时候也需要用引号括起来。string也可以比大小，比如生日日期

  SELECT *
  FROM customers
  WHERE state="VA"

AND OR NOT

• 逻辑运算顺序，AND最先计算，然后是OR

  SELECT *
  FROM customers
  WHERE birth_date > '1990-01-01' OR (points > 1000 AND state = 'VA')
  --相当于上面加了括号的写法

IN

SELECT *
FROM customers
WHERE state = 'VA' OR state = 'GA' OR state = 'FL'

-- 简化写法

SELECT *
FROM customers
WHERE state IN ('VA','GA','FL')

-- 也可以和NOT组合使用
SELECT *
FROM customers
WHERE state NOT IN ('VA','GA','FL')

BETWEEN

• 取中间值的写法，注意这里的写法包括了等于
• 同样不止包括数字，也包括string

  SELECT *
  FROM customers
  WHERE points BETWEEN 1000 AND 3000

LIKE

• 找到b开头的名字，后面的百分号可以表示随便多少位的字母。比如%B%可以找到名字里有B的
• _y只有两个character的长度，不管前面的是什么，后面的是y。可以增加下划线的长度。比如“B___Y
• 注意两个LIKE并列的时候需要写两遍
• 可以和NOT组合成not like

  SELECT *
  FROM customers
  WHERE last_name LIKE 'b%'

REGEXP

比like更复杂的表示方法，可以有几种方式

• ^field这个东西必须出现在最开头
• field$必须以这个string结束
• field|mac有前面或者后面的元素（中间不能有空格）
• [a-h]e可以随意排列组合的数字

SELECT *
FROM customers
WHERE last_name REGEXP "^field|mac|rose"


SELECT *
FROM customers
WHERE last_name REGEXP "[gim]e"  
-- 里面有ge ie 或者 me  也可以写 "[a-h]e"


SELECT *
FROM customers
-- WHERE first_name REGEXP "ELKA|AMBUR"
WHERE last_name REGEXP "EY$|ON$"
-- WHERE last_name REGEXP "^MY|SE"
-- WHERE last_name REGEXP "B[RU]"

IS NULL

• 找到空的表格的地方，可以和NOT组合使用

  SELECT *
  FROM customers
  WHERE phone IS NOT NULL

ORDER BY

• 每个表里都有一个primary的column，会代表这个表里面每个data的unique
• 可以进行多重的sort，每个可以单独设置DESC降序排列。

• 即使是SELECT里面没有选择的列，也可以进行排序

  SELECT *
  FROM customers
  ORDER BY state DESC, first_name

• 下面这种情况代表按first_name和last_name来排序（即SELECT里面的1，2列）

  • 但是希望尽量避免这种写法

    SELECT first_name,last_name
    FROM customers
    ORDER BY 1,2

LIMIT

• 不想得到所有的行，比如只想得到前三行，那就LIMIT 3
• LIMIT一定是出现在最后一行的

  SELECT *
  FROM customers
  LIMIT 6,3
  -- 这行的意思跳过前面的6个，然后显示后面的三个

Joins(组合column)

上面的都是从一个table里面选的，现在来看从不同table选

inner join

基础

可以把两个表根据一些相同的内容连接在一起(比如id)，这样会把两个小表组合成一个大表（分开的原因：可能有一些表容易改变，一些表不容易改变，比如用户信息和用户订单要分开）

• 如果想SELECT两个表里面都有的column，必须指定一个
• 另外，可以在FROM和JOIN后面简化database的名字。但是指定之后必须一直用指定后的，不然会报错

  SELECT order_id, first_name, last_name
  FROM orders
  JOIN customers 
  	ON orders.customer_id = customers.customer_id
  
  -- 简化版本
  SELECT order_id, o.customer_id, first_name, last_name
  FROM orders o
  JOIN customers c
  	ON o.customer_id = c.customer_id

DB之间的data join

• 只需要在table前面加上DB的索引就可以了。需要注意现在用的是哪个DB（USE关键词）

  SELECT * 
  FROM order_items oi
  JOIN sql_inventory.products p
  	ON oi.product_id = p.product_id

join itself

• 在例题的表里面，每个员工都会有一个对应的manager，这个manager也显示的是员工id，现在要在这个表里面找到所有员工的manager
• 必须给两个相同的table不同的代称，比如这里用了e表示员工，m表示manager。另外，在SELECT的时候因为列的名字都相同，需要加上不同的表的名字

  USE sql_hr;
  
  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

join multiple table

• 在后面继续JOIN就可以组合多个表格，注意名字不要打错

  USE sql_store;
  
  SELECT o.order_id, o.order_date,
  		c.first_name, c.last_name,
          os.name AS status
  FROM orders o
  JOIN customers c
  	ON o.customer_id = c.customer_id
  JOIN order_statuses os
  	ON o.status = os.order_status_id

有条件的join

之前的都用独一无二的id来join这些表格，但是有时候的表格是不一定独一无二的，需要用一些column的组合来确定

• ON后面用AND来增加多个条件

  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

outer join

如果只打join就是inner的，outer需要加上关键词。outer也是可选的，只需要left和right就行了

基础

USE sql_store;

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

• 上面的是刚才inner的方法，但是这种方法有一个问题，因为不是每个customer都有订单，所以最后的结果只会显示有订单的用户

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


-- 或者

SELECT 
	c.customer_id,
    c.first_name,
    o.order_id
FROM orders o 
RIGHT JOIN customers c
	ON c.customer_id = o.customer_id
ORDER BY c.customer_id

• 有两种方法，left join和right join。如果用了left，左边的table的所有元素（也就是FROM的那个）无论满不满足on的条件都会显示在列里，不满足条件的会用null轮空

多个表格的outer join

• 无论用户有没有订单，都会显示，无论订单有没有shipper，也都会显示
• 尽量不要用right，因为加多个表的时候容易看晕

  SELECT 
  	c.customer_id,
      c.first_name,
      o.order_id,
      sh.name
  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

self outer join

• 上面inner的HR例子里面，实际上是有信息丢失的，因为有一个人没有上司，所以在表里面也没显示

  USE sql_hr;
  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

• 在数据越来越多的时候，读起来就非常困难了，这时候可以选择简化语句
• 但是必须保证两者的表头是一样的才可以

  USE sql_store;
  SELECT 
  	c.customer_id,
      c.first_name,
      sh.name AS shipper
  FROM customers c
  LEFT JOIN orders o
  	-- ON c.customer_id = o.customer_id
      USING (customer_id)
  LEFT JOIN shippers sh
  	USING (shipper_id)

SELECT *
FROM order_items oi
JOIN order_item_notes oin
	-- ON oi.order_id = oin.order_id
 --    AND oi.product_id = oin.product_id
 	USING (order_id, product_id)

implicit join（不推荐）

• 也可以不用JOIN命令来写，但是这种写的方法很有可能产生不会报错的重大bug，所以不要用

  SELECT *
  FROM orders o, customers c
  WHERE o.customer_id = c.customer_id

natural join（不推荐）

• 会自动匹配名字相同的column
• 不是很好控制，尤其是复杂起来，别用

  USE sql_store;
  SELECT
  	o.order_id,
      c.first_name
  FROM orders o
  NATURAL JOIN customers c

cross join

• 会把两个表的元素全都组合一遍，下面这个例子没有什么实际意义，如果想得到所有的排列组合的时候可以用这个命令

  USE sql_store;
  SELECT
      c.first_name,
      p.name AS product
  FROM customers c
  CROSS JOIN products p
  ORDER BY c.first_name
  
  -- 也可以这么写，效果是一样的，但是不推荐
  USE sql_store;
  SELECT
      c.first_name,
      p.name AS product
  FROM customers c, products p
  ORDER BY c.first_name

对row的处理

Unions

• 链接两个不同的query,把结果放在一个里面
• 必须确保两个部分选择的column数量是一样的。表头是由前一个表的名字决定的

  -- 例子：用年份区分是不是现在的
  USE sql_store;
  SELECT 
  	order_id, order_date,
      "Active" AS status
  FROM orders
  WHERE order_date >= "2019-01-01"
  UNION
  SELECT 
  	order_id, order_date,
      "Archived" AS status
  FROM orders
  WHERE order_date < "2019-01-01"
  
  -- 例子：用积分区别等级
  USE sql_store;
  SELECT 
  	customer_id, first_name, points,
      "Bronze" AS type
  FROM customers
  WHERE points < 2000 
  UNION
  SELECT 
  	customer_id, first_name, points,
      "Silver" AS type
  FROM customers
  WHERE points BETWEEN 2000 AND 3000
  UNION
  SELECT 
  	customer_id, first_name, points,
      "Gold" AS type
  FROM customers
  WHERE points > 3000 
  ORDER BY first_name

插入，修改，删除data

• datatype里面 varchar（50），上限是50，写多少就存多少。如果是char（50）的话就肯定占50
  • PK：primary key
  • NN：not null，必须项目
  • AI：auto ++，一般加在PK里面
  • 如果这个值有默认值，可以直接用default关键字添加

插入row

• DEFAULT可以自己选择id
• 可以通过自定义的项目来跳过table里面默认的部分或者以及id值,也可以改变插入的顺序

  USE sql_store;
  INSERT INTO customers(
  	first_name,
      last_name,
      birth_date,
      address,
      city,
      state)
  VALUES ("John",
          "Smith",
          "1990-01-01",
          "address",
          "city",
          "CA")

插入多行

直接往后加就可以

USE sql_store;
INSERT INTO shippers(name)
VALUES ("shipper1"),
		("shipper2"),
        ("shipper3")

把data插入多个table

• 因为table之间是有关系的，会有parent和child table，也就是parent里面多了一行的话，child里面也必须多一行
• LAST_INSERT_ID()得到上次插入的id

USE sql_store;
INSERT INTO orders(customer_id,order_date,status)
VALUES(3,"2020-05-19",1);

INSERT INTO order_items
VALUES
	(LAST_INSERT_ID(),1,1,2.95),
    (LAST_INSERT_ID(),2,1,3.95)

copy data

USE sql_store;
CREATE TABLE orders_archived AS
SELECT * FROM ordersorders_archived

• 注意，以上这个方法的id不会被标记成PK，也不会自己增加

USE sql_store;
INSERT INTO orders_archived
SELECT * FROM orders
WHERE order_date < "2019-01-01"

• 这个方法可以用INSERT INTO来只copy一部分data到新的表里面

-- 创建表+JOIN+条件判断
CREATE TABLE invoices_archived AS
SELECT 
	c.name,
    i.payment_date
FROM invoices i
JOIN clients c
	USING (client_id)
WHERE i.payment_date IS NOT NULL

update数据

• Update一行

  UPDATE invoices
  SET payment_total = DEFAULT, payment_date = due_date
  WHERE invoice_id = 1

• update多行，选择多行的条件即可

  UPDATE invoices
  SET payment_total = invoice_total * 0.5, payment_date = due_date
  WHERE client_id IN (3,4)

• subquery来更新数据

  • 比如需要从名字知道id，再从id得到需要更改的行

    USE sql_invoicing;
    UPDATE invoices
    SET payment_total = invoice_total * 0.5, payment_date = due_date
    WHERE client_id = 
    			(SELECT client_id
    			FROM clients
    			WHERE name = "Myworks")
    
    -- 需要更新多个值得实惠
    UPDATE invoices
    SET payment_total = invoice_total * 0.5, payment_date = due_date
    WHERE client_id IN 
    			(SELECT client_id
    			FROM clients
    			WHERE state IN ("CA","NY"))

删除data

• 注意不加条件会全部删除的

  DELETE FROM invoices
  WHERE client_id =
  (SELECT *
  FROM clients
  WHERE name = "Myworks")