Mysql基礎教程 mysql進階

Mysql內連接查詢



什么是連接查詢?


連接查詢是將兩個或兩個以上的表按某個條件連接起來,從中選取需要的數據。連接查詢是同時查詢兩個或兩個以上的表時使用的。當不同的表中存在相同意義的字段時,可以通過該字段連接這幾個表。連接查詢就是找到表與表之間關系,或者找到表與表之間的連接的橋梁。



內連接查詢(常用的)


JOIN|CROSS JOIN INNER JOIN


通過ON 連接條件


顯示兩個表中符合連接條件的記錄(超過3個表效率低)


查詢cms_user表的id,username,proId(用戶所屬省份)的記錄


mysql> SELECT id,username,proId FROM cms_user;
+----+-----------+-------+
| id | username  | proId |
+----+-----------+-------+
|  1 | 張三     |     1 |
|  2 | 張三豐   |     2 |
|  3 | 章子怡   |     3 |
|  4 | long      |     4 |
|  5 | ring      |     2 |
|  6 | queen    |     3 |
|  8 | blek      |     1 |
|  9 | rose      |     2 |
| 10 | lily        |     2 |
| 11 | john       |     2 |
| 12 | test1      |     1 |
+----+-----------+-------+
11 rows in set (0.00 sec)


查看provinces(用戶省份表)表


mysql> DESC provinces;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | tinyint(3) unsigned    | NO   | PRI | NULL    | auto_increment |
| proName | varchar(10)         | NO   | UNI | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+
2 rows in set (0.07 sec)


查詢用戶表的ID,username


Province,proname
mysql> SELECT id,username,proName FROM cms_user,provinces;
ERROR 1052 (23000): Column 'id' in field list is ambiguous
查詢用戶表的ID,username
Province,proname


(解決用表名點字段)


mysql> SELECT cms_user.id,username,proName FROM cms_user,provinces;
+----+-----------+---------+
| id | username  | proName |
+----+-----------+---------+
|  8 | blek      | 上海    |
|  8 | blek      | 北京    |
|  8 | blek      | 廣州    |
|  8 | blek      | 深圳    |
|  8 | blek      | 重慶    |
| 11 | john      | 上海    |
| 11 | john      | 北京    |
| 11 | john      | 廣州    |
| 11 | john      | 深圳    |
| 11 | john      | 重慶    |
| 10 | lily      | 上海    |
| 10 | lily      | 北京    |
| 10 | lily      | 廣州    |
| 10 | lily      | 深圳    |
| 10 | lily      | 重慶    |
|  4 | long      | 上海    |
|  4 | long      | 北京    |
|  4 | long      | 廣州    |
|  4 | long      | 深圳    |
|  4 | long      | 重慶    |
|  6 | queen     | 上海    |
|  6 | queen     | 北京    |
|  6 | queen     | 廣州    |
|  6 | queen     | 深圳    |
|  6 | queen     | 重慶    |
|  5 | ring      | 上海    |
|  5 | ring      | 北京    |
|  5 | ring      | 廣州    |
|  5 | ring      | 深圳    |
|  5 | ring      | 重慶    |
|  9 | rose      | 上海    |
|  9 | rose      | 北京    |
|  9 | rose      | 廣州    |
|  9 | rose      | 深圳    |
|  9 | rose      | 重慶    |
| 12 | test1     | 上海    |
| 12 | test1     | 北京    |
| 12 | test1     | 廣州    |
| 12 | test1     | 深圳    |
| 12 | test1     | 重慶    |
|  1 | 張三      | 上海    |
|  1 | 張三      | 北京    |
|  1 | 張三      | 廣州    |
|  1 | 張三      | 深圳    |
|  1 | 張三      | 重慶    |
|  2 | 張三豐    | 上海    |
|  2 | 張三豐    | 北京    |
|  2 | 張三豐    | 廣州    |
|  2 | 張三豐    | 深圳    |
|  2 | 張三豐    | 重慶    |
|  3 | 章子怡    | 上海    |
|  3 | 章子怡    | 北京    |
|  3 | 章子怡    | 廣州    |
|  3 | 章子怡    | 深圳    |
|  3 | 章子怡    | 重慶    |
+----+-----------+---------+
55 rows in set (0.03 sec)


(形成了雙層嵌套記錄)


查詢cms_user表的記錄


mysql> SELECT * FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password| email  | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 張三 | zhangsan| [email protected] | 1419811708 | user.jpg |     1 |   21 | 男     |
|  2 | 張三豐 | zhangsanfeng | [email protected] | 1419812708 | user.jpg | 2 |   31 | 女     |
|  3 | 章子怡 | zhangsan | [email protected] | 1419813708 | user.jpg |  3 |   43 | 男     |
|  4 | long      | long | [email protected] | 1419814708 | user.jpg |     4 |   41 | 女     |
|  5 | ring      | ring | [email protected] | 1419815708 | user.jpg |     2 |    9 | 男     |
|  6 | queen| queen | [email protected] | 1419861708 | user.jpg |     3 |   77 | 女     |
|  8 | blek      | blek  | [email protected] | 1419818708 | user.jpg |     1 |   85 | 女     |
|  9 | rose      | rose  | [email protected] | 1419821708 | user.jpg |     2 |    9 | 男     |
| 10 | lily      | lily  | [email protected] | 1419831708 | user.jpg |     2 |   39 | 女     |
| 11 | john      | john| [email protected] | 1419841708 | user.jpg |     2 |   72 | 保密   |
| 12 | test1     | test1 | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
11 rows in set (0.00 sec)


查詢省份表的記錄


mysql> SELECT * FROM provinces;
+----+---------+
| id | proName |
+----+---------+
|  2 | 上海    |
|  1 | 北京    |
|  4 | 廣州    |
|  3 | 深圳    |
|  5 | 重慶    |
+----+---------+
5 rows in set (0.00 sec)


條件是Cms_user 的porid對應省份表的id


mysql> SELECT cms_user.id,username,proName FROM cms_user,provinces
    -> 
    -> WHERE cms_user.proId=provinces.id;
+----+-----------+---------+
| id | username  | proName |
+----+-----------+---------+
|  1 | 張三     | 北京    |
|  2 | 張三豐   | 上海    |
|  3 | 章子怡   | 深圳    |
|  4 | long      | 廣州    |
|  5 | ring      | 上海    |
|  6 | queen    | 深圳    |
|  8 | blek      | 北京    |
|  9 | rose      | 上海    |
| 10 | lily        | 上海    |
| 11 | john       | 上海    |
| 12 | test1      | 北京    |
+----+-----------+---------+
11 rows in set (0.04 sec)


(where進行第一次篩選)


查詢cms_name 表中id,username,email,sex


查詢provinces表proname(使用INNER  JOIN)


mysql> SELECT u.id,u.username,u.email,u.sex,p.proName
    -> 
    -> FROM cms_user AS u
    -> 
    -> INNER JOIN provinces AS p
    -> 
    -> ON u.proId=p.id;
+----+-----------+-------------+--------+---------+
| id | username  | email       | sex    | proName |
+----+-----------+-------------+--------+---------+
|  1 | 張三      | [email protected] | 男     | 北京    |
|  2 | 張三豐    | [email protected] | 女     | 上海    |
|  3 | 章子怡    | [email protected] | 男     | 深圳    |
|  4 | long       | [email protected] | 女     | 廣州    |
|  5 | ring       | [email protected] | 男     | 上海    |
|  6 | queen      | [email protected] | 女     | 深圳    |
|  8 | blek      | [email protected] | 女     | 北京    |
|  9 | rose      | [email protected] | 男     | 上海    |
| 10 | lily        | [email protected] | 女     | 上海    |
| 11 | john       | [email protected] | 保密   | 上海    |
| 12 | test1      | [email protected] | 保密   | 北京    |
+----+-----------+-------------+--------+---------+
11 rows in set (0.02 sec)


(內連接和where條件的結果一樣)


查詢cms_name 表中id,username,email,sex


查詢provinces表proname(使用CROSS  JOIN)


mysql> SELECT u.id,u.username,u.email,u.sex,p.proName
    -> 
    -> FROM provinces AS p
    -> 
    -> CROSS JOIN cms_user AS u
    -> 
    -> ON u.proId=p.id;
+----+-----------+-------------+--------+---------+
| id | username  | email       | sex    | proName |
+----+-----------+-------------+--------+---------+
|  1 | 張三      | [email protected] | 男     | 北京    |
|  2 | 張三豐    | [email protected] | 女     | 上海    |
|  3 | 章子怡    | [email protected] | 男     | 深圳    |
|  4 | long      | [email protected] | 女     | 廣州    |
|  5 | ring      | [email protected] | 男     | 上海    |
|  6 | queen     | [email protected] | 女     | 深圳    |
|  8 | blek      | [email protected] | 女     | 北京    |
|  9 | rose      | [email protected] | 男     | 上海    |
| 10 | lily      | [email protected] | 女     | 上海    |
| 11 | john      | [email protected] | 保密   | 上海    |
| 12 | test1     | [email protected] | 保密   | 北京    |
+----+-----------+-------------+--------+---------+
11 rows in set (0.00 sec)


查詢cms_name 表中id,username,email,sex


查詢provinces表proname(使用 JOIN)


mysql> SELECT u.id,u.username,u.email,u.sex,p.proName
    -> 
    -> FROM provinces AS p
    -> 
    -> JOIN cms_user AS u
    -> 
    -> ON u.proId=p.id;
+----+-----------+-------------+--------+---------+
| id | username  | email       | sex    | proName |
+----+-----------+-------------+--------+---------+
|  1 | 張三      | [email protected] | 男     | 北京    |
|  2 | 張三豐    | [email protected] | 女     | 上海    |
|  3 | 章子怡    | [email protected] | 男     | 深圳    |
|  4 | long      | [email protected] | 女     | 廣州    |
|  5 | ring      | [email protected] | 男     | 上海    |
|  6 | queen     | [email protected] | 女     | 深圳    |
|  8 | blek      | [email protected] | 女     | 北京    |
|  9 | rose      | [email protected] | 男     | 上海    |
| 10 | lily      | [email protected] | 女     | 上海    |
| 11 | john      | [email protected] | 保密   | 上海    |
| 12 | test1     | [email protected] | 保密   | 北京    |
+----+-----------+-------------+--------+---------+
11 rows in set (0.00 sec)


查詢cms_user id ,username,sex


查詢province proname


條件是cms_user的性別為男的用戶


 mysql> SELECT u.id,u.username,u.sex,p.proName 
    -> 
    -> FROM cms_user AS u
    -> 
    -> JOIN
    -> 
    -> provinces AS p
    -> 
    -> ON u.proId=p.id
    -> 
    -> WHERE u.sex='男';
+----+-----------+------+---------+
| id | username  | sex  | proName |
+----+-----------+------+---------+
|  1 | 張三      | 男   | 北京    |
|  3 | 章子怡    | 男   | 深圳    |
|  5 | ring      | 男   | 上海    |
|  9 | rose      | 男   | 上海    |
+----+-----------+------+---------+
4 rows in set (0.00 sec)


基于上面根據proname分組


mysql> SELECT u.id,u.username,u.sex,p.proName 
    -> 
    -> FROM cms_user AS u
    -> 
    -> JOIN
    -> 
    -> provinces AS p
    -> 
    -> ON u.proId=p.id
    -> 
    -> WHERE u.sex='男';
+----+-----------+------+---------+
| id | username  | sex  | proName |
+----+-----------+------+---------+
|  1 | 張三      | 男   | 北京    |
|  3 | 章子怡    | 男   | 深圳    |
|  5 | ring      | 男   | 上海    |
|  9 | rose      | 男   | 上海    |
+----+-----------+------+---------+
4 rows in set (0.00 sec)


基于上面根據proname分組,得到組中的人數


mysql> SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers
    -> 
    -> FROM cms_user AS u
    -> 
    -> JOIN
    -> 
    -> provinces AS p
    -> 
    -> ON u.proId=p.id
    -> 
    -> WHERE u.sex='男'
    -> 
    -> GROUP BY p.proName;
+----+-----------+------+---------+------------+
| id | username  | sex  | proName | totalUsers |
+----+-----------+------+---------+------------+
|  5 | ring      | 男   | 上海    |          2 |
|  1 | 張三      | 男   | 北京    |          1 |
|  3 | 章子怡    | 男   | 深圳    |          1 |
+----+-----------+------+---------+------------+
3 rows in set (0.07 sec)


基于上面根據proname分組,得到組中的人數,得到用戶名的詳情


mysql> SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
    -> 
    -> FROM cms_user AS u
    -> 
    -> JOIN
    -> 
    -> provinces AS p
    -> 
    -> ON u.proId=p.id
    -> 
    -> WHERE u.sex='男'
    -> 
    -> GROUP BY p.proName;
+----+-----------+------+---------+------------+------------------------+
| id | username  | sex  | proName | totalUsers | GROUP_CONCAT(username) |
+----+-----------+------+---------+------------+------------------------+
|  9 | rose      | 男   | 上海    |          2 | rose,ring              |
|  1 | 張三      | 男   | 北京    |          1 | 張三                   |
|  3 | 章子怡    | 男   | 深圳    |          1 | 章子怡                 |
+----+-----------+------+---------+------------+------------------------+
3 rows in set (0.03 sec)


對分組結果進行篩選,選出組中人數>=1用戶


mysql> SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
    -> 
    -> FROM cms_user AS u
    -> 
    -> JOIN
    -> 
    -> provinces AS p
    -> 
    -> ON u.proId=p.id
    -> 
    -> WHERE u.sex='男'
    -> 
    -> GROUP BY p.proName
    -> 
    -> HAVING COUNT(*)>=1;
+----+-----------+------+---------+------------+------------------------+
| id | username  | sex  | proName | totalUsers | GROUP_CONCAT(username) |
+----+-----------+------+---------+------------+------------------------+
|  5 | ring      | 男   | 上海    |          2 | ring,rose              |
|  1 | 張三      | 男   | 北京    |          1 | 張三                   |
|  3 | 章子怡    | 男   | 深圳    |          1 | 章子怡                 |
+----+-----------+------+---------+------------+------------------------+
3 rows in set (0.04 sec)


按照id升序排列


mysql> SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
    -> 
    -> FROM cms_user AS u
    -> 
    -> JOIN
    -> 
    -> provinces AS p
    -> 
    -> ON u.proId=p.id
    -> 
    -> WHERE u.sex='男'
    -> 
    -> GROUP BY p.proName
    -> 
    -> HAVING COUNT(*)>=1
    -> 
    -> ORDER BY id ASC;
ERROR 1054 (42S22): Unknown column 'id ASC' in 'order clause'


按照用戶表中id升序排列(錯誤寫法)


mysql> SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
    -> 
    -> FROM cms_user AS u
    -> 
    -> JOIN
    -> 
    -> provinces AS p
    -> 
    -> ON u.proId=p.id
    -> 
    -> WHERE u.sex='男'
    -> 
    -> GROUP BY p.proName
    -> 
    -> HAVING COUNT(*)>=1
    -> 
    -> ORDER BY u.id ASC;
ERROR 1054 (42S22): Unknown column 'u.id ASC' in 'order clause'


按照用戶表中id升序排列


mysql> SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
    -> 
    -> FROM cms_user AS u
    -> 
    -> JOIN
    -> 
    -> provinces AS p
    -> 
    -> ON u.proId=p.id
    -> 
    -> WHERE u.sex='男'
    -> 
    -> GROUP BY p.proName
    -> 
    -> HAVING COUNT(*)>=1
    -> 
    -> ORDER BY u.id ASC;
+----+-----------+------+---------+------------+------------------------+
| id | username  | sex  | proName | totalUsers | GROUP_CONCAT(username) |
+----+-----------+------+---------+------------+------------------------+
|  1 | 張三      | 男   | 北京    |          1 | 張三                   |
|  3 | 章子怡    | 男   | 深圳    |          1 | 章子怡                 |
|  5 | ring      | 男   | 上海    |          2 | ring,rose              |
+----+-----------+------+---------+------------+------------------------+
3 rows in set (0.01 sec)


限制顯示條數前2條


mysql> SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
    -> 
    -> FROM cms_user AS u
    -> 
    -> JOIN
    -> 
    -> provinces AS p
    -> 
    -> ON u.proId=p.id
    -> 
    -> WHERE u.sex='男'
    -> 
    -> GROUP BY p.proName
    -> 
    -> HAVING COUNT(*)>=1
    -> 
    -> ORDER BY u.id ASC
    -> 
    -> LIMIT 0,2;
+----+-----------+------+---------+------------+------------------------+
| id | username  | sex  | proName | totalUsers | GROUP_CONCAT(username) |
+----+-----------+------+---------+------------+------------------------+
|  1 | 張三      | 男   | 北京    |          1 | 張三                   |
|  3 | 章子怡    | 男   | 深圳    |          1 | 章子怡                 |
+----+-----------+------+---------+------------+------------------------+
2 rows in set (0.01 sec)


查看新聞表結構


mysql> DESC cms_news;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| title    | varchar(50)         | NO   | UNI | NULL    |                |
| content  | text                | YES  |     | NULL    |                |
| clickNum | int(10) unsigned    | YES  |     | 0       |                |
| pubTime  | int(10) unsigned    | YES  |     | NULL    |                |
| cId      | tinyint(3) unsigned | NO   |     | NULL    |                |
| aId      | tinyint(3) unsigned | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)


查看cms_cate結構


mysql> DESC cms_cate;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| cateName | varchar(50)         | NO   | UNI | NULL    |                |
| cateDesc | varchar(200)        | NO   |     |         |                |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.11 sec)


查詢cms_user中的id,title


查詢cms_cate中的catename


mysql> SELECT n.id,n.title,c.cateName FROM 
    -> 
    -> cms_news AS n
    -> 
    -> JOIN (連接符)
    -> 
    -> cms_cate AS c
    -> 
    -> ON n.cId=c.id;(連接條件)
+----+-------------------------------------------------------------+--------------+
| id | title                                        | cateName     |
+----+-------------------------------------------------------------+--------------+
|  1 | 亞航客機失聯搜救尚無線索 未發求救信號      | 國內新聞     |
|  2 | 北京新開通四條地鐵線路 迎接首位客人        | 國際新聞     |
|  3 | 考研政治題多次出現習近平講話內容           | 體育新聞     |
|  4 | 深度-曾雪麟:佩蘭別重蹈卡馬喬覆轍             | 國際新聞     |
|  5 | 國產JAD-1手槍槍架投入使用 手槍可變"沖鋒槍"    | 軍事新聞     |
|  6 | 麥子學院榮獲新浪教育大獎                      | 國內新聞     |
|  7 | 麥子學院榮獲騰訊教育大獎                      | 國內新聞     |
|  8 | 麥子學院新課上線                              | 國內新聞     |
+----+-------------------------------------------------------------+--------------+
8 rows in set (0.02 sec)


查詢新聞表的記錄


mysql> SELECT * FROM cms_news;
+----+-------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------------+-----+-----+
| id | title                                                       | content                                                                                                                                                                                                                                                                                                                                                                                                                     | clickNum | pubTime    | cId | aId |
+----+-------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------------+-----+-----+
|  1 | 亞航客機失聯搜救尚無線索 未發求救信號                       | 馬來西亞亞洲航空公司一架搭載155名乘客的客機28日早晨從印度尼西亞飛往新加坡途中與空中交通控制塔臺失去聯系,下落不明。                                                                                                                                                                                                                                                                                                         |        0 | 1419818808 |   1 |   2 |
|  2 | 北京新開通四條地鐵線路 迎接首位客人                         | 12月28日凌晨,隨著北京地鐵6號線二期、7號線、15號線西段、14號線東段的開通試運營,北京的軌道交通運營里程將再添62公里,共計達到527公里。當日凌晨5時許,北京地鐵7號線瓷器口換乘站迎來新線開通的第一位乘客。                                                                                                                                                                                                                     |        0 | 1419818108 |   2 |   1 |
|  3 | 考研政治題多次出現習近平講話內容                            | 新京報訊 (記者許路陽 (微博))APEC反腐宣言、國家公祭日、依法治國……昨日,全國碩士研究生招生考試進行首日初試,其中,思想政治理論考題多次提及時事熱點,并且多次出現習近平在不同場合的講話內容。                                                                                                                                                                                                                                |        0 | 1419818208 |   3 |   2 |
|  4 | 深度-曾雪麟:佩蘭別重蹈卡馬喬覆轍                           | 12月25日是前國足主帥曾雪麟的85歲大壽,恰逢圣誕節,患有尿毒癥老爺子帶著圣誕帽度過了自己的生日。此前,騰訊記者曾專訪曾雪麟,盡管已經退休多年,但老爺子仍舊關心著中國足球,為國足揪心,對于國足近幾位的教練,他只欣賞高洪波。對即將征戰亞洲杯的國足,老爺子希望佩蘭不要重蹈卡馬喬的覆轍                                                                                                                                        |        0 | 1419818308 |   2 |   4 |
|  5 | 國產JAD-1手槍槍架投入使用 手槍可變"沖鋒槍"                  | 日前,JAD-1型多功能手槍槍架通過公安部特種警用裝備質量監督檢驗中心檢驗,正式投入生產使用。此款多功能槍架由京安盾(北京)警用裝備有限公司開發研制,期間經廣東省江門市公安特警支隊試用,獲得好評。                                                                                                                                                                                                                               |        0 | 1419818408 |   4 |   4 |
|  6 | 麥子學院榮獲新浪教育大獎                                    | 麥子學院最大的職業IT教育平臺,獲獎了                                                                                                                                                                                                                                                                                                                                                                                        |        0 | 1419818508 |   1 |   5 |
|  7 | 麥子學院榮獲騰訊教育大獎                                    | 麥子學院最大的職業IT教育平臺,獲獎了                                                                                                                                                                                                                                                                                                                                                                                        |        0 | 1419818608 |   1 |   5 |
|  8 | 麥子學院新課上線                                            | 麥子學院PHP課程馬上上線了,小伙伴快來報名學習哈                                                                                                                                                                                                                                                                                                                                                                             |        0 | 1419818708 |   1 |   5 |
+----+-------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------------+-----+-----+
8 rows in set (0.00 sec)
Cms_name  id,title
Cms_admin  username,role


(查看新聞是誰發布的)


mysql> SELECT n.id,n.title,a.username,a.role
    -> 
    -> FROM 
    -> 
    -> cms_news AS n
    -> 
    -> JOIN
    -> 
    -> cms_admin AS a
    -> 
    -> ON n.aId=a.id;
+----+-------------------------------------------------------------+----------+-----------------+
| id | title                                      | username | role            |
+----+-------------------------------------------------------------+----------+-----------------+
|  2 | 北京新開通四條地鐵線路 迎接首位客人   | admin    | 超級管理員      |
|  1 | 亞航客機失聯搜救尚無線索 未發求救信號  | king     | 普通管理員      |
|  3 | 考研政治題多次出現習近平講話內容        | king     | 普通管理員      |
|  4 | 深度-曾雪麟:佩蘭別重蹈卡馬喬覆轍        | queen    | 普通管理員      |
|  5 | 國產JAD-1手槍槍架投入使用 手槍可變"沖鋒槍"| queen    | 普通管理員      |
|  6 | 麥子學院榮獲新浪教育大獎              | test     | 普通管理員      |
|  7 | 麥子學院榮獲騰訊教育大獎             | test     | 普通管理員      |
|  8 | 麥子學院新課上線                   | test     | 普通管理員      |
+----+-------------------------------------------------------------+----------+-----------------+
8 rows in set (0.12 sec)


查看cms_news表的結構


mysql> DESC cms_news;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| title    | varchar(50)         | NO   | UNI | NULL    |                |
| content  | text                | YES  |     | NULL    |                |
| clickNum | int(10) unsigned    | YES  |     | 0       |                |
| pubTime  | int(10) unsigned    | YES  |     | NULL    |                |
| cId      | tinyint(3) unsigned | NO   |     | NULL    |                |
| aId      | tinyint(3) unsigned | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
Cms_name  id,title
Cms_cate  catename
Cms_admin  username,role


(多表連接)


mysql> SELECT n.id,n.title,c.cateName,a.username,a.role
    -> 
    -> FROM cms_cate AS c
    -> 
    -> JOIN 
    -> 
    -> cms_news AS n
    -> 
    -> ON n.cId=c.id
    -> 
    -> JOIN 
    -> 
    -> cms_admin AS a
    -> 
    -> ON n.aId=a.id;
+----+-------------------------------------------------------------+--------------+----------+-----------------+
| id | title                                        | cateName     | username | role            |
+----+-------------------------------------------------------------+--------------+----------+-----------------+
|  2 | 北京新開通四條地鐵線路 迎接首位客人            | 國際新聞     | admin    | 超級管理員      |
|  1 | 亞航客機失聯搜救尚無線索 未發求救信號      | 國內新聞     | king     | 普通管理員      |
|  3 | 考研政治題多次出現習近平講話內容           | 體育新聞     | king     | 普通管理員      |
|  4 | 深度-曾雪麟:佩蘭別重蹈卡馬喬覆轍          | 國際新聞     | queen    | 普通管理員      |
|  5 | 國產JAD-1手槍槍架投入使用 手槍可變"沖鋒槍"       | 軍事新聞     | queen    | 普通管理員      |
|  6 | 麥子學院榮獲新浪教育大獎                         | 國內新聞     | test     | 普通管理員      |
|  7 | 麥子學院榮獲騰訊教育大獎                        | 國內新聞     | test     | 普通管理員      |
|  8 | 麥子學院新課上線                                | 國內新聞     | test     | 普通管理員      |
+----+-------------------------------------------------------------+--------------+----------+-----------------+
8 rows in set (0.00 sec)



【本文由麥子學院獨家原創,轉載請注明出處并保留原文鏈接】

logo
? 2012-2016 www.jvbprd.live
蜀ICP備13014270號-4 Version 5.0.0 release20160127

免費領取價值1888元求職寶典!

客服熱線 400-862-8862

回到頂部

彩票安徽25选5 传图片赚钱 全民欢乐捕鱼攻略技巧 快赢彩票苹果 360 锁屏赚钱如何取消 把老公当赚钱 可以赚钱的红包农场 茗彩彩票网址 在广告店上班赚钱吗 现在做什么事可以赚钱 湖北麻将卡五星规则 开一间小美容店赚钱不 可以打字打文章赚钱的软件好 大象彩票苹果 洗车店洗车卖精品赚钱吗 什么都不赚钱 福州麻将圈怎么感觉有作弊