mysql> -- 创建学生管理数据库
mysql> CREATE DATABASE IF NOT EXISTS student_management
-> DEFAULT CHARACTER SET utf8mb4
-> COLLATE utf8mb4_0900_ai_ci;
ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'
mysql>
mysql> USE student_management;
ERROR 1049 (42000): Unknown database 'student_management'
mysql>
mysql> -- 学生表
mysql> CREATE TABLE students (
-> student_id INT AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(50) NOT NULL,
-> gender ENUM('男','女') DEFAULT '男',
-> birthdate DATE,
-> contact_phone VARCHAR(15) UNIQUE
-> ) ENGINE=InnoDB;
ERROR 1046 (3D000): No database selected
mysql>
mysql> -- 课程表
mysql> CREATE TABLE courses (
-> course_id INT AUTO_INCREMENT PRIMARY KEY,
-> course_name VARCHAR(50) NOT NULL,
-> credit TINYINT UNSIGNED DEFAULT 1
-> );
ERROR 1046 (3D000): No database selected
mysql>
mysql> -- 成绩表
mysql> CREATE TABLE scores (
-> score_id INT AUTO_INCREMENT PRIMARY KEY,
-> student_id INT,
-> course_id INT,
-> score DECIMAL(5,2) CHECK (score BETWEEN 0 AND 100),
-> exam_date DATE,
-> FOREIGN KEY (student_id) REFERENCES students(student_id),
-> FOREIGN KEY (course_id) REFERENCES courses(course_id)
-> );
ERROR 1046 (3D000): No database selected
mysql>
mysql> -- 插入示例数据
mysql> INSERT INTO students (name, gender, birthdate, contact_phone) VALUES
-> ('张三', '男', '2003-05-15', '13800138001'),
-> ('李四', '女', '2002-11-23', '13900139002');
ERROR 1046 (3D000): No database selected
mysql>
mysql> INSERT INTO courses (course_name, credit) VALUES
-> ('高等数学', 4),
-> ('大学物理', 3),
-> ('数据库原理', 2);
ERROR 1046 (3D000): No database selected
mysql>
mysql> INSERT INTO scores (student_id, course_id, score, exam_date) VALUES
-> (1, 1, 85.5, '2023-06-10'),
-> (1, 3, 92.0, '2023-06-12'),
-> (2, 2, 78.5, '2023-06-11');
ERROR 1046 (3D000): No database selected
mysql>
mysql> -- 查询示例
mysql> -- 查询学生基本信息
mysql> SELECT * FROM st
最新发布
本文介绍了解决多库查询时出现的排序规则冲突问题的方法。通过在字段后添加CollateDatabase_Default来确保不同数据库间的一致性。
扫一扫
433
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
