1.查询指定字段
select c_id,c_age,c_name from t_student;
select c_id as 编号,c_name as 姓名,c_age 性别 from t_student;
2.去重查询
select distinct c_address from t_student
select distinct c_address,c_name from t_student
3.比较运算符查询
select c_name,c_gender from t_student where c_gender ="女 ";
select c_id ,c_name,c_age,c_gender from t_student where c_age <>18;
4.逻辑运算符查询
select c_name,c_age,c_gender from t_student where c_gender = "女" and c_age <20;
select c_name,c_age from t_student where c_age =18 or 20; # 有问题.会产生注入问题
select c_name ,c_age from t_student where not c_age = 18; # not一般用在非空的地方
5.模糊查询
like
%: 表示任意多个字符
_: 表示任意一个字符
select c_id,c_name from t_student where c_name like "孙";
select c_id,c_name from t_student where c_name like "孙_";
select c_id,c_name from t_student where c_name like "%三%";
6.范围查询
in:非连续 / between:连续
select c_id,c_name from t_student where c_id in (1,5,8,23,7,12,100000);
select c_name,c_age from t_student where c_age between 18 and 20; # 包括18和20
7.判空
select c_name,c_age from t_student where c_age is Null;
select c_name,c_age from t_student where c_age is not Null;
8.查询结果排序 order by
select c_name ,c_id,c_age from t_student order by c_age; # 默认升序
select c_name ,c_id,c_age from t_student where c_gender = "女" order by c_age desc; # 降序
9.分页查询
select c_id ,c_name from t_student limit 10,10; # 从11开始 20结束
select c_id ,c_name from t_student limit 30,10; # 从31开始 40结束
-- 每页显示2个,第3个页面
select * from students limit 4,2;
-- 每页显示m个,第n个页面
select * from students limit m(n-1),m;
10.聚合函数 # 很少单独用,一般结合分组一起用
select sum(c_age) from t_student;
select sum(c_age),avg(c_age),max(c_age),min(c_age),count(*) from t_stuudent where c_gender = "女"
11.分组:
1. where....group by....having: 先经过where大筛选,再.group by分组,再对分组的结果用having限制查询
2.单分组没有意义,相当于去重,往往结合:group_concat(c_name)使用
3.having中like查询不再适用,只能配合group by 使用
单字段分组:
select c_gender from t_student group by c_gender;
多字段分组: having 对group分组后进行筛选
select c_address,c_gender from t_student group by c_gender,c_address;
select c_address,c_gender,group_concat(c_name) from t_student group by c_gender,c_address having c_gender = "女"; <====>select c_address,c_gender,group_concat(c_name) from t_student where c_gender ="女"group by c_gender,c_address;
例1:mysql> select c_address,c_name from t_student group by c_address,c_name having c_gender = "女";
ERROR 1054 (42S22): Unknown column 'c_gender' in 'having clause'
注意1:having用在group by分组后,但是分组后并没有 c_gender这一字段
12.多表查询
没外键的时候:不会连接c_class_id为空的数据
select s.c_name,c.c_name from t_student as s,t_class as c where s.c_class_id = c.c_id;
1.内连接:on
"""注意:数据库默认为内连接,也可以不写inner join 用,隔开2个表但是连接方式需要用where
但是不能再接where了