PYTHON1.day20

朱雀 2022-01-09 04:25 219阅读 0赞

上次内容回顾  
1. 约束  
   1）什么是约束：数据库层面提供的数据  
      检查规则，目的保证数据的完整性、  
      一致性、正确性。这些规则是强制性  
   2）类型：非空、唯一性、主键、默认值、  
     自增长、外键约束  
2. 索引  
   1）什么是索引：提高查询效率  
   2）原理：避免全表扫描  
   3）类型：  
      普通索引、唯一索引  
      单列索引、组合索引  
   4）优缺点  
     - 优点：提高查询效率，唯一索引保证  
       数据唯一性，快速找到数据  
     - 缺点：额外空间，额外时间，降低增、  
       删、改效率  
   5）索引使用原则  
     - 在恰当的字段，使用恰当索引  
     - 索引不宜过多  
     - 对于频繁变更的表不宜建太多索引  
     - 在经常用于查询、排序、分组的  
       字段上建立索引  
     - 字段值太少不适合建立索引  
     - 很少查询的字段不是和建立索引  
     - 数据量太少不适合建立索引  
     - 二进制类型字段不适合建立索引  
      
今天内容  
1. 数据导入导出  
   1）导出  
     - 格式：  
       select查询语句  
       into outfile '文件名称'  
       fields terminated by '字段分隔符'  
       lines terminated by '行分隔符'  
     - 示例：导出acct表中数据  
       第一步：查看secure\_file\_priv变量值  
         show variables like 'secure\_file%'  
       第二步：执行导出  
         select \* from acct  
         into outfile  
         '/var/lib/mysql-files/acct.csv'  
         fields terminated by ','  
         lines terminated by '\\n';  
       第三步：查看导出结果(Linux命令行中执行)  
         sudo cat /var/lib/mysql-files/acct.csv       
   2）导入  
     - 格式：  
       load data infile '文件路径'  
       into table 表名  
       fields terminated by '字符分隔符'  
       lines terminated by '行分隔符';  
     - 示例：  
       load data infile  
       '/var/lib/mysql-files/acct.csv'  
       into table acct  
       fields terminated by ','  
       lines terminated by '\\n';     
        
2. 子查询(重点)  
   1）什么是子查询：一个查询语句中嵌套了另  
      一个查询，也叫嵌套查询。

例如：  
      select \* from acct  
      where cust\_no in ( -- 外层查询  
        select cust\_no from customer  
        where status = 1 -- 子查询  
      )  
     说明：  
       括号中的部分称为子查询  
       子查询返回一个集合  
       子查询的结果要和条件要求匹配  
       先执行子查询，再执行外层查询  
       子查询只执行一遍  
   2）使用子查询的情况：一个查询语句无法  
      实现或实现不方便，使用子查询  
   3）单表子查询：子查询、外层查询是一个表  
     - 语法：  
       select 字段列表 from 表A where 条件  
       (select 字段列表 from 表A)  
     - 示例：查询账户表中，余额大于平均  
       余额的账户  
       select \* from acct where balance >  
       (select avg(balance) from acct)  
       -- 等价于：  
       select \* from acct  
       where balance > 6612.50  
   4）多表子查询：子查询、外层查询非同一个表  
     - 语法：       
       select 字段列表 from 表A where 条件  
       (select 字段列表 from 表B where 条件)  
       
     - 示例：查询所有发生过交易的账户信息  
       select \* from acct where acct\_no in  
       (select acct\_no from acct\_trans\_detail)  
        
       -- 查询所有没有发生过交易的账户  
       select \* from acct\_new where acct\_no not in  
       (select distinct acct\_no from acct\_trans\_detail)   distinct\#去重  
        
       -- 查询发生过1500元以上交易金额的账户  
        select \* from acct where acct\_no in  
       (  
         select acct\_no from acct\_trans\_detail  
         where amt > 1500  
       )  
        
3. **连接查询(联合查询)(重点，难点!!!)**  
   1）什么是连接查询：将两个(或以上)的表连接  
      起来，得到一个查询结果(一个表)  
   2）什么情况下使用联合查询：当从一个表中无法  
      查询到想要的全部数据时使用  
      (前提是多表之间有关联关系)  
   3）格式  
     - select 字段列表 from 表A,表B  
       where 关联条件  
       -- 如果关联不正确，产生笛卡儿积     
       select a.acct\_no, a.balance, b.amt  
       from acct a, acct\_trans\_detail b  
       where a.acct\_no = b.acct\_no;-- 关联条件       
      
   4）笛卡尔积  
     - 定义：两个集合的乘积，表示用集合中的  
       元素两两组合，产生新的集合  
     - 意义：表示两个集合所有组合的可能  
       如：A集合表示学生，B集合表示课程  
           A\*B 表示所有学生选课的可能组合

A集合表示所有声母，B集合表示所有韵母  
           A\*B 表示所有发音的可能组合  
     - 笛卡儿积和关系：  
       笛卡尔积中包含不存在、无意义的组合  
       将这部分组合排除，就得到关系（二维表）  
        
   5）连接的分类  
     a)内连接：没有匹配（或关联）到的记录不显示  
       - 格式：select 字段列表 from 表A  
               inner join 表B  
               on 关联条件  
       - 示例：查询账户、户名、交易日期、交易金额  
         select a.acct\_no, a.acct\_name,  
                b.trans\_date, b.amt  
         from acct a inner join acct\_trans\_detail b  
         on a.acct\_no = b.acct\_no;     
         
         课堂练习：编写一个查询语句，从acct和customer  
         表做内连接查询，查询结果包含的字段有：  
         acct\_no  acct\_name  cust\_no  tel\_no  
         select a.acct\_no, a.acct\_name,  
                b.cust\_no, b.tel\_no  
         from acct a inner join customer b  
         on a.cust\_no = b.cust\_no;  
         或  
         select a.acct\_no, a.acct\_name,  
                b.cust\_no, b.tel\_no  
         from acct a, customer b  
         where a.cust\_no = b.cust\_no;         
      
     b)外连接: 分为左连接、右连接  
       - 左连接：左表为主(第一个表)，左表的数据  
         全部显示，右表数据去匹配。如果匹配到，  
         将右表字段的值连接在后面，如果没有匹配  
         到，则填NULL  
       - 格式：  
         select 字段列表 from 表A  
         left join 表B  
         on 关联条件  
       - 示例：查询账号、户名、交易日期、交易金额  
         如果某账户没有交易明细，则交易日期、交易  
         金额字段填写空值  
         select a.acct\_no, a.acct\_name,  
                b.trans\_date, b.amt  
         from acct a  
         left join acct\_trans\_detail b  
         on a.acct\_no = b.acct\_no;  
          
         课堂练习：编写一个查询语句，从acct和customer  
         表做左连接(acct为左表)查询，查询结果包含  
         的字段有：  
         acct\_no  acct\_name  cust\_no  tel\_no  
         select a.acct\_no, a.acct\_name,  
                b.cust\_no, b.tel\_no  
         from acct a left join customer b  
         on a.cust\_no = b.cust\_no;  
          
       - 右连接：以右表(第二个表)为主表，右表的值  
         全部显示，左表进行匹配，匹配到则填入左表  
         的值，匹配不到则填空值  
         格式：  
           select 字段列表 from 表A  
           right join 表B  
           on 关联条件  
            
         示例：查询账号、户名、交易日期、交易金额  
         右表(交易明细)全部显示，如果没有匹配到  
         账号、户名，则填空值  
         select a.acct\_no, a.acct\_name,  
                b.trans\_date, b.amt  
         from acct a  
         right join acct\_trans\_detail b  
         on a.acct\_no = b.acct\_no;

4. 权限管理  
   1）权限：用户可以进行哪些操作  
   2）分类：  
     - 用户类：创建/删除用户，给用户授权  
     - 库/表操作：创建/删除/修改库  
                  创建/删除/修改表     
     - 数据操作：增、删、改、查  
   3）权限用户分类  
     - root：最高权限用户，可以执行所有操作  
     - 大权限用户：可以执行数据库大部分操作  
     - 小权限用户：只能查询  
      
   4）权限表:MySQL中存放权限设置表  
     - user表：最重要的权限表，记录允许连接到  
                  服务器的账号、权限信息  
     - db表：记录授权库的信息  
     - table\_priv表：记录授权表的信息  
     - columns\_priv表：记录授权字段的信息  
      
   5）如何授权  
     - 语法：  
       grant 权限列表 on 库名.表名  
       to '用户名'@'客户端地址'  
       \[identified by '密码'\]  
       \[with grant option\]      该授权用户有无授权权限     
     - 说明：  
       权限列表：用户可以执行的操作  
         all privileges: 所有权限  
         select: 表示select单个权限  
         select,update,delete,...:分别指定权限  
       库名.表名  
         \*.\*       表示所有库下所有表  
         bank.acct 表示bank库下acct表  
         bank.\*    表示bank库下所有表  
       客户端地址  
         %         表示所有客户端  
         localhost 表示本机  
         192.168.0.5  表示指定IP地址的机器  
       \[with grant option\]：对其他用户授权权限  
        
     示例1：对Tom用户授权，能对所有库、所有表  
     进行查询，限定只能从本机登陆  
     并将密码设置为'123456'  
       grant select on \*.\* to 'Tom'@'localhost'  
       identified by '123456';  
       Flush PRIVILEGES;   -- 刷新权限并生效  
       -- 重新用Tom登陆执行查询、插入验证  
       -- 查看user表中的权限,首先进入mysql库  
       select \* from user where user='Tom'\\G  
       
     课堂练习：对bank\_user用户授权，能对bank库  
     下所有表所有表增、删、改、查，限定能从  
     任意客户端登陆，并将密码设置为'123456'  
     grant insert,delete,update,select  
     on bank.\* to 'bank\_user'@'%'  
     identified by '123456';

![1448556-20190215202521410-419930057.png][]

![1448556-20190215202548332-153144762.png][]

转载于:https://www.cnblogs.com/shengjia/p/10385608.html

[1448556-20190215202521410-419930057.png]: /images/20220109/79e5892cb5c741e086aae9e831d4b7b4.png
[1448556-20190215202548332-153144762.png]: /images/20220109/564e9f9a59f7482a851ae459d90d585b.png