python_MySQL数据库
MySQL数据库的特点：
1、是关系型数据库
关系型数据库的特点
1、数据是以行和列的的形式存储的
2、这一系列的行和列称为表
3、表中的每一行叫做记录
4、表中的每一列叫做字段
5、表和表之间的逻辑关联叫做关系
6、关系型数据库的核心内容是：关系 即二维表
示例：
1、关系型数据库存储
表1：学生信息表
姓名 年龄 班级
aa 20 1712
bb 25 1713
表2：班级信息表
班级 班主任
1712 曹操
1713 刘备
说明：要找到学生aa的班主任是谁，需要通过班级字段来找到班主任
2、非关系型数据库存储（是以键值对的形式存储的）
{姓名：“aa”，年龄：20，班级：1712，班主任：“曹操”}
二者之间的比较：
非关系行数据的性能比较高（查找数据的速度）但是会造成数据的冗余
2、跨平台
可以在Unix、Linux、Windows上运行MySQL服务
3、支持多种语言编程语言
4、是基于硬盘的读写
MySQL的安装：
1、Linux系统下安装MySQL服务
1、安装服务端：sudo apt-get install mysql-server
2、安装客户端：sudo apt-get install mysql-client
3、初始的用户名和密码在/etc/mysql/debian.cnf文件中，使用 cd /etc/mysql/ 进去目录，subl debian.cnf 打开查看
启动和连接MySQL服务
1、服务端启动
1、查看MySQL服务的状态：sudo /etc/init.d/mysql status
2、启动MySQL服务：sudo /etc/init.d/mysql start
3、停止MySQL服务：sudo /etc/init.d/mysql stop
4、重启MySQL服务：sudo /etc/init.d/mysql restart
2、客户端链接
1、命令格式：mysql -h主机名 -u用户名 -p密码（-h和-u后面可以添加空格，-p后面不可以）
mysql -h localhost -u debian-sys-maint -pLo0r79JmxvMFNtA
2、本地连接可以省略 -h 选项
mysql -u debian-sys-maint -pLo0r79JmxvMFNtA
3、断开与服务端的连接：exit quit \q
基本的SQL命令：
1、SQL命令的使用规则
1、每条命令必须以分号（；）结尾（说明中文分号无效）
2、SQL命令不区分字母大小写
3、使用\c终止命令的执行
2、库的管理（指定字符集）
1、库的基本操作
1、查看已有的库：show databases；
（有四个默认的库，不要修改或删除：information_schema，mysql，performance_schema，sys）
2、创建库：create database 库名 default charset=utf8；
3、查看创建库的语句：show create database 库名；
（ CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */）
4、查看当前所在库：select database();
5、切换库：use 库名；
6、查看库中已有的表：show tables；（如果当前的库为NULL则会报错：ERROR 1046 (3D000): No database selected）
7、删除库：drop database 库名；
2、库的命名规则
1、可以使用数字、字母、_,但是不能是纯数字
2、库名区分字母大小写
3、库名具有唯一性（不能重复）
4、不能使用特殊字符和mysql关键字
3、表的管理：
1、表的基本操作：
1、创建表：
create table 表名（
字段名1 数据类型，
字段名2 数据类型,
....
）
2、查看创建表的语句（字符集）：show create table 表名；
3、查看表结构：desc 表名；
4、删除表：drop table 表名；
2、注意：
1、所有的数据都是以文件的形式存储在数据库目录下
2、数据库目录：/var/lib/mysql
4、表记录的管理：
1、在表中插入记录
1、insert into 表名 values(值1,值2），... 说明:值就是每个字段对应的值，
2、insert into 表名（字段名1，字段名2...）values（值1，值2...)说明：给指定的字段添加值
3、insert into 表名 values（一条记录1）,（一条记录2）... 说明：可以同时添加多条记录
2、查看表记录
1、select * from 表名；*号代表所有的字段
2、select 字段名1，字段名2，... from 表名；
3、更新表的记录
1、update 表名 set 字段=值，字段=值，...where 条件；
2、注意：update语句后如果不加where子句，表中所有记录该字段的值都会更改
4、删除表记录
1、delete from 表名 where 条件1 and 条件2；
2、注意：delete语句后如果不加where子句，表中的所有记录都会被删除
5、如何更改默认字符集：
1、方法：通过更改Mysql的配置文件实现
2、步骤：
1、获取root权限：sudo -i
2、修改mysql配置文件：vi /etc/mysql/mysql.conf.d/mysqld.cnf
1、点击a键进入编辑状态
2、在[mysqld]下 插入character_set_server = utf8
3、点击Esc键退出编辑状态
4、点击Shift + ：
4、输入wq命令写入并退出 （输入q！退出不保存）
3、重启mysql服务：sudo /etc/init.d/mysql restart
说明：更改默认字符集之后，并不影响之前创建好的数据库和数据库中的数据表，要想修改已经创建好的数据库编码和数据表的编码：
数据库：alter database fruit character set utf8;
数据表：alter table user character set utf8;
客户端把数据存储到数据库服务器上的过程(面试会问到)
1、连接到数据库服务器：mysql -h -u -p
2、选择库：use 库名；
3、创建/修改表
4、断开与数据库的连接：exit 、quit、\q
数据类型：
1、数值类型（有符号signed 和 无符号 unsigned）
1、整型
1、int 大整型（占4个字节）：
取值范围：0~2**32-1
2、tinyint 微小整型（占1字节）
1、有符号（signed默认）取值范围：-128~127
2、无符号（unsigned） 取值范围：0~2**8-1
3、smallint 小整型（占2字节）
取值范围：0~65535
4、bigint 极大整型（占8字节）
取值范围：0~2**64-1
2、浮点型
1、float(4个字节，最多显示7个有效位)
1、用法：字段名 float（m，n） m代表总位数，n代表小数位位数
float（5,2）取值范围：-999.99~999.99；会根据小数第3位进行四舍五入
说明：m总位数不要超过7位，否则7位之后数字会随机生成。
2、double（8字节，最多显示15个有效位）
用法：字段名 double（m，n）
3、decimal（占M+2个字节，最多显示28个有效位）
用法：decimal（M，D）
说明：浮点型插入整数时会自动补全小数位数；
2、字符类型
1、char（定长）
1、宽度取值范围：1~255
2、不给宽度默认为宽度为1
2、varchar（变长）（输入要的值不能超过变长值，储存空间根据实际输入的数值来确定）
1、取值范围：1~65535
2、注意：
1、varchar没有默认宽度，必须给定一个宽度
2、char和varchar使用时都给宽度，但不能超过各自的范围
3、char 和 varchar 的特点
1、char浪费存储空间但是性能高
2、varchar节省存储空间，性能低（需要计算需要占用的存储空间）
4、字符类型的宽度和数值类型的宽度的区别
1、数值类型的宽度为显示宽度，只用于select查询时使用，和占用存储空间的大小无关，可用zerofill查看效果
2、字符类型的宽度超过则无法存储
3、枚举类型
1、定义：字段值只能在列举的范围内选择
2、enum 单选(最多有65535个不同的值)
字段名 enmu（值1，值2，...）
3、set 多选（最多有64个不同的值）
字段名 set（值1，值2，...）,插入值的时候每个值之间用逗号分隔开
4、日期时间类型
1、year: 年 YYYY
2、date：日期 YYYYMMDD
3、time：时间 HHMMSS
4、datetime：日期时间 YYYYMMDDHHMMSS
5、timestamp：时间日期 YYYYMMDDHHMMSS
6、说明：
1、datetime 不给值默认返回NULL
2、timestamp不给值默认返回系统当前时间
表字段的操作：
1、语法：alter table 表名 执行动作
1、添加字段（add）：
1、添加到末尾
alter table 表名 add 字段名 数据类型
2、添加到开始
alter table 表名 add 字段名 数据类型 first
3、添加到指定位置
alter table 表名 add 字段名 数据类型 after 已有字段名
2、删除字段（drop）：
alter table 表名 drop 字段名
3、修改数据类型（modify）：
alter table 表名 modify 字段名 新的数据类型
4、修改字段名
alter table 表名 change 旧名 新名 数据类型
5、修改表名
alter table 表名 rename 新表名
练习：
1、填空题：
1、MySQL中的数据类型有 数值类型、字符类型、枚举类型、日期时间类型
2、关系型数据库的核心内容是 关系 即 二维表
2、简答题：
1、简述客户端把数据存储到数据库服务器上的过程
答：
1、连接MySQL服务器数
2、选择库 use 库名
3、添加\修改表
4、断开与数据库的连接 exit | quit | \q
2、char和varchar的区别？各自的特点
答：
1、char（定长），所占用的内容空间已经由定长决定了，虽然多占用内存，但是效率高
2、varchar（不定长），所占用的内控空间由添加字段的数据本身决定，但是需要计算占用的空间，效率低，省内存
3、操作题：
1、创建一个库school
create database school;
2、在库中创建表students来存储学生信息，字段如下学生（id）要求显示宽度为3位，不够用0填充
姓名（name），年龄（age只能为正数）、成绩（score浮点）、性别（sex单选）、爱好（likes多选）、入学时间（年月日）
use school;
create table students(
id int(3) zerofill,
name char(20),
age tinyint unsigned,
score float(4,1),
sex enum('男','女'),
likes set('打篮球','学习','购物','打游戏','打仗'),
go_school_time date
)default charset = utf8;
3、查看students的表结构
desc students;
4、在students表中增加一个字段id，加在第一列
alter table students add kid int first;
5、在表中任意插入5条记录
insert into students values(1,1,'曹操',60,80,'男','打仗,打游戏',20000912);
insert into students values(2,2,'刘备',50,90,'男','打仗,打游戏',20000912);
insert into students values(3,3,'关羽',45,70,'男','打仗,打游戏',20000912);
insert into students values(4,4,'张飞',40,60,'男','打仗,打游戏',20000912);
insert into students values(5,5,'小乔',20,100,'女','学习,购物',20000912);
6、查看所有学生的姓名、成绩、和入学时间
select name,score,go_school_time from students;
名词介绍
1、DB（Database）
DB就是数据库，存储数据库的仓库
2、DBMS（Database Management System）
数据库管理系统
数据库管理的软件有MySQL、Oracle....
3、DBS（Database System）
数据库系统
DBS = DB（存储）+DBMS（数据库软件）+数据库应用（财务管理系统、人事管理系统）+用户
where 条件子句（配合查、改、删操作）
1、语法格式：
select * from 表名 where 条件；
运算符操作（配合查询、修改、删除操作）
1、数值比较&字符比较：
1、数值比较运算符：= != > >= < <=
2、字符比较运算符：= !=
3、语法格式：
查询：select * from 表名 where 字段名 运算符 数字/字符；
修改：update 表名 set 字段名=值，...where 字段名 运算符 数字/字符；
删除：delete from 表名 where 字段名 运算符 数字/字符；
2、逻辑比较：
1、运算符：
and(多个条件同时满足)
or（多个条件有一个条件满足就可以）
3、范围内比较：
1、运算符：between and 、 in 、not in
2、语法格式
字段名 between 值1 and 值2（包括值1和值2）
字段名 in（值1，值2，...）
字段名 not in (值1，值2，...)
4、匹配空、非空
1、空：is null
2、非空：is not null
3、注意：
1、null：空值，必须用is 或者is not 去匹配
2、‘’：空字符串，用 = 或者 != 去匹配
5、模糊比较
1、语法格式：字段名 like 表达式
2、表达式
1、_：匹配单个字符
2、%：匹配0到多个字符，不能是NULL
SQL查询：
1、总结（执行顺序）
完整的语句：select ...聚合函数 from ... where ... group by...having ... order by ...limit...；
上面语句的执行顺序：1、where 2、group by 3、select...聚合函数 from 4、having 5、order by 6、limit
2、order by
1、作用：对查询的结果进行排序
2、语法格式：order by 字段名 排序方式；
3、排序方式：
1、ASC（默认）：升序
2、DESC：降序
3、说明：这个排序方式不区分大小写(asc,desc)
3、limit （永远放在SQL语句的最后面）
1、作用：限制显示查询记录的条数
2、用法
1、limit n ：显示n条记录
2、limit m n ：从m+1条记录开始显示，n表示显示显示几条记录
示例：limit 1,3 ：显示2,3,4三条记录
3、练习
1、查找防御值倒数第二名至倒数第四名的蜀国英雄记录
select * from sanguo where country='蜀国' order by fangyu asc limit 1,3;
2、查找攻击值前三名且名字不为空值的蜀国英雄的姓名，攻击值和国家
select name,gongji,country from sanguo where country='蜀国' and name is not NULL order by gongji desc limit 3;
4、聚合函数：
1、分类
1、avg（字段名）：求字段的平均值
2、sum（字段名）：求字段的和
3、max（字段名）：求字段的最大值
4、min（字段名）：求字段的最小值
5、count（字段名）统计该字段记录的个数(空值NULL不会被统计，‘’可以被统计)
2、练习：
1、攻击力最强值
select max(gongji) from sanguo;(默认的将max（gongji）做为字段名，可以通过as 来修改)
2、统计一下表中id，name字段分别有多少条记录
select count(id) as,c_id count(name) as c_name from sanguo;
3、计算蜀国英雄的总攻击力
select sum(gongji) from sanguo where country='蜀国';
4、统计蜀国英雄中攻击力大于200的英雄数量
select count(*) from sanguo where country='蜀国' and gongji >200;
5、group by
1、作用：给查询的结果进行分组
2、练习
1、查询sanguo表中一共有几个国家
select country from sanguo group by country;
2、计算所有国家的平均攻击力
select country,avg(gongji)from sanguo group by country;
3、查找所有国家中 英雄数量最多的 前2名 的国家的名称和英雄数量
select country,count(*) from sanguo group by country order by count(*) desc limit 2;
3、注意：
1、group by之后的字段必须要为select之后的字段
2、如果select之后的字段和group by之后的字段不一致，则必须要对select之后的该字段做聚合处理
6、having
1、作用：对查询的结果进行进一步的筛选
2、练习
1、找出平均攻击力大于105的国家的前两名，显示国家名和平均攻击力
select country,avg(gongji) from sanguo group by country having avg(gongji)>105 order by avg(gongji) desc limit 2;
3、注意：
1、having语句通常与group by 语句联合使用，用来过滤由group by语句返回的记录集
2、having语句的存在弥补了where关键字不能与聚合函数联合使用的不足，having操作聚合函数生成的显示列
3、where只能操作表中实际存在的字段，having可以操作聚合函数生成的显示列
7、distinct
1、作用：不显示字段的重复值
2、练习：
1、sanguo表中一共有多少个国家
select distinct country from sanguo;
2、计算蜀国一共有多少个英雄
select count(distinct id) from sanguo where country='蜀国';
3、注意
1、distinct处理的是distinct和from之间的所有字段，所有字段值必须全部相同才能去重
2、distinct不能对任何字段做聚合处理
8、在查询表记录时做数学运算
1、运算符： + - * / %
2、练习：
1、查询时显示所有英雄的防御力+5
select id,name,fangyu+5 from sanguo;
约束
1、作用：为了保证数据的完整性、一致性、有效性
2、约束分类
1、默认约束（default）
1、作用：在插入记录时，如果不给字段赋值，则使用默认值
2、格式：字段名 数据类型 default 默认值
2、非空约束（not null）
1、作用：不允许将该字段设置为null
2、格式：字段名 数据类型 not null
索引
1、定义：对数据库中表的一列或者多列的值进行排序的一种结构（MySQL中用Btree方式（二叉树））
2、优点：加快数据的检索速度
3、缺点
1、当对表中的数据进行增加、删除或修改的时候，索引也要动态维护，降低了数据的维护速度
2、索引需要占用物理空间
4、索引分类
1、普通索引（index）
1、使用规则：
1、一个表中可以有多个index字段
2、字段的值可以有重复，且可以为null值
3、经常把做查询条件的字段设置为index字段
4、index字段的key标志是MUL
2、创建index
1、创建表时创建:index （字段名1），index（字段名2）
create table t2(
id int,
name char(15),
age tinyint unsigned,
index(id),inedx(name));
2、在已有表中创建index
1、语法：
create index 索引名 on 表名（字段名）
create index age on t2(age);
2、注意：
索引名一般和字段名一样
3、查看普通索引
1、通过desc 表名 -----→查看key标志
2、show index from 表名；
4、删除普通索引
drop index 索引名 on 表名；
2、唯一索引（unique key）
1、使用规则
1、一个表中可以有多个unique字段
2、unique字段的值不允许重复，但可以为空
3、unique的key标志是UNI
2、创建唯一索引
1、创建表的时候创建
方法1、unique (字段名1)，unique（字段名2）
方法2、字段名 数据类型 unique
2、在已有表中创建
create unique index 索引名 on 表名（字段名）；
示例：create unique index name on user(name);
3、删除唯一索引
drop index 索引名 on 表名；
注意：index unique在删除时只能一个一个删
3、主键索引（primary key）
1、使用规则
1、一个表中只能有一个主键字段
2、对应字段的值不能重复，且不能为空
3、主键字段的key标志为PRI
4、把表中能够唯一标识一个一条记录的字段设置为主键，通常把表中记录编号的字段设置为主键
2、创建主键（primary key）
1、在创建表时
1、字段名 数据类型 primary key ；
2、primary key （字段名）
3、primary key （字段名1，字段名2）：复合主键，当字段1,2的值都相同时才算相同，一般不用
2、在已有表中创建
1、alter table 表名 add primary key（字段名）
3、删除
1、alter table 表名 drop primary key
说明：删除主键后把不能重复的特性删掉了，但是不能为空的特性还在
4、自增长属性（auto_increment）
1、作用：通常和主键字段一起配合使用
2、创建
1、在创建表的时候创建
字段名 数据类型 primary key auto_increment
2、在已有表中创建
alter table 表名 modify 字段名 数据类型 primary key auto_increment;
3、说明：有自增长属性的字段在插入记录时可以用0来补位，值从1开始逐一递增，如果字段填入的值比表中最后一个的值大
那么他们之间的值将不存在。
5、删除主键及自增长属性
1、alter table 表名 modify 字段名 数据类型 ；（先删除自增长属性）
2、alter table 表名 drop primary key；
4、外键索引（foreign key）
1、定义：让当前表的字段值在另一张表的范围内选择
2、语法格式：
foreign key （参考字段名） reference 被参考表名（被参考字段名） on delete 级联动作 on update 级联动作
3、案例：
表1：缴费信息表（财务）
学号 姓名 班级 缴费金额
1 唐伯虎 1712 20000
2 秋香 1712 19000
表2：学生信息表（班主任）
学号 姓名 缴费金额
1 唐伯虎 20000
create table jftab(
id int primary key,
name char(20),
class char(7),
money int
)default charset=utf8;
create table bjtab(
-> stu_id int,
-> name varchar(15),
-> money int,
-> foreign key(stu_id) references jftab(id) on delete cascade on update cascade
-> )default charset=utf8;
4、删除外键
1、语法格式：
alter table 表名 drop foreign key 外键名（bjtab_ibfk_1）；
2、注意
查看外键的方式：
show create table 表名；（查看 CONSTRAINT `bjtab_ibfk_1`）
5、在已有表中添加外键
alter table 表名 add foreign key（参考字段名）references 被参考表名（被参考字段名）on delete 级联动作 on update 级联动作
说明：在已有表中添加外键时，会受到表中原有数据的限制
6、级联动作
1、cascade：数据级联更新
1、当主表删除记录时，如果从表有相关联记录则级联删除
2、当主表更新被参考字段值时，从表级联更新参考字段值
2、restrict（默认）
1、当主表删除记录时，如果从表中有相关联记录，则不允许主表删除
2、当主表更新被参考字段值时，如果自从表有参考字段，则不允许更新
3、set null
1、当主表删除记录时，从表中相关联记录外键字段值变为null
2、update同理
4、no action
同restrict一样，都是立即检查外键限制
7、使用规则
1、两张表被参考字段和参考字段的数据类型要一致
2、被参考字段必须是key的一种，通常是primary key
5、在哪些字段上面适合添加索引
1、在较为频繁的作为查询条件的字段应该创建索引
2、唯一性太差的字段不适合单独创建索引，比如性别
3、更新太频繁的字段不适合创建索引
4、不会出现在where子句中的字段不适合创建索引
6、注意事项
1、创建一个复合索引后，查询只用到了其中的一个字段则不会使用索引
创建复合索引：create index fangyu_country on sanguo(fangyu,country);
explain可以帮助我们在不真正执行某个SQL语句的时候，就可以分析sql语句
mysql> explain select * from sanguo where country="蜀国"\G
*************************** 1. row ***************************
id: 1 #查询序列
select_type: SIMPLE #查询类型
table: sanguo #查询表名
partitions: NULL
type: ALL #扫描方式，ALL表示全表扫描
possible_keys: NULL #可能使用到的索引
key: NULL #实际使用到的索引
key_len: NULL #使用索引的个数
ref: NULL
rows: 8 #SQL语句扫描的行数
filtered: 12.50
Extra: Using where
1 row in set, 1 warning (0.00 sec)
2、使用模糊查询时不会使用到索引
3、如果查询语句中使用了or只要其中一个条件没有索引，则查询时则不会使用索引
数据导入
1、作用：将文件系统的内容导入到数据库中
2、语法格式：
1、load data infile '文件名' into table 表名 fields terminated by '分隔符' lines terminated by '分隔符'；
说明：文件为绝对路径
3、xdl: x: 1000:1000:xdl,,,:/home/xdl:/bin/bash（使用cat /etc/passwd | grep "xdl";命令查看）
用户名：密码：UID：GID： 描述： 家目录： 登录权限
4、把/ect/passwd/ 导入到MySQL数据库中
5、操作步骤：
1、在数据库中创建对应的表：
create table userinfo(
-> username char(20),
-> password char(1),
-> uid int,
-> gid int,
-> comment varchar(50),
-> homedir varchar(50),
-> shell varchar(50)
-> );
2、查看数据库的默认搜索路径：
show variables like 'secure_file_priv';
3、将系统文件拷贝到数据库的默认搜索路径中
sudo cp /etc/passwd /var/lib/mysql-files
4、将数据导入表中
load data infile '/var/lib/mysql-files/passwd'
-> into table userinfo
-> fields terminated by ':'
-> lines terminated by '\n';
数据导出
1、作用：将数据库中表的记录保存到系统文件中
2、语法格式：
select ... from 表名 into outfile '文件名' fields terminated by '分隔符' lines teriminated by '分隔符'
3、练习：
1、把userinfo 表中的用户名，密码，和uid号三个字段导出到userinfo.txt中
select username,password,uid from userinfo
-> into outfile '/var/lib/mysql-files/userinfo.txt'
-> fields terminated by ' '
-> lines terminated by '\n';
2、将库名为mysql库中的user表中的User，Host两个字段的值导出到user2.txt
select User,Host from user
-> into outfile '/var/lib/mysql-files/user2.txt'
-> fields terminated by ' '
-> lines terminated by '\n';
4、注意：
1、导出的内容由SQL查询语句决定
2、执行导出命令时路径必须指定在对应的数据库目录下
3、show variables like 'secuer_file_priv';查询路径
表的复制
1、表的复制
1、语法格式
create table 表名 select 查询命令；
2、练习
1、复制userinfo表中的数据到userinfo2表中
create table userinfo2 select * from userinfo;
2、复制userinfo表的前10行记录到uesrinfo3中
create table userinfo3 select * from userinfo limit 10;
3、复制userinfo表的用户名、密码、uid三个字段的2～10条记录到userinfo4中
create table userinfo4 select username,password,uid from userinfo limit 1,9;
2、只复制表结构
1、语法格式
create table 表名 select...where false；
3、注意复制表的时候不会把原有表的 键 属性赋值过来
嵌套查询
1、定义：把内层的查询结果作为外层查询的条件
2、语法格式：
select 查询语句 where 条件 （select 查询语句）
3、练习：
1、把uid的值小于这个字段的平均值的用户和uid显示出来
方法1：select avg(uid) from userinfo;
select ussername,uid from userinfo where uid < 第一条查询的结果
方法2：
select username,uid from userinfo where uid < (select avg(uid) as vag_uid from userinfo);
2、查找userinfo表中用户名在mysql库下的user表中Host值为localhost并且User值为root的 用户名
select username from userinfo where username in (select User from mysql.user where Host='localhost' and User='root')
多表查询
1、两种方式：
1、select 字段名列表 from 表名列表；（笛卡尔积）
select * from tt2,tt3;
2、select 字段名列表 from 表名列表 where 条件
select * from tt2,tt3 where tt2.uid = tt3.uid;
2、练习
1、显示省和市的信息
select sheng.S_name,city.C_name from sheng,city where sheng.S_ID = city.CFather_ID;
2、显示省、市、县的信息
select sheng.S_name,city.C_name,xian.X_name from sheng,city,xian where sheng.S_ID=city.CFather_ID and city.C_ID=xian.XFather_ID;
连接查询
1、内连接
1、定义：从表中删除与其他被连接表中没有匹配到的行
2、语法格式
select 字段名列表 from 表1 inner join 表2 on 条件
3、练习
1、显示省、市信息
select sheng.S_name,city.C_name from sheng inner join city on sheng.S_ID = city.CFather_ID;
2、显示省、市、县的信息
select sheng.S_name,city.C_name,xian.X_name from sheng inner join city on sheng.S_ID = city.CFather_ID inner join xian on city.C_ID=xian.XFather_ID;
2、外连接
1、左连接
1、定义：以左表为主显示查询结果
2、语法：
select 字段名列表 from 表1 left join 表2 on 条件
3、练习：
1、显示省市信息
select sheng.S_name,city.C_name from sheng left join city on sheng.S_ID = city.CFather_ID;
2、右连接
1、定义：以右表为主显示查询结果
2、语法：
select 字段名列表 from 表1 right join 表2 on 条件
3、练习
1、显示省市信息
select sheng.S_name,city.C_name from sheng right join city on sheng.S_ID = city.CFather_ID;
2、显示省市县的信息，要求市全部显示
select sheng.S_name,city.C_name,xian.X_name from sheng right join city on sheng.S_ID = city.CFather_ID left join xian on city.C_ID=xian.XFather_ID;
数据备份（在linux终端下操作）
1、命令格式：
mysqldump -u用户名 -p 源库名 > 路径/xxx.sql：
2、源库名的表示方式
--all-database 备份所有库
库名 备份单个库
-B 库1 库2 ... 备份多个库
库名 表1 表2 备份指定库的指定表
3、练习
1、备份所有库为all_mysql.sql放到主目录下mydata目录中
mysqldump -u debian-sys-maint -p --all-databases > ~/mydata/all_mysql.sql
2、备份三个库，放到mydata目录中
mysqldump -u debian-sys-maint -p -B db1 db2 db3 > ~/mydata/db1_db2_db3.sql
3、备份省市县的三张表
mysqldump -u debian-sys-maint -p db3 sheng city xian > ~/mydata/sheng_city_xian.sql
数据恢复（在linux终端下操作）
1、命令格式
mysql -u用户名 -p 目标库名 < 路径/xxx.sql
2、从所有备份文件中恢复指定库（--one-database）
mysql -u用户名 -p --one-database 目标库 < 路径/xxx.sql
说明：目标库必须是所有备份中存在的库名
3、练习
1、先备份db3库，删除省市县三张表，
drop table sheng,city,xian;
2、在db3库中创建一张新表t88888
create table t88888(id int);
3、恢复db3库
4、注意
1、恢复时如果恢复到原有库会将表中数据覆盖，新增的表不会被删除
2、在恢复时如果恢复的库不存在，则先要创建空库
ER模型&ER图
1、定义：ER模型就是实体——关系模型，ER图就是实体-关系图
2、三个概念
1、实体
1、定义：现实世界中任何可以被认知、区分的实物
2、示例
1、学校：学生、教师、课程、班主任...
2、企业：职工、产品...
2、属性
1、定义：实体所具有的特性
2、示例
1、学生属性：学号、姓名、年龄、性别、专业...
2、产品属性：产品编号、产品名称、规格...
3、关系
1、定义：实体之间的关系
2、分类
1、一对一关系（1:1）：班级和班长
2、一对多关系（1：n）：公司和职工、班级和学生
3、多对多关系（m：n）：学生和课程、商店和顾客
4、ER图的绘制
1、矩形框代表实体、菱形框代表关系、椭圆形代表属性。
2、示例：
学生选课系统
workbench（图形化界面操作数据库）
事务&事务回滚
1、事务定义：一件事从开始到结束的整个过程
2、属性
1、原子性：
一个事务是不可分割的工作单位，事务中各个操作要么都做，要么都不做
2、一致性：
事务必须从一个一致性状态到另一个一致性状态
3、隔离性：
一个事务的执行不能被其他并发事务干扰
4、持久性：
一个事物一旦提交，它对数据的改变是永久性的
3、事务及事务回滚的应用：
1、mysql中默认sql语句会自动commit到数据库
show variables like ‘autocommit’；
2、事务应用
1、开启事务：
mysql> start transaction；#此时autocommit被禁用，SQL语句不会对数据库中数据做修改
2、终止事务；
1、mysql> commit；
2、mysql> rollback；
3、注意:
事务和事务回滚只针对对表记录的操作：增加、删除、修改，对创建库、创建表、查询表记录是无效的
python数据库编程
1、python数据库接口（Python DB-API）
1、定义：为开发人员提供的数据库应用编程接口
2、python支持的数据库服务软件
MySQL、Oracle、SQL_Server、Sybase、Mogodb
3、Python提供的操作MySQL的模块
模块名：pymysql
4、pymysql的使用流程
1、先建立数据库连接
2、创建游标对象
3、使用游标对象的方法和SQL语句操作mysql数据库
4、提交commit
5、关闭游标
6、关闭数据库连接
import pymysql #打开数据库连接 db = pymysql.connect('localhost','debian-sys-maint','Lo0r79JmxvMFNtA2',charset='utf8') #创建一个游标对象 cur = db.cursor() #创建一个库 cur.execute('create database if not exists python;') #切换库 cur.execute('use python;') #创建一个表 cur.execute('create table if not exists t1(id int primary key,name varchar(20),score tinyint unsigned)default charset=utf8;') #插入5条记录 cur.execute("insert into t1 values(1,'貂蝉',88),(2,'赵子龙',100),(3,'诸葛亮',80),(4,'张飞',60),(5,'司马懿',99);") #提交 db.commit() #关闭游标 cur.close() #关闭数据库连接 db.close()
5、需要安装pymysql模块
1、安装pip3：sudo apt install python3-pip
2、安装pymysql：pip3 install pymysql
6、建立数据库连接：
1、语法格式：
对象名=pymysql.connect('主机名','用户名','密码','库名',port=3306,charset='utf8')
2、connect对象（db）的方法
1、cursor（） 创建一个游标对象：db.cursor（）
2、commit（） 提交到数据库 db.commit()
3、rollback（） 回滚 db.rollback()
4、close（） 关闭与数据库的连接 db.close()
3、cursor游标对象（cur）的方法
1、execute（） 执行sql命令
2、fetchone（） 取得结果集的第一条记录
import pymysql db = pymysql.connect('localhost','debian-sys-maint','Lo0r79JmxvMFNtA2','python',charset='utf8') cur = db.cursor() sql_select = 'select * from t1;' cur.execute(sql_select) data = cur.fetchone() print('fetchone的结果为：',data) #fetchone的结果为： (1, '貂蝉', 88) db.commit() cur.close() db.close()
3、fetchmany（数字） 取得结果集的第几条记录
import pymysql db = pymysql.connect('localhost','debian-sys-maint','Lo0r79JmxvMFNtA2','python',charset='utf8') cur = db.cursor() sql_select = 'select * from t1;' cur.execute(sql_select) data = cur.fetchmany(3) all_data = cur.fetchall() print('fetchmany的结果为：',data)#fetchmany的结果为： ((1, '貂蝉', 88), (2, '赵子龙', 100), (3, '诸葛亮', 80)) print('fetchall的结果为：',all_data) db.commit() cur.close() db.close()
4、fetchall（） 取得结果集的所有行
5、close（） 关闭游标对象
属性：rowcount ： 返回命令执行影响的条数
import pymysql db = pymysql.connect('localhost','debian-sys-maint','Lo0r79JmxvMFNtA2','python',charset='utf8') cur = db.cursor() sql_select = 'select * from t1;' print('命令影响的条数：',cur.rowcount) cur.execute(sql_select) all_data = cur.fetchall() print('fetchall的结果为：',all_data)#fetchall的结果为： ((1, '貂蝉', 88), (2, '赵子龙', 100), (3, '诸葛亮', 80), (4, '张飞', 60), (5, '司马懿', 99)) db.commit() cur.close() db.close()
#rollback.py import pymysql db = pymysql.connect('localhost','debian-sys-maint','Lo0r79JmxvMFNtA2','db3',charset='utf8') cur = db.cursor() try: cur.execute("update CCB set money = 95000 where name='转钱';") cur.execute("update ICBC set money = 7000 where name= '借钱';") db.commit() print('ok') except Exception as e: db.rollback() print("出现错误已经回滚") cur.close() db.close()
from pymysql import * class MysqlPython: def __init__(self,host,user,password,db,port,charset='utf8'): self.host = host self.user = user self.pwd = password self.db = db self.port = port self.charset = charset def open(self): '''创建与数据库的连接''' self.conn = connect(self.host,self.user,self.pwd,self.db,self.port,charset=self.charset) #创建游标 self.cur = self.conn.cursor() def close(self): '''关闭游标、关闭与数据库的连接''' self.cur.close() self.conn.close() def myexecute(self,sql): '''执行SQL命令''' self.open() try: self.cur.execute(sql) self.conn.commit() print('操作成功') except Exception as e: self.conn.rollback() print('出现错误，已经回滚:',e) def myFetchone(self,sql): '''得到一条记录''' try: self.open() self.cur.execute(sql) data = self.cur.fetchone() return data except Exception as e: print('出现错误:',e) def myFetchmany(self,sql,n): '''得到多条记录''' try: self.open() self.cur.execute(sql) data = self.cur.fetchmany(n) return data except Exception as e: print('出现错误:',e) def myFetchall(self,sql): '''得到全部记录''' try: self.open() self.cur.execute(sql) data = self.cur.fetchall() return data except Exception as e: print('出现错误:',e)
#test.py from MysqlPython import MysqlPython # name = input("请输入要修改成绩的学生姓名：") # score = int(input("请输入新的成绩：")) # sql = "update t1 set score=%s where name='%s';"%(score,name) # mp = MysqlPython('localhost','debian-sys-maint','Lo0r79JmxvMFNtA2','python',3306) # mp.myexecute(sql) # mp.close() sql = "select * from t1" mp = MysqlPython('localhost','debian-sys-maint','Lo0r79JmxvMFNtA2','python',3306) data = mp.myFetchone(sql) print(data) mp.close() data = mp.myFetchmany(sql,3) print(data) data = mp.myFetchall(sql) print(data)
select user_id,count(*) from comment group by user_id order by count(*) desc limit 10;
思路：首先找到所有的用户，按用户分组，按评论的文章计数然后降序，取前10条记录
TCL(事务控制语言)
1.事务Transaction:要么一起成功,要么一起失败.
2.事务的开始
一个事务的结束,就是下一个事务的开始
3.事务的结束
DDL(数据定义语言)语句能够自动提交事务:create, drop, alter
DML(数据操纵语言)语句不会自动提交事务:insert, delete,update,select
4.commit:提交
5.rollback:回滚
创建用户并赋予某个库的权限
1、创建用户
create user 'username'@'host' identified by 'passwd'
说明：host表示用户可以在那个主机上面进行登录，%表示任意主机；
2、创建数据库
create database name chartset=utf8
3、赋予权限
grant all on databasename.tablename to 'username'@'%'
说明：all 表示所有权限，可以是 select insert update alert delete; databasename.tablename表示哪个库和哪个表*表示所有
