mysql学习总结

mysql总结

1.数据库的基础知识

• 安装

• 建库

  create database db default charset utf8; #创建一个表
  show databses;    #展示所有的数据库
  drop database db    #删除表
  

   

• 建表

  show tables #展示所有的表
  mysql -uroot -p  #进入数据库
  mysql -u root -h 192.168.11.98 -p  #进入别的电脑的数据库
  create tables 表名（id int not null auto_increment primary key,name char(30))engine=innodb default charset=utf8; #innodb支持事务
  

   

• 权限

  create user '用户名'@'IP地址' identified by '密码'
  create user '用户名'@'%' identified by '密码'   #表示所有IP段都可以登入的用户
  grant all privileges on db1.t1 to 'root'@'%' #db1库的表t1 给用户root权限 
  revoke all privileges from db1.t1  to 'root'@'%'   删除权限
  

• 数据库的类型

  数字：int
  	bigint
  	float
  	double
  	decimal #十进制小数
  字符串：char(10)  定长  速度快
  		varchar(10)  节省空间
  时间类型：DATETIME
  enum #枚举的用法    set 的哟个发
  create table enum_set_table(id int auto_increment primary key,gender enum('M','F'),hobby set('music','movie','swimming','footbal'));
  插入值：
  insert into enum_set_table(id,gender,hobby) values(null,'M','music');
   一个enum值，多个set值，二者均使用选项的字符串格式 */
  insert into enum_set_table(id,gender,hobby)values(null,'F','music,movie,footbal');
  /* 一个enum值，一个set值，二者均使用选项的数字格式 */
  insert into enum_set_table(id,gender,hobby) values(null,1,1);
  

• 数据库的基础操作

  primary key:表示 约束（不能重复不能为空）；加速查找
  delete from t1 #清空数据 新增数据还是按照记忆增加
  truncate table t1; #清空数据之后新增数据按照1开始增加
  删除表：
  drop table t1
  insert into t1(id,name)values(1,'ke')  #插入数据
  delete from t1 where id < 8  删除数据
  update t1 set age = 32 # 修改数据
  select * from t1;    # 查看数据
  

• 外键

  create table userinfo(
    		uid bigint auto_increment primary key,
    		name varhar(32),
    		department_id int,
    		constraint fk_user_depar foreign key ('department_id',) references department('id')
  )engine=innodb default charset=utf8;
  
  constraint 外键  fk_user_depar 外键起的名称  foregin key 要关联的字段  references与那个表 的字段
  
  create table department(
  		id bigint auto_increment primary key,
    title char(15)
  )engine=innodb default charset=utf8
  

   

• 表的基本信息查看

  desc 表名 查看表结构
  show create table 表名  展示表如何创建的 但是不太好看
  show create table 表名 \G 
  alter table 表名 auto_increment=2;  #表示让表自增从2开始
  show session variables like 'auto_inc%';  #查看步长信息
  set session auto_increment_increment=2;   #设置会话步长
  mysql基于会话级别的步骤 打开一个命令框一个会话的意思
  set global auto_increment_increment =2;  #设置全局步长
  

  • 唯一索引

    create table t1(id int,num int unqiue uq1(num,))
    

• 外键

  一对一：
  create table userinfo1(
  	id int auto_increment primary key,
    name char(10),
    gender char(10),
    email varchar(64)
  )engine=innodb default charset=utf8;
  
  
  create table admin(
  	id int not null auto_increment primary key,
    username varchar(64) not null,
    password varchar(64) not null,
    user_id int not null,
    unique uq_u1 (user_id),
    constraint fk_admin_u1 foreign key (user_id) references userinfo1(id)
  )engine=innodb default charset=utf8;
  多对多的列子：
  create table userinfo2(
  	id int auto_increment primary key,
    name char(10),
    gender char(10),
    email varchar(64),
  )engine=innodb default charset=utf8;
  
  create table host(
  	id int auto_increment primary key,
    hostname char(64)
    )engine=innodb default charset=utf8;
   
  create table user2host(
    id int auto_increment primary key,
  	userid int not null,
    hostid int not null,
    unique uq_user_host (userid,hostid),
    constraint fk_u2h_user foreign key (userid)references userinfo2(id),
    constraint fk_u2h_user foreign key (hostid)references host(id),
  )engine=innodb default charset=utf8;
  

   

2.数据库的增删改查

增

insert into db（name,age)values('a',32)
insert into db（name,age)values('a',32),('root',33);  #插入多条数据
insert into db(name,age)select name,age from tb1;  #插入一个表的数据

删

delete from db;
delete from db where id >2 ; 

改

update db set name='a'where id >2 and ss='a'
update db set name='a',age=19 where id >2 and ss='a'  #修改多列数据

查

select * from db ;#查看表所有数据
select id,name from db;
select id,name from db where id>10 or name ='1'
select id,name as cname from db where id>10 or name='1'  #表示别名而已
select id,name,11 from db ; # 查出的结果多出一列 列的内容都是11
select * from db where id in(18,10)    #表示这里的id都会查询到
select * from db where id not in(18,10)  #除了18，10的ID 都会查询到
select * from db where id between 1 and 8   #id1和8之间的都取
select * from db where id in (select id from db2) #先查询括号的语句，然后再查询
select * from db where name like 'ale_%'  #ale开头的一个字符
select * from db where name like 'ale%'   #以ale开头的字符
select * from db limit 8  # 查看前8行的数据
select * from limit 1,12  # 表示从1开始也就是从2 再取12行数据
select * from limit 10 offset 20;  #从20开始也就是21 再取10行数据

排序

select * from db order by id desc   #ID从大到小排序查看
select * from db order by id asc  # id从小到大排序
select * from db order by id desc limit 10 #取最后十条数据

分组

create table department5(
	id int auto_increment primary key,
	title varchar(32)
)engine=innodb default charset=utf8;

create table userinfo5(
	id int auto_increment primary key,
  name char(10),
  part_id int,
  constraint fk_user_part foreign key (part_id) references deparment5(id)
)engine=innodb default charset=utf8;
创建两张表插入数据
insert into department5(title) values('公关'),('关公'),('关关'),('公公')；
insert into userinfo5(name,part_id)values('a',1),('b',2),('c',3),('d',4),('e',4);
select min(id),part_id from userinfo5 group by part_id ;   group by是分组的意思 分组的时候
相同的min取最小id  max取最大id  count(id)计数 sum求和avg平均值
select count(id),part_id from userinfo5 group by part_id ;
如果对于聚合函数结果进行刷选时？ 必须使用having
select count(id),part_id from userinfo5 group by part_id having count(id) > 1;

链表操作

select * from userinfo5 left join department5 on userinfo5.part_id = department5.id #左连接 左边的表会全部显示
select * from userinfo5 right join department5 on userinfo5.part_id = department5.id #右连接 右边的表会全部显示 department5

多表连接
select * from userinfo5 
left join department5 on userinfo5.part_id = department5.id 
left join department6 on userinfo5.part_id = department6.id 

select * from userinfo5 inner join department5 on userinfo5.part_id = department5.id 
将出现null时一行隐藏

数据库的导入导出

mysqldump -uroot db1 >db1.sql -p  #导出数据库db1到当前目录 备份数据库的结构+数据
mysqldump -uroot -d db1 >db1.sql -p  #备份数据表结果
执行文件
create databse db1;
mysqldump -uroot db1 < db1.sqi -p 导入数据库

建表：

create database xuexi default charset=utf8;
use xuexi
create table class(cid int not null auto_increment primary key,caption varchar(32) not null)engine=innodb default charset=utf8;

create table teacher(tid int not null auto_increment primary key,tname varchar(32) not null)engine=innodb default charset=utf8;

create table course(cid int not null auto_increment primary key,cname varchar(32),tearch_id int,constraint f_course_teacher foreign key (tearch_id) references teacher(tid))engine=innodb default charset=utf8;

create table student(sid int not null auto_increment primary key,sname varchar(32),gender enum('男','女'),class_id int,constraint f_student_class foreign key class_id references class(cid))engine=innodb default charset=utf8;

create table score(sid int not null auto_increment primary key,student_id int,corse_id int,number char(32),
                   unique kk (student_id,corse_id),
                  constraint f_score_student foreign key student_id references student(sid),
                  constraint f_score_course foreign key corse_id references course(cid)
                  )engine=innodb default charset=utf8;

做题语句大概：

select * from course left join teacher on course.teacher_id=teacher.tid
select gender,count(sid) from student group by gender

--临时表
(select * from score where number > 60) as B
select sid from (select * from score where number > 60) as B

查询平均成绩大于60分的同学的学号和平均成绩
select student_id,avg(number) from score group by student_id having avg(number) > 60 
学生名字也显示出来结合上题
select B.student_id,student.sname,B.cc from(select student_id,avg(number) as cc from score group by student_id having avg(number) > 60) as B
left join student on B.student_id=student.sid


查询所有同学的学号、姓名、选课数、总成绩；
select score.student_id,student.sname,count(student_id),sum(number) from score left join student on score.student_id=student.sid group by score.student_sid

需要继续理解的语句

select course_id max(num),min(num),min(num)+1,case when min(num) < 10 then 0else min(num) end from score group bu course_id

 

 

3.临时表 ，视图 触发器 函数

视图

临时表的意思 某个查询语句设置别名厚方便后面使用 相当于怒你表

本身的数据库表相当于物理表真实存在，视图是会根据物理表更新的，但是不能直接往视图插入数据

create view 视图名称 as 语句   #创建视图
alter view 视图名称 as 语句    #修改视图
drop view 视图名称            #删除视图

触发器

当对某张表做增删改操作时，可以使用触发器自定义关联行为

delimiter //
create trigger t1 before insert on student for each row
begin
insert into teacher(tname)values(new.sanme);
end //
delimiter ;   # delimiter 是修改终止符  创建一个触发器的名字叫 t1  before 执行后面的语句之前出发
begin开始  end结束  然后再把终止符修改回来  其中new表示插入的数据信息  比如上面的就是指insert on student之前  each row  是指每一行数据  new是新数据 插入insert 更新 updte  old 是指老数据  删除  更新

函数

比如之星函数，内置函数
select CURDATE();
时间格式化常用
select ctime,count(1) from blog group DATE_FORMAT(ctmie,"%Y-%m")  #意思先对时间按照年月函数格式化之后在分组 ctime需要也和后面一样格式化

自定义函数：

delimite \\
create function f1(
	i1 int,
  i2 int)
)returns int
begin
declare num int;   #声明一个变量
set num = i1 + i2;
return(num);
end \\
delimite ;

存储过程

保存在mysql上的一个别名 一坨语句

别名（）使用就可以了

delimite \\
create procedure p1()
begin
select * from student;
insert into teacher(tanme)values('ct');
end \\
delimite ;

传参数（in out inout）

delimite \\
create procedure p2(
	in n1 int,
  in n2 int
)
begin
select * from student where id > n1;

end \\
delimite ;

调用的时候call p2(12,3)
cursor.callproc('p2',(12,3))

参数out

delimite \\
create procedure p2(
	in n1 int,
  out n2 int
)
begin
set n2=12;
select * from student where id > n1;

end \\
delimite ;
set @v1 = 0   #表示设置一个session级别的变量@是固定写法
调用的时候call p2(12,必须是个变量 )
cursor.callproc('p2',(12,3))

特性

1.可传参： in out inout
2.pymysql
cursor.callproc('p3',(12,2))
r1=cursor.fetchall()
print(r1)

cursor.execute('select @-p3_0,@-pe_1')
r2=cursor.fetchall()
print(r2)

补充union上下连表 join左右连表

select id ,name from tb1 
union
select num,sanme from tb2;  

比如select sid,sname from student
union
select sid,sanme from student   去重  比如这个表第一个语句查出是16条数据那么这样连表查询就会去重 显示16条数据
如果用union all 就是不去 显示32条数据

存储过程中的事物

delimite \\
create procedure p2(
out status int
)
begin
	1.声明如果出现异常则执行{
	set status = 1;
	rollback;
	}
select * from student where id > n1;

end \\
delimite ;

4.索引

作用 ：约束，加速查找

索引的分为：普通索引 主键索引 唯一索引 联合索引 联合普通索引联合主键索引 联合唯一索引

create index ix_name on userinfo(email);
为email创建了一个索引

索引的种类：

• hash索引 单值快 范围

• btree索引

  普通索引
  create index 索引名称 On 表名（列名）
  drop index 索引名称 on 表名
  唯一索引
  create unique index 索引名称 On 表名（列名）
  drop unique index 索引名称 on 表名
  联合索引
  create unique index 索引名称 On 表名（列名,列名）
  drop unique index 索引名称 on 表名
  普通就是用 index  unique 就是廉合同唯一
  create index_ix_name_email on userinfo3(name,email)最左前缀匹配
  

  命中索引 like != order by 有这些都不能命中索引

注意事项

避免使用select *
count（1）或count(列)
建立表时尽量用char少用varchar
连表时注意条件类型需要一致

mysql查询预估时间用

explain select * from db

慢日志

查看数据库内存配置：
show variables like '%query%'; 
set global 变量名 = 值 
-配置文件
mysqld --defaults-file=配置文件路劲

5.分页

max_id
min_id
select * from userinfo where  id >max_id limit 10； 下一页翻
select * from userinfo where  id <min_id order by id desc limit 10；  上一页
上一页 192 193 （196） 197 198 199 下一页  #假设目前在196  接下来点199
select id from (select id from userinfo where id > max_id lmit30)as N order by N.id desc limit 30

6.orm框架

当一类函数公用同样参数时候，可以变成类进行 -分类

面向对象：数据和逻辑合在一起

函数变成： 数据和逻辑分类

模版‘约束’

类的记忆

def __call__(self):       # obj1()
  pass
  
def __getitem__(self,key):       # obj1['k']
  pass
def __setitem__(self,key,value):       # obj1['k']=123
  pass  
def __delitem__(self,key):       # del obj[k]
  pass

orm框架 SQLAlchemy

作用：提供简单规则 自动转换成sql语句

--db first/ code first