【MySQL】第二弹——MySQL表的增删改查(CRUD)初阶

🎓一. CRUD

• CURD: Create，Retrieve，Update，Delete
• Create : 新增数据
• Retrieve : 查询数据
• Update : 修改数据
• Delete : 删除数据

MySQL的工作就是组织管理数据，先保存，保存好了后好进行增删改查,增删改查的前提是已经把数据库创建好,并且选中数据库,表也创建就绪

• 如何注释?
  在SQL中可以使用“–空格+描述”来表示注释说明

🎓二. 新增(Create)

insert into 表名 values(值, 值, 值…);

注意此处的值的个数要和表的列数匹配，值的类型也要和列的类型匹配（不匹配就会报错!）

这里也体现了关系型数据库的一个优势:对数据进行更严格的校验检查，更容易发现问题

①我们在库里创建一个学生表

②查看表的结构

③新增数据

注意:在SQL中可以单引号也可以用双引号,表示字符串,两种都可以,这两个是等价关系,在SQL中没有"字符类型",只有字符串类型,所以单引号也可以表示字符串

注意:这里表的列数不匹配或是类型不匹配,都会报错

出现ERROR意味着当前的操作是不生效的

• 扩展
  这里可以插入中文数据
  
  数据库表示中文需要明确字符编码，MySQL默认的字符集叫做拉丁文，不支持中文，为了可以存储，就需要把字符集改为UTF-8。我们这里直接修改MySQL的配置文件,直接把字符集改为UTF-8

至此MySQL配置修改就彻底结束了，我们继续insert的探讨

insert插入的时候可以指定列进行插入，不一定非得把这一行的所有列都插入数据，可以想插入几列就插入几列

名字列插入王五,其他列未被插入,比如id列,就会填入默认值NULL

insert语句也可以依次插入多条记录,在values后面,写多组(),每个()之间使用逗号分割

mysql>  insert into student values(1,"zhangsan"),(2,"lisi"),(3,"wangwu");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MySQL中,一次插入多条记录比不同的一条记录多次插入要快得多!

• 主要是因为MySQL是一个客户端/服务器结构的程序,每次在客户端里输入的命令,都是通过网络来进行输入的

• 数据库服务器需要解析请求，获取到其中的sql，解析sql执行相关操作，并且把操作结果返回给客户端
• 如果要是一次插入一条，分成多次插入就会有多个请求/响应，如果要是一次插入多条，就一次请求/响应就够了
• 所以还是一次请求/响应 快多了

插入是SQL中最简单的操作,也是最常用的操作

🎓三. 查询(Rertieve)

查询是SQL中最重要也是最复杂的操作,这里我们先介绍的是最简单的查询

📖1. 全列查询

• 直接把整个表的数据查询出来

select * from 表名;
其中 * 是通配符 表示匹配任意的列(也就是所有的列)

注意:要理解这里的执行过程,客户端和服务器之间通过网络进行通信

这一组结果是通过网络返回的，最终呈现在客户端上，这些数据是服务器筛选得到的数据结果，客户端也是以表格的形式进行呈现，但是大家不要把客户端显示的这个表格视为是服务器上数据的本体，这个客户端上显示的表格是个“临时表”

问题:如果当前数据库的数据特别多,执行select * 会发生什么?

• 服务器要先读取磁盘，把这些数据都查询出来，再通过网卡把数据传输给客户端，由于数据量非常大，极有可能就把磁盘IO（input output）吃满或者把网络带宽吃满。最直观的感受就是会感受到卡顿，至于卡多久，不明确! 不可控!
• 所以在我们的生产环境中这也是一个比较危险的操作,数据很多可能会达到TB级别,磁盘资源和网络资源在服务器上都是非常紧缺的资源,一定要珍惜我们有限的资源,全列查询可能会将服务器资源吃光,其他的操作就要等待当前操作执行完成,才能继续执行

在执行一些SQL的时候如果执行的时间比较长，随时可以按 Ctrl + c 来中断，以免造成不必要的损失

📖2. 指定列查询

select 列名,列名,列名… from 表名; //需要查询几个列,就写几个列

当我们省略掉一些不必要的列的时候，就可以节省大量的磁盘IO和网络带宽

同样的,MySQL是客户端服务器结构的程序，在此处看到的这个表结果，也同样是 " 临时表 " 只是在客户端这里显示成这个样子，而不是说服务器上就真有一个这样的表，里面只存了id列

select所有的操作结果都是临时表，都不会影响到数据库服务器原有的数据！

📖3. 查询带有表达式

创建一个表exam 设置属性 插入数据

可以将所有的成绩加在一起,起别名为总分

别名最规范的写法是 : as ’ 总分 ’
给结果集中的字段起别名:select 列名[as] 别名[, 列名]… from 表名;

📖4. 起别名查询(as ’ ')

select name, chinese + math + english as ‘total’ from exam;

给结果集中的字段起别名:select 列名[as] 别名[, 列名]… from 表名;

📖 5. 去重查询(distinct)

• 把查询结果相同的记录,合并为一条记录

select distinct 列名 from 表名;

• 去重时,只有查询结果中所有的列都相同才会被认定为重复记录
• 去重后,重复记录只保留一条

📖6. 排序查询(order by)

• 查询过程中,对于查询到的数据进行排序(只是针对临时表,对于数据库原本的数据没有影响)

select 列名 from 表名 order by 列名[ ASC | DESC];

排序规则:1.升序 ASC 2.降序 DESC 不写默认为升序

不推荐这种写法,我们强烈建议明确指定排序规则

没有order by 子句时,返回结果集按哪个字段进行排序?
没有指定order by 子句时,永远不要依赖默认排序返回结果集顺序
不同版本的 MYSQL 是根据哪个字段进行排序的 是不明确的
依赖某个排序规则时,就明确子句中排序字段

在SQL中,如果NULL和其他类型进行混合运算结果依然那是NULL

NULL 数据排序,视为比任何值都小,升序排在最上面,降序排在最下面

MySQL中NULL比较特殊
1.无论和什么值进行运算,返回值都是NULL
2.NULL始终被判定为FALSE
3.NULL的值不是我们以前学习的其他编程语言的0在MySQL他就是NULL

• 可以对多个字段进行排序,排序的优先级与书写顺序相关
• 可以为每个字段指定不同的排序规则

📖7. 条件查询(where)

• 指定条件,对于查询结果进行筛选
• 通过一写运算符,过滤掉不符合条件的记录,符合条件的发那会给用户

select * from 表名 where 条件;

1.比较运算符

下面进行几个示例:

2.逻辑运算符

逻辑运算符有优先级,不建议记忆,在使用时通过小括号的方式,手动指定优先级,这样可读性也会高一点

使用where条件的语法:select * from 表名 where 列名/表达式 运算符 条件;
条件查询时,首先要确定好给哪个列设置相应的条件

• 做几个练习:
• 1.查询英语不及格的同学及英语成绩
  
• 2.查询语文成绩好于英语成绩的同学
  
• 3.查询总分再200以下的同学

<出现这种现象和MYSQL内部实现有关,换句话说和MYSQL执行SQL语句的顺序相关>

1.如果要在数据中查询某些数据,首先要确定表,即先执行from
2.查询过程中,要根据指定的查询过程过滤符合条件的数据,这时执行where子句,也就是说执行where时total还没有被定义,所以不能使用别名
3.执行select后面指定的列,这些列加入到最终结果集中
4,执行order by 子句中指定的列名和排序规则进行最后的排序

• 4.查询语文成绩>80,且英语>80的同学
  
• 5.查询语文>80 或 英语>80 的同学
  

or 只要一个条件满足就符合整个查询条件
观察and 和 or 的优先级

• 6.查询语文成绩在[80,90]的同学及语文成绩
  

• 7.查询数学成绩是 58 或者 59 或者 98 或者 99 的同学及数学成绩
  

• 8.%匹配任意多个(包括0个)字符

%匹配的字符没有限制

• 9._匹配严格的一个任意字符
  
• 10. NULL的查询 : IS [NOT] NULL
      

📖8. 分页查询

• 使用 limit 关键字，来进行限制返回的结果条数，使用 offset 来确定从第几条开始进行返回

select 列名 from 表名 limit N offset M;
select 列名 from 表名 limit M,N;

之前我们学习select * from 表名;的时候,有提过,不加限制记录条数的查询是不安全的

分页查询在项目中运用非常多,只要查询的是一个记录的集合(多条记录)都在使用分页查询

通过分页查询可以有效的控制一次查询出来的结果集中的记录的条数,有效的减少数据库服务器的压力,同时对于用户页比较友好

• 从第M条开始查询,返回N条记录

• 1.从 0 开始,筛选 n 条结果

• 2.从S开始,筛选N条结果
  
  

• 3.从S开始,筛选N条结果,比第二种方法更明确,建议使用
  
  
  

S=(当前页号-1)*每页显示的记录数
第三页 S=(3-1)*2=4

• 第三页查询
  

🎓四. 修改(Update)

• 此处的修改,针对数据库服务进行的,修改时持续有效的

update 表名 set 列名 = 值… where 子句;
针对哪个表 哪些行 哪些列 修改成什么值

• 1.将孙悟空的数学成绩变更为80分
  

• 2.在插入一条同名的孙悟空同学
  
  3.update exam set math = 100;
  update 操作的时候,如果不加where 条件,修改的是整张表的所有记录,这个操作非常危险,谨慎操作!

• 4.将曹孟德的数学变更为60,语文 变更为70

update 表名 set 列名 = 值, 列名 = 值 where 条件;

• 5.将总成绩倒数三位的同学,数学成绩减30分

如果是加上30分会超出我们原定的范围
我们创建表时用到的decimal(3,1)表示三个数字的长度,保留一位小数,如88.3 77.9
set 中可以包含表达式

update 表名 set 列名 = 列名 + 30 where 条件 order by 列名 排序规则 limit N;

• 6.把所有语文成绩低于50的同学的语文成绩更新为原来的2倍
  

🎓五. 删除(Delete)

delete from 表名 [where…] [order by…] [limit…]

• 1.删除孙悟空同学的考试成绩
  
• 2.删除英语成绩倒数前三的同学的所有成绩
  
• 3.delete 的时候,如果不加where条件会出现什么问题–全删

如果删除时不加条件限制,那么整张表的数据都会被删除掉(清空)
这个操作是非常危险的
虽然可以恢复,每一条执行的SQL都会被记录到日志中,把日志中记录的操作,在执行一遍基本上就可以完成恢复

在生产环境中一般不使用delete操作
一般会在表中加一个deleteState字段,用来表示这条记录是否删除,0表示正常(没有删除) 1表示已删除,用update操作去更新deleteState字段,就可以实现删除功能,这条被删除的数据并没有实质上删除而是始终存在于数据库中

总结