when Sex = ‘1‘ then ‘男‘
when Sex = ‘0‘ then ‘女‘
end as 性别
from employees ;
===================================================
多表连接替换查询结果操作:
select Name as 姓名,Address as 住址,
case
when InCome<2000 then ‘低收入‘
when InCome>3000 then ‘高收入‘
else ‘中等收入‘
end as 收入水平
from employees join salary;
===================================================
累计行数操作:
select count(*) as 员工总数
from employees;
===================================================
求平均值操作:
select avg(InCome) as 月收入平均值
from salary;
===================================================
求最大值操作:
select max(InCome) as 月薪最高
from salary;
===================================================
求最小值操作:
select min(InCome) as 月薪最高
from salary;
===================================================
求总数操作:
select sum(InCome) as 月薪总数
from salary;
===================================================
模式匹配操作:
select *
from employees
where name like ‘王%‘;
===================================
select 学号,姓名,专业名
from xs
where 学号 like ‘%0_‘;【查看学号倒数第二位为零的学号】
===================================
select 学号,姓名
from xs
where 学号 like ‘%#_%‘ escape ‘#‘;
===================================================
子查询:
select *
from employees
where DepartmentID in
(
select DepartmentID
from departments
where DepartmentName=‘市场部‘
);
===================================================
连接查询的使用:
select employees.*,salary.*
from employees join salary on employees.EmployeeID=salary.EmployeeID;
===================================================
分类统计人数:
select Sex,count(Sex)
from employees
group by Sex;
===================================================
左连接:
select xs.*,课程号
from xs left join xs_kc on xs.`学号`=xs_kc.`学号`;
===================================================
右连接:
select xs.*,课程号
from xs right join xs_kc on xs.`学号`=xs_kc.`学号`;
===================================================
查询空值语句:
select *
from xs
where 备注 is null;
===================================================
and,or的使用:
select 姓名,专业名,性别
from xs
where 专业名=‘计算机‘ and 性别=1;
================================
select 姓名,专业名,性别
from xs
where 专业名=‘计算机‘ or 专业名=‘通行工程‘;
===================================================
all的使用:
select 姓名,出生日期
from xs
where 出生日期>all
(
select 出生日期
from xs
where 专业名=‘信息工程‘
);
==================================================
正向排序:
select *
from xs_kc
order by 成绩;
====================
逆向排序:
select *
from xs_kc
order by 成绩 desc;
==================================================
limit 字句使用:
select *
from xs_kc
order by 成绩 desc
limit 5;
========
limit 3,5;<从第三行开始，输出5行>
==================================================
视图使用:<注意！！多表连接时,查看设计表栏位字符集,排列顺序是否一致>
create view xs_1
as
select 学号,姓名,出生日期
from xs;
==================================================
MySQL知识
标签：
