数据库MYSQL

数据查询部分语句

一、简单查询
1、基础知识
   a：数据定义
   b: 数据查询
   c: 数据更新
   d: 简单查询：查找所有列，指定列，计算的列
          

2、为数据表添加记录，用于数据查询：
 create database xskc default character set utf8 collate utf8_general_ci;
 use xskc;

 create table student(sno char(10),sname varchar(10),ssex char(2),sage tinyint,sdept varchar(20));
 desc student;

 create table course(cno char(4) ,cname varchar(10) ,Cpno char(4),Ccredit tinyint);
 desc  course;

 create table sc(sno char(10),cno char(4) ,Grade float);
 desc  sc;

  insert into student values
  ('201215121', '李勇', '男',20, 'CS '),
  ('201215122', '刘晨', '女',19, 'CS ') ,
  ('201215123', '王敏', '女',18, 'MA ') ,
  ('201215125', '张立', '男',19, 'IS '),
  ('151006101','丁涵松','男',21,'计科'),
  ('151006102','林伟','男',22,'计科'),
  ('151006103','薛红','女',21,'计科'),
  ('151007101','林翔平','男',21,'网络工程'),
  ('151007102','琪琪','女',21,'网络工程'),
  ('151007103','张燕红','女',21,'网络工程'),
  ('161006188','马云','男',20,'计科');
  select *  from student;

  insert into course values
  ('1', '数据库', '5', 4) ,
  ('2', '数学', null,2) ,
  ('3', '信息系统', '1',4) ,
  ('4', '操作系统', '6',3) ,
  ('5', '数据结构', '6',3) ,
  ('6', '数据处理', null,2) ,
  ('7', 'PASCAL语言', '6',4);
  select * from course;

  insert into sc values
   ('201215121','1',92),
   ('201215121','2',85),
   ('201215121','3',88),
   ('201215122','2',70),
   ('201215122','3',80),
   ('151006101','1',98),
   ('151006101','2',88),
   ('151006101','3',90),
   ('151006102','1',88),
   ('151006102','2',98),
   ('151006102','3',89),
   ('151007101','1',90),
   ('151007101','2',92),
   ('151007101','3',88),
   ('151007101','4',98),
   ('151007101','5',87),
   ('151007101','6',90);
  select * from sc;

3.查询所有学生的信息
 select * from student;

4.查询所有学生的学号、姓名、性别
select sno,sname,ssex from student;

5.查询所有学生的学号、姓名、性别和出生年份
select sno,sname,ssex,year(now())-sage csnf from student;

  查询系统的当前日期、当前年份、当前月份？
select now();

6.查询选课的学生学号
select distinct sno from sc;

7.查询所有成绩不及格的学生学号
select distinct sno from sc where grade<60;

8.查询年龄小于18岁的学生姓名，性别
select sname,ssex from student where sage =<18;

9.查询年龄大于等于18岁且小于等于20的学生姓名、性别
select sname,ssex from student where sage between 18 and 20;

10.使用between... and重新写第8题
select sname,ssex from student where sage between 0 and 18;

11.查询姓名是马云、林伟、张燕红的学生学号和姓名
 select sno,sname from student where sname in('马云','林伟','张燕红');

12.查询所有姓张的学生
select * from student where sname like '张%';

13.查询所有姓张的学生，仅限两个字
select * from student where sname like '张_';

14.查询所有学生姓名中包括‘云’
select * from student where sname like '%云%';

15.查询缺考的学生学号
select sno from sc where grade is null;

16.查询选修1号课程的学生学号和成绩，并按成绩的降序排列
select sno,grade from sc where cno='1' order by grade desc;

二、高级查询
1.相关知识
  a:为什么要引入高级查询：
  b:如何实现统计：
  c:常用的聚集函数：
  d:需要分组的情况：
  e：having子句和where子句的不同：
   
2.切换数据库
use

3.统计学生人数
select count(*) from student;

4.统计男生人数
select count(*) from student where ssex='男';

5.统计选课人数
select count(distinct sno) from sc;

6.统计选修1号课程的学生人数
select count(distinct sno) from sc where cno='1';

7.计算1号课程的最低成绩,最高成绩,总成绩和平均成绩
select min(grade),max(grade),sum(grade),avg(grade) from sc where cno='1';

8.查询学号为'201215121'的学生所有课程的最低成绩,最高成绩,总成绩和平均成绩
select min(grade),max(grade),sum(grade),avg(grade) from sc where sno='201215121';

9.查询前3个学生的姓名,性别,出生年份
select sname,ssex,year(now())-sage csnf from student limit 0,3;

10.统计各系的学生人数
select count(sno) from student group by sdept;

11.查询学生人数超过100人的系
select sdept from student group by sdept having count(sno)>100;

12.统计各门课的选课人数
select cno,count(sno) from sc group by cno;

13.查询选课人数不足3人的课程号
select cno from sc group by cno having count(sno)<3;

14.统计每位学生选课的数量
select count(cno) from sc group by sno;

15.查询选修3门以上课程的学生学号
select sno from sc group by sno having count(cno)>3;

三、连接查询
1.相关知识：
  a:为什么要引入连接查询：
     
  b:连接条件：
     
  c:连接的类型：
内连接和外连接（左连接和右连接）和全连接    

  d:内连接的两种写法：
     select 字段 from 表1 inner join 表2 on 表1.字段=表2.字段
  e:外连接：
     左连接：SELECT 字段 FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段;
     右连接：SELECT 字段 FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;
2.切换数据库
use
3.查询自己的选课信息，输出学号、姓名、选修的课程号和成绩
select student.sno,sname,cno,grade from  student,sc where student.sno=sc.sno and student.sno='201215121';

4.查询选修“数据库”的学生信息，输出学号、姓名、选修的课程名称和成绩，
并按成绩从小到大排列（三张表连接）
select student.sno,sname,cname,grade from student,course,sc where student.sno=sc.sno and sc.cno=course.cno and sc.cno='1';

5.查询每位学生的选课情况，输出学号,姓名及他选修的课程号和成绩（外连接）
select student.sno,sname,cno,grade from student left join sc on student.sno=sc.sno;

6.统计选修“数据库”的学生人数
select count(sno) from sc where cno='1';

7.计算“数据库”课程的最低成绩,最高成绩,总成绩和平均成绩
select min(grade),max(grade),sum(grade),avg(grade) from sc where cno='1';

8.查询学号为'201215121'的学生已修的总学分
select sum(ccredit) from sc,course where sno='201215121' and sc.cno=course.cno;

9.查询数据库成绩考试最高的学生学号
select sno from sc,course where sc.cno=course.cno and cname='数据库' order by grade DESC limit 1;

10.统计各门课的选课人数（试着用多种方案）
select cno,count(sno) from sc group by cno;

11.统计各门课的选课人数，输出课程编号,课程名称和选课人数
 select sc.cno,cname,count(sc.sno) from sc left outer join course on(sc.cno=course.cno)group by sc.sno;???????
 
四、子查询

1.相关知识：
  a:为什么要引入子查询？
    只有子查询才能完成(第2、3、5、6、10题)
    更容易书写SQL语句(第4题)
    结构化的体现

  b：不同谓词的区别：
    in/not in 、  = 、 >some、 <some、 >=all、 <=all、 exists/not exists

  c：相关子查询和不相关子查询

  d：同一个查询可以用多种方式实现（连接、in、exists）（第9题）
 
  e：注意，子查询不能使用order by 子句

2.查询选课的学生学号
select sno from student where sno in (select sno from sc);

3,查询没有选课的学生学号
  select sno from student where sno not in (select sno from sc);

4.查询与马云同在一个系学习的学生学号和姓名
select sno,sname from student where sdept in (select sdept from student where sname='马云');

  也可以用自身连接
 select s1.sno,s1.sname from student s1,student s2 where s1.sdept=s2.sdept and s2.sname='马云';

5.检索所有比“马云”年龄大的学生姓名、年龄和性别。
select sname,sage,ssex from student where sage >(select min(sage) from student where sname='马云');

6.检索学生表中年龄最小的学生姓名、年龄和性别。
select sname,sage,ssex from student where sage =(select min(sage) from student);

7.查询数据库成绩最高的学生学号
select sno from sc,course where sc.cno=course.cno and cname='数据库' order by grade DESC limit 1;

8.查询既选修1号课程又选修3号课程的学生学号
  select sno from sc where cno='1' and sno in(select sno from sc where cno='3' );
  select sno from sc where cno='1' INTERSECT select sno from sc where cno='3';  ?????????

9.查询选修数据库的学生学号和成绩（用多种方案实现）
  -- 连接查询
select sno,grade from course,sc where course.cno=sc.cno and cname='数据库';

  -- in谓词引导的不相关子查询
select sno,grade from sc where cno in(select cno from course where cname='数据库');

  -- exists谓词引导的相关子查询
select sno,grade from sc where exists(select * from course where cname='数据库' and sc.cno=course.cno);

10.选修全部课程的学生学号
select sno from student where not exists(select * from course where not exists(select * from sc where sno=student.sno and cno=course.cno));