MySQL的多表联查

#创建成绩表
CREATE TABLE practice(  -- 创建表名为practice的数据表
    major varchar(4) COMMENT '学科',
    scores INT UNSIGNED COMMENT'成绩' -- 定义无符号整型成绩字段
)COMMENT'成绩表';
INSERT INTO practice(name,major,scores)VALUES('李华','数学','70'),('王芳','化学','74'),('刘欢','语文','74');      -- INSERT INTO 对practice批量插入数据

INSERT INTO practice(name,major,scores)VALUE('方雅','数学','81'); -- 对practice插入数据


#创建学生信息表
CREATE TABLE infor (
    id CHAR(5) PRIMARY KEY,
    name VARCHAR(6) UNIQUE,
    address VARCHAR(10)
)COMMENT'学生信息表';

INSERT INTO infor VALUES('00001','李华','凤凰街道'),('00002','王芳','昭文区向阳路'),('00003','刘欢','同德街道'),('00004','方雅','吴家坝');

ALTER TABLE practice ADD CONSTRAINT F_K FOREIGN KEY (id) REFERENCES infor(ID);  -- 为表practice添加infor的外键

ALTER TABLE practice DROP FOREIGN KEY F_K; -- 删除F_K外键


#内连接，只需要提前将两表设置外键关联，就能通过关联的字段查找两表之间所需的数据
SELECT practice.name,infor.address FROM practice,infor WHERE practice.id=infor.id; -- 隐式内连接和单表查询一样，只需把查询的字段和查询的表依次列举出来，用where筛选条件就行
SELECT infor.name,practice.major FROM infor INNER JOIN practice ON practice.ID=infor.id;  -- 显示内连接用INNER JOIN连接两表，用ON筛选条件


#外连接，包含左外连接和右外连接，区别在于查询左表和两表交集的全部数据、右表和交集的全部数据。和显式内连接相似，把INNER换成相应的LEFT/RIGHT就行
SELECT infor.name,practice.major FROM infor LEFT JOIN practice ON practice.id=infor.id; -- 左外连接，LEFT JOIN 连接两表，左边的为左表
SELECT infor.address,practice.name FROM infor RIGHT JOIN practice ON practice.id=infor.id;  -- 右外连接，RIGHT JOIN 连接两表，右边的为右表

#自连接和内外连接一样，区别在于自连接是在一张表内进行的查询，但由于表内数据间也存在对应关系所以把一张表看做多张表进行连接查询。
# 自连接由于是多张同样的表，所以必须给表起别名







#索引
#创建索引
CREATE INDEX name ON infor(name); -- 为表infor的name字段创建一个name索引
SHOW INDEXES FROM infor; -- 查看infor表中的全部索引。当把某一个字段设置了唯一或主键约束后，会自动设置为唯一索引或主键索引
DROP INDEX name ON infor; -- 删除infor表中的name索引

#查看索引频率
SHOW GLOBAL STATUS LIKE'COM_______' ;  -- 查看当前数据库全部索引的使用频率（七个下划线）

SELECT  @@HAVE_PROFILING; -- 查询当前数据库是否支持profiling
SELECT @@PROFILING; -- 查询当前profile是否开启，0为关闭，1为开启
SET profiling=1; -- 将profile的状态开启，可以查询sql中性能慢的语句
SELECT * FROM infor;
SHOW PROFILES ; -- 查询各语句的耗能情况

#explain 显示一句查询sql语句的具体信息，比如用到的索引，根据这些信息可以对sql语句进行优化
explain SELECT infor.name,practice.major FROM infor LEFT JOIN practice ON practice.id=infor.id;