SQL基础题----基本的SELECT语句、order by排序

本文涉及的产品
实时计算 Flink 版,5000CU*H 3个月
检索分析服务 Elasticsearch 版,2核4GB开发者规格 1个月
智能开放搜索 OpenSearch行业算法版,1GB 20LCU 1个月
简介: SQL基础题----基本的SELECT语句ambiguous 模糊

这是本人的学习过程,看到的同道中人祝福你们心若有所向往,何惧道阻且长;

但愿每一个人都像星星一样安详而从容的,不断沿着既定的目标走完自己的路程,一起做星光下的赶路人;

最后想说一句君子不隐其短,不知则问,不能则学。

所有不走心的努力都是对自已的辜负,所以友友们都要拼尽全力

如果大家觉得我写的还不错的话希望可以收获关注、点赞、收藏(谢谢大家)

1.select基本语句

SQL执行顺序:from、where、select、order
#列的别名只能用在order by中,where中不能使用
select emplotee_id,salary
from employees
where department_id in(50,60,70)
order by department_id desc;
#二级排序
#练习:显示员工信息,按照department_id的降序排序,salary的升序排序
select employ_id,salary,department_id
from employee
order by department_id desc,salary asc;
mysql不支持ISNULL(),因为ISNULL()只有SQL Server才有,解决方法是使用IFNULL()替换ISNULL()

2.select基本语句练习

1.查询员工12个月的工资总和,并起别名为ANNUAL SALARY

use atguigudb
select employee_id,salary*12 "ANNUAL SALARY" FROM employees;
-- 方法二:计算12月的基本工资加奖金
select employee_id,last_name,salary*12*(1+IFNULL(commission_pct,0)) "ANNUAL SALARY" 
FROM employees;

2.查询employees表中去除重复的job_id以后的数据

SELECT DISTINCT job_id
FROM employees;

3.查询工资大于12000的员工姓名和工资

SELECT last_name, salary
FROM employees
WHERE salary > 12000;

4.查询员工号为176的员工的姓名和部门号

SELECT last_name, department_id
FROM employees
WHERE employee_id = 176;

5.显示表 departments 的结构,并查询其中的全部数据

DESC departments;
SELECT * FROM departments;

3.运算符练习

LIKE运算符 LIKE运算符主要用来匹配字符串,通常用于模糊匹配,如果满足条件则返回1,否则返回

0。如果给定的值或者匹配条件为NULL,则返回结果为NULL

LIKE运算符通常使用如下通配符:

“%”:匹配0个或多个字符。
“_”:只能匹配一个字符。

1.选择工资不在500012000的员工的姓名和工资

SELECT last_name, salary
FROM employees
WHERE salary < 5000 OR salary > 12000;
SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;

2.选择在2050号部门工作的员工姓名和部门号

SELECT last_name, department_id
FROM employees
WHERE department_id = 20 OR department_id = 50;
SELECT last_name, department_id
FROM employees
WHERE department_id IN(20, 50)

3.选择公司中没有管理者的员工姓名及job_id

SELECT last_name, job_id
FROM employees
WHERE manager_id IS NULL;

4.选择公司中有奖金的员工姓名,工资和奖金级别


SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

5.选员工姓名的第三个字母是a的员工姓名

SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

6.选择姓名中有字母ak的员工姓名

SELECT last_name
FROM employees
WHERE last_name LIKE '%a%k%' OR last_name LIKE '%k%a%';

7.显示出表 employees 表中 first_name 'e'结尾的员工信息

SELECT employee_id,first_name,last_name
FROM employees
WHERE first_name LIKE '%e';
SELECT employee_id,first_name,last_name
FROM employees
WHERE first_name REGEXP 'e$';

8.显示出表 employees 部门编号在 80-100 之间的姓名、工种

SELECT last_name,job_id
FROM employees
#where department_id in (80,90,100);
WHERE department_id BETWEEN 80 AND 100;

9.显示出表 employees manager_id 100,101,110 的员工姓名、工资、

管理者id

SELECT last_name,salary,manager_id
FROM employees
WHERE manager_id IN (100,101,110);

4.排序与分页

limit 分页
LIMIT [位置偏移量,] 行数
MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”,意思是获取从第5条记录开始后面的3条记录,和“LIMIT
4,3;”返回的结果相同。
#需求1:每页显示20条记录,此时显示第1页
select employee_id,last_name
from employee
limit 0,20;
#需求2:每页显示20条记录,此时显示第2页
select employee_id,last_name
from employee
limit 20,20;
-- 分页显式公式:(当前页数-1)*每页条数,每页条数
-- SELECT * FROM table
-- LIMIT(PageNo - 1)*PageSize,PageSize;
#需求3:表中有107条数据,我们想要显示第32、33条数据怎么办呢?
select employ_id,last_name
from employees
limit 31,2;
#MySQL8.0新特性:LiMIT .. OFFSET ..
select employ_id,last_name
from employees
limit 2 31;

1. 查询员工的姓名和部门号和年薪,按年薪降序 按姓名升序显示

SELECT last_name,department_id,salary * 12 annual_sal
FROM employees
ORDER BY annual_sal DESC,last_name ASC;

2. 选择工资不在 8000 17000 的员工的姓名和工资,按工资降序,显示第

2140位置的数据

SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20,20;

3. 查询邮箱中包含 e 的员工信息,并先按邮箱的字节数降序,再按部门号

升序

SELECT last_name,email,department_id
FROM employees
#where email like '%e%'
WHERE email REGEXP '[e]'
ORDER BY LENGTH(email) DESC,department_id ASC;

5.多表查询

表的别名

使用别名可以简化查询。

列名前使用表名前缀可以提高查询效率。

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;

image.png

image.png

image.png

image.png

image.png

image.png

image.png

内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的
行 ,这种连接称为左(或右) 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
如果是左外连接,则连接条件中左边的表也称为 主表 ,右边的表称为 从表 。
如果是右外连接,则连接条件中右边的表也称为 主表 ,左边的表称为 从表 。

image.png

image.png

image.png

image.png

image.png

image.png

6.UNION的使用

合并查询结果 利用UNION关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集。合并
时,两个表对应的列数和数据类型必须相同,并且相互对应。各个SELECT语句之间使用UNION或UNION
ALL关键字分隔。
UNION 操作符返回两个查询的结果集的并集,去除重复记录
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分,不去重。

7.七SQL JOINS的实现

image.png


#中图:内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;


#左上图:左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;


#右上图:右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;


#左中图:A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL


#右中图:B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL


#左下
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作,效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;


#右下图
#左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ (B - A∩B)
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

SQL99语法新特性

6.1 自然连接

SQL99 SQL92 的基础上提供了一些特殊语法,比如 NATURAL JOIN 用来表示自然连接。我们可以把

自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ,然后进行 等值

连接

SQL92标准中:


SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

SQL99 中你可以写成:

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;


6.2 USING连接

当我们进行连接的时候,SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配

JOIN一起使用。比如:

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

你能看出与自然连接 NATURAL JOIN 不同的是,USING 指定了具体的相同的字段名称,你需要在 USING

的括号 () 中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接。它与下

面的 SQL 查询结果是相同的:

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

注意:

我们要 控制连接表的数量 。多表连接就相当于嵌套 for 循环一样,非常消耗资源,会让 SQL 查询性能下

降得很严重,因此不要连接不必要的表。在许多 DBMS 中,也都会有最大连接表的限制。

表连接的约束条件可以有三种方式:WHERE, ON, USING
WHERE:适用于所有关联查询
ON :只能和JOIN一起使用,只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起
写,但分开写可读性更好。
USING:只能和JOIN一起使用,而且要求两个关联字段在关联表中名称一致,而且只能表示关联字
段值相等

8.多表查询-1

1.显示所有员工的姓名,部门号和部门名称

SELECT last_name, e.department_id, department_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

2.查询90号部门员工的job_id90号部门的location_id

SELECT job_id, location_id
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
AND e.`department_id` = 90;
SELECT job_id, location_id
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` = 90;

3.选择所有有奖金的员工的 last_name , department_name , location_id ,

city

SELECT last_name , department_name , d.location_id , city
FROM employees e
LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT OUTER JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE commission_pct IS NOT NULL;

4.选择cityToronto工作的员工的 last_name , job_id , department_id ,

department_name

SELECT last_name , job_id , e.department_id , department_name
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND city = 'Toronto';
SELECT last_name , job_id , e.department_id , department_name
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON l.`location_id` = d.`location_id`
WHERE l.`city` = 'Toronto';

5.查询员工所在的部门名称、部门地址、姓名、工作、工资,其中员工所

在部门的部门名称为’Executive’

SELECT department_name, street_address, last_name, job_id, salary
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE department_name = 'Executive'

6. 选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果

类似于下面的格式

employees Emp# manager Mgr#
kochhar 101 king 100

SELECT emp.last_name employees, emp.employee_id "Emp#", mgr.last_name manager,
mgr.employee_id "Mgr#"
FROM employees emp
LEFT OUTER JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

7. 查询哪些部门没有员工

#方式1:
SELECT d.department_id
FROM departments d LEFT JOIN employees e
ON e.department_id = d.`department_id`
WHERE e.department_id IS NULL
#方式2:
SELECT department_id
FROM departments d
WHERE NOT EXISTS (
SELECT *
FROM employees e
WHERE e.`department_id` = d.`department_id`
)

8. 查询哪个城市没有部门

SELECT l.location_id,l.city
FROM locations l LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL

9. 查询部门名为 Sales IT 的员工信息

SELECT employee_id,last_name,department_name
FROM employees e,departments d
WHERE e.department_id = d.`department_id`
AND d.`department_name` IN ('Sales','IT');


相关文章
|
3月前
|
SQL Java 数据库连接
SQL SELECT语句的基本用法
SQL SELECT语句的基本用法
|
2月前
|
SQL 数据挖掘 大数据
如何在 SQL Server 中使用 `SELECT TOP`
【8月更文挑战第10天】
104 7
如何在 SQL Server 中使用 `SELECT TOP`
|
1月前
|
SQL XML Java
mybatis :sqlmapconfig.xml配置 ++++Mapper XML 文件(sql/insert/delete/update/select)(增删改查)用法
当然,这些仅是MyBatis功能的初步介绍。MyBatis还提供了高级特性,如动态SQL、类型处理器、插件等,可以进一步提供对数据库交互的强大支持和灵活性。希望上述内容对您理解MyBatis的基本操作有所帮助。在实际使用中,您可能还需要根据具体的业务要求调整和优化SQL语句和配置。
35 1
|
2月前
|
SQL 关系型数据库 MySQL
INSERT INTO t_a.tableName SELECT * FROM t_b.tableName 如何通过定义一个list对象,包含多个tableName,循环执行前面的sql,用MySQL的语法写
【8月更文挑战第7天】INSERT INTO t_a.tableName SELECT * FROM t_b.tableName 如何通过定义一个list对象,包含多个tableName,循环执行前面的sql,用MySQL的语法写
33 5
|
2月前
|
SQL 数据挖掘 关系型数据库
|
2月前
|
SQL
访问者模式问题之构造一个包含 select、from 和 where 子句的 SQL 节点树,如何解决
访问者模式问题之构造一个包含 select、from 和 where 子句的 SQL 节点树,如何解决
|
3月前
|
SQL Oracle 关系型数据库
SQL SELECT TOP 子句
【7月更文挑战第12天】SQL SELECT TOP 子句。
40 14
|
3月前
|
SQL Oracle 关系型数据库
SQL SELECT TOP 子句
【7月更文挑战第13天】SQL SELECT TOP 子句。
24 5
|
3月前
|
SQL 存储 数据库
SQL SELECT 语句
【7月更文挑战第10天】SQL SELECT 语句。
32 9
|
3月前
|
SQL 数据库
SQL SELECT DISTINCT 语句
【7月更文挑战第10天】SQL SELECT DISTINCT 语句。
39 6