欢迎来到Doc100.Net免费学习资源知识分享平台!
您的位置:首页 > 程序异常 >

mysql 二涓〃瀛楃闆嗕笉鍚宩oin鏃朵笉鑳芥纭蛋绱㈠紩

更新时间: 2014-01-05 02:08:30 责任编辑: Author_N1

 

mysql 2涓〃瀛楃闆嗕笉鍚宩oin鏃朵笉鑳芥纭蛋绱㈠紩
鏄ㄥぉ涓€涓悓浜嬪仛鏁版嵁杩佺Щ寮曡捣涓€涓晠闅滐紝鍘熷洜鏄?寮犺〃瀛楃闆嗕竴涓负gbk锛屼竴涓负utf8锛屽苟涓攋oin key涓簐achar绫诲瀷锛屽鑷翠笉鑳芥纭储寮曪紝瀵艰嚧鏁版嵁搴撹秴鏃讹紝淇敼瀛楃闆嗙紪鐮佸悗姝e父銆傛湰鍦伴噸鐜颁簡涓€涓嬶細

涓€銆佹悶娴嬭瘯鏁版嵁锛屾枃绔犳渶鍚庡張鑴氭湰
浜屻€佸缓绱㈠紩
--鍒犻櫎澶氫綑绱㈠紩
drop INDEX index_student_s_age on student ;
drop INDEX index_student_s_no on student ;
drop INDEX index_score_point on score ;
drop INDEX index_score_c_id on score ;

--寤虹储寮曪紝寤烘爣璇彞涓湁锛屽彲蹇界暐
CREATE INDEX index_student_s_age on student (s_age);
CREATE INDEX index_student_s_no on student (s_no);
CREATE INDEX index_score_point on score (point);
CREATE INDEX index_score_c_id on score (c_id);


涓夈€佹祴璇?

1銆?

鍙互璧板埌绱㈠紩


2銆佷慨鏀硅〃缂栫爜锛氫粛鐒跺彲浠ヨ蛋鍒扮储寮曪紝璇存槑join key 閮芥槸鏁板€间粛鐒跺彲浠ヨ蛋鍒扮储寮?
ALTER TABLE student CONVERT TO CHARACTER SET utf8; 
explain select a.*,b.* from student a , score b 
where a.s_no=b.c_id and a.s_no=14
1	SIMPLE	b	const	PRIMARY	PRIMARY	4	const	            1	
1	SIMPLE	a	ref	    index_student_s_no	index_student_s_no	5	const	1	Using where



3銆佷慨鏀瑰叧鑱斿瓧娈电被鍨嬩负涓嶅悓绫诲瀷锛屼笉鑳芥纭蛋鍒扮储寮?
ALTER TABLE score MODIFY c_id VARCHAR(32);
--ALTER TABLE student MODIFY s_no VARCHAR(32);


缁撹锛屽洜涓哄瓧娈电被鍨嬶紝缂栫爜涓嶅悓閮藉洖閫犳垚涓嶈兘姝g‘璧板埌绱㈠紩锛屽鏋滈兘鏄暟鍊肩被鍨嬬殑搴旇灏辨病闂锛?
1銆?琛ㄧ紪鐮佷笉鍚? join瀛楁鏁板€肩被鍨嬩笉鍚? 涓嶈兘姝e父璧扮储寮曪紝鍗充娇缂栫爜鐩稿悓锛燂紵锛?
2銆?琛ㄧ紪鐮佺浉鍚岋紝join key閮芥槸鏁板€肩被鍨嬶紝姝g‘璧板埌绱㈠紩
3銆?琛ㄧ紪鐮佺浉鍚岋紝join key鏁板€肩被鍨嬩笉鍚岋紝涓嶈兘姝g‘璧板埌绱㈠紩


鐩稿叧鏁版嵁锛?
CREATE TABLE `student` (
  `s_no` int(11) DEFAULT NULL,
  `s_name` varchar(500) DEFAULT NULL,
  `s_age` int(11) DEFAULT NULL,
  `s_sex` varchar(10) DEFAULT NULL,
  KEY `index_student_s_no` (`s_no`),
  KEY `index_student_s_age` (`s_age`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;


CREATE TABLE `score` (
  `c_id` int(11) NOT NULL,
  `c_name` varchar(255) DEFAULT NULL,
  `point` int(11) DEFAULT NULL,
  KEY `index_course_point` (`point`),
  KEY `index_course_c_id` (`point`),
  KEY `index_score_point` (`point`),
  KEY `index_score_c_id` (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

INSERT INTO `student` VALUES (1, '寮犳棤蹇?, 18, '鐢?);
INSERT INTO `student` VALUES (2, '鍛ㄨ姺鑻?, 19, '濂?);
INSERT INTO `student` VALUES (3, '鏉ㄨ繃', 19, '鐢?);
INSERT INTO `student` VALUES (4, '璧垫晱', 18, '濂?);
INSERT INTO `student` VALUES (5, '灏忛緳濂?, 17, '濂?);
INSERT INTO `student` VALUES (6, '寮犱笁涓?, 18, '鐢?);
INSERT INTO `student` VALUES (7, '浠ょ嫄鍐?, 19, '鐢?);
INSERT INTO `student` VALUES (8, '浠荤泩鐩?, 20, '濂?);
INSERT INTO `student` VALUES (9, '宀崇伒鐝?, 19, '濂?);
INSERT INTO `student` VALUES (10, '闊﹀皬瀹?, 18, '鐢?);
INSERT INTO `student` VALUES (11, '搴锋晱', 17, '濂?);
INSERT INTO `student` VALUES (12, '钀у嘲', 19, '鐢?);
INSERT INTO `student` VALUES (13, '榛勮搲', 18, '濂?);
INSERT INTO `student` VALUES (14, '閮潠', 19, '鐢?);
INSERT INTO `student` VALUES (15, '鍛ㄤ集閫?, 19, '鐢?);
INSERT INTO `student` VALUES (16, '鐟涘', 20, '濂?);
INSERT INTO `student` VALUES (17, '鏉庣姘?, 21, '濂?);
INSERT INTO `student` VALUES (18, '榛勮嵂甯?, 18, '鐢?);
INSERT INTO `student` VALUES (19, '鏉庤帿鎰?, 18, '濂?);
INSERT INTO `student` VALUES (20, '鍐粯椋?, 17, '鐢?);
INSERT INTO `student` VALUES (21, '鐜嬮噸闃?, 17, '鐢?);
INSERT INTO `student` VALUES (22, '閮', 18, '濂?);


INSERT INTO `score` VALUES (1, '浼佷笟绠$悊', 2);
INSERT INTO `score` VALUES (10, '绾挎€т唬鏁?, 17);
INSERT INTO `score` VALUES (11, '璁$畻鏈哄熀纭€', 13);
INSERT INTO `score` VALUES (12, 'AUTO CAD鍒跺浘', 15);
INSERT INTO `score` VALUES (13, '骞抽潰璁捐', 11);
INSERT INTO `score` VALUES (14, 'Flash鍔ㄦ极', 1);
INSERT INTO `score` VALUES (15, 'Java寮€鍙?, 9);
INSERT INTO `score` VALUES (16, 'C#鍩虹', 2);
INSERT INTO `score` VALUES (17, 'Oracl鏁版嵁搴撳師鐞?, 10);
INSERT INTO `score` VALUES (2, 'max, 8);
INSERT INTO `score` VALUES (3, 'UML', 6);
INSERT INTO `score` VALUES (4, '鏁版嵁搴?, 7);
INSERT INTO `score` VALUES (5, '閫昏緫鐢佃矾', 6);
INSERT INTO `score` VALUES (6, '鑻辫', 3);
INSERT INTO `score` VALUES (7, '鐢靛瓙鐢佃矾', 5);
INSERT INTO `score` VALUES (8, 'maozedong鎬濇兂姒傝', 4);
INSERT INTO `score` VALUES (9, '瑗挎柟鍝插鍙?, 12);



上一篇:上一篇
下一篇:下一篇

 

随机推荐程序问答结果

 

 

如对文章有任何疑问请提交到问题反馈,或者您对内容不满意,请您反馈给我们DOC100.NET论坛发贴求解。
DOC100.NET资源网,机器学习分类整理更新日期::2014-01-05 02:08:30
如需转载,请注明文章出处和来源网址:http://www.doc100.net/bugs/t/6084/
本文WWW.DOC100.NET DOC100.NET版权所有。