精华内容
下载资源
问答
  • sql语句练习50题(Mysql版)

    万次阅读 多人点赞 2017-12-19 00:11:31
    习题来源于网络,sql语句是自己写的。欢迎指正。 表名和字段 –1....Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 –2....Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师...

    习题来源于网络,sql语句是自己写的,部分有参考。欢迎指正。


    2019.3.29更新
    写完后一年没有看过,没想到这篇文章有这么多人点击。博主工作到一半去考研了,目前已上岸某中部985,也算是比较幸运。非常感谢大家在评论里的留言,留言太多不能一一回复,希望大家见谅。这两天根据评论把文章中的某些错误或者不足的地方更新了下,,希望大家能够继续指出不足之处。


    表名和字段

    –1.学生表
    Student(s_id,s_name,s_birth,s_sex) --学生编号,学生姓名, 出生年月,学生性别
    –2.课程表
    Course(c_id,c_name,t_id) – --课程编号, 课程名称, 教师编号
    –3.教师表
    Teacher(t_id,t_name) --教师编号,教师姓名
    –4.成绩表
    Score(s_id,c_id,s_score) --学生编号,课程编号,分数

    测试数据

    --建表
    --学生表
    CREATE TABLE `Student`(
    	`s_id` VARCHAR(20),
    	`s_name` VARCHAR(20) NOT NULL DEFAULT '',
    	`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
    	`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
    	PRIMARY KEY(`s_id`)
    );
    --课程表
    CREATE TABLE `Course`(
    	`c_id`  VARCHAR(20),
    	`c_name` VARCHAR(20) NOT NULL DEFAULT '',
    	`t_id` VARCHAR(20) NOT NULL,
    	PRIMARY KEY(`c_id`)
    );
    --教师表
    CREATE TABLE `Teacher`(
    	`t_id` VARCHAR(20),
    	`t_name` VARCHAR(20) NOT NULL DEFAULT '',
    	PRIMARY KEY(`t_id`)
    );
    --成绩表
    CREATE TABLE `Score`(
    	`s_id` VARCHAR(20),
    	`c_id`  VARCHAR(20),
    	`s_score` INT(3),
    	PRIMARY KEY(`s_id`,`c_id`)
    );
    --插入学生表测试数据
    insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
    insert into Student values('02' , '钱电' , '1990-12-21' , '男');
    insert into Student values('03' , '孙风' , '1990-05-20' , '男');
    insert into Student values('04' , '李云' , '1990-08-06' , '男');
    insert into Student values('05' , '周梅' , '1991-12-01' , '女');
    insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
    insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
    insert into Student values('08' , '王菊' , '1990-01-20' , '女');
    --课程表测试数据
    insert into Course values('01' , '语文' , '02');
    insert into Course values('02' , '数学' , '01');
    insert into Course values('03' , '英语' , '03');
    
    --教师表测试数据
    insert into Teacher values('01' , '张三');
    insert into Teacher values('02' , '李四');
    insert into Teacher values('03' , '王五');
    
    --成绩表测试数据
    insert into Score values('01' , '01' , 80);
    insert into Score values('01' , '02' , 90);
    insert into Score values('01' , '03' , 99);
    insert into Score values('02' , '01' , 70);
    insert into Score values('02' , '02' , 60);
    insert into Score values('02' , '03' , 80);
    insert into Score values('03' , '01' , 80);
    insert into Score values('03' , '02' , 80);
    insert into Score values('03' , '03' , 80);
    insert into Score values('04' , '01' , 50);
    insert into Score values('04' , '02' , 30);
    insert into Score values('04' , '03' , 20);
    insert into Score values('05' , '01' , 76);
    insert into Score values('05' , '02' , 87);
    insert into Score values('06' , '01' , 31);
    insert into Score values('06' , '03' , 34);
    insert into Score values('07' , '02' , 89);
    insert into Score values('07' , '03' , 98);
    

    练习题和sql语句

    -- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数	
    	
    select a.* ,b.s_score as 01_score,c.s_score as 02_score from 
    student a 
    	join score b on a.s_id=b.s_id and b.c_id='01'
    	left join score c on a.s_id=c.s_id and c.c_id='02' or c.c_id = NULL where b.s_score>c.s_score
    	
    --也可以这样写
    	select a.*,b.s_score as 01_score,c.s_score as 02_score from student 		  a,score b,score c 
    			where a.s_id=b.s_id 
    			and a.s_id=c.s_id 
    			and b.c_id='01' 
    			and c.c_id='02' 
    			and b.s_score>c.s_score
    -- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
    	
    select a.* ,b.s_score as 01_score,c.s_score as 02_score from 
    	student a left join score b on a.s_id=b.s_id and b.c_id='01' or b.c_id=NULL 
    	 join score c on a.s_id=c.s_id and c.c_id='02' where b.s_score<c.s_score
    			
    
    -- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
    select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from 
    	student b 
    	join score a on b.s_id = a.s_id
    	GROUP BY b.s_id,b.s_name HAVING avg_score >=60;
    	
    
    -- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
    		-- (包括有成绩的和无成绩的)
    		
    select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from 
    	student b 
    	left join score a on b.s_id = a.s_id
    	GROUP BY b.s_id,b.s_name HAVING avg_score <60
    	union
    select a.s_id,a.s_name,0 as avg_score from 
    	student a 
    	where a.s_id not in (
    				select distinct s_id from score);
    
    
    -- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
    select a.s_id,a.s_name,count(b.c_id) as sum_course,sum(b.s_score) as sum_score from 
    	student a 
    	left join score b on a.s_id=b.s_id
    	GROUP BY a.s_id,a.s_name;
    			
    			
    -- 6、查询"李"姓老师的数量 
    select count(t_id) from teacher where t_name like '李%';
    	
    -- 7、查询学过"张三"老师授课的同学的信息 
    select a.* from 
    	student a 
    	join score b on a.s_id=b.s_id where b.c_id in(
    		select c_id from course where t_id =(
    			select t_id from teacher where t_name = '张三'));
    
    -- 8、查询没学过"张三"老师授课的同学的信息 
    select * from 
        student c 
        where c.s_id not in(
            select a.s_id from student a join score b on a.s_id=b.s_id where b.c_id in(
            select a.c_id from course a join teacher b on a.t_id = b.t_id where t_name ='张三'));
    -- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
    
    select a.* from 
    	student a,score b,score c 
    	where a.s_id = b.s_id  and a.s_id = c.s_id and b.c_id='01' and c.c_id='02';
    	
    -- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
    			
    select a.* from 
    	student a 
    	where a.s_id in (select s_id from score where c_id='01' ) and a.s_id not in(select s_id from score where c_id='02')
    			
    
    -- 11、查询没有学全所有课程的同学的信息 
    --@wendiepei的写法
    select s.* from student s 
    left join Score s1 on s1.s_id=s.s_id
    group by s.s_id having count(s1.c_id)<(select count(*) from course)	
    --@k1051785839的写法
    select *
    from student
    where s_id not in(
    select s_id from score t1  
    group by s_id having count(*) =(select count(distinct c_id)  from course)) 
    -- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 
    
    select * from student where s_id in(
    	select distinct a.s_id from score a where a.c_id in(select a.c_id from score a where a.s_id='01')
    	);
    			
    -- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 
    --@ouyang_1993的写法
    SELECT
     Student.*
    FROM
     Student
    WHERE
     s_id IN (SELECT s_id FROM Score GROUP BY s_id HAVING COUNT(s_id) = (
        #下面的语句是找到'01'同学学习的课程数
        SELECT COUNT(c_id) FROM Score WHERE s_id = '01'
       )
     )
    AND s_id NOT IN (
     #下面的语句是找到学过‘01’同学没学过的课程,有哪些同学。并排除他们
     SELECT s_id FROM Score
     WHERE c_id IN(
       #下面的语句是找到‘01’同学没学过的课程
       SELECT DISTINCT c_id FROM Score
       WHERE c_id NOT IN (
         #下面的语句是找出‘01’同学学习的课程
         SELECT c_id FROM Score WHERE s_id = '01'
        )
      ) GROUP BY s_id
    ) #下面的条件是排除01同学
    AND s_id NOT IN ('01')
    --@k1051785839的写法
    SELECT
     t3.*
    FROM
     (
      SELECT
       s_id,
       group_concat(c_id ORDER BY c_id) group1
      FROM
       score
      WHERE
       s_id &lt;> '01'
      GROUP BY
       s_id
     ) t1
    INNER JOIN (
     SELECT
      group_concat(c_id ORDER BY c_id) group2
     FROM
      score
     WHERE
      s_id = '01'
     GROUP BY
      s_id
    ) t2 ON t1.group1 = t2.group2
    INNER JOIN student t3 ON t1.s_id = t3.s_id
    
    -- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名 
    select a.s_name from student a where a.s_id not in (
    	select s_id from score where c_id = 
    				(select c_id from course where t_id =(
    					select t_id from teacher where t_name = '张三')));
    
    -- 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩 
    select a.s_id,a.s_name,ROUND(AVG(b.s_score)) from 
    	student a 
    	left join score b on a.s_id = b.s_id
    	where a.s_id in(
    			select s_id from score where s_score<60 GROUP BY  s_id having count(1)>=2)
    	GROUP BY a.s_id,a.s_name
    
    -- 16、检索"01"课程分数小于60,按分数降序排列的学生信息
    select a.*,b.c_id,b.s_score from 
    	student a,score b 
    	where a.s_id = b.s_id and b.c_id='01' and b.s_score<60 ORDER BY b.s_score DESC;
    		
    -- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
    select a.s_id,(select s_score from score where s_id=a.s_id and c_id='01') as 语文,
    				(select s_score from score where s_id=a.s_id and c_id='02') as 数学,
    				(select s_score from score where s_id=a.s_id and c_id='03') as 英语,
    			round(avg(s_score),2) as 平均分 from score a  GROUP BY a.s_id ORDER BY 平均分 DESC;
    --@喝完这杯还有一箱的写法
    SELECT a.s_id,MAX(CASE a.c_id WHEN '01' THEN a.s_score END ) 语文, 
    MAX(CASE a.c_id WHEN '02' THEN a.s_score END ) 数学, 
    MAX(CASE a.c_id WHEN '03' THEN a.s_score END ) 英语, 
    avg(a.s_score),b.s_name FROM Score a JOIN Student b ON a.s_id=b.s_id GROUP BY a.s_id ORDER BY 5 DESC		
    -- 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
    --及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
    select a.c_id,b.c_name,MAX(s_score),MIN(s_score),ROUND(AVG(s_score),2),
    	ROUND(100*(SUM(case when a.s_score>=60 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 及格率,
    	ROUND(100*(SUM(case when a.s_score>=70 and a.s_score<=80 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 中等率,
    	ROUND(100*(SUM(case when a.s_score>=80 and a.s_score<=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优良率,
    	ROUND(100*(SUM(case when a.s_score>=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优秀率
    	from score a left join course b on a.c_id = b.c_id GROUP BY a.c_id,b.c_name
    	
    -- 19、按各科成绩进行排序,并显示排名
    -- mysql没有rank函数
    	select a.s_id,a.c_id,
            @i:=@i +1 as i保留排名,
            @k:=(case when @score=a.s_score then @k else @i end) as rank不保留排名,
            @score:=a.s_score as score
        from (
            select s_id,c_id,s_score from score GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
    )a,(select @k:=0,@i:=0,@score:=0)s
    --@k1051785839的写法
    (select * from (select 
    t1.c_id,
    t1.s_score,
    (select count(distinct t2.s_score) from score t2 where t2.s_score>=t1.s_score and t2.c_id='01') rank
    FROM score t1 where t1.c_id='01'
    order by t1.s_score desc) t1)
    union
    (select * from (select 
    t1.c_id,
    t1.s_score,
    (select count(distinct t2.s_score) from score t2 where t2.s_score>=t1.s_score and t2.c_id='02') rank
    FROM score t1 where t1.c_id='02'
    order by t1.s_score desc) t2)
    union
    (select * from (select 
    t1.c_id,
    t1.s_score,
    (select count(distinct t2.s_score) from score t2 where t2.s_score>=t1.s_score and t2.c_id='03') rank
    FROM score t1 where t1.c_id='03'
    order by t1.s_score desc) t3)
    -- 20、查询学生的总成绩并进行排名
    select a.s_id,
    	@i:=@i+1 as i,
    	@k:=(case when @score=a.sum_score then @k else @i end) as rank,
    	@score:=a.sum_score as score
    from (select s_id,SUM(s_score) as sum_score from score GROUP BY s_id ORDER BY sum_score DESC)a,
    	(select @k:=0,@i:=0,@score:=0)s
    	
    -- 21、查询不同老师所教不同课程平均分从高到低显示 
    		
    	select a.t_id,c.t_name,a.c_id,ROUND(avg(s_score),2) as avg_score from course a
    		left join score b on a.c_id=b.c_id 
    		left join teacher c on a.t_id=c.t_id
    		GROUP BY a.c_id,a.t_id,c.t_name ORDER BY avg_score DESC;
    -- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
    			
    			select d.*,c.排名,c.s_score,c.c_id from (
                    select a.s_id,a.s_score,a.c_id,@i:=@i+1 as 排名 from score a,(select @i:=0)s where a.c_id='01'  
    								ORDER BY a.s_score DESC  
                )c
                left join student d on c.s_id=d.s_id
                where 排名 BETWEEN 2 AND 3
                UNION
                select d.*,c.排名,c.s_score,c.c_id from (
                    select a.s_id,a.s_score,a.c_id,@j:=@j+1 as 排名 from score a,(select @j:=0)s where a.c_id='02'  
    								ORDER BY a.s_score DESC
                )c
                left join student d on c.s_id=d.s_id
                where 排名 BETWEEN 2 AND 3
                UNION
                select d.*,c.排名,c.s_score,c.c_id from (
                    select a.s_id,a.s_score,a.c_id,@k:=@k+1 as 排名 from score a,(select @k:=0)s where a.c_id='03' 
    								ORDER BY a.s_score DESC
                )c
                left join student d on c.s_id=d.s_id
                where 排名 BETWEEN 2 AND 3;
    			
    -- 23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
    
    
    		select distinct f.c_name,a.c_id,b.`85-100`,b.百分比,c.`70-85`,c.百分比,d.`60-70`,d.百分比,e.`0-60`,e.百分比 from score a
    				left join (select c_id,SUM(case when s_score >85 and s_score <=100 then 1 else 0 end) as `85-100`,
    											ROUND(100*(SUM(case when s_score >85 and s_score <=100 then 1 else 0 end)/count(*)),2) as 百分比
    								from score GROUP BY c_id)b on a.c_id=b.c_id
    				left join (select c_id,SUM(case when s_score >70 and s_score <=85 then 1 else 0 end) as `70-85`,
    											ROUND(100*(SUM(case when s_score >70 and s_score <=85 then 1 else 0 end)/count(*)),2) as 百分比
    								from score GROUP BY c_id)c on a.c_id=c.c_id
    				left join (select c_id,SUM(case when s_score >60 and s_score <=70 then 1 else 0 end) as `60-70`,
    											ROUND(100*(SUM(case when s_score >60 and s_score <=70 then 1 else 0 end)/count(*)),2) as 百分比
    								from score GROUP BY c_id)d on a.c_id=d.c_id
    				left join (select c_id,SUM(case when s_score >=0 and s_score <=60 then 1 else 0 end) as `0-60`,
    											ROUND(100*(SUM(case when s_score >=0 and s_score <=60 then 1 else 0 end)/count(*)),2) as 百分比
    								from score GROUP BY c_id)e on a.c_id=e.c_id
    				left join course f on a.c_id = f.c_id
    				 
    -- 24、查询学生平均成绩及其名次 
    
    		select a.s_id,
    				@i:=@i+1 as '不保留空缺排名',
    				@k:=(case when @avg_score=a.avg_s then @k else @i end) as '保留空缺排名',
    				@avg_score:=avg_s as '平均分'
    		from (select s_id,ROUND(AVG(s_score),2) as avg_s from score GROUP BY s_id ORDER BY avg_s DESC)a,(select @avg_score:=0,@i:=0,@k:=0)b;
    -- 25、查询各科成绩前三名的记录
    			-- 1.选出b表比a表成绩大的所有组
    			-- 2.选出比当前id成绩大的 小于三个的
    		select a.s_id,a.c_id,a.s_score from score a 
    			left join score b on a.c_id = b.c_id and a.s_score<b.s_score
    			group by a.s_id,a.c_id,a.s_score HAVING COUNT(b.s_id)<3
    			ORDER BY a.c_id,a.s_score DESC
    
    -- 26、查询每门课程被选修的学生数 
    
    		select c_id,count(s_id) from score a GROUP BY c_id
    
    -- 27、查询出只有两门课程的全部学生的学号和姓名 
    		select s_id,s_name from student where s_id in(
    				select s_id from score GROUP BY s_id HAVING COUNT(c_id)=2);
    
    -- 28、查询男生、女生人数 
    		select s_sex,COUNT(s_sex) as 人数  from student GROUP BY s_sex
    
    -- 29、查询名字中含有"风"字的学生信息
    
    		select * from student where s_name like '%风%';
    
    -- 30、查询同名同性学生名单,并统计同名人数 
    		
    		select a.s_name,a.s_sex,count(*) from student a  JOIN 
    					student b on a.s_id !=b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex
    		GROUP BY a.s_name,a.s_sex
    
    
    
    -- 31、查询1990年出生的学生名单
    		
    		select s_name from student where s_birth like '1990%'
    
    -- 32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列 
    
    	select c_id,ROUND(AVG(s_score),2) as avg_score from score GROUP BY c_id ORDER BY avg_score DESC,c_id ASC
    
    -- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 
    
    	select a.s_id,b.s_name,ROUND(avg(a.s_score),2) as avg_score from score a
    		left join student b on a.s_id=b.s_id GROUP BY s_id HAVING avg_score>=85
    	
    -- 34、查询课程名称为"数学",且分数低于60的学生姓名和分数 
    	
    		select a.s_name,b.s_score from score b join student a on a.s_id=b.s_id where b.c_id=(
    					select c_id from course where c_name ='数学') and b.s_score<60
    
    -- 35、查询所有学生的课程及分数情况; 
    	
    		
    		select a.s_id,a.s_name,
    					SUM(case c.c_name when '语文' then b.s_score else 0 end) as '语文',
    					SUM(case c.c_name when '数学' then b.s_score else 0 end) as '数学',
    					SUM(case c.c_name when '英语' then b.s_score else 0 end) as '英语',
    					SUM(b.s_score) as  '总分'
    		from student a left join score b on a.s_id = b.s_id 
    		left join course c on b.c_id = c.c_id 
    		GROUP BY a.s_id,a.s_name
    
    
     -- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数; 
    			select a.s_name,b.c_name,c.s_score from course b left join score c on b.c_id = c.c_id
    				left join student a on a.s_id=c.s_id where c.s_score>=70
    
    		
    
    -- 37、查询不及格的课程
    		select a.s_id,a.c_id,b.c_name,a.s_score from score a left join course b on a.c_id = b.c_id
    			where a.s_score<60 
    		
    --38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名; 
    		select a.s_id,b.s_name from score a LEFT JOIN student b on a.s_id = b.s_id
    			where a.c_id = '01'	and a.s_score>80
    
    -- 39、求每门课程的学生人数 
    		select count(*) from score GROUP BY c_id;
    
    -- 40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
    
    		
    		-- 查询老师id	
    		select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name='张三'
    		-- 查询最高分(可能有相同分数)
    		select MAX(s_score) from score where c_id='02'
    		-- 查询信息
    		select a.*,b.s_score,b.c_id,c.c_name from student a
    			LEFT JOIN score b on a.s_id = b.s_id
    			LEFT JOIN course c on b.c_id=c.c_id
    			where b.c_id =(select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name='张三')
    			and b.s_score in (select MAX(s_score) from score where c_id='02')
    
    
    -- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 
    	select DISTINCT b.s_id,b.c_id,b.s_score from score a,score b where a.c_id != b.c_id and a.s_score = b.s_score
    	
    
    -- 42、查询每门功成绩最好的前两名 
    		-- 牛逼的写法
    	select a.s_id,a.c_id,a.s_score from score a
    		where (select COUNT(1) from score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 ORDER BY a.c_id
    
    
    -- 43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列  
    		select c_id,count(*) as total from score GROUP BY c_id HAVING total>5 ORDER BY total,c_id ASC
    		
    -- 44、检索至少选修两门课程的学生学号 
    		select s_id,count(*) as sel from score GROUP BY s_id HAVING sel>=2
    
    -- 45、查询选修了全部课程的学生信息 
    		select * from student where s_id in(		
    			select s_id from score GROUP BY s_id HAVING count(*)=(select count(*) from course))
    
    
    --46、查询各学生的年龄
    	-- 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
    
    	select s_birth,(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birth,'%Y') - 
    				(case when DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(s_birth,'%m%d') then 0 else 1 end)) as age
    		from student;
    
    
    -- 47、查询本周过生日的学生
    	select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth)
    	select * from student where YEARWEEK(s_birth)=YEARWEEK(DATE_FORMAT(NOW(),'%Y%m%d'))
    	
    	select WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))
    
    -- 48、查询下周过生日的学生
    	select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =WEEK(s_birth)
    
    -- 49、查询本月过生日的学生
    
    	select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d')) =MONTH(s_birth)
    	
    -- 50、查询下月过生日的学生
    	select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =MONTH(s_birth)
    
    
    展开全文
  • docker_3 docker 部署练习

    万次阅读 2021-01-08 14:39:31
    docker 部署练习3.1 部署 nginx3.2 部署 tomcat3.3 部署 elastic search 3. docker 部署练习 3.1 部署 nginx # 下载 nginx 镜像 [root@izbp10tup89om84qulgxbsz ~]# docker pull nginx # 启动 nginx 镜像 # -d ...

    https://gitee.com/fakerlove/docker

    3. docker 部署练习

    3.1 部署 nginx

    # 下载 nginx 镜像
    [root@izbp10tup89om84qulgxbsz ~]# docker pull nginx
    
    # 启动 nginx 镜像
    # -d		表示后台运行
    # --name	给容器命名
    # -p		暴露端口	格式:宿主机端口:容器内部端口
    [root@izbp10tup89om84qulgxbsz ~]# docker run -d --name nginx01 -p:3344:80 nginx
    
    # 本机测试连接(出现 Welcome to nginx! 即表示连接成功)
    [root@izbp10tup89om84qulgxbsz ~]# curl localhost:3344
    

    外网测试连接:
    在这里插入图片描述

    # 查看容器
    [root@izbp10tup89om84qulgxbsz ~]# docker ps
    CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                  NAMES
    061942db0498        nginx               "/docker-entrypoint.…"   20 minutes ago      Up 20 minutes       0.0.0.0:3344->80/tcp   nginx01
    
    # 进入 nginx 容器
    [root@izbp10tup89om84qulgxbsz ~]# docker exec -it nginx01 /bin/bash
    root@061942db0498:/# 
    
    # 在容器中查找 nginx 配置文件
    root@061942db0498:/# whereis nginx 
    nginx: /usr/sbin/nginx /usr/lib/nginx /etc/nginx /usr/share/nginx
    
    # 退出容器
    root@061942db0498:/# exit
    exit
    
    # 停止容器
    [root@izbp10tup89om84qulgxbsz ~]# docker stop 061942db0498
    061942db0498
    

    3.2 部署 tomcat

    # 下载镜像
    [root@izbp10tup89om84qulgxbsz ~]# docker pull tomcat
    
    # 查看本地镜像
    [root@izbp10tup89om84qulgxbsz ~]# docker images
    REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
    tomcat              latest              dab3cf97dd54        4 days ago          648MB
    
    # 启动镜像
    [root@izbp10tup89om84qulgxbsz ~]# docker run -d -p 3355:8080 tomcat01 tomcat
    
    # 本地访问
    [root@izbp10tup89om84qulgxbsz ~]# curl localhost:3355
    <!doctype html><html lang="en"><head><title>HTTP Status 404 – Not Found</title><style type="text/css">body {font-family:Tahoma,Arial,sans-serif;} h1, h2, h3, b {color:white;background-color:#525D76;} h1 {font-size:22px;} h2 {font-size:16px;} h3 {font-size:14px;} p {font-size:12px;} a {color:black;} .line {height:1px;background-color:#525D76;border:none;}</style></head><body><h1>HTTP Status 404 – Not Found</h1><hr class="line" /><p><b>Type</b> Status Report</p><p><b>Description</b> The origin server did not find a current representation for the target resource or is not willing to disclose that one exists.</p><hr class="line" /><h3>Apache Tomcat/9.0.39</h3></body></html>\
    

    外网测试访问(连接成功,但是报404,因为这个 tomcat 是被阉割的):

    3.3 部署 elastic search

    # 下载运行elasticsearch镜像
    [root@izbp10tup89om84qulgxbsz ~]# docker run -d --name elasticsearch -p 9200:9200 -p 9300:9300 -e "discovery.type=single-node" elasticsearch:7.6.2
    
    # 发现并没有成功运行(原因是elastsearch极耗内存,而我的服务器内存不够)
    [root@izbp10tup89om84qulgxbsz /]# docker ps
    CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS               NAMES
    
    # 如果你和我一样,服务器内存不够用,建议使用下面的命令限制内存启动 elasticsearch
    [root@izbp10tup89om84qulgxbsz ~]# docker run -d --name elasticsearch -p 9200:9200 -p 9300:9300 -e "discovery.type=single-node" -e ES_JAVA_OPTS="-Xms64m -Xmx512m" elasticsearch:7.6.2
    
    # 启动后可以使用如下命令查看容器的内存占用情况:
    [root@izbp10tup89om84qulgxbsz /]# docker stats 784a6bb21c2d
    CONTAINER ID        NAME                CPU %               MEM USAGE / LIMIT     MEM %               NET I/O             BLOCK I/O           PIDS
    784a6bb21c2d        elasticsearch       0.39%               359.9MiB / 1.796GiB   19.57%              0B / 0B             265MB / 696kB       42
    
    # 本地访问es
    [root@izbp10tup89om84qulgxbsz /]# curl localhost:9200
    

    • (补充)Portainer 可视化面板的安装:
    # 下载并运行 Portainer
    docker run -d -p 8088:9000 \
    --restart=always -v /var/run/docker.sock:/var/run/docker.sock --privileged=true portainer/portainer
    # 内网访问(会很快)
    [root@izbp10tup89om84qulgxbsz /]# curl localhost:8088
    

    外网访问8088端口(第一次访问会非常非常慢):

    在这里插入图片描述
    第一次访问,需要添加用户名和密码:
    在这里插入图片描述
    在这里插入图片描述
    看看就好,反正不好用:

    在这里插入图片描述

    展开全文
  • 刻意练习

    千次阅读 2018-12-28 20:58:16
    刻意练习是什么 练习方法不对,越练越错 专注于练习20%的核心技能 让大脑跟随身体,将技能变成习惯 为什么你会在淋浴、开车和刷牙时脑洞打开 集中时间和精力设定练习目标 练习最擅长的,放大优势效应 专项练习宁多...

    刻意练习是什么

    练习方法不对,越练越错
    专注于练习20%的核心技能
    让大脑跟随身体,将技能变成习惯
    为什么你会在淋浴、开车和刷牙时脑洞打开
    集中时间和精力设定练习目标
    练习最擅长的,放大优势效应
    专项练习宁多勿少,实战演练宁少勿多
    反复练习正确动作,加强大脑记忆


    如何进行刻意练习

    研究成功者,并进行正确复制
    将技能分解,进行专项练习
    为练习命名,减少沟通成本
    模拟真实环境,整合所练技能
    制定优质练习计划,有效推进练习
    建立紧凑的练习程序,翻倍练习成效


    如何让练习更精准

    高手正确的示范和讲解,让练习如虎添翼
    预先知道练习关键点,最简单却能产生奇效
    眼见为实,才能坚持不懈
    为什么外语老师要用外语教学
    孩子模仿天赋中的练习秘诀
    为什么教孩子系鞋带那么难
    成为足球教练所坚持的练习方法
    记录关键镜头,反复推敲


    如何扩大练习效果

    有效利用反馈,及时改进
    行动、行动,永远要行动在先
    即时反馈,速度决定了练习的质量
    正面反馈,激发优势的力量
    具体可行的反馈,让练习更有成效
    关注重点,解决最紧要的问题
    让关注反馈成为一种习惯
    正确领悟反馈  概况反馈、排列反馈的优先次序、下一步行动


    如何在团队中刻意练习

    敢于面对错误
    克服练习障碍
    对抗惰性,让练习充满乐趣
    组建练习圈子
    在共赢中实现个人进步
    最大限度的发掘潜力
    练习需要得到认可


    如何实践在刻意练习中获得的新技能

    在实战中找准观察点,精确解决问题
    善用简短提示,锤炼新技能
    高效沟通,强化新技能
    不断挑战练习的新高度
    精益求精,高效评估练习效果和影响

     

            一口气读完了,感觉打开了一扇门,倒不是惊奇于文章中展示的技巧与方法,而是再一次佐证了成功不仅仅是天赋,是一套方法论,是坚持、意志力的完美绽放。


     

    展开全文
  • KG(KeyGame)是一款精心设计的键盘练习游戏,适合新手、初级、中级到高级所有需要熟悉键盘的人使用。具有以下特点: ·一次只出现一个字母,便于集中注意力练习。 ·由简单到复杂,循序渐进。 ·对新级别熟悉到一定...
  • bugku逆向练习

    千次阅读 2020-11-18 23:14:40
    bugku逆向练习

    call

    1找到main函数,在关键判断,发现一串数字,输入就弹出flag

    word

    动调产生代码,异或生成管理员密码,

    temp=[0xa3,0xa1,0x70,0xa6,0xf3,0xd7]
    result=[0xd0,0xcf,0x11,0xe0,0xa1,0xb1]
    flag=[0 for i in range(6)]
    for i in range(6):
    	flag[i]=temp[i]^result[i]
    print(''.join(map(chr,flag)))
    

    输入管理员密码后生成了一个flag.doc,但是需要密码,2选项是一个地图,走出地图,输入路径得到文档密码,
    2

    输入之后得到flag

    展开全文
  • Java练习网站

    千次阅读 2019-11-19 21:01:09
    练习练习练习
  • 【SICP练习】1 练习1.1-练习1.5

    千次阅读 2015-02-05 11:05:20
    练习1.1 这道题主要是关于简单的数字运算,我们看完之后可以在MIT-Scheme中进行验算。如果环境不熟悉,可以参见【Scheme归纳】的第一篇博文。如果发现在MIT-Scheme等环境上的运算结果和自己算的不一样,也应该再...
  • shell基础练习

    万次阅读 多人点赞 2018-02-14 16:58:53
    shell基础练习 1、编写shell脚本,实现1-100的猜数字游戏。 #!/bin/bash ##RANDOM随机函数,100取余就可以获得1-100的随机整数 n=$[$RANDOM%100] while : do read -p "请输入一个1-100间的整数:" n1 n2=`...
  • C Primer Plus 第六版 所有章节课后编程练习答案

    万次阅读 多人点赞 2017-11-01 20:25:56
    C Primer Plus 第六版 所有章节课后编程练习答案
  • SQL在线练习网站

    万次阅读 多人点赞 2018-07-04 21:43:57
    介绍一款好用的在线SQL语句练习工具,详情见下图。软件的地址是:http://sqlfiddle.com/   
  • mysql 练习

    2017-05-13 13:19:49
    最近找到一个不错的 mysql sql语句练习英文网站,分享下。有兴趣的同学可以查看原文 原资源地址 原网站支持在线脚本练习,推荐网速不错的同学直接使用在线资源。 接下来我会选取其中不错的练习题,并记录下来。接...
  • oscp练习目录

    万次阅读 2020-01-25 18:06:41
    0x00 前言 ...这里将oscp所做的练习进行一个总结,并且将每一个题所需要的内容以及可以练习学习到的东西进行汇总。 0x01 vulnhub总览 题目 链接 文章链接 Me and My Girlfriend 1 ...
  • 【SICP练习】72 练习2.43

    千次阅读 2015-02-22 14:02:10
    因为对于(enumerate-interval 1 board-size),Louis的过程会产生(queen-cols (- k 1))个棋盘,而上一练习中给出的代码则只产生board-size个棋盘。上一练习中,如果board-size为8,则会产生行列均为1、2……8的8个...
  • LeetCode 练习

    千次阅读 2018-02-08 10:31:42
    LintCode 与 LeetCode 练习 LintCode 官网 LintCode 793. Intersection of Arrays 求数组的交集的size LintCode 792.Kth Prime Number 判断素数n是第几个素数 LintCode 761. Smallest Subset LintCode 744. ...
  • 【SICP练习】92 练习2.65

    千次阅读 2015-03-05 10:47:39
    练习2.65最后一道题了,来个总结倒是很不错。要完成两个函数,我们就要用到前面所学,首先用练习2.63中的函数将树变成表,这样有利于后续的处理,而根据前面的习题,用tree->list-2会更加快速。然后要实现并集或补集...
  • CTF练习平台

    千次阅读 2019-09-27 17:16:03
    CTF资源(练习网站,偏向web) 既然能看到这篇文章,CTF应该不用我再介绍了 ,这篇文章主要分享一些练习资源,只有平时多练习才能增加丰富的经验,这篇文章适合小白阶段的日常难度后续等我掌握再多我再介绍更高深度...
  • 【SICP练习】20 练习1.26

    千次阅读 2015-02-07 10:30:11
    练习1.26 这本书的练习好像很多都和某个人有关,不愧是一本经典著作,通过MIT大量的修修补补。下面我们进入正题吧,Louis的问题就在于计算了2次(expmod base (/ exp 2) m),如果是用的square,则只会计算一次。更...
  • sql语句练习 及 答案

    千次下载 热门讨论 2011-08-31 09:06:25
    一些对初学者非常有用的练习,及练习的答案。希望可以给初学者一些帮助。
  • 【SICP练习】95 练习2.68

    千次阅读 2015-03-05 10:49:50
    练习2.68先要导入练习2.67中的sample-tree。这道题要求我们写出能够根据给定的树产生出给定符号的二进制位表的函数encode-symbol,这个函数还要能够在遇到未在树中出现的符号时报错。这个函数将要在给定的树中查找...
  • 【SICP练习】145 练习4.1

    千次阅读 2015-03-29 16:27:24
    练习4-1原文Exercise 4.1. Notice that we cannot tell whether the metacircular evaluator evaluates operands from left to right or from right to left. Its evaluation order is inherited from the ...
  • 【SICP练习】129 练习3.60

    千次阅读 2015-03-28 22:52:40
    练习3-60原文Exercise 3.60. With power series represented as streams of coefficients as in exercise 3.59, adding series is implemented by add-streams. Complete the definition of the following procedure...
  • 【SICP练习】146 练习4.2

    千次阅读 2015-03-29 19:31:59
    练习4-2原文Exercise 4.2. Louis Reasoner plans to reorder the cond clauses in eval so that the clause for procedure applications appears before the clause for assignments. He argues that this will make...
  • 【SICP练习】117 练习3.44

    千次阅读 2015-03-26 19:59:00
    练习3-43原文Exercise 3.44. Consider the problem of transferring an amount from one account to another. Ben Bitdiddle claims that this can be accomplished with the following procedure, even if there ...
  • 【SICP练习】119 练习3.50

    千次阅读 2015-03-28 21:44:40
    练习3-50原文Exercise 3.50. Complete the following definition, which generalizes stream-map to allow procedures that take multiple arguments, analogous to map in section 2.2.3, footnote 12. (define ...
  • 【SICP练习】130 练习3.61

    千次阅读 2015-03-28 22:58:34
    练习3-61原文 代码(define (reciprocal-series s) (cons-stream 1 (scale-stream (mul-series (stream-cdr s) (reciprocal-series s))
  • 【SICP练习】113 练习3.33

    千次阅读 2015-03-26 12:50:21
    练习3-33原文Exercise 3.33. Using primitive multiplier, adder, and constant constraints, define a procedure averager that takes three connectors a, b, and c as inputs and establishes the constraint ...
  • python项目练习

    千次阅读 2018-06-06 14:33:48
    练习一:即时标记 python项目 练习二:画幅好画 python项目 练习三:万能的XML python项目 练习四:新闻聚合 python项目 练习五:虚拟茶话会 python项目 练习六:使用CGI进行远程编辑 python项目 练习七:...
  • 【SICP练习】143 练习3.81

    千次阅读 2015-03-29 15:31:56
    练习3-81原文“random” numbers. Produce a stream formulation of this same generator that operates on an input stream of requests to generate a new random number or to reset the sequence to a specified...
  • 【SICP练习】150 练习4.6

    千次阅读 2015-04-01 08:52:20
    练习4-6原文Exercise 4.6. Let expressions are derived expressions, because (let (( ) … ( )) ) is equivalent to ((lambda ( … ) ) ) Implement a syntactic transformation let->combination that ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 428,569
精华内容 171,427
关键字:

练习