D32

今天学习时间为4个半小时，还可以，早上上满了四节课，太痛苦了，好困。今天学到了蛮多知识。正因为之前学过MySQL，所以学起来都蛮快的，很多知识，一看就懂。加油OVO

今日得到了一些小提示：

= 只能判断普通的内容
is 只能判断null值
<=> 安全等于，既能判断普通内容，又能判断null值。

mod：取余 select mod(-10,3)
被除数的正负数决定了结果的正负
a%b = a - (a/b)*b => -10 - (-10/3)*3 = -1
SELECT -10%3 ->-1
SELECT -10%-3 ->-1
SELECT 10%3 ->1
SELECT 10%-3 ->1

升序ascending全称

降序descending全称

Day32

学MySOL第二天

条件查询

#进阶二：条件查询
/*语法二：
select 查询列表
from 表名
where 筛选条件：
​
执行顺序
①from子句
②where子句
③select子句
​
特点
1，按条件表达式筛选
 
 关系运算符：>  <  >=  <=  =  !=等于<> ->这个是不等于符号
 补充：不建议使用 !=
 
2. 按逻辑表达式筛选
 逻辑运算符： and  or not
 补充：可以使用&& || !,但不建议
 
3.模糊查询
like
    一般和通配符搭配使用，对字符型数据进行部分匹配查询
    任意单个字符: _  
    任意多个字符: %  
in
between and
is null
 
*/
#案例1：查询工资>12000的员工信息
​
SELECT *
FROM employees
WHERE salary>12000;
​
#案例1：查询工资z在10000到20000之间的员工名、工资以及奖金
​
SELECT last_name,salary,commission_pct
FROM employees
WHERE 10000<=salary AND salary<=20000
​
SELECT * 
FROM EMPLOYEES
WHERE last_name LIKE '_\_%'
​
SELECT * 
FROM EMPLOYEES
WHERE last_name LIKE '__%' ESCAPE '_'
​
​
#in
/*
功能：查询某字段的值是否属于指定的列表之内
​
  a  in(常量值1，常量值2，常量值3,....)
  a not in(常量值1,常量值2,常量值3,....)
*/
​
#案例1：查询部门编号是30/50/90的员工名、部门编号
#方式一
SELECT last_name,department_id
FROM employees
WHERE department_id IN(30,50,90)
​
#方式二
SELECT last_name,department_id
FROM employees
WHERE department_id=30 OR department_id=50 OR department_id=90 
​
#案例2：查询工种编号不是SH_CLERR 或 IT_PROG的员工信息
​
SELECT *
FROM employees
WHERE job_id NOT IN ('SH_CLERR','IT_PROG')
​
#between and
/*
between and/not between and
​
*/
#案例1：查询部门编号是30-90之间的部门编号、员工姓名
SELECT CONCAT(first_name,last_name) AS NAME,department_id
FROM employees
WHERE department_id BETWEEN 30 AND 90
#先写30在写90，30 AND 90
​
#案例2：查询年薪不是100000-200000之间的员工姓名，工资，年薪。
​
SELECT (salary*12*(1+IFNULL(commission_pct,0))) AS'年薪',salary,CONCAT(first_name,last_name) AS NAME
FROM employees
WHERE (salary*12*(1+IFNULL(commission_pct,0))) NOT BETWEEN 100000 AND 200000 
​
#4. is null/is not null
#案例一：查询没有奖金的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NULL
​
/* ----------------------------------------------
   =   只能判断普通的内容
   is  只能判断null值
  <=>  安全等于，既能判断普通内容，又能判断null值。
*------------------------------------------------*/
​
#练习
/*
1. 查询工资大于 12000 的员工姓名和工资
2. 查询员工号为 176 的员工的姓名和部门号和年薪
3. 选择工资不在 5000 到 12000 的员工的姓名和工资
4. 选择在 20 或 50 号部门工作的员工姓名和部门号
5. 选择公司中没有管理者的员工姓名及 job_id
6. 选择公司中有奖金的员工姓名，工资和奖金级别
7. 选择员工姓名的第三个字母是 a 的员工姓名
8. 选择姓名中有字母 a 和 e 的员工姓名
9. 显示出表 employees 表中 first_name 以 'e'结尾的员工信息
10. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位
11. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位
*/
#1.查询工资大于 12000 的员工姓名和工资
​
SELECT CONCAT(last_name,first_name) AS "姓 名" ,salary
FROM employees
WHERE salary >12000
​
#2. 查询员工号为 176 的员工的姓名和部门号和年薪
SELECT salary*12*(1+ISNULL(commission_pct)) AS '年薪',
CONCAT(last_name,first_name) AS "姓 名" ,department_id
FROM employees 
WHERE employee_id <=> 176
​
​
#3. 选择工资不在 5000 到 12000 的员工的姓名和工资
​
SELECT salary ,CONCAT(last_name,first_name) AS "姓 名" 
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000
​
#4. 选择在 20 或 50 号部门工作的员工姓名和部门号
SELECT department_id,CONCAT(last_name,first_name) AS "姓 名" 
FROM employees
WHERE department_id =20 OR department_id =50
​
​
#5. 选择公司中没有管理者的员工姓名及 job_id
SELECT CONCAT(last_name,first_name) AS "姓 名" ,job_id
FROM employees
WHERE manager_id <=> NULL
​
​
#6. 选择公司中有奖金的员工姓名，工资和奖金级别
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL
​
​
#7. 选择员工姓名的第三个字母是 a 的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%'
​
​
#8. 选择姓名中有字母 a 和 e 的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%'
​
​
#9. 显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT *
FROM employees
WHERE first_name LIKE '%e'
​
​
#10. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位
SELECT job_id,last_name
FROM employees
WHERE department_id BETWEEN 80 AND 100
​
#11. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位
SELECT last_name,job_id
FROM employees
WHERE manager_id IN(100,101,110)
​

---

排序查询

#进阶3：排序查询
/*
语法：
select 查询列表
from 表名
【where  筛选条件】
order by 排序的字段或表达式;
​
​
特点：
1、asc代表的是升序，可以省略，默认是升序。
   desc代表的是降序
​
2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段
​
3、order by子句在查询语句的最后面，除了limit子句
​
*/
​
#1、按单个字段排序
SELECT * FROM employees ORDER BY salary DESC;
​
#2、添加筛选条件再排序
​
#案例：查询部门编号>=90的员工信息，并按员工编号降序
​
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY employee_id DESC;
​
#3、按表达式排序
#案例：查询员工信息 按年薪降序
​
SELECT *,salary*12*(1+IFNULL(commission_pct,0))
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 年薪 ASC;
​
#5、按函数排序
#案例：查询员工名，并且按名字的长度降序
​
SELECT LENGTH(last_name),last_name 
FROM employees
ORDER BY LENGTH(last_name) DESC;
​
#6、按多个字段排序
​
#案例：查询员工信息，要求先按工资降序，再按employee_id升序
SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC;
#先降序，再升序
​
#7、按列数排序
#按第二列来排序。
SELECT *
FROM employees
ORDER BY 2 
​
​
/*
作业题
1、查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序
*/
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS '年薪'
FROM employees
ORDER BY 年薪 DESC,last_name ASC
​
/*
2.选择工资不在8000到17000的员工的姓名和工资，按工资降序。
*/
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
​
/*
3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
*/
​
SELECT *
FROM employees
WHERE email LIKE '%e%' 
ORDER BY LENGTH(email) DESC,department_id ASC

常见函数

#常见函数
/*
概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节  2、提高代码的重用性
调用：select 函数名(实参列表) 【from 表】;
特点：
    ①叫什么（函数名）
    ②干什么（函数功能）
​
分类：
    1、单行函数
    如 concat、length、ifnull等
    2、分组函数
    
    功能：做统计使用，又称为统计函数、聚合函数、组函数
    
常见函数：
    一、单行函数
    字符函数：
    length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
    concat
    substr
    instr
    trim
    upper
    lower
    lpad
    rpad
    replace
    
    数学函数：
    round
    ceil
    floor
    truncate
    mod
    
    日期函数：
    now
    curdate
    curtime
    str_to_date
    date_format
    其他函数：
    version
    database
    user
    控制函数
    if
    case
*/
​
/*
字符函数：
    length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
    char_length:获取字符个数，
    concat:拼接字符
​
    substr(str,起始索引，截取长度)截取子串。起始索引是1，3是字符个数
    substr(str,起始索引)默认截取起始索引后的所有字符。
    
    instr：获取字符第一次出现的索引
​
    trim：去除前后指定的字符，默认去空格。
    
    upper：大写。
​
    lower：小写。
​
    lpad/rpad：左填充/右填充
    select rpad('sen',10,'a')
    
    replace：
    
    strcmp:比较两个字符大小,前面大-->返回1，后面大-->返回-1，相等-->返回0
    
    left/right：左右截取子串
*/
​
#案例，查询员工表的姓名，要求格式，姓首字符大写，其他字符小写，名所有字符大写，且姓和名之间用_分隔
#最后起别名‘OUTPUT’
​
SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)),'_',UPPER(last_name)) AS OUTPUT
FROM employees
​
SELECT STRCMP('aaa','aaa')
​
/*
数学函数：
    
    ABS：绝对值
    
    round：四舍五入。
​
    ceil：向上取整，1.09 -> 2 。返回>=的最小整数。
            -1.2 -> -1。
    
    floor：向下取整。返回<=的最大整数。
    
    truncate：截断 ≈ truncate(1.145613,0) ->1, 这里的0 ≈ 保留小数点后几位的意思。 
​
    mod：取余 select mod(-10,3)
          被除数的正负数决定了结果的正负
          a%b = a - (a/b)*b   =>  -10 - (-10/3)*3 = -1
          SELECT -10%3  ->-1 
          SELECT -10%-3 ->-1
          SELECT 10%3  ->1
          SELECT 10%-3 ->1
          
*/
​
/*
日期函数：
    now:获取当前时间 select now()
    curdate :获取当前日期
    curtime ：获取当前时间，不要日期
    
    datediff:获取两个日期的(天数差),前面减后面 
    
    str_to_date:将指定格式解析字符串为日期类型
​
    date_format: DATE_FORMAT('2000-7-25','%Y年%M月%d日 %H小时%i分钟%s秒')
    
*/
​
SELECT NOW()
SELECT CURTIME()
SELECT CURDATE()
SELECT DATEDIFF('2000-7-25','2021-4-15')
SELECT DATE_FORMAT('2000-7-25','%Y年%M月%d日 %H小时%i分钟%s秒') AS 出生日期
​
SELECT DATE_FORMAT(hiredate,'%Y年%M月%d日 %H小时%i分钟%s秒')AS 入职日期
FROM employees
​
SELECT STR_TO_DATE('15/4 2021','%d/%m %Y') 现在日期
​
/*
控制函数
    if：if(条件，如果是执行这个，如果不是执行这个)
    case:
    情况一：case 条件
        when  值1 then 结果1
        when  值2 then 结果2
        when  值3 then 结果3
        else 默认值
        end
    情况二：case
        when 条件一 then 结果1
        when 条件二 then 结果2
        else 结果n
        end
*/
​
#需求：如果有奖金，则显示最终奖金，如果没有，显示0
SELECT IF(commission_pct IS NOT NULL,salary*12*commission_pct,0) 奖金
FROM employees
ORDER BY 奖金 DESC
​
#case ≈ switch，可以实现等值判断
​
#案例，如果部门编号是30，工资为3倍，50，为5倍，60，为6倍。否则不变。
SELECT salary,department_id,
CASE department_id
WHEN 30 THEN salary*3
WHEN 50 THEN salary*5
WHEN 60 THEN salary*6
ELSE salary
END AS newsalary
FROM employees
ORDER BY department_id 
​
#案例，工资大于20000，显示A，工资>15000,显示B，>10000，显示C，否则显示D
SELECT salary,department_id,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END grade
FROM employees
ORDER BY grade

最后的小测试

#测试
#1. 显示系统时间(注：日期+时间)
SELECT NOW()
​
#2. 查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（new salary）
SELECT last_name,employee_id,salary,salary*1.2 'new salary'
FROM employees
​
#3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT LENGTH(CONCAT(first_name,last_name)),CONCAT(first_name,last_name)'姓名'
FROM employees
ORDER BY SUBSTR(CONCAT(first_name,last_name),1,1) ASC
​
#4. 做一个查询，产生下面的结果
/*
<last_name> earns <salary> monthly but wants <salary*3>
Dream Salary
King earns 24000 monthly but wants 72000
*/
​
SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) 'Dream Salary'
FROM employees
​
/*
5.使用 case-when，按照下面的条件：
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
*/
SELECT last_name,job_id,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END 'job_grade'
FROM employees