MySQL 数据库 day-03

SELECT CONCAT(last_name,‘_’,first_name) 姓名 FROM employees;

③ upper、lower:将字母变大写/小写

SELECT UPPER(‘jogn’);

SELECT LOWER(‘JmOw’);

案例：将姓名变大写，名变小写，然后拼接

SELECT CONCAT(UPPER(last_name),LOWER(first_name)) AS 姓名 FROM employees;

#④ substr、substring：截取字符

注意：索引都是从1开始

#截取从指定索引处开始后面的所有字符

SELECT SUBSTR(‘它是MySQL呀’,4) out_put;

截取从指定索引处指定字符长度的字符

SELECT SUBSTR(‘它是MySQL呀’,3,5) AS out_put;

案例：姓名中首字符大写，其他字符小写，然后用_拼接

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),‘_’,LOWER(SUBSTR(last_name,2)))

FROM employees;

⑤ instr:返回子串第一次出现的索引，如果找不到则返回0

SELECT INSTR(‘java和mysql’,‘mysql’) AS out_put #6

⑥ trim:移除字符串两侧的空白字符(默认)或其他预定义字符

SELECT LENGTH(’ myqsql ') #12

SELECT LENGTH(TRIM(’ mysql ')) AS out_put; #5

SELECT TRIM(‘a’ FROM(‘aaaaaMYSaaalaaa’)) AS out_put; #MYSaaal

⑦ **lpad:用指定的字符实现左填充指定长度 最终长度为指定长度 超过指定长度从右边截取

rpad:用指定的字符实现右填充指定长度 最终长度为指定长度 超过指定长度从右边截取**

SELECT LPAD(‘MYsql’,10,‘*’) AS out_put;

SELECT LPAD(‘MYsql’,3,‘*’) AS out_put;

SELECT RPAD(‘MYsql’,3,‘*’) AS out_put;

⑧ repalce:将指定字符替换为指定字符

SELECT REPLACE(‘我爱java’,‘java’,‘mysel’) out_put;

2、数学函数

① round:四舍五入

SELECT ROUND(1.65); #2

SELECT ROUND(1.49); #1

SELECT ROUND(-2.59); #-3

SELECT ROUND(1.5978,3); #1.598

② ceil:向上取整,返回不小于该参数的最小整数

SELECT CEIL(1.0002); #2

SELECT CEIL(-3.58); #-3

③ floor:向下取整,返回不大于该参数的最小整数

SELECT FLOOR(9.56); #10

SELECT FLOOR(-9.90); #-10

④** truncate:截断,保留指定小数**

SELECT TRUNCATE(1.6999,1); #1.6

⑤ mod取余

MOD(a,b); #结果：a-a/b*b

#相当于%

SELECT MOD(10,3); #1

SELECT MOD(10,-3); #1

SELECT MOD(-10,-3); #-1

SELECT MOD(-10,3); #-1

3、日期函数

now:返回当前系统日期+时间

SELECT NOW();

curdate:返回当前系统日期,不返回时间

SELECT CURDATE();

curtime:返回当前系统时间,不返回日期

SELECT CURTIME();

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

SELECT YEAR(NOW()) 年; #2021

SELECT MONTH(‘1998-10-1’) 月; #10

SELECT MONTHNAME(‘1997-10-5’) 月; #OCtober

SELECT YEAR(hiredate) 年 FROM employees;

str_to_date:将字符串通过指定的格式转换成日期

SELECT STR_TO_DATE(‘1998-3-2’,‘%Y-%c-%d’) AS out_put; #1998-03-02

#查询入职日期为 1992-4-3 的员工信息

SELECT * FROM employees WHERE hiredate=STR_TO_DATE(‘4-3 1992’,‘%m-%d %Y’);

#date_format 将日期转换成字符

#查询有奖金的员工名和入职日期（**月/**日 **年）

SELECT last_name,

DATE_FORMAT(hiredate,‘%m月/%d日 %y年’) AS 日期

FROM employees

WHERE commission_pct IS NOT NULL;

4、其他函数

SELECT VERSION(); #当前mysql版本

SELECT DATABASE(); #查看当前库

SELECT USER(); #当前用户

5、流程控制函数

① if函数：if else 的效果

SELECT IF(10>5,‘大’,‘小’);

SELECT last_name,commission_pct,IF(commission_pct IS NULL,‘无奖金’,‘有奖金’)

FROM employees;

② **case函数：

使用一：switch case的效果

语法：

case 要判断的字段或表达式

when 常量1 then 要显示的值1或语句1;

when 常量2 then 要显示的值2或语句2;

……

else 要显示的值n或语句n;

end**

_案例：查询员工的工资,要求

部门号=30,工资为1.1倍

40， 1.2

50 1.3

否则 1_

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;

**使用二：类似于 多重if

语法：

case

when 条件1 then 要显示的值1或语句1;

when 条件2 then 要显示的值2或语句2;

……

else 要显示的值n或者语句n;

end**

_案例：

查询员工的工资的情况

如果>20000,显示A级别

>15000,B

>10000,C

否则,D_

SELECT salary,

CASE

WHEN salary>20000 THEN ‘A’

WHEN salary>15000 THEN ‘B’

WHEN salary>10000 THEN ‘C’

ELSE ‘D’

END AS 工资级别

FROM employees;

四、分组函数

---

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

分类： sum 求和

avg 平均值

max 最大值

min 最小值

count 计算个数

**特点：

1、sum avg 只用来处理数值型

max min count 可以用来处理所有类型

2、以上分组函数都忽略null值

3、和distinct搭配

4、count(*) 一般用来统计行数

5、和分组函数一同查询的字段要求是group by后的字段**

1、简单使用

SELECT SUM(salary) FROM employees;

SELECT MAX(salary) FROM employees;

SELECT MIN(salary) FROM employees;

SELECT AVG(salary) FROM employees;

SELECT COUNT(salary) FROM employees;

SELECT SUM(salary) 和,MAX(salary) 最大,MIN(salary) 最小,AVG(salary) 平均值

FROM employees;

2、和distinct搭配

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

3、count函数的详细介绍

count() 一般用来统计行数*

SELECT COUNT(*) FROM employees; #107 统计行数

SELECT COUNT(1) FROM employees; #107 统计1的个数 即统计行数

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

SELECT AVG(salary),employee_id FROM employees; #报错

五、分组查询

---

**语法：

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

from 表

[where 筛选条件]

group by 分组的列表

[order by 子句]**

**注意:

查询列表必须特殊,要求是分组函数和group by后出现的字段**

**特点：

1、分组查询终的筛选条件分为两类

数据源　　　　　　 位置 　　　　　　　　关键字

分组前筛选　原始表 　　　　　 group by子句的前面 　　 where

分组后筛选　分组后的结果表 　group by子句的后面 　　　having**

①分组条件做条件肯定是放在having子句中

②能用分组前筛选的，就优先考虑分组前筛选

2、group by子句支持单个字段分组、多个字段分组（多个字段用逗号隔开，没有顺序要求），表达式或函数（用的较少）

**3、也可以添加排序（排序放在整个分组查询的最后）

#案例:查询每个部门的平均工资**

SELECT AVG(salary),department_id

FROM employees

GROUP BY department_id;

案例：查询每个工种的最高工资

SELECT MAX(salary),job_id

FROM employees

GROUP BY job_id;

案例：查询每个位置上的部门个数

SELECT COUNT(*),location_id

FROM departments

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;

添加分组后的筛选条件

_案例1：查询哪个部门的员工个数>2

①查询每个部门的员工个数

②根据①的结果进行筛选，查询哪个部门的员工个数>2_

SELECT COUNT(*),department_id

FROM employees

GROUP BY department_id

HAVING COUNT(*)>2;

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

_①查询每个工种有奖金的员工的最高工资

②根据①的结果进行筛选，查询哪个员工的最高工资>12000_

SELECT MAX(salary),job_id

FROM employees

WHERE commission_pct IS NOT NULL

GROUP BY job_id

HAVING MAX(salary>12000);

_案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号以及其最低工资

①查询领导编号>102的每个领导手下的最低工资_

SELECT MIN(salary),manager_id

FROM employees

WHERE manager_id>102

GROUP BY manager_id

HAVING MIN(salary)>5000;

按表达式或函数分组

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

#①查询每个长度的员工个数

#②查询哪一组的员工个数大于5_

SELECT COUNT(*),LENGTH(last_name) len_name

FROM employees

GROUP BY len_name;

HAVING COUNT(*)>5;

按多个字段分组

案例：查询每个部门每个工种的员工的平均工资

SELECT AVG(salary),department_id,job_id

FROM employees

GROUP BY department_id,job_id;

添加排序

案例：查询部门编号部位mull每个工种的员工平均工资>10000的，并按平均工资从小到大排序

SELECT AVG(salary),department_id,job_id

FROM employees

WHERE department_id IS NOT NULL

GROUP BY department_id,job_id

HAVING AVG(salary)>10000

ORDER BY AVG(salary) DESC;

六、连接查询

---

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1有m行，表2有n行，结果：m*n行

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

分类：

按年代分类：

sql92标准:仅仅支持内连接

sql99标准[推荐]：支持内连接+外连接(左外+右外)+交叉连接

按功能分类：

内连接：

• 等值连接

• 非等值连接

• 自连接

外连接：

• 左外连接

• 右外连接

• 全外连接

• 交叉连接

SELECT * FROM beauty;

SELECT * FROM boys;

SELECT NAME,boyName FROM boys,beauty

WHERE beauty.boyfriend_id=boys.id;

(一)、sq192标准

1、等值连接

_**① 多表等值连接的结果为多表的交集部分

② n表连接，至少需要n-1个连接条件

③ 一般需要为表名起别名

④ 多表的顺序没有要求

⑤ 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选**_

(1)、简单使用

案例1：查询女神名字和对应的男神名

SELECT NAME,boyName

FROM boys,beauty

WHERE beauty.boyfriend_id=boys.id;

案例2：查询员工名和对应的部门名

SELECT last_name,department_name

FROM employees,departments

WHERE employees.department_id=departments.department_id;

(2)、为表起别名

_**好处： ①提高语句的简洁度

②区分多个重名的字段**_

注意：如果为表起了别名，则查询的字段就不能再使用原来的表名去限定

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

SELECT last_name,e.job_id,job_title #这里只能用e.,使用employess.报错

FROM employees AS e,jobs j

WHERE e.job_id=j.job_id;

(3)、可以加筛选

案例1：查询有奖金的员工名和部门名

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;

案例2：查询城市名中第二个字符为o的部门名和城市名

SELECT department_name,city

FROM departments d,locations l

WHERE d.location_id=l.location_id

AND city LIKE ‘_o%’;

(4)、加分组

案例1：查询每个城市的部门个数

SELECT COUNT(*) 个数,city

FROM departments d,locations l

WHERE d.location_id=l.location_id

GROUP BY city;

案例2：查询有奖金的每个部门的部门名和领导编号和该部门的最低工资

SELECT department_name,e.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;

(5)、加排序

案例：查询每个工种的工种名和员工的个数，并且按照员工个数降序