一、用户
1.登录用户: username/password
2.切换用户: connect username/password
3.启用用户语句: alter user username account unlock
4.查看当前用户: show user
二、表空间
5.创建表空间: create [temporary] tablespace tablespace_name tempfile|datafile 'xxx.dbf' size file_size(10M); ss 6.查看表空间位置: desc dba_data_files; select file_name from dba_data_files where tablespace_name='TABLE_SPACENAME';
7.设置表空间联机/脱机,只读/读写状态: alter tablespace tablespace_name online/offline|read only/read write;
8.为表空间添加/删除数据文件: alter tablespace tablespace_name add/drop datafile 'xxx.dbf' [size 10M];
9.删除表空间[和表空间的数据文件]: drop tablespace tablespace_name [including contents];
三、管理表修改表
10.创建表: create table table_name ( column_name number(6,0), column_name varchar2(30)... );
11.为表添加字段: alter table table_name add new_column datatype;
12.更改表字段的数据类型: alter table table_name modify column datatype;
13.删除字段: alter table table_name drop column column_name;
14.修改字段名: alter table table_name rename column column_name to new_column_name;
15.修改表名: rename table_name to new_table_name;
16.截断表(删除表中的数据): truncate table table_name;
17.删除表(删除数据同时表也删除了): drop table table_name;
18.为表添加值: insert into table_name (column1,column2,...) values(value1,value2,...);
19.为表字段设置默认值: alter table table_name modify column default default_value;
20.复制表(创建的时候复制): create table table_name as select (column1,columnn3...)|* from copy_table_name;
21.复制表(插入表的时候复制): insert into table_name (column1,...) select (column1,...) from copy_table_name;
22.修改,更新表: update table_name set column_name='***' [where conditions]
23.删除表数据: delete from table_name [where conditions];
四、约束
24.非空约束: alter table table_name modify column_name datatype NUT NULL;
25.主键约束: 1:create table table_name (column column_name primary key,...);(创建表时直接在需要设置主键约束的字段后面添加 primary key) 2:create table table_name (col col_name,...,constraint constraint_name1 key(col1,col2,...);(创建表时设置了约束名constraint) 3:修改表时添加主键约束: alter table table_name add constraint constraint_name primary key(column);
26.修改主键名: alter table table_name rename constraint constraint_name to new_name;
27.创建表时添加外键约束: create table table2(col2 datatype references table1(col1),...);
28.创建表时设置外键约束(接连删除): create table table2(col_new datatype,..., constraint constraint_name foreign key(col_new) references table1(col)[on DELETE CASCADE]);
29.修改表时添加外键约束: alter table table2 add constraint constraint_name foreign key(col2) references table1(col1);
30.禁用/删除外键约束: alter table table_name disable|enable constraint constraint_name; alter table tablename drop constraint constraint_name;
31.创建唯一约束:(唯一约束的字段可以为null,而主键约束不能;一张表中唯一约束可以为多个,主键约束只能有一个) create table table_name (col datatype unique,...); 或者:create table table_name (col datatype,constraint constraint_name unique(col));
32.修改表的时候添加唯一约束: alter table table_name add constraint constraint_name unique(column);
33.检查约束: create table table_name (col datatype check(expressions),...);
34.检查约束: alter table table_name add constriant constrian_name check(expressions);
35.删除主键约束: alter table table_name drop primary key;
五、查询
36.基本查询: select [distinct] *|col_name,.. from table_name [where conditions];(distinct去掉重复的记录)
37.更改查询结果显示的字段名: col|column col_name heading col_name_new;
38.设置查询结果显示的字段长度: col|column col_name format a10/999.9(字符/数字的格式例$99.9|¥999.9);
39.清除设置的字段格式: col column_name clear;
40.查询的时候更改查询结果字段名的显示: select col as new_col,col2 as col2_new from table_name;
41.运算符优先级: 比较运算符高于逻辑运算符,not高于and高于or
42.模糊查询(关键字:like,通配符:,%): select * from table_name where username like 'a%'|'a_'|'a%';(代表一个字符,%代表一个或者多个字符。这里查询用户名已a开头|其他类型);
43.范围运算符(between and,in/not in): select * from table_name where salary between 800 and 2000; select * from table_name where username in('aaa', 'bbb');
44.对查询结果进行排序(order by...desc/asc): select ... from table_name [where] order by column1 desc/asc,...
45.case...when...then语句: 例1:select username,case username when 'aaa' then '计算机部门' when 'bbb' then '市场部门' else '其他部门' end as 部门 from table_name; 例2:select salary,case when salary<1000 then '底薪阶层' when salary between 1000 and 3000 then '中等阶层' when salary>5000 then '高新阶层' else '其他' end as 薪资阶层 from table_name;
46.decode函数: select username,decode(username,'aaa','计算机部门','bbb','市场部门','其他')as 部门 from table_name;
alter table userInfo add(msn varchar2(20));1、建表create table userInfo (id number(6),name varchar2(20),sex number(1),age number(3),birthday date,
address varchar2(50),email varchar2(25),tel number(11));2、创建约束不带约束名称的:create table userInfo (id number(6) primary key,--主键name varchar2(20) not null,--非空sex number(1),age number(3) default 18,
birthday date,
address varchar2(50),email varchar2(25) unique,--唯一
tel number(11),deptno number(2) references dept(deptno)—外键
);带约束名称:create table userInfo (id number(6) constraint id_pk primary key,name varchar2(20) constraint name_nn not null,sex number(1),age number(3) default 18,
birthday date,
address varchar2(50),email varchar2(25) constraint email_uqe unique,tel number(11),deptno number(2) constraint dept_deptno_ref references dept(deptno));列模式:create table userInfo (id number(6),name varchar2(20),sex number(1),age number(3) default 18,
birthday date,
address varchar2(50),email varchar2(25),tel number(11),deptno number(2),constraint id_pk primary key (id),--也可以两个以上,联合主键constraint dept_deptno_ref foreign key (deptno) references dept(deptno),constraint emial_name_uqe unique (email, name));Alter模式:alter table userInfo add(msn varchar2(20));alter table userInfo modify(msn varchar2(25));alter table userInfo drop(msn);alter table userInfo drop constraint id_pk;alter table userInfo add constraint id_pk primary key (id);3、创建视图create table v$_dept_viewas
select deptno, dname from dept;--重新编译视图alter view v$_dept_view compile;提示:视图一般是一个表或多个表的查询或子查询,这样可以减少代码量,但同时增加了对数据库视图的维护程度,如:某个表字段被删除或是修改,视图也要重新创建或修改,同时占用了数据库的一部分空间;视图就是一个虚拟的表格;4、创建索引普通索引:create index idx_dpt_dname on dept(dname);联合索引:create index idx_dept_dname_deptno on dept(dname, deptno);--唯一索引create unique index idx_emp_ename on scott.emp(ename);--反向键索引create index idx_emp_rev_no on scott.emp(empno) reverse;--位图索引create bitmap index idx_emp_name on scott.emp(dname);--索引组织表,一定要有主键create table tab (id int primary key,name varchar2(20)) organization index;
--索引组织表的insert效率非常低--分区表索引create index idx_name on table(col) local/global;--索引分区提示:当给表创建主键或唯一键约束时,系统也会创建一个约束给该字段;同样创建索引也会占用数据库空间;索引在访问、查询的时候效率有提高,但是在修改表的时候效率就会降低;5、创建序列create sequence seq;select seq.nextval from dual;insert into tab values(sql.nextval, ‘music’);create sequence seqtabstart with 2 –从2开始increment by 3—每次加3
nomaxvalue—没有最大值minvalue 1—最小值1nocycle—不循环nocache;--不缓存--修改序列 ,不能修改起始值alter sequence seqtabmaxvalue 1000;6、创建同义词同义词,顾名思义就是说别名、或是另一个名字。create synonym scott_emp for scott.emp;create public synonym scott_dept for scott.dept;select * from scott_emp;select * from scott_dept;7、创建表空间create tablespace HooMS
datafile 'E:\HooMS.dbf'
size 5M
autoextend on next 2M maxsize 10M;--创建用户、分配可以操作表空间create user hooidentified by hoo
default tablespace HooMS
temporary tablespace temp;
--创建表空间create tablespace myMS
datafile 'c:\myMS.dbf'
size 1M
autoextend on;
--扩展表空间--修改表空间大小alter databasedatafile 'c:\myMS.dbf'
resize 2M;--扩展表空间--添加数据文件alter tablespace myMS
add datafile 'c:\myMS_2.dbf'size 1M;
--设置dbf文件自动增长alter databasedatafile 'c:\myMS_2.dbf'
autoextend on next 2M maxsize 4M;--表空间重命名alter tablespace myMS
rename to hooMS;
--分离表空间(脱机)alter tablespace hooMS
offline temporary;
--归档模式下脱机alter tablespace hooMS
offline immediate;
--使表空间联机alter tablespace hooMS online;
--删除无数据的表空间drop tablespace hooMS;
--删除带数据的表空间drop tablespace hooMS
including contents;
Oracle 简单sql
最新推荐文章于 2021-07-21 03:04:02 发布