数据库基础练习
数据基础练习题—有空必做、面试必用
1、 写出创建上面这三张表的sql
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students`(
`Id` INT(10)PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT,
`Name` VARCHAR(20) NOT NULL,
Sex VARCHAR(4),
age int(10),
class VARCHAR(20) NOT NULL,
Addr VARCHAR(50)
)CHARSET utf8;
DROP TABLE IF EXISTS `Score`;
CREATE TABLE `Score`(
`Id` INT(10)PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT,
`Stu_id` INT(10) NOT NULL,
C_id int(10) NOT NULL,
Grade INT(10)
);
DROP TABLE IF EXISTS `cource`;
CREATE TABLE `cource`(
`Id` INT(10)PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT,
C_name VARCHAR(20)CHARACTER SET utf8 NOT NULL UNIQUE
);
-- 2、使用while循环和repeat循环写各两个存储过程，传入一个行数，
-- 控制插入多少条数往第一题创建的students和score两个表中各插入500条数据。
-- WHILE
Delimiter//
USE ts(数据库名称)//
DROP PROCEDURE IF EXISTS addstudents//
CREATE PROCEDURE addstudents(Num INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=Num DO
INSERT INTO students (name,sex,age,class,Addr)VALUES (CONCAT('test',i),'女','28','三年二班','北京市海淀区');
SET i = i+1;
END WHILE;
END//
Delimiter;
CALL addstudents(500);
SELECT * FROM students WHERE NAME LIKE '%test%';
SELECT count(*) FROM students WHERE NAME LIKE '%test%';
select FLOOR(1+(rand()*100));
-- REPEAT
delimiter //
DROP PROCEDURE if EXISTS insert_score//
CREATE PROCEDURE insert_score(num int)
BEGIN
DECLARE j INT DEFAULT 1;
DECLARE sid INT DEFAULT 1;
DECLARE cid INT DEFAULT 1;
DECLARE grad INT DEFAULT 1;
SET j=0;
REPEAT
INSERT INTO score(stu_id,C_id,Grade) VALUES(sid,cid,grad);
set j=j+1;
SELECT FLOOR(1+(RAND()*100))INTO cid;
SELECT FLOOR(1+(RAND()*100))INTO grad;
SELECT FLOOR(1+(RAND()*500))INTO sid;
UNTIL j>num
END REPEAT;
END//
delimiter ;
CALL insert_score(500);
SELECT count(*) FROM score;
3、 写insert语句插入3个表的数据
ALTER TABLE students auto_increment = 801;
INSERT INTO students(`Name`,Sex,age,class,Addr) VALUES
('刘海洋1','男',21,'乔巴','北京市海淀区'),
('周飞1','男',18,'乔巴','北京市昌平区'),
('味全1','男',26,'路飞','湖南省永州市'),
('孙洋1','女',21,'乔巴','辽宁省阜新市'),
('李佳1','女',22,'超人','福建省厦门市'),
('保总1','女',30,'乔巴','湖南省衡阳市');
INSERT INTO students(`Id`,`Name`,Sex,age,class,Addr) VALUES
(1001,'徐振永','男',21,'索隆','辽宁省阜新市'),
(1002,'李卫强','男',18,'索隆','福建省厦门市'),
(1003,'狄枫','男',26,'蜘蛛侠','湖南省衡阳市'),
(1004,'女屌丝','女',21,'蜘蛛侠','北京市海淀区'),
(1005,'郁燕','女',22,'索隆','北京市昌平区'),
(1006,'裴颖菲','女',30,'索隆','辽宁省阜新市'),
(1007,'戴小龙','男',50,'索隆','福建省厦门市');
INSERT INTO cource(`Id`,`C_name`) VALUES
(101,'计算机'),
(102,'英语'),
(103,'中文');
INSERT INTO Score(`Stu_id`,`C_id`,Grade) VALUES
(801,101,98),
(801,103,49),
(801,102,80),
(802,101,65),
(802,103,88),
(803,103,95),
(804,101,70),
(804,102,92),
(805,102,94),
(806,101,57),
(806,102,80),
(1001,101,98),
(1007,102,80),
(1002,101,65),
(1002,103,88),
(1003,103,95),
(1004,101,70),
(1004,102,92),
(1005,102,94),
(1006,101,57),
(1006,102,45);
4、 查询students表的所有记录
SELECT * FROM students;
5、 查询students表的第2条到4条记录
SELECT * FROM students LIMIT 1,3;
6、 从students表查询所有学生的学号（id）、姓名（name）和班级（class）的信息
SELECT `Id`,`Name`,`class` FROM students;
7、 从students表中查询乔巴和索隆的学生的信息
SELECT * FROM students WHERE class in ('乔巴','索隆');
8、 从students表中查询年龄18~25岁的学生信息
SELECT * FROM students WHERE age BETWEEN 18 AND 25;
9、 从students表中查询每个班有多少人
SELECT class,count(*) FROM students GROUP BY class;
10、 从score表中查询每个科目的最高分
SELECT a.C_name,MAX(Grade)
FROM cource a,Score b
WHERE a.Id=b.C_id
GROUP BY C_id;
11、 查询女屌丝的考试科目（c_name）和考试成绩（grade）
SELECT a.C_name,b.Grade
FROM cource a,Score b,students c
WHERE a.Id=b.C_id AND b.Stu_id=c.Id AND c.`Name`='女屌丝';
12、 简述什么是左连接、右连接，他们之间的区别是什么
左连接就是以左边的表为主表，结果为左边的全部数据加上右表能匹配上左表的数据；
右连接是以右表为主表，结果为右边的全部数据加上左表能匹配上左表的数据
13、 用4种多表连接的方式查询所有学生的信息和考试信息（左连接、右连接、内连接、=号连接）
左连接：
SELECT a.*,b.C_name,c.Grade
FROM students a LEFT JOIN (cource b,Score c)
ON a.Id=c.Stu_id AND b.Id=c.C_id;
右连接：
SELECT a.*,b.C_name,c.Grade
FROM (cource b,Score c)RIGHT JOIN students a
ON a.Id=c.Stu_id AND b.Id=c.C_id;
内连接：
SELECT a.*,b.C_name,c.Grade
FROM (cource b,Score c)INNER JOIN students a
ON a.Id=c.Stu_id AND b.Id=c.C_id;
=号连接：
SELECT a.*,b.C_name,c.Grade
FROM students a,cource b,Score c
WHERE a.Id=c.Stu_id AND b.Id=c.C_id;
14、 计算每个学生的总成绩
SELECT a.`Name`,SUM(Grade)
FROM students a,Score b
WHERE a.Id=b.Stu_id
GROUP BY a.Id;
15、 计算每个考试科目的平均成绩
SELECT a.C_name,ROUND(AVG(Grade),2) as c_grade
FROM cource a,Score b
WHERE a.Id=b.C_id
GROUP BY a.Id;
16、 查询同时参加计算机和英语考试的学生的信息
SELECT *
FROM students
WHERE Id in(
SELECT Stu_id FROM (SELECT *,COUNT(*) cou FROM Score
WHERE C_id in
(SELECT `Id` FROM cource WHERE C_name in('计算机','英语'))
#找到计算机和英语的id，也就是score中的c_id
GROUP BY Stu_id) b
WHERE cou=2
);
SELECT s1.* FROM
(SELECT COUNT(*) cout,Stu_id FROM score s WHERE s.c_id in(SELECT id FROM cource c WHERE c.C_name in ('计算机','英语')) GROUP BY s.Stu_id HAVING cout =2) a,
students s1
WHERE s1.id = a.Stu_id;
17、 将计算机考试成绩按从高到低进行排序
SELECT Grade FROM Score WHERE C_id=(SELECT `Id` FROM cource WHERE C_name='计算机')
ORDER BY Grade DESC;
18、 从student表和score表中查询出学生的学号，然后合并查询结果
SELECT a.`Name`,b.*
FROM students a RIGHT JOIN Score b
ON a.Id=b.Stu_id;
19、 查询索隆班姓李的男同学的成绩和学生信息
SELECT a.*,b.Grade
FROM students a,Score b
WHERE a.Id=b.Stu_id AND a.class='索隆' AND Sex='男';
20、 查询都是湖南的学生的姓名、年龄、班级和考试科目及成绩
SELECT a.`Name`,a.age,a.class,b.C_name,c.Grade
FROM students a,cource b,Score c
WHERE a.Id=c.Stu_id AND b.Id=c.C_id AND a.Addr LIKE '%湖南%';
SELECT s.Id,s.`Name` ,s.age,s.class,c.C_name,s1.grade FROM students s,score s1,cource c WHERE s.Addr LIKE '%湖南%' and s.Id = s1.Stu_id and c.Id = s1.C_id;
21、 把总成绩小于100的学生名称修改为天才
UPDATE students SET `Name`='天才' WHERE `Id` in
(SELECT Stu_id
FROM (SELECT Stu_id,SUM(Grade) sum FROM Score GROUP BY Stu_id ) a
WHERE sum<100);
22、 查询只学过一门课的学生信息
SELECT *
FROM students
WHERE Id in (SELECT Stu_id FROM (SELECT *,COUNT(*) cou FROM Score GROUP BY Stu_id) a
WHERE cou =1 );
23、 查出有多少个年龄一样的学生
SELECT age,COUNT(*),GROUP_CONCAT(`Name`)
FROM students
GROUP BY age;
24、 查询出每门课程低于平均成绩的学生姓名、课程名称、分
SELECT a.`Name`,b.C_name,c.Grade
FROM students a,cource b,Score c,(SELECT C_id,ROUND(AVG(Grade),2)AS avg FROM Score GROUP BY C_id ) d
WHERE a.Id=c.Stu_id AND b.Id=c.C_id AND c.C_id=d.C_id AND c.Grade<d.avg;
25、 查询出每个人成绩最高的课程名称及分数
SELECT b.`Name`,c.C_name,a.grade
FROM (SELECT Stu_id,C_id,MAX(Grade) grade FROM Score GROUP BY Stu_id) a,students b,cource c
WHERE a.C_id=c.Id AND a.Stu_id=b.`Id`;
26、 索引是什么，如何创建索引，为什么要使用索引？写自己的理解
索引由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。
索引是创建在表上的，是对数据库表中一列或多列的值进行排序的一种结构。索引可以提高查询的速度。
创建普通索引：
•create index 索引名称 on 表名(列)
•alter table 表名 add index 索引名称 (列)
–创建唯一索引：
•create unique index 索引名称 on 表名(列名）
•alter table 表名 add unique index 索引名称 (列)
27、 创建一个视图，要求显示总成绩大于160的学生的班级、课程名称、分数、学号、学生姓名、学生性别
DROP VIEW IF EXISTS d;
CREATE VIEW d AS
SELECT Stu_id,SUM(Grade) sumgrade FROM Score GROUP BY Stu_id
HAVING SUM(Grade)>160;
DROP VIEW IF EXISTS stu_view;
CREATE VIEW stu_view(class,C_name,Grade,Stu_id,`Name`,Sex) AS
SELECT a.class,c.C_name,b.Grade,b.Stu_id,a.`Name`,a.Sex FROM students a,Score b,cource c,d
WHERE a.Id=b.Stu_id AND c.Id=b.C_id AND b.Stu_id in(d. Stu_id);
SELECT * FROM stu_view;
28、 查询语文成绩高于计算机成绩的学生编号
SELECT a.Stu_id FROM
(SELECT Stu_id,Grade FROM Score WHERE C_id=(SELECT `Id` FROM cource WHERE C_name='计算机')) a,
(SELECT Stu_id,Grade FROM Score WHERE C_id=(SELECT `Id` FROM cource WHERE C_name='中文')) b
WHERE a.Stu_id =b.Stu_id AND b.Grade>a.Grade;
29、 查询所有学生的学号、姓名、班级、课程数、总成绩
SELECT a.`Id`,a.`Name`,class,COUNT(*),SUM(Grade)
FROM students a, Score b
WHERE a.Id=b.Stu_id
GROUP BY Stu_id;
30、 查询没有学完所有课程的学生学号、姓名
SELECT a.`Id`,a.`Name`
FROM students a,(SELECT Stu_id,COUNT(*) cou FROM Score GROUP BY Stu_id) b
WHERE a.Id=b.Stu_id AND cou!=3;
-- 查询没有学完课程的所有学生 、学号和姓名
-- 分析
-- 1、统计每个学生学了多少门课
-- 2、统计有多少门课
SELECT s.Stu_id,count(*) AS stu_count FROM Score s
GROUP BY s.Stu_id
HAVING stu_count NOT in (SELECT count(*) FROM Cource);
-- 展示学号和姓名把上面的结果当成一个结果集，在进行引用
SELECT a.Stu_id,b.`Name` FROM
(
SELECT a.Stu_id,count(*) AS stu_count
FROM Score a
GROUP BY a.Stu_id
HAVING stu_count NOT in (SELECT count(*) FROM Cource)
) a,Students b WHERE a.Stu_id = b.Id;
31、 drop和delete的用处和区别
drop用于对数据库和表以及表的字段的操作，包括删除数据、数据表、删除表的某个字段，删除外键，删除索引等；
而delete针对表中的数据的删除
32、给及格的分数减掉10分，不及格的分数增加5分，然后统计出不及格的学生姓名、课程名、分数（1个sql）
Select a.`Name`,b.C_name,c.final_Grade
FROM students a,cource b,
(
SELECT
(
case
when Grade>=60 then Grade-10
WHEN Grade<60 THEN Grade+5
end
) AS final_Grade,
C_id,Stu_id
FROM Score
) c
where a.Id=c.Stu_id AND b.Id=c.C_id AND c.final_Grade<60;
Select a.`Name`,b.C_name,c.final_Grade
FROM students a,cource b,
(
SELECT
(
case
when Grade>=60 then Grade-10
ELSE Grade+5
end
) AS final_Grade,
C_id,Stu_id
FROM Score
) c
where a.Id=c.Stu_id AND b.Id=c.C_id AND c.final_Grade<60;
SELECT s1.Stu_id,s1.Grade,c.C_name,CASE
WHEN Grade <60 THEN Grade + 5
WHEN Grade >=60 THEN Grade -10
END AS score
FROM Score s1,students s,cource c;
