mysql中sql语句去重_MySQL常用sql语句-----数据表的查询操作

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了