人工智能（mysql）—— mysql高级查询（索引、多表、连接）
一、索引
1、索引的定义
对数据库中表的一列或者多列的值进行排序的一种结构(MySQL中索引用Btree方式)
2、索引的优点
加快数据的检索速度
3、索引的缺点
a、需动态维护,占用系统资源,降低数据维护速度
b、占用物理存储空间
4、索引对比
a、运行 insert.py 文件（参见附录1），插入100万条记录(终端窗口)
python3 insert.py
b、开启性能分析（mysql环境）
set profiling=1;
备注 ：show variables like "profiling";
c、执行查询语句
select name from t1 where name="lucy99999";
d、查看性能分析结果
show profiles;
e、给name字段创建索引
create index name on t1(name);
f、执行查询语句
select name from t1 where name="lucy88888";
g、查看性能分析结果
show profiles;
h、关闭性能分析
set profiling = 0;
5、索引类型
普通索引(index)，唯一索引(unique)，主键索引(primary key)&&自增长属性(auto_increment)，外键(foreign key)
二、索引类型
1、普通索引(index)
a、使用规则
1）一个表中可以有多个index字段
2）字段的值可以重复，也可以为NULL
3）经常把做查询条件的字段设置为index字段
4）index字段的key标志位：MUL
b、创建
1）创建表时创建index语法：index(字段名),
create table t4(
id int,
name varchar(20),
index(id),
index(name)
);
desc t4; 2）在已有表中添加索引字段
语法：create index 索引名 on 表名（字段名）；
# 索引名一般和字段名一样，也可以自己定义索引名
alter table t4 add score tinyint;
create index score on t4(score);
desc t4;c、查看普通索引
1）desc 表名； --> 查看KEY标志位MUL
2）show index from 表名\G;
show index from t4;
show index from t4\G;d、删除
drop index 索引名 on 表名;
注意：删除普通索引只能一个一个删除
drop index name on t4;
drop index score on t3; 2、唯一索引(unique)
a、使用规则
1）一个表中可以有多个unique字段2）unique字段的值不允许重复，可以为空值NULL
3）unique的KEY表中是：UNI
b、创建（基本等同index创建）
1）创建表unique(字段名),
create table t5(
id int,
name varchar(20),
unique(id),
unique(name)
);create unique index 索引名 on 表名(字段名);
# 索引名一般和字段名一样，也可以自己定义索引名
alter table t5 add name varchar(10);
create unique index name_index on t5(name);c、查看、删除同普通索引
desc 表名；show index from 表名；
drop index 索引名 on 表名；
注：
Non_unique: 0 --> 唯一索引
Non_unique: 1 --> 普通索引
3、主键索引(primary key)&&自增长属性(auto_increment)
a、使用规则
1）一个表中只能有一个主键字段
2）对应字段的值不允许重复 且 不能为空值NULL
3）主键字段的KEY标志位：PRI
4）把表中能够唯一标识一条记录的字段设置为主键，通常把表中记录编号id的字段设置为主键
b、创建主键（PRI）
1）创建表时
格式1：字段名 数据类型 primary key auto_increment，
create table t6(
id int primary key auto_increment,
name varchar(10)
); 格式2：primary key（id），
create table t7(
id int auto_increment,
name varchar(10),
primary key(id)
); 2）已有表
I、在已有表中添加主键
alter table 表名 add primary key(字段名) ；
II、在已有表中添加自增长属性
alter table 表名 modify 字段名 数据类型 auto_increment;
注：字段名的数据类型可以重新指定
create table t8(
id int,
name varchar(10)
);
alter table t8 add primary key(id);
alter table t8 modify id bigint auto_increment;c、删除主键
1）删除自增长属性
alter table 表名 modify id 数据类型;
2）删除主键
alter table 表名 drop primary key;
d、指定自增长属性起始值
1）创建表（创建时就指定起始值）
create table 表名(
id int primary key auto_increment,
... ...
)auto_increment=10000;
2）已有表（自增长属性必须已存在，才能添加起始值）
alter table 表名 auto_increment=10000;
alter table t8 auto_increment=1000;
4、外键(foreign key)
a、定义
让当前表字段值从另一个表范围内选择b、语法格式
foreign key(参考字段名)references 主表(被参考字段名)
on delete 级联动作
on update 级联动作
c、使用规则
1）两张表被参考字段（主表）和参考字段（从表）数据类型要一致2）主表被参考字段 ：主键
d、级联动作
1）cascade 数据级联更新当主表删除级联时，从表会级联更新
当主表更新被参考字段的值时，从表会级联更新
2）restrict 默认
当删除主表记录时，如果从表中有相管理记录则不允许主表删除
更新同理
3）set null
当主表删除记录时，从表中相关联记录的参考字段值自动设置为NULL
更新同理
4）no action
on delete no action on update no action
同 restrict，都是立即检查外键限制
e、示例
| 学号 | 姓名 | 班级 | 金额 |
|---|---|---|---|
| 1 | 唐伯虎 | AID1 | 10 |
| 2 | 点秋香 | AID1 | 8 |
| 学号 | 姓名 | 金额 |
|---|---|---|
| 1 | 唐伯虎 | 10 |
| 2 | 点秋香 | 8 |
#主表：jftab
create table jftab(
id int primary key,
name varchar(20),
class char(7),
money int
)character set utf8;
insert into jftab values
(1,"唐伯虎","AID1",10),
(2,"点秋香","AID1",8);
#从表：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,"唐伯虎",10),
(2,"点秋香",8);g、删除外键
alter table 表名 drop foreign key 外键名；注：
外键名的查看方式：show create table 表名；
h、在已有表中添加外键
## 会受到表中原有数据的限制alter table 表名 add foreign key（参考字段名）
references 主表（被参考字段名）
on delete 级联动作
on update 级联动作；
二、多表查询
1、select 字段名列表 from 表1,表2;
相当于笛卡尔积(不加where)
表1中所有字段分别和表2的第一个字段匹配，然后表1中所有字段分别和表2的下一个字段匹配，...
2、select 字段名列表 from 表1,表2 where 条件;
等同于连接查询中的内连接（inner join）
# 通过附录2创建表，然后查询
a、显示 省、市详细信息
select sheng.s_name,city.c_name from sheng,city
where sheng.s_id=city.cfather_id;
b、显示 省、市、县详细信息
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;三、连接查询
1、内连接
从表中删除与其他被连接的表中没有匹配到的行（只显示匹配到的行）
语法格式select 字段名列表 from 表1
inner join 表2 on 条件
inner join 表3 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;2、外连接
a、左连接
以左表为主显示查询结果
语法格式select 字段名列表 from 表1
left join 表2 on 条件；
a.以省表为主（省全部显示），显示省、市详细信息
select sheng.s_name,city.c_name from sheng
left join city on sheng.s_id=city.cfather_id;
b.以县表为主（县全部显示），显示省、市、区详细信息
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;
c.以市表为主（市全部显示），显示省、市、区详细信息
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;b、右连接
用法同左连接，以右表为主显示查询结果四、附录1：insert.py 文件
import pymysql
db = pymysql.connect("localhost","root","123456")
cursor = db.cursor()
cursor.execute("create database indexdb;")
cursor.execute("use indexdb;")
cursor.execute("create table t1(id int,name char(20));")
n = 1
name="lucy"
while n <= 2000000:
cursor.execute("insert into t1 values('%s','%s')" % (n,name+str(n)))
# n = int(n)
n += 1
db.commit()
cursor.close()
db.close()五、附录2：创建省、市、区表
create table sheng(
id int primary key auto_increment,
s_id int,
s_name varchar(15)
)default charset=utf8;
insert into sheng values
(1, 130000, '河北省'),
(2, 140000, '陕西省'),
(3, 150000, '四川省'),
(4, 160000, '广东省'),
(5, 170000, '山东省'),
(6, 180000, '湖北省'),
(7, 190000, '河南省'),
(8, 200000, '海南省'),
(9, 200001, '云南省'),
(10,200002,'山西省');
create table city(
id int primary key auto_increment,
c_id int,
c_name varchar(15),
cfather_id int
)default charset=utf8;
insert into city values
(1, 131100, '石家庄市', 130000),
(2, 131101, '沧州市', 130000),
(3, 131102, '廊坊市', 130000),
(4, 131103, '西安市', 140000),
(5, 131104, '成都市', 150000),
(6, 131105, '重庆市', 150000),
(7, 131106, '广州市', 160000),
(8, 131107, '济南市', 170000),
(9, 131108, '武汉市', 180000),
(10,131109, '郑州市', 190000),
(11,131110, '北京市', 320000),
(12,131111, '天津市', 320000),
(13,131112, '上海市', 320000),
(14,131113, '哈尔滨', 320001),
(15,131114, '雄安新区', 320002);
create table xian(
id int primary key auto_increment,
x_id int,
x_name varchar(15),
xfather_id int
)default charset=utf8;
insert into xian values
(1, 132100, '正定县', 131100),
(2, 132102, '浦东新区', 131112),
(3, 132103, '武昌区', 131108),
(4, 132104, '哈哈', 131115),
(5, 132105, '安新县', 131114),
(6, 132106, '容城县', 131114),
(7, 132107, '雄县', 131114),
(8, 132108, '嘎嘎', 131115);六：附录3：目录
七、练习
综述：两张表，一张顾客信息表customers，一张订单表orders
1、创建一张顾客信息表customers，字段要求如下：
c_id 类型为整型，设置为主键，并设置为自增长属性
c_name 字符类型，变长，宽度为20
c_age 微小整型，取值范围为0~255(无符号)
c_sex 枚举类型，要求只能在('M','F')中选择一个值
c_city 字符类型，变长，宽度为20
c_salary 浮点类型，要求整数部分最大为10位，小数部分为2位
在表中任意插入3条记录,c_name为"Zhangsan","Lisi","Wangwu", c_city尽量 写"Beijing","Shanghai" ......
2、创建一张订单表orders，字段要求如下：
o_id 整型
o_name 字符类型，变长，宽度为30
o_price 浮点类型，整数最大为10位，小数部分为2位
设置此表中的o_id字段为customers表中c_id字段的外键,更新删除同步
在表中任意插入5条记录(注意外键限制)
o_name分别为"iphone","ipad","iwatch","mate9","r11",其他信息自己定
3、返回customers表中，工资大于4000元，或者年龄小于29岁，满足这样条件的前2条记录
4、把customers表中，年龄大于等于25岁，并且地址是北京或者上海，这样的人的工资上调15%
5、把customers表中，城市为北京的顾客，按照工资降序排列，并且只返回结果中的第一条记录
6、选择工资salary最少的顾客的信息
7、找到工资大于5000的顾客都买过哪些产品的记录明细
8、删除外键限制
9、删除customers主键限制
以下是整个操作的过程（用户名：root，密码：123456），根据自己的用户名、密码登录。
注：进入mysql交互终端$ mysql -uroot -p123456
mysql> create database homework
-> ;
Query OK, 1 row affected (0.13 sec)
mysql> use homework;
Database changed
mysql>
mysql> create table customers(
-> c_id int primary key auto_increment,
-> c_name varchar(20),
-> c_age tinyint unsigned,
-> c_sex enum("M","F"),
-> c_city varchar(20),
-> c_salary double(12,2));
Query OK, 0 rows affected (0.54 sec)
mysql> desc customers;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| c_id | int(11) | NO | PRI | NULL | auto_increment |
| c_name | varchar(20) | YES | | NULL | |
| c_age | tinyint(3) unsigned | YES | | NULL | |
| c_sex | enum('M','F') | YES | | NULL | |
| c_city | varchar(20) | YES | | NULL | |
| c_salary | double(12,2) | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.05 sec)
mysql> insert into customers values(1,"Zhangsan",20,"M","Shenzhen",2345.23);
Query OK, 1 row affected (0.21 sec)
mysql> insert into customers values(2,"Lisi",23,"F","Shanghai",7345.23);
Query OK, 1 row affected (0.02 sec)
mysql> insert into customers values(3,"Wangwu",29,"M","Beijing",70345.23);
Query OK, 1 row affected (0.01 sec)
mysql> create table orders(
-> o_id int,
-> o_name varchar(30),
-> o_price double(12,2),
-> foreign key(o_id)
-> references customers(c_id)
-> on delete cascade
-> on update cascade);
Query OK, 0 rows affected (0.12 sec)
mysql> desc orders;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| o_id | int(11) | YES | MUL | NULL | |
| o_name | varchar(30) | YES | | NULL | |
| o_price | double(12,2) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
mysql> insert into orders values(1,"iphone",293.1);
Query OK, 1 row affected (0.03 sec)
mysql> insert into orders values(3,"ipad",493.1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into orders values(3,"iwatch",334.1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into orders values(2,"mate9",199.9);
Query OK, 1 row affected (0.02 sec)
mysql> insert into orders values(1,"r11",99.9);
Query OK, 1 row affected (0.01 sec)
mysql> select * from orders;
+------+--------+---------+
| o_id | o_name | o_price |
+------+--------+---------+
| 1 | iphone | 293.10 |
| 3 | ipad | 493.10 |
| 3 | iwatch | 334.10 |
| 2 | mate9 | 199.90 |
| 1 | r11 | 99.90 |
+------+--------+---------+
5 rows in set (0.00 sec)
mysql> desc customers;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| c_id | int(11) | NO | PRI | NULL | auto_increment |
| c_name | varchar(20) | YES | | NULL | |
| c_age | tinyint(3) unsigned | YES | | NULL | |
| c_sex | enum('M','F') | YES | | NULL | |
| c_city | varchar(20) | YES | | NULL | |
| c_salary | double(12,2) | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> select * from customers where c_salary>4000 or c_age<29;
+------+----------+-------+-------+----------+----------+
| c_id | c_name | c_age | c_sex | c_city | c_salary |
+------+----------+-------+-------+----------+----------+
| 1 | Zhangsan | 20 | M | Shenzhen | 2345.23 |
| 2 | Lisi | 23 | F | Shanghai | 7345.23 |
| 3 | Wangwu | 29 | M | Beijing | 70345.23 |
+------+----------+-------+-------+----------+----------+
3 rows in set (0.00 sec)
mysql> update customers set c_salary=c_salary*1.15 where c_age>=25 and c_city in (
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from customers;
+------+----------+-------+-------+----------+----------+
| c_id | c_name | c_age | c_sex | c_city | c_salary |
+------+----------+-------+-------+----------+----------+
| 1 | Zhangsan | 20 | M | Shenzhen | 2345.23 |
| 2 | Lisi | 23 | F | Shanghai | 7345.23 |
| 3 | Wangwu | 29 | M | Beijing | 80897.01 |
+------+----------+-------+-------+----------+----------+
3 rows in set (0.00 sec)
mysql> select * from customers where c_city="Beijing" order by c_salary desc limit 1;
+------+--------+-------+-------+---------+----------+
| c_id | c_name | c_age | c_sex | c_city | c_salary |
+------+--------+-------+-------+---------+----------+
| 3 | Wangwu | 29 | M | Beijing | 80897.01 |
+------+--------+-------+-------+---------+----------+
1 row in set (0.03 sec)
mysql> select * from customers order by c_salary asc limit 1;
+------+----------+-------+-------+----------+----------+
| c_id | c_name | c_age | c_sex | c_city | c_salary |
+------+----------+-------+-------+----------+----------+
| 1 | Zhangsan | 20 | M | Shenzhen | 2345.23 |
+------+----------+-------+-------+----------+----------+
1 row in set (0.00 sec)
mysql> select * from orders where o_id in (select c_id from customers where c_salary>5000);
+------+--------+---------+
| o_id | o_name | o_price |
+------+--------+---------+
| 2 | mate9 | 199.90 |
| 3 | ipad | 493.10 |
| 3 | iwatch | 334.10 |
+------+--------+---------+
3 rows in set (0.02 sec)
mysql> show create table orders;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE `orders` (
`o_id` int(11) DEFAULT NULL,
`o_name` varchar(30) DEFAULT NULL,
`o_price` double(12,2) DEFAULT NULL,
KEY `o_id` (`o_id`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`o_id`) REFERENCES `customers` (`c_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
mysql> alter table orders drop foreign key orders_ibfk_1;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table customers modify c_id int;
Query OK, 3 rows affected (0.16 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> alter table customers drop primary key ;
Query OK, 3 rows affected (0.12 sec)
Records: 3 Duplicates: 0 Warnings: 0