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;