创建数据表
create table table_name (column_name column_type);
创建Author、Article、ArticleDetail三张表
#创建Author表drop table if exists Author;create table Author( au_id int not null PRIMARY key auto_increment comment '主键', name varchar(12) comment '姓名', sex varchar(12), address varchar(12), qq varchar(12), wechat varchar(25), create_date datetime)DEFAULT CHARSET=utf8; #创建Article表drop table if exists Article;create table Article( ar_id int not null PRIMARY key auto_increment comment '主键', type varchar(12) comment '类型', author varchar(12)comment '作者', au_id int(11), articles int(11), qq_group int(11), fans int(11), update_date datetime)DEFAULT CHARSET=utf8; #创建ArticleDetail表drop table if exists ArticleDetail;create table ArticleDetail( ad_id int not null PRIMARY key auto_increment comment '主键', ar_id int(11), title varchar(255) comment '文章标题', url varchar(255)comment '链接', reade_times int(11), praise_times int(11), comments_times int(11), publish_date datetime, FULLTEXT(url))DEFAULT CHARSET=utf8;
操作表
表中增加新列
alter table table_name add column_name column_type;
Author表增加国籍(hometown)列
#在Author表后增加国籍列alter table Author add hometown varchar(12);
删除表中的列
alter table table_name drop column_name ;
Author表删除国籍(hometown)列
#在Author表中删除国籍列alter table Author drop column hometown;
删除表
drop table table_name ;
删除Author表
#删除表没有确认,也不能撤销,执行后将直接删除表drop table Author;
重命名表
rename table table_nameA to table_nameB;
将Author表重命名为ITester_Authors
rename table Author to ITester_Authors;rename table ITester_Authors to Author;
表中插入数据
一次插入多条完整的行(不指定列名)
insert into table_name values(values_1,...,values_n);
在Author表中插入一条数据
insert into Author values(2019001,'coco','女','深圳','3593721069','Cc2015123',SYSDATE()),
一次多条插入完整的行(指定列名)
insert into table_name values(values_1,...,values_n,
values_2,...,values_m,
);
在Article表中一次插入多条数据(执行速度快)
insert into Article(ar_id,type,author,au_id,articles,qq_group,fans,update_date)values('1','Selenium','vivi','2019002','20','727998947','300','2019-11-01 11:11:11'),('2','MySQL','coco','2019001','10','727998947','400',SYSDATE()),('3','Python','小锦鲤','2019003','50','604469740','450',SYSDATE()),('4','Java','小胖虎','2019004','60','727998947','450','2019-11-30 23:59:59');
批量插入多条完整的行(指定列名)
insert into table_name values(values_1,...,values_n);
insert into table_name values(values_2,...,values_m);
在Article表中一次插入多行数据(执行速度较慢)
insert into Article(type,author,au_id,articles,qq_group,fans,update_date)values('APP','小锦鲤','2019003','21','727998947','350',SYSDATE()),('SQL Server','coco','2019001','30','727998947','200',SYSDATE()); insert into Article(type,author,au_id,articles,qq_group,fans)values('Oracle','coco','2019001','25','727998947','210');
创建ITester_Author表,并将Author表中的数据插入到ITester_Author表create table table_name (column_name column_type);
insert into table_nameA( column_name1, ..., column_namex)
select column_name1, ..., column_namex from table_nameB;
#创建表drop table if exists ITester_Author;create table ITester_Author( au_id int not null PRIMARY key auto_increment comment '主键', name varchar(12) comment '姓名', sex varchar(12), address varchar(12), qq varchar(12), wechat varchar(12), create_date datetime)DEFAULT CHARSET=utf8; #将Author表中的所有数据插入到ITester_Authorinsert into ITester_Author( au_id, name, sex, address, qq, wechat, create_date)select au_id, name, sex, address, qq, wechat, create_datefrom Author;
MySQL之
更新表中数据
更新一个列
update table_name set column_nameA='column_value1' where column_nameB='column_value2';
将Author表中coco的创建时间改为2019-12-12 12:12:12
update Author set create_date='2019-12-12 12:12:12' where name='coco';
更新多个列
将Author表中coco的创建时间改为2020-01-01 00:00:00,地址改为shenzhenupdate table_name set column_nameA='column_value1', column_nameB='column_value2' where column_nameC='column_value3';
#更新多个列时,只需要使用单个SET命令,每个“列=值”对之间用逗号分隔(最后一列之后不用逗号)update Author set create_date='2020-01-01 00:00:00',address='shenzhen' where name='coco';
更新列为空值
将Author表中coco的地址改为空update table_name set column_nameA=null where column_nameB='column_value';
#为了删除某个列的值,可设置它为NULL(假如表定义允许NULL值)update Author set address=NULL where name='coco';
更新时加IGNORE关键字
将Author表中coco的地址改为深圳,即使发生错误也继续执行更新update ignore table_name set column_nameA='column_value1' where column_nameB='column_value2';
#IGNORE关键字:即使是发生错误,也继续进行更新update IGNORE Author set address='深圳' where name='coco';
MySQL之
删除表中数据
删除表中数据
将Author表中名字为Tester_A的记录删除delete from table_name where column_name='column_value' ;
delete from Author where name='Tester_A';
删除表中所有数据
将ITester_Author表中所有记录删除delete from table_name;
delete from ITester_Author;
附:Author表Article表ArticleDetail表
最后是今天的分享:Author、Article、ArticleDetail三张表一键建表SQL语句