Java数据库建表和存储过程

1、在数据中，建表处理是很常见且很实用的方法。表和表之间的关系有 1:11:NN:N三种方式。1对1的方式?123示例：一夫一妻思考：在一个表中保存着所有人的信息。有男有女，要求查询出所有的夫妻，即一男一女。注意下面：王五是光棍不应该能查询出来。?12345678910111213141516171819202122CREATE TABLE person(id INT,NAME VARCHAR(10),sex CHAR(1),wife INT,husband INT);INSERT INTO person VALUES(1,小花,0,0,3);INSERT INTO person VALUES(2

2、,玉芬,0,0,4);INSERT INTO person VALUES(3,张三,1,1,0);INSERT INTO person VALUES(4,李四,1,2,0);INSERT INTO person VALUES(5,王五,1,0,0);/一对一关系的操作：查出每对夫妻的姓名CREATE VIEW w AS SELECT * FROM person WHERE sex=0;CREATE VIEW m AS SELECT * FROM person WHERE sex=1;/不利用表与表之间的关系SELECT w.NAME AS 妻子, m.NAME AS 丈夫 FROM w,m W

3、HERE w.husband=m.id AND m.wife=w.id;/现在更先进的方式：利用表间的关系SELECT w.NAME AS 妻子, m.NAME AS 丈夫 FROM w INNER JOIN m ON w.husband=m.id AND m.wife=w.id;1对多的方式?12341对多，多对1。一个人可以拥有多辆汽车，要求查询出某人所拥有的所有汽车。根据范式的要求，应该设计两张表，分别表示人的信息和汽车的信息。?1234567891011121314151617181920212223242526272829303132333435363738394041424344/

4、步骤1：画E-R图/步骤2:分别建实体表，并给多方的表添加外键约束CREATE TABLE person2(id VARCHAR(32) PRIMARY KEY,pname VARCHAR(30),sex CHAR(1);CREATE TABLE car(id VARCHAR(32) PRIMARY KEY,cname VARCHAR(30),price NUMERIC(10,2),pid VARCHAR(32),CONSTRAINT car_fk FOREIGN KEY(pid) REFERENCES person2(id);DROP TABLE car;/步骤3:为两个表添加测试数据/实体

5、表1INSERT INTO person2(id,pname,sex) VALUES(P001,Jack,1);INSERT INTO person2(id,pname,sex) VALUES(P002,Tom,1);INSERT INTO person2(id,pname,sex) VALUES(P003,Rose,0);INSERT INTO person2(id,pname,sex) VALUES(P004,Mary,0);INSERT INTO person2(id,pname,sex) VALUES(P005,Mike,1);SELECT * FROM person2;/实体表2IN

6、SERT INTO car(id,cname,price,pid) VALUES(C001,BMW,123.5,P001);INSERT INTO car(id,cname,price,pid) VALUES(C002,Benz,123.5,P001);INSERT INTO car(id,cname,price,pid) VALUES(C003,BMW,223.5,P001);INSERT INTO car(id,cname,price,pid) VALUES(C011,BMW,83.5,P003);INSERT INTO car(id,cname,price,pid) VALUES(C01

7、2,Benz,100,P003);INSERT INTO car(id,cname,price,pid) VALUES(C013,Audi,223.5,P003);INSERT INTO car(id,cname,price,pid) VALUES(C021,BMW,88.5,P004);INSERT INTO car(id,cname,price,pid) VALUES(C022,QQ,10,P004);INSERT INTO car(id,cname,price,pid) VALUES(C023,Audi,73,P005);INSERT INTO car(id,cname,price) V

8、ALUES(C033,Audi,1000);?123456789101112/查询：哪些人有什么样的车 (用表名.列名的形式访问列，如果列名不重复，可以省略表名)/利用一方的主键和“多方”的外键进行关联SELECT person2.pname,ame FROM person2,car WHERE person2.id=car.pid;/查询Jack有什么车SELECT person2.pname,ame FROM person2,car WHERE person2.id=car.pid AND person2.pname=Jack ;/查询哪些人有两辆以上的车SELEC

9、T person2.pname,COUNT(pname) AS 车数量 FROM person2,car WHERE person2.id=car.pid GROUP BY pname HAVING COUNT(pname)=2 ORDER BY 车数量;SELECT * FROM person2 WHERE id IN ( SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)=2 );/查询哪些人没有车SELECT * FROM person2 WHERE id NOT IN( SELECT pid FROM car );多对多?12345多对

10、多：一个人可以拥有多种角色，如某人，即是父亲、又是儿子、又是丈夫。而同时这三个角色又可以给其他所有的人。要求查询出即是父亲、又是儿子、又是丈夫的所有人。查询出所有人拥有的所有角色。?1还有学生选课系统都是多对多的情况具体的实现方法在最后的比笔记中贴出来数据库的约束主键 primary key在创建表时指定主键-在声明主键时，应该同时使用not null关键字。Create table stud(id int primary key,.)在创建完成之后指定主键：Alter table stud add constraint stud_pk primary key(id);主键自动增长：Auto_

11、increment 适合于int类型的字段外键 foreign key表示一种引用关系与另一个表的关联关系称为外键，外键的作用时当主表中没有记录时，无法向子表中写入无引用的记录：Alter table stud add constraint stud_fk foreign key(stud_clsid) references cls(id);主外键关系是一种1对多的关系。如果主表中没有的记录，子表中将不能增加。创建主外键关联，两个表的数据类型和大小必须保持一致否则创建将不会成功。可以通过可视化界面操作主外关联。唯一 unique 与主键有所区别，主键不能为null，而unique可以有一列为n

12、ull这是SqlServer的限制，但Mysql可以写入多列的null值，所以，在mysql上使用unique时一般再通过not null进行限制。alter table cls add constraint cls_uk unique(name); /* 指定name不能重复*/默认值 default在创建表时，可以指定默认值如：Create table stud(sex char(1) default 1,.);使用关联对数据进行查询当一个表的数据不能满足我们的需要时，我们就要从多个表中查询数据。此时必须使用关联查询：inner join 内关联，双方必须都要存在。left join 左关

13、联。以左边数据为准。right join 右关联。在上面的表与表之间的关系用到的总结：存储过程三种方式：1，最简单，最基本的方式。?1“DELIMITER$”这句是为了让解析器把“$”当作结束标志(否则默认是把“;“号当作结束标记),这样存储过程中的语句结束符;就不会当作过程的结束标记。?123456789DELIMITER$CREATE PROCEDURE p1()BEGINSELECT * FROM stud;INSERT INTO stud(id,sname,age,score,dept) VALUES(1014,刘三丰,33,55,通信学院);END$DELIMITER; /把结束标记

14、还原回来CALL p1(); /调用存储过程p12，带参数的方式?12345678910/带参数的存储过程DELIMITER$CREATE PROCEDURE p2(IN id INT, IN nm VARCHAR(30) )BEGININSERT INTO stud(id,sname) VALUES(id,nm);END$DELIMITER ; DROP PROCEDURE p2;CALL p2(1015,吊丝);3，带返回值的?12345678910/有返回值的存储过程-参数与变量问题( 变量名 ，一个为用户变量，两个即 为全局的系统变量 )DELIMITER$CREATE PROCEDU

15、RE p3(IN id INT, IN nm VARCHAR(30), OUT num INT )BEGININSERT INTO stud(id,sname) VALUES(id,nm);SELECT COUNT(*) INTO num FROM stud;END$DELIMITER ; CALL p3(1016,无名,aa);SELECT aa; /输出变量aa的值MySql区别大小写的查询?12345678910111213141516 mysql查询默认是不区分大小写的 如： select * from table_name where a like a% select * from

16、table_name where a like A% select * from table_name where a like a% select * from table_name where a like A% 效果是一样的。 要让mysql查询区分大小写，可以： select * from table_name where binary a like a% select * from table_name where binary a like A% select * from table_name where binary a like a% select * from table_

17、name where binary a like A% 也可以在建表时，加以标识 create table table_name( a varchar(20) binary )事务transaction:原子性(atomicity)：组成事务处理的语句形成了一个逻辑单元，不能只执行其中的一部分。一致性(consistency)：在事务处理执行前后，数据库是一致的(数据库数据完整性约束)。隔离性(isolcation)：一个事务处理对另一个事务处理的影响。持续性(durability)：事务处理的效果能够被永久保存下来 。一个事务只会有一个结果：要么成功、要么失败。?123Start transaction;开始一个事务。Commit;提交所做的修改。Rollback;回滚所做的修改。如果在操作时出错，应该从新开始一个事务。设置事物隔离级别 在mySql的命令行设置隔离级别。在MySql的命令行设置隔离级别，只对此打开的命令行窗口有效。第一个命令行窗口，就是一个独立的客户端。Select tx_isolation; 可以查看目前的隔离级别。Set