【澳门金沙vip】mysql去除重复数据

select可以取别名,delete不能。

插入INSERT(3种方式)

(一)单个字段

1.使用mysql进行delete from操作时,若子查询的 FROM
字句和更新/删除对象使用同一张表,会出现错误。

        1.INSERT  [INTO]  tb_name [(col_name, …)]  {VALUES  |
 VALUE}  ({expre  |  DEFAULT}, …), (…), …

1、查找表中多余的重复记录,根据(question_title)字段来判断

mysql> DELETE FROM tab1 WHERE col1 = ( SELECT MAX( col1 ) FROM tab1
);
ERROR 1093 (HY000): You can’t specify target table ‘tab1′ for update in
FROM clause

                $$:INSERT info_tb VALUES (DEFAULT, ‘111’, ‘小涛’,
DEFAULT, DEFAULT);

 代码如下 复制代码
select * from questions where question_title in (select
question_title from people group by question_title having
count(question_title) > 1)
 

针对“同一张表”这个限制,撇开效率不谈,多数情况下都可以通过多加一层select
别名表来变通解决,像这样

        2.INSERT  [INTO]  tb_name SET  col_name={expr  |  DEFAULT},

2、删除表中多余的重复记录,根据(question_title)字段来判断,只留有一个记录

DELETE FROM tab1
WHERE col1 = (
SELECT MAX( col1 )
FROM (
SELECT * FROM tab1
) AS t
);

                $$:INSERT info_tb SET nick_name=’小李’, age=DEFAULT;

 代码如下 复制代码
delete from questions
where peopleId in (select peopleId from people group by peopleId having
count(question_title) > 1)
and min(id) not in (select question_id from questions group by
question_title having count(question_title)>1)

 

        3.INSERT  [INTO]  tb_name [(col_name, …)]  SELECT…

(二)多个字段


                $$:INSERT cp_info_tb (name) SELECT (nick_name) FROM
info_tb WHERE id = 2;

删除表中多余的重复记录(多个字段),只留有rowid最小的记录

  1. mysql delete from where in 时后面 的查询语句里不能加where条件

更新记录(update)

 代码如下 复制代码
DELETE FROM questions WHERE (questions_title,questions_scope) IN
(SELECT questions_title,questions_scope FROM questions GROUP BY
questions_title,questions_scope HAVING COUNT(*) > 1) AND
question_id NOT IN (SELECT MIN(question_id) FROM questions GROUP BY
questions_scope,questions_title HAVING COUNT(*)>1)
 

 

    1.单表更新: UPDATE  [LOW_PRIORITY]  [IGNORE]  table_reference
 SET  col_澳门金沙vip,name1= {expr1 | DEFAULT} [, col_name2={expr2 |
DEFAULT}]…  [WHERE where_condition]

用上述语句无法删除,创建了临时表才删的,求各位达人解释一下。

Sql代码  
     
delete from `t_goods` where fi_id in (select * from ( select fi_id from `t_goods` where fs_num is null and fs_name is null and fs_type is null andfs_using is null and fs_lifetime is null) b)  

        $$: UPDATE info_tb SET token=’222′, sex=5 WHERE id = 2;

 代码如下 复制代码
CREATE TABLE tmp AS SELECT question_id FROM questions WHERE
(questions_title,questions_scope) IN (SELECT
questions_title,questions_scope FROM questions GROUP BY
questions_title,questions_scope HAVING COUNT(*) > 1) AND
question_id NOT IN (SELECT MIN(question_id) FROM questions GROUP BY
questions_scope,questions_title HAVING COUNT(*)>1);

 

删除记录

DELETE FROM questions WHERE question_id IN (SELECT question_id FROM
tmp);

Sql代码  
     
delete from `t_goods` where fi_id in (select fi_id from `t_goods` where fs_num is null and fs_name is null and fs_type is null andfs_using is null and fs_lifetime is null)   

    1.单表删除: DELETE  FROM tb_name [WHERE  where_condition]

DROP TABLE tmp;
 

 

        $$:DELETE FROM info_tb where token=’222′;

(三) 存储过程

Sql代码  
     
delete from `t_goods` where fi_id in ( select fi_id from `t_goods` )   

查询记录(SELECT):

 代码如下 复制代码
declare @max integer,@id integer