【自用】结合SQLrunoob网站教程的代码练习，使用oracle数据库

select upper('apple') from dual;
--建表语句websites
create table websites(
       id number(5,0) not null,
       name varchar2(20) not null,
       url varchar2(255),
       alexa number(10,0),
       country varchar2(10)
);
--插值语句websites
insert into websites
select '1','Google','https://www.google.cm/','1','USA' from dual
union all
select '2','淘宝','https://www.taobao.com/','13','CN' from dual
union all
select '3','菜鸟教程','http://www.runoob.com/','5000','USA' from dual
union all
select '4','微博','http://weibo.com/','20','CN' from dual
union all
select '5','Facebook','https://www.facebook.com/','3','USA' from dual
union all
select '6','stackoverflow','http://stackoverflow.com/','0','IND'from dual;
--rownum返回表wensites前两行
select * from websites where rownum <= 2;
select * from websites where name in ('Google','菜鸟教程');
select * from websites where id between 1 and 4;
select * from websites where (alexa between 1 and 20) and country not in ('USA','IND'); 

--建表语句access_log
create table access_log(
       aid number(5,0),
       site_id number(5,0),
       count number(5,0),
       adate varchar2(20)
);
drop table access_log;
select * from access_log;
--插值语句access_log
insert into access_log
select '1','1','45','2016-05-10' from dual 
union all
select '2','3','100','2016-05-13' from dual 
union all
select '3','1','230','2016-05-14' from dual 
union all
select '4','2','10','2016-05-14' from dual 
union all
select '5','5','205','2016-05-14' from dual 
union all
select '6','4','13','2016-05-15' from dual 
union all
select '7','3','220','2016-05-15' from dual 
union all
select '8','5','545','2016-05-16' from dual 
union all
select '9','3','201','2016-05-17' from dual;
--inner join练习
select websites.id, websites.name, access_log.count, access_log.adate
from websites
inner join access_log
on websites.id = access_log.site_id

--建表语句apps
create table apps(
       id number(5),
       app_name varchar2(20),
       url varchar2(50),
       country varchar2(5)
);
-- 删除表结构和表数据：drop table apps;
-- 插值语句apps
insert into apps
select '1','QQ APP','http://im.qq.com/','CN' from dual
union all
select '2','微博 APP','http://weibo.com/','CN' from dual
union all
select '3','淘宝 APP','https://www.taobao.com/','CN' from dual;

select country from websites
union 
select country from apps
order by country;

select country from websites
union all
select country from apps
order by country;


--直接复制创建表  使用create table as 语句 将apps表中的数据 复制 到apps_copy1表中
CREATE TABLE apps_copy1 AS SELECT * FROM apps;
--创建apps的第二个副本表 apps_copy2
create table apps_copy2(
       id number(5),
       app_name varchar2(20),
       url varchar2(50),
       country varchar2(5)
);
-- 先创建表再复制 使用inssert into select 语句 将apps表中的数据  复制 到apps_copy2表中
insert into apps_copy2 select * from apps;

--复制apps表中的数据 插入 到websites表中
INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps;
select * from websites
--↓↓↓↓↓解决上述问题 failed
--创建websites副本表websites_copy，不设置非空约束
create table websites_copy(
       id number(5,0),
       name varchar2(20),
       url varchar2(255),
       alexa number(10,0),
       country varchar2(10)
);
--插值语句websites_copy
insert into websites_copy
select '1','Google','https://www.google.cm/','1','USA' from dual
union all
select '2','淘宝','https://www.taobao.com/','13','CN' from dual
union all
select '3','菜鸟教程','http://www.runoob.com/','5000','USA' from dual
union all
select '4','微博','http://weibo.com/','20','CN' from dual
union all
select '5','Facebook','https://www.facebook.com/','3','USA' from dual
union all
select '6','stackoverflow','http://stackoverflow.com/','0','IND'from dual;
--select * from websites_copy;
INSERT INTO Websites_copy (name, country)
SELECT app_name, country FROM apps;
--↑↑↑↑↑解决成功：报错原因：websites表的id、name设置了not null约束，websites_copy表不设置not null约束，即可插值成功

--select * from websites;
--返回websites表前50%的记录
select * from websites where rownum <=(select 0.5*count(*) from websites);


--not null约束练习
create table person(
       id int not null,
       lastname varchar2(20) not null,
       firstname varchar2(20) not null,
       age int
);

alter table person modify age int not null;

alter table person modify age int null;
--unique约束练习
create table persons(
       p_id int not null unique,
       lastname varchar2(20) not null,
       firstname varchar2(20),
       address varchar2(20),
       city varchar2(20)
);
alter table persons drop unique (p_id);

--primary key   foreign key
--check 约束练习
-- drop table persons;
--oracle中可以只用check，但无法drop；
  ---所以后续如果需要drop的话，创建表添加约束使用：constraint XXX check ()
create table persons(
       p_id int not null unique,
       lastname varchar2(20) not null,
       firstname varchar2(20),
       address varchar2(20),
       city varchar2(20),
       constraint chk_person check (p_id >0 and city='Sandnes')
);
alter table persons drop constraint chk_person;


--default约束练习 failed
select * from persons;
alter table persons modify city default 'SANDNES';
insert into persons values('1','2','3','4','NUY');
insert into persons values('5','6','7','8',default);

alter table persons modify city null;

--index索引练习
create index index_name on table_name (column_name)

--alter练习
select * from persons;

添加列

alter table persons add weather varchar2(10);

删除列
alter table persons drop column weather;

改变表中列的数据类型
alter table persons modify address varchar2(55);

在persons表中添加一个名为“DateOfBirth”的列
alter table persons add DateOfBirth date;

删除DateOfBirth列
alter table  persons drop column DateOfBirth;

--auto_increment练习
drop table persons;

创建persons表，并给ID列定义为auto-increment主键字段
CREATE TABLE Persons
(
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
);

create sequence seq_person
minvalue 1
start with 1
increment by 1
cache 10

insert into persons(id, firstname, lastname)
values(seq_person.nextval, 'Lars','Monsen');


insert into persons(id, firstname, lastname)
values(seq_person.nextval, 'Lars1','Monsen1');

insert into persons(id, firstname, lastname)
values(seq_person.nextval, 'Lars2','Monsen2');

select * from persons;

--视图练习
create or replace view v_persons as 
select * from persons;

select * from v_persons;

--日期练习
select sysdate from dual;

--null函数练习
建表语句products
create table products(
       p_id int primary key,
       productname varchar2(20),
       unitprice number(3,2),
       unitinstock int,
       unitsonorder int
);

插值语句products
insert into products
select '1','Jarlsberg','10.45','16','15' from dual
union all
select '2','Mascarpone','32.56','23',null from dual
union all
select '3','Gorgonzola','15.67','9','20' from dual;

修改表unitprice 列unitprice 数据类型
alter table products modify  unitprice number(10,2)

select * from products;

select productname, unitprice*(unitsinstock+nvl(unitsonorder,0))
from products;

alter table products rename column unitinstock to unitsinstock;

select productname, unitprice*(unitsinstock+unitsonorder)
from products;

select unitsinstock+unitsonorder from products;--unitsonorder是null，不是0，不可以相加减