MySQL 8——学习笔记01(安装、数据库/数据表的操作 [DDL语句]、数据类型、运算符)
- 一、知识准备
- 1.0 数据库相关概念
- 1.0.1 关系型数据库特点
- 1.1 SQL 语言介绍及规范
- 1.2 MySQL的安装
- 1.3 什么是 MySQL?
- 1.4 MySQL 8 新特性(部分)
- 二、操作数据库[DDL语句]
- 2.1 常用命令
- 2.1.1 登录数据库
- 2.1.2 命令总结
- 2.2 数据库操作
- 三、操作数据表[DDL语句]
- 3.1 常用命令
- 3.2 创建数据表
- 3.3 约束
- 3.3.1 主键约束
- 3.3.2 外键约束
- 3.3.3 非空约束
- 3.3.4 唯一性约束
- 3.3.5 默认约束
- 3.3.6 属性自增
- 3.4 修改数据表
- 3.5 删除数据表
- 3.6 外键的更新删除策略
- 三、数据类型
- 3.1 整数类型
- 3.2 浮点数类型和定点数类型
- 3.3 日期与时间类型
- 3.4 文本字符串类型
- 3.5 二进制字符串类型
- 四、运算符
- 4.1 算术运算符
- 4.2 比较运算符
- 4.3 逻辑运算符
- 4.4 位运算
声明:
- 本博客摘录自
MySQL 8 从入门到精通(视频教学版) 王英英©著
。- 部分内容总结自互联网。
一、知识准备
1.0 数据库相关概念
1、DB
:数据库,按照一定的数据结构存储数据的仓库。
2、DBMS
:数据库管理系统,即数据库软件(产品),用于管理DB中的数据,又叫数据库服务器。
3、SQL
:结构化查询语言,用于和DBMS通信的语言。
1.0.1 关系型数据库特点
1)、将数据放到表中,表再放到库中。
2)、一个数据库中可以有多个表,每个表都有一个的名字,用来标识自己。表名具有唯一性。
3)、表具有一些特性,这些特性定义了数据在表中如何存储,类似java中 “类”的设计。
4)、表由列组成,也称为字段。所有表都是由一个或多个列组成的,每一列类似java 中的”属性”。
5、表中的数据是按行存储的,每一行类似于java中的“对象”。
关系型数据库反映了数据库对象和OOP的映射关系:
1.1 SQL 语言介绍及规范
对数据库进行查询和修改操作的语言叫做SQL。SQL 的含义是结构化查询语言(Structured Query Language)。
SQL 语言包含以下四个部分:
DQL(Data Query Language)
:数据查询语言,用于查询数据库记录。
关键字:selectDML(Data Manipulate Language)
:数据操作语言,用于添加、删除、修改数据库记录。
关键字:insert 、update、deleteDDL(Data Define Languge)
:数据定义语言,用于库和表等数据库对象的创建、修改、删除。
关键字:create、alter、dropDCL(Data Control Language)
:数据控制语言,用于创建或更改用户、角色及权限。
关键字:grant、revokeTCL(Transaction Control Language)
:事务控制语言,用于控制事务的提交或回滚。
关键字:commit、rollback
SQL语法规范:
不区分大小写
,但建议关键字大写,表名、列名小写;- 不能使用SQL语言中的关键字;
- 每条语句最好用分号结尾(登录命令除外)
- 注释有2种:单行注释:
-- 注释内容
,多行注释:/* 注释内容 */
1.2 MySQL的安装
略
1.3 什么是 MySQL?
MySQL是一个小型关系数据库管理系统。
1.4 MySQL 8 新特性(部分)
参考 拥抱大数据(知乎)
1、安全和账户管理
- MySQL 数据库的授权表统一为 InnoDB(事务性)表。
- MySQL 8.0 开始支持角色,角色可以看成一些权限的集合,为用户赋予统一的角色,权限的修改直接通过角色来进行,无须为每个用户单独授权。(管理员可以创建和删除角色)
- MySQL 8.0 开始维护和密码有关历史的信息,从而限制了以前密码的重用。
- MySQL 8.0 允许账户具有双密码,从而在多服务器系统中无缝地执行分阶段密码更改,无须停机。
2、InnoDB 增强功能
(1)MySQL 8.0 将自增主键的计数器持久化到重做日志中。每次计数器发生改变,都会将其写入重做日志中。如果数据库重启,InnoDB 会根据重做日志中的信息来初始化计数器的内存值。为了尽量减小对系统性能的影响,计数器写入重做日志时,并不会马上刷新数据库系统。
(2)如果索引损坏,InnoDB 将索引损坏标志写入重做日志,从而使得损坏标志安全。
(3)新的动态变量 innodb_deadlock_detect 可用于禁用死锁检测。在高并发系统上,当许多线程等待同一个锁时,死锁检测会导致速度减慢,此时禁用死锁检测可能更有效。
3、字符集支持
从 MySQL 8.0 开始,数据库的缺省编码从 latinl 改为 utf8mb4,这个编码包含了所有 emoji 字符。
4、增强 JSON 功能
(1)添加了 ->>
运算符,相当于调用 JSON_UNQUOTE() 。
(2)添加了两个 JSON 聚合函数 JSON_ARRAYAGG()
和JSON_OBJECTAGG()
。
(3)添加了 JSON 实用程序功能 JSON_PRETTY()
,JSON 以抑郁阅读的格式输出现有值。
(4)添加的 JSON_MERGE_PATCH()
可以合并符合 RFC 7396 标准的 JSON。在两个json对象上使用时,可以将他们合并成单个json对象。
5、查询的优化
(1)MySQL 8.0 支持不可见索引。优化器根本不使用不可见索引,但会以其他方式正常维护。默认情况下,索引是可见的。通过不可见索引,数据库管理员可以检测索引对查询性能的影响,而不会进行破坏性的更改。
(2)MySQL 8.0 开始支持降序索引。DESC 在索引定义中不再被忽略,而会降序存储索引字段。
6、公用表表达式
MySQL 现在支持非递归和递归的公用表表达式。公用表表达式允许使用命名的临时结果集,通过允许 WITH 语句之前的子句 SELECT 和某些其它语句来实现。
7、窗口函数
MySQL 8.0 版本中新增了一个窗口函数,用它可以实现很多新的查询方式。窗口函数类似于 SUM()、COUNT() 这样的集合函数,但它并不会将多行查询结果合并为一行,而是将结果放回多行当中。
8、备份锁
新类型的备份锁在联机备份期间允许 DML,同时防止可能到时快照不一致的操作。
二、操作数据库[DDL语句]
2.1 常用命令
2.1.1 登录数据库
#-h 后面的参数是服务器的主机地址
#-u 后面跟登录数据库的用户名(本地主机可以使用 localhost 或 127.0.0.1)
#-p 后面是用户登录密码(输入 -p 后回车输入密码,如下:)
C:\Users\cb> mysql -h localhost -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 195
Server version: 5.7.23 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#退出数据库
mysql> quit
Bye
C:\Users\cb>
2.1.2 命令总结
解释 | 命令 |
---|---|
退出 MySQL服务器 | exit; 或 quit; |
查看服务器版本 | select version(); |
查看字符集的 | show variables like 'character_set_database'; |
`` | |
`` | |
`` |
2.2 数据库操作
解释 | 命令 |
---|---|
查看所有数据库 | show databases; |
创建数据库 | create database [IF NOT EXISTS] 数据库名; |
查看数据库的定义 | show create database 数据库名; (包含编码等信息) |
删除数据库 | drop database 数据库名; (如果指定的数据库不存在则报错!) |
选择指定数据库 | use 数据库名; |
查看正在使用的库 | select database(); |
`` |
长命令:
1、查看系统表类型:select distinct(engine) from information_schema.tables
2、修改root用户的密码(三条命令):
- 命令一:
use mysql;
- 命令二:
update user set authentication_string=password('新密码') where user='root';
- 命令三:
flush privileges;
(刷新权限)
3、修改默认字符集为 utf8mb4
三、操作数据表[DDL语句]
3.1 常用命令
解释 | 命令 |
---|---|
查看当前库的所有表 | show tables; |
查看表的结构 | desc 表名; |
查看建表的SQL语句 | show create table 表名; |
查看数据表的默认编码 | show create table tb_emp \G |
`` | |
`` |
3.2 创建数据表
数据表属于数据库,在创建数据表之前,应该使用语句use <数据库名>
指定操作是在哪个数据库中进行,如果没有选择数据库,就会抛出 “No database selected” 的错误。
【建表语句】
CREATE TABLE [ IF NOT EXISTS] 表名(
字段名 1 数据类型1 [列级约束] [默认值],
字段名 2 数据类型2 [列级约束] [默认值],
......
[表级约束]
);
注意:
- 要创建的表的名称,不区分大小写,不能用 SQL 语言中的关键字。
- 数据表中每一列(字段)的名称和数据类型,如果创建多列,就要用逗号隔开。
3.3 约束
3.3.1 主键约束
(1)在定义列的同时指定主键,格式:
字段名 数据类型 primary key [默认值]
(2)在定义完所有列之后指定主键,格式:
[constraint <约束名>] primary key [字段名]
(3)多字段联合主键,格式:
primary key [字段1,字段2,字段3,...]
主键
,又称主码,是表中一列或多列的组合。
主键约束(Primary Key Constraint)
要求主键列的数数据唯一,并且不允许为空。主键能够唯一地标识表中的一条记录。
主键分为两种类型:单字段主键
和多字段联合主键
。
3.3.2 外键约束
创建外键的语法规则如下:
[constraint <外键名>] foreign key 字段名1 [, 字段名2, ...] references <主表名> 主键列1 [, 主键列2 , ...]
外键用来在两个表的数据之间建立连接,可以是一列或者多列。
外键对应的是参照完整性,一个表的外键可以为空值,若不为空值,则每一个外键值必须等于另一个表中主键的某个值。
外键
:首先它是表中的一个字段,虽然可以不是本表的主键,但要对应另外一个表的主键。
主表(父表)
:对于两个具有关联关系的表而言,相关联字段中主键所在的那个表即是主表。
从表(子表)
:对于两个具有关联关系的表而言,相关联字段中外键所在的那个表即是从表。
【案例】
create table tb_emp
{
id int(11) primary key,
name varchar(25),
deptId int(11),
constraint fk_emp_dept1 foreign key(deptId) references tb_dept(id)
};
3.3.3 非空约束
非空约束(Not Null Constraint)
指字段的之不能为空。对于使用了非空约束的字段,如果用户在添加数据时没有指定值,数据库系统会报错。
【语法】
字段名 数据类型 not null
3.3.4 唯一性约束
唯一性约束(Unique Constraint)
要求该列唯一,允许为空,但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。
【语法】
(1)在定义完列之后直接指定唯一约束:
字段名 数据类型 unique
(2)在顶一万所有列之后指定唯一约束:
[constraint <约束名>] unique(<字段名>)
unique 和 primary key 的区别:
- 一个表汇中可以有多个字段声明 unique,但只有一个primary key 声明;
- 声明 primary key 的列不允许有空值,但是声明为 unique 的字段允许空值(null)的存在;
3.3.5 默认约束
默认约束(Default Constraint)指定某列的默认值。
字段名 数据类型 default 默认值
【案例】
create table tb_emp1
{
id int(11) primary key,
name varchar(25),
deptId int(11) default 222
}
3.3.6 属性自增
默认的,在mysql中 auto_increment
的初始值为 1,每新增一条记录,字段值自动加 1。
一个表只能有一个字段使用 auto_increment 约束,并且该字段必须为主键的一部分。
auto_increment 约束的字段可以是任何整数类型。
【语法】
字段名 数据类型 auto_increment
3.4 修改数据表
修改表就是对表结构的修改,关键字是ALTER TABLE。包含如下几种操作:
#1. 添加字段
语法:ALTER TABLE 表名 ADD 列名 类型;
示例:ALTER TABLE studentinfo ADD email CHAR(20);
#2. 修改字段的类型
语法:ALTER TABLE 表名 MODIFY 列名 新的类型;
示例:ALTER TABLE studentinfo MODIFY email VARCHAR(20);
#3. 修改字段名(及类型)
语法:ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
示例:ALTER TABLE studentinfo CHANGE saddress address varchar(20);
#4. 删除字段
语法:ALTER TABLE 表名 DROP 列名;
示例:ALTER TABLE studentinfo DROP email;
#5. 修改表名
语法:ALTER TABLE 旧表名 RENAME TO 新表名;
示例:ALTER TABLE studentinfo RENAME TO student ;
#6.删除表的外键约束
语法:ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;
实例:ALTER TABLE tb_emp1 DROP FOREIGN KEY fk_emp_dept1;
3.5 删除数据表
删除表
语法:DROP TABLE [IF EXISTS] 表名1, 表名2, ...;
实例: DROP TABLE IF EXISTS tb_emp1;
注意:
- 在数据表之间存在外键关联的情况下,如果直接删除父表,结果会显示失败,原因是直接删除将破坏表的参照完整性。
- 如果必须要删除,可以先删除与其关联的子表,再删除父表。
3.6 外键的更新删除策略
策略名称 | 含义 |
---|---|
RESTRICT | 如果父表在子表中有关联的记录,则拒绝删除或更新父表 |
NO ACTION | 标准SQL的关键字,在MySQL中与RESTRICT意义相同 |
CASCADE | 更新或删除父表记录时,自动更新或删除子表中匹配的记录 |
SET NULL | 更新或删除父表记录时,将子表中外键列设置为NULL,要求子表中外键列允许为null |
三、数据类型
数据库表由多列字段构成,每一个字段制定了不同的数据类型。指定字段的数据类型后也就决定了向字段插入的数据内容。MySQL中一个字节为一个长度!
MySQL 支持多种数据类型,主要有数值类型、日期/时间类型和字符串类型。
- 数值类型:包括整数类型 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮点小数数据类型 FLOAT 和 DOUBLE,顶点小数类型 DECIMAL。
- 日期/时间类型:包括 YEAR、TIME、DATE、DATETIME、TIMESTAMP。
- 字符串类型:CHAR、 VARCHAR、 BINARY、 VARBINARY、 BLOB、 TEXT、 ENUM、 SET 等。字符串类型又分为文本字符串和二进制字符串。
3.1 整数类型
MySQL 中的整数型数据类型、取值范围说明
类型名称 | 说明 | 有符号 | 无符号 | 存储要求 |
---|---|---|---|---|
tinyint | 很小的整数 | -128~127 | 0~255 | 1 字节 |
smallint | 小的整数 | -32768~32767 | 0~65535 | 2 字节 |
mediumint | 中等大小的整数 | -8388608~8388607 | 0~16777215 | 3 字节 |
int(integer) | 普通大小的整数 | -2147483648~2147483647 | 0~4294967295 | 4 字节 |
bigint | 大整数 | -9223372036854775808~9223372036854775807 | 0~18446744073709551615 | 8 字节 |
3.2 浮点数类型和定点数类型
MySQL 中使用浮点数和定点数来表示小数。
浮点数类型有两种:单精度浮点类型(float)
和双精度浮点类型(double)
。
定点数类型
只有一种:decimal。
MySQL 中的小数类型说明
类型名称 | 说明 | 存储要求 |
---|---|---|
float | 单精度浮点数 | 4 字节 |
double | 双精度浮点数 | 8 字节 |
decimal(M,D),dec | 压缩的“严格”定点数 | M+2 字节 |
不论是定点数还是浮点数类型,如果用户指定的精度超出精度范围,则会四舍五入。
3.3 日期与时间类型
MySQL 中的日期与时间数据类型
类型名称 | 日期格式 | 日期范围 | 存储要求 |
---|---|---|---|
year | YYYY | 1901~2155 | 1 字节 |
time | HH:MM:SS | -838:59:59~838:59:59 | 3 字节 |
date | YYYY-MM-DD | 1000-01-01~9999-12-3 | 3 字节 |
datetime | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00~9999-12-31 23:59:59 | 8 字节 |
timestamp | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC | 4 字节 |
3.4 文本字符串类型
MySQL 中的文本字符串类型说明
类型名称 | 说明 | 存储要求 |
---|---|---|
char(M) | 固定长度非二进制字符串 | M 字节,1<= M <=255 |
varchar(M) | 变长非二进制字符串 | L+1 字节,在此 L<=M 和 1<=M<=255 |
tinytext | 非常小的非二进制字符串 | L+1 字节,在此 L< 2^8 |
text | 小的非二进制字符串 | L+2 字节,在此 L< 2^16 |
mediumtext | 中等大小的非二进制字符串 | L+3 字节,在此 L< 2^24 |
longtext | 大的非二进制字符串 | L+4 字节,在此 L< 2^32 |
enum | 枚举类型,只能有一个枚举字符串值 | 1 或 2 字节,取决于枚举值的数目(最大值为 65535) |
set | 一个设置,字符串对象可以有零个或多个 set 成员 | 1、2、3、4、8 字节,取决于集合成员的数量(最多为 64 个成员) |
3.5 二进制字符串类型
MySQL 中的二进制字符串类型说明
类型名称 | 说明 | 存储要求 |
---|---|---|
bit(M) | 位字段类型 | 大约 (M+7)/8 字节 |
binary(M) | 固定长度二进制字符串 | M 字节 |
varbinary(M) | 可变长度二进制字符串 | M+1 字节 |
tinyblob(M) | 非常小的 blob | L+1 字节,在此 L< 2^8 |
blob(M) | 小 blob | L+2 字节,在此 L< 2^16 |
mediumblob(M) | 中等大小的 blob | L+3 字节,在此 L< 2^24 |
longblob(M) | 非常大的 blob | L+4 字节,在此 L< 2^32 |
四、运算符
搬运自:菜鸟教程 MySQL 运算符
4.1 算术运算符
在除法运算和模运算中,如果除数为0,将是非法除数,返回结果为 NULL。
4.2 比较运算符
SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符,可以判断表中的哪些记录是符合条件的。比较结果为真,则返回 1,为假则返回 0,比较结果不确定则返回 NULL。
4.3 逻辑运算符
逻辑运算符用来判断表达式的真假。如果表达式是真,结果返回 1。如果表达式是假,结果返回 0。
运算符号 | 作用 |
---|---|
not 或 ! | 逻辑非 |
and 或 && | 逻辑与 |
`or 或 | |
xor | 逻辑异或 |
4.4 位运算
位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数,进行位运算。然后再将计算结果从二进制数变回十进制数。
更多推荐
MySQL 8——学习笔记01(安装、数据库/数据表的操作 [DDL语句]、数据类型、运算符)
发布评论