单表：增删改查 / WHERE字句 / 运算&函数 / NULL / 事务·源栈课堂·一起帮

单表：增删改查 / WHERE字句 / 运算&函数 / NULL / 事务

当前系列： SQL

已有表：

CREATE TABLE Student 
( 
	SName VARCHAR(50),
	Age INT,
	Enroll DATE,
	IsMale BIT
);

增

又称之为“插入（INSERT）”，标准写法：

-- Student表现有两列：SName 和 age
INSERT Student(SName, Age, Enroll, IsMale) VALUES('atai',23,'2022-11-17', 1);

语法注意事项：

关键字INSERT（插入），空格
后接SQL表名，圆括号中包含列名，用逗号（,）分隔，空格
再写关键字VALUES，圆括号中按顺序输入每一列的值（和之前Student表（）中的列一一对应）
文本和日期类型数据，需要用单引号（''）括起来；（尤其是，并）

	T-SQL	mysql
日期，忘了单引号，比如：2022-11-17	不会报错，而是按数值进行运算，并转换成（错误）日期	报错

当VALUES中包含了全部列的数据时，可以省略Student之后的括号：

INSERT Student VALUES('atai',23,'2022-11-17', 1);

或者，我们可以只插入某一列的数据：

INSERT Student(SName) VALUES('atai');

注意：没有赋值的列为NULL值。

但行数据不能和表结构定义（definition）相冲突！

演示报错：

SName的行数据中有NULL值，修改该列为NOT NULL
SName定义为NOT NULL，插入NULL值数据

T-SQL	mysql
Unicode编码的文本，前面还要加上一个N： INSERT Student(SName) VALUES(N'阿泰');	完全不需要

查

使用SELECT，可以查询得到表数据，比如：

SELECT * FROM Student;

其中，星号（*）代表所有列，等同于：

SELECT SName, Age, Enroll, IsMale FROM Student;

运行上述SQL语句，返回的就是Student表的所有行所有列的内容。

我们也可以指定其中的某些列：

-- 只查出Age列的数据
SELECT Age FROM Student;

SELECT还可以显示出在原表列的基础上进行运算的结果，比如：

SELECT Age+1 FROM Student

所以，我们还可以使用AS（可省略）给它指定一个名称：

SELECT Age+1 AS BigAge FROM Student; 
SELECT Age+1 BigAge FROM Student; --省略掉AS

AS也可以在已有列上使用，改变列名的显示（改变过后的列名又被称之为“别名”）：

SELECT Id AS Number FROM Student; --Number就是Id的别名 
SELECT Id Number FROM Student; --AS一样是可以省略的

注意：SELECT只是改变的呈现结果，永远不会更改数据本身。

最后，通配符*还可以和指定列一起共同使用：

SELECT Age+10 AS BigAge, * FROM Student;

WHERE子句

可以在SELECT（以及接下来要学习的UPDATE和DELETE）后面添加WHERE子句，以进行条件查询和精确更新/删除。

比如，我们要查询所有年龄（Age）大于18岁的同学：

SELECT * FROM Student WHERE Age > 18;

Age > 18：一个能返回真/假（boolean）值的运算表达式（复习）。

SQL中使用的比较运算符和其他编程语言类似：（使用SELECT 1 WHERE 演示）

大于小于：>, <, =，
大于等于、小于等于：>=, <=,
等于是单等号=，不是双等号==。注意赋值和比较都适用的=，要根据它出现的位置和上下文，进行判断。
不等于：<>或 !=。<> 仅出现在SQL中，而且是“标准”的
在...之间（包含边界值）：BETWEEN...AND..，比如：6 BETWEEN 5 AND 7，结果为真；8 BETWEEN 5 AND 7，结果为假。

上述运算符可以比较数值和日期，以及文本。

其中，文本的比较是按字符的编码。

	T-SQL	mysql
文本的末尾空格	会被忽略，即：'abc' = 'abc '的运算结果为真。	不会被忽略

逻辑运算

不使用符号，直接使用英语单词：

NOT：“非”，或者“取反”

(NOT 3<2) -- 3<2为假，NOT之后为真

AND：“且”

(3>2 AND 5>3 AND '2019/10/14'<'2019/11/23') --全部为真，AND之后还是为真 
(3>2 AND 5>3 AND '2019/10/14'>'2019/11/23') --一个为假，AND之后就为假

OR：“或”

(3>2 OR 5<3 OR '2019/10/14'>'2019/11/23') --只要一个为真，OR之后就是为真 
(3<2 OR 5<3 OR '2019/10/14'>'2019/11/23') --只有全部为假，OR之后才为假

#体会#：BETWEEN...AND...实际上也就是两个表达式的AND运算

LIKE

通配符：

% ：任意字符
_：单个任意字符（包含空格' '？）

IF( 'ABCD' LIKE '_BC%')

转义符（ESCAPE）

如果要查文本里有没有%呢？我们需要：

使用ESCAPE指定一个转义字符
在%前加上这个转义字符，告诉SQL Server不要认为%代指任意字符

IF( '源栈87%的就业率' LIKE '%%%') -- 这样写是不行的，%会被认为是“任意字符” 
IF( '源栈87%的就业率' LIKE '%#%%' ESCAPE '#') -- 转义字符为#

除了%和_，还有[]^都是需要转义的。注意UNICODE文本任然需要在单引号前面加N，如：N'源栈欢迎您！'

删

注意这里的删，删除的是表里面的行数据，不是表本身！

注意：mysql workbench需要修改Safe Update默认设置

可以用两种方式删除表数据：

DELETE（T-SQL中可省略FROM）

--删除Student表的所有行
DELETE FROM Student;

TRUNCATE

--注意有TABLE关键字
TRUNCATE TABLE Student;

常见面试题：TRUNCATE和DELETE有什么区别呢？

TRUNCATE只能删除整张表数据，而DELETE是可以跟条件的（WEHRE字句），比如删除Age小于20的同学：
```
DELETE Student WHERE Age < 20;
```
TRUNCATE更快捷更干净：DELETE归根结底还是按行操作的，TRUNCATE是按表操作的

参考：（设计到日志/事务/缓存……需要同学们在学习完成SQL课程后才能理解，^_^）

改

有被称之为“更新”，使用UPDATE，比如可以将某一列的值更改为一个固定值

-- 把Student的Age列上所有值改为18
UPDATE Student SET Age = 18;

或者同时更改好几列：

UPDATE Student SET Age = 18, SName='xj';

也可以让列本身参与运算，比如：

UPDATE Student SET Age = Age + 18; -- Age列所有值都加上18 
UPDATE Student SET Score = Score + Age; -- Score列所有值都加上Age的值

#体会#：SQL的“集合运算”特性，它直接了当的在Age列所有行上进行运算，不需要像其它一些编程语言那样，进行循环迭代，一行一行的取出数据再进行运算……

以上都会更新表中所有行数据，和DELETE语句一样，通常都会带着WHERE子句。比如：给所有入学时间（Enroll）在9月的同学成绩加上10分：

UPDATE Student SET Age += 10 WHERE Enroll BETWEEN '2017/8/31' AND '2017/10/1';

运算

大体上来说，SQL中常用的运算和其他编程语言类似：

赋值：=，把等号（=）右边的值赋值给左边的列（变量）
算术：加减乘除：+-*/，取余：%
组合运算符：+=、-=、*=、/=
圆括号()，和数学运算一样，最高优先级
文本拼接+：T-SQL和mysql不兼容，尽量使用函数CONCAT()

函数

复习/参考：JavaScript / J&C字符串 / C#类库 / Java类库……

数值函数

四舍五入：ROUND(value, n) ；
取整：FLOOR(value) / CEILING(value)；
随机：RAND()

字符串函数

拼接：CONCAT(a, b)
大小写：LOWER(value) / UPPER(value)；
截取：LEFT(string, n) / RIGHT(string, n) / SUBSTRING(string, start, length)
修剪：LTRIM(string) / RTRIM(string)，左边空格/右边空格
替换：REPLACE(string, target, substitute) 在string找到target，用substitute替换它
空格填充：SPACE(n)，生成n个空格

	T-SQL	mysql
取长度	LEN()	length()
查找：在string中找到target，返回其所在下标，找不到返回0	CHARINDEX(target, string)	instr(string, target)
重复：将string重复n次	REPLICATE(string, n)	repeat(string, n)

日期函数

年月日：YEAR()/MONTH()/DAY()

	T-SQL	mysql
当前时间	GETDATE()	now()
在date的上增加number个datetype（YEAR/MONTH/WEEK/DAY……）	DATEADD(datetype,number,date)	date_add(date, interval number datetype)
start和end之间有多少datetype（mysql只有DAY）	DATEDIFF(datetype, start, end)	datediff(start, end)

数据类型转换

	T-SQL	mysql
CAST(express AS datatype)	datatype同建表时声明	不一定同建表时声明，比如不是varchar而是char，不是int而是signed（带正负符号的）/unsigned（不带正负符号的），
CONVERT：	(datatype, express)	(express, datatype) 参数位置区别

特殊的NULL

飞哥专门做了一个总结，NULL值和任何值（包括NULL值）：

进行比较，其结果既不为真，也不为假，一定要说的话，为：unkown（未知）

SELECT 1 WHERE(NULL > 0); 
-- mysql：SELECT 1 as result……更清晰
SELECT 1 WHERE(NULL < 0);
SELECT 1 WHERE(NULL = 0);
SELECT 1 WHERE(NULL <> 0); 
SELECT 1 WHERE(NULL = '');
SELECT 1 WHERE(NULL = NULL); 
SELECT 1 WHERE(NULL <> NULL);

为什么不直接说结果为false？取反试试：
```
SELECT 1 WHERE(NOT NULL = 0);
```
进行运算，其结果总是为NULL
```
SELECT NULL+23;
SELECT NULL+'23';
```

那么，如何判断一个值是不是NULL呢？我们只能使用 IS NULL：

SELECT 1 WHERE(NULL IS NULL);

NULL值的特殊性将在以后的排序、聚合运算、集合运算……中不断体现

事务

@想一想@：怎么样才能避免“删库跑路”？数据库操作能不能Ctrl+Z？

只要我们把SQL语句放在事务（transaction）中，就可以选择提交（commit）或回滚（rollback），回滚就类似于Ctrl+Z的功能。

显式的开始一个事务：

T-SQL	mysql
BEGIN TRANSACTION --或者 BEGIN TRAN 说明：T-SQL中很多关键字都可以简写成前4个字符	BEGIN -- 不是可以，而是必须省略TRAN START TRANSACTION -- 或者

这样，就可以将其后的SQL语句处于一种“悬置”状态，比如：

DELETE FROM Student;

执行上述SQL语句，查看Student表的内容，似乎已经被删除了。但其实不是的，运行：

ROLLBACK; -- 回滚事务

然后我们会发现Student表又回来了！这就是事务的回滚。

PS：事务的回滚一般通过日志文件实现。

如果要真的删除Student表里的内容，我们需要继续调用：

COMMIT; -- 提交事务

提交之后的事务就再也无法回滚了！

PS：按SQL标准，

表结构的修改（Data Definition Language，数据库操作语言）是不受事务控制的，（）
受事务控制的是对行数据的修改（Data Manipulation Language，数据操作语言）

而TRUNCATE又属于DDL，所以……

mysql仍然遵循这一规则，但SQL Server的高版本突破了这一限制。

事务的更多内容我们会再后面继续学习。

作业

在User表中插入以下四行数据：

UserName	Password
17bang	1234
Admin	NULL
Admin-1
SuperAdmin	123456

将Problem表中的Reward全部更新为0
使用事务，

删除User表中的全部数据，
回滚事务，撤销之前的删除行为

在User表中：
1. 查找没有录入密码的用户
2. 删除用户名（UserSName）中包含“Admin”或者“17bang”字样的用户
在Problem表中：
1. 给所有悬赏（Reward）大于10的求助标题加前缀：【推荐】
2. 将为负数的Reward值全部变成相应的正数（绝对值）
3. 给所有悬赏大于20且发布时间（Created）在2019年10月10日之后的求助标题加前缀：【加急】
4. 删除所有标题以中括号【】开头（无论其中有什么内容）的求助
5. 查找Title中第5个起字符不为“数据库”且包含了百分号（%）的求助
6. 找出2022年10月发布的文章
7. 找出所有周末发布的求助
8. 添加一列：最后修改时间（LatestEditTime），找出发布后3天内修改过的文章
在Keyword表中：
1. 找出所有被使用次数（Used）大于10小于50的关键字名称（SName）
2. 如果被使用次数（Used）小于等于0，或者是NULL值，或者大于100的，将其更新为1
3. 删除所有使用次数为奇数的Keyword
4. 将Name中的“17bang”前面的内容全部删除，并把小写的“17bang”替换成大写的“17BANG”

注意，上述作业需要自己插入数据进行测试

学习笔记

源栈学历

键盘敲烂，月薪过万；作业不做，等于没学！

作业

单表：增删改查 / 运算&函数 / 事务：

在User表中插入以下四行数据：

UserName	Password
17bang	1234
Admin	NULL
Admin-1
SuperAdmin	123456

将Problem表中的Reward全部更新为0
使用事务，

删除User表中的全部数据，
回滚事务，撤销之前的删除行为

在User表中：
1. 查找没有录入密码的用户
2. 删除用户名（UserSName）中包含“Admin”或者“17bang”字样的用户
在Problem表中：
1. 给所有悬赏（Reward）大于10的求助标题加前缀：【推荐】
2. 将为负数的Reward值全部变成相应的正数（绝对值）
3. 给所有悬赏大于20且发布时间（Created）在2019年10月10日之后的求助标题加前缀：【加急】
4. 删除所有标题以中括号【】开头（无论其中有什么内容）的求助
5. 查找Title中第5个起字符不为“数据库”且包含了百分号（%）的求助
6. 找出2022年10月发布的文章
7. 找出所有周末发布的求助
8. 添加一列：最后修改时间（LatestEditTime），找出发布后3天内修改过的文章
在Keyword表中：
1. 找出所有被使用次数（Used）大于10小于50的关键字名称（SName）
2. 如果被使用次数（Used）小于等于0，或者是NULL值，或者大于100的，将其更新为1
3. 删除所有使用次数为奇数的Keyword
4. 将Name中的“17bang”前面的内容全部删除，并把小写的“17bang”替换成大写的“17BANG”