流程控制 1，IF(expr,v1,v2): 如果expr是TRUE则返回v1，否则返回v2 mysql> select if(3>0,'正数','0或负数') ; +-------------------------------+ | if(3>0,'正数','0或负数') | +-------------------------------+ | 正数 | +-------------------------------+ 1 row in set (0.00 sec) 2，IFNULL(v1,v2): 如果v1不为NULL，则返回v1，否则返回v2 mysql> insert into departments(dept_id) values(9); mysql> select dept_id, dept_name, ifnull(dept_name, '未设置') from departments; +---------+-----------+--------------------------------+ | dept_id | dept_name | ifnull(dept_name, '未设置') | +---------+-----------+--------------------------------+ | 1 | 人事部 | 人事部 | | 2 | 财务部 | 财务部 | | 3 | 运维部 | 运维部 | | 4 | 开发部 | 开发部 | | 5 | 测试部 | 测试部 | | 6 | 市场部 | 市场部 | | 7 | 销售部 | 销售部 | | 8 | 法务部 | 法务部 | | 9 | NULL | 未设置 | +---------+-----------+--------------------------------+ 9 rows in set (0.00 sec), 3，CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END: 如果expr等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不想等，则返回ELSE后面的rn mysql> select dept_id, dept_name, -> case dept_name -> when '运维部' then '技术部门' -> when '开发部' then '技术部门' -> when '测试部' then '技术部门' -> when null then '未设置' -> else '非技术部门' -> end as '部门类型' -> from departments; +---------+-----------+-----------------+ | dept_id | dept_name | 部门类型 | +---------+-----------+-----------------+ | 1 | 人事部 | 非技术部门 | | 2 | 财务部 | 非技术部门 | | 3 | 运维部 | 技术部门 | | 4 | 开发部 | 技术部门 | | 5 | 测试部 | 技术部门 | | 6 | 市场部 | 非技术部门 | | 7 | 销售部 | 非技术部门 | | 8 | 法务部 | 非技术部门 | | 9 | NULL | 非技术部门 | +---------+-----------+-----------------+ 9 rows in set (0.00 sec) 分组函数 mysql> select employee_id,sum(basic+bonus) as total from salary where employee_id=10 and year(date)=2018; 求和 +-------------+--------+ | employee_id | total | +-------------+--------+ | 10 | 116389 | +-------------+--------+ 1 row in set (0.00 sec)
mysql> select employee_id,basic+bonus as total from salary where employee_id=10 and year(date)=2018; +-------------+-------+ | employee_id | total | +-------------+-------+ | 10 | 24837 | | 10 | 31837 | | 10 | 29837 | | 10 | 29878 | +-------------+-------+ 4 rows in set (0.00 sec)
mysql> select employee_id,min(basic+bonus) as total from salary where employee_id=10 and year(date)=2018; 求最小值 +-------------+-------+ | employee_id | total | +-------------+-------+ | 10 | 24837 | +-------------+-------+ 1 row in set (0.00 sec)
mysql> select employee_id,count(*) as total from salary where employee_id=10 and year(date)=2018; +-------------+-------+ | employee_id | total | +-------------+-------+ | 10 | 4 | +-------------+-------+ 1 row in set (0.00 sec)
mysql> select count(*) 统计表总行数 -> from -> salary; +----------+ | count(*) | +----------+ | 8055 | +----------+ 1 row in set (0.00 sec) mysql> select 统计每个部门人数 -> dept_id,count(*) -> from -> employees -> group by dept_id; +---------+----------+ | dept_id | count(*) | +---------+----------+ | 1 | 8 | | 2 | 5 | | 3 | 6 | | 4 | 55 | | 5 | 12 | | 6 | 9 | | 7 | 35 | | 8 | 3 | +---------+----------+ 8 rows in set (0.00 sec) 查询2018年每个员工总收入 mysql> select -> employee_id,sum(basic+bonus) as total -> from -> salary -> where -> year(date)=2018 -> group by employee_id -> order by total desc; 查询小于10人的部门 mysql> select dept_id,count(*) from employees group by dept_id -> having count(*)<10; +---------+----------+ | dept_id | count(*) | +---------+----------+ | 1 | 8 | | 2 | 5 | | 3 | 6 | | 6 | 9 | | 8 | 3 | +---------+----------+ 5 rows in set (0.00 sec) 连接查询（多表查询） 笛卡尔积，直接查询两张表的结果 查询员工及部门名 mysql> select name,dept_name from employees,departments where employees.dept_id=departments.dept_id; 这种方法不推荐
mysql> select name,dept_name from employees inner join departments on employees.dept_id=departments.dept_id;
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
618
