SQL语句 查询select以及常用函数的基础用法

SELECT * FROM companydb.employees;

SELECT employee_id AS 员工编号,first_name,last_name,phone_number,
salary 工资,'china' AS country
FROM employees
-- distinct 去重
SELECT DISTINCT job_id
FROM employees
-- where 条件判断
-- 查询公司所有100号部门员工的信息
SELECT *
FROM employees
WHERE  department_id =100
-- 查询工资在10000以上的员工
SELECT *
FROM employees
WHERE  salary >10000;

SELECT * FROM employees 
WHERE NOT salary <10000

-- 逻辑运算 and or not
SELECT * FROM employees 
WHERE department_id =80 AND salary>10000

SELECT * FROM employees 
WHERE department_id =80 OR salary>10000

SELECT * FROM employees 
WHERE salary BETWEEN 2000 AND 15000

SELECT * FROM employees
WHERE department_id = 50 OR department_id =100

SELECT * FROM employees
WHERE department_id IN (10,50,80 ,100)

-- like运算符 一定要和通配符结合使用% _
SELECT * 
FROM employees
WHERE phone_number LIKE '650%2__'


SELECT  * FROM employees
WHERE commission_pct IS NULL

SELECT  * FROM employees
WHERE commission_pct IS NOT NULL

-- order by 对查询结果按照指定的列进行大小排序
-- asc 升序  DESC降序
SELECT * FROM employees
ORDER BY salary DESC

SELECT * FROM employees
ORDER BY salary ASC

SELECT * FROM employees 
WHERE salary >5000 AND department_id IS NOT NULL
ORDER BY department_id ASC ,salary DESC
-- limit m,n m其中表示跳过的记录数 n表示截取多少条
SELECT * FROM employees
ORDER BY salary DESC 
LIMIT 3,3

-- 内置函数
-- 1.数值函数
SELECT ABS(-10) -- 求绝对值
SELECT SQRT(99) -- 开平方根
SELECT ROUND(42.888,1) -- 四舍五入
SELECT CEILING(4.6) -- 向上取整
SELECT FLOOR(3.6) -- 向下取整
-- 2.字符函数
SELECT LENGTH('hello') -- 获取字符串长度
SELECT LEFT('heelo' ,2) -- 截取左边的字符
SELECT RIGHT('hello' ,3) -- 截取邮编的字符
SELECT SUBSTR('hello' ,2,2) -- 截取字符从下标位置开始截取几个
SELECT TRIM(' hello ') -- 去除空格
SELECT CONCAT('hello','world','!')-- 字符串拼接

-- 3.日期函数
SELECT CURDATE() -- 获取系统当前日期
SELECT CURTIME() -- 获取当前时间
SELECT NOW() -- 获取日期时间
SELECT YEAR(NOW()) -- 获取年份
SELECT MONTH(NOW()) -- 获取月份
SELECT DAY(NOW()) -- 获取日
SELECT DAYOFWEEK(NOW()) -- 获取在一个星期中的第几天
SELECT DAYOFMONTH(NOW()) -- 获取在一个月中的第几天
SELECT DAYOFYEAR(NOW()) -- 获取在一年中的第几天
-- 日期的加减运算 日期常量

SELECT DATE_ADD('2022-9-27', INTERVAL 5 DAY)
SELECT DATEDIFF(NOW(),'2022-10-1')

SELECT employee_id,CONCAT(first_name,'-',last_name) AS full_name,
salary AS old_salary,IF(department_id=50,salary*2,salary+1000) AS new_salary,department_id
FROM employees

SELECT * FROM employees
WHERE DATEDIFF(NOW(),hiredate)>7

-- 需求：查询每个员工的编号以及工资和绩效
-- 要求如果绩效为NULL则在结果中显示0.0
-- 第一种写法：
SELECT employee_id,salary,IF(commission_pct IS NULL,0.0,commission_pct) 
FROM employees

SELECT employee_id,salary,IFNULL(commission_pct,0.0) FROM employees

-- 需求：查询每个员工的编号、工资以及对应的级别
-- 20000以上A 15000-20000 B 10000-15000 C 5000-10000 D 5000以下 E
SELECT employee_id,salary,(
CASE WHEN salary>=20000 THEN 'A'
	WHEN salary >=15000 THEN 'B'
	WHEN salary >=10000 THEN 'C'
	WHEN salary >=5000 THEN 'D'
	ELSE 'E'
END
) AS salary_level
FROM employees