oracle

来源：互联网发布：魅族手机仅限数据连接编辑：程序博客网时间：2024/05/29 19:00

oracle
数据类型
  数字
   number(7,2)
   integer整型
   float 浮点型
  字符
   char(1)      定长 10
   varchar2(20)   可变长
   long   长字符串
  日期
   date 精确到天
   timestamp 精确到毫秒

建表语句
  create table test1(
   id number,
   name varchar2(20),
   age integer
  );

约束
   主键   pk   primary key
   唯一   uk  unique
   非空   nk  not null
   检查   ck    check（1-10，（1,2,3），‘%@%’）
   默认   dk  default
   外键   fk  foreign key

   and not or

   create table student(
    sid number primary key,
    sname varchar2(20) not null unique,
    birthday date default sysdate,
    age integer check(age between 1 and 150),
    sex char check(sex in('m','f')),
    email varchar2(20) check(email like '%@%')
   );

   like '@'   _ 一个字符位置
        %任意长度字符
        [0-9a-z] 一个字符区间内
        [^0-9]不在区间内的一个字符

表和表之间的关系
  1-1
   唯一外键
    create table person1(
     pid number primary key,
     pname varchar2(20)
    );
    create table passport(
     ppid number primary key,
     num char(10),
     person_id number references person1(pid) unique
    );

   共享主键
   create table person2(
     pid number primary key,
     pname varchar2(20)
    );
    create table passport2(
     ppid number primary key references person2(pid),
     num char(10)
    );

  1-*
   create table classes_405(
    cid number primary key,
    cname varchar2(20)
   );
   create table student_405(
    sid number primary key,
    sname varchar2(20),
    age integer,
    classes_id number references classes_405(cid)
   );

  *-*
  create table student_m2m(
   sid number primary key,
   sname varchar2(20)
  );
  create table teacher_m2m(
   tid number primary key,
   tname varchar2(20)
  );
  create table st_m2m(
   student_id number references student_m2m(sid),
   teacher_id number references teacher_m2m(tid)
  );
  alter table st_m2m
  add constraints st_m2m_uk unique(student_id,teacher_id);


序列
  create sequence student_seq;
  nextval 获取下一个值
  currval   返回当前序列的值

  select student_seq.nextval from dual;
增
  insert into student(sid,sname,sex,email)
  values(student_seq.nextval,'zhangsan','m','aass@qwq.com');
删
  delete from student
  where sid = 1;
改
  update student set sname='lisi',email='assss'
  where sid = 1;

事务
  默认开启事务 commit; rollback;

查

cmd

sqlplus scott/tiger

查询
  select *
  from tablename

  select first_name,last_name name from s_emp;

  select first_name||'_'||last_name NAME from s_emp;

  select id,first_name,salary
  from s_emp
  where id<20 and first_name like 'M%';

  排序
  select id,first_name,salary
  from s_emp
  where id<20
  order by salary desc,id desc;

  连接查询
  s_emp s_dept
   笛卡尔连接
   select * from s_emp e,s_dept d; 避免

   等值连接条件 =<>
   select e.last_name,d.name
   from s_emp e,s_dept d
   where e.dept_id = d.id;

   内连接
   select e.last_name,d.name
   from s_emp e inner join s_dept d
   on e.dept_id = d.id;

   外连接（左外，右外right outer join，全外 full outer join）
   select e.last_name,d.name
   from s_emp e left outer join s_dept d
   on e.dept_id = d.id;

   自连接
   员工名称经理名称
   select e.first_name,m.first_name
   from s_emp e join s_emp m
   on e.manager_id = m.id;

   A
   B
   C
   D
   a b a.id<b.id
   A B 1 2
   A C 1 3
   A D 1 4
   B C 2 3
   B D 2 4
   C D 3 4
   select a.name||b.name
   from test a,test b
   where a.id<b.id;

  三表连接
   select e.first_name,d.name,r.name
   from s_emp e join s_dept d on e.dept_id = d.id
   join s_region r on d.region_id = r.id;

分组
子查询