mysql 学习笔记2

1.构建一个大学数据库，并完成课本的一些习题

一开始外间约束会出错，在每个表上加入engine=myisam可以解决

create table classroom
	(building		varchar(15),
	 room_number		varchar(7),
	 capacity		numeric(4,0),
	 primary key (building, room_number)
	)engine=myisam;

create table department
	(dept_name		varchar(20), 
	 building		varchar(15), 
	 budget		        numeric(12,2) check (budget > 0),
	 primary key (dept_name)
	)engine=myisam;

create table course
	(course_id		varchar(8), 
	 title			varchar(50), 
	 dept_name		varchar(20),
	 credits		numeric(2,0) check (credits > 0),
	 primary key (course_id),
	 foreign key (dept_name) references department
		on delete set null
	)engine=myisam;
create table instructor
	(ID			varchar(5), 
	 name			varchar(20) not null, 
	 dept_name		varchar(20), 
	 salary			numeric(8,2) check (salary > 29000),
	 primary key (ID),
	 foreign key (dept_name) references department
		on delete set null
	)engine=myisam;

create table section
	(course_id		varchar(8), 
         sec_id			varchar(8),
	 semester		varchar(6)
		check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), 
	 year			numeric(4,0) check (year > 1701 and year < 2100), 
	 building		varchar(15),
	 room_number		varchar(7),
	 time_slot_id		varchar(4),
	 primary key (course_id, sec_id, semester, year),
	 foreign key (course_id) references course
		on delete cascade,
	 foreign key (building, room_number) references classroom
		on delete set null
	)engine=myisam;

create table teaches
	(ID			varchar(5), 
	 course_id		varchar(8),
	 sec_id			varchar(8), 
	 semester		varchar(6),
	 year			numeric(4,0),
	 primary key (ID, course_id, sec_id, semester, year),
	 foreign key (course_id,sec_id, semester, year) references section
		on delete cascade,
	 foreign key (ID) references instructor
		on delete cascade
	)engine=myisam;

create table student
	(ID			varchar(5), 
	 name			varchar(20) not null, 
	 dept_name		varchar(20), 
	 tot_cred		numeric(3,0) check (tot_cred >= 0),
	 primary key (ID),
	 foreign key (dept_name) references department
		on delete set null
	)engine=myisam;

create table takes
	(ID			varchar(5), 
	 course_id		varchar(8),
	 sec_id			varchar(8), 
	 semester		varchar(6),
	 year			numeric(4,0),
	 grade		        varchar(2),
	 primary key (ID, course_id, sec_id, semester, year),
	 foreign key (course_id,sec_id, semester, year) references section
		on delete cascade,
	 foreign key (ID) references student
		on delete cascade
	)engine=myisam;

create table advisor
	(s_ID			varchar(5),
	 i_ID			varchar(5),
	 primary key (s_ID),
	 foreign key (i_ID) references instructor (ID)
		on delete set null,
	 foreign key (s_ID) references student (ID)
		on delete cascade
	)engine = myisam;

create table time_slot
	(time_slot_id		varchar(4),
	 day			varchar(1),
	 start_hr		numeric(2) check (start_hr >= 0 and start_hr < 24),
	 start_min		numeric(2) check (start_min >= 0 and start_min < 60),
	 end_hr			numeric(2) check (end_hr >= 0 and end_hr < 24),
	 end_min		numeric(2) check (end_min >= 0 and end_min < 60),
	 primary key (time_slot_id, day, start_hr, start_min)
	)engine=myisam;

create table prereq
	(course_id		varchar(8), 
	 prereq_id		varchar(8),
	 primary key (course_id, prereq_id),
	 foreign key (course_id) references course
		on delete cascade,
	 foreign key (prereq_id) references course
	)engine=myisam;

两道经典例题：

3.1
	a. 
	select title from course 
	where credits >= 3 && dept_name = 'Comp. Sci.';

	b.
	select name from student natural join takes
	where course_id = (select course_id from instructor natural join teaches 
	where name = 'Einstein');

	c.
	select name
	from instructor
	where salary >= all (select salary from instructor);

	d.
	select salary 
	from instructor
	where salary >= all (select salary from instructor);

	e.
	select title, count(course_id) as num
	from takes natural join course 
	where semester = 'Fall' and year = 2009 
    group by title;

	f.
	with course2009fall (title, num) as 
	(select title, count(course_id) as num) 
	 from takes natural join course
	 where semester = 'Fall' and year = 2009
	 group by title)

	 select title, num
	 from course2009fall 
	 where num >= all(select num from course2009fall);

3.11

	a.
	select distinct name 
	from student natural join takes
    where dept_name = 'Comp. Sci.';
	
	b.
	select distinct ID, name 
    from student natural join takes
    where ID not in (select distinct ID
	from student natural join takes
    where semester = 'Fall' and year <= 2009);

	c.
	select dept_name, max(salary) as max_salary 
	from instructor group by dept_name;

	d.
	with max_salary_per_dept as (
	select dept_name, max(salary) as max_salary
	from instructor group by dept_name)
	select min(max_salary) as min_max_salary
	from max_salary_per_det;

	//ver2
	select min(max_salary) 
	from (select max(salary) as max_salary 
			from instructor group by dept_name) as a;

3.12
	a.
	insert into course
	value('CS-001', 'Weekly Seminar', 'Comp. Sci.', 0);

	b.
	insert into section
	value('CS-001', '1');
	
	c.
	insert into takes 
	select T.ID, 'CS-001', '1', 'Fall', 2009, NULL
	from student as T
	where T.dept_name = 'Comp. Sci.';

	d.
	delete from takes
	where ID = (select ID 
	from student where name = 'Chavez') and course_id = 'CS-001'

	e.
	delete from course where course_id = 'CS-001';
	
	f.
	delete from takes
	where course_id = (select course_id from course where title = '%database%')