mysql> select name,country -> from websites; +--------------+---------+ | name | country | +--------------+---------+ | Google | USA | | 淘宝 | CN | | 菜鸟教程 | CN | | 微博 | CN | | Facebook | USA | +--------------+---------+ 5 rows in set (0.00 sec)
SELECT DISTINCT
DISTINCT 关键词用于返回唯一不同的值。
select distinct country from websites; +---------+ | country | +---------+ | USA | | CN | +---------+ 2 rows in set (0.00 sec)
WHERE
SELECT column1, column2, ... FROM table_name WHERE condition;
mysql> select * from websites where country='CN'; +----+--------------+-------------------------+-------+---------+ | id | name | url | alexa | country | +----+--------------+-------------------------+-------+---------+ | 2 | 淘宝 | https://www.taobao.com/ | 13 | CN | | 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN | | 4 | 微博 | http://weibo.com/ | 20 | CN | +----+--------------+-------------------------+-------+---------+ 3 rows in set (0.00 sec)
sql使用单引号环绕文本值
数字字段不需要
WHERE 子句中的运算符
下面的运算符可以在 WHERE 子句中使用：
运算符
描述
=
等于
<>
不等于。注释：在 SQL 的一些版本中，该操作符可被写成 !=
>
大于
<
小于
>=
大于等于
<=
小于等于
BETWEEN
在某个范围内
LIKE
搜索某种模式
IN
指定针对某个列的多个可能值
AND&OR运算符
AND & OR 运算符用于基于一个以上的条件对记录进行过滤。
mysql> SELECT * FROM Websites -> WHERE country='USA' -> OR country='CN'; +----+--------------+---------------------------+-------+---------+ | id | name | url | alexa | country | +----+--------------+---------------------------+-------+---------+ | 1 | Google | https://www.google.cm/ | 1 | USA | | 2 | 淘宝 | https://www.taobao.com/ | 13 | CN | | 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN | | 4 | 微博 | http://weibo.com/ | 20 | CN | | 5 | Facebook | https://www.facebook.com/ | 3 | USA | +----+--------------+---------------------------+-------+---------+ 5 rows in set (0.00 sec)
也可以使用括号来设置优先级
ORDER BY
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
**column1, column2, …**：要排序的字段名称，可以为多个字段。
ASC：表示按升序排序。
DESC：表示按降序排序。
mysql> select * from websites -> where country='CN' -> order by alexa; +----+--------------+-------------------------+-------+---------+ | id | name | url | alexa | country | +----+--------------+-------------------------+-------+---------+ | 2 | 淘宝 | https://www.taobao.com/ | 13 | CN | | 4 | 微博 | http://weibo.com/ | 20 | CN | | 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN | +----+--------------+-------------------------+-------+---------+ 3 rows in set (0.00 sec)
INSERT INTO
INSERT INTO 语句用于向表中插入新记录。
INSERT INTO table_name VALUES (value1,value2,value3,...);
INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);
table_name：需要插入新记录的表名。
**column1, column2, …**：需要插入的字段名。
**value1, value2, …**：需要插入的字段值。
mysql> insert into websites (name,url,alexa,country) -> values('百度','https://www.baidu.com','4','CN'); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 15 Current database: runoob
