检索SELECT
检索一列:
SELECT name FROM user;
检索name这一列的所有值
检索多列:
SELECT name,age FROM user;
检索name、age多列全部值
检索所有列:
SELECT * FROM user;
检索所有列的所有值
用distinct检索不同列:
SELECT distinct name FROM user;
检索表中所有不同的名字
限制检索结果的数目:
SELECT name FROM user limit 5;
检索前5个name
排序检索ORDER BY
对检索语句排序:
SELECT age FROM user ORDER BY age;
默认升序,语句后添加desc
可以指定为降序
过滤数据WHERE
过滤数据:
SELECT name FROM user
WHERE age > 18;
WHERE的关键字可以用= < > >= <= != BETWEEN x AND x
SELECT name FROM user
WHERE age BETWEEN 10 AND 18;
组合WHERE:
SELECT name FROM user
WHERE age > 18 AND score > 60;
多个条件可以用AND或OR关键字连接
IN:
SELECT name FROM user
WHERE age IN (18,20);
指定范围
NOT:
SELECT name FROM user
WHERE age NOT IN (18,20);
NOT用于否定之后的条件
通配符过滤LIKE % _
使用LIKE加通配符模糊查询
%匹配多个字符模糊查询:
SELECT name FROM user
WHERE name LIKE 'a%';
将会得到类似abc、ab这种结果
_匹配单个字符模糊查询:
SELECT name FROM user
WHERE name LIKE 'a_';
只会返回ab、ac结果
聚集函数
用于数据汇集的函数:
AVG()得到平均值
SELECT AVG(age) as avg_age FROM user;
COUNT()得到记录条数
SELECT COUNT(*) FROM user;
MAX()得到列中最大值
SELECT MAX(age) FROM user;
MIN()得到列中最小值
SELECT COUNT(age) FROM user;
SUM()得到整列数据和
SELECT COUNT(score) FROM user;
创建/过滤分组
GROUP BY创建分组
SELECT age FROM user
GROUP BY score;
GROUP BY的内容可以不查询
HAVING过滤分组
SELECT age FROM user
HAVING age > 10;
HAVING的条件内容需要查询出来再用于过滤
组合查询UNION
UNION可以将两条SQL语句结合执行
SELECT age FROM user WHERE age > 17
UNION SELECT score FROM user WHERE score > 20
插入INSERT
INSERT INTO user(id, name, pwd) VALUES('1', 'yi', '123');
更新UPDATE
UPDATE user SET name = 'er' WHERE id = '1';
删除DELETE
DELETE FROM user WHERE id = '1';