SELECT * FROM `user`; 查询所有
SELECT id,user_name FROM `user`;查询id列和user_name类;
三种方式取别名
SELECT id,user_name name FROM `user`;
SELECT id,user_name as name FROM `user`;
SELECT id,user_name "name" FROM `user`;
DESC user;查询表结构
SELECT 3*2 from DUAL; DUAL(伪表)
日期型的数据可以做加减但不可以做乘除;
包含空值的数学表达式,结果还是空;
SELECT DISTINCT type from user; DISTINCT 是去重的
SELECT id,user_name from user WHERE user_name='15919448377'; 根据user_name查询数据
SELECT number ,id ,price FROM`product`where price BETWEEN 1000 AND 2000; 查询价格在1000到2000之间的数据(包含边界)
SELECT * FROM product WHERE number ='123456' OR number='J116AM439123' OR number='J116AS203203' or的用法,符合多种中的一个就查询
SELECT * FROM product WHERE number IN ('123456','J116AM439123','J116AS203203'); in的用法,也是符合条件的就查询
SELECT * FROM product WHERE number LIKE '%J116AM%'; like的用法 ,模糊查询,%号表示J116M的前面和后面可能有一个或多个字符
SELECT * FROM product WHERE number LIKE '_1%'; 下划线表示占一个字符的位置,这个查询的是number第二位是1的数据
如果就是查询number中带下划线的数据应该转义,写法如下:SELECT * FROM product WHERE number LIKE '%/_%' ESCAPE '/'; 或者SELECT * FROM product WHERE number LIKE '%#_%' ESCAPE '#';
SELECT * FROM product WHERE offshelvestime IS NOT NULL; 查询非空的值
SELECT * FROM product WHERE offshelvestime IS NULL; 查询空值
SELECT * FROM product ORDER BY price ASC; 升序查询
SELECT * FROM product ORDER BY price DESC; 倒序查询
SELECT * FROM product ORDER BY price DESC ,id ASC;先按price倒序排,如果price相同的数据,按id 升序排
WHERE 语句必须竟跟FROM语句