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;