键盘敲烂,月薪过万作业不做,等于没学
当前系列: 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);
语法注意事项:
  1. 关键字INSERT(插入),空格
  2. 后接SQL表名,圆括号中包含列名,用逗号(,)分隔,空格
  3. 再写关键字VALUES,圆括号中按顺序输入每一列的值(和之前Student表()中的列一一对应)
  4. 文本和日期类型数据,需要用单引号('')括起来;(尤其是,并)


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)

如果要查文本里有没有%呢?我们需要:

  1. 使用ESCAPE指定一个转义字符
  2. 在%前加上这个转义字符,告诉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有什么区别呢?

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

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

  1. What's the difference between TRUNCATE and DELETE in SQL
  2. Microsoft Docs


有被称之为“更新”,使用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的高版本突破了这一限制。

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


作业

  1. 在User表中插入以下四行数据:
    UserName
    Password
    17bang
    1234
    Admin
    NULL
    Admin-1

    SuperAdmin
    123456
  2. 将Problem表中的Reward全部更新为0
  3. 使用事务,
    1. 删除User表中的全部数据,
    2. 回滚事务,撤销之前的删除行为
  4. 在User表中:
    1. 查找没有录入密码的用户
    2. 删除用户名(UserSName)中包含“Admin”或者“17bang”字样的用户
  5. 在Problem表中:
    1. 给所有悬赏(Reward)大于10的求助标题加前缀:【推荐】
    2. 将为负数的Reward值全部变成相应的正数(绝对值)
    3. 给所有悬赏大于20且发布时间(Created)在2019年10月10日之后的求助标题加前缀:【加急】
    4. 删除所有标题以中括号【】开头(无论其中有什么内容)的求助
    5. 查找Title中第5个起字符不为“数据库”且包含了百分号(%)的求助
    6. 找出2022年10月发布的文章
    7. 找出所有周末发布的求助
    8. 添加一列:最后修改时间(LatestEditTime),找出发布后3天内修改过的文章
  6. 在Keyword表中:
    1. 找出所有被使用次数(Used)大于10小于50的关键字名称(SName)
    2. 如果被使用次数(Used)小于等于0,或者是NULL值,或者大于100的,将其更新为1
    3. 删除所有使用次数为奇数的Keyword
    4. 将Name中的“17bang”前面的内容全部删除,并把小写的“17bang”替换成大写的“17BANG”
注意,上述作业需要自己插入数据进行测试
学习笔记
源栈学历
大多数人,都低估了编程学习的难度,而高估了自己的学习能力和毅力。

作业

  1. 单表:增删改查 / 运算&函数 / 事务
    1. 在User表中插入以下四行数据:
      UserName
      Password
      17bang
      1234
      Admin
      NULL
      Admin-1

      SuperAdmin
      123456
    2. 将Problem表中的Reward全部更新为0
    3. 使用事务,
      1. 删除User表中的全部数据,
      2. 回滚事务,撤销之前的删除行为
    4. 在User表中:
      1. 查找没有录入密码的用户
      2. 删除用户名(UserSName)中包含“Admin”或者“17bang”字样的用户
    5. 在Problem表中:
      1. 给所有悬赏(Reward)大于10的求助标题加前缀:【推荐】
      2. 将为负数的Reward值全部变成相应的正数(绝对值)
      3. 给所有悬赏大于20且发布时间(Created)在2019年10月10日之后的求助标题加前缀:【加急】
      4. 删除所有标题以中括号【】开头(无论其中有什么内容)的求助
      5. 查找Title中第5个起字符不为“数据库”且包含了百分号(%)的求助
      6. 找出2022年10月发布的文章
      7. 找出所有周末发布的求助
      8. 添加一列:最后修改时间(LatestEditTime),找出发布后3天内修改过的文章
    6. 在Keyword表中:
      1. 找出所有被使用次数(Used)大于10小于50的关键字名称(SName)
      2. 如果被使用次数(Used)小于等于0,或者是NULL值,或者大于100的,将其更新为1
      3. 删除所有使用次数为奇数的Keyword
      4. 将Name中的“17bang”前面的内容全部删除,并把小写的“17bang”替换成大写的“17BANG”
    注意,上述作业需要自己插入数据进行测试
觉得很 ,不要忘记分享哟!

任何问题,都可以直接加 QQ群:273534701

在当前系列 SQL 中继续学习:

多快好省!前端后端,线上线下,名师精讲

  • 先学习,后付费;
  • 不满意,不要钱。
  • 编程培训班,我就选源栈

更多了解 加:

QQ群:273534701

答疑解惑,远程debug……

B站 源栈-小九 的直播间

写代码要保持微笑 (๑•̀ㅂ•́)و✧

公众号:源栈一起帮

二维码