mysql-8中select多表连接

mysql-8中select多表连接

1.表关联查询的时候首先两张表在关联查询的时候会形成一个笛卡尔积的形式,然后再通过where条件对形成的笛卡尔积进行条件筛选.

select * from students as s inner join score as b where s.sid = b.sid and s.sid =3;

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

| sid | sname | gender | dept_id | brithday            | sid | course_id | score |

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

|   3 | Bob   | 0      |       1 | 1983-01-01 00:00:00 |   3 |         1 |    48 |

|   3 | Bob   | 0      |       1 | 1983-01-01 00:00:00 |   3 |         2 |    95 |

|   3 | Bob   | 0      |       1 | 1983-01-01 00:00:00 |   3 |         3 |    75 |

|   3 | Bob   | 0      |       1 | 1983-01-01 00:00:00 |   3 |         4 |    89 |

|   3 | Bob   | 0      |       1 | 1983-01-01 00:00:00 |   3 |         5 |    92 |

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

如果在形形成交集以后那么可以再条件后面再次添加条件进行结果的筛选.

2.同事查询两张表和连表查询结果貌似一样.使用,和使用inner join 的表关联结果是一样的.

select * from students inner join dept where students.dept_id > dept.id;

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

| sid | sname | gender | dept_id | brithday            | id | dept_name        |

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

|   4 | Ruth  | 1      |       2 | 1983-01-01 00:00:00 |  1 | Education        |

|   5 | Mike  | 0      |       2 | 1986-01-01 00:00:00 |  1 | Education        |

|   6 | John  | 0      |       3 | 1986-01-01 00:00:00 |  1 | Education        |

|   6 | John  | 0      |       3 | 1986-01-01 00:00:00 |  2 | Computer Science |

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

使用逗号进行两表关联的时候使用的是where条件进行筛选结果.

 select * from students inner join score where students.sid = score.sid;

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

| sid | sname | gender | dept_id | brithday            | sid | course_id | score |

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

|   3 | Bob   | 0      |       1 | 1983-01-01 00:00:00 |   3 |         1 |    48 |

|   3 | Bob   | 0      |       1 | 1983-01-01 00:00:00 |   3 |         2 |    95 |

|   5 | Mike  | 0      |       2 | 1986-01-01 00:00:00 |   5 |         3 |    90 |

|   5 | Mike  | 0      |       2 | 1986-01-01 00:00:00 |   5 |         4 |    82 |

|   6 | John  | 0      |       3 | 1986-01-01 00:00:00 |   6 |         2 |    58 |

|   6 | John  | 0      |       3 | 1986-01-01 00:00:00 |   6 |         4 |    88 |

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

12 rows in set (0.00 sec)

使用inner join进行表关联的时候使用 on进行筛选结果,其实使用inner join也是用where就行.

结果是一样的.但是如果使用on进行结果筛选的话后面还可以再加where条件进行二次筛选.

或者直接加and 进行条件的增加.

3.如果两张表中有重复字段名的时候需要指定筛选结果和输出结果中的字段名称是那张表的.

select students.sid,score.sid from students,score where students.sid = score.sid;

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

| sid | sid |

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

|   3 |   3 |

|   5 |   5 |

|   6 |   6 |

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

12 rows in set (0.00 sec)

如果不进行字段名称指定的话会出现查询结果模糊输出错误的情况.

4.求成绩表中每个学生的学科数量,最大值,最小值,平均值.分组后每组所对应的最大值,最小值,平均值.

select sid,count(*) as num,max(score),min(score),avg(score) from score group by sid;

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

| sid | num | max(score) | min(score) | avg(score) |

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

|   2 |   4 |         92 |         65 |    78.0000 |

|   3 |   5 |         95 |         48 |    79.8000 |

|   4 |   2 |         78 |         67 |    72.5000 |

|   5 |   3 |         90 |         75 |    82.3333 |

|   6 |   2 |         88 |         58 |    73.0000 |

|   7 |   5 |         70 |         55 |    64.2000 |

|   8 |   2 |        100 |         88 |    94.0000 |

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

sid 分组,前面打印sid,然后分别对应后面的 学科数,最大值,最小值,平均值.

5.limit用法

select * from score limit 2,2;

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

| sid | course_id | score |

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

|   2 |         3 |    77 |

|   2 |         5 |    65 |

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

2 rows in set (0.00 sec)

表示从第几行开始取值,向后取糊几个值.

6.对某一个字段去重并统计个数.distinct

select count(distinct sid) from score;

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

| count(distinct sid) |

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

|                   7 |

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

1 row in set (0.00 sec)

表示对score表的sid字段去重,然后统计数量.

7.通过select命令将数据文件导出到系统磁盘文件中.

首先要更改数据库配置文件.添加

secore_file_priv='/tmp/'

然后重启数据库,

/etc/init.d/mysql.server restart

然后进入数据库中,并且进入相应的数据执行查询输出语句的执行命令.

select sid,sname,gender,dept_id into outfile "/tmp/students.txt1" from students;

然后就可以在系统中的/tmp/目录中查看到一个叫做students.txt的数据文件.

8.恢复select所导出的数据文件注意,这里导出的数据文件仅仅是数据,并不包含表结构.

首先需要将原有的相同字段的所有数据都清空,

truncate table students_temp.

然后根据现有的数据字段来恢复数据.

load data infile "/tmp/students.txt" into table students_temp;

最后查看数据是否恢复正常.

补充分隔符问题

SELECT

*

FROM

students INTO OUTFILE '/tmp/loaddatass.txt'  //表示导出到系统中的哪个目录文件中

FIELDS TERMINATED BY ';'        //指定字段之间的分隔符为分号

OPTIONALLY ENCLOSED BY '"'      //指定字符串分隔符为双引号

LINES TERMINATED BY '\n';        //指定结尾为换行符

 

9.修改表名称

alter table students_old  rename as  students;

Query OK, 0 rows affected (0.13 sec)

表示将前面表名改成后面表明.

10.左连接或者右连接,显示的内容是以左表或者右表为基准.进行显示.一张表显示全部,剩下的显示交集.左边围巾准有交集显示交集,没有交集显示空,

select * from dept left join students on dept.id=students.dept_id;

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

| id | dept_name        | sid  | sname | gender | dept_id | brithday            |

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

|  1 | Education        |    3 | Bob   | 0      |       1 | 1983-01-01 00:00:00 |

|  2 | Computer Science |    4 | Ruth  | 1      |       2 | 1983-01-01 00:00:00 |

|  2 | Computer Science |    5 | Mike  | 0      |       2 | 1986-01-01 00:00:00 |

|  3 | Mathematics      |    6 | John  | 0      |       3 | 1986-01-01 00:00:00 |

|  4 | Music            | NULL | NULL  | NULL   |    NULL | NULL                |

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

11.反之以students表为基准的话那么students表有几条数据就显示几条数据,如果有students表比dept表多的数据就显示null

select * from students left join dept on students.dept_id= dept.id;

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

| sid | sname | gender | dept_id | brithday            | id   | dept_name        |

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

|   3 | Bob   | 0      |       1 | 1983-01-01 00:00:00 |    1 | Education        |

|   4 | Ruth  | 1      |       2 | 1983-01-01 00:00:00 |    2 | Computer Science |

|   5 | Mike  | 0      |       2 | 1986-01-01 00:00:00 |    2 | Computer Science |

|   6 | John  | 0      |       3 | 1986-01-01 00:00:00 |    3 | Mathematics      |

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

4 rows in set (0.00 sec)

​

12.多个表进行关联的时候从前往后依次进行条件匹配.例如

select * from dept left join students on students.dept_id = dept.id inner join students2_tmp on students.sid=students2_tmp.sid;

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

| id | dept_name | sid  | sname | gender | dept_id | brithday            | sid | sname | gender | dept_id | brithday            |

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

|  1 | Education |    3 | Bob   | 0      |       1 | 1983-01-01 00:00:00 |   3 | Bob   | 0      |       1 | 1983-01-01 00:00:00 |

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

1 row in set (0.00 sec)

最后显示的结果是前两个表左连接的集合后的结果与后一个表的交集.

12.union和union all.

查询结果需要集中展示的情况下可以使用union,

select sid,sname from students union all select dept_name,id from dept;

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

| sid              | sname |

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

| 3                | Bob   |

| 4                | Ruth  |

| 5                | Mike  |

| 6                | John  |

| Education        | 1     |

| Computer Science | 2     |

| Mathematics      | 3     |

| Music            | 4     |

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

8 rows in set (0.00 sec)

但是如果查询的两个表中的字段值有都相同(组合结果)的情况存在的话那么union只显示一条.

如果查询结果只有一条那么会在最终显示的时候去重.union all就会全部显示.

如果数据中能保证合并展示的数据不会重复的话那么一般写成union all比较合适,为系统省去去重的操作.

​

13.使用括号保证查询的优先级.union 或者union all 是在两边的数据查询完成以后再进行结果的合并.

select *from (select sid from students union all select sid from students2_tmp) as t where sid >2;

+-----+

| sid |

+-----+

|   3 |

|   4 |

|   5 |

|   6 |

|   3 |

+-----+

括号中的语句可以看做是一张虚拟表,并且需要取一个别名.然后再进行这张表的查询.

并且添加筛选条件.

​