MySQL增删改查操作大全

目录

（一）插入语句

（二）修改语句

（三）删除语句

（四）查询语句

【1】基本查询语句

【2】聚集函数

【3】分组查询

【4】MySQL关键词执行顺序

（五）多表查询

【1】关于外键

【2】笛卡尔积查询

【3】内连接查询

【4】左外连接查询

【5】右外连接查询

【6】全外连接查询

（六）数据库的备份与恢复

---

（一）插入语句

INSERT INTO table_name [(column [, column...])] VALUES (value [, value...]);

插入单条语句：insert into employee values(null, '张三', '男', '1999-01-01');

插入多条语句：insert into employee values(null, '张三', '男', '1999-01-01'),(null, '李四', '男', '1995-01-01');

• 字符和日期型数据应包含在单引号中。

插入中文出现问题请参考：https://blog.csdn.net/c17315377559/article/details/100513115

（二）修改语句

UPDATE tbl_name  SET col_name1=expr1 [, col_name2=expr2 ...]  [WHERE where_definition]

更新id为1的员工的工资为15000： update employee set salary = 15000 where id = 1;

（三）删除语句

delete from tbl_name [WHERE where_definition]

删除id为1的数据： delete from employee where id = 1;

• 如果不使用where子句，将删除表中所有数据。
• Delete语句不能删除某一列的值。使用 alter table tb_name drop col_name;删除列
• 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。
• 删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同。

truncate 与 delete from 区别？

• truncate 是将整张表摧毁并重建，表中数据会被清空和表结构会重建。
• delete from 是逐行删除表中数据。所以truncate的删除效率要高于delete from
• 由于truncate会摧毁表结构，在面对多表操作时，这种删除方式，可能会影响表与表直接的关系。所以多表关系中不推荐使用truncate删除数据。

（四）查询语句

【1】基本查询语句

SELECT [DISTINCT] *|{column1, column2.column3..}FROM table;

1、*（使用 * 代表所有字段）

select * from employee;

select * 与 select 字段名 哪个效率高？

• 直接书写字段名称比select *效率稍高。原因是直接书写具体的字段名称，select会按照字段名称的内容直接查询对应字段。而select * 要先判断当前表格有哪些字段，再选择这些字段对应的内容。
• 但是上述内容影响时间较小，几乎可以忽略不计。影响两者查询效率的本质是，是否使用索引。由于在当前表中id字段是一个索引字段，只要在查询中使用了id，即可提高查询效率。所以书写全部字段名和select *都会选择id字段，查询效率自然都会很高。使用uuid方式进行设置的id也是具有索引效果的。

2、as （as作为别名查询）

select id, name as 姓名, chinese as 语文, math as 数学, english as  英语 from exam ;

3、distinct （distinct用于去重）

select ditinct english from exam;

4、过滤查询 

>、 < 、<= 、>=、 =、<>	大于、小于、大于等于、小于等于、不等于
between ...and...	显示在某一区间的值
in(set)	显示在in列表中的值，例：in(100,200)
like ‘%’	模糊查询     %（匹配多个）_（匹配一个）
is null	判断是否为空
ifnull(原值,替代值)	如果原值为null，则使用代替值 select ifnull(score,0) from exam;
and	多个条件同时成立
or	多个条件任意一个成立
not	不成立，例：where not(salary>100);

5、排序查询 （desc 降序， asc 升序默认）

 select * from exam order by chinese asc;

• 建议： 
• order by 不推荐使用。因为order by是对于已经查询好的结果，进行排序，这个排序过程十分漫长。 所以有order by 不推荐使用。

【2】聚集函数

1、count

统计表中的数据总量：select count(*) from table_name;

count(1)与count(*)区别？

• 在面对大量数据时，查询效率没有较大差距，可以认为是效率相同。

建议：

• 尽量不要使用count(col_name);因为如果字段名中存在null，则不会进行计数

2、SUM

统计班级语文、英语、数学各科的总成绩：select sum(chinese),sum(math),sum(english) from exam;

有多个字段需要求和操作的时候，使用逗号隔开。

3、AVG

求一个班级数学平均分：select avg(ifnull(math,0)) from exam;

ifnull()当math中存在null数据时，使用0替换。

4、MAX/MIN 

求一个班的最高分和最低分：select max(math+chinese+english),min(math+chinese+english) from exam;

子查询操作

• 在一个查询语句中，添加where子句，where子句中的条件是使用另一个查询语句来实现的。

查询一个班里语文成绩最高学生的信息

select * from exam where chinese = ( select max(chinese) from exam );

【3】分组查询

SELECT column1, column2. column3.. FROM table group by column having ...

查询购买了几类商品，并且每类总价大于100的商品

select product,sum(price) from orders group by product having sum(price)>100;

where与having区别？

• where在分组前进行条件过滤，having在分组后进行条件过滤。
• 使用where的地方都可以用having替换。但是having可以使用分组函数，而where后不可以使用。

【4】MySQL关键词执行顺序

from--where--group by--having--select--order by

（五）多表查询

【1】关于外键

外键：可以使用外键约束来通知数据库维护表与表之间数据的完整性和正确性。

格式：foreign key(外键) s references 涉及的表(涉及的表的主键)

案例：

create table dept(
id int primary key auto_increment,
name varchar(20)
);

create table emp(
id int primary key auto_increment,
name varchar(20),
dept_id int,
foreign key(dept_id) references dept(id)
);

建议：

外键不推荐使用。在高吞吐的情景下，应该优先保证数据入库，如果使用外键，则可能会发生数据无法正确入库的情况，造成数据丢失。

所以不推荐使用外键。

【2】笛卡尔积查询

select * from dept,emp;

笛卡尔积查询就是一张表的每一个字段都与另一张表的每一个字段建议联系。

【3】内连接查询

select * from dept inner join emp on dept.id =emp.dept_id;

在笛卡尔积查询的基础之上，获取左边表有且右边表也有的数据。（dept 与 emp 是两张表）

【4】左外连接查询

在内连接查询的基础之上，获取左边表有但右边表没有的数据。

select * from dept left join emp on dept.id = emp.dept_id;

【5】右外连接查询

在内连接查询的基础之上，获取右边表有但左边表没有的数据。

select * from dept right join emp on dept.id = emp.dept_id;

【6】全外连接查询

在内连接的基础之上，获取左边表有且右边表没有的数据 和 右边表有且左边表没有的数据。

mysql中没有全外连接关键字。

union 联合--将两个查询结果合并，并去除重复内容。

select * from dept left join emp on dept.id = emp.dept_id

union

select * from dept right join emp on dept.id = emp.dept_id;

（六）数据库的备份与恢复

1.备份数据库表中的数据

在cmd命令行输入：

mysqldump -u 用户名 -p 数据库名 > 文件名.sql

然后输入密码就可以了。

例：mysqldump -uroot -p mydb1> d:/a/mydb1.sql

 2. 恢复数据库 

（注意：如果数据库已经删除，先创建数据库再恢复数据。）

mysql –u 用户名 -p 数据库名 < 文件名.sql

mysql -uroot -p mydb1 < d:/a/mydb1.sql

或者进入MySQL里输入

source d:/a/mydb1.sql