创建表的时候尽量加上create_time字段, 为create_time设置默认值CURRENT_TIMESTAMP

CRUD操作

• Create(增)

  • 单条插入

    INSERT INTO 表名(字段1, 字段2..) VALUES(值1, 值2)
    
    INSERT INTO class_1(name) VALUES('name_1')

  • 多条插入

    INSERT INTO 表名(字段1, 字段2..) VALUES(值1, 值2), (值1, 值2)

• Retrieve(查)

  • 获取所有记录

    SELECT 字段1, 字段2 FROM 表名
    
    SELECT name FROM class_1
    SELECT * FROM class_1

  • 条件查询(WHERE, AND , OR)

    SELECT 字段1, 字段2 FROM 表名 WHERE 表达式
    
    SELECT * FROM class_1 WHERE name="name_1" AND id=8

  • 模糊匹配(LIKE, %)

    模糊匹配有性能问题, 表记录如果比较多, 查询速度很慢

    SELECT 字段1, 字段2 FROM 表名 WHERE 表达式
    
    SELECT * FROM class_1 WHERE name LIKE "%m%" 

  • 限制返回条数(LIMIT)

    查询表达式 LIMIT 数量

  • 过滤重复值(DISTINCT)

    对查询的结果进行过滤.

    如果指定多个字段, 会对多个字段联合进行过滤

    SELECT DISTINCT 字段1, 字段2 FROM 表名 [条件语句]
    
    SELECT distinct name, create_time  FROM class_1 WHERE name like '%m%'

  • 排序问题

    对查询的结果进行排序

    • 升序(ASC)

      数据库默认是升序的

      查询表达式 ORDER BY 字段 ASC

    • 降序(DESC)

      查询表达式 ORDER BY 字段 DESC

  • 获取查询结果的条数(COUNT)

    SELECT COUNT(*) FROM 表名 [条件语句]

• Update(更新)

  UPDATE 表名 SET 字段1=新值, 字段2=新值 WHERE 表达式
  
  UPDATE class_1 SET name='name_0' WHERE id=8

• Delete(删除)

  DELETE FROM 表名 WHERE 表达式
  
  DELETE FROM class_1 WHERE name='name_0'

集合操作

• 并集UNION

  子语句SELECT 必须拥有相同数量的列(字段), 且列的数据类型也相同

  SELECT name from class_1 WHERE name is not NULL
  UNION
  SELECT name from class_2 WHERE name is not NULL

• 交集(JOIN, INNER JOIN)

  SELECT s1.name FROM
  (SELECT name from class_1 WHERE name is not NULL) as s1
  JOIN
  (SELECT name from class_2 WHERE name is not NULL) as s2
  ON s1.name=s2.name

• 差集(LEFT JOIN, RIGHT JOIN)

  • A对B的差集(LEFT JOIN)

    SELECT * FROM // s1对s2的差集, select就可以使用s1.name
    (SELECT name from class_1 WHERE name is not NULL) as s1
    LEFT JOIN
    (SELECT name from class_2 WHERE name is not NULL) as s2
    ON s1.name=s2.name
    WHERE s2.name is NULL   // 限定s1有, s2没有的记录

  • B对A的差集(RIGHT JOIN)

    SELECT * FROM
    (SELECT name from class_1 WHERE name is not NULL) as s1
    RIGHT JOIN
    (SELECT name from class_2 WHERE name is not NULL) as s2
    ON s1.name=s2.name
    WHERE s1.name is NULL

• 补集

  A与B的补集 = A与B的并集 - A与B的交集

  A与B的补集 = A对B的差集 + B对A的差集 √

  SELECT s1.name FROM
  (SELECT name from class_1 WHERE name is not NULL) as s1
  LEFT JOIN
  (SELECT name from class_2 WHERE name is not NULL) as s2
  ON s1.name=s2.name
  WHERE s2.name is NULL
  
  UNION
  
  SELECT s2.name FROM
  (SELECT name from class_1 WHERE name is not NULL) as s1
  RIGHT JOIN
  (SELECT name from class_2 WHERE name is not NULL) as s2
  ON s1.name=s2.name
  WHERE s1.name is NULL