实训第四天
实训第四天了，最近脑子里灌的东西比较多，现在终于感到脑子不够用了，脑子疼啊。
今天学的依旧是mysql,学的是查询，查询命令单个简答，两个条件三个条件一组合，哎呀我的脑容量直接感觉不够用了，差点哭晕在厕所
查询条件
(1)简单查询
select * from Info
select Code as '代号',Name as '姓名' from Info as a
(2) 条件查询
Where后面跟条件 条件要写清楚
查询成绩表中成绩（degree）为92的信息
Select * from score where degree =”92”;
查询成绩表中课程号是3-245并且成绩是86的信息
Select * from score where cno='3-245' and degree=”86”
或者用or 并且用and
(3) 模糊查询 like not like
查找老师表中姓李的 名字是两个字老师
select * from teacher
where tName like '%李%'
%代表任意多个字符 _代表一个字符
(4)排序查询 order by 字段 排序值（desc降序/asc升序 ）
select * from student order by class asc
(5)范围查询 关系运算符 between。。。and
select * from Car where Price>=40 and Price<=60
select * from Car where Price between 40 and 50、
(6)离散查询 in not in
select * from student where sname in ('张三','李四')
。。。where sname =“张三” or sname =“李四”
(7)聚合函数，统计查询
select sum(Price) from Car #查询所有价格之和 sum()求和
select count(Code) from Car #查询数据条数
select max(Code) from Car #求最大值
select min(Brand) from Car #求最小值
select avg(Price) from Car #求平均值
(8)分页查询 limit 从第几条开始，取多少条数据
#每页显示5条数据，取第2页的数据
select * from student limit (pageSize-1)*5,5
注：Where 后面不能接limit，表示为空
select * from student limit (n-1)*5,5
select * from student limit 5 只写一个5时，表示默认从第0条开始
(9)去重查询distinct
select distinct cno from score;
(10)分组查询 group by 字段 having 条件
select count(*),cno,group_concat(degree),sum(degree) from score group by cno ;
select cno,group_concat(degree),sum(degree) from score group by cno having count(*)>3
#分组之后根据条件查询使用having 不使用where
根据某个字段分组
Having相当于where
Select count(*),cno,group_concat(sno)
高级查询
- 连接查询，对列的扩展
Select * from student as stu,score as sc
where stu.sno = sc.sno and sc.sno = “103” ;
连接查询Join 表名on条件
Select * from teacher
Join course
On+条件（on teacher.tno = course.tno）
Join score
On(on course.cno = score.cno)
2.联合查询，对行的扩展
select Code,Name from Info
union
select Code,Name from Nation
不相同条件不能连接，必须的一一对应的查询条件
3.子查询
(1)无关子查询
查询老师的信息，根据老师的编号
Select * from teacher where tno =?
查询教计算机的老师的编号
Select tno from course where cname = “计算机导论”;
合并
select * fron teacher where tno =（Select tno from course where cname = “计算机导论”
）;
以上是所学内容，还是需要多练啊
