<ruby id="bdb3f"></ruby>

    <p id="bdb3f"><cite id="bdb3f"></cite></p>

      <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
        <p id="bdb3f"><cite id="bdb3f"></cite></p>

          <pre id="bdb3f"></pre>
          <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

          <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
          <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

          <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                <ruby id="bdb3f"></ruby>

                ??碼云GVP開源項目 12k star Uniapp+ElementUI 功能強大 支持多語言、二開方便! 廣告
                本文適合將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 分的學生姓名。(表結構如下圖)** ![](https://box.kancloud.cn/2016-03-15_56e77dbde5745.jpg) 答案可以有如下兩種: ~~~ 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不同, 其他都相同的學生冗余信息,表如下:** ![](https://box.kancloud.cn/2016-03-15_56e77dbe0ee66.jpg) 答案: ~~~ 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 ~~~ 這個的執行結果如下: ![](https://box.kancloud.cn/2016-03-15_56e77dbe2ee46.jpg) 如果還不懂就再看看幾次吧。 PS:GROUP BY 語句用于結合合計函數,根據一個或多個列對結果集進行分組。剛剛就是GROUP BY 對多列的使用場景。 **3、行轉列:** 表數據如下: ![](https://box.kancloud.cn/2016-03-15_56e77dbe403fe.jpg) 希望查詢到結果如下: ![](https://box.kancloud.cn/2016-03-15_56e77dbe52629.jpg) 答案: ~~~ 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'); ~~~ 執行后數據如下: ![](https://box.kancloud.cn/2016-03-15_56e77dbe63ed1.jpg) ![](https://box.kancloud.cn/2016-03-15_56e77dbe75ea0.jpg) 顯然副表student中小寶鴿這條數據的calss_id,主表沒有對應的class_id. 執行對應SQL語句就會把小寶鴿這條數據刪除掉了。 * * * 未完待續……….(TODO),邊學習邊寫博客真的很花時間,累并快樂著~~~
                  <ruby id="bdb3f"></ruby>

                  <p id="bdb3f"><cite id="bdb3f"></cite></p>

                    <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
                      <p id="bdb3f"><cite id="bdb3f"></cite></p>

                        <pre id="bdb3f"></pre>
                        <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

                        <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
                        <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

                        <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                              <ruby id="bdb3f"></ruby>

                              哎呀哎呀视频在线观看