数据准备:
–建表
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;
select 列名 from 表名 where 列名 like”%R%”; —–“%”代表0个或多个任意字符
select 列名 from 表名 where 列名 like”R____”; ——“_”代表一个任意字符
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);
Original: https://www.cnblogs.com/cn-zhouchao/p/16487644.html
Author: 小胖子学编程
Title: Mysql的知识梳理
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/621010/
转载文章受原作者版权保护。转载请注明原作者出处！