Python之路（二十七）：Mysql（下）
Mysql（二）数据库及表操作、多表查询、索引、事务、Py连接MySQL
本结目录
- 数据库操作
- 数据表操作
- 外键约束
- 多表查询
- 索引
- 事务
- python连接Mysql
数据库操作
此操作针对windows环境下
该操作再Mysql服务器启动以及客户端连接之后
对数据库操作之前，引申一些较为听的懂的白话
|
1
2
3
4
5
6
|
概念 数据库(文件夹) 数据库表(文件) 数据行(文件中的一行数据)一个数据库就是一个文件夹 |
1、显示数据库
show databases; -- 后面记得跟英文字符的分号 show create database 数据库名称; -- 查看数据库创建信息
默认数据库：
mysql - 用户权限相关数据
test - 用于用户测试数据
information_schema - MySQL本身架构相关数据
2、创建数据库
show databases; --查看当前Mysql都有那些数据，根目录都有那些文件夹 create database 数据库名; --创建文件夹 use 数据库名; --使用选中数据库，进入目录 show tables; --查看当前数据库下都有那些表 create table 表名(nidint, namevarchar(20), pwdvarchar(64)); --创建数据库表 /*阐释 相当于创建一个tb1的表 有nid,name,pwd三列 nid int 为int类型 varchar(20) 为字符类型最多20*/ select * from 表名; --查看表中的所有数据 insert into 表名(nid, name, pwd) values(1, 'alex', '123'); --插入数据
3、修改数据库
alter databases 数据库名称 character set utf8; -- 修改数据库编码格式
4、删除数据库
drop database [if exists] db_name;
5、用户管理
显示当前使用的数据库中所有表：show tables;
PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）
show databases; use mysql; show tables; 可以看到mysql下有多少张表 user表是用户表 desc user; 查看user表有多少列 select host,user from user; 可以看到用户
创建用户 create user '用户名'@'IP地址' identified by '密码'; 删除用户 drop user '用户名'@'IP地址'; 修改用户 rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';; 修改密码 set password for '用户名'@'IP地址' = Password('新密码')
数据表操作
1、创建数据表
create table 表名( 列名 类型 是否可以为空， 列名 类型 是否可以为空 )ENGINE=InnoDB DEFAULT CHARSET=utf8 -- ENGINE=InnoDB 表示引擎 后为默认编码格式为utf
-- 是否可空，null表示空，非字符串 not null -- 不可空 null -- 可空
-- 默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值 create table tb1( nid int not null defalut 2, num int not null )
-- 主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。 create table tb1( nid int not null auto_increment primary key, num int null ) 或 create table tb1( nid int not null, num int not null, primary key(nid,num) )
-- 自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列） create table tb1( nid int not null auto_increment primary key, num int null ) 或 create table tb1( nid int not null auto_increment, num int null, index(nid) ) -- 对于自增列，必须是索引（含主键）。 -- 对于自增可以设置步长和起始值 show session variables like 'auto_inc%'; set session auto_increment_increment=2; set session auto_increment_offset=10; shwo global variables like 'auto_inc%'; set global auto_increment_increment=2; set global auto_increment_offset=10;
2、查看数据表
show tables; -- 显示当前数据库所有表 desc 表名; -- 查看表结构 show create table 表名 -- 查看完整表创建信息
3、修改表结构
--增加列(字段) --添加多字段用逗号隔开进行 alter table 表名 add 列名 类型 [约束条件]; --删除列(字段) --多字段删除也用逗号隔开,无需加数据类型 alter table 表明 drop 列名1,列名2...; --修改列(字段) --列类型修改 alter table 表名 modify 列名 类型 [约束条件][first|after 列名]; --列名称修改 alter table 表名 change 原列名 新列名 类型 [约束条件][first|after 列名];
4、修改表名
rename table 原表名 to 新表名;
5、删除表
drop table 表名;
表记录操作
1、增
insert into 表 (列名,列名...) values (值,值,值...) insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...) insert into 表 set 列名=值,列名=值...; -- insert into 表 (列名,列名...) select (列名,列名...) from 表
2、删
delete from 表名 where 字段键值对; -- 可多条多虑 delete from 表名; truncate table 表名; -- 如不进行筛选,则删除表的所有记录(表依旧存在) -- delete 逐条删除,留下空表 -- truncate 先删除表,而后copy一个与之前相同的空表
3、改
update 表 set name ＝ 'Tony' where id>1 -- 多条可以用逗号隔开进行修改 -- 进行筛选则单独对筛选的那部分进行修改 -- 不进行筛选,则修改所有符合的部分
4、查(*****)
select * from 表 -- 查看表中的所有数据 select * from 表 where id > 1 -- 筛选id大于1的所有数据 select nid,name,gender as gg from 表 where id > 1 -- 查看id大于1的nid,name,gender的三个字段 as ... 表示进行别名设置
A、条件 select * from 表 where id > 1 and name != 'alex' and num = 12; -- 筛选id>1且name不为alex且num为12的数据 select * from 表 where id between 5 and 16; -- 筛选id值在5到16范围内的数据 select * from 表 where id in (11,22,33) -- 筛选id值为11或22或33的数据 select * from 表 where id not in (11,22,33) -- 反之 select * from 表 where id in (select nid from 表) B、SQL通配符 select * from 表 where name like '%le%' -- 选取name包含有le的所有数据 select * from 表 where name like 'ale_' -- ale开头的所有（一个字符） select * from 表 where name regexp "^[awv]"; -- 选取name以'a'、'w'或'v'开始的所有数据 select * from tb where name regexp "^[a-c]"; -- 选取name以a到c开头范围内的所有的数据 select * from tb where name regexp "^[^a-c]";-- 选取name非以a到c开头的所有数据 C、限制 select * from tb limit 2; -- 前2行 select * from tb limit 2,2; -- 从第2行开始的后2行 select * from tb limit 2 offset 2; -- 从第2行开始的后2行 D、排序 select * from tb order by name asc; -- 按照name升序排列 select * from tb order by name desc; -- 按照name降序排列 E、分组 select * from tb group by name; -- 根据名字分组 select * from tb group by 2; -- 根据第2个字段分组 select * from employee_tbl group by name having id>4; -- 根据名字分组且id大于4的数据 -- where作用于分组前,having作用于分组后且可以用聚合函数,在where中就不行 F、聚合函数(经常作用于分组查询配合使用) SUM(字段) -- 求和 COUNT(字段) -- 次数统计 AVG(字段) -- 平均值 MAX(字段) -- 最大 MIN(字段) -- 最小
= -- 等于 <> -- 不等于。注释：在 SQL 的一些版本中，该操作符可被写成 != > -- 大于 < -- 小于 >= -- 大于等于 <= -- 小于等于 BETWEEN-- 在某个范围内 LIKE -- 搜索某种模式 IN -- 指定针对某个列的多个可能值
以上都只是单表性的查询,例如模拟在实际生活中,会有一张员工表,而员工会有其归属的部门,那么相应的也会有一张部门表.在其中相应的俩者之间会有一种相应的关联,那么这里引申了外键及多表查询
外键约束
外键可以理解为一种约束,它有以下限制(FOREIGN KEY为创建外键的关键词)
注意：作为外键一定要和关联主键的数据类型保持一致
- FOREIGN KEY 约束用于预防破坏表之间连接的行为。
- FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
创建外键
-- 1、建立从表的时候就和主表建立外键 CREATE TABLE TABLE_NAME( '字段' 类型, '字段' 类型, ... ... FOREIGN KEY (从表字段) REFERENCES 主表(字段) ); -- 2、建表完成之后,也可以通过sql语句和主表建立联系 ALTER TABLE 从表 ADD CONSTRAINT 外键名称（形如：FK_从表_主表） FOREIGN KEY (从表字段) REFERENCES 主表(字段);
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称
-- 创建表 班级表和学生表 CREATE TABLE class( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), priece INT ); DESC class; INSERT INTO class(name,priece) VALUES ("python",15800), ("linux",14800), ("go",16800), ("java",18800); SELECT * FROM class; CREATE TABLE student (sid INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), age INT, sex TINYINT(1), class_id INT, FOREIGN KEY (class_id) REFERENCES class(id)); SHOW CREATE TABLE student; DESC student; INSERT INTO student(name,age,sex,class_id) VALUES ("tony1",18,0,1), ("tony2",19,1,1), ("tony3",17,1,2), ("tony4",19,1,3), ("tony5",19,0,2), ("tony6",20,1,4); SELECT * FROM student2; CREATE TABLE student2(sid INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), age INT, sex TINYINT(1), class_id INT); -- 建表之后添加外键 ALTER TABLE student2 ADD CONSTRAINT a1 FOREIGN KEY (class_id) REFERENCES class(id); SHOW CREATE TABLE student2; -- 这样绑定的class_id超出class的id则不让插入 INSERT INTO student2(name, age, sex, class_id) VALUES ("tony8",17,0,1); -- 删除外键 ALTER TABLE student2 DROP FOREIGN KEY a1; -- 删除外键之后，则可以插入超出class id范围的数据 INSERT INTO student2(name, age, sex, class_id) VALUES ("tony9",17,0,5);
INOODB下支持的ON语句
--外键约束对子表的含义: 如果在父表中找不到候选键,则不允许在子表上进行insert/update --外键约束对父表的含义: 在父表上进行update/delete以更新或删除在子表中有一条或多条对 -- 应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的 -- on update/on delete子句 -----------------innodb支持的四种方式--------------------------------------- -----cascade方式 在父表上update/delete记录时，同步update/delete掉子表的匹配记录 -----外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动被删除-------- FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE CASCADE ------set null方式 在父表上update/delete记录时，将子表上匹配记录的列设为null -- 要注意子表的外键列不能为not null FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE SET NULL ------Restrict方式 :拒绝对父表进行删除更新操作(了解) ------No action方式 在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键 -- 进行update/delete操作（了解）
多表查询
join 用于把来自两个或多个表的行结合起来。
不同的 SQL JOIN 类型：
- INNER JOIN：如果表中有至少一个匹配，则返回行
- LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
- RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
- FULL JOIN：只要其中一个表中存在匹配，则返回行(mysql不支持全外连接,而是使用union与union all)
无对应关系则不显示 select A.num, A.name, B.name from A,B Where A.nid = B.nid 无对应关系则不显示 select A.num, A.name, B.name from A inner join B on A.nid = B.nid A表所有显示，如果B中无对应关系，则值为null select A.num, A.name, B.name from A left join B on A.nid = B.nid B表所有显示，如果B中无对应关系，则值为null select A.num, A.name, B.name from A right join B on A.nid = B.nid
索引
1、概述
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
2、分类
- 普通索引：仅加速查询
- 唯一索引：加速查询 + 列值唯一（可以有null）
- 主键索引：加速查询 + 列值唯一 + 表中只有一个（不可以有null）
- 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
- 全文索引：对文本的内容进行分词，进行搜索
索引合并：使用多个单列索引组合查询搜索
覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖
a、普通索引
# 创建表直接指定索引 CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX ix_name (username(length)) );
# 表外创建索引 CREATE INDEX indexName ON mytable(username(length)); # 修改表结构 ALTER mytable ADD INDEX indexName ON (username(length));
# 删除索引 DROP INDEX indexname ON tablename;
b、唯一索引
# 创建表直接指定唯一索引 CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );
# 创建唯一索引 CREATE UNIQUE INDEX indexName ON mytable(username(length))
# 删除索引 DROP UNIQUE INDEX 索引名 ON 表名
c、主键索引
# 创建主键索引 CREATE TABLE t1( id INT PRIMARY KEY, name VARCHAR(16) ); OR CREATE TABLE t1( id INT NOT NULL, name VARCHAR(16), PRIMARY KEY(id) );
# 添加主键 ALTER TABLE 表名 DROP PRIMARY KEY;
# 删除主键 ALTER TABLE 表名 DROP PRIMARY KEY; ALTER TABLE 表名 MODIFY 列名 类型,DROP PRIMARY KEY;
d、组合索引
组合索引是多个列组合成一个索引来查询
应用场景：频繁的同时使用多列来进行查询，如：where name = 'Tony' and age = 18。
# 创建表 CREATE TABLE demo( nid INT NOT NULL auto_increment PRIMARY KEY , name VARCHAR(32) NOT NULL, age INT NOT NULL ) # 创建组合索引 CREATE INDEX ix_name_age ON demo(name,age);
如上创建组合索引之后，查询一定要注意：
- name and email -- >使用索引，name一定要放前面
- name -- >使用索引
- email -- >不使用索引
注意：同时搜索多个条件时，组合索引的性能效率好过于多个单一索引合并。
e、全文索引
# 创建全文索引 CREATE TABLE emp3 ( id INT, name VARCHAR(30) , resume VARCHAR(50), FULLTEXT INDEX index_resume (resume) );
3、相关
# 显示索引信息 SHOW INDEX FROM table_name;
事务
1、概述
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务，但是一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。！
- 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
- 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
- 事务用来管理insert,update,delete语句
一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）
- 1、事务的原子性：一组事务，要么成功；要么撤回。
- 2、稳定性 ： 有非法数据（外键约束之类），事务撤回。
- 3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
- 4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。
2、事务操作
- 开启事务 start transaction
- 回滚事务 rollback
- 提交事务 commit
- 保留点 savepoint
View Codepy连接MySQL
pymsql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同
1、下载安装
|
1
|
pip3 install pymysql |
2、操作使用
执行SQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysql # 创建连接conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')# 创建游标cursor = conn.cursor() # 执行SQL，并返回收影响行数effect_row = cursor.execute("update hosts set host = '1.1.1.2'") # 执行SQL，并返回受影响行数#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,)) # 执行SQL，并返回受影响行数#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)]) # 提交，不然无法保存新建或者修改的数据conn.commit() # 关闭游标cursor.close()# 关闭连接conn.close() |
获取查询数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')cursor = conn.cursor()cursor.execute("select * from hosts") # 获取第一行数据row_1 = cursor.fetchone() # 获取前n行数据# row_2 = cursor.fetchmany(3)# 获取所有数据# row_3 = cursor.fetchall() conn.commit()cursor.close()conn.close() |
注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：
- cursor.scroll(1,mode='relative') # 相对当前位置移动
- cursor.scroll(2,mode='absolute') # 相对绝对位置移动
fetch数据类型
关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') # 游标设置为字典类型cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)r = cursor.execute("call p1()") result = cursor.fetchone() conn.commit()cursor.close()conn.close() |
Mysql（二）数据库及表操作、多表查询、索引、事务、Py连接MySQL
本结目录
- 数据库操作
- 数据表操作
- 外键约束
- 多表查询
- 索引
- 事务
- python连接Mysql
数据库操作
此操作针对windows环境下
该操作再Mysql服务器启动以及客户端连接之后
对数据库操作之前，引申一些较为听的懂的白话
|
1
2
3
4
5
6
|
概念 数据库(文件夹) 数据库表(文件) 数据行(文件中的一行数据)一个数据库就是一个文件夹 |
1、显示数据库
show databases; -- 后面记得跟英文字符的分号 show create database 数据库名称; -- 查看数据库创建信息
默认数据库：
mysql - 用户权限相关数据
test - 用于用户测试数据
information_schema - MySQL本身架构相关数据
2、创建数据库
show databases; --查看当前Mysql都有那些数据，根目录都有那些文件夹 create database 数据库名; --创建文件夹 use 数据库名; --使用选中数据库，进入目录 show tables; --查看当前数据库下都有那些表 create table 表名(nidint, namevarchar(20), pwdvarchar(64)); --创建数据库表 /*阐释 相当于创建一个tb1的表 有nid,name,pwd三列 nid int 为int类型 varchar(20) 为字符类型最多20*/ select * from 表名; --查看表中的所有数据 insert into 表名(nid, name, pwd) values(1, 'alex', '123'); --插入数据
3、修改数据库
alter databases 数据库名称 character set utf8; -- 修改数据库编码格式
4、删除数据库
drop database [if exists] db_name;
5、用户管理
显示当前使用的数据库中所有表：show tables;
PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）
show databases; use mysql; show tables; 可以看到mysql下有多少张表 user表是用户表 desc user; 查看user表有多少列 select host,user from user; 可以看到用户
创建用户 create user '用户名'@'IP地址' identified by '密码'; 删除用户 drop user '用户名'@'IP地址'; 修改用户 rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';; 修改密码 set password for '用户名'@'IP地址' = Password('新密码')
数据表操作
1、创建数据表
create table 表名( 列名 类型 是否可以为空， 列名 类型 是否可以为空 )ENGINE=InnoDB DEFAULT CHARSET=utf8 -- ENGINE=InnoDB 表示引擎 后为默认编码格式为utf
-- 是否可空，null表示空，非字符串 not null -- 不可空 null -- 可空
-- 默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值 create table tb1( nid int not null defalut 2, num int not null )
-- 主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。 create table tb1( nid int not null auto_increment primary key, num int null ) 或 create table tb1( nid int not null, num int not null, primary key(nid,num) )
-- 自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列） create table tb1( nid int not null auto_increment primary key, num int null ) 或 create table tb1( nid int not null auto_increment, num int null, index(nid) ) -- 对于自增列，必须是索引（含主键）。 -- 对于自增可以设置步长和起始值 show session variables like 'auto_inc%'; set session auto_increment_increment=2; set session auto_increment_offset=10; shwo global variables like 'auto_inc%'; set global auto_increment_increment=2; set global auto_increment_offset=10;
2、查看数据表
show tables; -- 显示当前数据库所有表 desc 表名; -- 查看表结构 show create table 表名 -- 查看完整表创建信息
3、修改表结构
--增加列(字段) --添加多字段用逗号隔开进行 alter table 表名 add 列名 类型 [约束条件]; --删除列(字段) --多字段删除也用逗号隔开,无需加数据类型 alter table 表明 drop 列名1,列名2...; --修改列(字段) --列类型修改 alter table 表名 modify 列名 类型 [约束条件][first|after 列名]; --列名称修改 alter table 表名 change 原列名 新列名 类型 [约束条件][first|after 列名];
4、修改表名
rename table 原表名 to 新表名;
5、删除表
drop table 表名;
表记录操作
1、增
insert into 表 (列名,列名...) values (值,值,值...) insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...) insert into 表 set 列名=值,列名=值...; -- insert into 表 (列名,列名...) select (列名,列名...) from 表
2、删
delete from 表名 where 字段键值对; -- 可多条多虑 delete from 表名; truncate table 表名; -- 如不进行筛选,则删除表的所有记录(表依旧存在) -- delete 逐条删除,留下空表 -- truncate 先删除表,而后copy一个与之前相同的空表
3、改
update 表 set name ＝ 'Tony' where id>1 -- 多条可以用逗号隔开进行修改 -- 进行筛选则单独对筛选的那部分进行修改 -- 不进行筛选,则修改所有符合的部分
4、查(*****)
select * from 表 -- 查看表中的所有数据 select * from 表 where id > 1 -- 筛选id大于1的所有数据 select nid,name,gender as gg from 表 where id > 1 -- 查看id大于1的nid,name,gender的三个字段 as ... 表示进行别名设置
A、条件 select * from 表 where id > 1 and name != 'alex' and num = 12; -- 筛选id>1且name不为alex且num为12的数据 select * from 表 where id between 5 and 16; -- 筛选id值在5到16范围内的数据 select * from 表 where id in (11,22,33) -- 筛选id值为11或22或33的数据 select * from 表 where id not in (11,22,33) -- 反之 select * from 表 where id in (select nid from 表) B、SQL通配符 select * from 表 where name like '%le%' -- 选取name包含有le的所有数据 select * from 表 where name like 'ale_' -- ale开头的所有（一个字符） select * from 表 where name regexp "^[awv]"; -- 选取name以'a'、'w'或'v'开始的所有数据 select * from tb where name regexp "^[a-c]"; -- 选取name以a到c开头范围内的所有的数据 select * from tb where name regexp "^[^a-c]";-- 选取name非以a到c开头的所有数据 C、限制 select * from tb limit 2; -- 前2行 select * from tb limit 2,2; -- 从第2行开始的后2行 select * from tb limit 2 offset 2; -- 从第2行开始的后2行 D、排序 select * from tb order by name asc; -- 按照name升序排列 select * from tb order by name desc; -- 按照name降序排列 E、分组 select * from tb group by name; -- 根据名字分组 select * from tb group by 2; -- 根据第2个字段分组 select * from employee_tbl group by name having id>4; -- 根据名字分组且id大于4的数据 -- where作用于分组前,having作用于分组后且可以用聚合函数,在where中就不行 F、聚合函数(经常作用于分组查询配合使用) SUM(字段) -- 求和 COUNT(字段) -- 次数统计 AVG(字段) -- 平均值 MAX(字段) -- 最大 MIN(字段) -- 最小
= -- 等于 <> -- 不等于。注释：在 SQL 的一些版本中，该操作符可被写成 != > -- 大于 < -- 小于 >= -- 大于等于 <= -- 小于等于 BETWEEN-- 在某个范围内 LIKE -- 搜索某种模式 IN -- 指定针对某个列的多个可能值
以上都只是单表性的查询,例如模拟在实际生活中,会有一张员工表,而员工会有其归属的部门,那么相应的也会有一张部门表.在其中相应的俩者之间会有一种相应的关联,那么这里引申了外键及多表查询
外键约束
外键可以理解为一种约束,它有以下限制(FOREIGN KEY为创建外键的关键词)
注意：作为外键一定要和关联主键的数据类型保持一致
- FOREIGN KEY 约束用于预防破坏表之间连接的行为。
- FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
创建外键
-- 1、建立从表的时候就和主表建立外键 CREATE TABLE TABLE_NAME( '字段' 类型, '字段' 类型, ... ... FOREIGN KEY (从表字段) REFERENCES 主表(字段) ); -- 2、建表完成之后,也可以通过sql语句和主表建立联系 ALTER TABLE 从表 ADD CONSTRAINT 外键名称（形如：FK_从表_主表） FOREIGN KEY (从表字段) REFERENCES 主表(字段);
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称
-- 创建表 班级表和学生表 CREATE TABLE class( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), priece INT ); DESC class; INSERT INTO class(name,priece) VALUES ("python",15800), ("linux",14800), ("go",16800), ("java",18800); SELECT * FROM class; CREATE TABLE student (sid INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), age INT, sex TINYINT(1), class_id INT, FOREIGN KEY (class_id) REFERENCES class(id)); SHOW CREATE TABLE student; DESC student; INSERT INTO student(name,age,sex,class_id) VALUES ("tony1",18,0,1), ("tony2",19,1,1), ("tony3",17,1,2), ("tony4",19,1,3), ("tony5",19,0,2), ("tony6",20,1,4); SELECT * FROM student2; CREATE TABLE student2(sid INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), age INT, sex TINYINT(1), class_id INT); -- 建表之后添加外键 ALTER TABLE student2 ADD CONSTRAINT a1 FOREIGN KEY (class_id) REFERENCES class(id); SHOW CREATE TABLE student2; -- 这样绑定的class_id超出class的id则不让插入 INSERT INTO student2(name, age, sex, class_id) VALUES ("tony8",17,0,1); -- 删除外键 ALTER TABLE student2 DROP FOREIGN KEY a1; -- 删除外键之后，则可以插入超出class id范围的数据 INSERT INTO student2(name, age, sex, class_id) VALUES ("tony9",17,0,5);
INOODB下支持的ON语句
--外键约束对子表的含义: 如果在父表中找不到候选键,则不允许在子表上进行insert/update --外键约束对父表的含义: 在父表上进行update/delete以更新或删除在子表中有一条或多条对 -- 应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的 -- on update/on delete子句 -----------------innodb支持的四种方式--------------------------------------- -----cascade方式 在父表上update/delete记录时，同步update/delete掉子表的匹配记录 -----外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动被删除-------- FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE CASCADE ------set null方式 在父表上update/delete记录时，将子表上匹配记录的列设为null -- 要注意子表的外键列不能为not null FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE SET NULL ------Restrict方式 :拒绝对父表进行删除更新操作(了解) ------No action方式 在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键 -- 进行update/delete操作（了解）
多表查询
join 用于把来自两个或多个表的行结合起来。
不同的 SQL JOIN 类型：
- INNER JOIN：如果表中有至少一个匹配，则返回行
- LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
- RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
- FULL JOIN：只要其中一个表中存在匹配，则返回行(mysql不支持全外连接,而是使用union与union all)
无对应关系则不显示 select A.num, A.name, B.name from A,B Where A.nid = B.nid 无对应关系则不显示 select A.num, A.name, B.name from A inner join B on A.nid = B.nid A表所有显示，如果B中无对应关系，则值为null select A.num, A.name, B.name from A left join B on A.nid = B.nid B表所有显示，如果B中无对应关系，则值为null select A.num, A.name, B.name from A right join B on A.nid = B.nid
索引
1、概述
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
2、分类
- 普通索引：仅加速查询
- 唯一索引：加速查询 + 列值唯一（可以有null）
- 主键索引：加速查询 + 列值唯一 + 表中只有一个（不可以有null）
- 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
- 全文索引：对文本的内容进行分词，进行搜索
索引合并：使用多个单列索引组合查询搜索
覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖
a、普通索引
# 创建表直接指定索引 CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX ix_name (username(length)) );
# 表外创建索引 CREATE INDEX indexName ON mytable(username(length)); # 修改表结构 ALTER mytable ADD INDEX indexName ON (username(length));
# 删除索引 DROP INDEX indexname ON tablename;
b、唯一索引
# 创建表直接指定唯一索引 CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );
# 创建唯一索引 CREATE UNIQUE INDEX indexName ON mytable(username(length))
# 删除索引 DROP UNIQUE INDEX 索引名 ON 表名
c、主键索引
# 创建主键索引 CREATE TABLE t1( id INT PRIMARY KEY, name VARCHAR(16) ); OR CREATE TABLE t1( id INT NOT NULL, name VARCHAR(16), PRIMARY KEY(id) );
# 添加主键 ALTER TABLE 表名 DROP PRIMARY KEY;
# 删除主键 ALTER TABLE 表名 DROP PRIMARY KEY; ALTER TABLE 表名 MODIFY 列名 类型,DROP PRIMARY KEY;
d、组合索引
组合索引是多个列组合成一个索引来查询
应用场景：频繁的同时使用多列来进行查询，如：where name = 'Tony' and age = 18。
# 创建表 CREATE TABLE demo( nid INT NOT NULL auto_increment PRIMARY KEY , name VARCHAR(32) NOT NULL, age INT NOT NULL ) # 创建组合索引 CREATE INDEX ix_name_age ON demo(name,age);
如上创建组合索引之后，查询一定要注意：
- name and email -- >使用索引，name一定要放前面
- name -- >使用索引
- email -- >不使用索引
注意：同时搜索多个条件时，组合索引的性能效率好过于多个单一索引合并。
e、全文索引
# 创建全文索引 CREATE TABLE emp3 ( id INT, name VARCHAR(30) , resume VARCHAR(50), FULLTEXT INDEX index_resume (resume) );
3、相关
# 显示索引信息 SHOW INDEX FROM table_name;
事务
1、概述
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务，但是一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。！
- 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
- 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
- 事务用来管理insert,update,delete语句
一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）
- 1、事务的原子性：一组事务，要么成功；要么撤回。
- 2、稳定性 ： 有非法数据（外键约束之类），事务撤回。
- 3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
- 4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。
2、事务操作
- 开启事务 start transaction
- 回滚事务 rollback
- 提交事务 commit
- 保留点 savepoint
View Codepy连接MySQL
pymsql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同
1、下载安装
|
1
|
pip3 install pymysql |
2、操作使用
执行SQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysql # 创建连接conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')# 创建游标cursor = conn.cursor() # 执行SQL，并返回收影响行数effect_row = cursor.execute("update hosts set host = '1.1.1.2'") # 执行SQL，并返回受影响行数#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,)) # 执行SQL，并返回受影响行数#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)]) # 提交，不然无法保存新建或者修改的数据conn.commit() # 关闭游标cursor.close()# 关闭连接conn.close() |
获取查询数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')cursor = conn.cursor()cursor.execute("select * from hosts") # 获取第一行数据row_1 = cursor.fetchone() # 获取前n行数据# row_2 = cursor.fetchmany(3)# 获取所有数据# row_3 = cursor.fetchall() conn.commit()cursor.close()conn.close() |
注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：
- cursor.scroll(1,mode='relative') # 相对当前位置移动
- cursor.scroll(2,mode='absolute') # 相对绝对位置移动
fetch数据类型
关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') # 游标设置为字典类型cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)r = cursor.execute("call p1()") result = cursor.fetchone() conn.commit()cursor.close()conn.close() |
