MySQL速通篇5000字吃透MySQL部分重要知识点

　　写在前面
　　这篇5000多字博客也花了我几天的时间，主要是我对MySQL一部分重要知识点的理解【后面当然还会写博客补充噻，欢迎关注我哟】，当然这篇文章可能也会有不恰当的地方【毕竟也写了这么多字，错别字可能也不少】，不足的地方欢迎各位的指正。一、主键和外键知识点补充1。1、主键的概念1、什么时候用主键？2、主键有什么用处3、一张表可以设置几个主键？4、一个主键只能是一列吗？5、主键和唯一索引有什么区别？
　　1）每个表应该有一个主键。定义一个保证唯一标识每个logging的主键。
　　2）数据库主键，指的是一个列或多列的组合，其值能唯一地标识表中的每一行，通过它可强制表的实体完整性。主键主要是用与其他表的外键关联，以及文本记录的修改与删除。
　　3）一张表只可以有一个主键
　　4）主键不一定只有一列，有些表的主键是多个属性构成的。表定义为列的集合
　　5）主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。1、主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。2、唯一性索引列允许空值，而主键列不允许为空值。3、主键列在创建时，已经默认为空值唯一索引了。4、主键可以被其他表引用为外键，而唯一索引不能。1。2、主键的创建1。2。1、创建一个主键
　　比如我们要创建一张名为tb1的表并且将它的id列设置为主键creattabletb1（idintnotnullautoincrementprimarykey，1。2。2、创建多个主键
　　为tb1的表创建多个主键creattabletb1（idintnotnullautoincrement，pidint（11）notNULL，primarykey（id，pid））二、补充知识点2。1、desc表名；desctablenames主要用来查看数据表的表结构
　　比如用以下命令创建了一张‘user’表createtableuser（idintautoincrementprimarykey，namevarchar（10），genderchar（2））engineinnodbdefaultcharsetutf8；
　　使用以上命令后可以得到如下结果：
　　2。2、showcreatetable表名；该语句的功能：查看表创建时的定义
　　列如对上面的‘user’表执行该操作得到如下结果：
　　但是上面的结果看起来非常混乱，我们可以使用G【G的作用是将查到的结构旋转90度变成纵向】使得结果更加美观
　　三、自增列起始值设置我们首先查看上面user表中的数据
　　不难看出这个表的id列已经自增到了5，其中showcreatetable表名；可以看出AUTOINCREMENT6，这个就表示接下来id列要递增成为的数字，
　　下一步我们使用语句来删除这个表
　　接着我们使用insertintouser（name，gender）values（周周，男）；再向表中插入一列会发现id列是从6开始递增
　　这时我们就会想，可不可以重新从1开始，或者自定义开始递增时的值呢？3。1、设置自增列初始值语句
　　altertableuserAUTOINCREMENT1；
　　要特别注意如果设置的初始值小于原来表递增列最后一个数据的值，那么语句是不会生效的四、自增列步长设置4。1、步长设置语法步长设置我就不举例了，上面是设置递增列的初始值，这个是步长【不设置默认是1】
　　语法：setsessionautoincrementincrement2；设置会话步长查看全局变量
　　但是要强调一点：
　　MySQL：自增步长是基于会话级别的【登入一次mysql就是一次会话】，改变一次步长之后同一次会话创建的所有表的步长都会改变为你设置的步长；
　　SqlServer：自增步长：是基础表级别的：可以单独的对某一张表的步长进行设置，而不改变全局的步长；
　　总结：SqlServer方便一点MySQL：自增步长基于会话级别：查看全局变量setsessionautoincrementincrement2；设置会话步长setsessionautoincrementoffset10；基于全局级别（接下来开的所有会话的步长都会改变）：查看全局变量setglobalautoincrementincrement2；设置会话步长setglobalautoincrementoffset10；设置起始值SqlServer：自增步长：基础表级别：CREATETABLEt5（nidint（11）NOTNULLAUTOINCREMENT，pidint（11）NOTNULL，numint（11）DEFAULTNULL，PRIMARYKEY（nid，pid））ENGINEInnoDBAUTOINCREMENT4，步长2DEFAULTCHARSETutf8CREATETABLEt6（nidint（11）NOTNULLAUTOINCREMENT，pidint（11）NOTNULL，numint（11）DEFAULTNULL，PRIMARYKEY（nid，pid））ENGINEInnoDBAUTOINCREMENT4，步长20DEFAULTCHARSETutf8五、唯一索引知识点5。1、什么是唯一索引？
　　所谓唯一索引，就是在创建索引时，限制索引的字段值必须是唯一的。通过该类型的索引可以比普通索引更快速地查询某条记录。唯一索引顾名思义不可以重复，但是可以为空，这也是它与主键的区别之一5。3、创建唯一索引的方式
　　创建方法一：CREATEUNIQUEINDEXindexNameONmytable（username（length））
　　创建方法二【联合唯一索引】：UNIQUEindexName（列名，列名），
　　在创建表时的例子：createtablet1（idint。。。。，numint，xxint，UNIQUE唯一索引名称（列名，列名），示例UNIQUEuql（num，xx），）六、外键变种详细知识点6。1、什么是外键变种
　　顾名思义就是外键的多种形式，下面会通过举例子的方式讲述6。2、外键变种之一对一
　　比如我们有两张表【用户表】和【博客表】，如果每个用户只能注册一个博客，那么用户账号与博客账号的外键关系就是一对一用户表：idnameage1xaiom232eagon343lxxx454owen83博客表：idurluserid（外键唯一约束）1xiaom22zekai13lxxx34owen46。3、外键变种之多对多
　　这个也是比较容易理解的，就比如我有两张表【用户表】和【主机表】，每个用户可以登入多台主机，同时每台主机也可以被多个用户同时使用，这种关系就是多对多用户表：idnamephone1root112342root212353root312364root412375root512386root612397root712408root81241主机表：idhostname1c1。com2c2。com3c3。com4c4。com5c5。com为了方便查询，用户下面有多少台主机以及某一个主机上有多少个用户，我们需要新建第三张表：用户主机表：iduseridhostid111212313424525632734创建的时候，userid和hostid必须是外键，然后联合唯一索引unique（userid，hostid），（避免重复出现）【联合唯一索引在多对多的情况下可以视情况而写】Djangoorm也会设计七、数据行操作补充7。1、增操作
　　向表的某一行插入数据insertinto表名（列名1，列名2）values（行一内容，行一内容），（行二内容，行二内容）
　　向表的多行插入数据insertinto表名（列名1，列名2）values（行一内容，行一内容），（行二内容，行二内容）
　　向某一张表中插入另一张表中的内容insertinto表一（name，age）selectname，agefrom表二；7。2、删操作
　　假设我创建了一张表叫【tb1】其中列名有【name】列和【id】列删除表delectfromtb1带条件的删除把id不等于2的行删除deletefromtb1whereid！2deletefromtb1whereid2deletefromtb1whereid2deletefromtb1whereid2把id2，并且namealex的数据行删除deletefromtb1whereid2ornamealex7。3、改操作
　　同样的使用上面删操作的表把tb1表中的id2，并且nameXX的数据行，的名字设为alex，其他的不变updatetb1setnamealexwhereid12andnamexxupdatetb1setnamealex，age19whereid12andnamexx7。4、查操作
　　基础的查操作查看表中所有数据selectfromtb1；查看表中id，name列的数据selectid，namefromtb1；selectid，namefromtb1whereid10查看表中id，name列的数据，并将name列名重新取个叫cname的别名selectid，nameascnamefromtb1whereid10selectname，age，11fromtb1；
　　进阶的查操作selectfromtb1whereid！1查看id为（1，5，12）中的数的行selectfromtb1whereidin（1，5，12）；selectfromtb1whereidnotin（1，5，12）；查tb1表中值id为tb11中元素的行selectfromtb1whereidin（selectidfromtb11）查看id为5到12之间数的行selectfromtb1whereidbetween5and12；
　　通配符的查操作查询表中以ale开头的所有用户表示后面可以有任意多个字符，比如可以匹配到【alex，alexk】selectfromtb1wherenamelikeale查询表中以ale开头的所有用户表示后面只能有一个字符，比如【alex】可以匹配到但是【alexxxx】就不可以匹配到selectfromtb1wherenamelikeale7。5、limit以及orderby语句
　　将上面知识是先看下面的图：
　　在我们浏览器搜素想要的内容时，返回的结果通常是很多的，如果一次将结果全部显示给你，那么电脑可能会崩溃，这时浏览器就会默认返回结果的前几十条，这种对想要查询结果的条数的限制我们在数据库中也可以使用limit来实现7。5。1、limit【限制】的用法查看表中的前十条数据selectfromtb1limit10；从0行开始后面取十条数据selectfromtb1limit0，10；selectfromtb1limit10，10；从20行开始后面取十条数据selectfromtb1limit20，10；从第20行开始读取，读取10行；selectfromtb1limit10offset20；7。5。2、orderby【排序语句】将表tb1按id列从大到小排selectfromtb1大到小【口诀先d后c，d在c后面所以是从大到小】selectfromtb1小到大【口诀先a后c，c在a后面所以是从小到大】将表tb1按age列从大到小排，如果id数值相同就按id列大小从小到大排selectfromtb1orderbyagedesc，
　　拓展要点：取后十条数据实现原理：将tb1表逆序，然后在取前十条数据，这样就相当于取了原表的最后十条数据selectfromtb1orderbyiddesclimit10；八、MySQL分组操作知识点
　　关键语句：groupby
　　首先我们按如下的方式创建两张表【department表】【userinfo表】department表CREATEtabledepartment（idintautoincrementprimarykey，titlevarchar（32））engineinnodbdefaultcharsetutf8；userinfo表CREATEtableuserinfo（idintautoincrementprimarykey，namevarchar（32），ageint，departidint，CONSTRAINTfkusrtdepartFOREIGNkey（departid）REFERENCESdepartment（id））engineinnodbdefaultcharsetutf8；给两张表加数据department表idtitle1财务2公关3测试4运维userinfo表idnameagedepartid1小费612小港633小干624小刚645小强646小美647小亮628小每61
　　对于语句我就不多解释了，主要看结果就可以了
　　1、将同一个部门的人放在一起，并且用户部门相同取id值大的用户SELECTdepartid，max（id）FROMuserinfoGROUPBY
　　输出结果：
　　2、在上面操作的基础上显示各个部门的人数selectcount（id），max（id），
　　输出结果：
　　3、如果对于聚合函数结果进行二次筛选时？必须使用havingselectcount（id），departidfromuserinfogroupbydepartidhavingcount（id）1；
　　4、上面的列名为count（id），这是看着有点不舒服的，我们可以使用as关键字改名
　　5、进一步的进阶方式selectcount（id），departidfromuserinfowhereid4groupbydepartidhavingcount（id）1；
　　九、MySQL连表操作9。1、连表操作概念连表顾名思义就是将两张表连在一起查看的操作，操作大的分为两种内连接和外连接，而外连接又分为左连接、右连接和全连接。
　　内连接（innerjoin）：只包含匹配的记录。
　　外连接（outerjoin）：除了包含匹配的记录还包含不匹配的记录。｛
　　1。左连接（leftjoin）：返回匹配的记录，以及表A多余的记录。
　　2。右连接（rightjoin）：返回匹配的记录，以及表B多余的记录。
　　3。全连接（fulljoin）：返回匹配的记录，以及表A和表B各自的多余记录。
　　｝
　　用网上一张图比较好的图可以更加方便理解如下：
　　下面我们都用【department表】【usermess表】来举例：department表idtitle1财务2公关3测试4运维usermess表idnamedepartid1小费12小港13小干24小刚4
　　执行如下语句可以连接两张表：selectfromusermess，departmentwhereusermess。departiddepartment。输出结果：idnamedepartididtitle1小费11财务2小港11财务3小干22公关4小刚44运维4rowsinset（0。00sec）9。2、内连接
　　内连接语法：ainnerjoinb，但是一般inner可以省略不写，也就是如下形式
　　执行下面语句：输出结果：idnamedepartididtitle4小刚41财务3小干21财务2小港11财务1小费11财务4小刚42公关3小干22公关2小港12公关1小费12公关4小刚43测试3小干23测试2小港13测试1小费13测试4小刚44运维3小干24运维2小港14运维1小费14运维
　　说明：像这样不加查询条件会形成笛卡尔积。笛卡尔积的意思是：是指包含两个集合中任意取出两个元素构成的组合的集合。两表分别交叉查询了一遍；也可以加上条件查询条件on或者using，两者的区别在于都是查询出符合条件的结果集，但是using会优化掉相同的字段。
　　下面来举个栗子更好理解：使用on语句添加条件selectfromusermessjoindepartmentonusermess。departiddepartment。输出结果：idnamedepartididtitle1小费11财务2小港11财务3小干22公关4小刚44运维4rowsinset（0。00sec）
　　这时我们可以发现这与上面的selectfromusermess，departmentwhereusermess。departiddepartment。语句输出结果是相同的9。3、外连接9。3。1、左连接
　　语法：左连接既左边tbleft表作为基表（主表）显示所有行，tbright表作为外表条件匹配上的就显示，没匹配上的就用Null填充selectfromtbleftleftjointbrightontbleft。idtbleft。
　　栗子：selectfromusermessleftjoindepartmentonusermess。departiddepartment。输出结果：idnamedepartididtitle1小费11财务2小港11财务3小干22公关4小刚44运维4rowsinset（0。00sec）9。3。1、右连接
　　语法：右连接即右边tbright表作为基表（主表）显示所有行，tbleft表作为外表条件匹配上的就显示，没匹配上的就用Null填充；和左连接相反。selectfromtbleftrightjointbrightontbleft。idtbleft。
　　栗子：selectfromusermessrightjoindepartmentonusermess。departiddepartment。输出结果：idnamedepartididtitle2小港11财务1小费11财务3小干22公关NULLNULLNULL3测试4小刚44运维5rowsinset（0。00sec）9。3。1、全外连接
　　语法：经查找发现MySQL是不支持所谓tbleftfulljointbright语作为全外连接查询的，想要实现全外连接查询可以通过union实现，union操作符用于合并两个或多个SELECT语句的结果集，语句如下：selectfromtbleftleftjointbrightontbleft。idtbright。idunionselectfromtbleftrightjointbrightontbleft。idtbright。
　　栗子：selectfromusermessleftjoindepartmentonusermess。departiddepartment。idunionselectfromusermessrightjoindepartmentonusermess。departiddepartment。输出结果：idnamedepartididtitle1小费11财务2小港11财务3小干22公关4小刚44运维NULLNULLNULL3测试5rowsinset（0。00sec）
　　值得注意的是：注：当union和all一起使用时（即unionall），重复的行不会去除。
　　栗子：selectfromusermessleftjoindepartmentonusermess。departiddepartment。idunionallselectfromusermessrightjoindepartmentonusermess。departiddepartment。输出结果：idnamedepartididtitle1小费11财务2小港11财务3小干22公关4小刚44运维2小港11财务1小费11财务3小干22公关NULLNULLNULL3测试4小刚44运维9rowsinset（0。00sec）9。4、交叉连接概念：
　　交错连接语法：tb1crossjointb2；交错连接可以加查询条件，也可以不加查询条件，如果不加查询条件会形成笛卡尔积，类似内连接效果，同样可以使用using语句优化字段。
　　栗子：输出结果：idnamedepartididtitle4小刚41财务3小干21财务2小港11财务1小费11财务4小刚42公关3小干22公关2小港12公关1小费12公关4小刚43测试3小干23测试2小港13测试1小费13测试4小刚44运维3小干24运维2小港14运维1小费14运维16rowsinset（0。00sec）9。5、总结各种连表操作
　　1、内连接和交叉连接是十分相似的，只是语句语法有所不同，但最后查询出来的结果集的效果都是一样的，添加条件查询就只查询匹配条件的行，不添加条件查询则形成笛卡尔积（生成重复多行）而降低效率。
　　2、左连接以左边表为基础表显示所有行，右边表条件匹配的行显示，不匹配的则有Null代替。
　　3、右连接以右边表为基础表显示所有行，左边表条件匹配的行显示，不匹配的则有Null代替。十、小结
　　恭喜你看到了最后，现在看了这么多，不如赶快网上找些题目自己动手实践一波撒。