python 数据库 Mysql (2)

删除数据库
Drop table 表名1 ，表名2；--可以一次删除多张表
如：DROP TABLE class;
当删除数据表的指令执行之后发生了什么
  1 在表空间中，没有了指定的表（数据也没有了）
  2 在数据库对应的文件夹下，表对应的文件（与存储引擎有关）也会被删除
注意：删除有危险，操作需谨慎（不可逆）

数据操作
新增数据
有两种方案
   方案1：给全表字段插入数据，不需要指定字段列表：要求苏剧的值出现的顺序与表中
       设计的字段出现的顺序一致，凡是非数值数据，都需要使用引号（建议是单引号）
       包裹，insert into 表名 values（值列表）[,(值列表)]; --可以一次性插入多条记录
   如：INSERT INTO my_strdent VALUES(1,'itcastoooa','jim','male'),
       (2,'itcast0002','han','female');
   方案2：给部分字段插入数据，需要选定字段列表，字段列表出现的顺序与字段的顺序无关,
        但是值列表的顺序必须与选定的字段顺序一致，
        insert into 表名 （字段列表） values(值列表）[(值列表)]；
        如：INSERT INTO my_strdent (number,sex,nname,id) VALUES
            ('itcast0003','male','tom',3),
            ('itcast0004','female','lily',4);

查看数据
  Select*/字段列表 from 表名 [where条件]；
  查看所有数据：
  如：SELECT * FROM my_strdent; 
  查看指定字段，指定条件数据
  如： SELECT id,number,sex,nname FROM my_strdent WHERE id=1;
 
更新数据
   Update 表名 set 字段 = 值 [where条件]； --建议都有where，要不是更新全部
   如：UPDATE my_strdent SET sex = 'female' WHERE nname = 'jim';
   更新不一定会成功，如没有真正要更新的数据，
   判断是否更新要看有没有行受影响：（共 ？ 行受到影响）

删除数据
   删除是不可逆的，谨慎删除
   Delete from 表名 [where条件]；
   如：DELETE FROM my_strdent WHERE sex = 'male';

中文数据问题
   中文数据问题本质是字符集问题
   计算机只识别二进制，人类更多是识别符号，需要有个二进制与字符的对应关系（字符集）
   如：INSERT INTO my_strdent VALUES(5,'itcast0005','张三','男');
   如果输入以上代码显示错误，可能是字符集的设置有问题。
   报错：服务器没有识别对应的四个字节，服务器认为数据是UTF8，一个汉字有3个字节，
   读取3个字节转换成汉字（失败），剩余的再补3个字节（不够），最终失败。
   所有的数据库服务器认为（表现）的一些特性都是通过服务器端的变量保存，系统先读取自
   己的变量，看看应该怎么表现

   查看服务器到底识别哪些字符集

   Show character_set;
   如：SHOW CHARACTER SET;  --查看所有能识别的字符集
   
   Show variables like 'charecter-set%';
   如：SHOW VARIABLES LIKE 'character_set%';--查看服务器默认的对外处理的字符集

   如果错误，问题根源：客户端只能是GBK，而服务器认为是UTF8，矛盾产生
   解决方案，改变服务器，默认的接收字符集为GBK，
   Set character_set_client = gbk;
   SET character_set_client = gbk;--修改服务器认为的客户端数据的字符集为GBK：

   查看数据效果，依然是乱码
   原因：数据来源是服务器，解析数据是客户端（客户端只识别GBK，只会两个字节一个汉字），
   但是事实服务器给的数据却是UTF8，三个字节一个汉字，乱码
    解决方案：
   Set character_set_results = GBK;
   如：SET character_set_results = gbk;--修改服务器给客户端的数据字符集为GBK
   Set 变量 = 值；修改只是会话级别（当前客户端，当次连接有效，关闭失效）

   设置服务器对客户端的字符集的认识，可以使用快捷方式：set names 字符集
   Set names gbk; ==> character_set_client,character_set_results,character_set_connection
   如：SET NAMES gbk;  --快捷设置字符集
   connection连接层，是字符集转变的中间者，如果统一了效果更高，不统一也吗问题

   校对集问题
   校对集：数据比较的方式
   校对集有三种格式
      -bin:binary;二进制比较，取出二进制位，一位一位的比较，区分大小写
      -cs:case sensitive;大小写敏感，区分大小写
      -ci:case insensitive;大小写不敏感，不区分大小写

   查看数据库所支持的校对集：show collation;
   如：SHOW COLLATION; --查看所有的校对集
 
   校对集应用：只有当数据产生比较的时候校对集才会生效
   对比：使用UTF8 的_bin 和_ci 来验证不同的校对集的效果
       1 创建不同校对集对应的表
          CREATE TABLE my_collate_bin(
                uname CHAR(1)
           )CHARSET utf8 COLLATE utf8_bin;

          CREATE TABLE my_collate_ci(
                uname CHAR(1)
           )CHARSET utf8 COLLATE utf8_general_ci;
       2 插入数据
          INSERT INTO my_collate_bin VALUES('a'),('A'),('b'),('B');

          INSERT INTO my_collate_ci VALUES('a'),('A'),('b'),('B');
       3 比较：根据某个字段进行排序;order by 字段名 [asc|desc];asc升序，desc降序，默认是升序
          排序对比
          SELECT * FROM my_collate_bin ORDER BY uname;


          SELECT * FROM my_collate_ci ORDER BY uname;
   校对集：必须在没有数据之前申明好，如果有了数据那么再进行校对集修改，那么修改无效
   如：ALTER TABLE my_collate_ci COLLATE = utf8_bin;--有数据后修改校对集（无效）


   Web乱码问题
   动态网站有三部分构成：浏览器，apacbe服务器（PHP），数据库服务器，三个部分都有自己的
   字符集（中文），数据需要在三个部分之间来回传递，很容易产生乱码。
   如何解决乱码问题：统一编码（三码合一）

   但是事实上不可能：浏览器是用户管理（根本不可能控制）

  但是必须要解决这些问题：主要靠PHP来做

 

   回顾
   
   数据库基本知识：关系型数据库（磁盘）和非关系型数据库（内存）
   关系型数据库：建立在关系型上的数据库
       数据结构：二维表（比较浪费空间）
       操作数据的指令集合：SQL(DDL,DML[DQL],DCL)
       完整性约束：表内和表之间（实体）
   Mysql关系型数据库，c/s结构软件认证，发送SQL指令，服务器处理指令返回结果，客户端
   接收结果解析结果
   Mysql服务的对象：DBMS_>Database_>Table_>fields
   SQL基本操作：库操作，表操作（字段）和数据操作
   字符集问题：中文数据问题  
       改变服务器接收数据的字符集：character_set_client
       改变服务器返回数据的字符集：character_set_results
       快捷方式：set names 字符集（三件事情）
   web乱码问题：浏览器解析，PHP处理（本地文件），数据库处理
   校对集问题：比较规则：_bin _cs _ci

   算法：快速排序（递归）

常用函数 聚合函数
 函数名              作用
 AVG()              返回某字段的平均值
 COUNT()         返回某字段的行数
 MAX()              返回某字段的最大值
 MIN()              返回某字段的最小值
 SUM()              返回字段的和
 
用法：SELECT AVG(字段名) FROM 表名；          
      SELECT COUNT(字段名) FROM 表名；
      SELECT MAX(字段名) FROM 表名； 
      SELECT MIN(字段名) FROM 表名；
      SELECT SUM(字段名) FROM 表名；
这些函数可以计算一些数值，用起来会非常方便

字符串函数
 函数名                      作用                         举例
 CONCAT                  字符串连接               CELECT CONCAT('my','s',ql');
 (str1,str2...)                                              返回：mysql
 INSERT                     字符串替换               SELECT INSERT('这是SQL Server数据库',3,10,'MySQL');
 (str,pos,len,newstr)                                   返回：这是MySQL数据库
 LOWER                    将字符串转为小写       SELECT LOWER(MySQL);
 (str)                                                          返回：mysql
 UPPER                     将字符串转为大写       SELECT UPPER(MySQL);
 (str)                                                          返回：MYSQL
 SUBSTRING              字符串截取                SELECT SUBSTRING('JavaMySQLOracle',5,5);
 (str,num,len)                                             返回：MySQL      

时间日期函数
 函数名                     作用                                举例
 CURDATE()              获取当前日期       SELECT CURDATE();   
                                           返回：2018-01-30
 CURTIME()              获取当前时间       SELECT CURTIME();
                                           返回：15:32:59
 NOW()                    获取当前日期和时间 SELECT NOW();
                                                              返回：2018-01-30 15:32:59
 WEEK(date)             返回日期date为一年 SELECT WEEK(NOW());
                               的第几周                  返回：4
 YEAR(date)             返回日期date的年份 SELECT YEAR(NOW());
                                                              返回：2018
 HOUR(time)            返回时间time的小时 SELECT HOUR(NOW());
                               值                            返回：15
 MINUTE(time)         返回时间time的分钟 SELECT MINUTE(NOW());
                               值                            返回：47
 DATEDIFF              返回日期参数date1和SELECT DATEDIFF(NOW(),'2009-8-8');
 (date1,date2)         date2之间相隔的天数  返回：
 ADDDATE              结算参数date加上n  SELECT ADDDATE(NOW(),5);
 (date,n)                  天后的日期              返回：2018-02-04 15:56:56


数学函数
 函数名      作用                                                举例
 CEIL(x)     返回大于或等于数值x的最小整数         SELECT CEIL(2.3);
                                                                        返回：3
 FLOOR(x)    返回小于或等于数值x的最大整数      SELECT FLOOR(2.3);
                                                                        返回;2
 RAND()      返回0-1之间的随机数                       SELECT RAND();
                                                                        返回：0.43563464424456

ORDER BY之句
ORDER BY之句实现按一定顺序显示查询结果
把成绩都降低10%后加5分，再查询及格成绩，并按照成绩从高到低排序
SELECT 字段名 AS 学生编号,(字段名*0.9+5) AS 综合成绩
FROM 表名
WHERE (字段名*0.9+5) >=60
OROER BY 字段名 DESC;

LIMIT之句
MySQL查询语句中使用LIMIT子句限制结果集
SELECT 字段名
FROM 表名
WHERE 查询条件
GROUP BY 分组的字段名 
ORDER BY 排序的列名 [ASC或DESC]
LIMIT [位置偏移量,]行数;

查询所有年级为1的学员信息，按学号升序排序
  显示前4条记录
  每页4条，显示第2页，即从第5条记录开始显示4条数据

SELECT 字段名，字段名 FROM 表名 WHERE 查询条件   
ORDER BY 字段名  --排序的默认是正序
LIMIT 4;   -- 显示前4条

SELECT 字段名，字段名 FROM 表名 WHERE 查询条件   
ORDER BY 字段名  
LIMIT 4,4;   -- 从第5条开始显示4条
使用LIMIT子句时，注意第1 条记录的位置是0

GROUP BY
分组是把一个字段里相同的分到一个组里
SELECT 字段名 FROM 表名 GROUP BY 字段名;

SELECT 字段名,COUNT(*) FROM 表名 GROUP BY 字段名;  --加了COUNT后，分组后会计数

SELECT 字段名
FROM 表名
WHERE 条件
GROUP BY 字段1，字段2（按照两个字段的组合分组）
ORDER BY 字段1，字段2（先按照第一个字段进行排序，如果有相同的值，再按照第二个字段排序）
LIMIT 开始索引，显示几条记录

什么是子查询

SELECT 字段名 FROM 表名 WHERE 查询条件

SELECT 字段名,字段名,字段名,字段名 FROM 表名 
WHERE 字段名 比较运算符 (SELECT 字段名 FROM 表名 WHERE 查询条件)
子查询：是嵌套关系，查询里包含查询
  先执行子查询，返回所有来自子查询的结果
  再执行外围的父查询，返回查询的最终结果
   
IN子查询：它的用法是在WHERE查询条下有多个相同的结果，可以把等于换成IN 
  这样会把满足条件的全部输出，就不会报错，因为等于只能输出一个结果

NOT IN子查询：它的和IN子查询意思是相反的