3 关系数据库标准语言

1. 三级模式对应

   1. 外模式：视图
   2. 模式：基本表
   3. 内模式：存储文件

2. 三语言一查询

   1. 数据定义语言 DDL：create drop alter
   2. 数据操纵语言 DML：insert update delete
   3. 数据控制语言 DCL：grant revoke
   4. 数据查询：select

3. 实操：

   #创建数据库
   create database dbtest;
   #删除数据库
   drop database dbtest;
   #创建表
   create table student
   (
       Sno char(25),
       sname char(100),
       Sex char(25),
       Sbirthday date,
       Sage int,
       Sgrade int
   );
   #约束
   #check约束
   create table student
   (
       Sno char(25),
       sname char(100),
       Sex char(25),
       Sbirthday date,
       Sage int,
       Sgrade int,
       constraint Sex_Check check(Sex = '男' or Sex = '女')
   );
   #非空约束
   create table student
   (
       Sno char(25) not null,
       sname char(100),
       Sex char(25),
       Sbirthday date,
       Sage int,
       Sgrade int
   );
   #唯一约束
   create table student
   (
       Sno char(25) unique,
       sname char(100),
       Sex char(25),
       Sbirthday date,
       Sage int,
       Sgrade int
   );
   #主键约束
   create table student
   (
       Sno char(25) primary key,
       sname char(100),
       Sex char(25),
       Sbirthday date,
       Sage int,
       Sgrade int
   );
   #主键约束
   create table student
   (
       Sno char(25),
       sname char(100),
       Sex char(25),
       Sbirthday date,
       Sage int,
       Sgrade int，
       constraint primary key(Sno,Sname)
   );
   #外键约束
   create table student
   (
       Sno char(25),
       sname char(100),
       Sex char(25),
       Sbirthday date,
       Sage int,
       Sgrade int，
       Tno char(25) foreign key
   );
   #修改表
   #增加列
   alter table student
   add
   Address varchar(50);
   #修改默认值
   alter table student
   alter column Sex default '男';
   #删除列
   alter table student
   drop column Address;
   #删除表
   drop table student;
   #添加数据
   insert into Student(Sno,Sname)
   values('202101','cwn'),
   ('202102','mr');
   #修改数据
   update student
   set Sno = '202301'
   where Sname = 'cwn';
   #删除数据
   delete 
   from student
   where Sno = '202102';
   #查询数据
   #投影查询
   select Sno,Sname
   from student;
   #条件查询
   select Sno,Sname
   from student
   where Sex = '男' and Sgrade between 80 and 90;
   #条件查询
   select Sno,Sname
   from student
   where Sname not in('cwn','mr');
   #模糊查询
   select Sno,Sname
   from student
   where Sname like '_m%';#_:代表一个字符
   #%:代表0个或多个字符
   

   常用库函数

   avg() #平均值
   sum() #某列值的总和
   max()
   min()
   count() #某列值的个数
   count(*) #计算记录个数
   distinct #q去重
   

   各种查询

   #分组查询
   select Sex as 性别,count(*) as 人数
   from student
   group by Sex# 统计不同性别的学生人数
   having (count(*) >= 2)#筛选人数打一次等于2的
   #分组
   cube:全部保留
   rollup：保留有值的
   #排序
   select Sno,Sgrade
   from student
   where(Sex = '男')
   order by Sgrade desc;#降序 默认升序ASC
   #内连接
   inner join table on 连接条件
   #外连接
   left outer join table on 连接条件;
   #交叉查询
   cross join table
   #自连接查询
   select S.Sno,X.Sex
   from student as S,student as X
   where S.Sname = X.Sname;
   #不相关子查询
   select Sno,Sname
   from student
   where Sgrade in 
   (select Sgrade
       from student
       where Tno = '2022')
   #all any
   any:某个
   all:所有
   #相关子查询
   select Sname from student
   where exists(select * from teacher
               where Sno = student.Sno and Tno = "2022");
   

   视图：

   create view vtest as 
   select Sno
   from student
   where....
   

   索引：

   create unique index itest on student