----------------------------------------------------------------- --基本操作 ----------------------------------------------------------------- --查看数据库 mysql> show databases; --选择数据库 mysql> use bugfree; --设置字符集 mysql> set names 'gbk'; --查询数据库中的表 mysql> show tables; --创建表 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> delete from test where tid='2'; --删除表 mysql> 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; --修改用户密码 D:/phpStudy/MySQL/bin>mysqladmin -uroot -proot password yangjuqi --将查询出的数据写入文件 mysql> select * from testbak into outfile "d:/test.txt" fields terminated by ","; --查看数据库状态 mysql> status; --查看所有编码 mysql> show variables like 'character_set_%'; --导入sql文件命令 mysql>source d:/mysql.sql; 创建表: mysql> create table weather(WID int(11)NOT NULL PRIMARY KEY AUTO_INCREMENT, City ID int(11), Tianqi VARCHAR(20) default 'Good', Date date not null, foreign key(C ityID) references city(CityID) on delete CASCADE); Query OK, 0 rows affected (0.05 sec) mysql> describe weather; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | WID | int(11) | NO | PRI | NULL | auto_increment | | CityID | int(11) | YES | MUL | NULL | | | Tianqi | varchar(20) | YES | | Good | | | Date | date | NO | | NULL | | +--------+-------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) 添加check约束: mysql> alter table weather add CONSTRAINT CHECK_W_TIANQI check(Tianqi='Good' or Tianqi='Bad'); Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 添加数据: mysql> insert into weather(CityID,Tianqi,Date) value(215000, 'Bad','2011-05-01'); Query OK, 1 row affected (0.02 sec) mysql> select * from weather; +-----+--------+--------+------------+ | WID | CityID | Tianqi | Date | +-----+--------+--------+------------+ | 1 | 100000 | Good | 2011-05-01 | | 3 | 300000 | Good | 2011-05-01 | | 4 | 400000 | Bad | 2011-05-01 | | 6 | 215000 | Bad | 2011-05-01 | +-----+--------+--------+------------+ 4 rows in set (0.00 sec)