MySQL5常用命令.doc

MySQL5.0常用命令?? MySQL5.0中文问题：登录时用命令：mysql?--default-character-set=gbkgb2312,utf8?-u?root?–p建表时用命令：create?table?name(name?varchar(20))[TYPE=MyISAM,]default?character?setgbkgb2312,utf8;===================== 连接服务者：mysql?-h?host?-u?user?-p=====================新增超级用户：GRANT?ALL?PRIVILEGES?ON?*.*?TO?[email=grb@%]grb@%22%[/email]?IDENTIFIED?BY?password?WITH?GRANT?OPTION;=====================返回当前所选数据库、当前用户、版本信息和当前时间：select?database(),user(),version(),now();=====================查看库、表：show?databases;use?databasename;show?tables;show?tables?from?databaseName;=====================建库、删库：create?database?if?not?exsits?library;drop?database?library;=====================建表：一个表中只能有一个auto_increment;在没指定default情况下：对于null就插入null,对于not?null,数值类型就插入0,字符串类型就插入空字符串，时间戳就插入当前时期和时间,enum类型就插入枚举组的第一条?create?table?members(member_id?int(11)?not?null?auto_increment,fname?varchar(50)?default?guo?not?null,lname?varchar(50)?not?null,tel?varchar(15),email?varchar(50)?not?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也可以用于多个字段，插入记录时要求两index不同时重复即可。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、