第四章 SQL语言(中)
lix_uan 183 3

1

查询所有&查询部分列

select * from Student;

select Sno from SC;

查询给列起别名

select username as 姓名 from user;

select * from user as u;

结果去重

select distinct title from edu_course;

查询结果加条件

2

select * from edu_course where price>2;

select * form edu_course where price between 21 and 50;

select * from edu_course where teacher_id in (1,2,3);

select * from edu_course where title like '%学习';

select * from edu_course where title like '_学习';

ps: %可代替多个字符,_只能代替一个字符

select * from edu_course where (price>=2 abd teacher_id=1) or (title='mysql学习');

select * from edu_course order by lesson_num desc;

ps:desc 的两种用法:
        1.查看表结构信息
        2.降序排序 (asc 升序排序)

聚集函数的使用

select count(title) from edu_course;

select count(distinct title) from end_course;

select avg(price) from edu_course;

select max(price) from edu_course;

select min(price) from edu_course;

分组查询

select title from edu_course group by title;

select title from edu_course group by title having title='sql';

连接

  • 等值&非等值连接:以where为关键词
    select t.*,c.* from edu_teacher t,edu_course c where t.id=c.teacher_id;
  • 自身连接:以本身这两个表起两个别名,然后进行操作
    select c1.version,c2.view_count from enu_course c1,edu_course c2 where c1.version=c2.view_count;
  • 外连接:把被舍弃的值,也保留在结果中,但是要加上NULL
    select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student left outer join SC on (Student.Sno=SC.Sno);
    3

多表查询

select c.id ID,c.title 课程名,c.title 课程描述 from edu_course c,edu_chapter cp,edu_course_description cd where c.id=cp.course_id and c.id=cd.id;

嵌套查询

select c.title 课程名,c.price 价格 from edu_course c where c.title in (select c2.title from edu_course c2 where c2.title='sql学习');

带有 any all 的子查询

4

select c.title 课程名,c.price 价格 from edu_course c where c.price>all | (select c2.price from edu_course c2 where c2.price between 1 and 20);

带exists的子查询

exits     如果后面的子查询没有值,返回0,否则返回1
not exits 相反

没有一门课是他不选修的:
select Sname from Student where not exits(select * from Course where not exits (select * from SC where Sno=Student.Sno and Cno=Course.Cno));

ps:最里面的子查询是 这个人没选一门课程
   最外面的子查询是 没有这样的课程

集合查询

  • select * from Student where Sdept='CS' union select * from Student where Sage<=19;
    • select * from Student where Sdept='CS' intersect select * from Student where Sage<=19;
    • select * from Student where Sdept='CS' except select * from Student where Sage<=19;
预览图
评论区

索引目录