查询：
在MySQL中，select的基本语法形式：
select 属性列表
from 表名和视图
[where 条件表达式]
[group by 属性名 [having 条件表达式]]
[order by 属性名 [asc|desc]]
看看具体的用例：
首先看看表的结构：
mysql> desc user;
+----------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------------+------+-----+---------+----------------+
| id | int(4) unsigned | NO | PRI | NULL | auto_increment |
| account | varchar(10) | NO | | NULL | |
| password | varchar(10) | NO | | NULL | |
+----------+-----------------+------+-----+---------+----------------+
最简单的查找，查找出全部，*匹配所有数据，user就是表名：
mysql> select * from user;
+----+------------+----------+
| id | account | password |
+----+------------+----------+
| 1 | xujianguo | xjg |
| 2 | wenjian | cwj |
| 3 | xiongsheng | hxs |
+----+------------+----------+
加入条件后的查询：
mysql> select * from user
-> where id > 1
-> order by id desc;
+----+------------+----------+
| id | account | password |
+----+------------+----------+
| 3 | xiongsheng | hxs |
| 2 | wenjian | cwj |
+----+------------+----------+
带in关键字的查询：
用in关键字可以判断某个字段的值是否在指定的集合中。
mysql> select * from user
-> where id in(1, 3);
+----+------------+----------+
| id | account | password |
+----+------------+----------+
| 1 | xujianguo | xjg |
| 3 | xiongsheng | hxs |
+----+------------+----------+
2 rows in set (0.02 sec)
带between and的范围查询：
between and关键字可以判断某个字段的值是否在指定的范围内。
mysql> select * from user
-> where id between 2 and 3;
+----+------------+----------+
| id | account | password |
+----+------------+----------+
| 2 | wenjian | cwj |
| 3 | xiongsheng | hxs |
+----+------------+----------+
带like的字符匹配查询：
like关键字可以匹配字符串。
mysql> select * from user
-> where account like 'wen%';
+----+---------+----------+
| id | account | password |
+----+---------+----------+
| 2 | wenjian | cwj |
+----+---------+----------+
分组查询：
group by关键字与group_concat函数一起使用，进行分组显示。
mysql> select * from user;
+----+------------+----------+
| id | account | password |
+----+------------+----------+
| 1 | xujianguo | xjg |
| 2 | wenjian | cwj |
| 3 | xiongsheng | hxs |
| 4 | zheng | cwj |
+----+------------+----------+ mysql> select password, group_concat(account) from user
-> group by password;
+----------+-----------------------+
| password | group_concat(account) |
+----------+-----------------------+
| cwj | wenjian,zheng |
| hxs | xiongsheng |
| xjg | xujianguo |
+----------+-----------------------+
使用count()函数查询：
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
使用max()函数查询：
mysql> select max(id) from user;
+---------+
| max(id) |
+---------+
| 4 |
+---------+
连接查询：
将两个或者两个以上的表按照某个条件连接起来。
mysql> select user.id, account, usermessage.message
-> from user, usermessage
-> where user.id = usermessage.id;
+----+------------+----------+
| id | account | message |
+----+------------+----------+
| 1 | xujianguo | hello |
| 2 | wenjian | love you |
| 3 | xiongsheng | yes |
| 4 | zheng | no no no |
+----+------------+----------+
子查询：
也就是说查询语句里面嵌套着另外一个查询语句。
mysql> select * from user
-> where id in (select id from usermessage where id in(1, 2))
-> ;
+----+-----------+----------+
| id | account | password |
+----+-----------+----------+
| 1 | xujianguo | xjg |
| 2 | wenjian | cwj |
+----+-----------+----------+
插入：
在MySQL中，可以通过不指定具体字段名为表插入记录，其基本语句形式：
insert into 表名 values(值1, 值2......)
mysql> insert into usermessage values(5, 'no problem');
Query OK, 1 row affected (0.09 sec)
这里就隐含了对应的值对应插入的规则。
当然也可以指定插入，其基本的语句格式：
insert into 表名(属性1, 属性2......) values(值1, 值2......)
mysql> insert into usermessage(message, id) values("enen", 6);
Query OK, 1 row affected (0.09 sec)
更新：
在MySQL中，update语句的基本语法形式：
update 表名
set 属性名1=值1, 属性名2=值2......
where 条件表达式;
mysql> update usermessage
-> set message = 'nimei'
-> where id = 2;
Query OK, 1 row affected (0.11 sec)
删除：
在MySQL中，delete语句的基本语法形式为：
delete from 表名 [where 条件表达式];
mysql> delete from usermessage where id = 5;
Query OK, 1 row affected (0.10 sec)