工作中mysql查询的常见情况【精华贴】【持续更新】【更新完毕】

1.首先执行下面的示例数据库，全量执行脚本即可

/*
SQLyog Professional v12.09 (64 bit)
MySQL - 5.6.40-log : Database - studentsys
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`studentsys` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `studentsys`;

/*Table structure for table `course` */

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (
  `cno` varchar(10) NOT NULL,
  `cname` varchar(20) NOT NULL,
  `credit` int(2) DEFAULT NULL,
  PRIMARY KEY (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `course` */

insert  into `course`(`cno`,`cname`,`credit`) values ('C01','网页基础',1),('C02','数据库系统',2),('C03','计算机基础',3);

/*Table structure for table `dept` */

DROP TABLE IF EXISTS `dept`;

CREATE TABLE `dept` (
  `did` int(11) NOT NULL AUTO_INCREMENT,
  `dname` varchar(20) NOT NULL,
  PRIMARY KEY (`did`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `dept` */

insert  into `dept`(`did`,`dname`) values (1,'计算机系'),(2,'土木工程系'),(3,'英语系');

/*Table structure for table `sc` */

DROP TABLE IF EXISTS `sc`;

CREATE TABLE `sc` (
  `sno` varchar(10) DEFAULT NULL,
  `cno` varchar(10) DEFAULT NULL,
  `degree` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `sc` */

insert  into `sc`(`sno`,`cno`,`degree`) values ('S01','C01',80),('S01','C02',85),('S01','C03',90),('S02','C01',63),('S02','C02',58),('S03','C01',55),('S03','C03',65),('S04','C01',58);

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `sno` varchar(10) NOT NULL,
  `sname` varchar(20) NOT NULL,
  `sex` char(4) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `brithday` datetime DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `did` int(11) DEFAULT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`sno`,`sname`,`sex`,`age`,`brithday`,`address`,`did`) values ('S01','陈宇乐','男',21,'2022-02-02 00:00:00','浙江义乌',1),('S02','陈紫樱','女',20,'2022-02-10 00:00:00','',1),('S03','杜陈宇','男',21,NULL,NULL,1),('S04','陈宇乐','男',23,NULL,NULL,2),('S05','陈樱','女',21,NULL,NULL,2),('S06','杜佳佳','男',19,NULL,NULL,NULL);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

2.各个表的内容：

课程表

科系表

分数表

学生表（主表）

3.具体的查询语句及其注解

#查询所有学生信息
SELECT * FROM student
#查询学生表中的学号与姓名
SELECT sno,sname FROM student 

#查询学生表中的学号与姓名，并分别给两个字段别名
select sno as '学生编号',sname as '姓名' from student

#查询学生表中的姓名信息，并过滤掉相同姓名信息
SELECT DISTINCT 
sname #要过滤的内容
FROM 
student 

#查询学生个数，年龄总和，平均年龄，最大年龄，最小年龄，并给他们一个别名
SELECT
count(*) as '学生个数',
sum(age) as '年龄总和',
avg(age) as '平均年龄',
max(age) as '最大年龄',
min(age) as '最小年龄' 
FROM 
student

#查所有男的，且21岁的
SELECT * FROM student where sex='男' and age = 21

#查询姓陈的同学
SELECT * FROM student where sname like '陈%'
#查询名字中出现'陈'的同学
SELECT * FROM student where sname like '%陈%'

#查询姓陈的二字姓名的同学 _ 表示一个字符匹配
SELECT * FROM student where sname like '陈_'

#查询名字结尾是'樱'的三个字姓名的同学
SELECT * FROM student where sname like '__樱'

#根据学生年龄从大到小进行排序学生信息

SELECT * FROM student order by age desc
#根据学生年龄从小到大进行排序男学生信息

SELECT * FROM student where sex='男' order by age

#第一排序根据学生年龄升序进行排序，第二排序根据'学号’降序排序的同学信息
SELECT * FROM student order by age,sno desc

#按性别进行分组，并分别统计各组的人数
#分组一般和聚合函数一起使用
SELECT sex, count(*) as '人数' FROM student group by sex

#根据性别进行分组，并分别统计各组的同学的平均年龄

select
sex as '性别',
avg(age) as '平均年龄'
FROM student group by sex

#HAVING子句 一般是配合GROUP BY使用
#根据性别进行分组，并统计各组的人数大于3人的分组信息

SELECT sex, count(*) as sexNum FROM student group by sex having sexNum > 3

#MYSQL中 HAVING子句可以单独使用 相当于where


SELECT * FROM student having age = 21

#MYSQL中 HAVING子句也可以和where使用，但是要放在最后

SELECT * FROM student having age = 21 where age = 21 having sex = '男'

#显示学生表信息的前3条

SELECT * FROM student limit 3
SELECT * FROM student limit 1,3
SELECT * FROM student limit 0

#显示学生表信息的2-4条
SELECT * FROM student limit 1,3

#显示年龄第二大和第三大的‘男’学生
SELECT * FROM student where sex='男' order by age desc limit 1,2



#查询  20<age<30 man

SELECT * FROM student where age > 20 and age < 30 and sex = '男'

#区间的另一种写法 BETWEEN 大于等于20岁小于等于23岁

SELECT * FROM student where age between 20 and 23

#查询性别是男，或者年龄大于等于21岁的学生
SELECT * FROM student where sex='男' or age >= 21

#查询地址为null的学生信息
select * from student where address is null;

#查询地址不为null的学生信息
select * from student where address is not null;
#查询年龄不是21岁的学生
select * from student where age != 21
select * from student where age <> 21

#多表连接
#显示拥有系别学生学号，姓名，及所在系名称-[内连接方式]
select s.sno,s.sname,d.dname from student s inner join dept d on s.did = d.did

select s.sno,s.sname,d.dname from student s join dept d on s.did = d.did


select * from dept;
select * from student;
#显示拥有系别学生学号，姓名，及所在系名称-[左/左外连接方式]
select s.sno,s.sname,d.dname from student s left join dept d on s.did = d.did
select s.sno,s.sname,d.dname from student s left join dept d on s.did = d.did

select s.sno,s.sname,d.dname from student s left outer join dept d on s.did = d.did
#左边为主表 主表的都要满足 匹配不上显示null

#右连接
select s.sno,s.sname,d.dname from student s right  join dept d on s.did = d.did
#右边为主表 主表的都要满足 匹配不上显示null

#全连接
select s.sno,s.sname,d.dname from student s left join dept d on s.did = d.did
union
select s.sno,s.sname,d.dname from student s right  join dept d on s.did = d.did

#查询已选课学生姓名，课程名称，课程成绩
select s.sname as 学生姓名 ,d.dname as 课程名称,sc.degree as 课程成绩 from student s
inner join sc on s.sno = sc.sno 
inner join dept d on s.did = d.did

#查询至少选修一门课的女同学姓名，除去重复姓名项

select distinct s.sname from sc 
inner join student s  on s.sno = sc.sno 
where s.sex = '女'


#查询和’陈樱’ 同龄的学生信息 (子查询)
select * from student where age = (
select age from student s where sname = '陈樱'
)

#查询课程成绩不及格的选修课课程信息
select * from course where cno in (
select distinct cno from sc where degree < 60
)


#查询课程成绩及格的选修课课程信息
select * from course where cno not in (
select distinct cno from sc where degree < 60
)

#高效率查询（多表连接）
SELECT c.* FROM sc JOIN course c ON sc.cno = c.cno
WHERE sc.degree<60


#ALL表示必须满足子查询结果的所有记录
#查询sc表里成绩最高的记录
select * from sc where degree >= all(select degree from sc); 

#查询sc表里成绩最低的记录
select * from sc where degree <= all(select degree from sc);


#any表示满足子查询结果的任意一条记录即可，和some一样

#查询选择’C01'课程的成绩高于’C02'的成绩的学生的学号

SELECT * FROM sc WHERE cno='C01' and degree > any (
SELECT degree FROM sc WHERE cno='C02'
)

SELECT * FROM sc WHERE cno='C01' and degree > some (
SELECT degree FROM sc WHERE cno='C02'
)
#EXISTS运算符的含义为"存在",外部查询的 WHERE 子句测试子查询返回的行是否存在
#EXISTS子查询返回结果类型bool,不产生任何数据;它只返回TRUE 或 FALSE 值

#显示已经选修了课程的学生信息
SELECT DISTINCT s.* FROM sc LEFT JOIN student s ON sc.sno =
s.sno #使用频率较高

SELECT * FROM student s WHERE EXISTS
(SELECT *FROM sc WHERE s.sno = sc.sno)
#显示已经未选修了课程的学生信息

SELECT * FROM student s WHERE not EXISTS
(SELECT *FROM sc WHERE s.sno = sc.sno)

#显示已经选修了c03课程的学生信息
SELECT * FROM student s WHERE EXISTS
(SELECT *FROM sc WHERE s.sno = sc.sno and sc.cno='C03')```