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

基于MySQL8.0

mysql登陆：

mysql -u${username} -p

mysql退出

exit

获取版本信息

mysql -V

登陆的同时打开数据库

mysql -u${username} -p -D${db_sename}

常用功能

mysql中语句以;或\g结束
可以使用help;、\h或?${keyword}来查看帮助手册
可以使用\c来取消当前指令

常用SQL语句

-- 获取当前用户名
select user();
-- 获取当前版本
select version();
-- 获取当前时间
select now();
-- 获取当前打开的数据库
select database();
-- 显示所有数据库
show databases;
-- 查看上一步的警告
show warnings;
-- 查看指定数据库的详细信息
show create database db_name;
-- 打开指定数据库
use db_name;
-- 查看当前数据库中的表
show tables;
-- 查看指定数据库中的表
show [full] tables from db_name;
-- 查看指定数据表的详细信息
show create table tb_name;
-- 查看表结构
desc tb_name;

SQL语法规范

常用MYSQL的关键字推荐大写(实际不区分大小写),库名、表名、字段名用小写
SQL语言支持拆分书写，拆分的时候不能把单词拆开
数据库的库名、表名、字段名不要使用MYSQL保留字，使用的话用反引号括起来`name`

数据库的创建与删除

-- 创建数据库
create database db_name; 
-- 如果数据库不存在则创建，否则什么都不做
create database if not exists db_name;
-- 在创建数据库的同时指定编码方式
create database db_name character set utf8;
-- 修改指定数据库的编码方式
alter database db_name character set utf8;
-- 删除数据库
drop database db_name;
-- 如果数据库存在则删除，否则什么都不做
drop database if exists db_name;

数据表的相关信息

数据表是数据库重要的组成部分，数据保存在数据表中
数据表由行（row）和列（column）组成
数据表名唯一，不含特殊字符，意义明确
插入数据超过最大范围会报错
整型可在后面加括号其中填写数字表示显示的长度，可以配合zerofill使用
zoerfill会自动设置数据为unsigned
unsigned输入负数会报错
浮点数会根据精度四舍五入自动截断，decimal截断会警告
decimal默认四舍五入截断到整数
char的检索速度比varchar快
char储存数据时候会自动填充空格达到指定长度
text列不能有默认值
字符型输入超出长度会报错
enum、set类型实际保存的是变量所代表的数值，数值从1开始
enum、set会自动过滤掉空格
enum、set不能设置成不存在的值
set可以对一个变量用逗号隔开同时设置多个值，并自动排序，enum只能设置成单个值
time设置值可以用'HH:MM:SS'也可以用'D HH:MM:SS'还可以用'HHMMSS'
time可以用'0'或0表示00:00:00
date可以用'YYYY-MM-DD'或'YYYYMMDD'其中-可以用其他符号代替
date可以用两位数年份70~99转换成1970~1999，00~69转换成2000~2069
datetime是date与time的结合，用法相似
timestamp与datetime类似，占用空间更小，时间的范围也更小，当为null或未赋值会自动取当前时间
time、date、datetime可以用now()取当前时间
创建表的最后可以用primary key(column1,column2,···)的形式定义复合主键，复合主键只有当值都相同时才相同
auto increment值为已有最大值+1（默认初始值为1），每次增长1，只能配合主键使用
一个字段有默认值default可以显式的定义为null
unique可以设置为null，不算重复
可以在字段后用comment '注释'的方式给字段添加注释
用alter选中表后可以一次进行插入和删除等多种不同的操作
对not null字段设置属性的时候约束条件要加not null否则默认为null
当主键有auto_increment应先去掉，才能够删除主键
添加索引未设置索引名，默认设为字段名
在见表的时候可以在后面加上rows_format=fixed设置表的储存格式为静态

数据表的操作

-- 创建数据表
create table [if not exists] tb_name[(
    col_name1 col_defintion1[ integrity]，
    col_name2 col_defintion2[ integrity]，
    ···
)][ engine=engine_name][ charset=encoded_mode];
-- 删除数据表
drop table if exists tb_name;
-- 向表中插入数据
insert tb_name (column1,column2,···) values (value1,value2,···);
-- 查询表中的所有数据
select * from tb_name;
-- 添加字段
alter table tb_name add col_name1 col_defintion1[ integrity1][ fisrt | after col_name];
-- 删除字段
alter table tb_name drop col_name;
-- 添加默认值
alter table tb_name alter col_name set default default_value;
-- 删除默认值
alter table tb_name alter col_name drop default;
-- 修改字段类型、属性
alter table tb_name modify col_name col_defintion[ integrity][ first | after other_col_name];
-- 修改字段名称、类型和属性
alter table tb_name change old_col_name new_col_name col_defintion[ integrity][ first | after other_col_name]; 
-- 添加主键
alter table tb_name add primary key(col_name);
-- 删除主键
alter table tb_name drop primary key;
-- 添加唯一约束
alter table tb_name add unique index_name (col_name);
-- 删除唯一约束
alter table tb_name drop key index_name;
-- 重命名数据表
rename table old_tb_name to new_tb_name;
-- 查看数据的储存格式
show table status like 'db_name';

常用数据类型

数据类型	存储	字节
tinyint	有符号：-128到127（-2⁷到2⁷-1）无符号：0到255（0到2⁸-1）	1
smallint	有符号：-32768到32767（-2¹⁵到2¹⁵-1）无符号：0到65535（0到2¹⁶-1）	2
mediumint	有符号：-8388608到8388607（-2²³到2²³-1）无符号：0到16777215（0到2²⁴-1）	3
int	有符号：-2147683648到2147683647（-2³¹到2³¹-1）无符号：0到4294967295（0到2³²-1）	4
bigint	有符号：-9223372036854775808到9223372036854775807（-2⁶³到2⁶³-1）无符号：0到18446744073709551615（0到2⁶⁴-1）	8
bool \| boolean	等价于tinyint{1}，0为false，其余为true	1
float(M,D)	负数：-3.40×10⁺³⁸到-1.17×10^-38 非负数：0和1.175×10^-38到3.40×10⁺³⁸ M是数字总位数，D是小数点后面的位数，可以省略精度约为小数点后7位	4
Double(M,D)	负数：-1.79×10⁺³⁰⁸到-2.22×10^-308 非负数：0和2.22×10^-308到1.79×10⁺³⁰⁸	8
decimal(M,D)	和double一样，内部以字符串形式存储	M+2
char(M)	M个字节，0≤M≤255
varchar(M)	L+1个字节，其中l≤M且0≤M≤65535
tinytext	L+1个字节,其中L≤2⁸
text	L+2个字节,其中L≤2¹⁶
mediumtext	L+3个字节,其中L≤2²⁴
longtext	L+4个字节,其中L≤2³²
enum(‘value1’,‘value2’,…)	1或2个字节，取决于枚举的值（最多65535个值）
set(‘value1’,‘value2’,…)	1、2、3、4或8个字节，取决于set成员的类型，（最多64个成员）
time	-838:59:59~838:59:59	3
date	1000-01-01~9999-12-31	3
datetime	1000-01-01 00:00:00~9999-12-31 23:59:59	8
timestamp	1970-01-01 00:00:01~2038-01-19 03:14:07	4
year	1901~2155	1

完整性约束条件

条件	描述
unsigned	无符号，没有负数，从零开始
zerofill	零填充，当显示长度不够的时候，前补0至指定长度
not null	非空约束，在插入值的时候该字段必须赋值
default	默认值，如果插入记录时没有给字段赋值，则使用默认值
primary key	主键，标识记录的唯一性，值不能重复，一个表只能有一个主键，自动禁止为空
unique[ key]	唯一性，值不能重复，null除外，一个表中可以有多个字段时唯一索引
auto_increment	自动增长，只能用于数值列，配合索引使用
foreign key	外键约束

存储引擎

名称详细信息

MyISAM 产生三个文件：.frm（表结构文件），.MYD（数据文件），.MYI（索引文件）
可以在建表的时候指定数据文件和索引文件的存储位置：data | index directory[=] 数据保存的绝对路径
单表最大支持2⁶⁴条记录、64个索引，复合索引最多包含16列，最大1000B
三种储存格式：定长（fixed），动态（dynamic），压缩（compressed）

InnoDB 设计遵循ACID模型（原子性、一致性、隔离性和持久性）
支持事物，有能从崩溃中回复的能力，能够最大限度保护数据
支持行级锁，可以提升多用户的读写性能
拥有自己独立的缓冲池，常用的数据和索引都在缓存中
对于insert、update和delete操作，会使用change buffering的机制来自动优化，还可以提供一致性的读，缓存变更数据，减少I/O
产生两个文件：.frm，.idb（数据和索引）
建表时应创建主键，最好配合auto_increment,也可放在常查询的列

名称	详细信息
MyISAM	产生三个文件：`.frm`（表结构文件），`.MYD`（数据文件），`.MYI`（索引文件）可以在建表的时候指定数据文件和索引文件的存储位置：`data \| index directory[=] 数据保存的绝对路径` 单表最大支持2⁶⁴条记录、64个索引，复合索引最多包含16列，最大1000B 三种储存格式：定长（`fixed`），动态（`dynamic`），压缩（`compressed`）
InnoDB	设计遵循ACID模型（原子性、一致性、隔离性和持久性）支持事物，有能从崩溃中回复的能力，能够最大限度保护数据支持行级锁，可以提升多用户的读写性能拥有自己独立的缓冲池，常用的数据和索引都在缓存中对于`insert`、`update`和`delete`操作，会使用`change buffering`的机制来自动优化，还可以提供一致性的读，缓存变更数据，减少I/O 产生两个文件：`.frm`，`.idb`（数据和索引）建表时应创建主键，最好配合`auto_increment`,也可放在常查询的列

创建数据库示例

-- 创建数据库test_db
create database if not exists test_db character set utf8;
-- 打开test_db
use test_db;
-- 新建用户信息表user示例
create table if not exists `user`(
    `id` int unsigned auto_increment primary key comment '用户编号',
    `username` varchar(20) not null unique comment '用户名',
    `password` varchar(32) not null 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) not null default 0 comment '薪水',
    `married` tinyint(1) not null default 0 comment '0代表未婚，1代表已婚',
    `addr` varchar(100) not null default '北京' comment '地址',
    `sex` enum('男','女','保密') not null default '保密' comment '性别'
)engine=innodb charset=utf8;
-- 向表中插入数据
insert user (
    id,
    username,
    password,
    email,
    age,
    card,
    tel,
    salary,
    married,
    addr,
    sex
)
values (
    1,
    'Bob',
    'passwd123',
    'test@sql.com',
    23,
    '123456199701017891',
    '13612345678',
    10000.00,
    0,
    'Hefei-China',
    '男'
);