MySQL

1、
CREATE TABLE sudents (
Id INT(10) PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT,
Name VARCHAR(20) NOT NULL,
Sex VARCHAR(4),
age INT(10),
class VARCHAR(20),
Addr VARCHAR(50)
);
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)
);
CREATE TABLE cource(
Id INT(10) PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT,
C_name VARCHAR(20) NOT NULL UNIQUE
);
2、使用while循环和repeat循环各写两个存储过程，传入一个行数，控制插入多少条数据。
delimiter $$;
CREATE PROCEDURE inst_data2(coutno INT)
BEGIN
DECLARE kmid INT(10);
DECLARE kmname VARCHAR(20);
WHILE coutno>0 DO
SET kmid = CONCAT(100,coutno);
SET kmname = CONCAT("体育",coutno);
INSERT INTO cource VALUES (kmid,kmname);
SET coutno = coutno-1;
END WHILE;
END
$$;
delimiter;
call inst_data2(10);

delimiter $$;
CREATE PROCEDURE inst_data_rep(coutno INT)
BEGIN
DECLARE kmid INT(10);
DECLARE kmname VARCHAR(20);
REPEAT
SET kmid = CONCAT(1000,coutno);
SET kmname = CONCAT("音乐",coutno);
INSERT INTO cource VALUES (kmid,kmname);
SET coutno = coutno-1;
UNTIL coutno>0
END REPEAT;
END
$$;
delimiter;
3、
INSERT INTO test2.sudents VALUES
(801,'刘海洋','男',21,'乔巴','北京市海淀区'),
(802,'周飞','男',18,'乔巴','北京市昌平区'),
(803,'味全','男',26,'路飞','湖南省永州市'),
(804,'孙洋','女',21,'乔巴','辽宁省阜新市'),
(805,'李佳','女',22,'超人','福建省厦门市'),
(806,'保总','女',30,'乔巴','湖南省衡阳市'),
(1001,'徐振永','男',21,'索隆','辽宁省阜新市'),
(1002,'李卫强','男',18,'索隆','福建省厦门市'),
(1003,'狄枫','男',26,'蜘蛛侠','湖南省衡阳市'),
(1004,'女屌丝','女',21,'蜘蛛侠','北京市海淀区'),
(1005,'郁燕','女',22,'索隆','北京市昌平区'),
(1006,'裴颖菲','女',30,'索隆','辽宁省阜新市'),
(1007,'戴小龙','男',50,'索隆','福建省厦门市');

INSERT INTO Score VALUES
(1,801,101,98),
(2,801,103,49),
(3,801,102,80),
(4,802,101,65),
(5,803,103,95),
(6,804,101,70),
(7,804,102,92),
(8,805,102,94),
(9,806,101,57),
(10,806,102,45),
(11,1001,101,98),
(12,1007,102,80),
(13,1002,101,65),
(14,1002,103,88),
(15,1003,103,95),
(16,1004,101,70),
(17,1004,102,92),
(18,1005,102,94),
(19,1006,101,57),
(20,1006,102,45);

INSERT INTO cource VALUES
(101,"计算机"),
(102,"英语"),
(103,"中文");
4、查询students表所有记录
SELECT * FROM students;
5、查询students表的第2条到4条记录
SELECT * FROM students LIMIT 1,4;
6、从students表查询所有学生的id，姓名name，班级class的信息。
SELECT Id,name,class FROM students;
7、从students表查询乔巴和索隆的学生的信息
SELECT * FROM students WHERE class = '乔巴' or class = '索隆';
8、从students表查询年龄18~25岁的学生信息
SELECT * FROM students WHERE age >= 18 AND age <= 25;

SELECT * FROM students WHERE age BETWEEN 18 AND 25;

9、从students表查询每个班有多少人
SELECT class,COUNT(1) FROM students GROUP BY class;
10、从score表中查询每个科目的最高分
SELECT C_id,MAX(Grade) FROM Score GROUP BY C_id;
11、查询女屌丝的考试科目（c_name）和考试成绩（grade）
SELECT km.C_name,cj.Grade FROM students s LEFT JOIN Score cj ON s.id = cj.Stu_id LEFT JOIN cource km ON km.Id = cj.C_id WHERE s.name = '女屌丝'; 
12、简述左链接右链接之间有什么区别
左连接左边是主表，没有交集会显示左边表的数据
右链接右边是主表，没有交集会显示右边表的数据
13、用四种多表链接的方式查询所有学生的信息和考试信息（左连接，右链接，内链接，=号链接）
左连接：SELECT * FROM students s LEFT JOIN Score cj ON s.Id = cj.Stu_id LEFT JOIN cource km on km.Id = cj.C_id;
右链接：SELECT * FROM students s RIGHT JOIN Score cj on s.id = cj.Stu_id RIGHT JOIN cource km on km.Id = cj.C_id;
内连接：SELECT * FROM students s INNER JOIN Score cj on s.id = cj.Stu_id INNER JOIN cource km on km.id = cj.C_id;
14、计算每个学生的总成绩
SELECT s.name,SUM(cj.Grade) FROM students s LEFT JOIN Score cj ON s.Id = cj.Stu_id GROUP BY s.name;
15、计算每个考试科目的平均成绩
SELECT km.C_name,SUM(Grade)/COUNT(C_id) as ave_score FROM Score cj LEFT JOIN cource km ON cj.C_id = km.Id GROUP BY C_id;
16、查询同时参加计算机和英语考试的学生信息
SELECT * FROM students s,Score cj1,Score cj2 WHERE s.id = cj1.Stu_id AND s.id = cj2.Stu_id AND cj1.C_id = 101 AND cj2.C_id = 102;
17、将计算机考试成绩按从高到低排序
SELECT Grade FROM Score WHERE C_id = '101' ORDER BY Grade DESC;
18、从sutdent表和score表中查询出学生的学号，然后合并查询结果
SELECT * FROM Score cj INNER JOIN students s on cj.Stu_id = s.id GROUP BY Stu_id;
19、查询索隆班姓李的男同学的成绩和学生信息
SELECT * FROM students s LEFT JOIN Score cj ON s.id = cj.Stu_id WHERE s.class = '索隆' and s.name LIKE "李%";
20、查询都是湖南的学生的姓名、年龄、班级和考试科目及成绩
SELECT s.name,s.age,s.class,km.C_name,cj.Grade,s.addr FROM students s LEFT JOIN Score cj ON s.Id = cj.Stu_id LEFT JOIN cource km on km.Id = cj.C_id WHERE s.Addr LIKE "%湖南%";
21、把总成绩小于100的学生名称修改为天才
UPDATE students set name = '天才' WHERE id in (SELECT idp.id FROM (SELECT s.id FROM Score cj LEFT JOIN students s ON cj.Stu_id = s.id GROUP BY Stu_id HAVING SUM(cj.Grade) < 100) idp);
22、查询只学过一门课的学生信息
SELECT * FROM students WHERE id in (SELECT Stu_id FROM Score GROUP BY Stu_id HAVING COUNT(Stu_id) =1);
23、查询出多少个年龄一样的学生
SELECT age,COUNT(age) FROM students GROUP BY age;
24、查询出每门课程低于平均成绩的学生姓名、课程名称、分数
SELECT s.name,km.C_name,cj.Grade FROM students s LEFT JOIN Score cj on s.id = cj.Stu_id LEFT JOIN cource km on cj.c_id = km.id WHERE cj.Grade < (SELECT a.avcj FROM (SELECT C_id,SUM(Grade)/COUNT(C_id) as avcj FROM Score GROUP BY C_id) a WHERE a.C_id = cj.C_id)
25、查询出每个人成绩最高的课程名称及分数
SELECT km.C_name,cj.Stu_id,MAX(cj.Grade) FROM Score cj LEFT JOIN cource km on cj.C_id = km.Id GROUP BY cj.Stu_id;
26、索引是什么，如何创建索引，为什么要使用索引？写自己的理解
索引是什么：索引是一个表中所包含值的列表，其中注明了表中包含各个值的行所在的存储位置，使用索引查找数据时，先从索引对象中获得相关列的存储位置，然后再直接去其存储位置查找所需信息，这样就无需对这个表进行扫描，从而可以快速的找到所需数据
为什么要创建索引：索引在数据库中的作用相当于目录在书籍中的作用类似，都用来提高查找信息的速度
如何创建索引：create index 索引名称 on 表明（列名）
alter table 表名 add unique index 索引名称（列名）
27、创建一个试图，要求显示总成绩大于160的学生的班级、课程名称、分数、学号、学生姓名
CREATE VIEW sutdent_info(class,cname,grade,xhid,sname) as SELECT s.class,km.c_name,cj.Grade,s.id,s.name FROM students s LEFT JOIN Score cj ON s.Id = cj.Stu_id LEFT JOIN cource km on km.Id = cj.C_id GROUP BY s.Id HAVING SUM(cj.grade) > 160;
28、查询语文成绩高于计算机成绩学生的编号
SELECT s.Stu_id FROM Score s GROUP BY s.Stu_id HAVING (SELECT Grade FROM Score WHERE Score.C_id = 103 AND Score.Stu_id = s.Stu_id) > (SELECT Grade FROM Score WHERE Score.C_id = 101 AND Score.Stu_id = s.Stu_id);
29、查询所有学生的学号、姓名、班级、课程数、总成绩
SELECT s.id,s.name,s.class,COUNT(cj.stu_id),SUM(cj.grade) FROM students s LEFT JOIN Score cj on s.id = cj.stu_id GROUP BY s.id;
30、查询没有学完所有课程的学生学号、姓名
SELECT * FROM students s LEFT JOIN Score cj on s.id = cj.Stu_id GROUP BY cj.Stu_id HAVING COUNT(cj.Stu_id) < (SELECT COUNT(1) FROM cource);
31、drop和delete的用处和区别
当你不再需要该表时， 用 drop;
当你仍要保留该表，但要删除所有记录时， 用 truncate;
当你要删除部分记录时（always with a WHERE clause), 用 delete.
32、给及格的分数减掉10分，不及格的分数增加5分，然后统计出不及格学生的姓名、课程名、分数
UPDATE Score s set s.Grade=s.Grade-10 WHERE s.Grade >= 60;
UPDATE Score s set s.Grade=s.Grade+5 WHERE s.Grade < 60;
SELECT s.name,km.C_name,cj.Grade FROM students s LEFT JOIN Score cj ON s.Id = cj.Stu_id LEFT JOIN cource km on km.Id = cj.C_id WHERE cj.Grade < 60;