连接mongoDB数据库

./bin/mongo 127.0.0.1:12345

显示所有的数据库

show dbs

切换数据库

use test

mongoDB中不需要直接创建数据库.

删除数据库

先切换数据库

再删除数据库

db.dropDatabase()

插入记录

db.test_collection.insert({x:1})

_id不能重复

循环插入多条记录

for (i = 3; i< 100; i++) db.test_collection.insert({x:i})

结果:

查看某个数据库所有的表

show collections

切换数据库

查看test数据库的所有的表

查询集合中的记录

db.test_collection.find()

_id是全局唯一的,毕竟是分布式数据库,id要不能重复.

有条件的查询

在find函数中传入json对象:

db.test_collection.find({x:1})

查询记录条数

db.test_collection.find().count()

查询的时候跳过多少条记录,限制返回多少条记录,进行排序

db.test_collection.find().skip(3).limit(2).sort({x:1})

更新记录

db.test_collection.update({x:1},{x:999})

默认的更新是全部更新

下面是具体操作的例子,你在这个例子中可以发现,{x:100,y:100,z:100}被覆盖为{y:999}:

局部更新

db.test_collection.update({z:100},{$set:{y:999}})

$set部分更新

更新不存在的记录不会有任何效果

上面的图片更新了{y:200}这条不存在的记录,结果显示没有更新,也不会插入新的记录.

更新不存在的记录时插入记录

db.test_collection.update({y:200},{y:666},true)

请注意update的最后一个参数,为true时,更新不存在的记录会自动插入.

传统的关系型数据库在应付这些已经显得力不从心，并暴露了许多难以克服的问题。

由此，各种各样的 NoSQL（Not Only SQL）数据库作为传统关系型数据的一个有力补充得到迅猛发展。

本文将分析传统数据库存在的一些问题，以及几大类 NoSQL 如何解决这些问题，希望给大家提供一些在不同业务场景下存储技术选型方面的参考。

传统数据库的缺点

传统的数据库有如下几个缺点：

大数据场景下 I/O 较高，因为数据是按行存储，即使只针对其中某一列进行运算，关系型数据库也会将整行数据从存储设备中读入内存，导致 I/O 较高。存储的是行记录，无法存储数据结构。表结构 Schema 扩展不方便，如要修改表结构，需要执行 DDL(data definition language)，语句修改，修改期间会导致锁表，部分服务不可用。全文搜索功能较弱，关系型数据库下只能够进行子字符串的匹配查询，当表的数据逐渐变大的时候，like 查询的匹配会非常慢，即使在有索引的情况下。况且关系型数据库也不应该对文本字段进行索引。存储和处理复杂关系型数据功能较弱，许多应用程序需要了解和导航高度连接数据之间的关系，才能启用社交应用程序、推荐引擎、欺诈检测、知识图谱、生命科学和 IT/网络等用例。

然而传统的关系数据库并不善于处理数据点之间的关系。它们的表格数据模型和严格的模式使它们很难添加新的或不同种类的关联信息。

NoSQL 解决方案

NoSQL，泛指非关系型的数据库，可以理解为 SQL 的一个有力补充。

在 NoSQL 许多方面性能大大优于非关系型数据库的同时，往往也伴随一些特性的缺失，比较常见的是事务库事务功能的缺失。

数据库事务正确执行的四个基本要素 ACID 如下：

下面介绍 5 大类 NoSQL 数据针对传统关系型数据库的缺点和提供的解决方案：

列式数据库

列式数据库是以列相关存储架构进行数据存储的数据库，主要适合于批量数据处理和即时查询。

相对应的是行式数据库，数据以行相关的存储体系架构进行空间分配，主要适合于小批量的数据处理，常用于联机事务型数据处理。

基于列式数据库的列列存储特性，可以解决某些特定场景下关系型数据库 I/O 较高的问题。

基本原理

传统关系型数据库是按照行来存储数据库，称为“行式数据库”，而列式数据库是按照列来存储数据。

将表放入存储系统中有两种方法，而我们绝大部分是采用行存储的。行存储法是将各行放入连续的物理位置，这很像传统的记录和文件系统。

列存储法是将数据按照列存储到数据库中，与行存储类似。下图是两种存储方法的图形化解释：

常见列式数据库

HBase：是一个开源的非关系型分布式数据库（NoSQL），它参考了谷歌的 BigTable 建模，实现的编程语言为 Java。

它是 Apache 软件基金会的 Hadoop 项目的一部分，运行于 HDFS 文件系统之上，为 Hadoop 提供类似于 BigTable 规模的服务。因此，它可以容错地存储海量稀疏的数据。

BigTable：是一种压缩的、高性能的、高可扩展性的，基于 Google 文件系统（Google File System，GFS）的数据存储系统，用于存储大规模结构化数据，适用于云端计算。

相关特性

优点如下：

高效的储存空间利用率：列式数据库由于其针对不同列的数据特征而发明的不同算法使其往往有比行式数据库高的多的压缩率。

普通的行式数据库一般压缩率在 3：1 到 5：1 左右，而列式数据库的压缩率一般在 8：1 到 30：1 左右。

比较常见的，通过字典表压缩数据： 下面中才是那张表本来的样子。经过字典表进行数据压缩后，表中的字符串才都变成数字了。

正因为每个字符串在字典表里只出现一次了，所以达到了压缩的目的(有点像规范化和非规范化 Normalize 和 Denomalize)。

查询效率高：读取多条数据的同一列效率高，因为这些列都是存储在一起的，一次磁盘操作可以把数据的指定列全部读取到内存中。

下图通过一条查询的执行过程说明列式存储(以及数据压缩)的优点。

执行步骤如下：

去字典表里找到字符串对应数字(只进行一次字符串比较)。用数字去列表里匹配，匹配上的位置设为 1。把不同列的匹配结果进行位运算得到符合所有条件的记录下标。使用这个下标组装出最终的结果集。

列式数据库还适合做聚合操作，适合大量的数据而不是小数据。

缺点如下：

不适合扫描小量数据。不适合随机的更新。不适合做含有删除和更新的实时操作。单行的数据是 ACID 的，多行的事务时，不支持事务的正常回滚，支持 I(Isolation)隔离性(事务串行提交)，D(Durability)持久性，不能保证 A(Atomicity)原子性， C(Consistency)一致性。

使用场景

以 HBase 为例说明：

大数据量（100s TB级数据），且有快速随机访问的需求。写密集型应用，每天写入量巨大，而相对读数量较小的应用，比如 IM 的历史消息，游戏的日志等等。不需要复杂查询条件来查询数据的应用，HBase 只支持基于 rowkey 的查询，对于 HBase 来说，单条记录或者小范围的查询是可以接受的。

大范围的查询由于分布式的原因，可能在性能上有点影响，HBase 不适用于有 join，多级索引，表关系复杂的数据模型。

对性能和可靠性要求非常高的应用，由于 HBase 本身没有单点故障，可用性非常高。数据量较大，而且增长量无法预估的应用，需要进行优雅的数据扩展的 HBase 支持在线扩展，即使在一段时间内数据量呈井喷式增长，也可以通过 HBase 横向扩展来满足功能。存储结构化和半结构化的数据。

K-V 数据库

指的是使用键值(key-value)存储的数据库，其数据按照键值对的形式进行组织、索引和存储。

K-V 存储非常适合不涉及过多数据关系业务关系的数据，同时能有效减少读写磁盘的次数，比 SQL 数据库存储拥有更好的读写性能，能够解决关系型数据库无法存储数据结构的问题。

常见 K-V 数据库

Redis：是一个使用 ANSI C 编写的开源、支持网络、基于内存、可选持久性的键值对存储数据库。

从 2015 年 6 月开始，Redis 的开发由 Redis Labs 赞助，而 2013 年 5 月至 2015 年 6 月期间，其开发由 Pivotal 赞助。

在 2013 年 5 月之前，其开发由 VMware 赞助。根据月度排行网站 DB-Engines 的数据显示，Redis 是最流行的键值对存储数据库。

Cassandra：Apache Cassandra（社区内一般简称为C*）是一套开源分布式 NoSQL 数据库系统。

它最初由 Facebook 开发，用于储存收件箱等简单格式数据，集 Google BigTable 的数据模型与 Amazon Dynamo 的完全分布式架构于一身。

Facebook 于 2008 将 Cassandra 开源，此后，由于 Cassandra 良好的可扩展性和性能。

它被 Apple，Comcas，Instagram，Spotify，eBay，Rackspace，Netflix 等知名网站所采用，成为了一种流行的分布式结构化数据存储方案。

LevelDB：是一个由 Google 公司所研发的键／值对（Key/Value Pair）嵌入式数据库管理系统编程库， 以开源的 BSD 许可证发布。

相关特性

以 Redis 为例，K-V 数据库优点如下：

性能极高：Redis 能支持超过 10W 的 TPS。丰富的数据类型：Redis 支持包括 String，Hash，List，Set，Sorted Set，Bitmap 和 Hyperloglog。丰富的特性：Redis 还支持 publish/subscribe，通知，key 过期等等特性。

缺点如下：

针对 ACID，Redis 事务不能支持原子性和持久性(A 和 D)，只支持隔离性和一致性(I 和 C) 。

特别说明一下，这里所说的无法保证原子性，是针对 Redis 的事务操作，因为事务是不支持回滚（roll back），而因为 Redis 的单线程模型，Redis 的普通操作是原子性的。

大部分业务不需要严格遵循 ACID 原则，例如游戏实时排行榜，粉丝关注等场景，即使部分数据持久化失败，其实业务影响也非常小。因此在设计方案时，需要根据业务特征和要求来做选择。

使用场景

适用场景：

储存用户信息(比如会话)、配置文件、参数、购物车等等。这些信息一般都和 ID（键）挂钩。

不适用场景：

需要通过值来查询，而不是键来查询。Key-Value 数据库中根本没有通过值查询的途径。需要储存数据之间的关系。在 Key-Value 数据库中不能通过两个或以上的键来关联数据。需要事务的支持。在 Key-Value 数据库中故障产生时不可以进行回滚。

文档数据库

文档数据库（也称为文档型数据库）是旨在将半结构化数据存储为文档的一种数据库。文档数据库通常以 JSON 或 XML 格式存储数据。

由于文档数据库的 no-schema 特性，可以存储和读取任意数据。

由于使用的数据格式是 JSON 或者 BSON，因为 JSON 数据是自描述的，无需在使用前定义字段，读取一个 JSON 中不存在的字段也不会导致 SQL 那样的语法错误，可以解决关系型数据库表结构 Schema 扩展不方便的问题。

常见文档数据库

MongoDB：是一种面向文档的数据库管理系统，由 C++ 撰写而成，以此来解决应用程序开发社区中的大量现实问题。2007 年 10 月，MongoDB 由 10gen 团队所发展。2009 年 2 月首度推出。

CouchDB：Apache CouchDB 是一个开源数据库，专注于易用性和成为"完全拥抱 Web 的数据库"。

它是一个使用 JSON 作为存储格式，JavaScript 作为查询语言，MapReduce 和 HTTP 作为 API 的 NoSQL 数据库。

其中一个显著的功能就是多主复制。CouchDB 的第一个版本发布在 2005 年，在 2008 年成为了 Apache 的项目。

相关特性

以 MongoDB 为例进行说明，文档数据库优点如下：

新增字段简单，无需像关系型数据库一样先执行 DDL 语句修改表结构，程序代码直接读写即可。容易兼容历史数据，对于历史数据，即使没有新增的字段，也不会导致错误，只会返回空值，此时代码兼容处理即可。容易存储复杂数据，JSON 是一种强大的描述语言，能够描述复杂的数据结构。

相比传统关系型数据库，文档数据库的缺点主要是对多条数据记录的事务支持较弱，具体体现如下：

Atomicity(原子性)，仅支持单行/文档级原子性，不支持多行、多文档、多语句原子性。Solation(隔离性)，隔离级别仅支持已提交读（Read committed）级别，可能导致不可重复读，幻读的问题。不支持复杂查询，例如 join 查询，如果需要 join 查询，需要多次操作数据库。

MongonDB 还支持多文档事务的 Consistency(一致性)和 Durability(持久性)，虽然官方宣布 MongoDB 将在 4.0 版本中正式推出多文档 ACID 事务支持，最后落地情况还有待见证。

使用场景

适用场景：

数据量很大或者未来会变得很大。表结构不明确，且字段在不断增加，例如内容管理系统，信息管理系统。

不适用场景：

在不同的文档上需要添加事务。Document-Oriented 数据库并不支持文档间的事务。多个文档之间需要复杂查询，例如 join。

全文搜索引擎

传统关系型数据库主要通过索引来达到快速查询的目的，在全文搜索的业务下，索引也无能为力，主要体现在：

全文搜索的条件可以随意排列组合，如果通过索引来满足，则索引的数量非常多。全文搜索的模糊匹配方式，索引无法满足，只能用 like 查询，而 like 查询是整表扫描，效率非常低。

而全文搜索引擎的出现，正是解决关系型数据库全文搜索功能较弱的问题。

基本原理

全文搜索引擎的技术原理称为“倒排索引”（inverted index），是一种索引方法，其基本原理是建立单词到文档的索引。与之相对的是“正排索引”，其基本原理是建立文档到单词的索引。

现在有如下文档集合：

正排索引得到索引如下：

由上可见，正排索引适用于根据文档名称查询文档内容。简单的倒排索引如下：

带有单词频率信息的倒排索引如下：

由上可见，倒排索引适用于根据关键词来查询文档内容。

常见全文搜索引擎

Elasticsearch：是一个基于 Lucene 的搜索引擎。它提供了一个分布式，多租户，能够全文搜索与发动机 HTTP Web 界面和无架构 JSON 文件。

Elasticsearch 是用 Java 开发的，并根据 Apache License 的条款作为开源发布。

根据 DB-Engines 排名，Elasticsearch 是最受欢迎的企业搜索引擎，后面是基于 Lucene 的 Apache Solr。

Solr：是 Apache Lucene 项目的开源企业搜索平台。其主要功能包括全文检索、命中标示、分面搜索、动态聚类、数据库集成，以及富文本（如 Word、PDF）的处理。Solr 是高度可扩展的，并提供了分布式搜索和索引复制。

相关特性

以 Elasticsearch 为例，全文搜索引擎优点如下：

查询效率高，对海量数据进行近实时的处理。可扩展性，基于集群环境可以方便横向扩展，可以承载 PB 级数据。高可用，Elasticsearch 集群弹性，他们将发现新的或失败的节点，重组和重新平衡数据，确保数据是安全的和可访问的。

缺点如下：

ACID 支持不足，单一文档的数据是 ACID 的，包含多个文档的事务时不支持事务的正常回滚，支持 I(Isolation)隔离性（基于乐观锁机制的），D(Durability)持久�...

自增长是auto_increment

drop table 表名 删除表

show create table 表名 可以看出该表的结构和看数据库结构类似

字段类型

sql插入语句规则

图片来自培训机构

插入数据在dos乱码问题的解决

将utf8改成gbk就ok，但是这个改变的是系统文件，不好，还有一种方式是

set names gbk;

使用这种方式很好，不会涉及系统文件

删除

这个可以看出，这个在事务内删除，删除成功了，但是后来回滚了事务，删除的内容又回来了

这个可以看出，即使回滚了，但是删除的数据还是回不来了，truncate是删除这个表，在重新创建一张，所以在插入数据的时候，这个表的自增长主键又从1开始了，和新表一样

查询

查询的时候可以给表或者字段设置别名，使用关键字as

select * from tsuiau as student这就相当于给tsuiau设置了别名student那么下面我们查询tsuiau表的时候可以直接使用studentselect * from student

distinct用于去除重复数据select distinct （字段） from 表名

条件查询

图片来自培训机构

查询之后，查寻结果排序，默认升序

聚合函数

分组查询group by 字段

根据某个字段分组，字段一样的为一组

上面是根据cid进行分组，cid一样的为一组，然后使用聚合函数统计每一组中的个数

where和having后面都是条件，二者区别是where用于分组前，having用于分组后，这几个的执行顺序是where-》group by-》having-》order by，where是分组前的条件，having是分组后的条件，分组后的条件可以使用分完组之后的查询结果的内容

顺序是这样的

分页查询limit

select * from emp limit 0， 5 从第一行开始查，一共查5行select * from emp limit 8， 5 从第9行开始查，一共查5行select * from emp limit 12, 5 从第11行开始查，一共查5行

行数是从0开始算的，0算第一行

假如规定每页有10行记录，那么如果查询第三页所有记录这个语句怎么写？

select * from emp limit 20，10；

这个20是这样求的（查询的第几页-1）*每页记录数=（3-1）*10=20

每天分享编程知识，欢迎关注

在SQL查询结果集中，记录的顺序是按它们在表中的顺序进行排列的，可以使用ORDER BY子句对查询结果按照给定的字段值重新进行排序。排序支持升序和降序，升序按照从低到高或者从小到大，降序按照从高到低或者从大到小。在ORDER BY子句后面添加关键字ASC可以让返回的记录按升序排序，添加关键字DESC可以让返回的记录按降序排序，如果省略ASC（升序）或DESC（降序），系统则默认为升序。需要注意的是关键字ASC（升序）或DESC（降序）只能单独使用，不能同时使用。

ORDER BY子句后面是待排序的字段。若字段的值是数值，进行ASC（升序）排序时，记录按照字段的值从小到大进行排序。进行DESC（降序）排序时，记录按照字段的值从大到小进行排序；若字段的值是字符串时，排序会按照字典顺序进行。

例1：查询mooc数据库的course表，查询返回的结果集按照价格字段升序排序。

例1要求按照价格字段升序排序，可以使用ORDER BY子句对查询返回的结果集排序，如果没有WHERE子句，ORDER BY子句可以直接添加在FROM子句后面。在查询窗口输入下面的SQL语句。

select * FROM course ORDER BY price ASC

在上面的SQL语句中将返回按照price字段值升序排序的课程记录。SQL查询结果如下图所示。

例2：查询mooc数据库的course表，查询返回的结果集按照价格字段降序排序。

查询内容同例1相同，排序要求按照价格字段的值降序排序。在查询窗口输入下面的SQL语句。

select * FROM course ORDER BY price DESC

在上面的SQL语句中将返回按照price字段值降序排序的课程记录。SQL查询结果如下图所示。

例3：查询mooc数据库的course表，查询返回的结果集按照课程名称排序。

例3要求按照课程名称字段进行排序，课程名称字段的值是字符串，排序规则是字典顺序。在查询窗口输入下面的SQL语句。

select * FROM course ORDER BY name

在上面的SQL语句中，ORDER BY子句name字段后面没有添加排序关键字，MySQL默认为升序排序。SQL查询结果如下图所示。

例4：查询mooc数据库的course表，查询返回的结果集按照课程名称降序排序。

查询内容同例3相同，排序要求按照课程名称字段的值降序排序。在查询窗口输入下面的SQL语句。

select * FROM course ORDER BY name DESC

在上面的SQL语句中，在ORDER BY子句name字段后面添加DESC关键字，返回结果集将以字典顺序降序排序。SQL查询结果如下图所示。

例5：查询mooc数据库的course表，查询课程类别为机器学习的课程记录，并按照课程价格降序排序。

查询内容要求类别为机器学习的课程并按照价格降序排序。在查询窗口输入下面的SQL语句。

select * FROM course WHERE category = "机器学习" ORDER BY price DESC

在上面的SQL语句中，首先查询出类别为“机器学习”的课程记录，然后再按照价格字段降序排序。SQL查询结果如下图所示。

例6：查询mooc数据库的course表，查询所有课程记录，并按照课程名称、价格字段升序排序。

查询内容要求按课程名称和价格两个字段值进行排序，对于多个字段排序，MySQL会先对第一个字段进行排序，然后在排序的基础上再对后面的字段进行排序，依次类推，字段与字段之间用英文逗号分隔。在查询窗口输入下面的SQL语句。

select * FROM course ORDER BY name,price

在上面的SQL语句中，SQL会先按照name排序，然后再按照price排序，前提是先要满足name排序。SQL查询结果如下图所示。

常用的SQL：相对简单，今天的东西就不进行截图啦

(1)as给列起一个别名:select username as 用户名 from t_user;(这里的as关键字可以省略)

(2)distinct去掉重复的行：select distinct username from t_user;

3)使用运算符：select age+10 from t_user;(给每个人的年龄都加10岁)(原数据不会改变，只是显示改变)

(4)in匹配集合中的任意值：select * from t_user where username in('A','B');

(5)like模糊查询：%匹配0个或多个任意字符，_匹配1个任意字符。

select * from t_user where username like '%少南%';

(6)null判断某列为空：select * from t_user where sex is null;（找到sex为空的这一列）

这里用is，不能用=，如果要返回不为null的记录就可以用is not null)

(7)order by排序：ASC: 升序排列（可以省略）,DESC: 降序排列

升序:select u.userid,u.username from t_user u order by u.userid;

降序:select u.userid,u.username from t_user u order by u.userid desc;

(8)系统函数(对一组数据进行处理，返回一个值):

AVG–求平均值，COUNT–统计记录数，MAX–最大值，MIN–最小值，SUM–求和

<1>返回最小和最大的用户编号： select min(userid),max(userid) from t_user;

<2>返回总记录数： select count(*) from t_user;

<3>返回某个字段不为空的记录数： select count(sex) from t_user;

<4>返回不为空且不重复的记录数： select count(distinct sex) from t_user;

(9)group by分组(分组了就不能直接返回*，经常和聚合函数count()一起使用):

<1>单字段，按部门号分组，并统计每部门人数:select deptno,count(*) from emp group by deptno;

<2>多字段，按性别和年龄分组: select sex,age,count(*) from t_user group by sex,age;

group by有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面。

(10)having过滤分组：select username from t_user group by username having count(*) >=2;

(11)between 某范围之间：select * from t_user where id between 20 and 100;

()Concat 拼接：select concat（a,b） from t_user

以下SQL函数全部借助dual辅助表完成，如有不懂可直接复制下列sql运行查看结果

(13)Length计算长度：select length('##123##') len from dual;

(14)LTRIM,RTRIM,TRIM【多用于处理空格】

LTRIM:左删除----left

RTRIM:右删除----right

TRIM:删除串两边的字符

select length('123') len1, length(ltrim(' 123 ')) lentrim from dual;

select length(' 123 ') len1, length(rtrim(' 123 ')) lentrim from dual;

select length(' 123 ') len1, length(trim(' 123 ')) lentrim from dual;

(15)TO_CHAR 是把日期或数字转换为字符串

select sysdate from dual;

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

select to_char(123,'9999.00') from dual; （把123以9999.00的格式显示）

select to_char(12333,'$99,999.99') from dual;

select to_char(123334444,'$99,999.99') from dual;

(16)substr 截取函数

select substr('12345',2,3) from dual;(截取从第2个字符开始的3个字符)

select substr('123456789',-6) from dual;(截取后6位)

(17)abs 绝对值

select abs(-5) from dual; (返回值为5)

select abs(5.5) from dual;(返回值为5.5)

(18)Round 函数 (四舍五入)

select round(123.123) from dual;

select round(123.8) from dual;

(19)trunc 取整【 截掉小数点后值，不会四舍五入】

select 3/2 from dual;

select trunc(3/2) from dual;

(20)Replace替换函数

select replace('abc','b','######') from dual;

(21)lpad [左添充] rpad [右填充](用于控制输出格式)

select lpad('func',7,'=') s1, rpad('func',7,'-') s2 from dual;

1、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

2、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

4、尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10

union all

select id from t where num=20

5、下面的查询也将导致全表扫描：(不能前置百分号)

select id from t where name like ‘%c%’

若要提高效率，可以考虑全文检索。

6、in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

7、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

9、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)=’abc’–name以abc开头的id

select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id

应改为:

select id from t where name like ‘abc%’

select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致。

12、不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(…)

13、很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

14、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20、尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21、避免频繁创建和删除临时表，以减少系统表资源的消耗。

22、临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使 用导出表。

23、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26、使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27、与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

30、尽量避免大事务操作，提高系统并发能力。

SQL 简介

关注小编分享更多教程

SQL 是用于访问和处理数据库的标准的计算机语言。

SQL 是什么？

SQL，指结构化查询语言，全称是 Structured Query Language。

SQL 让您可以访问和处理数据库。

SQL 是一种 ANSI（American National Standards Institute 美国国家标准化组织）标准的计算机语言。

SQL 能做什么？

SQL 面向数据库执行查询

SQL 可从数据库取回数据

SQL 可在数据库中插入新的记录

SQL 可更新数据库中的数据

SQL 可从数据库删除记录

SQL 可创建新数据库

SQL 可在数据库中创建新表

SQL 可在数据库中创建存储过程

SQL 可在数据库中创建视图

SQL 可以设置表、存储过程和视图的权限

SQL 是一种标准 - 但是...

虽然 SQL 是一门 ANSI（American National Standards Institute 美国国家标准化组织）标准的计算机语言，但是仍然存在着多种不同版本的 SQL 语言。

然而，为了与 ANSI 标准相兼容，它们必须以相似的方式共同地来支持一些主要的命令（比如 SELECT、UPDATE、DELETE、INSERT、WHERE 等等）。

在您的网站中使用 SQL

要创建一个显示数据库中数据的网站，您需要：

RDBMS 数据库程序（比如 MS Access、SQL Server、MySQL）

使用服务器端脚本语言，比如 PHP 或 ASP

使用 SQL 来获取您想要的数据

使用 HTML / CSS

RDBMS

RDBMS 指关系型数据库管理系统，全称 Relational Database Management System。

RDBMS 是 SQL 的基础，同样也是所有现代数据库系统的基础，比如 MS SQL Server、IBM DB2、Oracle、MySQL 以及 Microsoft Access。

RDBMS 中的数据存储在被称为表的数据库对象中。

表是相关的数据项的集合，它由列和行组成。

SQL 语法

数据库表

一个数据库通常包含一个或多个表。每个表由一个名字标识（例如:"Websites"）,表包含带有数据的记录（行）。

在本教程中，我们在 MySQL 的 RUNOOB 数据库中创建了 Websites 表，用于存储网站记录。

我们可以通过以下命令查看 "Websites" 表的数据：

mysql> use RUNOOB;Database changedmysql> set names utf8;Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM Websites;+----+--------------+---------------------------+-------+---------+| id | name | url | alexa | country |+----+--------------+---------------------------+-------+---------+| 1 | Google | https://google.cm/ | 1 | USA || 2 | 淘宝 | https://taobao/ | 13 | CN || 3 | 头条 | http://toutiao/ | 4689 | CN || 4 | 微博 | http://weibo/ | 20 | CN || 5 | Facebook | https://facebook/ | 3 | USA |+----+--------------+---------------------------+-------+---------+5 rows in set (0.01 sec)

解析

use RUNOOB; 命令用于选择数据库。

set names utf8; 命令用于设置使用的字符集。

SELECT * FROM Websites; 读取数据表的信息。

上面的表包含五条记录（每一条对应一个网站信息）和5个列（id、name、url、alexa 和country）。

SQL 语句

您需要在数据库上执行的大部分工作都由 SQL 语句完成。

下面的 SQL 语句从 "Websites" 表中选取所有记录：

实例

SELECT * FROMWebsites;

在本教程中，我们将为您讲解各种不同的 SQL 语句。

请记住...

SQL 对大小写不敏感：SELECT 与 select 是相同的。

SQL 语句后面的分号？

某些数据库系统要求在每条 SQL 语句的末端使用分号。

分号是在数据库系统中分隔每条 SQL 语句的标准方法，这样就可以在对服务器的相同请求中执行一条以上的 SQL 语句。

在本教程中，我们将在每条 SQL 语句的末端使用分号。

一些最重要的 SQL 命令

SELECT - 从数据库中提取数据

UPDATE - 更新数据库中的数据

DELETE - 从数据库中删除数据

INSERT INTO - 向数据库中插入新数据

CREATE DATABASE - 创建新数据库

ALTER DATABASE - 修改数据库

CREATE TABLE - 创建新表

ALTER TABLE - 变更（改变）数据库表

DROP TABLE - 删除表

CREATE INDEX - 创建索引（搜索键）

DROP INDEX - 删除索引

注释：除了 SQL 标准之外，大部分 SQL 数据库程序都拥有它们自己的专有扩展！

SQL SELECT 语句

SELECT 语句用于从数据库中选取数据。

结果被存储在一个结果表中，称为结果集。

SQL SELECT 语法

SELECT column_name,column_name

FROM table_name;

与

SELECT * FROM table_name;

演示数据库

在本教程中，我们将使用 RUNOOB 样本数据库。

下面是选自 "Websites" 表的数据：

+----+--------------+---------------------------+-------+---------+| id | name | url | alexa | country |+----+--------------+---------------------------+-------+---------+| 1 | Google | https://google.cm/ | 1 | USA || 2 | 淘宝 | https://taobao/ | 13 | CN || 3 | 头条 | http://toutiao/ | 4689 | CN || 4 | 微博 | http://weibo/ | 20 | CN || 5 | Facebook | https://facebook/ | 3 | USA |+----+--------------+---------------------------+-------+---------+

SELECT Column 实例

下面的 SQL 语句从 "Websites" 表中选取 "name" 和 "country" 列：

SELECTname,countryFROMWebsites;

输出结果为:

SELECT * 实例

下面的 SQL 语句从 "Websites" 表中选取所有列：

结果集中的导航

大多数数据库软件系统都允许使用编程函数在结果集中进行导航，比如：Move-To-First-Record、Get-Record-Content、Move-To-Next-Record 等等。

类似这些编程函数不在本教程讲解之列。如需学习通过函数

SQL SELECT DISTINCT 语句

SELECT DISTINCT 语句用于返回唯一不同的值。

在表中，一个列可能会包含多个重复值，有时您也许希望仅仅列出不同（distinct）的值。

DISTINCT 关键词用于返回唯一不同的值。

SQL SELECT DISTINCT 语法

SELECT DISTINCT column_name,column_name

+----+--------------+---------------------------+-------+---------+| id | name | url | alexa | country |+----+--------------+---------------------------+-------+---------+| 1 | Google | https://google.cm/ | 1 | USA || 2 | 淘宝 | https://taobao/ | 13 | CN || 3 | 菜鸟教程 | http://runoob/ | 4689 | CN || 4 | 微博 | http://weibo/ | 20 | CN || 5 | Facebook | https://facebook/ | 3 | USA |+----+--------------+---------------------------+-------+---------+

SELECT DISTINCT 实例

下面的 SQL 语句仅从 "Websites" 表的 "country" 列中选取唯一不同的值，也就是去掉 "country" 列重复值：

SELECTDISTINCTcountryFROMWebsites;

输出结果：

方法一：selecta.id,a.SName,a.ClsNo,a.ScorefromTable1 aleftjoinTable1 bona.ClsNo=b.ClsNoanda.Score

方法二：

select*fromTable1 awhere2>(selectcount(*)fromTable1whereClsNo=a.ClsNoandScore>a.Score)orderbya.ClsNo,a.Scoredesc

方法三：select*fromTable1 awhereidin(selectidfromTable1whereClsNo=a.ClsNoorderbyScoredesclimit2)orderbya.ClsNo,a.Scoredesc

方法....这里列出了多种SQL语句的实现方法，有些是MySQL特有的(Limit, 其它数据库可根据实际更改，比如Oracle的rownum,MSSQL SERVER的 top,..)，有时是SQL标准支持的。但效率上和应用的场合或许不同。具体应用时可根据实际表中的记录情况，索引情况进行选择。

特例 N=1 ,即取最大的/最小的一条记录。

select * from Table1 awhere not exists (select 1 from Table1 where ClsNo=a.ClsNo and Score>a.Score);

select a.* from Table1 a inner join (select ClsNo, max(Score) as mScore from Table1 group by ClsNo) b on a.ClsNo=b.ClsNo and a.Score=b.Score

select *from (select * from Table1 order by Score desc) tgroup by ClsNo

可用group by……having来实现。

可做如下测试：

1、创建表插入数据：

create table test(id int,name varchar(10))insert into test values (1,'张三')insert into test values (2,'李四')insert into test values (3,'张三')insert into test values (4,'王五')insert into test values (5,'赵六')

其中name是张三的有两行，也就是重复行。

2、执行sql语句如下：

select * from test where name in (select name from test group by name having COUNT(*)>1)

结果如图：

（本文内容由百度知道网友badkano贡献）

数据重复是困扰许多企业的问题，但是一旦你了解了它的特点，以及如何去处理它，就可以提前发现并预防。在识别和消除重复数据时，也有很多潜在的选择，这样就可以找到适合你的业务和需求的最佳方法。

但是如果你想解决这个问题，你怎么开始呢？

下面是一些值得注意的最大问题：

记录问题。第一个最明显的问题是你的记录的准确性和可靠性。例如，你无意中列出了同一业务在你的销售记录中有两次；该公司的销售数字将加倍，因此，导致你的收入预测不合理地激增。当查看数据组时，你会更容易出现错误，并且在查找特定实例时，你可能会遇到更大困难，跟踪你需要的确切数据。

系统存储和批量。重复数据也会增加你的表格负担，从而阻塞你的系统，显示不必要的信息。在小规模上，这不是一个主要的数据来源，但是如果重复的数据存在于整个系统中，它可能会导致整个系统减速。

一般问题。很多人发现当查找重要信息时，重复数据集知道跟踪“正确”条目是多么烦人。例如，如果正在寻找“abc通信”，但是有一些条目是“abc公司”，“abc”和“abc通信”，它将花费你三倍或更长时间来获得正确的记录。这对于任何一个工作者来说都是个难题。

其他问题。重复数据也可能是其他原因的问题，具体而言，对于你数据表的应用而言。例如，如果你的网站上有太多重复的内容要索引，那么它可能会危及百度搜索排名还有其他搜索引擎，或者增加被索引的“错误”页面的可能性。

那么，你能做些什么来主动识别和消除重复数据？

这是一些比较好的策略：

完美的数据录入标准。每个组织都需要有一些所有工作人员应遵循的数据输入标准无论您的系统多么好，可能会有一些重复的数据点，除非所有的数据点都是一直遵循这些标准。制定严格、清晰的入门规则是一个好的第一步；除此之外，你用比较好的方法去教育你的员工，并确保他们理解这些规则，并要求他们遵守这些规则，这样他们就会一直遵循这些规则。

算法匹配非相同名称。通过创建更好的自动化流程算法可以自动匹配非相同名称。从前面章节中的例子中，我们提到了“abc公司”、“abc”和“abc通信”词条。a算法围绕着识别和自动合并“模糊匹配”之类的构建，可以防止它们作为不同记录存储起来。幸运的是在sql中安装主数据服务使创建干净、更合并列表变得非常容易。

自动化数据库清理。如果你的数据库已经在许多章节中遭受重复数据，或者过期检查，你也可以运行自动检查。你需要创建一个算法来扫描记录，以获取重复条目的标志，然后将数据合并到一个记录中。这里出错的可能性很高，所以请注意在敏感表上使用它。

手动数据库清理。作为备份，你还要执行手动数据库清理，特别是对于小表。

这些策略无法严格保证你将来不会遇到重复数据问题，但它们将消除当前大多数问题。随着数据标准的提高和数据库的清洁，你的整个团队都将能够提高自己的公众效率。