- 前言:认知数据库管理系统MySQL,了解其基本概念
熟悉MySQL的基本操作
学会使用Navicat去操作MySQL
20200803
1 认知数据库
1.1 对比Excel来认识数据库
数据库(Database),对应一个Excel文件
数据表(Table),对应Excel文件中的一个工作表sheet
行(rows)与列(columns),二维数据表的行与列
Excel和MySQL的对比:Excel最多存放104万行的数据,MySQL就没有太
大的限制
1.2 数据库的构成
列:对应表中的某个字段
行:对应表中的某行记录
表:表是结构化的信息,行和列组成表
数据库:一个数据库中可以包括多张数据表
总结:行和列构成表,一张张表构成数据库实例,MySQL里面可以放多个数据库实例
MySQL里面可以存放多少数据呢?
- MySQL每个数据库最多可创建20亿个表
- 一个表允许定义1024列
- SQL对每个表中行的数量没有直接限制,但它受数据库存储空间的限制
1.3 市面上主流的数据库管理系统
1.4 SQL语言简介
- SQL:Structured Query Language,即结构化的查询语言
- 是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和
管理关系数据库系统 - 数据分析师最常用的是:数据查询语言(DQL)、数据操作语言(DML)、数据定义语言(DDL);也就是众所周知的,增删改查
2 MySQL的基本操作
2.1 连接到MySQL
- MySQL服务正常启动,处于正在运行的状态
- 以命令行的方式连接到MySQL:mysql -u root -p 密码
2.2 MySQL的常用指令
- 查看所有的数据库:show databases
- 使用/进入数据库:use 数据库名
- 查看创建的数据库:show create database 库名
- 查看数据表结构:describe/desc 表名
- 查看数据表详细结构:show create table 表名
2.3 数据库的操作
语法结构
create database demo;
扩展1:查看创建的数据库
SHOW CREATE DATABASE demo;
扩展2:删除数据库[了解]
DROP DATABASE demo;
2.4 创建表
语法结构
CREATE TABLE 表名 --创建的表名不区分大小写,但不能使用关键字,如DROP、ALTER等
(
字段1 数据类型 [列级别约束条件] [默认值],
字段2 数据类型 [列级别约束条件] [默认值],
.......,
[表级别约束条件]
)[ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COMMENT='关于表的注释'];
注:
ENGINE=InnoDB #引擎类型(事务处理引擎)
AUTO_INCREMENT=1 #每增加一行时自动增量
CHARSET=utf8mb4 #编码格式
建表案例演示
-- 主键约束
create table tb_emp1(
id INT(11) PRIMARY KEY,
name VARCHAR(20),
deptId INT(11),
salary FLOAT
);
等价于
create table tb_emp2(
id INT(11),
name VARCHAR(20),
deptId INT(11),
salary FLOAT
[constraint 自定义主键约束名] PRIMARY KEY(id)
--若是主键由多个字段联合组成,可以在括号中(字段1,字段2...)
);
注:
PRIMARY KEY:表示主键约束,限制了改列的值都是唯一且非空的。
-- 外键约束
说明:外键用来在两个表的数据之间建立连接,可以是一列或者是多列,也就是一个表可以有一个或多个外键。外键对应的是参照完整性,
一个表的外键可以为空值;若不为空值,则每一个外键值必须等于另一个表中主键的某个值。外键是表中的某个字段,对应另一个表的主
键,其作用是保证数据引用的完整性,定义外键后就不允许删除在另一个表中具有关联关系的行。主表(父表):对于两个具有关联关系的
表,主键所在表是主表。从表(子表):对于两个具有关联关系的表,外键所在表是从表。
-- 建立主表
create table tb_dept(
id INT(11) PRIMARY KEY, --部门编号
name VARCHAR(22) NOT NULL, --部门名称,非空约束
location VARCHAR(50) --部门位置
);
-- 建立从表
create table tb_emp3(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
[constraint fk_tb_dept] foreign key(deptId) references
tb_dept(id)
-- 唯一性约束
create table tb_dept2(
id INT(11) PRIMARY KEY,
name VARCHAR(22) UNIQUE,
location VARCHAR(50)
);
--或者是在最后指定 constraint unique_key UNIQUE(name)
)
说明:唯一性约束要求该列的值都是唯一的,允许为空,但只能出现一个空值。因此唯一性约束的功能是确保一列或者几列不出现重复值。一个
表中可以有多个UNIQUE字段,但只能有一个PRIMARY KEY声明,声明为主键的列不允许有空值,而唯一性约束的列可以且只有一个空值。
-- 默认约束:用来指定某列的默认值
create table tb_emp4(
id INT(11) PRIMARY KEY,
name VARCHAR(25) not null,
deptId INT(11) default 1000,
salary float
);
说明:如果在插入数据时没有指定部门编号,则默认都为1000。
-- 属性值自动增加
create table tb_emp5(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25) NOT NULL,
deptId INT(11),
salary FLOAT
);
说明:在插入记录时,默认的自增字段id的值从1开始,每次添加一条新纪录时
该值自动加1。
INSERT INTO tb_emp5 (name,salary) values('Tom',2000),
('Jack',10000),('Kevin',40);
select * from tb_emp5;
扩展1:查看表基本结构
desc tb_emp5;
扩展2:查看表详细结构语句
show create table tb_emp5\G
show create table tb_emp5
3 常用的数据类型
- 常用的char和varchar的对比
char(n)类型(定长)
当定义的是char(10),输入的是"abc"这三个字符时,它们占的空间一样是10个字节,包括7个空字节,而当检索到char值时,尾部的空格将
被删除;当输入的字符长度超过指定的数时,char会截掉超出的字符,若是在严格模式下,会直接报错(Data too long for column)。
例如:6位数的密码char(6)
varchar(n)类型(可变长度)
varchar、text与blob都是变长类型,其存储需求取决于列值的实际长度,而不是取决于类型的最大可能尺寸n。例如,一个varchar(10)列
能保存最大长度为10个字符的字符串,实际的存储需要的是字符串的长度L加上1字节(记录字符串的长度)。对于字符“abcd”,L是4而存储要求
是5字节。varchar的最大实际长度由最长的行的大小和使用的字符集确定,而其实际占用的空间为字符串的实际长度加1。
- 整数与浮点数、定点数的对比
如果不需要小数部分,就使用整数来保存数据;如果需要表示小数部分,就使用浮点数类型。对于浮点数据列,存入的数值会对该列定义的小数位
进行四舍五入。举例来说,若某列值的范围为1~99999,若使用整数,则MEDIUMINTUNSIGNED是最好的类型;若需要存储小数,则使用FLOAT
类型。浮点类型包括FLOAT和DOUBLE类型,DOUBLE类型精度比FLOAT类型高,因此要求存储精度比较高时应选择DOUBLE类型。
浮点数FLOAT、DOUBLE相对于定点数DECIMAL的优势是:在长度一定的情况下,浮点数能表示更大的数据范围。由于浮点数容易产生误差,因此
对精确度要求比较高时,建议使用DECIMAL来存储。DECIMAL在MySQL中是以字符串存储的,用于定义货币等对精确度要求比较高的数据。在数
据迁移中,float(M,D)是非标准化SQL定义,数据库迁移可能会出现问题,最好不要这样使用。另外,两个浮点数进行减法和比较运算时也容易
出现问题,因此在进行计算的时候,一定要小心。进行数值比较时,最好使用DECIMAL类型。
- 日期和时间类型
MySQL对于不同种类的日期和时间有很多数据类型,比如YEAR和TIME。如果只需要记录年份,则使用YEAR类型即可;如果只记录时间,则使TIME
类型。如果需要同时记录日期和时间,则可以使用TIMESTAMP或者DATETIME类型。由于TIMESTAMP列的取值范围小于DATETIME的取值范围,
因此存储范围较大的日期最好使用DATETIME。
TIMESTAMP也有一个DATETIME不具备的属性。默认情况下,当插入一条记录但没有指定TIMESTAMP这个列值时,MySQL会把TIMESTAMP列设
为当前的时间。因此当需要插入记录的同时插入当前时间时,使用TIMESTAMP是方便的。另外,TIMESTAMP在空间上比DATETIME更有效。
- BLOB和TEXT
BLOB是二进制字符串,TEXT是非二进制字符串,两者均可存放大容量的信息。BLOB主要存储图片、音频信息等,而TEXT只能存储纯文本文件,如
文章内容,评论等。当查询TEXT列的值时,不删除尾部空格。