Oracle的增删改查语法

原博客链接1：https://www.cnblogs.com/vivi-lxm/p/5081863.html

原博客链接2：https://blog.csdn.net/yelang0111/article/details/77435025

原博客链接3：https://www.cnblogs.com/garyzhuang/p/9670411.html

创建一个表：

cteate table 表名（列1 类型，
列2 类型）;
查看表结构 desc表名
添加一个字段 alter table 表名 add（列类型）;
修改字段类型 alter table 表名 modify(列类型);
删除一个字段 alter table 表名 drop column列名;
修改表的名字 rename student to stu
删除表 drop table 表名

 

插入数据:
1、 create table test(
id number,
name varchar2(20),
age number(2)
);
2、
insert into 表名 values(所有列的值);
insert into test values(1,'zhangsan',20);

插入的的数据应该与字段数据类型相同
数据的大小应该在列规定的范围内
在values中列出的数据位置必须与被加入的列的排列位置响对应


3、
insert into 表名（列）values(对应的值);
insert into test(id,name)values(2,'lisi');
更新语句：
update 表 set 列=新的值[where 条件]--》更新满足条件的记录
update test set name='zhangsan2'where name='zhangsan'
update 表 set 列=新的值--》更新所有的数据
update test set age=20;
删除数据：
1、delete from 表名 where 条件--》删除满足条件的记录
delete from test where id=1;
delete from test-->删除所有
commit；--》提交数据
rollback；-->回滚数据
2、truncate table 表名
删除所有数据，不会影响表结构，不会记录日志，数据不能恢复--》删除很快
3、drop table 表名
删除所有数据，包括表结构一并删除，不会记录日志，数据不能恢复--》删除很快
如何看一个SQL执行了多长时间：set timing on
如何快速的复制数据:1、insert into test select * from test;
2、create table 表名 select * from test;
导入数据：@文件名
去除重复的显示：select distinct 列 from 表名
日期类型:to_date(字符串1，字符串2)字符串1是日期的字符串，字符串2是格式
to_date('1990-1-1','yyyy-mm-dd')-->返回日期的类型是1990-1-1
SQL的模糊查询：like
查询结果需要排序 order by字段 
select * from emp order by sal-->> order by后面，默认升序
asc 升序 desc降序
group by 字段--》按照指定的字段进行升序
having 字句--》对分组后的结构进行筛选输出

① 进入数据库:

 sqlplus“/as sysdba” 或者sqlplus / as sysdba

 注：完整格式：  sqlplus“用户名/密码@数据库名as sysdba”

 注：请注意，sqlplus非常撇脚，没能回删，没有方向键，等等，最好安装一个rlwrap。

 详情链接：http://blog.csdn.net/wjx515628/article/details/35238097

② 查看表空间（表空间就是库的意思）

 方法1：只查询所有表空间的名称

select tablespace_name from dba_tablespaces;

 方法2：查询某张表所在的表空间的名称

select * from user_tables where table_name=‘表名';

 方法3：查询当前用户默认的表空间

Select default_tablespace from user_users;

 方法4：查看当前用户可以查看的所有表空间名以及其所拥有的属性

Select * from sys.dba_tablespaces;

 方法5：查看所有用户的默认表空间和临时表空间

Select username,default_tablespace,temporary_tablespace from dba_users where username like ‘%SYS%’;

③ 创建表空间（create tablespaces）（注：最好指定一下用户，只能由管理员才能创建）

 1)数据文件一个

Create [temporary] tablespace 表空间自定义名称 datafile ‘linux系统自定义路径和以.ora结尾的文件名’  size <数值M>  extent management local uniform size <数值M>;

 （注：放在oradata目录下，最好将不同用户的数据文件放在oradata下建立的对应用户名的文件夹中，放在其中，便于管理）

 2)数据文件多个

    Create [temporary] tablespace 自定义名 datafile ‘路径1’ size 数值M   ,
     
    ‘路径2’ size 数值M  ,
     
    ... ...
     
    [Extent management local]
     
    Uniform size 数值M;    

注：加了[temporary]后，新建的表空间是临时表空间，不加则是永久表空间。

④ 删除表空间

 方法1：

Drop tablespace 表空间名;

 注：需要自己手动删除对应路劲下的数据文件

 方法2：

drop talespace 表空间名 including contents and datafiles;

⑤ 修改表空间指定的数据文件的增大设定

 方法1：

Alter database datafile ‘文件所在路径’ autoextend on next 数值m;

 注：表示在原有大小的基础上增加数值m（看数值是1还是多少，数值是正整数型的）

 方法2：

Alter database datafile ‘文件所在路径’ autoextend on next 数值1m maxsize 数值2m;

 注：表示在原有大小的基础上增加数值1m，增加量直到数值2m为止！

⑥ 修改默认表空间

 查看目前设置的默认表空间和临时表空间（对未指定表空间和临时表空间的用户起作用）：

Select property_name,property_value from database_properties where property_name like ‘%TABLESPACE%’;

 类型1：在创建用户的时候没有指定默认表空间和临时表空间的时候起作用

 修改默认永久表空间：

 Alter database default tablespace 永久表空间名称;

 修改默认临时表空间：

Alter database default temporary tablespace 临时表空间名称；

 注：在创建表空间的时候永久和临时就有区别，所以指定临时表空间名的时候不要指到永久表空间名；指定永久表空间名的时候也是一样的。

 类型2：将用户本来指定的默认表空间更换。

 更换用户的永久表空间：

Alter user 用户名 default tablespace 表空间名称;

 更换用户的临时表空间：

Alter user 用户名 temporary tablespace 临时表空间名称;

⑦ 表空间状态查看（是否在线）

 方法1：查看所有表空间的在线状态：

Select tablespace_name,status from dba_tablespaces;

⑧ 表空间脱机（其实脱机就是用户不可访问，联机就是可访问）

 Alter  tablespace 表空间  offline 选项四选一;

解释：当表空间为脱机状态的时候，任何用户都访问不了的状态；（值得注意的是，system表空间无法脱机，因为数据库运行中，始终都会用到system表空间中的数据）。表空间脱机有如下四个选项：normal（正常），temporary（临时），immediately（立即），for recover（进行恢复）

 

⑨ 表空间联机

Alter tablespace 表空间名 online;

⑩ 增加表空间数据文件（为表空间增加数据库文件）

Alter  tablespace 表空间自定义名称 add datafile ‘自定义路径’ size 数值M；

⑪ 查看表

 类型1：查看当前用户权限内的所有表空间的所有表，同时显示每张表的属性。

Select * from user_tables;

 类型2：查看指定表空间下的所有表，只显示表空间名和表名

SELECT TABLESPACE_NAME,TABLE_NAME FROM DBA_TABLES WHERE TABLESPACE_NAME='表空间名称';

⑫ 创建表（可以指定到某个表空间中）

 方法1：

Create table 自定义表名(自定义字段名1 约束内容,自定义字段名2 约束内容,.. ..自定义字段名n 约束内容) [tablespace 已有表空间名字]；

  [tablespace已有表空间名字]  这个表示为表指定表空间，有需要的时候才写。

 方法2：

Create table 新表名 as <select语句>

 注：方法二中，可以让select语句中的查询条件不成立，这样就会建立一张数据为空的表格；方法二中得到的表格将不存在任何约束，需要自己添加。

 注：最后中括号中的内容为可加可不加，看是需要将表建立在默认表空间还是指定表空间中。

 数据类型：char(数值)  varchar2（数值）number（总位数，小数位数）date timestamp clob    blob

 注：表和索引一旦创建被指定表空间后，其所属表空间将无法修改；若想更改，就需要当网上搜（不想写~~~）

⑬ 删除表

drop table 表名;

 注：表中列为其他表外键且有数据应先解除约束，或删除相关表

⑭ 修改表名

Rename 旧表名 to 新表名

⑮ 查看表字段

Desc 表名；

⑯ 增加表字段

Alter table 表名 add 字段名 数据类型;

 注：comment on column表名.列名is '列注释，自定义';

 （注释中的命令是对字段进行注释）

⑰ 删除表字段

Alter table 表名 drop column 列名；

⑱ 修改表字段类型

Alter table 表名 modify(字段名 新的数据类型);

 (注，一般来说字段类型不可随便更改：大多数情况下，若实在需要改变，需要字段下没有数据；有的话，需要新建一个需求的类型的字段列，然后将旧字段列的值移动到新字段中，从而实现类型更改。少部分情况可以直接修改类型不报错！一旦报错则按照大多数情况下的解决方法。)

 详情可见：

  点击打开链接

⑲ 修改表字段名称

Alter table 表名 rename column 旧字段名 to 新字段名

⑳ 查看表数据（这个是重点，查询以后细讲）

Select <*|列名|表达式> from 表名 [where 条件] [order by 列名]

 注：<>中必须三选一；列名集或后者混用集（逗号为分隔符）

 注：order by默认排序方式是升序。

21 增加表数据

 方法1：

    Insert into 表名(列名1,列名2......) values(值1,值2......);
     
    Commit;

 注：可以将列名集合去掉，默认为所有列加值；字符串或者字符需要加引号。

 方法2：

    Insert into 表名 <select语句> [union <select 语句>];
     
    Commit;

22 删除表数据

 方法1：

    Delete from 表名 where 条件；
     
    Commit

 方法2：

    Truncate table表名；
     
    Commit

 注：两者区别，delete可以将表清空，同时可以通过日志恢复；truncate清空之后不能通过日志恢复，不过它的清空速度很快！一般来说truancate慎用，最好不用！

23 修改表数据

    Update 表名 set 列名1=值1，列名2=值2 .. ..  where 条件;
     
    Commit;

24 创建用户 （只是创建用户，不具备任何数据库权限）

Create user 用户名 identified by 口令 [account lock|unlock]

 (注：如果是lock状态的话，用户无法正常登录；口令就是密码，不需要引号；后面的[]内的东西可以写可不写，默认unlock) (注：创建用户后最好为用户指定一下默认的表空间，那么该用户所有的数据库对象都会放入表空间）

25 删除用户

Drop user 用户名 cascade

 注：删除用户的同时需要关注，其默认的表空间是否需要被删除。

26 修改用户锁定状态

Alter user 用户名 account lock|unlock

27 为用户指定默认表空间

Create user 用户名 identity by 密码口令 default tablespace 表空间名

28 用户授权（系统权限只能有dba用户授权，对象权限由拥有该对象的用户授权）

Grant  <角色|权限> to 用户

 常用权限（角色抑或权限）：connect，resource，DBA

29 修改授权用户密码

Alter user 用户名 identified by 新密码

30 查看授权用户的密码

select username,password from dba_users where username='SYSTEM';

 注意：在g11版本中，字段password将不显示值。

 所以可以查看还在使用默认密码的用户名：

 SELECT * FROM DBA_USERS_WITH_DEFPWD ;

 关于修改和查看密码的详细解析链接（只限于9-11g）：

 点击打开链接

31 回收权限

Revoke 角色|权限 from 用户

32 查看所有授权用户

Select * from dba_users;

 注：该方式可以查到每个用户的相关属性，可以查询username字段的值

33创建约束命令

Alter table 已有表名 add constraint 自定义约束名 约束内容

参考资源：

关于表空间的详细解析：

点击打开链接

关于表空间的详细属性操作：

点击打开链接

关于表空间和用户名创建和联系的具体步骤：

点击打开链接

关于新建用户未指定默认永久表空间和临时表空间，设置并修改或查看该信息：

点击打开链接
---------------------
作者：yes_is_ok
来源：CSDN
原文：https://blog.csdn.net/yes_is_ok/article/details/79271965
版权声明：本文为博主原创文章，转载请附上博文链接！