一、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, -nFlush 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 privileges on *.* to root@'localhost' identified by '123456';
grant select privileges on *.* to root@'localhost' identified by '123456';
revoke all privileges on *.* from root@'localhost';
flush privileges;
忘记root密码:
在my.cnf文件中mysqld配置skip-grant-tables,重启mysqld,由于没有载入权限表,这时候可以直接mysql -uroot登录;
二、常用数据库操作
数据库管理
drop database test;
create database test default charset=utf8 collate=utf8_bin; collate校对保证排序正确。
show databases; 查看所有数据库名。
show create database chanzhi; 查看数据库结构。
show tables; 查看数据库所有表名。
show table status\G 查看数据库所有表信息。
drop table test;
create table test(id int, type varchar(20), age int);
alter table test ADD COLUMN school varchar(100) COMMENT '学校';
alter table test DROP COLUMN school;
desc eps_file;
show create table eps_file;
数据操作:
insert into test values(1, 'a', 10);
insert into test values(2, 'a', 11);
insert into test values(3, 'b', 20);
update test SET age=age+10 where type='c';
delete from test where id=3;
查询语句
1、distinct用于唯一查询,包括NULL;
2、limit [offset],count用于限制查询记录数,offset参数慎用,可能导致性能问题;
3、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 adate between '2014-05-20 00:00:00' and '2015-05-20 23:59:59'\G
字符串操作
1、字符串函数索引从1开始,查找不到返回0;
2、字符串函数默认不区分大小写,如果要区分使用binary关键字,比如select locate('ad', binary 'admin');
3、like特殊字符,%匹配任意数量字符,_匹配一个字符;
4、like、rlike、regexp默认忽略大小写,如果要区分对字段使用binary关键字,binary name like 'Pi%';
5、like '%key%',这种模糊查询会导致整表搜索,数据量大时谨慎使用;
locate(substr, str)
locate(substr, str, pos)
position(substr in str)
instr(str, substr)
substr(str, pos),pos可以用负数,表示重尾部开始。
substr(str, pos, len)
substring_index(str, delim, count),以delim分割字符串然后取子串,count可以用负数。
replace(str, from_str, to_str),from_str区分大小写,locate、position、instr不区分大小写。
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),Returns the numberX, truncated toDdecimal places.
select truncate(12345.6789, 2); 输出12345.67
select truncate(12345.6789, -2); 输出12300
分组和排序查询
select extension,sum(size) from eps_file group by extension order by sum(size) asc\G,分组后对组调用sum函数。
select type,sum(age) as total from test group by type having total > 50; having对分组的结果过滤。
select * from eps_file order by id desc, size desc limit 4\G,desc降序,asc升序。