MySQL基础知识

MySQL中的一些数据语言

DDL数据定义语言

用来定义数据库对象，库，表，列等。如 create alter drop

DML数据操作语言

用来定义数据库记录（数据）:insert update delete

DCL数据控制语言

用来定义访问权限和安全级别

DQL数据查询语言

用来查询记录（数据）。select

DDL数据定义语言

#创建数据库，mydb3 设置字符编码为 gbk 排序规则为gbk_chinese_ci
create database mydb3 character set gbk collate gbk_chinese_ci;

#显示所有数据库
show databases;

#显示已经创建名为mydb1的数据库的信息
show create database mydb1;

#删除名为mydb3的数据库
drop database mydb3;

#查看服务器中的数据库，并把mydb2的字符集修改为utf8;
alter database mydb2 character set utf8;

#查看当前操作的数据库
select database();

#创建员工表
create table employee(
id int,
name varchar(20),
gender bool,
birthday date,
entry_date date,
job varchar(20),
salary float,
resume text
);

#查看数据库中的表
show tables;

#查看表employee的字段信息
desc employee;

#在employee表增加一个image列 blob代表二进制类型
alter table employee add image blob;

#修改employee表中的job字段的 数据类型为varchar(60)
alter table employee modify job varchar(60);

#删除employee表中的image字段
alter table employee drop image;

#修改employee表名为user
rename table employee to user;

#显示所创建表user的信息
show create table user;

#修改user表的字符集为gbk
alter table user character set gbk;

#修改user表的列名name为username 并讲其类型变为varchar(100)
alter table user change name username varchar(100);

#删除user表
drop table user;

#在mysql中，字符串类型和日期类型都要用单引号括起来
例如 ‘tom’ ‘2015-09-04’

DML数据操作语言

插入操作：INSERT

语法： INSERT INTO 表名（列名1，列名2 …）VALUES(列值1，列值2…);
注意：列名与列值的类型、个数、顺序要一一对应。
可以把列名当做java中的形参，把列值当做实参。
值不要超出列定义的长度。
如果插入空值，请使用null
插入的日期和字符一样，都使用引号括起来。
创建一张表
表名：emp
表结构

#插入一条数据：
insert into emp(id,name,gender,birthday,salary,job,entry_date,resume) values
(1,‘Echo’,‘male’,‘1996-10-01’,10000.99,‘Java kaifa’,‘2018-03-12’,‘帅的一马屁’);

#插入多条数据
insert into emp(id,name,gender,birthday,salary,job,entry_date,resume) values
(2,‘Zha’,‘male’,‘1998-01-05’,10000.89,‘PHP kaifa’,‘2018-03-12’,‘帅的两马屁’),
(3,‘Na’,‘female’,‘1997-03-15’,8880.89,‘HTML kaifa’,‘2018-03-18’,‘美的两马屁’),
(4,‘lzr’,‘male’,‘1998-01-05’,10000.89,‘PHP kaifa’,‘2018-03-12’,‘帅的两马屁’),
(5,‘zac’,‘male’,‘1998-01-05’,10000.89,‘PHP kaifa’,‘2018-03-12’,‘帅的两马屁’),
(6,‘zwy’,‘male’,‘1998-01-05’,10000.89,‘PHP kaifa’,‘2018-03-12’,‘帅的两马屁’);

修改操作 UPDATE

语法：UPDATE 表名 SET 列名1=列值1，列名2=列值2 。。。 WHERE 列名=值

#修改emp中的所有员工薪水为5000
update emp set salary=5000;

#修改emp中的名为Echo的员工薪水为99999.99
update emp set salary=99999.99 where name=‘Echo’;

#修改emp中的名为lzr的运功薪水为1000.77 工作为 C工程师
update emp set salary=1000.77,job=‘C工程师’ where name=‘lzr’;

#在emp中修改zac的工资 在原有的基础上加1000
update emp set salary=1000+salary where name=‘zac’;

删除操作 DELETE

语法 ： DELETE FROM 表名 【WHERE 列名=值】

#删除emp表中名称为’Zha’的记录。
delete from emp where name=‘Zha’;

#删除emp表中所有记录。
delete from emp;

#使用truncate删除emp表中记录。
truncate table emp;

DELETE 删除表中的数据，表结构还在;删除后的数据可以找回。
TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快。

DQL数据查询语言

查询关键字：SELECT 语法

查询关键字：SELECT 语法
SELECT selection_list /要查询的列名称/
FROM table_list /要查询的表名称/
WHERE condition /行条件/
GROUP BY grouping_columns /对结果分组/
HAVING condition /分组后的行条件/
ORDER BY sorting_columns /对结果排序/
LIMIT offset_start, row_count /结果限定/
顺序不能改变 HAVING 不能放在 GROUP 前面

#创建学生表
CREATE TABLE stu (
sid CHAR(6),
sname VARCHAR(50),
age INT,
gender VARCHAR(6)
);
表结构

#插入一条数据到stu表
INSERT INTO stu(sid,sname,age,gender) VALUES
(‘30001’,‘刘三姐’,20,‘female’);

#查询stu的全部内容
SELECT * FROM stu;

#插入一条数据到stu表,不一定要插入全部属性
INSERT INTO stu(sid,sname,gender) VALUES
(‘30001’,‘刘三姐’,‘female’);

#插入一条数据到stu表，如果要在每一个列都插入数据 可以省略列名
INSERT INTO stu VALUES
(‘30003’,‘刘五姐’,35,‘female’);

#初始化一些数据
INSERT INTO stu VALUES(‘S_1001’, ‘liuYi’, 35, ‘male’);
INSERT INTO stu VALUES(‘S_1002’, ‘chenEr’, 15, ‘female’);
INSERT INTO stu VALUES(‘S_1003’, ‘zhangSan’, 95, ‘male’);
INSERT INTO stu VALUES(‘S_1004’, ‘liSi’, 65, ‘female’);
INSERT INTO stu VALUES(‘S_1005’, ‘wangWu’, 55, ‘male’);
INSERT INTO stu VALUES(‘S_1006’, ‘zhaoLiu’, 75, ‘female’);
INSERT INTO stu VALUES(‘S_1007’, ‘sunQi’, 25, ‘male’);
INSERT INTO stu VALUES(‘S_1008’, ‘zhouBa’, 45, ‘female’);
INSERT INTO stu VALUES(‘S_1009’, ‘wuJiu’, 85, ‘male’);
INSERT INTO stu VALUES(‘S_1010’, ‘zhengShi’, 5, ‘female’);
INSERT INTO stu VALUES(‘S_1011’, ‘xxx’, NULL, NULL);
INSERT INTO stu VALUES(‘S_1009’, ‘wuJiu’, 85, ‘male’);
INSERT INTO stu VALUES(‘S_1010’, ‘zhengShi’, 5, ‘female’);
INSERT INTO stu VALUES(‘S_1011’, ‘xxx’, NULL, NULL);

#创建一个员工表并初始化一些数据
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
deptno INT
);
INSERT INTO emp values(7369,‘SMITH’,‘CLERK’,7902,‘1980-12-17’,800,NULL,20);
INSERT INTO emp values(7499,‘ALLEN’,‘SALESMAN’,7698,‘1981-02-20’,1600,300,30);
INSERT INTO emp values(7521,‘WARD’,‘SALESMAN’,7698,‘1981-02-22’,1250,500,30);
INSERT INTO emp values(7566,‘JONES’,‘MANAGER’,7839,‘1981-04-02’,2975,NULL,20);
INSERT INTO emp values(7654,‘MARTIN’,‘SALESMAN’,7698,‘1981-09-28’,1250,1400,30);
INSERT INTO emp values(7698,‘BLAKE’,‘MANAGER’,7839,‘1981-05-01’,2850,NULL,30);
INSERT INTO emp values(7782,‘CLARK’,‘MANAGER’,7839,‘1981-06-09’,2450,NULL,10);
INSERT INTO emp values(7788,‘SCOTT’,‘ANALYST’,7566,‘1987-04-19’,3000,NULL,20);
INSERT INTO emp values(7839,‘KING’,‘PRESIDENT’,NULL,‘1981-11-17’,5000,NULL,10);
INSERT INTO emp values(7844,‘TURNER’,‘SALESMAN’,7698,‘1981-09-08’,1500,0,30);
INSERT INTO emp values(7876,‘ADAMS’,‘CLERK’,7788,‘1987-05-23’,1100,NULL,20);
INSERT INTO emp values(7900,‘JAMES’,‘CLERK’,7698,‘1981-12-03’,950,NULL,30);
INSERT INTO emp values(7902,‘FORD’,‘ANALYST’,7566,‘1981-12-03’,3000,NULL,20);
INSERT INTO emp values(7934,‘MILLER’,‘CLERK’,7782,‘1982-01-23’,1300,NULL,10);

#查询表的部分列 从stu中只查询sid 和 sname
SELECT sid,sname from stu;

条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
=、!=、<>、<、<=、>、>=；
BETWEEN…AND；
IN(set)；
IS NULL； IS NOT NULL
AND；
OR；
NOT；

#在stu中查询性别为女且年龄为50的记录
select * from stu where gender=‘female’ and age=50;

#在stu中查询性别为女且年龄大于50的记录
select * from stu where gender=‘female’ and age>50;

#在stud中查询学号为S_1001 或姓名为liSi的记录
select * from stu where sid=‘S_1001’ and sname=‘liSi’;

#查询学号为S_1001,S_1002,S_1003的记录 in关键字 范围查找
select * from stu where sid in (‘S_1001’,‘S_1002’,‘S_1003’);

#Navicat的SQL文件的保存路径# C:\Users\Echo\Documents\Navicat\MySQL\servers\mysql\mydb1\sqlchaxun.sql

#在stu中查询学号不是S_1001,S_1002,S_1003的记录
select * from stu where sid not in (‘S_1001’,‘S_1002’,‘S_1003’)

#在stu中查询年龄在20到40之间的学生
select * from stu where age >= 20 and age <= 40;
select * from stu where age between 20 and 40;

#在stu中查询性别非男性的学生
select * from stu where gender = “female”;
select * from stu where gender != “male”;
#<>也代表不等于
select * from stu where gender <> “male”;

#在stu中查询姓名不为null的学生
select * from stu where sname is not null;
select * from stu where not sname is null;

模糊查询

当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。
通配符:
_ :任意一个字符
% :任意0~n个字符
‘%张%’ ‘张_’

#LIKE模糊查询 匹配一个字符
#在stu表中查询姓名由4个字母组成的学生记录 "_____"是四个下划线
select * from stu where sname like '___’

#在stu中查询姓名由5个字母组成的学生记录，并且第5个字母为"i"的记录
select * from stu where sname like ‘____i’

#在stu中查询姓名第二个字母为i的学生记录
select * from stu where sname like ‘_i%’;

#在stu中查询以"z"为开头的学生记录 %匹配n个字符
select * from stu where sname like ‘z%’;

#查询姓名中包含 a 字母的学生的记录
select * from stu where sname like ‘%a%’;

去重，求和，别名，排序

#去除重复记录使用DISTINCT
#查询员工工资，去除重复的工资
select distinct sal from emp;

#查询员工工资和姓名。并且去除重复工资
#注意 不能使用这种方法，因为想要去重的列使用distinct后 再与
#其他列混用 会使的distinct无效 此句的distinct无效 等价于 select sal,ename from emp;
#所以想去重查哪一列 只查那一列即可
select distinct sal,ename from emp;

#查询员工的月薪和佣金之和（两列的数值求和）
#会在查找全部列基础上增加一个 sal+comm列 保存月薪与佣金之和
#但注意 这是错误的 如果 sal 或者 comm中某一列 为null 那么
#生成的 sal + comm也会为空 在二者都不为空的情况下是可以的
select *,sal + comm from emp

#正确方法 如果字段为空 则设置值为0之后再相加
select *,ifnull(sal,0) + ifnull(comm,0) from emp;

#给列添加别名 as 关键字
select *, ifnull(sal,0) + ifnull(comm,0) as ‘total’ from emp;

#可以省略as关键字
select *, ifnull(sal,0) + ifnull(comm,0) ‘total’ from emp;

#查询部分字段 并重命名 但一般开发中不会取 中文名
select ename ‘名字’,job ‘工作’,sal ‘薪水’,comm ‘提成’ from emp;

#排序 order by 列名 asc(默认) 升序 desc降序

#查询所有的学生记录并按照升序排序 默认是按升序排序的
select * from stu order by age;
select * from stu order by age asc;

#查询所有的学生记录并按照降序排序
select * from stu order by age desc;

#查询所有的员工，按月薪降序排序，月薪相同时，按照编号升序排序
select * from emp order by sal desc,empno asc;

聚合函数

聚合函数是用来做纵向运算的函数：
COUNT()：统计指定列不为NULL的记录行数；
MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

count函数

#查询emp表中的记录数
select count(*) from emp;

#查询表中有佣金的人数
select count(comm) from emp;

#查询表中有佣金的人数 同时 去掉不等于0 的
select count(comm) from emp where comm != 0;

#查询表中月薪与佣金之和大于2500的人数
select count(*) from emp where ifnull(sal,0) + ifnull(comm,0) > 2500;

#查询有佣金的且有领导的人数
select count(comm) from emp where mgr is not null;

SUM和AVG

#查询所有雇员月薪和
select sum(sal) from emp;

#查询所有员工的月薪之和与佣金之和
select sum(sal),sum(comm) from emp;

#查询所有员工的月薪和佣金之和
select sum(sal)+sum(comm) as total from emp;
select sum(ifnull(sal,0) + ifnull(comm,0)) as total from emp;

#统计所有员工的平均工资
select avg(sal) from emp;

MAX MIN

#查询员工的最大工资 和最小工资
select max(sal),min(sal) from emp;

分组查询

#查询每个部门的部门编号和每个部门的工资和：
#按部门编号分组。此时的求和是按照部门求和的
select deptno,sum(sal) from emp group by deptno;

#查询每个部门的编号以及每个部门的人数
select deptno,count(*) from emp GROUP BY deptno;

#查询每个部门的部门编号以及每个部门工资大于1500的人数
select deptno,count(*) from emp where sal > 1500 group by deptno;

HAVING子句
having是在分组后对数据进行过滤.
where是在分组前对数据进行过滤

having后面可以使用聚合函数(统计函数)
where后面不可以使用聚合函数

WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件
，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

#查询工资总和大于9000的部门编号以及工资和
select deptno,sum(sal) from emp group by deptno having sum(sal) > 9000;

LIMIT

方言 MySQL 所独有的
LIMIT用来限定查询结果的起始行，以及总行数。

数据的完整性

作用：保证用户输入的数据保存到数据库中是正确的。
确保数据的完整性 = 在创建表时给表中添加约束
完整性的分类：
> 实体完整性
> 域完整性
> 引用完整性

实体完整性

实体：即表中的一行(一条记录)代表一个实体（entity）
实体完整性的作用：标识每一行数据不重复。
约束类型： 主键约束（primary key） 唯一约束(unique) 自动增长列(auto_increment)

主键约束（primary key）

特点：数据唯一，且不能为null
创建一个学生表
create table student(
id int,
name varchar(20)
);
不进行任何约束 会出现重复数据或空值

#对id字段进行主键约束，数据唯一 且不能重复，不能为null
create table student(
id int primary key,
name varchar(20)
);

#另外一种方式添加主键
#所有字段声明之后添加主键
create table student(
id int,
name varchar(20),
primary key(id)
);

#第三种添加主键的方式，在表创建完毕后添加
create table student(
id int,
name varchar(20)
);
alter table student add primary key(id);

#联合主键 id 和 name 同时为主键 两个字段加起来唯一
create table student(
id int,
name varchar(20)，
primary key(id,name)
);

唯一约束 unique

create table student(
id int primary key,
# 代表名字不能重复
name varchar(20) unique
);

自动增长列(auto_increment)
给主键添加自动增长的数值，列只能是整数类型，但是如果删除之前增长的序号，
后面再添加的时候序号不会重新开始，而是会接着被删除的那一列的序号
例如，删除3号数据 继续插入一条数据时 序号会从4开始而不是3 但是可以
手动自己添加进去

create table student(
id int primary key auto_increment,
name varchar(20) unique
);
#设置为自动递增之后 在插入操作时不需要填id的值也行
insert into student(name) values (‘mayun’);
insert into student(name) values (‘mahuateng’);
insert into student(name) values (‘liyanhong’);

外键约束 FOREIGN KEY

#创建两张表 一张为 student 一张为 score
create table student(
id int primary key auto_increment,
name varchar(20) not null,
gender char(1) default ‘男’
);
insert into student(name) values (‘张三’);
insert into student(name,gender) values(‘李小莲’,‘女’);

#创建成绩表
#添加外键的第一种方式 创建表时添加
#CONSTRAINT 外键名 FOREIGN KEY (外键字段) REFERENCES 表名(主键)
create table score(
id int primary key auto_increment,
score int,
name varchar(20) not null,
sid int,
constraint fk_student_score foreign key(sid) references student(id)
);
insert into score (score,name,sid)
values
(90,‘数学’,1),
(93,‘数学’,2);

#添加外键的第二种方式
ALTER TABLE score1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES student(id);

#外键约束，当插入数据时，先检测关联的student表中有没有相应的学生id
#有则插入 无则报错

表与表之间的关系一对一：

例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，
即谁是主表，谁是从表。人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。
设计从表可以有两种方案：
在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束；
给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。

一对多（多对一）：
最为常见的就是一对多！一对多和多对一，这是从哪个角度去看得出来的。t_user和t_section【部门】的关系，
从t_user来看就是一对多，而从t_section的角度来看就是多对一！这种情况都是在多方创建外键！

多对多：
例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。
这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表，给出两个外键，
一个相对t_stu表的外键，另一个相对t_teacher表的外键。

多表查询

多表查询有如下几种：
合并结果集；UNION 、UNION ALL
连接查询
内连接 [INNER] JOIN ON
外连接 OUTER JOIN ON
左外连接 LEFT [OUTER] JOIN
右外连接 RIGHT [OUTER] JOIN
全外连接（MySQL不支持）FULL JOIN
自然连接 NATURAL JOIN
子查询

合并结果集

作用：合并结果集就是把两个select语句的查询结果合并到一起！
合并结果集有两种方式：
UNION：去除重复记录，例如：SELECT * FROM t1 UNION SELECT * FROM t2；
UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。
要求：被合并的两个结果：列数、列类型必须相同

#创建两张表
create table employee_china(
id int,
name varchar(50)
);
create table employee_usa(
id int,
name varchar(50)
);

#查询中国员工
select * from employee_china;

#查询美国员工
select * from employee_usa;

#使用union 和 union all 的前提时两张表要有重复字段
#合并两张表 union 会去除重复的
select * from employee_china union select * from employee_usa;
#union all 不会去除重复的
select * from employee_china union all select * from employee_usa;

连接查询

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。
SELECT * FROM employee_china,employee_usa;
连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，
则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。
可以扩展到多个集合的情况。那么多表查询产生这样的结果并不是我们想要的，
那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。
通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

#连接查询，笛卡尔积
select * from employee_china,employee_usa;

#笛卡尔积的过滤 选择出每位员工所对应的部门
select * from employee as e,department as d where e.depno = d.id;
select e.id as ‘员工id’,e.name as ‘员工姓名’ ,e.depno as ‘员工部门编号’,d.name as ‘部门名称’ from employee as e,department as d where e.depno = d.id;

内连接

#建表填数据
CREATE TABLE department(
id int,
name varchar(50)
);

INSERT INTO department VALUE(10001,‘销售部’);
INSERT INTO department VALUE(10002,‘咨询部’);
INSERT INTO department VALUE(10003,‘人事部’);
INSERT INTO department VALUE(10004,‘技术部’);

CREATE TABLE employee(
id int,
name varchar(50),
depno int
);

INSERT INTO employee VALUES(1,‘tony’,10001);
INSERT INTO employee VALUES(2,‘lucy’,10001);
INSERT INTO employee VALUES(3,‘mia’,10001);

INSERT INTO employee VALUES(4,‘amy’,10002);
INSERT INTO employee VALUES(5,‘jerry’,10002);

INSERT INTO employee VALUES(6,‘micheal’,10003);
INSERT INTO employee VALUES(7,‘lily’,10003);

INSERT INTO employee VALUES(8,‘elain’,10004);
INSERT INTO employee VALUES(9,‘ruly’,10004);
INSERT INTO employee VALUES(10,‘kk’,10004);
INSERT INTO employee VALUES(11,‘cici’,10004);

#查询员工以及其所在部门
#查询时，需要什么数据就搜索什么数据，以免造成内存浪费
select e.id,e.name,e.depno,d.name from employee e, department d where e.depno = d.id;
#上面的写法就是内连接，理解成MySql的方言，也就是在其他数据库 不一定能这样

#标准的内连接 工作中用这种写法
select * from employee e inner join department d on e.depno = d.id;

外连接

#先写入一些数据
INSERT INTO department VALUE(10005,‘保洁部’);
INSERT INTO employee (id,name) VALUES(12,‘ela’);

左连接

#左连接 查询员工以及其所在部门
#是先查询出左表（即以左表为主），
#然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。
#而上面内连接，不满足条件的不显示
select e.id,e.name,e.depno,d.name
from employee e left join department d on e.depno = d.id;

右连接

#右连接就是先把右表中所有记录都查询出来，
#然后左表满足条件的显示，不满足显示NULL。
select e.id,e.name,e.depno,d.name
from employee e right join department d on e.depno = d.id;

连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。
通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。
这个条件大多数情况下都是使用主外键关系去除。
两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，
所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。
如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。

自连接
自己连接自己 起别名

#求7369员工编号，姓名，经理编号和经理姓名
select e1.empno ‘员工编号’ ,e1.ename,e1.mgr ‘经理编号’,e2.ename ‘经理姓名’
from emp e1,emp e2
where e1.empno = 7369 and e1.mgr = e2.empno;
#求每一个员工的上级
select e1.empno ‘员工编号’ ,e1.ename,e1.mgr ‘经理编号’,e2.ename ‘经理姓名’
from emp e1,emp e2
where e1.mgr = e2.empno;

自然连接

用添加主外键约束就可以去除笛卡尔积无用项
连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式：
两张连接的表中名称和类型完全一致的列作为条件，例如emp和dept表都存在deptno列，并且类型一致，所以会被自然连接找到！
当然自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！
创建两张表 插入一些数据
CREATE TABLE t_department(
depno varchar(10),
depname varchar(50),
primary key(depno)
);

CREATE TABLE t_employee(
eid int,
ename varchar(50),
depno varchar(10),
foreign key(depno) references t_department(depno)
);

insert into t_department values(‘10’,‘财务’);
insert into t_department values(‘20’,‘市场’);
insert into t_department values(‘30’,‘Java研发部’);
insert into t_department values(‘40’,‘PHP研发部’);
insert into t_employee values(1,‘gyf’,‘10’);
insert into t_employee values(2,‘ljl’,‘20’);
insert into t_employee values(3,‘hg’,‘30’);

#不需要等式去除多余的笛卡尔积
select * from t_employee e natural join t_department d;

#左自然连接
select * from t_employee e natural left join t_department d;
#右自然连接
select * from t_employee e natural right join t_department d;

子查询

子查询概念：一个select语句中包含另一个完整的select语句。
子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，
那么就是子查询语句了。
子查询出现的位置：
where后，作为查询条件的一部分；
from后，作表；
当子查询出现在where后作为条件时，还可以使用如下关键字：any，all
子查询结果集的形式：
单行单列（用于条件）单行多列（用于条件）多行单列（用于条件）多行多列（用于表）
#查找工资高于JONES的员工
#1.先找JONES的工资
select sal from emp where ename = “JONES” #单行单列，用于条件
#2.依据此条件 嵌套查找
select * from emp where sal > (select sal from emp where ename = “JONES”)

#查询与SCOTT同一个部门的员工
select * from emp where depno = (select depno from emp where ename=“SCOTT”)

#查询工资高于30号部门的所有员工的信息
#法1
#select MAX(sal) from emp where depno = “30”; 条件：找到30部门的最大sal 单行单列
select * from emp where sal > (select MAX(sal) from emp where = “30”);

#法2
#select sal from emp where depno = “30”; #单行多列
select * from emp where sal > all(select sal from emp where depno = “30”); #可以理解为内部执行最大值操作

#查询工作与工资都和MARTIN完全相同的员工信息
#select job,sal from emp where ename=“MARTIN”; # 单行多列 子查询

select * from emp where (job,sal) in (select job,sal from emp where ename=“MARTIN”);

#查询有两个以上直接下属的员工信息，(查询经理信息，并且该经理有两个以上的下属)
#查询所有的经理 并根据经理分组 统计
#select mgr,count(mgr) from emp group by(mgr);
#通过having 进行过滤
#select mgr,count(mgr) from emp where mgr is not null group by(mgr) having count(mgr) > 2;
select * from emp t1,(select mgr,count(mgr) from emp group by(mgr) having count(mgr) >= 2) t2
where t1.empno = t2.mgr;

#求各个部门薪水最高的员工所有信息
#先求每个部门最高的工资 select MAX(sal),depno from emp GROUP BY(depno);
#法1
select * from emp where sal in (select MAX(sal) from emp GROUP BY(depno));
#法2
select * from emp t1,(select depno,max(sal) maxsal from emp group by(depno)) t2
where t1.sal = t2.maxsal

MySql中的一些函数

日期函数

#将后面的 1:12:20 加到 12：00：00 上 addtime()是在时分秒上加
select addtime(‘2020-01-08 12:00:00’,‘1:12:20’)

#创建一个学生表
create table student(
id int primary key auto_increment,
#如果字符超过255就要用text文本类型
name varchar(10),
entry_date date
);
insert into student values(1,‘mayun’,‘2013-02-12 11:30:30’);
#如果是date类型，只保存年月日
select entry_date from student;
#在student表中选择entry_date字段，给他的月份加上2 adddate()是在年月日上加
select DATE_ADD(entry_date,INTERVAL 2 month) from student;
#在年月日上减一个值
select DATE_SUB(entry_date,INTERVAL 2 year) from student;

#获取当前时间
select NOW()
#插入当前时间
insert into student values (2,‘mahuat’,NOW());

select CURRENT_DATE(); #获取当前年月日
select CURRENT_TIME(); #获取当前时分秒
select CURRENT_TIMESTAMP(); #获取当前时间戳
#两个日期的差
select DATEDIFF(‘2018-03-15’,‘2018-03-16’)

字符串函数

#字符串拼接
select CONCAT(“xing”,“ming”);
#返回拼接的姓名和日期
select concat(name,entry_date) from student;

#instr 查找位置 注意下标是从1开始的
select instr(‘daydayup’,‘up’);

#ucase 转大写
select ucase(name) from student;

#lcase 转小写
select lcase(name) from student;

#获取长度 length(str)
select length(name) from student;

#替换字符串replace(源字符串,被替换对象,新替换的串)
select replace(‘MHT’,‘HT’,‘DM’);

#比较两个字符串大小
#0 代表相等 1 代表左边字符串大 -1 代表右边字符串大
select strcmp(‘aaa’,‘abb’)

#字符串截取 substr(str FROM pos FOR len)
select substr(‘IloveU’,2,5)

数学函数

#取绝对值
select abs(-11);

#转换二进制
select bin(8);

#ceiling(number) 向上取整
select CEILING(1.3)

#向下取整
select floor(1.3)

#保留两位小数的位数 会四舍五入
select format(1.1415,2)

#转16进制
select hex(15);

#求最小值
select least(40,30,42)

#求余
select mod(10,4)

#随机数
select rand(1534);