sql 语句
方法：
Select
update
create
or
and
like
统计数目
SELECT COUNT(*) FROM i_member_personal WHERE unit_nature_id = '828';
select COUNT(*) from i_member_user a , i_member_personal b where a.personal_id=b.id and b.nation_id is NULL
按降序排列
SELECT *FROM cm_sms_record WHERE mobile = 15718830678 ORDER BY created_time DESC ;
按降序排列并显示前两行内容
SELECT *FROM cm_sms_record WHERE mobile = 15718830678 ORDER BY created_time DESC limit 2 ;
更新某表中某行的某个字段值
update cm_sms_record set auth_code=111 where id =219;
UPDATE i_member_level SET min_exp = 1 and max_exp =2 WHERE id = 1 ; 同时修改多项内容
UPDATE i_member_level SET min_exp = 0 , max_exp = 120 WHERE id = 1 ;
查看整张表的内容
SELECT * FROM i_member_level;
SELECT * FROM i_member_level where id>=1;
sql 分页相关知识
select * from chengyuan where sid=2 limit 0,3 ;
在mysql 中分页使用如上的方式。
limit 0,3 表示 从地 0+1 条开始 取3条记录
而在sqlserver中使用top n 的方式。
比如：select *from cm_sms_record where mobile = 11111111130 limit 0,3
比如：SELECT * FROM `cm_sms_record` where mobile = 11111111111 ORDER BY created_time DESC limit 0,2 ;
查看包含某个字段或数字的值 ， like
SELECT * FROM `i_member_user` where nickname like '%test%' ;
SELECT
m.id,p. NAME,m.score
FROM
i_member_user m
INNER JOIN i_member_personal p ON m.personal_id = p.id
WHERE
m.id,p. NAME,m.score
FROM
i_member_user m
INNER JOIN i_member_personal p ON m.personal_id = p.id
WHERE
p. NAME = '王垂芳';
先获取一个值，再进行查询
UPDATE `i_member_personal` p SET p.mobile = (SELECT u1.mobile FROM i_member_user u1 WHERE u1.id = p.owner_id), p.email = (SELECT u2.email FROM i_member_user u2 WHERE u2.id = p.owner_id);
更新personal 表命令
从会议的稿件编号，一直查询到 附件； 显示所有关联表的数据在一行
SELECT
*
FROM
cm_attachment a,
i_paper b,
i_paper_article c ,
conf_paper_order d
WHERE
a.id = c.attachment_id
AND c.id = b.latest_article_id
AND b.`code` = d.paper_order_num
FROM
cm_attachment a,
i_paper b,
i_paper_article c ,
conf_paper_order d
WHERE
a.id = c.attachment_id
AND c.id = b.latest_article_id
AND b.`code` = d.paper_order_num
AND d.num = 20160108020008
//两张表关联查询， 查询专家的身份证号
SELECT
ex_user.id, id号
ex_user.real_name, 名字
ex_user_info.credential_name,
ex_user_info.credential_no
FROM ex_user
INNER JOIN ex_user_info
ON ex_user.id =ex_user_info.expert_id
WHERE
ex_user_info.credential_no
FROM ex_user
INNER JOIN ex_user_info
ON ex_user.id =ex_user_info.expert_id
WHERE
ex_user.real_name='张趵'
//ORDER BY
// baseOrder.created_time ASC;
//例子
SELECT
baseOrder. CODE 订单编号,
p. NAME 姓名,
r.remark 记录描述,
baseOrder.created_time 下单时间
FROM
i_task_publish_record r
INNER JOIN i_task t ON r.task_id = t.id
INNER JOIN i_order baseOrder ON t.order_id = baseOrder.id
INNER JOIN i_member_user u ON baseOrder.owner_id = u.id
INNER JOIN i_member_personal p ON u.personal_id = p.id
WHERE
r.remark IS NOT NULL
ORDER BY
baseOrder. CODE 订单编号,
p. NAME 姓名,
r.remark 记录描述,
baseOrder.created_time 下单时间
FROM
i_task_publish_record r
INNER JOIN i_task t ON r.task_id = t.id
INNER JOIN i_order baseOrder ON t.order_id = baseOrder.id
INNER JOIN i_member_user u ON baseOrder.owner_id = u.id
INNER JOIN i_member_personal p ON u.personal_id = p.id
WHERE
r.remark IS NOT NULL
ORDER BY
baseOrder.created_time ASC;
查询充值金额，统计前n行之和
先通过括号里面的sql，筛选出需要的数据，在对该数据进行统计
SELECT q.user_id,SUM(q.money)
FROM(
SELECT *
FROM jctp2p_decository pn
WHERE pn.status = 1 AND pn.type = 'R01' AND pn.user_id = '1124106'
ORDER BY id desc LIMIT 1 )q
Navicat For Mysql快捷键
ctrl+q 打开查询窗口
ctrl+/ 注释sql语句
ctrl+shift +/ 解除注释
ctrl+r 运行查询窗口的sql语句
ctrl+shift+r 只运行选中的sql语句
F6 打开一个mysql命令行窗口
ctrl+l 删除一行
ctrl+n 打开一个新的查询窗口
ctrl+w 关闭一个查询窗口
