MYSQL数据库学习笔记（总）

MYSQL 数据库学习笔记

一、建表与插入数据

使用JETBRAIN的Datagrip编辑数据库。
注意建立database之后，使用use database才可能异性后续操作。

---

CREATE DATABASE homework;
USE homework;
CREATE TABLE students
(sno varchar(3) not null,
 sname varchar(4) not null,
ssex varchar(2) not null,
sbirthday datetime,
 class varchar(5));
ALTER TABLE STUDENTS DEFAULT CHARACTER SET UTF8;
SHOW CREATE TABLE STUDENTS; #注意这种方式无法改变latin到UTF8。
ALTER TABLE STUDENTS CHANGE SNAME SNAME VARCHAR(50) CHARACTER SET utf8;
ALTER TABLE STUDENTS CHANGE SSEX SSEX VARCHAR(50) CHARACTER SET utf8;
SHOW CREATE TABLE STUDENTS;
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);

CREATE TABLE courses
(cno varchar(5) not null,
cname varchar(10) not null,
tno varchar(10) not null);
ALTER TABLE COURSES CHANGE CNAME CNAME VARCHAR(50) CHARACTER SET UTF8;
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);

CREATE TABLE scores
(sno varchar(3) not null,
cno varchar(5) not null,
  degree numeric(10,1) not null);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

CREATE TABLE teachers
(tno VARCHAR(3) NOT NULL,
tname VARCHAR(4) NOT NULL,
tsex VARCHAR(2) NOT NULL,
tbirthday DATETIME NOT NULL,
prof VARCHAR(6),
depart VARCHAR(10) NOT NULL);
ALTER TABLE TEACHERS CHANGE TNAME TNAME VARCHAR(40) CHARACTER SET UTF8;
ALTER TABLE TEACHERS CHANGE TSEX TSEX VARCHAR(40) CHARACTER SET UTF8;
ALTER TABLE TEACHERS CHANGE PROF PROF VARCHAR(40) CHARACTER SET UTF8;
ALTER TABLE TEACHERS CHANGE depart DEPART VARCHAR(40) CHARACTER SET UTF8;
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');

MYSQL常见习题45题

---

#1.查询StudentS表中的所有记录的Sname、Ssex和Class列。
SELECT SNAME,SSEX,CLASS FROM STUDENTS;
#2、 查询教师所有的单位即不重复的Depart列。
SELECT DISTINCT DEPART FROM TEACHERS;
#3.查询STUDENT表的所有记录
SELECT * FROM STUDENTS;
#4.查询Score表中成绩在60到80之间的所有记录。
SELECT * FROM SCORES
WHERE DEGREE BETWEEN 60 AND 80;
#5.查询Score表中成绩为85，86或88的记录。
SELECT * FROM SCORES
WHERE DEGREE IN (85,86,88)
#6.查询Student表中“95031”班或性别为“女”的同学记录。
SELECT * FROM STUDENTS
WHERE CLASS=95031 OR SSEX='女'
#7.以Class降序查询Student表的所有记录。
SELECT * FROM STUDENTS
ORDER BY CLASS DESC;
#8.以Cno升序、Degree降序查询Score表的所有记录。
SELECT * FROM scores
ORDER BY CNO,DEGREE DESC;
#9.查询“95031”班的学生人数。
SELECT COUNT(1) AS STUNUM
FROM STUDENTS WHERE CLASS=95031;
#10.查询Score表中的最高分的学生学号和课程号。
SELECT SNO,CNO FROM scores
ORDER BY DEGREE DESC
LIMIT 3;
#11.查询‘3-105’号课程的平均分。
SELECT AVG(DEGREE)
FROM SCORES
WHERE CNO='3-105';
#12.查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT CNO,AVG(DEGREE)
FROM scores WHERE CNO LIKE '3%'
GROUP BY cno
HAVING COUNT(SNO)>=5;
#13.查询最低分大于70，最高分小于90的Sno列。
SELECT SNO FROM scores
GROUP BY SNO
HAVING MIN(DEGREE)>70 AND MAX(DEGREE)<90;
#14.查询所有学生的Sname、Cno和Degree列。
SELECT SNAME,CNO,DEGREE FROM STUDENTS INNER JOIN scores
ON(STUDENTS.SNO=SCORES.sno)
ORDER BY degree;
#15.查询所有学生的Sno、Cname和Degree列。
SELECT SNO,CNAME,DEGREE FROM SCORES INNER JOIN COURSES
ON(SCORES.cno =COURSES.cno)
ORDER BY SNO;
#16.查询所有学生的Sname、Cname和Degree列。
SELECT SNAME,CNAME,DEGREE FROM STUDENTS INNER JOIN SCORES
ON(STUDENTS.sno = SCORES.sno) INNER JOIN COURSES
ON(COURSES.cno = SCORES.cno)
ORDER BY SNAME;
#17.查询“95033”班所选课程的平均分。
SELECT CNAME,AVG(DEGREE)
FROM STUDENTS INNER JOIN scores
ON(STUDENTS.sno=SCORES.sno) INNER JOIN COURSES
ON(SCORES.cno=COURSES.cno)
WHERE CLASS='95033'
GROUP BY COURSES.cno
ORDER BY CNAME;

#18.假设使用如下命令建立了一个grade表：
CREATE TABLE grade(low TINYINT,upp TINYINT,rank CHAR(1));
INSERT INTO grade VALUES(90,100,'A');
INSERT INTO grade VALUES(80,89,'B');
INSERT INTO grade VALUES(70,79,'C');
INSERT INTO grade VALUES(60,69,'D');
INSERT INTO grade VALUES(0,59,'E');
#现查询所有同学的Sno、Cno和rank列。
SELECT SNO,CNO,RANK FROM SCORES INNER JOIN grade
ON(SCORES.DEGREE>=GRADE.LOW AND SCORES.DEGREE<=GRADE.UPP)
ORDER BY SNO;
#19.查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT S1.SNO,S1.degree
FROM SCORES AS S1 INNER JOIN SCORES AS S2
ON(S1.CNO=S2.CNO AND S1.DEGREE>S2.DEGREE)
WHERE S1.CNO='3-105' AND S2.SNO='109'
ORDER BY S1.SNO;
#20.查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
SELECT * FROM scores
GROUP BY sno
HAVING COUNT(CNO)>1 AND DEGREE !=MAX(DEGREE);
#21.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SELECT S1.* FROM SCORES AS S1 INNER JOIN SCORES AS S2
ON(S1.CNO = S2.CNO AND S1.DEGREE > S2.DEGREE)
WHERE S2.sno='109' AND S2.CNO = '3-105'
ORDER BY S1.sno;
#22.查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT S1.SNO,S1.SNAME,S1.SBIRTHDAY FROM STUDENTS AS S1 INNER JOIN STUDENTS AS S2
ON(YEAR(S1.sbirthday)=YEAR(S2.sbirthday))
WHERE S1.SNO='108'
#23.查询“张旭“教师任课的学生成绩。
SELECT SNO,DEGREE
FROM SCORES INNER JOIN COURSES
ON(SCORES.CNO=COURSES.CNO) INNER JOIN TEACHERS
ON(COURSES.TNO=TEACHERS.TNO)
WHERE TEACHERS.TNAME ='张旭'
#24.查询选修某课程的同学人数多于5人的教师姓名。
#25.查询95033班和95031班全体学生的记录。
SELECT * FROM STUDENTS
WHERE CLASS IN('95033','95031')
ORDER BY CLASS;
#26.查询存在有85分以上成绩的课程Cno.
SELECT DISTINCT CNO FROM scores
WHERE DEGREE>85;
#27.查询出“计算机系“教师所教课程的成绩表。
SELECT Tname,Cname,SName,Degree
FROM Teachers INNER JOIN Courses
ON(Teachers.Tno=Courses.Tno) INNER JOIN Scores
ON(Courses.Cno=Scores.Cno) INNER JOIN Students
ON(Scores.Sno=Students.Sno)
WHERE Teachers.Depart='计算机系'
ORDER BY Tname,Cname,Degree DESC;
#29.查询选修编号为“3-105“课程且成绩至少高于任意选修编号为“3-245”的同学的成绩的Cno
# 、Sno和Degree,并按Degree从高到低次序排序。
SELECT CNO,SNO,degree
FROM scores
WHERE CNO='3-105' AND DEGREE > ANY(
  SELECT degree
  FROM scores
  WHERE CNO='3-245')
ORDER BY DEGREE DESC;
#31.查询所有教师和同学的name、sex和birthday.
SELECT SNAME,SSEX,sbirthday
FROM STUDENTS
UNION
SELECT TNAME,TSEX,tbirthday
FROM TEACHERS;
#32.查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT Sname,Ssex,Sbirthday
FROM Students
WHERE Ssex='女'
UNION
SELECT Tname,Tsex,Tbirthday
FROM Teachers
WHERE Tsex='女';
#33.查询成绩比该课程平均成绩低的同学的成绩表。
SELECT S1.*
FROM SCORES AS S1 INNER JOIN (
  SELECT CNO,AVG(DEGREE) AS ADEGREE
  FROM scores
  GROUP BY CNO)S2
ON(S1.CNO=S2.CNO AND S1.DEGREE<S2.ADEGREE
  )
#34.查询所有任课教师的Tname和Depart.
SELECT TNAME,DEPART FROM TEACHERS
WHERE TNO IN (SELECT TNO FROM COURSES)
#35.查询所有未讲课的教师的Tname和Depart.
SELECT TNAME,DEPART FROM TEACHERS
WHERE TNO NOT IN (SELECT TNO FROM COURSES)
#36.查询至少有2名男生的班号。
SELECT CLASS,COUNT(1) AS BOYCOUNT
FROM STUDENTS
WHERE SSEX='男'
GROUP BY class
HAVING BOYCOUNT>=2;
#37.查询Student表中不姓“王”的同学记录。
SELECT * FROM STUDENTS
WHERE SNAME NOT LIKE '王%'
#38.查询Student表中每个学生的姓名和年龄。
SELECT SNAME,YEAR(NOW())-YEAR(SBIRTHDAY) AS SAGE
FROM STUDENTS;
#39.查询Student表中最大和最小的Sbirthday日期值。
SELECT MAX(sbirthday),MIN(sbirthday)
FROM STUDENTS;
#40.以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT * FROM STUDENTS
ORDER BY CLASS DESC, SBIRTHDAY ASC;
#41.查询“男”教师及其所上的课程。
SELECT TEACHERS.TNAME,COURSES.cname
FROM TEACHERS INNER JOIN COURSES
ON(TEACHERS.TNO=COURSES.TNO)
WHERE TEACHERS.TSEX='男'
#42.查询最高分同学的Sno、Cno和Degree列。
SELECT *
FROM SCORES
GROUP BY cno
HAVING DEGREE=MAX(DEGREE)
#43.查询和“李军”同性别的所有同学的Sname.
SELECT s1.Sname
FROM Students AS s1 INNER JOIN Students AS s2
ON(s1.Ssex=s2.Ssex)
WHERE s2.Sname='李军';
#44.查询和“李军”同性别并同班的同学Sname.
SELECT s1.Sname
FROM Students AS s1 INNER JOIN Students AS s2
ON(s1.Ssex=s2.Ssex AND S1.CLASS=S2.class)
WHERE s2.Sname='李军';
#45.查询所有选修“计算机导论”课程的“男”同学的成绩表
SELECT * FROM scores
WHERE SNO IN (SELECT SNO FROM STUDENTS WHERE SSEX='男')
and CNO IN (SELECT CNO FROM COURSES WHERE CNAME='计算机导论');

---

HAVING与WHERE语句的辨析理解

误区：不要错误的以为HAVING必须和GROUP BY混合使用

1.having与where等价的情形

select goods_price,goods_name from sw_goods where goods_price > 100

select goods_price,goods_name from sw_goods having goods_price > 100

解释：上面的having可以用的前提是我已经筛选出了goods_price字段，在这种情况下和where的效果是等效的，但是如果我没有select goods_price 就会报错！！
因为having是从前筛选的字段再筛选，而where是从数据表中的字段直接进行的筛选的。

2、只能用where,不能用having的情形

select goods_name, goods_number from sw_goods having goods_price > 100 
 ##报错！！！因为前面并没有筛选出goods_price 字段

select goods_name,goods_number from sw_goods where goods_price > 100

3. 只可以用having，不可以用where情况

查询每种goods_category_id商品的价格平均值，获取平均价格大于1000元的商品信息

 select goods_category_id , avg(goods_price) as ag from sw_goods group by goods_category having ag > 1000

select goods_category_id , avg(goods_price) as ag from sw_goods where ag>1000 group by goods_category //报错！！因为from sw_goods 这张数据表里面没有ag这个字段

注意:where 后面要跟的是数据表里的字段，如果我把ag换成avg(goods_price)也是错误的！因为表里没有该字段。而having只是根据前面查询出来的是什么就可以后面接什么。

3.Having与Where的具体理解

HAVING类似于WHERE,唯一的差别是WHERE过滤行，HAVING过滤组。
见博客：https://blog.csdn.net/bingogirl/article/details/52559302，解释的非常清楚。

参考链接：
[1]https://blog.csdn.net/mrbcy/article/details/68965271
[2]https://blog.csdn.net/yexudengzhidao/article/details/54924471
[3]https://blog.csdn.net/bingogirl/article/details/52559302