-- 查询徐州学生 
  select * from student where city = '徐州'
  -- 逻辑运算符 and or not
  select * from student where not city = '徐州'    -- city != '徐州'
  select * from student where city = '徐州' or city = '无锡'
  
  -- 比较运算符
  -- 徐州 考试分数及格学生 
  select * from student where city = '徐州' and javasescore >= 60 and sex = '女'
  -- 不及格学生
  select * from student where javasescore < 60
  
  select * from users where userName = 'admin' -- 判断用户名是否存在
  select password from users where userName = 'admin'   -- 用户查找密码 - 输入密码比较 
  --select * from users where userName = 'admin' and password = 'admin'
 
  -- 查询商品
  select * from product where kind = '电子' and skind = '手机' and brand = '手机' 
                             and size >= 4.7 and price >= 2000 and price <= 3000 and
                             style = '4G' 
                             
  -- 其它运算符 between..and..   in(100,200,300)
  select * from student where javasescore >= 98 and javasescore <= 100
  select * from student where javasescore between 98 and 100;   -- >=   <= 
  
  select * from student where city = '徐州' or city = '无锡'
  select * from student where city in('徐州', '无锡', '淮北', '南京')  --  or 
  
  -- 算术运算符
    -- S0001学生参与活动  考试成绩 + 20
    select stuNo, name, javasescore + 20 as score from student where stuNo = 'S0001';
    
    select pname, pprice * 0.5 from product;
    select empNo, empName, (salary * 0.2 + salary) as newSalary from employee;
    
  -- 并置运算符 ||   -  String + 
  select '学号: '||stuNo || ' 姓名: ' ||name || ' 城市: ' || city as stuInfo from student;
  
  -- SQL日期默认结构 10-10月-00    2000-10-10
  select * from student where birthdate = '10-10月-00';   -- to_date  to_char

-- 未参与考试学1生 
 select * from student where javasescore is null;
 select * from student where cardNo is not null;

 -- 参与考试学生成绩 从上之下排序 
 select stuno, name, javasescore from student 
  where javasescore is not null and javasescore >= 60 
  order by javasescore, stuno desc;  -- javasescore升序  相同按stuNo降序排序

【ANY】

“比任意一个销售员工资低”==“比最高销售员工资低”；

“比任意一个销售员工资高”==“比最低销售员工资高”；

【ALL】

“比所有销售员工资都低”==“比最低销售员工资低”；

“比所有销售员工资都高”==“比最高销售员工资高”；

    -- rownum 必须从第一条件开始 
    select rownum, stuNo, name, score from student where rownum <= 3;   -- 获取数据库表中前三条数据
    
    -- 成绩前三名 
    select * 
      from (select stuNo, name, score from student order by score desc)     -- 虚拟临时表 内联视图
        where rownum <= 3; 

-- 查询学生及其专业 数据  每个页面显示2条数据 
    select stuNo, name, score, majorName 
      from (select rownum as num, s.stuNo, s.name, s.score, m.name as majorName 
              from student s, major m 
              where s.majorno = m.majorno)
      where num >= 1 and num <= 5;
    -- mysql   select ... limit 1, 5;   
    
    -- 查询学生及其专业 数据 按学生成绩降序排序 每个页面显示2条数据 
    select stuNo, name, score, majorName 
      from (select rownum as num, stuNo, name, score, majorName 
            from (select s.stuNo, s.name, s.score, m.name as majorName 
                    from student s, major m 
                    where s.majorno = m.majorno 
                    order by s.score desc))
      where num >= 4 and num <= 6;

 -- 查看 学校中 每个专业信息 学生数 
 select m.majorno, m.name, count(*), avg(s.javaseScore) as avgScore   -- 单行、多行函数结合  必须使用group by
   from major m, student s 
   where m.majorno = s.majorno 
   group by m.name, m.majorno                       -- select中单行函数必须在group by中出现 
   --order by m.majorno desc                        -- group by中单行函数可不在select中
   having avg(s.javaseScore) < 80                   -- 对分完组数据 条件操作 having  平均分不及格
   order by avgScore 
   --order by avg(s.javaseScore) desc 
 
 -- 学生       项目组   
  -- 查看每组学习情况  平均分降序  
  -- 对平均分不及格 
                                                     
 -- 查看公司每个部门  有几个员工  部门平均工资 
  select d.deptName, d.deptTel, count(*), avg(salary) 
    from department d, employee e 
    where d.departmentCode = e.deparmentCode 
    group by d.deptName, d.deptTel;
    
 -- 京东  哪些种类的商品  销售量较好 
 -- kind      kindNO   kindName
 -- product   proNo    proName    price   info   storeNum   salesNum   kindNo
    select k.kindNo, k.kindName, sum(salesNum) 
      from kind k, product p 
      where k.kindNo = p.kindNo 
      group by k.kindName, k.kindNo 
      order by sum(salesNum) desc;