/*   * 繪制E-R圖  *               姓名                          姓名  *                |                             |  *            +--------+                    +--------+  *    年齡<<--| 教師表 | -->>性別   年齡<<--  | 學生表 | -->> 性別  *            +--------+                    +--------+  *                 /                   /--------/  *                  /1                N  *                   //              //  *                  /  /            /  /  *                 |授課|          |上課|  *                  /  /            /  /  *                   //              //  *                    N +--------+ 1_/  *                     /| 班級表 |/  *                      +--------+    *  */-- 創建數據庫Create Database DB_DATAon PRimary   -- 定制數據庫主文件的屬性(	name	 = 'DB_DATA'   	,filename = 'c:/database/DB_DATA.mdf' -- 主文件的文件路徑	,size = 10MB  -- 主文件的起始字節數	,maxSize = 20MB -- 主文件的最大字節數	,FileGrowth=5% -- 主文件的增長大小,可百分比，也可使用字節數)log on -- 定制日志文件的屬性，參數的含義和主文件的參數一樣(	name	 = 'DB_DATALog'   	,filename = 'c:/database/DB_DATALog.ndf' 	,size = 10MB	,MaxSize = 20MB	,FileGrowth=10%)use DB_DATAgo/** 非空約束 : not null      語法: 直接使用  * 主鍵約束 : primary key   語法: 直接使用  * 默認約束 : default       語法: default(默認值)  * 檢查約束 : check         語法: check(表達式)  * 外鍵約束 : foreign key   語法: constraint 約束名 foreign key references 其他表名(其他表字段)  * 唯一約束 : unique        語法：直接使用  */-- 創建教師表Create Table Teacher(	id  int primary key identity(0,1), -- int 類型的id，主鍵約束， identity是一個默認的增量，從0開始，每添加一行記錄就會增加1	姓名 char(20) not null,   -- char類型的姓名， 非空約束	性別 char(2) check(性別='男' or 性別='女'),  -- char類型的性別，檢查約束，	年齡 int default(18) -- int 類型的年齡， 默認約束)-- 為Teacher添加約束-- Constraint：約束關鍵字，-- DK_默認約束 ： 約束的標識符Alter Table Teacher Add Constraint DK_默認約束 default('男') for 性別  -- 默認約束 Alter Table Teacher  Add Constraint CK_檢查約束 check(年齡>=18 and 年齡 <= 45) -- 檢查約束-- 創建班級表Create Table Class(	id  int primary key identity(0,1),	班級名 char(20) ,	班級人數 int,	教師id int )-- 為班級表添加約束Alter Table Class Add Constraint  UK_唯一約束 unique(班級名) -- 唯一性約束-- -- 所屬班級的id ,屬于外鍵，為班機表添加外鍵約束Alter Table Class Add constraint FK_CLassToTeacher foreign key(教師id)  references Teacher(id)-- 創建學生表Create Table Student(	id int primary key identity(0,1),   -- 學生id	姓名 char(20) not null ,    	性別 char(2) default('男') check(性別='男' or 性別='女'), 	年齡 int default(18) check(年齡>=12 and 年齡 <= 50),         -- 所屬班級的id ,屬于外鍵	班級id int constraint FK_StudentToClass foreign key references Class(id))-- 插入數據到教師表insert into Teacher(姓名,性別,年齡)values('楊老師','男',35),('張老師','男',40),('李老師','男',30),('王老師','男',28),('韋老師','男',18)Select * from TeacherInsert into Class(班級名,班級人數,教師id)Values('17期',23,1)-- 插入數據到班級表-- 聲明要使用的變量Declare @楊老師id intDeclare @張老師id intDeclare @李老師id intDeclare @王老師id intDeclare @韋老師id int-- 獲取對應老師的id select @楊老師id=id from Teacher where 姓名='楊老師'select @張老師id=id from Teacher where 姓名='張老師'select @李老師id=id from Teacher where 姓名='李老師'select @王老師id=id from Teacher where 姓名='王老師'select @韋老師id=id from Teacher where 姓名='韋老師'-- 開始插入數據insert into Class(班級名,班級人數,教師id)values('18期',6, @楊老師id),('19期',7,@張老師id),('20期',8,@王老師id),('21期',5,@楊老師id)-- 插入信息到學生表-- 聲明要用到的變量declare @班級18ID intdeclare @班級19ID intdeclare @班級20ID intdeclare @班級21ID int-- 得到對應班級別的id -- 使用select語句查詢表中的18期，并將查詢好的記錄中的id字段的值賦值給局部變量Select @班級18ID = id From Class where 班級名='18期'Select @班級19ID = id From Class where 班級名='19期'Select @班級20ID = id From Class where 班級名='20期'Select @班級21ID = id From Class where 班級名='21期'-- 開始插入數據insert into Student(姓名,性別,年齡,班級id)values('楊二狗','男',18,@班級18ID),('芙蓉','女',28,@班級18ID),('趙四','男',20,@班級18ID),('麻五','男',41,@班級18ID),('工人1','女',23,@班級18ID) ,('工人2','男',13,@班級19ID) ,('工人3','男',41,@班級19ID) ,('工人4','女',42,@班級19ID) ,('工人5','男',36,@班級19ID) ,('工人6','男',34,@班級19ID) ,('工人7','男',24,@班級19ID) ,('工人8','女',16,@班級21ID) ,('工人9','女',37,@班級20ID) ,('大神1','男',41,@班級20ID) ,('大神2','男',42,@班級20ID) ,('大神3','男',34,@班級20ID) ,('大神4','女',33,@班級20ID) ,('大神5','男',27,@班級20ID)-- 普通查詢Select * From Student-- 查詢指定的列select 姓名,性別,年齡 From Student-- 在查詢時，對字段進行改名輸出Select 姓名 as  name , Sex = 性別 , Age = 年齡 From Student-- 使用等于號也能夠完成和上一行相同的功能Select name = 姓名 , Sex = 性別 , Age = 年齡 From Student-- 在查詢時,過濾部分內容,只輸出符合條件的內容Select * From Student	where 年齡>=20 and 年齡<=25 and 性別 = '女'-- 過濾查詢，輸出名字中姓大的，并且年齡在30到40歲之間的學生。Select * From Student	where 姓名 like '大%' and 年齡>=30 and 年齡<= 40 -- _ 只通配一個字符-- [abcd] -- 過濾查詢， 輸出20，25，30歲的學生Select * From Student	where 年齡 in(20,25,30) -- 多表查詢 : 簡單地將兩個表的所有內容輸出(輸出的條目數一共是表1條目數 * 表2條目數)Select * From Student , Class-- 多表過濾查詢 (輸出的條目數是表1的條目數,靠近 from 關鍵字的表是表1,也叫作左表)Select * From Student , Class	where Student.班級Id = Class.id-- 多表查詢，只輸出指定字段select Student.姓名,Student.性別,Student.年齡,Class.班級名,Class.班級人數,Teacher.姓名,Teacher.性別,Teacher.年齡 	from Student,Class,Teacher		where Student.班級Id = Class.id and Class.教師id = Teacher.id-- 嵌套查詢 ， 只輸出20期的學生，且-- 先執行最內層的查詢，最內層的查詢把班級名對應的id找到select * From Student 	where Student.班級Id in 	(		select id From Class where 班級名 =  '20期'	)-- 下面這行代碼等同于上一條語句select * From Student 	where Student.班級Id in(4)-- 多表查詢-- 查詢所有學生的信息（本人信息，所屬班級信息，班級所屬教師信息select Student.姓名,Student.性別,Student.年齡,Class.班級名,Class.班級人數,Teacher.姓名,Teacher.性別,Teacher.年齡 	from Student,Class,Teacher		where Student.班級Id = Class.id and Class.教師id= Teacher.id-- 排序輸出:按姓名的拼音排序輸出select * From Student	Order By Student.姓名-- 排序輸出:按年齡排序輸出,升序select * From Student	Order By  Student.年齡 ASC-- 排序輸出:按年齡排序輸出,降序select * From Student	Order By  Student.年齡 DESC-- 分組輸出(用于配合聚合函數)-- 統計表中各個班級id出現的次數select 班級Id , count(*) From Student 	group by 班級Id