数据库操作实战：从创建到备份还原

创建以自己作品命名的数据库

CREATE DATABASE 'YourDatabaseName';

创建数据表(学生，课程，成绩)。

CREATE TABLE Student (
   id INT PRIMARY KEY,
   'name' VARCHAR(50),
   age INT
);

CREATE TABLE Course (
   id INT PRIMARY KEY,
   'name' VARCHAR(50),
   credits INT
);

CREATE TABLE Score (
   id INT PRIMARY KEY,
   student_id INT,
   course_id INT,
   score INT,
   FOREIGN KEY (student_id) REFERENCES Student(id),
   FOREIGN KEY (course_id) REFERENCES Course(id)
);

写出一个增加外码约束的语句，一个创建索引的语句。

增加外码约束的语句：

ALTER TABLE Score
ADD CONSTRAINT fk_student
FOREIGN KEY (student_id) REFERENCES Student(id);

创建索引的语句：

CREATE INDEX idx_course_name ON Course('name');

分别为每个表添加3条记录，注意表和表之间的外码约束关系。

INSERT INTO Student (id, 'name', age) VALUES (1, 'John', 20);
INSERT INTO Student (id, 'name', age) VALUES (2, 'Jane', 22);
INSERT INTO Student (id, 'name', age) VALUES (3, 'Mike', 21);

INSERT INTO Course (id, 'name', credits) VALUES (1, 'Math', 3);
INSERT INTO Course (id, 'name', credits) VALUES (2, 'English', 2);
INSERT INTO Course (id, 'name', credits) VALUES (3, 'Science', 4);

INSERT INTO Score (id, student_id, course_id, score) VALUES (1, 1, 1, 80);
INSERT INTO Score (id, student_id, course_id, score) VALUES (2, 2, 2, 90);
INSERT INTO Score (id, student_id, course_id, score) VALUES (3, 3, 3, 85);

创建一个视图。

CREATE VIEW StudentCourse AS
SELECT Student.'name', Course.'name' AS course_name, Score.score
FROM Student
JOIN Score ON Student.id = Score.student_id
JOIN Course ON Course.id = Score.course_id;

写出两个多表查询语句。

-- 查询每个学生的平均成绩
SELECT Student.'name', AVG(Score.score) AS average_score
FROM Student
JOIN Score ON Student.id = Score.student_id
GROUP BY Student.'name';

-- 查询选修了特定课程的学生
SELECT Student.'name'
FROM Student
JOIN Score ON Student.id = Score.student_id
JOIN Course ON Course.id = Score.course_id
WHERE Course.'name' = 'Math';

写成一个嵌套除语句，一个多表更新语句。

嵌套除语句：

SELECT 'name'
FROM Student
WHERE id NOT IN (
   SELECT student_id
   FROM Score
);

多表更新语句：

UPDATE Score
JOIN Student ON Score.student_id = Student.id
SET Score.score = 95
WHERE Student.'name' = 'John';

创建一个存储过程。

CREATE PROCEDURE GetStudentData()
BEGIN
   SELECT * FROM Student;
END;

创建一个触发器。

CREATE TRIGGER UpdateCourseCredits
AFTER UPDATE ON Score
FOR EACH ROW
BEGIN
   UPDATE Course
   SET credits = credits + 1
   WHERE id = NEW.course_id;
END;

创建一个用户，并进行授权。

CREATE USER 'YourUsername' IDENTIFIED BY 'YourPassword';
GRANT SELECT, INSERT, UPDATE, DELETE ON 'YourDatabaseName'.* TO 'YourUsername';

root身份修改上面用户的密码为“112233”。

ALTER USER 'YourUsername' IDENTIFIED BY '112233';

将当前数据库备份到c盘gwqw文件夹下。

-- 使用mysqldump命令备份数据库
mysqldump -u root -p 'YourDatabaseName' > C:\gwqw\database_backup.sql

删除当前数据库，还原数据，并验证数据是否还原成功。

-- 删除数据库
DROP DATABASE 'YourDatabaseName';

-- 还原数据库
CREATE DATABASE 'YourDatabaseName';
USE 'YourDatabaseName';
source C:/gwqw/database_backup.sql;

-- 验证数据是否还原成功
SELECT * FROM Student;
SELECT * FROM Course;
SELECT * FROM Score;