DQL笔记

distinct去重
嵌套查询
查询李姓同学的成绩

select * from result where studentno in (select studentno from student where studentname like '李%');

内联外联

1.内联

person表

mysql> select * from person;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | zs   |   28 |
|    2 | ls   |   25 |
|    3 | ww   |   29 |
|    4 | zl   |   35 |
|    5 | zq   |   30 |
+------+------+------+

part表

mysql> select * from part;
+------+-----------+--------------------------------+
| id   | pname     | desc                           |
+------+-----------+--------------------------------+
|    2 | 研发部    | 负责公司的研发工作             |
|    3 | 行政部    | 负责公司内部事务管理           |
|    4 | 总经办    | 摸鱼                           |
|    6 | 研发部    | 负责公司的研发工作             |
|    7 | 行政部    | 负责公司内部事务管理           |
+------+-----------+--------------------------------+
5 rows in set (0.00 sec)

两个表的id字段相通
连接的表名 inner join 连接的表名 on 两个表用来连接的字段

select person.*,part.* from person inner join part on person.id=part.id;
#      查询                 将person的id与part的id相连

mysql> select person.*,part.* from person inner join part on person.id=part.id;
+------+------+------+------+-----------+--------------------------------+
| id   | name | age  | id   | pname     | desc                           |
+------+------+------+------+-----------+--------------------------------+
|    2 | ls   |   25 |    2 | 研发部    | 负责公司的研发工作             |
|    3 | ww   |   29 |    3 | 行政部    | 负责公司内部事务管理           |
|    4 | zl   |   35 |    4 | 总经办    | 摸鱼                           |
+------+------+------+------+-----------+--------------------------------+

内联相当于交集

2.外联

左连接

select person.*,part.* from person left join part on person.id=part.id;

mysql> select person.*,part.* from person left join part on person.id=part.id;
+------+------+------+------+-----------+--------------------------------+
| id   | name | age  | id   | pname     | desc                           |
+------+------+------+------+-----------+--------------------------------+
|    1 | zs   |   28 | NULL | NULL      | NULL                           |
|    2 | ls   |   25 |    2 | 研发部    | 负责公司的研发工作             |
|    3 | ww   |   29 |    3 | 行政部    | 负责公司内部事务管理           |
|    4 | zl   |   35 |    4 | 总经办    | 摸鱼                           |
|    5 | zq   |   30 | NULL | NULL      | NULL                           |
+------+------+------+------+-----------+--------------------------------+

左边的person表显示完整，邮编显示part id相同的部分数据，以左边的表为主
从左表 (table 1)中返回所有的记录，即便在右 (table 2) 中没有匹配的行

右连接

select person.*,part.* from person right join part on person.id=part.id;

mysql> select person.*,part.* from person right join part on person.id=part.id;
+------+------+------+------+-----------+--------------------------------+
| id   | name | age  | id   | pname     | desc                           |
+------+------+------+------+-----------+--------------------------------+
|    2 | ls   |   25 |    2 | 研发部    | 负责公司的研发工作             |
|    3 | ww   |   29 |    3 | 行政部    | 负责公司内部事务管理           |
|    4 | zl   |   35 |    4 | 总经办    | 摸鱼                           |
| NULL | NULL | NULL |    6 | 研发部    | 负责公司的研发工作             |
| NULL | NULL | NULL |    7 | 行政部    | 负责公司内部事务管理           |
+------+------+------+------+-----------+--------------------------------+

右边的part表显示完整，左边的person显示与part id 相同的数据，以右边的表为主
从右表 (table 2) 中返回所有的记录，即便在左 (table 1) 中没有匹配的行

3.笛卡尔积

笛卡尔积，又叫cross join，是SQL中两表连接的一种方式。 假如A表中的数据为m行，B表中的数据有n行，那么A和B做笛卡尔积，结果为m*n行。 通常我们都要在实际SQL中避免直接使用笛卡尔积，因为它会使“数据爆炸”，尤其是数据量很大的时候。 但某些时候，巧妙的使用笛卡尔积，反而能快速帮助我们解决实际问题。

select person.*,part.* from person,part;#笛卡尔积
select person.*,part.* from person cross join part;

+------+------+------+------+-----------+--------------------------------+
| id   | name | age  | id   | pname     | desc                           |
+------+------+------+------+-----------+--------------------------------+
|    5 | zq   |   30 |    2 | 研发部    | 负责公司的研发工作             |
|    4 | zl   |   35 |    2 | 研发部    | 负责公司的研发工作             |
|    3 | ww   |   29 |    2 | 研发部    | 负责公司的研发工作             |
|    2 | ls   |   25 |    2 | 研发部    | 负责公司的研发工作             |
|    1 | zs   |   28 |    2 | 研发部    | 负责公司的研发工作             |
|    5 | zq   |   30 |    3 | 行政部    | 负责公司内部事务管理           |
|    4 | zl   |   35 |    3 | 行政部    | 负责公司内部事务管理           |
|    3 | ww   |   29 |    3 | 行政部    | 负责公司内部事务管理           |
|    2 | ls   |   25 |    3 | 行政部    | 负责公司内部事务管理           |
|    1 | zs   |   28 |    3 | 行政部    | 负责公司内部事务管理           |
|    5 | zq   |   30 |    4 | 总经办    | 摸鱼                           |
|    4 | zl   |   35 |    4 | 总经办    | 摸鱼                           |
|    3 | ww   |   29 |    4 | 总经办    | 摸鱼                           |
|    2 | ls   |   25 |    4 | 总经办    | 摸鱼                           |
|    1 | zs   |   28 |    4 | 总经办    | 摸鱼                           |
|    5 | zq   |   30 |    6 | 研发部    | 负责公司的研发工作             |
|    4 | zl   |   35 |    6 | 研发部    | 负责公司的研发工作             |
|    3 | ww   |   29 |    6 | 研发部    | 负责公司的研发工作             |
|    2 | ls   |   25 |    6 | 研发部    | 负责公司的研发工作             |
|    1 | zs   |   28 |    6 | 研发部    | 负责公司的研发工作             |
|    5 | zq   |   30 |    7 | 行政部    | 负责公司内部事务管理           |
|    4 | zl   |   35 |    7 | 行政部    | 负责公司内部事务管理           |
|    3 | ww   |   29 |    7 | 行政部    | 负责公司内部事务管理           |
|    2 | ls   |   25 |    7 | 行政部    | 负责公司内部事务管理           |
|    1 | zs   |   28 |    7 | 行政部    | 负责公司内部事务管理           |
+------+------+------+------+-----------+--------------------------------+

连表查询

查询subject 中的subjectname和grade 中对的gradename 字段

select s.subjectname,g.gradename from subject s inner join grade g on s.subjectno = g.subjectno;

+---------------+-----------+
| subjectname   | gradename |
+---------------+-----------+
| 高等数学1     | 大一      |
| 高等数学2     | 大二      |
| java编程      | 大三      |
| hadoop理论    | 大四      |
+---------------+-----------+

创建自连接

create table if not exists area(id int(10) primary key auto_increment,name varchar(32),pid int(10),level int(10));
insert into area(name,level) value ('中国',0);
insert into area(name, pid, level) values ('江苏省',1,1),('安徽省',1,1),('山西省',1,1);
select * from area;
insert into area(name, pid, level) values ('南京市',2,2),('无锡市',2,2),('苏州市',2,2);
insert into area(name, pid, level) values ('合肥市',3,2),('芜湖市',3,2),('蚌埠市',3,2);
insert into area(name, pid, level) values ('太原市',4,2),('大同市',4,2),('运城',4,2);

+----+-----------+------+-------+
| id | name      | pid  | level |
+----+-----------+------+-------+
|  1 | 中国      | NULL |     0 |
|  2 | 江苏省    |    1 |     1 |
|  3 | 安徽省    |    1 |     1 |
|  4 | 山西省    |    1 |     1 |
|  5 | 南京市    |    2 |     2 |
|  6 | 无锡市    |    2 |     2 |
|  7 | 苏州市    |    2 |     2 |
|  8 | 合肥市    |    3 |     2 |
|  9 | 芜湖市    |    3 |     2 |
| 10 | 蚌埠市    |    3 |     2 |
| 11 | 太原市    |    4 |     2 |
| 12 | 大同市    |    4 |     2 |
| 13 | 运城      |    4 |     2 |
+----+-----------+------+-------+

select a1.name '市',a2.name '省',a3.name'国家' from area a1 left join area a2 on a1.pid = a2.id
left join area a3 on a2.pid=a3.id where a1.level = 2;

+-----------+-----------+--------+
| 市        | 省        | 国家   |
+-----------+-----------+--------+
| 南京市    | 江苏省    | 中国   |
| 无锡市    | 江苏省    | 中国   |
| 苏州市    | 江苏省    | 中国   |
| 合肥市    | 安徽省    | 中国   |
| 芜湖市    | 安徽省    | 中国   |
| 蚌埠市    | 安徽省    | 中国   |
| 太原市    | 山西省    | 中国   |
| 大同市    | 山西省    | 中国   |
| 运城      | 山西省    | 中国   |
+-----------+-----------+--------+

### 单、多条件排序

改变学号1003科目2 的成绩

update result set studentresult=99 where studentno=1003 order by subjectno = 2;

select * from result where studentno=1003 order by studentresult desc;#降序
select * from result where studentno=1003 order by studentresult asc;#升序
select * from result where studentno=1003 order by studentresult desc,subjectno desc;#先按成绩降序，如果成绩相同，则按科目编号降序

查询java编程课程的学生成绩，由高到低

select studentno,subjectname,studentresult from result left join subject on result.subjectno=subject.subjectno where subjectname='java编程' order by studentresult desc ;