MySQL基础（三）SELECT语句详解

1.基本select语句

SELECT name,age FROM employee;

2.数学符号条件

SELECT name,age FROM employee WHERE age>25;

SELECT name,age,phone FROM employee WHERE name='Mary';

3.'AND'与'OR'

SELECT name,age FROM employee WHERE age<25 OR age>30;
SELECT name,age FROM employee WHERE age>25 AND age<30; 不包括25和30

SELECT name,age FROM employee WHERE age BETWEEN 25 AND 30; 包括25和30

4.'IN'与'NOT IN'

SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');

SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1','dpt3');

5.通配符

SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';  _代表一个未知字符

SELECT name,age,phone FROM employee WHERE name LIKE 'J%';  %代表不定个未知字符

6.对结果排序

SELECT name,age,salary,phone FROM employee ORDER BY salary; 默认升序，相当于省略ASC

SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;

7.SQL内置函数和计算

SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee; AS重命名

8.子查询

SELECT of_dpt,COUNT(proj_name) AS count_project FROM project
WHERE of_dpt IN

(SELECT in_dpt FROM employee WHERE name='Tom');

9.连接查询

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;

练习：

使用连接查询的方式，查询出各员工所在部门的人数与工程数，工程数命名为 count_project。（连接3个表）

select name,people_num,count(proj_name) as count_project 
from employee join department 
on employee.in_dpt = department.dpt_name 
join project 
on employee.in_dpt=project.of_dpt