DQL语言的学习（1）

最新推荐文章于 2024-11-01 14:31:36 发布

奋斗的小丸子lxj

最新推荐文章于 2024-11-01 14:31:36 发布

阅读量149

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/qq_41523205/article/details/105515140

版权

DQL语言的学习（1）

基础查询
条件查询
排序查询
常见函数

基础查询

#进阶1：基础查询
/*
语法：
select 查询列表 from 表名
特点：
1.查询列表可以是：表中的字段、常量值、表达式、函数
2.查询的结果是一个虚拟的表格
*/
USE myemployees;
#1.查询表名中的单个字段
SELECT last_name FROM employees;

#2.查询表中的多个字段
SELECT last_name,salary,email FROM employees;

#3.查询表中所有字段
#方式一：(格式化F12)
SELECT 
  `first_name`,
  `last_name`,
  `email`,
  `phone_number`,
  `job_id`,
  `salary`,
  `commission_pct`,
  `manager_id`,
  `department_id`,
  `hiredate` 
FROM
  employees ;
#方式二：
SELECT *FROM employees;

#4.查询常量值
SELECT 100;
SELECT 'john';

#5.查询表达式
SELECT 100%98;

#6.查询函数
SELECT VERSION();

#7.起别名
/*
①：便于理解
②：如果要查询的字段有重名的情况，使用别名可以区分开来
*/
#方式一：使用as
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
#方式二：使用空格
SELECT last_name 姓,first_name 名 FROM employees;
#案例：查询salary,显示结果为 out put
SELECT salary AS "out put" FROM employees;

#8.去重：(distinct)
#案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;

#9.+号作用：（concat）
/*
java中的+号：
①：运算符，两个操作数都为数值型
②：连接符，只要有一个操作数为字符串
mysql中的+号：
仅仅只有一个功能：运算符
select 100+99;     (199) 两个操作数都为数值型，则做加法运算
select '123'+90;   (213) 只要其中一方为字符型，试图将字符型数值转换成数值型
			 如果转换成功，则继续做加法运算
select 'join'+90;   (90) 如果转换失败，则将字符型数值转换成0
select null+10;    (null)只要其中一方为null,则结果肯定为null
*/
#案例：查询员工名和姓连接成一个字段，并显示为姓名
SELECT CONCAT('a','b','c') AS 结果;
SELECT
	CONCAT(last_name,first_name) AS 姓名
FROM 
        employees;
#例题：显示出表employees的部分列，各个列之间用逗号连接，列头显示成out_put
SELECT 
	IFNULL(commission_pct,0) AS 奖金率,
	commission_pct
FROM 
	employees;
#---------------------------------------------------------------
SELECT 
	CONCAT(`first_name`,',',`last_name`,',',`job_id`,',',IFNULL(commission_pct,0)) AS out_put
FROM 
	employees;

条件查询

#进阶2：条件查询
/*
语法：
	select 
		查询列表
	from
		表名
	where
		筛选条件;
分类：
	一、按条件表达式筛选
	条件运算符：> < = != <> >= <=
	二、按逻辑表达式筛选
	逻辑运算符：
	作用：用于连接条件表达式
		&& || ！
		and or not
	&&和and：两个条件都为true，结果为true，反之为false
	||或or：只要有一个条件为true，结果为true，反之为false
	！或not: 如果连接条件本身为false，结果为true，反之为false
	三、模糊查询
		like 
		between and 
		in
		is null
*/
#一、按条件表达式筛选
#案例一：查询工资>12000的员工信息
SELECT * FROM employees WHERE salary>12000;
#案例二：查询部门编号不等于90的员工名和部门编号
SELECT last_name,department_id FROM employees WHERE department_id<>90;


#二、按逻辑表达式查询
#案例一：查询工资在10000到20000之间的员工名、工资以及奖金
SELECT 
	last_name,salary,commission_pct
FROM 
	employees
WHERE 
	salary>=10000 AND salary<=20000;
#案例二：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT
	*
FROM 
	employees
WHERE 
	department_id<90 AND department_id>110 OR salary>15000;



#三、模糊查询
/*
like        
特点：
①：一般和通配符搭配使用
	通配符：
	%任意多个字符，包含0个字符
	_任意单个字符
	

between and
in
is null | is not null
*/

#1.like
#案例一：查询员工名中包含字符a的员工信息
SELECT 
	*
FROM 
	employees
WHERE
	last_name LIKE '%a%';
#案例二：查询员工名中第三个字符为n，第五个字符为l的员工名和工资
SELECT
	last_name,salary
FROM 
	employees
WHERE
	last_name LIKE '__n_l%';
#案例三：查询员工名中第二个字符为_的员工
SELECT
	last_name
FROM 
	employees
WHERE 
	#last_name like '_\_%';  
	last_name LIKE '_$_%' ESCAPE '$';
	
#2.between and
/*
①：使用between and可以提高语句的简洁度
②：包含临界值
③：两个临界值顺序不要颠倒
*/
#案例1：查询员工编号在100到120之间的员工信息
SELECT 
	*
FROM 
	employees
WHERE
	employee_id BETWEEN 100 AND 120;
	
#3.in
/*
含义：判断某字段的值是否属于in列表中的某一项
特点：
	①使用in提高了语句简洁度
	②in列表的值类型必须一致或兼容
*/
#案例：查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT
	last_name,job_id
FROM 
	employees
WHERE 
	job_id IN('IT_PROG','AD_VP','AD_PRES');

#4.is null
/*
=或<>不能用于判断null值
is null 或is not null 可以判断null值
*/
#案例一：查询没有奖金的员工名和奖金率
SELECT
	last_name,commission_pct
FROM 
	employees
WHERE
	commission_pct IS NULL;
	
#安全等于<=>
#案例一：查询没有奖金的员工名和奖金率
SELECT
	last_name,commission_pct
FROM 
	employees
WHERE
	commission_pct <=> NULL;
#案例二：查询工资为12000的员工信息
SELECT
	*
FROM 
	employees
WHERE
	salary <=> 12000;

#is null pk <=>
#is null:仅仅可以判断null值，可读性较高
#<=>:既可以判断null值，又可以判断普通数值可读性较低

排序查询

#进阶3：排序查询
/*
语法：
	select 查询列表
	from 表
	【where 筛选条件】
	order by 排序列表【asc|desc】 
特点：1.asc代表的是升序，desc代表的是降序
	如果不写，默认是升序
      2.order by子句中可以支持单个字段，多个字段、表达式、函数、别名
      3.order by子句一般是放在查询语句的最后面，limit子句除外
*/
#案例1：查询员工信息，要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;   
SELECT * FROM employees ORDER BY salary ASC;    
#案例2：查询部门编号>=90的员工信息，按入职时间的先后进行排序【添加筛选条件】
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY  hiredate ASC;
#案例3：按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
#案例4：按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC;
#案例5：按姓名长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH(last_name)字节长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;
#案例6:查询员工信息，要求先按工资升序，再按员工编号降序【按多个字段排序】
SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;

常见函数

#进阶4：常见函数
/*
功能：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1.隐藏了实现细节  2、提高代码的重用性
调用：select 函数名(实参列表)【from表】;
特点：①函数名②函数功能
分类：
	1.单行函数
	  如：concat、length、ifnull等
	2.分组函数
	功能：做统计使用，又称为统计函数、聚合函数、组函数
	
常见函数总结：
	     字符函数：
	     length
	     concat
	     substr
	     instr
	     trim
	     upper
	     lower
	     lpad
	     rpad
	     replace
	     
	     数学函数：
	     round
	     coil
	     floor
	     truncate
	     mod
	     
	     日期函数：
	     now
	     curdate
	     curtime
	     year
	     month
	     monthname
	     day
	     hour
	     minute
	     second
	     str_to_data
	     data_format
	     
	     其他函数：
	     version
	     database
	     user
	     
	     控制函数：
	     if
	     case
	     
	     
*/
#一、字符函数
#length：获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha'); #utf8字符集汉字占三个字节
SHOW VARIABLES LIKE '%char%'

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

#3.upper、lower
SELECT UPPER('john');
SELECT LOWER('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(first_name,2))) 姓名 FROM employees;

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

#6.trim
SELECT LENGTH(TRIM('     张翠山      ')) AS out_put;   #9
SELECT TRIM('a' FROM 'aaaaaaa张aaaaaaaaaaaaaaa翠山aaaaaaaaaaa') AS out_put;   #张aaaaaaaaaaaaaaa翠山

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

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

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


二、数学函数
#round 四色五入
SELECT ROUND(1.65);  #2
SELECT ROUND(-1.55);  #-2
SELECT ROUND(1.567,2);  #1.57
#ceil 向上取整,返回>=改参数的最小整数
SELECT CEIL(1.52);  #2
SELECT CEIL(-1.52);  #-1
#floor 向下取整,返回<=改参数的最大整数
SELECT FLOOR(9.99); #9
#truncate 截断
SELECT TRUNCATE(1.699999,1); #1.6
#mod取余   mod(a,b):a-a/b*b
SELECT MOD(10,3);  #1
SELECT MOD(-10,-3);  #-1


#三、日期函数
#now 返回当前系统日期+时间
SELECT NOW();     #2020-04-14 14:53:06
#curdate 返回当前系统日期，不包含时间
SELECT CURDATE();   #2020-04-14
#curtime 返回当前系统时间，不包含日期
SELECT CURTIME();   #14:55:08
#可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;	   #2020
SELECT YEAR('1998-03-20') 年;  #1998
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;  #4
SELECT MONTHNAME(NOW()) 月;  #April
#str_to_data   将字符通过指定格式转换成日期
SELECT STR_TO_DATE('1998-2-22','%Y-%c-%d') AS out_put;  #1998-02-22
#查询入职日期为1992-4-3的员工信息
SELECT	* FROM employees WHERE hiredate=STR_TO_DATE('4-3 1992','%c-%d %Y');
#date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;   #20年04月14日
#查询有奖金的员工名和入职日期（xx月/xx日 xx年）
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年')
FROM employees;

#四、其他函数
SELECT VERSION();
SELECT DATABASE();  #myemployees
SELECT USER();     #root@localhost

#五、流程控制函数
#1.if函数：if else的效果
SELECT IF(10<5,'大','小'); #小
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') AS 备注；
FROM employees;
#2.case函数
#使用一：switch case的效果
/*
java中
switch(变量或表达式){
	case 常量1：语句1；break;
	...
	default:语句n；break
}
mysql中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2；
....
else 要显示的值n或语句n；
end
*/
/*
案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资 
*/
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
/*
java中；
if(条件1){
     语句1；
}else if(条件2){
     语句2；
}
....
else{
      语句n；
}
mysql中：
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函数的单独介绍
一般使用count（*）统计行数
5.和分组函数一同查的字段要求是group by后的字段
*/
#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) 平均,MIN(salary) 最低, MAX(salary) 最高, COUNT(salary)个数
FROM employees;

#2.和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

#3.count函数单独介绍哦
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;

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

#4.和分组函数一同查询的字段有限制
SELECT AVG(salary),employee_id FROM employees;  #不对