MySQL 学习

第一次项目

安装 mysql 出现的问题:

① 未安装

解决办法:www.baidu.com

② 安装失败

多半是因为卸载 mysql 时没有卸载干净导致无法重新安装

解决办法:

通过 mysql installer 界面的 remove 进行卸载,之后再重新安装即可。

③ 未配置环境变量

出现该情况则需要配置环境变量。

默认的 mysql 安装路径为:C:\Program Files\MySQL\MySQL Server 5.6\bin,复制之后右键我的电脑(不能是快捷方式)属性,高级系统设置->环境变量。

将复制的 mysql 路径添加到 path 中(windows 系统下的用户变量和系统变量差别不是很大)

之后打开终端,在其中输入 mysql -uroot -p 之后输入密码得到如下界面便配置成功。

④ 忘记密码

解决办法一:

因为之前我们使用 ssm 项目连接过数据库,所以只需要打开上学期的 java 实训项目,找到/src/main/resources/db.properties,里面的 jdbc 就含有用户名和数据库密码。

如果上学期的 ssm 项目删除了,就看办法二。

办法二:

www.baidu.com

或者

https://blog.csdn.net/weidong_y/article/details/80493743

课后作业 employee 表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE TABLE `employee`(
`empno` BIGINT PRIMARY KEY AUTO_INCREMENT,
`ename` VARCHAR(20),
`job`VARCHAR(20),
`mgr`BIGINT,
`hiredate`DATE,
`sai`DOUBLE(10,2),
`COMM`DOUBLE(10,2),
`deptno`INT
);

INSERT INTO `employee` VALUES(1001,'甘宁','文员',1013,'2000-12-17',8000.00,NULL,20);
INSERT INTO `employee` VALUES(1002,'黛绮丝','销售员',1006,'2001-02-20',16000.00,3000.00,30);
INSERT INTO `employee` VALUES(1003,'殷天正','销售员',1006,'2001-02-20',12500.00,5000.00,30);
INSERT INTO `employee` VALUES(1004,'刘备','经理',1009,'2001-04-02',29750.00,NULL,20);
INSERT INTO `employee` VALUES(1005,'谢逊','销售员',1006,'2001-09-28',12500.00,14000.00,30);
INSERT INTO `employee` VALUES(1006,'关羽','经理',1009,'2001-05-01',28500.00,NULL,30);
INSERT INTO `employee` VALUES(1007,'张飞','经理',1004,'2007-04-19',30000.00,NULL,20);
INSERT INTO `employee` VALUES(1008,'诸葛亮','分析师',1004,'2007-04-19',30000.00,NULL,20);
INSERT INTO `employee` VALUES(1009,'曾阿牛','董事长',NULL,'2001-11-17',50000.00,NULL,10);
INSERT INTO `employee` VALUES(1010,'韦一笑','销售员',1006,'2001-09-08',15000.00,0.00,30);
INSERT INTO `employee` VALUES(1011,'周泰','文员',1008,'2007-05-23',11000.00,NULL,20);
INSERT INTO `employee` VALUES(1012,'程普','文员',1006,'2001-12-03',9500.00,NULL,30);
INSERT INTO `employee` VALUES(1013,'庞统','分析师',1004,'2001-12-03',30000.00,NULL,20);
INSERT INTO `employee` VALUES(1014,'黄盖','文员',1007,'2002-01-23',13000.00,NULL,10);
INSERT INTO `employee` VALUES(1015,'张三','保洁员',1001,'2013-05-01',80000.00,50000.00,50);

完成下列的查询题目:

  1. 查询出部门编号为 30 的所有员工
1
SELECT * FROM `employee` WHERE deptno = 30;
  1. 所有销售员的姓名、编号和部门编号。
1
SELECT empno,ename,deptno FROM `employee`;
  1. 找出奖金高于工资的员工。
1
SELECT * FROM `employee` WHERE COMM>sai;
  1. 找出奖金高于工资 60%的员工。
1
SELECT * FROM `employee` WHERE COMM>sai*0.6;
  1. 找出部门编号为 10 中所有经理,和部门编号为 20 中所有销售员的详细资料。
1
SELECT * FROM `employee` WHERE deptno=10 AND job='经理' OR deptno=20 AND job='销售员';
  1. 找出部门编号为 10 中所有经理,部门编号为 20 中所有销售员,还有即不是经理又不是销售员但其工资大或等于 20000 的所有员工详细资料。
1
SELECT * FROM `employee` WHERE deptno=10 AND job='经理' OR deptno=20 AND job='销售员' OR job !='经理' AND job != '销售员' AND sai >= 20000 ;
  1. 无奖金或奖金低于 1000 的员工。
1
SELECT * FROM `employee` WHERE COMM is NULL OR COMM < 1000;
  1. 查询名字由三个字组成的员工。
1
SELECT * FROM `employee` WHERE ename LIKE '___';
  1. 查询 2000 年入职的员工。
1
SELECT * FROM `employee` WHERE hiredate LIKE '2000%';
  1. 查询所有员工详细信息,用编号升序排序
1
SELECT * FROM `employee` ORDER BY empno ASC;
  1. 查询所有员工详细信息,用工资降序排序,如果工资相同使用入职日期升序排序
1
SELECT * FROM `employee` ORDER BY sai DESC, hiredate ASC;

第二次项目

课堂练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
create table student(
sno varchar(20) not null primary key,
sname varchar(20) not null,
ssex varchar(20) not null,
sbirthday datetime,
class varchar(20)
);
create table teacher
(
tno varchar(20) not null primary key,
tname varchar(20) not null,
tsex varchar(20) not null,
tbirthday datetime,
prof varchar(20),
depart varchar(20) not null
);
create table course
(
cno varchar(20) not null primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno)
);
create table score
(
sno varchar(20),
foreign key(sno) references student(sno),
cno varchar(20) not null,
foreign key(cno) references course(cno),
degree decimal
);
insert into student values('108','曾华','男','1977-09-01','95033');
insert into student values('105','匡明','男','1975-10-02','95031');
insert into student values('107','王丽','女','1976-01-23','95033');
insert into student values('101','李军','男','1976-02-20','95033');
insert into student values('109','王芳','女','1975-02-10','95031');
insert into student values('103','陆君','男','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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
-- 1、 查询 Student 表中的所有记录的 Sname、Ssex 和 Class 列。
SELECT Sname,Ssex,Class FROM `student`;
-- 2、 查询教师所有的单位即不重复的 Depart 列。
SELECT DISTINCT Depart FROM `teacher`;
-- 3、 查询 Student 表的所有记录。
SELECT * FROM `student`;
-- 4、 查询 Score 表中成绩在 60 到 80 之间的所有记录。
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
-- 5、 查询 Score 表中成绩为 85,86 或 88 的记录。
SELECT * FROM score WHERE degree=85 OR degree=86 OR degree=88;
-- 6、 查询 Student 表中“95031”班或性别为“女”的同学记录。
SELECT * FROM student WHERE class='95031' OR ssex='女';
-- 7、 以 Class 降序查询 Student 表的所有记录。
SELECT * FROM student ORDER BY class DESC;
-- 8、 以 Cno 升序、Degree 降序查询 Score 表的所有记录。
SELECT * FROM score ORDER BY cno ASC, degree DESC;
-- 9、 查询“95031”班的学生人数。
SELECT * FROM student WHERE class='95031';
-- 10、查询 Score 表中的最高分的学生学号和课程号。(子查询或者排序)
SELECT sno,cno,MAX(degree) FROM score;
-- 11、查询每门课的平均成绩。
SELECT cno,AVG(Degree) FROM Score GROUP BY cno;
-- 12、查询 Score 表中至少有 5 名学生选修的并以 3 开头的课程的平均分数。
SELECT AVG(degree) FROM score WHERE cno LIKE '3%' GROUP BY cno HAVING COUNT(sno)>4;
-- 13、查询分数大于 70,小于 90 的 Sno 列。
SELECT * FROM score WHERE degree BETWEEN 70 AND 90;
-- 14、查询所有学生的 Sname、Cno 和 Degree 列。
SELECT sname,cno,degree FROM student JOIN score ON student.sno=score.sno;
-- 15、查询所有学生的 Sno、Cname 和 Degree 列。
SELECT sno,cname,degree FROM score JOIN course ON score.cno=course.cno;
-- 16、查询所有学生的 Sname、Cname 和 Degree 列。
SELECT sname,cname,degree FROM student JOIN score ON student.sno=score.sno JOIN course ON course.cno=score.cno;
-- 17、查询“95033”班学生的平均分。
SELECT AVG(degree) FROM score WHERE sno IN (SELECT sno FROM student WHERE class='95033');
-- 18、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT * FROM student,score WHERE score.cno='3-105' AND student.sno=score.sno AND score.degree>(SELECT degree FROM score WHERE cno='3-105' AND sno='109');
-- 19、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SELECT * FROM student,score WHERE student.sno=score.sno AND score.degree>(SELECT degree FROM score WHERE cno='3-105' AND sno='109');
-- 20、查询和学号为 108、101 的同学同年出生的所有学生的 Sno、Sname 和 Sbirthday 列。
SELECT sno,sname,sbirthday FROM student WHERE YEAR(student.sbirthday) IN (SELECT YEAR(sbirthday)FROM student WHERE sno='108'OR sno='101');
-- 21、查询“张旭“教师任课的学生成绩。
SELECT sno,cno,degree FROM score WHERE cno IN (SELECT cno FROM course WHERE tno IN(SELECT tno FROM teacher WHERE tname='张旭'));
-- 22、查询选修某课程的同学人数多于 5 人的教师姓名。
SELECT tname FROM teacher WHERE tno IN (SELECT tno FROM course WHERE cno IN (SELECT cno FROM score GROUP BY cno HAVING COUNT(*)>5));
-- 23、查询 95033 班和 95031 班全体学生的记录。
SELECT * FROM student WHERE class='95033' OR class='95031';
-- 24、查询存在有 85 分以上成绩的课程 Cno。
SELECT DISTINCT cno FROM score WHERE degree>85;
-- 25、查询出“计算机系“教师所教课程的成绩表。
SELECT sno,cno,degree FROM score WHERE cno IN(SELECT cno FROM course WHERE tno IN (SELECT tno FROM teacher WHERE depart='计算机系'));
-- 26、查询“计算 机系”与“电子工程系“不同职称的教师的 Tname 和 Prof。
SELECT tname,prof FROM teacher WHERE depart='电子工程系' AND prof NOT IN(SELECT prof FROM teacher WHERE depart='计算机系');
-- 27、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的 Cno、Sno和 Degree,并按 Degree 从高到低次序排序。
SELECT cno,sno,degree FROM score AS one WHERE(SELECT degree FROM score as two WHERE cno='3-105' AND one.sno=two.sno)>(SELECT degree FROM score three WHERE cno='3-245' AND three.sno=one.sno);
-- 29、查询所有教师和同学的 name、sex 和 birthday。
SELECT DISTINCT tname,tsex,tbirthday FROM teacher;
-- 30、查询成绩比该课程平均成绩低的同学的成绩表。
SELECT sno,cno,degree FROM score A WHERE A.degree<(SELECT AVG(degree) FROM score B WHERE A.cno=B.cno);
-- 31、查询所有任课教师的 Tname 和 Depart。
SELECT tname,depart FROM teacher WHERE tname IN (SELECT DISTINCT tname FROM teacher, course, score WHERE teacher.tno=course.tno AND course.cno=score.cno);
-- 32、查询所有未讲课的教师的 Tname 和 Depart。
SELECT tname,depart FROM teacher WHERE tname NOT IN (SELECT DISTINCT tname FROM teacher,course,score WHERE teacher.tno=course.tno AND course.cno=score.cno);
-- 33、查询至少有 2 名男生的班号。
SELECT class FROM student WHERE ssex='男' GROUP BY class HAVING COUNT(*)>1;
-- 34、查询 Student 表中不姓“王”的同学记录。
SELECT * FROM student WHERE sname NOT LIKE('王%');
-- 35、查询 Student 表中每个学生的姓名和年龄。
SELECT sname,TIMESTAMPDIFF(YEAR,sbirthday,NOW()) AS '年龄' FROM student;
-- 36、查询 Student 表中最大和最小的 Sbirthday 日期值。
SELECT MAX(sbirthday), MIN(sbirthday) FROM student;
-- 37、以班号和年龄从大到小的顺序查询 Student 表中的全部记录。
SELECT * FROM student ORDER BY class DESC,sbirthday ASC;
-- 38、查询“男”教师及其所上的课程。
SELECT tname,cname FROM teacher,course WHERE tsex='男' AND teacher.tno=course.tno;
-- 39、查询最高分同学的 Sno、Cno 和 Degree 列。
SELECT sno,cno,degree FROM score WHERE degree=(SELECT MAX(degree)FROM score);
-- 40、查询和“李军”同性别的所有同学的 Sname。
SELECT sname FROM student WHERE ssex=(SELECT ssex FROM student WHERE sname='李军') AND sname NOT IN ('李军');
-- 41、查询和“李军”同性别并同班的同学 Sname。
SELECT sname FROM student WHERE ssex=(SELECT ssex FROM student WHERE sname='李军')AND sname NOT IN ('李军') AND class=(SELECT class FROM student WHERE sname='李军');
-- 42、查询所有选修“计算机导论”课程的“男”同学的成绩表。
SELECT sno,degree FROM score WHERE sno IN (SELECT sno FROM student WHERE ssex='男') AND cno IN (SELECT cno FROM course WHERE
cname='计算机导论');

项目练习

项目模型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
DROP TABLE IF EXISTS  `borrowing`,`student`,`admin`,`book`,`bookcase`;
CREATE TABLE `student` (
`sid` int(20) NOT NULL,
`sname` varchar(20) NULL,
`class` varchar(20) NULL,
`tel` varchar(20) NULL,
PRIMARY KEY (`sid`)
);

DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`adminid` int(20) NOT NULL,
`adminiName` varchar(20) NOT NULL,
`adminTel` varchar(20) CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`adminid`)
);

DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
`bookid` int NOT NULL,
`bname` varchar(20) NOT NULL,
`bauthor` varchar(20) CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`bookid`)
);

DROP TABLE IF EXISTS `borrowing`;
CREATE TABLE `borrowing` (
`borrowid` int(20) NOT NULL,
`b_sid` int(20) NOT NULL,
`b_aid` int(20) NULL,
`backDate` date NOT NULL,
`borrowDate` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
`borrowBook` int(20) NOT NULL,
PRIMARY KEY (`borrowid`),
FOREIGN KEY (`b_sid`) REFERENCES `student`(sid),
FOREIGN KEY (`b_aid`) REFERENCES `admin`(adminid),
FOREIGN KEY (`borrowBook`) REFERENCES `book`(bookid)
);

DROP TABLE IF EXISTS `bookcase`;
CREATE TABLE `bookcase` (
`bookcaseid` int NOT NULL,
`bcbook` int(20) NOT NULL,
PRIMARY KEY (`bookcaseid`),
FOREIGN KEY (`bcbook`) REFERENCES `book`(bookid)
);

  • Copyright: Copyright is owned by the author. For commercial reprints, please contact the author for authorization. For non-commercial reprints, please indicate the source.

扫一扫,分享到微信

微信分享二维码
  • Copyrights © 2021 John Doe
  • Visitors: | Views:

请我喝瓶快乐水吧~