4.SQL(数据库变更)

2021-07-17 05:15:10

1.Insert（增加）

语法：
INSERT INTO表名[(列名1，列名2，...)]VALUES(值1，值2，...)
标准写法
4.SQL(数据库变更)

简单写法（不建议）
INSERT INTO 表名VALUES(值1，值2，...)
insert into myemp values(7789, ‘张三‘, ‘开发‘, 7839, to_date(‘1992-10-22‘, ‘yyyy-MM-dd‘), 2000, 200, 10);

注意：使用简单的写法必须按照表中的字段的顺序来插入值，而且如果有为空的字段使用null

insert into myemp values(7790, ‘张三‘, ‘开发‘, null, to_date(‘1992-10-22‘, ‘yyyy-MM-dd‘), 2000, null, 10);

2.update（修改）

全部修改：UPDATE 表名 SET 列名1=值1，列名2=值2，....
局部修改：UPDATE 表名 SET 列名1=值1，列名2=值2，....WHERE 修改条件；

全部更新
4.SQL(数据库变更)

局部更新
4.SQL(数据库变更)

把张三的领导取消
4.SQL(数据库变更)

3.delete（删除）

语法 : DELETE FROM 表名 WHERE 删除条件;
4.SQL(数据库变更)
在删除语句中如果不指定删除条件的话就会删除所有的数据

4.事务处理

范例：删除员工7934；
4.SQL(数据库变更)

从结果上看似乎数据已经删除，但是我们再打开另一个窗口查看发现7934的员工还在
4.SQL(数据库变更)

事务处理：所谓的事务管理就是要保证数据的完整性，要么同时成功，要么同时失败
当我们执行完delete语句时，我们去数据库中去查看数据，发现并没有我们删除数据，这是因为oracle的事务对数据库的变更的处理，我们必须做提交事务才能让数据真正的插入到数据库中，在同样在执行完数据库变更的操作后还可以把事务进行回滚，这样就不会插入到数据库。如果事务提交后则不可以再回滚。
提交：commit
回滚：rollback

在oracle中会数据库变更会发生锁的情况（此处应用可以解决项目多线程并发带来的数据安全问题）
当两个数据库的连接同时来修改同一数据时，一定会有一连接先修改，另一个连接就会等待直到第一个连接修改完毕再修改

5.表的管理

1.常用的数据类型

No 数据类型 描述
1 Varchar， varchar2 表示一个字符串
2 NUMBER NUMBER(n)表示一个整数，长度是n
NUMBER(m,n):表示一个小数，总长度是m，小数是n，整数是m-n
3 DATA 表示日期类型
4 CLOB 大对象，表示大文本数据类型，可存4G
5 BLOB 大对象，表示二进制数据，可存4G

2.建表

语法：

Create table 表名（
字段1 数据类型 [default 默认值],
字段2 数据类型 [default 默认值],
...
字段n 数据类型 [default 默认值]
）;
范例：创建person表

create table person(
       pid      number(10),
       name     varchar2(10),
       gender   number(1)  default 1,
       birthday date
);

insert into person(pid, name, gender, birthday)
values(1, ‘张三‘, 1, to_date(‘1999-12-22‘, ‘yyyy-MM-dd‘));

3.表的删除

语法：DROP TABLE 表名

4.表的修改

在sql中使用alter可以修改表

添加语法：ALTER TABLE 表名称 ADD(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]...)
修改语法：ALTER TABLE 表名称 MODIFY(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]...)

注意修改时如果原有某列的数据长度为200，则不可以把该列修改成小于200的列

范例：在person表中增加列address

alter table person add(address varchar2(10));

范例：把person表的address列的长度修改成20长度

alter table person modify(address varchar2(20));

5.截断表

在person表中使用delete语句删除数据，则可以使用rollback来回滚，如果想要清空一张表的数据，同时不想回滚可以立即释放资源需要使用截断表的语法
语法：TRUNCATE TABLE 表名
范例：截断person表

truncate table person;

6.约束

在数据库开发中，约束是必不可少，使用约束可以更好的保证数据的完整性。

1.主键约束

主键约束都是在id上使用，而且本身已经默认了内容不能为空，可以在建表的时候指定。

创建一张表，把pid作为主键

create table person(
       pid      number(10) primary key,
       name     varchar2(10),
       gender   number(1)  default 1,
       birthday date
);

主键不可重复， SCOTT.SYS_C0017981是系统自动分配的约束的名字

4.SQL(数据库变更)

主键不可为空

4.SQL(数据库变更)

我们可以自己来指定主键约束的名字

create table person(
       pid      number(10),
       name     varchar2(10),
       gender   number(1)  default 1,
       birthday date,
       constraint person_pk_pid primary key(pid)
);

4.SQL(数据库变更)

2.非空约束

使用非空约束，可以使指定的字段不可以为空。
范例：建立一张pid和name不可以为空的表

create table person(
       pid      number(10) not null,
       name     varchar2(10) not null,
       gender   number(1)  ,
       birthday date,
);

4.SQL(数据库变更)

3.唯一约束（unique）

表中的一个字段的内容是唯一的
范例：建表一个name是唯一的表

create table person(
       pid      number(10) ,
       name     varchar2(10) unique,
       gender   number(1)  ,
       birthday date
);

4.SQL(数据库变更)

唯一约束的名字也可以自定义

create table person(
       pid      number(10) ,
       name     varchar2(10),
       gender   number(1)  ,
       birthday date,
       constraint person_name_uk unique(name)
);

4.SQL(数据库变更)

4.检查约束

使用检查约束可以来约束字段值的合法范围。
范例：创建一张表性别只能是1或2

create table person(
       pid      number(10) ,
       name     varchar2(10),
       gender   number(1)  check(gender in (1, 2)),
       birthday date
);

4.SQL(数据库变更)

检查约束也可以自定义

create table person(
       pid      number(10) ,
       name     varchar2(10),
       gender   number(1),
       birthday date,
       constraint person_gender_ck check(gender in (1,2))
);

4.SQL(数据库变更)

5.外键约束

之前所讲的都是单表的约束，外键是两张表的约束，可以保证关联数据的完整性。
范例：创建两张表，一张订单表，一张是订单明细表，订单和明细是一对多的关系

create table orders(
       order_id      number(10) ,
       total_price   number(10,2),
       order_time date,
      constraint orders_order_id_pk primary key(order_id)
);

create table order_detail(
       detail_id      number(10) ,
       order_id   number(10),
       item_name  varchar2(10),
       quantity   number(10),
      constraint order_detail_detail_id_pk primary key(detail_id)
);

insert into orders values(1, 200, to_date(‘2015-12-12‘,‘yyyy-MM-dd‘));
insert into order_detail values(1, 2, ‘java‘,1);

我们在两张表中插入如上两条数据，我们发现在order_detail表中插入的order_id在order表中并不存在，这样在数据库中就产生了脏数据。此时需要外键来约束它。

我们再次建表

create table orders(
       order_id      number(10) ,
       total_price   number(10,2),
       order_time date,
      constraint orders_order_id_pk primary key(order_id)
);

create table order_detail(
       detail_id      number(10) ,
       order_id   number(10),
       item_name  varchar2(10),
       quantity   number(10),
      constraint order_detail_detail_id_pk primary key(detail_id),
      constraint order_detail_order_id_fk foreign key(order_id) references orders(order_id)
);

4.SQL(数据库变更)

外键关联一定注意：
外键一定是主表的主键
删表时一定先删字表再删主表，如果直接删主表会出现由于约束存在无法删除的问题

4.SQL(数据库变更)

但是可以强制删除drop table orders cascade constraint;(不建议)
删除主表的数据可以删除子表的关联数据，再删主表，也可以使用级联删除级联删除在外键约束上要加上on delete cascade 如constraint order_detail_order_id_fk foreign key(order_id) references orders(order_id) on delete cascade
这样删除主表数据的时候会把字表的关联数据一同删除

4.SQL(数据库变更)

7.rownum

ROWNUM:表示行号，实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。
范例：查询emp表带有rownum列
select rownum, t.* from emp t
4.SQL(数据库变更)

我们可以根据rownum来取结果集的前几行，比如前5行
4.SQL(数据库变更)

但是我们不能取到中间几行，因为rownum不支持大于号，只支持小于号，如果想实现我们的需求怎么办呢？答案是使用子查询，也正是oracle分页的做法。
select * from (select rownum rm, a.* from (select * from emp) a where rownum < 11) b where b.rm > 5

8.视图

视图就是封装了一条复杂查询的语句。
语法1.：CREATE VIEW 视图名称 AS 子查询
范例：建立一个视图，此视图包括了20部门的全部员工信息
create view empvd20 as select * from emp t where t.deptno = 20

视图创建完毕就可以使用视图来查询，查询出来的都是20部门的员工
4.SQL(数据库变更)

语法2：CREATE OR REPLACE VIEW 视图名称 AS 子查询
如果视图已经存在我们可以使用语法2来创建视图，这样已有的视图会被覆盖。
create or replace view empvd20 as select * from emp t where t.deptno = 20

那么视图可以修改吗？
4.SQL(数据库变更)
4.SQL(数据库变更)

我们尝试着修改视图但是发现是视图所查询的表的字段值被修改了。所以我们一般不会去修改视图。
我们可以设置视图为只读。
语法3：CREATE OR REPLACE VIEW 视图名称 AS 子查询 WITH READ ONLY
create or replace view empvd20 as select * from emp t where t.deptno = 20 with read only
4.SQL(数据库变更)

9.序列

在很多数据库中都存在一个自动增长的列,如果现在要想在oracle 中完成自动增长的功能, 则只能依靠序列完成,所有的自动增长操作,需要用户手工完成处理。

语法：CREATE SEQUENCE 序列名 
     [INCREMENT BY n] 
     [START WITH n] 
     [{MAXVALUE/ MINVALUE n|NOMAXVALUE}] 
     [{CYCLE|NOCYCLE}] 
     [{CACHE n|NOCACHE}];

范例:创建一个seqpersonid的序列,验证自动增长的操作
CREATE SEQUENCE seqpersonid;
序列创建完成之后,所有的自动增长应该由用户自己处理,所以在序列中提供了以下的两种操作：
NextVal :取得序列的下一个内容
CurrVal :取得序列的当前内容

select seqpersonid.nextval from dual;
select seqpersonid.currval from dual;

在插入数据时需要自增的主键中可以这样使用
4.SQL(数据库变更)

    在实际项目中每一张表会配一个序列，但是表和序列是没有必然的联系的，一个序列被哪一张表使用都可以，但是我们一般都是一张表用一个序列。
    序列的管理一般使用工具来管理。

10.索引

    索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o 次数,从而提高数据访问性能。索引有很多种我们主要介绍常用的几种:
    为什么添加了索引之后，会加快查询速度呢？
    图书馆：如果杂乱地放书的话检索起来就非常困难,所以将书分类，然后再建一个箱子，箱子里面放卡片，卡片里面可以按类查询,按姓名查或者类别查,这样的话速度会快很多很多，这个就有点像索引。索引的好处就是提高你找到书的速度，但是正是因为你建了索引，就应该有人专门来维护索引，维护索引是要有时间精力的开销的，也就是说索引是不能乱建的，所以建索引有个原则：如果有一个字段如果不经常查询，就不要去建索引。现在把书变成我们的表，把卡片变成我们的索引，就知道为什么索引会快，为什么会有开销。

创建索引的语法：
创建索引：

  1. 单例索引
    单例索引是基于单个列所建立的索引，比如:
    CREATE index 索引名 on 表名(列名)
  2. 复合索引
    复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是
    要求列的组合必须不同,比如：
Create index emp_idx1 on emp(ename,job);
Create index emp_idx1 on emp(job,ename);

范例：给person表的name建立索引
create index pname_index on person(name);
范例：给person表创建一个name和gender的索引
create index pname_gender_index on person(name, gender);

索引的使用原则：

  • 在大表上建立索引才有意义
  • 在where子句后面或者是连接条件上建立索引
  • 索引的层次不要超过4层

4.SQL(数据库变更)

上一篇：oracle 12c重新创建数据字典和系统package


下一篇：重新想象 Windows 8 Store Apps (48) - 多线程之其他辅助类: SpinWait, SpinLock, Volatile, SynchronizationContext, CoreDispatcher, ThreadLocal, ThreadStaticAttribute