数据库

最新推荐文章于 2022-07-28 14:01:04 发布

missing walker

最新推荐文章于 2022-07-28 14:01:04 发布

阅读量953

点赞数

本文链接：https://blog.csdn.net/langdei/article/details/100045522

版权

1.排序操作：

排序：order by 默认升序排序asc 可以省略不写 Desc降序排序

select 字段名 from 表名 order by 排序条件 asc/desc;

例子：
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，参数2 第一个参数代表跳过多少条数据，第二个参数代表获取几条数据
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 8,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;

注意： 在有where,group by,order by,limit的语句中，其中的顺序问题
按照 weher group by order by limit;排序

3.嵌套子查询
将一条查询语句嵌入到另一条查询语句当中去
1.可做为数据源
2.可以作为查询条件
例子：
1.查询年龄大于18岁的男生
可以用where加and：

select * from students where age>18 and gender='1';

也可以用嵌套：其中as newtable为别名，给括号里面表的别名

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

2.查询每个班级的男生和女生的人数获取女生人数为0的班级
先统计每个班级里面男生和女生的人数
select class,gender,count(*) from students group by class,gender;
查询有女生的班级，排除没有女生的班级
select class from students group by class,gender having gender!=‘1’
和上面表中取得的数据做取反，最终得到没有女生的班级
select * from students where class not in (select class from students group by class,gender having gender!=‘1’);
取到女生为0的班级；
select class,count(class) from students where class not in (select class from students group by class,gender having gender!=‘1’) group by class;

另一种更简便的方式：
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.外键字段的类型 必须和要关联的字段类型保持一至

多表查询
1.嵌套查询
2.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.查询毛概都有哪些专业在学

2.连接查询
	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都要学习那些课程
		5.查询毛概都有哪些专业在学

	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;