libname sasfile "Folder";
data sasfile.test;
input a b c d;
datalines;
1 26 31 1
1 28 28 2
1 30 31 3
2 32 31 4
2 34 29 5
;
run;
Sunday, March 27, 2016
Thursday, March 17, 2016
控制美国经济的主要金融机构（部分） - May 11th, 2005
1.Fidelity Management & Research Corp （美国富达管理研究公司[音译的”富达”有时也意译作”忠诚”]，波士顿，管理资产约1.3万亿美元）
2.Barclays Bank Plc （英国巴克莱银行，是英国最大的金融机构之一，仅其旗下的全球投资业务部[Barclays Global Investors]管理的资产就达7000亿英镑）
3.State Street Corporation （美国道富公司,美国乃至全球最大的基金，总部位于美国波士顿，保管的资产达9.5万亿美元，管理的资产约1.4万亿美元）
4.Capital Research and Management Company （资产研究与管理公司,美国加州洛杉矶,管理资产约5000亿美元）
5.U.S. Bancorp （美国银行公司，美国明尼苏达州明尼阿波利斯，资产1980亿美元）
6.Citigroup Inc. （花旗集团公司，美国纽约派克大街，总资产1.484万亿美元）
7.Morgan Stanley （摩根斯坦利，美国纽约百老汇大街，管理资产约5760亿美元）
8.Deutsche Bank （德意志银行，德国法兰克福，总资产8400亿欧元）
9.JP Morgan Chase & Company （JP摩根大通公司，美国纽约派克大街，资产约1.15万亿美元）
10.Vanguard Group, Inc. （先锋集团公司, 管理资产约8000亿美元，曾经的基金两巨头之一，另一个是”富达”）
11.Northern Trust Corporation （北方信托公司，投资管理资产约6000亿美元）
12.AXA （法国安盛保险集团，法国巴黎，管理资产达8690亿欧元）
13.Putnam Investment Management （普南投资管理公司，美国，管理资产1940亿美元）
14.Mellon Financial Corporation （梅隆金融公司，美国宾夕法尼亚州匹兹堡[ Pittsburgh, Pennsylvania] 托管理及管理的总资产达4万亿美元，其中管理的资产为7290亿美元）
15.Wellington Management Company （惠灵顿管理公司，管理资产约5000亿美元）
16.TIAA-CREF （Teachers Insurance and Annuity Association - College Retirement Equities Fund，全美教师保险及年金协会-大学退休职工平衡基金，管理的资金也达数千亿美元）
17.Bank of America Corporation （美洲银行公司，美国北卡罗莱纳州夏洛特，总资产约1.1万亿美元，是一个一直以来在美国能与”一哥”花旗抗衡的大金融集团）
18.Brandes Investment Partners, LP （布朗德斯投资合伙公司, or 布兰帝投资伙伴基金，美国加州圣迭戈，管理资产大约为1000亿美元）
19.Southeastern Asset Management Inc. （该公司为长叶松基金[Longleaf Partners Funds，也称长叶基金，美国罗得岛州首府普罗维登斯]的投资顾问公司，这个基金管理的资产约300亿美元）
20.Merrill Lynch （美林公司，美国纽约维西大街[Vesey Street, New York, NY]，总客户资产约1.6万亿美元，其中管理的资产达5010亿美元）
21.Fayez Sarofim & Co. （法耶兹-沙罗菲公司，管理资产哟420亿美元）
22.Berkshire Hathaway, Inc （伯克希尔-哈撒威公司[“股神”巴菲特管理的公司]，美国内布拉斯加州奥马哈市基维特广场[Kiewit Plaza ,Omaha, NE]总资产约1900亿美元）
23.Lord Abbett & Co. （罗艾公司）
24.Marsico Capital Management （马尔西科资产管理公司，已被美洲银行[Bank of America]收购）
25.Janus Capital Group （骏利资产管理集团, 也称杰能士资本公司）
26.SunTrust Banks （美国太阳信用银行）
27.T.Rowe Price Associates （罗普合伙公司，因创始人Thomas Rowe Price而得名，管理的资产约2500亿美元）
28.Amvescap Plc （景顺集团）
29.Legg Mason Inc. （雷格-梅森投资公司）
30.Dodge & Cox Inc （道奇&考克斯）
31.Montag & Caldwell, Inc. （为ABN AMRO[荷兰银行]旗下投资管理公司）
32.Harris Associates L.P. （哈里斯合伙公司）
33.Goldman Sachs Group Inc （高盛公司
Sunday, March 6, 2016
Javascript OBJ Example
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<script language="javascript" type="text/javascript">
</script>
function bindBoxes(_selector) {
var data = new dataReader();
var _hspt = new objHospitalInfor();
data.Scr2_Obj_getHsptInfor();
_hspt.HospitalInfor = data.ArrayValue;
_hspt.init();
var _aspInput = new AspInputObj();
_aspInput.input = data.Scr2_Obj_getAspInputArray();
_aspInput.init();
_aspInput.bindEvent();
var aspTag = _aspInput.htmlTag;
var hipDrugTag = new HipDragPgTagObj();
hipDrugTag.init();
var construct = new constructsSoldPgTag();
construct.init();
var purchase = new PurchasePgTag();
purchase.init();
var imp = new ImpactSeqPgTag();
imp.init();
var tag = "";
tag += "<div><div class='ui-grid-b'>";
tag += "<div class='ui-block-a' style='border-right: 1px solid #d3d3d3; border-top: 1px solid #d3d3d3; '>";
tag += _hspt.htmlTag;
tag += "</div>";
tag += "<div class='ui-block-b' style='border-right: 1px solid #d3d3d3; border-top: 1px solid #d3d3d3;'>";
tag += aspTag;
tag += "</div>";
tag += "<div class='ui-block-c' style='border-left: 0px solid #d3d3d3; border-top: 1px solid #d3d3d3; '>";
tag += hipDrugTag.htmlTag;
tag += "</div></div>";
tag += "<div><div class='ui-grid-b'>";
tag += "<div class='ui-block-a' style='border-right: 1px solid #d3d3d3; border-top: 1px solid #d3d3d3; '>";
tag += construct.htmlTag;
tag += "</div>";
tag += "<div class='ui-block-b' style='border-right: 1px solid #d3d3d3; border-top: 1px solid #d3d3d3;'>";
tag += purchase.htmlTag;
tag += "</div>";
tag += "<div class='ui-block-c' style='border-left: 0px solid #d3d3d3; border-top: 1px solid #d3d3d3; '>";
tag += imp.htmlTag;
tag += "</div>";
tag += "</div>"; // end grid
tag += "</div>"; // end container
_selector.html(tag);
}
var HipChartObj = function () {
var _chart = {};
_chart.RenderTo = "hipChart";
_chart.title = "";
_chart.getTitle = function () {
var sub_titile1 = "Hip ASP as a Percentage of"; /// todo , get
var sub_titile2 = " DRG 470 Reimbursement"; /// todo , get
_chart.titile = sub_titile1 + sub_titile2;
return _chart.titile;
};
_chart.input = {
xAxis_categories : ["2010", "2011", "2012", "2013"]
};
_chart.getInputData = function () {
var _input = {};
_input.xAxis_categories = ["2010", "2011", "2012", "2013"]; // ["Todo", "Todo", "Todo", "Todo"]; // todo
_chart.input = _input;
return _input;
};
_chart.init = function (_renderTo) {
_chart.title = _chart.getTitle();
_chart.input = _chart.getInputData();
_chart.RenderTo = _renderTo;
};
_chart.Draw = function () {
var chart = new Highcharts.Chart({
chart : {
type : 'line',
renderTo : _chart.RenderTo
},
title : {
text : _chart.title,
x : -20 //center
},
subtitle : {
text : '',
x : -20
},
xAxis : {
categories : _chart.input.xAxis_categories
},
yAxis : [{ // Primary yAxis
labels : {
format : '{value}°C',
style : {
color : '#89A54E'
}
},
title : {
text : 'Reimbursement',
style : {
color : '#89A54E'
}
},
id : 1
}, { // Secondary yAxis
title : {
text : 'Hip ASP as Percent',
style : {
color : '#4572A7'
}
},
labels : {
format : '{value} mm',
style : {
color : '#4572A7'
},
formatter : function () {
return (this.value) * 100 + '%';
}
},
id : 2,
opposite : true
}
],
tooltip : {
valuePrefix : '$',
enabled : true,
formatter : function () {
return '<b>' + this.series.name + '</b><br/>' +
this.x + ': ' + '$' + this.y;
}
},
plotOptions : {
line : {
dataLabels : {
enabled : true
},
enableMouseTracking : true
}
},
legend : {
/* layout: 'Horizontal',*/
align : 'center',
verticalAlign : 'bottom',
borderWidth : 0
},
series : [{
name : 'Reimbursement',
data : [12239, 2520, 12578]
}, {
name : 'New York',
yAxis : 1,
data : [0.69, 0.20, 0.38]
}
]
});
}
return _chart;
}
var objHospitalInfor = function () {
var _ret = {};
_ret.HospitalInfor = null;
_ret.hsptInforTitle = ["HospitalName", "Addres", "City, State", "CustomerId", "ProviderId"];
_ret.setDefaultHospitalInfor = function () {
var _infor = new Array();
_infor["HospitalName"] = "Georgetown Hospital";
_infor["Addres"] = "606 606 Black River Rd Drawer 1718";
_infor["CityState"] = "Georgetown, SC";
_infor["CustomerId"] = "200710";
_infor["ProviderId"] = "420020";
_infor["Beds"] = "131";
_infor["GrossRevenue"] = "$283,921,756";
return _infor;
};
_ret.init = function () {
var _tag = "";
if (_ret.HospitalInfor == null) {
_ret.HospitalInfor = _ret.setDefaultHospitalInfor();
var hsptInfor = _ret.HospitalInfor;
for (var item in hsptInfor) {
_tag += "<p class='" + _ret.paraCss + "'><span class='" + _ret.titleCss + "'>" + item + ": " + "</span>";
_tag += "<span class='" + _ret.contentCss + "'>" + hsptInfor[item] + "</span></p>";
}
} else {
for (var i = 0; i < _ret.HospitalInfor.length; i++) {
_tag += "<p class='" + _ret.paraCss + "'><span class='" + _ret.titleCss + "'>" + _ret.hsptInforTitle[i] + ": " + "</span>";
_tag += "<span class='" + _ret.contentCss + "'>" + _ret.HospitalInfor[i] + "</span></p>";
}
}
_ret.htmlTag = _tag;
return _tag;
}
_ret.titleCss = "text-font-sansserif";
_ret.contentCss = "text-font-serif";
_ret.paraCss = "text-para-small";
_ret.htmlTag = null;
return _ret;
}
var CUST_ID_LIST = [{
id : 1,
custName : "100038, HOSPITAL 001"
}, {
id : 2,
custName : "100095, HOSPITAL 002"
}, {
id : 3,
custName : "100134, HOSPITAL 003"
}, {
id : 4,
custName : "100138, HOSPITAL 004"
}, {
id : 5,
custName : "100155, HOSPITAL 005"
}, {
id : 6,
custName : "100178, HOSPITAL 006"
}, {
id : 7,
custName : "100187, HOSPITAL 007"
}, {
id : 8,
custName : "100189, HOSPITAL 008"
}, {
id : 9,
custName : "100200, HOSPITAL 009"
}, {
id : 10,
custName : "100217, HOSPITAL 010"
}, {
id : 11,
custName : "100232, HOSPITAL 011"
}, {
id : 12,
custName : "100257, HOSPITAL 012"
}
];
var chart = null;
function BindViewDataPage() {
var _container = $("#view_data_container");
var newDiv = "<div id='veiw-Data-Page-Container'> </div>";
_container.prepend(newDiv);
PopulateTags_viewData();
}
function PopulateTags_viewData() {
_selector = $('#veiw-Data-Page-Container');
bindBoxes(_selector);
}
var AspInputObj = function () {
var _ret = {};
_ret.input = ["2010", "2011", "2012", "2013", "2014"];
_ret.inputTitle = ["2010", "2011", "2012", "2013", "2014"];
_ret.init = function () {
var asp = [];
var asp0 = "2010";
asp1 = "2011";
asp2 = "2012";
asp3 = "2013";
asp4 = "2014";
asp[0] = asp0; // todo: get the value from model
asp[1] = asp1;
asp[2] = asp2;
asp[3] = asp3;
asp[4] = asp4; // todo: get the value from model
_ret.input = asp;
_ret.htmlTag = _ret.getHtmlTag();
}; // get the init value
_ret.htmlTag = null;
_ret.getHtmlTag = function () {
var tag = "";
tag += "<div class='text-box-title'> Display Selection and ASP Input </div>";
tag += "<div> </div>";
tag += "<div style='text-aligh: left;' class='text-font-serif'> ASP Input </div>";
tag += "<div>"; // start table
tag += "<table><tr>";
tag += "<td><span class='text-lower text-font-serif'>ASP</span></td>";
for (var i = 0; i < 5; i++) {
tag += "<td><label for='" + "Asp_Input" + i.toString() + "'>" + "<span class='text-font-serif'>" + _ret.inputTitle[i] + "</span></label>";
tag += "<input type='text' class='input-text-box' id='Asp_Input" + i.toString() + "'/></td>";
}
tag += "</tr></table>";
tag += "</div>"; // end table
return tag;
};
_ret.bindEvent = function () {
var _sel = $('#Asp_Input1');
_sel.change(function () {
var val = _sel.val();
alert("changed" + val);
});
}
_ret.updateData = function () {}; // update year data
return _ret;
}
function populateChart() {
var _hipChart = new HipChartObj();
_hipChart.init("hipChart");
_hipChart.Draw();
var _aspChart = new AspChartObj()
_aspChart.init("aspChart");
_aspChart.Draw();
}
var AspChartObj = function () {
var _chart = {};
_chart.RenderTo = "testBarChart";
_chart.title = "Percentage Change of Hip ASP and DRG 470 Reimbursement Over Time";
_chart.getTitle = function () {
var sub_titile1 = "Percentage Change of Hip ASP and DRG 470 Reimbursement Over Time"; /// todo , get
var sub_titile2 = ""; /// todo , get
_chart.titile = sub_titile1 + sub_titile2;
return _chart.titile;
};
_chart.input = {
xAxis_categories : ["2010", "2011", "2012", "2013"]
};
_chart.getInputData = function () {
var _input = {};
_input.xAxis_categories = ["2010", "2011", "2012", "2013"]; // ["Todo", "Todo", "Todo", "Todo"]; // todo
_chart.input = _input;
return _input;
};
_chart.init = function (_renderTo) {
_chart.title = _chart.getTitle();
_chart.input = _chart.getInputData();
_chart.RenderTo = _renderTo;
};
_chart.Draw = function () {
var chart = new Highcharts.Chart({
chart : {
renderTo : _chart.RenderTo,
type : 'column'
},
title : {
text : _chart.title
},
xAxis : {
categories : ['2010-10', '2011-11', '2012-12', '2013-03']
},
yAxis : {
title : {
text : ' Percent',
style : {
color : '#4572A7'
}
},
labels : {
format : '{value} mm',
style : {
color : '#4572A7'
},
formatter : function () {
return (this.value) * 100 + '%';
}
},
opposite : false
},
credits : {
enabled : false
},
legend : {
/* layout: 'Horizontal',*/
align : 'center',
verticalAlign : 'bottom',
borderWidth : 0
},
series : [{
name : 'Knee ASP',
data : [0.538, 0.50, -1, 0.2]
}, {
name : 'Drug 470',
data : [3, 4, 4, -2]
}
]
});
}
return _chart;
}
var HipDragPgTagObj = function () {
var _ret = {};
_ret.input = ["-", "-"]; // get ASP and DRG value
_ret.title = "Percentage Change of Hip ASP and DRG 470 Over Time";
_ret.init = function () {
// get input from model
_ret.htmlTag = _ret.getHtmlTag();
};
_ret.htmlTag = "";
_ret.getHtmlTag = function () {
var dd1 = new selectObj();
var dd2 = new selectObj();
dd1.init();
dd2.init();
var tag = "";
tag += "<div class='text-box-title'>" + _ret.title + "</div>";
tag += "<div>"; // start table
tag += "<table class='constuct-table'>";
tag += "<tr><td style='width:70%'>" + dd1.htmlTag + "</td><td style='width:30%'>" + "<span class='text-font-serif'>ASP: </span> " + _ret.input[0] + "</td></tr>";
tag += "<tr><td style='width:70%'>" + dd2.htmlTag + "</td><td style='width:30%'>" + "<span class='text-font-serif'>DRG: </span>" + _ret.input[1] + "</td></tr>";
tag += "</table>";
tag += "</div>"; // end table
// tag += "</div>"; // end HipDragObj
return tag;
}
return _ret;
}
var selectObj = function () {
var _ret = {};
_ret.title = "Year1";
_ret.divTag = "ddl_Year1";
_ret.input = [2011, 2012, 2013];
_ret.init = function (_divTag, _title, _inputArray) {
// get input
_ret.divTag = (_divTag != "" && _divTag != null) ? _divTag : "id_undefined";
if (_title != null && _inputArray != null) {
_ret.title = _title;
_ret.input = null;
_ret.input = new Array();
for (var i = 0; i < _inputArray.length; i++) {
_ret.input[i] = _inputArray[i];
}
}
_ret.htmlTag = _ret.getHtmlTag();
// get html tag
};
_ret.htmlTag = "";
_ret.getHtmlTag = function () {
var _tag = "";
// _tag += "<div>";
_tag += "<div data-role='fieldcontain'>";
_tag += "<label for='" + _ret.divTag + "' class='dropdown-text-lower'>" + _ret.title + "</label>";
_tag += "<select id='" + _ret.divTag + "' data-inline='true' data-mini='true' data-theme='c'>";
for (var i = 0; i < _ret.input.length; i++) {
_tag += "<option value='" + util_forceString(i, "1") + "'>" + _ret.input[i] + "</option>";
}
_tag += "</select>";
_tag += "</div>";
return _tag;
};
return _ret;
}
var constructsSoldPgTag = function () {
var _ret = {};
_ret.title = "Constructs Sold";
_ret.inputData = [157, 306, 414, 0, 0];
_ret.inputType = "Hip";
_ret.inputTitle = ["2010", "2011", "2012", "2013", "2014"];
_ret.init = function () {
_ret.htmlTag = _ret.getHtmlTag();
};
_ret.htmlTag = "";
_ret.getHtmlTag = function () {
var _tag = "<div>";
_tag += "<div class='text-box-title'> <span>" + _ret.title + "</span></div>";
_tag += "<div> </div>";
_tag += "<table class='constuct-table'>";
_tag += "<tr><td> </td>"
for (var i = 0; i < _ret.inputTitle.length; i++) {
_tag += "<td>" + _ret.inputTitle[i] + "</td>";
}
_tag += "</tr>";
_tag += "<tr><td>" + _ret.inputType + "</td>";
for (var i = 0; i < _ret.inputTitle.length; i++) {
_tag += "<td>" + _ret.inputData[i] + "</td>";
}
_tag += "</tr>";
_tag += "</table>";
_tag += "</div>";
return _tag;
}
return _ret;
}
var PurchasePgTag = function () {
var _ret = {};
_ret.title = "Hospital Value-based Purchasing";
_ret.inputData2013 = ["2013", "-", "-", "-"];
_ret.inputData2014 = ["2014", "-", "-", "-"];
_ret.inputType = "Hip";
_ret.inputTitle = [" ", "Adjustment", "Rank", "Quartile"];
_ret.init = function () {
// get data;
_ret.htmlTag = _ret.getHtmlTag();
};
_ret.htmlTag = "";
_ret.getHtmlTag = function () {
var _tag = "<div>";
_tag += "<div class='text-box-title'> <span>" + _ret.title + "</span></div>";
_tag += "<table class='constuct-table'>";
_tag += "<tr>";
for (var i = 0; i < _ret.inputTitle.length; i++) {
_tag += "<td>" + _ret.inputTitle[i] + "</td>";
}
_tag += "</tr>";
_tag += "<tr>";
for (var i = 0; i < _ret.inputTitle.length; i++) {
_tag += "<td>" + _ret.inputData2013[i] + "</td>";
}
_tag += "</tr>";
_tag += "<tr>";
for (var i = 0; i < _ret.inputTitle.length; i++) {
_tag += "<td>" + _ret.inputData2014[i] + "</td>";
}
_tag += "</tr>";
_tag += "</table>";
_tag += "</div>";
return _tag;
}
return _ret;
}
var ImpactSeqPgTag = function () {
var _ret = {};
_ret.title = "Impact of Sequester";
_ret.inputClicke = true;
_ret.init = function () {
// get data;
_ret.htmlTag = _ret.getHtmlTag();
};
_ret.htmlTag = "";
_ret.getHtmlTag = function () {
var chkId = "impactSeq";
var chkbox = new objCheckbox(chkId);
var _tag = "<div>";
_tag += "<div class='text-box-title'> <span>" + _ret.title + "</span></div>";
_tag += "<div> </div>";
_tag += "<div class='box-align-center'><table class='Impact-table'>";
_tag += "<tr>";
_tag += "<td>" + chkbox.tagHtml;
_tag += "</td>" + "<td>" + "Click to Apply" + "</td>";
_tag += "</tr>";
_tag += "</table></div>";
_tag += "</div>";
return _tag;
}
return _ret;
}
var objCheckbox = function (_divTag) {
var ret = {};
var tag = "";
tag += "<fieldset data-role='controlgroup'>";
tag += "<input type='checkbox' data-theme='z' name='";
tag += util_forceString(_divTag, "") + "' id='" + util_forceString(_divTag, "") + "' data-iconpos='notext' />";
tag += "<label for='" + util_forceString(_divTag, "") + "' data='content' style='position:relative;right:-0.9em'></label>";
tag += "</fieldset>";
ret.tagHtml = tag;
ret.addObserver = function (observer) {
ret.Observers.push(observer);
};
ret.removeObserver = function (observer) {
if (ret.Observers.length > 0) {
ret.Observers.pop(observer);
}
};
ret.Observers = new Array();
return ret;
}
Saturday, March 5, 2016
Saturday, February 20, 2016
Unix Shell Script Basics
14 04. Unix Shell Scripting Tutorial - Shell Programming Features (Part 3)
###### the types of shell language #####
Bourne Shell --- sh
Ksh
bash
zsh
csh
#### the script interpreters
csh
sed
tcl
perl
awk
##############################
Step 1: edit shell script
Step 2: chmod +x script
step 3: ./script #######to run
##########################
echo $path
################
echo
read varname var2
echo $varname $var2
echo "abc \c"
#######################
ls > a.txt
ls >> a.txt #append the file
########## Error Message ##############
find -name a.txt >result.txt 2>error.txt
find -name a.txt >result.txt 2>&1 #save to same file
#######################
cat file
######### number of users on system #####
echo passwd > file.txt
## number of logged users ###\
echo who
## number of running processes ##
ps -e
## count numbers of user
cat /etc/passwd | wc -l
###############################
=========
### run in background ##
&
sleep 100 &
backup &
#######################
< stardard input
who | wc > user.txt
#######################
sudo add-apt-repository ppa:notepadqq-team/notepadqq
sudo apt-get update
sudo apt-get install notepadqq
#######################Special Character#######################
& * ? [ ] < > |
( ) ' # $ ^ =
` ' " { } ; \"
#######################
Echo the fee is \$10
echo the comment is "#" character
echo "the comment is # character"
echo the comment is `$` character
####################### Comments #######################
##run a script
1. myscript
2. sh myscript ### the script does not need to be made executable
3. . myscript ### the script are excuted by current shell
4. sh .profile
5. . . profile
6. exec myscript ## terminates current shell, run script, once done, logged off current user
############ Specify Script Interpreters ##########################
#!/bin/csh ### in first line of script, specify which interpreter
OR
#!/user/local/bin/perl
######################## Variables #######
month=agust #### case sensitive
sports=basketball ## no space
street="Smith Avenue" ## add space
###### all shell variables are strings
echo you live on $street
############ Environment Variables #########
$HOME
$PATH
$LOGNAME
$TERM
## change the enviroment variable , then execute
. SCRIPT
SH SCRIPT
## export variable to environment
## 1. script.sh ##
month=Jan
export month
## 2. run it ##
. script.sh
## #########
echo $month
## ############# Quotes ######################
' single quote ##### seperate command parameters, new line characters
" dobule quote #####
` back quote
###### the types of shell language #####
Bourne Shell --- sh
Ksh
bash
zsh
csh
#### the script interpreters
csh
sed
tcl
perl
awk
##############################
Step 1: edit shell script
Step 2: chmod +x script
step 3: ./script #######to run
##########################
echo $path
################
echo
read varname var2
echo $varname $var2
echo "abc \c"
#######################
ls > a.txt
ls >> a.txt #append the file
########## Error Message ##############
find -name a.txt >result.txt 2>error.txt
find -name a.txt >result.txt 2>&1 #save to same file
#######################
cat file
######### number of users on system #####
echo passwd > file.txt
## number of logged users ###\
echo who
## number of running processes ##
ps -e
## count numbers of user
cat /etc/passwd | wc -l
###############################
=========
### run in background ##
&
sleep 100 &
backup &
#######################
< stardard input
who | wc > user.txt
#######################
sudo add-apt-repository ppa:notepadqq-team/notepadqq
sudo apt-get update
sudo apt-get install notepadqq
#######################Special Character#######################
& * ? [ ] < > |
( ) ' # $ ^ =
` ' " { } ; \"
#######################
Echo the fee is \$10
echo the comment is "#" character
echo "the comment is # character"
echo the comment is `$` character
####################### Comments #######################
##run a script
1. myscript
2. sh myscript ### the script does not need to be made executable
3. . myscript ### the script are excuted by current shell
4. sh .profile
5. . . profile
6. exec myscript ## terminates current shell, run script, once done, logged off current user
############ Specify Script Interpreters ##########################
#!/bin/csh ### in first line of script, specify which interpreter
OR
#!/user/local/bin/perl
######################## Variables #######
month=agust #### case sensitive
sports=basketball ## no space
street="Smith Avenue" ## add space
###### all shell variables are strings
echo you live on $street
############ Environment Variables #########
$HOME
$PATH
$LOGNAME
$TERM
## change the enviroment variable , then execute
. SCRIPT
SH SCRIPT
## export variable to environment
## 1. script.sh ##
month=Jan
export month
## 2. run it ##
. script.sh
## #########
echo $month
## ############# Quotes ######################
' single quote ##### seperate command parameters, new line characters
" dobule quote #####
` back quote
Sunday, January 3, 2016
SQL编程经典-精妙SQL语句收集
SQL语句先前写的时候，很容易把一些特殊的用法忘记，我特此整理了一下SQL语句操作，方便自己写SQL时方便一点，想贴上来，一起看看，同时希望大家能共同多多提意见，也给我留一些更好的佳句，整理一份《精妙SQL速查手册》，不吝赐教！（文章作者：仰天一笑）
一、基础
1、说明：创建数据库
CREATE DATABASE database-name
2、说明：删除数据库
drop database dbname
3、说明：备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice ’disk’, ’testBack’, ’c:\mssql7backup\MyNwind_1.dat’
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
5、说明：删除新表
drop table tabname
6、说明：增加一个列
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：添加主键： Alter table tabname add primary key(col)
说明：删除主键： Alter table tabname drop primary key(col)
8、说明：创建索引：create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
9、说明：创建视图：create view viewname as select statement
删除视图：drop view viewname
10、说明：几个简单的基本的sql语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
11、说明：几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
12、说明：使用外连接
A、left outer join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right outer join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full outer join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
二、提升
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1
法二：select top 0 * into b from a
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in ’"&Server.MapPath(".")&"\data.mdb" &"’ where..
4、说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、说明：在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff(’minute’,f开始时间,getdate())>5
13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
14、说明：前10条记录
select top 10 * form table1 where 范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于**每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、说明：随机取出10条数据
select top 10 * from tablename order by newid()
18、说明：随机选择记录
select newid()
19、说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
20、说明：列出数据库里所有的表名
select name from sysobjects where type=’U’
21、说明：列出表里的所有的
select name from syscolumns where id=object_id(’TableName’)
22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when ’A’ then pcs else 0 end),sum(case vender when ’C’ then pcs else 0 end),sum(case vender when ’B’ then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
23、说明：初始化表table1
TRUNCATE TABLE table1
24、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
三、技巧
1、1=1，1=2的使用，在SQL语句组合时用的较多
“where 1=1” 是表示选择全部 “where 1=2”全部不选，
如：
if @strWhere !=’’
begin
set @strSQL = ’select count(*) as Total from [’ + @tblName + ’] where ’ + @strWhere
end
else
begin
set @strSQL = ’select count(*) as Total from [’ + @tblName + ’]’
end
我们可以直接写成
set @strSQL = ’select count(*) as Total from [’ + @tblName + ’] where 1=1 安定 ’+ @strWhere
2、收缩数据库
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE
3、压缩数据库
dbcc shrinkdatabase(dbname)
4、转移数据库给新用户以已存在用户权限
exec sp_change_users_login ’update_one’,’newname’,’oldname’
go
5、检查备份集
RESTORE VERIFYONLY from disk=’E:\dvbbs.bak’
6、修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB(’dvbbs’,repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO
7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE tablename -- 要操作的数据库名
SELECT @LogicalFileName = ’tablename_log’, -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想设定的日志文件的大小(M)
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT ’Original Size of ’ + db_name() + ’ LOG is ’ +
CONVERT(VARCHAR(30),@OriginalSize) + ’ 8K pages or ’ +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + ’MB’
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = ’BACKUP LOG ’ + db_name() + ’ WITH TRUNCATE_ONLY’
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES (’Fill Log’)
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT ’Final Size of ’ + db_name() + ’ LOG is ’ +
CONVERT(VARCHAR(30),size) + ’ 8K pages or ’ +
CONVERT(VARCHAR(30),(size*8/1024)) + ’MB’
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF
8、说明：更改某个表
exec sp_changeobjectowner ’tablename’,’dbo’
9、存储更改全部表
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select ’Name’ = name,
’Owner’ = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + ’.’ + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO
10、SQL SERVER中直接循环写入数据
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end
一、基础
1、说明：创建数据库
CREATE DATABASE database-name
2、说明：删除数据库
drop database dbname
3、说明：备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice ’disk’, ’testBack’, ’c:\mssql7backup\MyNwind_1.dat’
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
5、说明：删除新表
drop table tabname
6、说明：增加一个列
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：添加主键： Alter table tabname add primary key(col)
说明：删除主键： Alter table tabname drop primary key(col)
8、说明：创建索引：create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
9、说明：创建视图：create view viewname as select statement
删除视图：drop view viewname
10、说明：几个简单的基本的sql语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
11、说明：几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
12、说明：使用外连接
A、left outer join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right outer join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full outer join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
二、提升
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1
法二：select top 0 * into b from a
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in ’"&Server.MapPath(".")&"\data.mdb" &"’ where..
4、说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、说明：在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff(’minute’,f开始时间,getdate())>5
13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
14、说明：前10条记录
select top 10 * form table1 where 范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于**每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、说明：随机取出10条数据
select top 10 * from tablename order by newid()
18、说明：随机选择记录
select newid()
19、说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
20、说明：列出数据库里所有的表名
select name from sysobjects where type=’U’
21、说明：列出表里的所有的
select name from syscolumns where id=object_id(’TableName’)
22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when ’A’ then pcs else 0 end),sum(case vender when ’C’ then pcs else 0 end),sum(case vender when ’B’ then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
23、说明：初始化表table1
TRUNCATE TABLE table1
24、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
三、技巧
1、1=1，1=2的使用，在SQL语句组合时用的较多
“where 1=1” 是表示选择全部 “where 1=2”全部不选，
如：
if @strWhere !=’’
begin
set @strSQL = ’select count(*) as Total from [’ + @tblName + ’] where ’ + @strWhere
end
else
begin
set @strSQL = ’select count(*) as Total from [’ + @tblName + ’]’
end
我们可以直接写成
set @strSQL = ’select count(*) as Total from [’ + @tblName + ’] where 1=1 安定 ’+ @strWhere
2、收缩数据库
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE
3、压缩数据库
dbcc shrinkdatabase(dbname)
4、转移数据库给新用户以已存在用户权限
exec sp_change_users_login ’update_one’,’newname’,’oldname’
go
5、检查备份集
RESTORE VERIFYONLY from disk=’E:\dvbbs.bak’
6、修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB(’dvbbs’,repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO
7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE tablename -- 要操作的数据库名
SELECT @LogicalFileName = ’tablename_log’, -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想设定的日志文件的大小(M)
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT ’Original Size of ’ + db_name() + ’ LOG is ’ +
CONVERT(VARCHAR(30),@OriginalSize) + ’ 8K pages or ’ +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + ’MB’
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = ’BACKUP LOG ’ + db_name() + ’ WITH TRUNCATE_ONLY’
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES (’Fill Log’)
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT ’Final Size of ’ + db_name() + ’ LOG is ’ +
CONVERT(VARCHAR(30),size) + ’ 8K pages or ’ +
CONVERT(VARCHAR(30),(size*8/1024)) + ’MB’
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF
8、说明：更改某个表
exec sp_changeobjectowner ’tablename’,’dbo’
9、存储更改全部表
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select ’Name’ = name,
’Owner’ = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + ’.’ + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO
10、SQL SERVER中直接循环写入数据
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end
Subscribe to:
Posts (Atom)