【SQL】之select：运算符/排序/分页

1.select…from…where

-- 1.给列表起别名的三种方式，必须使用“”
SELECT employee_id emp_id,last_name AS lname,salary "薪水",salary*12 "annual sal" 
FROM employees;

-- 2.去除重复行 distinct
SELECT DISTINCT department_id 
FROM employees;

-- 3.null空值参与运算，结果也为空
SELECT employee_id,salary "月工资",salary*(1+commission_pct)*12 "年工资",commission_pct
FROM employees;
-- 使用函数，替换为0
SELECT employee_id,salary "月工资",salary*(1+IFNULL(commission_pct,0))*12 "年工资",commission_pct
FROM employees;

-- 4.着重号``避免与数据库中关键字重名
SELECT *
FROM `order`;

-- 5.添加常数
SELECT '阿里',124,employee_id,last_name
FROM employees;

-- 6.显示表结构
DESCRIBE employees;
DESC departments; 

-- 7.过滤数据
# 查询90号部门的员工信息
SELECT *
FROM employees
WHERE department_id = 90;
# 查询lastname为king的员工信息：
SELECT *
FROM employees
WHERE last_name='king';
#mysql中对查询的字段不区分大小写

练习

-- 查询员工12个月的工资总和，并起名为ANNUAL SALARY
SELECT employee_id,last_name,salary*(1+IFNULL(commission_pct,0))*12 "ANNUAL SALARY"
FROM employees;
-- 查询员工表中除去重复job_id后的数据
SELECT DISTINCT job_id
FROM employees;
-- 查询工资大于12000的员工姓名和工资
SELECT first_name,last_name,salary
FROM employees
WHERE salary>12000;
-- 查询员工号为176的员工的姓名和部门号
SELECT first_name,last_name,department_id
FROM employees
WHERE employee_id= 176;

2.运算符

算术运算符

-- 运算符 dual:伪表

SELECT 100,100+0,100-0,100+50,100-35.5,100+35.5
FROM DUAL;

#sql中，+没有连接的作用，只表示加法运算，此时会将字符串转换为数值（隐式转换）
SELECT 100+'1' #java中结果是1001
FROM DUAL;

SELECT 100+'a' #此时a看作0处理
FROM DUAL;

SELECT 100+NULL #null与任何加减结果都为null
FROM DUAL;

#先乘除后加减
SELECT 100,100*1,100*1.0,100/1.0,100/2,100+2*5/2,100/3,100 DIV 0
FROM DUAL;

SELECT 12%3,12%5,12 MOD -5,-12%5,-12%-5
FROM DUAL;

比较运算符

-- 比较运算符 --

SELECT 1=2,1!=2,1='1',1='a',0='a' #引号内的内容不能隐式转换为一个数值，则看作0
FROM DUAL;

SELECT 'a'='a','ab'='ab','a'='b'
FROM DUAL;

SELECT 1=NULL,NULL=NULL
FROM DUAL;

-- 查询表中commission字段为null的数据有哪些
SELECT last_name,salary,commission_pct
FROM employees
#where salary = 6000 只显示执行结果返回1（true）的
#where commission_pct=null; #什么都没有，只要有null参与判断，结果返回值都为null 
WHERE commission_pct<=>NULL;

SELECT last_name,salary,commission_pct
FROM employees
#where commission_pct is null;
WHERE ISNULL(commission_pct);
#where commission_pct is not null;


-- 安全等于<=> 为null而生
SELECT 1<=>2,1<=>'1',1<=>'a',0<=>'a'
FROM DUAL;

SELECT 1<=>NULL,NULL<=>NULL
FROM DUAL;
-- 不等于
SELECT 3<>2,'4'<>NULL,''!=NULL,NULL!=NULL
FROM DUAL;

-- 比较大小least，greatest
SELECT LEAST('h','c','w','a'),GREATEST('t','s','q','z','a')
FROM DUAL;

SELECT LEAST(first_name,last_name) #按字典序比
FROM employees;

SELECT LEAST(LENGTH(first_name),LENGTH(last_name)) #按名字长度比
FROM employees;

-- between and
#查询工资在6000~8000的员工信息(含边界)
SELECT employee_id,first_name,last_name,salary
FROM employees
#where salary between 6000 and 8000;
WHERE salary>=6000 && salary<=8000;
#where salary between 8000 and 6000; 无结果，上下限不能交换

#查询工资不在6000~8000的员工信息
SELECT employee_id,first_name,last_name,salary
FROM employees
#where salary not between 6000 and 8000;
WHERE salary<6000 OR salary>8000;

-- in \ not in (set)
#查询部门为10，20，30部门的员工信息
SELECT last_name,salary,department_id
FROM employees
#where department_id=10 or department_id=20 or department_id=30;
WHERE department_id IN (10,20,30);

#查询工资不是6000，7000，8000的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE salary NOT IN (6000,7000,8000);

-- like 模糊查询 %代表不确定个数的字符，0~多个
#查询lastname中包含字符'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';

#查询lastname中包含字符'a'且包含'e'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';
#where last_name like '%a%' and last_name like '%e%';

-- _:代表一个不确定的字符

#查询第二个字符是'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '_a%';

#查询第二个字符是'_'且第三个字符是'a'的员工信息
#转义字符\
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a%'; #第一个字符不确定，\_是第二个字符，下划线，第三个字符是a
#where last_name like '_$_a%' escape '$'; 此时$代表转义

-- 正则表达式 REGEXP\RLIKE
#以s开头，以t结尾，中间含有hk
SELECT 'shkstart' REGEXP '^s','shkstart' REGEXP 't$','shkstart' REGEXP 'hk'
FROM DUAL;

SELECT 'atguigu' REGEXP 'gu.gu','atguigu' REGEXP '[ab]'#包含a或b
FROM DUAL;

逻辑运算符、位运算符

-- 逻辑运算符

#xor 异或
SELECT last_name,salary,department_id
FROM employees
WHERE department_id=50 XOR salary>6000; #满足1不满足2，满足2不满足1

#and 优先级高于 or

-- 位运算符 
#&与（1,1->1;1,0->0;0,0->0）
#|或（1,1->1;0,1->1;0,0->0）
#^异或（1,1->0;1,0->1;0,0->0）
SELECT 12 & 5,12 | 5,12 ^ 5
FROM DUAL;
#~ 取反
SELECT 10 & ~ 1
FROM DUAL;
#<< 左移*2
#>> 右移/2
SELECT 4<<1,8>>1
FROM DUAL;

练习

#1.选择工资不在5000到12000的员工的姓名和工资
SELECT first_name,salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;
#2.选择在20或50号部门工作的员工姓名和部门号
SELECT first_name,department_id
FROM employees
WHERE department_id=20 OR department_id=50;
#3.选择公司中没有管理者的员工姓名及job_id
SELECT first_name,job_id,manager_id
FROM employees
WHERE ISNULL(manager_id);
#4.选择公司中有奖金的员工姓名，工资和奖金级别
SELECT first_name,salary,commission_pct
FROM employees
WHERE NOT ISNULL(commission_pct);
#5.选择员工姓名的第三个字母是a的员工姓名
SELECT first_name
FROM employees
WHERE first_name LIKE "__a%";
#6.选择姓名中有字母k和a的员工姓名
SELECT first_name
FROM employees
WHERE first_name LIKE "%a%" AND first_name LIKE "%k%";
#7.显示出表employees中firstname以e结尾的员工信息
SELECT first_name
FROM employees
WHERE first_name LIKE "%e";
#8.显示出表中部门编号在80~100之间的姓名、工种
SELECT first_name, job_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;
#9.显示出表的manager_id是100，101，110的员工姓名、工资、管理者id
SELECT first_name,salary,manager_id 
FROM employees
WHERE manager_id IN (100,101,110);

3.排序与分页

排序 select…from…order by…

-- 排序
SELECT *
FROM employees;

#1.按salary从高到低顺序显示员工信息
#order by:升序asc，降序desc；默认升序
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;

#2.可以使用列的别名进行排序
SELECT employee_id,last_name,salary,salary*12 annual_sal
FROM employees
ORDER BY annual_sal;

SELECT employee_id,last_name,salary,salary*12 annual_sal
FROM employees
WHERE annual_sal>50000; #列的别名只能用在order by

#3.二级排序
#员工信息按department id降序排列，再按salary升序排列
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;

分页 select…from…limit…

-- 分页
#1.mysql使用limit实现数据的分页显示 （起始，偏移量）
#每页显示20条记录,此时显示第一页
SELECT employee_id,last_name
FROM employees
LIMIT 0,20;

#每页显示20条记录,此时显示第二页
SELECT employee_id,last_name
FROM employees
LIMIT 20,20;

#每页显示pagesize条记录，此时显示pageno页
/*
SELECT employee_id,last_name
FROM employees
LIMIT (pageno-1)*pagesize,pagesize;
*/

#where···order by ··· limit···(声明顺序)

SELECT employee_id,last_name,salary
FROM employees
WHERE salary >6000
ORDER BY salary DESC
#limit 0,10;
LIMIT 10;

#表里有107条数据，只想显示第32，33条：
SELECT employee_id,last_name
FROM employees
LIMIT 31,2;

#mysql8.0新特性：
SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 31;	#offset偏移量

练习

#查询员工表中工资最高的员工信息
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC
LIMIT 1;

【注意】：limit不能用在oracle中
oracle查询前十条记录：

select rownum,employee_id,last_name
from employees
where rownum<=10;

#1.查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序显示
SELECT first_name,department_id,salary*12 AS annual_sal
FROM employees
ORDER BY annual_sal DESC,first_name ASC;

#2.选择工资不在8000到17000的员工姓名和工资，按工资降序，显示第21到40位置的数据
SELECT first_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20,20;

#3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT first_name,email,department_id
FROM employees
WHERE email LIKE "%e%"
ORDER BY LENGTH(email) DESC,department_id;