数据库笔记（持续更新中）

1.数据库的介绍

安装在操作系统之上的数据管理软件，存储和管理数据

1.1数据库的分类

• 关系型数据库（SQL）

  MySQL、Oracle、SQL Server、DB2、SQLite

  通过表和表之间的、行和列之间的关系进行数据的存储

• 非关系型数据库（Not Only SQL）

  Redis、MongoDB

  以对象方式存储，通过对象自身的属性来决定，如定位等需要动态更新的信息

1.2数据库管理系统（DBMS）

科学有效的管理数据、维护和获取数据；应用通过数据库管理系统操作数据库中的数据，如mysql

DDL ->数据库定义语言

DML ->数据库操作语言

DQL ->数据库查询语言

DCL ->数据库控制语言

2.数据库的使用

操作数据库->操作数据库中的表->操作数据库中表的数据 mysql中的关键字不区分大小写

2.1操作数据库

1.创建数据库

CREATE DATABASE IF NOT EXISTS `test`;

2.删除数据库

DROP DATABASE IF EXISTS `test`;

3.使用数据库

USE `CRITICALWORD`;  --如果标名或字段名为关键词需要用``包住

SELECT `name` FROM `student`;

4.查看数据库

SHOW DATABASES;  --查看所有的数据库

2.2数据库的数据类型

1.数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 3个字节
• int 标准的省属 4个字节（常用）
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节
• decimal 字符串形式的浮点数 金融计算的时候

2.字符串

• char 字符串固定的大小 0~255
• varchar 可变字符串 0~65535 常用的String
• tinytext 卫星文本 2^8-1
• text 文本串 2^16-1 保存大文本

3.时间日期

• date YYYY-MM-DD 日期格式
• time HH:MM:ss 时间格式
• datetime YYYY-MM-DD HH:MM:SS 最常用的时间格式
• timestamp时间戳， 1970.1.1到现在的毫秒数
• year年份表示

4.null

• 没有值、未知
• 不要使用null进行运算

2.3数据库的字段属性

1.unsigned

• 无符号整数，声明该列不能为负数

2.zerofill

• 不足的位数用0填充

3.自增（autoimplement）

• 自动在上一条记录的基础上加一，通常用来设计唯一的主键index，必须为整数

2.4创建数据库和一些规范

/*创建一个school数据库
--创建学生表（列、字段）使用SQL创建
--学号int、登录密码varchar（20）姓名varchar（20）性别varchar（2），出生日期（datetime）、家庭住址、email

--注意使用英文()、表的名称、字段尽量用``括起来
--AUTO INCREMENT 自增
--字符串用''括起来，所有语句后面加',',最后一个不加*/

CREATE TABLE IF NOT EXISTS `student` (
		`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
		`name` VARCHAR(30) NOT NULL DEFAULT('匿名') COMMENT '姓名',
		`password` VARCHAR(20) NOT NULL DEFAULT'123456' COMMENT '密码',
		`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
		`birthday` DATE DEFAULT NULL COMMENT '出生日期',
		`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
		`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
		PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
/*格式
CREATE TABLE [IF NOT EXISTS] `表名`(
	`字段名` 列属性 [属性] [属性] [属性] [注释]，
	`字段名` 列属性 [属性] [属性] [属性] [注释]，
	···
	`字段名` 列属性 [属性] [属性] [属性] [注释]
)[表类型][字符集设置][注释]

*/

/*每个表都必须存在以下五个字段，表示一个记录存在的意义
ID  --主键
`VERSION`  --乐观锁
id_delete  --伪删除
gmt_create  --记录的创建时间
gmt_update  --修改时间*/

2.5常用命令

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

2.6数据库引擎

• InnoDB: mysql默认使用、 安全性高，事务的处理，多表多用户操作
• MyISAM: 早些年使用的、 节约空间，速度较快

物理空间存在的位置

所有的数据库文件都存在data目录下，本质还是文件的存储，有特定的格式，一个文件夹对应一个数据库

MySQL引擎在物理文件上的区别

• InnoDB在数据库表中只有*.frm文件，以及上级目录下的ib_data文件
• MYISAM对应的文件
  • *.frm--表结构的定义文件
  • *.MYD--数据文件
  • *.MYI--索引文件

设置数据库的字符集编码

MySQL默认编码为Latin1，不支持中文

CHARTSET = utf8

2.7SQL语句对的增删改

--ALTER TABLE [表名] RENAME AS [新表名]
ALTER TABLE teacher RENAME AS teacher1  --修改表名
--ALTER TABLE [表名] ADD [字段名] [列属性]
ALTER TABLE teacher1 ADD age INT(11)  --添加表的字段
--ALTER TABLE [表名] MODIFY [属性名] [新的属性约束]
ALTER TABLE teacher1 MODIFY age VARCHAR(11)  --修改约束
--ALTER TABLE [表名] CHANGE [旧属性名] [新属性名]
ALTER TABLE teacher1 CHANGE age age1  --表中属性重命名
--ALTER TABLE [表名] DROP[删除的属性名]
ALTER TABLE teacher1 DROP age1  --删除表中的属性
--DROP TABLE [表名] IF EXISTS [表名]
DROP TABLE IF EXISTS teacher1  --删除表

3.数据管理(DML语言)

3.1外键

阿里的JAVA规范：【强制】不得使用外键与级联，一切外键概念必须在应用层解决

创建表的时候没有外键关系

ALTER TABLE `[表名]`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)

--ALTER TABLE [表名] ADD CONSTRAINT `FK_[需要添加外键的属性名]` FOREIGN KEY(`[需要添加外键的属性名]`) REFERENCES `[作为约束添加的所在表名]`(`[作为约束添加的所在属性]`)

最佳实践：

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
• 若要使用外键需要在程序层面实现，否则做DELETE或UPDATE都必须考虑外键因素，开发、测试极不方便

3.2添加

• INSERT

INSERT INTO [表名]([字段1,字段2,字段3]) VALUES('值1','值2','值3')  --多字段对多个值
INSERT INTO [表名]([字段]) VALUES('值1'),('值2')  --一个字段插入多个值

字段是可以省略的，但是后面的值必须要一一对应，不能少；可以同时插入多条数据

3.3修改

• UPDATE

--UPDATE [表名] SET [属性1] = '值1',[属性2] = '值2' WHERE [其他属性] = 值3;
UPDATE `student` SET `name` = 'ygw' WHERE `id` = '1';  --如果不指定条件会改变所有的内容

3.4删除(DELETE、TRUNCATE)

都能删除数据，都不会删除数据结构

• DELETE

不会影响自增，删除后认为原来的自增数

重启数据库后的现象：

1. InnoDB使用DELETE后，自增列会从1开始（存在内存当中断电即消失）
2. MyISAM继续从上一个自增量开始（存在文件中的，不会丢失）

--DELETE FROM [表名] WHERE [属性];
DELETE FROM `student` WHERE id = 1;

• TRUNCATE

完全清空一个数据库表，表的结构和索引约束不会变，

--TRUNCATE [表名];
TRUNCATE `student`;

TRUNCATE重新设置自增列，计数器会归零、TRUNCATE不会影响事务