Mysql数据库学习笔记（一）——数据库基础和常用数据类型

目录

1.数据库的相关概念

2.1数据库和表的基本操作

2.2数据类型和运算符

---

前言：毕业大半年了，发现大学学习的数据库知识难以应对日常项目中的正常开发需求，自己对Mysql也是停留在基本的CRUD语句的层面上；随着工作的加深发现更加需求系统化的学习才能对问题有较深的理解和见解，下定决心补补这方面的知识，所以买了《MySQL从零开始学》这本比较经典的书籍。读完之后收获颇多，整理笔记和大家分享。

1.数据库的相关概念

数据库名词定义差异大，一种比较普遍的观点

数据库（DataBase）：是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。

特点：实现数据共享，减少数据冗余；采用特定的数据类型；具有较高的独立性；具有统一的数据控制功能。

表：在关系数据库中，数据库表是一系列二维数组的集合，用来存储数据和操作数据的逻辑结构。

 注：行为记录，列为字段

主键(PRIMARY KEY)：又称主码，用于标示表中的每一条记录。（不能为空）

2.1数据库和表的基本操作

注：Windows下MySql默认是不区分大小写的

创建数据库	删除数据库
CREATE DATABASE 数据库名字	DROP DATABASE 数据库名字

2.1数据库表的基本操作

2.1.1创建数据表（不能使用数据库关键字做表名）

CREATE TABLE 表名（

字段名1，数据类型 [约束条件][默认值],

….

[表约束条件]

）

例：

CREATE TABLE  t_user (

  id int(11) NOT NULL AUTO_INCREMENT,

  username varchar(20) DEFAULT NULL COMMENT '用户名',

  password varchar(18) DEFAULT NULL COMMENT '用户密码',

  PRIMARY KEY (id)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

2.1.2外键约束

外键：首先它是表中的一个字段，它可以不是本表的主键，但对应另外一个表的主键，主要作用是保证数据引用的完整性，定义外键后，不允许删除在另外一个表中具有关联关系的行。

主表：被关联主键字段的那张表。

子表：外键所在的那张表就是子表

注：子表的主键必须关联主表的主键，且关联字段的数据类型必须匹配且存储引擎必须相同，如果类型不一致，创建子表时会报1005错误。

创建语句

[CONSTRAINT <外键名>] FOREIGN KEY (字段1，字段名2…) REFERENCES <主表名>（主键列1）

CREATE TABLE sys_user_role (

  id bigint(20) NOT NULL AUTO_INCREMENT(表示属性值自动递增),

  user_id bigint(20) DEFAULT NULL COMMENT '用户ID',

  role_id bigint(20) DEFAULT NULL COMMENT '角色ID',

  PRIMARY KEY (id),           --主键

  KEY user (user_id),

  CONSTRAINT user FOREIGN KEY (user_id) REFERENCES sys_user (id) –外键

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='用户角色关联表';

大家在工作中一般使用可视化工具创建外键中会选择外键约束

 

RESTRICT、NO ACTION

删除：从表记录不存在时，主表才可以删除。删除从表，主表不变

更新：从表记录不存在时，主表才可以更新。更新从表，主表不变

 

CASCADE

删除：删除主表时自动删除从表。删除从表，主表不变

更新：更新主表时自动更新从表。更新从表，主表不变

 

SET NULL

删除：删除主表时自动更新从表值为NULL。删除从表，主表不变

更新：更新主表时自动更新从表值为NULL。更新从表，主表不变

2.1.3数据表结构

DESCRIBE 表名  简写：  DESC   表名；

Fied:字段名      Type：类型     NULL：表示该列是否可以存储NULL值。

Key:表示该列是否已编制索引，PRI表示是表主键的一部分；UNI表示该列是UNQUE索引的一部分（后面有所有内容）；MUL表示在列中某个给定的值允许出现多次。

Default:该列是否有默认值，如果有是多少。

Extra:表示可以获取的与给定列有关的附加信息。如：AUTO_INCREMENT（自增）等。

2.1.4修改表数据

“[]”中的内容表示可选

修改表名	ALTER  TABLE 旧表名 RENAME 新表名
修改字段数据类型	ALTER  TABLE 表名 MODIFY 字段名 新数据类型(约束) 例:ALTER TABLE USER MODIFY INT(11)
修改字段名	ALTER  TABLE 表名  CHANGE 旧字段名 新字段名 新字段类型(约束)
添加字段名	ALTER  TABLE  表名  ADD 字段名 类型(约束)  [FIRST|AFTER 已存在字段名]  (表示新添加字段在第一列|已存在字段之后) 不写默认添加到最后
删除字段	ALTER  TABLE 表名 DROP 字段名

以上是最基本的操作，实际项目中可以使用可视化工具进行操作；但开发难免会接触到linux服务器，所以应该会最基本的操作

2.2数据类型和运算符

数值类型
整数型	tinyint、smallint、mediumint、int、bigint
浮点小数数据类型	float、double
定点小数类型	decimal
日期/时间类型	year、time、date、datetime、timestamp
字符串类型	char、varchar、binary、varbinary、blob、text、enum、set

 

整数型

类型名称	存储需求	有符号范围	无符号范围
tinyint	1个字节	-128——127	0——255
smallint	2个字节	-32768——32767	0——65535
mediumint	3个字节	-8388608——8388607	0——16777215
int	4个字节	-2147483648——2147483647	0-4294967295
bigint	8个字节	-9223372036854775808-9223372036854775807	0-18446744073709551615

注：计算2^n-1; n为bit,一个字节=8bit

创建表时INT(11),这表示的是该数据类型指定的显示宽度，指定能够显示的数值中的个数；显示宽度和数据类型的取值范围是无关的；限制插入数据值的是字段数据类型的取值范围。如：Int(3)会占用4个字节的存储空间，最大值也不是999，而是int类型的取值范围决定的。

小数型

类型名称	说明	存储需求
float	单精度浮点数	4字节
double	双精度浮点数	8字节
decimal(M,D)	压缩的”严格”定点数	M+2个字节

注：decimal实际是以串的方式存放的，其取值范围的大小决定于M的值。再使用数据库字段进行运算的时候推荐使用decimal，因为在精度进行四舍五入时，float和double不会给出警告且很容易出现其它问题。

 

日期类型基本能够见名知意，不再赘述。

字符串类型

类型名称	说明	存储需求(字节)
char（M）	固定长度的非二进制字符串	M，1<=M=<255
varchar(M)	可变长度非二进制字符串	L+1，L<=M且1<=M=<255
tinytext	非常小的非二进制字符串	L+1，L<2^8
text	小的非二进制字符串	L+2，L<2^16
mediumtext	中等大小的非二进制字符串	L+3，L<2^24
longtext	大的非二进制字符串	L+4, L<2^32
enum	枚举类型	取决于枚举数目，最大65535
set	一个设置，字符串对象可以有零个或多个	最多64

注：char类型尾部为空格时将会被删除掉，varchar则会保留尾部的空格。Varchar字符串占用的空间为字符串长度加1个字符串结束标识符。

二进制类型目前使用很少，而且也不提倡在数据库中使用二进制保存数据。不做详细讲解。

常用的比较运算符

=	等于
<=>	安全的等于（NULL=NULL返回1，判断NULL值；而当一个操作数为空时，返回为0，而不是NULL）
<>或!=	不等于
<=	小于等于
>=	大于等于
IS NULL	是否为空
IS NOT NULL	是否不为空
least	参数大于等于两个时，返回最小值（有NULL值，则返回NULL）
greatest	参数大于等于两个时，返回最大值（有NULL值，则返回NULL）
Between and	判断是否在一个区间
in	是否在in列表中
Not in	是否不在in列表中
like	通配符
regexp	正则表达式

注：1.正则表达式和like作用大致相同，但正则表达式更加灵活多变，可以使用更多通配

单引号，双引号，反斜杠，回车符，制表符，换行符等符号，这些符号不能直接输入使用，应该使用转义符进行转义，如输入反斜杠使用：”\\”。