WEB 12.MySQL

1.排序 order by

	desc降序排序 
	asc 升序排序 默认不写就是升序排序 
	1.查询所有学员信息 并按照年龄进行降序排序
		select * from students order by age desc;
	2.查询班级为python的男生信息 并按照年龄进行降序排序
		select * from students where class='python' and gender='1' order by age desc; 

2.分页获取数据 limit

	limit 1 ; 从前面获取前一条数据
	limit 1,1; 跳过第一条数据取一条数据

	1.获取前三个学员的数据
	select * from students limit 3;
	2.获取第二个学员的信息
	select * from students limit 1,1;

	使用limit实现分页效果
	要求 没两条数据为一页
	取第一页数据
	select * from students limit 0,2;   （1-1）*每一页数据的条数
	取第二页数据
	select * from students limit 2,2;    (2-1)*2=2
	取第三页
	select * from students limit 4,2;    （3-1）*2=4

	取第五页数据       （5-1）*2=8 
	select * from students limit (5-1)*2,2;  

练习：
	1.获取Python班级年龄最大的2个学生
	select * from students where class='python' order by age desc limit 2;
	2.获取每个班级的平均年龄，并按照平均年龄进行降序排序
	select class,avg(age) from students group by class order by avg(age) desc;
	3.统计每个班级的人数 获取人数最多的两个班级  
	select class,count(*) from students group by class  order by count(*) desc limit 2;  

顺序问题：
weher group by order by limit;

3.嵌套子查询

	将一条查询一句 嵌入到另一条查询语句当中去
	1.可做为数据源 
	2.可以作为查询条件

	1.查询年龄大于18岁的男生
	select * from students where age>18 and gender='1';

	select * from students where age>18;

	select * from (select * from students where age>18) as newtable  where newtable.gender='1';

	查询每个班级的男生和女生的人数 获取女生人数为0的班级
	    select class,gender,count(*) from students group by class,gender;  就吧没有女生的班级排出掉了

	查询的有女生的班级
	1.select class from students group by class,gender having gender!='1'
	2.那1和原表数据做取反 最终得到是没有女生的班级
	select * from students where class not in (select class from students group by class,gender having gender!='1');

	第二种方式
	select class,count(*) from students group by class having count(*)=sum(gender);

4.数据库关系搭建

一对一

		一张表中唯一一条数据对应另一张表中唯一一条数据
		users								
			id	uname	phone	password	age	emial	pic	address

		把一张表的数据库查分成两张表，并通过外键字段将两张表中的数据进行关联

		user			
		id	uname	password
		1	张三	123
		2	李四	123

		uinfo							
		id	phone	age	emial	pic	address	u_id
		1	110	18	zh@qq.com	1.jpg	beijing	1
		2	120	25	123	123	231	2

		1.创建两个表
		create table user(
			id int unsigned not null auto_increment primary key,
			uname char(32)
		)
		create table uinfo(
			id int unsigned not null auto_increment primary key, 
			phone varchar(11),
			age int,
			email varchar(32),
			gender varchar(10),
			address varchar(255) default '北京',
			u_id int
		)

		2.插入数据
		insert into user values(null,'老张'),(null,'老牛'),(null,'老刘'),(null,'老钟');
		insert into uinfo values(null,'110',18,'laozhang@qq.com','1','北京昌平',1),
								(null,'120',28,'laoniu@qq.com','1','北京朝阳',2),
								(null,'119',38,'laoliu@qq.com','1','黑龙江',3);

		通过用户去查询用户的详情
			根据用户名老张 查询老张的详细信息
			1.select id from user where uname='老张';
			2.select * from uinfo where u_id=1;

			select * from uinfo where u_id=(select id from user where uname='老刘');

		通过用户详情去查询用户
			已知手机号为120 查询手机号对应的用户

			select * from user where id=(select u_id from uinfo where phone='120');

			查询年龄为28和38的用户信息
			1.select u_id from uinfo where age=28 or age=38;
			2.select * from user where id in(select u_id from uinfo where age=28 or age=38);

一对多

		一张表中的一条数据 对应另一张表中的多条数据
		parent		
			id	name
			1	老张
			2	老李

		child			
			id	name	p_id
			1	张1		1
			2	张2		1
			3	李1		2
			4	李2		2

		根据父亲找儿子
			查找老张所有的孩子
			select * from child where p_id=(select id from parent where name='老张');
		根据儿子找父亲
			查询张1的父亲
			select * from parent where id=(select p_id from child where name='张1' );

多对多

		一张表中的多条数据 对应另一张表中的多条数据

		teachers		
			id	name
			1	老张
			2	老李
			3	老边
		students		
			id	name
			1	小黄
			2	小兰
			3	小绿
			4	小花
			5	小红
				
		t_s		
			t_id	s_id
			1		1
			1		2
			1		3
			3		5
			1		5
			2		5

	1.创建表
		create table teacher(
		id int unsigned not null auto_increment primary key, 
		name varchar(10)
		);

		create table teacher_student(
		id int unsigned not null auto_increment primary key, 
		t_id int,
		s_id int 
		);

	2.添加数据
		insert into teacher values(null,'老刘'),(null,'老边'),(null,'老温');

	表数据：
		老师：
			+----+------+
			| id | name |
			+----+------+
			|  1 | 老刘 |
			|  2 | 老边 |
			|  3 | 老温 |
			+----+------+
		学生：
			+----+--------+------+--------+-----------------+--------+
			| id | name   | age  | gender | email           | class  |
			+----+--------+------+--------+-----------------+--------+
			|  2 | 小张   |   19 | 1      | xiaowang@qq.com | python |
			|  5 | 赵六   |   30 | 1      | z@163.com       | web    |
			|  6 | 田七   | NULL | NULL   | NULL            | NULL   |
			|  7 | 王八   |   28 | 0      | w@.com          | ui     |
			|  8 | 九九   |   19 | 1      | j@qq.com        | python |
			|  9 | 后羿   |   80 | 1      | h@qq.com        | python |
			| 10 | 庄周   | NULL | NULL   | NULL            | NULL   |
			| 11 | 阿珂   |   18 | 0      | NULL            | python |
			| 12 | 贾克斯 |   24 | 1      | NULL            | 上单   |
			+----+--------+------+--------+-----------------+--------+
		关系：
			添加关系

				1     2
				1     5
				1     12
				2     2
				2     5
				2     12
				2     7
			insert into teacher_student values(null,1,2),(null,1,5),(null,1,12),(null,2,2),(null,2,5),(null,2,12),(null,2,7);

			+----+------+------+
			| id | t_id | s_id |
			+----+------+------+
			|  1 |    1 |    2 |
			|  2 |    1 |    5 |
			|  3 |    1 |   12 |
			|  4 |    2 |    2 |
			|  5 |    2 |    5 |
			|  6 |    2 |   12 |
			|  7 |    2 |    7 |
			+----+------+------+

		查询：
			查老刘老师带过的所有学生的信息
			1.查老师的id
			select id from teacher where name='老刘';
			2.根据老师的id去关系表查对应学生id;
			select s_id from teacher_student where t_id=(select id from teacher where name='老刘');
			3.根据学生id去查学生表
			select * from students where id in(select s_id from teacher_student where t_id=(select id from teacher where name='老刘'));

			查老刘老师带过的年龄大于19岁的学生信息
			select * from students where id in (select s_id from teacher_student where t_id in(select id from teacher where name='老刘')) and age>19;

			查询贾克斯被哪些老师带过
				1.根据名字查询学生id
				select id from students where name='贾克斯'
				2.根据学生的id去关系表查询对应t_id
				select t_id from teacher_student where s_id=(select id from students where name='贾克斯');
				3.根据t_id 去老师表查
				select * from teacher where id in(select t_id from teacher_student where s_id=(select id from students where name='贾克斯'));

5.外键

	逻辑外键
	物理外键 会增加表和表之间的复杂度，会降低查询的速度

	外键约束：foreign key(当前表的外键字段) references 关联的表名(关联表的字段)
		外键主要使用约束一对多关系
			一般情况下 多表中的外键字段 关联一表中的主键  


	学员和宿舍的关系
		student      dormitory
						id       num

	1.创建表
		create table dormitory(
			id int unsigned not null auto_increment primary key,
			num int
		); 
	2.学生
		create table student(
			id int unsigned not null auto_increment primary key,
			name char(32),
			age int,
			gender int,
			d_id int unsigned,
			foreign key(d_id) references dormitory(id)
		);

	专业mojar 课程course  mojar_course
		create table mojar(
			id int unsigned not null auto_increment primary key,
			name char(32)
		); 

		create table course(
			id int unsigned not null auto_increment primary key,
			name char(32)
		);

		create table mojar_course(
			id int unsigned not null auto_increment primary key,
			m_id int unsigned,
			c_id int unsigned,
			foreign key(m_id) references mojar(id),
			foreign key(c_id) references course(id)
		);

	注意：添加外键约束时
		1.要先有外键字段
		2.外键字段的类型 必须和要关联的字段类型保持一至

6.多表查询

1.嵌套查询

where 关联查询

		只能查询两张表都有对应数据的信息
		1.查看每个学生的宿舍信息
		select * from student s,dormitory d where s.d_id=d.id;
		2.查询小红住在哪个宿舍
		select s.id,s.name,d.num from student s,dormitory d where s.d_id=d.id and name='小红';

		3.所有专业对应的课程
		select * from mojar m,mojar_course m_c,course c where m.id=m_c.m_id and m_c.c_id=c.id;
		4.查询python都要学习那些课程
		select m.id,m.name,c.name from mojar m,mojar_course m_c,course c where m.id=m_c.m_id and m_c.c_id=c.id and m.name='python';

		5.查询毛概都有哪些专业在学
		select m.name from mojar m,mojar_course m_c,course c where m.id=m_c.m_id and m_c.c_id=c.id and c.name='毛概';

2.连接查询

inner join - left join - right join

		inner join 内链接 结果和where关联查询结果一样
			格式： select * from 表1 inner join 表2 on 关联条件
			1.查看每个学生的宿舍信息
			select * from student s inner join dormitory d on s.d_id=d.id; 
			2.查询小红住在哪个宿舍
			select s.id,s.name,d.num from student s inner join dormitory d on s.d_id=d.id and s.name='小红';
			3.所有专业对应的课程
			select * from 
				mojar m 
			inner join 
				mojar_course m_c 
			on 
				m.id=m_c.m_id 
			inner join 
				course c 
			on 
				m_c.c_id=c.id;

			4.查询python都要学习那些课程
 select * from 
				mojar m 
			inner join 
				mojar_course m_c 
			on 
				m.id=m_c.m_id 
			inner join 
				course c 
			on 
				m_c.c_id=c.id and m.name='python';


			5.查询毛概都有哪些专业在学
 select * from 
				mojar m 
			inner join 
				mojar_course m_c 
			on 
				m.id=m_c.m_id 
			inner join 
				course c 
			on 
	m_c.c_id=c.id and c.name='毛概';

		left join   外左连接
			以左表为准，将坐表中所有的数据查询出来，去右表匹配信息，
				如果没有对应的信息以null占位
			1.查看每个学生的宿舍信息
				select * from student s left join dormitory d on s.d_id=d.id;
			2.哪些学员没有宿舍
				select * from student s left join dormitory d on s.d_id=d.id where d.num is null;
			3.哪些宿舍是空宿舍
				select * from dormitory d left join student s on s.d_id=d.id where s.id is null;
			4.统计每个宿舍住了多少人(有问题)
				方法一：直接去统计字段 s.id就ok了
					select d.num,count(s.id) from dormitory d left join student s on s.d_id=d.id group by d.num;
					select d.num,count(s.id) from dormitory d left join student s on s.d_id=d.id group by d.num;

				方法2：
					1.先查询学员按照 外键宿舍id号进行分组 得到空宿舍
						mysql> select d_id,count(*)  from student group by d_id;
							
							+------+----------+
							| d_id | count(*) |
							+------+----------+
							| NULL |        1 |
							|    1 |        3 |
							|    2 |        2 |
							|    3 |        2 |
							+------+----------+
							4 rows in set (0.00 sec)
					2.把返回的结果 和宿舍进行关联  返回时判断d_id如果为空就输出 0，不为空就输出统计的值
						select d.id,d.num,if(news.d_id is null,0,news.num) from 
							dormitory d
						left join 
							(select d_id,count(*) num  from student group by d_id) as news
						on
							news.d_id=d.id;

						
							+----+------+----------------------------------+
							| id | num  | if(news.d_id is null,0,news.num) |
							+----+------+----------------------------------+
							|  1 |  208 |                                3 |
							|  2 |  209 |                                2 |
							|  3 |  520 |                                2 |
							|  4 |  911 |                                0 |
							+----+------+----------------------------------

		right join  外右链接
			以右表为准，将右表中所有的数据查询出来，去左表匹配信息
			1.查看每个学生的宿舍信息
			select * from dormitory d right join student s on d.id=s.d_id;