#删除数据库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';
数据库基本命令(一)
最新推荐文章于 2024-09-06 23:32:02 发布