mysql命令 用户以及权限管理 root密码恢复 查询数据库 字符串操作
- 2015-07-11 22:17:00
- admin
- 原创 1697
一、mysql命令
连接mysql数据库：
mysql -h127.0.0.1 -uwebadmin -p3scard dbname
mysql -h127.0.0.1 -P3306 -uwebadmin -p3scard dbname
执行sql语句：
mysql -h127.0.0.1 -uwebadmin -p3scard dbname -e "show databases;"
echo "show databases;" | mysql -h127.0.0.1 -uwebadmin -p3scard dbname
常用参数：
--unbuffered, -n Flush the buffer after each query，每次查询数据都从服务器获取；
--quick, -q Do not cache each query result，每次返回少量数据，不进行结果缓存，减少内存使用量；
--silent, -s Silent mode，Produce less output，安静模式可以减少日志输出；
--raw, -r，特殊字符不进行转义；
--no-auto-rehash, -A，禁用自动补全功能，加快命令启动速度；
用户表特性：
mysql.user表的主键是host+user，即用户由host和user决定；
用户授权时，如果不指定机器，默认user@'%'；
原则上%不包括localhost，但有些版本也包括；
用户以及权限管理：
update user set host='localhost' where user='root';
set password for root@localhost=password('123456');
grant all on *.* to root@'localhost' identified by '123456';
grant select on *.* to root@'localhost' identified by '123456';
revoke all on *.* from root@'localhost';
flush privileges;
细节特性：
1、mysql和mysqldump使用的数据库密码通过ps命令无法查看，操作系统有做特殊处理；
2、mysql客户端会存储操作记录到文件.mysql_history，删除该文件可以删除敏感操作记录；
3、sql注释在客户端解释，不会被发送到服务端，如果通过函数发送则可以发送到服务端；
4、my.cnf文件中配置skip-grant-tables，重启mysqld，由于没有载入权限表，root可以免密登录；
二、常用数据库操作
注释语法：
1、单行注释：#字符开始到行尾；
2、单行注释：--加空白字符开始到行尾；
3、多行注释：/*comment*/，/*开始注释，*/结束注释；
数据库管理
drop database test;
create database test default charset=utf8 collate=utf8_bin; 校对保证排序正确；
show create database test;
show databases;
use test，使用某个数据库；
select database(); 查看当前数据库；
show tables; 查看数据库所有表名；
show table status\G 查看数据库所有表信息；
drop table test;
create table test(id int, type varchar(32), age int);
alter table test ADD COLUMN school varchar(64);
alter table test DROP COLUMN school;
show create table eps_file;
desc eps_file;
数据操作：
insert into test values(1, 'a', 10);
insert into test values(2, 'b', 11);
insert into test values(3, 'c', 12);
update test SET age=age+10 where type='c';
delete from test where id=3;
查询语句
1、查找库show databases like 'xx%'，查找表show tables like 'xx%'；
2、查询条件包含比较操作则返回结果不包含NULL；
3、distinct用于唯一查询，返回结果包括NULL；
4、limit [offset],count用于限制查询记录数，offset参数慎用，可能导致性能问题；
5、count(1)、count(*)、count(列名)、count(distinct 列名)：
count(1)比count(*)快速；
count(列名)不统计NULL；
count(distinct 列名)不统计NULL，且唯一统计；
select distinct(列名) from test;
select * from eps_file where title in('location', 'JavaCard') \G
select count(*) from eps_file where create_date between '2014-05-20 00:00:00' and '2015-05-20 23:59:59'\G
字符串操作
1、字符串函数索引从1开始，查找不到返回0；
2、字符串函数不区分大小写，replace除外，如果要区分使用binary关键字，select locate('ad', binary 'admin')；
3、substr函数pos可以是负数，表示从尾部开始；
4、like特殊字符，%匹配任意数量字符，_匹配一个字符；
5、like '%key%'，这种模糊查询会导致整表搜索，数据量大时谨慎使用；
6、mysql使用C式转义字符，所以rlike使用双反斜杠\\表示单反斜杠\；
7、like、rlike、regexp不区分大小写，如果要区分使用binary关键字，binary name like 'Pi%'；
locate(substr, str)
locate(substr, str, pos)
position(substr in str)
instr(str, substr)
substr(str, pos)
substr(str, pos, len)
substring_index(str, delim, count)
replace(str, from_str, to_str)
concat(str1,str2,...)
select * from eps_file where pathname like '%201411%'\G
select * from eps_file where locate('201411', pathname) > 0\G
select * from eps_file where position('201411' in pathname) > 0\G
select * from eps_file where instr(pathname, '201411') > 0\G
select substr('abcdef', 4); 输出def
select substr('abcdef', 4, 2); 输出de
select substr('abcdef', -3); 输出def
select substr('abcdef', -3, 2); 输出de
select substring_index('www.mysql.com', '.', 2); 输出www.mysql
select substring_index('www.mysql.com', '.', -2); 输出mysql.com
select replace('www.mysql.com', 'w', 'Ww'); 输出WwWwWw.mysql.com
select concat('www', '.3scard', '.com'); 输出www.3scard.com
常用数值函数
truncate(X, D)，截断小数，保留指定位小数，剩余部分都丢弃。
select truncate(12345.6789, 2); 输出12345.67
select truncate(12345.6789, -2); 输出12300
分组和排序查询
1、分组查询结果只能是组名和聚集函数；
2、只有having可以对分组结果进行过滤；
3、常用聚合函数：sum、avg、group_concat；
4、排序可以使用列名、列别名、列位置，列位置从1开始；
分组查询：select extension,sum(size) from eps_file group by extension order by sum(size) asc\G
分组过滤：select type,sum(size) as total from eps_file group by type having total>1024
多个字段排序：select * from eps_file order by id desc,size desc\G