Mysql常用命令

一.数据表字段的增、删、改、查（最常用）

（1）.数据表中增加字段

INSERT INTO table_name (field1, field2,...fieldN ) VALUES (value1, value2,...valueN );

例子：

mysql> use RUNOOB;
Database changed
mysql> INSERT INTO runoob_tbl 
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 PHP", "菜鸟教程", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)

（2）.数据表中删除字段

DELETE FROM table_name WHERE 条件;

例子：

mysql> use RUNOOB;
Database changed
mysql> DELETE FROM runoob_tbl WHERE runoob_id=3;
Query OK, 1 row affected (0.23 sec)

（3）.数据表中修改字段

UPDATE table_name SET field1=value1 WHERE 条件;

例子：

UPDATE runoob_tbl SET runoob_title="学习mysql" WHERE runoob_id =2;

（4）.查找表中出现字段

SELECT column FROM table_name WHERE 条件;

例子：

SELECT * FROM runoob_tbl WHERE runoob_id = 3;

二.项目中常用的优化举例

（1）.事务
当需要对数据库进行两次或多次操作，并且需要保证这些操作要不都成功、要不都失败时；就可以用mysql事务进行处理。

1、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

举例：

mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表
Query OK, 0 rows affected (0.04 sec)
 
mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
 
mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
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;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
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;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql>

（2）.索引，以及索引与mysql各个关键字的优化效果

https://wwwblogs/yulei126/p/6786084.html
主键也是一种索引。