建表语句

set names utf8mb4;
use f_algo_platform;
CREATE TABLE `result_table` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键id',
    `prec`  VARCHAR(100) NOT NULL DEFAULT '' COMMENT '准确率',
    `acc` DOUBLE NOT NULL DEFAULT 0 COMMENT '精确率'，
    `model_path` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '模型存储地址',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_task_id` (`task_id`)
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '算法结果表';

DQL查询语句

show databases ;
show tables ;

use myemployees;
select * from employees;
select * from departments;
select * from locations;
select * from jobs;
show columns from employees;
desc employees;

#进阶1：基础查询
/*
语法:select 查询列表 from 表名;
特点:
1.可以查询表中的字段，常量值，表达式，函数
2.查询的结果是一个虚拟表格
*/
#1.查询表中单个字段
select last_name from employees;
#2.查询表中的多个字段
select last_name,email from employees;
select
    employee_id,
    first_name,
    last_name,
    phone_number
from employees;
#使用着重号
select
    `employee_id`,
    `first_name`,
    `last_name`,
    `phone_number`
from employees;
#3.查询表中所有的字段
select * from employees;
#4.查询常量值
select 100 from employees;
select 'john';
#5.查询表达式
select 100%98;
#6.查询函数
select version();
#7.为字段取别名
#方法一
select 100%98 as 结果;
select last_name as 姓 from employees;
#方法二
select last_name 姓 from employees;
#案例 查询salary显示结果为output
select salary as 'out put' from employees;
#8.去重
select department_id from employees;
select distinct department_id from employees;
#9.+号的作用-当作运算符
#案例：查询员工的姓名链接成一个字段，合并显示为姓名
select last_name+first_name '姓名'from employees;  #没有结果
select 100+90;
select '100'+88;  #会将字符串转换成为数值型，转换成功就继续做加法运算，
select '哈哈'+98;  #如果转换失败就将字符转换为0,
select null+98;   #如果有一个为null。则结果为null
#10.字符串的拼接
select concat(last_name,first_name) '姓名' from employees;
#11.ifnull函数
select ifnull(commission_pct,0) '奖金率' from employees;


# 进阶二 条件查询
/*
语法：
select
    查询条件
from
    表名
where
    筛选条件;

分类:
一:按条件表达式筛选
条件运算符： <,>,=,!=(<>)又小又大，<=,>=
二，按逻辑表达式筛选
逻辑表达式：and,or,not
三，模糊查询
like，between and,in,is null
*/
#1.按条件表达式进行筛选
select * from employees where salary>12000;
select last_name,department_id from employees where department_id!=90;
select last_name,department_id from employees where department_id<>90;
#2.按逻辑表达式筛选，将多条件链接在一起
select last_name,salary,commission_pct from employees where salary>10000 and salary<20000;
select * from employees where department_id<90 or department_id>110 or salary>15000;
select * from employees where not(department_id>=90 and department_id<=110) or salary>15000;
#3.like模糊查询
/*
一般和通配符一起搭配使用
通配符：
% 任意个字符，包含0个字符
_ 任意单个字符
*/
select * from employees where last_name like '%a%';
select last_name,salary from employees where last_name like '__n_l%';
select first_name,last_name,salary from employees where last_name like '_\_%';  #使用转义字符
select first_name,last_name,salary from employees where last_name like '_a_%' escape 'a'; #将a指明为转义字符
#4.between and
/*
可以提高语句的简洁度，
between and 是闭区间
between与and之间值不能颠倒顺序
*/
select first_name,last_name,department_id from employees where department_id between 0 and 100;
#5.in
/*
用于判读值是否为in中的值
特点：
使用in提高语句的简洁度
in类表的值类型必须一致或兼容
不支持通配符
*/
select * from employees where job_id in ('IT_PROG','AD_VP','AD_PRES');
#4.is null
/*
=或<,>不能够判断null
*/
select first_name,last_name,commission_pct from employees where commission_pct is null ;
select first_name,last_name,commission_pct from employees where commission_pct is not null ;
#5.安全等于
/*
<=> 可以用来判断null，也可以用来判断一般类型的数值
is null与<=>的区别
is null 只能判断null
<=> 可以判断null与一般值
= 只能判断一般值
*/
select first_name,last_name,commission_pct from employees where commission_pct <=> null ;
select first_name,last_name,commission_pct from employees where commission_pct <=> 0.2 ;


# 进阶三,排序查询
/*
select * from 表名 where 查询条件 order by 排序列表(asc|desc)
特点：asc升序，desc降序
order by 子句中可以支持单个字段，多个字段，表达式，函数，别名
order by 字句放在查询语句的最后面，只有limit放在order by后面
*/
select * from employees order by salary;
select * from employees order by salary desc;
select * from employees order by salary asc;

select * from employees where department_id >= 90 order by hiredate asc;

select salary*12*(1+ifnull(commission_pct,0)) 年薪 from employees order by 年薪 desc;

select last_name,salary from employees order by length(last_name) desc,salary;

select last_name,salary,employee_id from employees order by salary,employee_id desc;

select last_name,department_id,salary*12*(1+(ifnull(commission_pct,0))) 年薪 from employees order by 年薪 desc,last_name;
select last_name,salary from employees where not (salary between 8000 and 17000) order by salary desc ;
select * from employees where email like '%e%' order by length(email) desc,department_id asc;

#进阶四 常见函数
/*
功能：类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：
1.掩藏实现细节，
2.提高代码的重用性
调用：
select 函数名(实参列表) from 表名;
特点:
函数名
函数功能
分类：
单行函数：处理功能(字符函数)
concat，length，ifnull
分组函数：统计使用，(统计函数，聚合函数)
*/

#字符函数
#1.length()获取参数值的字节数
select length('join');
select length('张三丰');
#2.concat()拼接字符串
select concat(last_name,'-',first_name) 姓名 from employees;
#3.upper,lower
select upper(last_name) from employees;
select lower(first_name) from employees;
select concat(upper(last_name),'_',lower(first_name)) from employees;
#4.substr,substring截取字符串
select substr('去哪儿网度假部门',5,2);
select substr(last_name,1,2) ,last_name from employees;
select concat(upper(substr(last_name,1,1)),lower(substr(last_name,2))) from employees;
#5.instr返回字串在字符串中的索引
select instr('去哪儿网','网') as output;
select instr('去哪儿网','xie') as output;
#6.trim 去除空格，只取出首尾的空格
select trim(' aa  bb    ') as output;
select trim('a' from 'aaaaaaaaaaaaaaa你好aaaaa') as output;
#7.lpad ，rpad 用指定的字符填充字符串至指定的长度
select lpad('因叔叔',10,'*') as output;
# replace替换
select replace('张无忌爱上了周至若','周至若','赵敏') as output;

#数学函数
#1.round 四舍五入,取整数
select round(1.45);
select round(1.5678,2); #小数点后取两位
#2.ceil 向上取整
select ceil(1.002);
#3,floor向下取整
select floor(1.999);
#truncate 截断
select truncate(1.9999,1);
#mod取余
select mod(10,3);
select mod(10,-3);

#日期函数
#1.now()返回当前的系统时间
select now();
#2.curdate()返回当前系统的日期，不包括时间
select curdate();
#3,curtime()返回当前系统的时间，不包括日期
select curtime();
#4,获取指定的时，分，秒
select YEAR(2020-11-11);
#5,str_to_date
select str_to_date('9-13-1999','%m-%d-%Y');
select date_format('1999-6-12','%Y-%m-%d');

# 其他函数
select version();
select database();
select user();

#流程控制函数
#1.if函数  s实现if else的效果
select if(10>5,'大','小');
select last_name,if(commission_pct is null,'you','wu') from employees;
#2.case函数的使用
#使用一
/*
switch (常量或表达式){
    case 常量1:语句1;break;
    case 常量2:语句2;break;
}

mysql中的case
case 要判断的字段
when 常量1 then 要显示的语句;
when 常量2 then 要显示的语句;
....
else 要显示的语句
end
*/
select salary 原始工资,department_id,
       case department_id
           when 50 then salary*2
           when 60 then salary*1.2
           when 70 then salary*1.3
           else salary
           end 新工资
from employees;
#使用二
/*
case
when 条件1 then 值1
when 条件2 then 值2
when 条件3 then 值3
else 要显示的语句
end
*/
select salary,
       case
           when salary > 10000 then 'A'
           end
from employees;

select now();
select employee_id,last_name,salary,salary*1.2 from employees;
select length(last_name),substr(last_name,1,1) 首字符,last_name from employees order by 首字符;
select concat(last_name,'earns',salary,'monthly but wants',salary*3 ) from employees;
select job_id,
       case job_id
           when 'AD_PRES' then 'A'
           when 'AD_VP' then 'B'
           when 'IT_PROG' then 'C'
           end
from employees;

#分组函数
/*
主要用于统计，又称为聚合函数或统计函数
分类
sum求和
avg平均值
max 最大值
min 最小值
count 计算个数
*/
#1,简单使用
select sum(salary) 工资总和 from employees;
select round(avg(salary)) 平均值,min(salary) 最小值,max(salary) 最大值 ,count(salary) 总数 from employees;
#2,参数类型支持
/*
sum数值型，null不参与运算
avg数值型，null不参与运算
max数值型，字符型，null不参与运算
min数值型，字符型，null不参与运算
count 非null，null不计数
*/
select sum(last_name),avg(last_name) from employees;
select max(hiredate),min(hiredate)from employees;
#3,可以和distinct搭配使用，去重
select sum(distinct(salary)) from employees;
select count(distinct salary) from employees;
#4,count函数的详细介绍
/*
innodb count(*)与count(1)效率差不多，比count(字段)要高一些
myisam count(*)效率最高，直接返回计数器
一般使用count(*)统计行数
*/
select count(salary) from employees;
select count(*) from employees;
select count(1) from employees; #相当于在表中加了一列常量
#5,和分组函数一同查询的字段有限制，同分组查询的字段一般要求是group by之后的字段
select avg(salary),employee_id from employees;  #错误
select datediff(now(),'1995-09-15');
select count(*) from employees where department_id=90;
#进阶五，分组查询
/*
语法
select 分组函数，列(要求出现在group by的后面)
from 表
where 筛选条件
group by 分组列表
order by 字句
注意：查询列表比较特殊，要求是分组函数与group by后出现的字段
特点：
1,分组查询中的筛选条件分为两类
分组前筛选：原始表
分组后筛选：分组后的结果集

分组函数做条件，放在having函数中
*/
select department_id,avg(salary) from employees group by department_id;
select max(salary),job_id from employees group by job_id;
select count(*),location_id from departments group by location_id;
#添加筛选条件,分组前的筛选
select avg(salary) ,department_id from employees where email like '%a%' group by department_id;
select max(salary),manager_id from employees where commission_pct is not null group by manager_id;
#添加复杂的筛选条件
#select department_id,count(*) '员工数' from where '员工数'>2 employees group by department_id;
#根据已查询的结果进行再次查询分组
/*
利用having在查询的结果后面筛选
*/
select count(*) ,department_id from employees group by department_id having count(*)>2;
select job_id,max(salary) from employees where commission_pct is not null group by job_id having max(salary)>200;
select min(salary) 最低工资,manager_id from employees where manager_id>102 group by manager_id having 最低工资>5000 order by manager_id;
#gruopby 按表达式进行分组
select count(*),length(last_name) from employees group by length(last_name) having count(*) >5 order by length(last_name);
#按多个字段进行分组
select department_id,job_id from employees  group by department_id, job_id order by department_id  ;
select department_id,job_id,avg(salary) from employees group by department_id,job_id;

select max(salary)-min(salary) difference from employees;
select min(salary),manager_id from employees where manager_id is not null group by manager_id  having min(salary)>6000;
select department_id,count(*),avg(salary)from employees group by department_id order by avg(salary);



show databases ;
use girls;
show tables;
select * from beauty;
select * from boys;
#进阶六 链接查询
/*
多表链接，当查询的字段涉及到多个表
笛卡尔乘积现象：表一有m行，表二有n行，结果一共有mXn项
如何有效的避免：添加有效的链接条件
分类：
按年代分类
sql92标准
sql99标准
按功能进行分类
内链接
--等值链接
--非等值链接
--自链接
外链接
--左外链接
--右外链接
--全外链接
交叉链接


*/
#发生笛卡尔乘积现象
select name,boyName from beauty,boys;
select name ,boyName from beauty,boys where beauty.boyfriend_id=boys.id;
#1,等值链接
select boyName ,name from boys,beauty where beauty.boyfriend_id=boys.id;
use myemployees;
select * from departments;
#查询员工名和对应的部门名
select last_name ,department_name from employees e,departments d where e.department_id=d.department_id;
#查询员工号，工种号，工种名
#注意，其别名之后不能够再使用表明
select * from jobs;
select e.last_name,e.job_id,j.job_title from employees e,jobs j where e.job_id=j.job_id;
#可以加筛选
select last_name,department_name,commission_pct from employees e,departments d where e.department_id=d.department_id and e.commission_pct is not null;
#查询城市名中第二个字符为o的部门名和城市名
select * from departments;
select d.department_name,l.city from departments d,locations l where d.location_id=l.location_id and l.city like '_o%';
#加分组
#查询每一个城市的部门个数
select count(*) 部门数,city from departments d,locations l where d.location_id=l.location_id group by l.city;
#查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
select
    d.department_name,min(salary)
from departments d,employees e
where
        d.department_id=e.department_id and e.commission_pct is not null
group by d.department_name;
#加排序
select job_title,count(*) from jobs j,employees e where j.job_id=e.job_id group by job_title order by count(*) desc ;
#三表链接
select last_name,department_name,city from employees e,departments d,locations l where e.department_id=d.department_id and d.location_id=l.location_id;


#2,非等值链接
select last_name,salary,grade_level from employees e,job_grades j where e.salary>j.lowest_sal and e.salary <j.highest_sal;
#自链接,自己链接自己
select e1.last_name,e2.last_name from employees e1,employees e2 where e1.manager_id=e2.employee_id;

#3,sql99语法
/*
内链接  inner
外链接
--左外  left[outer]
--右外  right[outer]
--全外  full[outer]
交叉链接 cross

语法
select 查询列表
from 表1 别名 链接类型
join 表2 别名 on 链接条件
where 筛选条件
*/

#1.内链接
/*
select 查询列表
from 表1 别名 inner
join 表2 别名
*/
select last_name,department_name from employees e inner join departments d on e.department_id=d.department_id;
select last_name,job_title from employees e inner join jobs j on e.job_id = j.job_id where last_name like '%e%';
select city,count(*) from departments d inner join locations l on d.location_id = l.location_id group by d.location_id having count(*)>3;
select department_name,count(*) 员工数 from departments d inner join employees e on d.department_id = e.department_id group by e.department_id having count(*)>3 order by count(*)desc;
select last_name,department_name,job_title from employees e inner join departments d on e.department_id = d.department_id inner join jobs j on e.job_id = j.job_id order by  department_name desc;
#非等值链接
select last_name,salary,grade_level from employees e inner join job_grades j on e.salary>j.lowest_sal and e.salary<j.highest_sal;
select last_name,salary,grade_level from employees e inner join job_grades j on e.salary between j.lowest_sal and j.highest_sal;
select e.last_name,e.employee_id,m.last_name,m.employee_id from employees e inner join employees m on e.manager_id=m.employee_id;

#2.外链接
use girls;
select * from beauty;
select be.*,bo.* from beauty be left outer join boys bo on be.boyfriend_id=bo.id;
/*
left join 左边的是主表
right join 右边是主表
*/
use myemployees;
select d.*,e.* from departments d left join employees e on d.department_id = e.department_id where employee_id is null ;

#全外链接
use girls;
#交叉链接=笛卡尔乘积
select b.*,bo.* from boys b cross join beauty bo;

select be.*,b.*from beauty be left join boys b on be.boyfriend_id=b.id where be.id>3;
use myemployees;


select city,d.* from departments d right join locations l on d.location_id = l.location_id where d.department_id is null;

select last_name,department_name from departments d left join employees e on d.department_id = e.department_id where d.department_name='SAL' or d.department_name='IT' and last_name != null;

#进阶七 子查询
/*
含义：出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
--按子查询出现的位置分类
-----select后面，标量子查询
-----from后面，表子查询
-----where或having后面，标量子查询(单行)，列子查询(多行)，行子查询，
-----exist后面，表子查询
--按照功能不同，结果集的行列数不同
-----标量子查询(结果集只有一行一列)
-----列子查询(结果集只有一列多行)
-----行子查询(结果集只有一行多列)
-----表子查询(结果集有多行多列)
*/
#一，where,having后面
/*
特点：子查询都会放在小括号内
子查询一般放在条件右侧
标量子查询，一般搭配着单行操作符（>,<,+,>=,<=,<>）使用
列子查询，一般搭配着多行操作符使用，in/any/some/all
子查询优先与主查询，主查询用到了子查询的结果
*/

#1.标量子查询
select
    last_name,salary
from
    employees
where
        salary>=(select salary from employees where last_name='Abel')
order by
    last_name;

select
    last_name,job_id,salary
from employees
where job_id=(
    select job_id
    from employees
    where employee_id=141)
  and salary> (
    select salary
    from employees
    where employee_id=143
);

select last_name,job_id,salary
from employees
where salary = (
    select min(salary)
    from employees
);

select department_id,min(salary)
from employees
group by department_id
having min(salary)>(
    select min(salary)
    from employees
    where department_id=50
);

#2.列子查询(多行子查询)
/*
返回多行
使用多行比较符
IN/NOT IN 等于列表中的任意一个
ANY/SOME 和子查询返回的某一个值比较
ALL 和子查询返回的所有值比较
*/

select last_name,department_id from employees where department_id in (select distinct department_id from departments where location_id in (1400,1700));

select last_name,employee_id,job_id,salary from employees where salary <any(select distinct salary from employees where job_id='IT_PROG') and job_id!='IT_PROG';


#3.行子查询
select * from employees where (employee_id,salary)=(select min(employee_id),max(salary) from employees);

#二，放在select后面
select d.*,(select count(*) from employees e where e.department_id=d.department_id) 个数 from departments d;

select (select department_name from departments d where d.department_id=e.department_id)from employees e where employee_id=102;

#三，放在from后面
select j.grade_level,avgsalary.* from (select avg(salary) 平均工资,department_id from employees group by department_id) avgsalary,job_grades j where avgsalary.平均工资>j.lowest_sal and avgsalary.平均工资<j.highest_sal;

#四，放在exist后面
#，exist的使用,判断查询是否有值
select exists(select employee_id from employees);
select department_name from departments d where exists(select * from employees e where e.department_id=d.department_id);

# 进阶八，分页查询
/*
应用场景：当要显示的数据，一页现实不全，需要分页提交sql请求
语法：select 查询列表
     from  表
     join type join 表2
     on
     where
     group by
     having
     order by
     limit offset，size;
offset:要显示的条目的起始索引
size：要显示的条目个数

特点：用在查询语句的最后
公式：要显示的页数page，每条的条目数size
page :（page-1）* size
size : size


*/
select * from employees limit 0,5;

select * from employees limit 10,15;

select * from employees where commission_pct is not null order by salary desc limit 10;

#进阶九，联合查询union联合查询
/*
union联合，将多条语句的查询语句合并成一个结果
语法：查询语句1 union 查询语句2;
使用场景：
注意事项：多表中的结果列数要相同
        查询的列类型要对应一致
        会将结果自动的去重
关闭去重 union all

*/
#查询部门编号>90或者邮箱包含a的员工的信息
select * from employees where email like '%a%'
union
select * from employees where department_id>90;

DML语言

/*
数据的操作语言
插入：
修改：
删除：
*/

#一，插入语句
/*
语法：
insert into 表名 (列名,...) values (值);
特点：插入的值的类型要与列的类型一致或兼容
*/

#1.插入beauty
use girls;
insert into beauty (id,name,sex,borndate,phone,photo,boyfriend_id) values (13,'唐艺昕','女','1990-4-23','18988888888',null,2);
select * from beauty;
delete from beauty where id=16;

#2.可以为null的列是如何插入值的，不可以为null的列必须有值
#方式二，可以为空的列，可以不用写
insert into beauty (id,name,sex,borndate,phone,boyfriend_id) values (14,'金星','女','1990-4-23','13822222222',9);

#3.列的顺序是否可以颠倒
insert into beauty (name,sex,id,phone) values ('将昕','女',16,'1345678903');

#4.列数与值的个数必须一致

#5.可以省略列名，默认所有列，而且列的顺序与表中列的顺序一致
insert into beauty values (18,'张飞','男',null,'12345678901',null,null);

#方式二
/*
insert into 表名 set 列名=值，列名=值,...
*/
insert into beauty set id=19,name='刘涛',phone='34567654839';

/*
两种方式的区别
1.方式一，支持一次插入多行,方式二不支持
2.方式一支持子查询，方式二不支持
*/
insert into beauty (id,name,sex,borndate,phone,photo,boyfriend_id)
values (20,'唐艺昕','女','1990-4-23','18988888888',null,2),
       (21,'唐艺昕','女','1990-4-23','18988888888',null,2),
       (22,'唐艺昕','女','1990-4-23','18988888888',null,2),
       (23,'唐艺昕','女','1990-4-23','18988888888',null,2);
insert into beauty (id,name,phone) select 26,'宋茜','12345678901';


#二。修改语句
/*
语法：
1.修改单表中的记录
2.修改多表中的记录
语法
update 表明 set 列=新值，列=新值 where 筛选条件
*/
#1.修改单表的记录
update beauty set phone='13899888899' where name like '唐%';
select * from beauty;
#2.修改boys
update boys set boyName='张飞',userCP=20 where id=2;

#2.修改多表的记录
/*
sql92语法：
update 表1,别名，表2 别名
set 列=值
where 链接条件
and 筛选条件


sql99
update 表1 别名
inner、left、right join 表1 别名
on 条件
set 列=值
where 筛选条件
*/

#2.修改多表的记录
update boys b
    inner join beauty be
    on b.id=be.boyfriend_id
set be.phone=11111111111
where b.boyName='张无忌';
select * from beauty;

update beauty be left join boys b on be.boyfriend_id=b.id set be.boyfriend_id=2 where b.id is null;

#删除语句
/*
语法：
方式一
delete from 表名 where 筛选条件
1,实现单表的删除
2,实现多表的删除
方式二
truancate
truncate table 表名
*/
#方式一
#单表的删除
delete from beauty where phone like '%9';
select * from beauty;
#多表的删除
delete  be from beauty be inner join  boys b on be.boyfriend_id=b.id where b.boyName='张无忌';

delete be,b from beauty be inner join boys b on be.boyfriend_id=b.id where be.name='Angelababy';
#方式二 truncate清空数据
truncate table boys;
#delete 与truncate的区别
/*
delete 可以加where条件，truncate不能加
truncate删除，效率更高
如果删除表中的数据有自增长列，如果使用delete删除，新插入的数据，从断点处进行增长，如果使用truncate则从1开始
truncate删除没有返回值，delete删除有返回值
truncate删除不能回滚，delete删除可以回滚
*/
use myemployees;

CREATE TABLE my_employees(
                             Id INT(10),
                             First_name VARCHAR(10),
                             Last_name VARCHAR(10),
                             Userid VARCHAR(10),
                             Salary DOUBLE(10,2)
);
CREATE TABLE users(
                      id INT,
                      userid VARCHAR(10),
                      department_id INT

);
#2. 显示表my_employees的结构
DESC my_employees;

#方式一：
INSERT INTO my_employees
VALUES(1,'patel','Ralph','Rpatel',895),
      (2,'Dancs','Betty','Bdancs',860),
      (3,'Biri','Ben','Bbiri',1100),
      (4,'Newman','Chad','Cnewman',750),
      (5,'Ropeburn','Audrey','Aropebur',1550);

INSERT INTO users
VALUES(4,'Rpatel',10),
      (5,'Bdancs',10);

update my_employees set Last_name='drelxer' where id=3;
update my_employees set Salary =1000 where Salary<900;
delete my,u from my_employees my inner join  users u on my.Userid = u.userid where u.userid='Bbiri';
select * from my_employees;

DDL语言

/*
库的管理
创建
修改
删除
表的管理
创建 create
修改 alter
删除 drop
*/

#一，库的管理
/*
1.库的创建
语法
create database 库名;
*/
#if not exists
create database if not exists books;
show databases ;

#库的修改
/*
修改库名，可能有数据的丢失
可以修改库的字符集
alter database books charachter set gbk;
*/
alter database books character set gbk;

#库的删除
drop database if exists books;

#表的管理
/*
表的创建

*/
#1.表的创建
/*
语法
create table 表名(
列名 列的类型(长度) 约束，
列名 列的类型(长度) 约束，
列名 列的类型(长度) 约束，
...
列名 列的类型(长度) 约束，
)
*/
create database if not exists books;
create table book(
                     id int, #编号
                     bName varchar(20), #s书名
                     price double,
                     authorId int,
                     publishDate datetime #出版日期
);

desc book;
create table author(
                       id int,
                       authorName varchar(20),
                       nation varchar(10)
);
desc author;

#2.表的修改
/*
修改列名
修改列的类型与约束
添加列
删除列
修改列名

alter table 表名 drop|add|modify|change column 列名 类型 约束;
*/
#修改列名
alter table book change column  publishDate pubDate DATETIME;
desc book;
#修改列的类型
alter table book modify column pubDate timestamp;
desc book;
#添加新列
alter table author add column annual double;
#删除列
alter table author drop column annual;
#修改表名
alter table author rename to bookAuthor;
alter table bookAuthor rename to author;

#3表的删除
drop table if exists book;
show tables ;

#通用的写法
drop database if exists 旧库名;
drop table if exists 旧表名;

#表的复制
insert into author
(id,authorName,nation)
values
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'风谈','中国'),
(4,'金庸','中国');
#1.仅仅复制表的结构
create table cpoyAuthor like author;
select * from copyAuthor2;
#2,复制表的结构家数据
create table copyAuthor2 select * from author;
#3.只复制部分数据
create table capyAuthor3 select id,authorName from author where nation='中国';
#4.仅仅复制某些字段
create table copyAuthor4 select id,authorName from author where 1=2;

create database if not exists test;
use test;
create table dept1(
                      id int(7),
                      name varchar(20)
);
#2,将表departments中的数据插入的dept1中
create table dept2 select department_id,department_name from myemployees.departments;

create table emp5(
                     id int(7),
                     first_name varchar(20),
                     last_name varchar(20),
                     dep_id int(7)
);

alter table emp5 modify column last_name varchar(50);

create table employees2 like myemployees.my_employees;

drop table if exists emp5;

alter table employees2 rename to emp5;

alter table emp5 add column test_column varchar(20);

alter table test.emp5 drop column  test_column;

select * from emp5;

/*
数据表的类型
数值型：
--整型
Tinyint 1字节  -128～127 0～255 所保存的范围不同
Smallint 2字节  -32768～32767 0～65503
mediumint 3字节
int ，integer 4字节
bigint 8字节
--浮点型
-------定点数
-------浮点数
--字符型
-------较短的文本 char varchar
-------较长的文本 text blob
--日期
*/
#1。整型，如何设置无符号与有符号 unsigned
/*
特点;整型默认为有符号，使用无符号需要加上unsigned
超出范围，不会插入
如果不设置长度，或有默认的长度,默认长度只影响显示效果
如果要使用0填充，需要使用zerofill
*/
create table tabInt(
                       t1 int(9) zerofill,
                       t2 int(9) unsigned
);
select * from tabInt ;
insert into tabInt (t1) values (12);
insert into tabInt (t1) values (-12);
insert into tabInt (t2) values (-1);
insert into tabInt (t2) values (1);
drop table if exists tabInt;
insert into tabInt values (3563333333,333333567);

#3.小数
/*
浮点型
float(m,d) 4字节
double(m,d) 8字节
定点型 字节
decimal(m,d)
dec(m,d)
特点：
m，d可以省略，decimal默认(10,0)
m,总的位数 d，小数点后的位数
整数部分的长度=m-d
m，超过总的位数会报错，d超过小数会截取

定点型与浮点型的精度不同，如果需要的精度较高，使用定点型
float的占用空间较小，优先于double
越简单越好，越小越好
*/

create table tab_float(
                          f1 float(5,2),
                          f2 double(5,2),
                          f3 decimal(5,2)
);

select * from tab_float;
insert into tab_float values(123.45,123.56,123.67);
insert into tab_float values(1234.456,123.56,123.67);
insert into tab_float values(123.456,123.567,123.678);
insert into tab_float values(123.4,123.56,123.67);

#三，字符型
/*
较短的文本char，varchar
每个字符占3个字节
较长的文本text blob(较大的二进制)
cahr(M)
varchar(M)
m保存最多的字符数
char与varchar的区别，
char固定长度的字符，varchar不固定的长度
char默认为1,varchar中的m不可以省略
*/

/*
enum型
*/
create table tab_char(
    c1 enum('a','b','c','d')
);
/*
大写会自动转小写
*/
select * from tab_char;
insert into tab_char  (c1) values ('a');
insert into tab_char  (c1) values ('e');
insert into tab_char  (c1) values ('A');

/*
set 可以选择多个插入
超出范围会报错
*/
create table tab_set(
    c1 set('a','b','c')
);

insert into tab_set (c1) values ('a');
insert into tab_set (c1) values ('a,b');
insert into tab_set (c1) values ('a,e');

/*
日期类型        最小值
date   4字节    1000-01-01
datetime 8字节  1000-01-01 00：00：00
timestamp 4字节
time 3字节
year 1字节      1901-2155

datetime与timestamp的区别
datatime不受时区影响
timestamp 受时区影响
*/

create table tab_date(
                         t1 datetime,
                         t2 timestamp
);

insert into tab_date values (now(),now());
insert into tab_date values ('2020-02-04','2020-02-04');

select * from tab_date;

show variables like 'time_zone';
set time_zone ='+9:00'


#约束
/*
常见约束
含义：一种限制，用于限制表中的数据，为了保证表中数据的准确性和可靠性
六大约束：
-- not null非空约束，用于保证该字段必须填写，如姓名，学号
-- default 默认约束，用于保存该字段有默认值，
-- primary key 主键约束，用于保证该字段值具有唯一性，并且非空
-- unique 唯一约束，用于保证该字段的值具有唯一性，可以为空
-- check 检查约束，mysql中不支持
-- foreign key 外间约束吗，用于保证该字段值，必须来源与主表的关联列的值
       在从表中添加外健约束，用于引用主表中某列的值

添加约束在创建表时进行添加
修改表时，数据还没插入，可以进行修改
约束的添加分类
列级约束
表级约束
create table 表名(
列名 类型 列级约束(六大约束语法上都支持，但外健约束没有效果 not null,default)，
表级约束(除了非空，默认，其他都支持)
);
*/
#1,创建表添加越约束
#1.添加列级约束
create database if not exists students;
use students;
drop table if exists stuinfo;

create table stuinfo(
                        id int primary key, #主键
                        stuNmae varchar(20) not null,
                        gender char(1),
                        searnum int unique ,
                        age int default 18
);
desc stuinfo;
select * from stuinfo;
#查看表中的索引，主键，外健，唯一约束会创建索引
show index from stuinfo;

drop table if exists major;
create table major(
                      id int primary key ,
                      majorNmae varchar(20)
)default character set utf8;

#添加表级约束
/*
语法：在各个字段的最下面，
constraint 约束名 约束类型 (字段名)
可以不使用constraint 约束名这个部分

何时使用
通用的写法：

*/
drop table if exists stuinfo;
create table stuinfo(
                        id int,
                        stuName varchar(20),
                        gender char(1),
                        seat int,
                        age int,
                        majorid int,
                        constraint pk primary key(id),#主键
                        constraint uq unique (seat), #唯一键
                        constraint fk_stuinfo_major foreign key(majorid) references major(id)
);
show index from stuinfo;
create table stuinfo(
                        id int,
                        stuName varchar(20),
                        gender char(1),
                        seat int,
                        age int,
                        majorid int,
                        primary key(id),#主键
                        unique (seat), #唯一键
                        foreign key(majorid) references major(id)
);

create table if not exists stuinfo(
                                      id int primary key ,
                                      stuName varchar(20) not null ,
                                      gender char(1),
                                      seat int unique ,
                                      age int default 18,
                                      majorid int,
                                      constraint fk_stuinfo_major foreign key(majorid) references major(id)
) default character set utf8;

/*
主键和唯一的对比
            保证唯一性   是否可以为空    一个表中可以有多少个       是否允许组合
primary key     是         否              一个或者没有            是
unique          是         是              可以有多个或者没有       否
*/

select * from stuinfo;
insert into stuinfo (id,stuName,seat) values (1,'tom',null),
                                             (2,'john',null);
/*
联合主键，多列组成一个主键
只要有一个相同就不同
*/
create table if not exists stuinfo(
                                      id int,
                                      stuName varchar(20) not null unique default '张三',
                                      gender char(1),
                                      seat int unique ,
                                      age int default 18,
                                      majorid int,
                                      constraint pk primary key (id,stuName),
                                      constraint fk_stuinfo_major foreign key(majorid) references major(id)
);

insert into stuinfo (id,stuName,seat) values (1,'tom',null),
                                             (2,'tom',null);

/*
外健的特点
在从表的上建立外健约束，
从表与主表的类型和主表的关联列的类型要求一致或兼容，名称无要求
插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，在删除主表
*/

#修改表时，添加约束
/*
添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
添加表级约束
alter table 表名 add constraint 表级约束
*/

drop table if exists stuinfo;
desc stuinfo;
create table if not exists stuinfo(
                                      id int,
                                      stuName varchar(20),
                                      gender char(1),
                                      seat int ,
                                      age int ,
                                      majorid int
);
use students;
#1.添加非空约束
alter table stuinfo modify column stuName varchar(20) not null ;
#2.删除非空约束
alter table stuinfo modify column stuName varchar(20) null;
#3.添加默认约束
alter table stuinfo modify column age int default 18;
#4.添加主键
#列级约束
alter table stuinfo modify column id int primary key ;
#表级约束
alter table stuinfo add constraint primary key (id);
#5.添加唯一
use students;
alter table stuinfo modify column seat int unique ;
alter table students.stuinfo add constraint  unique (seat);
#6.添加外健
alter table students.stuinfo add foreign key (majorid) references major(id);
alter table students.stuinfo add constraint fk foreign key (majorid) references major(id);

#三，修改表时删除约束
#1.删除非空约束
alter table stuinfo modify column stuName varchar(20) null;
#2.删除默认约束
alter table stuinfo modify column age int ;
#3.删除主键
alter table stuinfo drop primary key ;
#4.删除唯一约束
alter table stuinfo drop index fk;
#5.删除外健约束
alter table stuinfo drop foreign key fk;
show index from stuinfo;
desc stuinfo;

#标识列
/*
又称为自增加列
可以不用手动的插入值，系统提供默认的序列值
特点：
标识页必须和主键搭配吗？不一定，但必须和键搭配
一个表中可以有多少个标识列 只能一个自增长列
标识的类型，只能是数字类型
*/
#一，创建表时设置标识列
drop table if exists tab_identity;
create table tab_identity(
    id int ,
    name varchar(20)
);
select * from tab_identity;
insert into tab_identity values (1,'john');
insert into tab_identity values (2,'tom');
insert into tab_identity (name) values ('wede');
insert into tab_identity values (20,'james');
#1.不从一开始
show VARIABLES like '%auto_increment%';
#修改表时设置标识列
alter table tab_identity modify column id int primary key auto_increment;

#############################     事物    ################################
/*
事物的创建
隐式的事物：事物没有明显的开启和结束的标记
比如insert，update，delete等单条sql语句
显示事物：必须先设置自动提交功能为禁用  set autocommit=0;
开启事物的语句
sql语句1;
sql语句2;
提交事物的语句
*/
show variables like 'autocommit';
/*
步骤1：开启事物
set autocommit=0;
start transcation;可选的
步骤2：编写事物中的语句
步骤3 ：结束事物
commit;提交事物
rollback;回滚事物
*/

#事物的演示
drop table if exists account;
create table account(
    id       bigint unsigned comment 'id',
    username varchar(20) comment '用户姓名',
    balance  decimal(40, 20) comment '存款'
)character set utf8;
alter table account default character set utf8;
alter table account modify column id bigint unsigned primary key auto_increment;
insert into account (username,balance) values ('aa',1000),('赵敏',1000);
select * from account;

#开启事物
set autocommit =0;


update account set balance=300 where username='张无忌';
start transaction ;
update account set balance=500 where username='张无忌';
update account set balance=1500 where username='赵敏';
#commit;
rollback ;

#事物没有隔离性
/*
脏读：对于两个事物t1,t2.t1读取了已经被t2更新，但是还没有提交的字段，若t2回滚，t1读取的数据就是无效的
不可重复读：对于两个事物t1,t2.t1读取了一个字段，然后t2个更新了该字段，t1再读取同一个字段，值就不同了
幻读:对于两个事物t1,t2.t1读取了一些字段，然后t2在表中插入了一些新的数据，如果t1再次读取同一个表，就会多出几行
可以设置隔离级别，避免脏读等问题
mysql支持的隔离级别
read uncommitted
read committed
*/
#查看隔离级别
select @@tx_isolation;
set session transaction isolation level read uncommitted ; #无法避免脏读，幻读，不可重复读

set session transaction isolation level read committed ; #可避免脏读，无法避免幻读，不可重复读

set session transaction isolation level repeatable read; #可避免脏读，不可重复读,无法避免幻读

set session transaction isolation level serializable; #可避免脏读，不可重复读,幻读
#savepoint 结点名 设置回滚点
use students;
select * from account;
set autocommit =0;
start transaction ;
delete from account where id=5;
savepoint a;
delete from account where id =6;
rollback to a;

#视图，虚拟的表
/*
和普通的表一样的，mysql5.1出现的新特性，通过表动态生成的数据
*/
desc students.stuinfo;
select * from students.stuinfo;
select * from major;
insert into
    students.stuinfo
values
    (1,'张飞','男',111,18,1),
    (2,'刘备','男',112,18,2),
    (3,'关羽','男',113,18,1),
    (4,'赵云','男',114,18,2),
    (5,'黄盖','男',115,18,1);
set character set utf8;
show variables like '%char%';
set character_set_database=utf8;
set character_set_server =utf8;
set character_set_connection =utf8;
set character_set_filesystem =utf8;
insert into major (id,majorNmae) values (1,'语文'),(2,'数学');
select s.stuName,majorNmae from stuinfo s inner join major m on s.majorid=m.id where s.stuName like '张%';

create view v1 as select s.stuName,majorNmae from stuinfo s inner join major m on s.majorid=m.id where s.stuName like '张%';
select * from v1 where stuName='张飞';

#一，创建视图
/*
语法：
create view 视图名
as 复杂查询语句
*/
use myemployees;
create view
    myv1
as select
    e.last_name,department_name,job_title
from employees e
    inner join departments d on e.department_id=d.department_id
    inner join jobs j on e.job_id = j.job_id;

#二，使用视图
select * from myv1;
select last_name,department_name,job_title from myv1 where last_name like '%a%';
drop view avgsalary;
create view avgsalary as select avg(salary) avs,department_id from employees group by department_id;
select  avs, j.grade_level from avgsalary a inner join job_grades j on a.avs>j.lowest_sal and a.avs<j.highest_sal ;

select avs,department_id from avgsalary order by avs desc limit 1;
create view myv3 as select avs,department_id from avgsalary order by avs desc limit 1;

select avs,department_name from avgsalary a inner join departments d on a.department_id = d.department_id order by a.avs desc limit 1;
select avs,department_name from myv3 m inner join  departments d on m.department_id = d.department_id;

#三，视图的修改
/*
方式一：
create or replace view 视图名 查询语句
方式二:
alter view 视图名 as 查询语句
*/

select * from myv3;
create or replace view myv3 as select avg(salary) avs,job_id from employees group by job_id;

#四，删除视图
/*
drop view 视图名1，视图名2
*/

#五，查看视图
desc myv3;
show create view myemployees.myv3;
drop view emp_v1;
create view emp_v1 as select last_name,salary,email from employees where phone_number like '011%';
select * from emp_v1;

create or replace view emp_v2
    as select max(salary),department_name
    from employees e inner join departments d on e.department_id = d.department_id
group by e.department_id having max(salary) >12000;
select * from emp_v2;

#六，视图的更新
create or replace view myv1 as select last_name,email,salary*12*(1+ifnull(commission_pct,0)) asalary from employees;
create or replace view myv1 as select last_name,email from employees;
#1.插入数据
/*
在视图中插入数据，会在原始的表中插入数据

*/
select * from myv1;
insert into myv1 values ('张飞','ZF@qq.com');

#2.修改
update myv1 set last_name='张无忌' where last_name='张飞';

#3.删除
delete from myv1 where last_name='张无忌';

#可以为视图添加只读权限

/*
具备一下关键词的视图是不允许更新
distinct,group by, having,union或则union all;
*/
create or replace view myv1 as select max(salary) m,department_id from employees group by department_id;
update myv1 set m=9000 where department_id=10;

#常量视图
create or replace view myv2 as select 'john' name;
update myv2 set name='jack';

#select 中包含子查询
create or replace view myv3 as select (select max(salary) from employees) '最高工资';
update myv3 set 最高工资=100000;

#join语句不能更新
#from 后是一个不能更新的视图

/*
视图  create table    不占用实际物理空间                              增删该查
表    create view     没有占有实际的物理空间，只是保存了实际的物理逻辑     增删该查，一般只做查询

delete与truncate在事物使用时的区别
delete可以回滚，
truncate不可以回滚
*/

#演示delete

create table if not exists bookType(
    id int auto_increment,
    type varchar(20) not null ,
    primary key (id)
);

use students;
create table book(
    bid int primary key ,
    bname varchar(20) unique  not null ,
    price float default 10,
    btype_id int ,
    constraint foreign key fk_book_booktype(btype_id) references bookType(id)
);
insert into bookType values(1,'aaaa');
set autocommit =0;
insert into book (bid, bname,price, btype_id) values (1,'it',12.34,1);

create view bookview as select bname,type from book b inner join bookType bT on b.btype_id = bT.id where b.price>1000;

#create or replace view bookview as
#alter view bookview as select

#级联更新或级联删除

drop table if exists major;
create table major(
                      id int primary key ,
                      majorName varchar(20)
)default character set utf8;
show index from major;
insert into major values (1,'java'),(2,'h5'),(3,'data');

drop table if exists stuinfo;
desc stuinfo;
create table if not exists stuinfo(
                                      id int,
                                      stuName varchar(20),
                                      gender char(1),
                                      seat int ,
                                      age int ,
                                      majorid int
)default character set =utf8;
show index from stuinfo;
#添加外健
alter table stuinfo add constraint fk_stu_major foreign key(majorid) references major(id);
insert into
    stuinfo
values
    (1,'john1','女',null,null,1),
    (2,'john2','女',null,null,2),
    (3,'john3','女',null,null,1),
    (4,'john4','女',null,null,2),
    (5,'john5','女',null,null,3),
    (6,'john6','女',null,null,1);

#删除专业表的3号专业
/*
由于存在外健存在不能删除
*/
use students;
#方式一：级联删除,删除主表会将从表进行删除
show create table students.stuinfo;
alter table stuinfo drop foreign key fk_stu_major;
alter table stuinfo add constraint fk_stu_major foreign key(majorid) references students.major(id) on delete cascade ;

select * from students.major;
select * from students.stuinfo;
delete from students.major where id=4;

#方式二：级联置空
alter table stuinfo drop foreign key fk_stu_major;
alter table stuinfo add constraint fk_stu_major foreign key(majorid) references students.major(id) on delete set null;

select * from students.major;
select * from students.stuinfo;
delete from major where id =2;


#变量
/*
系统变量:变量由系统提供，不是由用户定义，属于服务器层面
1>>使用语法：
1,查看所有的系统变量
show global variables ;查看全局变量
show session variables ;查看会话变量
2.查看满足条件的部分系统变量
show global|session variables like '%char%';
3.查看制定某个系统变量的值
select @@global|session.系统变量名
4.为某一个系统变量赋值
方式一：
set global|session 系统变量名=值
方式二：
set global|session.系统变量名=值;
2>>会话变量
作用域：仅仅针对当前的链接
1.查看会话变量
show session variables
show variables
2.查看部分会话变量
show session variables like '%char%'
3.查看制定的某一个会话变量
select @@tx_isolation;
4.为某一个会话变量赋值
set @@tx_isolation='read-uncommitted';
set session tx_isolation = 'read-committed'
二，自定义变量
用户自定义的变量
使用步骤：
申明
赋值
使用
1,用户变量：
作用域：针对当前会话（链接）有效，同于会话变量的作用域
#1,声明并初始化 =或者:=
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
#2.赋值或者更新用户变零的值
方式一:通过set或select
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
方式二： select into
select 字段 into 变量名 from 表;
#.3查看用户变量
select @用户变量名

--局部变量：
作用域:仅仅在定义他的begin end中有效
只能放在begin，end中的第一句话
#1,声明
declare 变量名 类型
declare 变量名 类型 default 值;
#2，赋值
方式一:通过set或select
set 局部变量名=值;
set 局部变量名:=值;
select @局部变量名:=值;
方式二： select into
select 字段 into 局部变量名 from 表;
#3.使用
select 局部变量名;

         作用域             定义与使用的位置                         语法
用户变量  当前会话          会话中的任何位置                   必须加@符号，不用设定类型
局部变量  begin end       只能在begin end中，且为第一句话      一般不用加@符号，需要限定
*/
set @name=100;
select count(*) into @count from students.major;
select @count ;

set @m=1;
set @n=2;
set @sum = @m+@n;
select @sum;


declare m int default 1;
declare n int default 2;
declare sum int;
set sum =n+m;
select sum;


#储存过程和函数
/*
存储过程与函数，类似于java中的方法
方法的好处：
1.提高代码的重用性
2.简化操作
3.减少了编译此时并且减少了和数据库服务器的链接次数，提高了效率
#存储过程：
预先编译好的sql语句的集合，可以理解成批处理语句
#一，创建语法
create procedure 存储过程名(参数类表)
begin
    存储过程体
end
注意，
1.参数列表包含三部分
参数模式 参数名 参数类型
IN stuname varchar(20)
参数模式：
in 该参数可以作为输入，也就是说需要调用法传入值
out 该参数可以作为输出，该参数可以作为返回值
inout

2.如果存储过程体仅仅只有一句话，begin end 可以省略
存储过程体中每条sql语句的结尾必须加分号
存储过程的结尾可以使用delimiter重新设置
语法
delimiter 结束标记
案例：
delimiter $
二，调用过程
call 存储过程名(实参列表);

*/

#1.空参列表
use girls;
drop table admin;
create table admin(
    id bigint unsigned auto_increment,
    username varchar(20)  not null,
    password varchar(20) not null,
    primary key (id)
)default character set = utf8;

insert into
    admin (id,username,password)
values
    (1,'join',8888),
    (2,'lyt',6666);
select * from admin;

delimiter $
create procedure myp1()
begin
    insert into admin(username,password) values ('john2',0000),('jack',0000),('tom1',0000);
end $

call myp1()$

#in模式的参数的存储过程
create procedure myp2(in name varchar(20))
begin
    select bo.* from boys bo right join beauty b on b.boyfriend_id=bo.id  where b.name=name;
end $

call myp2('柳岩') $

create procedure myp3(in username varchar(20),in password varchar(20))
begin
    declare result varchar(20) default '';
    select count(*) into result from admin where admin.username=username and admin.password=password;
    select result;

end $

create procedure myp5(in username varchar(20),in password varchar(20))
begin
    declare result int;
    select count(*) into result from admin where admin.username=username and admin.password=password;
    select if(result>0,'成功','失败');
end $

call myp5('张飞',0000) $

#创建带out模式的存储过程
create procedure myp7(in girlname varchar(20),out boyname varchar(20))
begin
    select bo.boyName into boyname from boys bo inner join beauty b on bo.id=b.boyfriend_id where b.name=girlname;
end $
select * from beauty;
select * from boys;
set @name='' $
call myp7('柳岩',@name) $
select @name;

call myp7('柳岩',@name1) $
select @name1;

#4.带inout模式的存储模式
create procedure myp8(inout a int,inout b int)
begin
    set a=a*2;
    set b=b*2;
end $


set @a=10 $
set @b=20 $
call myp8(@a,@b) $
select @a,@b;


create procedure test1(in user varchar(20),in psas varchar(20))
begin
    insert into admin (admin.username,admin.password) values (username,password);
end $
call test1('aaa','0000');
select * from admin;

create procedure test2(in id int,out name varchar(20),out phone varchar(20))
begin
    select b.name,b.phone into name,phone from beauty b where b.id=id;
end $

#存储过程的删除
drop procedure test1;
#查看存储过程的信息
show create procedure myp2;

create procedure test3(in mydate datetime,out strdate varchar(50))
begin
    select date_format(mydate,'%Y年%m月%d日') into strdate;
end $

call test3(now(),@str);
select @str;

#函数
/*
与存储过程的差异：
存储过程可以有0个返回，也可以有多个返回
函数必须有一个返回        处理数据后，返回一个结果

*/

#一，函数的创建语法
/*
create function 函数名(参数列表) returns 返回类型
begin
    函数体;
end
注意：
1.函数体：
肯定有return语句，如果没有会报错，
如果return语句没有放在函数体的最后也不会报错，但不建议
return 值;
2.使用delimiter语句设置结束标记
*/
#调用语法
/*
select 函数名(参数列表)
*/
#无参无返回
use myemployees;
create function myf1() returns int
begin
    declare c int default 0;
    select count(*) into c from employees;
    return c;
end $
select myf1()$
#有参有返回
create function myf2(empNmae varchar(20)) returns double
begin
    declare s double default 0;
    set @sal=0;
    select salary into s from employees where last_name=empNmae;
    return s;
end $
select * from employees;
select myf2('Kochhar');

create function myf3(deptname varchar(20)) returns double
begin
    declare sal double default 0;
    select avg(salary) into sal from employees e inner join departments d on e.department_id=d.department_id where department_name=deptname;
    return sal;
end $
select * from departments;
select myf3('IT');

#三，查看函数
show create function myf3;

#四，删除函数
drop function myf3;

create function myf4(num1 float,num2 float) returns float
begin
   declare sum float;
   set sum= num1+num2;
   return sum;
end $

select myf4(1.3,1.4);

#流程控制结构
/*
顺序结构：程序从上往下依次执行
分支结构:程序从两条或多条中选择一条去执行
循环结构：程序在满足一定条件下，重复执行一段代码

#一，分支结构
#1.if函数
功能：实现简单的双分支
语法：
if(表达式1,表达式2,表达式3)
如果表达式1成立，返回表达式2,不成立返回表达式3

#2.case函数
情况一：使用switch语句，一般用于实现等值判断
语法：
case 变量|表达式|字段
when 要判断的值 then 返回的值1或语句;
when 要判断的值 then 返回的值2或语句;
when 要判断的值 then 返回的值3或语句;
。。。
else 要返回的值;
end case;
情况二：类似与多重if语句，一般用于实现区间判断
语法：
case
when 要判断的条件 then 返回的值1
when 要判断的条件 then 返回的值2
when 要判断的条件 then 返回的值3
。。。
else 要返回的值
end

特点：
既可以作为表达式。嵌套在其他语句中使用，可以放在任何地方
*/

create procedure myf5(in score int)
begin
   case
   when  score>=90 and score<=100 then  select 'A';
   when  score>=80 and score<=90 then  select 'b';
   when  score>=70 and score<=80 then  select 'c';
   when  score>=60 and score<=70 then  select 'd';
   else select 'E';
   end case ;
end $

call myf5(77);

/*
if结构
语法：
if (条件1) then 语句1;
elseif 条件2 then 语句2;
elseif 条件2 then 语句2;
end if;

应用在begin end语句中
*/
create function myf6 (score int) returns char
begin
    if score>=90 and score<=100 then return 'a';
    elseif score>=80 and score<=90 then return 'b';
    elseif score>=70 and score<=80 then return 'c';
        end if;
end $
select myf6(77);


#循环结构
/*
分类while,loop,repeat
循环控制：
iterate 类似于continue
leave 类似于break
1。while的语法
while 循环条件 do
 循环体;
end while
2.loop
标签:loop
循环体
end loop [标签]
3.repeat
语法:
标签:repeat
循环体;
until 结束条件
end repeat[标签]
*/
use girls;
create procedure pro_while1(in insertCount int)
begin
    declare i int default 1;
    while (i<=insertCount) do
        insert into admin(username,password) values ('rose','6666');
        set i=i+1;
    end while ;
end $
drop procedure pro_while1;
call pro_while1(10)$
select * from admin;

create procedure pro_while2(in insertCount int)
begin
    declare i int default 1;
    a:while (i<=insertCount) do
            insert into admin(username,password) values (concat('rose',i),'5555');
            if i>=20 then leave a;
            end if;
            set i=i+1;
    end while a;
end $

call pro_while2(111);