SQL语言基本语句

SQL语言基本语句

数据定义

定义模式

create schema 模式名 authorization 用户名;

删除模式

drop schema 模式名 <cascade|restrict>;

+cascade（级联）表示把该模式下所有数据库对象全部删除
+restrict（限制）表示如果模式下已经存在数据库对象，则拒绝删除，
只有该模式下没有任何数据库对象时可以执行drop schema语句

定义基本表

create table 表名 (列名 数据类型 列级完整性约束性条件
					列名 数据类型 列级完整性约束性条件
					...
					表级完整性约束性条件);

数据类型

数据类型	含义
char(n),character(n)	长度为n的定长字符串
varchar(n),charactervarying(n)	最大长度为n的变长字符串
clop	字符串大对象
blob	二进制大对象
int,integer	长整数（4字节）
smallint	短整型（2字节）
bigint	大整型（8字节）
numeric(p,b)	定点数，由p位数字（不包括符号、小数点）组成，小数点后面有d位数字
decimal(p,b),dec(p,d)	同numeric含义一样
real	取决于机器精度的单精度浮点数
double precision	取决于机器精度的双精度浮点数
float(n)	可选精度的浮点数，精度至少为n位数字
boolean	逻辑布尔量
date	日期，包含年、月、日，格式为YYYY-MM-DD
time	时间，包含一日的时、分、秒、格式为HH:MM:SS
timestamp	时间戳类型
interval	时间间隔类型

模式与表

//显示当前搜索路径
show search_path;

//与用户名相同的模式名不存在，则使用public模式
set search_path to "定义的模式名",public;

修改基本表

alter table 表名
(
	//增加新列和新的约束条件
	add column 新列名 数据类型 完整性约束
	add 表级完整性约束
	//删除表中的列
	//+castrict:表示自动删除引用了该列的其他对象
	//+restrict:表示如果该列被其他对象引用，RDBMS将拒绝删除该列
	drop column 列名 (cascade|restrict)
	//删除指定的完整性约束条件
	drop constraint 完整性约束名 (restrict|cascade)
	//修改原有的列定义
	alter column 列名 数据类型
);

删除基本表

//+castrict:表示自动删除引用了该表的其他对象
//+restrict:表示如果该表被其他对象引用，不能删除
drop table 表名 (restrict|cascade);

索引的建立与删除

建立索引

//建立索引
create unique index 索引名
on 表名
(
	列名 (asc|desc)
	列名 (asc|desc)
	...);

asc：升序，为默认值。
desc：降序。
unique：表明此索引的每一个索引值只对应唯一的数据记录。

修改索引

//修改索引
alter index 旧索引名 rename to 新索引名;

删除索引

//删除索引
drop index 索引名;

数据查询

select (all|distinct) 目标列表达式,目标列表达式,...
from 表名或者视图名，表名或者视图名,...
where 条件表达式
//用于对列1分组，通常会在每组中作用聚集函数
group by 列名1 having 条件表达式
//结果表要按列2的升序或者降序输出
group by 列名2 (asc|desc);

distinct：表示消除取值重复的行
all:表示保留取值重复的行

单表查询

1.选择表中的若干列

查询指定列

//例如
select 目标列表达式,目标列表达式,...
from 表名;

查询全部列

//查询全部列可以用*代表，或者将全部的列表达式列出来
select *
from 表名;

注意：

1. 目标列表达式先后顺序可以和表中的顺序不一致
2. 目标列表达式不仅可以是表中的属性列，也可以是表达式
补充：
lower(列属性名) -将大写字母转换成小写字母（适用于字符型数据）

2.选择表中的若干元组

查询条件	谓词
比较	=(等于),>(大于),<(小于),>=(大于等于),<=(小于等于),!=或<>(不等于),!>(不大于),!<(不小于),not+等
确定范围	between and, not between and
确定集合	in, not in
字符匹配	like, not like
空值	is null,is not null
多重条件（逻辑运算）	and, or, not

select 目标列表达式,目标列表达式,...
from 表名;
where 条件;

条件：

1. 比较大小
2. 确定范围：between后是范围下限（最低值），and后是范围的上限（最高值）
3. 确定集合：用谓词in
4. 字符匹配：

(not) like '匹配串' （escape '换码字符'）

匹配串：可以是完整的字符串，也可以是通配符符%和_

%:代表任意长度（长度可以为0）的字符串。（代表不确定字符）
_:代表任意单个字符。（代表一个汉字字符）

如果用户要查询的字符串本身含有通配符%和_，这时要使用excape '换码字符’短语对通配符进行转义
escape ’ \ ’ 表示“\”为换码字符，通过在通配符前加“ \ ”，可以使通配符转义为普通的字符。

5. 涉及空值查询：

//例如
select 目标列表达式
from 表名
where 列名 is NULL;

其中is不能用=来代替

6. 多重条件查询
   逻辑运算符and和or可用来连接多个查询条件。
   and的优先级高于or，但括号可以改变其优先级。

3.order by子句

对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，默认值为升序。

order by 列名 (ASC|DESC)

4.聚集函数

函数名	含义
count(*)	统计元组个数
count([distinct | all] 列名)	统计一列中值的个数
sum([distinct | all] 列名)	计算一列值的总和（此列必须是数值型）
avg([distinct | all] 列名)	计算一列值的平均值（此列必须是数值型）
max([distinct | all] 列名)	求一列值中的最大值
min([distinct | all] 列名)	求一列值中的最小值

注意：
1.聚集函数除count(*)，都只处理非空值；
2.聚集函数只能用于select子句和group by中的having子句。

group by子句

• 将查询结果按某一列或多列的值分组，值相等的为一组
• 分组后聚集函数将作用与每一个组，即每一个组都有一个函数值

补充：where子句与having短语区别在于作用对象不同

• where子句作用与基本表或视图，从中选取满足条件的元组
• having短语作用与组，从中选择满足条件的组

连接查询

1.等值与非等值连接查询

形式一： [<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>
比较运算符：=、>、<、>=、<=、!=(或<>)
连接运算符为=时，为等值连接；其他运算符则表示非等值连接
连接谓词中的列名称为连接字段
形式二：[<表名1>.]<列名1>between [<表名2>.]<列名2>and[<表名>.]<列名3>
把目标列中重复的属性列去掉则为自然连接

2.自身连接

一个表与自己连接，称为表的自身连接

//例如
select 表别名1.列名,表别名2.列名
from 表名 别名1,表名 别名2
where 连接条件;

3.外连接

外连接：把悬浮元组也保存在结果关系中，而在其他属性上填空值（NULL）
左外连接：列出左边关系中所有的元组
右外连接：列出右边关系中所有的元组

4.多表连接

两个以上的表连接称为多表连接

嵌套查询

一个select-from-where语句称为一个查询块。
上层的查询块称为外层查询或父查询，下层查询块称为内层查询或子查询。

1.带有in谓词的子查询

//例如
select 内容
from 内容
where 内容 in 
	(select 内容
	from 内容
	where 内容);

注意：子查询的查询条件不依赖于父查询，称为不相关子查询
子查询的查询条件依赖于父查询，称为相关子查询

2.带有比较运算符的子查询

//例如
select 内容
from 内容
where 内容 <运算符>
	(select 内容
	from 内容
	where 内容);

3.带有any(some)或all谓词的子查询

//例如
select 内容
from 内容
where 内容 <运算符>(any|all)
	(select 内容
	from 内容
	where 内容);

4.带有exists谓词的子查询

带有exists谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false” 。
使用exists后，若内层查询结果为非空，则外层where子句返回真值，否则返回假值。
使用not exists后，若内层查询结果为空，则外层where子句返回真值，否则返回假值。

集合查询

集合查询操作主要包括并操作union、交操作intersect和差操作except

基于派生表的查询

子查询不仅可以出现在where子句中，还可以出现在from子句中，这时子查询生成的临时派生表成为主查询的查询对象。

数据更新

插入数据

1.插入元组

//例如
insert
into 表名(属性列名1,属性列名2,...)
values(常量1,常量2,...);

字符串常数要用单引号（英文符号）括起来
指出的属性名，没有常量赋值，要明确给出空值NULL。

2.插入子查询结果

//例如
insert
into 表名(属性列名1,属性列名2,...)
子查询;

修改数据

//例如
update 表名
set 列名=表达式,...
where 条件;

删除数据

//例如
delete
from 表名
where 条件;

注意：delete语句删除是表中的数据，而不是关于表的定义。

存储过程、触发器

常量

作用：参与运算或给变量赋值
类型：

• 字符串：用成对的单引号或双引号括起来
• 数值型：二进制、十进制、八进制、十六进制
• 日期时间型：‘年-月-日（时：分：秒）
• 布尔值
• NULL

变量

作用：用于记录或暂时存放某一时段的状态值
数据库系中的变量分为系统变量和自定义变量

系统变量

1.全局变量

作用域：针对所有会话（连接）有效，但不能跨重启

//查看所有全局变量
show global variables;
//查看满足条件的部分全局变量
//例如：
show global variables like'%char%';
//查看指定的全局变量的值
select @@global.autocommint;
//为某个全局变量赋值
set @@global.autocommit=0;
set global autocommit=0;

2.会话变量

作用域：**针对于当前会话（连接）有效

//查看所有会话变量
show session variables;
//查看满足条件的部分会话变量
show session variables like'%char%';
//查看指定的会话变量的值
select @@autocommint;
select @@session.tx_isolation;
//为某个会话变量赋值
set @@session.tx_isolation='read-uncommitted';
set session tx_isolation='read-committed';

自定义变量

1.用户变量

声明并初始化：

set @变量名=值;
set @变量名:=值;
select @变量名:=值;

赋值：

//方法一：赋简单值
set @变量名=值;
set @变量名:=值;
select @变量名:=值;

//方法二：赋表中的字段值
select 字段名或表达式 into 变量
from 表名;

使用：

select @变量名;

2.局部变量

声明：

declare 变量名 数据类型 [default 默认值];

赋值：

//方法一：赋简单值
set @变量名=值;
set @变量名:=值;
select @变量名:=值;

//方法二：赋表中的字段值
select 字段名或表达式 into 变量
from 表名;

使用：

select 变量名;

程序结构

顺序结构

程序中各个操作按照在源代码中的排列顺序，自上而下，依次执行

分支结构

IF语句

if 条件1 then 语句序列1 
elseif 条件2 then 语句序列2
...
else 语句序列n
end if;

case语句

//格式一
case
when 条件1 then 语句序列1
when 条件2 then 语句序列2
...
else 语句序列n
end case;

//格式二
case 表达式
when 值1 then 语句序列1
when 值2 then 语句序列2
...
else 语句序列n
end case;

循环结构

while语句

while 条件 do
程序段
end while;

repeat语句

repeat
程序段
until 条件 
end repeat;

loop语句

loop
程序段
end loop;

创建存储过程

//创建存储过程
delimiter //
create procedure 存储名字(in/out/inout 参数名 数据类型) 
存储过程的特性...
begin
存储过程的SQL语句代码
end //
delimiter;

//调用存储过程
call 存储过程名('存储过程的参数');

//查看存储过程状态信息
show procedure status like '存储过程名字';

//查看存储过程定义信息
show create procedure 存储过程名字;

//修改存储过程
alter procedure 存储过程名字 存储过程特性...;

//删除存储过程
drop procedure 存储过程名字;

in： 输入参数（表示调用者向过程传入参数）
out：输出参数（表示过程向调用者传出参数）
inout:输入输出参数（既表示调用者向过程传入参数，又表示过程向调用者传出参数）

创建函数

//创建函数
delimiter //
create function 函数名字(in/out/inout 参数列表)
returns 函数类型
//注：此处写出的是函数体body的一般格式
begin 
	return(select查询语句);
end //
delimiter;

delimiter命令用于显示定界字符值，主要原因是避免在数据库中使用对象名中所使用的字符。为了避免”;“存在冲突，所以要在用户开始在数据库中工作之前更改定界符。

新人创作，如有问题请大佬指正，如有帮助请点个赞吧！d=====(￣▽￣*)b