Mysql学习笔记（附一）

关于外键约束关系下修改或者删除表的方法：

http://wenku.baidu.com/link?url=RRaI160kvsdf7ibMLqxN815RvStSyenz_-ig1ONfpRfpfFpqtbGQIL4w3IhAw3XySR7Jfxn4Pncl68bIm3qQ_ZZkpJj_HoBt6tFVLa_IHsu

 

mysql中key 、primary key 、unique key 与index区别：

http://zccst.iteye.com/blog/1697043

 

CREATE TABLE products(
	id INT NOT NULL AUTO_INCREMENT,
	cid INT NOT NULL DEFAULT '0',
	name VARCHAR(60) NOT NULL DEFAULT '',
	price DOUBLE(7,2) NOT NULL DEFAULT '0.00',
	num INT NOT NULL DEFAULT '0',
	decn TEXT,
	ptime INT NOT NULL DEFAULT '0',
	PRIMARY KEY(id),
	KEY pname(name,price)
);
解析：KEY pname(name,price)	//括号里面有多个，表示创建多列索引，意思是创建名为pname的关于name和price的索引。

CREATE TABLE cats(
	id INT NOT NULL AUTO_INCREMENT,
	pid INT NOT NULL DEFAULT '0',
	name VARCHAR(60) NOT NULL DEFAULT '',
	desn TEXT,
	PRIMARY KEY(id),
	INDEX name(name,pid)
);

INSERT INTO
cats(pid,name,desn)
values(0,'soft','this is soft'),
(1,'java','this is java'),
(1,'php'),
(1,'c++'),
(2,'j2se'),
(2,'j2ee'),
(2,'j2me'),
(3,'php');

INSERT INTO 
products(cid,name,price,num,desn,ptime)
values(1,'javajava','134.5','10','good','123423'),
(1,'javaone','245.5','24','very good','5345345'),
(2,'javasd','445.5','24','very good','5345245'),
(2,'javsdf','455.5','24','very good','534345'),
(3,'asdf','456.5','24','very good','53445'),
(3,'javasadftwo','415.5','24','very good','54545'),
(4,'javatsdwo','452.5','24','very good','55434'),
(4,'jadfvatwo','415.5','24','very good','534545'),
(4,'jasdfvatwo','145.5','24','very good','534545'),
(4,'javsdfatwo','1245.5','24','very good','534445'),
(5,'javgfdatwo','12245.5','24','very good','535445'),
(5,'javatwo','1245.5','24','very good','55345'),
(5,'javatghwo','1545.5','24','very good','53355'),
(6,'javatfgwo','2845.5','24','very good','534445'),
(6,'javafgtwo','845.5','24','very good','53425'),
(7,'javatghwo','8545.5','24','very good','34345'),
(7,'javatjkwo','5645.5','24','very good','5342345'),
(7,'javatweto','2745.5','24','very good','534545'),
(7,'javatrtwo','8245.5','24','very good','534545'),
(7,'javatwrto','2445.5','24','very good','5345345'),
(8,'javatwweo','1645.5','24','very good','534545'),
(8,'javawetweo','1345.5','24','very good','5342345'),
(8,'javwatwwo','1845.5','24','very good','53456345'),
(8,'javawetwo','1565.5','24','very good','53345'),
(2,'javatwcvo','13745.5','24','very good','5342545'),
(1,'javatdfwo','17345.5','24','very good','534475'),
(1,'javatwco','3445.5','24','very good','534345');