精华内容
下载资源
问答
  • 主要介绍了asp.net实现的MVC跨数据库多表联合动态条件查询功能,结合实例形式较为详细分析了asp.net基于MVC架构的跨数据库多表联合查询功能实现技巧,需要的朋友可以参考下
  • 数据库多表联查 Db::table('think_artist') ->alias('a') // alias 表示命名数据库的别称为a ->join('think_work w','a.id = w.artist_id') ->join('think_card c','a.card_id = ...

    数据库多表联查

    Db::table('think_artist')
    ->alias('a')	// alias 表示命名数据库的别称为a
    ->join('think_work w','a.id = w.artist_id')
    ->join('think_card c','a.card_id = c.id')
    ->select();
    

    数据库 多条件查询

    // 使用数组的形式查询数据,db是是数据库的助手函数,注意去掉前缀
    $data = db('cms_document')->where(array('cid'=>array("in","5,6")))->select();
    
    展开全文
  • 数据库多表联合动态条件查询

    千次阅读 2016-08-18 18:32:49
    /// 分页查询,检索页数据 /// /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// 价格排序:0默认,1升序,2降序 /// <returns></returns> public List<string> ...

    一、控制器中方法

    [HttpGet]
            public ActionResult Search()
            {
                ViewBag.HeadTitle = "搜索";
                ViewBag.MetaKey = "\"123\"";
                ViewBag.MetaDes = "\"456\"";
    
                string whereText = "";
                if (Security.HtmlHelper.GetQueryString("first", true) != string.Empty)
                {
                    whereText += " and a.ParentId='" + StringFilter("first", true)+"'";
                }
    
                if (Security.HtmlHelper.GetQueryString("second", true) != string.Empty)
                    whereText += " and a.categoryId='" + StringFilter("second",true)+"'";
    
                string valueStr = "";
                if (Security.HtmlHelper.GetQueryString("theme", true) != string.Empty)
                    valueStr += StringFilter("theme", true) + ",";
                if (Security.HtmlHelper.GetQueryString("size", true) != string.Empty)
                    valueStr += StringFilter("size", true) + ",";
                if (Security.HtmlHelper.GetQueryString("font", true) != string.Empty)
                    valueStr += StringFilter("font", true) + ",";
                if (Security.HtmlHelper.GetQueryString("shape", true) != string.Empty)
                    valueStr += StringFilter("shape", true) + ",";
                if (Security.HtmlHelper.GetQueryString("technique", true) != string.Empty)
                    valueStr += StringFilter("technique", true) + ",";
                if (Security.HtmlHelper.GetQueryString("category", true) != string.Empty)
                    valueStr += StringFilter("category", true) + ",";
                if (Security.HtmlHelper.GetQueryString("place", true) != string.Empty)
                    valueStr += StringFilter("place", true) + ",";
                if (Security.HtmlHelper.GetQueryString("price", true) != string.Empty)
                    valueStr += StringFilter("price", true) + ",";
    
                if (valueStr != "")
                {
                    valueStr=valueStr.Substring(0, valueStr.Length - 1);
                    whereText += " and f.valueId in("+valueStr+")";
                }
    
                if (Security.HtmlHelper.GetQueryString("searchKeys", true) != string.Empty)
                    whereText += " and a.SaleTitle like '%'" + StringFilter("searchKes", true) + "'%' or a.SaleDes like '%'" + StringFilter("searchKes", true) + "'%' or a.SaleAuthor like '%'" + StringFilter("searchKes", true) + "'%' or a.KeyWords like '%'" + StringFilter("searchKes", true) + "'%' or g.valueProperty like '%'" + StringFilter("searchKes", true) + "'%'";
    
                int pageSize = 50;
                int pageIndex = HttpContext.Request.QueryString["pageIndex"].Toint(1);
                List<string> searchInfo = Search(pageIndex, pageSize, whereText, 1);
    
                if (Security.HtmlHelper.GetQueryString("sort", true) != string.Empty)
                {
                    string sort = StringFilter("sort", true);
                    switch (sort)
                    {
                        case "1":    //综合即默认按照上架时间降序排列即按照id降序
                            searchInfo = Search(pageIndex, pageSize, whereText, 1);
                            break;
                        case"2":    //销量
                            searchInfo = Search(pageIndex, pageSize, whereText,0, "saleTotal");
                            break;
                        case "3":   //收藏
                            searchInfo = Search(pageIndex, pageSize, whereText,0, "favoritesTotal");
                            break;
                        case "4":   //价格升序
                            searchInfo = Search(pageIndex, pageSize, whereText,1);
                            break;
                        case "5":   //价格降序
                            searchInfo = Search(pageIndex, pageSize, whereText,2);
                            break;
                    }
                }
    
                string jsonStr = searchInfo[0];
                ViewData["jsondata"] = jsonStr;
                
                int allCount = Utility.Toint(searchInfo[1], 0);
                ViewBag.AllCount = allCount;
                ViewBag.MaxPages = allCount % pageSize == 0 ? allCount / pageSize : (allCount / pageSize + 1).Toint(1);
    
                return View();
            }
    
            [NonAction]
            public List<string> Search(int pageIndex, int pageSize, string whereText, int orderByPrice, string orderBy = "SaleId")
            {
                BLL.Products searchInfoBLL = new BLL.Products();
    
                List<string> searchInfo = searchInfoBLL.GetSearchInfo(pageIndex, pageSize, whereText, orderByPrice,orderBy);
                return searchInfo;
            }
    :Security.HtmlHelper.GetQueryString(),StringFilter()为自己封装的方法,用于过滤参数值


    二、BLL层方法

    using System;
    using System.Web;
    using System.Web.Caching;
    using System.Collections;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using System.Data.Common;
    using System.Web.Script.Serialization;
    using FotosayMall.Model;
    using FotosayMall.Common;
    using System.Text.RegularExpressions;
    using System.IO;
    using Newtonsoft.Json;
    using Newtonsoft.Json.Converters;
    using FotosayMall.MVC.Models;
    
    namespace FotosayMall.BLL
    {
        public class Products
        {
    
            private readonly DAL.Products dal = new DAL.Products();
    
            /// <summary>
            /// 分页查询,检索页数据
            /// </summary>
            /// <param name="pageIndex"></param>
            /// <param name="pageSize"></param>
            /// <param name="orderByPrice">价格排序:0默认,1升序,2降序</param>
            /// <returns></returns>
            public List<string> GetSearchInfo(int pageIndex, int pageSize, string whereText, int orderByPrice, string orderBy = "SaleId")
            { 
                DataSet searchInfoTables = dal.GetSearchInfo(pageIndex, pageSize, whereText);
                //总记录数
                int allCount = Utility.Toint(searchInfoTables.Tables[1].Rows[0]["rowsTotal"], 0);
                var searchInfo = from list in searchInfoTables.Tables[0].AsEnumerable().OrderByDescending(x => x.Table.Columns[orderBy])
                                 select new SearchModel
                                 {
                                     Url = "/home/products?saleId=" + list.Field<int>("SaleId"),
                                     Author = list.Field<string>("SaleAuthor"),
                                     PhotoFileName = list.Field<string>("PhotoFileName"),
                                     PhotoFilePathFlag = list.Field<int>("PhotoFilePathFlag"),
                                     Province = list.Field<string>("Place").Split(' ').First(),
                                     SalePrice = list.Field<decimal>("SalePrice"),
                                     UsingPrice = list.Field<decimal>("usingPrice"),
                                     Title = list.Field<string>("SaleTitle").Length > 30 ? list.Field<string>("SaleTitle").Substring(0, 30) : list.Field<string>("SaleTitle"),
                                     Year = list.Field<DateTime>("BuildTime").ToString("yyyy") == "1900" ? "" : list.Field<DateTime>("BuildTime").ToString("yyyy年")
                                 };
    
                if (orderByPrice==2)
                    searchInfo = searchInfo.OrderByDescending(x => x.Price);
                else if (orderByPrice == 1)
                    searchInfo = searchInfo.OrderBy(x => x.Price);
    
                string jsonStr = JsonConvert.SerializeObject(searchInfo);
                List<string> dataList = new List<string>();
                dataList.Add(jsonStr);
                dataList.Add(allCount.ToString());
                return dataList;
            }
        }
    }
    :注意观察由DataTable转换为可枚举的可用于Linq查询的方法方式。


    DAL

    /// <summary>
            /// 获取检索页数据
            /// </summary>
            /// <param name="pageIndex"></param>
            /// <param name="pageSize"></param>
            /// <returns></returns>
            public DataSet GetSearchInfo(int pageIndex, int pageSize, string whereText)
            {
                StringBuilder sqlText = new StringBuilder();
                sqlText.Append("select * from (");
                sqlText.Append("select a.SaleId,a.PhotoId,SaleTitle,SaleAuthor,a.Status,a.categoryId,c.UserID,c.UserName,b.PhotoFilePathFlag,b.PhotoFileName,coalesce(e.BuildTime,0) BuildTime,c.Place,coalesce(d.usingPrice,0) usingPrice,coalesce(e.SalePrice,0) SalePrice,h.saleTotal,h.favoritesTotal,row_number() over(order by a.saleId) rowsNum ");
                sqlText.Append("from fotosay..Photo_Sale a join fotosay..Photo_Basic b on a.PhotoId = b.PhotoID ");
                sqlText.Append("join fotosay..System_AccountsDescription c on b.UserID = c.UserID ");
                sqlText.Append("left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId ");
                sqlText.Append("left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId ");
                sqlText.Append("join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId ");
                sqlText.Append("join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId ");
                sqlText.Append("join fotosay..Photo_Sale_Property h on a.saleId = h.saleId ");
                sqlText.Append("where a.Status=1 " + whereText + " "); 
                sqlText.Append("group by a.SaleId,a.PhotoId,SaleTitle,SaleAuthor,a.Status,a.categoryId,c.UserID,c.UserName,b.PhotoFilePathFlag,b.PhotoFileName,e.BuildTime,c.Place,usingPrice,SalePrice,h.saleTotal,h.favoritesTotal ");
                sqlText.Append(") t where rowsNum between @PageSize*(@PageIndex-1)+1 and @PageSize*@PageIndex;");
                sqlText.Append("select count(distinct a.saleId) rowsTotal  from fotosay..Photo_Sale a join (select b1.PhotoFilePathFlag,b1.PhotoFileName,b1.UserID,b1.PhotoID from fotosay..Photo_Basic b1 union select b2.PhotoFilePathFlag,b2.PhotoFileName,b2.UserID,b2.PhotoID from fotosay..Photo_Basic_History b2 ) b on a.PhotoId = b.PhotoID join fotosay..System_AccountsDescription c on b.UserID = c.UserID left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId join fotosay..Photo_Sale_Property h on a.saleId = h.saleId where a.Status=1 " + whereText + ";");
    
                DbParameter[] parameters = { 
                                            Fotosay.CreateInDbParameter("@PageIndex", DbType.Int32,pageIndex),
                                            Fotosay.CreateInDbParameter("@PageSize", DbType.Int32,pageSize)
                                           };
    
                DataSet searchInfoList = Fotosay.ExecuteQuery(CommandType.Text, sqlText.ToString(), parameters);
    
                //记录条数不够一整页,则查历史库
                if (searchInfoList.Tables[0].Rows.Count < pageSize)
                {
                    string sql = "select top(1) a.saleId  from fotosay..Photo_Sale a join fotosay..Photo_Basic_History b on a.PhotoId = b.PhotoID join fotosay..System_AccountsDescription c on b.UserID = c.UserID left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId join fotosay..Photo_Sale_Property h on a.saleId = h.saleId where a.Status=1 " + whereText + ";";
    
                    DataSet ds = Fotosay.ExecuteQuery(CommandType.Text, sql.ToString(), parameters);
    
                    if (ds != null && ds.Tables[0].Rows.Count > 0)
                    {
                        StringBuilder sqlTextMore = new StringBuilder();
                        sqlTextMore.Append("select * from (");
                        sqlTextMore.Append("select a.SaleId,a.PhotoId,SaleTitle,SaleAuthor,a.Status,a.categoryId,c.UserID,c.UserName,b.PhotoFilePathFlag,b.PhotoFileName,coalesce(e.BuildTime,0) BuildTime,c.Place,coalesce(d.usingPrice,0) usingPrice,coalesce(e.SalePrice,0) SalePrice,h.saleTotal,h.favoritesTotal,row_number() over(order by a.saleId) rowsNum ");
                        sqlTextMore.Append("from fotosay..Photo_Sale a ");
                        sqlTextMore.Append("join (select b1.PhotoFilePathFlag,b1.PhotoFileName,b1.UserID,b1.PhotoID from fotosay..Photo_Basic b1 union select b2.PhotoFilePathFlag,b2.PhotoFileName,b2.UserID,b2.PhotoID from fotosay..Photo_Basic_History b2 ) b on a.PhotoId = b.PhotoID join fotosay..System_AccountsDescription c on b.UserID = c.UserID ");
                        sqlTextMore.Append("left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId ");
                        sqlTextMore.Append("left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId ");
                        sqlTextMore.Append("join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId ");
                        sqlTextMore.Append("join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId ");
                        sqlTextMore.Append("join fotosay..Photo_Sale_Property h on a.saleId = h.saleId ");
                        sqlTextMore.Append("where a.Status=1 " + whereText + " ");
                        sqlTextMore.Append("group by a.SaleId,a.PhotoId,SaleTitle,SaleAuthor,a.Status,a.categoryId,c.UserID,c.UserName,b.PhotoFilePathFlag,b.PhotoFileName,e.BuildTime,c.Place,usingPrice,SalePrice,h.saleTotal,h.favoritesTotal");
                        sqlTextMore.Append(") t where rowsNum between @PageSize*(@PageIndex-1)+1 and @PageSize*@PageIndex;");
                        sqlTextMore.Append("select count(distinct a.saleId) rowsTotal  from fotosay..Photo_Sale a join (select b1.PhotoFilePathFlag,b1.PhotoFileName,b1.UserID,b1.PhotoID from fotosay..Photo_Basic b1 union select b2.PhotoFilePathFlag,b2.PhotoFileName,b2.UserID,b2.PhotoID from fotosay..Photo_Basic_History b2 ) b on a.PhotoId = b.PhotoID join fotosay..System_AccountsDescription c on b.UserID = c.UserID left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId join fotosay..Photo_Sale_Property h on a.saleId = h.saleId where a.Status=1 " + whereText + ";");
    
                        searchInfoList = Fotosay.ExecuteQuery(CommandType.Text, sqlTextMore.ToString(), parameters);
                    }
                }
    
                return searchInfoList;
            }
    :注意其中使用的跨数据库查询的方式和union的一种使用方式


    Model

    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Linq;
    using System.Web;
    
    namespace FotosayMall.MVC.Models
    {
        public class SearchModel
        {
            /// <summary>
            /// 原始图片文件夹(用于url地址)
            /// </summary>
            private const string OriginImagesUrlFolder = "userimages/photos_origin";
    
            /// <summary>
            /// 购买页链接
            /// </summary>
            public string Url { get; set; }
    
            /// <summary>
            /// 所属域名(1为fotosay,2为img,3为img1)
            /// </summary>
            public int PhotoFilePathFlag { get; set; }
    
            /// <summary>
            /// 图片名称
            /// </summary>
            public string PhotoFileName { get; set; }
    
            /// <summary>
            /// 商品名称
            /// </summary>
            public string Title { get; set; }
    
            /// <summary>
            /// 作者所在省份
            /// </summary>
            public string Province { get; set; }
    
            /// <summary>
            /// 作者
            /// </summary>
            public string Author { get; set; }
    
            /// <summary>
            /// 创作年份
            /// </summary>
            public string Year { get; set; }
    
            /// <summary>
            /// 图片:单次价格
            /// </summary>
            public decimal UsingPrice { get; set; }
    
            /// <summary>
            /// 实物:定价
            /// </summary>
            public decimal SalePrice { get; set; }
    
            /// <summary>
            /// 售价
            /// </summary>
            public string Price
            {
                get
                {
                    if (this.UsingPrice > 0)
                        return this.UsingPrice.ToString();
                    else if (this.SalePrice > 0)
                        return this.SalePrice.ToString();
                    else
                        return "议价";
                }
            }
    
            /// <summary>
            /// 
            /// </summary>
            private string MasterSite
            {
                get { return ConfigurationManager.AppSettings["masterSite"].ToString(); }
            }
    
            /// <summary>
            /// 图片完整路径
            /// </summary>
            public string Img
            {
                get
                {
                    return MasterSite + "/" + OriginImagesUrlFolder + this.PhotoFileName + "b.jpg";
                }
            }
        }
    }


    展开全文
  • MySQL数据库多表查询

    2020-12-07 08:20:11
    目录MySQL数据库多表查询多表查询查询联合查询交叉连接内连接外连接左外连接 left outer右外链接:right outer完全外连接三张表查询自连接 多表查询 查询结果来自于多张表,即多表查询查询:在SQL语句嵌套着...
        

    MySQL数据库多表查询

    多表查询

    1. 查询结果来自于多张表,即多表查询
    子查询:在SQL语句嵌套着查询语句,性能较差,基于某语句的查询结果再次进行的查询
    联合查询:UNION   
    交叉连接:笛卡尔乘积   
    内连接:
           等值连接:让表之间的字段以“等值”建立连接关系  
           不等值连接:不等值连接查询就是无条件判断,若查询多个表内的数据,其中的数据不会同步,各自把各自的展现出来,没有任何关联。
           自然连接:去掉重复列的等值连接   
    外连接:
       左外连接:FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
       右外连接:FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
    自连接:本表和本表进行连接查询
    

    子查询

    常用在WHERE子句中的子查询
    
    1. 用于比较表达式中的子查询;子查询仅能返回单个值(查询s1表中大于平均年龄的人)
    MariaDB [hellodb]> select * from s1 where age > (select avg(age) from s1);
    +-------+--------------+-------+-----+--------+---------+-----------+
    | StuID | Name         | phone | Age | Gender | ClassID | TeacherID |
    +-------+--------------+-------+-----+--------+---------+-----------+
    |     3 | Xie Yanke    |  NULL |  53 | M      |       2 |        16 |
    |     4 | Ding Dian    |  NULL |  32 | M      |       4 |         4 |
    |     5 | Yu Yutong    |  NULL |  26 | M      |       3 |         1 |
    |     6 | Shi Qing     |  NULL |  46 | M      |       5 |      NULL |
    |    13 | Tian Boguang |  NULL |  33 | M      |       2 |      NULL |
    |    24 | Xu Xian      |  NULL |  27 | M      |    NULL |      NULL |
    |    25 | Sun Dasheng  |  NULL | 100 | M      |    NULL |      NULL |
    +-------+--------------+-------+-----+--------+---------+-----------+
    7 rows in set (0.01 sec)
    
    1. 查询结果嵌入到另一个表里,小数转换整数会四舍五入
    MariaDB [hellodb]> select avg(age) from s1 ;  (查看s1表平均年龄)
    +----------+
    | avg(age) |
    +----------+
    |  25.0857 |
    +----------+
    1 row in set (0.00 sec)
    MariaDB [hellodb]> select * from teachers;  (原来的表内容)
    +-----+---------------+-----+--------+
    | TID | Name          | Age | Gender |
    +-----+---------------+-----+--------+
    |   1 | Song Jiang    |  45 | M      |
    |   2 | Zhang Sanfeng |  94 | M      |
    |   3 | Miejue Shitai |  77 | F      |
    |   4 | Lin Chaoying  |  26 | F      |
    +-----+---------------+-----+--------+
    4 rows in set (0.00 sec)
    
    MariaDB [hellodb]> update teachers set age=(select avg(age) from s1); (插入查询结果的表内容,没有指定字段会改掉所有)
    Query OK, 4 rows affected (0.00 sec)
    Rows matched: 4  Changed: 4  Warnings: 0
    
    MariaDB [hellodb]> select * from teachers;
    +-----+---------------+-----+--------+
    | TID | Name          | Age | Gender |
    +-----+---------------+-----+--------+
    |   1 | Song Jiang    |  25 | M      |
    |   2 | Zhang Sanfeng |  25 | M      |
    |   3 | Miejue Shitai |  25 | F      |
    |   4 | Lin Chaoying  |  25 | F      |
    +-----+---------------+-----+--------+
    4 rows in set (0.00 sec)
    
    MariaDB [hellodb]> update teachers set age=48 where tid=4;  (把tid为4的age修改为48做下面实验用)
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    MariaDB [hellodb]> select * from teachers;
    +-----+---------------+-----+--------+
    | TID | Name          | Age | Gender |
    +-----+---------------+-----+--------+
    |   1 | Song Jiang    |  25 | M      |
    |   2 | Zhang Sanfeng |  25 | M      |
    |   3 | Miejue Shitai |  25 | F      |
    |   4 | Lin Chaoying  |  48 | F      |
    +-----+---------------+-----+--------+
    4 rows in set (0.00 sec)
    
    MariaDB [hellodb]> update teachers set age=(select avg(age) from s1) where tid=4;  (指定tid为4的age字段修改)
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    MariaDB [hellodb]> select * from teachers;
    +-----+---------------+-----+--------+
    | TID | Name          | Age | Gender |
    +-----+---------------+-----+--------+
    |   1 | Song Jiang    |  25 | M      |
    |   2 | Zhang Sanfeng |  25 | M      |
    |   3 | Miejue Shitai |  25 | F      |
    |   4 | Lin Chaoying  |  25 | F      |
    +-----+---------------+-----+--------+
    4 rows in set (0.00 sec)
    
    1. 多表查询:
      用子循环查看s1表,显示teachers表年龄大于s1表平均年龄的人的信息。
    MariaDB [hellodb]> update teachers set age=45 where tid=1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    MariaDB [hellodb]> update teachers set age=94 where tid=2;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    MariaDB [hellodb]> update teachers set age=77 where tid=3;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    MariaDB [hellodb]> select  * from teachers;
    +-----+---------------+-----+--------+
    | TID | Name          | Age | Gender |
    +-----+---------------+-----+--------+
    |   1 | Song Jiang    |  45 | M      |
    |   2 | Zhang Sanfeng |  94 | M      |
    |   3 | Miejue Shitai |  77 | F      |
    |   4 | Lin Chaoying  |  25 | F      |
    +-----+---------------+-----+--------+
    4 rows in set (0.00 sec)
    (以上是把年龄修改回来做实验)
    
    
    MariaDB [hellodb]> select * from teachers where age > (select avg(age) from s1);  (多表子循环查询平均年龄大于25的人)
    +-----+---------------+-----+--------+
    | TID | Name          | Age | Gender |
    +-----+---------------+-----+--------+
    |   1 | Song Jiang    |  45 | M      |
    |   2 | Zhang Sanfeng |  94 | M      |
    |   3 | Miejue Shitai |  77 | F      |
    +-----+---------------+-----+--------+
    3 rows in set (0.00 sec)
    
    MariaDB [hellodb]> update teachers set age=26 where tid=4;  (修改一下最后一条的年龄为26)
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    MariaDB [hellodb]> select * from teachers where age > (select avg(age) from s1);   (最后一条也大于25就显示出来了)
    +-----+---------------+-----+--------+ 
    | TID | Name          | Age | Gender |
    +-----+---------------+-----+--------+
    |   1 | Song Jiang    |  45 | M      |
    |   2 | Zhang Sanfeng |  94 | M      |
    |   3 | Miejue Shitai |  77 | F      |
    |   4 | Lin Chaoying  |  26 | F      |
    +-----+---------------+-----+--------+
    4 rows in set (0.00 sec)
    

    联合查询

    1. union 纵向合并两张表,表头来自第一条查询记录.
    MariaDB [hellodb]> select * from teachers
        -> union
        -> select stuid,name,age,gender from s1;
    +-----+---------------+-----+--------+
    | TID | Name          | Age | Gender |
    +-----+---------------+-----+--------+
    |   1 | Song Jiang    |  45 | M      |
    |   2 | Zhang Sanfeng |  94 | M      |
    |   3 | Miejue Shitai |  77 | F      |
    |   4 | Lin Chaoying  |  26 | F      |
    |   1 | Shi Zhongyu   |  22 | M      |
    |   2 | Shi Potian    |  22 | M      |
    |   3 | Xie Yanke     |  53 | M      |
    |   4 | Ding Dian     |  32 | M      |
    |   5 | Yu Yutong     |  26 | M      |
    |   6 | Shi Qing      |  46 | M      |
    |   7 | Xi Ren        |  19 | F      |
    |   8 | Lin Daiyu     |  17 | F      |
    |   9 | Ren Yingying  |  20 | F      |
    |  10 | Yue Lingshan  |  19 | F      |
    |  11 | Yuan Chengzhi |  23 | M      |
    |  12 | Wen Qingqing  |  19 | F      |
    |  13 | Tian Boguang  |  33 | M      |
    |  14 | Lu Wushuang   |  17 | F      |
    |  15 | Duan Yu       |  19 | M      |
    |  16 | Xu Zhu        |  21 | M      |
    |  17 | Lin Chong     |  25 | M      |
    |  18 | Hua Rong      |  23 | M      |
    |  19 | Xue Baochai   |  18 | F      |
    |  20 | Diao Chan     |  19 | F      |
    |  21 | Huang Yueying |  22 | F      |
    |  22 | Xiao Qiao     |  20 | F      |
    |  23 | Ma Chao       |  23 | M      |
    |  24 | Xu Xian       |  27 | M      |
    |  25 | Sun Dasheng   | 100 | M      |
    |  26 | xietingfeng   |  23 | M      |
    |  27 | liudehua      |  18 | F      |
    |  28 | mahuateng     |  20 | M      |
    |  29 | wuyanzu       |  19 | M      |
    |  30 | wuzetian      |  21 | F      |
    |  31 | Song Jiang    |  18 | M      |
    |  32 | Zhang Sanfeng |  18 | M      |
    |  33 | Miejue Shitai |  18 | F      |
    |  34 | Lin Chaoying  |  18 | F      |
    |  38 | abc           |  20 | M      |
    +-----+---------------+-----+--------+
    39 rows in set (0.00 sec)
    
    
    MariaDB [hellodb]> select tid as id ,name,age,gender from teachers union select stuid,name,age,gender from s1;  (起个别名替换掉表头的tid并纵向合并两张表)
    +----+---------------+-----+--------+
    | id | name          | age | gender |
    +----+---------------+-----+--------+
    |  1 | Song Jiang    |  45 | M      |
    |  2 | Zhang Sanfeng |  94 | M      |
    |  3 | Miejue Shitai |  77 | F      |
    |  4 | Lin Chaoying  |  26 | F      |
    |  1 | Shi Zhongyu   |  22 | M      |
    |  2 | Shi Potian    |  22 | M      |
    |  3 | Xie Yanke     |  53 | M      |
    |  4 | Ding Dian     |  32 | M      |
    |  5 | Yu Yutong     |  26 | M      |
    |  6 | Shi Qing      |  46 | M      |
    |  7 | Xi Ren        |  19 | F      |
    |  8 | Lin Daiyu     |  17 | F      |
    |  9 | Ren Yingying  |  20 | F      |
    | 10 | Yue Lingshan  |  19 | F      |
    | 11 | Yuan Chengzhi |  23 | M      |
    | 12 | Wen Qingqing  |  19 | F      |
    | 13 | Tian Boguang  |  33 | M      |
    | 14 | Lu Wushuang   |  17 | F      |
    | 15 | Duan Yu       |  19 | M      |
    | 16 | Xu Zhu        |  21 | M      |
    | 17 | Lin Chong     |  25 | M      |
    | 18 | Hua Rong      |  23 | M      |
    | 19 | Xue Baochai   |  18 | F      |
    | 20 | Diao Chan     |  19 | F      |
    | 21 | Huang Yueying |  22 | F      |
    | 22 | Xiao Qiao     |  20 | F      |
    | 23 | Ma Chao       |  23 | M      |
    | 24 | Xu Xian       |  27 | M      |
    | 25 | Sun Dasheng   | 100 | M      |
    | 26 | xietingfeng   |  23 | M      |
    | 27 | liudehua      |  18 | F      |
    | 28 | mahuateng     |  20 | M      |
    | 29 | wuyanzu       |  19 | M      |
    | 30 | wuzetian      |  21 | F      |
    | 31 | Song Jiang    |  18 | M      |
    | 32 | Zhang Sanfeng |  18 | M      |
    | 33 | Miejue Shitai |  18 | F      |
    | 34 | Lin Chaoying  |  18 | F      |
    | 38 | abc           |  20 | M      |
    +----+---------------+-----+--------+
    39 rows in set (0.00 sec)
    
    1. union 自己和自己相连可以去重。
    MariaDB [hellodb]> create table t2 select * from teachers;  (先导一张表出来做实验不能有主键,所以只用了这种方法导了数据没有把主键导过来)
    Query OK, 4 rows affected (0.01 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    
    MariaDB [hellodb]> select * from t2;
    +-----+---------------+-----+--------+
    | TID | Name          | Age | Gender |
    +-----+---------------+-----+--------+
    |   1 | Song Jiang    |  45 | M      |
    |   2 | Zhang Sanfeng |  94 | M      |
    |   3 | Miejue Shitai |  77 | F      |
    |   4 | Lin Chaoying  |  26 | F      |
    +-----+---------------+-----+--------+
    4 rows in set (0.00 sec)
    
    MariaDB [hellodb]> desc t2;
    +--------+----------------------+------+-----+---------+-------+
    | Field  | Type                 | Null | Key | Default | Extra |
    +--------+----------------------+------+-----+---------+-------+
    | TID    | smallint(5) unsigned | NO   |     | 0       |       |
    | Name   | varchar(100)         | NO   |     | NULL    |       |
    | Age    | tinyint(3) unsigned  | NO   |     | NULL    |       |
    | Gender | enum('F','M')        | YES  |     | NULL    |       |
    +--------+----------------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)
    
    (添加重复的行做实验)
    MariaDB [hellodb]> insert into t2 set tid=4,name='linchaoying',age=26,gender='F';  (这条记录添加的只有name不一样少了个空格)
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [hellodb]> insert into t2 set tid=4,name='lin chaoying',age=26,gender='F';  (完全一样加了一行)
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [hellodb]> select * from t2;
    +-----+---------------+-----+--------+
    | TID | Name          | Age | Gender |
    +-----+---------------+-----+--------+
    |   1 | Song Jiang    |  45 | M      |
    |   2 | Zhang Sanfeng |  94 | M      |
    |   3 | Miejue Shitai |  77 | F      |
    |   4 | Lin Chaoying  |  26 | F      |
    |   4 | linchaoying   |  26 | F      |
    |   4 | lin chaoying  |  26 | F      |
    +-----+---------------+-----+--------+
    6 rows in set (0.00 sec)
    
    MariaDB [hellodb]> select * from t2 union select * from t2;  (用union过滤掉重复的行,少一个空格的那条记录过滤不了)
    +-----+---------------+-----+--------+
    | TID | Name          | Age | Gender |
    +-----+---------------+-----+--------+
    |   1 | Song Jiang    |  45 | M      |
    |   2 | Zhang Sanfeng |  94 | M      |
    |   3 | Miejue Shitai |  77 | F      |
    |   4 | Lin Chaoying  |  26 | F      |
    |   4 | linchaoying   |  26 | F      |
    +-----+---------------+-----+--------+
    5 rows in set (0.00 sec)
    
    1. 使用all 简单连接两张表不去重
    MariaDB [hellodb]> select * from t2 union all select * from t2;
    +-----+---------------+-----+--------+
    | TID | Name          | Age | Gender |
    +-----+---------------+-----+--------+
    |   1 | Song Jiang    |  45 | M      |
    |   2 | Zhang Sanfeng |  94 | M      |
    |   3 | Miejue Shitai |  77 | F      |
    |   4 | Lin Chaoying  |  26 | F      |
    |   4 | linchaoying   |  26 | F      |
    |   4 | lin chaoying  |  26 | F      |
    |   1 | Song Jiang    |  45 | M      |
    |   2 | Zhang Sanfeng |  94 | M      |
    |   3 | Miejue Shitai |  77 | F      |
    |   4 | Lin Chaoying  |  26 | F      |
    |   4 | linchaoying   |  26 | F      |
    |   4 | lin chaoying  |  26 | F      |
    +-----+---------------+-----+--------+
    12 rows in set (0.00 sec)
    

    交叉连接

    两张表横向组合,类似于笛卡尔乘积。  (cross join)
    
    1. 两张表使用交叉连接就是这张表的每一行去和另一张表的所有行组合一遍,形成新的行。
    MariaDB [hellodb]> select * from s1 cross join teachers;
    +-------+---------------+-------+-----+--------+---------+-----------+-----+---------------+-----+--------+
    | StuID | Name          | phone | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
    +-------+---------------+-------+-----+--------+---------+-----------+-----+---------------+-----+--------+
    |     1 | Shi Zhongyu   |  NULL |  22 | M      |       2 |         3 |   1 | Song Jiang    |  25 | M      |
    |     1 | Shi Zhongyu   |  NULL |  22 | M      |       2 |         3 |   2 | Zhang Sanfeng |  25 | M      |
    |     1 | Shi Zhongyu   |  NULL |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  25 | F      |
    |     1 | Shi Zhongyu   |  NULL |  22 | M      |       2 |         3 |   4 | Lin Chaoying  |  25 | F      |
    |     2 | Shi Potian    |  NULL |  22 | M      |       1 |         7 |   1 | Song Jiang    |  25 | M      |
    |     2 | Shi Potian    |  NULL |  22 | M      |       1 |         7 |   2 | Zhang Sanfeng |  25 | M      |
    |     2 | Shi Potian    |  NULL |  22 | M      |       1 |         7 |   3 | Miejue Shitai |  25 | F      |
    |     2 | Shi Potian    |  NULL |  22 | M      |       1 |         7 |   4 | Lin Chaoying  |  25 | F      |
    |     3 | Xie Yanke     |  NULL |  53 | M      |       2 |        16 |   1 | Song Jiang    |  25 | M      |
    |     3 | Xie Yanke     |  NULL |  53 | M      |       2 |        16 |   2 | Zhang Sanfeng |  25 | M      |
    |     3 | Xie Yanke     |  NULL |  53 | M      |       2 |        16 |   3 | Miejue Shitai |  25 | F      |
    |     3 | Xie Yanke     |  NULL |  53 | M      |       2 |        16 |   4 | Lin Chaoying  |  25 | F      |
    |     4 | Ding Dian     |  NULL |  32 | M      |       4 |         4 |   1 | Song Jiang    |  25 | M      |
    |     4 | Ding Dian     |  NULL |  32 | M      |       4 |         4 |   2 | Zhang Sanfeng |  25 | M      |
    |     4 | Ding Dian     |  NULL |  32 | M      |       4 |         4 |   3 | Miejue Shitai |  25 | F      |
    |     4 | Ding Dian     |  NULL |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  25 | F      |
    |     5 | Yu Yutong     |  NULL |  26 | M      |       3 |         1 |   1 | Song Jiang    |  25 | M      |
    |     5 | Yu Yutong     |  NULL |  26 | M      |       3 |         1 |   2 | Zhang Sanfeng |  25 | M      |
    |     5 | Yu Yutong     |  NULL |  26 | M      |       3 |         1 |   3 | Miejue Shitai |  25 | F      |
    |     5 | Yu Yutong     |  NULL |  26 | M      |       3 |         1 |   4 | Lin Chaoying  |  25 | F      |
    |     6 | Shi Qing      |  NULL |  46 | M      |       5 |      NULL |   1 | Song Jiang    |  25 | M      |
    |     6 | Shi Qing      |  NULL |  46 | M      |       5 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |     6 | Shi Qing      |  NULL |  46 | M      |       5 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |     6 | Shi Qing      |  NULL |  46 | M      |       5 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |     7 | Xi Ren        |  NULL |  19 | F      |       3 |      NULL |   1 | Song Jiang    |  25 | M      |
    |     7 | Xi Ren        |  NULL |  19 | F      |       3 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |     7 | Xi Ren        |  NULL |  19 | F      |       3 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |     7 | Xi Ren        |  NULL |  19 | F      |       3 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |     8 | Lin Daiyu     |  NULL |  17 | F      |       7 |      NULL |   1 | Song Jiang    |  25 | M      |
    |     8 | Lin Daiyu     |  NULL |  17 | F      |       7 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |     8 | Lin Daiyu     |  NULL |  17 | F      |       7 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |     8 | Lin Daiyu     |  NULL |  17 | F      |       7 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |     9 | Ren Yingying  |  NULL |  20 | F      |       6 |      NULL |   1 | Song Jiang    |  25 | M      |
    |     9 | Ren Yingying  |  NULL |  20 | F      |       6 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |     9 | Ren Yingying  |  NULL |  20 | F      |       6 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |     9 | Ren Yingying  |  NULL |  20 | F      |       6 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    10 | Yue Lingshan  |  NULL |  19 | F      |       3 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    10 | Yue Lingshan  |  NULL |  19 | F      |       3 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    10 | Yue Lingshan  |  NULL |  19 | F      |       3 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    10 | Yue Lingshan  |  NULL |  19 | F      |       3 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    11 | Yuan Chengzhi |  NULL |  23 | M      |       6 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    11 | Yuan Chengzhi |  NULL |  23 | M      |       6 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    11 | Yuan Chengzhi |  NULL |  23 | M      |       6 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    11 | Yuan Chengzhi |  NULL |  23 | M      |       6 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    12 | Wen Qingqing  |  NULL |  19 | F      |       1 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    12 | Wen Qingqing  |  NULL |  19 | F      |       1 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    12 | Wen Qingqing  |  NULL |  19 | F      |       1 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    12 | Wen Qingqing  |  NULL |  19 | F      |       1 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    13 | Tian Boguang  |  NULL |  33 | M      |       2 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    13 | Tian Boguang  |  NULL |  33 | M      |       2 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    13 | Tian Boguang  |  NULL |  33 | M      |       2 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    13 | Tian Boguang  |  NULL |  33 | M      |       2 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    14 | Lu Wushuang   |  NULL |  17 | F      |       3 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    14 | Lu Wushuang   |  NULL |  17 | F      |       3 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    14 | Lu Wushuang   |  NULL |  17 | F      |       3 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    14 | Lu Wushuang   |  NULL |  17 | F      |       3 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    15 | Duan Yu       |  NULL |  19 | M      |       4 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    15 | Duan Yu       |  NULL |  19 | M      |       4 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    15 | Duan Yu       |  NULL |  19 | M      |       4 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    15 | Duan Yu       |  NULL |  19 | M      |       4 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    16 | Xu Zhu        |  NULL |  21 | M      |       1 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    16 | Xu Zhu        |  NULL |  21 | M      |       1 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    16 | Xu Zhu        |  NULL |  21 | M      |       1 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    16 | Xu Zhu        |  NULL |  21 | M      |       1 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    17 | Lin Chong     |  NULL |  25 | M      |       4 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    17 | Lin Chong     |  NULL |  25 | M      |       4 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    17 | Lin Chong     |  NULL |  25 | M      |       4 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    17 | Lin Chong     |  NULL |  25 | M      |       4 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    18 | Hua Rong      |  NULL |  23 | M      |       7 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    18 | Hua Rong      |  NULL |  23 | M      |       7 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    18 | Hua Rong      |  NULL |  23 | M      |       7 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    18 | Hua Rong      |  NULL |  23 | M      |       7 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    19 | Xue Baochai   |  NULL |  18 | F      |       6 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    19 | Xue Baochai   |  NULL |  18 | F      |       6 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    19 | Xue Baochai   |  NULL |  18 | F      |       6 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    19 | Xue Baochai   |  NULL |  18 | F      |       6 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    20 | Diao Chan     |  NULL |  19 | F      |       7 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    20 | Diao Chan     |  NULL |  19 | F      |       7 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    20 | Diao Chan     |  NULL |  19 | F      |       7 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    20 | Diao Chan     |  NULL |  19 | F      |       7 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    21 | Huang Yueying |  NULL |  22 | F      |       6 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    21 | Huang Yueying |  NULL |  22 | F      |       6 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    21 | Huang Yueying |  NULL |  22 | F      |       6 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    21 | Huang Yueying |  NULL |  22 | F      |       6 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    22 | Xiao Qiao     |  NULL |  20 | F      |       1 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    22 | Xiao Qiao     |  NULL |  20 | F      |       1 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    22 | Xiao Qiao     |  NULL |  20 | F      |       1 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    22 | Xiao Qiao     |  NULL |  20 | F      |       1 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    23 | Ma Chao       |  NULL |  23 | M      |       4 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    23 | Ma Chao       |  NULL |  23 | M      |       4 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    23 | Ma Chao       |  NULL |  23 | M      |       4 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    23 | Ma Chao       |  NULL |  23 | M      |       4 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    24 | Xu Xian       |  NULL |  27 | M      |    NULL |      NULL |   1 | Song Jiang    |  25 | M      |
    |    24 | Xu Xian       |  NULL |  27 | M      |    NULL |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    24 | Xu Xian       |  NULL |  27 | M      |    NULL |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    24 | Xu Xian       |  NULL |  27 | M      |    NULL |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    25 | Sun Dasheng   |  NULL | 100 | M      |    NULL |      NULL |   1 | Song Jiang    |  25 | M      |
    |    25 | Sun Dasheng   |  NULL | 100 | M      |    NULL |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    25 | Sun Dasheng   |  NULL | 100 | M      |    NULL |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    25 | Sun Dasheng   |  NULL | 100 | M      |    NULL |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    26 | xietingfeng   |  NULL |  23 | M      |       2 |         1 |   1 | Song Jiang    |  25 | M      |
    |    26 | xietingfeng   |  NULL |  23 | M      |       2 |         1 |   2 | Zhang Sanfeng |  25 | M      |
    |    26 | xietingfeng   |  NULL |  23 | M      |       2 |         1 |   3 | Miejue Shitai |  25 | F      |
    |    26 | xietingfeng   |  NULL |  23 | M      |       2 |         1 |   4 | Lin Chaoying  |  25 | F      |
    |    27 | liudehua      |  NULL |  18 | F      |       1 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    27 | liudehua      |  NULL |  18 | F      |       1 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    27 | liudehua      |  NULL |  18 | F      |       1 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    27 | liudehua      |  NULL |  18 | F      |       1 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    28 | mahuateng     |  NULL |  20 | M      |       3 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    28 | mahuateng     |  NULL |  20 | M      |       3 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    28 | mahuateng     |  NULL |  20 | M      |       3 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    28 | mahuateng     |  NULL |  20 | M      |       3 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    29 | wuyanzu       |  NULL |  19 | M      |       4 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    29 | wuyanzu       |  NULL |  19 | M      |       4 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    29 | wuyanzu       |  NULL |  19 | M      |       4 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    29 | wuyanzu       |  NULL |  19 | M      |       4 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    30 | wuzetian      |  NULL |  21 | F      |    NULL |      NULL |   1 | Song Jiang    |  25 | M      |
    |    30 | wuzetian      |  NULL |  21 | F      |    NULL |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    30 | wuzetian      |  NULL |  21 | F      |    NULL |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    30 | wuzetian      |  NULL |  21 | F      |    NULL |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    31 | Song Jiang    |  NULL |  18 | M      |      45 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    31 | Song Jiang    |  NULL |  18 | M      |      45 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    31 | Song Jiang    |  NULL |  18 | M      |      45 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    31 | Song Jiang    |  NULL |  18 | M      |      45 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    32 | Zhang Sanfeng |  NULL |  18 | M      |      94 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    32 | Zhang Sanfeng |  NULL |  18 | M      |      94 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    32 | Zhang Sanfeng |  NULL |  18 | M      |      94 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    32 | Zhang Sanfeng |  NULL |  18 | M      |      94 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    33 | Miejue Shitai |  NULL |  18 | F      |      77 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    33 | Miejue Shitai |  NULL |  18 | F      |      77 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    33 | Miejue Shitai |  NULL |  18 | F      |      77 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    33 | Miejue Shitai |  NULL |  18 | F      |      77 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    34 | Lin Chaoying  |  NULL |  18 | F      |      93 |      NULL |   1 | Song Jiang    |  25 | M      |
    |    34 | Lin Chaoying  |  NULL |  18 | F      |      93 |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    34 | Lin Chaoying  |  NULL |  18 | F      |      93 |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    34 | Lin Chaoying  |  NULL |  18 | F      |      93 |      NULL |   4 | Lin Chaoying  |  25 | F      |
    |    38 | abc           |  NULL |  20 | M      |    NULL |      NULL |   1 | Song Jiang    |  25 | M      |
    |    38 | abc           |  NULL |  20 | M      |    NULL |      NULL |   2 | Zhang Sanfeng |  25 | M      |
    |    38 | abc           |  NULL |  20 | M      |    NULL |      NULL |   3 | Miejue Shitai |  25 | F      |
    |    38 | abc           |  NULL |  20 | M      |    NULL |      NULL |   4 | Lin Chaoying  |  25 | F      |
    +-------+---------------+-------+-----+--------+---------+-----------+-----+---------------+-----+--------+
    140 rows in set (0.00 sec)
    
    
    第一张表   
                    0  1  2  3
                    1  2  3  4
    第二张表   
                    1  3  4  5
                    2  3  4  5
    交叉连接后结果:
                    0 1 2 3   1 3 4 5
                    0 1 2 3   2 3 4 5
                    1 2 3 4   1 3 4 5
                    1 2 3 4   2 3 4 5
    两张表换下位置不影响数据只是显示效果变了而已:
                    1 3 4 5   0 1 2 3
                    1 3 4 5   1 2 3 4
                    2 3 4 5   0 1 2 3
                    2 3 4 5   1 2 3 4
    
    MariaDB [hellodb]> select * from teachers , s1;  (这个命令也可以交叉连接但是比较老了推荐使用第一种)
    +-----+---------------+-----+--------+-------+---------------+-------+-----+--------+---------+-----------+
    | TID | Name          | Age | Gender | StuID | Name          | phone | Age | Gender | ClassID | TeacherID |
    +-----+---------------+-----+--------+-------+---------------+-------+-----+--------+---------+-----------+
    |   1 | Song Jiang    |  25 | M      |     1 | Shi Zhongyu   |  NULL |  22 | M      |       2 |         3 |
    |   2 | Zhang Sanfeng |  25 | M      |     1 | Shi Zhongyu   |  NULL |  22 | M      |       2 |         3 |
    |   3 | Miejue Shitai |  25 | F      |     1 | Shi Zhongyu   |  NULL |  22 | M      |       2 |         3 |
    |   4 | Lin Chaoying  |  25 | F      |     1 | Shi Zhongyu   |  NULL |  22 | M      |       2 |         3 |
    |   1 | Song Jiang    |  25 | M      |     2 | Shi Potian    |  NULL |  22 | M      |       1 |         7 |
    |   2 | Zhang Sanfeng |  25 | M      |     2 | Shi Potian    |  NULL |  22 | M      |       1 |         7 |
    |   3 | Miejue Shitai |  25 | F      |     2 | Shi Potian    |  NULL |  22 | M      |       1 |         7 |
    |   4 | Lin Chaoying  |  25 | F      |     2 | Shi Potian    |  NULL |  22 | M      |       1 |         7 |
    |   1 | Song Jiang    |  25 | M      |     3 | Xie Yanke     |  NULL |  53 | M      |       2 |        16 |
    |   2 | Zhang Sanfeng |  25 | M      |     3 | Xie Yanke     |  NULL |  53 | M      |       2 |        16 |
    |   3 | Miejue Shitai |  25 | F      |     3 | Xie Yanke     |  NULL |  53 | M      |       2 |        16 |
    |   4 | Lin Chaoying  |  25 | F      |     3 | Xie Yanke     |  NULL |  53 | M      |       2 |        16 |
    
    
    1. 挑出两张表的个别字段。
    MariaDB [hellodb]> select name,age,gender from teachers cross join s1; (这里有两个字段是重复的name,age两个表都有)
    ERROR 1052 (23000): Column 'name' in field list is ambiguous
    
    MariaDB [hellodb]> select stuid,s1.name,tid,teachers.name from teachers cross join s1; (分别指定是哪个表的name)
    +-------+---------------+-----+---------------+
    | stuid | name          | tid | name          |
    +-------+---------------+-----+---------------+
    |     1 | Shi Zhongyu   |   1 | Song Jiang    |
    |     1 | Shi Zhongyu   |   2 | Zhang Sanfeng |
    |     1 | Shi Zhongyu   |   3 | Miejue Shitai |
    |     1 | Shi Zhongyu   |   4 | Lin Chaoying  |
    |     2 | Shi Potian    |   1 | Song Jiang    |
    |     2 | Shi Potian    |   2 | Zhang Sanfeng |
    |     2 | Shi Potian    |   3 | Miejue Shitai |
    |     2 | Shi Potian    |   4 | Lin Chaoying  |
    |     3 | Xie Yanke     |   1 | Song Jiang    |
    |     3 | Xie Yanke     |   2 | Zhang Sanfeng |
    |     3 | Xie Yanke     |   3 | Miejue Shitai |
    |     3 | Xie Yanke     |   4 | Lin Chaoying  |
    |     4 | Ding Dian     |   1 | Song Jiang    |
    |     4 | Ding Dian     |   2 | Zhang Sanfeng |
    |     4 | Ding Dian     |   3 | Miejue Shitai |
    |     4 | Ding Dian     |   4 | Lin Chaoying  |
    |     5 | Yu Yutong     |   1 | Song Jiang    |
    |     5 | Yu Yutong     |   2 | Zhang Sanfeng |
    |     5 | Yu Yutong     |   3 | Miejue Shitai |
    |     5 | Yu Yutong     |   4 | Lin Chaoying  |
    |     6 | Shi Qing      |   1 | Song Jiang    |
    |     6 | Shi Qing      |   2 | Zhang Sanfeng |
    |     6 | Shi Qing      |   3 | Miejue Shitai |
    |     6 | Shi Qing      |   4 | Lin Chaoying  |
    |     7 | Xi Ren        |   1 | Song Jiang    |
    |     7 | Xi Ren        |   2 | Zhang Sanfeng |
    |     7 | Xi Ren        |   3 | Miejue Shitai |
    |     7 | Xi Ren        |   4 | Lin Chaoying  |
    |     8 | Lin Daiyu     |   1 | Song Jiang    |
    |     8 | Lin Daiyu     |   2 | Zhang Sanfeng |
    |     8 | Lin Daiyu     |   3 | Miejue Shitai |
    |     8 | Lin Daiyu     |   4 | Lin Chaoying  |
    |     9 | Ren Yingying  |   1 | Song Jiang    |
    |     9 | Ren Yingying  |   2 | Zhang Sanfeng |
    |     9 | Ren Yingying  |   3 | Miejue Shitai |
    |     9 | Ren Yingying  |   4 | Lin Chaoying  |
    |    10 | Yue Lingshan  |   1 | Song Jiang    |
    |    10 | Yue Lingshan  |   2 | Zhang Sanfeng |
    |    10 | Yue Lingshan  |   3 | Miejue Shitai |
    |    10 | Yue Lingshan  |   4 | Lin Chaoying  |
    |    11 | Yuan Chengzhi |   1 | Song Jiang    |
    |    11 | Yuan Chengzhi |   2 | Zhang Sanfeng |
    |    11 | Yuan Chengzhi |   3 | Miejue Shitai |
    |    11 | Yuan Chengzhi |   4 | Lin Chaoying  |
                    (省略了太长)
    
    MariaDB [hellodb]> select stuid,s1.name,s1.age,tid,teachers.name,teachers.age from teachers cross join s1; (name和age都可以加在里面指定)
    +-------+---------------+-----+-----+---------------+-----+
    | stuid | name          | age | tid | name          | age |
    +-------+---------------+-----+-----+---------------+-----+
    |     1 | Shi Zhongyu   |  22 |   1 | Song Jiang    |  25 |
    |     1 | Shi Zhongyu   |  22 |   2 | Zhang Sanfeng |  25 |
    |     1 | Shi Zhongyu   |  22 |   3 | Miejue Shitai |  25 |
    |     1 | Shi Zhongyu   |  22 |   4 | Lin Chaoying  |  25 |
    |     2 | Shi Potian    |  22 |   1 | Song Jiang    |  25 |
    |     2 | Shi Potian    |  22 |   2 | Zhang Sanfeng |  25 |
    |     2 | Shi Potian    |  22 |   3 | Miejue Shitai |  25 |
    |     2 | Shi Potian    |  22 |   4 | Lin Chaoying  |  25 |
    |     3 | Xie Yanke     |  53 |   1 | Song Jiang    |  25 |
    |     3 | Xie Yanke     |  53 |   2 | Zhang Sanfeng |  25 |
    |     3 | Xie Yanke     |  53 |   3 | Miejue Shitai |  25 |
    |     3 | Xie Yanke     |  53 |   4 | Lin Chaoying  |  25 |
    |     4 | Ding Dian     |  32 |   1 | Song Jiang    |  25 |
    |     4 | Ding Dian     |  32 |   2 | Zhang Sanfeng |  25 |
    |     4 | Ding Dian     |  32 |   3 | Miejue Shitai |  25 |
    |     4 | Ding Dian     |  32 |   4 | Lin Chaoying  |  25 |
    |     5 | Yu Yutong     |  26 |   1 | Song Jiang    |  25 |
    |     5 | Yu Yutong     |  26 |   2 | Zhang Sanfeng |  25 |
    |     5 | Yu Yutong     |  26 |   3 | Miejue Shitai |  25 |
    |     5 | Yu Yutong     |  26 |   4 | Lin Chaoying  |  25 |
                        (省略了太长)
    
    MariaDB [hellodb]> select stuid,s1.name as s1_name,tid,teachers.name as teachers_name from teachers cross join s1;    (也可以加上别名来区分比较清晰)
    +-------+---------------+-----+---------------+
    | stuid | s1_name       | tid | teachers_name |
    +-------+---------------+-----+---------------+
    |     1 | Shi Zhongyu   |   1 | Song Jiang    |
    |     1 | Shi Zhongyu   |   2 | Zhang Sanfeng |
    |     1 | Shi Zhongyu   |   3 | Miejue Shitai |
    |     1 | Shi Zhongyu   |   4 | Lin Chaoying  |
    |     2 | Shi Potian    |   1 | Song Jiang    |
    |     2 | Shi Potian    |   2 | Zhang Sanfeng |
    |     2 | Shi Potian    |   3 | Miejue Shitai |
    |     2 | Shi Potian    |   4 | Lin Chaoying  |
    |     3 | Xie Yanke     |   1 | Song Jiang    |
    |     3 | Xie Yanke     |   2 | Zhang Sanfeng |
    |     3 | Xie Yanke     |   3 | Miejue Shitai |
    |     3 | Xie Yanke     |   4 | Lin Chaoying  |
                      (省略)
    
    1. 对表起别名利用表的别名来查询数据。(定义别名是在字段的最后用在前面用,只在当前字段生效)
    MariaDB [hellodb]> select stuid,s.name as s1_name,s.age,t.name as teachers_name,t.age from teachers t cross join s1 s;
    +-------+---------------+-----+---------------+-----+
    | stuid | s1_name       | age | teachers_name | age |
    +-------+---------------+-----+---------------+-----+
    |     1 | Shi Zhongyu   |  22 | Song Jiang    |  25 |
    |     1 | Shi Zhongyu   |  22 | Zhang Sanfeng |  25 |
    |     1 | Shi Zhongyu   |  22 | Miejue Shitai |  25 |
    |     1 | Shi Zhongyu   |  22 | Lin Chaoying  |  25 |
    |     2 | Shi Potian    |  22 | Song Jiang    |  25 |
    |     2 | Shi Potian    |  22 | Zhang Sanfeng |  25 |
    |     2 | Shi Potian    |  22 | Miejue Shitai |  25 |
    |     2 | Shi Potian    |  22 | Lin Chaoying  |  25 |
    |     3 | Xie Yanke     |  53 | Song Jiang    |  25 |
    |     3 | Xie Yanke     |  53 | Zhang Sanfeng |  25 |
    |     3 | Xie Yanke     |  53 | Miejue Shitai |  25 |
    |     3 | Xie Yanke     |  53 | Lin Chaoying  |  25 |
    |     4 | Ding Dian     |  32 | Song Jiang    |  25 |
    |     4 | Ding Dian     |  32 | Zhang Sanfeng |  25 |
    |     4 | Ding Dian     |  32 | Miejue Shitai |  25 |
    |     4 | Ding Dian     |  32 | Lin Chaoying  |  25 |
    
    MariaDB [hellodb]> select stuid,s1.name s1_name,s1.age,tid,t.name teacher_name,teachers.age from teachers t cross join s1;   (别名定义之后不能使用原始名字)
    ERROR 1054 (42S22): Unknown column 'teachers.age' in 'field list'
    

    内连接

    等值连接:让表之间的字段以“等值”建立连接关系
    
    1. 把两个表有交集的地方连接起来
    MariaDB [hellodb]> select * from s1 inner join t1 on s1.teacherid=t1.tid;  (三个个老师各教一个学生)
    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
    | StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
    |     5 | Yu Yutong   |  26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
    |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
    |     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
    3 rows in set (0.00 sec)
    
    
    MariaDB [hellodb]> update s1 set teacherid=1 where stuid=25;  (修改一下s1表的teacherid的值为1)
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    MariaDB [hellodb]> select * from s1 inner join t1 on s1.teacherid=t1.tid; (再次使用这条命令,查看就是songjiang教两个学生)
    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
    | StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
    |     5 | Yu Yutong   |  26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
    |    25 | Sun Dasheng | 100 | M      |    NULL |         1 |   1 | Song Jiang    |  45 | M      |
    |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
    |     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
    4 rows in set (0.00 sec)
    
    MariaDB [hellodb]> select * from s1 , t1 where s1.teacherid=t1.tid;  (不加 inner join 的老写法)
    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
    | StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
    |     5 | Yu Yutong   |  26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
    |    25 | Sun Dasheng | 100 | M      |    NULL |         1 |   1 | Song Jiang    |  45 | M      |
    |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
    |     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
    4 rows in set (0.01 sec)
    
    
    1. 内连接之后过滤:先连接再过滤,显示s1表大于30的人。
    MariaDB [hellodb]> select * from s1 inner join t1 on s1.teacherid=t1.tid and s1.age >30;
    +-------+-------------+-----+--------+---------+-----------+-----+--------------+-----+--------+
    | StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name         | Age | Gender |
    +-------+-------------+-----+--------+---------+-----------+-----+--------------+-----+--------+
    |    25 | Sun Dasheng | 100 | M      |    NULL |         1 |   1 | Song Jiang   |  45 | M      |
    |     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying |  93 | F      |
    +-------+-------------+-----+--------+---------+-----------+-----+--------------+-----+--------+
    2 rows in set (0.00 sec)
    
    1. 查询完之后过滤
    MariaDB [hellodb]> select * from s1 inner join t1 on s1.teacherid=t1.tid where s1.a
    +-------+-------------+-----+--------+---------+-----------+-----+--------------+--
    | StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name         | A
    +-------+-------------+-----+--------+---------+-----------+-----+--------------+--
    |    25 | Sun Dasheng | 100 | M      |    NULL |         1 |   1 | Song Jiang   |  
    |     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying |  
    +-------+-------------+-----+--------+---------+-----------+-----+--------------+--
    2 rows in set (0.00 sec)
    

    外连接

    外连接:
        左外连接:FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col   (排在前面的)
        右外连接:FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col   (排在后面的)
    

    左外连接 left outer

    在这里插入图片描述

    1. 学生表全留下来老师的只留下来有交集的地方。 (没有交集的地方空值代替)
    MariaDB [hellodb]> select * from s1 left outer join t1 on s1.teacherid=t1.tid;  
    +-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
    | StuID | Name          | Age | Gender | ClassID | TeacherID | TID  | Name          | Age  | Gender |
    +-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
    |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |    3 | Miejue Shitai |   77 | F      |
    |     2 | Shi Potian    |  22 | M      |       1 |         7 | NULL | NULL          | NULL | NULL   |
    |     3 | Xie Yanke     |  53 | M      |       2 |        16 | NULL | NULL          | NULL | NULL   |
    |     4 | Ding Dian     |  32 | M      |       4 |         4 |    4 | Lin Chaoying  |   93 | F      |
    |     5 | Yu Yutong     |  26 | M      |       3 |         1 |    1 | Song Jiang    |   45 | M      |
    |     6 | Shi Qing      |  46 | M      |       5 |      NULL | NULL | NULL          | NULL | NULL   |
    |     7 | Xi Ren        |  19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
    |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
    |     9 | Ren Yingying  |  20 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
    |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
    |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
    |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
    |    13 | Tian Boguang  |  33 | M      |       2 |      NULL | NULL | NULL          | NULL | NULL   |
    |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
    |    15 | Duan Yu       |  19 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
    |    16 | Xu Zhu        |  21 | M      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
    |    17 | Lin Chong     |  25 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
    |    18 | Hua Rong      |  23 | M      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
    |    19 | Xue Baochai   |  18 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
    |    20 | Diao Chan     |  19 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
    |    21 | Huang Yueying |  22 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
    |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
    |    23 | Ma Chao       |  23 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
    |    24 | Xu Xian       |  27 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
    |    25 | Sun Dasheng   | 100 | M      |    NULL |         1 |    1 | Song Jiang    |   45 | M      |
    +-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
    25 rows in set (0.00 sec)
    
    
    1. 左外连接扩展用法
      在这里插入图片描述
    • 取出没有老师教的学生 (用where指定)
    MariaDB [hellodb]> select * from t1;  (教师表)
    +-----+---------------+-----+--------+
    | TID | Name          | Age | Gender |
    +-----+---------------+-----+--------+
    |   1 | Song Jiang    |  45 | M      |
    |   2 | Zhang Sanfeng |  94 | M      |
    |   3 | Miejue Shitai |  77 | F      |
    |   4 | Lin Chaoying  |  93 | F      |
    +-----+---------------+-----+--------+
    4 rows in set (0.00 sec)
    
    MariaDB [hellodb]> select * from s1 left outer join t1 on s1.teacherid=t1.tid where tid is null; (前面两个,老师表里没有这两个老师的编号)
    +-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
    | StuID | Name          | Age | Gender | ClassID | TeacherID | TID  | Name | Age  | Gender |
    +-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
    |     2 | Shi Potian    |  22 | M      |       1 |         7 | NULL | NULL | NULL | NULL   |
    |     3 | Xie Yanke     |  53 | M      |       2 |        16 | NULL | NULL | NULL | NULL   |
    |     6 | Shi Qing      |  46 | M      |       5 |      NULL | NULL | NULL | NULL | NULL   |
    |     7 | Xi Ren        |  19 | F      |       3 |      NULL | NULL | NULL | NULL | NULL   |
    |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL | NULL | NULL | NULL | NULL   |
    |     9 | Ren Yingying  |  20 | F      |       6 |      NULL | NULL | NULL | NULL | NULL   |
    |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL | NULL | NULL | NULL | NULL   |
    |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL | NULL | NULL | NULL | NULL   |
    |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL | NULL | NULL | NULL | NULL   |
    |    13 | Tian Boguang  |  33 | M      |       2 |      NULL | NULL | NULL | NULL | NULL   |
    |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL | NULL | NULL | NULL | NULL   |
    |    15 | Duan Yu       |  19 | M      |       4 |      NULL | NULL | NULL | NULL | NULL   |
    |    16 | Xu Zhu        |  21 | M      |       1 |      NULL | NULL | NULL | NULL | NULL   |
    |    17 | Lin Chong     |  25 | M      |       4 |      NULL | NULL | NULL | NULL | NULL   |
    |    18 | Hua Rong      |  23 | M      |       7 |      NULL | NULL | NULL | NULL | NULL   |
    |    19 | Xue Baochai   |  18 | F      |       6 |      NULL | NULL | NULL | NULL | NULL   |
    |    20 | Diao Chan     |  19 | F      |       7 |      NULL | NULL | NULL | NULL | NULL   |
    |    21 | Huang Yueying |  22 | F      |       6 |      NULL | NULL | NULL | NULL | NULL   |
    |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL | NULL | NULL | NULL | NULL   |
    |    23 | Ma Chao       |  23 | M      |       4 |      NULL | NULL | NULL | NULL | NULL   |
    |    24 | Xu Xian       |  27 | M      |    NULL |      NULL | NULL | NULL | NULL | NULL   |
    +-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
    21 rows in set (0.00 sec)
    
    

    右外链接:right outer

    在这里插入图片描述

    1. 老师表全留下来,学生表有交集的地方留下来。
    MariaDB [hellodb]> select * from s1 right outer join t1 on s1.teacherid=t1.tid;
    +-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
    | StuID | Name        | Age  | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
    +-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
    |     1 | Shi Zhongyu |   22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
    |     4 | Ding Dian   |   32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
    |     5 | Yu Yutong   |   26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
    |    25 | Sun Dasheng |  100 | M      |    NULL |         1 |   1 | Song Jiang    |  45 | M      |
    |  NULL | NULL        | NULL | NULL   |    NULL |      NULL |   2 | Zhang Sanfeng |  94 | M      |
    +-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
    5 rows in set (0.00 sec)
    
    
    1. 右外连接扩展用法
      在这里插入图片描述
    • 没有教学生的老师留下来。 (和左外连接的逻辑是一样的)
    MariaDB [hellodb]> select * from s1 right outer join t1 on s1.teacherid=t1.tid where s1.teacherid is null;
    +-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+
    | StuID | Name | Age  | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
    +-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+
    |  NULL | NULL | NULL | NULL   |    NULL |      NULL |   2 | Zhang Sanfeng |  94 | M      |
    +-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+
    1 row in set (0.00 sec)
    
    

    完全外连接

    和交叉连接不一样     mysql不支持full outer join
    

    在这里插入图片描述

    1. 把左外连接和右外连接用union联合起来,有交集的地方对应,没有交集的也地方输出出来。
    MariaDB [hellodb]> select * from s1 left outer join t1 on s1.teacherid=t1.tid
        -> union
        -> select * from s1 right outer join t1 on s1.teacherid=t1.tid;
    +-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
    | StuID | Name          | Age  | Gender | ClassID | TeacherID | TID  | Name          | Age  | Gender |
    +-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
    |     1 | Shi Zhongyu   |   22 | M      |       2 |         3 |    3 | Miejue Shitai |   77 | F      |
    |     2 | Shi Potian    |   22 | M      |       1 |         7 | NULL | NULL          | NULL | NULL   |
    |     3 | Xie Yanke     |   53 | M      |       2 |        16 | NULL | NULL          | NULL | NULL   |
    |     4 | Ding Dian     |   32 | M      |       4 |         4 |    4 | Lin Chaoying  |   93 | F      |
    |     5 | Yu Yutong     |   26 | M      |       3 |         1 |    1 | Song Jiang    |   45 | M      |
    |     6 | Shi Qing      |   46 | M      |       5 |      NULL | NULL | NULL          | NULL | NULL   |
    |     7 | Xi Ren        |   19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
    |     8 | Lin Daiyu     |   17 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
    |     9 | Ren Yingying  |   20 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
    |    10 | Yue Lingshan  |   19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
    |    11 | Yuan Chengzhi |   23 | M      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
    |    12 | Wen Qingqing  |   19 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
    |    13 | Tian Boguang  |   33 | M      |       2 |      NULL | NULL | NULL          | NULL | NULL   |
    |    14 | Lu Wushuang   |   17 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
    |    15 | Duan Yu       |   19 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
    |    16 | Xu Zhu        |   21 | M      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
    |    17 | Lin Chong     |   25 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
    |    18 | Hua Rong      |   23 | M      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
    |    19 | Xue Baochai   |   18 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
    |    20 | Diao Chan     |   19 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
    |    21 | Huang Yueying |   22 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
    |    22 | Xiao Qiao     |   20 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
    |    23 | Ma Chao       |   23 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
    |    24 | Xu Xian       |   27 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
    |    25 | Sun Dasheng   |  100 | M      |    NULL |         1 |    1 | Song Jiang    |   45 | M      |
    |  NULL | NULL          | NULL | NULL   |    NULL |      NULL |    2 | Zhang Sanfeng |   94 | M      |
    +-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
    26 rows in set (0.00 sec)
    
    
    1. 完全外连接扩展用法:有交集的地方去除掉,只留外连接。
      在这里插入图片描述
    • 把没有老师的学生,和没有学生的老师取出来。
    MariaDB [hellodb]> select * from (select s.stuid,s.name s_name,s.teacherid,t.tid,t.name t_name from s1 s left outer join t1  t on s.teacherid=t.tid union select s.stuid,s.name,s.teacherid,t.tid,t.name from s1 s right outer join t1 t on s.teacherid=t.tid) as a where a.teacherid is null or a.tid is null;
    +-------+---------------+-----------+------+---------------+
    | stuid | s_name        | teacherid | tid  | t_name        |
    +-------+---------------+-----------+------+---------------+
    |     2 | Shi Potian    |         7 | NULL | NULL          |
    |     3 | Xie Yanke     |        16 | NULL | NULL          |
    |     6 | Shi Qing      |      NULL | NULL | NULL          |
    |     7 | Xi Ren        |      NULL | NULL | NULL          |
    |     8 | Lin Daiyu     |      NULL | NULL | NULL          |
    |     9 | Ren Yingying  |      NULL | NULL | NULL          |
    |    10 | Yue Lingshan  |      NULL | NULL | NULL          |
    |    11 | Yuan Chengzhi |      NULL | NULL | NULL          |
    |    12 | Wen Qingqing  |      NULL | NULL | NULL          |
    |    13 | Tian Boguang  |      NULL | NULL | NULL          |
    |    14 | Lu Wushuang   |      NULL | NULL | NULL          |
    |    15 | Duan Yu       |      NULL | NULL | NULL          |
    |    16 | Xu Zhu        |      NULL | NULL | NULL          |
    |    17 | Lin Chong     |      NULL | NULL | NULL          |
    |    18 | Hua Rong      |      NULL | NULL | NULL          |
    |    19 | Xue Baochai   |      NULL | NULL | NULL          |
    |    20 | Diao Chan     |      NULL | NULL | NULL          |
    |    21 | Huang Yueying |      NULL | NULL | NULL          |
    |    22 | Xiao Qiao     |      NULL | NULL | NULL          |
    |    23 | Ma Chao       |      NULL | NULL | NULL          |
    |    24 | Xu Xian       |      NULL | NULL | NULL          |
    |  NULL | NULL          |      NULL |    2 | Zhang Sanfeng |
    +-------+---------------+-----------+------+---------------+
    22 rows in set (0.00 sec)
    
    

    三张表查询

    取学生姓名,成绩,科目。
    
    1. 先取出学生的姓名和成绩 (分两步做思路清晰一点)
    MariaDB [hellodb]> select st.name,sc.courseid,sc.score from s1 st inner join scores sc on st.stuid=sc.stuid;
    +-------------+----------+-------+
    | name        | courseid | score |
    +-------------+----------+-------+
    | Shi Zhongyu |        2 |    77 |
    | Shi Zhongyu |        6 |    93 |
    | Shi Potian  |        2 |    47 |
    | Shi Potian  |        5 |    97 |
    | Xie Yanke   |        2 |    88 |
    | Xie Yanke   |        6 |    75 |
    | Ding Dian   |        5 |    71 |
    | Ding Dian   |        2 |    89 |
    | Yu Yutong   |        1 |    39 |
    | Yu Yutong   |        7 |    63 |
    | Shi Qing    |        1 |    96 |
    | Xi Ren      |        1 |    86 |
    | Xi Ren      |        7 |    83 |
    | Lin Daiyu   |        4 |    57 |
    | Lin Daiyu   |        3 |    93 |
    +-------------+----------+-------+
    15 rows in set (0.00 sec)
    
    
    1. 在连接一次取出学生姓名,成绩,科目。
    MariaDB [hellodb]> select st.name,co.course,sc.score from s1 st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid;
    +-------------+----------------+-------+
    | name        | course         | score |
    +-------------+----------------+-------+
    | Shi Zhongyu | Kuihua Baodian |    77 |
    | Shi Zhongyu | Weituo Zhang   |    93 |
    | Shi Potian  | Kuihua Baodian |    47 |
    | Shi Potian  | Daiyu Zanghua  |    97 |
    | Xie Yanke   | Kuihua Baodian |    88 |
    | Xie Yanke   | Weituo Zhang   |    75 |
    | Ding Dian   | Daiyu Zanghua  |    71 |
    | Ding Dian   | Kuihua Baodian |    89 |
    | Yu Yutong   | Hamo Gong      |    39 |
    | Yu Yutong   | Dagou Bangfa   |    63 |
    | Shi Qing    | Hamo Gong      |    96 |
    | Xi Ren      | Hamo Gong      |    86 |
    | Xi Ren      | Dagou Bangfa   |    83 |
    | Lin Daiyu   | Taiji Quan     |    57 |
    | Lin Daiyu   | Jinshe Jianfa  |    93 |
    +-------------+----------------+-------+
    15 rows in set (0.00 sec)
    
    
    严禁出现四张表join的情况
    

    自连接

    自联结顾名思义就是把一张表假设为两张一样的表,然后在做“多表查询”
    
    1. 先构建一张表
    MariaDB [hellodb]> create table emp (id int, name char(20),leaderid int);
    Query OK, 0 rows affected (0.01 sec)
    
    MariaDB [hellodb]> insert emp value(1,'huangshang',null);
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [hellodb]> insert emp value(2,'taihou','huangshang');
    ERROR 1366 (22007): Incorrect integer value: 'huangshang' for column `hellodb`.`emp`.`leaderid` at row 1
    MariaDB [hellodb]> insert emp value(2,'taihou',1);
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [hellodb]> insert emp value(3,'guifei',2);
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [hellodb]> insert emp value(4,'shufei',3);
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [hellodb]> select * from emp;
    +------+------------+----------+
    | id   | name       | leaderid |
    +------+------------+----------+
    |    1 | huangshang |     NULL |
    |    2 | taihou     |        1 |
    |    3 | guifei     |        2 |
    |    4 | shufei     |        3 |
    +------+------------+----------+
    4 rows in set (0.00 sec)
    
    1. 查询表里的上级的姓名。
    • 想象为两张表 员工表 和上司表 起别名做成
    MariaDB [hellodb]> select * from emp as e inner join emp as l on e.leaderid=l.id;
    +------+--------+----------+------+------------+----------+
    | id   | name   | leaderid | id   | name       | leaderid |
    +------+--------+----------+------+------------+----------+
    |    2 | taihou |        1 |    1 | huangshang |     NULL |
    |    3 | guifei |        2 |    2 | taihou     |        1 |
    |    4 | shufei |        3 |    3 | guifei     |        2 |
    +------+--------+----------+------+------------+----------+
    3 rows in set (0.00 sec)
    
    • 取出来对应的上级,但是缺失了最上级。
    MariaDB [hellodb]> select e.name emp,l.name leader from emp as e inner join emp as l on e.leaderid=l.id;
    +--------+------------+
    | emp    | leader     |
    +--------+------------+
    | taihou | huangshang |
    | guifei | taihou     |
    | shufei | guifei     |
    +--------+------------+
    3 rows in set (0.00 sec)
    
    • 取出每个人对应的上级的id。
    MariaDB [hellodb]> select e.name emp,l.name leader from emp as e left join emp as l on e.leaderid=l.id;
    +------------+------------+
    | emp        | leader     |
    +------------+------------+
    | taihou     | huangshang |
    | guifei     | taihou     |
    | shufei     | guifei     |
    | huangshang | NULL       |
    +------------+------------+
    4 rows in set (0.00 sec)
    

    SQL语句的关键字执行顺序
    在这里插入图片描述

    展开全文
  • 数据库多表查询

    2021-01-19 09:59:56
    多表查询查询语法: SELECT ... FROM ... WHERE ... 案例 : 创建部门员工表进行后续操作 多表查询: 笛卡尔积:有2个集合A和B 取这两个集合的所有的组成情况 要完成多表查询需要消除冗余数据 多表...

    多表查询查询语法:

    SELECT 
    ...

    FROM

    ...

    WHERE

    ...

    案例 : 创建部门员工表进行后续操作

    多表查询:

    笛卡尔积:有2个集合A和B 取这两个集合的所有的组成情况
                      要完成多表查询需要消除冗余数据

    多表查询的分类 :

    1.内连接查询
        1.隐式内连接:使用where条件消除多余的数据

    取别名替换

        2.显示内连接

    SELECT 字段列表   FROM 表名1  INNER JOIN 表名2 ON 条件 

    2.外连接查询

    左外连接 SELECT 字段列表 FROM 表名1 LEFT JOIN 表名2 ON 条件  :查询的是左边表所有的记录以及其交集
    右外连接  SELECT 字段列表 FROM 表名1 RIGHT  JOIN 表名2 ON 条件

    3.子查询:

    查询中嵌套查询:嵌套的查询叫做子查询

    子查询的不同种情况
    结果是单行单列:子查询可以用作条件,使用运算符去判断


    结果是单行多列:子查询可以作为条件使用运算符IN来判断

    结果是多行多列:子查询可以做为一张虚拟表
    查询员工入职日期

    展开全文
  • oracle数据库多表条件查询语句

    千次阅读 2018-12-28 13:11:57
    SELECT a.id,a.guid,a.col1,a.col2 FROM a INNER JOIN b ON a.guid = b.a_guid WHERE a.set = 'xxx' -- 必选条件
  • 数据库有三个,stu属性有xh,xm,bj;course属性有kch,kcm,xq,xn;cj属性有xh,kch,cj 现在需要把三个链接查询,写了如下代码 if isnumeric(xh)=false then sql_xh="" else sql_xh=" and stu.xh='"&...
  • 数据库多表查询 内连接查询 (1) 隐式内连接 :使用where条件来消除无用数据 (2) 显式内连接: 语法: select 字段列表 from 表名1 inner join 表名2 on 条件 外连接查询 (1)左外连接查询查询左表的...
  • 多表查询的分类 内连接查询: 隐式内连接:使用where条件消除无用数据 语法 select 字段列表 from 表1,表2 where 条件; 显示内连接: 语法 select 字段列表 from 表名1 [inner] join 表名2 on 条件; ...
  • 当有条件查询数据库表中的记录时,只需要将各个条件用and或者or即可,与条件顺序无关。 如: select * from Table_login where name = 'wp'and password='1' and pp='pp'and qq='qq' or qq='dd'
  • 记录数据库多表查询相关操作 给定两张表 当我们只是简单的要求提供person的一下信息时: FirstName, LastName, City, State 我们首先想到的就是我们使用最多的语句: select P.FirstName,P.LastName,A.City,A....
  • 详述数据库多表查询

    千次阅读 2019-08-17 16:54:49
    多个表关联查询需要依据多表之间列关系将其连接起来,这种连接方式分为三种:内连接(inner join)、外连接(outer join)及交叉连接(cross join)。 举例: 1、内连接(多个表的交集): 如果依据多个表之间列关系进行内...
  • oracle数据库多表查询SQL编写 -多表查询SQL语法 ——select distinct+[字段+别名,字段+别名] from [table1,table2] where [条件表达式] orde by[排序字段+asc|desc] -多表查询中的-笛卡尔积-是...
  • 数据库多表联合查询

    2019-03-27 15:20:43
    多表联合查询 多表联合查询可以通过连接运算实现,而连接运算又可以通过广义笛卡尔积后在进行选择运算来实现。 Select 多表联合查询语句 Select 列名 [[,列名]…] from 表名1,表名2,… Where 查询条件; 可以使用as为...
  • 详解数据库多表连接查询的实现方法 通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点,也是它区别于其它类型数据库管理系统的一个标志。 在关系数据库管理系统中,表建立时各数据之间的关系不必...
  • 数据库 多表连接查询

    2019-05-11 09:52:27
    多表查询,也叫多表连接查询。 /* 按连接的情况不同可以分为: 等值连接 非等值连接 等值连接:通过条件是否相等做连接查询 非等值连接:不是通过条件是否相等做连接查询 内连接 外连接 内连接 inner JOIN ...
  • 5.8 数据库多表查询

    2019-02-01 01:28:00
    多表连接查询 交叉连接:  不适用任何匹配条件。生成笛卡尔积、 select * from employee1 ,department; 内连接:  找两张表共有的部分,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。(只连接匹配...
  • 数据库多表查询总结

    2017-10-24 22:21:50
    多表查询语法: SELECT table1.column, table2.column ...但要注意where 不要省,省略where 即为笛卡尔集,而且where 条件要有效,两张表间有一个相同的字段,才好进行有效的多表查询 内连接:

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 4,616
精华内容 1,846
关键字:

数据库多表多条件查询