数据库SQL复习速记

一、连接查询

Select S.Sno, S.Sname
From S
Join SC On S.Sno = SC.Sno
Join C On SC.Cno = C.Cno
Where S.Sdept = '自动化系'
  And C.Cname = '操作系统';

Select S.Sname, SC.Grade
From S
Join SC On S.Sno = SC.Sno
Join C On SC.Cno = C.Cno
Where C.Cname = 'Python程序设计';

Select Cus.Name, Pro.PName, Ord.Num
From Cus
Join Ord On Cus.CustID = Ord.CustID
Join Pro On Ord.PID = Pro.PID
Where Cus.RegDate > '2025-01-01'
  And Cus.City = '深圳'
Order By Ord.OrderDate Desc;

回顶部

二、分组统计

Select Sno
From SC
Group By Sno
Having Count(Distinct Cno) = (
    Select Count(*)
    From C
);

Select Category, Avg(Price) As AvgPrice
From Pro
Group By Category
Having Avg(Price) > 300
Order By AvgPrice Desc;

回顶部

三、条件筛选

Select Cno
From SC
Where Grade < 60;

回顶部

四、视图

Create View v_CustOrder As
Select 
    Cus.Name,
    Count(Ord.OID) As OrderCount,
    Coalesce(Sum(Ord.Num), 0) As TotalNum
From Cus
Left Join Ord 
    On Cus.CustID = Ord.CustID
   And Ord.OrderDate >= '2025-01-01'
Group By Cus.CustID, Cus.Name;

回顶部

五、事务与存储过程

Delimiter //

Create Procedure PlaceOrder(
    In p_oid Int,
    In p_custid Int,
    In p_pid Int,
    In p_num Int
)
Begin
    Declare Exit Handler For Sqlexception
    Begin
        Rollback;
    End;

    Start Transaction;

    Update Pro
    Set Stock = Stock - p_num
    Where PID = p_pid
      And Stock >= p_num;

    If Row_Count() = 1 Then
        Insert Into Ord(OID, CustID, PID, Num, OrderDate)
        Values(p_oid, p_custid, p_pid, p_num, Now());

        Commit;
    Else
        Rollback;
    End If;
End //

Delimiter ;

回顶部

六、用户管理

Create User 'shop_user'@'localhost' Identified By 'Shop@666';

回顶部

七、权限控制 DCL

Grant Select On 商城.* To 'shop_user'@'localhost';

Grant Insert On 商城.Ord To 'shop_user'@'localhost';

Grant Insert On 商城.Orders To 'shop_user'@'localhost';

回顶部