数据库的一些基本语法二

-- 给出诺贝尔奖获奖总人数
SELECT COUNT(DISTINCT winner) FROM nobel;

-- 给出诺贝尔物理奖获奖次数
SELECT COUNT(SUBJECT) FROM nobel WHERE SUBJECT='Physics';

-- 显示每个奖项获奖总次数
SELECT SUBJECT,COUNT(*) FROM nobel GROUP BY SUBJECT;
 
-- 显示每个奖项第一次获得该奖的年份
SELECT SUBJECT,yr FROM nobel GROUP BY SUBJECT ORDER BY yr;
SELECT SUBJECT,MIN(yr)FROM nobel GROUP BY SUBJECT;


-- 显示每个奖项在2000年获奖的人数
SELECT SUBJECT,COUNT(CASE WHEN yr>='2000-01-01' AND yr<='2000-12-31'THEN 1 END)AS '总计' FROM nobel GROUP BY SUBJECT ;
SELECT SUBJECT,COUNT(SUBJECT) FROM nobel WHERE yr=2000 GROUP BY SUBJECT;


-- 显示每个奖项不同获奖者人数
SELECT SUBJECT,COUNT(DISTINCT winner) FROM nobel GROUP BY SUBJECT;


-- 显示每个奖项有多少年获奖
SELECT COUNT(DISTINCT yr),SUBJECT FROM nobel GROUP BY SUBJECT;

-- 显示当年有三个物理奖的年份
SELECT yr FROM nobel GROUP BY yr HAVING COUNT(CASE WHEN SUBJECT='Physics'THEN 1 END)>=3;
SELECT yr FROM nobel WHERE SUBJECT='Physics' GROUP BY yr HAVING COUNT(SUBJECT)=3;


-- 显示得奖超过一次的获奖者
SELECT winner,COUNT(*) FROM nobel GROUP BY winner HAVING COUNT(*)>1; 

-- 显示得到多个奖项的获奖者
SELECT winner FROM nobel GROUP BY winner HAVING COUNT(DISTINCT SUBJECT)>1;

-- 显示2000年及以后，有三个人获得同一奖项的年份及奖项 GROUP BY yr,SUBJECT 指的是多分组
SELECT yr,SUBJECT FROM nobel WHERE yr>=2000 GROUP BY yr,SUBJECT HAVING COUNT(DISTINCT winner)=3;


-- not null 表示非空，该列必须有值不能为空
 -- default 表示不输入值的时候 默认为该值
DROP TABLE t_man;
CREATE TABLE t_man(
	id INT PRIMARY KEY AUTO_INCREMENT,
	manName VARCHAR(20) NOT NULL,
	gender VARCHAR(10)DEFAULT'男'
);
INSERT INTO t_man (manName,gender) VALUES('张三','男');

INSERT INTO t_man (manName,gender) VALUES('张三','男');

DROP TABLE t_room;
CREATE TABLE t_room(
	id INT PRIMARY KEY AUTO_INCREMENT,
	address VARCHAR(50),
	price INT,
	manID INT
);
-- 为房间添加外键约束
ALTER TABLE t_room ADD CONSTRAINT fk_1 FOREIGN KEY(manID) REFERENCES t_man(id);
INSERT INTO t_room (address,price,manID)VALUES ('人民南路',200000,1);
INSERT INTO t_room (address,price,manID)VALUES ('人民南路',200000,2);

-- 要删除主表的键，必须先删除从表和主表级联的值
DELETE FROM t_room WHERE manID=1;
DELETE FROM t_man WHERE id=1;

-- 删除主表的键，另一种方法是把从表的外键值设置为空
UPDATE t_room SET manID =NULL WHERE manID=2;
DELETE FROM t_man WHERE id =2;

-- 两个表之间如果存在外键约束，删除主表记录的时候，由于有外键约束存在，无法删除又从表记录引用的主表记录
-- 删除方式一：级联删除，讲主表记录对应的从表记录先做删除，在删除主表记录
-- 删除方式二：外键设空，讲主表记录的对应的从表记录的外键设置为空，在删除主表记录

-- 外键和外键约束
-- 没有建立外键约束不等于没有外键
-- 外键是值从表的某列和主表的某列存在依附关系
-- 外键约束是指在外键关联的主键上强制加上一个约束，如果违反该约束，则不允许该条数据的修改