// 创建表空间

CREATE TABLESPACE hy DATAFILE 'hy.dbf' SIZE 10m;

// 创建用户

CREATE user hy identified by "123" default tablespace hy temporary tablespace TEMP profile DEFAULT;

// 更改密码

alter user hy identified by "123456"

// 给用户加上DBA权限

grant dba to hy with admin option;

// 建表

CREATE TABLE Employee

( ID VARCHAR(255) NOT NULL primary key,

　　NAME VARCHAR(20),

　　AGE Integer,

　　SEX CHAR(1)

)

// 插入数据

insert into Employee values('0001','Andy',20,'M');

// 查询数据

select * from Employee where age>22

// 更新数据

UPDATE Employee SET name = 'Felex' WHERE age = 25

// 删除数据

delete from Employee where age<21

Select语句基本结构

Select [Distinct] {column1,column2,…}

From tablename

Where {conditions}

Group by {conditions}

Order by {expressions} [ASC/DESC]

//求行总和函数SUM

select sum(AMOUNT) from CHECKTABLE

//求平均值函数AVG

select round(avg(AMOUNT)) from CHECKTABLE

//求最大值函数Max

select max(AMOUNT) from CHECKTABLE

//求最小值函数Min

select min(AMOUNT) from CHECKTABLE

//分组求和

Select PAYEE,sum(amount) from CHECKTABLE Group by payee

//使用Having子句进行分组过滤

select PAYEE,

avg(AMOUNT)

from CHECKTABLE

group by PAYEE

having avg(AMOUNT)>200

//左外连接

select n.id, n.name, t.name

from nation n, tank t

where n.id=t.nid(+);

//右外连接

select n.id, n.name, t.name

from nation n, tank t

where n.id(+)=t.nid;

//左连接

select *

from nation n left join tank t

on n.id = t.id;

//右连接

select *

from nation n right join tank t

on n.id = t.id;

//内连接

select *

from nation n inner join tank t

on n.id=t.nid

//使用union得到并集

select NAME, SCORE from CALSS07

union

select * from CALSS05

//使用minus得到差集

select NAME, SCORE from CALSS35

minus

select NAME, SCORE from CALSS07

//使用intersact得到交集

select NAME, SCORE from CALSS35

intersect

select NAME, SCORE from CALSS07

//从一个表向另外的表中复制记录

insert into calss05(id,sc) select name,score from calss07

//找出学生‘牛顿’选择的所有科目

select

t01.id,

t01.name,

subject.name

from

subject,

(

select

student.id,

student.name,

connector.subjectid as sid

from

student,connector

where

student.name='牛顿' and

student.id=connector.studentid

) t01

where

subject.id=t01.sid

//Case表达式

select name,score,

case when score>90 then 'A'

when score>80 then 'B'

when score>70 then 'C'

when score>60 then 'D'

else 'E'

end as grade

from singerscore

//对结果集进行转置

select sum(case when score>=60 then 1 else 0 end) as passed,

sum(case when score<60 then 1 else 0 end) as failed

from singerscore