MySQL数据库基础：增删查改

 💎所属专栏： MySQL

 

💎1. 常用数据类型

💎1.1 数值类型

数值类型可以指定无符号类型，默认为有符号类型，例如身高体重这种，只可能是正数的，可以指定为无符号

CREATE TABLE example (  
    height INT UNSIGNED,  
    weight BIGINT UNSIGNED  
);

 以下是这些类型在Java中对应的类型

数据类型	对应Java中的类型
TINYINT	Byte
SMALLINT	Short
INT	Integer
BIGINT	Long
FLOAT(M,D)	Float
DOUBLE(M,D)	Double
DECIMAL(M,D)	BigDecimal
NUMERIC(M,D)	BigDecimal

 float和double类型，在表示小数的时候不是很精准，所以一般用decimal表示

💎1.2 字符串类型

 指定大小的时候，指定的是字符串的长度，例如varchar(10)，表示可以存放10个以内的字符，根据编码格式来判断一个字符多少个字节

一般情况下，不直接在数据库中存放文件，而是把文件存放在磁盘中，再去根据文件路径在磁盘上加载或是返回具体的真实文件

💎1.3 日期类型

 💎2. 创建表

 需要操作数据库中的表时，需要先使用该数据库，例如选择text1数据库

use text1;

之后来看创建表的语法

-- 图书表 图书名称，图书作者、图书价格、图书分类
drop table if exists book;
create table if not exists book(
    name varchar(20),
    author varchar(20),
    price decimal(10,2),
    category varchar(20)
);

查看表的结构

语法是：

 查看刚刚创建的book表

desc book;

一般情况下，创建表的代码比较多，再去在命令行中执行就比较麻烦，所以可以使用一些可视化工具，还可以在命令行中执行sql文件，例如把刚才创建表的代码导出为sql文件，直接通过source命令来执行也可以

 通过show tables 命令可以查看当前选择的数据库中都有哪些表

show tables;

💎3. 删除表

和删除数据库类似，这里把之前的book删除一下 

drop table book;

💎4. 插入数据

 要注意的是：

插入数据时，指定的字段顺序要和需要的值的顺序一致

字符串和日期型数据应该用引号引起来

插入字段的数据大小应该在规定范围内

在表名后指定要插入的列字段

insert into student(id, name) values (1,'张三');

还可以不指定列进行插入

insert into student values(2,'李四');

同理，也可以只指定插入一列的值，id那一列没有指定的话会自动补充，默认为null

insert into student(name) values('王五');

 还可以插入多行

insert into student values (4,'赵六'),(5,'钱七');

通过  select * from student; 可以查看字段的信息

💎5. 查询

💎5.1 全列查询和指定列查询

全列查询：

select * from exam;

在实际开发中不要使用 * 来进行查询，因为数据库会很大，影响效率 

指定列查询：

select id,name,math from exam;

查询的结果是一个表达式，例如，在查询命令中添加一个数值，每一列都会有这个数值

select id ,name ,10 from exam;

 之后，可以通过as关键字来为查询结果中的列指定别名，as和引号可以省略，但如果别名中存在空格就不能省略引号了

select id as '编号' ,name as '姓名' from exam;

列与列之间还可以进行运算：

select chinese + math + english as '总分' from exam;

 还可以直接加上指定的数字

select math + 10 from exam;

💎5.2 去重查询

去重查询是通过关键字 distinct 来实现的，在之前的math中，是存在一个重复的98的，我们来试验一下去重查询操作：

select distinct math from exam;

有一点需要注意的是，在查询结果中，每一列都相同才认为是重复数据，刚刚只查询的是math这一列，这次加上id试试：

可以看到，这一次重复的98并没有被去掉，因为id不同

💎5.3 排序查询

 对于多字段，按照字段的前后顺序，如果第一个字段相同，按照第二个字段进行排序

select math from exam order by math desc ;

 

 如果数据为 null 就认为是最小的，升序排最前面，降序排最后面

还可以加上之前的表达式和别名进行排序

select id ,name, math + chinese + english as '总分' from exam order by math + chinese + english desc;

 前面已经定义了别名，所以后面可以直接用

select id ,name, math + chinese + english as '总分' from exam order by 总分 desc;

 关于null的特殊情况：

1.不论和任何值相加，结果都是null

2.始终被看作false

3.null 并不等同于 0

 所以说，如果想上面的按照总分排序，如果其中一门为Null,那么他的总分就是null

最后还可以通过多个字段进行排序，排序的优先级就按照书写的顺序进行排列

例如：先按数学降序排列，再按语文升序排列，再按英语顺序排列

select id, name, math, chinese,english
from exam
order by math desc, chinese asc, english asc;

💎5.4 条件查询

 💎5.4.1 比较运算符

 先来看比较运算符

 前面的大于等于这些符号和java中一样，就不多说了，而java中相等是用 "==" 表示的，mysql中是 "=" ，判断不等于的这两个写法都可以

这里有一些小细节需要注意：

-- 查询英语不及格的
select name ,english from exam where english < 60;

 如果表中有null，不参与筛选，所以最终的结果不会包括null

-- 查询英语比语文好的
select * from exam where english > chinese;

 在上面的比较方式中，同一行的数据是可以比较的，但是不能跨行比较

select name, english + math + chinese as total
from exam
where english + math + chinese < 250
order by total desc ;

如果where 后面使用了表达式的话要写完整的表达式，不能写别名 

原因：和SQL的执行顺序有关

1.如果要在数据库中查找某些数据，首先要确定表，先执行from

2.在查询过程中，要根据指定的查询条件把符合条件的数据过滤出来，这时执行的是where字句

3.执行select后面的指定的列，这些列最终要加到结果的展示集里

4.根据order by 子句的列名和排序规则进行最后的排序

根据以上顺序可以推断出，在执行where语句时，还没到select里的total，所以用不了

<=>	表示等于，是专门针对null判断的，例如null <=> null 的结果为1

 null的判断是不能用 "=" 判断的

接下来的 between...and...和 in (...) 都是字面意思，分别用来判断是否在一个区间和是否在括号中的列表中

select 1 in (1,2,3);
select 2 between 1 and 4;

而like表示模糊匹配的意思是，按照单个字符和任意个字符进行匹配，来看一个例子：

 当用 '%' 表示的是找出姓张的人，任意字符也就是张后面可以跟任意个字符，'_' 表示单个字符，也就是张后面只能跟一个字符

最后还有一个判断是否为null的命令

💎5.4.2 逻辑运算符

 接下来看逻辑运算符

运算符	说明	对应Java中的逻辑运算符
AND	多个条件必须都为 TRUE(1)，结果才是 TRUE(1)	&&
OR	任意一个条件为 TRUE(1), 结果为 TRUE(1)	||
NOT	条件为 TRUE(1)，结果为 FALSE(0)	!

-- 语文大于80或英语大于80
select name, english, chinese
from exam
where english > 80
   or chinese > 80;

  or 的话是任意一个条件为true 就符合，哪怕另一个条件为null不参与比较

-- 语文英语都大于80分
select name, english, chinese
from exam
where english > 80
  and chinese > 80;

下面来分析一下and 和 or 的优先级：

-- 比较and 和 or 的运算优先级
select name, chinese, math, english
from exam
where chinese > 80
   or math > 70 and english > 70;

select name, chinese, math, english
from exam
where (chinese > 80 or math > 70)
  and english > 70

通过对比发现，and 和 or 和 java 中的优先级是一样的，都是and > or ，不过还是建议根据需求加括号 

 💎5.5 分页查询

在实现已经提到过，如果直接通过select * from不加限制来查询全部的数据是不安全的，通过分页查询可以有效的控制一次查询出来的结果集中的记录条数，可以有效的减少数据库服务器的压力，也有利于用户查看，例如我们经常见到的这种就是用到了分页查询

 例如从第0条开始，往后读取2条数据有一下这几种写法：

-- 从第0条开始往后读取2条数据
select *
from exam
order by id
limit 2;

select *
from exam
order by id
limit 0,2;

select * from exam
order by id
limit 2 offset 0;

 可以通过下面这个公式来计算第 s 页所需要的偏移量 n:

n = (s - 1) * 每页显示的记录数

 如果说指定的起始位置超出了整个表的范围就会返回一个空的结果集

💎6. 修改

-- 修改张小明的数学为85分
update exam
set math = 85
where name = '张小明';

 如果再插入一个‘张小明’，并且数学为85，再次执行上面的更新操作，虽然匹配到了两个，但是只会执行一个的修改操作

 如果说把数学修改为86，那么这两个都会被修改，只要找到了符合条件的数据行，就会一次性把这些数据都修改掉，如果不加where条件，所有的内容都会被修改

修改时，set中是可以包含表达式的，下面做一个小练习：

-- 把总成绩倒数前三的三位同学的数学成绩减5
update exam
set math = math - 5
where math is not null
order by (chinese + math + english) asc
limit 3;

 其中 math = math - 5 不能写成 math -= 5;

还有就是，如果修改的时候需要用到乘法的运算，如果匹配到的数据中包含0，那么就不会被修改掉

💎7. 删除

 

-- 删除 第10条数据 
delete from exam where id = 10;

和修改一样，只要匹配到符合条件的数据就会删除，所以如果不加条件，全部数据都会被删除， 一般情况下会在表中加上一个deleteState字段，用来标记是否被删除

-- 删除总分后三名的同学
delete from exam order by math asc limit 3;