【MySQL】SQL语句进阶-创建sql练习环境

今天药忘吃喽~ 2022-12-24 12:54 183阅读 0赞

开始之前先来学习一个数据库查询命令

#### select ####

##### 1）打印当前日期和时间 #####

mysql> select now();
    +---------------------+
    | now()               |
    +---------------------+
    | 2020-11-28 12:34:56 |
    +---------------------+
    1 row in set
    
    mysql>

##### 2） 打印当前日期 #####

mysql> select curdate();
    +------------+
    | curdate()  |
    +------------+
    | 2020-11-28 |
    +------------+
    1 row in set
    
    mysql>

##### 3. 打印当前数据库 #####

mysql> select database();
    +------------+
    | database() |
    +------------+
    | zmedu      |
    +------------+
    1 row in set
    
    mysql>

##### 4. 打印MySQL版本 #####

mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 8.0.22    |
    +-----------+
    1 row in set

##### 5. 打印当前用户 #####

mysql> select user();
    +----------------+
    | user()         |
    +----------------+
    | root@localhost |
    +----------------+
    1 row in set

##### 6. 查看系统运行状态的信息 #####

#### 搭建练习环境 ####

说明：  
–1.学生表  
Student(s\_id,s\_name,s\_birth,s\_sex) –学生编号,学生姓名, 出生年月,学生性别  
–2.课程表  
Course(c\_id,c\_name,t\_id) – –课程编号, 课程名称, 教师编号  
–3.教师表  
Teacher(t\_id,t\_name) –教师编号,教师姓名  
–4.成绩表  
Score(s\_id,c\_id,s\_score) –学生编号,课程编号,分数

**建立学生表：**

mysql> CREATE TABLE `Student`(
    `s_id` VARCHAR(20),
    `s_name` VARCHAR(20) NOT NULL DEFAULT '',
    `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
    `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY(`s_id`)
    );
    Query OK, 0 rows affected

**建立课程表**

mysql> CREATE TABLE `Course`(
    `c_id` VARCHAR(20),
    `c_name` VARCHAR(20) NOT NULL DEFAULT '',
    `t_id` VARCHAR(20) NOT NULL,
    PRIMARY KEY(`c_id`)
    );
    Query OK, 0 rows affected
    
    mysql>

**建立教师表**

mysql> CREATE TABLE `Teacher`(
    `t_id` VARCHAR(20),
    `t_name` VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(`t_id`)
    );
    Query OK, 0 rows affected

**建立成绩表**

CREATE TABLE `Score`(
    `s_id` VARCHAR(20),
    `c_id` VARCHAR(20),
    `s_score` INT(3),
    PRIMARY KEY(`s_id`,`c_id`)
    );

插入学生表测试数据：

insert into Student values('01' , '高胜寒' , '1990-06-01' , '男');
    insert into Student values('02' , '高峰' , '1990-2-21' , '男');
    insert into Student values('03' , '维度' , '1990-07-20' , '男');
    insert into Student values('04' , '李云龙' , '1995-08-06' , '男');
    insert into Student values('05' , '沐晨' , '1991-12-01' , '女');
    insert into Student values('06' , '舒展' , '1992-03-01' , '女');
    insert into Student values('07' , '李白' , '1989-07-01' , '女');
    insert into Student values('08' , '白居易' , '1990-01-20' , '女');

插入课程表测试数据：

insert into Course values('01' , '语文' , '02');
    insert into Course values('02' , '数学' , '01');
    insert into Course values('03' , '英语' , '03');
    insert into Course values('04' , '化学' , '04');

插入教师表数据

insert into Teacher values('01' , '张帅');
    insert into Teacher values('02' , '李广');
    insert into Teacher values('03' , '王浩');
    insert into Teacher values('04' , '李强');

成绩表测试数据

insert into Score values('01' , '01' , 80);
    insert into Score values('01' , '02' , 90);
    insert into Score values('01' , '03' , 99);
    insert into Score values('02' , '01' , 70);
    insert into Score values('02' , '02' , 60);
    insert into Score values('02' , '03' , 80);
    insert into Score values('03' , '01' , 80);
    insert into Score values('03' , '02' , 80);
    insert into Score values('03' , '03' , 80);
    insert into Score values('04' , '01' , 50);
    insert into Score values('04' , '02' , 30);
    insert into Score values('04' , '03' , 20);
    insert into Score values('05' , '01' , 76);
    insert into Score values('05' , '02' , 87);
    insert into Score values('06' , '01' , 31);
    insert into Score values('06' , '03' , 34);
    insert into Score values('07' , '02' , 89);
    insert into Score values('07' , '03' , 98);

课程表：  
![在这里插入图片描述][20201128133512576.png]  
分数表：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbnNodXpoYW4_size_16_color_FFFFFF_t_70]  
学生表

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbnNodXpoYW4_size_16_color_FFFFFF_t_70 1]  
老师表

![在这里插入图片描述][20201128133622388.png]

[20201128133512576.png]: /images/20221120/89652889fb744db98755c565475ec308.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbnNodXpoYW4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201128133541144.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbnNodXpoYW4=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbnNodXpoYW4_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201128133600378.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbnNodXpoYW4=,size_16,color_FFFFFF,t_70
[20201128133622388.png]: https://img-blog.csdnimg.cn/20201128133622388.png