CREATE TABLE tbl_dept( id INT(11) NOT NULL AUTO_INCREMENT, deptName VARCHAR(30) DEFAULT NULL, locAdd VARCHAR(40) DEFAULT NULL, PRIMARY KEY(id) )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE tbl_emp ( id INT(11) NOT NULL AUTO_INCREMENT, NAME VARCHAR(20) DEFAULT NULL, deptId INT(11) DEFAULT NULL, PRIMARY KEY (id), KEY fk_dept_Id (deptId) #CONSTRAINT 'fk_dept_Id' foreign key ('deptId') references 'tbl_dept'('Id') )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11); INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12); INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13); INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14); INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15); INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1); INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1); INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1); INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2); INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2); INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3); INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4); INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);
#左连接 SELECT * FROM tbl_dept t1 LEFT JOIN tbl_emp t2 on t1.id = t2.deptId; #右连接 SELECT * FROM tbl_dept t1 RIGHT JOIN tbl_emp t2 on t1.id = t2.deptId; #内连接(交集) SELECT * FROM tbl_dept t1 INNER JOIN tbl_emp t2 on t1.id = t2.deptId; #左连接(只取A独有的) SELECT * FROM tbl_dept t1 LEFT JOIN tbl_emp t2 on t1.id = t2.deptId WHERE t2.deptId is NULL; #右连接(只取B独有的) SELECT * FROM tbl_dept t1 RIGHT JOIN tbl_emp t2 on t1.id = t2.deptId WHERE t1.id is NULL; #A和B均有 SELECT * FROM tbl_dept t1 LEFT JOIN tbl_emp t2 on t1.id = t2.deptId UNION SELECT * FROM tbl_dept t1 RIGHT JOIN tbl_emp t2 on t1.id = t2.deptId; #A和B均是独有 SELECT * FROM tbl_dept t1 LEFT JOIN tbl_emp t2 on t1.id = t2.deptId WHERE t2.deptId is NULL UNION SELECT * FROM tbl_dept t1 RIGHT JOIN tbl_emp t2 on t1.id = t2.deptId WHERE t1.id is NULL;
CREATE [UNIQUE] INDEX index_name ON mytable(column_name(length));
DROP INDEX index_name ON mytable;
\G
表示将查询到的横向表格纵向输出,方便阅读)SHOW INDEX FROM table_name\G
#该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。 ALTER TABLE tbl_name ADD PRIMARY KEY(column_list) #这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次) ALTER TABLE tbl_name ADD UNIQUE index_name(column_list) #添加普通索引,索引值可出现多次。 ALTER TABLE tbl_name ADD INDEX index_name(column_list) #该语句指定了索引为FULLTEXT,用于全文索引。 ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list)
详情可查看:多路查找树(B-Tree,B+Tree,B*Tree)
B-树的关键字(数据项)和记录是放在一起的; B+树的非叶子节点中只有关键字和指向下一个节点的索引, 记录只放在叶子节点中。
select_type:查询的类型,主要用于区别普通查询、联合查询、子查询等复杂查询
table:显示这一行的数据是关于哪张表的
type:访问类型排列,显示查询使用了何种类型,其中从好到坏依次是:system>const>eq_ref>ref>fultext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL。其中,相比比较重要的指标为:system > const > eq_ref > ref > range > index > ALL