SELECT查询语句

SELECT * FROM tbl_name;

SELECT tbl_name.* FROM tbl_name;

查询指定字段

SELECT test1,test2 FROM tbl_name;

可以通过db_name.tbl_name查询哪个数据库的哪张表

例SELECT * FROM test.user;  SELECT 表名.字段名 FROM 数据库名.表名;

SELECT test1.id,test2.username FROM project.user;

给表起别名

SELECT 字段名 FROM tbl_name AS a;  现在a是别名了，查询某表的字段时可以用a代替了

SELECT a.字段名1,a.字段名2 FROM tbl_name AS a;

给字段起别名

SELECT id AS '编号',username AS '用户名',age AS '年龄 FROM user；

带WHERE 条件查询

运算符=,<,>,<>,<=>等等，<=>这符号为null而设

SELECT * FROM tbl_name WHERE test1=1;

SELECT * FROM tbl_name WHERE test1=null;   这写错错误，要想查询条件是test1=null的内容时，要这样写

SELECT * FROM tbl_name WHERE test1<=>null; 或者SELECT * FROM tbl_name WHERE test1 is null;

BETWEEN AND查询 几到几之间的记录

SELECT * FROM tbl_name WHERE id BETWEEN 5 AND 10; 查询id5至10的记录;

IN 指定集合

SELECT * FROM tbl_name WHERE id IN(1,3,5,,7,100);  查询id 为1,3,5,7,100的记录

SELECT * FROM tbl_name WHERE username IN('张三','李四','丁五'); 查询username为张三，李四，丁五的记录

模糊查询 LIKE 

%表示0个或1个或多个任意字符

_表示1个任意字符

SELECT * FROM tbl_name WHERE username LIKE '张%';  查询姓张的用户；

SELECT * FROM tbl_name WHERE username LIKE '%in%';  查询包含in的用户;

SELECT * FROM tbl_name WHERE username LIKE '___';  查询用户名为3位数的用户;

关键字AND 代表并且

SELECT * FROM tbl_name WHERE username='niuB' AND age=18;

SELECT * FROM tbl_name WHERE username='niuB' AND age=18 AND address='北京;

关键字GROUP BY 分组查询  注：用分组查询只会显示该组的第一条记录

SELECT * FROM tbl_name GROUP BY sex; 以性别为分组查询

 

转载于:https://blog.51cto.com/12034701/1856243