MySQL5.0常用命令
MySQL5.0中文問題：
登錄時(shí)用命令：mysql --default-character-set=gbk<gb2312,utf8> -u root -p
建表時(shí)用命令：create table name(name varchar(20))[TYPE=MyISAM,]default character set gbk<gb2312,utf8>; 

=====================
連接服務(wù)者：
mysql -h host -u user -p

=====================
新增超級(jí)用戶：
GRANT ALL PRIVILEGES ON *.* TO grb@"%" IDENTIFIED BY 'password' WITH GRANT OPTION;

=====================
返回當(dāng)前所選數(shù)據(jù)庫、當(dāng)前用戶、版本信息和當(dāng)前時(shí)間：
select database(),user(),version(),now();

=====================
查看庫、表：
show databases;
use databasename;
show tables;
show tables from databaseName;

=====================
建庫、刪庫：
create database if not exsits library;
drop database library;

=====================
建表：
一個(gè)表中只能有一個(gè)auto_increment;
在沒指定default情況下：對(duì)于null就插入null,對(duì)于not null,數(shù)值類型就插入0,字符串類型就插入

空字符串，時(shí)間戳就插入當(dāng)前時(shí)期和時(shí)

間,enum類型就插入枚舉組的第一條?

create table members(member_id int(11) not null auto_increment,fname varc
har(50) default 'guo' not null,lname varchar(50) not null,tel varchar(15),email varchar

(50) no
t null,primary key(member_id));

not null       auto_increment      default       primary key()

=====================
索引：
create table users(
user_id int(4) not null auto_increment,
fname varchar(50) not null,
lname varchar(50) not null,
index id(user_id);

create table users(
id int(4) not null auto_increment,
fname varchar(50) not null,
lname varchar(50) not null,
index (fname,lname),primary key(id));

=====================
為已存在的表建索引：
create index indexname on tablename(column);

=====================
刪除索引：
drop index indexname on tablename;

=====================
unique索引：
用unique修飾符指定輸入字段值必須唯一.
create table users(name varchar(8),pass varchar(20),unique(name));

=====================
* fulltext全文索引

=====================
主鍵：
index也可以用于多個(gè)字段，插入記錄時(shí)要求兩index不同時(shí)重復(fù)即可。
create table firewall( host varchar(11) not null, port smallint(4) not null,access enum

('deny','allow') not null,primary key

(host,port));

=====================
* 外鍵：

刪除外鍵：alter table table_name drop foreign key key_id;

=====================
* 表類型：MyISAM、ISAM、HEAP、BerkeleyDB、InnoDB、MERGE

=====================
復(fù)制表：(create table…select不能復(fù)制鍵，要手工創(chuàng)建)
create table newTable select field1,field2 from oldTable where condition;
新建表并復(fù)制原表的若干字段：
create table newTable(newField1,newField2) select oldField1,oldField2 from oldTable;
創(chuàng)建一個(gè)空副本：
create table newTable select * from oldTable where 0=1;
另一種復(fù)制表方法：(MySQL 4.1)
create table newTable like oldTable;

=====================
描述表(查看表結(jié)構(gòu))：
describe tableName;
=====================
查看索引：
show index from tableName;

=====================
查看數(shù)據(jù)庫中表的詳細(xì)信息：
show table status from databaseName;

=====================
檢索用sql命令創(chuàng)建的表：
show create table databaseName.tableName;

=====================
修改表：
alter table members add othermessage varchar(50) not null;//添加字段
alter table tableName add primary key(email);//添加主鍵
alter table drop primary key;//刪除主鍵
alter table tableName change oldField newField auto_increment unique;//修改已存在字段
alter table tableName drop field;//刪除字段
alter table tableName add email varchar(30) after id;//在指定位置添加新字段after或first
alter table tableName alter id set default 0;//設(shè)置或刪除默認(rèn)值
alter table tableName add id int(3) auto_increment primary key first;//添加

auto_increment字段后，原有記錄會(huì)被自動(dòng)編號(hào)
alter ignore table tableName change name name varchar(10) not null unique;//設(shè)置某一字段

為unique時(shí)用ignore字句刪除重復(fù)記錄
* 添加外鍵參照；
* 更改表類型；
* 添加刪除索引；
alter table addressbook_table default character set gb2312;

=====================
重命名表：
alter table oldTableName rename to newTableName;
或：rename table oldTableName to newTableName;

=====================
刪除表：
drop table tableName;
drop table if exsits tableName;

=====================
查詢記錄：
select id,name,mail from dbname.tablename;

=====================
插入記錄：(into是可選的)
insert into members values(null,'guo','guo','7758521','mailtoguoguo@163.com',null);
insert into members(id,name,password) values(last_insert_id()+1,'tom','secret');
insert into members values(a,b,c),(d,e,f),(g,h,i);//一次插入多條記錄
插入時(shí)間：insert into time(now());

=====================
* replace

=====================
on duplicate key update
insert into menu(id,label,url) values(a,b,c) on duplicate key update label='d',url='e';

=====================
刪除所有記錄：
delete from members;

=====================
修改記錄：
 update members set tel='7654321',email='mrguoguo@mail.csdn.net' where member_id=1;

=====================
重復(fù)信息只顯示一次：
select distinct name from members;

=====================
查詢中運(yùn)用算術(shù)運(yùn)算：
select name,math+physics+chinese from grades;

=====================
使用內(nèi)建函數(shù)：
select count(*) from members;
select avg(math),avg(phisics),avg(chinese) from grades;
select min(math) from grades;
select max(math) from grades;

=====================
排序：
select * from members order by member_id,name desc;//asc

=====================
限制條數(shù)：
select * from members limit 2,3;(從第2行開始顯示，共顯示3條記錄)
select * from members limit 19,-1;//返回從第20行到表尾的記錄

=====================
分組：
select name,count(*) from tableName group by name;

=====================
having類似where
where針對(duì)所有記錄，通常與select delete update搭配
having只對(duì)經(jīng)過操作的記錄檢索，以進(jìn)一步篩選，通常與group by搭配

=====================
* 使用變量

=====================
通配符：
select * from members where name like '%guo%';

=====================
* 為表和列取別名

=====================
復(fù)制記錄：
insert into users(name,pass) select name,pass from otherTable;

=====================
導(dǎo)出記錄：
select * from tableName into outfile 'd:/abc.txt'  fields terminated by '\t' enclosed by 

'@';

=====================
將.sql或.txt文件導(dǎo)入數(shù)據(jù)
mysql> \. d:\site\grb.sql
注意：不用逗號(hào)結(jié)束。