3.18学习总结

1.sql条件查询练习回顾+继续

#条件查询
/*
语法
select 查询列表 from 表名 where 筛选条件；

分类：一.按条件表达式筛选
        条件运算符：> < = <> != >= <=
     
      二.按逻辑表达式筛选，与或非
         逻辑运算符： 用于链接条件表达式
                     && || ！
                    and or not
                    
      三.模糊查询
        like 
        between and
        in
        is null
*/
#1.按条件表达式筛选

#案例一.查询员工工资>12000的员工信息
SELECT * FROM employees WHERE salary>12000;

#案例二，查询部门编号不等与90号的员工名和部门编号
SELECT `first_name`,`last_name`,`department_id` FROM employees WHERE department_id<>90;

#2.按逻辑表达式筛选
#案例1：查询工资在1万到两万之间的员工名，工资和奖金
SELECT `first_name`,`last_name`,`salary`,`commission_pct`
FROM employees
WHERE salary >= 10000 AND salary <= 20000;

#案例2，查询部门编号不是在90到110之间，或工资高于15000的员工信息
#方法一
SELECT * FROM employees
WHERE salary > 15000 OR department_id  <90 OR department_id > 110;
#方法二
SELECT * FROM employees
WHERE salary > 15000 OR NOT(department_id  >=90 AND department_id <= 110);

#3.模糊查询
/*
like
between and 
is null|is not null
一般和通配符搭配使用，% ：任意多个字符（包含0）
                      _ : 任意单个字符
*/
#like

#案例1：查询员工名中包含字符a的员工信息,字符型必须用单引号加通配符%，‘%a%'
SELECT * FROM employees
WHERE last_name LIKE '%a%';

#案例2.查询员工名中第三个字符为e，第五个字符为a的员工名和工资
SELECT first_name,last_name,salary
FROM employees
WHERE first_name LIKE '__e_a%'

#案例3.查询员工名中第二个字符为_的员工名-------转译\ 或者用escape ,\_等于把通配符_变成了普通字符串
#方法一
SELECT first_name,last_name,salary
FROM employees
WHERE last_name LIKE '_\_%';

#方法二
SELECT first_name,last_name,salary
FROM employees
WHERE last_name LIKE '_*_%' ESCAPE '*';

#between and 
/*
包含临界值
需要遵守从小到大的顺序
*/
#案例1；查询员工编号在100到120之间的信息
SELECT * FROM employees
WHERE employee_id BETWEEN 100 AND 120;

#in
/*
含义：判断某字段的值是否属于in列表中的某一项
特点：or的极速版
      in列表的值类型必须统一或兼容
      不支持通配符
*/
#案例，查询员工的工种编号是 IT_PROG，AD_VP，AD_PRES的员工名和工种编号
#方法一
SELECT first_name,job_id 
FROM employees
WHERE job_id= 'IT_PROG' OR job_id='AD_VP' OR job_id='AD_PRES';
 
#方法二
SELECT first_name,job_id 
FROM employees
WHERE job_id IN( 'IT_PROG' ,'AD_VP' ,'AD_PRES');

#is null、is not null
/*
NULL 相关的不能用=
     需要用is，固定搭配
*/
#案例1；查询没有奖金的员工名和奖金率
SELECT first_name,commission_pct
FROM employees
WHERE commission_pct IS NULL;

#案例2；查询有奖金的员工名和奖金率
SELECT first_name,commission_pct
FROM employees
WHERE commission_pct IS  NOT NULL;

#安全等于 <=>
/*
<=>是=和is的合体
*/
#案例1；查询没有奖金的员工名和奖金率
SELECT first_name,commission_pct
FROM employees
WHERE commission_pct <=> NULL;

#案例二；查询工资为12000的员工信息
SELECT *
FROM employees
WHERE salary <=> 12000;

#特例：查询员工号为176的员工姓名和部门号和年薪
SELECT first_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
WHERE`employee_id`=176;

#练习题目
#1.查询没有奖金，且工资小于18000的salary，last_name
SELECT salary,last_name
FROM employees
WHERE commission_pct IS NULL AND salary < 18000;

#2.查询employees表中，job_id不为‘IT’或者工资12000的员工信息
SELECT *
FROM employees
WHERE job_id <> 'IT' OR salary =12000;

#3查询departments表中内容
DESC departments;

#4.查询部门departments表中涉及到了哪些位置编号
SELECT DISTINCT location_id FROM departments;

2.sql排序查询练习

#排序查询
/*
语法
select 查询列表
from 表
【where 筛选条件】
order by 排序列表【asc(低到高)|desc（高到低）】

特点
：不写的话，默认升序asc
  支持单个字段，多个字段，表达式，函数，别名
  order 不用子句一般放在最后面
*/
 #案例1：查询员工信息，要求工资从高到底排序
 SELECT * FROM employees ORDER BY salary DESC;、、
 
 #案例2：查询部门编号>=90的员工信息，按入职时间的先后进行排序。
 SELECT * FROM employees
 WHERE department_id >= 90
 ORDER BY hiredate;
 
 #案例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；按姓名的长度显示员工信息【按函数排序】
 #长度length（）
 SELECT * FROM employees
 ORDER BY LENGTH(CONCAT(first_name,last_name));
 
 #案例6查询员工信息，要求先按工资升序，再按员工编号降序【按多个字段排序】
 SELECT * FROM employees
 ORDER BY salary ASC,employee_id DESC;
 
 #测试1 查员工的姓名和部门号和年薪，按年薪降序，按姓名升序
 SELECT CONCAT(first_name,last_name) AS 姓名,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
 FROM employees
 ORDER BY 年薪 DESC,LENGTH(姓名) ASC;
 
#测试2 选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT CONCAT(first_name,last_name) AS 姓名,salary
FROM employees
WHERE salary < 8000 OR salary >17000
ORDER BY salary DESC;

#查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT * FROM employees
WHERE email LIKE('%e%')
ORDER BY LENGTH(email) DESC,department_id;

3.sql常见函数练习

#常见函数
/*
概念：将一组逻辑语句封装在方法体中，对外暴露方法名
好处；隐藏了实现细节，提高代码的重要性
调用；select 函数名（实参列表）from 表;
分类：
1.单行函数:concat、length、ifnull等
2.分组函数：统计函数，聚合函数，组函数，租婆统计通，多输入单输出
*/

#一.字符函数
#1.length 获取参数值的字节个数，一个中文3个字节
SELECT LENGTH('jijiji');
SELECT LENGTH('河豚河豚haha');

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

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

#4.substr、substring
#截取从指定索引处输出后面的所有字符
SELECT SUBSTR('河豚宝宝吃河豚',6) AS 输出;
#截取指定索引处指定字符长度的字符
SELECT SUBSTR('河豚宝宝吃大河豚',2,5) AS 输出;

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

#5.instr,返回子串第一次出现的位置，如果没有则返回0
SELECT INSTR('setgsgasgfg','as');

#6.trim,去前后字符，默认去空格,'字符串'from'字符串'。
SELECT TRIM('    we r we rwe  er we rw  r wer we r   we     ');
SELECT TRIM('acv' FROM 'acvacvacvadgsdgwagawgweagacvacvacv');

#7.lpad.....用指定的字符实现左填充指定长度,rpad..用指定的字符实现右填充指定长度
SELECT LPAD('河豚宝宝',11,'ha');
SELECT RPAD('河豚宝宝',11,'ha');

#8.replace替换,replace('字符串','被替换','去替换')
SELECT REPLACE('河豚宝宝睡觉起不来','起不来','起得来');

#二.数学函数
#1.round 四舍五入
SELECT ROUND(1.21);
SELECT ROUND(1.65);
SELECT ROUND(-5.11);
SELECT ROUND(2.2155,3);#小数点后保留3位

#2.ceil向上取整,返回大于等于该参数的最小整数
SELECT CEIL(3.11);
SELECT CEIL(-45.235);

#floor向下取整，返回小于等于该参数的最大参数
SELECT FLOOR(33.413245);
SELECT FLOOR(-54.54);

#3.truncate 截断,小数点后取N位
SELECT TRUNCATE(1.34546,4);
SELECT TRUNCATE(2.1444,1);

#4.mod 取余数d等同于%,结果的正负看被除数的正负
SELECT MOD(-10,6);
SELECT 10%6;
SELECT MOD(10,-3);

 

4.明日计划sql语句继续，预计单行函数完毕+分组函数+其他