目录:
数据库基础
基本概念
约束
数据类型
多表关联
表间关系图
表相关操作
一对多查询
关键字
pymysql模块
权限管理
数据库高级
重点:增删改查
基本概念:
--
1.什么是数据库
存放数据的仓库,本质就是一套C/S架构的socket软件;
1 已学习的文件操作的缺陷: 2 1.IO操作多,效率问题 3 4 2.多用户竞争数据,安全问题 5 6 3.数据不能同步 7 8 4.不同机器间网络访问数据 9 10 5.用户的验证
2.常见的数据库
1 关系型:数据之间存在某种关联关系 2 oracle 3 目前最好关系型数据库,体现在用户管理,分布式 4 商业用途收费 5 mysql 6 免费开源,功能支持没有oracle强 但是足以满足中小企业使用 7 先后被sun 和 oracle 收购 8 mysql创始人wedenius担心 mysql会闭源 于是另起灶炉 搞了marriaDB 9 mariaDB和mysql 使用方法一模一样 10 sqlServer 11 属于微软生态链,需要和windows配合使用 12 DB2 13 IBM开发的大型关系型数据库 ,收费的,通常与硬件捆绑销售 14 15 非关系型:数据以key -value的形式来存储,数据存在内存中,速度快,断电丢失,存储空间小 16 mongoDB 17 redis 18 memcache
3.数据库的相关概念 ******
1 1.age = 18,一个变量,无法描述清楚数据,称之为一个字段 2 age = 18,name = Jack,genger = man 3 4 2.一堆变量在一起,可以描述清楚数据,称之为一条记录,对应着文件中的一行 5 6 3.文件需要分门别类,每个文件存储不同的数,一个文件称之为表 7 8 4.文件越来越多,得分文件夹,一个文件夹称之为库 9 10 数据库中包含所有内容: 11 字段(列) 12 记录 13 表 14 库 15 DBMS:数据库管理系统,socket软件 16 数据库服务器:计算机 17 一个库可以包含多个表,一个表可以包含多条记录,一条记录中有多个字段 18 19 数据库是什么,本质上就是一套C/S架构得socket软件
4.安装数据库与配置使用环境 ***
1 1.界面版本 傻瓜式安装,记住密码 2 2.绿色解压版 3 1.5.7以后的版本要初始化 ，进入bin所在目录下，mysqld --initialize-insecure 4 2.需要添加到环境变量 5 打开环境变量,将mysql安装目录下的bin加入path变量 6 3.将mysqld加入到windows的系统服务：进入bin文件夹下，mysqld --install
net start mysql
5.绿色版修改默认密码
一:修改默认密码需要先登录成功
执行update语句直接修改,user表中的密码
5.7
update mysql.user set authentication_string = password("root") where user = "root";
5.6
登录：mysql -uroot
update mysql.user set password = password("root") where user = "root";
select user,host,password from mysql.user;
二:修改密码方式,不需要登录,需要知道旧密码
mysqladmin -uroot -p旧密码 password 123
三:破解密码,当忘记密码时 无法登录 也就不能值执行update语句
1 1.启动服务器时 控制它不要加载授权表 2 2.无密码登录服务器 3 3.执行update mysql.user set authentication_string = password("root") where user = "root" 4 4.重启服务器
---
用到的系统指令: mysqld 直接运行服务器程序 mysqld --skip-grant-tables 跳过授权表,用于重设密码 tasklist | findstr mysqld taskkill /f /pid 结束服务器程序 mysql -u用户名 -p密码 -h主机地址 -P端口号 运行客户端程序 mysqld --install 将mysqld注册当windows服务中,在服务中叫MySQL windows服务就是绑定了一个exe程序 sc delete mysql 删除windows服务中的mysql exit 退出客户端
6.库/表/记录sql语句*****
针对库的相关操作:---------------------------------------------------------------
增:create
create database 库名称
删:drop
drop database 库名称
改:alter
alter database 库名称 要改的属性名称
alter database db1 DEFAULT CHARACTER SET utf8;
(alter database db1 CHARSET utf8;)
注意 在mysql中 utf-8 不能带- 写成utf8
查:show
show databases:查看所有数据库
show create databases 库名称:查看自己的建库
命名规范:
1.不能使用纯数字
2.可以是数字 字母 下滑线的组合
3.可以下滑线开头
4.不能是关键字 如create
大致和python相同
不区分 大小写
表相关的操作:------------------------------------------------------------------
增:create
建表时要明确数据库
use db1;
create table 表名称(字段名 类型(长度),....)
create table dog(nikename char(10),gender char(1),age int)
#创建时同时指定数据库
create table 库名称.表名称(字段名 类型(长度),....)
删:drop
drop table 表名;
改:alter,rename
alter table 表名称 drop|change|modify|add
drop 字段名称
alter table dog drop color;
change 旧的字段名 新的字段名 新的类型:改字段
alter table dog change gender sex char(2);
modify 字段名 新的类型:改类型
alter table dog modify color char(5);
add 字段名称 类型
alter table dog add color char(10);
重命名表
rename table 旧表名称 to 新表名称
rename table dog to dogtable;
修改表的属性
alter table 表名 属性名 值;
alter table dogtable DEFAULT CHARSET gbk;
查:show,desc
show tables; 查看所有表
desc 表名称; 查看表结构
show create table 表名; 查建的表
记录相关操作:-----------------------------------------------------------------
增:insert
insert into 表名 values(值1,值2.....)
insert into dog valuse("大黄",1,1,"黄色");
insert into dog(name) valuse("大黄");---可以只传一个name
删:delete
delete from 表名 where 字段名称 = 值
没有条件的话删除表内全部数据
改:update
update 表名 set 字段名 = 新的值 where 字段名 = 值
没有条件的话修改全部
查:select
select * from 表名; *表示通配符 查看所有字段
select 字段名1,字段名2.. from 表名;
针对库的相关操作:--------------------------------------------------------------- 增:create create database 库名称 删:drop drop database 库名称 改:alter alter database 库名称 要改的属性名称 alter database db1 DEFAULT CHARACTER SET utf8; (alter database db1 CHARSET utf8;) 注意 在mysql中 utf-8 不能带- 写成utf8 查:show show databases:查看所有数据库 show create databases 库名称:查看自己的建库 命名规范: 1.不能使用纯数字 2.可以是数字 字母 下滑线的组合 3.可以下滑线开头 4.不能是关键字 如create 大致和python相同 不区分 大小写 表相关的操作:------------------------------------------------------------------ 增:create 建表时要明确数据库 use db1; create table 表名称(字段名 类型(长度),....) create table dog(nikename char(10),gender char(1),age int) #创建时同时指定数据库 create table 库名称.表名称(字段名 类型(长度),....) 删:drop drop table 表名; 改:alter,rename alter table 表名称 drop|change|modify|add drop 字段名称 alter table dog drop color; change 旧的字段名 新的字段名 新的类型:改字段 alter table dog change gender sex char(2); modify 字段名 新的类型:改类型 alter table dog modify color char(5); add 字段名称 类型 alter table dog add color char(10); 重命名表 rename table 旧表名称 to 新表名称 rename table dog to dogtable; 修改表的属性 alter table 表名 属性名 值; alter table dogtable DEFAULT CHARSET gbk; 查:show,desc show tables; 查看所有表 desc 表名称; 查看表结构 show create table 表名; 查建的表 记录相关操作:----------------------------------------------------------------- 增:insert insert into 表名 values(值1,值2.....) insert into dog valuse("大黄",1,1,"黄色"); insert into dog(name) valuse("大黄");---可以只传一个name 删:delete delete from 表名 where 字段名称 = 值 没有条件的话删除表内全部数据 改:update update 表名 set 字段名 = 新的值 where 字段名 = 值 没有条件的话修改全部 查:select select * from 表名; *表示通配符 查看所有字段 select 字段名1,字段名2.. from 表名;
简易版
1. 操作文件夹（库） 增：create database db1 charset utf8; 查：show databases; 改：alter database db1 charset latin1; 删除: drop database db1; 2. 操作文件（表） 先切换到文件夹下：use db1 增：create table t1(id int,name char); 查：show tables; 改：alter table t1 modify name char(3); alter table t1 change name name1 char(2); 删：drop table t1; 3. 操作文件中的内容（记录） 增：insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3'); 查：select * from t1; 改：update t1 set name='sb' where id=2; 删：delete from t1 where id=1; 清空表： delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。 truncate table t1;数据量大，删除速度比上一条快，且直接从零开始， *auto_increment 表示：自增 *primary key 表示：约束（不能重复且不能为空）；加速查找
7.修改默认编码
1 配置文件放在安装路径根目录中 就是和bin同级 名称必须叫my.ini 2 #客户端的配置 3 [client] 4 #mysql这个客户端的配置 5 [mysql] 6 user = root 7 password = root 8 default-character-set = utf8 9 #服务器端的配置 10 [mysqld] 11 character-set-server = utf8
8.mysql版本5.6 与5.7的区别
1 1. 5.7需要初始化 2 2. 5.6游客模式,没有密码可以登录,但是无法操作数据 3 只能看到 information_schema mysql 4 3. 5.7中 data数据存放目录是由初始化时决定 5 带界面的,在C:ProgramData中 6 不带界面的就在安装目录中 7 5.6就在安装目录中 8 4. 密码存储字段名:在5.6中时password,5.7中叫authentication_string
9.数据库的引擎 ***
InnoDB是最常用存储引擎.支持事物,行级锁,外键
BLACKHOLE:写进去就没
MEMORY:存到内存中,断电易失
MYISAM:不支持事物,行级锁,外键,用于记录抽象内容.
一个产品或服务的核心部分称之为引擎
mysql的核心功能存取数据
mysql存储引擎就是负责存取数据那一段代码
约束:（外链）
学习过的数据类型,unsigned无符号,字符串长度,浮点的长度均属于约束.
约束的作用是?
为了保证数据的正确性,完整性
例如要存储密码 ,char(20),只限制了类型和长度,无法保证数据的正确性
1 语法: 2 创建时指定约束 3 create table 表名称(字段名 类型(长度) 约束名称1 约束名称n,....) 4 后期修改的方式添加 约束 5 alter table 表名称 modify 字段名 类型(长度) 约束名称1 约束名称n,.... 6 UNSIGNED 整数,无符号,默认有符号 7 NOT NULL 非空约束 限制该字段的值不能为空 8 UNIQUE 唯一性约束 限制该字段的值是唯一的不能出现重复,可以为空 9 DEFAULT 默认值约束 如果插入数据时没有指定该字段的值则使用默认值 10 PRIMARY KEY 主键约束 限制该字段 不能为空 并且是唯一的 可以唯一标识一条数据 ***** 11 FOREIGN KEY 外键约束 用来指向另一个表的主键 12 14 每一个表都应该有一个主键,需要唯一标识,否则可能出现完全相同的两个数据,无法区分 15 UNIQUE 不能重复,但是可以为空,这样也不能唯一标识 16 UNIQUE NOT NULL 不能为空且唯一,可以唯一标识一条数据,书写顺序无所谓 17 18 UNIQUE NOT NULL 与 主键的区别 19 UNIQUE NOT NULL 不能被其他表引用 (不能作为其它表的外键) 20 UNIQUE NOT NULL 约束一个表中可以有多个,但是主键只能有一个
索引:
1 索引:用于加速查询 2 InnoDB中索引是树形结构 3 为了提高查询效率,InnoDB为找一个不为空,且唯一的字段作为主键 4 如果表中不存在这样的字段,会自动帮你建一个隐藏主键字段,但是无法提升查询效率 5 6 只要是使用InnoDB就应该为每个表指定一个非空且唯一的字段.如果为空,可以传入多个值,不再唯一 7 InnoDB阻止数据时,首先使用主键,如果没有主键,找一个非空且唯一.如果也没有,建一个隐藏字段
自增:
1 多字段联合主键: 不常用 2 学生表的stu_id,course_id 做为联合主键 3 1 1 已有数据 4 1 2 可以插入 5 2 1 可以插入 6 1 1 不能插入 7 只有当两个字段都重复才算重复 8 9 当一个表中,由于业务需求没有一个非空且唯一的字段时,我们可以建一个新的字段专门作为主键 10 管理主键的值挺麻烦的,你得记录上一次的主键值. 11 mysql可以帮你自动管理主键.auto_increment自动增长 12 auto_increment只能用于整型字段,并且该字段必须具备索引,所以通常主键都会加上auto_increment 13 14 手动修改自动增长计数 15 alter table 表名 auto_increment 新的值; 16 注意:如果新的值小于当前的最大值,是无效的 17 18 通常建一个表就要建一个主键,主键的类型通常是整型
数据类型:（外链）
1 整数类型 2 默认有符号的,即数据的存储范围是带负数的 3 设置为无符号 4 1.create table t2(age tinyint unsigned); 5 2.建表后用alter修改 6 7 !!!!!: 8 对于整数类型而言,长度不是数据所占的字节数,是显示数据时的宽度(字符数) 9 默认情况下,存储的数值的十进制位数.小于所设置的显示宽度,不会填充.没有任何效果. 10 加上zerofill,当存储的数值的十进制位数小于所设置的显示宽度时,用0来填充. 11 当数据的十进制位长度大于显示宽度,可以正常显示 12 主要记住:整型后面的长度的含义与字符是不同的 13 字节数 14 tinyint 1 15 smallint 2 16 mediumint 3 17 int ***** 4 18 bigint 8 19 总结:除了存储范围没啥区别 20 都是整型 21 默认有符号 22 长度参数如果不指定,会有默认值 23 int(a),a限制小,不限大,小的用0补齐,大的可以显示(不超过类型范围) 24 25 严格模式 26 什么是严格模式? 27 对插入的数据严格要求,不再范围内直接报错. 28 例如往tinyint中插入大于255的值将报错. 29 什么是非严格模式? 30 不对插入的数据严格要求,不在范围内也可以保存,保存当前类型最大支持的值 31 5.6默认是非严格模式 32 5.7以后默认严格模式 33 34 查看SQL模式 35 select @@sql_mode; 36 show variables like "sql_mode"; 37 38 修改SQL模式 39 set @@sql_mode = "值"; 40 正常情况不需要改 41 42 43 浮点类型 44 float 4字节 45 double 8字节 46 decimal 不固定 47 create table t9(num float(m,d)) 48 insert into t9 valuse(1111.11); 49 m 表示总长度 d 表示小数部分的长度 50 长度表示不是数据存储范围,而是字符长度 51 10.12 总长为4,小数部分为2 52 53 各个类型的最大长度: 54 float (255,30) ***** 55 double (255,30) 56 decimal (65,30) 57 58 区别: 59 float与double的精度不同,都是不准确的小数 60 decimal 准确的小数,不会丢失精度 61 62 具体使用哪种类型得根据使用场景判断 63 float满足大部分使用场景 64 decimal适合银行系统,科学研究等 65 66 括号中m和d的值:可以限制数据存储范围,与整型不同 67 重点:记住m和d的含义 68 69 字符串类型 70 常用两种 71 char 定长字符串 72 varchar 可变长度字符串 73 注意字符串中,长度指定的是数据的字符长度,与字节没关系 74 75 create table t13(c1 char,c2 varchar(10)); 76 在创建时,varchar必须指定长度,char有默认值 77 78 不同点: 79 a char(3) b char(3) 80 A |B | 81 char类型在取数据时,就根据长度来获取,不关心真实数据长度 82 无论的数据有多长,占用的空间是固定的,造成了一定空间浪费 83 84 a varchar(30) b varchar(30) 85 (1)A(1)B 86 varchar类型在取数据时,先获取数据长度,再根据长度获取真实数据,关心真实数据长度 87 先存储长度需要一个字节,再存储真实数据,不会浪费空间 88 但是,由于需要计算数据的长度,所以存取速度会比定长慢 89 90 相同点: 91 括号中的数字,都是表示存储最大字符长度 92 93 char使用频率更高 94 95 96 mysql会在存储数据时自动将数据末尾的空格去掉 97 如果必须要存空格,需要修改sql_mode,增加 PAD_CHAR_TO_FULL_LENGTH,意思是把空格当作有效数据 98 99 100 由于自动去除空格这个机制,在使用等于符号和like时有区别 101 select *from t1 where name = "yh "; 会自动去除空格 102 select *from t1 where name like "yh "; 不会自动去除空格 103 like 用于模糊匹配,使用%表示0或任意个任意字符 ,使用_表示一个任意字符 104 105 日期和时间 106 year 107 time 108 date 109 datetime ***** 110 timestamp ***** 111 timestamp特点是:可以给null,自动输入当前时间,当这条记录被修改了会自动更新当前时间 112 113 枚举 114 enum 可以指定一堆字符串的值,在插入数据时,数据必须是这堆字符串中的其中一个 ("男的","女的"),多选一 115 116 117 集合 118 set 可以指定一堆字符串的值 在插入数据时 数据必须这堆字符串中的其中一个或多个 ("男的","女的") 多选多 119 120 共同点:数据都是字符串类型
多表关联:
--
1.为什么需要分表:
将部门数据与员工数据放到同一张表中会造成:
数据重复
结构混乱
扩展维护性差
2.为什么需要建立关联:
create table dept(id int primary key auto_increment,name char(20),job char(20)); create table emp(id int primary key auto_increment,name char(20),gender char,age int,salary float,d_id int); 以上代码,可以建立关联关系,但是这个关系是逻辑上的,不实际存在,需要为他建立物理上的关联.通过外键约束. create table 表名(字段名 类型(长度),foreign key(外键的字段名称) references 对方表名(对方主键名));
3.外键约束,必须分清主从:
1 使用外键时,必须分清,主从关系 2 3 外键的第一种约束 4 先建主表 5 再建从表 6 create table dept(id int primary key auto_increment,name char(20),job char(20)); 7 create table emp(id int primary key auto_increment,name char(20),d_id int,foreign key(d_id) references dept(id)); 8 9 外键的第二个约束 10 先插入主表 11 再插入从表 12 13 外键的第三个约束 14 删除记录时 15 先删除从表记录 16 再删主表记录 17 18 外键的第四个约束 19 从表更新外键时 必须保证外键是存在的 20 21 外键的第五个约束 22 更新主表的id时 23 必须先删除从表关联的数据 24 或者把关联数据 关联其他的主表id 25 26 外键的第六个约束 27 删除主表时 要先删除从表 28 29 有了这几个约束后,主表和从表中的数据必然是完整的 30 相应的受到外键约束,主表的删除,更新操作受到限制
4.级联:解决主从表更新及删除限制
很多情况下,我们就是要删除一个部门,然而需要至少两条sql语句,造成麻烦 可以使用级联 create table emp( id int primary key auto_increment, name char(20), d_id int, foreign key(d_id) references dept(id) on delete cascade on update cascade ); on delete cascade 当主表删除记录时,从表相关联的记录同步删除 on update cascade 当主表id更新时,从表相关联的记录同步更新 注意是单向的,主表变化是级联操作从表,从表的变化不会级联到主表
表间关系图:
--
多对一:
1 create table dept(id int primary key auto_increment,name char(20),job char(20)); 2 create table emp(id int primary key auto_increment,name char(20),d_id int,foreign key(d_id) references dept(id));
图例:
多对多:
1 create table teacher(id int primary key auto_increment,name char(15)); 2 create table student(id int primary key auto_increment,name char(15)); 3 #中间表 4 create table tsr( 5 id int primary key auto_increment, 6 t_id int,s_id int, 7 foreign key(t_id) references teacher(id), 8 foreign key(s_id) references student(id) 9 ); 10 现在老师和学生 都是主表 关系表是从表 11 先插入老师和学生数据 12 insert into teacher values 13 (1,"高跟"), 14 (2,"矮跟"); 15 16 insert into student values 17 (1,"炜哥"), 18 (2,"仨疯"); 19 20 # 插入对应关系 21 insert into tsr values 22 (null,1,1), 23 (null,1,2), 24 (null,2,2);
图例:
一对一:
1 客户和学生 2 一个客户只能产生一个学生 3 一个学生只能对应一个客户 4 这样的关系是一对一 5 使用外键来关联,但是需要给外键加上唯一约束 6 客户和学生有主从关系,需要先建立客户,再建学生 7 8 create table customer(c_id int primary key auto_increment, 9 name char(20),phonenum char(11),addr char(20)); 10 11 create table student1(s_id int primary key auto_increment, 12 name char(20), 13 class char(11), 14 number char(20), 15 housenum char(20),c_id int UNIQUE, 16 foreign key(c_id) references customer(c_id) 17 );
图例:
垂直分表:一对一的另一种表现形式
水平分表:
当一个表中数据记录太多时,查询效率会降低,可以采取水平分表,两个表字段完全相同
stu中有十万数据,stu1 放五万,stu2 放五万
表相关操作:
1.修改表 add 添加字段 after|first after 添加字段到哪个字段后面 first 添加字段到最前面 modify 修改字段类型 change 修改字段名称或类型 drop 删除字段 rename 改表名 2.复制表 约束不能复制 create table 新的表名 select * from 源表名; 数据 结构 当条件不成立时,只复制表结构 create table 新的表名 select * from 源表名 where 1 = 2; create table stu_copy2 select * from student1 where 1 = 2; 3.蠕虫复制 自我复制 insert into 表名称 select *from 表名; 如果有主键,避开主键字段 insert into 表名称(其他字段) select 其他字段 from 表名; sql注入攻击 一个了解sql语法的攻击者,可以在输入框输入sql语句 如:输入账号1255241708,密码123 将传入数据库的语句结构改变,传入自己的语句,从而对数据库中的数据进行操作. select *from user where account = ":"drop database mysql" and pwd = "123";
关键字:
distinct 去除重复数据 ,所有字段的值全都重复才算重复
where 在逐行读取数据时的一个判断条件,对数据进行过滤.
group by 对数据分组,select后跟被分组字段名
having 对分组后的数据进行过滤,放在group后
order by 对结果排序
limit m,n 分页查询,
指定获取数据条数,用于分页查询.
concat() 函数用于拼接字符串
[inner]join 将不同表联合,只返回两个表中连接字段相等的行
完整的select语句,语法:
书写顺序:
1 select [distinct] * from 表名 2 [where 3 group by 4 having 5 order by 6 limit 7 ]
注意:在书写时,必须按照这个顺序来写,但是书写顺序不代表执行顺序
执行顺序:
from:读数据
where:加条件,过滤数据
group by:对过滤后数据分组
having:对分组后数据进行过滤,放在group by之后
distinct:数据去重rder by:对结果排序 order by salary desc,id asc(当工资条数相等)
limit m,n:显示条数.开始号,不包含,n条数据
数据库伪代码:关键字执行顺序
1 def from(): 2 打开文件 3 def where(): 4 对读取的数据进行过滤 5 6 def group_by(): 7 对数据分组 8 def having(): 9 对分组后的数据进行过滤 10 def distinct(): 11 去除重复数据 12 def order(): 13 排序 14 def limit(): 15 指定获取条数 16 17 select 语句的执行顺序 ***** 18 def select(sql): 19 data = from() 20 data = where(data) 21 data = group by(data) 22 data = having(data) 23 data = distinct(data) 24 data = orderby(data) 25 data = limit(data) 26 return data; 27 28 简单查询 29 指定显示格式: 30 concat()函数用于拼接字符串 31 select 32 ( 33 case 34 when english + math > 120 then 35 concat(name," nice") 36 when english + math <= 130 then 37 concat(name," shit") 38 end 39 ) ,english,math from stu; *完全不重要
group by 分组查询:
建表:
create table emp (id int,name char(10),sex char,dept char(10),job char(10),salary double); insert into emp values (1,"刘备","男","市场","总监",5800), (2,"张飞","男","市场","员工",3000), (3,"关羽","男","市场","员工",4000), (4,"孙权","男","行政","总监",6000), (5,"周瑜","男","行政","员工",5000), (6,"小乔","女","行政","员工",4000), (7,"曹操","男","财务","总监",10000), (8,"司马懿","男","财务","员工",6000);
什么是分组?
把一个整体,分割为多个部分
为什么分组?
在数据库中分组为了统计 *****
分组后,组里的详细记录就被隐藏起来了,不能直接查看
dept 一分组,变成三条记录,每个组中却包含多条记录,没办法显示.
一定要显示的话,可以使用group_concat(字段名),将多个值拼接成一个字符串
注意:1.只有出现在group by 后面的字段,才可以被显示,其他都被影藏了
2.聚合函数不能写在where的后面,where最先执行,它的作用硬盘读取数据并过滤,如果数据还没有读取完,此时不能进行统计
什么样的字段适合用于分组?
重复性高的字段
请查询每种性别的平均工资
性别分组
请查询每种岗位的平均工资
岗位分组
每字后面就是分组的依据
了解：
在mysql 5.6中,分组后会默认显示,每组的第一条记录,这是没有意义的
5.7不显示,因为5.7中 sql_mode中自带 ONLY_FULL_GROUP_BY
group by 后面可以有多个分组与依据 会按照顺序执行
order by:排序用的
asc 表示升序 是默认的 desc 表示降序 by 后面可以有多个排序依据 limit 限制显示条数 limit a,b limit 1,5 从1开始 到5结束 错误 从1开始,不包含1,取5条
1 计算起始位置: 2 第一页 3 select *from emp limit(0,3) 4 第二页 5 select *from emp limit(3,3) 6 第二页 7 select *from emp limit(6,3) 8 9 起始位置的算法 10 页数 - 1 * 条数 11 1 - 1 = 0 * 3 = 0 12 2 - 1 = 1 * 3 = 3
正则表达式匹配:
由于like只能使用% 和 _ 不太灵活
可以将like换为 regexp 来使用正则表达式
单表增删改查: where可选
--
增删改:
增加 insert [into] 表名[(可选字段名)] values(一堆值1),(一堆值2),..... into 可以省略 表名后的字段可以选. 如果写了,后面的values中的值必须与表名后的字段一一对应 如果没写,后面的values中的值必须与表所有字段一一对应 values后面可以给多组值,用逗号隔开 删除 delete from 表名[where 条件] 条件不写,是删除所有记录,是一行一行删除 .注意 :自增id不会归零 truncate:重建表,先记录表结构,删除整个表再重新建出来表. 自增id会归零 更新数据 update 表名 set 字段名 = 值[,字段2 = 值2],[where 条件] 可以一次性修改多个字段的值用逗号隔开 条件如果不写,修改所有记录,要极力避免
单表查询:
语法:
不带关键字的查询 select {*|字段名|四则运行|聚合函数} from 表名 [where 条件] 1.* 表示查询所有字段 2.可以手动要查询的字段 3.字段的值可以进行加减乘除运算 4.聚合函数,用于统计 where是可选的
一对多查询的五种查询方式:
数据准备:
create table emp (id int,name
char(10),sex char,dept_id int);
insert emp values(1,"大黄","m",1);
insert emp values(2,"老王","m",2);
insert emp values(3,"老李","w",30);
create table dept (id int,name char(10));
insert dept values(1,"市场");
insert dept values(2,"财务");
insert dept values(3,"行政");
1.笛卡尔积查询 select *from 表1,表n 查询结果是 将左表中的每条记录,与右表中的每条记录都关联一遍 因为他不知道什么样的对应关系是正确,只能帮你都对一遍 a表有m条记录,b表有n条记录 笛卡尔积结果为m * n 记录 需要自己筛选出正确的关联关系 select *from emp,dept where emp.dept_id = dept.id; 2.内连接查询就是笛卡尔积查询:[inner] join select *from emp [inner] join dept; select *from emp inner join dept where emp.dept_id = dept.id; 3.左外链接查询:left join select *from emp left join dept on emp.dept_id = dept.id; 左表数据全部显示,右表只显示匹配上的 4.右外链接查询:right join select *from emp right join dept on emp.dept_id = dept.id; 右表数据全部显示,左表只显示匹配上的 内和外的理解:内指的是匹配上的数据,外指的是没匹配上的数据 5.全外连接:union select *from emp full join dept on emp.dept_id = dept.id; ##mysql不支持 union: 合并查询结果,默认会去重 select *from emp left join dept on emp.dept_id = dept.id union select *from emp right join dept on emp.dept_id = dept.id; union 去除重复数据,只能合并字段数量相同的表 union all 不会去除重复数据 on,where关键字都是用于条件过滤,没有本质区别 在单表中where的作用是筛选过滤条件 只要是连接多表的条件就使用on,为了区分是单表还是多表,搞个新的名字就是on 在多表中on用于连接多表,满足条件就连接,不满足就不连接
多对多查询:
数据准备:
1 create table stu(id int primary key auto_increment,name char(10)); 2 create table tea(id int primary key auto_increment,name char(10));
#中间表 3 create table tsr(id int primary key auto_increment,t_id int,s_id int, 4 foreign key(s_id) references stu(id), 5 foreign key(s_id) references stu(id)); 6 insert into stu values(null,"张三"),(null,"李李四"); 7 insert into tea values(null,"egon"),(null,"wer"); 8 insert into tsr values(null,1,1),(null,1,2),(null,2,2); 9 #查询语句 10 select *from stu join tea join tsr 11 on stu.id = tsr.s_id and tea.id = tsr.t_id 12 where tea.name = "egon";
多表查询套路:
1.把所有表都连起来
2.加上连接条件
3.如果有别的过滤条件,加上on
子查询:一个查询结果作为另一个的查询条件
什么时候使用子查询
当一次查询无法得到想要结果时,需要多次查询
解决问题的思路
是把一个复杂的问题,拆分为多个简单的问题
是把一个复杂的查询,拆分为多个简单的查询
实验代码:
给你部门的的名称,查部门有哪些人? 第一步查到部门的id 第二部拿着id去员工表查询 select *from dept join emp on dept.id = emp.dept_id; select *from emp join # 使用子查询,得到每个部门的id以及部门的最高工资,形成一个虚拟表,把原始表和虚拟表连接在一起 (select dept_id,max(salary)as m from emp group by dept_id) as t1 # 如果这个人的部门编号等于虚拟表中的部门编号 on emp.dept_id = t1.dept_id and # 并且,如果这个人的工资等于虚拟表中的最高工资,就是你要找的人 emp.salary = t1.m;
=============================================================================================
模块操作数据库：pymysql模块
--
1.为什么使用pymysql:
开发中需要在程序中取连接数据库进行增删改查(后期开发中会用框架替代MySQL)
2.使用模板:
1 import pymysql 2 3 conn = pymysql.connect( 4 host="127.0.0.1", 5 port=3306, 6 user="root", 7 password="root", 8 database="db1", 9 charset="utf8" 10 ) 11 # cursor:游标对象,负责执行sql语句,获取返回的数据 12 # pymysql.cursors.DictCursor指定使用字典类型的游标,默认是元组类型 13 cursor = conn.cursor(pymysql.cursors.DictCursor) 14 15 sql = "select *from t1" 16 17 # 返回值是本次查询的记录条数 18 res = cursor.execute(sql) #执行sql 19 20 # cursor.scroll(1,mode="absolute") # 绝对:游标从开始位置往后移动1条记录 21 # cursor.scroll(1,mode="relative") # 相对:游标从当前位置往后移动1条记录 22 print(cursor.fetchone()) # 提取一条记录 23 # print(cursor.fetchone()) 24 # print(cursor.fetchone()) 25 # print(cursor.fetchmany(2))# 提取指定数量记录 26 print(cursor.fetchall()) # 提取所有结果 27 28 # print(res)
3.sql注入攻击:
1 import pymysql 2 3 conn = pymysql.connect( 4 host="127.0.0.1", 5 port=3306, 6 user="root", 7 password="root", 8 database="day47", 9 charset="utf8" 10 ) 11 # cursor 游标对象 负责执行sql语句 获取返回的数据 12 # pymysql.cursors.DictCursor指定使用字典类型的游标 默认是元祖类型 13 cursor = conn.cursor(pymysql.cursors.DictCursor) 14 15 name = input("输入用户名:") 16 17 pwd = input("输入密码:") 18 19 20 sql = "select *from user where name = %s and password = %s" 21 res = cursor.execute(sql,(name,pwd)) 22 if res: 23 print("登录成功") 24 else: 25 print("登录失败") 26 27 28 # 什么是sql注入攻击?一些了解sql语法的攻击者,可以通过一些特殊符号,来修改sql执行逻辑,达到绕过验证的效果 29 # 避免的方式
1.在输入时加上正则判断,不允许输入与sql相关的关键字,但是这种方式无法避免代理服务器发起的攻击 2.在服务器端,执行sql前先来判断. pymysql中已经帮你做了处理,只要将参数的拼接交给pymysql来完成就能够避免攻击
4. 增删改:
1 import pymysql 2 3 conn = pymysql.connect( 4 host="127.0.0.1", 5 port=3306, 6 user="root", 7 password="root", 8 database="day47", 9 charset="utf8" 10 ) 11 # cursor 游标对象 负责执行sql语句 获取返回的数据 12 # pymysql.cursors.DictCursor指定使用字典类型的游标 默认是元祖类型 13 cursor = conn.cursor(pymysql.cursors.DictCursor) 14 15 # sql = "insert into user values(null,'中狗子','123')" 16 sql = "update user set name = '耕' where name = '晨'" 17 # sql = "delete from user where name = '大狗子'" 18 19 res = cursor.execute(sql) 20 # pymysql不会自动提交 对数据的修改不会持久化 需要手动commit 21 conn.commit() 22 print(res)