SQL快速入门

建立数据库

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)