6.《MySQL数据库简介》
1数据库的诞生： 人类在进化的过程中，创造了数字、文字、服务号等进行数据的记录，但是随着认知能力和创造能力的提升，数据量越来越大，对于数据的记录和准去的查找，成为了一个重大难题。计算机诞生后，数据开始在计算机中存储并计数，并设计出了数据库系统 ---------------------------------------------------------------------------------- 2.数据库解决的问题： 1、持久化存储 2、优化读写 3、保证数据的有效性 ---------------------------------------------------------------------------------- 3为什么要使用数据库： 因为数据量越来越大，有效的存储对应的数据 说明： ATM系统，用户名和密码 tom#123456 lilei#234567 hanmeimei#456789 使用数据库存储用户的账号密码 ---------------------------------------------------------------------------------- 4.mysql(充当管理文件的软件)数据库： 服务端软件 socket服务器 解析指令（SQL语句） 本地文件的操作 客户端软件(各种各样) socket客户端 发送指令（SQL语句） ---------------------------------------------------------------------------------- 5.据库的分类： 文档型：例如SQLit，就是一个文档型数据库，通过对文件的复制完成数据库的复制 服务型：例如mysql，数据存储在一个物理文件中，但是需要使用终端以tcp/ip协议连接，进行数据库的读写操作 ---------------------------------------------------------------------------------- 6.三范式简介： 概念：经过研究和对使用中问题的总结，对于设计数据库提出了一些规范，这些规范被称为范式 范式： 1、列不可拆分 2、唯一标识 3、引用主键 说明：后一个范式，都是在前一个范式的基础上建立的 ---------------------------------------------------------------------------------- 7.E-R模型简介： 当前物理的数据库都是按照E-R模型进行设计的 E(entry)：实体 R(relationship)：关系 一个实体转为数据库中的一个表 关系描述两个实体之间的对应规则 1、一对一 2、一对多 3、多对多 关系转为数据库表中的一个列，在关系型数据库中一行就是一个对象(数据) ---------------------------------------------------------------------------------- 8.主要操作： 数据库的操作： 创建 删除 表的操作： 创建 修改 删除 数据的操作(crud)： 增加 修改 删除 查询 inser update delete select
7.Linux系统安装MySQL
sudo apt-get update sudo apt-get install mysql-server 安装过程中输入用户名与密码 服务命令 启动服务 sudo service mysql start 停止服务 sudo service mysql stop 重启服务 sudo service mysql restart
8.连接MySQL
8.1注意
安装后mysql默认有一个root用户使用root用户登录 mysql -uroot -p 输入密码指令必须以分号结尾,不能是中文字符
8.2创建数据库
1.show databases; 显示所有数据库 其中默认的数据库为 mysql 用户权限相关的数据 information_schema mysql本身架构相关数据 ---------------------------------------------------------------------------------- 2.创建数据库 格式: utf-8格式：create database 数据库名称 default charset utf8 collate utf8_general_ci; gbk格式：create database 数据库名称 default charset gbk collate gbk_chinese_ci; 示例： creat database echo1901 defult charset utf8 collate utf8_general_ci; 其中 utf8_general_ci 为排序的意思 ---------------------------------------------------------------------------------- 3.使用数据库 格式: use 数据库名称; 示例: use echo1901; 查看当前正在使用的数据库: select database(); 显示当前使用的数据库中所有的表: show tables;
8.3用户管理
1.注意 需要使用Mysql的root用户 ---------------------------------------------------------------------------------- 2.创建用户 格式: create user '用户名'@'IP地址' identified by '密码'; 示例： create user 'echo'@'10.0.120.123' identified by 'echo1996'; 允许10.0.120.199机器使用echo用户登录 create user 'echo'@'10.0.120.%' identified by 'echo1996'; 允许10.0.120.*机器使用echo用户登录 create user 'echo'@'%' identified by 'amor5438'; 允许所有机器使用echo用户 ---------------------------------------------------------------------------------- 3.查看 use mysql; show tables; select user,host from user; ---------------------------------------------------------------------------------- 4.删除用户 格式: dorp user '用户名'@'IP地址'; ---------------------------------------------------------------------------------- 5.修改用户 格式: rename user '原用户名'@'原IP地址' to '新用户名'@'新IP地址'; ---------------------------------------------------------------------------------- 6.修改密码 格式: set password for '用户名'@'IP地址'=password('新密码');
8.4授权管理
1.注意: 需要使用root用户 ---------------------------------------------------------------------------------- 2.查看权限 格式: show grants for '用户名'@'IP地址'; 示例: 子主题 ---------------------------------------------------------------------------------- 3.授权 格式: grant 权限 on 数据库.数据库中的表 to '用户名'@'IP地址'; 示例: grant select,insert,update,delete on py1901.students to 'echo'@'%'; 给suzhan用户添加对于py1901数据库下的students表的查看\插入\修改\删除的权限 grant select,insert,update on py1901.* to 'echo'@'%'; 给suzhan用户添加对于py1901数据库下所有表的查看\插入\修改的权限 grant all privileges on py1901.* to 'echo'@'%'; 给echo用户添加对于py1901下所有表除了grant(赋予权限)权限以外的所有权限 ---------------------------------------------------------------------------------- 4.授权说明 update 使用update reload 使用flush shutdown 使用mysqladmin shutdown(关闭MySQL) super 使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆 replication client 服务器位置的访问 replication slave 由复制从属使用 ---------------------------------------------------------------------------------- 5.取消授权 格式: revoke 权限 on 数据库.表 from '用户名'@'IP地址'; ---------------------------------------------------------------------------------- 6.将数据读取到内存中,从而立即生效 flush privileges;
8.5开发使用说明
后期开发基本不会使用root用户,进入公司后,公司DB人员会提供一个用户名和密码并说明可以操作那些数据库,
这样可以避免因某个账号密码泄露而威胁整个mysql数据库
8.6修改配置文件
1.打开文件 sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf ---------------------------------------------------------------------------------- 2.修改内容 通过(shift+:+/bind-address)找到bind-address参数 bind-address> =0.0.0.0 保存并退出 ---------------------------------------------------------------------------------- 3.重启mysql服务 sudo service mysql restart
8.7阿里云安全组协议
8.8远程连接mysql
mysql -h ip地址 -u 用户名 -p navicat图形化界面
9.数据库的操作
9.1创建
1.创建 格式: utf-8格式：create database 数据库名称 default charset utf8 collate utf8_general_ci; gbk格式：create database 数据库名称 default charset gbk collate gbk_chinese_ci; 示例： creat database echo1901 defult charset utf8 collate utf8_general_ci; 其中 utf8_general_ci 为排序的意思 -------------------------------------------------------------------------------------------
9.2.删除
drop database 数据库名
9.3.切换
use 数据库名
9.4.查看
show databases； select database();
10.数据库中表的操作
10.1查看数据库中所有的表
show tables;
10.2创建数据库中的表
1.数据的完整性: 一个数据库就是一个完整的数据单元,可以包含多张表,数据被存储到表中，在表中为了更加准确的存储数据,保证数据库的正确有效,可以在建表的时候,为表添加一些强制性的验 证,包括数据字段类型、约束 ---------------------------------------------------------------------------------- 2.创建表 格式: create table 表名(列名 类型[约束[,.....]][,.....]) engine=引擎 default charset=编码; 名词解析: 表名:给数据集合起的名字 列名:字段名称,遵守标识符规则 类型: 明确字段存储的数据类型 具体查看<<mysql类型文档>> 约束:后续单独讲 引擎: myisam 默认引擎,不支持事物 innodb 支持事物,原子性操作 编码:必须与数据库编码相同,解决存储编码错误问题 建表示例： create table students( id int not null auto_increment, name char(20), age int, sex bit, score float, birthday date, grade enum('python01','python02','python03'), hobby set('power','money','girl','boy'), content varchar(100), primary key(id) ) engine=innodb default charset=utf8; 或者: create table students( id int not null auto_increment primary key, name char(20), age int, sex bit, score float, birthday date, grade enum('python01','python02','python03'), hobby set('power','money','girl','boy'), content varchar(100) ) engine=innodb default charset=utf8; 测试数据: insert into students values(0,"suzhan",18,1,100,"2001-10- 01","python01","power,money","suzhan is a good man"); insert into students values(0,"zhange",18,1,100,"2001-10- 01","python04","power,money","zhange is a good man"); 上面的代码会报错,以为限制枚举('python01','python02','python03')中的一个 而上述代码填写信息为"python04"超出限制范围报错 insert into students values(0,"zhange",18,1,100,"2001-10- 01","python03","power,money","zhange is a good man"); ---------------------------------------------------------------------------------- 约束: 1.primary key （主键) 作用: 主键必须包含唯一的值 主键列不能包含null值 每个列表都应该有一个主键,并且只能有一个主键 2.not null 作用: 约束强制列不接受NULL值 注意: 约束强制字段始终包含值,意味着如果不向字段添加值,就无法插入新的记录或更新记录 3.null 作用:允许为空 4.auto_increment 作用: 自增长(默认从1开始) 注意: 约束字段为int类型(就是为数值类型) 约束的字段必须是索引(主键) 5.default 作用: 用于向列中插入默认值 示例: create table students( id int not null auto_increment primary key, name char(20), age int default 18, sex bit, score float, birthday date, grade enum('python01','python02','python03'), hobby set('power','money','girl','boy'), content varchar(100) ) engine=innodb default charset=utf8; insert into students(name, sex, score, birthday, grade, hobby, content) values("suzhan",1,100,"2001-10-01","python01","power,money","suzhan is a good man"); 6.unique 作用: 约束唯一标识数据库表中的每条记录 与primary key的区别: unique和primary key 约束均为列或者列集合提供一个唯一性的保障 primary key拥有自动定义的unique约束 每个表可以有多个unique约束,但是每个表只能有一个primary key约束 使用: unique 唯一索引名称 (列) 示例: create table students( id int not null auto_increment primary key, name char(20), age int default 18, sex bit, score float, birthday date, grade enum('python01','python02','python03'), hobby set('power','money','girl','boy'), content varchar(100) unique uq_name(name) ) engine=innodb default charset=utf8; 或者 create table students( id int not null auto_increment primary key, name char(20) unique, age int default 18, sex bit, score float, birthday date, grade enum('python01','python02','python03'), hobby set('power','money','girl','boy'), content varchar(100) ) engine=innodb default charset=utf8; unique 唯一索引名称(列[,......]) 7.foreign key 说明: 一个表中的foreign key指向另一个表中的primary key 作用: 用于预防破坏表之间连接的动作 也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一
10.3插入数据
1.全列插入: 格式: insert into 表名 values(......); 注意: 主键列是自增长,但是在全列插入时需要占位,通常使用0,插入成功后以实际数据为准 示例: insert into students values(0,"sunck",16,1,100,"2001-10-01","python01","poswer,money","sunck is a good man"); ---------------------------------------------------------------------------------- 2.缺省插入: 格式: insert into 表名(列1,……) values(值1,……); 示例: insert into students(name, sex, score, birthday, grade, hobby, content) values("kaige",1,100,"2001-10-01","python01","poswer,money","kaige is a good man"); ---------------------------------------------------------------------------------- 3.同时插入多条数据: 格式: insert into 表名 values(……),(……),……; insert into 表名(列1,……) values(值1,……),(值1,……),……;
10.4删除表
删除表 格式: drop table 表名;
10.5查看表的结构
查看表的结构 格式: desc 表名;
10.6查看建表语句
查看建表语句 横着看: show create table 表名; 竖着看: show create table 表名 \G;
10.7重命名
重命名 格式: rename table 原表名 to 新表名;
10.8清空表中的数据
清空表的数据 格式: delete from 表名;
10.9修改表
添加列: alter table 表名 add 列名 类型; 删除列: alter table 表名 drop column 列名; 修改列: 类型: alter table 表名 modify column 列名 类型; 列名,类型: alter table 表名 change 原列名 新列名 类型; 添加主键: alter table 表名 add primary key(列名); 删除主键: alter table 表名 drop primary key; alter table 表名 modify 列名 类型, drop primary key; 添加外键: alter table 表名 add constraint 外键名称 foreign key 从表 references 主表; 外键名称: FK_从表名_主表名 从表: 外键字段 主表: 主键字段 删除外键: alter table 表名 drop foreign key 外键名称; 修改默认值: alter table 表名 alter 列名 set default 值; 删除默认值: alter table 表名 alter 列名 drop default;
10.10查看表中的所有内容
select * from 表名
11.约束详解
11.1auto_increment
auto_increment 作用: 自增长 注意: 列必须是int 必须是索引(含主键) 原理: 使用查看建表语句查看建表语句，第一次查看不到什么特殊情况，插入一条数据后，在查看建表语句， 发现在语句后面多了AUTO_INCREMENT=值，下次再插入数据时，表中的主键的值为刚才看到的 AUTO_INCREMENT的值，并且AUTO_INCREMENT值按步长为1递增 可以修改建表语句中的AUTO_INCREMENT的值: alter table students auto_increment=10; sqlite数据库是基础表级别: create table students( id int not null auto_increment primary key, name char(20) unique, age int default 18, sex bit ) engine=innodb AUTO_INCREMENT=10 步长=值 default charset=utf8; mysql是基于会话级别: 自身会话: 只影响当前链接 show session variables like "auto_inc%"; set session auto_increment_increment=5; 全局会话: 影响所有链接 set global auto_increment_increment=5; 对重写链接的连接有效
11.2primary key
primary key 概念: 一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一 作用: 唯一约束标识数据库表中的每条数据 注意: 主键必须包含唯一的值 主键列不能包含null值 每个表都应该有一个主键，并且每个表只能有一个主键 使用单个列: create table students1( id int not null auto_increment primary key, name char(20) unique, age int default 18, sex bit ) engine=innodb default charset=utf8; 使用多个列: create table students3( aid int not null, bid int not null, id int, name char(20) unique, age int default 18, sex bit, primary key(aid,bid) ) engine=innodb default charset=utf8; insert into students3 values(1,1,1,"tom1",18,1); insert into students3 values(1,2,1,"tom2",18,1); insert into students3 values(2,1,1,"tom3",18,1); insert into students3 values(1,1,1,"tom4",18,1);
11.3unique(唯一索引)
unique(唯一索引) 功能: 约束唯一标识数据库表中的每条数据 与primary key的区别: 这两个约束均为列或者列集合提供唯一性保障 pk拥有自定义的unique的约束 每个表可以有多个unique，但是最多只能有一个pk 优点: 加速查询 唯一约束(可以为null) 基本使用: create table students4( id int not null auto_increment primary key, name char(20) unique, age int default 18 ) engine=innodb default charset=utf8; create table students5( id int not null auto_increment primary key, name char(20), age int default 18, unique uq_name(name) ) engine=innodb default charset=utf8; insert into students4 values(0,"tom1",18); insert into students4 values(0,"tom1",16); 思考: 是否可以有多个唯一约束？ create table students6( id int not null auto_increment primary key, aid int unique, bid int unique, name char(20), age int default 18 ) engine=innodb default charset=utf8; create table students7( id int not null auto_increment primary key, aid int, bid int, name char(20), age int default 18, unique uq_aid(aid), unique uq_bid(bid) ) engine=innodb default charset=utf8; insert into students6 values(0,1,1,"tom1",16); insert into students6 values(0,2,2,"tom2",16); insert into students6 values(0,1,2,"tom3",16); 思考: unique 唯一索引名称()，小括号中是否可以写多个字段？ create table students8( id int not null auto_increment primary key, aid int, bid int, name char(20), age int default 18, unique uq_aid_bid(aid, bid) ) engine=innodb default charset=utf8； insert into students8 values(0,1,1,"tom1",16); insert into students8 values(0,1,2,"tom2",16); insert into students8 values(0,1,1,"tom3",16); 注意 多条数据只要aid和bid有一个不同即可(联合唯一)
11.4外键foreign key
1.概念: 外键，一个表中的foreign key指向另一个表中的primary key 2.作用: 用于预防破坏表之间连接的动作 也能防止非法数据插入外键列，因为它的值必须指向那个表中的值之一 3.为什么使用外键？: 三张图片 4.基本代码实现: create table grades( id int not null auto_increment primary key, name char(20) ) engine=innodb default charset=utf8; create table students( id int not null auto_increment primary key, name char(20), grade_id int, constraint fk_students_grades foreign key(grade_id) references grades(id) ) engine=innodb default charset=utf8; insert into grades values(0, "python01"); insert into grades values(0, "python02"); insert into students values(0, "tom", 1); insert into students values(0, "lilei", 2); insert into students values(0, "hanmeimei", 2); insert into students values(0, "sunck", 3); 5.外键约束两个字段: create table grades1( aid int not null, bid int not null, name char(20), primary key(aid, bid) ) engine=innodb default charset=utf8; create table students1( id int not null auto_increment primary key, name char(20), aaid int, bbid int, constraint fk_students1_grades1 foreign key(aaid, bbid) references grades1(aid, bid) ) engine=innodb default charset=utf8; insert into grades1 values(1,1, "python01"); insert into grades1 values(1,2, "python02"); insert into grades1 values(2,1, "python03"); insert into grades1 values(2,2, "python04"); insert into students1 values(0, "tom", 1, 1); insert into students1 values(0, "lilei", 1, 1); insert into students1 values(0, "hanmeimei", 1, 2); insert into students1 values(0, "sunck", 1, 3); 6.主表从表: 声明关系的表示从表 7.一对多: 外键在多的那一方 上面班级与学生即为一对多 一对一: 外键在哪张表都行 使用场景 表的字段太多，需要拆分 人与户口 create table persons( id int not null auto_increment primary key, name char(20), age int, card_id int unique, constraint fk_persons_cards foreign key (card_id) references cards(id) ) engine=innodb default charset=utf8; create table cards( id int not null auto_increment primary key, cardstr char(20), sex bit ) engine=innodb default charset=utf8; insert into cards values(0,"1111", 1); insert into persons values(0, "tom", 19, 1); 多对多: 原理 底层通过两个外键实现，两个外键存储在关系表中 create table users( id int not null auto_increment primary key, name char(20) ) engine=innodb default charset=utf8; create table hosts( id int not null auto_increment primary key, title char(20) ) engine=innodb default charset=utf8; create table users2hosts( id int not null auto_increment primary key, userid int not null, hostid int not null, unique uq_userid_hostid(userid, hostid), constraint fk_u2h_user foreign key(userid) references users(id), constraint fk_u2h_host foreign key(hostid) references hosts(id) ) engine=innodb default charset=utf8;
12.使用SQL语句操作数据
12.1建表
creat table students( id int not null auto_increment primary key, name char(20), age int, sex bit, content varchar(50) ) engine = innodb defult charset = utf8;
12.2 增
1.全排列插入 格式： insert into 表名 values(...); 注意： 主键列是自增长，在全排列插入时需要占位，通常使用0，插入成功后以实际数据为准 ---------------------------------------------------------------------------------- 2.缺省插入 格式： insert into 表名（列1,...）values(值1,...) ---------------------------------------------------------------------------------- 3.同时插入多条数据 insert into 表名 values(...),(...),(...),...; 示例： insert into students values (0, "tom1", 18, 1, "tom1 is a good man"), (0, "tom2", 19, 1, "tom2 is a good man"), (0, "tom3", 16, 0, "tom3 is a good man"), (0, "tom4", 15, 1, "tom4 is a good man"), (0, "tom5", 14, 0, "tom5 is a good man"); insert into 表名 (列1，...),(列2，...),... values(值1，...),(值2,...),...; --------------------------------------------------------------------------------- 4. 将一张表的数据导入另一张表 两张表结构一样： insert into 目的表 select * from 原始表; 示例：insert into students2 select * from students; 两张表结构不一样：insert into 目的表 (列1,列2,...) select 列1,列2,... from 原始表; 示例：insert into students2(name, age, sex) select name, age, sex from students
12.3 删
格式： 根据条件删除：delete from 表名 where 条件; 清空表： delete from 表名; truncate table 表名； 示例： delete from students where id = 14; delete from students where name = 'tom9;(删除所有符合条件的数据) delete from students where id > 20; delete from students where id > 12 and id < 16; delete from students where id < 12 or id > 16; ---------------------------------------------------------------------------------- 物理删除：将数据从数据库中删除，delete操作属于物理删除，物理删除的数据无法恢复，对于一些重要的数据，以后建议使用逻辑删除 ---------------------------------------------------------------------------------- 逻辑删除：本质是修改操作，对于重要数据表，增加一个isDelete字段，一般为0（没有被删除的意思），为1的话表示删除，该字段逻辑上表示这条数据是否被删除，真实情况是在数据库中数据依然存在。 示例： create table students( id int not null auto_increment primary key, name char(20), age int, sex bit, content varchar(50), isDelete bit default 0 ) engine=innodb default charset=utf8;
12.4 改
格式： 根据条件修改某些数据： update 表名 set 列1=值1,...where 条件; 全列修改： update 表名 set 列1=值1,...; 示例： update students set age=20 where id=2; update students set age=22, sex=0 where id=2; update students set age=30 where name='tom9;(修改所有匹配的数据) update students set age=18
12.5 查
假数据
create table students(
id int not null auto_increment primary key,
name char(20),
age int,
sex bit,
content varchar(50),
isDelete bit default 0) engine=innodb default charset=utf8;
insert into students values
(0, “tom1”, 18, 1, “tom1 is a good man”,0),
(0, “tom2”, 19, 1, “tom2 is a good man”,0),
(0, “tom3”, 16, 0, “tom3 is a good man”,0),
(0, “tom4”, 15, 1, “tom4 is a good man”,0),
(0, “tom5”, 14, 0, “tom5 is a good man”,0),
(0, “tom6”, 20, 0, “tom6 is a good man”,0),
(0, “tom7”, 24, 0, “tom7 is a good man”,0),
(0, “tom8”, 10, 0, “tom8 is a good man”,0),
(0, “tom9”, 25, 0, “tom9 is a good man”,0),
(0, “tom9”, 22, 0, “tom10 is a good man”,0),
(0, “tom11”, 30, 0, “tom11 is a good man”,0),
(0, “tom12”, 38, 0, “tom12 is a good man”,0);
12.5.1查询全部
格式： select * from 表名; 说明： from关键字后面写表名，表示数据来源于这张表 select后面表示的是列名，如果是*号表示显示表中的所有列 select * from students; 如果要查询多个列，列之间使用逗号隔开 select name,content from students; 在select后面的列名部分，可以使用as为列起别名，这个别名出现在结果集中 select name,content as info from students;
12.5.2条件查询
格式： select * from 表名 where 条件; ---------------------------------------------------------------------------------- 比较运算符 符号：（= > >= < <= !=或<>） 示例： select * from students where id=2; select * from students where name='tom9'; 获取所有匹配的数据 select * from students where id > 6; ---------------------------------------------------------------------------------- 逻辑运算符 符号：and逻辑与 or逻辑或 not逻辑非 示例： select * from students where id > 6 and id < 11; select * from students where name = 'tom9' and age = 25; ---------------------------------------------------------------------------------- 范围查询 in 表示在一个非连续的范围内 示例： select * from students where id in(2,4,6,8); between...and... 表示在一个连续范围内 示例： select * from students where id between 4 and 8; ---------------------------------------------------------------------------------- 空判断 假数据：nsert into students(name, age, sex) values ("tom13", 24, 0), ("tom14", 64, 1), ("tom15", 29, 0); 符号： is null 判空 示例： select * from students where content=null; ----->拿不出数据 select * from students where content is null; is not null 判非空 示例： select * from students where content is not null; ---------------------------------------------------------------------------------- 优先级 小括号，not，比较运算符，逻辑运算符 and比or先运算，比如同时出现并希望先算or，需要结合小括号来使用
12.5.3模糊查询
假数据：
insert into students values
(0, “刘烨”, 19, 1, “刘涛 is a good man”,0),
(0, “刘德华”, 18, 1, “刘德华 is a good man”,0),
(0, “刘涛”, 19, 1, “刘涛 is a good man”,0),
(0, “刘若英”, 16, 0, “刘若英 is a good man”,0),
(0, “张学友”, 15, 1, “张学友 is a good man”,0),
(0, “张震”, 14, 0, “张震 is a good man”,0);使用like % 表示任意多个任意字符串 _ 表示一个任意字符 示例： select * from students where name like '刘%'; select * from students where name like '刘_'; 注意： 在like语句中如果想使用%需要转义\%
12.5.4分页查询
limit select * from 表名 limit [start,]count; start:从start开始获取，如果没有默认从0开始 count：获取count条数据 示例:select * from students limit 5; ---------------------------------------------------------------------------------- offset select * from 表名 limit count offset start; 需求：一共100条数据，每页10条，获取第n页数据 select * from 表名 limit 10 offset（n-1）*10
12.5.5排序查询
语法： select * from 表名 order by 列1 asc|desc[列2 asc|desc[,....]] 说明： 将行数据按照列1进行排序，如果默认列1值相同是，则按照列2进行排序，以次类推 默认排序升序排序 asc从小到大排序-----升序 desc从大到小排序----降序 示例： select * from students order by age; select * from students order by age desc; select * from students order by age desc,id asc;
12.5.6聚合
为了快速得到统计数据，mysql提供了5个聚合函数 count(*) 表示计算总行数，括号中写*与列名，结果是相同的 max(列) 表示求此列的最大值 min(列) 表示求此列的最小值 sum(列) 表示求此列的和 avg(列) 表示求此列的平均值 示例： select count(*) from students where id > 18; select max(age) from students where id < 10; 说明： 与offset连用没有效果
12.5.7组合
概述 按字段分组，表示此字段相同的数据会放到一个组中 分组后，只能查询出相同的数据列，对于有差异的数据列无法显示在结果集中 可以对分组后的数据进行统计，做聚合运算 ---------------------------------------------------------------------------------- 语法 select 列1,列2,...,聚合 from 表名 group by 列1,列2,... ---------------------------------------------------------------------------------- 示例： select grade_id,count(*) from students group by grade_id; select grade_id,age,count(*),sum(age) from students group by grade_id,age; ---------------------------------------------------------------------------------- 分组后的数据进行筛选 需求 展示班级人数多于2的组的信息 报错语句 select grade_id,count(*) from students group by grade_id where count(*) > 2; 使用having: 说明 如果对于聚合函数结果进行二次筛选时必须使用having 语法 select 列1,列2,……,聚合 from 表名 group by 列1,列2,…… having 列1,……,聚合,……; 示例 select grade_id,count(*) from students group by grade_id having count(*) > 2; ---------------------------------------------------------------------------------- where与having的区别 where是对from后面指定的表进行数据筛选，对于原始数据的筛选 having是对group by的结果进行筛选(也可对原始数据进行筛选，但是不建议使用) ----------------------------------------------------------------------------------- 假数据： create table grades( id int not null auto_increment primary key, name char(20) ) engine=innodb default charset=utf8; create table students( id int not null auto_increment primary key, name char(20), age int, sex bit, content varchar(50), grade_id int, isDelete bit default 0, constraint fk_students_grades foreign key(grade_id) references grades(id) ) engine=innodb default charset=utf8; insert into grades values (0, "python01"), (0, "python02"), (0, "python03"), (0, "python04"); insert into students values (0, "tom1", 18, 1, "tom1 is a good man",1,0), (0, "tom2", 19, 1, "tom2 is a good man",2,0), (0, "tom3", 18, 0, "tom3 is a good man",1,0), (0, "tom4", 15, 1, "tom4 is a good man",3,0), (0, "tom5", 14, 0, "tom5 is a good man",4,0), (0, "tom6", 20, 0, "tom6 is a good man",4,0), (0, "tom7", 24, 0, "tom7 is a good man",2,0), (0, "tom8", 10, 0, "tom8 is a good man",3,0), (0, "tom9", 25, 0, "tom9 is a good man",1,0), (0, "tom9", 22, 0, "tom10 is a good man",1,0), (0, "tom11", 30, 0, "tom11 is a good man",4,0), (0, "tom12", 38, 0, "tom12 is a good man",3,0);
12.5.8连表操作
需求 显示所有学生，不仅仅显示班级id，也要把班级名称显示出来 ---------------------------------------------------------------------------------- 实现 select * from students, grades; 没有提供关系 select * from students, grades where students.grade_id=grades.id; select students.name,grades.name from students, grades where students.grade_id=grades.id; ---------------------------------------------------------------------------------- 关联分类 表A left join 表B ----表A与表B匹配的行会出现在结果集中，外加表A独有的数据，为对应的数据使用null填充(左边全部显示) 表A right join 表B -----表A与表B匹配的行会出现在结果集中，外加表B独有的数据，为对应的数据使用null填充(右边全部显示) 表A inner join 表B -----表A与表B匹配的行会出现在结果集中(将所有null的行隐藏) ---------------------------------------------------------------------------------- 示例： insert into students(name) values("liudh"); select * from students left join grades on students.grade_id = grades.id; insert into grades values(0, "python05"); select * from students right join grades on students.grade_id = grades.id; select * from students inner join grades on students.grade_id = grades.id; ---------------------------------------------------------------------------------- 说明 早期left与right存在性能差异，现在没有差异 在查询或条件中推荐使用"表名.列名"的语法 如果多个表中列不重复可以省略"表名."部分 如果表的名字太长，可以在表名后面使用“as 简写名”为表起一个临时名称
13.Pymysql模块
作用： 对数据库进行操作（执行SQL语句） 安装： windows： pip install pymysql linux: sudo pip install pymysql
13.1连接数据库
#导入pymysql模块 import pymysql #连接数据库 #参数1：mysql服务器IP地址 #参数2：用户名 #参数3：用户密码 #参数4：要连接的数据库名称 #返回值：操作数据库的唯一标识 db = pymysql.connect("39.106.150.87","echo","echo1996","py1901") #创建cursor对象，用该对象执行SQL语句 cursor = db.cursor() #执行SQL语句 cursor.execute("select version();") #获取返回信息 data = cursor.fetchone() print(data) #断开数据库连接 cursor.close() db.close()
13.2建表
import pymysql db = pymysql.connect("www.sunck.wang", "sunck", "sunck", "py1901") cursor = db.cursor() # 检查表是否存在，如果存在则删除 cursor.execute("drop table if EXISTS grades;") sql = ''' create table grades( id int not null auto_increment primary key, name char(20) ) engine=innodb default charset=utf8; ''' cursor.execute(sql) cursor.execute("drop table if EXISTS students;") sql = ''' create table students( id int not null auto_increment primary key, name char(20), age int, sex bit, content varchar(50), grade_id int, isDelete bit default 0, constraint fk_students_grades foreign key(grade_id) references grades(id) ) engine=innodb default charset=utf8; ''' cursor.execute(sql) cursor.close() db.close()
13.3增
import pymysql db = pymysql.connect("www.sunck.wang", "sunck", "sunck", "py1901") cursor = db.cursor() sql1 = 'insert into grades values(0, "python01");' sql2 = 'insert into grades values(0, "python02");' sql3 = 'insert into grades values(0, "python03");' sql4 = 'insert into grades values(0, "python04");' try: cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute(sql4) #提交事物，真正写入数据库 db.commit() except: #如果提交失败，回滚到上条数据 db.rollback() cursor.close() db.close()
13.4改
import pymysql db = pymysql.connect("www.sunck.wang", "sunck", "sunck", "py1901") cursor = db.cursor() sql = 'update grades set name="python03" where id = 15;' try: cursor.execute(sql) db.commit() except: db.rollback() cursor.close() db.close()
13.5删
import pymysql db = pymysql.connect("39.106.150.87","echo","echo1996","py1901") cursor = db.cursor() sql = "delete from grades where id = 5;" try: cursor.execute(sql) db.commit() except: db.rollback cursor.close() db.close()
13.6查
import pymysql db = pymysql.connect("www.sunck.wang", "sunck", "sunck", "py1901") # cursor = db.cursor() # 以字典形式显示获取的每条数据 cursor = db.cursor(cursor=pymysql.cursors.DictCursor) sql1 = "select * from students where id = 2;" sql2 = "select * from students where id < 5;" try: #获取一条数据 cursor.execute(sql1) ret1 = cursor.fetchone() print(ret1) print("---------------------------") #获取多条数据 cursor.execute(sql2) retlist = cursor.fetchall() for ret2 in retlist: print(ret2) #rowcount属性：是一个只读属性，返回执行execute()方法后影响的行数 print(cursor.rowcount) except: print("Error: unable to fetch data") cursor.close() db.close()
13.7SQL注入
import pymysql account = input("请输入账号：") passwd = input("请输入密码：") db = pymysql.connect("www.sunck.wang","sunck","sunck","py1901") cursor = db.cursor() # sql = "select * from users where account = '%s' and passwd = '%s';"%(account, passwd) # 111111' -- # select * from users where accout = '111111' -- ' and passwd = '2'; # 222222' or 1=1 -- # select * from users where account = '222222' or 1=1 -- ' and passwd = '2'; # sql = "select * from users where account=%s and passwd=%s" sql = "select * from users where account=%(account)s and passwd=%(passwd)s" try: # execute 为执行，格式化的数据在这里传值，以规避SQL注入 # cursor.execute(sql, [account, passwd]) cursor.execute(sql, {"passwd": passwd, "account": account}) ret = cursor.fetchone() if ret: print("登陆成功") else: print("登陆失败") except: print("Error:unable to fetch data") cursor.close() db.close()
13.8增加多条数据
import pymysql db = pymysql.connect("www.sunck.wang", "sunck", "sunck", "py1901") cursor = db.cursor() sql = "insert into students(name) values(%s);" try: cursor.executemany(sql, [("lilei",),("liudh",),("abc",)]) db.commit() except: db.rollback() cursor.close() db.close()
13.9新插入数据的自增id
import pymysql db = pymysql.connect("www.sunck.wang", "sunck", "sunck", "py1901") cursor = db.cursor() sql = "insert into grades values(0,'python05')" try: cursor.execute(sql) db.commit() #继续给该班级插入1个学生 #cursor.lastrowid：插入成功后要插入具体学生，但是不知道班级id，可以使用该属性 sql2 = "insert into students(name, grade_id) values('zhoujl',%d)"%(cursor.lastrowid) cursor.execute(sql2) db.commit() except: db.rollback() cursor.close() db.close()
14.计划执行
作用： 让mysql预估执行操作（一般正确） 使用： explain SQL语句
15.分页优化
1.分页查询 select * from student limit 40000，10； ---------------------------------------------------------------------------------- 2.解决方案 （1）不让访问 （2）索引表中扫描 select * from student where id in（select id from（select id from student limit 40000,10）as t）; ---------------------------------------------------------------------------------- (3)记住当前页数数据的最大id和最小id 只有上一页和下一页： 下一页：select * from student where id > max_id limit 10; 上一页：select * from student where id < min_id limit 10; 上一页 192 193 [194] 195 196 197 下一页 page1 = 原页码 page2 = 现页码 num = 每页条数 max_id = 原页数据最大id min_id = 原页数据最小id count1 = (page2-page1)*num count2 = (page2-page1-1)*num select * from student where id in (select id from (select id from student where id > max_id limit count1)as b limit count2,num)as a); 示例： select * from student where id in(select id from (select id from (select id from student where id > 1950 limit 20) as b limit 10,10) as a); select * from student where id in(select id from (select id from (select id from student where id < 1950 limit 30) as b limit 20,10) as a);
16.Mysql视图
概述：类似临时表，给SQL语句起别名，方便后面使用 创建视图： 格式：create view 视图名称 as sql语句； 示例：create view v1 as select * from student where id>800000; 使用视图：select * from v1 where age = 5; 注意：视图虚拟存在 删除：drop view 视图名称； 修改：alter view 视图名称 as SQL语句； 说明：视图不经常使用，可读性不高
17.触发器
作用：当对某张表做增、删、改操作时，可以使用触发器自定义关联行为 举例：在用户注册会在用户表中添加一条数据，同时也会在日志表中增加一条数据 ---------------------------------------------------------------------------------- 创建触发器： create trigger 触发器名 before insert on 表名 for each row begin …… end ---------------------------------------------------------------------------------- 结束字符问题： create trigger after_grades_students after insert on grades for each row begin insert into students(name) values("qwert"); end 此时会因为语句中的分号而导致创建触发器失败 ---------------------------------------------------------------------------------- 修改结束字符： delimiter 修改终止符 示例： delimiter // create trigger after_grades_students after insert on grades for each row begin insert into students(name) values("qwert"); end // delimiter ; ---------------------------------------------------------------------------------- 需求 插入一个班级，插入一个学生 delimiter // create trigger after_grades_students after insert on grades for each row begin insert into students(name) values("qwert"); end // delimiter ; insert into grades values(0, "python07");
18.Mysql中的函数
内置函数 详见<内置函数.txt> 自定义函数： 格式： delimiter // create function 函数名( 参数列表) returns 返回值类型 begin 功能的实现 return(返回值) end // delimiter ; 示例： delimiter // create function mySum( a int, b int) returns int begin declare num int default 0; set num = a + b; return(num); end // delimiter ; 使用自定义函数： select mySum(1,2);
19.多余字段
建议：今后在建表时额外增加几个无用的字段作为备用 create table persons( id int not null auto_increment primary key, name char(20), age int, sex bit, isDelete bit default 0, a int default 0, b int default 0, c char(20) default "", d char(20) default "" ) engine=innodb default charset=utf8;
扫一扫
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
