MySQL基础

MySQL数据库

SQL语言

1. DQL语言: 查询
2. DML语言: 插入、修改、删除语句
3. DDL语言: 库和表的管理、数据类型、约束
4. TCL语言：事物和事物处理

排序

#进阶3：排序查询
/*
 引入：
    select * from employees;
 语法:
    select 查询列表                                      3
    from 表                                             1
    [where 筛选条件]                                     2
    order by 排序列表 asc 升序 | desc 降序  默认为升序       4
 特点：
    1.asc代表的是升序，desc代表的是降序 ， 如果不写，默认是升序
    2.order by 子句中可以支持单个字段、多个字段、表达式、函数、别名
    3.order by 子句一般是放在查询语句的最后面，limit子句除外
 */

#案例1: 查询员工信息，要求工资从高到低
select * from myemployees.employees order by salary desc;
select * from myemployees.employees order by salary asc;

#案例2: 排序基础上加上筛选 查询部门编号大于等于90的员工信息，按入职时间的先后进行排序 [添加筛选条件]
select *
from myemployees.employees
where department_id>=90 order by hiredate asc

#案例3：按年薪高低显示员工的信息和年薪 [按表达式排序]
select *,salary*12*(1+ifnull(commission_pct,0)) 年薪
from myemployees.employees
order by salary*12*(1+ifnull(commission_pct,0)) desc

#案例4：按年薪高低显示员工的信息和年薪 [按别名排序]
select *,salary*12*(1+ifnull(commission_pct,0)) 年薪
from myemployees.employees
order by 年薪 desc

#案例5：按姓名的长度显示员工的姓名和工资 [按函数排序]
select
    length(last_name) 字节长度,
       last_name,
       salary
from myemployees.employees
order by length(last_name) desc;

#案例6：查询员工信息，要求先按工资升序，再按员工编号降序 [按多个字段排序]
select
    employee_id,
       first_name,
       last_name,
       salary
from myemployees.employees
order by salary asc,employee_id desc

常见函数

#进阶4：常见函数
/*
 #进阶4：常见函数
/*
 概念：类似于java的方法,将一组逻辑语句封装在方法体中，对外暴露方法名
 好处：1.隐藏了实现细节 2.提高代码的重用性
 调用：select 函数名(实参列表) [from 表];
 特点: 1.叫什么(函数名)
      2.干什么(函数功能)
 分类：
      1.单行函数
      如 concat、length、ifnull等
      2.分组函数
      功能：做统计使用，又称为统计函数、聚合函数、组函数
  常见函数：
    字符函数：
    length
    concat
    substr
    instr
    trim
    upper
    lower
    lpad
    rpad
    replace

    数学函数
    round
    ceil
    floor
    truncate
    mod

    日期函数
    now
    curdate
    curtime
    year
    month
    day
    hour
    minute
    second
    str_to_date
    date_format

    其他函数
    version
    database
    user
    控制函数
    if
    case
 */


#一.字符函数 (utf8 一个英文字母占一个字节 一个汉字占3个字节)
#length 获取参数值的字节个数
select length('john');
select length('张三丰hahaha');
show variables like '%char%'

#2.concat 拼接字符串

select concat(last_name, '_', first_name)
from myemployees.employees

#3.upper lower

select upper('john');
select lower('joHn');

#示例：将姓变大写，名变小写
select concat(upper(first_name), '_', lower(last_name))
from myemployees.employees

#4.substr、substring
# 注意索引从1开始
select substr('李莫愁爱上了陆展元', 7) output; #陆展元 （截取从指定索引处后面的所有字符）
select substr('李莫愁爱上了陆展元', 1, 3) output;
#李莫愁 (截取从指定索引处指定字符长度的字符)

# 案例：姓名中首字符大写，其他
select concat(upper(substr(last_name, 1, 1)), '_', lower(substr(last_name, 2))) output
from myemployees.employees

#5. instr
# 用于返回子字符串第一次出现的索引，如果找不到返回0
select instr('杨不悔爱上了殷六侠', '殷六侠')

#6. trim
# 前后空格去除
select length(trim(' 张翠山 ')) as Output;

select trim('a' from 'aaa张aaa翠山aaa') as output;
#运行结果:张aaa翠山

select trim('aa' from 'aaa张aaa翠山aaa') as output;
#运行结果:a张aaa翠山a

#7. lpad
# 用指定的字符实现左填充指定长度
select lpad('殷素素', 10, '*') as output
#运行结果:*******殷素素

#8.replace

select replace('张无忌爱上了周芷若', '周芷若', '赵敏');
#运行结果:张无忌爱上了赵敏

#二、数学函数

#1.round 四舍五入
select round(1.65);
#运行结果: 2
select round(-1.65);
#运行结果: -2

#2.ceil 向上取整(返回大于等于该参数的最小整数）
select ceil(1.52)
#运行结果:2
select ceil(-1.52)
#运行结果:-1

#3.floor 向下取整(返回小于等于该参数的最大整数）
select floor(-9.99)
#运行结果:-10

#4.truncate 阶段
select truncate(1.699999, 1)
#运行结果:1.6

#5. mod 取余
#mod(a,b) : 底层公式：a-a/b*b
select mod(-10, -3);
#运行结果：-1

#三.日期函数

#now 返回当前系统日期
select now();
#运行结果:2021-11-17 12:15:49
select current_date;
#运行结果:2021-11-17
select current_time;
#运行结果:12:16:32

#可以获取指定的部分、年、月、日、小时、分钟、秒
select year(now());
#运行结果:2021
select year(hiredate)
from myemployees.employees;

select month(now());

select monthname(now())
#运行结果:November

#str_to_date: 将日期格式的字符转换成指定格式的日期
select str_to_date('1998-3-2', '%Y-%c-%d') as output

#查询出入职日期为1992-04-03的员工信息
select *
from myemployees.employees
where hiredate = '1992-4-3'

select *
from myemployees.employees
where hiredate = str_to_date('4-3 1992', '%c-%d %Y')

#date_format: 将日期类型转换成字符

select date_format(now(), '%y年%m月%d日')
#运行结果：21年11月17日

#查询有奖金的员工名和入职日期(xx月/xx日/xx年)
select last_name,
       date_format(hiredate, '%m月/%d日 %y年') 入职日期
from myemployees.employees
where commission_pct is not null

#其他函数
select version();
select database();
select user();

#五.流程控制函数
#1.if函数: if else 的效果

select if(10 > 5, '大', '小');
#运行结果：大

select last_name,
       commission_pct,
       if(commission_pct is not null, '有奖金', '没奖金') judgement
from myemployees.employees

#2.case函数：使用1：switch case

/*
 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 myemployees.employees
order by 新工资 desc

#3.case 函数的使用二：类似于 多重if
/*
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n;
 */

/*
案例：查询出员工的工资情况
如果工资>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
from myemployees.employees

#进阶五：分组查询

#引入：查询每个部门的平均工资
select avg(salary), department_id
from myemployees.employees
group by department_id

/*
 语法：
    select 分组函数,列(要求出现在group by的后面)
    from 表
    [where 筛选条件]
    group by 分组列表
    [order by 子句]
 注意：
    查询列表必须特殊，要求是分组函数和group by 后出现的字段
 特点：
    1.分组查询中的筛选条件分为两类
                数据源               位置              语句
    分组前筛选    原始表        group by 子句的前面      where
    分组后筛选    分组后的结果集  group by 子句的后面     having
    2.分组函数做条件肯定是放在having子句中
    3.能用分组前筛选的优先考虑使用分组前筛选
 */
#简单的分组查询
#案例1 查询每个工种的最高工资
select max(salary),
       job_id
from myemployees.employees
group by job_id

#案例2：查询每个位置上的部门个数
select count(*), location_id
from myemployees.departments
group by location_id

#添加筛选条件
#案例1：查询邮箱中包含a字符的，每个部门的平均工资

select round(avg(salary), 2),
       department_id
from myemployees.employees
where email like '%a%'
group by department_id

#案例2：查询有奖金的每个领导手下员工的最高工资
select max(salary),
       manager_id
from myemployees.employees
where commission_pct is not null
group by manager_id

# 添加复杂筛选条件
#案例1：查询哪个部门的员工个数大于2

select count(*),
       department_id
from myemployees.employees
group by department_id
having count(*) > 2

select a.co,
       a.department_id
from (select count(*) co,
             department_id
      from myemployees.employees
      group by department_id) a
where co > 2

#案例2 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

select employee_id,
       job_id,
       max(salary)
from myemployees.employees
where commission_pct is not null
group by job_id
having max(salary) > 12000

#1.查询每个工种有奖金的员工的最高工资
select max(salary),
       job_id
from myemployees.employees
where commission_pct is not null
group by job_id
#2.根据1的结果继续筛选最高工资>12000
select max(salary),
       job_id
from myemployees.employees
where commission_pct is not null
group by job_id
having max(salary) > 12000

#案例3 查询领导编号>102的每个领导手下的员工的最低工资>5000的领导编号是哪个，以及领导手下员工的最低工资
select manager_id,
       min(salary)
from myemployees.employees
where manager_id > 102
group by manager_id
having min(salary) > 5000


#按表达式(函数)分组

#案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些

#查询每个长度的员工个数

select count(*),
       length(last_name) len_name
from myemployees.employees
group by length(last_name)
having count(last_name) > 15

#支持别名
select count(*)          c,
       length(last_name) len_name
from myemployees.employees
group by len_name
having c > 15