数据库学习笔记.docx
- 文档编号:26105762
- 上传时间:2023-06-17
- 格式:DOCX
- 页数:19
- 大小:25.20KB
数据库学习笔记.docx
《数据库学习笔记.docx》由会员分享,可在线阅读,更多相关《数据库学习笔记.docx(19页珍藏版)》请在冰豆网上搜索。
数据库学习笔记
数据库学习笔记
Sql基本操作
1、基本操作CRUD(增删改查)
2、将sql基本操作根据操作对象分类:
库操作,表操作(字段),数据操作。
2.1库操作:
对数据库的增删改查;
基本语法:
Createdatebase数据库名字[库选项]
库选项:
用来约束数据库,分为两个选项
●字符集设定:
charset(数据存储的编码格式):
常用的有GBK与UTF8
●校对集设定:
collate(数据比较的规则)
当创建数据库的sql语句执行之后,发生了什么?
1、在数据库系统中增加了对应的数据库信息;
2、会在保存数据的文件夹下:
Date目录,创建一个对应数据库名字的文件夹;
3、每个数据库下都有一个opt文件,保存了库选项。
1、查看所有数据库
Showdatabases;
2、查看指定部分的数据库:
模糊查询
Showdatabaseslike‘pattern’;---pattern是匹配模式
%:
表示匹配多个字符;
_:
表示匹配单个字符;
3、查看数据库创建语句:
Showcreatedatabase数据库名字;
eg:
SHOWCREATEDATABASEimooc;
数据库在执行sql语句之前会优化sql,系统保存的结果是优化后的结果。
数据库名字不可修改;
数据库的修改仅限库选项:
字符集和校对集(校对集依赖字符集)。
Alterdatabase数据库名字[库选项];
Charset/characterset字符集;
Collate校对集;
Dropdatabase数据库名字;
当删除数据库语句执行之后发生了什么?
1、在数据库内部看不到对应的数据库;
2、在对应的数据库存储的文件夹内,数据库名字对应的文件夹也被删除(级联删除:
里面的数据表全部删除),数据库删除不可逆。
2.2表操作
表与字段密不可分
语法:
Createtable[ifnotexists]表名(
字段名字数据类型(x),(如果数据类型是字符型,需要将字符长度加上)
字段名字数据类型,
·······-----最后一行不需要逗号
)[表选项];
Ifnotexists:
如果表名不存在则创建,如果存在则不执行创建代码:
检查功能;
表选项:
控制表的表现,
字符集:
charset/characterset具体字符集;--保证表中数据存储的字符集;
校对集:
collate具体校对集;
存储引擎:
engine具体存储引擎(innodb和myisam);
任何一个表的设计都必须指定数据库
方案1:
显示的指定表所属的数据库
Createtable[ifnotexists]数据库名.表名();--将当前数据表创建到指定的数据库下。
方案2:
隐式的指定数据表所属数据库:
先进入到某个数据库环境,然后这样创建的数据表自动归属到该数据库。
进入数据库环境:
use数据库名字;
1、查看所有表showtables;
2、查看部分表:
模糊匹配:
Showtableslike‘pattern’;
%:
表示匹配多个字符;
_:
表示匹配单个字符;
3、查看表的创建语句;
Showcreatetable表名;
4.查看表结构:
查看表中的字段信息
Desc/describe/showcolumns(列)from表名;
表本身可以修改:
表名和表选项;
1、修改表名:
Renametable老表名to新表名;
2、修改表选项:
A
1、新增字段:
Altertable表名add[column]字段名数据类型[列属性][位置];
位置:
字段名可以存放表中任意位置
First:
第一个位置;
After:
在哪个字段之后:
after字段名;默认的是在最后一个字段之后。
2、修改字段:
Altertable表名modify字段名数据类型【属性】【位置】;
3、重命名字段
Altertable表名change旧字段新字段名数据类型【属性】【位置】;
4、删除字段:
Altertable表名drop字段名;
注意:
如果表中已经存在数据,那么删除字段会清空该字段的所有数据(不可逆)。
Droptable表名1,表名2,表名3···;可以一簇删除多张表。
2.3数据操作:
两种方案:
方案1、给全表字段插入数据,不需要指定字段列表;要求数据的值出现的顺序必须与表中设计的字段出现的顺序一致,凡是非数值数据都需要使用引号(建议使用单引号)包裹。
Insertinto表名values(值列表)【,(值列表)】;--可以一次插入多条数据。
方案2、给部分字段插入数据,需要选定字段列表,字段列表出现的顺序与字段的顺序无关,但是值列表的顺序必须与选定的字段的顺序一致
Insertinto表名(字段列表)values(值列表)【,(值列表)】;
1、查看所有数据
Select*from表名;
2、查看指定字段,指定条件的数据
Select字段列表from表名【where条件】;
Update表名set字段=值【where条件】;---建议都有where:
要不是更新全部
Deletefrom表名【where条件】;
3、中文数据问题
中文数据问题本质是字符集问题。
4、数据类型(列类型)
Sql中将数据类型分为三大类:
数值类型,字符串类型和时间类型
4.1、整数型(tinyint,int)
常用Tinyint和Int。
SQL中的数据类型全部都是默认有符号,分正负;
有的时候需要使用无符号的数据,需要给数据类型限定:
intunsigned,----无符号从0开始。
显示宽度没有特别的含义,只是默认告诉用户可以显示的形式而已,实际上用户可以控制的,这种控制不会改变数据本身的大小。
显示宽度的意义:
5、列属性
列属性:
真正约束字段的是数据类型,但是数据类型的约束很单一,需要有一些额外的约束,来更加保证数据的合法性。
列属性有很多:
NULL/NOTNULL,default,Primarykey,uniquekey,auto_increment,comment
5.1、空属性
两个值:
NULL(默认的)和NOTNULL
虽然默认为空,但实际开发中,尽可能保证所有数据都不为空,因为空数据没有意义,也无法参加运算。
5.2、列描述
列描述:
comment,描述,没有实际含义:
是专门用来描述字段的,会根据表创建语句保存,用来给程序员(数据库管理员)来进行了解的。
5.3、默认值
默认值:
某一种数据会经常性的出现某一个具体的值,可以在一开始便指定好,在需要真实数据的时候,用户可以选择性的使用默认值。
6、字段属性
6.1、主键:
主键:
primarykey,主要的键,一张表只能有一个主键,用来唯一的约束该字段里的数据,不能重复。
增加主键的三种方式:
1、创建表时直接在字段之后跟primarykey关键字(主键本身不允许为空)
优点:
直接;缺点:
只能使用一个字段作为主键。
2、创建表的时候,在所有字段之后使用primarykey(主键字段列表)来创建主键(如果有多个字段为主键,可以是复合主键)
3、当表已经创建好之后,再次额外追加主键,可以通过修改表字段属性,也可以直接追加。
Altertable表名addprimarykey(字段列表)
前提:
表中字段对应的数据本身是独立的(不重复)。
主键对应的字段中数据不允许重复,一旦重复,数据操作失败。
没有办法更新主键,主键必须先删除,才能增加。
Altertable表名dropprimarykey;
6.2、自动增长
自增长:
当对应字段不给值或者给默认值或者给NULL时,自增长被系统触发,系统会从当前字段中已有的最大值进行+1操作,得到一个新的不同字段。
自增长通常与主键配合使用;
自增长的特点:
auto_increment
1、任何一个字段做自增长,前提本身必须是一个索引(key一栏有值);
2、字段必须是数字,而且是整型;
3、一张表最多只能有一个自增长。
当对应字段不给值或者给默认值或者给NULL时,自增长被系统触发,系统会从当前字段中已有的最大值进行+1操作,得到一个新的不同字段。
如果自增长对应字段输入了值,那么自增长将失效,但是下一次还是能自动的自增长(从最大值+1)
如何确定下一次自增长是什么?
可以通过查看创建表语句
自增长如果涉及到字段改变,必须先删除自增长后增加(因为一张表只能有一个自增长)。
修改当前自增长已经存在的值,修改只能比当前已有的自增长的最大值大,不能小,(小不生效)。
Altertable表名auto_increment=?
;
自增长是一个属性,可以通过modify来进行修改(保证字段里没有auto_increment即可)
Altertable表名modify字段类型。
6.3、唯一键
一张表往往有许多字段需要具有唯一性,数据不能重复,但是一张表中只能有一个主键,唯一键(uniquekey)就可以解决多个字段需要唯一性约束的问题。
唯一键本质与主键差不多,唯一键默认的允许自动为空,而且可以对个为空(空字段不参与唯一性比较)。
基本与主键差不多
1、在创建表时,字段之后直接跟unique/uniquekey
2、在所有字段之后增加uniquekey(字段列表);--复合唯一。
4、在创建表之后增加唯一键;
唯一键与主键本质相同,唯一的区别就是唯一键默认允许为空,而且是多空;
如果唯一键也不允许为空,与主键的约束作用是一致的。
更新唯一键:
先删除后新增(唯一键可以有多个,可以不删除)
1、删除唯一键
Altertable表名dropindex索引名字;唯一键默认使用字段名作为索引名字。
6.4、索引
几乎所有的索引都建立在字段之上
索引:
系统根据某种算法,将已有的数据(未来可能新增的数据)单独建立一个文件,文件能够实现快速的匹配数据,并且能快速的找到对应表中的记录。
索引的意义:
1、提升查询数据的效率;
2、约束数据的有效性(唯一性等);
增加索引的前提条件:
索引本身会产生索引文件(有时候可能比数据文件还大),会非常耗费磁盘空间,
Mysql中提供多种索引:
1、主键索引:
primarykey;
2、唯一索引:
uniquekey;
3、全文索引:
fulltextindex;
4、普通索引:
index
全文索引:
针对文章内部的关键字进行索引,全文索引最大的一个问题在于如何确定关键字。
英文很容易:
英文单词与单词之间有空格;
中文很难:
没有空格,而且中文可以各种随意组合(分词:
sphinx)。
7、数据的高级操作
数据操作:
增删改查
7.1新增数据
基本语法:
Insertinto表名【(字段列表)values(值列表)】;
7.2查询数据
基本语法:
Select字段列表/*from表名【where条件】
完整语法:
Select【select选项】字段列表【字段别名】/*from数据源【where条件子句】【groupby子句】【having子句】【orderby子句】【limit子句】;
1、select选项:
select对查出来的结果的处理方式。
All:
默认的,保留所有结果;
Discinct:
去重,查出来的结果,将重复的给去除(所有字段都相同)。
2、字段别名:
字段别名:
当数据进行查询出来的时候,有时候名字并不一定就满足需求(多表查询的时候,会有同名字段)需要对字段名进行重命名:
别名。
语法:
字段名【as】别名;
3、数据源
数据源:
数据的来源,关系型数据库的来源都是数据表,本质上只要保证数据表类似二维表,最终都可以作为数据源。
数据源分为多种:
单表数据源,多表数据源,查询语句;
1、单表数据源:
select*from表名;
2、多表数据名:
select*from表名1,表名2,····;(笛卡尔积没什么卵用)
3、子查询:
数据来源是一条查询语句(查询语句的结果是二维表)
Select*from(select语句)as表名;
4、where子句
where子句:
用来判断数据,筛选数据。
Where子句返回结果,0或者1,0代表false,1代表true。
判断条件:
条件查询1:
要求找出学生id为1或者3或者5的学生;
条件查询2:
查询区间落在180,190身高之间的学生;
Between本身是闭区间,between左边的值必须小于或者等于右边的值。
4、groupby子句
groupby:
分组的意思,根据某个字段进行分组(相同一组,不同的分到不同组)
基本语法:
Groupby字段名;
分组的意义在于统计数据(按组统计,按分组字段进行数据统计)
SQL提供一系列统计函数:
Count();统计分组后的记录数,每一组有多少记录;
Max();统计每组中最大值;
Min();统计每组中最小值;
Avg();统计平均值;
Sum();统计和;
Count()函数,里面可以使用两种参数,*代表统计记录,字段名代表统计对应的字段(NULL不统计);
分组会自动排序,根据分组字段自动排序,默认升序。
Groupby字段【asc|desc(降序)】;---对分组结果合并之后的整个结果进行排序。
多字段分组:
先根据一个字段进行分组,然后对分组后的结果再次按照其他字段进行分组。
5、having子句
having子句:
与where子句一样,进行条件判断的。
Where是对磁盘数据进行判断的,进入内存之后,会进行分组操作,分组结果就需要having来处理。
Having能做where能做的几乎所有事情,但where却不能做having能做的很多事情。
5.1、分组统计的结果或者说统计函数都只有having能够使用。
5.2、having能够使用字段别名但where不能,where是从磁盘取数据,所以名字只可能是字段名,别名是在字段进入到内存后才产生的。
6、Orderby子句:
Orderby:
排序,根据某个字段进行升序后者降序排序,依赖校对集。
基本语法:
Orderby字段名【asc|desc】;--asc是升序(默认的),desc是降序。
排序可以进行多字段排序,先根据某个字段进行排序,然后排序好的内部再按照某个数据进行再次排序。
7、limit子句
limit子句是一种限制结果的语句:
限制数量。
Limit有两种使用方式:
7.1、只用来限制长度(数据量),limit数据量;
7.2、限制起始位置,限制数量;limit起始位置,长度;
8、数据库事务的基本特性
数据库事务概念
什么是数据库事务?
事务(transaction)是由一系列操作序列构成的程序执行单元,这些操作要么都做,要么都不做,是一个不可分割的工作单位。
数据库事务的四个基本性质(ACID)
1.原子性(Atomicity)
事务的原子性是指事务中包含的所有操作要么全做,要么全不做(allornone)。
2.一致性(Consistency)
在事务开始以前,数据库处于一致性的状态,事务结束后,数据库也必须处于一致性状态。
拿银行转账来说,一致性要求事务的执行不应改变A、B两个账户的金额总和。
如果没有这种一致性要求,转账过程中就会发生钱无中生有,或者不翼而飞的现象。
事务应该把数据库从一个一致性状态转换到另外一个一致性状态。
3.隔离性(Isolation)
事务隔离性要求系统必须保证事务不受其他并发执行的事务的影响,也即要达到这样一种效果:
对于任何一对事务T1和T2,在事务T1看来,T2要么在T1开始之前已经结束,要么在T1完成之后才开始执行。
这样,每个事务都感觉不到系统中有其他事务在并发地执行。
4.持久性(Durability)
一个事务一旦成功完成,它对数据库的改变必须是永久的,即便是在系统遇到故障的情况下也不会丢失。
数据的重要性决定了事务持久性的重要性。
9、关系:
将实体与实体的关系,反映到最终数据表的设计上来:
将关系分为三种:
一对一,一对多(多对一)和多对多。
所有的关系指的都是表与表之间的关系。
9.1一对一:
一张表的一条记录只能与另一张表的一条记录一一对应,反之亦然。
9.2一对多
9.3多对多:
连接查询;join,使用方式:
左表join右表
左表:
在join关键字左边的表
右表:
在join关键字右边的表
10.1交叉连接(理解为取并集)
交叉连接(全连接,理解为取并集):
crossjoin,从一张表中循环取出每一条记录,每条记录都去另一张表进行匹配,匹配一定保留(没有条件匹配),而连接本身字段就会增加(保留),最终形成的结果叫做笛卡尔积。
基本语法:
左表crossjoin右表;=======from左表,右表;
注意
1、是先进行连接,后进行查询;
2、笛卡尔积没有意义,应该尽量避免使用;
3、交叉连接的意义,保证连接这种结构的完整性。
10.2内连接(取交集)
内连接:
【inner】join,从左表中取出每一条记录,去右表中与所有的记录进行匹配,匹配必须是左表中某个条件与右表中相同最终才会保留结果,否则不保留。
基本语法:
左表【inner】join右表on左表.字段=右表.字段;on表示连接条件,条件字段就是代表相同的业务含义。
字段别名以及表别名的使用:
在查询数据的时候,不同表有同名字段,这个时候需要加上表名才能区分,而表名太长,通常可以使用别名。
内连接可以没有连接条件:
没有on之后的内容,这个时候系统会保留所有结果(笛卡尔积),相当于交叉连接。
内连接还可以使用where代替on关键字(where没有on效率高)
10.3外连接
外连接:
outerjoin,以某张表为主,取出里面的所有记录,然后每条与另外一张表进行连接,不管能不能匹配上条件,最终都会保留,能匹配,正确保留;不能匹配,其他表的字段都置为NULL。
外连接分为2种:
左连接(leftjoin)与右连接(rightjoin)
基本语法:
左表left/rightjoin右表on左表.字段=右表.字段;
虽然左连接和右连接有主表的差异,但是显示的结果:
左表的数据在左边,右表的数据在右边,左右表连接可以互转。
10.4自然连接
自然连接;naturaljoin,就是自动匹配连接条件,系统以字段名字作为匹配模式(同名字段就可作为条件,多个同名字段都作为条件)
自然连接分为:
自然内连接和自然外连接。
自然内连接:
左表naturaljoin右表;
自然外连接:
左表naturalleft/rightjoin右表;
11、外键
外键:
foreignkey,外面的键(键不在自己表中);如果一张表中有一个字段(非主键)指向另外一张表的主键,那么将该字段称之为外键。
11.1增加外键
外键可以在创建表的时候或者创建表之后增加(但是要考虑数据问题)
1、创建表的时候增加外键:
在所有的表字段之后,使用foreignkey(外键字段)references外部表(主键字段)
2、在新增表之后增加外键修改表结构
Altertable表名add【constraint外键名字】foreignkey(外键字段)references父表(主键字段)
12、联合查询
联合查询:
将多次查询(多条select语句),在记录上进行拼接(字段不会增加)
基本语法:
多条select语句构成:
每一条select语句获取的字段必须严格一致(但是字段类型无关)
Select语句1
Union【union选项】
Select语句2、、、、
Union选项与select选项一样有2个;
All:
保留所有(不管重复)
Distinct:
去重(整个重复):
默认的。
联合查询的意义:
1、查询统一张表,但是需求不同,如查询学生信息,男生身高升序,女生身高降序。
2、多表查询,多张表的结构是完全一样的,保存的数据结构也是一样的。
orderby的使用
在联合查询中,orderby不能直接使用,需要对查询语句使用括号才行;
若要orderby生效,必须搭配limit,limit使用限定的最大数即可。
13、子查询
子查询:
subquery,查询是在某个查询结果之上进行的(一条select语句中包含另外一条select语句)。
子查询的分类:
按位置分类;按结果分类。
按位置分类:
子查询(select语句)在外部查询(select语句)中出现的位置。
From子查询:
子查询跟在from之后;
Where子查询:
子查询出现在where条件中;
Exists子查询:
子查询出现在exists里面。
按结果分类:
根据子查询得到的数据进行分类(理论上讲任何一个子查询得到的结果都可以理解为二维表)
标量子查询:
子查询得到的结果是一行一列;
列子查询:
子查询得到的结果是一列多行;
行子查询:
子查询得到的结果是多行一列(多行多列)
上面几个出现的位置都是在where之后
表子查询:
子查询得到的结果是多行多列(出现的位置是在from之后)。
13.1标量子查询
14、视图:
视图:
view是一种有结构(有行有列)但是没结果(结构中不存放真实数据)的虚拟表,虚拟表的结构来源不是自己定义,而是从对应的基表中产生(视图的数据来源)。
14.1创建视图
基本语法:
Createview视图名字asselect语句;---select语句可以是普通查询,可以是连接查询,可以是联合查询,可以是子查询。
14.2查看视图
查看视图:
查看视图的结构
视图是一张虚拟表,表的所有查看方式都适用于视图,
showtables【like】;
desc视图名字;
showcreatetable视图名字;
14.3使用视图
使用视图主要是为了查询,将视图当做表一样查询即可。
视图的执行:
其实本质就是执行封装的select语句。
14.4修改视图
视图本身不可修改,但是视图的来源是可以修改的;
修改视图:
修改视图本身的来源语句(select语句)
Alterview视图名字as新的select语句;
14.5删除视图
Dropview视图名字;
14.6视图意义
15、数据的备份与还原
备份:
将当前已有的数据或者记录保留;
还原:
将已保存的数据恢复到对应表中;
数据备份还原的方式有很多种,数据表备份、单表数据备份、sql备份、增量备份
15.1数据表备份
不需要通过sql来备份:
直接进入到数据库文件夹复制对应的表结构以及数据文件,以后还原的时候直接将备份的内容放进去即可。
数据表备份有前提条件,根据不同的存储引擎有不同的区别;
存储引擎:
mysql进行数据存储的方式:
主要有两种:
innodb和myisam(免费)
16、事务(存储引擎必须是innodb)
16.1事务安全:
事务:
一系列要发生
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 学习 笔记