热点
mysql – ActiveRecord在Ruby 1.9.2-rc1下以ASCII-8Bit返回数据
MYSQL将另一个表中的值插入一列
mysql – 只有一行Rails ActiveRecord数据库
mysql – 在MariaDB中如何从表中选择前10行？
php – 如何在PDO中使用/编写mysql_real_escape_string？
mysql – 这是更改大数据表字符集的更好方法吗？
R批量上传数据到MYSQL数据库
如何使用rails运行简单的MYSQL查询
MySQL – 无法使用SET变量创建视图
mysql – 使用GROUP BY删除重复项的查询
5 5 月 2025, 周一
MsSql

2008年6月25日收集SQL命令：

由 dawei 没有评论 #2008年 #25日 #命令 #收集

2008年6月25日 11:12:28
1.检查要创建的表是否存在？

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top"><span style="color: #0000ff">IF

<span style="color: #000000"><span style="color: #ff00ff">OBJECT_ID<span style="color: #000000">(<span style="color: #ff0000">'<span style="color: #ff0000">Price_history<span style="color: #ff0000">'<span style="color: #000000">,<span style="color: #ff0000">'<span style="color: #ff0000">U<span style="color: #ff0000">'<span style="color: #000000">)<span style="color: #0000ff">IS<span style="color: #000000"><span style="color: #808080">NOT<span style="color: #000000"><span style="color: #0000ff">NULL<span style="color: #000000">

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">DROP<span style="color: #000000"><span style="color: #0000ff">TABLE<span style="color: #000000">Price_history
2.复到一个表的表结构以开成一个新表。

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top"><span style="color: #0000ff">SELECT

<span style="color: #000000"><span style="font-weight: bold; color: #800000">1<span style="color: #000000"><span style="color: #0000ff">AS<span style="color: #000000">ProductID,UnitPrice<span style="color: #0000ff">AS<span style="color: #000000">OldPrice,UnitPrice<span style="color: #0000ff">AS<span style="color: #000000">NewPrice,<span style="color: #ff00ff">GETDATE<span style="color: #000000">()<span style="color: #0000ff">AS<span style="color: #000000">Date

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">INTO<span style="color: #000000">Price_history

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">FROM<span style="color: #000000">dbo.Products

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">WHERE<span style="color: #000000"><span style="font-weight: bold; color: #800000">1<span style="color: #808080">=<span style="font-weight: bold; color: #800000">0
3.判断某触发器是否存在？

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top"><span style="color: #0000ff">IF

<span style="color: #000000"><span style="color: #ff00ff">OBJECT_ID<span style="color: #000000">(<span style="color: #ff0000">'<span style="color: #ff0000">trg_Products_u<span style="color: #ff0000">'<span style="color: #000000">,<span style="color: #ff0000">'<span style="color: #ff0000">TR<span style="color: #ff0000">'<span style="color: #000000">)<span style="color: #0000ff">IS<span style="color: #000000"><span style="color: #808080">NOT<span style="color: #000000"><span style="color: #0000ff">NULL<span style="color: #000000">

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">DROP<span style="color: #000000"><span style="color: #0000ff">TRIGGER<span style="color: #000000">trg_Products_u;

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">GO
4.创建一个<font color="#800000">触发器

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top"><span style="color: #0000ff">CREATE

<span style="color: #000000"><span style="color: #0000ff">TRIGGER<span style="color: #000000">trg_Products_u<span style="color: #0000ff">ON<span style="color: #000000">dbo.Products<span style="color: #0000ff">FOR<span style="color: #000000"><span style="color: #0000ff">UPDATE<span style="color: #000000">

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">AS<span style="color: #000000">

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #008080">--<span style="color: #008080">如果更新的不是UnitPrice或没有更新直接返回<span style="color: #008080">

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">IF<span style="color: #000000"><span style="color: #808080">NOT<span style="color: #000000"><span style="color: #0000ff">UPDATE<span style="color: #000000">(UnitPrice)<span style="color: #808080">OR<span style="color: #000000"><span style="font-weight: bold; color: #008000">@@ROWCOUNT<span style="color: #808080">=<span style="font-weight: bold; color: #800000">0<span style="color: #000000">

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">RETURN<span style="color: #000000">;

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">ELSE<span style="color: #000000">

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">INSERT<span style="color: #000000"><span style="color: #0000ff">INTO<span style="color: #000000">price_history//实现了在更新价格时在日志表里记录下原始价格和新价格

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">SELECT<span style="color: #000000">i.ProductID,d.UnitPrice,i.UnitPrice,<span style="color: #ff00ff">getdate<span style="color: #000000">()

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">FROM<span style="color: #000000">insertedi

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #808080">join<span style="color: #000000">deletedd //用Inserted和Deleted这两个表实现：）

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">on<span style="color: #000000">i.ProductID<span style="color: #808080">=<span style="color: #000000">d.ProductID

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">GO
5.一个带多表查询的UPdate语句

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top"><span style="color: #0000ff">update

<span style="color: #000000">p

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">set<span style="color: #000000">UnitPrice<span style="color: #808080">=<span style="color: #000000">UnitPrice<span style="color: #808080"><span style="font-weight: bold; color: #800000">1.5<span style="color: #000000">

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">from<span style="color: #000000">dbo.Productsp

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #808080">join<span style="color: #000000">dbo.Supplierss

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">on<span style="color: #000000">p.SupplierID<span style="color: #808080">=<span style="color: #000000">s.SupplierID

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">where<span style="color: #000000">s.city<span style="color: #808080">=<span style="color: #000000">N<span style="color: #ff0000">'<span style="color: #ff0000">London<span style="color: #ff0000">'
6.检查、创建、执行存储过程

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top"><span style="color: #0000ff">IF

<span style="color: #000000"><span style="color: #ff00ff">OBJECT_ID<span style="color: #000000">(<span style="color: #ff0000">'<span style="color: #ff0000">prc_UpdateProductPrice<span style="color: #ff0000">'<span style="color: #000000">,<span style="color: #ff0000">'<span style="color: #ff0000">P<span style="color: #ff0000">'<span style="color: #000000">)<span style="color: #0000ff">IS<span style="color: #000000"><span style="color: #808080">NOT<span style="color: #000000"><span style="color: #0000ff">NULL<span style="color: #000000">

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">DROP<span style="color: #000000"><span style="color: #0000ff">PROC<span style="color: #000000">prc_UpdateProductPrice;

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">GO<span style="color: #000000">

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">CREATE<span style="color: #000000"><span style="color: #0000ff">PROCEDURE<span style="color: #000000">prc_UpdateProductPrice

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">WITH<span style="color: #000000">RECOMPILE

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">AS<span style="color: #000000">

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">BEGIN<span style="color: #000000">

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">SET<span style="color: #000000">NOCOUNT<span style="color: #0000ff">ON<span style="color: #000000">;

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">update<span style="color: #000000">p

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">set<span style="color: #000000">UnitPrice<span style="color: #808080">=<span style="color: #000000">UnitPrice<span style="color: #808080"><span style="font-weight: bold; color: #800000">1.5<span style="color: #000000">

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">from<span style="color: #000000">dbo.Productsp

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #808080">join<span style="color: #000000">dbo.Supplierss

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">on<span style="color: #000000">p.SupplierID<span style="color: #808080">=<span style="color: #000000">s.SupplierID

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">where<span style="color: #000000">s.city<span style="color: #808080">=<span style="color: #000000">N<span style="color: #ff0000">'<span style="color: #ff0000">London<span style="color: #ff0000">'<span style="color: #000000">

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">END<span style="color: #000000">

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">GO<span style="color: #000000">

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">EXEC<span style="color: #000000">prc_UpdateProductPrice

<img alt="" src="https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif" align="top">

<span style="color: #0000ff">GO

由 dawei

【声明】：丽水站长网内容转载自互联网，其相关言论仅代表作者个人观点绝非权威，不代表本站立场。如您发现内容存在版权问题，请提交相关链接至邮箱：bqsm@foxmail.com，我们将及时予以处理。

相关文章

MsSql

SQL：计算所有表中的列数,不包括视图

dawei
MsSql

sqlserver中datetime和timestamp之间的区别？

dawei
MsSql

sql-server – SQL Server 2008：从datetime2获取日期部分

dawei

您错过了

MySql

mysql – ActiveRecord在Ruby 1.9.2-rc1下以ASCII-8Bit返回数据

MySql

MYSQL将另一个表中的值插入一列

MySql

mysql – 只有一行Rails ActiveRecord数据库

MySql

mysql – 在MariaDB中如何从表中选择前10行？