select 语句详解

1. select 的基本语法：

select 列名 from 表名 where 条件
条件： <、 > 、= 、 <=、>=、 between、 and、or、like(_、%)

• 一般查找

-- 查找所有列
select * from employee;
-- 按照名字查找
select name, age, phone from employee where name='Tom';

-- 按照年龄查找
select name, age, phone from employee where age>=25 and age<=30;
select name, age, phone from employee where age between 25 and 30;

-- 反向查找
select name, age, phone from employee where name not in('Tom', 'Jack');
select name, age, phone from employee where name in('Tom', 'Jack');
 

-- 模糊查找
select name, age, phone from employee where name like 'T__';  -- '_'表示一个字符
select name, age, phone from employee where name like 'T%';  --'_'表示不定个字符

• 查找结果排序

select name, age, phone from employee where name='Tom' order by name ASC;
select name, age, phone from employee where name='Tom' order by name DESC;

• 内置函数

函数名	作用
count	计数
sum	求和
avg	求平均值
max	最大值
min	最小值

select max(selery) as max_selery, min(selery) as min_selery from employee;

• 子查询(多表查询)

-- 名为 "Tom" 的员工所在部门做了几个工程。
-- 员工信息储存在 employee 表中，
-- 但工程信息储存在 project 表中。
SELECT of_dpt,COUNT(proj_name) AS count_project FROM project GROUP BY of_dpt
HAVING of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');

-- 各员工所在部门的人数，
-- 其中员工的 id 和 name 来自 employee 表，
-- people_num 来自 department
SELECT id,name,people_num
FROM employee,department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;

SELECT id,name,people_num
FROM employee JOIN department
ON employee.in_dpt = department.dpt_name
ORDER BY id;