欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql mysql 

程序员文章站 2022-06-20 16:18:30
...

用户使用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

相关标签: mysql