数据准备:
--建表
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/p/16487644.html
本站部分图文来源于网络,如有侵权请联系删除。