链接msql:
mysql -h127.0.0.1 -P3336 -pmhtx123123
创建数据库:
mysql > create database basename;
导入数据库结构:
mysql -uroot -p basename < ./basename.sql
或者:
source ./home/mysql/xx.sql;
显示数据库:
mysql > show databases;
使用数据库:
mysql > use dbname;
显示所有表:
mysql > show tables;
显示表结构:
mysql > desc tablename;
显示版本:
mysql > select version();
显示日期:
mysql > select current_date();
删除库:
mysql > drop database basename;
删除表:
mysql > drop table tablename;
改表名:
mysql > rename table tablename to newtablename;
查询:
mysql > select * from tablename where condition;
group by / order by
修改:
mysql > update tablename set columnname = "abc" where condiion;
插入:
mysql > insert into tablename(a, b, c, ...) value('a', 'b', ...)
删除:
mysql > delete from tablename where condtion;
备份数据库:
mysqldump -uroot -p -l basename >./basename.sql
备份数据表:
mysqldump -uroot -p -l --database basename --table tablename > ./tablename.sql
导出数据库结构:
mysqldum -d -uroot -p basename > .basename.sql
查看数据库路径:
mysql > show variables like "%datadir";
egg: /home/mysql/mysql_master/data/
导出数据:
mysql > select * from tablename into outfile './mypath/';
修改文件夹权限:chmod 777 /home/dir
或者:chown mysql:mysql /home/dir
查看表的状态:
mysql > check table tablename;
设置密码:
mysqladmin -uroot password "password"
修改密码:
mysqladmin -uroot -p password "password"
修改表名:
mysql > rename table tablename1 to tablename2;