标准SQL语言的用法_MySQL

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

 

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=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);