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