建立数据库
create database zhouzhuo
建立学生表
create table student
(student_number char(9) primary key,
student_name char(20),
student_sex char(2),
student_age smallint,
student_depart char(4)
);
删除表
drop table stuent;
查询表的所有内容
select * from student;
插入数据
insert into student values('200215121','李勇','男','20','CS');
insert into student values('200215122','刘晨','女','19','CS');
insert into student values('200215123','王敏','女','18','MA');
insert into student values('200215125','张立','男','19','IS');
创建一张课程表
create table course(
course_number char(4) primary key,
course_name char(20),
primary_course char(4)constraint PK_course foreign key references course(course_number),
course_credit smallint)
向课程表中插入数据
insert into course values('1','数据库','5',4);
insert into coursevalues('2','数学',null,2);
insert into coursevalues('3','信息系统','1',4);
insert into coursevalues('4','操作系统','6',3);
insert into coursevalues('5','数据结构','7',4);
insert into coursevalues('6','数据处理',null,2);
insert into coursevalues('7','Pascal语言','6',4);
--注意:上面的插入数据有技巧,要先插入先修课是null的,这些语句要服从完整性约束
建立学生_课程表
create table student_course(
student_number char(9) foreign key references student(student_number),
course_number char(4) foreign key references course(course_number),
primary key(student_number,course_number),
grade smallint);
插入数据
insert into student_coursevalues('200215121','1',92);
insert into student_coursevalues('200215121','2',85);
insert into student_coursevalues('200215121','3',88);
insert into student_coursevalues('200215122','2',90);
insert into student_coursevalues('200215122','3',80);
修改表
(1)增加列 增加学生表中入学年份一列
alter table stu add student_entrancedate char(10);
(2) 删除表 注意:当要删除的表中某一列被其他表所引用时,要先删除所引用的表才能删除此表
drop table stu;
部分查询
select student_name,student_sex,student_agefrom stu;
select distinct student_number from student_course
--distinct表示唯一显示学号
--注意:在有些元组的某些属性上若存在多个null,distinct可以只显示一个空
--使用distinct时可以将两个或多个属性同时执行,得出来的结果为其属性组的唯一表达式
--eg 唯一查询学生选课表中学生号和分数
select distinct student_number,gradefrom student_course;
select student_number from student_course
--没有distinct表示显示所有学号与下面的语句等价
select all student_number from student_course
--条件查询 eg1
select student_namefrom studentwhere student_depart='CS';
--eg2
select student_name,student_agefrom studentwhere student_age<20;
--eg3
select distinct student_number,gradefrom student_coursewhere grade<60;
--唯一查询不及格的学生
--查询~22岁之间的学生的姓名,系别,年龄
select student_name,student_depart,student_agefrom studentwhere student_age between 20 and 22;
--可以否定地使用between and 语句
select student_name,student_depart,student_agefrom studentwhere student_age not between 20 and 22;
--查询属于某个属性的元组例:查询CS,MA,IS,系的所有学生的元组
select student_name,student_sex,student_departfrom studentwhere student_depart in('CS','MA','IS');
--模糊匹配
--用like进行字符串匹配
--eg 查询学生的详细情况
select * from studentwhere student_number like '200215121';
--等价于下面命令
select * from studentwhere student_number = '200215121';
--eg 用%,%可以任意长度
select student_name,student_number,student_sexfrom studentwhere student_name like '刘%';
--可以否定like 查询 eg
select student_name,student_number,student_sexfrom studentwhere student_name not like '刘%';
--使用模糊查询时可以限定查询的要范围,用[a-b]表示要查询的字段在a-b范围内
--用[a,b]表示要查询的字段为a或者b
--用[a-b]表示要查询的字段在a-b范围内
--用[^a-b]表示要查询的字段不在a-b范围内--- ' ^ '为异或符Ctrl+6
--eg 输出课程表里课程号为含有或者的课程
select *from coursewhere course_number like '[3,7]'
--eg 输出课程表里课程号为第一个字符含有-7的课程
select *from coursewhere course_number like '[3-7]%'
--eg 输出课程表里课程号为第一个字符不含有-7的课程
select *from coursewhere course_number like '[^3-7]%'
--总结:要匹配的符号均要用单引号括起来
--如果用户要查询的字符串本身就包含通配符%或_,这时就要使用escape'<换码符>'短语
--eg 先建一个含有通配符的元组
insert studentvalues('10001','刘_','男',18,'CS');
--再查询
select student_number,student_sexfrom studentwhere student_name like '刘\_'escape '\';
--在这里,escape '\' 表明\号做转义字符,\后面的符号_为普通字符
空值的查询
实例:某学生没有参加选修课的考试,则其考试成绩为NULL(注意是为空,不是为)要求查询其学生的学号和对应的课程号
--eg 先建一个分数为空的元组
select *from student_course;insert student_coursevalues('10001','1',null);
--查询分数为空的学生
select student_number,course_numberfrom student_coursewhere grade is null;
--注意:is 不能用 = 代替
多重条件查询
运用逻辑运算符and ,or可以联结多个查询条件and的优先级高于or 但可以用括号改变其优先级
--eg 查询计算机系年龄在岁以下的学生名字
select student_namefrom studentwhere student_depart='CS'and student_age<20;
--在使用or语句时如果条件比较多时,可以用in语句简化
--eg 查询计算机系,数学系,信息系学生的姓名和性别
select student_name,student_sex from student where student_depart='CS'or student_depart='MA'or student_depart='IS';
--上面的语句等价于下面的语句
select student_name,student_sexfrom studentwhere student_depart in('CS','MA','IS');
--计算列
----eg 计算学生选课表中所有学生的成绩减少分
select student_number,course_number,grade-10 from student_course
--注意:此时grade-10这一列是无列名,可以用as跟它起个名字
select student_number,course_number,grade-10 as "AAA"from student_course
--注意:as 可以省略,为了移植性好,起的名字最好用双引号括起来(在sql中单引号也可以)
----在数据库中,不等于号可以写为!= 更多的情况下写为 <>
-----逻辑取反 或取反变且, 且取反为或
--top 的用法
--top 用于输出表中的前若干个元组或若干百分比个元组
----eg 输出学生表前个元组
select top 2 *from student
-----eg 输出学生表前%的元组,由于学生表共有个元组前%元组应取上整 即为个
select top 70 percent *from student
----eg 把课程表中学分在-3之间的课程前个降序排列
select top 3 *from course where course_credit between 2 and 3 order by course_credit desc
--注意此语select *from course 句的执行顺序:先选择-3的学分课程,再排序,最后选择前三个
null 语句
----实例1 输出课程表中先修课为空的课程,2 先修课不为空的课程
select *from course where primary_course=null
--执行错误
select *from course where primary_course!=null
--执行错误
select *from course where primary_course<>null
--执行错误
------总结:null语句不能参与 = != <> 运算
--注意null 不能参加四则运算,只能参与is is not 运算
----有时候要让null能参加运算,并把它看做时,要用isnull语句
----isnull(A,0),表示若A为空,就返回
---实例:在员工表中有月薪和年终奖两列,有的员工没有奖金,则其值为空,但要计算他们的年总
--收入时由于null不能参加四则运算,就得调用isnull语句;sal*12+isnull(jiangjin,)
select *from course where primary_course is null
select *from course where primary_course is not null
----另外,与null意义不同0是一个值,null表示值为空 任何类型的数据都允许为空
--order by 语句,用户可以使用order by 语句对查询结果按照一个或多个属性列的升序(asc)或降序排列(desc),缺值默认为升序
--eg 查询选修了号课程的学生学号和其成绩,结果按降序排列
select student_number,gradefrom student_coursewhere course_number='3'order by grade desc
---order by 语句后面可以接多个参数,排列顺序由参数顺序依次排列
--eg 将选课表中的学号降序排列,分数升序排列
select *from student_courseorder by student_number desc,grade
/*注:
order by A,B ---先按A升序排列,再按B升序排列
order by A desc,B ----先按A降序排列,再按B升序排列
order by A,B desc ----先按A升序排列,再按B降序排列
order by A desc,B desc ----先按A降序排列,再按B降序排列
*/
--函数的分类: 1,单行函数;2,多行函数
--单行函数 每一行返回一个值
--多行函数: 多行返回一个值
----eg 将学生表中系的名字变成小写形式
select lower(student_depart) "low_depart" from student
--lower()函数每一行返回一个值,所以它是单行函数
----eg 求学生选课表中学生最高成绩
select max(grade) "max_grade" from student_course
--max()函数多行返回一个值,所以它是多行函数
------聚合函数的引入
--聚合函数是多行函数
--常见的聚合函数有
--1 max() 求最大值
--2 min() 求最小值
--3 avg() 求平均值
--4 count() 计数,求个数
--count的用法
--eg 计算学生表所有记录的总和
select count(*) from student
--eg 计算学生表中学生的系部个数
select count(student_depart) from student
--注:这里返回的是逐个记录个数,要去除重复的,需要用distinct
select count(distinct student_depart) from student
----此语句统计的是有效的系部
--在使用count()统计含有空(null)的记录时,count不会计算其数目
--eg
select *from student_course
--学生选课表中的学生成绩为空,记录共有条
--现在统计学生选课表的成绩记录个数
select count(grade) from student_course
--这里把null的记录去除了,记录为条
--group by 语句
--语句功能:对表进行分组,可求表的整体属性
--eg 求学生表中各系的学生的平均年龄
--按两个列分组 group by a,b;
--eg 对学生表各系的学生分男女求平均年龄
select student_depart,student_sex,avg(student_age)"各系学生平均年龄"from student group by student_depart,student_sex
--求分组内部各参数
--eg 对学生表各系的学生求平均年龄,最大年龄,最小年龄
select student_depart,avg(student_age)"平均年龄", max(student_age)"最大年龄",min(student_age)"最小年龄"from student group by student_depart
--注:group by a,b,c 的意义,先按a分组,若a相同,再按b分组,若b相同,再按c分组,
--最终统计的是最小分组的信息
--说明一个问题,若要分组的属性里含有空值,也将把空值分为一组
--eg对课程表按先修课分组 仅作为例子,无实际意义
select primary_coursefrom coursegroup by primary_course
--having 语句:对分组信息进行过滤
--eg 求学生表中各系的学生的平均年龄大于的信息
select student_depart,avg(student_age)from studentgroup by student_departhaving avg(student_age)>18
--没有having之前有三组信息,加上having语句过滤后,将小于的信息过滤掉了
--注意:在用having语句过滤信息时,不允许用自己起的别名进行过滤
--eg
select student_depart,avg(student_age)"平均年龄"from student group by student_departhaving "平均年龄">18
--error
----上面语句中使用了别名进行过滤,出现错误
--having总结:
--having与where的异同
--having对分组后的信息过滤,where对原始数据过滤
--where语句不能使用聚合函数而having可以
----having是对分组进行过滤,因此,使用having之前一般都有group by语句
---若没使用group by 语句就使用having 语句,这就意味着having将所有元组视为一组来过滤,很少使用
---having子句出现的字段必须是分组之后的组的整体信息,having子句不允许出现组内的详细信息
---select中可以出现别名,而having子句和where语句都不允许出现别名
--连接查询(重难点)
----定义:将两个表或者两个以上的表以一定的连接条件连接起来,从中检索出满足一定条件的数据
---分类
----内连接 select...from A,B 的用法 此语句相当于把A表和B表做笛卡尔积
---eg 对学生表和课程表做笛卡尔积 学生表行列,课程表行列,结果为行列
select *from student,course
---结果的行数是原两个表的行数之积,列为两表列之和
-----select ...from A,B where 的用法:对原来的笛卡尔积进行过滤
---eg:选出学生号为的学生与课程表的笛卡尔积
select *from student,coursewhere student_number='10001'
---结果为行列
-----select...from A join B on 的用法
--A表与B表的连接,on后面接A的某个属性和B的某个属性
--eg 查询学生的个人信息与选课信息
select *from student "S"join student_course "SC"on "S".student_number="SC".student_number
---双引号里面表示别名on后面接的是筛选条件,去除了笛卡尔积中一些没用的东西
--eg 查询学生的姓名,系别及他们所选课的课程信息
select "S".student_name,"S".student_depart,"SC".course_numberfrom student "S"join student_course "SC"on "S".student_number="SC".student_number
--注意:select语句后面的选择条件的属性值要明确,student_name 等要指明是哪张表里的
--以上语句等效于下面语句(书上是下面的语句),上面的语句是SQL99标准,下面的语句是SQL92标准
--推荐使用SQL99标准
select "S".student_name,"S".student_depart,"SC".course_numberfrom student "S", student_course "SC"where "S".student_number="SC".student_number
--SQL99标准中的on语句能够更好地与where语句分工,on作为两张表的连接条件,where作为过滤
--示例:查询学生姓名,系别,所大于分选课程
--SQL92标准
select "S".student_name,"S".student_depart,"SC".course_number,"SC".grade from student "S",student_course "SC"where "S".student_number="SC".student_number and "SC".grade>85
--SQL99标准
select "S".student_name,"S".student_depart,"SC".course_number,"SC".gradefrom student "S"join student_course "SC"on "S".student_number="SC".student_number where "SC".grade>85
---此示例能清楚地看到on与where的分工,在标准中where的分工不明确,而标准中on与where分工明确
三表连接(难点)
--以示例说明
--eg 查询学生姓名,系别,所大于分选课程名
--此要求涉及到三张表,在连接时,遵循两表连接的规律
--SQL99标准
select "S".student_name,"S".student_depart,"C".course_name,"SC".grade from student "S"join student_course "SC"on "S".student_number="SC".student_number join course "C"on "SC".course_number="C".course_number where "SC".grade>85
--SQL92标准
select "S".student_name,"S".student_depart,"C".course_name,"SC".grade from student "S",student_course "SC",course "C" where "S".student_number="SC".student_number and "SC".course_number="C".course_number and "SC".grade>85
左外连接
--左外连接是内连接加以特殊要求的连接:用左表的第一行和右表的所有行进行连接
--若有匹配的行,则一起输出,若没有匹配的,则结果只输出一行,该行左边是左表第一行的内容,
--右边全部输出null;再用左表的第二行和右表的所有行匹配,若有匹配的一行,则一起输出,
--若有多行匹配则输出多行,否则结果只输出一行,该行左边是左表第一行的内容,
--右边全部输出null;以此类推;
完全连接 (full join)
--它的结果包括三个部分的集合 1 两个表匹配的所有行的记录
--2 左表中那些在右表中找不到的行的记录,这些记录的右边全为null
--3 右表中那些在左表总找不到的行的纪录,这些纪录的左边全为null
交叉连接(cross join)
--等价于全体的笛卡尔积
--自连接 定义:一张表和自己建立连接
--eg 用聚合函数查询分数最高的学生信息
select *from student_coursewhere grade=(select max(grade) from student_course)
---若不用聚合函数,则要用到自连接(还会出错,不建议这种方法)
select * from student_coursewhere student_number not in select "S1".student_number from student_course "S1" join student_course "S2" on "S1".grade<"S2".grade)