目录
SQL(Struted Query Language): 结构化查询语言,是用来连接和操作RDBMS的标准计算机语言
ER(Entity RelationShip):实体关系图,用来描述业务实体数据之间的关系。
三个图形:矩形(实体类型)、椭圆形(属性名)、菱形(关系)、连线(几对几的关系)
SQL语言分类:
DDL:数据定义语言,一般用来对数据库表进行结构调整的,比如Create、Drop、Alter
DML:数据操作语言,对数据进行增删改查操作,比如Insert、Delete、Update、Select
DCL: 数据控制语言, grant、commit、rollback
ACID:
A (Atomictiy): 原子性
C(Consistency): 一致性
I (ISolation): 隔离性, 由事务隔离级别决定隔离性影响的大小
D(Durability): 持久性
PRIMARY KEY: 主键ID
FOREIGN KEY:互联网公司极少用物理外键,用逻辑外键
INDEX:索引 (主键索引、普通索引、唯一索引)
3. SQL之DML语句
insert into wm_channel values (9,"Scala","新型编程语言",0,1,9,"2022-06-27 12:00:00");
insert into wm_channel values (10,".NET","微软件编程语言",0,0,10,"2022-06-27 13:00:00"),(11,"C++","很好的编程语言",0,1,11,"2022-06-27 14:00:00");
delete from wm_channel where id=11;
c++
,描述改为不错的编程语言
update wm_channel set name="c++",description="不错的编程语言" where id=10;
select * from wm_channel where name = "java";
与查询:查询名称为java且状态为1的频道
select * from wm_channel where name = "java" and status = 1;
或查询:查询名称为java或状态为0的频道
select * from wm_channel where name = "java" or status = 0;
非查询:查询状态不为1的频道
select * from wm_channel where status != 1;
框架
的频道select * from wm_channel where wm_channel.description like "%框架%";
区间查询1:查询序号为6和7的频道
select * from wm_channel where ord in (6,7);
区间查询2:查询序号大于5的频道
select * from wm_channel where ord > 5;
区间查询3:查询时间大于2022-06-24 00:00:00
的频道
select * from wm_channel where created_time > "2022-06-24 00:00:00";
区间查询4:查询时间大于等于2022-06-22 00:00:00
且小于等于2022-06-24 00:00:00
的频道
select * from wm_channel where created_time between "2021-06-22 00:00:00" and "2021-06-24 00:00:00";
查询文章标题、所属频道名、文章状态、文章创建时间
select wn.title,wc.name channelName,wn.status,wn.created_time from wm_news wn,wm_channel wc where wn.channel_id = wc.id;
select wn.title,wc.name channelName,wn.status,wn.created_time from wm_news wn left join wm_channel wc on wn.channel_id = wc.id;
查询频道名称为java和Python的所有文章(仅需查询文章标题、频道id、创建时间)
select title,channel_id,created_time from wm_news where channel_id in (select id from wm_channel where name in ( 'java','Python'))
分页查询
select * from wm_channel limit 1,3;
select * from wm_channel limit 4;
排序查询:查询文章标题、发布时间按照发布时间倒排序
select title,publish_time from wm_news order by publish_time desc ;
select min(ord) from wm_channel;
select max(ord) from wm_channel;
select avg(ord) from wm_channel;
select sum(ord) from wm_channel;
select date_format(publish_time,"%Y-%m-%d") dateStr,count(*) dateCount from wm_news group by dateStr having dateCount>2 order by dateCount desc limit 5;