有意思的 sql训练

sql

sql训练一

在使用mysql的过程中，sql是基本功，在写sql的时候，一定要提高自己的速度；

sql 训练一 ：海康笔试题

表结构如下：
student (sid, sname, age, gender) : 学生表 （学生id, 学生名，年龄，性别）
teacher (tid, tname): 教师表 (教师id, 教师名)
course (cid, cname, tid) : 课堂信息表（课程id, 课程名，教师id）
student_course_score (sid, cid, score): 学生课程成绩表（学生id, 课程id, 成绩）
1） 查询”A“课程比”B“课程成绩高的所有学生的学号
2） 查询名字中含有”王“的老师个数
3） 查询平均成绩大于90分的学生sid和平均成绩
4） 查询各科最高分和最低分

---

1）
测试如下：先创建一个学生课程成绩表；

我们，先不用查询A 课程比B课程成绩高的所有学生的学号，先使用1 和 2 来进行表示；
那么，找出cid 1 比 cid 2 高的同学的学号，怎么找？
这个时候，就需要先找到cid = 1同学的信息，再找到cid = 2同学的信息；

# 先找到cid = 1课程的同学
select sid, cid, score FROM student_course_score where cid = 1;
# 再找到cid = 2课程的同学
select sid, cid, score FROM student_course_score where cid = 2;

这里就需要使用联结了；
为什么要使用联结？
数据存储在多个表中，想要用单条select语句检索出数据，就要使用联结。注意：联结两个表时，实际上是将第一个表中的每一行与第二个表中的每一行配对。where子句作为过滤条件，如果，没有where子句，第一个表中的每行将第二个表中的每个行配对，而不管它们逻辑是否可以匹配在一起。由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

然后，以上这两种结果需要满足下面两个条件 1. sid 要同一个 2. 1.score > 2.score；

# 把它们分成两张表，注意，需要使用这两张表来作为查询条件
select a.sid as Asid, a.cid as Acid, a.score as Ascore, b.sid as Bsid, b.cid as Bcid, b.score as Bscore from (select sid, cid, score FROM student_course_score where cid = 1) as a, 
(select sid, cid, score FROM student_course_score where cid = 2) as b where a.sid = b.sid and a.score > b.score;

这时候，就满足查询条件了；
运行结果如下：

这时候，已经解决了在一张表上查询的问题，那么，这里是需要结合课程信息表的（course），是需要使用A 而不是使用1来进行代替的；其实就是加入course 的一个字段而已
先查出两张表的状态
先展示一下course的状态

# 这个时候，要求是查询"A"课程比"B"课程成绩高的所有学生的学号，所以，需要使用上课程信息表 根据课程id相等来建立联系
select  scs.sid,  scs.score, course.cname From student_course_score as scs, course where course.cname = 'A' and course.cid = scs.cid;

select  scs.sid,  scs.score, course.cname From student_course_score as scs, course where course.cname = 'B' and course.cid = scs.cid;

这个时候，就使用A来进行查询了；然后，再将这两张表进行结合即可；

select a.sid from (select  scs.sid,  scs.score, course.cname From student_course_score as scs, course where course.cname = 'A' and course.cid = scs.cid) as a, 
(select  scs.sid,  scs.score, course.cname From student_course_score as scs, course where course.cname = 'B' and course.cid = scs.cid) as b where a.sid = b.sid and a.score > b.score;

2）查询名字中含有”王“的老师个数

select count(*) from teacher WHERE tname like "王%"

3） 查询平均成绩大于90分的学生sid和平均成绩
select a.sid, (a.score + b.score) / 2 as c from (select sid, score FROM student_course_score where cid = ‘2’) as a , (select sid, score FROM student_course_score where cid = ‘1’) as b WHERE a.sid = b.sid and a.score + b.score > 180;

注意： 这里，课程id = 1的查出来当作一张表 a， 课程id = 2也查出来当作一张表 b 当a.sid = b.sid时，再把符合条件的查找出来；这种叫做内部联结（基于两个表之间的相等测试）；
那么，内部联结 可是使用where 也可以inner join 来进行实现，那么，使用那个来实现比较好呢？
推荐 使用inner join 来代替 where

select a.sid, (a.score + b.score) / 2 as c from (select sid, score FROM student_course_score where cid = '2') as a  inner join (select sid, score FROM student_course_score where cid = '1') as b on a.sid = b.sid and a.score + b.score  > 180;

select score from student_course_score WHERE cid = '1' order by score limit 1 ; # 默认从小到大
select score from student_course_score WHERE cid = '1' order by score desc limit 1; # 从大到小进行排列

sql训练二

一般像我们的安全管理框架，一般至少是有3张表的；权限表 用户表 用户权限对应表

role(id, name) 权限表（权限id, 用户名）
user(id, password, username) 用户表（用户id, 用户密码，用户名）
user_role(user_id, role_id) 用户权限对应表（用户id, 权限id）

现在，需要传入用户名，把用户找出来，并且需要包含权限
那么，先来一个内部联结的；

SELECT u.username, u.password, r.`name`  
from user as u, role as r, user_role ur 
where u.id = ur.user_id and ur.role_id = r.id;

当然，也可以使用left join right join

# 混合使用
select u.username, u.password, r.`name` FROM user as u left join user_role as ur on u.id = ur.user_id , role as r where ur.role_id = r.id; 
# left join 以及 right join使用
select u.username, u.password, r.`name` FROM user as u left join user_role as ur on u.id = ur.user_id right JOIN role as r on ur.role_id = r.id;

结果如下：

注意，这里是使用sql把数据先查出来，但是，还需要把数据进行封装，封装就需要写mapper 这里，你看username 是 lgb3有两条 很显然，要给他封装成一条，把权限用list来进行保存，其他的数据是一样的；
mapper 配置如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.demo.dao.UserDao" >

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.example.demo.User">
        <id column="id" property="id" />
        <result column="username" property="username" />
        <result column="password" property="password" />
        <collection property="authorities" javaType="ArrayList" column="id" ofType="com.example.demo.Role" >
            <id column="perId" property="id"></id>
            <result column="perName" property="name"></result>
        </collection>
    </resultMap>

    <select id="findByUsername" resultType="com.example.demo.User" resultMap="BaseResultMap" parameterType="String">
        select r.id, r.username, r.`password`, p.`name` as perName, p.id as perId from user r
            left join user_role as ur on r.id=ur.user_id
            right join role as p on p.id = ur.role_id where r.username = #{username};
    </select>

 </mapper>

解释一下：Mybatis把查找出来的数据封装成为对象；像username password这种的话，正常封装就好了；但是authorities 对应的是一个list 在这个list中存的是Role 这里就需要处理一下了；这里是数据库查出来的perId 对应的是 Role的id 下面sql语句也是有重命名，因为id 产生了冲突；
以上就实现了对sql查询出来的数据进行封装；

补充 idea 连接 mysql8数据库

由于，在电脑上安装了一个mysql 插件，每个Mapper都会对应dao的一个方法；那么，这个时候，在idea上是需要连接mysql数据库的；

在连接mysql过程中，需要设置serverTimezone

刚刚开始我写utc 即世界时间，发现还是出错；

尝试再次更改，此时更改为“UTC”，测试成功！

这个时候，就不会出现变红这个情况了；