Your browser (Internet Explorer 6) is out of date. It has known security flaws and may not display all features of this and other websites. Learn how to update your browser.
X
Post

SQL删除重复数据只保留一条

用SQL语句,删除掉重复项只保留一条

在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
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)

3、查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
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)

5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
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)

6.消除一个字段的左边的第一位:

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

7.消除一个字段的右边的第一位:

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

8.假删除表中多余的重复记录(多个字段),不包含rowid最小的记录
update vitae set ispass=-1
where peopleId in (select peopleId from vitae group by peopleId

Post

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.
    noteNote
    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:

    http://localhost

    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:

    http://localhost

    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.
    CautionCaution
    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 (Php_wincache-svn20110402-5.2-nts-vc6-x86.zip) 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.
Post

Windows Custom ISO w/VirtIO Drivers

Building the ISO

  1. Get the latest binary VirtIO drivers for Windows, packaged as an ISO file, from http://www.linux-kvm.org/page/WindowsGuestDrivers/Download_Drivers/
  2. Make a folder: c:\custom.
  3. Extract your Windows Server 2008r2 ISO to: c:\custom\server2008r2 with a compression tool such as 7ziphttp://www.7-zip.org/.
  4. Extract the VirtIO ISO to c:\custom\server2008r2\virtio.
  5. Use ImgBurn http://www.imgburn.com/ 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\etfsboot.com
      • 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.


Installing

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

virtio1-driveisnotfound.png

  • 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.

virtio2-selectfolder.png

Select “Red Hat VirtIO SCSI” driver.

virtio3-selectdriver.png

Now the drive is visible.

virtio4-driveisfound.png


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.
Image

IMG_0229

IMG_0228

Image

IMG_0221 IMG_0220

Image

BBQ

IMG_0533

IMG_0531

Audio

Happy Place

那天看了乐视TV的广告,于是找了很久的背景音乐,嘿嘿。

Audio

Sleep Away

Win7的示例音乐,还不错。

Post

How to use REPLACE INTO in SQL

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

使用格式:

代码如下 复制代码
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,…)]
VALUES ({expr | DEFAULT},…),(…),…
或:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, …
或:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,…)]
SELECT …

id 是主键
测试方式一,插入索引值是一样的:
代码如下 复制代码
REPLACE INTO fanwe_order(id,sn) VALUES(’33′,’测试replace into 使用’) 结果:受影响的行数:2
SELECT * FROM fanwe_order WHERE sn=’测试replace into 使用’
结果:查出 一行记录
测试方式二,插入主键值是重复的:
将插入id指定为34。这样不与数据表中的id有重复现象,之后运行查询
REPLACE INTO fanwe_order(id,sn) VALUES(’34′,’测试replace into 使用’)
结果:没有新插入一条数据。还是替换了原来的那行。id从33变为34
原因分析:
手册上提到,如果表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值。
意,除非表有一个PRIMARY KEY或UNIQUE索引,否则,使用一个REPLACE语句没有意义。
刚才测试的例子中。id是主键,sn是唯一索引。测试方式一是出现主键值一样,测试方式二是出现唯一索
引值一样。两种情况都出现了replace
理解:插入数据的时候,假如遇到主键值或者唯一索引键值一样的话。那么就使用替代(replace单词的含义反应了其作用)的方式,删掉原来的。以当前插入的行进行替代(所以需要同时具有insert和delete权限)
先删除后插入新的。正好说明了我看到的现象:显示受影响的行数是2
两种情况之一使用replace语句才会出现替换:1.主键值相同 2.索引键值相同

为了能够使用REPLACE,您必须同时拥有表的INSERT和DELETE权限。
REPLACE语句会返回一个数,来指示受影响的行的数目。该数是被删除和被插入的行数的和。如果对于一个单行REPLACE该数为1,则一行被插入,同时没有行被删除。如果该数大于1,则在新行被插入前,有一个或多个旧行被删除。如果表包含多个唯一索引,并且新行复制了在不同的唯一索引中的不同旧行的值,则有可能是一个单一行替换了多个旧行。
受影响的行数可以容易地确定是否REPLACE只添加了一行,或者是否REPLACE也替换了其它行:检查该数是否为1(添加)或更大(替换)。
MySQL replace into 用法(insert into 的增强版)
REPLACE依赖于表中的主键或唯一索引,如果一个表中存在的记录与用于PRIMARY KEY或UNIQUE索引的新记录具有相同的值,则在新记录被插入之前,旧记录被删除。
依赖主键或索引Mysql能够实现快速的判断,使用REPLACE,需要同时拥有表的INSERT和DELETE权限。
Replace首先尝试把新行插入到表中,如果因为主键或唯一键出现冲突而造成插入失败时,则从表中删除含有重复关键字值的冲突行,然后尝试把新行插入到表中。

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

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

Post

TRUNCATE, DELETE and DROP in SQL

这三个命令的相同点truncate和不带where子句的delete, 以及drop都会删除表内的数据,不同点是truncate会清除表数据表置id从1开始,delete就只删除记录,drop可以用来删除表或数据库并且将表所占用的空间全部释放
不同点:
1. truncate和 delete只删除数据不删除表的结构(定义)
drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态.
代码如下 复制代码
Truncate是SQL中的一个删除数据表内容的语句,用法是:
语法   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 语句
DELETE 语句用于删除表中的行。
语法
代码如下 复制代码
DELETE FROM 表名称 WHERE 列名称 = 值
“Fred Wilson” 会被删除:
DELETE FROM Person WHERE LastName = ‘Wilson’
删除所有行
可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的:
代码如下 复制代码
DELETE FROM table_name或者:
DELETE * FROM table_name

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

5.安全性:小心使用drop 和truncate,尤其没有备份的时候.否则哭都来不及
使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大.
想删除表用drop
想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还是用delete.
如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据。
总结,在使用上面三种命令时最好用delete来代替,其它两个都是非常危险工作,一不小心你的数据库或表可能被删除或清除哦。