mysql笔记

mysql 3066

redis 6479

mongodb 27017

django 8000

flask 5000

======================================================================================

mysql -h 127.0.0.1 -p 3306 -uroot -p
show databases;
\s
\c取消
mysqladmin -uroot -p password 新密码 #cmd终端下直接输入此命令，无需进入mysql界面
mysqld --skip-grant-tables #跳过密码验证（需先将服务关停，net stop mysql）
update mysql.user set password=password(新密码) where user='root' and host='localhost'; 　　#修改当前用户密码(5.6版本)
                                                         密文加密
update mysql.user set authentication_string=password(新密码) where user='root' and host='localhost'; 　　（5.7版本）
flush privileges; 　　　　 #立刻将数据刷入硬盘

#增库
create database 库名;
create database 库名 charset='gbk';
#查库
show databases;
show create database 库名;
#改库
alter database 库名 charset='utf8';
#删库
drop database 库名;

#增表
create table 表名(id int,name char);
create table 表名(id int,name char(4)); #限制4字符
#查表
show tables;
show create table 表名;
describe 表名;
desc 表名;
#改表
alter table 表名 modify name char(20);

1、修改表名
alter table 表名 rename 新表名
2、增加字段
alter table 表名 add 字段名 字段类型 约束条件（not null/zerofill/unsigned）
alter table 表名 add 字段名 字段类型 约束条件 first #将字段放在表的最前面
alter table 表名 add 字段名 字段类型 约束条件 after 字段名 #指定新字段名跟在哪个字段后面
3、删除字段
alter table 表名 drop 字段名
4、修改字段
alter table 表名 modify 字段名 字段类型 约束条件

alter table 表名 change 旧字段名 新字段名 字段类型 约束条件

5、复制表create table 新表名 select * from 旧表名

#删
drop table 表名;

#增数据
insert into 表名 values('数据')；

insert into 表名 （name） values('数据')；
insert into 表名 values('数据')，（‘数据’），（‘数据’）;
#查数据
select * from 表名;
select id,name from 表名;
#改数据
update 表名 set name='新数据' where id>1(条件);
#删数据
delete from 表名 where id>1(条件);
delete from 表名;

 

select database(); #查看当前所在库
use 库名; #切换库
show engines; #查看存储引擎查看严格模式

status;　　简写：\s　　查看状态信息

show variables like "%char%";查看编码格式

show variables like "%mode";
set session 　　#只在当前窗口有效
set global 　　 #全局有效
set global sql_mode = 'STRICT_TRANS_TABLES'; #修改严格模式，5.7以后默认无需修改
(ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION) 5.7.默认的sql_mode模式
select char_length(name) from 表名; 　　#统计字段长度
create table abc (gender enum('male','female','other'),hobby set('java','python','golang','c') ); 　　#枚举、集合类型

======================================================================================

　　　　　　　　　　　　　　　　　　　　　　primary key

create table 表名 (

id int unique, 　　#单列唯一
name varchar(20),
gender enum('male','female','others')default 'male' 　　#默认值

);

 

create table 表名 (
id int,
ip varvchar(16)
prot int,
unique(ip,prot)　　 #联合唯一
);

 

alter table 表名 modify id int not null default 20 primary key; #约束条件：非空、默认20、主键
create table 表名 (id int primary key auto_increment); #自增
truncate 表名; #清空数据并重置主键

======================================================================================

　　　　　　　　　　　　　　　　　　　　　　　　foreign key

 

foreign key 外键
一对多表关系 外键在多的一方
create table addr(
id int primary key auto_increment,
addr varchar(20)
);

 

create table human(
id int primary key auto_increment,
age int,
name varchar(20),
gender enum('male','female','others') default 'male',
add_id int,
foreign key (add_id) references addr(id)
on update cascade
on delete cascade
);
多对多 外键在第三张表中
create table book(
id int primary key auto_increment,
name char(20)
);
create table author(
id int primary key auto_increment,
name char(20)
);
create table a2b(
id int primary key auto_increment,
a_id int,
foreign key (a_id) references author (id)
on update cascade
on delete cascade,
b_id int,
foreign key(b_id) references book(id)
on update cascade
on delete cascade
);
一对一 外键在哪都行，推荐在使用频繁的表中
create table userdetail(
id int primary key auto_increment,
name varchar(20)
);
create table user(
id int primary key auto_increment,
name varchar(20),
age int,
addr varchar(20),
ud_id int unique,
foreign key (ud_id) references userdetail(id)
on update cascade
on delete cascade
);

 

======================================================================================

                             group by,having，distinct,order by,limit

 set global sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY'; #修改严格模式、分组模式，5.7以后默认无需修改,改不改都行

select mep,max(salary) from aaa group by mep;　　#按组查看，查看每部门的最高薪资

select mep as '部门',min(salary) as '最低薪资' from aaa group by job; 　　#安组查看，查看每岗位的最低薪资，并添加别名

avg（评价），sum(汇总)，count（计数）

select job,group_concat(name) from aaa group by job;　　#查看组下成员

select job,group_concat(name,‘:dsb@%!’) from aaa group by job;　　#拼接字符

select concat(name) from aaa;

select salary*12 from aaa;

select job,group_concat(name),avg(salary) from aaa where age > 50 or age < 20 group by job;　　#group by 应在where后面

select job,group_concat(name),avg(salary) from aaa where age<28 or age>70 group by job having avg(salary) > 200;　　#having分组之后的筛选（类似where）

select distinct age,name from aaa;　　#distinct去重，注意不要带主键primary key

select * from aaa order by salary;　　#排序，默认为asc(ascending)升序，需要降序再最后加上 desc（descending)

select * from aaa order by salary,age desc;　　#排序时，薪资同样的话，按年龄倒序排

select job,avg(salary) from aaa

　　where age>10

　　group by job

　　having avg(salary) >2000

　　order by avg(salary)

　　;

select * from aaa limit 3;　　#从0开始取3行数据

select * from aaa limit 2,3;　　#从2开始取3行数据

======================================================================================

　　　　　　　　　　　　　　　　　　　　regexp

select * from aaa where name regexp '^m.*(i|a)$';　　#m开头，i或a结尾，中间随意 

======================================================================================

　　　　　　　　　　　　　inner join,left join,right join,union

select * from aaa,bbb where aaa.a_id = bbb.b_id;　　#将aaa表中b_id与bbb表中a_id相等的数据拼成一个表

select * from aaa inner join bbb on aaa.a_id=bbb.b_id;　　#只显示两个表都符合条件的数据

select * from aaa left join bbb on aaa.a_id=bbb.b_id;　　#将aaa表作为主表，显示aaa全部数据

 

select * from aaa inner join bbb on aaa.a_id=bbb.b_id;

union　　　　　　　　　　　　　　　　　　　　　　　　　　#将两表的数据都显示出来，需要配合inner\left使用

select * from aaa left join bbb on aaa.a_id=bbb.b_id;

======================================================================================

　　　　　　　　　　　　　　　　　　视图、触发器、事务、存储过程

delimiter $$ #仅当前窗口有效

create view 表名 as 虚拟表的查询sql语句

create trigger 触发器名字 before/after insert/update/delete on 表名
for each row
begin
sql语句
end
drop trigger 触发器名字

触发器例子
create table cmd(
id int primary key auto_increment,
user char(20),
cmd char(64),
sub_time datetime,
success enum ('yes','no')
);
create table errlog(
id int primary key auto_increment,
err_cmd char(64),
err_time datetime
);
delimiter $$
create trigger err_trigger after insert on cmd
for each row
begin
if NEW.success = 'no' then #NEW指代数据对象
insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
end if;
end $$
delimiter ;

 

start transaction; #开启事务
SQL语句
rollback; #回滚，回到事务执行前的状态
commit; #确认

 

create procedure 存储过程的名字(参数)
begin
sql语句
end
call 存储过程的名字();
存储过程例子
delimiter $$
create procedure ppp(
in m int, #m不能返回出去
in n int,
out res int #res可以返回出去,但需要传变量
)
begin
select name from aaa where id >m and id < n;
set res =666;
end $$
delimiter;
调用
set @sb=38;
call ppp(1,5,@sb);
select @sb;
#pymysql中调用 cursor.callproc('ppp',(1,5,38))

======================================================================================
if
delimiter //
create procedure proc_if()
begin
declare i int default 0 ;
if i = 1 then
select 1;
elseif i=2 then
select 2;
else
select 3;
end if;
end //
delimiter ;

while
delimiter //
create procedure proc_while()
begin
declare num int;
set num=0;
while num<10 do
select
num;
set num=num+1;
end while;

======================================================================================