-- 查看数据库
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);
扫一扫
06-15
2073
2073
06-16
2166
2166
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
