mysql

用户使用mysql命令配置

ln -fs /usr/local/mysql/bin/mysql /usr/bin

ln -s /usr/local/mysql/bin/mysqldump /usr/bin/mysqldump

重启：

sudo /usr/local/mysql/support-files/mysql.server restart

 

二、查询database中表信息

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'databasename';

三、查询列信息

select * from INFORMATION_SCHEMA.Columns where table_name='tablename' and table_schema='databasename';

select column_name,column_comment,column_type,is_nullable,concat(column_key,',',column_default) from INFORMATION_SCHEMA.Columns where table_name='tablename' and table_schema='databasename';

四、mysql现在已提供什么存储引擎:

show engines;

五、mysql当前默认的存储引擎:

show variables like '%storage_engine%';

六、某个表用了什么引擎

mysql> show create table 表名;

七、生成修改引擎

SET @DATABASE_NAME = 'smallbss';

SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements

FROM    information_schema.tables AS tb

WHERE   table_schema = @DATABASE_NAME

AND     `ENGINE` = 'MyISAM'

AND     `TABLE_TYPE` = 'BASE TABLE'

ORDER BY table_name DESC;

八、通过多记录表生成序列号

select (select count(*) from USER where user_id<=a.user_id) as rownum from USER a order by rownum;

九、列出MYSQL支持的所有字符集：

SHOW CHARACTER SET;

十、字符集设置

1、查询字符集

SHOW VARIABLES LIKE 'character_set_%';

2、当前MYSQL服务器字符集校验设置

SHOW VARIABLES LIKE 'collation_%';

3、显示某数据库字符集设置

show create database 数据库名;

4、显示某数据表字符集设置

show create table 表名;

5、修改数据库字符集

alter database 数据库名default character set 'utf8';

6、修改表默认字符集

alter table smallbss.settle_plan_rate convert to character set utf8;

十一、导出

1.导出所有库数据

mysqldump -uroot -p --all-databases > all.sql

2.导出数据忽略某个表 --ignore-table=dbname.tablename，可以忽略多个。

mysqldump -uroot -p123456 dbname --ignore-table=dbname.tb1 --ignore-table=dbname.tb2 > ./db_files/dsp.sql

3.导出数据和表结构：

mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql

4.只导出表结构

mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql

5.导出数据中dbname多张表（test1,test2,test3）结构及表数据用用空格隔开

mysqldump -uroot -pdbpasswd dbname test1 test2 test3>db.sql;

6.导出csv格式文件

select * from user where carrier_code='2016021' into outfile '/usr/local/mysql/data/user_Invisimax.csv'  fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';

7.导出带标题csv文件

select imsi,state,ki,opc from (select 'imsi','state','ki','opc' union select imsi,state,ki,opc from baisim where imsi like '00%' ) t into outfile '/home/data/mysql/imsi_info.csv'  fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';

十二、建空数据库

create database abc;

十三、导入

导入数据库

方法一：

（1）选择数据库

mysql>use abc;

（2）设置数据库编码

mysql>set names utf8;

（3）导入数据（注意sql文件的路径）

mysql>source /home/abc/abc.sql;

方法二：

mysql -u用户名 -p密码 数据库名 < 数据库名.sql

#mysql -uabc_f -p abc < abc.sql

十四、sql语句例子

1.有则更新无则插入

INSERT table (auto_id, auto_name) values (1, ‘yourname') ON DUPLICATE KEY UPDATE auto_name='yourname';

INSERT INTO tb (SELECT * FROM tmp)

ON DUPLICATE KEY UPDATE 

tb.`role_level` = 

(

SELECT role_level FROM tmp WHERE tmp.user_id = tb.`user_id` AND tmp.role_name = tb.`role_name`

)；

2.用一个表更新另一个表

修改1列

update student s, city c

   set s.city_name = c.name

 where s.city_code = c.code;

修改多个列

update  a,  b 

set a.title=b.title, a.name=b.name

where a.id=b.id

3.查询重复记录

select user_name,count(*) as count from user_table group by user_name having count>1;

 

十五、日期函数

SELECT DATE_ADD(expire_date,INTERVAL 2 DAY) AS OrderPayDate FROM smallbss.user

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')  

DATE_FORMAT(NOW(),'%m-%d-%Y')  

DATE_FORMAT(NOW(),'%d %b %y')  

DATE_FORMAT(NOW(),'%d %b %Y %T:%f') 

十六、用户管理

查看

mysql> select host,user,password from user ;

创建

mysql> create user  zx_root   IDENTIFIED by 'xxxxx';   //identified by 会将纯文本密码加密作为散列值存储

create user 'selectuser'@'%' IDENTIFIED BY 'baicells@NAselect';

修改

mysql>rename   user  feng  to   newuser；//mysql 5之后可以使用，之前需要使用update 更新user表

删除

mysql>drop user newuser;   //mysql5之前删除用户时必须先使用revoke 删除用户权限，然后删除用户，mysql5之后drop 命令可以删除用户的同时删除用户的相关权限

更改密码

mysql> set password for zx_root =password('xxxxxx');

 mysql> update  mysql.user  set  password=password('xxxx')  where user='otheruser'

查看用户权限

mysql> show grants for zx_root;

赋予权限

mysql> grant select on dmc_db.*  to zx_root;

GRANT SELECT ON smallbss.* TO 'selectuser'@'%';

grant all privileges on *.* to 'bossquery'@"%" identified by "12345";

回收权限

mysql> revoke  select on dmc_db.*  from  zx_root;  //如果权限不存在会报错

上面的命令也可使用多个权限同时赋予和回收，权限之间使用逗号分隔

mysql> grant select，update，delete  ，insert  on dmc_db.*  to  zx_root;

如果想立即看到结果使用

flush  privileges ;

 

grant和revoke可以在几个层次上控制访问权限

1，整个服务器，使用 grant ALL  和revoke  ALL

2，整个数据库，使用on  database.*

3，特点表，使用on  database.table

4，特定的列

5，特定的存储过程

 

user表中host列的值的意义

%              匹配所有主机

localhost    localhost不会被解析成IP地址，直接通过UNIXsocket连接

127.0.0.1      会通过TCP/IP协议连接，并且只能在本机访问；

::1                 ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1

 

grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利。

grant select on testdb.* to common_user@’%’

grant insert on testdb.* to common_user@’%’

grant update on testdb.* to common_user@’%’

grant delete on testdb.* to common_user@’%’

或者，用一条 MySQL 命令来替代：

grant select, insert, update, delete on testdb.* to common_user@’%’

 

修改用户密码：

delete from user where USER='';

FLUSH PRIVILEGES;

update mysql.user set password=password('boss123') where user='bossquery';

 

grant 创建、修改、删除 MySQL 数据表结构权限。

grant create on testdb.* to developer@’192.168.0.%’;

grant alter on testdb.* to developer@’192.168.0.%’;

grant drop on testdb.* to developer@’192.168.0.%’;

 

grant 操作 MySQL 外键权限。

grant references on testdb.* to developer@’192.168.0.%’;

 

grant 操作 MySQL 临时表权限。

grant create temporary tables on testdb.* to developer@’192.168.0.%’;

 

grant 操作 MySQL 索引权限。

grant index on testdb.* to developer@’192.168.0.%’;

 

grant 操作 MySQL 视图、查看视图源代码 权限。

grant create view on testdb.* to developer@’192.168.0.%’;

grant show view on testdb.* to developer@’192.168.0.%’;

 

grant 操作 MySQL 存储过程、函数 权限。

grant create routine on testdb.* to developer@’192.168.0.%’; -- now, can show procedure status

grant alter routine on testdb.* to developer@’192.168.0.%’; -- now, you can drop a procedure

grant execute on testdb.* to developer@’192.168.0.%’;

 

grant 普通 DBA 管理某个 MySQL 数据库的权限。

grant all privileges on testdb to dba@’localhost’

其中，关键字 “privileges” 可以省略。

 

grant 高级 DBA 管理 MySQL 中所有数据库的权限。

grant all on *.* to dba@’localhost’

MySQL grant 权限，分别可以作用在多个层次上。

1. grant 作用在整个 MySQL 服务器上：

grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。

grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库

2. grant 作用在单个数据库上：

grant select on testdb.* to dba@localhost; -- dba 可以查询 testdb 中的表。

3. grant 作用在单个数据表上：

grant select, insert, update, delete on testdb.orders to dba@localhost;

4. grant 作用在表中的列上：

grant select(id, se, rank) on testdb.apache_log to dba@localhost;

5. grant 作用在存储过程、函数上：

grant execute on procedure testdb.pr_add to ’dba’@’localhost’

grant execute on function testdb.fn_add to ’dba’@’localhost’

注意：修改完权限以后 一定要刷新服务，或者重启服务，刷新服务用：FLUSH PRIVILEGES

 

十七、查看mysql连接情况

查看mysql的最大并发连接数：

mysql> show variables like 'max_connections';

 

设置mysql最大连接数：

方法一：进入MYSQL安装目录 打开MYSQL配置文件 my.ini 或 my.cnf查找 max_connections=100   修改为 max_connections=1000 服务里重起MYSQL即可

方法二：MySQL的最大连接数默认是100客户端登录：mysql -uusername -ppassword

设置新的最大连接数为200：mysql> set GLOBAL max_connections=200

显示当前运行的Query：mysql> show processlist

 

显示当前状态：mysql> show status

Threads_connected 当前的连接数，

Connections 试图连接到（不管是否成功）MYSQL服务器的连接总数， 

Max_used_connections 服务器启动后已经同时使用过的连接最大数量（并发）

 

show processlist  显示当前正在执行的mysql连接

mysql> show processlist;

 

mysqladmin -u -p -h status    显示当前mysql状态

  mysqladmin -uroot -p -hlocalhost status

 

显示mysql的其他状态

mysqladmin -u -p -h extended-status 

 

十八、字符串

拼接字符串

 

1、CONCAT(string1,string2,…)   

string1,string2代表字符串,concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL

 

2、CONCAT_WS(separator,str1,str2,...)

说明 : string1,string2代表字符串,concat_ws 代表 concat with separator,第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。

 

分隔字符串：

从第9个字符开始截取到最后。SUBSTRING的参数有三个，最后一个是截取的长度，默认是到结尾，负数是倒数第几位

SUBSTRING(commentid,9)

 

以 - 进行拆分字符串，从第一个关键词开始取前面所有的字符串。如果上面的第三个参数修改为 -1，那么就是取倒数第一个

SUBSTRING_INDEX(commentid, '-', 1)

 

从c-11065-50 中，取出50

SELECT SUBSTRING_INDEX(checkid,'-',-1) FROM `check` WHERE checkid = 'c-11065-50'

 

取得11065的写法：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(checkid,'-',-2),'-',1) FROM check WHERE checkid = 'c-11065-50'

或者：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(checkid,'-',2),'-',-1) FROM check WHERE checkid = 'c-11065-50'

 

截取：content_13-11220-1中的13：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(commentid,9), '-', 1), '-',-1) FROM check WHERE commentid = 'content_13-11220-1'

或者

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(commentid, '-', 1), '_',-1) FROM check WHERE commentid = 'content_13-11220-1'

 

十九、Linux下彻底卸载mysql详解

一、使用以下命令查看当前安装mysql情况，查找以前是否装有mysql

1、rpm -qa|grep -i mysql

显示之前安装了：

     MySQL-client-5.5.25a-1.rhel5

     MySQL-server-5.5.25a-1.rhel5

2、停止mysql服务、删除之前安装的mysql

删除命令：rpm -e –nodeps 包名

rpm -ev MySQL-client-5.5.25a-1.rhel5 

rpm -ev MySQL-server-5.5.25a-1.rhel5

如果提示依赖包错误，则使用以下命令尝试

rpm -ev MySQL-client-5.5.25a-1.rhel5 --nodeps

如果提示错误：error: %preun(xxxxxx) scriptlet failed, exit status 1

则用以下命令尝试：

rpm -e --noscripts MySQL-client-5.5.25a-1.rhel5

3、查找之前老版本mysql的目录、并且删除老版本mysql的文件和库

find / -name mysql

查找结果如下：

find / -name mysql 

/var/lib/mysql

/var/lib/mysql/mysql

/usr/lib64/mysql

删除对应的mysql目录

rm -rf /var/lib/mysql

rm -rf /var/lib/mysql

rm -rf /usr/lib64/mysql

具体的步骤如图：查找目录并删除

注意：卸载后/etc/my.cnf不会删除，需要进行手工删除

rm -rf /etc/my.cnf