数据库知识点总结pdf(数据库知识总结)

数据库 SQL结构化查询语言

Structured Query Language

RDBMS 指关系型数据库管理系统，全称 Relational Database Management System

1，认识DB数据库

前端：（页面：展示，数据！）

后端：（连接数据库JDBC，连接前端（控制跳转，给前端传数据））

数据库：存数据

程序员三大阶段

写代码，学好数据库操作系统，数据结构与算法离散，数电，…

关系型数据库：（SQL）

MySQL, Oracle, Sql Serve, DB2,SQLlite

表与表之间的联系，行列关系

非关系型数据库：（NoSQL）not only SQL

Redis,MongDB

对象存储，

DBMS(数据库管理软件)

MySQL关系型数据库管理系统5.7比较稳定尽可能使用压缩包安装，不要用exe 2，mysql操作启动：

net start mysql

mysql -u root -p

关闭：

exit

net stop mysql

修改密码

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
刷新权限
flush privileges;

安装详见：https://mp.weixin.qq.com/s/E1PM4EHwU6Joot4OG0gDjw

安装SQLyog

可视化软件

下载链接

https://sqlyog.en.softonic.com/

MySQL基本命令行语句

mysql -u root -p123457 --链接数据库（--+注释）


-------------
语句使用:结尾
show databases: --查看所有的数据库
use school --切换数据库
show tables; --查看数据库所有的表
describe student; --查看表所欲数据
create database westos; -- 创建一个数据库
exit; --推出链接
--单行注释

数据库XXX语言 CRUD增删改查

DDL 定义

DML 操作

DQL 查询

DCL 控制

操作数据库（了解）

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

不区分大小写

创建数据库

create database if not exists mingzi

删除数据库

drop database [if exists] mingzi;

使用数据库

--tab上边的·特殊关键字·
show `user`;

查看数据库

show databases --查看所有数据库

数据库的列类型

数值

tinyint 1字节smallint 2字节mediumint 3字节int 标准的整数 4个字节bigint 8字节float 浮点数 4字节double 浮点数 8字节decimal 字符串形式的浮点数，金融计算时候，一般使用decimal

字符串

char 字符串固定的大小2—255varchar 可变字符串0—65535 常用stringtinytext 微型文本2^8-1text 文本串 2^16-1 保存大文本

时间日期

java.util.Date

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

null

没有值，未知不使用NULL进行运算，结果为NULL 数据库的字段类型（重要）

Unsigned

无符号的整数不能为负数

zerodill

0填充不足的位数用0填充

自增

自动在上一条记录上+1通常使用设计唯一的主键-index ，必须是整数类型自定义设计主键自增的起始值和步长

非空

假设设置为not null ,如果不赋值，就会报错Null，如果不填写值，默认是null

默认

设置默认的值sex,默认值为男，不指定该列的值，填充默认值

id  主键
·version·  乐观锁
is_delete  伪删除
gmt_create 创建时间
gmt_update x

创建数据库表

--创建一个school数据库
--创建学生表，用sql创建
-- 学号int 密码varchar 姓名,性别varchar 出生日期，家庭住址，Email

--用英文符号() .``
--字符串用单引号
语句后加英文 ,最后一个不用加
CREATE TABLE IF NOT EXISTS `student`(
 `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
 `name` VARCHAr(30) 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(100) DEFAULT NULL COMMENT '家庭住址',
 `email` VARCHAr(50) DEFAULT NULL COMMENT '邮箱', 
 PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式

CREAT TABLE  [IF NOT EXISTS] `表名`(
	`字段名` 列类型 [属性] [索引] [注释],
	`字段名` 列类型 [属性] [索引] [注释],
	...
	`字段名` 列类型 [属性] [索引] [注释],
)[表类型][字符集设置][注释]

常用命令

SHOW CREATE DATAbase school --查看数据库的定义语句
SHOW CREATE TABLE student --查看数据表的定义语句
DESC student --显示表的结构

数据表类型

--关于数据库引擎

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

常规使用操作：

MYISAM节约空间，速度快INNODB安全性高，事务处理，多表多用户操作

物理空间存在位置

所有数据库文件在data目录下

本质还是文件存储

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

innoDB在数据库表中只有一个*.frm文件，以及上级目录的ibdata1文件MYISAM对应文件

*。frm 表结构的定义文件*.MYD 数据文件（data）*.MYI 索引文件（index）

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

CHARSET=utf8

不设置的话就是mysql默认的字符集编码（不支持中文）

MySQL的默认编码是Latin1，不支持中文，
打开my.ini以后，在[client]和[mysqld]下面均加上default-character-set=utf8
https://www.cnblogs.com/sailleaf/p/4704261.html
在my.ini配置默认编码
character-set-server=utf8

修改删除表

--修改表名
ALTER TABLE student RENAME AS student1
--增加表的字段
ALTER TABLE student1 ADD age INT(11)
--修改表的字段（重命名，修改约束）
ALTER TABLE student1 MODIFY age VARCHAr(11)--修改约束
ALTER TABLE student1 CHANGE age age1 INT(1)--段重命名
--除表的字段
ALTER TABLE student1 DROP age1

--删除表(如果存在再删除)
DROP TABLE IF EXISTS 表名

创建和删除尽量都是用判断IF EXISTS

3,MySQL数据管理 3.0，基础补充

InnoDB，是MySQL的数据库引擎之一，现为MySQL的默认存储引擎，为[MySQL AB](https://baike.baidu.com/item/MySQL AB)发布binary的标准之一。InnoDB由Innobase Oy公司所开发，2006年五月时由甲骨文公司并购。与传统的ISAM与MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务 3.1，外键（了解）

改变表，外部键啥都有

数据库级别的外键，不用

数据库纯粹论

单纯的表，只用来存（行）数据，（列）字段如果想实现多张表的数据，使用外键（程序实现） 3.2DML语言

数据库意义：数据存储，数据管理

DML语言：数据操作语言

Insertupdatedelete 3.3 添加

--入语句
--insert INTO ([字段一,字段二,字段三])VALUES('值1')('值2'),('值3'),....
INSERT INTO `grade`(`gradename`) VALUES('大四')--设置的有自增
INSERT INTO `grade`(`gradeid`,`gradename`) VALUES('3','大四')--一一对应
--插入多个字段
INSERT INTO `grade`(`gradename`)VALUES('大二'),('大一')

3.4 修改

--修改学员名字
UPDATE `student1` SET `name`='景之' WHERe id = 1;
 
 --修改多个，逗号隔开
UPDATE `student1` SET `name`='景之',`email`='wang' WHERe id = 1;
 
-不指定条件改变所有
UPDATE `student1` SET `name`='景之';

条件：where子句 id等于，大于，…区间内修改

BETWEEN 2 AND 5(闭合区间)

AND

多条件定位语句，条件无上限

也可以通过修改表的数据修改而增加一列

UPDATE `student` SET `birthday`=`CURRENT_TIME WHERe` `name`='哈哈哈' AND sex='女'

3.5 删除

--删除指 定数据
DELETE FROM `student1` WHERe id = 1;

TRUNCATE 命令

作用：完全清空一个数据库表，表的结构和索引约束不会边！

--清空—表
TRUNCATE student1

delete和truncate区别

truncate 重新设置自增列，计数器会归零

truncate不会影响事物

--测试delete和truncate的区别
CREATE TABLE `test`(
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	`coll` VARCHAr(20) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `test`(`coll`) VALUES('1'),('23'),('34')

DELETE FROM `test` --不会影响自增 `school`

TRUNCATE `test` --自增会清零

4.DQL查询数据（重重点） 4.1DQL

data query language:查询语言

select

4.2 指定查询字段

--查询所有学生

SELECT * FROM student
--查询指定字段
SELECt `studentno` , `studentname` FROM student

--别名
SELECt `studentno` AS 学号 , `studentname` AS 学生姓名 FROM student AS s

--函数 CONCAt(a,b)
SELECt CONCAt('姓名:' , StudentName) AS 新名字 FROM student

列名字看不懂时，可以起别名

去重，distinct

作用：去除select查询出来的重复数据，重复数据只显示一条

--查询那些同学参加了考试，成绩
SELECT * FROM result 

--查询那些同学参加了考试
SELECt `StudentNo` FROM result

--去重
SELECt DISTINCT `StudentNo` FROM result

数据库的列（表达式）

--查询系统版本号(函数)
SELECt VERSION()
--计算结果（表达式）
SELECT 100*7-33 AS 计算结果
--查询自增步长（变量）
SELECT @@auto_increment_increment

--学员考试成绩+1
SELECT `StudentNo`, `StudentResult`+1 AS '提分后' FROM result

数据库的表达式，文本值，列，变量，null，函数，计算表达式

4.3 where 条件子句

作用：检索数据中符合条件的值

搜索的条件由一个或多个表达式组成，结果为布尔值

逻辑运算符

运算符	语法	描述
and &&	a and b a && b	逻辑与
or \|\|	a or b a \|\| b	逻辑或
Not !	not a ! a	逻辑非

尽量使用英文字母

--where
SELECt studentNo,`StudentResult` FROM result

--查询分数在95到100的
SELECt studentNo,`StudentResult` FROM result
WHERe StudentResult>=95 AND StudentResult<=100

--&& 
SELECt studentNo,`StudentResult` FROM result
WHERe StudentResult>=95 && StudentResult<=100

--模糊查询（区间）
SELECt studentNo,`StudentResult` FROM result
WHERe StudentResult BETWEEN 50 AND 100

--除了学号为1000的其他学生
SELECt studentNo,`StudentResult` FROM result
WHERe StudentNo!=1000;

--!= NOT 
SELECt studentNo,`StudentResult` FROM result
WHERe  NOT StudentNo = 1000;

模糊查询：比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为NULL,结果为真
IS NOT NULL	a is not null	如果操作符不为NULL,结果为真
BETWEEN	a between b and c	若a在b和c之间，则结果为真
Like	a like b	SQL匹配，如果a 匹配b，则结果为真
in	a in (a1,a2,a3…)	假设a在a1，或者a2…其中的某一个值中，结果为真

--in（具体的一个或者多个值）
--查询，1001，1000号同学
SELECt `StudentNo` ,`StudentName` FROM `student`
WHERe StudentNo IN (1000,1001);

--查询在北京的同学
SELECt `StudentNo` ,`StudentName` FROM `student`
WHERe `Address` IN ('北京海淀');

--null NOT NULL
--查询地址为空的学生 NULL
SELECt `StudentNo` ,`StudentName` FROM `student`
WHERe address='' OR address IS NULL

--查询有出生日期的学生
SELECt `StudentNo` ,`StudentName` FROM `student`
WHERe `borndate` IS NOT NULL

--查询没有出生日期的学生
SELECt `StudentNo` ,`StudentName` FROM `student`
WHERe `borndate` IS NULL

4.4 连表查询

--查询参加了考试的同学，（学号，姓名，科目编号，分数）
SELECt * FROM student
SELECt * FROM result



SELECt s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
WHERe s.studentNo = r.studentNo

--Right JOIN
SELECt s.studentNo,studentName,SubjectNo,StudentResult
FROM student s 
RIGHT JOIN result r
ON s.studentNo = r.studentNo

--Left JOIN
SELECt s.studentNo,studentName,SubjectNo,StudentResult
FROM student s 
LEFT JOIN result r
ON s.studentNo = r.studentNo

操作	描述
inner join	如果表中有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

自链接及连表查询联系

自链接（了解）

自己的表和自己的表链接核心：一张表拆成两张表

父类

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps设计

查询父类对应子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

4.5 分页和排序

升序ASC 降序DESC

order by 通多那个字段排序

分页

缓冲压力，体验更好，现在是瀑布流

-- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
SELECt s.studentno studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERe subjectname='Java程序设计-1'
--order by 名字 方式（ASC DESC）排序方式
ORDER BY studentresult DESC
--LIMIT起分页效果
LIMIT 0,2

子查询

--子查询（查询数据结构-1的所有考试结果）学号，科目编号，成绩，降序
--方法一，连接查询
SELECt studentno,r.subjectno,studentresult
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERe subjectname = '数据结构-1'
ORDER BY studentresult DESC;

--方法二，比较简洁吧
SELECt studentno,r.subjectno,studentresult
FROM result r
WHERe subjectno = (
	SELECt subjectno FROM `subject`
	WHERe subjectname = '数据结构-1'
)
ORDER BY studentresult DESC;

5.MySQL函数常用函数数据函数

现在是8.0的文档了

https://dev.mysql.com/doc/refman/8.0/en/

SELECt ABS(-9);--绝对值
SELECT CEILING(9.4);--向上取整
SELECT FLOOR(7.7);--向下取整
SELECT RAND();--随机数，0-1之间
SELECT SIGN(7);--负数返回-1，正数返回1，0返回0

字符串函数

SELECT CHAR_LENGTH('王景玉大帅哥，子杰太牛逼，佳鹏很能卷，我要平复心情')；--字符串长度
SELECT CONCAt('我'，'最帅呀');--合并字符串
SELECT INSERT('我爱柳暗花明又一村',3,9,'少年行不终，景自到');--替换，从第几个，替换n个
SELECT LOWER ('WANGJINGYU');--小写
SELECT UPPER ('wangjingyu');--大写
SELECT RIGHt('wodes,hijieshipiaoliangme,ilide',5);--从右边往左边截取n个
SELECT LEFt('wodes,hijieshipiaoliangme,ilide',5);--从左边往右边截取n个
SELECT REPLACE('景之说效率能成功','效率','效率加努力');--替换，后换前
SELECT SUBSTr('景之说效率能成功',4,6);-- 从第几个开始截取长度为n个
SELECT REVERSe('景之说效率能成功');--反转字符串

--查询姓文的同学，改成景
SELECT REPLACE(studentname,'文','景') AS 新名字
FROM student WHERe studentname LIKE '文%';

日期和时间函数

--日期和时间函数
SELECt CURRENT_DATE();--当前日期
SELECT CURDATE();--获取当前日期
SELECT NOW();--获取当前日期和时间
SELECT LOCALTIME();--获取当前日期和时间
SELECT SYSDATE();--获取当前日期和时间

--年月日，时分秒
SELECT YEAR(NOW());--年份
SELECT MonTH(NOW());--月份
SELECT DAY(NOW());--日份
SELECT HOUR(NOW());--时
SELECT MINUTE(NOW());--分
SELECT SECOND(NOW());--秒

系统函数

--系统信息函数
SELECT VERSION();--版本
SELECT USER();--用户

聚合函数

函数	描述
COUNT()	统计出现满足条件的个数
SUM（）	求和（表达式，字段）
AVG（）	平均值（表达式，字段）
MAX()	最大值
MIN（）	最小值

--聚合函数
SELECT COUNT(studentname)FROM student;
SELECt COUNT(*)FROM student;
SELECt COUNT(1) FROM student;
--含义上，count(1)和count(*)都表示对全部数据查询
--count(字段) 会统计，该字段在表中出现的次数，忽略字段为null的
--count(1)和count(*)速度都差不多（对全表扫描），但是count(字段)比较快，只对该字段扫描



SELECt SUM(studentresult)AS 总和 FROM result;
SELECt AVG(studentresult)AS 平均分 FROM result;
SELECt MAX(studentresult)AS 最高分 FROM result;
SELECt MIN(studentresult)AS 最低分 FROM result;

分组过滤

--查询不同课程的最高分，最低分
--根据不同的课程分组

SELECt subjectname,AVG(studentresult) AS 平均分,MAX(studentresult) AS 最高分,
MIN(studentresult) AS 最低分
FROM result AS r
INNER JOIN `subject` AS s
ON r.subjectno = s.subjectno
GROUP BY r.subjectno
HAVINg 平均分>80;
--having 也是一种筛选，是从前面筛选的字段再筛选

MD5加密

--实现数据加密
CREATE TABLE `testmd5`(
 `id` INT (4) NOT NULL,
 `name` VARCHAr(20) NOT NULL,
 `pwd` VARCHAr(50) NOT NULL,
 PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

 INSERT INTO testmd5 VALUES(1,'kuangshen','123456'),(2,'qinjiang','456789')

--d对pwd加密
UPDATe testmd5 SET pwd = MD5(pwd);
--单独对某个用户加密,不过是加了一个where条件而已，懂得都懂
UPDATE testmd5 SET pwd = MD5(pwd) WHERe NAME = 'wangjingyu';
--插入新的数据自动加密
INSERT INTO testmd5 VALUES(4,'jingzhi',MD5('1234'));
--查询登录用户信息（md5对比使用，看用户输入加密后的密码对比）说实话，这句话没看懂，
--感觉就是普通的查询加了个条件而已
SELECT * FROM testmd5 WHERe `name` = 'jingzhi' AND pwd=MD5('1234');

6.事务和索引

事务：同时成功，同时失败

将一组SQL语句放在同一批次内去执行

如果一个SQL语句出错，则该批次内所有SQL都将被取消执行

MySQL事务处理只支持InnoDB和BDB数据表类型

事务原则：ACID原则，原子性，一致性，隔离性，持久性（脏读，幻读）

（理解中。。）

参考来源：kuangshen

数据库知识点总结pdf(数据库知识总结)

Java相关栏目本月热门文章