基本SQL语句整理

创建数据库基本语句

CREATE

CREATE DATABASE db_name;


CREATE TABLE table_name(
    column_1 data_type(size),
    column_2 data_type(size),
    column_3 data_type(size),
);

ALTER

用于修改数据库对象

// 添加列
alter table table_name
add column_name datatype

// 删除列
alter table table_name
drop column column_name

DROP

drop index index_name
drop table table_name
drop database database_name

INSERT

insert into table_name
values
(value1, value2, value3)

insert into table_name (column1, column2, column3)
values
(value1, value2, value3)

UPDATE

update table_name
set column1 = value1, column2 = value2, 
where some_column = some_value

DELETE

删除行

delete from table_name
where some_column = some_value

SELECT

一般select

select column_name from table_name

select top

规定需要返回的记录的数目，一般用于大型表，不同数据库有不同的写法

select column_name from table_name
where rownum <= number

select into

从一个表复制数据，插入一个新表

select * 
into new_table
from  table

select column_name
into new_table
from  table

GROUP BY

表格式,以fruit和supplier为例

f_id	s_id	f_name	f_price
f01	s01	apple	5.5
f02	s01	orange	7.5

s_id	s_name
s01	Tom
s02	Jerry
s03	Jasper

对数据进行分组查询，通常与MAX(),MIN(),SUM(), AVG()一起使用

GROUP BY 字段 HAVING 条件表达式

// 得到的是不同种类的水果的 种类 数目 表
// listagg需要有within
select S_ID, listagg(F_NAME, ',')
within group ( order by S_ID)
as fruit_type 
from FRUITS group by S_ID;

// HAVING
// 查询s_id，以逗号连接的水果名，数目大于2
select S_ID, listagg(F_NAME, ';')
within group ( order by S_ID) as fruit_type
from FRUITS
group by S_ID having count(F_NAME) > 1;

// ROLLUP
// 统计记录数量
select s_id, count(*) as Total
from fruits a
group by rollup(s_id)

LISTAGG表示以何种字符串来连接若干查询结果

HAVING与WHERE都是用来过滤数据的，HAVING是在数据分组之后进行过滤来选择分组，选择的是分组中包含的字段，WHERE在分组之前用来选择记录，选择的是数据库中的所有字段

出现在select列表中的字段，如果没有出现在聚合函数中，则必须出现在group by子句中

ROWNUM限制查询结果的数量

显示前4行

select * from fruits where ROWNUM < 5;

聚合函数

count()

与group by一起，计算不同分组中记录的总数。

select F_NAME, count(S_ID) from FRUITS group by F_NAME;

avg()

指定列数据的平均值

select avg(all F_PRICE) as avg_price from FRUITS where S_ID = 's01';

max()

指定列中的最大值

select F_NAME, max(F_PRICE) as max_price from FRUITS group by F_NAME;

select  max(F_PRICE) as max_price from FRUITS

min()

sum()

内连接

使用比较运算符进行表间某些数据的比较，并列出所有匹配行

Inner Join连接的条件是ON或者WHERE

select SUPPILIER.S_ID, S_NAME, F_NAME, F_PRICE
from FRUITS, SUPPILIER
where FRUITS.S_ID = SUPPILIER.S_ID

// 换用inner join写法
select S.S_ID, S_NAME, F_NAME, F_PRICE 
from FRUITS
Inner Join SUPPILIER S on FRUITS.S_ID = S.S_ID;

// 两个相同的表
select f1.F_ID, f1.F_NAME, f1.S_ID 
from FRUITS f1, FRUITS f2 
where f1.S_ID = f2.S_ID and f2.F_ID = 'a01'

左外连接

左连接

左表中所有记录和右表中连接字段相等的记录

换言之，即使左表中存在右表缺乏关联的数据，例如水果不存在供货商，也会展示水果和供货商的对应关系信息，在没有供货商的水果处，值为null

右连接

右表中所有记录和左表中连接字段相等的记录

select S.S_ID, S_NAME, F_NAME, F_PRICE
from FRUITS left outer join SUPPILIER S on FRUITS.S_ID = S.S_ID;

ANY, SOME 同义词

创建表达式对子查询的返回值列表进行比较，返回满足内层查询条件的值

select F_PRICE from FRUITS where F_PRICE > any (select G_PRICE from PRICE);

ALL

满足所有内层查询条件

select F_PRICE from FRUITS where F_PRICE > all (select G_PRICE from PRICE);

exist

exists后面的参数是任意一个子查询，如果子查询有返回行，则exist的结果为true，执行外查询。否则外查询不执行。

in/not in

返回一个数据列，类似直接写in(结果集)

正则表达式

使用的关键词为REGEXP_LIKE(column_name, reg)

// 以b开头的单词
select * from FRUITS where regexp_like(F_NAME, '^b');

// 匹配指定字符，注意不存在开头和结尾
select * from FRUITS where regexp_like(F_NAME, 'ba');

// 匹配任意字符
select * from FRUITS where regexp_like(F_NAME, '[ba]');

// 匹配某字符出现至少两次
select * from FRUITS where regexp_like(F_NAME, 'x{2, }');

数据控制语言

一般只有sysadmin, dbcreateor, db_owner, db_securityadmin才有此权限，包括GRANT(授权), REVOKE(删除权限), COMMIT, ROLLBACK

数据库建表基本语句

带有主键的表

create table user_info_1(
    id Number(11),
    name varchar(255),
    gender char(2),
    primary key(id)
);

主键有多个字段的表

create table user_info_1(
    id Number(11),
    name varchar(255),
    gender char(2),
    primary key(id, name)
);

修改表的时候添加主键

// pk_id表示约束的名称
alter table user_info
add constraint pk_id primary key (id);

修改表的时候移除主键

alter table user_info
drop constraint PK_ID;

添加外键

注意外键必须是另一个表的主键，如果不是主键，则无法添加成功外键

// user_info与dept_info的一对多

// 建表时添加
create table user_info_1(
    id Number(11),
    name varchar(255),
    gender char(2),
    dept_id varchar(20),
    primary key (id),
    constraint fk_user_dept foreign key (dept_id) references dept_info(dept_id)
);

// 修改时添加
alter table USER_INFO
add constraint fk_user_dept foreign key(dept_id)
references DEPT_INFO(dept_id)
on delete cascade;

非空约束

name varchar(255) not null,

alter table user_info
modify name not null;

自增约束

// 定义时
dept_name varchar(20) unique 

// 修改时
alter table user_info
add constraint STH unique(dept_name)

// 移除
alter table user_info
drop constraint STH

默认约束

表中元素默认为此值

dept_name varchar(20) default '领导' 

检查元素

规定每一列能够输入的值

create table user_info(
    id Number(11),
    name varchar(255) not null,
    gender char(2),
    constraint CHK_GENDER check ( gender='男' or gender = '女')
);

自增

create table user_info(
    id Number(11) generated by default as identity ,
    name varchar(255) not null,
    gender char(2)
);

查看表结构

desc user_info

修改表名称，字段

// 表名
alter table dept_info rename to tb_dept_info

// 字段
alter table dept_info rename column name to dept_name

// 添加字段
alter table dept_info add location varchar(20) not null

// 修改字段的数据类型
alter table dept_info
modify location varchar(255)

删除表

如果两个表之间存在关联性约束，那么直接drop table xxx是无法删除父表的，因此需要先删除子表。或是先删除外键，再删除父表。

插入数据

简单插入

column_name可以省略，但后面的column_value需要和column_name顺序保持一致

column_name可以是所有字段的子集，其它未指定值的字段为默认值

insert into FRUITS column_name VALUES column_value;

// eg
insert into FRUITS (F_ID, S_ID, F_NAME, F_PRICE) VALUES ('w01', 's03', 'watermelon', 4.5);

查询结果插入

insert into
fruit_new (f_id, s_id, f_name, f_price)
select f_id, s_id, f_name, f_price
from fruit_old;

更新数据

update table_name
set column1 = value1, column2 = value2
where condition

删除数据

delete from table_name
where condition

// 全删
delete from table_name

一些特殊关键字

union

表示拼接两个select的查询结果，要求两个select的查询结果具有相似的数据结构。union的拼接结果是去重的，如果需要保留若干相同的结果，需要使用union all

distinct

用于去重，返回不同的值