-- 用户表
CREATE TABLE users(
	id      INT(10) PRIMARY KEY AUTO_INCREMENT COMMENT 'id',
	uname   VARCHAR(20) NOT NULL DEFAULT '默认用户' COMMENT '昵称', 
	upass   VARCHAR(30) NOT NULL COMMENT '密码',
	phone   BIGINT(11)  NOT NULL COMMENT '电话号码',
	qq      INT(10) COMMENT 'qq号码',
	id_card CHAR(18) COMMENT '身份证号',
	sex     INT(1) NOT NULL DEFAULT 0 COMMENT '0代表女 1代表男'
);
-- 成绩表
CREATE TABLE grade(
	id INT(10) PRIMARY KEY AUTO_INCREMENT COMMENT 'id', -- 主键
	english DOUBLE(4,1) COMMENT '分数',
	grade_time DATE COMMENT '考试时间',
	user_id INT(11) COMMENT '用来放用户表的主键' -- 外键
);


-- 主外键约束 防止垃圾数据的产生
-- ALTER TABLE 外键表 ADD CONSTRAINT 约束名称 FOREIGN KEY(外键列) REFERENCES 主键表(主键列);
ALTER TABLE grade ADD CONSTRAINT fk_users_grade FOREIGN KEY(user_id) REFERENCES users(id);

-- 添加用户
INSERT INTO users(uname,upass,phone) VALUEs
("张三","1123321",13916134321),
("李四","123321",13914234321),
("王五","1123321",13916134321);

-- 添加成绩
-- 有约束过后 会在主键表里面看主键存不存在 存在才添加数据。
INSERT INTO grade(english,grade_time,user_id) VALUEs
(60.0,"2022-10-22",1),
(60.0,"2022-10-22",2),
(80.0,"2022-10-21",2),
(70.0,"2022-10-21",3);




-- 查询成绩时把这个人的信息一起查询了
-- 复杂查询（多表联查）
-- 不加条件（3*3=9个数据） 会把 每个成绩和每个用户组合起来
-- 如果字段有重复需要加上表名，否则可省略
select grade.id,uname,english,grade_time -- 查询的字段 id 姓名 成绩 考试时间
from users,grade  -- 要查询的表
where user_id = users.id -- 条件
-- 看70分以下的人有哪些（包括70分）
and english <= 70 
-- 22号的数据
and grade_time = "2022-10-22";

-- Where后面加添加条件  AND后面补充条件  条件越多查询越精准。


-- 查询语句
SELECT * FROM users;
SELECT * FROM grade;

-- 删除语句
delete from users where id = 2;
-- 主外键约束
-- 在添加数据时约束外键表
-- 在删除数据时约束主键表
delete from grade where user_id = 2;



-- 什么时候添加约束？
-- dev环境   开发环境   可以不加约束（不规范）
-- test环境  测试环境   添加约束
-- prod环境  生产环境   添加约束


-- 练习时间
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;