1.起别名 :AS
SELECT `last_name` AS 姓 FROM `employees`;
也可以省略
SELECT `last_name` 姓 FROM `employees`;
2.去重:distinct
select distinct `department_id` from `employees`;
3.姓+名两个字符拼接起来:concat
SELECT CONCAT(`last_name`,`first_name`) 姓名 FROM `employees`;
4.显示结构:desc
DESC `employees`;
二.条件查询
语法:
select
查询列表
from
表名
where
筛选条件;
分类
一、按条件表达式筛选
简单条件运算符: > < = != <> >= <=
二、按逻辑表达式筛选
逻辑运算符
作用:用于连接条件表达式
&& || !
and or not
三、模糊查询
like
between and
in
is null
#`salary`>12000 的员工信息
select * from `employees` where `salary`>12000;
#查询员工工资在10000,到20000之间的员工名、工资以及奖金
select `first_name`,`salary`,`commission_pct` from `employees` where `salary`>10000 and `salary`<20000;
#查询部门编号不是在90 和110 之间,或者工资高于15000的员工信息
SELECT * FROM `employees` WHERE `department_id`<90 OR `department_id`>110 OR `salary`>15000;
like
#模糊查询,'%__%'通配符
SELECT * FROM `employees` WHERE `first_name` LIKE '%a%';
#查询员工的名字中第二个字符为_的员工名
SELECT `last_name` FROM `employees` WHERE `last_name` LIKE '__%';
between and
#查询员工编号在100到120之间的员工信息
SELECT * FROM `employees` WHERE `employee_id` BETWEEN 100 AND 120;
in
#案例:查询员工的工种编号,AD_VP、 AD_ASST、FI_MGR中的一个员工名和工种编号
SELECT `last_name`, `job_id` FROM `employees` WHERE `job_id` IN('AD_VP', 'AD_ASST','FI_MGR');
is null
#查询没有奖金的员工名和奖金率
SELECT `last_name`,`commission_pct` FROM `employees` WHERE `commission_pct` IS NULL;
is not null
#查询有奖金的员工名和奖金率
SELECT `last_name`,`commission_pct` FROM `employees` WHERE `commission_pct` IS NOT NULL;
<=>
#安全等于 <=>
SELECT `commission_pct` FROM `employees` WHERE `commission_pct`<=>NULL;
SELECT `salary` FROM `employees` WHERE `salary`<=>12000;