数据库基础学习（二）

数据库基础知识

顺序：

操作数据库>操作数据库中的表>操作数据库中表的数据

数据库操作了解

注意： 假如表中，表明字段名存在特殊字符需要添加（``）来区别。

创建数据库
```
CREATE DATABASE mydd
```

删除数据库

DROP DATABASE IF EXISTS mydd    -- IF EXISTS 可要可不要

使用数据库

USE `school`  -- 跳转到指定的数据库

查看数据库

SHOW DATABASES  -- 查看所有的数据库
SELECT `age` FROM student  -- 查询student表中的age数据
SELECT * FROM `school`.`student`  -- 在其他数据库中跳转到school数据库下的student表

数据库列的类型

文本类

数据类型	描述
CHAR(size)	保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size)	保存可变长度的字符串（可包含字母、数字以及特殊字符）。MySQL4.1以前，VARCHAR数据类型所支持的最大长度255，5.0以上版本支持65535字节长度，utf8编码下最多支持21843个字符(不为空)
TINYTEXT	存放最大长度为 255 个字符的字符串。
TEXT	存放最大长度为 65,535 个字符的字符串。
BLOB	用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。
MEDIUMTEXT	存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。
LONGTEXT	存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.)	允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。注释：这些值是按照你输入的顺序存储的。可以按照此格式输入可能的值：ENUM('X','Y','Z')
SET	与 ENUM 类似，SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。

数字类

数据类型	描述
TINYINT(size)	占1字节
SMALLINT(size)	占2字节
MEDIUMINT(size)	中等大小，占3个字节
INT(size)	标准的整数，占4个字节
BIGINT(size)	大于int，占8个字节
FLOAT(size,d)	带有浮动小数点的小数字。单精度浮点型，占4个字节
DOUBLE(size,d)	带有浮动小数点的大数字。双精度浮点型，占8个字节
DECIMAL(size,d)	作为字符串存储的 DOUBLE 类型，允许固定的小数点。（精度不会丢失）

这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数

日期/时间类

数据类型	描述
DATE()	日期。格式：YYYY-MM-DD 注释：支持的范围是从 '1000-01-01' 到 '9999-12-31'
TIME()	时间。格式：HH:MM:SS 注释：支持的范围是从 '-838:59:59' 到 '838:59:59'
DATETIME()	日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'
TIMESTAMP()	时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的描述来存储。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC
YEAR()	2 位或 4 位格式的年。注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。

即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

NULL
1. 没有值，未知的。
2. 不要使用NULL进行运算（可以运算但没有必要），结果一定为NULL。

数据库的字段属性

Unsigned： 无符号的整数，声明了该类不能声明为负数。

Zerofill： 不足的位数，使用0来填充。例如：定义一个长度为2的int类，输入3会默认填充03。

自增（AUTO_INCREMENT）：

通常理解为自动在上一条记录的基础上+1（默认）。
通常用来设计唯一的主键，必须是整数类型。
可以自定义设计主键自增的起始值和步长。

非空（NULL not null）：

假如勾选非空，如果不给值，程序将会报错。
不勾选非空，并且不填写值，默认为NULL。

默认： 设置的默认值，如果不指定该列的值，设置的默认值将会自动填充。

拓展（以后做项目必备）：

字段	称呼
id	主键
version	乐观锁
is_delete	伪删除
gmt_create	创建时间
gmt_update	修改时间

创建数据的表

创建形式：

CREATE TABLE IF NOT `表名` ( -- IF NOT 可写可不写，建议加上
	`字段名` 列类型(size)  [属性] [索引] [注释]
PRIMARY KEY (`主键，字段名`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

常用指令：

SHOW CREATE DATABASE 数据库名 -- 查看创建数据库的语句
SHOW CREATE TABLE 表名  -- 查看创建数据表的定义语句
DESC 表名  -- 显示表的结构

案例：创建一个表，里面保存了学生的学号id，名字name，密码pwd，以及地址address，性别sex，生日birthday和邮件email。

CREATE TABLE IF NOT EXISTS `school` (
	`id` BIGINT (100) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR (10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR (20) NOT NULL DEFAULT '123456..' COMMENT '密码',
	`sex` VARCHAR (2) NOT NULL DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR (200) DEFAULT NULL COMMENT '地址', 
	`email` VARCHAR (100) DEFAULT NULL COMMENT '电子邮件',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

数据表的类型

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为两倍

常规使用操作：

MYISAM：节约空间，速度较快。
INNODAB：安全性高，事务的处理，多表多用户操作。

在物理空间存在的区别

左右的数据库文件都存在data目录下，本质还是文件储存

INNODB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1。
MYISAM对应文件
- *.frm 表结构的定义文件。
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）

设置数据库表的字符集编码

CHARSET=utf8

注意：不设置，MySQL将会使用默认的字符集（不支持中文）

解决方法：

方法一：在创建表的后面写下代码ENGINE=INNODB DEFAULT CHARSET=utf8
```
CREATE TABLE IF NOT EXISTS `school` (

)ENGINE=INNODB DEFAULT CHARSET=utf8
```
方法二：在my.ini文件里面输入代码character-set-sever=utf8（此方法不建议使用，因为每个人使用你的数据库不一定会自己写入）
```
[mysqld]
basedir=D:\Exploit\mysql-5.7.33\   
datadir=D:\Exploit\mysql-5.7.33\data\
port=3306
character-set-sever=utf8
```

操作数据库

数据库的基本操作

修改数据表名：

ALTER TABLE `旧表名` RENAME AS `新表名`

增加数据库表字段：

ALTER TABLE `表名` ADD `增加新的字段名` 参数类型(size)

修改表：

修改约束

ALTER TABLE `表名` MODIFY `字段名` 字段类型(size) -- 修改约束，即修改原来字段的的字段类型以及大小

字段重名

ALTER TABLE `表名` CHANGE `旧字段名`  `新字段名` 字段类型(size) -- 便于修改字段名

拓展： modify和change都可以修改字段类型，但是change却需要写两次列名，不是很方便。但是change的优点是可以修改列名称，modify则不能

删除表：

删除表的字段

ALTER TABLE `表名` DROP `字段名` -- 删除表中其中的一个字段

删除表

DROP TABLE IF EXISTS `表名` -- 删除表，尽量加上IF EXISTS判断

消除重复：

关键字DISTINCT

语法实现

SELECT DISTINCT `字段名` FROM `表名`

注意： 当查询多个字段时，多个字段看作一个组合，组合中的所有字段一模一样才会被视为重复

WHERE过滤记录

语法

select 列名 from 表名 where 列名 运算符 条件值;

案例
```
SELECT  *  FROM `city` WHERE CountryCode = 'PSE';
```
CountryCode = 'PSE' 即是本例中的查询条件。从 city 表中查询出所有的国家代号为 PSE 的城市信息

运算符

在 MySQL 中，主要有以下几种运算符。

比较运算符
逻辑运算符
位运算符
算术运算符

比较运算符

运算符	作用
=	等于
<> 或 !=	不等于
<	小于
>	大于
<=	小于或等于
>=	大于或等于
<=>	两边为 NULL 返回值 1，一个为 NULL 返回值 0
BETWEEN min AND max	在 min 和 max 的值之间，包含 min 和 max
IN(value1,value2,…)	在集合(value1,value2,…)中
IS NULL	为空
IS NOT NULL	不为空
LIKE	模糊匹配
REGEXP 或 RLIKE	正则表达式匹配

between：

SELECT * FROM `city` WHERE `id` BETWEEN 1 AND 10;

筛选id从1到10的数据

等于/不等于：

等于
```
SELECT `id` FROM `city` WHERE id = 1;
```

只寻找id为1的字段

不等于
```
SELECT `id` FROM `city` WHERE id != 1;
```
查询除了id为1的字段所有id

位运算符

运算符	作用
&	按位与
\|	按位或
^	按位异或
!	取反
<<	左移
>>	右移

位运算是以二进制作为基础的运算，所以做位运算之前，会先将操作数变成二进制数，然后进行位运算，计算的结果再从二进制变为十进制输出。

按位与
```
select 2 & 3;
```
按位或
```
select 2 | 3;
```
按位异或
```
select 2 ^ 3;
```
按位取反
```
select ~3;
```
按位右移
```
select 5>>2;
```
按位左移
```
select 5 << 1;
```

MySQL数据管理

外键

理解：另一个表的主键

方法一：

创建表的时候，增加约束（麻烦，比较复杂）

创建一个student表在student表中增加一个gradeid（班级）约束，然后创建一个约束的表grade

增加student表的一个约束gradeid需要先创建约束的表grad

CREATE TABLE IF NOT EXISTS `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(100) NOT NULL COMMENT '年级名字',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

然后在创建student的表

CREATE TABLE IF NOT EXISTS `student` (
	`id` BIGINT (100) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR (10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR (20) NOT NULL DEFAULT '123456..' COMMENT '密码',
	`sex` VARCHAR (2) NOT NULL DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR (200) DEFAULT NULL COMMENT '地址', 
	`email` VARCHAR (100) DEFAULT NULL COMMENT '电子邮件',
	`gradeid` INT (10) NOT NULL COMMENT '班级',
	PRIMARY KEY (`id`),
	KEY `FK_gradeid` (`gradeid`),  -- 定义外键
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)  -- 添加约束。constraint（约束），references（引用）
)ENGINE=INNODB DEFAULT CHARSET=utf8

学生表的gradeid字段引用grade表的字段gradeid。定义外键key，然后添加约束references（引用）。

添加约束与可视化的对照

CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)

需要了解的问题： 删除有外键关系的两个表的顺序，必须先删除student（引用别人）表，才能删除grade（被引用）表，否则会出现图下显示的状态

方法二

将CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)放在外面添加，也就是grade表与student表同时创建互不相干。

CREATE TABLE IF NOT EXISTS `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(100) NOT NULL COMMENT '年级名字',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

CREATE TABLE IF NOT EXISTS `student` (
	`id` BIGINT (100) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR (10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR (20) NOT NULL DEFAULT '123456..' COMMENT '密码',
	`sex` VARCHAR (2) NOT NULL DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR (200) DEFAULT NULL COMMENT '地址', 
	`email` VARCHAR (100) DEFAULT NULL COMMENT '电子邮件',
	`gradeid` INT (10) NOT NULL COMMENT '班级',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

创建完之后输入一下的代码即可约束student表

ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)

以上操作均为物理外键，数据库级别的外键，不推荐使用，避免数据库过过多造成的困扰。

最佳使用方法：

数据库就是相当于office的Excel，用来存数据，只有行（数据）和列（字段）。
想使用多张表的数据，想使用外键（程序实现）。

DML语言（数据库操作语言）

现有一个student表，储存一个学生的信息

添加（insert）

图中储存数据一个学生的信息对应的添加代码就是

INSERT INTO `school`.`student` (`name`, `birthday`, `address`, `email`, `gradeid`) VALUES ('下', '2021-05-12 19:18:09', '湖北', '13123@gmail.com', '10');

还有一种写法就是不写字段名，添加的信息就是从左到右一一对应，当添加的数据多的时候此写法容易造成数据遗忘，且值必须一一对应

INSERT INTO `school`.`student` VALUES ('4','比','2384384','女','2021-05-12 19:18:09', '湖北', '13123@gmail.com', '30');

修改（update）

通过添加发现，该学生的名字(小红)，地址（四川），班级（1）打错了需要进行修改，对应的代码是

UPDATE `school`.`student` SET `name` = '小红' , `address` = '四川' , `gradeid` = '1' WHERE `id` = '1' ;

删除（delete）

学校收到通知，该学生大家斗殴被劝退，此时要删除该学生的信息，对应代码如下

DELETE FROM `school`.`student` WHERE `id` = '1' ;   -- 对应主键id，找到该行进行删除。