---
title: "17-T-SQL程序设计"
created: 2025-11-25
tags:
  - 项目筑基
---
# 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码函数返回值

![[image-0925d9c4.png]]

SELECT ASCII(123)

![[image-c7556548.png]]

SELECT ASCII('A')

![[image-c1666406.png]]

SELECT ASCII('AB')

![[image-c1666406.png]]

SELECT ASCII('A''B')

![[image-71d76781.png]]

/\*对于多个字符 则只返回第一个 比如123 返回1的值   AB返回A\*/

#### char函数：

将ASCII码转换成对应的字符

SELECT CHAR(50) AS ASCII码值对应的字符

![[image-64a2207a.png]]

SELECT CHAR(65)

![[image-f0e6f6f4.png]]

SELECT CHAR(36)

![[image-82048f90.png]]

#### LOWER函数

SELECT LOWER('HELLO WORLD') AS 小写显示

![[image-82048f90.png]]

#### UPPER函数

SELECT UPPER('hello world') AS 大写显示

![[image-8f077c85.png]]

#### 去空格函数

--原

SELECT('  HELLO WORLD  ')

![[image-f21c43b5.png]]

--去左空格 LTRIM函数

SELECT LTRIM('  HELLO WORLD  ') AS 去左空格

![[image-153463ac.png]]

--去右空格 RTRIM函数

SELECT RTRIM('  HELLO WORLD  ') AS 去右空格

![[image-8a8c02f9.png]]

--去两边空格 用个嵌套

SELECT LTRIM(RTRIM('  HELLO WORLD  ')) AS 去两边空格

![[image-5cf8cd7c.png]]

#### 取子串函数

SELECT \* FROM 学生 WHERE LEFT(姓名,1)='张'

![[image-b1a25d3a.png]]

--截取右边 RIGHT（1,2）

SELECT RIGHT('江西服装学院',2) AS 右侧两字符

![[image-b2a81bab.png]]

--截取中间 SUBSTRING（1,2,3） 1：截取对象 2：开始位置 3：截取长度

Mysql中是substr

SELECT SUBSTRING(学号,5,2) AS 班号,姓名 FROM 学生

![[image-a6ec6fb1.png]]

SELECT \* FROM 学生 WHERE 姓名 LIKE'\_美%'

SELECT \* FROM 学生 WHERE SUBSTRING(姓名,2,1)='美'

![[image-3dc5dd93.png]]

注意：RIGHT与定长类型

因为学号使用类型为char 定长 无论满不满 都消耗了12这个长度

未满处用空填充 使用right函数往右开始读取 会读到那些空格

SELECT RIGHT(学号,2)FROM 学生

SELECT RIGHT(学号,5)FROM 学生

![[image-e62fe205.png]]

![[image-8467b913.png]]

### 字符串比较函数

#### CHARINDEX

返回字符串中某子串出现的开始位置 未检索到返回0 检索到了会返回其位置

--CHARINDEX(1,2) 从2里找1  1一定要是2中完完整整存在的内容

select CHARINDEX('student','I am a student') as 子串检测

![[image-4a44def7.png]]

SELECT CHARINDEX('软件',专业班级),姓名 FROM 学生

![[image-aa55630f.png]]

#### PATINDEX

模糊检索 一定要用通配符

select PATINDEX('student','I am a student')

![[image-580880c3.png]]

select PATINDEX('%student','I am a student')

![[image-4fe15332.png]]

select PATINDEX('%stu%t%','I am a student') as 带通配符的子串检测

![[image-a1cbef86.png]]

### 字符操作函数

#### Quotename

quotename（1，（2）） 使用2类型框将字符串1框起来 2可缺省 默认为[]

SELECT QUOTENAME('I am student','()')

![[image-2a4b2ff8.png]]

SELECT QUOTENAME('I am student')

![[image-1ffd32cf.png]]

SELECT QUOTENAME('I am student','''')

![[image-035bff9e.png]]

#### Replicate

replicate（1,n） 将字符串1重复显示n次

SELECT REPLICATE('ABC',3) AS 重复显示

![[image-5c3359d0.png]]

#### Reverse

reverse(1) 将字符串1逆序输出

SELECT REVERSE('HELLO WORLD')

![[image-e025372f.png]]

SELECT REVERSE(姓名) AS 逆序姓名 FROM 学生

![[image-464ae62b.png]]

#### Replace

replace(1,2,3)  1:目标 2：原名称 3：新名称

SELECT 学号,姓名,REPLACE(专业班级,'软件','软件工程') AS 班级 FROM 学生

![[image-1ef358d9.png]]

#### 字符拼接

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 表名

![[image-984f4d19.png]]

对于数据呢 可以使用鱼皮的sql之父 自动生成数据

![[image-20f1c45a.png]]

![[image-38bfd8b9.png]]

这个10是varchar的长度 因为不知道要取多少 所以直接给满

### 数学函数

#### 三角函数

SELECT SIN(180)AS sin,COS(20) AS cos,TAN(45) AS tan,COT(60) AS cot

![[image-5b7199d8.png]]

#### 取整函数

SELECT CEILING(1.1) AS 向上取整

![[image-76759a25.png]]

SELECT FLOOR(1.9) AS 向下取整

![[image-2cae436a.png]]

-- round(1,n) 四舍五入 将1保留n位小数

SELECT ROUND(2.6,0) AS 四舍五入

![[image-9ab0e8a9.png]]

SELECT ROUND(3.1415926,3)

![[image-79fbf027.png]]

 其实 还可以保留10位数……

![[image-aa8968a9.png]]

-2那不就百位嘛 类推

#### 绝对值

SELECT ABS(-125) AS 绝对值

![[image-3a6eafe2.png]]

#### 判断正、负、零

SELECT SIGN(5),SIGN(-5),SIGN(0)

![[image-4f524aac.png]]

#### 方幂

SELECT POWER(2,3) AS 方幂

![[image-62f98bf1.png]]

#### 随机数

SELECT RAND() AS [0-1随机数]

![[image-c03982e6.png]]

### 数据类型转换函数

#### Str

STR 转换为字符串

SELECT STR(1997)

![[image-8e19a650.png]]

#### Cast

CAST(1 AS 2)  将1以2的格式进行显示

SELECT CAST('2022-12-18' AS varchar(4))

![[image-27a0c0fd.png]]

#### Convert

SELECT CONVERT(varchar(100), GETDATE(), 0)

![[image-239112e2.png]]

还有前面聚合查询里面的

### 分组函数

（多行处理函数、聚合函数）

COUNT

SUM

AVG

MIN

MAX

\*自动忽略NULL

### MYSQL增加函数

#### Concat连接

SELECT concat('hello','world');

![[image-4357c375.png]]

#### lpad左填充

select lpad('01',5,'-');

![[image-b78bf223.png]]

#### rpad右填充

select rpad('01',5,'-');

![[image-0ee76fda.png]]

#### trim去头尾空格

select trim(' hello  MYSQL ');

（sqlsever只有 ltrim rtrim 要实现去收尾得嵌套）

![[image-f621194d.png]]

#### ceil向上取整

select ceil(1.1);

Sql sever中是 CEILING  向下取整都一样 都是floor

![[image-71354505.png]]

#### mod求余数

select mod(7,4);

![[image-5698cbc7.png]]

#### curdate当前日期

select curdate();

![[image-00bee942.png]]

#### curtime当前时间

select curtime();

![[image-98cd326d.png]]

#### now当前日期加时间

select now();

![[image-464e942e.png]]

下面几个sql sever也有，但用法可能有些不一样

#### year month day

给定日期 提取出年月日信息

select year(now());

select month(now());

select day(now());

![[image-7ae5d6fc.png]]

#### 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了 这怎么行

![[image-6cdc0f9d.png]]

那为了避免这个现象 就要用到ifnull函数

![[image-22f65189.png]]

当月补助为NULL时 把它当做0来计算

这样就解决了这个问题

\*分组函数自动忽略NULL 如果这里使用sum(月补助) 会得到想要的值

#### format/date\_format

![[image-e808d2b5.png]]

![[image-695a8cbe.png]]

### 一些有趣的组合操作

-- 将所有员工工号统一为五位数 不足五位处用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 教师是可行的

它会把表中 你有多少字段 就全用这个字面值去表示

所以我们就可以做一下有趣的事情

这些函数的结果不也是字面值吗 是可以放在表里面的

![[image-7a16b7f8.png]]

![[image-b14e2117.png]]

那可操作的东西就多了

![[image-71780a54.png]]

![[image-d54c4b43.png]]

（在MYSQL中 这个随机数会生成字段多个 不会跟这里一样 5个字段全是一样的数）

## 结构

### 选择结构

#### IF ELSE

IF(SELECT AVG(学时数) FROM 课程)<=40

PRINT'显示：正常'

ELSE

PRINT'警告：过多！！！

![[image-31a0de2d.png]]

![[image-80acc762.png]]

#### F EXISTS

![[image-13479e80.png]]

![[image-88c9c071.png]]

#### case when then end

（case……end）（when……then）

```sql
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
```

![[image-bcf5f941.png]]

#### 实例

datediff 两个指定时间的差值

--datediff（1,2,3） 1：单位  2:初值  3：终值  结果为3减2

--以天为单位 DD

select DATEDIFF(DD,'2021-10-1','2021-12-1')

![[image-1404b35f.png]]

select DATEDIFF(DD,'2021-12-1','2021-10-1')

![[image-c7ebe922.png]]

--以月为单位 MM

select DATEDIFF(MM,'2021-10-1','2021-12-22')

![[image-889a36c6.png]]

--以年为单位 YY

select DATEDIFF(YY,'2004-11-18','2022-12-19')

![[image-b1b7f8e0.png]]

```
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) + '日'
```

![[image-d0807419.png]]

### 循环结构

WHILE（……） BEGIN BREAK/CONTINUE  END

```sql
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
```

![[image-17ba085d.png]]

---
⬅️ [[16-导入导出|16-导入导出]] 🏠 [[00-数据库|00-数据库]] ➡️ [[18-存储过程|18-存储过程]]