SQL實(shí)例整理

1、用一條SQL語(yǔ)句 查詢出每門課都大于80 分的學(xué)生姓名。（表結(jié)構(gòu)如下圖）

答案可以有如下兩種：
select distinct student_name from table_test_one where student_name not in   
(select distinct student_name from table_test_one where score<=80);
或者
select student_name from table_test_one group by student_name having min(score)>80;
第二種方法是group by 、min函數(shù) 結(jié)合 having的使用，w3school教程里面也提到過(guò)（在 SQL 中增加 HAVING 子句原因是，WHERE 關(guān)鍵字無(wú)法與合計(jì)函數(shù)一起使用）
似乎看懂了，但是還是沒(méi)有自己運(yùn)行一遍深刻?。?！自己能動(dòng)手敲一遍就更好了！
下面我們自己造數(shù)據(jù)，后面的例子也會(huì)用到。
建表然后倒入初始數(shù)據(jù)：
DROP TABLE IF EXISTS `table_test_one`;   
CREATE TABLE `table_test_one` (   
  `id` int(11) NOT NULL AUTO_INCREMENT,   
  `student_no` varchar(10) NOT NULL,   
  `student_name` varchar(10) NOT NULL,   
  `subject_no` varchar(10) NOT NULL,   
  `subject_name` varchar(10) NOT NULL,   
  `score` int(11) NOT NULL,   
  PRIMARY KEY (`id`)   
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
INSERT INTO `table_test_one` VALUES ('1', '201601', '張三', '0001', '數(shù)學(xué)', '98');      
INSERT INTO `table_test_one` VALUES ('2', '201601', '張三', '0002', '語(yǔ)文', '66');      
INSERT INTO `table_test_one` VALUES ('3', '201602', '李四', '0001', '數(shù)學(xué)', '60');      
INSERT INTO `table_test_one` VALUES ('4', '201602', '李四', '0003', '英語(yǔ)', '78');      
INSERT INTO `table_test_one` VALUES ('5', '201603', '王五', '0001', '數(shù)學(xué)', '99');     
 INSERT INTO `table_test_one` VALUES ('6', '201603', '王五', '0002', '語(yǔ)文', '99');      
INSERT INTO `table_test_one` VALUES ('7', '201603', '王五', '0003', '英語(yǔ)', '98');
可以運(yùn)行一下上面兩個(gè)語(yǔ)句試試結(jié)果是不是你想要的。
2、刪除除了id不同, 其他都相同的學(xué)生冗余信息，表如下：

答案：
delete table_test_one where id not in    
(select min(id) from table_test_one group by    
student_no, student_name, subject_no, subject_name, score);
是否有看懂？如果沒(méi)能看懂的話，繼續(xù)往下看：
先來(lái)造數(shù)據(jù)，題1中的數(shù)據(jù)只需要執(zhí)行如下SQL就變成題2中的數(shù)據(jù)了：
update table_test_one set subject_no = '0001', subject_name = '數(shù)學(xué)' where id = 6;
然后我們先執(zhí)行這個(gè)看看：
select min(id) from table_test_one group by    
student_no, student_name, subject_no, subject_name, score
這個(gè)的執(zhí)行結(jié)果如下：

如果還不懂就再看看幾次吧。
PS：GROUP BY 語(yǔ)句用于結(jié)合合計(jì)函數(shù)，根據(jù)一個(gè)或多個(gè)列對(duì)結(jié)果集進(jìn)行分組。剛剛就是GROUP BY 對(duì)多列的使用場(chǎng)景。
3、行轉(zhuǎn)列：
表數(shù)據(jù)如下：

希望查詢到結(jié)果如下：

答案：
select year,   
(select amount from table_test_two t where t.month = 1 and t.year = table_test_two.year) as month1,   
(select amount from table_test_two t where t.month = 2 and t.year = table_test_two.year) as month2,   
(select amount from table_test_two t where t.month = 3 and t.year = table_test_two.year) as month3   
from table_test_two group by year;

利用group by 實(shí)現(xiàn)行轉(zhuǎn)列，這種場(chǎng)景在數(shù)據(jù)統(tǒng)計(jì)的時(shí)候經(jīng)常用到。
猿友可以造數(shù)據(jù)自己運(yùn)行試試：
-- ----------------------------   -- Table structure for `table_test_two`   -- ----------------------------   

DROP TABLE IF EXISTS `table_test_two`;   
CREATE TABLE `table_test_two` (   
  `year` int(11) NOT NULL,   
  `month` int(11) NOT NULL,   
  `amount` decimal(10,1) NOT NULL,   
  PRIMARY KEY (`year`,`month`,`amount`)   
) ENGINE=InnoDB DEFAULT CHARSET=latin1;   
-- ----------------------------   
-- Records of table_test_two   -
- ----------------------------   
INSERT INTO `table_test_two` VALUES ('1991', '1', '1.1');   
INSERT INTO `table_test_two` VALUES ('1991', '2', '1.2');   
INSERT INTO `table_test_two` VALUES ('1991', '3', '1.3');   
INSERT INTO `table_test_two` VALUES ('1992', '1', '2.1');   
INSERT INTO `table_test_two` VALUES ('1992', '2', '2.2');  
 INSERT INTO `table_test_two` VALUES ('1992', '3', '2.3');
4、復(fù)制表( 只復(fù)制結(jié)構(gòu), 源表名：table_test_two 新表名：table_test_three)
答案：
create table table_test_three as    
select * from table_test_two where 1=2;
PS：如果需要將數(shù)據(jù)也復(fù)制過(guò)去，則上面改成where 1=1
5、復(fù)制表數(shù)據(jù)（將表 table_test_two 的數(shù)據(jù)復(fù)制到表table_test_three 里面）
答案：
insert into table_test_three (year,month,amount)    select year,month,amount from table_test_two;
6、兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒(méi)有的信息
答案：
delete from table_test_student where not exists    
(select * from table_test_class where table_test_student.class_id = table_test_class.calss_id);
我們先造點(diǎn)數(shù)據(jù)吧：
-- ----------------------------   
-- Table structure for `table_test_class`   
-- ----------------------------   
DROP TABLE IF EXISTS `table_test_class`;   
CREATE TABLE `table_test_class` (   
  `calss_id` int(11) NOT NULL AUTO_INCREMENT,   
  `calss_name` varchar(10) CHARACTER SET utf8 NOT NULL,   
  PRIMARY KEY (`calss_id`)   
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;   
-- ----------------------------   
-- Records of table_test_class   
-- ----------------------------   
INSERT INTO `table_test_class` VALUES ('1', '一班');

-- ----------------------------   
-- Table structure for `table_test_student`   
-- ----------------------------   
DROP TABLE IF EXISTS `table_test_student`;   
CREATE TABLE `table_test_student` (   
  `student_id` int(11) NOT NULL AUTO_INCREMENT,   
  `student_name` varchar(10) CHARACTER SET utf8 NOT NULL,   
  `class_id` int(11) NOT NULL,   
  PRIMARY KEY (`student_id`)   
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;   
-- ----------------------------   
-- Records of table_test_student   
-- ----------------------------   
INSERT INTO `table_test_student` VALUES ('1', '羅國(guó)輝', '1');   
INSERT INTO `table_test_student` VALUES ('2', '小寶鴿', '2');
執(zhí)行后數(shù)據(jù)如下：

顯然副表student中小寶鴿這條數(shù)據(jù)的calss_id，主表沒(méi)有對(duì)應(yīng)的class_id.
執(zhí)行對(duì)應(yīng)SQL語(yǔ)句就會(huì)把小寶鴿這條數(shù)據(jù)刪除掉了。