sql语句（非常全）

1001001-- 双中划线+空格：注释（单行注释），也可以使用#号


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


-- 创建关键字数据库
create database database charset utf8;


-- 使用反引号
create database `database` charset utf8;


-- 创建中文数据库
create database 中国 charset utf8;
create database `中国` charset utf8;


-- 解决方案：告诉服务器当前中文的字符集是什么
set names gbk;


-- 查看所有数据库
show databases;


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


-- 查看以information_开头的数据库（_需要被转义）
show databases like 'information\_%';
show databases like 'information_%'; -- 相当于information%


-- 查看数据库创建语句
show create database mydb;
show create database `database`; -- 关键字需要使用反引号


-- 修改数据库informationtest的字符集
alter database informationtest charset GBK;


-- 删除数据库
drop database informationtest;


-- 创建表
-- 显式地将student表放到mydb数据库下
create table if not exists mydb.student(
name varchar(10),
gender varchar(10),
number varchar(10),
age int
)charset utf8;


-- 创建数据表
-- 进入数据库
use mydb;


-- 创建表
create table class(
name varchar(10),
room varchar(10)
)charset utf8;


-- 查看所有表
show tables;


-- 查看以s结尾的表
show tables like '%s';


-- 查看表创建语句
show create table student\g -- \g 等价于 ;
show create table student\G -- 将查到的结构旋转90度变成纵向


-- 查看表结构
desc class;
describe class;
show columns from class;


-- 重命名表：student -> my_student
rename table student to my_student;


-- 修改表选项：字符集
alter table my_student charset=GBK;


-- 给学生表增加ID字段，放到第一个位置（用first来表示）
alter table my_student 
add column id int 
first;


-- 将学生表中的number学号字段变成固定长度，且放到第二位（id之后）
alter table my_student modify number char(10) after id;


-- 修改学生表中的gender字段为sex
alter table my_student change gender sex varchar(10);


-- 删除学生表中的age年龄字段
alter table my_student drop age;


-- 删除数据表
drop table class;


-- 插入数据
insert into my_student values
(1,'bc20170001','Jim','male'),
(2,'bc20170002','Lily','female');


-- 插入数据：指定字段列表
insert into my_student(number,sex,name,id) values
('bc20170003','male','Tom',3),
('bc20170004','female','Lucy',4);


-- 查看所有数据
select * from my_student;


-- 查看指定字段、指定条件的数据
-- 查看满足id为1的学生信息
select id,number,sex,name from my_student where id=1;


-- 更新数据
update my_student set sex='female' where name='Jim';


-- 删除数据
delete from my_student where sex='male';


-- 插入数据（带中文）
insert into my_student values
(5,'bc20170005','小马哥','男');


-- 查看所有字符集
show character set;


-- 查看服务器默认的对外处理的字符集
show variables like 'character_set%';


-- 修改服务器认为的客户端数据的字符集为GBK
set character_set_client=gbk;


-- 修改服务器给定数据的字符集为GBK
set character_set_results=gbk;


-- 快捷设置字符集
set names gbk;


-- 查看所有校对集
show collation;


-- 创建表使用不同的校对集
create table my_collate_bin(
name char(1)
)charset utf8 collate utf8_bin;


create table my_collate_ci(
name char(1)
)charset utf8 collate utf8_general_ci;


-- 插入数据
insert into my_collate_bin values
('a'),('A'),('B'),('b');


insert into my_collate_ci values
('a'),('A'),('B'),('b');


-- 排序查找
select * from my_collate_bin order by name;
select * from my_collate_ci order by name;


-- 有数据后修改校对集
alter table my_collate_ci collate=utf8_bin;
alter table my_collate_ci collate=utf8_general_ci;


-- 创建整型表
create table my_int(
int_1 tinyint,
int_2 smallint,
int_3 int,
int_4 bigint
)charset utf8;


-- 插入数据
insert into my_int values(100,100,100,100); -- 有效数据
insert into my_int values('a','b','199','f'); -- 无效数据：类型限定
insert into my_int values(255,10000,100000,1000000); -- 错误：超出范围


-- 给表增加一个无符号类型
alter table my_int add int_5 tinyint unsigned; -- 无符号类型


-- 插入数据
insert into my_int values(127,10000,100000,1000000,255);


-- 指定显示宽度为1
alter table my_int add int_6 tinyint(1) unsigned;


-- 插入数据
insert into my_int values(127,0,0,0,255,255);


-- 显示宽度为2，0填充
alter table my_int add int_7 tinyint(2) zerofill;


-- 插入数据
insert into my_int values(1,1,1,1,1,1,1);
insert into my_int values(100,100,100,100,100,100,100);


-- 浮点数表
create table my_float(
f1 float,
f2 float(10,2), -- 10位在精度范围之外
f3 float(6,2) -- 6位在精度范围之内
)charset utf8;


-- 插入数据
insert into my_float values(1000.10,1000.10,1000.10);
insert into my_float values(1234567890,12345678.90,1234.56);
-- 3*10^38  3.01*10^7
insert into my_float values(3e38,3.01e7,1234.56);
-- 后两个是最大值
insert into my_float values(9999999999,99999999.99,9999.99);


-- 超出长度插入数据
-- 小数部分可以超出长度
insert into my_float values(123456,1234.12345678,123.9876543);
-- 最后一个整数部分超出
insert into my_float values(123456,1234.12,12345.56);


-- 创建定点数表
create table my_decimal(
f1 float(10,2),
d1 decimal(10,2)
)charset utf8;


-- 插入数据
insert into my_decimal values(12345678.90,12345678.90); -- 有效数据
insert into my_decimal values(1234.123456,1234.123456); -- 小数部分可以超出


-- 查看警告
show warnings;


-- 插入数据
insert into my_decimal values(99999999.99,99999999.99); -- 没有问题
insert into my_decimal values(99999999.99,99999999.999);-- 进位超出范围


-- 创建时间日期表
create table my_date(
d1 datetime,
d2 date,
d3 time,
d4 timestamp,
d5 year
)charset utf8;


-- 插入数据
insert into my_date values(
'2017-10-26 10:30:36',
'2017-10-26',
'10:30:36',
'2017-10-26 10:30:36',
2017
);


-- 时间使用负数
insert into my_date values(
'2017-10-26 10:30:36',
'2017-10-26',
'-10:30:36',
'2017-10-26 10:30:36',
2017
);


insert into my_date values(
'2017-10-26 10:30:36',
'2017-10-26',
'-210:30:36', -- 表示从过去到现在的时间段
'2017-10-26 10:30:36',
2017
);


insert into my_date values(
'2017-10-26 10:30:36',
'2017-10-26',
'-2 10:30:36', -- -2表示过去2天，即48小时，48+10 -> -58
'2017-10-26 10:30:36',
2017
);


-- year可以使用2位或者4位
insert into my_date values(
'2017-10-26 10:30:36',
'2017-10-26',
'10:30:36',
'2017-10-26 10:30:36',
69
);


insert into my_date values(
'2017-10-26 10:30:36',
'2017-10-26',
'10:30:36',
'2017-10-26 10:30:36',
70
);


-- timestamp：修改记录
update my_date set d1='2017-10-26 10:43:45' where d5=2069;


-- 查看时间戳
select unix_timestamp();


-- 创建枚举表
create table my_enum(
gender enum('男','女','保密')
)charset utf8;


-- 插入数据
insert into my_enum values('男'),('保密'); -- 有效数据
insert into my_enum values('male'); -- 错误数据，没有该元素


-- 将字段结果取出来进行+0运算
select gender+0,gender from my_enum;


-- 数值插入枚举元素
insert into my_enum values(1),(2);


-- 创建集合表
create table my_set(
hobby set('篮球','足球','乒乓球','羽毛球','排球','台球','网球','棒球')
--          0      1       0          0     0       1      1      0
--          1      1       0          0     0       0      0      0
)charset utf8;


-- 插入数据
insert into my_set values('足球,台球,网球');
insert into my_set values(3);


-- 查看集合数据
select hobby+0,hobby from my_set;
-- 98转成二进制64+32+2=01100010
-- 3转成二进制2+1=00000011


-- 数值插入集合元素
insert into my_set values(255);


-- 颠倒元素出现的顺序
insert into my_set values('网球,台球,足球');


-- 求出varchar在utf8和GBK下的实际最大值
create table my_utf8(
name varchar(21844) -- 21844*3+2=65532+2=65534
)charset utf8;


create table my_gbk(
name varchar(32766) -- 32766*2+2=65532+2=65534
)charset gbk;


create table my_utf81(
age tinyint,
name varchar(21844)
)charset utf8;


create table my_gbk1(
age tinyint,
name varchar(32766)
)charset gbk;


-- 释放NULL
create table my_utf82(
age tinyint not null,
name varchar(21844) not null
)charset utf8;


create table my_gbk1(
age tinyint not null,
name varchar(32766) not null
)charset gbk;


-- text占用十个字节长度
create table my_text(
name varchar(21841) not null,
content text not null
)charset utf8;


-- 创建班级表
create table my_class(
name varchar(20) not null,
room varchar(20) null -- 代表允许为空，不写默认就是允许为空
)charset utf8;


-- 创建表
create table my_teacher(
name varchar(20) not null comment '姓名',
money decimal(10,2) not null comment '工资'
)charset utf8;


-- 默认值
create table my_default(
name varchar(20) not null,
age tinyint unsigned default 0,
gender enum('男','女','保密') default '男'
)charset utf8;


-- 插入数据
insert into my_default (name) values('阿飞');
insert into my_default values('男闺蜜',18,default);


-- 增加主键
create table my_pri1(
name varchar(20) not null comment '姓名',
number char(10) primary key comment '学号：bc2017+0001，不能重复'
)charset utf8;


-- 复合主键
create table my_pri2(
number char(10) comment '学号：bc2017+0001',
course char(10) comment '课程代码：bc2589+0001',
score tinyint unsigned default 60 comment '成绩',
-- 增加主键限制：学号和课程号应该是对应的，具有唯一性
primary key(number,course)
)charset utf8;


-- 追加主键
create table my_pri3(
course char(10) not null comment '课程代码：bc2589+0001',
name varchar(10) not null comment '课程名称'
)charset utf8;


alter table my_pri3 modify course char(10) 
primary key comment '课程代码：bc2589+0001';


alter table my_pri3 add primary key(course);


-- 向pri1、2表插入数据
insert into my_pri1 values
('古天乐','bc20170001'),
('蔡康永','bc20170002');


insert into my_pri2 values
('bc20170001','bc25890001',90),
('bc20170001','bc25890002',85),
('bc20170002','bc25890001',92);


-- 主键冲突（重复）
insert into my_pri1 values
('刘涛','bc20170002'); -- 不可以：主键冲突
insert into my_pri2 values
('bc20170001','bc25890001',100); -- 不可以，冲突


-- 删除主键
alter table my_pri3 drop primary key;


-- 自增长
create table my_auto(
id int primary key auto_increment comment '自动增长',
name varchar(10) not null
)charset utf8;


-- 触发自增长
insert into my_auto(name) values('邓丽君');
insert into my_auto values(null,'成龙');
insert into my_auto values(default,'吴绮莉');


-- 指定数据
insert into my_auto values(6,'黄晓明');
insert into my_auto values(null,'杨颖');


-- 修改表选项的值
alter table my_auto auto_increment=4; -- 向下修改（改小），不生效
alter table my_auto auto_increment=10; -- 向上修改（改大）


-- 查看自增长变量
show variables like 'auto_increment%';


-- 修改自增长步长
set auto_increment_increment=5; -- 一次自增5


-- 插入记录：使用自增长
insert into my_auto values(null,'杨紫');
insert into my_auto values(null,'张一山');


-- 删除自增长
-- 错误：主键理论上是单独存在的
alter table my_auto modify id int primary key;
-- 有主键的时候，千万不要再加主键
alter table my_auto modify id int;


-- 唯一键
create table my_unique1(
number char(10) unique comment '学号：唯一，允许为空',
name varchar(20) not null
)charset utf8;


create table my_unique2(
number char(10) not null comment '学号',
name varchar(20) not null,
-- 增加唯一键
unique key(number)
)charset utf8;


create table my_unique3(
id int primary key auto_increment,
number char(10) not null,
name varchar(20) not null
)charset utf8;


-- 追加唯一键
alter table my_unique3 add unique key(number);


-- 插入数据
insert into my_unique1 values
(null,'大雄'),
('bc20070001','胖虎'),
(null,'小静');


-- 唯一键冲突
insert into my_unique1 values
('bc20070001','哆啦A梦');


-- 删除唯一键
alter table my_unique3 drop index number;


-- 给my_class表增加主键
alter table my_class add primary key(name);


-- 插入数据
insert into my_class values('Java1707B','A204');
insert into my_class values('Java1707B','B510');
insert into my_class values('Java1708','A203');


-- 主键冲突：更新
insert into my_class values('Java1707B','B510')
-- 冲突处理
on duplicate key update
-- 更新教室
room='B510';


-- 主键冲突：替换
replace into my_class values('Java1708','B409');
replace into my_class values('Java1710','C110');


-- 复制创建表
create table my_copy like my_gbk;


-- 蠕虫复制
insert into my_copy select * from my_collate_bin;
insert into my_copy select * from my_copy;


-- 更新部分a变成c
update my_copy set name='c' where name='a' limit 3;


-- 删除数据：限制记录数为10
delete from my_copy where name='b' limit 10;


-- 给学生表增加主键
alter table my_student modify id int primary key auto_increment;


-- 清空表，重置自增长
truncate my_student;


-- select选项
select * from my_copy;
select all * from my_copy;


-- 去重
select distinct * from my_copy;


-- 向学生表插入数据
insert into my_student values
(null,'bc20170001','张三','男'),
(null,'bc20170002','李四','男'),
(null,'bc20170003','王五','女'),
(null,'bc20170004','赵六','男'),
(null,'bc20170005','小明','男');


-- 字段别名
select 
id,
number as 学号,
name as 姓名,
sex 性别 from my_student;


-- 多表数据源
select * from my_student,my_class;


-- 子查询
select * from (select * from my_student) as s;


-- 增加age年龄和height身高字段
alter table my_student add age tinyint unsigned;
alter table my_student add height tinyint unsigned;


-- 增加字段值：rand取得一个0-1之间的随机数，floor向下取整
update my_student set 
age=floor(rand()*20+20), -- 年龄在20-40
height=floor(rand()*20+170); -- 身高在170-190


-- 找出学生id为1、3、5的学生
select * from my_student where id=1 || id=3 || id=5; -- 逻辑判断
select * from my_student where id in(1,3,5); -- 在集合中


-- 找身高在175到180之间的学生
select * from my_student where height>=175 and height<=180;
select * from my_student where height between 175 and 180;
select * from my_student where height between 180 and 175;
select * from my_student where 1; -- 所有条件都满足


-- 根据性别分组
select * from my_student group by sex;


-- 分组统计：身高高矮、平均年龄、总年龄
select sex,count(*),max(height),min(height),avg(age),sum(age) 
from my_student
group by sex;


select sex,count(*),count(age),max(height),min(height),avg(age),sum(age) 
from my_student
group by sex;


select sex,count(*),count(age),max(height),min(height),avg(age),sum(age) 
from my_student
group by sex desc;


-- 修改id为4的记录，把年龄设置为NULL
update my_student set age=null where id=4;


-- 修改id为1的记录，把性别设置为女
update my_student set sex='女' where id=1;


-- nan
-- nv


-- 给班级表加主键
alter table my_class add id int primary key auto_increment;


alter table my_class drop primary key;


alter table my_student add c_id int;


update my_student set c_id=ceil(rand()*3);


insert into my_student values(6,'bc20170006','小芳','女',18,160,2);


update my_student set c_id=2 where id=5;


-- 多字段分组：先班级，后男女
select c_id,sex,count(*) from my_student group by c_id,sex; -- 多字段排序


select c_id,sex,count(*),group_concat(name) 
from my_student group by c_id,sex;


-- 统计
select c_id,count(*) from my_student group by c_id;


-- 回溯统计
select c_id,count(*) from my_student group by c_id with rollup;


-- 多字段分组回溯统计
select c_id,sex,count(*),group_concat(name) 
from my_student group by c_id,sex;


select c_id,sex,count(*),group_concat(name) 
from my_student group by c_id,sex with rollup;


-- 求出所有班级人数大于等于2的学生人数
select c_id,count(*) from my_student 
group by c_id having count(*)>=2;


-- 错误的
select c_id,count(*) from my_student 
where count(*)>=2
group by c_id;




select c_id,count(*) as total from my_student 
group by c_id having total>=2;


-- 错误的
select c_id,count(*) as total from my_student 
where total>=2 group by c_id;


-- having子句进行条件查询
select name as 名字,number as 学号 from my_student
having 名字 like '张%';


select name as 名字,number as 学号 from my_student
where 名字 like '张%';


-- 排序
select * from my_student group by c_id;
select * from my_student order by c_id;


-- 多字段排序：先班级排序，后性别排序
select * from my_student order by c_id asc,sex desc;


-- 查询学生：前两个
select * from my_student limit 2;
select * from my_student limit 0,2; -- 记录数是从0开始编号
select * from my_student limit 2,2;
select * from my_student limit 5,2;


-- 更改ID为班级表的第一列
alter table my_class change id id int first;


-- 交叉连接，my_student cross join my_class是数据源
select * from my_student cross join my_class;


-- 内连接
select * from my_student inner join my_class 
on my_student.c_id=my_class.id;


select * from my_student inner join my_class 
on c_id=my_class.id;


select * from my_student inner join my_class 
on c_id=id; -- 两张表都有id字段


select s.*,c.name as c_name, c.room -- 字段别名
from my_student as s inner join my_class as c -- 表别名
on s.c_id=c.id;


-- where代替on
select s.*,c.name as c_name, c.room -- 字段别名
from my_student as s inner join my_class as c -- 表别名
where s.c_id=c.id;


-- 把学生表id为5的记录的c_id设置为NULL
update my_student set c_id=null where id=5;


-- 左连接
select s.*,c.name as c_name, c.room
-- 左表为主表，最终记录数至少不少于左表已有的记录数
from my_student as s left join my_class as c
on s.c_id=c.id;


-- 右连接
select s.*,c.name as c_name, c.room
-- 右表为主表，最终记录数至少不少于右表已有的记录数
from my_student as s right join my_class as c
on s.c_id=c.id;


-- 左连接与右连接互转
select s.*,c.name as c_name, c.room
from my_class as c right join my_student as s
on s.c_id=c.id;


-- 自然内连接
select * from my_student natural join my_class;


-- 修改班级表的name字段名为c_name
alter table my_class change name c_name varchar(20) not null;


-- 自然左外连接
select * from my_student natural left join my_class;


-- 外连接模拟自然外连接：using
select * from my_student left join my_class using(id);


-- 创建外键
create table my_foreign1(
id int primary key auto_increment,
name varchar(20) not null comment '学生姓名',
c_id int comment '班级id', -- 普通字段
-- 增加外键
foreign key(c_id) references my_class(id)
)charset utf8;


-- 创建表
create table my_foreign2(
id int primary key auto_increment,
name varchar(20) not null comment '学生姓名',
c_id int comment '班级id' -- 普通字段
)charset utf8;


-- 追加外键
alter table my_foreign2 add
-- 指定外键名
constraint student_class_1
-- 指定外键的字段
foreign key(c_id)
-- 引用父表主键
references my_class(id);


-- 删除
alter table my_foreign1 drop foreign key my_foreign1_ibfk_1;


-- 插入数据 外键字段在父表中不存在
insert into my_foreign2 values(null,'郭富城',4);  -- 没有4班


insert into my_foreign2 values(null,'项羽',1);
insert into my_foreign2 values(null,'刘邦',2);
insert into my_foreign2 values(null,'韩信',2);


-- 更新父表记录
update my_class set id=4 where id=1; -- 失败id=1的记录已经被学生引用了
update my_class set id=4 where id=3; -- 可以 没有引用


-- 插入数据
insert into my_foreign1 values(null,'马超',3);


-- 增加外键
alter table my_foreign1 add foreign key(c_id) references my_class(id);


-- 创建外键 ：指定模式：删除置空。更新级联
create table my_foreign3(
id int primary key auto_increment,
name varchar(20) not null,
c_id int,
-- 增加外键
foreign key(c_id)
-- 引用父表
references my_class(id)
-- 指定删除模式
on delete set null
-- 指定更新模式
on update cascade
)charset utf8;


-- 插入数据
insert into my_foreign3 values
(null,'刘备',1),
(null,'曹操',1),
(null,'孙权',1),
(null,'诸葛亮',2),
(null,'周瑜',2);


-- 解除my_foreign2表的外键
alter table my_foreign2 drop foreign key student_class_1;


-- 更新父表主键
update my_class set id=3 where id=1;


-- 删除父表主键
delete from my_class where id=2;
 
-- 联合查询·
select * from my_class
union     -- 默认去重
select * from my_class;


select * from my_class
union all    -- 不去重
select * from my_class;


select name,room,id from my_class
union all    -- 不去重
select name,number,id from my_class;


-- 需求 男按身高升序，女降序
(select * from my_student where sex='男' order by height asc limit 9999999)
union
(select * from my_student where sex='女' order by height desc limit 9999999);


-- 标量子查询
select * from my_student where c_id=
-- id一定只有一个值（一行一列）
(select id from my_class where c_name='java1707b');


-- 列子查询
select * from my_student where c_id in(select id from my_class);


insert into my_class values('java1708','A203',1);


-- any,some,all
select * from my_student where c_id=any(select id from my_class);
select * from my_student where c_id=some(select id from my_class);
select * from my_student where c_id=all(select id from my_class);


-- 所有结果 （null除外）
select * from my_student where c_id!=any(select id from my_class);
-- 所有结果 （null除外）
select * from my_student where c_id!=some(select id from my_class);
--2 null除外
select * from my_student where c_id!=all(select id from my_class);


select * from my_student where
age=(select max(age) from my_student)
and 
height=(select max(height) from my_student);


-- 行子查询
select * from my_student where (age,height)=       -- (age,height)行元素
(select max(age),max(height) from my_student);


select * from my_student order by age desc,height desc limit 1;


-- 婊子查询
select * from my_student order by height desc;
select * from my_student group by c_id; -- 每个班选出第一个学生


select * from
(select * from my_student order by height desc) as student  -- 别名
group by c_id order by height desc;


-- 
select exists(select * from my_student where id=100);


-- exists子查询
select * from my_student where
exists(select * from my_class where id=1); -- 是否成立 -- exists是否存在


select * from my_student where
exists(select * from my_class where id=2);


-- 视图：单表加多表
create view my_v1 as
select * from my_student;


create view my_v2 as
select * from my_class;


create view my_v3 as
select * from my_student as s
left join my_class as c on s.c_id=c.id; -- name重复


-- 多表视图
create view my_v3 as
select s.*,c.name as c_name,c.room from my_student as s
left join my_class as c on s.c_id=c.id;


-- 查看视图创建语句
show create view my_v3\G


-- 视图使用
select * from my_v1;
select * from my_v2;
select * from my_v3;


-- 修改视图
alter view my_v1 as
select id,name,age,sex,height,c_id from my_student;


-- 删除视图
drop view my_v4;


-- 多表视图不能插入数据
insert into my_v3 values
(null,'bc20170007','张三丰','男',150,180,1,'Python1711','A208');


-- 将学生表的学号字段设置不允许为空
alter table my_student modify number char(10) not null unique;


-- 单表视图插入数据:视图不包含所有不允许为空的字段
insert into my_v3 values
(null,'张三丰',150，'男',180,1);


-- 单表视图插入数据
insert into my_v2 values('python0711','A204',2);


-- 多表视图删除数据
delete from my_v3 where id=1;


-- 单表视图删除数据
delete from my_v2 where id=4;


-- 多表视图更新数据
update my_v3 set c_id=2 where id=5;


-- 视图：age字段限制更新
create view my_v4 as
select * from my_student where age>30 with check option; -- 限制更新
-- 表示视图的数据来源都是年龄大于30岁的，是由where age>30 决定的
-- with check option 决定通过视图更新的时候，
-- 不能将已经得到的数据age>30的改成<30的


-- 将视图可以查到的数据，改成年龄小于30
update my_v4 set age=28 where id=1;


-- 可以修改数据让视图可以查到:可以改，但是无效
update my_v4 set age=32 where id=2;


-- 获取所有班级中最高的一个学生
create view my_v5 as 
select * from my_student order by height desc;


select * from my_v5 group by c_id;


-- 指定算法为临时表
create algorithm=temptable view my_v6 as 
select * from my_student order by height desc;


select * from my_v6 group by c_id;


-- 创建myisam表
create table my_myisam(
id int
)charset utf8 engine=myisam;


-- 向my_myisam表插入几条记录
insert into my_myisam values(1),(2),(3);


-- 单表数据备份
select * into outfile 'D:/java1/student.txt' from my_student;


select * into outfile 'D:/java1/class1.txt' from my_class;




 -- 指定备份处理方式
 select * into  outfile
 'D:/java1/class1.txt'
 -- 字段处理
 fields
 enclosed by '"' -- 数据使用双引号包裹
 terminated by '|'  -- 使用竖线分割字段数据
 -- 行处理
 lines
 starting by 'START:'
 from my_class;




 -- 还原数据
 load data infile
 'D:/java1/class1.txt'
 into table my_class
 -- 字段处理
 fields
 enclosed by '"' -- 数据使用双引号包裹
 terminated by '|'  -- 使用竖线分割字段数据
 -- 行处理
 lines
 starting by 'START:';


 -- sql备份
 mysqldump -uroot -p密码 mydb my_student > D:/java1/class1.sql


 -- 整库备份
 mysqldump -uroot -p密码 mydb > D:/java1/mydb.sql


 -- 还原数据：mysql客户端还原
 mysql -uroot -p密码 mydb < D:/java1/class1.sql


 -- sql指令还原备份
 source D:/java1/class1.sql


 -- 创建一个账户表
 create table my_account(
 id int primary key auto_increment,
 number char(16) not null unique comment '账户',
 name varchar(20) not null,
 money decimal(10,2) default 0.0 comment '账户余额'    -- default 默认值
 )charset utf8;


 -- 插入数据
 insert into my_account values
 (null,'0000000000000001','张三',1000),
 (null,'0000000000000002','李四',2000);


-- 张三转账1000元给李四
update my_account set money=money-1000 where id=5;


-- 事物安全
-- 开启事务
start transaction;


-- 事物操作1.李四账户减少
update my_account set money=money-0.1 where id=6;


-- 事物操作2.张三账户增加
update my_account set money=money+0.1 where id=5;


-- 提交事务  rollback;
commit;  


-- 回滚点操作
-- 开启事务
start transaction;


-- 事务处理1.张三发工资了，加钱
update my_account set money=money+10000 where id=5;


-- 设置回滚点
savepoint sp1;


-- 银行扣税
update my_account set money=money-10000*0.05 where id=6; -- 错误的


-- 回滚到回滚点
rollback to sp1;


-- 继续操作
update my_account set money=money-10000*0.05 where id=5;


-- 查看结果
select * from my_account;


-- 提交结果
commit;


-- 显示系统变量autocommit（模糊查询）
show variables like 'autocommit';


-- 关闭事物自动提交
set autocommit=0;


-- 开启事务自动提交
set autocommit=1;


-- 锁机制
start transaction;
update my_account set money=money+500 where name='张三';


-- 查看所有系统变量
show variables;


-- 查看系统变量值
select @@version,@@autocommit,@@auto_increment_offset,@@character_set_results;


-- 修改会话级别变量
set autocommit=0;


-- 修改全局级别变量
set global autocommit=0;


-- 自定义变量
set @name='张三';


-- 查看变量
select @name;


-- 定义变量
set @age:=18; -- 标准的


-- 从表中获取数据赋值给变量
select @name:=name,name from my_student;


select name,age from my_student where id=2 into @name,@age;


-- 创建表
create table my_goods(
id int primary key auto_increment,
name varchar(20) not null,
price decimal(10,2) default 1,
inv int comment'库存'
)charset utf8;


insert into my_goods values
(null,'iphone8',5888,100),
(null,'mate10保时捷',10900,100);


create table my_order(
id int primary key auto_increment,
g_id int not null comment'商品id',
g_number int comment'商品数量'
)charset utf8;


-- 触发器:订单生成一个，商品库存减少一个
-- 临时修改语句结束符
delimiter $$
create trigger after_order after insert on my_order for each row
begin
-- 触发器内容开始了 新增一条订单 old没有 new代表新的订单记录
update my_goods set inv=inv-new.g_number where id=new.g_id;
-- old是订单 new没有
-- after delete on 
-- delete my_goods set inv=inv+old.g_number where id=new.g_id;
end
-- 结束
$$


-- 修改临时语句结束符
delimiter ; -- 记得有空格


-- 查看所有触发器
show triggers\G


-- 查看触发器创建语句
show create trigger after_order\G


select * from information_schema.triggers\G


-- 插入订单
insert into my_order values(null,1,2);


-- 删除触发器
drop trigger after_order;


-- 触发器：订单生成之前要判断库存是否满足
delimiter %%
create trigger before_order before insert on my_order for each row
begin
     -- 判断商品库存是否够
     -- 获取商品库存：商品库存在表中
     select inv from my_goods where id=new.g_id into @inv;


     -- 比较库存
     if @inv<new.g_number then
               -- 库存不够:触发器没有提供一个能够阻止事件发生的能力
      insert into xxx values(xxx);
     end if;
end
%%
-- 别忘了把语句结束符改回来
delimiter ;     -- 记得空格


-- 插入一个订单
insert into my_order values(null,1,1000);


-- 定义两个变量
set @cn='世界你好';
set @en='hello world';


-- 字符串截取
select substring(@cn,1,1);
select substring(@en,1,1);


-- 字符串的长度
select char_length(@cn),char_length(@en),length(@cn),length(@en);


-- 字符串寻找
select instr(@cn,'界'),instr(@en,'ll'),instr(@cn,'拜拜');


-- 字符串填充
select lpad(@cn,20,'欢迎'),lpad(@en,20,'hello');


-- 字符串替换
select insert(@en,3,3,'y'),@en;-- 这个insert不是插入


-- 字符串比较
set @f='hello';
set @s='hey';  -- 一个一个比  y比l大    所以hey大
set @t='HEY';


select strcmp(@f,@s),strcmp(@s,@t),strcmp(@s,@f);


-- 创建函数
create function display1() returns int 
return 100;


-- 调用函数
select display1();


-- 查看所有函数
show function status\G


-- 查看函数创建语句
show create function display1\G  -- 这个不用括号


-- 删除函数
drop function display1;


-- 做函数；计算1到指定数之间的和
delimiter $$
create function display2(int_1 int) returns int -- 根java相反   先名字  后属性     returns记得s
begin
      -- 定义条件变量
      set @i=1;     -- @定义的变量是全局变量
      set @res=0;   -- 保存结果


      -- 循环求和
      while @i<=int_1 do
            -- 求和:任何变量的修改必须使用set关键字
   -- mysql中没有+=   没有++/-- 
   set @res=@res+@i;


   -- 修改循环的变量
   set @i=@i+1;
      end while;


      -- 返回值
      return @res;
end
$$  -- 函数结束
delimiter ; -- 修改回；


-- 调用函数
select display2(10);


-- 求和:1到指定数之间的和，要求5的倍数不加
delimiter $$
create function display3(int_1 int) returns int
begin
    -- 声明变量：循环变量 结果变量
    declare i int default 1;
    declare res int default 0;  -- 定义局部变量可以有属性


    -- 循环判断
    mywhile:while i<=int_1 do
           -- 相加:判断
           if i%5=0 then
         -- 修改循环条件
 set i=i+1;    -- 不写这个将会死循环  一直循环5
                  -- 不符合条件：循环重新来过
                  iterate mywhile;
           end if;


  -- 相加
  set res=res+i;


  -- 改变循环变量
  set i=i+1;
    end while;


    -- 返回结果
    return res;


end
$$
delimiter ;


-- 创建存储过程
create procedure pro1()
-- 假设过程中需要显示数据，就用select
select * from my_student;


-- 查看过程
show procedure status like 'pro%'\G


-- 查看过程创建语句
show create procedure pro1\G


-- 调用过程
call pro1();


-- 删除过程
drop procedure pro1;






-- 过程参数
delimiter $$
create procedure pro2(in int_1 int, out int_2 int, inout int_3 int)
begin
     -- 查看三个变量 (都是局部变量)
     select int_1,int_2,int_3;    -- int_2的值一定是null
end
$$
delimiter ;


-- 设置变量
set @int_1=1;
set @int_2=2;
set @int_3=3;


select @int_1,@int_2,@int_3;
call pro3(@int_1,@int_2,@int_3);
select @int_1,@int_2,@int_3;






delimiter $$
create procedure pro3(in int_1 int, out int_2 int, inout int_3 int)
begin
     -- 查看三个变量 (都是局部变量)
     select int_1,int_2,int_3;    -- int_2的值一定是null
     
     -- 修改局部变量
     set int_1=10;
     set int_2=100;
     set int_3=1000;


     -- 查看局部变量
     select int_1,int_2,int_3;


     -- 查看全局变量
     select @int_1,@int_2,@int_3;
     
     -- 修改全局变量
     set @int_1='a';
     set @int_2='b';
     set @int_3='c';


     -- 查看全局变量
     select @int_1,@int_2,@int_3;
end
$$
delimiter ;


-- 设置变量
set @int_1=1;
set @int_2=2;
set @int_3=3;


call pro3(@int_1,@int_2,@int_3);