阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

Oracle数据库语言——结构化查询语言SQL

151次阅读
没有评论

共计 7513 个字符,预计需要花费 19 分钟才能阅读完成。

Oracle 数据库语言——结构化查询语言 SQL

一、数据定义语言 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

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计7513字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中