Unity高级查询，主外键约束

1.主键约束

格式：alter table 表名 add constraint 主键别名 primary key (主键列)
alter table students add constraint t_pr primary key(id)

2.外键约束

格式：alter table 表名1 add constraint  外键别名 foreign key(外键) references表名2(主键)
alter table stu add constraint t__fo foreign key(id) references students(id)

3.删除约束

格式：alter table 表名  drop constraint 约束别名
alter table stu drop constraint t__fo

4.多连接查询---分类

内联结：inner join
 
SELECT Students.SName, Score.CourseID, Score.Score
FROM     Students,Score
WHERE  Students.SCode = Score.StudentID

5.多表查询---三表联结

SELECT
S.SName AS 姓名, CS.CourseName AS 课程, C.Score AS 成绩
FROM Students AS S
INNER JOIN Score AS C ON (S.SCode = C.StudentID)
INNER JOIN Course AS CS ON (CS.CourseID = C.CourseID

6.多表联结查询---左外联结

SELECT S.SName,C.CourseID,C.Score

FROM   StudentsAS S

LEFT JOIN   ScoreAS C

ON   C.StudentID= S.SCode

7.多表联结查询---右外连接

SELECT Titles.Title_id, Titles.Title, Publishers.Pub_name
FROM titles
RIGHT OUTER JOIN Publishers
ON Titles.Pub_id = Publishers.Pub_id

8.高级查询

模糊查询 ---like

定义：查询时，字段中的内容并不一定与查询内容完全匹配，只要字段中含有这些内容
select id,name from work where name like 'a'

模糊查询---between

定义：把某一字段中内容在特定范围内的记录查询出来
select id,name from work where id between 1 and 2

模糊查询---in

定义：把某一字段中内容与所列出的查询内容列表匹配的记录查询出来
select id,name from work where id in (1,2)

MicrosoftInternetExplorer402DocumentNotSpecified7.8 磅Normal0 更多精彩关注： http://www.gopedu.com/