一、六大约束

1.primary key ；一个表中只能有一个主键约束，一个主键约束可由多个字段组成，且设为主键的字段的值不能为空。

2.unique；一个表中可有多个唯一键约束，not可以为空。

3.not null；可有多个非空约束，非空约束字段值不能为空。

4.default；表中可有多个默认值，当插入值不设置时，自动补充设置的默认值。

5.check；检查约束，插入的值只有通过check检查时才能插入。

6.foreign key;外键约束，外键约束的字段值必须是主表的键。

实例：

create table student(

id int primary key auto_increment,

name varchar(20) unique not null,

sex char check(sex='男' or sex='女'),

age int default 18,

majorid int ,

constraint fk_student_major foreign key (majorid) reference major(id)

); 

/*1.除外键其他约束基本用列级约束，外键用表级约束

2.创建表时可添加标识列auto_increment,标识列必须加在一个key上，一个遍只能添加一个标识列，且标识列必须加在数据类型为整型、小数等类型后。

3.标识列可设置步长 set auto_increment_incremet=常数

二、事务

特性（ACID）

1.原子性（atomicity):要么都执行，要么都回滚

 2.一致性(consistency):保证数据的状态操作前和操作后保持一致

3.隔离性(isolation):多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰

4.持久性(surability):一个事务一旦提交，则数据将持久化到本地，除非其他事务对其进行修改

显示事务运行

set autocommit=0;

srart transaction;

insert/delete/update 语句

commit/rollback

事务并发问题

1.读脏：一个事务读取到了另外一个事务未提交的数据

2.不可重复读：同一个事务中，多次读取到的数据不一致

 3.幻读：一个事务读取数据时，另外一个事务进行更新，导致第一个事务读取到了没有更新的数据

避免措施

1.READ COMMITTED 可以避免脏读

2.REPEATABLE READ 可以避免脏读、不可重复读和一部分幻读

3.SERIALIZABLE：避免脏读、不可重复读和幻读,但是性能特别地

一般设定为：repeatable read

设置隔离级别语句：set session/global transaction level 级别名

查看级别： select @@tx_isolation

三、视图

创建视图

create view 视图名

as

select语句；

增删改视图

1.查询视图数据：select* from 视图名；

2.查看视图结构：desc 视图名 或 show create view 视图名；

3.插入视图数据:insert into 视图名(字段名) values （字段值);

4.修改视图数据：update 视图名 set

5.删除试图数据：delete from 视图名；

6.删除试图：drop view 视图1，视图2~;

以下情况不能更新试图

1.sql语句包含分组函数、distinct、group by、having、union或者union all

2.常量视图(create view v3 as select 'john' ）

3.Select中包含子查询 join

4.from一个不能更新的视图

5.where子句的子查询引用了from子句中的表

四、变量

系统变量

全局变量      global               会话变量      session

1.查看所有变量 show global/session variables

2.查看满足条件变量：show global/session variables like '  '

3.查看指定系统变量的值：select @@global/session.变量名

4.为某个变量赋值：set global/session 变量名=值

                                set @@global/session.变量名=值

**所有的session都可以省略，例：select @@变量名；表示查询会话变量的指定变量值，只针对当前的连接有效。全局变量跨连接有效，但不可跨重启。

用户自定义变量

用户变量

作用于：针对于当前会话连接有效，作用域同于会话变量；

1.声明初始化：set @用户变量名=值

                      set @用户变量名:=值

                      select @用户变量名:=值

2.赋值（更新用户变量值）：

              方式一：通过set select

              set @用户变量名=值

              set @用户变量名:=值

              select @用户变量名:=值

              方式二：通过select into

              select 字段 into  变量名  from 表；

              例：select count(*) into  @count  from employees

3.查看用户变量名：select @用户变量名

局部变量

作用于：仅在定义他的begin end中，且必须放在begin end 中的第一句。

1.声明：declare 变量名  类型 【default 值】;#default赋初值

2.赋值:方式一：通过set select

                   set 局部变量名=值

                   set 局部变量名:=值

                   select 局部变量名:=值

            方式二：通过select into

                   select 字段 into 变量名 from 表；

3.使用：select 变量名；（注意此处没有@）

五、存储过程

含义：一组经过预先编译的sql语句的集合，理解成批处理语句。

创建存储过程

create procedure 存储过程名（参数列表）

begin

           存储过程体（一组合法的sql体）

end

**注意1.参数列表包含三部分  

             参数模式  ：IN:该参数需要调用方输入值；

                                 OUT:该参数可以作为返回值；

                                 INOUT:该参数及需要传入值，又可以返回值；

             参数名 

             参数类型

2.若参数过程体中只有一句话，则begin end可以省略

3.存储过程体中的sql语句必须加分号，存储过程体结尾用“delimiter 结束标记”

调用

call   存储过程名（实参列表）；

实例

1.参数列表为空

delimiter $

CREATE procedure myp1()

BEGIN

 insert into admin(username,`password`)

values ('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');

 end $

调用：call myp1() $

2.创建带in模式参数的存储过程

传一个in参数的存储过程

CREATE PROCEDURE myp2(in beautyname varchar(20))

BEGIN

SELECT bo.* FROM boys bo RIGHT JOIN beauty b on b.boyfriend_id=bo.id WHERE b.name=beautyname;

END $

传两个in参数的存储过程

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 $

3.创建带out参数的存储过程

创建带一个in一个out的存储过程(输入beautyname 输出boyname)

CREATE PROCEDURE myp8 (in beautyname VARCHAR(20),out boyname VARCHAR(20))

 begin

SELECT bo.boyname into boyname from boys bo INNER JOIN beauty b on b.boyfriend_id=bo.id where b.name=beautyname;

end

set @bname='  '    #此处将bname设定为用户变量

call myp8('唐艺昕',@bname)

SELECT @bname $

创建带两个out的存储过程

CREATE PROCEDURE myp11(in beautyname VARCHAR(20),out boyname varchar(20),out usercp int )

BEGIN

SELECT bo.boyname,bo.usercp into boyname,usercp from boys bo INNER JOIN beauty b on b.boyfriend_id=bo.id where b.name=beautyname;     #注意此处boyname/usercp是局部变量，此处为两个字段同时赋值

END

call myp11('唐艺昕',@bname,@bcp)

select @bname,@bcp

4.创建inout参数的存储过程

CREATE procedure mpy13(inout a int,inout b int)

BEGIN

      set a=a*2;

      set b=b*2;

end $

set @a=10

 set @b=20

call mpy13(@a,@b)

SELECT @a,@b

删除

drop procedure 存储过程名；

查看存储过程结构：show create prodecure 存储过程名

六、函数

函数定义类似于存储过程

创建函数

create fincation 函数名(【参数类型】) returns 数据类型

begin

变量声明

SQL语句

return 变量名

end

实例

1.无参有返回

 CREATE function my1() RETURNS INT

begin

        declare c int DEFAULT 0; #声明局部变量

        SELECT count(*) into c from employees;

         return c;

END

SELECT my1()

2.有参有返回

CREATE FUNCTION my3(empname VARCHAR(20)) RETURNS DOUBLE

begin

        set @sal=0;

        SELECT salary into @sal from employees e where e.last_name=empname;

        RETURN @sal;

END

SELECT my3('kochhar');

查看函数结构

show create function 函数名;

删除函数：

drop function 函数名；

七、流程控制结构

顺序结构：从上往下依次执行

分支结构：程序从两条或多条路径中选择一条

1.if函数：实现简单双分支

语法：

if（表达式1，表达式2，表达式3） 若表达式1成立，则返回表达式2，不成立返回表达式3。

2.case结构：一般用于实现等值判断（类似于switch），或区间判断（类似于if)

语法一：

case 变量/表达式/字段

when 要判断的值  then 返回值1或语句；

when 要判断的值  then 返回值2或语句；

......

else 要返回值n；

END case;

语法二：

case

when 要判断的条件  then 返回值1或语句；

when 要判断的条件  then 返回值2或语句；；

......

else 要返回值n

END case；

特点：作为独立语句执行时必须放在 begin end中

else 可以省略，若else省略且所有的when都不成立则返回null

3.if结构：只能应用在begin end

语法

if 条件1 then 语句1；

elseif  条件2 then 语句2；

......

【else 语句n】

end if

循环结构：程序在满足一定的条件基础上，重复执行一段代码

1.while

语法:

【标签】while 循环条件 do

                             循环体；

              end while 【标签】；

实例：

CREATE PROCEDURE test_while3(in insertcount int)

begin

      DECLARE i int DEFAULT 1;

      a:while i<intercount do

            set i=i+1;

            if i%2!=0 then iterate;

           end if;

           insert into  admin(username,`password`)VALUES(concat('杉菜',i),'6666');

           end while a;

end

2.loop:

语法：【标签】 loop

                                       循环体；

                          end loop 【标签】；

3.repeat

语法：【标签】 repeat

                                      循环体；

                          until 结束循环的条件

                          end repeat 【标签】；

4.循环控制 

                      iterate(类似于continue）结束本次循环进行下一次；

                      leave（类似于break）跳出，结束当前循环；