面试前必看基础sql语句
面试常用4表
学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）
建表
begin;
DROP TABLE IF EXISTS `Teacher`;
CREATE TABLE `Teacher`
(
`Tno` varchar(3) NOT NULL comment '教师编号',
`Tname` varchar(4) NOT NULL comment '老师姓名',
`Tsex` varchar(2) NOT NULL comment '老师性别',
`Tbirthday` date DEFAULT NULL comment '老师生日',
`Tprof` varchar(6) DEFAULT NULL comment '老师职称',
`Depart` varchar(10) NOT NULL comment '老师专业',
PRIMARY KEY (`Tno`)
) ENGINE = InnoDB
CHARSET = utf8;
INSERT INTO `Teacher` ( `Tno`, `Tname`, `Tsex`, `Tbirthday`, `Tprof`
, `Depart`)
VALUES ('804', '李诚', '男', '1958-12-02', '副教授', '计算机系'),
('825', '王萍', '女', '1972-05-05', '助教', '计算机系'),
('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系'),
('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
DROP TABLE IF EXISTS `Course`;
CREATE TABLE `Course`
(
`Cno` varchar(5) NOT NULL comment '课程号',
`Cname` varchar(10) NOT NULL comment '查看名',
`Tno` varchar(3) NOT NULL comment '授课教师编号',
PRIMARY KEY (`Cno`),
KEY `Tno` (`Tno`),
CONSTRAINT `Course_ibfk_1` FOREIGN KEY (`Tno`) REFERENCES `Teacher` (`Tno`)
) ENGINE = InnoDB
CHARSET = utf8;
INSERT INTO `Course` (`Cno`, `Cname`, `Tno`)
VALUES ('3-105', '计算机导论', '825'),
('3-245', '操作系统', '804'),
('6-166', '数字电路', '856'),
('9-888', '高等数学', '831');
/*Table structure for table `Score` */
DROP TABLE IF EXISTS `Score`;
/*Table structure for table `Student` */
DROP TABLE IF EXISTS `Student`;
CREATE TABLE `Student`
(
`Sno` varchar(3) NOT NULL comment '学生学号',
`Sname` varchar(8) NOT NULL comment '学生姓名',
`Ssex` varchar(2) NOT NULL comment '学生性别',
`Sbirthday` date DEFAULT NULL comment '学生出生年月',
`Sclass` varchar(5) DEFAULT NULL comment '学生所在班级',
PRIMARY KEY (`Sno`)
) ENGINE = InnoDB
CHARSET = utf8;
INSERT INTO `Student` (`Sno`, `Sname`, `Ssex`, `Sbirthday`, `Sclass`)
VALUES ('101', '李军', '男', '1976-02-20', '95033'),
('103', '陆君', '男', '1974-06-03', '95031'),
('105', '匡明', '男', '1975-10-02', '95031'),
('107', '王丽', '女', '1976-01-23', '95033'),
('108', '曾华', '男', '1977-09-01', '95033'),
('109', '王芳', '女', '1975-02-10', '95031');
/*Table structure for table `Teacher` */
CREATE TABLE `Score`
(
`Sno` varchar(3) NOT NULL comment '学号',
`Cno` varchar(5) NOT NULL comment '课程号',
`Degree` decimal(4, 1) DEFAULT NULL comment '成绩',
PRIMARY KEY (`Sno`, `Cno`),
KEY `Cno` (`Cno`),
CONSTRAINT `Score_ibfk_1` FOREIGN KEY (`Sno`) REFERENCES `Student` (`Sno`),
CONSTRAINT `Score_ibfk_2` FOREIGN KEY (`Cno`) REFERENCES `Course` (`Cno`)
) ENGINE = InnoDB
CHARSET = utf8;
INSERT INTO `Score` (`Sno`, `Cno`, `Degree`)
VALUES ('101', '3-105', '64.0'),
('101', '6-166', '85.0'),
('103', '3-105', '92.0'),
('103', '3-245', '86.0'),
('105', '3-105', '88.0'),
('105', '3-245', '75.0'),
('107', '3-105', '91.0'),
('107', '6-166', '79.0'),
('108', '3-105', '78.0'),
('108', '6-166', '81.0'),
('109', '3-105', '76.0'),
('109', '3-245', '68.0');
commit;
表内容
sql语句练习
1.查询与李军选至少一门相同课的所有学生姓名
select distinct Sname
from Student join Score on Student.Sno = Score.Sno
where Cno in (select distinct Cno from Student, Score where Student.Sno = Score.Sno and Sname = '李军');
2.查询与李军选至少2门相同课的所有学生姓名
select Sname
from score join Student on score.Sno = Student.Sno
where Cno IN (select distinct Cno from Student, Score where Student.Sno = Score.Sno and Sname = '李军') and Sname!='李军'
group by Score.Sno having count(*)>1;
3.查询与学号为101的同学选至少2门相同课的所有学生姓名
SELECT Sname
FROM student JOIN score s1 ON s1.Sno = student.Sno
WHERE EXISTS (
SELECT Sno
FROM score s2
WHERE s1.Sno != 101 and
s2.Sno = 101 and
s1.Cno = s2.Cno
)
GROUP BY s1.Sno
HAVING COUNT(Cno) > 1;
3张表
学生表（Student）、课程表（Course）、成绩表（Score）
建表
begin;
-- （1）创建Student表
CREATE TABLE Student
(
Sno CHAR(8) PRIMARY KEY,
Sname CHAR(8),
Ssex CHAR(2) NOT NULL,
Sage INT,
Sdept CHAR(20)
);
-- （2）创建Course表
CREATE TABLE Course
(
Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40) NOT NULL,
Cpno CHAR(4),
Ccredit SMALLINT
);
-- （3）创建SC表
CREATE TABLE SC
(
Sno CHAR(8),
Cno CHAR(4),
Grade SMALLINT,
CONSTRAINT `SC_ibfk_1` FOREIGN KEY (Sno) REFERENCES Student (Sno),
CONSTRAINT `SC_ibfk_2` FOREIGN KEY (Cno) REFERENCES Course (Cno)
);
-- （6）修改表结构及约束
-- 增加班级列
ALTER TABLE Student
ADD Sclass char(4);
-- 修改年龄列
ALTER TABLE Student
MODIFY Sage smallint;
-- 增加约束
ALTER TABLE Course
ADD UNIQUE (Cname);
-- （7）删除表
-- （1）为Course表按课程名称创建索引
CREATE INDEX cname_index On Course (Cname);
-- （2）为Student表按学生姓名创建唯一索引
CREATE UNIQUE INDEX sname_index ON Student (Sname);
-- （3）为SC表按学号和课程号创建聚集索引
CREATE INDEX sno_cno_index On SC (Sno, Cno desc);
-- （4）为Course表按课程号创建唯一索引
CREATE UNIQUE INDEX iSCno ON Course (Cno);
-- 3．创建视图
-- 建立信息系学生的视图：
CREATE VIEW IS_Student
AS
SELECT Sno, Sname, Sage
FROM Student
WHERE Sdept = 'IS';
-- （1）插入到Student表
INSERT INTO Student
VALUES ('20100001', '李勇', '男', 20, 'CS', '1001'),
('20100002', '刘晨', '女', 19, 'CS', '1001');
INSERT INTO Student(Sno, Sname, Ssex, Sage, Sdept, Sclass)
VALUES ('20100021', '王敏', '女', 18, 'MA', '1002'),
('20100031', '张立', '男', 19, 'IS', '1003');
INSERT INTO Student(Sno, Sname, Ssex, sclass)
VALUES ('20100003', '刘洋', '女', '1001');
INSERT INTO Student(Sno, Sname, Ssex, Sage, Sdept, sclass)
VALUES ('20100010', '赵斌', '男', '19', 'IS', '1005');
INSERT INTO Student
VALUES ('20100022', '张明明', '男', 19, 'CS', '1002');
-- （2）插入到Course表
INSERT INTO Course(Cno, Cname, Cpno, Ccredit)
VALUES ('1', '数据库系统原理', '5', 4);
INSERT INTO Course(Cno, Cname, Cpno, Ccredit)
VALUES ('2', '高等数学', null, 2);
INSERT INTO Course(Cno, Cname, Cpno, Ccredit)
VALUES ('3', '管理信息系统', '1', 4);
-- 请写出插入其余行的插入语句，并插入数据。
INSERT INTO Course(Cno, Cname, Cpno, Ccredit)
VALUES ('6', '数据处理', null, '2');
INSERT INTO Course(cno, cname, cpno, ccredit)
values ('7', 'c语言', null, '4');
-- （3）插入到SC表
INSERT INTO SC
VALUES ('20100001', '1', 92);
INSERT INTO SC
VALUES ('20100002', '2', 80);
INSERT INTO SC(Sno, Cno)
VALUES ('20100003', '1');
INSERT INTO SC(Sno, Cno, Grade)
VALUES ('20100010', '3', null);
-- 请写出插入其余行的插入语句，并插入数据。
INSERT INTO SC
VALUES ('20100002', '2', 85);
INSERT INTO SC
VALUES ('20100002', '3', 88);
INSERT INTO SC
VALUES ('20100002', '1', 90);
commit ;
表内容
sql语句练习
1.为SC表按学号和课程号创建聚集索引
CREATE CLUSTERED INDEX iSnoCno On SC(Sno,Cno desc)
3.为Course表按课程号创建唯一索引
CREATE UNIQUE INDEX iSCno ON Course(Cno)
4.填写赵斌同学的管理信息系统课程的成绩
UPDATE SC SET Grade = 85
WHERE Sno='20100010' AND Cno='3'
5.将计算机科学系全体学生的成绩加5分
UPDATE sc SET Grade=Grade + 5
WHERE 'CS'=(select Sdept from student where student.Sno=sc.Sno)
删除计算机科学系所有学生的选课记录
DELETE FROM SC WHERE 'CS'=(select Sdept from student where student.Sno=SC.Sno );
查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。
SELECT Sname, 'Year of Birth:' as BIRTH, 2000-Sage BIRTHDAY, DEPARTMENT = LOWER(Sdept)
FROM Student;
查询选修了课程的学生学号：比较ALL和DISTINCT的区别
SELECT Sno FROM SC;
SELECT DISTINCT Sno FROM SC;
按范围查询
查询年龄在20~23岁之间的学生的姓名、系别和年龄
SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23
查询属性值属于指定集合的行
查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别
SELECT Sname,Ssex FROM Student WHERE Sdept IN ('IS','MA','CS');
模糊查询
查询所有姓刘学生的姓名、学号和性别
SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE '刘%'
查询空值
查询缺少成绩的学生的学号和相应的课程号
SELECT Sno,Cno FROM sc WHERE Grade is null;
