Day8 每日打卡 -简单可预期

comAndny

于 2022-09-09 12:54:33 发布

阅读量268

点赞数

文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/qq_45802809/article/details/126780775

版权

sql语法

基本

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-27ASY9np-1662699261473)(${graph}/image-20220706222535820.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PRRgsgp2-1662699261474)(${graph}/image-20220706222550174.png)]

执行顺序

from–where–group by–having–select–order by,

from:需要从哪个数据表检索数据

where:过滤表中数据的条件

group by:如何将上面过滤出的数据分组

having:对上面已经分组的数据进行过滤的条件

select:查看结果集中的哪个列，或列的计算结果

order by :按照什么样的顺序来查看返回的数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HpnqcYoN-1662699261476)(${graph}/image-20220709164946728.png)]

当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序是：

从from中得到数据，返回第1个结果集( 可以直接通过表，或者多表关联(最终形成一张表))

针对第1个结果集执行where xx对全表数据做筛选，返回第2个结果集。

针对第2个结果集使用group by分组，返回第3个结果集。

针对第3个结集执行having xx进行筛选，返回第4个结果集。

针对第4个结果集中的每1组数据执行select xx，有几组就执行几次，返回第5个结果集。

针对第5个结果集排序。

总语法

select col
from 
where 
group by     having
order by
limit

条件表达式

if

select  if(sva=1,'男''女')  ssva
from user

case

# 上面的if语句可转换为
select 
case sva
	when  1  then '男'
	else then  '女'
	end
from user	


# 范围判断
select  
case 
	when age<10       then '年龄小于10'
	else   then '年龄大于等于10'
	end
	as description
from user

distinct

相同值只会出现一次。它作用于所有列，也就是说所有列的值都相同才算相同。

where 过滤

注意null 与空字符串’’ 的区别

null判断： col is null / col is not null

空字符串’’ 判断： col = ‘’ / col != ‘’

操作符	范围	例子
=
<> 或！=
>
<=
>=
BETWENT a AND b	[a,b]	age BETWENT 1 AND 5
not BETWENT a AND b	不在[a,b]之间	age not BETWENT 1 AND 5
in	age in (5,6,7)	年龄为5,6,7
AND 或者 &&	多条件且	age>5 and age <10
OR 或者 \|\|	多条件或	age =5 or age =10
NOT 或者！	非， not +其他表达式，取反	age not BETWENT 1 AND 5

通配符

通配符也是用在过滤语句中，但它只能用于文本字段。

% 匹配 >=0 个任意字符；
_ 匹配 ==1 个任意字符；
[ ] 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。

使用 Like 来进行通配符匹配。

SELECT *
FROM mytable
WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本

计算字段

在数据库服务器上完成数据的转换和格式化的工作往往比客户端上快得多，并且转换和格式化后的数据量更少的话可以减少网络通信量。

计算字段通常需要使用 AS 来取别名，否则输出的时候字段名为计算表达式。

SELECT col1 * col2 AS alias
FROM mytable;

CONCAT() 用于连接两个字段。许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 TRIM() 可以去除首尾空格。

SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM mytable;

函数

汇总

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

AVG() 会忽略 NULL 行。

使用 DISTINCT 可以让汇总函数值汇总不同的值。

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable;

文本处理

函数	说明
LEFT()	左边的字符
RIGHT()	右边的字符
LOWER()	转换为小写字符
UPPER()	转换为大写字符
LTRIM()	去除左边的空格
RTRIM()	去除右边的空格
LENGTH()	长度
SOUNDEX()	转换为语音值
substring_index(target,‘/’,1)	截取

substring_index(target,‘/’,1):

user表：
id   url
1	www/qq/张三
2	wss/qq/李四
3   xxx/qq/王五
4	sss/qq/老六

select substring_index(target,'/',1)     # 从左往右遇到第一个'/'',然后输出'/'左边的字符串
from user
# 获得 www  wss xxx  sss

select substring_index(target,'/',-1)     # 从右往左遇到第一个'/'',然后输出'/'右边的字符串
from user
# 获得 张三 李四 王五 老六

日期和时间处理

日期格式: YYYY-MM-DD 2020-01-06
时间格式: HH:MM:SS 12:05:56

函数	说明
AddDate()	增加一个日期(天、周等)
AddTime()	增加一个时间(时、分等)
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

数值处理

函数	说明
SIN()	正弦
COS()	余弦
TAN()	正切
ABS()	绝对值
SQRT()	平方根
MOD()	余数
EXP()	指数
PI()	圆周率
RAND()	随机数

group by 分组

having 后面接聚合函数

分组就是把具有相同的数据值的行放在同一组中。
可以对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值等。
指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col;

GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;

WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。

SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2;

分组规定:

对于每一个分组，最后都只能输出一行查询数据。除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出，因为不出现的字段不能保证他们的值唯一(而产生多条结果)，这就破坏了分组的意义。

有多少个分组，最终就有多少行输出数据

GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
NULL 的行会单独分为一组；

order 排序

ASC : 升序(默认)
DESC : 降序

可以按多个列进行排序，并且为每个列指定不同的排序方式:

SELECT *
FROM mytable
ORDER BY col1 DESC, col2 ASC;

limit

限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。

返回前 5 行:

SELECT *
FROM mytable
LIMIT 5;

SELECT *
FROM mytable
LIMIT 0, 5;

返回第 3 ~ 5 行:

SELECT *
FROM mytable
LIMIT 2, 3;

复杂查询

子查询

子查询中只能返回一个字段的数据。

可以将子查询的结果作为 WHRER 语句的过滤条件:

SELECT *
FROM mytable1
WHERE col1 IN (SELECT col2
               FROM mytable2);

下面的语句可以检索出客户的订单数量，子查询语句会对第一个查询检索出的每个客户执行一次:

SELECT cust_name, (SELECT COUNT(*)
                   FROM Orders
                   WHERE Orders.cust_id = Customers.cust_id)
                   AS orders_num
FROM Customers
ORDER BY cust_name;

join

介绍

JOIN的含义就如英文单词“join”一样，连接两张表，大致分为内连接，外连接，右连接，左连接，自然连接

笛卡尔积：

不管是值关联一张表，还是多表关联，最终from 后面都只接一张表

当只有一张表时，就是原表

当有多表时，就是多表在特定条件下的笛卡尔积

要理解各种JOIN首先要理解笛卡尔积。笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录。下面的例子，t_blog有10条记录，t_type有5条记录，所有他们俩的笛卡尔积有50条记录。有五种产生笛卡尔积的方式如下。

	SELECT * FROM t_blog CROSS JOIN t_type;
    SELECT * FROM t_blog INNER JOIN t_type;
    SELECT * FROM t_blog,t_type;
    SELECT * FROM t_blog NATURE JOIN t_type;
    select * from t_blog NATURA join t_type;
    +----+-------+--------+----+------------+
    | id | title | typeId | id | name       |
    +----+-------+--------+----+------------+
    |  1 | aaa   |      1 |  1 | C++        |
    |  1 | aaa   |      1 |  2 | C          |
    |  1 | aaa   |      1 |  3 | Java       |
    |  1 | aaa   |      1 |  4 | C#         |
    |  1 | aaa   |      1 |  5 | Javascript |
    |  2 | bbb   |      2 |  1 | C++        |
    |  2 | bbb   |      2 |  2 | C          |
    |  2 | bbb   |      2 |  3 | Java       |
    |  2 | bbb   |      2 |  4 | C#         |
    |  2 | bbb   |      2 |  5 | Javascript |
    |  3 | ccc   |      3 |  1 | C++        |
    |  3 | ccc   |      3 |  2 | C          |
    |  3 | ccc   |      3 |  3 | Java       |
    |  3 | ccc   |      3 |  4 | C#         |
    |  3 | ccc   |      3 |  5 | Javascript |
    |  4 | ddd   |      4 |  1 | C++        |
    |  4 | ddd   |      4 |  2 | C          |
    |  4 | ddd   |      4 |  3 | Java       |
    |  4 | ddd   |      4 |  4 | C#         |
    |  4 | ddd   |      4 |  5 | Javascript |
    |  5 | eee   |      4 |  1 | C++        |
    |  5 | eee   |      4 |  2 | C          |
    |  5 | eee   |      4 |  3 | Java       |
    |  5 | eee   |      4 |  4 | C#         |
    |  5 | eee   |      4 |  5 | Javascript |
    |  6 | fff   |      3 |  1 | C++        |
    |  6 | fff   |      3 |  2 | C          |
    |  6 | fff   |      3 |  3 | Java       |
    |  6 | fff   |      3 |  4 | C#         |
    |  6 | fff   |      3 |  5 | Javascript |
    |  7 | ggg   |      2 |  1 | C++        |
    |  7 | ggg   |      2 |  2 | C          |
    |  7 | ggg   |      2 |  3 | Java       |
    |  7 | ggg   |      2 |  4 | C#         |
    |  7 | ggg   |      2 |  5 | Javascript |
    |  8 | hhh   |   NULL |  1 | C++        |
    |  8 | hhh   |   NULL |  2 | C          |
    |  8 | hhh   |   NULL |  3 | Java       |
    |  8 | hhh   |   NULL |  4 | C#         |
    |  8 | hhh   |   NULL |  5 | Javascript |
    |  9 | iii   |   NULL |  1 | C++        |
    |  9 | iii   |   NULL |  2 | C          |
    |  9 | iii   |   NULL |  3 | Java       |
    |  9 | iii   |   NULL |  4 | C#         |
    |  9 | iii   |   NULL |  5 | Javascript |
    | 10 | jjj   |   NULL |  1 | C++        |
    | 10 | jjj   |   NULL |  2 | C          |
    | 10 | jjj   |   NULL |  3 | Java       |
    | 10 | jjj   |   NULL |  4 | C#         |
    | 10 | jjj   |   NULL |  5 | Javascript |
    +----+-------+--------+----+------------+

内连接

内连接又称等值连接，使用 INNER JOIN 关键字。

SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;

可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来。

INNER JOIN 可简写为 JOIN

SELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;

自连接

自连接可以看成内连接的一种，只是连接的表是自身而已。

一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。

子查询版本：

SELECT name
FROM employee
WHERE department = (
      SELECT department
      FROM employee
      WHERE name = "Jim");

自连接版本：

SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

外连接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rCI8EoV6-1662699261477)(${graph}/image-20220709155901888.png)]

LEFT JOIN

左连接LEFT JOIN的含义就是求两个表的交集外加左表剩下的数据。依旧从笛卡尔积的角度讲，就是先从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录（见最后三条）。

SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id;
    +----+-------+--------+------+------+
    | id | title | typeId | id   | name |
    +----+-------+--------+------+------+
    |  1 | aaa   |      1 |    1 | C++  |
    |  2 | bbb   |      2 |    2 | C    |
    |  7 | ggg   |      2 |    2 | C    |
    |  3 | ccc   |      3 |    3 | Java |
    |  6 | fff   |      3 |    3 | Java |
    |  4 | ddd   |      4 |    4 | C#   |
    |  5 | eee   |      4 |    4 | C#   |
    |  8 | hhh   |   NULL | NULL | NULL |
    |  9 | iii   |   NULL | NULL | NULL |
    | 10 | jjj   |   NULL | NULL | NULL |
    +----+-------+--------+------+------+

RIGHT JOIN

同理右连接RIGHT JOIN就是求两个表的交集外加右表剩下的数据。再次从笛卡尔积的角度描述，右连接就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上右表中剩余的记录（见最后一条）。

SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id;
    +------+-------+--------+----+------------+
    | id   | title | typeId | id | name       |
    +------+-------+--------+----+------------+
    |    1 | aaa   |      1 |  1 | C++        |
    |    2 | bbb   |      2 |  2 | C          |
    |    3 | ccc   |      3 |  3 | Java       |
    |    4 | ddd   |      4 |  4 | C#         |
    |    5 | eee   |      4 |  4 | C#         |
    |    6 | fff   |      3 |  3 | Java       |
    |    7 | ggg   |      2 |  2 | C          |
    | NULL | NULL  |   NULL |  5 | Javascript |
    +------+-------+--------+----+------------+

OUTER JOIN

外连接就是求两个集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录，最后加上右表中剩余的记录。另外MySQL不支持OUTER JOIN，但是我们可以对左连接和右连接的结果做UNION操作来实现。

	SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id
    UNION  # 默认去重
    SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id;
    +------+-------+--------+------+------------+
    | id   | title | typeId | id   | name       |
    +------+-------+--------+------+------------+
    |    1 | aaa   |      1 |    1 | C++        |
    |    2 | bbb   |      2 |    2 | C          |
    |    7 | ggg   |      2 |    2 | C          |
    |    3 | ccc   |      3 |    3 | Java       |
    |    6 | fff   |      3 |    3 | Java       |
    |    4 | ddd   |      4 |    4 | C#         |
    |    5 | eee   |      4 |    4 | C#         |
    |    8 | hhh   |   NULL | NULL | NULL       |
    |    9 | iii   |   NULL | NULL | NULL       |
    |   10 | jjj   |   NULL | NULL | NULL       |
    | NULL | NULL  |   NULL |    5 | Javascript |
    +------+-------+--------+------+------------+

组合查询

使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。

每个查询必须包含相同的列、表达式和聚集函数。

默认会去除相同行，如果需要保留相同行，使用 UNION ALL。

只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2;

练习

# 范围特定值查询
select device_id,gender,	age,	university,	gpa
from user_profile
where university in ('北京大学','复旦大学','山东大学')

# 判断是否为null
select device_id,gender,age,university
from user_profile
where age is not null

# 特定包含
select device_id,	age,	university
from user_profile
where university like '%北京%'

# 分组
select gender,	university,
count(id) asuser_num,
avg(active_days_within_30) avg_active_day,	
avg(question_cnt) avg_question_cnt
from user_profile
group by gender,university

# 分组再过滤
select university,avg(question_cnt) avg_question_cnt,avg(answer_cnt)	avg_answer_cnt
from user_profile
group by university 
having avg(question_cnt)<5 
or avg(answer_cnt)<20

# 排序
select university,avg(question_cnt)	avg_question_cnt
from user_profile
group by university
order by avg(question_cnt) # 默认为升序

# 子查询
select device_id,question_id,result
from question_practice_detail
where device_id in (
    select device_id from
    user_profile 
    where university='浙江大学'
)

# 去重处理
select university,
count(question_practice_detail.device_id)/
count(distinct  question_practice_detail.device_id)
avg_answer_cnt
from user_profile inner join question_practice_detail
on question_practice_detail.device_id = user_profile.device_id
group by university
order by university

# 多连接
select  university,	difficult_level,
round(count(user_profile.device_id)/
count(distinct user_profile.device_id),4)	avg_answer_cnt
from user_profile inner join question_practice_detail
on user_profile.device_id=question_practice_detail.device_id
inner join question_detail
on question_practice_detail.question_id = question_detail.question_id
group by university ,difficult_level

# 合并结果
# union 结果去重
# union all 结果不去重
select device_id,	gender,	age,	gpa
from user_profile
where university='山东大学'
union all
select device_id,	gender,	age,	gpa
from user_profile
where gender='male'

# if条件分组
select  if(age>=25,'25岁及以上','25岁以下') as  age_cut ,
count(device_id) as number
from user_profile
group by age_cut

# case - when 条件
select device_id,	gender,
case	
    when age<20   then '20岁以下'
    when age >=20 and age<=24   then '20-24岁'
    when age>=25  then '25岁及以上'
    when age is null   then '其他'
    end as age_cut
from user_profile

# 字符串操作
select 
substring_index(profile,',',-1)
as gender,
count(device_id)  number
from user_submit
group by gender

# 第一种 找出每个学校gpa 最低的学生id
# 先找出
select a.device_id ,a.university ,a.gpa
from user_profile a
join (
    # 找出每个学校最低的
    select university,min(gpa) gpa
    from user_profile
    group by university
) as b
# 找到与最低相等的直接返回
on a.university = b.university and a.gpa = b.gpa
order by a.university

练习2

快速构建

package com.al;

import java.util.Random;

/**
 * @author :deng
 * @version :1.0
 * @description :
 * @since :1.8
 */
public class B {
    public static void main(String[] args) {
        int start = 1;
        int end = 5;
        int count = 5;
        Random random = new Random();
        for (int i = start; i <= end; i++) {
            for (int j = 0; j < count; j++) {
                int grade = random.nextInt(100);
                String format = String.format("INSERT INTO s_course(s_id,c_id,grade) VALUES(%d,%d,%d);", i, j + 1, grade);
                System.out.println(format);
            }
            System.out.println();
        }

    }
}

建表语句

# student表
CREATE TABLE student(
	id INT  PRIMARY KEY  AUTO_INCREMENT COMMENT ' id',
	NAME CHAR(20) NOT NULL,
	age INT  NOT NULL
)

BEGIN;
INSERT INTO student(NAME,age) VALUES('王五',12);
INSERT INTO student(NAME,age) VALUES('赵六',18);
INSERT INTO student(NAME,age) VALUES('武七',19);

COMMIT;


# course表
CREATE TABLE course (
	id INT  PRIMARY KEY  AUTO_INCREMENT COMMENT ' id',
	NAME CHAR(20) NOT NULL
)


BEGIN;
INSERT INTO course(NAME) VALUES('java');
INSERT INTO course(NAME) VALUES('高数');
INSERT INTO course(NAME) VALUES('python');
INSERT INTO course(NAME) VALUES('c++');
INSERT INTO course(NAME) VALUES('go');

COMMIT;


# s_course表
CREATE TABLE s_course (
		
	id INT  PRIMARY KEY  AUTO_INCREMENT COMMENT ' id',
	s_id INT NOT NULL,
	c_id INT NOT NULL,
	grade INT  NOT NULL
)

BEGIN;
INSERT INTO course(NAME) VALUES('java');
INSERT INTO course(NAME) VALUES('高数');
INSERT INTO course(NAME) VALUES('python');
INSERT INTO course(NAME) VALUES('c++');
INSERT INTO course(NAME) VALUES('go');

COMMIT;


BEGIN;
INSERT INTO s_course(s_id,c_id,grade) VALUES(1,1,20);
INSERT INTO s_course(s_id,c_id,grade) VALUES(1,2,17);
INSERT INTO s_course(s_id,c_id,grade) VALUES(1,3,6);
INSERT INTO s_course(s_id,c_id,grade) VALUES(1,4,14);
INSERT INTO s_course(s_id,c_id,grade) VALUES(1,5,8);

INSERT INTO s_course(s_id,c_id,grade) VALUES(2,1,0);
INSERT INTO s_course(s_id,c_id,grade) VALUES(2,2,16);
INSERT INTO s_course(s_id,c_id,grade) VALUES(2,3,81);
INSERT INTO s_course(s_id,c_id,grade) VALUES(2,4,82);
INSERT INTO s_course(s_id,c_id,grade) VALUES(2,5,18);

INSERT INTO s_course(s_id,c_id,grade) VALUES(3,1,60);
INSERT INTO s_course(s_id,c_id,grade) VALUES(3,2,19);
INSERT INTO s_course(s_id,c_id,grade) VALUES(3,3,41);
INSERT INTO s_course(s_id,c_id,grade) VALUES(3,4,9);
INSERT INTO s_course(s_id,c_id,grade) VALUES(3,5,19);

INSERT INTO s_course(s_id,c_id,grade) VALUES(4,1,49);
INSERT INTO s_course(s_id,c_id,grade) VALUES(4,2,43);
INSERT INTO s_course(s_id,c_id,grade) VALUES(4,3,25);
INSERT INTO s_course(s_id,c_id,grade) VALUES(4,4,58);
INSERT INTO s_course(s_id,c_id,grade) VALUES(4,5,38);

INSERT INTO s_course(s_id,c_id,grade) VALUES(5,1,51);
INSERT INTO s_course(s_id,c_id,grade) VALUES(5,2,68);
INSERT INTO s_course(s_id,c_id,grade) VALUES(5,3,37);
INSERT INTO s_course(s_id,c_id,grade) VALUES(5,4,62);
INSERT INTO s_course(s_id,c_id,grade) VALUES(5,5,66);


COMMIT;

数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wENfS3oS-1662699261478)(${graph}/image-20220907202453684.png)]

查询练习

# 查询同时选修了 课程号为1 和课程号为2 的学生

WITH 
t1 AS(
	SELECT s_id
	FROM s_course,course
	WHERE s_course.`c_id` = course.id
	AND s_course.`c_id` = 1
)
,
t2 AS(
	SELECT s_id
	FROM s_course,course
	WHERE s_course.`c_id` = course.id
	AND s_course.`c_id` = 2
)
SELECT t1.s_id  FROM t1,t2
WHERE t1.s_id = t2 .s_id 


# 查询 成绩全部合格的学生(方法1)
WITH
t1 AS(   # 每一个学生总科数
	SELECT student.`id` , COUNT(*) num
	FROM student, s_course
	WHERE student.`id` = s_course.`s_id`
	GROUP BY student.`id` 
),
t2 AS(
	# 每一个学生及格的数目
	SELECT student.`id` , COUNT( s_course.`grade`>=60 OR NULL) num 
	FROM student, s_course
	WHERE student.`id` = s_course.`s_id`
	GROUP BY student.`id` 
)
SELECT  t1.id
FROM t1,t2
WHERE t1.id = t2.id
AND t1.num = t2.num





# 查询 成绩全部合格的学生 (方法2)
SELECT * FROM
student
WHERE  NOT EXISTS(  # 存在于不存在
	SELECT * FROM s_course
	WHERE s_course.`s_id` = student.`id`
	AND s_course.`grade` <60
)

 # 查询合格数查过2的
SELECT * FROM
student
WHERE  ( 
	SELECT COUNT(*) FROM s_course
	WHERE s_course.`s_id` = student.`id`
	AND s_course.`grade` >=60
)>=2




# 查询挂科的
SELECT * FROM 
student 
WHERE  EXISTS (
	SELECT * FROM s_course
	WHERE s_course.`s_id` = student.`id`
	AND s_course.`grade` <60
)


# 查询挂科查过2门的(大于等于4)
SELECT * FROM 
student 
WHERE (
	SELECT COUNT(*) FROM s_course
	WHERE s_course.`s_id` = student.`id`
	AND s_course.`grade` <60
) >=4


# 查询java 挂科的
SELECT * FROM 
student 
WHERE  EXISTS (
	SELECT COUNT(*) FROM s_course,course
	WHERE s_course.`s_id` = student.`id`
	AND  s_course.`c_id` = course.`id`
	AND 
)

comAndny

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Day8 每日打卡 -简单可预期

JOIN的含义就如英文单词“join”一样，连接两张表，大致分为内连接，外连接，右连接，左连接，自然连接不管是值关联一张表，还是多表关联，最终from 后面都只接一张表当只有一张表时，就是原表当有多表时，就是多表在特定条件下的笛卡尔积要理解各种JOIN首先要理解笛卡尔积。笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录。
复制链接

扫一扫