MySQL数据库对数据表的管理

对数据表的管理

数据类型

数据表的字段类型，在MySQL中总的分为3大类，数值、时间和日期、字符串。合理利用字段的类型有时可以节省资源也可以提升数据库的效率。

类型	举例
数值型	TINYINT、INT、FLOAT、DOUBLE、DECIMAL(存储金额专用)等等
时间和日期型	DATE、TIME、DATETIME(日期加时间)、TIMESTAMP
字符串类型	CHAR(定长即必须满足该长度)、VARCHAR(边长即小于等于该长度)、BLOB(二进制数据)、TEXT(长度大字符数据)

更多详细信息请转移到此

建立一张表

使用一个例子来介绍如何建一张表，有哪些关键字等，在有SQL基础下会很好理解。

# 建立一张名为tbone的数据表，其中id是无符号自增的主键，title是有默认值的字符串，
# content是文本类型字符串，时间是默认当前时间的时间类型，
# 存储引擎使用InnoDB存储字符编码为utf8
CREATE TABLE IF NOT EXISTS `tbone` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(32) NOT NULL DEFAULT '暂无',
  `context` text,
  `submit_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `price` float(8,2) unsigned, 
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
# 省略collate效果相同，即charset=utf8默认的排序规则Collate=utf8_general_ci

如果需要在建好表的情况下进行修改会使用到ALTER子句，以下简单的举几个例子好触类旁通：

# 需要增加表中的字段，add后如建表是写即可
ALTER TABLE tb_name ADD column_name datatype...;
# 需要删除表中的字段
ALTER TABLE tb_name DROP COLUMN column_name;
# 需要修改表中字段，modify后面按照如新建的表时写即可
ALTER TABLE tb_name MODIFY COLUMN column_name datatype...;
# 需要修改表的引擎如从MyISAM到InnoDB
ALTER TABLE tb_name ENGINE=InnoDB;
# 需要修改表的编码方式从GBK到UTF8，该编码方式是对数据表本身做设置
ALTER TABLE tb_name CHARSET=utf8;
**补充说明**
SET NAMES utf8; # 它解决的问题是MySQL终端与数据库Server之间传输使用统一的编码方式 

删除一张表

DROP TABLE tb_name;
# Delete只是删除表中的数据，后插入的数据id会从最后一个开始，
# 省去where条件语句会删除表中每一行
DELETE FROM tb_name WHERE xxx=xxx;
# TRUNCATE是重置表中数据，即id会从1从新开始
TRUNCATE TABLE tb_name;

复制数据表(2种方式)

# 只~复制表结构~到新表
create table new_tb_name like old_tb_name; 

# ~复制表结构及数据~到新表
create table new_tb_name select * from old_tb_name; 
# 甚至可以挑取某些字段及其数据来建新的表
create table new_tb_name select column1,column2[,column3...] from old_tb_name; 

处理重复数据

想要使某一行作为唯一的，可以有一下方式：

• 将其作为主键primary key
• 为其建立unique索引

但是我们设置了唯一索引，那么在插入重复数据时，如果直接使用INSERT INTO语句将无法执行成功,会报错，可以使用INSERT IGNORE INTO 或者 REPLACE INTO避免报错。

三者的区别：
1. INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

2. INSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。

约束，一种对字段的规定

如果对表中的某一个字段进行约束，称之为列级约束。如果对2个字段及以上进行约束，那么称之为表级约束。

• NOT NULL # 指示某列不能存储 NULL 值。
• UNIQUE # 保证某列的每行必须有唯一的值。
• PRIMARY KEY # NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY # 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK # 保证列中的值符合指定的条件。
• DEFAULT # 规定没有给列赋值时的默认值。

外键约束的参照操作：
CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL
RESTRICT:拒绝对父表的删除或更新操作
NO ACTION:标准SQL的关键字，在MySQL中与RESTRICT相同

CREATE TABLE user1(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY(pid) REFERENCES provinces (id) ON DELETE CASCADE

外键约束最后：ON [操作] [操作类型]。
通常的开发情况下不直接使用物理外键约束，而是使用逻辑外键约束。

创建自定义函数

自定义函数用得不多，但是还是记录下来。供以后参考：

# 无复合结构的自定义函数
CREATE FUNCTION fun1([param1 data_type,param data_type])
RETURNS [data_type]
RETURN [SQL]

# 有复合结构的自定义函数
DELIMITER //
CREATE FUNCTION fun1([param1 data_type,param data_type])
RETURNS [data_type]
BEGIN
# ---复合结构，复合体中可以有条件控制、循环控制---
SQL1;
SQL2;
RETURN [SQLX];
# ---复合结构---
END//

存储过程

一般SQL执行过程是，分析合法性、编译SQL、执行可编译代码，得到结果并返回。而存储过程是将第一次编译代码作为一个单位取名保存下来，第二次执行该相同SQL语句时直接执行。(即：是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。)这样的好处是：

• 提升了代码执行效率；
• 减少了网络流量；

代码演示：

# 简单无参数存储过程
CREATE PROCEDURE sp() SELECT version();
# 调用无参数存储过程有方式2种：无参数可以省去双括号
CALL sp();
CALL sp;
# 建立有参数的存储过程，调用是就如同函数一样使用CALL进行调用
DELIMITER //
CREATE PROCEDURE removeById(IN param1 data_type) # IN表示输入的参数
BEGIN
DELETE FROM userTable WHERE id=param1;
END// # 存储过程编写结束
DELIMITER;

CALL removeById(id); # 调用该过程

# 建立有输入，输出的存储过程
delimiter//
create procedure p1(IN pid smallint unsigned,OUT num smallint unsigned)
begin
delete from user where p_id=pid; # 局部变量pid，执行一次消失
select count(pid) from users into num; # 存储到变量nums中
end//
CALL p1(12,@nums); # 声明用户变量，该会话间有效
select @nums; # 显示用户变量的值

# 建立有输入，多个输出的存储过程
DELIMITER //
CREATE PROCEDURE removeUserByAgeAndReturnInfos(
IN p_age SMALLINT UNSIGNED,
OUT deleteUsers SMALLINT UNSIGNED,
OUT userCounts SMALLINT UNSIGNED)
BEGIN
DELETE FROM usersTb WHERE age = p_age;
SELECT ROW_COUNT() INTO deleteUsers;
SELECT COUNT(id) FROM usersTb INTO userCounts;
END//
CALL removeUserByAgeAndReturnInfos(12,@a,@b);
SELECT @a,@b;

# 删除某个存储过程
DROP PROCEDURE procedure_name;

存储过程与自定义函数的区别：

• 存储过程实现的功能要复杂一些；而函数的针对性更强
• 存储过程可以返回多个值；函数只能有一个返回值
• 存储过程一般独立的来执行；而函数可以作为其他SQL语句的组成部分来实现

触发器

触发器概念：
触发器，需要有出发条件，当条件满足后做相应的操作。

create table board1(id int(11) primary key auto_increment,name varchar(50),articleCount int(11));
create table article1(id int(11) primary key auto_increment,title varchar(50) ,bid int(11) references board1(id));

delimiter |    # 将分隔符;改为|
create trigger interArticle_Trigger 
after insert on article1 
for each row begin
    update boardi set articleCount=articleCount+1 where id= NEW.bid;
end;|    #结束符
delimiter ;    # 将分隔符改为|

insert into board1 value (null,'test',0);
insert into article1 value (null,'test',1); # 插入该该句后会更新上局中的articleCount

视图

相当于一个虚表，是将SELECT查询语句结果作为一个表来用

CREATE VIEW virtualTb
AS
SELECT name,age,class FROM userTb WHERE id<100;

事务

索引

事务和索引见同归类中另一篇笔记 《MySQL常用关键字》 有更为详细关键字的介绍。

数据引擎之间的不同

当项目不需要事务和外键以及不用太大的并发量时，但需要存储更多数据或数据压缩时，建议使用MyISAM引擎。其他情况下一般使用InnoDB引擎。 下图是从慕课网imooc中截取。