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脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
R.E.P.O.能量晶体解释及其做什么（黄色晶体）
2 周前
By 尊渡假赌尊渡假赌尊渡假赌
仓库：如何复兴队友
4 周前
By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒险：如何获得巨型种子
3 周前
By 尊渡假赌尊渡假赌尊渡假赌
击败分裂小说需要多长时间？
3 周前
By DDD
R.E.P.O.保存文件位置：在哪里以及如何保护它？
3 周前
By DDD
热工具
记事本++7.3.1
好用且免费的代码编辑器
SublimeText3汉化版
中文版，非常好用
禅工作室 13.0.1
功能强大的PHP集成开发环境
Dreamweaver CS6
视觉化网页开发工具
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
sqlserver数据库中已存在名为的对象怎么解决
Apr 05, 2024 pm 09:42 PM
对于 SQL Server 数据库中已存在同名对象，需要采取以下步骤：确认对象类型（表、视图、存储过程）。如果对象为空，可使用 IF NOT EXISTS 跳过创建。如果对象有数据，使用不同名称或修改结构。使用 DROP 删除现有对象（谨慎操作，建议备份）。检查架构更改，确保没有引用删除或重命名的对象。
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 10:00 PM
当 SQL Server 服务无法启动时，可采取以下步骤解决：检查错误日志以确定根本原因。确保服务帐户具有启动服务的权限。检查依赖项服务是否正在运行。禁用防病毒软件。修复 SQL Server 安装。如果修复不起作用，重新安装 SQL Server。
怎么查看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 11:27 PM
如果 SQL Server 安装失败，可通过以下步骤清理：卸载 SQL Server删除注册表项删除文件和文件夹重启计算机
sqlserver英文安装怎么更改中文
Apr 05, 2024 pm 10:21 PM
SQL Server 英文安装可通过以下步骤更改为中文：下载相应语言包；停止 SQL Server 服务；安装语言包；更改实例语言；更改用户界面语言；重启应用程序。
