MYSQL

MYSQL(一)

1、基础查询

USE myemployees

#查询结果是虚拟表格
SELECT last_name FROM employees;

SELECT last_name ,salary,email FROM employees;

SELECT * FROM  employees;

#查询常量值
SELECT 100;

SELECT 'john';

#查询表达式
SELECT 100+100;

#查询函数
SELECT version();

2、起别名

#起别名  AS
SELECT 100 + 10 AS result;

#方式一
SELECT last_name AS '姓',first_name AS '名' FROM employees;

#方式二
SELECT last_name 姓, first_name 名 FROM employees;

#特殊符号需要单引号或者双引号包括
SELECT salary AS 'out put' FROM employees;

3、去重，合并

#去重	DISTINCT
SELECT DISTINCT department_id FROM employees;

#合并字段
SELECT CONCAT('a','b','c') AS result;

SELECT CONCAT(last_name,first_name) AS '姓名' FROM employees;

4、条件表达式

#条件表达式  = >= <= != 
SELECT * FROM employees WHERE salary > 12000;

# != 和 <> 相等
SELECT DISTINCT last_name,department_id FROM employees WHERE department_id != 90;

SELECT last_name, salary, commission_pct FROM employees WHERE salary >= 10000 AND salary <= 20000;
#同样的效果
SELECT last_name ,salary ,commission_pct FROM employees WHERE salary BETWEEN 10000 AND 20000;

#SELECT * FROM employees WHERE NOT(department_id BETWEEN 90 AND 110);
#同样的效果
SELECT * FROM employees WHERE department_id >= 90 AND department_id <= 110;

SELECT * FROM employees WHERE NOT(department_id >= 90 AND department_id <=100);

1、like

#关键字like  % 任意多个字符 
SELECT * FROM employees WHERE last_name LIKE '%a%';

SELECT * FROM employees WHERE last_name LIKE '__n_l%';

SELECT last_name FROM employees WHERE last_name LIKE '_\_%';

#可以标明转义字符 ESCAPE '$'
SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';

2、between and

#between and  关键字
SELECT * FROM employees WHERE department_id >= 90 AND department_id <= 110;

SELECT last_name, salary, commission_pct FROM employees WHERE salary >= 10000 AND salary <= 20000;

SELECT department_id FROM employees WHERE department_id BETWEEN 100 AND 110;

3、in

#in关键字
SELECT last_name, job_id FROM employees WHERE job_id IN ('It_PROG', 'AD_VP', 'AD_PRES');

#   =  不能判断 null 可以使用is 关键字
SELECT last_name, commission_pct FROM employees WHERE commission_pct is NULL;

#安全等于(可读性差)
SELECT last_name, commission_pct FROM employees WHERE commission_pct <=> NULL;

# IFNULL(commission_pct,0) 如何是null 就用后面0代替
SELECT last_name, department_id, salary*12*(1 + IFNULL(commission_pct,0)) AS '年薪' FROM employees;