MySQL 基础 笔记

本文基于尚硅谷MySQL基础视频而来

1.数据库基本知识

1.数据库相关概念

1. DB：数据库，保存一组有组织的数据的容器
2. DBMS：数据库管理系统，又称为数据库软件(产品)，用于管理DB中的数据
3. SQL:结构化查询语言，用于和DBMS通信的语言

2.数据库存储数据的特点

1. 将数据放到表中，表再放到库中
2. 一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
3. 表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
4. 表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
5. 表中的数据是按行存储的，每一行类似于java中的“对象”。

3.MySQL的常见命令

1. 查看当前所有的数据库
   show databases;

2. 打开指定的库
   use 库名

3. 查看当前库的所有表
   show tables;

4. 查看其它库的所有表
   show tables from 库名;

5. 创建表

   create table 表名(
       列名 列类型,
       列名 列类型，
       。。。
   );
   

6. 查看表结构
   desc 表名;

7. 查看服务器的版本

   • 方式一：登录到mysql服务端
     • select version(); // 函数
   • 方式二：没有登录到mysql服务端
     • mysql --version
     • mysql --V

4.MySQL的语法规范

1. 不区分大小写,但建议关键字大写，表名. 列名小写
2. 每条命令最好用分号结尾
3. 每条命令根据需要，可以进行缩进 或换行
4. 注释
   单行注释：# 注释文字
   单行注释：-- 注释文字
   多行注释：/* 注释文字 */

5.SQL的语言分类

• DQL(Data Query Language)：数据查询语言
  • select
• DML(Data Manipulate Language):数据操作语言
  • insert . update. delete
• DDL(Data Define Languge)：数据定义语言
  • create. drop. alter
• TCL(Transaction Control Language)：事务控制语言
  • commit. rollback
    ​

6.SQL的常见命令

show databases； # 查看所有的数据库
use 库名；# 打开指定 的库
show tables ; # 显示库中的所有表
show tables from 库名; # 显示指定库中的所有表
create table 表名(
    字段名 字段类型,
    字段名 字段类型
);  # 创建表

desc 表名; 查看指定表的结构
select * from 表名;显示表中的所有数据

2.DQL语言

• 数据查询语言

1. 基础查询

• 语法：

  • SELECT 要查询的东西 FROM [表名];

  • 类似于Java中 :System.out.println(要打印的东西);

• 应用：

  1. 通过select查询完的结果 ，是一个虚拟的表格，不是真实存在

  2. 要查询的东西 可以是常量值. 可以是表达式. 可以是字段. 可以是函数

  3. distinct 去重

  4. + 号, 只有一个功能, 就是运算符

     select 12+3;    # 都为数值型, 直接相加
     select '123'+3; # 其一为字符型, 则试图将字符型转换为数值型, 转换成功继续相加, 转换失败则将字符型数值转换为0
     select null+3;  # 其中一方为null, 则结果为null
     

  5. concat 函数 字符串拼接
     select concat(lastName, firstName) as 姓名 from users;

  6. ifnull 函数, ifnull(ex1, ex2);

     • ex1: 判断是否为null 的参数
     • ex2: 如果ex1为null的替代值

  7. 字符型和日期型的常量必须用单引号括起来

2. 条件查询

• 条件查询：根据条件过滤原始表的数据，查询到想要的数据

• 语法：

  select  
      要查询的字段|表达式|常量值|函数
  from  
      表
  where  
      条件 ;
  

• 条件的分类：

  1. 条件表达式
     示例：salary > 10000
     条件运算符：> < >= <= = != / <>
  2. 逻辑表达式
     示例：salary>10000 && salary<20000
     • 逻辑运算符：
       • and(&&):两个条件如果同时成立，结果为true，否则为false
       • or(||)：两个条件只要有一个成立，结果为true，否则为false
       • not(!)：如果条件成立，则not后为false，否则为true
  3. 模糊查询

     • 示例：last_name like 'a%'

     • 通配符:

       • %: 任意多个字符, 包含0个
       • _: 任意单个字符
       • escape ‘$’,表示 $ 为转义字符’’

     • between and
       包含临界值, 即闭区间

       select  
           *  
       from
           employee  
       where  
           employeeId between 100 and employeeId<=120;
       

     • in

       select  
           `name`, jobId
       from
           employees
       where
           jobId in ('IT_PRO','AD_VP','AD_PRES'); # in 里面不支持通配符
       

     • is null / is not null
       只能判断null值

       select
           `name`,
           `commission_pct`
       from
           users
       where
           commission_pct is not null;
       

     • 安全等于 <=>
       可以判断任何类型的值, 可读性较差

       select
           `name`,
           `commission_pct`
       from
           users
       where
           commission_pct <=>null;
       

3. 排序查询

• 语法：

  select
      要查询的东西
  from
      表
  [where 条件]
  order by 排序的字段|表达式|函数|别名 [asc|desc]
  

• 应用:

  • 默认是升序排列, asc
  • select * from users order by salary;
  • select * from users where id > 90 order by hiredate desc;
  • order by 子句支持 单个字段, 别名, 表达式, 函数, 多个字段
  • order by 子句 在查询语句的最后, 除了limit子句

4. 常见函数

1. 单行函数

   1. 字符函数

      • concat拼接
      • substr截取子串
      • upper转换成大写
      • lower转换成小写
      • trim去前后指定的空格和字符
      • ltrim去左边空格
      • rtrim去右边空格
      • replace替换
      • lpad左填充
      • rpad右填充
      • instr返回子串第一次出现的索引
      • length 获取字节个数

   2. 数学函数

      • round 四舍五入
      • rand 随机数
      • ceil 向上取整, 返回>=该参数的最小整数
      • floor 向下取整
      • truncate 截断
        truncate(1.234,1); ==> 1.2
      • mod 取余 mod(a,b) = a-a/b*b
        mod(10,-3); -1

   3. 日期函数

      • now 当前系统日期+时间
      • curdate 当前系统日期
      • curtime 当前系统时间
      • str_to_date 将字符转换成日期
      • date_format 将日期转换成字符

   4. 其他函数

      • version 版本
      • database 当前库
      • user 当前连接用户

   5. 流程控制函数

      • if 处理双分支
      • case语句 处理多分支
        • 情况1：处理等值判断
        • 情况2：处理条件判断

   6. 分组函数

      • 用作统计使用, 又称为聚合函数或统计函数
      • sum 求和
      • max 最大值
      • min 最小值
      • avg 平均值
      • count 计数
      • 特点：
        1. 以上五个分组函数都忽略null值，除了count(*)
        2. sum和avg一般用于处理数值型
           max. min. count可以处理任何数据类型
        3. 都可以搭配distinct使用，用于统计去重后的结果
        4. count的参数可以支持：
           • 字段. *. 常量值，一般放1
           • 建议使用 count(*)
             • MYISAM 引擎下, count(*)效率最高
             • INNODB 引擎下, count(1)和count(*)差不多
        5. 和分组函数一同查询的字段要求是 group by 后的字段, 否则没有意义

   select length('join'); # get the length of the string
   select concat(lastName,'_',firstName) from users; # concat the string  
   select upper('join'); # `JOIN`
   select substr('htasdfja', 6); # sql中的索引 从 1 开始;截取从 6 开始到末尾 fja
   select substr('htasdfja', 1, 3); # 从 1 开始, 截取长度为3
   select instr('数据结构与算法', '算法'); # 返回算法的索引
   select trim('a' from 'aaaaaa张aaaaa阿斯aaaaaa') as out; # 返回张aaaaa阿斯
   select lpad('杨不悔',4,'*'); # 长度为4, 不够向左边填充*, 若长度不够, 比如 2, 则从左往右截断, 杨不
   select rpad('asdf',12,'+'); # 右填充指定长度
   
   select replace('aslkdjfalksjdfa;shdfba','a','A'); # 将a替换为A
   
   select if(10>5, '大', '小');  
   select `name`, commission_pct, if(commission_pct is null, '没奖金,惨', '有奖金,棒') 备注 from users;
   
   # 方式一 类似 java switch
   case `要判断的值或表达式`
   when `常量1` then `要表示的值1或表达式1; `
   when `常量2` then `要表示的值2或表达式2;`
   `...`
   else `默认显示的值或语句`
   end
   # 方式二 类似java 多重if else
   case
   when `条件1` then `显示的值或 语句;`
   when `条件2` then `显示的值或 语句;`
   else `显示的值或 语句;`
   end
   
   
   select salary 原始工资, dept_id,
   case dept_id
   when 30 then salary*1.1
   when 40 then salary*1.2
   when 50 then salary*1.4
   else salary
   end as 新工资
   from employees;
   
   

5. 分组查询

• 语法：

  select 查询列表: 分组函数, 列(要求在 group by 的后面)
  from 表
  [where 筛选条件]
  group by 分组的列表
  [order by 子句]
  

  • where 一定在from后面
  • 查询列表必须特殊, 必须是分组函数或是group by后出现的字段

• 特点：

  1. 可以按单个字段分组
  2. 和分组函数一同查询的字段最好是分组后的字段
  3. 分组筛选
     分组前筛选：原始表; group by的前面 where
     分组后筛选：分组后的结果集; group by的后面having
     • 分组函数做条件, 一定在having中
     • 能用分组前筛选的就用分组前筛选
  4. 可以按多个字段分组，字段之间用逗号隔开
  5. 可以支持排序
  6. having后可以支持别名

  select MAX(salary), job_id
  from emps
  group by job_id;
  
  select COUNT(*), location_id
  from depts
  group by location_id;
  
  select AVG(salary),dept_id
  from emps
  where email like '%a%'
  group by dept_id;
  
  select MAX(salary),manager_id
  from emps
  where commission_por is not null
  group by manager_id;
  
  select COUNT(*), dept_id
  from emps
  group by dept_id
  having count(*)>2;
  
  select MAX(salary) ma, job_id
  from emps
  where commission_por is not null
  group by job_id
  having ma>12000;
  
  select COUNT(*) c,LENGTH(name) len_name
  from emps
  group by len_name
  having c > 5;
  
  select AVG(salary), dept_id, job_id
  from emps
  group by dept_id, job_id; # dept_id == job_id 的分为一组
  
  select AVG(salary) a, dept_id, job_id
  from emps
  where dept_id is not null
  group by dept_id, job_id
  having a>10000
  order by a DESC;
  

6. 多表连接查询

• 当查询的字段来自于多个表时, 就会用到连接查询

• 笛卡尔乘积：

  • 如果连接条件省略或无效则会出现
  • 表1中有m行, 表2中有n行, 结果有m*n行
  • 解决办法：添加上连接条件

• 分类:

  • 按年代分:

    • sql92: 仅支持内连接
    • sql99(推荐) 支持内连接+外连接(左外,右外)+交叉连接

  • 按功能分:

    • 内连接 : 查询两个表交集的部分
      1. 等值连接
         • 用表1的每一行匹配表2的每一行, 但是最后根据连接条件进行筛选
         • n表连接, 至少需要 n-1 个连接条件
         • 多表顺序没有要求
         • 可以加任何之前讲过的分组, 筛选等
      2. 非等值连接
         • 连接条件不是等号, 其他同等值连接
      3. 自然连接
         • 把原始的表当作2张表, 甚至更多表
         • 相当于等值连接, 但是另一张表就是自己, 必须有特殊的字段才可以
           • 比如: emps员工表, 有员工id, 某员工的领导存的是领导的员工id, 当查询员工和员工的领导时, 就是先查询该员工领导的员工id, 再根据id查询员工姓名, 得到领导的姓名, 相当于emps表查了2遍, 即自连接
    • 外连接 : 查询一个表中有, 一个表中没有的数据
      • 分主表和从表, 查询的结果为主表中的所有数据, 如果从表中有满足条件的,则显示匹配的值,相当于内连接的部分;如果没有匹配的值, 显示null
      • 外连接查询的结果 = 内连接结果 + 主表中有而而从表没有记录
      • 分类:
        • 左外连接 : 左边是主表
        • 右外连接 : 右边是主表
        • 全外连接 : 不分主从表, 结果相当于 内连接结果+左外连接结果+右外连接结果 , MySQL不支持
    • 交叉连接
      • 笛卡尔乘积的结果
      • 表之间没有顺序关系

    # sql92 标准 案例
    #等值连接
    select `name`, deptId
    from emps,depts
    where emps.deptId=depts.deptId;
    
    # 如果为表起了别名, 则查询的字段就不能用原来的表名去限定
    select name,e.jobId,jobTiele
    from emps as e,jobs as j
    where e.jobId=j.jobId;
    
    # 加筛选
    select name,deptName,commission_pct
    from emps e, dept d
    where e.deptId=d.deptId
    and e.commission_pct is not null;
    
    # 加分组
    select count(*) 个数,city
    from dept d, location l
    where d.locationId=l.locationId
    group by city
    
    # 非等值连接
    select salary, gradeLevel
    from employee e, jobGrade g
    where salary between g.lowerSal and g.higerSal;
    
    # 自然连接
    select e.empId, e.name, m.empId, m.name
    from emps e, emps m
    where e.managerId=m.empId;
    
    

    • sql99语法：

      select 字段，...
      from 表1 别名
      [连接类型] join 表2 别名 on  连接条件
      [where 筛选条件]
      [group by 分组字段]
      [having 分组后的筛选条件]
      [order by 排序的字段或表达式]
      

      • 连接类型:
        • 内连接 [inner], inner 可以直接省略
        • 外连接
          • 左外 left [outer]
          • 右外 right [outer]
        • 交叉连接 cross
      • 好处：语句上，连接条件和筛选条件实现了分离，简洁明了！

    # sql99
    SELECT e.last_name,m.last_name
    FROM employees e
    JOIN employees m ON e.`manager_id`=m.`employee_id`
    where e.last_name like '_k%';
    
    # sql92
    SELECT e.last_name,m.last_name
    FROM employees e,employees m  
    WHERE e.`manager_id`=m.`employee_id`;
    
    # 左外连接
    select b.name
    from beauty b               # 主表
    left join boys bo
    on b.boyfriendId=bo.id
    where bo.id is null;
    # 右外连接 等价于上面的左外连接
    select b.name
    from boys bo               # 主表
    right join beauty b
    on b.boyfriendId=bo.id
    where bo.id is null;
    
    # 全外   mysql 不支持, 但是语法是这么写
    select b.*, bo.*
    from beauty b
    FULL outer join boys bo
    on b.boyfriendId=bo.id;
    
    # 交叉连接
    select b.*, bo.*
    from beauty b
    cross join boys bo;
    
    

7. 子查询

• 含义：

  • 出现在其他语句中的select语句，称为子查询或内查询

  • 出现在外面的查询语句，称为主查询或外查询

    select                         # 主查询
        name  
    from emps  
    where dept_id in (
        select dept_id from depts  # 子查询
        where location_id=1700
    );
    

• 分类：

  1. 子查询按照出现位置的分类:
     1. select后面 :
        仅支持标量子查询, 即结果只能是一行一列
     2. from后面 :
        支持表子查询, 将子查询的结果充当一张表, 必须起别名
     3. where 或 having后面 :
        标量子查询, 列子查询, 行子查询
     4. exists后面(相关子查询) :
        表子查询
        语法: exists(完整的查询语句)
        结果: 1, 0
  2. 子查询根据查询结果的行列数不同分为:
     1. 标量子查询(单行子查询)
        • 结果集只有一行一列
        • 一般搭配单行操作符使用：> < = <> >= <=
        • 非法使用子查询的情况：
          • 子查询的结果为一组值
          • 子查询的结果为空
     2. 列子查询(多行子查询)
        • 结果集有一列多行
        • 一般搭配多行操作符使用：any. all. in. not in
          • in： 属于子查询结果中的任意一个就行
          • any|some: 和子查询中的某一个值比较
          • all: 和子查询中所有的值比较
          • any和all往往可以用其他查询代替, MIN MAX
     3. 行子查询
        • 结果集一行多列
     4. 表子查询
        • 结果集一般为多行多列

• 特点:

  1. 子查询都放在小括号内
  2. 子查询一般放在条件的右侧
  3. 子查询优先于主查询执行, 主查询使用了子查询的执行结果

  # 1. where 或 having后面
      # 标量子查询(单行子查询) 谁的工资比 able 高
      select *
      from emps
      where salary>(
          select salary
          from emps
          where name='able'
      );
  
      SELECT `name`, jobId, salary
      FROM  emps
      WHERE jobId = (
          SELECT jobId
          FROM emps
          WHERE empId = 141
      ) AND salary > (
          SELECT salary
          FROM emps
          WHERE empId = 143
      );
  
      select `name`, jobId, salary
      from emps
      where salary=(
          select MIN(salary)
          from emps
      );
      # 列子查询(多行子查询)
      select `name`
      from emps
      where deptId in(
          select distinct deptId
          from depts
          where locationId in (1400,1700)
      );
  
      select `name`, empId, jobId, salary
      from emps
      where salary < ANY(
          select distinct salary
          from emps
          where jobId ='IT'
      ) and jobId <> 'IT';
      # 上述可以简化为
      select `name`, empId, jobId, salary
      from emps
      where salary < (
          select MAX(salary)
          from emps
          where jobId ='IT'
      )
      and jobId <> 'IT;'
  
      # 员工编号最小, 且工资最高
      select *
      from emps
      where empId=(
          select MIN(empId)
          from emps
      ) and salary=(
          select MAX(salary)
          from emps
      );
      # 行子查询
      select *
      from emps
      where (empId,salary)=(
          select MIN(empId), MAX(salary)
          from emps
      );
  
  # 2. select 后面
      # 查询每个部门的员工个数
      select d.*,  (
          select count(*)
          from emps e
          where e.deptId=d.deptId
      ) 个数
      from dept d;
  
      # 查询员工号为102的部门名
      select (
          select deptName
          from dept d
          inner join emp e
          on d.deptId=e.deptId
          where e.empId=102
      ) 部门名;
  
  # 3. from 后面
      # 查询每个部门的平均工资的工资等级
  
      select ag_dep.*, gradeLevel
      from (
          select AVG(salary) ag, deptId
          from emps
          group by deptId
      ) ag_dep
      inner join jobGrade g
      on ag_dep.ag between lowerSa and higherSa
  
  

8. 分页查询

• 应用场景：

  • 实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句

• 语法：

  select 字段|表达式,...
  from 表
  [where 条件]
  [group by 分组字段]
  [having 条件]
  [order by 排序的字段]
  limit [起始的条目索引，]条目数;  # 该索引从 0 开始
  

• 特点：

  1. 起始条目索引从0开始
  2. limit子句放在查询语句的最后
  3. 公式：select * from 表 limit (page-1)*sizePerPage,sizePerPage
     • 每页显示条目数sizePerPage; 要显示的页数 page

• 例子

  select * from emps limit 5;
  
  select *
  from emps
  where commission_pct is not null
  order by desc
  limit 10;
  
  # 查询平均工资最低的部门信息
  SELECT *
  FROM departments
  WHERE department_id=(
      SELECT department_id
      FROM employees
      GROUP BY department_id
      ORDER BY AVG(salary)
      LIMIT 1
  );
  

9. 联合查询

• 概念：union 联合. 合并, 将多条查询语句的结果合成成一个结果

• 应用场景: 查询的结果来自多个表, 但多个表之间没有连接关系, 且查询的信息一致

• 语法：

  select 字段|常量|表达式|函数 [from 表] [where 条件] union [all]
  select 字段|常量|表达式|函数 [from 表] [where 条件] union [all]
  select 字段|常量|表达式|函数 [from 表] [where 条件] union  [all]
  .....
  select 字段|常量|表达式|函数 [from 表] [where 条件]
  

• 特点：

  1. 多条查询语句查询的列数必须是一致的
  2. 多条查询语句查询的列的类型和顺序最好一致
  3. union代表去重，union all代表不去重

  # 查询部门编号大于90或邮箱包含a 的员工信息
  select * from emps where email like '%a%' or deptId>90;
  # 等价于
  select * from emps where email like '%a%'
  union
  select * from emps where deptId>90;
  

3.DML语言

• 数据管理语言

1.插入 insert

• 语法：
  1. insert into 表名(字段名，...) values(值1，...);
  2. insert into 表名 set 列名1=值1, 列名2=值2, ...
• 特点：
  1. 字段类型和值类型一致或兼容，而且一一对应
  2. 可以为空的字段，可以不用插入值，或用null填充
  3. 不可以为空的字段，必须插入值
  4. 字段个数和值的个数必须一致
  5. 字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致
  6. 法1支持插入多行, 法2不支持; 法1支持子查询, 法2不支持

2.修改 update

• 修改单表语法：update 表名 set 字段1=新值1,字段2=新值2 [where 条件]

• 修改多表语法：

  # 92语法
  update 表1 别名1,表2 别名2
  set 字段=新值，字段=新值
  where 连接条件
  and 筛选条件
  # 99语法
  update 表1 别名
  inner|left|right join 表2 别名
  on 连接条件
  where 筛选条件
  

3.删除 delete

• 方式1：delete语句

  • 单表的删除： ★
    delete from 表名 [where 筛选条件]

  • 多表的删除：(利用连接删除)

    # 92
    delete 别名1，别名2  # 删谁写谁
    from 表1 别名1，表2 别名2
    where 连接条件
    and 筛选条件;
    
    # 99
    delete 别名1，别名2  # 删谁写谁
    from 表1 别名1
    [连接方式] join 表2 别名2
    on 连接条件
    where 筛选条件
    
    # eg 删除Java的朋友的信息
    delete f
    from friend f
    inner join boy b
    on f.friendId=b.id
    where b.name='java'
    

• 方式2：truncate语句

  • truncate table 表名; 删除整个表

• 两种方式的区别:

  1. truncate 不能加 where 条件，而 delete 可以加 where 条件
  2. truncate 的效率高一丢丢, 因为直接全部删掉了
  3. truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始
     delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始
  4. truncate 删除没有返回值，delete 删除有返回值
  5. truncate 删除不能回滚，delete 删除可以回滚

4.DDL语言

• 数据定义语言

4.库和表的管理

• 库的管理：
  1. 创建库
     create database [if not exists] 库名
  2. 修改库的字符集
     alter database books character set utf-8
  3. 删除库
     drop database [if exists] 库名
• 表的管理：

  1. 创建表

     create table [if not exists] 表名(
        列名 列的类型 [长度 约束],
        列名 列的类型 [长度 约束],
        ...
        列名 列的类型 [长度 约束]
     );
     
     # eg
     CREATE TABLE IF NOT EXISTS stuinfo(
         stuId INT,
         stuName VARCHAR(20),
         gender CHAR,
         bornDate DATETIME
     );
     DESC studentinfo;
     

  2. 修改表 alter : 是修改表的结构, 不是修改数据

     • 语法：ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 字段名 [字段类型];

     # 1. 修改字段名
     ALTER TABLE studentinfo CHANGE COLUMN sex gender CHAR;
     # 2. 修改表名
     ALTER TABLE stuinfo RENAME [TO]  studentinfo;
     # 3. 修改字段类型和列级约束
     ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;
     # 4. 添加字段
     ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first;
     # 5. 删除字段
     ALTER TABLE studentinfo DROP COLUMN email;
     

  3. 删除表

     • DROP TABLE [IF EXISTS] studentinfo;

  4. 表的复制

     • create table 新表 like 要复制的表; 只复制表的结构
     • create table 新表 select * from 要复制的表; 复制表的结构和数据

5.常见类型

1. 数值型

   • 整型：

     整数类型	字节数	范围
     Tinyint	1	有符号 -128~127; 无符号0~255
     Smallint	2	有符号 -32768~32767; 无符号0~65535
     Mediumint	3	很大 2^24
     Int/Integer	4	很大 2^32
     Bigint	8	很大 2^64

     create table tabInt(
         t1 int,         # 默认有符号
         t2 int unsigned,
         t3 int(7) zerofill
     );
     

     • 特点:
       • 默认是有符号的
       • 插入数值超出范围会报异常(out of range), 插入临界值
       • int(长度), 不设置长度, 有默认长度, 长度的含义是显示的最大宽度, 不是范围, 可用zerofill 表示0填充, 此时为无符号

   • 小数：

     浮点数类型	字节数	范围
     float	4	很大
     double	8	很大

     定点型类型	字节数	范围
     dec(M,D) | decimal(M,D)	M+2	最大取值范围和double相同, 给定decimal的有效取值范围由M和D决定

     • 定点型的小数精度更高

     • M, D的含义

       create table tabFloat(
           f1 float(5,2),
           f2 double(5,2),
           f3 decimal(5,2)
       );
       

       • M 表示整数部分加小数部分的总长度, 超过范围插入临界值
       • D 表示小数点后的位数, 不够用0补
       • M 和 D 都可以省略, decimal 默认为(10,0), float, double则根据插入值的精度来决定, 只要在取值范围内即可
       • 定点型的精度较高, 如果要求插入数精度较高时才使用
       • 优先选用占用空间小的和简单的类型

2. 字符型：

   • 较短的文本:

     字符串类型	最多字符数	描述
     char(M)	M	固定长度 M: 0~255 效率稍高
     varchar(M)	M	可变长度 M: 0~65535 效率稍低

     • char 的 M 可以省略, 默认为1
     • varchar 的 M 不可以省略
     • 其他:
       • Enum类, 枚举类型, 要求插入的值必须是列表中指定的值, 否则报错, 不区分大小写
       • Set 类, 类似枚举, 枚举只能插入1个值, set可以插入多个值
   • 较长的文本:
     • text
     • blob(较长的二进制数据)

3. 日期型：

   1. date: 4个字节, 只能保存日期 2010-01-10
   2. datetime: 8个字节, 保存日期和时间 2010-01-10 00:00:00
   3. timestamp: 4个字节, 时间戳 20100110000000
   4. time: 3个字节, 只有时间 21:22:00
   5. year: 1个字节, 只有年份 2020

• timestamp 和 datetime 的区别

  • timestamp 的范围较小, 19700101080001–2038年的某个时间
  • datetime 范围: 1000-1-1 – 9999-1-1
  • timestamp 和实际时区有关,能反映实际日期, 而datetime只能反映插入时的当地时区
  • timestamp的属性首MySQL版本和语法的影响很大

• 插曲

  • show variables like ‘time_zone’; 查看当前时区
  • set time_zone=’+9:00’; 设置时区为东9区

6.常见约束

• 常见约束

  NOT NULL     # 非空约束, 该字段的值不能为空, 比如姓名,学号
  DEFAULT      # 默认约束, 保证该字段的值有默认值, 比如性别
  UNIQUE       # 唯一约束, 保证该字段唯一, 可以为空, 比如座位号
  CHECK        # 检查约束,(MySQL不支持, 加上没效果)
  PRIMARY KEY  # 主键约束, 该字段为主键, 具有唯一性并且非空, 比如序号
  FOREIGN KEY  # 外键约束, 限制两个表的关系, 在从表添加外键约束
               # 用于保证该字段的值必须来自主表的关联列的值, 比如学生表的专业编号
  
  # 语法示例
  create table 表名(
      字段名 字段类型 列级约束,
      字段名 字段类型,
      表级约束
  );
  

• 添加约束的时机:

  1. 创建表时
  2. 修改表时

• 约束的分类:

  1. 列级约束: 6大约束语法上都支持, 但外键约束没有效果
  2. 表级约束: 除了非空, 默认约束, 其他都支持
     • 语法: [constraint 约束名] 约束类型(字段名); 约束名m默认为字段名

  # 列级约束
    CREATE TABLE stuinfo(
        id INT PRIMARY KEY COMMENT '主键',
        stuName VARCHAR(20) NOT NULL,
        gender CHAR(1) CHECK(gender IN ('男','女')),
        seat INT UNIQUE,
        age INT DEFAULT 18,
        majorId INT REFERENCES major(id)
    );
  
  # 表级约束
    CREATE TABLE IF NOT EXISTS stu(
        id INT  PRIMARY KEY,
        sName VARCHAR(20),
        gender CHAR(1),
        seat INT,
        age INT,
        majorId INT,
        # 表级约束
        CONSTRAINT uq UNIQUE(seat), # 唯一键
        CONSTRAINT ck CHECK(gender IN('女','男')),
        CONSTRAINT fk_stu_major FOREIGN KEY(majorId) REFERENCES major(id) # 外键
    );
  
    # 通用写法
    create table if exists stuinfo(
        id int primary key,
        sName varchar(10) not null,
        gender char(1),
        age int default 18,
        set int unique,
        majorId int,
        constraint fk_stuinfo_major foreign key(majorId)
        references major(id)
    );
  

• 主键约束和唯一约束的区别:

  	保证唯一性	是否可以为空	一个表中可以存在	是否允许组合
  主键约束	√	×	至多1个	允许, 但不推荐
  唯一约束	√	√	可以多个	允许, 但不推荐

• 外键的特点

  1. 要求在从表设置外键关系
  2. 从表的外键列的类型和主表的关联列的类型要一致或兼容, 名称无所谓
  3. 主表的关联列必须是一个key(一般是主键或唯一键)
  4. 插入数据时, 先插入主表, 再插入从表, 删除数据时, 先删除从表, 再删除主表

• 标识列 – 自增长列(auto_increment)

  • 从1开始, 每次增长1

  • 标识列不一定是主键, 但是必须是一个key

  • 一个表中至多有1个自增长列

  • 标识列的类型必须是数值型(int, float, double)

    -- 创建表时设置标识列
    create table tab(
        id int primary key AUTO_INCREMENT, -- 自增长, 从1开始
        name varchar(10)
    );
    -- 此时再执行 insert 时, 每次插入数据时, id可以传null, 每次会自动增长
    
    show variables like '%auto_increment%' ; -- 查看增长的初始值和步长
    -- 步长可以修改, set auto_increment=3;  一般不改
    -- 初始值不可以修改, 默认为1
    
    -- 修改表时设置标识列
    alter table tab modify colum id int primary key auto_increment;
    
    -- 删除标识列
    alter table tab modify colum id int;
    

5.数据库事务

一个或一组sql语句组成一个执行单元, 这个执行单元要么全都执行成功, 要么全部执行失败

• TCL 事务控制语言

1.含义

• 通过一组逻辑操作单元(一组DML——sql语句)，将数据从一种状态切换到另外一种状态

• 不同的存储引擎的处理方式不同, 通过show engines;查看数据库支持的引擎

• MySQL 中用的最多的是Innodb, myisam, memory等, 其中 Innodb支持事务, 其他2个不支持

2.事务的特点

• 事务的(ACID)属性

  • 原子性(Atomicity)：要么都执行，要么都回滚
  • 一致性(Consistency)：保证数据的状态操作前和操作后保持一致
  • 隔离性(Isolation)：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰
  • 持久性(Durability)：一个事务一旦提交，则数据将持久化到本地，除非其他事务对其进行修改

• 相关步骤：

  1. 开启事务
  2. 编写事务的一组逻辑操作单元(多条sql语句)
  3. 提交事务或回滚事务

3.事务的分类

• 隐式事务，没有明显的开启和结束事务的标志
  • 比如 : insert. update. delete语句就是一个隐式事务
• 显式事务，具有明显的开启和结束事务的标志
  1. 开启事务: 取消自动提交事务的功能
  2. 编写事务的一组逻辑操作单元(多条sql语句), insert | update | delete
  3. 提交事务或回滚事务

4.使用到的关键字

• 关键字

  show variables like 'autocommit'; -- 查看自动提交属性
  
  set autocommit=0; -- 禁用自动提交, 只对当前会话有效, 每个事务开始前都要写
  start transaction; -- 开启事务(可选的)
  commit; -- 提交
  rollback; -- 回滚
  
  savepoint  断点名 -- 设置保存点
  commit to 断点名  -- 提交到保存点
  rollback to 断点名 -- 回滚到保存点的状态
  

5.事务的隔离级别

• 事务并发问题如何发生？
  当多个事务同时操作同一个数据库的相同数据时

• 事务的并发问题有哪些？

  • 脏读：一个事务读取到了另外一个事务未提交的数据
  • 不可重复读：同一个事务中，多次读取到的数据不一致(强调的是另一个表的更新数据被读取到)
  • 幻读：一个事务读取数据时，另外一个事务进行更新，导致第一个事务读取到了没有更新的数据(强调的是另一个表的插入数据被读取到)

• 如何避免事务的并发问题？

  • 通过设置事务的隔离级别
    1. READ UNCOMMITTED
    2. READ COMMITTED 可以避免脏读(Oracle默认)
    3. REPEATABLE READ 可以避免脏读. 不可重复读和一部分幻读(MySQL默认)
    4. SERIALIZABLE可以避免脏读. 不可重复读和幻读

• 设置隔离级别：

  • set session | global transaction isolation level 隔离级别名;

• 查看隔离级别：

  • select @@tx_isolation;

6.视图

• 含义：理解成一张虚拟的表, 和普通的表一样使用
  通过表动态生成的数据, 只保存sql逻辑, 不保存查询结果

• 视图和表的区别：

  	使用方式	占用物理空间
  视图	完全相同	不占用，仅仅保存的是sql逻辑
  表	完全相同	占用

• 应用场景

  1. 多个地方用到相同的查询结果
  2. 该查询结果的sql语句复杂

• 视图的好处：

  1. sql语句提高重用性，效率高
  2. 和表实现了分离，保护数据, 提高了安全性

1.视图的创建

• 语法：CREATE VIEW 视图名 AS 查询语句;

2.视图的增删改查

-- 1. 查看视图的数据
    - SELECT * FROM my_v4;
    - SELECT * FROM my_v1 WHERE last_name='Partners';

-- 2. 插入视图的数据
    - INSERT INTO my_v4(last_name,department_id) VALUES('虚竹',90); -- 会对原始表插入数据, 没提提供的自动为null

-- 3. 修改视图的数据
    - UPDATE my_v4 SET last_name ='梦姑' WHERE last_name='虚竹'; -- 会对原始表更新

-- 4. 删除视图的数据
    - DELETE FROM my_v4;

    -- 一般是不会更新的

3.某些视图不能更新

• 包含以下关键字的sql语句：分组函数. distinct. group by. having. union或者union all

  • 常量视图
  • Select中包含子查询
  • join
  • from一个不能更新的视图
  • where子句的子查询引用了from子句中的表

    -- eg 不能更新的视图
  
    -- 1
    create or replace view myv1
    as
    select Max(salary), dept_id
    from emp
    group by dept_id
  
    -- 2 常量视图
    create or replace view myv2
    as
    select 'john' name;
  
    -- 3 包含子查询
    create or replace view myv3
    as
    select (select max(salary) from emps) maxSalary;
  
    -- 4 join
    create or replace view myv4
    as
    select lastName, dept_name
    from emp
    join dept
    on emp.id = dept.id;
  
    -- 5 form一个不能更新的视图
    create or replace view myv5
    as
    select * from myv2
  
    -- 6 where子句的子查询引用了from子句中的表
    create or replace view myv6
    as
    select name, email, salary
    from emps
    where empId in(
        select  managerId
        from emps
        where managerId is not null
    );
  

4.视图逻辑的更新

    #方式一：
    -- 创建或修改视图名, 如果视图名存在则修改, 不存在则创建
    CREATE OR REPLACE VIEW test_v7
    AS
    SELECT last_name FROM employees
    WHERE employee_id>100;

    #方式二:
    ALTER VIEW test_v7
    AS
    SELECT employee_id FROM employees;

    SELECT * FROM test_v7;

5.视图的删除

• DROP VIEW test_v1,test_v2,test_v3;

6.视图结构的查看

• DESC test_v7;
• SHOW CREATE VIEW test_v7;

7.存储过程

• 含义：一组经过预先编译的sql语句的集合, 类似Java中的方法
• 好处：
  1. 提高了sql语句的重用性，减少了开发程序员的压力
  2. 简化操作
  3. 减少了编译次数和数据库的连接数, 提高了效率
• 分类：
  1. 无返回无参
  2. 仅仅带in类型，无返回有参
  3. 仅仅带out类型，有返回无参
  4. 既带in又带out，有返回有参
  5. 带inout，有返回有参

  注意：in. out. inout都可以在一个存储过程中带多个

1.创建存储过程

• 语法：

  create procedure 存储过程名(in|out|inout 参数名  参数类型,...)
  begin
      存储过程体
  end
  

• 参数列表：

  • 参数模式, 参数名, 参数类型
  • 举例:
    in stuname varchar(20)

• 注意

  1. 需要设置新的结束标记
     delimiter 新的结束标记

     # 示例：
     # delimiter $
     
     CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型,...)
     BEGIN
         sql语句1;
         sql语句2;
     
     END $
     

  2. 存储过程体中可以有多条sql语句，如果仅仅一条sql语句，则可以省略begin end

  3. 参数前面的符号的意思

     • in:该参数只能作为输入 (该参数不能做返回值)
     • out：该参数只能作为输出(该参数只能做返回值)
     • inout：既能做输入又能做输出

2.调用存储过程

• call 存储过程名(实参列表)

-- 空参列表, 插入5条记录
delimiter $
create procedure myp1()
begin
    insert into admin(username, password)
    values('john1','0000'),('john2','1111'),('john3','2222'),('john4','3333'),('john5','4444')
end $

call myp1()$

-- 传入值
create procedure myp2(in beauty_name varchar(20))
begin
    select bo.* from boys bo
    right join beauty b on bo.id=b.boyfirend_id
    where b.name = beauty_name;
end $

call myp2('java')$

-- 查看用户是否登录成功
create procedure myp3(in username varchar(20), in password varchar(20))
begin
    declare result varchar(20) default '';
    select count(*) into result
    from admin
    where admin.username = username
    and admin.password=password;

    select result;
end $

call myp3('cpp', '0000') $


create procedure myp4(in username varchar(20), in password varchar(20))
begin
    declare result int default 0;
    select count(*) into result
    from admin
    where admin.username = username
    and admin.password=password;

    select if(result>0, '成功','失败');
end $

call myp4('cpp', '0000') $

-- 创建 out 模式的参数
create procedure myp5(in beautyname varchar(20), out boyname varchar(20))
begin
    select bo.boyname into boyname
    from boys bo
    inner join beauty b on bo.id=b.boyfriend_id
    where b.name=beautyname;
end $

set @bname$
call myp5('cpp', @bname)$
select @bname;

-- inout 模式, 传入a, b, 返回a,b的2倍
create procedure myp6(inout a int, inout b int)
begin
    set a=a*2;
    set b=b*2;
end $

set @a=10$
set @b=20$
call myp6(@a, @b) $
select @a,@b

删除与查看存储过程

• drup procedure p1; 一次只能删除一个
• show create proecure p1; 查看存储过程

3.函数

1.创建函数

• 学过的函数：LENGTH. SUBSTR. CONCAT等

• 语法：

  CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
  BEGIN
      函数体
  END
  

2.调用函数

• SELECT 函数名(实参列表)

3.函数和存储过程的区别

	关键字	调用语法	返回值	应用场景
函数	function	select 函数()	有且仅有1个	一般用于查询结果为一个值并返回时
存储过程	PROCEDURE	CALL 存储过程()	可以有0个或多个	一般用于批量插入和更新

-- 案例演示
-- 无参有返回
create function myf1() returns int
begin
    declare c int default 0; -- 定义局部变量
    select count(*) into c
    from employees;
    return c;
end ;

select myf1();

-- 有参有返回
create function myf2(empName varchar(20)) returns double
begin
    set @sal=0; -- 用户变量
    select salary into @sal
    from employees
    where lastName=empName;

    return @sal;
end;

select myf2('king');

查看和删除函数

• show create function myf3; 查看函数
• drop function myf3;, 删除函数

8.流程控制结构

1.系统变量

1. 全局变量

• 作用域：针对于所有会话(连接)有效，但不能跨重启

  -- 查看所有全局变量
  SHOW GLOBAL VARIABLES;
  -- 查看满足条件的部分系统变量
  SHOW GLOBAL VARIABLES LIKE '%char%';
  -- 查看指定的系统变量的值
  SELECT @@global.autocommit;
  -- 为某个系统变量赋值
  SET @@global.autocommit=0;
  SET GLOBAL autocommit=0;
  

2. 会话变量

• 作用域：针对于当前会话(连接)有效

  -- 查看所有会话变量
  SHOW SESSION VARIABLES;
  -- 查看满足条件的部分会话变量
  SHOW SESSION VARIABLES LIKE '%char%';
  -- 查看指定的会话变量的值
  SELECT @@autocommit;
  SELECT @@session.tx_isolation;
  -- 为某个会话变量赋值
  SET @@session.tx_isolation='read-uncommitted';
  SET SESSION tx_isolation='read-committed';
  

2.自定义变量

1. 用户变量

-- 声明并初始化：

SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

-- 赋值：
-- 方式一：一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
SELECT 变量名:=值;
-- 方式二：一般用于赋表 中的字段值
SELECT 字段名或表达式 INTO 变量
FROM 表;

-- 使用：
select @变量名;

2. 局部变量

-- 声明：
declare 变量名 类型 [default 值];

-- 赋值：
-- 方式一：一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
SELECT 变量名:=值;
-- 方式二：一般用于赋表 中的字段值
SELECT 字段名或表达式 INTO 变量
FROM 表;

-- 使用：
select 变量名

• 局部变量和用户变量二者的区别：

	作用域	定义位置	语法
用户变量	当前会话	会话的任何地方	加@符号，不用指定类型
局部变量	定义它的BEGIN END中	BEGIN END的第一句话	一般不用加@,需要指定类型

3.分支结构

1. if函数

• 语法：if(条件，表达式1，表达式2), 调用 select if()
  • 如果条件成立,则返回表达式1的值, 否则返回表达式2的值
• 特点：可以用在任何位置

2. case语句

-- 语法：
-- 情况一：类似于switch
case 变量|表达式|字段
when 值1 then 结果1或语句1(如果是语句，需要加分号)
when 值2 then 结果2或语句2(如果是语句，需要加分号)
...
else 结果n或语句n(如果是语句，需要加分号)
end [case](如果是放在begin end中需要加上case，如果放在select后面不需要)

-- 情况二：类似于多重if
case
when 条件1 then 结果1或语句1(如果是语句，需要加分号)
when 条件2 then 结果2或语句2(如果是语句，需要加分号)
...
else 结果n或语句n(如果是语句，需要加分号)
end [case](如果是放在begin end中需要加上case，如果放在select后面不需要)

• 特点：
  • 可以作为表达式, 嵌套在其他语句中使用, 可以放在任何位置
  • 也可以作为独立的语句使用, 只能在begin end中

-- 举例
create procedure test_case(in score int)
begin
    case
    when score>=90 and score<=100 then select 'A';
    when score>=80 then select 'B';
    when score>=60 then select 'C';
    else select 'D';
    end case;
end ;

3. if elseif语句

-- 语法：
if 情况1 then 语句1;
elseif 情况2 then 语句2;
...
else 语句n;
end if;

• 特点：只能用在begin end中！！！！！！

• 三者比较：

  函数	应用场合
  if函数	简单双分支
  case结构	等值判断的多分支
  if结构	区间判断的多分支

4.循环

• 分类: while, loop, repeat
• 循环控制:
  iterate (continue)
  leave (break)

-- 1. while 语法：
[标签：]WHILE 循环条件  do
    循环体;
END WHILE [标签];

-- 2. loop , 默认死循环, 要搭配控制语句
[标签：] loop
    循环体;
END loop [标签];

-- 3. repeat ( do while)
[标签：] repeat
    循环体;
until 结束循环的条件
end repeat [标签];

• 特点：
  • 只能放在BEGIN END里面
  • 如果要搭配leave跳转语句，需要使用标签，否则可以不用标签
  • leave类似于java中的break语句，跳出所在循环！！！

-- 批量插入
create procedure pro_while1(in insertCount int)
begin
    declare i int default 1;
    where i<= insertCount do
        insert into admin(username, `password`)
        values(concat('java',i), 'asdf');
        set i=i+1;
    end where;
end;

call pro_while1(100);

-- eg2
create procedure pro_while2(in insertCount int)
begin
    declare i int default 1;
    a:where i<= insertCount do
        insert into admin(username, `password`) values(concat('cpp',i), 'asdfasd');
        if i>=20 then leave a; end if;
        set i=i+1;
    end where a;
end;

call pro_while2(100);

-- eg3
create procedure pro_while3(in insertCount int)
begin
    declare i int default 0;
    a: where i<= insertCount do
        set i=i+1;
        if mod(i,2)!=0 then itreate a; end if;
        insert into admin(username, `password`)
        values(concat('python',i), 'assaddf');
    end where a;
end;

call pro_while3(100);