【MySQL】数据库复习——SQL
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
目录
1. 准备工作
-
将SQL文件导入到本地数据库今日主要设计两张表wm_channel、wm_news
-
熟悉业务
2. 专业术语
SQLStruted Query Language: 结构化查询语言是用来连接和操作RDBMS的标准计算机语言
EREntity RelationShip实体关系图用来描述业务实体数据之间的关系。
三个图形矩形实体类型、椭圆形属性名、菱形关系、连线几对几的关系
SQL语言分类
DDL数据定义语言一般用来对数据库表进行结构调整的比如Create、Drop、Alter
DML数据操作语言对数据进行增删改查操作比如Insert、Delete、Update、Select
DCL 数据控制语言, grant、commit、rollback
ACID
A (Atomictiy): 原子性
CConsistency: 一致性
I ISolation: 隔离性 由事务隔离级别决定隔离性影响的大小
DDurability: 持久性
PRIMARY KEY 主键ID
FOREIGN KEY互联网公司极少用物理外键用逻辑外键
INDEX索引 主键索引、普通索引、唯一索引
3. SQL之DML语句
3.1 增删改
- 插入一条 插入一条频道
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");- 删除ID为11的频道
delete from wm_channel where id=11;- 修改ID为10的频道名称为
c++描述改为不错的编程语言
update wm_channel set name="c++",description="不错的编程语言" where id=10;3.2 单表查询
- 条件查询查询频道名为java的频道
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";3.3 多表关联查询
查询文章标题、所属频道名、文章状态、文章创建时间
-
写法一- 直接FROM两张表
select wn.title,wc.name channelName,wn.status,wn.created_time from wm_news wn,wm_channel wc where wn.channel_id = wc.id;- 写法二- JOIN两张表
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;3.4 子查询
查询频道名称为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'))3.5 分页排序
分页查询
-
分页查询1从第2条开始查询3条频道
select * from wm_channel limit 1,3;- 分页查询2查询前4条频道
select * from wm_channel limit 4;排序查询查询文章标题、发布时间按照发布时间倒排序
select title,publish_time from wm_news order by publish_time desc ;3.6 聚合查询
- 查询频道序号最小值
select min(ord) from wm_channel;- 查询频道序号最大值
select max(ord) from wm_channel;- 查询频道序号平均值
select avg(ord) from wm_channel;- 查询频道序号之和
select sum(ord) from wm_channel;- 查询每天对应的已发布的文章数量只查询发布数量大于2的记录按数量倒排序取前5条
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;