day10 mysql常用操作
一. 目录
1.mysql的简介
2.增删改查操作
3.单表查询
4.多表查询常见的三种方式
5.pymysql模块操作数据库
二. 内容
一.mysql的简介
概述：mysql是一个关系型数据库，关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式，不是excel，但是和excel的形式很像）
mysql安装目录说明：
配置文件里面主要分三段：
[mysqld] 配置mysql服务端相关的信息
[mysql] 配置mysql客户端相关的信息
[client] 配置其他客户端相关的信息
安装数据库后会有四个自带的数据库：
information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。什么是元数据呢？元数据是关于数据的数据，如数据库名或表名，
列的数据 类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。
mysql：这个是mysql的核心数据库，类似于sql server中的master表，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理
信息。不可以删除，如果对mysql不是很了解，也不要轻易修改这个数据库里面的表信息。
performance_schema mysql 5.5 版本 新增了一个性能优化的引擎： PERFORMANCE_SCHEMA这个功能默认是关闭的： 需要设置参数： performance_schema 才可以启动该功能，这个参数是静态参数，只能写在my.ini 中 不能动态修改。
这里的数据表分为几类：
1） setup table ： 设置表，配置监控选项。
2） current events table : 记录当前那些thread 正在发生什么事情。
3） history table 发生的各种事件的历史记录表
4） summary table 对各种事件的统计表
5） 杂项表，乱七八糟表
test: 安装mysql自带的测试表，默认为空，没什么用。
给mysql root用户设置密码的三种方式
方法1： 用SET PASSWORD命令
首先登录MySQL，使用mysql自带的那个客户端连接上mysql。
格式：mysql> set password for 用户名@localhost = password('新密码');
例子：mysql> set password for root@localhost = password('123');
方法2：用mysqladmin
格式：mysqladmin -u用户名 -p旧密码 password 新密码
例子：mysqladmin -uroot -p123456 password 123
方法3：用UPDATE直接编辑那个自动的mysql库中的user表
use mysql
mysql> update user set password=password('123') where user='root' and host='localhost';
mysql> flush privileges; 刷新权限，让其生效，否则不生效，修改不成功。
忘记root的解决方案以windows为例：
先关闭mysql服务：
方法一：net start/stop MySQL ps:需要以管理员的身份运行cmd才行
方法二：tasklist | findstr mysql
taskkill /F /PID id
切换的安装mysql的bin下
G: ps:windows下切换到盘符不用写cd，直接写盘符即可
cd MySQL/bin
--skip-grant-table 跳过权限表
update user set password=password("new password") where user="root"
flush privileges
最后重启服务即可
修改字符集：
\s 查看mysql初始信息
show create database dbname; 查看创建库的信息
show variables like "%char%"; 查看各个角色的编码
解决乱码：注意一下其中的character_set_client、character_set_connection、character_set_results这三项就是因为这三和服务端的编码不一致导致的，所以我们需要将这三项改为和服务端一致的字符集就可以了。
- 1.character_set_client是client发送过来的sql语句的编码,由于服务端本身并不知道client的sql语句的编码是什么,所以是以这个变量作为clientsql语句的初始编码.而服务端接收到sql语句后,则会将sql语句转换为character_set_connection指定的编码(注意,对于字面值字符串,假设前面有introducer标记如latin1或utf8,则不会进行这一步转换).转换完毕,才会真正运行sql语句.
- 2.进行内部操作前将sql语句中的数据从character_set_connection转换为数据表中对应字段的编码.
- 3.将操作结果从内部字符集编码转换为character_set_results编码.
在windows下查看sqlite的utf-8中文需要先执行chcp 65001把当前页换为utf-8编码
chcp 命令:
chcp 65001 就是换成UTF-8代码页，在命令行标题栏上点击右键，选择"属性"->"字体"，将字体修改为True Type字体"Lucida Console"，然后点击确定将属性应用到当前窗口
chcp 936 可以换回默认的GBK
chcp 437 是美国英语
linux系统查看系统默认编码的指令：
执行指令：cat sysconfig i18n
结果中有一条是：LANG="zh_CN.utf8"
关于引擎知识： mysql默认引擎是Innodb，不同的引擎在目录下会生成不同的文件，不同的引擎会生成不同的文件，Innodb支持事务操作
1.后缀名为.frm的文件：这个文件主要是用来描述数据表结构(id,name字段等)和字段长度等信息
2.后缀名为.ibd的文件：这个文件主要储存的是采用独立表储存模式时储存数据库的数据信息和索引信息；
3.后缀名为.MYD（MYData）的文件：从名字可以看出，这个是存储数据库数据信息的文件，主要是存储采用独立表储存模式时存储的数据信息；
4.后缀名为.MYI的文件：这个文件主要储存的是数据库的索引信息；
5.ibdata1文件:主要作用也是储存数据信息和索引信息，这个文件在mysql安装目录的data文件夹下。
从上面可以看出，.ibd储存的是数据信息和索引信息，ibdata1文件也是存储数据信息和索引信息，.MYD和.MYI也是分别储存数据信息和索引信息，那他们之间有什么区别呢？
主要区别是再于数据库的存储引擎不一样，如果储存引擎采用的是MyISAM，则生成的数据文件为表名.frm、表名.MYD、表名的MYI;而储存引擎如果是innoDB，开启了innodb_file_per_table＝1,也就是采用独立储存的模式，生成的文件是表名.frm、表名.ibd，如果采用共存储模式的，数据信息和索引信息都存储在ibdata1中;
在进行数据恢复的时候，如果用的是MYISAM数据引擎，那么数据很好恢复，只要将相应.frm, .MYD, .MYI文件拷贝过去即可。但是如果是innodb的话，则每一个数据表都是一个单独的文件，只将相应的.frm和.ibd文件拷贝过去是不够的，必须在你的ibd文件的tablespace id和ibdata1文件中的元信息的tablespace id一致才可以。
开启事务：start transaction 开启事务 begin也是开启事务
事务回滚：rollback
提交：commit
二.增删改查操作
简单操作：
SQL语句主要是针对数据库里面三个角色进行操作，对象是：库、表、行，操作包括：增删改查。
1、库（data文件夹中的文件夹，每创建一个库，这个库的名称就是文件夹的名称，文件夹里面保存着一些这个库相关的初始信息）
增：create database db1 charset utf8; #创建一个库，可以指定字符集
查：show databases; #查看数据库中所有的库
show create database db1; #查看单独某个库db1的信息
改：alter database db1 charset latin1; #修改库的字符集，注意语句的格式（其他语句也是这么个格式），alter（修改） database（修改数据库） db1 （哪个数据库） charset（字符集） latin1（改成哪个字符集）
删除: drop database db1; #删除数据库
2、表（操作文件，表是上面库文件夹里面的文件）
先切换库：use db1； #要操作表文件，要先切换到对应的库下才能操作表
查看当前所在的是哪个库：select database();
增：create table t1(id int,name char(10) );
查：show tables； #查看当前库中所有的表
show create table t1; #查看单表的创建信息
desc t1；查看表信息，以表格的形式展示结果：
describe t1；#上下这两句是一样的结果
改：alter table t1 modify name char(3); #修改字段属性的，将name字段的char长度改为3
删：drop table t1;
3. 行（操作文件（表）中的内容/记录）（*****将来的重中之重）
增：insert into t1 values(1,'dsb1'),(2,'dsb2'),(3,'dsb3'); #往t1表中插入三行数据
查：select * from t1; #查看t1表中所有字段的数据
改：update t1 set name='sb' where id=2;
删：delete from t1 where id=1; #删除id为1的行
清空表：
delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。
truncate table t1;数据量大，删除速度比上一条快，且直接从零开始， 一般用于设置了自增然后想从id 1起始的。
auto_increment 表示：自增
primary key 表示：约束（不能重复且不能为空）；加速查找
创建语法：
1、创建数据库
CREATE DATABASE 数据库名 charset utf8;
命令规则：
可以由字母、数字、下划线、＠、＃、＄
区分大小写
唯一性
不能使用关键字如 create select
不能单独使用数字
最长128位
2.创建表语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选、非必须，宽度指的就是字段长度约束，例如：char(10)里面的10
3. 字段名和类型是必须的 常用的数据类型：
1. 数字：
整型：tinyinit int bigint
适用范围：存储年龄，等级，id，各种号码等
tinyint[(m)] [unsigned] [zerofill] ps:unsigned 代表无符号不设置unsigned默认有符号 zerofill 0填充
小数：
float ：在位数比较短的情况下不精准
double ：在位数比较长的情况下不精准
decimal：（如果用小数，则用推荐使用decimal）
精准，内部原理是以字符串形式去存
2. 字符串：
char（10）：简单粗暴，浪费空间，存取速度快，以三个字节来存储数据，小于三的，自动补零
varchar：精准，节省空间，存取速度慢
sql优化：创建表时，定长的类型往前放，变长的往后放比如性别 比如地址或描述信息
>255个字符，超了就把文件路径存放到数据库中。
比如图片，视频等找一个文件服务器，数据库中只存路径或url。
3.时间：
data: 年月日
time: 时分秒
datetime 年月日加时分秒
4.枚举和集合：
enum：多选一
set：复选
扩展：MySQL的mode设置，用于限制一些无效的输入
设置的三种方法： 方法3需要重启服务才能生效
1.临时：set sql_mode='strict_trans_tables'
2.数据库重启失效:set global sql_mode='strict_trans_tables'
3.永久设置：my.cnf文件(windows系统是my.ini文件)，新增 sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
查看当前mode模式：
select @@global.sql_mode
select @@sql_mode
表的完整性约束
约束条件与数据类型的宽度一样，都是可选参数，作用：用于保证数据的完整性和一致性
主要分为以下：
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充 设置unique的两种方法：
方法一：
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
); 方法二：
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)
); 联合唯一的设置：
create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
); ps:如果插入的值host和port都重复才会报错
主键primary key字段的值不为空且唯一
主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。
单列做主键===============
#方法一：not null+unique
create table department1(
id int not null unique, #主键
name varchar(20) not null unique,
comment varchar(100)
);
#方法二：在某一个字段后用primary key
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);
#方法三：在所有字段后单独定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id)); #创建主键并为其命名pk_name
==================多列做主键================
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
); 自增 auto_increment
默认起始位置为1，步长也为1.
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);
对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
truncate student;
在创建完表后，修改自增字段的起始值
alter table student auto_increment=3;
#也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
)auto_increment=3; 外键：foreign key
一 快速理解foreign key（外键其实就是标明表和表之间的关系，表和表之间如果有关系的话就三种：一对一，多对一，多对多
foreign key的下面几个约束作用：
1、先要建立被关联的表才能建立关联表
2、在插入数据记录的时候，要先想被关联表中插入数据，才能往关联表里面插入数据
3、更新或者删除数据的时候，都需要考虑关联表和被关联表的关系
解决方案：
a.删除表的时候，先删除关联表，再删除被关联表
b.重建表的时候，在加外键关联的时候加上这两句：on delete cascade 和 on update cascade
创建：
表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一
create table department(
id int primary key,
name varchar(20) not null
)engine=innodb; #dpt_id外键，关联父表（department主键id），同步更新，同步删除
create table employee(
id int primary key,
name varchar(20) not null,
dpt_id int,
constraint fk_name foreign key(dpt_id) #这句话的意思是constraint 是声明我们要建立一个约束啦，fk_name是约束的名称。
on delete cascade
on update cascade
)engine=innodb;
总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表 #多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系 #一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
查看所有外键的名称的方法： select REFERENCED_TABLE_SCHEMA,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME,table_name,CONSTRAINT_NAME from
information_schema.key_column_usage;
#包含我们创建外键的时候，mysql帮我们自动生成的外键名称。
修改表alter table
语法：
1. 修改表名
ALTER TABLE 表名 RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…];
增加数据字段的时候指定位置通过first和after控制
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
3. 删除字段
ALTER TABLE 表名 DROP 字段名;
4. 修改字段
ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…]; 改字段类型和约束条件
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; 改字段名
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…]; 除了改了字段名，还改了数据类型、完整性约束等等的内容
5. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined mysql> alter table student10 modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 6. 对已经存在的表增加复合主键
mysql> alter table service2
-> add primary key(host_ip,port); 7. 增加主键
mysql> alter table student1
-> modify name varchar(10) not null primary key; 8. 增加主键和自动增长
mysql> alter table student1
-> modify id int not null primary key auto_increment; 9. 删除主键
a. 删除自增约束
mysql> alter table student10 modify id int(11) not null; b. 删除主键
mysql> alter table student10
-> drop primary key; 复制表：
语法：复制表结构＋记录 （key不会复制: 主键、外键和索引）
mysql> create table new_service select * from service; 只复制表结构，不要数据
mysql> select * from service where 1=2;
行记录操作
1.插入
插入完整数据（顺序插入）
语法一：
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n); #指定字段来插入数据，插入的值要和你前面的字段相匹配
语法二：
INSERT INTO 表名 VALUES (值1,值2,值3…值n); #不指定字段的话，就按照默认的几个字段来插入数据
插入查询结果
语法：
INSERT INTO 表名(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM 表2
WHERE …; #将从表2里面查询出来的结果来插入到我们的表中，但是注意查询出来的数据要和我们前面指定的字段要对应好
2.更新
语法：
UPDATE 表名 SET
字段1=值1, #注意语法，可以同时来修改多个值，用逗号分隔
字段2=值2,
WHERE CONDITION; #更改哪些数据，通过where条件来定位到符合条件的数据
3.删除
语法：
DELETE FROM 表名
WHERE CONITION; #删除符合条件的一些记录
DELETE FROM 表名；如果不加where条件，意思是将表里面所有的内容都删掉，但是清空所有的内容，一般我们用truncate ，能够将id置为零，delete不能将id置零，再插入数据的时候，会按照之前的数据记录的id数继续递增
4.查询（写在下一节，因为查询的知识点多）
5.授权
#授权表
user #该表放行的权限，针对：所有数据，所有库下所有表，以及表下的所有字段
db #该表放行的权限，针对：某一数据库，该数据库下的所有表，以及表下的所有字段
tables_priv #该表放行的权限。针对：某一张表，以及该表下的所有字段
columns_priv #该表放行的权限，针对：某一个字段
#授权：对文件夹，对文件，对文件某一字段的权限
查看帮助：help grant
常用权限有：select,update,alter,delete
all可以代表除了grant之外的所有权限
#创建用户
create user 'egon'@'1.1.1.1' identified by '123';
create user 'egon'@'192.168.1.%' identified by '123';
create user 'egon'@'%' identified by '123';
#针对所有库的授权:*.*
grant select on *.* to 'egon1'@'localhost' identified by '123'; #只在user表中可以查到egon1用户的select权限被设置为Y #针对某一数据库：db1.*
grant select on db1.* to 'egon2'@'%' identified by '123'; #只在db表中可以查到egon2用户的select权限被设置为Y #针对某一个表：db1.t1
grant select on db1.t1 to 'egon3'@'%' identified by '123'; #只在tables_priv表中可以查到egon3用户的select权限
select * from tables_priv where user='egon4'\G 查看权限 tables_priv可以修改成对应的表
#删除权限
revoke select on db1.* from 'egon'@'%';
三.单表查询
语法：
select * from，这个select * 指的是要查询所有字段的数据。
SELECT distinct 字段1,字段2... FROM 库名.表名 #from后面是说从库的某个表中去找数据，mysql会去找到这个库对应的文件夹下去找到你表名对应的那个数据文件，找不到就直接报错了，找到了就继续后面的操作
WHERE 条件 #从表中找符合条件的数据记录，where后面跟的是你的查询条件
GROUP BY field（字段） #分组
HAVING 筛选 #过滤，过滤之后执行select后面的字段筛选，就是说我要确定一下需要哪个字段的数据，你查询的字段数据进行去重，然后在进行下面的操作
ORDER BY field（字段） #将结果按照后面的字段进行排序
LIMIT 限制条数 #将最后的结果加一个限制条数，就是说我要过滤或者说限制查询出来的数据记录的条数
重点中的重点：关键字的执行优先级
from 找到表:from
where 拿着where指定的约束条件，去文件/表中取出一条条记录
group by 将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
having 将分组的结果进行having过滤
select 执行select
distinct 去重
order by 将结果按条件排序：order by
limit 限制结果的显示条数 1.简单查询
SELECT * FROM employee; #不推荐用* 慢
SELECT DISTINCT post FROM employee; 去重
SELECT name, salary*12 FROM employee; 四则运算查询
SELECT name, salary*12 AS Annual_salary FROM employee; #as + 新字段名，就是起一个别名的意思
CONCAT() 函数用于连接字符串
SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary 按照自己规定的格式查询
SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary,CONCAT('性别：',sex) from employee;#还可以这样分成两列
CONCAT_WS() 第一个参数为分隔符来进行字符串拼接
SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary #通过冒号来将name和salary连接起来
SELECT CONCAT(name,':',salary*12) AS Annual_salary from employee; 能实现跟上面一样的效果
结合CASE语句：结合条件来对查询的结果进行一些加工操作
SELECT
(
CASE
WHEN NAME = 'egon' THEN
NAME
WHEN NAME = 'alex' THEN
CONCAT(name,'_BIGSB')
ELSE
concat(NAME, 'SB')
END
) as new_name,sex
FROM
employee; where约束
where语句中可以使用：
1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在80到100之间
3. in(80,90,100) 值是80或90或100
4. like 'egon%'
pattern可以是%或_，
%表示任意多字符
_表示一个字符
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
#1:单条件查询
SELECT name FROM employee
WHERE post='sale'; #注意优先级，我们说where的优先级是不是比select要高啊，所以我们的顺序是先找到这个employee表，然后按照post='sale'的条件，然后去表里面select数据 #2:多条件查询
SELECT name,salary FROM employee
WHERE post='teacher' AND salary>10000; #3:关键字BETWEEN AND 写的是一个区间
SELECT name,salary FROM employee
WHERE salary BETWEEN 10000 AND 20000; #就是salary>=10000 and salary<=20000的数据 SELECT name,salary FROM employee
WHERE salary NOT BETWEEN 10000 AND 20000; #加个not，就是不在这个区间内，薪资小于10000的或者薪资大于20000的，注意没有等于， #4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS) 判断null只能用is
SELECT name,post_comment FROM employee
WHERE post_comment IS NULL; SELECT name,post_comment FROM employee
WHERE post_comment IS NOT NULL; SELECT name,post_comment FROM employee
WHERE post_comment=''; 注意''是空字符串，不是null，两个是不同的东西，null是啥也没有，''是空的字符串的意思，是一种数据类型，null是另外一种数据类型
ps：
执行
update employee set post_comment='' where id=2;
再用上条查看，就会有结果了 #5:关键字IN集合查询
SELECT name,salary FROM employee
WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ; #这样写是不是太麻烦了，写一大堆的or，下面我们用in这个简单的写法来搞 SELECT name,salary FROM employee
WHERE salary IN (3000,3500,4000,9000) ; SELECT name,salary FROM employee
WHERE salary NOT IN (3000,3500,4000,9000) ; #6:关键字LIKE模糊查询，模糊匹配，可以结合通配符来使用
通配符’%’ #匹配任意所有字符
SELECT * FROM employee
WHERE name LIKE 'eg%'; 通配符’_’ #匹配任意一个字符
SELECT * FROM employee
WHERE name LIKE 'al__'; #注意我这里写的两个_，用1个的话，匹配不到alex，因为al后面还有两个字符ex。
Goup by 分组
单独使用GROUP BY关键字分组
SELECT post FROM employee GROUP BY post;
注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数 GROUP BY关键字和GROUP_CONCAT()函数一起使用,比如说我想按部门分组，每个组有哪些员工，都显示出来，怎么搞
SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内所有成员名，通过逗号拼接在一起
SELECT post,GROUP_CONCAT(name,':',salary) as emp_members FROM employee GROUP BY post; GROUP BY一般都会与聚合函数一起使用，聚合是什么意思：聚合就是将分组的数据聚集到一起，合并起来搞事情，拿到一个最后的结果
select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人，每个人都有唯一的id号，我count是计算一下分组之后每组有多少的id记录，通过这个id记录我就知道每个组有多少人了
聚合函数
#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组 示例：
SELECT COUNT(*) FROM employee; #count是统计个数用的
SELECT COUNT(*) FROM employee WHERE depart_id=1; #后面跟where条件的意思是统计一下满足depart_id=1这个的所有记录的个数
SELECT MAX(salary) FROM employee; #max（）统计分组后每组的最大值，这里没有写group by，那么就是统计整个表中所有记录中薪资最大的，薪资的值
SELECT MIN(salary) FROM employee;
SELECT AVG(salary) FROM employee;
SELECT SUM(salary) FROM employee;
SELECT SUM(salary) FROM employee WHERE depart_id=3; having过滤
HAVING与WHERE不一样的地方在于!!!!!!
having的语法格式和where是一模一样的，只不过having是在分组之后进行的进一步的过滤，where不能使用聚合函数，having是可以使用聚合函数的
#！！！执行优先级从高到低：where > group by > having
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。 #2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,having是可以使用聚合函数
需求：统计各部门年龄在30岁及以上的员工的平均薪资，并且保留平均工资大于10000的部门
答案：select post,avg(salary) as new_sa from employee where age>=30 group by post having avg(salary) > 10000;
having只能在group by后面运行
去重：distinct
去重：select distinct post from employee; 注意distinct去重要写在查询字段的前面
查询排序order by
按单列排序
SELECT * FROM employee ORDER BY salary; #默认是升序排列
SELECT * FROM employee ORDER BY salary ASC; #升序
SELECT * FROM employee ORDER BY salary DESC; #降序
按多列排序:先按照age升序，如果年纪相同，则按照薪资降序
SELECT * from employee
ORDER BY age, #注意排序的条件用逗号分隔
salary DESC; 限制查询记录limit
示例：
#取出工资最高的前三位
SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; #默认初始位置为0,从第一条开始顺序取出三条 SELECT * FROM employee ORDER BY salary DESC
LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条 SELECT * FROM employee ORDER BY salary DESC
LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
有时需要查询出某个字段不重复的记录，这时可以使用mysql提供的distinct这个关键字来过滤重复的记录，但是实际中我们往往用distinct来返回不重复字段的条数（count(distinct id)）,其原因是distinct只能返回他的目标字段，而无法返回其他字段，distinct 想写在其他字段后面需要配合聚合函数来写。 mysql> select id,count(distinct post) from employee;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
报错了：是因为distinct不能返回其他的字段，只能返回目标字段
mysql> select count(distinct post) from employee;
有时需要查询出某个字段不重复的记录，这时可以使用mysql提供的distinct这个关键字来过滤重复的记录，但是实际中我们往往用distinct来返回不重复字段的条数（count(distinct id)）,其原因是distinct只能返回他的目标字段，而无法返回其他字段，distinct 想写在其他字段后面需要配合聚合函数来写。 mysql> select id,count(distinct post) from employee;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
报错了：是因为distinct不能返回其他的字段，只能返回目标字段
mysql> select count(distinct post) from employee;
正则表达式查询
#之前我们用like做模糊匹配，只有%和_，局限性比较强，所以我们说一个正则，之前我们是不是学过正则匹配，你之前学的正则表达式都可以用，正则是通用的 SELECT * FROM employee WHERE name REGEXP '^ale';
SELECT * FROM employee WHERE name REGEXP 'on$';
SELECT * FROM employee WHERE name REGEXP 'm{2}';
查看所有员工中名字是jin开头，n或者g结果的员工信息
select * from employee where name regexp '^jin.*[g|n]$';
四.多表查询常见的三种方式
多表连接查询
内连接：只连接匹配的行
select * from employee,department where employee.dep_id=department.id;
查出技术部的员工的名字
select employee.name from employee,department where employee.dep_id=department.id and department.name='技术';
缺点：逻辑不太清晰 外链接之左连接：优先显示左表全部记录
#以左表为准，即找出所有员工信息，当然包括没有部门的员工
#本质就是：在内连接的基础上增加左边有右边没有的结果 #注意语法：
select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
外链接之右连接：优先显示右表全部记录
#以右表为准，即找出所有部门信息，包括没有员工的部门
#本质就是：在内连接的基础上增加右边有左边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
全外连接：显示左右两个表全部记录
全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#注意：mysql不支持全外连接 full JOIN
#强调：mysql可以使用此种方式间接实现全外连接
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：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select employee.name,department.name from employee inner join department
on employee.dep_id = department.id
where age > 25; #示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select employee.id,employee.name,employee.age,department.name from employee,department
where employee.dep_id = department.id
and age > 25
order by age asc;
子查询
select name from employee where dep_id = (select id from department where name='技术');
表示一条id=200的数据，然后我们通过员工表来查询dep_id=这条数据作为条件来查询员工的name
子查询：
#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等 1、带IN关键字的子查询
#查询员工平均年龄在25岁以上的部门名，可以用连表，也可以用子查询，我们用子查询来搞一下
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
#连表来搞一下上面这个需求
select department.name from department inner join employee on department.id=employee.dep_id
group by department.name
having avg(age)>25;
总结：子查询的思路和解决问题一样，先解决一个然后拿着这个的结果再去解决另外一个问题，连表的思路是先将两个表关联在一起，然后在进行group by啊过滤啊等等操作，两者的思路是不一样的
#查看技术部员工姓名
select name from employee
where dep_id in
(select id from department where name='技术'); #查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee); 2、带比较运算符的子查询
select name,age from emp where age > (select avg(age) from emp); ps:查询年龄大于平均年的的人 3、带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。还可以写not exists，和exists的效果相反。
select * from employee
-> where exists
-> (select id from department where id=200);
五.pymysql模块操作数据库
#安装
pip3 install pymysql
import pymysql conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='wang',
database='db1',
charset='utf8'
)
cursor = conn.cursor(pymysql.cursors.DictCursor) #创建游标以字典的形式展示，不写值默认是元组
sql = 'select * from department;' #写sql
ret = cursor.execute(sql) #执行sql
print('受影响的行数>>> ',ret)
#print(cursor.fetchall()) #打印所有
# cursor.scroll(3,mode='absolute') #绝对位置移动光标
# cursor.scroll(3,mode='relative') #相对位置移动光标
print(cursor.fetchone()) #打印一行
#cursor.commit() #相当于mysql里面的flush privileges 增删改需要commit才能生效
未完待续......
最新文章
- 理解HTTP协议
- C++11 auto_ptr 的问题
- 判断一个数num是否是2的幂（乐视题）
- 深度浅出immutable.js
- Unity官网教程之Tips
- Code笔记 之：注册页面验证码
- 【Ext.Net学习笔记】06：Ext.Net GridPanel的用法（GridPanel 折叠/展开行、GridPanel Selection、 可编辑的GridPanel）
- cocos 帧率测试
- HTML5 拖拽功能
- Firefox下载文件时中文名乱码问题
- Question2Answer初体验
- java.util.ConcurrentModificationException 解决办法（转）
- 【翻译自mos文章】当指定asm disk 为FRA时，11.2.0.3的dbua hang住
- jQuery选择器使用习惯
- 遇到的一些Jquery函数
- php curl 请求302跳转页面
- 个人信息——头像更换（拍照或相册上传）~ 微信小程序
- Ububtu 14.04 安装 Hadoop 2.7.3
- day17：递归函数
- 用matlab生成mif文件