mysql普通使用

create

-- 创建表操作
--  创建News_user数据库表
create table if not exists  News_user(
    id int not null primary key auto_increment,
    username varchar(50) not null unique,
    tel varchar(50),
    mail varchar(50),
    imgfile varchar(50) not null
);

-- 创建News_info数据库表
create table if not exists News_info(
    id int not null primary key auto_increment,
    title varchar(100) not null,
    info longtext not null,
    Pubtime timestamp default CURRENT_TIMESTAMP not null,
    classify varchar(50) not null,
    mark int default 0 not null,
    check (mark=0 or mark=1)
);

-- 创建用户与数据对应表News_relation
create table if not exists News_relation(
    id int not null primary key auto_increment,
    userid int not null,
    infoid int not null,
    mark int default 0 not null,
    foreign key (userid) references `news_user`(id),
    foreign key (infoid) references `news_info`(id),
    check (mark=0 or mark=1)
);

select

-- 单表查询
select title,Pubtime from `news_info` where id<2;
-- 多表联锁查询
select `news_user`.id,`news_user`.username,`news_info`.title from 
`news_user`,`news_info`,`news_relation` where
`news_user`.username='张三' and `news_user`.id=`news_relation`.userid and `news_relation`.infoid=`news_info`.id;

-- join多表查询
select `news_user`.id,`news_user`.username,`news_info`.title from
`news_user` inner join `news_relation` on `news_user`.username='张三' and `news_user`.id=`news_relation`.userid
inner join `news_info` on `news_relation`.infoid=`news_info`.id;

-- 左连接left join
select `news_user`.id,`news_user`.username,`news_relation`.infoid from
`news_user` left join `news_relation` on `news_user`.username='张三' and `news_user`.id=`news_relation`.userid;

-- 右连接right join
select `news_user`.id,`news_user`.username,`news_relation`.infoid from
`news_user` right join `news_relation` on `news_user`.username='张三' and `news_user`.id=`news_relation`.userid;

-- distinct关键词
select distinct title from `news_info`;

-- order by
select title,info from `news_info` order by id;
select title,info from `news_info` order by title desc;
select title,info from `news_info` order by title asc;
select title,info from `news_info` order by info asc,title desc;

-- limit
select title,info from `news_info` limit 5;

-- alias表名称别名
select title as titlename from `news_info`;

-- 创建新表复制旧表
create table news_infos(select *from `news_info`);
-- union命令 all允许重复
select title from `news_info` union all select info from `news_info`;

-- 导出数据 需要设置路径
select *from `news_info` into outfile "xxx\out.txt"

-- group
select id,title,info from `news_info` group by title;
-- having
select id,title,info from `news_info` group by title having id>3;

insert

insert into `news_user` (username,tel,mail,imgfile) values("张三","12345678","12345678@qq.com","img/q.jpg");
insert into `news_user` (username,imgfile) values("李四","img/b.jpg");
insert into `news_info`(title,info,classify) values("人工智能","例子等","高科技技术");
insert into `news_info`(title,info,Pubtime,classify,mark) values("百度广告","推广等",now(),"百度广告",1);
insert into `news_info`(title,info,classify) values("算法分析","算法讲解等","高科技技术");
insert into `news_info`(title,info,Pubtime,classify,mark) values("网易广告","推广等",now(),"网易广告",1);
insert into `news_relation`(userid,infoid) values(1,1);
insert into `news_relation`(userid,infoid,mark) values(1,2,1);
insert into `news_relation`(userid,infoid,mark) values(2,2,1),(2,1,0);

-- 多数插入操作
insert into `news_info`(title,info,classify) values("算法详解","算法认识等","高科技技术"),("大数据认识","大数据认识等","高科技技术");

alter

-- 声明外键
alter table `news_relation` add foreign key (userid) references `news_user`(id);
-- 撤销外键 news_relation_ibfk_1约束名称
alter table `news_relation` drop foreign key 'news_relation_ibfk_1';
-- check约束
alter table `news_info` add check (mark=0 or mark =1);
-- 撤销check
alter table `news_info` drop check (mark=0 or mark=1);
-- default约束
alter table `news_info` alter mark set default 0;
-- 撤销default
alter table `news_info` alter mark drop default;
-- 增加新列
alter table `news_info` add test int;
-- 删除列
alter table `news_info` drop column test;
-- 改变数据类型
alter table `news_info` modify column test varchar(50);

drop

drop tables `news_info`,`news_user`,`news_relation`;
drop database work1;