MYSQL笔记

CREATE DATABASE 库名;	/*创建数据库*/
SHOW databases;			/*查看所有数据库*/
use 库名;					/*选择数据库*/
drop database 库名;		/*删除数据库*/
show tables;				/*查看所有表*/
create table class(id int, name varchar(128), teacher varchar(64));/*创建表class*/
insert into class values(101,'六年级一班','马老师');/*向表class插入信息*/
select*from class;	/*查询表class所有信息*/
select*from class where class_id=101;/*查询表class中class_id=101的信息*/

/*primary key主键唯一的  not null非空，不能插入空值  unique唯一的*/
create table class1(class_id int primary key,class_name varchar(128) not null,class_teacher varchar(64) unique);/*创建表class*/

/*整数类型*/
 
/*int(8) 8个字节的int类型，zerofill 数字位不够的字节用0在前面代替并且自动添加 unsigned 属性 无符号数*/
create table class2(class_id int(8) zerofill,class_name varchar(128),class_teacher varchar(64));/*创建表class*/
/* auto_increment 一个表只能有一个这样的属性，自增长属性不指定值时默认由上一个值自增1，并且必须定义成主键或者唯一的*/
create table class3(id int auto_increment primary key,name varchar(128),teacher varchar(64));
/*有一列不插入数据时应指定从哪一列开始插入到哪一列*/
insert into class3(name,teacher) values('六年级三班','贰老师');

/*浮点类型*/
 
/*DEC为定点类型 M>D  M默认为10，D默认为0，M为总位数，D为小数点后的位数*/
create table class4(fl float,dl double,de dec(10,9));

/*日期和时间*/
 

/*year 为年，date为年月日，time 为时间，datetime为年月日时间，timestamp 为时间戳，/:-都可以做分隔符*/
create table time1(y year,d date,t time, dt datetime,ts timestamp);
insert into time1 values(2020,'2020-10-1','24:10:56','2020-10-01 23:10:57','2020:10:01 23:10:56');

 
/*year(now()) ，当前年，time(now())，当前时间*/
insert into time1 values(year(now()),curdate(),time(now()),now(),now());

/*char的长度是固定的0~255，储值时会用空格填充到指定长度，varchar的长度是可变的，创建表时指定了最大长度，范围为0~65535，动态分配*/
/*char类型的插入会屏蔽空格，varchar不会*/
 
default null comment'注释'/*设置默认值为空*/
/*用户表*/
create table userinfo(_no int unsigned auto_increment primary key ,
_name varchar(64) default null comment'姓名',
telephone char(11) default null comment'电话',
home varchar(128) default null comment'家庭地址',
prpr text default null comment'个人简历',
sex enum('男','女','保密') default null comment'性别',
age tinyint unsigned default null comment'年龄',
id char(18) default null comment'身份证号');

enum类型单选create table test1(sex enum('男','女','保密')); 只能插入列举出来的内容或者用数字替换 男=1女=2，加了not null属性默认为第一个值
set类型多选create table test2(hobby set('足球','篮球','羽毛球')); insert into test2 values('足球,篮球');set选项的最大个数为64
二进制类型
 
binary(M)建表时长度就指定了，不足最大长度空间用”\0”补全，varbinary长度是可变的





 

constraint 设置主键别名为pk_name
create table class(id int,constraint pk_name primary key(id));
多个属性组合成主键
create table class2(id int,_name char(4),constraint pk_name primary key(id,_name));
外键约束foreign key(字段1)references 表2(字段2)
create table class3(m_id int,foreign key(m_id)references class(id));
auto_increment 自动属性必须设置为键
default默认值，不设置时默认为空

/*向已建好的表新增键(主键，外键，唯一)*/
alter table 表名 add constraint 约束名 primary key(列名);
删除主键
alter table 表名 drop primary key;
外键或唯一键
alter table 表名 drop index(约束名);
/*修改默认值、自增等属性*/
alter table 表名 modify 列名 类定义;

普通索引，不附加任何键，在表后加index 索引名(列名 长度 asc或desc),索引名和长度可加可不加，asc为升序desc为降序
create table 表名(id int,m_name varchar(64),telephone char(11),index (id asc));
追加普通索引，索引名不能省略长度可以省略，长度必须是字符串类型才能使用
create index 索引名 on 表名(列名 长度 排序方法);
alter table 表名 add index 索引名(列名 长度 排序方法);
/*唯一索引,比普通索引更快的查询某条记录，在index前加上unique关键字*/
create table 表名(id int,m_name varchar(64),unique index (列名 asc));
追加唯一索引，在index前加上unique，规则和追加普通索引一样
create unique index 索引名 on 表名(列名 长度 排序方法);
alter table 表名 add unique index 索引名(列名 长度 排序方法);
全文索引，主要基于char、varchar和text的字段上
create table 表名(test varchar(164),fulltext index 索引名(test 长度));
select*from 表名 where match(列名) against(查询字段);
追加全文索引
create fulltext index 索引名 on 表名(列名);
alter table 表名 add fulltext index 索引名 (列名);
删除索引
drop index 索引名 on 表名;
多列索引
create table 表名(id int,m_name varchar(64),index 索引名(列名1,列名2));


/*隐藏索引*/ invisible隐藏，visible取消隐藏，用来验证索引的必要性
alter table 表名 alter index 索引名 invisible;
/*取消隐藏*/
alter table 表名 alter index 索引名 visible;

插入多条记录
insert into 表名 values(数据),(数据),(数据)……;
更新数据，替换所有数据则不需要加where
update 表名 set id=10,m_name='小十' where id=8;
删除数据记录，删除所有记录则不需要加where
delete from 表名 where 条件;
md5();加密函数，括号里面填写需要加密的字符,验证时也加上md5()即可

distinct去重
select distinct 列名 from 表名;
in查询，查询是否有集合内的数据，not in 反向查询非集合中的数据
select*from 表名 where 列名 in(值1,值2);
区间between and 查询，not between and不在区间内查询
select*from 表名 where 列名 between 小值 and 大值;
like查询，模糊查找 _单个字符,%任意长度字符
select*from 表名 where 列 like '%王';
查询列中既有王又有张的记录
select*from 表名 where 列 like '%王%'and 列 like’%张%’;
对查询结果排序，空值视为最小的数
select*from 表名 where 条件 order by 列名 排序方式;
依据列进行分组
select count(列) from 表  group by 列;
统计分组，显示每个分组中的所有字段
select count(列),group_concat(需要统计的列) from 表  group by 列;

联合查询,内连接
select*from 表1 inner join 表2 on 表一.字段=表二.字段;
as取别名，表名 as 别名
select*from stdu as s inner join class as c on c.id=s.class_id;
左外连接，如果左表某行在右表中没有匹配行，则在显示结果中游标右表显示行所有值为空，右连接把left改成right就行了
select*from 左表 left join 右表 on 左表.列=右表.列;
-- 合并查询，多表合并，查询列数必须相同，使用union连接两个查询并且去重，当在union后加上all时不会去重
select*from class union select id,class_id,m_name from stdu;
-- 嵌套查询，使用比较运算符（=,!=,<=,>=,<,>）时,子语句可获得的记录数只能有一条
select*from class where id=(select class_id from stdu where m_name='小李');
嵌套，当子语句可以查询到多条记录时可以使用in查询
select*from stdu where class_id in (select id from class where teacher!='王老师');
exists判断子查询记录是否存在，存在则返回ture，不存在则返回false
select*from stdu where class_id=102 and exists (select*from class where id=102);
any表示满足子查询中的任何一个记录，可在any前加上运算符
select*from stdu where class_id >any(select id from class);
all表示满足子查询中所有记录，可在all前加上运算符
select*from stdu where class_id =all(select id from class);

-- 视图
create view 视图名 as 查询语句;
-- 查看视图
desc 视图名;
-- 删除视视图
drop view 视图名;

-- 触发器        
--  for each row表示任何一条记录上满足条件都能触发
--  delete 触发事件，可以替换为update insert
--  for each row 后加触发后的语句
--  after为触发后，可以替换成before触发前
--  new和old，insert为new、delete为old，update更新前为old更新后为new
--  有多条需要执行的语句时，在for each row后加上begin以及末尾加上end，使用分号隔开
create trigger 触发器名称 after insert on stdu1 for each row update class1 set count=count+1 where class1.id=new.class_id;
create trigger 触发器名称 after delete on stdu1 for each row update class1 set count=count-1 where class1.id=old.class_id;
--  删除触发器
drop trigger 触发器名称;
-- 查看所有触发器
show triggers;

-- 存储过程，和函数差不多的作用
DELIMITER $$	-- 重新定义结束符号
create procedure proc_detele_stu (in sid int) -- sid相当于形参，in为输入类型，out为输出类型，inout输入输出
begin 
declare cid int;	-- 定义变量cid
select class_id into cid from stdu1 where id=sid; -- into cid，把查找出来的class_id保存在cid里面
delete from grade where id=sid; 		
delete from	 stdu1 where id=sid;
update class set count=count-1 where id=cid;
end;
$$
DELIMITER ;
-- 调用存储过程
call proc_detele_stu(2);
定义变量，多个变量用逗号隔开
declare 变量名1 类型 default 默认值
给变量赋值，多个变量赋值则在值后面用逗号隔开
set 变量名=值;
删除存储过程
drop procedure proc_detele_stu;









-- 光标
DELIMITER $$	
create procedure quer_student (in sid int,out cname varchar(128),out cid int)
begin 
declare tmp_name varchar(128);
declare tmp_cid int;		-- 定义变量必须在声明光标前
declare cur_student cursor for select stdu_name,class_id from stdu1;	-- 声明光标declare 光标名 cursor for 查询语句
open cur_student;	-- 打开光标
fetch cur_student into tmp_name,tmp_cid; 		-- 使用光标 fetch 光标名 into 变量
select tmp_name,tmp_cid;		-- 打印光标中获取的值
close cur_student;				-- 关闭光标
set cname=tmp_name,cid=tmp_cid;	-- 给参数赋值
end;
$$
DELIMITER ;

if 条件 then 需要执行的语句 ;elseif 条件 then需要执行的语句; else 需要执行的语句 ;end if;
if sid<10 then set count1=sid+1;
elseif sid>10 then set count1=sid+2;
else set count1=0;
end if;

case语句，就是c++的switch
case sid
when sid=1 then set count1=sid;
when sid=2 then set count1=sid;
when sid=3 then set count1=sid;
else set count1=0;
end case;

loop语句，就是c++的循环语句，语法 开始标志:loop 循环语句 end loop 结束标志;
Add_num:loop
set sid=sid+1;
if sid=20 then set count1=sid;leave Add_num;end if;
end loop add_num;
leave语句，leave 结束标志;结束循环
leave Add_num;
iterate语句，跳出本次循环
iterate Add_num;
repeat语句，相当于do while， 开始标志:repeat 语句; until 结束条件 end repeat 结束标志;
Add_num:repeat
set sid=sid+1, count1=sid;
until sid>100
end repeat add_num;
while 语句，开始标志:while 开始条件 do 语句; end while 结束标志;
Add_num:while sid<100 do
set sid=sid+1, count1=sid;
end while add_num;
-- 查找当前用户最后插入的id并保存在temp中，当在插入时获取当前插入记录的自增长id
select last_insert_id() as temp;

-- SHOW databases;
-- CREATE DATABASE my_sql;/*新建数据库*/
-- SHOW databases;
-- use my_sql;
-- drop database my_sql;/*删除数据库*/
-- SHOW databases;

-- CREATE DATABASE school; /*创建school数据库*/
-- use school;	/*选择school数据库*/
-- create table class(class_id int primary key,class_name varchar(128),class_teacher varchar(64));/*创建表class*/
-- insert into class values(101,'六年级一班','马老师');/*向表class插入信息*/
-- insert into class values(102,'六年级二班','潘老师');
-- select*from class;	/*查询表class所有信息*/
-- select*from class where class_id=101;/*查询表class中class_id=101的信息*/

-- use school;	/*选择school数据库*/
-- /*primary key主键唯一的  not null非空，不能插入空值  unique唯一的*/
-- create table class1(class_id int primary key,class_name varchar(128) not null,class_teacher varchar(64) unique);/*创建表class*/

-- use school;	/*选择school数据库*/
-- /*int(8) 8个字节的int类型，zerofill 数字位不够的字节用0在前面代替并且自动添加 unsigned 属性 无符号数*/
-- create table class2(class_id int(8) zerofill,class_name varchar(128),class_teacher varchar(64));/*创建表class*/
-- insert into class2 values(103,'六年级三班','喵老师');
-- select*from class2;

-- /* auto_increment 一个表只能有一个这样的属性，自增长属性不指定值时默认由上一个值自增1，并且必须定义成主键或者唯一的*/
-- create table class3(id int auto_increment primary key,name varchar(128),teacher varchar(64));
-- insert into class3(name,teacher) values('六年级三班','贰老师');
-- insert into class3(name,teacher) values('六年级四班','王老师');
-- select*from class3;

-- show tables;
-- drop table class4;
-- /*dec定点类型*/
-- create table class4(fl float,dl double,de dec(10,9));
-- insert into class4 values(1.1111111,1.11111111111111,1.111111111);
-- select*from class4;

-- /*year 为年，date为年月日，time 为时间，datetime为年月日时间，timestamp 为时间戳，/:-都可以做分隔符*/
-- create table time1(y year,d date,t time, dt datetime,ts timestamp);
-- insert into time1 values(2020,'2020-10-1','24:10:56','2020-10-01 23:10:57','2020:10:01 23:10:56');
-- /*year(now()) ，当前年，time(now())，当前时间*/
-- insert into time1 values(year(now()),curdate(),time(now()),now(),now());
-- select*from time1;

use school;	
/*序号，姓名，电话，家庭住址，个人简介，性别，年龄，身份证号*/
create table userinfo(_no int unsigned auto_increment primary key ,
_name varchar(64) default null comment'姓名',
telephone char(11) default null comment'电话',
home varchar(128) default null comment'家庭地址',
prpr text default null comment'个人简历',
sex enum('男','女','保密') default null comment'性别',
age tinyint unsigned default null comment'年龄',
id char(18) default null comment'身份证号');
insert into userinfo values(1,"老王","182****9567","江西省某某市某某县","性格开朗活泼","男",18,"360781************");
select*from userinfo;
/*单选*/
create table test1(sex enum('男','女','保密'));
insert into test1 values('男');
select*from test1;
/*多选,最多64个*/
create table test2(hobby set('足球','篮球','羽毛球'));
insert into test2 values('足球,篮球');
select*from test2;

create table bin_example(e_bin binary(5),e_varbin varbinary(5));
insert into bin_example values('ab','ab');
select*from bin_example;

drop table class;
create table class(id int,constraint pk_name primary key(id));
create table class2(id int,_name char(4),constraint pk_name primary key(id,_name));
create table class3(m_id int,foreign key(m_id)references class(id));

/*向已建好的表新增键(主键，外键，唯一)*/
create table class4(test int);
alter table class4 add constraint pk_id primary key(test);
show keys from class4;
alter table class4 drop primary key;
/*修改默认值、自增等属性*/
alter table class4 modify test char(4) default NULL;

/*普通索引*/
use school;
drop table class5;
create table class5(id int,m_name varchar(64),telephone char(11),index (id asc));
insert into class5 values(5,"老五","1*********");
insert into class5 values(4,"老四","1*********");
insert into class5 values(6,"老六","1*********");
insert into class5 values(1,"老大","1*********");
select*from class5 where id>0;
/*追加普通索引*/
create index index_id on class5(id asc);
alter table class5 add index index_id (id asc);

/*唯一索引,比普通索引更快的查询某条记录，在普通索引前加上unique关键字,列名必须有unique约束*/
create table class6(id int,m_name varchar(64),unique index id_index(id asc));
drop table class6;
insert into class6 values(1,"一");
insert into class6 values(2,"二");
insert into class6 values(3,"三");
select*from class6;

/*全文索引*/
create table class7(test varchar(164),fulltext index fu_index(test));
insert into class7 values("我是你爸爸，最伟大");
select*from class7 where match(test) against("我是你爸爸");
/*删除索引*/
drop index fu_index on class7;
/*追加全文索引*/
create fulltext index fu_index on class7(test);
alter table class7 add fulltext index fu_index (test); 

/*多列索引*/
create table class8(id int,m_name varchar(64),index (id,m_name));
insert into class8 values(5,'老王');
insert into class8 values(4,'老张');
select *from class8 where id>0;

/*隐藏索引*/
alter table class7 alter index fu_index invisible;
/*取消隐藏*/
alter table class7 alter index fu_index visible;

/*插入多条记录*/
insert into class8 values(6,'老九'),(7,"老七"),(8,'老八');
/*更新数据*/
update class8 set id=10,m_name='小十' where id=8;
/*删除数据记录*/
delete from class8 where id=10;

/*去重*/
select distinct m_name from class8;
/*in查询*/
select*from class8 where m_name in('老王','老张');
/*between and 查询*/
select*from class8 where id between 3 and 7;
/*like查询*/
select*from class8 where m_name like '%王';

/*查询结果排序*/
select*from class8 where id>0 order by id desc;

create table class9(id int,m_name varchar(64),class varchar(10));
insert into class9 values(1,'张三',1),(2,'李四',2),(3,'熊大',1),(4,'熊二',2);
select count(class),class from class9  group by class;
select count(class),group_concat(m_name) from class9  group by class;

/*创建教室表和学生表*/
use school;
create table class(id int not null auto_increment unique,m_name varchar(64)default null,teacher varchar(64)default null);
create table stdu(id int not null auto_increment unique,m_name varchar(64) default null,class_id int default null,sex enum("男","女")default null);
insert into class values(101,'一班','马老师'),(102,'二班','王老师'),(103,'三班','刘老师');
insert into stdu values(1,'小花',101,'女'),(2,'小李',102,'男'),(3,'小美',101,'女'),(4,'小明',102,'男'),(5,'小红',103,'女');
/*联合查询，内连接*/
select*from stdu inner join class on class.id=stdu.class_id;
/*as取别名*/
select*from stdu as s inner join class as c on c.id=s.class_id;
/*左外连接，如果左表某行在右表中没有匹配行，则在显示结果中游标右表显示行所有值为空*/
select*from stdu left join class on stdu.class_id=class.id;
select*from class right join stdu on stdu.class_id=class.id;
-- 合并查询，多表合并，查询列数必须相同
select*from class union select id,class_id,m_name from stdu;
-- 嵌套查询，运算符、in、exists、any、all
select*from class where id=(select class_id from stdu where m_name='小李');
select*from stdu where class_id in (select id from class where teacher!='王老师');
select*from stdu where class_id=102 and exists (select*from class where id=102);
select*from stdu where class_id =any(select id from class);
select*from stdu where class_id =all(select id from class);

-- 视图
create view te as select teacher from class;
-- 查看视图
desc te;
select*from te;
-- 删除视图
drop view te;

--  触发器        
--  for each row表示任何一条记录上满足条件都能触发
--  delete 触发事件，可以替换为update insert
--  for each row 后加触发后的语句
--  after为触发后，可以替换成before触发前
--  new和old，insert为new、delete为old，update更新前为old更新后为new
--  有多条需要执行的语句时，在for each row后加上begin以及末尾加上end
create table class1 (id int not null auto_increment unique,class_name varchar(64)default null,teacher varchar(64)default null,count int default 0);
insert into class1 values(101,'一班','马老师',0),(102,'二班','王老师',0),(103,'三班','刘老师',0);
create table stdu1 (id int not null auto_increment unique,stdu_name varchar(64)default null,class_id int default 0,sex enum('男','女')default null);

create trigger tri_inser_class after insert on stdu1 for each row update class1 set count=count+1 where class1.id=new.class_id; 
insert into stdu1 values(1,'小王',102,'男'),(2,'小美',101,'女');
select*from class1;
create trigger tri_delete_class after delete on stdu1 for each row update class1 set count=count-1 where class1.id=old.class_id;
delete from stdu1 where id=1;
--  删除触发器
drop trigger tri_inser_class;
-- 查看触发器
show triggers;

-- 存储过程，和函数差不多的作用
DELIMITER $$	-- 重新定义结束符号
create procedure proc_detele_stu (in sid int) -- sid相当于形参，in为输入类型，out为输出类型，inout输入输出
begin 
declare cid int;	-- 定义变量cid
select class_id into cid from stdu1 where id=sid; -- into cid，把查找出来的class_id保存在cid里面
-- delete from grade where id=sid; 		
delete from	stdu1 where id=sid;
update class1 set count=count-1 where id=cid;
end;
$$
DELIMITER ;
-- 调用存储过程
call proc_detele_stu(2);
-- 删除存储过程
drop procedure proc_detele_stu;

-- 光标
DELIMITER $$	
create procedure quer_student (in sid int,out cname varchar(128),out cid int)
begin 
declare tmp_name varchar(128);
declare tmp_cid int;		-- 定义变量必须在声明光标前
declare cur_student cursor for select stdu_name,class_id from stdu1 where id=sid;	-- 声明光标
open cur_student;	-- 打开光标
fetch cur_student into tmp_name,tmp_cid; 		-- 使用光标
select tmp_name,tmp_cid;		-- 打印光标中获取的值
close cur_student;				-- 关闭光标
set cname=tmp_name,cid=tmp_cid;	-- 给参数赋值
end;
$$
DELIMITER ;
drop procedure quer_student;
call quer_student(1,@a,@b);
select @a,@b;

use school;
-- if控制语句
DELIMITER $$	
create procedure pro_test (in sid int,out count1 int)
begin 
if sid<10 then set count1=sid+1;
elseif sid>10 then set count1=sid+2;
else set count1=0;
end if;
end;
$$
DELIMITER ;
drop procedure pro_test;
call pro_test(11,@c);
select @c;

-- case语句
DELIMITER $$	
create procedure pro_test1 (in sid int,out count1 int)
begin 
case sid
when sid=1 then set count1=sid;
when sid=2 then set count1=sid;
when sid=3 then set count1=sid;
else set count1=0;
end case;
end;
$$
DELIMITER ;
drop procedure pro_test1;
call pro_test1(1,@c);
select @c;

-- loop语句,leave 语句 
DELIMITER $$	
create procedure pro_test2 (in sid int,out count1 int)
begin 
Add_num:loop
set sid=sid+1;
if sid=20 then set count1=sid;leave Add_num;end if;
end loop add_num;
end;
$$
DELIMITER ;
drop procedure pro_test2;
call pro_test2(1,@c);
select @c;

-- repeat语句
DELIMITER $$	
create procedure pro_test3 (in sid int,out count1 int)
begin 
Add_num:repeat
set sid=sid+1,count1=sid;
until sid>100
end repeat add_num;
end;
$$
DELIMITER ;
drop procedure pro_test3;
call pro_test3(1,@c);
select @c;

-- while语句
DELIMITER $$	
create procedure pro_test4 (in sid int,out count1 int)
begin 
Add_num:while sid<100 do
set sid=sid+1,count1=sid;
end while add_num;
end;
$$
DELIMITER ;
drop procedure pro_test4;
call pro_test4(1,@c);
select @c;

-- 综合实例，循环判断配合光标使用
DELIMITER $$	
create procedure quer_student1 (out cname varchar(128),out cid int)
begin 
declare tmp_name varchar(128);-- 定义变量必须在声明光标前
declare tmp_cid int;		
declare done int default 0;
declare cur_student cursor for select stdu_name,class_id from stdu1;	-- 声明光标
declare continue handler for not found set done=1; 				-- 当光标没有记录时done设为1
open cur_student;	-- 打开光标
READ_Stdu:loop
	fetch cur_student into tmp_name,tmp_cid; 		-- 使用光标
	if done=1 then leave READ_Stdu;
	end if ;
	select tmp_name,tmp_cid;		-- 打印光标中获取的值
end loop READ_Stdu;
close cur_student;				-- 关闭光标
set cname=tmp_name,cid=tmp_cid;	-- 给参数赋值
end;
$$
DELIMITER ;
drop procedure quer_student1;
call quer_student1(@a,@b);
select @a,@b;

-- 查找当前用户最后插入的id
select last_insert_id();
-- 查看存储过程
show procedure status like 'quer%';
-- 查看创建语句
show create procedure quer_student1;

-- 查看存储引擎
show engines;