[心得]mysql基本命令整理

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

登陆mysql：
$ mysql -u root -pxxx
应该看到类似这样的提示信息才算登录成功：
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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,"");