oracle表dml语句执行记录,SQL建表,SQLLDR导入数据,DML语句

建表语句

create table tablename

(

name1 varchar2(15) primary key,

name2 char,

name3 data,

name4 nubmber(p,s)

)

创建带主键约束的表(primary key)

create table ClassInfo

(

classid varchar2(15) primary key,

classname varchar2(50) not null,

classdesc varchar2(100)

)

创建带外键约束的表(foreing key)

create table StudInfo

(

studno varchar2(15) primary key,

studname varchar2(20) not null,

studsex char(2) check (studsex in ('男','女')),

studbirthday date,

classid varchar(10) not null,

constraint FK_classid foreign key (classid)

references classinfo(classid)//学生信息表依赖于班级信息表,班级ID为学生信息表的外键

)

创建联合主键约束的表{constraint PK_duoble primary key (one,two)}

create table StudScoreInfo

(

studno varchar2(15) not null,

courseid varchar2(15) not null,

studscore number(4,1) check(studscore>=0 and studscore<=100),

constraint PK_double primary key (studno,courseid)

//将学生ID和课程ID同时作为主键约束该表

)

创建带check约束条件的表

studsex char(2) check (studsex in ('男','女'))

//性别只能为男或女

studscore number(4,1) check (studsccore between 0 and 100)

//分数的取值范围为 0 ~ 100

通过脚本导入数据

将保存在Excel中的数据另存为以 .csv 结尾的纯文本文件,它的每一行代表一条记录,字段之间用 ' , '分割

编写控制文件(文件扩展名.ctl)

load data infile 'D:\classinfo.csv' //指定要导入的数据源(位置&文件名)

append into tablename[classinfo] //要导入数据表的表名

fields terminated by ',' //表明字段之间的分割符是逗号,如果是空格分割 by X'09'

(字段1，字段2，字段3...)//数据库中表名的字段名

load data infile 'D:\classinfo.csv'

appendinto into classinfo

fields terminated by ','

(classid,classname,classdesc)

2.执行控制文件

sqlldr userid=yhch/passwrod123@193.112.77.165/oracle control='c:\loadclassinfo.ctl'

DML语句

插入数据(insert)

简单插入

insert into tablename(字段1，字段2，字段3) values (value1,value2,value3)

将数据导入到已存在的表中

insert into tablename

select * from studinfo

将一张表的数据插入到新表

create table newtablename as

select * from studinfo

修改数据(update)

update tablename set 字段=newvalue where 更新条件

update studinfo set studname = 钱||substr(studname,2)

where studname like '赵%'

//将性赵的同学改为性钱

删除数据(delete)

删除表

drop table tablename

删除数据

delete from tsablename where 删除条件

delete from studinfo where studname='杨浩成'