MySQL-day1
date
Jun 3, 2023
slug
MySQL
status
Published
tags
数据库
summary
type
Post
#查询
#单字段
SELECT last_name FROM employees;
#多字段
SELECT last_name,salary,email FROM employees;
#所有字段
SELECT * FROM employees;
#查询常量
SELECT 100;
SELECT 'john';
#表达式
SELECT 100*98;
#函数
SELECT VERSION();
#别名
#如果查询的字段有重名的情况，使用别名可以区分开来
SELECT 100%98 AS 结果 ;
SELECT last_name AS 姓 FROM employees;
SELECT last_name 姓 , first_name 名 FROM employees;
SELECT salary AS "out put" FROM employees;
#去重
#查询所有部门编号
SELECT DISTINCT department_id FROM employees;
#+只能是运算符
/*
SELECT 100+99;
SELECT '123'+100; 223
SELECT 'john'+100; 0+100
SELECT null+100; null
*/
#员工名和姓
SELECT CONCAT('last_name','first_name') AS 姓名 FROM employees;
#IFNULL
SELECT
IFNULL( commission_pct, 0 ) AS 奖金率,
commission_pct
FROM
employees;
/*
条件查询
SELECT
FROM
WHERE
筛选条件
分类 ：
1.条件表达式筛选
条件运算符：> <
2.逻辑表达式筛选
AND
OR
NOT
3.模糊查询
LIKE
BETWEEN AND
IN
ISNULL(expr)
- /
#1
SELECT
*
FROM
employees
WHERE
salary > 12000;
SELECT
last_name,
department_id
FROM
employees
WHERE
department_id<> 90;
#department_id!= 90;
#2
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE
salary >= 10000
AND salary <= 20000
SELECT
*
FROM
employees
WHERE
department_id < 90 OR department_id > 110
OR salary > 15000;
#LIKE
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%';#%通配符 多个或0个 _ 通配单个
#员工名中第三个字符为e,第五个字符为a
SELECT
last_name,
salary
FROM
employees
WHERE
last_name LIKE '__e_a%';
#last_name LIKE '$_e' ESCAPE '$';查_e
#BETWEEN AND
SELECT
*
FROM
employees
WHERE#employee_id>=100 AND employee_id<=120;
employee_id BETWEEN 100 AND 120;#包含区间
#IN
#查寻工种编号job_id
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN ( 'IT_PROT', 'AD_VP', 'AD_PRES' );
#is NULL
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NULL;#= <> 不能判断
#<=>
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct <=> NULL ;
SELECT
last_name,
salary
FROM
employees
WHERE
salary <=> 12000;
#查询员工号为176的员工的姓名和部门号和年薪
SELECT
last_name,
department_id,
salary * 12 *(1+IFNULL ( commission_pct, 0 )) 年薪
FROM
employees;