【Mysql】第六章 表增删改查（insert+duplicate+replace+select+distinct+where+order by+limit+update+delete+truncat

---

---

mysql中关键字执行顺序

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

from > where > group by > select> having > distinct > order by > limit

• from拿出表
• where初步筛选行。
• group by对数据分组。
• select将分组后数据聚合统计。
• having分组聚合统计后数据中间筛选行。
• order by对数据排序。
• limit最后筛选。

数据操作语言（DML）：SELECT、DELETE、INSERT INTO、UPDATE

一、插入-insert

1.insert

创建表之后需要插入数据

insert into 表名 (列1, 列2...) values (插入值1, 插入值2...);//(列1, 列2...)是要插入列名称， (插入值1, 插入值2...)是要插入列的值
insert into student (id, name) values (1, '张三');//向表student中的id列name列分别插入1和'张三'

2.覆盖替换-on duplicate key update

冲入后更新-duplicate key update，如果冲突就将表中当前行除了主键的其他内容用update后的数据覆盖。

insert into 表名 (列1, 列2...) values (插入值1, 插入值2...)//(列1, 列2...)是要插入列名称， (插入值1, 插入值2...)是要插入列的值
on duplicate key update 列名='描述值';

//假设下面主键是id，id为1，name为'李四'
insert into student (id, name) values (1, '张三');//insert会冲突，因为1已经存在了，错误写法
//insert冲突后更新，表中id为1，name为'张三'（'李四'被覆盖了），正确写法
insert into student (id, name) values (1, '张三')
on duplicate key update name='张三';

3.全部替换-replace

冲突后删除再插入-replace，如果冲突就将表中原本的行删除，然后重新插入

replace into 表名 (列1, 列2...) values (插入值1, 插入值2...);//(列1, 列2...)是要插入列名称， (插入值1, 插入值2...)是要插入列的值
replace into student (id, name) values (1, '张三');//向表student中的id列name列替换为1和'张三'

二、查找-retrieve（重要）

select

| 代表可以选择左侧的语句或右侧的语句

全列查找

select * from 表名;

指定列查找

select 列1,列2... from 表名;

计算列

select id+10 from student; //从表student中将列id全部+10

结果去重-distinct

注意distinct写在哪里

select distinct 列名 from 表名;

重命名列

select  列名 [as] 新列名 from 表名;

where条件

>、>=、<、<=、=、!=	where 列名 > 数值1	select * from exam_result where math>70;//选择表exam_result中math列大于70的
is null或者is not null	where 列名 is not null/where 列名 is null	select * from exam_result where math is not null;//选择表exam_result中math列不为空
between 数据1 and 数据2	where 列名 between 数值1 and 数值2	select * from exam_result where math between 70 and 80;//选择表exam_result中math列在70到80之间
in(数值1,数值2…)	where 列名 in(数值1,数值2…)	select * from exam_result where math in(60,90);//选择表exam_result中math列值为60或90的
like(%表示0个或多个字符，_表示任意一个字符)	where 列名 like ‘孙%’/where 列名 like ‘孙_’	select * from exam_result where name like ‘孙%’;//选择表exam_result中name列叫孙或者孙xx
and not or	逻辑	select * from exam_result where math >=70 and math <=80;//选择表exam_result中math列在70到80之间

注意：''和null不一样，一个是空串，一个是没有。

用is null筛选等于null的数据，筛选等于null的数据，用is not null筛选不等于null的数据，筛选不等于null的数据

用=筛选等于’'的数据，不能筛选null

结果排序-order by

asc和desc分别代表的是排升序和排降序，默认为asc

select [列1,列2...] from 表名 [where...] order by 列名1 [asc|desc], 列名2 [asc|desc];
select * from exam_result order by math;//选择表exam_result中math列按照升序
select * from exam_result order by math dese, id asc;//选择表exam_result中math列按照降序，id列按照升序

筛选分页结果-limit

select [列1,列2...] from 表名 [where...] [order by 列名] limit n;//从第0个数据开始向后筛选n个数据
select [列1,列2...] from 表名 [where...] [order by 列名] limit s,n;//从第s个数据开始向后筛选n个数据
select [列1,列2...] from 表名 [where...] [order by 列名] limit n offset s;//从第s个数据开始向后筛选n个数据

select * from exam_result limit 3,6;//从第3个数据开始向后筛选6个数据

三、更新-update set

update 表名 set 列1=数值1... [where...] [order by 列名] [limit...];//更新表中列1的数值

update exam_result set math=80 where name='孙悟空';//将孙悟空的数学修改为80
update exam_result set math=80;//将所有人的数学修改为80

四、删除-delete from

delete from 表名 [where...] [order by 列名] [limit...];

delete from exam_result where name='孙悟空';//删除孙悟空的数据
delete from exam_result;//删除表exam_result

截断(清空表)-truncate

truncate只能对整表操作，会重置AUTO_INCREMENT

truncate 表名;

插入查询结果

insert into 表名1 select [列1,列2...] from 表2 [where...] [order by 列名] [limit...];//将表2中数据插入到表1
insert into table1 select distinct * from table2;//将table2中数据插入到table1，并且去重

聚合函数

统计列方向数据

count(列名)	计数	select count(*) from student;//统计表student中记录的条数
sum(列名)	求和	select sum(math) from student where math<60;//统计表student中math<60的总和
avg(列名)	平均值	select avg(math) from student ;//统计表student中math的平均值
max(列名)	最大值	select max(math) from student ;//统计表student中math的最大值
min(列名)	最小值	select min(math) from student where math>60;//统计表student中math>60的最小值

分组查询-group by

建立表用于测试

雇员信息表中包含三张表，分别是员工表（emp）、部门表（dept）和工资等级表（salgrade）。

员工表（emp）中包含如下字段：

• 雇员编号（empno）。
• 雇员姓名（ename）。
• 雇员职位（job）。
• 雇员领导编号（mgr）。
• 雇佣时间（hiredate）。
• 工资月薪（sal）。
• 奖金（comm）。
• 部门编号（deptno）。

部门表（dept）中包含如下字段：

• 部门编号（deptno）。
• 部门名称（dname）。
• 部门所在地点（loc）。

工资等级表（salgrade）中包含如下字段：

• 等级（grade）。
• 此等级最低工资（losal）。
• 此等级最高工资（hisal）。

1.touch一个文件scott_data.sql，然后用vim写入一下代码

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `scott`;

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);


DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);


DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等级',
  `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
  `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);


insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

2.之后再mysql中source scott_data.sql，将数据库回复

group by语法

select [列1,列2...] from 表名 [where...] group by 列1, 列2.. [order by 列名] [limit...];

注意：group by的插入位置，执行顺序，where、group by、select、order by、limit

select job, max(sal) from emp group by job;//在表emp中，按照列job分组，同一组互相比，每组求出一个sal的最大值
select deptno,job,max(sal) from emp group by deptno,job;//在表emp中按照列deptno和列job分组，先是同一列deptno互相比，都是列deptno那就比较列job，最后每组求出一个sal的最大值

注意：不能写select deptno,job…group by job;//这在select后出现的列，在group by中也要出现，意思是被group by分组后的列，才能在select中打印

having条件筛选

having子句和where子句的区别

• where子句放在表名后面，而having子句必须搭配group by子句使用，放在group by子句的后面。
• where子句是对整表的数据进行筛选，having子句是对分组后的数据进行筛选。
• where子句中不能使用聚合函数和别名，而having子句中可以使用聚合函数和别名。

select ... from 表名 [where...] [group by...] having [order by...] [limit...];//from拿出表，where筛选体条件，group by分组,select统计,having筛选，order by排序，limit筛选

select deptno, avg(sal) from emp group by deptno having avg(sal) < 2000;//在emp表中，将列deptno分组，然后select统计列deptno和列avg(sal)，之后having筛选avg(sal) < 2000的数据

练习

查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J

select ename from emp where (sal>500 or job='MANAGER') and substring(ename, 1, 1)='J';

按照部门号升序而雇员的工资降序排序

select deptno, sal from emp order by deptno asc,sal desc;

使用年薪进行降序排序

select ename, 12*sal+ifnull(comm, 0) 年薪 from emp order by 12*sal+ifnull(comm, 0) desc;

显示工资最高的员工的名字和工作岗位

select ename, job  from emp where sal = (select max(sal) from emp);

显示工资高于平均工资的员工信息

select * from emp where sal > (select avg(sal) from emp);

显示每个部门的平均工资和最高工资

select max(sal), avg(sal), deptno from emp group by deptno;

显示平均工资低于2000的部门号和它的平均工资

select deptno, avg(sal) from emp group by deptno having avg(sal) < 2000;

显示每种岗位的雇员总数，平均工资

select job, avg(sal), count(ename)from emp group by job;

查询姓名重复的员工信息:名字重复，说明同一个名字有多条数据，可以先按照名字分组，分组之后再过滤行数大于1的，就表示同一个名字至少有2条记录，重复了

select ename from emp group by ename having count(ename)>1;

---