--建表语句
/* create table userInfo(
id number(6,0),
username varchar2(20),
userpwd varchar2(20),
email varchar2(30),
regdate date
) */
//向表里添加字段
--alter table userInfo add remard varchar2(500)
//删除字段(注意关键字drop column)
--alter table userInfo drop column sex
//修改表中字段的数据类型
--alter table userInfo modify userpwd number(6,0)
//修改表字段名
--alter table userInfo rename column remard to remarks
//修改表名
--rename userInfo to usersinfo
//查询表信息
--select * from usersInfo
//删除表中的全部数据(速度较drop来说快)
--truncate table usersInfo
//删除整个表
--drop table usersInfo
/*******************************添加、复制、修改、删除表数据*********************************************/
//添加表中数据(所以字段)
--insert into usersInfo values(1,'Mery','123456','1446397444@qq.com',sysdate)
//给表中指定字段添加值
--insert into USERSINFO(id,USERNAME,USERPWD) values(2,'panada','a')
//为表中字段添加默认值
/* create table userInfo(
id number(6,0),
regdate date default sysdate --将系统日期作为默认日期
)
insert into userinfo(id)values(1) --必须指定列添加
select * from USERINFO
*/
//修改表中字段为其添加默认值
--alter table usersinfo modify userpwd default '000000'
--insert into usersinfo(id)values(3) --必须指定列添加
/*在建表时复制数据
语法:create table table_newname
as
select column1,column2,...|* from table_oldname
*/
--create table userinfo_new as (select * from usersinfo)
--create table userinfo_new1 as (select id,USERNAME from usersinfo)
--select * from userinfo_new
/*在添加时复制旧表数据(注:insert into table_newname【必须是存在的】)
insert into table_newname [(column1,column2,column3,...)]
select column1,column2,...|* from table_oldname
注:添加字段与查询字段必须一一对应
*/
--insert into userinfo_new select * from usersinfo
/*更新表数据语法:
update tablename set column1=value1,...[where conditions]
注:当where条件子句不存在时表示更新表中所有行指定字段数据
*/
--update usersinfo set REGDATE=sysdate --不指定条件,更新所有数据
--update usersinfo set EMAIL='2653491062@qq.com' where id=3 --有条件更新
--select * from usersinfo
/* 删除表数据语法:
delete from table_name [where conditions]
注:当无where条件删除时等同于truncate语句,只是效率相较低,因为truncate是数据库定义级操作
*/
--delete from usersinfo where id=1
/**********************************Oracle约束*********************************************/
//非空约束
/* 1、在创建表时设置非空约束
create table tablename(
column_name datatype not null,...
)
2、在修改表时创建非空约束
alter table tablename modify column_name datatype not null
注:执行此语句前必须确保表中该字段无空值
3、在修改表时去除非空约束
alter table tablename modify column_name datatype null
*/
--alter table usersinfo modify userpwd varchar2(20) not null
//主键约束
/*作用:确保表中每一行数据的唯一性,一张表只能有一个主键约束,主键约束可以由多个字段构成(复合主键或联合主键)
1、创建表时设置主键约束
第一种方法: create table tablename(
column_name datatype primary key,...
)
第二种方法:constraint constrain_name primary key(column1,column2,...) --联合主键约束设置
2、在修改表时添加主键约束
语法:alter table tablename add constraint constrain_name primary key(column_name1,...);
*/
/* create table userinfo_p1(
id number(6,0) primary key,
username varchar2(20)
) */
/* create table userinfo_p2( --联合主键约束的创建
id number(6,0),
username varchar2(20),
userpwd varchar2(20),
constraint pk_id_username primary key(id,username)
) */
--alter table userinfo add constraint pk_id primary key(id) --修改时创建主键约束
//更改约束的名称
--alter table userinfo rename constraint pk_id to pk_sid
//删除主键(外键、唯一,检查)约束
/* 不永久删除仅禁用
alter table tablename disable|enable constraint constraint_name
*/
--alter table userinfo disable constraint pk_sid --disable禁用 ,enable启用
//查询约束的状态信息【user_constraints是约束的数据字典】
--select constraint_name,status from user_constraints
/* 永久性删除主键(外键,唯一,检查适用第一种方法)约束
第一种方法:alter table tablename drop constraint constraint_name
第二种方法:alter table tablename drop primary key[cascade] --cascade用于级联删除
*/
//外键约束
/*
1、在创建表时设置外键约束(table1表示从表,table2表示主表)
第一种方法: create table table1(
column_name datatype references table2(column_name),...
);
注:设置外键约束时,主表中的字段必须是主键,主从表中相应的字段必须是相同的数据类型
从表中外键字段的值必须来自主表中相应字段的值,或者为null值
第二种方法:constraint constraint_name foreign key(column_name
references table_name(column_name)[on delete cascade]
2、在修改表时添加外键语法:(tablename从表名,tablename_column从表中的外键字段,tablePKname主表名)
alter table tablename
add constraint fk_name foreign key(tablename_column) references tablePKname(tablePKname_column)
*/
/* create table typeinfo(
typeid varchar2(10) primary key,
typename varchar2(20)
)
create table userinfo_f(
id varchar2(10)primary key,
username varchar2(20),
typeid varchar2(10) references typeinfo(typeid) ----在创建表时设置外键约束第1种方法
) */
--insert into typeinfo values(1,'it')
--select * from typeinfo
--insert into userinfo_f values(1,'a',1)
--insert into userinfo_f values(2,'a',null)
--select * from userinfo_f
/* create table userinfo_f1( --在创建表时设置外键约束第二种方法
id varchar2(10)primary key,
username varchar2(20),
typeid varchar2(10)
constraint fk_typeid foreign key(typeid)
references typeinfo(typeid)
) */
//在修改表时添加外键语法
/* alter table userinfo_f2
add constraint fk_name foreign key(typeid) references typeinfo(typeid) */
//唯一性约束
/*唯一约束和主键约束的区别:
主键字段值必须是非空的,唯一约束允许有一个空值
主键约束在一张表中只能有一个,唯一约束可不止一个
1、在创建表时设置唯一约束语法:
第一种方法(列级设置): create table tablename(
column_name datatype unique,...
)
第二种方法(表级设置):create table tablename(
column_name datatype,
constraint un_username unique(username)
)
2、在修改时添加唯一约束
alter table tablename add constraint unique_name unique(column_name)
3、删除唯一约束(同上删除主键,外键约束)
*/
/* create table userinfo_u( --列级设置唯一约束
id varchar2(10)primary key,
username varchar2(20) unique,
userpwd varchar2(20)
) */
/* create table userinfo_u1( --表级设置唯一约束
id varchar2(10)primary key,
username varchar2(20),
constraint un_name unique(username)
) */
//检查约束(使数据库数据具有实际意义)
/*
1、在创建表的时候设置检查约束(可有多个)
第一种方法(列级):create table tablename(
column_name datatype check(expressions),...
)
第二种方法(表级):create table tablename(
column_name datatype,...
constraint constraint_name check(expressions)
2、在修改表时添加检查约束
alter table tablename add constraint constraint_name check(expressions);
3、删除检查约束(同上)
*/
/* create table userinfo_c( --列级设置检查约束
id varchar2(10)primary key,
username varchar2(20) unique,
userpwd varchar2(20),
salary number(5,0) check(salary>0)
) */
/* create table userinfo_c1( --表级设置检查约束
id varchar2(10)primary key,
username varchar2(20) unique,
salary number(5,0),
constraint ck_salary check(salary>0)
) */
--insert into userinfo_c1 values(1,'a',-10) --ERROR:ORA-02290: 违反检查约束条件 (SCOTT.SYS_C0011068)
/**********************************ORACLE基本查询语句**************************************************/
//基本查询语句【distinct表示去掉重复行】
--select [distinct]column1,column2,...from table_name [where conditions]
//逻辑运算符的优先级:按not 、and 、or的顺序依次递减
//查询工资用户名为a,工资在2000到5000之间
--select * from userinfo_c where username='a' or salary>2000 and salary<=5000
//模糊查询【like 通配符%,_表示任意一个字符】
--select * from usersinfo where username like '%a%'
//范围查询
/*
between ...and...
in / not in (,,,...)相当于or
*/
//对查询结果进行排序语法:select ...from ...where ... order by column1 deac/asc(降序/升序)
/*
case ...when 语句的使用 :第一种方法:case column_name when value1 then result1,...[else result] end
第二种方法:case when column_name=value1 then result1,...[else result] end
*/
--select * from usersinfo
--update usersinfo set username='aaa' where id=3
/* select username,case username when 'panada' then '计算机部门'
when 'aaa' then '销售部门' else '其他部门' end as 部门 from usersinfo */
/* select username,case when username='panada' then '计算机部门'
when username='aaa' then '销售部门' else '其他部门' end as 部门 from usersinfo */
//decode函数的作用和case ...when ... 相同
--select id,username ,decode(username,'aaa','销售部','panada','计算机部门')as 部门 from usersinfo
//*注:Case函数只返回第一个符合条件的值,剩下的Case部分将会被自动忽略。
select id,username ,case when id in(2,3) then '第一类'
when id in(2) then '第二类' end as 类别 from usersinfo 2
/*结果显示: panada 第一类
aaa 第一类 */