数据CRUD操作

​ CRUD即是指增加(Create)、检索(Retrieve)、更新(Update)和删除(Delete)，也是我们口中经常所说的增删改查。

插入数据

1、插入单条数据

INSERT INTO 表名 VALUES(val_list);

INSERT INTO 表名(col_list) VALUES(val_list);

2、插入多条数据

INSERT INTO 表名(col_list) VALUES(val_list1),(val_list2),(val_list3)(val_list4);

3、备份数据表（表不存在）

CREATE TABLE emp_bak AS SELECT * FROM emp;

4、备份数据表（表已存在）

INSERT INTO emp_bak SELECT * FROM emp; 

更新数据

UPDATE table_name SET col=val,col=val [WHERE <condition>]; 

例：将列id为1的数据更新为（Jerry，延安，118）

UPDATE emp SET empname='jerry',empaddr='延安',empphone='118' WHERE empid=1;

删除数据

DELETE FROM table_name [WHERE <condition>];

​ 注意：如果删除语句没where,此时会将数据表中的记录全部删除，类似TRUNCATE TABLE。

TRUNCATE TABLE table_name；

​ 注意：TRUNCATE将直接删除原来的表，并重新创建一个表，TRUNCATE直接删除表而不是删除记录，因此执行速度比DELETE快。而且不能用在有主外键关系的主表中。

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

查询数据

MySQL从数据表中查询数据的基本语句为SELECT语句。SELECT语句的基本格式是：

SELECT 字段列表  FROM 表1,表2... WHERE 表达式  GROUP BY 字段  HAVING 条件
ORDER BY 字段 LIMIT  [<offset>,] <row count>]

单表查询

1、查询所有字段

SELECT * FROM 表名; 

2、查询指定字段

SELECT 列名 FROM 表名;

3、查询多个字段

SELECT 字段名1,字段名2,…,字段名n FROM 表名;

例：查询emp表中id、name、sex、birth

select id,name,sex,birth from emp;

4、查询指定记录

数据库中包含大量的数据，根据特殊要求，可能只需要查询表中的指定数据，即对数据进行过滤。在SELECT语句中，通过WHERE子句可以对数 据进行过滤，语法格式为：

SELECT 字段名1,字段名2,…,字段名n FROM 表名 WHERE 查询条件 

例：查询emp表中性别为女的员工信息

SELECT * FROM emp WHERE sex='女'; 

5、带in关键字的查询

​ IN操作符用来查询满足指定范围内的条件的记录，使用IN操作符，将所有检索条件用括号括起来，检索条件之间用逗号分隔开，只要满足条件范围内的一个值即为匹配项。

SELECT * FROM emp WHERE id=1 OR id=4 OR id=8 OR id=10;
SELECT * FROM emp WHERE id IN (1,4,8,10);

在IN关键字前面加上了NOT关键字，这使得查询的结果与前面一个的结果正好相反，

SELECT * FROM emp WHERE id NOT IN (1,4,8,10);

6、带between and关键字的查询

​ BETWEEN AND用来查询某个范围内的值，该操作符需要两个参数,即范围的开始值和结束值，如果字段值满足指定的范围查询条件，则这些记录被返回。

SELECT * FROM emp WHERE id>=6 AND id<=10;
SELECT * FROM emp WHERE id BETWEEN 6 AND 10;

​ 同样，BETWEEN AND操作符前可以加关键字NOT，表示指定范围之外的 值，如果字段值不满足指定的范围内的值，则这些记录被返回。

​ 注意：使用between and一定是between 小值 and 大值。

7、带like的字符匹配查询

​ 通配符是一种在SQL的WHERE条件子句中拥有特殊意思的字符。SQL 语句中支持多种通配符，可以和LIKE一起使用的通配符有’%‘和’_’。

1. 百分号通配符‘%’，匹配任意长度的字符，甚至包括零字符

2)下划线通配符‘_’，一次只能匹配任意一个字符

SELECT * FROM emp WHERE name LIKE 'a%'; -- 以a开头
SELECT * FROM emp WHERE name LIKE '%a'; -- 以a结尾
SELECT * FROM emp WHERE name LIKE '%a%';  -- 包含a

例：名字是两个字母的而且是a开头的

SELECT * FROM emp WHERE name LIKE 'a_';

8、查询空值

​ 数据表创建的时候，设计者可以指定某列中是否包含空值（NULL）在SELECT语句中使用IS NULL子句，可以查询某字段内容为空记录。与IS NULL相反的是NOT NULL，该关键字查找字段不为空的记录。

例：空的查询 查询地址为空的员工信息

SELECT * FROM emp WHERE address IS NULL;
SELECT * FROM emp WHERE address ='';
SELECT * FROM emp WHERE address IS NULL OR address='';

非空查询

SELECT * FROM emp WHERE address IS NOT NULL;
SELECT * FROM emp WHERE address !='';
SELECT * FROM emp WHERE address IS NOT NULL AND adress!=''

9、带and的多条件查询

​ MySQL在WHERE子句中使用AND操作符限定只有满足所有查询条件的记录才会被返回。（也就是我们说的且关系）

例：查询性别为男同时地址是公学北路

SELECT * FROM emp WHERE sex='男' AND empaddr='公学北路'; 

10、带or的多条件查询

​ 与AND相反，在WHERE声明中使用OR操作符，表示只需要满足其中一个条件的记录即可返回。（也就是我们说的或关系）

11、查询结果不重复

​ 在SELECT语句中，可以使用DISTINCT关键字指示MySQL消除重复的记录值。

SELECT DISTINCT empid,empname,empsex FROM emp;

12、对查询结果排序

​ 使用ORDER BY子句对指定的列数据进行排序。 desc:倒叙 asc:默认值，升序

​ 1）单列排序

SELECT * FROM emp ORDER BY id DESC; 

​ 2）多列排序

SELECT * FROM emp ORDER BY id DESC ,deptid DESC;

13、聚合函数

​ 有时候并不需要返回实际表中的数据，而只是对数据进行总结。 MySQL提供一些查询功能，可以对获取的数据进行分析和报告。

函数	作用
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列的总和

​ 1）COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果 返回列中包含的数据行数。其使用方法有两种：
​ COUNT(*)计算表中总的行数，不管某列是否有数值或者为空值。
​ COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行。
​ 2）SUM()是一个求总和的函数，返回指定列值的总和。 SUM()函数在计算时，忽略列值为NULL的行。
​ 3）AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。
​ 4）MAX()函数返回指定列中的最大值。
​ MAX()函数除了用来找出最大的列值或日期值之外，还可以返回任意列 中的最大值，包括返回字符类型的最大值。在对字符类型数据进行比较 时，按照字符的ASCII码值大小进行比较，从a~z，a的ASCII码最小，z的 最大。在比较时，先比较第一个字母，如果相等，继续比较下一个字符，一直到两个字符不相等或者字符结束为止。例如，‘b’与‘t’比较时，‘t’为最大值；“bcd”与“bca”比较时，“bcd”为最大值。
​ 5）MIN()函数与MAX()函数类似，不仅适用于查找数值类型，也可应用于字符类型。

14、分组查询

​ GROUP BY关键字通常和集合函数一起使用

例：查询每个部门的人数

SELECT deptid,COUNT(1) FROM emp GROUP BY deptid;

例：统计每个部门的男女人数

SELECT deptid,empsex,COUNT(1) FROM emp GROUP BY deptid,sex;

可以和之前的排序一起运用：分组后再排序

SELECT deptid,empsex,COUNT(1) FROM emp GROUP BY deptid,sex  ORDER BY
deptid DESC,COUNT(1) DESC; 

15、使用HAVING过滤分组

​ GROUP BY可以和HAVING一起限定显示记录所需满足的条件，只有满 足条件的分组才会被显示

例：对于统计的每个部门的男女人数只要大于等于3的信息

SELECT deptid,empsex,COUNT(1) FROM emp GROUP BY deptid,empsex HAVING
COUNT(1)>=3  ORDER BY COUNT(1) DESC,deptid DESC;

表关系

​ 表和表之间存在1对1、1对多、多对多、自关联的关系。

1、1对1的关系

例：人和身份证的关系

2、1对多的关系

例：一个班的课程与成绩之间的关系。一个课程对应多个成绩。

3、多对多的关系

例：游戏中用户和角色之间的关系。一个用户可以创建多名角色，一个角色也可以被多名用户创建。