共计 7513 个字符,预计需要花费 19 分钟才能阅读完成。
一、数据定义语言 DDL
1. 创建表空间:CREAT TABLESPACE lyy DATAFILE ‘C:/app/lyy.dbf’ SIZE 10M;(创建一个 10M 的表空间,存放在 C 盘 app 文件夹中)
删除表空间:DROP TABLESPACE lyy;
2. 创建用户 和赋权:CREAT USER lyy PROFILE DEFAULT IDENTIFIED BY 123456 DEFAULT TABLESPACE lyy TEMPORARY TABLESPACE temp ACCOUNT UNLOCK;(创建用户 lyy, 默认密码为 123456,默认表空间为 lyy, 临时表空间为 temp, 账号开启状态)
GRANT CONNECT TO lyy;
GRANT RESOURCE TO lyy;(赋予用户 lyy connect 和 resource 权限)
3. 创建表
语法格式:CREAT TABLE 表名(属性名 1 数据类型(长度),属性名 2 数据类型 …… 属性名 n 数据类型);
例:创建以下三个表:
– 学生表 student:学号 sid、姓名 sname、性别 ssex、年龄 sage、电话 sphone
– 课程表 course: 课程编号 cid、课程名称 cname、老师名称 tname、课时 chour
– 成绩表 score: 成绩编号 scid、学号 sid、课程编号 cid、分数 grade
在 Oracle 中,表的全名是:方案名. 表名;方案名就是用户名,方案名不写,表示方案名就是当前用户自己;
——创建学生表
CREAT TABLE student(
sid CHAR(10),
sname VARCHAR2(50) NOT NULL,
ssex CHAR(1),
sage NUMBER,
sphone INTEGER
);
——创建课程表
CREAT TABLE course(
cid CHAR(10),
cname VARCHAR2(50),
tname VARCHAR2(50),
chour NUMBER
);
——创建成绩表
CREAT TABLE score(
scid CHAR(10),
sid CHAR(10),
cid CHAR(10),
grade NUMBER
);
删除表:DROP TABLE student;
4. 约束条件
约束是由用户添加,用来保证存储到数据库的数据的实体完整性和参照完整性;
约束种类一般有五种:
– 主键约束:Primary key,要求被定义为主键的字段的值具有唯一性和非空性;
constraint p1_sid primary key(sid) – 定义 sid 为主键。
– 外键约束:Foreign key,要求被定义为外键的字段的值必须来源于所引用字段的值,外键字段和所引用的字段,名称可以不一样,但是两者的数据类型和长度必须一致;
constraint f1_sid foreign key(sid) references student(sid) – 定义 sid 为外键,值来源于 student 表的 sid
– 唯一约束:Unique,要求具有唯一性;
unique(sid,cid) – 定义 sid 和 cid 的组合有唯一性。
– 非空约束:not null,要求必须有值;
sname varchar2(50) not null – 定义 sname 字段非空
– 检查约束:check,用户可以根据业务的需要,对字段的值进行自定义限制。
constraint c1_ssex check(ssex in (‘M’,’F’)) – 定义检查约束,要求 ssex 字段的取值必须为 M 或者 F
5. 修改表 ALTER
ALTER TABLE SCOTT.TEST RENAME TO TEST1– 修改表名
ALTER TABLE SCOTT.TEST RENAME COLUMN NAME TO NAME1 – 修改表列名
ALTER TABLE SCOTT.TEST MODIFY NAME1 NUMBER(20) – 修改字段类型
ALTER TABLE SCOTT.TEST ADD ADDRESS VARCHAR2(40) – 添加表列
ALTER TABLE SCOTT.TEST DROP NAME CASCADECONSTRAINTS – 删除表列
二、数据操作语言 DML
DML 是数据操作语言,主要可以完成三个操作:insert 插入、update 更新、delete 删除;DML 语句是一种事务操作语句,需要做 commit 确认、rollback 回滚操作才能最终完成的。如果不做确认或者回滚操作,则会锁定当前的表,导致针对该表的 DDL 等操作都会失败。
1.insert 插入数据
语法格式 1:insert into 表名 values(值 1,值 2 ……,值 n);
注意事项:在 SQL 语句中,一般来说,除了值得部分(数据),其他部分都是不区分大小写的,数据部分,字符类型需要单引号,其他类型不需要单引号;该格式下,要保证值得数量、类型、长度、顺序都要和表的字段保持一致。
示例:SELECT * FROM student;
INSERT INTO student VALUES(‘S001′,’ 张三 ’,‘M’,30,13089247856);
语法格式 2:insert into 表明(字段名 1,字段名 2,……,字段名 n)values(值 1, 值 2,……,值 n);
注意事项:注定值和字段的对应关系(按顺序对应),好处是可以根据自身的值和字段的对应关系,有选择性进行插入操作。
示例: INSERT INTO student(sname,ssex,sid,sage) VALUES(‘ 李四 ’,’F’,’s0002′,20);
2.update 更新数据
语法格式:update 表名 set 赋值表达式 [where 条件]
注意事项:update 本身是一个列操作的语句,即不加条件,操作的是表中的整列数据;如果加了条件,因为条件限定是行,则表示操作的是选中的行所对应的列。
示例:SELECT * FROM student;
UPDATE student SET sage=40;
UPDATE student SET sage=(sage+sphone)-sage,sphone=(sage+sphone)-sphone;
UPDATE student SET sage=sage+1;
UPDATE student SET sage=sage+1 where ssec=’F’;
UPDATE student SET sname=replace(sname,’ 张 ’,’ 陈 ’);
3.delete 删除数据
语法格式:delete from 表 [where 条件]
注意事项:delete 是一个行操作,最小操作单位是一条记录,切记 delete 没有 * 号;如果不加 where 条件,则表示删除整个表中的所有记录。
示例:DELETE FROM student WHERE sid=’s0002′;
INSERT INTO course VALUES(‘c0001′,’oracle’,’teacher 1′,32);
INSERT INTO score VALUES(‘sc0001′,’s0001′,’c0001’,100);
SELECT * FROM student;
SELECT * FROM course;
SELECT * FROM score;
4.truncate:是 DDL 语句,但也可以实现删除整表的数据的效果
语法格式:truncate table 表名
示例:truncate table score;
三、数据查询语言 DQL
1. select 用来对数据进行查询,获取用户想要的信息。
语法格式:
select:查询的内容,是必选关键字,后面跟要查询的内容,一般以字段为主、也可以是常量、表达式(包含字段)。
–from:查询内容的来源,是必选关键字,来源可以是表格、多个表格、其他的查询语句等;
–where:条件,可选关键字,一般用来指定查询的条件,即用来过滤数据;
–group by:字段,可选关键字,用来实现分组查询;
–having:条件,可选关键字,是用来对分组之后的结果进行过滤;
–order by:字段,可选关键字,用来实现排序操作;
注意事项:不带条件的查询,即列查询,查询内容可以是表中的一个字段、多个字段、常量或者表达式。
示例:
– 查询学生表的所有内容
SELECT * FROM student;
– 查询所有学生的姓名和年龄
SELECT sname,sage FROM student;
– 查询常量
SELECT sname,sage,’teacher 1′ FROM student;
– 查询内容是表达式
SELECT sname,sage,sage+1 FROM student;
2. || 用来实现字符串、变量的拼接操作的
示例:SELECT sname,sage,sage||’ 岁 ’ FROM student;
SELECT sname,ssex FROM student;
SELECT sname,ssex,case WHEN ssex=’M’ THEN ‘ 男 ’ ELSE ‘ 女 ’ END FROM student;
3. 条件
加查询条件是用来过滤数据的,过滤的基本单位是行,常见的关键字有:(> /< /= />= /<=/ !=/ <> /between..and.. /like/ in /all /any /exists/not exists 等;多个条件的连接符有:and\or\!
示例:
– 查询年龄大于 28 岁的所有学生的信息
SELECT * FROM student WHERE sage>28;
– 查询年龄大于等于 28 岁的所有学生的信息;
SELECT * FROM student WHERE sage>=28;
– 查询所有男生信息
SELECT * FROM student WHERE ssex=’M’;
SELECT * FROM student WHERE ssex!=’F’;
SELECT * FROM student WHERE ssex<>’F’;
– 查询学号比 s0010 靠前的学生的信息。
SELECT * FROM student WHERE sid<‘s0010’;
1)between..and..
语法格式:字段 between 值 1 and 值 2 等价于 字段 >= 值 1 and 字段 <= 值 2;是一个独立、完整的字段,不可拆分。
示例:– 查询年龄在 23-28 岁之间(包含)的学生信息。
SELECT * FROM student WHERE sage>=23 AND sage<=28;
SELECT * FROM student WHERE sage BETWEEN 23 AND 28;
– 查询年龄在 23-28 岁之间(包含)的女生信息。
SELECT * FROM student WHERE sage BETWEEN 23 AND 28 AND ssex=’F’;
SELECT * FROM student WHERE sage>=23 AND ssex=’F’AND sage<=28;
2)like: 实现的是模糊查询,一般来说会结合两个特殊符号使用,% 通配符,匹配任意多个字符;_表示匹配一个字符。
示例:– 查询所有姓张的同学信息。
SELECT * FROM student WHERE sname LIKE ‘ 张 %’;
– 查询所有姓张,姓名总共为 2 个字的同学信息。
SELECT * FROM student WHERE sname LIKE ‘ 张_’;
– 查询所有姓张,姓名总共为 3 个字的同学信息。
SELECT * FROM student WHERE sname LIKE ‘ 张__’;
SELECT * FROM student WHERE sname LIKE ‘% 张 ’;
3)in:是一种枚举用法,字段 in(值 1,值 2,……,值 n)等价于 字段 = 值 1 or 字段 = 值 2 or … or 字段 = 值 n。
示例:– 查询年龄等于 27 岁或者 28 岁的学生的信息。
SELECT * FROM student WHERE sage=27 OR sage=28;
SELECT * FROM student WHERE sage IN (27,28);
4)distinct:用来修饰字段,表示唯一查询、去除重复值。
示例:– 查询所有选课了的学生的学号。
SELECT sid FROM score;
SELECT DISTINCT sid FROM score;
5)别名:可以应用在查询内容和来源中。
语法格式:原名 as 别名,一般来说,as 是省略的,原名 别名。
示例:SELECT sname,sage FROM student;
SELECT sname as 姓名,sage 年龄 FROM student;
SELECT sname,sage+1 sage FROM student;
SELECT sname sage FROM student; – 语法没问题,从应用层面是有问题的。查询学生姓名,但结果是列名改为了 sage。
6)嵌套
SQL 语句中,查询语句是可以被嵌套使用。嵌套是通过括号 () 实现。每一条查询语句的结果本身就是一个表、是一些值的集合,可以被嵌套使用在值或者来源的部分。
– 查询所有选修 oracle 课程的学生的姓名。
SELECT sname FROM student WHERE sid IN (SELECT sid FROM score WHERE cid=(SELECTcid FROM course WHERE cname=’oracle’));
– 查询某一位同学所选修的课程的名称。(已经学生姓名)
SELECT cname FROM course WHERE cid IN (SELECT cid FROM score WHERE sid in (SELECT sid FROM student WHERE sname=’ 张三 ’));
7)all: 使用 and 来对表达式进行分解
– 字段 > all(值 1,值 2,。。。,值 N) 等价于:字段 > 值 1 and 字段 > 值 2 and… and 字段 > 值 N。
– 字段 < all(值 1,值 2,。。。,值 N) 等价于:字段 < 值 1 and 字段 < 值 2 and… and 字段 < 值 N。
示例:查询比所有女生年龄都大的男生信息。
SELECT * FROM student WHERE ssex=’M’ AND sage>39;
SELECT sage FROM student WHERE ssex=’F’; –21\25\39
SELECT * FROM student WHERE ssex=’M’ AND sage>ALL(SELECT sage FROM student WHERE ssex=’F’);
– 等价于 SELECT *FROM student WHERE ssex=’M’ AND sage>21 AND sage>25 AND sage>39;
8)any: 就是用 or 来对表达式进行分解
– 字段 > any(值 1,值 2,。。。,值 N) 等价于:字段 > 值 1 or 字段 > 值 2 or … or 字段 > 值 N。
示例:– 查询比任一女生年龄都大的男生信息。
SELECT * FROM student WHERE ssex=’M’ AND sage>ANY(SELECT sage FROM student WHERE ssex=’F’);
4. 聚合函数(分组函数 / 组函数)
常见的聚合函数有:count()\avg()\min()\max()\sum(), 括号里加字段或字段表达式。
示例:– 查询男生的数量
SELECT count(*) FROM student WHERE ssex=’M’;
– 查询有成绩的学生的数量
SELECT count(distinct sid) FROM score;
– 查询女生的最小年龄
SELECT min(sage) FROM student WHERE ssex=’F’;
– 查询某一位学生的平均成绩
SELECT avg(grade) FROM score WHERE sid=’s0001′;
– 查询年龄最大的学生的姓名
SELECT* FROM student WHERE sage=(SELECT max(sage) FROM student);
5. 分组查询
语法格式:group by 分组字段
作用:表示会将表格中所有的数据按照分组字段进行分组处理、分组字段的值相同的行会被合并为一条记录、即一组;分组字段以外的字段是无法再被独立查询,只能通过聚合函数来实现查询,分组字段可以是多个,用逗号分隔。
示例:– 查询男生和女生的数量
SELECT ssex,COUNT(*) FROM student GROUP BY ssex;
– 查询男生和女生的平均年龄、最大年龄、最小年龄
SELECT ssex,AVG(sage),MAX(sage),min(sage) FROM student GROUP BY ssex;
– 查询每一门课程被选的数量
SELECT cid,COUNT(DISTINCT sid) FROM score GROUP BY cid;
注意:where 是发生在分组之前,having 是发生在分组之后,意味分组函数是不能直接作为条件的值出现在 where 之后,但是可以出现在 having 之后。
示例:– 查询所有科目都及格的学生的 sid。
SELECT sid FROM score WHERE grade>=60 GROUP BY sid; – 错误的写法, 实现的是任意科目及格的学生。
SELECT sid FROM score GROUP BY sid HAVING MIN(grade)>=60; – 先按学生分组,设定条件每个学生的最低分要大于等于 60.
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址:http://www.linuxidc.com/Linux/2016-05/130971.htm