sql笔记

注释：   

单行注释      --注释内容

多行注释      /*注释内容*/

1，模式

1.1，定义模式

管理员权限(DBA)或DBA授予 create schema 权限
create schema [<模式名>] authorization <用户名>;

/* 例：为用户 zhang 创建一个模式 test ，并在其中定义一个表 tab1 */
create schema test authorization zhang
create table tab1(
col1 smallint,
col2 char(20),
col3 numeric(10,3),
col4 decimal(5,2));

1.2，删除模式

drop schema <模式名><cascade|restrict>;

/* 删除 1中建立的模式 */

drop schema test cascade;  /*此句也同时删除了 tab1 */

2，表

2.1，定义基本表

create table <表名>(<列名><数据类型>[列级完整性约束]
             [,<表名>(<列名><数据类型>[列级完整性约束]]
             ...
             [,<>]);

2.1.1 建立一个学生表student

create table student(
Sno char(8) primary key,  /*列级完整性约束条件，Sno是主码*/
Sname varchar(20) unique, /*Sname取唯一值*/
Ssex char(6),
Sbirthdate date,
Smajor varchar(40));

2.1.2 建立一个课程表course 

create table course(
Cno char(5) primary key,   /*列级完整性约束条件，Sno是主码*/
Cname varchar(40) not null,/*列级完整性约束，Cname不能取空值*/
Ccredit smallint,
Cpno char(5),
foreign key(Cpno)references Course(Cno)
/*表级完整性约束，Cpno是外码，course是被参照表，Cno是被参照列*/
);

2.1.3 建立一个选课表SC 

create table SC(
Sno char(8),
Cno char(5),
Grade smallint,         /*成绩*/
Semester char(5),       /*开课学期*/
Teachingclass char(8),  /*学生选修某一门可所在的教学班*/
primary key(Sno,Cno),
/*主码由两个主属性构成，必须作为表级完整性进行定义*/
foreign key(Sno) references Student(Sno), 
/*表级完整性约束，Sno是外码，Student是被参照表*/
foreign key(Cno) references Course(Cno)); 

2.2，修改基本表

create table 表名(
[add[column]<新列名><数据类型>[完整性约束]]
[add<表级完整性约束>]
[drop[column]<列名>[cascade|resterict]]
[drop constraint<完整性约束名>[cascade|resterict]]
[rename column<列名>to<新列名>]
[alter column<列名>type<数据类型>];

add句子增加新列、新的列级完整性约束、新的表级完整性约束
drop column 删除表中的列，如果指定了 cascde 短语则自动删除引用了该列的其他对象
drop constraint 删除指定的完整性约束
rename column 修改列名
alter column 修改列的数据类型

2.2.1 增加列

Student表中增加”邮箱地址“列Semail，数据类型为字符型

alter table Student add Semail varchar(30);

2.2.2 修改数据类型 

Student 出生日期 数据类型 由 date 改为字符型

alter table Student alter column Sbirthdate varchar(20);

2.2.3 增加约束条件

增加课程名称必须取唯一值的约束条件

alter table course add unique(cname);

2.3，删除基本表

drop table<表名>[restrict|cascade];
cascade  该表的删除无限制条件
restrict （默认）该表的删除有限制条件

删除 student 表，若表上建有视图，restrict 时表不能删除，cascade 时表能删除，视图也会删除
create view cs_Student 
as select sno,sname,ssex,sbirthdate,smajor
from student where Smajor='计算机科学与技术';

drop table student restirict;/*错误，指出存在依赖该表的存在，不能被删除*/
drop table student cascade;
--notice:drop cascades to view cs_Student /*表上的视图也被删除*/

2.4,索引

2.4.1，建立索引

create [unique] [cluster] index <索引名>

on<表名> （<列名>[<次序>] [,<列名>[<次序>] ]...）;

2.4.2，修改索引

altable index <旧索引名> rename to <新索引名>;

altable 表名 rename 旧索引名 to 新索引名;

/* 将 sc 表中的idx_sccno 索引名改为 idx_scsnocno */
altable index idx_sccno rename to idx_scsnocno;

2.4.3，删除索引

drop index <索引名>;

2.5，数据类型

​

3，数据查询

3.1 单表查询

3.1.1 选择表中的若干列

（1）查询指定列

/* 查询全体学生的学号与姓名 */
select sno,sname from Student;

（2）查询全部列

/* 查询全体学生的记录 */
select * from Student;

（3）查询经过计算的值

/* 查询全体学生的姓名与年龄 */
select sname,(extract(year from current_date)-extract((year from sbirthdate))"年龄"
from Student;

extract(year from current_date) 获取当前年份

3.1.2 选择表中的若干元组

（1）消除取值重复行 

/* 查询选修了课程的学生的学号 */
select sno from SC;

/* 结果里重复行，若取消，用 distinct */
select distinct sno from SC;

（2）查询满足条件的元组

/* 查询主修计算机科学与技术专业全体学生的姓名 */
select sname from Student
where smajor='计算机科学与技术';

/* 查询2000年及其以后出生的学生的姓名、性别 */
select sname,ssex from Student
where extract(year from sbirthdate) >= 2000;

between...and...和 not  between...and...

查找属性值（在、不在）指定范围内的元组

/* 查询 20~23 之间的学生的姓名、出生日期、专业 */
select sname,sbirthdate,smajor from Student
where extract(year from current_date)-extract((year from sbirthdate)
not between 20 and 23;

谓词 in 可以属性值属于指定集合的元组，not in 不属于指定集合的元组

/* 查询计算机科学与技术专业和信息安全专业的学生的姓名及性别 */
select sname,ssex from Student where smajor in ('计算机科学与技术','信息安全');
       /* 等价于 */
select sname,ssex from Student where smajor='计算机科学与技术' or smajor='信息安全';

select sname,ssex from student where Smajor not in ('计算及科学技术','信息安全');

% 代表任意长度（可为0）的字符串

a%b 以a开头，以b结尾的字符串，如 abc,addgb,ab 等

_ 代表任意单个字符

a_b 以a开头，以b结尾的长度为 3 的字符串，如 acb,afb 等

select * from student where sno like '2018';
      /* 等价于 */
select * from student where sno='2018';

查询空值

select sno,cno from sc where grade is null; /* grade是空值 */

多重查询

/* 查询计算机科学与技术专业，在2000年以后（包括2000年）出生的学生学号，姓名，性别 */
select sno,sname,ssex from student where smajor='计算机科学与技术'and extract(year from sbirthdate)>=2000;    

3.1.3 order by

对结果 升序asc（默认），降序 desc  排列

/* 查询选修了81003 的学号，成绩 降序排列 */
select sno，grade from sc where cno='81003' order by grade desc; 

3.1.4 聚集函数

count(*)    统计元组个数

count([distinct|all]<列名>)  统计一列中值的个数

sum([distinct|all]<列名>)  计算一列值的总和（数值型）

avg([distinct|all]<列名>)  计算一列值的平均值（数值型）

max([distinct|all]<列名>)  求一列值中的最大值

min([distinct|all]<列名>)   求一列值中的最小值

   /* 查询学生总人数 */
select count(*) from student;
   /* 查询选修了课程的总人数 */
select count(distinct sno)from sc;
   /* 查询学号为21080003的学生选修课程的总学分数 */
select sum(ccredit) from sc,course where sno='21080003'and sc.cno=course.cno;

group by

将查询结果按某一列或多列的值分组

/* 求各个课程号及选修课程的人数 */
select cno,count(sno) from sc group by cno;

limit

限制查询结果的数量

  limit 行数1 [offset 行数2];
  读取 行数1，忽略 前 行数2 作为查询数据   

/* 查询选修 数据库系统概论 且成绩 前10 的学号 */
select sno from sc,course where course.cname='数据库系统概论'and sc.cno=course.cno
order by grade desc limit 10;

3.2 连接查询