SQL概述

语言简介，易学易用。SQL功能极强，完成核心功能只用了9个动词：

  • 数据查询：SELECT
  • 数据定义：CREATE,DROP,ALTER
  • 数据操纵：INSERT,UPDATE,DELETE
  • 数据控制：GRANT,REVOKE

数据定义

SQLD额数据定义功能：模式定义，表定义、视图索引定义

操作对象 创建 (CREATE) 删除 (DROP) 修改 (ALTER)
模式 CREATE SCHEMA DROP SCHEMA
表 CREATE TABLE DROP TABLE ALTER TABLE
视图 CREATE VIEW DROP VIEW
索引 CREATE INDEX DROP INDEX ALTER INDEX

模式的定义和删除

模式的定义

什么是模式呢？一个数据库是一个仓库，里面很多房间，每个房间放几个东西，而模式就是一个一个房间。凡事跟这个模式相关的表都可以放进去，或者规定某些用户访问模式的权限。

1
CREATE SCHEMA "模式名" AUTHORIZATION 用户名;

有些时候会没有模式名,这个时候我们默认用户名就是模式名

1
CREATE SCHEMA AUTHORIZATION 用户名;

并且CREATE SCHEMA还支持在其中直接创建表，视图，授权。

1
CREATE SCHEMA "模式名" AUTHORIZATION 用户名[表定义语句|视图定义语句|授权定义语句];

举个例子吧：

1
2
3
CREATE SCHEMA "TEST" AUTHORIZATION ZHANG
    CREATE TABLE TABL(COL1 SMALLINT,
                      COL2, INT)；

执行创建模式语句必须拥有数据库管理员DBA的权限，或者DBA授予的权限

模式的删除

语句格式：

1
DROP SCHEMA 模式名 CASCADE|RESTRICT
  • CASCADE和RESTRICT必须二选一
  • CASCADE（级联）：把该模式中的所有数据库对象全部删除
  • RESTRICT（限制）：如果里面有数据，那么会报错。如果没数据，会删除。

基本表的定义、删除与修改

定义基本表

语句格式：

1
2
3
4
5
CREATE TABLE 表名(
	列名 数据类型 [列级完整性约束条件],
	列名 数据类型 [列级完整性约束条件],
	[表级完整性约束条件]
);

建立学生表Student，学号是主码，姓名取唯一值

1
2
3
4
5
6
7
CREATE TABLE Student(
                        Sno CHAR(9) PRIMARY KEY,
                        Sname CHAR(20) UNIQUE,
                        Ssex CHAR(2),
                        Sage SMALLINT,
                        Sdept CHAR(20)
);

建立一个课程表Course，这里Cpno是先导课，被参照表是本身Course中的Cno；举个例子，有课程1,2,3,4。他们的先导课可能是4,3,2,1。

1
2
3
4
5
6
7
CREATE TABLE Course(
	Cno CHAR(4) PRIMARY KEY,
	Cname CHAR(40),
	Cpno CHAR(4),
	Ccredit SMALLINT,
	FOREIGN KEY(Cpno) REFERENCES Course(Cno)
);

建立一个学生选课表SC

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE SC(
	Sno CHAR(9),
	Cno CHAR(4),
	Grade SMALLINT,
	PRIMARY KEY(Sno,Cno),
	--主码由两个属性构成，必须作为表级完整性进行定义--
	FOREIGN KEY(Sno) REFERENCES Student(Sno),
	--表级完整性约束条件，Sno是外码，被参照表是Student--
	FOREIGN KEY(Cno) REFERENCES Course(Cno)
	--表级完整性约束条件，Cno是外码，被参照表是Course--
);

数据类型

数据类型 含义
CHAR(n) 长度为 n 的定长字符串
VARCHAR(n) 最大长度为 n 的变长字符串
INT 长整数（也可写作 INTEGER）
SMALLINT 短整数
NUMERIC(p, d) 定点数，由 p 位数字（不含符号和小数点）组成，小数部分占 d 位
REAL 取决于机器精度的浮点数
Double Precision 取决于机器精度的双精度浮点数
FLOAT(n) 浮点数，精度至少为 n 位数字
DATE 日期，包含年、月、日，格式为 YYYY-MM-DD
TIME 时间，包含一日的时、分、秒，格式为 HH:MM:SS

模式与表

每一个表都有一个自己的模式，如果创建表时没有指定模式的话，系统会根据搜索路径来确定该对象所属的模式。

显示当前的搜索路径：

1
SHOW search_path;

上面搜索路径会显示下面这个👇🏻，user可能是用户名之类的，然后如果找不到的话，就会放到一个公共区域：

1
$user, PUBLIC;

DBA管理员也可以设置搜索路径，后面的PUBLIC意思是，你没有模式名的话，就会设置一个公共的模式：

1
SET search_path TO 模式名 , PUBLIC;

模式下创建基本表

创建表的时候给出模式：

1
2
CREATE TABLE "S-T" Student();
CREATE TABLE "S-T" Course();

创建模式的时候给出表：

1
2
3
CREATE SCHEMA "TEST" AUTHORIZATION ZHANG
CREATE TABLE TABL(COL1 SMALLINT,
				  COL2, INT)；

设置所属模式，在创建表名中不必给出模式名：

1
SET search_path TO 模式名 , PUBLIC;

修改基本表

语句格式：

1
2
3
4
5
6
7
8
9
10
11
ALTER TABLE 表名
ADD [COLUMN] 新列名 数据类型 [完整性约束条件]
ADD 表级完整性约束
--用于增加新列、新的列级完整性约束条件和新的表级完整性约束--
DROP [COLUMN] 列名 [CASCADE|RESTRICT]
DROP CONSTRAINT 完整性约束名 [CASCADE|RESTRICT]
--如果指定了CASCADE短语，则自动删除引用该列的其他对象--
--如果指定了RESTRICT短语，则如果该列被其他对象引用，将会拒绝删除--
--DROP CONSTRAINT子句用于删除指定的完整性约束条件--
ALTER COLUMN 列名 数据类型;
--ALTER COLUMN子句用于修改原有的列定义，包括修改列的数据类型--

向Student表增加“入学时间”列，其数据类型为日期：

1
ALTER TABLE Student ADD S_entrance DATE;

将年龄的数据类型由字符型转换为整数

1
ALTER TABLE Student ALTER COLUMN Sage INT;

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

1
ALTER TABLE Course ADD UNIQUE(Cname);

删除基本表

语句格式：

1
2
3
DROP TABLE 表名 [RESTRICT|CASCADE];
--RESTRICT：若有引用，无法删除，有视图也删不了🤔不过这个视图是我们人为规定的--
--CASCADE：谁用我这个表嘞也会删除--

删除Student表：

1
DROP TABLE Student CASCADE;

索引的建立和删除

索引就是一本书的目录。

  • 建立索引的目的：加快查询速度
  • 谁可以建立索引：DBA或表的主人。DBMS一般会自动建立索引，在有以下命令的条件下：
    • PRIMARY KEY
    • UNIQUE
      类似于直接建立了一个字典，好查找。也就是哈希或者B+树

自定义列建立索引

语句格式：

1
2
3
4
CREATE [UNIQUE] [CLUSTER] INDEX 索引名 ON 表名(
	列名[次序],
	列名[次序]
);

UNIQUE表明此索引每一个索引值只对应唯一的数据，为学生-课程数据库中的Student，Course，SC三个表建立索引：

1
2
3
4
5
6
CREATE UNIQUE INDEX Stusno ON Student(Sno);
--Student表按照学号升序建立唯一索引--
CREATE UNIQUE INDEX Coucno ON Course(Cno);
--Course表按照课程号升序建立唯一索引--
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);
--SC表按学号升序和课程号降序建唯一索引--

CLUSTER表示要建立的索引是聚簇索引，聚簇索引是表中该列的顺序在硬盘上也要按照这个排序，算是直接操控了物理。在Student表的Sname列上建立聚簇索引：

1
CREATE CLUSTER INDEX Stusname ON Student(Sname);

为什么聚簇索引会这样？因为我们的表中排序正确并不代表本地正确，毕竟计算机就是直接找一个地方塞进去，让内存最大化。当然这样也会让查找速度变慢。

  • 在最经常查询的列上建立聚簇索引
  • 一个基本表上最多只能建立一个聚簇索引
  • 经常更新的列不建议建立聚簇索引

删除索引

语句格式：

1
DROP INDEX 索引名

删除索引时，系统会从数据字典中删去有关该索引的描述

删除Student表的Stusname索引：

1
DROP INDEX Stusname;

数据字典

  • 数据字典时关系数据库管理系统内部的一组系统表
  • 数据字典记录了数据库中所有的定义信息，包括模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库操作权限、统计信息等。
  • 我们执行SQL数据定义时，实际就是更新数据字典

说些什么吧！

twikoo