4.SQL(数据库变更)
1.Insert(增加)
语法:
INSERT INTO表名[(列名1,列名2,...)]VALUES(值1,值2,...)
标准写法
简单写法(不建议)
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 修改条件;
全部更新
局部更新
把张三的领导取消
3.delete(删除)
语法 : DELETE FROM 表名 WHERE 删除条件;
在删除语句中如果不指定删除条件的话就会删除所有的数据
4.事务处理
范例:删除员工7934;
从结果上看似乎数据已经删除,但是我们再打开另一个窗口查看发现7934的员工还在
事务处理:所谓的事务管理就是要保证数据的完整性,要么同时成功,要么同时失败
当我们执行完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是系统自动分配的约束的名字
主键不可为空
我们可以自己来指定主键约束的名字
create table person(
pid number(10),
name varchar2(10),
gender number(1) default 1,
birthday date,
constraint person_pk_pid primary key(pid)
);
2.非空约束
使用非空约束,可以使指定的字段不可以为空。
范例:建立一张pid和name不可以为空的表
create table person(
pid number(10) not null,
name varchar2(10) not null,
gender number(1) ,
birthday date,
);
3.唯一约束(unique)
表中的一个字段的内容是唯一的
范例:建表一个name是唯一的表
create table person(
pid number(10) ,
name varchar2(10) unique,
gender number(1) ,
birthday date
);
唯一约束的名字也可以自定义
create table person(
pid number(10) ,
name varchar2(10),
gender number(1) ,
birthday date,
constraint person_name_uk unique(name)
);
4.检查约束
使用检查约束可以来约束字段值的合法范围。
范例:创建一张表性别只能是1或2
create table person(
pid number(10) ,
name varchar2(10),
gender number(1) check(gender in (1, 2)),
birthday date
);
检查约束也可以自定义
create table person(
pid number(10) ,
name varchar2(10),
gender number(1),
birthday date,
constraint person_gender_ck check(gender in (1,2))
);
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)
);
外键关联一定注意:
外键一定是主表的主键
删表时一定先删字表再删主表,如果直接删主表会出现由于约束存在无法删除的问题
但是可以强制删除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
这样删除主表数据的时候会把字表的关联数据一同删除
7.rownum
ROWNUM:表示行号,实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。
范例:查询emp表带有rownum列
select rownum, t.* from emp t
我们可以根据rownum来取结果集的前几行,比如前5行
但是我们不能取到中间几行,因为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部门的员工
语法2:CREATE OR REPLACE VIEW 视图名称 AS 子查询
如果视图已经存在我们可以使用语法2来创建视图,这样已有的视图会被覆盖。
create or replace view empvd20 as select * from emp t where t.deptno = 20
那么视图可以修改吗?
我们尝试着修改视图但是发现是视图所查询的表的字段值被修改了。所以我们一般不会去修改视图。
我们可以设置视图为只读。
语法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
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;
在插入数据时需要自增的主键中可以这样使用
在实际项目中每一张表会配一个序列,但是表和序列是没有必然的联系的,一个序列被哪一张表使用都可以,但是我们一般都是一张表用一个序列。
序列的管理一般使用工具来管理。
10.索引
索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o 次数,从而提高数据访问性能。索引有很多种我们主要介绍常用的几种:
为什么添加了索引之后,会加快查询速度呢?
图书馆:如果杂乱地放书的话检索起来就非常困难,所以将书分类,然后再建一个箱子,箱子里面放卡片,卡片里面可以按类查询,按姓名查或者类别查,这样的话速度会快很多很多,这个就有点像索引。索引的好处就是提高你找到书的速度,但是正是因为你建了索引,就应该有人专门来维护索引,维护索引是要有时间精力的开销的,也就是说索引是不能乱建的,所以建索引有个原则:如果有一个字段如果不经常查询,就不要去建索引。现在把书变成我们的表,把卡片变成我们的索引,就知道为什么索引会快,为什么会有开销。
创建索引的语法:
创建索引:
- 单例索引
单例索引是基于单个列所建立的索引,比如:
CREATE index 索引名 on 表名(列名)
- 复合索引
复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引,但是
要求列的组合必须不同,比如:
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层