1 --使用like进行模糊查询
2
3 --查询所有姓王的同学的信息
4
5 select * from student where stuName like '王%'
6
7 --'%'号与任意个字符相匹配其实就是0到n个
8
9 --查询所有赵姓同学的信息并且其名字是两个字
10
11 select * from student where stuName like '赵_'
12 --'_'号与一个字符相匹配
13
14 --查询第二字为志的同学的信息
15 select * from student where stuName like'_志%'
16
17 insert into student values('20060211','赵红','女','1985-05-28 00:00:00','电子商务',null,'电子商务系')
18 --查询没有平均成绩的同学信息
19
20 select * from student where stuAvgrade is null
21
22 --创建选课表(课程表)
23
24
25
26 create table sc
27 (
28 stuId char(8),
29 cName varchar(20),--课程名
30 cGrade numeric(3,1) check(cGrade >=0 And cGrade <= 100)
31 primary key(stuId,cName) --联合主键
32 )
33 go
34
35
36 insert into sc values('20060201','英语',80.2)
37 insert into sc values('20060201','数据库原理',70.0)
38 insert into sc values('20060201','算法设计与分析',92.4)
39 insert into sc values('20060202','英语',81.9)
40 insert into sc values('20060202','算法设计与分析',85.2)
41 insert into sc values('20060203','多媒体技术',68.1)
42 go
43
44
45 --查询选了课的学生的学生信息以及所选课程名称和成绩
46
47 select * from student
48 select * from sc
49
50 select s.*,sc.* from student s, sc
51 where s.stuId = sc.stuId
52 order by s.stuId
53
54
55
56 select student.*,sc.* from student, sc
57
58 select student.*, sc.* from student, sc
59 order by student.stuId --这叫全映射，又叫笛卡尔乘积
60
61
62 select student.*,sc.stuId, cName, cGrade from student, sc
63 where student.stuId = sc.stuId
64 order by sc.stuId
65
66
67
68 select student.stuId as 学号, stuName as 姓名, stuSex as 性别,
69 stuSpeciality as 专业, stuDept as 系别, cName as 课程名称, cGrade as 课程成绩
70 from student,sc
71 where student.stuId = sc.stuId
72
73 --查询所有没有选英语课的学生的信息
74
75
76 select * from student
77 select * from sc
78
79 select student.*,cName from student, sc
80 where student.stuId = sc.stuId and cName <> '英语'
81
82 select student.*,cName from student, sc
83 where student.stuId = sc.stuId and student.stuId not in (选了英语课的学生的ID)
84
85 --选了英语课的学生的ID
86 select stuId from sc where cName = '英语'
87
88 select student.*,cName from student, sc
89 where student.stuId = sc.stuId and student.stuId not in (
90 select stuId from sc where cName = '英语'
91 )
92
93 --查询学生李好的同专业同学的信息
94
95
96
97 --嵌套子查询版
98 select * from student where stuSpeciality in
99 (
100 select stuSpeciality from student where stuName = '李好'
101 )
102 and stuName <> '李好'
103
104
105
106
107 --查询学生李好的同系同学的信息不包含李好的信息
108
109 select * from student
110 select * from student
111
112
113
114 select s1.* from student s1, student s2
115 where s1.stuDept = s2.stuDept
116 and s2.stuName = '李好'
117 and s1.stuName <> '李好'
118
119
120
121 select s2.* from student s1, student s2
122 where s1.stuDept = s2.stuDept
123 and s1.stuName = '李好'
124 and s2.stuName <> '李好'
125
126
127
128
129 --查找同城好友
130 select h1.* from haoyou h1, haoyou h2
131 where h1.city = h2.city
132 and h2.hName = '李白'
133 and h1.hName <> '李白'
