MySQL基础 [五] - 表的增删查改

目录
Create（insert）
Retrieve（select）
where条件
​编辑 NULL的查询
 结果排序(order by)
筛选分页结果 (limit)
Update
Delete 
删除表
 截断表（truncate）
插入查询结果（insert+select）
聚合函数
分组聚合统计（group by）

---

CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）
Create（insert）

语法：
[ ]内的是可以省略的

1. INSERT [INTO] table_name
3.       [(column [, column] ...)]
5.       VALUES (value_list) [, (value_list)] ...
7. value_list: value, [, value] ...

复制代码

使用：创建一个学生表 

   单行数据 + 指定列插入
  value_list 数量必须和定义表的列的数量及次序一致。value的左右两边必须值对应，类型也对应

可以不用指定id，由于mysql会用默认的值进行自增 
   单行数据 + 全列插入
  全列插入可以省略values左侧的列属性

    多行数据 + 全列插入
  多行数据用逗号隔开 

   多行数据 + 指定列插入
  

   插入是否更新
  可能会出现由于主键或者唯一键对应的值已经存在而导致插入失败的情况

这时间我们希望可以大概进行可以选择性的进行同步更新操纵​​​​而不是直接报错

1. INSERT ... ON DUPLICATE KEY UPDATE
2.     column = value [, column = value] ...

复制代码

举个例子

第一个错误是由于主键辩论，第二个错误是由于我们尝试更新的数据和其他行数据也辩论了
相当于是多做一次尝试，如果语句辩论了，就把insert操纵改成updata操纵
必要注意的是你也要保证更新的数据不要和其他行数据的主键发生辩论！！

• 0 row affected: 表中有辩论数据，但辩论数据的值和 update 的值相称
  
• 1 row affected: 表中没有辩论数据，数据被插入
  
• 2 row affected: 表中有辩论数据，并且数据已经被更新
  

也可通过 MySQL    row_count()函数获取受到影响的数据行数  (-1表现没有)

   replace
  

• 主键 或者唯一键 没有辩论，则直接插入；
  
• 主键 或者 唯一键 如果辩论，则删除后再插入
  

1 row affected: 表中没有辩论数据，数据被插入
2 row affected: 表中有辩论数据，删除后重新插入
Retrieve（select）

语法

1. SELECT
2. [DISTINCT]//去重 {* | {column [, column] ...} [FROM table_name] //从某个表里去提取
3. [WHERE ...] //筛选条件
4. [ORDER BY column [ASC | DESC], ...] //排序
5. LIMIT ...    //限定筛选出来的结果条数

复制代码

   创建表结构 并插入数据

1. CREATE TABLE exam_result (
2. id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
3. name VARCHAR(20) NOT NULL COMMENT '同学姓名',
4. chinese float DEFAULT 0.0 COMMENT '语文成绩',
5. math float DEFAULT 0.0 COMMENT '数学成绩',
6. english float DEFAULT 0.0 COMMENT '英语成绩'
7. );

复制代码

1. INSERT INTO exam_result (name, chinese, math, english) VALUES
2. ('唐三藏', 67, 98, 56),
3. ('孙悟空', 87, 78, 77),
4. ('猪悟能', 88, 98, 90),
5. ('曹孟德', 82, 84, 67),
6. ('刘玄德', 55, 85, 45),
7. ('孙权', 70, 73, 78),
8. ('宋公明', 75, 65, 30);
9. Query OK, 7 rows affected (0.00 sec)
10. Records: 7 Duplicates: 0 Warnings: 0

复制代码

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

• 查询的列越多，意味着必要传输的数据量越大（线性遍历）；
  
• 可能会影响到索引的使用 。  
  

   指定列查询
  是将表中全部的数据拿出来，然后要什么再表现什么，指定列的次序不必要按定义表的次序来 

   查询字段为表达式
   表达式不包含字段

表达式包含一个字段 

 表达式包含多个字段  

   为查询结果指定别名(as) 

1. SELECT column [AS] alias_name [...] FROM table_name;

复制代码

起多个别名

   结果筛选并去重
    我们发现98分重复了

 

1. SELECT DISTINCT column  [...] FROM table_name;

复制代码

where条件

   比较运算符
  

    逻辑运算符
  

   英语不合格的同砚即英语结果 ( < 60 ) 

1. SELECT name, english FROM exam_result WHERE english < 60;

复制代码

 
   语文结果在 [80, 90] 分的同砚及语文结果
  使用 AND 进行条件毗连 

1. SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese <= 90;

复制代码

 使用 BETWEEN ... AND ... 条件 

1. SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;

复制代码

   数学结果是 58 或者 59 或者 98 或者 99 分的同砚及数学结果 
  使用 OR 进行条件毗连

1. SELECT name, math FROM exam_result
2. WHERE math = 58
3. OR math = 59
4. OR math = 98
5. OR math = 99;

复制代码

使用 IN 条件 

1. SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);

复制代码

 _ 匹配严格的一个恣意字符

1. SELECT name FROM exam_result WHERE name LIKE '孙_';

复制代码

   语文结果好于英语结果的同砚 
  WHERE 条件中比较运算符两侧都是字段 

1. SELECT name, chinese, english FROM exam_result WHERE chinese > english;

复制代码

   总分在 200 分以下的同砚 

1. SELECT name, chinese + math + english 总分 FROM exam_result
2. WHERE chinese + math + english < 200;

复制代码

   能用别名进行查找吗？ 
  

上述代码的执行次序：先 from exam_result   再 where total < 200  末了 chinese+english+math total; 
根据上图我们会发现如果直接在筛选条件那里重定名也是不可以的！！由于对列做重定名已经是属于表现范畴了，相当于是已经把数据拿完了，然后在末了把列名字起别名，这一步是末了一步了！所以语法上不答应的！！ 
   孙某同砚，否则要求总结果 > 200 并且 语文结果 < 数学结果 并且 英语结果 > 80 
  综合性查询 

1. SELECT name, chinese, math, english, chinese + math + english 总分
2. FROM exam_result
3. WHERE name LIKE '孙_' OR (
4. chinese + math + english > 200 AND chinese < math AND english > 80
5. );

复制代码

 NULL的查询

   ‘ ’ 和NULL没有关系！！ 
  

   -- NULL 和 NULL 的比较，= 和 的区别   
  

 结果排序(order by)

语法:

1. SELECT ... FROM table_name [WHERE ...]
2. ORDER BY column [ASC|DESC], [...]; //依据哪一列做排序

复制代码

• ASC 为升序（从小到大） //ascending order
  
• DESC 为降序（从大到小） //descending order
  
• 默认为 ASC 
  

注意：没有 ORDER BY 子句的查询，返回的次序是未定义的，永远不要依靠这个次序
   同砚及数学结果，按数学结果升序表现  
  

   同砚及qq号，按姓名排序表现 
  NULL视为比任何值都小，升序出现在最上面。降序在最下面

默认是按照升序来排序 

   查询同砚各门结果，依次按数学降序，英语升序，语文升序的方式表现 
  多字段排序，排序优先级随书写次序  
也就是说先比较第一个次序，然后当两个或者多个数据第一个次序相同时，再比较第二个数据，第三个数据，以此类推

   查询同砚及总分，由高到低  
  

• ORDER BY中可以使用表达式
  
• ORDER BY子句中可以使用列别名
  

   为什么这里用别名进行排序呢？上次我们将where的时间不是不能用别名吗？
  

 由于这个排序是第四步，也就是晚于下面的任何一步

所以也就是起别名之后再进行order by操纵，故而是可以用别名来进行排序的。能不能用起的别名，完全取决与使用的次序，如果你使用的时间别名还没起呢，那肯定用不了，如果已经起过了，那就可以用了
   查询姓孙的同砚或者姓曹的同砚数学结果，结果按数学结果由高到低表现
  结合 WHERE 子句 和 ORDER BY 子句 

筛选分页结果 (limit)

   对于limit而言，也可以使用别名的缘故原由 
  

排序是必要现有数据的
只有数据准备好了，你才能表现，而limit的本质功能就是"表现" ,而不是筛选，所以limit的执行次序会更靠后，比排序还靠后。所以也可以使用别名。
Update

语法

1. UPDATE table_name SET column = expr [, column = expr ...]
2.     [WHERE ...] [ORDER BY ...] [LIMIT ...]

复制代码

对查询到的结果进行列值更新（一般要加where条件否则会全部被更新）
    将孙悟空同砚的数学结果变动为80分  
  更新值为详细值

   将曹孟德同砚的数学结果变动为60分，语文结果变动为70分  
   一次更新多个列  

   将总结果倒数前三的3位同砚的数学结果加上30分  
  数据更新，不支持math+=30这种语法   要用math = math+30这种写法

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

复制代码

   将全部同砚的语文结果更新为原来的2倍
  注意：更新全表的语句慎用！--没有WHERE子句，则更新全表

1. update exam_result set chinese=chinese*2;

复制代码

Delete 

删除表

语法

1. DELETE FROM  table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

复制代码

  删除孙悟空同砚的测验结果 

1. delete from exam_result where name='孙悟空'；

复制代码

   去掉班级的倒数第一名

1. delete from  exam_result order by english+math+chinese asc limit 1;

复制代码

   删除整张表数据
   delete只是删表数据，不删表结构。表结构是由alter来管理的
先新建表，并插入一点数据 

再查表的结构，然后再进行删除操纵 

 我们会发现我们只是把表数据给删除了，但是表的结构还在！！计数器没有变
 截断表（truncate）

语法

1. TRUNCATE [TABLE] table_name

复制代码

我们还按照刚才的例子重新测试下

 然后开始截断，并再次查看表数据和表结构

注意：这个操纵慎用
只能对整表操纵，不能像DELETE一样针对部门数据操纵；
实际上MySQL 不对数据操纵，所以比DELETE更快，但是TRUNCATE在删除数据的时间，并不经过真正的事务（不会被记载到日志里），所以无法回滚
会重置AUTO_INCREMENT项
   
  三种日志：
  bin log： 历史上操纵过的sql语句优化之后保留下来——方便主从同步、备份、恢复
  redo log：确保宕机、断电的时间数据不丢失（由于数据可能在内存中存着）——保证崩溃安全
  undo log：服务务回滚、事务的隔离性
  插入查询结果（insert+select）

1. INSERT INTO table_name [(column [, column ...])] SELECT ...

复制代码

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

 插入测试数据

不能用distinct去重，由于它不影响原表的数据，而是修改的表现的数据
但是我们可以将insert和select结合起来用，将distinct筛选出来的数据插入到空表中！！然后再改一下表的名字！！
第一步：create table no_duplicate_table like duplicate_table;创建一张和原表结构相同的空表

第二步：insert into no_duplicate_table select distinct * from duplicate_table; 查询原表去重后的结果然后插入到新表中

第三步：rename table duplicate_table to old_duplicate_table,no_duplicate_table to duplicate_table;将原表重定名备份一下，然后再把新表的名字改成原表的名字

   为什么末了是通过rename的方式进行的？
  创建一个数据库着实就是创建一个文件夹,创建一张表着实就是创建一个文件，对应的系统调用就是mkdir和touch，而rename背后的也是类似rename这样的系统调用，平时我们用的move指令重定名也是类似的，如果我本日想把一个文件上传到linux下，可能上传得很慢，我想等这个文件上传好之后，把这个文件放到某个目录下，我希望他放入的过程是原子的，所以我们一定不能直接把这个文件直接上传到对应的目录下，由于上传的过程一直在写入，一定不是原子的，所以一般我们喜好把这个要上传的文件上传到一个暂时的目录下，等全都上传完成之后，再把整个文件move到特定的目录下，这个move是原子的。
所以总的来说，用rename单纯就是相称统统都就绪了，然后统一放入、更新、生效等。由于我们的move操纵和重定名操纵实际上就是在文件系统里就是改这个文件地点的目录内里文件名和inode的映射关系，他相较于冗长地向表中插入和冗长的上传行为比起来非常轻。很有可能我这个目录有很多文件包括正在操纵的这个文件正在被外部的网站或者各种语言正在访问，所以我们不能着急动这个表而是应该先把这个表先传到暂时目录然后再统一move过去，这是一种比较推荐的做法
聚合函数

   统计班级共有多少同砚 
  使用 * 做统计，不受 NULL 影响

   统计本次测验的数学结果分数个数
  NULL 不管帐入结果 
COUNT(math) 统计的是全部结果  

 COUNT(DISTINCT math) 统计的是去重结果数量  

   统计数学结果总分 
  

   统计数学的均匀分
  

   数学不合格的人有多少
  

   统计均匀总分 
  

   返回英语最高分 
  

   返回 > 70 分以上的数学最低分
  

聚合函数：1、在应用层上更多的是在未来进行某种程度上的数据统计，是有自己的现实需求的2、大部门聚合都是简单的场景，另有一部门场景必要对信息做完分组之后做聚合 
分组聚合统计（group by）

分组的目的是为了方便背面的聚合统计 （比如说分成男生女生然后分别做统计）
在select中使用group by 子句可以对指定列进行分组查询  

1. select column1, column2, .. from table group by column;

复制代码

案例：准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）
1、EMP员工表
2、DEPT部门表
3、SALGRADE工资等级表

1. //利用source将该备份文件恢复到数据库中
2. DROP database IF EXISTS `scott`;//如果曾经有这个名字是数据库就删掉
3. CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;//创建这个数据库
5. USE `scott`;//使用这个数据库
7. DROP TABLE IF EXISTS `dept`;//如果有这个名字的部门表叫把他删掉
8. CREATE TABLE `dept` (//创建部门表
9.   `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
10.   `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
11.   `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
12. );
15. DROP TABLE IF EXISTS `emp`;//如果有这个名字的部门表叫把他删掉
16. CREATE TABLE `emp` (//创建员工表
17.   `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
18.   `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
19.   `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
20.   `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
21.   `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
22.   `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',//外键
23.   `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
24.   `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'//外键
25. );
28. DROP TABLE IF EXISTS `salgrade`;//如果有这个名字的部门表叫把他删掉
29. CREATE TABLE `salgrade` (//薪资表  可以客观反应这个员工在公司的重要程度
30.   `grade` int(11) DEFAULT NULL COMMENT '等级',
31.   `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
32.   `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
33. );
35. //插入部门
36. insert into dept (deptno, dname, loc)
37. values (10, 'ACCOUNTING', 'NEW YORK');//核算部门
38. insert into dept (deptno, dname, loc)
39. values (20, 'RESEARCH', 'DALLAS');//搜索部门
40. insert into dept (deptno, dname, loc)
41. values (30, 'SALES', 'CHICAGO');//销售部门
42. insert into dept (deptno, dname, loc)
43. values (40, 'OPERATIONS', 'BOSTON');//运营部门
45. //插入员工
46. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
47. values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
49. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
50. values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
52. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
53. values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
55. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
56. values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
58. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
59. values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
61. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
62. values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
64. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
65. values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
67. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
68. values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);
70. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
71. values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
73. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
74. values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);
76. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
77. values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);
79. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
80. values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
82. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
83. values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
85. insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
86. values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
87. //插入不同等级的薪资
88. insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
89. insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
90. insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
91. insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
92. insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

复制代码

该数据库的表结构 

表emp 

表 dept 

表salgrade 

   表现每个部门的均匀工资和最高工资 

1. select deptno,max(sal) 最高,avg(sal) 平均 from EMP group by deptno;

复制代码

• from emp：
  
  
  
  • 从 emp（员工）表中查询数据
    
  
  
• group by deptno：
  
  
  
  • 按部门编号（deptno）将员工数据分组
    
  • 意味着全部具有相同部门编号的员工会被归类到同一个组
    
  
  
• select 部门：
  
  
  
  • deptno：表现部门编号
    
  • max(sal) 最高：计算每个部门的最高工资
    
  • avg(sal) 均匀：计算每个部门的均匀工资
    
  
  

这个查询的详细含义是：

• 按部门编号（deptno）将员工表（emp）分组
  
• 对每个部门计算：
  
  
  
  • 最高工资（max(sal)）
    
  • 均匀工资（avg(sal)）
    
  
  

   groupby的宏观明确
  让我们进行分组聚合统计的。分组指定列名，实际分组是用该列的不同的行数据
比如说你按照deptno进行分组，那它就会把deptno列中，相同的数据成为一组，也就是可以被聚合压缩。
分组，就是把一组按照条件拆成了多个组，然后进行各自组内的统计。
分组也就是把一张表按照条件在逻辑上拆成了多个子表，然后分别对各自的子表进行聚合统计
   表现每个部门的每种岗位的均匀工资和最低工资
  也就是说不光要按照部门分组，也要按照岗位分组
用,来进行区分不同的组

1. select deptno,job from EMP group by deptno, job;

复制代码

再聚合，也就是进行计算所必要的数据

1. select deptno,job,avg(sal) 平均,min(sal) 最低 from EMP group by deptno, job;

复制代码

   为啥进行员工名字ename分组的时间不可呢？
  

缘故原由是ename没有在分组条件中出现，不属于分组条件，所以无法进行聚合和压缩。也就是说，select背面要想能出现，必须在group by后进行添加。
   表现均匀工资低于2000的部门和它的均匀工资
  第一步，先统计出来每一个部门的均匀工资，然后在进行对比，也就是说先把结果聚合出来

1. select deptno,avg(sal) deptavg from EMP group by deptno;

复制代码

第二步，再进行判定。就是对聚合的结果进行判定

1. select deptno,avg(sal) deptavg from EMP group by deptno having deptavg<2000;

复制代码

   那怎么对聚合的结果进行判定呢？
  having和group by共同使用，对group by结果进行过滤
having常常和group by搭配使用，作用是对聚合后的统计数据进行条件筛选，作用有些像where。 
   hvaing和where的区别明确是什么呢？ 
  

• 执行次序不同：
  
  
  
  • WHERE在分组和聚合函数计算之前执行
    
  • HAVING在分组和聚合函数计算之后执行
    
  
  
• 作用对象不同：
  
  
  
  • WHERE作用于表中的列/字段，筛选原始数据
    
  • HAVING作用于分组后的结果集，可以使用聚合函数
    
  
  

不要单纯的认为，只有磁盘上表结构导入到mysql，真实存在的表才叫做表。中间筛选出来的，包括最闭幕果，全部都是逻辑上的表！“MySQL统统皆表”。也就是说未来只要我们处理处罚好单表的CURD，全部的sql场景我们全部都能用统一的方式进行
面试题：SQL查询中各个关键字的执行先后次序 from > on> join > where > group by > with > having > select > distinct > order by > limit 

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。