Mysql查询联系，知识点补充

MySql创建表

• 参考文档https://www.cnblogs.com/wupeiqi/articles/5729934.html

• 补充，查看mysql数据库存放位置

show variables like 'datadir'

• 创建班级表

  -- 注：mysql中UTF8汉字占3个字符，gbk占两个字符
  
  -- 班级表
  create table class(
      cid int not null AUTO_INCREMENT primary key,
      caption char(12) not null
  );
  
  -- 学生表
  create table student(
      sid int not null AUTO_INCREMENT primary key,
      sname varchar(16) not null,
      gender char(3) not null,
      class_id int not null,
      constraint fk_cla_stu foreign key(class_id) REFERENCES class(cid)
  )
  
  -- 教师表
  create table teacher(
  	tid int not null AUTO_INCREMENT primary key,
      tname varchar(16) not null
  )
  -- 课程表
  create table course(
  	cid int not null AUTO_INCREMENT primary key,
      cname varchar(20) not null,
      teacher_id int not null,
      constraint fk_cou_tea foreign key(teacher_id) REFERENCES teacher(tid)
  )
  -- 成绩表
  -- 注成绩表由多个表联合产生
  create table score(
  	sid int not null AUTO_INCREMENT primary key,
      student_id int not null,
      course_id int not null,
      number int not null,
      -- 设置唯一约束
      unique uq1(student_id,course_id),
      constraint fk_sc_stu foreign key(student_id) REFERENCES student(sid),
      constraint fk_sc_cou foreign key(course_id) REFERENCES course(cid),
  )
  

• 插入数据

  -- 插入数据
  -- 班级表
  insert into class(caption)VALUES('一年三班'),('三年二班'),('七年五班')
  -- 学生表
  insert into student(sname,gender,class_id)VALUES('傻狗','公',1),('啊哈','男',1),('二哈','男',2),('王二麻子','男',3)
  -- 教师表
  insert into teacher(tname)VALUES('神仙'),('圣人'),('你觉得呢')
  -- 课程表
  insert into course(cname,teacher_id)VALUES('编译原理',1),('Java高级编程',1),('vue实战',2)
  -- 成绩表
  insert into score(student_id,course_id,number)VALUES(1,2,80),(1,1,90),(2,2,90),(1,3,90),(3,1,85),(3,2,87)
  

navicat

转储sql文件
内部命令
mysqldump
用法:输出到当前目录
不加入-d 输出数据库表结构加数据
mysqldump -u root db1(库)  > db1.sql -p 
不含数据
mysqldump -u root -d db1(库)  > db1.sql -p 

-- 执行文件
mysqldump -u root -d db5 < db.sql -p

查询问题

• 补充：distinct 去重效果，但是效率不高

• 查询java高级编程比编译原理成绩高的所有学生的学号

  -- 查询Java高级编程的成绩比编译原理的成绩高的所有学生的学号
  SELECT student_id from score LEFT join course on score.course_id=course.cid WHERE cname='Java高级编程'
  SELECT student_id from score LEFT join course on score.course_id=course.cid WHERE cname='编译原理'
  -- 合并
  select A.student_id from (SELECT student_id,number from score LEFT join course on score.course_id=course.cid WHERE cname='Java高级编程')as A INNER join (SELECT student_id,number from score LEFT join course on score.course_id=course.cid WHERE cname='编译原理') as B on A.student_id=B.student_id WHERE A.number>B.number
  
  
  -- 查询成绩表中成绩大于85分的数据
  SELECT * from score WHERE number>85
  -- 查询每门老师任课的个数
  select teacher_id,count(cname) from course GROUP BY teacher_id;
  -- 显示老师名称
  select * from course LEFT join teacher on course.teacher_id=teacher.tid;
  -- 查看性别统计
  select gender,count(sid) from student GROUP BY gender;
  -- 临时表,使用括号生成临时表
  (SELECT * from score WHERE number>85) as B
  SELECT number from (SELECT * from score WHERE number>85) as B
  
  -- 3、查询平均成绩大于85分的同学的学号，姓名和平均成绩； 
  SELECT B.student_id,student.sname,B.avgscore from(select student_id,AVG(number)as avgscore from score GROUP BY student_id HAVING avg(number)>85) as B LEFT join student on B.student_id=student.sid
  -- 使用美化SQL 将sql语句进行美化
  SELECT
  	B.student_id,
  	student.sname,
  	B.avgscore 
  FROM
  	( SELECT student_id, AVG( number ) AS avgscore FROM score GROUP BY student_id HAVING avg( number ) > 85 ) AS B
  	LEFT JOIN student ON B.student_id = student.sid
  	
  -- 查询所有同学的学号，姓名，选课数，总成绩
  SELECT student.sname,A.student_id,A.counum,A.sumnum from (SELECT student_id,count(course_id) as counum,sum(number) as sumnum from score left join course on score.course_id=course.cid GROUP BY student_id) as A LEFT join student on student.sid=A.student_id
  -- 查询所有同学的学号，姓名，选课数，总成绩(美化sql)
  SELECT
  	student.sname,
  	A.student_id,
  	A.counum,
  	A.sumnum 
  FROM
  	(
  SELECT
  	student_id,
  	count( course_id ) AS counum,
  	sum( number ) AS sumnum 
  FROM
  	score
  	LEFT JOIN course ON score.course_id = course.cid 
  GROUP BY
  	student_id 
  	) AS A
  	LEFT JOIN student ON student.sid = A.student_id
  -- 	查询姓“神”的老师的个数；
  SELECT count(tid) from teacher WHERE tname like '神%' 
  -- 查询没学过“圣人”老师课的同学的学号、姓名；
  SELECT sid,sname from student WHERE sid not in(SELECT student_id from score WHERE course_id in (select cid from course LEFT join teacher on course.teacher_id=teacher.tid WHERE tname='圣人') GROUP BY student_id) 
  

• 查询练习

  SELECT course_id,avg(number),sum(case when number<87 then 0 else 1 END),sum(1),sum(case when number<87 then 0 else 1 END)/sum(1)as jgl from score GROUP BY course_id ORDER BY AVG(number) asc,jgl desc
  -- 美化
  SELECT
  	course_id,
  	avg( number ),
  	sum( CASE WHEN number < 87 THEN 0 ELSE 1 END ),
  	sum( 1 ),
  	sum( CASE WHEN number < 87 THEN 0 ELSE 1 END ) / sum( 1 ) AS jgl 
  FROM
  	score 
  GROUP BY
  	course_id 
  ORDER BY
  	AVG( number ) ASC,
  	jgl DESC
  	
  -- 知识点：select后面可以跟常数，会直接产生一列
  -- 语法：case when (条件) then 1 else 0 end
  
  -- 20、按照课程平均分从高到低显示任课老师；
  -- 知识点，if的用法,由于连表查询会产生空值，因此使用isnull函数将空值赋值为0，不为空则使用原来的成绩
  SELECT teacher.tname,avg(if(isnull(score.number),0,score.number)) from course left join score on course.cid=score.course_id left join teacher on course.teacher_id=teacher.tid GROUP BY score.course_id
  -- 22、查询每门课程被选修的学生数；--附加，并显示出对应的课程名称；
  SELECT course_id,course.cname,count(1) from score left join course on score.course_id= course.cid GROUP BY course_id 
  -- 10、查询有课程成绩小于90分的同学的学号、姓名；
  -- 知识点，distinct 使用
  select student.sid,sname from student WHERE sid in(SELECT DISTINCT student_id from score WHERE number<90)
  
  -- 6、查询没学过“神仙”老师课的同学的学号、姓名；
  SELECT * from student WHERE sid not in(
  SELECT DISTINCT student_id from score WHERE score.course_id in(select cid from course left join teacher on teacher.tid=course.teacher_id WHERE tname="神仙"))
  -- 法二使用分组去重
  SELECT * from student WHERE sid not in(
  SELECT student_id from score WHERE score.course_id in(select cid from course left join teacher on teacher.tid=course.teacher_id WHERE tname="神仙") GROUP BY student_id)
  -- 21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
  -- 知识点 as 临时表的多重用法
  -- 先去出第一名(单科目)
  select number from score where course_id=1 group by number order by number desc LIMIT 0,1
  -- 第三名
  -- select number from score where course_id=1 group by number order by number desc LIMIT 3,1
  
  -- 第二名
  select number from score where course_id=1 group by number order by number desc LIMIT 1,1
  -- 00
  SELECT * from 
  (SELECT 
  	student_id,
  	course_id,
  	number,
  	1,
  	(select number from score as s2 where s2.course_id=s1.course_id group by number order by number desc LIMIT 0,1),
  	(select number from score as s2 where s2.course_id=s1.course_id group by number order by number desc LIMIT 1,1) as cc
  	from score as s1 ) as B
  	WHERE B.number>B.cc
  -- 34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
  -- 知识点：笛卡尔集
  -- 取出笛卡尔集
  select * from score as s1,score as s2
  -- 去重
  select * from score as s1,score as s2 where s1.course_id!=s2.course_id
  -- 设置分数相等
  select s1.student_id,s2.student_id,s1.course_id,s2.course_id,s1.number,s2.number from score as s1,score as s2 where s1.course_id!=s2.course_id and s1.number=s2.number
  -- 15、删除学习“神仙”老师课的score表记录；
  delete from score where course_id in (SELECT cid FROM course LEFT join teacher on course.teacher_id=teacher.tid WHERE tname='神仙')
  -- 删除1号和2号的成绩
  -- 40、删除1号和2号的成绩
  DELETE from score WHERE student_id=1 or student_id=2
  

• 查询练习中的知识点补充

  DISTINCT 用来做去重的操作，但是一般会使用group by 来实现去重，因为distinct效率较低
  34.笛卡尔集
  
  SELECT course_id,avg(number),sum(case when number<87 then 0 else 1 END),sum(1),sum(case when number<87 then 0 else 1 END)/sum(1)as jgl from score GROUP BY course_id ORDER BY AVG(number) asc,jgl desc
  

  select后跟常数会直接产生一列，因此通常会使用count（1）或者count（主键）进行统计，

  select后也可以跟（查询语句），且查询语句可以使用下面from的表进行条件判断