表的查询
操作数据库内部的表记录 :
增 ; insert into 表名 (列1) values (值1,值2) ,。。。;
删 : delect from 表名 + where 条件
delect from 表名 and / or
truncate 表名
改: update 表名 set name = '' ,age = '' where + 条件
查: select * from 表名
select 列名1,列名2 .。。from 表名;
select * from 表名 + where + 条件
between ...and ... 在。。 与。。之间
select * from 表名 where id between xx and xx;
select distinct name from 表名 去重
null 与 not null 区别 :
null
create table t8(
id int auto_increment primary key,
name varchar(32),
email varchar(32)
)charset=utf8;
-- insert into t8(email) values ('xxx')
-- select * from t8;
----select * from t8 where name is null;
+----+------+-------+
| id | name | email |
+----+------+-------+
| 1 | NULL | xxxx |
+----+------+-------+
not null :
create table t9(
id int auto_increment primary key,
name varchar(32) not null default '',
email varchar(32) not null default ''
)charset = utf8;
insert into t9(email) values ('xxxx');
select * from t9;
----select * from t9 where name = '';
+----+------+-------+
| id | name | email |
+----+------+-------+
| 1 | | xxxx |
+----+------+-------+
is null 与 null : ?????
'''
单表操作 :
--分组 :
--- group by
- 分组 : 将所有数据 按照奥格相同字段进行归类
用法 : select 聚合函数 ,选取字段 from 表名 group by 分组 的条件;
--group by + 聚合函数 (min() , max() , sum() , count() ,avg())
-1.select count(id) ,gender from 表名 group by gender ;
--select count(id),gender as f from 表名 group by gender;
+-----------+--------+
| count(id) | gender |
+-----------+--------+
| 10 | male |
| 8 | female | # count (n) : 计数 n 出现的次数
+-----------+--------+
-2. select depart_id,max(age) from 表名 group by depart_id;
---max(m) : m字段名内数值最大的值
-3. --min() : m字段名内数值最小的值
-4. --sum() : m字段名内数值之和
-5. --count 与 sum 的区别 : count 计数个数 , sum 数值的和
-6. --avg() : m字段名内数值之平均值
--- having : + 条件
- 表示对group by 之后的数据 , 进行再一次的二次筛选。
- select depart_id,avg(age) from 表名 group by depart_id having avg(age) > 35;
where 条件语句和 group by 分组语句 的先后顺序;
--where > group by > having + 条件
--- order by 升序降序
--order by + (asc)默认 升序
--order by + desc
***对多个字段列进行升降序 :eg: age asc, id desc;
----表示 先将age 降序,如果有age相同 ,则对id 降序
-- select * from 表名 oreder by age desc, id desc;
---limit 分页
-- : limit offset ,size
offset : 列字段数据的索引 (从o 开始)
size : 取多少条数据
select * from 表名 limit 0 , 10; 第一页
select * from 表名 limit 10 , 10; 第二页
总结 :
- ,使用的顺序 :
select * from 表名 where 条件 group by 条件 having 条件 order by 条件 limit 条件;
-》 where > group by > having > order by > limit
多表联查:
-select * from department ;
-left join ..on
-select * from userinfo left join department on depart_id = department.id
-right join ... on
- select userinfo.name as uname, department.name as dname from userinfo right join department on depart_id = department.id;
-inner join .on
-select * from department inner join userinfo on department.id=userinfo.depart_id;