day11.23
#03章 基本的select 语句 课后练习题
# 题目1:查询员工12个月的工资总和,并起名为ANNUAL SALARY
#理解1 计算12月的基本工资
SELECT employee_id,last_name,salary*12"ANNUAL SALARY" FROM employees;
#理解2计算12月的基本工资和奖金
SELECT employee_id,last_name,salary*12*(1+IFNULL(commission_pct,0)) "ANNUAL SALARY"
FROM employees;
#2.查询employees表中去除重复的job_id以后的数据
SELECT DISTINCT job_id FROM employees;
#3.查询员工工资大于12000的员工姓名和工资
SELECT DISTINCT job_id FROM employees;
#4.查询员工号为176的员工的姓名和部门号
SELECT DISTINCT job_id FROM employees;
#5.显示表departments的结构,病查询其中全部的数据
DESCRIBE departments;
SELECT*FROM departments;
#04章 运算符
#1.算数运算符:+ -* / div % mod
SELECT 100,100+0,100-0,100+120,100+100-50, 100+35.6,100-35.5
FROM DUAL;
# 3 5.5浮点型 100整型
SELECT 100+'1' FROM DUAL;
#在java中 结果是1001,在sql中+没有链接的作用,就表示加法运算此时会将字符串转换成数值(隐式转化)
SELECT 100+'a' #此时将a看做0来处理
FROM DUAL;
SELECT 100+NULL
FROM DUAL;#NULL参与运算结果为null
SELECT 100,100*1,100*1.0,100/1.0,100/2,
100+2*5/2,100/3,100 DIV 0 #分母如果为0结果则为null
FROM DUAL;
#取模运算:% mod
SELECT 12%3,12%5,12 MOD-5,-12%5,-12%-5 FROM dual;
#结果的符号与被摸数的符号相同
#练习 :查询员工id为偶数的员工信息
SELECT employee_id ,last_name,salary
FROM employees WHERE employee_id %2=0;
#比较运算符
#比较运算符用来对表达式左边操作数和右边的操作数进行比较,返回结果为真则返回1,比较的结果为假
#则返回null; 比较运算符通常作为select查询语句的条件来使用,返回符合条件的结果记录
#2,比较运算符
#2。1 =等于 <=>安全等于 <>不等于 !=不等于 < <= > >=
SELECT 1=2,1!=2, 1='1',1='a',0='a',#字符串出现隐式转换。如果数值转换不成功,则看做0
FROM DUAL;
SELECT 'a'='a','ab'='ab','a'='b' #两边都是字符串的话则按照ANSI的规则进行比较
FROM DUAL;
SELECT 1=NULL,NULL=NULL#符号左右两边只要有null参与比较则结果就为null
from DUAL;
SELECT last_name,salary
FROM employees WHERE salary=6000;
#<=>:安全等于 安全等于运算符(<=>)与等于运算符(=)的作用是相似的,唯一的区别是<=>
#可以用来对null进行判断。再两个操作数均为null时,其返回值为1而不是null;当一个操作数为null时
#其返回值为0,而不为null
SELECT 1<=>NULL,NULL<=>NULL
FROM DUAL;#练习 查询表中commission_pct为null的数据有哪些
SELECT last_name,salary ,commission_pct
from employees
WHERE commission_pct<=> NULL;
SELECT 3<=>2,'4'<=>NULL,''!=NULL,NULL!=NULL
FROM DUAL;
#2.2
# ①IS NULL is not NULL ISNULL(expr)
#练习: 查询表中commission_pct为null的数据有哪些
SELECT last_name,salary,commission_pct
from employees
WHERE commission_pct IS NULL;
SELECT last_name,salary ,commission_pct
FROM employees
WHERE ISNULL(commission_pct);
#练习:查询表中commission_pct不为null的数据有哪些
SELECT last_name,salary,commission_pct
from employees
WHERE commission_pct IS NOT NULL;
SELECT last_name,salary,commission_pct
FROM employees
WHERE NOT commission_pct<=>null;
#② LEAST() GREATEST()
SELECT LEAST('a','b','t'),GREATEST('g','b','m')
from DUAL;
SELECT LEAST(first_name,last_name),LEAST(LENGTH(first_name),LENGTH(last_name))
FROM employees;#③ BETWEEN(条件1)AND(条件2 )(查询条件1和条件2范围之内的数据,包含边界)
#查询工资在6000到8000的员工信息 BETWEEN...and 包含边界
SELECT employee_id,last_name,salary
from employees
WHERE salary BETWEEN 6000 AND 8000;
#查询员工工资在6000到8000范围之内的员工的信息 包含边界
SELECT employee_id,last_name ,salary
FROM employees
WHERE salary >=6000&&salary<=8000;
#查询员工工资不在6000到8000之间的
SELECT employee_id,last_name,salary
FROM employees
WHERE salary <6000 OR salary>8000;
#④in(set)\ 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;=
#或者
SELECT last_name ,salary,department_id
FROM employees
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: 模糊查询
#练习:查询last_name中包含字符a的员工信息
# %%:代表不确定个数的字符(0个,1个或者多个)
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';
#练习:查询last_name中以字符‘a’开头的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE 'a%';
#练习:查询last_name中包含字符a且包含字符e的员工的信息 写法1
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
#写法2
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';
#查询第二个字符是‘a’的员工的信息
#_:代表一个不确定的字符
SELECT last_name
FROM employees
WHERE last_name LIKE '_a%';
#练习:查询第二个字符是_且第三个字符是‘a’的员工的信息
#需要使用转义字符 意义 代表第一个字符是不确定的字符 第二个字符是下划线
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a%';
#或者(了解)
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_a%' ESCAPE '$';
#正则表达式的操作
#⑥ REGEXP\RLIKE:正则表达式
SELECT 'shkstart' REGEXP '^s','shkstart' REGEXP 't$'
, 'shkstart' REGEXP 'hk' FROM DUAL;
SELECT 'atguigu' REGEXP 'gu..gu','atguigu' REGEXP '[ab]'
FROM DUAL;