SQLServer学习笔记系列1
sqlserver
前言
學習
筆記
系列
自己
一.前言 一直自己没有学习做笔记的习惯，所以为了加强自己对知识的深入理解，决定将学习笔记写下来，希望向各位大牛们学习交流！不当之处请斧正！在此感谢！ 这边就先从学习Sqlserver写起，自己本身对数据库方面不擅长，所以决定对此从基础开始学习，大牛们
一.前言
一直自己没有学习做笔记的习惯，所以为了加强自己对知识的深入理解，决定将学习笔记写下来，希望向各位大牛们学习交流！不当之处请斧正！在此感谢！
这边就先从学习Sqlserver写起，自己本身对数据库方面不擅长，所以决定对此从基础开始学习，大牛们对此文可以忽略！首先以《Sqlserver2008技术内幕》
这本书作为学习的指导，大家如果觉得这本书不错的话，可以去网上买一本，作为菜鸟的我，觉得这本书对于入门介绍的还是非常不错的。请戳
我：http://item.jd.com/10067484.html#none。
二.Sqlserver基础知识
（1）创建数据库
创建数据库有两种方式，手动创建和编写sql脚本创建，在这里我采用脚本的方式创建一个名称为TSQLFundamentals2008的数据库。脚本如下：
<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>
登入後複製
同时往数据库表插入一些数据，用户后续对数据库的sql的练习。在这里有需要的可以下载相应的脚本进行数据库的初始化。我放到百度云上面，请戳
我：http://yun.baidu.com/share/link?shareid=3635107613&uk=2971209779，提供了《Sqlserver2008技术内幕》这本书的电子版和脚本。
（2）在这里对TSQLFundamentals2008数据各个表进行表说明一下：
数据库表界面如下：
|
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
登入後複製
当要查询的字段不包含在group by子句中，则会报相应的错误，所以此时要注意出现在select 后面的查询字段进行分组后，也同时需要出现在group by后面。
（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>
登入後複製
可以看到查询结果将1973年的雇员信息查出来了，但是大家可以思考一下，上面的sql语句在查询的时候，首先是要讲birthdate进行取出年度的计算，
Year（birthdate），其中Year为sql的内置函数，可以用于对字符串日期进行取出年份的计算。同时我们还可以采用下面的sql语句进行查询：
通过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>
登入後複製
我们在查询一下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
登入後複製
可以看出，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>
登入後複製
加入我们查询前五个订单信息时候，加入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>
登入後複製
查询结果如图：
对比没有加top 5，查询结果截取了前五条订单信息，但是有时候我们需要将与最后一条订单日期相同的一起取出来，此时就需要采用附加属性with ties。
（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
登入後複製
其中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
登入後複製
五.排名函数
（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
登入後複製
（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
登入後複製
可以看出，根据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
登入後複製
可以看出来，使用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
登入後複製
可以看出采用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
登入後複製
有时候为了在某一个范围内进行排序，比如：
<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
登入後複製
为了实现根据在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
登入後複製
希望各位大牛给出指导，不当之处虚心接受学习！谢谢！
本網站聲明
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
Video Face Swap
使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！
熱門文章
<🎜>：種植花園 - 完整的突變指南
3 週前
By DDD
<🎜>：泡泡膠模擬器無窮大 - 如何獲取和使用皇家鑰匙
3 週前
By 尊渡假赌尊渡假赌尊渡假赌
如何修復KB5055612無法在Windows 10中安裝？
3 週前
By DDD
北端：融合系統，解釋
3 週前
By 尊渡假赌尊渡假赌尊渡假赌
Mandragora：巫婆樹的耳語 - 如何解鎖抓鉤
3 週前
By 尊渡假赌尊渡假赌尊渡假赌
熱工具
記事本++7.3.1
好用且免費的程式碼編輯器
SublimeText3漢化版
中文版，非常好用
禪工作室 13.0.1
強大的PHP整合開發環境
Dreamweaver CS6
視覺化網頁開發工具
SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)
sqlserver怎麼匯入mdf文件
Apr 08, 2024 am 11:41 AM
匯入步驟如下：將 MDF 檔案複製到 SQL Server 的資料目錄（通常為 C:\Program Files\Microsoft SQL Server\MSSQL\DATA）。在 SQL Server Management Studio（SSMS）中，開啟資料庫並選擇「附加」。點選“新增”按鈕，選擇 MDF 檔案。確認資料庫名稱，點選確定按鈕即可。
sqlserver資料庫中已存在名為的物件怎麼解決
Apr 05, 2024 pm 09:42 PM
對於 SQL Server 資料庫中已存在同名對象，需要採取下列步驟：確認物件類型（表格、檢視、預存程序）。如果物件為空，可使用 IF NOT EXISTS 跳過建立。如果物件有數據，使用不同名稱或修改結構。使用 DROP 刪除現有物件（謹慎操作，建議備份）。檢查架構更改，確保沒有引用刪除或重新命名的物件。
怎麼查看sqlserver連接埠號
Apr 05, 2024 pm 09:57 PM
若要查看 SQL Server 連接埠號碼：開啟 SSMS，連線到伺服器。在物件資源管理器中找到伺服器名稱，右鍵單擊它，然後選擇“屬性”。在「連線」標籤中，查看「TCP 連接埠」欄位。
sqlserver誤刪資料庫怎麼恢復
Apr 05, 2024 pm 10:39 PM
若誤刪 SQL Server 資料庫，可採取下列步驟還原：停止資料庫活動；備份日誌檔案；檢查資料庫日誌；復原選項：從備份還原；從交易日誌還原；使用 DBCC CHECKDB；使用第三方工具。請定期備份資料庫並啟用交易日誌以防止資料遺失。
sqlserver資料庫在哪裡
Apr 05, 2024 pm 08:21 PM
SQL Server 資料庫檔案通常儲存在下列預設位置：Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data可透過修改資料庫檔案路徑設定來自訂資料庫檔案位置。
sqlserver服務無法啟動怎麼辦
Apr 05, 2024 pm 10:00 PM
當 SQL Server 服務無法啟動時，可採取下列步驟解決：檢查錯誤日誌以確定根本原因。確保服務帳戶具有啟動服務的權限。檢查依賴項服務是否正在執行。禁用防毒軟體。修復 SQL Server 安裝。如果修復不起作用，重新安裝 SQL Server。
sqlserver安裝失敗怎麼樣刪除乾淨
Apr 05, 2024 pm 11:27 PM
如果 SQL Server 安裝失敗，可透過下列步驟清理：解除安裝 SQL Server刪除註冊表項刪除檔案和資料夾重新啟動計算機
sqlserver刪除不乾淨無法重新安裝怎麼辦
Apr 05, 2024 pm 11:30 PM
SQL Server 刪除不乾淨導致無法重新安裝的問題可以透過以下步驟解決：手動刪除檔案和登錄項目；使用SQL Server 安裝卸載工具；使用第三方卸載工具；檢查Windows 事件檢視器；重新啟動電腦；重新安裝SQL Server。
