Sql数据库查询语言

1、概述

Sql是一种面向数据库的结构化查询语言，是符合美国国家标准化组织ANSI的一种计算机标准语言。

Sql具对数据库的操作有：增删改查，创建数据库、创建表、创建存储过程、创建视图等

RDBMS关系型数据库管理系统，是指一套数据库软件，有Mysql，DB2，Oracle等

除了Sql标准之外，各数据库软件都有自己的私有扩展。

数据库管理系统中的数据被存储在“表”的数据库对象中，表是相关数据项的集合，它由列和行组成。

Sql分为两个部分：数据库操作语言DML(Database Manipulation Language)和数据库定义语言DDL(Database Defination Language)

DML包括：增INSERT INTO、删DELETE、改UPDATE、查SELECT

DDL包括：

对数据库（创建数据库CREATE DATABASE、修改数据库ALTER DATABASE）

对数据库的表（创建数据库中的表 CREATE TABLE、更改表ALTER TABLE、删除表DROP TABLE）

对索引（创建索引CREATE INDEX、删除索引DROP INDEX）

注意：Sql对大小写不敏感！

在Sql中，文本值使用单引号'str'引起，数值不需要也不能使用引号

2、DML数据库操作语言

1、查询SELECT

SELECT的使用：

SELECT columnName1, columnName2 FROM tableName  ：返回指定列

SLELCT * FROM tableName  ：返回所有的列

返回的结果被存放在称为“结果集”的对象中。

1.DISTINCT

DISTINCT与SELECT连用，用以查询不同的值：

SELECT DISTINCT columnName1,columnName2  FROM tableName ：返回不同的值

在查询过程中，经常会出现相同的值，比如查询一列，这一列中会有相同的值；查询两列，会有两列同时相同的值。

DISTINCT就是为了在查询结果中，将那些相同的值去除，只保留一个。

注意：如果选取多列，相同与否，在于多列的值是否都满足相同，这样才能判定为相同，有一列值的不同，就属于不同，需显示出。

2.WHERE 

WHERE子句中用于规定选择的标准，即满足的先决条件。

SELECT 列名 FROM 表名 WHERE 列名 运算符 值  ：选择满足先决条件“列名 运算符 值”的值

运算符有：=、<>(或!=)、>、<、>=、<=、BETWEEN AND、LIKE

其中，BETWEEN AND是选取某个范围的值，LIKE是按某种模式搜索

3.AND和OR

AND和OR运算符在WHERE子句中使用，目的是把两个或多个条件结合起来。

SELECT 列名 FROM 表名 WHERE 列1 运算符1 值1 AND(OR) 列2 运算符2 值2 ：选择满足多个先决条件的值

也可以使用圆括号()，来组建复杂的表达式

SELECT 列名  FROM 表名  WHERE (列1 运算符1 值1  OR 列2 运算符2 值2) AND 列3 运算符3 值3

4.ORDER BY

对结果集进行排序，默认是按照升序的规则

SELECT 列名 FROM 表名 ORDER BY 列1  (ASC或 DESC), 列2  (ASC或 DESC)：按照列进行排序，如果是多列，那么先按照列1排序，在列1中有相同的值时，再按照列2排序

其中，ASC升序，DESC为降序，也可以两者都不写，采用默认排序

注意，如果按照多个列排序，每个列都需指定排序的方式，在指定列的后边加上ASC或DESC，或默认

2、插入INSERT INTO

INSERT INTO 表名 VALUES (值1, 值2, ...) ：向表中的插入一条包含所有列值的数据

INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...) ：向表中插入一条只包含指定列的值的数据

注意：在不指定列名的时候，插入的数据必须把表中所有的列的值都写明，否则出错

在mysql 中，INSERT可以不必和INTO连用，没有INTO也正确，不知其他数据库怎样的。

3、更新UPDATE

UPDATE 表名 SET 列1=值1, 列2=值2  WHERE 列3 运算符 值3 ：更具先决条件（可以是多个条件的组合），定位到的指定条目（可以是多条），更新这些数据

4、删除DELETE FROM

DELETE FROM 表名 WHERE 列 运算符 值 ：根据先决条件选定的数据，删除这些数据

DELETE * FROM 表名， 或者

DELETE FROM 表名 ：删除表中所有的数据，但不删除表，保留表的结构，也可以使用 TRUNCATE TABLE 表名，效果一样

注意：DELETE 必须与FROM连用，没有FROM出错

5、TOP

TOP子句是规定要返回的数据的数目，对于拥有千万计条目的大型表来说，TOP字句是非常重要的。

概念语法：

SELECT TOP num * FROM 表名 ：从表中返回满足条件的前num条记录，或者

SELECT TOP num PERCENT * FROM 表名：从表中返回满足条件的前num%的记录

对于概念语法，每个数据库软件的使用方法不同，

Mysql中使用 LIMIT关键字： SELECT * FROM 表名 LIMIT num 

Oracle中使用 ROWNUM关键字与WHERE连用： SELECT * FROM 表名 WHERE ROWNUM<=num

6、LIKE

LIKE关键字是为了在WHERE子句中使用，按照某个列的指定模式，搜索相应的条目

使用方法：

SELECT * FROM 表名 WHERE 列名 LIKE '模式' 

模式通常与通配符连用，通配符有%, _, [charlist], [^charlist]或[!charlist]。

例如以%使用：'%str'，以str结尾的字符串；'str%'以str开头的字符串；'%str%'包含str的字符串

使用NOT关键字，选择不符合'模式'的数据

SELECT * FROM 表名 WHERE 列名 NOT LIKE '模式'

注意：如果 列名 LIKE '模式' 中没有通配符，那么就相当于 列名='模式'

7、通配符

通配符有： % , _ , [charlist] , [^charlist]或者[!charlist]，通配符必须与LIKE连用，以某种模式查询

%表示 替代一个或多个字符；

_仅替代一个字符；例如'_addc'表示第一个字母之后是addc的字符串，或者'a_d_s'都可以。

[charlist]字符中的任意单一字符；例如‘[ABC]%’表示以'ABC'中的任意单一字符开头的字符串

[^charlist]或[!charlist]不在字符集中的任意单一字符

注意，不知为什么[charlist]通配符在mysql中不能使用，有待于查明原因。其他都可以。

8、IN

SELECT * FROM 表名 WHERE 列名 IN (值1，值2, ...)

IN关键字，给定一个集合，选择列的值在结合中的数据条目。值可以是字符串，也可以是数值，具体根据列的数据类型

9、BETWEEN AND

SELECT * FROM 表名 WHERE 列名 BETWEEN 值1 AND 值2 ：选择指定的列介于值1和值2之间的数据。 

注意，不同的数据库软件对BETWEEN的处理方式不同，有的包含值1和值2，有的不包含，有的只包含其中一个。mysql是前后都包含。

如果列的类型是数值，那么就是大于值1，小于值2的数据（必须是大于前边的，小于后边的，不能颠倒）

如果列的类型是字符串，那么就是按字母表的顺序，介于值1和值2之间的数据。（也是前后不能颠倒）

注意，'BB'是排在'B'之后的，所有如果值2是'B'，那么‘BB’就不能包含进来。

10、别名 Alias

对数据表或者列采用别名，使用 AS关键字

对表： SELECT 数据表别名.列名 FROM 表名 AS 数据表别名 WHERE 数据表别名.列名 运算符 值 ：使用数据表别名可以

对列名： SELECT 列名 AS 列别名 FROM 表名 WHERE 列名 运算符 值：在显示的时候，列的名字显示为别名，注意，在WHERE字句中不能使用列的别名

例如：SELECT po.orderid, p.firstname, p.lastname FROM persons AS p, product_orders AS po 

注意，显示的结果为 product_orders的列数与persons的列数的积

11、JOIN

JOIN关键字，目的是实现同时对两个相关表的查询，这两个表一般有一列是相关的。

在别名Alias一节中，同时查询两个表 的结果显示的是两个表乘积的显示，即其中一个表的每条都会对应另外一个表的所有显示。

那么，给两个表的查询，再加上先决条件，例如：

SELECT p.id,p.firstname, p.lastName , po.order_id FROM persons AS p, products_orders AS po WHERE p.id=po.p_id;

那么，就是在乘积表中，查找出符合先决条件的显示出来。

这种查询，可以使用JOIN关键字完成：

SELECT 表1别名.列名, 表2别名.列名 FROM 表1 AS 表1别名  JOIN 表2 AS表2别名 ON 表1别名.列名 运算符 表2别名.列名

也可以不使用别名，但是要写  表的名称.列名，会比较麻烦。

如果不写ON 及后边的条件，那么就会显示整个的乘积表的数据个数。

两个表的所有数据条目可能会有不满足先决条件的，比如表1中有几条不满足的，表2中有几条不满足的，那么如何显示这些不满足先决条件的呢？

就是使用LEFT JOIN 和 RIGHT JOIN 

LEFT JOIN

SELECT 表1别名.列名, 表2别名.列名  FROM 表1  AS 表1别名  LEFT JOIN 表2  AS 表2别名  ON 表1别名.列名 运算符 表2别名.列名

LEFT JOIN是显示表1中不满足先决条件的，显示结果中有关表2的列为NULL

RIGHT JOIN

SELECT 表1别名.列名, 表2别名.列名  FROM 表1  AS 表1别名  RIGHT JOIN 表2  AS 表2别名  ON 表1别名.列名 运算符 表2别名.列名

RIGHT JOIN是显示表2中不满足先决条件的，显示结果中有关表1的列为NULL

注意，使用LEFT/RIGHT JOIN时，必须要有ON的先决条件，否则出错。

FULL JOIN

FULL JOIN 其实是LEFT JOIN和RIGHT JOIN的集合，显示两个表中都不满足先决条件的数据项

在mysql中，不支持FULL JOIN ，而是使用LEFT JOIN 与 RIGHT JOIN 结合的方式：

SELECT 表1别名.列名, 表2别名.列名  FROM 表1  AS 表1别名  LEFT JOIN 表2  AS 表2别名  ON 表1别名.列名 运算符 表2别名.列名

UNION

SELECT 表1别名.列名, 表2别名.列名  FROM 表1  AS 表1别名  RIGHT JOIN 表2  AS 表2别名  ON 表1别名.列名 运算符 表2别名.列名

12、UNION

UNION功能是将两个SELECT语句连接起来，实现多个SELECT显示的集合。

使用UNION，每个SELECT语句选取的列数以及对应的列的数据类型应保持一致，否则出错

如果多个SELECT语句选出的结果中有相同的结果（选取多列的，每列都相同），那么只显示其中一项。

使用方法：

SELECT 列名1 FROM 表名1 UNION (ALL) SELECT 列名2 FROM 表名2

UNION ALL 显示的所有的结果，相同的结果也全部显示出来

13、SELECT INTO

SELECT INTO用于从一个表中选取数据然后插入到另一个表中，常用于创建表的备份

概念语法：

SELECT 列名 INTO 表名2 FROM 表名1 ，从表1中赋值数据到表2

但是，mysql不支持SELECT INTO 语句，mysql有两种方式实现这一功能：

当备份的表不存在，需要创建新表：

CREATE TABLE 表_备份 (SELECT * FROM 表名 )，必须要用圆括号将SELECT语句括起。

当备份文件存在时，

INSERT INTO 备份表名 SELECT * FROM 表名，已存在的备份表的结构和所选出的列的结构要保持一致。

3、数据库定义语言DDL

1、CREATE DATABASE

创建数据库：

CREATE DATABASE 数据库名

2、CREATE TABLE

创建表：

CREATE TABLE 表名 

(列名1 数据类型1 约束条件1, 

列名2 数据类型2 约束条件2，

列名3 数据类型3 约束条件3 )

创建表时，必须指明表的列以及数据类型，约束条件可以根据需求给出。

基本的数据类型有

整形：int(size)，smallint(size)，tinyint(size)

浮点型：double(size,d), decimal(size,d)

字符串： CHAR(size),VARCHAR(size)

日期：DATE

注意，可以在指定数据类型时不指定数据长度size，但是一般都要指定为好。DATE类型不需要也不能指定。

3、约束Constraints

约束，是指在定义数据表时，给指定的列加上一个约束条件，如非空、唯一、主键等。

SQL约束有：非空值NOT NULL、唯一UNIQUE、主键PRIMARY KEY、外键FOREIGN KEY、限制CHECK 、默认DEFAULT

NOT NULL

非空，位置写在列的类型的后边，被NOT NULL约束的列，在插入数据与更新数据时，原则上必须有该列的数据，也就是该列的值不能为空。

实际上，在MYSQL中，对于NOT NULL约束的列，可以插入为空的值，但是会发出警告，所以，一般情况，对于此类的列，要有数据为好。

CREATE TABLE persons_back
(
id INT(16) NOT NULL,
firstname VARCHAR(32) ,
birth DATE
);

UNIQUE

被UNIQUE约束的列，只能存在唯一的值，不能有相同的值。

如果UNIQUE同时约束多列：UNIQUE (列名1, 列名2)，那么只有多个列的值同时出现相同时，才判定为相同，不能插入，有任意其中一个列的值不同，就可以插入数据。

如果UNIQUE对每个列单独约束：UNIQUE (列名1), UNIQUE(列名2)，那么每列都不能有相同数据，与其他列无关。

UNIQUE在MYSQL中的位置，既可以放在类型的后边，也可以在列的定义完成的最后另起一行单独定义UINQUE

CREATE TABLE persons_back
(
id INT(16) NOT NULL UNIQUE,
firstname VARCHAR(32) UNIQUE,
birth DATE
);

或者

CREATE TABLE persons_back
(
id INT(16) NOT NULL ,
firstname VARCHAR(32),
birth DATE,
UNIQUE (id),
UNIQUE (firstname)
);

上述对每列单独进行约束，下面对多列同时约束：

CREATE TABLE persons_back
(
id INT(16) NOT NULL ,
firstname VARCHAR(32),
birth DATE,
UNIQUE (id,firstname)
);

其实每个UNIQUE都有一个名字，默认为列名，也可以自己指明：UNIQUE 别名 (列名)，或者 CONSTRAINT 别名 UNIQUE (列名)

CREATE TABLE persons_back
(
id INT(16) NOT NULL ,
firstname VARCHAR(32),
birth DATE,
UNIQUE uc_personsID (id,firstname)
);

或者

CREATE TABLE persons_back
(
id INT(16) NOT NULL ,
firstname VARCHAR(32),
birth DATE,
CONSTRAINT uc_personsID UNIQUE (id,firstname)
);

如果不指明别名，约束多个列以第一个列的名字为UNIQUE的名字；这样的别名方式也可以是单列

如果表已经创建，也可以通过ALTER TABLE ADD UNIQUE (列名)的方式为表的列添加UNIQUE约束

在mysql中，下面两种方式都可以：

ALTER TABLE persons_back ADD UNIQUE (id,firstname)
ALTER TABLE persons_back ADD CONSTRAINT uc_personsID UNIQUE (id,firstname)

也可以通过ALTER TABLE DROP INDEX 名字 的方式删除UNIQUE约束

ALTER TABLE persons_back DROP INDEX id

Mysql中不支持使用下边的方式，Oracle适用：

ALTER TABLE persons_back DROP CONSTRAINT uc_personsID

注意：如果UNIQUE约束时，没有指明NOT NULL，该列是可以为空值的。

PRIMARY KEY

PEIMARY KEY的使用方法和UNIQUE是相同的，不过代表的意思有些区别。

主键约束的列，不能为空值，也不能有相同的值，也就是说PRIMARY KEY 结合了NOT NULL 和 UNIQUE 两者，

另外一点，每个表中只能有一列被约束为主键，否则出错；每个表中都最好有一个主键，如果没有也没有错误

CREATE TABLE persons_back
(
id INT(16) PRIMARY KEY,
firstname VARCHAR(32),
birth DATE
);

或者

CREATE TABLE persons_back
(
id INT(16) ,
firstname VARCHAR(32),
birth DATE,
PRIMARY KEY (id)
);

musql中，主键没有别名，也没必要指明别名，删除的时候只需指明PRIMARY KEY即可，因为一个表只有一个主键。

ALTER TABLE person DROP PRIMARY KEY

PRIMARY KEY 也可以同时约束几个列，只有当所有列的值都相同时，才能判定两项数据相同，不能插入。但是多个列可以同时为NULL，但也只能存在一条，否则就是相同的。

FOREIGN KEY

外键，目的是使两个表产生关联，将表B的列某列指定为外键，关联表A的某列。

外键可以防止非法数据插入到外键列，因为他必须是指向那个外键关联的表的值之一。

CREATE TABLE persons
(
id INT(16) PRIMARY KEY,
firstname VARCHAR(32) NOT NULL,
lastname VARCHAR(32) NOT NULL,
city VARCHAR(32)
)
CREATE TABLE products_orders
(
order_id INT(16) PRIMARY KEY,
order_name VARCHAR(32),
p_id INT(16),
FOREIGN KEY(p_id) REFERENCES persons(id)
)

mysql中，外键的定义不能之间在列类型的后边，需单独一行定义，否则出错。

外键的命名方式与UNIQUE类似，

FOREIGN KEY ns (p_id) REFERENCES persons(id)

或者

CONSTRAINT ns FOREIGN KEY(p_id) REFERENCES persons(id)

不指名别名的话，默认就是列名

删除

ALTER TABLE products_orders DROP FOREIGN KEY p_id

注意：不知为什么在创建外键时，写的是FOREIGN KEY，mysql实现时，却成了KEY，删除也是这样，为什么？（待解决）
且在Mysql中使用MyISAM引擎可以创建外键，担不起作用；使用InnoDB引擎不能创建外键，不知为什么？

网上查资料，很多说在开发中能不用外键就不用，因为相关表的操作相当于事务，有其对于大数据量的并发操作，性能提不上来。

CHECK

CHECK约束是对表中列的值进行范围的限定，目前mysql不支持CHECK字句，但也不会报错，会自动忽略。

例如限定整型值大于0： CHECK(id>0)

对多个列限制，使用AND连接，因为mysql不支持，就不多说了。

CREATE TABLE persons_back
(
id INT(16),
firstname VARCHAR(32),
birth DATE,
p_id INT(16),
CHECK (id>0 AND firstname IN('my'))
) ;

DEFAULT

DEFAULT是为列自动添加默认值，在插入的时候可以不必向该列插入数据，该列存有默认值，也可以插入数据进行更新。

使用方法，直接在列的类别后边添加 DEFAULT 'str'或者DEFAULT 数值

CREATE TABLE persons_back
(
id INT(16),
firstname VARCHAR(32) DEFAULT 'my',
birth DATE,
p_id INT(16)
) ;

删除：ALTER TABLE 表名 ALTER 列名 DROP DEFAULT

ALTER TABLE persons_back ALTER firstname DROP DEFAULT

注意是先对表ALTER，在对列ALTER，然后DROP DEFAULT

4、索引INDEX

索引是什么？是一种数据结构或算法，帮助快速在数据库中查找数据，只能被用来搜索和查询

使用：创建一个普通索引，允许创建索引的行有不同的值。

CREATE INDEX 索引名 ON 表名 (列名1，列名2)

删除索引：

DROP INDEX 索引名 ON 表名

注意：更新一个包含索引的表比更新一个不包含索引的表需要更多的时间，因为索引本身也要更新。

也可以使用ALTER 进行添加、删除索引

ALTER TABLE 表名 ADD INDEX 索引名 (列名1，列名2)

ALTER TABLE 表名 DROP INDEX 索引名

索引分为普通索引、唯一索引和聚焦索引三类

1. 唯一索引UNIQUE INDEX：添加索引的列中，不能存在两个相同的数据。

CREATE UNIQUE INDEX 索引名  ON 表名 (列名1，列名2)，删除时不必指明也不能写DROP UNIQUE INDEX ，普通删除即可。

2. 主键索引：是唯一索引的一种特殊类型

注意，主键约束自动建立主键索引，唯一约束自动建立同名唯一索引

3.聚焦索引：表中行的物理顺序与键值的逻辑属性相同

5、DROP

DROP用于删除数据库、删除表、删除索引、删除约束

DROP DATABASE 数据库名

DROP TABLE 表名

DROP INDEX 索引名 ON 表名

mysql中，感觉约束和索引总是分不开，删除一个另一个也会删除，有待于求证

6、ALTER

ALTER可以在已有的表中添加、删除和修改列

添加列：

 ALTER TABLE 表名 ADD 列名 列类型 约束

删除列：

ALTER TABLE 表名 DROP 列名

或

ALTER TABLE 表名  DROP COLUMN 列名

修改列

删除，添加约束、索引等

修改列的类型

概念语法：ALTER TABLE 表名 ALTER 列名 类型

但在mysql中，使用MODIFY

ALTER TABLE 表名 MODIFY 列名 类型

7、自动增加

在每次插入新数据时，自动增加主键数据，插入数据不必指定主键数据。

不同的数据库中，该功能实现的方法不一样，在mysql中：

在主键列的后边加上 AUTO_INCREMENT，但必须是在定义主键的前提下，该关键字也必须写在主键列后，否则出错。

CREATE TABLE persons (
  id INT(16) NOT NULL AUTO_INCREMENT,
  firstname VARCHAR(32) NOT NULL,
  lastname VARCHAR(32) NOT NULL,
  city VARCHAR(32) DEFAULT NULL,
  email INT(11) DEFAULT NULL,
  PRIMARY KEY (id)
)

4、函数function

在sql中可以使用一些函数，对选择的数据进行运算或筛选；

sql中有很多内建的函数，用于计算和计数

这些函数分为合计函数和Scalar函数

合计函数：面向一系列的值，并返回单一值，例如AVG(),SUM()

Scalar函数：基于输入的单一值，返回某个单一值，例如 UCASE()，使该列的显示为大写，注意，数据库中的值不会发生改变

合计函数

AVG()

求某一列的平均值，返回浮点型的数据

SELECT AVG(列名) FROM 表名 WHERE 限制条件，AVG()中不同包含多列，可以AVG(列1), AVG(列2)的形式

使用这样SELECT的语句，返回的值为AVG的值，可以继续作为限制条件：

SELECT * FROM persons WHERE num > (SELECT AVG(num) AS number FROM persons)

COUNT()

返回匹配条件的数据的行数

SELECT COUNT(列 ) FROM 表：返回指定列的数据的数目，NULL不算在内，但是空字符串是计入的。

SELECT COUNT(*) FROM 表 ：返回整个表的数据行数

SELECT COUNT(DISTINCT 列 ) FROM 表：返回列中不相同值的数目

FIRST()

返回表中第一行数据指定列的数据，但是mysql不支持该函数，而是通过LIMIT关键字完成

SELECT city  FROM persons LIMIT 1

LAST()

mysql也不支持last函数，该函数是取得表中指定列中最后一行的数据，mysql可以曲线实现，比如先获取最大的id，再根据id 获取最后一列数据

MAX  MIN

MAX和MIN分别返回指定列最大值和最小值的数据，对于数值，是大小；对于字符串，是排列前后，MIN是排列靠前的，其中，数字字符串比字母字符串要靠前

SUM

求和函数，一般使用GROUP BY指明分组求和

对整个表的某列求和

SELECT SUM(列名) FROM table 

对表进行分组，然后对某列按照组求和

SELECT 列1 , SUM(列2) FROM table GROUP BY 列1

对表按照列1分组后，再按照每组求和，当然也可以在选择列时不写列1，在GROUP BY中使用列1

GROUP BY

对表按照一个列或多个列进行分组，一般和合计函数连用

SELECT 列1 , SUM(列2) FROM table GROUP BY 列1

如果去掉GROUP BY，那么列1按照原表显示，SUM列每行都是整个列2的SUM值

HAVING

Having关键字的目的是给合计函数的结果做选择，前边限定选择条件时是使用WHERE关键字，但是WHERE不能与合计函数连用，所以，只能使用Having与合计函数连用，作为选择的限制前提条件

SELECT 列1, SUM(列2) FROM table WHERE 先决条件  HAVING SUM(列2)>数值1

分组选择出，组的求和大于数值1的组