mysql 去重取出最小值_MySQL数据库---记录相关操作

序

表中记录的相关操作一共四种：插入，更新，删除、查询。其中使用最多，也是最难的就是查询。

记录的插入

1. 插入完整数据(顺序插入)

语法一：

INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

语法二：

INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2. 指定字段插入数据

语法：

INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3. 插入多条记录

语法：

INSERT INTO 表名 VALUES

(值1,值2,值3…值n),

(值1,值2,值3…值n),

(值1,值2,值3…值n);

例子：

insert into db1 values(1, "aaa", 20, "male");

insert into db1 values(1, "aaa", 20, "male"), (2, "bbb", 22, "female");

记录的更新

UPDATE 表名 SET 字段1=值1, 字段2=值2, WHERE CONDITION;

例子：

update db1 set name = "ccc" where name = "aaa";

记录的删除

DELETE FROM 表名 WHERE CONITION;

例子：

delete from db1 where name ="xxx";

记录的查询

1.单表查询

格式：

select distinct 字段1，字段2，... from 表名

where 条件

group by 分组条件

having 对分组后的数据进行筛选的条件

order by 排序

limit 分组

关键字的执行优先级：

from找到表:from

where拿着where指定的约束条件，去表中取出一条条记录

group by将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

having将分组的结果进行having过滤

select执行select

distinct去重

order by将结果按条件排序：order by

limit限制结果的显示条数

数据准备：

create table employee(

id int not null unique auto_increment,

name varchar(20) not null,

sex enum('male','female') not null default 'male',

age int(3) unsigned not null default 28,

hire_date date not null,

post varchar(50),

post_comment varchar(100),

salary double(15,2),

office int,

depart_id int

);

insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values

('aaa','male',18,'20170301','tracher',7300.33,401,1),

('bbb','male',78,'20150302','teacher',1000000.31,401,1),

('ccc','female',18,'20110211','teacher',9000,401,1),

('111','female',48,'20150311','sale',3000.13,402,2),

('222','female',38,'20101101','sale',2000.35,402,2),

('333','female',18,'20110312','sale',1000.37,402,2),

('xxx','male',28,'20160311','operation',10000.13,403,3),

('yyy','male',18,'19970312','operation',20000,403,3),

('zzz','female',18,'20130311','operation',19000,403,3);

1.1 where查询：

where查询：

查询所有员工的姓名和薪水

select name,salary from employee;

查询所有女性员工的姓名和薪水

select name,salary from employee where sex = 'female';

查询薪资在10000到30000之间的员工的姓名和薪水

select name,salary from employee where salary between 10000 and 30000;

查询薪资是19000或是7300.33员工的姓名和薪水

select name,salary from employee where salary in (19000, 7300.33);

匹配名称以a开头的员工的姓名和薪水(%：任意长度任意字符 _：一个任意字符)

select name,salary from employee where name like 'a%';

1.2 group by分组：

group by分组：

分组：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等

注意：分组发生在where之后，即分组是基于where之后得到的记录而进行的

注意：可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数

按照职位进行分组，查询职位的名称

select post from employee group by post;

按照职位进行分组，查询职位的名称和每一种职位上的员工个数，注意不能单独使用id字段，但是可以使用聚合函数

select post, count(id) from employee group by post;

1.3 聚合函数：

聚合函数：

注意：聚合函数聚合的是组的内容，若是没有分组，则默认一组

count() 计算组内个数

max() 计算组内最大值

min() 计算组内最小值

sum()计算组内和

avg()计算组内平均值

1.4 having：

having：

注意：Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

统计平均薪资超过10000的职位

select post, avg(salary) from employee group by post having avg(salary) > 10000;

1.5 select：

select：

注意：执行的是字段，字段也是可以进行计算的

查询所有员工的姓名和薪资

select name, salary from employee;

查询所有员工的姓名和年薪

select name, salary * 12 as nian_xing from employee;

1.6 distinct：

distinct：

注意：distinct后面接多个字段时，表示多个字段接续之后字符串不重复

1.7 order by：

order by：

按照薪资升序排列

select name, salary from employee order by salary asc;

按照薪资降序排列

select name, salary from employee order by salary desc;

1.8 limit：

limit：

按照薪资升序排列, 从第0开始，即先查询出第一条，然后包含这一条在内往后查3条

select name, salary from employee order by salary asc limit 0, 3;

按照薪资升序排列, 从第3开始，即先查询出第6条，然后包含这一条在内往后查3条

select name, salary from employee order by salary asc limit 3, 3;

1.9 正则表达式查询：

正则表达式查询：

查询所有名称以aa开头的员工

select * from employee where name regexp '^aa';

查询所有名称以aa结尾的员工

select * from employee where name regexp 'aa$';

查询所有名称以aa开头且以bb结尾的员工

select * from employee where name regexp '^aa.*[bb]$';

2.多表查询

2.1 表与表之间的关系

表与表之间的关系：

一对一foreign key + unique

一对多foreign key

多对多新建第三张表，建立两个字段，每个字段分别是两张表的外键

判断表关系的方法：

先站在左表的角度去找

是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段(通常是id)

再站在右表的角度去找

是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段(通常是id)

总结：

多对一：

如果只有步骤1成立，则是左表多对一右表

如果只有步骤2成立，则是右表多对一左表

多对多

如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

一对一:

如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

2.2 查询格式

格式：

select 字段列表 from 表1 inner | left | right join 表2 on 表1.字段 = 表2.字段;

2.3 数据准备

create table department(

id int,

name varchar(20)

);

create table employee(

id int primary key auto_increment,

name varchar(20),

sex enum('male','female') not null default 'male',

age int,

dep_id int

);

#插入数据

insert into department values

(200,'aaa'),

(201,'bbb'),

(202,'cc'),

(203,'dd');

(205,'ee');

insert into employee(name,sex,age,dep_id) values

('zhansan','male',18,200),

('lisi','female',48,201),

('wangwu','male',38,201),

('zhaoliu','female',28,202)

('chengyi','female',29,204);

2.4 查询方式

内连接：

不适用任何匹配条件。生成笛卡尔积

select * from employee inner join department;

根据部门ID相同过滤

select * from employee inner join department on employee.dep_id = department.id;

左连接：

将左表中没有匹配成功的内容也输出

select * from employee left join department on employee.dep_id = department.id;

右连接：

将右表中没有匹配成功的内容也输出

select * from employee right join department on employee.dep_id = department.id;

子查询：

子查询是将一个查询语句嵌套在另一个查询语句中

内层查询语句的查询结果，可以为外层查询语句提供查询条件

子查询中可以包含：in、any、all、exists等关键字

还可以包含比较运算符：= 、 !=、> 、

in：表示值存在子查询结果集中

any：表示满足子查询中的任意一个条件

all：表示满足所有子查询的条件

exists：子查询为真，外层循环正常执行；只循环为假，直接返回

查询平均年龄在25岁以上的部门名

select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);

查询大于所有人平均年龄的员工名与年龄

select name,age from emp where age > (select avg(age) from emp)；

查询为空，因为子查询返回false

select * from employee where exists (select id from department where id = 204);