1. 数据库概述
1.1 数据库相关概念
1.1.1 基本概念
了解三个概念:数据库、数据库管理系统、SQL。
名称 | 解释 | 简称 |
---|---|---|
数据库 | 存储数据的仓库,数据是有组织的进行存储 | DataBase(DB) |
数据库管理系统 | 操纵和管理数据库的大型软件 | DataBase Management System (DBMS) |
SQL | 操作关系型数据库的编程语言,定义了一套操作关系型数据库统一 标准 | Structured Query Language (SQL) |
常见的关系型数据库:
- Oracle:大型的收费数据库,Oracle公司产品,价格昂贵。
- MySQL:开源免费的中小型数据库,后来Sun公司收购了MySQL,而Oracle又收购了Sun公司。目前Oracle推出了收费版本的MySQL,也提供了免费的社区版本。
- PostgreSQL:开源免费的中小型数据库。
- DB2:IBM公司的大型收费数据库产品。
- SQLLite:嵌入式的微型数据库。Android内置的数据库采用的就是该数据库。
- MariaDB:开源免费的中小型数据库。是MySQL数据库的另外一个分支、另外一个衍生产品,与MySQL数据库有很好的兼容性。
1.1.2 数据模型
关系型数据库(RDBMS)
- 概念:建立在关系模型基础上,由多张相互连接的二维表组成的数据库。不是基于二维表存储数据的数据库,就是非关系型数据库。
- MySQL就是关系型数据库是基于二维表进行数据存储的,具体的结构图下
- 可以通过MySQL客户端连接数据库管理系统DBMS,然后通过DBMS操作数据库。
- 可以使用SQL语句,通过数据库管理系统操作数据库,以及操作数据库中的表结构及数据。
- 一个数据库服务器中可以创建多个数据库,一个数据库中也可以包含多张表,而一张表中又可以包含多行记录。
1.2 MySQL数据库
1.2.1 下载
1.2.2 安装
在官网安装msi文件
默认安装会装在C盘的C:\Program Files
目录下,我这里选择自定义安装
选择安装的应用
下面步骤基本就是next即可,就不放全部的步骤图片
1.2.3 配置环境
为在任何位置都能直接输入MySQL登录命令(省去每次输入具体的文件路径来操作MySQL),需要配置环境变量
1、在此电脑上,右键选择属性
2、点击左侧的 “高级系统设置”,
3、选择环境变量
4、找到 Path 系统变量, 点击 “编辑”
5、选择"新建" , 将MySQL Server的安装目录下的bin目录添加到环境变量
1.2.4 启动/停止mysql服务
以管理员身份运行命令行工具
net start mysql80
net stop mysql80
注意:这里的mysql80 是在安装MySQL时,默认指定的mysql的系统服务名,如果未改动,默认就是mysql80。若你自己改了,按你该的服务器名来(不区分大小写)。
1.2.5 客户端连接
方式一:使用MySQL提供的客户端命令行工具
方式二:使用系统自带的命令行工具执行指令
mysql [-h 127.0.0.1] [-P 3306] -u root -p
参数说明:
参数 | 说明 |
---|---|
-h | MySQL服务所在的主机IP |
-P | MySQL服务端口号, 默认3306 |
-u | MySQL数据库用户名 |
-p | MySQL数据库用户名对应的密码 |
中括号[]
内为可选参数,如果需要连接远程的MySQL,需要加上这两个参数来指定远程主机IP、端口,如果连接本地的MySQL,则无需指定这两个参数。
2. SQL
2.1 SQL通用语法
- SQL语句可以单行或多行书写,以分号结尾。
- SQL语句可以使用空格/缩进来增强语句的可读性。
- MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。
- 注释:
- 单行注释:
-- 注释内容
或# 注释内容
- 多行注释:
/* 注释内容 */
- 单行注释:
2.2 SQL分类
SQL语句,根据其功能,主要分为四类:DDL、DML、DQL、DCL。
分类 | 全称 | 说明 |
---|---|---|
DDL | Data Definition Language | 数据定义语言,用来定义数据库对象(数据库,表, 字段) |
DML | Data Manipulation Language | 数据操作语言,用来对数据库表中的数据进行增删改 |
DQL | Data Query Language | 数据查询语言,用来查询数据库中表的记录 |
DCL | Data Control Language | 数据控制语言,用来创建数据库用户、控制数据库的访问权限 |
3. DDL
Data Definition Language,数据定义语言,用来定义数据库对象(数据库,表,字段) 。
3.1 数据库操作
1、查询所有数据库
show databases;
2、查询当前数据库
select database();
3、创建数据库
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
可以通过if not exists 参数来解决这个问题,数据库不存在, 则创建该数据库,如果存在,则不创建。
【示例】
create database testdb; -- 创建一个testdb数据库, 使用数据库默认的字符集
create database if not extists testdb;
create database testdb default charset utf8mb4 collate utf8mb4_0900_ai_ci; -- 指定字符集和排序规则
4、删除数据库
drop database [if exists] 数据库名;
如果删除一个不存在的数据库,将会报错。此时,可以加上参数 if exists ,如果数据库存在,再执行删除,否则不执行删除。
5、切换/使用数据库
use 数据库名;
我们要操作某一个数据库下的表时,就需要通过该指令,切换到对应的数据库下,否则是不能操作的。
6、备份数据库
mysqldump -u 用户名 -p -B 数据库名1 数据库名2 > 文件名.sql -- 备份整个数据库
注意:要在DOS命令行执行
7、恢复数据库
Source 文件名.sql
另一种方法:也可以直接将 xxx.sql 的内容放到查询编辑器中执行
8、备份数据库的表
mysqldump -u 用户名 -p 密码 -B 数据库名 表1 表2 > 文件名.sql -- 备份表
3.2 表操作-创建/查询
1、查询当前数据库所有表
show tables;
2、查看指定表结构
desc 表名;
通过这条指令,我们可以查看到指定表的字段,字段的类型、是否可以为NULL,是否存在默认值等信息。
3、查询指定表的建表语句
show create table 表名;
通过这条指令,主要是用来查看建表语句的,而有部分参数我们在创建表的时候,并未指定也会查询到,因为这部分是数据库的默认值,如:存储引擎、字符集等。
4、创建表结构
CREATE TABLE 表名(
字段名1 字段1类型 [ COMMENT 字段1注释],
字段名2 字段2类型 [COMMENT 字段2注释],
字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释];
【示例】
CREATE TABLE tb_user(
id INT COMMENT '编号',
`name` VARCHAR(50) COMMENT '姓名',
age INT COMMENT '年龄',
gender VARCHAR(1) COMMENT '性别'
) COMMENT '用户表';
5、创建指定字符集和引擎的表结构
CREATE TABLE 表名(
field1 type,
字段名2 字段2类型,
字段n 字段n类型]
) character set 字符集 collate 排序规则 engine 引擎;
character set :若不指定则为所在数据库的字符集
collate可以说是排序规则或校对集:若不指定则为所在数据库的排序规则
engine:建表用的存储引擎
3.3 数据类型
MySQL中的数据类型有很多,主要分为三类:数值类型、字符串类型、日期时间类型。
3.3.1 数值类型
类型 | 占用大小 | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 | 描述 |
---|---|---|---|---|
TINYINT | 1byte | (-128,127) | (0,255) | 小整 数值 |
SMALLINT | 2bytes | (-32768,32767) | (0,65535) | 大整数值 |
MEDIUMINT | 3bytes | (-8388608,8388607) | (0,16777215) | 大整 数值 |
INT/INTEGER | 4bytes | (-2147483648,2147483647) | (0,4294967295) | 大整数值 |
BIGINT | 8bytes | (-2 ^ 63,2 ^ (63-1) | (0,2^64-1) | 极大整数值 |
FLOAT | 4bytes | (-3.402823466 E+38, 3.402823466351 E+38) | 0 和 (1.175494351 E- 38,3.402823466 E+38) | 单精度浮点数值 |
DOUBLE | 8bytes | (-1.7976931348623157E+308,1.7976931348623157E+308) | 0和(2.2250738585072014E-308,1.7976931348623157E+308) | 双精度浮点数值 |
DECIMAL | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度) 的值 | 小数值(精确定点数) |
3.3.2 字符串类型
类型 | 大小 | 描述 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串(需要指定长度) |
VARCHAR | 0-65535 bytes | 变长字符串(需要指定长度) |
TINYBLOB | 0-255 bytes | 不超过255个字符的二进制数据 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
说明:char是定长字符串,指定长度多长,就占用多少个字符,和字段值的长度无关 。而varchar是变长字符串,指定的长度为最大占用长度 。相对来说,char的性能会更高些。
3.3.3 日期时间类型
类型 | 大小 | 范围 | 格式 | 描述 |
---|---|---|---|---|
DATE | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | -838:59:59 至 838:59:59 | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901 至 2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:01 至 2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值,时间戳 |
3.4 表操作-修改
1、添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [ COMMENT 注释] [约束];
2、修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
3、修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [ COMMENT 注释] [约束];
4、删除字段
ALTER TABLE 表名 DROP 字段名;
5、修改表名
ALTER TABLE 表名 RENAME TO 新表名;
6、修改表字符集
ALTER TABLE 表名 CHARACTER SET 字符集;
3.5 表操作-删除
1、删除表
DROP TABLE [IF EXISTS] 表名;
可选项 IF EXISTS 代表,只有表名存在时才会删除该表,表名不存在,则不执行删除操作(如果不加该参数项,删除一张不存在的表,执行将会报错)。
2、删除指定表, 并重新创建表
TRUNCATE TABLE 表名;
注意: 在删除表的时候,表中的全部数据也都会被删除。
图形化界面工具推荐
4 DML
DML英文全称是Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进行增、删、改操作。
- 添加数据(INSERT)
- 修改数据(UPDATE)
- 删除数据(DELETE)
4.1 添加数据
1、给指定字段添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
2、给全部字段添加数据
INSERT INTO 表名 VALUES(值1, 值2, ...);
3、批量添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值