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语言的前身。

五、从三个方面学习数据库

1、数据库是如何存储数据的

字段记录表约束（主键、外键、唯一键、非空、check、default、触发器）

2、数据库是如何操作数据的

insert、update、delete、T-Sql、存储过程、函数、触发器

3、数据库是如何显示数据的

select（重点的重点）

——数据库是如何解决数据存储问题的

表的相关数据

字段（列）

一个事物的某一个静态特征（属性）。

记录（行）

字段的组合，表示的是一个具体的事物

表

记录的组合，表示的是同一类型事物的集合

表和字段、记录的关系

字段表示的是事物的属性

记录表示的是事物本身

表是事物的集合

列

字段的另一称谓

属性（列）

字段的另一种称谓

元组（行）

行、记录的另一种称谓

——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，数据定义语言
                  用于创建，从无到有。

对数据库或表的结构进行操作。

    DML：Data Manipulation Language，数据操作语言
                  操作数据库，例如增删改。
                  insert delete update

DQL：Data Query Language，数据查询语言
select 查询

        DCL：Data Control Language，数据控制语言
                  对用户的创建及授权。
                  revoke create table from user1

DAC：Data Administrate Command，数据管理命令。

TCC：Thing Control Command，事务控制命令。

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 重复）

一个表中可以有多个unique列。

        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、如何设计主外键：

create table product

(

pid int not null default 0,

name varchar(10) not null default '',

price int,

store int

);

create table sale

(

sid int not null default 0,

pid int not null default 0,

salecount int default 0,

saledate date default '0000-00-00'

);

-- 创建外键表

商品表中pid是商品编号，应该是唯一的，在商品表中pid应该设置为主键，

在销售表中，只要迈出一件商品，就需要在销售表中添加一条记录，因为一个商品可以卖多次，

所以该表中pid允许重复，所以pid不能作为该表的主键，但是他在商品表中作为主键，

所以pid可以作为sale表中的外键。

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

一般不指定列为null值，因为不容易比较与查询。

因为null是一种类型，比较时只能用专门的is null和is not null进行比较，费时费力，如果使用运算符进行比较，则一律返回null，效率不高且不利于数据库优化（影响索引效果）。
因此在建表时，一般指定not null default ’ ’ 或者default 0。

            主键约束
                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]

可以是字符列[a-f]中的任意一个字符。

--查询ename字段中第二个字符是A到F中任意一个单一字符的员工信息。

select * from emp

where ename like '_[A-F]%'

[a,f]

可以是a或f的单一字符。

--查询ename字段中第二个字符是A或F的员工信息。

select * from emp

where ename like '_[A,F]%'

[^a-c]

不在字符列中的任意一个字符。

--查询ename字段中第二个字符不在A到F的范围内的员工信息。

select * from emp

where ename like '_[A-F]%'

注意：匹配的条件必须用单引号括起来。（单引号表示字符串，双引号表示一个对象的名字）

--如果要查询% _ 通配符等特殊字符，可以使用：

select * from emp

where ename like '%\%%' escape '\'

--其中escape关键字将'\'转换成“转义字符”，使 '\' 之后的字符不再是本来含义。

                      是否为空值：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 排序字段]

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

select tjob,avg(tage) from t_teacher

                        group by tjob;
                      统计学生表中各班级人数
                          -- 统计学生表中各班级人数

select sclass,count(*) from t_student

group by sclass

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

having中只能查询组的整体信息，但不能查询组的详细信息。
where必须写在having前面，顺序不可颠倒，否则运行出错。

      对分组结果进行排序：
          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()

  常用函数

-- database()

-- 返回当前数据库名

select database();

-- version()

-- 返回当前数据库版本

select version();

-- user()

-- 返回当前登录用户名

select user();

-- inet_aton(ip)

-- 返回IP地址的数字表示形式

select inet_aton('192.168.1.127');

-- inet_ntoa(ip)

-- 返回数字代表的IP地址

select inet_ntoa(3232235903);

-- password(str)

-- 返回字符串str的加密版本，加密是单向的（不可逆），适用于MySQL数据库的用户密码加密，41位固定长度。

select password('aaa');

-- md5(str)

-- 返回字符串str的MD5值，该值以32位十六进制数字的二进制字符串的形式返回。

select md5('aaa');

字符串函数

-- concat(str1,str2)

-- 连接字符串，返回一个子串。

select concat('aaa','222');

-- insert(str,pos,len,newstr)

-- 将字符串str从第pos位置开始的len个字符替换为新串newstr

select insert('aaaaaa',1,4,'ss')

-- 如果新串长度不足len，则将len长度的字符串全部替换为newstr

select insert('aaaaaa',1,4,'ss')

str = ssaa

-- 如果newstr长度超过len，则将newstr全部插入指定位置，即使超出长度。

select insert('aaaaaa',1,4,'sssss')

str = sssssaa

-- lower(str)

-- 转换成小写

select lower('AAA');

-- upper(str)

-- 转换成大写

select upper('aaa');

-- length(str)

-- 获取字符串长度

select length('aa');

-- char_length(str)

-- 返回字符串str的长度

select char_length('aa');

-- lpad(str,len,padstr)

-- 返回字符串str，其左边由字符串padstr填补到len字符长度。

select lpad('aaaaa',5,'c');

-- 当str不满足len长度时，才会使用padstr填补，如果str超出len长度范围，则不进行填补。

-- rpad(str,len,padstr)

-- 与lpad相反。

-- trim(str)

-- 去掉字符串两边的空格。

select trim(' a');

-- repeat(str,count)

-- 返回str重复count次的结果。

select repeat('a',5);

-- replace(str,from_str,to_str);

-- 用字符串to_str替换字符串str中所有的字符串from_str

select replace('aabbccdd','bb','ss');

-- substring(str,pos,len)

-- 返回从字符串str的pos位置起len个字符长度的子串。

select substring('aaabbbccc',3,2);

时间函数

-- curdate()

-- 返回当前日期年月日

select curdate();

-- curtime()

-- 返回当前时间时分秒

select curtime();

-- now()

-- 返回当前的日期和时间

select now();

-- week(date)

-- 返回指定日期为一年中的第几周

-- 其中date可以是date time datetime timestamp

select week(timestamp(now()));

select timestamp(curdate())

-- year(date)

-- 返回指定日期的年份

select year(now());

-- hour(time)

-- 返回日期的小时值

select hour(now());

-- minute(time)

-- 返回time的分钟值

select minute(now());

-- monthname(date)

-- 返回date的月份名

select monthname(now());

-- date_format(date,fmt)

-- 返回按字符串fmt格式化日期date的值

-- %a：缩写星期名

-- %b：缩写月名

-- %d：日（05,06）

-- %e：日（5,6）

-- %H：24小时制（05,06）

-- %h：12小时制（05，06）

-- %k：24小时制（5,6）

-- %l：12小时制（5,6）

-- %s：秒

-- %S：秒

-- %Y：年，4位

-- %y：年，2位

select date_format(now(),'%yy年%M月%d日');

-- date_add(date,interval exp type)

-- 返回一个日期或时间值加上上一个时间间隔的时间值。

-- year：年 -- YY

-- month：月 -- MM

-- day：日 -- DD

-- hour：时 -- hh

-- minute：分 -- mm

-- second：秒 -- ss

select date_add(now(),interval 1 year);

date = 2017-04-05 12:29:19

-- datediff(expr1,expr2)

-- 返回起始时间expr1和结束时间expr2之间的天数。

select datediff('2012-08-08',now());

-- 注意：expr必须是标准时间格式。

数学函数

-- abs(x)

-- 返回x的绝对值

select abs(-1);

-- ceil(x)

-- 返回不小于x的最小整数值

select ceil(3.8);

-- floor(x)

-- 返回不大于x的最大整数值

select floor(3.9);

-- mod(x,y)

-- 返回x/y的模（取余）

select mod(5,3);

select round(1.5,0); 该方法可以四舍五入取整数。

-- rand()

-- 返回一个0-1.0之间的随机浮点数，(0 <= x <=1.0)

select rand();

-- round(x,y)

-- 返回参数x的四舍五入的y位小数的值

-- 只四舍五入小数位，不取整

select round(1.2338,3);

-- truncate(x,y)

-- 返回数字x截断为y位小数的结果

select truncate(1.222333,5);

——索引

索引管理
      概念：
              索引是建立在表上，是对数据库表上的一列或者多列进行排序的一种结构
              作用：
                      提高对数据库表查询的速度。
                      相当于根据拼音或部首查找某字在词典中的位置——词典的索引。
                      对于数据库来讲，创建索引，就是将数据库中关键字的位置建立目录，方便于查找。
              索引的存储类型：
                      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

select t_student.sid,sname,stelephone,sclass,t_score.cid,cname,score,tname

from t_student,t_score,t_course,t_teacher,t_teachcourse

where t_student.sid = t_score.sid and t_score.cid = t_course.cid

and t_course.cid = t_teachcourse.cid and t_teachcourse.tid = t_teacher.tid

                    select * from view_v
目的：
          用于集中、简化和定制显示数据库中的数据信息。
          增加数据的安全性。
          提高表的逻辑独立性。
              数据的修改不会影响视图定义。
创建视图
          T-SQL语句
          语法：
               create [or replace] view 视图名
              as
              <select 语句>

          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] 视图名
              判断如果视图存在，则删除视图视图。

索引练习-student数据库

1、在t_student 表中的sname列创建唯一索引

create index index_sname on t_student(sname(2));

alter table t_student

add index ss (sname(2));

2、在t_student表中的sname和stelephone列创建多列索引

3、在t_teacher表中的tname创建普通索引

4、在t_course表中的cname列创建唯一索引

5、删除以上索引

drop index index_sname on t_student;

alter table t_student

drop index index_sname;

视图练习-student数据库

1、创建视图

a）创建视图view_selStu,能够显示选修课程的学生的学号，姓名，选修科目代号,科目名称，科目成绩

create view view_selstu

select t_student.sid,t_student.sname,t_course.cid,cname,score

from t_student,t_course,t_score

where t_student.sid = t_score.sid

and t_score.cid = t_course.cid;

b）创建视图view_selSubject,能够显示选修科目代号，科目名称和科目成绩

create view view_selstu

select t_student.sid,t_student.sname,t_course.cid,cname,score

from t_student,t_course,t_score

where t_student.sid = t_score.sid

and t_score.cid = t_course.cid;

c）创建视图view_execllent,能够显示选修课成绩>85的学生的学号，姓名，选修科目名称，成绩

create view view_execllent

select t_student.sid,sname,cname,score

from t_student,t_course,t_score

where t_student.sid = t_score.sid

and t_score.cid = t_course.cid

and score > 85;

d）创建视图view_fail,能够显示选修课成绩不及格的学生学号，姓名，班级，课程号，课程名，成绩信息

create view view_fail

select t_student.sid,sname,sclass,t_course.cid,cname,score

from t_score,t_student,t_course

where t_student.sid = t_score.sid

and t_score.cid = t_course.cid

and score < 60

2、通过所创建的视图查询数据信息

a）在view_execllent 中查询选修课成绩在85-95的信息

select * from view_execllent

where score between 85 and 95;

b）在view_fail中查询各班不及格的成绩信息

select * from view_fail

where score < 60;
c）在view_selSubject中统计各科目的最高成绩，最低成绩，平均成绩

select max(score) '最高成绩',min(score) '最低成绩',avg(score) '平均成绩'

from view_selsubject

3、通过视图修改数据

a）修改view_fail，将某一不及格成绩信息修改成60分

update view_fail

set score = 60

b）修改view_selStu，修改某一学生的选修科目代号及科目名称

update view_selstu

set cname = '2'

where cid = 2

4、修改视图定义

a）修改视图view_execllent, 能够显示选修课成绩>85的学生的学号，姓名，选修科目，成绩，科目老师

alter view view_execllent

select t_student.sid,sname,cname,score,tname

from t_student,t_score,t_course,t_teachcourse,t_teacher

where t_student.sid = t_score.sid

and t_score.cid = t_course.cid

and t_course.cid = t_teachcourse.cid

and t_teachcourse.tid = t_teacher.tid

and score > 85;

b)修改视图view_selStu,能够显示选修课程的学生的学号，姓名，选修科目代号,科目名称，科目成绩，班级，教师姓名

create or replace view view_selstu

select t_student.sid,sname,t_course.cid,cname,score,sclass,tname

from t_student,t_course,t_score,t_teachcourse,t_teacher

where t_student.sid = t_score.sid

and t_score.cid = t_course.cid

and t_course.cid = t_teachcourse.cid

and t_teachcourse.tid = t_teacher.tid

5）删除视图：将以上某个视图删除

drop view if exists view_selstu

——存储过程

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

delimiter && //分隔符，当下一次遇到&&时就会结束可以使用其他字符例如：//

create procedure sp_stu(in inputsid int ) in 输入 int 参数类型

reads sql data

begin

select * from t_student

where sid = inputsid;

end &&
delimiter; // 将分隔符再次设为分号 ;

                            call sp_stu(2016003);      // 使用存储过程

                          对于 select * from where sid = 2016001，数据库每次执行都需要分析查询语句，转成执行。
                          而对于存储过程，在服务器已经编译好，不需要再去分析语句。

          意义：
                      1、存储过程只在创建时进行编译，以后执行都不需要编译，而一般的SQL语句执行一次就需要编译一次，所以使用存储
                             过程会提高执行效率。
                      2、存储过程可以重复使用，可以减少数据库开发人员的工作量。

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

delimiter &&

create procedure sp_fail()

reads sql data

begin

select t_student.sid,sname,t_course.cid,cname from t_student,t_course,t_score

where t_student.sid = t_score.sid

and t_score.cid = t_course.cid

and score < 60;

end &&

delimiter ;

执行：call sp_fail();

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

                      说明：
                              输入参数通常作为查询条件。

                      示例：
                              创建存储过程sp_stuscore输入学生学号，查询学生学号，姓名，课程名和成绩。
                            delimiter &&

create procedure sp_stuscore(in inputsid int)

reads sql data

begin

select t_student.sid,sname,cname,score

from t_student,t_course,t_score

where t_student.sid = t_score.sid

and t_score.cid = t_course.cid

and t_student.sid = inputsid;

end &&

delimiter ;

                      执行：call sp_stuscore(2016001)

                带输出参数的存储过程：
                      语法：
                            delimiter &&

create procedure sp_phone(in inputsname varchar(20),out otelephone varchar(11))

reads sql DATA

begin

select stelephone into otelephone from t_student -- 将查找到的stelephnoe赋值给otelephone

where sname = inputsname;

end&&

delimiter;

-- 调用

call sp_phone('马云',@telephone);

-- 输出

select @telephone;

示例：
-- 定义存储过程sp_score，输入学生学号和课程号，返回学生的成绩

delimiter &&

create procedure sp_score(in inputsid int,in inputcid int,out outscore int)

reads sql data

begin

select score into outscore

from t_student,t_score

where t_score.sid = t_student.sid

and inputsid = t_student.sid

and inputcid = t_score.cid;

end &&

delimiter;

drop procedure sp_score;

-- 调用

call sp_score(2016001,2,@score);

-- 输出

select @score;

银行转账示例：

-- 创建银行表

create table bank

(

id int not null primary key,

name varchar(5) not null default'',

balance float not null default 0

);

insert into bank values(10001,'张三',100);

insert into bank values(10002,'李四',200);

-- 创建存储过程

delimiter &&

create procedure bank_test(in inputid int,in outputid int, in money float)

modifies sql data // 此处使用midifies 因为需要修改多个内容

begin

-- 转入账户

update bank

set balance = balance + money

where id = inputid;

-- 转出账户

update bank

set balance = balance - money

where id = outputid;

end &&

delimiter;

call bank_test(10001,10002,100);

select * from bank;

删除存储过程：
drop procedure 存储过程名;

——触发器

触发器的概念
          触发器是由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

create trigger tr_choose

after insert

on t_score for each row

-- 插入一条选课信息后需要知道选课号是多少

update t_course

set realcount = realcount + 1

where cid = new.cid

select * from t_course

-- 使用触发器

insert into t_score (sid,cid)values(2016001,1)

2、删除一条选课信息后，相应的课程实际选课人数减1

create trigger tr_del

after delete

on t_score

for each row

update t_course

set realcount = realcount -1

where cid = old.cid;

select * from t_course;

select * from t_score

delete from t_score

where cid = 1 and sid = 2016001;

删除触发器
          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
设置最大可传输数据包大小。

示例代码：

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.InputStream;

import java.io.OutputStream;

import java.sql.Blob;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import javax.sql.rowset.serial.SerialBlob;

import org.apache.commons.io.IOUtils;

import org.junit.Test;

import sun.nio.ch.IOUtil;

import com.wyc.demo03.JDBCUtils;

* 2016年12月1日15:45:48

* 向数据库中插入大文本数据

public class Demo04 {

* 把mp3保存到数据库中

@Test

public void fun1() throws Exception {

* 1、得到Connection对象 2、给出SQL模板，创建pstmt 3、设置SQL模板中的参数

* 4、调用pstmt的executeUpdate()方法执行SQL语句

Connection conn = JDBCUtils.getConnection();

String sql = "insert into tab_bin values(?,?,?)";

PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, 1);

pstmt.setString(2, "Alan Walker - Fade.mp3");

* 得到Blob 1、现把文件变成byte数组 2、再使用byte数组创建Blob

// 使用字节数组创建Blob

byte[] bytes = IOUtils.toByteArray(new FileInputStream("F:/Alan Walker - Fade.mp3"));

// Blob是一个接口，可以使用它的实现类来创建对象

Blob blob = new SerialBlob(bytes);

// 设置参数

pstmt.setBlob(3, blob);

pstmt.executeUpdate();

}

* 从数据库中取出mp3

@Test

public void fun2() throws Exception {

// 得到Connection对象

Connection conn = JDBCUtils.getConnection();

// 给出select模板，创建pstmt

String sql = "select * from tab_bin";

PreparedStatement pstmt = conn.prepareStatement(sql);

// pstmt执行executeQuery方法，执行查询，得到ResultSet

ResultSet rs = pstmt.executeQuery();

// 获取第三列名为data数据

if (rs.next()) {

Blob blob = rs.getBlob("data");

* 将Blob保存到硬盘 1、通过Blob得到输入流对象 2、自己创建输出流对象 3、把输入流的数据写入到输出流中

InputStream in = blob.getBinaryStream();

OutputStream out = new FileOutputStream("D:/Alan Walker - Fade.mp3");

IOUtils.copy(in, out);

}

——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语句

建表

通过图形化界面建表。

通过命令建表：

create table Course

(

Cou_name nvarchar(50) not null,

Cou_id int not null primary key,

Cou_tercher nvarchar(20) not null

)

create table Student

(

Stu_id int not null primary key,

Stu_name nvarchar(50) not null,

Stu_sex nchar(2) not null,

Stu_age int not null,

Cou_id int foreign key references Course(Cou_id) not null

)

create table 最后一个字段的后面建议不要写逗号。

references的作用是外键表创建外键时引用主键表的主键。

  修改字段属性：
      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表作为练习表。

-- 创建goods表

create table goods

(

goods_id mediumint(8) unsigned primary key not null auto_increment,

cat_id smallint(5) unsigned not null default '0',

goods_sn varchar(60) not null default '',

goods_name varchar(120) not null default '',

click_count int(10) unsigned not null default '0',

goods_number smallint(5) unsigned not null default '0',

market_price decimal(10,2) unsigned not null default '0.00',

shop_price decimal(10,2) unsigned not null default '0.00',

add_time int(10) unsigned not null default '0',

is_best tinyint(1) unsigned not null default '0',

is_new tinyint(1) unsigned not null default '0',

is_hot tinyint(1) unsigned not null default '0'

);

-- 将shop.goods表中的数据插入testshop.goods表

insert into testshop.goods

select goods_id,cat_id,goods_sn,goods_name,click_count,goods_number,market_price,shop_price,add_time,is_best,

is_new,is_hot from shop.goods;

goods_id cat_id goods_sn goods_name click_count goods_number market_price shop_price add_time is_best is_new is_hot

| 1 | 4| ECS000000 | KD876 | 7 | 1 | 1665.60 | 1388.00 | 1240902890 | 1 | 1 | 1 |

| 3 | 8| ECS000002 | 诺基亚原装5800耳机 | 3 | 24 | 81.60 | 68.00 | 1241422082 | 0 | 0 | 0 |

| 4 | 8| ECS000004 | 诺基亚N85原装充电器| 0 | 17 | 69.60 | 58.00 | 1241422402 | 0 | 0 | 0 |

| 5 |11| ECS000005 | 索爱原装M2卡读卡器 | 3 | 8 | 24.00 | 20.00 | 1241422518 | 1 | 1 | 0 |

| 6 |11| ECS000006 | 胜创KINGMAX内存卡 | 0 | 15 | 50.40 | 42.00 | 1241422573 | 0 | 0 | 0 |

| 7 | 8| ECS000007 | 诺基亚N85原装立体声耳机HS-82 | 0 | 20 | 120.00 100.00 | 1241422785 | 0 | 0 | 0 |

| 8 | 3| ECS000008 | 飞利浦9@9v | 9 | 1 | 478.79 | 399.00 | 1241425512 | 1 | 1 | 1 |

| 9 | 3| ECS000009 | 诺基亚E66 | 20 | 4 | 2757.60 | 2298.00 | 1241511871 | 1 | 1 | 1 |

|10 | 3| ECS000010 | 索爱C702c | 11 | 7 | 1593.60 | 1328.00 | 1241965622 | 0 | 0 | 1 |

|11 | 3| ECS000011 | 索爱C702c | 0 | 1 | 0.00 | 1300.00 | 1241966951 | 0 | 0 | 0 |

|12 | 3| ECS000012 | 摩托罗拉A810 | 13 | 8 | 1179.60 | 983.00 | 1245297652 | 0 | 1 | 0 |

|13 | 3| ECS000013 | 诺基亚5320 XpressMusic |13| 8 | 1573.20 1311.00 | 1241967762 | 0 | 0 | 1 |

|14 | 4| ECS000014 | 诺基亚5800XM | 6 | 1 | 3150.00 2625.00 | 1241968492 | 0 | 0 | 1 |

|15 | 3| ECS000015 | 摩托罗拉A810 | 8 | 3 | 945.60 | 788.00 | 1241968703 | 0 | 1 | 1 |

|16 | 2| ECS000016 | 恒基伟业G101 | 3 | 0 | 988.00 | 823.33 | 1241968949 | 0 | 0 | 0 |

|17 | 3| ECS000017 | 夏新N7 | 2 | 1 | 2760.00 | 2300.00 | 1241969394 | 1 | 0 | 1 |

|18 | 4| ECS000018 | 夏新T5 | 0 | 1 | 3453.60 | 2878.00 | 1241969533 | 0 | 0 | 0 |

|19 | 3| ECS000019 | 三星SGH-F258 | 7 | 12 | 1029.60 | 858.00 | 1241970139 | 1 | 1 | 1 |

|20 | 3| ECS000020 | 三星BC01 | 14 | 12 | 336.00 | 280.00 | 1241970417 | 1 | 1 | 1 |

|21 | 3| ECS000021 | 金立 A30 | 4 | 40 | 2400.00 | 2000.00 | 1241970634 | 0 | 0 | 0 |

|22 | 3| ECS000022 | 多普达Touch HD | 15 | 1 | 7198.80 | 5999.00 | 1241971076 | 1 | 1 | 0 |

|23 | 5| ECS000023 | 诺基亚N96 | 17 | 8 | 4440.00 | 3700.00 | 1241971488 | 1 | 1 | 0 |

|24 | 3| ECS000024 | P806 | 35 | 100 | 2400.00 | 2000.00 | 1241971981 | 1 | 1 | 1 |

|25 |13| ECS000025 | 小灵通/固话50元充值卡 | 0 | 2 | 57.59 | 48.00 | 1241972709 | 1 | 0 | 1 |

|26 |13| ECS000026 | 小灵通/固话20元充值卡 | 0 | 2 | 22.80 | 19.00 | 1241972789 | 0 | 0 | 1 |

|27 |15| ECS000027 | 联通100元充值卡 | 0 | 2 | 100.00 | 95.00 | 1241972894 | 1 | 1 | 1 |

|28 |15| ECS000028 | 联通50元充值卡 | 0 | 0 | 50.00 | 45.00 | 1241972976 | 0 | 0 | 1 |

|29 |14| ECS000029 | 移动100元充值卡 | 0 | 0 | 0.00 | 90.00 | 1241973022 | 1 | 0 | 1 |

|30 |14| ECS000030 | 移动20元充值卡 | 1 | 9 | 21.00 | 18.00 | 1241973114 | 1 | 0 | 1 |

|31 | 3| ECS000031 | 摩托罗拉E8 | 5 | 1 | 1604.39 | 1337.00 | 1242110412 | 0 | 0 | 0 |

|32 | 3| ECS000032 | 诺基亚N85 | 9 | 4 | 3612.00 | 3010.00 | 1242110760 | 0 | 1 | 1 |

——where条件查询

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

select * from goods

where goods_id = 32;

-- 2、查询不属于第三栏的所有商品

select * from goods

where cat_id <> 3;

-- 3、本店价格高于3000元的商品

select * from goods

where shop_price > 3000;

-- 4、本店价格低于或等于100元的商品

select * from goods

where shop_price <= 100;

-- 5、取出第4栏和者第11栏的商品（不能用or）

select * from goods

where cat_id in (4,11);

-- 6、取出100<=店铺价格<=500的商品(不许用and)

select * from goods

where shop_price between 100 and 500;

-- 7、取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)

select * from goods

where cat_id <> 3 and cat_id <> 11;

select * from goods

where cat_id not in(3,11);

-- 8、取出店铺价格大于100且小于300,或者大于4000且小于5000的商品()

select * from goods

where (shop_price > 100 and shop_price < 300)

or (shop_price > 4000 and shop_price < 5000);

-- 9、取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品

select * from goods

where cat_id = 3 and (shop_price < 1000 or shop_price > 3000) and click_count > 5;

      注意：必须加括号，否则默认or两边各为一个条件。
                  or的优先级最低。

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

-- 11、取出名字以"诺基亚"开头的商品

select * from goods

    where goods_name like '诺基亚%';
      此处使用模糊查询，而不是正则匹配，因为正则匹配效率低。
      %：匹配零到多个字符。
      _：匹配一个字符。

    -- 12、取出名字不以"诺基亚"开头的商品

select * from goods

where goods_name not like '诺基亚%';

-- 13、取出第3个栏目下面价格在1000到3000之间,并且点击量>5 "诺基亚"开头的系列商品

select * from goods

where (cat_id = 3 and shop_price > 1000 and shop_price < 3000 and click_count > 5)and goods_name like '诺基亚%';

面试题

有如下表和数组，把num值处于[20,29]的数改为20，把num值处于[30,39]之间的数改为30。

+------+

| num |

+------+

| 3 |

| 12 |

| 15 |

| 25 |

| 23 |

| 29 |

| 34 |

| 37 |

| 32 |

| 45 |

| 48 |

| 52 |

+------+

update test

set num = 20

where num between 20 and 29;

update test

set num = 30

    where num between 30 and 39;

    或者：

    update test
    set num = floor(num/10)*10
    where num >= 20 and num <= 30;

    where查询模型
      可以把字段看成变量，把where看做Java中if语句中的条件，就是哪条记录能让if为真，就能取出哪条记录。
      非零并且非null 非字符串都为真。
      既然字段是变量，那么变量之间就可以运算。
      例如：
        select market_price - shop_price as discount from goods

                where goods_id = 1;
              注意：不能在where中使用discount，因为where是对原表进行筛选生成一个临时表，discount存在于临时表中，所以where无
                           法使用discount对原表进行筛选。
                       如果想对结果也就是discount进行筛选，只能使用having，因为在where生成结果后，having是对结果再一次进行筛选。

——group by分组查询

      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中可以被执行，但是为了可移植性和规范性，不推荐使用。

-- 1、查询出最贵的商品价格

select max(shop_price) from goods;

-- 2、查询最新的商品编号

select max(goods_id) from goods;

-- 3、查询最便宜的的商品价格

select min(shop_price) from goods;

-- 4、查询最旧的商品编号

select min(goods_id) from goods;

-- 5、查询该店所有商品的库存总量

select sum(goods_id) from goods;

-- 6、查询所有商品的平均价

select avg(shop_price) from goods;

-- 7、查询该店一共有多少种商品

select count(goods_id) from goods;

-- 8、查询每个栏目下面

-- 最贵商品价格

-- 最低商品价格

-- 商品平均价格

-- 库存总量

-- 商品种类

-- （提示：5个聚合函数sum avg max min count 与 group by综合运用）

select cat_id,max(shop_price),min(shop_price),avg(shop_price),sum(goods_number) from goods

group by cat_id;

——having分组筛选

-- 1、查询该店的商品比市场价所节省的价格

select goods_id,market_price - shop_price from goods;

-- 2、查询每个商品所积压的货款（库存*单价）

select shop_price,(goods_number * shop_price) from goods;

-- 3、查询该店积压的总货款

select sum(goods_number * shop_price) from goods;

-- 4、查询该店每个栏目下面积压的货款

select cat_id,goods_number * shop_price from goods

group by cat_id;

-- 5、查询比市场价省钱200元以上的商品及该商品所省的钱。

-- 用where和having分别实现

select goods_id,market_price - shop_price as price

from goods

where market_price - shop_price > 200;

select goods_id,market_price - shop_price as price

from goods

having price > 200;

-- 6、查询积压货款超过2万元的栏目，以及该栏目积压的货款

select cat_id,sum(goods_number * shop_price) as price

from goods

group by cat_id

having price > 20000;

-- 7、where group by having综合练习题

-- 有如下表及数据

-- 查询出2门及2门以上不及格者的平均成绩

-- +------+---------+-------+

-- | name | subject | score |

-- +------+---------+-------+

-- | 张三 | 数学 | 90 |

-- | 张三 | 语文 | 50 |

-- | 张三 | 地理 | 40 |

-- | 李四 | 语文 | 55 |

-- | 李四 | 政治 | 45 |

-- | 王五 | 政治 | 30 |

-- +------+---------+-------+

create table score

(

sname char(4) not null,

ssubject char(5) not null,

sscore float not null

);

insert into score values('张三','数学',90);

insert into score values('张三','语文',50);

insert into score values('张三','地理',40);

insert into score values('李四','语文',55);

insert into score values('李四','政治',45);

insert into score values('王五','政治',30);

insert into score values('赵六','政治',90);

insert into score values('赵六','政治',80);

select sname,count(sscore < 60) as c,avg(sscore) as a

from score

group by sname

having c >= 2 -- 错误！因为无论count()中是否为真，都会将行数全部取出，即使全部及格，会全部取出。

## 一种错误做法

mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;

+------+---+------------+

| name | k | avg(score) |

+------+---+------------+

| 张三 | 3 | 60.0000 |

| 李四 | 2 | 50.0000 |

+------+---+------------+

2 rows in set (0.00 sec)

mysql> select name,count(score<60) as k,avg(score) from stu group by name;

+------+---+------------+

| name | k | avg(score) |

+------+---+------------+

| 张三 | 3 | 60.0000 |

| 李四 | 2 | 50.0000 |

| 王五 | 1 | 30.0000 |

+------+---+------------+

3 rows in set (0.00 sec)

mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;

+------+---+------------+

| name | k | avg(score) |

+------+---+------------+

| 张三 | 3 | 60.0000 |

| 李四 | 2 | 50.0000 |

+------+---+------------+

2 rows in set (0.00 sec)

#加上赵六后错误暴露

mysql> insert into stu

-> values

-> ('赵六','A',100),

-> ('赵六','B',99),

-> ('赵六','C',98);

Query OK, 3 rows affected (0.05 sec)

Records: 3 Duplicates: 0 Warnings: 0

#错误显现

mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;

+------+---+------------+

| name | k | avg(score) |

+------+---+------------+

| 张三 | 3 | 60.0000 |

| 李四 | 2 | 50.0000 |

| 赵六 | 3 | 99.0000 |

+------+---+------------+

3 rows in set (0.00 sec)

#正确思路,先查看每个人的平均成绩

mysql> select name,avg(score) from stu group by name;

+------+------------+

| name | avg(score) |

+------+------------+

| 张三 | 60.0000 |

| 李四 | 50.0000 |

| 王五 | 30.0000 |

| 赵六 | 99.0000 |

+------+------------+

4 rows in set (0.00 sec)

mysql> # 看每个人挂科情况

mysql> select name,score < 60 from stu;

+------+------------+

| name | score < 60 |

+------+------------+

| 张三 | 0 |

| 张三 | 1 |

| 李四 | 1 |

| 王五 | 1 |

| 赵六 | 0 |

+------+------------+

9 rows in set (0.00 sec)

mysql> #计算每个人的挂科科目

mysql> select name,sum(score < 60) from stu group by name;

+------+-----------------+

| name | sum(score < 60) |

+------+-----------------+

| 张三 | 2 |

| 李四 | 2 |

| 王五 | 1 |

| 赵六 | 0 |

+------+-----------------+

4 rows in set (0.00 sec)

#同时计算每人的平均分

mysql> select name,sum(score < 60),avg(score) as pj from stu group by name;

+------+-----------------+---------+

| name | sum(score < 60) | pj |

+------+-----------------+---------+

| 张三 | 2 | 60.0000 |

| 李四 | 2 | 50.0000 |

| 王五 | 1 | 30.0000 |

| 赵六 | 0 | 99.0000 |

+------+-----------------+---------+

4 rows in set (0.00 sec)

#利用having筛选挂科2门以上的.

mysql> select name,sum(score < 60) as gk ,avg(score) as pj from stu group by name having gk >=2;

+------+------+---------+

| name | gk | pj |

+------+------+---------+

| 张三 | 2 | 60.0000 |

| 李四 | 2 | 50.0000 |

+------+------+---------+

2 rows in set (0.00 sec)

正确答案：
select sname,sum(sscore < 60) as s,avg(sscore) from score

group by sname

having s >= 2;

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

当需要进行多次排序时：

order by 列名1 desc/asc,列名2 desc/asc,

当得到最终结果集之后，才可以进行排序，因为在结果集生成过程当中进行排序无意义。

反过来说，排序是针对最终结果，即：order by要放在where/group by having之后，顺序不能颠倒。

知识点：

回收站机制，逻辑删除。

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

offset：偏移量（跳过多少行，也就是从什么位置开始取出记录，不包含offset）

n：取出条目。

offset如果不写，相当于limit 0,n

-- 1、按价格由高到低排序

select * from goods

order by shop_price;

-- 2、按发布时间由早到晚排序

select * from goods

order by add_time

-- 3、按栏目由低到高排序，栏目内部按价格由高到低排序

select * from goods

order by cat_id,shop_price desc;

-- 4、取出价格最高的前三个商品

select * from goods

order by shop_price desc

limit 3;

-- 5、取出点击量前三名到前五名的商品

select * from goods

order by click_count desc

limit 2,3

五个子句的运算顺序

where  group by  having  order by  limit

——where型子查询

把内层查询结果作为外层查询的比较条件

例题：

-- 取出每个栏目下的最新的商品

-- 第一步：先查询每个栏目下最新商品的goods_id

select max(goods_id),cat_id from goods

group by cat_id;

-- 第二步：把goods_id对应的商品信息输出即可

select * from goods

where goods_id in

(

select max(goods_id) from goods

group by cat_id

);

where子查询的SQL语句不能select * from表

因为：

如果where 列名 = (内层SQL) 则内层SQL返回的必须是单列单行值，也就是单个值。

如果where 列名 in (内层SQL) 则内层SQL只能返回单列值，可以多行，也可以单个值。

——from型子查询

将内层查询SQL的查询结果，当成一张临时表，供外层SQL再次进行查询。

注意：

临时表一定要起一个别名。

例题：

-- 取出每个栏目下的最新的商品

select * from

(

select * from goods

order by cat_id,goods_id desc

) as t

group by cat_id

order by goods_id;

——exists型子查询

将外层的查询结果（查询字段）拿到内层查询，判断内层的查询是否成立，如果内层的查询where成立，则该行取出，
并在外行输出显示，如果不成立则不输出。

exists效率高于in，因为exists遇到满足条件就返回，而in则全部比较。

——两表之间的全连接查询

表与集合的关系

一张表就是一个集合。

每一行就是一个元素。

疑问：

集合不能重复，但是有可能有两行数据完全一样，如何处理？

答：

MySQL内部每一行又一个rowid

在数据库中，如何操作表来得到笛卡尔积：

select * from table1,table2;

——左连接

A表与B表通过一个关系来筛选B表的行

语法：

A left join B on 筛选条件

如果条件为真，则B表取出对应的行。

该语句得到的结果也是一个临时表，可以当做一张表，设为C。

既然如此，就可以对C表做查询，所以where group by having order by limit都可以照常使用。

多表连接中的where和on

在多表连接之后，形成的是第三张新表，第三张新表中无任何索引字段，因此子表的索引发挥不了作用。

posted @ 2017-02-07 17:21 WWWYC 阅读(601) 评论(0) 编辑收藏举报

WWWYC

MySQL学习笔记

公告