CREATE USER 'root'@'%' IDENTIFIED BY 'YOUR_PASSWORD';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

# 创建数据库
create database 数据库名;
# 创建数据库,当指定的数据库不存在时才执行
create database if not exists 数据库名;
# 在创建数据库的同时指定数据库的字符集(数据存储在数据库中采用的编码格式 utf8 gbk)
create database 数据库名 character set utf8;
create database if not exists 数据库名 character set utf8;

# 删除数据库
drop database 数据库名;
# 删除数据库,当指定的数据库存在时才执行
drop database if exists 数据库名;

# 修改数据库的字符集
alter database 数据库名 character set utf8;

# 显示当前mysql数据库中的数据库列表
show databases;
# 查看当前使用的数据库
select database();
# 显示指定名称的数据库创建的SQL指令
show create database 数据库名; 

use 数据库名;

# 创建表
create table 表名(
	字段名1 数据类型1 [约束] [comment 字段描述],
	字段名2 数据类型2 [约束] [comment 字段描述],
	……
	字段名n 数据类型n [约束] [comment 字段描述]
);

# 创建表,当表不存在时才执行
create table if not exists 表名(
	字段名1 数据类型1 [约束] [comment 字段描述],
	字段名2 数据类型2 [约束] [comment 字段描述],
	……
	字段名n 数据类型n [约束] [comment 字段描述]
);

# 删除表
drop table 表名;
# 删除表,当表存在时才执行
drop table if exists 表名;

# 修改表名:
alter table 表名 rename to 新表名;
# 修改表的字符集
alter table 表名 character set utf8;
# 添加列:
alter table 表名 add 列名 数据类型 [约束] [comment 字段描述];
# 删除字段:
alter table 表名 drop 列名;
# 修改列的数据类型:
alter table 表名 modify 列名 新的数据类型;
# 修改列的列名和数据类型:
alter table 表名 change 列名 新列名 新数据类型

# 查询当前数据库下的所有表的名称:
show tables;
# 查询表结构：
desc 表名称;

insert into 表名(列名1,列名2,列名3,……) values(值1,值2,值3,……)

insert into 表名 values(值1,值2,值3,……)

insert into 表名(列名1,列名2,列名3,……) values(值1,值2,值3,……),(值1,值2,值3,……)……;

insert into 表名 values(值1,值2,值3,……),(值1,值2,值3,……),

delete from 表名 [where 条件]

update 表名 set 列名1=值1,列名2=值2,…… [where 条件]

create procedure 存储过程名称([IN/OUT args])
begin
      -- SQL
end;

-- 创建一个存储过程实现加法运算:
create procedure proc_test1(IN a int,IN b int,OUT c int)
begin
   SET c = a+b;
end;

call 存储过程名称(args……)

-- 调用存储过程
-- 定义变量@m
set @m = 0;
-- 调用存储过程，将3传递给a，将2传递给b，将@m传递给c
call proc_test1(3,2,@m);
-- 显示变量值（dual系统表，无需创建，定义变量的值都会在这里）
select @m from dual;

-- 局部变量要定义在存储过程中，而且必须定义在存储过程开始
declare 参数名 参数类型 [default 默认值];

create procedure proc_test2(IN a int,OUT r int)
begin
 declare x int default 0;  -- 定义x  int类型，默认值为0
 declare y int default 1;  -- 定义y
 set x = a*a;
 set y = a/2; 
 set r = x+y;
end;

-- 用户变量会存储在mysql数据库的数据字典中（dual）
-- 用户变量定义使用set关键字直接定义，变量名要以@开头
set @n=1;

set 变量名=值

-- 查询学生数量
-- 注意在储存过程中使用SQL语句需要将结果赋值给变量，那么就需要使用into关键字来进
行赋值
create procedure proc_test3(OUT c int)
begin
   select count(stu_num) INTO c from students; -- 将查询到学生数量赋值给c
end;
-- 调用存储过程
call proc_test3(@n);
select @n from dual;

-- 创建存储过程：添加学生信息
create procedure proc_test4(IN snum char(8),IN sname varchar(20), IN gender char(2), IN age int, IN cid int, IN remark varchar(255))
begin
  insert into students(stu_num,stu_name,stu_gender,stu_age,cid,remark)
    values(snum,sname,gender,age,cid,remark);
end;
call proc_test4('20220108','小丽','女',20,1,'aaa');

-- 创建存储过程，根据学生学号，查询学生姓名
create procedure proc_test5(IN snum char(8),OUT sname varchar(20))
begin
   select stu_name INTO sname from students where stu_num=snum;
end;
set @name='';
call proc_test5('20220107',@name);
select @name from dual;

create procedure proc_test6(INOUT str varchar(20))
begin
   select stu_name INTO str from students where stu_num=str;
end;
set @name='20220108';
call proc_test6(@name);
select @name from dual;

-- 单分支：如果条件成立，则执行SQL
if conditions then
    -- SQL
end if;
-- 如果参数a的值为1，则添加一条班级信息
create procedure proc_test7(IN a int)
begin
     if a=1 then
        insert into classes(class_name,remark) values('Java2209','test');
     end if;
end;

-- 双分支：如果条件成立则执行SQL1，否则执行SQL2
if conditions then
    -- SQL1
else
    -- SQL2
end if;
-- 如果参数a的值为1，则添加一条班级信息；否则添加一条学生信息
create procedure proc_test7(IN a int)
begin
     if a=1 then
        insert into classes(class_name,remark) values('Java2209','test');
     else
        insert into students(stu_num,stu_name,stu_gender,stu_age,cid,remark) values('20220110','小花','女',19,1,'...');
     end if;
end;

-- case
create procedure proc_test8(IN a int)
begin
  case a
    when 1 then  
        -- SQL1   如果a的值为1  则执行SQL1
        insert into classes(class_name,remark) values('Java2210','wahaha');
    when 2 then
        -- SQL2   如果a的值为2  则执行SQL2
        insert into students(stu_num,stu_name,stu_gender,stu_age,cid,remark) values('20220111','小刚','男',21,2,'...');
    else
        -- SQL (如果变量的值和所有when的值都不匹配，则执行else中的这个SQL)
        update students set stu_age=18 where stu_num='20220110';
  end case;
end;

-- while
create procedure proc_test9(IN num int)
begin
  declare i int;
    set i = 0;
    while i<num do
            -- SQL
            insert into classes(class_name,remark) values( CONCAT('Java',i) 
,'....');
            set i = i+1;
    end while;
end;
call proc_test9(4);

-- repeat
create procedure proc_test10(IN num int)
begin
    declare i int;
    set i = 1;
    repeat
        -- SQL
        insert into classes(class_name,remark) values( CONCAT('Python',i) 
,'....');
        set i = i+1;
    until i > num end repeat;
end;
call proc_test10(4);

-- loop
create procedure proc_test11(IN num int)
begin
   declare i int ;
     set i =0;
     myloop:loop
            -- SQL
            insert into classes(class_name,remark) values( CONCAT('HTML',i) 
,'....');
            set i = i+1;
            # 结束循环的条件
            if i=num then
               # 离开循环
               leave myloop;
            end if;
     end loop;
end;
call proc_test11(5);

-- 查询所有存储过程
show procedure;
-- 根据数据库名，查询当前数据库中的存储过程
show procedure status where db='db_test2';
-- 查询存储过程的创建细节
show create procedure db_test2.proc_test1;

alter procedure 存储过程名称 特征1 [特征2 特征3 ....]

-- 删除存储过程
-- drop 删除数据库中的对象 数据库、数据表、列、存储过程、视图、触发器、索引....
-- delete 删除数据表中的数据
drop procedure 存储过程名称;

-- 声明游标语法
DECLARE 游标名称 CURSOR FOR select_statement;

-- 示例
declare mycursor cursor for select class_id,class_name from classes;

-- 语法
open 游标名称;

-- 示例
open mycursor;

-- 语法
-- 使用游标：提取游标当前指向的记录（提取之后，游标自动下移）
fetch 游标名称 into xxx,yyy;

-- 示例
fetch mycursor into cid,cname;

-- 语法
CLOSE 游标名称; 

-- 示例
CLOSE mycursor; 

-- MySQL中， Concat_WS()  函数 用来通过指定符号，将2个或多个字段拼接在一起，返回拼接后的字符串。
create procedure proc_test12(out result varchar(200))
begin
    # 游标变量
    declare cid int;
    # 游标变量
    declare cname varchar(20);
    # 计数变量
    declare num int;
    # 计数变量
    declare i int;
    # 每条数据
    declare str varchar(100);
    # 查询语句执行之后返回的是一个结果集（多条记录），使用游标遍历查询结果集
    declare mycursor cursor for select class_id,class_name from classes;
    # 记录总数据量
    select count(*) into num from classes;
    # 打开游标
    open mycursor;
    set i = 0;
    # 开始遍历游标
    while i<num do
        # 提取游标中的数据，并将结果赋值给游标变量
        fetch mycursor into cid,cname;
        set i = i+1;
        # set str=concat_ws('~',cid,cname); 不同的写法
        select concat_ws('~',cid,cname) into str;
        set result = concat_ws(',',result,str);
    end while;
    close mycursor;
end;
# 案例测试
set @r = '';
call proc_test12(@r);
select @r from dual;

-- 语法
create trigger 触发器名称
<before|after>                     -- 定义触发时机
<insert|delete|update>             -- 定义DML类型
ON 表名
for each row                       -- 声明为行级触发器（只要操作一条记录就触发触发器执行一次）
sql_statement                      -- 触发器操作


-- 示例
create trigger tri_test1
after insert on students
for each row
insert into stulogs(time,log_text) values(now(), concat('添加',NEW.stu_num,'学生信息'));

show triggers;

drop trigger 触发器名称;

create trigger tri_test1
after insert on students
for each row
insert into stulogs(time,log_text) values(now(), concat('添加',NEW.stu_num,'学生信息'));

-- 创建触发器 : 在监听update操作的触发器中，可以使用NEW获取修改后的数据
create trigger tri_test2
after update on students for each row
insert into stulogs(time,log_text) values(now(), concat('修改学生信息为：',NEW.stu_num,NEW.stu_name));

create trigger tri_test3
after delete on students for each row
insert into stulogs(time,log_text) values(now(), concat('删除',OLD.stu_num,'学生信息'));

create trigger tri_test2
after update on students for each row
insert into stulogs(time,log_text) values(now(), concat('将学生姓名从【',OLD.stu_name,'】修改为【',NEW.stu_name,'】'));

create view 视图名称
AS
select_statement

-- 查询视图结构
desc 视图名称;

-- 方式1
create OR REPLACE view 视图名称
AS
select_statement


-- 方式2
alter view 视图名称
AS
select_statement

drop view 视图名称;

-- 创建唯一索引: 创建唯一索引的列的值不能重复
create unique index 索引名 on 表名(列名);

-- 创建普通索引: 不要求创建索引的列的值的唯一性
create index 索引名 on 表名(列名);

-- 创建组合索引
create index 索引名 on 表名(列名1,列名2...);


-- 全文索引(了解即可),MySQL 5.6 版本新增的索引，可以通过此索引进行全文检索操作，因为MySQL全文检索不支持中文，因此这个全文索引不被开发者关注，在应用开发中通常是通过搜索引擎（数据库中间件）实现全文检索
create fulltext index 索引名 on 表名(字段名);

-- 查询索引
show keys from 表名;

-- 查询数据表的索引
show indexes from 表名;

-- 通过创建表的语句查看索引
show create table 表名;

-- 删除索引: 索引是建立在表的字段上的,不同的表中可能会出现相同名称的索引,因此删除索引时需要指定表名
drop index 索引名 on 表名;

explain select_statement