sql语言
2018-07-20 10:39:45 19 举报AI智能生成
登录查看完整内容
sql语言
作者其他创作
大纲/内容
一个数据库通常包含一个或多个表。每个表由一个名字标识（例如“客户”或者“订单”）。表包含带有数据的记录（行）。
例子
数据库表
SELECT 列名称 FROM 表名称
注意：星号（*）是选取所有列
语法
SELECT - 从数据库表中获取数据
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
子主题
UPDATE - 更新数据库表中的数据
DELETE FROM 表名称 WHERE 列名称 = 值
可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的
注意
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据
DISTINCT 用于返回唯一不同的值。
作用
SELECT DISTINCT 列名称 FROM 表名称
DISTINCT 语句
有条件地从表中选取数据
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
操作符
WHERE 子句
AND 和 OR 可在WHERE 子语句中把两个或多个条件结合起来。如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。
AND 和 OR 运算符
ORDER BY 语句用于根据指定的列对结果集进行排序。ORDER BY 语句默认按照升序对记录进行排序。如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。
ORDER BY
TOP 子句用于规定要返回的记录的数目。对于拥有数千条记录的大型表来说，TOP 子句是非常有用的。注释：并非所有的数据库系统都支持 TOP 子句。
SELECT TOP number|percent column_name(s)FROM table_name
SQL Server
MySQL 语法SELECT column_name(s)FROM table_nameLIMIT number
MySQL
SELECT column_name(s)FROM table_nameWHERE ROWNUM <= number
Oracle
SELECT TOP 2 * FROM Persons
SQL Server
SELECT *FROM PersonsLIMIT 5
SELECT *FROM PersonsWHERE ROWNUM <= 5
Oracle
TOP 子句
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
SELECT column_name(s)FROM table_nameWHERE column_name LIKE pattern
示例
LIKE
在搜索数据库中的数据时，SQL 通配符可以替代一个或多个字符。SQL 通配符必须与 LIKE 运算符一起使用。
包括
通配符
NOT
IN 操作符允许我们在 WHERE 子句中规定多个值。
IN
操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
SELECT column_name(s)FROM table_nameWHERE column_nameBETWEEN value1 AND value2
不同的数据库对 BETWEEN...AND 操作符的处理方式是有差异的。某些数据库会列出介于 \"Adams\" 和 \"Carter\" 之间的人，但不包括 \"Adams\" 和 \"Carter\" ；某些数据库会列出介于 \"Adams\" 和 \"Carter\" 之间并包括 \"Adams\" 和 \"Carter\" 的人；而另一些数据库会列出介于 \"Adams\" 和 \"Carter\" 之间的人，包括 \"Adams\" ，但不包括 \"Carter\" 。
BETWEEN
为列名称和表名称指定别名（Alias）。
SELECT column_name(s)FROM table_nameAS alias_name
表的别名
SELECT column_name AS alias_nameFROM table_name
列的别名
Alias（别名）
利用内连接可获取两表的公共部分的记录（返回连接表中符合连接条件和查询条件的数据行）
通过某个字段使用=运算符连接两个表的链接 也叫做 等值连接
等值连接
where 连接多个表也属于内连接，在数据库中被称为隐性内链接。inner join被称为显性内连接。
补充
内连接
是以左表为基准，将a.stuid = b.stuid的数据进行连接，然后将左表没有的对应项显示，右表的列为NULL（公共部分记录集C＋表A记录集））在语句中，A在B的左边，并且是Left Join，所以其运算方式为：A左连接B的记录=图3公共部分记录集C＋表A记录集A1
左外连接
是以右表为基准，将a.stuid = b.stuid的数据进行连接，然以将右表没有的对应项显示，左表的列为NULL（公共部分记录集C＋表B记录集B1。）在语句中，A在B的左边，并且是Right Join，所以其运算方式为：A右连接B的记录=图3公共部分记录集C＋表B记录集B1
右外连接
返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
、
全外连接
外连不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行。
外连接
交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。
交叉连接
Join
合并两个或多个 SELECT 语句的结果集union 剔除重复的查询数据unionall不剔除重复的查询数据
union 内部的 select语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 select语句中的列的顺序必须相同。
联合查询（union/union all）
从一个表中选取数据，然后把数据插入另一个表中。常用于创建表的备份复件或者用于对记录进行存档。
您可以把所有的列插入新表：SELECT *INTO new_table_name [IN externaldatabase] FROM old_tablename或者只把希望的列插入新表：SELECT column_name(s)INTO new_table_name [IN externaldatabase] FROM old_tablename
SELECT INTO
会在新记录插入表中时生成一个唯一的数字。我们通常希望在每次插入新记录时，自动地创建主键字段的值。
默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。要让 AUTO_INCREMENT 序列以其他的值起始，请使用下列 SQL 语法：ALTER TABLE Persons AUTO_INCREMENT=100
Mysql
SQL Server
Access
Oracle
自动增长（Auto-increment ）
NULL 值是遗漏的未知数据。默认地，表的列可以存放 NULL 值。
比较NULL值就用IS NULL 和 IS NOT NULL 操作符。
NULL 值（IS NULL 和 IS NOT NULL ）
用于结合合计函数，根据一个或多个列对结果集进行分组。
GROUP BY
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
HAVING
关键字
数据操作语言 (DML)
创建或删除表格。我们也可以定义索引（键），规定表之间的链接，以及施加表间的约束。
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引
具体实现
数据定义语言 (DDL)
Microsoft Access
Text 类型
这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。
Number 类型
即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。
Date类型
MySQL 数据类型
Character 字符串
Unicode 字符串
Binary 类型
Date 类型
其他数据类型
数据类型
NOT NULL 约束强制列不接受 NULL 值。NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
NOT NULL
UNIQUE 约束唯一标识数据库表中的每条记录。UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
命名 UNIQUE 约束，以及为多个列定义 UNIQUE 约束
单个列UNIQUE约束
创建表时候增加约束示例
修改表结构的时候增加约束
撤销 UNIQUE 约束
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。
UNIQUE
PRIMARY KEY 约束唯一标识数据库表中的每条记录。主键必须包含唯一的值。主键列不能包含 NULL 值。每个表都应该有一个主键，并且每个表只能有一个主键。
单个列约束
多个列约束
创建表的时候增加约束示例
撤销PRIMARY KEY
PRIMARY KEY
一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。FOREIGN KEY 约束用于预防破坏表之间连接的动作。FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
FOREIGN KEY
CHECK 约束用于限制列中的值的范围。如果对单个列定义 CHECK 约束，那么该列只允许特定的值。如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。
撤销 CHECK
CHECK
DEFAULT 约束用于向列中插入默认值。如果没有规定其他的值，那么会将默认值添加到所有的新记录。
撤销DEFAULT
DEFAULT
约束
在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。索引是要占磁盘空间的。
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
概念
聚簇索引是按照数据存放的物理位置为顺序的，能提高多行检索的速度
聚簇索引
没有顺序，对于单行的检索很快
非聚簇索引
何时使用聚集索引或非聚集索引？
分类
最基本的索引，它没有任何限制，比如上文中为title字段创建的索引就是一个普通索引，MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。
普通索引
与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。
唯一索引
MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。////对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
全文索引（FULLTEXT）
多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。
单列索引、多列索引
平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引
创建复合索引时应该将最常用（频率）作限制条件的列放在最左边，依次递减。
组合索引（最左前缀）
索引（CREATE INDEX）
在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。
视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。 通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。
CREATE VIEW 视图名(列1，列2...) AS SELECT (列1，列2...) FROM ...;
创建视图
CREATE OR REPLACE VIEW 视图名 AS SELECT [...] FROM [...];
修改视图
DROP VIEW 视图名；
删除视图
DESC 视图名;或者SHOW FIELDS FROM 视图名;
查看视图详情
因为视图本身没有数据，因此对视图进行的dml操作最终都体现在基表中
①select子句中包含distinct②select子句中包含组函数③select语句中包含group by子句④select语句中包含order by子句⑤select语句中包含union 、union all等集合运算符⑥where子句中包含相关子查询⑦from子句中包含多个表⑧如果视图中有计算列，则不能更新⑨如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作
就像对普通表那样子，但是有的视图不能进行DML操作
视图的DML操作（增删改查）
数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。
视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。
视图是虚拟表，本身不存储数据，而是按照指定的方式进行查询。
可以通过视图插入数据，但是只能基于一个基础表进行插入，不能跨表更新数据。
一般情况下，在创建有条件限制的视图时，加上“WITH CHECK OPTION”命令。with check option约束限制，保证更新视图是在该视图的权限范围之内。
显示指定视图列名，要求视图名后面的列的数量必须匹配select子句中的列的数量。
简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
为了保障数据安全性，提高查询效率
优点
调用视图的时候，才会执行视图中的sql，进行取数据操作。视图的内容没有存储，而是在视图被引用的时候才派生出数据。这样不会占用空间，由于是即时引用，视图的内容总是与真实表的内容是一致的。我们不需要维护视图的内容，维护好真实表的内容，就可以保证视图的完整性了。
工作机制
替换式
mysql先得到了视图执行的结果，该结果形成一个中间结果暂时存在内存中。之后，外面的select语句就调用了这些中间结果(临时表)。（先处理视图结果，后处理外面的查询需求。）
具化式
mysql会自己确定使用哪种方式进行处理的。
CREATE [ALGORITHM]={UNDEFINED|MERGE|TEMPTABLE}]VIEW 视图名 [(属性清单)]AS SELECT 语句[WITH [CASCADED|LOCAL] CHECK OPTION];ALGORITHM有三个参数分别是：merge、TEMPTABLE、UNDEFINEDmerge：处理方式替换式，可以进行更新真实表中的数据；TEMPTABLE：具化式，由于数据存储在临时表中，所以不可以进行更新操作！当你的参数定义是UNDEFINED(没有定义ALGORITHM参数)。mysql更倾向于选择替换方式。是因为它更加有效。
自己选择方式
工作方式
视图
\t返回当前的日期和时间
NOW()
返回当前的日期。
CURDATE()
返回当前的时间。
CURTIME()
返回日期或日期/时间表达式的日期部分。
DATE(date)
返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。
unit 参数
EXTRACT(unit FROM date)
向日期添加指定的时间间隔。
date 参数是合法的日期表达式。
expr 参数是您希望添加的时间间隔。
type 参数
从日期减去指定的时间间隔。
date 参数是合法的日期表达式
返回两个日期之间的天数。
date1 和 date2 参数是合法的日期或日期/时间表达式。
只有值的日期部分参与计算。
用于以不同的格式显示日期/时间数据。
date 参数是合法的日期。
format 规定日期/时间的输出格式。
MYSQL中
返回当前日期和时间
SELECT GETDATE() AS CurrentDateTime
GETDATE()
返回日期/时间的单独部分
datepart 参数
在日期中添加或减去指定的时间间隔
number 是您希望添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。
返回两个日期之间的时间
startdate 和 enddate 参数是合法的日期表达式。
CONVERT() 函数是把日期转换为新数据类型的通用函数。CONVERT() 函数可以用不同的格式显示日期/时间数据。
data_type(length) 规定目标数据类型（带有可选的长度）
data_to_be_converted 含有需要转换的值
style 规定日期/时间的输出格式。
有关Date的函数
ISNULL()
SQL Server / MS Access
IFNULL()
COALESCE()
MySQL
NVL()
Oracle
有关NULL的函数
面向一系列的值，并返回一个单一的值。
返回某列的平均值
SELECT AVG(列名) FROM 表名
SELECT AVG(OrderPrice) AS OrderAverage FROM Orders
SELECT Customer FROM OrdersWHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
AVG(column)
返回某列的行数（不包括 NULL 值）
SELECT COUNT(列名) FROM 表名
SELECT COUNT(*) FROM 表名
SELECT COUNT(DISTINCT 列名) FROM 表名
COUNT(DISTINCT) 适用于 ORACLE 和 Microsoft SQL Server，但是无法用于 Microsoft Access。
SELECT COUNT(Customer) AS CustomerNilsen FROM OrdersWHERE Customer='Carter'
SELECT COUNT(*) AS NumberOfOrders FROM Orders
COUNT(column)
返回被选行数
SELECT COUNT(*) FROM Persons
SELECT COUNT(*) FROM Persons WHERE Age>20
COUNT(*)
返回某列的最高值
SELECT MAX(列名) FROM 表名
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders
MAX(column)
返回某列的最低值
SELECT MIN(列名) FROM 表名
SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders
MIN(column)
返回某列的总和
SELECT SUM(列名) FROM 表名
SELECT SUM(OrderPrice) AS OrderTotal FROM Orders
SUM(column)
返回指定的字段中第一个记录的值。
SELECT FIRST(列名) FROM 表名
SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders
FIRST()
返回指定的字段中最后一个记录的值。
SELECT LAST(列名) FROM 表名
SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders
LAST()
合计函数 (比如 SUM) 常常需要添加 GROUP BY 语句
合计函数（Aggregate functions）
面向某个单一的值，并返回基于输入值的一个单一的值。
把字段的值转换为大写。
SELECT UCASE(列名) FROM 表名
UCASE()
把字段的值转换为小写。
SELECT LCASE(列名) FROM 表名
LCASE()
用于从文本字段中提取字符。
column_name：必需。要提取字符的字段。
start ：必需。规定开始位置（起始值是 1）。
length： 可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本。
MID()
返回文本字段中值的长度。
SELECT LEN(列名) FROM 表名
SELECT LEN(City) as LengthOfCity FROM Persons
LEN()
用于把数值字段舍入为指定的小数位数。
column_name\t必需。要舍入的字段。
decimals\t必需。规定要返回的小数位数。
ROUND()
用于对字段的显示进行格式化。
column_name\t必需。要格式化的字段。
format\t必需。规定格式。
FORMAT()
Scalar 函数
函数
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。
工作模式
1.存储过程可以重复使用，大大减小开发人员的负担；
2.对于网络上的服务器，可以大大减小网络流量，因为只需要传递存储过程的名称即可；
3，可以防止对表的直接访问，只需要赋予用户存储过程的访问权限。
存储过程中具体的处理类容放在BEGIN和ENDBEGIN和END 之间；
参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值（传入值可以是字面量或变量）
IN（输入参数）
该值可在存储过程内部被改变，并可返回（传出值只能是变量）
OUT（输出参数）
调用时指定，并且可被改变和返回（传出值只能是变量）
INOUTIN（即是输入也是输出的参数）
>输入值使用in参数；>返回值使用out参数； >inout参数就尽量的少用。
确保参数的名字不等于列的名字，否则在过程体中，参数名被当做列名来处理
存储过程的参数种类
创建一个对表customer的姓名(nam)进行模糊检索，命名为sp_search_customer。
简单条件示例
case示例
REPEAT示例
WHILE示例
DELIMITER //表示改变分隔符，默认分隔符是;，否则存储过程中含有;，MySQL监视器无法分辨。(最后将分隔符改回来）
WHILE是前置判断，是先验的，先验证WHILE后面的条件是否成立，为TRUE则继续执行，若FALSE则结束循环； 而REPEAT是后置判断，是后验的，不管三七二十一先执行语句，然后验证UNTIL后面的条件语句是否成立，不成立则结束。
WHILE 和 REPEAT循环的区别
创建存储过程
SHOW PROCEDURE STATUS\\G
查看数据库中的存储过程
\tSHOW CREATE PROCEDURE 存储过程名\\G
查看具体的存储过程
CALL sp_search_customer('王%');
调用（执行）存储过程
\tDROP PROCEDURE 存储过程名
删除存储过程
DECLARE 变量名 数据类型;
变量声明
\tSET 变量名= ;
变量赋值
存储过程中的变量
存储过程
触发器
游标
组成
数据库管理系统是一种可以访问数据库中数据的计算机程序。DBMS 使我们有能力在数据库中提取、修改或者存贮信息。不同的 DBMS 提供不同的函数供查询、提交以及修改数据。
DBMS- 数据库管理系统（Database Management System）
关系数据库管理系统 (RDBMS) 也是一种数据库管理系统，其数据库是根据数据间的关系来组织和访问数据的。20 世纪 70 年代初，IBM 公司发明了 RDBMS。RDBMS 是 SQL 的基础，也是所有现代数据库系统诸如 Oracle、SQL Server、IBM DB2、Sybase、MySQL 以及 Microsoft Access 的基础。
RDBMS-关系数据库管理系统（Relational Database Management System）
SQL 服务器 - RDBMS
SQL 指结构化查询语言
SQL 使我们有能力访问数据库
SQL 是一种 ANSI 的标准计算机语言
注意：SQL 对大小写不敏感！
什么是sql
SQL 面向数据库执行查询
SQL 可从数据库取回数据
SQL 可在数据库中插入新的记录
SQL 可更新数据库中的数据
SQL 可从数据库删除记录
SQL 可创建新数据库
SQL 可在数据库中创建新表
SQL 可在数据库中创建存储过程
SQL 可在数据库中创建视图
SQL 可以设置表、存储过程和视图的权限
sql的作用
sql语言
0 条评论
回复 删除
下一页
