目录
基础概念
1,关系型数据库与非关系型数据库
关系型数据库:按照比较严格,比较整齐的结构来组织数据。可以针对数据的操作处理,提供更多的功能,和更多的校验能力
非关系型数据库:使用不太严格,松散的方式来组织数据,比如,按照键值对/文档。针对数据的处理操作,提供的功能更有限,校验能力也比较有限,效率会更高,比较适合“分布式系统”使用
2,客户端与服务器
MySQL:是一个"客户端-服务器"的结构程序
客户端和服务器,是两个独立的程序,两个程序通过“网络”进行通信。主动发起网络通信的一方称为“客户端”,被动接受网络通信的一方,称为“客户端”
请求/响应:客户端给服务器发的数据就是请求。服务器给客户端发的数据就叫响应
服务器的特点:
(1)被动接受请求,返回响应
(2)服务器往往是要给多个客户端提供服务,而不是只给一个(也有少数情况下只给少数,或者几个或一个固定的客户端提供服务)
分布式系统中,就会存在服务器之间相互调用的情况,比如,服务器A请求服务器B这个过程中,A就相当于B的客户端了,此时B就是A的专属服务器了
服务器是本体(配置要求比较高):数据都是由服务器管理和储存的
客户端只是一个个用户交互的界面
3,硬盘和内存
一台电脑:
(1)CPU(中央处理器):可以进行各种各样的算术运算和逻辑判断
(2)内存:(3)硬盘:计算机储存数据主要靠这两个部分
(4)电源(5)主板(6)散热器
(7)显卡:专用计算芯片GPU,只能进行特定的运算和逻辑
(8)机箱
数据库:像MySQL,Oracle这些,都是把数据存储到硬盘上
内存:存储空间小,访问速度快,成本更高,数据易失(掉电之后,数据就会消失)
硬盘:存储空间大,访问速度慢,成本更低,持久储存(掉电之后,数据仍然存在)
硬盘和内存的速度,要相差几千倍
机械硬盘,固态硬盘(比机械硬盘快很多)
对于数据库来说,肯定希望持久储存,数据存储空间比较大,希望成本更低,所以数据库选择使用硬盘来储存(同时意味着,访问操作比较慢)
软盘:U盘:光盘:硬盘:统称为外存
4,弱类型系统/强类型系统
比较支持隐式类型转换的称为“弱类型系统”;不太支持隐式类型转换称为“强类型系统”
动态类型:程序运行过程中,变量类型可以改变,优势:非常灵活,表达能力强。缺点:程序运行之前,难以做很复杂的检查,很多代码只有运行的时候才能发现
静态类型:程序运行过程中,变量不可以改变,产品具有一定规模,代码和业务也越来越复杂时,使用
第一组:“数据库”操作(SQL):
(注意:这里的“数据库”是指数据库软件上,组织数据的“数据集合”,而不是指之前的数据库软件,像像MySQL,Oracle这些)
(1)查看当前有哪些数据库:
show databases;
(2)创建数据库:
(数据库的名字要保持唯一性,且不能与SQL中的关键字重复,但是如果想让关键字成为数据库名,给数据库名加上"反引号": ` 名称 `)
create database+名字:
a,需要指定数据库的“字符集”
主流的编码方式:
(a)GBK
主要在中国大陆使用(简体)Windows简体中文的默认编码方式,汉字是2个字节表示,之前使用的vs编写代码,打印出来的汉字长度就是2个字节
(b)UTF-8
属于变长编码,表示不同的字符,可以用1-4个字节表示,对于汉字来说一般是三个字节,是当今世界上最流行的编码方式,不仅能表示中文,也能表示世界上任何一种语言。MySQL的utf8,是不完全体,是个残体,有些字符不支持。但是MySQL提供了一个方案utf8mb4,这个是utf8的完全体,但这个是MySQL独有的。
create database+名字 charset utf8;
b,指定一个简单的条件
(如果不存在就创建,存在就啥也不做但会弹出警告)用于批量执行
create database if not exists +名字:
查看警告:show warnings;
c,字符约束collate:
描述了字符串之间的比较规则
(3)选中数据库:
use +数据库名;
代表切换成功
(4)删除数据库
drop database+数据库名;
删除数据库是一个非常危险的的操作,一定要慎重!!!一旦删除就恢复不了
1,控制权限,不是每个人都能操作,只有少数人才能操作(MySQL也有权限机制)
2,要对数据库进行及时的备份,拷贝出来,存储到别的地方(数据备份,一二三原则:一份数据,至少需要保存到两个机器上,至少有三个副本)
3,确实需要进行删库的操作的时候,最好拉上一个人,和你一起操作,你操作,他在旁边,他在旁边给你看着(僚机)
第二组:数据表的操作
常用数据类型(MySQL中):
数字:
虽然TINYINT和SMALLINT更节省空间,但是更推荐使用INT,BIGINT,硬件设备成本越来越低,但相比之下,程序员的开发成本(时间)更加尖锐。如果TINYINT和SMALLINT用出bug,造成的损失远远不是几个硬盘能比的。
FLOUT(M,D):M表示浮点数的长度为M,D小数点后的位数,后序对数据库进行插入,此时新的数据就得遵循上述规则,如果非法数据就会插入失败
MySQL这里也遵守IEEE754标准,表示浮点数。很多时候,小数是不能精准储存和表示的,这就导致在进行某些计算的时候,会出现误差,也严禁使用两个浮点数进行比较
在工程上对于误差的容忍度比较大,但又一些特殊情况,对容忍就特别小,甚至0容忍
DECIMAL和NUMERIC(这两个差别不大,一般使用DECIMAL)不再使用IEEE754,这一套了,而是自己设置了一套存储格式,自己设定的这个存储格式相当于“变长的”,付出了更多的空间,使存储的数据更加准确
java中提供了类BigDecimal来表示。
上述谈到的类型均为有符号类型。无符号数字相减,很容易出现溢出。
字符串类型:
最常使用的类型VARCHAR,可变长字符串。如果是定长字符串,设定小了,容易不够用,设定大了,容易浪费空间。
强调:varchar(size)的单位是字符,不是字节。括号里面的为最大字符串长度
TEXT;不需要指定最大长度,完全根据你存储的数据自适应
虽然VARCHAR和TEXT都是可变长,但实际使用的时候更倾向于VARCHAR。TEXT可能会很大,大小难以预估,varchar由于制定了最大长度,所以容易预估出表里的数据量有多少
BLOB:存储的是二进制的数据,前面几个存储的是文本数据。
文本:(r,w,a)
二进制:(rb,wb,ab)
计算机存储和表示数据都是二进制方式(内存,硬盘,CPU……);
所谓文本是一种特殊的情况:文本数据里面的二进制数据,都是可以在码表上查询到对应的合法的字符,反之,这段二进制内容,无法在码表上查询到,或者查询到一些乱码之类的字符
使用数据库:
图片,视频,音频,可执行文件……都是二进制,可以使用BLOB进行存储
如果需要储存“hello”这样的字符串,使用varchar、text等类型
blob只能表示最大64kb,所以我们不建议直接用数据库直接存储图片/视频/音频/等内容,数据库SQL提供了很多丰富的功能,但这些功能只能针对数字/字符串/时间日期才有效。只存二进制数据,上述这些丰富的功能就没用了,一方面,数据库往往是一个系统中,执行效率比较低的环节,容易造成性能瓶颈。把二进制数据提取出来,不使用数据库保存,也能将的数据库的负担
时间类型:
操作之前都需要use选中数据库
(1)查看数据库中有哪些表
show tables;
(2)创建表
create table 表名 (列名 类型,列名 类型,列名 类型……);
在关系型数据库中,一个表有哪些列,每一个列叫什么名字,类型是什么,都是要提前确定好的,后续往表里存储的数据,都要严格最受上述类型的要求
注释:comment‘注释’,只能在建表中使用
更推荐:--或者#
在最初的时候就把表的结构设计好,但后期无可避免的是有修改的,这时该怎样做(数据量很大时)
1)申请一个新的机器,搭建好数据库环境
2)把旧的数据导入新的机器中(导入的过程中要注意,对于旧的数据库代码,切换成新的数据库代码)
3)当所有数据同步后,把应用程序中访问旧的数据库的代码更换成访问新的数据库的代码
(3)查看表结构
类似于查看的是“文件详情”,这样的内容也称为“元数据”
desc 表名;
此处的int(11):11表示位宽,在控制台上表示的时候,最多表示11个字符的宽度
这两列都允许为空;default:默认值为空
(4)删除表
drop table 表名;
注意:删表,也是一个危险的操作!!!删表比删库的危害性更大!!!删库第一时间就能发现问题,对于删表操作就更隐蔽了,程序使用数据库的时候,绝大部分都是正常的,但是可能逻辑以及出问题了
第三组:数据表内容的操作
(1)增
insert into 表名 +values(……);
insert into 表名(列名,列名……)values(……);个数,类型,顺序和()中的列名进行匹配
insert into 表名 values(值,值……),(值,值……),(值,值……),(值,值……);
在“客户端-服务器”交互过程中,交互的次数越多,整体开销就越大,花的时间就越长
插入时间:
插入当前时间:
(2)删
危险操作!!
delete from 表名 where 条件/order by /limit;
(3)改
一定要确保update数据改对了,改出问题,很麻烦!!
update 表名 set 列名 =值,列名=值……where/order by/limit;
(4)查
基础:
a,全列查询
select * from student;
*称为通配符。
select *是一个很危险的操作,如果这种表比较小select *无所谓,但是如果一个表一旦非常的,就会产生大量的硬盘IO和网络IO
硬盘和网卡,读写速度都是存在上限的,一旦触发大规模的select *就意味着把你的的硬盘/网卡 宽带给吃满了,其他客户端尝试访问数据库,访问操作就无法进行了
b,指定列查询
select 列名,列名……from 表名;
c,表达式查询
select 表达式 from 表名;
这样的结果只是数据库查询过程中生成的临时表,数据库本体没有进行任何修改
d,引入多个列进行计算
表达式查询只针对列和列只进行运算!!
e,别名
select 表达式 as 别名 from 表名;
(as可以省略)
f,去重查询
select distinct 列名 from 表名;
如果列名有多项,那么需要两项内容都相同,才会去重
g,排序查询
order by+排序依据;
asc可以显示表示升序,也可以省略
查询的时候可以按照指定的某个列进行查询,顺序或者倒序
数据库不会对于查询得到的结果做出任何承诺,除非SQL包含order by(如果不写结果是不可预期的)
降序排序:order by+排序依据 desc;(descen:降序)
如果数据相同,那么排序顺序是不可与预期的。
可以指定多个列来排序:
order by 列名,列名……;先按照第一个列来排序,如果数据相同,再按照第二个来排序……
h,条件查询
满足条件的记录就保留,不满足条件的就跳过
select 列名 from 表名 where 条件;
SQL中null是一个特殊的值;加减乘除都是null。null=null=>null,null相当于不成立
null<=>null =》true
顺序:
1)遍历表,取出每一行
2)把当前行带入where条件,根据条件决定真假
3)再根据select后面的列名/表达式,进行选择行/进行计算
4)order by进行排序
i,like模糊匹配:
不要求完全相等,只要满足一定的条件就可以了
%:匹配0个或任意个任意的字符
_:匹配一个特定的运算符
在SQL的like是不支持正则的,只能简单的使用%和_;
对于数据库来说,like是一个比较低效的操作,在实际开发中,使用要节制一些
j,NULL 的查询
k,分页查询
select*查询容易查询的数据过多,数据库容易挂,更稳妥的的方式是分页查询
select 列名 from 表名 limit N;
从下标为3开始,显示5个长度
进阶:
a,查询+插入
将一个表里的内容查询后直接放到另一个表中,这时要保证两个表中的列数/类型/顺序要相匹配,列名不需要相同
但是如果列的顺序不一致,也可以指定列插入
b,聚合查询
需要搭配集合函数进行查询
(1)count
先执行select*from,再根据上述结果计算count()
如果是count(*)中有空值,那么空值的那一行也会被计数。但是如果count(某列),遇到空值则不会进行计数。
计数时去重:distinct
(2)sum
select sum(列名+列名.....)from grade;
对一列进行求和,但要确保这一列是数字
在遇到null时,会直接跳过,不参与运算
(3)avg
在遇到null时,会直接跳过,不参与运算
(4)max/min
在遇到null时,会直接跳过,不参与比较
(5)group by
使用group by,制定一个列,就会把列,就会把列的值相同的行,归到一组,分完组后,还可以针对每个组,分别进行集合查询
(6)分组前加条件:
select role,avg(salary)from emp where name!="张三" group by role;
这时,在统计平均薪资时,少了"张三的数据"
(7)分组后加条件:
select role,avg(salary) from emp group by role having avg(salary)<50000;
相结合:
select role,avg(salary) from emp where name!="张三" group by role having avg(salary)<50000;
c,联合查询
内连接
(1)明确信息来自那张表
(2)把学生表和分数表进行笛卡尔积
(3)按照链接条件进行筛选,删除无意义数据
(4)指定其他条件
(5)删除多余的列
1)许仙同学的成绩:
mysql> select sn,name,score from student,score where student.id=score.student_id and student.name="许仙";
mysql> select sn,name,score from student join score on student.id=score.student_id and student.name="许仙";
2)所有同学的总成绩,及个人信息:
mysql> select sn,name,student.qq_mail,sum(score) from student,score where student.id=score.student_id group by student.name;
3)学生课程信息:
mysql> select sn,student.name,course.name as course_name ,score from student,score,course where student.id=score.student_id and course.id=score.course_id;
mysql> select sn,student.name,course.name as course_name ,score from student join score on student.id=score.student_id join course on course.id=score.course_id;
用要慎重!!由于笛卡尔积会产生大量的"中间结果"此时就会对性能影很大,有时甚至会把数据库搞挂
外连接:
如果两个表的数据是一一对应的,那么内连接和外连接的结果是一样的,但是数据不在一一对应,那么内连接和外连接就会不同
内连接:只包含两张表中同时具备的数据
外连接不支持from这种写法,给join 前面加上left/right就表示外连接
左外连接:
左连接就是以左表为基准,能够确保为左表的数据都呈现在最终的结果里,如果左表中的数据在右表中没有对应的记录,此时就会把右表中的相关字段改为null
右外连接:
自连接:
自己和自己进行笛卡尔积;
sql中进行的条件查询,是针对两个列之间进行比较,不能两个行进行比较
自连接本质上就是把行关系转换成列关系(这时一个经典的数学思维,把未知问题转换为已知问题)
我们需要给这个表前后取不同的别名,指定连接条件
以比较学生的不同科目的成绩比较为例:
mysql> select s1.student_id,s1.score,s2.score from score as s1,score as s2 where s1.student_id=s2.student_id and s1.course_id=3 and s2.course_id=1 and s1.score>s2.score;
子查询(原则上不推荐使用):
单行子查询:
查询不想毕业同学的同班同学
mysql> select classes_id from student where name="不想毕业";
mysql> select name from student where classes_id=1 and name!="不想毕业";
两行sql合并就构成了子查询:
mysql> select name from student where classes_id=(select classes_id from student where name="不想毕业") and name!="不想毕业";
多行子查询:
mysql> select id from course where name="语文";
mysql> select id from course where name="英文";
mysql> select *from score where course_id in(4,6);
三行sql合并就构成了子查询:
mysql> select *from score where course_id in( select id from course where name="语文"or name="英文");
exists,子查询本身就不推荐使用,exists非常消耗时间,背后会触发大量的硬盘IO操作,并且代码理解起来比较复杂
合并查询:
可以针对不同表
union 会去重,union all,不会去重
要求合并双方列的类型,个数,顺序匹配,列名不做要求
约束
——数据库这样的程序,对于保存的数据进行合法性检查这样的机制,一旦发现问题,数据库就能直接作出反馈
1)NOT NULL
——指示某列不能存储 NULL 值。
not null不仅插入的时候有效,修改的时候同样有效
2)UNIQUE
—— 保证某列的每行必须有唯一的值。
插入/修改之前,先进行查询。
3)DEFAULT
—— 规定没有给列赋值时的默认值。
4)PRIMARY KEY(主键)
—— NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标 识,有助于更容易更快速地找到表中的一个特定的记录。
不允许一张表中,存在多个主键:
逐渐也会在插入/修改前进行查询。
MySQL提供了一个自动分配主键值方式,“自增主键”插入数据时,可以不指定id列的值,让数据库自动分配,就会按照1,2,3……
写的时候id为null,系统会自动分配……
在MySQL给每一个表中都记录的了一个“自增逐渐的最大值”,后续继续插入数据,无论之前的最大值是否存在,都会根据之前保存的最大值,继续往后分配
自增主键只针对int/bigint整数,但有可能会用字符串作为主键(数据量特别大的时候,不是一个MySQL数据库,而是多个MySQL构成的分布式“集群”,这样就引入了多个机器,每个机器上只储存一部分数据,但我们仍希望这些数据的id是唯一的,所以这时MySQL的自增主键就难以胜任力,因为他只能在一个机器上的MySQL生效,不能跨主机,此时,就会把id指定成一个字符串类型,然后通过应用程序里面的一系列算法,生成一个“唯一的字符串id”生成的时候有很多不同的方法,例如uuid/雪花算法……)
5)FOREIGN KEY (外键)
——保证一个表中的数据匹配另一个表中的值的参照完整性。
学生表的classid列引用了班级表的classid列,班级表中的数据约束了学生表的数据。。起约束作用的称为父表,被约束的一方称为子表
如果父表中的某个记录被子表引用了,那么父表就不能随意删除/修改父表中的数据
以下这种情况是因为没有给classid指定主键(数据库会针对主键建立索引,加快查询速度)
当不需要一个数据时,需要进行逻辑删除(逻辑删除,硬盘空间并没有被真正的释放,只不过把对应硬盘的空间标记为“未使用”,直到后续有其他文件写入硬盘,恰好用到了这块空间,才会把这里的数据覆盖掉)
6)CHECK
——保证列中的值符合指定的条件。对于MySQL数据库,对CHECK子句进行分析,但是忽略 CHECK子句。
表的设计:
基本的切入点:梳理清楚需求中的“实体/对象”和“关系”
实体之间的关系:
1)一对一关系
例如:一个学生只有一个账号
a,搞一张表,同时包含学生和账户的信息
student_account(studentid,name,accountname,password)
b,搞两张表,一张是学生表,一张是账户表
student(studentid,name,accountid)
account(accountid,accountname,password)
----------------------------------------------------------------
student(studentid,name)
account(accountid,accountname,password,studentid)
2)一对多关系
一个学生只能属于一个班级,但是一个班级包含多个学生
学生表(学生id,学生姓名,班级id)
班级表(班级id,班级名称)
3)多对多关系
一个学生可以选择多门课程,一门课也可以被多个学生选择
学生表(学生id,学生姓名)
课程表(课程id,课程名称)
引入一个关联表:
学生课程表(学生id,课程id)