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;
9. case when then
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
11. 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.studentid where managerid='10209' group by mo.orgid,mo.orgtype
12. 实践案例
12.1 两张表 查一张表的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)
12.2 mysql 字段按拼音的第一个字母排序
SELECT dm.id ,dm.name FROM domain dm ORDER BY CONVERT(name USING gbk)
12.3 本地时间加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()
12.4 插缝查询(查一张表中最先中断的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;
12.5 查上周的时间周日到周六
and cr.end_time>=subdate(curdate(),date_format(curdate(),'%w')+7) and cr.end_time<=subdate(curdate(),date_format(curdate(),'%w')-0)
12.6 查上周周一到周五的数据
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());
12.7 可以直接加
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