10分钟学会MySQL,其实只是个标题。10分钟肯定不能让大家从小白变成高手,但入门足够了。
因为最近公司招聘遇到了一个做了4年PHP开发,薪资要求1.2k/月。本身是没打算面试mysql这些题目的,毕竟太基础,不合适。所以先问了下缓存架构算法之类的。结果一问三不知。所以最后问题就变成了两个。一个是用PHP给[1,3,5,2,4,6,10,9,8]排个顺序,从小到大或从大到小均可。另外一道题就是有两个数据库表,需要查出picture字段不为空时的数据。
面试结果真是让人很无语。所以今天分享一篇博客。把MySQL常用的查询语句为大家列一下,基础不好或刚刚入门的朋友可以收藏下。
- #### SELECT 常用语句####
- #查询年龄在18-19的学生
- #select * from student where Sage between 18 and 19;
- #查询年龄不在18-19的学生
- #select * from student where Sage not between 18 and 19;
- #查询计算机科学系(CS系),数学系(MA系)和信息系(IS)学生
- #select * from student where Sdept in('CS','MA','IS');
- #查询既不是计算机系(IS系),也不是信息系(MA系)和信息系(IS系)学生
- #select * from student where Sdept not in('CS','MA','IS');
- #查询姓刘的学生
- #select * from student where Sname like '刘%';
- #查询姓欧阳,且全名为三个汉字的学生
- #select *from student where Sname like '欧阳_';
- #查询名字中第二个字为晨的学生
- #select * from student where Sname like '_晨';
- #查询不姓刘的学生
- #select * from student where Sname not like '刘%';
- #查询缺少成绩的学生的学号和相对的课程号
- #select sno,cno from sc where grade is null;
- #查询所有有成绩的学生的学号和课程号
- #select sno,cno from sc where grade is not null;
- #查询选修了3号课程的学生的学号,姓名和分数,按成绩降序排序
- #select sc.sno,student.sname,sc.grade from student,sc where cno=3 and student.sno=sc.sno order by grade desc;
- #查询全体学生,按所在系升序排序,同一个系按年龄降序
- #select * from student order by sdept asc,sage desc ;
- #查询学生总人数
- #select count(*) from student;
- #查询选修了课程的学生总人数
- #select count(distinct sno) from sc ;
- #计算3号课程的平均分
- #select avg(grade) from sc where cno=2;
- #查询学号201215121 选修课程的总分数
- #select sum(grade) from sc where sno='201215121';
- #查询每个学生及其选修课的情况
- #select * from student, sc where student.sno = sc.sno;
- #查询选修了2号课程且成绩在90分以上的所有学生
- #select * from sc where cno=2 and grade>90;
- #左外连接
- #select student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from student left outer join sc on (student.sno = sc.sno);
- #多表连接
- #select student.sno,sname,cname,grade from student, sc, course where student.sno = sc.sno and course.cno = sc.cno
- #嵌套查询-查询选修了2号课程的学习姓名
- #select sname from student where sno in (select sno from sc where cno='2');
- #查询与刘晨在同一系学习的学生
- #select * from student where sdept = (select sdept from student where sname = '刘晨');
- #select * from student where sdept in (select sdept from student where sname = '刘晨');
- #select * from student s1,student s2 where s1.sdept = s2.sdept and s1.sname = '刘晨';
- #查询选修了信息系统的学生
- #select * from student,sc,course where sc.sno = student.sno and sc.cno = course.cno and course.cname = '信息系统'; ;
- #select * from student where sno in (select sno from sc where cno = ( select cno from course where cname = '信息系统' ));
- #找出每个学生超过其选修平均成绩的课程号
- #select * from sc where grade > (select avg(grade) from sc where sno = sc.Sno);
- #查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生
- #select * from student where sage < any (select sage from student) and sdept != 'cs';
- #select * from student where sage < (select max(sage) from student) and sdept != 'cs';
- #查询非计算机系中比计算机系所有年龄都要小的学生
- #select * from student where sage < all (select sage from student ) and sdept != 'cs';
- #select * from student where sage < (select min(sage) from student);
- #查询选修了2号课程的学生
- #select * from student where exists (select * from sc where cno = 2 and sc.sno = student.sno);
- #select * from student where sno in (select sno from sc where cno = 2);
- #查询没有选修2号课程的学生
- #select * from student where not exists (select * from sc where cno = 2 and sc.sno = student.sno);
- #select * from student where student.sno not in (select sc.sno from sc where sc.sno = 2);
- #查询选修了全部课程的学生
- -- select * from student where not exists
- -- (
- -- select * from course where not exists
- -- (
- -- select * from sc where sc.sno = student.sno and cno = course.cno
- -- )
- -- )
- -- select sno from
- -- (
- -- select sno,count(*) as sum from sc group by sno having sum =
- -- (
- -- select count(*) from course
- -- )
- -- ) as A
- # 集合查询 UNION (或,并集)
- #select * from student where Sname = '罗琪' UNION select * from student where Sname = '刘言曌';
- #select * from student where Sname = '罗琪' or Sname = '刘言曌';
- #找出每个学生超过自己选修课程平均成绩的课程号
- -- select * from sc,
- -- (
- -- select sno,avg(grade) as avg_grade from sc group by sno
- -- ) as A
- -- where A.sno = sc.sno and sc.grade > A.avg_grade;
- #### INSERT 常用语句 ####
- #将一个新学生插入到 Student 表中
- #insert into Student value(201215128, '曹志雄', '男', 20, 'MA');
- #insert into student(sno,sname,ssex,sage,sdept) values (201215207, '曹阿瞒','男',19, 'IS');
- #### UPDATE 常用语句 ####
- #修改某一个元组的值
- #将罗琪的年龄-1
- #update student set Sage = '18' where Sname = '罗琪'
- #修改多个元组的值
- #将所有的学生年龄-1
- #update Student set Sage = Sage - 1;
- #带子查询的修改语句
- #将计算机系的学生成绩+5
- -- update sc
- -- set grade = grade + 5
- -- where sno in
- -- (select sno from student where sdept = 'CS');
- #### DELETE 常用语句 ####
- #先添加一条学生记录,然后删除他
- #insert into student value(201215130,'张三','男',19,'IS');
- #delete from student where sno = '201215130';
- #删除多条记录
- ##delete from sc;
- #### 空值的处理 ####
- #空值产生
- #添加一个学生,学院为NULL
- #insert into student value(201215208, '春日风','女',18,NULL);
- #将曹志雄的学院设置为NULL
- #update student set sdept = NULL where sname = '曹志雄';
- #空值判断
- #查询没有先行课的课程
- #select * from course where cpno is null;
- #查询有先行课的课程
- #select * from course where cpno is not null
- #### 视图常用语句 ####
- #创建一个男生视图
- -- create view is_boy
- -- as
- -- select * from student where ssex = '男';
- #查询视图
- #select * from is_boy where sage < 19
- #更新视图
- #update is_boy set sage = sage +1 where sname = '刘中江'
- #删除视图
- #drop view is_boy;
以上的MySQL语句对于开发人员来讲基本上都是很常见的。同时如果想把基础打牢,最好还是建议大家把MySQL内置的一些函数、语法仔细看一遍。不说能倒背如流,最起码在遇到问题时知道搜什么。