重点：MySQL 查询语句练习总汇

最新推荐文章于 2024-07-07 13:53:42 发布

mu城

最新推荐文章于 2024-07-07 13:53:42 发布

阅读量433

点赞数 1

分类专栏： MySQL学习笔记

本文链接：https://blog.csdn.net/weixin_45014586/article/details/105990744

版权

本文是一篇关于MySQL查询语句的练习总结，涵盖了单表查询、多表查询及各种条件筛选，包括使用select *与select指定字段的区别，以及如何进行多表连接操作。通过对学生表、教师表、课程表和成绩表的实例操作，详细解析了count(), max()等函数，以及子查询和聚合函数的应用。" 130336336,17795352,Java应用在Kubernetes中的内存管理挑战,"['Java', 'Kubernetes', 'JVM内存管理']

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

标题 sql 语句select 练习笔记

1、建库

SelectTest

mysql> create database SelectTest;
Query OK, 1 row affected (0.60 sec)

mysql> use SelectTest;
Database changed

2、建表

新建以下表 :
学生表student、教师表teacher，课程表course、成绩表score

学生表

mysql> create table student(s_no varchar(20) primary key comment'学生学号',
    -> s_name varchar(20) not null comment'学生姓名 不能为空',
    -> s_sex varchar(10) not null comment'学生性别',
    -> s_birthday datetime comment'学生生日',
    -> s_class varchar(20) comment'学生所在班级');
Query OK, 0 rows affected (4.68 sec)

教师表

mysql> CREATE TABLE teacher(
    ->     t_no VARCHAR(20) PRIMARY KEY COMMENT'教师编号',
    ->     t_name VARCHAR(20) NOT NULL COMMENT'教师姓名',
    ->     t_sex VARCHAR(20) NOT NULL COMMENT'教师性别',
    ->     t_birthday DATETIME COMMENT'教师生日',
    ->     t_rof VARCHAR(20) NOT NULL COMMENT'教师职称',
    ->     t_depart VARCHAR(20) NOT NULL COMMENT'教师所在的部门'
    -> );
Query OK, 0 rows affected (1.26 sec)

课程表

mysql> CREATE TABLE course(
    ->     c_no VARCHAR(20) PRIMARY KEY COMMENT'课程号',
    ->     c_name VARCHAR(20) NOT NULL COMMENT'课程名称',
    ->     t_no VARCHAR(20) NOT NULL COMMENT'教师编号 外键关联teacher表',
    ->     FOREIGN KEY(t_no) references teacher(t_no)
    -> );
Query OK, 0 rows affected (0.85 sec)

成绩表

mysql> create table score(
    -> s_no varchar(20) not null comment'成绩表的编号 依赖于学生表学生编号',
    -> c_no varchar(20) not null comment'成绩表的编号 依赖于课程表的编号',
    -> sc_degree decimal,
    -> foreign key(s_no) references student(s_no),
    -> foreign key(c_no) references course(c_no),
    -> primary key(s_no,c_no));
Query OK, 0 rows affected (0.42 sec)

查看库里的表

mysql> show tables;
+----------------------+
| Tables_in_selecttest |
+----------------------+
| course               |
| score                |
| student              |
| teacher              |
+----------------------+
4 rows in set (0.74 sec)

各个表结构

mysql> desc course;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| c_no   | varchar(20) | NO   | PRI | NULL    |       |
| c_name | varchar(20) | NO   |     | NULL    |       |
| t_no   | varchar(20) | NO   | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.28 sec)

mysql> describe score;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| s_no      | varchar(20)   | NO   | PRI | NULL    |       |
| c_no      | varchar(20)   | NO   | PRI | NULL    |       |
| sc_degree | decimal(10,0) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
3 rows in set (0.07 sec)

mysql> describe student;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| s_no       | varchar(20) | NO   | PRI | NULL    |       |
| s_name     | varchar(20) | NO   |     | NULL    |       |
| s_sex      | varchar(10) | NO   |     | NULL    |       |
| s_birthday | datetime    | YES  |     | NULL    |       |
| s_class    | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.15 sec)

mysql> describe teacher;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| t_no       | varchar(20) | NO   | PRI | NULL    |       |
| t_name     | varchar(20) | NO   |     | NULL    |       |
| t_sex      | varchar(20) | NO   |     | NULL    |       |
| t_birthday | datetime    | YES  |     | NULL    |       |
| t_rof      | varchar(20) | NO   |     | NULL    |       |
| t_depart   | varchar(20) | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.03 sec)

向表中添加数据


--学生表数据
INSERT INTO student VALUES('101','曾华','男','1977-09-01','95033');
INSERT INTO student VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO student VALUES('103','王丽','女','1976-01-23','95033');
INSERT INTO student VALUES(