“数据库系统实验”实验报告

SELECT 语句详解

image.png 选取employee表中的姓名和年龄 select age,name from employee image.png 选取大于25岁的人的姓名与年龄 select age,name from employee where age>25 image.png 选取Mary的姓名年龄及电话 select age,phone from employee where name='Mary' image.png 选取小于30岁或大于25岁的信息 select age,name from employee where age<30 or age>25 image.png 选取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; image.png 选取在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'); image.png 选取电话开头四位为1101的人的信息 SELECT name,age,phone FROM employee WHERE phone LIKE '1101__'; 选取姓名开头是J的人的信息 SELECT name,age,phone FROM employee WHERE name LIKE 'J%'; image.png 按薪资降序排列员工信息 SELECT name,age,salary,phone FROM employee ORDER BY salary DESC; image.png SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee; 求最大薪资和最少薪资 image.png 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'); image.png 各员工所在部门的人数 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; image.png 查询出各员工所在部门的人数与工程数 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;

最新评论
暂无评论~