一、Oracle基础SQL练习
班级表：班级编号（主键、标识列）、班级名称、班主任、开班日期
老师表：老师编号（主键、标识列） 、姓名、性别（默认值‘男’）、身份证号（必须是18位数字）、联系电话
学生表：学号（主键、固定五位数字） 、班级编号、姓名、性别、出生日期、联系电话、家庭地址
成绩表：编号（主键、标识列） 、学号、科目名称、分数
1、表格创建 create table();
语法
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr],
[…]
);
schema表示对象所有者的姓名，table表示表的名称，DEFAULT表示默认值，column表示列的名称，datatype表示数据类型及长度
2、插入数据测试
3、查询数据测试
4、约束：a.非空约束（NOT NULL）
b.主键约束（PRIMARY KEY）：不可为空
c.唯一性约束（UNIQUE）：可以插入空值
注：增加约束的两种方式：
方法一：在建表语句中；
方法二：
alter table t_country add constraint PK_countrycode primary key(countrycode)；
5、删除表：DROP TABLE
6、插入数据：
a.全列插入：
b.部分列插入：
c.插入来自其他表的记录
7、查询数据
a.全列查询：
b.部分列查询：
c.条件查询：
注：为列显示不同名称
8、删除表内的数据记录(与删除表的区别)
逐行删除Delete from :
直接删除Truncate:
9、更新数据update：
10、数据类型分类：
a.文本数据类型
固定长度类型（CHAR/NCHAR)
可变长度类型（VARCHAR2/NVARCHAR2)
注：变长字符串，与CHAR类型不同，它不会使用空格填充至最大长度。VARCHAR2最多可以存储4,000字节的信息。
b.日期和时间
Date
Timestamp：时间可以是用小数表示的秒
c.数字
Number(p, s) --p是精度，指总的数字数 -s指小数位数，小数点右边的数字数
d.LOB类型(1~4GB，存储非结构化的信息，如声音剪辑、视频文件)
CLOB：存储字符数据，如非结构化的XML文档
BLOB：如图形、视频剪辑、声音文件
BFILE：在数据库之外的操作系统文件中存储二进制文件
11、ALTER TABLE命令
a.用户要添加新的列：
ALTER TABLE <table name> ADD(column definition…);
b.修改数据类型的宽度或数据类型本身：
ALTER TABLE <table name> MODIFY (column definition…);
eg. ALTER TABLE vendor_master MODIFY (venname VARCHAR2(25));
注：表的列不为空时，不能减小其长度
c.新增或删除完整性约束:
alter table t_country add constraint PK_countrycode primary key(countrycode)；
d.物理删除该列列名及其对应的数据:
ALTER TABLE table_name DROP COLUMN column_name;
12、DESC查看表结构命令:DESC table_name
13、事务处理命令
COMMIT
保存点:SAVEPOINT savepoint_id;
ROLLBACK :ROLLBACK;ROLLBACK TO SAVEPOINT save_pt;
14、创建现有表的副本 create table as....
15、外键(子表的外键参考父表的主键)
插入子表数据：必须先插入父表相关记录；
删除父表数据：必须先删除子表相关记录；
练习：加入外键，并尝试向子表插入父表没有的数据，删除父表中子表有的记录：
CREATE TABLE T_BuyerInfo
(
buyerNo int primary key auto_increment,
chnName varchar(50),
countryCode varchar(10) ,
buyerAddr varchar(300),
buildDate date,
yearSale double,
CONSTRAINT t_buyer_cc_fk FOREIGN KEY(countryCode)
REFERENCES t_country(countryCode)
);
16、 关联查询
inner join(等值连接)只返回两个表中联结字段相等的行
left join(左联接)返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接)返回包括右表中的所有记录和左表中联结字段相等的记录
13、 运算符：=，>，< ，>=，<=，，<>，!=，BETWEEN 和 NOT BETWEEN
14、 逻辑表达式
? And : 1 AND 1 =1 ; 1 AND 0 = 0; 0 AND 0 = 0;
? Or : 1 OR 1 = 1; 1OR 0 = 1; 0 OR 0 = 0;
? Not: select * from T_BuyerInfo where not yearsale>900000
15、 通配符
一个字符: ‘_’
任意长度的字符串: %
16、 查询细节
排序查询: desc(降序) asc
使用AS来命名列：
使用常量列:
限制固定行数:rownum
Between
is null
in
rowid
17、 函数
a.字符串函数：
Length(string):求字符串的长度
lower(String)：转小写
upper(String)：转大写
REPLACE(str ,from_str ,to_str )
b.日期函数
Months_between(d1,d2),: 查出两个日期之间的月份数;
Last_day(d),d表示日期:返回月末最后一天相应的日期;
sysdate:
c. 数学函数
ABS(x) 返回x的绝对值
Power(n1,n2):求n1的n2次方
Round(n1,n2):返回n1小数点为n2位的数,四舍五入法
d. 聚合函数(常用于GROUP BY从句的SELECT查询中)
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
e.转换函数
To_char(d|n,fmt):将日期或数字按指定格式转换为字符串
To_char(sysdate,’yyyy”-”mm”-”dd’)
To_char(23457,’$99,999.00’)
rr:返回指定的日期的世纪年份值
To_date(s,[fmt]):
将char或varchar数据类型转换为date数据类型
To_date('2-11-2004','month-dd-yyyy')
To_number(s):将包含数字的字符串转换为可以执行算术操作的number类型
f.其他：
select case '1' when '0' then '男' when '1' then '女' end case from dual;
select decode(gender,'0','男','1','女') from dual
18、序列：生成唯一、连续的整数，用于生成主键值
a.nextval:第一次使用nextval时,将返回该序列的初始值
b.currval:返回序列的当前值,即最后一次引用nextval时返回的值
CREATE SEQUENCE <sequencename>
INCREMENT BY n
START WITH n
[MAXVALUE n] [MINVALUE n]
[CYCLE|NOCYCLE]
[CACHE n|NOCACHE];
注：INCREMENT BY n:指定序列数字之间的整数间隔
START WITH n:指定要生成的第一个序列号
MINVALUE n:指定序列的最小值
MAXVALUE n:指定序列可以生成的最大值
CYCLE:指定序列即使达到了最大值，还应继续生成值,通常是循环从头开始产生序列值
CACHE:允许更快地生成序列号，Oracle分配序列号，并将其保存在内存中以便更快地访问
c.删除序列
语法：
DROP SEQUENCE <sequencename>;
示例
DROP SEQUENCE member_seq;
查看序列：
select * from seq;
19、视图：经过定制的表示方式，用来显示来自一个或多个表的数据，也称为“虚拟表”
CREATE [OR REPLACE] VIEW viewname[(alias,alias,…)]
AS subquery
WITH CHECK OPTION [CONSTRAINT constraintname]
WITH READ ONLY [CONSTRAINT constraintname]
20、索引（加快 SQL 语句的执行，慎重，需具体情况具体分析）
a.唯一索引:确保在定义索引的列中没有重复的值
CREATE UNIQUE INDEX 索引名 ON 表名（字段名）;
b.组合索引:在表的多个列上创建的索引,对于在 WHERE 子句中包含多个列的查询，可以提高数据访问速度
CREATE INDEX 索引名 ON 表名(列名1,列名2);
c.分区索引:索引存储在不同的分区中并对应不同的表空间，与表分区一样
CREATE INDEX indexname ON tablename(c1,c2,…) [GLOBAL/LOCAL]
PARTITION BY RANGE(c2)
PARTITION VALUES LESS THAN(v1) TABLESPACE tb1,
PARTITION VALUES LESS THAN(v2) TABLESPACE tb2;