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
05-24
1万+
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
09-29
5123
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)