MySQL基础学习笔记_Day02

最新推荐文章于 2024-08-21 15:00:00 发布
最新推荐文章于 2024-08-21 15:00:00 发布
阅读量174 收藏
点赞数
分类专栏： SQL 文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_41561168/article/details/120015375
版权
本文详细介绍了MySQL中的DQL语言，包括排序查询、常用函数如字符、数学、日期和分组函数的使用，以及分组查询和连接查询的操作。通过案例展示了如何进行多字段排序、使用IF和CASE函数、统计函数SUM、AVG、COUNT等，并讲解了避免笛卡尔乘积的方法。内容涵盖数据筛选、分组统计和多表联合查询，适合MySQL初学者学习。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

MySQL 基础学习_Day02

1.前言

2.DQL语言的学习（二）

在这里插入图片描述

2.1 排序查询

特点：
① 使用 order by 排序的字段或表达式 asc | desc ; asc代表的是升序，可省略，desc代表的是降序
② order by子句可以支持 单个字段、别名、表达式、函数、多个字段
③ order by子句在查询语句的最后面，除了limit子句

2.1.1 按单个字段排序

  • 案例：查询员工信息，要求工资从高到底排序

    SELECT * FROM employees ORDER BY salary DESC;

2.1.2 添加筛选条件再排序

  • 案例：查询部门编号>=90的员工信息，并按员工编号降序
    SELECT *
FROM employees
WHERE department_id>=90
ORDER BY employee_id DESC;

2.1.3 按表达式排序

  • 案例：查询员工信息 按年薪降序
    SELECT *,salary*12*(1+IFNULL(commission_pct,0))
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

2.1.4 按别名排序

  • 案例：查询员工信息 按年薪升序
    SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 ASC;

2.1.5 按函数排序

  • 案例：查询员工名，并且按名字的长度降序
    SELECT LENGTH(last_name),last_name 
FROM employees
ORDER BY LENGTH(last_name) DESC;

2.1.6 按多个字段排序

  • 案例：查询员工信息，要求先按工资降序，再按employee_id升序
    SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC;

2.2 常见函数

在这里插入图片描述
2.2.1 字符函数

#1.length 获取参数值的字节个数
#utf-8一个汉字代表3个字节,gbk为2个字节
SELECT LENGTH('john'); #4
SELECT LENGTH('张三丰hahaha'); #15
SHOW VARIABLES LIKE '%char%' #查看系统变量

#2.concat 拼接字符串
#xxx_yyy
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;

#3.upper、lower
SELECT UPPER('john'); #JOHN
SELECT LOWER('joHn'); #john
#示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name))  姓名 FROM employees;

#4.substr、substring
#***注意：索引从1开始******************************
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7)  out_put; #陆展元
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put; #李莫愁

#案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))  out_put
FROM employees;

#5.instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put; #0

#6.trim #默认去掉前后空格
SELECT LENGTH(TRIM('    张翠山    ')) AS out_put; #9
#'aa'参数，去掉前后的'aa'
SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaa翠山aaaaaaaaaaaa')  AS out_put; #a张aaaaaaaa翠山

#7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',2,'*') AS out_put; #殷素
SELECT LPAD('殷素素',10,'*') AS out_put;#*******殷素素

#8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'ab') AS out_put; #殷素素ababababa

#9.replace 替换
SELECT REPLACE('周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put; #赵敏赵敏张无忌爱上了赵敏

2.2.2 数学函数

#round 四舍五入
SELECT ROUND(-1.55); #-2
SELECT ROUND(1.567,2); #1.57；#第二个参数是保留多少位小数

#ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.02); #-1

#floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99); #-10

#truncate 截断
SELECT TRUNCATE(1.69999,1); #1.6；#第二个参数是保留多少位小数

#mod取余
/*
mod(a,b) ：  a-a/b*b
mod(-10,-3):-10- (-10)/(-3)*（-3）=-1
*/
SELECT MOD(10,-3); #1
SELECT 10%3; #1

2.2.3 日期函数
日期函数格式符总结：
在这里插入图片描述

#now 返回当前系统日期+时间
SELECT NOW(); #2021-09-01 11:00:25

#curdate 返回当前系统日期，不包含时间
SELECT CURDATE(); #2021-09-01

#curtime 返回当前时间，不包含日期
SELECT CURTIME(); #11:01:03

#可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年; #2021
SELECT YEAR('1998-1-1') 年; #1998
SELECT MONTH(NOW()) 月; #9
SELECT MONTHNAME(NOW()) 月; #September

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 = '1992-4-3';
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');

查询入职日期为1992–4-3的员工信息结果：
在这里插入图片描述
2.2.4 其他函数

SELECT VERSION(); #8.0.20        #查看Mysql版本 
SELECT DATABASE(); #myemployees  #当前使用数据库
SELECT USER();  #root@localhost  #用户信息

2.2.5 流程控制函数

  • if 函数： if else 的效果

    SELECT IF(10<5,'大','小'); #小

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

    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

    #案例：查询员工的工资的情况
/*如果工资>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;

    在这里插入图片描述

2.3 分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数
在这里插入图片描述

  • 2.3.1 简单的使用

    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;

SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM employees;

SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM employees;

  • 2.3.2 参数支持哪些类型

    #SUM,AVG：使用处理数值型
SELECT SUM(last_name) ,AVG(last_name) FROM employees;#没有报错，但结果没有意义
SELECT SUM(hiredate) ,AVG(hiredate) FROM employees;#同上

#处理任何语言
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT MAX(hiredate),MIN(hiredate) FROM employees;

SELECT COUNT(commission_pct) FROM employees;
SELECT COUNT(last_name) FROM employees;

  • 2.3.3 是否忽略null——是忽略null值

    SELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;
SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees;

SELECT COUNT(commission_pct) FROM employees;
SELECT commission_pct FROM employees;

  • 2.3.4 和distinct搭配

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

  • 2.3.5 count函数的详细介绍

    SELECT COUNT(manager_id) FROM employees; #106 #因为会忽略null
SELECT COUNT(*) FROM employees; #107
SELECT COUNT(1) FROM employees; #107

/*效率：
MYISAM存储引擎下  ，COUNT(*)的效率高
INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些
*/

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

    SELECT AVG(salary),employee_id  FROM employees;
/*
Mysql低版本：可能运行成功，但结果没有意义；
Mysql高版本：会出现错误，运行不成功。
附：
In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'myemployees.employees.employee_id';
this is incompatible with sql_mode=only_full_group_by
*/

2.4 分组查询

筛选条件分两类：
①针对原始表筛选，group by 前使用where
②针对group by后的结果集，group by后使用 having

select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【having 筛选条件】
【order by 排序的字段】;

2.4.1 简单的分组
案例1：查询每个工种的员工平均工资

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

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

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

2.4.2 可以实现分组前的筛选
案例1：查询邮箱中包含a字符的每个部门的最高工资

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

案例2：查询有奖金的每个领导手下员工的平均工资

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

2.4.3 分组后筛选
案例1：查询哪个部门的员工个数>5

#①查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;

#② 筛选刚才①结果
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;

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

SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL #在原始表有的字段，直接用where查询
GROUP BY job_id
HAVING MAX(salary)>12000; #查询出来之后的表，再需要添加条件，则使用having查询

案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资

SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

2.4.4 添加排序
案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序

SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000
ORDER BY m ;

2.4.5 按多个字段分组
案例：查询每个工种每个部门的最低工资,并按最低工资降序

SELECT MIN(salary),job_id,department_id
FROM employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;

2.5 连接查询

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

  • 发生原因：没有有效的连接条件
  • 如何避免：添加有效的连接条件

分类：

  • 按年代分类：
    • sql92标准:仅仅支持内连接
    • sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
  • 按功能分类：
    在这里插入图片描述

注：此处使用girls.sql的数据库，上图星号代表本节内容

#一、sql92标准
#一、等值连接
/*
① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-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 e.last_name,e.job_id,j.job_title
FROM employees  e,jobs j
WHERE e.`job_id`=j.`job_id`;


#3、两个表的顺序是否可以调换
#查询员工名、工种号、工种名

SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;


#4、可以加筛选
#案例：查询有奖金的员工名、部门名
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%';

#5、可以加分组
#案例1：查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;

#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
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`;

#6、可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

#7、可以实现三表连接？
#案例：查询员工名、部门名和所在的城市
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`
AND city LIKE 's%'
ORDER BY department_name DESC;

#二、非等值连接
#案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';

/*
select salary,employee_id from employees;
select * from job_grades;
CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  int,
 highest_sal int);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);

*/

#三、自连接
#案例：查询 员工名和上级的名称
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`;
【MySQL】函数
fight_p的博客
06-09 2885
比如密码在数据库绝对不能是明文保存的。万一表结构泄漏了，用户信息就全部被泄漏了。这里有一个细节mysql对于sql里面涉及核心密码password关键字之类的这个sql语句就不会被保存，不能上翻下翻了密码被变成固定32位字符串，就不用担心密码被泄漏了。但是登录的时候也必须是数据库的摘要密码。除了md5进行保存密码之外，数据库还提供更复杂的密码设定的函数。
MySQL基础学习笔记
最新发布
weixin_67732682的博客
12-27 1027
数据库基础知识：SQL、函数、约束、多表查询、事务
mysql 函数
mengzuchao的专栏
06-13 452
MySQL聚合函数MySQL聚合函数 - 提供最常用的MySQL聚合函数的简要概述。avg()函数 - 计算一组值或表达式的平均值。count()函数 - 计算表中的行数。instr()函数 - 返回子字符串在字符串中第一次出现的位置。sum()函数 - 计算一组值或表达式的总和。min()函数 - 在一组值中找到最小值。max()函数 - 在一组值中找到最大值。group_concat()函数 ...
MySQL 基础学习笔记2
weixin_42167594的博客
06-08 212
InnoDB和MyISAM的使用场合MyISAM储存引擎：由于不支持事物处理，也不支持外键，访问速度比较快，以访问为主优先选择。InnoDB储存引擎：在事务处理上有优势，有事物控制能力，可以保证事物的完整性，还可实现并发控制。这样的需求优选。默认引擎的的更改：在配置文件my.ini文件中可以修改。修改defaultstorage-engine=InnoDB也可以在创建表时修改:engine=MyI...
MySQL(基础篇)
Xiaowenyangnb的博客
05-18 836
函数是指一段可以直接被另一段程序调用的程序或代码。a.概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。b.目的：保证数据库中数据的正确性、有效性和完整性。2.外键约束a.语法①CREATE TABLE 表名(字段名 数据类型,...[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)②。
MySQL课堂笔记-day02.txt
01-24
主要讲解mysql的具体操作以及部分常用指令，适合想初步了解数据库的人学习
MySql学习笔记
weixin_45906989的博客
07-27 1566
MySql学习笔记
mysql学习笔记
12-16
这份“mysql学习笔记”涵盖了MySQL的基础知识，包括但不限于数据库的概念、SQL语言的使用、以及更深入的多表操作。 在“day08入门笔记”中，你可能会学到以下内容： 1. **数据库基本概念**：了解什么是数据库，...
MYSQL的select 学习笔记
09-11
MySQL的SELECT语句是SQL语言中最基础且最重要的部分，它用于从数据库中选取特定的数据。以下是一些关于MySQL SELECT语句的关键知识点： 1. **换行书写**：在编写SQL语句时，虽然换行不会影响语法，但在长查询中使用...
MySql常用函数大全讲解
热门推荐
Evankaka的专栏
08-23 1万+
MySQL数据库中提供了很丰富的函数。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。通过这些函数，可以简化用户的操作。例如，字符串连接函数可以很方便的将多个字符串连接在一起。在这一章中将讲解的内容包括：数学函数字符串函数日期和时间函数条件判断函数系统信息函数加密函数格式化函数 。
MySQL函数
快乐星球
04-12 151
日期函数 获得年月日: select current_date(); 获得时分秒: select current_time(); 获得时间戳: select current_timestamp(); 在日期的基础上加日期: 加10天 select date_add('2017-10-28', interval 10 day); 在日期的基础上减去时间: 减两天 selec...
MySQL高级语句（三）——数据库函数和存储过程
ItookapillinNJ的博客
04-23 556
文章目录数学函数聚合函数字符串函数日期时间函数存储过程概述优点语法 MySQL数据库函数提供了能够实现各种功能的方法，使我们在查询记录时能够更有效的输出。MySQL内建了很多函数，常用的包括数学函数、聚合函数、字符串函数和日期时间函数。 数学函数 数据库内存储的记录，经常要进行一系列的算术操作，所以MySQL支持很多数学函数。 常用的数学函数 数学函数 描述 abs(x) 返回x的绝对值 rand() 返回0到1的随机数 mod(x,y) 返回x除以y以后的余数 power(x,
Mysql——函数
程序员劝退师
02-27 364
函数 进阶4：常见函数 /* 概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名 好处：1、隐藏了实现细节 2、提高代码的重用性 调用：select 函数名(实参列表) 【from 表】; 特点： ①叫什么（函数名） ②干什么（函数功能） 分类： 1、单行函数 如 concat、length、ifnull等 2、分组函数 功能：做统计使用，又称为统计函数...
MySQL 函数
yimeixiaolangzai的博客
08-21 5019
MySQL 函数是服务器端定义的程序，可以接收输入参数，进行处理后返回一个结果值。函数的主要特点是可以在 SQL 语句中直接调用，通常用于计算、数据转换或返回某种特定的结果。与存储过程不同，函数必须返回一个值，并且可以在 SQL 语句的SELECT列表、WHERE子句、ORDER BY子句等位置使用。
MySQL之函数
m0_62604616的博客
07-05 125
目录二、常用函数1)日期函数 2)字符串函数 3、数字函数四、聚合函数（SUM/COUNT/AVG/MAX/MIN）五、合并(union)一、函数定义 第五条SQL语句运行结果： 4和5的运行结果: 运行结果： 三、条件判断函数 运行结果： union的运行结果： union all运行结果：...
MySQL学习笔记：关键概念与操作指令
"MySQL笔记概述及基础操作" MySQL是一种广泛使用的开源关系型数据库管理系统，它以其高效、稳定和易用性而受到中小型企业及个人开发者的青睐。与其他常见数据库系统如Oracle、DB2和SQL Server相比，MySQL是免费且轻...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值