mysql(java)基本用法

 

use zmz;# 使用zmz 数据库
# 书表
create table article(
 id int unsigned auto_increment  not null primary key,
 title varchar(50),
 author varchar(30),
 post_date date
)engine=innodb default charset=utf8;
insert into article values(null,'三国演义','罗贯中','1995-12-15'),(null,'三国演义','罗贯中','1995-11-15'),(null,'水浒传','施耐庵','1995-10-15');
insert into article values(null,'魔道师祖','墨香铜臭','1995-12-15'),(null,'明朝那些事儿','当年明月','1995-11-15'),(null,'简爱','夏洛特.勃朗特','1995-10-15');
insert into article values(null,'家','巴金','1995-12-15'),(null,'春','巴金','1995-11-15'),(null,'秋','巴金','1995-10-15');
select * from article;
# 删除表内数据
truncate table article;
# 删除表
drop table article;
# 评论表
create table comment(
 id int unsigned auto_increment not null primary key,
 content varchar(100),
 comment_date date,
 article_id int unsigned not null
)engine=innodb default charset=utf8;

# 修改表名
alter table comment rename to comments;
rename table comments to comment;

# 增加
insert into comment values(null,'好','2010-11-11',1),(null,'精彩','2012-11-11',2),(null,'好看','2001-11-11',3);
insert into comment values(null,'好看','2000-11-11',4),(null,'good','2000-11-11',5),(null,'good','2000-11-11',6);
insert into comment values(null,'good','2000-11-11',1),(null,'good','2000-11-11',2),(null,'good','2000-11-11',3);
# 查询
select * from comment;
# 删除一条数据
delete from comment where id=4;
# 修改数据
update comment set content='不好' where id=5;
# 删除表内所有数据
truncate table comment;
# 删除表
drop table comment;
# 建立comment 和 article 的关联  外键
alter table comment add foreign key(article_id) references article(id) on delete set null on update cascade;
# 显示每篇文章的评论数   
select a.title,count(c.article_id) from article a, comment c where a.id=c.article_id group by c.article_id;
# 查询结果显示三列 article_id title comment_count 并按照count的从大到小的顺序排列
select c.article_id,a.title,count(c.article_id) from comment c,article a where a.id=c.article_id group by c.article_id;
# 查询 重名的
select title,count(*) from article group by title having count(*)>1;
select title,count(title) from article group by title having count(title)>=1 order by count(title); 
# 消除重名的
select distinct title  from article;
# 分页显示  第一个参数是a  第二个参数 b  a=b(当前页数—1)次幂;
select * from  article limit 0,3;
# like % ; 查询作者名字带有 中的 书
select * from article where author like'%中%';
# 正则表达式 查询姓罗 的作者 的书
select * from article where author regexp '^罗.*$';