[大数据]mysql

CREATE TABLE `classes` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`cid`)
);

CREATE TABLE `db_12_01`.`students` (
  `sid` INT NOT NULL AUTO_INCREMENT,
  `cid` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`sid`),
  INDEX `classes_cid_idx` (`cid` ASC),
  CONSTRAINT `classes_cid`
    FOREIGN KEY (`cid`)
    REFERENCES `db_12_01`.`classes` (`cid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
    
-- 插入测试数据
insert into classes (name) values ('冲刺班');
insert into students (cid, name) values 
	(1, '小A'),
    (1, '小B'),
    (1, '小C');
    
-- 尝试删除班级表中的记录
-- 09:16:05	delete from classes where cid = 1	
-- Error Code: 1451. 
-- Cannot delete or update a parent row: a foreign key constraint fails (`db_12_01`.`students`, CONSTRAINT `classes_cid` FOREIGN KEY (`cid`) REFERENCES `classes` (`cid`) ON DELETE NO ACTION ON UPDATE NO ACTION)	0.000 sec
delete from classes where cid = 1;

-- 尝试 drop 班级表
-- 09:17:15	DROP TABLE `db_12_01`.`classes`	
-- Error Code: 1217. 
-- Cannot delete or update a parent row: a foreign key constraint fails	0.000 sec
drop table classess;

-- NO ACTION 在 MySQL 下和 RESTRICT 同义
-- SET NULL
-- 要求 学生表中的 cid 字段允许 NULL
CREATE TABLE `db_12_01`.`students` (
  `sid` INT NOT NULL AUTO_INCREMENT,
  `cid` INT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`sid`),
  INDEX `cs_idx` (`cid` ASC),
  CONSTRAINT `cs`
    FOREIGN KEY (`cid`)
    REFERENCES `db_12_01`.`classes` (`cid`)
    ON DELETE SET NULL
    ON UPDATE SET NULL);
    
-- CASCADE 下落
-- 删除班级记录，下落（向下跟着）把学生记录也删除

CREATE TABLE `db_12_01`.`students` (
  `sid` INT NOT NULL AUTO_INCREMENT,
  `cid` INT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`sid`),
  INDEX `cs_idx` (`cid` ASC),
  CONSTRAINT `cs`
    FOREIGN KEY (`cid`)
    REFERENCES `db_12_01`.`classes` (`cid`)
    ON DELETE CASCADE
    ON UPDATE CASCADE);

-- 聚合查询
CREATE TABLE `db_12_01`.`records` (
  `rid` INT NOT NULL AUTO_INCREMENT,
  `book` VARCHAR(45) NOT NULL,
  `borrowed_at` DATE NOT NULL,
  PRIMARY KEY (`rid`));
  
insert into records (book, borrowed_at) values
	('红楼梦', '2021-10-13'),
    ('红楼梦', '2021-10-14'),
    ('西游记', '2021-10-14'),
    ('水浒传', '2021-10-14'),
    ('红楼梦', '2021-10-14'),
    ('西游记', '2021-10-15'),
    ('水浒传', '2021-10-16'),
    ('水浒传', '2021-10-16'),
    ('水浒传', '2021-10-16'),
    ('水浒传', '2021-10-16'),
    ('水浒传', '2021-10-16'),
    ('西游记', '2021-10-17');
    
select book, count(*) from records where borrowed_at = '2021-10-13' group by book;
select book, count(*) from records where borrowed_at = '2021-10-14' group by book;
select book, count(*) from records where borrowed_at = '2021-10-15' group by book;
select book, count(*) from records where borrowed_at = '2021-10-16' group by book;
select book, count(*) from records where borrowed_at = '2021-10-17' group by book;

-- 练习课件内容
-- emp : employee
create table emp (
	id int primary key auto_increment,
    name varchar(45) not null,
    role varchar(45) not null,
    salary numeric(11, 2)
);

insert into emp(name, role, salary) values
	('马云','服务员', 1000.20),
	('马化腾','游戏陪玩', 2000.99),
	('孙悟空','游戏角色', 999.11),
	('猪无能','游戏角色', 333.5),
	('沙和尚','游戏角色', 700.33),
	('隔壁老王','董事长', 12000.66);
    
select role, max(salary) max_salary from emp group by role order by max_salary desc;
select role, min(salary) min_salary from emp group by role order by min_salary desc;
select role, avg(salary) avg_salary from emp group by role order by avg_salary desc;
select role, sum(salary) sum_salary from emp group by role order by sum_salary desc;


select role, avg(salary) as avg_salary 
from emp 
group by role 
having avg_salary >= 1500 
order by avg_salary desc
limit 1;

-- 联表查询（多表查询）
CREATE TABLE `classes` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`cid`)
);

insert into classes values
	(101, '葵花'),
    (102, '牡丹'),
    (103, '莲花'),
    (104, '兰花');

CREATE TABLE `db_12_01`.`students` (
  `sid` INT NOT NULL AUTO_INCREMENT,
  `cid` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`sid`));
  
insert into students values
	(1, 101, '小A'),
	(2, 101, '小B'),
	(3, 102, '小C'),
	(4, 102, '小D'),
	(5, 103, '小E'),
	(6, 104, '小F'),
	(7, 104, '小G');
    
-- 观察结果是笛卡尔积
select count(*) from classes, students;
select * from classes, students;
-- 在字段有歧义时，必须使用字段的全名称
-- 表名.列名         classes.cid 以及 students.cid
select * from classes, students where classes.cid = students.cid;
select classes.cid, classes.name, sid, students.name from classes, students where classes.cid = students.cid;
-- 直接给表名起别名，跟着 from 子句后 使用 as ...     from students as s；同理 as 可以省略   from students s
select c.cid, c.name, sid, s.name from classes c, students s where c.cid = s.cid;

-- 多表查询的另一种写法
select c.cid, c.name, sid, s.name from classes c join students s on c.cid = s.cid;

select c.cid, c.name, sid, s.name from classes c, students s where c.cid = s.cid and c.name in ('葵花', '牡丹');
select c.cid, c.name, sid, s.name from classes c join students s on c.cid = s.cid where c.name in ('葵花', '牡丹');

-- 自己和自己联合，强制必须至少给其中一个起别名
select count(*) from classes c, classes t;
select * from classes c, classes t;


create table students_with_leader (
	sid int primary key,
    name varchar(45) not null,
    leader_id int not null
);

insert into students_with_leader values
	(1, '小A', 1),
	(2, '小B', 1),
	(3, '小C', 1),
	(4, '小D', 4),
	(5, '小E', 4),
	(6, '小F', 6),
	(7, '小G', 7);
    
select s.name, b.name from students_with_leader s join students_with_leader b on s.leader_id = b.sid;

-- 演示 内联、左联、右联的现象
select * from classes c inner join students s on c.cid = s.cid;
select * from classes c join students s on c.cid = s.cid;
select * from classes c, students s where c.cid = s.cid; 

-- 左外联：对应不了的记录，全部用 null 填充
select * from classes c left outer join students s on c.cid = s.cid;
select * from classes c left join students s on c.cid = s.cid;

-- 右外联：对应不了的记录，全部用 null 填充
select * from classes c right outer join students s on c.cid = s.cid;
select * from classes c right join students s on c.cid = s.cid;