SQLServer学习笔记系列1-mysql教程-PHP中文網

一.前言一直自己没有学习做笔记的习惯，所以为了加强自己对知识的深入理解，决定将学习笔记写下来，希望向各位大牛们学习交流！不当之处请斧正！在此感谢！这边就先从学习Sqlserver写起，自己本身对数据库方面不擅长，所以决定对此从基础开始学习，大牛们

一.前言

一直自己没有学习做笔记的习惯，所以为了加强自己对知识的深入理解，决定将学习笔记写下来，希望向各位大牛们学习交流！不当之处请斧正！在此感谢！

这边就先从学习Sqlserver写起，自己本身对数据库方面不擅长，所以决定对此从基础开始学习，大牛们对此文可以忽略！首先以《Sqlserver2008技术内幕》

这本书作为学习的指导，大家如果觉得这本书不错的话，可以去网上买一本，作为菜鸟的我，觉得这本书对于入门介绍的还是非常不错的。请戳

我：http://item.jd.com/10067484.html#none。

二.Sqlserver基础知识

（1）创建数据库

创建数据库有两种方式，手动创建和编写sql脚本创建，在这里我采用脚本的方式创建一个名称为TSQLFundamentals2008的数据库。脚本如下：

SQLServer学习笔记系列1

<span>  1</span> <span>--</span><span>-------------------------------------------------------------------</span>
<span>  2</span> <span>--</span><span> Microsoft SQL Server 2008 T-SQL Fundamentals</span>
<span>  3</span> <span>--
</span><span>  4</span> <span>--</span><span> Script that creates the sample database TSQLFundamentals2008</span>
<span>  5</span> <span>--
</span><span>  6</span> <span>--</span><span> Supported versions of SQL Server: 2005, 2008</span>
<span>  7</span> <span>--
</span><span>  8</span> <span>--</span><span> Based originally on the Northwind sample database</span>
<span>  9</span> <span>--</span><span> with changes in both schema and data to fit the book's needs</span>
<span> 10</span> <span>--
</span><span> 11</span> <span>--</span><span> Last updated: 20081202</span>
<span> 12</span> <span>--</span><span>-------------------------------------------------------------------</span>
<span> 13</span> 
<span> 14</span> <span>--</span><span>-------------------------------------------------------------------</span>
<span> 15</span> <span>--</span><span> Create Database</span>
<span> 16</span> <span>--</span><span>-------------------------------------------------------------------</span>
<span> 17</span> 
<span> 18</span> <span>USE</span><span> master;
</span><span> 19</span> 
<span> 20</span> <span>--</span><span> Drop database</span>
<span> 21</span> <span>IF</span> <span>DB_ID</span>(<span>'</span><span>TSQLFundamentals2008</span><span>'</span>) <span>IS</span> <span>NOT</span> <span>NULL</span> <span>DROP</span> <span>DATABASE</span><span> TSQLFundamentals2008;
</span><span> 22</span> 
<span> 23</span> <span>--</span><span> If database could not be created due to open connections, abort</span>
<span> 24</span> <span>IF</span> <span>@@ERROR</span> <span>=</span> <span>3702</span> 
<span> 25</span>    <span>RAISERROR</span>(<span>'</span><span>Database cannot be dropped because there are still open connections.</span><span>'</span>, <span>127</span>, <span>127</span>) <span>WITH</span> NOWAIT, <span>LOG</span><span>;
</span><span> 26</span> 
<span> 27</span> <span>--</span><span> Create database</span>
<span> 28</span> <span>CREATE</span> <span>DATABASE</span><span> TSQLFundamentals2008;
</span><span> 29</span> <span>GO</span>
<span> 30</span> 
<span> 31</span> <span>USE</span><span> TSQLFundamentals2008;
</span><span> 32</span> <span>GO</span>
<span> 33</span> 
<span> 34</span> <span>--</span><span>-------------------------------------------------------------------</span>
<span> 35</span> <span>--</span><span> Create Schemas</span>
<span> 36</span> <span>--</span><span>-------------------------------------------------------------------</span>
<span> 37</span> 
<span> 38</span> <span>CREATE</span> <span>SCHEMA</span> HR <span>AUTHORIZATION</span><span> dbo;
</span><span> 39</span> <span>GO</span>
<span> 40</span> <span>CREATE</span> <span>SCHEMA</span> Production <span>AUTHORIZATION</span><span> dbo;
</span><span> 41</span> <span>GO</span>
<span> 42</span> <span>CREATE</span> <span>SCHEMA</span> Sales <span>AUTHORIZATION</span><span> dbo;
</span><span> 43</span> <span>GO</span>
<span> 44</span> 
<span> 45</span> <span>--</span><span>-------------------------------------------------------------------</span>
<span> 46</span> <span>--</span><span> Create Tables</span>
<span> 47</span> <span>--</span><span>-------------------------------------------------------------------</span>
<span> 48</span> 
<span> 49</span> <span>--</span><span> Create table HR.Employees</span>
<span> 50</span> <span>CREATE</span> <span>TABLE</span><span> HR.Employees
</span><span> 51</span> <span>(
</span><span> 52</span>   empid           <span>INT</span>          <span>NOT</span> <span>NULL</span> <span>IDENTITY</span><span>,
</span><span> 53</span>   lastname        <span>NVARCHAR</span>(<span>20</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 54</span>   firstname       <span>NVARCHAR</span>(<span>10</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 55</span>   title           <span>NVARCHAR</span>(<span>30</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 56</span>   titleofcourtesy <span>NVARCHAR</span>(<span>25</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 57</span>   birthdate       <span>DATETIME</span>     <span>NOT</span> <span>NULL</span><span>,
</span><span> 58</span>   hiredate        <span>DATETIME</span>     <span>NOT</span> <span>NULL</span><span>,
</span><span> 59</span>   address         <span>NVARCHAR</span>(<span>60</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 60</span>   city            <span>NVARCHAR</span>(<span>15</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 61</span>   region          <span>NVARCHAR</span>(<span>15</span>) <span>NULL</span><span>,
</span><span> 62</span>   postalcode      <span>NVARCHAR</span>(<span>10</span>) <span>NULL</span><span>,
</span><span> 63</span>   country         <span>NVARCHAR</span>(<span>15</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 64</span>   phone           <span>NVARCHAR</span>(<span>24</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 65</span>   mgrid           <span>INT</span>          <span>NULL</span><span>,
</span><span> 66</span>   <span>CONSTRAINT</span> PK_Employees <span>PRIMARY</span> <span>KEY</span><span>(empid),
</span><span> 67</span>   <span>CONSTRAINT</span> FK_Employees_Employees <span>FOREIGN</span> <span>KEY</span><span>(mgrid)
</span><span> 68</span>     <span>REFERENCES</span><span> HR.Employees(empid),
</span><span> 69</span>   <span>CONSTRAINT</span> CHK_birthdate <span>CHECK</span>(birthdate <span> <span>CURRENT_TIMESTAMP</span><span>)
</span><span> 70</span> <span>);
</span><span> 71</span> 
<span> 72</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_lastname <span>ON</span><span> HR.Employees(lastname);
</span><span> 73</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_postalcode <span>ON</span><span> HR.Employees(postalcode);
</span><span> 74</span> 
<span> 75</span> <span>--</span><span> Create table Production.Suppliers</span>
<span> 76</span> <span>CREATE</span> <span>TABLE</span><span> Production.Suppliers
</span><span> 77</span> <span>(
</span><span> 78</span>   supplierid   <span>INT</span>          <span>NOT</span> <span>NULL</span> <span>IDENTITY</span><span>,
</span><span> 79</span>   companyname  <span>NVARCHAR</span>(<span>40</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 80</span>   contactname  <span>NVARCHAR</span>(<span>30</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 81</span>   contacttitle <span>NVARCHAR</span>(<span>30</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 82</span>   address      <span>NVARCHAR</span>(<span>60</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 83</span>   city         <span>NVARCHAR</span>(<span>15</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 84</span>   region       <span>NVARCHAR</span>(<span>15</span>) <span>NULL</span><span>,
</span><span> 85</span>   postalcode   <span>NVARCHAR</span>(<span>10</span>) <span>NULL</span><span>,
</span><span> 86</span>   country      <span>NVARCHAR</span>(<span>15</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 87</span>   phone        <span>NVARCHAR</span>(<span>24</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 88</span>   fax          <span>NVARCHAR</span>(<span>24</span>) <span>NULL</span><span>,
</span><span> 89</span>   <span>CONSTRAINT</span> PK_Suppliers <span>PRIMARY</span> <span>KEY</span><span>(supplierid)
</span><span> 90</span> <span>);
</span><span> 91</span> 
<span> 92</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_companyname <span>ON</span><span> Production.Suppliers(companyname);
</span><span> 93</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_postalcode  <span>ON</span><span> Production.Suppliers(postalcode);
</span><span> 94</span> 
<span> 95</span> <span>--</span><span> Create table Production.Categories</span>
<span> 96</span> <span>CREATE</span> <span>TABLE</span><span> Production.Categories
</span><span> 97</span> <span>(
</span><span> 98</span>   categoryid   <span>INT</span>           <span>NOT</span> <span>NULL</span> <span>IDENTITY</span><span>,
</span><span> 99</span>   categoryname <span>NVARCHAR</span>(<span>15</span>)  <span>NOT</span> <span>NULL</span><span>,
</span><span>100</span>   description  <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>101</span>   <span>CONSTRAINT</span> PK_Categories <span>PRIMARY</span> <span>KEY</span><span>(categoryid)
</span><span>102</span> <span>);
</span><span>103</span> 
<span>104</span> <span>CREATE</span> <span>INDEX</span> categoryname <span>ON</span><span> Production.Categories(categoryname);
</span><span>105</span> 
<span>106</span> <span>--</span><span> Create table Production.Products</span>
<span>107</span> <span>CREATE</span> <span>TABLE</span><span> Production.Products
</span><span>108</span> <span>(
</span><span>109</span>   productid    <span>INT</span>          <span>NOT</span> <span>NULL</span> <span>IDENTITY</span><span>,
</span><span>110</span>   productname  <span>NVARCHAR</span>(<span>40</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>111</span>   supplierid   <span>INT</span>          <span>NOT</span> <span>NULL</span><span>,
</span><span>112</span>   categoryid   <span>INT</span>          <span>NOT</span> <span>NULL</span><span>,
</span><span>113</span>   unitprice    <span>MONEY</span>        <span>NOT</span> <span>NULL</span>
<span>114</span>     <span>CONSTRAINT</span> DFT_Products_unitprice <span>DEFAULT</span>(<span>0</span><span>),
</span><span>115</span>   discontinued <span>BIT</span>          <span>NOT</span> <span>NULL</span> 
<span>116</span>     <span>CONSTRAINT</span> DFT_Products_discontinued <span>DEFAULT</span>(<span>0</span><span>),
</span><span>117</span>   <span>CONSTRAINT</span> PK_Products <span>PRIMARY</span> <span>KEY</span><span>(productid),
</span><span>118</span>   <span>CONSTRAINT</span> FK_Products_Categories <span>FOREIGN</span> <span>KEY</span><span>(categoryid)
</span><span>119</span>     <span>REFERENCES</span><span> Production.Categories(categoryid),
</span><span>120</span>   <span>CONSTRAINT</span> FK_Products_Suppliers <span>FOREIGN</span> <span>KEY</span><span>(supplierid)
</span><span>121</span>     <span>REFERENCES</span><span> Production.Suppliers(supplierid),
</span><span>122</span>   <span>CONSTRAINT</span> CHK_Products_unitprice <span>CHECK</span>(unitprice <span>>=</span> <span>0</span><span>)
</span><span>123</span> <span>);
</span><span>124</span> 
<span>125</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_categoryid <span>ON</span><span> Production.Products(categoryid);
</span><span>126</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_productname <span>ON</span><span> Production.Products(productname);
</span><span>127</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_supplierid  <span>ON</span><span> Production.Products(supplierid);
</span><span>128</span> 
<span>129</span> <span>--</span><span> Create table Sales.Customers</span>
<span>130</span> <span>CREATE</span> <span>TABLE</span><span> Sales.Customers
</span><span>131</span> <span>(
</span><span>132</span>   custid       <span>INT</span>          <span>NOT</span> <span>NULL</span> <span>IDENTITY</span><span>,
</span><span>133</span>   companyname  <span>NVARCHAR</span>(<span>40</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>134</span>   contactname  <span>NVARCHAR</span>(<span>30</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>135</span>   contacttitle <span>NVARCHAR</span>(<span>30</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>136</span>   address      <span>NVARCHAR</span>(<span>60</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>137</span>   city         <span>NVARCHAR</span>(<span>15</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>138</span>   region       <span>NVARCHAR</span>(<span>15</span>) <span>NULL</span><span>,
</span><span>139</span>   postalcode   <span>NVARCHAR</span>(<span>10</span>) <span>NULL</span><span>,
</span><span>140</span>   country      <span>NVARCHAR</span>(<span>15</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>141</span>   phone        <span>NVARCHAR</span>(<span>24</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>142</span>   fax          <span>NVARCHAR</span>(<span>24</span>) <span>NULL</span><span>,
</span><span>143</span>   <span>CONSTRAINT</span> PK_Customers <span>PRIMARY</span> <span>KEY</span><span>(custid)
</span><span>144</span> <span>);
</span><span>145</span> 
<span>146</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_city <span>ON</span><span> Sales.Customers(city);
</span><span>147</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_companyname <span>ON</span><span> Sales.Customers(companyname);
</span><span>148</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_postalcode  <span>ON</span><span> Sales.Customers(postalcode);
</span><span>149</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_region      <span>ON</span><span> Sales.Customers(region);
</span><span>150</span> 
<span>151</span> <span>--</span><span> Create table Sales.Shippers</span>
<span>152</span> <span>CREATE</span> <span>TABLE</span><span> Sales.Shippers
</span><span>153</span> <span>(
</span><span>154</span>   shipperid   <span>INT</span>          <span>NOT</span> <span>NULL</span> <span>IDENTITY</span><span>,
</span><span>155</span>   companyname <span>NVARCHAR</span>(<span>40</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>156</span>   phone       <span>NVARCHAR</span>(<span>24</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>157</span>   <span>CONSTRAINT</span> PK_Shippers <span>PRIMARY</span> <span>KEY</span><span>(shipperid)
</span><span>158</span> <span>);
</span><span>159</span> 
<span>160</span> <span>--</span><span> Create table Sales.Orders</span>
<span>161</span> <span>CREATE</span> <span>TABLE</span><span> Sales.Orders
</span><span>162</span> <span>(
</span><span>163</span>   orderid        <span>INT</span>          <span>NOT</span> <span>NULL</span> <span>IDENTITY</span><span>,
</span><span>164</span>   custid         <span>INT</span>          <span>NULL</span><span>,
</span><span>165</span>   empid          <span>INT</span>          <span>NOT</span> <span>NULL</span><span>,
</span><span>166</span>   orderdate      <span>DATETIME</span>     <span>NOT</span> <span>NULL</span><span>,
</span><span>167</span>   requireddate   <span>DATETIME</span>     <span>NOT</span> <span>NULL</span><span>,
</span><span>168</span>   shippeddate    <span>DATETIME</span>     <span>NULL</span><span>,
</span><span>169</span>   shipperid      <span>INT</span>          <span>NOT</span> <span>NULL</span><span>,
</span><span>170</span>   freight        <span>MONEY</span>        <span>NOT</span> <span>NULL</span>
<span>171</span>     <span>CONSTRAINT</span> DFT_Orders_freight <span>DEFAULT</span>(<span>0</span><span>),
</span><span>172</span>   shipname       <span>NVARCHAR</span>(<span>40</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>173</span>   shipaddress    <span>NVARCHAR</span>(<span>60</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>174</span>   shipcity       <span>NVARCHAR</span>(<span>15</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>175</span>   shipregion     <span>NVARCHAR</span>(<span>15</span>) <span>NULL</span><span>,
</span><span>176</span>   shippostalcode <span>NVARCHAR</span>(<span>10</span>) <span>NULL</span><span>,
</span><span>177</span>   shipcountry    <span>NVARCHAR</span>(<span>15</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>178</span>   <span>CONSTRAINT</span> PK_Orders <span>PRIMARY</span> <span>KEY</span><span>(orderid),
</span><span>179</span>   <span>CONSTRAINT</span> FK_Orders_Customers <span>FOREIGN</span> <span>KEY</span><span>(custid)
</span><span>180</span>     <span>REFERENCES</span><span> Sales.Customers(custid),
</span><span>181</span>   <span>CONSTRAINT</span> FK_Orders_Employees <span>FOREIGN</span> <span>KEY</span><span>(empid)
</span><span>182</span>     <span>REFERENCES</span><span> HR.Employees(empid),
</span><span>183</span>   <span>CONSTRAINT</span> FK_Orders_Shippers <span>FOREIGN</span> <span>KEY</span><span>(shipperid)
</span><span>184</span>     <span>REFERENCES</span><span> Sales.Shippers(shipperid)
</span><span>185</span> <span>);
</span><span>186</span> 
<span>187</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_custid <span>ON</span><span> Sales.Orders(custid);
</span><span>188</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_empid          <span>ON</span><span> Sales.Orders(empid);
</span><span>189</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_shipperid      <span>ON</span><span> Sales.Orders(shipperid);
</span><span>190</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_orderdate      <span>ON</span><span> Sales.Orders(orderdate);
</span><span>191</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_shippeddate    <span>ON</span><span> Sales.Orders(shippeddate);
</span><span>192</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_shippostalcode <span>ON</span><span> Sales.Orders(shippostalcode);
</span><span>193</span> 
<span>194</span> <span>--</span><span> Create table Sales.OrderDetails</span>
<span>195</span> <span>CREATE</span> <span>TABLE</span><span> Sales.OrderDetails
</span><span>196</span> <span>(
</span><span>197</span>   orderid   <span>INT</span>           <span>NOT</span> <span>NULL</span><span>,
</span><span>198</span>   productid <span>INT</span>           <span>NOT</span> <span>NULL</span><span>,
</span><span>199</span>   unitprice <span>MONEY</span>         <span>NOT</span> <span>NULL</span>
<span>200</span>     <span>CONSTRAINT</span> DFT_OrderDetails_unitprice <span>DEFAULT</span>(<span>0</span><span>),
</span><span>201</span>   qty       <span>SMALLINT</span>      <span>NOT</span> <span>NULL</span>
<span>202</span>     <span>CONSTRAINT</span> DFT_OrderDetails_qty <span>DEFAULT</span>(<span>1</span><span>),
</span><span>203</span>   discount  NUMERIC(<span>4</span>, <span>3</span>) <span>NOT</span> <span>NULL</span>
<span>204</span>     <span>CONSTRAINT</span> DFT_OrderDetails_discount <span>DEFAULT</span>(<span>0</span><span>),
</span><span>205</span>   <span>CONSTRAINT</span> PK_OrderDetails <span>PRIMARY</span> <span>KEY</span><span>(orderid, productid),
</span><span>206</span>   <span>CONSTRAINT</span> FK_OrderDetails_Orders <span>FOREIGN</span> <span>KEY</span><span>(orderid)
</span><span>207</span>     <span>REFERENCES</span><span> Sales.Orders(orderid),
</span><span>208</span>   <span>CONSTRAINT</span> FK_OrderDetails_Products <span>FOREIGN</span> <span>KEY</span><span>(productid)
</span><span>209</span>     <span>REFERENCES</span><span> Production.Products(productid),
</span><span>210</span>   <span>CONSTRAINT</span> CHK_discount  <span>CHECK</span> (discount <span>BETWEEN</span> <span>0</span> <span>AND</span> <span>1</span><span>),
</span><span>211</span>   <span>CONSTRAINT</span> CHK_qty  <span>CHECK</span> (qty <span>></span> <span>0</span><span>),
</span><span>212</span>   <span>CONSTRAINT</span> CHK_unitprice <span>CHECK</span> (unitprice <span>>=</span> <span>0</span><span>)
</span><span>213</span> <span>)
</span><span>214</span> 
<span>215</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_orderid <span>ON</span><span> Sales.OrderDetails(orderid);
</span><span>216</span> <span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> idx_nc_productid <span>ON</span><span> Sales.OrderDetails(productid);
</span><span>217</span> <span>GO</span></span>

登入後複製

View Code

同时往数据库表插入一些数据，用户后续对数据库的sql的练习。在这里有需要的可以下载相应的脚本进行数据库的初始化。我放到百度云上面，请戳

我：http://yun.baidu.com/share/link?shareid=3635107613&uk=2971209779，提供了《Sqlserver2008技术内幕》这本书的电子版和脚本。

（2）在这里对TSQLFundamentals2008数据各个表进行表说明一下：

数据库表界面如下：

SQLServer学习笔记系列1


HR.Employees	雇员表，存放员工的一些基本信息。
Production.Products	产品信息表
Production.Suppliers	供应商表
Production.Customers	顾客信息表
Production.Categories	产品类别表
Sales.OrderDetails	订单详情表
Sales.Orders	订单表
Sales.Shippers	货运公司表

三.Sqlserver一些基本命令：

查询数据库是否存在：

if DB_ID（"testDB"）is not null;

检查表是否存在：

if OBJECT_ID（“textDB”，“U”） is not null ;其中U代表用户表

创建数据库：

create database+数据名

删除数据库：

drop database 数据库名 --删除数据库的

drop table 表名--删除表的

delete from 表名 where 条件 --删除数据的

查询语句：

use 数据库名称 --修改的数据库

select*from +表名称 --要查询的表

select某某，某某，某某 from 表名称 where 条件 --带条件查询的数据

插入数据：

insert into 表名称（条件）values （相对应的值）

四.单表查询

（1）分组--对于分组查询，select字句会有限制，需要查询字段要出现在group by 子句中，同时分组以后，可以对分组情况进行统计。

查询雇员表，根据雇员所在国家分组，统计每组的人数情况：

<span>1</span> <span>select</span> country,<span>count</span>(<span>*</span>) <span>as</span> N<span>'</span><span>人数</span><span>'</span>
<span>2</span> <span>from</span><span> hr.Employees
</span><span>3</span> <span>group</span> <span>by</span> country

登入後複製

SQLServer学习笔记系列1

当要查询的字段不包含在group by子句中，则会报相应的错误，所以此时要注意出现在select 后面的查询字段进行分组后，也同时需要出现在group by后面。

SQLServer学习笔记系列1

（2）在这里提示一下：查询条件不要使用计算列，下面谈谈具体原因：

例如：查询雇员表里面雇员出生为1973年的所有雇员信息，可以这样编写sql语句:

<span>1</span> <span>select</span> <span>YEAR</span>(birthdate),firstname,lastname <span>from</span><span> HR.Employees
</span><span>2</span> <span>where</span> <span>YEAR</span>(birthdate)<span>=</span><span>'</span><span>1973</span><span>'</span>

登入後複製

SQLServer学习笔记系列1

可以看到查询结果将1973年的雇员信息查出来了，但是大家可以思考一下，上面的sql语句在查询的时候，首先是要讲birthdate进行取出年度的计算，

Year（birthdate），其中Year为sql的内置函数，可以用于对字符串日期进行取出年份的计算。同时我们还可以采用下面的sql语句进行查询：

SQLServer学习笔记系列1

通过sql执行计划可以看出来，查询条件带计算列走的是索引扫描，而where子句后面采用查找范围限制，则走的是索查找。对比两个查询显然绝大部分情况下

走索引查找的查询性能要高于走索引扫描，特别是查询的数据库不是非常大的情况下，索引查找的消耗时间要远远少于索引扫描的时间。所以在查询条件中尽

量避免计算条件。

（3）说说sqlserver中的null，null在数据库中表示不存在，与C#中的null不同，不表示空引用，没有对象，NULL的运算规则：有null的任何运算都是null。

is [not] null：只能用做条件判断表达式，是否是null？是条件为true，不是条件为false。

isnull()：函数，如果第一个参数是null，则用第二个参数的值替换第一个参数的值作为函数的返回值。记住：第二个参数的类型必须和第一个兼容。

nullif()：函数，如果两个参数值相等、有一个参数是null、或两个参数是null，函数返回值是null；否则返回第一个参数的值。

（4）top用法：意在取出表中满足条件的前多少位。top 10---前10位

说到top，突然想到了面试题中经常出现的查询某表中的前30—40条记录，注意id可能不连续。利用top可以这样写：

<span>1</span> <span>select</span> <span>top</span> <span>10</span> <span>*</span> <span>from</span> A <span>where</span><span> ID
</span><span>2</span> <span>not</span> <span>in</span>(<span>select</span> <span>top</span> <span>30</span> ID <span>from</span> A  <span>order</span> <span>by</span> ID <span>asc</span><span>)
</span><span>3</span> <span>order</span> <span>by</span> ID <span>asc</span>

登入後複製

同时也可以采用如下写法，只不过可读性比较差：

<span>1</span> <span>select</span> <span>top</span> <span>10</span> <span>*</span> fron A <span>where</span> ID<span>></span>
<span>2</span> (<span>select</span> <span>Max</span>(ID) <span>from</span> (<span>select</span> <span>top</span> <span>30</span> ID <span>from</span> A <span>order</span> <span>by</span> ID)<span>as</span><span> t)
</span><span>3</span> <span>order</span> <span>by</span> ID <span>asc</span>

登入後複製

当然既然有范围in存在，就可以用exist实现：

<span>1</span> <span>select</span> <span>top</span> <span>10</span> <span>*</span> <span>from</span><span> A a1 
</span><span>2</span> <span>WHERE</span> <span>NOT</span> <span>EXISTS</span> 
<span>3</span> (<span>SELECT</span> <span>*</span> <span>from</span> 
<span>4</span> (<span>SELECT</span> <span>TOP</span> <span>30</span> <span>*</span> <span>FROM</span> A <span>ORDER</span> <span>BY</span> id <span>asc</span><span>) a2
</span><span>5</span> <span>WHERE</span> a2.id <span>=</span><span>a1.id 
</span><span>6</span> )

登入後複製

但是目前需要考虑到----相关子查询：主查询每遍历一条记录时，都要针对主查询的值执行子查询，所以效率比较低。

下面介绍一下top与percent联合使用，percent表示所占的百分比：例如查询雇员表里面，前面百分之二十的雇员的信息，可以写sql，查询结果为两人。

<span><span>1</span> <span>select</span> <span>top</span>(<span>20</span>) <span>percent</span> <span>*</span> <span>from</span> hr.employees  </span>

登入後複製

SQLServer学习笔记系列1

我们在查询一下hr.employees（雇员表），同时查询一下雇员表里面总共有多少人，查出结果显示有9人。

<span>1</span> <span>select</span> <span>count</span>(<span>*</span>) <span>as</span> N<span>'</span><span>总人数</span><span>'</span> <span>from</span> hr.employees

登入後複製

SQLServer学习笔记系列1

可以看出，9个人按百分之二十取整数了，所以查出来的显示有两个人。

（5）with ties附加属性：

当我们查询订单表时，查询sql：

<span>1</span> <span>select</span><span> orderid,orderdate
</span><span>2</span> <span>from</span> sales.orders <span>order</span> <span>by</span> orderdate  <span>desc</span>

登入後複製

SQLServer学习笔记系列1

加入我们查询前五个订单信息时候，加入top 5

<span>1</span> <span>select</span> <span>top</span> <span>5</span><span> orderid,orderdate
</span><span>2</span> <span>from</span> sales.orders <span>order</span> <span>by</span> orderdate  <span>desc</span>

登入後複製

查询结果如图：

SQLServer学习笔记系列1

对比没有加top 5，查询结果截取了前五条订单信息，但是有时候我们需要将与最后一条订单日期相同的一起取出来，此时就需要采用附加属性with ties。

SQLServer学习笔记系列1

（6）over开窗函数：

上面讲到要用count聚合函数，在需要分组求和。但采用over 则可以同样实现基于什么的求和。省去group by。

<span>1</span> <span>select</span> firstname,lastname ,<span>count</span>(<span>*</span>) <span>over</span>()  <span>as</span> N<span>'</span><span>总人数</span><span>'</span>
<span>2</span> <span>from</span> hr.employees

登入後複製

SQLServer学习笔记系列1

其中over（），括号里面可以附加条件，基于什么进行汇总。不添加，则表示对所有的记录进行汇总。例如求每位顾客所消费的订单总额，可以这样写：

<span>1</span> <span>select</span> orderid,custid,<span>sum</span>(val) <span>over</span> (partition <span>by</span> custid) <span>as</span> N<span>'</span><span>顾客消费总额</span><span>'</span><span>,
</span><span>2</span> <span>sum</span>(val) <span>over</span>() <span>as</span> N<span>'</span><span>订单总额</span><span>'</span> <span>from</span> sales.ordervalues

登入後複製

SQLServer学习笔记系列1

五.排名函数

（1）row_number，行号，一般与over联合使用。over基于什么排名。

<span>1</span> <span>select</span> row_number() <span>over</span>(<span>order</span> <span>by</span> lastname) <span>as</span> N<span>'</span><span>行号</span><span>'</span><span>, lastname,firstname
</span><span>2</span> <span>from</span> hr.employees

登入後複製

SQLServer学习笔记系列1

（2）rank ，排名,真正意义上的排名，例如：

<span>1</span> <span>select</span> country,row_number() <span>over</span>(<span>order</span> <span>by</span> country) <span>as</span> N<span>'</span><span>rank排名</span><span>'</span><span>, lastname,firstname
</span><span>2</span> <span>from</span> hr.employees

登入後複製

SQLServer学习笔记系列1

可以看出，根据country排名，确实排出来啦，但是发现前四位同为UK，按理来说使部分先后顺序的，所以在此可以用rank来操作。

<span>1</span> <span>select</span> country,rank() <span>over</span>(<span>order</span> <span>by</span> country) <span>as</span> N<span>'</span><span>rank排名</span><span>'</span><span>, lastname,firstname
</span><span>2</span> <span>from</span> hr.employees

登入後複製

SQLServer学习笔记系列1

可以看出来，使用rank以后，country同为UK的并列第一，类似于学生考试成绩排名并列第一的情况。

（3）dense_rank,密集排名

通过上面rank排名以后，存在并列第一的情况，但是country为USA的应该为第二，所以就出现了使用密集排名dense_rank进行排名。

<span>1</span> <span>select</span> country,dense_rank() <span>over</span>(<span>order</span> <span>by</span> country) <span>as</span> N<span>'</span><span>dense_rank排名</span><span>'</span><span>, lastname,firstname
</span><span>2</span> <span>from</span> hr.employees

登入後複製

SQLServer学习笔记系列1

可以看出采用dense_rank以后，就满足了某一条件下，同属一个名次的需求。

（4）分组ntile。按某一条件进行分组。

<span>1</span> <span>select</span> country,ntile(<span>3</span>) <span>over</span> (<span>order</span> <span>by</span> country) <span>as</span> N<span>'</span><span>ntile分组</span><span>'</span>,dense_rank() <span>over</span>(<span>order</span> <span>by</span> country) <span>as</span> N<span>'</span><span>dense_rank排名</span><span>'</span><span>, lastname,firstname
</span><span>2</span> <span>from</span><span> hr.employees
</span><span>3</span> <span>order</span> <span>by</span> country

登入後複製

SQLServer学习笔记系列1

有时候为了在某一个范围内进行排序，比如：

<span>1</span> <span>select</span> lastname,firstname,country,row_number() <span>over</span>( <span>order</span> <span>by</span> country) <span>as</span> N<span>'</span><span>排名</span><span>'</span>
<span>2</span> <span>from</span> hr.employees

登入後複製

SQLServer学习笔记系列1

为了实现根据在country范围内排序，即country为Uk的为一组进行排序，country为USA的为一组进行排序。可以这样写：

<span>1</span> <span>select</span> lastname,firstname,country,row_number() <span>over</span>( partition <span>by</span> country <span>order</span> <span>by</span> country) <span>as</span> N<span>'</span><span>排名</span><span>'</span>
<span>2</span> <span>from</span> hr.employees

登入後複製

SQLServer学习笔记系列1