自己怎样开网站,苏州网站推广找苏州梦易行,wordpress菜单联动,seo服务公司招聘主要内容#xff1a;
常用函数#xff08;函数分类1#xff1a;单行、分组#xff1b;函数分类2#xff1a;字符、数学、日期、流程控制#xff09;、分组查询group by、连接查询 一、常用函数
1. 按使用方式分类
① 单行函数
单行函数#xff08;Scalar Functions
常用函数函数分类1单行、分组函数分类2字符、数学、日期、流程控制、分组查询group by、连接查询 一、常用函数
1. 按使用方式分类
① 单行函数
单行函数Scalar Functions是对单个行进行操作的函数每行返回一个结果。单行函数可以用于 SELECT、WHERE、ORDER BY 等子句中。
示例
字符函数UPPER()、LOWER()、CONCAT()数学函数ABS()、ROUND()、SQRT()日期函数NOW()、DATE_FORMAT()
SELECT UPPER(name) AS upper_name, ABS(salary) AS abs_salary
FROM employees
WHERE DATE_FORMAT(hire_date, %Y-%m) 2023-01;② 分组函数
分组函数Aggregate Functions是对一组行进行操作的函数返回一个汇总结果。分组函数通常与 GROUP BY 子句一起使用。
示例
求和SUM()平均值AVG()最大值MAX()最小值MIN()计数COUNT()
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
2. 按用途分类
① 字符函数
字符函数用于处理字符串数据包括字符串的拼接、截取、转换等操作。
示例
字符串拼接CONCAT()字符串长度LENGTH()字符串截取SUBSTRING()字符串转换UPPER()、LOWER()
SELECT CONCAT(first_name, , last_name) AS full_name, LENGTH(full_name) AS name_length
FROM employees;② 数学函数
数学函数用于处理数值数据包括数值的计算、取整、取绝对值等操作。
示例
绝对值ABS()四舍五入ROUND()平方根SQRT()幂运算POWER()
SELECT ABS(salary) AS abs_salary, ROUND(salary, 2) AS rounded_salary
FROM employees;③ 日期函数
日期函数用于处理日期和时间数据包括日期的格式化、日期差计算、当前日期获取等操作。
示例
当前日期NOW()日期格式化DATE_FORMAT()日期差DATEDIFF()日期加减DATE_ADD()、DATE_SUB()
SELECT NOW() AS current_date, DATE_FORMAT(hire_date, %Y-%m-%d) AS formatted_hire_date
FROM employees;④ 流程控制函数
流程控制函数用于在 SQL 语句中实现条件判断和流程控制类似于编程语言中的条件语句。
示例
条件判断CASE空值处理IFNULL()、COALESCE()条件选择IF()
SELECT employee_id, salary,CASEWHEN salary 10000 THEN HighWHEN salary 5000 THEN MediumELSE LowEND AS salary_level
FROM employees; 二、函数应用
1、字符函数实例 LENGTH(s)、CHAR_LENGTH(s)、CONCAT(s1,s2...)、UPPER(s)、LOWER(s)、SUBSTR(s)、INSTR(s)、TRIM(s) 1LENGTH(str)返回字符串长度以字节为单位
mysql select length(abc);
---------------
| length(abc) |
---------------
| 3 |
---------------
1 row in set (0.00 sec)mysql select length(您好);
------------------
| length(您好) |
------------------
| 6 |
------------------
1 row in set (0.00 sec) 补充一个中文字一般占3-4个字节 例如查询name字段是李平的记录计算其email的字符串长度 mysql select name,email,length(email) from employees where name李平;
---------------------------------------
| name | email | length(email) |
---------------------------------------
| 李平 | lipingtedu.cn | 14 |
---------------------------------------
1 row in set (0.00 sec)
2CHAR_LENGTH(str)返回字符串长度以字符为单位
mysql select char_length(abc);
--------------------
| char_length(abc) |
--------------------
| 3 |
--------------------
1 row in set (0.00 sec)mysql select char_length(您好);
-----------------------
| char_length(您好) |
-----------------------
| 2 |
-----------------------
1 row in set (0.00 sec)
3CONCAT(str1,str2,...)返回连接参数产生的字符串一个或多个待拼接的内容任意一个为NULL则返回值为NULL
mysql select concat(dept_id,-,dept_name) from departments;
-------------------------------
| concat(dept_id,-,dept_name) |
-------------------------------
| 1-人事部 |
| 2-财务部 |
| 3-运维部 |
| 4-开发部 |
| 5-测试部 |
| 6-市场部 |
| 7-销售部 |
| 8-法务部 |
| NULL |
-------------------------------
9 rows in set (0.00 sec)
4UPPER(str)和UCASE(str): 将字符串中的字母全部转换成大写
mysql select upper(hello);
----------------
| upper(hello) |
----------------
| HELLO |
----------------
1 row in set (0.00 sec)mysql select ucase(abc);
--------------
| ucase(abc) |
--------------
| ABC |
--------------
1 row in set (0.00 sec)
例如查询name字段为“李某”将email字段记录转换成大写
mysql select name,upper(email) from employees where name like 李_;
---------------------------
| name | upper(email) |
---------------------------
| 李平 | LIPINGTEDU.CN |
| 李莹 | LIYINGTEDU.CN |
| 李柳 | LILIUTARENA.COM |
| 李慧 | LIHUITARENA.COM |
| 李静 | LIJINGTARENA.COM |
| 李瑞 | LIRUITARENA.COM |
---------------------------
6 rows in set (0.00 sec)
5LOWER(str)和LCASE(str)将str中的字母全部转换成小写
mysql select lower(HELLO);
----------------
| lower(HELLO) |
----------------
| hello |
----------------
1 row in set (0.00 sec)mysql select lcase(ABC);
--------------
| lcase(ABC) |
--------------
| abc |
--------------
1 row in set (0.00 sec)
6SUBSTR(str, start, length)从字符串str的start位置开始取出length长度的子串位置从1开始计算
mysql select substr(hello world,7);
-------------------------
| substr(hello world,7) |
-------------------------
| world |
-------------------------
1 row in set (0.00 sec)mysql select substr(hello world,7,3);
---------------------------
| substr(hello world,7,3) |
---------------------------
| wor |
---------------------------
1 row in set (0.00 sec)
7INSTR(str,str1)返回str1参数在str参数内的位置
mysql select instr(hello world,o);
--------------------------
| instr(hello world,o) |
--------------------------
| 5 |
--------------------------
1 row in set (0.00 sec)mysql select instr(hello world,or);
---------------------------
| instr(hello world,or) |
---------------------------
| 8 |
---------------------------
1 row in set (0.00 sec)mysql select instr(hello world,ol); //不存在的参数返回0
---------------------------
| instr(hello world,ol) |
---------------------------
| 0 |
---------------------------
1 row in set (0.00 sec)
例如查询name字段为“李某”返回email字段的记录中“”的位置
mysql select name,email,instr(email,) from employees where name李平;
------------------------------------------
| name | email | instr(email,) |
------------------------------------------
| 李平 | lipingtedu.cn | 7 |
------------------------------------------
1 row in set (0.00 sec)
8TRIM(str)返回并删除两边空格之后的字符串str仅字符串两边的空格
mysql select trim( Hello World );
-------------------------
| trim( Hello World ) |
-------------------------
| Hello World |
-------------------------
1 row in set (0.00 sec) 2、数学函数实例 ABS(x)、PI()、MOD(x,y)、CEIL(x)、FLOOR(x)、ROUND(x)和ROUND(x,y) 1ABS(x)返回x的绝对值
mysql select abs(-10);
----------
| abs(-10) |
----------
| 10 |
----------
1 row in set (0.01 sec)
2PI(): 返回圆周率π默认显示6位小数
mysql select pi();
----------
| pi() |
----------
| 3.141593 |
----------
1 row in set (0.00 sec)
3MOD(x,y): 返回x被y除后的余数
mysql select mod(10,3);
-----------
| mod(10,3) |
-----------
| 1 |
-----------
1 row in set (0.00 sec)
4CEIL(x)、CEILING(x): 返回不小于x的最小整数
mysql select ceil(10.1);
------------
| ceil(10.1) |
------------
| 11 |
------------
1 row in set (0.00 sec)mysql select ceil(-10.1);
-------------
| ceil(-10.1) |
-------------
| -10 |
-------------
1 row in set (0.00 sec)
5FLOOR(x): 返回不大于x的最大整数
mysql select floor(10.1);
-------------
| floor(10.1) |
-------------
| 10 |
-------------
1 row in set (0.00 sec)mysql select floor(-10.1);
--------------
| floor(-10.1) |
--------------
| -11 |
--------------
1 row in set (0.00 sec)
6ROUND(x)返回最接近于x的整数即对x进行四舍五入ROUND(x,y)返回最接近x的数其值保留到小数点后y位若y为负值则对x数进行位数四舍五入例如y-1则为1的1次方(10位数)y-2则为1的1的2次方100位数
mysql select round(10.66666);
-----------------
| round(10.66666) |
-----------------
| 11 |
-----------------
1 row in set (0.00 sec)mysql select round(10.66666,2);
-------------------
| round(10.66666,2) |
-------------------
| 10.67 |
-------------------
1 row in set (0.00 sec)mysql select round(13,-1); //对13进行位数上的四舍五入
--------------
| round(13,-1) |
--------------
| 10 |
--------------
1 row in set (0.00 sec)mysql select round(16,-1);
--------------
| round(16,-1) |
--------------
| 20 |
--------------
1 row in set (0.00 sec) 3、日期和时间函数实例
1CURDATE()、CURRENT_DATE(): 将当前日期按照YYYY-MM-DD或者YYYYMMDD格式的值返回具体格式根据函数用在字符串或是数字语境中而定
mysql select curdate();
------------
| curdate() |
------------
| 2021-06-02 |
------------
1 row in set (0.00 sec)mysql select curdate() 0;
---------------
| curdate() 0 |
---------------
| 20210602 |
---------------
1 row in set (0.00 sec)mysql select curdate() 30; //字符串语境
----------------
| curdate() 30 |
----------------
| 20210632 |
----------------
1 row in set (0.00 sec)
2NOW()返回当前日期和时间值格式为YYYY_MM-DD HH:MM:SS或YYYYMMDDHHMMSS具体格式根据函数用在字符串或数字语境中而定
mysql select now();
---------------------
| now() |
---------------------
| 2021-06-02 13:27:01 |
---------------------
1 row in set (0.00 sec)mysql select now() 0;
----------------
| now() 0 |
----------------
| 20210602185848 |
----------------
1 row in set (0.00 sec)
3UNIX_TIMESTAMP()返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数时间戳UNIX_TIMESTAMP(date): 返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数
mysql select unix_timestamp();
------------------
| unix_timestamp() |
------------------
| 1622603480 |
------------------
1 row in set (0.00 sec)mysql select unix_timestamp(20211001);
--------------------------
| unix_timestamp(20211001) |
--------------------------
| 1633017600 |
--------------------------
1 row in set (0.00 sec)
4FROM_UNIXTIME(date)和UNIX_TIMESTAMP互为反函数把UNIX时间戳转换为普通格式的时间
mysql select from_unixtime(0);
---------------------
| from_unixtime(0) |
---------------------
| 1970-01-01 08:00:00 |
---------------------
1 row in set (0.00 sec)
5MONTH(date)返回指定日期中的月份MONTHNAME(date): 返回指定日期中的月份的名称
mysql select month(20210602111400);
-----------------------
| month(20210602111400) |
-----------------------
| 6 |
-----------------------
1 row in set (0.00 sec)mysql select month(now());
--------------
| month(now()) |
--------------
| 6 |
--------------
1 row in set (0.00 sec)mysql select month(20211001);
-----------------
| month(20211001) |
-----------------
| 10 |
-----------------
1 row in set (0.00 sec)mysql select monthname(20210602111400);
---------------------------
| monthname(20210602111400) |
---------------------------
| June |
---------------------------
1 row in set (0.00 sec)
6DAY(date)返回日期DAYNAME(date)返回d对应日期的英文名称如Sunday、Monday等
mysql select day(20211001);
---------------
| day(20211001) |
---------------
| 1 |
---------------
1 row in set (0.00 sec)mysql select dayname(20211001); //20211001为星期五
-------------------
| dayname(20211001) |
-------------------
| Friday |
-------------------
1 row in set (0.00 sec)
7WEEK(date)返回计算日期是一年中的第几周DAYOFWEEK(date)返回的对应一周中的索引1表示周日、2表示周一WEEKDAY(date)表示d对应的工作日索引0表示周一1表示周二
mysql select week(20211001);
----------------
| week(20211001) |
----------------
| 39 |
----------------
1 row in set (0.00 sec)mysql select dayofweek(20211001);
---------------------
| dayofweek(20211001) |
---------------------
| 6 |
---------------------
1 row in set (0.00 sec)mysql select weekday(20211001);
-------------------
| weekday(20211001) |
-------------------
| 4 |
-------------------
1 row in set (0.00 sec)
8DAYOFYEAR(date)返回date是一年中的第几天DAYOFMONTH(date): 返回d是一月中的第几天
mysql select dayofyear(20211001);
-----------------------
| dayofyear(20211001) |
-----------------------
| 274 |
-----------------------
1 row in set (0.00 sec)mysql select dayofmonth(20211001);
------------------------
| dayofmonth(20211001) |
------------------------
| 1 |
------------------------
1 row in set (0.00 sec)
9YEAR(date)返回指定日期对应的年份范围是1970到2069QUARTER(date)返回date对应一年中的季度范围是1到4MINUTE(time)返回time对应的分钟数范围是0~59SECOND(time)返回制定时间的秒值
mysql select year(20211001);
------------------
| year(20211001) |
------------------
| 2021 |
------------------
1 row in set (0.00 sec)mysql select quarter(20211001);
---------------------
| quarter(20211001) |
---------------------
| 4 |
---------------------
1 row in set (0.00 sec)mysql select minute(20211001141158);
--------------------------
| minute(20211001141158) |
--------------------------
| 11 |
--------------------------
1 row in set (0.00 sec)mysql select second(20211001141158);
--------------------------
| second(20211001141158) |
--------------------------
| 58 |
--------------------------
1 row in set (0.00 sec)
例如查询birth_date字段的记录以2000后出生的人并进行升序排序 mysql select name,birth_date - from employees where year(birth_date) 2000 order by birth_date;
-----------------------
| name | birth_date |
-----------------------
| 陈斌 | 2000-01-22 |
| 王璐 | 2000-02-01 |
| 游静 | 2000-02-14 |
| 陶红 | 2000-02-21 |
| 张倩 | 2000-04-27 |
| 蒋秀芳 | 2000-04-27 |
| 胡秀云 | 2000-05-14 |
| 张宇 | 2000-07-16 |
-----------------------
8 rows in set (0.00 sec) 4、流程控制函数实例
1IF(expr,v1,v2)如果expr表达式是TRUE则返回v1否则返回v2
mysql select if(30,yes,no);
--------------------
| if(30,yes,no) |
--------------------
| yes |
--------------------
1 row in set (0.00 sec)
例如查询name字段为张亮的记录判断该记录的dept_id是否为1
mysql select name,dept_id,if(dept_id1,人事部,非人事部) - from employees where name张亮;
-----------------------------------------------------------
| name | dept_id | if(dept_id1,人事部,非人事部) |
-----------------------------------------------------------
| 张亮 | 7 | 非人事部 |
-----------------------------------------------------------
1 row in set (0.00 sec)
2IFNULL(v1,v2)如果v1不为NULL则返回v1否则返回v2
mysql select dept_id,dept_name,ifnull(dept_name,not set) - from departments;
-------------------------------------------------
| dept_id | dept_name | ifnull(dept_name,not set) |
-------------------------------------------------
| 1 | 人事部 | 人事部 |
| 2 | 财务部 | 财务部 |
| 3 | 运维部 | 运维部 |
| 4 | 开发部 | 开发部 |
| 5 | 测试部 | 测试部 |
| 6 | 市场部 | 市场部 |
| 7 | 销售部 | 销售部 |
| 8 | 法务部 | 法务部 |
| 9 | NULL | not set |
-------------------------------------------------
9 rows in set (0.00 sec)
3CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END: 如果expr等于某个vn则返回对应位置THEN后面的结果如果与所有值都不相等则返回ELSE后面的rn
mysql select dept_id,dept_name,- case- when dept_name运维部 then 技术部门- when dept_name开发部 then 技术部门- when dept_name测试部 then 技术部门- when dept_name is null then 未设置- else 非技术部门- end as 部门类型- from departments;
-------------------------------------
| dept_id | dept_name | 部门类型 |
-------------------------------------
| 1 | 人事部 | 非技术部门 |
| 2 | 财务部 | 非技术部门 |
| 3 | 运维部 | 技术部门 |
| 4 | 开发部 | 技术部门 |
| 5 | 测试部 | 技术部门 |
| 6 | 市场部 | 非技术部门 |
| 7 | 销售部 | 非技术部门 |
| 8 | 法务部 | 非技术部门 |
| 9 | NULL | 未设置 |
-------------------------------------
9 rows in set (0.00 sec) 5、分组函数 分组函数用于统计又称为聚合函数或统计函数 1sum(): 求和
例如查询员工id10且在2018年的工资奖金的记录每一行记录单独处理
mysql select employee_id,basicbonus - from salary where employee_id10 and year(date)2018;
--------------------------
| employee_id | basicbonus |
--------------------------
| 10 | 24837 |
| 10 | 31837 |
| 10 | 29837 |
| 10 | 29878 |
--------------------------
4 rows in set (0.00 sec)
例如查询员工id10且在2018年的工资奖金求和的记录所有记录进行求和
mysql select employee_id,sum(basicbonus) - from salary where employee_id10 and year(date)2018;
-------------------------------
| employee_id | sum(basicbonus) |
-------------------------------
| 10 | 116389 |
-------------------------------
1 row in set (0.00 sec)
2avg()求平均值
例如查询员工id10且在2018年的工资奖金的平均值的记录
mysql select employee_id,avg(basicbonus) - from salary where employee_id10 and year(date)2018;
-------------------------------
| employee_id | avg(basicbonus) |
-------------------------------
| 10 | 29097.2500 |
-------------------------------
1 row in set (0.00 sec)
3max()求最大值
mysql select employee_id, max(basicbonus) - from salary where employee_id10 and year(date)2018;
-------------------------------
| employee_id | max(basicbonus) |
-------------------------------
| 10 | 31837 |
-------------------------------
1 row in set (0.00 sec)
4min()求最小值
mysql select employee_id, min(basicbonus) - from salary where employee_id10 and year(date)2018;
-------------------------------
| employee_id | min(basicbonus) |
-------------------------------
| 10 | 24837 |
-------------------------------
1 row in set (0.01 sec)
5count()统计记录的总个数
mysql select count(*) from departments;
----------
| count(*) |
----------
| 9 |
----------
1 row in set (0.00 sec) 三、分组查询
在对数据表中数据进行统计时可能需要按照一定的类别分别进行统计。比如查询每个部门的员工数使用GROUP BY按某个字段或者多个字段中的值进行分组字段中值相同的为一组
语法格式 SELECT 字段名1(要求出现在group by后面)分组函数(),…… FROM 表名 WHERE 条件 GROUP BY 字段名1字段名2 HAVING 过滤条件; ORDER BY 字段 注意事项
① 查询列表必须是分组函数和出现在GROUP BY后面的字段② 通常而言分组前的数据筛选放在where子句中分组后的数据筛选放在having子句中
例如查询每个部门的人数
mysql select dept_id,count(*) from employees group by dept_id;
-------------------
| dept_id | count(*) |
-------------------
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 4 | 55 |
| 5 | 12 |
| 6 | 9 |
| 7 | 35 |
| 8 | 3 |
-------------------
8 rows in set (0.00 sec)
例如查询每个部门的每个员工在2018年的工资奖金
mysql select employee_id,sum(basicbonus) - from salary where year(date)2018 - group by employee_id;
-------------------------------
| employee_id | sum(basicbonus) |
-------------------------------
| 1 | 151046 |
| 2 | 328131 |
| 3 | 177595 |
…
| 133 | 146733 |
-------------------------------
120 rows in set (0.01 sec)
例如查询每个部门中年龄最大的员工出生年月最小即年龄最大
mysql select dept_id,min(birth_date) from employees group by dept_id;
--------------------------
| dept_id | min(birth_date) |
--------------------------
| 1 | 1971-08-19 |
| 2 | 1971-11-02 |
| 3 | 1971-09-09 |
| 4 | 1972-01-31 |
| 5 | 1971-08-14 |
| 6 | 1973-04-14 |
| 7 | 1971-12-10 |
| 8 | 1989-05-19 |
--------------------------
8 rows in set (0.00 sec)
例如查询每个部门入职最晚员工的入职时间
mysql select dept_id,max(hire_date) from employees group by dept_id;
-------------------------
| dept_id | max(hire_date) |
-------------------------
| 1 | 2018-11-21 |
| 2 | 2018-09-03 |
| 3 | 2019-07-04 |
| 4 | 2021-02-04 |
| 5 | 2019-06-08 |
| 6 | 2017-10-07 |
| 7 | 2020-08-21 |
| 8 | 2019-11-14 |
-------------------------
8 rows in set (0.00 sec)
例如统计各部门使用了tedu.cn邮箱的人数
mysql select dept_id,count(*) - from employees where email like %tedu.cn- group by dept_id;
-------------------
| dept_id | count(*) |
-------------------
| 1 | 5 |
| 2 | 2 |
| 3 | 4 |
| 4 | 32 |
| 5 | 7 |
| 6 | 5 |
| 7 | 15 |
| 8 | 1 |
-------------------
8 rows in set (0.00 sec)
例如查看员工2018年工资总收入按总收入进行降序排列
mysql select employee_id,sum(basicbonus) as total - from salary where year(date)2018 - group by employee_id - order by total desc;
---------------------
| employee_id | total |
---------------------
| 31 | 374923 |
| 117 | 374923 |
| 37 | 362981 |
| 68 | 360923 |
…
| 8 | 25093 |
---------------------
120 rows in set (0.00 sec)
例如查询部门人数少于10人
mysql select dept_id,count(*) - from employees group by dept_id having count(*) 10; //分组后过滤
-------------------
| dept_id | count(*) |
-------------------
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 6 | 9 |
| 8 | 3 |
-------------------
5 rows in set (0.00 sec)
例如查询2018年的员工总工资大于300000的员工
mysql select employee_id,sum(basicbonus) as total - from salary where year(date)2018 - group by employee_id having sum(basicbonus)300000 - order by total desc;
---------------------
| employee_id | total |
---------------------
| 31 | 374923 |
| 117 | 374923 |
| 37 | 362981 |
…
| 40 | 304131 |
---------------------
31 rows in set (0.01 sec) 四、连接查询 连接查询也叫多表查询常用于查询字段来自于多张表 例如如果直接查询两张表将会得到笛卡尔积两张表相乘
mysql select name, dept_name from employees, departments;
----------------------
| name | dept_name |
----------------------
| 梁伟 | 人事部 |
| 梁伟 | 财务部 |
| 梁伟 | 运维部 |
| 梁伟 | 开发部 |
| 梁伟 | 测试部 |
| 梁伟 | 市场部 |
| 梁伟 | 销售部 |
| 梁伟 | 法务部 |
| 梁伟 | NULL |
----------------------
1197 rows in set (0.00 sec)
例如通过添加有效的条件可以进行查询结果的限定
mysql select name,dept_name - from employees,departments - where employees.dept_iddepartments.dept_id;
----------------------
| name | dept_name |
----------------------
| 梁伟 | 人事部 |
| 郭岩 | 人事部 |
| 李玉英 | 人事部 |
| 张健 | 人事部 |
| 郑静 | 人事部 |
| 杨金凤 | 法务部 |
----------------------
133 rows in set (0.00 sec)
1. 按功能分类
1内连接Inner Join
内连接返回两个表中满足连接条件的行。
① 等值连接Equi Join
等值连接使用等号作为连接条件返回两个表中连接字段值相等的行。
示例
SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id departments.department_id;② 非等值连接Non-Equi Join
非等值连接使用除等号以外的比较运算符如 、、、、BETWEEN 等作为连接条件返回两个表中连接字段值满足非等值条件的行。
示例
SELECT employees.employee_id, employees.first_name, salaries.salary
FROM employees
INNER JOIN salaries ON employees.salary_id BETWEEN salaries.min_salary AND salaries.max_salary;③ 自连接Self Join
自连接是指一个表与自身进行连接通常用于查询表中具有层次结构或父子关系的数据。
示例
SELECT e1.employee_id, e1.first_name, e2.first_name AS manager_name
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id e2.employee_id;2外连接Outer Join
外连接返回两个表中满足连接条件的行同时还会返回不满足连接条件的行。
① 左外连接Left Outer Join
左外连接返回左表中的所有行以及右表中满足连接条件的行。如果右表中没有匹配的行则返回 NULL。
示例
SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id departments.department_id;② 右外连接Right Outer Join
右外连接返回右表中的所有行以及左表中满足连接条件的行。如果左表中没有匹配的行则返回 NULL。
示例
SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id departments.department_id;③ 全外连接Full Outer Join
全外连接返回两个表中的所有行无论是否满足连接条件。如果一个表中没有匹配的行则返回 NULL。MySQL 不直接支持全外连接但可以使用 UNION 实现相同的效果。
示例
SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id departments.department_id
UNION
SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id departments.department_id;3交叉连接Cross Join
交叉连接返回两个表的笛卡尔积即第一个表中的每一行与第二个表中的每一行进行组合。
示例
SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
CROSS JOIN departments;2. 按年代分类
① SQL92 标准
SQL92 标准仅支持内连接使用 FROM 子句和 WHERE 子句进行连接操作。
示例
SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees, departments
WHERE employees.department_id departments.department_id;② SQL99 标准
SQL99 标准支持所有功能的连接包括内连接、外连接和交叉连接使用 JOIN 关键字和 ON 子句进行连接操作。
示例
SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id departments.department_id;补充SQL99 标准多表查询
SQL99 标准引入了更清晰的语法来处理多表查询使得查询语句更易于理解和维护。
示例
SELECT e.employee_id, e.first_name, d.department_name, l.city
FROM employees e
INNER JOIN departments d ON e.department_id d.department_id
INNER JOIN locations l ON d.location_id l.location_id; 【SQL99标准多表查询】
语法格式 SELECT 字段... FROM 表1 [AS] 别名 [连接类型] JOIN 表2 [AS] 别名 ON 连接条件 WHERE 分组前筛选条件 GROUP BY 分组 HAVING 分组后筛选条件 ORDER BY 排序字段 【内连接】
语法格式 SELECT 查询列表 FROM 表1 别名 INNER JOIN 表2 别名 ON 连接条件 INNER JOIN 表3 别名 ON 连接条件 [WHERE 筛选条件] [GROUP BY 分组] [HAVING 分组后筛选] [ORDER BY 排序列表] 内连接查询案例
1内连接 - 等值连接案例
例如查询每个员工所在的部门名
mysql select name,dept_name from employees - inner join departments - on employees.dept_iddepartments.dept_id;
例如查询每个员工所在的部门名使用别名
mysql select e.name,d.dept_name from employees as e - inner join departments as d - on e.dept_idd.dept_id;
----------------------
| name | dept_name |
----------------------
| 梁伟 | 人事部 |
| 郭岩 | 人事部 |
| 李玉英 | 人事部 |
| 张健 | 人事部 |
| 郑静 | 人事部 |
| 杨金凤 | 法务部 |
----------------------
133 rows in set (0.00 sec)
例如查询11号员工的名字及2018年每个月工资
mysql select name, date, basicbonus as total- from employees as e- inner join salary as s- on e.employee_ids.employee_id- where year(s.date)2018 and e.employee_id11;
------------------------------
| name | date | total |
------------------------------
| 郭兰英 | 2018-01-10 | 18206 |
| 郭兰英 | 2018-02-10 | 19206 |
| 郭兰英 | 2018-03-10 | 18206 |
| 郭兰英 | 2018-04-10 | 19206 |
| 郭兰英 | 2018-05-10 | 18206 |
| 郭兰英 | 2018-06-10 | 19206 |
| 郭兰英 | 2018-07-10 | 27206 |
| 郭兰英 | 2018-08-10 | 27206 |
| 郭兰英 | 2018-09-10 | 19206 |
| 郭兰英 | 2018-10-10 | 21206 |
| 郭兰英 | 2018-11-10 | 22206 |
| 郭兰英 | 2018-12-10 | 25016 |
------------------------------
12 rows in set (0.00 sec) 例如查询2018年每个员工的总工资按工资升序排列
mysql select name, sum(basicbonus) as total from employees as e- inner join salary as s- on e.employee_ids.employee_id- where year(s.date)2018- group by name- order by total; 例如查询2018年总工资大于30万的员工按工资降序排列
mysql select name, sum(basicbonus) as total from employees as e- inner join salary as s- on e.employee_ids.employee_id- where year(s.date)2018- group by name- having total300000- order by total desc; 2内连接 - 非等值连接案例
① 创建数据表
语法格式 CREATE TABLE 表名称 ( 列名称1 数据类型, 列名称2 数据类型, 列名称3 数据类型, .... ); 例如创建工资级别表
- id设为主键仅作为表的行号- grade工资级别共ABCDE五类- low该级别最低工资- high该级别最高工资
mysql use nsd2021;
mysql create table wage_grade- (- id int,- grade char(1),- low int,- high int,- primary key (id));
Query OK, 0 rows affected (0.00 sec)
② 向表中插入数据
语法格式INSERT INTO 表名称 VALUES (值1, 值2,....);
例如向wage_grade表中插入五行数据
mysql insert into wage_grade values- (1, A, 5000, 8000),- (2, B, 8001, 10000),- (3, C, 10001, 15000),- (4, D, 15001, 20000),- (5, E, 20001, 1000000);
例如查询2018年12月员工基本工资级别
mysql select employee_id, date, basic, grade- from salary as s- inner join wage_grade as g- on s.basic between g.low and g.high- where year(date)2018 and month(date)12;
---------------------------------------
| employee_id | date | basic | grade |
---------------------------------------
| 1 | 2018-12-10 | 17016 | D |
| 2 | 2018-12-10 | 20662 | E |
| 3 | 2018-12-10 | 9724 | B |
---------------------------------------
120 rows in set (0.00 sec)
例如查询2018年12月员工各基本工资级别的人数
mysql select grade, count(*)- from salary as s- inner join wage_grade as g- on s.basic between g.low and g.high- where year(date)2018 and month(date)12- group by grade;
-----------------
| grade | count(*) |
-----------------
| A | 13 |
| B | 12 |
| C | 30 |
| D | 32 |
| E | 33 |
-----------------
5 rows in set (0.00 sec)
例如查询2018年12月员工基本工资级别员工需要显示姓名
mysql select name,date,basic,grade- from employees as e- inner join salary as s- on e.employee_ids.employee_id- inner join wage_grade as g- on s.basic between g.low and g.high- where year(date)2018 and month(date)12;
-------------------------------------
| name | date | basic | grade |
-------------------------------------
| 梁伟 | 2018-12-10 | 17016 | D |
| 郭岩 | 2018-12-10 | 20662 | E |
| 李玉英 | 2018-12-10 | 9724 | B |
| 杨金凤 | 2018-12-10 | 6076 | A |
-------------------------------------
120 rows in set (0.00 sec) 3内连接 - 自连接案例 将一张表作为两张表使用每张表各起一个别名 例如查看哪些员的生日月份与入职月份相同同一张表中的两个字段作为两张表使用
mysql select e.name,e.hire_date,em.birth_date- from employees as e- inner join employees as em- on month(e.hire_date)month(em.birth_date)- and e.employee_idem.employee_id;
-----------------------------------
| name | hire_date | birth_date |
-----------------------------------
| 李玉英 | 2012-01-19 | 1974-01-25 |
| 郑静 | 2018-02-03 | 1997-02-14 |
| 林刚 | 2007-09-19 | 1990-09-23 |
| 刘桂兰 | 2003-10-14 | 1982-10-11 |
| 张亮 | 2015-08-10 | 1996-08-25 |
| 许欣 | 2011-09-09 | 1982-09-25 |
| 王荣 | 2019-11-14 | 1999-11-22 |
-----------------------------------
7 rows in set (0.01 sec) 外连接查询案例
常用于查询一个表中有另一个表中没有的记录
如果从表中有和它匹配的则显示匹配的值如果从表中没有和它匹配的则显示NULL外连接查询结果 内连接查询结果 主表中有而从表中没有的记录左外连接中left join左边的是主表右外连接中right join右边的是主表左外连接和右外连接可互换实现相同的目标 【左外连接】
语法格式 SELECT tb1.字段..., tb2.字段 FROM table1 AS tb1 LEFT [OUTER] JOIN table2 AS tb2 ON tb1.字段tb2.字段 例如查询所有部门的人员以及没有员工的部门
mysql SELECT d.*,e.name- from departments d- left outer join employees e- on d.dept_id e.dept_id;
-------------------------------
| dept_id | dept_name | name |
-------------------------------
| 1 | 人事部 | 梁伟 |
| 1 | 人事部 | 郭岩 |
| 1 | 人事部 | 李玉英 |
…
| 8 | 法务部 | 王荣 |
| 8 | 法务部 | 刘倩 |
| 8 | 法务部 | 杨金凤 |
| 9 | NULL | NULL |
-------------------------------
134 rows in set (0.00 sec) 【右外连接】
语法格式 SELECT tb1.字段..., tb2.字段 FROM table1 AS tb1 RIGHT [OUTER] JOIN table2 AS tb2 ON tb1.字段tb2.字段 例如查询所有部门的人员以及没有员工的部门结果左外连接与相同
mysql SELECT d.*,e.name- from employees e- right outer join departments d- on d.dept_id e.dept_id; 【交叉连接】
- 返回笛卡尔积
格式SELECT 字段名 FROM 表1 CROSS JOIN 表2 [WHERE子句]
mysql select name, dept_name- from employees- cross join departments; 扩展知识
授予管理员root可以通过任意地址访问数据库密码是NSD2021tedu.cn。默认情况下root只允许在本机访问
mysql grant all on *.* to root% identified by NSD2021tedu.cn;
# 解释后面是主机名to后面是用户名一般是root用户by后面是mysql数据库的密码
报错信息java.sql.SQLException: null, message from server: Host 192.168.2.5 is not allowed to connect...
分析原因表示该host不是远程对象不能通过该对象远程访问数据库解决办法授予远程访问数据库对象权限如果尚明命令不生效尝试以下办法 a) 如果使用root主机名的授权方式在没有设置本机IP与主机名的映射即使数据库知道IP或者hostname单独的一个是不能远程连接Mysql数据库的需要修改hosts文件添加主机名和IP的映射 b) 不建议的方法对任何远程的IP或者Hostname都允许远程连接root%的授权方式 c) 通过在配置好Hosts文件后在JDBC的连接中输入IP或者Hostname都是可以访问的单独设置主机和IP的映射 例如
mysql grant all on *.* to rootlocalhost identified by NSD2021tedu.cn;
mysql grant all on *.* to root192.168.2.5 identified by NSD2021tedu.cn;
Visualstudio数据库工具下载地址Visual Studio Code - Code Editing. Redefined 思维导图 小结
本篇章节为【第四阶段】RDBMS1-DAY2 的学习笔记这篇笔记可以初步了解到 常用函数函数分类1单行、分组函数分类2字符、数学、日期、流程控制、分组查询group by、连接查询。 Tip毕竟两个人的智慧大于一个人的智慧如果你不理解本章节的内容或需要相关笔记、视频可私信小安请不要害羞和回避可以向他人请教花点时间直到你真正的理解。
