第三階段數據庫-2：數據庫中的sql語句

1_數據庫操作

（1）注釋：

-- 單行注釋
/**/ 多行注釋

（2）創建數據庫：create database 數據庫名

-- create database 數據庫名
create  database db_first;

(3）查詢數據庫：

if exsists(select * from sysdatabases where name='StudentManageDB')

（4）刪除數據庫：drop database 數據庫名

-- drop database 數據庫名
drop database  db_first;

（5）創建變量，可以聲明的時候賦值，也可以先聲明再賦值

declare  @currentPage int;
declare  @pageSize int;
set @currentPage=1;
set @pageSize=10;

2_數據表操作

（1）創建表：creat table 表明（列名，數據類型）

not null：設定該列非空，
primary key，設定該列為主鍵，
identity(1,1) 設定該列自增，從1開始自增，增幅為1

create table Teachers(Id int not null primary key identity(1,1),TeacherName varchar(30) ?not null,Age int ? not null  default(20),Sex int )

（2）查詢表

if exists (select * from sysobjects where name='StudentClass')

（3）刪除表

drop table StudentClass

3_插入數據

（1）表的插入：insert into 表名(列名) values (值)，

插入的時候表名后面填寫列名,values 后面填寫值值和列名要--對應

當主鍵設置了標識規范的時候,不讓插入,只有吧IDENTITY_INSERT設置為ON才能插入

數據庫對大小寫不敏感，IDENTITY_INSERT與identity_insert一樣

GETDATE() 是SQL_server 中得函數用來獲取當前日期

--插入
insert  into Teachers (TeacherName,Age) values ('孫老師',30)
insert  into Teachers (TeacherName,Age,Sex) values ('孫老師',20,0)
SET IDENTITY_INSERT  Teachers ON;
insert  into Teachers (Id,TeacherName,Age,Sex) values (6,'王老師',20,0)
SET IDENTITY_INSERT  Teachers OFF;
insert  into  Students(StuName,StuAge,Birthday) values ('張三',20,'2025-02-01 14:13:45');
insert  into  Students(StuName,StuAge,Birthday) values ('韓李四',20,GETDATE());
--插入多條數據
insert into  Teachers(TeacherName,Age)
--自定義結果集  開發者用多條數據合并而來
select '王老師1',30
union all
select '王老師2',30
union all
select '王老師3',30

4_修改數據

（1）修改數據：update 表名 set 修改的列名='值' where 條件

or 或者，滿足一項就可以，相當于C#中的||

and 并且滿足所有的條件相當于&&

不加修改條件的時候會修改所有的數據，切記加條件

update  Students set StuName='張三' where  Id=7;
update  Students set StuName='王大陸3',StuAge=10 where Id=6 or Id=5;  -- or  ===> ||
update  Students
set StuName='4563' 
where Id=6 and StuAge=100;  ---> and ===> &&

5_刪除數據：

delete from 表名：刪除數據，標識符不重置，可以使用where添加刪除條件

truncate table 表名：刪除清空數據保留表結構標識規范重置，不能添加where 謹慎使用

delete from  Teachers;
delete from  Teachers where  Id=2;
delete from  Teachers where  TeacherName ='孫老師';
truncate table Teachers  ;

6_查詢數據

6.1_基本查詢

（1）查詢語句會查到一個結果集把結果集返回出來

select * from 表明： * all 全部列

-- * all  全部列
select  * from  Students;

（2）查詢部分行,使用 'as' 或使用 '=' 重新命名字段

--查詢部分列
select  StuName,StuAge from  Students;
--  查詢替換列名
select TeacherName as 老師姓名,Age as  年齡 from Teachers
--使用等號重命名字段
select 出生年月=Birthday from Students where Gender='男'

（3）條件查詢，多個并列條件使用 and 連接，多個或條件使用 or 連接

--加 where 篩選  
select  * from  Students  where Id =1;--查詢id等于1的全部數據
select  StuName  from  Students  where Id =4; --查詢id等于4的學生姓名
select  StuName  from  Students  where Id =4  and StuAge=10;
select  StuName  from  Students  where Id =4  or StuAge=10;

（4）使用加號可以將多列數據顯示到同一列中

+ 連接的數據類型必須兼容
如果使用 + 連接字符型數據，結果為字符串數據的連接
如果使用 + 連接數值型數據，結果為數值的和

select 學號=StudentId,總成績=CSharp + SQLServer from ScoreList

（5）查詢空列

select * from ScoreList where SQLServer is null

（6）使用常量列：增加新的一列

select StudentName, Gender, Birthday, Age, StudentIdNo,學校='111' from Students where Gender='男' and Age > 24

（7）限制固定行數 top 頂端的，

top n 最上邊的n行數據
top 40 percent 返回百分之多少行

select top 4 StudentName, Gender, Birthday from Students
select top 40 percent StudentName, Gender, Birthday from Students

（8）排序

升序：asc 默認為升序排列，可省略

降序：desc

select StudentId, (CSharp + 5) as C#, DB=SQLServer 
from ScoreList 
where (CSharp + 5) > 80 
order by CSharp ASCselect StudentId, (CSharp + 5) as C#, DB=SQLServer 
from ScoreList 
where (CSharp + 5) > 80 
order by CSharp DESC

（9）多列排序，前一個條件相等時，自動按照下一個條件排序。

select StudentId, (CSharp + 5) as C#, DB=SQLServer from ScoreList where (CSharp + 5) > 80 order by CSharp DESC, SQLServer DESC

6.2_模糊查詢

（1）like：使用 like 查詢時，字段中的內容并不一定與查詢內容完全匹配，只要字段中含有這些內容即可。

select  * from  Students   where StuName   like  '王%';-- 以 '王' 開頭
select  * from  Students   where StuName   like  '%2';-- 以 '2' 結尾
select  * from  Students   where StuName   like  '%2%';--  包含 '2'  2 在結尾 中間 開頭 都可以匹配

（2）between：把某一字段中的值在特定范圍內的記錄查詢出來，使用 between包含斷點值（閉合區間）。

-- between 之間 
select  StuName  from  Students  where Id   between 5 and 8;select StudentName, Birthday from Students where Birthday between '1999-01-01' and '2001-05-05'
--建議不要比較字符串
-- select  * from  Students   where StuName >= '吳亦凡'  and StuName<='王大陸'

（3）IN：即把某一字段中內容與所列出的查詢內容列表匹配的記錄查詢出來(相當于把要查詢的內容通過枚舉的方式一一列出來)，更精確一些。

IN：指定某列的值必須在指定的列表中，

NOT IN ：操作符用于指定某列的值不能在指定的列表中。

--IN操作符用于指定某列的值必須在指定的列表中。
select  * from  Students   where StuName  in  ('張三','李四');
-- NOT IN操作符用于指定某列的值不能在指定的列表中。
select  * from  Students   where StuName  not  in  ('張三','李四');

6.2_多表聯查

（1）連接分類

外連接(outer)
- 左連接：Left，左表為主，返回左表中的所有行，如果左表中行在右表中沒有匹配行，則結果中右表中的列返回空值。
- 右連接，right，右表為主，返回右表中的所有行，如果右表中行在左表中沒有匹配行，則結果中左表中的列返回空值。
- 全連接：返回左表和右表中的所有行。當某行在另一表中沒有匹配行，則另一表中的列返回空值
內連接（inner）
- 等值連接：在連接條件中使用等于號（=）運算符，其查詢結果中列出被連接表中的所有列，包括其中的重復列。
- 不等鏈接：在連接條件中使用除等于號之外運算符（>、<、<>、>=、<=、!>和!<）
交叉連接
- 不帶where條件子句：它將會返回被連接的兩個表的笛卡爾積，返回結果的行數等于兩個表行數的乘積（例如：T_student和T_class，返回4*4=16條記錄），如果帶where，返回或顯示的是匹配的行數
- 有where子句：往往會先生成兩個表行數乘積的數據表，然后才根據where條件從中選擇。cross join后加條件只能用where,不能用on

--多表查詢
--外連接
--左連
select * from CustomerInfo as C
left outer join AddressInfo as A on C.AddressId=A.AddressId;
--左連
--as 可以省略，outer也可以省略，默認為outer
select * from CustomerInfo as C
left join UserInfo U on C.CreateUaerId=U.UserId;
--右連接
select * from CustomerInfo as C
right join UserInfo U on C.CreateUserId=U.UserId;
--全連接
select * from CustomerInfo as C
full join UserInfo U on C.CreateUserId=U.UserId;
--設置顯示的列
select C.CustomerId,C.CustomerName,C.Sex,C.Age,C.Phone,A.ProvinceName,A.City,A.Area from CustomerInfo as  C 
left outer  join AddressInfo as A on C.AddressId=A.AddressId;
--列可使用+顯示在一列
select C.CustomerId,C.CustomerName,C.Sex,C.Age,C.Phone,A.ProvinceName+A.City+A.Area   DataiAddress from CustomerInfo as  C 
left  outer  join AddressInfo as A on C.AddressId=A.AddressId;--內連接
--等值連接
select * from CustomerInfo as  C 
inner   join UserInfo U  on  C.CreateUserId=U.UserId;
--不等連接
select * from CustomerInfo as C
inner join UserInfo U on CreateUserId<>U.UserId;
--不等連接
select C.CustomerId,C.CustomerName,C.AddressId,A.AddressId, A.ProvinceName+A.City+A.Area as  DataiAddress from CustomerInfo as C
inner join AddressInfo as A on C.AddressId<>A.AddressId;--交叉連接
--不帶where
select C.CustomerId,C.CustomerName,C.Age,A.AddressId, A.ProvinceName+A.City+A.Area as  DataiAddress  from CustomerInfo as C 
cross join  AddressInfo as A
--帶where
select C.CustomerId,C.CustomerName,C.Age,A.AddressId, A.ProvinceName+A.City+A.Area as  DataiAddress  from CustomerInfo as C 
cross join  AddressInfo as A where C.AddressId=A.AddressId;

6.3_分組查詢與統計

（1）使用Group by分組

分組 Group By 的標準，一般要出現在展示項中，一般形如：select 聚合函數, xx, [不要出現非聚合項] from table_name group by xx

select COUNT(*) as 總人數, ClassName from Students
inner join StudentClass on StudentClass.ClassId = Students.ClassId
group by ClassNameselect Score ,Count(Score) as ScoreCount from StudentInfo--查詢
where Score>=90 and Score<=100			--篩選
group by Score							--分組
order by ScoreCount desc,Score asc;		--排序

（2）分組統計篩選 having：

分組后篩選:借助having子句，having子句，只能配合group by使用

having count(Score)>=4專門對分組后的結果進行二次篩選，列的別名不能當作having條件

select  Score ,Count(Score) as ScoreCount from StudentInfo
where Score>=90 and Score<=100			
group by Score	having count(Score)>=4
order by ScoreCount desc,Score asc;	
--having的其他作用
-- 查詢重復的字段
select StudentId from ScoreList group by StudentId having COUNT(*) > 1
select * from ScoreList
where StudentId in (select StudentId from ScoreList group by StudentId having COUNT(*) > 1)
order by StudentId

（3）嵌套查詢，

select * from 
(
select  Score ,Count(Score) as ScoresCount from StudentInfo
where Score>=90 and Score<=100		
group by Score
)
as MyTable where ScoresCount>=4

（4）分組查詢對比

where子句：