大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说mysql知识点梳理_数学知识,希望您对编程的造诣更进一步.
数据准备:
–建表
create table customer_jia(CID int(4),
Cname varchar(20),
Csex varchar(2),
Cage int(3),
Cjob varchar(20),
CCNO int(2)
);
create table company_jia(CCNO int(4),
CCname varchar(20),
Cvalue int(3),
CADD varchar(20)
);
新增数据
insert into customer_jia values("1002","马化腾","男","49","CEO","02");
insert into customer_jia values("1003","黄铮","男","38","CEO","03");
insert into customer_jia values("1004","李彦宏","男","52","CEO","04");
insert into customer_jia values("1005","刘强东","男","55","CEO","05");
insert into customer_jia values("1006","张小龙","男","50","CFO","02");
insert into customer_jia values("1007","任正非","男","72","董事长","06");
insert into customer_jia values("1008","张三","男","80","董事长","07");
新增数据
insert into company_jia values("2","腾讯","3885","深圳");
insert into company_jia values("3","拼多多","691","上海");
insert into company_jia values("4","百度","484","北京");
insert into company_jia values("5","京东","941","北京");
insert into company_jia values("6","华为","10000","深圳");
insert into company_jia values("7","格力","300","深圳");
— 查询数据
select * from company_jia ;
select * from customer_jia a ,company_jia b where a.ccno=b.ccno;
插入数据:
insert into 表名 (值1,值2,值3);
修改数据:
update 表名 set 列名=值 where 列名=值;
删除:
delete from customer_jia where cname=刘强东; ——-效率低，可恢复
truncate table customer_jia; ——–一次性将数据或断，效率高，不可恢复,适合大量数据删除
drop table .customer_jia ; ———-删除表的同时把数据一起删除
行询
查询:
select 列名 from 表名 where 条件语句;
select 列名 from 表名 where 条件语句 order by 列名 desc;
模糊匹配: like
select 列名 from 表名 where 列名 like”%R%”; —–“%”代表0个或多个任意字符
select 列名 from 表名 where 列名 like”R____”; ——“_”代表一个任意字符
去重查询:distinct
select distinct 列名 from 表名 where 条件语句;
子查询:
当值用语法:select 列名 from 表名 where 列名=(select 列名 from 表名 where 条件语句);
当表用语法:select 列名 from (select 列名 from 表名 where 条件语句) 表别名 where 条件语句;
分组查询:
select 列名1,列名2 where 条件语句 group by 列名 ;
分组查询过滤:
select 列名1,列名2 where 条件语句 group by 列名 having 过滤条件;
表连接:
— 内连接:
select * from customer_jia a inner join company_jia b on a.ccno=b.ccno;
— 全连接: mysql 中没有full join 只是了解
select * from customer_jia a full outer join company_jia b on a.ccno=b.ccno;
select * from customer_jia a full join company_jia b on a.ccno=b.ccno;
— 左连接:
select * from customer_jia a left join company_jia b on a.ccno=b.ccno;
— 右连接:
select * from customer_jia a right join company_jia b on a.ccno=b.ccno;
— 联合查询:union
(select * from customer_jia a left join company_jia b on a.ccno=b.ccno)
union
(select * from customer_jia a right join company_jia b on a.ccno=b.ccno);
— 联合查询:union all
(select * from customer_jia a left join company_jia b on a.ccno=b.ccno)
union all
(select * from customer_jia a right join company_jia b on a.ccno=b.ccno);
原文地址：https://www.cnblogs.com/cn-zhouchao/archive/2022/07/17/16487644.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/5003.html
