MySQL单表操作

添加

INSERT [INTO] table_name [(col_name,...)] {VALUES|VALUE}

 INSERT user VALUES(NULL,'Tom','456',25,1);

 
 INSERT [INTO] table_name SET col_name={expr|DEFAULT}

 mysql> INSERT user SET username='Mary',password='123';

 INSERT [INTO] table_name SELECT......

 mysql> INSERT test(username) SELECT username FROM user WHERE age <15;

 更新
 UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET
 col_name={expr|DEFAULT}[,col_name={expr|DEFAULT}]
 [WHERE where_condition]

UPDATE user SET age = age + 5 WHERE id%2=0;

删除
DELETE FROM table_name [WHERE where_condition]

DELETE FROM user WHERE id=1;

查找
SELECT select_expr [,select_expr.....]
[
    FORM table_name
    [WHERE where_condition]
    [GROUP BY {col_name|position} [ASC|DESC],...]
    [HAVING where_condition]
    [ORDER BY {col_name|expr|position} [ASC|DESC],...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
]
 select_expr:查询表达式
 每一个表达式表示想要查询的一列，必须至少有一个
 多个列之间以逗号隔开
 *：表示所有列
 查询表达式可以使用 [AS] alias_name 为其赋予别名，用于GROUP BY，ORDRE BY，HAVING子句中
 where_condition:条件表达式
 GROUP BY ：查询结果分组
 HAVING : 分组条件
 ORDER BY :对查询结果进行排序
 LIMIT: 限制查询返回结果

SELECT VERSION();
 SELECT NOW();
 SELECT id AS userID FROM user;
 mysql> SELECT age FROM user GROUP BY age;
+-----+
| age |
+-----+
|  15 |
|  23 |
|  25 |
+-----+
 
mysql> SELECT * FROM user ORDER BY age;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  5 | May      | 456      |  15 |    0 |
|  6 | Paige    | 126      |  15 |    0 |
|  7 | Jack     | 126      |  23 |    1 |
|  2 | Mary     | 123      |  25 | NULL |
|  3 | Bob      | 456      |  25 |    1 |
|  4 | Jerry    | 456      |  25 |    1 |
+----+----------+----------+-----+------+
mysql> SELECT * FROM user ORDER BY age,id DESC;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  6 | Paige    | 126      |  15 |    0 |
|  5 | May      | 456      |  15 |    0 |
|  7 | Jack     | 126      |  23 |    1 |
|  4 | Jerry    | 456      |  25 |    1 |
|  3 | Bob      | 456      |  25 |    1 |
|  2 | Mary     | 123      |  25 | NULL |
+----+----------+----------+-----+------+

mysql> SELECT * FROM user LIMIT 1;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | Rose     | 456      |  15 |    0 |
+----+----------+----------+-----+------+
mysql> SELECT * FROM user LIMIT 1,5;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  2 | Mary     | 123      |  25 | NULL |
|  3 | Bob      | 456      |  25 |    1 |
|  4 | Jerry    | 456      |  25 |    1 |
|  5 | May      | 456      |  15 |    0 |
|  6 | Paige    | 126      |  15 |    0 |
+----+----------+----------+-----+------+