一、MySQL的认识与使用（初级）

1.1 MySQL的概念和特点

（1）概念

  • DB（datadase）：数据库，存储数据的仓库，它保存了一系列有组织的数据；
  • DBMS（database management system）：数据库管理系统，数据库是通过DBMS来创建和操作的容器；DBMS的分类：
    • 基于共享文件系统的DBMS（Access）；
    • 基于客户机——服务器的DBMS（Mysql、Oracle、SqlServer）
  • SQL（structure query language）：结构化查询语言，专门用来与数据库通信的语言。不是某个特定数据库供应商专有的语言，几乎所有的DBMS都支持SQL。

（2）特点

  • 将数据放到表中（这个表可以理解为文件当中excl表），表再放到数据库中（数据库可以理解为文件当中的文件夹）。
  • 一个数据库中可以有多个表，每个表都有一个名字。用来标识自己，所以表名具有唯一性。
  • 表具有一些特性。这些特性定义了数据在表中该如何存储，类似于Java中的类的 设计。
  • 表由列组成，我们也称之为“字段”，所有表都是由一个或者多个列组成的，每一列类似于Java中的属性（成员变量）。
  • 表中的数据是按行存储的，每一行类似于Java中的“对象”。

1.2 MySQL的安装

（1）MySQL的下载

  • MySQL数据库隶属于MySQLAB公司，总部位于瑞典，后被Oracle收购。
  • 优点：
    • 成本低：开放源代码，一般可以免费使用。
    • 性能高：执行很快。
    • 简单：很容易安装与使用。
  • 官网：windows平台下的下载官网：http://dev.mysql.com/downloads/mysql
  • 安装和配置过程，有必要的话自行百度。

1.3 MySQL的语法和规范

（1）MySQL的语法规范

  • 不区分大小写，但建议关键字大写，表名、列名小写；
  • 每条命令最后以分号结尾；
  • 每条命令根据需求，可以进行缩进和换行；
  • 注释：
    • 单行注释：#注释释文
    • 多行注释：/注释释文/

（2）常用的命令（dos阶段）

  1. #连接（登录）MySQL服务器
  2. mysql -h 主机名（主机的IP地址） -P 端口号 -u 用户名 -p密码
  4. #  查看当前所有的数据库
  5. SHOW  DATABASES;
  7. #  打开指定的数据库
  8. USE 数据库名
  9. USE test;
  11. #  查看当前所在的数据库
  12. SELECT DATABASE();
  14. #  查看当前数据库的所有表
  15. SHOW TABLES;
  17. #  创建数据表
  18. create table 表名 (
  19.         列名1 类型1,
  20.       列名2 类型2......
  21. );
  23. #  查看数据表的结构
  24. desc 表名;
  26. #  查看数据库服务器的版本
  27.     # 方法一：登录到MySQL服务端。
  28. select version();
  29.     # 方法二：没有登录到MySQL服务端；
  30. mysql --version 或者 mysql -V

（3）DQL（data query language 查询语言）

①基础查询语法

  1. #  查询的基础语法 特点：查询列表可以是表中的字段（列表或者是属性）、常量值、表达式、函数
  2. #  查询结果是一张虚拟的表格，就像Java中将数据输出到控制台一样。
  3. select 查询列表 from 表名;
  5. #  查询单个列表：
  6. select 查询列表 from 表名;
  8. #  查询多个列表，方法一：可以双击要查询的列表（格式化小技巧：按F12）
  10. #  查询多个列表，方法二：
  11. select * from 表名;
  13. #  查询常量值
  14. select 常量值;
  17. #  查询表达式
  18. select 表达式;
  20. #  查询函数 mysql里面的函数相当于Java中的带有返回值的方法。
  21. select 函数名;
  23. #  去重
  24. select distinct 列名 from 表名;
  • 起别名：
    • 好处：便于理解；避开了要查询的字段重名的情况，使用别名可以区分开来。

  • 使用格斯：

    • 方式一：使用as

      1. select 查询字段 as 要起的别名 from 表名;

    • 方式二：使用空格

      1. select 查询字段1 要起的别名1, 查询字段2 要起的别名2 from 表名;

  • MySQL中的“+”的使用注意事项

    • 功能：仅仅只有运算符一个功能；
    • 当两个操作数据都是数值型的，则作加法运算；
    • 当其中的一个为字符型的，试图将字符型的转换为数值型的，如果转换成功，则继续作加法运算，如果转换失败，则将字符型的转换成“0”，再作加法运算。
    • 当其中一方为null，则结果肯定为null。

②条件查询语法

  • 条件查询语法规则

    1. select 查询列表 from 表名 where 筛选条件;（筛选条件相当于Java中的if）
    2. #  执行的顺序是，先定位表（from表），再筛选（where 筛选条件），最后定位查询列表（select 查询列表）

  • 条件查询的分类

    • 按条件表达式筛选：>、<、=、!=（另一种写法：<>）、>=、<=
    • 按逻辑表达式筛选：&&（另一种写法：and）、||（另一种写法：or）、!（另一种写法：not）
    • 模糊查询：
      • like函数：like(%a%)：通配符“%”表示任意多个长度的字符，包括0；表示包含字母“a”，的数据。like(a__)：通配符“”“表示任意单个长度的字符，它是限制长度的。注意：当要指定的模糊查询的字符跟通配符一样，就需要进行转义了：通过”_“或者是like’_’escape’‘
      • between关键字：查询某个字段的取值范围：例如：查询员工部门id在100到120之间的员工信息；
        • 方法一：select * from employees where employee_id >=100 and employee_id <=120;
        • 方法二：select * from employees where employee_id between 100 and 120；
      • in函数：查询某个字段可以满足多个值的条件进行查询，或者说判断某字段的值是否属于in列表中的某一项，特点：①使用in提高语句的简洁度；②in列表的值类型必须统一或者兼容，比如”123“可以转化成123；例如 ：查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号：
        • 方法一：select * from employees where job_id = ‘IT_PROG’ or job_id = ‘AD_VP’ or job_id = ‘AD_PRES’;
        • 方法二：select * from employees where job_id in( ‘IT_PROG’ , ‘AD_VP’ , ‘AD_PRES)’;
      • is null 关键字和 is not null关键字：判断某个字段是否为空或者不为空；”=“和”<>“不能用于判断null值；is null或者is not null 可以判断null值

③排序查询语法

  1. select 查询列表 from 表名 [where 查询条件]  order by 列名1 排序方式1,列名2 排序方式2......
  • 排序方式：
    • ASC 表示升序（默认方式）
    • DESC表示降序
  • 排序查询的注意事项：有多个排序条件时，只有当前边的条件值一样时，才会判断第二条件，它可以按字段排序，也可以按表达式进行排序，还可以按函数进行排序。

④分组查询语法

  1. /*
  2.     语法：
  3.               select 分组函数（聚合函数）, 查询列表（要求出现在group by的后面）
  4.         from 表名
  5.         【where 筛选条件】
  6.         group by 分组的列表（字段）、表达式或函数、多个字段分组
  7.         【order by 子句】
  8.   注意：
  9.               查询列表必须特殊，要求是分组函数和group by 后出现的字段
  10. */
  11. # 案例：
  12. # 按多个字段分组 查询每个部门每个工种的员工的平均工资
  13. select avg(salary), department_id , job_id from employees group by department_id , job_id
  15. # 按照品牌分组，获取每组商品的总金额
  16. select 
  17.     brand , sum(price) 
  18. from 
  19.     product 
  20. group by 
  21.     brand;
  23. # 对金额大于40000元的商品按照品牌分组，获取每组商品的总金额；
  24. select 
  25.     brand , sum(price) 
  26. from 
  27.     product 
  28. where 
  29.     price > 40000 
  30. group by 
  31.     brand ;
  33. # 对金额大于4000元的商品按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的
  34. select 
  35.     brand , sum(price) 
  36. as 
  37.     getSum 
  38. from 
  39.     product 
  40. where 
  41.     price > 4000 
  42. group by 
  43.     brand 
  44. having 
  45.     getSum > 7000;

⑤连接查询

  • 笛卡尔乘积现象
    • select * from 表1 , 表2
  • sql92标准版：
    • 等值连接
      • 语法：select 查询列表 from 表名 where 判断有关联的字段（连接条件）
      • 特点：1. 多表等值连接的结果为多表之间的交集部分；2. n张表连接，至少需要n-1个连接条件；3. 多表的顺序没有要求
      • 注意：一般需要为表取别名；可以搭配前面的所有子句使用，比如排序、分组、筛选等。
    • 自连接 ：
      • 就是自己连自己
      • select e.employee_id , e.last_name , m.employee_id, m.last_name from employees e , employees m where e.manager_id = m.employee_id；

  • sql99标准版：支持内外连接，交叉连接

    • 主体语法：select 查询列表 from 表1 别名 【连接类型（）】 join 表2 别名 on 连接条件（连接条件可以是等值的，也可以是区域范围的，比如salary >4000 && salary <7000） 【where 筛选条件】 【group by 分组】 【order by 排序列表】;
    • 内连接主体语法：select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件（连接条件可以是等值的，也可以是区域范围的，比如salary >4000 && salary <7000） 【where 筛选条件】 【group by 分组】 【order by 排序列表】；
    • 外连接主体语法：select 查询列表 from 表1 别名 left 【outer】（左外）（right 【outer】右外）（full 【outer】全外）join 表2 别名 on 连接条件（连接条件可以是等值的，也可以是区域范围的，比如salary >4000 && salary <7000） 【where 筛选条件】 【group by 分组】 【order by 排序列表】；

      ⑥子查询

  • 子查询的概述：出现在其他语句中的select语句，称为子查询或内查询，外部查询的语句，称之为主查询或外查询、父查询；

  • 子查询的分类：

    • 按结果集的行列数不同分为：
      • 标量子查询：结果集只有一行一列
      • 列子查询：结果集只有一列多行；说明：使用该查询需要配合操作符进行操作；操作符：in/not in 表示等于或不等于列表中的任意一个； any/ some 和子查询返回的某一个值进行比较 ； all 和子查询返回的所有值进行比较。
      • 行子查询：结果集有一行多列
      • 表子查询：结果集一般为多行多列。
    • 按子查询出现的位置分为：
      • select后面：仅仅支持标量子查询
      • from后面：支持表子查询；
      • where或者having后面（重点）：支持标量子查询、列子查询 、行子查询
      • exists后面（重点）：支持表子查询

        ⑦分页查询

  • 语法：select 查询列表 from 表名 【where 筛选条件】 【group by 分组】 【having 分组排序后后的筛选条件】【order by 排序】 【limit 参数一（当前页） , 参数二（每页显示的数据条数）】

  • 当前计算公式：当前页 = （当前页码 - 1）* 每页显示的数据条数

⑧联合查询

  • 联合查询的概述：关键字“union” 联合、合并，将多个查询语句的结果合并成一个结果。
  • 联合查询的语法：查询语句1 union 查询语句2 union ……
  • 联合查询的应用场景：要查询的结果来自于多个表，且多个表之间没有直接的连接关系，但查询的信息一致时，使用联合查询。

（4）MySQL中的函数

  • 函数的概述：类似于Java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名；
  • 使用函数的好处：隐藏细节，提高代码的重用性；
  • 函数的调用：select 函数名（实参列表）【from 表名】

  • 函数的分类：

    • 单行函数：

      • 字符函数：

        • 字符拼接函数：concat（实参列表）
        • 返回字符串字节个数的函数：length（实参列表）

        • 字符大小写转换函数：upper是将小写字母转换成大写；lower是将大写转换成小写 ；

          1. #  例如我要查询员工姓名，并且姓大写，名小;
          2. select concat(upper(last_name) , lower(first_name)) as 姓名 from employees;

        • 字符串截取：substr、substring，substr（str , 7）表示截取从指定的索引处后面所有的字符。substr（str , 1 , 3）表示截取从指定的索引处指定字符长度的字符。

        • instr：返回子串在整串中第一次出现的索引，如果找不到，则返回0，例如：select instr（”杨不悔爱上殷六侠“,”殷六侠“），返回结果为7.
        • replace：替换 ：例如 select replace（”张无忌爱上了周紫若“，”周紫若“，”赵敏“）

        • trim： 默认情况下是去除首尾空格，也可以指定要去除的重复内容。

          1. select length(trim('    张翠山    '));  # 返回结果是字符串的字节长度
          2. select trim('a' from 'aaaaa张翠山aaaaaa'); # 结果为张翠山

        • lpad：用指定的字符实现左填充指定的长度

        • rpad：用指定的字符实现右填充指定的长度
          1. select lpad('殷素素', 10 , '*');  # 从左向右填充，如果不够就往左边补。参数一为操作的字符串，参数二为指定的字符串总长度，参数三为指定填充的字符。
          2. select rpad('殷素素 ' , 4, '*')； # 从左向右填充，如果不够就往右边补。

      • 数学函数：

        • 四舍五入：round（）
        • 向上取整：ceil（） 返回>=该参数的最小值
        • 向下取整：floor（） 返回<=该参数的最大值
        • 截断：truncate（） 保留指定参数为小数部位
        • 取余：mod（） 参数一为被除数，参数二为除数，返回值的符号取决于被除数；
      • 日期函数：
        • now（）：返回当前的系统日期+时间
        • curdate（）：返回当前系统日期，不包括时间
        • curtime（）：返回当前系统时间，不包括日期
        • year（）、month（）：参数可以指定一个如期，也可以传入now（）函数；
        • str_to_date（）：将日期格式的字符串转换成指定格式的日期
        • date_format（）：将日期转换成字符
        • datediff（）：计算日期的差值，返回的是天数 | 序号 | 格式符 | 功能 | | —- | —- | —- | | 1 | %Y | 四位的年分 | | 2 | %y | 两位的年份 | | 3 | %m | 月份（01、02、……、11、12） | | 4 | %c | 月份（1、2、……、11、12） | | 5 | %d | 日（01、02、……、30） | | 6 | %H | 小时（24小时制） | | 7 | %h | 小时（12小时制） | | 8 | %i | 分钟 | | 9 | %s | 秒 |

    • 流程控制函数

      • if（参数一，参数二，参数三）：参数一是一个条件表达式，参数二是一个值，参数三也是一个值；（说白了就是一个Java中的三元运算符）

      • case：

        • 用法一（语句语法）：case 运算表达式 when 常量值 then 显示的值或语句…… else 显示的值或语句 end （相当于Java 中的switch case）案例代码演示

          1. select salary 原工资 , department_id,
          2. case department_id 
          3. when 30 then salary*1.1
          4. when 40 then salary*1.2
          5. when 50 then salary*1.3
          6. else salary end 新工资
          7. from employees；

        • 用法二（语句语法）：case when 区间表达式 then 显示的值或语句……else 显示的值或语句 end （相当于Java中的if……else if） 案例代码

          1. select salary,
          2. case 
          3. when salary>20000 then 'A'
          4. when salary>15000 then 'B'
          5. when salary>10000 then 'C'
          6. else 'D' end 工资级别
          7. from employees
          8. order by salary；

    • 分组函数（聚合函数）：

      • 分组函数（聚合函数）的概述：将一列数据作为一个整体，进行纵向的计算，一般用来做统计用的。
      • 聚合函数的分类：
        • 统计表行数量（不包括 为null的行）
          • count（列名）、count（*）、count（1）
          • count（）和count（1）的效率比count（列名）要高些，所以一般用count（）作为查询表的行数。
        • 最大值：max（列名）
        • 最小值：min（列名）
        • 求和：sum（列名）
        • 平均值：avg（列名）
      • 聚合函数的特点：
        • sum、avg一般用于处理数值型
        • max、min、count可以处理任何类型的数据
        • 以上的聚合函数都忽略null值的
        • 可以和distinct（去重）配合使用。
      • 聚合函数的注意点：和聚合函数一同查询的字段要求是group by后的字段。

（5）DML数据操作语句

①数据插入

  • 语法：
    • 方式一：insert into 表名 （列名2 , 列名2……）values （对应值1 , 对应值2……）； 该方式常用，因为支持子查询和插入多行； insert into 表名 values （值1 ，值2，值3……），（值1 ，值2，值3……）
    • 方式二：insert into 表名 set 列名1 = 值1 , 列名2 = 值2…… ；

②数据修改

  • 语法：
    • 修改单表： update 表名 set 列名1 = 值1 ，列名2 = 值2，…… 【where 筛选条件】
    • 修改多表关联：update 表1 别名 inner/left/right join 表2 别名 on连接条件 set 列名1 = 值1 ，…… 【where 筛选条件】
  • 注意：修改语句中必须加筛选条件，如果不加，则将会修改所有的数据。

③数据删除

  • 语法： delete from 表名 【where 筛选条件】
  • 注意：删除语句中必须加条件，如果不加 ，则将会删除表中的所有的数据。

（6）DDL数据定义语言

①数据库的创建、修改和删除

  • 创建数据库
    • 语法：
      • 方式一（直接创建）create database 指定数据库名称；
      • 方式二（条件判断，如果不存在就创建）：create database if not exists 指定数据库名称；
      • 方式三（指定字符集）： create database 指定数据库名称 character set 字符集名称；
  • 修改数据库
    • 修改数据库的字符集：alter database 要修改的数据库名称 character set 修改指定的字符集名称；
  • 删除数据库
    • 语法：
      • 方式一（直接删除）：drop database 要删除的数据库名称；
      • 方式二（条件判断，如果存在就删除）：drop database if exists 要删除的数据库名称；
  • 使用数据库
    • use 数据库名称；
  • 查看当前使用的数据库
    • select datase（）；
  • 查看当前所有的数据库：show databases；

②数据表的创建、修改和删除

  • 创建数据表
    • 语法：
      • create table 表名 （列名 列的数据类型 【（长度） 约束】，列名 列的数据类型 【（长度） 约束】，……列名 列的数据类型 【（长度） 约束】）
  • 修改数据表
    • 修改列名和数据类型
      • alter table 表名 change column 旧列名 新列名 数据类型；
    • 修改列的数据类型或约束
      • 类型：alter table 表名 modify column 指定的列名 修改的类型；
    • 添加新列
      • 单独添加一列：alter table 表名 add 列名 数据类型
    • 删除列
      • alter table 表名 drop 列名；
    • 修改表名
      • alter table 表名 rename to 新表名；
    • 修改表的字符集
      • alter table 表名 character set 修改的字符集名称；
  • 删除数据表
    • 方式一（直接删除）：drop table 表名；
    • 方式二（判断，如果存在就删除）：drop table if exists 表名；
  • 复制数据表
    • 方式一，仅仅复制表的结构：create table 要复制的表名 like 被复制的表名；
    • 方式二：复制表的结构+数据：create table 要复制的表名 select * from 被复制的表名； 扩展，只复制部分数据和只复制部分表的结构，直接加where 筛选条件就行，

1.4 MySQL中的数据类型和表的约束

（1）常见的数据类型

  • 整型 | 类型 | 字节 | 取值范围 | | —- | —- | —- | | Tinyint | 1 | 有符号：-128到127
    无符号：0到255 | | smallint | 2 | 有符号：-32768到32767
    无符号：0到65535 | | mediumint | 3 | 有符号：-8388608到8388607
    无符号：0到1677215 | | int、integer | 4 | 不用记 | | bigint | 8 | 不用记 |

  • 小数

    • 定点数
      • dec（M,D）
      • decimal（M,D）
    • 浮点数
      • float（M,D）
      • double（M,D）
    • 特点：
      • M：整数部位+小数部位的长度
      • D：小数部位的长度
      • M和D都可以省略，如果是decimal，则M默认是10，D默认是0；如果是float和double，则会根据插入的数值的精度来决定精度
      • 定点型的精确度较高，如果要求插入的数据的精度较高如货币运算等则考虑使用
  • 字符型
    • 较弱的文本：char 、 varchar
    • 较长的文本：text 、 blob （较长的二进制数据）
  • 日期型：
    • 日期类型的，在sql语句中必须用引号包裹，
    • 常用的日期类型有：date 、datetime 、timestmep

（2）表的约束

①主键约束

  • 主键约束的特点
    • 主键约束默认包含非空和唯一两个功能
    • 一张表只能有一个主键
    • 主键一般用于表中数据的唯一标识
  • 创建主键约束：
    • 创建表时添加主键约束：create table 表名（列名 数据类型 primary key , …… , 列名 数据类型 约束）；
    • 建表后单独添加主键约束：alter table 表名 modify 列名 数据类型 primary key；
  • 删除主键约束：alter table 表名 drop primary key；

②唯一约束

  • 创建唯一约束
    • 建表时添加唯一约束：create table 表名（列名 数据类型 primary key , …… , 列名 数据类型 unique）；
    • 建表后单独添加唯一约束：alter table 表名 modify 列名 数据类型 unique；
  • 删除唯一约束：alter table 表名 drop index 列名；

③主键自增约束

  • 创建主键自增约束
    • 建表时添加主键自增约束：create table 表名 （列名 数据类型 primary key auto_increment，……）;
    • 建表后单独添加主键自增约束：alter talbe 表名 modify 列名 数据类型 auto_increment
  • 删除主键自增约束：alter table 表名 modify 列名 数据类型

④外键约束

  • 特点
    • 要求在从表设置外键关系
    • 从表的外键列的类型和主表的关联列的类型要求一致或兼容
    • 主表的关联列必须是一个key（一般是主键或者是唯一）
    • 外键约束的关联表，先删除有外键的从表再删除主表
    • 插入数据时，先插入主表，再插入从表
    • 修改数据时，
  • 语法
    • 创建从表时添加外键约束：create table 表名 （列名 数据类型 ，…… ，列名 数据类型 ，primary key（要添加主键的字段1，要添加主键的字段2） ，unique（要添加唯一约束的字段1，要添加唯一约束的字段2），check （要检查字段名1 = 值1 ，要检查字段名2 = 值2），foreign key （要加外键的字段） references（外键关联的主表的字段） ）

1.5 TCL事务控制语言

（1）事务的概念和属性特点

  • 概念：一个或一组sql语句组成一个执行单元，在这个单元中，每个sql语句是相互依赖的；而整个单独的单元作为一个不可分割的整体，如果单元中的某条sql语句出现了错误，整个单元将会回滚。所有受到影响的数据将返回到事务开始以前的状态；如果单元中的所有sql语句都执行成功，则事务被顺利执行。
  • 属性特点：
    • 原子性：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都成功发生，要么都执行失败；
    • 一致性：事务必须使数据库从一个一致性状态转换成另外一个一致性的状态（相当于sql执行前的数据总量和sql执行后的数据总量是一致的，比如说：张三转账给李四，前后的总金额是一致的）
    • 隔离性：事务的隔离性是指一个事务的执行不能被其他的事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的 ，并发执行的各个事务之间不能互相干扰。
    • 持久性：持久性是指一个事务一旦被提交，它对数据库中的数据的改变是永久性的，接下来的其他的操作和数据库故障不应该对其数据有任何影响；
  • 常见的并发问题：
    • 对于同时运行的多个事务，当这些事务访问数据库中的相同数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：
    • 脏读：对于两个事务T1，T2，T1读取了已经被T2更新但没有提交的字段，之后，若T2回滚，T1读取的内容就是临时的且无效的
    • 不可重复读：对于两个事务T1，T2，T1读取了一个字段，然后T2更新了该字段，之后，T1再次读取同一个字段，值就不同；
    • 幻读：对于两个事务T1，T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行之后，如果T1再次读取同一个表，就会多出几行

（2）事务的创建

  • 事务的分类
    • 隐式事务：事务没有明显的开启和结束的标记；比如说，insert 、update 、delete语句，像这些语句是自动开启和提交事务的，每一条这样的语句就是一个事务，如果想要将两条这样的语句或事务合并成一个事务，那么就要用到显示事务了。
    • 显示事务：事务具有明显的开启和结束标记；使用前提必须设置自动提交功能为禁用
  • 事务的使用步骤：
    • set autocommit=0；或者start transaction ；关闭自动提交事务
    • 语句1
    • 语句2
    • ……
    • commit；或者rollback；提交或回滚事务