Mysql 外键索引关联

程序员文章站 2022-06-02 18:36:50

...

mysql字段类型

正确地定义的表中的字段在数据库的整体优化是非常重要的。我们应该只使用真正需要使用类型和字段的大小; 如果知道只使用2个字符，就不使用10个字符宽定义一个字段。这些类型的字段(或列)，也被称为数据类型，数据存储这些字段之中。

MySQL使用许多不同的数据类型，总体上分为三类：数字，日期时间和字符串类型。

1 数字数据类型

unsigned 无符号

INT - 正常大小的整数，可以带符号。如果是有符号的，它允许的范围是从-2147483648到2147483647。如果是无符号，允许的范围是从0到4294967295。可以指定多达11位的宽度。 2**32-1 ,4个字节
TINYINT - 一个非常小的整数，可以带符号。如果是有符号，它允许的范围是从-128到127。如果是无符号，允许的范围是从0到255，可以指定多达4位数的宽度。 2**8 -1 ，1个字节
SMALLINT - 一个小的整数，可以带符号。如果有符号，允许范围为-32768至32767。如果无符号，允许的范围是从0到65535，可以指定最多5位的宽度。2**16-1，2个字节
MEDIUMINT - 一个中等大小的整数，可以带符号。如果有符号，允许范围为-8388608至8388607。如果无符号，允许的范围是从0到16777215，可以指定最多9位的宽度。2**24，3个字节
BIGINT - 一个大的整数，可以带符号。如果有符号，允许范围为-9223372036854775808到9223372036854775807。如果无符号，允许的范围是从0到18446744073709551615. 可以指定最多20位的宽度。2**64，8个字节
FLOAT(M,D) - 不能使用无符号的浮点数字。可以定义显示长度(M)和小数位数(D)。其中D是小数的位数，M是数字(包括小数)的总数。小数精度可以到24个浮点。
DOUBLE(M,D) - 不能使用无符号的双精度浮点数。可以定义显示长度(M)和小数位数(D)。其中D是小数的位数，M是数字(包括小数)的总数。。小数精度可以达到53位的DOUBLE。 REAL是DOUBLE同义词。
DECIMAL(M,D) - 非压缩浮点数不能是带符号的。在解包小数，每个小数对应于一个字节。定义显示长度(M)和小数(D)的数量是必需的。 NUMERIC是DECIMAL的同义词。

2 日期和时间类型

MySQL的日期和时间数据类型包括：

DATE - 以YYYY-MM-DD格式的日期，在1000-01-01和9999-12-31之间。例如，1973年12月30日将被存储为1973-12-30。‘yyyy/mm/dd’
DATETIME - 日期和时间组合以YYYY-MM-DD HH:MM:SS格式，在1000-01-01 00:00:00 到9999-12-31 23:59:59之间。例如，1973年12月30日下午3:30，会被存储为1973-12-30 15:30:00。
TIMESTAMP - 1970年1月1日午夜之间的时间戳，到2037的某个时候。这看起来像前面的DATETIME格式，无需只是数字之间的连字符; 1973年12月30日下午3点30分将被存储为19731230153000(YYYYMMDDHHMMSS)。
TIME - 存储时间在HH:MM:SS格式。
YEAR(M) - 以2位或4位数字格式来存储年份。如果长度指定为2(例如YEAR(2))，年份就可以为1970至2069(70〜69)。如果长度指定为4，年份范围是1901-2155，默认长度为4。

3字符串类型

虽然数字和日期类型比较有意思，但存储大多数数据都可能是字符串格式。下面列出了在MySQL中常见的字符串数据类型。

CHAR(M) - 固定长度的字符串是以长度为1到255之间个字符长度(例如：CHAR(5))，存储右空格填充到指定的长度。限定长度不是必需的，它会默认为1。
VARCHAR(M) - 可变长度的字符串是以长度为1到21845之间字符数(高版本的MySQL超过255); 例如： VARCHAR(25). 创建VARCHAR类型字段时，必须定义长度。
BLOB or TEXT - 字段的最大长度是65535个字符。 BLOB是“二进制大对象”，并用来存储大的二进制数据，如图像或其他类型的文件。定义为TEXT文本字段还持有大量的数据; 两者之间的区别是，排序和比较上存储的数据，BLOB大小写敏感，而TEXT字段不区分大小写。不用指定BLOB或TEXT的长度。
TINYBLOB 或 TINYTEXT - BLOB或TEXT列用255个字符的最大长度。不指定TINYBLOB或TINYTEXT的长度。
MEDIUMBLOB or MEDIUMTEXT - BLOB或TEXT列具有16777215字符的最大长度。不指定MEDIUMBLOB或MEDIUMTEXT的长度。
LONGBLOB 或 LONGTEXT - BLOB或TEXT列具有4294967295字符的最大长度。不指定LONGBLOB或LONGTEXT的长度。

2.Mysql操作表

表的创建命令需要：

表的名称
字段名称
定义每个字段(类型、长度等)

2.1 创建表

语法:

CREATE TABLE table_name (column_name column_type);

例如：

create table student(
    id int not null auto_increment primary key,
    sex bit,
    age int,
    name char(20),
    info varchar(100),
    birthday date
);

注意：

字段使用NOT NULL属性，是因为我们不希望这个字段的值为NULL。因此，如果用户将尝试创建具有NULL值的记录，那么MySQL会产生错误。
字段的AUTO_INCREMENT属性告诉MySQL自动增加id字段下一个可用编号。
关键字PRIMARY KEY用于定义此列作为主键。可以使用逗号分隔多个列来定义主键。

查看表结构：

>describe student;

>desc student;

2.3 插入数据

语法

#单行插入
INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );
#多行插入
INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN ),
                    ( value12, value22,...valueNN )...;

插入数据到数据库


insert into student(age,name,info,birthday) values(22,'hule','laiyifa','2018-7-5');

一次性插入多行数据

insert into student(age,name,info,birthday) 
values
(22,'wangjianling','laiyifa','2018-7-5'),
(33,'mayun','i have money','2011-11-11');

2.4，修改表

>alter table student modify name char(20) unique;

#如果想在一个已经建好的表中添加一列，可以用以下代码：

alter table 表名 add column 列名 varchar(20) not null;

>alter table student add column info2 varchar(100);

#改列名
>alter table student change column name stuname varchar(20);
#改表名
alter table stu rename stu1

#增加普通索引
alter table test add index(t_name)

#查看索引
show index from 表名;

删除一列

>alter table student drop column sex;

增加外键约束

>alter table stu add column class_id int;
#添加外键
#alter table 从表 add constraint 外键名称 foreign key 从表(外键字段) references 主表(主表字段);
>alter table stu add foreign key stu(class_id) references grade(id);  #stu_ibfk_1
#加约束名称
>alter table stu add constraint stu_grade foreign key stu(class_id) references grade(id);
#删除外键
>alter table stu drop foreign key stu_ibfk_1

加表时加外键

>create table stu( 
    id int not null auto_increment primary key, 
    name char(20) unique, 
    age int, 
    class_id int, 
    foreign key(class_id) references grade(id) 
);

#查看外键约束
show create table student;

3、数据操作

1、增

a、全列插入
格式：insert into 表名 values(…);
说明：主键列是自动增长，但是在全列插入时需要占位，通常使用0，插入成功以后以实际数据为准
示例：insert into student values(0,“tom”,19,1,“北京”,0);
b、缺省插入
格式：insert into 表名(列1,列2,……) values(值1,值2,……);
示例：insert into student(name,age,address) values(“lilei”,19,“上海”);
c、同时插入多条数据
格式：insert into 表名 values(…),(…),……
示例：insert into student values(0,“hanmeimei”,18,0,“北京”,0),(0,“poi”,22,1,“海南”,0),(0,“wenli”,20,0,“石家庄”,0);

2、删

格式：delete from 表名 where 条件;
示例：delete from student where id=4;
注意：没有条件是全部删除，慎用

3、改

格式：update 表名 set 列1=值1,列2=值2,…… where 条件;
示例：update student set age=16 where id=7;
注意：没有条件是全部列都修改，慎用

4、查

说明：查询表中的全部数据
格式：select * from 表名;
示例：select * from student;

1、基本语法

格式：select * from 表名;
说明：
a、from关键字后面是表名，表示数据来源于这张表
b、select后面写表中的列名，如果是*表示在结果集中显示表中的所有列
c、在select后面的列名部分，可以使用as为烈面起别名，这个别名显示在结果集中
d、如果要查询多个列，之间使用逗号分隔

实例：

 select * from student;
 select name, age from student;
 select name as a, age from student;

4.查

1、条件查询

a、语法
select * from 表名 where 条件
b、比较运算符
等于 =
大于 >
小于 <
大于等于 >=
小于等于 <=
不等于 !=或<>

需求：查询id值大于8的所有数据

select * from student where id>8;

c、逻辑运算符
and 并且
or 或者
not 非

需求：查询id值大于7的女同学

select * from student where id>7 and gender=0;

d、模糊查询

like
%表示任意多个任意字符
_表示一个任意字符

需求：查询姓习的同学

 insert into student values(0,"*",65,1,"北京",0);
 insert into student values(0,"习大",66,1,"北京",0);
 
 select * from student where name like "习%";
 select * from student where name like "习_";

e、范围查询

in 表示在一个非连续的范围内
between…and… 表示在一个连续的范围内

需求：查询编号为8、10、12的学生

select * from student where id in (8,10,12);

需求：查询编号为6到8的学生

select * from student where id between 6 and 8;

f、空判断

注意：null与""是不同
判断空：is null
判断非空： is not null

需求：查询没有地址的同学

insert into student(name,age) values("特朗普",70);

select * from student where address is null;

需求：查询有地址的同学

select * from student where address is not null;

g、优先级
小括号，not 比较运算符，逻辑运算符
and比or优先级高，如果同时出现并希望先选or,需要结合()来使用

需求：求名字以li结尾的或者年龄为18且地址为空的同学

select * from student where address is null and (name like "%li" or age = 18);

2、聚合

为了快速等到统计数据，提供了5个聚合函数
a、count() 表示计算总行数，括号中可以写和列名
b、max(列) 表示求此列的最大值
c、min(列) 表示求此列的最小值
d、sum(列) 表示求此列的和
e、avg(列) 表示求此列的平均值

需求：查询学生总数

select count(*) from student;

需求：查询女生的编号最大值

select max(id) from student where gender=0;

查询女生的编号最小值

select min(id) from student where gender=0;

查询所有学生的年龄和

select sum(age) from student;

查询所有学生的年龄平均值

select avg(age) from student;

3、分组

按照字段分组，表示此字段相同的数据会被放到一个集合中.分组后，只能查询出相同的数据列，对于有差异的数据列无法显示在结果集中,可以对分组后的数据进行统计，做聚合运算.

#语法
select 列1,列2,聚合…… from 表名 group by 列1,列2,列3,……;

需求：查询男女生总数

select gender,count(*) from student group by gender;
select name,gender,count(*) from student group by gender,age;
select max(age),sex from stu group by sex;

分组后的数据筛选：

where与having的区别：
1、where是对from后面指定的表进行筛选，属于对原始数据的筛选
2、having是对group by的结果进行筛选

#语法
select 列1,列2,聚合…… from 表名 group by 列1,列2,列3,…… having 列1,……聚合……;

实例：

select max(age),sex from stu group by sex having max(age)>26;

#####6、排序

#语法：
select * from 表名 order by 列1 asc|desc，列2 asc|desc , ……;

说明：
a、将数据按照列1进行排序，如果某些列1的值相同，则按照列2进行排序
b、默认按照从小到大的顺序排序
c、asc升序
d、desc降序

需求：将没有被删除的数据按年龄排序

select * from student where isDelete=0 order by age desc;
select * from student where isDelete=0 order by age desc, id desc;

4、分页

#语法：
select * from 表名 limit start,count;

page = 2
page_num =3

start = (page - 1) * page_num
count = page_num 

select * from student limit start,count

说明：start索引从0开始

例如：

 select * from student limit 0,3;
 select * from student limit 3,3;
 select * from student where gender=1 limit 0,3;

5、关联

建表语句：

mysql>create table class(
    id int auto_increment primary key, 
    name varchar(20) not null, 
    stuNum int not null);

mysql>create table students(
    id int auto_increment primary key, 
    name varchar(20) not null, 
    gender bit default 1, 
    classid int not null, 
    foreign key(classid) references class(id)
);

#插入一些数据：
insert into class values(0, "python01", 55),(0, "python02", 50),(0, "python03", 60),(0, "python04", 80);

insert into students values(0, "tom", 1, 1);
insert into students values(0, "lilei", 1, 10);
insert into students values(0, "jack", 1, 2);

select * from students;
关联查询：
select students.name,class.name from class inner join students on class.id=students.classid;

select students.name,class.name from class left join students on class.id=students.classid;

select students.name,class.name from students right join class on class.id=students.classid;

分类：
1、表A inner join 表B：
    表A与表B匹配的行会出现在结果集中
2、表A left join 表B：
    表A与表B匹配的行会出现在结果集中，外加表A中独有的数据，未对应的数据使用null填充
3、表A right join 表B：
    表A与表B匹配的行会出现在结果集中，外加表B中独有的数据，未对应的数据使用null填充

students.name,class.name from class inner join students on class.id=students.classid;

select students.name,class.name from class left join students on class.id=students.classid;

select students.name,class.name from students right join class on class.id=students.classid;


```mysql
分类：
1、表A inner join 表B：
    表A与表B匹配的行会出现在结果集中
2、表A left join 表B：
    表A与表B匹配的行会出现在结果集中，外加表A中独有的数据，未对应的数据使用null填充
3、表A right join 表B：
    表A与表B匹配的行会出现在结果集中，外加表B中独有的数据，未对应的数据使用null填充

上一篇： Centos7如何配置路由

下一篇： jquery 常用操作整理基础入门篇_jquery

Mysql 外键索引关联

mysql字段类型

1 数字数据类型

2 日期和时间类型

3字符串类型

2.Mysql操作表

2.1 创建表

2.3 插入数据

2.4，修改表

3、数据操作

1、增

2、删

3、改

4、查

1、基本语法

4.查

1、条件查询

2、聚合

3、分组

4、分页

5、关联

mysql处理添加外键时提示error 150 问题的解决方法_MySQL

mysql处理添加外键时提示error 150 问题的解决方法

MySQL使用外键实现级联删除与更新的方法

MYSQL建立外键失败几种情况记录Can't create table不能创建表

MySQL外键使用及说明详解

MySQL添加外键时报错：1215 Cannot add the foreign key constraint的解决方法

mysql处理添加外键时提示error 150 问题的解决方法

MYSQL建立外键失败几种情况记录Can't create table不能创建表

浅谈hibernate急迫加载问题(多重外键关联)

MySQL添加外键时报错：1215 Cannot add the foreign key constraint的解决方法

Mysql 外键 索引 关联

mysql字段类型

1 数字数据类型

2 日期和时间类型

3字符串类型

2.Mysql操作表

2.1 创建表

2.3 插入数据

2.4，修改表

3、数据操作

1、增

2、删

3、改

4、查

1、基本语法

4.查

1、条件查询

2、聚合

3、分组

4、分页

5、关联

mysql处理添加外键时提示error 150 问题的解决方法_MySQL

mysql处理添加外键时提示error 150 问题的解决方法

MySQL使用外键实现级联删除与更新的方法

MYSQL建立外键失败几种情况记录Can't create table不能创建表

MySQL外键使用及说明详解

MySQL添加外键时报错：1215 Cannot add the foreign key constraint的解决方法

mysql处理添加外键时提示error 150 问题的解决方法

MYSQL建立外键失败几种情况记录Can't create table不能创建表

浅谈hibernate急迫加载问题(多重外键关联)

MySQL添加外键时报错：1215 Cannot add the foreign key constraint的解决方法

Mysql 外键索引关联