mysql DEMO
来源:互联网 发布:淘宝网买苹果手机可靠吗 编辑:程序博客网 时间:2024/06/15 23:43
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()
0 0
- mysql DEMO
- MYSQL DEMO
- qt mysql demo
- jdbc for mysql demo
- linux c mysql demo
- mysql存储过程demo
- sails+mysql demo
- Demo:C++操作MySql
- springMVC + Mybatis +Mysql demo
- mysql分库分表方案demo
- 【MySQL】事务入门demo
- mysql定时任务demo
- Mysql LOAD DATA demo
- mysql sql demo
- 用PHP+MySQL 留言板demo
- MySQL 事物回滚Demo
- python 操作mysql数据库demo
- PHP连接mysql数据库demo
- 从点滴看管理之会议管理
- Adaboost算法
- 求两个数的最大公约数(位运算)
- 从点滴看管理之积极的进行经验总结
- Python中的NumPy函数库
- mysql DEMO
- 利用servlet转发技术实现统计form表单中字母次数
- SQL数据库主从配置
- python中array和matrix的区别
- 项目方案目录结构总结
- 利用python实现KNN分类器
- linux top命令实用详解
- oracle_sql基础
- 心灵鸡汤:如何摆脱迷茫