首页 > 代码库 > 晚修自习代码---mysql练习代码

晚修自习代码---mysql练习代码

###使用php20170106数据库;
create table php_student(
id int primary key auto_increment,
name varchar(20),
gender enum(male,female),
class_id tinyint unsigned,
age int unsigned,
home varchar(40),
score tinyint unsigned
);
insert into php_student values
(null,孙悟空,male,23,600,花果山,98),
(null,猪悟能,male,23,700,高老庄,88),
(null,沙悟净,male,22,750,流沙河,78),
(null,唐僧,male,21,30,东土大唐,100),
(null,蛤蟆精,male,22,250,流沙河,88),
(null,高翠兰,female,22,18,高老庄,70),
(null,小猴子,male,21,100,花果山,95),
(null,皇帝,male,20,60,东土大唐,93),
(null,高翠华,female,20,16,高老庄,80);
#第26讲 having子句
--having子句和where子句一样,也是用来筛选数据的,通常是对group by之后的统计结果再次加以条件判断进行筛选,以得到想要的结果!
 select home ,max(age) as max_age,avg(score) as avg_score
 from php_student group by home having avg_score>=85;
#第27讲 order by子句
--作用
--根据某个字段进行排序,有升序和降序之分!
order by 字段1 [asc|desc],默认的为asc,也就是升序!

select*from php_student order by score desc;

多字段排序
order by 字段1 [asc|desc],字段2[asc|desc]

select*from php_student order by score desc,age desc;

#第28讲 limit子句
limit就是限制的意思,所以limit子句的作用就是限制查询记录的条数!
语法
limit offset,length
其中,offset是指偏移量,默认为0,也就是从第1条记录开始显示!也就是第1条记录的偏移量是0,而length是需要显示的记录数
思考:
现在想获取第4条到第6条记录,应该怎么写?
select*from php_student limit 3,3;
另外,如果这里的length超出了余下的总记录数,相当于获取了余下的全部的记录!
select *from php_student  limit 3,30;
#分页原理
在项目中,如果想达到分页效果,就应该使用limit子句,比如每页显示10条:
第一页:limit 0,10
第二页:limit 10,10
第三页:limit 20,10
如果用$pageNum代表第多少多少页,用$rowsPerPage代表每页显示的数量!
limti($pageNum-1)*$rowsPerPage,$rowsPerPage
#第29讲 联合查询
关键字:union
select 语句1 union [union 选项] select 语句2 union [union 选项]...
这里的union选项跟以前学习的select选项的可选值是一样的,只是默认值不一样:
all:显示全部记录
distinct:去重,也是默认值!(select选项的默认值是all)
select*from php_student
union all
select*from php_student;
注意:
使用union查询最重要的是所有select语句的字段数是一样的,但是类型可以不一样,而且永远只显示第一个select语句的字段名,但是在实际的运用中,通常各个select语句的字段类型保持一致!
select id,name,gender from php_student
union all
select age,home,score from php_student;
比如:
现在要查询上面php_student表中,高老庄中的成绩score最高的一个,以及花果山中score最低的一个,如何实现?
--先查询高老庄中的成绩score最高一个
select*from php_student where home="高老庄" order by score desc limit 1;
--在去查询花果山中的成绩score最低一个
select*from php_student where home="花果山" order by score asc limit 1;
--联合查询
(select*from php_student where home="高老庄" order by score desc limit 1)
union
(select*from php_student where home="花果山" order by score asc limit 1);
需要注意的地方:
1,联合查询中如果要使用order by子句,那么必须要对各个select语句加上一对括号()
2,如果联合查询中使用了order by子句,还必须搭配上limit子句才能生效!当然,如果想显示全部的数据,常用的做法就是在limit的后面加上一个很多的数,比如:99999

#第30讲 连接查询——交叉连接
连接查询概念与分类

连接查询根据操作的性质不同,又可以分成以下的几类:
1.交叉连接
2.内连接
3.外连接
4.自然连接

交叉连接
select*|字段列表 from 表1 cross join 表2;
关键字:cross join
交叉连接是最容易理解的吗,就是从一张表中的第一条记录开始连接另外一张表中的所有记录,并且保存所有的记录,其中也包括两张表的所有字段,也就是两张表做笛卡尔积!

create table php_student1(
id int primary key auto_increment,
name varchar(20),
gender enum(male,female),
class_id tinyint unsigned,
age int unsigned,
home varchar(40),
score tinyint unsigned
);
create table php_class1(
class_id tinyint unsigned primary key,
class_teacher varchar(20)
);
insert into php_class1 values(21,卡卡西),
(22,自来也),
(23,"大蛇丸"),
(24,纲手);

insert into php_student1 values(1,鸣人,male,22,23,木叶村,89);
insert into php_student1 values(2,佐助,male,21,26,木叶村,99);
insert into php_student1 values(3,小樱,female,21,24,木叶村,79);
insert into php_student1 values(null,,male,23,23,木叶村,79);
insert into php_student1 values(null,,male,23,23,,100);

select*from php_student1 cross join php_class1;

#第31讲 连接查询——内连接
关键字:inner join
内连接要区分左表和右表,出现在join关键字左边的就是左表,反之就是右表!
select*|字段列表 from 左表[inner]  jion 右表 on 左表.字段名=右表.字段名;
其中,上面inner可以省略,默认的就是内连接!
select*from php_student1 as s inner join php_class1  as c
on s.class_id=c.class_id;

select*from php_student as s inner join php_class1 as c
on s.class_id=c.class_id;

几点注意
1,当左表和右表用来判断的字段名是否相同的时候,如果左表的字段名和右表的字段名相同,必须要以 表名.字段名 的形式!
2,如果表名很长,一般都使用别名!
3,如果省略掉了on和后面的连接条件,其实就相当于交叉连接!
select*from php_student as s join php_class1 as c
on s.class_id= c.class_id;

第32讲 连接查询——外连接
外连接又可以分成左外连接和右外连接!
左外连接
关键字:left outer join
含义
同样是拿左表的每一条记录按照on后面的条件去匹配右表,如果匹配成功,那么就保存两个表的所有的记录,如果匹配失败,只保留左表的记录,而右表的记录全部为NULL,此时左表也叫作主表!
select*from php_student as s left join php_class1 as c
on s.class_id=c.class_id;
右外连接
关键字:right outer join
select*from php_student as s right join php_class1 as c
on s.class_id=c.class_id;

#第33讲 连接查询——自然连接
关键字:natural join
--自然内连接
左表 natural join 右表
自然内连接其实和内连接没有什么本质的区别,只是自然内连接中的连接条件由系统自动匹配罢了
--内连接
select*from php_student as s inner join php_class1 as c
on s.class_id=c.class_id;
--自然内连接
select*from php_student as s natural join php_class1 as c;
从结果上看,自然内连接会自动删除字段名相同的一列(只保留一列),并且放在最前面!
自然外连接
也可以分成自然左外连接和自然右外连接:
语法形式:
--自然外连接
左表 natural left|right join 右表;
自然外连接的本质还是在做外连接,只是匹配条件是由系统自动指定罢了!
select *from php_student as s natural left join php_class1 as c;


总结:内连接和(左,右)外连接对应自然连接(内,左右外连接)概念和运行结果一样;交叉连接相互交叉;

#第34讲 子查询——标量子查询
子查询的概念
所以,我们需要换一个思维方式,此时需要把上面的业务逻辑分成两步:
1,先得到最高分的分数值!
select max(score) from php_student;
2,把成绩为最高分的学生信息全部查询出来!
select*from php_student where
score=(select max(score) from php_student);

最基本的要求:子查询语句需要使用一对括号括起来!

子查询的分类
一般的,有两种不同的分类方式:

根据子查询返回值的形式
无外乎也有四种形式:
单一值:返回单一值的子查询就叫作标量子查询!
通常的,我们就是把标量子查询的结果当成一个数值来使用!比如用来做比较、判断或直接参与运算等!
select(select max(score) from php_student)>=95;
一列:返回一列的子查询就叫作列子查询
一行:返回一行的子查询就叫作行子查询
多行多列:返回多行多列的子查询就叫作表子查询!

根据子查询出现的位置
from型:出现在from之后
where型:出现在where之后
exists型:出现在exists之后

#第35讲 子查询——列子查询
返回一列的子查询就叫作列子查询

列子查询的结果往往就是一列相同性质的数据的集合,所以,通常就是配合着in和not in等集合运算符一起来使用!
找出php_class1中所有已经开班了的学生的信息!
分成两个步骤:
1,找出所有已经开班的班级号!
select class_id from php_class1;
2,找出符合条件的学生信息
select*from php_student1
where class_id in(select class_id from php_class1);

#第36讲 子查询——行子查询
查询结果为一行的子查询叫作行子查询!
所谓的构造行元素,就是一个由多个字段组成的元素,形式上就是将多个字段括起来:
现在要查询php_student表中年龄最大而且分数还要最高的记录!
1,先查询最大年龄和最高分
select max(age),max(score) from php_student;
2,查询满足条件的记录
select*from php_student where
(age,score)=(select max(age),max(score) from php_student);

#第37讲 子查询——表子查询
返回结果为多行多列的子查询就是表子查询!
表子查询一般都是用在from之后,当成一个数据源来使用!
--语法形式
select*|字段列表 from 子查询结果 as 别名 where 字句......
特别强调:必须要对子查询的结果起一个别名!

在不使用任何统计函数的前提下,找出php_student表中每个家乡home中分数score最低的一个学生!
1,先对整个表以score字段进行排序
select *from php_student order by score asc;
2,对排序后的表以home字段进行分组
select*from (select*from php_student order by score asc) as s
group by home;

现在要查询出所有家乡的所有的最低分!
1,先查出所有家乡的最低分
select home,min(score) as score from php_student group by home;
2.采用自然连接进行连接查询
select*from php_student natural join
(select home,min(score) as score from php_student group by home) as s;


#第38讲 子查询——exists子查询
exists主要是用来做判断的,判断一个子查询有没有数据返回!返回结果是一个布尔值!
返回0;
select exists(select*from php_student where home="南海");
返回1;
select exists(select*from php_student where home="花果山");

 

晚修自习代码---mysql练习代码