面试前必看基础sql语句

面试前必看基础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;

系别和年龄

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;