SQL 基础（1）

1.1 TOP,PERSENT, LIMIT,ROWNUM 限制查询结果

SQL Server

SELECT TOP number|percent column_name(s)

FROM table_name;

MySQL

SELECT column_name(s)

FROM table_name

LIMIT number;

Oracle

SELECT column_name(s)

FROM table_name

WHERE ROWNUM <= number;

 

1.2 模糊查询—通配符“_”,”%”,”[]”,”[^]”

%	替代 0 个或多个字符
_	替代一个字符
[charlist]	字符列中的任何单一字符,[0-9][a-z]
[^charlist] 或 [!charlist]	不在字符列中的任何单一字符[^0-9][^a-z]

 

1.3 数值查询

ISNUMBERIC,判断是否是数值，指定数据是数值返回1，否则返回0

SELECT ISNUMBERIC(columnName) FROM tbName

 

CEILING 对指定小数数值向上取整；FLOOR 对指定小数数值向下取整

SELECT CEILING(colName) AS colName1,FLOOR(colName) AS colName2 FROM tbName;

 

ROUND(colName,n) 对指定列数据进行四舍五入，保留n位小数

SELECT ROUND(colName,n) AS colName FROM tbName

ABS(colName) 求指定列数据绝对值

RAND() 生成0-1的小数

SELECT FLOOR(RAND()*10) AS randNum FROM tbName;生成0-10随机数

 

1.4 字符串查询

UPPER,LOWER 大小写转换；

LEN 字符长度

SUBSTRING(colName,index,length) 截取字符长度；

 

STUFF(colName,startIndex,length,insertStr) 删除子字符串并插入新字符串

colName 字段名，startIndex 删除子字符开始索引，length删除子字符串长度，insertStr在索引位插入新的字符串

 

REPLACE(colName,subStr,newStr) 将指定子字符串替换成新字符串；

CHARINDEX(“subStr”,colName)  查找指定子字符串索引；

 

1.5 周期，日期查询

按年月日查询数据，YEAR,MONTH,DAY

SELECT * FROM tbName WHERE YEAR(colName)=’2010’ AND MONTH(colName)=’2’ AND DAY(colName)=’9’

 

GETDATE() 当前日期；NOW 当前系统时间；

DATENAME(WEEKDAY,GETDATE()) 当前星期

 

DATEDIFF(YEAR|MONTH|DAY,date1,date2) 查询指定时间间隔

DATEDIFF接收3个参数，第一个参数用于规定时间差的那一部分，可以是YEAR,MONTH,DAY;第二三个参数都是是datetime或smalldatetime 类型数据

SELECT DATEDIFF(YEAR,出生年月,GETDATE()) AS 年龄 FROM tbName;

 

区间

BETWEEN, NOT BETWEEN;

 

FORMAT() 函数用于对字段的显示进行格式化。

SELECT FORMAT(column_name,format) FROM table_name;

SELECT FORMAT(GETDATE(),’dd/MM/yyyy’) FROM table_name;

2.1 MySQL,SQL Server,Oracle 分页查询

MySQL：mysql大数据量使用limit分页，随着页码的增大，查询效率越低下，因为分页查询是全表查询，然后从查询出的记录中再获取分页数据。

Select badge,name from emp limit 10,50  注释：从10条记录开始取50条数据 limit (page-1)*size,size

  limit语句的查询时间与起始记录的位置成正比

  mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用

可以利用表的覆盖索引来加速分页查询。

覆盖索引所要查询(select)的字段和where条件都只用建立了索引的字段，只在索引中查询就能完成查询工作，不用全表查询，这样节省了很多时间。另外Mysql中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。

 

SQL Server: a,2005以上;b:2012以上

1. 利用 row_number() over(order by id) 函数计算出相应的行数，选定相应行数返回

 select top size * from (select row_number() over(order by id) as rownumber，* from tablename) temp_row

 where rownuber> (page-1)*size

1. Offset/fetch next

 Select * from tablename order by id offset (page-1)*size fetch next size rows only

 

Oracle: a:效率高

SELECT * FROM(

SELECT A.*,ROWNUM as RN FROM (

SELECT * FROM tablename

WHERE 条件

ORDER BY 排序) AS tb

WHERE ROWNUM<= (page*size))

)

WHERE RN>(page-1)*size

 

B:

SELECT * FROM(

SELECT A.*,ROWNUM RN

FROM(SELECT * FROM tb) A

)

WHERE (page-1)*size<RN AND RN<= (page)*size

             

 

2.2 存储过程

SQL Server 查询存储过程

Create procedure page_procedure(

       @page int,

       @size int,

) as

begin

       Select top (select @size) * from ( - 这里注意一下，不能直接把变量放在这里，要用select

select row_number() over (order by id) as rownumber ,* from tablename) temp_row

where rownumber>（@page-1）*@size

end

执行 exec page_procedure @page=2,@size=10

2.3 DDL,DCL,DML,DQL

DDL：数据定义语言 create,alter,drop;

DCL：数据控制语言 commit,rollback,savepoint,grant 权限 to user;

DML：数据操纵语言 insert,update,delete;

DQL：数据查询语言 select;

2.4 事务

2.5 SQL 优化

Mysql能够处理的优化类型：

  选择最合适的字段属性(字段类型)

数据库中的表越小，在它上面执行的查询也就越快。在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度舍得尽可能小；另外若是字段长度不是固定长的化，尽量使用varchar,可变长，按照实际存储的长度。

  尽量把字段设置长NOT NULL

查询的时候不用去判断是否为NULL, 对于null的判断会导致引擎放弃使用索引而进行全表扫描；

  避免函数索引，会导致索引失效，直接全表扫描；

  尽量避免LIKE ‘%XX%’, 会导致索引失效而进行全表扫描；

在MySQL中，有全文索引来帮助模糊匹配，创建全文索引的SQL：

ALTER TABLE `table_name` ADD FULLTEXT INDEX `index_name` (`col_name`);

使用全文索引的SQL:

select col_name from table_name where match(col_name) against(‘模糊关键字' in boolean mode);

  查找需要的字段（非*）并读取适当的记录LIMIT,M,N,避免一下返回大量数据，增加很多不必要的消耗

  可用IN代替OR,OR可能会导致索引失效，需要全表扫描；但IN的数值不应过多；

or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果。

  适当的使用索引，对于经常用于join,where,group by,order by 的字段可以创建索引；

  避免隐式类型转换

尽量避免，联表的ON 条件 和where ，having 条件出现字段类型与值类型不一致的情况，会导致索引失效，需要全表扫描；

  尽量避免非必要的排序，可在后台进行排序的尽量在后台处理排序问题；

  做MySQL优化，要善用EXPLAIN查看SQL执行计划

  避免在where中对字段进行表达式操作，会造成引擎放弃使用索引，例如 where age*2=36 可换成 age=36/2