mysql常用到的查询语句

记录一下,不要老是问我了。

以id字段为依据,选择两个表中的字段
SELECT a.name as name, b.name as nickname FROM a, b WHERE a.id = b.id;

以id字段为依据,把b的name更新到a的name
UPDATE a,b SET a.name = b.name WHERE a.id = b.id;

删除字段内容中间的空格
UPDATE a SET name=REPLACE(name,' ','');

删除字段内容左右两边的空格
UPDATE a SET name=TRIM(BOTH ' ' FROM name);

移动mysql数据库文件夹

os: ubuntu 12.04 server 32bit
mysql-server: 5.5.35-0ubuntu0.12.04.1

1.停止mysql
sudo service mysql stop

2.复制/移动mysql数据库文件夹
sudo cp -R -p /var/lib/mysql /srv

3.修改配置文中的datadir
vi /etc/mysql/my.cnf
修改为
datadir = /srv/mysql

4.修改apparmor
修改apparmor,允许新文件夹的权限,有两个地方需要修改。
vi /etc/apparmor.d/usr.sbin.mysqld
添加下面两行
/srv/mysql/ r,
/srv/mysql/** rwk,

vi /etc/apparmor.d/abstractions/mysql
添加下面一行
/srv/mysql/mysql.sock rw,

5.重启
sudo service apparmor restart
sudo service mysql restart

mysql因连接过多出错

提示
Host 'labeyes' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

原因
同一个ip在短时间内产生太多(超过mysql数据库max_connection_errors的最大值),导致中断的数据库连接而导致的阻塞。

解决
方法一:使用mysqladmin flush-hosts清空
也可以在数据库中执行 flush hosts; 来实现

方法二:提高允许的max_connection_errors数量:
1.进入Mysql数据库查看max_connection_errors:
show variables like 'max_%';

2.修改max_connection_errors的数量为100:
set global max_connect_errors = 100;

mysql查询重复记录

MYSQL查询重复记录的方法很多,下面就介绍几种最常用的MYSQL查询重复记录的方法。

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

select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录

delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

3、查找表中多余的重复记录(多个字段)

select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

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

delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录

select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

mysql 存储过程笔记

1.在命令中应设置分隔符。
2.有输入输出参数要标明,应以begin开始,end 加分隔符结束。
3.参数、变量均不能和列名一样。就算有大小写区分也不行。
简单例子如下:

DELIMITER //

Create PROCEDURE sp_GetTeacherByCornet ( in CORNET int(11) )
BEGIN
Select t.Id,t.Exceptionid,dept.name,t.name,t.cornet,t.pass
FROM teacher t,department dept
Where  t.Exceptionid = dept.Id AND t.cornet LIKE CONCAT('%',CORNET,'%');
END //

带循环的例子:

DELIMITER //

Create PROCEDURE sp_InsertID ( in IMAGE_ID_START int(11), in IMAGE_ID_END int(11),in TAG_ID int(11))
BEGIN DECLARE i INTEGER;
SET i=IMAGE_ID_START;
WHILE i<=IMAGE_ID_END DO BEGIN
Insert piwigo_image_tag(image_id,tag_id) VALUES(i,TAG_ID);
SET i=i+1;
END;
END WHILE;
END//

执行存储过程:
call sp_InsertID(1,5)