SQL相关的知识点重新整理一下

SQL 查询列之间用逗号隔开 这里包括ORDER BY WHERE等条件语句
SELECT * FROM admin WHERE 1 LIMIT 2,3 查询 从2开始数3个

SELECT DISTINCT order_num FROM Product WHERE quantity>=100 #DISTINCT 去重复的

SELECT * FROM admin WHERE 1 ORDER BY id 按字段id升序 排列

SELECT * FROM admin WHERE 1 ORDER BY id DESC 按字段id降序 排列

SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC,prod_name;

SELECT prod_name,prod_price FROM Products WHERE prod_price=3.01 查找俩个列 但是只返回prod_price=3.01的行

SELECT name FROM Product WHERE id=1 ORDER BY id DESC /*注意where和order by的顺序 */

= 等于 <>不等于 !=不等于 <小雨 <=小于等于 !<不小于 >大于 >= 大于等于 !>不大于 BETWEEN IS NULL

SELECT vend_id FROM Products WHERE vend_id<>‘DLL01’ 不是供应商DLL01

SELECT aid FROM liqi_addonarticle WHERE aid BETWEEN 20 AND 30 WHERE条件语句 查询aid 20-30之间的数字

SELECT aid FROM Product WHERE name IS NULL 判断空值的

SELECT aid FROM Product WHERE name=‘1ke’ AND price>=3 #可以多条件查询 用AND OR

SELECT aid FROM Product WHERE name=‘1ke’ OR price>=3

SELECT aid FROM Product WHERE (name=‘dd’ OR name=‘cc’) AND price>=10 #运算符优先级

SELECT aid FROM Product WHERE id IN (‘dll’,‘cll’) ORDER BY name #IN符号 可以包含其他 SELECT 语句

SELECT aid FROM Product WHERE NOT id=‘dll’ ORDER BY name #where not除了之外 NOT 关键字很少单独自己用

SELECT aid FROM Product WHERE aid IN (‘BR1’,‘BR2’,‘BR3’) AND quantity>=100

SELECT aid FROM Product WHERE aid LIKE ‘FISH%’#百分号标识任何字符出现的任意次数 %为通配符

SELECT aid FROM Product WHERE aid LIKE ‘_inc’ # _ 下标只能代替一个字符 和% 任意字符在数量有差距

/**
WHERE 下的LIKE 记得单引号 里边的通配符 % _ 一个是代表任意数一个是只代表单个字符

WHERE 下的IN 记得加括号
**/

SELECT aid FROM Product WHERE aid LIKE ‘%F%’ AND PRICE>3
//
SELECT DISTINCT CONCAT(PAGE_TYPE,’(’,PAGE_NUMBER,’)’) FROM INNODB_BUFFER_PAGE_LRU
注意：去重不能在ORDER BY排序

SELECT Concat(name,’(’,country,’)’) FROM Vendors ORDER BY name用于表中不存在的字符 mysql用逗号