本文適合將w3school的SQL教程([http://www.w3school.com.cn/sql/sql_create_table.asp](http://www.w3school.com.cn/sql/sql_create_table.asp))都基本看過一遍的猿友閱讀。
說說博主的情況吧。畢業找工作之前確實有大概看過w3school的SQL教程,然后參加校園招聘,每次遇到一些SQL筆試題,立馬懵逼了(大寫的)。其實我那時候大概知道怎么寫的,只是總是寫不正確,或者是對一些特定的而且沒有見過的場景的SQL語句,根本寫不出來。相信不少猿友工作之后,其實挺多都用得不熟吧(如果白板編寫的話)。
因為大部分Java猿友工作做的事情,其實比較少情況自己去動手寫特定場景的SQL(可能有也是百度,接觸過一個會一個),簡單SQL也是直接由框架(hibernate和Mybatis)提供接口。當然,那種專門做后臺,經常跟數據打交道的Java猿友除外,因此只能說大部分。
如果還是繼續保持這樣的狀態的話,下次自己找工作遇到SQL筆試題,估計也會繼續懵逼(大寫的)。
下面小寶鴿整理了一些實例(實例主要來自網上),以提升自己寫SQL的某些關鍵字的理解。
**1、用一條SQL 語句 查詢出每門課都大于80 分的學生姓名。(表結構如下圖)**

答案可以有如下兩種:
~~~
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函數 結合 having的使用,w3school教程里面也提到過(在 SQL 中增加 HAVING 子句原因是,WHERE 關鍵字無法與合計函數一起使用)
似乎看懂了,但是還是沒有自己運行一遍深刻!!!自己能動手敲一遍就更好了!
下面我們自己造數據,后面的例子也會用到。
建表然后倒入初始數據:
~~~
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', '數學', '98');
INSERT INTO `table_test_one` VALUES ('2', '201601', '張三', '0002', '語文', '66');
INSERT INTO `table_test_one` VALUES ('3', '201602', '李四', '0001', '數學', '60');
INSERT INTO `table_test_one` VALUES ('4', '201602', '李四', '0003', '英語', '78');
INSERT INTO `table_test_one` VALUES ('5', '201603', '王五', '0001', '數學', '99');
INSERT INTO `table_test_one` VALUES ('6', '201603', '王五', '0002', '語文', '99');
INSERT INTO `table_test_one` VALUES ('7', '201603', '王五', '0003', '英語', '98');
~~~
可以運行一下上面兩個語句試試結果是不是你想要的。
**2、刪除除了id不同, 其他都相同的學生冗余信息,表如下:**

答案:
~~~
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);
~~~
是否有看懂?如果沒能看懂的話,繼續往下看:
先來造數據,題1中的數據只需要執行如下SQL就變成題2中的數據了:
~~~
update table_test_one set subject_no = '0001', subject_name = '數學' where id = 6;
~~~
然后我們先執行這個看看:
~~~
select min(id) from table_test_one group by
student_no, student_name, subject_no, subject_name, score
~~~
這個的執行結果如下:

如果還不懂就再看看幾次吧。
PS:GROUP BY 語句用于結合合計函數,根據一個或多個列對結果集進行分組。剛剛就是GROUP BY 對多列的使用場景。
**3、行轉列:**
表數據如下:

希望查詢到結果如下:

答案:
~~~
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 實現行轉列,這種場景在數據統計的時候經常用到。
猿友可以造數據自己運行試試:
~~~
-- ----------------------------
-- 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、復制表( 只復制結構, 源表名:table_test_two 新表名:table_test_three)**
答案:
~~~
create table table_test_three as
select * from table_test_two where 1=2;
~~~
PS:如果需要將數據也復制過去,則上面改成where 1=1
**5、復制表數據(將表 table_test_two 的數據復制到表table_test_three 里面)**
答案:
~~~
insert into table_test_three (year,month,amount)
select year,month,amount from table_test_two;
~~~
**6、兩張關聯表,刪除主表中已經在副表中沒有的信息**
答案:
~~~
delete from table_test_student where not exists
(select * from table_test_class where table_test_student.class_id = table_test_class.calss_id);
~~~
我們先造點數據吧:
~~~
-- ----------------------------
-- 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', '羅國輝', '1');
INSERT INTO `table_test_student` VALUES ('2', '小寶鴿', '2');
~~~
執行后數據如下:


顯然副表student中小寶鴿這條數據的calss_id,主表沒有對應的class_id.
執行對應SQL語句就會把小寶鴿這條數據刪除掉了。
* * *
未完待續……….(TODO),邊學習邊寫博客真的很花時間,累并快樂著~~~
- 前言
- Java生成中間logo的二維碼(還可以加上二維碼名稱哦)
- Java我的高效編程之常用函數
- AES加密解密&&SHA1、SHA加密&&MD5加密
- Java中synchronized的使用實例
- Java基礎之集合
- Java基礎之泛型
- Java基礎之枚舉妙用
- 那些年用過的一些前端框架
- 關于正則,那些年一直存在的疑惑解答(正則菜鳥不容錯過)
- 給pdf文件添加防偽水印logo(附工程源碼下載)
- SpringMVC+BUI實現文件上傳(附詳解,源碼下載)
- Java異常封裝(自己定義錯誤碼和描述,附源碼)
- javaweb異常提示信息統一處理(使用springmvc,附源碼)
- 關于Java,那些我心存疑惑的事(不斷更新中...)
- 深入Java虛擬機(1)——Java體系結構
- 深入Java虛擬機(2)——Java的平臺無關性
- 深入Java虛擬機(3)——安全
- 深入Java虛擬機(4)——網絡移動性
- Linux文件編輯命令詳細整理
- 阿里云服務器云數據庫免費體驗(Java Web詳細實例)
- 項目部署、配置、查錯常用到的Linux命令
- Shell腳本了解
- Ajax原理學習
- linux下安裝apache(httpd-2.4.3版本)各種坑
- JSP九大內置對象
- Servlet再度學習
- 開發人員系統功能設計常用辦公軟件分享
- java.lang.ClassNotFoundException:org.springframework.web.context.ContextLoaderListener問題解決
- tomcat內存溢出解決,java.lang.OutOfMemoryError: PermGen space
- 《Java多線程編程核心技術》推薦
- 關于跳槽,是我心浮氣躁?還是我確實該離開了?
- Java I/O學習(附實例和詳解)
- Java經典設計模式之五大創建型模式(附實例和詳解)
- Java經典設計模式之七大結構型模式(附實例和詳解)
- Java經典設計模式之十一種行為型模式(附實例和詳解)
- Java內存管理
- SQL實例整理
- 數據庫面試常問的一些基本概念