mysql学习

1.嵌套查询

 查询出姓李的同学的所有成绩
mysql> select * from result where studentno in (select studentno from student where studentname like '李%');

举例:mysql> select * from result where studentno in (select studentno from student where studentname like '李%');

2.连接查询

 

person    part
id          id
select person.*,part.* from person inner join part on person.id=part.id

内连接
mysql> select person.*,part.* from person inner join part on person.id=part.id;
mysql> select person.*,part.* from person 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 | 总经办    | 摸鱼                           |
+------+------+------+------+-----------+--------------------------------+
左外连接
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                           |
+------+------+------+------+-----------+--------------------------------+

右外连接
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 | 行政部    | 负责公司内部事务管理           |
+------+------+------+------+-----------+--------------------------------+

mysql> select person.*,part.* from person,part;
mysql> 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字段
mysql> select s.subjectname, g.gradename from subject s join grade g on s.subjectno=g.subjectno;
+---------------+-----------+
| subjectname   | gradename |
+---------------+-----------+
| 高等数学1     | 大一      |
| 高等数学2     | 大二      |
| java编程      | 大三      |
| hadoop理论    | 大四      |
+---------------+-----------+

自连接

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

mysql> select a1.name,a2.name from area a1 left join area a2 on a1.pid=a2.id where a1.level=2;mysql> select a1.name city,a2.name province,a3.name country from area a1 left join area a2 on a1.pid=a2.id lefft join area a3 on a2.pid=a3.id where a1.level=2;

mysql> select a1.name city,a2.name province,a3.name country 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 and a1.name='大同市';

mysql> insert into area(name,pid,level) values('鼓楼区',5,3),('建邺区',5,3),('雨花台区',5,3);
mysql> insert into area(name,pid,level) values('芜湖县',9,3),('繁昌县',9,3),('南宁县',9,3);

求出芜湖县所在的城市，省份，国家？
select from area a1 left join area a2 on a1.pid=a2.id and a1.name='芜湖县'
| id | name         | pid  | level || id | name         | pid  | level || id | name         | pid  | level || id | name         | pid  | level |
| 17 | 芜湖县       |    9 |     3 ||  9 | 芜湖市       |    3 |     2 ||  3 | 安徽省       |    1 |     1 ||  1 | 中国         |   -1 |     0 |
select a1.name,a2.name,a3.name,a4.name from area a1 left join area a2 on a1.pid=a2.id and a1.name='芜湖县'
left join area a3 on a2.pid=a3.id 
left join area a4 on a3.pid=a4.id 
where a1.name='芜湖县'

升降序操作和分页查询

降序
mysql> select * from result where studentno=1003 order by studentresult desc;
升序
mysql> select * from result where studentno=1003 order by studentresult asc;

多列排序 先按成绩降序，成绩相同则按课程编号降序
mysql> select * from result where studentno=1003 order by studentresult desc, subjectno desc;

查询出java编程课程的学生成绩，由高到低排序
mysql> select r.studentresult,s.subjectname from result r left join subject s on r.subjectno=s.subjectno where s.subjectname='java编程' order by r.studentresult desc;

Limit  查询前五条数据
mysql> select * from result limit 5;

从下标为六的数据开始，查三条
mysql> select * from result limit 6,3;

limit 0,5
limit 5,5
limit 10,5
limit (n-1)*5,5

查询hadoop理论课的学生学号，姓名，成绩，按成绩排序，分页
mysql> select stu.studentno,stu.studentname, r.studentresult from result r left join subject sub on r.subjectno=sub.subjectno  left join student stu on stu.studentno=r.studentno  where sub.subjectname='hadoop理论'  order by r.studentresult desc limit 2;
+-----------+-------------+---------------+
| studentno | studentname | studentresult |
+-----------+-------------+---------------+
|      1001 | 李四        |           100 |
|      1003 | 李冰冰      |           100 |
+-----------+-------------+---------------+
2 rows in set (0.00 sec)

mysql> select stu.studentno,stu.studentname, r.studentresult from result r left join subject sub on r.subjectno=sub.subjectno  left join student stu on stu.studentno=r.studentno  where sub.subjectname='hadoop理论'  order by r.studentresult desc limit 2,2;
+-----------+-------------+---------------+
| studentno | studentname | studentresult |
+-----------+-------------+---------------+
|      1000 | 张三        |            85 |
|      1002 | 王五        |            63 |
+-----------+-------------+---------------+
2 rows in set (0.00 sec)

mysql> select stu.studentno,stu.studentname,sub.subjectname, r.studentresult from result r left join subjectsub on r.subjectno=sub.subjectno  left join student stu on stu.studentno=r.studentno  where sub.subjectname='hhadoop;

select * from result where subjectno=
(select subjectno from subject where subjectname='hadoop理论')