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);