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 <> '李白'