mysql50条练习题

mysql数据库考试试题及答案一、选择题（每题2分，共20分）1. MySQL数据库默认使用的端口号是多少？A. 3306B. 8080C. 80D. 21答案：A2. 下列哪个不是MySQL的数据类型？A. INTB. VARCHARC. DATED. BOOLEAN答案：D3. 在MySQL中，以下哪个命令用于创建数据库？A. CREATE TABLEB. CREATE DATABASEC. DROP DATABASED. ALTER DATABASE答案：B4. 如何在MySQL中查看当前所有数据库？A. SHOW DATABASES;B. LIST DATABASES;C. SELECT DATABASES;D. DISPLAY DATABASES;答案：A5. 在MySQL中，以下哪个语句用于删除表？A. DROP TABLEB. REMOVE TABLEC. DELETE TABLED. DESTROY TABLE答案：A6. 下列哪个不是MySQL的索引类型？A. PRIMARYB. UNIQUEC. INDEXD. FOREIGN答案：D7. 在MySQL中，使用哪个命令来查看表结构？A. DESCRIBE table_name;B. EXPLAIN table_name;C. SHOW table_name;D. SELECT table_name;答案：A8. 如何在MySQL中为表添加新列？A. ADD COLUMN column_name;B. INSERT COLUMN column_name;C. CREATE COLUMN column_name;D. APPEND COLUMN column_name;答案：A9. 在MySQL中，使用哪个命令来创建视图？A. CREATE VIEW view_name AS SELECT ...B. VIEW view_name AS SELECT ...C. DEFINE VIEW view_name AS SELECT ...D. CREATE SELECT view_name AS ...答案：A10. 下列哪个命令用于在MySQL中备份数据库？A. BACKUP DATABASEB. COPY DATABASEC. DUMP DATABASED. SAVE DATABASE答案：C二、填空题（每空1分，共10分）1. 在MySQL中，使用______语句可以创建新的表。

mysql测试题及答案MySQL测试题及答案一、选择题1. MySQL的默认端口号是什么？A. 22B. 3306C. 80D. 443答案：B2. 下列哪个SQL语句用于查询表中的所有记录？A. SELECT * FROM table_name WHERE condition;B. SELECT * FROM table_name;C. SELECT table_name.*;D. SELECT ALL FROM table_name;答案：B3. 在MySQL中，以下哪个命令用于创建新的数据库？A. CREATE DATABASE database_name;B. NEW DATABASE database_name;C. START DATABASE database_name;D. INIT DATABASE database_name;答案：A二、填空题4. 在MySQL中，使用____语句可以删除一个表。

答案：DROP TABLE5. 要查看当前MySQL服务器上所有数据库的名称，可以使用____命令。

答案：SHOW DATABASES;6. 以下SQL语句的作用是____：SELECT column1, column2 FROMtable_name WHERE column1 = value1 ORDER BY column2 DESC LIMIT 1;答案：查询表table_name中column1等于value1的记录，并按column2降序排列，返回第一条记录。

三、简答题7. 请简述主键（Primary Key）和外键（Foreign Key）的区别。

答案：主键是表中用于唯一标识每条记录的字段，一个表只能有一个主键，并且主键的值不能为NULL。

外键是表中用于与另一个表的主键建立链接的字段，用于维护两个表之间的数据一致性，一个表可以有多个外键。

8. 解释什么是事务（Transaction）以及它的特性。

mysql试题及答案一、选择题（每题2分，共20分）1. MySQL数据库默认的端口号是多少？A. 21B. 22C. 3306D. 80答案：C2. 在MySQL中，以下哪个命令用于创建数据库？A. CREATE DATABASEB. CREATE TABLEC. CREATE USERD. CREATE INDEX答案：A3. 如何在MySQL中选择所有的行和列？A. SELECT * FROM table_name;B. SELECT ALL FROM table_name;C. SELECT ALL table_name;D. SELECT table_name;答案：A4. 下列哪个选项不是MySQL的数据类型？A. INTB. DATEC. FLOATD. STRING答案：D5. 在MySQL中，如何删除一个数据库？A. DROP DATABASE database_name;B. DELETE DATABASE database_name;C. REMOVE DATABASE database_name;D. REMOVE database_name;答案：A6. 如何在MySQL中为一个表添加一个新列？A. ADD COLUMN column_name datatype;B. CREATE COLUMN column_name datatype;C. INSERT COLUMN column_name datatype;D. ADD NEW COLUMN column_name datatype;答案：A7. 在MySQL中，以下哪个命令用于删除表中的行？A. DELETE FROM table_name;B. REMOVE FROM table_name;C. DROP FROM table_name;D. REMOVE table_name;答案：A8. 如何在MySQL中更新表中的记录？A. UPDATE table_name SET column1=value1, column2=value2 WHERE condition;B. CHANGE table_name SET column1=value1, column2=value2 WHERE condition;C. MODIFY table_name SET column1=value1, column2=value2 WHERE condition;D. ALTER table_name SET column1=value1, column2=value2WHERE condition;答案：A9. MySQL中的事务是什么？A. 一系列原子性的SQL操作B. 单个SQL语句C. 一组数据D. 一个数据库答案：A10. 如何在MySQL中查看当前数据库的版本？A. SELECT VERSION();B. SHOW VERSION;C. VERSION();D. SHOW DATABASE VERSION;答案：A二、填空题（每题2分，共20分）1. MySQL的默认用户是________。

mysql练习题50道1. 编写一个MySQL查询，用于查找表中的所有数据。

```SELECT * FROM 表名;```2. 编写一个MySQL查询，用于查找表中的所有数据，并按照某一列的升序排列。

```SELECT * FROM 表名 ORDER BY 列名 ASC;```3. 编写一个MySQL查询，用于查找表中的所有数据，并按照某一列的降序排列。

```SELECT * FROM 表名 ORDER BY 列名 DESC;```4. 编写一个MySQL查询，用于查找表中某一列的数据，并去除重复项。

```SELECT DISTINCT 列名 FROM 表名;```5. 编写一个MySQL查询，用于查找表中满足某个条件的数据。

```SELECT * FROM 表名 WHERE 条件;```6. 编写一个MySQL查询，用于查找表中满足多个条件的数据。

```SELECT * FROM 表名 WHERE 条件1 AND 条件2;```7. 编写一个MySQL查询，用于查找表中满足某个条件或另一个条件的数据。

```SELECT * FROM 表名 WHERE 条件1 OR 条件2;```8. 编写一个MySQL查询，用于计算表中某一列的总和。

```SELECT SUM(列名) FROM 表名;```9. 编写一个MySQL查询，用于计算表中某一列的平均值。

```SELECT AVG(列名) FROM 表名;```10. 编写一个MySQL查询，用于计算表中某一列的最大值。

```SELECT MAX(列名) FROM 表名;```11. 编写一个MySQL查询，用于计算表中某一列的最小值。

```SELECT MIN(列名) FROM 表名;```12. 编写一个MySQL查询，用于统计表中某一列的行数。

```SELECT COUNT(列名) FROM 表名;```13. 编写一个MySQL查询，用于在查询结果中限制返回的行数。

MySQL-50道经典sql题汇总题⽬是根据⾃⼰的理解做的，都能跑出来，但是肯定还有不⾜之处，欢迎⼤家提出问题，⼀起研究学习建表DROP TABLE IF EXISTS `course`;CREATE TABLE `course` (`CId` varchar(10) default NULL,`Cname` varchar(10) default NULL,`TId` varchar(10) default NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Table structure for sc-- ----------------------------DROP TABLE IF EXISTS `sc`;CREATE TABLE `sc` (`SId` varchar(10) default NULL,`CId` varchar(10) default NULL,`score` int(4) default NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Table structure for student-- ----------------------------DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (`SId` varchar(10) default NULL,`Sname` varchar(20) default NULL,`Sage` date default NULL,`Ssex` varchar(10) default NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Table structure for teacher-- ----------------------------DROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher` (`TId` varchar(10) default NULL,`Tname` varchar(20) default NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;添加数据courseINSERT INTO `course` VALUES ('01', 'CHINESE', '02');INSERT INTO `course` VALUES ('02', 'MATH', '01');INSERT INTO `course` VALUES ('03', 'ENGLISH', '03');INSERT INTO `course` VALUES ('04', 'JAVA', '04');scINSERT INTO `sc` VALUES ('01', '01', 80);INSERT INTO `sc` VALUES ('01', '03', 99);INSERT INTO `sc` VALUES ('02', '04', 50);INSERT INTO `sc` VALUES ('02', '03', 80);INSERT INTO `sc` VALUES ('03', '01', 80);INSERT INTO `sc` VALUES ('03', '03', 80);INSERT INTO `sc` VALUES ('04', '01', 50);INSERT INTO `sc` VALUES ('04', '03', 20);INSERT INTO `sc` VALUES ('05', '01', 100);INSERT INTO `sc` VALUES ('06', '01', 31);INSERT INTO `sc` VALUES ('06', '03', 34);INSERT INTO `sc` VALUES ('07', '03', 98);INSERT INTO `sc` VALUES ('07', '04', 55);INSERT INTO `sc` VALUES ('01', '04', 50);INSERT INTO `sc` VALUES ('01', '02', 10);INSERT INTO `sc` VALUES ('02', '02', 70);INSERT INTO `sc` VALUES ('03', '02', 50);INSERT INTO `sc` VALUES ('04', '02', 80);INSERT INTO `sc` VALUES ('05', '02', 73);INSERT INTO `sc` VALUES ('06', '02', 99);INSERT INTO `sc` VALUES ('07', '02', 100);INSERT INTO `sc` VALUES ('08', '02', 59);INSERT INTO `sc` VALUES ('09', '02', 80);INSERT INTO `sc` VALUES ('08', '01', 40);INSERT INTO `sc` VALUES ('08', '03', 35);studentINSERT INTO `student` VALUES ('01', 'Zhao Lei', '1990-1-1', 'MAN'); INSERT INTO `student` VALUES ('02', 'Qian Dian', '1990-12-21', 'MAN'); INSERT INTO `student` VALUES ('03', 'Sun Feng', '1990-5-20', 'MAN'); INSERT INTO `student` VALUES ('04', 'Li Yun', '1990-8-6', 'MAN'); INSERT INTO `student` VALUES ('05', 'Zhou Mei', '1991-12-1', 'WOMAN'); INSERT INTO `student` VALUES ('06', 'Wu Lan', '1992-3-1', 'WOMAN'); INSERT INTO `student` VALUES ('07', 'Zheng Zhu', '1989-7-1', 'WOMAN'); INSERT INTO `student` VALUES ('08', 'Wang Ju', '1990-1-20', 'WOMAN'); INSERT INTO `student` VALUES ('09', 'Wang Ju', '2020-8-30', 'MAN');teacherINSERT INTO `teacher` VALUES ('01', 'Li Pengfei');INSERT INTO `teacher` VALUES ('02', 'Wang Wen');INSERT INTO `teacher` VALUES ('03', 'Zhang Zhichao');INSERT INTO `teacher` VALUES ('04', 'Ye Ping');题⽬01、查询'01'课程⽐'02'课程成绩⾼的所有学⽣的学号；SELECT a.SId FROM (select SId,score from `sc` where CId = '01')as aINNER JOIN(select SId,score from `sc` where CId = '02')as bON a.SId = b.SIdINNER JOIN student as c ON c.SId = a.SIdwhere a.score>b.score02、查询平均成绩⼤于60分的同学的学号和平均成绩；select SId,AVG(score) FROM sc GROUP BY SId HAVING AVG(score)>6003、查询所有同学的学号、姓名、选课数、总成绩；⼀SELECT t1.SId,t1.Sname,(SELECT COUNT(CId) FROM sc s1 where s1.SId=t1.SId) 数量,(SELECT SUM(score) FROM sc s2 where s2.SId = t1.SId) 总成绩from student t1⼆select stu.SId , stu.Sname,COUNT(s.CId),sum(s.score) from sc as s join student as stuon s.SId = stu.SIdGROUP BY stu.SId04、查询姓“李”的⽼师的个数；SELECT COUNT(Tid) FROM teacher where Tname LIKE 'Li%'05、查询没学过“叶平”⽼师课的同学的学号、姓名； 思路：查找所有学过yp⽼师可的学⽣，然后看哪个Sid没在⾥⾯SELECT SId,Sname from student WHERE SId NOT IN(SELECT SId from sc where CId IN(SELECT CId FROM course where TId IN(SELECT TId FROM teacher where Tname ='Ye Ping')))06、查询学过“01”并且也学过编号“02”课程的同学的学号、姓名；SELECT SId,Sname from student st where(select COUNT(*) FROM sc s1 where s1.SId=st.SId and CId='01')>0and (select COUNT(*) from sc s2 where s2.SId=st.SId and CId='02')>0SELECT DISTINCT stu.SId , stu.Sname from sc as s join student as stu on s.SId = stu.SId where01 in (SELECT CId from sc where stu.SId = SId) and 02 in (SELECT CId from sc where stu.SId = SId)07、查询学过“Li Pengfei”⽼师所教的所有课的同学的学号、姓名；select DISTINCT stu.SId ,stu.Sname from student as stujoin sc as s on stu.SId = s.SIdwhere (SELECT COUNT(1) from sc as ss join course as c on ss.CId = c.CIdJOIN teacher as t on c.TId = t.TIdwhere s.SId = ss.SId and t.Tname = "Li PengFei" )=(select COUNT(1) from course as cou join teacher as tea on cou.TId = tea.TIdwhere tea.Tname = "Li PengFei")08、查询课程编号“02”的成绩⽐课程编号“01”课程低的所有同学的学号、姓名；⼀ SELECT st.SId,st.Sname from (SELECT SId,score from sc where CId = '02') aINNER JOIN (SELECT SId,score from sc where CId = '01') bON a.SId = b.SIdINNER JOIN student st ON st.SId = b.SId where a.score<b.score⼆ SELECT SId,Sname from student where SId IN(SELECT s1.SId from sc s1 ,sc s2 wheres1.SId = s2.Sid and s2.CId = '02' and s1.CId = '01' and s1.score>s2.score)09、查询所有课程成绩⼩于80分的同学的学号、姓名；SELECT SId ,sname from student where SId NOT IN (select SId from sc where score >=80)SELECT stu.SId,stu.Sname from student as stuwhere (SELECT COUNT(1) from sc where stu.SId = SId) = (SELECT COUNT(1) from sc WHERE stu.SId = SId and score<80) 10、查询没有学全所有课的同学的学号、姓名；SELECT SId,Sname from student where SId IN(select SId from scgroup BY SId HAVING (SELECT COUNT(CId) from course)=COUNT(SId))SELECT DISTINCT stu.SId,stu.Sname from student as stuwhere (SELECT COUNT(1) from sc where stu.SId = SId) = (SELECT COUNT(1) from course )11、查询⾄少有⼀门课与学号为“01”的同学所学相同的同学的学号和姓名；SELECT SId ,Sname from student where SId IN(select SId from sc where CId IN(select CId from sc where SId = '01'))12、查询学过学号为“07”同学所有门课的其他同学学号和姓名；select SId ,Sname from student where SId IN(SELECT SId from sc WHERECId IN(SELECT CId from sc where SId = '07')GROUP BY CId HAVING COUNT(SId)>=(SELECT COUNT(SId) from sc where SId = '07'))13.丢了 知道的可以告诉我⼀下14、查询和“07”号的同学学习的课程完全相同的其他同学学号和姓名；select s.SId,stu.Sname from sc as s join student as stu on s.SId = stu.SId wheres.SId not in (select SId from sc where CId not in (select CId from sc where SId = 07)) and(SELECT COUNT(1) from sc where SId = 07) = (SELECT COUNT(1) from sc where SId = s.SId)GROUP BY s.SId,stu.Sname15、删除学习“Li Pengfei”⽼师课的SC表记录；DELETE from sc where CId IN (select CId from course where TId = (SELECT TId from teacher where Tname = 'Li Pengfei'))16、向SC表中插⼊⼀些记录，这些记录要求符合以下条件：没有上过编号“03”课程的同学学号，“02”，以及“02”课的平均成绩；INSERT INTO sc SELECT SId,'02',b.av from sc,(SELECT AVG(score) AS av from sc where CId = '02') bwhere SId NOT IN (SELECT SId from sc s2 where CId ='03')17、按平均成绩从⾼到低显⽰所有学⽣的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显⽰：select st.Sname,a.score as 'MATH',b.score as 'ENGLISH',c.score as 'CHINESE' from(SELECT SId,score from sc where CId = (select CId FROM course where Cname = 'MATH')) as aINNER JOIN (SELECT SId,score from sc where CId = (select CId FROM course where Cname = 'ENGLISH')) as b ON a.SId = b.SIdINNER JOIN(SELECT SId,score from sc where CId = (select CId FROM course where Cname = 'CHINESE')) as c ON b.SId = c.SIdINNER JOIN student as st ON c.SId = st.SIdGROUP BY st.Sname ORDER BY AVG(a.score+b.score+c.score)DESC18、查询各科成绩最⾼和最低的分：以如下形式显⽰：course_id，max，minSELECT CId as 'coures_id',MAX(score) as 'max',MIN(score) as 'min'from scGROUP BY CId19、按各科平均成绩从低到⾼和及格率的百分数从⾼到低顺序SELECT CId,AVG(score) as '平均成绩',sum(case when score>60 then 1 else 0 end)/count(*)*100 as '及格率'from scGROUP BY CIdORDER BY AVG(score) asc,sum(case when score>=60 then 1 else 0 end)/count(*) descSELECT s.CId,ame,AVG(s.score),(SELECT COUNT(1) from sc where CId = s.CId and score>60)/(SELECT COUNT(1) from sc WHERE CId = s.CId) as 及格率from sc as s JOIN course as cou on s.CId = cou.CIdGROUP BY CIdORDER BY AVG(s.score) ASC,及格率 DESC20、查询如下课程平均成绩和及格率的百分数(⽤"1⾏"显⽰): math（01），chinese（02），english（03）select AVG(a.score) as 'MATH',SUM(case when a.score>=60 then 1 else 0 end)/count(a.SId)*100 as 'MATH%',AVG(b.score) as 'ENGLISH',sum(case when b.score>=60 then 1 else 0 end)/count(b.SId)*100 as 'ENGLISH%',AVG(c.score) as 'CHINESE', sum(case when c.score>=60 then 1 else 0 end)/count(c.SId)*100 as 'CHINESE%'from(SELECT SId,score from sc where CId = (select CId FROM course where Cname = 'MATH')) as aINNER JOIN (SELECT SId,score from sc where CId = (select CId FROM course where Cname = 'ENGLISH')) as b INNER JOIN(SELECT SId,score from sc where CId = (select CId FROM course where Cname = 'CHINESE')) as cSELECT s.CId,ame,AVG(s.score),(SELECT COUNT(1) from sc where CId = s.CId and score>60)/(SELECT COUNT(1) from sc WHERE CId = s.CId) as 及格率from sc as s JOIN course as cou on s.CId = cou.CId where s.CId in (01,02,03)GROUP BY CId21、查询不同⽼师所教不同课程平均分从⾼到低显⽰SELECT tea.Tname,ame,AVG(s.score) as 平均分from sc as s JOIN course as cou on s.CId = cou.CIdJOIN teacher as tea on tea.TId = cou.TIdGROUP BY s.CIdORDER BY 平均分 desc22、查询如下课程成绩第 3 名到第 6 名的学⽣成绩单：math(01),chinese(02),english(03)——student_id,student_name,math,chinese,english,avg_score;⼀：select stu.SId,stu.Sname,(SELECT score from sc as s1 where CId = "02" and SId = stu.SId) as 数学,(SELECT score from sc as s2 where CId = "01" and SId = stu.SId) as 语⽂,(SELECT score from sc as s3 where CId = "03" and SId = stu.SId) as 英语,avg((SELECT score from sc as s1 where CId = "02" and SId = stu.SId)+(SELECT score from sc as s1 where CId = "01" and SId = stu.SId)+(SELECT score from sc as s1 where CId = "03" and SId = stu.SId))/3 as 平均成绩from student stuGROUP BY stu.SIdORDER BY 平均成绩 desc LIMIT 2,4⼆：select stu.SId,stu.Sname,ms.score as "数学",ys.score as "语⽂",es.score as "英语" ,avg(ms.score+ys.score+es.score)/3 as "平均成绩" from(SELECT SId,score from sc where CId = "02") as msINNER join (SELECT SId,score from sc where CId = "01" )as ys on ms.SId = ys.SIdINNER join (SELECT SId,score from sc where CId = "03")as es on ys.SId = es.SIdINNER join student stu on stu.SId = es.SIdGROUP BY stu.SId,stu.Sname,ms.score,ys.score,es.scoreORDER BY avg(ms.score+ys.score+es.score) DESC23、统计列印各科成绩,各分数段⼈数:课程ID,课程名称,[100-85],[85-70],[70-60],[-60]--分组求和 sum case when then else endSELECT cou.CId ,ame,SUM(CASE when s.score>=85 then 1 ELSE 0 END) as "[100-85]" ,SUM(CASE when s.score<85 and s.score >=70 then 1 ELSE 0 END) as "[85-70]" ,SUM(CASE when s.score<70 and s.score>=60 then 1 ELSE 0 END) as "[70-60]" ,SUM(CASe when s.score<60 then 1 ELSE 0 END) as "60以下"from sc as s join course as cou on cou.CId = s.CIdGROUP BY cou.CId ,ame24、查询学⽣平均成绩及其名次select SId,平均成绩,(SELECT COUNT(1) from(SELECT SId,AVG(score) as 平均成绩 from sc GROUP BY SId)as a where a.平均成绩>b.平均成绩)+1 as 排名 from(select SId,AVG(score) as 平均成绩 from sc GROUP BY SId) as bGROUP BY SIdORDER BY 平均成绩 DESCSELECT(SELECT COUNT(1) from (SELECT AVG(score) as pjcj,SId from sc GROUP BY SId) as b where b.pjcj>a.平均成绩)+1 as 排名,姓名,平均成绩from(SELECT stu.Sname as 姓名, AVG(s.score) as 平均成绩 from sc as s join student as stu on s.SId = stu.SId GROUP BY stu.Sname)as aORDER BY 排名25、查询各科成绩前三名的记录:(不考虑成绩并列情况)//有瑕疵SELECT ame,b.score,b.SId,(SELECT COUNT(1) from sc as c where c.score>b.score and c.CId = b.CId)+1 as 排名fromsc as b join course as cou on b.CId = cou.CId where (select COUNT(1) from sc as a WHERE a.CId = b.CId and a.score>b.score order BY a.CId)<3 GROUP By ame,b.SIdORDER BY ame,b.score descSELECT s.CId ,s.SId ,s.score from(SELECT CId,SId,score,(SELECT COUNT(1) from sc as s2 WHERE s2.score>s1.score and s2.CId = s1.CId)+1 as c1 from sc as s1HAVING c1<4)as sGROUP BY s.CId ,s.SIdORDER BY s.CId, s.score DESC26、查询每门课程被选修的学⽣数select ame,(SELECT count(*) from sc as a WHERE a.CId = b.CId),COUNT(b.SId)from sc as b JOIN course as cou on b.CId = cou.CidGROUP BY ame27、查询出只选修了2门课程的全部学⽣的学号和姓名select stu.SId,stu.Sname from student as stu join (SELECT SId,COUNT(SId) from scGROUP BY SId HAVING COUNT(SId) = 2) as a on stu.SId = a.SId28、查询男⽣、⼥⽣⼈数SELECT Ssex,count(Ssex) from studentGROUP BY Ssex29、查询姓“王”的学⽣名单select * from student WHERE Sname LIKE "Wang%"30、查询同名同性学⽣名单，并统计同名⼈数select a.Sname,(SELECT COUNT(1) from student as b where a.Sname = b.Sname)as c from student as a GROUP BY a.SnameHAVING c>131、1990年出⽣的学⽣名单(注：Student表中Sage列的类型是datetime)select * from student where YEAR(Sage)=199032、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列SELECT s.CId,ame,AVG(s.score) from sc as s JOIN course as cou on s.CId = cou.CIdGROUP BY s.CIdORDER BY AVG(s.score) DESC ,s.CId ASC33、丢了 知道的告诉我⼀下34、查询课程名称为“MATH”，且分数低于60的学⽣姓名和分数SELECT stu.Sname,s.score from sc as s join student as stu on s.SId = stu.SIdwhere s.score<60 and s.CId = (SELECT CId from course where Cname = "MATH")35、查询所有学⽣的选课情况；⼀ select stu.SId,stu.Sname,s.CId,ame from sc as s join student as stu on s.SId = stu.SIdjoin course as cou on cou.CId = s.CIdGROUP BY stu.SId,s.CIdORDER BY s.SId asc⼆.合并SELECT stu.SId,stu.Sname,GROUP_CONCAT(ame SEPARATOR ' , ') as 选课 from sc as s join student as stu ON s.SId = stu.SId JOIN course as cou on cou.CId = s.CIdGROUP BY stu.SId36、查询每门课程成绩在70分以上的姓名、课程名称和分数；SELECT stu.Sname,ame,s.score from student as stu join sc as s on stu.SId = s.SId JOIN course as cou on cou.CId = s.CIdwhere s.score>=7037、查询不及格的课程，显⽰学号、姓名、课程号、成绩SELECT stu.SId,stu.Sname,s.CId,s.score from student as stu join sc as s on stu.SId = s.SIdwhere s.score <6038、查询课程编号为03且课程成绩在80分以上的学⽣的学号和姓名；SELECT stu.SId,stu.Sname from student as stu join sc as s on stu.SId = s.SIdwhere s.score >=80 and s.CId = 0339、求选了课程的学⽣⼈数去重SELECT COUNT(DISTINCT SId) as 选课⼈数 from sc40、查询选修“Li Pengfei”⽼师所授课程的学⽣中，成绩最⾼的学⽣姓名及其成绩select stu.Sname,s.score from student as stujoin sc as s on stu.SId = s.SIdwhere s.score = (SELECT MAX(score) from sc as s join course as cou on cou.CId = s.CIdjoin teacher as tea on tea.TId = cou.TId where tea.Tname = "Li Pengfei" )and s.CId = (SELECT DISTINCT s.CId from sc as s join course as cou on cou.CId = s.CIdjoin teacher as tea on tea.TId = cou.TId where tea.Tname = "Li Pengfei" )41、查询各个课程及相应的选修⼈数SELECT ame,count(1) from sc as s join course as cou on s.CId = cou.CIdGROUP BY ame42、查询不同课程成绩相同的学⽣的学号、课程号、学⽣成绩理解俩种意思：⼀：不同课程，不同学⽣，成绩相同SELECT stu.SId,stu.Sname,s.CId,s.score from student as stu join sc as s on stu.SId = s.SIdwhere s.score in (SELECT score from sc where SId!=s.SId)ORDER BY s.score DESC⼆：不同课程，相同学⽣，成绩相同（可以去重不⽤in）SELECT stu.SId,stu.Sname,s.CId,s.score from student as stu join sc as s on stu.SId = s.SIdwhere s.score in (SELECT score from sc where SId=s.SId and CId!=s.CId)ORDER BY s.score DESC43、查询每门课程成绩最好的前两名SELECT s.CId,stu.Sname,s.score from student as stu join sc as s on stu.SId = s.SIdwhere (SELECT COUNT(1) FROM sc where CId = S.CId and score>s.score)<2GROUP BY s.CId,stu.SnameORDER BY s.CId,s.score DESC44、统计每门课程的学⽣选修⼈数（超过5⼈的课程才统计）。

mysql 经典50道题sid英文回答：1. What is MySQL?MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL)for managing and manipulating data. It is widely used for building web applications and is known for its performance, scalability, and ease of use.2. What are the advantages of using MySQL?One advantage of using MySQL is its speed and performance. It is optimized for handling large amounts of data and can efficiently execute queries. Additionally, MySQL is highly scalable, allowing it to handle increasing amounts of data and users without sacrificing performance.Another advantage is its ease of use. MySQL has asimple and intuitive syntax for writing SQL queries, making it accessible to both beginners and experienced developers. It also has a large and active community, providing support and resources for troubleshooting and learning.Furthermore, MySQL is cross-platform compatible, meaning it can run on various operating systems such as Windows, Linux, and macOS. This flexibility allows developers to choose the environment that best suits their needs.中文回答：1. MySQL是什么？MySQL是一个开源的关系型数据库管理系统（RDBMS），使用结构化查询语言（SQL）来管理和操作数据。

mysql练习题（打印版）# MySQL练习题## 1. 基本数据操作题目：创建一个名为`students`的表，包含`id`（主键，自增），`name`（姓名），`age`（年龄），`grade`（年级）四个字段。

```sqlCREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50),age INT,grade VARCHAR(10));```题目：向`students`表中插入三个学生记录，姓名分别为“张三”，“李四”，“王五”，年龄分别为18，19，20，年级均为“大一”。

```sqlINSERT INTO students (name, age, grade) VALUES('张三', 18, '大一'),('李四', 19, '大一'),('王五', 20, '大一');```题目：查询`students`表中所有学生的姓名和年龄。

```sqlSELECT name, age FROM students;```题目：更新`students`表中名为“张三”的学生的年级为“大二”。

```sqlUPDATE students SET grade = '大二' WHERE name = '张三';```题目：删除`students`表中年龄为19的学生记录。

```sqlDELETE FROM students WHERE age = 19;```## 2. 高级查询题目：查询`students`表中年龄大于18岁的所有学生信息。

```sqlSELECT * FROM students WHERE age > 18;```题目：查询`students`表中年级为“大一”的学生的平均年龄。

mysql 50条语句1. 创建一个名为"users"的表，包含id、name和age三个字段，分别表示用户的唯一标识、姓名和年龄。

2. 在"users"表中插入一条记录，id为1，name为"小明"，age为18。

3. 查询"users"表中所有的记录。

4. 将"users"表中id为1的记录的name字段更新为"小红"。

5. 删除"users"表中id为1的记录。

6. 创建一个名为"products"的表，包含id、name和price三个字段，分别表示产品的唯一标识、名称和价格。

7. 在"products"表中插入一条记录，id为1，name为"手机"，price 为1999。

8. 查询"products"表中价格大于1000的记录。

9. 将"products"表中id为1的记录的price字段更新为2999。

10. 删除"products"表中id为1的记录。

11. 创建一个名为"orders"的表，包含id、user_id和product_id三个字段，分别表示订单的唯一标识、用户id和产品id。

12. 在"orders"表中插入一条记录，id为1，user_id为1，product_id为1。

13. 查询"orders"表中用户id为1的所有记录。

14. 将"orders"表中id为1的记录的product_id字段更新为2。

15. 删除"orders"表中id为1的记录。

16. 创建一个名为"categories"的表，包含id和name两个字段，分别表示分类的唯一标识和名称。

mysql-50道经典sql题English.Q1: Find all customers who have placed orders with a total value of over $1000.sql.SELECT customer_id, SUM(order_total) AS total_value.FROM orders.GROUP BY customer_id.HAVING total_value > 1000;Q2: Find all products that have been ordered more than 10 times.sql.SELECT product_id, COUNT() AS order_count.FROM order_items.GROUP BY product_id.HAVING order_count > 10;Q3: Find all employees who have worked on more than 5 projects.sql.SELECT employee_id, COUNT() AS project_count.FROM project_assignments.GROUP BY employee_id.HAVING project_count > 5;Q4: Find all orders that were shipped within 2 days of being placed.sql.SELECT order_id, DATEDIFF(ship_date, order_date) AS shipping_time.FROM orders.WHERE shipping_time <= 2;Q5: Find all customers who have placed at least one order in the last year.sql.SELECT customer_id.FROM orders.WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 1YEAR);Chinese.Q1: 找出所有总订单价值超过 1000 美元的客户。

MySQL1、以下选项中，（）用于描述数据在磁盘中如何存储。

——[单选题]A 外模式B 内模式C 概念模式D 以上答案都不正确正确答案：B2、以下选项描述错误的是（）。

——[单选题]A 等值连接的关系需要具有数目相等且可比的属性组B 自然连接的结果是等值连接去除重复的属性组C 除法可看作是笛卡尔积的逆运算D 以上说法都不正确正确答案：D3、对于关系R和S来说，（）表示属于S而不属于R的元组。

——[单选题]A R-SB S-RC R∪SD R∩S正确答案：B4、以下（）在关系模型中表示属性的取值范围。

——[单选题]A 元组B 键C 属性D 域正确答案：D5、下列（）不能称为实体。

——[单选题]A 班级B 手机C 图书D 姓名正确答案：D6、在数据库建模的过程中，E-R图属于（）的产物。

——[单选题]A 物理模型B 逻辑模型C 概念模型D 以上答案都不正确正确答案：C7、下列（）可以在命令提示符下停止MySQL服务器——[单选题]A net stopB net start mysqlC net stop mysqlD stop mysql正确答案：C8、以下选项中，不属于MySQL特点的是（）。

——[单选题]A 界面良好B 跨平台C 体积小D 速度快正确答案：A9、MySQL数据库服务器的默认端口号是（）。

——[单选题]A 80B 8080C 3306D 1433正确答案：C10、以下选项中，（）面向数据库设计人员，描述数据的整体逻辑结构。

——[单选题]A 概念模式B 存储模式C 外模式D 以上答案都不正确正确答案：A11、以下模式之间的映像能体现逻辑独立性的是（）。

——[单选题]A 外模式/内模式映像B 内模式/概念模式映像C 外模式/概念模式映像D 以上答案都不正确正确答案：C12、下面关于MySQL安装目录描述错误的是（）。

——[单选题]A lib目录用于存储一系列的库文件B include目录用于存放一些头文件C bin目录用于存放一些课执行文件D 以上答案都不正确正确答案：D13、下面关于命令“mysqld --initialize-insecure”描述错误的是（）。