Deng
Deng
MySQL基础之数据类型 | odjBlog
    欢迎来到odjBlog的博客!
> MySql > MySQL基础之数据类型

MySQL基础之数据类型

MySql odjbin 4年前 (2021-09-09) 129次浏览 1个评论

数据库简介及安装

  • MySQL 属于关系型数据库

  • MySQL 是按照数据结构来组织、存储和管理数据的仓库

    • 登录：mysql -uroot -p
    • 退出：exit / quit / \q
    • 清屏：cls
    • 得到版本号：mysql -V / mysql --version
    • 登录的同时打开指定数据库：mysql -uroot -p -D db_name
    • 登录信息中需要掌握的：命令行结束符默认使用；或者\g 来结束
      可以通过 help 或者\h 或者?加上相关关键字来查看手册
      help creat database
    • \c 可以取消当前命令的执行

      常用 SQL 语句

  • 常用 MySQL 的关键字我们需要大写，库名、表名、字段名称等使用小写

  • SQL 语句支持折行操作,拆分的时候不能把完整单词拆开

  • 数据库名称、表名称、字段名称不要使用 MySQL 的保留字，如果必须要使用，需要用反引号``将其括起来

  • 语句

    • SELECT USER() 得到登陆的用户
    • SELECT VERSION() 得到 MySQL 的版本信息
    • SELECT NOW() 得到当前的日期时间
    • SELECT DATABASE() 得到当前打开的数据库
    • SHOW WARNINGS; 查看上一步操作产生的警告信息
    • mysql 中的注释：#注释内容 --注释内容

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

  • 创建数据库：CREATE{DATABASE|SCHEMA] db_name;

    • 检测数据库名称是否存在，不存在则创建：CREATE DATABASE [IF NOT EXISTS] db_name;
    • 在创建数据库的同时指定编码方式：CREATE DATABASE [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET[=] charset;

  • 注意：

    • 数据库名称最好有意义
    • 名称不要包含特殊字符或者是 MySQL 关键字

  • 查询当前服务器下全部数据库： SHOW DATABASES|SCHEMAS;

  • 查看指定数据库的详细信息：SHOW CREATE DATABASE db_name;

  • 修改指定数据库的编码方式：ALTER DATABASE db_name [DEFAULT] CHARACTER SET [=] charset;

  • 打开指定数据库：USE db_name;

  • 得到当前打开的数据库：SELECT DATABASE()|SCHEMA();

  • 删除指定的数据库:DROP DATABASE db_name;查看：SHOW DATABASES;

  • 如果数据库存在则删除：DROP DATABASE [IF EXISTS] db_name;

    数据表及数据类型简介

  • 是数据库最重要的组成部分之一，数据是保存在数据表中

  • 数据表由行(row)和列(column)来组成

  • 每个数据表中至少有一列，行可以有零行一行或者多行组成

  • 表名要求唯一，不要特殊字符，最好含义明确

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

MySQL 中常用的数据类型

  • MYSQL 中的数据类型

    • 数值型
      整数型

      浮点型、定点数 DECIMAL

    • 字符串类型：

    • 日期时间类型

  • CHAR 类型存储的最大字节数是（
    255）

    简单慕课用户数据表创建

  • 查看当前数据库下已有数据表：SHOW TABLES;
  • SHOW [FULL] TABLES [{FROM | lN} db_name] [LIKE 'pattern' | WHERE expr]
  • 查看指定数据表的详细信息：SHOW CREATE TABLE tbl_name;
  • 查看表结构：DESC tbl_name;
  • DESCRIBE tbl_name;
  • SHOW COLUMNS FROM tbl_name;
  • 删除 imooc_user 表
  • DROP TABLE [IF EXISTS] tbl_name;

    完整性约束条件介绍

  • 创建数据表：CREATE TABLE [IF NOT EXISTS] tbl..name(
    字段名称 字段类型 [完整性约束条件],
    字段类型 [完整性约束条件],
    ...
    )ENGINE=存储引擎 CHARSET=编码方式;
    //////////////////////////////////////////////////
  • unsigned 无符号，没有负数，从 0 开始
  • zerofill 零填充，当数据的显示长度不够的时候可以使用前补 0 的效果填充至指定长度
  • NOT NULL 非空约束，也就是插入值的时候这个字段必须要给值，值不能为空
  • DEFAULT 默认值，如果插入记录的时候没有给字段赋值，则使用默认值
  • PRIMARY KEY 主键，标识记录的唯一性，值不能重复，一个表只能有一个主键，自动禁止为空
  • UNIQUE KEY 唯一性，一个表中可以有多个字段是唯一索引，同样的值不能重复，但是 NULL 值除外
  • AUTO_INCREMENT 自动增长，只能用于数值列，而且配合索引所有，默认起始值从 1 开始，每次增长 1
  • FOREIGN KEY 外键约束
  • MySQL 中自动编号的关键字是 AUTO_INCREMENT
  • MySQL 中表示唯一约束的关键字是 UNIQUE KEY
-- 用户表 imooc_user
CREATE TABLE IF NOT EXISTS imooc_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;
-- INSERT [INTO] tbl_name(id,username,...) VALUES(1,'KING',...);
-- 向 imooc_user 表插入一条记录
INSERT imooc_user(id,username,password,email,age,card,tel,salary,married,addr,sex)
VALUES(1,'king','king','382771946@qq.com',24,'230112197809871234','18635579617',88888.68,0,'北京','男');
-- 查询表中所有记录 SELECT * FROM tbl_name;
SELECT * FROM imooc_user;
INSERT imooc_user(id,username,password,email,age,card,tel,salary,married,addr,sex)
VALUES(-5,'king','123456','382771946@qq.com',19,'230112197809871234','18635579617',2345.68,1,'北京','女');

测试整型

  • TINYINT 无符号型存储范围是？0 到 255
-- 测试整型
CREATE TABLE test_int(
    a tinyint,
    b smallint,
    c mediumint,
    d int,
    e bigint
);
INSERT test_int(a) VALUES(-128);
-- 如果超出数据的范围，会产生截断现象
INSERT test_int(a) VALUES(-129);
INSERT test_int(a) VALUES(1270);
-- 测试无符号
CREATE TABLE test_unsigned(
a tinyint,
b tinyint unsigned
);
INSERT test_unsigned(a,b) VALUES(-12,-12);
INSERT test_unsigned(a,b) VALUES(0,0);
INSERT test_unsigned(a,b) VALUES(0,256);
-- 测试零填充 ZEROFILL
CREATE TABLE test_int1(
    a tinyint ZEROFILL,
    b smallint ZEROFILL,
    c mediumint ZEROFILL,
    d int ZEROFILL,
    e bigint ZEROFILL
);
INSERT test_int1(a,b,c,d,e) VALUES(1,1,1,1,1);
CREATE TABLE test_int2(
    a tinyint(2),
    b smallint(2)
);
INSERT test_int2(a,b) VALUES(123,45678);

测试浮点类型

-- 测试浮点型
CREATE TABLE test_float(
a FLOAT(6,2),
b DOUBLE(6,2),
c DECIMAL(6,2)
);
INSERT test_float(a,b,c) VALUES(4.143,4.146,4.149);
CREATE TABLE test_float1(
a FLOAT,
b DOUBLE,
c DECIMAL
);
INSERT test_float1(a,b,c) VALUES(4.143,4.146,4.649);

测试字符串类型、ENUM 和 SET 类型

  • 字符串类型：CHAR 效率高于 VARCHAR,CHAR 相当于拿空间换时间，VARCHAR 拿时间换空间
  • CHAR 默认存储数据的时候，后面会用空格填充到指定长度；而在检索的时候会去掉后面空格；VARCHAR 在保存的时候不进行填充，尾部的空格会留下
  • TEXT 列不能有默认值，检索的时候不存在大小写的转换
  • TEXT 类型最大存储字节是？65535
  • ENUM 是枚举类型，会自动过滤掉空格
  • ENUM 类型和 SET 类型的区别
    ENUM 可以用来定义性别这种二选一的情况
    ENUM 类型只能从列表中的值选一个，SET 类型可以从列出的值中选多个
SHOW DATABASES;
use imooc;
-- 测试 char 和 varchar
CREATE TABLE test_str(
    a CHAR(5),
    b VARCHAR(5)
);
INSERT test_str(a,b) VALUES('','');
INSERT test_str(a,b) VALUES('a','a');
INSERT test_str(a,b) VALUES('ab','ab');
INSERT test_str(a,b) VALUES('abc','abc');
INSERT test_str(a,b) VALUES('abcd','abcd');
INSERT test_str(a,b) VALUES('abcde','abcde');
INSERT test_str(a,b) VALUES('abcdef','abcdef');
INSERT test_str(a,b) VALUES(' 123 ',' 123 ');
select * from test_str;
show warnings;
SELECT CONCAT('*',a,'*'),CONCAT('*',b,'*') FROM test_str;
-- 测试 TEXT 不能有默认值
CREATE TABLE test_str1(
 content TEXT DEFAULT 'THIS IS A TEST'
);
-- 测试 ENUM
CREATE TABLE test_enum(
sex ENUM('男     ','女 ','保密 ')
);
DESC test_enum;
INSERT test_enum(sex) VALUES('男');
INSERT test_enum(sex) VALUES('男 1');
INSERT test_enum(sex) VALUES(NULL);
INSERT test_enum(sex) VALUES(1);
INSERT test_enum(sex) VALUES(3);
INSERT test_enum(sex) VALUES(5);
SELECT * FROM test_enum;
-- 测试 SET
CREATE TABLE test_set(
    a SET('A','B','C','D','E','F')
);
INSERT test_set(a) VALUES('A');
INSERT test_set(a) VALUES('C');
INSERT test_set(a) VALUES('C,D,E');
INSERT test_set(a) VALUES('C,F,A');
INSERT test_set(a) VALUES('C,F,A,H');
INSERT test_set(a) VALUES(2);

测试 TIME 类型

-- HH:MM:SS [D HH:MM:SS] D 表示天数 0~34
-- 测试 TIME 类型
CREATE TABLE test_time(
a TIME
);
INSERT test_time(a) VALUES('12:23:45');
INSERT test_time(a) VALUES('2 12:23:45');<!--2 天 48 小时-->
INSERT test_time(a) VALUES('22:22');<!--22:22:00-->
INSERT test_time(a) VALUES('22');<!--00:00:22-->
INSERT test_time(a) VALUES('2 22');<!--70:00:00-->
-- HHMMSS
INSERT test_time(a) VALUES('121212');
INSERT test_time(a) VALUES('0');
INSERT test_time(a) VALUES(0);
INSERT test_time(a) VALUES('787878');
INSERT test_time(a) VALUES(NOW());
INSERT test_time(a) VALUES(CURRENT_TIME);
-- 测试 DATE 类型 YYYY-MM-DD YYYYMMDD
CREATE TABLE test_date(
    a DATE
);
INSERT test_date(a) VALUES('2017-03-04');
INSERT test_date(a) VALUES('2017-2-13');
INSERT test_date(a) VALUES('4007-03-23');
INSERT test_date(a) VALUES('40071212');
INSERT test_date(a) VALUES('4007@12@12');
INSERT test_date(a) VALUES('4008#4#13');
INSERT test_date(a) VALUES('4009.8.14');
-- YY-MM-DD YYMMDD
-- 70~99 转换成 1970~1999  00~69 转换 2000~2069
INSERT test_date(a) VALUES('780902');
INSERT test_date(a) VALUES('650902');
INSERT test_date(a) VALUES('880902');
INSERT test_date(a) VALUES(NOW());
INSERT test_date(a) VALUES(CURRENT_DATE);
-- 测试 DATETIME
CREATE TABLE test_datetime(
a DATETIME
);
INSERT test_datetime(a) VALUES('1004-09-12 13:24:56');
INSERT test_datetime(a) VALUES('720305121212');<!--1972-03-05 12:12:12-->
INSERT test_datetime(a) VALUES(NOW());
INSERT test_datetime(a) VALUES(DATETIME);
-- 测试 TIMESTAMP
CREATE TABLE test_timestamp(
    a TIMESTAMP
);
INSERT test_timestamp(a) VALUES('1978-10-23 12:12:12');
-- 插入 CURRENT_TIMESTAMP
INSERT test_timestamp VALUES(CURRENT_TIMESTAMP);
-- 插入 NULL
INSERT test_timestamp VALUES(NULL);
-- 什么也不写 得到当前系统日期和时间
INSERT test_timestamp VALUES();
-- 测试 YEAR
CREATE TABLE test_year(
    a YEAR
);
INSERT test_year(a) VALUES(1901);
-- 00~69 2000~2069 70~99 1970~1999
-- 0 插入的结果是 0000
-- '0' 插入的结果是 2000

测试主键和自增

-- 测试主键
CREATE TABLE test_primary_key(
    id INT UNSIGNED PRIMARY KEY,
    username VARCHAR(20)
);
INSERT test_primary_key(id,username) VALUES(1,'king');
INSERT test_primary_key(id,username) VALUES(1,'king123');<!--主键唯一性 -->
INSERT test_primary_key(username) VALUES('QUEEN');
SELECT * FROM test_primary_key;
CREATE TABLE test_primary_key1(
    id INT UNSIGNED KEY,
    username VARCHAR(20)
);
CREATE TABLE test_primary_key2(
    id INT UNSIGNED,
    username VARCHAR(20),
    PRIMARY KEY(id)
);
-- CREATE TABLE test_primary_key3(
--     id INT UNSIGNED PRIMARY KEY,
--     courseId INT UNSIGNED PRIMARY KEY,
--     username VARCHAR(20),
--     email VARCHAR(50)
-- );
-- 复合主键
CREATE TABLE test_primary_key3(
    id INT UNSIGNED,
    courseId VARCHAR(20),
    username VARCHAR(20),
    email VARCHAR(50),
    PRIMARY KEY(id,courseId)
);
-- 1-a 复合主键
INSERT test_primary_key3(id,courseId,username,email)
VALUES(1,'a','king','382771946@qq.com');
INSERT test_primary_key3(id,courseId,username,email)
VALUES(1,'b','king','382771946@qq.com');
INSERT test_primary_key3(id,courseId,username,email)
VALUES(2,'a','king','382771946@qq.com');
INSERT test_primary_key3(id,courseId,username,email)
VALUES(1,'a','1king','1382771946@qq.com');
-- 测试 AUTO_INCREMENT
CREATE TABLE test_auto_increment(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20)
);
INSERT test_auto_increment(username) VALUES('A');-- 1A 2B 3C 4E 5F
INSERT test_auto_increment(username) VALUES('B');
INSERT test_auto_increment(username) VALUES('C');
INSERT test_auto_increment(id,username) VALUES(NULL,'E');
INSERT test_auto_increment(id,username) VALUES(DEFAULT,'F');
INSERT test_auto_increment(id,username) VALUES(15,'G');-- 15 G

测试其他约束条件

mysql -root -p
SHOW DATABASES;
use imooc;
-- 测试非空
CREATE TABLE test_not_null(
a varchar(20),
b varchar(20) not null
);
INSERT test_not_null(a,b) VALUES('','');
INSERT test_not_null(a,b) VALUES(NULL,NULL);
INSERT test_not_null(a,b) VALUES(NULL,'abc');
INSERT test_not_null(a) VALUES('TEST');--不能空 ，NULL
select * from test_not_null;
-- 测试默认值
CREATE TABLE test_default(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com'
);
INSERT test_default(username) VALUES('A');
INSERT test_default(username,age,email) VALUES('B',30,'imooc@qq.com');
INSERT test_default(username,age,email) VALUES('C',NULL,'imooc@qq.com');
INSERT test_default(username,age,email) VALUES('D',NULL,NULL);
INSERT test_default(username,age,email) VALUES('D',NULL,DEFAULT);-- DEFAULT 默认值
-- 枚举
CREATE TABLE test_default1(
id INT UNSIGNED AUTO_INCREMENT KEY,
sex ENUM('a','b','c') NOT NULL DEFAULT 'a'
);
INSERT test_default1(sex) VALUES(DEFAULT);
select * from test_default1;
INSERT test_default1(id) VALUES(2);
-- 测试 UNIQUE KEY
CREATE TABLE test_unique(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
email VARCHAR(50) UNIQUE,
card CHAR(18) UNIQUE
);
INSERT test_unique(username,email,card) VALUES('A','A@QQ.COM','1');
INSERT test_unique(username,email,card) VALUES('A','1A@QQ.COM','12');
INSERT test_unique(username,email,card) VALUES('B',NULL,NULL);
INSERT test_unique(username,email,card) VALUES('C',NULL,NULL);

完善慕课网用户表的创建

-- 用户表 imooc_user
CREATE TABLE IF NOT EXISTS `imooc_user`(
`id` INT UNSIGNED AUTO_INCREMENT KEY COMMENT '用户编号',
`username` VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
`password` CHAR(32) NOT NULL COMMENT '密码',
`email` VARCHAR(50) NOT NULL UNIQUE COMMENT '邮箱',
`age` TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
`sex` ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
`tel` CHAR(11) NOT NULL UNIQUE COMMENT '电话',
`addr` VARCHAR(50) NOT NULL DEFAULT '北京' COMMENT '地址',
`card` CHAR(18) NOT NULL UNIQUE COMMENT '身份证号',
`married` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0 代表未结婚，1 代表已结婚',
`salary` FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
SHOW TABLES;
DROP TABLE IF EXISTS imooc_user;
DESC imooc_user;
SHOW CREATE TABLE imooc_user;
-- 测试布尔类型
CREATE TABLE test_bool(
id int,
married BOOL
);
CREATE TABLE test_bool1(
id int,
married BOOLEAN
);
  • 假设我们在建表时需要定义一个列名为年级 grade 的列，要求字段值从 one、two、three 中选择一项，默认值为 one，则下列操作语句正确的是
    grade ENUM(‘one’,’two’,’three’) DEFAULT ‘one’
  • 若在数据表中需要创建列 score 用来存储某个班学生的考试成绩（0~100 分，没有小数），那用下面哪种数据类型最节省空间【 TINYINT】
喜欢 (0)赏
[]
分享 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi，您需要填写昵称和邮箱！

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(1)个小伙伴在吐槽
  1. 5+5
    clay2021-09-11 22:41 回复
已稳定运行：3年255天5小时17分