Java数据库建表和存储过程

在数据中，建表处理是很常见且很实用的方法。表和表之间的关系有1:11:NN:N三种方式。1对1的方式?123spanstyle=font-size:18px;示例：一夫一妻思考：在一个表中保存着所有人的信息。有男有女，要求查询出所有的夫妻，即一男一女。注意下面：王五是光棍不应该能查询出来。/span?12345678910111213141516171819202122spanstyle=font-size:18px;CREATETABLEperson(idINT,NAMEVARCHAR(10),sexCHAR(1),wifeINT,husbandINT);INSERTINTOpersonVALUES(1,'小花','0',0,3);INSERTINTOpersonVALUES(2,'玉芬','0',0,4);INSERTINTOpersonVALUES(3,'张三','1',1,0);INSERTINTOpersonVALUES(4,'李四','1',2,0);INSERTINTOpersonVALUES(5,'王五','1',0,0);//一对一关系的操作：查出每对夫妻的姓名CREATEVIEWwASSELECT*FROMpersonWHEREsex='0';CREATEVIEWmASSELECT*FROMpersonWHEREsex='1';//不利用表与表之间的关系SELECTw.NAMEAS妻子,m.NAMEAS丈夫FROMw,mWHEREw.husband=m.idANDm.wife=w.id;//现在更先进的方式：利用表间的关系SELECTw.NAMEAS妻子,m.NAMEAS丈夫FROMwINNERJOINmONw.husband=m.idANDm.wife=w.id;/span1对多的方式?12341对多，多对1。一个人可以拥有多辆汽车，要求查询出某人所拥有的所有汽车。根据范式的要求，应该设计两张表，分别表示人的信息和汽车的信息。strong/strong?123456789101112131415161718192021222324252627282930313233//步骤1：画E-R图//步骤2:分别建实体表，并给多方的表添加外键约束CREATETABLEperson2(idVARCHAR(32)PRIMARYKEY,pnameVARCHAR(30),sexCHAR(1));CREATETABLEcar(idVARCHAR(32)PRIMARYKEY,cnameVARCHAR(30),priceNUMERIC(10,2),pidVARCHAR(32),CONSTRAINTcar_fkFOREIGNKEY(pid)REFERENCESperson2(id));DROPTABLEcar;//步骤3:为两个表添加测试数据//实体表1INSERTINTOperson2(id,pname,sex)VALUES('P001','Jack','1');INSERTINTOperson2(id,pname,sex)VALUES('P002','Tom','1');INSERTINTOperson2(id,pname,sex)VALUES('P003','Rose','0');INSERTINTOperson2(id,pname,sex)VALUES('P004','Mary','0');INSERTINTOperson2(id,pname,sex)VALUES('P005','Mike','1');SELECT*FROMperson2;////实体表2INSERTINTOcar(id,cname,price,pid)VALUES('C001','BMW',123.5,'P001');INSERTINTOcar(id,cname,price,pid)VALUES('C002','Benz',123.5,'P001');INSERTINTOcar(id,cname,price,pid)VALUES('C003','BMW',223.5,'P001');3435363738394041424344INSERTINTOcar(id,cname,price,pid)VALUES('C011','BMW',83.5,'P003');INSERTINTOcar(id,cname,price,pid)VALUES('C012','Benz',100,'P003');INSERTINTOcar(id,cname,price,pid)VALUES('C013','Audi',223.5,'P003');INSERTINTOcar(id,cname,price,pid)VALUES('C021','BMW',88.5,'P004');INSERTINTOcar(id,cname,price,pid)VALUES('C022','QQ',10,'P004');INSERTINTOcar(id,cname,price,pid)VALUES('C023','Audi',73,'P005');INSERTINTOcar(id,cname,price)VALUES('C033','Audi',1000);?123456789101112/查询：哪些人有什么样的车(用表名.列名的形式访问列，如果列名不重复，可以省略表名)//利用一方的主键和“多方”的外键进行关联SELECTperson2.pname,car.cnameFROMperson2,carWHEREperson2.id=car.pid;//查询Jack有什么车SELECTperson2.pname,car.cnameFROMperson2,carWHEREperson2.id=car.pidANDperson2.pname='Jack';//查询哪些人有两辆以上的车SELECTperson2.pname,COUNT(pname)AS车数量FROMperson2,carWHEREperson2.id=car.pidGROUPBYpnameHAVINGCOUNT(pname)=2ORDERBY车数量;SELECT*FROMperson2WHEREidIN(SELECTpidFROMcarGROUPBYpidHAVINGCOUNT(pid)=2);//查询哪些人没有车SELECT*FROMperson2WHEREidNOTIN(SELECTpidFROMcar);多对多?12345多对多：一个人可以拥有多种角色，如某人，即是父亲、又是儿子、又是丈夫。而同时这三个角色又可以给其他所有的人。要求查询出即是父亲、又是儿子、又是丈夫的所有人。查询出所有人拥有的所有角色。strong/strong?1还有学生选课系统都是多对多的情况具体的实现方法在最后的比笔记中贴出来数据库的约束主键primarykey在创建表时指定主键---在声明主键时，应该同时使用notnull关键字。Createtablestud(idintprimarykey,…..)在创建完成之后指定主键：Altertablestudaddconstraintstud_pkprimarykey(id);主键自动增长：Auto_increment适合于int类型的字段外键foreignkey－表示一种引用关系与另一个表的关联关系－称为外键，外键的作用时当主表中没有记录时，无法向子表中写入无引用的记录：Altertablestudaddconstraintstud_fkforeignkey(stud_clsid)referencescls(id);主外键关系是一种1对多的关系。如果主表中没有的记录，子表中将不能增加。创建主外键关联，两个表的数据类型和大小必须保持一致否则创建将不会成功。可以通过可视化界面操作主外关联。唯一unique–与主键有所区别，主键不能为null，而unique可以有一列为null这是SqlServer的限制，但Mysql可以写入多列的null值，所以，在mysql上使用unique时一般再通过notnull进行限制。altertableclsaddconstraintcls_ukunique(name);/*指定name不能重复*/默认值default在创建表时，可以指定默认值如：Createtablestud(sexchar(1)default‘1’,…..);使用关联对数据进行查询当一个表的数据不能满足我们的需要时，我们就要从多个表中查询数据。此时必须使用关联查询：innerjoin–内关联，双方必须都要存在。leftjoin－左关联。以左边数据为准。rightjoin－右关联。在上面的表与表之间的关系用到的总结：存储过程三种方式：1，最简单，最基本的方式。?1“DELIMITER$$”这句是为了让解析器把“$$”当作结束标志(否则默认是把“;“号当作结束标记),这样存储过程中的语句结束符;就不会当作过程的结束标记。?12345678DELIMITER$$CREATEPROCEDUREp1()BEGINSELECT*FROMstud;INSERTINTOstud(id,sname,age,score,dept)VALUES(1014,'刘三丰',33,55,'通信学院');END$$DELIMITER;//把结束标记还原回来9CALLp1();//调用存储过程p12，带参数的方式?12345678910//带参数的存储过程DELIMITER$$CREATEPROCEDUREp2(INidINT,INnmVARCHAR(30))BEGININSERTINTOstud(id,sname)VALUES(id,nm);END$$DELIMITER;DROPPROCEDUREp2;CALLp2(1015,'吊丝');3，带返回值的?12345678910//有返回值的存储过程----参数与变量问题(@变量名，一个@为用户变量，两个@即@@为全局的系统变量)DELIMITER$$CREATEPROCEDUREp3(INidINT,INnmVARCHAR(30),OUTnumINT)BEGININSERTINTOstud(id,sname)VALUES(id,nm);SELECTCOUNT(*)INTOnumFROMstud;END$$DELIMITER;CALLp3(1016,'无名',@aa);SELECT@aa;//输出变量aa的值MySql区别大小写的查询?12345678910111213141516spanstyle=font-size:18px;white-space:pre;/spanmysql查询默认是不区分大小写的如：spanstyle=white-space:pre/spanselect*fromtable_namewherealike'a%'spanstyle=white-space:pre/spanselect*fromtable_namewherealike'A%'spanstyle=white-space:pre/spanselect*fromtable_namewherealike'a%'spanstyle=white-space:pre/spanselect*fromtable_namewherealike'A%'spanstyle=white-space:pre/span效果是一样的。spanstyle=white-space:pre/span要让mysql查询区分大小写，可以：spanstyle=white-space:pre/spanselect*fromtable_namewherebinaryalike'a%'spanstyle=white-space:pre/spanselect*fromtable_namewherebinaryalike'A%'spanstyle=