数据库学习笔记(13页).doc

《数据库学习笔记(13页).doc》由会员分享，可在线阅读，更多相关《数据库学习笔记(13页).doc（13页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、-数据库学习笔记-第 13 页数据库学习笔记Sql基本操作1、基本操作CRUD（增删改查）2、将sql基本操作根据操作对象分类：库操作，表操作（字段），数据操作。2.1库操作：对数据库的增删改查；新增数据库：基本语法：Create datebase 数据库名字 库选项库选项：用来约束数据库，分为两个选项l 字符集设定：charset（数据存储的编码格式）：常用的有GBK与UTF8l 校对集设定：collate（数据比较的规则）当创建数据库的sql语句执行之后，发生了什么？1、 在数据库系统中增加了对应的数据库信息；2、 会在保存数据的文件夹下：Date目录，创建一个对应数据库名字的文件夹；3、

2、 每个数据库下都有一个opt文件，保存了库选项。：查看数据库：1、查看所有数据库Show databases；2、查看指定部分的数据库：模糊查询Show databases like pattern；-pattern是匹配模式%:表示匹配多个字符；_:表示匹配单个字符；3、查看数据库创建语句：Show create database 数据库名字；eg: SHOW CREATE DATABASE imooc;数据库在执行sql语句之前会优化sql，系统保存的结果是优化后的结果。更新数据库：数据库名字不可修改；数据库的修改仅限库选项：字符集和校对集（校对集依赖字符集）。Alter database

3、 数据库名字 库选项；Charset/character set 字符集；Collate 校对集；删除数据库：Drop database 数据库名字；当删除数据库语句执行之后发生了什么？1、 在数据库内部看不到对应的数据库；2、 在对应的数据库存储的文件夹内，数据库名字对应的文件夹也被删除（级联删除：里面的数据表全部删除），数据库删除不可逆。2.2表操作表与字段密不可分新增数据表语法：Create table if not exists 表名（字段名字 数据类型（x），（如果数据类型是字符型，需要将字符长度加上）字段名字 数据类型， -最后一行不需要逗号）表选项；If not exists ：

4、如果表名不存在则创建，如果存在则不执行创建代码：检查功能；表选项：控制表的表现，字符集：charset/character set 具体字符集；-保证表中数据存储的字符集；校对集：collate 具体校对集；存储引擎：engine 具体存储引擎（innodb和myisam）；任何一个表的设计都必须指定数据库方案1：显示的指定表所属的数据库Create table if not exists 数据库名.表名（）；-将当前数据表创建到指定的数据库下。方案2：隐式的指定数据表所属数据库：先进入到某个数据库环境，然后这样创建的数据表自动归属到该数据库。进入数据库环境：use 数据库名字；2.2.2查看

5、数据表1、查看所有表 show tables；2、查看部分表：模糊匹配：Show tables likepattern；%:表示匹配多个字符；_:表示匹配单个字符；3、 查看表的创建语句；Show create table 表名；4.查看表结构：查看表中的字段信息Desc/describe/show columns（列）from 表名；2.2.3修改数据表修改表本身表本身可以修改：表名和表选项；1、修改表名：Rename table 老表名 to 新表名；2、修改表选项：A 修改字段（增、删、改、查）1、新增字段：Alter table 表名 addcolumn 字段名 数据类型 列属性 位置

6、；位置：字段名可以存放表中任意位置First：第一个位置；After：在哪个字段之后：after 字段名；默认的是在最后一个字段之后。2、修改字段：Alter table 表名 modify 字段名 数据类型【属性】【位置】；3、重命名字段Alter table 表名 change 旧字段 新字段名 数据类型【属性】【位置】；4、 删除字段：Alter table 表名 drop字段名；注意：如果表中已经存在数据，那么删除字段会清空该字段的所有数据（不可逆）。2.2.4删除数据表Drop table 表名1，表名2，表名3；可以一簇删除多张表。2.3数据操作：新增数据：两种方案：方案1、给全表

7、字段插入数据，不需要指定字段列表；要求数据的值出现的顺序必须与表中设计的字段出现的顺序一致，凡是非数值数据都需要使用引号（建议使用单引号）包裹。Insert into 表名 values（值列表）【，（值列表）】；-可以一次插入多条数据。方案2、给部分字段插入数据，需要选定字段列表，字段列表出现的顺序与字段的顺序无关，但是值列表的顺序必须与选定的字段的顺序一致Insert into 表名（字段列表）values （值列表）【，（值列表）】；2.3.2查看数据：1、查看所有数据Select * from 表名；2、查看指定字段，指定条件的数据Select 字段列表 from 表名 【where条

8、件】；2.3.3更新数据：Update 表名 set 字段 = 值【where条件】；-建议都有where：要不是更新全部2.3.4删除数据：Delete from 表名【where条件】；3、中文数据问题中文数据问题本质是字符集问题。4、数据类型（列类型）Sql中将数据类型分为三大类：数值类型，字符串类型和时间类型4.1、整数型（tinyint，int）常用Tinyint和Int。SQL中的数据类型全部都是默认有符号，分正负；有的时候需要使用无符号的数据，需要给数据类型限定：int unsigned，-无符号从0开始。显示宽度没有特别的含义，只是默认告诉用户可以显示的形式而已，实际上用户可以

9、控制的，这种控制不会改变数据本身的大小。显示宽度的意义：5、列属性列属性：真正约束字段的是数据类型，但是数据类型的约束很单一，需要有一些额外的约束，来更加保证数据的合法性。列属性有很多：NULL/NOTNULL，default，Primary key，unique key，auto_increment，comment5.1、空属性两个值：NULL（默认的）和NOT NULL虽然默认为空，但实际开发中，尽可能保证所有数据都不为空，因为空数据没有意义，也无法参加运算。5.2、列描述列描述：comment，描述，没有实际含义：是专门用来描述字段的，会根据表创建语句保存，用来给程序员（数据库管理员）来

10、进行了解的。5.3、默认值默认值：某一种数据会经常性的出现某一个具体的值，可以在一开始便指定好，在需要真实数据的时候，用户可以选择性的使用默认值。6、 字段属性6.1、主键：主键：primary key，主要的键，一张表只能有一个主键，用来唯一的约束该字段里的数据，不能重复。增加主键增加主键的三种方式：1、 创建表时直接在字段之后跟primary key关键字（主键本身不允许为空）优点：直接；缺点：只能使用一个字段作为主键。2、 创建表的时候，在所有字段之后使用primary key（主键字段列表）来创建主键（如果有多个字段为主键，可以是复合主键）3、 当表已经创建好之后，再次额外追加主键，可

11、以通过修改表字段属性，也可以直接追加。Alter table 表名 add primary key（字段列表）前提：表中字段对应的数据本身是独立的（不重复）。主键约束主键对应的字段中数据不允许重复，一旦重复，数据操作失败。更新主键&删除主键没有办法更新主键，主键必须先删除，才能增加。Alter table 表名 drop primary key； 、主键分类6.2、自动增长自增长：当对应字段不给值或者给默认值或者给NULL时，自增长被系统触发，系统会从当前字段中已有的最大值进行+1操作，得到一个新的不同字段。自增长通常与主键配合使用；自增长的特点：auto_increment新增自增长1、 任

12、何一个字段做自增长，前提本身必须是一个索引（key一栏有值）；2、 字段必须是数字，而且是整型；3、 一张表最多只能有一个自增长。自增长使用当对应字段不给值或者给默认值或者给NULL时，自增长被系统触发，系统会从当前字段中已有的最大值进行+1操作，得到一个新的不同字段。如果自增长对应字段输入了值，那么自增长将失效，但是下一次还是能自动的自增长（从最大值+1）如何确定下一次自增长是什么？可以通过查看创建表语句6.2.3修改自增长自增长如果涉及到字段改变，必须先删除自增长后增加（因为一张表只能有一个自增长）。修改当前自增长已经存在的值，修改只能比当前已有的自增长的最大值大，不能小，（小不生效）。A

13、lter table 表名 auto_increment =？；6.2.4删除字增长自增长是一个属性，可以通过modify来进行修改（保证字段里没有auto_increment即可）Alter table 表名 modify 字段 类型。6.3、唯一键一张表往往有许多字段需要具有唯一性，数据不能重复，但是一张表中只能有一个主键，唯一键（unique key）就可以解决多个字段需要唯一性约束的问题。唯一键本质与主键差不多，唯一键默认的允许自动为空，而且可以对个为空（空字段不参与唯一性比较）。增加唯一键基本与主键差不多1、在创建表时，字段之后直接跟unique/unique key2、在所有字段之

14、后增加unique key （字段列表）；-复合唯一。4、 在创建表之后增加唯一键；唯一键约束唯一键与主键本质相同，唯一的区别就是唯一键默认允许为空，而且是多空；如果唯一键也不允许为空，与主键的约束作用是一致的。更新唯一键&删除唯一键更新唯一键：先删除后新增（唯一键可以有多个，可以不删除）1、删除唯一键Alter table 表名 drop index 索引名字；唯一键默认使用字段名作为索引名字。6.4、索引几乎所有的索引都建立在字段之上索引：系统根据某种算法，将已有的数据（未来可能新增的数据）单独建立一个文件，文件能够实现快速的匹配数据，并且能快速的找到对应表中的记录。索引的意义：1、 提升

15、查询数据的效率；2、 约束数据的有效性（唯一性等）；增加索引的前提条件：索引本身会产生索引文件（有时候可能比数据文件还大），会非常耗费磁盘空间，Mysql中提供多种索引：1、 主键索引：primary key；2、 唯一索引：unique key；3、 全文索引：fulltext index；4、 普通索引：index全文索引：针对文章内部的关键字进行索引，全文索引最大的一个问题在于如何确定关键字。英文很容易：英文单词与单词之间有空格；中文很难：没有空格，而且中文可以各种随意组合（分词：sphinx）。7、 数据的高级操作数据操作：增删改查7.1新增数据基本语法：Insert into 表名

16、【（字段列表） values （值列表）】；7.2查询数据基本语法：Select 字段列表/* from 表名 【where 条件】完整语法：Select【select 选项】字段列表【字段别名】/* from 数据源 【where 条件子句】【group by 子句】【having 子句】【order by 子句】【limit 子句】；1、 select 选项：select对查出来的结果的处理方式。All：默认的，保留所有结果；Discinct：去重，查出来的结果，将重复的给去除（所有字段都相同）。2、 字段别名：字段别名：当数据进行查询出来的时候，有时候名字并不一定就满足需求（多表查询的时

17、候，会有同名字段）需要对字段名进行重命名：别名。语法：字段名 【as】 别名；3、 数据源数据源：数据的来源，关系型数据库的来源都是数据表，本质上只要保证数据表类似二维表，最终都可以作为数据源。数据源分为多种：单表数据源，多表数据源，查询语句；1、 单表数据源：select * from 表名；2、 多表数据名：select * from 表名1，表名2，；（笛卡尔积没什么卵用）3、 子查询：数据来源是一条查询语句（查询语句的结果是二维表）Select * from （select 语句） as 表名；4、where子句where子句：用来判断数据，筛选数据。Where子句返回结果，0或者1,

18、0代表false，1代表true。判断条件：条件查询1：要求找出学生id为1或者3或者5的学生；条件查询2： 查询区间落在180,190身高之间的学生;Between本身是闭区间，between左边的值必须小于或者等于右边的值。4、 group by子句group by ：分组的意思，根据某个字段进行分组（相同一组，不同的分到不同组）基本语法：Group by 字段名；分组的意义在于统计数据（按组统计，按分组字段进行数据统计）SQL提供一系列统计函数：Count（）；统计分组后的记录数，每一组有多少记录；Max（）；统计每组中最大值；Min（）；统计每组中最小值；Avg（）；统计平均值；Sum

19、（）；统计和；Count（）函数，里面可以使用两种参数，*代表统计记录，字段名代表统计对应的字段（NULL 不统计）；分组会自动排序，根据分组字段自动排序，默认升序。Group by 字段 【asc|desc（降序）】；-对分组结果合并之后的整个结果进行排序。多字段分组：先根据一个字段进行分组，然后对分组后的结果再次按照其他字段进行分组。5、 having子句having子句：与where子句一样，进行条件判断的。Where是对磁盘数据进行判断的，进入内存之后，会进行分组操作，分组结果就需要having来处理。Having能做where能做的几乎所有事情，但where却不能做having能做的

20、很多事情。5.1、分组统计的结果或者说统计函数都只有having能够使用。5.2、having能够使用字段别名但where不能，where是从磁盘取数据，所以名字只可能是字段名，别名是在字段进入到内存后才产生的。6、 Order by子句：Order by：排序，根据某个字段进行升序后者降序排序，依赖校对集。基本语法：Order by 字段名 【asc|desc】；-asc是升序（默认的），desc是降序。排序可以进行多字段排序，先根据某个字段进行排序，然后排序好的内部再按照某个数据进行再次排序。7、 limit子句limit子句是一种限制结果的语句：限制数量。Limit有两种使用方式：7.1

21、、只用来限制长度（数据量），limit数据量；7.2、限制起始位置，限制数量；limit起始位置，长度；8、 数据库事务的基本特性数据库事务概念什么是数据库事务？事务（transaction）是由一系列操作序列构成的程序执行单元，这些操作要么都做，要么都不做，是一个不可分割的工作单位。数据库事务的四个基本性质（ACID）1. 原子性（Atomicity）事务的原子性是指事务中包含的所有操作要么全做，要么全不做（all or none）。2. 一致性（Consistency）在事务开始以前，数据库处于一致性的状态，事务结束后，数据库也必须处于一致性状态。拿银行转账来说，一致性要求事务的执行不应改

22、变A、B 两个账户的金额总和。如果没有这种一致性要求，转账过程中就会发生钱无中生有，或者不翼而飞的现象。事务应该把数据库从一个一致性状态转换到另外一个一致性状态。3. 隔离性（Isolation）事务隔离性要求系统必须保证事务不受其他并发执行的事务的影响，也即要达到这样一种效果：对于任何一对事务T1 和 T2，在事务 T1 看来，T2 要么在 T1 开始之前已经结束，要么在 T1 完成之后才开始执行。这样，每个事务都感觉不到系统中有其他事务在并发地执行。4. 持久性（Durability）一个事务一旦成功完成，它对数据库的改变必须是永久的，即便是在系统遇到故障的情况下也不会丢失。数据的重要性决

23、定了事务持久性的重要性。9、 关系：将实体与实体的关系，反映到最终数据表的设计上来：将关系分为三种：一对一，一对多（多对一）和多对多。所有的关系指的都是表与表之间的关系。9.1一对一：一张表的一条记录只能与另一张表的一条记录一一对应，反之亦然。9.2一对多 9.3多对多：连接查询；join，使用方式:左表join右表左表：在join关键字左边的表右表：在join关键字右边的表10.1交叉连接（理解为取并集）交叉连接（全连接，理解为取并集）：cross join，从一张表中循环取出每一条记录，每条记录都去另一张表进行匹配，匹配一定保留（没有条件匹配），而连接本身字段就会增加（保留），最终形成的结

24、果叫做笛卡尔积。基本语法：左表 cross join 右表；=from 左表，右表；注意1、是先进行连接，后进行查询；2、笛卡尔积没有意义，应该尽量避免使用；3、交叉连接的意义，保证连接这种结构的完整性。10.2内连接（取交集）内连接：【inner】join ，从左表中取出每一条记录，去右表中与所有的记录进行匹配，匹配必须是左表中某个条件与右表中相同最终才会保留结果，否则不保留。基本语法：左表 【inner】join 右表 on 左表.字段 = 右表.字段；on表示连接条件，条件字段就是代表相同的业务含义。字段别名以及表别名的使用：在查询数据的时候，不同表有同名字段，这个时候需要加上表名才能区

25、分，而表名太长，通常可以使用别名。内连接可以没有连接条件：没有on之后的内容，这个时候系统会保留所有结果（笛卡尔积），相当于交叉连接。内连接还可以使用where代替on关键字（where没有on效率高）10.3外连接外连接：outer join，以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接，不管能不能匹配上条件，最终都会保留，能匹配，正确保留；不能匹配，其他表的字段都置为NULL。外连接分为2种：左连接（left join）与右连接（right join）基本语法：左表 left/right join 右表 on 左表.字段 = 右表.字段;虽然左连接和右连接有主表的差异，但

26、是显示的结果：左表的数据在左边，右表的数据在右边，左右表连接可以互转。10.4自然连接自然连接；natural join ，就是自动匹配连接条件，系统以字段名字作为匹配模式（同名字段就可作为条件，多个同名字段都作为条件）自然连接分为：自然内连接和自然外连接。自然内连接：左表natural join 右表；自然外连接：左表 natural left/right join 右表；11、外键外键：foreign key，外面的键（键不在自己表中）；如果一张表中有一个字段（非主键）指向另外一张表的主键，那么将该字段称之为外键。11.1增加外键外键可以在创建表的时候或者创建表之后增加（但是要考虑数据问题

27、）1、创建表的时候增加外键：在所有的表字段之后，使用foreign key（外键字段）references 外部表（主键字段）2、 在新增表之后增加外键 修改表结构Alter table 表名 add 【constraint 外键名字】foreign key（外键字段）references 父表（主键字段）12、联合查询联合查询：将多次查询（多条select语句），在记录上进行拼接（字段不会增加）基本语法：多条select语句构成：每一条select语句获取的字段必须严格一致（但是字段类型无关）Select 语句1Union【union选项】Select 语句2、Union选项与select选

28、项一样有2个；All:保留所有（不管重复）Distinct：去重（整个重复）：默认的。联合查询的意义:1、 查询统一张表，但是需求不同，如查询学生信息，男生身高升序，女生身高降序。2、 多表查询，多张表的结构是完全一样的，保存的数据结构也是一样的。order by 的使用在联合查询中，order by 不能直接使用，需要对查询语句使用括号才行；若要order by 生效，必须搭配limit，limit使用限定的最大数即可。13、子查询子查询：sub query ，查询是在某个查询结果之上进行的（一条select语句中包含另外一条select语句）。子查询的分类：按位置分类；按结果分类。按位置分

29、类：子查询（select语句）在外部查询（select语句）中出现的位置。From子查询：子查询跟在from之后；Where子查询：子查询出现在where条件中；Exists子查询：子查询出现在exists里面。按结果分类：根据子查询得到的数据进行分类（理论上讲任何一个子查询得到的结果都可以理解为二维表）标量子查询：子查询得到的结果是一行一列；列子查询：子查询得到的结果是一列多行；行子查询：子查询得到的结果是多行一列（多行多列）上面几个出现的位置都是在where之后表子查询：子查询得到的结果是多行多列（出现的位置是在from之后）。13.1标量子查询14、视图：视图：view是一种有结构（有行

30、有列）但是没结果（结构中不存放真实数据）的虚拟表，虚拟表的结构来源不是自己定义，而是从对应的基表中产生（视图的数据来源）。14.1创建视图基本语法：Create view 视图名字 as select 语句；-select语句可以是普通查询，可以是连接查询，可以是联合查询，可以是子查询。14.2查看视图查看视图:查看视图的结构视图是一张虚拟表，表的所有查看方式都适用于视图，show tables【like】；desc 视图名字；show create table 视图名字；14.3使用视图使用视图主要是为了查询，将视图当做表一样查询即可。视图的执行：其实本质就是执行封装的select语句。14

31、.4修改视图视图本身不可修改，但是视图的来源是可以修改的；修改视图：修改视图本身的来源语句（select语句）Alter view 视图名字 as 新的select语句；14.5删除视图Drop view 视图名字；14.6视图意义15、数据的备份与还原备份：将当前已有的数据或者记录保留；还原：将已保存的数据恢复到对应表中； 数据备份还原的方式有很多种，数据表备份、单表数据备份、sql备份、增量备份15.1数据表备份不需要通过sql来备份：直接进入到数据库文件夹复制对应的表结构以及数据文件，以后还原的时候直接将备份的内容放进去即可。数据表备份有前提条件，根据不同的存储引擎有不同的区别；存储引擎

32、：mysql进行数据存储的方式：主要有两种：innodb和myisam（免费）16、事务（存储引擎必须是innodb）16.1事务安全：事务：一系列要发生的连续的操作。事务安全：一种保护连续操作同时执行的一种机制。事务安全的意义：保证数据操作的完整性。16.2事务操作：事务操作分为两种：自动事务（默认的）、手动事务手动事务：操作流程1、 开启事务：告诉系统以下所有的操作（写）不要直接写入到数据表，先存放到事务日志；Start transaction；2、 进行事务操作：3、 关闭事务：选择性的将日志文件中操作的结果保存到数据表中（同步），或者说直接清空事务日志（原来操作全部清空）。1、 提交事务：同步数据表（操作成功）：commit；2、 回滚事务：直接清空日志表（操作失败）：rollback；16.2事务原理：事务操作原理：事务开启之后，所有操作都会临时保存在事务日志上，事务日志只有在得到commit命令之后才会同步到数据表，其他任何情况都会清空（rollback、断电、断开连接）。16.3回滚点：回滚点：在某个成功的操作完成之后，后续的操作有可能成功也有可能失败，但不管成功或者失败，前面的操作都已经成功，可以在当前成功的位置设置一个点，供后续失败操作返回该位置，而不是返回所有操作，这个点称为回滚点。设置回滚点语法：savepoint 回滚点名字