SQL基础与常用技巧

1、SQL基础知识数据库、表介绍基本概念和查询语句建数据库、建表进阶子句、函数的应用优化使用技巧SQL基础SQL进阶SQL技巧数据库就是存放数据的仓库。是一组相关数据的集合。数据库由表、关系和操作对象组成。表行和列行记录（客观存在、可被描述）列字段、属性DBMS：操纵和管理数据库的大型软件，用于建立、使用和维护数据库。常用：SYBASE、ORACLE、DB2、Mysql、SQL Server、Postgre。SQL：用于存取数据以及查询、更新和管理DBMS数据库的概念数据的存放数据库管理系统结构化查询语言SQL是一门标准语言。但根据数据库操作系统的不同存在一些不同版本的SQL。换句话说，除了一些主

2、要的关键词和语法外，各数据库管理系统中都有关于SQL的扩展语言。标准SQL在任何DBMS中都可以正常使用。SQL可以查询、插入记录。可以更新数据、删除记录。可以创建数据库。可以创建表、存储过程、视图。为表、存储过程、视图设置权限。每张表都有一个名字。每张表都有至少一个列。表用来存放记录，新建的表中没有记录，是一张空表。IdNameSexProvinceCity1赵阿土男台湾省台北市2钱夫人女福建省厦门市3孙小美女广东省广州市4李宝藏男江苏省苏州市如下表，表名为：Persons。该表含有Id、姓名、性别、省、市这5个列。该表包含4条记录，就是4行数据。SQL语言是一种大小写不敏感的语言，书写SQ

3、L的时候理论上无需在乎大小写。在每一段完整SQL语句后增加分号（；）某些数据库会要求在每句SQL后加上分号，某些不需要。SQL单行注释是 （2个-）SQL多行注释是 /*/但在实际开发中可以用：大写表名SQL关键字大写或首字母大写SELECT、FROM、WHERE等关键字前换行每句SQL后都以分号做结尾每句SQL均增加注释等方式增加SQL代码的可读性。数据操作语言DMLlSELECT从数据库表中获取数据lUPDATE更新数据库中表中的数据lDELETE从数据库表中删除数据lINSERT INTO向数据库表中插入数据数据定义语言DDLCREATE DATABASE创建新数据库ALTER DATA

4、BASE修改数据库CREATE TABLE创建新表ALTER TABLE变更（改变）数据库表DROP TABLE删除表CREATE INDEX创建索引（搜索键）DROP INDEX删除索引SELECT单列：SELECT 列名 FROM 表名多列：SELECT 列名,列名, FROM 表名所有列：SELECT * FROM 表名DISTINCTSELECT DISTINCT 列名 FROM 表名返回不重复的结果集WHERE用于规定选择的标准（返回结果集的条件）SELECT 列名 FROM 表名 WHERE 列 运算符 值运算符：=、(!=)、=、=、between、like值若为字符串格式，则应

5、该加单引号 为空判断独有的写法is null、is not nullAND 和 OR运算符在WHERE子句中将多个条件连接起来AND并且OR 或者AND比OR拥有更高的优先级使用时注意配合括号ORDER BY 语句用于根据指定的列对结果集进行排序。默认升序升序为ASC降序为DESCINSERT INTO语句用于向表格中插入新的行。INSERT INTO 表名称 VALUES (值1, 值2,.) -所有列INSERT INTO table_name (列1, 列2,.) VALUES (值1, 值2,.) -选择列Update 语句用于修改表中的数据。语法：UPDATE 表名SET 列名 =

6、新值 , 列名 = 新值WHERE 列名 = 某值;DELETE 语句用于删除表中的行。DELETE FROM 表名WHERE 列名 = 值;删除部分数据使用delete时，最好先使用select查询，检查一下结果集是不是要删除的数据，没有问题再删LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。“% 可用于定义通配符（模式中缺少的文字）。SELECT * FROM Persons WHERE Name LIKE 赵%SELECT * FROM Persons WHERE Name LIKE %美SELECT * FROM Persons WHERE Name LIKE %夫%语法

7、：SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern通配符通配符含义含义%替代一个或多个字符_仅替代一个字符charlist字符列中的任何单一字符charlist或者!charlist不在字符列中的任何单一字符SELECT * FROM Persons WHERE Name LIKE 赵钱孙%SELECT * FROM Persons WHERE Name LIKE !赵钱孙%在ORACLE中只有%和_这两种通配符。 通配符在mySQL等数据库中使用。IN 操作符在 WHERE 子句中规定多个值。SELECT

8、 column_name(s) FROM table_name WHERE column_name IN (value1,value2,.)不在规定多个值之中的使用NOT ININ 操作符在 WHERE选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。（文本只支持字母且区分大小写）SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2开区间还是闭区间因不同数据库而异。不在范围内使用NOT BETWEEN可以为列名或表名指定别名表：SELECT column_name(s) F

9、ROM table_name AS alias_name列：SELECT column_name AS alias_name FROM table_name在ORACLE中可以省略AS关键字，在列或表名后面空格直接加别名。使用别名会使查询程序更容易阅读和书写。给使用了函数后形成的查询字段起别名可以缩短列宽且便于理解从多个表中取数据时使用别名表名更简单从多个表中取数据时可能碰到同名字段，别名有助于区分字段来源主键（Primary Key）是一个列，在这个列中的每一行的值都是唯一的。在表中，每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起。主键P

10、rimary Key外键Foreign Keyselect , lo.order_nofrom LESSON_ORDERS lo, LESSON_PERSONS lpwhere lo.person_id = lp.id这种写法等同于INNER JOIN 为了得到完整的结果，需要从两个或更多的表中获取结果。这时就需要执行 join。select , lo.order_nofrom LESSON_ORDERS lo inner join LESSON_PERSONS lpon lo.person_id = lp.idINNER JOIN: 如果表中有至少一个匹配，则返回

11、行，也可写为JOINLEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行FULL JOIN: 只要其中一个表中存在匹配，就返回行UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2UNION ALL

12、操作符用法和意义与UNION相同。区别在于UNION ALL 命令会列出所有的值。而UNION 操作符选取不同的值。结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。SQL Statement 1 UNION ALL SQL Statement 2TOP 子句用于规定要返回的记录的数目。注意：并非所有的数据库系统都支持 TOP 子句。SQL Server 的语法：的语法：SELECT TOP number | percent column_name(s) FROM table_nameMySQL 语法语法SELECT column_name(s) FROM table_

13、name LIMIT numberOracle 语法语法SELECT column_name(s) FROM table_name WHERE ROWNUM = number例子：M：SELECT * FROM Persons LIMIT 5O：SELECT * FROM Persons WHERE ROWNUM 0 AND City=杭州)单独定义：ALTER TABLE Persons ADD CHECK (Id0);撤销：ALTER TABLE Persons DROP CONSTRAINT chk_PersonDEFAULT 约束用于向列中插入默认值。如果没有规定其他的值，那么会将默认

14、值添加到所有的新记录。在定义数据类型后增加DEFAULT 值，也可以使用函数。如DEFAULT 北京市 DEFAULT GETDATE()单独定义：ALTER TABLE Persons ALTER COLUMN CitySET DEFAULT 北京市撤销：ALTER TABLE Persons ALTER COLUMN City DROP DEFAULTCREATE INDEX 语句用于在表中创建索引。在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。语法：CREATE INDEX index_name ON table_name (column_name);在表上创建一个唯一

15、的索引。唯一的索引意味着两个行不能拥有相同的索引值。CREATE UNIQUE INDEX index_name ON table_name (column_name);创建CREATE INDEX PersonIndex ON Person (LastName) 保留字CREATE INDEX PersonIndex ON Person (LastName DESC) 多列CREATE INDEX PersonIndex ON Person (LastName, FirstName)DROP删除索引、表和数据库索引DROP INDEX index_name表DROP TABLE 表名数据库D

16、ROP DATABASE 数据库名ALTER TABLE 语句用于在已有的表中添加、修改或删除列。增：ALTER TABLE table_name ADD column_name datatype删：ALTER TABLE table_name DROP COLUMN column_name要改变表中列的数据类型，使用下列语法：ALTER TABLE table_name ALTER COLUMN column_name datatype在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们

17、可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句。视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。创建视图：CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition更新视图：CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition删除视图：DROP VIEW view_name函数函数描述描述GETDATE()返回当前日期

18、和时间DATEPART()返回日期/时间的单独部分DATEADD()在日期中添加或减去指定的时间间隔DATEDIFF()返回两个日期之间的时间CONVERT()用不同的格式显示日期/时间SQL Server内建日期函数SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：DATE - 格式 yyyy-MM-ddDATETIME - 格式: yyyy-MM-dd HH:mm:ssSMALLDATETIME - 格式: yyyy-MM-dd HH:mm:ssTIMESTAMP - 格式: 唯一的数字如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有

19、的记录。这意味着该字段将以 NULL 值保存。NULL 值的处理方式与其他值不同。NULL 用作未知的或不适用的值的占位符。注释：注释：无法比较 NULL 和 0或空字符串；它们是不等价的。无法使用比较运算符来测试 NULL 值，比如 =, , 或者 。我们必须使用 IS NULL 和 IS NOT NULL 操作符。Null函数SQL SERVER：SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0) FROM ProductsORACLE：SELECT order_no,Unit_Price*(unit_pric

20、e+NVL(order_num,0) FROM LESSON_ORDERS_BAK函数函数描述描述AVG(column)返回某列的平均值COUNT(column)返回某列的行数（不包括NULL值）COUNT(*)返回被选行数COUNT(DISTINCT column)返回相异结果的数目MAX(column)返回某列的最高值MIN(column)返回某列的最低值SUM(column)返回某列的总和聚合函数（聚合函数（Aggregate functions）Aggregate 函数的操作面向一系列的值，并返回一个单一的值。标量函数的操作面向某个单一的值，并返回基于输入值的一个单一的值。函数函数描述

21、描述UPPER(column)将某个域转换为大写LOWER(column)将某个域转换为小写SUBSTR(c,start,len)从某个文本域提取字符LENGTH(column)返回某个文本域的长度，不分中英LENGTHB(column)返回某个文本域的字符数，1个中文算2个字符INSTR(c,char)返回在某个文本域中指定字符的数值位置，不分中英INSTRB(c,char)返回在某个文本域中指定字符的数值位置，1个中文算2个字符ROUND(c,decimals)对某个数值域进行指定小数位数的四舍五入MOD(x,y)返回除法操作的余数TO_CHAR(d,format)将日期转换为指定格式的文

22、本TO_DATE(c,format)将指定格式的文本转换为日期GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。语法：SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。语法：SELECT column_name, aggregate_function(column_name) FROM tab

23、le_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value好的命名规则增加数据库的可操作性可使开发人员不用阅读文档，对表名望文生义，提高操作、维护效率表名：T_系统_模块_功能_对象视图名：V_系统_模块_功能_对象存储过程名：PRO_系统_模块_功能函数名：F_系统_功能_对象自己新建的文件在开始部分注释作者、新建日期、功能概述等在每个功能点或值得关注的地方前添加注释增加或修改代码要在代码前添加注释，包含操作、作者、日期、

24、功能概述等将常用查询条件或业务主键作为索引会提高查询效率。用户无法看到索引，它们只能被用来加速搜索/查询。更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。但查询时不要使用： 、!=这样的运算符is null、is not null相关函数，如trunc建议：使用num or num来代替 num把需要索引的列设为not null可查询却不可以增删改的列ROWID是行的存储地址，物理主键，快速定位一条记录ROWNUM是结果集中的行号，主要用于限制查询返回的行数对记录条数使用COUNT函数时Count(*)的速度略快于count(1)若对主键（ID）建立了索引

25、，则count(ID)还是最快的使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表select count(*) from LESSON_PERSONS where sex = 男;select count(*) from LESSON_PERSONS where sex = 女;select count(decode(sex,男,m,null) as count_male,sum(decode(sex,女,1,0) as count_female from LESSON_PERSONSAuto-increment 会在新记录插入表会在新记录插入表中时生成一个唯一的数字。中时生成一

26、个唯一的数字。有时候我们希望在每次插入新记录时，自动地创建主键字段的值。这时可以在表中创建一个 auto-increment字段。SQL SERVER：CREATE TABLE Persons ( Id int PRIMARY KEY DENTITY, )默认IDENTITY开始值是 1每行递增 1。要规定 Id 列以 20 起始且递增 10，把 identity 改为 IDENTITY(20,10)INSERT时可直接忽略这个字段。ORACLE：CREATE SEQUENCE seq_person MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10上面的代码创建名为 seq_person 的序列对象，它以 1 起始且以 1 递增。该对象缓存 10 个值以提高性能。CACHE 选项规定了为了提高访问速度要存储多少个序列值。要在 Persons 表中插入新记录，我们必须使用 nextval 函数（该函数从 seq_person 序列中取回下一个值）：INSERT INTO Persons (Id,FirstName,LastName) VALUES (seq_person.nextval,Lars,Monsen)能