解决MySQL其他机器连接不上的问题
开启MySQL数据库的远程连接权限:显示数据库:
- grant all privileges on *.* to 'root' @'%' identified by 'wrx123';
- flush privileges;
mySQL 的root用户在安装的时候如果没有特殊的指定!是不允许其他其它机器连接的。
解决办法:在mysql命令行输入下面的命令。即可解决此问题!
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
其中:myuser位用户名,mypassword 为登录密码。
如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器,并使用mypassword作为密码
Sql代码 复制代码
1. GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY
2. 'mypassword' WITH GRANT OPTION;
3. FLUSH PRIVILEGES;
还有一种情况!也用这个解决办法!
我本地的mysql服务器,应用 localhost链接的时候没有问题!但是将localhost换成本机的ip,如192.168.0.100,就连接不上。采用上述命令!问题解决。
---------------------------------------------------------------------------------------------------------------------------------------------------------------
修改最大连接数:
如果 my.ini 文件中没有找到 max_connections 条目,可自行添加以下条目
show databases;
创建数据库:
create database testmysql;
使用数据库:
use testmysql;
显示已有表:
show tables;
创建表:
create table stuinfo(
num int(11) not null auto_increment,
name varchar(50) not null,
sex char(2) default "M",
age int,
primary key (num)
);
create table banzhang(
num int(11),
name varchar(50) not null,
sex char(2) default "M",
age int,
primary key(num),
foreign key (num) references stuinfo(num)
);
描述表:
desc stuinfo;
显示建表语句:
show create table stuinfo;
引入已有SQL:
/. SQL文件路径
插入:
insert into stuinfo values(null,'张三','男',21);
insert into stuinfo values(null,'李四','男',21);
删除表中数据:
delete from stuinfo;
删除表格:
drop table stuinfo;
分页显示:
select * from stuinfo order by num desc limit 0,3;
按倒序从第0条之后取三条,即倒数1,2,3条记录
--------------------------------------------------------------------------------------------------------------------------------------
mysql数据备份与还原命令:
Innodb 表
mysqldump -u root -p emailhunter--default-character-set=utf8 --opt --extended-insert=false --triggers -R --hex-blob --single-transaction search_record > C:\db_name.sql
emailhunter是数据库名称
还原
用 mysqldump 备份出来的文件是一个可以直接倒入的 SQL 脚本,有两种方法可以将数据导入。
直接用 mysql 客户端
例如:
/usr/local/mysql/bin/mysql -uyejr -pyejr db_name < db_name.sql
用 SOURCE 语法
其实这不是标准的 SQL 语法,而是 mysql 客户端提供的功能,例如:
SOURCE /tmp/db_name.sql;
这里需要指定文件的绝对路径,并且必须是 mysqld 运行用户(例如 nobody)有权限读取的文件。
交换两列的值:
-
update brand_title_trans a inner join brand_title_trans b on a.id=b.id set a.title=b.originaltitle ,a.originaltitle=b.title;
将一个表的查询记录插入另外一个张表:
insert into brand_title (KEYWORD,INTEREST,CATEGORYID,TITLE) SELECT 'a22',INTEREST,CATEGORYID,TITLE from brand_title b where b.INTEREST= 'ASD';
select a.TITLE,b.WEBSITE,a.website from search_record_noemail a LEFT OUTER JOIN brand_title b on b.ID = a.TITLE_ID where a.ISMAIN = '1' ;
update search_record_noemail a LEFT OUTER JOIN brand_title b on b.ID = a.TITLE_ID set a.website=b.WEBSITE where a.ISMAIN = '1' and a.website is null;
mysql 随机排序:
select id from
(SELECT t1.* FROM `brand_title` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `brand_title`)-(SELECT MIN(id) FROM `brand_title`))+(SELECT MIN(id) FROM `brand_title`)) AS id) AS t2 WHERE t1.id >= t2.id and t1.FIRSTREAD = 0 LIMIT 100)
tt
order by RAND()
CREATE PROCEDURE addtitle_task_assignment()
begin
declare i int;
set i=0;
while i<100 do
insert into title_task_assignment (clientnum,paritionid,hold) values (0,i,0);
set i=i+1;
end while;
end;
call addtitle_task_assignment();
select ENTRY_PERSON,count(1) from manual_record where date(RECORD_TIME) = date(now()) group by ENTRY_PERSON ;
按照某一天时间统计
select ENTRY_PERSON,count(1) from manual_record where DATE_FORMAT(RECORD_TIME,'%Y-%m-%d') = '2013-06-19' group by ENTRY_PERSON ;
删除重复数据:
delete a from brand_dict as a ,
(
select *,min(id) from brand_dict group by KEYWORD,CATEGORYID having count(1) > 1
) as b
where a.KEYWORD = b.KEYWORD and a.id > b.id;
Mysql中也可以使用case
select ids from (select (case when COUNT(1)=0 then 0 else id end) ids from selleck_base_emails where email ='016850@mail.fju.edu.tw') matol;