3.2 从关系型数据库中查询数据

筛选数据--插入选课数据

select version（）；

select now();

select sysdate from dual;

select stuname ,stubirth from tb_student stusex=0;

----查询80后学生的信息----两种方法

select stuname ,stusex,stubirth  from tb_student where 

stubirth >='1980-1-1' and stubirth <='1989-12-31'

select stuname ,stusex,stubirth  from tb_student where 

stubirth between '1980-1-1' and '1989-12-31'

---查询姓“林”的学生和性别--模糊查询----

select stuname ,stusex from tb_student where stuname like '林%'；

select stuname ,stusex from tb_student where stuname like '林_ _'；

---查询有“不”的学生和性别--模糊查询----

select stuname ,stusex from tb_student where stuname like '%不%' or stuname like '%嫣%'；

%匹配任意个字符，_ 精确匹配字符个数

---查询没有录入家庭地址的学生---处理空值用is，不能用=

select stuname from tb_student where stuaddr  is null;

select stuname from tb_student where stuaddr  is not  null;

---查询学生选课的所有日期（去重）

select distinct scdate from tb_score;

---查询学生的家庭住址（去重）

select distinct stuaddr from tb_student where stuaddr is not null;

---查询男学生的姓名和生日按照年龄从大到小排列（排序）

select stuname ,stubirth from tb_student where stusex=1 order by stubirth desc

select stuname ,year(now())-year(stubirth) as 年龄 from tb_student where stusex=1 order by 年龄 desc

---查询年龄最大的学生的出生日期（聚合函数）

---查询年龄最小的学生的出生日期（聚合函数）

select max (stubirth) from tb_student

select min (stubirth) from tb_student

---查询男女学生的人数---（分组和聚合函数）

select count(*) from tb_student;

select count(stuid) from tb_student;

select stusex,count(stusex) from tb_student group by stusex;

select stusex ,min(stubirth) from tb_student group by stusex;

----查询课程编号为1111的课程的平均成绩

select avg(scmark) from tb_score where couid=1111;

select min(scmark) from tb_score where couid=1111;

select count(scmark) from tb_score where couid=1111;

----先筛选--在分组--在排序---

----查询学号为1001的学生所有的课程的平均分

select avg(scmark) from tb_score where stuid =1001;

select stuid as 学号，avg(scmark) as 平均分 from tb_score group by stuid

---查询年龄大于等于90分的学生的学号和平均成绩

select stuid as 学号，avg(scmark) as 平均分 from tb_score group by stuid having 平均分>=90

---查询年龄最大的学生的姓名和年龄（子查询+运算）/嵌套查询

select  stuname as 姓名，year(now())-year(stubirth) as 年龄 from tb_student where stubirth =(select min(stubirth) from tb_student) ;

---查询选了两门以上课程的学生的姓名（子查询/分组条件/集合运算）

select stuname from tb_student where stuid in (

select stuid from tb_score group by stuid having count (stuid)>2

) ；

---查询学生姓名

import itertools

for val in itertools.permutations("ABCD",2):

         print(val)

for val in itertools.permutations("ABCD",'1234'):

         print(val)-------笛卡尔积 

内查询---外查询---

分页查询----