MySQL基础学习

最新推荐文章于 2022-09-28 22:08:41 发布

qq_51478930

最新推荐文章于 2022-09-28 22:08:41 发布

阅读量111

点赞数

分类专栏：笔记文章标签： mysql

本文链接：https://blog.csdn.net/qq_51478930/article/details/125413391

版权

笔记专栏收录该内容

13 篇文章 0 订阅

订阅专栏

一，最基本的SELECT语句

SELECT 字段1，字段2，...FROM表名 SELECT 1+1， 3*2； SELECT　1+1， 3*2 FROM　DUAL； DUAL：伪表

二，多表查询

1，等值连接与非等值连接
2，自连接与非自连接
3，内连接与外连接（类似于交并集）
内连接；inner join
内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表中匹配的行。
sql92连接

select employe_id,department_name
from employees e,department_id d
where e.department_id=d.departmen_id;

sql99连接

select employe_id,department_name
from employees e inner join department_id d
on e.department_id=d.departmen_id;

三个以上就不断的join on即可

select employe_id,department_name,city
from employees e inner join department_id d
on e.department_id=d.departmen_id;
join locations l
on d.location_id=l.location_id;

外连接：outer join
外连接：合并具有同一列的两个以上的表的行，结果集中处理包含一个表与另一个表匹配的行之外。
外连接的分类：左外，右外，满外。
左外连接：两个表在连接过程中除了返回满足连接条件的行意外还返回左表中不满足条件的行。
右外连接：两个表在连接过程中除了返回满足连接条件的行意外还返回右表中不满足条件的行。
sql92语法实现外连接：使用+ --------mysql不支持sql92语法中外连接的写法。

#练习：查询所有员工的last_name,department_name信息
select employe_id,department_name
from employees e left join department_id d
on e.department_id=d.departmen_id;

4,union和union all的使用
合并查询结果：利用union关键字，可以给出多条select语句，并将他们的结果组成单个结果集，合并时，两个表对应的列数和书类型必须相同，并且互相对应。各个select语句之间使用union或union all关键字分割。
语法格式：

select column ... from table1
union [all]
select column ...from table2

union操作符：返回两个查询的结果集的并集，去掉重复记录。
union all 操作符：返回两个查询的结果的并集，对于两个结果的重复部分，不去重。
注意:执行 union all语句时所需的资源比union语句少，如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用union all语句，以提高查询的效率。
5,joins的实现
在这里插入图片描述
a，中图：内连接

select employe_id,department_name
from employees e,department_id d
where e.department_id=d.departmen_id;

b，左上图：左外连接

select employe_id,department_name
from employees e left join department_id d
on e.department_id=d.departmen_id;

c，右上图：右外连接

select employe_id,department_name
from employees e right join department_id d
on e.department_id=d.departmen_id;

d,左中图

select employe_id,department_name
from employees e left join department_id d
on e.department_id=d.departmen_id
where d.department_id is null;

e，右中图

select employe_id,department_name
from employees e right join department_id d
on e.department_id=d.departmen_id;
where d.department_id is null;

f，左下图：满外连接
方式1：左上图 union all 右中图
(要求列一样)

select employe_id,department_name
from employees e left join department_id d
on e.department_id=d.departmen_id
union all
select employe_id,department_name
from employees e right join department_id d
on e.department_id=d.departmen_id;
where d.department_id is null;

方式二：右上图union all右中图

三，sql99新特性

1，自然连接
natural join用来表示自然连接，可理解为sql92的等值连接，它会帮你自动查询两张连接表种所有相同的字段，然后进行等值连接。
sql92中

select employe_id,department_name
from employees e join department_id d
on e.department_id=d.departmen_id
and e.manger_id=d.maneger_id;

sql99中

select employe_id,last_name,department_name
from employees e natural join departments d;

2,using连接(同on)
支持使用using指定数据表里的同字段进行等值连接。但只能配合join使用。

select employe_id,last_name,department_name
from employees e join departments d
using (department_id);

四，函数

**1，基本函数 **
在这里插入图片描述
四舍五入round，截断truncate

SELECT ABS(-123),SIGN(-23)，CELL(32.32),RAND(),ROUND(123.456,-1),TRUNCATE(123.456,1)
FROM DUAL;
答案；123，-1，32，0.9892729,120,123.4

**2，三角函数 **
在这里插入图片描述
RADIANS(x)：将角度化为弧度 30°->值
DEGREES(x)：将弧度化为角度 2pi->360°
3，指数函数

4，进制间的转换

5，字符串函数
表1：

SELECT ASCII('Abcdfsf')，CHAR_LENGTH('hello')，CHAR_LENGTH('我们')，LENGTH('hello')，LENGTH('我们')
FROM DUAL;
65，5，2，5，6

#xxx worked for xxx
SELECT CONCAT(last_name,'worked for', last_name)
from employees emp join employees mgr
where emp.manager_id=mgr.employees_id;

#字符串索引是从1开始的！
SELECT INSERT(‘helloworld’，2，3，‘aaaaa’)，REPLACE('hello'，‘lol’，‘mmm’)
FROM DUAL;
haaaaaoworld , 没有lol所以还是hello。

表2：
在这里插入图片描述
6.1，日期的格式化和解析

6.2，日期前与时间戳的转换

6.3，获取月份，星期，星期数，天数等函数

6.4，日期的操作函数

6.5，时间和秒钟转换的函数
第一组:
6.6，计算日期和时间的函数
di
在这里插入图片描述

SELECT DATE_ADD(NOW(),TNTERVAL 1 YEAR),
 DATE_ADD(NOW(),TNTERVAL -1 YEAR)
 FROM DUAL;

第二组：
在这里插入图片描述
举例：

6.7，日期的格式化与解析
此时我们谈的是日期的显示格式化和解析
格式化；日期—>字符串

SELECT DATA_FORMAT (CURDATE(),'%Y-%M-%D'),DATA_FORMAT(NOW(),'%Y-%M-%D'),TIME_FORMAT(CURTIME(),'%H:%i:%S')
FROM DUAL;

解析：字符串---->日期

SELECT STR_TO_DATE('2022-Novmber-12th 17:20:54'，%Y-%M-%D %h:%i:%s)
FROM DUAL;

7，流程控制函数
在这里插入图片描述
IF(VALUE,VALUE1,VALUE2)

SELECT last_name,salary，(IF (salary >=6000)，'高工资'，'低工资')  "details"
from employees;

SELECT last_name，commission_pic，if(commission_pct IS NOT NULL, commission_pct, 0) "details"
FROM employees;

在这里插入图片描述
**IFNULL(VALUE1,VALUE2): 看作上面的特殊情况 **

SELECT last_name，commission_pic，IFNULL(commission_pct, 0) "details"
FROM employees;

CASE WHEN…THEN…WHEN…THEN…ELSE…END

SELECT last_name, salary, CASE WHEN salary >=15000 THEN  'hzy'
                                                        WHEN salary >=10000 THEN  'why'
                                                        WHEN salary >=8000 THEN    'kli'
                                                        ELSE '草根' END "details"
  FROM employees;

练习1：查询部门号为10，20，30的员工信息，若部门号为10，则打印其员工工资的1.1倍，20号部门，打印其员工工资的1.2倍，30，打印1.3倍，其他部门打印1.4倍。

SELECT last_name, department_id,salary, CASE department_id WHEN 10 THEN salary *1.1
                                                                                                        WHEN 20 THEN salary *1.2
                                                                                                        WHEN 30 THEN salary *1.3
                                                                                                        ELSE salary * 1.4
  FROM empoyees;

练习2：查询部门号为10，20，30的员工信息，若部门号为10，则打印其员工工资的1.1倍，20号部门，打印其员工工资的1.2倍，30，打印1.3倍.

SELECT last_name, department_id,salary, CASE department_id WHEN 10 THEN salary *1.1
                                                                                                        WHEN 20 THEN salary *1.2
                                                                                                        WHEN 30 THEN salary *1.3
                                                                                                        END 'details'
  FROM empoyees
  WHERE department_id IN (10, 20, 30);

在这里插入图片描述
8，加密函数

9，mysql信息函数

10，其他函数

练习：

五，聚合函数

1，聚合函数
它是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。
聚合函数作用于一组数据，并对一组数据返回一个值。

在这里插入图片描述

2，GROUP BY用法
需求：查询各个部门的平均工资，最高工资

SELECT department_id, AVG(salary),SUM(salary)
FROM employees
GROUP BY department_id;

需求：查询各个department_id,job_id的平均工资

SELECT department_id,job_id, AVG(salary)
FROM employees
GROUP BY departmrnt_id, job_id;

在这里插入图片描述
结论1：SEMLECT中出现的非组函数的字段必须声明在GROUP BY中。反之，GROUP BY中声明的字段可以不出现在SELECT中。
结论2：GROUP BY声明在FROM后面，WHERE后面，ORDER BY前面，LIMIT后面。
结论3：GROUP BY使用WITH ROLLUP。
注意：当使用ROLLUP时，不能同时使用ORDER BY 子句进行结果排序，即ROLLUP和ORDER BY是互斥的。

SELECT department_id,job_id
FROM employees
GROUP BY department_id WITH ROLLUP;

结果会多一行：即总平均。它不属于任何一个部门，因此在使用ORDER BY师就会矛盾。
3，HAVING的用法
作用：用来过滤数据的
需求：查询各个部门中最高工资比10000高的部门信息

#错误写法：
SELECT departmrnt_id,MAX(salary)
FROM employees
WHERE MAX(salary )>10000
GROUP BY department_id;

结论1：如果过滤条件使用了聚合函数。则必须使用HAVING来替代WHERE，否则报错。
要求1：HAVING必须声明在GROUP BY的后面。
要求2：开发中，使用HAVING前提是sql中使用了GROUP BY。

结论2：当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。
当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE和HAVING都可以。建议声明在WHERE中，因为效率高。

#正确写法：
SELECT departmrnt_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary )>10000;

练习：查询部门id为10，20，30，40这四个部门中最高工资比10000高的部门信息。

#方式1：推荐，比方式2效率高
SELECT departmrnt_id,MAX(salary)
FROM employees
WHERE department_id IN(10,20,30,40)
GROUP BY department_id
HAVING MAX(salary )>10000;
#方式2
SELECT departmrnt_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary )>10000 AND department_id IN(10,20,30,40)

4，SQL底层执行原理
1，SQL的完整结构
在这里插入图片描述
2，SQL的执行过程
FROM ON…->(LEFT/RIGHT JOIN)->WHERE->GROUP BY->SELECT->DISTINCT

练习：

1,查询公司员工工资的最大值，最小值，平均值，总和
select last_name,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
from employees;
2,查询各job_id的员工工资的最大值，最小值，平均值，总和
select job_id, last_name,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
from employees
group by job_id;
3,选择具有各个员工job_id的员工人数
select job_id,count(*)
from employees
group by job_id;
4,查询各个管理者手下员工的最低工资，期中工资不能低于6000，没有管理者的员工不算在内
select manager_id,MIN(salary) 
from employees
where manager_id is not null  #说明员工的管理者不为空
group by MIN(salary)
having MIN(salary)>=6000;
5,查询所有部门的名字，location_id,员工数量和平均工资，并按平均工资降序
select department_name,locatio_id,count9(*),AVG(salary)
6，查询所有部门的名字，location_id,员工数量和平均工资，并按平均工资降序
select d.department_name,d.location_id,count(employee_id),avg(salary)
from departments d left join employees e
on d.department_id=e.employee_id
group by department_name,location_id;

六，子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询，子查询的使用大大增加了select查询能力，因为很多时候查询需要从结果集中获取数据，或者从一个表中先计算一个结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

练习：谁的工资比ABEL高
select last_name,salary
from employees
where salary>(
              select salary
              from employees
              where last_name='Abel'
              );

称谓的规范：外查询，内查询
角度1：从内查询返回的条目数
1,单行子查询
1.1 单行比较操作符
若子查询查出多行，则使用就报错。
在这里插入图片描述

1.2 代码示例
查询与员工141号有相同manager_id和department_id的其他员工的employee_id，manager_id，department_id。

#方式1：
select employee_id,manager_id,deprtment_id
from employees
where manager_id=(
                 select manager_id
                 from employees
                 where employees_id=141
                 )
and  department_id=(
                 select department_id
                 from employees
                 where employees_id=141
                 )
and employee_id<>141;
#方式2：适用范围窄，了解即可
select employee_id,manager_id,deprtment_id
from employees
where （manager_id，department_id)=(
                 select manager_id,departmebt_id
                 from employees
                 where employees_id=141
                 )
and employee_id<>141;

1.3 HAVING中的子查询
在这里插入图片描述
练习：显示员工的employee_id,last_name和location,其中，若员工的department_id与location_id为1800的department_id相同，则location为canada，其余为USA。

select employee_id,last_name,case department_id when (
                             select departmen_id
                             from departments
                             where department_id=1800) then 'canada'
                             else 'usa' 
                             end "location"

1.4，子查询的空值问题
若查询为空，则赋值也为空

2,多行子查询

角度2：内查询是否被执行多次
（1）相关子查询：如查询工资大于本部门平均工资的员工信息。
子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式叫做相关子查询。
（2）不相关子查询：如查询工资大于本公司平均工资的员工信息。
子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的体哦爱按进行执行，那么这样的自查选叫做不想管子查询。
2.1，多行操作符
在这里插入图片描述
2.2，代码示例

题目：找寻所有比每个部门最低工资小的员工姓名和员工id。
IN:
SELECT employee_id,last_name
FROM employees
WHERE salary IN (
                select min(salary)
                from employees
                group by department_id#按 部门id排序
                );

在这里插入图片描述

ANY:
题目：返回其他job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号，姓名，job_id以及salary。 
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (
                 SELECT salary
                 FROM employees
                 WHERE job_id = 'IT_PROG'
                 ) ；
题目：查询平均工资最低部门的id
(mysql中聚合函数是不能嵌套使用的)
方式一：
SELECT department_id
FROM employees
GROUP BY departmen_id
HAVING AVG(salary) =(
					 SELECT MIN(avg_sal)
					 FROM(
					     SELECT AVG(salary) avg_sal
					     FROM employees
					     GROUP BY department_id
					     ) t_dept_avg_sal
					     #找出最低工资
					 )
方式二：
SELECT department_id
FROM employees
GROUP BY departmen_id
HAVING AVG(salary) < =ALL(				
					     SELECT AVG(salary) avg_sal
					     FROM employees
					     GROUP BY department_id
						 )

3,相关子查询
3.1，代码展示

题目：查询员工工资大于本部门平均工资的员工的last_name,salary和其department_id。
使用相关子查询
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary >(
              SELECT AVG(salary)
              FROM employees e2
              WHERE department_id=e1.department_id
              );  
题目：查询员工的id，salary，按照department_name排序
SELECT employee_id,salary
FROM employees e
order by(
        SELECT department_name
        FROM departments d
        WHERE e.employee_id=d.department_id
        )ASC;

在这里插入图片描述

题目:若employees表中employee_id与job_history表中employee_id相同的数目不小于2
输出这些相同id的员工的employee_id,last_name,job_id。
SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2<=(
         SELECT COUNT(*)
         FROM job_history j
         WHERE e.employee_id=j.employee
         )

3.2,exists与 not exsits关键字
在这里插入图片描述
（1） exists 用法

distinct去重！！！
在这里插入图片描述

**（2） not exists **

3.3，相关更新

3.4 相关删除

自连接与子查询：二者都可用时，自连接更好，因为在许多dbms处理过程中，对于自连接的处理速度比咋查询快的多。子查询实际上时通过未知表进行查询后的条件判断，而自连接时通过已知的自身数据进行条件判断，因此大部分dbms中都对自连接进行了优化。

qq_51478930

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础学习

一，最基本的SELECT语句SELECT 字段1，字段2，...FROM表名 SELECT 1+1， 3*2； SELECT　1+1， 3*2 FROM　DUAL； DUAL：伪表二，多表查询1，等值连接与非等值连接2，自连接与非自连接3，内连接与外连接（类似于交并集）内连接；inner join内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表中匹配的行。sql92连接select employe_id,department_namefrom employe
复制链接

扫一扫