欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL语言基础及应用实例讲解

程序员文章站 2022-04-08 10:41:33
数据库 一. SQL语言概述 SQL(结构化查询语言)是关系型数据库语言,用于建立、存储、修改、检索和管理关系数据库中的数据。 PL/SQL与SQL的关系: l PL/SQ...

数据库

一. SQL语言概述

SQL(结构化查询语言)是关系型数据库语言,用于建立、存储、修改、检索和管理关系数据库中的数据。

PL/SQL与SQL的关系:

l PL/SQL是Oracle对关系数据库语言SQL的扩充,它将SQL的数据操纵功能与过程化语言数据处理功能相结合。

l PL/SQL是Oracle数据库应用的重要开发工具。

二.SQL语言的组成

数据定义语言DDL :CREATE、ALTER、DROP

数据操纵语言DML:INSERT、DELETE、UPDATE、SELECT

事物控制语句TCL:COMMIT、ROLLBACK、SAVEPOINT(部分回滚)

数据控制语言DCL:GRANT、REVOKE

三.数据类型

常用数据类型:

1.字符串

CHAR 固变 默认1字节,最大2000字节

VARCHAR2 可变长度 默认0字节,最大4000字节

NVARCHAR2 可变长度 默认0节字,最大4000字节

VARCHAR2和NVARCHAR2的区别:

1.NVARCHAR2的性能更强;

2.varchar2(字节数),nvarchar2(字符数)

LONG 可变长度 最大为2GB

2.数值类型

NUMBER(p, s) 长度1-22字节

示例:

NUMBER 默认为整型

NUMBER(5) 长度为5的整数,如:12345

NUMBER(5,1) 长度为5,且有1位小数位,如:1234.5

3.日期类型

DATE 保存日期和时间

TIMESTAMP 除保存日期和时间外,还可保存小数秒,小数位可指定为0-9,默认6位

CREATE TABLE tb_date(

D1 DATE,

D2 TIMESTAMP(9)

);

插入日期类型数据

方式1:INSERT INTO tb_date(D1, D2)

VALUES( DATE‘2018-2-16’, TIMESTAMP '2018-2-16 13:24:52.123456789');

方式2:INSERT INTO tb_date(D1, D2)

VALUES( TO_DATE(‘2018-2-16 10:20:30’, ‘YYYY-MM-DD HH24:MI:SS’), TO_TIMESTAMP( '2018-2-16 13:24:52.123456', ‘YYYY-MM-DD HH24:MI:SS.FF6’));

4. LOB大对象类型

BLOB 存二进制对象(图片、视频等)

CLOB 存字符格式大对象

提示:在应用开发中,我们将图片、视频、音乐等地址(字符串,如:E:\abc\110.jpg)存在数据库表中,根据地址找到对应的图片、视频、音乐。

5. 数据完整性(约束)

数据完整性要求数据库中的数据具有准确性。准确性是通过数据库表的设计和约束来实现的。

为实现数据完整性,数据库需要做两方面的工作:

第一:确保每行的数据符合要求

第二:确保每列的数据符合要求

五大约束:

主键约束:primary key 外键约束:foreign key 唯一约束:unique

空值约束:null 检查约束:check 默认约束:default

四.创建表结构

CREATE TABLE 表名(

列名 数据类型 完整性约束,

……

);

案例:列级定义

CREATE TABLE t_class(

class_id NUMBER CONSTRAINT pk_class_id PRIMARY KEY, --为class_id添加主键约束

cname VARCHAR2(20) CONSTRAINT uq_cname UNIQUE --为cname添加唯一约束

)

案例:表级定义

CREATE TABLE tb_student(

stu_id NUMBER(5),

stu_name VARCHAR2(10) NOT NULL, --空值约束

u_sex VARCHAR2(6),

bitrhday DATE DEFAULT SYSDATE, --默认约束:系统时间(system)

class_id NUMBER(3),

CONSTRAINT pk_stu_id PRIMARY KEY(stu_id), --主键约束

CONSTRAINT fk_class_id FOREIGN KEY(class_id) REFERENCES t_class(class_id), --外键约束

CONSTRAINT sex_chk CHECK(sex IN ('男','女')) --检查约束

);

注意:空值约束和默认无论是列级约束还是表级约束都打在列后面,主键约束,外键约束,唯一约束,检查约束可以用列级定义也可以用表级定义。

一、修改表结构

1. 修改约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 具体的约束说明

案例:

ALTER TABLE t_student

ADD CONSTRAINT uq_username UNIQUE(stuname) --创建唯一约束

ADD CONSTRAINT pk_uid PRIMARY KEY(stuID) --创建主键约束

ADD CONSTRAINT fk_cid FOREIGN KEY(cid) REFERENCES t_class(cid) --创建外键约束

ADD CONSTRAINT ck_age CHECK(age>0 AND age<150) --创建检查约束

----默认值约束

ALTER TABLE tb_student

MODIFY birthday DEFAULT SYSDATE

2. 删除约束

删除T_STUDENT表中的pk_uid约束。

ALTER TABLE t_student

DROP CONSTRAINT pk_uid

3. 查看约束

查看T_STUDENT表中所有约束。注意:表名必须全部大写

SELECT * FROM user_constraints

WHERE table_name = ‘T_STUDENT’

4..使用ALTER TABLE命令增加新字段

ALTER TABLE <表名>

ADD 列名 类型 约束

5.使用ALTER TABLE 命令删除字段

ALTER TABLE <表名>

DROP COLUMN 列名

 

6.用ALTER TABLE命令修改字段

ALTER TABLE <表名>

MODIFY 列名 类型(修改后的) 约束(修改后的)

oracle修改字段名,

alter table [表名] rename column 旧的字段名 to 新的字段名;

 

7.重命名基本表

RENAME <原表名> TO <目标表名>

或者:

ALTER TABLE <原表名>

RENAME TO <目标表名>

 

8. 追加约束

追加一个表的主键约束:

ALTER TABLE <表名>

ADD CONSTRAINT <主键名> PRIMARY KEY(列名)

 

追加一个表的唯一约束:

ALTER TABLE <表名>

ADD CONSTRAINT <约束名> UNIQUE(列名)

 

追加一个表的检查约束:

ALTER TABLE <表名>

ADD CONSTRAINT <约束名> CHECK(表达式)

 

追加一个表的外键约束:

ALTER TABLE <表名>

ADD CONSTRAINT <约束名>

FOREIGN KEY(列名) REFERENCES 主表名(列名)

二、删除表

DROP TABLE 表名

TRUNCATE TABLE 表名truncate

注意:该命令执行后,目标表即从数据库中被移除到回收站中,

基于该表的索引,触发器等数据对象也同时被删除。

三、复制表

1. 复制表结构及数据

--将emp表中的数据插入到tb_new表中(tb_new表要先创建,而且表结构与SELECT的结果集相同)

INSERT INTO tb_new

SELECT * FROM emp;

--复制emp表

CREATE TABLE tb_new

AS SELECT * FROM emp;

--复制emp表的结构

CREATE TABLE tb_new

AS SELECT * FROM emp WHERE 1=2;

2. 复制表结构

CREATE TABLE tb_new

AS SELECT * FROM emp WHERE 1=2;

四、设置多用户访问同一张表

建一张表,用管理员给多个用户授予访问权限

数据库

一. SQL语言概述

SQL(结构化查询语言)是关系型数据库语言,用于建立、存储、修改、检索和管理关系数据库中的数据。

PL/SQL与SQL的关系:

l PL/SQL是Oracle对关系数据库语言SQL的扩充,它将SQL的数据操纵功能与过程化语言数据处理功能相结合。

l PL/SQL是Oracle数据库应用的重要开发工具。

二.SQL语言的组成

数据定义语言DDL :CREATE、ALTER、DROP

数据操纵语言DML:INSERT、DELETE、UPDATE、SELECT

事物控制语句TCL:COMMIT、ROLLBACK、SAVEPOINT(部分回滚)

数据控制语言DCL:GRANT、REVOKE

三.数据类型

常用数据类型:

1.字符串

CHAR 固变 默认1字节,最大2000字节

VARCHAR2 可变长度 默认0字节,最大4000字节

NVARCHAR2 可变长度 默认0节字,最大4000字节

VARCHAR2和NVARCHAR2的区别:

1.NVARCHAR2的性能更强;

2.varchar2(字节数),nvarchar2(字符数)

LONG 可变长度 最大为2GB

2.数值类型

NUMBER(p, s) 长度1-22字节

示例:

NUMBER 默认为整型

NUMBER(5) 长度为5的整数,如:12345

NUMBER(5,1) 长度为5,且有1位小数位,如:1234.5

3.日期类型

DATE 保存日期和时间

TIMESTAMP 除保存日期和时间外,还可保存小数秒,小数位可指定为0-9,默认6位

CREATE TABLE tb_date(

D1 DATE,

D2 TIMESTAMP(9)

);

插入日期类型数据

方式1:INSERT INTO tb_date(D1, D2)

VALUES( DATE‘2018-2-16’, TIMESTAMP '2018-2-16 13:24:52.123456789');

方式2:INSERT INTO tb_date(D1, D2)

VALUES( TO_DATE(‘2018-2-16 10:20:30’, ‘YYYY-MM-DD HH24:MI:SS’), TO_TIMESTAMP( '2018-2-16 13:24:52.123456', ‘YYYY-MM-DD HH24:MI:SS.FF6’));

4. LOB大对象类型

BLOB 存二进制对象(图片、视频等)

CLOB 存字符格式大对象

提示:在应用开发中,我们将图片、视频、音乐等地址(字符串,如:E:\abc\110.jpg)存在数据库表中,根据地址找到对应的图片、视频、音乐。

5. 数据完整性(约束)

数据完整性要求数据库中的数据具有准确性。准确性是通过数据库表的设计和约束来实现的。

为实现数据完整性,数据库需要做两方面的工作:

第一:确保每行的数据符合要求

第二:确保每列的数据符合要求

五大约束:

主键约束:primary key 外键约束:foreign key 唯一约束:unique

空值约束:null 检查约束:check 默认约束:default

四.创建表结构

CREATE TABLE 表名(

列名 数据类型 完整性约束,

……

);

案例:列级定义

CREATE TABLE t_class(

class_id NUMBER CONSTRAINT pk_class_id PRIMARY KEY, --为class_id添加主键约束

cname VARCHAR2(20) CONSTRAINT uq_cname UNIQUE --为cname添加唯一约束

)

案例:表级定义

CREATE TABLE tb_student(

stu_id NUMBER(5),

stu_name VARCHAR2(10) NOT NULL, --空值约束

u_sex VARCHAR2(6),

bitrhday DATE DEFAULT SYSDATE, --默认约束:系统时间(system)

class_id NUMBER(3),

CONSTRAINT pk_stu_id PRIMARY KEY(stu_id), --主键约束

CONSTRAINT fk_class_id FOREIGN KEY(class_id) REFERENCES t_class(class_id), --外键约束

CONSTRAINT sex_chk CHECK(sex IN ('男','女')) --检查约束

);

注意:空值约束和默认无论是列级约束还是表级约束都打在列后面,主键约束,外键约束,唯一约束,检查约束可以用列级定义也可以用表级定义。

一、修改表结构

1. 修改约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 具体的约束说明

案例:

ALTER TABLE t_student

ADD CONSTRAINT uq_username UNIQUE(stuname) --创建唯一约束

ADD CONSTRAINT pk_uid PRIMARY KEY(stuID) --创建主键约束

ADD CONSTRAINT fk_cid FOREIGN KEY(cid) REFERENCES t_class(cid) --创建外键约束

ADD CONSTRAINT ck_age CHECK(age>0 AND age<150) --创建检查约束

----默认值约束

ALTER TABLE tb_student

MODIFY birthday DEFAULT SYSDATE

2. 删除约束

删除T_STUDENT表中的pk_uid约束。

ALTER TABLE t_student

DROP CONSTRAINT pk_uid

3. 查看约束

查看T_STUDENT表中所有约束。注意:表名必须全部大写

SELECT * FROM user_constraints

WHERE table_name = ‘T_STUDENT’

4..使用ALTER TABLE命令增加新字段

ALTER TABLE <表名>

ADD 列名 类型 约束

5.使用ALTER TABLE 命令删除字段

ALTER TABLE <表名>

DROP COLUMN 列名

 

6.用ALTER TABLE命令修改字段

ALTER TABLE <表名>

MODIFY 列名 类型(修改后的) 约束(修改后的)

oracle修改字段名,

alter table [表名] rename column 旧的字段名 to 新的字段名;

 

7.重命名基本表

RENAME <原表名> TO <目标表名>

或者:

ALTER TABLE <原表名>

RENAME TO <目标表名>

 

8. 追加约束

追加一个表的主键约束:

ALTER TABLE <表名>

ADD CONSTRAINT <主键名> PRIMARY KEY(列名)

 

追加一个表的唯一约束:

ALTER TABLE <表名>

ADD CONSTRAINT <约束名> UNIQUE(列名)

 

追加一个表的检查约束:

ALTER TABLE <表名>

ADD CONSTRAINT <约束名> CHECK(表达式)

 

追加一个表的外键约束:

ALTER TABLE <表名>

ADD CONSTRAINT <约束名>

FOREIGN KEY(列名) REFERENCES 主表名(列名)

二、删除表

DROP TABLE 表名

TRUNCATE TABLE 表名truncate

注意:该命令执行后,目标表即从数据库中被移除到回收站中,

基于该表的索引,触发器等数据对象也同时被删除。

三、复制表

1. 复制表结构及数据

--将emp表中的数据插入到tb_new表中(tb_new表要先创建,而且表结构与SELECT的结果集相同)

INSERT INTO tb_new

SELECT * FROM emp;

--复制emp表

CREATE TABLE tb_new

AS SELECT * FROM emp;

--复制emp表的结构

CREATE TABLE tb_new

AS SELECT * FROM emp WHERE 1=2;

2. 复制表结构

CREATE TABLE tb_new

AS SELECT * FROM emp WHERE 1=2;

四、设置多用户访问同一张表

建一张表,用管理员给多个用户授予访问权限

数据库

一. SQL语言概述

SQL(结构化查询语言)是关系型数据库语言,用于建立、存储、修改、检索和管理关系数据库中的数据。

PL/SQL与SQL的关系:

l PL/SQL是Oracle对关系数据库语言SQL的扩充,它将SQL的数据操纵功能与过程化语言数据处理功能相结合。

l PL/SQL是Oracle数据库应用的重要开发工具。

二.SQL语言的组成

数据定义语言DDL :CREATE、ALTER、DROP

数据操纵语言DML:INSERT、DELETE、UPDATE、SELECT

事物控制语句TCL:COMMIT、ROLLBACK、SAVEPOINT(部分回滚)

数据控制语言DCL:GRANT、REVOKE

三.数据类型

常用数据类型:

1.字符串

CHAR 固变 默认1字节,最大2000字节

VARCHAR2 可变长度 默认0字节,最大4000字节

NVARCHAR2 可变长度 默认0节字,最大4000字节

VARCHAR2和NVARCHAR2的区别:

1.NVARCHAR2的性能更强;

2.varchar2(字节数),nvarchar2(字符数)

LONG 可变长度 最大为2GB

2.数值类型

NUMBER(p, s) 长度1-22字节

示例:

NUMBER 默认为整型

NUMBER(5) 长度为5的整数,如:12345

NUMBER(5,1) 长度为5,且有1位小数位,如:1234.5

3.日期类型

DATE 保存日期和时间

TIMESTAMP 除保存日期和时间外,还可保存小数秒,小数位可指定为0-9,默认6位

CREATE TABLE tb_date(

D1 DATE,

D2 TIMESTAMP(9)

);

插入日期类型数据

方式1:INSERT INTO tb_date(D1, D2)

VALUES( DATE‘2018-2-16’, TIMESTAMP '2018-2-16 13:24:52.123456789');

方式2:INSERT INTO tb_date(D1, D2)

VALUES( TO_DATE(‘2018-2-16 10:20:30’, ‘YYYY-MM-DD HH24:MI:SS’), TO_TIMESTAMP( '2018-2-16 13:24:52.123456', ‘YYYY-MM-DD HH24:MI:SS.FF6’));

4. LOB大对象类型

BLOB 存二进制对象(图片、视频等)

CLOB 存字符格式大对象

提示:在应用开发中,我们将图片、视频、音乐等地址(字符串,如:E:\abc\110.jpg)存在数据库表中,根据地址找到对应的图片、视频、音乐。

5. 数据完整性(约束)

数据完整性要求数据库中的数据具有准确性。准确性是通过数据库表的设计和约束来实现的。

为实现数据完整性,数据库需要做两方面的工作:

第一:确保每行的数据符合要求

第二:确保每列的数据符合要求

五大约束:

主键约束:primary key 外键约束:foreign key 唯一约束:unique

空值约束:null 检查约束:check 默认约束:default

四.创建表结构

CREATE TABLE 表名(

列名 数据类型 完整性约束,

……

);

案例:列级定义

CREATE TABLE t_class(

class_id NUMBER CONSTRAINT pk_class_id PRIMARY KEY, --为class_id添加主键约束

cname VARCHAR2(20) CONSTRAINT uq_cname UNIQUE --为cname添加唯一约束

)

案例:表级定义

CREATE TABLE tb_student(

stu_id NUMBER(5),

stu_name VARCHAR2(10) NOT NULL, --空值约束

u_sex VARCHAR2(6),

bitrhday DATE DEFAULT SYSDATE, --默认约束:系统时间(system)

class_id NUMBER(3),

CONSTRAINT pk_stu_id PRIMARY KEY(stu_id), --主键约束

CONSTRAINT fk_class_id FOREIGN KEY(class_id) REFERENCES t_class(class_id), --外键约束

CONSTRAINT sex_chk CHECK(sex IN ('男','女')) --检查约束

);

注意:空值约束和默认无论是列级约束还是表级约束都打在列后面,主键约束,外键约束,唯一约束,检查约束可以用列级定义也可以用表级定义。

一、修改表结构

1. 修改约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 具体的约束说明

案例:

ALTER TABLE t_student

ADD CONSTRAINT uq_username UNIQUE(stuname) --创建唯一约束

ADD CONSTRAINT pk_uid PRIMARY KEY(stuID) --创建主键约束

ADD CONSTRAINT fk_cid FOREIGN KEY(cid) REFERENCES t_class(cid) --创建外键约束

ADD CONSTRAINT ck_age CHECK(age>0 AND age<150) --创建检查约束

----默认值约束

ALTER TABLE tb_student

MODIFY birthday DEFAULT SYSDATE

2. 删除约束

删除T_STUDENT表中的pk_uid约束。

ALTER TABLE t_student

DROP CONSTRAINT pk_uid

3. 查看约束

查看T_STUDENT表中所有约束。注意:表名必须全部大写

SELECT * FROM user_constraints

WHERE table_name = ‘T_STUDENT’

4..使用ALTER TABLE命令增加新字段

ALTER TABLE <表名>

ADD 列名 类型 约束

5.使用ALTER TABLE 命令删除字段

ALTER TABLE <表名>

DROP COLUMN 列名

 

6.用ALTER TABLE命令修改字段

ALTER TABLE <表名>

MODIFY 列名 类型(修改后的) 约束(修改后的)

oracle修改字段名,

alter table [表名] rename column 旧的字段名 to 新的字段名;

 

7.重命名基本表

RENAME <原表名> TO <目标表名>

或者:

ALTER TABLE <原表名>

RENAME TO <目标表名>

 

8. 追加约束

追加一个表的主键约束:

ALTER TABLE <表名>

ADD CONSTRAINT <主键名> PRIMARY KEY(列名)

 

追加一个表的唯一约束:

ALTER TABLE <表名>

ADD CONSTRAINT <约束名> UNIQUE(列名)

 

追加一个表的检查约束:

ALTER TABLE <表名>

ADD CONSTRAINT <约束名> CHECK(表达式)

 

追加一个表的外键约束:

ALTER TABLE <表名>

ADD CONSTRAINT <约束名>

FOREIGN KEY(列名) REFERENCES 主表名(列名)

二、删除表

DROP TABLE 表名

TRUNCATE TABLE 表名truncate

注意:该命令执行后,目标表即从数据库中被移除到回收站中,

基于该表的索引,触发器等数据对象也同时被删除。

三、复制表

1. 复制表结构及数据

--将emp表中的数据插入到tb_new表中(tb_new表要先创建,而且表结构与SELECT的结果集相同)

INSERT INTO tb_new

SELECT * FROM emp;

--复制emp表

CREATE TABLE tb_new

AS SELECT * FROM emp;

--复制emp表的结构

CREATE TABLE tb_new

AS SELECT * FROM emp WHERE 1=2;

2. 复制表结构

CREATE TABLE tb_new

AS SELECT * FROM emp WHERE 1=2;

四、设置多用户访问同一张表

建一张表,用管理员给多个用户授予访问权限