mysql DEMO

1.导出数据库：mysqldump -u 用户名 -p 数据库名 > 导出的文件名 
如我输入的命令行:mysqldump -u root -p news > news.sql   (输入后会让你输入进入MySQL的密码)
（如果导出单张表的话在数据库名后面输入表名即可）

mysqldump -u root -p kouyu100 user_info >user_info.sql ,会看到文件news.sql自动生成到bin文件下  

2.创建一个数据库create database datebase_name

3.show databases;

4.show tables; 

5. user database    source d:\test.sql; 导入表        

7.select procedure status where Db='database';  // 查看存储过程

8. describe table_name;//查看表结构

//------------------------------------------------CASE语句----------------------------------------

case

    when        then

end    

语句：

select dm.name as schoolName,ui.realname,ui.alias,ore.user_id,ore.*

,

case

when so.orgType=1 then (select class_name from user_class where id = so.orgId) 

when so.orgType =3 then (select name from society_class where id = so.orgId)

end

as classname

 from student_org so  inner join order_remittance ore on ore.user_id=so.studentId left join user_info ui on ui.id=ore.user_id left join domain dm on dm.id=ui.domain  group by so.studentId

//------------------------------------------------------

插缝查询（查一张表中最先中断的Id）

select school_id+1 from user_school  t1 where 

not exists 

(select school_id from user_school t2 where t2.school_id=t1.school_id+1) 

and school_id   between 546475 and 600000 order by school_id limit 1;

//------------------------------------------------------

// 查上周的时间周日到周六

and cr.end_time>=subdate(curdate(),date_format(curdate(),'%w')+7) and cr.end_time<=subdate(curdate(),date_format(curdate(),'%w')-0) 

//查上周周一到周五的数据

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

Calendar c = Calendar.getInstance();

int day_of_week =  c.get(Calendar.DAY_OF_WEEK);

if (day_of_week ==1){

 c.add(Calendar.DAY_OF_WEEK,-7);

}else{

c.set(Calendar.DAY_OF_WEEK,-6);

}

String etime = sdf.format(c.getTime());

c.add(Calendar.DATE,-6);

String qtime = sdf.format(c.getTime());

//----------------------------------------------------------------

可以直接加

select  (uf2.fcount + ur2.rcount) as toc, fcount as fct ,rcount as rct , uf2.user_id as uid ,ur2.sender_name as umane  from 

       ( select count(us.friend_id) as fcount , us.user_id from  user_friends us group by user_id  ) as uf2

       

         inner join 

       ( select count(um.receiver_id) as rcount , um.sender_id ,um.sender_name  from user_message um group by um.sender_id ) as ur2

       on uf2.user_id = ur2.sender_id 

       order by toc desc limit 0,12

//------------------------------------------------------------------

mysql 效率分析关键字

explain

SELECT (select count(id) from student_org sso where sso.orgtype=mo.orgtype and sso.orgid=mo.orgid)  as pc,count(distinct cr.user_id) as c2,so.orgid as c3,so.orgType as c4,count(cr.id) as c5 ,case when so.orgType=1 then (select class_name from user_class as uc where uc.id=so.orgId) when so.orgType=3 then (SELECT name FROM society_class sc where sc.id=so.orgId) end as name, case when so.orgType=1 then (select trial from user_class as uc1 where uc1.id = so.orgId) when so.orgType=3 then (select trial from society_class sc1 where sc1.id=so.orgId) end as trial,(select GROUP_CONCAT(ui.realname) from manager_org mmo inner join user_info ui on mmo.managerId= ui.id and ui.status!=6 where mmo.orgId=mo.orgId and mmo.orgType=mo.orgType) as realname,(select COUNT(hk.id) from homework as hk where hk.orgId=mo.orgid and hk.orgType=mo.orgtype) as hkcount ,(select COUNT(crd.id) from check_record as crd where crd.org_id=mo.orgid and crd.org_type=mo.orgtype)as yycount,(select COUNT(hk2.id) from homework hk2 where hk2.orgid=mo.orgid and hk2.orgType=mo.orgtype  and hk2.end_time < subdate(curdate(),date_format(curdate(),'%w')-1) and hk2.end_time > subdate(curdate(),date_format(curdate(),'%w')+7)) as lasthkCount,

(select count(crd2.id) from check_record crd2 where crd2.org_id=mo.orgid and crd2.org_type=mo.orgtype and crd2.create_time < subdate(curdate(),date_format(curdate(),'%w')-1) and crd2.create_time > subdate(curdate(),date_format(curdate(),'%w')+7) ) as lastyyCount ,

(SELECT count( useCount.userid ) from  (select cr2.user_id as userId,cr2.end_time as endTime,so3.orgId as orgId ,so3.orgType as orgType from course_read cr2  inner join student_org so3  on so3.studentId=cr2.user_id where cr2.end_time < subdate(curdate(),date_format(curdate(),'%w')-1)  and cr2.end_time > subdate(curdate(),date_format(curdate(),'%w')+7) group by cr2.user_id ) as useCount where useCount.orgid=mo.orgid and useCount.orgType=mo.orgtype )  as lswUseCount from manager_org mo  inner join student_org so on so.orgid = mo.orgid and so.orgtype=mo.orgtype

left join course_read cr on cr.user_id = so.studentidwhere managerid='10209' group by mo.orgid,mo.orgtype

//-----------------------------------------------

两张表  查一张表的id在另一张表中不存在的sql：

select * from user_class where 

id not in(

   select distinct orgid from student_org where orgtype=1

)

and 

    id not in

    

(select distinct orgid from manager_org where orgtype=1)

//------------------------------------------------------

mysql   字段按拼音的第一个字母排序

SELECT dm.id ,dm.name  FROM domain dm ORDER BY CONVERT(name USING gbk)

//-----------------------------------------------------

本地时间加240天小于当前时间

select ui.id as uiid from user_info ui 

       inner join (select rs.uid as uid from (select avg(read_score) as avgsc ,user_id as uid from course_read where read_score >0  group by user_id ) rs where avgsc>=90)as rs2

       on rs2.uid = ui.id

       inner join (select ls.luid as luid from (select avg(listen_score)as avgls ,user_id as luid from course_listen where listen_score>0 group by user_id) ls where avgls>=90) as ls2

       on ls2.luid = ui.id

where date_add(ui.reg_datetime,INTERVAL 240 DAY) < now()