常见约束

 六大约束:
            NOT NULL:非空约束,保证字段不能为空
            CHEAK:检查约束,限定字符(mysql不支持)
            UNIQUE:唯一约束,保证字段唯一性,可以为空
            DEFAULT:默认约束,字段的默认值
            PRIMARY KEY : 主键约束,主键字段唯一且不为空
            FOREIGN KEY:外键约束,保证字段的值必须来至主表的关联列的值
 约束添加分类:
            列级约束:
                     六大约束都可以写,但是外键没有效果
            表级约束:
                     除了非空和默认都可以写
 主键和约束的比较
            主键:唯一性 最多一个 可以组合
            唯一:唯一性 可以为空 可以多个 可以组合

列级约束

  • 只需在创建字段时在后面添加约束即可
    1
    2
    3
    4
    5
    6
    7
    8
    CREATE TABLE stuinfo(
    stuid int PRIMARY KEY, --主键
    stunama varchar(5) NOT NULL, --非空
    seat int UNIQUE, --唯一
    gender char(1) CHECK(gender in('男','女')), --检查
    age int DEFAULT 18 --默认

    );

    表级约束

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    CREATE TABLE stuinfo2(
    stuid int,
    stuname varchar(5),
    seat int,
    gender char(1),
    major_id int,
    CONSTRAINT pk PRIMARY KEY(stuid),
    CONSTRAINT name_y UNIQUE(stuname),
    CONSTRAINT gender_y CHECK(gender in('男','女')),
    CONSTRAINT FS_STU_major FOREIGN KEY(major_id) REFERENCES major(major_id) --外键

    );
    CREATE TABLE major(
    major_id int PRIMARY KEY,
    major_name varchar(10) UNIQUE

    );
    :::info no-icon
    一般使用表级约束写外键,其他的都可以写成列级约束
    :::
    修改约束和添加约束
    1
    2
    3
    4
    5
    ALTER TABLE 表名 MODIFY COLUMN 字段 varchar(10) UNIQUE;
    # 列级约束
    ALTER TABLE 表名 MODIFY COLUMN 字段 int PRIMARY KEY;
    # 表级约束
    ALTER TABLE 表名 ADD COLUMN PRIMARY KEY(字段);

标识列(

  • 自增长列,无需插入)类型只能时数值型 关键字:AUTO_INCREMENT
    1
    2
    3
    4
    CREATE TABLE IF NOT EXISTS id_identity(
    id int PRIMARY KEY AUTO_INCREMENT,
    name varchar(10)
    );
    :::warning
  • 一个表仅仅可以有一个自增长列
  • 自增长列必须为一个键(primary key|unique|foreign key)
    :::

查看auto_increment参数

1
2
3
4
5
6
7
8
9
SHOW VARIABLES LIKE '%AUTO_INCREMENT%';

/*
auto_increment_increment,1 步长为1
auto_increment_offset,1 起始值为1
*/
# 设置步长和初始值
SET auto_increment_increment=2;
SET auto_increment_offset=5; -- MYSQL不支持设置初始值,设置后没效果,设置初始值需要自己在插入第一个元素时设置

事务

    TCL语言(Transaction Control Language)事务控制语言
事务:
    一个或一组sql语句构成一个执行单元,要么全部执行要么全部不执行
事务的4大属性(ACID):
    1.原子性(Atomicity)
            事务是一个不可分割的单位,要么一起执行,要么全不执行
    2.一致性(cansistency)
            事务必须从数据库的一致性转换到另一个一致性状态(即数据不变状态能量守恒)
    3.隔离性(isolation)
            一个事务的执行不能被其他事务干扰,并发执行的各事务之间不能相互干扰
    4.持久性(Durability)
            事务一旦提交,对数据的改变时永久性的
回滚:回到最初的状态

查看存储引擎

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SHOW ENGINES;
/*
MEMORY,YES,"Hash based, stored in memory, useful for temporary tables",NO,NO,NO
MRG_MYISAM,YES,Collection of identical MyISAM tables,NO,NO,NO
CSV,YES,CSV storage engine,NO,NO,NO
FEDERATED,NO,Federated MySQL storage engine,,,
PERFORMANCE_SCHEMA,YES,Performance Schema,NO,NO,NO
MyISAM,YES,MyISAM storage engine,NO,NO,NO
InnoDB,DEFAULT,"Supports transactions, row-level locking, and foreign keys",YES,YES,YES
BLACKHOLE,YES,/dev/null storage engine (anything you write to it disappears),NO,NO,NO
ARCHIVE,YES,Archive storage engine,NO,NO,NO

InnoDB支持事务myisam、memory不支持事务
*/

:::info no-icon
InnoDB
:::

  • 是 MySQL 默认的事务型存储引擎,只有在需要它不支持的特性时,才考虑使用其它存储引擎。
    实现了四个标准的隔离级别,默认级别是可重复读(REPEATABLE READ)
    。在可重复读隔离级别下,通过多版本并发控制(MVCC)+ 间隙锁(Next-Key Locking)防止幻影读。
    主索引是聚簇索引,在索引中保存了数据,从而[避免直接读取磁盘]{.yellow},
    因此对查询性能有很大的提升。 内部做了很多优化,包括从磁盘读取数据时采用的可预测性读、能够加快读操作
    并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。 支持真正的在线热备份。
    其它存储引擎不支持在线热备份,要获取一致性视图需要停止对所有表的写入,而在读写混合场景中,停止写入可能也意味着停止读取。
    :::info no-icon
    MyISAM
    :::
  • 设计简单,数据以紧密格式存储。对于只读数据,或者表比较小、可以容忍修复操作,则依然可以使用它。
    提供了大量的特性,包括压缩表、空间数据索引等。
    :::warning
  • 事务: InnoDB 是事务型的,可以使用 Commit 和 Rollback 语句。
  • 并发: MyISAM 只支持表级锁,而 InnoDB 还支持行级锁。
  • 外键: InnoDB 支持外键。
  • 备份: InnoDB 支持在线热备份。
  • 崩溃恢复: MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多,而且恢复的速度也更慢。
  • 其它特性: MyISAM 支持压缩表和空间数据索引。
    :::
    事务的创建隐式事务没有明显的开启和结束的标记列如:insert、updata等
  • 前提:必须关闭自动提交
    1
    2
    3
    4
    5
    6
    7
    开启事务
    set autocommit = off;
    start transaction;
    编写sql语句
    结束事务
    commit;提交事务
    rollback;回滚数据

    事务案列 转账

    1
    2
    3
    4
    5
    set autocommit = off;
    start transaction;
    update test1 set salary= 500 WHERE username='张无忌';
    update test1 set salary= 1500 WHERE username='郭襄';
    commit;

    事务的隔离级别

  • mysql 默认repeatable read级别
  • oracle 默认read commit 级别
    查看隔离级别
    1
    SELECT @@transaction_isolation
    设置隔离级别
    1
    SET session|global transaction isolaction level 隔离级别
    | 事务的隔离级别 | 脏读 | 不可重复读 | 幻读 |
    |-|-|-|-|
    | read uncommit | √ | √ | √ |
    | read commit | × | √ | √ |
    | repeatable read | × | × | √ |
    | serializable | × | × | × |

    回滚

    演示delete和truncate回滚
    1
    2
    3
    4
    set autocommit = 0;
    start transaction;
    delete from 表名;
    ROLLBACK --回滚将此操作回滚到上一次数据(相当于没有此操作)
    1
    2
    3
    4
    set commit = 0;
    start transaction;
    truncate from 表名;
    rollback;
    :::warning
  • delete支持回滚
  • truncate不支持回滚
    :::
    设置回滚点
    1
    2
    3
    4
    5
    6
    set autocommit = 0;
    start transaction;
    delete from 表名 where id=1;
    savepoint a;
    delete from 表名 where id =3;
    rollback to a; --回滚到保存点a三号数据没有被删除

    视图

  • 在使用视图时动态生成,只保存了sql逻辑,不保存查询结果
  • 一种虚拟存在的表
    1
    2
    CREATE VIEW v1 AS
    查询语句
  • 重用sql语句
  • 简化复杂的sql语句
  • 保护数据

    删除外键

    级联删除

  • 级联删除,如果有外键存在怎么删除外键和从表中有外键约束的元素
  • 在添加外键时添加级联子句 ON DELETE CASCADE
    1
    2
    3
    4
    5
    #添加级联删除
    alter table 表名
    add constraint 外键名 foreign key(字段) references 从表(字段) ON DELETE CASCADE;

    delete from major where major_id =3;#删除3号专业时stuinfo中的数据与3号相关的也会删除

    级联制空

  • 级联删除,如果有外键存在怎么制空外键和从表中有外键约束的元素
  • 在添加外键时添加级联子句 ON DELETE SET NULL
    1
    2
    3
    4
    5
    # 级联制空
    ALTER TABLE stuinfo ADD constraint stu_maj2
    foreign key(majorid) references major(major_id) ON DELETE SET NULL;
    delete from major where major_id = 3;
    -- 删除3号专业时stuinfo中的数据与3号相关的会变成null

    变量:

    系统变量

  • 变量有系统提供,属于服务器层面
    查看系统变量
    1
    SHOW GLOBAL|SESSION VARIABLES
  • global 全局变量
  • session 会话变量
    查看条件的系统变量
    1
    SHOW GLOBAL|SESSION VARIABLES LIKE '%character%'
    查看指定的系统变量名
    1
    SELECT @@GLOBAL|SESSION.系统变量名; 
    给变量名赋值
    1
    2
    SET global|session 变量=值;
    set @@global|session.变量=值;
    :::warning no-icon
  • 全局作用域:服务器每次启动将为全局变量赋初值,修改值只在本次生效,重启后将恢复初值
  • 会话作用域:仅对当前会话(连接)有效
    :::

    自定义变量

  • 用户变量
    1
    2
    3
    SET @用户变量名=值;
    SET @用户变量名:=值;
    SELECT @用户变量名:=值;
  • 局部变量
    1
    2
    3
    4
    5
    6
    声明:
    DECLARE 变量名 类型;
    赋值:
    SET 用户变量名=值;
    SET 用户变量名:=值;
    SELECT @用户变量名:=值;
    :::warning no-icon
  • 用户变量作用域:针对当前会话有效
  • 局部变量作用域:仅仅在定义的bdgin end中生效
  • 局部变量的声明只能在begin end的第一句
    :::

    存储过程和函数

    [类似java的方法]{.label .info}
  1. 提高代码重用性
  2. 简化操作
  3. 减少了编译次数并减少了与服务器连接的次数,提高了效率
  • 含义:一组预先编译好的sql语句集合
    1
    2
    3
    4
    CREATE PROCEDURE 存储名(参数模式 参数名 参数类型 )
    BEGIN
    方法体
    END
    参数模式
    1
    2
    3
    4
    5
    in      该参数可以作为输入,也就是该参数需要传入值
    out 该参数可以作为输出,也就是该参数作为返回值
    inout 该参数可以作为输入和输出,也就是该参数既需要输入值也可以作为返回值

    结尾 DELIMITER 结束标记
    调用存储过程
    1
    call 存储名(参数);

    创建存储过程的案例

    :::info no-icon
  • 无参存储过程
    :::
    1
    2
    3
    4
    5
    6
    7
    8
    9
    # 用函数插入五条数据
    DELIMITER // -- 设置结束标记
    CREATE PROCEDURE sum()
    BEGIN
    INSERT INTO admin(username,'password') values ('jerry',1111),('jack',2222)
    ,('anny',3333),('lite',4444),('tom',7777);
    end //;

    call sum(); -- 调用函数
    :::info no-icon
  • 带in的存储过程
    :::
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    # 判断登录人员信息
    DELIMITER //
    CREATE PROCEDURE myprocedure3(IN usernamae varchar(10),IN password varchar(10))
    BEGIN
    declare result int default 0; -- 定义局部变量

    select count(*) INTO result
    from admin
    where admin.username=username
    and admin.password=password;

    select IF(result>0,'登录成功,欢迎管理员','登录失败,无关人员') AS '结果';

    end //
    :::info no-icon
  • 带out的存储过程
    :::
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    # 根据女生名字显示出男生姓名
    DELIMITER //
    CREATE PROCEDURE myprocedure4(IN beautyname varchar(10),OUT boname varchar(10))
    BEGIN
    SELECT bo.boyName
    from boys bo
    right join beauty b
    on b.boyfriend_id = bo.id
    where b.name = beautyname;

    end //

    call myprocedure4('小昭',@resultname);
    :::info no-icon
  • 带inout的存储过程
    :::
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    # 返回参数的2
    DELIMITER //
    CREATE procedure myprocedure5(INOUT a INT,INOUT b INT)
    BEGIN
    SET a=a*2;
    SET b=b*2;
    end //

    SET @m=10;
    SET @n=30;
    call myprocedure5(@m,@n);
    select @m,@n;
    删除存储过程
    1
    DROP PROCEDURE 存储名;

    函数

    [创建函数]{.label .info}
    1
    2
    3
    4
    CREATE FUNCTION 函数名() RETURNS 返回类型
    BEGIN
    函数体
    END
    [调用函数]{.label .info}
    1
    select 函数名()
    [函数案列]{.label .info}
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    # 返回beauty表的字段个数
    SET global log_bin_trust_function_creators=true; -- 如果出现1418错误可以设置这个

    delimiter //
    CREATE FUNCTION myfunction1() returns int
    begin
    declare c int default 0;
    select count(*) into c
    from beauty;
    return c;
    end //

    select myfunction1();
    [查看函数]{.label .info}
    1
    SHOW CREATE FUNCTION 函数名
    [删除函数]{.label .info}
    1
    DROP FUNCTION 函数名

    区别

    :::warning
  • 存储过程能有多个返回
  • 函数只能有一个返回
    :::

    分支结构

    IF
    1
    2
    IF(表达式1,表达式2,表达式3)
    如果表达式1成立返回表达式2,否则返回表达式3
    case
    1
    2
    3
    4
    5
    6
    case 变量|表达式|字段
    WHEN 判断 then 值;
    WHEN 判断 then 值;
    。。。。;
    ELSE 值;
    END case;
    if结构
    1
    2
    3
    4
    if 条件 then 语句;
    elseif 条件 then 语句;
    。。。。
    else 语句;

    case案例

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    # 根据成绩判断等级
    delimiter //
    CREATE PROCEDURE myfunc3(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 //;

    call myfunc3(85);

    IF案例

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    # 根据成绩判断等级
    set global log_bin_trust_function_creators = true;

    delimiter //
    CREATE FUNCTION myfunc3(score int) returns char(1)
    begin
    IF score>=90 and score <=100 then return 'A';
    ELSEIF score>=80 then return 'B';
    ELSEIF score>=60 then return 'C';
    else return 'D';
    end IF;
    end //;

    select myfunc3(85);

    循环结构

  • while、loop、repeat
  • iterate 类似java的continue
  • leave 类似java的break
    while
    1
    2
    3
    开始标签:while 循环条件 do
    循环体
    end while 结束标签;
    loop
    1
    2
    3
    开始标签:loop
    循环体
    end loop 结束标签;
    :::warning
  • 一般搭配leave使用,否则成死循环
    :::
    repeat
    1
    2
    3
    4
    开始标签: repeat
    循环体
    until 结束循环条件
    end repeat 结束标签;
    :::warning
  • while类似于java的while
  • repeat类似于java的do。。while
  • loop类似于死循环
    :::

    案列

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    # 添加数据(限制最多添加10个)
    delimiter //
    create procedure pro_while2(IN insertcount int)
    begin
    declare i int default 1;
    s: while i<= insertcount do
    insert into admin(username,password) value(concat('panther',i),concat('111',i));
    set i=i+1;
    IF i >10 then leave s;
    end if;
    end while s;
    end //
    call pro_while2(15);