基础查询
1. 查询常量:
select 100;
2. 查询表达式
select 100%3;
3. 查询到单个字段
SELECT last_name FROM employees;
4. 查询多个字段
SELECT last_name,email,department_id,employee_id from employees;
5. 查询所有字段
select * from employees;
6. 查询函数(调用函数,返回数据)
-
select DATABASE() //当前所使用数据库
-
select VERSION() //查询当前数据库版号
-
select USER() //查询当前用户
7. 起别名(as)
-
select user() as 用户名 //别名包含空格或者关键字,应加引号
-
select last_name 姓 from employees;
8. 拼接(如果其中一个字段为null,全部为null)
select CONCAT(first_name,last_name) as "姓 名" from employees;
9. distinct 数据去重
select distinct department_id FROM employees;
10. 查看表结构
desc employees;
show columns from employees
条件查询
语法: 执行顺序
select 查询列 ③
from 表名 ①
where 条件 ②
按条件表达式筛选
<, >, <=, >=, =, <>(!=)
按逻辑表达式筛选
and(&&) or(||) not(!)
模糊查询(一般和通配符搭配使用)
like, in, between and, is null
-
常见的通配符:
-
_:任意单个字符
-
%:任意多个字符
-
转义字符:escape
-
in:查询某字段的值是否属于指定列表之内
= 只能判断普通内容
is 只能判断null
<=> 安全等于,都能判断
FROM employees
WHERE department_id IN (30,50,90);
SELECT *
FROM employees
WHERE job_id not in('SH_CLERK','IT_PROG');`
SELECT department_id,last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90; //30、90不能颠倒
SELECT * FROM employees
WHERE commission_pct IS NULL
排序查询
语法: 执行顺序
select 查询列表 ③
from 表名 ①
where 条件 ②
order by 排序列表 ④
升序:asc (默认)
降序:desc
-
按字段排序
SELECT * FROM employees WHERE employee_id>120 ORDER BY salary ASC;
-
按表达式排序
SELECT *, salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees WHERE commission_pct is not null ORDER BY salary*12*(1+IFNULL(commission_pct,0)) desc;
-
按别名排序
SELECT *, salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees WHERE commission_pct is not null ORDER BY 年薪 desc;
-
按函数的结果排序
SELECT * FROM employees ORDER BY LENGTH(last_name) desc;
-
按多个字段排序
SELECT last_name,salary,department_id FROM employees ORDER BY salary asc , department_id desc;
-
按列数排(哪列排序),用的较少
select * FROM employees ORDER BY 2;