MySQL学习笔记

 

时间：2016-3-28 19:40

 

 

学习就怕心浮气躁，有个三脚猫的功夫，就以为什么都会了。                                                                                                                                                   ——  赠自己

 

 

 
——数据库的基本概念

一、定义：
    1、数据库指的是以一定方式存储在一起、能为多个用户提供共享，具有尽可能小的冗余度的特点，是与应用程序彼此独立的数据集合。

    2、数据库是依照某种数据结构（层次性，网状型，关系型）组织起来并存放二级存储器（外存）中的数据集合。这种数据集合具有如下特点：尽可能不重复，以最优方式为某个特定组织的多种应用服务，其数据结构独立于使用它的应用程序，对数据的增删改查由统一软件进行管理和控制。

二、数据库发展阶段：
    1、人工管理阶段
    2、文件系统阶段
        文件共享性差，不容易同步。
    3、数据库系统阶段

三、数据库种类：
    1、层次模型（树状结构）
    2、网状模型（只有概念，尚未实现）
    3、关系模型（一数据库中，一个表就是一个关系）

四、常见的关系型数据库：
    1、SqlServer：
        微软旗下数据库
        适用于Windows平台
        服务器版需要付费
    2、MySQL
        属于Oracle公司
        性能较高
        社区版免费
        体积小、速度快、总体拥有成本低
        开源
        适用平台：
            Windows
            Linux
            Unix
    3、DB2
        IBM公司旗下数据库
            IBM1970年首次提出关系型数据库模型。
            IBM1974年提出了SEQUEL语言，即SQL语言的前身。

——MySQL数据库的下载和安装


安装完成后设置数据库字符集为UTF-8：
    查看字符集:show variables like 'character%';
    修改字符集：
        打开my.ini文件，在ini文件中#表示注释。
        添加：default-character-set=utf8
                  character-set-server=utf8
    修改完成后重启服务器。

1、MySQL安装成功后会在两个目录中存储文件
    >   D:\Program Files\MySQL Server 5.6
        DBMS管理程序
    >   C:\ProgramData\MySQL Server 5.6\data
        DBMS数据库文件，卸载数据库时不会删除该目录，需要自己手动删除。

2、MySQL重要文件
    >   D:\Program Files\MySQL Server 5.6\bin\mysql.exe
        客户端程序，用来操作服务器，但必须保证服务器已经启动才能连接数据库。
    >   D:\Program Files\MySQL Server 5.6\bin\mysqld.exe
        服务器程序，必须先启动它，然后客户端才能连接服务器。
    >   D:\Program Files\MySQL Server 5.6\bin\my.ini
        服务器配置文件。

3、C:\ProgramData\MySQL\MySQL Server 5.6\data
    该目录下的每一个目录都表示一个数据库，例如该目录下有一个mysql目录，说明DBMS中有一个名为mysql的databases。
    在每个数据库目录下会有0~N个扩展名为frm的文件，每个frm文件表示一个 table，不要使用文本编辑器打开。

4、my.ini
    配置MySQL的端口：默认为3306,
    配置字符编码：
        *   [client]下配置客户端编码：default-character-set=utf8
        *   [mysqld]下配置服务器编码：character-set-server=utf8
    配置二进制数据大小上限：
        *   在[mysqld]下配置：max_allowed_packet=8M

5、导出数据库及数据
    mysqldump -uroot -pAdmin123 mydb3 >f:\sql1.sql

——MySQL数据库基本操作


1、服务器的启动与停止
    在services中启动或停止。
    1）开启服务：net start mysql
    2）关闭服务：net stop mysql
    服务器：一直处于监听状态，等待接收命令
2、MySQL客户端登录
    mysql -uroot -p123 -hlocalhost
    >   -u：表示用户名
    >   -p：表示密码
    >   -h：表示主机（IP地址）
        *   root用户可以使用任意IP，普通用户则绑定IP。
3、退出客户端
    exit、quit
3、查看数据库
    查看所有数据库：show databases;
    information_schema：提供访问数据库元数据的方式。
    mysql：与数据库系统服务有关的数据。
    performance_schema：收集服务器数据库性能的参数
    test：用户测试表。
            
查看所有数据库：show databases;
切换到当前数据库：use 数据库名;
查看当前数据库中所有表：show tables;
查询某表中的数据：select * from 表名;


——管理数据库

1、数据库相关概念
    1）数据库 DataBase
        分类：
            针对于系统数据库
                information_schema
                mysql
                performance_schema
                    用于支持数据库系统正常运行的数据库。
            用户数据库：
                针对于特定项目需求所创建的数据库
        组成：
            逻辑结构：
                数据库中文件的组织结构。
            物理结构：
                存储在磁盘上的文件结构。
    2）数据库管理系统 DBMS
        DataBase Management System
        管理数据库的平台：
        服务器
        客户端

2、管理方式
        1）可视化管理
            例如navicat管理数据库
        2）SQL语言管理
            Structured Query Language
            针对于数据库操作的语言，程序开发过程中所需要的语言。
            分类：
                DDL：Data Description Language，数据定义语言
                    用于创建，从无到有。

3、数据库的管理说明
    1）创建：create database 数据库名;    创建在默认data目录下。
    2）删除：drop database 数据库名;

——管理表

1、表的相关概念
    关系数据库（Relational DataBase, RDB）：就是基于关系模型的数据库，在计算机中，关系数据库是数据和数据库对象的结合关系型数据库基础数据是数据表。

2、实体
    数据表是由行row和列column组成的二维表，每行描述一个实体，数据表中的列通常叫做字段，他代表数据表中存储实体的共有属性。
    属性值的不同代表不同的实体。
    数据表中存储的数据就是具有不同属性的的不同实体的集合。

3、数据表的管理
    设计某类实体具有哪些属性（字段）、表结构
        类似于设计类，该类具有哪些属性。
        根据项目实际需求设计哪些表，每个表包含哪些字段。
    对属性有哪些限定条件——约束。

    数据类型：
        整型：
            int
                占4个字节。
            tinyint
                占1个字节，在数据库优化的时候可以使用tinyint。
        浮点型：
            float / double
                double(5, 2)表示最多5位整数，其中必须有2位小数，即最大值为999.99
        浮点型：
            decimal(6,2)
                用于进行十进制运算，精确度高，不会丢失二进制数据，常用于表示金额。
                一共六位数，小数占2位。
                此时decimal占8个字节。
                在设计数据库时要注意不要浪费空间。 
        日期：
            date：
                占3个字节。
                日期类型，不能保存时间
                格式：YYYY-MM-DD
                范围：1000-01-01 到 9999-12-31
                如果超出范围，则存储0000-00-00
            time：
                占3个字节。
                时间类型，不能保存日期
            datetime：
                占8个字节。
                日期、时间类型，即年月日时分秒
            timestamp：
                占4个字节，并且可以表示年月日时分秒.
                可以表示纳秒。
                ts timestamp default CURRENT_TIMESTAMP（这是一个常量，表示系统当前时间。）
                因为timestamp也可以表示年月日时分秒，并且所需空间是datetime的一半，所以在设计数据库时可以用时间戳表示时间
            year：
                表示年，占1个字节，范围是1901-2155年，如果没有则0000。
        字符：
            char(m)
                m指的是字符数，而不是字节数。
                定长字符串，数据长度不足指定长度，会自动补足到指定长度。
                最大值为255。
                一般用于保存时间等固定长度字符串。
            varchar
                可变长度字符串类型，最大值为65535。
                会单独使用一个字节保存字符串长度。
        大字符串类型（CLOB）：（MySQL独有）
            tinytext：2^8-1B  255
            text：2^16-1B  65535
            mediumtext：2^24-1B  3KB
            longtext：2^32-1B  4GB
        BLOB：（大字节类型）

    注意：数据库优化
        当每一条记录都是定长数据时，会极大的提高数据库的查找效率。
        因为每一行的长度都是固定的，所以查找时的定址、寻址特别快。
        当存在数据浪费和效率提高两种情况同时存在是，以效率为准。（不能浪费太多）
        时间与空间是一对矛盾体，无非是时间换空间，或者空间换时间。
        当数据表中的某一列影响了整体速度并且无法进行空间优化时，并且该列修改频率也不高，
        那么可以将该列单独取出存为一张表。
        在开发过程中，数据库优化往往是把频繁用到的信息存储到一张表中，优先考虑查找效率。
        不常用的信息和比较占据空间的信息，优先考虑空间占用，单独存储到另外一张表中。
        并且一般时间并不使用datetime存储，而是使用时间戳，因为datetime虽然直观，但不方便计算，影响效率。


4、SQL管理表结构
    1）建表语法
        use 数据库名;
        create table [if not exists] 表名
        (
            id int not null,
            name varchar(20) not null
        );

    2）查看表结构
        desc t1;    description描述
        查看表结构。
        show create table t1;
        查看创建表的过程。

    3）列的特征
        是否为主键
        是否为空
        是否为自动增长列
        是否有默认值

        以上特征都属于数据表的约束

    4）表字段修改
        修改字段定义：
            修改数据类型用modify
            alter table 表名
            modify 字段名 数据类型;
 
        修改字段名用change
            alter table 表名;
            change 旧字段名 新字段名 新数据类型;
 
        增加字段：
            alter table 表名 
            add 字段名 数据类型 （完整性约束条件）

            /*如果想在第一个位置增加字段，需要在数据类型后面加上first*/
            /*如果想在指定位置添加，可以使用after 字段名;   在该字段之后添加*/

        删除字段：
            alter table 表名 drop 字段名;

        删除表：
            drop table 表名;

5、管理约束
    概念：
        对一个表中的属性操作的限制叫做约束。
        约束是数据库提供的自动强制数据完整性的一种方法，它是通过定义列的取值规则来维护数据的完整性。
    数据完整性：
        实体完整性：要求数据表中不能出现重复行（完全相同数据行）
        域完整性：域，范围，其实就是属性，要求列的数据类型，取值范围，特定值等符合规定要求
        引用完整性：要求两个或两个以上表之间的关系，关系列在数值上保持一致。
        自定义完整性：针对于具体表的具体规定。
    约束类型：
        1）主键约束
            primary key
            维护实体完整性
            该字段唯一，并且不能为null
            通常是没有业务含义的编号做主键
            含有主键的表叫做主键表

        2）外键约束
            foreign key
            维护引用完整性
            不是本表的主键，但却是另一个表的主键。
        问题：

        3）唯一约束
            unique
            维护实体完整性
            和主键的区别：unique允许有且只有一个null（Oracle中允许null 重复）

        4）检查约束
            check
            维护数据完整性
            在MySQL中数据库引擎会分析check语句，但是会忽略check约束。
            所以有关字段范围的操作，可以在程序中限定。
        5）默认约束
            default
            维护域完整性
        6）非空约束
            not null
            维护域完整性
            如果不加约束，默认可以为空。
        7）自动增长列
            auto_increment
            能够自动增长的类型必须是整型
            默认从1开始
            如果想改变初始值，代码如下：
            create table 表名
            (
                id int primary key auto_increment
            )auto_increment=20160001;
            这样就将自动增长的初始值改为20160001了。
            修改约束：
                alter table 表名
                auto_increment = 1001;


6、如何设计主外键：

7、SQL管理约束
    添加约束：
        创建表时添加
            非空约束
                not null

            主键约束
                primary key，主键默认非空。
            唯一约束
                直接写
            缺省约束
                default '男'
            自动增长列
                auto_increment
                创建表后添加
                    格式
                        alter table 表名
                        add
                            constraint 约束名
                            具体约束说明;
            外键约束
                格式
                    alter table 外键表名
                    add constraint 约束名    (fk_主键表_外键表_外键)
                    foreign key(外键)references 主键表(主键)
                删除外键约束：
                    alter table 表名 drop foeign key 外键别名;


——数据库操作

    增删改中都不会出现table关键字。

    增：
        1、insert [into] <表名> [(列明)] values<(值列表)>
            < >必写、[ ] 可选，最好都写，方便移植。
            可以不指定列名，但是值列表中值的顺序必须与表中字段顺序保持一致。
            可以指定列名，并且不按照数据库表中字段顺序插入，但是值需要和指定列名保持一致。
        2、对于自动增长列的处理：
            1）可以指定自动增长列的数值。
                insert into t1 values(10,'qq',21,2);
            2）如果不指定，则按照当前列的最大值自动增加。
                insert into t1 (name,age,class)values('dd',19,5);
            3）可以以指定自动增长列，赋值时赋null，依然自动增长。
                insert into t1 (id,name,age,class) values (null,'qq',21,2);
            4）不指定列，并且自动增长赋null。
                insert into t1 values(null,'aa',21,2);
            只要插入值和插入列不匹配，就要指定列。
        3、当存在默认值时，有三种插入方式：
            1）指定全部值
            2）指定列，指定值
            3）使用default关键字来指定使用默认值。
        4、一次性插入多行
            insert into 表名 values (值列表1),(值列表2),(值列表);

    改
        修改数据
            通常根据主键来修改，因为主键是唯一的。
            语法
                修改单列值：
                    update 表名
                    set 字段名 = 修改的值
                    where 字段名 = 值;

                修改多列值：
                    update 表名
                    set 字段名1 = 值1,字段名2 = 值2
                    where 字段名 = 值;

                多条件
                    where 字段名1 = 值1 and 字段名2 = 值2; 

    删
        delete from 表名
        where 删除条件
            删除表中数据，保留表结构。
            事务提交后才生效，否则回滚。
            如果有对应的触发器，会处罚执行。

        delete from 表名
            删除表中全部数据，保留表结构，记录日志。
        
        truncate 表名
            删除表中数据，保留表结构。
            不记录日志，删除后不可恢复。
            删除后相当于保留表结构，所有状态恢复到最初。（无数据，相当于格式化）
            truncate属于DDL，数据定义语言。
    
        drop table 表名
            直接从数据库中删除表。


——单表查询

    1、查询和记录集的概念
        查询：在现有的数据表中过滤符合条件的信息。
        记录集：查询的结果通常叫做“记录集”，记录集通常是一个虚拟表。

    2、基本select查询语句
        语法格式：
                select 列名 from 表名
                where 查询条件表达式
                group by 属性名1 having 条件表达式    -- 用于分组
                order by 排序的列名 asc | desc;  -- 用于排序
        查询表中所有列：
                select * from 表名;
                查询所有列，顺序按照表的原有顺序显示。
                select 列名2,列名1 from 表名，调整列名的顺序。
        查询表中部分列：
                select 列名1,列名2 from 表名 where 条件;
                在查询所有记录的基础上过滤信息。
                条件大小顺序不能改变，必须由小到大。
                条件：
                        比较
                        指定范围：between and  /  not between and
                            可以and多个条件
                        指定集合：in  /  not in
                        匹配字符：like  /  not like
                            %：匹配任意多个字符
                            _： 匹配任意一个字符
                             [a-f]

                        是否为空值：is null  /  is not null
                        多个查询条件：and  /  or
                

        使用distinct关键字屏蔽重复数据：
                select distinct 列名 from 表名;
                    对指定列屏蔽重复值。

        使用limit关键字查询表中限定行：
                select * from 表名 limit m,n
                m是起始记录，从0开始，n为查询的记录数
                当m为3时，从第4条记录开始，显示五行。
                不指定初始位置时，默认从记录第一行开始。

            公式：（当前页-1） * 每页记录数，得出的就是起始行

    3、对查询结果进行排序
        select 列名 from order by 排序字段 排序方式
        order by 放在查询的最后面。
        order by默认升序排序。
                asc：升序    desc：降序
        还可以按多列进行排序，当其中一个序列值相同时按另一列排序（多门课程成绩）
                order by sclass,sbirthday;    -- 先按照班级排序，当班级相同时，再按照生日排。


——分组统计与多表关联查询
    1、聚合函数
        where中不能包含聚合函数。
        凡是聚合函数，返回的都是单值。
            sum
            avg
            max
            min
        以上四种聚合函数通用特点：
            select 聚合函数(列名) from 表名

            count
                统计记录数，但是不记录null。
                count(*)   count(列名)

    2、分组查询
        --分组查询只能显示分组之后的整体信息，不能显示组内部某一字段的信息。
        语法：
                select 字段列表 [聚合函数] from 表
                [where 条件]
                group by 字段列表
                [having 筛选条件]
                [order by 排序字段]

        单列分组：
                对其中一列分组，对另一列进行统计。
                查询项只包含分组列和统计列。
                例：统计教师表中各职务的平均年龄。
                        -- 统计教师表中各职务的平均年龄

        多列分组：
                对多列进行分组，对另一列进行统计。
                对分组列按顺序进行分组统计，先对tjob进行分组，再对tsex进行分组。
                select tjob,tsex,avg(tage) from t_teacher
                group by tjob,tsex;
        HAVING：    
            使用having子句对分组信息再次过滤。
            筛选分组后的信息。
            必须配合group by使用。
            having语句可以按照select字段名进行过滤，可以用聚合函数进行过滤，不可以使用别名和模糊查询进行查询。

        对分组结果进行排序：
            order by放在查询语句的最后。

        select语句中查询列必须在聚合函数中或group by中。
            
    3、条件总结
        where
            针对于表中所有信息进行筛选。
        having
            针对于分组后的信息进行筛选。

    4、多表关联查询
        1、使用多表关联查询的原因：
                查询的信息分布在多个表中。
                四个表三个连接条件，五个表四个连接条件。
        2、交叉连接
                得到的结果是一个笛卡尔积，是两个表记录的交叉乘积，列是两个列表的集合。
                一旦表名指定了别名，则全部查询必须使用别名。
        3、内连接
                语法
                        join on法
                            select 字段名
                            from 表1 inner join 表2
                            on 表1.字段名 = 表2.字段名
                            where 筛选条件
                            order by 排序列
                            条件运算符 = 或 <>
                        说明
                            列名可以试表1和表2中的任意字段。
                            若查询列出现共有字段则必须指定该字段取自哪个表，格式是：表名.列名。
                            表1.列名 = 表2.列名：使用两个表共有的字段建立联系。
                            可以指定表的别名，一旦指定别名后，所有出现表名的地方都要使用别名。
                        where条件法
                            select 字段名 from 表1,表2
                步骤总结
                    列出查询字段
                    观察查询字段在哪些表中
                    提取个表的公共字段作为连接条件
                    分析条件
        4、外连接
                左外连接
                    谁做主表，谁的信息全部显示，如果信息不匹配，则右表信息显示null。
                        语法
                            select 字段名 from 左表 left [outer] join 右表
                                on 左表.列名 条件运算符 右表.列名
                                where 条件
                            含义
                                左外连接是以左表为主表，去连接右表（从表），结果集中包含主表所有数据行，如果主表的某行在从表中没
                                有匹配时，则从表的选择列为null值。
                                例如：
                                        以学生表作为主表，连接成绩表。
                右外连接
                    主从表与左外连接相反。
                    
——子查询
    1、子查询的概念
        如果一个select语句能够返回一个单值或一列值并嵌套在一个select、insert、update或delete语句中，则称之为子查询或者
        内层查询，而包含一个子查询的语句则成为主查询或外层查询。
        执行过程：
            先执行子查询，再执行主查询。
        问题剖析：
            查询出生日期小于雷军的学生信息：
                第一步：
                        分析查询字段-学生表的所有信息字段。
                        select * from student
                第二部：
                        分析查询条件，出生日期小于雷军的出生日期。
                        where birthday < (雷军的出生日期)
                第三部：查询雷军的出生日期。
                        select birthday from student
                        where name = '雷军'
    2、子查询的分类
        1）比较子查询
            带有比较运算符的查询
                单值比较查询
                    where id = (子查询)
                批量比较子查询
                    
                    语法：
                        select 字段名 from 表名
                        where 字段名 比较运算符 any | all ( 子查询 )
                    说明：
                        在带有any或all运算符的子查询中，子查询的结果是一个集合，在使用时必须同时使用比较运算符。
                    示例：
                        在学生表中查询比2班中某一学生出生日期小的学生信息。
                            select * from student
                            where birthday < any
                            (
                                select birthday from student
                                where class = 2
                            );
        2）带有in或not in的子查询
            语法：
                select 字段名 from 表名
                where 字段名 [not] in (子查询)
                示例：
                    查询选修了课程的学生信息
                    select * from student
                    where sid in 
                    (
                        select sid from score
                    )
        3）连接查询和子查询的总结
            连接查询：
                查询的字段存在于多个表中
                    首先分析查询字段
                    再查看查询字段分布的表
                    然后找出表之间的公共字段（建立连接的条件）
                    最后再分析其他条件
            子查询：
                查询的字段在同一个表中
                    首先分析查询字段
                    再分析查询条件
                    然后找出查询条件用到的字段
                    再分析该字段关联的表


——函数
    具有特定功能的方法    
    常用在创建表和查询表中。
    
    mod(x,y)
    rand()
    round(x,y)

    curdate()
    curtime()
    now()
    datediff(expr1,expr2)

    concat(str1,str2)
    lower(str)
    upper(str)
    length(str)
    trim(str)
    replace(str,oldstr,newstr)
    substring(str,pos,len)

    database()
    version()
    user()

    常用函数
 

    字符串函数

    数学函数

        -- abs(x)

——索引

    索引管理
        概念：
                索引是建立在表上，是对数据库表上的一列或者多列进行排序的一种结构
                作用：
                        提高对数据库表查询的速度。
                        相当于根据拼音或部首查找某字在词典中的位置——词典的索引。
                        对于数据库来讲，创建索引，就是将数据库中关键字的位置建立目录，方便于查找。
                索引的存储类型：
                        BTREE：
                        HASH：
                        不同的存储引擎采用索引的存储类型不同。
                        查看数据库引擎：
                                show engines;
                        每个引擎支持的功能不同，所以索引的存储类型也不同，innodb存储类型是BTREE
        使用索引的带价：
                1、索引需要占用数据表以外的物理存储空间。
                2、创建索引和维护索引要花费时间（数据库进行的操作，而不是程序员进行的操作）
                          只要插入或者删除数据，索引都要修改。（可以全部删除，然后重新建表，速度略快）
                3、当对表进行更新操作时，索引需要被重建，降低数据的维护速度。
        索引类型：
                1、普通索引
                        可以在数据表的任意列建立该索引。
                        创建方式：
                            1）创建表时创建索引
                                create table 表名
                                (
                                    [字段说明]
                                    index [索引名] ( 列名 [长度] )
                                );
                            2）修改表时添加索引
                                alter table 表名
                                add index[索引] (列名 [长度])
                            3）创建表后创建索引
                                create index 索引名 on 表名(列名[长度])
                        注意：
                                如果要创建索引的列是char或varchar类型，长度可以小于实际长度。
                2、唯一索引
                        创建索引列的唯一值
                        相对于普通索引，唯一索引创建索引时在index前加上unique
                3、主键索引
                        主键索引是一种特殊的唯一索引，不允许为null。
                        主键索引是在给表设置主键时自动创建。
                        一个表只能有一个主键，即只能有一个主键索引。
                4、全文索引
                5、单列索引和多列索引
                        单列索引：
                                索引建立在表中的某一列上。
                        多列索引：
                                索引建立在表中的多个列上
                                例如：
                                        create index 索引名 on 表名(列名1[长度],列名2[长度])
                                        create index index_id_name on t1(id,name(2))
                                            将多个列作为一个索引，可以提高查询效率。
                6、删除索引
                    无法修改索引，只能删除索引，再创建。
                        alter table 表名
                        drop index 索引名;
                    或者
                        alter table 表名
                        drop index 索引名 on 表名;
                    例如
                        alter table t1
                        drop index index_id;
                7、建立索引的原则
                    最适合建立索引的列是where子句中的列。
                    索引列的值不相同的越多，索引效果越好
                            比如：不建议在x性别列建索引，因为重复值太多。
                    使用短索引
                            取字符类型的前几个字符。
                    利用最左前缀
                            多列索引
                            create index index_id_name on t1(id,name(2))
                                当创建了index_id_name索引时，查询id可以，查询id,name也可以。
                                只要是左边的索引列即可。
                    不要过度使用索引
                            因为创建和维护索引需要花费时间。


——视图

    视图、存储过程和自定义函数的区别：
            视图仅限于查询，而存储过程和自定义函数可以操作表。


    可以将所有信息存放到一张视图中，再由其它条件进行筛选。
    定义：
            视图是一种数据库对象，其内容由查询（来自于查询）定义
            实例：创建视图，查询学号，姓名，联系电话，班级，选课号，课程名，成绩，教师名称
                    create view view_v

            or replace 是替换当前视图。
    对视图的进一步说明
        1、视图是一个虚拟表，从一个或多个表中导出（查询）
        2、视图也可以从视图中导出。
        3、其内容由查询语句（select）定义生成
        4、在数据库中不存在视图的查询内容，只存在视图的定义，什么时候用实体，什么时候执行视图的定义。
    管理视图
        1、使用视图：对视图可以像对待表一样进行查询和修改
                查询：select * from view_v
                修改：
                        update view_v
                        set score = 65
                        where sid = 2016001;
                        因为视图是表的映射，所以修改视图相当于修改表。
                        只能修改表的原数据，不能修改计算出来的数据。
        2、查看视图定义（命令）
                desc 视图名;
                show create view 视图名;
        3、修改视图定义
                适用于初次创建和修改：
                    create or replace view 视图名
                    as
                    select 语句;
                    如果视图不存在，则创建视图，如果存在则修改视图

                必须保证视图已经存在：
                    alter view 视图名
                    as
                    select 语句;
                    仅限于修改已存在的视图。

        4、删除视图
                drop view [if exists] 视图名
                判断如果视图存在，则删除视图视图。

——存储过程


    存储过程概述
            概念：
                    存储过程是一种数据库对象，是为了实现某个特定任务，将一组预编译的SQL语句以一个存储单元的形式存储在服务器上
                    ，由用户通过指定存储过程的名字指定它。
                    MySQL5.0之前不支持存储过程。
                    示例：
                            创建一个存储过程，输入学生的学号，查询学生的详细信息。

    创建存储过程
            不带参数的存储过程
                    语法：
                            delimiter &&
                            create procedure 存储过程名()
                            reads sql data
                            begin
                                    存储过程语句
                            end &&
                            delimiter ;
                    示例：
                            创建存储过程sp_fail，查询所有选修课成绩不及格的学生学号，姓名，课程号，课程名。

                带有输入与参数的存储过程。
                        语法：
                                delimiter &&
                                create procedure 存储过程名(in 参数名 参数数据类型,......)    // 可以输入多个参数。
                                reads sql data
                                begin
                                        存储过程语句
                                end &&
                                delimiter ;

                        说明：
                                输入参数通常作为查询条件。
                                 
                        示例：
                                创建存储过程sp_stuscore输入学生学号，查询学生学号，姓名，课程名和成绩。
                                delimiter &&

                        

——触发器


    触发器的概念
            触发器是由inert update delete等事件来触发某种特定事件

    触发器的四个特性
            原子性：Atomiity
                    事务是一个完整的操作，事务的各步操作都是不可分的（原子的）；要么都执行，要么都不执行。
             一致性：Consistency
                    当事务完成时，数据必须处于一致状态。也就是说，在事务开始之前，数据存储中的数据处于一致状态。在正在进
                    行的事务中，数据可能处于不一致的状态，例如，数据可能有部分修改。然而，当事务成功完成时，数据必须再次
                    回到已知的一致状态，通过事务对数据所做的修改不能损坏数据，或者说事务不能使数据存储处于不稳定的状态。
            隔离性：Isolation
                    对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
            永久性：Durability
                    事务完成后，它对数据库的修改被永久保存，事务日志能够保持事务的永久性。
    创建触发器的语法
            语法格式
                    create trigger 触发器名
                    before | after 触发事件
                    on 表名 for each row
                    触发执行语句
            说明：
                    trigger：触发器关键字
                    before | after：指定执行语句在触发前还是触发后。
                    触发事件：insert  delete  update
                    触发执行语句：当有触发事件发生所执行的语句。
                    on表名：触发事件发生的表。
    创建触发器示例
            new表示新添加的数据行对象。
            old表示刚删除的数据行对象。
            1、增加一条选课信息后，相应的课程实际选课人数增1。
                    -- 1、增加一条选课信息后，相应的实际选课人数增加1

    删除触发器
            drop trigger 触发器名;


——MySQL保存大数据类型

标准SQL中提供了如下类型来保存大数据类型：
    BLOB：binary 字节流
    CLOB：character 字符流
    类型                        长度
tinyblob                    2^8-1B(256B)
blob                          2^16-1B(64KB)
mediumblob             2^64-1B(16MB)
longblob                   2^32-1B(4GB)

tinyclob                    2^8-1B(256B)
clob                          2^16-1B(64KB)
mediumclob             2^64-1B(16MB)
longclob                   2^32-1B(4GB)

但是在MySQL中没有提供以上字符流的四种数据类型，二十使用如下四种类型来处理大文本数据：
    tinytext、text、mediumtext、longtext

首先需要创建一张表，表中有一个mediumblob(16M)类型的字段：
create table tab_bin(
    id int primary key auto_increment,
    filename varchar(100),
    data mediumblob
);

向数据库插入二进制数据需要使用PreparedStatement为原serBinaryStream(int, InputStream)方法来完成。

还需要在my.ini中添加如下配置：
    max_allowed_packet=102400
设置最大可传输数据包 大小。

示例代码：

——MySQL事务


为了方便事务的操作，需要创建一个account表：
create table account(
    id int primary key auto_increment,
    name varchar(20),
    balance number(10,2)
);
insert into account(name, balance) values ("张三", 100000);
insert into account(name, balance) values ("李四", 100000);
insert into account(name, balance) values ("王五", 100000);

面试容易问到：
1、事务的四大特性（ACID）
    1）原子性（Atomicity）：事务中所有操作是不可再分割的原子单位，事务中所有操作要么全部执行成功，要么全部执行失败。
    2）一致性（Consistency）：事务执行后，数据库状态与其他业务规则保持一致，如转账业务，无论事务执行成功与否，参与转账的两个账号越之和应该是不变的。其它特性都是为了这一特性服务的。
    3）隔离性（Isolation）：隔离性是指在并发操作中，不同事物之间应该隔离开来，使每个并发中的实物不会相互干扰。
    4）持久性（Durability）：一旦事务提交成功，事务中所有的数据操作必须被持久化到数据库中，即使提交事务后，数据库发生崩溃，在重启数据库时，也必须能保证通过某种机制恢复数据。

2、MySQL中的事务
    在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务，如果需要在一个事务中包含多个SQL语句，那么需要开启事务和结束事务：
        *   开启事务：start transaction;
        *   结束事务：commit或rollback
    在执行SQL语句之前，先执行start transaction，这就开启了一个事务（事务的起点），然后可以执行多条SQL语句，最后要结束事务，可以使用commit提交事务，即事务中的多条SQL语句所作出的影响会被持久化到数据库中，或者使用rollback回滚，即回滚到事务的起点，之前所作的所有操作都被撤销了。

——事务隔离级别

1、事务的并发读取问题
    *   脏读：读取到另一个事务未提交的数据，即读取到了脏数据。
    *   不可重复读：两次读取内容不一致，因为另一事务对该记录做了修改。
    *   虚读：对同一张表的两次查寻内容不一致，读到另一事务已提交的数据。

    不可重复读和虚读的区别：
        *   不可重复读是读取到了另一事务的更新。
        *   虚读是读取到了另一事物的插入（MySQL中无法测试虚读）。

2、四大隔离级别
    4个等级的事务隔离级别，在相同数据环境下，使用相同的输入，执行相同的工作，根据不同的隔离级别，可以导致不同的结果，不同事务隔离级别能够解决的数据并发问题的能力是不同的。
    1）SERIALIZABLE（串行化）：
        *   不会出现任何并发问题，因为它是对同一数据的访问是串行的，非并发访问。
        *   性能最差。
    2）REPEARABLE READ（可重复读）：（MySQL默认）
        *   防止脏读和不可重复读，不能处理虚读问题。
        *   性能比SERIALIZABLE好。
    3）READ COMMITTED（读取已提交数据）：（Oracle默认）
        *   防止脏读，不能处理不可重复读，也不能处理虚读。
        *   性能比REPEATABLE READ好。
    4）READ UNCOMMITTED（读取未提交数据）：
        *   可能出现任何事务并发问题。
        *   性能最高。

    MySQL默认的隔离级别为REPEATABLE READ，可以通过下面的语句查看：
        select @@tx_isolation
    也可以通过下面语句来设置当前连接的隔离级别：
        set transaction isolationlevel [4 选 1]



——MySQL语句

    修改字段属性：
        update 表名
        set 字段名 = 需要修改的值
        where 字段名 = 值;    //如果不加限制，则会将该字段全部修改。
    
    添加记录：
        insert into 表名 (列名) values(值列表);
        列名可以省略，根据值列表进行判断。

    删除：
        delete from 表名
        where 字段 = 值;

    创建表：
        create table 表名
        (
            字段名 数据类型
        );

    查看表结构：
        desc 表名;
        show create table 表名;

    字段修改属性：
        修改字段数据类型：
            alter table 表名
            modify 字段名 数据类型;
    
    修改字段名：
            alter table 表名
            change 旧属性名 新属性名 新属性类型;
            当不指定原有字段的自增长时，自增长会丢失。

    增加字段：
        alter table 表名
        add 字段名 数据类型(完整性约束条件);
            如果想在第一个位置增加字段，需要在数据类型后面加上first
            如果想在指定位置添加，可以使用 after 字段名;    意思是在该字段后面添加。

    删除字段：
        alter table 表名
        drop 字段名;

    删除表：
        drop table 表名;

    主键约束：
        create table 表名
        (
            主键 数据类型 primary key
        );

    外键约束：
        create table 表名
        (
            外键名 数据类型,
            constraint fk_主键约束名 foreign key (外键名) references 主键表(主键)
        );

    唯一约束：
        create table 表名
        (
            字段名 数据类型 unique
        );

    默认约束：
        create table 表名
        (
            字段 数据类型 default 默认值
        );

    检查约束：
        在MySQL中数据库引擎会分析check语句，但是会忽略check约束，所以有关字段限制范围的操作，可以在程序中限定。

    非空约束：
        create table 表名
        (
            字段名 数据类型 not null
        );

    自动增长列：
        create table 表名
        (
            字段名 数据类型（必须是int型） primary key（必须是主键） auto_increment
        ) auto_increment = 10001;（可以在此处指定默认值初始值）







——MySQL入门最基本语句
    连接服务器：
        mysql -uusername -ppassword
    当连上服务器后，我们首先面对的是库，库有一个或多个，如果不知道有哪些库，需要查看全部的库，语句如下
    查询所有库：
        show databases;

    设置字符集：
        set names gbk;
        设置mysql字符集为gbk。 

   我们要想对表/行进行操作的话，首先要选库
    选库语句：
        use 库名;

    选库之后，面对的是多张表：
    查看所有表：
        show tables;

    创建一个数据库：
        create database [IF NOT EXISTS] 数据库名 [charset=字符集];    -- 中括号内容可选
        create database if not exists mydb charset=utf8;

    删除数据库：
        drop database 数据库名;

    修改数据库编码：
        alter database mydb character set utf8;

    数据库如何改名？
        MySQL中，表和列可以改名，但是database不能改名。
        修改表名
            rename table oldname to newname;
            或者：alter table 原表名 rename to 新表名;

        PHPMyAdmin：新建库，把所有表复制到新库，再删除旧库完成的。


    创建表：
        create table test
        (
            id int
        ); 

    清空表
        truncate 表名;
        truncate和delete的区别：
            truncate清空表并且保留表结构，不记录日志。
            delete只删除数据，记录日志。

    退出
        exit;
        quit;
        \c  跳出执行












——错误提示
    1366：客户端未声明字符集。
    1064：语法错误。






 


————————————————————————————————————————————————————————


查询！！！！！！！！！！！！！！！！！！

以goods表作为练习表。

——where条件查询

    比较运算符
        <    <=    =    >    >=    <>    in
    逻辑运算符
        not 或 !    or 或 ||    and 或 &&
    -- 1、查询主键为32的商品

    

    

    -- 10、取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)
        无限极分类，顶级分类，也就是该大类下面没有直接数据，而是二级分类。
    

    

    
        max()函数取最大值。
        min()函数取最小值。
        avg()统计平均值
        count(*)函数统计个数，如果count(列名)，在指定列名时则不统计null值。
        那么使用count(*)和count(1)谁更好呢？
            对于myisam引擎的数据库没有区别，这种引擎的数据库内部有一个计数器统计这行数，当需要输出行数的时候，会直接取出行数
            对于使用innodb引擎的数据库，使用count(*)直接读行数，会导致效率低下，因为innodb会一行一行的数。
    
        有如下语句：
            select goods_id,sum(goods_number) from goods;
            取出结果为：
            goods_id    sum(goods_number)
                  1                        314
            对于SQL标准来说，该语句是错误的，不能被执行，但是在MySQL中可以被执行，但是为了可移植性和规范性，不推荐使用。
    

    
    
——having分组筛选

——order by排序
    order by 列名 desc/asc

——limit限制结果条数
    limit [offset,] n（不包含offset）

 

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有