--創(chuàng)建學(xué)生信息表
create table student(
sid number(8,0),
name varchar2(20),
sex char(2),
birthday date,
address varchar2(50)
);

--修改表結(jié)構(gòu)，為學(xué)生信息表添加主鍵約束
alter table student
add constraint sid_pk primary key(sid);

--創(chuàng)建表時(shí)添加主鍵約束
create table student(
sid number(8,0) primary key,
name varchar2(20),
sex char(2),
birthday date,
address varchar2(50)
);

create table student(
sid number(8,0),
name varchar2(20),
sex char(2),
birthday date,
address varchar2(50),
constraint sid_pk primary key(sid)
);

--創(chuàng)建學(xué)生信息表
create table student(
sid number(8,0) primary key,
name varchar2(20),
sex char(2),
birthday date,
address varchar2(50)
);

--修改表結(jié)構(gòu)，添加非空約束
ALTER TABLE student MODIFY (name varchar2(20) NOT NULL);

--創(chuàng)建表時(shí)添加非空約束
create table student(
sid number(8,0),
name varchar2(20) not null,
sex char(2) constraint nn_sex not null,
birthday date,
address varchar2(50),
constraint sid_pk primary key(sid)
);

--刪除非空約束的方式
ALTER TABLE student MODIFY sex char(2) NULL;

--禁用主鍵約束(無效化約束)
ALTER TABLE student DISABLE CONSTRAINT sid_pk;
--激活主鍵約束
ALTER TABLE student ENABLE CONSTRAINT sid_pk;

--刪除主鍵約束
ALTER TABLE student DROP CONSTRAINT sid_pk;
ALTER TABLE student DROP PRIMARY KEY;

--創(chuàng)建表時(shí)添加唯一性約束
--create table student (
-- sid number(8,0),
-- name varchar2(20),
-- sex char(2),
-- birthday date,
-- address varchar2(50),
-- email varchar2(50) unique,
-- cardid varchar2(18),
-- constraint uk_cardid unique(cardid)
--);

--創(chuàng)建學(xué)生信息表
--create table student (
-- sid number(8,0),
-- name varchar2(20),
-- sex char(2),
-- birthday date,
-- address varchar2(50),
-- email varchar2(50) unique,
-- cardid varchar2(18)
--);

--更改表結(jié)構(gòu)，添加唯一性約束
--alter table student add constraint uk_student_cardid unique(cardid);

--禁用約束
alter table student disable constraint uk_student_cardid;

--徹底刪除
alter table student drop constraint uk_student_cardid;

--create table student (
-- sid number(8,0),
-- name varchar2(20),
-- sex char(2),
-- birthday date,
-- address varchar2(50)
--);

--修改表添加檢查約束的方式
--alter table student add constraint ck_sex check(sex='男' or sex='女');

--創(chuàng)建表時(shí)設(shè)置檢查約束的方式：列級約束
--create table student (
-- sid number(8,0),
-- name varchar2(20),
-- sex char(2) check(sex='男' or sex='女'),
-- birthday date,
-- address varchar2(50)
--);

--創(chuàng)建表時(shí)設(shè)置檢查約束的方式：表級約束
--create table student (
-- sid number(8,0),
-- name varchar2(20),
-- sex char(2),
-- birthday date,
-- address varchar2(50),
-- constraint ck_sex check(sex='男' or sex='女')
--);

--禁用約束
--alter table student disable constraint ck_sex;

--徹底刪除
alter table student drop constraint ck_sex;

--主表
--create table department(
-- depid varchar2(10) primary key,
-- depname varchar2(30)
--);

--從表
--create table student (
-- sid number(8,0),
-- name varchar2(20),
-- sex char(2),
-- birthday date,
-- address varchar2(50),
-- depid varchar2(10)
--);

--修改表時(shí)添加約束
--alter table student add constraint fk_depid foreign key(depid) references department(depid) on delete cascade;

--創(chuàng)建列級約束
--create table student (
-- sid number(8,0),
-- name varchar2(20),
-- sex char(2),
-- birthday date,
-- address varchar2(50),
-- depid varchar2(10) references department(depid)
--);

--創(chuàng)建表級約束
--create table student (
-- sid number(8,0),
-- name varchar2(20),
-- sex char(2),
-- birthday date,
-- address varchar2(50),
-- depid varchar2(10),
-- constraint fk_depid foreign key(depid) references department(depid) on delete cascade
--);

--禁用約束
--alter table student disable constraint fk_depid;

--徹底刪除
alter table student drop constraint fk_depid;

修改表結(jié)構(gòu)：
--添加列
--alter table student add tel varchar2(11);
--修改列
--alter table student modify tel number(11,0);
--刪除列
--alter table student drop column tel;
--修改列名
--alter table student rename column gender to sex;
--修改表名
--rename studentinfo to student;

--刪除表
--truncate
--truncate table emp_bak;

--drop
--drop table emp_bak;

--insert（添加數(shù)據(jù)）
--insert into student (sid,name,sex)values(20010001,'張珊珊','女');
--insert into student(sid,name,sex,address,birthday) values(20010002,'王五'，'男','北京市昌平區(qū)',to_date('19820909','YYYYMMDD'));
--insert into student values(20010003,'張三','女',to_date('19830808','YYYYMMDD'),'北京市昌平區(qū)');

--select（查詢數(shù)據(jù)）
--select * from student;
--select sid,name from student;

--update（更新數(shù)據(jù)）
select * from student;
--update student set address = '北京市昌平區(qū)' where sid = 20010001;
--update student set address='北京市海淀區(qū)';

--delete（刪除數(shù)據(jù)）
--delete from student where sid = 20010001;
delete from student;

--update emp_bak set sal=6000 where empno=7369;

--演示事務(wù)的三個(gè)語句（commit,savepoint,rollback）的作用

--delete from emp_bak where empno=7369;
--commit;

update emp_bak set sal=5555 where empno=7499;
savepoint a;
update emp_bak set sal=6666 where empno=7499;
savepoint b;

select * from emp_bak;
rollback to a;
rollback;

--查看當(dāng)前用戶下的用戶信息
select * from user_users;

--當(dāng)前用戶有權(quán)訪問的所有用戶的基本信息
select * from all_users;

--訪問數(shù)據(jù)庫所有用戶的用戶信息（要求具有dba權(quán)限的用戶使用此數(shù)據(jù)字典）
select * from dba_users;

--去除重復(fù)行
--select distinct deptno, job from emp;

select * from emp;

--設(shè)置簡體中文的日期格式
alter session set nls_language = 'SIMPLIFIED CHINESE';
select empno,ename,hiredate from emp;

--設(shè)置美國英語的日期格式
alter session set nls_language = 'AMERICAN';
select empno,ename,hiredate from emp;

--設(shè)置特定格式
alter session set nls_date_format='YYYY/MM/DD';
select empno,ename,hiredate from emp;

-select * from emp;
-- 算術(shù)運(yùn)算符的演示
--select empno,ename,sal,sal*12 from emp;
--
--select empno, ename, sal*12 sal/2 from emp;
--
--select empno, ename, 200 sal*12 from emp;
--
--select empno, ename, (sal 100)*12 from emp;
--
--select empno, ename, sal, comm, sal comm from emp;

--默認(rèn)值的演示
--create table student(
-- sid number(8,0) primary key,
-- name varchar2(10),
-- sex char(2) default '男',
-- age number(2,0) default 20,
-- address varchar2(50)
--);

--insert into student(sid,name) values (20010001,'張三');
select * from student;

insert into student values(20010002,'王五',default,21,'北京市昌平區(qū)');
commit;

--列的別名
select empno "雇員編號", ename "雇員名", sal*12 "年收入" from emp;

select empno "雇員編號", ename "雇員名", sal*12 年收入 from emp;

select empno "雇員編號", ename "雇員名", sal*12 as 年收入 from emp;

--連接符
select ename||'的月工資是：'||sal||'崗位是：'||job as 雇員職位信息 from emp;

select ename||5 from emp;

--數(shù)字值的情況
select * from emp where deptno=20;

--字符值
select * from emp where job='MANAGER';

--日期值
select * from emp where hiredate = '02-4月-81';

--比較運(yùn)算
select * from emp where sal = 3000;

select * from emp where sal >= 3000;

select * from emp where sal < 3000;

select * from emp where sal <> 3000;
select * from emp where sal != 3000;

select * from emp where sal>1000 and sal<3000;

select * from emp where sal<1000 or sal>3000;

--范圍查詢between..and
select empno, ename, sal from emp where sal between 1500 and 3000;

--in
select empno,ename, job from emp where job in ('SALESMAN','MANAGER','CLERK');

--模糊查詢like
select * from emp where ename like 'J%';
select * from emp where ename like '_AR%';

select * from emp;

select * from emp where ename like 'G\_%' escape '\';

--is null
select empno, ename, sal, comm from emp where comm is not null;

--and
select empno, ename, job, deptno from emp where job='MANAGER' and deptno=10;

--or
select empno, ename, job, sal from emp where job='MANAGER' or sal>2000;

--not
select empno, ename,job from emp where job not in ('CLERK','SALESMAN','MANAGER');

--混合
select empno,ename, job, sal from emp where (sal>2000 or deptno=30) and job not in ('PRESIDENT','MANAGER');

--單列排序
select ename, comm from emp order by comm asc;

--使用列的別名排序
select empno, ename, sal*12 年收入 from emp order by 年收入 desc;

--多列排序
select empno,ename, deptno, sal from emp order by deptno asc, sal desc;