java中级笔记四

1.主外键
1.主键 primary key
id int PRIMARY KEY,
primary key(`id`)
2.外键 foreign key
CASCADE  级联
RESTRICT 限制

CREATE TABLE `NewTable` (
`id`  int(11) NOT NULL DEFAULT 0 ,
PRIMARY KEY (`id`),
FOREIGN KEY (`baseid`) REFERENCES `basetable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX `foreigntable_ibfk_1` (`baseid`) USING BTREE 
)
delete from basetable where id=1

ASC 升序
DESC 降序 默认使用

LIMIT  0  10  42
0：从0开始
10：每页10条记录
42：共42条记录
2.多表
1.多表基本查询
1.自然连接
等值连接，相同的记录条数被抽取
SELECT * from goods1 NATUEAL join goods2
2.交叉连接
3*3交叉
SLECT * from goods1 g1，goods2 g2 ORDER BY g1.id
3.内连接
相同的id对应
SELECT * from goods1 g1, inner join goods2 g2 on g1.id=g2.id//速度快
SELECT * from goods1 g1, goods2 g2 where g1.id = g2.id //速度慢
差9条，手动过滤结果集内连接效率较高
4.外连接
右外连接
以右表为基准取左表和它相同的
SELECT * from goods1 g1 rigtht join goods2 g2 on g1.id=g2.id;
左外连接
以左表为基准取右表和它相同的
SELECT * from goods1 g1 left join goods2 g2 on g1.id=g2.id;
5.自连接 
SELECT * from goods1 g1 left join goods1 g2 on g1.id = g2.price;
2.子表
子查询
all
SELECT * from goods1 where price>=all(SELECT price from goods1 where `name`='老罗')
exits/not exits 已经存在，查询所有
SELECT * from  goods1 where  not exits(SELECT `name` from goods1 where `name`='老罗')
3.分组查询
group by having
SELECT * from goods1 group by goods1.price where price>3 
4.集合
unin
unin 表与表的合并，重复的数据被过滤
SELECT * from goods1 UNION
SELECT * from goods2
5.视图
1.创建视图
CREATE VIEW MY_VIEW as SELECT * from goods1 UNION
SELECT * from goods2
2.查询视图
SELECT * from my_view
3.JDBC:Java DataBase Connection java连接数据库
产品 框架


1. JAR


2. 常用类


3. 配置