数据库的基础操作-温故知新

包含一些简单的数据库操作指令，主要暂时T-SQL语句，图形化界面暂不记录

本文知识来源：观看B站视频讲解，自己操作总结，B站链接为：https://space.bilibili.com/494829372?spm_id_from=333.788.b_765f7570696e666f.1

  1. 数据库的创建、删除和修改：
create Database FiveCirclesDB2  /*创建数据库*/
drop Database FiveCirclesDB2 /*删除数据库*/
alter database FiveCirclesDB2 Modify name = FiveCirclesDB/*修改数据库名字*/
  1. 创建表和添加主外键约束：

解释：
primary key 中文叫主键，用来唯一确定表中的某一行，相当于一个人的身份证号码。

/*创建表GenderTable，主键为ID：*/
Create table GenderTable
(
	ID int primary key ,
	Gender nvarchar(50) not null
)
  • 添加主外键约束
    约束：constraint
    外键：foreign key
alter table XX(添加外键的表名）
add constraint XXXX(添加的约束名称)
foreign key AA(添加外键的表的列名) references BB (CC) (外键对应表名以及列名)

实例：
alter table EmployTable 
add constraint EmployTable_GenderID_GenderTable_ID 
foreign key (GenderID) references GenderTable (ID)

在这里插入图片描述

  1. 添加Default约束：
alter table EmployTable 
add constraint EmployTable_Phone_default
default 158665423 for Phone

insert into EmployTable(ID ,[Name],GenderID) values (4,'hh','3')

select * from EmployTable
delete from EmployTable where ID = '4'
/*删除约束
alter table EmployTable 
drop constraint EmployTable_Phone_default
*/
  1. 添加Check & Unique 约束：
    check constraint :给列设置条件，检查新插入的数据或者更新的数据是否符合该条件；
alter table EmployTable 
add constraint check_Phone
check (Phone > 0)

insert into EmployTable values (5 , 'saa' , 7899,2)
select * from EmployTable

unique key:设置为unique 的列不允许有重复值的出现，可以允许一个null

create table test
(
	ID int Primary key,
	[Name] nvarchar(50),
	Phone int check (Phone > 0),
	Email nvarchar(50) unique,
	GenderID int references GenderTable (ID)

)

alter table test
insert into test values (1,'ma',123,'ma@qq.com',2)
select * from test
insert into test values (2,'ba',1423,'4ma@qq.com',3)
  1. Select 和 Where 用法：
    select 查询：distinct(筛选不重复数据)
/*
查询全部orders
查询固定列名数据
查询某列不重复数据
查询两列不完全重复的数据
*/
 select * from Orders
 select OrderId , OrderDate from Orders
 select distinct ShipCountry from Orders
 select distinct ShipCountry,ShipVia from Orders

where:过滤器，筛选出满足条件的行
not in,like, in ,and,or(等一系列可以放在语句中的条件)

select OrderID,ShippedDate,ShipCountry   
from Orders
where ShipCountry not in ('France','UK')
  1. Group by , having & where区别，sql执行顺序

Group by:数据分组，组内数据处理（求组内最值，平均值）
聚合函数（处理组内数据）：max(),min(),average(),sum();

/*计算不同OrderID卖出去的总价*/
select OrderID,Sum(UnitPrice * Quantity * (1 - Discount)) as OrderPrice
from [Order Details]
group by OrderID

在这里插入图片描述
当我们数据分好组后，如果我们只希望筛选出其中的一些组，这时候通常会用到having语句：
例如我们只希望查询订单编号是10248和10249,这两个订单的总价需要用到having.

select OrderID,Sum(UnitPrice * Quantity * (1 - Discount)) as OrderPrice
from [Order Details]
group by OrderID
having OrderID in (10248,10249)

select OrderID,Sum(UnitPrice * Quantity * (1 - Discount)) as OrderPrice
from [Order Details]
group by OrderID
having sum(UnitPrice * Quantity * (1 - Discount)) > 500

where 和 having :where不能用于分组后的数据，而having可以

sql语句的执行顺序：
在这里插入图片描述7. join用法

*join分类
在这里插入图片描述

  • cross join
    如果表一有M行，表二有N行，那么cross join后的表有M*N行
    即是两个表行所有的可能组合
select * from Region
select * from Territories
select * from Region cross join Territories
  • inner join(满足条件的筛选)
    先执行Cross join然后再根据条件筛选行
/*同时满足on 后面的条件，才会筛选出来*/
select * from Region
select * from Territories;

select * from Region inner join Territories
on Region.RegionID = Territories.RegionID;
  • outer join (left join & right join)
    先做cross join，在做inner join,最后加上左表或右表中不满足inner join条件的行
/*这里在Region表中插入一行Territories表中没有的数据，发现
筛选出的结果是在inner join基础上的，left表中有，而right表中可能
没有的结果，right join道理同样适用
*/
insert into Region values (5,'Middle')
select * from Region
select * from Territories
select * from Region left join Territories
on Region.RegionID = Territories.RegionID
  1. 存储过程（Stored Procedure）
    一组预先编译好的sql语句，存在数据库中，需要时直接调用，实现了代码的复用和优化。
/*通过存储过程的名字可以直接执行*/
create procedure getEmployeesAddress
as
begin 
select LastName,FirstName,[Address] from Employees
end

stored procedure也支持传入参数
eg.通过LastName来获取地址：

create procedure getEmployeesAddressByLastName
@lastName nvarchar(50)
as
begin 
select LastName,FirstName,[Address] from Employees
where LastName = @lastName
end

/*getEmployeesAddressByLastName 'Davolio'*/
/*getEmployeesAddressByLastName @lastName = 'Davolio'*/

eg.output输出
问题：
在这里插入图片描述
解决方案（正确代码如下）：

create procedure getEmployeesAddressByLastName
@lastName nvarchar(50),
@name nvarchar(100) output,
@address nvarchar(1000) output
as
begin 
select @name = FirstName + ' ' + LastName,@address =[Address] 
from Employees
where LastName = @lastName
end

数据库中执行存储过程操作：
在这里插入图片描述
然后可以查看数据库自动生成的执行语句。