【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;