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