子查询

子查询

：出现在其他语句中的 select语句
： 其他语句:insert,update,delete,select

1、在insert 语句中使用子查询：

INSERT INTO t_student (stu_name, stu_sex, stu_phone)
SELECT
  stu_name,
  stu_sex,
  stu_phone
FROM
  t_student
WHERE stu_score = 85

2、在UPDATE 语句中使用子查询 :

UPDATE
  t_student
SET
  stu_name = '王五'
WHERE stu_num =
  (SELECT
    stu_num
  FROM
    stu_temp
  WHERE stu_num = 102)-- DISTINCT  stu_sex 去除查询出来的重复数据（行） (所有的列都想同)
SELECT DISTINCT  stu_sex FROM t_student

3、在DELETE 语句中使用子查询:

DELETE
FROM
  t_student
WHERE stu_num =
  (SELECT
    stu_num
  FROM
    stu_temp
  WHERE stu_num = 102)

4、按照子查询结果，查询分为:

标量子查询(一行一列)
列子查询(一列多行)
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

-- select 后面只能是标量子查询
 SELECT stu_num,stu_name,(SELECT NOW()) FROM t_student
 
 -- 表子查询 结果为多行多列  查询性别人数大于2的是哪个性别 
 SELECT *
 FROM (SELECT stu_sex,COUNT(*) c FROM t_student GROUP BY stu_sex) AS A
 WHERE A.c > 2
 
 -- 列查询(一列多行的)
SELECT * FROM t_student 
WHERE stu_score IN(SELECT stu_score FROM t_student WHERE stu_score >50)
 
 -- 行子查询（结果集有一行多列） 查询学号最小,成绩最高的学生
 SELECT *
 FROM t_student
 WHERE (stu_num,stu_score)=(SELECT MIN(stu_num),MAX(stu_score) FROM t_student)