MYSQL第二次作业

-- 第一题
CREATE TABLE BBC(
	NAME VARBINARY(100) , -- 国家名
	region VARBINARY(100), -- 地区
	AREA NUMERIC(5), -- 面积
	population NUMERIC(20), -- 人口
	gdp NUMERIC(20) -- GDP
);
-- 查询
SELECT * FROM BBC;
-- 增加
INSERT INTO BBC VALUES('中国','亚洲',960,120000,1002800);
INSERT INTO BBC VALUES('美国','北美洲',937,30000,1752800);
INSERT INTO BBC VALUES('日本','亚洲',37,12500,484600);
INSERT INTO BBC VALUES('德国','欧洲',35,8164,379400);
INSERT INTO BBC VALUES('英国','欧洲',24,5880,282800);
INSERT INTO BBC VALUES('巴西','南美洲',854,16000,22160);
-- 查人口>=2亿的国名和人均GDP                       改属性名
SELECT t.`name`,t.`gdp`*1000000/(t.`population`*10000) AS 人均GDP FROM  BBC t WHERE t.`population`*10000>=200000000;
-- 查属于欧洲的国名和人数
SELECT t.`name`,t.`population` AS 人数（万） FROM BBC t WHERE t.`region`='欧洲';
-- 查
SELECT t.`name`,t.`area`,t.`population` AS 人数（万） FROM BBC t WHERE t.`name`='中国' OR t.`name`='美国' OR t.`name`='日本';
SELECT t.`name` FROM BBC t WHERE t.`name` LIKE '%国%';
SELECT t.`name` FROM BBC t ;
SELECT * FROM BBC t ORDER BY t.`population` DESC;
-- 改
UPDATE BBC t SET t.`region`='火星' WHERE t.`name`='日本';
-- 删除
DELETE FROM BBC WHERE NAME='日本';
DELETE FROM BBC WHERE NAME='德国';
DELETE FROM BBC WHERE NAME='英国';
DELETE FROM BBC WHERE NAME='美国';

-- 第二题
CREATE TABLE nobel(
	ID INT PRIMARY KEY AUTO_INCREMENT, -- 主键
	yr INT , -- 年份
	SUBJECT VARCHAR(100), -- 奖项
	winner VARCHAR(100) -- 得奖者
);
-- 查询
SELECT * FROM nobel;
-- 增
INSERT INTO nobel VALUES(NULL,2000,'java','张三');
INSERT INTO nobel VALUES(NULL,2001,'c++','李四');
INSERT INTO nobel VALUES(NULL,2002,'c','王五');
INSERT INTO nobel VALUES(NULL,2002,'java','赵六');
INSERT INTO nobel VALUES(NULL,2002,'c','刘八');
INSERT INTO nobel VALUES(NULL,2002,'c++','罗九');
INSERT INTO nobel VALUES(NULL,2003,'java','朱十');
INSERT INTO nobel VALUES(NULL,2004,'足球','陈十一');
INSERT INTO nobel VALUES(NULL,2005,'篮球','唐十二');
INSERT INTO nobel VALUES(NULL,2006,'乒乓球','隔壁老王');

-- 查
SELECT * FROM nobel t WHERE t.`yr`=2000 AND t.`subject`='c';
SELECT * FROM nobel t WHERE t.`yr`=2000;
SELECT t.`yr`,t.`winner` FROM nobel t WHERE t.`subject`='足球';
SELECT * FROM nobel t WHERE t.`yr`=2000 AND t.`subject`='java';
SELECT * FROM nobel t WHERE t.`yr`>=2001 AND t.`yr`<=2006 AND t.`subject`='java';
SELECT  t.`winner`,t.`subject`,t.`yr` FROM nobel t WHERE t.`winner` LIKE '%八%';
-- 去重复
 
INSERT INTO nobel VALUES(NULL,2006,'不睡觉','凯杰哥哥');
UPDATE nobel t SET t.`subject`='LOL' ,t.`winner`='杰哥' WHERE t.`yr`=2006 ;
-- 倒序排列
SELECT * FROM nobel t ORDER BY t.`yr` DESC ;