MySQL查询语句使用

2021/12/11 19:47:30

本文主要是介绍MySQL查询语句使用,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

一、在school数据库中建立四个表:

student/teacher/course/score

mysql> create table student(
    -> sno varchar(20) primary key,
    -> sname varchar(20)not null,
    -> ssex varchar(10)not null,
    -> sbirthday datetime,
    -> class varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table teacher(
    -> tno varchar(20) primary key,
    -> tname varchar(20) not null,
    -> tsex varchar(20) not null,
    -> tbirthday datetime,
    -> prof varchar(20) not null,
    -> depart varchar(20) not null
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table course(
    -> cno varchar(20) primary key,
    -> cname varchar(20) not null,
    -> tno varchar(20) not null,
    -> foreign key(tno) references teacher(tno)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table score(
    -> sno varchar(20) not null,
    -> cno varchar(20) not null,
    -> degree decimal,
    -> foreign key(sno) references student(sno),
    -> foreign key(cno) references course(cno),
    -> primary key(sno,cno)
    -> );
Query OK, 0 rows affected (0.02 sec)

 

二、插入数据

insert into 表名 values();

 

三、查询数据

1.查询student表中所有记录sname,ssex,class列:

select sname,ssex,class from student;

2.查询教师所有单位中不重复的depart列:(distinct排除重复)

select distinct depart from teacher;

3.查询score表中成绩在60~80之间的所有记录:(查询区间between..and../直接使用运算符比较)

select *from score where degree between 60 and 80;

或select *from score where degree>60 and degree<80;

4.查询score表中成绩为85,86或88的记录:(表示或者关系的查询 in)

select *from score where degree in(85,86,88);

5.查询student表中“95031”班或性别为女的同学记录:

select *from student where class='95031' or ssex='女';

6.以class降序查询student表的所有记录:(升序asc/降序desc)

select *from student order by class desc;

默认是升序所以一般不会写

7.以cno升序、degree降序查询score表的所有记录:

select *from score order by cno asc,degree desc;

8.查询“95031”班的学生人数:(统计count)

select count(*) from student where class='95031';

9.查询score表中的最高分的学生学号和课程号:(子查询或者排序)

select sno,cno from score where degree=(select max(degree) from score);

 



这篇关于MySQL查询语句使用的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程