MySQL笔记
一、SQL(访问和处理数据库语言)
1.1简介
SQL(Structured Query Language)用于访问和处理数据库的标准的计算机语言
1.2语法特点
-
SQL对关键字大小写不敏感
-
SQL语句可以以单行或多行书写,以分号结尾
-
SQL的注释:
-
单行注释
- – 【–后面一定要有一个空格】
- #【#的后面可以没有空格】
例如:SELECT * FORM emp;–
-
多行注释
/* …*/
-
二、DDL(数据定义语言)
【DDL】数据定义语言,包括:
2.1对数据库的常用操作
功能 | SQL |
---|---|
查看所有的数据库 | show ; |
创建数据库 | create database[if not exists] mydb1 [charset=utf8] |
切换(选择要操作的)数据库 | use mydb1; |
删除数据库 | drop database[if exists] mydb1; |
修改数据库编码 | alter database mydb1 character set utf8; |
-- 1.DDL操作之数据库操作
#查看所有数据库
show databases;
#创建数据库,数据库名字叫mydb1
CREATE DATABASE mydb1;
-- 如果不存在该数据库则创建
CREATE DATABASE IF NOT EXISTS mydb1;
#选择使用哪一个数据库
use mydb1;
#删除数据库
drop DATABASE mydb1;
-- 如果存在该数据库则删除
drop DATABASE if exists mydb1;
#修改数据库编码为utf8
alter database mydb1 character set utf8;
2.2对表结构的常用操作
2.2.1创建表
- 格式:
#创建表是构建一张空表,指定这个表的名字,这表有几列,每一列叫什么名字,以及每一列存储的数据类型
create table [if no exists]表名(
字段名1 类型 [(宽度)] [约束条件] [comment '字段说明'],
字段名2 类型 [(宽度)] [约束条件] [comment '字段说明'],
字段名3 类型 [(宽度)] [约束条件] [comment '字段说明']
)[表的一些设置];
【字段名】:列的名字
- 例如:
use mydb1;
create table if not exists student(
sid int,
name varchar(20),
gender varchar(20),
age int,
birth date,
address varchar(20)
);
2.2.2数据类型
-
数值类型
类型 范围 用途 tinint (-128,127) 小整数值 smallint (-32 768,32 767) 大整数值 mediumint (-8 388 608,8 388 607) 大整数值 int/integer (-2 147 483 648,2 147 483 647) 大整数值 bingint (-9 233 372 036 854 775 808,9 233 372 036 854 775 807) 极大整数值 float (-3.402 823 466 E+38,3.402 823 466 351E+38) 单精度浮点数值 double (-1.797 693 134 862 315 7 E+308,1.797 693 134 862 315 7 E+308) 双精度浮点数值 decimal 依赖于M和D的值 小数值 -
字符串类型
类型 用途 char 定长字符串 varchar 变长字符串 tinyblob 不超过255个字符的二进制字符串 tinytext 短文本字符串 blob 二进制的长文本字符串 text 长文本数据 mediumblob 二进制形式的中等长度文本数据 mediumtext 中等长度文本数据 longblob 二进制形式的极大文本数据 longtext 极大文本数据 【注意】:在MySQL中字符串用单引号,如:‘ 张三 ’
-
日期类型
类型 范围 格式 用途 date 100-01-01/9999-12-31 YYYY-MMM-DD 日期值 time ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间 year 1901/2155 YYYY 年份值 datetime 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值 timestamp 1970-01-01 00:00:00/2038 YYYYMMDD HHMMSS 混合日期和时间值,时间戳
2.2.3其他操作
功能 | SQL |
---|---|
查看当前数据库的所有表名称 | show tables; |
查看指定某个表的创建语句 | show create table 表名; |
查看表结构 | desc 表名 |
删除表 | drop table 表名 |
#3.查看当前数据库所有的表
show tables;
#4.查看指定表的创建语句
show create table student;
CREATE TABLE `student` (
`sid` int DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`gender` varchar(20) DEFAULT NULL,
`age` int DEFAULT NULL,
`birth` date DEFAULT NULL,
`address` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
#5.查看表结构
desc student;
#6.删除表
drop table student;
2.3修改表结构
2.3.1修改表结构格式
-
添加列
格式:
alter table 表名 add 列名 类型(长度) [约束];
例子:
#为student表添加一个新的字段为:系别dept 类型varchar(20) alter table student add dept varchar(20);
-
修改列名和类型
格式:
alter table 表名 change 旧列名 新列名 类型(长度) [约束];
例子:
#将student表的dept字段更换为department varchar(30) alter table student change dept department varchar(30);
-
修改表删除列
格式:
alter table 表名 drop 列名;
例子:
#删除student表中department这列 alter table student drop department;
-
修改表名
格式:
rename table 表名 to 新表名;
例子:
#修改student改名为stu rename table student to sti;
三、DML(数据操作语言)
DML是指数据操作语言,用来对数据库中表的数据记录进行更新
关键字:(增删改)
- 插入insert
- 删除delete
- 更新update
3.1数据插入
格式:
insert into 表(列名1,列名2,列名3...)
values (值1,值2,值3...),
(值1,值2,值3...),
...;-- 向表中插入某些行的数据
insert into 表 values (值1,值2,值3...),
(值1,值2,值3...),
...;-- 向表中插入所有列的数据
列子:
insert into student(sid,name,gender,age,birth,adddress,score) values(1001,'男',18,'1996-12-23','北京',83.5);
insert into student values(1001,'男',18,'1996-12-23','北京',83.5);
3.2数据修改
格式:
update 表名 set 字段名=值,字段名=值...;
update 表名 set 字段名=值,字段名=值... where 条件;
列子:
#将所有学生的地址改为重庆
update student set address = '重庆' ;
#将id为1004的学生地址改为北京
update student set address = '北京' where id = 1004;
#将id为1005的学生的地址修改为北京,成绩修改为100
update student set address = '北京',score = 100 where id = 1005;
3.3数据删除
格式:
delete from 表名 [where 条件];
truncate table 表名;
truncate 表名;
例子:
#删除sid为1004的学生数据
delete from student where sid = 1004;
#删除表所有数据
delete from student;
#清空表数据
truncate table student;
truncate student;
【注意】:delete只删除内容,而truncate类似于删除表后再创建表
四、mysql约束
约束(constraint):表中数据的限制条件【在创建表的时候使用】
常用约束:
- 主键约束(primary key)PK
- 自增长约束(auto_increment)
- 非空约束(not null)
- 唯一性约束(unique)
- 默认约束(default)
- 零填充约束(zerofill)
- 外键约束(foreign key)FK
4.1主键约束
4.1.1概念
-
MySQL主键约束是一个列或多个列的组合,其值能唯一地标识表中的每一行,方便在RDBMS中尽快的找到某一行
-
主键约束 ≈ 唯一约束+非空约束
主键约束列不允许重复,也不允许出现空值
-
每个表最多只允许一个主键
-
主键约束的关键字是primary key
-
当创建主键的约束时,系统默认会在所在的列和列组合上建立对应的唯一索引
4.1.2添加单列主键
方法一:在定义字段的同时指定主键
格式:
create table 表名(
...
<字段名> <数据类型>primary key
...
);
例子:
create table emp1(
eid int primary key,
name varchar(20)
);
方法二:定义字段后再指定主键
格式:
create table 表名(
...
<字段名> <数据类型>primary key
...
[constraint <约束名>] primary key [字段名]
);
例子:
create table emp2(
eid int primary key,
name varchar(20),
constraint pk1 primary key(id) -- constraint pk1 可省略
);
4.1.3添加多列联合主键
【注意】:当主键是由多个字段组成时,不能直接在字段后面声明主键约束;联合主键也是一个主键
格式:
create table 表名(
...
primary key(字段1,字段2,...,字段n)
);
例子:
create table enp3(
name varchar(20),
deptid int,
primary key(name,deptid)
);
4.1.4通过修改表结构添加主键
格式:
create table 表名(
...
);
alter table <表名> add primary key(字段列表);
例子:
create table emp4(
eid int,
name varchar(20),
deptid int,
salsry double
);
alter table emp4 add primary key(eid);
4.1.5删除主键
格式:
alter table <数据表名> drop primary key;
例子:
#删除单列主键
alter table emp1 drop primary key;
#删除联合主键
alter table emp5 drop primary key;
4.2自增长约束
4.2.1概念和特点
- 通常和主键放一块,功能是实现主键的自增长
- 方法:通过给字段添加auto_increment属性来实现主键自增长
- 默认初始值为1,每增一条记录,字段值自动加1
- auto_increment约束的字段必须具备not null属性
- auto_increment约束的字段只能是整数类型
- auto_increment约束字段的最大值受该字段的数据类型约束
4.2.2添加自增长约束
格式:
字段名 数据类型 auto_increment
例子:
create table user1(
id int primary key auto_increment,
name varchar(20)
);
4.2.3指定自增字段初始值
方法一:创建表时指定
create table uesr2(
id int primary key auto_increment,
name varchar(20)
)auto_increment=100;
方法二:创建表之后指定
create table user3(
id int primary key auto_increment,
name varchar(20)
);
alter table user3 auto_increment = 100;
4.2.4delete和truncate删除后自增列的变化
- delete数据后自动增长从断点开始
- truncate数据后自动增长从1(默认起始值)开始
4.3非空约束
功能:指定字段的值不能为空
4.3.1添加非空约束
格式:
#方法一
<字段名><数据类型>not null;
#方法二
alter table 表名 modify 字段 类型 not null;
例子:
#方式一:创建表时指定
create table user6(
id int,
name varchar(20) not null,
address varchar(20) not null
);
#方法二:创建表之后指定
create table user7(
id int,
name varchar(20),
address varchar(20)
);
alter table user7 modify name varchar(20)not null;
alter table user7 modify address varchar(20)not null;
4.3.2删除非空约束
格式:
alter table 表名 modify 字段 类型;
例子:
alter table user7 modify name varchar(20);
alter table user7 modify address varchar(20);
4.4唯一约束
4.4.1概念
所有记录中字段的值不能重复出现
4.4.2添加唯一约束
格式:
#方法一
<字段名><数据类型> unique
#方法二
alter table 表名 add constraint 约束名 unique(列);
例子:
#创建表时指定
create table user8(
id int,
name varchar(20),
phonenumber varchar(20) unique -- 指定唯一约束
);
#创建表后指定
create table user9(
id int,
name varchar(20),
phonenumber varchar(20)
);
alter table user9 add constraint unique1 unique(phonenumber)
4.4.3删除唯一约束
格式:
alter table <表名> drop index <唯一约束名>
例子:
alter table user9 drop index unique_1;
4.5默认约束
4.5.1概念
指定某列的默认值
4.5.2添加默认约束
格式:
#方法一
<字段名><数据类型> default <默认值>;
#方法二
alter table 表名 modify 列名 类型 default 默认值;
#创建表时指定
create table user10(
id int,
name varchar(20),
address varchar(20) default '北京' -- 指定默认约束
);
#创建表之后指定
create table user11(
id int,
name varchar(20),
address varchar(20)
);
alter table user11 modify address varchar(20) default '深圳';
4.5.3删除默认约束
格式:
alter table <表名> change column <字段名> <类型> default null;
例子:
alter table user11 modify address varchar(20) default null;
4.6零填充约束
- 插入数据时,当该字段的值的长度小于定义的长度时,会在该值的前面补上相应的0
- zerofil默认为int(10)
- 使用zerofill时,默认会自动加unsigned(无符号)属性【数值范围是原范围的两倍】
-
操作
create table user12( id int zerofill,-- 零填充约束 name varchar(20) );
-
删除
alter table user12 modify id int;
五、DQL(基本查询)
格式:
select
[all|distinct]
<目标列的表达式1> [别名],
<目标列的表达式2> [别名]...
from <表名或视图名> [别名],<表名或视图名> [别名]...
[where<条件表达式>]
[group by <列名>]
[having <条件表达式>]
[order by <列名> [asc|desc]]
[limit <数字或者列表>];
简化格式:
select *| 列名 from 表 where 条件
5.1数据准备
第一步:创建数据库和表
-- 创建数据库
create database if not exist mydb2;
-- 创建表
create table product(
pid int primary key auto_increment,-- 商品编号
pname varchar(20) not null,-- 商品名字
price double,-- 商品价格
category_id varchar(20)-- 商品所属分类
);
第二步:添加数据
insert into product values(null,'海尔洗衣机',5000,'c001');
insert into product values(null,'美的冰箱',3000,'c001');
5.2简单查询
-- 查询所有的商品
select * from product;
-- 查询商品名和商品价格
select pname,price from product;
-- 别名查询.使用的关键字是as(as可省略)
#表别名
select * from product as p; -- p是别名
#列别名
select pname as pn from product;--pn是列别名
-- 去掉某列的重复值
select distinct price from product;
-- 去掉一模一样的数据
select distinct * from product;
-- 查询结果是表达式(运算查询):将所有商品的价格+10元进行显示
select pname,price+10 newprice from product;-- 显示的价格是新价格 别名为newprice
5.3运算符
-
算术运算符
算术运算符 说明 + 加法运算 - 减法运算 * 乘法运算 / 或 DIV 除法运算,返回商 % 或 MOD 除法运算,返回余数 -
比较运算符
比较运算符 说明 = 等于 < 和 <= 小于 和 小于等于 > 和 >= 大于 和 大于等于 <=> 两个操作码均为NULL时,所得值为1;当一个操作码为NULL时,所得值为0 <> 或 != 不等于 IS NULL 或 ISNULL 判断一个值是否为NULL IS NOT NULL 判断一个值是否不为NULL LEAST 当有两个或多个参数时,返回最小值 GREATEST 当有两个或多个参数时,返回最大值 BETWEEN AND 判断一个值是否落在两个值之间 IN 判断一个值是IN列表中的任意一个值 NOT IN 判断一个值不是IN列表中的任意一个值 LIKE 通配符匹配 REGEXP 正则表达式匹配 #查询商品价格在200到1000之间所有商品 select * from product where price between 200 and 1000; select * from product where price >= 200 and price <= 1000; select * from product where price >= 200 && price <= 1000; #查询商品价格是200或800的所有商品 select * from product where price in(200,800); #查询含有‘箱’字的所有商品 -- %用来匹配任意字符 select * from product where pname like '箱%';-- 只要第一个字是‘箱’就行 select * from product where pname like '%箱%';-- 只要中间出现‘箱’就行 select * from product where pname like '%箱';-- 只要最后一个字是‘箱’就行 #查询第二个字为‘的’所有商品 -- _用来匹配单个字符 select * from product where pname like '_的%'; #查询category_id为null的商品 select * from product where category_id is null; #使用least求最小值 -- 如果求最小值时,有个值为null,则不会比较,结果直接为null select least(10,5,20) as small_number; #使用greatest求最大值 -- 如果求最大值时,有个值为null,则不会比较,结果直接为null select greatest(10,30,20) as big_number;
-
逻辑运算符
逻辑运算符 说明 NOT 或者 I 逻辑非 AND 或者 && 逻辑与 OR 或者 || 逻辑或 XOR 逻辑异或 -
位运算符
位运算符 说明 | 按位或 & 按位与 ^ 按位异或 << 按位左移 >> 按位右移 ~ 按位取反,反转所有比特
5.4排序查询
(order by)
格式:
select
字段名1,字段名2,......
from 表名
order by 字段名1 [asc|desc],字段名2 [asc|desc]......
特点
-
asc表示升序,desc表示降序,如果不写默认升序
-
order by用于子句中可以支持单个字段,多个字段,表达式,函数,别名
字段必须为数值类型 或者 英文和数字的字符串类型
-
order by子句,放在查询语句的最后面,LIMIT子句除外
#使用价格降序
select * from product order by price desc;
#在价格降序的基础上,以分类降序
select * from product order by price desc,category_id desc;
#显示商品的价格(去重)并且降序
select distinct price from product order by price desc;
5.5聚合查询
5.5.1聚合函数
聚合函数 | 作用 |
---|---|
count() | 统计指定列不为NULL的记录行数 |
sum() | 计算指定列的数值和【若指定列类型不是数值类型,那么计算结果为0】 |
max() | 计算指定列的最大值【若指定列是字符串类型,那么使用字符串排序运算】 |
min() | 计算指定列的最小值【若指定列是字符串类型,那么使用字符串排序运算】 |
avg() | 计算指定列的平均值【若指定列类型不是数值类型,那么计算结果为0】 |
#查询商品的总行数
select count(pid) from product;
select count(*) from product;
#查询价格大于200商品的总条数
select count(pid) from product where price > 200;
#查询分类为‘c001’的所有商品的总和
select sum(price) from product where category_id = 'c001';
#查询商品的最大和最小价格
select max(price) as max_price,min(price) as min_price from product;
5.5.2NULL值的处理
-
count函数对null值的处理
若参数为*,则统计所有记录的个数
若参数为某字段,则不统计含null值的记录个数
-
sum、avg、max和min函数对null值的处理
忽略null值的存在
5.6分组查询
(group by)
格式:
select 字段1,字段2... from 表名 group by 分组字段 having 分组条件;
例子:
#统计各个分类商品的个数
select category_id,count(pid) from product group by category_idi;
【注意】:分组之后,select后边只可以跟分组字段和聚合操作
关键字having(作用类似于where)
SQL书写顺序:select -> from -> where -> group by -> having ->order by ->limit
SQL执行顺序:from -> where -> group by -> count -> having -> select ->order by
5.7分页查询
格式:
#显示前n条
select 字段1,字段2... from 表明 limit n
#分页显示
select 字段1,字段2... from 表明 limit m,n
-- m(整数):表示从第几条索引开始,计算方式(当前页-1)*每页显示条数
-- n(整数):表示查询多少条数据
例子:
#查询product表的前5条记录
select * from product limit 5
#从第4条开始显示,显示5条
select * from product limit 3,5
5.8INSERT INTO SELECT语句
功能:将一张表的数据导入到另一张表中
格式:
#方法一
insert into Table2(fidle1,fidle2,...) select value1,value2,... from Table1;
#方法二
insert into Table2 select * from Table1;
【注意】目标表Table2必须存在,所以要先创建目标表
练习中出现的点
-
ifnull(comn,0)
【如果comn的值为null,则当作0,不为null(comn是一个参数)】
-
题目要求求”每个“、”各个“的时候 -----> 分组操作
5.9正则表达式
正则表达式:描述一种字符串匹配的规则
关键字:REGEXP
模式 | 描述 |
---|---|
^ | 匹配输入字符串的开始位置 |
$ | 匹配输入字符串的结束位置 |
· | 匹配除”\n“之外的任何单个字符 |
[…] | 字符集合。匹配所包含的任意一个字符 例如,‘ [abc] ’可以匹配 ”plain“ 中的 ‘a’ |
[^…] | 负值字符集合。匹配未包含的任意字符 例如,’ [ ^ abc] ’ 可以匹配 ”plain“ 中的 ‘p’ |
p1|p2|p3 | 匹配p1或p2或p3例如,‘z | food’ 能匹配” z “ 或 ”food“’(z|f)ood‘能匹配” zood “或” food “ |
* | 匹配前面的子表达式零次或多次。例如,zo*能匹配”z“以及”zoo“【 * 等价于{0,}】 |
+ | 匹配前面的子表达式一次或多次。例如,zo+能匹配”zo“以及”zoo“【 + 等价于{1,}】 |
{n} | n(非负整数)。匹配确定的n次例如,’ o{2} ‘不能匹配“Bob”中的o,但是能匹配“food”中的o |
{n,m} | m(非负整数)、n(非负整数)【n <= m】,最少匹配n次并且最多匹配m次 |
六、多表操作
6.1多表关系
- 一对一关系
- 一对多/多对一关系
- 多对多关系
6.2外键约束
6.2.1特点
- 定义一个外键时,需要遵守下列规则
- 主表必须存在于数据库中,或者是当前正在创建的表
- 必须为主表定义主键
- 主键不能包含空值,但允许在外键中出现空值
- 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键
- 外键中列的数目必须和主表的主键中列的数目相同
- 外键中列的数据类型必须和主表的主键中对应列的数据类型相同
6.2.2操作
方法一:在创建表时设置外键约束
关键字:foreign key
格式:
[constraint <外键名>] foreign key 字段名 [,字段名2,...] references <主表名> 主键列1 [,主键列,...]
例子:
#创建数据库
create database mydb3;
use mydb3;
#创建部门表(主表)
create table if not exists dept(
deptno varchar(20) primary key, -- 部门号
name varchar(20) -- 部门名字
);
#创建员工表(从表)
create table if not exists emp(
eid varchar(20) primary key, -- 员工编号
ename varchar(20), -- 员工名字
age int, -- 员工年龄
dept_id varchar(20), -- 员工所属部门
-- 外键约束
constraint emp_fk foreign key (dept_id) references dept (deptno) -- emp_fk自己取名的外键名
);
方法二:在创建表时设置外键约束
格式:
alter table <数据表名> add constaint <外键名> foreign key(<列名>) references <主表名> (<列名>);
例子:
#创建部门表(主表)
create table if not exists dept(
deptno varchar(20) primary key, -- 部门号
name varchar(20) -- 部门名字
);
#创建员工表(从表)
create table if not exists emp(
eid varchar(20) primary key, -- 员工编号
ename varchar(20), -- 员工名字
age int, -- 员工年龄
dept_id varchar(20), -- 员工所属部门
);
-- 外键约束
alter table emp add constraint dept_fk foreign key(dept_id) references dept(deptno);
6.2.3在外键约束下的数据操作
-
数据插入
#1.添加主表数据 insert into dept values('1001','销售部'); insert into dept values('1002','开发部'); ...... #2.添加从表数据(外键列的值必须依赖主表的主键列) insert into emp values('7','张三',20,'1002 '); ......
-
数据删除
【注意】:
- 主表的数据被从表依赖时,不能删除,否则可以删除
- 从表的数据可以随便删除
delete from dept where deptno = '1001'; -- 可以删除 delete from dept where deptno = '1002'; -- 不可以删除 delete from emp where eid = '7'; -- 可以删除 delete from emp; -- 可以删除
6.2.4删除外键约束
外键一旦删除,就会解除主表和从表间的关联关系
格式:
alter table <表名> drop foreign key <外键约束名>;
例子:
alter table emp drop foreign key dept_id_fk;
6.2.5外键约束的多对多关系
#3.创建中间表student_course/score(从表)
create table score(
sid int,
cid int,
score double
);
#4.建立外键约束(2次)
alter table score add foreign key (sid) references student(sid);
alter table score add foreign key (cid) references course(cid);
#5.给学生表添加数据
insert into student values(1,'张三',18,'女'),(2,'李四',20,'男'),(3,'王五',19,'男');
#6.给课程表添加数据
insert into course values(1,'语文'),(2,'数学'),(3,'英语');
#7.给中间表添加数据
insert into score values(1,1,78),(1,2,75),(2,1,88),(2,3,90),(3,2,80),(3,3,65);
【注意】:修改和删除时,中间从表可以随便删除和修改,但是两边的主表受从表依赖的数据不能删除或修改
6.3多表联合查询
-
数据准备
【注意】:外键约束对多表查询并无影响
6.3.1交叉连接查询
特点:
- 笛卡尔积:一张表的每一行和另外一张表的任意一行进行匹配
- 假设A表有m行数据,B表有n行数据,则返回m*n行数据
- 会产生很多冗余的数据,后期的其他查询可以在该集合的基础上进行条件筛选
格式:
select * from 表1,表2,表3...;
例子:
-- 交叉连接查询
select * from dept,emp;
6.3.2内连接查询
特点:
- 求多张表的交集
格式:
#隐式内连接
select * from A,B where 条件;
#显式内连接(inner可省略)
select * from A inner join B on 条件;
例子:
#查询每个部门的所属员工
-- 隐式
select * from dept,emp where dept.deptno = emp.dept_id;
-- 显式
select * from dept inner join emp on dept.deptno = emp.dept_id;
6.3.3外连接查询
【outer可省略】
-
左外连接:left outer join
select * from A left outer join B on 条件1 left outer join C on 条件2;
把A表中数据全部输出,B表中有的数据输出,没有的补null
-
右外连接:right outer join
select * from A right outer join B on 条件1 right outer join C on 条件2;
把B表中数据全部输出,A表中有的数据输出,没有的补null
-
满外连接:full outer join【union】
select * from A full outer join B on 条件
#使用union(将两个查询结果上下拼接,并且查重) select * from A left outer join B on 条件1 union right outer join B on 条件1; #使用union all(将两个查询结果上下拼接) select * from A left outer join B on 条件1 union right outer join B on 条件1;
把A、B中数据全部输出
#查询哪些部门有员工,哪些部门没有员工
use mydb3;
select * from dept a left outer join emp b on a.deptno = b.dept_id;
#查询哪些员工有对应的部门,哪些没有
select * from dept a right outer join emp b on a.deptno = b.dept_id;
#使用union关键字实现满外连接
select * from dept
left outer join emp on 条件1
union
right outer join emp on 条件1;
6.3.4子查询
子查询就是select嵌套的查询
6.3.4.1基础子查询
子查询返回的数据类型有四种
- 单行单列:返回的是一个具体列的内容,可理解为一个单值数据
- 单行多列:返回一行数据中多个列的内容
- 多行单列:返回多行记录之中同一列的内容,相当于给出了一个操作范围
- 多行多列:查询返回的结果是一张临时表
#查询年龄最大的员工信息,显示信息包括员工号、员工名字、员工年龄
-- 1.查询最大年龄
select max(age) from emp;
-- 2.让每一个员工的年龄和最大年龄进行比较,相等则满足
select * from emp where age = (select max(age) from emp);
#查询研发部和销售部的员工信息,包含员工号、员工名字
-- 方式1-关联查询
select * from dept a join emp b on a.deptno = b.dept_id and (name = '研发部' or name = '销售部');
-- 方式2-子查询
-- 2.1先查询研发部和销售部的部门号->1001 1002
select deptno from dept where name = '研发部' or name = '销售部';
-- 2.2查询哪个员工的部门号是1001或1001
select * from emp where dept_id in (select deptno from dept where name = '研发部' or name = '销售部');
6.4.3.2子查询关键字
-
ALL关键字
格式:
select...from...where c > all(查询语句) -- 等价于 --> select...from...where c > result1 and c >result2 and c >result3
特点:
- ALL与子查询返回的所有值比较为true,则返回true
- ALL可与=、>、<、>=、<=、<>结合使用
例子:
#查询年龄大于‘1003’部门所有年龄的员工信息 select * from dept where age > all(select age from emp where dept_id = '1003');
-
ANY关键字
格式:
select...from...where c > any(查询语句) -- 等价于 --> select...from...where c > result1 or c >result2 or c >result3
特点:
- ANY与子查询返回的任意值比较为true,则返回true
- ANY可与=、>、<、>=、<=、<>结合使用
- ANY与SOME作用相同
-
SOME关键字【同ANY】
-
IN关键字
格式:
select...from...where c > in(查询语句) -- 等价于 --> select...from...where c = result1 or c =result2 or c =result3
特点:
- 用于判断某个记录的值,是否在指定的集合中
- NOT IN 可以将条件反过来
例子:
#查询有粉饼和销售部的员工信息,包含员工号、员工名字 select eid,ename,t.name from emp where dept_id in(select deptno from dept where name = '研发部' or name = '销售部');
-
EXISTS关键字
格式:
select ...from ...where exists(查询语句)
特点:
- 只要查询语句至少返回一条数据,则EXISTS()的结果为”true“
- EXISTS关键字效率比IN高,数据量大的时候推荐使用EXISTS关键字
例子:
#查询公司是否有大于60岁的员工,有则输出 select * from emp where exists (select * from emp where age>60);-- 错误 全表输出 select * from emp a where exists (select * from emp where a.age>60);-- 正确
6.3.5表自关联
对表自身进行关联查询
【注意】:自关联时表必须给表起别名
格式:
select 字段列表 from 表1 a, 表1 b where 条件;
或者
select 字段列表 from 表1 a [left] join 表1 b on 条件;
例子:
#创建表,并且建立自关联约束
create table t_sanguo(
eid int primary key,
ename varchar(20),
manager_id int,
foreign key (manger_id) references t_sanguo(eid)
);
#查询每个三国人物及他的上级信息,如:关羽 刘备
select * from t_sanguo a,t_sanguo b where a.manager_id = b.eid;
#查询所有人物及上级
select a.ename,b.ename from t_sanguo a left join t_sanguo b on a.manager_id = b.eid;
#查询所有人物、上级、上上级
select
a.ename,b.ename,c.ename
from t_sanguo a
left join t_sanguo b on a.manager_id = e.eid
left join t_sanguo c on b.manager_id = c.eid;
七、MySQL函数
- 聚合函数
- 数学函数
- 字符串函数
- 日期函数
- 控制流函数
- 窗口函数
7.1聚合函数
除了之前的count、sum、min、max、avg这些聚合函数
还有 group_concat()函数:该函数用户实现行的合并
格式:
group_concat{[distinct] 字段名 [order by 排序字段 asc/desc [separator '分隔符']}
【注意】:
- 使用distinct可以排除重复值
- 如果需要对结果中的值进行排序,可以使用order by子句
- separator是一个字符串值,默认为逗号
例子:
#将所有员工的名字合并成一行
select gruop_concat(emp_name) from emp;
#指定分隔符合并
select department,gruop_concat(emp_name separator ';') from emp group by deperment;
7.2数学函数
数学函数 | 描述 | 实例 |
---|---|---|
abs(x) | 返回x的绝对值 | select abs(-1)–>返回1 |
ceil(x) | 返回>=x的最小整数【向上取整】 | select ceil(1.5)–>返回2 |
floor(x) | 返回<=x的最大整数【向下取整】 | select floor(1.5) -->返回1 |
greatest(expr1,expr2,expr3,…) | 返回列表中的最大值 | select greatest(3,12,34,8,15) -->返回34 |
least(expr1,expr2,expr3,…) | 返回列表中的最小值 | select least(3,12,34,8,15) -->返回3 |
max(expression) | 返回字段expression中的最大值 | # 返回数据表products中字段price的最大值 select max(price)largest_price from products; |
min(expression) | 返回字段expression中的最小值 | # 返回数据表products中字段price的最小值 select min(price)largest_price from products; |
mod(x,y) | 返回x除以y以后的余数 | select mod(5,2)–>返回1 |
pi( ) | 返回圆周率(3.141593) | select pi()–>返回3.141593 |
pow(x,y) | 返回x的y次方 | select pow(2,3) -->返回8 |
rand() | 返回0到1的随机数 | select rand()–>返回0.94865 #想要返回0到100之间的随机数 select rand()*100 |
round(x) | 返回离x最近的整数(遵循四舍五入) | select round(1.6686)–>返回2 |
round(x,y) | 返回指定位数的小数(遵循四舍五入) | select round(1.6686,2)–>返回1.67 |
truncate(x,y) | 返回数值x保留到小数点后y位的值(不会四舍五入) | select truncate(1.6686,2)–>返回1.66 |
【expr】:可以是数字,也可以是字符串
【expressio】:字段