数据库学习笔记与总结

1.数据库

1.1.基本查询语句

1. S elect

SELECT column_name,column_name FROM table_name;

SELECT * FROM table_name;

SELECT DISTINCT column_name,column_name FROM table_name;

2. W here

SELECT * FROM Websites WHERE country='CN'

SQL 使用单引号来环绕文本值，如果是数值字段，请不要使用引号

运算符描述

= 等于

<> 不等于。注释：在SQL 的一些版本中，该操作符可被写成!=

> 大于

< 小于

>= 大于等于

<= 小于等于

BETWEEN 在某个范围内

LIKE 搜索某种模式

IN 指定针对某个列的多个可能值

SELECT * FROM Websites WHERE country='CN' AND alexa > 50

SELECT * FROM Websites WHERE country='USA' OR country='CN';

SELECT * FROM Websites WHERE alexa > 15 AND (country='CN' OR country='USA');

3. O RDER BY

ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用DESC 关键字。

SELECT column_name,column_name

FROM table_name

ORDER BY column_name,column_name ASC|DESC;

SELECT * FROM Websites ORDER BY country,alexa;

4. I NSERT INTO

INSERT INTO table_name

VALUES (value1,value2,value3,...);

NSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

5. U PDEATE

UPDATE table_name

SET column1=value1,column2=value2,...

WHERE some_column=some_value;

6. D ELETE

DELETE FROM Websites WHERE name='百度' AND country='CN';

1.2.高级查询

1.LIKE

SELECT TOP 50 PERCENT * FROM Websites;

SELECT * FROM Websites WHERE name LIKE 'G%'

"%" 符号用于在模式的前后定义通配符（缺省字母）；“_”代替一个字符

2.IN、BETWEEN、AS

(1) IN 操作符允许您在WHERE 子句中规定多个值

(2) BETWEEN 操作符用于选取介于两个值之间的数据范围内的值

(3) AS 为表名称或列名称指定别名

SELECT * FROM Websites

WHERE name IN ('Google','菜鸟教程')

SELECT column_name(s)

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

3.JOIN

(1)INNER JOIN 与JOIN 是相同的。

(2) LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如

果右表中没有匹配，则结果为NULL。

(3) RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为NULL。

(4) FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。FULL OUTER JOIN 关键字结合了LEFT JOIN 和RIGHT JOIN 的结果。

SELECT https://www.360docs.net/doc/dd4929885.html,, access_log.count, access_log.date

FROM Websites

LEFT JOIN access_log

ON Websites.id=access_log.site_id

ORDER BY access_log.count DESC;

4.UNION

UNION 操作符用于合并两个或多个SELECT 语句的结果集。

请注意，UNION 内部的每个SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个SELECT 语句中的列的顺序必须相同。

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2;

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用UNION ALL。5.SELECT INTO

SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。

SELECT name, url

INTO WebsitesBackup2016

FROM Websites;

SELECT *

INTO WebsitesBackup2016

FROM WebsitesWHERE country='CN'

SELECT https://www.360docs.net/doc/dd4929885.html,, access_log.count, access_log.date

INTO WebsitesBackup2016

FROM Websites

LEFT JOIN access_log

ON Websites.id=access_log.site_id;

6.INSERT INTO SELECT

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。

INSERT INTO Websites (name, country)

SELECT app_name, country FROM apps;

7.CREAT DATABASE 、TABLE

CREATE DATABASE dbname;

CREATE TABLE table_name

(

column_name1 data_type(size),

column_name2 data_type(size),

column_name3 data_type(size),

....

);

column_name 参数规定表中列的名称。

data_type 参数规定列的数据类型（例如varchar、integer、decimal、date 等等）。

size 参数规定表中列的最大长度。

8.SQL CREATE TABLE + CONSTRAINT 语法

CREATE TABLE table_name

(

column_name1 data_type(size) constraint_name,

column_name2 data_type(size) constraint_name,

column_name3 data_type(size) constraint_name,

....

);

在SQL 中，我们有如下约束：

NOT NULL - 指示某列不能存储NULL 值。

UNIQUE - 保证某列的每行必须有唯一的值。

PRIMARY KEY - NOT NULL 和UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

CHECK - 保证列中的值符合指定的条件。

DEFAULT - 规定没有给列赋值时的默认值。

9.DROP

DROP TABLE 语句：DROP TABLE 语句用于删除表。

DROP TABLE table_name

DROP DATABASE 语句：DROP DATABASE 语句用于删除数据库。

DROP DATABASE database_name

TRUNCATE TABLE 语句：如果我们仅仅需要删除表内的数据，但并不删除表本身，那么我们该如何做呢？

请使用TRUNCATE TABLE 语句：

TRUNCATE TABLE table_name

10.视图

在SQL 中，视图是基于SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。

您可以向视图添加SQL 函数、WHERE 以及JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样。

CREATE VIEW [Current Product List] AS

SELECT ProductID,ProductName

FROM Products

WHERE Discontinued=No

视图

视图是虚表，是从一个或几个基本表（或视图）中导出的表，在系统的数据字典中仅存放了视图的定义，不存放视图对应的数据。

视图是原始数据库数据的一种变换，是查看表中数据的另外一种方式。可以将视图看成是一个移动的窗口，通过它可以看到感兴趣的数据。视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。

视图的定义存在数据库中，与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中。

视图看上去非常像数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产

生的视图中。由于逻辑上的原因，有些视图可以修改对应的基表，而有些则不能（仅仅能查询）

1.3.函数

(1) AVG() 函数返回数值列的平均值

SELECT AVG(column_name) FROM table_name

(2) COUNT() 函数返回匹配指定条件的行数（NULL 不计入）

SELECT COUNT(column_name) FROM table_name;

(3) SUM() 函数返回数值列的总数

SELECT SUM(column_name) FROM table_name;

2.接口

【接口是纯数据的交互】APP接口是移动设备和业务之间进行通信的途径。实质就是以特定的规则通过接口直接操作数据库的增删改查。

2.1.接口的分类

1. 查询类接口

查询类接口是指客户端传递一些参数，服务端根据参数依据需求，前往数据库查询需要的结果返回数据的一类接口。

返回类型一般有两种。第一种是返回一个对象，第二种是返回一个数组对象。

第一种比如登陆，客户端把用户名密码上传到接口，服务器返回用户的个人信息。

第二种比如获取客户，客户端把用户的身份信息上传到接口，服务器返回此身份下的所有客户数组集合。

2. 操作类接口

操作类接口是指，客户端通过接口进行一些增删改的操作。比如新增一个客户，修改客户信息，或者删除一个客户。服务器一般返回执行的状态，有的需要返回执行结果的一些信息，比如新增客户后，返回客户的ID。

3. 上传下载类接口

上传下载类接口是涉及到文件传输的接口。比如上传头像，需要上传图片到服务器，服务端根据需求响应保存并返回结果。比如客户端需要显示用户头像，需要读取网络图片文件，在手机上进行显示。

4. 推送类接口

除了客户端主动去请求服务端，获取需要信息之外。有时候，也存在服务端有消息需要通知客户端的情况，这时候就是服务端向客户端发送消息。这类需求可以通过客户端短时间类循环请求解决，也可以通过第三方专业推送解决。也可以通过自己使用socket或者xmpp等协议进行开发。

2.2.API接口、webservice接口、HTTP接口

webservice用的是soap协议；http协议传输的都是字符串了，webservice则是包装成了更复杂的对象

http：客户向服务器请求服务时，只需传送请求方法和路径。请求方法常用的有GET、HEAD、

POST。每种方法规定了客户与服务器联系的类型不同。由于HTTP协议简单，使得HTTP服务器的程序规模小，因而通信速度很快。

2.3.案例

2.3.1.客户概要列表信息获取接口

接口说明：获取当前用户已添加的客户概要列表信息

接口URL：http://188.4.19.57:8282/iot360/iot/groupListQuery

输入样例：

{

"loginName":"dwdingtingfang",

"pageSize":5,

"pageIndex":0

}

输出样例：

{

"retCode":"000",

"message":"success",

"count":4,

"datas":[

{

"groupName":"深圳百米乐科技有限公司",

"groupNo":"2002628554",

"monitorNoCount":4

},

{

"groupName":"思建科技有限公司",

"groupNo":"2002628255",

"monitorNoCount":4

},

{

"groupName":"深圳市畅行神州科技有限公司",

"groupNo":"2001286623",

"monitorNoCount":4

},

{

"groupName":"广州滨派通信科技有限公司",

"groupNo":"2002519965",

"monitorNoCount":4

}

]

}