背景

新年伊始,小伙伴是不是还处于假期综合症的状态。我们在日常运维数据库的时候,会时常查看数据库的状态,检查数据库是否正常运行。对于这些状态的熟悉对于我们处理数据库无法访问的

问题非常重要。当数据库突然变成一个你没有见到过的状态时,你就会非常慌乱,手足无措。这里给小伙伴普及下数据库的各个状态。已经他们是怎么转换的

 

前面的话

  mysql数据库中的数据存储在被称为表(tables)的数据库对象中。表是相关的数据项的集合,它由列(字段)和行(记录)组成。下面将详细介绍数据表操作

 

下面,列出一些实用的表达式:

案例

 

前两天在处理一个客户问题,突然某个数据库无法访问了。数据库下面的表也无法查看。从SSMS界面上看数据库是正常的状态(就是数据库名字后面没有显示特别的状态)。查看

SYS.DATABASES
查看状态列,发现是ONLINE。此时其他数据库是正常的,就这个库有问题。肯定是数据库状态不对。
那么问题出在哪里呢? 仔细观察发现这个问题的数据库 collation_name 是null
值。

原来问题在这,

刚刚联机的数据库不一定马上能接受连接。 要确定数据库何时可以接受连接,可以查询
sys.databases 的 collation_name 列或 DATABASEPROPERTYEX 的 Collation
属性。 在数据库排序规则返回非
Null 值之后,数据库就可以接受连接了。

 

于是用命令把数据库设置为脱机,然后马上联机,再查看sys.databases 的
collation_name 列 变成了非null值。此时数据库恢复正常。

 

 

准备工作

  在进行数据表操作之前,需要先登录mysql服务器,创建一个数据库,并使用创建好的数据库

澳门微尼斯人手机版 1

 

 

状态的转换

数据库有很多状态。他们是如何在这些状态之间进行切换的呢?下面这个图非常清晰的标示了各个状态的切换。在我刚学习数据库的时候,这个图给了我很大的帮助,

让我对数据库各个状态的转换有了很清楚的认识。

澳门微尼斯人手机版 2

 

 

ONLINE (在线) 

数据库可正常运行

RESTORING (正在还原)

数据库正在还原,当我们还原数据库使用NORECOVERY
模式时,数据库就会变成该状态

RECOVERING (正在恢复)

数据库启动,数据库创建,ALTER
ONLINE,RESTORE WITH
RECOERY 时,会经过这个状态,进行REDO,UNDO等操作。此时如果遇到问题就进入RECOVERY_PENDING。如果正常就会变成ONLINE。

RECOVERY_PENDING(等待恢复)

数据库在还原时遇到跟资源相关的错误,表明还原进程被挂起,数据库不能开始数据库的数据和日志的还原进程,这种情况下,最可能的原因是丢失数据文件或日志文件。

SUSPECT  (置疑)

数据库可能损坏了

EMERGENCY (紧急)

供DBA用来修复数据库的状态

OFFLINE (脱机)

离线状态

 

创建数据表

  下面在db1数据库中创建数据表tb1

CREATE TABLE [IF NOT EXISTS] table_name(column_name data_type,...)

  在下面的数据表tb1中,创建一个VARCHAR(20)的变长字符型的username字段;创建一个TINYINT
UNSIGNED的一个字节长度的无符号(0-255)整型的age字段;创建一个salary
FLOAT(8,2)
UNSIGNED的数字总长度为8,小数位数为2的无符号浮点数的salary字段

澳门微尼斯人手机版 3

 

1,路径取文件名

总结

了解数据库处于的状态非常重要。我们如果还不熟悉的小伙伴可以看看。

 

查看数据表

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]

查看数据表的列项

SHOW COLUMNS FORM tbl_name

澳门微尼斯人手机版 4

 

 

记录操作

  记录操作的第一步是写入记录

  在写入记录时,要注意的是,如果没有省略的字段,则按照参数顺序直接赋值即可;如果有省略的字段,则字段名和参数值都需要写出来

INSERT [INTO] tbl_name [(col_name,...)] VALUES(VAL,...)

澳门微尼斯人手机版 5

  下面来查找记录

SELECT EXPR,...FROM tbl_name

澳门微尼斯人手机版 6

 

1 RIGHT([FilePath],FINDSTRING(REVERSE([FilePath]),"\\",1) - 1)  
2 RIGHT(@[User::FilePath],FINDSTRING(REVERSE(@[User::FilePath]),"\\",1) - 1)  

记录约束

空值

NULL         字段值可以为空
NOT NULL     字段值禁止为空

  首先,创建一个数据表tb2,username字段禁止为空,而age字段可以为空。插入记录时,如果username字段为空,则提示错误

澳门微尼斯人手机版 7

主键

  每张数据表只能存在一个主键(PRIMARY
KEY),主键保证记录的唯一性,且自动为NOT NULL

  主键可以写为KEY或PRIMARY KEY

澳门微尼斯人手机版 8

自动编号

  自动编号(AUTO_INCREMENT)必须与主键组合使用,默认情况下,起始值为1,每次增量为1

澳门微尼斯人手机版 9

唯一

  唯一约束(UNIQUE
KEY)是指选定的记录中不可以存在相同值的情况,这样可以保证记录的唯一性,唯一约束的字段可以为空值NULL,每张数据表可以存在多个唯一约束

  下面记录中对username字段进行唯一约束限制,添加username为’Tom’记录后,不允许再添加username为’Tom’记录

澳门微尼斯人手机版 10

默认值

  当插入记录时,如果没有明确为字段赋值,则自动赋予默认值(DEFAULT)

澳门微尼斯人手机版 11

外键

  外键约束(FOREIGN
KEY)用来保持数据一致性和完整性,实现一对一或一对多的关系

  外键列是指加入(FOREIGN
KEY)的列,外键列参照的那一列叫做参照列,外键列和参数列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同

  索引是一种特殊的文件,在InnoDB数据表上的索引是表空间的一个组成部分,它们包含着对数据表中所有记录的引用指针。外键列和参照列必须创建索引,如果参照列不存在索引的话,MySQL将自动创建索引

  子表指有外键列的表,子表所参照的表叫做父表。父表和子表必须使用相同的存储引擎,而且禁止使用临时表。数据表的存储引擎只能是InnoDB

  所以,首先需要修改mysql配置文件my.ini中的默认存储引擎

default-storage-engine = INNODB

  创建父表provices,参照列为id

澳门微尼斯人手机版 12

  创建子表users,外键列为pid

澳门微尼斯人手机版 13

  父表provices的参照列id为主键列,主键在创建的同时,会自动创建索引

澳门微尼斯人手机版,  下面来查看父表provices的索引

澳门微尼斯人手机版 14

  下面来查看子表users的索引

澳门微尼斯人手机版 15

  外键约束的参照操作中,一共存在四个选项。用来设置更新父表时,子表是否也进行相应操作

  1、CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行

  2、SET NULL:
从父表删除或更新行,并设置子表中的外键列为NULL。如果使用该选项,必须保证子表列没有指定NOT
NULL

  3、RESTRICT: 拒绝对父表的删除或更新操作

  4、NO ACTION: 标准SQL的关键字,在MYSQL中与RESTRICT相同

  首先,创建父表provinces

澳门微尼斯人手机版 16

  创建子表users1,并设置选项cascade

澳门微尼斯人手机版 17

  在父表中插入记录’A’、’B’、’C’

澳门微尼斯人手机版 18

  在子表中,插入名字’a1’、’a2’、’a3’、’a4′

澳门微尼斯人手机版 19

  删除父表中id为2的记录,并查看删除后父表和子表的结果

澳门微尼斯人手机版 20

  在实际的开发过程中,我们很少使用物理的外键约束,很多都去使用逻辑的外键约束,因为物理的外键约束只有INNODB这种引擎才会支持,像我们另外的一种引擎MYISAM则不支持,反过来说,如果要创建的数据表,假设存储引擎为MYISAM,而且又想使用外键约束的话,其实是不可能实现的,所以说,在实际的项目开发中,并不去定义物理的外键,所谓的逻辑外键指的是在定义两张表的结构的时候,按照存在的某种结构的方式去定义,但是不去使用FOREIGN
KEY这个关键词来定义

【表级约束和列级约束】

  约束除了按照功能,分为上面介绍过的5种约束外,也可以按照作用范围分为表级约束和列级约束

  对一个数据列建立的约束,称为列级约束,对多个数据列建立的约束,称为表级约束。列级约束既可以在列定义时声明,也可以在列定义后声明。而表级约束只能在列定义后声明

  在实际开发中,用列级约束比较多,表级约束很少用,除此之外,在所有的约束中,并不是说每种约束都存在着表级或列级约束,其中,NOT
NULL
非空约束,DEFAULT约束这两种约束就不存在表级约束,它们只有列级约束,而对于其他的三种,像主键、唯一、外键约束,它们都可以存在表级和列级约束

 

 

列操作

添加单列

ALTER TABLE tb!_name ADD[COLUMN] col_name column_definition [FIRST|AFTER col_name];

  添加单列有三个位置选择,位于起始处,位于指定列的后面和位于最后

  first加入的放在整张表最前面,after放在指定列后面,不填则放在整张表最后

澳门微尼斯人手机版 21

添加多列

ALTER TABLE tb1_name ADD[COLUMN] (col_name column_definition,...);

  添加多列只能位于最后

删除单列

ALTER TABLE tb1_name DROP [COLUMN] col_name

澳门微尼斯人手机版 22

删除多列

ALTER TABLE tb1_name DROP col1_name, DROP col2_name, ...

澳门微尼斯人手机版 23

 

2,空字符设置默认值

约束操作

添加主键约束

ALTER TABLE tb1_name ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type](index_col_name,...)

  在未添加主键前,表users2有’username’、’pid’和’id’三个field

澳门微尼斯人手机版 24

  向字段’id’添加主键约束

澳门微尼斯人手机版 25

添加唯一约束

  唯一约束与主键约束的不同之处在于,唯一约束可以存在多个字段,而主键约束只能有一个

ALTER TABLE tb1_name ADD [CONSTRAINT[symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type]

澳门微尼斯人手机版 26

添加外键约束

ALTER TABLE tb1_name ADD [CONSTRAINT[symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

澳门微尼斯人手机版 27

添加或删除默认约束

ALTER TABLE tb1_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

澳门微尼斯人手机版 28

澳门微尼斯人手机版 29

澳门微尼斯人手机版 30

删除主键约束

ALTER TABLE tbl_name DROP PRIMARY KEY

澳门微尼斯人手机版 31

删除唯一约束

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

澳门微尼斯人手机版 32

澳门微尼斯人手机版 33

删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

澳门微尼斯人手机版 34

澳门微尼斯人手机版 35

  删除外键约束后,如果索引也不要,可以接着删除之索引

澳门微尼斯人手机版 36

 

 

修改列

修改列定义

  修改列定义指修改列的类型或位置

ALTER TALBE tb1_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]

澳门微尼斯人手机版 37

  下面将字段’id’的位置调整到第一个

澳门微尼斯人手机版 38

  下面,将字段’id’的类型由smallint转换为tinyint

  [注意]在由大范围的类型转换为小范围类型时,可能会造成数据丢失

澳门微尼斯人手机版 39

修改列名称

  使用下面的CHANGE语法,比MODIFY语法更加强大,可以在修改列名称的同时,修改列类型

ALTER TABLE tb1_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

澳门微尼斯人手机版 40

 

1 LEN([STRING_COLUMN]) == 0 ? "default value" : [STRING_COLUMN]  
2 ISNULL([STRING_COLUMN]) ? "default value" : [STRING_COLUMN]  
3 ISNULL([STRING_COLUMN]) || LEN([STRING_COLUMN]) == 0 ? "default" : [STRING_COLUMN]  

数据表更名

方法一 

ALTER TABLE tb1_name RENAME [TO|AS] new_tb1_name

澳门微尼斯人手机版 41

方法二

  使用RENAME方法可以为多张数据表更名

RENAME TABLE tb1_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2] ...

澳门微尼斯人手机版 42

  [注意]不要随意改变数据列和数据表的名字

3,数字

例如:123 变 00123

1 RIGHT(("00000" + [STRING_COLUMN]), 5)  
2 REPLICATE("0", 5 - LEN([STRING_COLUMN])) + [STRING_COLUMN]  

 

4,日期

发表评论

电子邮件地址不会被公开。 必填项已用*标注