MySQL基础之数据类型与表的管理

MySQL数据库简介及安装

• mysql.com 官网下载
• 数据库系统（Database System DBS)包括
  1、数据库（Database）
  2、数据库管理系统（Database Management System DBMS）
  3、应用开发工具
  4、管理员及用户

MySQL登陆与退出常用命令

• mysql -uroot -p 或者 mysql -uroot -p直接跟root用户的密码 命令行登陆
• exit 或者 quit 退出
• mysql -V 查看版本信息
• mysql -uroot -p -D db_name 登陆的同时打开指定的数据库
• SELECT USER() 得到登陆用户
• SELECT VERSION() 得到版本信息
• SELECT NOW() 得到当前的日期时间
• SELECT DATABASE() 得到当前打开的数据库

创建和管理数据库常用操作

• CREATE (DATABASE|SCHEMA) db_name 创建数据库
• CREATE DATABASE IF NOT EXISTS test1; 如何不存在test1数据库则创建
• CREATE DATABASE IF NOT EXISTS test1 DEFAULT CHARACTER SET 'UTF8'; 创建时设置编码方式
• SHOW WARNINGS; 查看上一条错误信息
• SHOW DATABASES 查询所有的数据库
• SHOW CREATE DATABASE db_name 查看指定数据库详细信息
• ALTER DATABASE db_name CHARACTER SET charset 更改数据库的编码方式
• USE db_name 打开指定数据库
• SELECT DATABASE() 查看当前打开的数据库
• DROP DATABASE db_name 删除数据库
• # 或者-- 进行注释

数据表及数据类型简介

• 创建表 CREATE TABLE tbl_name (
  字段名称 字段类型 [完整性约束条件],
  字段名称 字段类型 [完整性约束条件],
  ...
  )ENGINE=存储引擎 CHARSET=编码方式;

• MySQL数据类型
  1、数值型：包括整数型

  
  图片.png
  

  浮点数、定点数

  
  图片.png

2、字符串类型

图片.png

• char存储数据的时候，后面会用空格填充到指定长度，而在检索的时候会去掉后面的空格，varchar存储数据的时候不进行填充，尾部的空格会留下。

• text相关列不能有默认值,检索的时候不存在大小写转换（区分大小写）
  3、日期时间类型

  
  图片.png

• 创建表

--创建数据库
 CREATE DATABASE IF NOT EXISTS alan CHARACTER SET 'UTF8';

-- 打开alan数据库

USE DATABASE alan;

-- 用户表 user
CREATE TABLE IF NOT EXISTS alan_user(
    id INT,
    username VARCHAR(20),
    password CHAR(32),
    email VARCHAR(50),
    age TINYINT,
    card CHAR(18),
    tel CHAR(11),
    salary FLOAT(8,2),
    married TINYINT(1),
    addr VARCHAR(100),
    sex ENUM('男','女','保密')
)ENGINE=INNODB CHARSET=UTF8;

• 查看当前数据库下已有数据表 SHOW TABLES
  SHOW [EXTENDED] [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
• 查看数据表详细信息 SHOW CREATE TABLE tbl_name
• 查看表结构 DESC tbl_name
• 删除表 DROP TABLE tbl_name

完整性约束条件

• unsigned 无符号，没有负数，从0开始
• zerofill 零填充，长度不够前补0填充至指定长度
• NOT NULL 非空约束，插入时这个字段一定要给值
• DEFAULT 默认值，如果插入时没有赋值时，使用默认值
• PRIMARY KEY 主键，标识记录的唯一性，值不能重复，一个表只能有一个主键，设置为主键就默认不允许为空。
• UNIQUE KEY 唯一性，一个表中可以有多个字段是唯一索引，同样值不能重复，NULL值除外。
• AUTO_INCREMENT 自动增长，只能用于数值列，配合索引使用
• FOREIGN KEY 外键约束
• 字符串测试

CREATE TABLE test_str(
  a CHAR(5),
  b VARCHAR(5)
);
-- char存储数据的时候，后面会用空格填充到指定长度，而在检索的时候会去掉后面的空格，varchar存储数据的时候不进行填充，尾部的空格会留下。
INSERT INTO test_str(a,b) values('','');
INSERT INTO test_str(a,b) values('a','a');
INSERT INTO test_str(a,b) values('ab','ab');
INSERT INTO test_str(a,b) values('abc','abc');
INSERT INTO test_str(a,b) values('abcd','abcd');
INSERT INTO test_str(a,b) values('abcde','abcde');
INSERT INTO test_str(a,b) values('abcdef','abcdef');
INSERT INTO test_str(a,b) values(' 123 ',' 123 ');
SELECT CONCAT('*',a,'*'),CONCAT('*',b,'*') FROM test_str;

-- 测试TEXT不能有默认值
CREATE TABLE test_str1(
  content TEXT DEFAULT 'THIS IS A TEST'
)

• 测试枚举类型 只能选择1个，最多可以设置65535个

--  测试枚举类型，会自动过滤掉空格
CREATE TABLE test_enum1(
  sex ENUM('男','女','保密')
);
INSERT INTO test_enum1(sex) values('男');
INSERT INTO test_enum1(sex) values('男1');
-- 可以插入NULL
INSERT INTO test_enum1(sex) values(null);
-- 编号从1开始 3对应保密 5不能插入
INSERT INTO test_enum1(sex) values(1);
INSERT INTO test_enum1(sex) values(3);
INSERT INTO test_enum1(sex) values(5);

• 集合类型 可以选择多个 最多可以设置64个

-- 测试set 不区分大小写
CREATE TABLE test_set1(
  a SET('A','B','C','D','E','F')
);

INSERT INTO test_set1(a) VALUES('A');
INSERT INTO test_set1(a) VALUES('A,B,C');
INSERT INTO test_set1(a) VALUES('C,F,A');
INSERT INTO test_set1(a) VALUES(2);

• TIME类型

-- HH:MM:SS [D HH:MM:SS] D表示天数 0～34 
-- 测试TIME类型
CREATE TABLE test_time(
  a TIME
);

INSERT INTO test_time(a) VALUES('12:23:34');
INSERT INTO test_time(a) VALUES('2 12:23:34');
INSERT INTO test_time(a) VALUES('22:22');
-- HHMMSS
INSERT INTO test_time(a) VALUES('121234');
INSERT INTO test_time(a) VALUES(0);

-- 插入当前时间
INSERT INTO test_time(a) VALUES(NOW());
INSERT INTO test_time(a) VALUES(CURRENT_TIME);

• DATE类型

-- 测试DATE类型 YYYY-MM-DD YYYYMMDD
CREATE TABLE test_date(
  a DATE
);

INSERT INTO test_date(a) VALUES('2017-03-04');
INSERT INTO test_date(a) VALUES('40071212');
INSERT INTO test_date(a) VALUES('4007@12@12');
INSERT INTO test_date(a) VALUES('4007#12#12');
INSERT INTO test_date(a) VALUES('4007.12.12');
-- 70~99 转换成1970～1999  00～69 转换成2000～2069
INSERT INTO test_date(a) VALUES('780912');
INSERT INTO test_date(a) VALUES('181112');
INSERT INTO test_date(a) VALUES(NOW());
INSERT INTO test_date(a) VALUES(CURRENT_DATE);

• DATETIME类型

-- 测试DATETIME

CREATE TABLE test_datetime(
  a DATETIME
);

INSERT INTO test_datetime(a) VALUES('1988-02-12 13:33:58');
INSERT INTO test_datetime(a) VALUES('721230121326');
INSERT INTO test_datetime(a) VALUES(NOW());

• TIMESTAMP类型

-- 测试TIMESTAMP
CREATE TABLE test_timestamp(
  a TIMESTAMP
);

INSERT INTO test_timestamp(a) VALUES('1988-02-12 13:33:58');
-- 获取当前时间
INSERT INTO test_timestamp(a) VALUES(CURRENT_TIMESTAMP);

• PRIMARY KEY

--  测试主键
CREATE TABLE test_primary_key(
  id INT unsigned PRIMARY KEY,
  username VARCHAR(20)
);

INSERT INTO test_primary_key(id,username) VALUES (1,'king');
INSERT INTO test_primary_key(id,username) VALUES (1,'alan');

--  测试主键 可以省略PRIMARY
CREATE TABLE test_key(
  id INT unsigned KEY,
  username VARCHAR(20)
);
-- 也可以主键最后写
CREATE TABLE test_primary_key1(
  id INT unsigned ,
  username VARCHAR(20),
  PRIMARY KEY(id)
);

-- 复合主键
CREATE TABLE test_primary_key2(
  id INT UNSIGNED,
  courseId INT UNSIGNED,
  username VARCHAR(20),
  email VARCHAR(50),
  PRIMARY KEY(id,courseId)
);

-- 测试AUTO_INCREMENT  需要和主键一起使用
CREATE TABLE test_auto_increment1(
  id INT UNSIGNED KEY AUTO_INCREMENT,
  username VARCHAR(20)
);


INSERT INTO test_auto_increment1(username) VALUES('A');
INSERT INTO test_auto_increment1(username) VALUES('B');
INSERT INTO test_auto_increment1(username) VALUES('C');
INSERT INTO test_auto_increment1(username) VALUES('D');

INSERT INTO test_auto_increment1(id,username) VALUES(NULL,'E');
INSERT INTO test_auto_increment1(id,username) VALUES(DEFAULT,'F');
INSERT INTO test_auto_increment1(id,username) VALUES(15,'G');

• 带约束条件的用户表创建

-- 用户表  
CREATE TABLE IF NOT EXISTS `alan_user1`(
    `id` INT UNSIGNED KEY AUTO_INCREMENT COMMENT '用户编号',
    `username` VARCHAR(20) NOT NULL COMMENT '用户名', 
    `password` CHAR(32) NOT NULL UNIQUE  COMMENT '用户密码',
    `email` VARCHAR(50) NOT NULL UNIQUE COMMENT '用户邮箱',
    `age` TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄', 
    `card` CHAR(18) NOT NULL UNIQUE COMMENT '身份证',
    `tel` CHAR(11) NOT NULL UNIQUE COMMENT '电话号',
    `salary` FLOAT(8,2) UNSIGNED COMMENT '薪水',
    `married` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '代表未结婚',
    `addr` VARCHAR(100) NOT NULL DEFAULT '北京' COMMENT '地址',
    `sex` ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别'
)ENGINE=INNODB CHARSET=UTF8;

数据表操作

在表中添加或删除字段

• 添加字段 ALTER TABLE tbl_name ADD 字段名称 字段属性 [完整性约束条件] [FIRST|AFTER 字段名称]
• 删除字段 ALTER TABLE tbl_name DROP 字段名称

-- 测试添加和删除字段
CREATE TABLE IF NOT EXISTS user5(
  id INT UNSIGNED AUTO_INCREMENT KEY
);

-- 添加用户名字段
ALTER TABLE user5 ADD username VARCHAR(20) NOT NULL AFTER id;
-- 删除字段
ALTER TABLE user5 DROP username;
-- 添加age、addr字段，删除email字段
ALTER TABLE user5
ADD age TINYINT UNSIGNED NOT NULL DEFAULT 18,
ADD addr VARCHAR(100) NOT NULL DEFAULT '北京',
DROP username;

添加和删除默认值

• 添加默认值 ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值;
• 删除默认值 ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT ;

MODIFY和CHANGE关键字使用

• 修改字段类型、字段属性 ALTER TABLE tbl_name MODIFY 字段名称 字段类型 [字段属性] [FIRST|AFTER 字段名称]
• 修改字段名称、字段类型、字段属性 ALTER TABLE tb1_name CHANGE 原字段名称 新字段名称 字段类型 字段属性 [FIRST|AFTER 字段名称]

-- 测试修改字段类型、字段属性、字段名称
CREATE TABLE user6(
  id INT UNSIGNED AUTO_INCREMENT KEY,
  username VARCHAR(5) NOT NULL UNIQUE,
  password CHAR(32) NOT NULL,
  email VARCHAR(10) NOT NULL
);

-- 将用户名字段类型改为20
ALTER TABLE user6 
MODIFY username VARCHAR(20) NOT NULL ;
-- 将username 名称改为user
ALTER TABLE user6
CHANGE username user VARCHAR(30) NOT NULL;

主键和唯一索引操作

• 添加主键 ALTER TABLE tbl_name ADD PRIMARY KEY(字段名称)
• 删除主键 ALTER TABLE tbl_name DROP PRIMARY KEY

-- 测试添加和删除主键
CREATE TABLE user4(
  id INT UNSIGNED,
  username VARCHAR(20) NOT NULL
);

-- 添加主键
ALTER TABLE user4
ADD PRIMARY KEY(id);

-- 删除主键
ALTER TABLE user4
DROP PRIMARY KEY;

--如果有AUTO_INCREMENT应该先删除 再删除主键
CREATE TABLE user7(
  id INT UNSIGNED AUTO_INCREMENT KEY,
  username VARCHAR(20) NOT NULL
);
-- 第一步AUTO_INCREMENT去掉
ALTER TABLE user7
MODIFY id INT UNSIGNED;
-- 第二部 删除主键
ALTER TABLE user7
DROP PRIMARY KEY;

• 添加唯一索引 ALTER TABLE tbl_name ADD UNIQUE KEY|INDEX index_name
• 删除唯一索引 ALTER TABLE tbl_name DROP INDEX index_name

-- 添加和删除唯一索引

CREATE TABLE user6(
  id INT UNSIGNED AUTO_INCREMENT KEY,
  username VARCHAR(20) NOT NULL UNIQUE,
  password CHAR(32) NOT NULL,
  email VARCHAR(50) NOT NULL UNIQUE
);

-- 查看表结构，索引没有命名，默认设置名字与字段名称一致
SHOW CREATE TABLE USER6;

 USER6 | CREATE TABLE `USER6` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` char(32) NOT NULL,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

-- 删除username索引
ALTER TABLE user6
DROP INDEX username;

-- 添加username索引(没加名字)
ALTER TABLE user6
ADD UNIQUE INDEX(username);
-- 添加username索引(加名字user_index)
ALTER TABLE user6
ADD UNIQUE INDEX user_index (username);

重命名表

• 方式1 ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
• 方式2 RENAME TABLE tbl_name TO new_tbl_name

MySQL常用存储引擎

MyISAM存储引擎

• 默认MyISAM的表会在磁盘产生三个文件
  1、.frm表结构文件
  2、.MYD 数据文件
  3、.MYI 索引文件
• 可以在建表的时候指定数据文件和存储文件的位置，只有MyISAM表支持
  1、DATA DIRECTORY [=] 数据文件保存的绝对路径
  2、INDEX DIRECTORY [=] 索引文件保存的绝对路径
• MyISAM单表最大支持的数据量2的64次方条记录
• 每个表最多可以建立64个索引
• 如果是复合索引，每个复合索引最多包含16个列，索引值最大长度是1000B
• MyISAM引擎的存储格式
  1、定长（FIXED静态）是指字段中不包含VARCHAR/TEXT/BLOB (速度最快的)
  2、动态（DYNAMIC）
  3、压缩（COMPRESSED）

-- 测试MyISAM存储引擎
CREATE TABLE test_myisam(
  a INT UNSIGNED,
  b VARCHAR(20),
  c CHAR(32)
)ENGINE=MyISAM;

CREATE TABLE myisam_1(
  a char(30),
  id int
)ENGINE=MyISAM;

-- 查看表状态
SHOW TABLE STATUS LIKE 'myisam_1' \G;

-- 强制设置为静态表
CREATE TABLE myisam_2(
  a VARCHAR(20)
)ENGINE=MyISAM ROW_FORMAT=FIXED;

InnoDB存储引擎

• 设计遵循ACID模型，支持事物，具有从服务崩溃中恢复的能力，能够最大限度的保护用户数据
  1、原子性（Atomiocity）
  2、一致性（Consistency）
  3、隔离性(Isolation)
  4、持久性（Durability）
• 支持行级锁（提升用户多并发时，读写性能）
• 支持外键，保证数据的一致性和完整性
• InnoDB拥有自己独立的缓冲池，常用的数据和索引都在缓存中
• 对INSERT、UPDATE、DELETE操作，InnoDB会使用一种change buffering 的机制来自动优化，还可以提供一执行的读，并且还能缓存变更的数据，减少磁盘IO,提高性能。
• 创建InnoDB表之后会产生两个文件
  1、 .frm表结构文件
  2、.ibd,数据和索引存储在表空间中
• 所有的表都需要创建主键，最好配合AUTO_INCREMENT使用，也可以放在经常查询的列作为主键