database
create database web08 character set gbk;
show create database web08;
select database(); -- 查看正在使用的数据库
use web08; -- 没有 'database'
table
create table user (
uid int(32) primary key auto_increment,
uname varchar(32),
upwd varchar(32)
) -- mysql中是varchar, oracle中是varchar2
show tables; -- 显示所有表
desc user; -- 建表细节
drop table user;
--------------------------------------
alter table user add uage int(32) not null; -- 增加一列
... modify 列名 类型(长度) 约束; -- 修改某列长度或约束
... change 旧列名 新列名 类型(长度) 约束; -- 修改整列
... drop 列名
rename table 表名 to 新表名; -- 改表名
alter table 表名 character set 字符集; -- 修改字符集
set names gbk; -- 解决姓名中文乱码 ??
DML
自动增长的列插入时可以用null占位
外键 一对多(主对从),多(从表)的一侧建外键
create table category ( -- 商品目录表
cid varchar(32) primary key,
cname varchar(100)
);
create table product( -- 商品表
pid varchar(32) primary key,
pname varchar(40),
price double,
category_id varchar(32)
);
alter table product add foreign key(category_id) references category(cid); -- 没有constraint
-- 此时可以删除从表的数据,但是主表就不能删除了,若主表删除了一行,则少了一列主键,则违反从表中外键约束
' a foreign key constraint fails '
alter table product drop foreign key 外键名称(一般以 '_fk' 结尾)
多对多的情况 学生选课 — 制造一张中间表,包含两张表的主键分别作为外键
alter table 学生_课程表 add foreign key(学号) references 学生表(学生id);
alter table 学生_课程表 add foreign key(课程号) reference 课程表(课程id);
订单多对多表
create table orderitem(
oid varchar(50),
pid varchar(50)
);
alter table orderitem add CONSTRAINT orderitem_fk FOREIGN key(oid) references orders(oid);
alter table orderitem add CONSTRAINT orderitem_product_fk foreign key(pid) references product(pid); -- 有constraint
多表查询
交叉连连
select * from category,product
内连接 inner join
前提:
alter table product add foreign key(category_id) references category(cid); -- 没有constraint
显式:select * from category inner join product on cid=category_id
隐式:select * from category c, product p where c.cid=p.category_id
外连接 outer join (outer可省略) —左外连接、右外连接
显式:
select * from category left join product on cid=category_id (结果同上)
select * from category right join product on cid=category_id(结果同上)
如果category或product中数据不完整:
insert into category values('c004', null);
insert into product values('p010', 'iphone', 900, null)
则
select * from category left join product on cid=category_id
select * from category right join product on cid=category_id
哪侧连接,以那侧为主,另一侧交集显示,不是交集不显示
隐式:
连null那条记录都不显示,只显示有值的交集记录
子查询
查询化妆品类别下的商品
select * from product where category_id = (select cid from category where cid = 'c003')
下面的jdbc不想记了,做项目时不是这样写的,小项目现用DBUtils
DBUtils
常规jdbc存在大量的重复代码,只有sql语句有点区别,DBUtils用来解决这个问题。
QueryRunner(Datasource ds)
ResultSetHandler
QueryRunner
update(String sql, Object... params)
query(String sql, ResultSetHandler<T> rsh, Object... params)
ResultSetHandler
CRUD
public void testAddUser() {
//1. 创建QueryRunner
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
//2. 写sql
String sql = "insert into user values(null, ?, ?)";
//3. 所有参数都放到参数数组中
Object[] params = {"caocao", "haha"};
try {
int rows = qr.update(sql, params);
if(rows > 0) {
System.out.println("success.");
} else {
System.out.println("failed");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} //增删改都差不多
@Test
public void testQueryAll() {
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from user";
try {
List<User> list = qr.query(sql, new BeanListHandler<User>(User.class));
for (User user : list) {
System.out.println(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Test
public void testQueryBy() {
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from user where uname = ?";
Object[] params = {"liubei"};
try {
User user = qr.query(sql, new BeanHandler<User>(User.class), params);
System.out.println(user);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//查询个数 count(*)
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select count(*) from user";
try {
Long count = (Long) qr.query(sql, new ScalarHandler());
System.out.println(count);
...