错误案例:查询员工名和姓连接成一个字段,并显示为 姓名
select
last_name+first_name as 姓名
from
employees;
concat实现连接
#案例:查询员工名和姓连接成一个字段,并显示为 姓名select concat('a','b','c')as 结果;select
concat(last_name,first_name)as 姓名
from
employees;
多个句式的综合
select last_name, job_id, salary as sal from employees;select*from employees;select employee_id, last_name, salary *12as"annual salary"from employees;desc department;select*from`departments`;selectdistinct job_id from employees;select
ifnull(commission_pct,0)as 奖金率,
commission_pct
from
employees;select
concat(`first_name`,',',`last_name`,',',`job_id`,',',ifnull(commission_pct,0))as out_put
from
employees;
#按条件表达式筛选#案例1:查询工资>12000的员工信息select*from
employees
where
salary>12000;#案例2:查询部门编号不等于90号的员工名和部门编号select
last_name,
department_id
from
employees
where
department_id<>90;#department_id!=90;也可以这么写
#按逻辑表达式筛选#案例1:查询工资再10000到20000之间的员工名、工资以及奖金select
last_name,
salary,
commission_pct
from
employees
where
salary>=10000and salary<=20000;#案例2:查询部门编号不是在90到110之间,或者工资高于15000的员工信息select*from
employees
wherenot(department_id>=90and department_id<=110)or salary>15000;#department_id<90 or department_id>110 or salary>15000;也可以这么写
模糊查询 like——特点:一般和通配符搭配使用(通配符:%任意多个字符,包含0个字符;_任意单个字符) between and in is null | is not null
#1、like#案例1:查询员工名中包含字符a的员工信息select*from
employees
where
last_name like'%a%'#案例2:查询员工名中第三个字符为n,第五个字符为l的员工名和工资select
last_name,
salary
from
employees
where
last_name like'__n_l%';#案例3:查询员工名中第二个字符为_的员工名select
last_name
from
employees
where
last_name like'_$_%'escape'$';#last_name like '_\_%';也可以这样写
#2、between and#包好临界值,两个临界值不要调换顺序#案例1:查询员工编号在100到120之间的员工信息select*from
employees
where
employee_id between100and120;#employee_id >= 100 and employee_id <= 120;一个效果
#in#in列表的值类型必须一致或兼容#案例:查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号select
last_name,
job_id
from
employees
where
job_id IN('IT_PROT','AD_VP','AD_PRES');#job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';一个效果
#is null#案例1:查询没有奖金的员工名和奖金率select
last_name,
commission_pct
from
employees
where
commission_pct isnull;#案例2:查询没有奖金的员工名和奖金率select
last_name,
commission_pct
from
employees
where
commission_pct isnotnull;
安全等于
#案例1:查询没有奖金的员工名和奖金率select
last_name,
commission_pct
from
employees
where
commission_pct <=>null;#案例2:查询工资为12000的员工信息select
last_name,
salary
from
employees
where
salary <=>12000;