MySQL数据库学习笔记（Ⅰ）

create database demo_01 default charset =utf8mb4;
use demo_01;
show tables ;
drop table city;
create table city
(
  city_id    int auto_increment
    primary key,
  city_name  varchar(50) not null,
  country_id int         not null
) engine=InnoDB default charset=utf8;

create table country
(
	country_id int auto_increment,
	country_name varchar(100) not null,
	constraint country_pk
		primary key (country_id)
)engine=InnoDB default charset=utf8;

show tables ;
select *from city;

create index idx_city_name on city(city_name); # 创建索引 create index index_name on table_name(column_name)

show index from city;

drop index idx_city_name on city;

alter table city add unique idx_city_name(city_name);

create index idx_city_name_country_id on city(city_name,country_id); # 组合索引

select * from  city c, country t where c.country_id=t.country_id;

create view view_city_country as (select c.*,t.country_name from  city c, country t where c.country_id=t.country_id); # 创建视图

select * from view_city_country; # 查看视图

update view_city_country set city_name='广州' where city_id=1; # 更新视图会更新表

show create view view_city_country; # 查看创建视图语句

# 创建存储过程
delimiter $ # 声明分隔符为 $
create procedure pro_city_name()
begin
  select city_name from city;
end $

# 调用存储过程
call pro_city_name();

# 查询存储过程
show procedure status where Db='demo_01';

# 查看存储过程的定义
show create procedure pro_city_name;

delimiter $
create procedure pro_num()
begin
  declare num int default 10; # 声明变量
  select concat('num的值为：',num);
end $

call pro_num();

delimiter $
create procedure pro_set_num()
begin
  declare num int default 10; # 声明变量
  set num = num + 10;
  select num;
end $

call pro_set_num();

delimiter $
create procedure pro_set_city_num()
begin
  declare num int;
  select count(*) into num from city;
  select concat('city表的记录数为：',num);
end $

call pro_set_city_num();

delimiter $
create procedure pro_set_description()
begin
  declare height int default 175;
  declare description varchar(50);
  if height>= 180 then
    set  description='高个子';
  elseif height>=170 then
    set description='中等个子';
  else set description='矮个子';
  end if;
  select concat('身高为',height,'是一个',description);
end $

call pro_set_description();

# 函数 in输入参数，out输出参数，inout输入输出
delimiter $
create procedure pro_inout(in height int, out description varchar(50))
begin
  if height>= 180 then
    set  description='高个子';
  elseif height>=170 then
    set description='中等个子';
  else set description='矮个子';
  end if;
end $

call pro_inout(190,@description);
# @value 用户会话变量 @@value 系统会话变量
select @description;

# 游标 存储查询结果集的数据类型
delimiter $
create procedure pro_cursor()
begin
  declare country_id int(11);
  declare country_name varchar(50);
  declare has_data int default 1;
  # 声明游标
  declare result cursor for select * from country;
  declare exit handler for not found set has_data = 0;
  # 打开游标
  open result;

  repeat
    # fetch游标
    fetch result into country_id,country_name;
    select concat('id=',country_id,'， name=',country_name);
    until has_data = 0
  end repeat;
  # 关闭游标
  close result;
end $

call pro_cursor();

use demo_01;
set global log_bin_trust_function_creators=TRUE;
# 存储函数
delimiter $
create function count_city(countryId int)
returns int
begin
  declare cnum int ;
  select count(*) into cnum from city where country_id = countryId;
  return cnum;
end $

# 函数调用
select count_city(1);

# 触发器 在insert/update/delete操作之前或者只后，执行的SQL语句集合
# create trigger trigger_name
# before/after insert/update/delete
# on table_name
# [for each row] 行级触发器
# begin
#    ...
# end;

# 创建日志表
create table city_logs
(
	id int auto_increment,
	operation varchar(20) not null comment '操作类型，insert/update/delete',
	operate_time datetime not null comment '操作时间',
	operate_id int not null comment '操作表的ID',
	operate_params varchar(500) null comment '操作参数',
	constraint logs_pk
		primary key (id)
)engine=innoDB default charset=utf8;

# 创建触发器，每次对city表进行插入操作后触发，更新city_logs表
delimiter $
create trigger city_insert_trigger
  after insert
  on city
  for each row
  begin
    insert into city_logs(id,operation,operate_time,operate_id,operate_params) values (null,'insert',now(),NEW.city_id,concat('city_id:',NEW.city_id,',city_name:',NEW.city_name,',country_id:',NEW.country_id));
  end $

insert into city(city_id, city_name, country_id) values (null,'深圳',1);

# 查看触发器
show triggers;