# 进阶1:基础查询
/*
语法:
select 查询列表 from 表名;
特点:
1.查询列表可以是:表中的字段、常量值、表达式、表达式、函数
2.查询的结果是一个虚拟的表格
如何执行:执行谁,请选中谁!
*/
# 先确定是什么数据库。
use myemployees;
#1.查询表中的单个字段
select last_name from employees;
#2.查询表中的多个字段
select last_name,salary,email from employees;
#3.查询表中的所有字段
select * from employees;
# 双击列表中出现的字段,就可以不用自己手敲了。
#select employee_id,first_name,last_name from employees;
#4.查询常量值
select 100;
select "john";
#5.查询表达式
#100对98取余
select 100%98;
#6.查询函数
select version();
#7.为字段取别名
/*
a.便于理解
b.如果要查询的字段有重名的情况,使用别名可以区分开来
*/
# 方式一:使用as
select 100%98 as 结果;
select last_name as 姓,first_name as 名 from employees;
# 方式二:使用空格
select last_name 姓,first_name 名 from employees;
# 案例:查询salary,显示结果为out put,遇到空格,#等关键字,需要加引号。
select salary as "out put" from employees;
#8. 去重
# 案例:查询员工表中涉及到的所有的部门编号
select distinct department_id from employees;
#9.+号的作用
/*
mysql中 +号 只有一个功能:运算符
select 100+90; 两个操作数均为数值型,则作加法运算
select "123"+90; 其中一方为字符型,试图将字符型数值转换为数值型,
如果转换成功,则继续做加法运算;
如果转换失败,则不能转换的当作0处理;
select "john"+90;
select null+90; 只要其中一方为null,则结果肯定为null
*/
# 案例:查询员工名和姓连接成一个字段,并显示为 姓名
select last_name+first_name as "姓名" from employees; #这个结果是错误的
如何解决:
use myemployees;
select concat("a","b","c") as "结果";
select concat(last_name," ",first_name) as "姓名" from employees;
结果的显示:
姓名的显示:
题目:显示employees的全部列,各个列之间用逗号链接,列头显示成out_put
我们先看下employees的全部列
发现结果全为null,说明有些字段中出现了null,需要分情况考虑‘
select ifnull(commission_pct,0) as "奖金率", commission_pct from employees;
将为null值的改为0,结果如下:
# 正确写法
select concat(employee_id,",",first_name,",",last_name,",",email,",",phone_number,",",job_id,",",salary,",",ifnull(commission_pct,0),",",manager_id,",",department_id,",",hiredate)
as "out_put" from employees;
显示:
isnull函数
功能:判断某字段或者表达式是否为null,如果是,则返回1,否则返回0