学习笔记——JavaWeb开发
第一章 数据库相关概念
第二章 MySQL基础1
第三章 MySQL基础2
目录
一、 MySQL介绍
1.1 概述
- MySQL是一个
开放源代码的关系型数据库管理系统
-
MySQL支持大型数据库,支持5000万条记录的数据仓库,32位系统表文件最大可支持
4GB
,64位系统支持最大的表文件为8TB
-
MySQL使用
标准的SQL数据语言
形式 -
MySQL可以允许运行于多个系统上,并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP和Ruby等
1.2 MySQL服务
1.2.1 服务的停止和启动
1 桌面图形化操作
步骤1:打开windows服务
-
方式1:计算机(点击鼠标右键)→ 管理(点击)→ 服务和应用程序(点击)→ 服务(点击)
-
方式2:控制面板(点击)→ 系统和安全(点击)→ 管理工具(点击)→ 服务(点击)
-
方式3:任务栏(点击鼠标右键)→ 启动任务管理器(点击)→ 服务(点击)
-
方式4:单击【开始】菜单,在搜索框中输入“services.msc”,按Enter键确认
步骤2:找到MySQL(点击鼠标右键)→ 启动或停止(点击)
2 使用命令行操作
# 启动 MySQL 服务命令:
net start MySQL服务名
# 停止 MySQL 服务命令:
net stop MySQL服务名
注意:
1. start和stop后面的服务名应与之前配置时指定的服务名一致。
2. 如果当你输入命令后,提示“拒绝服务”,请以`系统管理员身份`打开命令提示符界面重新尝试。
1.2.2 mysql的登录
方式1 自带客户端
开始菜单 → 所有程序 → MySQL → MySQL Command Line Client
说明:仅限于root用户
方式2 命令行
mysql -h 主机名 -P端口号 -u用户名 -p密码
例如: mysql -h localhost -P 3306 -uroot -proot
注意:
1 -p与密码之间不能有空格,其他参数名与参数值之间可以有空格也可以没有空格
2 密码建议在下一行输入,保证安全
mysql -h localhost -P 3306 -u root -p
Enter password:****
3 客户端和服务器在同一台机器上,所以输入localhost或者IP地址127.0.0.1。
同时,因为是连接本机: -hlocalhost就可以省略,如果端口号没有修改:-P3306也可以省略
简写格式
mysql -u root -p
Enter password:****
1.2.3 查看服务版本信息
c:\> mysql -V # 大写
c:\> mysql --version
或登录后,通过以下方式查看当前版本信息:
mysql> select version();
1.2.4 退出
exit
或
quit
二、数据模型
2.1 mysql支持多种数据类型
类型 | 类型举例 |
---|---|
整数类型 | TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT |
浮点类型 | FLOAT、DOUBLE |
定点数类型 | DECIMAL |
位类型 | BIT |
日期时间类型 | YEAR、TIME、DATE、DATETIME、TIMESTAMP |
文本字符串类型 | CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT |
枚举类型 | ENUM |
集合类型 | SET |
二进制字符串类型 | BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB |
JSON类型 | JSON对象、JSON数组 |
空间数据类型 | 单值类型:GEOMETRY、POINT、LINESTRING、POLYGON; 集合类型:MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION |
2.2 整数类
整数类型 | 字节 | 有符号数取值范围 | 无符号数取值范围 |
---|---|---|---|
TINYINT | 1 | -128~127 | 0~255 |
SMALLINT | 2 | -32768~32767 | 0~65535 |
MEDIUMINT | 3 | -8388608~8388607 | 0~16777215 |
INT、INTEGER | 4 | -2147483648~2147483647 | 0~4294967295 |
BIGINT | 8 | -9223372036854775808~9223372036854775807 | 0~18446744073709551615 |
适用场景
TINYINT
:一般用于枚举数据,比如系统设定取值范围很小且固定的场景。
SMALLINT
:可以用于较小范围的统计数据,比如统计工厂的固定资产库存数量等。
MEDIUMINT
:用于较大整数的计算,比如车站每日的客流量等。
INT、INTEGER
:取值范围足够大,一般情况下不用考虑超限问题,用得最多。比如商品编号。
BIGINT
:只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。
2.3 浮点类型
类型 | 大小 | 说明 |
---|---|---|
FLOAT | 4 bytes | 单精度浮点数值 |
DOUBLE | 8 bytes | 双精度浮点数值 |
DECIMAL | 小数值 |
2.4 定点数类型
MySQL中的定点数类型只有 DECIMAL 一种类型。
数据类型 | 字节数 | 含义 |
---|---|---|
DECIMAL(M,D) | M+2字节 | 有效范围由M和D决定 |
使用 DECIMAL(M,D) 的方式表示高精度小数。其中,M被称为精度,D被称为标度
DECIMAL(M,D)的最大取值范围与DOUBLE类型一样
定点数在MySQL内部是以
字符串
的形式进行存储,这就决定了它一定是精准的。当DECIMAL类型不指定精度和标度时,其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时,则MySQL同样会进行四舍五入处理
浮点数 vs 定点数
浮点数相对于定点数的优点是在长度一定的情况下,浮点类型取值范围大,但是不精准,适用于需要取值范围大,又可以容忍微小误差的科学计算场景(比如计算化学、分子建模、流体动力学等)
定点数类型取值范围相对小,但是精准,没有误差,适合于对精度要求极高的场景 (比如涉及金额计算的场景)
2.5 日期时间类型
日期与时间是重要的信息,在我们的系统中,几乎所有的数据表都用得到。原因是客户需要知道数据的时间标签,从而进行数据查询、统计和处理。
-
YEAR`类型通常用来表示年
-
DATE
类型通常用来表示年、月、日 -
TIME
类型通常用来表示时、分、秒 -
DATETIME
类型通常用来表示年、月、日、时、分、秒 -
TIMESTAMP
类型通常用来表示带时区的年、月、日、时、分、秒
类型 | 名称 | 字节 | 日期格式 | 最小值 | 最大值 |
---|---|---|---|---|---|
YEAR | 年 | 1 | YYYY或YY | 1901 | 2155 |
TIME | 时间 | 3 | HH:MM:SS | -838:59:59 | 838:59:59 |
DATE | 日期 | 3 | YYYY-MM-DD | 1000-01-01 | 9999-12-03 |
DATETIME | 日期时间 | 8 | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
TIMESTAMP | 日期时间 | 4 | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:00 UTC | 2038-01-19 03:14:07UTC |
date
DATE类型表示日期,没有时间部分,格式为YYYY-MM-DD
,其中,YYYY表示年份,MM表示月份,DD表示日期。需要3个字节
的存储空间。在向DATE类型的字段插入数据时,同样需要满足一定的格式条件。
以
YYYY-MM-DD
格式或者YYYYMMDD
格式表示的字符串日期,其最小取值为1000-01-01,最大取值为9999-12-03。YYYYMMDD格式会被转化为YYYY-MM-DD格式。以
YY-MM-DD
格式或者YYMMDD
格式表示的字符串日期,此格式中,年份为两位数值或字符串满足YEAR类型的格式条件为:当年份取值为00到69时,会被转化为2000到2069;当年份取值为70到99时,会被转化为1970到1999。使用
CURRENT_DATE()
或者NOW()
函数,会插入当前系统的日期。
datetime
DATETIME类型在所有的日期时间类型中占用的存储空间最大,总共需要8
个字节的存储空间。在格式上为DATE类型和TIME类型的组合,可以表示为YYYY-MM-DD HH:MM:SS
,其中YYYY表示年份,MM表示月份,DD表示日期,HH表示小时,MM表示分钟,SS表示秒。
在向DATETIME类型的字段插入数据时,同样需要满足一定的格式条件。
以
YYYY-MM-DD HH:MM:SS
格式或者YYYYMMDDHHMMSS
格式的字符串插入DATETIME类型的字段时,最小值为1000-01-01 00:00:00,最大值为9999-12-03 23:59:59。
以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时,会被转化为YYYY-MM-DD HH:MM:SS格式。
以
YY-MM-DD HH:MM:SS
格式或者YYMMDDHHMMSS
格式的字符串插入DATETIME类型的字段时,两位数的年份规则符合YEAR类型的规则,00到69表示2000到2069;70到99表示1970到1999。使用函数
CURRENT_TIMESTAMP()
和NOW()
,可以向DATETIME类型的字段插入系统的当前日期和时间。
TIMESTAMP
时间戳
只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中,UTC表示世界统一时间,也叫作世界标准时间。
2.6 文本字符串
CHAR和VARCHAR类型都可以存储比较短的字符串。
类型 | 大小 | 说明 |
---|---|---|
CHAR(M) | 0-255 bytes | 定长字符串 |
VARCHAR(M) | 0-65535 bytes | 变长字符串 |
CHAR类型:
CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M),则表示长度默认是1个字符。
如果保存时,数据的实际长度比CHAR类型声明的长度小,则会在
右侧填充
空格以达到指定的长度。当MySQL检索CHAR类型的数据时,CHAR类型的字段会去除尾部的空格。定义CHAR类型字段时,声明的字段长度即为CHAR类型字段所占的存储空间的字节数。
VARCHAR类型:
VARCHAR(M) 定义时,
必须指定
长度M,否则报错。MySQL4.0版本以下,varchar(20):指的是20字节,如果存放UTF8汉字时,只能存6个(每个汉字3字节) ;MySQL5.0版本以上,varchar(20):指的是20字符。
检索VARCHAR类型的字段数据时,会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。
哪些情况使用 CHAR 或 VARCHAR 更好
类型 | 特点 | 空间上 | 时间上 | 适用场景 |
---|---|---|---|---|
CHAR(M) | 固定长度 | 浪费存储空间 | 效率高 | 存储不大,速度要求高 |
VARCHAR(M) | 可变长度 | 节省存储空间 | 效率低 | 非CHAR的情况 |
情况1:存储很短的信息。比如门牌号码101,201……这样很短的信息应该用char,因为varchar还要占个byte用于存储信息长度,本来打算节约存储的,结果得不偿失。
情况2:固定长度的。比如使用uuid作为主键,那用char应该更合适。因为他固定长度,varchar动态根据长度的特性就消失了,而且还要占个长度信息。
情况3:十分频繁改变的column。因为varchar每次存储都要有额外的计算,得到长度等工作,如果一个非常频繁改变的,那就要有很多的精力用于计算,而这些对于char来说是不需要的。
情况4:具体存储引擎中的情况:
MyISAM
数据存储引擎和数据列:MyISAM数据表,最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化,从而使数据检索更快
,用空间换时间。
MEMORY
存储引擎和数据列:MEMORY数据表目前都使用固定长度的数据行存储,因此无论使用CHAR或VARCHAR列都没有关系,两者都是作为CHAR类型处理的。
InnoDB
存储引擎,建议使用VARCHAR类型。因为对于InnoDB数据表,内部的行存储格式并没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针),而且主要影响性能的因素是数据行使用的存储总量,由于char平均占用的空间多于varchar,所以除了简短并且固定长度的,其他考虑varchar。这样节省空间,对磁盘I/O和数据存储总量比较好。
三、DDL操作
3.1 操作数据库
3.1.1 创建数据库
-
方式1:创建数据库
CREATE DATABASE 数据库名; # 使用的默认的字符集
-
方式2:创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集; # 显示的指明了要创建的数据库的字符集
-
方式3:判断数据库是否已经存在,不存在则创建数据库(
推荐
)
CREATE DATABASE IF NOT EXISTS 数据库名;
如果MySQL中已经存在相关的数据库,则忽略创建语句,不再创建数据库。
注意:DATABASE 不能改名。一些可视化工具可以改名,它是建新库,把所有表复制到新库,再删旧库完成的。
要注意的事项:
在5.7中编码默认是拉丁的需要需改成utf8
在8.0中编码默认是utf8的
但是在企业中大部分使用的还是低版本 所以推荐在创建数据库的时候指明字符集
# 推荐的写法
create database if not exists mytest1 character set 'utf8';
#如果创建的库已经存在并且还要使用新的字符集字符集
create database if not exists mytest1 character set 'gbk';
#并不会报错,其并不会覆盖原来的内容且字符集也没有变化
3.1.2 使用数据库
-
查看当前所有的数据库
SHOW DATABASES; #有一个S,代表多个数据库
-
查看当前正在使用的数据库
SELECT DATABASE(); #使用的一个 mysql 中的全局函数
-
使用/切换数据库
USE 数据库名;
-
查看指定库下所有的表
SHOW TABLES FROM 数据库名;
#如果只是查看当前库下的所有表
show tables
-
查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;
或者:
SHOW CREATE DATABASE 数据库名\G (命令行形式)
注意:要操作表格和数据之前必须先说明是对哪个数据库进行操作,否则就要对所有对象加上“数据库名.”。
3.1.3 删除数据库
-
方式1:删除指定的数据库
DROP DATABASE 数据库名;
-
方式2:删除指定的数据库(
推荐
)
DROP DATABASE IF EXISTS 数据库名;
3.1.4 修改数据库
-
更改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如:gbk、utf8等
3.2 操作表
3.2.1 查询表
- 查询某个数据库下的所有表名称
SHOW TABLES;
- 查看某个库中的具体的表的结构
DESCRIBE 表名;
或
DESC 表名;
show columns from 表名;
desc 数据库名.表名; # 在一个库中查看另一个库中的表的结构
使用SHOW CREATE TABLE语句不仅可以查看表创建时的详细语句,还可以查看存储引擎和字符编码。
如果创建表的时候没有指明字符集 那么默认使用所在库的字符集
SHOW CREATE TABLE 表名\G
# 展示创建表的结构信息
CREATE TABLE `emp` (
`e_id` int(11) DEFAULT NULL,
`e_name` varchar(20) DEFAULT NULL,
`e_bir` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
3.2.2 创建表
创建表方式1
-
必须具备:
-
CREATE TABLE权限 (当前用户需要有创建表的权限)
-
存储空间
-
-
语法格式:
CREATE TABLE [IF NOT EXISTS] 表名(
字段1 数据类型 [约束条件] [默认值],
字段2 数据类型 [约束条件] [默认值],
字段3 数据类型 [约束条件] [默认值],
……
字段n 数据类型 # 最后一个字段且该字段后边没有内容不能加逗号
[表约束条件]
);
加上了IF NOT EXISTS关键字,则表示:如果当前数据库中不存在要创建的数据表,则创建数据表;如果当前数据库中已经存在要创建的数据表,则忽略建表语句,不再创建数据表。
-
必须指定:
-
表名
-
列名(或字段名),数据类型,长度
-
-
可选指定:
-
约束条件
-
默认值
-
-
创建表举例1:
-- 创建表
CREATE TABLE emp (
-- int类型
emp_id INT,
-- 最多保存20个中英文字符
emp_name VARCHAR(20),
-- 总位数不超过15位
salary DOUBLE,
-- 日期类型
birthday DATE
);
创建表方式2
-
使用 AS subquery 选项,将创建表和插入数据结合起来
-
指定的列和子查询中的列要一一对应
-
通过列名和默认值定义列
创建表举例2:
# 创建的A表不是空表 有B的所以信息
CREATE TABLE A AS SELECT * FROM B;
# 创建的A是空表,但是表字段和B保持一致
CREATE TABLE A AS SELECT * FROM B WHERE 1=2;
# 创建的表还可以是其他表的一部分数据
CREATE TABLE A
AS
SELECT B_id, B_name, B_salary
FROM B
WHERE B_id = 80;
#在通过as方式创建表的时候还可以是其他库中的表
create table 表
as
select * from 库名.表名;
3.2.3 修改删除表
删除表
在MySQL中,当一张数据表
没有与其他任何数据表形成关联关系
时,可以将当前数据表直接删除。数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除
语法格式:
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n]; #可以一次性的删除多个
IF EXISTS
的含义为:如果当前数据库中存在相应的数据表,则删除数据表;如果当前数据库中不存在相应的数据表,则忽略删除语句,不再执行删除数据表的操作。
-
举例:
DROP TABLE 表名;
-
DROP TABLE 语句不能回滚 除非有日志备份等
重命名表
-
方式一:使用RENAME
RENAME TABLE 表名
TO 新表名;
-
方式二:
ALTER table 表名
RENAME [TO] 新表名; -- [TO]可以省略
清空表
-
TRUNCATE TABLE语句:
-
删除表中所有的数据 但是表结构存在
-
释放表的存储空间
-
-
举例:
TRUNCATE TABLE 表名;
-
TRUNCATE语句不能回滚,而使用 DELETE 语句删除数据,可以回滚
修改表的结构
修改表指的是修改数据库中已经存在的数据表的结构。
使用 ALTER TABLE 语句可以实现:
向已有的表中添加列
修改现有表中的列
删除现有表中的列
重命名现有表中的列
追加一个列
语法格式如下:
ALTER TABLE 表名 ADD [COLUMN] 字段名 字段类型 [FIRST|AFTER 字段名];
# 可以选择添加位置【FIRST|AFTER 字段名】
# 如果不选默认是在最后一列
举例:
ALTER TABLE dept ADD XX varchar(15);
修改一个列
-
可以修改列的数据类型,长度、默认值和位置 (一般我们不会修改一个字段的数据类型)
-
修改字段数据类型、长度、默认值、位置的语法格式如下:
ALTER TABLE 表名 MODIFY [COLUMN] 字段名1 字段类型 [DEFAULT 默认值] [FIRST|AFTER 字段名2];
#如果想调整位置first 不能跟字段 表示直接放到第一位
#after 能跟字段名 表示放到哪个字段后边
举例:
ALTER TABLE 表名
MODIFY 列名 VARCHAR(30);
重命名一个列
使用 CHANGE old_column new_column dataType子句重命名列。语法格式如下
ALTER TABLE 表名 CHANGE [column] 列名 新列名 新数据类型;
举例:
ALTER TABLE dept80
CHANGE department_name dept_name varchar(15);
删除一个列
删除表中某个字段的语法格式如下:
ALTER TABLE 表名 DROP [COLUMN]字段名
举例:
ALTER TABLE dept
DROP COLUMN job_id;