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

作者：小编更新时间：2023-08-16 17:42:56 浏览量：260人看过

写在前面

ps：【文章最后有相关练习题的分享】

第一段：主键和外键知识点补充

?1.1、主键的概念

①.）每个表应该有一个主键 . 定义一个保证唯一标识每个logging的主键.

?1.2、主键的创建

?1.2.1、创建一个主键


creat table tb1(
id int not null auto_increment primary key,
)

?1.2.2、创建多个主键

为"tb1"的表创建多个主键


creat table tb1(
id int not null auto_increment ,
pid int(11) not NULL,
primary key(id, pid)
)

?第二段：补充知识点

?2.1、desc 表名;

desc tablenames 主要用来查看数据表的表结构

比如用以下命令创建了一张'user'表


create table user(
id int auto_increment primary key,
name varchar(10),
gender char(2)
)engine=innodb default charset=utf8;

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

?2.2、show create table 表名;

该语句的功能：查看表创建时的定义

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

?第三段：自增列起始值设置

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

?③1、设置自增列初始值语句


alter table user AUTO_INCREMENT=1;

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

第四段：自增列步长设置

?④1、步长设置语法

步长设置我就不举例了,上面是设置递增列的初始值,这个是步长【不设置默认是1】

语法：


set session auto_increment_increment=2; 设置会话步长
show session variables like 'auto_inc%';查看全局变量

但是要强调一点：

MySQL: 自增步长是基于会话级别的【登入一次mysql就是一次会话】,改变一次步长之后同一次会话创建的所有表的步长都会改变为你设置的步长；

SqlServer：自增步长：是基础表级别的：可以单独的对某一张表的步长进行设置,而不改变全局的步长；

总结：SqlServer方便一点


MySQL: 自增步长
基于会话级别：
show session variables like 'auto_inc%';查看全局变量
      set session auto_increment_increment=2; 设置会话步长
# set session auto_increment_offset=10;
基于全局级别（此时此刻呢开的所有会话的步长都会改变）：
show global variables like 'auto_inc%';    查看全局变量
      set global auto_increment_increment=2;     设置会话步长
# set global auto_increment_offset=10;     设置起始值


SqlServer：自增步长：
基础表级别：
CREATE TABLE ◆t5◆ (
  ◆nid◆ int(11) NOT NULL AUTO_INCREMENT,
  ◆pid◆ int(11) NOT NULL,
  ◆num◆ int(11) DEFAULT NULL,
  PRIMARY KEY (◆nid◆,◆pid◆)
) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=2 DEFAULT CHARSET=utf8

CREATE TABLE ◆t6◆ (
  ◆nid◆ int(11) NOT NULL AUTO_INCREMENT,
  ◆pid◆ int(11) NOT NULL,
  ◆num◆ int(11) DEFAULT NULL,
  PRIMARY KEY (◆nid◆,◆pid◆)
) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=20 DEFAULT CHARSET=utf8

第五段：唯一索引知识点

?⑤1、什么是唯一索引?

所谓唯一索引,就是在创建索引时,限制索引的字段值必须是唯一的.通过该类型的索引可以比普通索引更快速地查询某条记录.唯一索引顾名思义不可以重复,但是可以为空,这也是它与主键的区别之一

?⑤3、创建唯一索引的方式

创建方法一：


CREATE UNIQUE INDEX indexName ON mytable(username(length))

创建方法二【联合唯一索引】：


UNIQUE indexName (列名,列名),

在创建表时的例子：


create table t1(
id int ....,
num int,
xx int,
UNIQUE 唯一索引名称 (列名,列名),
# 示例
UNIQUE uql (num,xx),
)

第六段：外键变种详细知识点

?⑥1、什么是外键变种

顾名思义就是外键的多种形式,下面会通过举例子的方式讲述

?⑥2、外键变种之一对一

比如我们有两张表【用户表】和【博客表】,如果每个用户只能注册一个博客,那么用户账号与博客账号的外键关系就是一对一


　　用户表:
　　　 id     name     age 
　　　　1      xaiom    23  
　　　　2      eagon    34
　　　　3      lxxx     45
　　　　4      owen     83
　　博客表:
　　　id         url        user_id  (外键 ◆ 唯一约束)
　　　　1       /xiaom       2
　　　　2       /zekai            1
　　　　3       /lxxx             3
　　　　4       /owen             4

?⑥3、外键变种之多对多

这个也是比较容易理解的,就比如我有两张表【用户表】和【主机表】,每个用户可以登入多台主机,同时每台主机也可以被多个用户同时使用,这种关系就是多对多


用户表:
                  id    name    phone 
                  1    root1    1234
                  2    root2    1235
                  3    root3    1236
                  4    root4    1237
                  5    root5    1238
                  6    root6    1239
                  7    root7    1240
                  8    root8    1241

主机表:

                  id    hostname    
                  1    c1.com    
                  2    c2.com    
                  3    c③com    
                  4    c④com    
                  5    c⑤com    

为了方便查询, 用户下面有多少台主机以及某一个主机上有多少个用户, 我们需要新建第三张表:
用户主机表:

                id    userid    hostid
                  1    1    1
                  2    1    2
                  3    1    3
                  4    2    4
                  5    2    5
                  6    3    2
                  7    3    4    
创建的时候, userid 和 hostid 必须是外键, 然后联合唯一索引 unique(userid, hostid),（避免重复出现）【联合唯一索引在多对多的情况下可以视情况而写】

              Django orm 也会设计

第七段：数据行操作补充

?⑦1、增操作

向表的某一行插入数据


insert into 表名(列名1, 列名2) values('行一内容', '行一内容'), ('行二内容', '行二内容')

向表的多行插入数据


insert into 表名(列名1, 列名2) values('行一内容', '行一内容'), ('行二内容', '行二内容')

向某一张表中插入另一张表中的内容


insert into 表一(name,age) select name,age from 表二;

?⑦2、删操作

假设我创建了一张表叫【tb1】其中列名有【name】列和【id】列


# 删除表
delect from tb1
# 带条件的删除
# 把id不等于2的行删除
delete from tb1 where id !=2 
delete from tb1 where id =2 
delete from tb1 where id > 2 
delete from tb1 where id >=2 
# 把id > 2,并且name='alex'的数据行删除
delete from tb1 where id >=2 or name='alex'

?⑦3、改操作

同样的使用上面删操作的表


# 把tb1表中的id > 2,并且name='XX'的数据行,的名字设为'alex',其他的不变
update tb1 set name='alex' where id>12 and name='xx'
update tb1 set name='alex',age=19 where id>12 and name='xx'

?⑦4、查操作

基础的查操作


# 查看表中所有数据
select * from tb1;
# 查看表中id,name列的数据
select id,name from tb1;

select id,name from tb1 where id > 10 or name ='xxx';
# 查看表中id,name列的数据,并将name列名重新取个叫cname的别名
select id,name as cname from tb1 where id > 10 or name ='xxx';

select name,age,11 from tb1;

进阶的查操作


select * from tb1 where id != 1
# 查看id为(1,5,12)中的数的行
select * from tb1 where id in (1,5,12);
select * from tb1 where id not in (1,5,12);
# 查tb1表中值id为tb11中元素的行
select * from tb1 where id in (select id from tb11)
# 查看id为5到12之间数的行
select * from tb1 where id between 5 and 12;

通配符的查操作


# 查询表中以ale开头的所有用户 %表示后面可以有任意多个字符,比如可以匹配到【alex,alexk】
select * from tb1 where name like "ale%"
# 查询表中以ale开头的所有用户 _表示后面只能有一个字符,比如【alex】可以匹配到但是【alexxxx】就不可以匹配到
select * from tb1 where name like "ale_"

?⑦5、limit以及order by语句

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

?⑦⑤1、limit【限制】的用法


# 查看表中的前十条数据
select * from tb1 limit 10;
# 从0行开始后面取十条数据
select * from tb1 limit 0,10;
select * from tb1 limit 10,10;
# 从20行开始后面取十条数据
select * from tb1 limit 20,10;
# 从第20行开始读取,读取10行；
select * from tb1 limit 10 offset 20;

?⑦⑤2、order by【排序语句】


# 将表tb1按id列从大到小排
select * from tb1 order by id desc; 大到小 【口诀先d后c,d在c后面所以是从大到小】
select * from tb1 order by id asc;  小到大 【口诀先a后c,c在a后面所以是从小到大】
# 将表tb1按age列从大到小排,如果id数值相同就按id列大小从小到大排
select * from tb1 order by age desc,id desc;

拓展要点：取后十条数据


# 实现原理：将tb1表逆序,然后在取前十条数据,这样就相当于取了原表的最后十条数据
select * from tb1 order by id desc limit 10;

第八段：MySQL分组操作知识点

关键语句：


group by

首先我们按如下的方式创建两张表【department表】【userinfo表】


department表

CREATE table department(
id int auto_increment primary key,
title varchar(32)
)engine=innodb default charset=utf8;

userinfo表

CREATE table userinfo(
id int auto_increment primary key,
name varchar(32),
age int,
depart_id int,
CONSTRAINT fk_usrt_depart FOREIGN key (depart_id) REFERENCES department(id)
)engine=innodb default charset=utf8;

# 给两张表加数据
# department表
◆----◆-------◆
| id | title |
◆----◆-------◆
|  1 | 财务  |
|  2 | 公关  |
|  3 | 测试  |
|  4 | 运维  |
◆----◆-------◆
# userinfo表
◆----◆------◆------◆-----------◆
| id | name | age  | depart_id |
◆----◆------◆------◆-----------◆
|  1 | 小费 |    6 |         1 |
|  2 | 小港 |    6 |         3 |
|  3 | 小干 |    6 |         2 |
|  4 | 小刚 |    6 |         4 |
|  5 | 小强 |    6 |         4 |
|  6 | 小美 |    6 |         4 |
|  7 | 小亮 |    6 |         2 |
|  8 | 小每 |    6 |         1 |
◆----◆------◆------◆-----------◆

对于语句我就不多解释了,主要看结果就可以了

①.、将同一个部门的人放在一起,并且用户部门相同取id值大的用户


SELECT depart_id, max(id) FROM userinfo GROUP BY depart_id;

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


select count(id),max(id),depart_id from userinfo group by depart_id;

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


select count(id),depart_id from userinfo group by depart_id having count(id) > 1;

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


select count(id),depart_id from userinfo where id > 4 group by depart_id having count(id) > 1;

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

第九段：MySQL连表操作

?9.1、连表操作概念

连表顾名思义就是将两张表连在一起查看的操作,操作大的分为两种内连接和外连接,而外连接又分为左连接、右连接和全连接.

内连接（inner join）：只包含匹配的记录.

外连接（outer join）：除了包含匹配的记录还包含不匹配的记录.{

① 左连接（left join）：返回匹配的记录,以及表 A 多余的记录.

}

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


# department表
◆----◆-------◆
| id | title |
◆----◆-------◆
|  1 | 财务  |
|  2 | 公关  |
|  3 | 测试  |
|  4 | 运维  |
◆----◆-------◆
# user_mess表
◆----◆------◆-----------◆
| id | name | depart_id |
◆----◆------◆-----------◆
|  1 | 小费 |         1 |
|  2 | 小港 |         1 |
|  3 | 小干 |         2 |
|  4 | 小刚 |         4 |
◆----◆------◆-----------◆

执行如下语句可以连接两张表：


select * from user_mess,department where user_mess.depart_id = department.id;
输出结果：
◆----◆------◆-----------◆----◆-------◆
| id | name | depart_id | id | title |
◆----◆------◆-----------◆----◆-------◆
|  1 | 小费 |         1 |  1 | 财务  |
|  2 | 小港 |         1 |  1 | 财务  |
|  3 | 小干 |         2 |  2 | 公关  |
|  4 | 小刚 |         4 |  4 | 运维  |
◆----◆------◆-----------◆----◆-------◆
4 rows in set (0.00 sec)

?9.2、内连接

内连接语法： a inner join b ,但是一般 inner 可以省略不写,也就是如下形式


select  *  from  a  join  b ;

执行下面语句：


select  *  from  user_mess join department;
输出结果：
◆----◆------◆-----------◆----◆-------◆
| id | name | depart_id | id | title |
◆----◆------◆-----------◆----◆-------◆
|  4 | 小刚 |         4 |  1 | 财务  |
|  3 | 小干 |         2 |  1 | 财务  |
|  2 | 小港 |         1 |  1 | 财务  |
|  1 | 小费 |         1 |  1 | 财务  |
|  4 | 小刚 |         4 |  2 | 公关  |
|  3 | 小干 |         2 |  2 | 公关  |
|  2 | 小港 |         1 |  2 | 公关  |
|  1 | 小费 |         1 |  2 | 公关  |
|  4 | 小刚 |         4 |  3 | 测试  |
|  3 | 小干 |         2 |  3 | 测试  |
|  2 | 小港 |         1 |  3 | 测试  |
|  1 | 小费 |         1 |  3 | 测试  |
|  4 | 小刚 |         4 |  4 | 运维  |
|  3 | 小干 |         2 |  4 | 运维  |
|  2 | 小港 |         1 |  4 | 运维  |
|  1 | 小费 |         1 |  4 | 运维  |
◆----◆------◆-----------◆----◆-------◆

说明：像这样不加查询条件会形成笛卡尔积 .笛卡尔积的意思是：是指包含两个集合中任意取出两个元素构成的组合的集合.// 两表分别交叉查询了一遍；也可以加上条件查询条件 on 或者 using ,两者的区别在于都是查询出符合条件的结果集 ,但是using会优化掉相同的字段.

下面来举个栗子更好理解：


# 使用on语句添加条件
select  *  from  user_mess join department on user_mess.depart_id = department.id;
输出结果：
◆----◆------◆-----------◆----◆-------◆
| id | name | depart_id | id | title |
◆----◆------◆-----------◆----◆-------◆
|  1 | 小费 |         1 |  1 | 财务  |
|  2 | 小港 |         1 |  1 | 财务  |
|  3 | 小干 |         2 |  2 | 公关  |
|  4 | 小刚 |         4 |  4 | 运维  |
◆----◆------◆-----------◆----◆-------◆
4 rows in set (0.00 sec)

这时我们可以发现这与上面的select * from user_mess,department where user_mess.depart_id = department.id;语句输出结果是相同的

?9.3、外连接

?9.③1、左连接


# 左连接既 左边 tb_left 表作为基表（主表）显示所有行, tb_right 表作为外表 条件匹配上的就显示,没匹配上的就用 Null 填充
select * from tb_left left join tb_right on tb_left.id = tb_left.id ;

栗子：


select  *  from  user_mess left join department on user_mess.depart_id = department.id;

输出结果：
◆----◆------◆-----------◆------◆-------◆
| id | name | depart_id | id   | title |
◆----◆------◆-----------◆------◆-------◆
|  1 | 小费 |         1 |    1 | 财务  |
|  2 | 小港 |         1 |    1 | 财务  |
|  3 | 小干 |         2 |    2 | 公关  |
|  4 | 小刚 |         4 |    4 | 运维  |
◆----◆------◆-----------◆------◆-------◆
4 rows in set (0.00 sec)

?9.③1、右连接


# 右连接即 右边 tb_right 表作为基表（主表）显示所有行, tb_left 表作为外表 条件匹配上的就显示,没匹配上的就用 Null 填充； 和左连接相反.
select * from tb_left right join tb_right on tb_left.id = tb_left.id ;


select  *  from  user_mess right join department on user_mess.depart_id = department.id;
输出结果：

◆------◆------◆-----------◆----◆-------◆
| id   | name | depart_id | id | title |
◆------◆------◆-----------◆----◆-------◆
|    2 | 小港 |         1 |  1 | 财务  |
|    1 | 小费 |         1 |  1 | 财务  |
|    3 | 小干 |         2 |  2 | 公关  |
| NULL | NULL |      NULL |  3 | 测试  |
|    4 | 小刚 |         4 |  4 | 运维  |
◆------◆------◆-----------◆----◆-------◆
5 rows in set (0.00 sec)

?9.③1、全外连接


# 经查找发现 MySQL 是不支持所谓 tb_left full join tb_right 语作为 全外连接查询的,想要实现全外连接查询可以通过 union 实现,union 操作符用于合并两个或多个 SELECT 语句的结果集,语句如下：

select * from tb_left left join tb_right on tb_left.id = tb_right.id  union  select * from tb_left right join tb_right on tb_left.id = tb_right.id ;

栗子:


select  *  from  user_mess left join department on user_mess.depart_id = department.id union select * from  user_mess right join department on user_mess.depart_id = department.id;

输出结果：

◆------◆------◆-----------◆------◆-------◆
| id   | name | depart_id | id   | title |
◆------◆------◆-----------◆------◆-------◆
|    1 | 小费 |         1 |    1 | 财务  |
|    2 | 小港 |         1 |    1 | 财务  |
|    3 | 小干 |         2 |    2 | 公关  |
|    4 | 小刚 |         4 |    4 | 运维  |
| NULL | NULL |      NULL |    3 | 测试  |
◆------◆------◆-----------◆------◆-------◆
5 rows in set (0.00 sec)

值得注意的是：注：当 union 和 all 一起使用时（即 union all ）,重复的行不会去除.


select  *  from  user_mess left join department on user_mess.depart_id = department.id union all select * from  user_mess right join department on user_mess.depart_id = department.id;

输出结果：

◆------◆------◆-----------◆------◆-------◆
| id   | name | depart_id | id   | title |
◆------◆------◆-----------◆------◆-------◆
|    1 | 小费 |         1 |    1 | 财务  |
|    2 | 小港 |         1 |    1 | 财务  |
|    3 | 小干 |         2 |    2 | 公关  |
|    4 | 小刚 |         4 |    4 | 运维  |
|    2 | 小港 |         1 |    1 | 财务  |
|    1 | 小费 |         1 |    1 | 财务  |
|    3 | 小干 |         2 |    2 | 公关  |
| NULL | NULL |      NULL |    3 | 测试  |
|    4 | 小刚 |         4 |    4 | 运维  |
◆------◆------◆-----------◆------◆-------◆
9 rows in set (0.00 sec)

?9.4、交叉连接

概念：


select * from user_mess cross join department;

输出结果：
◆----◆------◆-----------◆----◆-------◆
| id | name | depart_id | id | title |
◆----◆------◆-----------◆----◆-------◆
|  4 | 小刚 |         4 |  1 | 财务  |
|  3 | 小干 |         2 |  1 | 财务  |
|  2 | 小港 |         1 |  1 | 财务  |
|  1 | 小费 |         1 |  1 | 财务  |
|  4 | 小刚 |         4 |  2 | 公关  |
|  3 | 小干 |         2 |  2 | 公关  |
|  2 | 小港 |         1 |  2 | 公关  |
|  1 | 小费 |         1 |  2 | 公关  |
|  4 | 小刚 |         4 |  3 | 测试  |
|  3 | 小干 |         2 |  3 | 测试  |
|  2 | 小港 |         1 |  3 | 测试  |
|  1 | 小费 |         1 |  3 | 测试  |
|  4 | 小刚 |         4 |  4 | 运维  |
|  3 | 小干 |         2 |  4 | 运维  |
|  2 | 小港 |         1 |  4 | 运维  |
|  1 | 小费 |         1 |  4 | 运维  |
◆----◆------◆-----------◆----◆-------◆
16 rows in set (0.00 sec)

?9.5、总结各种连表操作

十、小结

恭喜你看到了最后,现在看了这么多,不如赶快网上找些题目自己动手实践一波撒?.

不知道在哪找?放心我帮你找好了?.

以上就是土嘎嘎小编大虾米为大家整理的相关主题介绍,如果您觉得小编更新的文章只要能对粉丝们有用，就是我们最大的鼓励和动力,不要忘记讲本站分享给您身边的朋友哦！！

网站首页

视频教程

下载频道

文章中心

问答中心

在线工具

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

?1.1、主键的概念

?1.2、主键的创建

?1.2.1、创建一个主键

?1.2.2、创建多个主键

?第二段：补充知识点

?2.1、desc 表名;

?2.2、show create table 表名;

?第三段：自增列起始值设置

?③1、设置自增列初始值语句

?④1、步长设置语法

?⑤1、什么是唯一索引?

?⑤3、创建唯一索引的方式

?⑥1、什么是外键变种

?⑥2、外键变种之一对一

?⑥3、外键变种之多对多

?⑦1、增操作

?⑦2、删操作

?⑦3、改操作

?⑦4、查操作

?⑦5、limit以及order by语句

?⑦⑤1、limit【限制】的用法

?⑦⑤2、order by【排序语句】

?9.1、连表操作概念

?9.2、内连接

?9.3、外连接

?9.③1、左连接

?9.③1、右连接

?9.③1、全外连接

?9.4、交叉连接

?9.5、总结各种连表操作

相关推荐

编辑推荐

热门文章

网站首页

视频教程

下载频道

文章中心

问答中心

在线工具

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

?1.1、 主键的概念

?1.2、主键的创建

?1.2.1、创建一个主键

?1.2.2、创建多个主键

?第二段：补充知识点

?2.1、desc 表名;

?2.2、show create table 表名;

?第三段：自增列起始值设置

?③1、设置自增列初始值语句

?④1、步长设置语法

?⑤1、什么是唯一索引?

?⑤3、创建唯一索引的方式

?⑥1、什么是外键变种

?⑥2、外键变种之一对一

?⑥3、外键变种之多对多

?⑦1、增操作

?⑦2、删操作

?⑦3、改操作

?⑦4、查操作

?⑦5、limit以及order by语句

?⑦⑤1、limit【限制】的用法

?⑦⑤2、order by【排序语句】

?9.1、连表操作概念

?9.2、内连接

?9.3、外连接

?9.③1、左连接

?9.③1、右连接

?9.③1、全外连接

?9.4、交叉连接

?9.5、总结各种连表操作

相关推荐

编辑推荐

热门文章

?1.1、主键的概念