mysql的常用操作笔记(关联多个表的检索)

之前虽然会点sql语句,大多数懵懵懂懂能看懂却又自己上不了手的,最近在工作中经常要批量添加修改用户,也算稍微有了一些心得,特此记录下来作为自己的笔记.

在数据库中插入数据

insert into 表名 [(字段1,字段2 )] values (字段1,字段2 ..), (字段1,字段2 ..), (字段1,字段2 ..)

从数据库中获取数据

select * from 表名 [可选where userid =3333];

重要的要来了,自己写这个文章就是要总结这里的知识点

从多个表中获取数据,也叫关联表

1,简单的两个表的关联

select orders.orderid, orders.amount,orders.date from customers,orderswhere customers.name= laowai and customers.customerid=orders.customerid;

这样就检索除了两个表中id一样,且customers.name为laowai的用户的order表的数据了,其实原理很简单,这里的from后面两个表中间的逗号就相当于inner into了,也叫完全关联或表的笛卡尔乘积,上面的条件等价于:

select orders.orderid, orders.amount,orders.date from customers inner join orders where customers.name= laowai and customers.customerid=orders.customerid;

其实上面也可以alias别名的,customers as a或是customers a就可以临时命名为a

同理,如果三个表关联的话也可以按照上面的方式进行写条件,到最后条件where注意写全就好了a.id=b.id and b.userid=c.userid and c.classid=d.classid这样最后就能得到自己想要的数据了.

2,左关联查找不匹配的数据

有时候我们要找没有班级的用户或是没有考试的同学,这里就用上了左关联了,格式是把inner join换成left join

注意在这里left join左匹配可以用using(userid)来替代where中的a.userid=b.userid,但是using(userid)是写在where前面的

注意left join会关联出来所有的用户,而我们现在要查找没有值的用户,所以要加一个b表的值是空的选项

select customers.customerid, customers.name from customers left join orders using(customerid) where orders.orderid is null;

这样就显示出来了匹配用户中没有订单的用户了

这里说一个以特定顺序进行排序的方法

select * from z_user order by username DESC;

上面是将user表以username字段进行倒序排序

这里是分组给id取平均值且大于50的值

select customerid ,avg(amount) from orders group by customerid having avg(amount) >50;

注意avg()平均值才可以使用group by子句(合计函数都可以count,min,max等函数)

limit限制返回条数

select name from user limit 2,3;//从第三条开始要3条数据

使用子查询,这个也比较重要

select customerid,amount from orders where

amount= (select max(amount) from orders);

//动态返回最大的数值

关联子查询

select isbn,title from books

where not exists (select * from order_items where order_items.isbn=books.isbn);

//查找所有不存在order的书

行子查询

目前介绍的所有子查询都返回单一的值,这里的查询会返回一行的

select c1,c2,c3 from t1 where (c1,c2,c3) in (select c1,c2,c3 from t2);

返回t1的三个数据(t2也有的)

使用子查询作为临时表

select * from (select customerid,name from customers where city= luoyang ) as aaa;//这个临时表名叫aaa

更新数据库记录

update 表名 set price =15元 where name= 打火机 ;

删除表中的数据

delete from 表名 where id=3;

下面是之前工作自己摸索的,做个记录,不一定对

//下面sql是查找在机构18中班级删除了,但是班级学生没有隐藏的,这样就不能解绑老师了
select ClassName,UserID,SubjectID,OrganID,IfDelete,IfHidden,zj_class_list.ClassID from zj_class_list,zj_class_user where zj_class_list.ClassID=zj_class_user.ClassID and OrganID=18 and IfDelete=1
//下面改善了
select IfDelete,IfHidden from zj_class_list,zj_class_user where zj_class_list.ClassID=zj_class_user.ClassID and OrganID=18 and IfDelete=1 and IfHidden=0
// 下面是操作办法(更新organid里18的数据,如果classlist的ifdelete=1删除了,那么classuser的ifhidden也隐藏设置为1)
update zj_class_user INNER JOIN (SELECT * from zj_class_list) a ON a.ClassID=zj_class_user.ClassID and a.OrganID=18 and a.IfDelete=1 and zj_class_user.IfHidden=0 SET zj_class_user.IfHidden=1

//第二部,找出删除班级了,但是organuser的status还是为1的用户

//查找删除班级了,但是organuser的status状态还是为1的用户
SELECT DISTINCT a.UserID,a.OrganID,a.`Status`
FROM `zj_organ_user` a INNER JOIN ( select OrganID,IfDelete,UserID from zj_class_list INNER JOIN zj_class_user ON zj_class_list.ClassID=zj_class_user.ClassID and OrganID=18 and IfDelete=1 )b
WHERE STATUS =1 and a.organID=b.organid AND a.UserID= b.UserID

// 然后更新字段值
update zj_organ_user a INNER JOIN (select OrganID,IfDelete,UserID from zj_class_list INNER JOIN zj_class_user ON zj_class_list.ClassID=zj_class_user.ClassID and OrganID=18 and IfDelete=1 )b
ON STATUS =1 and a.organID=b.organid AND a.UserID= b.UserID SET a.`Status`=0
之后就可以正常把教师和学生移出到培训机构了

ok,这里就已经完美解决问题了!

//在organ_user表有重复数据
SELECT a.*,b.OUID,b.status FROM zj_organ_user a,zj_organ_user b where a.UserID=b.UserID AND a.OrganID=b.OrganID and a.OUID!=b.OUID
查看
//去除重复
SELECT DISTINCT b.UserID,b.OrganID,b.`Status` FROM zj_organ_user a INNER JOIN zj_organ_user b ON a.UserID=b.UserID AND a.OrganID=b.OrganID and a.OUID!=b.OUID AND b.UserID=37363

//看了一个链接,成功执行了http://www.cnblogs.com/nzbbody/p/4470638.html
select a.ouid,a.organid,min(a.userid),a.status from zj_organ_user a,zj_organ_user b where a.userid=b.userid and a.ouid!=b.ouid and a.organid=b.organid and a.userid=b.userid group by a.userid order by a.userid DESC
//上面是查询,这里是删除操作
delete from zj_organ_user where userid=(select min(a.userid) from zj_organ_user a,zj_organ_user b where a.userid=b.userid and a.ouid!=b.ouid and a.organid=b.organid and a.userid=b.userid group by a.userid order by a.userid DESC)
//先别急,因为status可能还不一样,先把status一样的先删除了,在解决不一样的问题
select min(a.userid) from zj_organ_user a,zj_organ_user b where a.userid=b.userid and a.ouid!=b.ouid and a.organid=b.organid and a.userid=b.userid and a.status=b.status group by a.userid order by a.userid DESC
//上面是查找status一样的id,然后执行删除操作
-错误语句不要执行
delete from zj_organ_user where userid in (select minuserid from (select min(a.userid) minuserid from zj_organ_user a,zj_organ_user b where a.userid=b.userid and a.ouid!=b.ouid and a.organid=b.organid and a.userid=b.userid and a.status=b.status group by a.userid order by a.userid DESC) k)
错误语句上面

上面的先不管,最近在研究怎么批量添加用户,发现zj_user表有个ordernum学号主键难倒了,因为批量添加的时候他会自己增加,所以在批量添加就会有问题了,最近复习了mysql准备再试试,写了这样一个语句来更新自己添加的nigud帐号的ordernum

update zj_user set ordernum=(select ordernum from zj_user order by userid desc limit 1,1)+1 order by userid desc limit 1

这个语句逻辑上就是把当前userid最后的ordernum取出来然后给现在最后添加的nigud帐号的ordernum值加1,结果系统提示

You can t specify target table zj_user for update in FROM clause

这明显是报错了,百度了一下原来还是mysql不能直接调用的问题,然后自己修改了一下sql语句:

update zj_user set ordernum=(select a.* from (select ordernum from zj_user order by userid desc limit 1,1)a)+1 order by userid desc limit 1

这样就好了,原来mysql不能直接调用数据库select的数据,还需要再外面包装成一个虚拟表才可以的

转载请注明：稻香的博客 » mysql的常用操作笔记(关联多个表的检索)