这是个面向初学者的，以数据库系统基本原理书上的教务管理系统为例，主要进行实操的博客
安装sql以及相关的启用方式参考b站黑马程序员的视频
mac用户记得打开sql服务
下面是具体的sql语句，在terminal或者随便一个有图形化的软件中使用控制台复制粘贴即可
这一篇对应的是第三章基础sql，后面会更新第四章和第五章
黑马程序员视频链接：讲解难度较低，了解基本语法还行，不足以应对考试（私以为）
看看数据库和软件安装即可
mac用户参考下面的链接进行安装：
我当时弄的时候是，在跟着视频在terminal中弄完环境变量后，依旧启动不了mysql服务
后来发现应该先在系统设置中打开mysql服务
建立数据库及相关的表项
-- 首先是建立数据库并且建立相关的表项部分
-- 为了充分理解外键的作用，你可以尝试改变一下表的定义顺序，改变表外键相关的constraint信息，进行删除表项，根据数据库的报错信息理解外键约束的作用
create database college_db;
use college_db;
-- 建立各表及其之间的各种关系
create table department(
dept_name varchar(20) primary key,
building varchar(15),
budget numeric(12,2)
)comment '部门';
create table course(
course_id varchar(8),
title varchar(50),
dept_name varchar(20),
credits numeric(2,0),
primary key (course_id),
foreign key (dept_name) references department(dept_name)
)comment '课程';
create table instructor(
ID char(5) ,
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2),
primary key (ID),
foreign key (dept_name) references department(dept_name)
)comment '导师';
create table section(
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
building varchar(15),
room_number varchar(7),
time_slot_id char(4),
primary key (course_id,sec_id,semester,year),
foreign key (course_id) references course(course_id)
)comment '课程';
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(course_id,sec_id,semester,year)
)comment '教学关系';
create table student(
ID char(5),
name varchar(20) not null,
dept_name varchar(20),
total_cred int,
primary key (ID),
foreign key (dept_name) references department(dept_name)
);
create table takes(
ID char(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
grade varchar(2),
foreign key (ID) references student(ID),
foreign key (course_id,sec_id,semester,year) references section(course_id, sec_id, semester, year)
)comment '选课关系';
-- 填充数据 注意顺序
insert into department values
('Biology','Watson',90000),
('Comp.Sci.','Taylor',100000),
('Elec.Eng.','Taylor',85000),
('Finance','Painter',120000),
('History','Painter',80000),
('Music','Packard',80000),
('Physics','Watson',70000);
insert into course values
('BIO-101','Intro. to Biology','Biology',4),
('BIO-301','Genetics','Biology',4),
('BIO-399','Computational Biology','Biology',3),
('CS-101','Intro. to Computer Science','Comp.Sci.',4),
('CS-190','Game Design','Comp.Sci.',4),
('CS-315','Robotic','Comp.Sci.',3),
('CS-319','Image Processing','Comp.Sci.',3),
('CS-347','Database System Concepts','Comp.Sci.',3),
('EE-181','Intro. to Digital Systems','Elec.Eng.',3),
('FIN-201','Investment Banking','Finance',3),
('HIS-351','World History','History',3),
('MU-199','Music Video Production','Music',3),
('PHY-101','Physical Principles','Physics',4);
insert into instructor (ID, name, dept_name, salary) VALUES
('10101','Srinivasan','Comp.Sci.',65000),
('12121','Wu','Finance',90000),
('15151','Mozart','Music',40000),
('22222','Einstein','Physics',95000),
('32343','El Said','History',60000),
('33456','Gold','Physics',87000),
('45565','Katz','Comp.Sci.',75000),
('58583','Califieri','History',62000),
('76543','Singh','Finance',80000),
('76766','Crick','Biology',72000),
('83821','Brandt','Comp.Sci.',92000),
('98345','Kim','Elec.Eng.',80000);
insert into section values
('BIO-101','1','Summer',2009,'Painter','514','B'),
('BIO-301','1','Summer',2010,'Painter','514','A'),
('CS-101','1','Fall',2009,'Packard','101','H'),
('CS-101','1','Spring',2010,'Packard','101','F'),
('CS-190','1','Spring',2009,'Taylor','3128','E'),
('CS-190','2','Spring',2009,'Taylor','3128','A'),
('CS-315','1','Spring',2010,'Watson','120','D'),
('CS-319','1','Spring',2010,'Watson','100','B'),
('CS-319','2','Spring',2010,'Taylor','3128','C'),
('CS-347','1','Fall',2009,'Taylor','3128','A'),
('EE-181','1','Spring',2009,'Taylor','3128','C'),
('FIN-201','1','Spring',2010,'Packard','101','B'),
('HIS-351','1','Spring',2010,'Painter','514','C'),
('MU-199','1','Spring',2010,'Packard','101','D'),
('PHY-101','1','Fall',2009,'Watson','100','A');
insert into teaches values
('10101','CS-101','1','Fall',2009),
('10101','CS-315','1','Spring',2010),
('10101','CS-347','1','Fall',2009),
('12121','FIN-201','1','Spring',2010),
('15151','MU-199','1','Spring',2010),
('22222','PHY-101','1','Fall',2009),
('32343','HIS-351','1','Spring',2010),
('45565','CS-101','1','Spring',2010),
('45565','CS-319','1','Spring',2010),
('76766','BIO-101','1','Summer',2009),
('76766','BIO-301','1','Summer',2010),
('83821','CS-190','1','Spring',2009),
('83821','CS-190','2','Spring',2009),
('83821','CS-319','2','Spring',2010),
('98345','EE-181','1','Spring',2009);
insert into student values
('00128','Zhang','Comp.Sci.',102),
('12345','Shankar','Comp.Sci.',32),
('19991','Brandt','History',80),
('23121','Chavez','Finance',110),
('44553','Peltier','Physics',56),
('45678','Levy','Physics',46),
('54321','Williams','Comp.Sci.',54),
('55739','Sanchez','Music',38),
('70557','Snow','Physics',0),
('76543','Brown','Comp.Sci.',58),
('76653','Aoi','Elec.Eng.',60),
('98765','Bourikas','Elec.Eng.',98),
('98988','Tanaka','Biology',120);
insert into takes values
('00128','CS-101','1','Fall',2009,'A'),
('00128','CS-347','1','Fall',2009,'A-'),
('12345','CS-101','1','Fall',2009,'C'),
('12345','CS-190','2','Spring',2009,'A'),
('12345','CS-315','1','Spring',2010,'A'),
('12345','CS-347','1','Fall',2009,'A'),
('19991','HIS-351','1','Spring',2010,'B'),
('23121','FIN-201','1','Spring',2010,'C+'),
('44553','PHY-101','1','Fall',2009,'B-'),
('45678','CS-101','1','Fall',2009,'F'),
('45678','CS-101','1','Spring',2010,'B+'),
('45678','CS-319','1','Spring',2010,'B'),
('54321','CS-101','1','Fall',2009,'A-'),
('54321','CS-190','2','Spring',2009,'B+'),
('55739','MU-199','1','Spring',2010,'A-'),
('76543','CS-101','1','Fall',2009,'A'),
('76543','CS-319','2','Spring',2010,'A'),
('76653','EE-181','1','Spring',2009,'C'),
('98765','CS-101','1','Fall',2009,'C-'),
('98765','CS-315','1','Spring',2010,'B'),
('98988','BIO-101','1','Summer',2009,'A'),
('98988','BIO-301','1','Summer',2010,null);删除表项
方便尝试，鼓励试错，了解数据库实现细节
-- 删除表(重制) 注意顺序
drop table teaches;
drop table section;
drop table instructor;
drop table course;
drop table department;基础的语法
数据库的基本操作，增删查改
对于书上的内容有一定的删改，主要体现在嵌套子查询那里，删改的也不多
如果完全按照和书上sql语句一模一样敲上去会发现，有的甚至连语法都是不对的，虽然很少
-- SQL具体操作
-- 基础语法篇
select name from instructor;
select dept_name from instructor;
select distinct dept_name from instructor;
select all dept_name from instructor;
-- 可以运算
select ID,name,dept_name,salary*1.1 from instructor;
-- 多关系查询
select name,instructor.dept_name,building
from instructor,department
where department.dept_name=instructor.dept_name;
select name,title
from instructor natural join teaches,course
where teaches.course_id=course.course_id;
-- 字符串模糊匹配 like _ %
select dept_name,building
from department
where building like '_atson%';
-- order by group by having 聚集函数
-- 注意顺序
-- order by 默认asc 不是desc，降序
select name
from instructor
where dept_name='Physics'
order by name asc;
select *
from instructor
where dept_name='Physics'
order by salary desc,name asc;
-- 集合运算
-- intersect union except
(
select course_id
from section
where semester='Fall'and year=2009
)
intersect
(
select course_id
from section
where semester='Spring' and year=2010
);
-- 聚集函数
select avg(salary)
from instructor
where dept_name='Comp.Sci.';
select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name;
select dept_name,count(distinct ID) as instr_count
from instructor natural join teaches
where semester='Spring' and year=2010
group by dept_name;
-- 嵌套子查询 集合操作
-- in/not in
select distinct course_id
from section
where semester='Fall' and year=2009 and
course_id not in(
select distinct course_id
from section
where semester='Spring' and year=2010
);
select count(distinct ID)
from takes
where (course_id,sec_id,semester,year) in (
select course_id,sec_id,semester,year
from teaches
where teaches.ID='10101'
);
-- some/all
select instructor.name
from instructor
where salary>some(
select salary
from instructor
where dept_name='Biology'
);
select instructor.name
from instructor
where salary>all(
select salary
from instructor
where dept_name='Biology'
);
-- exists/not exists
select course_id
from section as S
where semester='Fall' and year=2009 and exists(
select *
from section as T
where semester='Spring' and year=2010 and S.course_id=T.course_id
);
-- 子查询的返回结果不同
-- 返回结果具体结合select的内容来看
select T.course_id
from course as T
where (select count(*) from section as R where R.year=2009 and T.course_id=R.course_id)=1;
-- form子句中的子查询
-- 因为无论是where还是from后面其实跟的都是和关系集合相关的内容
select dept_name,avg_salary
from (select dept_name,avg(salary) as avg_salary from instructor group by dept_name) as dept_avg
where avg_salary>42000;
-- 注意子查询必须存在标别名，否则会报语法错误
-- 以下直接使用聚集函数也可以获得相同的结果
select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary)>42000;
select max(tot_salary)
from (select dept_name,sum(salary)
from instructor
group by dept_name)as dept_total (dept_name,tot_salary);
-- 但是这样查询只能查到总工资，而不能显示相应的dept_name
-- 修改成下面的方式，可以查找的更精准
select dept_name,tot_salary
from (select R.dept_name,sum(R.salary)
from instructor as R
group by R.dept_name) as dept_total(dept_name,tot_salary)
where tot_salary=(select max(tot_salary)
from (select sum(T.salary) as tot_salary
from instructor as T
group by T.dept_name)as temp
);
-- 可以看出来还是比较复杂的，而且还有重复的sql段，效率不是很高
-- 但是通过 lateral 关键字作为前缀就可以访问到其他数据
select name,salary,avg_salary
from instructor I1,lateral(select avg(salary) as avg_salary
from instructor I2
where I2.dept_name=I1.dept_name)as temp;
-- with子句 with实际上提供了一个定义临时关系的方法,类似函数
with max_budget(value) as
(select max(budget)from department)
select budget
from department,max_budget
where department.budget=max_budget.value;
-- 所有 工资总额 大于 所有系平均工资总额 的系
with dept_total(dept_name,value)as(
select dept_name,sum(salary)
from instructor
group by dept_name
),
dept_total_avg(value)as(
select avg(value)
from dept_total
)
select dept_name,dept_total.value
from dept_total,dept_total_avg
where dept_total.value>=dept_total_avg.value;
-- 面向过程编程，将相应的过程转化成函数
-- 数据库的修改
-- delete子句删除操作 delete from where
-- insert into插入 insert into course (, , , ,) values(, , , ,)
-- 前面定义插入顺序，后面定义插入的值，注意约束信息
-- 更新 update子句 update table_name set table_data =... where ..condition
-- 这里只举update子句的例子
update instructor
set salary=salary*1.05;
update instructor
set salary=salary/1.05;
-- update 结合case
update instructor
set salary = case
when salary<=100000 then salary*1.05
else salary*1.03
end;
-- case语句可以跟很多行when...then...
扫一扫
2523
到【灌水乐园】发言
