mysql数据库学习知识点.docx
《mysql数据库学习知识点.docx》由会员分享，可在线阅读，更多相关《mysql数据库学习知识点.docx（10页珍藏版）》请在冰豆网上搜索。
mysql数据库学习知识点
Mysql数据库的学习知识点
SQL是一门ANSI标准的计算机语言，但是仍然存在着多种不同版本的SQL语言，主要命令（SELECT，UPDATE，DETELE，INSERT，WHERE等等）
RDBMS关系型数据库管理系统，全称RelationalDatabaseManagementSystem，RDBMS是SQL的基础，同样也是所有现代数据库系统的基础，比如MSSQLServer、IBMDB2、Oracle、MySQL以及MicrosoftAccess。
RDBMS中的数据存储在被称为表的数据库对象中。
表是相关的数据项的集合，它由列和行组成。
常见的SQL命令：
SELECT从数据库中提取数据，UPDATE更新数据库中的数据，DELETE从数据库中删除数据，
Insertinto向数据库中插入新数据，createdatabase创建新数据库，ALTERDATABASE修改数据库，createtable创建新表，altertable变更（改变）数据库，DROPTABLE删除表
CREATEINDEX创建索引（搜索键）、dropindex删除索引；
常用的聚合函数有以下几种：
1、count:
行数；2、sum:
总和（求总和）；3、avg:
平均值；4、max最大值5、min最小值；
Mysql语句的先后顺序：
SELECT—FROM—WHERE—GROUPBY—HAVING—-ORDERBY—（LIMIT）;
SQL语句的执行顺序：
1、首先执行from子句；2、如果有where子句，则根据其中的过滤条件从中间表中去掉不满足过滤条件的行；3、根据groupby子句中指定的分组列；4、为每个组计算select子句聚合函数的值，并为每组生成查询结果中的一行。
数据库与数据仓库的区别：
数据库是面向事务的设计，数据仓库是面向主题设计的。
数据库一般存储在线交易数据，数据仓库存储的一般是历史数据。
数据库设计是尽量避免冗余，一般采用复合范式的规则来设计，数据仓库在设计时有意引入冗余，采用反范式的方式来设计。
数据库是为捕获数据而设计，数据仓库是为分析数据而设计，它的两个基本的元素是维表和事实表。
维是看问题的角度，比如时间，部门，维表放的就是这些东西的定义，事实表李放着要查询的数据，同时有维的ID。
数据仓库，是在数据库已经大量存在的情况下，为了进一步挖掘数据资源、为了决策需要而产生的，它决不是所谓的“大型数据库”。
那么，数据仓库与传统数据库比较，有哪些不同呢？
数据仓库：
面向主题的、集成的、与时间相关且不可修改的数据集合。
“面向主题的”：
数据库：
菜市场，数据仓库：
超市；
传统数据库主要是为应用程序进行数据处理，未必按照同一主题存储数据；数据仓库侧重于数据分析工作，是按照主题存储的。
这一点，类似于传统农贸市场与超市的区别一市场里面，白菜、萝卜、香菜会在一个摊位上，如果它们是一个小贩卖的；而超市里，白菜、萝卜、香菜则各自一块。
也就是说，市场里的菜（数据）是按照小贩（应用程序）归堆（存储）的，超市里面则是按照菜的类型（同主题）归堆的。
“与时间相关”：
数据库保存信息的时候，并不强调一定有时间信息。
数据仓库则不同，出于决策的需要，数据仓库中的数据都要标明时间属性。
决策中，时间属性很重要。
同样都是累计购买过九车产品的顾客，一位是最近三个月购买九车，一位是最近一年未买过，这对于决策者意义是不同的。
“不可修改”：
数据仓库中的数据并不是最新的，而是来源于其它数据源。
数据仓库反映的是历史信息，并不是很多数据库处理的那种日常事务数据（有的数据库例如电信计费数据库甚至处理实时信息）。
因此，数据仓库中的数据是极少或根本不修改的;当然，向数据仓库添加数据是允许的。
数据仓库的出现，并不是要取代数据库。
目前，大部分数据仓库还是用关系数据库管理系统来管理的。
可以说，数据库、数据仓库相辅相成、各有千秋。
补充一下，数据仓库的方案建设的目的，是为前端查询和分析作为基础，由于有较大的冗余，所以需要的存储也较大。
为了更好地为前端应用服务，数据仓库必须有如下几点优点，否则是失败的数据仓库方案。
1、效率足够高。
2、数据质量。
3、扩展性。
Mysql中delete和trncate
Mysql中有两种删除表中记录的方法：
（1）deletefrom语句
（2）trncatetable语句。
detelefrom语句可以使用where对要删除的记录进行选择。
delete语句更灵活。
truncatetable将删除表中的所有记录。
情况一：
清空表中的所有记录，可以使用下面的两种方法：
detelefromtablename；truncatetabletablename;
其中第二条记录中的table是可选的。
情况二：
删除表中的部分记录，只能使用detele语句。
deteleFROMtable1WHERE；
1、如果detele不加WHERE子句，那么它和truncatetable是一样的，但它们有一点不同，那就是detele可以返回被删除的记录数，而truncatetable返回的是0；
2、如果一个表中有自增字段，使用truncatetable和没有WHERE子句的detele删除所有记录后，这个自增字段将起始值恢复成1.如果你不想这样做的话，可以在delete语句中加上永真的WHERE，如WHERE1或WHEREtrue。
deleteFROMtable1WHERE1;
3、还有一点就是,如果要删除表中的所有数据,建议使用truncatetable,尤其是表中有大量的数据,使用truncatetable是将表结构重新建一次速度要比使用deletefrom快很多,而deletefrom是一行一行的删除,速度很慢.
总结：
detele和truncatetable的最大区别是detele可以通过WHERE语句选择要删除的记录。
但执行得速度不快。
而且还可以返回被删除的记录数。
而truncatetable无法删除指定的记录，而且不能返回被删除的记录。
但它执行得非常快。
delete:
与标准的SQL语句不同，delete支持ORDERBY和LIMIT子句。
deleteFROMusersWHEREname='nike'ORDERBYidDESCLIMIT6;
2019.10.1：
SQL语言分类：
共四类：
数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL；
1、数据查询语言DQL：
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块；
SELECT，FROM，WHERE；
2、数据操纵语言DML：
插入：
INSERT，更新：
UPDATE，删除：
DETELE；
3、数据定义语言DDL：
数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如：
CREATETABLE/VIEW/INDEX/SYN/CLUSTERE
表视图索引同义词簇
DDL操作是隐性提交的！
不能rollback，即不能返回修改前的样子；
4、数据控制语言DCL：
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。
如：
1：
GRANT：
授权。
2：
ROLLBACK[WORK]TO[SAVEPOINT]：
回退到某一点。
回滚---ROLLBACK；回滚命令使数据库状态回到上次最后提交的状态。
其格式为：
SQL>ROLLBACK;
3：
COMMIT[WORK]:
提交。
在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。
在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。
提交数据有三种类型：
显式提交、隐式提交及自动提交。
下面分别说明这三种类型。
（1）显式提交：
用COMMIT命令直接完成的提交为显式提交。
其格式为：
SQL>COMMIT；
（2）隐式提交：
用SQL命令间接完成的提交为隐式提交。
这些命令是：
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。
（3）自动提交：
若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。
其格式为：
SQL>SETAUTOCOMMITON；
主键、外键、超键、候选键：
超键：
在关系中能够唯一标识元组的属性集称为关系模式的超键。
一个属性可以作为一个超键，多个属性组合在一起也可以作为一个超键。
超键包含候选键和主键。
候选键：
是最小超键，即没有冗余元素的超键。
主键：
数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。
一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）;
外键：
在一个表中存在的另一个表的主键称此表的外键。
DISTINCT关键字，指示MySQL只返回不同的值；
SELECTDISTINCTvend_id告诉MySQL只返回不同的vend_id行；
按多个列排序：
SELECTprod_id，prod_price,prod_nameFROMproductsORDERBYprod_price,prod_name按其中两个列对结果进行排序—首先对价格，然后再按名称排序；仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。
如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。
指定排序方向：
默认为升序排序顺序，进行降序排序必须指定关键字DESC。
SELECTprod_id,prod_price,prod_nameFROMproductsORDERBYprod_priceDESC;
使用ORDERBY和LIMIT的组合，能够找出一个列中最高或最低的值。
SELECTprod_priceFROMproductsORDERBYprod_priceDESCLIMIT1;
过滤数据：
使用WHERE子句
在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。
WHERE子句在表名（FROM子句）之后给出，如下所示：
SELECTprod_name,prod_priceFROMproductsWHEREprod_proce=2.50;
返回prod_price值为2.5的行。
ORDERBY应该位于WHERE之后，否则将会产生错误；
不匹配检查，列出不是由供应商1003制造的所有产品；
SELECTvend_id,prod_nameFROMproductsWHEREvend_id<>1003;等价于！
=；
范围值查找：
为了检查某个范围的值，可使用BETWEEN操作符。
SELECTprod_name,prod_priceFROMproductsWHEREprod_priceBETWEEN5AND10;
检索价格在5美元和10美元之间的所有产品。
圆括号具有较AND或OR操作符高的计算次序。
IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。
IN取合法值的由逗号分隔的清单，全都括在圆括号中。
10.2：
聚集函数（aggregatefunction）运行在行组上，计算和返回单个值的函数。
主要5种聚合函数：
AVG（）,COUNT（）,MAX（）,MIN（）,SUM（）;
分组数据：
涉及两个新SELECT语句子句，分别是GROUPBY子句和HAVING子句。
所有的类型的WHERE子句都可以用HAVING来代替。
唯一的差别是WHERE过滤行，而HAVING过滤分组。
HAVING和WHERE的差别:
这里有另一种理解方法啊，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。
这是一个重要的区别。
请你介绍一下，数据库的三个范式：
第一范式（1NF）：
强调的是列的原子性，即列不能够再分为其他几列。
第二范式（2NF）：
首先是1NF，另外包括两部分内容，一是表必须有一个主键；二是没有包含主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
在1NF基础上，任何非主属性不依赖与其它非主属性[在2NF基础上消除传递依赖]。
第三范式（3NF）：
是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。
首先是2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。
即不能存在：
非主键列A依赖于非主键列B，非主键列B依赖于主键的情况。
查询（query）任何SQL语句都是查询，但此术语一般指SELECT语句。
利用子查询进行过滤，即内部用子查询进行嵌套；
子查询：
SELECTorder_numFROMorderitemsWHEREprod_id=’TNT2’
外部查询：
SELECTcust_idFROMordersWHEREorder_numIN（SELECTorder_numFROMorderitemsWHEREprod_id=‘TNT2’）
联结：
联结（join）是利用SQL的SELECT能执行的最重要的操作。
将数据与产品信息分开存储的利用：
因为同一供应商生产的每个产品的供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间。
如果供应商信息改变，只需改动一次即可。
如果有重复数据（即每种产品都存储供应商信息），很难保证每次输入该数据的方式都相同。
不一致的数据在报表中很难利用。
关系表的设计就是要保证把信息分解成多个表，一类数据一个表。
各表通过某些常用的值互相关联。
外键（foreignkey）外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
关系数据可以有效地存储和方便地处理。
因此，关系数据库的可伸缩性远比非关系数据库更好。
可伸缩性（scale）能够适应不断增加的工作量而不失败。
设计良好的数据库或应用程序称之为可伸缩性好（scalewell）。
联结语句：
SELECTvend_name,prod_name,pord_priceFROMvendors,productsWHEREvendors,productsWHEREvendors.vend_id=products.vend_idORDERBYvend_name,prod_name;
通过vendors表中的vend_id和products表中的vend_id联结。
笛卡尔积（cartesianproduct）：
由没有联结条件的表关系返回的结果为笛卡尔积。
检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
所以要保证所有联结都有WHERE子句。
优化查询：
使用索引，优化索引，优化SQL语句，优化表结构；
使用索引：
尽量避免全表扫描，首先应考虑在where及orderby，groupby涉及的列上建立索引。
优化SQL语句：
优化SQL语句/数据库对象：
优化表的数据类型
对表进行拆分：
可以提高表的访问效率。
有两种拆分方法：
垂直拆分：
把主键和一些列放在一个表中，然后把主键和另外的列放在另一个表中；
使用场景：
如果一个表中某些列常用，另外一些不常用，就可以垂直拆分。
水平拆分：
根据一列或者多列数据的值把数据行放到两个独立的表中。
数据库explain分析查询：
升级会员 