sql 语句

方法：

Select

update

create

and

统计数目

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

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

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.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.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 关闭一个查询窗口