mysql多表查询打卡

[jiefancis]

mysql单表

查找

• 精准查询 select from user where title --查询一title查询所有数据 select from user where title='今日新闻' --查询title为今日新闻的一条数据

• 多条件查询 select from user where title='今日新闻' and age='28' 与 select from user where title='今日新闻' or age='28' 或 select * from user where id in (1,2)

  as取别名 select * from users u , posts as p where u.id = p.id

• 模糊查询 select from user where title like '%龙%' --查询出所有含有“龙”的记录 select from user where realname like '%文%' and realname like '%龙%' --所查询字段 + like '%龙%' and 所查询字段 + like '%文%'

• 分页查询 select * from users where title like '%龙%' limit page,pageSize page是第几页，size每页几条， 单纯的limit（没有where条件）查询的可以不加where，where limit会报错

增 插入

insert into tableName (fieldName1, fieldName2, fieldName3) values(value1, value2, value3) 避免重复插入？

• 多条插入 insert into tableName values(1,2,3,4,5,6) insert into tableName values(1,2,3,4,5,6) insert into tableName values(1,2,3,4,5,6) insert into tableName values(1,2,3,4,5,6) 等价于 insert into tableName (fieldName1, fieldName2, fieldName3) values() , (), (), ()

改 修改

update users set name ='', pass='',avator='' where id = ; 修改多条数据呢？

删

delete from users where id = ''

排序

select * from users where name like '%张%' order by fieldName limit page,pageSize

条件语句

• where 筛选

  在where语句筛选的结果集中查找满足条件的数据并聚合

  SELECT a.name,a.address,a.date,b.chinese,b.math,b.english FROM tb_demo065 AS a INNER JOIN tb_demo065_tel AS b on a.id=b.id WHERE b.id=(SELECT id FROM  tb_demo065 WHERE tb_demo065.name='xxx')

• group by 分组 自动去重。利用HAVING语句过滤分组数据

• order by 排序

• on (需要在left join、 right joine、 union中使用)

• having 过滤group by分组数据

关键字

• 限定范围

  • in 范围之内，NOT IN 表示之外
  • between

  select from users u where u.id in (2,3,4,5,6) 等价于 select from users u where id between 2 and 6

mysql多表

多表查询谓词

• ANY
• SOME
• ALL

多表关联查询

• join 合并结果集（子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段）

  • left join 即使右表中没有匹配，也从左表返回所有的行 取左并集（两者交集和左集合）
  • right join 即使左表中没有匹配，也从右表返回所有的行 取右并集（两者交集和右集合）
  • inner join 如果表中至少有一个匹配，则返回行 取交集（两个集合的乘积）
  • full join 只要其中一个表中存在匹配，则返回行（mysql不支持）

  隐式内连接 select u.name, p.title from users u, posts p where u.id = p.id 显式内连接 select u.name, p.title from users u inner join posts p on u.id = p.id

• join, inner join, cross join 等价 select * from users u join posts p; 上面查询语句的结果是两个数据表的乘积（是users数据表中所有数据出现的次数是p表中数据的个数） left 和 right才是p和u一对一

  当有WHERE, ON或USING条件的时候一般不建议使用。当数据表中数据太多会非常卡，建议使用left join 和 right join

  • 引申：我们可以用左表没有on匹配则显示null的规律, 来找出所有在右表，不在左表的纪录， 注意用来判断的那列必须声明为not null的。（这里是u.id） select * from users u right join posts as p on u.id = p.id where u.id is NULL

• union 操作符用于合并两个或多个 SELECT 语句的结果集。 请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。 列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

  注释：UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
  
  默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL
  
  select u.name from users u union select p.name from posts p (会自动去重)
  select u.name from users u union all select p.name from posts p (不会去重 )

子查询 （也叫嵌套查询）（in exits from）

子查询 当一个查询是另一个查询的条件时，称之为子查询

- in 简单嵌套查询

    分析： 先查询张三的id 再根据他的id 查询订单

    select id  from user where username = '张三';
    select * from orders where user_id = 3;--结果为3

    以上执行两部太麻烦，两者和2 为1
    select * from orders where user_id = ( select id  from user where username = '张三');

    - 一个字段多个值如何查询
    select * from posts where id in (1,2,3)
    select * from posts where id = 1 or id = 2 or id =3
    select * from posts where id in (select id from users where name like '%张三%')

- in 复杂嵌套查询
    - 多表之间的嵌套查询

    ```
    test_expression[NOT] IN{
        subquery （参数说明：test_expression指SQL表达式，subquery包含某结果集的子查询）
    }
    ```

    多表嵌套查询的原理:无论是多少张表进行嵌套，表与表之间一定存在某种关联，通过WHERE子句建立此种关联实现查询

• exits EXISTS用于检查 子查询 是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False EXISTS 指定一个子查询，检测 行 的存在。

  SELECT c.CustomerId,CompanyName FROM Customers c WHERE EXISTS( SELECT OrderID FROM Orders o WHERE o.CustomerID = c.CustomerID )

  • 以name字段值为判断条件，查找在users表中出现但不在posts表出现的数据） select from users where name not in (select name from posts); 等价于 select from users u where not exits (select name from posts p where p.name = u.name)

使用子查询作派生的表

• 在实际项目开发过程中经常用到从一个信息较为完善的表中派生出一个只含有几个关键字段的信息表，通过子查询就可以来实现这一目标 select people.name from (select * from posts p ) as people;

  SELECT people.name,people.chinese,people.math,people.english FROM (SELECT name,chinese,math,english FROM tb_demo071) AS people

使用子查询作表达式

SELECT (SELECT AVG(chinese) FROM tb_demo071) AS yuwen ,(SELECT AVG(english) FROM tb_demo071) AS yingyu,(SELECT AVG(math) FROM tb_demo071) AS shuxue FROM tb_demo071

算术表达式、聚合函数

AVG 平均数 count求和

reference

https://juejin.cn/post/6918624554201907207 in exists