MySQL常见问题汇总及解答

MySQL常见问题汇总及解答

1、分组显示，想把一个字段相同数据放在一起查询出来

解决方案：使用排序 order by(column_name)

例如：把分数相同的学生放在一起然后查询出来

select * from student order by score desc;

如果这个字段没有用到索引，在数据库中查询会很慢，所以这个操作一般都是在java代码中完成的

2、删除表中相同字段

例如：删除emp_no重复的记录，只保留最小的id对应的记录。

CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values 
('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

解决方案：

delete from titles_test where id not in 
(select min(id) from titles_test group by emp_no);

思路：获取到满足条件的行id，然后删除不满足条件的行，即 id != (找出满足条件的id)

3、replace函数

replace(object,search,replace)
-- object 需要替换的字符串，源数据串
-- search 被替换的字符串
-- replace 替换成的字符串
-- 把object字符串用replace替换掉出现的search字符串

4、substr()函数

substr(column_name,start_index,offset)或者substr(column_name,start_index)
-- column_name 就是列名或者字符串
-- start_index 开始位置，开始位置从1开始
-- offset 偏移量

5、group_concat()函数

group concat(column_name)
-- 结合group by 语句使用，最后的效果是同一分组下的数据使用逗号连接成为新的一列

select t.name as '姓名',GROUP_CONCAT(t.course_name )  as '课程' from course t  group by t.name ;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dszqbo4i-1631616323286)(MySQL常见问题汇总及解答.assets/image-20200912233555378.png)]

6、in和exists以及not in和 not exists

• in和exists

（1）in：先把in里面的数据查出来，然后把in上层的数据和in里面的数据做一次笛卡尔积

（2）exists：先查询exists外面的数据，然后遍历外层数据，根据exists里的where条件查询数据

-- 使用含有关键字exists查找分配具体部门的员工的所有信息
select * from employees e where exists   -- 直接写exists，然后匹配条件写在exists里面的where
(select de.emp_no from dept_emp de where e.emp_no = de.emp_no);

• not in和not exists

如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表

上的索引。所以无论那个表大，用not exists都比not in要快。

7、distinct用法

• 基础用法

select distinct column_name from table_name;
-- 去除掉column_name字段相同的列，只保留一列
select distinct column_name1,column_name2 from table_name;
-- 去掉column_name1和column_name2相等的列，只保留一列

• 用group_concat(distinct name)配合group by name可以实现实现了

select *,group_concat(distinct name) from table group by name;

• 用count函数试验一下也是可以的

select *, count(distinct name) from table group by name

8、round函数

在mysql中，round函数用于数据的四舍五入，它有两种形式：

1、round(x,d) ，x指要处理的数，d是指保留几位小数

这里有个值得注意的地方是，d可以是负数，这时是指定小数点左边的d位整数位为0,同时小数位均为0；

2、round(x) ,其实就是round(x,0),也就是默认d为0；

实例

1、查询: select round(1123.26723,2);

结果：1123.27

2、查询: select round(1123.26723,1);

结果: 1123.3

3、查询: select round(1123.26723,0);

结果：1123

4、查询: select round(1123.26723,-1);

结果: 1120

5、查询: select round(1123.26723,-2);

结果：1100

5、查询: select round(1123.26723);

结果：1123

9、sum求和函数

sum(case e.type when 'no_completed' then 1 else 0 end)
-- 利用sum函数和case语句统计符合条件的列数
-- case 语句的结果是要么取1，要么取0