python 数据库 Mysql (3)

数据类型（列类型）
所谓的数据类型：对数据进行统一的分类，从系统的角度出发为了能够使用统一的方法进行
管理，更好的利用有限的空间
SQL中将数据类型分成三大类：数值类型，字符串类型，时间日期类型，每类会分成很多小类
数值型
数值型数据：都是数值
系统将数值型分成整数和小数型
整数型
存放整型数据：在SQL中因为更多要考虑如何节省磁盘空间，所以将整型又细分成了5类，
tinyint:迷你整型，使用一个字节存储，表示的状态最多为256种（常用）
smallint:小整型：使用2个字节存储，表示的状态最多为65536种
Mediumint:中整型：使用3个字节存储
int：标准整型，使用4个字节存储（常用）
bigint:大整型，使用8个字节存储

#创建整型表
CREATE TABLE my_int(
int_1 TINYINT,
int_2 SMALLINT,
int_3 INT,
int_4 BIGINT
);
#插入数据
INSERT INTO my_int VALUES(100,100,100,100);#有效数据
INSERT INTO my_int VALUES('a','b',100,100);#五效数据：类型限定
INSERT INTO my_int VALUES(255,10000,100000,1000000);#错误：超出范围

SQL中的数据类型全部都是无符号，分正负
有时候需要使用无符号数据：需要给数据类型限定：int unsigned 无符号：从0开始
#给表增加一个无符号类型
ALTER TABLE my_int ADD int_5 TINYINT UNSIGNED;#无符号类型
#插入数据
INSERT INTO my_int VALUES(127,1000,10000,1000000,255);#有效数据

查看表结构的时候发现每个字段的数据类型之后都会自带一个括号，里面有指定的数字
显示宽度，没有特别的含义，只是默认的告诉用户可以显示的形式而已，实际上用户是
可以控制的，这种控制不会改变数据本身的大小
ALTER TABLE my_int ADD int_6 TINYINT(1) UNSIGNED;#指定显示宽度为1
INSERT INTO my_int VALUES(127,0,0,0,255,255);# 指定宽度1的时候，不会影响它的存储
显示宽度的意义，在于当数据不够显示宽度的时候，会自动让数据变成对应的显示宽度：
通常需要搭配一个前导0来增加宽度，不会改变值大小，zerofill(0填充)，0填充会导致数值
自动变成无符号
ALTER TABLE my_int ADD int_7 TINYINT ZEROFILL;#显示宽度为2，0填充
0填充：显示宽度的效果
INSERT INTO my_int VALUES(1,1,1,1,1,1,1);
INSERT INTO my_int VALUES(100,100,100,100,100,100,100);
0填充的意义（显示宽度），保证数据格式

小数型
小数型：带有小数点或者范围超出整型的数值类型
SQL中：将小数型细分两种，浮点型和定点型
浮点型：小数点浮动，精度有限，而且会丢失精度
定点型：小数点固定，精度固定，不会丢失精度

浮点型
浮点型数据是一种精度型数据，因为超出指定范围之后会丢失精度（自动四舍五入）
浮点型：理论分为两种精度
  float:单精度,占有4个字节存储数据，精度范围大概为7位左右
  double:双精度，占有8个字节存储数据，精度范围大概为15位左右

创建浮点数表：浮点的使用方式，直接float表示没有小数部分，float（M.D），M代表总长度
，D代表小数部分长度，整数部分长度为M-D

#浮点数表
CREATE TABLE my_float(
fi FLOAT,
f2 FLOAT(10,2),#10位在精度范围之外
f3 FLOAT(6,2)  #6位在精度范围之内
)CHARSET utf8;

插入数据：可以是直接小数，也可以是科学计数法
INSERT INTO my_float VALUES(1000.10,1000.10,1000.10);#符合条件
INSERT INTO my_float VALUES(1234567890,12345678.90,1234.56);#符合条件
INSERT INTO my_float VALUES(999999999,99999999.99,99999.99);#最大值
INSERT INTO my_float VALUES(3e38,3.01e7,1234.56);
浮点想数据的插入：整数部分是不能超出长度的，但是小数不分可以超出长度（系统
会自动四舍五入）

#超出长度插入数据
INSERT INTO my_float VALUES(123456,1234.123456789,123.9876543);#小数部分超出
INSERT INTO my_float VALUES(123445,1234.12,12345.56);#整数部分超出

结果：浮点数一定会进行四舍五入（超出精度范围），浮点数如果是因为系统进位导致整数部分
超出指定的长度，那么系统也允许成立，
如：INSERT INTO my_float VALUES(999999999,99999999.99,99999.99);#最大值

定点型
定点型：绝对的保证整数部分不会四舍五入（不会丢失精度），小数部分有可能（理论小数
部分也不会丢失精度）

创建定点数表，以浮点数作为对比
#创建定点数表
CREATE TABLE my_decimal(
f1 FLOAT(10,2),
di DECIMAL(10,2)
)CHARSET utf8;

插入数据：定点数的整数部分一定不能超出长度（进位也不可以），小数部分的长度可以
随意超出（系统会自动四舍五入）
#插入数据
INSERT INTO my_decimal VALUES(12345678.90,12345678.90);#有效数据
INSERT INTO my_decimal VALUES(1234.123456,1234.123456);#小数部分超出

浮点数如果进位导致长度溢出没有问题，但是定点数不行
#插入数据
INSERT INTO my_decimal VALUES(99999999.99,99999999.99);#有效数据
INSERT INTO my_decimal VALUES(99999999.99,99999999.999);#进位超出范围
时间日期类型
datetime 时间日期，格式是YYYY-mm-dd HH:ii:ss,表示的范围从1000到9999年，
0值 0000-00-00 00：00：00
date 日期，就是datetime中的date部分
time 时间（段），指定的某个区间之间，时间到到+时间
timestamp 时间戳，并不是时间戳，只是从1970年开始的YYYY-mm-dd HH:ii:ss
格式与datetime完全一致
year 年份，两种形式，year(2)和year(4):1901-2155

创建时间日期表
#创建时间日期表
CREATE TABLE my_date(
d1 DATETIME,
d2 DATE,
d3 TIME,
d4 TIMESTAMP,
d5 YEAR(2)
)CHARSET utf8;

插入数据：时间time可以是负数，而且可以是很大的负数，year可以使用2位数插入也
可以使用4位数
#插入数据
INSERT INTO my_date VALUES('2017-9-28 11:50:36','2017-9-28','11:51:36','2017-9-28 11:53:09',2015);

#时间使用负数
INSERT INTO my_date VALUES('2017-9-28 11:50:36','2017-9-28','-11:51:36','2017-9-28 11:53:09',2015);
INSERT INTO my_date VALUES('2017-9-28 11:50:36','2017-9-28','-211:51:36','2017-9-28 11:53:09',2015);
INSERT INTO my_date VALUES('2017-9-28 11:50:36','2017-9-28','-2 11:51:36','2017-9-28 11:53:09',2015);#-2过去两天：48小时

#year可以使用2位或者4位
INSERT INTO my_date VALUES('2017-9-28 11:50:36','2017-9-28','11:51:36','2017-9-28 11:53:09',69);
INSERT INTO my_date VALUES('2017-9-28 11:50:36','2017-9-28','11:51:36','2017-9-28 11:53:09',70);

tomestamp字段：只是当前所在的记录被更新，该字段一定会自动更新成当前时间
#timestamp：修改记录
UPDATE my_date SET d1='2017-9-28 11:45:46' WHERE d5=69;

网站是以PHP位实现的主要操作对象，PHP中有非常强大的时间日期处理函数，达特，只需要
一个时间戳就可以转换任意类型的时间，以PHP位主的时候，都是在数据库使用时间戳
（整型）来存储时间

字符串类型
在SQL中，将字符串类型分成6类：char，varchar,blob,enum和set

定长字符串
定长字符串：char磁盘（二维表）在定义结构的时候，就已经确定了最终数据的存储长度
char（L）：代表length，可以存储的长度，单位位字符，最大长度值可以为255
char（4）：在utf8环境下，需要4*3=12个字节

变长字符串
变长字符串：在分配空间的时候，按照最大的空间分配，但是实际上最终用了多少，
是根据具体的数据确定
varchar(L):表示字符串长度 理论长度是65536个字符，但是会多出1到2个字节来确定存储
的实际长度,但是实际上如果长度超过255，既不用定长也不用变长，使用文本字符串text
varchar（10）：的确存了10个汉字，utf8环境10*3+1=31
               存储了3个汉字，3*3+1=10（bytes)
定长与变长的存储实际空间（utf8）
实际存储数据   char(4)    varchar(4)   char占用字节   varchar占用字节
abcd           abcd       abcd         4*3=12         4*3+1=13
a              a          a            4*3=12         1*3+1=4
abcd           ×         ×           数据超过长度   数据超过长度

如何选择定长或者是变长字符串呢？
定长的磁盘空间比较浪费，但是效率高，如果数据基本上长度都一样，就是使用定长
如：身份证，电话号码，手机号码
变长的磁盘空间比较节省，但是效率低，如果数据不能确定长度（不同数据有变化）
如：姓名，地址等

文本字符串
如果数据量非常大，通常说超过255个字符就会使用文本字符串
文本字符串根据存储的数据的格式分类：text和blob
  text：存储文字（二进制数据实际上都是存储路径）
  blob：存储二进制数据（通常不用）

枚举字符串
枚举：enum，事先将所有可能的结果都设计好，实际上存储的数据必须是规定好的数据
中的一个
枚举的使用方式
  定义：enum（可能出现的元素列表）； 如：enum('男','女','不男不女','保密');
  使用;存储数据，只能存储上面固定好的数据
#创建枚举表
CREATE TABLE my_enum(
gender ENUM('男','女','保密')
)CHARSET utf8;

加入数据：作用之一，规定数据格式，数据只能是规定的数据中的其中一个
#插入数据
INSERT INTO my_enum VALUES('男'),('保密');#有效数据
INSERT INTO my_enum VALUES('male');       #错误，没有该元素

作用之二：节省存储空间（枚举通常有一个别名：单选框）枚举实际存储是数值二不是
字符串本身
在MYSQL中，系统也是自动转换数据格式的，而且基本与PHP一样（尤其是字符串转数字）

证明字段存储的数据是数值，将数据取出来+0就可以判断出原来的数据存的到底是字符串
还是数值，如果是字符串最终结果永远为0，否则就是其他值。

#将字段结果取出来+0运算
SELECT gender+0,gender FROM my_enum;
找出了枚举元素的实际规律，按照元素出现的顺序，从1开始编号

枚举的原理：枚举在进行数据规范的时候（定义的时候），系统会自动建立一个数字与枚举元素
的对应关系（关系放到日志中），然后在进行数据插入的时候，系统自动将字符转换成对应的
数字存，然后在进行数据提取的时候，系统自动将数值转换成对应的字符串显示

因为枚举实际存储的是数值，所以可以直接插入数值
#数值插入枚举元素
INSERT INTO my_enum VALUES(1),(2);

集合字符串
集合跟枚举很类似:实际存储的是数值，而不是字符串（集合是多选）
集合使用方式;
  定义：Set（元素列表）
  使用：可以使用元素列表中的元素（多个），使用逗号分隔
#创建集合表
CREATE TABLE my_set(
hoddy SET('篮球','足球','乒乓球','羽毛球','排球','台球','网球','棒球')
)CHARSET utf8;
插入数据：可以使用多个元素字符组合，也可以直接插入数值
#插入数据
INSERT INTO my_set VALUES('足球,台球,网球');#'足球','台球','网球'
INSERT INTO my_set VALUES(3);#'篮球,足球'
INSERT INTO my_set VALUES(255);# 显示全部
#查看数据
SELECT hobby +0,hobby FROM my_set;

集合中没每一个元素都对应二进制
集合中元素的顺序没有关系，最终系统都会去匹配顺序
#颠倒元素出现的顺序
INSERT INTO my_set VALUES('网球,台球,足球');#'足球','台球','网球'

集合的强大在于能够规范数据和节省空键，PHP也可以规范数据，但是对于PHP来说效率优先
而且数据的维护可以通过数字进行，增加PHP的维护成本，PHP根本没有办法判断数据在数据

库的形式

EXISTS子查询

如何用SQL语句检测temp是否已经创建
DROP TABLE IF EXISTS temp; #检测是否有存在，有就删除
CREATE TABLE temp(         #创建temp表
... ...#省略建表语句
）;

EXISTS子查询的语法
SELECT 字段 FROM 表名 WHERE EXISTS(子查询)；
子查询有返回结果：EXISTS子查询结果为TRUE
子查询无返回结果：EXISTS子查询结果为FALSE,外层查询不执行
不相关子查询：
SELECT 字段 FROM 表名 WHERE EXISTS(子查询)；
相关子查询：
SELECT 字段 FROM 表名 WHERE EXISTS(子查询 WHERE(查询条件：里面和外面的关系))；

嵌套查询：
SELECT i.字段名, i.字段名,...FROM 表名1 i
WHERE i.字段名 IN(SELECT 字段名 FROM 表名1
WHERE 字段名=
(SELECT 字段名 FROM 表名2 WHERE 表名2.字段名='条件'));

查平均数，分组，倒序排序
SELECT 字段名(分组),AVG(字段名) FROM 表名
GROUP BY 字段名(分组)
ORDER BY AVG(字段名) DESC;

分组查询每组的个数
SELECT 字段名(分组),字段名,COUNT(字段名) FROM 表名
GROUP 字段名,字段名(分组);

按条件查平均数，分组
WHERE子句
  用来筛选FROM子句中指定的操作所产生的行
GROUP BY子句
  用来分组WHERE子句的输出
HAVING子句
  用来分组的结果中筛选行

用WHERE 要放在FROM后面
SELECT 字段名(分组),AVG(字段名) FROM 表名
WHERE AVG(字段名) 比较运算符 条件
GROUP BY 字段名(分组);

用HAVING 可以放在最后面
SELECT 字段名(分组),AVG(字段名) FROM 表名
GROUP BY 字段名(分组)
HAVING AVG(字段名) 比较运算符 条件;

常用的多表连接查询
内连接(INNER JOIN)  得到表里共有的数据
外连接
  左外连接(LEFT JOIN)
  右外连接(RIGHT JOIN)

如何从两张表里取得数据
SELECT 表名1.字段名,字段名,... FROM 表名1
INNER JOIN 表名2 ON 表名1.字段名=表名2.字段名；

SELECT 表名1.字段名,字段名,... FROM 表名1,表名2
WHERE 表名1.字段名=表名2.字段名；
重点：这两张表要互相有关联，WHERE后面的条件必须相等 

如何从三张表里取得数据
SELECT 表名1.字段名,字段名,... FROM 表名1
INNER JOIN 表名2 ON 表名1.字段名=表名2.字段名
INNER JOIN 表名3 ON 表名2.字段名=表名3.字段名;

SELECT 表名1.字段名,字段名,... FROM 表名1,表名2,表名3
WHERE 表名2 ON 表名1.字段名=表名2.字段名 
AND 表名3 ON 表名2.字段名=表名3.字段名;
重点：这三张表要互相有关联，WHERE后面的条件必须相等

左外连接：
它的特点是有主表和从表，主表的内容会全部显示，不足的以NULL填充
SELECT 字段名,字段名,... FROM 表名1
LEFT JOIN 表名2 ON 表名1.字段名=表名2.字段名；
在这里以表名1为主表，表名2为从表

右外连接：
SELECT 字段名,字段名,... FROM 表名1
RIGHT JOIN 表名2 ON 表名1.字段名=表名2.字段名；
在这里以表名2为主表，表名1为从表

关系数据库的三大范式：
第一范式:确保每列的原子性(强调的是列的原子性，即列不能够再分成其他几列).
    如果每列(或者每个属性)都是不可再分的最小数据单元(也称为最小的原子单元),则满足第一范式.
    例如:顾客表(姓名、编号、地址、……)其中"地址"列还可以细分为国家、省、市、区等。

第二范式:在第一范式的基础上更进一层,目标是确保表中的每列都和主键相关(一是表必须有一个主键；
   二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的部分)
    如果一个关系满足第一范式,并且除了主键以外的其它列,都依赖于该主键,则满足第二范式.
    例如:订单表(订单编号、产品编号、定购日期、价格、……)，"订单编号"为主键，"产品编号"
    和主键列没有直接的关系，即"产品编号"列不依赖于主键列，应删除该列。

第三范式:在第二范式的基础上更进一层,目标是确保每列都和主键列直接相关,而不是间接相关
    (另外非主键列必须直接依赖于主键，不能存在传递依赖).
    如果一个关系满足第二范式,并且除了主键以外的其它列都不依赖于主键列,则满足第三范式.
    为了理解第三范式，需要根据Armstrong公里之一定义传递依赖。假设A、B和C是关系R的三个属性，
    如果A-〉B且B-〉C，则从这些函数依赖中，可以得出A-〉C，如上所述，依赖A-〉C是传递依赖。
例如:订单表(订单编号，定购日期，顾客编号，顾客姓名，……)，初看该表没有问题，满足第二范式，
   每列都和主键列"订单编号"相关，再细看你会发现"顾客姓名"和"顾客编号"相关，"顾客编号"和"订
   单编号"又相关，最后经过传递依赖，"顾客姓名"也和"订单编号"相关。为了满足第三范式，应去
   掉"顾客姓名"列，放入客户表中。

事务：什么是事务
  事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作
  多个操作作为一个整体向系统提交，要么都执行，要么都不执行
  事务是一个可分割的工作逻辑单元
事务必须具备以下四个属性，简称ACID属性
  原子性(Atomicity)
     事务是一个完整的操作，事务的各步操作不课分的（原子的），要么都执行，
    要么都不执行
  一致性(Consistency)
     当事务完成时，数据必须处于一致状态
  隔离性(Lsolation)
     并发事务之间彼此隔离，独立，它不应以任何方式以来于或影响其他事务
  持久性(Durability)
     事务完成后，它对数据库的修改被永久保持

自动关闭和开启事务
示例
CREATE DATABASE mybank CHARSET UTF8;
USE mybank;
CREATE TABLE bank(
customerName CHAR(10), #用户名
currentMoney DECIMAL(10,2) #当前余额
);
# 插入数据
INSERT INTO bank(customerName,currentMoney) VALUES('张三','1000');
INSERT INTO bank(customerName,currentMoney) VALUES('李四','1');

1：
BEGIN; #开启事务（指定事务从此处开启，后续的SQL语句是一个整体）
# 转账：张三的账户减少500元，李四的账户增加500元
UPDATE bank SET currentMoney=currentMoney-500
WHERE customerName='张三';

UPDATE bank SET currentMoney=currentMoney+500
WHERE customerName='李四';
COMMIT; #提交事务，事务结果

2：
SET autocommit=0; #关闭自动提交，以下视为一个事务
# 转账：张三的账户减少500元，李四的账户增加500元
UPDATE bank SET currentMoney=currentMoney-500
WHERE customerName='张三';

UPDATE bank SET currentMoney=currentMoney+500
WHERE customerName='李四';
COMMIT; #提交事务
UPDATE bank SET currentMoney=currentMoney-1000
WHERE customerName='张三';
ROLLBACK; #回滚事务
SET autocommit=1; #开启自动提交，恢复默认状态

视图：
视图是一张虚拟表
  表示一张表的部分数据或多张表的综合数据
  其结构和数据是建立在对表的查询基础上
视图中不存放数据
  数据存放在视图所引用的原始表中
一个原始表，根据不同用户的不同需求，可以创建不同的视图

语法：使用SQL语句创建视图
  CREATE VIEW view_name AS SELECT（查询语句）；
      使用SQL语句删除视图
  OROP VIEW [IF EXISTS] view_name;
      使用SQL语句查看视图
  SELECT 字段名1,字段名1,... FROM view_name;