python开发boMySQL索引 索引的类型 数据导出 数据导入 表的复制 嵌套查询 多表查询 连接查询

索引

BTREE

优点	加快数据的检索速度
缺点	1、 当对表中的数据进行增加、修改、删除时 索引需要动态维护 降低了数据的维护速度
	2、索引需要占用物理存储空间

 

示例：

1、开启运行时间检测	Set profiling=1;
2、执行查询语句	select name from t1 where name=”lucy99999”;
3、查看执行时间	Show prfoiles;
4、给name字段创建索引	create index name on t1(name);
5、执行查询语句	select name from t1 where name= “licy88888”;
6、查看执行时间	Show profiles;

 

 

 

 

 

 

 

 

 

索引的类型

1、普通索引（index）	使用规则	1.可设置多个字段   2.约束: 无 3.KEY标志： MUL
	创建	1.创建表时:index(字段名)，          index(字段名); 2.已有表时： Create index索引名 on 表名（字段名）;
	查看	1. desc 表名; 2. show index from 表名\G;(详细信息)
	删除	1.drop index 索引名 on 表名;ec
2、唯一索引（unique）	使用规则	1. 可设置多个字段 2. 约束: 字段值不允许重复，允许 3. KEY标志：UNT
	创建	1. 创建表：unique（字段名），        unique（字段名） 2. 已有表： Create unique index 索引名 on 表名（字段名）;
	查看	1.desc 表名; 2.show index from 表名\G;(详细信息) (Non_unique: 0 à 唯一索引) (Non_unique: 1 à 普通索引)
	删除	1.drop index 索引名 on 表名;
3、主键（primary key）	使用规则	1. 只能有一个主键字段 2. 约束：不允许重复，不能为NULL 3. KEY标志：PRI 4. 通常设置记录编号字段 id 为主键 唯一锁定一条记录
	创建	1. 创建表时： id int primary key auto_increment, 自增长：auto_increment, 添加、删除：modify id int； 2. 已有表时： alter table 表名 add primary key（字段名）；
	删除	1. 删除自增长属性 alter table 表名 modify id int； 2. 删除主键 alter table 表名 drop primary key；
	指定自增长属性起始值	1、创建表Create table 表名（ Id int primary key auto_increment）， ……. ）auto_increment=10000; 2、alter table 表名 auto_increment=10000;
4、外键（foreign key）	定义	当前表字段值从另一个表范围内选择
	语法	foreign key(参考字段名) references 主表(被参考字段名) on delete 级联动作 on update 级联动作
	使用规则	1、主表、从表 字段数据类型要一致 2、主表被参考字段 ：主键
	示例	表1、缴费信息表(财务)     学号  姓名     班级    金额   1   唐伯虎  AID1805  300   2   点秋香  AID1805  200        表2、学生信息表(班主任)         学号  姓名    金额     表1、jftab       create table jftab(       id int primary key,       name varchar(20),       class char(7),       money int       )character set utf8;       insert into jftab values       (1,"唐伯虎","AID1805",300),       (2,"点秋香","AID1805",200);     表2、bjtab       create table bjtab(       stu_id int,       name varchar(20),       money int,       foreign key(stu_id) references jftab(id)       on delete cascade       on update cascade)character set utf8;       insert into bjtab values       (1,"唐伯虎",300),       (2,"点秋香",200);
	删除外键	1、 查看外键名 ： show create table 表名; 2、 删除外键 ： alter table 表名 drop foreign key 外键名;
	级联动作	1、cascade    数据级联删除、更新(参考字段) 2、restrict(默认)    从表有相关联记录,不允许主表操作 3、set null    主表更新,从表相关联记录字段值自动设置为 ：NULL
	已有表添加键	alter table 表名 add foreign key(参考字段名) references 主表(被参考字段) on delete ... on update ...

 

 

数据导入

意义	1、 把文件系统内容导入到数据的表中
命令格式	load data infile “文件名” into table 表名 fileds terminated by “分隔符” lines terminated by “\n”
示例	将scretable.csv 文件导入到db3库下的socre表中

示例

将scretable.csv 文件导入到db3库下的socre表中

1.查看搜索路径方法

show variables like “secure_file_priv”;

2.执行复制命令

tarena@tedu:~$ sudo cp /home/tarena/scoretable.csv/var/lib/mysql-files/

tarena@tedu:~$ sudo –i 

切换到mysql下

root@tedu:~# cd /var/lib/mysql-files

查看是否已经导入

root@tedu:/var/lib/mysql-files# ls

结果如下

scoretable.csv

查看文件的权限

root@tedu:/var/lib/mysql-files# ls -l scoretable.csv

结果如下

-rwxr--r-- 1 root root 1719 7月   4 12:48 scoretable.csv

   修改权限

root@tedu:/var/lib/mysql-files# chmod 666（可读可写） scoretable.csv

结果如下

root@tedu:/var/lib/mysql-files# ls -l scoretable.csv

-rw-rw-rw- 1 root root 1719 7月   4 12:48 scoretable.csv

3、创建库、表（utf8字符集）

1、create database db3 character set utf8;

2、use db3;

3、

create table score(

id int,

name varchar(20),

score decimal(5,2),

phone char(11),

class char(7)

)character set utf8;

4、执行导入语句

load data infile

         "/var/lib/mysql-files/scoretable.csv"

into table score

fields terminated by ","

        lines terminated by "\n";

注意：

1、库、表必须都为utf8编码

2、路径必须写绝对路径 /var/lib/mysql-files/..."

4、(1)文件权限问题

rwx ：所有者对此文件权限

--- ：所属组其他用户对此文件权限

rw- ：其他组的用户对此文件权限

root ：所有者    

root ：所属组

1、修改文件权限

chmod +rw scoretable.csv

chmod 666 scoretable.csv

r:4

w:2

x:1

步骤：

1、sudo -i

2、cd /var/lib/mysql-files

3、chmod 666 scoretable.csv

 

 

 

 

5、excel 文件转为csv 文件

 文件另存为 csv

打开excel 表格 -> 文件 -> 另存为 –>*.csv（逗号分隔）

6、更改一个文件的字符编码

用记事本/编辑器 打开，文件-> 另存为 –>选择编码

 

 

 

数据导出

意义	将数据库中表记录保存到系统文件里
语法格式	select …from 表名 into outfile “文件名” fields terminated by“分隔符” lines terminated by“\n”

示例：

1. 把sanguo表中的姓名，攻击值，国家三个字段导出到文件sanguo.csv中

mysql> 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 mysql.user

into outfile "/var/lib/mysql-files/user.txt"

fields terminated by "   "

lines terminated by "\n";

注意

1、导出的内容完全由SQL查询语句决定

2、路径必须指定为数据库搜索的绝对路径

表的复制

语法	create table 表名 select ... from 表名;

示例

1、复制sanguo表,sanguo2

create table MOSHOU.sanguo2 select * from MOSHOU.sanguo;

2、复制sanguo表的前3条记录,sanguo3

create table MOSHOU.sanguo3 select * from sanguo limit 3;

3、复制sanguo表的id、name和country三个字段的前5条记录,sanguo4

create table MOSHOU.sanguo4 select id,name,country from MOSHOU.sanguo limit 5;

3、只复制表结构

create table 表名 select * from 表名 where false;

 

 

 

嵌套查询

定义	把内层的查询结果作为外层的查询条件
语法	Select … from 表名 where 字段名 运算符（select…）;

示例：

1. 把攻击值小于 平均攻击值 的名字和攻击值显示出来

select name,gongji from MOSHOU.sanguo

where gongji<(select avg(gongji) from MOSHOU.sanguo);

2、找出每个国家攻击力最高的英雄名字和攻击值

逻辑错误：

select name,gongji from sanguo

where gongji in

(select max(gongji) from sanguo group by country);

正确：

select name,gongji from sanguo

where (country,gongji) in

(select country,max(gongji) from sanguo group by country);

多表查询

方式	1.lesect 字段名列表 from 表1，表2；迪卡尔积（不加where）
	2.select 字段名列表 from 表1,表2 where 条件

  示例

1、显示 省、市详细信息

select sheng.s_name,city.c_name from sheng,city

where sheng.s_id=city.cfather_id;

2、显示 省、市、县详细信息

select sheng.s_name,city.c_name,xian.x_name from sheng,city,xian

    where sheng.s_id=city.cfather_id and city.c_id=xian.xfather_id;

 

 

 

 

 

 

连接查询

内连接	Select … from 表1 inner join 表2 on 条件； 示例： 1、显示 省、市详细信息 select sheng.s_name,city.c_name from sheng inner join city on sheng.s_id=city.cfather_id; 2、显示 省、市、县详细信息 select sheng.s_name,city.c_name,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;
外连接	左连接	意义	以左表为主显示查询结果
		语法	select ... from 表1 left join 表2 on 条件;
		示例	1、显示 省、市 详细信息,要求省全部显示 select sheng.s_name,city.c_name from sheng  left join city  on sheng.s_id=city.cfather_id; 2、显示 省、市、县 详细信息,要求 省 全部显示 select sheng.s_name,city.c_name,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、 显示 省、市、县详细信息,要求市 全部显示(left、right)（左连接、右连接） select sheng.s_name,city.c_name,xian.x_name from sheng right join city on sheng.s_id=city.cfather_id left join xian on city.c_id=xian.xfather_id;
	右连接	语法	select ... from 表1 right join 表2 on 条件;