【MySQL】数据库之表的增删查改

目录

 

表的增删查改

1.1Create

1.1.1单行数据＋全部插入(全列插入)

1.1.2多行数据+指定列插入(批量插入)

  1.1.3插入否则更新

1.1.4冲突更新

1.1.5冲突替换

1.2Retrieve

1.2.1select列

1.2.2 指定列查询

1.2.3查询字段为表达式

1.2.4结果去重

1.3Where条件

1.3.1比较运算符：

1.3.2逻辑运算符：

1.3.3Null查询

1.4结果排序：

1.5筛选分页结果

1.6Update

1.7Delete

1.8截断表

1.9插入查询结果

---

• 面试题：

SQL查询中各个关键字的执行先后顺序

from > on> join > where > group by > with > having > select > distinct > order by > limit

表的增删查改

CRUD：create,retrieve,update,delete

1.1Create

•    语法：

insert [into] table_name
   [(column [,column] ...)]
   values(value_list) [,(value_list)]...
value_list:value,[,value] ...

 

   示例：

•    创建一张学生表

create table if not exists student(
    id int primary key auto_increment comment '编号',
    sn int not null unique comment '学号',
    name varchar(32) not null comment '姓名',
    qq varchar(12) unique key comment 'qq号'
);

1.1.1单行数据＋全部插入(全列插入)

-- 插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致

  示例：

insert into student values(1,100,'唐三藏',null); 
insert into student values(2,101,'孙悟空',500400); 

•    查看输入结果 

select * from student;

1.1.2多行数据+指定列插入(批量插入)

--插入两条记录，value_list 数量必须和指定列数量及顺序一致

 

insert into student (id,sn,name)values(3,102,'曹孟德'),(4,103,'孙仲谋');

•  查看输入结果

select * from student;

• 补充：

   --单行数据+指定列

insert into student(id,sn,name) values(5,104,'刘备'); 

   --多行数据+全列插入

insert into student values(6,105,'诸葛亮','6666'),(7,106,'关羽','34325');

  1.1.3插入否则更新

由于主键或者唯一键对应的值已经存在而导致插入失败

--主键或者唯一键冲突

insert into student (id,sn,name) values (7,'107','张飞'); 

insert into student (id,sn,name) values (8,'106','张飞');

解决：可选择性进行同步更新操作

•  语法：

insert ... on duplicate key update column = value[, column = value] ...

1.1.4冲突更新

insert into student (id,sn,name) values (7,'107','张飞')
 on duplicate key update sn = '107',name = '张飞';

-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等

-- 1 row affected: 表中没有冲突数据，数据被插入

-- 2 row affected: 表中有冲突数据，并且数据已经被更新

•   通过MySQL函数获取受到影响的数据行数

select row_count();

1.1.5冲突替换

--主键 或者 唯一键 没有冲突，则直接插入

--主键 或者 为一间 如果冲突，则删除后在插入

先删除原有的数据，再插入新数据
 

replace into student (id,sn,name) values(7,'107','关羽'); 

replace into student (id,sn,name) values(8,'108','鲁肃'); 

-- 1 row affected: 表中没有冲突数据，数据被插入

-- 2 row affected: 表中有冲突数据，删除后重新插入

1.2Retrieve

• 语法：

select [distinct]{column [,column] ...} [from table_name] [where ...] 
[order by column] [ASC|DESC], ...] limit ...

 示例:

•    创建表结构

create table exam_result( 
    id int unsigned primary key auto_increment, 
    name varchar(20) not null comment'同学成绩', 
    chinese float default 0.0 comment'语文成绩', 
    math float default 0.0 comment'数学成绩', 
    english float default 0.0 comment'英语成绩' 
);

•   插入测试数据

insert into exam_result(name,chinese,math,english) values 
   ('唐三藏', 67, 98, 56), 
   ('孙悟空', 87, 78, 77), 
   ('猪悟能', 88, 98, 90), 
   ('曹孟德', 82, 84, 67), 
   ('刘玄德', 55, 85, 45),  
   ('孙权', 70, 73, 78), 
   ('宋公明', 75, 65, 30 );

1.2.1select列

•   全列查询

--通常情况下不建议使用* 进行全列查询

--1.查询的列越多，需要传输的数据量越大

--2.可能会影响到索引的使用。

select id,name,chinese,math,english from exam_result;

1.2.2 指定列查询

--制定列的顺序不需要按定义表的顺序来

示例：

select id, name, english from exam_result;

1.2.3查询字段为表达式

--表达式包含多个字段 

select id,name, chinese + math + english from exam_result;

1.2.4结果去重

•    98 分重复 

select math from exam_result;

• 去重结果

select math from exam_result;

1.3Where条件

1.3.1比较运算符：

 

1.3.2逻辑运算符：

 

示例：

• 英语不及格的同学及英语成绩（<60）

  --基本比较

select name,english from exam_result where english < 60;

• 语文成绩在[80,90]分的同学及语文成绩

   --使用and进行条件连接

select name,chinese from exam_result where chinese >= 80 and chinese <=90;

 --使用between ... and ... 条件

select name,chinese from exam_result where chinese between 80 and 90;

• 数学成绩是58或者59或者98或者99分的同学及数学成绩

--使用or进行条件连接

select name,math from exam_result where math = 58 or math = 59 or math = 98 or math = 99;

• 姓孙的同学及孙某同学

 --%匹配任意多个（包括 0 个）任意字符

select name from exam_result where name like '孙%';

-- _匹配严格的一个任意字符

select name from exam_result where name like '孙_';

• 语文成绩好于英语成绩的同学

   --where 条件中比较运算符两侧都是字段

select name,chinese,english from exam_result where chinese > english;

• 总分在200分以下的同学

    --where 条件中使用表达式

    --别名不能用在Where条件中

select name,chinese + math + english from exam_result 
    where chinese + math + english < 200;

• 语文成绩> 80并且不姓孙的同学

     --and 与 not的使用

select name,chinese from exam_result where chinese > 80 and name not like '孙%';

1.3.3Null查询

•   Null he Null 的比较， = 和 <=>的区别

select null = null, null = 1,null = 0; 

select null <=> null,null <=> 1,null <=> 0;

1.4结果排序：

语法：

--ASC为升序（从小到大）

--DESC为降序（从大到小）

--默认为ASC（升序）

select ... from table_name [where ...] order by column [ASC|DESC],[...];

注意：

没有order by 子句的查询，返回的顺序是未定义的，永远不要依赖此顺序

示例：

• 查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

   --多字段排序，排序优先级随书写顺序

select name,math,english,chinese from exam_result order by math desc,english,chinese;

1.5筛选分页结果

• 语法：

   -- 起始下标为 0

   -- 从 0 开始，筛选 n 条结果

select ... from table_name[where ...] [order by ...] limit n;

   -- 从 s 开始，筛选 n 条结果 

select ... from table_name [where ...] [order by ...] limit n offset n;

注意：

对未知表进行查询时，加一条limit 1,避免因为表中数据过大，查询全表数据导致数据库卡死

•  按id进行分页，每页3条记录分别显示第1、2、3页

   --第1页

select id,name,math,english,chinese from exam_result order by id limit 3 offset 0;

   --第2页

select id,name,math,english,chinese from exam_result order by id limit 3 offset 3;

   --第3页

select id,name,math,english,chinese from exam_result order by id limit 3 offset 6;

1.6Update

• 语法：

update table_name set cliumn = expr[,clumn = expr ...] 
    [where ...] [order by ...] [limit...]

• 对查询到的结果进行列值更新

示例：

• 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

    --一次更新多个列

    --查看原数据

select name, math, chinese from exam_result where name = '曹孟德'; 

   -- 数据更新

update exam_result set math = 60, yuwen = 70 where name = '曹孟德'; 

   --查看更新后数据

select name,math,chinese from exam_result where name = '曹孟德';

• 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

   -- 更新值为原值基础上变更

   -- 查看原数据

select name, math, chinese + math + chinese 总分 from exam_result order by 总分 limit 3;

   --数据更新

 update exam_result set math = math + 30
    order by chinese + math + english limit 3;

• 将所有同学的语文成绩更新为原来的 2 倍

注：更新全表的语句慎用

   --没有where 子句，则更新全表

  --查看原数据

select * from exam_result; 

  --数据更新

update exam_result set chinese = chinese *2; 

--查看更新后的数据

select * from exam_result;

1.7Delete

• 删除数据

语法:

delete from table_name [where ...] [order by ...] [limit ...]

示例：

• 删除孙悟空同学的考试成绩

-- 查看原数据

delete from table_name [where ...] [order by ...] [limit ...]

  -- 删除数据

delete from exam_result where name = '孙悟空';

  -- 查看删除结果

select * from exam_result where name = '孙悟空';

1.8截断表

truncate [table] table_name

Truncate与Delete的区别：

1.9插入查询结果

• 语法：

insert into table_name [(column [,column ... ])] select ...

示例：分析

• 删除表中的重复记录，重复的数据只能有一份

--创建原始数据

create table duplicate_table (id int, name varchar(20)); 

--插入数据

insert into duplicate_table values 

(100, 'aaa'),

(100, 'aaa'), 

(200, 'bbb'), 

(200, 'bbb'), 

(200, 'bbb'), 

(300, 'ccc');

  --结果

(100, 'aaa'), 

(100, 'aaa'), 

(200, 'bbb'), 

(300, 'ccc');

• 思路：

实现步骤：

查询表结构：

desc duplicate_table; 

1.创建一个和原表一摸一样的表

create table duplicate_table2 like duplicate_table; 

2.查询原表的数据并且去重

select distinct id,name from duplicate_table; 

3.查询原表去重的数据插入至新表
 

duplicate_table2 insert into duplicate_table2(id,name) 

   select distinct id,name from duplicate_table;

4.删除原表

drop table duplicate_table; 

5.将新表duplicate_table2 名字改为duplicate_table

alter table duplicate_table2 rename to duplicate_table;

查看最终结果

select * from duplicate_table;