mysql-day1

一、转储SQL文件

• 关键字 mysqldump
• 导出
  mysqldump -u root -d db1 > db1.sql -p : 只保存数据表结构
  mysqldump -u root db1 > db1.sql -p : 保存数据表结构和数据
• 导入
  create database db2;
  mysqldump -u root -d db2 < db1.sql -p;

二、练习题

1. 创建如上表

-- 创建班级表 
CREATE TABLE class ( 
cid INT auto_increment PRIMARY KEY, 
caption VARCHAR ( 20 ) NOT NULL 
);

INSERT INTO class ( caption )
VALUES
	( "三年二班" ),
	( "一年二班" ),
	( "三年一班" );
	
	
-- 创建学生表 
CREATE TABLE student (
	sid INT auto_increment PRIMARY KEY,
	sname VARCHAR ( 20 ) NOT NULL,
	gender ENUM ( "男", "女" ) NOT NULL,
	class_id INT,
	FOREIGN KEY ( class_id ) references class ( cid ) 
);

INSERT INTO student ( sname, gender, class_id )
VALUES
	( "钢弹", "女", 1 ),
	( "铁锤", "女", 1 ),
	( "山炮", "男", 2 );
	
	
-- 创建老师表 

CREATE TABLE teacher ( 
tid INT auto_increment PRIMARY KEY, 
tname VARCHAR ( 20 ) NOT NULL 
);

insert INTO teacher ( tname )
VALUES
	( "波多" ),
	( "苍空" ),
	( "饭岛" );
	
	
-- 创建课程表 
CREATE TABLE course (
	cid INT auto_increment PRIMARY KEY,
	cname VARCHAR ( 20 ) NOT NULL,
	teacher_id INT,
	FOREIGN KEY ( teacher_id ) references teacher ( tid ) 
);


INSERT INTO course ( cname, teacher_id )
VALUES
	( "生物", 1 ),
	( "体育", 1 ),
	( "物理", 2 ); 
	
	
-- 创建成绩表 
	CREATE TABLE score (
		sid INT auto_increment PRIMARY KEY,
		student_id INT,
		course_id INT,
		number INT,
		FOREIGN KEY ( student_id ) references student ( sid ),
		FOREIGN KEY ( course_id ) references course ( cid ) 
	);
INSERT INTO score ( student_id, course_id, number )
VALUES
	( 1, 1, 60 ),
	( 1, 2, 59 ),
	( 2, 2, 100 );

2. 查询

• 查询每个老师任课数

SELECT
	teacher_id,
	teacher.tname,
	count( cid ) AS course_count 
FROM
	course
	LEFT JOIN teacher ON course.teacher_id = teacher.tid 
GROUP BY
	teacher_id;

• 获取学生详细信息
  SELECT * FROM student LEFT JOIN class ON student.class_id = class.cid;

• 查找及格的学生姓名（临时表）

SELECT
	student.sname 
FROM
	( SELECT score.student_id FROM score WHERE score.number >= 60 ) AS t1
	LEFT JOIN student ON t1.student_id = student.sid;

• 查询平均成绩大于60分的同学的姓名和平均成绩

SELECT student.sname,AVG(score.number)
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid 
GROUP BY
	score.student_id 
HAVING
	AVG( score.number )> 60;