ORACLE学习笔记——SQL基础

查询一个表是否存在：select count(*) from user_tables where table_name = 'TABLE_NAME'; 若返回1则存在，TABLE_NAME全部大写

SQL（结构化查询语言）的主要用于存储数据及查询、更新和管理关系数据库系统

Oracle数据库使用的是PL/SQL语言

SQL语言本身分为4类：

定义要在数据库存储哪些信息的数据定义语言（DDL）

包括对数据库中对象的创建、修改、删除的操作，这些对象主要有数据库、数据表、视图、索引等。

对数据库中的表进行操作的数据操纵语言（DML）

对数据库表中的数据进行增加、删除、修改的操作，并且操作时一次可以把表中数据按条件进行多条或全部的处理，为数据库的使用提供方便

对数据库中的表进行检索的数据查询语言（DQL）

既可以查询一个表也可以进行多表查询，并且可以按不同的条件来检索数据

对数据库中中对象进行权限管理的数据控制语言（DCL）

对数据库中的对象权限进行设置和取消等操作，但只有数据库的系统管理员才有权力去执行对数据库对象权限的操作。

 

Oracle 11g支持的数据类型

常用数据类型

字符型

数字型

日期类型

其他数据类型

数据定义语言（DDL）

DDL主要包括数据库对象的创建（create）、删除（delete）和修改（alter）的操作。下面是以数据表为对象进行操作的

使用create语句创建表

语法：

CREATE TABLE table_name

(column_name datatype [null|not null],

column_name datatype [null|not null],

…

[constraint]

)

语法说明：

table_name:在数据库中创建的数据表的名称，在一个数据库中不能重复

column_name：表中列名，在一个表中不能重复

datatype：该列存放数据的数据类型

[null|not null]:允许该列为空户或者不允许该列为空，默认不允许为空

[constraint]：为表中的列设置约束，主要包括主键约束、外键约束、检查约束等

 

注：语法中的中括号表示可选

SQL> create table productinfo

  2  (ProductId varchar2(10),

  3  ProductName varchar2(20),

  4  ProductPrice number(8,2),

  5  Quantity number(10),

  6  Category varchar2(10),

  7  Desperation varchar2(1000),

  8  Origin varchar(10));

 

使用Alter语句修改表

ALTER TABLE table_name

ADD colum_name | MODIFY colum_name | DROP COLUMN column_name;

在原来的表中添加remark列

修改productinfo商品信息表，删除表中的字段

对表的修改操作并不是一次只能修改一个字段，也可以同时完成对多个字段的修改。

注：不区分大小写

 

使用Drop语句删除表

DROP TABLE table_name;

约束的使用

主键约束

每个表只有一个，一个主键约束可以由数据表中多个列组成

在创建表时就创建主键约束，只需要使用primary key(字段名)即可完成。primary key(column_name)

在创建表时没有创建主键约束，可以在修改表时为表添加主键约束。添加主键约束的语法：

移除主键约束

外键约束

可以保证外键约束的数据库列与所引用的主键约束的数据列一致，在一个数据表中可以有多个。

使用外键约束创建商品信息表

外键约束是建立在两张表中的约束，需要在创建表的语句后面加上如下语句：

 

在修改数据库表时添加外键约束

移除外键约束

CHECK约束

CHECK约束是检查约束，能规定每个列能够输入的值，以保证数据的正确性。

创建表时添加CHECK约束

CONSTRAINT  constraint_name  CHECK(condition);

其中condition是检查约束的条件，检查约束的条件要建立在具体的字段中。如字段Age设置为18~50岁，可以写成age>=18 and age<=50

创建了一个顾客信息表custominfo，并设置Age列的取值范围为18~50岁。

在修改数据表时添加CHECK约束                 

在ALTER TABLE语句后面添加：

ADD  CONSTRAINT  constraint_name  CHECK(condition);

例如对顾客信息表添加性别列的检查约束，要求性别只能输入“男”或者“女”

移除CHECK约束

与移除其他约束一样，只要知道CHECK约束的名字，就可以移除CHECK约束，下面是移除顾客信息表中Gender列的检查约束chk_gender

ALTER TABLE custominfo

DROP CONSTRAINT chk_gender;

 

UNIQUE约束

唯一约束，可以设置在表中输入的字段值都是唯一的，和主键约束非常相似，不同的是唯一约束可以在一个表中有多个，而主键约束一个表中只能有一个。

 

 

创建表时添加UNIQUE约束

CONSTRAINT  constraint_name  UNIQUE(column_name);

如创建一个订单信息表，

把订单编号设置成UNIQUE约束，这样订单编号在订单信息表中的信息就不可以重复了。

在修改表时添加UNIQUE约束

如对订单信息表中的顾客编号加入UNIQUE约束

这就表明在一个表中可以添加多个UNIQUE约束

移除UNIQUE约束

NOT NULL约束

非空约束，经常会在创建表时添加非空约束以确保字段必须要输入值，该约束与之前的额约束不同，是直接在创建列时设置字段的非空约束

创建NOT NULL约束

创建一个商品管理员信息表

把LoginName和Password两个字段设置为非空约束。

修改表时设置NOT NULL约束

不需要用ADD关键字来添加约束，只要使用MODIFY关键字就可以

ALTER TABLE table_name

MODIFY column NOT NULL;

如设置管理员信息表中真实姓名列为NOT NULL

数据操纵语言（DML）和数据查询语言（DQL）

DML用来操纵数据库中数据所使用的语言，对数据库中的操纵无非就是对数据进行增加、删除、修改、查询的操作。

数据的查询也称为数据查询语言

添加数据就用INSERT

在创建好数据表之后，添加数据要与表中字段类型相匹配。

直接添加数据

直接添加数据用上面语句就可以。如向管理员信息表中添加数据

输入发现报错，是由于之前将TEL设置为最大10位，修改之后结果：

这就在管理员信息表managerinfo中就增加了一条数据。

insert into persons

(id_p, lastname , firstName, city )

values

(200,'haha' , 'deng' , 'shenzhen'),

(201,'haha2' , 'deng' , 'GD'),

(202,'haha3' , 'deng' , 'Beijing');

由此可以一次添加多条数据（mysql中，在oracle中不可用）

通过其他数据表向表中添加数据

如果在数据库中需要新创建一个数据表，但是这个表中的数据又与其他表中的数据有些相似，那么就可以直接把其他表中的数据添加到新创建的数据表中，减少添加数据的工作量。

注：在使用来源表向目标表中插入数据时，一定要确保两个表的列的个数和列的数据类型都一致，否则会出现错误。

创建一个账号信息表：

把管理员信息表中的注册名和密码添加到账号信息表中（因为managerinfo中只有一条数据，所以账号信息表中也获得了一条数据）。

上面是在目标数据表已经存在的情况下，即logininfo已经先创建好的，如果想不创建表就直接通过源数据表在添加数据的同时创建表可以通过以下语法来实现：

利用上面语句创建一个表login，数据来源表是managerinfo

SELECT语句查看在login表已经存在一条记录。

修改数据就用UPDATE

在已经存在数据的表中修改数据

修改表中指定字段的全部值

此时不需要WHERE子句的语句。

如修改新建的表login中农工loginname注册名字段，把注册名都改写成“test”。

根据条件修改表中指定字段的值

加上where子句。

如将表login中用户名是“AAA”的密码改为‘654321’。

       

注：查询时要区分大小写

删除数据就用DELETE

根据条件删除表中的记录

需用where condition。如删除login表中用户名是“AAA”的记录

Oracle中对单引号和双引号是有区分的,’AAA’和”AAA”不一样?

删除表中的全部记录

不用where

注：未选定行就代表表中已经不存在数据了。

查询数据就用SELECT

数据查询语言也称为DQL，select的基本用法

查询表中全部数据

select * from table_name;

查询表中某一字段的数据

select loginname from login;

根据条件查询数据

用到where

其他数据操纵语句

除了上述几种语句之外，还有MERGE, TRUNCATE, LOCK TABLE等语句。

TRUNCATE语句

TRUNCATE TABLE table_name;

与delete语句一样都是用来完成删除数据表中数据的，TRUNCATE语句删除表中的记录都是要把表中的记录全部删除，但是TRUNCATE语句删除表中数据的速度要比delete语句快。

MERGE语句

与update语句功能类似，都是修改数据表中数据的，但merge可以对数据表同时进行增加和修改的操作。

注：merge_update_clause和merge_insert_clause都是可以省略的，但是在操作时只能省略一个，如果两个语句都省略，那么merge语句就失去意义了。

省略增加的语句