Oracle sql 常用操作
时间：2021-07-01 10:21:17
帮助过：2人阅读
.创建一个表： (Oracle
database)
create table stu
(
id int,
name varchar(
20),
sex varchar(
10),
age int
);
2.向已有的表中插入一行数据：
insert into stu(id,name,sex,age)
values(
1,
‘huhu‘,
‘man‘,
20);
insert into stu(id,name,sex,age)
values(
212,
‘huiny‘,
‘woman‘,
22);
insert into stu(id,name,sex,age)
values(
315,
‘灰灰‘,
‘男‘,
23);
3. 查寻表中元素
1.查询所有
select *from stu;
2.查询部分(某几列)
select id,sex
from stu;
3.查询部分(某几行)
select *from stu
where id
=1;
4.查询表中记录条数
select count(
*)
from stu;
4.删除
1.删除表中记录
delete from stu
where id
=1;
2.删除表
drop table stu;
5.修改
update from ...;
6.约束
1.在表中直接添加约束
primary key(主键)
unique(唯一)
check(检查) 例：
check(stusex
in(
‘男‘,
‘女‘))
not null(非空)
foreign key(外键)
1.数据复用
2.字段可以拆成两列
1.两个表有关系，用外键
2.(一般)在元素多的一方加外键，(偶尔)考虑方向问题，不要造成数据重复
1.创建司机表
create table driver_table
(
flowid number(
12)
primary key,
driver_name varchar2(
90),
driver_sex varchar2(
20)
);
;创建车辆表
create table vehicle_table
(
vehicle_id number(
12)
primary key,
vehicle_type varchar2(
90),
vehcile_num varchar2(
90),
driver_id number(
12)
references driver_table(flowid)
);
3.删除两个表
drop table driver_table;
drop table vehicle_table;
4.向两个表中插入数据
insert into driver_table(flowid,driver_name,driver_sex)
values(
1,
‘yangee‘,
‘nan‘);
insert into vehicle_table(vehicle_id,vehicle_type,vehcile_num,driver_id)
values(
1,
‘sang‘,
‘yu-a3i3i‘,
1);
insert into vehicle_table(vehicle_id,vehicle_type,vehcile_num,driver_id)
values(
2,
‘sang‘,
‘yu-a3i3i‘,
2);
5.
select v.vehicle_type, v.vehcile_num, d.driver_name, d.driver_sex
from vehicle_table v
left outer join driver_table d
on v.driver_id
=d.flowid;
作业：1.练习多个表之间的增删改成，
2.设计你的项目，利用讲的原则
3.每个组设计一个数据库文档(数据库设计说明书)
自练 /*by myself*/
create table student
(
id int,
name varchar2(
8)
);
create table course
(
id int,
cname varchar2(
8)
);
drop table student;
drop table course;
insert into student(id,name)
values(
1,
‘张三‘);
insert into student(id,name)
values(
2,
‘李四‘);
insert into student(id,name)
values(
3,
‘王五‘);
insert into course(id,cname)
values(
1,
‘足球‘);
insert into course(id,cname)
values(
2,
‘音乐‘);
insert into course(id,cname)
values(
4,
‘美术‘);
/* 左连接 */
select * from student
left join course
on student.ID
=course.ID;
insert into student(id,name)
values(
1,
‘huiny‘);
insert into course(id,cname)
values(
1,
‘tuny‘);
/*右连接*/
select * from student
right join course
on student.ID
=course.ID;
/*完全外连接*/
select * from student
full join course
on student.ID
=course.ID;
/*内连接*/
select * from student
inner join course
on student.ID
=course.ID;
select student.id,student.name,course.cname
from student
inner join course
on student.ID
=course.ID;
/*交叉连接*/
select * from student
cross join course;
select * from student
cross join course
where student.ID
=course.ID;
/*链接：http://www.dedecms.com/knowledge/data-base/sql-server/2012/0709/2872.html */
select s.Name,C.Cname
from student_course
as sc
left join student
as s
on s.Sno
=sc.Sno
left join course
as c
on c.Cno
=sc.Cno;
查询不重复记录
select distinct id
from student;
select distinct name
from student;
order by 排序
/*默认从小到大排序*/
select *from student
order by id;
update 使用
update student
set name
=‘huhu‘ where id
=1;
显示所有表
select * from all_tables;
/***************************2015/07/20***************************/
1.创建表空间(oracle放表的地方)
/*表空间名：account */
create tablespace account datafile
‘c:/account.dbf‘ size 25M;
2.创建用户(与表空间绑定)
/*用户名:account 密码:account 用户空间:account */
create user account identified
by account
default tablespace account;
3.用户授权
grant resource,connect
to account;
/*授权*/
/**创建自己的表空间**/
create tablespace huiny datafile
‘c:/huiny.dbf‘ size 25M;
create user huhu identified
by huhu
default tablespace huiny;
grant resource,connect
to huhu;
Oracle sql 常用操作
标签：
