mysql 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
推荐阅读
-
JBPM4.4安装(Tomcat-MySQL)方法
-
很简单的Eclipse连接MySQL数据库方法
-
python 对mysql新增,修改,删除操作
-
CentOS 7.x编译安装Nginx1.10.3+MySQL5.7.16+PHP5.2 5.3 5.4 5.5 5.6 7.0 7.1多版本全能环境
-
通过Navicat for MySQL远程连接的时候报错mysql 1130的解决方法
-
Win7下Redmine2.0.3+Mysql55+Ruby1.8.7成功安装记录分享
-
java连接mysql的jar包没有bin(mysql可视化管理工具)
-
MYSQL定时清除备份数据的具体操作
-
Mysql错误:Too many connections的解决方法
-
mysql-8.0.16 winx64的最新安装教程图文详解