dev-train-doc/sql/03.singletable.md

单表操作

增删查改

增

/* 插入用户输入数据的记录 */
insert into testTable1 (id, name, age, joindate, note) --列名列表必须和数据保持一一对应，不插入数据的字段必须是可以为null的数据列
values
(1, 'Tony', 18, '2021/04/10', 'A'),
(2, '小船', 21, '2021/04/10', 'center'),
(3, '航咪', 19, '2021/04/10', 'A'),
(4, '小连', 23, '2021/04/10', 'A'),
(5, 'JoJo', 23, '2021/04/10', 'N'),
(6, '大龙', 20, '2021/04/10', 'A'),
(7, '星星', 23, '2021/04/10', 'A'),
(8, '妖僧', 24, '2021/04/10', 'B'),
(9, '隽宝', 24, '2021/04/10', 'A')

/* 从数据库表中插入数据 */
--创建临时表
create table #temp
(
	id int not null primary key,
	name nvarchar(50) not null,
	age int not null,
	joindate date null,
	note nvarchar(50) null
)
insert into #temp --括号和字段列表可省略；当省略括号和字段时，插入数据必须与字段一一对应
values (10,'厂花', 23, '2021/04/10', 'N')
--插入数据
insert into testTable1 
select * from #temp --插入其他表中已有数据时，两张表的数据结构必须相同

/* 自动创建表并添加数据 */
select * into #testInsert --执行前表#testInsert不能存在，执行后会自动创建表并将testTable1的数据结构和行数据都复制给#testInsert
from testTable1

select * from #testInsert 
order by id

插入记录后的结果

新建并插入数据后的结果

删

delete from testTable1 
where id > 9 --如果不填写where条件，则删除表中所有行数据

删除 id > 9 的记录后的结果

查

/* 查询行数据 */
select id, name, age, joindate, note --选择要查询的字段，用*表示所有字段
from testTable1
where age > 20 --筛选出符合条件的数据
order by id --默认升序排序

/* 汇总查询 */
select note as 等级, avg(age) 平均年龄 --可以在查询结果中重命名列，在列名后面空格[+as]+新列名；分组查询时，结果列必须为 group by 后面的列，或者被聚合函数处理的列
from testTable1
group by note --按照note列对行数据进行分组，结果为分组汇总数据，而不是行数据
having note in ('A', 'B', 'C', 'N') --对分组后的数据进行筛选

/* 去重查询 */.
select distinct age, joindate --distinct 关键字会匹配后面的所有列
from testTable1 
order by age 

/* 查询处理后的数据 */
select id, 'xls-'+name name, --拼接字符串
	(case when age > 18 then 1 else 0 end) 是否成年, --通过判断数据获取某种结果
	datediff(day, joindate, getdate()) 时间间隔, --通过函数获取结果
	note
from testTable1

查询年龄大于20的记录

分组汇总的结果

去重查询的结果

查询处理后的结果

改

/* 自定义更新后的值 */
update testTable1 set note = 'A', name = '船船子'  --可同时更新多个字段
where name like '%船%' --如果不填写where条件，更新表中所有行的备注为'A'，where条件筛选出数据库表中 name 包含“船”的记录

/* 更新为空 */
update testTable1 set note = null -- set 值为空时用 "=" ，不用 is ，和 where 条件区别开
where id = 9

更新 name 包含“船”的记录后的结果

更新备注为空后的结果

where条件

比较

--唯一确定
select * 
from testTable1 
where id = 5 --需要根据主键或唯一索引才能唯一确定一条数据

--不相等
select * 
from testTable1 
where note <> 'A' --<> 和 != 都是不等于，是等价的

select * 
from testTable1 
where note != 'N'

--大小
select * 
from testTable1 
where age > 20 

集合

--在某个集合中
select * 
from testTable1 
where name in ('Tony', '小船')

select * 
from testTable1 
where age between 18 and 21 --between and 包含两个边界值，相当于闭区间

--不在某个集合中
select * 
from testTable1 
where note not in ('B', 'C', 'N')

select * 
from testTable1 
where age not between 18 and 21 --和 between and 相反，相当于闭区间的补集

字符匹配

--更多种格式见通配符一节
--符合某种格式
select * 
from testTable1 
where name like '%小%' --筛选名字中包含"小"的记录

--不符合某种格式
select * 
from testTable1 
where name not like '__' --筛选名字不是两个字符的记录

空值

--取为空的记录
select * 
from testTable1 
where note is null

--取不为空的记录
select * 
from testTable1 
where note is not null

逻辑

--优先级：() > not > and > or
--1：备注为B或N 或 id在前五且年龄大于22
select * 
from testTable1 
where note not in ('A', 'center')
  or not(id > 5)
  and age > 22
  
--2：(备注为B或N)或id在前五 且 年龄大于22
select * 
from testTable1 
where (note not in ('A', 'center')
  or not(id > 5))
  and age > 22
  
--3：(备注为B或N)且id在前五 或 年龄大于22
select * 
from testTable1 
where note not in ('A', 'center')
  and not(id > 5)
  or age > 22
 
--4：备注为B或N 且 id在前五或年龄大于22
select * 
from testTable1 
where note not in ('A', 'center')
  and (not(id > 5)
  or age > 22)

表中数据：

查询结果：

通配符

在查询数据库中的数据时，配合 like 运算符一起使用，用于匹配符合某种格式的数据。

通配符可以替代一个或多个字符。

在 SQL 中，可使用以下通配符：_、%、[]、[^]或[!]。

_

"_"表示一个字符

select * 
from testTable1 
where note not like '_' --匹配 note 不是一个字符的数据

select * 
from testTable1 
where name like '_o__' --匹配第二个字符为 o 总共4个字符的数据

	
select * 
from testTable1 	--即使不是字符类型的列也可以匹配		
where age like '1_' --匹配第一个字符是1开头且只有两个字符的数据

%

"%"表示0或n个字符

select * 
from testTable1 
where name like 'J_%' --匹配 name 列以 J 开头，至少有两个字符的数据

select * 
from testTable1			 --匹配时也会匹配null值
where age not like '%3%' --匹配 age 列不包含3的数据

select * 
from testTable1		 --匹配时不区分大小写
where note like 'C%' --匹配 note 列以 C 开头的数据

[]

"[]"判断某个字符是不是子字符列表中的某个值

select * 
from testTable1 
where name like '[大小宝]_' --匹配 name 列第一个字是 大或小或宝 ，共有两个字符的数据

select * 
from testTable1 
where age like '%[13579]%' --匹配 age 列中包含奇数的数据

select * 
from testTable1 
where note not like '%[mn]%' --匹配 note 列不包含mn的数据

[^] 或 [!]

"[^]"或"[!]"判断某个字符是否不等于字符列表中的值

select * 
from testTable1 
where name like '%[^龙妖]%' --匹配 name 列中不包含 龙和妖 的数据

select * 
from testTable1 
where age like '[^1]%' --匹配 age 列不以1开头的数据

select * 
from testTable1 
where note not like '[!abc]' --匹配 note 列为 A或B或C 的数据，双重否定等于肯定