mysql学习第二天

SHOW DATABASES;
#运算符
#1.算术运算符：+  -  *  /  div  %  mod
SELECT 100,100+0,100+50,100+50*30,100+35.5,100-35.5
FROM DUAL;

SELECT 100+'1'#在sql中+ 没有连接的作用  就表示加法运算。此时，会将字符串转换为数值（隐式转换）
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 DIV 0
FROM DUAL;
#查询员工id为偶数的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE employee_id%2 = 0;
#2.比较运算符
#2.1   =  <=>  <>  !=  
SELECT 1=2,1!=2,1='1',1='a',0='a'#字符串存在隐式转换。0='a' 如果转换不成功，则看做0
FROM DUAL;

SELECT 'a'='a','ab'='ab','a'='b'  ##两边都是字符串的话，则按照ANsi的比较规则进行比较

FROM DUAL;
#取模运算：%mod
SELECT 12%3,12%5,12 MOD -5,-12 % 5,-12% -5
FROM DUAL;#结果的符号与被模数的符号一致
#查询员工ID为偶数的员工信息
SELECT *FROM employees;
SELECT employee_id,job_id
FROM employees
WHERE employee_id%2=0;

SELECT last_name, salary
FROM employees
WHERE commission_pct=NULL;#此时执行不会有任何的结果
#<=>安全等于 可以用null来进行判断
SELECT 1<=>NULL,NULL<=>NULL
FROM DUAL;

SELECT last_name,salary,commission_pct
FROM employees
WHERE ISNULL(commission_pct);
#LEAST()\GREATEST
SELECT LEAST('g','b','t','m'),GREATEST('g','b','t','m')
FROM DUAL;

SELECT LEAST(first_name,last_name),LEAST(LENGTH(first_name),LENGTH(last_name))
FROM employees;

#BETWEEN..条件下界.AND  条件上界
SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;
#in(set)\not in(set)
SELECT employee_id, last_name, salary, manager_id
FROM   employees
WHERE  manager_id IN (110, 101, 201,103);

SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT IN (6000,7000);
#模糊查询
#练习：查询last_name中包含字符‘a’的员工信息
# %:代表不确定个数的字符（0个，1个，或多个）
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';
#练习：查询last_name中包含字符‘a’且包含字符‘e’的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' OR last_name LIKE '%e%';

#查询第2个字符是‘a’的员工信息
SELECT last_name 
FROM employees
WHERE last_name LIKE '_a%';
#练习查询第2个字符是_且第3个字符是‘a’的员工信息
#需要使用转义字符\
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a%'
#正则表达式
#REGEXP\RLIKE:正则表达式
SELECT last_name REGEXP '^K'
FROM employees;
#逻辑运算符
SELECT last_name,salary,department_id
FROM employees
#where department_id=10 and department_id=20;
WHERE department_id=50 AND salary>6000;
#XOR追求的"异"
SELECT last_name,salary,department_id
FROM employees
WHERE department_id=50 XOR salary>6000;
#练习
SELECT*FROM employees;
# 1.选择工资不在5000到12000的员工的姓名和工资
SELECT last_name,salary
FROM employees
WHERE  salary NOT BETWEEN 5000 AND 12000;

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