select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

delete from people
where   peopleName in (select peopleName    from people group by peopleName      having count(peopleName) > 1)
and   peopleId not in (select min(peopleId) from people group by peopleName     having count(peopleName)>1)

select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)


update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like ‘村%’


update tableName set [Title]=left([Title],(len([Title])-1)) where Title like ‘%村’

update vitae set ispass=-1
where peopleId in (select peopleId from vitae group by peopleId


Install IIS and PHP

Applies To: Windows Server 2012, Windows Server 2012 R2

In this step of building a PHP website, you install IIS and FastCGI, download and install PHP and the WinCache extension, and upload your PHP application.

The following list shows the tasks required to complete this step:

When you are done, make sure that IIS and the PHP are installed, and your PHP application has been added to your website. Then go on to Step 2: Configure PHP Settings.

You can use the Web Platform Installer (Web PI) to install IIS, and applications that run on IIS. Web PI installs the latest versions of available Web Platform offerings with just a few simple clicks. Using Web PI, you can download and install any new tools or updates, including PHP. To learn more about the Web PI, see Learn more and install the Web PI.

If you do not use Web PI to install IIS, you can install IIS manually. To install IIS manually, use the following steps:

  1. On the Start page, click the Server Manager tile, and then click OK.
  2. In Server Manager, select Dashboard, and click Add roles and features.
  3. In the Add Roles and Features Wizard, on the Before You Begin page, click Next.
  4. On the Select Installation Type page, select Role-based or Feature-based Installation and click Next
  5. On the Select Destination Server page, select Select a server from the server pool, select your server, and click Next.
  6. On the Select Server Roles page, select Web Server (IIS), and then click Next.
  7. On the Select Features page, note the preselected features that are installed by default, and then select CGI. This selection also installs FastCGI, which is recommended for PHP applications.
  8. Click Next.
  9. On the Web Server Role (IIS) page, click Next.
  10. On the Select Role Services page, note the preselected role services that are installed by default, and then click Next.
    You only have to install the IIS 8 default role services for a static-content web server.
  11. On the Confirm Installation Selections page, confirm your selections, and then click Install.
  12. On the Installation Progress page, confirm that your installation of the Web Server (IIS) role and required role services completed successfully, and then click Close.
  13. To verify that IIS installed successfully, type the following into a web browser:


    You should see the default IIS Welcome page.

  1. On the Start page, type Control Panel, and then click the Control Panel icon in the search results.
  2. In Control Panel, click Programs, and then click Turn Windows features on or off.
  3. In the Windows Features dialog box, click Internet Information Services, note the preselected features that are installed by default, and then select CGI. This selection also installs FastCGI, which is recommended for PHP applications.
  4. Click OK.
  5. To verify that IIS installed successfully, type the following into a web browser:


    You see the default IIS Welcome page.

The preferred method to install PHP on a Windows or Windows Server computer is to use Web Platform Installer (Web PI).

  1. Open a browser to the following website: Microsoft Web Platform Installer 3.0.
  2. Click Download It Now, and then click Run.
  3. At the top of the Web Platform Installer window, click Products.
  4. Click Frameworks, and then select the current version of PHP. (At this writing, the current version is PHP 5.3.13.)
  5. Click Install.

    The Web Platform Installation page displays the version of PHP and its dependencies that will be installed.

  6. Click I Accept.

    Web PI installs the PHP packages.

  7. Click Finish.

If you decide to download PHP and install it manually, the procedures in this section guide you the following tasks:

  • Download PHP and the WinCache extension.
  • Install PHP and WinCache.
  • Add the PHP installation folder to the Path environment variable.
  • Set up a handler mapping for PHP.
  • Add default document entries for PHP.
  • Test your PHP installation.

To keep this procedure simple, install the WinCache extension but do not configure it. You will configure and test WinCache in Step 2: Configure PHP Settings.

  1. Open your browser to Windows for PHP Download Page and download the PHP 5.3 non-thread-safe zip package.
    The PHP 5.4 version does not work with the WinCache extension version 1.1. Use PHP 5.3 until this problem is resolved.
  2. Download the WinCache extension ( from the List of Windows Extensions for PHP.
  3. Extract all files in the PHP .zip package to a folder of your choice, for example C:\PHP\.
  4. Extract the WinCache .zip package to the PHP extensions folder (\ext), for example C:\PHP\ext.

    The WinCache .zip package contains one file (Php_wincache.dll).

  5. Open Control Panel, click System and Security, click System, and then click Advanced system settings.
  6. In the System Properties window, select the Advanced tab, and then click Environment Variables.
  7. Under System variables, select Path, and then click Edit.
  8. Add the path to your PHP installation folder to the end of the Variable value, for example ;C:\PHP. Click OK.
  9. Open IIS Manager, select the hostname of your computer in the Connections panel, and then double-click Handler Mappings.
  10. In the Action panel, click Add Module Mapping.
  11. In Request path, type *.php.
  12. From the Module menu, select FastCgiModule.
  13. In the Executable box, type the full path to Php-cgi.exe, for example C:\PHP\Php-cgi.exe.
  14. In Name, type a name for the module mapping, for example FastCGI.
  15. Click OK.
  16. Select the hostname of your computer in the Connections panel, and double-click Default Document.
  17. In the Action panel, click Add. Type Index.php in the Name box, and then click OK.
  18. Click Add again. Type Default.php in the Name box, and then click OK.
  1. Open a text editor, for example Notepad, as Administrator.
  2. In a new file, type the following text: <?php phpinfo(); ?>
  3. Save the file as C:\inetpub\wwwroot\Phpinfo.php.
  4. Open a browser and enter the following URL: http://localhost/phpinfo.php

    A nicely formatted webpage is displayed showing the current PHP settings.

Once you have IIS and PHP installed, you can add a PHP application to your web server. This section describes how to set up your PHP application on an IIS web server with PHP installed. It does not explain how to develop a PHP application.

  1. Open IIS Manager.
    • For Windows Server 2012, on the Start page click the Server Manager tile, and then click OK. On the Server Manager Dashboard, click the Tools menu, and then click Internet Information Services (IIS) Manager.
    • For Windows 8, on the Start page type Control Panel, and then click the Control Panel icon in the search results. On the Control Panel screen, click System and Security, clickAdministrative Tools, and then click Internet Information Services (IIS) Manager.
  2. In the Connections pane, right-click the Sites node in the tree, and then click Add Website.
  3. In the Add Website dialog box, type a friendly name for your website in the Site name box.
  4. If you want to select a different application pool than the one listed in the Application Pool box, click Select. In the Select Application Pool dialog box, select an application pool from the Application Pool list and then click OK.
  5. In the Physical path box, type the physical path of the website’s folder, or click the browse button () to navigate the file system to find the folder.
  6. If the physical path that you entered in step 5 is to a remote share, click Connect as to specify credentials that have permission to access the path. If you do not use specific credentials, select the Application user (pass-through authentication) option in the Connect As dialog box.
  7. Select the protocol for the website from the Type list.
  8. The default value in the IP address box is All Unassigned. If you must specify a static IP address for the website, type the IP address in the IP address box.
  9. Type a port number in the Port text box.
  10. Optionally, type a host header name for the website in the Host Header box.
  11. If you do not have to make any changes to the site, and you want the website to be immediately available, select the Start Web site immediately check box.
  12. Click OK.

Windows Custom ISO w/VirtIO Drivers

Building the ISO

  1. Get the latest binary VirtIO drivers for Windows, packaged as an ISO file, from
  2. Make a folder: c:\custom.
  3. Extract your Windows Server 2008r2 ISO to: c:\custom\server2008r2 with a compression tool such as 7zip
  4. Extract the VirtIO ISO to c:\custom\server2008r2\virtio.
  5. Use ImgBurn or equivalent ISO mastering tool to build your custom ISO. These instructions apply to ImgBurn.
    • mode menu > build
    • select your source: click the icon of a folder with a magnifying glass and select c:\custom\server2008r2
    • select your destination (output): same icon under destination field c:\custom\server2008r2_virtio.iso
    • Options tab > filesystem: UDF
    • Labels tab > UDF: Server2008r2_virtio
    • Advanced tab > bootable disc sub-tab > check make image bootable
      • Emulation Type: none
      • Boot Image: C:\custom\server2008r2\boot\
      • Platform ID: 80×86
      • Developer ID: Microsoft Corporation
      • Load Segment: leave the default
      • Sectors to load: 8
      • Build (folder arrow to disc file icon).

You now have a Windows ISO with built-in VirtIO drivers ready for use as a custom Vultr ISO.


At first, no drive is present. This is normal. Click “Load Driver”.


  • For Server 2008, use VISTA.
  • For Server 2008r2, use WIN7.
  • For Server 2012, use WIN8.

For example, using 2008r2 on a 64 bit VPS:

Browse to virtio > WIN7 > AMD64 > OK.


Select “Red Hat VirtIO SCSI” driver.


Now the drive is visible.


Additional Steps

Configuring Network Connectivity
  1. After you log in for the first time on your Windows VPS via View Console, you will be greeted by the Server Manager.
    On the upper right part of the menu, click on Tools then choose Computer Management. A new window should open.
  2. On the left pane of that new window (should be named Computer Management), select Device Manager.
  3. You should notice 3 Devices that are marked with Yellow ! signs (4 if you chose to enable Private Networking).
    Right-click on Ethernet Controller and choose Update Driver Software...
  4. Two choices should appear, choose the one below, which is Browse my computer for driver software.
  5. Click Browse... and navigate to D:\virtio\WIN8\AMD64, then click Next.
  6. You will see a pop-up confirmation to verify that you want to install Red Hat VirtIO Ethernet Adapter, just click Install.
  7. Your VPS should now have Internet Connectivity! Do steps 3-6 again for any more Unrecognized Devices on your system.
Getting Windows RDP to Work (optional)
  1. Though you can do just fine with Vultr’s View Console. You can’t copy / paste text to or from it, for that you might want to installWindows RDP (Remote Desktop Protocol). To do so, open Server Manager.
  2. Click on Manage, then choose Add Roles and Features.
  3. It is safe to keep clicking Next until you get to Server Roles section of it.
  4. Scroll down a bit, and find Remote Desktop Services, click the check-box beside it to select. Then click Next.
  5. You can skip the Features part for now, so just click Next again.
  6. Now on Role Services, click the check-box beside Remote Desktop Session Host.
  7. A pop-up should appear, just click Add Features, then click Next one last time.
  8. Confirm your installation by clicking Install. Your VPS should now be installing Windows RDP.
  9. Once the installation finishes, you can reboot your VPS to apply the changes. And you’re done! You should now be able to connect to your VPS via Windows RDP, using your IP Address, User name (default is Administrator) and Password.




IMG_0221 IMG_0220






Happy Place



Sleep Away



How to use REPLACE INTO in SQL

1. 尝试把新行插入到表中
2. 当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时:
a. 从表中删除含有重复关键字值的冲突行
b. 再次尝试把新行插入到表中


代码如下 复制代码
[INTO] tbl_name [(col_name,…)]
VALUES ({expr | DEFAULT},…),(…),…
[INTO] tbl_name
SET col_name={expr | DEFAULT}, …
[INTO] tbl_name [(col_name,…)]

id 是主键
代码如下 复制代码
REPLACE INTO fanwe_order(id,sn) VALUES(’33′,’测试replace into 使用’) 结果:受影响的行数:2
SELECT * FROM fanwe_order WHERE sn=’测试replace into 使用’
结果:查出 一行记录
REPLACE INTO fanwe_order(id,sn) VALUES(’34′,’测试replace into 使用’)
手册上提到,如果表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值。
意,除非表有一个PRIMARY KEY或UNIQUE索引,否则,使用一个REPLACE语句没有意义。
两种情况之一使用replace语句才会出现替换:1.主键值相同 2.索引键值相同

MySQL replace into 用法(insert into 的增强版)
REPLACE依赖于表中的主键或唯一索引,如果一个表中存在的记录与用于PRIMARY KEY或UNIQUE索引的新记录具有相同的值,则在新记录被插入之前,旧记录被删除。

在 SQL Server 中可以这样处理:
代码如下 复制代码
if not exists (select 1 from t where id = 1)
insert into
t(id, update_time) values(1, getdate())
update t set update_time = getdate() where id = 1
那么 MySQL 中如何实现这样的逻辑呢?别着急!MySQL 中有更简单的方法: replace into
代码如下 复制代码
t(id, update_time) values(1, now());

代码如下 复制代码
t(id, update_time) select 1, now();
replace into 跟 insert 功能类似,不同点在于:replace into 首先尝试插入数据到表中, 1. 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据。 2. 否则,直接插入新数据。



这三个命令的相同点truncate和不带where子句的delete, 以及drop都会删除表内的数据,不同点是truncate会清除表数据表置id从1开始,delete就只删除记录,drop可以用来删除表或数据库并且将表所占用的空间全部释放
1. truncate和 delete只删除数据不删除表的结构(定义)
drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态.
代码如下 复制代码
语法   TRUNCATE TABLE name   
参数   name   
Truncate table 表名 速度快,而且效率高,因为:   TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少

2.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发.
truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.
DELETE 语句用于删除表中的行。
代码如下 复制代码
“Fred Wilson” 会被删除:
DELETE FROM Person WHERE LastName = ‘Wilson’
代码如下 复制代码
DELETE FROM table_name或者:
DELETE * FROM table_name

truncate 语句缺省情况下见空间释放到 minextents个 extent,除非使用reuse storage.
4.速度,一般来说: drop> truncate > delete
有时候我们会决定我们需要从数据库中清除一个表格。事实上,如果我们不能这样做的话,那将会是一个很大的问题,因为数据库管理师 (Database Administrator — DBA) 势必无法对数据库做有效率的管理。还好,SQL 有提供一个 DROP TABLE的语法来让我们清除表格。 DROP TABLE 的语法是:
代码如下 复制代码
我们如果要清除在SQL CREATE 中建立的顾客表格,我们就打入:
代码如下 复制代码
DROP TABLE customer.

5.安全性:小心使用drop 和truncate,尤其没有备份的时候.否则哭都来不及
使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大.
想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还是用delete.
如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据。