选取employee表中的姓名和年龄
select age,name from employee
选取大于25岁的人的姓名与年龄
select age,name from employee where age>25
选取Mary的姓名年龄及电话
select age,phone from employee where name='Mary'
选取小于30岁或大于25岁的信息
select age,name from employee where age<30 or age>25
选取age>25并且小于30的
SELECT name,age FROM employee WHERE age>25 AND age<30;
SELECT name,age FROM employee WHERE age between 25 and 30;
选取在dpt3，dpt4的人的信息
选取不在dpt1，dpt3的人的信息
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');
选取电话开头四位为1101的人的信息
SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';
选取姓名开头是J的人的信息
SELECT name,age,phone FROM employee WHERE name LIKE 'J%';
按薪资降序排列员工信息
SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;
SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;
求最大薪资和最少薪资
Tom所在部门做的工程数量
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');
各员工所在部门的人数
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
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;
查询出各员工所在部门的人数与工程数
SELECT name, people_num, COUNT(proj_name) AS count_project FROM employee, department, project WHERE in_dpt = dpt_name and of_dpt = dpt_name GROUP BY name, people_num;
