知识久了不用就会忘记,今早起床突然发现自己连最基本的sql语句都不会写了。
复习了半天,好记性不如烂键盘,做个复习笔记。
SQL 命令动词:
数据定义:CREATE、DROP、ALTER
数据操作:SELECT、INSERT、UPDATE、DELTE
数据控制:GRANT、REVOKE
-- Table "student" DDL
CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Table "courses" DDL
CREATE TABLE `courses` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Table "student_course" DDL
CREATE TABLE `student_course` (
`sid` int(11) NOT NULL,
`cid` int(11) NOT NULL,
PRIMARY KEY (`cid`,`sid`),
KEY `FKB0A3729FDBF3B7BF` (`sid`),
KEY `FKB0A3729FE343F963` (`cid`),
CONSTRAINT `FKB0A3729FDBF3B7BF` FOREIGN KEY (`sid`) REFERENCES `student` (`id`),
CONSTRAINT `FKB0A3729FE343F963` FOREIGN KEY (`cid`) REFERENCES `courses` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Table "department" DDL
CREATE TABLE `department` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Table "employee" DDL
CREATE TABLE `employee` (
`id` int(11) NOT NULL,
`name` varchar(10) NOT NULL,
`id_department` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `emp_id__dep_id` (`id_department`),
CONSTRAINT `emp_id__dep_id` FOREIGN KEY (`id_department`) REFERENCES `department` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#1统计每个部门的员工数
select d.* ,e.num from department d
left join (
select id_department,count(*) num from employee group by id_department
)e on e.id_department= d.id
#2统计选修了某一门课程的学生人数(结果集中不包含课程名)
select cid,count(sid) from student_course group by cid
#3统计选修了某一门课程的学生人数(结果集中包含课程id 、课程名、选修人数)
select c.id ,c.name , num as "选修人数" from courses c
left join (
select cid,count(*) num from student_course group by cid
)sc on c.id=sc.cid
#4统计选修课程数超过三门的学生学号
select sid from student_course group by sid having count(*) >3
#5统计选修课超过三门课的学生(学号、姓名)
select id,name,sc.num as "选修课程数量" from student s
left join (
select sid,count(sid) num from student_course group by sid having count(*) >3
)sc on s.id=sc.sid
where sc.num>3
#6统计选修了某门课程的学生人数
select count(* ) as "选修了课程5的人数" from student_course where cid=5
#7求学生总数
select count(*) from 学生
#8求选修了课程的学生人数(distinct 去除重复的sid)
select count(distinct sid) from student_course