系統城裝機大師 - 唯一官網:www.farandoo.com!

當前位置:首頁 > 數據庫 > MsSql > 詳細頁面

數據庫介紹及SQL語句及視圖、事務

時間:2020-05-15來源:電腦系統城作者:電腦系統城

數據庫介紹

  • 數據庫DataBase

用于存儲和管理數據的倉庫

  • 特點
  1. 持久化存儲數據,數據庫就是一個文件系統
  2. 方便存儲和管理數據
  3. 使用統一操作方式
  • 常見的數據庫軟件:
  1. Oracel
  2. MySQL
    MySQL圖形化工具:SQLYog
  3. SQL Sever(微軟中型數據庫)
  4. DB2(IBM公司的)
  5. SQLite(嵌入式的小型數據庫,應用在手機端)
  • MySQL目錄結構:
  1. 數據庫:文件夾
  2. 表:文件
  3. 數據:數據
  • 關系型數據庫多表之間的關系
  1. 一對一(了解):
  2. 一對多(多對一):外鍵在多中,是一的主鍵
  3. 多對多: 學生 老師 必須新建一張表 實現關系:新建一張表

SQL

SQL定義

SQL:結構化查詢語言
定義了操作所有關系型數據庫的規則

  1. 以分號結尾
  2. 使用空格、縮進提高可讀性
  3. MySQL數據庫的SQL不區分大小寫
  4. 三種注釋: 單行:-- 注釋內容或# 注釋內容 多行:/注釋/

SQL的分類:

DDL(數據定義語言:操作數據庫和表)

  1. 操作數據庫:CRUD
  • C(Create):創建
	create database 名稱;
	create database if not exists 名稱;

--指定字符集為gbk:

	create database 名稱 character set gbk;
  • R(Retrieve):查詢
--查詢所有數據庫的名稱:
	show databases;
--查詢某個數據庫的字符集(查詢某個數據庫的創建語句):
	show create database 數據庫名稱;
  • U (Update):修改
--修改數據庫的字符集
	alter database 數據庫名稱 character set 字符集名稱;
  • D (Delete):刪除
--刪除數據庫
		drop database 數據庫名稱;
--判斷數據庫是否存在,存在再刪除
		drop database if exists 數據庫名稱;
  • 使用數據庫
--查詢當前正在使用的數據庫名稱
	select database()
--使用數據庫
	use 數據庫名稱;
  1. 操作表
  • C(Create):創建
    1. 語法:
create table 表名(列名1 數據類型1,
				  列名2 數據類型2,	
);

數據庫類型:
1. int:整數類型
2. double:小數類型
3. date:日期,只包含年月日
4. datetime日期:包含年月日秒
5. timestamp:時間錯類型
6. 字符串類型:varchar

  • R(Retrieve):查詢
--查詢某個數據庫中所有的表名稱
	show tables;
--查詢表結構
	desc 表名;
  • U (Update):修改
  1. 修改表名
alter table 表名 rename to 新的表名;
  1. 修改表的字符集
--首先學會查看 
	show create table 表名;
alter table 表名 character set 字符集名稱;
  1. 添加一列
alter table 表名  add 列名 數據類型; 
  1. 修改列的名稱、類型
alter table 表名 change 列名 新列名 新數據類型;
alter table 表名 modify 列名 新數據類型;
  1. 刪除列
alter table 表名 drop 列名;
  • D (Delete):刪除
drop table 表名;
drop table if exists 表名;
  • 約束:
    概念:對表中的數據限定,保證正確性、有效性、 完整性; 分類:
  1. 主鍵約束:primary key
  • 注意:
  1. 含義:非空且唯一
    2. 一張表只能有一個字段為主鍵
    3. 主鍵就是表中記錄的唯一標識
  • 在創建表時,添加主鍵約束
create table stu(
	id int primary key,
	name varchar(20)
);
  • 刪除主鍵
alter table stu drop  primary key;
  • 創建完表后,添加主鍵約束
alter table stu modify id int primary key;
  • 自動增長
  1. 概念:如果某一列是數值類型的,使用auto_increment可以來完成自動增長;
    2. 在創建表時,添加主鍵約束,并且完成主鍵自增長:
    3. 刪除
    alter table stu modify ID int;

增加知識點:identity【主鍵自動增長,用戶不需要為identity修飾的主鍵賦值】 ...primary key
identity(100,5);--id從100開始,以5遞增,可以并不設置參數,為1,1;

  1. 非空約束:not null
  • 創建表時添加約束;
  • 創建表完后,添加非空約束;
  • 刪除非空約束;
  1. 唯一約束:unique,值不能重復
  • 刪除唯一約束;
alter table 表名 drop index 列名;
  1. 外鍵約束:foreign key
  • 創建表時,可以添加外鍵;
    語法:
create  table 表名(
...
外鍵列
constraint 外鍵名稱 foreign key (外鍵名稱) references 主表名稱(主表列名稱) 			);
  • 刪除外鍵
alter table   表名  drop foreign key   外鍵名稱  ;
  • 創建表之后添加外鍵
alter table   表名  add constraint 外鍵名稱 foreign key (外鍵名稱) references 主表名稱(主表列名稱);

DML(數據操縱語言:操作數據(增刪改))

  • 添加數據
    語法:
insert into 表名(列名1,列名2...列名n) values(值1,值2...值n);

注意事項:
1. 列名和值要一一對應;
2. 如果表名后,不定義列名,則默認給所有列添加值
3. 除了數字類型,其他類型需要使用引號引起來

  • 刪除數據
    語法:
delete from 表名 where 條件;

注意:
1. 如果不加條件,則刪除表中所有記錄
2. truncate table 表名; -----效率更高,刪除表,再創建一個一模一樣的空表

  • 修改數據
    語法:
update 表名 set 列名1=值1,列名2=值2... where 條件;

注意事項:
如果不加任何條件,則會將表中所有記錄修改;

DQL(查詢表中的數據記錄)(重點)

--復雜查詢語法:
	select 
		字段列表
	from
		表名列表
	where
		條件列表
	group by
		分組字段
	having  
		分組之后的條件
	order by
		排序
	limit
		分頁限定

查詢:

  1. 計算列
--查詢所有
select * from 表名;
	如:select * from banji;

--as可以起別名		
select banji_id as "班級號",banji_name as "班級名" from banji;
select banji_id,banji_name,banji_num*12 as "年薪",banji_name as "班級名" from banji;

--輸出的行數是banji表的行數,每行只有一個字段,值是4		
select 4 from banji;         
		
  1. distinct【不允許重復的】消除重復(多個NULL也可以過濾掉)
--如:
select jiaoshi_id from banji_jiaoshi_mapping;
select distinct jiaoshi_id from banji_jiaoshi_mapping;
	--若查詢兩列,則會把組合過濾,distinct放在兩列之前,不能放在中間,要不然會邏輯錯誤 
  1. between
	--如:select * from banji 
	where banji_id>=1 and banji_id <=2;
--等價于
select * from banji
	where banji_id between 1 and 2;

select * from banji
	where banji_id <=1 or banji_id>2;
--等價于
select * from banji
	where banji_id not between 1 and 2;
  1. in[屬于若干個孤立的值]
	如: select * from banji
	where banji_id in (2,4);
--等價于
select * from banji
	where banji_id=2 or banji_id=4;
	
	select * from banji
	where banji_id not in (2,4);
--等價于
select * from banji
	where banji_id!=2 and banji_id!=4;
  1. top
	select top 2 * from banji;

select top 100 percent * from banji; --百分比

	select top 3 * from banji
	where banji_num between 1 and 60
	order by banji_num desc;  --降序

	select top 3 * from banji
	where banji_num between 1 and 60
	order by banji_num;   --不寫默認升序
  1. null[空值]
select * from banji;
select * from banji where banji_num <> null;  --error
select * from banji where banji_num !=null;  --error
select * from banji where banji_num =null;   --error
--null不能參與<>   !=   =運算
--null可以參與is    not is
select * from banji where banji_num  is null;
select * from banji where banji_num  is not null;
  1. order by【以某個字段排序】默認升序asc
select * from banji order by banji_num;--默認升序排列
select * from banji order by banji_num desc;--降序

--出現多列是按一列排完排另一列,不是按組合排序
select * from banji order by banji_num,banji_id ;
select * from banji order by banji_id,banji_num ;
--asc是升序的意思,默認可以不寫,desc是降序
select * from banji order by banji_num desc,banji_id ;--desc只對banji_num產生影響
select * from banji order by banji_num ,banji_id  desc;--desc對banji_id產生影響

強烈建議為每一個字段都制定排序的標準
  1. 模糊查詢【匹配的條件必須用單引號括起來】
	/*格式:select 字段的集合 from 表名 where 某個字段的名字 like 匹配的條件
	通配符:
		% :表示任意0個或多個字符
	    _:[下劃線] 任意的單個字符
	    [a-f]: a到f的任意單個字符 只能是其中的任意一個字符  
	    [a,f] : a或f 
	    [^a-c]:不是a 也不是b也不是c的任意字符*/

select * 
	from banji 
	where banji_name like '二%'

select * 
	from banji 
	where banji_name like '二_'

select * 
	from banji 
	where banji_name like '[^a-c]_'

select * 
	from banji 
	where banji_name like '%\二%' escape '\' --表示\后面的字符就是普通的字符
  1. 聚合函數【多行記錄返回一個值,通常用于統計分組的信息】
  1. 函數分類:單行函數:每行返回一個值

  2. 多行函數:多行返回一個值

  3. 聚合函數是多行函數

  4. 單行函數和多行函數一起用出錯

    如:聚合函數的分類:
    1.max()

    1. min()
    2. avg():平均值
    3. count():求個數 count(*)返回表中所有記錄的個數 count(字段名):返回字段值非空的記錄個數,重復的記錄有效

可能遇到的問題: select max(banji_num),min(banji_num),count(*) from banji;

  1. group by
use student;
select * from banji
alter table  banji add sal int;
update banji set sal=4000;
update banji set sal=5000 where banji_id in (2,4,6);
select dept,avg(sal)as "平均工資" from banji group by dept;

--使用了group by之后只能顯示分組后的整體信息,不能出現組內詳細信息
  1. having【對分組后的信息過濾】
  • having使用時通常先使用group by
  • 如果沒使用group by使用having意味著把所有記錄當做一組來過濾
  • having子句出現的字段必須是分組之后組的整體信息,不是詳細信息
  • having中不能使用字段的別名
  • having和where區別: 相同:都是對數據過濾,只保留有效的數據 不同:where是對原始的記錄的過濾,having是對分組之后的記錄過濾
--如:
select dept,avg(sal)as "平均工資" from banji group by dept having avg(sal)>3000 order by dept desc;

--參數的位置不可以變
  1. 連接查詢
    定義:將兩個或多個表以一定條件連接起來從中檢索出滿足條件的數據

分類:

  • 內連接
  1. select ... from A,B 的用法
select * from jiaoshi;
select * from banji;
update banji set banji_name='六班' where banji_id=6;

select * from banji,jiaoshi;-- 產生4*6 行臨時表,行數相乘,列數相加,笛卡爾積
  1. select ... from A,B where ... 的用法
select * from banji,jiaoshi where banji_id=1;--產生4*1行
--對	select  ... from A,B where ... 產生的笛卡爾積用where中的條件進行過濾
  1. select ... from A join B on ...的用法
select banji.sal from banji join jiaoshi on banji_id in(1,2);--join是連接,on是連接條件,必須是組合
  1. select ... from A,B where ...與
    select ... from A join B on ...的比較
  • 分別是SQL92和99標準;輸出結果是一樣的,推薦SQL99標準
    • SQL99更容易理解
    • SQL99標準中,on和where可以做不同的分工 on指定連接條件,where可以對連接后過濾
  1. select、 from、 where、 join、 on、 group、 order、 top 、having的混合使用
--查詢的順序:
select top...
	from A
	join B
	on ...
	join C
	on ...
	where ...
	group by ...
	having ...
	order by ...
  • 外連接【返回滿足連接條件的記錄和不滿足條件的部分記錄】
  • 內連接默認inner join 分類:
  • 左外連接:返回滿足連接條件的記錄和左表不滿足條件的部分記錄 left join 匹配則輸出,不匹配就用第幾行做匹配輸出第幾行,其中左邊為左表,右邊為null
    實際意義:返回一個事物的相關信息,如果該事物沒相關信息,則輸出null;如:倉庫訂單
  • 右外連接:返回滿足連接條件的記錄和右表不滿足條件的部分記錄 right join 匹配則輸出,不匹配就用第幾行做匹配輸出第幾行,其中右邊為右表,左邊為null
  • 完全連接
    • full join
      左外連接+右外連接-所有正常記錄=完全連接
      交叉連接
    • cross join結果是笛卡爾積,等價于什么也不寫
  • 自連接
    一張表自己和自己連接起來查詢數據
    起兩個不同的別名
    子查詢返回的值只有一個
  1. 聯合 :union,兩個select中使用

定義:表和表之間的數據以縱向的方式連接在一起 以前的所有的連接是以橫向的方式連接;
注意:要聯合成功,列數必須是相等的,類型是兼容的

  1. 分頁:
--如:
select top 4 * from banji
	where banji_num not in (select top 6 banji_id from banji order by banji_num desc)
	order by banji_num desc;
 
  1. 公式:開始的索引=(當前的頁碼-1)*每頁顯示的條數
  2. 分頁操作是一個“方言”
  1. 嵌套查詢

視圖

為什么需要視圖: 簡化查詢,避免了代碼的冗余,避免書寫大量重復的SQL語句.

什么是視圖?視圖邏輯上當做一張虛擬表看待,

視圖的格式:

create view 視圖的名字
as
	--select前面不能添加begin
	select語句
	--select后面不能添加end

視圖的優點:簡化查詢、增加數據的保密性
視圖的缺點:增加了數據庫維護的成本 視圖只能簡化查詢,但并不能加快查詢的速度

注意問題:1. 創建視圖的select語句必須為所有的計算列指定別名
2. 視圖不是物理表,是虛擬表
3. 不建議通過視圖更新視圖所依附的原始表的數據

事務

事務要么全部執行成功,要么全部執行失敗

為什么需要事務? 事務主要用來保證數據的合理性和并發處理的能力; 如:銀行轉賬、賣票

事務研究的問題

  1. 避免數據處于不合理的中間狀態
  2. 怎樣避免多用戶同時訪問是數據呈現給用戶的數據是合理的 事務和線程的關系: 事務也是通過鎖來解決很多問題的 線程同步就是通過鎖來解決的 synchronized

事務和第三方插件的關系 直接使用事務庫技術難度大,所以大多使用第三方插件庫來實現
第三方插件要想完成預期的功能,一般必須借助數據庫的事務機制

T-SQL使用下列語句管理事務:
開始事務:begin transaction
提交事務:commit transaction
回滾(撤銷)事務:rollback transaction

TL-SQL:速度快,安全性好;難移植(也可以使用Java在編譯器去實現)

一旦事務提交或者回滾,則事務結束

事務三種運行模式:
1. 自動提交事務
2. 顯式事務 每個事務均以begin transaction開始,以commit或rollback顯式結束
3. 隱形事務 在前一個事務完成時新事務隱式啟動,但每個事務仍以commit或rollback語句結束

事務的四大屬性:
1. 事務是一個完整的操作,事務的各步操作不可再分
2. 當事務完成時,數據必須處于一致狀態
3. 當前事務與其他未完成的事務是隔離的
4. 事務完成后,事務日志能夠保持事務的永久性

分享到:

相關信息

系統教程欄目

欄目熱門教程

人氣教程排行

站長推薦

熱門系統下載

jlzzjlzz亚洲乱熟在线播放