数据库实验三-单表查询

#选择当前数据库
USE hisdb;

#查询内科一生的基本信息
SELECT * FROM doctor WHERE Department='内科';


#查询年龄大于30岁的女病人的姓名，性别，年龄，电话
SELECT pname,sex,DATEDIFF((CURDATE(),str(birth))) AS age,tel
FROM patient
WHERE DATEDIFF(CURDATE(),str(birth))>30 AND sex='女';

#查询所有姓李的李胜
SELECT * FROM doctor WHERE dname LIKE '李%';

#查询没有助手的医生的基本信息
SELECT * FROM doctor WHERE Assistant='';

#查询病人的就诊信息,查询的结果按照就诊费用降序排列
SELECT * FROM curefee ORDER BY fee DESC;

#统计科室医生人数超过1个的科室
SELECT Department,COUNT(*) AS members FROM doctor GROUP BY Department;