Mysql笔记入门到精通

-- 查看数据库
show databases;

-- 创建数据库
create database php9 charset utf8

-- 查看数据库的创建语句
show create database php9;

-- 删除数据库
drop database php9;


-- 创建数据库
create database php9 charset utf8;

-- 选择数据库
use php9;

-- 设置通讯字符集
set names gbk;

-- 查看数据表
show tables;

-- 创建数据表
create table stus(
s_id   char(10),
s_name varchar(32),
s_age  tinyint,
s_sex  char(6)
)charset utf8;

-- 查看表结构
desc stus;

-- 查看表的创建语句
show create table stus;

-- 删除表
drop table stus;

-- 插入数据
insert into stus(s_id,s_age,s_name) values('php001',20,'zhangsan');
insert into stus values('php001','zhangsan',20,'男');
insert into stus values('php003','lisi',20,'女');
insert into stus values('php004','wangwu',22,'女');
insert into stus values('php005','zhaoliu',25,'女');
insert into stus values('php006','tianqi',18,'女');
insert into stus values('php006','QQ',21,'女');
insert into stus values('php007','wangwang',23,'女');
insert into stus values('php008','wangw',26,'男');

-- 查看表中所有的字段数据
select * from stus;

-- 仅查看s_name,s_age字段
select s_name,s_age from stus;

-- 需求:查询年龄为20所有的学生
select s_name,s_age from stus where s_age = 20;

-- 需求:查看年龄大于等于20,小于等于23的所有的学生
select * from stus where s_age>=20 and s_age<=23;

select * from stus where s_age between 20 and 23;
select * from stus where s_age not between 20 and 23;

-- 需求:查看年龄为18或20或23的学生
select * from stus where s_age =18 or s_age =20 or s_age=23;

select * from stus where s_age in(18,20,23);

-- 需求:查看性别为null的所有的记录
select * from stus where s_sex is null;
select * from stus where s_sex is not null;

-- 需求:查看姓wang的所有的学生
select * from stus where s_name like 'wang%';

select * from stus where s_name like 'wangw%';
select * from stus where s_name like 'wangw_';

-- 更改:将s_sex为null的记录的s_sex字段更改为'男'
update stus set s_sex='男' where s_sex is null;

-- 删除:stus表中的姓名为wangwang的记录
delete from stus where s_name='wangwang';


-- 浮点型
create table test_float(
f1 float(12,4),
f2 double(20,4)
);

-- 插入数据
insert into test_float values(12345678.6789,1234567892345689.6789);
insert into test_float values(99999123.6789,9999999999945689.6789);

-- decimal
create table test_decimal(
d1 decimal(12,3),
d2 decimal
);

insert into test_decimal values(123456789.999,123456789);

-- 创建表
create table test_char_varchar(
s_num char(8),
s_name varchar(32),
s_tel char(11)
)charset utf8;

-- 插入数据
insert into test_char_varchar values('php001','zhangsan','12312312312');

-- 创建表
create table test_enum(
color enum('yellow','green','white','red','blue','pink','cyan','purple')
)charset utf8;

-- 插入数据
insert into test_enum values('yellow');

insert into test_enum values('black');

-- 创建表
create table test_set(
color set('yellow','green','white','red','blue','pink','cyan','purple')
)charset utf8;

-- 插入1个值
insert into test_set values('yellow');

-- 插入多个值
insert into test_set values('green,white');

-- 创建表
create table test_date(
content char(10),
f1 year(4),
f2 date,
f3 time,
f4 datetime,
f5 timestamp
)charset utf8;

-- 插入数据
insert into test_date(f1,f2,f3,f4) values('2017','2017-12-04','17:18:50','2017-12-04 17:18:50');

-- 修改content值
update test_date set content='abc';

-- 查看字符集
show charset;

-- 查看校验集
show collation;

-- 创建表
create table test_collate(
f1 char(10)
)charset utf8;

insert into test_collate values('abc'),('ABC'),('aBc');


-- 创建表
create table test_bin(
f1 char(10)
)charset utf8 collate utf8_bin;

insert into test_bin values('abc'),('ABC'),('aBc');

-- 显示宽度
drop table if exists test_zerofill;
create table test_zerofill(
f1 int(6) zerofill
)charset utf8;

-- 插入测试数据
insert into test_zerofill values(100);


create table test_zero(
f1 int
)charset utf8;

-- 插入测试数据
insert into test_zero values(100);

-- null
drop table if exists test_null;
create table test_null(
s_num char(8),
s_name varchar(16),
s_age tinyint unsigned,
s_sex char(1)
)charset utf8;

-- 插入测试数据
insert into test_null(s_num,s_name) values('php001','zhangsan');

-- not null
drop table if exists test_null;
create table test_null(
s_num char(8),
s_name varchar(16),
s_age tinyint unsigned not null,
s_sex char(1) not null
)charset utf8;

-- 插入测试数据
insert into test_null(s_num,s_name) values('php001','zhangsan');

-- not null
drop table if exists test_null;
create table test_null(
s_num char(8),
s_name varchar(16),
s_age tinyint unsigned not null default 18,
s_sex char(1) not null default '男'

)charset utf8;

-- 插入测试数据
insert into test_null(s_num,s_name) values('php001','zhangsan');

-- default关键字
insert into test_null values('php003','wangwu',default,default);

-- unique
drop table if exists test_unique;
create table test_unique(
s_num char(8) unique,
s_name varchar(16),
s_age tinyint unsigned not null default 18,
s_sex char(1) not null default '男'
)charset utf8;


-- 插入测试数据
insert into test_unique(s_num,s_name) values('php001','zhangsan');
insert into test_unique(s_num,s_name) values('php001','lisi');

insert into test_unique(s_name) values('wangwu');
insert into test_unique(s_name) values('zhaoliu');

-- primary key
drop table if exists test_primary;
create table test_primary(
id int unsigned primary key,
s_num char(8) unique,
s_name varchar(16),
s_age tinyint unsigned not null default 18,
s_sex char(1) not null default '男'
)charset utf8;

insert into test_primary values(1,'php001','zhangsan',30,'男');
insert into test_primary values(2,'php002','lisi',25,'男');
insert into test_primary values(3,'php002','lisi',25,'男');


-- primary key auto_increment
drop table if exists test_primary;
create table test_primary(
id int unsigned primary key auto_increment,
s_num char(8) unique,
s_name varchar(16),
s_age tinyint unsigned not null default 18,
s_sex char(1) not null default '男'
)charset utf8;

-- 插入测试数据
insert into test_primary values(default,'php001','zhangsan',30,'男');
insert into test_primary values(default,'php002','lisi',23,'男');





insert into test_primary values(2,'php002','lisi',25,'男');


insert into test_primary values(10,'php003','wangwu',25,'女');
insert into test_primary values(default,'php004','zhaoliu',26,'女');


-- unique & not null
drop table if exists test_primary;
create table test_primary(
f1 int unsigned unique not null,
f2 int unsigned unique,
f3 int unsigned not null,
f4 int unsigned unique not null
)charset utf8;

-- 复合主键
create table test(
f1 int unsigned,
f2 char(10),
primary key(f1,f2)
)charset utf8;

-- 插入测试数据
insert into test values(1,'a');
insert into test values(1,'b');
insert into test values(2,'b');
insert into test values(1,'a');

-- comment
create table test_comment(
id int unsigned primary key auto_increment comment '主键',
s_num char(8) unique comment '学号',
s_name varchar(16) comment '姓名',
s_age tinyint unsigned not null default 18 comment '年龄',
s_sex char(1) not null default '男' comment '性别'
)charset utf8;


-- 创建测试表
drop table if exists test;
create table test(
s_name char(32)
)charset utf8;

-- 添加字段
alter table test add s_age tinyint unsigned;
alter table test add s_nickname varchar(32) after s_name;

-- 删除字段
alter table test drop s_nickname;

-- 修改字段名
alter table test change s_age age tinyint unsigned;

-- 修改字段类型
alter table test modify s_name varchar(32);

-- 修改表名
alter table test rename to php9;

create table test_charset(
f1 char(10)
)charset utf8;

-- 修改列属性
drop table test_modify;
create table test_modify(
f1 int unsigned,
f2 char(10),
f3 int unsigned
)charset utf8;

-- 添加列属性
alter table test_modify modify f1 int not null default 0;
alter table test_modify modify f2 char(10) unique;
alter table test_modify modify f3 int unique auto_increment;

-- 添加主键
drop table test_primary;
create table test_primary(
f1 int unsigned,
f2 char(10),
f3 int unsigned
)charset utf8;

alter table test_primary add primary key(f1);

alter table test_primary modify f1 int unsigned auto_increment;



-- 修改列属性
drop table test_modify;
create table test_modify(
f1 int unsigned,
f2 char(10),
f3 int unsigned
)charset utf8;

-- 添加列属性
alter table test_modify modify f1 int not null default 0;
alter table test_modify modify f2 char(10) unique;
alter table test_modify modify f3 int unique auto_increment;

-- 删除列属性
alter table test_modify modify f1 int;
alter table test_modify modify f2 char(10);


alter table test_modify modify f3 int unique;


-- 删除主键
-- 如果auto_increment与primary key联用,那么想删除primary key 要先删除auto_increment
alter table test_primary modify f1 int unsigned;
-- 再删除primary key
alter table test_primary drop primary key;

-- 创建表
drop table if exists test_unique;
create table test_unique(
f1 char(10) unique
)charset utf8;

-- 查看唯一键的索引名
show create table test_unique;

-- 删除unique
alter table test_unique drop key f1;


-- 主键冲突
drop table if exists goods;
create table goods(
id int unsigned primary key auto_increment,
brand char(16),
goodsName char(16),
channel char(8),
price decimal(10,2),
inventory smallint unsigned
);
insert into goods values(default,'huawei','mate','3G',2300,30);
insert into goods values(default,'apple','6plus','4G',4500,43);
insert into goods values(default,'apple','6splus','4G',5100,20);
insert into goods values(default,'samsung','galaxy6','4G',3800,28);
insert into goods values(default,'huawei',null,'3G',3200,15);
insert into goods values(default,'apple','5c','3G',1800,38);
insert into goods values(default,'huawei','mate8','4G',3500,51);
insert into goods values(default,'huawei','p8Max','4G',2700,27);
insert into goods values(default,'apple','5s','3G',2800,38);
insert into goods values(default,'samsung','galaxy5','3G',3400,33);

-- 进货
insert into goods values(1,'huawei','mate','3G',2300,50) on duplicate key update inventory=inventory+50,;
insert into goods values(11,'oppo','R','3G',3300,40) on duplicate key update inventory=inventory+50;

-- 冲突替换
replace into goods values(1,'huawei','mate','3G',2300,50);


-- 主键冲突
drop table if exists goods;
create table goods(
id int unsigned primary key auto_increment,
brand char(16),
goodsName char(16),
channel char(8),
price decimal(10,2),
inventory smallint unsigned
);
insert into goods values(default,'huawei','mate','3G',2300,30);
insert into goods values(default,'apple','6plus','4G',4500,43);
insert into goods values(default,'apple','6splus','4G',5100,20);
insert into goods values(default,'samsung','galaxy6','4G',3800,28);
insert into goods values(default,'huawei',null,'3G',3200,15);
insert into goods values(default,'apple','5c','3G',1800,38);
insert into goods values(default,'huawei','mate8','4G',3500,51);
insert into goods values(default,'huawei','p8Max','4G',2700,27);
insert into goods values(default,'apple','5s','3G',2800,38);
insert into goods values(default,'samsung','galaxy5','3G',3400,33);


-- 字段别名
select brand as 品牌,channel 制式,goodsName,price from goods;

drop table if exists stu;
create table stu(
id int unsigned primary key auto_increment,
s_num char(10),
s_name char(10),
s_sex char(2),
s_age tinyint unsigned);

-- 再向从表插入数据
insert into stu values(default,'it001','zhangsan',1,22);
insert into stu values(default,'it002','lisi',1,20);
insert into stu values(default,'it003','wangwu',0,26);
insert into stu values(default,'it004','zhaoliu',1,18);
insert into stu values(default,'it005','tianqi',0,21);
insert into stu values(default,'it006','tianqi',0,21);
insert into stu values(default,'it007','wangwang',0,21);

drop table if exists class;
create table class(
c_id int unsigned primary key auto_increment,
c_name char(10),
c_room char(10)
);

-- 先向主表插入数据
insert into class values(default,'php001','1312');	
insert into class values(default,'php002','1322');
insert into class values(default,'php003','1302');
insert into class values(default,'php004','1311');

-- 表别名
select * from stu as s;

-- 使用列别名引用字段
select s.s_name,s.s_num from stu s;

-- 需求,按品牌进行分组
select * from goods group by brand;

-- 需求:统计每一品牌的记录数。
select brand,count(*) from goods group by brand;

-- 需求:统计每一品牌中商品的价格最大值
select brand,max(price) from goods group by brand;

-- 需求:统计每一品牌中商品的价格最小值
select brand,min(price) from goods group by brand;

-- 需求:统计每一品牌中价格的平均值
select brand,avg(price) from goods group by brand;

-- 需求:统计每一品牌中的库存的总数量
select brand,sum(inventory) from goods group by brand;

-- 需求:统计每一品牌中的记录数
select brand,count(*) from goods group by brand;

-- 需求:统计每一种制式中的每一品牌的记录数
select channel,brand,count(*) from goods group by channel,brand with rollup;

-- 需求:查找每一种品牌中,库存数量大于100的品牌
select brand,sum(inventory) sum from goods group by brand having sum >100;

-- 需求:查询商品的价格>2000的商品
select * from goods where price >2000;

-- 需求:查询商品的价格>2000的商品
select * from goods having price >2000;

-- 需求:查找所有的商品按价格进行升序排列
select * from goods order by price asc;

-- 需求:查找所有的商品按价格进行降序排列
select * from goods order by price desc;

-- 需求:按品牌进行升序排序,如果相同品牌按价格进行降序排序
select * from goods order by brand,price desc;

-- 需求:查找每一品牌中价格最大的商品
select *,max(price) from goods group by brand order by price desc;

-- 显示所有的记录的前5行
select * from goods limit 5;

假设	
rowsPerPage=3		表示每一页显示3条记录
	curPage		1   2  3   4  5-- 第1页的数据
select * from goods limit 0,3;	

-- 第2页的数据
select * from goods limit 3,3;

-- 第3页的数据
select * from goods limit 6,3;

-- 第4页的数据
select * from goods limit 9,3;


select * from goods limit (curPage-1)*rowsPerPage,rowsPerPage;

-- 联合查询
select * from goodsA
union
select * from goodsB;

-- 需求:s_sex=1的学生按年龄降序排序
select * from stu where s_sex='1' order by s_age desc;


-- 需求:s_sex=0的学生按年龄升序排序
select * from stu where s_sex='0' order by s_age asc;

(select * from stu where s_sex='1' order by s_age desc limit 999999)
union
(select * from stu where s_sex='0' order by s_age asc limit 999999);



create table test_myisam(
id int unsigned primary key auto_increment,
brand char(16),
goodsName char(16),
channel char(8),
price decimal(10,2),
inventory smallint unsigned
);

drop table if exists class;
create table class(
c_id int unsigned primary key auto_increment,
c_name char(10),
c_room char(10)
);

-- 先向主表插入数据
insert into class values(default,'php001','1312');	
insert into class values(default,'php002','1322');
insert into class values(default,'php003','1302');
insert into class values(default,'php004','1311');



drop table if exists stu;
create table stu(
id int unsigned primary key auto_increment,
s_num char(10),
s_name char(10),
s_sex char(2),
s_age tinyint unsigned,
c_id int unsigned
);

-- 再向从表插入数据
insert into stu values(default,'it001','zhangsan',1,22,1);
insert into stu values(default,'it002','lisi',1,20,2);
insert into stu values(default,'it003','wangwu',0,26,1);
insert into stu values(default,'it004','zhaoliu',1,18,3);
insert into stu values(default,'it005','tianqi',0,21,4);
insert into stu values(default,'it006','tianqi',0,21,4);
insert into stu values(default,'it007','wangwang',0,21,1);

-- 新来一个学生
insert into stu values(default,'it008','qq',0,21,1);

-- 又新来一个学生
insert into stu values(default,'it008','xiaoqiang',0,21,5);


-- 先创建主表
drop table if exists class;
create table class(
c_id int unsigned primary key auto_increment,
c_name char(10),
c_room char(10)
);

-- 先向主表插入数据
insert into class values(default,'php001','1312');	
insert into class values(default,'php002','1322');
insert into class values(default,'php003','1302');
insert into class values(default,'php004','1311');

-- 创建外键
drop table if exists stu;
create table stu(
id int unsigned primary key auto_increment,
s_num char(10),
s_name char(10),
s_sex char(2),
s_age tinyint unsigned,
c_id int unsigned comment '外键字段',
foreign key(c_id) references class(c_id)
);

-- 再向从表插入数据
insert into stu values(default,'it001','zhangsan',1,22,1);


insert into stu values(default,'it002','lisi',1,20,5);



-- 创建外键的级联与置空约束
-- 先创建主表
drop table if exists class;
create table class(
c_id int unsigned primary key auto_increment,
c_name char(10),
c_room char(10)
);

-- 先向主表插入数据
insert into class values(default,'php001','1312');	
insert into class values(default,'php002','1322');
insert into class values(default,'php003','1302');
insert into class values(default,'php004','1311');

-- 创建外键
drop table if exists stu;
create table stu(
id int unsigned primary key auto_increment,
s_num char(10),
s_name char(10),
s_sex char(2),
s_age tinyint unsigned,
c_id int unsigned comment '外键字段',
foreign key(c_id) references class(c_id) on update cascade on delete set null
);


-- 查找班级为php002的所有的学生。
select * from stu where c_id = (select c_id from class where c_name='php002');

-- 查找班级为php002,以及php003,php004所有的学生

select * from stu where c_id in (select c_id from class where c_name in ('php002','php003','php004'));


-- 需求:找老乡
create table php(
name varchar(12),
province varchar(20),
city varchar(20),
town varchar(20)
)charset utf8;


insert into php values('zhangsan','gd','sz','ft');

create table ui(
name varchar(12),
province varchar(20),
city varchar(20),
town varchar(20)
)charset utf8;
insert into ui values('lisi','gd','sz','ft');
insert into ui values('liwu','gx','nn','cy');
insert into ui values('liliu','sd','ta','sj');

-- php中的zhangsan去ui中找老乡
select * from ui where (province,city,town)=(select province,city,town from php where name='zhangsan');

-- 统计每一品牌中价格最大的商品信息。
select *,max(price) from goods group by brand;


drop table if exists stus;


select * from stu where 5 and exists(select * from class where c_id=5);
select * from stu where 5 and 0;

create table employee(
e_id int unsigned primary key auto_increment,
e_name varchar(16),
e_dept tinyint unsigned,
e_salary decimal(10,3)
);

-- 插入数据
insert into employee values(default,'zhangsan',1,4000);
insert into employee values(default,'lisi',2,5000);
insert into employee values(default,'wangwu',1,5360);
insert into employee values(default,'zhaoliu',2,5300);
insert into employee values(default,'tianqi',1,6000);
insert into employee values(default,'wangwang',3,5000);
insert into employee values(default,'QIQI',3,5000);
insert into employee values(default,'YuanYuan',2,5800);



-- 查询工资大于5000的所有的员工的信息
select * from employee where e_salary>=5000;



-- 查询1号部门工资大于2号部门中任何一个员工工资的员工的信息。
select * from employee where e_salary >any(select e_salary from employee where e_dept=2) and e_dept <>2;


-- 查询1号部门工资大于2号部门中所有员工工资的员工的信息。
select * from employee where e_salary > (select max(e_salary) from employee where e_dept=2) and e_dept=1;

select * from employee where e_salary >all(select e_salary from employee where e_dept=2) and e_dept <>2;



-- 子查询的需求的特点:
-- 查询班级为php002班的所有的学生。
select * from stu where c_id = (select c_id from class where c_name='php002');

-- 连接查询的特点:
-- 查询班级为php002班的所有的学生,及php002班级的信息
select * from stu,class where c_id=c_id;

-- 显式内连接
select * from stu inner join class where stu.c_id=class.c_id;

-- 顾客表
create table customer(
c_id int unsigned primary key auto_increment,
c_name varchar(32),
c_mobile char(11)
)charset utf8;

-- 插入测试数据
insert into customer values(10,'zhangsan','12312312312');
insert into customer values(11,'lisi','12312312323');
insert into customer values(12,'wangwu','12312312334');


-- 消费表
create table trans(
id int unsigned primary key auto_increment,
c_id int unsigned,
balance decimal(10,3)
)charset utf8;

-- 插入测试数据
insert into trans values(1,10,1000);
insert into trans values(2,11,1100);

-- 查询已在店消费的用户及消费信息
select * from customer c inner join trans t where c.c_id = t.c_id;


-- 查询所有的用户及消费信息
select * from customer c left join trans t on c.c_id = t.c_id;

-- 自然左外连接
select * from customer c natural left join trans t;


-- 自然右外连接
select * from customer c natural right join trans t;


-- 查询所有的用户及消费信息
select * from customer c left join trans t on c.c_id = t.c_id;
select * from customer c left join trans t using(c.c_id);
-- 创建视图
create or replace view v_goods as select id,brand,goodsname,price from goods;

-- 查询视图
select * from v_goods group by brand;

-- 重新创建视图
create or replace view v_goods as select id,brand,goodsname,channel,price from goods;


-- 多表视图
create view v_stu as select * from stu,class where stu.c_id = class.c_id;

-- 创建视图
create algorithm=temptable view v_temptable as select * from goods order by price desc;

-- 查询视图
select *,max(price) from v_temptable group by brand;

-- 创建视图
create algorithm=merge view v_merge as select * from goods order by price desc;

-- 查询视图
select *,max(price) from v_merge group by brand;


select * from goods order by price desc;
select *,max(price) from v_merge group by brand;


-- 触发器
-- 创建商品名
drop table if exists goods;
create table goods(
id int unsigned primary key auto_increment,
brand char(16),
goodsName char(16),
channel char(8),
price decimal(10,2),
inventory smallint unsigned
);
insert into goods values(default,'huawei','mate','3G',2300,30);
insert into goods values(default,'apple','6plus','4G',4500,43);
insert into goods values(default,'apple','6splus','4G',5100,20);
insert into goods values(default,'samsung','galaxy6','4G',3800,28);
insert into goods values(default,'huawei',null,'3G',3200,15);
insert into goods values(default,'apple','5c','3G',1800,38);
insert into goods values(default,'huawei','mate8','4G',3500,51);
insert into goods values(default,'huawei','p8Max','4G',2700,27);
insert into goods values(default,'apple','5s','3G',2800,38);
insert into goods values(default,'samsung','galaxy5','3G',3400,33);


-- 创建订单表
create table orderlist(
o_id int unsigned primary key auto_increment,
g_id int unsigned,
g_num int unsigned,
u_id int unsigned
)charset utf8;



-- 创建触 发器
-- 第1步:更改客户端命令结符
delimiter //

-- 第2步:定义触发器
create trigger tri_order after insert on orderlist for each row
begin
	
end;
//
-- 第3步:还原客户端命令结束符
delimiter ;


-- 产生订单
insert into orderlist value(default,2,10,19);


update goods set inventory=inventory-new.g_num where id=new.g_id;


-- 银行表
drop table if exists bank;
create table bank(
id int unsigned primary key auto_increment,
name varchar(32),
balance decimal(10,2)
)charset utf8;

-- 插入测试数据
insert into bank value(default,'zhangsan',1000),(default,'lisi',1000);


-- 1、开启事务
start transaction;

-- 2、执行事务中的sql

-- sql1
update bank set balance=balance-100 where id=1;

-- sql2
update banak set balance =balance+100 where id=2;

-- 3、根据执行结果进行提交
rollback;


start transaction;
-- 需求,向商品表中添加一个商品
insert into goods values(default,'huawei','mate 10','4G',5000,10);

-- 回滚点
-- 1、开启事务
start transaction;

-- 2、执行事务中的sql
-- sql1
update bank set balance=balance-100 where id=1;

-- 定义回滚点
savepoint sp1;

-- sql2
update banak set balance =balance+100 where id=2;

-- 回滚
rollback to sp1;



-- cmdA
start transaction;
update goods set inventory = inventory+10 where id=7;

-- cmdB
start transaction;
update goods set inventory = inventory + 10 where id = 11;




-- 定义函数实现两个数相加
-- 1、更改客户端的命令结束符
delimiter //

-- 2、定义函数
create function getSum(v1 int,v2 int) returns int
begin
	return v1+v2;
end;

//

-- 3、还原客户端的命令结束符
delimiter ;

-- 函数的调用
set @v1 = 10;
set @v2 = 20;

select getSum(@v1,@V2);
select getSum(100,200);


set @v = 100;


-- 1、更改客户端的命令结束符
delimiter //

-- 2、定义函数
create function getV() returns int
begin
	return @v + 100;
end;

//

-- 3、还原客户端的命令结束符
delimiter ;



-- 1、更改客户端的命令结束符
delimiter //

-- 2、定义函数
create function setG() returns char
begin
	set @G= 300;
	return '';
end;

//

-- 3、还原客户端的命令结束符
delimiter ;







-- 1、更改客户端的命令结束符
delimiter //

-- 2、定义函数
create function setDeclare() returns char
begin
	declare d int default 300;
	return '';
end;

//

-- 3、还原客户端的命令结束符
delimiter ;



-- if分支
delimiter //
create function judge(v int) returns char(10)
begin
if v>0 then
return '正数';
else
return '负数';
end if;
end;

//
delimiter ;




-- while分支
delimiter //
create function test(v int) returns int
begin
	declare total int default 0;
	declare i int default 0;

	while i<=v do
		set total = i+total;
		set i=i+1;
	end while;
	return total;
end;
//
delimiter ;




-- 1、更改客户端的命令结束符
delimiter //

-- 2、定义函数
create procedure pro(in v1 int,out v2 int,inout v3 int)
begin
	select v1,v2,v3;
	set v1 = 100;
	set v2 = 200;
	set v3 = 300;
end;

//

-- 3、还原客户端的命令结束符
delimiter ;

-- 定义实参
set @a=10,@b=20,@c=30;

-- 调用存储过程
call pro(@a,@b,@c);

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

孑然R

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值