MySQL操作手记

启动服务器: mysqld -- console

停止服务器: mysqladmin -u root shutdown

连接服务器: mysql - h host -u user -p (mysql 127.0.0.1 -u willem -p)

断开: quit

增加数据库: create database wil091028

表增加列: alter table tablename add column cname ctype

设列为主键: add primary key (c)

创建表: create table pet (name VARCHAR(20) , owner VARCHAR(20))

自增编号: creat table 'database'.'table' ( 'id' int unsigned not null auto_increatent primary key, ... )

保存表和列信息的表: information_schema.tables information_schema.columns

创建用户: grant all privileges on *.* to 'monty'@"local host' identified by 'some_pass' with grant option

grant all privileges on *.* to 'monty'@'%' dentified by 'some_pass' with grant option

 

数据导入: mysqlimport --localhost --user=wil --password=091028 db file.txt

 

数据导出: mysqldump -u willem -p dbname tbname > d:/users.sql

 

添加列: alter table 'users' add 'cname' text not null

 

修改列: alter table 'tbname' modify column 'fieldname' varchar(14)

 

删除列: alter table tbname drop column cname

 

改列名: alter table tbname change oldcname newcname text not null

 

改表名: rename table oldtbname to newtbname

 

建外键: alter table tbname add constraint fk_name foregin key (cname) reference tbname(id) on delete restriction update restrict