mysql更改用户名和密码及mysql常用命令

安装时默认用户名为“root”，密码为安装时设定的

想改的话很容易。以下为过程。

改用户名

local@local-Lenovo-G470:~$ su
密码： 
root@local-Lenovo-G470:/home/local# MySQL -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 48
Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)


Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> use mysql;  选择数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> update user set user="dns" where user="root";    将用户名为root的改为dns
Query OK, 4 rows affected (0.12 sec)
Rows matched: 4  Changed: 4  Warnings: 0


mysql> flush privileges;    刷新权限
Query OK, 0 rows affected (0.04 sec)


mysql> exit
Bye
root@local-Lenovo-G470:/home/local# 

改密码用mysqladmin -u 用户名 -p password 新密码

提示输入旧密码，输入即可。

mysql常用命令

查看数据库

mysql> show databases;

 

选择数据库

mysql> use bugfree;

 

设置字符集

mysql> set names 'gbk';

 

查询数据库中的表

mysql> show tables;

 

MySQL基本操作创建表

mysql> create table test(

 

-> tid int(10) not null,

 

-> tname varchar(100) not null,

 

-> tdate datetime not null default '0000-00-00',

 

-> primary key (tid));

 

查看表结构

mysql> desc test;

 

添加列

mysql> alter table test add(tage int(3));

 

修改原表结构

mysql> alter table test modify tage int(5) not null;

 

修改列的默认值

mysql> alter table test alter tage set default '0';

 

去掉列的默认值

mysql> alter table test alter tage drop default;

 

删除列

mysql> alter table test drop column tage;

 

插入数据

mysql> insert into test(tid,tname,tdate) value(1,'yangjuqi','2008-03-21');

 

查询数据

mysql> select * from test;

 

模糊查询

mysql> select * from test where tname like '%杨%';

 

修改数据

mysql> update test set tname='张三' where tid='2';

 

MySQL基本操作删除数据

mysql> delete from test where tid='2';

 

删除表

mysql>L> drop table test;

 

重命名表

mysql> alter table test rename testbak;

 

分页查询(limit 起始行，取多少行)

mysql> select * from testbak limit 2,1;

 

刷新数据库

mysql> flush privileges;

 

显示数据库版本

mysql> select version();

 

显示当前时间

mysql> select current_date;

 

将查询出的数据写入文件

mysql> select * from user into outfile "/etc/test.txt" fields terminated by ",";

 

查看数据库状态

mysql> status;

 

MySQL基本操作查看所有编码

mysql> show variables like 'character_set_%';

 

导入sql文件命令

mysql> source /etc/MySQL.sql;