代码改变世界

Sql数据库查询语言

2017-07-01 10:56  tlnshuju  阅读(970)  评论(0)  编辑  收藏  举报

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 值1AND(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中使用LIMITkeyword：SELECT * FROM 表名 LIMIT num 
Oracle中使用ROWNUMkeyword与WHERE连用：SELECT * FROM 表名WHERE ROWNUM<=num


6、LIKE


LIKEkeyword是为了在WHERE子句中使用。依照某个列的指定模式。搜索对应的条目
用法：
SELECT * FROM 表名 WHERE 列名 LIKE '模式' 
模式通常与通配符连用，通配符有%, _, [charlist], [^charlist]或[!charlist]。
比如以%使用：'%str'，以str结尾的字符串；'str%'以str开头的字符串；'%str%'包括str的字符串
使用NOTkeyword，选择不符合'模式'的数据
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, ...)
INkeyword，给定一个集合，选择列的值在结合中的数据条目。值能够是字符串，也能够是数值，详细依据列的数据类型

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


对数据表或者列採用别名，使用ASkeyword
对表：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


JOINkeyword。目的是实现同一时候对两个相关表的查询，这两个表一般有一列是相关的。
在别名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;
那么，就是在乘积表中。查找出符合先决条件的显示出来。
这样的查询。能够使用JOINkeyword完毕：
SELECT 表1别名.列名, 表2别名.列名 FROM 表1 AS 表1别名 JOIN 表2AS表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 列名2FROM 表名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，但必须是在定义主键的前提下。该keyword也必须写在主键列后，否则出错。
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不支持该函数，而是通过LIMITkeyword完毕
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

Havingkeyword的目的是给合计函数的结果做选择，前边限定选择条件时是使用WHEREkeyword。可是WHERE不能与合计函数连用，所以，仅仅能使用Having与合计函数连用，作为选择的限制前提条件
SELECT 列1, SUM(列2) FROM table WHERE 先决条件  HAVING SUM(列2)>数值1
分组选择出，组的求和大于数值1的组