SQL从入门到删库2SQL语言数据库表表数据类型表命名规范主键约束DDL语法

一、SQL语言

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理[关系数据库系统;

二、sql语言的分类

• DML: Data Manipulation Language 数据操纵语

  DML用于查询与修改数据记录，包括如下SQL语句：
  INSERT：添加数据到数据库中
  UPDATE：修改数据库中的数据
  DELETE：删除数据库中的数据

• DDL: Data Definition Language 数据定义语

  DDL用于定义数据库的结构，比如创建、修改或删除数据库对象，包括如下SQL语句：
  CREATE TABLE：创建数据库表
  ALTER TABLE：更改表结构、添加、删除、修改列长度
  DROP TABLE：删除表
  CREATE INDEX：在表上建立索引
  DROP INDEX：删除索引

• DCL: Data Control Language 数据控制语言

  DCL用来控制数据库的访问，包括如下SQL语句：
  GRANT：授予访问权限
  REVOKE：撤销访问权限
  COMMIT：提交事务处理
  ROLLBACK：事务处理回退
  SAVEPOINT：设置保存点
  LOCK：对数据库的特定部分进行锁定

• DQL: DataQueryLanguage数据查询语言的简称

  SELECT：选择（查询）数据
  SELECT是SQL语言的基础，最为重要

三、数据库表

表是 Oracle数据库最基本的对象,他是存储用户数据, 关系数据库的所有操作最终都是围绕用户数据(表)进行的.

表由行和列两部分组成：

行：数据（表的数据）

列：字段（表的属性）

四、表数据类型

1. 字符串类型

   • CHAR 定长字符串类型，最多可以存储2,000字节的信息。

     例如 CHAR (10),插入abc，则在数据库中占10个字节。

   • VARCHAR 变长字符串类型，最多可以存储4,000字节的信息。

     例如 VARCHAR (10),插入abc，则在数据库中占3个字节。

   • VARCHAR2 变长字符串类型，最多可以存储4,000字节的信息。

     例如 VARCHAR 2(10),插入abc，则在数据库中占6个字节。

     区别

     • varchar只对汉字和全角等字符占两字节，数字，英文字符等都是一个字节。
     • varchar2把空串等同于null处理，而varchar仍按照空串处理。
     • varchar2把所有字符都占两字节处理(一般情况下)，并且字符要用几个字节存储，要看数据库使用的字符集。

2. 数字类型

   • NUMBER(P,S)是最常见的数字类型

     P 是Precison的英文缩写，即精度缩写，表示有效数字的位数，最多不能超过38个有效数字
     S是Scale的英文缩写，可以使用的范围为-84~127。Scale为正数时，表示从小数点到最低有效数字的位

   • INTEGER是NUMBER的子类型，它等同于NUMBER（38,0），用来存储整数。若插入、更新的数值有小数，则会被四舍五入。

   • BINARY_FLOAT 是 32 位、 单精度浮点数字数据类型，可以支持至少6位精度,每个 BINARY_FLOAT 的值需要 5 个字节，包括长度字节。

   • BINARY_DOUBLE 是为 64 位，双精度浮点数字数据类型，每个 BINARY_DOUBLE 的值需要 9 个字节，包括长度字节。。

   • Float(n),数 n 指示位的精度，可以存储的值的数目。N 值的范围可以从 1 到 126。若要从二进制转换为十进制的精度，请将 n 乘以 0.30103。要从十进制转换为二进制的精度，请用 3.32193 乘小数精度。126 位二进制精度的最大值是大约相当于 38 位小数精度。

3. 日期类型

   • DATE这是ORACLE最常用的日期类型，它可以保存日期和时间，常用日期处理都可以采用这种类型。DATE表示的日期范围可以是公元前4712年1月1日至公元9999年12月31日
   • TIMESTAMP§这也是ORACLE常用的日期类型，它与date的区别是不仅可以保存日期和时间，还能保存小数秒，小数位数可以指定为0-9，默认为6位，所以最高精度可以到ns(纳秒)，数据库内部用7或者11个字节存储，如果精度为0，则用7字节存储，与date类型功能相同，如果精度大于0则用11字节存储。

4. LOB类型（大字段类型）

   内置的LOB数据类型包括BLOB、CLOB、NCLOB、BFILE（外部存储）的大型化和非结构化数据，如文本、图像、视频、空间数据存储。BLOB、CLOB、NCLOB类型

   • CLOB全称为字符大型对象（Character Large Object)。它与LONG数据类型类似，只不过CLOB用于存储数据库中的大型单字节字符数据块，不支持宽度不等的字符集。可存储的最大大小为4G字节。
   • BLOB它存储非结构化的二进制数据大对象，它可以被认为是没有字符集语义的比特流，一般是图像、声音、视频等文件。BLOB对象最多存储(4G)的二进制数据。

五、表命名规范

• 必须以字母开头
• 字符长度在1-30之间
• 只能包含A-Z，a-z，0-9，_，$和#
• 被同一个用户拥有的对象不能有重复的名字
• 不能是Oracle服务器保留字

六、主键

主键：表中经常有一个列或多列的组合，其值能唯一地标识表中的每一行。

七、约束

分类：

列约束必须跟在列的定义后面，表约束不与列一起，而是单独定义。

• 表级约束

  表级约束可以作用在多个列上（当然表级约束也可以作用在一个列上）

• 列级约束

  列级约束只能作用在一个列上

数据库约束有五种：

• ​ 主键约束（PRIMARY KEY）可以定义在表级或列级

  1. 键列必须必须具有唯一性，且不能为空，其实主键约束 相当于 UNIQUE+NOT NULL
  2. 一个表只允许有一个主键
  3. 主键所在列必须具有索引（主键的唯一约束通过索引来实现），如果不存在，将会在索引添加的时候自动创建

• ​ 唯一性约束（UNIQUE)可以定义在表级或列级

  1. 对于UNIQUE约束来讲，索引是必须的。如果不存在，就自动创建一个（UNIQUE的唯一性本质上是通过索引来保证的）
  2. UNIQUE允许null值，UNIQUE约束的列可存在多个null。这是因为，Unique唯一性通过btree索引来实现，而btree索引中不包含null。当然，这也造成了在where语句中用null值进行过滤会造成全表扫描。

• ​ 非空约束（NOT NULL)只能定义在列级

  1. 非空约束作用的列也叫强制列。顾名思义，强制键列中必须有值，当然建表时候若使用default关键字指定了默认值，则可不输入。

• ​ 外键约束（FOREIGN KEY)可以定义在表级或列级

  外键约束定义在具有父子关系的子表中，外键约束使得子表中的列对应父表的主键列，用以维护数据库的完整性。不过出于性能和后期的业务系统的扩展的考虑，很多时候，外键约束仅出现在数据库的设计中，实际会放在业务程序中进行处理。

  外键约束注意以下几点：

  1. 外键约束的子表中的列和对应父表中的列数据类型必须相同，列名可以不同
  2. 对应的父表列必须存在主键约束（PRIMARY KEY）或唯一约束（UNIQUE）
  3. 外键约束列允许NULL值，对应的行就成了孤行了

• ​ 检查约束（CHECK)可以定义在表级或列级

  1. 检查约束可用来实施一些简单的规则，比如列值必须在某个范围内。检查的规则必须是一个结果为true或false 的表达式。

语法：

CREATE TABLE [schema.]table
	    (column datatype [DEFAULT expr]
		[column_constraint],
		...
		[table_constraint][,...]);

CREATE TABLE employees(
  	     employee_id  NUMBER(6),
    	     first_name   VARCHAR2(20),
  	     ...
  	     job_id       VARCHAR2(10) NOT NULL,
	     CONSTRAINT emp_emp_id_pk 
		           	PRIMARY KEY (EMPLOYEE_ID));

八、DDL语法

CREATE TABLE -----创建表
格式：
CREATE TABLE 表名 (
	属性名 属性类型(长度),
	属性名 属性类型(长度),
	.....
)


	PRIMARY KEY -----设置主键（用于定义主键的，在主键的属性后面添加PRIMARY KEY）
	格式：
	CREATE TABLE 表名 (
		属性名 属性类型(长度),
		属性名 属性类型(长度),
		id varchar2(20) PRIMARY KEY   ----这个是设置主键
	)

COMMENT ON -----添加注释，添加表备注使用table、添加字段备注使用COLUMN
格式：
COMMENT ON table usr2 is '用户信息2';
COMMENT ON COLUMN usr2.name is '姓名';	

DESCRIBE -----用于列出指定表或视图中的所有列
格式：
DESCRIBE 表名/视图名称;


	DEFAULT -----设置字段的默认值，默认值的类型要和设置字段的类型要一致
	格式1：
		CREATE TABLE 表名 (
			属性名 属性类型(长度) DEFAULT 'fjb',
			属性名 属性类型(长度),
			id varchar2(20) PRIMARY KEY   ----这个是设置主键
		)
	格式2：
		ALTER TABLE USR2 MODIFY (AGE DEFAULT 18 );


ALTER  TABLE
格式：
    1.添加字段：
    alter table  表名  add (字段  字段类型)  [ default  '输入默认值']  [null/not null]  ;
    2.添加备注：
    comment on column  库名.表名.字段名 is  '输入的备注';  如： 我要在ers_data库中  test表 			document_type字段添加备注  comment on column ers_data.test.document_type is '文件类型';
    3.修改字段类型：
    alter table 表名  modify (字段  字段类型  [default '输入默认值' ] [null/not null] ,字段字段		类型  [default '输入默认值' ] [null/not null] ); 修改多个字段用逗号隔开
    4.删除字段：
    alter table  表名  drop (字段);
    5.添加检查约束
    alter table emp add constraint ename check(sex in('男','女'));
    6.添加非空约束
   	alter table emp modify ename not null;
   	7.添加唯一约束
   	alter table emp modify ename unique;

SQL1:

--执行sql 对话框
--select * from HELP;
--这个help是查询哪一个schema，答案：查询的是自己默认的表空间
--每一个用户中都有一个表空间，需要查询哪一个表空间就在表前面加上schema即可
--select * from system.help;
--system  就是我们的schema
--可以通过schema，查询其它用户的数据表
--select * from SCOTT.dept;



--在dagege用户空间中创建一张数据表
--在system中查询 dagege中table1的数据
select * from dagege.table1;

SQL2:

--CREATE TABLE aaa
--(
--    bbb VARCHAR2(20) not null ,
--    ccc INTEGER 
--);

--创建数据表的同时设置主键
--CREATE TABLE bbbbbb(
--    bbbbbb VARCHAR2(20) not null,
--    cccccc INTEGER,
--    id VARCHAR2(20) PRIMARY KEY
--);

--加注释
--COMMENT ON table dagege.aaa is '测试数据表aaa';
--
--COMMENT ON COLUMN dagege.aaa.bbb is '测试数据表aaa';
--
----创建带有默认的字段的表
--CREATE table ccc(
--    id VARCHAR2(20) PRIMARY key,
--    name VARCHAR2(20) DEFAULT 'abc'
--)

----给数据表添加默认值
--alter table dagege.bbbbbb MODIFY(BBB DEFAULT '123');
--
--
----添加字段：
--alter table aaa ADD(aaa number(10,2));
--
--
----修改字段类型
--alter table aaa modify(aaa VARCHAR2(20) not null);
--
----删除约束
--alter table aaa modify(aaa VARCHAR2(20) null);
--
----删除字段
--alter table aaa drop (ccc);

--添加检查约束
alter table dagege.person add CONSTRAINT sex_check check(sex in('a','n','男','女','人妖'));

--添加非空约束
alter table person MODIFY sex not null;

--添加唯一约束
alter table person modify name unique;