精华内容
下载资源
问答
  • SQL 联表查询

    2016-12-07 11:21:42
    SQL总结(二)连表查询 连接查询包括合并、内连接、外连接和交叉连接,如果涉及多表查询,了解这些连接的特点很重要。 只有真正了解它们之间的区别,才能正确使用。 1、Union UNION 操作符用于合并两个...

    SQL总结(二)连表查询

    连接查询包括合并、内连接、外连接和交叉连接,如果涉及多表查询,了解这些连接的特点很重要。

    只有真正了解它们之间的区别,才能正确使用。

    1、Union

    UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

    UNION 运算符通过组合其他两个结果表(例如 TABLE1  TABLE2)并消去表中任何重复行而派生出一个结果表。

     ALL  UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2

    注意:使用UNION时,两张表查询的结果有相同数量的列、列类型相似。

    学生表信息(Students):

    ID Name Age City MajorID
    101 Tom 20 BeiJing 10
    102 Lucy 18 ShangHai 11

     

     

     

    教师表信息(Teachers):

     ID  Name
     101  Mrs Lee
     102  Lucy

     

     

     

    预置脚本:

    INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(101,'Tom',20,'BeiJing',10)
    INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(102,'Lucy',18,'ShangHai',11)
    
    INSERT INTO Teachers(ID,Name) VALUES(101,'Mrs Lee')
    INSERT INTO Teachers(ID,Name) VALUES(102,'Lucy')

     

    1)基本UNION查询,查询学校教师、学生的总的信息表,包括ID和姓名

    SELECT ID,Name FROM Students
    UNION
    SELECT ID,Name FROM Teachers

    查询结果:

     ID  Name
    101 Mrs Lee
    101 Tom
    102  Lucy

     

     

     

     

    2)带条件的UNION查询,也可以查询同一张表,查询年龄为18,23岁的学生信息

    SELECT ID,Name FROM Student WHERE Age=18
    UNION
    SELECT ID,Name FROM Student WHERE Age=23

    当然,这可以使用IN或者OR很容易实现,这里只是点到,以后遇到复杂查询,相信你会用到。

    3)查询教师学生全部姓名

     因为UNION只会选择不同的值,如果学生中和教师中有重名的情况,这就需要UNION ALL

    SELECT Name FROM Students
    UNION ALL
    SELECT Name FROM Teachers

    查询结果:

     ID  Name
    101 Tom
    102 Lucy
    101 Mrs Lee
    102  Lucy

     

     

     

      

     

    2、INNER JOIN(内连接)

    INNER JOIN(内连接),也成为自然连接

    作用:根据两个或多个表中的列之间的关系,从这些表中查询数据。

    注意: 内连接是从结果中删除其他被连接表中没有匹配行的所有行,所以内连接可能会丢失信息。

    重点:内连接,只查匹配行。

    语法:(INNER可省略)

    SELECT fieldlist
    FROM table1 [INNER] join table2
    ON table1.column=table2.column

    学生表信息(Students):

    ID Name Age City MajorID
    101 Tom 20 BeiJing 10
    102 Lucy 18 ShangHai 11

     

     

     

    专业信息表(Majors): 

    ID  Name
    10 English
    12 Computer

     

     

     

     预置脚本:

    复制代码
    DELETE FROM Students
    INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(101,'Tom',20,'BeiJing',10)
    INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(102,'Lucy',18,'ShangHai',11)
    
    DELETE FROM Majors
    INSERT INTO Majors(ID,Name) VALUES(10,'English')
    INSERT INTO Majors(ID,Name) VALUES(12,'Computer')
    复制代码

     

    实例:查询学生信息,包括ID,姓名、专业名称

    SELECT Students.ID,Students.Name,Majors.Name AS MajorName
    FROM Students INNER JOIN Majors
    ON Students.MajorID = Majors.ID

     查询结果:

    ID Name MajorName
    101 Tom English

     

     

    根据结果可以清晰看到,确实只有匹配的行。学生Lucy的信息丢失了。

     

    但是,inner join也会产生重复数据。如果将Majors表的主键约束去掉,可以插入重复的ID,如:

    DELETE FROM Majors
    INSERT INTO Majors(ID,Name) VALUES(10,'English')
    INSERT INTO Majors(ID,Name) VALUES(10,'Computer')

    继续执行上面的关联语句,结果为:

    ID Name MajorName
    101 Tom English
    101 Tom Computer

     

     

     

    如果是LEFT JOIN也会有重复记录,其结果为:

    ID Name MajorName
    101 Tom English
    101 Tom Computer
    102 Lucy NULL

     

     

     

    RIGHT JOIN 结果与INNER JOIN一样。

    后续我们会深入研究JOIN的具体原理。

    3、外连接

    与内连接相比,即使没有匹配行,也会返回一个表的全集。

    外连接分为三种:左外连接,右外连接,全外连接。对应SQL:LEFT/RIGHT/FULL OUTER JOIN。通常我们省略outer 这个关键字。写成:LEFT/RIGHT/FULL JOIN。

    重点:至少有一方保留全集,没有匹配行用NULL代替。

    1)LEFT OUTER JOIN,简称LEFT JOIN,左外连接(左连接)

    结果集保留左表的所有行,但只包含第二个表与第一表匹配的行。第二个表相应的空行被放入NULL值。

    依然沿用内链接的例子

    (1)使用左连接查询学生的信息,其中包括学生ID,学生姓名和专业名称。

    SELECT Students.ID,Students.Name,Majors.Name AS MajorName
    FROM Students LEFT JOIN Majors
    ON Students.MajorID = Majors.ID

     

    结果:

    ID

    Name

    MajorName

    101

    Tom

    English

    102

    Lucy

    NULL

     

     

     

     

     

     结论:

    通过结果,我们可以看到左连接包含了第一张表的所有信息,在第二张表中如果没有匹配项,则用NULL代替。 

     

    2)RIGHT JOIN(right outer join)右外连接(右连接)

    右外连接保留了第二个表的所有行,但只包含第一个表与第二个表匹配的行。第一个表相应空行被入NULL值。

    右连接与左连接思想类似。只是第二张保留全集,如果第一张表中没有匹配项,用NULL代替

    依然沿用内链接的例子,只是改为右连接

    (1)使用右连接查询学生的信息,其中包括学生ID,学生姓名和专业名称。

    SELECT Students.ID,Students.Name,Majors.Name AS MajorName
    FROM Students RIGHT JOIN Majors
    ON Students.MajorID = Majors.ID

    查询结果:

    ID

    Name

    MajorName

    101

    Tom

    English

    NULL

    NULL

    Computer

     

     

     

     

     

     

    通过结果可以看到,包含了第二张表Majors的全集,Computer在Students表中没有匹配项,就用NULL代替。 


    3)FULL JOIN (FULL OUTER JOIN,全外连接)

    全外连接,简称:全连接。会把两个表所有的行都显示在结果表中

    1)使用全连接查询学生的信息,其中包括学生ID,学生姓名和专业名称。

    SELECT Students.ID,Students.Name,Majors.Name AS MajorName
    FROM Students FULL JOIN Majors
    ON Students.MajorID = Majors.ID

    查询结果: 

    ID

    Name

    MajorName

    101

    Tom

    English

    102

    Lucy

    NULL

    NULL

    NULL

    Computer

     

     

     

     

     

     

     

     

    包含了两张表的所有记录,没有记录丢失,没有匹配的行用NULL代替。

    4、CROSS JOIN(交叉连接)

    交叉连接。交叉连接返回左表中的所有行,左表中的每一行与右表中的所有行组合。交叉连接也称作笛卡尔积。 

    简单查询两张表组合,这是求笛卡儿积,效率最低。

    笛卡儿积:笛卡尔乘积,也叫直积。假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有,如果A表示某学校学生的集合,B表示该学校所有课程的集合,则A与B的笛卡尔积表示所有可能的选课情况。

    1)交叉连接查询学生的信息,其中包括学生ID,学生姓名和专业名称。

    SELECT Students.ID,Students.Name,Majors.Name AS MajorName
    FROM Students CROSS JOIN Majors

    查询结果:

    ID

    Name

    MajorName

    101

    Tom

    English

    102

    Lucy

    English

    101

    Tom

    Computer

    102

    Lucy

    Computer

     

     

     

     

     

     

     

     

     

     

    2)查询多表,其实也是笛卡儿积,与CROSS JOIN等价,以下查询同上述结果一样。

    这个可能很常见,但是大家一定要注意了,这样就查询了两张表中所有组合的全集。

    SELECT Students.ID,Students.Name,Majors.Name AS MajorName
    FROM Students,Majors

    3)加了查询条件

    注意:在使用CROSS JOIN关键字交叉连接表时,因为生成的是两个表的笛卡尔积,因而不能使用ON关键字,只能在WHERE子句中定义搜索条件。

    SELECT Students.ID,Students.Name,Majors.Name AS MajorName
    FROM Students CROSS JOIN Majors
    WHERE Students.MajorID = Majors.ID

    查询结果:

    ID

    Name

    MajorName

    101

    Tom

    English

     

     

     

     

    查询结果与INNER JOIN一样,但是其效率就慢很多了。

    5、全部实例脚本 

    复制代码
    CREATE DATABASE TestDB
    
    USE TestDB
    
    ------------------------------------------
    --创建相关表
    IF OBJECT_ID('Students','U') IS NOT NULL
    DROP TABLE Students
    --学生信息表
    CREATE TABLE Students(
    ID int primary key not null,
    Name nvarchar(50),
    Age int,
    City nvarchar(50),
    MajorID int
    )
    
    
    --专业信息表
    IF OBJECT_ID('Majors','U') IS NOT NULL
    DROP TABLE Majors
    
    CREATE TABLE Majors(
    ID int primary key not null,
    Name nvarchar(50)
    )
    
    --教师信息表
    IF OBJECT_ID('Teachers','U') IS NOT NULL
    DROP TABLE Teachers
    CREATE TABLE Teachers(
        ID int primary key not null,
        Name nvarchar(20) not null
    )
    
    --预置数据
    DELETE FROM Students
    INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(101,'Tom',20,'BeiJing',10)
    INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(102,'Lucy',18,'ShangHai',11)
    
    DELETE FROM Majors
    INSERT INTO Majors(ID,Name) VALUES(10,'English')
    INSERT INTO Majors(ID,Name) VALUES(12,'Computer')
    
    DELETE FROM Teachers
    INSERT INTO Teachers(ID,Name) VALUES(101,'Mrs Lee')
    INSERT INTO Teachers(ID,Name) VALUES(102,'Lucy')
    
    
    
    SELECT ID,Name FROM Students
    UNION
    SELECT ID,Name FROM Teachers
    
    SELECT ID,Name FROM Students
    UNION ALL
    SELECT ID,Name FROM Teachers
    
    --内连接
    SELECT Students.ID,Students.Name,Majors.Name AS MajorName
    FROM Students INNER JOIN Majors
    ON Students.MajorID = Majors.ID
    
    --左连接
    SELECT Students.ID,Students.Name,Majors.Name AS MajorName
    FROM Students LEFT JOIN Majors
    ON Students.MajorID = Majors.ID
    
    --右连接
    SELECT Students.ID,Students.Name,Majors.Name AS MajorName
    FROM Students LEFT JOIN Majors
    ON Students.MajorID = Majors.ID
    
    --全连接
    SELECT Students.ID,Students.Name,Majors.Name AS MajorName
    FROM Students FULL JOIN Majors
    ON Students.MajorID = Majors.ID
    
    --交叉连接
    SELECT Students.ID,Students.Name,Majors.Name AS MajorName
    FROM Students CROSS JOIN Majors
    
    --交叉连接
    SELECT Students.ID,Students.Name,Majors.Name AS MajorName
    FROM Students CROSS JOIN Majors
    WHERE Students.MajorID = Majors.ID
    
    --一次查询多表
    SELECT Students.ID,Students.Name,Majors.Name AS MajorName
    FROM Students,Majors
    复制代码
    展开全文
  • sql联表查询

    2018-09-13 17:39:02
    用户(user) id (int) 用户id name (varchar) 用户名 gender   投资(orders) id 订单号 user_id 用户id amount(int) 该笔订单投资额 add_time   1. 求所有投资用户中...

    题目描述:

    用户表(user)

    id (int) 用户id
    name (varchar) 用户名
    gender  

    投资表(orders)

    id 订单号
    user_id 用户id
    amount(int) 该笔订单投资额
    add_time  

    1. 求所有投资用户中,投资总额排名前10位的用户,按投资金额倒叙排列,

    输出格式为:

    用户名,投资金额

    张三,800000

    李四,600000

    ......

    select b.name, sum(a.account) from orders a 
    left join user b on a.user_id=b.id 
    group by a.user_id
    order by sum(a.amount) desc 
    limit 10;

    2. 给出投资榜单第一名用户的单笔平均投资额

    select user_id, avg(amount) 
    from orders o 
    group by o.user_id 
    order by sum(amount) desc 
    limit 1;
    select avg(amount) from orders 
    where user_id=
    (select user_id from orders group by user_id order by sum(amount) desc limit 1);

     

    note:

    1. order by是sql语句,用于对指定的列的结果集进行排序,默认升序

        group by是函数,合计函数 (比如 SUM) 常常需要添加 GROUP BY 语句

        limit,显示num条检索数据

    select accountid,sum(amount) from new_mayi.accountitem group by accountid order by sum(amount) desc limit 10;

        limit的其它用法:

    select * from Customer LIMIT 10 ;--检索前10行数据,显示1-10条数据
    select * from Customer limit 5,10 ;--检索从第6行开始向前加10条数据,共显示id为6,7....15

    2. left join关键字会从左表(TABLE_NAME1)那里返回所有的行,即使右表(TABLE_NAME2)中没有匹配的行

    SELECT COLUMN_NAME(S) FROM TABLE_NAME1 LEFT JOIN TABLE_NAME2 ON TABLE_NAME1.COLUMN_NAME=TABLE_NAME2.COLUMN_NAME

    3. sql语法顺序

      SELECT select_list
      [ INTO new_table ]
      FROM table_source
      [ WHERE search_condition ]
      [ GROUP BY group_by_expression ]
      [ HAVING search_condition ]
      [ ORDER BY order_expression [ ASC | DESC ] ]

     

     

    展开全文
  • SQL联表查询

    2019-10-01 09:08:12
    表查询和单表查询最打区别就是多表查询多了join on 语句,讲专业点叫连接。连接常用的有三种inner、left、right,这里我们不展开讲三种方式的区别(因为我自己也讲不清楚!如果想知道请自行谷歌笛卡尔积、SQL多表...

      数据库中最最常用的语法----select.简单的select语法很直白:

        

    select column from table where expression;

     

        从((from)存储数据的地方(table)按照(where)一定的条件(expression)查找(select)我要的数据(column);

      但是在实际工作中用到的比较多的往往还是多联表查询,所以在这里记下自己学习多联表查询的心得。

      首先聊一聊笛卡尔积,这是几乎所有数据库书籍在讲多联表查询时第一个要讲的东西,我等P民也只能是把笛卡尔积放在第一位了。用初中数学来解释笛卡尔积其实就是因式展开(各位大神不要打我),类似于下面这种:

      A=(a+b),B=(c+d),A*B=ac+ad+bc+bd;

    嗯虽然不一样,但是其实表面上就是这样过的。

      然后基础语法其实和单表查询还是差不多的:

      

    select column from table1 (方式) join table2 on expression;

     

    多表查询和单表查询最打区别就是多表查询多了join on 语句,讲专业点叫连接。连接常用的有三种inner、left、right,这里我们不展开讲三种方式的区别(因为我自己也讲不清楚!如果想知道请自行谷歌笛卡尔积、SQL多表联合查询十篇文章九篇会讲的),只讲怎么用,用哪个好。

      首先,放出结论(一般情况下):

        1)、如果table1符合条件数据量远大于table2,且table2数据非常多,用left join;

        2)、如果table1符合条件数据量远小于table2,且table1数据非常多,用right join;

        3)、如果一样多且数据量不多推荐使用inner join;

        4)、但是如果都非常多请用联合查询 + where,后面会讲到。

      当然结论并非绝对,会受到两表中Null数据行的影响,但是我想说的是!实际中Null数据行是比较少的,所以这个结论大致上还是可以用的!!!不要问我为什么,就是这么粗暴!就是这么任性!

      最后放大招--嵌套查询

      嵌套查询顾名思义就是个嵌套,以内查询语句的输出作为外查询语句的输入或条件语句,以求达到缩减数据量的目的。在实际应用中,如果两表的数据量非常大,那么强烈推荐嵌套查询。假设有下表;

    那么嵌套查询的典型语法如下:

    在where条件中使用嵌套查询

    select 
        account 
    from 
        userbindinfo 
    where 
        user_id 
    in 
    (
        select 
            user_id    -- 该处内查询语句的输出必须与外部查询语句的条件一一对应
        from 
            users 
        where 
            user_type = 1
    )        

     

      

    转载于:https://www.cnblogs.com/zhoujm928/p/5467422.html

    展开全文
  • Spring Hibernate JPA 利用原生sql联表查询 测试类 代码片. @Test public void getDeviceStatusList() throws Exception { String tenantId = "1e88104b7aeab10b6debf3a73521807"; List<Object...

    Spring Hibernate JPA 利用原生sql联表查询

    测试类 代码片.

    	@Test
        public void getDeviceStatusList() throws Exception {
            String tenantId = "1e88104b7aeab10b6debf3a73521807";
            List<Object[]> list = deviceDao.getDeviceStatusList(tenantId);
            // 对象类型转换
            List<DeviceCustomerAttributeKvEntity> testViews = EntityUtil.castEntity(list, DeviceCustomerAttributeKvEntity.class);
            for(DeviceCustomerAttributeKvEntity dca: testViews) {
                System.out.println(dca.toString());
            }
        }
    

    查询语句代码片

    	// 查询设备状态列表 Created by Wenhui Huang 2019/03/11
        @Query(nativeQuery = true, value = "select d.name as deviceName, d.device_address as deviceAddress, d.device_longitude as deviceLongitude, " +
                "d.device_latitude as deviceLatitude, d.created_ts as deviceCreatedTs, d.updated_ts as deviceUpdatedTs, c.title as CustomerTitle, a.bool_v as CustomerBoolV from device d " +
                                           "left join customer c on d.customer_id = c.id " +
                                           "left join attribute_kv a on a.entity_id = d.id and a.entity_type = 'DEVICE' " +
                                           "where " +
                                           "d.tenant_id = :tenantId " +
                                           "order by d.id")
        List<Object[]> getDeviceStatusList(@Param("tenantId") String tenantId);
    

    实体转换工具类 代码片

    /**
     * @author Created by Wenhui
     * @description 实体转换工具类
     * @date 2019/3/11
     */
    public class EntityUtil {
        //转换实体类
        public static <T> List<T> castEntity(List<Object[]> list, Class<T> clazz) throws Exception {
            List<T> returnList = new ArrayList<T>();
            Field[] fields = clazz.getDeclaredFields();
            Object[] co = new Object[fields.length];
            Class[] c2 = new Class[co.length];
            
            //确定构造方法
            for (int i = 0; i < co.length; i++) {
                co[i] = fields[i].getType();
                // 获取对应属性类型对应class
                Class<?> classs=Class.forName(co[i].toString().substring(6, co[i].toString().length()));
                c2[i] = classs;
            }
    
            for (Object[] o : list) {
                Constructor<T> constructor = clazz.getConstructor(c2);
                returnList.add(constructor.newInstance(o));
            }
            
            return returnList;
        }
    
    }
    
    展开全文
  • JPA踩坑记:Spring Data Jpa 原生SQL联表查询返回自定义DTO 关键字: JPA复杂查询,JPA返回自定义实体,JPA返回自定义DTO,JPA联表查询,JPA原生SQL查询,JPA踩坑 新冠疫情之下,闲来无事研究了一下JPA,发现坑还是不少的,...
  • sql联表查询常用

    2018-05-08 15:41:20
    有三个:T_USER, t_group, T_USER_GROUP,求分组某个平均数:[html] view plain copy3./*各部门平均奖金*/ select t_group.groupname as "部门", AVG(T_USER.bonus) AS "平均奖金"...
  • SQL 联表查询问题

    2017-05-15 10:27:34
    AAA: 字段:BID 1 2,3 3 BBB: 字段:BID 字段:NAME 1 qq 2 qq 3 ww 要求AAA含有BBB中NAME=qq的BID值. 结果:输出AAA 第1行和第2行
  • 上面的语句会让teacher_region_info中如果没有对应的teacher_id就使teacher_info的数据也不显示出来了,如何更改代码? 找到了答案: SELECT teacher_info.teacher_id, tearcher_name, teacher_...
  • 比如说有两张。 A有4个字段,B有5个字段。 查询到的结果只想显示A的3个字段,B的2个字段 SQL语句咋写?
  • select s.so_no, (case when left(s.so_no, 3) = N'SCK' then (s.TOTALAMOUNT + (case when discount_name = N'Money box' then ...二次查询结果不一样,第一次没有正确获取数据???为什么会这样高人指点下。谢谢
  • source:插入数据channel:插入数据查询每个channel对应的访问数和注册数:SELECT DISTINCT(channel_name) as cname, identification as iden, (select count(*) from source WHERE create_time &gt;= '2018...
  • 关于SQL联表查询

    2009-03-04 10:24:45
    两张通过NATURAL JOIN连接的时候,相当于有个隐含的WHERE子句,对两张中同名的对应列相比较看是否相等。 二、CROSS JOIN(创建笛卡尔积) 对两张通过交叉联合产生第三张返回结果集的。相当于普通的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 746
精华内容 298
关键字:

sql联表查询