30 Pro jQuery Tips, Tricks and Strategies

Standard

原文:http://www.problogdesign.com/coding/30-pro-jquery-tips-tricks-and-strategies/

Whether you’re a developer or a designer, a strong jQuery skillset is something you can’t afford to be without. Today, I’m going to show you 30 handy jQuery coding tricks that will help you make your scripts more robust, elegant and professional.

Getting Started

These tips and tricks all have one thing in common- they are all smashingly useful. With this stuff in your back pocket, you’ll be ready to go change the world, and even better, write jQuery like you know what you’re doing. It’s gonna be fun.

We’ll start with some basic tricks, and move to some more advanced stuff like actually extending jQuery’s methods and filters. Of course, you should be familiar with the basics of jQuery first. If you haven’t used jQuery before, I highly recommend browsing the documentation and watching jQuery for Absolute Beginners Video Series. Otherwise, you’re ready to dig in!

#1 – Delay with Animate()

This is a very quick, easy way to cause delayed actions in jQuery without using setTimeout. The way we make it work is to add an animate function into your chain and animate the element to 100% opacity (which it’s already at), so it looks like nothing is happening.

For instance, let’s say that you wanted to open a dialog and then fade it away after 5 seconds. Using animate, you can do it like this:

1
2
3
$(function(){
	$("body").append("<div class='dialog'></div>").<em>animate({ opacity : 1.0 }, 5000)</em>.fadeOut();
});

Don’t you just love jQuery chaining? You’re welcome to read more about this technique from Karl Swedberg.

UPDATE: jQuery 1.4 has eliminated the need for this hack with a method called delay(). It is just what is sounds like – a function specifically made to delay an animation effect. Way to go, jQuery!

#2 – Loop through Elements Backwards

One of my personal favorites is being able to loop backwards through a set of elements. We all know each() lets us easily loop through elements, but what if we need to go backwards? Here’s the trick:

1
2
3
4
5
6
7
8
$(function(){
	var reversedSet = $("li").get().reverse();
	//Use get() to return an array of elements, and then reverse it
 
	$(reversedSet).each(function(){
		//Now we can plug our reversed set right into the each function. Could it be easier?
	});
});

#3 – Is There Anything in the jQuery Object?

Another very elementary but regularly useful trick is checking if there are any elements in the jQuery object. For example, let’s say we need to find out if there are any elements with a class of ‘active’ in the DOM. You can do that with a quick check of the jQuery object’s length property like this:

1
2
3
4
5
$(function(){
	if( $(".active").length ){
		//Now the code here will only be executed if there is at least one active element
	}
});

This works because 0 evaluates false, so the expression only evaluates true if there is at least one element in the jQuery object. You can also use size() to do the same thing.

#4 – Access iFrame Elements

Iframes aren’t the best solution to most problems, but when you do need to use one it’s very handy to know how to access the elements inside it with Javascript. jQuery’s contents() method makes this a breeze, enabling us to load the iframe’s DOM in one line like this:

1
2
3
4
5
6
7
$(function(){
	var iFrameDOM = $("iframe#someID").contents();
	//Now you can use <strong>find()</strong> to access any element in the iframe:
 
	iFrameDOM.find(".message").slideUp();
	//Slides up all elements classed 'message' in the iframe
});

#5 – Equal Height Columns

This was one of CSS Newbie’s most popular posts of 2009, and it is a good, solid trick to have in your toolbox. The function works by accepting a group of columns, measuring each one to see which is largest, and then resizing them all to match the biggest one. Here’s the code (slighly modified):

1
2
3
4
5
6
7
8
9
10
11
12
$(function(){
	jQuery.fn.equalHeight = function () {
		var tallest = 0;
		this.each(function() {
			tallest = ($(this).height() > tallest)? $(this).height() : tallest;
		});
		return this.height(tallest);	
	}
 
	//Now you can call equalHeight
	$(".content-column").equalHeight();
});

An interesting and similar concept is the awesome jQuery masonry plugin, if you’re interested in checking it out.

#6 – Find a Selected Phrase and Manipulate It

Whether you’re looking to perform find and replace, highlight search terms, or something else, jQuery again makes it easy with html():

1
2
3
4
5
6
7
8
9
10
11
12
$(function(){
	//First define your search string, replacement and context:
	var phrase = "your search string";
	var replacement = "new string";
	var context = $(body);
 
	//
	context.html(
		context.html().replace('/'+phrase+'/gi', replacement);
	);
 
});

#7 – Hack Your Titles to Prevent Widows

Nobody likes to see widows – but thankfully with some jQuery and a little help from &nbsp; we can stop that from happening:

1
2
3
4
5
6
7
8
$(function(){
	//Loop through each title
	$("h3").each(function(){
		var content = $(this).text().split(" ");
		var widow = "&amp;nbsp;"+content.pop();
		$(this).html(content.join(" ")+widow);
	});
});

This technique was suggested in a comment by Bill Brown on Css-Tricks.

#8 – Add Pseudo-Selector Support in IE

Whether or not to support IE (especially 6) is a hotly debated issue, but if you are of the “let’s-make-the-best-of-this” camp, it’s nice to know that you can add pseudo-selector support with jQuery. And we aren’t just limited to :hover, although that’s the most common:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$(function(){
	<strong>//ADD HOVER SUPPORT:</strong>
	function hoverOn(){
		var currentClass = $(this).attr('class').split(' ')[0]; //Get first class name
		$(this).addClass(currentClass + '-hover');
	}
	function hoverOff(){
		var currentClass = $(this).attr('class').split(' ')[0]; //Get first class name
		$(this).removeClass(currentClass + '-hover');
	}
	$(".nav-item").hover(hoverOn,hoverOff);
 
	<strong>//ADD FIRST-CHILD SUPPORT:</strong>
	jQuery.fn.firstChild = function(){
		return this.each(function(){
			var currentClass = $(this).attr('class').split(' ')[0]; //Get first class name
			$(this).children(":first").addClass(currentClass + '-first-child');
		});
	}
	$(".searchform").firstChild();
});

The great thing about setting it up that way firstChild(), hoverOn() and hoverOff() are very reusable. Now, in the CSS we can simply add the ‘nav-item-hover’ or ‘searchform-first-child’ classes as additional selectors:

1
2
3
4
5
6
7
8
.nav-item:hover, <strong>.nav-item-hover</strong>{
	background:#FFFFFF;
	border: solid 3px #888;
}
.searchform:first-child, <strong>.searchform-first-child</strong>{
	background:#FFFFFF;
	border: solid 3px #888;
}

It’s not pretty, but it is valid and it works. I’ve got to say, though, that I sure am looking forward to the day we won’t have to bother with this stuff!

#9 – Manage Search Box Values

A popular effect is to fill a site’s search box with a value (like ‘search…’) and then use jQuery to clear the default value when the field receives focus, reverting if the field is empty when blurred. That is easily accomplished with a couple lines of jQuery:

1
2
3
4
5
6
7
8
9
$(function(){
	//set default value:
	$("#searchbox")
	  .val('search?');
	  .focus(function(){this.val('')})
	  .blur(function(){
		(this.val() === '')? this.val('search?') : null;
	  });
});

#10 – Create a Disappearing ‘Back-to-Top’ Link

The disappearing back-to-top link was inspired by Brian Cray. All you have to do is add a back-to-top link at the bottom of your content like normal, and then jQuery performs the magic:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
$(function(){
/* set variables locally for increased performance */
	var scroll_timer, 
		displayed = false,
		$message = $('#message a'),
		$window = $(window),
		top = $(document.body).children(0).position().top;
 
	/* react to scroll event on window */
	$window.scroll(function () {
		window.clearTimeout(scroll_timer);
		scroll_timer = window.setTimeout(function () { // use a timer for performance
			if($window.scrollTop() <= top) // hide if at the top of the page
			{
				displayed = false;
				$message.fadeOut(500);
			}
			else if(displayed == false) // show if scrolling down
			{
				displayed = true;
				$message.stop(true, true).show().click(function () { $message.fadeOut(500); });
			}
		}, 100);
	});
});

Brian also added some nice-looking CSS, which you could add as a css file or define in an object literal and apply it using jQuery.css(). Feel free to go check out his in-depth explanation if you want to learn more.

#11 – Easily Respond to Event Data

One of my favorite things about jQuery is its convenient remapping of event data, virtually eliminating cross-browser inconsitencies and making events much easier to respond to. jQuery passes an event parameter into all bound/triggered functions, which is commonly called e:

1
2
3
4
5
6
7
8
9
10
11
12
$(function() {
	//We can get X/Y coordinates on click events:
	$("a").click(function(<em>e</em>){
		var clickX = e.pageX;
		var clickY = e.pageX;
	});
 
	//Or detect which key was pressed:
	$("window").keypress(function(<em>e</em>){
		var keyPressed = e.which;
	});
});

You can check out the jQuery docs on this one to see all the possibilites, or view this keycode reference if you’d like to look up a certain key’s character code.

#12 – Encode HTML Entities

The first place I saw this mentioned was over at Debuggable, and I have to say they really came up with something good here. The idea is to produce a jQuery result similar to PHP’s htmlentities(). Check this out:

1
2
3
4
5
6
7
8
9
$(function(){
	var text = $("#someElement").text();
	var text2 = "Some <code> & such to encode";
	//you can define a string or get the text of an element or field
 
	var html = $(text).html();
	var html2 = $(text2).html();
	//Done - html and html2 now hold the encoded values!
});

#13 – Friendly Text Resizing

Originally mentioned at ShopDev, this is an excellent way to include some user-centricity in your code (allowing them to control the font-size):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$(function(){
  // Reset Font Size
  var originalFontSize = $('html').css('font-size');
    $(".resetFont").click(function(){
    $('html').css('font-size', originalFontSize);
  });
  // Increase Font Size
  $(".increaseFont").click(function(){
    var currentFontSize = $('html').css('font-size');
    var currentFontSizeNum = parseFloat(currentFontSize, 10);
    var newFontSize = currentFontSizeNum*1.2;
    $('html').css('font-size', newFontSize);
    return false;
  });
  // Decrease Font Size
  $(".decreaseFont").click(function(){
    var currentFontSize = $('html').css('font-size');
    var currentFontSizeNum = parseFloat(currentFontSize, 10);
    var newFontSize = currentFontSizeNum*0.8;
    $('html').css('font-size', newFontSize);
    return false;
  });
});

As I said, this is nice trick to know and adds some of that dynamic friendliness that people enjoy so much.

#14 – Open External Links in a New Window

This external links hack has been mentioned before at Cats Who Code, and although imperfect it’s a good way to open external links in new windows without causing validation errors in XHTML 1.0 Strict.

1
2
3
4
5
$(function(){
	$('a[rel$='external']').click(function(){
		this.target = "_blank";
	});
});

This works by grabbing all links with an external rel and adding a blank target. Same result, it’s just not hardcoded into the site.

#15 – Gracefully Degrading AJAX Navigation

AJAX navigation is great – but not for users and bots who can’t use it. The good news is, it’s possible to offer direct links to your content while still presenting AJAX functionality (to users who have that capability) by catching links before they go anywhere, returning false on them and loading the AJAX content instead. It could look like this:

1
2
3
4
5
6
$(function(){
	$("a").bind("click",function(){
		//Put your AJAX request code here
		return false;
	});
});

Of course, this is very basic, but it’s an essential facet to any AJAX navigation. You can also check out SpeckyBoy’s post on Easy-to-Use Free Ajax Navigation Solutions.

#16 – Create an Array of GET variables

Although this is not specifically a jQuery trick, it’s useful enough to be included here. Using GET variables in Javascript code doesn’t happen everyday, but when it does you’ll want to know a quick and efficient way to read them. All we have to do is get document.location.search and do some parsing on it:

1
2
3
4
5
6
7
8
9
10
 
var searchArray = document.location.search.substring(1).split("&");
//Take off the '?' and split into separate queries
 
//Now we'll loop through searchArray and create an associative array (object literal) called GET
var GET = []; 
for (searchTerm in searchArray){
	searchTerm.split("="); //Divide the searchTerm into property and value
	GET[searchTerm[0]] = searchTerm[1]; //Add property and value to the GET array
}

#17 – Partial Page Refresh Using load()

This excellent technique found at the Mediasoft Blog is way cool and very handy for creating a regularly updating dashboard/widget/etc. It works by using jQuery.load() to perform a AJAX request:

1
2
3
4
5
$(document).ready(function() {
	setInterval(function() {
		$("#content").load(location.href+" #content>*","");
	}, 5000);
});

Voila! It works – no iframes, meta refreshes or other such nonsense.

#18 – Skin with jQuery UI

If you’re going to write any jQuery plugins (I hope you have already!), you should know that a great way to add flexibility and elegance is to incorporate jQueryUI theming classes into any widgets/visible elements your plugin produces. The great thing about doing this is that it cuts or eliminates the css you have to provide with the plugin, and it adds a lot of customizability, too (which is one of the key factors in a successful plugin).

And the actual implementation is as simple as learning how the classes work and attaching them to plugin elements. I can see this would be especially great with plugins like form beautifiers and photo sliders, making it easy to keep a consistent look throughout a website or app. You can check out the Theming Reference here.

#19 – Include Other Scripts

Stylesheet switchers are nothing new, but adding other scripts with jQuery is something that is often overlooked. The advantage is twofold:

  1. It makes it easy to have lazy script loading.
  2. It also allows us to add scripts at runtime, which could be useful in a whole host of situations.

It’s as easy as using append() to add a new script to the head of the document:

1
2
3
4
5
6
7
8
$(function(){
	$("head").append("<script type='text/javascript' src='somescript.js'></script>");
 
	//Or, loading only when the slow stuff is ready:
	$("img,form").load(function(){
		$("head").append("<script type='text/javascript' src='somescript.js'></script>");
	});
});

#20 – Use Body Classes for Easy Styling

Do you want to save on code and keep your styling in the css file where it should be? Body classes (another great ideas suggested by Karl Swedberg) allow you to do that. In short, you can use jQuery to add a ‘JS’ class to the body element, which will enable you to set styles in your css that will only be applied if Javascript is enabled. For example:

1
2
3
$(document).ready(function() {
	$("body").addClass("JS");
});

For Javascript users the body now has a JS class, so in our CSS we can add styles like this:

ul.navigation{
	display:block;
}
.JS ul.navigation{
	display:none;
}

This gives us a great way to change styles based on whether or not Javascript is supported/enabled, and we can still keep the styling in the CSS file. Another interesting related use of this technique is to add browser classes to the body, enabling easy browser-specific styling. You can read more about that here.

#21 – Optimize Your Performance

Experienced coders don’t make clients wait – they write code that runs fast! There are several ways you can make your code run faster like:

  • Reference id’s rather than classes (id selection is native and therefore quicker)
  • Use for instead of each()
  • Limit DOM manipulation by adding elements in one big chunk rather than one at a time
  • Take advantage of event delegation
  • Link to Google’s jQuery copy rather than hosting your own – it’s faster and always up to date

Basically, it all boils down to not making jQuery do any more work than it has to (and using native abilites whenever possible). Giulio Bai wrote an excellent post on jQuery perfomance, if you’d like to dig in deeper.

#22 – Adapt Your Scripts to Work Cross-Browser – The Right Way

Thankfully, jQuery’s cross-browser compatibility really cuts down the need for browser hacks. Sometimes, though, it is good to be able to get information about the client, and we can do that cleanly and unobtrusively with jQuery.support:

1
2
3
4
5
//Does this client follow the W3C box model?
var boxModel = $.support.boxModel;
 
//Does this client support 'opacity'?
var opacity = $.support.opacity;

It’s definitely better practice to use feature-detection rather than browser sniffing, and this is a very efficient way to do it. Read more about jQuery.support’s properties here.

#23 – Configure jQuery to be Compatible with Other Libraries

We all find ourselves in situations where multiple libraries are needed, and because jQuery isn’t the only library that uses the $ alias, compatiblity issues sometimes pop up. Thankfully, this is easy to fix using jQuery’s noConflict(). You can even define a custom alias to replace the $:

1
2
3
4
var $j = jQuery.noConflict();
 
//Now you can use '$j' just like '$'
$j("div").hide();

An alternate technique is to wrap jQuery calls in an anonymous function and pass jQuery in as a parameter. Then you can use whatever alias you want, including the ‘$’. This is especially useful for plugin authoring:

1
2
3
4
5
(function($){
	$(document).ready(function(){
		//You can use normal jQuery syntax here
	});
})(jQuery);

#24 – Efficiently Store Element-Specific Information with data()

data() is probably one of the lesser used jQuery methods, although it certainly shouldn’t be. It allows us to attach/retrieve as much data as we want to DOM elements without misusing attributes, and is especially useful for more complex scripts. For example, Stefan Petre’s Colorpicker plugin uses data a lot because it’s tracking lots of fields and colors, converting rgb to hex, etc. Here’s are some examples of how data() works:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$(document).ready(function() {
	//Set status to 'unsaved'
    $("button:first").data("status", "unsaved");
 
	//Retrieve status
	var buttonStatus = $("button:first").data("status");
 
	//Change status, this time defining an object literal
	$("button:first").data("status", {saved : true, index : 1});
 
	//Retrieve status of index property
	var buttonStatusIndex = $("button:first").data("status").index;
 
	//Remove status data
	$("button:first").removeData("status");
});

I’m sure you can imagine the huge extent of possibilities this presents. Again, it’s worth reading the documentation if you haven’t used data() before.

#25 – Extend/Modify Existing jQuery Functions

Nobody says you can’t use the existing jQuery platform as a springboard for new ideas – and many have done just that using extend(), another wonderful jQuery method. The popular Easing plugin, for example, adds some animation variety by extending the easing object, an already existing object that is passed to animate() and others:

1
2
3
4
5
6
7
8
9
10
11
12
13
jQuery.extend({
	easing: {
		easein: function(x, t, b, c, d) {
			return c*(t/=d)*t + b; // in
		},
		easeinout: function(x, t, b, c, d) {
			if (t < d/2) return 2*c*t*t/(d*d) + b;
			var ts = t - d/2;
			return -2*c*ts*ts/(d*d) + 2*c*ts/d + c/2 + b;		
		},
		easeout: function(x, t, b, c, d) {
			return -c*t*t/(d*d) + 2*c*t/d + b;
		}...

By extending and improving jQuery’s default functionality, you can open up a whole new world of cool possibilities.

#26 – Reverse Engineer before() and after()

I always appreciated how jQuery provided append()/prepend() and appendTo()/prependTo(), enabling us to easily perform an append in either direction. I’ve wished, though, that a similar ability was provided with before() and after(). To change that, we can easily add two functions called putBefore() and putAfter() that will fulfill that purpose. Here’s how:

1
2
3
4
5
6
7
8
9
10
11
12
$(function(){
	jQuery.fn.putBefore = function(dest){
		return this.each(function(){
			$(dest).before($(this));
		});
	}
	jQuery.fn.putAfter = function(dest){
		return this.each(function(){
			$(dest).after($(this));
		});
	}
});

#27 – Add an isChildOf() Test

I’m sure we all have found ourselves in this situation – needing to know if an element is a descendant of another element. The good news is, with one line of code we can extend jQuery to allow this:

1
2
3
4
5
6
7
8
$(function(){
	jQuery.fn.isChildOf = function(b){return (this.parents(b).length > 0);};
 
	//Now we can evaluate like this:
	if ( $("li").isChildOf("ul") ){
		//Obviously, the li is a child of the ul so this code is executed
	}
});

Thanks to Dan Switzer II for this contribution!

#28 – Add Custom Selectors

This is another one that has been talked about a lot in the development community, so you may have this already figured out. If not, get ready because this will open some whole new windows for jQuery efficiency. The short story is, jQuery allows us to extend its expression object, which means we can add whatever custom selectors we want. For example, say we wanted to add a selector version of the isChildOf() method we wrote earlier:

1
2
3
4
5
6
7
8
9
10
$(function(){
	jQuery.extend(jQuery.expr[':'], {   
		'child-of' : function(a,b,c) {
			return (jQuery(a).parents(c[3]).length > 0);
		}   
	});
 
	//'child-of' is now a valid selector:
	$("li:child-of(ul.test)").css("background","#000");  
});

Debuggable has a great post on this one as well, if you’d like to read more about how the parameters work, etc.

#29 – Smooth Scrolling Without Plugin

Karl Swedberg posted this one a while back on the Learning jQuery site, and it is definitely worth a look. Of course, there are a couple of plugins to accomplish this (and they have more features), but I think the real value in this is the excercise of doing it yourself. Plus, look at how tiny it is:

1
2
3
4
5
6
7
8
9
10
11
12
13
$(document).ready(function() {
  $('a[href*=#]').click(function() {
    if (location.pathname.replace(/^\//,'') == this.pathname.replace(/^\//,'') 
      && location.hostname == this.hostname) {
       var $target = $(this.hash);
       $target = $target.length && $target || $('[name=' + this.hash.slice(1) +']');
       if ($target.length) {
           $target.ScrollTo(400);
           return false;
       }
    };
  });
});

#30 – Add Tabs without a Plugin

jQuery tabs are often covered but also often used, and like the scrolling trick above it’s important to know how to do these without a plugin. The first thing to do is write our markup, which should be perfectly presentable in the absence of Javascript:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<div class="widget">
	<h3>Popular</h3>
	<ul>
		<li>Item #1</li>
		<li>Item #2</li>
		<li>Item #3</li>
	</ul>
</div>
<div class="widget">
	<h3>Recent</h3>
	<ul>
		<li>Item #1</li>
		<li>Item #2</li>
		<li>Item #3</li>
	</ul>
</div>

With a bit of styling, this would look just fine, so we know our jQuery is going to degrade gracefully. Now we can write the code:

1
2
3
4
5
6
7
8
9
10
11
$(document).ready(function() {
	$("div.widget").hide().filter(":first").before("<ul class='tabs'></ul><div class='tab-content'></div>").add("div.widget").each(function(){
		$(this).find("ul").appendTo(".tab-content");
		$("ul.tabs").append("<li>" +$(this).find(":header:first").text()+ "</li>");
	});
	$("ul.tabs li").click(function(){
		$(this).addClass("active");
		$(".tab-content ul").slideUp().eq($("ul.tabs li").index(this)).slideDown();
	});
	$("ul.tabs li:first").click();
});

Of course, that’s just one way to do it. If you’d like to see some other approaches, see Extra Tuts’ jQuery Tabs Tutorials collection.

Wrapping Up

Thanks for reading, I hope you’ve enjoyed it! In case you’re all fired up on jQuery now, here are some links to more great tips n’ tricks collections:

As you can see, there are infinite opportunities for amazing innovation with jQuery. So keep experimenting, keep trying things, keep thinking, “What if?” You could be the next jQuery supergeek!

CTE公用表表达式和With用法总结

Standard

CTE(Common Table Expression) 公用表表达式,它是在单个语句的执行范围内定义的临时结果集,只在查询期间有效。它可以自引用,也可在同一查询中多次引用,实现了代码段的重复利用。

CTE最大的好处是提升T-Sql代码的可读性,可以更加优雅简洁的方式实现递归等复杂的查询。

CTE可用于:
⒈ 创建递归查询,这个应该是CTE最好用的地方
⒉ 在同一语句中多次引用生成的表
3. 减少子查询和表变量,提高执行效率

CTE优点:
1. 使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。同时,CTE要比表变量的效率高得多。
2. 可以用来定义一个SQL片断,该SQL片断会被整个SQL语句所用到。有的时候,是为了让SQL语句的可读性更高些,也有可能是在UNION ALL的不同部分,作为提供数据的部分。
3. 查询可以分为单独块、简单块、逻辑生成块。之后,这些简单块可用于生成更复杂的临时 CTE,直到生成最终结果集。

下面是CTE的语法:

WITH cte_name ( column_name [,...n] )
AS
(
    CTE_query_definition –- Anchor member is defined.
)

 

使用示例
1. 查询临时结果集

复制代码
WITH cte(CategoryID,CategoryName,ParentID,CategoryLevel)
AS (
  SELECT CategoryID
      ,CategoryName
      ,ParentID
      ,CategoryLevel
  FROM Category(NOLOCK)
  WHERE Status = 1 and parentid = 23
)
select * from cte;
复制代码

注意: 1.使用CTE的SQL语句应紧跟在相关的CTE后面。
2.多重CTE中间用逗号,分隔。
3.可以被紧跟着的一条SQL语句所使用多次,但不能被紧跟着的多条SQL语句使用。

2. 创建递归查询

复制代码
WITH cte(CategoryID ,CategoryName,ParentID,CategoryLevel)
AS (
  SELECT CategoryID
      ,CategoryName
      ,ParentID
      ,CategoryLevel
      FROM Category(NOLOCK)
  WHERE Status= 1 and parentid in (21,22,23,25,26)
  UNION ALL
  SELECT t.CategoryID
      ,t.CategoryName
      ,t.ParentID
      ,t.CategoryLevel
  FROM Category(NOLOCK) AS t
  INNER JOIN cte AS c ON t.parentid = c.CategoryID where Status= 1
)
select * from cte;
复制代码

 

3. cte结果集和数据表关联

复制代码
WITH cte(CategoryID,CategoryName,ParentID,CategoryLevel)
AS (
  SELECT CategoryID
      ,CategoryName
      ,ParentID
      ,CategoryLevel
  FROM Category(NOLOCK)
  WHERE Status = 1 and parentid = 23
)
select p.ProductId,p.ProductName,c.CategoryID,c.CategoryName,c.CategoryLevel 
from product p(NOLOCK)
inner join cte c(NOLOCK) on p.CategoryId=c.CategoryID
复制代码

 

浅谈 PHP 中的多种加密技术及代码示例

Standard

原文:http://www.cnblogs.com/nixi8/p/4926689.html

同样是一道面试答错的问题,面试官问我非对称加密算法中有哪些经典的算法? 当时我愣了一下,因为我把非对称加密与单项散列加密的概念弄混淆了,所以更不用说什么非对称加密算法中有什么经典算法,结果当然也让面试官愣了一下,所以今天就花点时间说说PHP中的信息加密技术

信息加密技术的分类

单项散列加密技术(不可逆的加密)

属于摘要算法,不是一种加密算法,作用是把任意长的输入字符串变化成固定长的输出串的一种函数

MD5

string md5 ( string $str [, bool $raw_output = false ] ); //MD5加密,输入任意长度字符串返回一个唯一的32位字符

md5()为单向加密,没有逆向解密算法,但是还是可以对一些常见的字符串通过收集,枚举,碰撞等方法破解;所以为了让其破解起来更麻烦一些,所以我们一般加一点盐值(salt)并双重MD5;

md5(md5($password).'sdva'); 

sdva就是盐值,该盐值应该是随机的,比如md5常用在密码加密上,所以在注册的时候我会随机生成这个字符串,然后通过上面的方法来双重加密一下;

Crypt

很少看到有人用这个函数,如果要用的话有可能是用在对称或非对称的算法里面,了解一下既可;

string crypt ( string $str [, string $salt ] ) //第一个为需要加密的字符串,第二个为盐值(就是加密干扰值,如果没有提供,则默认由PHP自动生成);返回散列后的字符串或一个少于 13 字符的字符串,后者为了区别盐值。
<?php
$password='testtest.com';
echo crypt($password);
//输出:$1$DZ3.QX2.$CQZ8I.OfeepKYrWp0oG8L1
/*第二个$与第三个$之间的八个字符是由PHP生成的,每刷新一次就变一次
*/
echo "<hr>";

echo crypt($password,"testtest");
//输出:tesGeyALKYm3A
//当我们要加自定义的盐值时,如例子中的testtest作为第二个参数直接加入, 超出两位字符的会截取前两位
echo "<hr>";

echo  crypt($password,'$1$testtest$');
//输出:$1$testtest$DsiRAWGTHiVH3O0HSHGoL1
/*crypt加密函数有多种盐值加密支持,以上例子展示的是MD5散列作为盐值,该方式下
盐值以$1$$的形式加入,如例子中的testtest加在后两个$符之间,
超出八位字符的会截取前八位,总长为12位;crypt默认就是这种形式。
*/
echo "<hr>";
//crypt还有多种盐值加密支持,详见手册

Sha1加密:

string sha1 ( string $str [, bool $raw_output = false ]); //跟md5很像,不同的是sha1()默认情况下返回40个字符的散列值,传入参数性质一样,第一个为加密的字符串,第二个为raw_output的布尔值,默认为false,如果设置为true,sha1()则会返回原始的20 位原始格式报文摘要
<?php
$my_intro="zhouxiaogang";
echo sha1($my_intro); // b6773e8c180c693d9f875bcf77c1202a243e8594
echo "<hr>";
//当然,可以将多种加密算法混合使用
echo md5(sha1($my_intro));
//输出:54818bd624d69ac9a139bf92251e381d
//这种方式的双重加密也可以提高数据的安全性

非对称加密

非对称加密算法需要两个密钥来进行加密和解密,这两个秘钥是公开密钥(public key,简称公钥)和私有密钥(private key,简称私钥);

1b4c510fd9f9d72a79ac165bd72a2834359bbbaf.jpg-182.2kB

如图所示,甲乙之间使用非对称加密的方式完成了重要信息的安全传输。

  1. 乙方生成一对密钥(公钥和私钥)并将公钥向其它方公开。
  2. 得到该公钥的甲方使用该密钥对机密信息进行加密后再发送给乙方。
  3. 乙方再用自己保存的另一把专用密钥(私钥)对加密后的信息进行解密。乙方只能用其专用密钥(私钥)解密由对应的公钥加密后的信息。

在传输过程中,即使攻击者截获了传输的密文,并得到了乙的公钥,也无法破解密文,因为只有乙的私钥才能解密密文
同样,如果乙要回复加密信息给甲,那么需要甲先公布甲的公钥给乙用于加密,甲自己保存甲的私钥用于解密。

在非对称加密中使用的主要算法有:RSA、Elgamal、背包算法、Rabin、D-H、ECC(椭圆曲线加密算法)等。其中我们最见的算法是RSA算法

以下是从网上摘抄的一段PHP通过openssl实现非对称加密的算法

<?php
/**
 * 使用openssl实现非对称加密
 * @since 2010-07-08
 */
class Rsa {
    /**
     * private key
     */
    private $_privKey;
    /**
     * public key
     */
    private $_pubKey;
    /**
     * the keys saving path
     */
    private $_keyPath;
    /**
     * the construtor,the param $path is the keys saving path
     */
    public function __construct($path) {
        if (empty($path) || !is_dir($path)) {
            throw new Exception('Must set the keys save path');
        }
        $this->_keyPath = $path;
    }
    /**
     * create the key pair,save the key to $this->_keyPath
     */
    public function createKey() {
        $r = openssl_pkey_new();
        openssl_pkey_export($r, $privKey);
        file_put_contents($this->_keyPath . DIRECTORY_SEPARATOR . 'priv.key', $privKey);
        $this->_privKey = openssl_pkey_get_public($privKey);
        $rp = openssl_pkey_get_details($r);
        $pubKey = $rp['key'];
        file_put_contents($this->_keyPath . DIRECTORY_SEPARATOR . 'pub.key', $pubKey);
        $this->_pubKey = openssl_pkey_get_public($pubKey);
    }
    /**
     * setup the private key
     */
    public function setupPrivKey() {
        if (is_resource($this->_privKey)) {
            return true;
        }
        $file = $this->_keyPath . DIRECTORY_SEPARATOR . 'priv.key';
        $prk = file_get_contents($file);
        $this->_privKey = openssl_pkey_get_private($prk);
        return true;
    }
    /**
     * setup the public key
     */
    public function setupPubKey() {
        if (is_resource($this->_pubKey)) {
            return true;
        }
        $file = $this->_keyPath . DIRECTORY_SEPARATOR . 'pub.key';
        $puk = file_get_contents($file);
        $this->_pubKey = openssl_pkey_get_public($puk);
        return true;
    }
    /**
     * encrypt with the private key
     */
    public function privEncrypt($data) {
        if (!is_string($data)) {
            return null;
        }
        $this->setupPrivKey();
        $r = openssl_private_encrypt($data, $encrypted, $this->_privKey);
        if ($r) {
            return base64_encode($encrypted);
        }
        return null;
    }
    /**
     * decrypt with the private key
     */
    public function privDecrypt($encrypted) {
        if (!is_string($encrypted)) {
            return null;
        }
        $this->setupPrivKey();
        $encrypted = base64_decode($encrypted);
        $r = openssl_private_decrypt($encrypted, $decrypted, $this->_privKey);
        if ($r) {
            return $decrypted;
        }
        return null;
    }
    /**
     * encrypt with public key
     */
    public function pubEncrypt($data) {
        if (!is_string($data)) {
            return null;
        }
        $this->setupPubKey();
        $r = openssl_public_encrypt($data, $encrypted, $this->_pubKey);
        if ($r) {
            return base64_encode($encrypted);
        }
        return null;
    }
    /**
     * decrypt with the public key
     */
    public function pubDecrypt($crypted) {
        if (!is_string($crypted)) {
            return null;
        }
        $this->setupPubKey();
        $crypted = base64_decode($crypted);
        $r = openssl_public_decrypt($crypted, $decrypted, $this->_pubKey);
        if ($r) {
            return $decrypted;
        }
        return null;
    }
    public function __destruct() {
        @fclose($this->_privKey);
        @fclose($this->_pubKey);
    }
}
//以下是一个简单的测试demo,如果不需要请删除
$rsa = new Rsa('ssl-key');
//私钥加密,公钥解密
echo 'source:我是老鳖<br />';
$pre = $rsa->privEncrypt('我是老鳖');
echo 'private encrypted:<br />' . $pre . '<br />';
$pud = $rsa->pubDecrypt($pre);
echo 'public decrypted:' . $pud . '<br />';
//公钥加密,私钥解密
echo 'source:干IT的<br />';
$pue = $rsa->pubEncrypt('干IT的');
echo 'public encrypt:<br />' . $pue . '<br />';
$prd = $rsa->privDecrypt($pue);
echo 'private decrypt:' . $prd;
?>  

对称加密算法

对称加密(也叫私钥加密)指加密和解密使用相同密钥的加密算法。有时又叫传统密码算法,就是加密密钥能够从解密密钥中推算出来,同时解密密钥也可以从加密密钥中推算出来。而在大多数的对称算法中,加密密钥和解密密钥是相同的,所以也称这种加密算法为秘密密钥算法或单密钥算法。它要求发送方和接收方在安全通信之前,商定一个密钥。对称算法的安全性依赖于密钥,泄漏密钥就意味着任何人都可以对他们发送或接收的消息解密,所以密钥的保密性对通信性至关重要。

对称加密的常用算法有: DES算法,3DES算法,TDEA算法,Blowfish算法,RC5算法,IDEA算法

在PHP中也有封装好的对称加密函数

Urlencode/Urldecode

string urlencode ( string $str ) 
/*
1. 一个参数,传入要加密的字符串(通常应用于对URL的加密)
2. urlencode为双向加密,可以用urldecode来加密(严格意义上来说,不算真正的加密,更像是一种编码方式)
3. 返回字符串,此字符串中除了 -_. 之外的所有非字母数字字符都将被替换成百分号(%)后跟两位十六进制数,空格则编码为加号(+)。
*/

通过Urlencode函数解决链接中带有&字符引起的问题:

<?php
$pre_url_encode="zhougang.com?username=zhougang&password=zhou"; //在实际开发中,我们很多时候要构造这种URL,这是没有问题的
$url_decode    ="zhougang.com?username=zhou&gang&password=zhou";//但是这种情况下用$_GET()来接受是会出问题的;
/*
Array
(
  [username] => zhou
  [gang] => 
  [password] => zhou
)
 */


//如下解决问题:
$username="zhou&gang";
$url_decode="zhougang.com?username=".urlencode($username)."&password=zhou";
?>

常见的urlencode()的转换字符

?=> %3F
= => %3D
% => %25
& => %26
\ => %5C

base64

string base64_decode ( string $encoded_data )
  1. base64_encode()接受一个参数,也就是要编码的数据(这里不说字符串,是因为很多时候base64用来编码图片)
  2. base64_encode()为双向加密,可用base64_decode()来解密
$data=file_get_contents($filename);
echo base64_encode($data);
/*然后你查看网页源码就会得到一大串base64的字符串,
再用base64_decode()还原就可以得到图片。这也可以作为移动端上传图片的处理方案之一(但是不建议这样做哈)
*/

严格的来说..这两个函数其实不算是加密,更像是一种格式的序列化

以下是我们PHP程序中常用到的对称加密算法

discuz经典算法

<?php
function authcode($string, $operation = 'DECODE', $key = '', $expiry = 0) {   
    // 动态密匙长度,相同的明文会生成不同密文就是依靠动态密匙   
    $ckey_length = 4;   
       
    // 密匙   
    $key = md5($key ? $key : $GLOBALS['discuz_auth_key']);   
       
    // 密匙a会参与加解密   
    $keya = md5(substr($key, 0, 16));   
    // 密匙b会用来做数据完整性验证   
    $keyb = md5(substr($key, 16, 16));   
    // 密匙c用于变化生成的密文   
    $keyc = $ckey_length ? ($operation == 'DECODE' ? substr($string, 0, $ckey_length): 
substr(md5(microtime()), -$ckey_length)) : '';   
    // 参与运算的密匙   
    $cryptkey = $keya.md5($keya.$keyc);   
    $key_length = strlen($cryptkey);   
    // 明文,前10位用来保存时间戳,解密时验证数据有效性,10到26位用来保存$keyb(密匙b), 
//解密时会通过这个密匙验证数据完整性   
    // 如果是解码的话,会从第$ckey_length位开始,因为密文前$ckey_length位保存 动态密匙,以保证解密正确   
    $string = $operation == 'DECODE' ? base64_decode(substr($string, $ckey_length)) :  
sprintf('%010d', $expiry ? $expiry + time() : 0).substr(md5($string.$keyb), 0, 16).$string;   
    $string_length = strlen($string);   
    $result = '';   
    $box = range(0, 255);   
    $rndkey = array();   
    // 产生密匙簿   
    for($i = 0; $i <= 255; $i++) {   
        $rndkey[$i] = ord($cryptkey[$i % $key_length]);   
    }   
    // 用固定的算法,打乱密匙簿,增加随机性,好像很复杂,实际上对并不会增加密文的强度   
    for($j = $i = 0; $i < 256; $i++) {   
        $j = ($j + $box[$i] + $rndkey[$i]) % 256;   
        $tmp = $box[$i];   
        $box[$i] = $box[$j];   
        $box[$j] = $tmp;   
    }   
    // 核心加解密部分   
    for($a = $j = $i = 0; $i < $string_length; $i++) {   
        $a = ($a + 1) % 256;   
        $j = ($j + $box[$a]) % 256;   
        $tmp = $box[$a];   
        $box[$a] = $box[$j];   
        $box[$j] = $tmp;   
        // 从密匙簿得出密匙进行异或,再转成字符   
        $result .= chr(ord($string[$i]) ^ ($box[($box[$a] + $box[$j]) % 256]));   
    }   
    if($operation == 'DECODE') {  
        // 验证数据有效性,请看未加密明文的格式   
        if((substr($result, 0, 10) == 0 || substr($result, 0, 10) - time() > 0) &&  
substr($result, 10, 16) == substr(md5(substr($result, 26).$keyb), 0, 16)) {   
            return substr($result, 26);   
        } else {   
            return '';   
        }   
    } else {   
        // 把动态密匙保存在密文里,这也是为什么同样的明文,生产不同密文后能解密的原因   
        // 因为加密后的密文可能是一些特殊字符,复制过程可能会丢失,所以用base64编码   
        return $keyc.str_replace('=', '', base64_encode($result));   
    }   
} 

加解密函数encrypt()

<?php
//$string:需要加密解密的字符串;$operation:判断是加密还是解密,E表示加密,D表示解密;$key:密匙
function encrypt($string,$operation,$key=''){ 
    $key=md5($key); 
    $key_length=strlen($key); 
      $string=$operation=='D'?base64_decode($string):substr(md5($string.$key),0,8).$string; 
    $string_length=strlen($string); 
    $rndkey=$box=array(); 
    $result=''; 
    for($i=0;$i<=255;$i++){ 
           $rndkey[$i]=ord($key[$i%$key_length]); 
        $box[$i]=$i; 
    } 
    for($j=$i=0;$i<256;$i++){ 
        $j=($j+$box[$i]+$rndkey[$i])%256; 
        $tmp=$box[$i]; 
        $box[$i]=$box[$j]; 
        $box[$j]=$tmp; 
    } 
    for($a=$j=$i=0;$i<$string_length;$i++){ 
        $a=($a+1)%256; 
        $j=($j+$box[$a])%256; 
        $tmp=$box[$a]; 
        $box[$a]=$box[$j]; 
        $box[$j]=$tmp; 
        $result.=chr(ord($string[$i])^($box[($box[$a]+$box[$j])%256])); 
    } 
    if($operation=='D'){ 
        if(substr($result,0,8)==substr(md5(substr($result,8).$key),0,8)){ 
            return substr($result,8); 
        }else{ 
            return''; 
        } 
    }else{ 
        return str_replace('=','',base64_encode($result)); 
    } 
}
?>
————————-END————————-