数据库-表增删改
# ### mysql
ctrl + l 清屏
ctrl + c 终止
[linux]
service mysql start 启动mysql
service mysql stop 停止mysql
service mysql restart 重启mysql
[windows] 在超级管理员权限下才能使用
net start mysql 启动mysql
net stop mysql 停止mysql
1 登录
mysql -u用户名 -p密码 -hip地址
# (1) 登录到本地mysql 默认root 是最高权限账户
mysql -uroot -p123456
localhost => 127.0.0.1 本地IP地址
# (2) 退出mysql
exit 或者 \q 退出
# (3) 远程连接mysql 服务器
mysql -uroot -p -h192.168.80.135
# 查询当前登录用户是谁
select user()
2 密码设置
# 设置密码
set password = password("123456");
# 去除密码
set password = password("");
3 mysql 创建用户
(当然也可以不指定ip,只针对某个用户名)
create user "ceshi01"@"192.168.111.222" identified by "111"; # 给具体某个ip设置账户(一般是公网ip)
create user "ceshi02"@"192.168.111.%" identified by "222"; # 给具体某个网段下的所有ip设置一个账户
create user "ceshi03"@"%"; # 所有ip都能登录,不需要密码
# 通过windows链接linux的mysql ,要通过vnet8 nat模式下的ip创建用户才可使用
create user "ceshi04"@"192.168.80.1" identified by "333";
# 查看用户权限
show grants for "ceshi03"@"%";
# GRANT USAGE ON *.* TO 'ceshi03'@'%' | USAGE 无任何权限
grant 权限 on 数据库.表名 to "用户名"@"ip地址" identified by "密码";
"""
# 权限:
select 查询数据库的权限
insert 插入数据库的权限
update 更新数据库的权限
delete 删除数据库的权限
all 操作数据库的所有权限
* 代表所有
\G 代表垂直分布查看
"""
grant select on *.* to "ceshi03"@"%" identified by "222"
# all 代表所有的权限
grant all on *.* to "ceshi03"@"%" identified by "222"
# 查看所有数据库
show databases
# 移除权限
revoke select on *.* from "ceshi03"@"%"
# 删除账户
drop user "ceshi03"@"%";
# 刷新权限
flush privileges
数据库/表的增删改
"""
linux
sudo find / -name db1
sudo su root 切换到root用户才可以
cd /var/lib/mysql
能看到对应的数据库
"""
操作[数据库] (文件夹)
增
# 创建数据库
create database db1 charset utf8mb4; # 这里一般都用utf8mb4代替utf8
查
# 查看数据库
show databases
# 查看数据库的建库语句
show create database db1
改
# alter 更改数据库的字符集
alter database db1 charset gbk
删
# 删除数据库db1
drop database db1;
操作[数据表] (文件)
"""选择使用哪个数据库创建表 use 数据库名称 """
增
# int 整型 char字符
# 字段名1 类型1 , 字段名2 类型2 ...
create table t1(id int , name char)
查
# 查询所有数据表
show tables
# 查看建表语句
show create table t1\G;
# 查看表字段信息
desc t1
"""
CREATE TABLE `t2` (
`id` int(11) DEFAULT NULL,
`name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
"""
# 查看表结构
desc t1
改
# modify 只能改变数据类型
alter table t1 modify name char(5)
# change 连带字段名和数据类型一起改变
alter table t1 change name NAME char(4)
alter table t1 change NAME name char(5)
# add 添加字段
alter table t1 add age int;
# drop 删除字段 column 列
alter table t1 drop column age;
# rename 更改表名
alter table t1 rename t1_1;
删
# 删除表t1_1
drop table t1_1
操作记录 (文件内容)
"""mysql null 相当于 python None"""
增:
# 一次插入一条数据
insert into t1(id,name) values(1,"xboy1")
# 一次插入多条数据
insert into t1(id,name) values(2,"xboy2"),(3,"xboy3"),(4,"xboy4")
# 不指定具体字段,默认把所有字段值插入一遍
insert into t1 values(5,"xboy5"), (6,"xboy6")
# 可以具体指定某个字段进行插入
insert into t1(name) values("xboy6")
查:
# * 代表所有
select * from t1;
select id,name from t1;
改:
# update 表名 set 字段=值 where 条件
update t1 set name = "王文" where id = 1
# 如果不加条件,所有数据都改了
update t1 set name = "王文"
删:
# 删除id为1的这条数据
delete from t1 where id = 2
delete from t1 # 不加条件默认删除所有数据
# 重置数据表 (包括重置id(id从1开始计))
truncate table t1;
删除数据表内的所有数据
以上两种清空数据表的区别，例如目标对象是表wp_comments，里面的所有留言均是垃圾留言，均可删除。然后便有了以下2种方式（进入mysql操作界面后）：
truncate table wp_comments;
delete from wp_comments;
其中truncate操作中的table可以省略，delete操作中的*可以省略。这两者都是将wp_comments表中数据清空，不过也是有区别的，如下：
truncate是整体删除（速度较快）,delete是逐条删除（速度较慢）。
truncate不写服务器log，delete写服务器log，也就是truncate效率比delete高的原因。
truncate不激活trigger(触发器)，但是会重置Identity（标识列、自增字段），相当于自增列会被置为初始值，又重新从1开始记录，而不是接着原来的ID数。
而delete删除以后，Identity依旧是接着被删除的最近的那一条记录ID加1后进行记录。
如果只需删除表中的部分记录，只能使用DELETE语句配合where条件。 DELETE FROM wp_comments WHERE……
清空表格数据报错
mysql> alter table survey_testresult add foreign key(parent_id_id) references survey_testcase(id);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ate_2021`.`#sql-2e9_7c7690`, CONSTRAINT `#sql-2e9_7c7690_ibfk_1` FOREIGN KEY (`parent_id_id`) REFERENCES `survey_testcase` (`id`))
报错的原因大概分为三种：
原因一：
添加的外键列与另一个表的唯一索引列（一般是主键）的数据类型不同
原因二：
要添加外键的表类型与另一个表的存储引擎是不是都为innodb引擎
#查看表引擎
法一： show create table 表名;
法二：show table status from 数据库 where name=‘表名’;
法三：use information_schema;
select table_catalog,table_schema,table_name,engine from tables
where table_schema=‘数据库名’ and table_name=‘表名’;
原因三：
设置的外键与另一个表中的唯一索引列（一般是主键）中的值不匹配
# 解决办法：
1 删除主动关联此表的关联表中的外键,然后再清空此表,后面再重新添加外键即可
2 删除要成为外键的列，再次创建并默认为空字符.
# 推荐使用第一种方式
原因四：
新增的数据格式不对，例如往外键列中新增数据格式不对情况下：
数据库常用数据类型
整型
tinyint 1个字节 有符号(-128~127) 无符号(unsigned) (0~255) 小整型值
int 4个字节 有符号(-21亿 ~ 21亿 左右 ) 无符号 (0 ~ 42亿 左右) 大整型值,精度范围更广
create table t1(id int , sex tinyint);
insert into t1 values(2200000000,128);
insert into t1 values(2100000000,127);
浮点型
float(255,30) 单精度
double(255,30) 双精度
decimal(65,30) 金钱类型,使用字符串的形式保存小数
"""float 小数点默认保留5位 , double 小数点默认保留16位 decimal 保留个整数 存在四舍五入"""
create table t2( f1 float(5,2) , f2 double(5,2) , f3 decimal(5,2) );
insert into t2 values(1.55555555,1.55555555,1.55555555) # 存在四舍五入
create table t3( f1 float , f2 double , f3 decimal )
insert into t3 values(1.8288888888888888888888888888888888,1.8288888888888888888888888888888888,1.8288888888888888888888888888888888)
create table t3_3(f1 float(5,2));
insert into t3_3 values(12.5678); # 12.57
字符串
# char(255) varchar(255)
char(11) 定长: 固定开辟11个长度的空间(手机号,身份证), 速度比较快
varchar(11) 变长: 最大开启字符长度为11的空间(文章评论,0~255字符,短信), 相比较于char类型,速度稍慢
text 文本类型,针对于 文章,论文,小说
create table t4( c char(11) ,v varchar(11) , t text )
insert into t4 values("1111","地方","sldfjsdkfjlskdfjksdfksdjklfsjklf")
select concat(c,":",v,":",t) from t4
时间
date YYYY-MM-DD 年月日 (纪念日)
time HH:MM:SS 时分秒 (体育竞赛)
year YYYY 年份值 (酒的年份,82年拉菲)
datetime YYYY-MM-DD HH:MM:SS 年月日 时分秒 (登录时间,下单时间)
create table t5(d date,t time , y year , dt datetime);
insert into t5 values("2019-11-21","09:30:30","2019","2019-11-21 09:30:30")
insert into t5 values(now(),now(),now(),now())
timestamp YYYYMMDDHHMMSS 自动更新时间(不需要手动写入,修改数据时候,自动更新,记录最后一次修改时间)
create table t6(dt datetime , ts timestamp);
insert into t6 values(null,null)
insert into t6 values(20191121093728,20380101050505)
insert into t6 values(20191121093728,20390101050505) error # 时间戳最多到2038年的某一天
# mysql 内置函数
now 获取当前时间
concat 拼接各个参数
user() 获取当前登录的用户
# mysql
select user()
concat 拼接 concat(参数1,参数2,参数3 ... ) 把所有参数拼接在一起
# 枚举 和 集合
enum 枚举: 从一组数据中选一个(性别)
set 集合: 从一组数据中选多个(自动去重)
create table t5(
id int,name char(10),
money float(6,2),
sex enum("man","woman"),
hobby set("eat","drink","piao","du","chou")
)
# 正常写法
insert into t5(id,name,money,sex,hobby) values(1,"zhangsan",9.9,"woman","piao,du,chou")
# 自动去重
insert into t5(id,name,money,sex,hobby) values(1,"zhangsan",9.9,"woman","chou,drink,drink,drink,drink,drink")
# ### 配置linux 中 utf8编码集
# (1) find / -name my.cnf
# (2) 找到之后,发现是链接,打开mysql.cnf 找到其中真正引入的路径
!includedir /etc/mysql/conf.d/ (客户端配置)
!includedir /etc/mysql/mysql.conf.d/ (服务端配置)
# (3) nano /etc/mysql/conf.d/mysql.cnf default-character-set=utf8 添加到文件中
# nano /etc/mysql/mysql.conf.d/mysql.cnf character-set-server=utf8添加到文件中
# (4) 重启 service mysql restart
约束
# ### part2 约束
查看索引
show index from [table]
# 关于约束的添加和删除
1 添加/删除 不为空约束 not null
#alter table 表名 modify 字段名 类型
alter table t1 modify id int not null
alter table t1 modify id int
2 添加/删除 普通/唯一索引 index/unique
# alter table 表名 add unique(xxx)
alter table t1 add unique(xxx) # 添加普通索引:把unique改为index即可
alter table t1 drop index xxx # 删除索引/唯一索引统一用index
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
# 在一对多中, 多的一方建立外键
alter table student1 drop foreign key student1_ibfk_1; #删除
alter table table1 add foreign key(classid) references table2(id) #添加
常用约束
对插入的数据进行限制,不满足约束的条件就直接报错
unsigned 无符号
not null 不为空
default 设置默认值
index 普通索引(可重复,可为空)
unique 唯一约束,数据唯一不能重复,但可以为空
primary key 主键,唯一不为空的值,表达这条数据的唯一性
auto_increment 自增加一,(一般针对于主键 或者 unique 进行自增)
zerofill 零填充 , int(6) , 位数不够6为,拿0来填充
foreign key 外键,把多张表通过一个字段联合在一起
其它
# unsigned 无符号
create table t7(id int unsigned)
insert into t7 values(5)
insert into t7 values(-1000) error
# not null 不为空
create table t8(id int not null , name varchar(255));
insert into t8 values(1,"tianqi")
insert into t8 values(null,"tianqi") error
# default 设置默认值
create table t9(id int not null , name varchar(255) default "常远" );
insert into t9 values(1,null)
insert into t9(id) values(2)
# unique 唯一约束,数据唯一不能重复 [默认创建索引,通过索引可以加快查询速度,相当于字典的目录]
"""默认允许插入多个null空值 UNI """
create table t10(id int unique , name char(10) default "张龙");
insert into t10 values(1,null)
insert into t10(id) values(1) error
insert into t10(id) values(2)
insert into t10(id) values(null)
insert into t10(id) values(null)
# primary key 主键,唯一不为空的值,表达这条数据的唯一性
"""在一个表中,只能有一个字段标记成主键,一般标记id"""
# 原型 PRI
create table t11(id int not null unique ,name char(15) default "周永玲")
insert into t11 values(1,"你好")
insert into t11 values(null,"你好")
# primary key 创建主键
create table t12(id int primary key , name char(15) default "周永玲")
insert into t12 values(1,"你好")
# 两者同时存在 , 优先显示primary key 作为主键,
create table t12_2(id int primary key , name char(15) not null unique);
# 一个表只能让一个字段变成主键
create table t12_3(id int primary key , name char(15) primary key); error
# auto_increment 自增加一,(一般针对于主键 或者 unique 进行自增)
create table t13(id int primary key auto_increment , name char(15) default "尹家平");
insert into t13 values(null,"李四")
insert into t13 values(100,"张三")
insert into t13(id) values(null)
insert into t13(name) values("王二麻子")
# 使用默认值进行插入;
insert into t13 values();
# zerofill 零填充 , int(6) , 位数不够6为,拿0来填充
create table ceshi111(id int(6) zerofill );
insert into ceshi111 values(2)
insert into ceshi111 values(222222222)
# 删除:
# (1) delete from 表 where 条件 (删除数据,保留id)
delete from t13 where id = 1
delete from t13 ;
insert into t13(id,name) values(null,"王文")
# (2) truncate table 表名 (删除数据,重置id ,重置表)
truncate table t13
insert into t13(id,name) values(null,"王文")
# ### part3
联合唯一约束
unique(字段1,字段2,..... ) 把多个字段拼在一起表达一个唯一的数据
# 添加: alter table xxx add unique(barcode, id);
# (1) 联合唯一索引 (在非空的情况,显示为主键 PRI)
create table t1_server(id int , server_name char(10) not null,ip char(15) not null , port int not null , unique(ip,port));
insert into t1_server values(1,"aa","192.168.111.15",3306);
insert into t1_server values(1,"aa","192.168.111.15",3307);
insert into t1_server values(1,"aa","192.168.111.16",3306);
insert into t1_server values(1,"aa",null,null); error
# (2) 联合唯一索引 (在为空的情况,显示索引 MUL 代表普通索引)
create table t2_server(id int , server_name char(10) not null,ip char(15) , port int , unique(ip,port));
insert into t2_server values(1,"aa","192.168.111.15",3306);
insert into t2_server values(1,"aa","192.168.111.15",3306); error
insert into t2_server values(1,"aa","192.168.111.17",3306);
insert into t2_server values(1,"aa",null,null); # 允许插入多个控制,推荐使用第一种
| id | server_name | ip | port |
+------+-------------+----------------+------+
| 1 | aa | 192.168.111.15 | 3306 |
| 1 | aa | 192.168.111.17 | 3306 |
| 1 | aa | NULL | NULL |
| 1 | aa | NULL | NULL |
# (3) 联合唯一索引 和 主键 是否可以同时存在呢?可以同时存在 primary key 是真正的主键,联合唯一索引恢复成MUL索引状态
# 方法一
create table t3_server(id int , server_name char(10) not null,ip char(15) not null , port int not null , unique(ip,port))
alter table t3_server add primary key(id);
# 方法二
create table t4_server(id int primary key , server_name char(10) not null,ip char(15) not null , port int not null , unique(ip,port))
# (了解) unique(ip,port) 联合唯一索引 , primary key(ip,port) 联合唯一主键 用法一样,区别在于后者不能在继续添加主键了
删除联合唯一索引
alter table t1 add index idx(name, gender) # 建立联合索引, 其中idx就是我们给联合索引起的别名,方便删除用的 alter table t1 drop index idx;
如果不起别名,命令就是alter table t1 add index(name, gender)
默认使用第一个参数作为索引名,即name
foreign key 外键
把多张表通过一个字段联合在一起
"""外键的要求: 主动关联的这张表设置外键,要求被关联的表字段必须具有唯一属性 (unique 或者 primary key)"""
student:
id name age classname .... address
1 changyuan 81 python8 世外桃源 ...
2 zhouyongling 7 python8 世外桃源 ...
3 wangwen 18 python9 富丽华冠冕堂皇大酒店 ...
# 为了避免出现过多的字段,可以采取分表的形式,来减少冗余数据,提升查询的效率;
student1:
id name age classid
1 changyuan 81 1
2 zhouyongling 7 1
3 wangwen 18 2
class1:
id classname
1 python8
2 python9
# 创建class1表
create table class1(id int , classname varchar(255));
# 设置classid 为主键或者唯一索引
alter table class1 add unique(id);
# 创建student1表
create table student1(
id int primary key auto_increment,
name varchar(255) not null,
age int not null,
classid int,
foreign key(classid) references class1(id)
);
# 插入数据
insert into class1 values(1,"python8")
insert into class1 values(2,"python9")
insert into student1 values(null,"changyuan",81,2);
insert into student1 values(null,"zhouyongling",7,1);
insert into student1 values(null,"wangwen",7,2);
# 删除class1 如果这条数据在多张表中被使用,直接删除会报错,因为有外键关联
delete from class1 where id = 1
# 把关联的数据删掉之后,才可以
delete from student1 where id = 2;
delete from class1 where id = 1;
# 联级删除 联级更新 (谨慎操作)
"""
联级删除 on delete cascade
联级更新 on update cascade
"""
# 创建class2
create table class2(id int unique , classname varchar(255));
# 创建student2
create table student2(
id int primary key auto_increment,
name varchar(255) not null,
age int not null,
classid int,
foreign key(classid) references class2(id) on delete cascade on update cascade
);
# 插入数据
insert into class2 values(1,"python8");
insert into class2 values(2,"python9");
insert into student2 values(null,"changyuan",81,2);
insert into student2 values(null,"zhouyongling",7,1);
insert into student2 values(null,"wangwen",18,2);
# 联级删除class2数据
delete from class2 where id = 2
# 联级更新
update class2 set id = 100 where classname = "python8"
# 表和表之间的关系
(1) 一对一 表1:id m1 m2 m3 m4(表2的id数据) 表2: id m5 m6 m7
(2) 一对多 或多对一 : 1个班级 可以对应多个学生 把学生作为主动关联的表,其中设置一个外键,去关联那个唯一的数据
(3) 多对多 : 一个学生可以对应多个学科,一个学科可以多个学生学习,
一本书可以对应多个作者,一个作者可以写多本书
xueke (表1)
id name
1 math
2 english
3 huaxue
student (表2)
id name
1 wangwen
2 changyuan
3 zhouyongling
relation (表3) 把 xid 和 sid 设置成外键 关联xueke 的 id 和 student 的 id
xid sid
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
# 存储引擎 : 存储数据的方法
"""
show engines
"""
# 概念理解:
表级锁: 如果有人修改这张表,就直接上锁,其他人无法修改,速度慢,不能并发 (MyISAM)
行级锁: 如果有人修改这个表中的一个记录,当前这条记录会上锁,其他数据可以进行修改,允许更大的并发和更快的速度 (InnoDB)
事务处理 : 如果执行sql语句,在全部执行成功之后,在选择提交,如果操作时,有一条失败,直接回滚,恢复成初始状态
begin : 开启事务
commit: 提交数据
rollback:回滚数据
# 存储引擎:
MyISAM : 是5.6版本之前,默认的存储引擎,支持表级锁
InnoDB : 是5.6版本之后,默认的存储引擎,支持行级锁,能够抗住更大的并发
BLACKHOLE : 黑洞,用来同步数据的,场景发生在服务器集群,用在:主从数据库 [主:查询 ,从:增删改]
MEMORY : 把数据存储在内存当中,也可以作为缓存
create table myisam1(id int , name char(10)) engine = myisam;
myisam1.frm 表结构
myisam1.MYD 表数据
myisam1.MYI 表索引
create table innodb1(id int , name char(10)) engine = innodb;
innodb1.frm 表结构
innodb1.ibd 表数据 表索引
create table memory1(id int , name char(10)) engine = memory;
memory1.frm 只有一个表结构,数据在内存中
create table blackhole1(id int , name char(10)) engine = blackhole;
blackhole1.frm
基础查询
单表查询
# ### part1 单表查询
# sql 查询语句的完整语法
''' select .. from .. where .. group by .. having .. order by .. limit .. '''
一.where
# 条件的使用
"""功能:对表中的数据进行筛选过滤"""
"""
语法:
1.判断的符号:
= > < >= <= != <> 不等于
2.拼接条件的关键字
and or not
3.查询的区间范围值 between
between 小值 and 大值 [小值,大值] 查询两者之间这个范围的所有数据
4.查询具体某个值的范围 in
in(1,-9,-10,"a") 指定范围
5.模糊查询 like "%" 通配符
like "%a" 匹配以a结尾的任意长度的字符串
like "a%" 匹配以a开头的任意长度的字符串
like "%a%" 匹配含有a字母的任意长度字符串
like "_a" 个数一共2个字符,必须以a结尾,前面这个字符随意
like "a__" 个数一共3个字符,必须以a开头,后面这个两字符随意
"""
# (1) 单条件的查询
# 查询部门是sale的所有员工姓名:
select emp_name from employee where post = "sale";
# (2) 多条件的查询
# 部门是teacher,收入大于10000的所有数据
select * from employee where post = "teacher" and salary > 10000;
# (3) 关键字between .. and
# 收入在1万到2万之间的所有员工姓名和收入
select emp_name,salary from employee where salary between 10000 and 20000;
# 收入不在1万到2万之间的所有员工姓名和收入
select emp_name,salary from employee where salary not between 10000 and 20000;
# (4) null关键字 在查询null数据的时候,要用is进行判定,不能用=
# 查询 post_comment 是空的NULL 所有数据
select * from employee where post_comment is NULL # 查询为空的数据
select * from employee where post_comment is not NULL # 查询不为空的数据
update employee set post_comment = "" where id = 1
select * from employee where post_comment = '';
# (5) 关键字 in 的查询
# 查询收入是 3000 或 5000 或者 4000 或者 8000 所有员工姓名和收入
select emp_name,salary from employee where salary=3500 or salary=5000 or salary=8300 or salary=4000;
# 用in优化,在小括号里面写上可能的值
select emp_name,salary from employee where salary in (3500,5000,8300,4000);
# 不在括号中的值,搜索出来
select emp_name,salary from employee where salary not in (3500,5000,8300,4000);
# (6) 关键字 like 模糊查询
# (1) % 通配符
select emp_name,age,post from employee where emp_name like "%on";
# (2) _ 通配符
select emp_name,age,post from employee where emp_name like "a_e_";
# (7) concat
select concat("姓名:",emp_name,"薪资:",salary) as aaa from employee;
# concat_ws(拼接的符号,参数1,参数2,参数3 ... )
select concat_ws(" : ",emp_name,salary) as bbb from employee;
# 可以在sql中使用四则运算(+ - * /)
select concat_ws(" : ",emp_name, salary * 12 ) as bbb from employee;
二.group by
# 子句 分组,分类
"""group by 对数据进行分类, by 后面接的字段,就是select要搜索的字段"""
select sex from employee group by sex;
select post from employee group by post;
# group_concat 按照分组形式进行字段的拼接
select group_concat(emp_name),post from employee where id>1 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 post , avg(salary) from employee group by post;
# 2. 查询部门名以及各部门的最高薪资
select post , max(salary) from employee group by post;
# 3. 查询部门名以及各部门的最低薪资
select post , min(salary) from employee group by post;
# 4. 查询公司内男员工和女员工的个数
select sex,count(*) from employee group by sex
# 5. 查询部门名以及部门包含的所有员工名字
select group_concat(emp_name) , post from employee group by post
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
# 排序 , 按照什么字段进行排序
# 默认值asc 升序排序
# 按照desc 降序排序
select * from employee order by age (默认升序)
select * from employee order by age desc (降序)
# 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
select emp_name,sex,age,hire_date,post from employee order by age,hire_date desc
# 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc
# 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc
五.limit
# 限制查询的条数 (数据分页)
limit m,n m代表从第几条开始查询,n代表查询几条 m=0 代表的是第一条
select * from employee limit 0,5 从第一条开始查,查5条
select * from employee limit 5,5 从第六条开始查,查5条
# 只查询一条数据
select * from employee limit 1
# 最后一条数据
select * from employee order by id desc limit 1
# 拿到最后三条数据
select * from employee order by id desc limit 3
# 六.(了解) 可以使用正则表达式查询数据 (不推荐使用,不好用效率不高)
select * from employee where emp_name regexp ".*on$" # .*? 的?号不识别
select * from employee where emp_name regexp "^程";
select * from employee where emp_name regexp "^程.*金";
多表查询
# ### part2 多表查询
# 内连接:(内联查询 inner join ) : 两表或者多表满足条件的所有数据查询出来[两个表之间共同具有的数据]
"""
# 两表查询
select 字段 from 表1 inner join 表2 on 条件
# 多表查询
select 字段 from 表1 inner join 表2 on 条件 inner join 表3 on 条件
"""
# 基本语法 inner join on 接的表与表之间的必要连接条件
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 实现的就是内联查询
select * from employee,department where employee.dep_id = department.id
select * from employee as e,department as d where e.dep_id = d.id
外连接
# (1) 左连接 (左联查询 left join ) : 以左表为主,右表为辅,完整查询左表所有数据,右表没有的数据补NULL
""" select 字段 from 表1 left join 表2 on 条件 """
select * from employee left join department on employee.dep_id = department.id
# (2) 右连接 (右联查询 right join) : 以右表为主,左表为辅,完整查询右表所有数据,左表没有的数据补NULL
""" select 字段 from 表1 right join 表2 on 条件 """
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
# ### part3 子查询
"""
子查询: 嵌套查询
(1) 子查询是查询的语句当中又嵌套的另外一条sql语句,用括号()抱起来,表达一个整体
(2) 一般应用在from 子句后面表达一张表,或者 where 子句后面表达一个条件
(3) 速度从快到慢 单表查询速度最快 -> 联表查询 -> 子查询
"""
# (1)找出平均年龄大于25岁以上的部门
# 普通的where 相当于内联查询
select
d.id,d.name
from
employee e,department 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 e inner join department 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 dep_id having avg(age) > 25;
# 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 dep_id having avg(age) > 25)
# (2)查看技术部门员工姓名
# 1.普通where查询
select
e.name
from
employee e ,department d
where
e.dep_id = d.id and d.name = "技术"
# 2.inner join 实现
select
e.name
from
employee e inner join department 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 employee.dep_id = ?
# 3.综合拼接
select name from employee where employee.dep_id = (select id from department where name = "技术")
# (3)查看哪个部门没员工
# 联表写法
select
d.id,d.name
from
employee e right join department d on e.dep_id = d.id
where
e.dep_id is NULL
# 子查询
# 1.先查询,员工都在哪些部门
select dep_id from employee group by dep_id => (200,201,202,204)
# 2.把不在部门列表中的数据找出来
select from department where id not in (1)
# 3.综合拼接
select id,name from department where id not in (select dep_id from employee group by dep_id)
# (4)查询大于平均年龄的员工名与年龄
# 假设平均年龄是18岁
select name,age from employee where age > ?
# 找平均年龄
select avg(age) from employee
# 综合拼装
select name,age from employee where age > (select avg(age) from employee)
# (5)把大于其本部门平均年龄的员工名和姓名查出来
# employee
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id || dep_id | avg(age) |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
# department
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
# 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.把额外的比较的条件加进去
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
t1.age > t2.avg_age
# (6)查询每个部门最新入职的那位员工 # 利用上一套数据表进行查询;
# 1.找每个部门最大的入职时间
select post,max(hire_date) as max_date from employee group by post
# 2.把子查询查出来的数据和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
t1.emp_name,t1.hire_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
# (7)带EXISTS关键字的子查询
"""
exists 关键字表达存在
如果内层sql 能够查到数据, 返回True , 外层sql执行查询语句
如果内层sql 不能查到数据, 返回False, 外层sql不执行查询语句
"""
select * from employee where exists (select * from employee where id = 1)
"""
子查询总结:
子查询可以单独作为一个子句,也可以作为一个表或者某个字段
一般用在from where select 子句后面
通过查询出来的临时表,可以跟任意的表重新拼接,组成更大的表,在通过筛选达成自己的目的
"""
pymysql增删改
# ### python 操作 mysql 增删改查
import pymysql
"""
python 操作mysql 默认开启事务,必须在增删改之后,提交数据,
才会对数据库产生变化,否则默认回滚
提交数据 conn.commit()
回滚数据 conn.rollback()
execute 执行sql
executemany 执行多条sql (插入时,可以使用)
"""
# 创建连接mysql
conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db7")
# 查询数据,默认返回的是元组,可以设置参数,返回字典 pymysql.cursors.DictCursor
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)
# 增
"""
sql = "insert into t1(first_name,last_name,age,sex,money) values(%s,%s,%s,%s,%s)"
# 一次插一条数据
# res = cursor.execute(sql , ("周","永陵",81,1,9.9) )
# print(res)
# 一次插入多条数据
res = cursor.executemany(sql, [ ("马","训",20,0,15000), ("常","远",90,0,10000) , ("李","德亮",18,0,8.8) ] )
print(res)
# 获取最后一条数据的id号 (针对于单条语句的执行,获取最后id)
print(cursor.lastrowid)
# 如果是执行多条数据executemany , 通过查询的方式获取
# select id from t1 order by id desc limit 1
"""
# 删
"""
sql = "delete from t1 where id = %s"
res = cursor.execute(sql,(5,))
print(res)
if res :
print("删除成功")
else:
print("删除失败")
"""
# 改
"""
sql = "update t1 set first_name= %s where id=%s"
res = cursor.execute(sql,("王二麻子",8))
print(res)
if res:
print("更新成功")
else:
print("更新失败")
"""
# 查
sql = "select * from t1" # 6~65
res = cursor.execute(sql)
print(res)
# (1) 获取一条数据 fetchone
res = cursor.fetchone()
print(res)#{'id': 6, 'first_name': '常', 'last_name': '远', 'age': 90, 'sex': 0, 'money': 10000.0}
# (2) 获取多条数据 fetchmany 默认搜索一条,上一次查询的数据,往下获取
data = cursor.fetchmany(3)
print(data)
"""
[
{'id': 7, 'first_name': '李', 'last_name': '德亮', 'age': 18, 'sex': 0, 'money': 8.8},
{'id': 8, 'first_name': '王二麻子', 'last_name': '永陵', 'age': 81, 'sex': 1, 'money': 9.9},
{'id': 9, 'first_name': '马', 'last_name': '训', 'age': 20, 'sex': 0, 'money': 15000.0}
]
"""
for row in data:
first_name = row["first_name"]
last_name = row["last_name"]
age = row["age"]
if row["sex"] == 0:
sex = "男"
else:
sex = "女"
money = row["money"]
print("姓:{},名:{},年龄:{},性别:{},收入:{}".format(first_name,last_name,age,sex,money))
# (3) 获取所有数据 fetchall 从上一次搜索的数据,往下搜
data = cursor.fetchall()
print(data)
# 可以自定义查询的位置
print("<=================>")
sql = "select * from t1 where id >= 50"
res = cursor.execute(sql)
"""
# 1.相对滚动
# 先搜索一条 50
res = cursor.fetchone()
print(res)
# 再向后滚动3条 54
cursor.scroll(3,mode="relative")
res = cursor.fetchone()
print(res)
# 再向后滚动2条 56
cursor.scroll(2,mode="relative")
res = cursor.fetchone()
print(res)
# 在往前滚2条 error 下标越界
cursor.scroll(-30,mode="relative")
res = cursor.fetchone()
print(res)
"""
# 2.绝对滚动 相对于最开始第一条数据进行运算
cursor.scroll(0,mode="absolute")
print(cursor.fetchone())
cursor.scroll(3,mode="absolute")
print(cursor.fetchone())
cursor.scroll(5,mode="absolute")
print(cursor.fetchone())
# 在进行增删改的时候,必须替换数据,才真正进行修改,默认开启事务处理
conn.commit()
cursor.close()
conn.close()
查找mysql配置文件的位置
which mysqld 得到 /usr/sbin/mysqld, 然后cd /usr/sbin,执行
./mysqld --verbose --help |grep -A 1 'Default options'
得到/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 这三个路径,分别打开,只有/etc/mysql/my.cnf文件是非空文件,文件内容:
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
然后找到/etc/mysql/mysql.conf.d/mysqld.cnf 就是mysql的配置文件
查看mysql数据存储位置
show global variables like "%datadir%";
修改mysql数据存储位置
# 简单来说就是找到服务, 右键停止服务, 修改mysql配置文件, 启动服务
# 这里以windows举例,linux也是一样
1 停止mysql服务。
2 在mysql安装目录下找到mysql配置文件my.ini。(如上查看mysql数据存储位置)
3 在my.ini中找到mysql数据存储位置配置datadir选项，比如我电脑上的配置如下：
# Path to the database root
datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data
4 进入到数据存储目录C:/ProgramData/MySQL/MySQL Server 8.0/Data，将所有文件复制到新的数据存储目录下，比如我的新目录是D:/software/MySql/data
5 修改配置文件my.ini中数据存储目录为新的目录，例如：
# Path to the database root#注释原来的目录#datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data
# 新加一行，注意：分隔符使用的是 /
datadir=D:/software/MySql/data
mysql导出与导入数据
1 导出:
在cmd中:
mysqldump -uroot -p db1 > db1_bak.sql # 导出某个库
mysqldump -uroot -p db1 tb1 tb2 > db1_bak.sql # 导出db1中的某些表
# 指定条件导出
mysqldump -uroot -p ate_2021 services_bluetooth --where="create_time>='2021-08-11' and create_time<='2021-08-12'" > output2.sql
# 注意, 导出来的数据需要把导出文件中的清空数据库语句删掉, 不然再导入自己的数据库时就会清空数据表
2 导入:
在mysql中:
use [database name] # 没有库就创建
source E:\path...\db1_bak.sql # 路径中不要用中文
sql文件导入报错
报错1
mysql> source db1_bak.sql
ERROR:
ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: '?-'.
报错原因: 导出格式问题
解决方法:
1 用cmd打开而不是powershell
2 用记事本打开sql脚本，另存为，同时把编码方式改为UTF-8即可(或者直接用notepad++转为UTF-8编码保存)
3 如果还不行,就用 mysql --binary-mode=1 -uroot -p 进入mysql(不清楚有没有用)
报错2
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4'
报错原因：高版本数据库（8.0）转存sql文件 并导入低版本数据库（5.7）
解决办法：
方案一：升级mysql至高版本
方案二：将需要导入的sql文件，把其中的utf8mb4_0900_ai_ci全部替换为utf8_general_ci, 把utf8mb4替换为utf8, 然后重新执行sql文件
报错3
mysql导入sql文件报错
Incorrect string value: '\xAE\xB6\xE4\xBC\x9F' for column 'source' at row 1
报错原因: (原因之一)数据库编码格式与导入的sql文件编码格式不匹配.
在数据库中执行 show variables like 'character_set_%'; 查看默认编码格式, 例如:
其中character_set_client的编码为gbk, 而导入的sql文件的编码为utf-8
在数据库中修改character_set_client的编码为utf8:
set character_set_client=utf8;
再次查看默认编码, 确认已修改为utf8后再导入sql数据
注意: set的修改方式为一次性的, 重新进入mysql终端后又会变成gbk, 目前不清楚怎么在配置文件中永久修改.
若有其他配置为latin1编码的, 直接去my.ini配置文件中添加
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8
mysql查询一个表的所有列名
select COLUMN_NAME from INFORMATION_SCHEMA.Columns where table_name='表名';
mysql查询group by多个字段怎么处理
# group by单个字段就是把这个字段值相同的数据划分为一个分组, group by多个字段就代表把多个字段值都相同的数据划分为一组
group by name, age # 这里是把名称和年龄都相同的数据化为一个分组
sqlyog导入excel表格
首先把准备好的表格改为csv格式 -> 打开sqlyog中对应的表格 -> 右键导入 -> 导入使用本地加载的csv数据 -> 从文件中导入(选择文件), 可以选择栏位 -> 文件需要事先按照字段顺序排列好, 导入的时候会自动按行读取然后按字段顺序插入
mysql快速创建相同结构的表
1 快速创建相同结构的表, 包括索引:
create table b like a;
2 快速创建相同结构的表, 但不包括索引:
create table c select * from a limit 0;
sql查询分页(在sql层而不是在业务层进行分页查询)
# 参考: https://blog.csdn.net/qq_41757790/article/details/125521229
方法一、直接限制返回区间
SELECT * FROM tablename WHERE 查询条件 ORDER BY 排序条件 LIMIT ((页码-1)*页大小),页大小;
//优点：写法简单。
//缺点：当页码和页大小过大时，性能明显下降。
//适用：数据量不大。
mysql垂直分表和水平分表
垂直分表的定义:
将一个表按照字段分成多表, 每个表存储其中一部分字段, 它带来的提升是: 为了避免IO争抢并减少锁表的几率, 查看详情的用户与商品信息浏览互不影响, 充分发挥热门数据的操作效率, 商品信息的操作的高效率不会被商品描述的低效率所拖累, 提高检索性能
水平分表的定义:
将一个表按照数据行分到同一个数据库的多张表中, 目的是为了解决单表数据量大的问题, 例如按id或者按时间字段切分成多个表, 查询进来时, 按照查询的id或者时间去对应的表中查询数据即可. 它带来的提升是: 优化单一表数据量过大而产生的性能问题, 避免IO争抢并减少锁表的几率
复制一个表格数据插入到另一个相同的表格
INSERT INTO user1(user1.id,user1.name,user1.sex) SELECT (user.id,user.name,user.sex)FROM old_user where user.id <= 5000000
mysql忘记密码重置密码
1）：编辑mysqld.cnf文件
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
2）：在文件中的skip-external-locking一行的下面添加一行：
skip-grant-tables
3）：重启MySQL服务
sudo service mysql restart
4）：终端输入mysql进入MySQL，输入USE mysql切换至mysql数据库
mysql
USE mysql
5）：把root用户的密码修改为000000
UPDATE mysql.user SET authentication_string=password('000000') WHERE User='root' AND Host ='localhost';
6）：修改字段plugin
UPDATE user SET plugin="mysql_native_password";
7）：
flush privileges;
8）：退出
quit;
# 最后mysqld.cnf文件注释掉加的那段配置然后重启mysql
