数据库-函数

1、 SQL语句
-> 创建(DDL)
-> 创建数据库
-> 创建架构
-> 创建表
-> 操作数据(DML)
-> 增
-> 删
-> 改
-> 查
-> 完整查询语句
-> 执行顺序： from where groupby having select orderby
-> 各个子句细节
-> from 数据源
-> where 逻辑判断 | 多条件处理 | 模糊匹配 | NULL值处理 | 范围处理
-> group by
-> 分组
-> 聚合函数
-> 开窗函数: 聚合函数() over()
-> having
-> select 表达式 | 列别名 | 常量 | 去重复 | top | select可以显式行
-> order by 排序
-> 联合结果集
结果集 union all 结果集
-> 插入多条数据
-> insert into 结果集
-> 2008+ : insert into 表名(列) values(值),(值),(值), ...
-> 表值构造函数(记住语法)
-> select into 语法

-------------------------------------------

-- 常用函数(重点)
-- 数学函数、字符串函数、时间函数、转换函数


-- 数学函数
--ROUND 舍入指定长度与精度(最难)
-- 数字 round(源数字, 精确度:int, 截断标志:int)
select 125.556, ROUND(125.556, 0)
-- 截断参数取值有两个：0、非0
select 123.459, round(123.459, 2, 1)    -- 0 假   非0 真


--FLOOR 地板函数(不超过本身的最大整数)
--CEILING 天花板函数(不小于本身的最小整数)
-- 数字有一个特点，有序
-- 数字 函数(数字)
select 1.2, -1.2, CEILING(1.2), FLOOR(1.2), CEILING(-1.2), FLOOR(-1.2);




--SIGN 返回符号. 等价于：|x|/x 
-- x的值为正数 则sign(x) = 1
-- x的值为负数 则sign(x) = -1
-- x的值为0   则sign(x) = 0


-- "12345-12+45*67"
-- "12345+(-12)+45*67"
-- "1*12345 + -1*12 + 1*45 * 1*67"


select SIGN(123), SIGN(456), SIGN(-789), SIGN(-0.123), SIGN(0);






--SQRT 正平方根(正数)
select SQRT(2);


--PI
select PI();
select 22.0/7;


--SQUARE 平方   Math.Power(3,2)
select SQUARE(3);




--POWER 幂
select POWER(2,30);


--RAND ( [ seed ] ) 随机数
select RAND(0);
-- [0-1) -> [1-10)


-- Random r = new Random(0); --获取系统当前时间作为种子，利用随机算法生成一个数字
-- r.Next();




-------------------------------------
-- 字符串函数
-------------------------------------
/*
增
new string(char[]) 或 new string(char, int)
str.insert(string, int)
str += string
删
str.Remove(int[, int])
str.Trim()
str.TrimEnd()
str.TrimStart()
改
str.Replace(string, string)
str.Substring(int, int)
str.Split(char[], StringSplitOptions)
string.Join(string, string[])
str.ToUpper()
str.ToLower()
查
str.IndexOf(string[, int])
str.IndexOfAny(string[])
str.LastIndexOf()
str.LastIndexOfAny()
str.Contains()
str.StartsWith(string)
str.EndsWith(string)
str.Equals()
string.Compare(string, string)
其他
string.Format()
string.IsNullOrEmpty()
str.Length
*/


----------------
-- len() 字符长度
-- datalength() 字节长度
select '一2三', LEN('一2三'), DATALENGTH('一2三');


-- lower() upper()
select 'aBcDeF', UPPER('aBcDeF'), LOWER('aBcDeF');


-- ltrim() rtrim()
select len('     12345'), len(LTRIM('     12345'))


-- left() right()
-- left
-- 字符串 left(字符串, 数字)
-- 从源字符串的左边开始，截取指定数字个字符，返回
select LEFT('1234567890', 5);
-- right 与left刚好相反
select right('1234567890', 5);


--------------- 
-- 订单
---------------
use HeiMa8;
select * from Exe1.OrdersTable;
-- 订单格式为 JK12345
-- JK00001
-- JK00002
-- JK00003
-- ... ...
select 
* 
, 'JK' + right('00000' + CAST(orderId as varchar(5)), 5)as [订单]
from Exe1.OrdersTable;


-- orderId
-- 123
-- 456
-- 1234
-- 234
-- 102


-- orderId
-- 00000123
-- 00000456
-- 000001234
-- 00000234
-- 00000102


-- orderId
-- 00123
-- 00456
-- 01234
-- 00234
-- 00102


-- substring()
-- 字符串 substring(源字符串, 开始索引, 长度)
-- 索引从1开始
select SUBSTRING('0123456789', 5, 2);




--------------------------------------------
-- 时间函数
--------------------------------------------
-- 获得当前时间
select getdate()
-- 简写形式
-- year(时间), month(时间), day(时间)
select YEAR(GETDATE())
-- 查找年龄在19岁的人
-- year(getdate()) - year(stuBirthdate) = 19
-- select显式数据的时候
-- select 
-- *
-- , year(getdate()) - year(stuBirthdate) as stuAge
-- ...




-- dateadd
-- 在指定时间上的指定部分加上一个数字
-- 时间 dateadd(部分, 数字, 时间基数)
select GETDATE(), DATEADD(DAY, 10, GETDATE());
-- 部分有哪些?
-- 时间数字的使用说明
select '2013-8-7 11:31:30.123', dateadd(MONTH, 4, '2013-8-7 11:31:30.123');
select '2013-8-7 11:31:30.123', dateadd(MONTH, 5, '2013-8-7 11:31:30.123');


select '2000-2-27 11:31:30.123', dateadd(DAY, 2, '2000-2-27 11:31:30.123');


-- dateadd在制定位置加上指定的书，可以用来获得午夜日期   00:00:00.000
select DATEADD(MONTH, 1, '2013-8-7');




-- datediff
-- 数字 datediff(部分, 开始时间, 结束时间)
-- 求学生的年龄
-- select datediff(year, stuBirthdate, getdate()) 
select '2013-2-27 11:31:30.123', DATEDIFF(month, '2013-2-27 11:31:30.123', GETDATE());
-- 2  3  4  5  6  7  8
-- 1  31 30 31 30 31 7
select '2013-2-27 11:31:30.123', DATEDIFF(day, '2013-2-27 11:31:30.123', GETDATE());


-- 查询出上个月最后一天的订单，条件怎么写？
-- 要求返回的肯定是日期 dateadd() 返回日期
-- dateadd(month, -1, '2013-8-31')  得到上个月今天 
-- select dateadd(month, -2, '2013-8-31');
-- 由于最后一天不好控制，找一下规律，在数字上做文章


-- 当前是8月  7 月
-- dateadd(month, -1, '2013-8-31')
-- dateadd(month, 0, '2013-7-31')
-- dateadd(month, 1, '2013-6-30')


-- dateadd(month, datediff(month, '2013-6-30', getdate()), '2013-6-30');
-- 数据库会自动的计算每一个月份的具体天数，我只要规定一个基数日期，是这个月的最后一天
-- 那么数据库就会计算出指定月份的最后一天
select dateadd(month, datediff(month, '2013-7-31', GETDATE()) - 1, '2013-7-31');




-- SELECT DATEADD(DAY,-1 * DAY(GETDATE()),GETDATE())






-- datepart 与简写功能一直，更强大
-- 数字 datepart(部分, 时间)
-- dayName = "星期日|星期一|星期二|星期三|星期四|星期五|星期六".Split('|');
-- dayName[数字]
select DATEPART(WEEKDAY, '2013-8-11');


-- 转换函数 cast convert
-- 基本语法
-- cast(数据或字段 as 数据类型)
-- convert(类型, 数据或字段)
select cast(1 as CHAR(1)) + 'a';
select CONVERT(char(1), 3) + 'a';


-- convert函数针对时间类型有转换为字符串的格式控制
-- convert(字符串类型, 时间, 格式码)
select CONVERT(varchar(20), GETDATE());
select CONVERT(varchar(20), GETDATE(), 101);