MySQL資料庫（3/5）SQL導入導出及強化練習

一、SQL強化

create database day27db default charset utf8 collate utf8_general_ci;
  use day27db;

  drop database day27db;
  drop database IF EXISTS day27db;

【注意】if exists 的使用，防止報錯。

利用導入資料庫指令：

導入

mysql -u root -p day27db < /Users/wupeiqi/day27db.sql
導出

# 結構+資料 mysqldump -u root -p day27db > /Users/wupeiqi/day27db2.sql # 結構 mysqldump -u root -p -d day27db > /Users/wupeiqi/day27db3.sql

【總結】可以将sql指令寫到一個檔案中，檔案字尾名為.sql，然後使用mysql指令導入這個sql檔案自動執行裡面的sql指令。

【mysqldump】導出指令，在mysql安裝目錄bin裡面。

如上圖，導出的sql檔案中，表名class兩邊帶`符号，這樣做是為了防止表名是sql關鍵字的情況下發生混淆。ENGINE是資料庫引擎的參數，AUTO_INCAREMENT = 5，是因為表裡面已經有4條資料，是以再有資料的時候id從5開始自增。

【注意】不管怎麼導入資料，建立資料庫的步驟隻能通過用戶端登入後建立，而沒法通過導入sql檔案的方式建立。

二、對本章内容的習題都捋一遍，能夠了解它的原理和實作，後期開發中基本上沒什麼問題了。

根據上圖建立資料庫 & 表結構并錄入資料（可以自行創造資料） create database day27db default charset utf8 collate utf8_general_ci; use day27db; drop database day27db; drop database IF EXISTS day27db; 利用導入資料庫指令：
- 導入
  
  mysql -u root -p day27db < /Users/wupeiqi/day27db.sql
- 導出
  
  # 結構+資料 mysqldump -u root -p day27db > /Users/wupeiqi/day27db2.sql # 結構 mysqldump -u root -p -d day27db > /Users/wupeiqi/day27db3.sql
create table class( cid int not null auto_increment primary key, caption varchar(16) not null )default charset=utf8; INSERT INTO class VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班'); create table student( sid int not null auto_increment primary key, gender char(1) not null, class_id int not null, sname varchar(16) not null, constraint fk_student_class foreign key (class_id) references class(cid) )default charset=utf8; INSERT INTO student VALUES ('1', '男', '1', '了解'), ('2', '女', '1', '鋼蛋'), ('3', '男', '1', '張三'), ('4', '男', '1', '張一'), ('5', '女', '1', '張二'), ('6', '男', '1', '張四'), ('7', '女', '2', '鐵錘'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '劉三'), ('14', '男', '3', '劉一'), ('15', '女', '3', '劉二'), ('16', '男', '3', '劉四'); create table teacher( tid int not null auto_increment primary key, tname varchar(16) not null )default charset=utf8; INSERT INTO `teacher` VALUES ('1', '張磊老師'), ('2', '李平老師'), ('3', '劉海燕老師'), ('4', '朱雲海老師'), ('5', '李傑老師'); create table course( cid int not null auto_increment primary key, cname varchar(16) not null, teacher_id int not null, constraint fk_course_teacher foreign key (teacher_id) references teacher(tid) )default charset=utf8; INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '實體', '2'), ('3', '體育', '3'), ('4', '美術', '2'); CREATE TABLE `score` ( `sid` int NOT NULL AUTO_INCREMENT PRIMARY KEY, `student_id` int NOT NULL, `course_id` int NOT NULL, `num` int NOT NULL, CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ) DEFAULT CHARSET=utf8; INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
建立使用者 luffy 并賦予此資料庫的所有權限。

create user 'luffy'@'%' identified by 'root123'; grant all privileges on day27db.* TO 'luffy'@'%'; flush privileges;
查詢姓“李”的老師的個數。

select * from teacher where tname like "李%";
查詢姓“張”的學生名單。

select * from student where sname like "張%";
查詢男生、女生的人數。

select gender,count(1) from student group by gender;
查詢同名同姓學生名單，并統計同名人數。

select sname,count(1) from student group by sname; select sname,count(1) from student group by sname having count(1) > 1;
查詢 “三年二班” 的所有學生。

select * from student left join class on student.class_id = class.cid where class.caption="三年二班";
查詢每個班級的班級名稱、班級人數。

select class_id,count(1) from student group by class_id; select class.caption,count(1) from student left join class on student.class_id = class.cid group by class.caption;
查詢成績小于60分的同學的學号、姓名、成績、課程名稱。

select * from score where num <60; select student.sid, student.sname, score.num, course.cname from score left join student on score.student_id=student.sid left join course on score.course_id =course.cid where num <60;
查詢選修了 “生物課” 的所有學生ID、學生姓名、成績。

select * from score left join course on score.course_id =course.cid where course.cname="生物"; select student.sid, student.sname, score.num from score left join course on score.course_id =course.cid left join student on score.student_id=student.sid where course.cname="生物";
查詢選修了 “生物課” 且分數低于60的的所有學生ID、學生姓名、成績。

select student.sid,student.sname,score.num from score left join course on score.course_id =course.cid left join student on score.student_id=student.sid where course.cname="生物" and score.num < 60;
查詢所有同學的學号、姓名、選課數、總成績。

select student_id,count(1),sum(num) from score group by student_id; select student_id,student.sname,count(1),sum(num) from score left join student on score.student_id=student.sid group by student_id;
查詢各科被選修的學生數。

select course_id,count(1) from score group by course_id; select course_id,course.cname,count(1) from score left join course on score.course_id =course.cid group by course_id;
查詢各科成績的總分、最高分、最低分，顯示：課程ID、課程名稱、總分、最高分、最低分。

select course_id,course.cname,sum(num), max(num), min(num) from score left join course on score.course_id =course.cid group by course_id;
查詢各科成績的平均分，顯示：課程ID、課程名稱、平均分。

select course_id,course.cname,avg(num) from score left join course on score.course_id =course.cid group by course_id;
查詢各科成績的平均分，顯示：課程ID、課程名稱、平均分（按平均分從大到小排序）。

select course_id,course.cname,avg(num) from score left join course on score.course_id =course.cid group by course_id order by avg(num) desc; select course_id,course.cname,avg(num) as A from score left join course on score.course_id =course.cid group by course_id order by A desc;
查詢各科成績的平均分和及格率，顯示：課程ID、課程名稱、平均分、及格率。

10/20 = 及格率 select course_id,count(1) from score group by course_id; select sid, course_id, num, case when score.num >= 60 then 1 else 0 end "是否及格" from score; select sid,course_id,num,case when score.num > 60 then 1 else 0 end "是否及格" from score; select course_id, course.cname, avg(num), count(1) as total, sum(case when score.num > 60 then 1 else 0 end) from score left join course on score.course_id =course.cid group by course_id; select course_id, course.cname, avg(num), sum(case when score.num > 60 then 1 else 0 end)/count(1) *100 as percent from score left join course on score.course_id =course.cid group by course_id;
查詢平均成績大于60的所有學生的學号、平均成績；

select student_id,avg(num) from score group by student_id having avg(num) > 60;
查詢平均成績大于85的所有學生的學号、平均成績、姓名。

select student_id,avg(num) from score group by student_id having avg(num) > 85; select student_id,avg(num),student.sname from score left join student on score.student_id=student.sid group by student_id having avg(num) > 85;
查詢 “三年二班” 每個學生的學号、姓名、總成績、平均成績。

SELECT * FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN class ON class.cid = student.class_id; SELECT * FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN class ON class.cid = student.class_id WHERE class.caption = "三年二班"; SELECT student_id, sname, sum( num ), avg( num ) FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN class ON class.cid = student.class_id WHERE class.caption = "三年二班" GROUP BY student_id
查詢各個班級的班級名稱、總成績、平均成績、及格率（按平均成績從大到小排序）。

SELECT class.cid, class.caption, sum( num ), avg( num ) as av, sum( CASE WHEN score.num > 60 THEN 1 ELSE 0 END ) / count( 1 ) * 100 as JG FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN class ON class.cid = student.class_id GROUP BY class.cid ORDER BY av desc
查詢學過 “波多” 老師課的同學的學号、姓名。

SELECT student.sid, student.sname FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN course ON score.course_id = course.cid LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = "波多"
查詢沒學過 “波多” 老師課的同學的學号、姓名。

SELECT student.sid, student.sname FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN course ON score.course_id = course.cid LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname != "波多" select * from student where sid not in( SELECT student.sid FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN course ON score.course_id = course.cid LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = "波多" )
查詢選修 “蒼空” 老師所授課程的學生中，成績最高的學生姓名及其成績（不考慮并列）。

SELECT student.sid, student.sname FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN course ON score.course_id = course.cid LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = "蒼空" ORDER BY score.num DESC LIMIT 1
查詢選修 “蒼空” 老師所授課程的學生中，成績最高的學生姓名及其成績（考慮并列）。

SELECT student.sid, student.sname FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN course ON score.course_id = course.cid LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = "蒼空" AND score.num = ( SELECT max( num ) FROM score LEFT JOIN course ON score.course_id = course.cid LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = "蒼空" )
查詢隻選修了一門課程的全部學生的學号、姓名。

SELECT student.sid, student.sname FROM score LEFT JOIN student ON score.student_id = student.sid GROUP BY student_id HAVING count( 1 ) =1
查詢至少選修兩門課程的學生學号、學生姓名、選修課程數量。

SELECT student.sid, student.sname , count(1) FROM score LEFT JOIN student ON score.student_id = student.sid GROUP BY student_id HAVING count( 1 ) >= 2
查詢兩門及以上不及格的同學的學号、學生姓名、選修課程數量。

SELECT student.sid, student.sname , count(1) FROM score LEFT JOIN student ON score.student_id = student.sid where num < 60 GROUP BY student_id HAVING count( 1 ) >= 2
查詢選修了所有課程的學生的學号、姓名。

SELECT student.sid, student.sname FROM score LEFT JOIN student ON score.student_id = student.sid GROUP BY student_id HAVING count( 1 ) = ( SELECT count( 1 ) FROM course )
查詢未選修所有課程的學生的學号、姓名。

SELECT student.sid, student.sname FROM score LEFT JOIN student ON score.student_id = student.sid GROUP BY student_id HAVING count( 1 ) != ( SELECT count( 1 ) FROM course )
查詢所有學生都選修了的課程的課程号、課程名。

SELECT course.cid, course.cname FROM score LEFT JOIN course ON score.course_id = course.cid GROUP BY course_id HAVING count( 1 ) = ( SELECT count( 1 ) FROM student )
查詢選修 “生物” 和 “實體” 課程的所有學生學号、姓名。

SELECT student.sid, student.sname FROM score LEFT JOIN course ON score.course_id = course.cid LEFT JOIN student ON score.student_id = student.sid WHERE course.cname in ("生物","實體") GROUP BY student_id having count(1) = 2;
查詢至少有一門課與學号為“1”的學生所選的課程相同的其他學生學号和姓名。

SELECT student.sid, student.sname FROM score LEFT JOIN course ON score.course_id = course.cid LEFT JOIN student ON score.student_id = student.sid WHERE score.course_id in ( select course_id from score where student_id=1) and score.student_id != 1 GROUP BY student_id HAVING count(1) > 1
查詢與學号為 “2” 的同學選修的課程完全相同的其他學生學号和姓名。

SELECT student.sid, student.sname FROM score LEFT JOIN course ON score.course_id = course.cid LEFT JOIN student ON score.student_id = student.sid WHERE score.course_id in ( select course_id from score where student_id=2) and score.student_id in ( select student_id from score where student_id!=2 group by student having count(1) = select count(1) from score where student_id=2 ) GROUP BY student_id HAVING count(1) = ( select count(1) from score where student_id=2 ) # 如果id=2學生他的課程數量和其他人的課程數量是一樣。 select student_id from score where student_id!=2 group by student having count(1) = select count(1) from score where student_id=2 select student_id from score where student_id!=2 group by student_id having count(1) = select count(1) from score where student_id=2
查詢“生物”課程比“實體”課程成績高的所有學生的學号；

SELECT * FROM score LEFT JOIN course ON score.course_id = course.cid where cname in ("生物","實體"); SELECT *, case cname WHEN "生物" then num else -1 end sw, case cname WHEN "實體" then num else -1 end wl FROM score LEFT JOIN course ON score.course_id = course.cid where cname in ("生物","實體"); SELECT student_id, max(case cname WHEN "生物" then num else -1 end) as sw, max(case cname WHEN "實體" then num else -1 end) as wl FROM score LEFT JOIN course ON score.course_id = course.cid where cname in ("生物","實體") GROUP BY student_id; SELECT student_id, max( CASE cname WHEN "生物" THEN num ELSE 0 END ) AS sw, max( CASE cname WHEN "實體" THEN num ELSE 0 END ) AS wl FROM score LEFT JOIN course ON score.course_id = course.cid WHERE cname IN ( "生物", "實體" ) GROUP BY student_id HAVING sw > wl;
查詢每門課程成績最好的前3名 (不考慮成績并列情況) 。

SELECT cid, cname, ( select student.sname from score left join student on student.sid = score.student_id where course_id = course.cid order by num desc limit 1 offset 0) as "第1名", ( select student.sname from score left join student on student.sid = score.student_id where course_id = course.cid order by num desc limit 1 offset 1) as "第2名", ( select student.sname from score left join student on student.sid = score.student_id where course_id = course.cid order by num desc limit 1 offset 2) as "第3名" FROM course;
查詢每門課程成績最好的前3名 (考慮成績并列情況) 。

SELECT cid, cname, ( select num from score where course_id = course.cid GROUP BY num order by num desc limit 1 offset 0) as "最高分", ( select num from score where course_id = course.cid GROUP BY num order by num desc limit 1 offset 1) as "第二高分", ( select num from score where course_id = course.cid GROUP BY num order by num desc limit 1 offset 2) as "第三高分" FROM course; select * from score left join ( SELECT cid, cname, ( select num from score where course_id = course.cid GROUP BY num order by num desc limit 1 offset 0) as "最高分", ( select num from score where course_id = course.cid GROUP BY num order by num desc limit 1 offset 1) as "第二高分", ( select num from score where course_id = course.cid GROUP BY num order by num desc limit 1 offset 2) as third FROM course ) as C on score.course_id = C.cid WHERE score.num >= C.third
建立一個表 sc
，然後将 score 表中所有資料插入到 sc 表中。

CREATE TABLE `sc` ( `sid` int NOT NULL AUTO_INCREMENT PRIMARY KEY, `student_id` int NOT NULL, `course_id` int NOT NULL, `num` int NOT NULL, CONSTRAINT `fk_sc_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), CONSTRAINT `fk_sc_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ) DEFAULT CHARSET=utf8; INSERT INTO sc SELECT * from score;
向 sc 表中插入一些記錄，這些記錄要求符合以下條件：
- 學生ID為：沒上過課程ID為 “2” 課程的學生的學号；
- 課程ID為：2
- 成績為：80
-- 上過 select student_id from score where course_id =2; -- 沒上過 SELECT sid FROM student WHERE sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 ) -- 構造資料 SELECT sid, 2, 80 FROM student WHERE sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 ); INSERT INTO sc ( student_id, course_id, num ) SELECT sid, 2, 80 FROM student WHERE sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )
向 sc 表中插入一些記錄，這些記錄要求符合以下條件：
- 學生ID為：沒上過課程ID為 “2” 課程的學生的學号。
- 課程ID為：2。
- 成績為：課程ID為3的最高分。
SELECT sid, 2, (select max(num) from score where course_id=3) as num FROM student WHERE sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 ); INSERT INTO sc ( student_id, course_id, num ) SELECT sid, 2, (select max(num) from score where course_id=3) as num FROM student WHERE sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )

MySQL資料庫（3/5）SQL導入導出及強化練習

一、SQL強化

【注意】if exists 的使用，防止報錯。

【總結】可以将sql指令寫到一個檔案中，檔案字尾名為.sql，然後使用mysql指令導入這個sql檔案自動執行裡面的sql指令。

【mysqldump】導出指令，在mysql安裝目錄bin裡面。

【注意】不管怎麼導入資料，建立資料庫的步驟隻能通過用戶端登入後建立，而沒法通過導入sql檔案的方式建立。

二、對本章内容的習題都捋一遍，能夠了解它的原理和實作，後期開發中基本上沒什麼問題了。

繼續閱讀

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

JAVA高效程式設計指南

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

關于SQL語言

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

詳解STM32單片機的堆棧

mysql使用source指令導入.sql檔案

sqlServer根據經緯查距離