数据库day03

最新推荐文章于 2023-11-07 15:10:09 发布

Eshel_

最新推荐文章于 2023-11-07 15:10:09 发布

阅读量5k

点赞数 3

分类专栏：数据库 MySQL 文章标签：数据库 database

本文链接：https://blog.csdn.net/Eshel_/article/details/124973765

版权

这篇博客详细介绍了多种SQL查询技巧，包括投影、筛选、别名、模糊查询、空值处理、去重、排序、聚合函数、分组、子查询、连接查询、分页查询等，覆盖了从基本操作到复杂查询的多个方面。

摘要由CSDN通过智能技术生成

1. 查询所有学生的所有信息

select * from tb_student;

select stu_id, 
	   stu_name, 
       stu_sex, 
       stu_birth, 
       stu_addr, 
       col_id 
  from tb_student;

2. 查询学生的学号、姓名和籍贯(投影)

select stu_id, 
	   stu_name, 
       stu_addr 
  from tb_student;

3. 查询所有课程的名称及学分(投影和别名)

select cou_name, 
	   cou_credit 
  from tb_course;

select cou_name as 课程名称, 
	   cou_credit as 学分
  from tb_course;

4. 查询所有女学生的姓名和出生日期(筛选)

select stu_name, 
	   stu_birth 
  from tb_student 
 where stu_sex=0;

5. 查询籍贯为“四川成都”的女学生的姓名和出生日期(筛选)

select stu_name, 
	   stu_birth 
  from tb_student 
 where stu_sex=0
   and stu_addr='四川成都';

6. 查询籍贯为“四川成都”或者性别是女的学生

select stu_name,
	   stu_birth
  from tb_student 
 where stu_sex=0
    or stu_addr='四川成都';

7. 查询所有80后学生的姓名、性别和出生日期(筛选)

select stu_name, 
	   stu_sex, 
       stu_birth 
  from tb_student 
 where stu_birth >= '1980-1-1' 
   and stu_birth <= '1989-12-31';

select stu_name, 
	   stu_sex, 
       stu_birth 
  from tb_student 
 where stu_birth between '1980-1-1' and '1989-12-31';

8. 查询学分大于2的课程的名称和学分(筛选)

select cou_name, 
	   cou_credit 
  from tb_course 
 where cou_credit > 2;

9. 查询学分是奇数的课程的名称和学分(筛选)

select cou_name, 
	   cou_credit 
  from tb_course 
 where cou_credit mod 2=1;

select cou_name, 
	   cou_credit 
  from tb_course 
 where cou_credit % 2 <> 0;

10. 查询选择选了1111的课程考试成绩在90分以上的学生学号(筛选)

select stu_id 
  from tb_record 
 where cou_id = 1111 
   and score > 90;

11. 查询名字叫“杨过”的学生的姓名和性别

select stu_name, 
	   stu_sex 
  from tb_student 
 where stu_name='杨过';

12. 查询姓“杨”的学生姓名和性别(模糊)

wild card - 通配符 % - 匹配0个或任意多个字符。

select stu_name,
	   stu_sex 
  from tb_student 
 where stu_name regexp '杨.';

select stu_name, 
	   stu_sex 
  from tb_student 
 where stu_name like '杨%';