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 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/
格式: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
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/