Mysql基础练习记录

创建&删除数据库

Create database RUNOOB;
drop database RUNOOB;

use runoob;

创建数据表

create table if not exists BookInfo(
book_id int not null auto_increment,
name varchar(150) not null comment ‘book_name’,
author varchar(50) not null comment ‘book_author’,
create_time timestamp null default current_timestamp comment ‘创建时间’,
update_time timestamp null default current_timestamp comment ‘更新时间’,
primary key(book_id)
)engine=InnoDB default charset=utf8;

create table if not exists MusicInfo(
music_id int not null auto_increment,
name varchar(150) not null comment ‘book_name’,
author varchar(50) not null comment ‘book_author’,
create_time timestamp null default current_timestamp comment ‘创建时间’,
update_time timestamp null default current_timestamp comment ‘更新时间’,
primary key(music_id)
)engine=InnoDB default charset=utf8;

create table if not exists bookcontent(
id int not null auto_increment,
book_id int not null comment ‘绑定book_id’,
chapter varchar(50) not null comment ‘章节’,
content longtext null comment ‘章节内容’,
link text null comment ‘章节链接’,
create_time timestamp null default current_timestamp,
update_time timestamp null default current_timestamp,
primary key(id)
)engine=InnoDB default charset=utf8;

create table if not exists familyscore(
id int not null auto_increment,
name varchar(30) not null comment ‘姓名’,
math decimal not null,
chinese decimal not null,
english decimal not null,
primary key(id)
)engine=InnoDB default charset=utf8;

删除数据表

drop table BookInfo;

插入数据

insert into RUNOOB.familyscore (name, math, chinese, english) values
(‘李小侠’, 68, 88, 10),
(‘李小猫’, 95, 56, 78),
(‘李大猫’, 77, 68, 77),
(‘黎小明’, 99, 67, 79);
insert into RUNOOB.BookInfo (name, author) values
(‘红楼梦’, ‘曹雪芹’),
(‘本草纲目’, ‘李时珍’),
(‘memory’, ‘MeYouAndOur’);
insert into RUNOOB.musicinfo (name, author) values
(‘晴天’, ‘周杰伦’),
(‘第一次爱的人’, ‘王心凌’),
(‘嗷嗷战歌’, ‘李大猫’),
(‘沉默吃饭’, ‘李小猫’);

insert into runoob.bookcontent (book_id, chapter, content, link) values
(2, ‘第一章’, '服药忌食

白术、苍术忌吃桃、李及雀肉、菘菜、青鱼。

巴豆忌吃芦笋、酱、豆豉、冷水及野猪肉。

黄莲、胡黄连忌吃猪肉和冷水。

半夏、菖莆忌吃饴糖、羊肉和羊血。',‘https://www.zk120.com/ji/book/531’),
(2, ‘第二章’, '水部·露水

释名在秋露重的时候，早晨去花草间收取。

气味甘、平、无毒。

主治用以煎煮润肺杀虫的药剂，或把治疗疥癣、虫癞的散剂调成外敷药，可以增强疗效。白花露：止消渴。百花露：能令皮肤健好。柏叶露、菖蒲露：每天早晨洗眼睛，能增强视力。韭叶露：治白癜风。每天早晨涂患处’,‘http://guoxue.httpcn.com/html/book/MEUYCQCQ/CQMETBPWPWXV.shtml’);

查询数据

select name from RUNOOB.bookinfo where author = ‘李时珍’;
select content from RUNOOB.bookcontent where chapter=‘第二章’ and id=2;
select chapter, content from RUNOOB.bookcontent where book_id>=2;
select name from RUNOOB.bookinfo where author like ‘李%’;

更新数据

update RUNOOB.bookinfo set author = ‘李大猫’ where name=‘memory’;

删除数据

delete from RUNOOB.bookinfo where author=‘李大猫’;
insert into RUNOOB.musicinfo (name, author) value (“我爱吸尘器”, ‘李大猫’);

union 筛选掉重复项

select author from RUNOOB.bookinfo
union
select author from RUNOOB.musicinfo
order by author ASC ;
#union all 选出所有包括重复项
select author from RUNOOB.bookinfo
union all
select author from RUNOOB.musicinfo
order by author DESC ;

group by 根据一个或多个列对结果进行分组

select author, count() from RUNOOB.musicinfo group by author;
select author, count() from RUNOOB.musicinfo where author like ‘李%’ group by author;

with rollup实现分组统计基础上进行相同统计(sum, avg, count)

select name, sum(math) as average from RUNOOB.familyscore group by name with rollup;
select name, avg(math) as math, avg(chinese) as chinese, avg(english) as english from RUNOOB.familyscore where id <= 4 group by name with rollup;

coalesce()设置一个取代Null的名字

select coalesce(name, ‘all_average’), avg(math) from RUNOOB.familyscore group by name with rollup;

join(inner join) 取并集，用于多表查询

select b.author, b.name, m.name from RUNOOB.bookinfo b
join RUNOOB.musicinfo m on m.author=b.author
order by b.author;

仅联合两个表查询，直接使用where即可

select b.author, b.name, m.name from RUNOOB.bookinfo b, RUNOOB.musicinfo m where b.author=m.author order by b.name desc;

left join / right join

select b.author, b.name, m.name from RUNOOB.bookinfo b
left join RUNOOB.musicinfo m on m.author=b.author
order by m.name;

select m.author, b.name, m.name from RUNOOB.bookinfo b
right join RUNOOB.musicinfo m on m.author=b.author
order by m.name;

获取左边差集的数据

select b.author, b.name, m.name from RUNOOB.bookinfo b
left join
RUNOOB.musicinfo m on m.author=b.author
where m.name is null
order by m.name;

正则表达式

select b1.name, b2.chapter, b2.content, b2.link from RUNOOB.bookinfo b1, RUNOOB.bookcontent b2 where b2.book_id=b1.book_id and b2.link REGEXP ‘^https’;
select b1.name, b2.chapter, b2.content, b2.link from RUNOOB.bookinfo b1, RUNOOB.bookcontent b2 where b2.book_id=b1.book_id and b2.link REGEXP ‘http’;