https://www.jianshu.com/p/dffd40d06e3c?u_atoken=347d4392-252a-4b45-a678-bcec250f9e04&u_asession=01eMtP4JqegFoAlsURoakpOGrbcerjLJ_zN8f1aUZcmXP4RFYHr8mgHddXP2E4de3jX0KNBwm7Lovlpxjd_P_q4JsKWYrT3W_NKPr8w6oU7K_rPtya4yy2JhGPHuukmz9-nHmbkqVcEgdObpAroqY1_GBkFo3NEHBv0PZUm6pbxQU&u_asig=05C37JabxAuQ31mDFqjIByfyLzE6L1QI2GClS_4S2oH_CRohkzGM9lmVwfVnmUENav6UAlwIGd0n5-OXsYaCKG3INIU3CDihJTJ51P3S6kkIKOa1ngTAW4FxeLWO19IXEBBGQDh9HMF8GBnsGLJ80uwzZlCYPl5VPi0r_5fKCzBJb9JS7q8ZD7Xtz2Ly-b0kmuyAKRFSVJkkdwVUnyHAIJzZmc6KEtJ8Ac7iz7KRn3KyaF2ULn3BedZgqLkWUaPI706xbSxAaWh9ph0bRUFW-6vO3h9VXwMyh6PgyDIVSG1W_zuO1CFQeRvItTQZoN37h469EgyhIIU9G134I4HWFmMM2zxaRDUMsexOWGXS4DANDdkV_2vuXIqfq23xGu1PxmmWspDxyAEEo4kbsryBKb9Q&u_aref=XH4TVtlUH3sT%2FSBszLSI6AAx8uI%3D（1）创建一个数据库 sql_exercise01
（2）创建4个数据表：
①学生表（Student）
②课程表（Course）
③成绩表（Score）
④教师信息表（Teacher）
设计如下针对四张表完成如下信息：
表结构：
#学生表
mysql> desc student;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Sno | varchar(20) | NO | PRI | NULL | |
| Sname | varchar(20) | NO | | NULL | |
| Ssex | varchar(20) | NO | | NULL | |
| Sbirthday | datetime | YES | | NULL | |
| Class | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#教师表
mysql> desc teacher;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Tno | varchar(20) | NO | PRI | NULL | |
| Tname | varchar(20) | NO | | NULL | |
| Tsex | varchar(20) | NO | | NULL | |
| Tbirthday | datetime | YES | | NULL | |
| Prof | varchar(20) | YES | | NULL | |
| Depart | varchar(20) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> #课程表
mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Cno | varchar(20) | NO | PRI | NULL | |
| Cname | varchar(20) | NO | | NULL | |
| Tno | varchar(20) | NO | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> #成绩表
mysql> desc score;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| Sno | varchar(20) | NO | MUL | NULL | |
| Cno | varchar(20) | NO | MUL | NULL | |
| Degree | decimal(10,0) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)表数据
mysql> #学生表
mysql> select *from student;
+-----+--------+------+---------------------+-------+
| Sno | Sname | Ssex | Sbirthday | Class |
+-----+--------+------+---------------------+-------+
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
6 rows in set (0.00 sec)
mysql> #课程表
mysql> select *from course;
+-------+-----------------+-----+
| Cno | Cname | Tno |
+-------+-----------------+-----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
+-------+-----------------+-----+
4 rows in set (0.00 sec)
mysql> #成绩表
mysql> select *from score;
+-----+-------+--------+
| Sno | Cno | Degree |
+-----+-------+--------+
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
| 103 | 3-105 | 64 |
| 105 | 3-105 | 91 |
| 109 | 3-105 | 78 |
| 103 | 6-166 | 85 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
12 rows in set (0.00 sec)
mysql> #教师表
mysql> select *from teacher;
+-----+--------+------+---------------------+-----------+-----------------+
| Tno | Tname | Tsex | Tbirthday | Prof | Depart |
+-----+--------+------+---------------------+-----------+-----------------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
+-----+--------+------+---------------------+-----------+-----------------+
4 rows in set (0.00 sec)根据上述要求完成MySQL语句书写
#创建数据库sql_exercise01
CREATE DATABASE sql_exercise01;
#使用数据库sql_exercise02
USE sql_exercise01;
#学生表
CREATE TABLE student(
`Sno` VARCHAR(20) NOT NULL COMMENT '学生学号',
`Sname` VARCHAR(20) NOT NULL COMMENT '学生姓名',
`Ssex` VARCHAR(20) NOT NULL COMMENT '学生性别',
`Sbirthday` DATETIME COMMENT '出生日期',
`Class` VARCHAR(20) COMMENT '班级编号',
PRIMARY KEY(`Sno`)
)
#教师表
CREATE TABLE teacher(
`Tno` VARCHAR(20) NOT NULL COMMENT '老师编号',
`Tname` VARCHAR(20) NOT NULL COMMENT '老师姓名',
`Tsex` VARCHAR(20) NOT NULL COMMENT '老师性别',
`Tbirthday` DATETIME COMMENT '出生日期',
`Prof` VARCHAR(20) COMMENT '教师职称',
`Depart` VARCHAR(20) NOT NULL COMMENT '成绩',
PRIMARY KEY(`Tno`)
)
#课程表
CREATE TABLE course(
`Cno` VARCHAR(20) NOT NULL COMMENT '选修编号',
`Cname` VARCHAR(20) NOT NULL COMMENT '课程名称',
`Tno` VARCHAR(20) NOT NULL COMMENT '老师编号',
PRIMARY KEY(`Cno`)
)
#将其中Tno字段里的key设为MUL
ALTER TABLE course ADD INDEX(Tno);
#成绩表
CREATE TABLE score(
`Sno` VARCHAR(20) NOT NULL COMMENT '学生学号',
`Cno` VARCHAR(20) NOT NULL COMMENT '选修编号',
`Degree` DECIMAL(10,0) COMMENT '成绩'
)
#将其中Sno,Cno字段里的key设为MUL
ALTER TABLE score ADD INDEX(Sno);
ALTER TABLE score ADD INDEX(Cno);
#向学生student表添加数据
#使用insert语句向user表格中添加数据
#语法：insert into 表名( 字段1, 字段2, 字段3, … )values('值1', '值2', '值3',…);
INSERT INTO student(sno,sname,ssex,sbirthday,class)VALUES(101,'李军','男','1976-02-20','95033');
INSERT INTO student VALUES(103,'陆君','男','1974-06-03','95031');
INSERT INTO student VALUES(105,'匡明','男','1975-10-02','95031');
INSERT INTO student VALUES(107,'王丽','女','1976-01-23','95033');
INSERT INTO student VALUES(108,'曾华','男','1977-09-01','95033');
INSERT INTO student VALUES(109,'王芳','女','1975-02-20','95031');
#向教师信息teacher表添加数据
INSERT INTO teacher
VALUES
(804,'李诚','男','1958-12-02','副教授','计算机系'),
(856,'张旭','男','1969-03-12','讲师','电子工程系'),
(825,'王萍','女','1972-05-05','助教','计算机系'),
(831,'刘冰','女','1977-08-14','助教','电子工程系');
#向课程course表添加数据
INSERT INTO course
VALUES
('3-105','计算机导论',825),
('3-245','操作系统',804),
('6-166','数字电路',856),
('9-888','高等数学',831);
#向成绩score表添加数据
INSERT INTO score
VALUES
(103,'3-245',86),
(105,'3-245',75),
(109,'3-245',68),
(103,'3-105',92),
(105,'3-105',88),
(109,'3-105',76),
(101,'3-105',64),
(107,'3-105',91),
(108,'3-105',78),
(101,'6-166',85),
(107,'6-166',79),
(108,'6-166',81);
1.查询Student表中的所有记录的Sname , Ssex 和 Class列
SELECT Sname,Ssex,Class FROM Student;
-->最终结果
mysql> SELECT Sname,Ssex,Class FROM Student;
+-------+------+-------+
| Sname | Ssex | Class |
+-------+------+-------+
| 李军 | 男 | 95033 |
| 陆君 | 男 | 95031 |
| 匡明 | 男 | 95031 |
| 王丽 | 女 | 95033 |
| 曾华 | 男 | 95033 |
| 王芳 | 女 | 95031 |
+-------+------+-------+2.查询教师所有的单位即不重复的Depart列
SELECT DISTINCT Depart FROM teacher;
-->最终结果：
mysql> SELECT DISTINCT Depart FROM teacher;
+------------+
| Depart |
+------------+
| 计算机系 |
| 电子工程系 |
+------------+3.查询Student表的所有记录
SELECT * FROM Student;
-->最终结果：
mysql> SELECT * FROM Student;
+-----+-------+------+---------------------+-------+
| Sno | Sname | Ssex | Sbirthday | Class |
+-----+-------+------+---------------------+-------+
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 109 | 王芳 | 女 | 1975-02-20 00:00:00 | 95031 |
+-----+-------+------+---------------------+-------+