数据库处理tips

1.mysql 开放，关闭 远程授权

默认安装mysql是关闭远程连接的 这样也有助于系统的安全 但是有时候需要开启下远程连接方便操作

开启连接 mysql -uroot -p 进入mysql 输入密码

GRANT ALL PRIVILEGES ON * . * TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ;

flush privileges;

输入这两个命令即可开启mysql远程连接

关闭授权

REVOKE ALL PRIVILEGES ON *.* FROM root@”%”;
USE mysql;
DELETE FROM user WHERE User=”root” and Host=”%”;
FLUSH PRIVILEGES;

使用下面的命令可以查询用户：

select host,user from user where user="root";

结果：

+-----------+------+
| host      | user |
+-----------+------+
| %         | root |
| localhost | root |
+-----------+------+

2.删除重复数据，只留下唯一不重复

1.先多建立一列 temp，用来合并meetingId和speakerId,用“-”连接，之后就判断这一列是否相同。

ALTER TABLE cekasp_meeting_speaker ADD temp VARCHAR(20) NULL;

update cekasp_meeting_speaker set temp = concat(meetingId, '-', speakerId);

delete from cekasp_meeting_speaker where temp in (
SELECT * from (SELECT b.temp from cekasp_meeting_speaker b GROUP BY b.temp HAVING count(*) > 1)ggg)
and id not in (select * from( (select min(c.id) from cekasp_meeting_speaker c GROUP BY c.meetingId,c.speakerId HAVING count(*) > 1) ) ggggggg);

ALTER TABLE cekasp_meeting_speaker DROP temp;

2.或者直接以meetingId和speakerId作为查询

delete from cekasp_meeting_speaker where (meetingId,speakerId) in (
SELECT * from (SELECT b.meetingId,b.speakerId from cekasp_meeting_speaker b GROUP BY b.meetingId,b.speakerId HAVING count(*) > 1)ggg)
and id not in (select * from( (select min(c.id) from cekasp_meeting_speaker c GROUP BY c.meetingId,c.speakerId HAVING count(*) > 1) ) ggggggg);

问题：delete/update里，有in的查询语句时，要使用临时表才能进行删除：

原本的

SELECT b.meetingId,b.speakerId from cekasp_meeting_speaker b GROUP BY b.meetingId,b.speakerId HAVING count(*) > 1

应该要使用

select * from ... as NAME

包起来，作为一个临时表。

3.两张表表结构一样，将一张表中数据插入另一张表

insert into table1 (SELECT * from table2 WHERE id not in (SELECT id from table1))

4.查询某张表的信息

select * from information_schema.COLUMNS where TABLE_SCHEMA = (select database()) and TABLE_NAME="paper";