MySql查询功能梳理

CREATE DATABASE CristinMysql

 

Create table employee(

eId int(9) not null auto_increment,

eName varchar(8) charset utf8 not null,

eComeDate date not null,

eGender enum('Male','Female','Uncertaion') default null,

primary key (eId)

) engine = InnoDB auto_increment = 20170001 default charset = utf8mb4;

 

--auto_increment:主键自动增加编号;

--engine = InnoDB 支持事务查询，engine = ISAM 执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错；

--utf8与utf8mb4的区别：utf8只支持3个字节，utf8mb4可支持4个字节;

 

insert into employee values(null,'张三','2015-01-01','Male'),(null,'李四','2015-01-01','Male'),(null,'王五','2014-01-01','Male'),(null,'白凤九','2016-01-01','Male'),(null,'白凤八','2017-01-01','Male'),(null,'张三丰','2015-06-01','Male')

 

--【简单查询】

select * from employee;

 

--where【精确查询】

select * from employee where eName = '张';

 

--like % _【模糊查询，%与_区别：%表示模糊位数，_表示第几位模糊】

select * from employee where eName like '张%'；

select * from employee where eName like '张_';

 

--between and < > <>【区间查询，between and 从哪里到哪里;< > 逻辑区间；<>表示不等于】

select * from employee where eComeDate between 20150101 and 20170101;

select * from employee where eComeDate >= 20160101;

 

--order by limit【排序规则，默认升序排列,limit表示取几个】

select * from employee where eComeDate >= 20150101 order by eComeDate desc limit 3;

 

-- and or【和与或】

select * from employee where eComeDate >= 20150101 and eName like '白%';

select * from employee where eComeDate <20150101 or eName like '白%';

 

-- group by regexp【group by分组查询，regexp选择性查询】

select eGender,count(eId) from employee group by eGender;

select * from employee where eName regexp '张|白';

 

--【查询函数】

select count(eId) from employee;//集合

select avg(eComeDate) from employee;//平均

select max(eComeDate) from employee;//最大

select min(eComeDate) from employee;//最小

 

--date【日期查询】

select curdate();-- 获取数据库服务器当前时间

select date_sub(curdate(),interval 1 year);-- DATE_SUB() 函数从日期减去指定的时间间隔，curdate() 参数是合法的日期表达式。interval 1 year 参数是您希望添加的时间间隔。

select * from employee where eComeDate < date_sub(curdate(), interval 1 year);

 

--disint【不同值】

select distinct(eGender) from employee; --返回唯一不同的值

 

--【连接查询】

create table company(

id int(3) not null auto_increment,

cName varchar(8) charset utf8mb4 not null,

cEId int (9) not null,

primary key(id)

)engine = InnoDB default charset = utf8;

 

show tables;

 

insert into company values (null,'张三','20170001'),(null,'梁宽','20150101'),(null,'白凤九','20170004'),(null,'梁窄','201570001');

select * from company;

 

-- 查询employee 和 company两个表中cEId都有的数据

-- 子查询

select * from company where cEId in (select eId from employee);

 

-- 连接查询（四种：交叉查询、内查询、左连接、右连接）逻辑：形成一个虚拟表，将多表查询出来之后的连查数据放在里面

-- 交叉查询

select * from company c , employee e where e.eId = c.cEId; -- 隐式的交叉查询，左边表和右边表相同记录的内容，显示出来

select * from company c , employee e where e.eId <> c.cEId; -- 不等于交叉查询,左边表乘以右边表再把相同的记录去掉，然后展示出来

select * from company c cross join employee e where e.eId = c.cEId; -- 隐式的交叉查询，左边表和右边表相同记录的内容，显示出来

 

-- 【内链接】

select * from company c inner join employee e where e.eId = c.cEId; -- 内链接

select * from company c join employee e where e.eId = c.cEId; -- 默认的内链接

 

-- 左连接，意义：左表中的数据全部查出来，如果与右表中没有匹配的就用null表示

select * from company c left join employee e on e.eId = c.cEId; -- 写法1

select * from company c left outer join employee e on e.eId = c.cEId; -- 写法2

 

-- 右连接，意义：右表中的数据全部查出来，如果与左表中没有匹配的就用null表示

select * from company c right join employee e on e.eId = c.cEId;