day05 关键字补充 以及练习题

day05 关键字补充 以及练习题

昨日内容回顾

• 表关系之一对一

  换位思考之后得出两边都是不可以
  	那么表关系要么是没有关系要么是一对一
      	针对一对一表关系外键字段建在任意一方均可 但是条件建在查询频率较高的表中
          创建外键字段记得加一个unique  唯一不能重复，为了防止他有多个重复的就不是一对一了，而是多对一
  
  """
  外键其实是强耦合 有时候表特别多并不一定会建外键
  而是通过代码层面建立逻辑意义上的关系
  """
  

• 操作表的SQL语句补充

  1.rename关键字
  	rename table old_name to new_name,
      			old_name1 to new_name1,
         			old_name2 to new_name2;
  2.add关键字
  	alter table t1 add 字段名 字段类型 after/first;
  3.drop关键字
  	alter table t1 drop 字段名;
  4.modify与change关键字
  	modify pwd int # 修改字段类型
      change pwd password tinyint #修改字段名 和字段类型
  

• 复制表

  # SQL语句的查询结果其实与也可以看成是一张虚拟表
  create table t1 select * from t2;    #复制表结构 数据  不复制各种键
  create table t1 select * from t2 where 1=2; #复制表结构
  

• 表查询关键字

  select
  from
  where		筛选数据
  		条件(逻辑运算符)
      	成员运算
          模糊查询
          	关键字 like
              关键符号
              	%   #任意字符的任意个数
                  _    #单个个数的任意字符
                  
  group by	分组
  		将单个单个的个体按照指定的条件分成一个个整体
      		分组之后研究的对象应该是整体而不再直接是个体了
          分组之后默认只能直接获取分组的依据无法直接获取其他字段
          	前提要sql_mode要有'only_full_group_by'
              
  having		过滤数据
  		where与having的功能其实是一样的
      		只不过where用于分组之前
          	having用于分组之后
  distinct	去重数据
  		去重数据的前提示数据必须是一模一样的(主键)
  order by	排序
  		默认是升序 	asc可以不写
      	也可以降序    desc
          排序还支持多字段(一个比不出就换一个比)
  

• 聚合函数

  max()         #最大值
  min()		 #最小值
  sum()		#求和
  count()		#计数总共多少个
  avg()		#平均值
  # 上述聚合函数都是在分组之后使用 用于操作整体数据
  

• 补充知识

  1.起别名
  	as语法	
      	给字段和表起别名
  2.group_concat拼接分组之后的字段
  	group_concat(name,'|',salary)
  3.concat拼接分组之前的字段
  	concat(name,'|',salary)
  4.concat_ws多个连接符相同情况下
  	concat_ws('|',name,salary,age)
  

今日内容概要

• 表查询关键字补充

• 多表查询两种方法

  1.子查询
  2.连表操作
  

• 可视化软件

  通过鼠标点点点完成数据库的增删改
  

• 多表查询练习题

今日内容详细

表查询关键字补充

# 1.limit  分页
1.只跟一个数字
	select * from emp limit 5;  # 从头开始展示几行数据
2.只跟两个数字
	select * from emp limit 5,5;  # 第一个是起始位置 第二个是几行数据
    
# 求薪资最高的员工所有数据
	1.先按照薪资降序排序
    2.在使用limit限制取一行
    select * from emp order by salary desc limit 1;
    
# 2.regexp	正则表达式
	select * from emp where name regexp '^j.*(n|y)$';
   
# 3.exists  是否存在
    EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，
    而是返回一个真假值，True或False。
    当返回True时，外层查询语句将进行查询
    当返回值为False时，外层查询语句不进行查询。
    select * from emp
        where exists
        (select id from emp where id > 3);

    select * from emp
        where exists
        (select id from emp where id > 250);

多表查询前准备

# 数据准备
#建表
create table dep(
    id int primary key auto_increment,
    name varchar(20) 
);

create table emp(
    id int primary key auto_increment,
    name varchar(20),
    sex enum('male','female') not null default 'male',
    age int,
    dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('tony','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

多表查询方法之连表操作

# 连表操作
	先将查询涉及到的表拼接成一张大表 之后基于单表查询
 
# 笛卡尔积
	select * from emp,dep;
    select * from emp,dep where dep_id=id;
    select emp.name,dep.name from emp,dep where emp.dep_id=dep.id;
"""
涉及到多表操作的时候 为了避免表字段重复 
	需要在字段名的前面加上表名限制
"""

# 上述操作并不是合理的连表操作
	inner join	内连接:只连接两表中都存在(有对应关系)的数据
    	select * from emp inner join dep on emp.dep_id = dep.id;
    left join	左连接:以左表为基准展示左表所有的数据没有对应则NULL填充
    	select * from emp left join dep on emp.dep_id = dep.id;
    right join	右连接:以右表为基准展示右表所有的数据没有对应则NULL填充
    	select * from emp right join dep on emp.dep_id = dep.id;
    union	    全连接:展示左右两表中所有的数据没有对应则NULL填充
    	select * from emp left join dep on emp.dep_id = dep.id
        union
        select * from emp right join dep on emp.dep_id = dep.id;

多表查询方法之子查询

# 子查询:其实就是分步操作
	将一张表的查询结果当做另外一条SQL语句的查询条件

1.查询部门是技术或者人力资源的员工信息
	1.先查询技术和人力资源的部门编号
    	select id from dep where name in ('技术','人力资源');
    2.根据部门编号去员工表中筛选出对应的员工数据
    	select * from emp where dep_id in (200,201);
    '''子查询:将SQL语句括号括起来即可充当查询条件'''
    select * from emp where dep_id in (select id from dep where name in ('技术','人力资源'));

可视化软件

为了提高开发的效率 但是不能太过于依赖该软件
其实这些软件的底层还是执行的SQL语句来操作数据库的

该软件是收费的 但是有很多破解版本 自我百度下载即可(正式版只能使用14天)

1.链接数据库
2.创建数据库
3.创建表
4.创建数据
5.创建外键
6.逆向数据库到模型
7.转储SQL文件

多表查询练习题

1、查询所有的课程的名称以及对应的任课老师姓名
2、查询平均成绩大于八十分的同学的姓名和平均成绩

# 写多表查询题跟写代码是一样的 先写思路再写sql
# 不要想着一次性写完 写一点查一点再写一点
-- 1、查询所有的课程的名称以及对应的任课老师姓名
# 1.先明确需要用到几张表  老师表与课程表
# 2.查询的结果也必须来源于两种表
# 步骤1 先连表
-- SELECT
-- 	course.cname,
-- 	teacher.tname 
-- FROM
-- 	course
-- 	INNER JOIN teacher ON course.teacher_id = teacher.tid;
	
	
-- 2、查询平均成绩大于八十分的同学的姓名和平均成绩
# 先明确需要用到两张表
# 1.查询平均成绩大于80分的学生id号
# 按照学生id分组 之后求每个学生的平均成绩
-- select student_id,avg(num) from score group by student_id;
# 在使用having过滤出平均成绩大于80分的数据
-- select student_id,avg(num) as avg_num  from score group by student_id
-- HAVING avg(num) > 80
-- ;
# 将上述的表与学生表按照学生id拼接到一起
SELECT
	student.sname,
	t1.avg_num 
FROM
	student
	INNER JOIN ( SELECT student_id, avg( num ) AS avg_num FROM score GROUP BY student_id HAVING avg( num ) > 80 ) AS t1 ON student.sid = t1.student_id;

练习题

1、查询所有的课程的名称以及对应的任课老师姓名
#先判断需要几张表 发现需要课程表 和老师表
#查询结果也需要这两张表 所以多表查询
select course.cname,teacher.tname from course INNER JOIN  teacher ON course.teacher_id=teacher.tid

2、查询学生表中男女生各有多少人
#先判断需要几张表 一张表
#查询结果要几张表 一张表
select gender,COUNT(sid) from student GROUP BY gender

3、查询物理成绩等于100的学生的姓名
#先判断需要几张表 成绩表 学生表  三张表 
#物理成绩等于100 先去成绩表找 发现还需要知道那科是物理 需要科别表 
#分成两步 先找物理成绩等于100的人的id
-- select score.student_id from score INNER JOIN course on score.course_id=course.cid  where course.cname='物理' and score.num=100
#然后通过学生表找这个学生姓名
select sname as '学生姓名' from student where sid in (select score.student_id from score INNER JOIN course on score.course_id=course.cid  where course.cname='物理' and score.num=100)

4、查询平均成绩大于八十分的同学的姓名和平均成绩
#先判断需要几张表  成绩表  学生表  
#先查平均成绩，然后大于80的 学生id
-- select student_id from score  GROUP BY student_id HAVING AVG(num)>80
#再去找学生姓名
SELECT sname from student WHERE student.sid in(select student_id from score  GROUP BY student_id HAVING AVG(num)>80)

5、查询所有学生的学号，姓名，选课数，总成绩
#先判断需要几张表 学生表 课程表两张
#先在成绩表找选课数 总成绩 然后筛出来学生id 
-- select student_id,COUNT(course_id) , SUM(num)  from score GROUP BY student_id 
#在通过学生id 查找 学生姓名
-- select student.sname as '学生姓名',aa.`选课总数`,aa.`总成绩` from  student INNER JOIN (select student_id,COUNT(course_id) as '选课总数', SUM(num)as '总成绩'  from score GROUP BY student_id) as aa  on student.sid=aa.student_id
6、 查询姓李老师的个数
#先判断需要几张表 老师表 一张
--  select COUNT(tid) as '' from teacher WHERE tname like '李%'

7、 查询没有报李平老师课的学生姓名
#先判断需要几张表 需要成绩表 学生表 老师表 
#先需要把李平老师课程id找出来 
-- select course.cid from course INNER JOIN teacher on course.teacher_id=teacher.tid where teacher.tname='李平老师'
#再通过课程id去成绩表找上了李平老师课程对应学生的id
-- select student_id from score where course_id in(select course.cid from course INNER JOIN teacher on course.teacher_id=teacher.tid where teacher.tname='李平老师')  GROUP BY student_id 

#最后通过对应学生的id去学生表找对应学生姓名
-- select sname  from student where sid not in (select student_id from score where course_id in(select course.cid from course INNER JOIN teacher on course.teacher_id=teacher.tid where teacher.tname='李平老师')  GROUP BY student_id )

8、 查询物理课程比生物课程高的学生的学号
-- 
#分别查询物理成绩 生物成绩
-- select student_id,num from score where course_id=(select cid from course where cname='物理')
-- select student_id,num from score where course_id=(select cid from course where cname='生物')
 # 把这俩表 一起查 物理大于生物的人的id拿出来 
-- select a1.student_id from (select student_id,num from score where course_id=(select cid from course where cname='物理')) as a1 INNER JOIN (select student_id,num from score where course_id=(select cid from course where cname='生物')) as a2  on a1.student_id = a2.student_id where a1.num > a2.num
-- 

-- 9、 查询没有同时选修物理课程和体育课程的学生姓名
#先判断需要几张表 课程表和成绩表
#先找到物理课程id 体育课程id
select cid from course where cname='物理'
select cid from course where cname='体育'
#先找到选修物理课程 和 选修体育课程学生的id
select * from  score where course_id=(select cid from course where cname='物理')
select * from  score where course_id=(select cid from course where cname='体育')
#选修了物理和体育的学生
select a1.student_id from (select * from  score where course_id=(select cid from course where cname='物理'))as a1 INNER JOIN  (select * from  score where course_id=(select cid from course where cname='体育')) as a2 on a1.student_id=a2.student_id

#根据id 查找姓名 选修物理或者选修体育，剩下的就是没有选修两门的
select * from student where sid not in (select a1.student_id from (select * from  score where course_id=(select cid from course where cname='物理'))as a1 INNER JOIN  (select * from  score where course_id=(select cid from course where cname='体育')) as a2 on a1.student_id=a2.student_id)

-- 10、查询挂科超过两门(包括两门)的学生姓名和班级 、
#先判断需要几张表   学生表   班级表 成绩表
#1查找挂科超过两门的学生id

select student_id from score where num<=60 group by student_id HAVING COUNT(course_id)>=2 ;

#去学生表找姓名

select student.sname as '学生姓名',class.caption as '班级名称' from student INNER JOIN class on student.class_id=class.cid WHERE sid=(select student_id from score where num<=60 group by student_id HAVING COUNT(course_id)>=2)


-- 11.查询选修了所有课程的学生姓名
#先判断需要几张表 成绩表和学生表

select student_id from score GROUP BY student_id HAVING COUNT(course_id)=4 

select sname from student WHERE sid in (select student_id from score GROUP BY student_id HAVING COUNT(course_id)=4)

-- 12、查询李平老师教的课程的所有成绩记录
#先判断需要几张表 成绩表 课程表对应的 教师表
#先把李平老师教的课程id拿出来
-- select tid from teacher where tname='李平老师'
-- select cid from course where teacher_id=(select tid from teacher where tname='李平老师')

#然后去成绩表找对应成绩
-- select * from score WHERE course_id in (select cid from course where teacher_id=(select tid from teacher where tname='李平老师'))

-- 13、查询全部学生都选修了的课程号和课程名
#判断需要几张表 课程表 成绩表
#先查询学生表总共多少人按照学号（sid）分组 降序排列 取第一个 为多少人
-- select sid from student  GROUP BY sid  ORDER BY sid  desc  LIMIT 1
#去成绩表查询全部学生选修了的课程id   根据课程id分组 分组之后having查询一下里面的count（学号）总共多少个，总共个数和之前学号总人数相等那么这个课程id就是全部人都选择的
-- select course_id from score GROUP BY course_id HAVING COUNT(student_id)=(select sid from student  GROUP BY sid  ORDER BY sid  desc  LIMIT 1)

#拿到课程id去课程表找课程名
-- select cid,cname  from course where cid in (select course_id from score GROUP BY course_id HAVING COUNT(student_id)=(select sid from student  GROUP BY sid  ORDER BY sid  desc  LIMIT 1))
-- 

-- 14、查询每门课程被选修的次数
#先判断需要几张表 课程表 成绩表 
#先查询课程选修的次数和课程id
-- select course_id,COUNT(student_id) from score GROUP BY course_id 
#课程名称加上
select course.cname as '课程名称', a1.a2 as '选修次数' from course INNER JOIN (select course_id,COUNT(student_id)as a2 from score GROUP BY course_id )as a1 on course.cid=a1.course_id


-- 15、查询之选修了一门课程的学生姓名和学号
#需要几张表 学生表 成绩表和课程表
#先查修了一门课程的学生id

select student_id from score GROUP BY student_id HAVING COUNT(course_id)=1

select student.sid as '学号',student.sname as '姓名' from student where sid in (select student_id from score GROUP BY student_id HAVING COUNT(course_id)=1)