Mysql：数据表的数据操作

【声明：】本文是作者（蘑菇v5）原创，版权归作者 蘑菇v5所有，侵权必究。本文首发在简书。如若转发，请注明作者和来源地址！未经授权，严禁私自转载！

插入

insert into t_user(username,password,nickname) value ('zhangsan','123','张三');
//必须写出所有字段
insert into t_user values (null,'lisi','456','李四');
//通过insert into xxx select xx 插入已有表中的数据
insert into t_user (username,nickname) select no,name from t_student;

更新

update t_user set nickname='王五',username='wangwu' where id=1；

删除

delete from t_user where id=2;
//清空表中的全部信息，将自动递增的标识清空
truncate table t_student;

简单查询

select id,username from t_user;
select * from t_user;
select * from t_user where id >2;
select * from t_user where id >2 and nickname='李四';

常用查询

• 1、like表示模糊查询

//查询张xx的数据
select * from t_stu where name like '张%';
//查询姓张，并且是女性
select * from t_stu where name like '张%' and sex='女';

• 2、in表示某个值在某个数组中

//查询一班和二班的学生
select * from t_stu where cla_id in (1,2);

• 3、between可以查询某个范围内的数据

//查询出生在某个日期范围内的
select * from t_stu where born between '1980-01-01' and '1981-12-31';

• 4、取日期的年份（根据年份求年龄YEAR(now())-YEAR(date)）as：投影别名

//某个日期的年份
YEAR(xx)
select YEAR(born) from t_stu;
//当前年份
YEAR(now())
select YEAR(now());
//检索学生表中姓李的名字和年龄 
select name as '姓名',(YEAR(now())-YEAR(born))  as '年龄' from t_stu 
where name like '李%';
//检索学生表中姓夏的男性年龄小于30的所有学生
select * from t_stu where (YEAR(now())-YEAR(born))<30 
and sex='男' and name like '夏%';

• 5、最大、最小

  最大：max(xx)
  最小：min(xx)

//年龄最小的出生日期
select max(born)  from  t_stu;
//年龄最大的出生日期
select min(born)  from  t_stu;
//年龄最小的学生的所有信息（二次查询（子查询））
select * from t_stu where born=(select max(born)  from  t_stu);
//年龄最大的学生的所有信息（二次查询）
select * from t_stu where born=(select min(born)  from  t_stu);
//三班年龄最小的男生的所有信息（二次查询 （子查询）中条件）
select * from t_stu where born=(select max(born)  from  t_stu 
where cla_id=3 and sex="男");

• 6、空值 is null、不为空is not null

• 7、统计数目 count(*)

//获取二班的人数
select count (*) from t_stu where cla_id =2;

• 8、排序 order by asc（升序） desc（降序）

//获取二班学生的所有信息按出生日期升序（从小到大）排列 默认asc
select * from t_stu where cla_id =2 order by born asc;
//获取二班学生的所有信息按姓名降序（从大到小）（ASCii）排列
select * from t_stu where cla_id =2 order by name desc;

• 9、分组查询 group by（统计不同组别的信息）

//获取班级id
select cla_id from t_stu group by cla_id;
//按照班级分组，统计每个班级学生的数目
select cla_id,count(id) from t_stu group by cla_id;
//按照班级分组，统计学生的数目，班级不为空
select cla_id,count(id) from t_stu  where cla_id is not null group by cla_id;
//按照班级分组，统计学生的数目、年龄最大和最小的信息，班级不为空
select cla_id,count(id),max(born),min(born) from t_stu 
 where cla_id is not null group by cla_id;

---

//查询学生表中的男女人数
select  sex,count(id) from t_stu group by sex;
//查询学生表中每个班级的男女人数，班级不为空
select  cla_id,sex,count(id) from t_stu  where cla_id is not null 
group by cla_id,sex;
//查询学生表中一、二、三班的男女人数
select  cla_id,sex,count(id) from t_stu  where cla_id in(1,2,3)
 group by cla_id,sex;
//查询学生表中不同出生日期的人数
select  YEAR(born) as 'y',count(id) from t_stu  group by y;
//查询学生表中三班不同出生日期的人数
select  YEAR(born) as 'y',count(id) from t_stu  where cla_id =3  group by y;
//查询学生表中一、二、三班不同出生日期的人数
select  cla_id,YEAR(born) as 'y',count(id) from t_stu 
 where cla_id in(1,2,3)  group by cla_id, y;
//查询学生表中不同年龄的人数
select  (YEAR(now())-YEAR(born)) as 'age',count(id) from t_stu  
group by age;

• 10、分组查询 条件 group by ... having ...（统计不同组别的信息 条件...）

//按照班级分组，统计学生的数目大于50的、年龄最大和最小的信息，班级不为空
select cla_id,count(id) as pn,max(born) ,min(born) from t_stu  
where cla_id is not null group by cla_id having pn>50;

注意：分组时，比较条件是投影别名，限制要放在having中，不能放到where，因为pn不在表里面，而在查询出来的投影中，特别在group by中

• 11、跨表查询 （连接查询）

两种方式：

1、相对较早的方式：

select t1.name,t2.name from t_cla t1,t_stu t2 
where t1.id=t2.cla_id and t1.id in (1,2,3);

2、常用方式：join .. on.. where..

select t1.name,t2.name from t_cla t1 join t_stu t2 
on (t1.id=t2.cla_id) where  t1.id in (1,2,3);

举例如下：

---

//查询计科系所有的学生名称
select t4.name,t1.name from t_stu t1 join t_cla t2 
on(t1.cla_id=t2.id) join t_spe t3 on(t2.spe_id=t3.id) 
join t_dep t4 on (t3.dep_id=t4.id) where t4.name="计科系";
//查询每个院系的学生人数
select t4.name,count(*) from t_stu t1 join t_cla t2 
on(t1.cla_id=t2.id) join t_spe t3 on(t2.spe_id=t3.id) 
join t_dep t4 on (t3.dep_id=t4.id) group by t4.id;
//查询每个院系的男女人数（按照院系排序）
select t4.name,t1.sex,count(*) from t_stu t1 join t_cla t2 
on(t1.cla_id=t2.id) join t_spe t3 on(t2.spe_id=t3.id) join t_dep t4 
on (t3.dep_id=t4.id) group by t1.sex,t4.id order by t4.id;
//查询每个专业的学生人数（此种方法有弊端：专业下面没有学生的情况，不显示）
select t3.name,count(*) from t_stu t1 join t_cla t2 
on(t1.cla_id=t2.id) join t_spe t3 on(t2.spe_id=t3.id) group by t3.id ;

连接分类：

内连接（join）、左连接（left）、右连接（right）
左连接：
是将左边的表设置为主表，来连接右边的表，如果左边表中的数据在右边表中没有出现，则会自动使用null替代
右连接：
是将右边的表设置为主表，来连接左边的表，如果右边表中的数据在左边表中没有出现，则会自动使用null替代

//查询每个专业的学生人数（左连接）
select t1.name,count(t2.id) from t_cla t1 left join t_stu t2
on(t2.cla_id=t1.id) group by t1.id;
//查询每个专业的学生人数（右连接）
select t2.name,count(t1.id) from t_stu t1 right join t_cla t2 
on(t2.id=t1.cla_id) group by t2.id;

• 12、分页 limit

//获取分页的数据sql，从第0位数开始，显示15条数据 start=0 pageSize=15
select * from t_user limit 0,15;

分页：

1、页码(第几页)pageIndex
2、每页显示多少条？pageSize
3、总共多少条记录totalRecord
4、总共多少页totalPage
5、放置具体数据的列表List<E>datas

0,10
11,10
...
101,10

//开始位置
int start=(pageIndex-1)*pageSize;
//总记录数 totalRecord
select count(*) from t_user where username like '%"+con+"%' 
or nickname like '%"+con+"%';
//总页数 totalPage
int totalPage=(totalRecord-1)/pageSize+1;