1、选择特定的字段
select id,name,password from user; //查询特定字段,id,name,password顺序可以随意
select*from user; //查询所有字段
2、字段别名:用空格或as
select id '学号',name '姓名',password '密码' from user;
select id as '学号',name as '姓名',password as '密码' from user;
3、distinct关键字,去除重复的值(多字段时需要所有字段同时相同才会认为是重复)
select distinct age from user;
4、使用where条件查询
5、查询空值null
select*from user where name is null;
select*from user where name is not null;
6、between and用法
select*from user where id between 3 and 5;
7、in 的用法
select*from where id in(1,3,5);
8、like关键字用法,模糊查询,text类型不能用
select*from user where name like '%s%';
select*from user where name like '%s%'or name like '%b%';
//查询name里面包含字母s的数据,%表示任意多个字符
也可以用正则表达式:(不建议用)
select*from user where name regexp '.*s.*';
select*from user where name regexp '(.*s.*)|(.*5.*)';//包含s或5
9、使用order by对查询结果排序
select*from user order by 字段名 desc; //desc表示降序,默认升序(asc)
10、使用limit限定输出个数(分页实现)
select*from user order by id limit 0,2;
select*from user order by id limit 5; //相当于0,5,从0开始取5个