mysql相关操作
14.innodb 在只有frm和ibd文件的情况下,如何恢复数据
1.基础操作
# [快捷键] 快捷键:ctrl + l 清屏 快捷键:ctrl + c 终止 exit : 退出数据库 \q : 退出 \G : 垂直显示 # [linux] mysql服务命令 service mysql stop service mysql start service mysql restart # [windows] mysql服务命令 net stop mysql net start mysql # 数据库内部方法 select user() select concat() select database() select now() # 登录的完整语法 # (1) 登录 mysql -u用户 -p密码 -hip地址 mysql -uroot -p -h默认本地ip localhost => 127.0.0.1 # (2) 退出 exit 或者 \q # 查询当前登录用户 select user() # (1)设置密码 set password = password("123456") # (2)去除密码 set password = password("");
2.设置权限及授权
VMnet8: nat VMnet1:host-only ipconfig [windows] ifconfig[linux] # 给具体某个ip设置一个账户连接linux create user "ceshi100"@"192.168.126.1" identified by "111"; # 给具体192.168.126.% 这个网段下的所有ip设置账户 create user "ceshi101"@"192.168.126.%" identified by "222"; # 给所有ip下的主机设置账户 create user "ceshi102"@"%" identified by "333"; USAGE 默认没有任何权限 # 查看具体某个ip下的用户权限 show grants for "ceshi102"@"%"; +--------------------------------------+ | Grants for ceshi102@% | +--------------------------------------+ | GRANT USAGE ON *.* TO 'ceshi102'@'%' | +--------------------------------------+ # 授权语法 grant 权限 on 数据库.表 to "用户名"@"ip地址" identified by "密码"; """ select 查询数据的权限 insert 添加数据的权限 update 更改数据的权限 delete 删除数据的权限 * 所有 """ # 授予查询权限 grant select,insert on *.* to "ceshi102"@"%" identified by "333"; # 授予所有权限 grant all on *.* to "ceshi102"@"%" identified by "333"; # 移除删除权限(删除数据库/表) revoke drop on *.* from "ceshi102"@"%" # 移除所有权限 revoke all on *.* from "ceshi102"@"%" # 刷新权限,立刻生效 flush privileges
3.操作数据库、数据表、记录
""" mysql命令中,不区分大小写 [linux]路径 sudo find / -name db001 sudo su root 切换到最高权限账户 cd mysql /var/lib/mysql/数据库... [windows]路径 D:\MySQL5.7\mysql-5.7.25-winx64\data """ # (1) 操作数据库 [文件夹] 增: # 创建数据库 create database db001 charset utf8; 查: # 查看数据库 show databases; # 查看建库语句; show create database db001; 改: alter database db002 charset gbk; 删: # 删除数据库 drop database db001 # (2) 操作数据表 [文件] 增: # 选择数据库 use db001 # 创建表 create table t1(id int , name char); 查: # 查看所有表 show tables; # 查看建表语句 show create table t1; # modify 只能改变类型 alter table t1 modify name char(5); # change 改变类型+字段名 alter table t1 change name name123 char(4); # add 添加字段 alter table t1 add age int; # drop 删除字段 alter table t1 drop age; # rename 更改表明 alter table t1 rename t1111111; 删: drop table t1; # (3) 操作记录 [文件的内容] 增: # 一次插入一条数据 insert into t1(id,name) values(1,'abcd'); # 一次插入多条数据 insert into t1(id,name) values(2,"王文"),(3,"刘文波"),(4,"康裕康"),(5,"张保障"); # 不指定具体字段,默认把字段全部插一遍 insert into t1 values(6,"沈思雨"); # 可以具体指定某个字段进行插入 insert into t1(name) values("张宇"); 查: # * 所有 select * from t1; # 查询单个字段 select id from t1; # 查询多个字段 select id,name from t1; 改: # update 表名 set 字段=值 where 条件 update t1 set name="王伟" where id = 2; # 不加条件有风险,一改全改,一定加where update t1 set name="王伟" ; 删: # 删除的时候,必须加上where delete from t1 where id = 1; # 删除所有数据,一删全删,一定加where delete from t1; # 删除所有 (数据+重置id) truncate table t1;
4.常用数据类型
整型
# 整型 tinyint 1个字节 有符号范围(-128~127) 无符号(0~255) unsigned 小整型值 int 4个字节 有符号范围(-21亿 ~ 21亿左右) 无符号(0~42亿) 大整型值 create table t3(id int , sex tinyint); insert into t3(id,sex) values(4000000000,127) error out of range insert into t3(id,sex) values(13,128) error Out of range insert into t3(id,sex) values(13,127);
浮点型
# 浮点型 float(255,30) 单精度 double(255,30) 双精度 decimal(65,30) 金钱类型 (用字符串的形式来存储小数) create table t4(f1 float(5,3) , f2 double(5,3) , f3 decimal(5,3) ); insert into t4 values(1.7777777777777777777777777,1.7777777777777777777777777,1.7777777777777777777777777); insert into t4 values(11.7777777777777777777777777,11.7777777777777777777777777,11.7777777777777777777777777); insert into t4 values(111.7777777777777777777777777,111.7777777777777777777777777,111.7777777777777777777777777); error out of range insert into t4 values(1.7,1.7,1.7); error 整数位最多保留2位 , 小数位最多保留3位;存在四舍五入 # float 小数位默认保留5位,double 小数位默认保留16位,decimal 默认保留整数,四舍五入 create table t5(f1 float , f2 double , f3 decimal); insert into t5 values(1.7777777777777777777777777,1.7777777777777777777777777,1.7777777777777777777777777); create table t6(f1 float(7,3)); insert into t6 values(1234.5678); +----------+ | f1 | +----------+ | 1234.568 | +----------+ # 整数位最多保留4位,小数位最多保留3位 # 默认double保留的小数位更多,float保留的小数位少;decimal保留整数位 insert into t6 values(12345.67);
字符串类型
# 字符串 char(字符长度) varchar(字符长度) char(11) 定长:固定开辟11个字符长度的空间(手机号,身份证号),开辟空间的速度上来说比较快,从数据结构上来说,需谨慎,可能存在空间浪费. max = 255 varchar(11) 变长:动态最多开辟11个字符长度的空间(评论,广告),开辟空间的速度上来说相对慢,从数据结构上来说,推荐使用,不存在空间浪费 max = 21845 text 文本类型:针对于文章,论文,小说. max > varchar create table t7(c char(11), v varchar(11) , t text); insert into t7 values("11111","11111","11111"); insert into t7 values("你好啊你好啊你好啊你好","你好啊你好啊你好啊你好","你好啊你好啊你好啊你好"); # concat 可以把各个字段拼接在一起 select concat(c,"<=>",v,"<=>",t) from t7; # 枚举和集合 enum 枚举 : 从列出来的数据当中选一个 (性别) set 集合 : 从列出来的数据当中选多个 (爱好) create table t8( id int , name varchar(10) , sex enum("男性","兽性","人妖") , money float(5,3) , hobby set("吃肉","抽烟","喝酒","打麻将","嫖赌") ); # 正常写法 insert into t8(id,name,sex , money , hobby) values(1,"张三","兽性",2.6,"打麻将,吃肉,嫖赌"); # 自动去重 insert into t8(id,name,sex , money , hobby) values(1,"张三","兽性",2.6,"打麻将,吃肉,嫖赌,嫖赌,嫖赌,嫖赌,嫖赌,嫖赌"); # 异常写法 : 不能选择除了列出来的数据之外的其他值 error 报错 insert into t8(id,name,sex , money , hobby) values(1,"张三","人妖12",2.6,"打麻将,吃肉,嫖赌12");
时间类型
date YYYY-MM-DD 年月日 (节假日,纪念日) time HH:MM:SS 时分秒 (体育竞赛,记录时间) year YYYY 年份 (历史,酒的年份) datetime YYYY-MM-DD HH:MM:SS 年月日 时分秒 (上线时间,下单时间) create table t1(d date, t time , y year , dt datetime); insert into t1 values("2020-11-3","9:19:30","2020","2020-11-3 9:19:30"); insert into t1 values(now(),now(),now(),now()); timestamp YYYYMMDDHHMMSS(时间戳) 自动更新时间 (不需要手动写入,自动实现更新记录,[用作记录修改的时间]) create table t2(dt datetime , ts timestamp); insert into t2 values(20201103092530 , 20201103092530); insert into t2 values(null,null); # 区别 timestamp 自动更新时间(以当前时间戳) datetime没有 insert into t2 values(20390102101010 , 20390102101010); error # 超越2038
5.约束
# 约束 : 对编辑的数据进行类型的限制,不满足约束条件的报错 unsigned : 无符号 not null : 不为空 default : 默认值 unique : 唯一值,加入唯一索引 (索引相当于字典目录,索引的提出是为了加快速度,一味地乱加索引不会提高查询效率) primary key: 主键 auto_increment: 自增加一 zerofill : 零填充 foreign key: 外键 # unsigned 无符号 create table t3(id int unsigned); insert into t3 values(-1); error insert into t3 values(4000000000); success # not null : 不为空 create table t4(id int not null , name varchar(11)); insert into t4 values(1,"张宇"); insert into t4 values(null,"张宇"); error insert into t4(name) values("李四"); error # default : 默认值 create table t5(id int not null , name varchar(11) default "沈思雨" ); insert into t5 values(1,null); insert into t5(id) values(2); create table t5_2(id int not null default "1111" , name varchar(11) default "沈思雨" ); insert into t5_2 values(); # 在values里面不写值,默认使用默认值; # unique : 唯一值,加入唯一索引(索引的提出是为了加快速度,一味地乱加索引不会提高查询效率) # 唯一 可为null 标记成: UNI create table t6(id int unique , name char(10) default "赵万里" ); insert into t6(id) values(1); insert into t6(id) values(1); error insert into t6(id) values(null); insert into t6(id) values(null); # id变成了多个null # primary key: 主键 [ 唯一 + 不为null ] PRI 标记数据的唯一特征 """一个表中,只能设置一个字段为一个主键,unique唯一约束可以设置多个""" # 创建主键 create table t7(id int primary key , name varchar(10) default "赵沈阳"); insert into t7(id) values(1); insert into t7(id) values(1); error insert into t7(id) values(null); error # unique + not null => PRI create table t8(id int unique not null , name varchar(10) default "赵沈阳" ); # primary key / unique + not null => 优先把primary key 作为主键; create table t9(id1 int unique not null , id2 int primary key ); # 一个表只能设置单个字段为一个主键; create table t10(id1 int primary key , id2 int primary key ); error # auto_increment: 自增加一 (一般配合 主键或者unique 使用) create table t11(id int primary key auto_increment , name varchar(255) default "敬文栋"); insert into t11 values(1,"张三"); insert into t11 values(null,"李四"); insert into t11(id) values(null); # 使用默认值或者自增插入数据 insert into t11 values(); # 删除数据 delete from t11; # 删除数据 + 重置id truncate table t11; # zerofill : 零填充 (配合int使用,不够5位拿0来填充) create table t12(id int(5) zerofill); insert into t12 values(1234567); insert into t12 values(12);
额外补充
# ### 额外补充 # 关于约束的添加和删除 # 1 添加/删除 约束 not null #alter table 表名 modify 字段名 类型 alter table t1 modify id int not null alter table t1 modify id int # 2 添加/删除 unique 唯一索引 # alter table 表名 add unique(id) alter table t1 add unique(id) alter table t1 drop index id # 3 添加/删除 primary key # alter table 表名 add primary key(id); alter table t1 add primary key(id); alter table t1 drop primary key; # 4 添加/删除 foreign key 外键 (先通过desc 表 找到外键名字,然后再删) alter table student1 drop foreign key student1_ibfk_1; #删除 alter table student1 add foreign key(classid) references class1(id) #添加
6.主键、唯一、普通索引
""" 主键索引 : PRI [primary key] 唯一索引 : UNI [unique] 普通索引 : MUL [index] """ # 1.联合唯一索引 """unique(字段1,字段2,字段3 ..... ) 合在一起,该数据不能重复""" # unique + not null create table t1_server(id int , server_name varchar(10) not null , ip varchar(15) not null , port int not null , unique(ip,port) ); insert into t1_server values(1,"阿里","192.168.11.251",3306); insert into t1_server values(1,"阿里","192.168.11.251",80); insert into t1_server values(1,"阿里","192.168.11.252",80); insert into t1_server values(1,"阿里","192.168.11.252",80); error # unique : 有可能出现多个空值的情况要注意; create table t2_server(id int , server_name varchar(10) not null , ip varchar(15) , port int , unique(ip,port) ); insert into t2_server values(1,"腾讯","192.168.11.251",3306); insert into t2_server values(1,"腾讯","192.168.11.251",3306); error insert into t2_server values(1,"腾讯",null,null); # 注意点: 允许插入多个空值; +------+-------------+----------------+------+ | id | server_name | ip | port | +------+-------------+----------------+------+ | 1 | 腾讯 | 192.168.11.251 | 3306 | | 1 | 腾讯 | NULL | NULL | | 1 | 腾讯 | NULL | NULL | | 1 | 腾讯 | NULL | NULL | | 1 | 腾讯 | NULL | NULL | +------+-------------+----------------+------+ # 2.联合唯一主键 create table t3_server(id int ,server_name varchar(10) not null , ip varchar(15) , port int , primary key(ip,port) ); insert into t3_server values(1,"华为","192.168.11.251",3306); insert into t3_server values(1,"华为","192.168.11.251",3307); """ 总结: primary key(字段1,字段2 ... ) 联合唯一主键 , 单个字段情况,可以设置一个主键,如果是多个字段只能设置成联合主键,合在一起表达一个主键概念; unique(字段1,字段2 ... ) 联合唯一索引 index(字段1,字段2 ... ) 联合普通索引 """ # 3.foreign key: 外键,把多张表通过一个关联字段联合在一起 (该字段可以设置成外键,作用是可以联级更新或者联级删除) """ 语法: foreign key(classid) references class1(id) 条件: 被关联的字段,必须具备唯一属性; """ student1: id name age classid 1 wangtongpei 58 1 2 liuyifeng 85 1 3 wangwen 18 2 class1: id classname 1 python32 2 python33 # 创建class1 create table class2(id int , classname varchar(255) , unique(id)); # 添加唯一索引 alter table class1 add unique(id); # 创建student1 create table student1( id int primary key auto_increment, name varchar(255), age int, classid int, foreign key(classid) references class1(id) ); # 添加数据 insert into class1 values(1,"python32"); insert into class1 values(2,"python33"); insert into class1 values(3,"python34"); insert into student1 values(null,"wangtongpei",58,1); insert into student1 values(null,"liuyifeng",85,1); insert into student1 values(null,"wangwen",18,2); # 没有关联的数据可以直接删除 delete from class1 where id = 1; # 有关联的数据不能直接删除,要先把关联的数据删掉之后再删除 delete from student1 where id = 3; delete from class1 where id = 2; # 联级更新 , 联级删除 ( 谨慎使用 ) """ 联级删除 on delete cascade 联级更新 on update cascade """ # 创建class2 create table class2(id int primary key auto_increment, classname varchar(255)); # 创建student2 create table student2( id int primary key auto_increment, name varchar(255), age int, classid int, foreign key(classid) references class2(id) on delete cascade on update cascade ); # 添加数据 insert into class2 values(1,"python32"); insert into class2 values(2,"python33"); insert into class2 values(3,"python34"); insert into student2 values(null,"wangtongpei",58,1); insert into student2 values(null,"liuyifeng",85,1); insert into student2 values(null,"wangwen",18,2); # 联级删除 (把所有关联数据全部删除,谨慎;) delete from class2 where id = 1; # 联级更新 (把所有关联数据全部更新,谨慎;) update class2 set id = 100 where classname="python33";
7.表与表之间的关系
(1) 一对一 : id name age sex address guanlian id userid mother father .... (2) 一对多(多对一) : 班级和学生之间的关系 一个班级可以对应多个学生,反过来,多个学生对应一个班级; (3) 多对多 : 一个学生可以同时学习多个学科,一个学科同时可以被多个学生学习 一本书可以被多个作者共同编写,一个作者可以写多本书 xueke (表1) id name 1 math 2 english 3 wuli student (表2) id name 1 wangwen 2 wangwei 3 wangtongpei relation (关系表3) """ 把 xid 和 sid 这两个关联字段设置成外键, 关联xueke表里的id(对应的xid) , 关联student表里的id(对应的sid) """ foreign key(xid) references xueke(id) foreign key(sid) references student(id) xid sid 1 1 1 2 1 3 2 1 2 2 2 3
8.存储引擎
# 存储引擎 : 存储数据的一种结构方式 # 概念: 表级锁 : 只要有一个线程执行修改表中的相关操作,就会上锁,其他线程默认等待; 行级锁 : 针对于当前表中的这条记录,这一行进行上锁,其他数据仍然可以被其他线程修改,实现高并发,高可用; 事务处理: 执行sql语句时,必须所有的操作全部成功,才最终提交数据,有一条失败,直接回滚,恢复到先前状态 begin : 开启事务 commit : 提交数据 rollback : 回滚数据 MyISAM: 表级锁 (5.5版本之前的默认存储引擎) InnoDB: 事务处理,行级锁,外键 (5.5版本之后的默认存储引擎) MEMORY: 把数据放在内存中,临时缓存; BLACKHOLE: anything you write to it disappears 一般用于同步主从数据库;(放在主数据库和从数据库之间的一台服务器;) """ 主数据库: 增删改 从数据库: 查询 配置: 一主一从 , 一主多从 , 多主多从 """ create table myisam1( id int ) engine=MyISAM; .frm 表结构 .MYD 表数据 .MYI 表索引 create table innodb1( id int ) engine=InnoDB; .frm 表结构 .ibd 表数据+表索引 create table memory1( id int ) engine=MEMORY; .frm 只有表结构 , 数据存放在内存中 create table blackhole( id int ) engine=BLACKHOLE; .frm 只有表结构 , 所有的数据都不会存储;
9.单表与多表的查询
单表查询
# ### 单表查询 """ select ... from ... where ... group by ... having ... order by ... limit ... """ # 一.where 条件的使用 """ 功能: 对表中的数据进行帅选和过滤 语法: 1.判断的符号 = (!= <>不等于) > >= < <= 2.拼接不同的条件的关键字 and or not 3.查询对应的区间值 between 小值 and 大值 [小值,大值] 查询两者之间的范围值 4.查询具体在哪个范围中 in(1,21,333,444) 指定范围 5.模糊查询 like % 通配符 _ 通配符 like "%b" 匹配以b结尾的任意长度的字符串 like "b%" 匹配以b开头的任意长度的字符串 like "%b%" 匹配字符串中含有b的任意长度的内容 like "__b" 匹配总长度为3个字符,任意内容的字符串,并且以b结尾 like "b_" 匹配总长度为2个字符,任意内容的字符串,并且以b开头 """ # 1. 查询部门是sale的所有员工姓名: select emp_name from employee where post="sale"; # 2. 部门是teacher , 收入大于10000的所有数据 select * from employee where post = "teacher" and salary > 10000; # 3. 收入在1万到2万之间的所有员工姓名和收入 select emp_name,salary from employee where salary between 10000 and 20000; # 4. 收入不在1万到2万之间的所有员工姓名和收入 select emp_name,salary from employee where salary not between 10000 and 20000; # 5. 查看岗位描述为NULL的员工信息 select emp_name from employee where post_comment = null; select emp_name from employee where post_comment = ''; select emp_name from employee where post_comment is null; # 6. 查看岗位描述不为NULL的员工信息 select emp_name from employee where post_comment is not null; # 7. 查询收入是3000 ,4000 ,5000,8300 所有员工的姓名和收入 select emp_name,salary from employee where salary in(3000,4000,5000,8300); select emp_name,salary from employee where salary = 3000 or salary=4000 or salary=5000 or salary=8300; # 8. 查询收入不是3000 ,4000 ,5000,8300 所有员工的姓名和收入 select emp_name,salary from employee where salary not in(3000,4000,5000,8300); # 9. 以on结尾的员工名搜一下 select emp_name from employee where emp_name like "%on"; select emp_name from employee where emp_name like "ji%"; select emp_name from employee where emp_name like "_le_"; # 10. 统计员工一年的年薪 select concat(" 姓名: ",emp_name," 收入: ",salary) from employee; # 计算年薪,可以在mysql中使用四则运算符 + - * / select concat(" 姓名: ",emp_name," 收入: ",salary * 12) from employee; select concat_ws(" : ",emp_name,salary*12 ) from employee; # 11. 查询部门的种类 # distinct 返回唯一不同的值 select distinct(post) from employee; # 二.group by 子句 分组分类 """group by 字段,对数据进行分类, by后面接什么字段,select后面就搜什么字段""" select sex from employee group by sex; # group_concat 按照分组把对应字段拼在一起; select group_concat(emp_name),post from employee group by post; # 聚合函数 # count 统计总数 *所有 select count(*) from employee; # max 统计最大值 select max(salary) from employee; # min 统计最小值 select min(salary) from employee; # avg 统计平均值 select avg(salary) from employee; # sum 统计总和 select sum(salary) from employee; # 1. 查询部门名以及各部门的平均薪资 select avg(salary),post from employee group by post; # 2. 查询部门名以及各部门的最高薪资 select max(salary),post from employee group by post; # 3. 查询部门名以及各部门的最低薪资 select min(salary),post from employee group by post; # 4. 查询公司内男员工和女员工的个数 select count(*),sex from employee group by sex; # 5. 查询部门名以及部门包含的所有员工名字 select group_concat(emp_name),post from employee group by post; # 6 可以group by 两个字段,就可以同时搜索两个字段 select emp_name,post from employee group by post ,emp_name; # 三.having 在数据分类分组之后,对数据进行二次过滤,一般配合group by来使用的; # 找出各部门平均薪资,并且大于10000 select post , avg(salary) from employee group by post having avg(salary) > 10000 # 1.查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 select post , group_concat(emp_name), count(*) from employee group by post having count(*) < 2; # 2.查询各岗位平均薪资小于10000的岗位名、平均工资 select post , avg(salary) from employee group by post having avg(salary) < 10000 # 3.查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资 select post, avg(salary) from employee group by post having avg(salary) between 10000 and 20000 select post, avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000; # 四.order by 排序 , 按照某字段排序 order by age asc (升序) order by age desc (降序) # 按照年龄从小到大排序 select * from employee order by age; # 按照年龄从大到小排序 select * from employee order by age desc; # 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序 select * from employee order by age asc , hire_date desc; # 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列 select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc # 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列 select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc # 五.limit 限制查询条数 (应用在分页) """ limit m,n m代表从第几条数据开始查, n 代表查几条 m=0 代表的是第一条数据""" select * from employee limit 0,10 # 0代表的是第一条数据 select * from employee limit 10,10 # 10代表的是第十一条数据 select * from employee limit 20,10 # 20代表的是第二十一条数据 # limit + num num => 搜索的条数据 select * from employee limit 1 # 搜索这个表里面最后一条数据 select * from employee order by id desc limit 1 # 搜索这个表里面最后五条数据 select * from employee order by id desc limit 5 # 六.mysql 当中可以使用正则表达式 (不推荐,效率低) select * from employee where emp_name regexp ".*on$"; # mysql中无法识别? select * from employee where emp_name regexp "^程.*"; select * from employee where emp_name regexp "^程.*金";
多表查询
# `### 多表查询 # 1.内联接 : inner join : 两表或者多表之间,把满足条件的所有数据查询出来 (多表之间共同拥有的数据会被查询出来) # 两表联查 select 字段 from 表1 inner join 表2 on 必要的关联条件 # 多表联查 select 字段 from 表1 inner join 表2 on 必要的关联条件1 inner join 表3 on 必要的关联条件2 select * from employee inner join department on employee.dep_id = department.id; # as 起别名 select * from employee as e inner join department as d on e.dep_id = d.id; # 也可以省略as (不推荐) select * from employee e inner join department d on e.dep_id = d.id; # where 写法默写是内联接( 等同于inner join ) select * from employee,department where employee.dep_id = department.id; select * from employee as e ,department as d where e.dep_id = d.id; # 2.外联接 : left join左联接 / right join 右联接 # (1)left join左联接 : 以左表为主,右表为辅,完整查询左表所有数据,右表没有的数据补null select * from employee left join department on employee.dep_id = department.id; # (2)right join右联接 : 以右表为主,左表为辅,完整查询右表所有数据,左表没有的数据补null select * from employee right join department on employee.dep_id = department.id; # 3.全联接 : union select * from employee left join department on employee.dep_id = department.id union select * from employee right join department on employee.dep_id = department.id;
子查询
# ### 子查询 """ 子查询: 嵌套查询 (1) sql语句当中又嵌套了另外一条sql,用括号()进行包裹,表达一个整体 (2) 一般用在from子句,where子句... 身后,表达一个条件或者一个表 (3) 速度快慢: 单表查询 > 联表查询 > 子查询; """ # 一.找出平均年龄大于25岁以上的部门 # (1) where select d.id,d.name from employee as e ,department as d where e.dep_id = d.id group by d.id,d.name having avg(e.age) > 25 # (2) inner join select d.id,d.name from employee as e inner join department as d on e.dep_id = d.id group by d.id,d.name having avg(e.age) > 25 # (3) 子查询 # 1.先找出平均年龄大于25岁的部门id select dep_id from employee group by employee.dep_id having avg(age)>25; # 201 202 # 2.通过部门的id找部门的名字 select name from department where id in (201,202); # 3.综合拼接: select id , name from department where id in (select dep_id from employee group by employee.dep_id having avg(age)>25); # 二.查看技术部门员工姓名 # (1) 普通的where 查询 select e.id,e.name from employee as e,department as d where e.dep_id = d.id and d.name = "技术" # (2) inner join select e.id,e.name from employee as e inner join department as d on e.dep_id = d.id where d.name = "技术" # (3)子查询 # (1) 找技术部门对应的id select id from department where name = "技术"; # (2) 通过id找员工姓名 select name from employee where dep_id = 200; # (3) 综合拼接 select id,name from employee where dep_id = (select id from department where name = "技术"); # 三.查看哪个部门没员工 # 联表写法 select d.id,d.name from department as d left join employee as e on d.id = e.dep_id where e.dep_id is null # 1.找员工在哪些部门 (200 201 202 204) select dep_id from employee group by dep_id # 2.把不在该部门的员工找出来 select id from department where id not in (200,201,202,204); # 3.综合拼接 select id,name from department where id not in (select dep_id from employee group by dep_id); department; +------+--------------+ | id | name | +------+--------------+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +------+--------------+ employee; +----+------------+--------+------+--------+ | id | name | sex | age | dep_id |avg(age) +----+------------+--------+------+--------+ | 1 | egon | male | 18 | 200 | 18 | 2 | alex | female | 48 | 201 | 43 | 3 | wupeiqi | male | 38 | 201 | 43 | 4 | yuanhao | female | 28 | 202 | 28 | 5 | liwenzhou | male | 18 | 200 | 18 | 6 | jingliyang | female | 18 | 204 | 18 +----+------------+--------+------+--------+ # 四.查询大于平均年龄的员工名与年龄 # 假设已经知道了平均年龄; select name,age from employee where age > 30; # 计算平均年龄 select avg(age) from employee; # 综合拼接 select name,age from employee where age > (select avg(age) from employee); # 五.把大于其本部门平均年龄的员工名和姓名查出来 # 1.先计算本部门的平均年龄是多少 select dep_id , avg(age) from employee group by dep_id; +--------+----------+ | dep_id | avg(age) | +--------+----------+ | 200 | 18.0000 | | 201 | 43.0000 | | 202 | 28.0000 | | 204 | 18.0000 | +--------+----------+ # 2.把查询的各部门平均年龄和employee进行联表,变成一张大表,最后做单表查询 select * from employee as t1 inner join (1号查询出来的数据) as t2 on t1.dep_id = t2.dep_id # 3.综合拼装 select * from employee as t1 inner join (select dep_id , avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id # 4.最后做一次单表查询,让age > 平均值 select * from employee as t1 inner join (select dep_id , avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id where age >avg_age # 六.查询每个部门最新入职的那位员工 # 利用上一套数据表进行查询; employee +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | max_date +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | | 7300.33 | 401 | 1 | 2017-03-01 | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | 2015-03-02 | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | 2015-03-02 | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | 2015-03-02 | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | 2015-03-02 | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | 2015-03-02 | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | 2015-03-02 | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | 2015-03-02 | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | 2017-01-27 | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | 2017-01-27 | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | 2017-01-27 | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | 2017-01-27 | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | 2017-01-27 | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | 2016-03-11 | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | 2016-03-11 | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | 2016-03-11 | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | 2016-03-11 | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | 2016-03-11 +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ # 1.找各部门的最新入职的时间 select post,max(hire_date) as max_date from employee group by post +-----------------------------------------+------------+ | post | max_date | +-----------------------------------------+------------+ | operation | 2016-03-11 | | sale | 2017-01-27 | | teacher | 2015-03-02 | | 老男孩驻沙河办事处外交大使 | 2017-03-01 | +-----------------------------------------+------------+ # 2.把子查询搜索出来的结果作为一张表和employee这个表做联表,把max_date拼接在employee这个表中,变成一张大表,最后做一次单表查询 select * from employee as t1 inner join (1号数据) as t2 on t1.post = t2.post where t1.hire_date = t2.max_date # 3.综合拼装 select emp_name , max_date from employee as t1 inner join (select post,max(hire_date) as max_date from employee group by post) as t2 on t1.post = t2.post where t1.hire_date = t2.max_date # 七.带EXISTS关键字的子查询 """ exists 关键字 , 表达存在 , 应用在子查询中 如果内层sql , 能够查到数据, 返回True , 外层sql执行相应的sql语句 如果内层sql , 不能查到数据, 返回False , 外层sql不执行sql语句 """ select * from employee where exists (select * from employee where id = 1); select * from employee where exists (select * from employee where id = 100000); """ 总结: 子查询可以单独作为临时数据,作为一张表或者一个字段,通过()进行包裹,表达一个整体; 一般用在from,where,select.子句的后面 可以通过查询出来的数据和另外的表做联表变成更大一张表, 最后做单表查询,达到目的; """
10.用python操作数据库
# ### python 操作mysql import pymysql # ### 1.基本语法 # (1) 创建连接对象 host user password database 这四个参数必写 conn = pymysql.connect( host="127.0.0.1" , user="root" , password="123456" , database="db003" , charset="utf8" , port=3306 ) # (2) 创建游标对象 (用来操作数据库的增删改查) cursor = conn.cursor() print(cursor) # (3) 执行sql语句 sql = "select * from employee" # 执行查询语句返回的总条数 res = cursor.execute(sql) print(res) # (4) 获取数据 fetchone 获取一条数据 # 返回的是元组,里面包含的是第一条的完整数据 res = cursor.fetchone() print(res) res = cursor.fetchone() print(res) res = cursor.fetchone() print(res) # (5) 释放游标对象 cursor.close() # (6) 释放连接对象 conn.close() # ### 2.创建/删除 表操作 # conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db003") # cursor = conn.cursor() # 1.创建一张表 sql = """ create table t1( id int unsigned primary key auto_increment, first_name varchar(255) not null, last_name varchar(255) not null, sex tinyint not null, age tinyint unsigned not null, money float ); """ # res = cursor.execute(sql) # print(res) # 无意义返回值 # 2.查询表结构 sql = "desc t1" res = cursor.execute(sql) print(res) # 返回的是字段的个数 res = cursor.fetchone() print(res) res = cursor.fetchone() print(res) res = cursor.fetchone() print(res) # 3.删除表 try: sql = "drop table t1" res = cursor.execute(sql) print(res) # 无意义返回值 except: pass # ### 3.事务处理 """pymysql 默认开启事务的,所有增删改的数据必须提交,否则默认回滚;rollback""" conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db003") cursor = conn.cursor() sql1 = "begin" sql2 = "update employee set emp_name='程咬钻石' where id = 18 " sql3 = "commit" res1 = cursor.execute(sql1) res1 = cursor.execute(sql2) res1 = cursor.execute(sql3) # 一般在查询的时候,通过fetchone来获取结果 res1 = cursor.fetchone() print(res1) cursor.close() conn.close()
11.sql注入攻击以及解决
# ### sql 注入攻击 import pymysql # (1) sql注入的现象 ''' 现象:绕开账号密码登录成功 ''' user = input("请输入您的用户名>>>") pwd = input("请输入您的密码>>>") conn = pymysql.connect(host="127.0.0.1" , user="root" , password="123456",database="db005") cursor = conn.cursor() sql1 = """ create table usr_pwd( id int unsigned primary key auto_increment, username varchar(255) not null, password varchar(255) not null ) """ sql2 = "select * from usr_pwd where username='%s' and password='%s' " % (user,pwd) print(sql2) res = cursor.execute(sql2) print(res) # 1查到成功 0没查到失败 # res=cursor.fetchone() """ select * from usr_pwd where username='2222' or 4=4 -- aaa' and password='' 相当于 : select * from usr_pwd where 10=10; 绕开了账户和密码的判断 -- 代表的是注释; """ if res: print("登录成功") else: print("登录失败") cursor.close() conn.close() # (2) 预处理机制 """ 在执行sql语句之前,提前对sql语句中出现的字符进行过滤优化,避免sql注入攻击 """ """ execute( sql , (参数1,参数2,参数3 .... ) ) execute2个参数默认开启预处理机制 """ """ 填写 234234' or 100=100 -- sdfsdfsdfsdf 尝试攻击 """ user = input("请输入您的用户名>>>") pwd = input("请输入您的密码>>>") conn = pymysql.connect(host="127.0.0.1" , user="root" , password="123456",database="db005") cursor = conn.cursor() sql = "select * from usr_pwd where username=%s and password=%s" res = cursor.execute(sql , (user,pwd) ) print(res) print( "登录成功" if res else "登录失败" ) cursor.close() conn.close()
12.python 操作mysql 数据库 (增删改查)
# ### python 操作mysql 数据库 (增删改查) import pymysql """ python 操作mysql增删改时,默认是开启事务的, 必须最后commit提交数据,才能产生变化 提交数据: commit 默认回滚: rollback """ conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db005") # 默认获取查询结果时是元组,可以设置返回字典; cursor=pymysql.cursors.DictCursor cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 执行对mysql 的操作 # 1.增 sql = "insert into t1(first_name,last_name,sex,age,money) values(%s,%s,%s,%s,%s)" # (1) 一次插入一条 res = cursor.execute( sql , ("孙","健",0,15,20000) ) print(res) # 1 # 获取最后插入这条数据的id号 print(cursor.lastrowid) # (2) 一次插入多条 res = cursor.executemany( sql , [ ("安","晓东",0,18,30000) , ("刘","玉波",1,20,50000) ,("张","光旭",0,80,60000) , ("李","是元",0,10,10) , ("高","大奥",1,20,80000) ] ) print(res) # 返回插入的条数 # 插入5条数据中的第一条数据的id print(cursor.lastrowid) # 获取最后一个数据的id sql = "select id from t1 order by id desc limit 1" res = cursor.execute(sql) print(res) # 获取结果,返回元组 res = cursor.fetchone() print(res["id"]) # 默认元组 : (57, '高', '大奥', 1, 20, 80000.0) # 返回字典 : {'id': 51, 'first_name': '高', 'last_name': '大奥', 'sex': 1, 'age': 20, 'money': 80000.0} # 2.删 sql = "delete from t1 where id in (%s,%s,%s)" res = cursor.execute(sql , (3,4,5) ) print(res) # 返回的是3,代表删除了3条 if res: print("删除成功") else: print("删除失败") # 3.改 sql = "update t1 set first_name = '王' where id = %s" sql = "update t1 set first_name = '王' where id in (%s,%s,%s,%s)" res = cursor.execute(sql , (6,7,8,9)) print(res) # 返回的是4,代表修改了4条 if res: print("修改成功") else: print("修改失败") # 4.查 """ fetchone 获取一条 fetchmany 获取多条 fetchall 获取所有 """ sql = "select * from t1" res = cursor.execute(sql) print(res) # 针对于查询语句来说,返回的res是总条数; # (1) fetchone 获取一条 res = cursor.fetchone() print(res) res = cursor.fetchone() print(res) # (2) fetchmany 获取多条 res = cursor.fetchmany() # 默认获取的是一条数据,返回列表,里面里面是一组一组的字典; data = cursor.fetchmany(3) print(data) """ [ {'id': 9, 'first_name': '王', 'last_name': '是元', 'sex': 0, 'age': 10, 'money': 10.0}, {'id': 10, 'first_name': '孙', 'last_name': '健', 'sex': 0, 'age': 15, 'money': 20000.0}, {'id': 11, 'first_name': '安', 'last_name': '晓东', 'sex': 0, 'age': 18, 'money': 30000.0} ] """ for row in data: first_name = row["first_name"] last_name = row["last_name"] sex = row["sex"] if sex == 0: sex = "男性" else: sex = "女性" age = row["age"] money = row["money"] strvar = "姓:{},名:{},性别:{},年龄:{},收入:{}".format(first_name,last_name,sex,age,money) print(strvar) # (3) fetchall 获取所有 # data = cursor.fetchall() # print(data) # (4) 自定义搜索查询的位置 print("<==================>") # 1.相对滚动 relative """相对于上一次查询的位置往前移动(负数),或者往后移动(正数)""" """ cursor.scroll(-1,mode="relative") # cursor.scroll(5,mode="relative") res = cursor.fetchone() print(res) """ # 2.绝对滚动 absolute """永远从数据的开头起始位置进行移动,不能向前滚""" cursor.scroll(0,mode="absolute") res = cursor.fetchone() print(res) conn.commit() cursor.close() conn.close()
13.导入导出数据库、更改编码集
# ### (1) 导入导出 不要加分号 导出数据库 1.退出mysql 2.选择要导出的默认路径 3.mysqldump -uroot -p db001 > db001.sql 导入数据库 1.登录到mysql之后 2.创建新的数据库 3.source 路径+文件 # ### (2) 配置linux下的编码集 !includedir /etc/mysql/conf.d/ 客户端的修改 # 设置mysql客户端默认字符集 default-character-set=utf8 !includedir /etc/mysql/mysql.conf.d/ 服务端的修改 # 服务端使用的字符集默认为8比特编码的latin1字符集 character-set-server=utf8 service mysql restart
14.innodb 在只有frm和ibd文件的情况下,如何恢复数据
# innodb 在只有frm和ibd文件的情况下,如何恢复数据; 安装 MySQL Utilities https://downloads.mysql.com/archives/utilities/ cmd中找到frm那个文件,执行如下命令: 切换到对应目录,执行下面语句,不要加分号 mysqlfrm --diagnostic ./文件目录/t1.frm 查出建表语句,复制查询出来的建表语句在mysql中创建的新数据中使用 #对已创建的表进行表空间卸载 删除ibd文件 mysql> alter table innodb1 discard tablespace; 把要恢复的idb文件替换进去 #对已创建的表进行空间装载 mysql> alter table innodb1 import tablespace; CREATE TABLE `innodb1` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB;
浙公网安备 33010602011771号