数据库(Mysql)----mysql查询练习(1、准备)

﹏ヽ暗。殇╰゛Y 2021-09-23 20:42 434阅读 0赞

# 1、建库 #

新建一个查询用的数据库:selectTest。 `create database selectTest;`  
选择该数据库: `use selectTest;`

# 2、建表 #

学生表：
    	student
    字段：
    	学号           s_no 
    	姓名           s_name 
    	性别           s_sex 
    	出生日期        s_birthday 
    	所在班级        s_class

CREATE TABLE student(
        s_no VARCHAR(20) PRIMARY KEY COMMENT'学生学号',
        s_name VARCHAR(20) NOT NULL COMMENT'学生姓名 不能为空',
        VARCHAR(10) NOT NULL COMMENT'学生性别',
        s_birthday DATETIME COMMENT'学生生日',
        s_class VARCHAR(20) COMMENT'学生所在的班级'
    );

教师表：
    	teacher
    字段：
    	教师编号     t_no 
    	教师名字     t_name 
    	教师性别     t_sex 
    	出生日期     t_birthday 
    	职称         t_rof 
    	所在部门     t_depart

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'教师所在的部门'
    );

课程表:
    	course
    字段：
    	课程号        c_no 
    	课程课程名称   c_name 	
    	教师编号       t_no

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)
    );

成绩表
    	srore
    字段：
    	学号                 s_no 
    	课程号                c_no 
    	成绩                 sc_degree

CREATE TABLE score (
        s_no VARCHAR(20) NOT NULL COMMENT'成绩表的编号 依赖学生学号',
            c_no VARCHAR(20)  NOT NULL COMMENT'课程号 依赖于课程表中的c_id',
        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)
    );

# 3、查看各表的结构 #

**查看所出创建的表`show tables;`**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70]  
**查看student表结构 `DESCRIBE student;`**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 1]  
**查看teacher表结构 `DESCRIBE teacher;`**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 2]  
**查看course表结构 `DESCRIBE course;`**  
![在这里插入图片描述][20200130170117115.png]  
**查看score表结构 `DESCRIBE score;`**  
![在这里插入图片描述][20200130170203872.png]

# 4、向表中添加数据 #

**–学生表数据**

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('104','李军','男','1976-02-20','95033');
    INSERT INTO student VALUES('105','王芳','女','1975-02-10','95031');
    INSERT INTO student VALUES('106','陆军','男','1974-06-03','95031');
    INSERT INTO student VALUES('107','王尼玛','男','1976-02-20','95033');
    INSERT INTO student VALUES('108','张全蛋','男','1975-02-10','95031');
    INSERT INTO student VALUES('109','赵铁柱','男','1974-06-03','95031');

**–教师表数据**

INSERT INTO teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系');
    INSERT INTO teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
    INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系');
    INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系')

**–添加课程表**

INSERT INTO course VALUES('3-105','计算机导论','825');
    INSERT INTO course VALUES('3-245','操作系统','804');
    INSERT INTO course VALUES('6-166','数字电路','856');
    INSERT INTO course VALUES('9-888','高等数学','831');

**–添加成绩表**

INSERT INTO score VALUES('103','3-245','86');
    INSERT INTO score VALUES('105','3-245','75');
    INSERT INTO score VALUES('109','3-245','68');
    INSERT INTO score VALUES('103','3-105','92');
    INSERT INTO score VALUES('105','3-105','88');
    INSERT INTO score VALUES('109','3-105','76');
    INSERT INTO score VALUES('103','6-166','85');
    INSERT INTO score VALUES('105','6-166','79');
    INSERT INTO score VALUES('109','6-166','81');

# 5、几张表的数据展现 #

**student表**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 3]  
**teacher表**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 4]  
**score表**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 5]  
**course表**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 6]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70]: /images/20210923/92a233a891da41b284e977220a0f0fc7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 1]: /images/20210923/b8653bf0471d42a0845208a9a296ad8c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 2]: /images/20210923/43a45461d26e4cd9a823cf31ceb0ac7d.png
[20200130170117115.png]: /images/20210923/2f232875cb58405194ae6cd14df29e36.png
[20200130170203872.png]: /images/20210923/15de81809a54473cbeb07dea73eac11d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 3]: /images/20210923/8655a836504d47b8b61a03c2b79c8f46.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 4]: /images/20210923/ebf414c0b2a5428dad6b66608fd8a728.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 5]: /images/20210923/361378b8b9e7407ca9e7f0b750b45f11.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 6]: /images/20210923/240ef807d01c49729b2713c1bbc2a588.png