MySQL基础_day03

1.连接查询:(inner join、left join、right join)

        a.inner join:内连接

create table teacher(
    id int primary key,
    name varchar(20)
);
create table student (
    id int primary key,
    name varchar(20),
    teacher_id int
);
alter table student add foreign key(teacher_id) references teacher(id);
insert into  teacher (id,name) values(1,'张老师'),(2,'王老师'),('3', '刘老师');;
insert into  student (id,name,teacher_id) values(1,'小明',1),(3,'小黑',2);
insert into student (id,name) values(2,'小华');

--inner join:数据是以左表数据为基准,
--左表中有的,右表中没有的数据,都不会生成;同样右表中有的,左表中没有,一样不会生成

select * from student s inner join teacher t on s.teacher_id=t.id;

select * from teacher t inner join student s on t.id=s.teacher_id;

        b.left join:左连接

--left join:以左边的表为基准,
--左表中有的,右表中不满足on的条件的,就用null代替
select * from student s left join teacher t on s.teacher_id=t.id;--小华没有teacher_id,所以教师一栏全为null

select * from teacher t left join student s on t.id=s.teacher_id;

        c.right join:右连接(不经常使用)

2.事务及存储过程

        a.事务的特性(acid):

                原子性(atomic):指操作不能够分割

                一致性(consistent):操作前后,保持状态一致

                隔离性(isolation):事务与事务之间的隔离关系

                        共有四种隔离级别:read uncommitted、read committed、repeatrable read、serializable

                        脏读:事务隔离级别处于read uncommitted时,即读取了别人未提交的数据

                持久性(durable):数据在数据库中,稳定持久的存在

--查看默认的隔离级别
select @@tx_isolationl;

--修改默认的隔离级别
set global transaction isolation level 隔离级别;

--开启事务
start transaction;    begin transaction;

--回滚:提交事务之后,是无法实现回滚的
rollback;

--结束事务
commit;

        b.存储过程:

                1).创建存储过程:

--无返回值的
delimiter //    --更改语句结束标志
create procedure 存储过程名(变量1 数据类型1,...)
begin
    sql代码;
end//
delimiter ;    --更改回原来的结束标志

--有返回值的
delimiter //
create procedure 存储过程名(in 变量1 数据类型1,out 变量2 数据类型2,...) --in是输入参数,out是返回值
begin
    sql语句;
end//
dilimiter ;

                2).调用存储过程:

--调用无返回值的
call 存储过程名(参数1,...);

--调用有返回值的
call 存储过程名(参数1,参数2,...,@aaa) --@aaa是返回值的名
select @aaa as 'xxx';

                3).if

delimiter //
create procedure addSalary(in money float,in idd bigint)
begin
    if money>0 then
        update emp set salary=salary+money where id=idd; --emp:表名
    end if;
end //
delimiter ;

call addSalary(100,2);

                4).if-else

delimiter //
create procedure addSalary(in money float,in idd bigint)
begin
    if money>0 then
        update emp set salary=salary+money where id=idd; --emp:表名
    else
        select '你在逗我玩?' as '友情提示';
    end if;
end //
delimiter ;

call addSalary(3000,3);

                5).if_elseif

delimiter //
create procedure buyCar(in money float)
begin
    if money>500 then
        select '买凯迪拉克' as '买啥车';
    elseif money>100 then
        select '买帕萨特' as '买啥车';
    elseif money>50 then
        select '买伊兰特' as '买啥车';
    else
        select '买自行车' as '买啥车';
    end if;
end //
delimiter ;

call buyCar(60);

                6).case:选择结构

delimiter //
create procedure callPhone(in flag int)
begin
    case flag
        when 1 then
            select '111' as 提示;
        when 2 then
            select '222' as 提示;
        when 3 then
            select '333' as 提示;
        when 4 then
            select '444' as 提示;
        else
            select '555' as 提示;
    end case;
end //
delimiter ;

call callPhone(2);

                7).while循环

DELIMITER //
CREATE PROCEDURE addStudent(IN num INT)
BEGIN
	DECLARE i INT DEFAULT 1;-- 定义临时变量i,并赋值为1;
	WHILE i<=num DO
		INSERT INTO students(stuName,gender) VALUES (CONCAT('张三',i),'男');
		SET i=i+1; -- 循环的步长
	END WHILE;
END//
DELIMITER ;
CALL addStudent(5);

TRUNCATE TABLE students;

                8).loop循环

DELIMITER //
CREATE PROCEDURE addstuname(IN num INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	aa:LOOP
		INSERT INTO students(stuName,gender,classId) VALUES(CONCAT('张三',i),'男',2);
		SET i=i+1;
		
		IF i=num+1 THEN
			LEAVE aa;
		END IF;
	END LOOP;
END//
DELIMITER ;

CALL addstuname(40);