MySQL数据类型_约束_SQL查询

最新推荐文章于 2024-05-28 12:32:57 发布
最新推荐文章于 2024-05-28 12:32:57 发布
阅读量182 收藏
点赞数
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_45570058/article/details/119573971
版权

MySQL_数据类型_约束_SQL查询

文章目录

1. 数据类型

既然是数据就必然存在类型之分，mysql数据库对于不同的数据也使用不同的类型进行表示：

  • 数值
  • 字符串
  • 日期时间

1.1. 数值类型

mysql数据库数值类型和Java数值类型对照表

MysqlJava
tinyintbyte
smallintshort
intint
bigintlong
floatfloat
doubledouble
decimal(m,d)java.lang.BigDecimal

Decimal类型中m表示数值长度,d表示小数点后位数（精度）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uTrNQKEe-1628585723558)(assets/image-20210804094535059.png)]

常用的数值类型：

  • tinyint 一般用于表示状态字段，例如：订单状态(0-未支付，1-已支付，2-已出库，3-配送中)
  • int：用于表示常见的整数值，例如：年龄，库存
  • double：表示常见浮点数据，例如：体重，分数
  • decimal：用于表示对精度要求高的数值类型，例如：金额

1.2. 字符串类型

Mysql中对于字符串数据表示包含了很多可以选择的类型：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-O1xettcb-1628585723559)(assets/image-20210804095337363.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DIBBzXyW-1628585723561)(assets/image-20210804095438410.png)]

以上char和varchar中的M实际表示的是字符数，Mysql中字符数据占据的存储空间按照如下：

  1. 字符集如果是GBK则一个字符占据2个字节，使用char或varchar存储中文时最大范围是（65536/2）
  2. 字符集如果是UTF8则一个字符占据是3个字节，使用char或varchar存储中文时最大范围是（65536/3）

常见字符串类型：

  • varchar:可变长字符串
  • char:定长字符串
  • text:表示大文本类型
  • enum:枚举类型

常见面试题：

  • char类型和varchar类型区别?

  • char类型是一个定长字符串类型，无论实际为列指定的值大小多少，都会占满申请的空间，不足的内存空间使用空格填充，一般用于长度固定的数据列（例如:性别，手机号，身份证号），不存在大小改变问题，因此效率较高。

  • varchar类型是一个可变长字符串类型，在声明时，数据列不会占据存储空间，只在具体赋值时，根据数据值的实际大小占据空间，在内存空间方面能够合理分配，但是如果涉及到值的长度更改，则需要重新分配，因此效率相对char较低，一般用于长度不定的数据列（例如:姓名，个人简介等）。

  • enum枚举类型使用：

  • 可以使用枚举类型对可选的数据值进行列举，表示列中存储的数据只能是枚举中的类型，也可以使用序号来表示改列值：

     create table t4(a enum('男','女','male','famale'));

 --使用方式1：直接从枚举中选择值
 insert into t4 values('女');
 --使用方式2：使用枚举字段序号表示枚举值
 insert into t4 values(2);
 insert into t4 values('2');

  • 在Mysql中字符串类型和数值类型存在隐式转换机制：

create table t5 values(a int,b varchar(5));

--向表中插入数据
 insert into t5(a,b) values('123','123');
 insert into t5(a,b) values(456,456);

注意事项：对于以上隐式转换机制，只适用于数值数据（数值类型，字符串类型数值）。

1.3. 日期时间类型

Mysql对于日期时间也提供一些常见的数据类型来表示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ePWPJtnc-1628585723563)(assets/image-20210804104326607.png)]

常用的日期时间类型：

  • date:用于表示日期（年-月-日）
  • datetime:用于表示日期时间（年-月-日 时-分-秒）
  • timstamp:用于表示时间戳（在创建表时可以指定默认值，自动生成，也可以在更新数据时自动更新）

常见日期类型的使用:

 create table t6(a date);
 insert into t6(a) values('2021-08-08');
 insert into t6(a) values('20210808');
 insert into t6(a) values('2021/08/08');
 -- now()表示当前系统时间（内置函数）
 insert into t6(a) values(now()); 
 
 create table t7(a datetime);
 insert into t7(a) values('2021-08-08 12:13:34');
 insert into t7(a) values(now());
 
 create table t8(id int,a timestamp default CURRENT_TIMESTAMP);
 -- 若未指定timestamp的列值，此时数据库服务器会自动生成系统时间
 insert into t8(id) values(2);

注意事项：

  • 在使用timestamp作为列类型时，一张表中只能为一个timestamp列指定default CURRENT_TIMESTAMP

1.4. 数据类型总结

Mysql中常见的数据类型主要包含：

  • 数值类型：
    • int
    • double
    • decimal
  • 字符串类型
    • char
    • varchar
    • text
  • 日期时间类型
    • datetime
    • timestamp
    • date

2. 维护数据完整性-约束

Mysql中对数据完整性的保障策略主要包含以下:

  • 约束（constraint）
  • 数据库可编程性（触发器）

约束是其中最为简单也是最为常用的一种方式，数据库中的约束主要包含以下：

  • primary key
  • unique
  • not null
  • default
  • check（Mysql8.0实现）
  • foreign key

2.1. 主键约束（Primary Key）

通常在创建表时，为了保证每条数据的唯一性，一般都会为每条数据指定一个标志列，该标志列存在以下特征：

  • 唯一
  • 非空

使用方式：

  1. 创建表时直接在字段后定义
create table student(
    id int primary key, -- 设置主键  
	...
)
  1. 建表时，在所有字段末尾设置
create table student(
	id int,
    sname varchar(16),
    ...
    primary key(id) -- 设置主键列
)
  1. 表已经创建完成，通过DDL语句修改指定列为主键列
alter table student add constraint pk_sno primary key(id)

auto_increment:用于设置键列（主键）的自动递增，即该列的值无需再添加数据时手动指定，可以由系统自动生成。

主键的使用事项：

  1. 主键列一般用于标志列（不能重复，不能为空）
  2. 尽量避免使用联合主键（设置多个列为主键）
  3. 任何表都应该存在一个主键
  4. 针对主键列的查询操作效率最高的（唯一索引）

2.2. 唯一约束（Unique）

设置当前列的值在表中不能出现重复的，例如：手机号，账号，qq号，邮箱地址，使用方式:

在建表时使用unique关键字约束字段即可

phone varchar(16) unique

2.3. 非空约束（Not Null）

设置当前列必须存在一个具体值，不能为null,除非由设置过列的缺省值（default），使用方式：

在建表时使用not null关键字约束

username varchar(16) not null

2.4. 缺省值约束（Default）

为当前列设置一个缺省值（默认值）,即添加数据时若未指定具体列值，则使用缺省值代替，使用方式：

在建表时使用default 默认值指定

password varchar(32) default '123456'

2.5. 检查约束（Check）

检查约束用于限定列中数据的范围和内容，例如限制性别列只能是男或女；但是该约束的生效是在mysql8.0.5以上版本，以下的版本还未被实现（不生效）

sex char(2) check(sex in('男','女'))

针对版本问题，我们实际可以使用enum代替

sex enum('男','女')

约束综合使用：

create table student(
	sno int,
	sname varchar(16) not null,
	pwd varchar(32) not null default '123456',
	phone char(11) unique,
	sex enum('男','女')),
	primary key (sno)
);

2.6. 外键约束（Foreign Key）

为了维护表和表之间的数据完整性，通常会使用一种约束：外键约束，对数据列进行限定；

外键约束指的是将一个表(从表)的某一个列于另一个表(主表)的主键列关联，这样一来，从表中的数据列值只能从主表列中筛选，外键创建方式分为两种：

  1. 在创建表时直接指定

    create table student
(
	sno int primary key auto_increment,
    sname varchar(16) not null,
    pwd varchar(32) default '123456',
    cno int,
    foreign key(cno) references classes(cno) -- 设置外键
)

  2. 表已创建完成，后续通过DDL修改

    alter table student add constraint fk_cno foreign key(cno) references classes(cno);

外键约束注意事项：

  1. 外键列必须关联其他表的主键列
  2. 被关联的表叫主表，外键列所在表叫从表
  3. 在进行数据删除时，需要先删除从表数据再删除主表数据

关于pk_sno和fk_cno：

指的是主键外键的别名,类似变量名,可以通过该名称对主外键进行删除操作：

alter table student drop foreign key fk_cno;

3. 运算符

3.1. 算术运算

运算符操作方式说明
+a + b实现两数相加
-a - b两数相减
--a一元减号，取负值
*a * b两数相乘
/a / b两数相除
%a % b两数取余

3.2. 关系运算

运算符操作方式说明
=a=b如果两操作数相等则为true
!=,<>a != b,a<>b如果两操作数不相等则为true
>a > b如果a大于b,为true
>=a >= b如果a大于等于b,为true
<a < b如果a小于b,为true
<=a <= b如果a小于等于b,为true
ina in(b1,b2,b3…)判断a是否是b1,b2,b3…中的一个
between…anda between b1 and b2判断a是否在b1,b2之间
likea like b如果a与b匹配，则为true
not likea not like b如果a与b不匹配，则为true
is nulla is null如果操作数为null，则为true
is not nulla is not null如果操作数不为null，则为true

3.3. 逻辑运算

运算符操作房四海说明
and,&&a and b,a && b逻辑与，两操作数都为true则为true
or，||a or b,a||b逻辑或，两操作数任意一个为true，则为true
not,!not a,!a逻辑非，如果操作数为false，则结果为true

4. SQL查询

SQL语句中对于程序员来说最为重要的当属DML，而DML语句中最常用的语句一般为查询语句select;对于增删改操作千篇一律，但是查询语句千变万化。

查询语句的语法规范：

select 查询列 from 查询目标表 
where 查询条件  
group by 分组依据 having 分组条件 
order by 排序列 
limit 起始查询位置,结果集条数

对于查询语句的编写可以先从以下几个目标展开：

  1. 查询表是哪一张（哪几张）
  2. 查询列有哪些
  3. 查询条件

4.1. 单表查询

4.1.1 基础查询
-- 查询所有数据
select * from student;

-- 查询指定列
select sno,sname,phone from student;

-- 查询所有学生姓名（去除重复）
select distinct sname from student;

-- 查询学生姓名，手机号（拼接前缀）
select sname,concat('+86-',phone) from student;

/* 聚合函数（分组函数）*/
-- 统计数据的总行数
select count(*) from student;

-- 查询最高的学分
select max(score) from student;

-- 查询最低的学分
select min(score) from student;

-- 查询平均的学分
select avg(score) from student;

-- 对学分求和
select sum(score) from student;
4.1.2. 条件查询
-- 查询所有学分在60分以上的学生信息
select * from student where score >= 60;

-- 查询区间值（查询分数在70~90之间的学生）
select * from student where score>=70 and score <=90;
select * from student where score between 70 and 90;

-- 查询james,lily,lucy三名学生的成绩和姓名
select score,sname from student where sname='james' || sname='lily' || sname='lucy';
select score,sname from student where sname='james' or sname='lily' or sname='lucy';
select score,sname from student where sname in('james','lily','lucy');
 
-- 查询所有学分不等80 的学生信息
select * from student where score != 80;
select * from student where score <> 80;

/*模糊查询*/
-- 查询所有名字以"l"开头的学生信息
select * from student where sname like 'l%';

-- 查询所有名字以"j"开头5个子符的学生
select * from student where sname like 'j____';

-- 查询所有名字中包含’e‘的学生信息
select * from student where sname like '%e%';

-- 查询任意列中包含指定查询目标字符串（将部分列拼接成一个字符串）
select concat(sno,sname,phone,sex,score)  from student where concat(sno,sname,phone,sex,score) like '%89%' ;

-- 查询名字不是以"j"开头的学生信息
select * from student where sname not like 'j%';

-- 查询所有没有参加社团的学生
select * from student where gid is null;

-- 查询所有参加过社团的学生
select * from student where gid is not null;

模糊查询问题：

  • % 是一个占位符，表示任意个字符
  • _ 是一个占位符，只表示一个字符
4.1.3. 分组，排序，分页
/* 分组 */
-- 查询每个班级的平均分和班级号
select cno,avg(score) '平均分' from student group by cno;

-- 查询每个班级的总人数
select cno,count(sno) from student group by cno;

-- 查询出总人数超过4个人的班级和总人数
select cno,count(sno) from student group by cno having count(sno) > 4;

/* 排序 */
-- 查询所有学生成绩，并排序显示（默认从低到高 asc）
select * from student order by score;

-- 查询所有学生成绩，并降序显示
select * from student order by score desc;

-- 查询每个班的平均分并降序显示
select cno,avg(score) from student group by cno order by avg(score) desc;

/* 分页（对查询数据行限制）*/
-- 查询前5条数据
select * from student limit 5;

-- 查询分数排名前三的学生信息
select * from student order by score desc limit 3;

-- 查询第二页数据，每页显示3条，limit后面的参数：1-查询起始索引位置，2-结果集数目
select * from student limit 3,3;

注意事项：

  1. group by...having:查询列必须是分组列或者分组函数，having表示分组条件，分组条件也应该分组列或分组函数
  2. order by asc|desc:默认排序规则使用升序asc,order by一般出现在查询语句的末尾（在limit之前）
  3. limit:对结果集限制，可以使用1个或者两个参数，如果1个则指的结果集的前n条;如果是两个则，第一个表示查询的起始位置，第二个表示条数限制。
西伯利亚大熊猫
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
MySQL数据类型详解及部分使用范例
12-14
MySQL数据类型数据类型数值类型整数类型使用实例浮点数和定点数类型使用实例日期类型使用实例字符串类型相关概念CHAR和VARCHARBINARY和VARBINARYTEXT和BLOBENUM：单选SET：多选使用范例小小的注意事项 数据类型 ...
【MySQL】之字段约束类型
王廷云的博客
08-09 1137
目录 1、主键约束 Primary Key 2、外键约束 Foreign Key 3、唯一约束 Unique 4、非空约束 Not Null 5、默认值 Default 6、值自增 Auto_Increment 7、使用 0 填充空白 Zerofill 1、主键约束 Primary Key 主键约束相当于：唯一约束 + 非空约束的组合，主键约束的字段不允许重复，也不允许出现空值； 每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建； 主键可以由一个列组成，也可以有多个列组成
SQL Server 如何给属性添加标识？
weixin_48779307的博客
01-22 3611
SQL Server 如何给属性添加标识？ 标识列，又称自增列，有标识增量（相邻两个值之间的增量），标识种子（第一行的值），可以标识表的每一行。在插入数据时，标识列的值由系统按一定规律自动生成。 一 找到需要添加标识的属性列 本例中，依次展开，数据库→学生选课情况→表→dbo.Student→列 在“列”下方的属性中，任选一个属性，右键点击“修改” 二 添加标识 在右边出现的弹框中，选中需要添加标识的属性，在下方的列属性找到“标识规范”，展开“+”号，将“（是标识）”右栏修改为“是”即可 （可直接双击左键
MySql数据库基础知识
Amign的博客
06-10 686
MySql数据库基础知识 目标： 理解关系型数据库的基本概念 掌握关系模型 掌握MySql安装及连接 熟悉结构化查询语言定义及其分类 掌握数据库管理操作 掌握创建数据表，掌握MySql的数据类型 掌握数据库表操作 为何需要数据库： 存储数据的方法 第一种方法：用大脑来记住数据 第二种方法：写在纸上 第三种方法：写在计算机的内存中 第四种方法：写成磁盘文件 数据库能干什么 存储大量数据，方便检索和访问 保持数据信息的一致、完整 共享和安全 通过组合
mysql查看表的约束条件_（五）mysql表操作和约束条件
weixin_31430653的博客
01-19 510
(1)表基本操作1)创建表create table 表名(字段 数据类型 约束条件)mysql> create table student1(id int,name varchar(50),sex enum('w','f'),age int);2)查看表mysql> desc student1; #查看表结构+-------+---------------+------+-----+...
MySQL字段类型查询语句_几种常用数据库字段类型查询语句
weixin_32440261的博客
02-06 789
这里总结几个数据库的查询所有表字段名和字段类型的SQL语句:Mysql database:select COLUMN_NAME, DATA_TYPE from information_schema.COLUMNS where table_name =注意:这样查询能把所有数据库中table_name为该名字的表查询出来;如果要指定数据库可以所有下面的语句:select COLUMN_NAME,DA...
MySQL约束类型及举例介绍
01-19
约束类型包括：NOT NULL （非空约束），PRIMARY KEY（主键约束），UNIQUE KEY（唯一约束），DEFAULT（默认约束），FOREIGN（外键约束） 1.主键约束 PRIMARY KEY 每张数据表只能存在一个主键 主键保证记录的唯一...
MySQL语句之约束条件的设置
12-14
在向数据表中插入数据时，如果用户希望每条记录的编号自动生成，并且按照顺序排列，可以为该字段设置自增约束。 自增约束的关键词是：AUTO_INCREMENT。 语法形式： col_name data_type AUTO_INCREMENT 注意： ①...
（2）MySQL——————DML，DDL，数据类型，约束以及事务
12-14
3，数据类型 3.1，整型 3.2，浮点型 3.3，字符型 3.4，日期型 4，约束 4.1，什么是约束 4.2，NOT NULL 约束 4.3，UNIQUE 约束 4.4，PRIMARY KEY 约束 4.5，FOREIGN KEY 约束 5，事务 5.1，什么是约束 5.2，事务的...
18.MySQL中的LIKE(模糊查询) 和 NOT LIKE
码海无际的博客
04-09 3099
目录 1.准备 2.LIKE 3.NOT LIKE LIKE运算符的作用就是模糊匹配，NOT LIKE的使用方式与之相同，用于获取匹配不到的数据。 1.准备 CREATE DATABASE mahaiwuji; USE mahaiwuji; CREATE TABLE emp ( empno INT PRIMARY KEY, ename VARCHAR(10), sal INT...
03-MySQL查询语句及其约束
qq_37602325的博客
06-25 214
查询语句，约束，数据库的设计，数据库的备份和还原
mysql查看表的约束条件_mysql-查看表中所有约束
weixin_26766559的博客
01-28 3944
一、查看所有数据库中所有约束select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE;系统字典中所有列：CONSTRAINT_CATALOG |CONSTRAINT_SCHEMA |CONSTRAINT_NAME|TABLE_CATALOG |TABLE_SCHEMA | TABLE_NAME |COLUMN_NAME |ORDINAL_POSITIO...
数据库 数据类型 字段约束 基础函数、条件查询
无名氏—不学代码的博客
08-03 373
1 数据类型 1.1 命名规则 l 字段名必须以字母开头，尽量不要使用拼音 l 长度不能超过30个字符（不同数据库，不同版本会有不同） l 不能使用SQL的保留字，如where,order,group l 只能使用如下字符az、AZ、0~9、$ 等 l Oracle习惯全大写：USER_NAME，mysql习惯全小写：user_name l 多个单词用下划线隔开，而非java语言的驼峰规则 1.2 字符 l char长度固定，不足使用空格填充，最多容纳2000个字符，char(11)存储ab
mysql查看约束_Mysql通过sql语句添加约束和查看约束
weixin_36365168的博客
01-25 4908
一：添加约束1.添加唯一约束：alter table student add constraint uk_name unique(name);2.添加外键约束：altertable表名addconstraintfk_字段名foreignkey(字段名)references关联的表名(关联的字段名)3.添加check约束：altertable表名addconstraintCK...
Mysql的基本使用总结！( 数据类型 - 约束 - 范式 - 陌生命令 - 联合查询 - 事务)
一个塑料袋
03-23 190
目录 1："数据类型"： 2："约束"： 3："三大范式"： 4："基础命令"： 5："连接查询"： 6："事务"： "数据类型"： 日期按照格式选择，数值和字符串按照范围大小选择。 下方几张图片来自菜鸟教程 "约束"： 约束可以在建表的时候添加： alter table 表名 add 约束(字段); ...
【Java面试】四、MySQL篇（上）
llg___的博客
05-27 951
如果去维护一个类似二叉树的结构，再查age=45的数据，则直接从根节点开始⇒ 45 > 36，去右侧 ⇒ 45 < 48 ⇒去左侧 ⇒ 查找完毕，如此，查找效率提升，这即索引的思想。是覆盖索引，虽然select * ，但其where是根据id过滤的，即用的是主键索引、聚簇索引，索引的叶子节点存了整行数据，需要返回的字段，在索引中能够全部找到。给name字段加了非聚簇索引，因此，执行如上SQL，先根据name的非聚簇索引的B+树 ⇒ A小于L，走左边，到G和J，再走左边，找到Arm ⇒ 因为是。
mysql存储地理信息的方法
m0_72958694的博客
05-27 474
mysql存储地理信息的方法详细教程。
MySQL导入SQL脚本---超详细介绍
qq_38382365的博客
05-27 268
3.执行mysql -uroot -p123456 --default-character-set=utf8命令。需要注意的是--default-character-set=utf8，要不然可能会有报错。2.在mysql安装目录下cmd进入小黑窗。1.新建xxx数据库，字符集选对。执行完上面的命令等待结束就可以了。
java医院管理系统源码(springboot+vue+mysql)
最新发布
sinat_26552841的博客
05-28 411
的主要使用者分为管理员功能，个人中心，医生管理，护士管理，病床类别管理，药品类别管理，科室信息管理，内部论坛管理，系统管理。医生功能，个人中心，病床管理，药房管理，病人信息管理，药方信息管理。护士功能修改个人中心，查看病床管理，查看药房管理，查看病人信息管理，查看药方信息管理。💕💕作者：风歌（风定落花生，歌声逐流水的风歌）💕💕个人简介：混迹在java圈十年有余，擅长Java、微信小程序、Python、Android等，大家有这一块的问题可以一起交流！💕💕各类成品java毕设。
mysql建表 sql语句_mysql
07-15
上述建表语句中，id列使用INT数据类型，表示整数，并设置为主键（PRIMARY KEY），同时使用AUTO_INCREMENT属性来自动增加其值。name列使用VARCHAR(50)数据类型，表示最大长度为50的字符。age列使用INT数据类型，表示...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

西伯利亚大熊猫

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值