MySQL常用常用SQL语句总结包含复杂语句总结包含复杂SQL查询查询
今天小编就为大家分享一篇关于MySQL常用SQL语句总结包含复杂SQL查询,小编觉得内容挺不错的,现在分
享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧
1、复杂、复杂SQL查询查询
1.1、单表查询、单表查询
(1)选择指定的列
[例]查询全体学生的学号和姓名
select Sno as 学号,Sname as 姓名 from student;
select Sno,Sname from student;
(2)查询全部列
[例]查询全体学生的详细信息
select * from student;
(3)对查询后的指定列进行命名
[例]查询全部学生的“姓名”及其“出生年”两列
select Sname as 姓名,(2014-Sage) as 出生年 from student;
select Sname ,(2014-Sage) from student;
(4)消除取值重复的行
[例]查询选修了课程的学生学号
select distinct Sno as 选修了课程的学生学号 from SC;
select distinct Sno from SC;
(5)选择表中若干元组(满足条件的)
1.2、大小比较、大小比较
[例]查询计算机系(IS)全体学生名单
select Sname as 学生姓名 from student where Sdept='IS';
[例]查询全体20岁以下的学生姓名和年龄
select Sname as 姓名,Sage as 年龄 from student where Sage<20;
1.3、确定范围、确定范围
[例]查询所有在20到23岁(含20和23)的学生姓名、系别和年龄
select Sname as 姓名,Sdept as 系别,Sage as 年龄 from student where Sage between20 and 23;
注意between 小数 and 大数。
1.4、、in和和not in确定集合确定集合
[例]查询IS系和CS系的全体学生姓名和性别
select Sname as 姓名,Ssex as 性别 from student where Sdept='IS' or Sdept='CS';
select Sname as 姓名,Ssex as 性别 from student where Sdept in ('IS','CS');
[例]查询既不属于IS系,也不属于MA系的学生姓名和年龄
select Sname as 姓名,Sage as 年龄 from student where Sdept !='IS'and Sdept!='CS';
select Sname as 姓名,Sage as 年龄 from student where Sdept not in('IS','MA');
1.5、字符匹配(、字符匹配(like % _ ))
[例]查询所有姓李的学生姓名和性别
select Sname as 姓名,Ssex as 性别 from student where Sname like '李%';