简单查询:
select * from dbo.SmsLog where mobile='186xxxxxxxx' *代表查询所有,可以替换为具体字段,查询具体字段值
select * from dbo.ClienterFinanceAccount where clienterID=3263
select top 100 * from OldCustomerCard where CustomerName='一米阳光' 查询前100条数据
SELECT count(distinct Id) FROM SupplierGroup(nolock)
删除命令 delete from tableName where 条件
delete from ClienterFinanceAccount where clienterID=3263 解绑提现账户
更新值 update tableName set ... where 条件
UPDATE BusinessClienterRelation SET isbind=0 WHERE BusinessId=2092
UPDATE[dbo].[OrderChild] SET TotalPrice=0.01 where OrderId='151152'; 支付金额修改
update CustomerExtOrderInfo set CustomerTypeId=0 where CustomerID =5833733 and SupplierId=269267
UPDATE dbo.Dine SET OrderStatusId=2,CreatTime=GETDATE() WHERE DineId=10500451
插入命令:insert into tableName values('ss','11'....)
INSERT INTO OldCustomerCard VALUES ('一米阳光','X0255452010','0.0','18618486814','2','会员类型1','0','269267','1990-05-08',
'2016-09-18 15:23:20:000')
复杂查询
select * from dbo.clienter c where c.Id>(select min(Id) from dbo.clienter d where c.IDcard=d.IDcard
group by IDcard having count(IDcard)>1)
delete from dbo.clienter where Id !=(select min(Id) from dbo.clienter where dbo.clienter.IDcard=dbo.clienter.IDcard)
select count(1) from [order] o
join business b on o.businessid =b.id
where b.city='北京市' 查询城市订单数
两张表关联查询,
select distinct b.cityid from order a join business b on a.businessid=b.id 查询订单城市并去重
SELECT OrderCount,clienterId,*
FROM [order] (NOLOCK)
WHERE ActualDoneDate IS NOT NULL
AND ActualDoneDate >= '2015-07-30'
AND ActualDoneDate < '2015-07-30 17:45:25'
执行监控作业 :exec+监控事件
EXEC dbo.RedPackageActivityTimer
左右外连接的区别:
左连接:a left join b on a.id=b.id 以a表为中心匹配,b表中没有与之匹配,返回null
右连接 a right join b on a.id=b.id 以b表为中心匹配,a表中没有与之匹配,返回null
表中有A B C三列,用SQL语句实现:当A列大于B列时选择A列否则选择B列,当B列大于C列时选择B列否则选择C列。
select (case when a>b then a else b end),(case when b>c then b else c end) from table
两张关联表,删除主表中已经再附表中没有的信息
delete from info where not exists(select * from infobz where info.id=infobz.dtID)
--删除除了自动编号不同, 其他都相同的学生冗余信息
DELETE table where 自动编号 not in(sele min(自动编号) from table group by 学号,姓名,
课程编号,课程名称,分数);
--用一条SQL 语句 查询出每门课都大于80 分的学生姓名
select name from table group by name having min(fenshu)>80
有一张表,里面有3个字段:语文,数学,英语。其中有3条记录分别表示语文70分,数学80分,英语58分,请用一条sql语句查询出这三条记录并按以下条件显示出来(并写出您的思路):
大于或等于80表示优秀,大于或等于60表示及格,小于60分表示不及格。
显示格式:
语文 数学 英语
及格 优秀 不及格
select(case when '语文'>80 then '优秀'
when '语文'>=60 then '及格' else '不及格') as 语文
(case when '数学'>80 then '优秀'
when '数学'>=60 then '及格' else '不及格') as 数学
(case when '英语'>80 then '优秀'
when '英语'>=60 then '及格' else '不及格') as 英语
from table;
一个表中的Id有多个记录,把所有这个id的记录查出来,并显示共有多少条记录数。
select id, count(*) from table group by id having count(*)>1
日程安排提前五分钟提醒
select * from table where datediff('minute',f开始时间,getdate())>5
创建sonar数据库、用户 并赋予其数据库权限
mysql> CREATE DATABASE sonar CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> CREATE USER 'sonar' IDENTIFIED BY 'sonar';
mysql> GRANT ALL ON sonar.* TO 'sonar'@'%' IDENTIFIED BY 'sonar';
mysql> GRANT ALL ON sonar.* TO 'sonar'@'localhost' IDENTIFIED BY 'sonar';
mysql> FLUSH PRIVILEGES;
两张表关联查询
SELECT DISTINCT u.user_id FROM `user` u left join supermarket s on s.user_id=u.user_id WHERE u.is_cs=1 and s.sm_state=1 ORDER BY u.create_time DESC LIMIT 100
select * from table where isnull(userid)and userid=0 //查询内容为空或内容为0的数据