结构化查询语言SQL及其分类

2019独角兽企业重金招聘Python工程师标准>>>

简单了解SQL（结构化查询语言）

结构化查询语言（英语：Structural Query Language，缩写：SQL），是一种特殊目的之编程语言，用于数据库中的标准数据查询语言，IBM公司最早使用在其开发的数据库系统中。1986年10月，美国国家标准学会对SQL进行规范后，以此作为关系式数据库管理系统的标准语言（ANSI X3. 135-1986），1987年得到国际标准组织的支持下成为国际标准。不过各种通行的数据库系统在其实践过程中都对SQL规范作了某些编改和扩充。所以，实际上不同数据库系统之间的SQL不能完全相互通用。

SQL分类：

• 数据查询语言（DQL）
• 数据定义语言（DDL）
• 数据操纵语言（DML）
• 数据控制语言（DCL）
• 事务控制语言（TCL）

图片可在新窗口中打开，放大进行查看

1.数据查询语言（DQL）

数据查询语言（Data Query Language, DQL）是SQL语言中，负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：

常规字段
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

常用字段

SELECT [要提取的字段，或是用"*"代表全部的字段]

FROM [要提取的数据来源对象，包含数据表，视图表等] -- 可搭配JOIN做连接。

WHERE [提取的查询條件] -- WHERE可有可無。

GROUP BY [要做聚合的分组字段] -- GROUP BY在使用聚合指令時必须要有。

HAVING [要做为条件的聚合字段] -- 要使用聚合函数作为条件时使用

ORDER BY [要排序的字段和方向] -- ORDER BY可有可无，若沒有则是按照查询引擎的输出顺序为主。

2.数据定义语言（DDL）

1.CREATE - to create objects in the database 创建对象，可以用来创建用户、数据库，数据表，视图，存储过程，存储函数，触发器，索引等。
2.ALTER - alters the structure of the database 修改对象，负责数据库对象修改的指令。
3.DROP - delete objects from the database 删除对象，只需要指定要删除的数据库对象名即可。
4.TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
Truncate table 表名 速度快,而且效率高,因为:
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

DDL使用示例

-创建一个名为p_student的表
create table p_student(
sno varchar2(5) not null primary key,
sname varchar2(50) not null,
ssex varchar2(1) not null,
sage varchar2(2));

创建之后表状态见下图

--修改列的类型
alter table p_student modify(sno number(4));
--重命名一个列
alter table p_student rename column sage to age;
--增加一个数据列
alter table p_student add(score number(2));
--删除一个数据列
alter table p_student drop column ssex;
--重命名表名称
alter table p_student rename to student;

修改完成表结构后的表的属性状态见下图

--删除已存在的p_student表
drop table student;

 

3.数据操纵语言（DML）

数据操纵语言（Data Manipulation Language, DML）是SQL语言中，负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除。

1.INSERT - insert data into a table 添加数据，将数据插入到数据库对象中的指令，可以插入数据的数据库对象有数据表以及可更新查看表两种。
2.UPDATE - updates existing data within a table 更新数据，依给定条件，将匹配条件的数据表中的数据更新为新的数值。
3.DELETE - deletes all records from a table, the space for the records remain 删除数据。

DML使用示例

INSERT INTO 表（列1 ，列2 …） VALUE （表达式1 ，表达式2 .. ) ;

INSERT INTO 表（列1 ，列2 …） VALUES （表达式1 ，表达式2 .. ) ，（表达式1 ，表达式2 .. ) ，...;

--不加列选项默认全插入
insert into student values(1,'abc',23,99);

--插入指定的列
insert into student (sno,sname,age) values (2,'oka',24);

利用INSERT语句还可以从另一个表中复制数据，这时要在INSERT语句中使用子查询，对应的语法格式为：
INSERT INTO 表1 （列1 ，列2 …）
SELECT 列 1 ，列2 ••• FROM 表2 WHERE 条件表达式;

这里的SELECT子句实际上是一个子查询。

执行这样的语句时，首先执行SELECT 子句，将返回的查询结果作为指定列的值，插入到表中。
用这种方法可以－次向表中插入多行，但是需要注意的是，表指定的各列要与SELECT子句中的各列在排列顺序、数据类型和数量上保持一致。

 --复制表

create table student_copy as select * from student;

--修改表中的数据
update student_copy set sno=3 where sno=1;
update student_copy set sno=4 where sno=2;

--整个记录的每一列都插入
insert into student select * from student_copy where sno=3;

--插入记录的指定列
insert into student (sno,sname) select sno,sname from student_copy where sno=4;

操作完毕数据状态见下图

UPDATE可以一次修改一行，也可以修改多行。
这条语句的语法格式为：

UPDATE 表名称 SET 列名称=表达式1, 列名称=表达式2 WHERE 条件;

UPDATE语句通过SET子句为指定列指定新值，将列值修改为指定的表达式。

在SET子句中指定所有需要修改的列。
在默认情况下，UPDATE语句不需要WHERE子句，这时UPDATE语句将修改表中的所有行。

如果通过WHERE子句指定了条件，那么UPDATE语句只修改满足条件的行。

在UPDATE语句的WHERE子句中，也可以使用子查询。这时的条件并不是一个确定的条件，而是依赖于对另一个表的查询。

--不加where条件的话默认修改表中的所有行
update student set score=66;

--更新指定列
update student set sname='xiaoming',age='20' where sno=4;

--使用子查询进行更新
update student set (sname,age) =(select sname,age from student_copy where sno=3) where sno=4;
update student set sname=(select sname from student_copy where sno=3),age=(select age from student_copy where sno=4) where sno=3; 
update student set sname=(select sname from student_copy where sno=3),age=(select age from student_copy where sno=4);

 

DELETE语句的语法格式为：

DELETE FROM 表名称 WHERE 条件;

在默认情况下， DELETE语句可以不使用WHERE子句，这时将删除表中的所有行。

如果希望只删除表中的一部分数据，需要通过WHERE指定条件。

在DELETE语句的WHERE子句也可以使用子查询，子查询与SELECT语句中的子查询用法相同。

--删除指定行数据
delete from student where sno=4;
--删除名称为abc的行数据
delete from student where sname='abc'
--删除全部数据
delete from student;

--使用子查询的删除
delete from student where sname=(select sname from student_copy where sno=3);
delete from student where sname in (select sname from student_copy);

4.数据控制语言（DCL）

数据控制语言 (Data Control Language) 在SQL语言中，是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。DCL以控制用户的访问权限为主，GRANT为授权语句，对应的REVOKE是撤销授权语句。

1.GRANT - gives user's access privileges to database 授权，允许对象的创建者给某用户或某组或所有用户（PUBLIC）某些特定的权限。
2.REVOKE - withdraw access privileges given with the GRANT command 收回已经授予的权限，可以废除某用户或某组或所有用户访问权限

DCL使用示例

--DCL数据控制语言
/**
系统权限:是在数据库中执行某种特定操作的权利，系统权限并不针对某个特定的对象，而是针对整个数据库范围。常用的系统权限有：
CREATE SESSION 连接到数据库上
CREATE SEQUENCE 创建序列，序列是一系列数字，通常用来自动填充主键列
CREATE SYNONYM　　 创建同名对象
CREATE TABLE　　创建表
CREATE ANY TABLE 在任何模式中创建表
DROP TABLE 删除表
DROP ANY TABLE 　　 删除任何模式中的表
CREATE PROCEDURE 创建存储过程
EXECUTE ANY PROCEDURE 执行任何模式中的存储过程
CREATE USER 创建用户
DROP USER　　　删除用户
CREATE VIEW 创建视图

对象权限
是针对特定的模式对象执行操作的权利，只能针对模式对象来设置和管理对象全新啊，这些对象包括表、视图、存储过程等常用的对象权限有：

SELECT 允许执行查询操作
INSERT 允许执行插入操作
UPDATE 允许执行修改操作
DELETE 允许执行删除操作
EXECUTE 语序执行存储过程
**/
--创建用户
create user xiaoming identified by 123;
--给用户分配权限
grant create session,create user,create table to xiaoming;
--收回用户某一权限
revoke create table from xiaoming;
--查看当前用户下所对应的系统权限和对象权限
select * from user_sys_privs;
select * from user_tab_privs;

5.事务控制语言（TCL）

什么是事务:用于确保数据的一致性,由一组相关的DML组成, 该组DML的操作要么全确认,要么全取消。事务控制是指对关系型数据库管理系统（RDBMS）里可能发生的各种事务的管理能力。

COMMIT - save work done 提交
SAVEPOINT - identify a point in a transaction to which you can later roll back 保存点
ROLLBACK - restore database to original since the last COMMIT 回滚
SET TRANSACTION - Change transaction options like what rollback segment to use 设置当前事务的特性，它对后面的事务没有影响。

转载于:https://my.oschina.net/fairy1674/blog/1819245