查看mysql数据库及表编码格式

1.查看数据库编码格式

1mysql> show variables like 'character_set_database';

 2.查看数据表的编码格式

1mysql> show create table <表名>;

 3.创建数据库时指定数据库的字符集

mysql>create database <数据库名> character set utf8;

4.创建数据表时指定数据表的编码格式

create table tb_books (     
name varchar(45) not null,
price double not null,
bookCount int not null,
author varchar(45) not null
)
default charset = utf8;

5.修改数据库的编码格式

mysql>alter database <数据库名> character set utf8;

6.修改数据表格编码格式

mysql>alter table <表名> character set utf8;

7.修改字段编码格式

mysql>alter table <表名> change <字段名> <字段名> <类型> character set utf8; 
mysql>alter table user change username username varchar(20) character set utf8 not null;

 8.添加外键

mysql>alter table tb_product add constraint fk_1 foreign key(factoryid) references tb_factory(factoryid); 
mysql>alter table <表名> add constraint <外键名> foreign key<字段名> REFERENCES <外表表名><字段名>;

9.删除外键

mysql>alter table tb_people drop foreign key fk_1; 
mysql>alter table <表名> drop foreign key <外键名>;

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

ubuntu安装nginx、php、mysql

nginx是高性能的网站服务,发音为 engine x,网站为:http://wiki.nginx.org
在linux下安装nginx、php、mysql这套方案可以简称为lnmp或lemp。

os: ubuntu 13.10 64bit
nginx: 1.2.6-1ubuntu3.3
php5-fpm: 5.4.9-4ubuntu2.4
mysql: 5.5.34-0ubuntu0.13.04.1

1.安装nginx

sudo apt-get install nginx
如果要安装最新稳定版,可以用ppa的源
注意:使用ppa配置文件的值有点不一样,建议下载nginx源代码,使用里面的配置文件。
sudo apt-get install python-software-properties
sudo add-apt-repository ppa:nginx/stable
sudo apt-get update
sudo apt-get install nginx

2.安装php5

sudo apt-get install php5-fpm

常用的扩展

sudo apt-get install  php5-mysql php5-curl php5-gd php5-intl php-pear php5-imagick php5-imap php5-mcrypt php5-memcache php5-xmlrpc php5-xsl php-apc

不常用的扩展

sudo apt-get install  php5-ming php5-ps php5-pspell php5-recode php5-snmp php5-sqlite php5-tidy

安装完扩展之后需要重启一下fpm : sudo service php5-fpm restart

如果php5-mcrypt没生效,则需要在/etc/php5/fpm/conf.d中做个链接:

sudo ln -s ../../mods-available/mcrypt.ini 20-mcrypt.ini

3.为nginx启用php支持

修改/etc/nginx/sites-available/default,加亮的为修改的部分

server {
listen 80;

root /var/www;
index index.php index.html index.htm;

server_name localhost;

location / {
try_files $uri $uri/ /index.php;
}

location /doc/ {
alias /usr/share/doc/;
autoindex on;
allow 127.0.0.1;
allow ::1;
deny all;
}

error_page 500 502 503 504 /50x.html;
location = /50x.html {
root /usr/share/nginx/html;
}

location ~ \.php$ {
try_files $uri =404;
fastcgi_pass unix:/var/run/php5-fpm.sock;
fastcgi_index index.php;
include fastcgi_params;
}

location ~ /\.ht {
deny all;
}
}

配置完后重启nginx。

4.安装mysql-server

sudo apt-get install mysql-server

5.nginx的配置文件说明
http://my.oschina.net/winHerson/blog/143781
http://my.oschina.net/u/586648/blog/146824

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)

php的mysql数据库对比代码

php的mysql数据库对比代码。来自:这里

<?php

$dbhost='127.0.0.1'; //服务器
$dbuser='root'; //用户名
$dbpw='comet'; //密码
$dbname='cacti'; //数据库名
$dbname1='cacti-nopia'; //数据库名

header("Content-type:text/html; char=gbk");
mysql_connect($dbhost,$dbuser,$dbpw) or die('数据库连接失败');
mysql_query("set names utf8");
mysql_select_db($dbname) or die('找不到数据库');
echo '<style>*{font-size:12px;}.fenlei{width:250px;float:left;display:inline;}.pic{height:60px;float:left;display:inline;}</style>';

$tb_names=mysql_list_tables($dbname);

$tables_one=array(); //表名数组

/* 数据库所有表 */
while($row=mysql_fetch_row($tb_names)){
$tables_one[]=$row[0];
}
echo '<font style="font-size:24px" color=red>'.$dbname.'</font>该数据库含有<font color=red>'.count($tables_one).'</font>个表,显示如下:<br>';
echo '<div class="fenlei"></div>';
foreach($tables_one as $v){
echo '<div class="fenlei">'.$v.'</div>';
}
echo '</table>';

/* 结构显示 */
$the_one = array();

for($i=0;$i<count($tables_one);$i++){
//获取表结构,此处可以改造成 show columns from 表名 的方式直接获取表结构。本文采用的是获取表头
$query=mysql_query("select * from `".$tables_one[$i]."` limit 1;");
while($row=mysql_fetch_field($query)){
$fd.=str_pad(" ",2).$row->name." ".$row->type."(".$row->max_length."),<br>";
$the_one[$tables_one[$i]][] = $row->name;
$the_one[$tables_one[$i].'_detail'][] = str_pad(" ",2).$row->name." ".$row->type."(".$row->max_length."),<br>";
}
$fd=substr($fd,0,-1)."\n";
$fd=str_replace(",",",\n",$fd);

unset($fd);
}

echo "\n<br><br><br><br>===============================分割线===================================<br><br><br><br>";

mysql_connect($dbhost,$dbuser,$dbpw) or die('数据库连接失败');
mysql_query("set names utf8");
mysql_select_db($dbname1) or die('找不到数据库');
echo '<style>*{font-size:12px;}.fenlei{width:250px;float:left;display:inline;}.pic{height:60px;float:left;display:inline;}</style>';

$tb_names=mysql_list_tables($dbname1);

$tables_two=array(); //表名数组

/* 数据库所有表 */
while($row=mysql_fetch_row($tb_names)){
$tables_two[]=$row[0];
}
echo '<font style="font-size:24px" color=red>'.$dbname1.'</font>该数据库含有<font color=red>'.count($tables_two).'</font>个表,显示如下:<br>';
echo '<div class="fenlei"></div>';
foreach($tables_two as $v){
echo '<div class="fenlei">'.$v.'</div>';
}
echo '</table>';

/* 结构显示 */
$the_two = array();

for($i=0;$i<count($tables_two);$i++){
$query=mysql_query("select * from `".$tables_two[$i]."` limit 1;");
while($row=mysql_fetch_field($query)){
$fd.=str_pad(" ",2).$row->name." ".$row->type."(".$row->max_length."),<br>";
$the_two[$tables_two[$i]][] = $row->name;
$the_two[$tables_two[$i].'_detail'][] = str_pad(" ",2).$row->name." ".$row->type."(".$row->max_length."),<br>";
}
$fd=substr($fd,0,-1)."\n";
$fd=str_replace(",",",\n",$fd);
unset($fd);
}

echo "\n<br><br><br><br>==============================表对比==================================<br><br><br><br>";

$diff_1 = array_diff($tables_one,$tables_two);
$diff_2 = array_diff($tables_two,$tables_one);
if(empty($diff_1) && empty($diff_2)){
echo "数据库表一样";
}
if($diff_1){
echo "{$dbname1}少{$dbname} <font style='font-size:24;color:red'>".count($diff_1)."</font> 张表。表名:".implode(",",$diff_1);
}
if($diff_2){
echo "{$dbname}少{$dbname1} <font style='font-size:24;color:red'>".count($diff_2)."</font> 张表。表名:".implode(",",$diff_2);
}
if($diff_1 || $diff_2 ){
echo "请先对照好数据库表再进行表结构对比";
exit();
}

echo "<br><br><br><br>=============================={$dbname}与{$dbname1}表结构对比(只显示{$dbname}表比{$dbname1}多的字段,不显示{$dbname1}比{$dbname}多的字段)==================================<br><br><br><br>";
$have = true;
for($i=0;$i<count($tables_one);$i++){
$diff_struct = array();
$diff_struct[$tables_one[$i]] = array_diff($the_one[$tables_one[$i]],$the_two[$tables_two[$i]]);
if($diff_struct[$tables_one[$i]]){
$have = FALSE;
echo $tables_one[$i]."中,多".implode(',',$diff_struct[$tables_one[$i]])."字段<br>";
}
}
$have = TRUE;
echo "<br><br><br><br>=============================={$dbname1}与{$dbname}表结构对比(只显示{$dbname1}表比{$dbname}多的字段,不显示{$dbname}比{$dbname1}多的字段)==================================<br><br><br><br>";
for($i=0;$i<count($tables_two);$i++){
$diff_struct = array();
$diff_struct[$tables_two[$i]] = array_diff($the_two[$tables_two[$i]],$the_one[$tables_one[$i]]);
if($diff_struct[$tables_two[$i]]){
$have = FALSE;
echo $tables_two[$i]."中,多".implode(',',$diff_struct[$tables_two[$i]])."字段<br>";
}
}

if($have){
echo "数据库表结构一样";
}
?>