首页  思维笔记  详情

MySQL思维导图

2021-06-29 22:07:07   0  举报





仅支持查看

Mysql思维导图

ppt

作者其他创作

大纲/内容

表于表之间的关系

外键

一对多关系

foreign key 1 一对多表关系外键字段建在多的一方 2 在创建表的时候一定要先建被关联表 3 在录入数据的时候也必须先录入被关联表

# SQL语句建立表关系 create table dep( id int primary key auto_increment, dep_name char(16), dep_desc char(32) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male','female','others') default 'male', dep_id int, foreign key(dep_id) references dep(id) ); insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门'); insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);

级联更新、级联删除

# 修改dep表里面的id字段 update dep set id=200 where id=2; 不行 # 删除dep表里面的数据 delete from dep; 不行

# 1 先删除教学部对应的员工数据之后再删除部门 操作太过繁琐 # 2 真正做到数据之间有关系 更新就同步更新 删除就同步删除

""" 级联更新 >>> 同步更新 级联删除 >>> 同步删除 """ create table dep( id int primary key auto_increment, dep_name char(16), dep_desc char(32) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male','female','others') default 'male', dep_id int, foreign key(dep_id) references dep(id) on update cascade # 同步更新 on delete cascade # 同步删除 ); insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门'); insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);

多对多关系

图书表和作者表 其实我们只是想记录书籍和作者的关系 针对多对多字段表关系不能在两张原有的表中创建外键 需要你单独再开设一张专门用来存储两张表数据之间的关系 create table book( id int primary key auto_increment, title varchar(32), price int ); create table author( id int primary key auto_increment, name varchar(32), age int ); create table book2author( id int primary key auto_increment, author_id int, book_id int, foreign key(author_id) references author(id) on update cascade # 同步更新 on delete cascade, # 同步删除 foreign key(book_id) references book(id) on update cascade # 同步更新 on delete cascade # 同步删除 );

一对一

一对一外键字段建在任意一方都可以但是推荐你建在查询频率比较高的表中 create table authordetail( id int primary key auto_increment, phone int, addr varchar(64) ); create table author( id int primary key auto_increment, name varchar(32), age int, authordetail_id int unique, foreign key(authordetail_id) references authordetail(id) on update cascade # 同步更新 on delete cascade # 同步删除 )

修改表

# MySQL对大小写是不敏感的 """ 1 修改表名 alter table 表名 rename 新表名; 2 增加字段 alter table 表名 add 字段名字段类型(宽度) 约束条件; alter table 表名 add 字段名字段类型(宽度) 约束条件 first; alter table 表名 add 字段名字段类型(宽度) 约束条件 after 字段名; 3 删除字段 alter table 表名 drop 字段名; 4 修改字段 alter table 表名 modify 字段名字段类型(宽度) 约束条件; alter table 表名 change 旧字段名新字段名字段类型(宽度) 约束条件; """

复制表

""" 我们sql语句查询的结果其实也是一张虚拟表 """ create table 表名 select * from 旧表; 不能复制主键外键 ... create table new_dep2 select * from dep where id>3;

表查询

前期表准备

create table emp( id int not null unique auto_increment, name varchar(20) not null, sex enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int ); #插入记录 #三个部门：教学，销售，运营 insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values ('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部 ('tom','male',78,'20150302','teacher',1000000.31,401,1), ('kevin','male',81,'20130305','teacher',8300,401,1), ('tony','male',73,'20140701','teacher',3500,401,1), ('owen','male',28,'20121101','teacher',2100,401,1), ('jack','female',18,'20110211','teacher',9000,401,1), ('jenny','male',18,'19000301','teacher',30000,401,1), ('sank','male',48,'20101111','teacher',10000,401,1), ('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门 ('呵呵','female',38,'20101101','sale',2000.35,402,2), ('西西','female',18,'20110312','sale',1000.37,402,2), ('乐乐','female',18,'20160513','sale',3000.29,402,2), ('拉拉','female',28,'20170127','sale',4000.33,402,2), ('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门 ('程咬金','male',18,'19970312','operation',20000,403,3), ('程咬银','female',18,'20130311','operation',19000,403,3), ('程咬铜','male',18,'20150411','operation',18000,403,3), ('程咬铁','female',18,'20140512','operation',17000,403,3); # 当表字段特别多展示的时候错乱可以使用\G分行展示 select * from emp\G; # 个别同学的电脑在插入中文的时候还是会出现乱码或者空白的现象你可以将字符编码统一设置成GBK

# 书写顺序 select id,name from emp where id > 3; # 执行顺序 from where select """ 虽然执行顺序和书写顺序不一致你在写sql语句的时候可能不知道怎么写 你就按照书写顺序的方式写sql select * 先用*号占位 之后去补全后面的sql语句 最后将*号替换后你想要的具体字段 明天会一直使用这里先理解 """

where筛选条件

# 作用:是对整体数据的一个筛选操作 # 1.查询id大于等于3小于等于6的数据 select id,name,age from emp where id>=3 and id<=6; select id,name from emp where id between 3 and 6; 两者等价 # 2.查询薪资是20000或者18000或者17000的数据 select * from emp where salary=20000 or salary=18000 or salary=17000; select * from emp where salary in (20000,18000,17000); # 3.查询员工姓名中包含字母o的员工的姓名和薪资 """ 模糊查询 like % 匹配任意多个字符 _ 匹配任意单个字符 """ select name,salary from emp where name like '%o%'; # 4.查询员工姓名是由四个字符组成的姓名和薪资 char_length() _ select name,salary from emp where name like '____'; select name,salary from emp where char_length(name) = 4; # 5.查询id小于3或者id大于6的数据 select * from emp where id not between 3 and 6; # 6.查询薪资不在20000,18000,17000范围的数据 select * from emp where salary not in (20000,18000,17000); # 7.查询岗位描述为空的员工姓名和岗位名针对null不用等号用is select name,post from emp where post_comment = NULL; select name,post from emp where post_comment is NULL;

group by分组

# 分组实际应用场景分组应用场景非常的多 男女比例 部门平均薪资 部门秃头率 国家之间数据统计 # 1 按照部门分组 select * from emp group by post; """ 分组之后最小可操作单位应该是组还不再是组内的单个数据 上述命令在你没有设置严格模式的时候是可正常执行的返回的是分组之后每个组的第一条数据但是这不符合分组的规范:分组之后不应该考虑单个数据而应该以组为操作单位(分组之后没办法直接获取组内单个数据) 如果设置了严格模式那么上述命令会直接报错 """ set global sql_mode = 'strict_trans_tables,only_full_group_by'; 设置严格模式之后分组默认只能拿到分组的依据 select post from emp group by post; 按照什么分组就只能拿到分组其他字段不能直接获取需要借助于一些方法(聚合函数) """ 什么时候需要分组啊？？？ 关键字 每个平均最高最低 聚合函数 max min sum count avg """ # 1.获取每个部门的最高薪资 select post,max(salary) from emp group by post; select post as '部门',max(salary) as '最高薪资' from emp group by post; select post '部门',max(salary) '最高薪资' from emp group by post; # as可以给字段起别名也可以直接省略不写但是不推荐因为省略的话语意不明确容易错乱 # 2.获取每个部门的最低薪资 select post,min(salary) from emp group by post; # 3.获取每个部门的平均薪资 select post,avg(salary) from emp group by post; # 4.获取每个部门的工资总和 select post,sum(salary) from emp group by post; # 5.获取每个部门的人数 select post,count(id) from emp group by post; # 常用符合逻辑 select post,count(salary) from emp group by post; select post,count(age) from emp group by post; select post,count(post_comment) from emp group by post; null不行 # 6.查询分组之后的部门名称和每个部门下所有的员工姓名 # group_concat不单单可以支持你获取分组之后的其他字段值还支持拼接操作 select post,group_concat(name) from emp group by post; select post,group_concat(name,'_DSB') from emp group by post; select post,group_concat(name,':',salary) from emp group by post; # concat不分组的时候用 select concat('NAME:',name),concat('SAL:',salary) from emp; # 补充 as语法不单单可以给字段起别名还可以给表临时起别名 select emp.id,emp.name from emp; select emp.id,emp.name from emp as t1; 报错 select t1.id,t1.name from emp as t1; # 查询每个人的年薪 12薪 select name,salary*12 from emp;

分组注意事项

# 关键字where和group by同时出现的时候group by必须在where的后面 where先对整体数据进行过滤之后再分组操作 where筛选条件不能使用聚合函数 select id,name,age from emp where max(salary) > 3000; select max(salary) from emp; # 不分组默认整体就是一组 # 统计各部门年龄在30岁以上的员工平均薪资 1 先求所有年龄大于30岁的员工 select * from emp where age>30; 2 再对结果进行分组 select * from emp where age>30 group by post; select post,avg(salary) from emp where age>30 group by post;

having分组之后的筛选条件

""" having的语法根where是一致的 只不过having是在分组之后进行的过滤操作 即having是可以直接使用聚合函数的 """ # 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门 select post,avg(salary) from emp where age>30 group by post having avg(salary) > 10000 ;

distinct去重

""" 一定要注意必须是完全一样的数据才可以去重！！！ 一定不要将主键忽视了有主键存在的情况下是不可能去重的 [ {'id':1,'name':'jason','age':18}, {'id':2,'name':'jason','age':18}, {'id':3,'name':'egon','age':18} ] ORM 对象关系映射让不懂SQL语句的人也能够非常牛逼的操作数据库 表类 一条条的数据对象 字段对应的值对象的属性 你再写类就意味着在创建表 用类生成对象就意味着再创建数据 对象点属性就是在获取数据字段对应的值 目的就是减轻python程序员的压力只需要会python面向对象的知识点就可以操作MySQL """ select distinct id,age from emp; select distinct age from emp;

order排序

select * from emp order by salary; select * from emp order by salary asc; select * from emp order by salary desc; """ order by默认是升序 asc 该asc可以省略不写 也可以修改为降序 desc """ select * from emp order by age desc,salary asc; # 先按照age降序排如果碰到age相同则再按照salary升序排 # 统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门,然后对平均工资降序排序 select post,avg(salary) from emp where age>10 group by post having avg(salary) > 1000 order by avg(salary) desc ;

limit限制展示条数

select * from emp; """针对数据过多的情况我们通常都是做分页处理""" select * from emp limit 3; # 只展示三条数据 select * from emp limit 0,5; select * from emp limit 5,5; 第一个参数是起始位置 第二个参数是展示条数

正则

mysql也支持正则

select * from emp where name regexp '^j.*(n|y)$';

多表操作

前期表准备

#建表 create table dep( id int, name varchar(20) ); create table emp( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'male', age int, dep_id int ); #插入数据 insert into dep values (200,'技术'), (201,'人力资源'), (202,'销售'), (203,'运营'); insert into emp(name,sex,age,dep_id) values ('jason','male',18,200), ('egon','female',48,201), ('kevin','male',18,201), ('nick','male',28,202), ('owen','male',18,203), ('jerry','female',18,204);

多表查询

select * from dep,emp; # 结果笛卡尔积 """ 了解即可不知道也没关系 """ select * from emp,dep where emp.dep_id = dep.id; """ MySQL也知道你在后面查询数据过程中肯定会经常用到拼表操作 所以特地给你开设了对应的方法 inner join 内连接 left join 左连接 right join 右连接 union 全连接 """ # inner join 内连接 select * from emp inner join dep on emp.dep_id = dep.id; # 只拼接两张表中公有的数据部分 # left join 左连接 select * from emp left join dep on emp.dep_id = dep.id; # 左表所有的数据都展示出来没有对应的项就用NULL # right join 右连接 select * from emp right join dep on emp.dep_id = dep.id; # 右表所有的数据都展示出来没有对应的项就用NULL # union 全连接左右两表所有的数据都展示出来 select * from emp left join dep on emp.dep_id = dep.id union select * from emp right join dep on emp.dep_id = dep.id;

子查询

""" 子查询就是我们平时解决问题的思路 分步骤解决问题 第一步 第二步 ... 将一个查询语句的结果当做另外一个查询语句的条件去用 """ # 查询部门是技术或者人力资源的员工信息 1 先获取部门的id号 2 再去员工表里面筛选出对应的员工 select id from dep where name='技术' or name = '人力资源'; select name from emp where dep_id in (200,201); select * from emp where dep_id in (select id from dep where name='技术' or name = '人力资源');

总结

表的查询结果可以作为其他表的查询条件 也可以通过起别名的方式把它作为一个张虚拟表根其他表关联 """ 多表查询就两种方式 先拼接表再查询 子查询一步一步来 """

navicat软件

练习题

""" 课下一定要把握上课将的这几道题全部自己独立的理解并写出来 在解决sql查询问题的时候不要慌 一步一步慢慢来最终能够东拼西凑出来就过关了！！！ """ -- 1、查询所有的课程的名称以及对应的任课老师姓名 -- SELECT -- course.cname, -- teacher.tname -- FROM -- course -- INNER JOIN teacher ON course.teacher_id = teacher.tid; -- 4、查询平均成绩大于八十分的同学的姓名和平均成绩 -- SELECT -- student.sname, -- t1.avg_num -- FROM -- student -- INNER JOIN ( -- SELECT -- score.student_id, -- avg( num ) AS avg_num -- FROM -- score -- INNER JOIN student ON score.student_id = student.sid -- GROUP BY -- score.student_id -- HAVING -- AVG( num ) > 80 -- ) AS t1 ON student.sid = t1.student_id; -- 7、查询没有报李平老师课的学生姓名 # 分步操作 # 1 先找到李平老师教授的课程id # 2 再找所有报了李平老师课程的学生id # 3 之后去学生表里面取反就可以获取到没有报李平老师课程的学生姓名 -- SELECT -- student.sname -- FROM -- student -- WHERE -- sid NOT IN ( -- SELECT DISTINCT -- score.student_id -- FROM -- score -- WHERE -- score.course_id IN ( SELECT course.cid FROM teacher INNER JOIN course ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师' ) -- ); -- 8、查询没有同时选修物理课程和体育课程的学生姓名 -- (只要选了一门的选了两门和没有选的都不要) # 1 先查物理和体育课程的id # 2 再去获取所有选了物理和体育的学生数据 # 3 按照学生分组利用聚合函数count筛选出只选了一门的学生id # 4 依旧id获取学生姓名 -- SELECT -- student.sname -- FROM -- student -- WHERE -- student.sid IN ( -- SELECT -- score.student_id -- FROM -- score -- WHERE -- score.course_id IN ( SELECT course.cid FROM course WHERE course.cname IN ( '物理', '体育' ) ) -- GROUP BY -- score.student_id -- HAVING -- COUNT( score.course_id ) = 1 -- ); -- 9、查询挂科超过两门(包括两门)的学生姓名和班级 # 1 先筛选出所有分数小于60的数据 # 2 按照学生分组对数据进行计数获取大于等于2的数据 SELECT class.caption, student.sname FROM class INNER JOIN student ON class.cid = student.class_id WHERE student.sid IN ( SELECT score.student_id FROM score WHERE score.num < 60 GROUP BY score.student_id HAVING COUNT( score.course_id ) >= 2 );

sql注入

""" 利用一些语法的特性书写一些特点的语句实现固定的语法 MySQL利用的是MySQL的注释语法 select * from user where name='jason' -- jhsadklsajdkla' and password='' select * from user where name='xxx' or 1=1 -- sakjdkljakldjasl' and password='' """ 日常生活中很多软件在注册的时候都不能含有特殊符号 因为怕你构造出特定的语句入侵数据库不安全 # 敏感的数据不要自己做拼接交给execute帮你拼接即可 # 结合数据库完成一个用户的登录功能？ import pymysql conn = pymysql.connect( host = '127.0.0.1', port = 3306, user = 'root', password = '123456', database = 'day48', charset = 'utf8' # 编码千万不要加- ) # 链接数据库 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) username = input('>>>:') password = input('>>>:') sql = "select * from user where name=%s and password=%s" # 不要手动拼接数据先用%s占位之后将需要拼接的数据直接交给execute方法即可 print(sql) rows = cursor.execute(sql,(username,password)) # 自动识别sql里面的%s用后面元组里面的数据替换 if rows: print('登录成功') print(cursor.fetchall()) else: print('用户名密码错误')

数据库介绍

数据库的本质：本质其实就是一款基于网络通信的应用程序

关系型数据库

1. 数据之间彼此有关系或约束

2. 存储数据的变现形式通常是以表格存储

非关系性数据库

存储数据通常是以k，v键值对的形式

MySQL的安装

网站：www.mysql.com

目录结构

客户端：bin目录下的mysql.exe

服务端：bin目录下的mysqld.exe

启动

以管理员身份运行，因为部分命令仅管理员身份有效

服务端：先切换到mysqld所在目录下，然后输入mysqld即可启动服务端

客户端：保留原来窗口，重新打开cmd窗口客户端链接服务端完整命令：

mysql -h 127.0.0.1 -P 3306 -uroot -p

环境变量配置

将bin目录添加到环境变量中

将mysql服务端制作成系统服务（开机自启）

安装mysql服务：mysqld --install

卸载mysql服务：mysqld --remove

统一编码配置

my-default.ini 文件是默认配置模板，程序启动会先夹在配置文件中的配置之后才真正的启动

[mysqld] # 一旦服务端启动立刻加载这下面的配置 [mysql] # 一旦客户端启动立刻加载这下面的配置 [client] # 其他客户端

新建一个my.ini配置文件，然后添加配置，修改配置文件后一定要重启服务才能生效

[mysqld] character-set-server=utf8 collation-server=utf8_general_ci [client] default-character-set=utf8 [mysql] user="root" # 此是直接将用户名密码添加到配置文件中，使启动mysql时候，直接以root账号登录 password=123456 default-character-set=utf8

SQL语句初识

1. MySQl中的sql语句是以分号作为结束的标志

2. 基本命令

查看所有库名：show databases;

3. 连接服务的的命令简写：mysql -uroot -p（默认本地，默认端口形式连接）

4. 当你输入的命令不对，又不想让服务端执行并返回错误信息是，可以用 /c 来取消

5. 客户端退出：退出命令加不加分好都可以执行

quit

exit

6. 当你在连接服务端的时候，只输入mysql也能链接，但此时不是管理员身份，而是一个游客身份

7. 设置密码：

mysqladmin -uroot -p原密码 password 新密码

示例：mysqladmin -uroot -p123 password 123456

8. 重置密码：

1. 先关闭当前mysql服务端；然后以命令行方式启动服务端（让mysql跳过用户名密码验证功能）

mysqld --skip-grant-tables

2. 以无密码方式连接

mysql -uroot -p 直接回车

3. 修改当前用户的密码：

update mysql.user set password=password(123456) where user='root' and host='localhost';

4. 立刻将修改数据刷到硬盘

flush privileges;

5. 关闭当前服务端，然后以正常校验授权表的形式启动

针对库的增删改查（文件夹）

增

create database db1;

新增库，并制定编码格式： cerate database db2 charset='gbk';

查

查所有库： show databases;

查单个库，且查询的是创建库的语句： show create database db1;

改

alter database db2 charset='utf8';

删

drop database db2;

针对表的增删改查（文件）

在操作表（文件）的时候，需要指定所在的库（文件夹）

查看当前所在库的名字

select database();

切换库

use db1;

也可以用绝对路径的形式操作不同的库

create table db2.t1(id int);

增

create tabale t1(id int,name char(4));

查

查看当前库下所有的表名： show tables;

查看某个表的创建语句： show create table t1;

查看表结构： describe t1; 简写：desc t1;

改

alter table t1 modify name char(16);

删

drop table t1;

针对数据的增删改查（一行行数据）

一定要先有库，有表，才能操作记录

增

插入一条数据： inster into t1 value(1,'jason');

插入多条数据： inster into t1 value(1,'jason'),(2,'egon'),(3,'tank');

查

当数据量特别大的时候，不建议使用该命令 select * from t1;

查询该表中，所有的name字段 select name from t1;

改

update t1 set name='dsb' where id > 1;

删

delete from t1 where id > 1;

delete from t1 where name='jason';

将表所有的数据清空

delete from t1;

存储引擎

介绍

innodb

是MySQL5.5版本及之后默认的存储引擎 数据存储更加安全

myisam

是MySQL5.5版本之前默认的存储引擎 速度要比innodb快，但是我们更加注重的是数据的安全

memory

内存引擎（数据全部存放在内存中）断电数据丢失

blackhole

无论什么时候，都立刻消失（黑洞）

使用

查看所有的存储引擎

show engines;

不同的存储引擎在存储表的时候，异同点

create table t1(id int) engine=innodb;

create table t2(id int) engine=myisam;

create table t3(id int) engine=blackhole;

create table t4(id int) engine=memory;

基本数据类型

创建表的完整语法

语法

create table 表名( 字段名1 类型(宽度) 约束条件, 字段名2 类型(宽度) 约束条件, 字段名3 类型(宽度) 约束条件 )

注意

1. 同一张表中字段名不能重复

2. 宽度和约束条件是可选的（可写可不写）而字段名和 字段类型是必须的约束条件写的话，也支持写多个

字段名1 类型(宽度) 约束条件1 约束条件2..., create table t5(id); 报错

3. 最后一行不能有逗号

create table t6( id int, name char, ); 报错

补充

宽度

一般情况下指的是对存储数据的限制

create table t7(name char); # 默认宽度是1 insert into t7 values('jason');  # 会自动截取 insert into t7 values(null); #  会存关键字NULL

5.6版本默认没有开启严格模式，规定只能存一个字符， 你给了多个字符，那么MySQL会自动帮你截取

5.7版本及以上或者开启了严格模式，那么规定只能存几个，就不能超， 一旦超出范围，会立刻报错  Data too long for ...

严格模式到底开不开呢？

5.7以后的版本默认都是开启严格模式的

使用数据库的准则： 能尽量少的让数据库干活就尽量少，不要给数据库增加额外的压力

约束条件 null not null 不能插入null

create table t8(id int,name char not null);

总结： 宽度和约束条件到底是什么关系

宽度是用来限制数据的存储 约束条件是在宽度的基础之上增加的额外的约束

严格模式

查看严格模式

show variables like '%mode';

'%mode' 模糊匹配/查询     %:匹配任意多个字符     _:匹配任意单个字符

修改严格模式

set session  # 只在当前窗口有效 set global  # 全局有效 set global sql_mode = 'STRICT_TRANS_TABLES'; 修改完之后，冲新进入服务端即可

修改sql_mode，让M有SQL不要做自动剔除空格操作 set global sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';

整型

分类 tinyint smallint meduimnt int bigint

作用： 存储年龄、等级、id、号码等

以tinyint为例

是否有符号 默认情况下是带符号的

超出会如何： 超出限制只存最大可接受值

create table t9(id tinyint); insert into t9 values(-129),(256);

约束条件之unsigned。无符号

create table t10(id tinyint unsigned);

int 也是默认带符号的，整型默认情况下都是带有符号的

只有整形括号里面的数字不是表示限制位数 id int(8)     如果数字没有超出8位，那么默认用空格填充至8位     如果数字超出了8位，那么有几位就存几位(但是还是要遵守最大范围) create table t13(id int(8) unsigned zerofill);  # 用0填充至8位 针对整型字段，括号内无需指定宽度，因为它默认的宽度（11位）足够显示 所有的数据了

浮点型

分类： FLOAT DOUBLE DECIMAL

作用： 身高、体重、薪资

存储限制： float(255,30) # 总共255位小数部分占30位 double(255,30) # 总共255位小数部分占30位 decimal(65,30) # 总共65位小数部分占30位

精确度验证： create table t15(id float(255,30)); create table t16(id double(255,30)); create table t17(id decimal(65,30));

float < double < decimal # 要结合实际应用场景三者都能使用

字符类型

char     定长     char(4)  数据超过4个字符直接报错，不够4个字符空格不全 varchar     变长     varchar(4)  数据超过4个字符直接报错，不够有几个存几个

create table t18(name char(4)); create table t19(name varchar(4));

char_length统计字段长度

select char_length(name) form t18;

char与varchar对比

char     缺点：浪费空间     优点：存取都很简单          直接按照固定字符存储数据即可          存按照5个字符存，取也直接按照5个字符取，速度就会相对varchar快

varchar      优点：节省空间      缺点：存取较为麻烦            存的时候需要制作报头            取的时候也要先读取报头，之后才能读取真实数据

时间类型

date：年月日  2021-6-20

datetime：年月日时分秒 2021-6-20 11:11:11

time：时分秒 11:11:11

Year：2021

create table student( id int, name varchar(16), born_year year, birth date, study_time time, reg_time datetime ); insert into student values(1,'egon','1880','1880-11-11','11:11:11','2020-11-11 11:11:11');

枚举与集合类型

分类：     枚举（enum）：多选一     集合（set）：多选多

create table user( id int, name char(16), gender enum('male','female','others') ); insert into user values(1,'jason','male'); 正常 insert into user values(2,'egon','xxxxooo'); 报错 # 枚举字段后期在存数据的时候只能从枚举里面选择一个存储 create table teacher( id int, name char(16), gender enum('male','female','others'), hobby set('read','DBJ','hecha') ); insert into teacher values(1,'jason','male','read'); 正常 insert into teacher values(2,'egon','female','DBJ,hecha'); 正常 insert into teacher values(3,'tank','others','生蚝'); 报错 # 集合可以只写一个但是不能写没有列举的

约束条件

default默认值

# 补充知识点插入数据的时候可以指定字段 create table t1( id int, name char(16) ); insert into t1(name,id) values('jason',1); create table t2( id int, name char(16), gender enum('male','female','others') default 'male' ); insert into t2(id,name) values(1,'jason'); insert into t2 values(2,'egon','female');

unique唯一

# 单列唯一 create table t3( id int unique, name char(16) ); insert into t3 values(1,'jason'),(1,'egon'); insert into t3 values(1,'jason'),(2,'egon');

# 联合唯一 """ ip和port 单个都可以重复但是加载一起必须是唯一的 """ create table t4( id int, ip char(16), port int, unique(ip,port) ); insert into t4 values(1,'127.0.0.1',8080); insert into t4 values(2,'127.0.0.1',8081); insert into t4 values(3,'127.0.0.2',8080); insert into t4 values(4,'127.0.0.1',8080); 报错

primary key主键

# 1 一张表中有且只有一个主键如果你没有设置主键那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键 create table t6( id int, name char(16), age int not null unique, addr char(32) not null unique );

# 2 如果表中没有主键也没有其他任何的非空且唯一字段那么Innodb会采用自己内部提供的一个隐藏字段作为主键，隐藏意味着你无法使用到它就无法提示查询速度

# 3 一张表中通常都应该有一个主键字段并且通常将id/uid/sid字段作为主键 以后我们在创建表的时候id字段一定要加primary key

# 单个字段主键 create table t5( id int primary key name char(16) );

# 联合主键(多个字段联合起来作为表的主键本质还是一个主键) create table t7( ip char(16), port int, primary key(ip,port) );

auth_increment自增

# 当编号特别多的时候人为的去维护太麻烦 create table t8( id int primary key auto_increment, name char(16) ); insert into t8(name) values('jason'),('egon'),('kevin');

# 注意auto_increment通常都是加在主键上的不能给普通字段加 create table t9( id int primary key auto_increment, name char(16), cid int auto_increment ); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

delete from t1 删除表中数据后主键的自增不会停止 truncate t1 清空表数据并且重置主键

结论

以后在创建表的id(数据的唯一标识id、uid、sid)字段的时候 id int primary key auto_increment

视图

什么是视图

视图就是通过查询得到一张虚拟表，然后保存下来，下次可以直接使用 其实视图也是表

为什么要用视图

如果要频繁的操作一张虚拟表(拼表组成的)，你就可以制作成视图后续直接操作

如何操作

# 固定语法 create view 表名 as 虚拟表的查询sql语句 # 具体操作 create view teacher2course as select * from teacher INNER JOIN course on teacher.tid = course.teacher_id ;

注意：

1 创建视图在硬盘上只会有表结构没有表数据(数据还是来自于之前的表) 2 视图一般只用来查询里面的数据不要继续修改可能会影响真正的表 3 视图基本上不用不好维护

触发器

概念

在满足对表数据进行增、删、改的情况下，自动触发的功能 使用触发器可以帮助我们实现监控、日志... 触发器可以在六种情况下自动触发增前增后删前删后改前改后

语法结构

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

# 具体使用针对触发器的名字我们通常需要做到见名知意 # 针对增 create trigger tri_before_insert_t1 before insert on t1 for each row begin sql语句 end create trigger tri_after_insert_t1 after insert on t1 for each row begin sql语句 end """针对删除和修改书写格式一致""" ps:修改MySQL默认的语句结束符只作用于当前窗口 delimiter $$ 将默认的结束符号由;改为$$ delimiter ;

案例

建表

CREATE TABLE cmd ( id INT PRIMARY KEY auto_increment, USER CHAR (32), priv CHAR (10), cmd CHAR (64), sub_time datetime, #提交时间 success enum ('yes', 'no') #0代表执行失败 ); CREATE TABLE errlog ( id INT PRIMARY KEY auto_increment, err_cmd CHAR (64), err_time datetime );

当cmd表中的记录succes字段是no那么就触发触发器的执行去errlog表中插入数据 NEW指代的就是一条条数据对象

写触发器

delimiter $$ create trigger tri_after_insert_cmd after insert on cmd for each row begin if NEW.success = 'no' then insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time); end if; end $$ delimiter ;

朝cmd表插入数据

INSERT INTO cmd ( USER, priv, cmd, sub_time, success ) VALUES ('jason','0755','ls -l /etc',NOW(),'yes'), ('jason','0755','cat /etc/passwd',NOW(),'no'), ('jason','0755','useradd xxx',NOW(),'no'), ('jason','0755','ps aux',NOW(),'yes');

删除触发器

drop trigger tri_after_insert_cmd;

事务

什么是事务

开启一个事务可以包含多条sql语句这些sql语句要么同时成功 要么一个都别想成功称之为事务的原子性

事务的作用

""" 保证了对数据操作的安全性 """ eg:还钱的例子 egon用银行卡给我的支付宝转账1000 1 将egon银行卡账户的数据减1000块 2 将jason支付宝账户的数据加1000块 你在操作多条数据的时候可能会出现某几条操作不成功的情况

事务的四大特性

""" ACID A:原子性 一个事务是一个不可分割的单位，事务中包含的诸多操作 要么同时成功要么同时失败 C:一致性 事务必须是使数据库从一个一致性的状态变到另外一个一致性的状态 一致性跟原子性是密切相关的 I:隔离性 一个事务的执行不能被其他事务干扰 （即一个事务内部的操作及使用到的数据对并发的其他事务是隔离的，并发执行的事务之间也是互相不干扰的） D:持久性 也叫"永久性" 一个事务一旦提交成功执行成功那么它对数据库中数据的修改应该是永久的 接下来的其他操作或者故障不应该对其有任何的影响 """

如何使用事务

# 事务相关的关键字 # 1 开启事务 start transaction; # 2 回滚(回到事务执行之前的状态) rollback; # 3 确认(确认之后就无法回滚了) commit;

"""模拟转账功能""" create table user( id int primary key auto_increment, name char(16), balance int ); insert into user(name,balance) values ('jason',1000), ('egon',1000), ('tank',1000); # 1 先开启事务 start transaction; # 2 多条sql语句 update user set balance=900 where name='jason'; update user set balance=1010 where name='egon'; update user set balance=1090 where name='tank'; """ 总结 当你想让多条sql语句保持一致性要么同时成功要么同时失败 你就应该考虑使用事务 """

存储过程

存储过程就类似于python中的自定义函数 它的内部包含了一系列可以执行的sql语句，存储过程存放于MySQL服务端中，你可以直接通过调用存储过程触发内部sql语句的执行

基本使用

create procedure 存储过程的名字(形参1,形参2,...) begin sql代码 end # 调用 call 存储过程的名字();

存储过程演示

delimiter $$ create procedure p1( in m int, # 只进不出 m不能返回出去 in n int, out res int # 该形参可以返回出去 ) begin select tname from teacher where tid>m and tid<n; set res=666; # 将res变量修改用来标识当前的存储过程代码确实执行了 end $$ delimiter ; # 针对形参res 不能直接传数据应该传一个变量名 # 定义变量 set @ret = 10; # 执行存储过程 call p1(3,5,@ret); # 查看变量对应的值 select @ret;

pymysql模块中如何调用存储过程

import pymysql conn = pymysql.connect( host = '127.0.0.1', port = 3306, user = 'root', passwd = '123456', db = 'day48', charset = 'utf8', autocommit = True ) cursor = conn.cursor(pymysql.cursors.DictCursor) # 调用存储过程 cursor.callproc('p1',(1,5,10)) """ @_p1_0=1 @_p1_1=5 @_p1_2=10 """ # print(cursor.fetchall()) cursor.execute('select @_p1_2;') print(cursor.fetchall())

函数

跟存储过程是有区别的，存储过程是自定义函数，函数就类似于是内置函数

自行了解

('jason','0755','ls -l /etc',NOW(),'yes') CREATE TABLE blog ( id INT PRIMARY KEY auto_increment, NAME CHAR (32), sub_time datetime ); INSERT INTO blog (NAME, sub_time) VALUES ('第1篇','2015-03-01 11:31:21'), ('第2篇','2015-03-11 16:31:21'), ('第3篇','2016-07-01 10:21:31'), ('第4篇','2016-07-22 09:23:21'), ('第5篇','2016-07-23 10:11:11'), ('第6篇','2016-07-25 11:21:31'), ('第7篇','2017-03-01 15:33:21'), ('第8篇','2017-03-01 17:32:21'), ('第9篇','2017-03-01 18:31:21'); select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

流程控制

# 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 7; 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 ;

索引

""" 1 当表中有大量数据存在的前提下创建索引速度会很慢 2 在索引创建完毕之后对表的查询性能会大幅度的提升但是写的性能也会大幅度的降低 """ 索引不要随意的创建！！！

b+树

""" 只有叶子节点存放的是真实的数据其他节点存放的是虚拟数据仅仅是用来指路的 树的层级越高查询数据所需要经历的步骤就越多(树有几层查询数据就需要几步) 一个磁盘块存储是有限制的 为什么建议你将id字段作为索引 占得空间少一个磁盘块能够存储的数据多 那么久降低了树的高度从而减少查询次数 """

聚集索引（primary key）

""" 聚集索引指的就是主键 Innodb 只有两个文件直接将主键存放在了idb表中 MyIsam 三个文件单独将索引存在一个文件 """

辅助索引(unique,index)

查询数据的时候不可能一直使用到主键，也有可能会用到name,password等其他字段 那么这个时候你是没有办法利用聚集索引。这个时候你就可以根据情况给其他字段设置辅助索引(也是一个b+树）

""" 叶子节点存放的是数据对应的主键值 先按照辅助索引拿到数据的主键值 之后还是需要去主键的聚集索引里面查询数据 """

覆盖索引

在辅助索引的叶子节点就已经拿到了需要的数据

# 给name设置辅助索引 select name from user where name='jason'; # 非覆盖索引 select age from user where name='jason';