T-SQL程序设计
ASCII码表
| 000 | 001 | 010 | 011 | 100 | 101 | 110 | 111 | |
| 0000 | 0 | P | p | |||||
| 0001 | 1 | A | Q | a | q | |||
| 0010 | 2 | B | R | b | r | |||
| 0011 | 3 | C | S | c | s | |||
| 0100 | 4 | D | T | d | t | |||
| 0101 | 5 | E | U | e | u | |||
| 0110 | 6 | F | V | f | v | |||
| 0111 | 7 | G | W | j | w | |||
| 1000 | 8 | H | X | h | x | |||
| 1001 | 9 | I | Y | i | y | |||
| 1010 | J | Z | j | z | ||||
| 1011 | K | k | ||||||
| 1100 | L | l | ||||||
| 1101 | M | m | ||||||
| 1110 | N | n | ||||||
| 1111 | O | o |
数字从第三列第零行开始 大写字母从第四列第一行开始 小写字母从第六列第一行开始
数字<大写字母<小写字母
数字的ASCII码值等于30H+它本身（‘5’=30H+5=35H=3*16+5=53D ）
其实就是 30H为 00110000 代表着数字0 之后数字以此类推就行了
记住0是30H
大小写同字母相差两列（20H/32D） （‘A’+20H=‘a’）
20H=32D 一列16（0000~1111） 相差两列就32D换算20H
A 0100 0001（B） 41（H） 65（D）
a 65+32=97
函数
字符转换函数
ASCII函数：
将符号转换成对应ASCII码值
SELECT ASCII(1) AS ASCII码函数返回值
SELECT ASCII(123)
SELECT ASCII('A')
SELECT ASCII('AB')
SELECT ASCII('A''B')
/*对于多个字符 则只返回第一个 比如123 返回1的值 AB返回A*/
char函数：
将ASCII码转换成对应的字符
SELECT CHAR(50) AS ASCII码值对应的字符
SELECT CHAR(65)
SELECT CHAR(36)
LOWER函数
SELECT LOWER('HELLO WORLD') AS 小写显示
UPPER函数
SELECT UPPER('hello world') AS 大写显示
去空格函数
--原
SELECT(' HELLO WORLD ')
--去左空格 LTRIM函数
SELECT LTRIM(' HELLO WORLD ') AS 去左空格
--去右空格 RTRIM函数
SELECT RTRIM(' HELLO WORLD ') AS 去右空格
--去两边空格 用个嵌套
SELECT LTRIM(RTRIM(' HELLO WORLD ')) AS 去两边空格
取子串函数
SELECT * FROM 学生 WHERE LEFT(姓名,1)='张'
--截取右边 RIGHT（1,2）
SELECT RIGHT('江西服装学院',2) AS 右侧两字符
--截取中间 SUBSTRING（1,2,3） 1：截取对象 2：开始位置 3：截取长度
Mysql中是substr
SELECT SUBSTRING(学号,5,2) AS 班号,姓名 FROM 学生
SELECT * FROM 学生 WHERE 姓名 LIKE'_美%'
SELECT * FROM 学生 WHERE SUBSTRING(姓名,2,1)='美'
注意：RIGHT与定长类型
因为学号使用类型为char 定长 无论满不满 都消耗了12这个长度
未满处用空填充 使用right函数往右开始读取 会读到那些空格
SELECT RIGHT(学号,2)FROM 学生
SELECT RIGHT(学号,5)FROM 学生
字符串比较函数
CHARINDEX
返回字符串中某子串出现的开始位置 未检索到返回0 检索到了会返回其位置
--CHARINDEX(1,2) 从2里找1 1一定要是2中完完整整存在的内容
select CHARINDEX('student','I am a student') as 子串检测
SELECT CHARINDEX('软件',专业班级),姓名 FROM 学生
PATINDEX
模糊检索 一定要用通配符
select PATINDEX('student','I am a student')
select PATINDEX('%student','I am a student')
select PATINDEX('%stu%t%','I am a student') as 带通配符的子串检测
字符操作函数
Quotename
quotename（1，（2）） 使用2类型框将字符串1框起来 2可缺省 默认为[]
SELECT QUOTENAME('I am student','()')
SELECT QUOTENAME('I am student')
SELECT QUOTENAME('I am student','''')
Replicate
replicate（1,n） 将字符串1重复显示n次
SELECT REPLICATE('ABC',3) AS 重复显示
Reverse
reverse(1) 将字符串1逆序输出
SELECT REVERSE('HELLO WORLD')
SELECT REVERSE(姓名) AS 逆序姓名 FROM 学生
Replace
replace(1,2,3) 1:目标 2：原名称 3：新名称
SELECT 学号,姓名,REPLACE(专业班级,'软件','软件工程') AS 班级 FROM 学生
字符拼接
Mysql 中
字符串拼接函数Concat(a,b)
--姓名首字母大写
Select concat(upper(substr(name,1,1)),substr(name,2,length(name)-1)) as result from student
Sqlsever也能实现 str()+str() 先转变成字符类型才能用“+”连接（已经是字符类型则不需换）
但是它没有length取长度的函数所以首字母后该去多少就很难搞 干脆往大了
因为懒得创表 从mysql里拿了一下这些数据
查找创表时所用语句 使用：show create table 表名
对于数据呢 可以使用鱼皮的sql之父 自动生成数据
这个10是varchar的长度 因为不知道要取多少 所以直接给满
数学函数
三角函数
SELECT SIN(180)AS sin,COS(20) AS cos,TAN(45) AS tan,COT(60) AS cot
取整函数
SELECT CEILING(1.1) AS 向上取整
SELECT FLOOR(1.9) AS 向下取整
-- round(1,n) 四舍五入 将1保留n位小数
SELECT ROUND(2.6,0) AS 四舍五入
SELECT ROUND(3.1415926,3)
其实 还可以保留10位数……
-2那不就百位嘛 类推
绝对值
SELECT ABS(-125) AS 绝对值
判断正、负、零
SELECT SIGN(5),SIGN(-5),SIGN(0)
方幂
SELECT POWER(2,3) AS 方幂
随机数
SELECT RAND() AS [0-1随机数]
数据类型转换函数
Str
STR 转换为字符串
SELECT STR(1997)
Cast
CAST(1 AS 2) 将1以2的格式进行显示
SELECT CAST('2022-12-18' AS varchar(4))
Convert
SELECT CONVERT(varchar(100), GETDATE(), 0)
还有前面聚合查询里面的
分组函数
（多行处理函数、聚合函数）
COUNT
SUM
AVG
MIN
MAX
*自动忽略NULL
MYSQL增加函数
Concat连接
SELECT concat('hello','world');
lpad左填充
select lpad('01',5,'-');
rpad右填充
select rpad('01',5,'-');
trim去头尾空格
select trim(' hello MYSQL ');
（sqlsever只有 ltrim rtrim 要实现去收尾得嵌套）
ceil向上取整
select ceil(1.1);
Sql sever中是 CEILING 向下取整都一样 都是floor
mod求余数
select mod(7,4);
curdate当前日期
select curdate();
curtime当前时间
select curtime();
now当前日期加时间
select now();
下面几个sql sever也有，但用法可能有些不一样
year month day
给定日期 提取出年月日信息
select year(now());
select month(now());
select day(now());
date_add
-- date_add（date，INTERVAL expr type）
-- 指定日期的基础上增加一个时间间隔之后的时间值
-- INTERVAL必须要有 expr：我们指定的时间 type：单位
select date_add(now(),INTERVAL 70 year );
select date_add(now(),INTERVAL 20 day );
select date_add(now(),INTERVAL 4 month);
datediff
两个指定时间之间相差的天数 (第一个减第二个）
select DATEDIFF('2021-12-1','2021-10-1');
select DATEDIFF('2021-10-1','2021-12-1');
在sql sever中 datediff需要三个空 （a，b，c） a中用dd mm yy来表示单位
if
判断第一个值正确与否来选择执行其后操作 true执行1 false执行2
select if(true,'ok','error');
select if(false,'ok','error');
select if(1>2,'ok','error');
select if('b'>'a','ok','error');
*ifnull
判断某值是否为空 不为空返回该值 为空返回第二个值
理解为当数据为NULL时 就把这个数据当做哪个值
select ifnull('OK','Default');
select ifnull('','Default'); -- 空格不是null
select ifnull(null,'Default');
有一个注意的点 在数据库中的计算 只要有null存在 它的结果就一定是null
（相当于0×任何数都为0 但它更严格 拓展到其他任何运算 null+1也等于null ）
举个例子 算年薪 年薪=（月薪+月补助）*12
在数据表中 月补助可能是null 那这样的话 年薪就成null了 这怎么行
那为了避免这个现象 就要用到ifnull函数
当月补助为NULL时 把它当做0来计算
这样就解决了这个问题
*分组函数自动忽略NULL 如果这里使用sum(月补助) 会得到想要的值
format/date_format
一些有趣的组合操作
-- 将所有员工工号统一为五位数 不足五位处用0填充
UPDATE emp set workno = lpad(workno,5,'0');
select * from emp;
-- 生成六位随机验证码
select rpad(round(rand()*1000000,0),6,'0');
-- 查询所有员工的入职天数 并据此倒序排序
select name,datediff(curdate(),entrydate) from emp;
select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;
发现
我们以上的操作都没有跟表挂钩 都只是单独的select 能不能挂钩呢？
可以
select后面通常是接表中的字段名
也可以接字面值，比如字符串或者数字 但是结果为该字面值组成的字段表
比如 select abc from emp 会报错 因为它会把abc当成一个列名 但是表中不存在这个列
但是 select 'abc' from 教师
和 select 1 from 教师是可行的
它会把表中 你有多少字段 就全用这个字面值去表示
所以我们就可以做一下有趣的事情
这些函数的结果不也是字面值吗 是可以放在表里面的
那可操作的东西就多了
（在MYSQL中 这个随机数会生成字段多个 不会跟这里一样 5个字段全是一样的数）
结构
选择结构
IF ELSE
IF(SELECT AVG(学时数) FROM 课程)<=40
PRINT'显示：正常'
ELSE
PRINT'警告：过多！！！
F EXISTS
case when then end
（case……end）（when……then）
select
id,
name,
(case when math>=85 then '优秀'
when math >=60 then '及格'
else '不及格' end) as '数学',
(case when english>=85 then '优秀'
when english >=60 then '及格'
else '不及格' end) as '英语',
(case when chinese>=85 then '优秀'
when chinese >=60 then '及格'
else '不及格' end) as '语文'
from score
实例
datediff 两个指定时间的差值
--datediff（1,2,3） 1：单位 2:初值 3：终值 结果为3减2
--以天为单位 DD
select DATEDIFF(DD,'2021-10-1','2021-12-1')
select DATEDIFF(DD,'2021-12-1','2021-10-1')
--以月为单位 MM
select DATEDIFF(MM,'2021-10-1','2021-12-22')
--以年为单位 YY
select DATEDIFF(YY,'2004-11-18','2022-12-19')
DECLARE @day int,@month int,@year int
DECLARE @birthday datetime,@today datetime,@yearadd datetime,@monthadd datetime
set @birthday='2004-11-18'
set @today='2022-12-19'
SELECT @year=DateDiff(yy,@birthday,@today)
if(month(@today)<month(@birthday))
set @year=@year-1
set @yearadd=DATEADD(yy,@year,@birthday)
select @month=DATEDIFF(mm,@yearadd,@today)
if(DAY(@today)<DAY(@birthday))
set @month=@month-1
set @monthadd=DATEADD(mm,@month,@yearadd)
set @day=DATEDIFF(dd,@monthadd,@today)
SELECT '一共'+ STR(@year) + '年'
+ STR(@month) + '月'
+ STR(@day) + '日'
循环结构
WHILE（……） BEGIN BREAK/CONTINUE END
DECLARE @num int,@i int
set @i=1
SELECT @num=count(*) from 利滚利
WHILE(@i<=@num)
BEGIN
DECLARE @capital real,@total real,@years real
SELECT @capital=本金 FROM 利滚利 where ID=@i
set @years=0
set @total=@capital
WHILE(@total<100000)
BEGIN
SET @total=@total*(1+0.05)
set @years=@years+1
END
UPDATE 利滚利
set 总额=@total,年限=@years
WHERE ID=@i
SET @i=@i+1
END
💬 评论