如何全面梳理并掌握MySQL数据库的安装、配置、优化与安全等核心知识？

本文共计848个文字，预计阅读时间需要4分钟。

数据准备：创建表customer_jia和company_jia

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

数据准备:

--建表

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