数据库基础知识——DQL语言（一）

1.基础查询

语法：
SELECT 要查询的东西
【FROM 表名】;

#查询employees表中所有字段
select * from employees;

#查询employees表中的first_name字段
select first_name from employees;

#查询employees表中的多个字段
select first_name,salary FROM employees;

#查询常量值
select 100+123;
select "zhaoxr"

#查询表达式
select 100*98;

#查询函数
select version();

#起别名
select 98*98 as result;
select last_name as "姓",first_name as "名" from employees;
select last_name "姓",first_name "名" from employees;

#去重
select distinct department_id from employees;

# +的作用
select 100+99;
select "123"+99;
select "zhaoxr"+"linux";
select null+16;

# concat函数
select concat(last_name," ",first_name) "姓名" from employees;

类似于Java中 :System.out.println(要打印的东西);
特点：
① 通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数

2.条件查询

条件查询：根据条件过滤原始表的数据，查询到想要的数据
语法：
select 
	要查询的字段|表达式|常量值|函数
from 
	表
where 
	条件 ;


分类：
一、条件表达式
	示例：salary>10000
	条件运算符：
	> < >= <= = != <>

二、逻辑表达式
示例：salary>10000 && salary<20000

逻辑运算符：

	and（&&）:两个条件如果同时成立，结果为true，否则为false
	or(||)：两个条件只要有一个成立，结果为true，否则为false
	not(!)：如果条件成立，则not后为false，否则为true

三、模糊查询
like
between and
in
is null | is not null

通配符：
%：任意多个字符，包含0个字符
_：单个字符
\：转义字符，"\_"代表字符_

示例：last_name like 'a%'

# 查询工资大于等于12000的员工ID,姓名和薪水
select employee_id,CONCAT(last_name," ",first_name) as "姓名",salary 
from employees
where salary>=12000;

# 查询部分编号不等于90的员工姓名和部门编号
select CONCAT(last_name," ",first_name) as "姓名",department_id
from employees
where department_id!=90;

#查询工资在10000到20000之间的员工ID，姓名和工资
select employee_id,concat(last_name," ",first_name) as "姓名",salary
from employees
where salary>=10000 and salary<=20000;

#查询员工姓中含有a的员工信息
select *
from employees
where last_name like "%a%";

#查询员工姓第三个字母是l,第五个是e的员工信息
select *
from employees
where last_name like "__l_e%"; 

#查询员工姓的第二个字符是_的员工信息
select *
from employees
where last_name like "_\_%";

#查询员工ID在100到200之间的员工信息,包含100和200
select *
from employees
where employee_id between 100 and 200;

#查询工种编号是IT_PROG,PU_CLERK,ST_CLERK的员工信息
select *
from employees
where job_id in("IT_PROG","PU_CLERK","ST_CLERK");

#查询奖金率为null的员工信息
select *
from employees
where commission_pct is null;

3.排序查询

语法：
select
	要查询的东西
from
	表
where 
	条件

order by 排序的字段|表达式|函数|别名 【asc|desc】

#查询员工信息，工资由低到高排列
select *
from employees
order by salary asc;

#查询员工信息，部门编号大于等于90，并且按照入职先后排列
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)) asc;

#查询员工的姓名和工资，按照姓名的长度从小到大排列
select CONCAT(last_name," ",first_name) as "姓名",
			 length(concat(last_name," ",first_name)) as "姓名长度",
			 salary
from employees
order by length(concat(last_name," ",first_name)) asc;

#查询员工信息，先按工资从低到高排名，再按员工编号从低到高排序
select *
from employees
order by salary asc,employee_id asc;

4.常见函数

4.1 单行函数

4.1.1 字符函数

	concat拼接
	substr截取子串
	upper转换成大写
	lower转换成小写
	trim去前后指定的空格和字符
	ltrim去左边空格
	rtrim去右边空格
	replace替换
	lpad左填充
	rpad右填充
	instr返回子串第一次出现的索引
	length 获取字节个数

#length函数|查询员工的姓，以及姓的长度
select last_name,length(last_name)
from employees;

#concat函数|查询员工的姓名
select concat(last_name," ",first_name) as "姓名"
from employees;

#upper函数和lower函数|查询员工的姓名，姓大写，名小写
select concat(upper(last_name)," ",lower(first_name)) as "姓名"
from employees;

#substr截取函数|注意：截取函数从1开始
#返回"i love china"中的"china"
select substr("i love china",8);

#返回"我爱你中国"中"中国"
select substr("我爱你中国",4);

#返回"i love china"中的"love"
select substr("i love china",3,4);

#instr返回子串第一次出现的索引|返回"i love china"中的'c'在第几个位置
select instr("i love china","c");

#trim去空格|删除"  love   "左右的空格
select trim("   love   ");

#trim去除指定的字符|删除"aaaaaloveaaaa"左右的'a'
select trim('a' from "aaaaaaaloveaaaaa");

#lpad填充|使用'0'填充字符"我爱你",总字符数10个，填充结果为"0000000我爱你"
select lpad("我爱你",10,'0');

#replace替换|将"我爱china",替换为"我爱中国"
select replace("我爱china","china","中国");

4.1.2 数学函数

	round 四舍五入
	rand 随机数
	floor向下取整
	ceil向上取整
	mod取余
	truncate截断

#数学函数
#round四舍五入|2.55四舍五入保留整数部分
select round(2.55);

#ceil向上取整，返回>=该数的最小整数|取整2.0001,2.0000
select ceil(2.0001),ceil(2.0000);

#floor向下取整，返回<=该数的最大整数|取整-2.0001,2.0000
select floor(-2.0001),floor(2.0000);

#truncate截断，保留小数几位|1.99999保留两位小数
select truncate(1.99999,2);

#mod取余|10/3的余数
select mod(10,3);

4.1.3 日期函数

	now当前系统日期+时间
	curdate当前系统日期
	curtime当前系统时间
	str_to_date 将字符转换成日期
	date_format将日期转换成字符

#时间函数
#查询当前时间
select now();

#获取指定的年月日，获取当前年份,月份,日
select year(now()) as "年",month(now()) as "月",day(now()) as "日";

#获取员工姓名和入职年份
select concat(last_name," ",first_name) as "姓名",year(hiredate) as "入职年份"
from employees;

#str_to_date将日期格式的字符转换为规定的日期格式，规定的日期格式"2021-04-29"
select str_to_date("2021年4月29日","%Y年%m月%d日"),str_to_date("04/29/2021","%m/%d/%Y");

#date_format将日期转换为自己想要的日期字符|查询员工的姓名和入职时间，入职时间显示为"2021年4月29日"的格式
select concat(last_name," ",first_name) as "姓名",date_format(hiredate,"%Y年%m月%d日") as "日期"
from employees;

4.1.4 流程控制函数

	if 处理双分支
	if(判断条件,真就执行此处，假就执行此处)
	
	case 要判断的字段或者表达式
	when 常量1 then 要显示的值1或者语句1
	when 常量2 then 要显示的值2或者语句2
	。。。。。
	else 要显示的值n或者语句n
	end
	
	case 
	when 条件1 then 要显示的值1或者语句1
	when 条件2 then 要显示的值2或者语句2
	。。。。。
	else 要显示的值n或者语句n
	end

#流程控制函数
#if函数|查询员工的姓，奖金率，如果有奖金，返回有奖金，没有奖金返回没奖金
select last_name,commission_pct,if(commission_pct is null,"没奖金","有奖金") as "奖金"
from employees;

#case|查询员工的姓名，工资，部门ID
     #如果部门=90，salary*1.1;如果部门=100，salary*1.2;如果部门=110，salary*1.3;其它salary不变;并显示
select concat(last_name," ",first_name),
		   department_id,
			 salary as "原始工资",
			 case department_id
			 when 90 then salary*1.1
			 when 100 then salary*1.2
			 when 110 then salary*1.3
			 else salary
			 end as "当前工资"
from employees;

#case|查询员工的姓名，工资
     #如果工资小于10000，显示低工资；
		 #如果工资大于等于10000，小于20000，显示中等工资；
		 #如果工资大于等于20000，显示高工资
select concat(last_name," ",first_name),
			 salary,
			 case 
			 when salary<10000 then "低工资"
			 when salary>=10000 and salary<20000 then "中等工资"
			 when salary>20000 then "高工资"
			 end as "工资区间"
from employees;

4.1.5 其他函数

	version版本
	database当前库
	user当前连接用户

#其它函数
#version查看版本号
select version();

#database查看当前数据库
select database();

#user查看当前用户
select user();

4.2 分组函数/统计函数/聚合函数

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

#分组函数
#简单使用
select sum(salary) as "总工资",
			 max(salary) as "最高工资",
			 min(salary) as "最低工资",
			 avg(salary) as "平均工资",
			 count(salary) as "工资个数"
from employees;

#查询有多少中工资
select count(distinct salary)
from employees;

#查询employees表有多少行
select count(*) as "行数",count(1) as "行数"
from employees;

	特点：
	1、以上五个分组函数都忽略null值，除了count(*)
	2、sum和avg一般用于处理数值型
		max、min、count可以处理任何数据类型
    3、都可以搭配distinct使用，用于统计去重后的结果
	4、count的参数可以支持：
		字段、*、常量值，一般放1

	   建议使用 count(*)

5.分组查询

语法：
select 查询的字段，分组函数
from 表
group by 分组的字段


特点：
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选
		针对的表	位置			关键字
分组前筛选：	原始表		group by的前面		where
分组后筛选：	分组后的结果集	group by的后面		having

4、可以按多个字段分组，字段之间用逗号隔开
5、可以支持排序
6、having后可以支持别名

#查询每个部门的平均工资
select avg(salary),department_id
from employees
group by department_id;

#查询每个工种的最高工资
select max(salary),job_id
from employees
group by job_id;

#查询工资大于10000的每个工种的最高工资
select max(salary),job_id
from employees
where salary>10000
group by job_id;

#查询哪个部门员工的个数大于2
select count(*),department_id
from employees
group by department_id
HAVING count(*)>2;

#按员工的姓名长度分组，查询每一组员工的个数，筛选员工个数大于5的有哪些
select count(*),length(last_name) as "len_name"
from employees
group by length(last_name)
having count(*)>5;

#查询每个部门每个工种的平均工资
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) desc;