数据库基本命令（一）

#删除数据库employees
DROP DATABASE employees;
#创建数据库
CREATE DATABASE XXX;
#数据库对大小写不敏感
#SELECT选择要查什么列的数据，每个列用，分割
#FROM选择从哪个表来查询 EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY为列名，t_employees为表明
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY FROM t_employees;
#如果要查询所有的数据需要把所有的列名全部写出来，也可以用*号代替
SELECT * FROM t_tmployees;
#改变列名,也可以省略AS
SELECT
FIRST_NAME AS '名',LAST_NAME AS '姓'，SALARY AS '年薪'
FROM t_employees;
#当需要引用不同的表时，可以给表设置别名，并且要加上``
SELECT
e.`FIRST_NAME`
FROM t_employees AS e;
#排序查询，关键字ORDER BY 默认为升序，ASC升序，DESC降序
SELECT
*
FROM t_employees ORDER BY SALARY DESC;
#多条件查询，前面查询的时候相同的话，按后面的规则去排序
SELECT
*
FROM t_employees ORDER BY SALARY ASC,HIRE_DATE DESC;
#条件查询
#数值
SELECT
*
FROM t_employees WHERE SALARY=12000;
#字符串
SELECT
*
FROM t_employees WHERE HIRE_DATE='IT_PROG';
#日期类型
SELECT
*
FROM t_employees WHERE HIRE_DATE='1997-03-10';
#并且  AND,也可以用&&（不建议）
SELECT
*
FROM t_employees WHERE SALARY = 12000 AND COMMISSION_PCT=0.3;
#或者  OR,也可以用||
SELECT
*
FROM t_employees WHERE SALARY = 12000 OR COMMISSION_PCT=0.3;
#关系判断符
#入职时间在2000-1-1后
SELECT
*
FROM t_employees WHERE HIRE_DATE >='2000-1-1';
#查询工资大于10000
SELECT 
*
FROM t_employees WHERE SALARY>10000;
#查询工资是3200,并且工作不是SH_CLERK
SELECT 
* 
FROM t_employees WHERE SALARY = 3200 AND JOB_ID!='SH_CLERK';
SELECT 
* 
FROM t_employees WHERE SALARY = 3200 AND JOB_ID<>'SH_CLERK';
#查询工资在5000-8000
SELECT 
* 
FROM t_employees WHERE SALARY <=8000 AND SALARY >=5000;
#区间判断使用关键字BETWEEW
SELECT * FROM t_employees WHERE SALARY BETWEEN 5000 AND 8000
#判断空值  null
SELECT * FROM t_employees WHERE COMMISSION_PCT IS NULL;
#判断非空 is not null
SELECT * FROM t_employees WHERE COMMISSION_PCT IS NOT NULL ORDER BY COMMISSION_PCT DESC;
#查询部门编号是 10 20 30 所有员工
SELECT * FROM t_employees WHERE DEPARTMENT_ID =10 OR DEPARTMENT_ID =20 OR DEPARTMENT_ID =30;
# in 相当多个or的条件拼接
SELECT * FROM t_employees WHERE DEPARTMENT_ID IN (10,20,30);
#模糊查询 关键字LIKE  匹配1个字符 _(1个)__(俩个)   匹配0-n个字符%(0-n多个)
SELECT * FROM t_employees WHERE FIRST_NAME LIKE 'D%';
SELECT * FROM t_employees WHERE FIRST_NAME LIKE '%l';