// 创建表空间
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