# 选择
SELECT cpu FROM laptop
# 去重
SELECT DISTINCT cpu FROM laptop
# 最多取几条
SELECT id FROM laptop
LIMIT 5
# 从第22行开始取五条
SELECT id FROM laptop
LIMIT 21, 5
# 排序并且降序,升序去掉desc
SELECT `memory` FROM laptop
ORDER BY `memory` DESC
# 下面都是where的操作符
SELECT * FROM laptop
WHERE laptop.disk='512GB'
SELECT * FROM laptop
WHERE laptop.disk<>'512GB'
SELECT * FROM laptop
WHERE laptop.disk!='512GB'
SELECT * FROM laptop
WHERE laptop.memory>8
SELECT * FROM laptop
WHERE laptop.memory>=8
SELECT * FROM laptop
WHERE laptop.memory<16
SELECT * FROM laptop
WHERE laptop.memory<8
# 两值之间,还有and or not等等
SELECT * FROM laptop
WHERE laptop.memory BETWEEN 8 AND 16
# 通配符%
SELECT * FROM laptop
WHERE laptop.cpu LIKE '%i%'
# 通配符_
SELECT * FROM laptop
WHERE laptop.cpu LIKE '_ayzen'
# 正则表达式
# 列数据中包含某个字符串
SELECT cpu FROM laptop
WHERE cpu REGEXP 'i'
# 在上面的基础上包含多个字符串
SELECT laptop.disk FROM laptop
WHERE laptop.disk REGEXP '512|1T'
# 中括号括起来
SELECT laptop.disk FROM laptop
WHERE laptop.disk REGEXP '[12]T'
# 匹配中括号之外
SELECT cpu FROM laptop
WHERE cpu REGEXP '[^i57]'
# 指定范围
SELECT id FROM laptop
WHERE id REGEXP '[0-9]'
# 拼接和别名
SELECT CONCAT(id,'(',cpu,')') AS cpulist FROM laptop
# 文本处理函数
# left返回数据从左边开始几个字符,超了也不报错
SELECT LEFT(cpu, 1) FROM laptop
SELECT RIGHT(cpu, 10) FROM laptop
# 长度返回内容的字符个数
SELECT LENGTH(id) FROM laptop
# 返回子串的起始位置,找不到就是0
SELECT LOCATE("a", cpu) FROM laptop
# 从第几个位置开始,包括这一位(这里是2)找子串
SELECT LOCATE(1, id, 2) FROM laptop
# 从第二位开始返回后面的字符(包括这一位)
SELECT SUBSTRING(`disk`, 2) FROM laptop
SELECT SUBSTRING(`disk` FROM 2) FROM laptop
# 从第二位开始返回2个字符
SELECT SUBSTRING(`disk`, 2, 2) FROM laptop
SELECT SUBSTRING(`disk` FROM 2 FOR 2) FROM laptop
# 找发音相似的
SELECT cpu FROM laptop
WHERE SOUNDEX(cpu)=SOUNDEX("raisen")
# lower upper大小写,不试了,trim rtrim ltrim 去空格,不试了
# 分组和聚集
SELECT cpu, COUNT(id) FROM laptop
GROUP BY cpu
HAVING cpu <> "rayzen"
# 子查询
SELECT cpu FROM laptop
WHERE `memory`=16 AND graphics NOT IN (SELECT graphics FROM laptop WHERE graphics="uhd")
# 连结
SELECT l1.cpu
FROM laptop AS l1, laptop AS l2
WHERE l1.id=l2.id AND l1.memory=16 AND l1.graphics="vega"
# 组合
SELECT id FROM laptop
WHERE id<11
UNION
SELECT id FROM laptop
WHERE id>20
MySQL select语句 汇总
最新推荐文章于 2021-07-27 16:43:45 发布