oracle数据库sql基础

一 用户和表空间

• 使用system用户登陆:

  [username/password] [@server] [as sysdba|sysoper]

  例如：

  system/1111 @orcl as sysdba
  
  sys/111
  

  注意：orcl就是自己设置的服务名，若不在本机上，也可以用ip地址。

• 查看用户

  1.show user命令

  注意：在SQL PLUS中输入命令时不用";"结尾，但是若是SQL语句时则必须用";"结尾来表示语句结束，若无分号则sql语句是不执行的。
  

  2.通过数据字典查看其他用户

  dba_users数据字典
  

  3.通过desc查看数据字典dva_users中包含哪些字段。

  desc dba_users;
  

  4.再根据数据字典查询用户

  select username from  dba_users;
  

• 启用scott用户

  启用用户的语句：

  alter user username account unlock;锁定把"unlock"换为"lock"
  

  例如：

  alter user scott account unlock;
  

• 查看用户的表空间

  1.数据字典dba_tablespaces（系统管理员级别的）,user_tablespaces（普通用户级别）

  dba_tablespaces（系统管理员级别的）

  查看对应的数据字典中的字段后，再查看tablespace_name字段

  desc dba_tablespaces;

  select tablespace_name from dba_tablespaces;

  作为系统管理员级登陆时的默认表空间为：

tablespace_name	作用
system	永久性表空间，用于存放sys用户的表，视图以及存储过程的的数据库对象，也称系统表空间
sysaux	作为example的一个辅助表空间
undotbs1	主要存放一些撤销信息的，属于undo类型的表空间
temp	存储sql语句处理表，索引信息的，是个临时表空间
users	永久性表空间，存储数据库用户创建的数据库对象
example	用于安装oracle 11 g示例的一个表空间

user_tablespaces（普通用户级别）

查看对应的数据字典中的字段后，再查看tablespace_name字段

desc user_tablespaces;

select tablespace_name from user_tablespaces;

注意：若是用普通用户登陆，如scott用户，进行查询时，只能用："select tablespace_name from user_tablespaces;"而不能用"select tablespace_name from dba_tablespaces;"

2.数据字典dba_users（系统管理员级），user_users（普通用户级）

dba_users（系统管理员级别的）

查看对应的数据字典中的字段后，再查看default_tablespace,temporary_tablesapce字段

select default_tablespace,temporary_tablespace from dba_users where username='SYSTEM';

• 设置用户默认或临时表空间

  设置语句：
  
  alter user username default|temporary tablespace tablespace_name;
  
  alter user system default tablespace users;
  
  select default_tablespace,temporary_tablespace from dba_users where username='SYSTEM';
  

  • 创建表空间（永久表空间和临时表空间）

    设置语句

    CREATE [TEMPORARY] TABLESPACE tablespace_name TEMPFILE|DATAFILE ‘xx.dbf’ SIZE xx;

    若是临时表空间的话需在CREATE后加上[TEMPORARY]关键字,并将DATAFILE换为TEMPFILE，若不指定存放路径则会自动存放在oracle的安装目录下。

    如：

    CREATE TABLESPACE test1_tablespace DATAFILE ‘testfile.dbf’ size 10m;

    create temporary tablespace temptest1_tablespace tempfile ‘tempfile1.dbf’ size 10m;

  • 查看所创建的表空间的路径

    通过永久表空间的数据字典dba_data_files来查看对应的数据字典，进行查找。

    desc dba_data_files;

    利用file_name字段查看

    select file_name from dba_data_files where tablespace_name=’TEST1_TABLESPACE’;

    通过临时表空间的数据字典dba_temp_files来查看对应的数据字典，进行查找。

    desc dba_temp_files;

    利用file_name字段查看

    select file_name from dba_temp_files where tablespace_name=’TEMPTEST1_TABLESPACE’;

    注意：上面的两种查询，所填写的表空间名字均应大写：”TEMPTEST1_TABLESPACE”和”TEST1_TABLESPACE”。

  • 修改表空间（永久表空间）

  修该表空间的状态

  1.设置联机或脱机状态

  ALTER TABLESPACE tablespace_name ONLINE|OFFLINE;
  

  注：当我们创建一个表空间后默认的是联机状态

  例如：

  alter tablespace test1_tablespace offline;
  

  2.如何知道某一个表空间处于什么状态呢

  查询数据字典dba_tablespace

  desc dba_tablespaces;
  

  查询这个数据字典中的STATUS字段。

  select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';
  

  注意：大写TEST1_TABLESPACE

  3.设置只读或可读写状态，在此之前必须是联机状态能设置。

  ALTER TABLESPACE tablespace_name READ ONLY|READ WRITE;
  

  例如：

  alter tablespace test1_tablespace read only;
  
  alter tablespace test1_tablespace read write;
  

  默认的联机状态ONLINE为read write状态。

  向表空间中增加数据文件

  ALTER TABALESPACE tablespace_name ADD DATAFILE ‘xx.dbf’ SIZE xx;

  创建时可以直接写表空间的名字，还可以把表空间名字部分直接写为所在路径。

  例如：

  alter tablespace test1_tablespace add datafile 'test2_file.dbf' size 10m;
  

  若此时再执行下面的语句，应该能够看到两个数据文件：test1_file.dbf和test2_file.dbf。

  select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';
  

  向表空间中删除数据文件

  ALTER TABLESPACE tablespace_name DROP DATAFILE ‘xx.dbf’;

  在删除表空数据文件时，我们不能删除第一个数据文件，即我们创建表空间时的第一个数据文件。如果要删除，则应删除整个表空间

  • 删除表空间

    DROP TABLESPACE tablespace_name [INCLUDING CONTENTS];

    如果在删除表空间的同时还要删掉表空中的数据文件，则需要加上后面的[INCLUDING CONTENTS]才可以删除。

    例如：

    drop tablespace test1_tablespace including contents;

    删除后再执行”select file_name from dba_data_files where tablespace_name=’TEST1_TABLESPACE’;”会出现“未选定行”；表示表空间TEST1_TABLESPACE已经被删除。

二 管理表

• 数据类型
  字符型、数值型、日期型、其它类型（存放大数据对象）。

  字符型：

  CHAR(n)和NCHAR(n)；CHAR(n)和NCHAR(n)为固定长度，CHAR(n)的最大长度为2000，NCHAR(n)最大长度为1000；NCHAR(n)一般较多用于存储汉字。

  VARCHAR2(n)和 NVARCHAR2(n)都是支持unicode编码格式的字符类型。均为变长度，VARCHAR2(n)最大长度为4000，NVARCHAR2(n)最大长度为2000；

  数值型：

  NUMBER(p,s);”p”代表有效数字，”s”小数点后的位数

  例如：NUMBER(5,2)

  有效数字5位，保留2位小数，如123.45

  number(p,s)

  p:1~38

  s:-84~127 [@more@]

  p>0，对s分2种情况分析：

  1. s>0

     精确到小数点右边s位，并四舍五入。然后检验有效数位是否<=p；如果s>p，小数点右边至少有s-p个0填充。

  2. s<0

     精确到小数点左边s位，并四舍五入。然后检验有效数位是否<=p+|s|

     (有效数位：从左边第一个不为0的数算起)

     FLOAT(n);主要用于存储二进制数据（1-126），对二进制数乘以0.30103可以得到十进制数。

     在oracle中经常用NUMBER类型来存储数值。

     日期型：

     DATE类型；表示范围：公元前4712年1月1日到公元前9999年12月31日。可直接精确到秒。

     TIMESTAMP;时间戳类型，能够精确到小数秒，精度更高。一般用的多的是DATE类型。

     其它类型：

     用于存放大对象的类型：

     BLOB和CLOB类型；BLOB类型可存放4G的二进制数据，CLOB类型可存放4G的字符串数据。

• 创建表

  CREATE TABLE table_name (
  column_name datatype,…
  );

  其中table_name在同一个登录用户中必须保持唯一性；

  例如：创建用户信息表

  编号 用户名 密码 邮箱 注册时间

  create table userinfo(
  id number(6,0),
  username varchar2(20),
  userpwd varchar2(20),
  email varchar2(30),
  regdate date
  );

• 修改表

  1 添加字段

  ALTER TABLE table_name
  ADD column_name datatype;

  例如：

  alter table userinfo
  add remarks varchar2(500);

  2 更改字段数据类型

  ALTER TABLE table_name
  MODIFY column_name datatype;

  例如：

  1.修改长度400

  alter table userinfo
  modify remarks varchar2(400);

  2.修改密码类型

  alter table userinfo
  modify userpwd number(6,0);

  3 删除字段

  ALTER TABLE table_name
  DROP COLUMN column_name;

  例如：

  alter table userinfo
  drop column remarks;

  4 修改字段名

  ALTER TABLE table_name
  RENAME COLUMN column_name TO
  new_column_name;

  例如：

  alter table userinfo
  rename column email to
  new_email;

  5 修改表名

  RENAME table_name TO new_table_name;

  例如：

  rename userinfo to new_userinfo;

• 删除表

  TRUNCATE TABLE table_name;

  这种方式是删除表中的全部数据，并不是将表删除掉，表还存在，只是所有的数据没有了，也叫做截断表，这种删除表中全部数据的形式要比delete的速度快很多。

  例如：

  truncate table new_userinfo;

  DROP TABLE table_name;

  这种方式是删除表结构，即同时数据也删除。

  例如：

  drop table new_userinfo;

三 操作表中数据

• 添加数据

  向表中所有字段添加值：

  INSERT INTO table_name
  (column1,column2,…)
  VALUES(value1,value2,…)

  表中的字段个数要和字段值的个数一一对应，且顺序要一致。若要向表中添加全部字段，则字段项可省略，即VALUES前的括号可省略。

  例如：

  insert into userinfo
  values(1,’xxx’,’123’,’xxx@126.com’,sysdate);

  向表中指定字段添加值：

  insert into userinfo(id,username,userpwd)
  values(2,’yyy’,’123’);

  向表中添加默认值：

  create table userinfo1
  (id number(6,0),
  regdate date default sysdate);

  必须把字段对应，不然会出错。

  insert into userinfo1(id)
  values(1);

  修改表中字段为有默认值：

  alter table userinfo
  modify email default ‘无’;

  insert into userinfo(id)
  values(3);

  insert into userinfo(id,email)
  values(4,’aaa’);

• 复制表中数据

  在建表时复制：

  前提是table_new是不存在的；

  CREATE TABLE table_new
  as
  SELECT column1,…|* FROM table_old;

  例如：

  create table userinfo_new
  as
  select * from userinfo;（复制的为全部数据）

  create table userinfo_new1
  as
  select id,username from userinfo;（复制的为选中字段）

  在添加数据时复制：

  前提是table_new已经存在，且insert into 后的字段的类型及顺序必须和select后的一致，否则不成功。

  INSERT INTO table_new
  [(column1,…)]
  SELECT column1,…|* FROM table_old;

  例如：

  insert into userinfo_new
  select * from userinfo;（复制插入了userinfo表中的所有字段）

  insert into userinfo_new(id,username)
  select id,username from userinfo;（复制插入了两个字段，字段名可不同，但是数据类型则必须相同）

• 修改数据

  UPDATE语句：

  UPDATE table_name
  SET column=value1,…
  [WHERE conditions]

  若不加where子句的话，update修改的是表中全部的数据，也就是所有行的数据都会被修改。

  例如：

  update userinfo
  set userpwd=’111111’;

  这个是更新的全部数据，即每一行都进行了更新。无条件的更新。

  update userinfo
  set userpwd=’111’,email=’111@126.com’；

  有条件的更新：

  update userinfo
  set userpwd=’123456’
  where username=’xxx’;where后面的条件不止可以加一个，也可以加多个。在查询语句部分讲解。

• 删除数据

  oracle中删除数据是按行进行删除的，不能按列进行删除。

  DELETE语句：

  DELETE FROM table_name [WHERE conditions];

  若不加条件则表示删除表中所有的数据，同truncate table table_name;效果相同，但相比truncate table截断表，效率低，速度慢。

  例如：

  无条件删除：

  create table testdel
  as
  select * from userinfo;

  有条件的删除：

  delete from testdel
  where username=’yyy’;where后面的条件可以是多个，在查询语句部分讲解。

四 约束

• 约束的作用

  1.定义规则：哪些字段是必须要输入的，哪些字段输入什么样的值，在某一个范围内的值等，这些都是可以通过约束来进行设置的。

  2.确保数据的完整性：指数据的精确性和可靠性，这样就能够防止一些错误的信息或无效的信息输入。因此约束在表中是非常重要的。

• oracle中五个重要的约束

  1.非空约束

  1.1 在创建表时为字段设置非空约束

  CREATE TABLE table_name(
    column_name datatype NOT NULL,...
    );
  
  大写的NOT NULL表示的就是非空约束。默认情况下的字段均是允许为空的。
  
  例如：
  
  create table userinfo_1
  (id number(6,0),
  username varchar2(20) not null,
  userpwd varchar2(20) not null);
  
  insert into userinfo_1(id) values(1);
  
  会出现ORA-01400: 无法将 NULL 插入 ("SYSTEM"."USERINFO_1"."USERNAME")
  

  1.2 在修改表时为字段添加非空约束

  ALTER TABLE table_name
  MODIFY column_name datatype NOT NULL;
  
  例如：
  
  alter table userinfo
  modify username varchar2(20) not null;
  
  会出现：ORA-02296: 无法启用 (SYSTEM.) - 找到空值。原因是在userinfo表中的username字段对应列的数据有空值，即没有数据。可删除全部数据：delete from userinfo;再在修改时添加非空约束
  
  alter table userinfo
  modify username varchar2(20) not null;
  
  可查看表字段：desc userinfo;
  

  1.3 在修改表时为字段去除非空约束

  ALTER TABLE table_name
  MODIFY column_name datatype NULL;
  
  例如：
  
  alter table userinfo
  modify username varchar2(20) null;
  

  2.主键约束

  1.1 作用：确保每一行数据的唯一性；设置主键约束的字段必须是非空且唯一的，一张表中只能设置一个主键约束，主键约束可有多个字段构成（联合主键或复合主键）

  1.2 在创建表时设置主键约束

  方法一：

  CREATE TABLE table_name(
  column_name datatype PRIMARY KEY,…
  )

  例如：

  create table userinfo_p
  (id number(6,0) primary key,
  username varchar2(20),
  userpwd varchar2(20));

  方法二：

  CREATE TABLE table_name
  (id number(6,0),
  username varchar2(20),
  userpwd varchar2(20)，
  CONSTRAINT constraint_name
  PRIMARY KEY(column_name,…));这个是在创建表结束时添加的

  create table userinfo_p1
  (id number(6,0),
  username varchar2(20),
  userpwd varchar2(20),
  constraint pk_id_name primary key(id,username));这里constraint_name是可以任意取名的。

  创建完一个约束后，加入假如忘记了约束的名字，那么应该怎么查找呢？
  可在数据字典user_constraints中查找：

  desc user_constraints;

  根据数据字典中的字段来查找。

  select constraint_name from user_constraints where table_name=’USERINFO_P1’;

  这样就可以找到约束名了，另外在方法一中，并没有设置主键名字，但实际上系统会自动为其设置主键名字，可以查看一下：

  select constraint_name from user_constraints where table_name=’USERINFO_P’;

  1.3 在修改表时添加主键约束

  ALTER TABLE table_name
  ADD CONSTRAINT constraint_name
  PRIMARY KEY(column_name,…);

  例如：

  alter table userinfo
  add constraint pk_id primary key(id);若id已经存在值，则值必须是唯一非空，最好是id字段没有值。

  查询主键信息：select constraint_name from user_constraints where table_name=’USERINFO’;可先通过查询数据字典user_constraints的结构信息来查询要查询的字段。

  1.4 更改约束名称

  ALTER TABLE table_name
  RENAME CONSTRAINT old_name
  TO new_name;

  例如：

  alter table userinfo rename constraint pk_id to new_pk_id;

  1.5 删除主键约束

  情况一：暂时不想用

  ALTER TABLE table_name
  DISABLE|ENABLE CONSTRAINT constraint_name

  例如：

  alter table userinfo disable constraint pk_id;

  可通过数据字典user_constraints来查看约束的状态：select constraint_name,status from user_constraints where table_name=’USERINFO’;

  情况二：删除约束

  ALTER TABLE table_name
  DROP CONSTRAINT constraint_name;

  例如：alter table userinfo drop constraint new_pk_id;

  此时，再执行select constraint_name,status from user_constraints where table_name=’USERINFO’;则会提示未选定行，表示没有主键了。

  情况三：通过关键字直接删除

  ALTER TABLE table_name
  DROP PRIMARY KEY[CASCADE]

  这里CASCADE表示级联删除，即有外键约束时用。

  例如： alter table userinfo_p drop primary key;

  3.外键约束

  3.1 在创建表时设置外键约束

  在列级设置外键约束的方法：

  CREATE TABLE table1
  (column_name datatype REFERENCES
  table2(column_name),…);

  其中table2是主表，table1是从表，外键约束也叫主从表。

  注意：1.设置外键约束时，主表的字段必须是主键；

  2.主从表中相应的字段必须是同一个数据类型。

  3.从表中外键字段的值必须来自主表中的相应字段的值，或者为空值。其它值是不允许的。

  例如：

  创建主表(用户类型表)：create table typeinfo
  (typeid varchar2(10) primary key,
  username varchar2(20));

  创建从表：create table userinfo_f
  (id varchar2(10) primary key,
  username varchar2(20),
  typeid_new varchar2(10) references typeinfo(typeid));

  输入数据：insert into typeinfo values(1,1);

  insert into userinfo_f(id,typeid_new) values(1,2);

  这时候会弹出错误信息：ORA-02291: 违反完整约束条件 (SYSTEM.SYS_C0011369) - 未找到父项关键字。这是因为向从表中插入数据typeid_new字段和主表中的typeid值不相同。

  insert into userinfo_f(id,typeid_new) values(1,1);则可以正确运行。

  insert into userinfo_f(id,typeid_new) values(2,null);也可以创建。

  在表级设置外键约束的方法：

  放在创建表的后面

  CONSTRAINT constraint_name FOREIGGN
  KEY(column_name) REFERENCES
  table_name(column_name) [ON DELETE CASCADE]

  [ON DELETE CASCADE]表示级联删除，意思就是若是在主表中一条数据被删除，那么在从表中使用了这条数据的字段所在的行也会被删除掉。确保主从表数据的完整性。

  例如：

  创建一个新的用户信息表userinfo_f2

  create table userinfo_f2
  (id varchar2(10) primary key,
  username varchar2(20),
  typeid_new varchar2(10),
  constraint fk_typeid_new foreign key(typeid_new) references typeinfo(typeid));

  此时创建的表，并未设置级联删除，也就是当我们的主表typeinfo删除一条数据时，从表userinfo_f2中引用了相同值的地方并不会被删掉。

  create table userinfo_f3
  (id varchar2(10) primary key,
  username varchar2(20),
  typeid_new varchar2(10),
  constraint fk_typeid_new1 foreign key(typeid_new) references typeinfo(typeid) on delete cascade);

  这样就是加上级联删除的新表userinfo_f3

  3.2 在修改表时添加外键约束

  放在修改表语句的后面：

  ALTER TABLE table_name
  ADD CONSTRAINT constraint_name FOREIGN
  KEY(column_name) REFERENCES
  table_name(column_name) [ON DELETE CASCADE];

  例如：

  创建新的用户信息表:userinfo_f4

  create table userinfo_f4
  (id varchar2(10) primary key,
  username varchar2(20),
  typeid_new varchar2(10));

  修改userinfo_f4

  alter table userinfo_f4
  add constraint fk_typeid_alter foreign key(typeid_new) references typeinfo(typeid);

  3.3 删除外键约束

  3.3.1 禁用外键约束，以后还能恢复使用

  ALTER TABLE table_name
  DISABLE|ENABLE CONSTRAINT constraint_name;

  例如：

  先查看一下约束信息：
  select constraint_name,constraint_type,status from user_constraints
  where table_name=’USERINFO_F4’;

  CONSTRAINT_NAME C STATUS

  ---

  SYS_C0011374 P ENABLED
  FK_TYPEID_ALTER R ENABLED

  ‘P’代表的是主键约束，而’R’代表的则是外键约束。

  禁用：alter table userinfo_f4
  disable constraint FK_TYPEID_ALTER;

  3.3.2 彻底删除外键约束

  ALTER TABLE table_name
  DROP CONSTRAINT constraint_name;

  例如：

  先查看下约束：

  select constraint_name,constraint_type,status from user_constraints
  where table_name=’USERINFO_F4’;

  删除：alter table USERINFO_F4
  drop constraint FK_TYPEID_ALTER;

  查询结果：

  CONSTRAINT_NAME C STATUS

  ---

  SYS_C0011374 P ENABLED

  4.唯一约束

  作用：保证字段值的唯一性

  唯一约束和主键约束的区别：主键字段值必须是非空的，唯一约束允许有一个空值；主键在每张表中只能有一个，唯一约束在每张表中可以有多个；

  4.1 在创建表时设置唯一约束

  列级唯一约束：

  CREATE TABLE table_name
  (column_name datatype UNIQUE,…);关键字UNIQUE，需要哪个关键字是唯一约束就在哪个字段后面加上关键字UNIQUE。

  例如：

  create table userinfo_u
  (id varchar2(10) primary key,
  username varchar2(20) unique,
  userpwd varchar2(20));

  表级唯一约束：

  CONSTRAINT constraint_name
  UNIQUE(column_name);若是要设置多个字段为唯一约束，则需要写多个子句。

  例如：

  CREATE TABLE table_name
  (id varchar2(10) primary key,
  username varchar2(20),
  constraint un_username unique(username));

  4.2 在修改表时添加唯一约束

  ADD CONSTRAINT constraint_name
  UNIQUE(column_name);

  例如：

  create table userinfo_u2
  (id varchar2(10) primary key,
  username varchar2(20));

  alter table userinfo_u2
  add constraint un_username_new unique(username);

  4.3 删除唯一约束

  4.3.1 禁用唯一约束

  DISABLE|ENABLE CONSTRAINT constraint_name;

  例如：

  查看约束信息：select constraint_name,constraint_type,status from user_constraints
  where table_name=’USERINFO_U2’;

  alter table userinfo_u2
  disable constraint UN_USERNAME_NEW;

  4.3.2 删除唯一约束

  DROP CONSTRAINT constraint_name;这个子句是加在alter table table_name后面的

  例如：alter table userinfo_u2
  drop constraint UN_USERNAME_NEW;

  5.检查约束

  作用：表中的值更具有实际意义

  如：员工个人信息：年龄，工资，电话号码等等信息，若年龄=1000，工资=-50，这些值就是无意义的。

  5.1 在创建表时设置检查约束

  5.1.1 在列级设置检查约束

  CREATE TABLE table_name
  (column_name datatype CHECK(expressions),
  …);CHECK后面是检查的条件，例如工资>0;

  例如：create table userinfo_c
  (id varchar2(10) primary key,
  username varchar2(20),
  salary number(5,0) check(salary>0));

  插入员工信息，使工资小于0，看看出现什么：

  insert into userinfo_c values(1,’aaa’,-50);

  第 1 行出现错误:
  ORA-02290: 违反检查约束条件 (SYSTEM.SYS_C0011397)

  5.1.2 在表级设置检查约束

  CONSTRAINT constraint_name CHECK(expressions)

  例如：

  create table userinfo_c1
  (id varchar2(10) primary key,
  username varchar2(20),salary number(5,0),
  constraint ck_salary check(salary>0));

  5.2 在修改表时添加检查约束

  ALTER TABLE table_name
  ADD CONSTRAINT constraint_name
  CHECK(expressions);

  例如：

  create table userinfo_c3
  (id varchar2(10) primary key,
  username varchar2(20),
  salary number(5,0));

  alter table userinfo_c3
  add constraint ck_salary_new check(salary>0);

  5.3 删除检查约束

  5.3.1 禁用检查约束

  DISABLE|ENABLE CONSTRAINT constraint_name;

  例如：
  desc userinfo_c3;

  select constraint_name,constraint_type,status from user_constraints
  where table_name=’USERINFO_C3’;

  alter table userinfo_c3
  disable constraint CK_SALARY_NEW;

  5.3.2 删除检查约束

  DROP CONSTRAINT constraint_name;

  例如：alter table userinfo_c3
  drop constraint CK_SALARY_NEW;

  总结

  oracle中的五个常用约束：

  1.1

  非空约束

  主键约束：每张表中只能有一个，可以由多个字段构成

  外键约束：涉及两个表之间的关系

  唯一约束

  检查约束

  1.2 在创建表时设置约束：非空约束只能在列级设置，不能在表级设置；其它约束即可在列级设置，也可在表级设置；并且非空约束在设置时是没有名字的

  1.3 在修改表时添加约束：唯一一个和其它约束不同的也是非空约束，实际上在修改表时添加约束用到的语句是修改字段的语句ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

  1.4 更改约束的名称：非空约束是没用名字的，因此不能使用下面的语句更改约束的名称，其它约束都是可以更改的。

  1.5 改名首先应记住数据字典：user_constraints，通过这个数据字典来查找对应的名字。 语句：ALTER TABLE table_name RENAME CONSTRAINT old_name TO new_name;

  1.6 删除约束:删除非空约束就是把非空变成可以为空：ALTER TABLE table_name MODIFY column_name datatype NULL;

  其它四个约束均可以使用下面的语句禁用或彻底删除非空约束：DISABLE|ENABLE CONSTRAINT constraint_name;

  DROP CONSTRAINT constraint_name;

  删除主键约束：DROP PRIMARY KEY;

五 查询

简单举例：查询表中含有哪些约束？select constraint_name,constraint_type,status from user_constraints where table_name ='USERINFO_U2'

• 基本查询语句

  SELECT [DISTINCT] column_name1,…|*
  FROM table_name
  [WHERE conditions];其中的[DISTINCT]作用是不显示重复的记录

• 在SQL*PLUS中设置格式（查询结果的显示格式）

  COLUMN column_name HEADING new_name;更改我们显示的字段名，更改查询后结果中的字段名，并没有改变表中的字段名；其中COLUMN可简写为COL；

  例如：先创建表：create table users
  (id varchar2(10) primary key,
  username varchar2(20),
  salary number(7,2));

  col username heading 用户名

  select * from users;

  ID 用户名 SALARY

  ---

  1 aaa 800
  2 bbb 1800.5
  3 cccc 5000.5

  COLUMN column_name FORMAT dataformat;使用来设置结果显示的格式的；注意：字符类型只能设置显示的长度。

  col username format a10;字符型数据格式的设置，用a开头，后面写长度，如：“a10”

  select * from users;

  ID 用户名 SALARY

  ---

  1 aaa 800
  2 bbb 1800.5
  3 cccc 5000.5

  col salary format 9999.9;数值类型用“9”代表一个数字“9999.9”表示4位整数，一位小数

  select * from users;

  ID 用户名 SALARY

  ---

  1 aaa 800.0
  2 bbb 1800.5
  3 cccc 5000.5

  col salary format 999.9;

  select * from users;

  ID 用户名 SALARY

  ---

  1 aaa 800.0
  2 bbb ######
  3 cccc ######

  能够满足设置的三位整数，一位小数的格式的数据被输出，而不能满足的则显示其它字符；

  col salary format $9999.9;可以添加美元符号；

  select * from users;

  ID 用户名 SALARY

  ---

  1 aaa $800.0 2 bbb$1800.5
  3 cccc $5000.5

  COLUMN column_name CLEAR;清除设置的格式。

  col username clear;
  col salary clear;
  select * from users;

  ID USERNAME SALARY

  ---

  1 aaa 800
  2 bbb 1800.5
  3 cccc 5000.5

• 查询表中的所有字段及制定字段

  查询所有字段：select * from table_name;

  col id heading 编号;
  col username heading 用户名;
  col salary heading 工资;
  select * from users;

  编号 用户名 工资

  ---

  1 aaa 800
  2 bbb 1800.5
  3 cccc 5000.5

  查询指定的字段：select username,salary from users;显示顺序是根据查询的顺序，以*号查询全部字段则是按表中字段的顺序显示；

• 给字段设置别名

  注意：给字段设置别名是针对查询结果进行的，并没更改字段的名字。

  SELECT column_name AS new_name,…
  FROM table_name;

  注意：AS可以省略，用空格隔开原来的字段名和新字段名即可；

  例如：select id as 编号,username as 用户名,salary 工资
  from users;

  select distinct username as 用户名 from users;可以去掉重复的用户名。

• 运算符和表达式

  运算符：2*3中的’ * ‘就是运算符，2*3就是表达式
  表达式=操作数+运算符

  oracle中的操作数可以有变量、常量和字段

  算术运算符（+,-,* ,/）

  比较运算符（>,>=,<,<=,=,<>）都是用在SQL语句中where条件中的。5>2的到true,5>6得到fasle；比较运算符的优先级大于逻辑运算符的优先级；

  逻辑运算符（and,or,not），优先级按not,and,or顺序递减

• 在SELECT语句中使用运算符

  表中所有数据：

  ID USERNAME SALARY

  ---

  1 aaa 800
  2 bbb 1800.5
  3 cccc 5000.5
  4 aaa 2000

  使用算术运算符：select id,username,salary+200 from users;结果并不影响原来的数据；若要影响表中的数据，则必须使用update对表中数据修改。

  使用比较运算符：select username from users where salary>800;

  使用逻辑运算符：select username from users where salary>800 and salary<>1800.5;

• 带条件的查询

  单一条件的查询：select salary from users where username=’aaa’;

  select username,salary from users where id=3;

  多条件的查询：（逻辑运算符连接表达式）select * from users where username=’aaa’ or salary>2000;

  select * from users where username=’aaa’ or (salary>800 and salary<2000);

  select * from users where username=’aaa’ or salary>800 and salary<2000;和上面的结果相同；

  select * from users where not(username=’aaa’);

• 模糊查询

  LIKE运算符

  通配符的使用（ _ 或者 % ），一个_ 只能代表一个字符，% 可以代办0到多个任意字符

  使用LIKE查询：

  例如：查询用户名以a开头的用户信息

  select * from users where username like ‘a%’;

  select * from users where username like ‘a_’;

  查询用户名第二个字符是a的用户信息：

  select * from users where username like ‘_ a%’;记得去掉空格，在md格式下，下划杠会冲突

  select * from users where username like ‘%a%’;

• 范围查询

  如：年龄在18-40岁之间的员工有哪些？年龄在50岁以上的有哪些？

  例如：查询800到2000之间的员工工资

  salary>=800 and salary<=2000;

  还可以使用：

  BETWEEN…AND操作符，查询结果是一个闭合区间

  select * from users where salary between 800 and 2000;查询结果是一个闭合区间，即包含800也包含2000；

  select * from users where salary not between 800 and 2000;

  IN/NOT IN 操作符

  例如：查询用户名是aaa或者bbb的用户信息

  select * from users where username in(‘aaa’,’bbb’);

  select * from users where username not in(‘aaa’,’bbb’);

• 对查询结果排序

  SELECT … FROM …[WHERE…]
  ORDER BY column1 DESC/ASC,…

  关键字DESC是降序排列，ASC是升序排列。

  select * from users order by id desc;

  select * from users order by id desc,salary asc;工资升序排列是在前面的id字段相同的情况下，这里id是主键，不能重复，可以改为username

  select * from users order by usename desc,salary asc;

• case…when语句的使用

  第一种形式的应用：
  CASE column_name
  WHEN value1 THEN result1 …
  [ELSE result] END;通常会放到select语句中

  例如：

  select username,case username when ‘aaa’ then ‘计算机部门’
  when ‘bbb’ then ‘市场部门’ else ‘其它部门’ end as 部门
  from users;when和when直间不叫逗号，不然会报错。

  第二种形式的应用(搜索)：

  CASE
  WHEN clumn_name =value1
  THEN result1,…[ELSE result] END

  例如：
  select username,case when username=’aaa’ then ‘计算机部门’
  when username=’bbb’ then ‘市场部门’ else ‘其他部门’ end as 部门
  from users;

  select username,case when salary<=800 then ‘工资低’
  when salary>5000 then ‘工资高’ else ‘工资正常’ end as 工资水平
  from users;

• decode函数的使用

  decode(column_name,value1,result1,…,defaultvalue)

  例如：

  select username,decode(username,’aaa’,’计算机部门’,’bbb’,’市场部门’,’其它’) as 部门
  from users;

六 总结

• 用户与表空间

  如何查看登录用户：show user命令 dba_users数据字典

  启用scott用户：alter user scott account unlock;

  如何查看某个用户的默认表空间和临时表空间

  表空间管理：创建，修改，删除表空间

• 表与约束

  数据类型：字符（CHAR(N),NCHAR(n),VARCHAR2(n),NVARCHAR2(n)），数值(NUMBER(p,s),FLOAT(n))，日期(DATE,TIMESTAMP)，其它(BLOB,CLOB)

  对表的创建，修改，删除

  对表数据的操作：添加数据insert,修改数据update,删除数据delete

  约束：非空，主键，外键，唯一，检查

• 查询语句

  查询语句：查询所有字段和指定字段

  为字段设置别名

  在查询语句中使用运算符和表达式

  在查询语句中加入条件where

  范围查询

  模糊查询：LIKE关键字，通配符_和%

  CASE…WHEN语句和DECODE函数