-- 用户表
drop table grade;
drop table users;
CREATE TABLE users(
	id    INT(11) PRIMARY KEY AUTO_INCREMENT, 
	uname VARCHAR(40) NOT NULL DEFAULT '张三' COMMENT '用户名',
	upass VARCHAR(40) NOT NULL COMMENT '密码',
	phone BIGINT(11)  NOT NULL COMMENT '电话号码',
	qq    BIGINT(15)  NOT NULL COMMENT 'qq号码',
	sex   INT(1)      NOT NULL DEFAULT 0 COMMENT '性别',
	id_card CHAR(18) COMMENT '身份证号'
);
-- 成绩表
CREATE TABLE grade(
	id         INT(11) PRIMARY KEY AUTO_INCREMENT, #主键
	english    DOUBLE(4,1) COMMENT '英语成绩',
	grade_time DATE COMMENT '考试时间',
	user_id    INT(11) NOT NULL COMMENT 'users表的主键' #外键
);


-- 添加用户
-- values 可以添加多个值
insert into users(uname,upass,phone,qq) values
("张三","123456",13914231523,123512),
("李四","141242",13914231523,123512),
("王五","612312",13914231523,123512);

-- 添加成绩
-- NOW()函数 为当前时间
-- 带条件的插入语句
insert into grade(english,grade_time,user_id) value (99.5,now(),(
	SELECT id FROM users WHERE uname = "王五"
));
-- 基础
INSERT INTO grade(english,grade_time,user_id) VALUE (92.5,"2022-12-04",1);
INSERT INTO grade(english,grade_time,user_id) VALUE (93.5,"2022-12-04",2);
INSERT INTO grade(english,grade_time,user_id) VALUE (90.5,"2022-12-04",3);


-- 什么时候添加主外键约束
-- dev环境   开发环境  为了方便就没有添加约束（不规范）
-- test环境  测试环境  添加主外键约束
-- prod环境  生产环境  添加主外键约束
-- 添加主外键约束
ALTER TABLE grade ADD CONSTRAINT fk_users_grade FOREIGN KEY(user_id) REFERENCES users(id);
-- 删除主外键约束
ALTER TABLE grade DROP FOREIGN KEY fk_users_grade;

-- 好处就是可以约束外键 避免生成垃圾数据
INSERT INTO grade(english,grade_time,user_id) VALUE (90.5,"2022-12-04",5);
-- 有什么坏处囊
-- 在开发的时候我想删除一个用户就不允许删除，因为成绩表还有数据
DELETE FROM users WHERE id = 3;
delete from grade where user_id = 3;



-- 复杂的查询 （多表联合查询）
-- 在from后面用,隔开 然后让外键列和主键列相等
-- select 要显示的字段 from 表1,表2 where 表1的外键 = 表2的主键
select grade.id,uname,english,grade_time  -- 显示的哪些字段
from users,grade -- 查询的两张表
where grade.user_id = users.id -- 表1的外键=表2的主键 
-- 不加上面这个条件 数据库就会把每一条成绩分给每一个人
and users.id = 3 -- 补充条件 id=3
and english > 95; -- 补充条件 成绩>95分
-- 注意 条件部分 两张表有相同字段需要加上表名，没有相同字段是可省略。

-- 练习时间
INSERT INTO users(uname,upass,phone,qq) VALUES
("张三","123456",13914231523,123512),
("李四","141242",13914231523,123512),
("王五","612312",13914231523,123512);
INSERT INTO grade(english,grade_time,user_id) VALUES
(92.5,"2022-12-04",1),
(20.5,"2022-12-04",2),
(95.5,"2022-12-04",2),
(93.5,"2022-12-03",1),
(80.5,"2022-12-03",3),
(59.5,"2022-12-03",3);
SELECT * FROM users;
SELECT * FROM grade;



-- 练习1
-- 查询考试小于60分的有哪些人 显示姓名和成绩
-- AS 别名（昵称）
-- grade.user_id  =  g.user_id
SELECT u.uname,g.english FROM users AS u,grade AS g 
WHERE u.id = g.user_id
AND g.english < 60;

-- 练习2
-- 查询在 12月4日考试的有哪些人  显示id和姓名
SELECT u.id,u.uname FROM users AS u,grade AS g 
WHERE u.id = g.user_id
AND g.grade_time = "2022-12-04";

-- 练习3
-- 查询 考试成绩在60分以上 80分以下(包含80)有哪些人  显示姓名和成绩
SELECT u.uname,g.english FROM users AS u,grade AS g 
WHERE u.id = g.user_id
AND g.english > 60 
AND g.english <= 80;