1.创建
CREATE TABLE IF NOT EXISTS `table_name`(
`id` INT UNSIGNED AUTO_INCREMENT,
`age` INT NOT NULL,
`author` VARCHAR(40) NOT NULL,
`date` DATE,
PRINARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf-8;
2.删除
DROP TABLE tablename；
3.插入数据
INSERT INTO tablename
(id, age, author, date)
VAlUES
(1, 18, "OK", NOW());
4.查询数据
SELECT id,age
FROM tablename
WHERE
id=2;
5.更新数据
UPDATE tablename
SET age=22
WHERE
id=2;
6.删除
DELETE FROM tablename
WHERE
id=2;
7.LIKE
SELECT * FROM tablenam
WHERE
auther LIKE '%com';
8.GROUP BY
SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
9.事务
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
mysql> select * from runoob_transaction_test;
mysql> begin; # 开始事务
Query OK, 0 rows affected (0.00 sec)
mysql> insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
mysql> rollback; # 回滚
Query OK, 0 rows affected (0.00 sec)
mysql> select * from runoob_transaction_test; # 因为回滚所以数据没有插入
10.ALTER
mysql> ALTER TABLE testalter_tbl ADD i INT;
mysql> ALTER TABLE testalter_tbl DROP i;
11.索引
创建：CREATE INDEX indexName ON table_name (column_name)；
增加：ALTER table tableName ADD INDEX indexName(columnName)；
删除：ALTER TABLE testalter_tbl DROP INDEX c;
创建表时创建索引：
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );
12.显示表结构
mysql> SHOW CREATE TABLE runoob_tbl \G;
13.导出数据
库外：mysqldump -uroot -p123456 dbname > dbname.sql
库内：mysql> SELECT * FROM runoob_tbl INTO OUTFILE '/tmp/runoob.txt';
14.导入数据
库内：mysql> source /home/abc/abc.sql
库外：mysql -uroot -p123456 < runoob.sql
15.备份所有数据库
mysqldump -uroot -p --all-databases > /backup/all.db
16.备份指定数据库指定表(多个表以空格间隔)
mysqldump -uroot -p mysql db event > /backup/mysqldump/2table.db
备份指定数据库排除某些表
mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.db
17.还原命令
mysqladmin -uroot -p create db_name
mysql -uroot -p db_name < /tmp/db_name.db
注：在导入备份数据库前，db_name如果没有，是需要创建的； 而且与db_name.db中数据库名是一样的才可以导入。
