标准SQL语言的用法

时间:2024-06-15 12:04:44

原文链接:http://www.ifyao.com/2015/05/18/%E6%A0%87%E5%87%86%E7%9A%84sql%E8%AF%AD%E8%A8%80%E4%BD%BF%E7%94%A8%E6%96%B9%E6%B3%95%E5%8F%8A%E5%A4%9A%E8%A1%A8%E8%BF%9E%E6%8E%A5/

标准SQL语言的用法

SQL语言是目前最通用的关系数据库语言。ANSI SQL是指由美国国家标准局(ANSI)的数据库委员会制定的标准SQL语言,多数关系数据库产品支持标准SQL语言,但是它们也往往有各自的SQL方言。

在分层的软件结构中,关系数据库位于最底层,它的上层应用都被称为数据库的客户程序。以MySql为例,mysql.exe和Java应用就是它的两个客户程序。这些客户程序最终通过SQL语言与数据库通信。

SQL(Structured Query Language)的英语全称可翻译为结构化查询语言,但实际上它除了具有数据查询功能,还具有数据定义、数据操纵和数据控制功能。

SQL语言的类型

语言类型 描述 SQL语句
DDL(Data Definition Language) 数据定义语言,定义数据库中的表、视图和索引等 create、drop和alter语句
DML(Data Manipulation Language) 数据操纵语言,保存、更新或删除数据 insert、update和delete语句
DQL(Data Query Language) 数据查询语言,查询数据库中的数据 select语句
DCL(Data Control Language) 数据控制语言,用于设置数据库用户的权限 grant和remove语句

数据完整性

当用户向数据库输入数据时,由于种种原因,用户有可能输入错误数据。保证输入的数据符合规定,成为数据库系统,尤其是多用户的关系数据库系统首要关注的问题。为了解决这一问题,在数据库领域出现了数据完整性的概念。数据完整性(Data Integrity)就是指数据必须符合的规范,它主要分为三类:

实体完整性(Entity Integrity)

规定表的一行(即每一条记录)在表中是唯一的实体。实体完整性通过表的主键来实现。

域完整性(Domain Integrity)

指数据库表的列(即字段)必须符合某种特定的数据类型或约束。如not null。

参照完整性(Referential Integrity)

保证一个表的外键和另一个表的主键对应。

DDL数据定义语言

用于定义数据库中的表、视图和索引等。相关DDL语句如下:

  • createtable:创建一个表。

crate table CUSTOMERS(

ID bigint not null,

NAME varchar(15) not null,

AGE int,

primary key (ID)

);

crate table ORDERS(

ID bigint not null,

ORDER_NUMBER varchar(15) not null,

PRICE double precision,

CUSTOMER_ID bigint,

foreign key(CUSTOMER_ID) references CUSTOMERS(ID)

);

在创建数据库schema时,通常所有表的DDL语句都放在同一个SQL脚本文件中,必须按照先父表后子表的顺序来定义DDL语句。假如表之间的参照关系发生变化,就必须修改DDL语句的顺序,这增加了维护SQL脚本文件的难度。为了解决这一问题,可以采用另一种方式来定义外键。

crate table CUSTOMERS(

ID bigint not null,

NAME varchar(15) not null,

AGE int,

primary key (ID)

);

crate table ORDERS(

ID bigint not null,

ORDER_NUMBER varchar(15) not null,

PRICE double precision,

CUSTOMER_ID bigint,

primary key (ID)

);

alter table ORDERS add constraint FK_CUSTOMER foreign key (CUSTOMER_ID) references CUSTOMERS(ID);

  • altertable:修改一个表。
  • droptable:删除一个表,同时删除表中所有记录。

DML数据操纵语言

DML用于向数据库插入、更新或删除数据,这些操作分别对应insert、update和delete语句。

在执行这些语句时,数据库系统先进行数据完整性检查,如果这些语句违反了数据完整性,数据库系统会异常终止执行SQL语句。

DQL数据查询语言

SQL语言的核心就是数据查询语言。查询语句的语法如下:

select 目标列 from 基本表(或视图) [where 条件表达式] [group by 列名1[having 条件表达式]] [order by 列名2[asc|desc]] 

简单查询

简单SQL查询语句,其中where子句设定查询条件,order by子句设定查询结果的排序方式。

(1)查询年龄在10到50之间的客户,查询结果先按照年龄降序排列,再按照名字升序排列。

select * from customers where age between 18 and 50 order by age desc,name asc;

(2)查询名字为“Tom”、“Mike”、“Jack”的客户。

select * from customers where name in (‘Tom’,’ Mike’,’ Jack’);

(3)查询姓名的第二个字母是“a”的客户。

select * from customers where name like ‘’_a%’’;

(4)查询年龄为null的客户的名字。

select name from customers where age is null;

注意:不能用表达式age=null来比较age是否为null,因为这个表达式的值既不为true,也不为false,而是永远为null。当where子句的取值为null,select的查询语句的查询结果为空。

(5)在查询语句中为表和字段指定别名:

select name c_name,age c_age from customer c where c.id=1;

连接查询

连接查询的from子句的连接语法格式为:

from talbe1 join_type table2 [on (join_condition)] [where (query_condition)]

table1和talbe2表示参与连接操作的表,table1为左表,table2为右表。on子句设定连接条件,where子句设定查询条件,join_type表示连接类型,可分为3种:

  • 交叉连接(cross join):不带on子句,返回连接表中所有数据行的笛卡儿积。

Select * from customers,orders;

返回两张表中记录数的乘积。若customers中有5条记录,orders表中有7条记录,则结果返回35条记录。

  • 内连接(inner join):返回连接表中符合连接条件及查询条件的数据行。

显式内连接:使用inner join关键字,在on子句中设定连接条件

Select c.id,o.customer_id,c.name,o.id roder_id,order_number from customers c inner join orders o on c.id=o.customer_id;

隐式内连接:不包含inner join关键字和on关键字,在where子句中设定连接条件

Select c.id,o.customer_id,c.name,o.id order_id,order_number from customers c,orders o where c.id=o.customer_id;

  • 外连接:分为左外连接(left outer join)、右外连接(right outer join)。与内连接不同的是,外连接不仅返回连接表中符合连接条件及查询条件的数据行,也返回左表(左外连接时)或右表(右外连接时)中仅符合查询条件但不符合连接条件的数据行。

Select c.id,o.customer_id,c.name,o.id order_id,order_number from customers c left outer join orders o on c.id=o.customer_id;

以上查询语句的查询结果不仅包含符合on连接条件的数据,还包含customers左表中的其他数据行。

子查询

子查询也叫嵌套查询,是指在select子句或者where子句中又嵌入select查询语句,下面举例说明它的用法。

1)查询具有3个以上订单的客户:

select * customers c where c<=(select count(*) from orders o where c.id=o.customer_id);

2)查询名为“Mike”的客户的所有订单

select * from orders o where o.customer_id in (select id form cutomers where name=’Mike’);

3)查询没有订单的客户:

select * from customers c where 0=(select count(*) from order o where c.id=o.customer_id);

或者

select * from customers c where not exists (select * from orders o where c.id=o.customer_id);

4)查询ID为1的客户的姓名、年龄及它的所有订单的总价格:

select name,age,(select sum(price) from orders where customer_id=1) total_price from customers where id=1;

total_price是别名

也可以通过左外连接查询来完成相同的功能:

select name,age,sum(price) from customers c left outer join orders o on c.id=o.customer_id where c.id=1 group by c.id;

如果数据库不支持子查询,可以通过连接查询来完成相同的功能。事实上,所有的子查询语句都可以改写为连接查询语句。

联合查询

联合查询能够合并两条查询语句的查询结果,去掉其中的重复数据行,然后返回没有重复数据行的查询结果。联合查询使用union关键字,例如:

select * from customers where age<25 union select * from customers where age>=24;

报表查询

报表查询对数据行进行分组统计,其语法格式为:

[select …] from … [where…] [ group by … [having… ]] [ order by … ]

其中group by 子句指定按照哪些字段分组,having子句设定分组查询条件。在报表查询中可以使用以下SQL聚集函数。

count():统计记录条数

min():求最小值

max():求最大值

sum():求和

avg():求平均值

用法:

1)按照客户分组,查询每个客户的所有订单的总价格:

select c.id,c.name,sum(price) from customers c left outer join orders o on c.id=o.customer_id group by c.id;

2)按照客户分组,查询每个客户的所有订单的总价格,并且要求订单的总价格大于100:

select c.id,c.name,sum(price) from customers c left outer join orders o on c.id=o.customer_id group by c.id having(sum(price)>100);