数据库查询语句和数据的导入,导出,及索引的建立

1.SQL查询
    1.distinct: 不显示字段的重复值
    select distinct 字段1,字段2 from 表名;
    示例:
        1.表中都有哪些国家
        select distinct country from sanguo;
        2.表中一共有多少个国家
        select count(distinct country) as n from sanguo;
        3.注意:
            1.distinct 和from之间的所有字段值都想同才会去重
    2.查询表记录时可以做数学运算
        1.运算符:+ - * / %
        2.示例:
            1.查询时显示所有英雄攻击力奖励翻倍
            select id,name,gongji*2 from sanguo;

2.约束
    1.保证数据的一致性和有效性
    2.约束分类:
        1.默认约束(default)
            插入记录时,不给该字段赋值,则使用默认值
            sex enum('man',"woman",'s') default 's',
        2.非空约束(not null)
            不允许该字段的值为NULL
            id int not null,
            id int not null default 0,

3.索引
    1.定义:
        对数据库中表的一列或多列的值进行排序的一种结构
        (BTree)
    2.优点
        加快数据的检索速度
    3.缺点
        1.当对表中数据更新时,索引需要动态维护,降低数据的维护速度
        2.索引需要占用物理存储空间

4索引示例:
    1.开启运行时间检测:mysql > 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 = 'luct1000000';
    6.查看执行时间
        show profiles;

5.索引
    1.普通索引(index)
        1.使用规则:
            1.可设置多个字段,字段值无约束
            2.把经常用来查询的字段设置为索引字段
            3.key标志:mul
        2.普通索引的创建
            1.创建表时:
                create table t1(
                ......,
                index(name),
                index(id)
                );
        3.查看索引
            1.desc 表名,--->key标志为mul
            2.show index from 表名\G
        4.删除index
            drop index 索引名 on 表名;
    2.唯一索引(unique)
        create index 索引名 on 表名(字段名);
        1.使用规则
            1.可设置多个字段
            2.约束:字段值不能重复,但可以为NULL
            3.key标志:UNI
        2.创建
            1.创建表时
                unique(phnumber)
                unique(cardnumber)
            2.已有表中
                create unique index 索引名 on 表名;
            3.查看 删除同普通索引
                删除:drop index 索引名 on 表名;
    3.主键索引(primary key)&&自增长(auto_increament)
        1.使用规则
            1.只能有一个字段为主键字段
            2.约束,字段值不允许重复,也不能为NULL值
            3.KEY标志:PRI
            4.通常设置记录编号的字段id为主键,能够唯一锁定一条记录
        2.创建
            1.创建表时
                1.id int primary key auto_increment,
                alter table 表名 auto_increment=10000,
                示例:
              create table t5( id int primary key auto_increment,

                                          name varchar(100)  not null)

            auto_increment=100000,charset=utf8,engine=InnoDB;
                2.
                    id int auto_increment,
                    name varchar(20),
                    primary key(id,name) #复合主键
            2.已有表
                alter table 表名 add primary key(id);
                alter table 表名 modify id int auto_increment;

        3.删除主键
            1.先删除自增长属性(modify)
                alter table 表名 modify id int;
            2.删除主键
                alter table 表名 drop primary key; 

6.数据导入
    1.作用:把文件自系统中的内容导入到数据库中
    2.语法格式
        load data infile '文件名'     #导入文件
        into table 表名               #导入表
        fields terminated by '分隔符' #字段和单元格的关系
        lines terminated by '\n';
    3.将scoreTable.csv导入到数据库中
        1.在数据库中创建对应的表
        create table score(
        id int,
        name varchar(15),
        score float(5,2),
        phnumber char(11),
        class char(7)
        )character set utf8;

        2.执行数据导入
            1.查看搜索路径
            show variables like 'secure_file_priv'
            2.拷贝文件
                sudo cp ~/scoreTable.csv /var/lib/mysql-files/
            3.执行数据导入
                load data infile
                "/var/lib/mysql-files/scoreTable.csv"
                into table score
                fields terminated by ','
                lines terminated by '\n';

7数据导出
    1.把数据库表的记录导出到系统文件里
    2.语法格式
        select ... from 表名
        into outfile '文件名'
        fileds terminated by '分隔符'
        lines terminated by '\n';
    3.练习
        1.把MOSHOU库下的sanguo表中,英雄的姓名,攻击值,和国家给导出来,sanguo.csv
            1.查看搜索路径
                show variables like '%secure%';
            2.执行数据导出语句
                select name,gongji,country from MOSHOU.sanguo
                into outfile '/var/lib/mysql-files/sanguo.csv'
                fields terminated by ','
                lines terminated by '\n';
        2.把mysql 库下的user表中user,host的值导出到系统文件user.txt
            select user,host from musql.user
            into outfile '/var/lib/mysql-files/user.txt'
            fields terminated by ','
            lines terminated by '\n';
    4.查看和更改文件权限
        1.ls -l score.txt
          #- rw- rw- r--  tarena       tarena 
          r(4): 读         文件的所有者    文件的所属组
          w(2):写
          x(1): 可执行
          rw-:  所有者权限
          rw-:  同组的其他用户对文件的权限
          r--:   其他组的其他用户对文件的权限
          2..chmod 777 score.txt     #rwx-rwx-rwx
          d开头代表文件夹
8.表的复制
    1.语法:
        create table 表名 select ... from 表名 where 条件;
    2.示例:
        复制MOSHOU.sanguo表,sanguo2
        create table MOSHOU.sanguo2 select * from MOSHOU.sanguo;

9.外键(foreign key)
    1.定义:让当前表的字段值在另一张表的范围内去选择
    2.语法格式: 
        foreign key(参考字段名)
        references 主表(被参考字段名)
        on delete 级联动作
        on　datate 级联动作；
    3.使用规则
        1.主表,从表的字段数据类型要一致
        2.主表:被参考字段是主键
    4.示例
        1.表1:缴费信息表(财务)jftb
            id 姓名 班级   缴费金额
             1 张三 aid07   3000
             2 李四 aid07   3000
          表2:学生信息表(班主任)bjtb
            stu_id   姓名    缴费金额
               2      李四    3000
   
        2.创建表  
            表1:
            create table jftab(
            id int primary key,
            name varchar(20) not null,
            class char(5),
            money smallint
            )character set utf8;

            insert into jftab values
            (1,'唐伯虎',1807,3000),
            (2,'点秋香',1807,3000),
            (3,'文彦',1807,3000);
            表2:
            create table bjtab(
            stu_id int,
            name varchar(20),
            money smallint,
            foreign key(stu_id) references jftab(id)
            on delete cascade 
            on update cascade
            )character set utf8;

        3.删除外键
            alter table 表名 drop foreign key　外键名;
            外键名查看　: show create table 表名
        4.已有表添加外键
            alter table 表名 add 
            foreign key(stu_id) references jftab(id)
            on delete 级联动作
            on update 级联动作;
        5.级联动作
            1.cascade 
                数据级联删除,级联更新(参考字段)
            2.restrict(默认)
                从表中有相关联的记录,不允许主表操作
            3.set null
                主表删除或者更新的时候,从表相关联记录字段值为NULL

 

10.嵌套查询(子查询)
    1.定义:把内层的查询结果作为外层的查询条件
    2.语法:
        select ... from 表名 where 字段名 运算符(select...from 表名 where 条件)
    3.练习:
        1.把攻击值小于平均攻击值的名字和攻击值显示出来
            select name,gongji from MOSHOU.sanguo where 
            gongji < (select avg(gongji) from MOSHOU.sanguo);
        2.找出每个国家攻击值最高的英雄的名字和攻击值
            select name,gongji from MOSHOU.sanguo where gongji in
            (select max(gongji) from MOSHOU.sanguo group by country); 
        3.找出每个国家攻击值最高的国家和攻击值
            select country,gongji from MOSHOU.sanguo
            where
            (country,gongji) in (select country,max(gongji) from MOSHOU.sanguo group by country);
11.多表查询
    1.两种方式
        1.笛卡尔积:不加where条件
            select ... from 表1,表2;
        2.加where条件
            select ... from 表1,表2 where 条件;
        3.
            1.记录多的表的每一条记录,去匹配另一张表的所有记录
            2.2张记录条数相同,则后表的每一条记录去匹配前表的所有记录

12.连接查询
    1.内连接(inner join)
        1.语法格式
            select 字段名列表 from 表1 
            inner join 表2 on 条件;
            eg:
            1.显示省和市的详细信息
                select sheng.s_id,sheng.s_name,city.c_id,city.c_name from sheng
                inner join city on sheng.s_id = city.cfather_id;
            2.显示省市县的详细信息
                select sheng.s_id,sheng.s_name,city.c_id,city.c_name,xian.x_id,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.左连接(left join)
            1.以表1为主显示查询结果
            2.select 字段名列表 from 表1
              left join 表2 on 条件
              ledt join 表3 on 条件
            1.显示省和市的详细信息
                select sheng.s_id,sheng.s_name,city.c_id,city.c_name
                from sheng
                left join city on sheng.s_id = city.cfather_id;

            2.显示省市县的详细信息
                select sheng.s_id,sheng.s_name,city.c_id,city.c_name,
                xian.x_id,xian.x_name from sheng
                left join city on sheng.s_id = city.cfather_id 
                left join xian on city.c_id = xian.xfather_id;
        2.右连接(right join)
            以右表为主显示查询结果,用法同左连接