sqlserver提高篇

来源:http://www.cnblogs.com/julinhuitianxia/archive/2017/05/16/6864282.html
-Advertisement-
Play Games

Microsoft SQL Server2008複習提高 一．Microsoft SQL Server 系統的體繫結構 1.Microsoft SQL Server2008由4個主要的部分組成，即4個服務： 資料庫引擎、分析服務、報表服務、集成服務。如下圖： 2.Microsoft SQL Serv ...


Microsoft SQL Server2008複習提高

一．Microsoft SQL Server 系統的體繫結構

1.Microsoft SQL Server2008由4個主要的部分組成，即4個服務：

  資料庫引擎、分析服務、報表服務、集成服務。如下圖：

 

2.Microsoft SQL Server2008提供了兩種類型的資料庫:系統資料庫和用戶資料庫。

系統資料庫包括：master、model、msdb、Resource和tempdb資料庫；

用戶實例資料庫包括：AdventureWorks、AdventureWorksDW、AdventureWorksDW2008、AdventureWorksLT、AdventureWorksLT2008等資料庫。

Master資料庫是最重要的系統資料庫，它記錄了伺服器配置信息、登錄帳戶信息、資料庫文件信息、SQL Server初始化信息等。

Model 他是於SQLServerAgent服務有關的資料庫。該系統記錄了有關作業、警報、操作員、調度信息，這些信息可以用作自動化系統操作。

Tempdb 是一個臨時資料庫，用於存儲查詢過程中的中間數據和結果。

3.OLTP和OLAP

OLTP是傳統的關係型資料庫的主要應用，主要是基本的、日常的事務處理，例如銀行交易。OLAP是數據倉庫系統的主要應用，支持複雜的分析操作，側重決策支持，並且提供直觀易懂的查詢結果.

4.資料庫對象類型主要包括了：

資料庫關係圖、表、視圖、同義詞、可編程性、Service Broker、存儲和安全性等。

5.SQL Server Profilter（照相）

它主要用於從伺服器中捕獲Sqlserver2008事件的工具。

6.資料庫引擎優化顧問：

這個工具可以幫助用戶分析工作負荷、提出創建高效率索引的建議等功能。他就是一個優化大師，儘可能的提高系統效率。

7.資料庫管理員DBA

職責：保證系統正常高效的運行。

 

二．管理安全性

  1. 管理登錄名

包括：創建登錄名、設置密碼策略、查看登錄名信息及修改和刪除登錄名等。

登錄策略：windows身份驗證和sqlserver身份驗證登錄。

比如：create login lin   from windows go//創建windows登錄名

      create login lin with password=‘sa’//創建sqlserver登錄名

  1. 管理資料庫用戶

資料庫用戶是資料庫級的主體，是登錄名在資料庫中的映射，實在資料庫中執行操作和活動的行動者。

在某個A資料庫中創建對應sa登錄名的用戶lin：

use A

create user lin from login sa  [with default_schema=架構名] go

3.管理架構

架構是形成單個命名空間的資料庫實體的集合。

如果一個用戶沒有制定架構名，則預設為dbo架構。

1)創建架構的sql:   create schema 架構名

2)創建某個架構屬於某個用戶：create schema 架構名 authorization 用戶名

3)創建某個架構屬於某個用戶同時創建表: create schema 架構名 authorization 用戶名 create table 表名 (………)

4.資料庫角色(許可權)

資料庫角色是資料庫級別的主體，也是資料庫用戶的集合。

1）創建簡單的角色：create role 角色名

2）創建帶所有者的角色：create role 角色名 authorization 用戶名

3）在角色中添加成員：sp_addrolemember(‘lin’,…)

5.管理許可權（放權和釋權）

授權：grand語句

釋權：revoke語句

拒絕：deny語句 通過繼承他人許可權則無法收回許可權，便可以用deny拒絕。

1)  將創建資料庫的許可權授予某個lin用戶：grant create table to lin [with grant option 表示可以將此許可權轉給他人]

2)  將lin在某張表的查詢許可權回收：revoke select on student from lin go

6. Microsoft SQL Server2008內置的加密機制

對稱加密和非對稱加密兩種。密鑰分為公鑰和私鑰。

對稱加密：

 

三.管理資料庫

1.資料庫文件和文件組的特征：

資料庫文件分為：主資料庫.mdf、次資料庫.ndf、日誌文件.ldf;

1)主資料庫.mdf包含資料庫的啟動信息，並指向資料庫中的其他文件。用

戶數據和對象可存儲在此文件中，也可以存儲在次要數據文件中。

每個資料庫有一個主要數據文件。主要數據文件的建議文件擴展名是 .mdf。

2) 次要數據文件是可選的，由用戶定義並存儲用戶數據。通過將每個文件放在不同的磁碟驅動器上，次要文件可用於將數據分散到多個磁碟上。另外，如果資料庫超過了單個Windows 文件的最大大小，可以使用次要數據文件，這樣資料庫就能繼續增長。次要數據文件的建議文件擴展名是 .ndf。

3)日誌文件

文件組：是文件的集合。

註意事項：①一個文件或者文件組只能用於一個資料庫，不能用於多個數②一個文件只能是某一個文件組的成員，不能是多個文件組的成員③資料庫的數據信息和日誌信息不能放在同一個文件或文件組中，應該分開④日誌文件永遠不能是任何文件組的一部分。

  1. 管理單位：管理的最小物理單位是以頁為單位的，每一個頁的大小為8KB.extend 64kb。
  2. 定義資料庫： create database 資料庫名

修改資料庫： alter database資料庫名

  1. 擴大資料庫方法：

1）通過增加新的文件來擴大資料庫。

例如：alter database 資料庫名  add file ( name=‘‘，filename=’’,size=’’,filegrowth=’’)

2）通過擴大資料庫文件的大小來擴大資料庫

例如：alter database 資料庫名 modify file(name=’’, size=’新值’)

6.三種主要收縮資料庫方法：

   1）設置資料庫為自動收縮；auto_shrink

   2)收縮整個資料庫的大小: dbcc shrinkdatabase

   3)收縮制定的數據文件: dbcc shrinkfile

7.管理資料庫快照:

概念: 資料庫某一瞬時的狀態.

1)創建資料庫快照的語法:   create database 快照名稱   on(name=’’,filename=’’, as snapshot of 源資料庫名稱)

2)刪除快照: drop database  快照名稱

8.RAID技術—獨立磁碟冗餘陣列：

RAID是一個磁碟系統，可以將多個磁碟驅動器合成一個磁碟陣列，以提供高性能、高可靠性及低成本。

1)  容錯等級: RAID0（數據並行）、 RAID1(設備鏡像)、 RAID5(奇偶信息並行存儲).

 

四．T-SQL語言

1.T-SQL語言的4個特點:  一體化（定義、操縱、控制、事務管理語言與一體）、兩種使用方式(互動式和嵌入式到高級語言中)、非過程化語言（告訴乾什麼就行）、類似人的語言，容易理解和掌握。

2.T-SQL語言的5個特點：數據定義語言、數據操縱語言、數據控制語言、事務管理語言和附加的語言元素。

3.重點說一下附加語言元素：

1）標識符格式規則:  規則一，第一個字元①Unicode標准定義的字母a-z、A-Z②下劃線_、符號@、數字元號#； 以一個符號@開頭的標識符表示局部變數，以兩個符號@@開頭表示內置的某些函數。以#開頭表示臨時表或臨時存儲過程，##開頭標識符表示全局臨時對象。

規則二：①跟第一字元規則一樣，多了數字。

規則三：不能是保留字；

規則四：不允許嵌入空格和其他特殊字元。

分割標識符：“”、[]

4.變數和常量：

變數以@開頭 定義變數如: declare @name varchar

用set為變數設置值

對於常量需要使用’’來使用。

  1. 控制流語言:

Begin…end 、break、goto、continue、if…else、 while、return、waitfor（懸掛起批處理、存儲過程、事務的執行）。

  1. 錯誤的捕捉語言:

Try…catchj構造和@@ERROR函數、ERROR_NUMBER(返回錯誤號)。

  1. 數據類型：

 

 

  1. 聚合函數和內置函數:

  SUM                                                             返回選取結果集所有值的和
   MAX                                                             返回選取結果集中所有值的最大值
   MIN                                                             返回選取結果集中所有值的最小值
   AVG                                                             返回選取結果集中所有值的平均值
   COUNT                                                          返回選取結果集中行的數目

  內置函數:

Convert                 Convert(varchar(10),123)結果返回“123”                   裝換數據類型
  DataLength              DataLength('12中國')結果返回6                           返回任何數據類型的位元組數。“中國”包含4個位元組，“12”包含兩個位元組，所以返回6.

 

日期和時間函數:

-時間函數

select  getDate() as currentTime;

select  GETUTCDATE() AS UTCtime;

select  DAY('2015-04-30 01:01:01');

SELECT  MONTH('2015-04-30')AS monthValue;

SELECT  YEAR('2015-04-30'),YEAR('1997-07-01');

 

SELECT DATENAME(year,'2015-04-30 01:01:01') AS yearValue,

       DATENAME(quarter,'2015-04-30 01:01:01') AS quaterValue,

       DATENAME(dayofyear,'2015-04-30 01:01:01') AS dayofyearValue,

       DATENAME(day,'2015-04-30 01:01:01') AS dayValue,

       DATENAME(week,'2015-04-30 01:01:01') AS weekValue,

       DATENAME(weekday,'2015-04-30 01:01:01') AS weekdayValue,

       DATENAME(hour,'2015-04-30 01:01:01') AS hourValue,

       DATENAME(minute,'2015-04-30 01:01:01') AS minuteValue,

       DATENAME(second,'2015-04-30 01:01:01') AS secondValue;

 

SELECT  DATEPART(year,'2015-04-30 01:01:01'),

        DATEPART(month,'2015-04-30 01:01:01'),

        DATEPART(dayofyear,'2015-04-30 01:01:01');

       

SELECT    DATEADD(year,1,'2015-04-30 01:01:01') AS yearAdd,

        DATEADD(month ,2, '2015-04-30 01:01:01') AS weekdayAdd,

        DATEADD(hour,2,'2015-04-30 01:01:01') AS hourAdd;

 

排名函數:

  • --創建測試數據表  
  • DECLARE @table TABLE  (姓名 VARCHAR(4),成績 INT)  
  • insert into @table  
  • SELECT '張三',129 UNION ALL  
  • SELECT '李四',137 UNION ALL  
  • SELECT '王二',137 UNION ALL  
  • SELECT '小明',126 UNION ALL  
  • SELECT '小六',126 UNION ALL  
  • SELECT '小白',125 UNION ALL  
  • SELECT '小黑',124 UNION ALL  
  • SELECT '西大',123 UNION ALL  
  • SELECT '小才',120 UNION ALL  
  • SELECT '師兄',120 UNION ALL  
  • SELECT '唐朝',99 UNION ALL  
  • SELECT '尼瑪',99  
  •     
  • --查看一下  
  • --SELECT * FROM @table  
  •    
  • SELECT 姓名,成績,  
  •   ROW_NUMBER()  OVER(ORDER  BY 成績 DESC) AS [ROW_NUMBER],  
  •   RANK()        OVER(ORDER  BY 成績 DESC) AS [RANK],  
  •   DENSE_RANK()  OVER(ORDER  BY 成績 DESC) AS [DENSE_RANK],  
  •   NTILE(6)      OVER(ORDER  BY 成績 DESC) AS [NTILE]  
  • FROM @table  

五．資料庫表

1.表的類型: 普通表、以分區表、臨時表、系統表。

2.創建表：create table 表名

3.計算列：使用persisted關鍵字，指定由該列保存計算得到的數據，因此該列是一個物理列，當該列所依賴的其他列中的數據發生變化時該列中的數據也會發生變化。

4.創建臨時表: #本地臨時表只能由創建著使用 、 ##全局臨時表，其生存期間可以為所有的用戶使用。

例如：create table ##student(…)

5.增加列和刪除列

Alert table 表名    add/drop  column_name 數據類型

6.修改該列的數據類型

Alter table table_name  alter COLUMN column_name new_type_name數據類型

7.創建和修改標識符列：

identity(seed, increment)

8.ROWGUIDCOL列

Uname unqiueidentityfier default NEWID() 這就一個ROWGUIDCOL列。

10.創建分區表步驟:

1).創建分區函數，指定如何分區。

2）創建分區方案，指定分區函數的分區在文件祖上的位置。

3）創建使用分區表方案的表。

例如：

創建分區函數：  create partition function saleAmountPF(INT)

                As rang left for values(10,100,1000,10000)

創建分區方案:   create partition schema saleAmountPS

                As partition saleAmountPF

                TO(saleFG1,saleFG2,…)

創建分區表:     create table salePT(

serolId int,  saleAmount int, saleDate datetime, salePerson varchar(32)  on saleAmountPF(saleamount)

)

六．操縱數據

 1.使用INSERT語句插入數據

Insert into 表名（欄位列…） values(值…)

如果是unicode數據，應該在字元數據的引號前使用N字元。

字元數據需要使用 引號 引起來，數字數據可以直接插入。

提供空值：NULL、default。

使用default values向表中提供一行全部是預設值。

使用bulk insert語句按照用戶指定的格式批量的插入數據。

2.使用update語句更新數據

Update 表名 set column_name=expression,….

Where 查詢條件

 

3.使用delete語句刪除數據

Delete from table_name where 刪除條件

delete操作只是刪除表中的數據，並沒有破壞表的結構。被刪除的數據會被記錄在日誌中。

Truncate table語句刪除表中的數據，系統會立即釋放表中的數據和索引所占的空間，並且不把這種數據的變化記錄在日中。

4.改變列標題 = 和 as

5.order by進行排序：可以使用多個列進行排序，如果第一個列的值相同，那麼就按照第二個列排序，每一個列都可以指定升序或降序。

Top：返回指定數量的數據行。用在分頁。

6.like進行模糊匹配 % _ [] [^]

7.Compute字句：  當使用聚合函數得到的結果集全是聚合值，沒有明細值，解決方法就是是喲哦那個compute字句。

兩種形式：一種帶by字句；一種不帶by字句。

例如：  select … from 表名  order by 排序欄位 compute sum(欄位)

select … from 表名  order by 排序欄位 compute sum(欄位) by 欄位

8.分組技術:group by子句

分為無條件的分組：

Select 欄位…from 表名  where 條件 group by 列名

有條件的分組:

Select 欄位…from 表名  where 條件 group by 列名 having 分組篩選條件

ROLLUP得到各組的單項組合和CUBE關鍵字得到各組之間的任意組合。

 

9.連接技術：交叉連接即笛卡兒積 cross join、內連接inner join 用on指定連接條件按、左外連接left outer join、有外連接right outer join、完全外連接full outer join

10.子查詢技術：where、group by、having

七．索引

1.索引的類型和特點

建立索引的優點：
1）.大大加快數據的檢索速度;
2）.創建唯一性索引,保證資料庫表中每一行數據的唯一性;
3）.加速表和表之間的連接;
4）.在使用分組和排序子句進行數據檢索時,可以顯著減少查詢中分組和排序的時間.
索引類型：
根據資料庫的功能,可以在資料庫設計器中創建四種索引：唯一索引、非唯一索引、主鍵索引和聚集索引.儘管唯一索引有助於定位信息,但為獲得最佳性能結果,建議改用主鍵或唯一約束.
唯一索引：
唯 一索引是不允許其中任何兩行具有相同索引值的索引.當現有數據中存在重覆的鍵值時,大多數資料庫不允許將新創建的唯一索引與表一起保存.資料庫還可能防止 添加將在表中創建重覆鍵值的新數據.例如,如果在 employee 表中職員的姓 (lname) 上創建了唯一索引,則任何兩個員工都不能同姓.
非唯一索引：
非唯一索引是相對唯一索引,允許其中任何兩行具有相同索引值的索引.當現有數據中存在重覆的鍵值時,資料庫是允許將新創建的索引與表一起保存.這時資料庫不能防止添加將在表中創建重覆鍵值的新數據.
主鍵索引：
資料庫表經常有一列或列組合,其值唯一標識表中的每一行.該列稱為表的主鍵.在資料庫關係圖中為表定義主鍵將自動創建主鍵索引,主鍵索引是唯一索引的特定類型.該索引要求主鍵中的每個值都唯一.當在查詢中使用主鍵索引時,它還允許對數據的快速訪問.
聚集索引（也叫聚簇索引）：
在聚集索引中,表中行的物理順序與鍵值的邏輯（索引）順序相同.一個表只能包含一個聚集索引.如果某索引不是聚集索引,則表中行的物理順序與鍵值的邏輯順序不匹配.與非聚集索引相比,聚集索引通常提供更快的數據訪問速度.

2.創建索引：create index語句

Create [unique唯一] [clustered聚集 | nonclustered非聚集] index index_name on table_or_view_name (列名)[include (列…)包含性]

例如: create unique clustered index index_xxx on tabl_xxx(…)

3.維護索引：dbccshow_statistics命令和圖形化工具。查看索引碎片信息:sys.dm_db_index_physical_stats系統函數和使用圖形化工具。

未完待續。。。。。。明天更新。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 仿拉手團購App5--登錄界面
    activity_login.xml 快速登錄(獲得驗證碼和登錄） 賬號登錄（登錄和切換密碼明文) ...
  • 仿拉手團購App4--我的模塊
    fragment_me.xml MeFragment.java ...
  • 又是一個MVP+RxJava+Retrofit的乾貨集中營
    今天想要與大家一起分享的是近一個星期內開發的一個新app——乾貨集中營客戶端，其實網上已經有許多類似的項目，代碼家也在他的乾貨集中營中推薦了幾款優秀的作品，我也借鑒了其中的一些，自己開發出一款乾貨集中營客戶端，目前項目已經發佈到github，如果你想瞭解整個工程的具體內容，那麼你可以fork或者cl... ...
  • 【開發時，應註意事項】 vendor tools 無法 work 時，怎麼辦？
    遇到 vendor tools 無法 work 時， 最好的方法直接請 vendor 來， 為什麼呢？ 因為 tool 可能 有版本的問題， 譬如： vendor tool A tool 在 build B 版本的 code 時，會發生 error ，需要打開某幾個 config。 vendor t ...
  • MySQL:校對規則
    校對規則：在當前編碼下，字元之間的比較順序。（cs大小寫敏感，ci大小寫不敏感，bin二進位編碼比較） 1. 查看當前校對規則 show collation; 每個字元集都支持不定數量的校對規則,下圖為查看結果的部分截圖： tip : 以big5_chinese_ci為例，其中big5表示字元集，c ...
  • 免安裝Oracle配置（instantclient_11_2）
    首先下載instantclient，可自行搜索引擎搜索下載。 下載完成解壓後 找到解壓的文件目錄下的network\admin路徑下的tnsnames.ora 文件 比如我解壓在c盤根目錄，那麼在C:\instantclient_11_2\network\admin在此路徑中找到tnsnames.o ...
  • VMWare linux安裝mysql 5.7.13
    1、基礎環境說明 虛擬機：VMWare 操作系統：linux 資料庫版本：mysql 5.7.13 社區版（別問為什麼不裝企業版，因為企業版要錢） 背景：虛擬機可以連上外網 2、摸底 查看linux上是否已經安裝了mysql ① 啟動服務 service mysqld start 沒有安裝，則提示 ...
  • .Net程式員學用Oracle系列(27)：PLSQL 之游標、異常和事務
    " 1、游標 " "1.1、游標屬性" "1.2、隱式游標" "1.3、游標處理及案例" " 2、異常 " "2.1、異常類別" "2.2、異常函數" "2.3、異常處理及案例" " 3、事務 " "3.1、開始事務、結束事務" "3.2、自治事務" "3.3、事務處理及案例" " 4、總結 " 1 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類