对Oracle的增删改查基本操作

来源：互联网发布：阿迪达斯淘宝推荐店铺编辑：程序博客网时间：2024/05/18 20:08

提示：操作生产库的表时，最好先用上一篇文章里的导入excel文件创建一个临时表，把要操作的数据全部准备好，都来自临时表，那样比较单一，操作会更加简单。

1.创建表
1）直接创建

    create table emp1(name varchar2(20),salary number(8,2)default 1000,id number(4),hire_date date    );

2）通过子查询的方式创建

   create table emp2   as    select last_name name,employee_id id,hire_date    from employees;

或者

    create table emp2    as     select last_name name,employee_id id,hire_date     from employees     where department_id = 80;/where 1=2;

2.修改表
1)增加新的列

   alter table emp1 add(birthday date);

2)修改现有的列

   alter table emp1 modify(name varchar2(25) default 'abc');

3)重命名现有的列
alter table emp1
rename column salary to sal;

4)删除现有的列

  alter table emp1 drop column birthday;

3.清空表中的数据(与delete from table_name区分开)

  truncate table emp2;

4.重命名表

  rename emp2 to emp3;

5.删除表

  drop table emp3;

数据处理

1）增
1.1 增添一条记录

  insert into  [表名](,,,,,)  values(,,,,,)

1.2 从其它表中拷贝数据

  insert into [表名]  select .... from [另一个表]   where ....

2）改

  update [表名]  set .....  where ....

3）删

  delete from [表名]  where ....

4）查

  select ....

约束：对创建的表的列属性、字段进行的限制。诸如:not null/unique/primary key/foreign key/check

1.如何定义约束---在创建表的同时，添加对应属性的约束
1.1 表级约束 & 列级约束

create table emp1(employee_id number(8),salary number(8),--列级约束hire_date date not null,dept_id number(8),email varchar2(8) constraint emp1_email_uk unique,name varchar2(8) constaint emp1_name_uu not null,first_name varchar2(8),--表级约束constraint emp1_emp_id_pk primary key(employee_id),constraint emp1_fir_name_uk unique(first_name),constraint emp1_dept_id_fk foreign key(dept_id) references departments(department_id) ON DELETE CASCADE

)

1.2 只有not null 只能使用列级约束。其他的约束两种方式皆可

过滤查询：四个基本的过滤查询

①between......and......包含两边边界

SELECT last_name, salaryFROM   employeesWHERE  salary BETWEEN 2500 AND 3500;

②in:在里面的一个

SELECT employee_id, last_name, salary, manager_idFROM   employeesWHERE  manager_id IN (100, 101, 201);

③like:模糊查询，%代表任意个字符，_代表一个字符

SELECT employee_id, last_name, salary, manager_idFROM   employeesWHERE  manager_id IN (100, 101, 201);

④null

SELECT last_name, manager_idFROM   employeesWHERE  manager_id IS NULL;

逻辑运算:and,or,not

SELECT employee_id, last_name, job_id, salaryFROM   employeesWHERE  salary >= 10000OR     job_id LIKE '%MAN%';

排序：order by; ASC:升序 DESC:降序, 必须在select语句的结尾处

SELECT   last_name, job_id, department_id, hire_dateFROM     employeesORDER BY hire_date ;

降序：

SELECT   last_name, job_id, department_id, hire_dateFROM     employeesORDER BY hire_date DESC ;

多个列排序：

SELECT last_name, department_id, salaryFROM   employeesORDER BY department_id, salary DESC;

单行函数
①控制大小写

LOWER('SQL Course')

UPPER('SQL Course')

SELECT employee_id, last_name, department_idFROM   employeesWHERE  LOWER(last_name) = 'higgins';

②字符控制函数

例子：

SELECT employee_id, CONCAT(first_name, last_name) NAME,        job_id, LENGTH (last_name),        INSTR(last_name, 'a') "Contains 'a'?"FROM   employeesWHERE  SUBSTR(job_id, 4) = 'REP';

0 0