Mysql数据库基础知识1

数据库概述

什么是数据

数据是用于描述某些事物的属性，是一种符号，比如之前我们学习代码的时候说声明的各种变量里面赋值的都称之为数据，例如：学号，姓名，性别，生日，学分，专业等等信息都可以称之为数据

什么是数据库

数据库通俗的理解就是存储数据的仓库，具备能够长期有效储存数据的能力，同时也能保证数据库储存的结构性，安全性，可移植性，良好的备份机制

为什么需要数据库

之前我们在学习代码的时候所有的数据都是在内存中以变量的形式存在，而内存是一个临时存储，只有在程序运行的时候可以用来储存数据，当程序结束运行数据销毁，因此需要一个能够长期的有效的保存数据的载体

长期储存数据的载体中包含了文件系统、数据库系统，其中文件系统虽然能够长期有效的储存数据，但是无法保证文件的完整性，结构性，另外对于超大文件需要消耗大量的内存开启，如果需要从文件中筛选有效数据，需要对整个文件读取，需要大量内存和时间开销，因此文件系统不是一个合适持久化数据储存的载体

此时就有了数据库系统，目前主流的数据系统以关系型为主

如何理解数据库

数据库系统 == 档案室

数据库实例 == 档案柜

数据库表 == 抽屉

数据 == 文件袋

数据库的相关名词

• DBS 数据库系统（由以下三者构成的一个集合体）
• DBMS 数据库管理系统
• DB 数据库
• DBA 数据库管理员

常见的数据库管理系统（关系型）

oracle 甲骨文

MSSQLServer 微软

Access 微软

BD2 IBM

informix IBM

mySQL oracle

达梦数据库 （光谷软件园）

OceanBase 阿里巴巴

关系型数据库（RDBMS）

• 这种类型的数据库是最古老的数据库类型，关系型数据库就是把复杂的数据解构归为简单的二维结构（及二维表格结构）
• 关系型数据以行row和列column的形式储存数据，这一系列的行和列称之为表table，一组表组成一个库database
• 表与表之间的数据记录有关系，关系型数据库就是建立在关系模型上的数据库
• SQL语句就是关系型的查询语言

关系型数据设计原则

• 关系型数据的典型数据结构就是数据表，这些数据表的组成都是结构化的
• 将数据放在表中，表再放到库中
• 一个数据库可以有多张表，每个表都有一个名字，用来标识自己，表名具备唯一性
• 表具备一些特性，这些特性定义了数据再表中如何储存，类似我们之前在JS中学习的构造函数，如果通过面向对象设计的语言的特性来表达就是类class的设计

表、记录、字段

E-R（entity-relationship, 实体-联系） 模型中有三个主要观念：

实体集：一个实体集（class）对应数据库一个表（table），一个实体（instance）对应数据库表中的一行（row），也称之为一条记录

属性：一个属性（attribute）对应数据库表中的一列（column），也称为一个字段（field）

联系集：表的关联关系

表的关联关系

表与表之间的数据记录有关系

具体的表现为4种关系：一对一关联，一对多关联，多对多关联、自我引用

一对一关联

实际开发用应用不多，因为一对一可以直接用一张表来表示

举例：设计学生表：学号、姓名、手机号、班级、性别、系别、省份证号、家庭住址、籍贯、紧急联系人…

拆分成两张表：两个表之间的记录是一一对应的关系

• 基础信息表（常用信息）：学号、姓名、手机号、班级、系列
• 档案信息表（不常用信息）：学号、省份证号、家庭住址、籍贯、紧急联系人

两种建表原则：

• 外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一
• 外键即主键：主表的主键和从表的主键、形成主外键关系

一对多的关系

常见实例场景：客户表和订单表、分类表和商品表、部门表和员工表

举例：

• 员工表：编号、姓名 … 所属部门
• 部门表：编号、名称、简介

一对多的建表原则：在从表（多方）创建一个字段，该字段作为外键指向主表（一方）的主键

部门表（主表）

部门编号（主键）	名称	简介
001	捕鱼部门	专业摸鱼20年
002	睡觉部门	一觉直接睡三天

员工表1（从表）

员工编号（主键）	姓名	所属部门（主表的部门编号外键）
0001	张三	001
0002	李四	002

多对多关系

要表示多对多的关系，必须创建第三个表，该表通过被称为联接表，他将多对多关系划分成两个一对多关系，将这两个表的主键都插入到第三个表中

举例：学生-课程

学生信息表：一行代表一个学生信息

课程信息表：一行代表一个课程信息

选课信息表：一个学号可以选多门课，一门课也可以被多个学生选

学号	课程编号
1	1001
2	1001
3	1002
1	1003

举例2：产品-订单

订单表和产品表有一种多对多的关系，这种关系是通过与订单明细表建立的两个一对多关系来定义的，一个订单可以有多个产品，每个产品可以出现在多个订单当中

• 产品表：每条记录表示一个产品
• 订单表：每条记录表示一个订单
• 订单明细表：每个产品可以与订单表中的多条记录对应，即出现在多个订单中，要给订单可以与产品表中的多条记录对应，即包含多个产品

使用mySQL

使用windows命令行

mysql -h 主机名 -P 端口号 -u 用户名 -p密码

注意：

-p与密码之间不能有空格，其他的参数名和参数值之间可以有空格也可以没有，密码我们一般不建议直接在写在-p后面

客户端和服务器在在同一台机器上，我们直接输入本地服务器地址即可 localhost或者127.0.0.1，同时因为是链接本机：-h输入主机名的部分可以省略掉，如果端口号没有修改，-P3306 也可以省略掉

简写：

mysql -u root -p
Enter password:*******

登录之前，可以通过以下指令查看mysql版本

mysql --version 或 mysql -v

登录之后：

select version()

退出登录

exit 或 quit

基础命令

显示数据库：

show databases;

创建数据库实例

create database 数据库名；

使用数据库

use mydb;

显示当前实例下所有的数据表

show tables;

删除数据库

drop database 数据库名

查看表的创建信息

show create table 表名称\G

查看数据库的创建信息

show create database 数据库名称\G

SQL概述

目前主流的数据库产品非常多，但是这些所有数据库产品都支持一项通用的操作语言：SQL

基本规则

• SQL语句可以写在一行也可以写多行，为了提高可读性，语句还是分行写，必须时使用缩进
• 每条命令以；或者\g或\G结束
• 关键字不能被缩写也不能分行
• 关于标点符号：
• • 必须保证所有的（），单引号，双引号成对的
  • 必须使用英文状态下半角输入方式
  • 字符串型和日期时间类型的数据可以使用单引号表示
  • 列的别名，尽量使用双引号，而且不建议省略as

SQL大小写规范

• mysql在windows环境下大小写不敏感

• mysql在Linux环境下大小写敏感

  • 数据库名、表名、表的别名、变量名是严格区分大小写的
  • 关键字、函数名、列表、列的别名是忽略大小写的

  推荐采用统一的书写规范：

  数据库名、表名、表别名、字段名、字段别名都小写

  SQL关键字、函数名、绑定变量大写

注释

单行注释：# mysql特有的方式
单行注释：-- 
多行注释：/*   */

命名规则

• 数据库名、表明不得超过30个字符，变量名限制为29个
• 必须只能包含A-Z，a-z，0-9，_ 供63字符
• 数据库名、表名、字段名等对象名中不要包含空格
• 同一个mySQL软件中，数据库不能同名，同一个库中，表不能重名，同一个表中字段不能重名
• 必须保证你的字段没有保留字
• 保持字段名和类型的一致性

SQL结构化查询语言，是一种类似英语口语的命令语句，对于SQL语句主要分为以下三个分支：

1、DCL语句（数据控制语句）

• 授权 grant
• 接触授权 revoke

2、DDL语句（数据定义语句：对表结构的操作）

• 创建 create
• 删除 drop
• 修改 alter

3、DML（数据操作语句：主要对表中的数据操作）

• 增加 insert
• 删除 delete
• 修改 update
• 查询 select

DDL语句

DDL数据定义语句：主要用于对表、储存过程、索引、触发器等元素进行结构上的操作，例如，创建表、删除表、修改表

建表操作

语法

create table 表名称
(
	列名称：类类型,
	...
)

举例：

create table user
(
	uid int,
	username varchar(16),
	password varchar(32),
	sex char(1)
);

新增列

语法

alter table 表名称 add 列名称 列类型

举例：

alter table user add birthday date;

修改列类型

语法：

alter table 表名称 modify column 列名称 列类型

举例：

alter table user modify column sex varchar(2);

删除列

语法：

alter table 表名称 drop column 列名;

举例：

alter table user drop column birthday;

修改表名称

语法：

alter table 表名称 rename to 新表名

举例：

alter table tbuser rename to user;

删除表

drop table 表名称

注意：

删除的表不能被其他表关联

修改列名称

alter table 表名称 change 旧列名 新列名 类型;

DML语句

DML：数据操作语句，主要包含的是针对表中的数据进行操作的指令，例如：增（insert）删（delete）

改（update）查（select）

新增数据

语句：

insert into 表名称(列名1，列2,...) values(列值1，列值2，...)

举例：

insert into user values(1,'zhangsan','1111111','F','2000-01-02');

insert into user(username,password) values('lisi','123456');

注意：

1、如果没有指定列名，则表示向所有列插入数据

2、可以同时向表种插入多条数据

insert into user(username,password) values('lisi','123456'),('wangwu','123'),('zhaoliu','456');

3、如果插入的数据中有中文的情况下，可以会出现以下问题

insert into user values(10,'张三','1234','M','2001-02-02');

1366 - Incorrect string value: '\xE5\xBC\xA0\xE4\xB8\x89' for column 'username' at row 1

解决方案：

修改库字符集

alter database mydb charset utf8mb4;

修改表字符集

alter table user charset utf8mb4;

修改数据

语句：

update 表名称 set 列名1=列值1,列名2=列值2, ......  where 修改条件

举例：

update user set sex='女',birthday='1999-01-01' where username='zhangsan';

update user set sex='女',birthday='1999-01-01' where username='zhangsan' or password='123';

update user set sex='女',birthday='1999-01-01' where username='zhangsan' and password='123';

注意点：

对于为null的列进行条件判断时候不能使用 = ，要使用 is

update user set uid=2 where username='wangwu' and sex is null;

删除数据

语法：

delete from 表名称 where 删除条件

举例：

delete from user where username='zhaoliu';

查询操作（入门）

语法：

select 查询列 from 表名称 where 查询条件

举例：

select * from user;

select username,password from user where username='zhangsan';

数据类型

即然是数据那么就必然有类型之分，mysql数据库对于不同的数据也使用不同的类型进行表示：

• 数值
• 字符串
• 日期时间

数值类型

mysql中将整数和浮点数都表示为数值类型，数值类型主要包含以下几种：

mysql	面向对象
tinyint	byte
smallint	short
int	int
bigint	long
float	float
double	double
decimal(m,d)

常用类型：

tinyint 一般用于表示状态的字段，比如：订单状态（0：未支付，1：已支付，2：已出库，3：配送中）

int 用于表示常见的整数型，比如，年龄，库存

double：用于表示常见的浮点数据，比如，价格，体重，身高

decimal(m,d) ：用于表示对精度要求高的数值类型，比如金额

字符串类型

类型	说明
char(M)	定长字符串
varchar(M)	可变长字符串
tinytext	非常小的文本串
text	小文本串
mediumtext	中等文本串
longtext	大文本串
enum(‘value1’,‘value2’, …)	枚举
set(‘value1’,‘value2’, …)	集合

以上char和varchar中的M实际表示字符数，mysql中字符数据占据的内部储存空间如下：

字符集是gbk则一个字符占2个字节，使用char或者varchar储存中文的时候，最大范围66536/2

字符集是utf8则一个字符占3个字节，使用char或者varchar储存中文时，最大范围66536/3

常用字符串类型：

varchar 可变长字符串

char 定长字符串

text 表示文本类型

enum 枚举类型

关于enum

可以使用枚举类型的数据进行列举，表示列中储存的数据只能是枚举中的类型，也可以使用序列号来表示修改的列值

create table t1(
	a enum('男','女','male','famale')
);
-- 使用方式1：直接从枚举中选择值
insert into t1 values('女');
-- 使用方式2：使用枚举成员的序列号表示枚举值
insert into t1 values(2);
insert into t1 values('2');

在mysql中还存在数据类型的隐式转换（这种转换只局限在数据类型和字符串类型）

日期时间类型

在该类型比较常用的时间日期类型有：

类型	说明
date	用于表示日期（年-月-日）
datetime	用于表示日期时间（年-月-日 时:分:秒）
timestamp	用于表示时间戳（在创建表的时候可以指定默认值，自动生成，也可以在更新数据的时候自动更新）

常见日期类型的使用：

insert into t2(a) values('2021-09-09');
insert into t2(a) values('20210909');
insert into t2(a) values('2021/09/09');
-- now() 表示当前系统时间
insert into t2(a) values(now());

create table t3(
	id int,
	a timestamp default CURRENT_TIMESTAMP
)
insert into t3(id) values(2);

注意点：

我们在使用timestamp作为列类型的时候，一张表中只能为一个timestamp列指定CURRENT_TIMESTAMP

数据类型总结

mysql中常用的数据类型主要包含：

数值类型：

• int
• double
• decimal

字符串类型

• char
• varchar
• text

日期时间类型

• datetime
• timestamp
• date