数据库基础学习4--表格的 增 删 改 查（简单查询与高级查询）
一、增
C：create 增加，创建，向数据库里面添加数据。
insert into Fruit values('K009','苹果',3.0,'高青',90,'')
insert into Fruit(Ids,Name,Price,Source,Numbers) values('K010','苹果',3.0,'高青',90)
二、改
U：update修改，从数据库表里面修改数据。
update Fruit set Source='烟台' where Ids='K001'
三、删
D：delete删除，从数据库中删除数据。
delete from Fruit where Ids='K007'
事务：出现了错误，可以进行回滚
加事务：begin tarn
回滚：rollback
四、查
R：retrieve检索，查询，从数据库里面查询数据。
A、简单查询
一、查询表名和列名
1.查询所有 select * from 表名
2.查指定列 select 列名，列名 from 表名
3.替换列名 select Ids '代号',Name '名称',Price '价格',Source '产地' from Fruit
select Ids AS '代号',Name AS '名称',Price AS '价格',Source AS '产地' from Fruit
4.查指定行
select * from Fruit where Ids='K006'
select * from Fruit where Price=2.4 and Source='烟台'
select * from Fruit where Price between 2.0 and 4.0
select * from Fruit where Numbers in (90,80,70)
select *from 表名
select 列1，列2···from 表名
B、筛选
select top 3* from 表名 查询表的前三行
select top 3 列名 from 表名 where age >22 查询年龄大于22 岁的的前三行
1、等值与不等值
select *from 表名 where 列名=（!= ,>,<,>=,<=）值
2、多条件与范围
select*from 表名 where 条件 1 and或or 条件2 -- 查指定行按条件查
select*from 表名where between····and··· --查指定行按范围查
select*from 表名 where 列 in（值）--查指定行，离散查
select distinct 列名 from表名 去重只能是一列
3、模糊查询
select * from News where title like '%。。。' --查以。。结尾的
select * from News where title like '。。。%' --查以。。开头的
select * from News where title like '%。。。%' --查以包含。。。的
select * from News where title like '%。。。_'--，查。。。之后只有一个字符的
C、排序
select *from 表名 order by 列名 asc/ desc 把一列进行升序或者降序排列
select*from 表名 where age<25 order by age asc ,code desc 把小于年龄25岁的按照升序排列，如果有相同年龄的，再把相同年龄的按照降序排列
D、分组
a、聚合函数
count（），max(), min(),sum(),avg()
select count(*) from 表名 where 列名 统计总行数
select count（列名）from 表名 只统计这一列中的非空值，如果有一格为空，会少统计一行
select min(列名) from 表名 找这一列的最小值
select avg(列名)from 表名 算这一列的平均分
b、group by....having.....
1、group by后面跟的是列名
2、一旦使用group by分组了，则select 和from中间就不能用星号，只能包含两类东西，一类是 group by后面的列名，另一类是统计函数
select 列名,avg(列名) from 表名 group by 列名
having 后面一般跟的统计函数，根据分组后的结果进行进一步筛选
select 列名 from 表名 group by 列名 having count(*)>1 可以把重复的找出来，并且显示有几个相同的
E、高级查询
1、连接查询
select * from 列名,列名 -- 形成笛卡尔积
select * from Info,Nation where Nation.Code=Info.Nation
join on 内连接(列的连接)
select * from Info join Nation on Info.Nation = Nation.Code
eg
--查哪位学生的哪一门课考了多少分？
select student.sname,course.cname,score.degree from student join score on score.sno=student.sno join course on course.cno = score.cno
右连接,右边表必须显示全,如果在左边表没有与之对应的信息,则补空值
select * from Info right join Nation on Info.Nation=Nation.Code
左连接,左边表必须显示全,如果在右边表没有与之对应的信息,则补空值
select * from Info left join Nation on Info.Nation=Nation.Code
全连接，左右两边的表都显示完全
select * from Info full join Nation on Info.Nation=Nation.Code
2、联合查询（行的扩展）
对于查出的两个或多个结构相同的表联合显示
select Code,Name from Info union select Info Code,Name from Family
3、子查询
--子查询的结果当做父查询的条件
select * from Info
--无关子查询，子查询执行是独立的，和父查询是没有关系的（没有用到父查询的东西）
select * from Info where year(Birthday)=(
select YEAR(Birthday) from info where Code='p005')
--相关子查询
select * from teacher
--求计算机系和电子工程系不同职称的老师信息
select * from teacher t1 where depart='计算机系' and not exists(
select * from teacher t2 where depart='电子工程系' and t1.prof = t2.prof)
union
select * from teacher t1 where depart='电子工程系'
and not exists(
select * from teacher t2 where depart='计算机系' and t1.prof = t2.prof
)
--查询除了每门课最高分之外的其他学生信息。
select * from score where degree not in(select MAX(degree) from score group by cno)--错误
select * from score s1 where degree not in(
select MAX(degree) from score s2 group by cno having s1.cno = s2.cno
)
--select * from score where degree not in(86,75)
--分页
select top 5 * from Car -- 前5条数据，第一页
select top 5 * from Car where Code not in(
select top 5 Code from Car
eg
-查找男同志里面年龄最大的全部信息
select *from haha where age=(select MAX(age)from haha where sex='男')and sex='男'
select top 1* from haha where sex='男' order by age desc
) -- 第二页的数据
select top 5 * from Car where Code not in(
select top 10 Code from Car
) --第三页的数据
select top 5 * from Car where Code not in(
select top (5*2) Code from Car
)
select ceiling(COUNT(*)/5) from Car --求总页数
select * from Car where 条件 limit 跳过几条数据，取几条数据 --mysql里面的分页
总结注意:
一、Select 语句的测试顺序：
From 子句
Where子句 （可选）
Group by子句 （可选）
Having子句 （可选）
Select子句
Order by 子句（可选）
Between and 条件包含临界值
二、
自然连接
1、Select * from table1，table2，table3 where table1.主键=table2.外键 and table2.主键=table3.外键 (注意此处也可以用and 连接)
2、注意 * 里面，如果说 两个标的列名不同 则不用table.列名，直接写列名
3、any 只要有一个 all 所有
4、exists 只要能查询到结果就返回 真，结果为空就返回 假。
三、
--相关子查询总结
1、内层查询与外层查询同时进行
2、限定条件 的添加，
3、问题要求都是要求两个条件
a、主要是为了区分 不同同组 （可以分组的情况下）。eg.求各科除最高分以外的成绩。
select *from Score s1 where Degree not in (select MAX(Degree) from Score s2 group by Cno having s1.Cno=s2.Cno)
b、如何通过内层查询，添加的可以限制在外层查询时出现重复的条件。eg.查询和“李军”同性别并同班的同学Sname.
select *from Student s1 where Ssex in (select Ssex from Student s2 where Sname='李军' and s1.Class=s2.Class)
c、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
select Tname,Prof from Teacher t1 where Depart='电子工程系' and not exists(select *from Teacher t2 where Depart='计算机系' and t1.Prof=t2.Prof)
union
select Tname,Prof from Teacher t1 where Depart='计算机系' and not exists(select *from Teacher t2 where Depart='电子工程系' and t1.Prof=t2.Prof)
更新一条数据时操作：
update zb set zb_px=(zb_px+1) where zb_ids='106'--对id=106的数据加1操作
