MySQL(Python相关) day03

2018.8.1

目录

1，SQL查询

2，约束

3，索引

4，数据导入

5，数据导出

6，作业

---

day03

1，SQL查询

1. 执行顺序
   3,select ... 聚合函数 from 表名
   1,where ...
   2,group ...
   4,having ...
   5,order by ...
   6,limit ...
2. group by
   1. 作用：给查询结果进行分组
   2. 示例
      1. 查询表中一共有几个国家
         select country from sanguo group by country;
      2. 计算所有国家的平均攻击力
         select country,avg(gongji) from sanguo
         group by country;

         先分组---->再聚合---->再去重
         蜀国 
         蜀国
         蜀国  -->   120  -->  蜀国
         魏国
         魏国        110       魏国
         吴国        115       吴国

          

      3. 查找所有国家中英雄数量最多的前2名的国家名称和英雄数量
         select country,count(id) from sanguo
         group by country
         order by count(id) desc
         limit 2;
   3. 注意
      1. group by之后的字段名必须要为select之后的字段名
      2. 如果select之后的字段名和group by之后的字段不一致，则必须对该字段进行聚合处理(聚合函数)
3. having 语句
   1. 作用
      对查询的结果进行进一步筛选
   2. 示例
      1. 找出平均攻击力大于105的国家前2名，显示国家名和平均攻击力
         select country,avg(gongji) as pjgj from sanguo
         group by country
         having pjgj > 105
         order by pjgj desc
         limit 2;
   3. 注意
      1. having 语句通常和group by 语句联合使用，过滤由group by 语句返回的记录集
      2. where 只能操作表中实际存在字段 ，having可操作有聚合函数生成的显示列
4. distinct 
   1. 作用：不显示字段重复值
   2. 示例
      1. 表中都有哪些国家
         select distinct country from sanguo;
      2. 计算蜀国一共有多少个英雄
         select count(distinct id) from sanguo
         where country='蜀国';
      3. 注意
         1. distinct 和 from之间所有字段都相同才会去重
         2. distinct 不能对任何字段做聚合处理
5. 查询表记录时做数学运算
   1. 示例
      1. 查询时所有英雄攻击力翻倍
         select id,name,gongji*2 from sanguo;
   2. 运算符
      +  -  *  /  %

2，约束

1. 作用：保证数据的完整性，一致性，有效性
2. 约束分类
   1. 默认约束(default)
      1，插入记录，不给该字段赋值，则使用默认值
      2，格式：字段名 数据类型 default 默认值
   2. 非空约束(not NULL)
      1，不允许该字段的值有NULL记录
      2，格式：字段名 数据类型 not null
      
      create table day2(
      id int not null,
      name varchar(15) not null,
      sex enum('m','f','secret') default 'secret'
      );
      
      desc day2;

      +-------+------------------------+------+-----+---------+-------+
      | Field | Type                   | Null | Key | Default | Extra |
      +-------+------------------------+------+-----+---------+-------+
      | id    | int(11)                | NO   |     | NULL    |       |
      | name  | varchar(15)            | NO   |     | NULL    |       |
      | sex   | enum('m','f','secret') | YES  |     | secret  |       |
      +-------+------------------------+------+-----+---------+-------+
      3 rows in set (0.00 sec)
      
      insert into day2(id) values(1);
      
      ERROR 1364 (HY000): Field 'name' doesn't have a default value
      #这句话的意思是name没有值可以代替，不允许为空所以会报错
      
      insert into day2(id,name) values(1,'sss');
      
      mysql> select * from day2;
      +----+------+--------+
      | id | name | sex    |
      +----+------+--------+
      |  1 | sss  | secret |
      +----+------+--------+
      1 row in set (0.00 sec)
      #sex的值，不给数据时，我们默认的值是secret，给数据看下面一句
      
      mysql> insert into day2 values(2,'sss','m');
      
      mysql> select * from day2;
      +----+------+--------+
      | id | name | sex    |
      +----+------+--------+
      |  1 | sss  | secret |
      |  2 | sss  | m      |
      +----+------+--------+
      2 rows in set (0.00 sec)
      
      

3，索引

1. 定义
   对数据库表的一列或多列的值进行排序的一种结构
   (Btree方式)
2. 优点
   加快数据检索速度
3. 缺点
   1，占用物理存储空间
   2，当对表中数据更新时，索引需要动态维护，降低数据维护速度
4. 索引示例
   1. 开启运行时间检查：
      show variables like '%character%';
      show variables like '%pro%';
      set profiling=1;
   2. 执行查询语句
      select name from t1 where name ='lucy99999';
   3. 查看执行时间
      show profiles;
   4. 在name字段创建索引
      create index name on t1(name);
   5. 再执行查询语句
      select name from t1 where name='lucy88888';
   6. 查看执行时间
      show profiles;
   7. 关闭性能分析
      set profiling=0;
5. 索引
   1. 普通索引 (index)
      1. 使用规则
         1. 可设置多个字段
         2. 字段值无约束
         3. key标志：MUL
      2. 创建index
         1. 创建表时
            create table 表名( 
            ... ...,
            ... ...,
            index(字段名),
            index(字段名)
            );
         2. 已有表
            create index 索引名 on 表名(字段名);
            #索引名一般和字段名一样
      3. 查看索引
         1. desc 表名； -->KEY标志为: MUL
         2. show index from 表名\G;
      4. 删除索引
         drop index 索引名 on 表名；
         删除普通索引只能一个一个删除
   2. 唯一索引（unique）
      1. 使用规则
         1. 可设置多个字段
         2. 约束：字段值不允许重复，但可为NULL
         3. KEY 标志：UNI
      2. 创建
         1. 创建表时创建
            unique(字段名)，
            unique(字段名)
         2. 已有表
            create unique index 索引名 on 表名(字段名);
         3. 查看，删除，同普通索引
            desc  表名；
            show index from 表名；
            drop index 索引名 on 表名；
   3. 主键索引(primary key)
      自增长属性(auto_increment,配合主键一起使用)
      1. 使用规则:
         1. 只能有一个主键字段
         2. 约束:不允许重复，且不能为NULL
         3. KEY标志：PRI
         4. 通常设置记录编号字段id，能唯一锁定一条记录
      2. 创建
         1. 创建表 
            … id int primary key auto_increment, 
            … …)[auto_increment=10000]; 
            
            … id int auto_increment, 
            … primary key(id))[auto_increment=1000];
         2. 在已有表中创建 
            alter table 表名 add primary key(字段名);
         3. 删除主键 
            1. 删除auto_increment属性 (自增长属性)
               alter table 表名 modify id int;
            2. 删除主键 
               alter table 表名 drop primary key;
         4. 在已有表中添加自增长属性并指定起始值 
            1. 添加自增长属性 
               alter table 表名 modify id int auto_increment;
            2. 指定起始值 
               alter table 表名 auto_increment=值;
                

               mysql> create table t6(
                   -> id int primary key auto_increment,
                   -> name varchar(15)
                   -> );
               Query OK, 0 rows affected (0.01 sec)
               
               mysql> desc t6;
               +-------+-------------+------+-----+---------+----------------+
               | Field | Type        | Null | Key | Default | Extra          |
               +-------+-------------+------+-----+---------+----------------+
               | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
               | name  | varchar(15) | YES  |     | NULL    |                |
               +-------+-------------+------+-----+---------+----------------+
               2 rows in set (0.01 sec)
               
               mysql> insert into t6(name) values('试试');
               mysql> select * from t6;
               +----+--------+
               | id | name   |
               +----+--------+
               |  1 | 试试   |
               +----+--------+
               
               insert into t6 values(null,'等等');#null属于占位
               mysql> select * from t6;
               +----+--------+
               | id | name   |
               +----+--------+
               |  1 | 试试   |
               |  2 | 等等   |
               +----+--------+
               
               alter table t6 auto_increment=9999;
               mysql> insert into t6 values(null,'傻妮');
               mysql> select * from t6;
               
               +------+--------+
               | id   | name   |
               +------+--------+
               |    1 | 试试   |
               |    2 | 等等   |
               | 9999 | 傻妮   |
               +------+--------+
               #如果输错了，ctrl+c +回车，可以退出

               #)auto_increment=10000; ##设置自增长起始值
               mysql> create table t5(
                   -> id int primary key auto_increment,
                   -> name varchar(15)
                   -> )auto_increment=8888;
               
               mysql> insert into t5 values(null,'ss');
               Query OK, 1 row affected (0.01 sec)
               
               mysql> select * from t5;
               +------+------+
               | id   | name |
               +------+------+
               | 8888 | ss   |
               +------+------+

                

   4. 外键索引 此篇点击

4，数据导入

1. 作用
   把文件系统的内容导入数据库中
2. 语法
   load data infile '/var/lib/mysql-files/文件名'
   into table 表名
   fields terminated by '分隔符'
   lines terminated by '\n'
3. 将scoretable.csv 文件导入到数据库的表中
   1. 在数据库中创建对应的表
      create table scoretab(
      id int,
      name varchar(15),
      score float(5,2),number bigint,
      class char(7)
      );
   2. 把文件拷贝到数据库的默认搜索路径中
      1. 查看默认搜索路径
         show variables like 'secure_file_priv';
         
         /var /lib/mysql-files/
      2. 拷贝文件
         sudo cp ~ /scoretable.csv /var/lib/mysql-files/
         
         本题是这样的，sudo cp /home/tarena/scoretable.csv /var/lib/mysql-files/
   3. 执行数据导入语句
      
      load data infile "/var/lib/mysql-files/scoretable.csv"
      into table scoretab
      fields terminated by ","
      lines terminated by "\n";
      
      
       
   4. 文件权限

      rwxrw-rw- 1 tarena tarena scoretable.csv
                  所有者  所属组
      rwx：tarena用户
      rw-：同组其它用户
      rw-：其它组的其它用户（mysql用户，第三组用户）
      
      r -> 4
      w -> 2
      x -> 1
      chmod 644 文件名   rw-r--r--
      
      #修改文件权限
      ls -l 文件名
      chmod 644 文件名

       

   5. Excel表格如何转化为csv文件
      1. 打开Excel文件 -> 另存为 -> csv(逗号分隔)
   6. 更改文件编码格式
      1. 用记事本/编辑器 打开，文件 ->另存为 ->选择编码

         数据导入格式：
         创建表
         
         复制文件
         
         数据导入
         
         #编码问题

          

 

5，数据导出

1. 作用
   将数据库中表的记录导出到系统文件里
2. 语法格式
   select ... from 表名
   into outfile '/var/lib/mysql-files/文件名'
   fields terminated by '分隔符'
   lines terminated by '\n';
3. 把MOSHOU 库下的sanguo表英雄的姓名，攻击值，国家导出来，sanguo.txt

   select name,gongji,country from MOSHOU.sanguo
   into outfile '/var/lib/mysql-files/sanguo.txt'
   fields terminated by '   '
   lines terminated by '\n';
   
   $sudo -i
   $cd /var/lib/mysql-files/
   $ls
   $cat sanguo.txt

    

4. 将mysql库下的user表中 user，host两个字断的值导出到user.txt

   select user,host from mysql.user
   into outfile '/var/lib/mysql-files/user.txt'
   fields terminated by '   '
   lines terminated by '\n';
   
   system sudo -i
   cd /var/lib/mysql-files/
   ls
   cat user.txt

    

6，作业

1. 把 /etc/passwd 导入到数据库表里面

   tarena  :  x  :  1000  : 1000  :  tarena,,,
   用户名    密码    UID号   GID号    用户描述  
   :  /home/tarena:   /bin/bash
      家目录/主目录     登录权限
   
   
   create table userinfo(
   username char(20),
   password char(1),
   uid int,
   gid int,
   comment varchar(50),
   homedir varchar(50),
   shell varchar(50)
   );
   
   
   show variables like 'secure_file_priv';
   
   
   $sudo cp /etc/passwd /var/lib/mysql-files/
   
   
   load data infile '/var/lib/mysql-files/passwd'
   into table userinfo
   fields terminated by ':'
   lines terminated by '\n';
   
   
   select * from userinfo;

2. 在userinfo 第一列添加一个id字段，主键，自增长，显示宽度为3，位数不够用0填充
    

   alter table userinfo add id int(3) zerofill primary key auto_increment first;

3. 面试题
   
   有一张文章评论表comment如下

   comment_id	article_id	user_id	date
   1	10000	10000	2018-01-30 09:00:00
   2	10001	10001	... ...
   3	10002	10000	... ...
   4	10003	10015	... ...
   5	10004	10006	... ...
   6	10025	10006	... ...
   7	10009	10000	... ...

   以上是一个应用的comment表格的一部分，请使用SQL语句找出在本站发表的所有评论数量最多的10位用户及评论数，并按评论数从高到低排序

   备注：comment_id为评论id

             article_id为被评论文章的id

             user_id 指用户id

   select user_id,count(user_id) from comment
   group by user_id order by count(user_id) desc limit 10;