MYSQL基本知识02

一、子查询

1、where的子查询

        举个简单的例子，例如现在需要查找比最低薪资高的薪资。那么我们第一步是找到最低薪资：

        select  min(salary)  from  sal;

        我们将上面的语句记为Fun

        然后再找出比最低薪资高的薪资

        select salary from  sal where Fun

        在这里我们只需要把Fun语句的部分粘贴过来即可

这就是where的子查询

2、from子查询

可以将子查询的查询结果看做一张临时表

        举个例子，查询每个岗位平均工资的薪资等级

第一步：找出每个岗位的平均工资并且看做临时表

        select  job，avg(salary)  from emp  group by job;

第二步：将上述查询结果当做一张新表t

第三步：查询新表t的薪资等级

        select 

                t.*，s.grade

        from

                ( select  job，avg(salary)  from emp  group by job ）t

        join 

                salary,grade  s

        on

                t.avgsal between s.low and s.high;

上述from后面的语句是精髓，将查询语句指定为一张表t

3、select子查询

        略

二、union的用法

        依然举个简单的例子

        如果我需要查询job为工程师（A）和农民工（B）的人

我们可以使用

        select  name ,job from emp where job ='A' or job = 'B'

或者

        select name ,job from emp where job in ('A','B')

        但是我们要知道的是，遍历的次数是笛卡尔积，当表中的数据很多时，会十分费时。使用union可以节省时间提高效率，它的效率是A*B。语句是：

select  name,job from emp where job ='A'

union 

select name,job from emp where job='B'

三、limit的用法

        limit是将查询结果集合的一部分取出来，通常使用在分页查询中。当查询结果过多时，我们可以通过limit限制一次显示的条数（在order by后面执行）。

        语法是：limit startindex length

        表达的意思是，开始显示时的下标和所需要显示的长度。如果不加startindex，默认为0；

        所以我们通用的分页是这样的：limit  (pageNo -1)*pagesize,pagesize

   意思是，第一页时，pageNo为1，所以下标从0 开始，显示pagesize个查询结果。

                第二页时，pageNo为2，所以下标从pagesize开始，显示pageSize个结果。、

                以此类推，每页显示pagesize个结果，且不重复。

四、表的增删改

        1、表的创建（DDL语句）

          语法：create table 表名(字段名1 数据类型，字段名2 数据类型..）

                （表名建议以t_或者tbl_开始）

数据类型

varchar(255)	可变长度字符串
char(255)	定长字符串
int(11)	相当于java的int
bigint	相当于java的long
date	短日期类型
datetime	长日期类型
clob	字符大对象，最多存储4G字符串
blob	二进制大对象，一般存视频或者音频

        2、表的删除

                语法：drop table if exists  表名

        3、插入语句Insert

        语法：insert into 表名（字段名1，字段名2...）values（值1，值2...）

        数据类型和数量都要对应好，如果想要建表时给定默认值，需要default值.

                insert插入日期

        格式化数字：format（数字，'格式'）

                        select name ， format(sal,'$999.999')

        上述语句就会将sal转成后面的格式。

        str_to_date：将varchar类型转为date类型

        date_format：将date类型转换为varchar

        例如插入date类型的生日时，如果加上了单引号，那我们可以使用str_to_date函数进行转化，将生日转化为需要的date类型。语法：str_to_date（'字符串日期','日期格式'）

        MYSQL日期格式：

                %年,%m月，%d日，%h时，%m分，%s秒、

        4、update更新语句

        update语句可以将更改表值。

语法：update 表名  set  字段名1 = 值1，字段名2=值2 ...where  限制条件

        例如，只修改id=2 的行，where id =2

如果不加where的话，会对整个表进行修改！！！！

        5、delete删除语句

        之前我们学过了使用drop对整个表进行删除，现在我们学习使用delete对表中的数据进行删除。

语法：delete  from 表名  where  条件

        注意：不加条件也等于全部删除，不过不同的是，这是删除表中所有数据，但是表还在，而drop是删除整个表。

五、表的增删改查的一些技巧

        insert一次插入多条语句：

        insert  into  表名（字段名1，字段名2...）values（值1，值2...），（值3，值4...）

        快速创建表：

        create  table  emp2  as   select  * from  emp

                指的是，将emp中查询的所有内容都当做emp2的内容。

        将查询结果插入到表中：

                insert  into  表1  select *from  表2

        指的是将对表2 的查询结果插入表1中

        快速删除表中数据：因为delete是DML语句，删除表中数据较慢，它的原理是先将每个数据内容抹除，但是空间不释放，删完是可以撤销的。

        快速删除语句truncate语句：truncate table  表名

        它和drop的区别在于：drop是删除表，truncate和delete是删除表中的数据。

六、约束

        1、约束分类和简单介绍

                创建表加入约束（constraint）

                常见约束：非空约束（not null）

                唯一性约束（unique）

                主键约束（primary key）

                外键约束（foreign key）

                检查约束（check）（mysql不支持，oracle支持）

介绍：

                非空约束（Not null）没有表级约束

                非空约束约束的字段不可以为空！

                Create table t_vip（

                Id int，

                Name  varchar（255） not null

                ）；

         

                唯一性约束（unique）

                唯一性约束约束的字段不能重复，但是可以为null

                Create table t_vip（

                Id int，

                Name  varchar（255） unique

                ）；

                联合唯一性，比如要两个字段联合具有唯一性，如果分别在两个字段后加unique，是各自唯一

                Create table t_vip（

                Id int，

                Name  varchar（255） ，

                unique（id，name）约束没添加在列后面，称为表级约束

                ）；

                主键约束（primary key）！！！！

                主键约束：约束的一种

                主键字段：该字段上添加了主键约束，该字段叫主键字段

                主键值：主键字段的每一个值都叫主键值

                主键值是每一行 记录的 唯一标识，任何一张表都有主键，主键值不可以为null，不可以重复

                Create table t_vip（

                Id int primary key，

                Name  varchar（255） unique

                ）；

添加主键约束也可以表级约束 （不建议使用）

              一张表主键约束只能添加一个！！！！！！

              主键值建议使用int ，bigint，char类型，最好定长

        主键除了单一主键和复合主键，还可以分为自然主键和业务主键

        自然主键：主键是一个自然数，和业务没关系

        业务主键：主键值和业务紧密相关，例如拿银行卡账号做主键值

        实际开发中使用使用自然主键多一些

        主键值可以使用auto_increment自增

                外键约束：

                当cno字段没有任何约束的时候可能会导致数据无效

                方法：foreign key（字段名1） reference  表2（字段2）

                也就是说字段名1的值只可以是字段2中的值

                注意，reference后的表时父表，前的是子表，

                先删除子，再删除父

                先创建父，再创建子

                子表中的外键引用父表的某个字段，被引用的字段必须是主键吗

                答案是不一定！但是至少拥有unique约束，具有唯一性

                外键字段可以为null

七、存储引擎

        什么是存储引擎，有什么用？

                存储引擎是MYSQL特有术语。存储引擎实际上是一个表存储或组织数据的方式。

        怎么给表指定存储引擎？

                Show create table  表名（可以显示当时建表的时候的语句）

                可以在建表的时候指定存储引擎，可以在小括号的外表使用ENGINE指定存储引擎，使用CHARSET指定这张表的字符编码

                例如engine=InnoDB default charset = utf-8        

查看MYSQL支持的引擎，show engine \G.

可以被转换为压缩、只读表来节省空间

对于一张表，只要是主键，或者加有unique约束的字段会自动创建索引

InnoDB存储引擎： 是一个重量级的存储引擎。支持数据库崩溃后自动恢复机制，非常安全。

Memory存储引擎：

八、事务

        什么是事务：事务是一个完整的业务逻辑

                这些操作是一个最小工作单元，要么同时成功，要么同时失败，不可再分

                和事务有关的语句是DML语句（只有insert、delete和update）。

                一个事务就是一堆DML同时成功或者失败

                事务怎么做到同时成功和失败？

                InnoDB存储引擎提供了一组记录事务性活动的日志文件

                事务执行过程中，每一条DML语句都会记录到事务性活动的日志文件中

                提交事务：清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中，提交事务标志着事务的结束，并且使一种全部成功的结束

                回滚事务：将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件，也标志着事务的结束，但是都是失败的结束

                怎么提交和回滚事务：

                提交：commit

                回滚：rollback（回滚到上一次提交点）

                回滚为了防止默认提交，需要先加上start transaction

                自动提交不符合开发习惯。

                事务特性

                A：原子性：事务是最小工作单元，不可再分

                C：一致性：所有事务要求在同一个事务中，所有操作必须同时成功，或同时失败

                I：隔离性：A事务和B事务具有一定的隔离。

                D：持久性：事务最终结束的保障，事务提交就相当于将没有保存的数据保存到硬盘上。

                事务隔离等级：

                读未提交：read uncommitted（最低级别）

                        事务A可以读取到事务B未提交的数据，问题是脏读现象

                读已提交：read committed

                        事务A只能读取到事务B提交之后的数据，解决脏读现象，问题是不可重复读取数据

                可重复读：repeatable read（MYSQL默认）

                        事务A开启之后，不管多久，每一次在事务A中读取到的数据是一致的，即使事务B将数据修改，并且提交，事务A读取到的数据没有发生改变，可重复读。问题是可出现幻影读，读取的都是刚开启事务的数据。

                序列化/串行化：serializable（最高级别）

                最高隔离级别，效率最低，解决了所有问题

                        表示事务排队，不能并发。

                查看隔离级别：select @@表名

                设置全局隔离级别：set global transaction level read uncommitted，设置后退出来

九、索引

        索引是在数据库表的字段上添加的，为了添加查询效率而存在的一种机制，一张表的一个字段可以添加一个索引，多个字段联合也可以添加索引。相当于一本书的目录，是为了减小扫描范围而存在的一种机制。

        对于字典来说，查找某个字段，有两种方式：

                一页一页找，直到找到，属于全字典扫描

                通过索引找到大概位置，然后定位到这个位置，做局域性扫描。

        索引实现原理：MYSQL中，索引是一个单独的对象，不同的引擎以不同的形式存在，在MYISAM中，索引存储在一个.MYI文件中，在InnoDB中，索引存储在一个逻辑名称叫tablespace的当中，索引在MYSQL中都是以一个树的形式存在（自平衡二叉树B树）

        添加索引的条件：（1）数据量庞大（2）这个数据段经常被扫描，出现在where后面（3）在字段很少DML操作（Insert、delete、update）

        索引的创建和删除：create index  索引名 index on 表名（字段名）

                                        Drop index 索引名  index on 表名

                            查看一条SQL语句是否用到索引，只需要在语句前加explain

        索引的失效：尽量避免模糊查询中以百分号开始， 因为这样就无法使用索引查询

        使用or的时候会失效，如果使用or那么要求or的两边条件字段都有索引，才可使用索引

        使用复合索引时，如果没有使用左侧的列查找，那么索引失效

                （复合索引：两个字段或者更多字段联合起来添加一个索引）

                在where中，索引列参加了数学运算，索引失效

                在where中，索引列使用了函数

                索引分为：

                        单一索引：一个字段添加索引

                复合字段：两个或多个字段添加索引

                主键索引：主键上添加索引

                唯一性索引：具有unique约束的字段添加索引（用处不大）

十、视图

                视图:视图（View）：站在不同角度看待同一份数据

                创建视图：create view  视图名 + as select * from  emp

                对视图对象的增删改查会影响原表

                DBA常用命令：

                        Create user  用户名  identified  by 密码

                        导出：mysql  数据库名> 路径  -uroot -p密码（在dos窗口运行）

                        可以通过source命令导入

十一、范式

        范式共有三种

                第一范式：第一张表必须有主键，每一个字段原子性不可再分

                第二范式：建立在第一张表范式基础上，所有非主键字段完全依赖主键，不要产生部分依赖

                第三范式：建立在第二范式基础之上，所有非主键字段直接依赖主键，不要产生传递依赖        

        按照三范式走，可以避免表中数据的冗余，空间的浪费。

        第一范式（核心）

                必须要定义一个不相同的字段为主键，不能将多个属性的内容放入一个字段

        第二范式

                建立在第一范式基础之上,不能使用多个字键联合去对应多个不同类型的字段

                比如:学生编号+教师编号作为联合主键去对应学生姓名和教师姓名会出现大量的冗余

        口诀:多对多，三张表，关系表两个外键

        第三范式

               第三范式要求不要产生传递依赖.口诀：一对多，两张表。多的表一个外键

                数据库三段式是理论上的.实践与理论有偏差,有的时候会拿空间浪费去换取速度,最终的目的是满足客户的需求