oracle学习笔记

启动Oracle,在cmd模式下依次启动:
net start oracleservice服务名
lsnrctl start  启动监听程序
关闭服务为:
lsnrctl stop
net stop oracleservice服务名

1. Oracle安装完成后的初始口令?
internal/oracle
sys/change_on_install
system/manager
scott/tiger
sysman/oem_temp

修改用户密码：登陆sys：conn / as sysdba 然后执行：alter user user_name identified by new_password.
增加新的表空间：两种：
1：create tablespace testspace datafile 'd:\dataspace\testspace.dbf' size 50m autoextend on next 10m maxsize unlimited;
2： create tablespace bojeryspace datafile 'd:\dataspace\bojeryspace' size 50m autoextendon next 10m maxsize unlimited;
创建用户给表空间： create user test identified by test default tablespace testspace;
给用户权限： grant connect,resource,dba to bojery;（连接，开发，管理一起给）
创建
create table student(
id int primary key,
name varchar2(10) unique,
age int check(age>18 and age<45),
addr varchar2(10),
cid int references student(id))

添加字段：alter table student add（price number（8,2））
添加主键 alter table student1 add constraints pk_student_name primary key(id);
添加外键
SQL> alter table student1 add constraints fk_student1_cid foreign key(cid) references clas
s(id);
修改字段： alter table student1 modify(name varchar2(15));
删除字段： alter table student drop column name;
关联表给两个字段加主键
  创建时create table student_class（
a int，b int constraints pk_name primary key(a,b);
   创建后加alter table student_class add constraints pk_name primary key(a,b)
修改表中数据
update table_name set column(字段名）='value'（新的值）；
删除表中一条记录
delete from table_name where column='value';
添加一条数据
insert into table_name [(column1 type...)]values(' ',.....)
select column1,... from table_name where column='value';
并列条件
select name,sal from emp where sal>1000 and job='SALESM';
grant 授权 revole 撤销
创建用户：create user myuser identified by bojery;
 grant create table to user_java18

a:between  and :
select * from emp where sal between 500 and 1900;
b:like
select * from emp where ename like'M_'(1个)或‘M%（多个）’
c:order by column
 select * from emp order by sal;(默认升序 )
 select* from emp order by sal desc;(降序)
 order by column1,column2
多表查询 select ename, loc from emp,dept where emp.deptno=dept.deptno and empno=7782;
 
序列使用
创建
 create or replace sequence myseq
 minvalue 1
 maxvalue 10000
 start with 1
 increment by 1
 cache 20;
使用
 create table student2(
 id int,
 name varchar(20),
 sex varchar(10),
 age number)

 insert into student2 values(myseq.nextval,'aaaa','boy',23);