二、SQL 格式 检索数据
2.1 Select 需要得到的列名1,列名2,AVG(DISTINCT XXX),完整的查询子句
From 表名1,表名2 (自动连接)
(select 子查询)
表名3 AS XX (表别名)
内联结:表名 INNER JOIN 表名 ON 条件;
Group by 列名
HAVING 条件和Where的条件一样写法
Where (条件1>), AND|OR|NOT, 条件2<, IN(‘XX’,’XX’), XXX LIKE ‘XXX%’
XXXX IN (完整的查询语句);//子查询
表联结条件
ORDER by 列名1,列名2 DESC;
语法格式:
1、 结束用分号;
2、 Select大小写不敏感;
3、 得到多个列用逗号隔开;
4、 最好不用*来进行通配;
5、 Order by一定是select的最后一句;
6、 Order by 默认是升序,要用降序使用DESC;
7、 Order by 中对于a和A 的排序,需要请求管理员帮助,默认a和A是没有区别的;
注意:Access 不兼容性,Access不允许按照别名排序。
8、 LIKE 使用通配符,% 表示任意个任意字符;如果是Access数据库,使用*;
9、 LIKE 使用通配符,_ 表示匹配单个字符;
10、 LIKE 使用通配符,[JM]表示通配集合 ;
11、 LIKE 使用通配符,^[JM]表示否定 通配集合 ;
12、 拼接:+ 、||、CONCAT()
13、 RTRIM() 函数去掉值右边的所有空格;
14、 获取当前日期:Access使用Now(); DB2 和postggreSQL 使用CURRENT_DTAE;MySQL使用CRUDATE(); Oracle使用SYSDATE; sql server 和Sybase使用GETDATE();
15、 提取字符串成分:Access使用MID(),DB2、Oracle 和PosgreSQL 使用SUBSTR(); MySQL 、SQLServer 和Sybase 使用 SUBSTRING()’
16、 数据类型转换:Access 和 Oracle 使用多个函数,每种类型的转换有一个函数:DB2 和PostgreSQL 使用CAST() ; MySQL、SQL Server 和Sybase 使用CONVERT();
17、 还有一个特殊的发音匹配:SOUNDEX();//发音类似的,其实也就是相近的;
18、 返回日期的某一部分,Where DATEPART(‘yyyy’, order_date) = 2004; 或者Where DATEPART(‘year’, order_date) = 2004;
注意,Oracle没有DATEPART()函数,不过可以用其他的函数来代替,WHERE to_number(to_char(order_date, ‘YY’)) = 2004; 其中,to_char() 函数用来提取日期成分,to_number 用来转成数值;
19、
一、 聚集函数
用于汇总:
AVG():注意:只能用来确定特定数值列的平均值,而且列名必须作为函数给出;
COUNT():注意:如果函数参数weo* ,则不忽略空格;
MAX():
Min();
SUM();
二、 分组数据GROUP BY
过滤分组:HAVING
HAVING 与WHERE 的区别: HAVING 是分组后过滤,WHERE 是分组前过滤;HAVING 必须在GROUP BY之后使用;
三、 使用子查询
子查询总是从内向外处理;
XXXX IN (完整的查询语句);
四、 联结表
自动连接:笛卡尔集合;也叫等值连接、内部联结;
表别名:
注意:Oracle 中没有AS ,Oracle 用户应该记住去掉AS;
表别名为了防止表联结的二义性;
自联结比子查询要快:
表名 INNER JOIN 表名 ON 条件;
自然联结:去重的联结,自联结基本上都是自然联结;
外联结:连接中包含了那些在相关表中没有关联行的行(例如没有订单的行);
表名 RIGHT|LEFT OUTER JOIN 表名 ON 条件
RIGHT表示的是包含左边的表的行;
左外部联结和右外部联结的转换只需要颠倒FROM和WHERE 子句中表的顺序即可;
全外部联结:FULL OUTER JOIN XXX ON XXX
注意:在Oracle中,外部连接使用的是+;
注意:Access、MySQL、SQL Server 或者Sybase 不支持 FULL OUTER JION 。
四、 组合查询 UNION
使用: 只需要在每条SELECT语句之间放上关键字 UNION;
使用UNION 查询结果集中自动除去重复的行,如果想要全部的那么使用UNION ALL;
如果需要全部的项包括重复行,则必须使用UNION ALL 而不是用WHERE;
使用UNION 组合查询的时候,只能使用一条 GROUP BY ,它必须出现最后一条SELECT语句之后;
注意:UNION的每个查询必须包含相同的列、表达式或者聚集函数(字段的顺序可以变);同时,列的类型必须兼容,可以隐含地转换的类型;
实际上UNION 类型很少被使用,因为相同的结果可以利用联结来得到;
但是UNION 极大的简化了复杂 的 WHERE 子句,简化从多个表中检索数据工作;
数据库操纵
一、 插入数据
INSERT语句可能需要客户机/服务器DBMS的特定安全权限;
格式:INSERT INTO Customers [(列名1,列名2,列名3)] VALUE(‘xx’, ‘XX’ ,’XX’) ;
枚举出列名不是必须的,但是有列名,字段可以换位子,移植性好;
没有提供值得列为NULL;
INSERT SELECT 就是将Select语句的结果作为参数插入到一个标中;
复制表:
SEHLECT * INTO CustCopy FROM Customers;
二、 更新和删除 数据
UPDATE 表名
SET
字段1 = ‘AAAAA’,
字段2 = ‘BBBBB’,
字段3 = NULL .// 使用NULL 可以删除某个列的值
WHERE 字段4 = ‘CCCCC’
说明:UPDATE 对表的行做更新;对比ALTER TABLE
删除数据16.2(TODO)
DELETE FROM 表名
WHERE 字段名 = “AAAA”;
删除只能删除行,要删除列 应该使用UPDATE。
删除操作不能删除表本身。
注意: SQL没有撤销功能,应该要小心使用UPDATE 和 DELETE 。
十、 创建和操纵表
CREATE TABLE 表名
{
字段名A 类型 NOT NULL,
字段名B 类型 NOT NULL,
字段名C 类型 NOT NULL,
….
}
10.1 使用NULL值
默认使用 NULL,NULL 表示没有值或者缺值;
主键不能为NULL属性;
10.2 制定默认值
CREATE TABLE 表名
{
字段名A 类型 NOT NULL,
字段名B 类型 NOT NULL DEFAULT XXX,
字段名C 类型 NOT NULL,
….
}
10.3获取系统当前时间:
Access NOW()
DB2 CURRENT_DATE
MySQL CURRENT_DATE()
Oracle SYSDATE
PostgreSQL Current_DATE
SQL Server GETDATE()
Sybase GETDATE()
10.4更新表: 表的列做更新;对比UPDATE TABLE
ALTER TABLE 表名
ADD 列明 字段数据类型; //表中添加列
DROP COLUMN 列名 ; //表中删除列
例如:
ALTER TABLE Vendors
ADD vend_phone CHAR(20);
10.5 删除表
DROP TABLE 表名;
10.6 重命名表
DB2、MySQL、Oracle 和PostgreSQL 可以使用RENAME语句;
SQL SERVER 和 Sybase 用户可以使用sp_rename 存储过程;
十一、 使用视图
视图是虚拟的表,视图与表不一样,只包含使用时动态检索数据的查询;视图本身不包含数据,因此,它只是用来查看存储在别处的数据的一种设施;
视图的使用就类似于我们开发语言中的
相当于:存储的一个中间表;
作用:
1、 重用SQL语句;
2、 简化SQL操作
3、 使用表的组成成分,而不是整个表;
4、 保护数据;
5、 更改数据格式和表示;
创建视图后,可以像表一样使用它们;(感觉就像是一个封装好的函数)
性能问题:因为视图不包含数据;每次使用时都必须处理查询执行时的任意一个检索,如果是过滤创建复杂的视图或者嵌套了视图,性能就会下降的很厉害;
语法: CREATE VIEW
删除视图:DROP VIEW viewname;
例子:
CREATE VIEW ProductCustiomers AS
SELECT Cust_name,…列名..
FROM Customers, … 表名…
WHERE Customers.cust_id = Order.cust_id … 其他的条件;
视图常见用法:
11.1 使用视图重新格式化检索出来的数据;
CREATE VIEW ProductCustiomers AS
SELECT [这里可以自定义视图的格式]
FROM Customers, … 表名…
WHERE Customers.cust_id = Order.cust_id … 其他的条件;
11.2 使用视图过滤不想要的数据
WHERE 条件写为:列名 IS NOT NULL;
十二、 使用存储过程
可以单独编写每条SQL语句,并根据结果有条件的执行另外的语句;
存储过程为:为以后的使用而保存的一条或者多条SQL语句集合。
好处:封装、安全
12.1 创建存储过程
Oracle 版本:
CREATE PROCEDURE 【过程名称】
(参数名1 OUT NUMBER)
IS
BEGIN
SELECT * From Customers
WHERE NOT cust_email is NULL
END;
SQL SERVER版本:
CREATE PROCEDURE 【过程名称】
AS
DECLARE @cnt Integer
SELECT @cnt = COUNT(*)
FROM Customers
WHERE NOT cust_email is NULL
RETURN @cnt
注释: --
12.2 执行存储过程
EXECUTE AddNewProduct(过程名称) (参数)
十三、 管理事务处理
事物处理用来维护数据库的完整性,保障成批的SQL要么完全执行,要么完全不执行;
这个就相当于是开发中的互斥操作;可以保证一组操作不会被中途停止;
事物:transaction 指一组SQL语句;
回退:rollback撤销指定SQL语句的过程;
提交:commit 将未保存的SQL语句结果写入到数据表;
保留点: 事务处理中设置的临时占位符placeholder,可以发布回退;
13.1 控制事物处理
SQL Server的语法如下:
BEGIN TRANSACTION
…【SQL 语句】
COMMMIT TRANSACTION
MySQL 的语法如下:
START TRANSACTION
…【SQL 语句】
PostGreSQL 使用ANSI SQL语法:
BEGIN;
…【SQL 语句】
13.2 使用撤销ROLLBACK
例如: DELETE FROM Orders;
ROLLBACK;
数据表的操作是可以回退的,但是建表是不能撤销的;
13.3 使用COMMIT
一般的SQL语句都是直接对数据库执行和编写的,这是隐含提交 (implicit Commit),即自动提交操作; 但是在事物中,不会这样,必须要使用 COMMIT才能将结果提交;
13.4 保留点的使用
设置保留点: SAVEPOINT delete1;
回退到保留点: ROLLBACK TRANSACTION delete1;
事物中的SQL语句,如果操作失败,事物处理最近能回退到保留点处;
在SQL SERVER中,可以检查一个名为@@ERROR的变量,查看操作是否成功;
为非零表示错误,事物回退到保留点,处理成功,就COMMIT;
可以看出:保留点越多越好;
十四、 使用游标
游标cursor 是一个存储在DBMS服务器上的数据库查询;它不是SELECT语句,而是被语句检索出来的额结果集合;在存储了游标之后,应用程序可以根据需要滚动或者浏览其中的数据;
14.1 创建游标
DB2、SQL SERVER 和 Sybase 的版本:
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL
Oracle 和 PostgreSQL版本:
DECLARE CURSOR CustCursor
IS
SELECT * FROM Customers
WHERE cust_email IS NULL
14.2 使用游标
OPEN CURSOR CustCursor;
CLOSE CustCursor;
Oracle 语句:从游标第一行中检索一行;
DECLARE TYPE CustCursor IS REF CURSOR
RETURN Customers%ROWTYPE;
Oracle 语法,从第一行到最后一行,对检索出来的数据进行循环;
DECLARE TYPE CustCursor IS REF CURSOR
RETURN Custormers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
OPEN CustCursor;
LOOP
FETCH CustCursor INTO CustREcord;
EXIT WHEN CustCursor%NOTFOUND;
…
END LOOP;
CLOSE Custcursor;
END;
十五、 了解高级SQL 特性
15.1 约束:管理如何插入或者处理数据库数据的规则;大多数的约束都是在创建表CREATE TABLE 或者 ALTER TABLE 中定义;
约束是实施引用完整性的一个主要成分;
15.2 主键:唯一性
CREATE TABLE 【表名】
(
字段1 char(10) NOT NULL PRIMARY KEY,
…
)
15.3 外键: REFERENCES表中的一个列必须在另外一个表的主键中出现;
CREATE TABLE 【表名】
(
字段名1 char(10) NOT NULL REFERENCES 表名(字段名),
…
)
外键可以防止意外的删除;
15.4 唯一性约束:保证数据的唯一性 UNIQUE
15.5 检查约束
CREATE TABLE 【表名】
(
字段名1 char(10) NOT NULL CHECK (对当前字段的条件),
…
)
15. 6 索引
用来排序数据以加快搜索和排序操作的速度;
CREATE INDEX 语句
例如:CREATE INDEX prod_name_id
ON PRODUCTS(prod_name);
增加了索引的操作,但是减低了插入、修改、删除的性能;
索引数据可能占大量的存储空间;
所以,索引可以改善数据检索的性能;
15.7 触发器
触发器是特殊的存储过程,在特定的数据库活动发生时自动执行;触发器可以与特定表上的INSERT 、UPDATE和DELETE操作相关联;
触发器可以用来执行运行前后的处理,安全选项可以管理数据访问;
十六、 常见的数据库应用系统
16.1 Aqua Data Studio 能运行在所有平台上,支持所有重要的DBMS已经ODBC,基于java 免费;
16.2 DB2 :IBM的高端多平台DBMS。
16.3 Macromedia ColdFusion :Web应用开发平台,建立一个可以从Web浏览器调用执行的简单页面。
16.4 Microsoft Access:任意的ODBC数据源。
16.5 Microsoft ASP 创建基于Web应用的脚本平台,建立一个可以从Web浏览器调用执行的简单页面。
16.6 使用Microsoft ASP.net 使用.net框架以创建基于Web应用的脚本平台,通过创建一个可以通过从浏览器中调用来执行的页面。
17.7 使用Microsoft Query 一种对ODBC数据源进行测试SQL语句的理想使用程序。
17.8 Microsoft SQL SERVER:基于窗口的查询分析工具;
17.9 MySQL 自带mysql的命令行实体程序;直接命令行处理;
17.10 Oracle 有一个基于java的 管理工具:SQL*Plus Worksheet;
17.11 PHP 是一个流行的Web脚本语言,提供了连接各种数据库的函数和库。
17.12 PostgreSQL 有一个带有命令行实用程序,名为psql;
17.13 Query Tool 是George Poulose 创建的一个独立的SQL查询工具,对ODBC数据源测试SQL语句的理想实用程序;也有ADO版本;
17.14 Sybase Adaptive SERVER 带有一个基于java的使用程序,名为SQL Advantage;