[心得]mysql基本命令整理

看过了好多数据库概念，下面要进入实战了。看基本命令。

登陆mysql：$ mysql -u root -pxxx应该看到类似这样的提示信息才算登录成功：Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 7Server version: 5.1.73 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.可以通过这个命令查看到初始的3个数据库：mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || test               |+--------------------+3 rows in set (0.13 sec)用exit退出mysql增加用户的操作为：GRANT SELECT,INSERT,UPDATE,DELETE on mydb.* to [email=tangchen@localhost]tangchen@localhost][/email] identified by "";免密操作数据创建mysql> CREAT DATABASE mydb;mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER on mydb.* to tangchen@localhost identified by "";mysql> SHOW DATABASES;mysql> CREATE DATABASE mydb;mysql> DROP DATABASE mydb;mysql> CREATE DATABASE mydb;mysql> USE mydb;mysql中select类似其他语言的print语句操作表mysql> CREATE TABLE mytbl->(-> id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,-> name CHAR(20) NOT　NULL,-> sex INT(4) NOT NULL DEFAULT '0',-> degree double(16,2));获取表的结构mysql> DESC mytbl;或者mysql> SHOW columns FROM mytbl;key有4种值：'' NULL，可以重复，该列没有索引或者是一种非唯一的符合索引的非前导列。PRI 主键UNI 唯一值索引的第一列。删除表mysql> DROP TABLE mytbl;插入数据mysql>INSERT INTO mytbl VALUES(1,'liyanhong',96.45);查询表mysql>SELECT * FROM mytbl order by id limit 0,2;删除表mysql>DELETE FROM mytbl WHERE id=1;更新表mysql>UPDATE mytbl SET name='tangchen' WHERE id=1;扩展表mysql>ALTER TABLE mytbl add score INT(4) default "0";重命名mysql>RENAME table mytbl TO mynewtbl;转存数据库$ mysqldump -uroot -pxxx mydb > mydb.dump;

最后附上一个完整的例子：

DROP DATABASE if exists TESTRECORD;CREATE DATABASE TESTRECORD;USE TESTRECORD;CREATE TABLE rts_rec_tbl(caseid INT(4) AUTO_INCREMENT NOT NULL PRIMARY KEY,name CHAR(20) NOT NULL,config CHAR(30),totalrun INT(4) DEFAULT '0',totalfail INT(4) DEFAULT '0',runfreq INT(2) DEFAULT '0',logpath CHAR(30));INSERT INTO rts_rec_tbl VALUES("","test_config","",0,0,0,"");