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; 默认升序,相当于省略ASCSELECT 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 projectWHERE of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');
9.连接查询
SELECT id,name,people_numFROM employee,department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;
SELECT id,name,people_numFROM 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_projectfrom employee join department
on employee.in_dpt = department.dpt_name
join project
on employee.in_dpt=project.of_dpt