数据库02

-- sql基础补充
-- 1.条件语句的写法
-- 在SQL中可以通过 where 条件语句来对操作对象进行筛选 - 筛选
-- a. 比较运算符： =, <>, >, <, >=, <=
-- 注意: 判断一个字段的值是否为空不用使用=和<>, 而是使用is null 和 is not null
SELECT addr FROM t_student;
SELECT stuname FROM t_student WHERE addr is NULL;-- 判断是否为NULL（空）
SELECT stuname FROM t_student WHERE addr=''; -- 判断是否是空串
-- b. 逻辑运算符: and, or, not
-- c. where 字段名 between 值1 and 值2 -- 筛选指定的字段的值在值1和值2之间
SELECT stuname,birth FROM t_student WHERE birth BETWEEN '1990-1-1' AND '1999-12-31';
-- d. where 字段名 in 集合 -- 筛选出字段值是集合中的元素;(集合是使用括号括起来里面多个值)
SELECT * FROM t_student WHERE stuname in ('小花', '小明', '路飞');

-- 2.数据类型
-- varchar(size): 不定长字符串，size决定的是最大长度
-- char(size): 定长字符
-- text: 不限长度(最大是255个字符)
-- int/tinyint(-128~127)
-- FLOAT(size,d)/DOUBLE(size,d) - 这儿的size和d的值都有约束效果
-- bit: 只有0和1两个值
-- date/datetime/time: 值可以是时间函数的结果，也可以时间字符串；计算或者是比较的时候内部是按时间处理的
UPDATE t_student SET tel='1367819230';
ALTER TABLE t_student ADD COLUMN intro VARCHAR(10);
SELECT * FROM t_student;

-- 3.去重
SELECT DISTINCT addr FROM t_student;

-- ========================添加约束=========================
-- 1.创建表的时候添加约束

CREATE TABLE if not EXISTS t_college
(
collid int, 
collname VARCHAR(20) NOT NULL,   -- 创建表的时候添加约束
website VARCHAR(1024), 
intro VARCHAR(200),
PRIMARY KEY(collid)
);

-- 2.通过添加约束索引的方式添加约束
-- a.添加约束
-- alter table 表名 add constraint 索引名 约束 (字段名);
-- 说明: 索引名 - 自己随便命名，用来指向当前添加的约束； 约束 - 需要添加的约束(支持唯一约束、主键约束和外键约束)

ALTER TABLE t_college ADD CONSTRAINT con_website UNIQUE (website);
-- 给t_college表中的website添加unique约束，约束索引为con_website

-- b.删除约束
-- alter table 表名 drop index 约束索引;
ALTER TABLE t_college DROP index con_website;

-- ====================外键和E.R图================
-- 1.什么是外键：表中的某个字段的值是根据其他表中主键的值来确定的。那么这个字段就是外键
-- 1.1 多对一的外键的添加： 将外键添加到多的一方对应的表中
-- 一对一的外键的添加： 将外键随便添加到哪一方，同时添加值唯一约束
-- 多对多的外键的添加： 关系型数据库中，两张表没法实现多多的关系，需要一个中间表。(中间表有两个外键分别参照多多的两个表的主键)
-- 1.2 怎么添加外键：

-- a.添加外键对应的字段
alter TABLE tb_student add COLUMN collid int;

-- b.给设计好的外键对应的字段添加外键约束
-- alter table 表1 add constraint 索引名 foreign key (字段1) references 表2 (字段2);
-- - 将表1中的字段1设置为外键，并且让这个外键的值参照表2中的字段2

ALTER TABLE tb_student ADD CONSTRAINT fk_collid_stu 
FOREIGN KEY (collid) 
REFERENCES  tb_college (collid);

-- c. 删除外键约束
-- alter table 表名 drop foreign key 外键索引名; - 可以删除外键约束，但是外键索引还存在；需要额外的把索引删掉
-- 注意: 删除外键约束的时候直接删除约束的索引无效，必须先将约束删掉，然后再删除索引.
ALTER TABLE tb_student DROP FOREIGN KEY fk_collid_stu;
ALTER TABLE tb_student DROP INDEX fk_collid_stu;

-- d. 多对多的外键约束
-- 添加学生的外键约束

ALTER TABLE tb_score ADD CONSTRAINT fk_stuid_score 
FOREIGN KEY (stuid) 
REFERENCES tb_student (stuid);

-- 添加课程的外键约束

ALTER TABLE tb_score ADD CONSTRAINT fk_couid_score 
FOREIGN KEY (couid) 
REFERENCES tb_course (couid);

-- ==================查询的高级操作=================
-- 1.聚合：max()/min()/sum()/avg()/count()
-- SELECT 聚合函数(字段) FROM 表名 WHERE 条件; -- 按条件多表查询指定字段数据，然后将查询结果做相应的聚合运算。聚合运算的结果是最后结果

SELECT mark FROM tb_score;    -- 获取tb_score表中的所有分数值
SELECT max(mark) as maxmark FROM tb_score;      -- 获取tb_score表中所有分数的最大值
SELECT min(mark) FROM tb_score; 
SELECT sum(mark) FROM tb_score; 
SELECT avg(mark) FROM tb_score WHERE scoreid>=123;   -- 计算平均值的时候如果参与运算的对象的值为NULL,那么这个数据不会参与计算
SELECT count(mark) FROM tb_score WHERE mark>=90;

-- 2.分组
-- SELECT 字段操作 FROM 表名 WHERE 条件 GROUP BY(字段2);
-- 将指定表中满足条件的记录按照字段2的进行分组(值是一样的在一个组里面), 然后再讲每个分组作为整体按照指定字段进行指定聚合操作
-- 求每个学生的平均成绩
SELECT stuid,avg(mark) FROM tb_score WHERE scoreid <= 115 GROUP BY(stuid) ;
-- 注意:a.字段操作的位置除了分组字段不用聚合，其他字段都必须聚合 b.分组的时候where要放到分组前对需要分组的数据进行筛选

-- having: 分组的时候，在分组后用having代替where来对分组后的数据进行筛选
-- 获取平均分数大于60分的学生的id
SELECT stuid,avg(mark) FROM tb_score GROUP BY(stuid) HAVING avg(mark)>60;

-- 3.子查询: 将一个查询操作的结果作为另外一个查询的数据源
-- 在tb_score表中获取成绩是大于90分的学生的id
SELECT stuid FROM tb_score WHERE mark>90 and stuid is not NULL;

-- 获取成绩大于90分的学生的名字
SELECT stuname FROM tb_student WHERE stuid in
(SELECT stuid FROM tb_score WHERE mark>90 and stuid is not NULL);

SELECT stuid,mark FROM tb_score WHERE mark > 90 and stuid is not NULL;
-- 将一个查询的结果作为查询对象提供给另外一个查询。但是第一个查询结果需要重命名
SELECT mark FROM (SELECT stuid,mark FROM tb_score WHERE mark > 90 and stuid is not NULL) as t2;

-- =============1.学生表===============
CREATE TABLE IF NOT EXISTS tb_student
(
stuid int not NULL auto_increment,
stuname VARCHAR(20) not NULL,
tel CHAR(11) not NULL,
birth date DEFAULT '2019-10-21',
addr VARCHAR(100),
PRIMARY key(stuid)
);

-- 添加学院的外键约束
ALTER TABLE tb_student ADD CONSTRAINT fk_collid_stu
FOREIGN KEY (collid)
REFERENCES tb_college (collid);

INSERT INTO tb_student (stuname, addr) VALUES
('王大锤', '北京'),
('张怀义', '江西'),
('张楚楠', '四川'),
('罗玉凤', '重庆'),
('王祖贤', '香港'),
('刘德华', '香港'),
('周杰伦', '台湾');

-- ===============2.课程表================
CREATE TABLE IF NOT EXISTS tb_course
(
couid int NOT NULL auto_increment,
couname VARCHAR(20) NOT NULL,
startdate date NOT NULL DEFAULT '2019-10-21',
intro VARCHAR(200),
credit int NOT NULL,
PRIMARY key(couid)
);

-- 添加老师的外键约束
ALTER TABLE tb_course ADD COLUMN teaid int COMMENT '老师外键字段';
ALTER TABLE tb_course ADD CONSTRAINT fk_teaid_course
FOREIGN KEY (teaid)
REFERENCES tb_teacher (teaid);

-- =================3.学院表====================
CREATE TABLE IF NOT EXISTS tb_college
(
collid int auto_increment,
website VARCHAR(200) UNIQUE,
collname VARCHAR(50),
PRIMARY KEY(collid)
);

-- =================4.老师表====================
CREATE TABLE IF NOT EXISTS tb_teacher
(
teaid int NOT NULL auto_increment,
teaname VARCHAR(20) NOT NULL,
teaage INT,
tel char(11),
PRIMARY KEY(teaid)
);

-- 添加学院的外键约束
ALTER TABLE tb_teacher ADD COLUMN collid int COMMENT '学院的外键';
ALTER TABLE tb_teacher ADD CONSTRAINT fk_collid_teacher
FOREIGN KEY (collid)
REFERENCES tb_college (collid);

-- ================分数表===================
CREATE TABLE if NOT EXISTS tb_score
(
scoreid int NOT NULL auto_increment,
mark FLOAT(4,1) COMMENT '分数',
stuid int COMMENT '学生外键',
couid int COMMENT '课程外键',
PRIMARY KEY (scoreid)
);

INSERT INTO tb_score (mark) VALUES (89),(45.5),(100),(95),(60),(77),(90),
(97),(49),(66),(56),(71),(80.5),(33),(87),(56),(74),(68),(80),(92),(34),(75);