最全MySQL8.0实战教程 10 MySQL数据库基本操作 - DQL 10.1 基本查询【10 练习】

素颜马尾好姑娘i 2024-03-26 10:51 52阅读 0赞

#### 最全MySQL8.0实战教程 ####

#### 文章目录 ####

*   *   *  最全MySQL8.0实战教程
         *  10 MySQL数据库基本操作 - DQL
         *   *  10.1 基本查询
             *   *  10.1.19 练习

[【黑马程序员MySQL知识精讲+mysql实战案例\_零基础mysql数据库入门到高级全套教程】 ][MySQL_mysql_mysql_]

#### 10 MySQL数据库基本操作 - DQL ####

##### 10.1 基本查询 #####

###### 10.1.19 练习 ######

create table student(
    	id int,
    	name varchar(20),
    	gender varchar(20),
    	chinese int,
    	english int,
    	math int
    );
    
    insert into student(id,name,gender,chinese,english,math) values(1,'张明','男',89,78,90);
    insert into student(id,name,gender,chinese,english,math) values(2,'李进','男',67,53,95);
    insert into student(id,name,gender,chinese,english,math) values(3,'王五','女',87,78,77);
    insert into student(id,name,gender,chinese,english,math) values(4,'李一','女',88,98,92);
    insert into student(id,name,gender,chinese,english,math) values(5,'李财','男',82,84,67);
    insert into student(id,name,gender,chinese,english,math) values(6,'张宝','男',55,85,45);
    insert into student(id,name,gender,chinese,english,math) values(7,'黄蓉','女',75,65,30);
    insert into student(id,name,gender,chinese,english,math) values(7,'黄蓉','女',75,65,30);

![在这里插入图片描述][ff93c1b37d4f44c7823f37ab3bd66e68.png_pic_center]

-- 查询表中所有学生的信息
    select * from student;

![在这里插入图片描述][07aaec5ad57c4b49a862b69df92b002b.png_pic_center]

-- 查询表中所有学生的姓名和对应的英语成绩
    select name,english from student;

![在这里插入图片描述][faf76058728a41428c9667cd34fe8925.png_pic_center]

-- 过滤表中重复数据
    select distinct * from student;

![在这里插入图片描述][e3fc9b6547804c79ad125f8db9c95a3d.png_pic_center]

-- 统计每个学生的总分
    select name,(chinese + english + math) as total_score from student;

![在这里插入图片描述][aa3845e6ad8d466d82fe29043e66d4ed.png_pic_center]

-- 在所有学生总分数上加10分特长分
    select name,(chinese + english + math + 10) as total_score from student;

![在这里插入图片描述][f1b9f20718af4158945b69d4a0ea7753.png_pic_center]

-- 使用别名表示学生分数
    select name,chinese '语文成绩',english '英语成绩',math '数学成绩' from student;

![在这里插入图片描述][2219df31c27447f5b5cc9b3109f34d40.png_pic_center]

-- 查询英语成绩大于90分的同学
    select * from student where english > 90;

![在这里插入图片描述][ad8436d8f2e44dc295b3bdd0dc4fc9ad.png_pic_center]

-- 查询总分大于200分的所有同学
    select * from student where (chinese + english + math) > 200

![在这里插入图片描述][aebca6e6451a4f65a74d7b06acf1f969.png_pic_center]

也可以明显查出每个人的总分

![在这里插入图片描述][60231fc1139f4ff78a0a6f73a3fb2d1c.png_pic_center]

-- 查询英语分数在80 - 90 之间的同学
    select * from student where english between 80 and 90;

![在这里插入图片描述][af884f62b5a142f9bd7db9686d40b247.png_pic_center]

-- 查询英语分数不在80 - 90 之间的同学
    select * from student where not (english between 80 and 90);

![在这里插入图片描述][cd71d2107c554b2a94b92b26864287f1.png_pic_center]

-- 查询数学分数为89,90,91 的同学
    select * from student where math in(89,90,91);

![在这里插入图片描述][6d6d1edf69244ecd82f1cbb1dd6189ae.png_pic_center]

-- 查询数学分数不为89,90,91 的同学
    select * from student where math not in(89,90,91);

![在这里插入图片描述][43ac450be6bf4c46b7c59a986d5c3ffb.png_pic_center]

-- 查询所有姓李的学生的英语成绩
    select name,english from student where name like '李%';

![在这里插入图片描述][08fadb1251cb45528be0ca36f29c48a7.png_pic_center]

-- 查询数学80分并且语文80分的同学
    select name,math,chinese from student where math = 80 and chinese = 80;

![在这里插入图片描述][f93ad0dec1cf49b09a3135e28bb6c561.png_pic_center]

-- 查询英语80分或总分200的同学
    select * from student where english = 80 or (chinese + english + math) = 200;

![在这里插入图片描述][b56e2c83a24d469abe13c96fc28bf5f6.png_pic_center]

-- 对数学成绩降序排序后输出
    select * from student order by math desc;

![在这里插入图片描述][c4a307236b0f46e480037d5c21ca02d4.png_pic_center]

-- 对总分排序后输出，然后再按从高到低的顺序输出
    select * from student order by (chinese + english + math) desc;

![在这里插入图片描述][d4d274103cf14fb3bf73a2b2fd22d824.png_pic_center]

-- 对姓李的学生总分成绩排序输出
    select * from student where name like '李%' order by (chinese + english + math) desc;

![在这里插入图片描述][706ed1e686e74429bc2b54b8cef07c8c.png_pic_center]

-- 查询男生和女生分别有多少人，并将人数降序排序输出
    select gender,count(*) as total_cnt from student group by gender order by total_cnt desc;

![在这里插入图片描述][7cecb5569b004f48a3ce98d99a36a943.png_pic_center]

use mydb2;
    create table emp(
    	empno int, -- 员工编号
    	ename varchar(50), -- 员工姓名
    	job varchar(50), -- 工作名字
    	mgr int, -- 上级领导编号
    	hiredate date, -- 入职日期
    	sal int, -- 薪资
    	comm int, -- 奖金
    	deptno int -- 部门编号
    );
    
    insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);
    insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
    insert into emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
    insert into emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20);
    insert into emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
    insert into emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,null,30);
    insert into emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,null,10);
    insert into emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,null,20);
    insert into emp values(7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,10);
    insert into emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
    insert into emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,null,20);
    insert into emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,null,30);
    insert into emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,null,20);
    insert into emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,null,10);

-- 按员工编号升序排列不在10号部门工作的员工信息
    select * from emp where deptno != 10 order by empno;

![在这里插入图片描述][b553857e2cc94a0daabf3de89a81ed71.png_pic_center]

-- 查询姓名第二个字母不是'A'且薪资大于1000元的员工信息, 按年薪降序排序
    select * from emp where ename not like '_A%' and sal > 1000 order by (sal * 12 + ifnull(comm,0)) desc;

![在这里插入图片描述][edf2a4aa3e7b4d95a96cab16b766561b.png_pic_center]

-- 求每个部门的平均薪资
    select deptno, avg(sal) from emp group by deptno;

![在这里插入图片描述][27ebcd82bb1242c3866be2a56f24a856.png_pic_center]

-- 求每个部门的最高薪资
    select deptno, max(sal) from emp group by deptno;

![在这里插入图片描述][153126f70f214034a5d5194eed555b87.png_pic_center]

-- 求每个部门每个岗位的最高薪资
    select deptno,job, max(sal) from emp group by deptno,job;

![在这里插入图片描述][dcedd02df2834bfd903a506880d83235.png_pic_center]

-- 求平均薪资大于2000 的部门编号
    select deptno, avg(sal) as avg_sal from emp group by deptno having avg_sal > 2000;

![在这里插入图片描述][dd10d51602fe44db8b45847663d71f21.png_pic_center]

-- 将部门平均薪资大于1500的部门编号列出来，按部门平均薪资降序排序
    select deptno, avg(sal) as avg_sal from emp group by deptno having avg_sal > 1500 order by avg_sal desc;

![在这里插入图片描述][062ca06530a748c9974f2e86a8bff895.png_pic_center]

-- 选择公司中有奖金的员工姓名、薪资
    select * from emp where comm is not null;

![在这里插入图片描述][809d702749f4480385549aec622a239a.png_pic_center]

-- 查询员工最高薪资和最低工资的差距
    select max(sal) - min(sal) as '薪资差距' from emp;

![在这里插入图片描述][e5d39738c16e4794aabeda4159b8a413.png_pic_center]

[MySQL_mysql_mysql_]: https://www.bilibili.com/video/BV1iF411z7Pu
[ff93c1b37d4f44c7823f37ab3bd66e68.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c03d27e229594cb790146ac98179b40a.png
[07aaec5ad57c4b49a862b69df92b002b.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/817b3982f3cc4fb99f2a5457f0592d7b.png
[faf76058728a41428c9667cd34fe8925.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/8f5c0f248ad1485e8c9fd14894003c1a.png
[e3fc9b6547804c79ad125f8db9c95a3d.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/a6ccd2750a6d4419a558921096077275.png
[aa3845e6ad8d466d82fe29043e66d4ed.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/55994b364f314b7ab3eaf80801450cdf.png
[f1b9f20718af4158945b69d4a0ea7753.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/f78e8befc17349ffa51473771c53e30c.png
[2219df31c27447f5b5cc9b3109f34d40.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/2f92739c5d88459bac52f5c0734ff5e9.png
[ad8436d8f2e44dc295b3bdd0dc4fc9ad.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/60e270005aba4e29be77fc75bf070397.png
[aebca6e6451a4f65a74d7b06acf1f969.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/dea4f785fc784f3ba774a3d3479c64da.png
[60231fc1139f4ff78a0a6f73a3fb2d1c.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/50958ffbc4234ef2bf6ecedce536fbe1.png
[af884f62b5a142f9bd7db9686d40b247.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/dc5d7d146b2647c283900419ca6ecd41.png
[cd71d2107c554b2a94b92b26864287f1.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/65fe2a4ed6154bf0b573c6d62d1be0e7.png
[6d6d1edf69244ecd82f1cbb1dd6189ae.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/704b5e6710c747deb1f859f1552e9540.png
[43ac450be6bf4c46b7c59a986d5c3ffb.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/1b41ee2fe6de4523bdcf4b21f63633a5.png
[08fadb1251cb45528be0ca36f29c48a7.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c4356868d42a42cc9319a560ba7e231c.png
[f93ad0dec1cf49b09a3135e28bb6c561.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/4b54896ee9c748228c6a9dc8c81760e0.png
[b56e2c83a24d469abe13c96fc28bf5f6.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/5f241e2a46f24e7d8772c5a9db5fd20e.png
[c4a307236b0f46e480037d5c21ca02d4.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/8449200e6ece41d683c7827e5506ba2a.png
[d4d274103cf14fb3bf73a2b2fd22d824.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/dc0f4b2426ef4989ae15004fc52d8de3.png
[706ed1e686e74429bc2b54b8cef07c8c.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/d2af1a2dffdf456bbdb8ad61c7c1e2c3.png
[7cecb5569b004f48a3ce98d99a36a943.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/408c0bf7d3c24748af0957e55c3eb05e.png
[b553857e2cc94a0daabf3de89a81ed71.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/bb22ecd991df41379a5c0172f80466f3.png
[edf2a4aa3e7b4d95a96cab16b766561b.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/5edec831280e454d8e34110ddac202e0.png
[27ebcd82bb1242c3866be2a56f24a856.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/00ebad49d144468c877ce66fa23487a7.png
[153126f70f214034a5d5194eed555b87.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/b9a9c27c688549b7b3f0ab24fba0974b.png
[dcedd02df2834bfd903a506880d83235.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/ad371f4c95d94e06aa9632d7facc0e94.png
[dd10d51602fe44db8b45847663d71f21.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/64b688ec9a7d4c4fa48b29d59287dd1b.png
[062ca06530a748c9974f2e86a8bff895.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/444305070e5843d090f044bdeca6001b.png
[809d702749f4480385549aec622a239a.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/f486a2580ced4ee09fcd74d2f4c930a1.png
[e5d39738c16e4794aabeda4159b8a413.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/6cbb6f5d122d490c9cf7872f2f6c627c.png