增删查改-MySQL

查询:

  在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)

增删查改-MySQL,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。