精华内容
下载资源
问答
  • 1.首先建立学生: 2.需要用到的相关包: 3.mybatis.xml配置: <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" ...

    1.首先建立学生表:
    在这里插入图片描述
    2.需要用到的相关包:
    在这里插入图片描述
    3.mybatis.xml配置:

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-config.dtd">       
       <configuration>  
       <settings>
       <setting name="logImpl" value="STDOUT_LOGGING" />
       </settings>  
       <typeAliases>
       <package name="com.po"/>
       </typeAliases>
           <!--    定义环境-->
        <environments default="mysql">
            <environment id="mysql">
    <!--          定义事务-->
                <transactionManager type="JDBC"></transactionManager>
    <!--定义数据源            -->
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/mybatisdb?characterEncoding=UTF-8"/>
                    <property name="username" value="root"/>
                    <property name="password" value="wu"/>
                </dataSource>
            </environment>
        </environments>
        <mappers >
        <mapper resource="com/mapper/StudentMapper.xml"/>
        </mappers>
       </configuration>
    

    4.DButil里面的相关内容:

    package com.util;
    
    import java.io.Reader;
    import java.util.List;
    
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    
    
    
    public class DButil {
    	 public static SqlSessionFactory factory;
    	    static {
    	        //通过字符流的读取将主配置文件读取到程序中;
    	        try {
    	            Reader reader= Resources.getResourceAsReader("mybatis.xml");
    	            
    	            //创建会话工厂建造者对象
    	            SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
    	            //得到会话工厂对象
    	            factory=builder.build(reader);
    	        } catch (Exception e) {
    	            e.printStackTrace();
    	        }
    	    }
    	    //得到链接对象
    	    public static SqlSession getSession(boolean auto){
    	        return  factory.openSession(auto);
    	    }
    		
    }
    
    

    5.com.po包里面对学生信息进行封装;

    package com.po;
    
    public class Student {
    	private int id;
    	private String username;
    	private String sex;
    	private String age;
    	private String banji;
    	private String hobby;
    	public int getId() {
    		return id;
    	}
    	public void setId(int id) {
    		this.id = id;
    	}
    	public String getUsername() {
    		return username;
    	}
    	public void setUsername(String username) {
    		this.username = username;
    	}
    	public String getSex() {
    		return sex;
    	}
    	public void setSex(String sex) {
    		this.sex = sex;
    	}
    	public String getAge() {
    		return age;
    	}
    	public void setAge(String age) {
    		this.age = age;
    	}
    	public String getBanji() {
    		return banji;
    	}
    	public void setBanji(String banji) {
    		this.banji = banji;
    	}
    	public String getHobby() {
    		return hobby;
    	}
    	public void setHobby(String hobby) {
    		this.hobby = hobby;
    	}
    	public Student() {
    		super();
    		// TODO Auto-generated constructor stub
    	}
    	@Override
    	public String toString() {
    		return "Student [id=" + id + ", username=" + username + ", sex=" + sex + ", age=" + age + ", banji=" + banji
    				+ ", hobby=" + hobby + "]";
    	}
    	
    	
    }
    
    

    6.com.inter包里面写入对student相关操作:

    package com.inter;
    
    import java.util.List;
    
    import com.po.Student;
    
    public interface StudentInter {
    	public List<Student> select();
    	public void insert(Student student);
    	public void delete(int id);
    	public int update(Student student);
    	Student findwhere(Student student);
    	
    }
    
    

    7.在com.mapper里面通过xml方式写入增删改查以及多条件查询的方法:

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
            <!-- namespace指定接口的地址 -->
     <mapper namespace="com.inter.StudentInter">
     	 <sql id="select"><!-- id可以自己取 -->
     	 select * from student
     	 </sql>	
     	  
    	 <select id="select" resultType="Student">
    	 select * from student
    	 </select>	 
    	 <select id="selectById" parameterType="Integer" resultType="Student">
    		<include refid="select" /> where id=#{id}
    	 </select>
    	 
    	 <insert id="insert" parameterType="Student">
    	 <!-- #{id},#{uname},#{upass},#{sex}是test这个bean中定义的属性名 -->
    	 insert into student(username,sex,age,banji,hobby) values(#{username},#{sex},#{age},#{banji},#{hobby});
    	 </insert>
    	 
    	 <delete id="delete" parameterType="int">
    	 delete from student where id=#{id}
    	 </delete>
    	 
    	 <update id="update" parameterType="com.po.Student">
    	 update student set username=#{username},sex=#{sex},age=#{age},banji=#{banji},hobby=#{hobby} where id=#{id}
    	 </update>
    	 
    	 
    	 <select id="findwhere" parameterType="Student" resultType="Student">
    		 <include refid="select" />
    			
    				 <where>
    				 	<if test="banji!=null">
    				 		 banji like '%' #{banji} '%'
    				 	</if>
    				 	<if test="hobby!=null">
    				 		and hobby like #{hobby} '%'
    				 	</if>
    				 </where>
    					 
    	 </select>
     </mapper>
    

    8.在com.test里面编写测试类:

    package com.test;
    
    import java.util.List;
    
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    
    import com.inter.StudentInter;
    import com.po.Student;
    import com.util.DButil;
    
    public class Studentmain {
    
    	public static void main(String[] args) {
    		SqlSession session=DButil.getSession(true);
    		StudentInter inter=session.getMapper(StudentInter.class);
    		Student student=new Student();
    		student.setBanji("20");
    		student.setHobby("aa");
    		inter.findwhere(student);
    	}
    	@Test
    	public void insert() {
    	SqlSession session=DButil.getSession(true);
    	StudentInter inter=session.getMapper(StudentInter.class);
    	Student st=new Student();
    	st.setUsername("吴");
    	st.setSex("男");
    	st.setAge("21");
    	st.setBanji("24班");
    	st.setHobby("篮球");
    	inter.insert(st);
    	}
    	
    	@Test
    	public void delete() {
    		SqlSession session=DButil.getSession(true);
    		StudentInter inter=session.getMapper(StudentInter.class);
    		inter.delete(4);
    	}
    	
    	@Test
    	public void update() {
    		SqlSession session=DButil.getSession(true);
    		StudentInter inter=session.getMapper(StudentInter.class);
    		Student st=new Student();
    		st.setUsername("yang");
    		st.setSex("男");
    		st.setAge("23");
    		st.setBanji("14班");
    		st.setHobby("running");
    		st.setId(6);
    		inter.update(st);
    	}
    	@Test
    	public void select() {
    		SqlSession session=DButil.getSession(true);
    		StudentInter inter=session.getMapper(StudentInter.class);
    		List<Student> list=inter.select();
    		for (Student st : list) {
    			System.out.print("序号:"+st.getId()+";姓名:"+st.getUsername()+";sex:"+
    		st.getSex()+";age:"+st.getAge()+";班级:"+st.getBanji()+";爱好:"+st.getHobby());
    		}
    	}
    	
    	@Test
    	public void findwhere() {
    		SqlSession session=DButil.getSession(true);
    		StudentInter inter=session.getMapper(StudentInter.class);
    		Student student=new Student();
    		student.setBanji("20");
    		student.setHobby("aa");
    		inter.findwhere(student);
    		System.out.println(inter.findwhere(student));
    	}
    }
    
    

    9.最后的测试结果图:
    在这里插入图片描述

    展开全文
  • 多表查询查询结果来自于多张表,即多表查询查询:在SQL语句嵌套着查询语句,性能较差,基于某语句的查询结果再次进行查询联合查询:UNION交叉连接:笛卡尔乘积内连接:等值连接:让之间的字段以“等值”建立...

    本篇文章和大家一起了解一下MySQL数据库多表查询。有一定的参考价值,有需要的朋友可以参考一下,希望对大家有所帮助。

    568848a7ce2a44bb21df12d92062ebcd.png

    多表查询

    查询结果来自于多张表,即多表查询子查询:在SQL语句嵌套着查询语句,性能较差,基于某语句的查询结果再次进行的查询

    联合查询:UNION

    交叉连接:笛卡尔乘积

    内连接:

    等值连接:让表之间的字段以“等值”建立连接关系

    不等值连接:不等值连接查询就是无条件判断,若查询多个表内的数据,其中的数据不会同步,各自把各自的展现出来,没有任何关联。

    自然连接:去掉重复列的等值连接

    外连接:

    左外连接:FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col

    右外连接:FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col

    自连接:本表和本表进行连接查询

    子查询

    常用在WHERE子句中的子查询

    1、用于比较表达式中的子查询;子查询仅能返回单个值(查询s1表中大于平均年龄的人)MariaDB [hellodb]> select * from s1 where age > (select avg(age) from s1);

    +-------+--------------+-------+-----+--------+---------+-----------+

    | StuID | Name | phone | Age | Gender | ClassID | TeacherID |

    +-------+--------------+-------+-----+--------+---------+-----------+

    | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |

    | 4 | Ding Dian | NULL | 32 | M | 4 | 4 |

    | 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |

    | 6 | Shi Qing | NULL | 46 | M | 5 | NULL |

    | 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |

    | 24 | Xu Xian | NULL | 27 | M | NULL | NULL |

    | 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |

    +-------+--------------+-------+-----+--------+---------+-----------+

    7 rows in set (0.01 sec)

    2、查询结果嵌入到另一个表里,小数转换整数会四舍五入MariaDB [hellodb]> select avg(age) from s1 ; (查看s1表平均年龄)

    +----------+

    | avg(age) |

    +----------+

    | 25.0857 |

    +----------+

    1 row in set (0.00 sec)

    MariaDB [hellodb]> select * from teachers; (原来的表内容)

    +-----+---------------+-----+--------+

    | TID | Name | Age | Gender |

    +-----+---------------+-----+--------+

    | 1 | Song Jiang | 45 | M |

    | 2 | Zhang Sanfeng | 94 | M |

    | 3 | Miejue Shitai | 77 | F |

    | 4 | Lin Chaoying | 26 | F |

    +-----+---------------+-----+--------+

    4 rows in set (0.00 sec)

    MariaDB [hellodb]> update teachers set age=(select avg(age) from s1); (插入查询结果的表内容,没有指定字段会改掉所有)

    Query OK, 4 rows affected (0.00 sec)

    Rows matched: 4 Changed: 4 Warnings: 0

    MariaDB [hellodb]> select * from teachers;

    +-----+---------------+-----+--------+

    | TID | Name | Age | Gender |

    +-----+---------------+-----+--------+

    | 1 | Song Jiang | 25 | M |

    | 2 | Zhang Sanfeng | 25 | M |

    | 3 | Miejue Shitai | 25 | F |

    | 4 | Lin Chaoying | 25 | F |

    +-----+---------------+-----+--------+

    4 rows in set (0.00 sec)

    MariaDB [hellodb]> update teachers set age=48 where tid=4; (把tid为4的age修改为48做下面实验用)

    Query OK, 1 row affected (0.00 sec)

    Rows matched: 1 Changed: 1 Warnings: 0

    MariaDB [hellodb]> select * from teachers;

    +-----+---------------+-----+--------+

    | TID | Name | Age | Gender |

    +-----+---------------+-----+--------+

    | 1 | Song Jiang | 25 | M |

    | 2 | Zhang Sanfeng | 25 | M |

    | 3 | Miejue Shitai | 25 | F |

    | 4 | Lin Chaoying | 48 | F |

    +-----+---------------+-----+--------+

    4 rows in set (0.00 sec)

    MariaDB [hellodb]> update teachers set age=(select avg(age) from s1) where tid=4; (指定tid为4的age字段修改)

    Query OK, 1 row affected (0.00 sec)

    Rows matched: 1 Changed: 1 Warnings: 0

    MariaDB [hellodb]> select * from teachers;

    +-----+---------------+-----+--------+

    | TID | Name | Age | Gender |

    +-----+---------------+-----+--------+

    | 1 | Song Jiang | 25 | M |

    | 2 | Zhang Sanfeng | 25 | M |

    | 3 | Miejue Shitai | 25 | F |

    | 4 | Lin Chaoying | 25 | F |

    +-----+---------------+-----+--------+

    4 rows in set (0.00 sec)

    多表查询:

    用子循环查看s1表,显示teachers表年龄大于s1表平均年龄的人的信息。MariaDB [hellodb]> update teachers set age=45 where tid=1;

    Query OK, 1 row affected (0.00 sec)

    Rows matched: 1 Changed: 1 Warnings: 0

    MariaDB [hellodb]> update teachers set age=94 where tid=2;

    Query OK, 1 row affected (0.00 sec)

    Rows matched: 1 Changed: 1 Warnings: 0

    MariaDB [hellodb]> update teachers set age=77 where tid=3;

    Query OK, 1 row affected (0.00 sec)

    Rows matched: 1 Changed: 1 Warnings: 0

    MariaDB [hellodb]> select * from teachers;

    +-----+---------------+-----+--------+

    | TID | Name | Age | Gender |

    +-----+---------------+-----+--------+

    | 1 | Song Jiang | 45 | M |

    | 2 | Zhang Sanfeng | 94 | M |

    | 3 | Miejue Shitai | 77 | F |

    | 4 | Lin Chaoying | 25 | F |

    +-----+---------------+-----+--------+

    4 rows in set (0.00 sec)

    (以上是把年龄修改回来做实验)

    MariaDB [hellodb]> select * from teachers where age > (select avg(age) from s1); (多表子循环查询平均年龄大于25的人)

    +-----+---------------+-----+--------+

    | TID | Name | Age | Gender |

    +-----+---------------+-----+--------+

    | 1 | Song Jiang | 45 | M |

    | 2 | Zhang Sanfeng | 94 | M |

    | 3 | Miejue Shitai | 77 | F |

    +-----+---------------+-----+--------+

    3 rows in set (0.00 sec)

    MariaDB [hellodb]> update teachers set age=26 where tid=4; (修改一下最后一条的年龄为26)

    Query OK, 1 row affected (0.00 sec)

    Rows matched: 1 Changed: 1 Warnings: 0

    MariaDB [hellodb]> select * from teachers where age > (select avg(age) from s1); (最后一条也大于25就显示出来了)

    +-----+---------------+-----+--------+

    | TID | Name | Age | Gender |

    +-----+---------------+-----+--------+

    | 1 | Song Jiang | 45 | M |

    | 2 | Zhang Sanfeng | 94 | M |

    | 3 | Miejue Shitai | 77 | F |

    | 4 | Lin Chaoying | 26 | F |

    +-----+---------------+-----+--------+

    4 rows in set (0.00 sec)

    联合查询

    union 纵向合并两张表,表头来自第一条查询记录.MariaDB [hellodb]> select * from teachers

    -> union

    -> select stuid,name,age,gender from s1;

    +-----+---------------+-----+--------+

    | TID | Name | Age | Gender |

    +-----+---------------+-----+--------+

    | 1 | Song Jiang | 45 | M |

    | 2 | Zhang Sanfeng | 94 | M |

    | 3 | Miejue Shitai | 77 | F |

    | 4 | Lin Chaoying | 26 | F |

    | 1 | Shi Zhongyu | 22 | M |

    | 2 | Shi Potian | 22 | M |

    | 3 | Xie Yanke | 53 | M |

    | 4 | Ding Dian | 32 | M |

    | 5 | Yu Yutong | 26 | M |

    | 6 | Shi Qing | 46 | M |

    | 7 | Xi Ren | 19 | F |

    | 8 | Lin Daiyu | 17 | F |

    | 9 | Ren Yingying | 20 | F |

    | 10 | Yue Lingshan | 19 | F |

    | 11 | Yuan Chengzhi | 23 | M |

    | 12 | Wen Qingqing | 19 | F |

    | 13 | Tian Boguang | 33 | M |

    | 14 | Lu Wushuang | 17 | F |

    | 15 | Duan Yu | 19 | M |

    | 16 | Xu Zhu | 21 | M |

    | 17 | Lin Chong | 25 | M |

    | 18 | Hua Rong | 23 | M |

    | 19 | Xue Baochai | 18 | F |

    | 20 | Diao Chan | 19 | F |

    | 21 | Huang Yueying | 22 | F |

    | 22 | Xiao Qiao | 20 | F |

    | 23 | Ma Chao | 23 | M |

    | 24 | Xu Xian | 27 | M |

    | 25 | Sun Dasheng | 100 | M |

    | 26 | xietingfeng | 23 | M |

    | 27 | liudehua | 18 | F |

    | 28 | mahuateng | 20 | M |

    | 29 | wuyanzu | 19 | M |

    | 30 | wuzetian | 21 | F |

    | 31 | Song Jiang | 18 | M |

    | 32 | Zhang Sanfeng | 18 | M |

    | 33 | Miejue Shitai | 18 | F |

    | 34 | Lin Chaoying | 18 | F |

    | 38 | abc | 20 | M |

    +-----+---------------+-----+--------+

    39 rows in set (0.00 sec)

    MariaDB [hellodb]> select tid as id ,name,age,gender from teachers union select stuid,name,age,gender from s1; (起个别名替换掉表头的tid并纵向合并两张表)

    +----+---------------+-----+--------+

    | id | name | age | gender |

    +----+---------------+-----+--------+

    | 1 | Song Jiang | 45 | M |

    | 2 | Zhang Sanfeng | 94 | M |

    | 3 | Miejue Shitai | 77 | F |

    | 4 | Lin Chaoying | 26 | F |

    | 1 | Shi Zhongyu | 22 | M |

    | 2 | Shi Potian | 22 | M |

    | 3 | Xie Yanke | 53 | M |

    | 4 | Ding Dian | 32 | M |

    | 5 | Yu Yutong | 26 | M |

    | 6 | Shi Qing | 46 | M |

    | 7 | Xi Ren | 19 | F |

    | 8 | Lin Daiyu | 17 | F |

    | 9 | Ren Yingying | 20 | F |

    | 10 | Yue Lingshan | 19 | F |

    | 11 | Yuan Chengzhi | 23 | M |

    | 12 | Wen Qingqing | 19 | F |

    | 13 | Tian Boguang | 33 | M |

    | 14 | Lu Wushuang | 17 | F |

    | 15 | Duan Yu | 19 | M |

    | 16 | Xu Zhu | 21 | M |

    | 17 | Lin Chong | 25 | M |

    | 18 | Hua Rong | 23 | M |

    | 19 | Xue Baochai | 18 | F |

    | 20 | Diao Chan | 19 | F |

    | 21 | Huang Yueying | 22 | F |

    | 22 | Xiao Qiao | 20 | F |

    | 23 | Ma Chao | 23 | M |

    | 24 | Xu Xian | 27 | M |

    | 25 | Sun Dasheng | 100 | M |

    | 26 | xietingfeng | 23 | M |

    | 27 | liudehua | 18 | F |

    | 28 | mahuateng | 20 | M |

    | 29 | wuyanzu | 19 | M |

    | 30 | wuzetian | 21 | F |

    | 31 | Song Jiang | 18 | M |

    | 32 | Zhang Sanfeng | 18 | M |

    | 33 | Miejue Shitai | 18 | F |

    | 34 | Lin Chaoying | 18 | F |

    | 38 | abc | 20 | M |

    +----+---------------+-----+--------+

    39 rows in set (0.00 sec)

    union 自己和自己相连可以去重。MariaDB [hellodb]> create table t2 select * from teachers; (先导一张表出来做实验不能有主键,所以只用了这种方法导了数据没有把主键导过来)

    Query OK, 4 rows affected (0.01 sec)

    Records: 4 Duplicates: 0 Warnings: 0

    MariaDB [hellodb]> select * from t2;

    +-----+---------------+-----+--------+

    | TID | Name | Age | Gender |

    +-----+---------------+-----+--------+

    | 1 | Song Jiang | 45 | M |

    | 2 | Zhang Sanfeng | 94 | M |

    | 3 | Miejue Shitai | 77 | F |

    | 4 | Lin Chaoying | 26 | F |

    +-----+---------------+-----+--------+

    4 rows in set (0.00 sec)

    MariaDB [hellodb]> desc t2;

    +--------+----------------------+------+-----+---------+-------+

    | Field | Type | Null | Key | Default | Extra |

    +--------+----------------------+------+-----+---------+-------+

    | TID | smallint(5) unsigned | NO | | 0 | |

    | Name | varchar(100) | NO | | NULL | |

    | Age | tinyint(3) unsigned | NO | | NULL | |

    | Gender | enum('F','M') | YES | | NULL | |

    +--------+----------------------+------+-----+---------+-------+

    4 rows in set (0.00 sec)

    (添加重复的行做实验)

    MariaDB [hellodb]> insert into t2 set tid=4,name='linchaoying',age=26,gender='F'; (这条记录添加的只有name不一样少了个空格)

    Query OK, 1 row affected (0.00 sec)

    MariaDB [hellodb]> insert into t2 set tid=4,name='lin chaoying',age=26,gender='F'; (完全一样加了一行)

    Query OK, 1 row affected (0.00 sec)

    MariaDB [hellodb]> select * from t2;

    +-----+---------------+-----+--------+

    | TID | Name | Age | Gender |

    +-----+---------------+-----+--------+

    | 1 | Song Jiang | 45 | M |

    | 2 | Zhang Sanfeng | 94 | M |

    | 3 | Miejue Shitai | 77 | F |

    | 4 | Lin Chaoying | 26 | F |

    | 4 | linchaoying | 26 | F |

    | 4 | lin chaoying | 26 | F |

    +-----+---------------+-----+--------+

    6 rows in set (0.00 sec)

    MariaDB [hellodb]> select * from t2 union select * from t2; (用union过滤掉重复的行,少一个空格的那条记录过滤不了)

    +-----+---------------+-----+--------+

    | TID | Name | Age | Gender |

    +-----+---------------+-----+--------+

    | 1 | Song Jiang | 45 | M |

    | 2 | Zhang Sanfeng | 94 | M |

    | 3 | Miejue Shitai | 77 | F |

    | 4 | Lin Chaoying | 26 | F |

    | 4 | linchaoying | 26 | F |

    +-----+---------------+-----+--------+

    5 rows in set (0.00 sec)

    使用all 简单连接两张表不去重MariaDB [hellodb]> select * from t2 union all select * from t2;

    +-----+---------------+-----+--------+

    | TID | Name | Age | Gender |

    +-----+---------------+-----+--------+

    | 1 | Song Jiang | 45 | M |

    | 2 | Zhang Sanfeng | 94 | M |

    | 3 | Miejue Shitai | 77 | F |

    | 4 | Lin Chaoying | 26 | F |

    | 4 | linchaoying | 26 | F |

    | 4 | lin chaoying | 26 | F |

    | 1 | Song Jiang | 45 | M |

    | 2 | Zhang Sanfeng | 94 | M |

    | 3 | Miejue Shitai | 77 | F |

    | 4 | Lin Chaoying | 26 | F |

    | 4 | linchaoying | 26 | F |

    | 4 | lin chaoying | 26 | F |

    +-----+---------------+-----+--------+

    12 rows in set (0.00 sec)

    交叉连接两张表横向组合,类似于笛卡尔乘积。 (cross join)

    两张表使用交叉连接就是这张表的每一行去和另一张表的所有行组合一遍,形成新的行。MariaDB [hellodb]> select * from s1 cross join teachers;

    +-------+---------------+-------+-----+--------+---------+-----------+-----+---------------+-----+--------+

    | StuID | Name | phone | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |

    +-------+---------------+-------+-----+--------+---------+-----------+-----+---------------+-----+--------+

    | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 | 1 | Song Jiang | 25 | M |

    | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 | 2 | Zhang Sanfeng | 25 | M |

    | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 | 3 | Miejue Shitai | 25 | F |

    | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 | 4 | Lin Chaoying | 25 | F |

    | 2 | Shi Potian | NULL | 22 | M | 1 | 7 | 1 | Song Jiang | 25 | M |

    | 2 | Shi Potian | NULL | 22 | M | 1 | 7 | 2 | Zhang Sanfeng | 25 | M |

    | 2 | Shi Potian | NULL | 22 | M | 1 | 7 | 3 | Miejue Shitai | 25 | F |

    | 2 | Shi Potian | NULL | 22 | M | 1 | 7 | 4 | Lin Chaoying | 25 | F |

    | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 | 1 | Song Jiang | 25 | M |

    | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 | 2 | Zhang Sanfeng | 25 | M |

    | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 | 3 | Miejue Shitai | 25 | F |

    | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 | 4 | Lin Chaoying | 25 | F |

    | 4 | Ding Dian | NULL | 32 | M | 4 | 4 | 1 | Song Jiang | 25 | M |

    | 4 | Ding Dian | NULL | 32 | M | 4 | 4 | 2 | Zhang Sanfeng | 25 | M |

    | 4 | Ding Dian | NULL | 32 | M | 4 | 4 | 3 | Miejue Shitai | 25 | F |

    | 4 | Ding Dian | NULL | 32 | M | 4 | 4 | 4 | Lin Chaoying | 25 | F |

    | 5 | Yu Yutong | NULL | 26 | M | 3 | 1 | 1 | Song Jiang | 25 | M |

    | 5 | Yu Yutong | NULL | 26 | M | 3 | 1 | 2 | Zhang Sanfeng | 25 | M |

    | 5 | Yu Yutong | NULL | 26 | M | 3 | 1 | 3 | Miejue Shitai | 25 | F |

    | 5 | Yu Yutong | NULL | 26 | M | 3 | 1 | 4 | Lin Chaoying | 25 | F |

    | 6 | Shi Qing | NULL | 46 | M | 5 | NULL | 1 | Song Jiang | 25 | M |

    | 6 | Shi Qing | NULL | 46 | M | 5 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 6 | Shi Qing | NULL | 46 | M | 5 | NULL | 3 | Miejue Shitai | 25 | F |

    | 6 | Shi Qing | NULL | 46 | M | 5 | NULL | 4 | Lin Chaoying | 25 | F |

    | 7 | Xi Ren | NULL | 19 | F | 3 | NULL | 1 | Song Jiang | 25 | M |

    | 7 | Xi Ren | NULL | 19 | F | 3 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 7 | Xi Ren | NULL | 19 | F | 3 | NULL | 3 | Miejue Shitai | 25 | F |

    | 7 | Xi Ren | NULL | 19 | F | 3 | NULL | 4 | Lin Chaoying | 25 | F |

    | 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL | 1 | Song Jiang | 25 | M |

    | 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL | 3 | Miejue Shitai | 25 | F |

    | 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL | 4 | Lin Chaoying | 25 | F |

    | 9 | Ren Yingying | NULL | 20 | F | 6 | NULL | 1 | Song Jiang | 25 | M |

    | 9 | Ren Yingying | NULL | 20 | F | 6 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 9 | Ren Yingying | NULL | 20 | F | 6 | NULL | 3 | Miejue Shitai | 25 | F |

    | 9 | Ren Yingying | NULL | 20 | F | 6 | NULL | 4 | Lin Chaoying | 25 | F |

    | 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL | 1 | Song Jiang | 25 | M |

    | 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL | 3 | Miejue Shitai | 25 | F |

    | 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL | 4 | Lin Chaoying | 25 | F |

    | 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL | 1 | Song Jiang | 25 | M |

    | 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL | 3 | Miejue Shitai | 25 | F |

    | 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL | 4 | Lin Chaoying | 25 | F |

    | 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL | 1 | Song Jiang | 25 | M |

    | 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL | 3 | Miejue Shitai | 25 | F |

    | 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL | 4 | Lin Chaoying | 25 | F |

    | 13 | Tian Boguang | NULL | 33 | M | 2 | NULL | 1 | Song Jiang | 25 | M |

    | 13 | Tian Boguang | NULL | 33 | M | 2 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 13 | Tian Boguang | NULL | 33 | M | 2 | NULL | 3 | Miejue Shitai | 25 | F |

    | 13 | Tian Boguang | NULL | 33 | M | 2 | NULL | 4 | Lin Chaoying | 25 | F |

    | 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL | 1 | Song Jiang | 25 | M |

    | 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL | 3 | Miejue Shitai | 25 | F |

    | 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL | 4 | Lin Chaoying | 25 | F |

    | 15 | Duan Yu | NULL | 19 | M | 4 | NULL | 1 | Song Jiang | 25 | M |

    | 15 | Duan Yu | NULL | 19 | M | 4 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 15 | Duan Yu | NULL | 19 | M | 4 | NULL | 3 | Miejue Shitai | 25 | F |

    | 15 | Duan Yu | NULL | 19 | M | 4 | NULL | 4 | Lin Chaoying | 25 | F |

    | 16 | Xu Zhu | NULL | 21 | M | 1 | NULL | 1 | Song Jiang | 25 | M |

    | 16 | Xu Zhu | NULL | 21 | M | 1 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 16 | Xu Zhu | NULL | 21 | M | 1 | NULL | 3 | Miejue Shitai | 25 | F |

    | 16 | Xu Zhu | NULL | 21 | M | 1 | NULL | 4 | Lin Chaoying | 25 | F |

    | 17 | Lin Chong | NULL | 25 | M | 4 | NULL | 1 | Song Jiang | 25 | M |

    | 17 | Lin Chong | NULL | 25 | M | 4 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 17 | Lin Chong | NULL | 25 | M | 4 | NULL | 3 | Miejue Shitai | 25 | F |

    | 17 | Lin Chong | NULL | 25 | M | 4 | NULL | 4 | Lin Chaoying | 25 | F |

    | 18 | Hua Rong | NULL | 23 | M | 7 | NULL | 1 | Song Jiang | 25 | M |

    | 18 | Hua Rong | NULL | 23 | M | 7 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 18 | Hua Rong | NULL | 23 | M | 7 | NULL | 3 | Miejue Shitai | 25 | F |

    | 18 | Hua Rong | NULL | 23 | M | 7 | NULL | 4 | Lin Chaoying | 25 | F |

    | 19 | Xue Baochai | NULL | 18 | F | 6 | NULL | 1 | Song Jiang | 25 | M |

    | 19 | Xue Baochai | NULL | 18 | F | 6 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 19 | Xue Baochai | NULL | 18 | F | 6 | NULL | 3 | Miejue Shitai | 25 | F |

    | 19 | Xue Baochai | NULL | 18 | F | 6 | NULL | 4 | Lin Chaoying | 25 | F |

    | 20 | Diao Chan | NULL | 19 | F | 7 | NULL | 1 | Song Jiang | 25 | M |

    | 20 | Diao Chan | NULL | 19 | F | 7 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 20 | Diao Chan | NULL | 19 | F | 7 | NULL | 3 | Miejue Shitai | 25 | F |

    | 20 | Diao Chan | NULL | 19 | F | 7 | NULL | 4 | Lin Chaoying | 25 | F |

    | 21 | Huang Yueying | NULL | 22 | F | 6 | NULL | 1 | Song Jiang | 25 | M |

    | 21 | Huang Yueying | NULL | 22 | F | 6 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 21 | Huang Yueying | NULL | 22 | F | 6 | NULL | 3 | Miejue Shitai | 25 | F |

    | 21 | Huang Yueying | NULL | 22 | F | 6 | NULL | 4 | Lin Chaoying | 25 | F |

    | 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL | 1 | Song Jiang | 25 | M |

    | 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL | 3 | Miejue Shitai | 25 | F |

    | 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL | 4 | Lin Chaoying | 25 | F |

    | 23 | Ma Chao | NULL | 23 | M | 4 | NULL | 1 | Song Jiang | 25 | M |

    | 23 | Ma Chao | NULL | 23 | M | 4 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 23 | Ma Chao | NULL | 23 | M | 4 | NULL | 3 | Miejue Shitai | 25 | F |

    | 23 | Ma Chao | NULL | 23 | M | 4 | NULL | 4 | Lin Chaoying | 25 | F |

    | 24 | Xu Xian | NULL | 27 | M | NULL | NULL | 1 | Song Jiang | 25 | M |

    | 24 | Xu Xian | NULL | 27 | M | NULL | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 24 | Xu Xian | NULL | 27 | M | NULL | NULL | 3 | Miejue Shitai | 25 | F |

    | 24 | Xu Xian | NULL | 27 | M | NULL | NULL | 4 | Lin Chaoying | 25 | F |

    | 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL | 1 | Song Jiang | 25 | M |

    | 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL | 3 | Miejue Shitai | 25 | F |

    | 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL | 4 | Lin Chaoying | 25 | F |

    | 26 | xietingfeng | NULL | 23 | M | 2 | 1 | 1 | Song Jiang | 25 | M |

    | 26 | xietingfeng | NULL | 23 | M | 2 | 1 | 2 | Zhang Sanfeng | 25 | M |

    | 26 | xietingfeng | NULL | 23 | M | 2 | 1 | 3 | Miejue Shitai | 25 | F |

    | 26 | xietingfeng | NULL | 23 | M | 2 | 1 | 4 | Lin Chaoying | 25 | F |

    | 27 | liudehua | NULL | 18 | F | 1 | NULL | 1 | Song Jiang | 25 | M |

    | 27 | liudehua | NULL | 18 | F | 1 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 27 | liudehua | NULL | 18 | F | 1 | NULL | 3 | Miejue Shitai | 25 | F |

    | 27 | liudehua | NULL | 18 | F | 1 | NULL | 4 | Lin Chaoying | 25 | F |

    | 28 | mahuateng | NULL | 20 | M | 3 | NULL | 1 | Song Jiang | 25 | M |

    | 28 | mahuateng | NULL | 20 | M | 3 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 28 | mahuateng | NULL | 20 | M | 3 | NULL | 3 | Miejue Shitai | 25 | F |

    | 28 | mahuateng | NULL | 20 | M | 3 | NULL | 4 | Lin Chaoying | 25 | F |

    | 29 | wuyanzu | NULL | 19 | M | 4 | NULL | 1 | Song Jiang | 25 | M |

    | 29 | wuyanzu | NULL | 19 | M | 4 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 29 | wuyanzu | NULL | 19 | M | 4 | NULL | 3 | Miejue Shitai | 25 | F |

    | 29 | wuyanzu | NULL | 19 | M | 4 | NULL | 4 | Lin Chaoying | 25 | F |

    | 30 | wuzetian | NULL | 21 | F | NULL | NULL | 1 | Song Jiang | 25 | M |

    | 30 | wuzetian | NULL | 21 | F | NULL | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 30 | wuzetian | NULL | 21 | F | NULL | NULL | 3 | Miejue Shitai | 25 | F |

    | 30 | wuzetian | NULL | 21 | F | NULL | NULL | 4 | Lin Chaoying | 25 | F |

    | 31 | Song Jiang | NULL | 18 | M | 45 | NULL | 1 | Song Jiang | 25 | M |

    | 31 | Song Jiang | NULL | 18 | M | 45 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 31 | Song Jiang | NULL | 18 | M | 45 | NULL | 3 | Miejue Shitai | 25 | F |

    | 31 | Song Jiang | NULL | 18 | M | 45 | NULL | 4 | Lin Chaoying | 25 | F |

    | 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL | 1 | Song Jiang | 25 | M |

    | 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL | 3 | Miejue Shitai | 25 | F |

    | 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL | 4 | Lin Chaoying | 25 | F |

    | 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL | 1 | Song Jiang | 25 | M |

    | 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL | 3 | Miejue Shitai | 25 | F |

    | 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL | 4 | Lin Chaoying | 25 | F |

    | 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL | 1 | Song Jiang | 25 | M |

    | 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL | 3 | Miejue Shitai | 25 | F |

    | 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL | 4 | Lin Chaoying | 25 | F |

    | 38 | abc | NULL | 20 | M | NULL | NULL | 1 | Song Jiang | 25 | M |

    | 38 | abc | NULL | 20 | M | NULL | NULL | 2 | Zhang Sanfeng | 25 | M |

    | 38 | abc | NULL | 20 | M | NULL | NULL | 3 | Miejue Shitai | 25 | F |

    | 38 | abc | NULL | 20 | M | NULL | NULL | 4 | Lin Chaoying | 25 | F |

    +-------+---------------+-------+-----+--------+---------+-----------+-----+---------------+-----+--------+

    140 rows in set (0.00 sec)

    第一张表

    0 1 2 3

    1 2 3 4

    第二张表

    1 3 4 5

    2 3 4 5

    交叉连接后结果:

    0 1 2 3 1 3 4 5

    0 1 2 3 2 3 4 5

    1 2 3 4 1 3 4 5

    1 2 3 4 2 3 4 5

    两张表换下位置不影响数据只是显示效果变了而已:

    1 3 4 5 0 1 2 3

    1 3 4 5 1 2 3 4

    2 3 4 5 0 1 2 3

    2 3 4 5 1 2 3 4

    MariaDB [hellodb]> select * from teachers , s1; (这个命令也可以交叉连接但是比较老了推荐使用第一种)

    +-----+---------------+-----+--------+-------+---------------+-------+-----+--------+---------+-----------+

    | TID | Name | Age | Gender | StuID | Name | phone | Age | Gender | ClassID | TeacherID |

    +-----+---------------+-----+--------+-------+---------------+-------+-----+--------+---------+-----------+

    | 1 | Song Jiang | 25 | M | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |

    | 2 | Zhang Sanfeng | 25 | M | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |

    | 3 | Miejue Shitai | 25 | F | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |

    | 4 | Lin Chaoying | 25 | F | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |

    | 1 | Song Jiang | 25 | M | 2 | Shi Potian | NULL | 22 | M | 1 | 7 |

    | 2 | Zhang Sanfeng | 25 | M | 2 | Shi Potian | NULL | 22 | M | 1 | 7 |

    | 3 | Miejue Shitai | 25 | F | 2 | Shi Potian | NULL | 22 | M | 1 | 7 |

    | 4 | Lin Chaoying | 25 | F | 2 | Shi Potian | NULL | 22 | M | 1 | 7 |

    | 1 | Song Jiang | 25 | M | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |

    | 2 | Zhang Sanfeng | 25 | M | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |

    | 3 | Miejue Shitai | 25 | F | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |

    | 4 | Lin Chaoying | 25 | F | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |

    挑出两张表的个别字段。MariaDB [hellodb]> select name,age,gender from teachers cross join s1; (这里有两个字段是重复的name,age两个表都有)

    ERROR 1052 (23000): Column 'name' in field list is ambiguous

    MariaDB [hellodb]> select stuid,s1.name,tid,teachers.name from teachers cross join s1; (分别指定是哪个表的name)

    +-------+---------------+-----+---------------+

    | stuid | name | tid | name |

    +-------+---------------+-----+---------------+

    | 1 | Shi Zhongyu | 1 | Song Jiang |

    | 1 | Shi Zhongyu | 2 | Zhang Sanfeng |

    | 1 | Shi Zhongyu | 3 | Miejue Shitai |

    | 1 | Shi Zhongyu | 4 | Lin Chaoying |

    | 2 | Shi Potian | 1 | Song Jiang |

    | 2 | Shi Potian | 2 | Zhang Sanfeng |

    | 2 | Shi Potian | 3 | Miejue Shitai |

    | 2 | Shi Potian | 4 | Lin Chaoying |

    | 3 | Xie Yanke | 1 | Song Jiang |

    | 3 | Xie Yanke | 2 | Zhang Sanfeng |

    | 3 | Xie Yanke | 3 | Miejue Shitai |

    | 3 | Xie Yanke | 4 | Lin Chaoying |

    | 4 | Ding Dian | 1 | Song Jiang |

    | 4 | Ding Dian | 2 | Zhang Sanfeng |

    | 4 | Ding Dian | 3 | Miejue Shitai |

    | 4 | Ding Dian | 4 | Lin Chaoying |

    | 5 | Yu Yutong | 1 | Song Jiang |

    | 5 | Yu Yutong | 2 | Zhang Sanfeng |

    | 5 | Yu Yutong | 3 | Miejue Shitai |

    | 5 | Yu Yutong | 4 | Lin Chaoying |

    | 6 | Shi Qing | 1 | Song Jiang |

    | 6 | Shi Qing | 2 | Zhang Sanfeng |

    | 6 | Shi Qing | 3 | Miejue Shitai |

    | 6 | Shi Qing | 4 | Lin Chaoying |

    | 7 | Xi Ren | 1 | Song Jiang |

    | 7 | Xi Ren | 2 | Zhang Sanfeng |

    | 7 | Xi Ren | 3 | Miejue Shitai |

    | 7 | Xi Ren | 4 | Lin Chaoying |

    | 8 | Lin Daiyu | 1 | Song Jiang |

    | 8 | Lin Daiyu | 2 | Zhang Sanfeng |

    | 8 | Lin Daiyu | 3 | Miejue Shitai |

    | 8 | Lin Daiyu | 4 | Lin Chaoying |

    | 9 | Ren Yingying | 1 | Song Jiang |

    | 9 | Ren Yingying | 2 | Zhang Sanfeng |

    | 9 | Ren Yingying | 3 | Miejue Shitai |

    | 9 | Ren Yingying | 4 | Lin Chaoying |

    | 10 | Yue Lingshan | 1 | Song Jiang |

    | 10 | Yue Lingshan | 2 | Zhang Sanfeng |

    | 10 | Yue Lingshan | 3 | Miejue Shitai |

    | 10 | Yue Lingshan | 4 | Lin Chaoying |

    | 11 | Yuan Chengzhi | 1 | Song Jiang |

    | 11 | Yuan Chengzhi | 2 | Zhang Sanfeng |

    | 11 | Yuan Chengzhi | 3 | Miejue Shitai |

    | 11 | Yuan Chengzhi | 4 | Lin Chaoying |

    (省略了太长)

    MariaDB [hellodb]> select stuid,s1.name,s1.age,tid,teachers.name,teachers.age from teachers cross join s1; (name和age都可以加在里面指定)

    +-------+---------------+-----+-----+---------------+-----+

    | stuid | name | age | tid | name | age |

    +-------+---------------+-----+-----+---------------+-----+

    | 1 | Shi Zhongyu | 22 | 1 | Song Jiang | 25 |

    | 1 | Shi Zhongyu | 22 | 2 | Zhang Sanfeng | 25 |

    | 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 25 |

    | 1 | Shi Zhongyu | 22 | 4 | Lin Chaoying | 25 |

    | 2 | Shi Potian | 22 | 1 | Song Jiang | 25 |

    | 2 | Shi Potian | 22 | 2 | Zhang Sanfeng | 25 |

    | 2 | Shi Potian | 22 | 3 | Miejue Shitai | 25 |

    | 2 | Shi Potian | 22 | 4 | Lin Chaoying | 25 |

    | 3 | Xie Yanke | 53 | 1 | Song Jiang | 25 |

    | 3 | Xie Yanke | 53 | 2 | Zhang Sanfeng | 25 |

    | 3 | Xie Yanke | 53 | 3 | Miejue Shitai | 25 |

    | 3 | Xie Yanke | 53 | 4 | Lin Chaoying | 25 |

    | 4 | Ding Dian | 32 | 1 | Song Jiang | 25 |

    | 4 | Ding Dian | 32 | 2 | Zhang Sanfeng | 25 |

    | 4 | Ding Dian | 32 | 3 | Miejue Shitai | 25 |

    | 4 | Ding Dian | 32 | 4 | Lin Chaoying | 25 |

    | 5 | Yu Yutong | 26 | 1 | Song Jiang | 25 |

    | 5 | Yu Yutong | 26 | 2 | Zhang Sanfeng | 25 |

    | 5 | Yu Yutong | 26 | 3 | Miejue Shitai | 25 |

    | 5 | Yu Yutong | 26 | 4 | Lin Chaoying | 25 |

    (省略了太长)

    MariaDB [hellodb]> select stuid,s1.name as s1_name,tid,teachers.name as teachers_name from teachers cross join s1; (也可以加上别名来区分比较清晰)

    +-------+---------------+-----+---------------+

    | stuid | s1_name | tid | teachers_name |

    +-------+---------------+-----+---------------+

    | 1 | Shi Zhongyu | 1 | Song Jiang |

    | 1 | Shi Zhongyu | 2 | Zhang Sanfeng |

    | 1 | Shi Zhongyu | 3 | Miejue Shitai |

    | 1 | Shi Zhongyu | 4 | Lin Chaoying |

    | 2 | Shi Potian | 1 | Song Jiang |

    | 2 | Shi Potian | 2 | Zhang Sanfeng |

    | 2 | Shi Potian | 3 | Miejue Shitai |

    | 2 | Shi Potian | 4 | Lin Chaoying |

    | 3 | Xie Yanke | 1 | Song Jiang |

    | 3 | Xie Yanke | 2 | Zhang Sanfeng |

    | 3 | Xie Yanke | 3 | Miejue Shitai |

    | 3 | Xie Yanke | 4 | Lin Chaoying |

    (省略)对表起别名利用表的别名来查询数据。(定义别名是在字段的最后用在前面用,只在当前字段生效)MariaDB [hellodb]> select stuid,s.name as s1_name,s.age,t.name as teachers_name,t.age from teachers t cross join s1 s;

    +-------+---------------+-----+---------------+-----+

    | stuid | s1_name | age | teachers_name | age |

    +-------+---------------+-----+---------------+-----+

    | 1 | Shi Zhongyu | 22 | Song Jiang | 25 |

    | 1 | Shi Zhongyu | 22 | Zhang Sanfeng | 25 |

    | 1 | Shi Zhongyu | 22 | Miejue Shitai | 25 |

    | 1 | Shi Zhongyu | 22 | Lin Chaoying | 25 |

    | 2 | Shi Potian | 22 | Song Jiang | 25 |

    | 2 | Shi Potian | 22 | Zhang Sanfeng | 25 |

    | 2 | Shi Potian | 22 | Miejue Shitai | 25 |

    | 2 | Shi Potian | 22 | Lin Chaoying | 25 |

    | 3 | Xie Yanke | 53 | Song Jiang | 25 |

    | 3 | Xie Yanke | 53 | Zhang Sanfeng | 25 |

    | 3 | Xie Yanke | 53 | Miejue Shitai | 25 |

    | 3 | Xie Yanke | 53 | Lin Chaoying | 25 |

    | 4 | Ding Dian | 32 | Song Jiang | 25 |

    | 4 | Ding Dian | 32 | Zhang Sanfeng | 25 |

    | 4 | Ding Dian | 32 | Miejue Shitai | 25 |

    | 4 | Ding Dian | 32 | Lin Chaoying | 25 |

    MariaDB [hellodb]> select stuid,s1.name s1_name,s1.age,tid,t.name teacher_name,teachers.age from teachers t cross join s1; (别名定义之后不能使用原始名字)

    ERROR 1054 (42S22): Unknown column 'teachers.age' in 'field list'

    内连接等值连接:让表之间的字段以“等值”建立连接关系

    把两个表有交集的地方连接起来MariaDB [hellodb]> select * from s1 inner join t1 on s1.teacherid=t1.tid; (三个个老师各教一个学生)

    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+

    | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |

    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+

    | 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |

    | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |

    | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |

    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+

    3 rows in set (0.00 sec)

    MariaDB [hellodb]> update s1 set teacherid=1 where stuid=25; (修改一下s1表的teacherid的值为1)

    Query OK, 1 row affected (0.00 sec)

    Rows matched: 1 Changed: 1 Warnings: 0

    MariaDB [hellodb]> select * from s1 inner join t1 on s1.teacherid=t1.tid; (再次使用这条命令,查看就是songjiang教两个学生)

    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+

    | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |

    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+

    | 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |

    | 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M |

    | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |

    | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |

    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+

    4 rows in set (0.00 sec)

    MariaDB [hellodb]> select * from s1 , t1 where s1.teacherid=t1.tid; (不加 inner join 的老写法)

    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+

    | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |

    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+

    | 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |

    | 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M |

    | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |

    | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |

    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+

    4 rows in set (0.01 sec)内连接之后过滤:先连接再过滤,显示s1表大于30的人。MariaDB [hellodb]> select * from s1 inner join t1 on s1.teacherid=t1.tid and s1.age >30;

    +-------+-------------+-----+--------+---------+-----------+-----+--------------+-----+--------+

    | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |

    +-------+-------------+-----+--------+---------+-----------+-----+--------------+-----+--------+

    | 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M |

    | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |

    +-------+-------------+-----+--------+---------+-----------+-----+--------------+-----+--------+

    2 rows in set (0.00 sec)

    查询完之后过滤MariaDB [hellodb]> select * from s1 inner join t1 on s1.teacherid=t1.tid where s1.a

    +-------+-------------+-----+--------+---------+-----------+-----+--------------+--

    | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | A

    +-------+-------------+-----+--------+---------+-----------+-----+--------------+--

    | 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang |

    | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying |

    +-------+-------------+-----+--------+---------+-----------+-----+--------------+--

    2 rows in set (0.00 sec)

    外连接外连接:

    左外连接:FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col (排在前面的)

    右外连接:FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col (排在后面的)

    左外连接 left outer

    2309d28ae77face7f2d5ca683d91381c.png

    学生表全留下来老师的只留下来有交集的地方。 (没有交集的地方空值代替)MariaDB [hellodb]> select * from s1 left outer join t1 on s1.teacherid=t1.tid;

    +-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+

    | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |

    +-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+

    | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |

    | 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |

    | 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |

    | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |

    | 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |

    | 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL |

    | 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |

    | 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL |

    | 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL |

    | 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |

    | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL |

    | 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL |

    | 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL |

    | 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL |

    | 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL |

    | 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL |

    | 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL |

    | 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL |

    | 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL |

    | 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL |

    | 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL |

    | 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL |

    | 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL |

    | 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL |

    | 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M |

    +-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+

    25 rows in set (0.00 sec)

    左外连接扩展用法

    b8fd6d3d64040d980532bff96b33bfd7.png取出没有老师教的学生 (用where指定)MariaDB [hellodb]> select * from t1; (教师表)

    +-----+---------------+-----+--------+

    | TID | Name | Age | Gender |

    +-----+---------------+-----+--------+

    | 1 | Song Jiang | 45 | M |

    | 2 | Zhang Sanfeng | 94 | M |

    | 3 | Miejue Shitai | 77 | F |

    | 4 | Lin Chaoying | 93 | F |

    +-----+---------------+-----+--------+

    4 rows in set (0.00 sec)

    MariaDB [hellodb]> select * from s1 left outer join t1 on s1.teacherid=t1.tid where tid is null; (前面两个,老师表里没有这两个老师的编号)

    +-------+---------------+-----+--------+---------+-----------+------+------+------+--------+

    | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |

    +-------+---------------+-----+--------+---------+-----------+------+------+------+--------+

    | 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |

    | 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |

    | 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL |

    | 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |

    | 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL |

    | 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL |

    | 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |

    | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL |

    | 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL |

    | 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL |

    | 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL |

    | 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL |

    | 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL |

    | 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL |

    | 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL |

    | 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL |

    | 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL |

    | 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL |

    | 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL |

    | 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL |

    | 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL |

    +-------+---------------+-----+--------+---------+-----------+------+------+------+--------+

    21 rows in set (0.00 sec)

    右外链接:right outer

    2bcb888abbc7bfa98f5d60d4efeddb1c.png

    老师表全留下来,学生表有交集的地方留下来。MariaDB [hellodb]> select * from s1 right outer join t1 on s1.teacherid=t1.tid;

    +-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+

    | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |

    +-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+

    | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |

    | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |

    | 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |

    | 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M |

    | NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |

    +-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+

    5 rows in set (0.00 sec)

    右外连接扩展用法

    d95854f63d4ed0c10b7313e47c85a568.png没有教学生的老师留下来。 (和左外连接的逻辑是一样的)MariaDB [hellodb]> select * from s1 right outer join t1 on s1.teacherid=t1.tid where s1.teacherid is null;

    +-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+

    | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |

    +-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+

    | NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |

    +-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+

    1 row in set (0.00 sec)

    完全外连接和交叉连接不一样 mysql不支持full outer join

    69a2f98e8364a1bd6b1da075717041ac.png

    把左外连接和右外连接用union联合起来,有交集的地方对应,没有交集的也地方输出出来。MariaDB [hellodb]> select * from s1 left outer join t1 on s1.teacherid=t1.tid

    -> union

    -> select * from s1 right outer join t1 on s1.teacherid=t1.tid;

    +-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+

    | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |

    +-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+

    | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |

    | 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |

    | 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |

    | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |

    | 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |

    | 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL |

    | 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |

    | 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL |

    | 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL |

    | 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |

    | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL |

    | 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL |

    | 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL |

    | 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL |

    | 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL |

    | 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL |

    | 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL |

    | 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL |

    | 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL |

    | 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL |

    | 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL |

    | 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL |

    | 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL |

    | 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL |

    | 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M |

    | NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |

    +-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+

    26 rows in set (0.00 sec)

    完全外连接扩展用法:有交集的地方去除掉,只留外连接。

    be33e7dad90d95f0aefefe60317e0b5e.png把没有老师的学生,和没有学生的老师取出来。MariaDB [hellodb]> select * from (select s.stuid,s.name s_name,s.teacherid,t.tid,t.name t_name from s1 s left outer join t1 t on s.teacherid=t.tid union select s.stuid,s.name,s.teacherid,t.tid,t.name from s1 s right outer join t1 t on s.teacherid=t.tid) as a where a.teacherid is null or a.tid is null;

    +-------+---------------+-----------+------+---------------+

    | stuid | s_name | teacherid | tid | t_name |

    +-------+---------------+-----------+------+---------------+

    | 2 | Shi Potian | 7 | NULL | NULL |

    | 3 | Xie Yanke | 16 | NULL | NULL |

    | 6 | Shi Qing | NULL | NULL | NULL |

    | 7 | Xi Ren | NULL | NULL | NULL |

    | 8 | Lin Daiyu | NULL | NULL | NULL |

    | 9 | Ren Yingying | NULL | NULL | NULL |

    | 10 | Yue Lingshan | NULL | NULL | NULL |

    | 11 | Yuan Chengzhi | NULL | NULL | NULL |

    | 12 | Wen Qingqing | NULL | NULL | NULL |

    | 13 | Tian Boguang | NULL | NULL | NULL |

    | 14 | Lu Wushuang | NULL | NULL | NULL |

    | 15 | Duan Yu | NULL | NULL | NULL |

    | 16 | Xu Zhu | NULL | NULL | NULL |

    | 17 | Lin Chong | NULL | NULL | NULL |

    | 18 | Hua Rong | NULL | NULL | NULL |

    | 19 | Xue Baochai | NULL | NULL | NULL |

    | 20 | Diao Chan | NULL | NULL | NULL |

    | 21 | Huang Yueying | NULL | NULL | NULL |

    | 22 | Xiao Qiao | NULL | NULL | NULL |

    | 23 | Ma Chao | NULL | NULL | NULL |

    | 24 | Xu Xian | NULL | NULL | NULL |

    | NULL | NULL | NULL | 2 | Zhang Sanfeng |

    +-------+---------------+-----------+------+---------------+

    22 rows in set (0.00 sec)

    三张表查询取学生姓名,成绩,科目。

    先取出学生的姓名和成绩 (分两步做思路清晰一点)MariaDB [hellodb]> select st.name,sc.courseid,sc.score from s1 st inner join scores sc on st.stuid=sc.stuid;

    +-------------+----------+-------+

    | name | courseid | score |

    +-------------+----------+-------+

    | Shi Zhongyu | 2 | 77 |

    | Shi Zhongyu | 6 | 93 |

    | Shi Potian | 2 | 47 |

    | Shi Potian | 5 | 97 |

    | Xie Yanke | 2 | 88 |

    | Xie Yanke | 6 | 75 |

    | Ding Dian | 5 | 71 |

    | Ding Dian | 2 | 89 |

    | Yu Yutong | 1 | 39 |

    | Yu Yutong | 7 | 63 |

    | Shi Qing | 1 | 96 |

    | Xi Ren | 1 | 86 |

    | Xi Ren | 7 | 83 |

    | Lin Daiyu | 4 | 57 |

    | Lin Daiyu | 3 | 93 |

    +-------------+----------+-------+

    15 rows in set (0.00 sec)

    在连接一次取出学生姓名,成绩,科目。MariaDB [hellodb]> select st.name,co.course,sc.score from s1 st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid;

    +-------------+----------------+-------+

    | name | course | score |

    +-------------+----------------+-------+

    | Shi Zhongyu | Kuihua Baodian | 77 |

    | Shi Zhongyu | Weituo Zhang | 93 |

    | Shi Potian | Kuihua Baodian | 47 |

    | Shi Potian | Daiyu Zanghua | 97 |

    | Xie Yanke | Kuihua Baodian | 88 |

    | Xie Yanke | Weituo Zhang | 75 |

    | Ding Dian | Daiyu Zanghua | 71 |

    | Ding Dian | Kuihua Baodian | 89 |

    | Yu Yutong | Hamo Gong | 39 |

    | Yu Yutong | Dagou Bangfa | 63 |

    | Shi Qing | Hamo Gong | 96 |

    | Xi Ren | Hamo Gong | 86 |

    | Xi Ren | Dagou Bangfa | 83 |

    | Lin Daiyu | Taiji Quan | 57 |

    | Lin Daiyu | Jinshe Jianfa | 93 |

    +-------------+----------------+-------+

    15 rows in set (0.00 sec)严禁出现四张表join的情况

    自连接自联结顾名思义就是把一张表假设为两张一样的表,然后在做“多表查询”

    先构建一张表MariaDB [hellodb]> create table emp (id int, name char(20),leaderid int);

    Query OK, 0 rows affected (0.01 sec)

    MariaDB [hellodb]> insert emp value(1,'huangshang',null);

    Query OK, 1 row affected (0.00 sec)

    MariaDB [hellodb]> insert emp value(2,'taihou','huangshang');

    ERROR 1366 (22007): Incorrect integer value: 'huangshang' for column `hellodb`.`emp`.`leaderid` at row 1

    MariaDB [hellodb]> insert emp value(2,'taihou',1);

    Query OK, 1 row affected (0.00 sec)

    MariaDB [hellodb]> insert emp value(3,'guifei',2);

    Query OK, 1 row affected (0.00 sec)

    MariaDB [hellodb]> insert emp value(4,'shufei',3);

    Query OK, 1 row affected (0.00 sec)

    MariaDB [hellodb]> select * from emp;

    +------+------------+----------+

    | id | name | leaderid |

    +------+------------+----------+

    | 1 | huangshang | NULL |

    | 2 | taihou | 1 |

    | 3 | guifei | 2 |

    | 4 | shufei | 3 |

    +------+------------+----------+

    4 rows in set (0.00 sec)

    查询表里的上级的姓名。想象为两张表 员工表 和上司表 起别名做成MariaDB [hellodb]> select * from emp as e inner join emp as l on e.leaderid=l.id;

    +------+--------+----------+------+------------+----------+

    | id | name | leaderid | id | name | leaderid |

    +------+--------+----------+------+------------+----------+

    | 2 | taihou | 1 | 1 | huangshang | NULL |

    | 3 | guifei | 2 | 2 | taihou | 1 |

    | 4 | shufei | 3 | 3 | guifei | 2 |

    +------+--------+----------+------+------------+----------+

    3 rows in set (0.00 sec)取出来对应的上级,但是缺失了最上级。MariaDB [hellodb]> select e.name emp,l.name leader from emp as e inner join emp as l on e.leaderid=l.id;

    +--------+------------+

    | emp | leader |

    +--------+------------+

    | taihou | huangshang |

    | guifei | taihou |

    | shufei | guifei |

    +--------+------------+

    3 rows in set (0.00 sec)取出每个人对应的上级的id。MariaDB [hellodb]> select e.name emp,l.name leader from emp as e left join emp as l on e.leaderid=l.id;

    +------------+------------+

    | emp | leader |

    +------------+------------+

    | taihou | huangshang |

    | guifei | taihou |

    | shufei | guifei |

    | huangshang | NULL |

    +------------+------------+

    4 rows in set (0.00 sec)

    SQL语句的关键字执行顺序

    b5b2bfb31b1d4d48e1d1295405dc8d27.png

    展开全文
  • MySQL数据表进行分组查询(GROUP BY)GROUP BY关键字可以将查询结果按照某个字段或个字段进行分组。字段中值相等的为一组。基本的语法格式如下:GROUP BY 属性名 [HAVING 条件表达式] [WITH ROLLUP]属性名:是指...

    MySQL对数据表进行分组查询(GROUP BY)

    GROUP BY关键字可以将查询结果按照某个字段或多个字段进行分组。字段中值相等的为一组。基本的语法格式如下:

    GROUP BY 属性名 [HAVING 条件表达式] [WITH ROLLUP]

    属性名:是指按照该字段的值进行分组。

    HAVING 条件表达式:用来限制分组后的显示,符合条件表达式的结果将被显示。

    WITH ROLLUP:将会在所有记录的最后加上一条记录。加上的这一条记录是上面所有记录的总和。

    GROUP BY关键字可以和GROUP_CONCAT()函数一起使用。GROUP_CONCAT()函数会把每个分组中指定的字段值都显示出来。

    同时,GROUP BY关键字通常与集合函数一起使用。集合函数包括COUNT()函数、SUM()函数、AVG()函数、MAX()函数和MIN()函数等。

    COUNT()函数:用于统计记录的条数。

    SUM()函数:用于计算字段的值的总和。

    AVG()函数:用于计算字段的值的平均值。

    MAX()函数:用于查询字段的最大值。

    MIN()函数:用于查询字段的最小值。

    如果GROUP BY不与上述函数一起使用,那么查询结果就是字段取值的分组情况。字段中取值相同的记录为一组,但是只显示该组的第一条记录。

    单独使用GROUP BY关键字进行分组

    如果单独使用GROUP BY关键字,查询结果只显示一个分组的一条记录。

    实例:将employee表的sex字段进行分组查询,将查询结果与分组前的结果进行对比。操作步骤如下:

    1. 首先执行不带GROUP BY关键字的SELECT语句。如下图所示:

    4504.gif

    上图中代码执行的结果显示的是employee表中原始的记录情况。

    2. 执行带有GROUP BY关键字的SELECT语句。代码如下:

    SELECT * FROM employee GROUP BY sex;

    在DOS提示符窗口中查看执行带有GROUP BY关键字的SELECT语句的操作效果。如下图所示:

    4503.gif

    上图中代码执行的结果只显示了两条记录。这两条记录的sex字段的值分别为“女”和“男”。

    查询结果进行比较,GROUP BY关键字只显示每个分组的一条记录。这说明,GROUP BY关键字单独使用时,只能查询出每个分组的一条记录,这样做的意义不大。因此,一般在使用集合函数时才使用GROUP BY关键字。

    GROUP BY关键字与GROUP_CONCAT()函数一起使用

    GROUP BY关键字与GROUP_CONCAT()函数一起使用时,每个分组中指定的字段值会全部显示出来。

    实例:将employee表按照sex字段进行分组查询。使用GROUP_CONCAT()函数将每个分组的name字段的值显示出来。

    SELECT语句的代码如下:

    SELECT sex,GROUP_CONCAT(name) FROM employee GROUP BY sex;

    在DOS提示符窗口中查看代码的执行效果。如下图所示:

    4502.gif

    上图中代码执行的结果显示,查询结果分为两组。sex字段取值为“女”的记录是一组,取值为“男”的记录是一组。每一组中所有人的名字都被查询出来了。

    该实例说明,使用GROUP_CONCAT()函数可以很好的把分组情况表示出来。

    GROUP BY关键字与集合函数一起使用

    GROUP BY关键字与集合函数一起使用时,可以通过集合函数计算分组中的总记录、最大值、最小值等。

    实例:将employee表的sex字段进行分组查询。sex字段取值相同的为一组。然后对每一组使用集合函数COUNT()函数进行计算,求出每一组的记录数。

    SELECT语句的代码如下:

    SELECT sex,COUNT(sex) FROM employee GROUP BY sex;

    在DOS提示符窗口中查看GROUP BY关键字与集合函数一起使用的操作效果。如下图所示:

    4501.gif

    上图中代码执行的结果显示,查询结果按sex字段的取值进行分组。取值为“女”的记录为一组,取值为“男”的记录为一组。COUNT(sex)计算出了sex字段不同分组的记录数。第一组共有2条记录,第二组共有3条记录。

    提示

    通常情况下,GROUP BY关键字与集合函数一起使用,先使用GROUP BY关键字将记录分组,然后每组都使用集合函数进行计算。在统计时经常需要使用GROUP BY关键字和集合函数。

    GROUP BY关键字与HAVING一起使用

    使用GROUP BY关键字时,如果加上“HAVING 条件表达式”,则可以限制输出的结果。只有符合条件表达式的结果才会显示。

    实例:将employee表的sex字段进行分组查询。然后显示记录数大于等于3的分组。

    SELECT语句的代码如下:

    SELECT sex,COUNT(sex) FROM employee GROUP BY sex HAVING COUNT(sex)>=3;

    在DOS提示符窗口中查看GROUP BY关键字与HAVING一起使用的操作效果。如下图所示:

    4500.gif

    上图中代码执行的结果只显示了取值为“男”的记录的情况。因为,该分组的记录数为3,刚好符合HAVING COUNT(sex)>=3的条件。

    该实例说明,“HAVING 条件表达式”可以限制查询结果的显示情况。

    提示

    “HAVING 条件表达式”与“WHERE 条件表达式”都是用于限制显示的。但是,两者起作用的地方不一样。

    WHERE 条件表达式:作用于表或者视图,是表和视图的查询条件。

    HAVING 条件表达式:作用于分组后的记录,用于选择符合条件的组。

    按照多个字段进行分组

    在MySQL中,还可以按照多个字段进行分组。例如,employee表按照d_id字段和sex字段进行分组。分组过程中,先按照d_id字段进行分组,遇到d_id字段的值相等的情况时,再把d_id值相等的记录按照sex字段进行分组。

    实例:将employee表按照d_id字段和sex字段进行分组。

    SELECT语句的代码如下:

    SELECT * FROM employee GROUP BY d_id,sex;

    在DOS提示符窗口中查看按照多个字段进行分组的操作效果。如下图所示:

    4499.gif

    上图中代码执行的结果显示,记录先按照d_id字段进行分组,因为分别有两条记录的d_id的值为1001和1004,所以这4条记录再次按照sex字段的取值进行了分组。

    GROUP BY关键字与WITH ROLLUP一起使用

    使用WITH ROLLUP时,将会在所有记录的最后加上一条记录。这条记录是上面所有记录的总和。

    实例:将employee表的sex字段进行分组查询。使用COUNT()函数计算每组的记录数,并且加上WITH ROLLUP。

    SELECT语句的代码如下:

    SELECT sex,COUNT(sex) FROM employee GROUP BY sex WITH ROLLUP;

    在DOS提示符窗口中查看GROUP BY关键字与WITH ROLLUP一起使用的操作效果。如下图所示:

    4498.gif

    上图中代码执行的结果显示,计算出了各个分组的记录数,并且,在记录的最后加上了一条新的记录。该记录的COUNT(sex)列的值正好是上面分组的值的总和。

    实例:将employee表的sex字段进行分组查询。使用GROUP_CONCAT()函数查看每组的name字段的值,并且加上WITH ROLLUP。

    SELECT语句的代码如下:

    SELECT sex,GROUP_CONCAT(name) FROM employee GROUP BY sex WITH ROLLUP;

    在DOS提示符窗口中查看SELECT语句WITH ROLLUP参数的操作效果。如下图所示:

    4497.gif

    上图中代码执行的结果显示,GROUP_CONCAT(name)显示了每个分组的name字段的值。同时,最后一条记录的GROUP_CONCAT(name)列的值正好是上面分组name取值的总和。

    展开全文
  • MySQL数据表进行分组查询(GROUP BY)GROUP BY关键字可以将查询结果按照某个字段或个字段进行分组。字段中值相等的为一组。基本的语法格式如下:GROUP BY 属性名 [HAVING 条件表达式] [WITH ROLLUP]属性名:是指...

    MySQL对数据表进行分组查询(GROUP BY)

    GROUP BY关键字可以将查询结果按照某个字段或多个字段进行分组。字段中值相等的为一组。基本的语法格式如下:

    GROUP BY 属性名 [HAVING 条件表达式] [WITH ROLLUP]

    属性名:是指按照该字段的值进行分组。

    HAVING 条件表达式:用来限制分组后的显示,符合条件表达式的结果将被显示。

    WITH ROLLUP:将会在所有记录的最后加上一条记录。加上的这一条记录是上面所有记录的总和。

    GROUP BY关键字可以和GROUP_CONCAT()函数一起使用。GROUP_CONCAT()函数会把每个分组中指定的字段值都显示出来。

    同时,GROUP BY关键字通常与集合函数一起使用。集合函数包括COUNT()函数、SUM()函数、AVG()函数、MAX()函数和MIN()函数等。

    COUNT()函数:用于统计记录的条数。

    SUM()函数:用于计算字段的值的总和。

    AVG()函数:用于计算字段的值的平均值。

    MAX()函数:用于查询字段的最大值。

    MIN()函数:用于查询字段的最小值。

    如果GROUP BY不与上述函数一起使用,那么查询结果就是字段取值的分组情况。字段中取值相同的记录为一组,但是只显示该组的第一条记录。

    单独使用GROUP BY关键字进行分组

    如果单独使用GROUP BY关键字,查询结果只显示一个分组的一条记录。

    实例:将employee表的sex字段进行分组查询,将查询结果与分组前的结果进行对比。操作步骤如下:

    1. 首先执行不带GROUP BY关键字的SELECT语句。如下图所示:

    4504.gif

    上图中代码执行的结果显示的是employee表中原始的记录情况。

    2. 执行带有GROUP BY关键字的SELECT语句。代码如下:

    SELECT * FROM employee GROUP BY sex;

    在DOS提示符窗口中查看执行带有GROUP BY关键字的SELECT语句的操作效果。如下图所示:

    4503.gif

    上图中代码执行的结果只显示了两条记录。这两条记录的sex字段的值分别为“女”和“男”。

    查询结果进行比较,GROUP BY关键字只显示每个分组的一条记录。这说明,GROUP BY关键字单独使用时,只能查询出每个分组的一条记录,这样做的意义不大。因此,一般在使用集合函数时才使用GROUP BY关键字。

    GROUP BY关键字与GROUP_CONCAT()函数一起使用

    GROUP BY关键字与GROUP_CONCAT()函数一起使用时,每个分组中指定的字段值会全部显示出来。

    实例:将employee表按照sex字段进行分组查询。使用GROUP_CONCAT()函数将每个分组的name字段的值显示出来。

    SELECT语句的代码如下:

    SELECT sex,GROUP_CONCAT(name) FROM employee GROUP BY sex;

    在DOS提示符窗口中查看代码的执行效果。如下图所示:

    4502.gif

    上图中代码执行的结果显示,查询结果分为两组。sex字段取值为“女”的记录是一组,取值为“男”的记录是一组。每一组中所有人的名字都被查询出来了。

    该实例说明,使用GROUP_CONCAT()函数可以很好的把分组情况表示出来。

    GROUP BY关键字与集合函数一起使用

    GROUP BY关键字与集合函数一起使用时,可以通过集合函数计算分组中的总记录、最大值、最小值等。

    实例:将employee表的sex字段进行分组查询。sex字段取值相同的为一组。然后对每一组使用集合函数COUNT()函数进行计算,求出每一组的记录数。

    SELECT语句的代码如下:

    SELECT sex,COUNT(sex) FROM employee GROUP BY sex;

    在DOS提示符窗口中查看GROUP BY关键字与集合函数一起使用的操作效果。如下图所示:

    4501.gif

    上图中代码执行的结果显示,查询结果按sex字段的取值进行分组。取值为“女”的记录为一组,取值为“男”的记录为一组。COUNT(sex)计算出了sex字段不同分组的记录数。第一组共有2条记录,第二组共有3条记录。

    提示

    通常情况下,GROUP BY关键字与集合函数一起使用,先使用GROUP BY关键字将记录分组,然后每组都使用集合函数进行计算。在统计时经常需要使用GROUP BY关键字和集合函数。

    GROUP BY关键字与HAVING一起使用

    使用GROUP BY关键字时,如果加上“HAVING 条件表达式”,则可以限制输出的结果。只有符合条件表达式的结果才会显示。

    实例:将employee表的sex字段进行分组查询。然后显示记录数大于等于3的分组。

    SELECT语句的代码如下:

    SELECT sex,COUNT(sex) FROM employee GROUP BY sex HAVING COUNT(sex)>=3;

    在DOS提示符窗口中查看GROUP BY关键字与HAVING一起使用的操作效果。如下图所示:

    4500.gif

    上图中代码执行的结果只显示了取值为“男”的记录的情况。因为,该分组的记录数为3,刚好符合HAVING COUNT(sex)>=3的条件。

    该实例说明,“HAVING 条件表达式”可以限制查询结果的显示情况。

    提示

    “HAVING 条件表达式”与“WHERE 条件表达式”都是用于限制显示的。但是,两者起作用的地方不一样。

    WHERE 条件表达式:作用于表或者视图,是表和视图的查询条件。

    HAVING 条件表达式:作用于分组后的记录,用于选择符合条件的组。

    按照多个字段进行分组

    在MySQL中,还可以按照多个字段进行分组。例如,employee表按照d_id字段和sex字段进行分组。分组过程中,先按照d_id字段进行分组,遇到d_id字段的值相等的情况时,再把d_id值相等的记录按照sex字段进行分组。

    实例:将employee表按照d_id字段和sex字段进行分组。

    SELECT语句的代码如下:

    SELECT * FROM employee GROUP BY d_id,sex;

    在DOS提示符窗口中查看按照多个字段进行分组的操作效果。如下图所示:

    4499.gif

    上图中代码执行的结果显示,记录先按照d_id字段进行分组,因为分别有两条记录的d_id的值为1001和1004,所以这4条记录再次按照sex字段的取值进行了分组。

    GROUP BY关键字与WITH ROLLUP一起使用

    使用WITH ROLLUP时,将会在所有记录的最后加上一条记录。这条记录是上面所有记录的总和。

    实例:将employee表的sex字段进行分组查询。使用COUNT()函数计算每组的记录数,并且加上WITH ROLLUP。

    SELECT语句的代码如下:

    SELECT sex,COUNT(sex) FROM employee GROUP BY sex WITH ROLLUP;

    在DOS提示符窗口中查看GROUP BY关键字与WITH ROLLUP一起使用的操作效果。如下图所示:

    4498.gif

    上图中代码执行的结果显示,计算出了各个分组的记录数,并且,在记录的最后加上了一条新的记录。该记录的COUNT(sex)列的值正好是上面分组的值的总和。

    实例:将employee表的sex字段进行分组查询。使用GROUP_CONCAT()函数查看每组的name字段的值,并且加上WITH ROLLUP。

    SELECT语句的代码如下:

    SELECT sex,GROUP_CONCAT(name) FROM employee GROUP BY sex WITH ROLLUP;

    在DOS提示符窗口中查看SELECT语句WITH ROLLUP参数的操作效果。如下图所示:

    4497.gif

    上图中代码执行的结果显示,GROUP_CONCAT(name)显示了每个分组的name字段的值。同时,最后一条记录的GROUP_CONCAT(name)列的值正好是上面分组name取值的总和。

    展开全文
  • 数据库多表查询

    2019-01-09 16:15:48
    多表的数据操作 之间的关系 一对多多对一) 在的一方加入一的一方的外键。...使用 WHERE 条件将两个之间进行关联查询查询学员的学号、姓名、所在班级名称 SELECT s.clazz_id,s.code,s...
  • 注意:聚合不应出现在 WHERE 子句中,除非该聚合位于 HAVING 子句或选择列表所包含的子查询中,并且要进行聚合的列是外部引用。 正确写法: select Sno,AVG(Grade) from sc group by Sno having avg(grade)>...
  • 包括模糊查询、分组、排序、各种筛选条件等等操作,在实际应用中,查询的数据往往不止局限在一张表里,通常需要张表在一起进行组合查询,今天我们将会Mysql当中的张有关联的表进行组合查询,本节内容涉及到的...
  • 数据库表连接查询

    2013-05-08 13:52:50
    如果一个查询需要对多表进行操作就称为连接查询,连接查询的结果集或结果称为表之间的连接。 连接查询实际上是通过 各个 表之间共同列 的关联性 来查询数据的,它是关系数据库查询最主要的特征。 select 表1....
  • 包括模糊查询、分组、排序、各种筛选条件等等操作,在实际应用中,查询的数据往往不止局限在一张表里,通常需要张表在一起进行组合查询,今天我们将会Mysql当中的张有关联的表进行组合查询,本节内容涉及到的...
  • 包括模糊查询、分组、排序、各种筛选条件等等操作,在实际应用中,查询的数据往往不止局限在一张表里,通常需要张表在一起进行组合查询,今天我们将会Mysql当中的张有关联的表进行组合查询,本节内容涉及到的...
  • 数据库中的多表查询问题

    千次阅读 2015-08-19 11:59:47
    使用比较运算符各个内的数据进行比较操作,列出符合条件的各列。关键字为inner join 或者 join 内连接又分为等值连接 非等值连接、自然连接。 (2)外连接 当数据中的一些行在其他中不存在匹配行时,...
  • 本文以某一比赛样式要求为基础,数据表多条件模糊查询进行分析与实现说明,为数据库SQL初学者提供参考。问题描述多条件模糊查询需求来于项目开发需求,本项目提供信息查询页面如下所示,开发者需要为用户提供...
  • 本文以某一比赛样式要求为基础,数据表多条件模糊查询进行分析与实现说明,为数据库SQL初学者提供参考。问题描述多条件模糊查询需求来于项目开发需求,本项目提供信息查询页面如下所示,开发者需要为用户提供...
  • 针对在Select中返回单DataSet的情况,该方法进行了重载,采用最常用的InnerJoin连接返回多表数据相关资料链接:数据库层抽象工厂解决方案归档[不断升级版] 数据库层抽象工厂更新1——多表内连接条件查询操作 /...
  • SQL提供select语句在一定条件指定的基本表进行查询。由于查询需求的复杂程度不同,可分为单表查询查询。两种查询将在后面依次进行介绍。 在介绍单表查询查询之前,首先我们提出select 语句查询...
  • 数据库在通过连接两张或张表来返回记录时,都会生成一张中间的临时表,...2、where条件是在临时表生成好后,再临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全...
  • 在进行数据库查询时,有时需要用到既有的数据表进行多查询得出的临时条件的数据表,就可以暂时创建成为虚表,并赋予简单明了的字段名以及临时表名。例题a:查询出每门课程低于平均成绩的学生姓名、课程名称、...
  • 有个要求需要一张记录条数为3万条的表进行筛选排序,筛选及排序条件会和其它表内容值相关,且要求要在1秒之内将这些内容按要求筛选并排序出来,有什么比较好的方法吗?有初步打算是将大致范围从数据库里获取到...
  • 进行多表连接查询时, [驱动] 的定义为: 1)指定了联接条件时,满足查询条件的记录行数少的为[驱动] 2)未指定联接条件时,行数少的为[驱动](Important!) left join 则左边的为驱动 right join 则...
  • 数据进行查询时,有时候并不知道 完整的条件是什么,这时候就需要用到模糊查询。 模糊查询的关键字是LIKE 占位符: _:单个任意字符 %:个任意字符 这里以下为例子进行举例: 其中有4个姓马的人物,现在...
  • 所以考虑编写一个方法可以对数据库对象进行多条件查询,并返回泛型对象,这样就可以方便使用。具体实现思路如下:第一步:编写数据库查询参数对象,此部分包含两个,一个是查询实体名称(Quer...
  • 查询操作本章主要内容1简单查询2查询结果排序3主要内容条件查询4限制查询5使用函数查询6分组查询 查询数据是指用户根据不同的需求使用不同的查询方式在数据中获取自己所需要的数据是数据库操作中最重要也是...
  • 当两个或个表中存在相同意义的字段时,便可以通过这些字段不同的表进行连接查询。如下将介绍表之间的内连接查询、外连接查询以及复合条件连接查询。内连接查询 内连接(inner join)使用比较运算符进行表间列数....

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,435
精华内容 574
关键字:

对数据库表进行多条件查询