【MySQL基础】一、介绍_非等值连接和group by-CSDN博客

本文链接：https://blog.csdn.net/Elked/article/details/115391470

一、数据库的好处

持久化数据到本地
可以实现结构化查询，方便管理

二、数据库的概念

DB：数据库（database）：存储数据的“仓库”，它保存了一系列有组织的数据
DBMS：数据库管理系统（Database Management System），数据库是通过DBMS创建和操作的容器
- 基于共享文件系统的DBMS（Access）
- 基于客户机-服务器的DBMS（MySQL、Oracle、SQL server）
SQL：结构化查询语句（Structure Query Language），专门用来和数据库通信的语言
- SQL的优点：不是某个特定的数据库提供商专有的语言，几乎所有的DBMS都支持SQL；简单易学，虽然简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作

三、数据库的特点

将数据放到表中，表再放到库中
一个数据库可有有多张表，每个表有一个名字，用来标识自己，表名具有唯一性
表具有一些特性，这些特性定义了数据在表中如何存储，类似Java中的“类”设计
表由列组成，我们也称为字段，所有的表都由一个或多个列组成，每一列类似Java中的“属性”
表中的数据是按行存储的，每一行类似Java中的“对象”

四、MySQL的常见命令

SHOW DATABASES;  # 获取当前存在哪些库
USE database_name;  # 进入到具体的库中
SHOW TABLES;  # 查看一个库中所有的表（前提是已经进入到某个库中）
SHOW TABLES FROM database_name;  # 直接查看某个库中所有的表，而并不需要进入到库中
SELECT DATABASE();  # 获取当前进入的是哪个库
CREATE TABLE stuinfo (id INT,stu_name VARCHAR(20));  # 创建一张表
DESC table_name;  # 获取一张表的结构
SELECT * FROM table_name;  # 获取一张表的所有数据
INSERT INTO stuinfo (id,stu_name) VALUES (1,'zhangsan');  # 向一张表中插入数据
UPDATE stuinfo SET stu_name = 'lisi' WHERE id = 1;  # 更新一张表中的某个字段
SELECT VERSION();  # 获取mysql的版本信息

五、MySQL的语法规范

不区分大小写，但建议关键字大写，表名、列名小写
每条命令最好用分号结尾
每条命令根据需要，可以进行缩进或换行
注释
- 单行注释：# 注释文字或 -- 注释文字，但是 -- 后面是有一个空格的
- 多行注释：/* 注释文字 */

六、DQL语言的学习

6.1、基础查询

语法：SELECT 查询列表 FROM 表名;

特点：

1、查询列表可以是：表中的字段、常量值、表达式、函数

2、查询的结果是一个虚拟的表格

查询单个字段

SELECT last_name FROM employees;

查询表中多个字段

SELECT last_name,salary,email FROM employees;

查询表中所有字段

SELECT * FROM employees;

查询常量值

SELECT 100;
SELECT 'JOHN';

查询表达式

SELECT 100 * 90;

查询函数

SELECT version();

起别名（便于理解，如果查询的字段有重名的情况，可以使用别名来区分）

SELECT 100%98 AS result;
SELECT last_name 姓, first_name 名 FROM employees;（这里使用查询的时候可以省略中间的 AS）
如果在使用别名的时候，别名中间存在有特殊符号或者别名中存有关键字，这个时候可以将别名加上单/双引号（MySQL推荐使用双引号）

去重

SELECT DISTINCT department_id FROM employees;

+ 号的作用（在MySQL中间的 + 仅仅只有一个功能：运算符）

SELECT 10 + 90 AS out_data;
SELECT '123' + 90; （如果一方为字符型，则视图将字符型转换为数值型，如果转换成功，则继续进行加法运算，如果转换失败，则将字符型转为0，再进行加法运算）
SELECT NULL + 10;（只要一方为NULL，则结果为NULL）

在MySQL中想要实现多个字段查询结果的拼接，可以使用CONCAT函数：SELECT CONCAT(str1, str2, str3);

查询为NULL进行默认处理

SELECT IFNULL(age, 20) FROM employees;（在进行查找的时候，如果字段为NULL，则会设定一个默认值20）

6.2、条件查询

语法：SELECT 查询列表 FROM 表名 WHERE 筛选条件

特点：

1、按条件表达式进行筛选，条件运算符： > < = != <>

2、按逻辑表达式进行筛选，逻辑运算符：&& || ! 但是在MySQL中使用的是and、or、not，and ：两个条件都为true，结果为true，否则为false；or ：只有一个条件为true，结果为true，否则为false；not ：如果连接条件本身为false，结果为true，否则为false

3、模糊查询：LIKE、BETWEEN AND、IN、IS NULL；LIKE 一般和通配符搭配使用，%表示任意多个字符；使用BETWEEN AND 可以提高语句的简明度（包含临界值，两个临界值的顺序不能颠倒）；IN 判断某个字段的值是否属于IN列表中的一项（使用IN提高语句简洁度、IN列表的值必须一致或兼容）

安全等于：<=>

按条件进行筛选

SELECT * FROM employees WHERE salary > 10000;
SELECT last_name,department_id FROM employees WHERE department_id <> 90;

按逻辑表达式进行筛选

SELECT last_name,salary,commission_pct FROM employees WHERE salary >=10000 AND salary <= 20000;
SELECT * FROM employees WHERE department_id < 90 OR department_id > 110 OR salary > 15000;

模糊查询

SELECT * FROM employees WHERE last_name LIKE '%a%'; （这里的%代表的是通配符（包含0个字符），_ 表示任意单个字符）
SELECT last_name FROM employees WHERE last_name LIKE '__e_a%';
SELECT last_name FROM employees WHERE last_name LIKE '_\_%'; （这里的 \ 是转义字符）
SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$'; （这里使用ESCAPE声明$不需要进行转义）
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
SELECT * FROM employees WHERE job_id IN('IT_PROT','AD_VP','AD_PRES') ; (在使用IN的时候，不能在中间使用%)
SELECT * FROM employees WHERE a IS NULL; （和 IS NULL 对应的就是 IS NOT NULL，这里也可以使用 <=> NULL）

6.3、排序查询

语法：SELECT 查询列表 FROM 表名 [WHERE 筛选条件] ORDER BY 排序列表 ASC | DESC

特点：

1、asc 代表的是升序，desc代表的是降序，如果不写，默认是升序

2、order by 子句中可以支持单个字段、多个字段、表达式、函数、别名

3、order by 子句一般放在查询语句的最后面，limit子句除外

查询结果从高到低进行排序

SELECT * FROM employees ORDER BY salary DESC;

查询结果从低到高进行排序

SELECT * FROM employees ORDER BY salary ASC;

查询时候存在筛选条件，并且结果按照另一个字段进行排序

SELECT * FROM employees WHERE department_id >= 90 ORDER BY hiredate asc;

表达式排序

SELECT salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

按别名排序

SELECT salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY 年薪 DESC;

按函数排序

SELECT LENGTH(last_name) AS 字节长度,last_name,salary FROM employees ORDER BY LENGTH(last_name) DESC;

按多个字段排序

SELECT * FROM employees ORDER BY salary ASC,employ_id DESC;

6.4、常见函数

6.4.1、字符函数

length 获取参数值的字节个数

SELECT LENGTH('john') 这里得到的结果是4
SELECT LENGTH('张三丰hahaha') 这里得到的结果为15，一个中文占据3个字节，当然，这个是根据字符集来进行判断的

concat 拼接字符串

SELECT CONCAT('aaa',',','cccc') 这里得到的结果是 aaa,ccc

upper 和 lower

SELECT UPPER('beijing') 得到的结果是 BEIJING 同理使用lower就能够将大写变成小写

substr、substring，索引是从1开始的

SELECT SUBSTR('我爱北京天安门',5) 通过这样的方式就能够将中间的天安门获取出来
SELECT SUBSTR('我爱北京天安门',3,2) 这里给定了两个参数，第一个参数表示从哪个位置开始截取，第二个参数表示截取多长的字符，这里注意是字符，并不是字节

instr用于获取子串在全字符串中的第一次出现的起始索引，如果没有则返回0

SELECT INSTR('hello world','world'); 这里返回的是 7 ，索引从1开始的

trim用于将获取的内容左右的空格去掉，如果想要去掉指定的内容，则需要将内容进行申明

SELECT TRIM(' hello world ');
SELECT TRIM('#' FROM'#######hello#####world#########'); 得到的结果就是 hello#####world，这里除了可以使用到单个字符，也可以用到一个字符串

lpad、rpad

SELECT LPAD('hello',10,'*') AS out_put; 这里使用左填充，将 * 填充在 hello 的左边，并且填充完成之后的总长度是10
SELECT RPAD('hello',10,'*') AS out_put; 这里使用右填充

replace 替换

SELECT REPLACE('hello world','world','china') AS out_put; 这里输出结果是 hello china

6.4.2、数学函数

round 四舍五入

SELECT ROUND(1.65); 得到的结果就是 2
SELECT ROUND(1.689,2); round 后面支持第二个参数，表示保留小数点后面几位，这里指定保留的是小数点后面两位，结果为1.69

ceil 向上取整，返回>=该参数的最小整数

SELECT CEIL(1.52); 得到的结果是2

floor 向下取整，返回<=该参数的最大整数

SELECT FLOOR(1.98); 得到的结果是1

truncate 截断

SELECT TRUNCATE(1.65,1); 得到的返回结果是1.6，这里表示的是将一个小数后面截断几位

mod 取余

SELECT MOD(10,3); 这里取余得到的结果是1

6.4.3、日期函数

now 获取当前系统日期+时间

SELECT NOW();

curdate 返回当前系统日期，不包含时间

SELECT CURDATE();

curtime 返回当前系统时间，不包含日期

SELECT CURTIME();

可以获取指定的部分：年、月、日、小时、分钟、秒

SELECT YEAR(NOW()); 获取当前的年份
SELECT MONTH(NOW()); 获取当前的月份
SELECT MONTHNAME(NOW()); 获取当前月份的名字

datediff：获取两个日期相差的天数

SELECT DATEDIFF('2020-6-13','2020-6-2');

str_to_date：将日期格式的字符串转换成指定格式的日期

SELECT STR_TO_DATE('13-6-2020','%d-%m-%Y');

date_format：将日期转为字符

SELECT DATE_FORMAT(NOW(),'%d-%m-%Y');

6.4.4、其他函数

SELECT VERSION(); # 获取当前数据库的版本
SELECT DATABASE(); # 获取当前所处的数据库
SELECT USER(); # 获取当前登陆的用户

6.4.5、流程控制函数

if 函数：if else 的效果

SELECT IF(10>5,'true','false'); mysql中间的if其实和三目运算符差不多的意思，如果第一个条件成立，则返回第二个参数的结果，否则返回第三个参数的结果

case 函数的使用一：switch case 的效果

语法：
case要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end

SELECT
	salary 原始工资,
	department_id
CASE department_id 
WHEN 30 THEN salary * 1.1 
WHEN 40 THEN salary * 1.2
WHEN 50 THEN salary * 1.3 
ELSE salary 
END AS 新工资 
FROM
	employees;

case 函数的使用二：类似于多重 if

语法：
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

SELECT
	salary
CASE
WHEN salary > 20000 THEN 'A' 
WHEN salary > 15000 THEN 'B' 
WHEN salary > 10000 THEN 'C'
ELSE 'D' 
END AS '工资级别' 
FROM
employees;

6.4.6、分组函数

功能：用于统计使用，又称为聚合函数或统计函数或组函数

分类：

sum：求和 avg：平均值 max：最大值 min：最小值 count：计算个数

简单的使用

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

参数支持哪些类型

SUM 和 AVG 仅适合处理数值型的参数，其他的类型不会报错，但是已经没有意义了
MAX、MIN、COUNT 可以处理任何类型

是否忽略NULL值

在使用这些分组函数的时候都是已经忽略了NULL值的

可以和 distinct 搭配去重的运算

SELECT SUM(DISTINCT salary) FROM employees;

count 函数的详细介绍

SELECT COUNT(salary) FROM employees; 在使用这样的方式进行统计的时候，会忽略NULL行
SELECT COUNT(*) FROM employees; 通常使用这样的方式统计一个数据库中的总行数，只要一行中有一个不是NULL就会被统计上
SELECT COUNT(1) FROM employees; 这里除了1也能是其他的常量值，相当于在数据库中加了一列都是1的内容，然后统计这一列，其实这个是count(*)的结果是一样的
效率：MYISAM 存储引擎下，count(*) 的效率最高，因为这个存储引擎中，直接就有一个计数器，直接返回数字，效率很高；INNODB 存储引擎下，count(*)和count(1)的效率差不多，比count(字段)要高

和分组函数一同查询的字段有限制

和分组函数一同查询的字段要求是 gourp by 后的字段

6.4.7、分组查询

引入：需要获取每个部门的平均工资

语法：

select 分组函数,列(要求出现在group by的后面)

from 表

[where 筛选条件]

group by 分组的列表

[order by 子句]

注意：查询列表比较特殊，要求是分组函数和 group by 后出现的字段

查询每个工种的最高工资（简单分组查询）

SELECT MAX(salary),job_id FROM employees GROUP BY job_id;

查询每个位置上的部门个数（简单分组查询）

SELECT COUNT(*),location_id FROM employees GROUP BY location_id;

查询邮箱中包含a字符的，每个部门的工资

SELECT AVG(salary),department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id

查询有奖金的每个领导手下的最高工资

SELECT MAX(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;

查询哪个部门的员工个数 > 2(添加分组后的筛选)

SELECT COUNT(*),department_id FROM employees GROUP BY department_id; 这里首先获取到每个部门的员工个数
SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 2; 这里基于前面获取到的每个部门员工个数的前提下得到员工个数 > 2的

查询每个工种有奖金的员工的最高工资 > 12000的工种编号和最高工资

SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id; 查询每个工种有奖金的员工的最高工资
SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary) > 12000; 根据上面的结果继续筛选，最高工资大于12000

查询领导编号 > 102的每个领导手下的最低工资 > 5000 的领导编号是哪个，以及其工资

SELECT MIN(salary),manager_id FROM employees GROUP BY manager_id; 查询每个领导手下的员工固定最低工资
SELECT MIN(salary),manager_id FROM employees WHERE manager_id > 102 GROUP BY manager_id; 添加筛选条件：编号 > 102
SELECT MIN(salary),manager_id FROM employees WHERE manager_id > 102 GROUP BY manager_id HAVING MIN(salary) > 5000; 添加筛选条件：最低工资 > 5000

小结

按表达式或函数进行分组（group by）

前面介绍的group by后面基本就是某一个字段，其实group by后面也是能够放一个表达式或函数的

按员工姓名额长度分组，查询每一组员工的个数，筛选员工个数 > 5 的有哪些

SELECT COUNT(*),LENGTH(last_name) AS len_name FROM employees GROUP BY LENGTH(last_name); 查询每个长度的员工个数
SELECT COUNT(*),LENGTH(last_name) AS len_name FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*) > 5; 添加筛选条件

按多个字段进行分组（group by）

SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id; 查询每个部门每个工种的员工的平均工资

添加排序

SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id ORDER BY AVG(salary); 查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示

小结

group by 子句支持单个字段分组，多个字段分组（多个字段用逗号隔开，没有顺序要求），表达式或函数
也可以添加排序（排序放在整个分组查询的最后）

6.4.8、连接查询

sql92标准

1、等值连接

查询女神名对应的男神名

SELECT name,boyName FROM boys,beauty WHERE beauty.boyfirend_id = boys.id;

查询员工名对应的部门名

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

查询员工号、工种号、工种名

SELECT last_name,e.job_id,job_title FROM employees e,jobs j WHERE e.job_id = j.job_id; 中间使用到了别名

加筛选

SELECT last_name,department_name,commission_pct FROM employees e,departments d WHERE e.department_id = d.department_id AND e.commission_pct IS NOT NULL; 查询有奖金的员工名、部门名
SELECT department_name,city FROM departments d,locations l WHERE d.location_id = l.location_id AND city like '_o%'; 查询城市名中第二个字符为o的部门名和城市名

加分组

SELECT COUNT(*),city FROM departments d,locations l WHERE d.location_id = l.location_id GROUP BY city; 查询每个城市的部门个数
SELECT department_name,d.manager_id,MIN(salary) FROM departments d,employees e WHERE d.department_id = e.department_id AND commission_pct IS NOT NULL GROUP BY department_name,d.manager_id; 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

加排序

SELECT job_title,COUNT(*) FROM employees e,jobs j WHERE e.job_id = j.job_id GROUP BY job_title ORDER BY COUNT(*) DESC; 查询每个工种的工种名和员工的个数，并且按员工个数进行排序

实现三个表连接

SELECT last_name,department_name,city FROM employees e,departments d,locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id; 查询员工名、部门名和所在的城市

2、非等值连接

查询员工的工资和工资级别

SELECT salary,grade_level FROM employees e,job_grades g WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

3、自连接

这里就是将一个表自己和自己进行关联

查询员工名和上级的名称

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name FROM employees e,employees m WHERE e.manager_id = m.employee_id;

sql99标准

语法

select 查询列表
from 表1 别名【连接类型】
join 表名2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
内连接：inner
外连接
左外：left [outer]
右外：right [outer]
全外：full [outer]
交叉连接：cross

一、内连接

1、等值连接

SELECT last_name,department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id; 查询员工名、部门名
SELECT last_name,job_title FROM employees e INNER JOIN jobs j ON e.job_id = j.job_id WHERE e.last_name LIKE '%e%'; 查询名字中包含e的员工名和工种名（添加筛选）
SELECT city,COUNT(*) AS 部门个数 FROM departments d INNER JOIN locations l ON d.location_id = l.location_id GROUP BY city HAVING COUNT(*) > 3; 查询部门个数 > 3 的城市名和部门个数（添加分组+筛选）
SELECT COUNT(*),department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id GROUP BY department_name; 查询每个部门的员工个数
SELECT COUNT(*),department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id GROUP BY department_name HAVING COUNT(*) > 3 ORDER BY COUNT(*) DESC; 在上面的结果基础上筛选员工个数 > 3 的记录，并排序

小结

在sql99标准中，可以添加排序、分组、筛选
inner可以省略
筛选条件在where后面，连接条件放在on后面，提高了分离性，便于阅读
inner join 连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

2、非等值连接

3、自连接

二、外连接

应用场景：用于查询一个表有，另一个表没有的记录

特点：

1、外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示NULL（外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录）

2、左外连接，left join 左边是主表，右外连接，right join 右边的是主表

3、左外和右外交换两个表的顺序，可以实现同样的效果

4、全外连接 = 内连接结果 + 表1中有但表2中没有的 + 表2中有但表1中没有的

查询男朋友不在男神表的女神名

SELECT b.name bo.* FROM beauty b LEFT JOIN boys bo ON b.boyfriend_id = bo.id WHERE bo.id IS NULL;

三、交叉连接

交叉连接其实就是笛卡尔乘积的结果

SELECT b.*,bo.* FROM beauty b CROSS JOIN boys bo;

6.4.9、子查询

where和having后面

1、标量子查询

2、列子查询（多行子查询，一列多行）

3、行子查询（结果集一行多列或多行多列）

select后面

from后面

exists后面（相关子查询）

6.4.10、分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求

语法：

SELECT b.*,bo.* FROM beauty b CROSS JOIN boys bo;
select 查询列表 from 表名 limit offset,size;
offset：要显示条目的起始索引（起始索引从0开始）
size：要显示的条目个数
limit 语句放在查询语句的最后
公式：
要显示的页数page，每页的条目数：select 查询列表 from 表名 limit (page-1)*size,size;

查询前5条员工信息

SELECT * FROM employees LIMIT 0,5; 如果起始位置就是从0开始，可以使用SELECT * FROM employees LIMIT 5;

查询第11条到第25条

SELECT * FROM employees LIMIT 10,15;

有奖金的员工信息，并且工资较高的前10名显示出来

SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10;

6.4.11、union联合查询

union 联合合并：将多条查询语句的结果合并成一个结果

语法

查询语句1
union
查询语句2
union
...

应用场景：要查询的结果来自多个表，且多个表没有直接的连接关系，查询的信息一致时

特点

1、要求多条查询语句的查询列表是一致的

2、要求多条查询语句每一列的类型和顺序最好一致

3、union关键字默认是去重，如果使用union all 可以包含重复项

查询部门编号 > 90 或邮箱包含a的信息