mysql> show tables; +-------------------+ | Tables_in_pikachu | +-------------------+ | httpinfo | | member | | message | | users | | xssblind | +-------------------+ 5 rows in set (0.00 sec)
列出表中的所有记录
查看表格内容
SELECT * FROM [库名.]表名；
1 2 3 4 5 6 7 8 9 10 11 12 13
mysql> select * from member; +----+----------+----------------------------------+------+-------------+-----------------------+-------------------+ | id | username | pw | sex | phonenum | address | email | +----+----------+----------------------------------+------+-------------+-----------------------+-------------------+ | 1 | vince | e10adc3949ba59abbe56e057f20f883e | boy | 18626545453 | chain | vince@pikachu.com | | 2 | allen | e10adc3949ba59abbe56e057f20f883e | boy | 13676767767 | nba 76 | allen@pikachu.com | | 3 | kobe | e10adc3949ba59abbe56e057f20f883e | boy | 15988767673 | nba lakes | kobe@pikachu.com | | 4 | grady | e10adc3949ba59abbe56e057f20f883e | boy | 13676765545 | nba hs | grady@pikachu.com | | 5 | kevin | e10adc3949ba59abbe56e057f20f883e | boy | 13677676754 | Oklahoma City Thunder | kevin@pikachu.com | | 6 | lucy | e10adc3949ba59abbe56e057f20f883e | girl | 12345678922 | usa | lucy@pikachu.com | | 7 | lili | e10adc3949ba59abbe56e057f20f883e | girl | 18656565545 | usa | lili@pikachu.com | +----+----------+----------------------------------+------+-------------+-----------------------+-------------------+ 7 rows in set (0.00 sec)
数据筛选
结合条件子句筛选部分数据记录
SELECT * FROM [库名.]表名 WHERE 条件子句；
1 2 3 4 5 6 7 8
mysql> select username,sex,phonenum,email from member where sex='girl'; +----------+------+-------------+------------------+ | username | sex | phonenum | email | +----------+------+-------------+------------------+ | lucy | girl | 12345678922 | lucy@pikachu.com | | lili | girl | 18656565545 | lili@pikachu.com | +----------+------+-------------+------------------+ 2 rows in set (0.00 sec)
比较运算符
比较结果为真，则返回1，为假则返回0
类型
用途
备注
=
等于
!=、<>
不等于
>、>=
大于、大于等于
数值
<、<=
小于、小于等于
数值
IN、NOT IN
在集合中、不在集合中
BETWEEN
在两值之间
数值
多个条件的组合
使用多个条件时，通过逻辑操作进行组合（AND优先级高于OR）
类型
用途
OR
逻辑或
AND
逻辑与
1 2 3 4 5 6 7 8 9 10
mysql> select username,sex,phonenum,email from member where sex='girl' or id<3; +----------+------+-------------+-------------------+ | username | sex | phonenum | email | +----------+------+-------------+-------------------+ | vince | boy | 18626545453 | vince@pikachu.com | | allen | boy | 13676767767 | allen@pikachu.com | | lucy | girl | 12345678922 | lucy@pikachu.com | | lili | girl | 18656565545 | lili@pikachu.com | +----------+------+-------------+-------------------+ 4 rows in set (0.00 sec)
模糊查询
使用LIKE语句实现相似性检查
通配符_代表单个不确定字符，%代表0~N个字符
1 2 3 4 5 6 7 8 9
mysql> select username,sex,address from member where address like 'nba%'; +----------+-----+-----------+ | username | sex | address | +----------+-----+-----------+ | allen | boy | nba 76 | | kobe | boy | nba lakes | | grady | boy | nba hs | +----------+-----+-----------+ 3 rows in set (0.00 sec)
