mysql基本常识

来源：互联网发布：linux上没有telnet 编辑：程序博客网时间：2024/06/11 00:12

1，查看所有用户;

select user from mysql.user;

2，导出数据库(结构+数据)

mysqldump -u root -p dbname > filename

3，导出结构:
mysqldump -opt -d dbname tablename -u root -p > file.sql

4，导出数据:
mysqldump -t dbname tablename -u root -p > file.sql

5，导入数据库
先建立用户，建立数据库，然后登陆mysql,然后:
source filename.sql

6，创建索引:
alter table final_test add index(ip) using hash;

7，分组时出现如下错误

mysql> select dt,count(dt), label from test where ip='101.116.58.221' group by dt;
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'HumanOrRobot.test.label' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

use:
select ip, dt, ANY_VALUE(label), count(label) from test where ip='101.116.58.221;
instead

8，多字段分组:
group by col1, clo2, col3, col4

9，查询结果导出到文件:
select.... into outfile 'filename' fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';
;

10，清屏:

system clear(仅限制linux)

11 查询结果按照固定格式导出:

默认按照\t分隔不同字段
select... into outfile 'filename' fields terminated by "," --optionally enclosed by '"' lines terminated by '\r\n'--

12 将查询结果建成表格:
create table tablename as
select cola as a, colb as b from ... where group order....

13 出现The total number of locks exceeds the lock table size时需要修改buffer_pool_size的值：
1, 在my.inf中修改innodb_buffer_pool_size为8G，一般改为innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的整数倍
2, 重启mysql服务
3, 查看innodb_buffer_pool_size的值, show variables like "%buffer_pool_size%";

13, 修改旧的表名

alter table old_table_name rename new_table_name;

0 0