-- 1 查询表中单个字段
SELECT `last_name` FROM `employees`;
-- 2 查询表中多个字段
SELECT `last_name`,`email`,`department_id` FROM `employees`;
# 3.查询表中所有字段
SELECT * FROM `employees`;
# 4. 查询表中的常量值
SELECT 100;
# 5. 查询表达式
SELECT 100%98;
# 6.查询函数
SELECT VERSION();
# 7.起别名
SELECT 100%98 AS 结果;
SELECT `last_name` AS 姓 FROM `employees`;
# 8. 去重复--查询员工表中涉及到的部门编号
SELECT DISTINCT `department_id` FROM `employees`;
# 9.+号的作用--查询员工名和姓连接成一个字段，并显示为 姓名
SELECT CONCAT(`last_name`,`first_name`) AS 姓名 FROM `employees`;


#  (二)条件查询

/*
语法  selcet  查询列表 
	from  表名  
	where 筛选条件
分类
	一、按条件表达式筛选
	条件运算符：<;>;!=;<=;>=;
	二、按逻辑运算符，与或非 and or not  用于连接条件表达式
	三、模糊查询
		like
		between and
		in 
		is null

*/

# 按条件表达式筛选员工信息
# 1。查询工作大于12000的员信息
SELECT * FROM`employees` WHERE`salary`>12000;

# 2.查询部门编号不等于90号员工名和部门编号
SELECT CONCAT(`last_name`,`first_name`) AS 姓名,`department_id` FROM `employees` WHERE `department_id`!=90;
# 3.查询工资在10000到12000之间的员工名,工资和奖金

SELECT `first_name`,`salary`,`commission_pct`
FROM`employees`
WHERE `salary` >10000 AND`salary`<12000;

# 4.查询部门编号不是在90到100之间，或者工资高于15000的员工信息
SELECT *
FROM`employees`
WHERE`department_id`<90 OR `manager_id`>100 OR `salary`>15000;

# 模糊查询
-- like
-- 和通配符搭配使用：% 表示任意多个字符
--		    _下划线表示任意单个字符
-- 查询员工名中第三个字符为n，第5个字符为l的员工名和工资
SELECT `last_name`,`salary` FROM `employees`WHERE `last_name`LIKE ‘__n_l%‘;
-- 查询员工名中包含字符a的员工信息
SELECT * FROM `employees`WHERE `last_name`LIKE ‘%a%‘;-- % 表示任意字符的通配符

-- 查询员工名中第二个字符为_下划线的员工名
SELECT `last_name` FROM `employees`WHERE `last_name`LIKE‘_\_%‘;-- \:表示转义字符
-- ESCAPE:表示指定任意符号为转义字符
SELECT `last_name` FROM `employees`WHERE `last_name`LIKE‘_$_%‘ ESCAPE ‘$‘;

-- between and-- 闭区间
-- 查询员工编号在90到100之间的员工信息
SELECT * FROM `employees`WHERE	`department_id` BETWEEN 90 AND 100;

-- in 判断某字段的值是否属于in列表中某一个，值类型必须统一或兼容，列表值不支持通配符
-- 查询员工工种编号是 AD_PRES，AD_VP 中的一个员工名和工种编号
SELECT `last_name`,`job_id` FROM`employees`WHERE `job_id` IN (‘AD_PRES‘,‘AD_VP‘);

-- is null
-- 查询摩尔没有奖金的员工名和奖金率
SELECT `last_name`,`commission_pct` FROM`employees`WHERE `commission_pct`IS NULL;

-- 查询摩尔有奖金的员工名和奖金率
SELECT `last_name`,`commission_pct` FROM`employees`WHERE `commission_pct`IS NOT NULL;


# 安全等于<=>,是判断等于，用的较少

-- 查询摩尔没有奖金的员工名和奖金率
SELECT `last_name`,`commission_pct` FROM`employees`WHERE `commission_pct`<=> NULL;


-- 查询员工号为176的员工的姓名和部门编号，年薪

SELECT `last_name`,`department_id`,`salary`*12*(1+IFNULL(`commission_pct`,0)) 
AS 年薪 FROM `employees`;

# 排序查询
/*

语法 

select  
from
[where]
order by [asc, desc] 


order by 中可以支持单个字段，多个字段，表达式，函数，别名
order by 一般是放在查询语句的最后面，limit字句除外


*/


-- 查询员工信息，要求工资从高到低
SELECT* FROM`employees` ORDER BY`salary` DESC;

-- 查询部门编号大于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 年薪 DESC;

-- 按照姓名的长度显示员工的姓名和工资 [按函数排序]
SELECT LENGTH(`last_name`)  AS 姓名长度,`last_name`,`salary` FROM `employees` ORDER BY 姓名长度 DESC;


-- 查询员工信息，要求先按照工资排序，在按照员工部门编号排序[按多个字段排序]

SELECT *FROM `employees`ORDER BY `salary` ASC,`department_id` DESC;


# 常见函数

/*
分类
单行函数-- concat length 等
分组函数-- 做统计用，

*/
-- 字符函数
-- length
SELECT LENGTH(‘john‘)

-- 查看数据库的字符集
SHOW VARIABLES LIKE‘%char%‘;

-- concat -- 拼接字符串
SELECT CONCAT(`last_name`,`first_name`) FROM `employees`;
-- upper,lower
SELECT UPPER(‘john‘);
SELECT LOWER(‘JOHN‘);

-- substr,substring-- 索引从1开始，到结束

SELECT SUBSTR(‘小龙女金庸‘,4);-- 金庸

SELECT SUBSTR(‘小龙女金庸‘,1,3);-- 小龙女

-- instr --子串在大串中的起索引[第一次]
SELECT INSTR(‘小龙女金庸‘,‘金庸‘);-- 4
-- trim -- 去空格[前后]

SELECT TRIM(‘   小龙女金庸   ‘);

SELECT TRIM(‘a‘FROM‘aaaa 小龙aaaaaaa女金庸   ‘);
-- lpad-- 左边填充指定长度的符号
SELECT LPAD(‘张三丰‘,10,‘*‘);
-- rpad-- 右边填充指定长度的符号
SELECT RPAD(‘张三丰‘,12,‘*‘);

-- replace-- 替换

SELECT REPLACE(‘张无忌爱上了周芷若‘,‘周芷若‘,‘赵敏‘);

# 数学函数 
-- round:四舍五入

SELECT ROUND(1.65);-- 2
# ceil :向上取整
SELECT CEIL(1.2);
# floor :向下取整

# truncate-- 截断
SELECT TRUNCATE(1.69,1);

# mod 取余数


# 日期函数
-- now 返回当前的日期
SELECT NOW();

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

SELECT CURDATE();

SELECT CURTIME();-- 返回当前的时间，不包含日期
# 可以获取指定的部分，年，月，日，时，分，秒；

SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());

# str_to_date 将日期字符转换成指定日期格式

SELECT STR_TO_DATE(‘1988-12-13‘,‘%Y-%c-%d‘);
-- date_format 将日期转换成字符

SELECT DATE_FORMAT(NOW(),‘%y年%m月%d 日‘);



# 其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();


# 流程控制函数

-- if函数：if else 的效果；

SELECT `last_name`,`commission_pct`,IF(`commission_pct` IS NULL,‘hehe ‘,‘haha ‘)
FROM`employees`;

-- case 函数
-- 使用1


/*
案例1.
查询员工工资，要求 
部门号=30，显示的工资为1.1倍；
部门号=40，显示的工资为1.2倍；
部门号=50，显示的工资为1.3倍；
其他部门，显示的工资为原工资。


*/

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


-- case 类似于多重if

/*
查询员工的工资请
如果工资大于2000，显示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
SELECT SUM(`salary`)FROM `employees`;
-- avg
SELECT AVG(`salary`)FROM `employees`;
-- max
SELECT MAX(`salary`)FROM `employees`;
-- min
SELECT MIN(`salary`)FROM `employees`;
-- count
SELECT COUNT(`salary`)FROM `employees`;

# 以上分组函数均忽略null值
-- 可以和distinct搭配使用

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

SELECT COUNT(*)FROM employees;-- 统计所有样本数

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


# 分组查询
/*
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`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`;
-- 查询那个部门员工的个数大于2
# 添加分组后的筛选
SELECT COUNT(*) AS 员工个数,department_id
FROM`employees`
GROUP BY `department_id`

HAVING COUNT(*)>2;
-- 查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资
SELECT MAX(salary) ,job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;


-- 查询领导编号大于102的每个领导手下的最低工资大于5000的领导编号是哪个，以及其最低工资
SELECT MIN(salary),`manager_id`
FROM`employees`
WHERE `manager_id`>102
GROUP BY`manager_id`
HAVING MIN(`salary`)>5000;



# 按表达式或函数分组

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

SELECT COUNT(*),LENGTH(`last_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`
WHERE department_id IS NOT NULL
GROUP BY `department_id`,`job_id`
HAVING	AVG(`salary`)>10000
ORDER BY AVG(`salary`) DESC;