VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > T-SQL >

  • 基本的SQL语句

表结构




实验内容

1

  • 用SQL语句创建数据库CAP，数据文件名为CAPData.mdf，数据文件的初始存储空间大小为50M，最大存储空间为500M，存储空间自动增长量为10M。



	

		CREATE DATABASE CAP ON (  NAME = CAP,  FILENAME = 'C:\Users\Zero\Desktop\CAPData.mdf', -- 文件名  SIZE = 50, -- 初始存储空间大小  MAXSIZE = 500, -- 最大存储空间大小  FILEGROWTH = 10 -- 自动增长量 ) 





	

		

			

				 

		

		

			 

	








	2


    
	
  • 
		在CAP数据库中用SQL语句创建下面的4张表，合理设计每个字段的数据类型，建立主键与外键约束。表Products中的Price字段不允许为空。表Customers的discnt字段取值范围在[0,30]之间。利用SQL语句向表中添加表结构中的数据。
    • 







	

		use CAP CREATE TABLE Customers (  "cid" char(4) CONSTRAINT Custom_Prim PRIMARY KEY,  "cname" varchar(10),  "city" varchar(10),  "discnt" numeric(4,2) CONSTRAINT DISCNT_CHK CHECK(discnt BETWEEN 0 AND 30) ) CREATE TABLE Products (  "Pid" char(3) CONSTRAINT Product_Prim PRIMARY KEY,  "pname" varchar(10),  "city" varchar(10),  "quantity" int,  "price" numeric(10,2) CONSTRAINT Price_NotNull NOT NULL ) CREATE TABLE Agents (  "Aid" char(3) CONSTRAINT Agent_Prim PRIMARY KEY,  "aname" varchar(10),  "city" varchar(10),  "percent" TINYINT ) CREATE TABLE Orders (  "OrDno" char(4) CONSTRAINT Order_Prim PRIMARY KEY,  "month" char(3) CONSTRAINT Month_CHK CHECK(month in ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') ),  "cid" char(4) CONSTRAINT Cid_Fore FOREIGN KEY REFERENCES Customers(cid),  "aid" char(3) CONSTRAINT Aid_Fore FOREIGN KEY REFERENCES Agents(Aid),  "pid" char(3) CONSTRAINT Pid_Fore FOREIGN KEY REFERENCES Products(Pid),  "qty" int,  "dollars" numeric(10,2) ) 





	

		

			

				 

		

		

			 

	





	

		

			

				 

		

		

			 

	








	3


    
	
  • 
		利用系统预定义的存储过程sp_helpdb查看数据库的相关信息，例如所有者、大小、创建日期等。
    • 







	

		EXEC sp_helpdb 





	

		

			

				 

		

		

			 

	








	4


    
	
  • 
		利用系统预定义的存储过程sp_helpconstraint查看表中出现的约束（包括Primary key, Foreign key, check constraint, default, unique）。
    • 







	

		EXEC sp_helpconstraint Orders 





	

		

			

				 

		

		

			 

	








	5


    
	
  • 
		创建一张表Orders_Jan，表的结构与Orders相同，将Orders表中month为‘Jan’的订单记录复制到表Orders_Jan中。
    • 







	

		CREATE TABLE Orders_Jan (  "OrDno" char(4) CONSTRAINT Order_Jan_Prim PRIMARY KEY,  "month" char(3) ,  "cid" char(4) CONSTRAINT Jan_Cid_Fore FOREIGN KEY REFERENCES Customers(cid),  "aid" char(3) CONSTRAINT Jan_Aid_Fore FOREIGN KEY REFERENCES Agents(Aid),  "pid" char(3) CONSTRAINT Jan_Pid_Fore FOREIGN KEY REFERENCES Products(Pid),  "qty" int,  "dollars" numeric(10,2) );  INSERT INTO Orders_Jan SELECT * FROM Orders WHERE month='Jan' 





	

		

			

				 

		

		

			 

	








	6


    
	
  • 
		将Orders表中month为‘Jan’的订单记录全部删掉。
    • 







	

		DELETE FROM Orders WHERE month='Jan' 





	

		

			

				 

		

		

			 

	








	7


    
	
  • 
		对曾经下过金额（dollars）大于500的订单的客户，将其discnt值增加2个百分点（+2）。
    • 







	

		UPDATE Customers SET discnt = discnt + 2 -- 更新discnt WHERE cid IN( -- 找出下过500订单的客户cid  SELECT DISTINCT cid -- 使用DISTINCT防止对同一个客户重复更新discnt  -- 其实可以直接SELECT cid , IN会自动去除重复值  FROM Orders  WHERE dollars > 500  ) 





	

		

			

				 

		

		

			 

	








	8


    
	
  • 
		写一段TSQL程序，向表Orders中增加5000条记录，要求订单尽可能均匀地分布在12个月中。
    • 







	

		use CAP DECLARE @i AS INT,@randNum AS FLOAT,@mon AS char(3),@OrdNo AS SMALLINT,@cid AS char(4),@aid AS char(3),@pid AS char(3),@price AS numeric(10,2); SET @i=1; SET @OrdNo=1030; while @i<=5000 -- 流程控制，循环5000次  BEGIN  SET @randNum=RAND()*12;  SET @mon= -- 随机产生月份  CASE  WHEN @randNum<1 THEN 'Jan'  WHEN @randNum>=1 AND @randNum<2 THEN 'Feb'  WHEN @randNum>=2 AND @randNum<3 THEN 'Mar'  WHEN @randNum>=3 AND @randNum<4 THEN 'Apr'  WHEN @randNum>=4 AND @randNum<5 THEN 'May'  WHEN @randNum>=5 AND @randNum<6 THEN 'Jun'  WHEN @randNum>=6 AND @randNum<7 THEN 'Jul'  WHEN @randNum>=7 AND @randNum<8 THEN 'Aug'  WHEN @randNum>=8 AND @randNum<9 THEN 'Sep'  WHEN @randNum>=9 AND @randNum<10 THEN 'Oct'  WHEN @randNum>=10 AND @randNum<11 THEN 'Nov'  ELSE 'Dec'  END   SELECT @cid=cid -- 随机获取一个用户id  FROM Customers  ORDER BY NEWID()   SELECT @pid=Pid,@price=price -- 随机获取一个产品id以及对应的产品价格  FROM Products  ORDER BY NEWID()   SET @aid=(SELECT TOP 1 Aid -- 随机获取一个代理商id  FROM Agents  ORDER BY NEWID()  )   SET @randNum=RAND()*2000+400; -- 生成400至2400的随机订单数   INSERT INTO Orders -- 插入一条订单记录  VALUES(CONVERT(char(4),@OrdNo),@mon,@cid,@aid,@pid,CONVERT(INT,@randNum),CONVERT(INT,@randNum) * @price);  -- 使用转换函数CONVERT()   SET @OrdNo=@OrdNo+1;  SET @i=@i+1;  END 





	

		

			

				 

		

		

			 

	





	

		

			

				 

		

		

			 

	








	9


    
	
  • 
		在表Orders的’month’字段上建立索引。
    • 







	

		CREATE INDEX Orders_Index ON Orders(month) 





	

		

			

				 

		

		

			 

	








	10


    
	
  • 
		创建一个视图order_month_summary，视图中的字段包括月份、该月的订单总量和该月的订单总金额。基于视图order_month_summary，查询第一季度各个月份的订单总量和订单总金额。
    • 







	

		CREATE VIEW order_month_summary(month,total_qty,total_dollars) AS SELECT month,SUM(qty),SUM(dollars) -- 获得每月的总订单，总金额  FROM Orders  GROUP BY month 





	

		

			

				 

		

		

			 

	








	

		SELECT month,total_qty,total_dollars FROM order_month_summary WHERE month IN ('Jan','Feb','Mar') -- 查询第一季度 





	

		

			

				 

		

		

			 

	






	__EOF__



	

		 

	

		本文作者：Hickey Zhang 本文链接：https://www.cnblogs.com/hickey2048/p/14723606.html






      



  

  
  

   
    
    
   

  

  
 
 

 
 
  
 
 
 
 






 






相关教程