编辑代码

CREATE DATABASE test;
use test;

/*
CREATE TABLE student (
	id int primary key,
	name varchar(255),
	address varchar(255),
	city varchar(255)
);

INSERT INTO student VALUES (1, '刘一', '郑十家隔壁', '河南');
INSERT INTO student VALUES (2, '陈二',  '李四家隔壁', '安徽');
INSERT INTO student VALUES (3, '张三',  '白娘子家隔壁', '杭州');
INSERT INTO student VALUES (4, '李四',  '许仙家隔壁', '杭州');
INSERT INTO student VALUES (5, '王五',  '李四家隔壁', '杭州');
INSERT INTO student VALUES (6, '赵六',  '赵六家隔壁', '河南');
INSERT INTO student VALUES (7, '孙七',  '张三家隔壁', '杭州');
INSERT INTO student VALUES (8, '周八',  '雷峰塔附近', '安徽');
INSERT INTO student VALUES (9, '吴九',  '孙七家隔壁', '杭州');
INSERT INTO student VALUES (10, '郑十',  '周八家隔壁', '杭州');
 

*/

-- 创建学生表
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    sex CHAR(1),
    age TINYINT UNSIGNED,
    class TINYINT UNSIGNED,
    position VARCHAR(5)
);

-- 插入数据（使用单引号，标准SQL格式）
INSERT INTO student (id, name, sex, age, class, position) VALUES
(1, '白起', '男', 40, 1, '坦克'),
(2, '猪八戒', '男', 80, 2, '坦克'),
(3, '花木兰', '女', 22, 3, '战士'),
(4, '狂铁', '男', 30, 1, '战士'),
(5, '娜可', '女', 20, 2, '刺客'),
(6, '李白', '男', 28, 3, '刺客'),
(7, '妲己', '女', 18, 1, '法师'),
(8, '周瑜', '男', 25, 2, '法师'),
(9, '孙尚香', '女', 18, 3, '射手'),
(10, '鲁班', '男', 16, 3, '射手'),
(11, '庄周', '男', 21, 3, '辅助'),
(12, '瑶', '女', 18, 3, '辅助');

-- 课程数据
CREATE TABLE course(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL
);

INSERT INTO course VALUES 
(1, '大学语文'),
(2, '火系魔法导论'),
(3, '高等恋爱理论');

-- 成绩数据
CREATE TABLE sc(
    s_id INT,
    c_id INT,
    score INT
);

INSERT INTO sc VALUES 
(8, 2, 98),
(5, 2, 66),
(2, 1, 20),
(2, 3, 88),
(6, 1, 99);

#insert into student (name, sex) values ('蔡文姬', '女');

#delete from student where id=5;

#update student set position='辅助' where id=13;

#SELECT * FROM student;

#select avg(age) from student;

#select sex,count(*) from student group by sex having sex='男'; 

#select * from student order by age desc;

#select * from student limit 3,2;

#select s.name, sc.score from student s left join sc on s.id = sc.s_id;

select s.name 姓名, sc.score 成绩, c.name 课程 from student s 
inner join sc on s.id = sc.s_id 
inner join course c on sc.c_id = c.id 
where s.sex = '男' order by sc.score desc limit 0,3;

select * from student where age > (select avg(age) from student) and position = '坦克';