Oracle知识梳理(三)操作篇:SQL基础操作汇总
一、表操作
1、表的创建(CREATE TABLE):
基本语句格式: CREATE TABLE table_name ( col_name datatype, ……);
解释: table_name 为要创建的表的名称(同一登录用户下表名必须唯一),col_name 为表中属性的名称,datatype 为属性的相应数据类型,同一表中会有许多字段,也可以在字段数据类型后追加各种约束条件(具体见约束)。
例:
2、表的修改(ALTER TABLE):
解释:所有对表进行修改的语句,开头均以alter table+要修改的表的名称,后面的一系列操作只需要追加相应的关键字语句即可,例如添加字段 ALTER TABLE table_name ADD col_name datatype
(修改表名)基本语句格式:RENAME table_name TO new_table_name; (TO前面为原表名,后面为新的表名)
例:
(添加字段)基本语句格式:ALTER TABLE table_name ADD col_name datatype; (add关键字为添加字段使用)
例:
(修改字段名称)基本语句格式:ALTER TABLE table_name RENAME COLUMN col_name TO new_col_name; (RENAME COLUMN为修改字段名的关键字,TO后面为新的名称,前面为原名称)
例:
(修改字段数据类型)基本语句格式:ALTER TABLE tbale_name MODIFY col_name datatype;
例:
(删除字段)基本语句格式: ALTER TABLE table_name DROP COLUMN col_name; (drop column为字段删除的关键字)
例:
3、表的删除(DROP TABLE\TRUNCATE TABLE):
①截断方式删除表:仅清空表中数据,不会删除表,也不会删除表中的字段。
基本语句格式:TRUNCATE TABLE table_name;
例:
②完全删除方式删除表:会将整个表删除,包括表中数据和表的结构、字段。
基本语句格式:DROP TABLE table_name;
例:
4、向插入数据(INSERT INTO):
①纯插入方式
基本语句格式:INSERT INTO table_name (col_name1,col_name2…… ) VALUES(value1,value2……);
解释:values前的括号内为字段名集,后面为对应的值,值的数据类型必须与字段的数据类型一致,否则无法添加,同时,根据values前括号内的内容可以进行指定字段添加,例如:仅写一个username字段,values后括号内仅一个“zhangsan”,即对单一字段添加值
例:
②复制表数据
(在创建表时进行复制)基本语句格式:CREATE TABLE table_new AS SELECT col_name1,col_name2… | * FROM table_old;
解释:将table_old表中数据复制到新表中,可以指定字段,也可全部复制,全部复制使用*,具体字段指定字段名即可。
例:
(在添加时进行复制)基本语句格式:INSERT INTO table_new (col_name1,col_name2…… ) SELECT col-name1,col-name2… | * FROM table_old;
例:
5、修改表中数据(UPDATE ):
(无条件更新)基本语句格式:UPDATE table_name SET col_name1 = value1,col_name2=value2……; (这种方式会将表中全部数据进行更新)
例:
此例中将所有password字段值设为11111
(有条件更新)基本语句格式:UPDATE table_name SET col_name1=value1,col_name2=value2.. WHERE col-name=value…
例:
6、删除表中数据(DELETE):
①删除全部数据:
基本语句格式:DELETE FROM table_name; 这一句与上文截断的结果一致,删除该表中全部数据。
②删除具体某行的数据:
基本语句格式:DELETE FROM table_name WHERE col_name=value….;
例:
二、约束
1、主键约束(PRIMARY | CONSTRAINT)
注:一张表仅能有一个主键约束,主键约束可以由多个字段构成(联合主键,复合主键)。主键约束根据关键字PRIMARY KEY 和 CONSTRAINT来实现
创建表时添加主键约束:
① CREATE TABLE table_name (col_name1 datatype PRIMARY KEY);//创建表时,直接对某一属性指定为主键
②CONSTRAINT constraint_name PRIMARY KEY (col_name1,col_name2..);//表级约束,一般在创建表时,所有字段都完成后,最后追加这个语句,如下例子:
create table userinfo(
id number(6,0),
username varchar2(20),
rank varchar2(10),
constraint pk_key (id,username),
); //其中pk_key为人为命名的主键名称
修改表时添加主键约束:ADD CONSTRAINT constraint_name PRIMARY KEY(col_name1,col_name2..);//一般跟在 ALTER TABLE table table_name后,进行主键追加,如下例子:
ALTER TABLE userinfo ADD CONSTRAINT pk_id (id );
修改主键约束名称:RENAME CONSTRAINT old_name TO new_name; //用在ALTER TABLE table_name 后面,进行主键名称的更改,如下例子:
ALTER TABLE userinfo RENAME CONSTRAINT pk_id TO pk_newid;
删除主键约束:
①DROP CONSTRAINT constraint_name ; //彻底删除名称为constraint_name的主键约束,跟在ALTER TABLE table_name 后使用。
②DISABLE|ENABLE CONSTRAINT constraint_name;//这种方式为 禁用 | 开启 主键约束,并没有实质的删除,同样是跟在ALTER TABLE table_name 语句后面使用。
2、外键约束(REFERENCES)
创建表时添加外键约束:
①CREATE TABLE table1(col_name1 datatype REFERENCES table2(col-name));//通过关键字REFERENCES,选择table2中的字段作为外键,被引用的字段必须为table2的主键
②CONSTRAINT constraint_name FOREIGN KEY( col_name1,col_name2…) REFERENCES table2(col-name,col-name)[ON DELETE CASCADE ] //表级设置,同时指定级联删除,所谓级联删除就是,我们主表中的一条数据被删除,那么从表中引用主表字段的这一行数据也会被删除(通过ON DELETE CASCADE)。
修改表时设置外键约束:ADD CONSTRAINT constraint_name FOREIGN KEY( col_name1,col_name2…) REFERENCES table2(col-name,col-name)[ON DELETE CASCADE ]//跟在修改表语句后使用
删除外键约束:
①DROP CONSTRAINT constraint_name ; //彻底删除名称为constraint_name的外约束,跟在ALTER TABLE table_name 后使用。
②DISABLE|ENABLE CONSTRAINT constraint_name;//这种方式为 禁用 | 开启 外键约束,并没有实质的删除,同样是跟在ALTER TABLE table_name 语句后面使用。
3、非空约束(NOT NULL)
创建时添加非空约束:CREATE TABLE table_name (col_name datype NOT NULL);
修改表时添加非空约束:ALTER TABLE tbale_name MODIFY col_name datatype NOT NULL;
删除非空约束:ALTER TABLE tbale_name MODIFY col_name datatype NULL;
4、唯一约束(UNIQUE)
注:唯一约束可以有多个,而主键约束仅限一个,两者都有唯一限制
创建时添加非空约束:CREATE TABLE table_name (col_name datype UNIQUE); 或者 CONSTRAINT constraint_name UNIQUE (col-name,col-name);
修改表时添加非空约束:ALTER TABLE tbale_name ADD CONSTRAINT constraint_name UNIQUE (col-name,col-name);
删除非空约束:DISABLE|ENABLE CONSTRAINT constraint_name; 或者 DROP CONSTRAINT constraint_name ;
5、检查约束(CHECK)
注:检查约束就是限制数据的实际意义,比如现实生活中工资不会出现负数,所以在表设计的时候,工资字段就要设置检查约束,使其大于等于0
创建表的时候添加检查约束:CREATE TABLE table_name (col_name datype CHECK( 具体的约束条件 )); 或者 CONSTRAINT constraint_name CHECK (条件);
修改表时添加检查约束:ALTER TABLE tbale_name ADD CONSTRAINT constraint_name CHECK (条件);
删除检查约束:DISABLE|ENABLE CONSTRAINT constraint_name; 或者 DROP CONSTRAINT constraint_name ;
三、查询
1、运算符
算数运算符: +、-、*、/
比较运算符:> , >= , < , <= , = , <> 注:主要用在where关键字后面,结果为布尔值
逻辑运算符:NOT, AND , OR 用以链接条件,对应着并,或,非。
表达式 = 操作数 + 运算符 这里的操作数在oracle中可以是变量,常量,字段
2、基本查找语句(SELECT)
基本语句格式:SELECT col-name1,col-name2… | * FROM table_name;
3、条件查询(SELECT …. WHERE …)
基本语句格式:SELECT col-name1,col-name2… | * FROM table_name WHERE 具体的条件表达式
例如: SELECT username,password FROM userinfo WHERE id>=1 AND salary >500; //查询id大于1,工资大于500的所有用户的账号和密码信息
4、模糊查询(LIKE)
LIKE运算符可以归为比较运算符的一种,通过 LIKE 关键字配合通配符 ‘_,%’ 构成实现模糊查询的表达式,放置where后面即可。
” _ ” : 一个 ‘ _ ‘仅能代替一个字符,例如 a_,代表首字母为a,后面仅有一位字符的组合。
” % “:代表0个或多个字符,不限个数,例如 %a,代表末尾为a的所有字符组合
基本语句格式:SELECT * | col-name1,col-name2… FROM table_name WHERE col-name LIKE ‘ 通配符组成的信息 ‘
例如: SELECT * FROM userinfo WHERE username LIKE ‘a% ’ //查询用户名以a为首字母的所有信息
SELECT email FROM userinfo WHERE username LIKE ‘_a% ’ // 查询用户名第二个字符为a的邮箱
SELECT username FROM userinfo WHERE username LIKE ‘%a% ’ //查询用户名中包含a字母的所有用户的用户名
5、范围查询(BETWEEN…..AND….)
主要通过 between and 的组合构成某项属性的范围条件,与之前的条件查询中运用<,>运算符进行条件查询效果一致,同时进行条件表达式组合时也可以配合NOT 进行组合,例如 NOT BETWEEN 30 AND 50,即是查小于30和大于50的范围
基本语句格式: SELECT * | col-name1,col-name2… FROM table_name WHERE col-name BETWEEN ? AND ?;
6、并表查询
并表查询主要依附于表的链接(JOIN),表的链接分为,内连接,外连接,交叉连接。
内连接基本语句格式:SELECT *FROM table1 [inner] join table2 on table1.字段1=table2.字段1….;
内连接基本语句格式2:SELECT * FROM table1,table2 WHERE table1.字段1=table2.字段1….;
交叉连接:表与表之间做笛卡儿积,SELECT * FROM table1,table2….
外连接又可细分为左外连接,右外连接和全连接
①左外连接:LEFT OUTER JOIN
左侧的表中的全部数据都会被显示出来,但是右侧表的数据,只有和左侧匹配上的字段才会被查询出来!否则都会显示null!
基本语句格式1:SELECT * FROM table1 LEFT OUTER JOIN table2 on table1.col-name1=table2.col-name1;
基本语句格式2:SELECT* FROM table1 LEFT OUTER JOIN table2 where table1.col-name1=table2.col-name1;
②右外连接:RIGHT OUTER JOIN
右侧的表中的全部数据都会被显示出来,但是左侧表的数据,只有和右侧匹配上的字段才会被查询出来!否则都会显示null!
基本语句格式1:SELECT * FROM table1 RIGHT OUTER JOIN table2 on table1.col-name1=table2.col-name1;
基本语句格式2:SELECT* FROM table1 RIGHT OUTER JOIN table2 where table1.col-name1=table2.col-name1;
③全外连接:FULL/ALL OUTER JOIN
基本语句格式:SELECT * FROM table1 FULL OUTER JOIN table2 on table1.col-name1=table2.col-name1;