【MySQL】数据库的增、删、查、改（基础）（重要）

MySQL表的增删改查（CURD）（基础）

1.CURD

• 注释：在SQL中可以使用“ --空格+描述”来表示注释说明

  -- 这是注释

• CURD即增加（Create）、更新（Update）、查询（Retrieve）、删除（Delete）四个单词的首字母缩写。

2.新增（Create）

2.1 插入数据

2.1.1单行数据+全列插入

insert into 表名 values(); -- 括号中是对应的字段内容，插入的字段数目和类型需要和表结构中的要求一致。

create table student(
    id int,
    name varchar(16),  
    score decimal(3,1),  
    email varchar(50) 
);

insert into student value(1,'李明',89.5,'liming@qq.com');
insert into student value(2,'Lucy',98.8,'Lucy@qq.com');

2.1.2 指定列插入数据

根据需要只插入其中的某几列,其中没有被插入的列默认被填充成NULL。

insert into 表名 (若干个指定列) values (对应的列数据);

insert into student (id,name) values (101,'王二');

注意：数目和类型都要对应

​ 指定的列都得在表头中存在

2.1.3 一次插入多条记录

一次插入多条记录就是把指，一个insert同时插入多组数据，想要插入多组，就把values后面多加上几个逗号，来把多组数据分割开就行了。

insert into 表名 (若干个指定列) values (对应的列数据),(对应的列数据),(对应的列数据)...

insert into student (id,name) values (3,'小赵'),(4,'小钱'), (5,'小孙'),(6,'小李');

3 .查询（Retrieve）

3.1全列查询

先创建一个表，为查询做准备。

create table exam_result(
    id int,
    name varchar(20),
    chinese decimal(3,1),
    math decimal(3,1),
    english decimal(3,1)
);

insert into exam_result values
(1,'贾宝玉',89.5,78,45),
(2,'林黛玉',97.6,69,72),
(3,'薛宝钗',98,78,69),
(4,'王熙凤',94.5,89,86),
(5,'史湘云',99,78.8,69),
(6,'元春',95,77,94),
(7,'迎春',89,78,69.5),
(8,'惜春',79,74,78.5),
(9,'探春',89,70,85);

select * from 表名;   -- *是一个通配符，意思是把所有的列都查找出来

这样的查找方式方式仅限于在测试环境中使用。千万不能在生产环境的服务器上执行这样的SQL.

3.2查找指定列

select 列名 from 表名；

select id,name,chinese from exam_result;

3.3查询的列为表达式

针对查到的列进行一定的表达式计算,MySQL自身有求和的功能。

1）查找所有同学的姓名和总成绩 。
select name,chinese+math+english from exam_result;

2)查找所有同学的语文成绩，并在原来的基础上加10分。
select name, chinese+10 from exam_result;

3.4将查询字段指定别名

列求和：同一行中的若干列加到一起，如：针对贾宝玉的语文、数学、英语加到一起。

行求和：将同一列的若干行加到一起，如：针对贾宝玉、林黛玉、薛宝钗的语文加到一起。

select name,chinese+math+english as '总成绩' from exam_result;    -- as '总成绩'，表示把列指定为别名'总成绩'，其中as是可以省略的。

3.5将查到的结果去重（distinct）

指定某一列进行去重，但是去重查找得到的结果表的行数和原来可能是不一样的。

去重操作也可以指定多列来去重，不过此时要求指定的列的数据都完全相同的才会被合并到一起。

select distinct 列名  from 表名;
-- 使用 distinct 的时候必须把对应的列都放到 distinct 之后
select distinct chinese from exam_result;

【注意】：所有的select 操作都只是基于原来的表，生成结果表，它的结果都是放在一个新的表里，并不会对原来的表造成任何改变。要想修改需要使用insert、update、delete等才会对原表造成影响。

3.6排序的几个基本用法：order by

order by – 指定针对哪个列进行排序
– 默认是升序排序
– 可使用asc表示升序排序，desc表示降序排序

select 列 from 表名 order by 列/表达式 asc/desc;

a)查找学生信息，并且按照数学成绩进行升序排序
select * from exam_result order by math asc;

b)查找学生信息，并且按照数学成绩进行降序排序
select * from exam_result order by math desc;

c)查找学生信息，并按照总成绩降序排序
select name, chinese+math+english from exam_result order by chinese+math+english desc;

d)查找学生信息，并按照总成绩进行降序排序，总成绩使用别名
select name, chinese+math+english as '总分' from exam_result order by '总分';

e)指定多个列进行排序，多列排序需要在前一个列相等的基础上后面再按照第二个列排序
题目描述：先把所有同学的信息按照语文降序排序，再按照数学降序排序，再按照英语降序排序。
-- 列越靠前排序规则的优先级越高，即如果语文的分数一样，再按照数学排序，如果语文和数学都一样，再按照英语排序。
select * from exam_result order by chinese desc,math desc,english desc;

【注意】：如果指定的列中是null的话，它会被认为是最小的值。

3.7条件查询：where(关键字)（重要）

select 列 from 表名 where 条件表达式;
-- 条件为真的记录才会被最终获取到

比较运算符

运算符	说明
> , >= ,< ,<=	大于、大于等于、小于、小于等于
=	等于，null不安全，例如null = null的结果是null
<=>	等于，null安全，例如null <=> null的结果是True(1)
!= , <>	不等于
between x and y	范围匹配，[x,y],如果x<= value <=y,返回True(1)
in (option )	如果是option中的任意一个，返回True（1）
is null	是null
is not null	不是null
like	模糊匹配。%表示任意多个（包括0个）任意字符；_表示任意一个字符

【个别运算符拓展解释】

=: 这里都是比较相等，而不是赋值，update中的 = 相当于赋值

表达式null = null的结果是null，相当于条件不 成立；

表达式null <=> null 的结果是true,相当于条件 成立；

between x and y: 表示当值在[x,y]闭区间之间都是满足条件的

in (若干个选项)：当值在（）中的若干选项里匹配任意一个都是满足条件

like 操作需要搭配通配符来使用，%：匹配任意个任意字符，_ :匹配一个任意字符。

逻辑运算符

运算符	说明
and	逻辑与，多个条件必须都为True(1),结果才是True(1)
or	逻辑或，任意一个条件为True(1),结果为True(1)
not	逻辑取反，条件为True(1),结果为false(0)

【注意】

1.where条件可以使用表达式，但不能使用别名；

2.and的优先级高于or,在同时使用时，需要使用小括号（）包裹优先执行的部分。

a)查找数据中语文成绩为null的记录
select * from exam_result where chinese <=> null;
或
select * from exam_result where chinese is null;

b)查找英语成绩不及格（<60）的学生信息
select * from exam_result where english < 60;

c)查找数学成绩比英语成绩好的学生信息
select * from exam_result where math > english;

d)查找总分在200分以下的学生
select name,chinese+math+english as '总分' from exam_result where chinese+math+english < 200;

e)查询语文成绩大于80，并且英语成绩也大于80的学生。
select * from exam_result where chinese > 80 and english > 80;  -- 语文和英语同时超过80才可以

f)查询语文成绩大于80 或者 英语成绩也大于80的学生。
select * from exam_result where chinese > 80 or english > 80;  -- 语文和英语只要有一个超过80就可以

【注意】：and 和 or 如果同时出现，and的优先级要更高一些，不过还是推荐加上适当的括号。

g)查询语文成绩在[85,95]之间的学生
select * from exam_result where chinese between 85 and 95 ;
或
select * from exam_result where chinese >=85 and chinese <=95 ;

h)查询英语成绩是45.0 或者 70.0的学生
select * from exam_result where english in (45.0,85.0,95.0);
或
select * from exam_result where english = 45.0 or english=85.0 or english= 90.0;

i)查找所有姓王的学生成绩；
select * from exam_result where name like '王%';

-- like 操作需要搭配通配符来使用，%：匹配任意个任意字符，_ :匹配一个任意字符。
-- %和数量无关，下划线和数量相关
-- 模糊匹配查询效率相对较低，一般不建议使用like
select * from exam_result where name like '_春';

j)查询所有同学中语文成绩是9开头的
select * from exam_result where chinese like '9%';
-- like不仅仅能对字符串进行匹配，也能针对数字进行匹配

【一个常见的优化方式】：在进行复杂条件查询的时候，存在一个’最左原则‘

select * from exam_result where chinese>60 and name like '贾%';
或
select * from exam_result where name like '贾%' and chinese>60;
-- 通过查看结果，我们知道，chinese>60 和 name like '贾%'的先后顺序并不影响结果，但是还是建议把name like '贾%'这个条件放在左边

上面讲过的这些select 操作，除了条件查找之外，剩下的都是不应该在生产服务器上直接执行的。

最保险的就是加上分页查找，分页查找相当于把查找结果只选取其中的一小部分来作为结果。

3.8分页查找（limit）

select 列 from 表名 limit 结果的数目;
select 列 from 表名 limit 结果的数目 offset 起始记录的下标;

a)查找学生信息中总分最高的前三名
select name, chinese+math+english as '总分' from exam_result  order by '总分' desc  limit 3;
-- 直接limit获取前n个元素，前几就写limit几

b)查找学生信息中总分最高的4-6
select name,chinese+math+english as '总分' from exam_result  order by '总分' desc  limit 3 offset 4;
-- limit 搭配 offset 从某个位置开始在往后去找n个元素
-- offset 4可以当成下标，下标从0开始计数

c)如果limit后面的数字太大，超出记录的数目，返回结果不会有任何错误,不过可能会丢失数据
select name,chinese+math+english as '总分' from exam_result  order by '总分' desc  limit 15 offset 4;

d)如果offser后面的数字太大，得到的结果可能是一个空的结果
select name,chinese+math+english as '总分' from exam_result  order by '总分' desc  limit 3 offset 15;

很多网站如果信息太多，就会分页，而它里面的分页功能就是基于limit来实现的。

如类似下图这样的就是基于limit来实现的分页功能

以上关于查找的学习只是一些基础的，并不是全部，后面会有一些复杂的相关查找的学习。

4.修改（update）

update 表名 set 列名 = 新的值，(列名 = 新的值) where 筛选条件;

a)把贾宝玉的数学成绩改成88
update exam_result set math = 88  where name = '贾宝玉';
-- update 每次修改几行记录是不确定的，具体取决于where中的条件怎么写，得看where条件过滤之后剩下多少记录。

b)把林黛玉的数学成绩改为79，英语成绩改为78
update exam_result set math = 79,english = 78 where name='林黛玉';

c)把所有同学的语文成绩减10分
update  exam_result set chinese = chinese-10;

?? d)将总成绩倒数前3名的同学数学成绩+10分
update  exam_result set math= math+10 order by chinese+math+english asc limit 3;

e)将所有同学的id更新为原来的 2 倍
update exam_result set id = id * 2;

5.删除（Delete）

delete from 表名 where 筛选条件;

a)删除林黛玉同学的考试成绩
delete from exam_result where name = '林黛玉';

b)删除整张表
delete from 表名;
delete from exam_result;

【注意】：delect（删除）操作非常危险，没事不要乱删，一旦删除，使用常规手段是无法恢复的，一旦不小心把公司里面很重要的数据删掉了，那就准备跑路吧~~

不过数据库都会支持一些丰富的**”权限“**控制，例如，有些数据，只能读，不能改；有些数据，能读能改，不能删除。

【疑问】：数据多了会爆表吗？

会，因为数据库的数据都是存到磁盘上的，如果数据特别多，超出磁盘容纳的上限，就无法继续写入数据了。

解决方法：可以根据数据的重要情况，决定是定期清理还是定期备份。

还可以使用分布式的方式，针对数据库进行分库，分表使用多台机器共同保存所有的数据集合。

6.总结

6.1新增

-- 单行插入
insert into 表名 (字段1,...,字段N) values (value1,...,value N);
insert into 表名 values (value1,...);
insert into 表名 values (value2,...);
-- 多行插入
insert into 表名 (字段1,...,字段N) values (value1,...), (value2,...), (value3,...);
insert into 表名 values (value1,...), (value2,...), (value3,...);

6.2查询

-- 全列查询
select * from 表名;
-- 指定列查询
select 字段1,字段2... from 表名;
-- 查询表达式字段
select 字段1+100,字段2+字段3  from 表名;
-- 别名
select 字段1 as 别名1,字段2  as 别名2 from 表名;
-- 去重distinct
select distinct 字段 from 表名;
-- 排序order by
select * from 表名 order by 排序字段;
-- 条件查询where
select * from 表名 where 条件;
-- 1.比较运算符    2.between ... and ...    3.in    4.is null   5.like   6.and   7.or   8.not

6.3修改

update 表名 set 字段1 = value1,字段2 = value2... where 条件;

6.4删除

delete from 表名 where 条件;