mysql_语法整理

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中数据库名是一样的才可以导入。