MySQL- - 基本CRUD操作

CRUD是指对数据库的一系列操作，C（create）增加、R（retrieve）检索、U（update）更新、D（delete）删除。

1、插入数据
-- 插入数据
insert into 表名 values(val_list);
或
insert into 表名(col_list) values(val_list);
-- 可以插入多条记录
insert into 表名(col_list) values(val_list1),(val_list2),(val_list3);
》备份数据表

①如果表不存在，此时可以将其他表的查询结果作为数据

create table emp_bak as select * from emp;

【注意：查询数据时，不建议使用*号，会降低性能，此处用*是为了方便演示】
②如果表存在，此时可以直接使用insert插入数据

insert into emp_bak select * from emp;

【表存在时候，没有as关键字】

2、更新数据
update table_name set col=val,col=val [where <condition>];
-- update offices set address = "涪城区" where city = "绵阳";

【注意：不要忘了set关键字！！】

3、删除数据
delete from table_name [where <condition>];
  • 如果删除语句没有where，此时会将表中的记录全部删除，类似于TRUNCATE table；
  • TRUNCATE将直接删除原来的表，并重新创建一个表，并且会清除主键自增的量（id从1开始），其语法结构为TRUNCATE TABLE table_name。TRUNCATE直接删除表而不是删除记录，因此执行速度比DELETE块，但是不能用在有主外键关系的主表。【delete删除表中的记录是可以恢复的，TRUNCATE不可以恢复】

    拓展（面试）：
    mysql为什么不建议delete数据？
    delete对性能会有影响，一般不建议硬性delete数据，而是标记deleted = 1这种软删除
    原因：
    》mysql底层存储原理最小存储单元page页，无论是非叶子节点page（page存的是索引key和指针），还是叶子节点（存的是行数据），
    1）当删除大量数据时候：
    MySQL内部不会真正删除空间，而是做标记删除，将delflag：N修改为delflag：Y，commit之后会被purge进入删除链表，如果下一次insert更大的记录，delete之后的空间不会被重用，如果插入的记录小于等于delete的记录，则会被重用
    2）当少量删除中间数据时
    你在中间删了某些值，实际上只是找到page页对应的数据做标记删除，并不会实际影响page页已经占的大小，这块可能也永远不会被利用，产生了内存碎片导致索引频繁分裂，影响SQL执行的稳定性
    ->正确方式：
    使用deleted=1字段来软删除，保证索引的连续性；
    必要时，可将deleted=0的字段完整迁移到新表，以此来解决碎片问题。
4、查询数据

单表查询：
1>查询所有字段

select * from 表名;

2>查询指定字段

select 列名 from 表名;

3>查询多个字段

select 字段名1,字段名2,...,字段名n from 表名;
-- select empid, empname, empsex, empaddr,empphone from emp;

查询的时候可以起别名

select e.empid as 员工编号, e.empname as 员工姓名 from emp e;

4>查询指定记录

select 字段名1,字段名2,...,字段名n from 表名 where 查询条件;
-- 查询性别为女的员工信息
-- select * from emp where empsex = '女';

5>带IN关键字查询
IN操作符用来查询满足指定范围内的条件的记录，使用IN操作符，将所有检索条件用括号括起来，检索条件之间使用逗号分开，只要满足条件范围内的一个值即为匹配项。
在IN关键字前面加上了NOT关键字，使得查询的结果与前面的正好相反

-- 查询编号为 1,4,8,10的员工的信息
select * from emp where empid in(1,4,8,10);
-- 查询编号不为1,4,8,10的员工的信息
select * from emp where empid not in(1,4,8,10);

6>带between and的范围查询

between and 操作符前可以加关键字not，表示指定范围之外的值，如果字段值不满足指定的范围内的值，则这些记录被返回

select * from emp where empid between 6 and 10;

7>带like的字符串匹配查询
SQL语句中支持多种通配符，可以和like一起使用的通配符的有’%‘和’_’。
①百分号通配符’%’，匹配任意长度的字符，甚至包括零字符
②下划线通配符’_’,一次只能匹配任意一个字符

select * from emp where empname like 'a%'; -- 以a开头
select * from emp where empname like '%a'; -- 以a结尾
select * from emp where empname like '%a%'; -- 名字中包含a

8>查询空值
①查询空值
查询地址为空的员工信息：

-- select * from emp where empaddr is null;
-- select * from emp where empaddr = '';
-- null值与''不一样
select * from emp where empaddr is null or empaddr = '';

②非空查询

-- select * from emp empaddr is not null;
-- select * from emp where empaddr != '';
-- null与空字符串不同
select * from emp where empaddr is not null and empaddr !='';

9>带and的多条件查询【“且”】
如：查询性别为男同时地址是涪城区的员工信息

select * from emp where empsex = '男' and empaddr = '涪城区';

10>带or的多条件查询【“或”】

-- 查询性别为男或地址是涪城区员工的信息
select * from emp where empsex = '男' or empaddr = '涪城区';

11)查询结果不重复
使用distinct消除重复记录

select distinct empid, empname, empsex from emp;

12>对结果查询排序
使用order by子句对指定的列数据进行排序。desc：倒序，asc：升序，默认值
①单列排序

select *from emp order by empid desc; -- 按照员工id降序排列

②多列排序

select * from emp order by emp_salary desc, deptid ; -- 按照员工薪资进行降序排列，薪资相同的按照部门升序排列