mysql
一,数据库
1).什么是数据库?
注解: 数据库是一个专业的存储和管理数据的仓库
2).数据库的分类
扩展内容:数据库的分类
早期 : 层次式数据库 , 网络型数据库
现在 : 关系型数据库 , 非关系型数据库
现在市面上大部分用的还是关系型数据库
(2.1). 什么是关系型数据库
注解: 底层以 **二维表 ** 的形式保存数据的库 , 就是关系型数据库
常见的关系型数据库有哪些?
Oracle :
甲骨文公司提供的一款数据库产品,收费的 , 之前在java中市场份额超过50%
特点 : 主要适用于一些大型或者超大型应用系统.
SQL Server :
微软提供的一款数据库产品 , 收费.
特点 : 主要使用与一些中型或者大型的应用系统.
MySQL :
瑞典的一个公司 (MySQLAB) 提供的一款数据库产品 ,
已被 ( 甲骨文 )收购
特点 :
(1)小巧轻量 , 简单易用 , 适用于一些小型或中型的应用系统,
(2)如果做mysql集群 , 也可以用于一些大型或者超大型系统 .
DB2 : IBM公司的一款产品 , 多用于金融 / 银行等系统较多 , 收费
SQLite : 迷你数据库 , 用于嵌入式设备 ( 手机 / 智能家居 等产品 )
3).数据库相关名字解释
数据库服务器 : 将mysql安装在计算机上 , 那么这台计算机就可以作为数据库服务器使用 ,
可以实现数据的存和取 . 一个数据库服务器可以包含多个数据库.
比如 : 装好的mysql 服务器自带了四个数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
-- 数据库 : 就是 存储数据 的仓库 , 通常情况下一个网站(系统)中的所有数据会存放在一个数据库中
例 : 京东网站的所有数据 db_jd
淘宝网站的所有数据 db_taobao
百度网站的所有数据 db_baidu
-- 表: 数据库中的数据是按照 类型 存放的 , 一类数据往往存储在一张表中 , 一个数据库中可以创建多张表
例 : 京东网站的用户数据 tb_user
京东网站的商品数据 tb_product
京东网站的订单数据 tb_order
-- 表记录 : 一张表中可以包含多行表记录 , 一张表中用于存储一类信息 , 一行表记录就用于存储某一个具体的数据
例 : 数据库中的表 java中的类(student)
表记录 对象
二. SQL 语言
1) 什么是SQL语言
SQL 语言是一门操作关系型数据库的通用的语言 ( 学会了SQL可以操作所有的关系型数据库 )
SQL 可以操作 的有 :
(1) 库的 增 删 改 查
(2) 表的 增 删 改 查
(3) 表记录的 增 删 改 查( 数据 )
(4) 存储过程 / 视图 / 索引 等也可以操作
注意 : SQL 语言 是一门通用的操作关系型数据库的语言: 但每个数据库厂商 都提供了 “少量 独有” 的SQL语言 ,
SQL语言通用
单独语句不通用!
(1.1) 如何链接mysql服务器
cmd窗口
-- 方式一 : mysql -u用户名 -p用户名
-- 方式二 : mysql -u用户名 -p 第二行输入密码
-- 方式三 : mysql -u用户名 -p -h主机名或ip地址 -p端口
-h : 主机名 或 ip地址
-- 注: 如果不写-h 则默认连接localhost(127.0.0.1)
-p : 端口
-- 注: 如果不写-p 则默认连接3306
退出连接mysql服务器 :
exit
quit
直接关闭窗口
2).数据库及表操作
导入SQL脚本
source 绝对路径
(2.1) . 增 删 改 查 数据库
-- (1) . 查
-- 查看mysql服务器中的数据库
show(展示) databases;
-- 查看建库时的语句
show create database mydb1;
-- 查看进入了什么库
select database();
-- (2) . 增
-- 创建 mydb1 库 , 并 指定编码为 UTF8;
-- 语法: CREATE(创建) DATABASE(库) 库名 CHARSET(编码) uft8;
create database mydb1 charset utf8;
-- 增强语法 :
-- 用if(是否) not(不) exists(存在)进行判断数据库中是否有这个库
-- 如果没有(not) 则执行crate database 创建一个库
-- 如果有 则 不重新创建
结果: create database
判断: if not exists mydb1 charset utf8;
-- (3) . 改 ?
-- (4) . 删
-- 删除 mydb1 库
drop(删除) database(库) mydb1;
-- 代码优化:
结果: drop database
判断: if exists mydb1;
-- 判断:
-- if exists 如果 mydb1 存在
-- 则执行drop database 删除库
-- 不存在则 不执行也不会报错
(2.2) . 增 删 改 查 表
-- (1) 查
-- 查看库内的表 例:mydb1里有一个表 stu
show(展示) tables;
-- 查看创建的stu表结构
desc(描述) stu;
-- (2) 增
-- 创建 stu 学生表 ( id[int] , 姓名 , 性别 , 出生年月 , 考试成绩(浮点) )
-- 语法:
create table stu(
id int primary key auto_increment,
name varchar(50),
gander varchar(10),
birthday date,
score double
);
-- (3) 删
-- 删除stu学生表
drop table stu;
结果: drop table
判断: if exists stu;
-- (4) 改 ?
(2.3) . 增 删 改 查 表记录
-- (1) 查
-- 查看 stu 表中所有的学生信息
select * from stu
-- 查看 stu 表中 单列的信息
例: select(选择) 列名 from(来自) stu
-- (2) 增
insert(插入) into(进入) stu(表名)
-- 增加学生的信息
-- 一次添加一条 (value)
insert into stu value(2,'陈子书','男','1978-10-11',82);
-- 一次添加多个信息 (values)
insert into stu values(2,'陈子书','男','1978-10-11',82),
(3,'君陌','男','2000-7-25',98.8);
-- (3) 删 delete(删除)
-- 删除语法:
delete from 表名 where(判断) 判断条件;
结果: delete from stu
判断: where id<2; -- 仅删除符合条件的记录
-- 删除全部:
delete from 表名;
-- (4) 改 update(修改升级)
-- 修改语法
update 表名 set 需要修改的列名 = 需要修改的值 where(判断) 判断条件;
-- 例如:
-- where 判断 将 id为4 的进行 updata(修改更新)
结果: updata stu set id=1,name='天子'
判断: where id=4;
3) . 聚合函数 (查询)
is - 是 not - 不是
or - 和 / 或 and - 并且
(3.1) . where 子句查询
问题1: 如果需要+处理的数据中出现null
注解: null值 和任何数据运算结果都为null 而不是0
-- null的 ( 处理方式 ) :
方式一:
将所有的奖金为 null 的列值都更新为 0
update stu set bonus=0 where bonus is null;
方式二:
在查询时 将 null 当作0来处理 --- ifnull(列名,0)
结果: select name as 姓名,sal+ifnull(bonus,0) as 总薪资 from emp
判断: where sal+ifnull(bonus,0)>3500;
-- as 为表头指定另外的姓名 as可以省略不写
(3.2). 模糊查询(like)
通配符:
% : 可以标识0个或多个任意字符
_ : 只能标识一个字符
语法 : 查询emp表中姓名包含 君 字的员工
查询
-- 结果:select name from emp
-- 判断:where name like '%君%'
(3.3). 多行函数查询(count)
注:count 函数会自动忽略null
如: score列中有19行 null为一行 count 计算时会只计算18行
语法:
select 函数(列名) from emp;
常见的多行函数有:
-- count(列名 / *):
count(列名):标识统计当前列的值有多少个(默认不统计null值)
count(*):以行为单位 , 统计查询结果中有多少行记录
max(列名) -- 统计当前这一列中所有值的最大者
min(列名) -- 统计当前这一列中所有值的最小者
sum(列名) -- 统计当前这一列中所有值的和(会将这一列的值加在一起返回)
avg(列名) -- 统计当前这一列中所有值的平均值(这一列中所有值的和)
null 处理:
select avg(sal+bonus) from emp;
错: bonus奖金为null的话 相加结果为 svg(null)
select avg(sal+ifnull(bonus,0))from emp;
-- 将 null 改为0再进行判断
(3.4). 分组查询(group by 进行分组)
语法: select 名称/函数 from emp group by 列名;
例句:
结果:select count(*) – 输出各薪资有多少个
判断:from emp group by dept; – 通过薪资进行分组
(3.5). 排序查询
ASC 默认升序 DESC 默认降序
语法 :
固定语法 : order by 列名 asc
结果 : select 列名 from emp
判断 : order(排序) by(由) sal(列) asc(升序) -- 将sal 升序排列
desc
例如 : select * from emp order by sal asc;
(3.6). 分页查询(limit)
查询公式 :
limit(页码-1)*每页显示数 , 每页显示数量
-- 查询emp表中的记录 , 每页三条 , 查询第1页
select * from emp limit 0 , 3;
-- 查询 emp 表中的记录 ,每页三条 查询第2页
select * from emp limit 3 , 3;
-- 查询 emp 表中的记录, 每页三条 查询第3页
select * from emp limit 6, 3;
-- 查询 emp 表中的记录 , 每页三条 , 查询第四页
select * from emp limit 9, 3;
题: 求emp表中薪资最高的前三名员工的信息,显示姓名和薪资
结果: select name,sal from emp
判断: order by sal asc desc limit 0,3; --升序排列返回 然后输出结果
(3.7). 其他函数
函数有:
curdate() -- 获取当前日期: 年月日
curtime() -- 获取当前世界: 时分秒
sysdate()/now() -- 获取当前日期+时间 ,年月日 时分秒
year('2020'-8-10) -- 返回数值中的年份
month('2020-8-10') -- 返回数值中的月份
day('2020-8-10') -- 返回日期中的天数
hour('2020-8-10 11:22:6') -- 返回时间中的小时 11
minute() -- 返回时间中的 分钟
second() -- 返回时间中的秒值
--------------------------------------------------------------------------
concat_ws(x,s1,s2,...sn) -- 拼接语句
-- 将x,后面的字符都拼接在一起 , 且会将 x 作为分隔符拼接
-----------------------------------------------------------------------------
三.数据类型
MySQL 中支持多种类型 分为三类: 数值
字符串类型
日期 / 时间
1. 数值类型
类型 | 大小 | 用途 |
---|---|---|
tinyint (byte) | 1 byte | 小 整数值 |
smallint (short) | 2 byte | 大 整数值 |
int (int) | 4 byte | 大 整数值 |
bigint (long) | 8 byte | 极大 整数值 |
float (float) | 4 byte | 单精度 浮点数值 |
double (double) | 8 byte | 双精度 浮点数值 |
decimal | - | 存储 精确 的小数值 |
注: decimal
-- decimal 用法
语法: 变量名 decimal(P,D);
p: 表示 有效数的 精度 代表整数+小数的总位数
D: 表示 小数点后 的位数 表示小数点后能留几位小数 必须 : (D<=P)
--例如:
uid DECIMAL(6,2);
表示:
P : amout 最多可以 存储 6位数字,
D : 小数位置为2位 ---- 也就是 整数位最多可存四位
2 . 字符串类型
常见的字符串类型:
类型 | 大小 | 用途 |
---|---|---|
char | 0~255(字符) | 定长字符串 |
varchar | 0~65535(字节) | 边长字符串 |
mediumtext | 0~16 777 215(字节,约16M) | 大文本 / 长文本 |
mediumblob | 0~16 777 215(字节,约16M) | 二进制 |
longtext | 0~4 294 967 295(字节, 约4G) | 极大文本 |
longblob | 0~4 294 967 295(字节,约4G) | 极大二进制 |
定长字符串: – -char 长度固定时使用
-- 例:
-- create table user(
-- username char(10),
-- ...
-- )
结果:
1) 设置长度为10的字符串
2) 存入 3字符 的数据
3) 还剩 7字符 空间
4) 这 7字符空间会用空格进行填充 (浪费空间)
边长字符串: — varchar 长度不固定时使用
-- 在使用varchar是 65535个字节 实际只能使用65532个
-- 实际为:
-- 65535-1-2
-- -1 : varchar的输入是从第二个字符开始输入 第一个字符为空格 所以-1
-- -2 : varchar头部拿出两个字符用来统计varchar的长度 65 -- 两个字符
3.日期类型
类型 | 大小(bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
date | 3 | 1000-01-01 / 9999-12-31 | YYYY-MM-DD | 日期 |
time | 3 | ‘-838:59:59’ / ‘838:59:59’ | HH:MM:SS | 时间 |
datetime | 8 | 1000-01-01 00:00:00 / 9999-12-31 23:59:59 | YYY-MM-DD HH:MM:SS | 时间和日期 |
timestamp | 4 | 1970-01-0100:00:00/2038 | YYYYMMDD HHMMSS | 日期和简介 |
year | 1 | 1901 / 2155 | YYYY | 年份 |
timestamp: 时间戳 : 实际存储的是一个1970-1-1到该 日期时间值 的时间毫秒值
四. 联表查询
(1) 多表查询
操作 | 描述 | Name |
---|---|---|
Inner Join | 如果表中有一个匹配到, 就返回该行 | 内连接 |
Left Join | 根据左表查询 , 就算右表没有 , 也会返回一个null | 左外连接 |
Right Join | 根据右表查询 展示所有的数据 , 就算左表没有, 也会返回一个null | 右外连接 |
cross Join | 返回左表的所有行,左表的每一行与右表中的所有行组合 | 交叉连接(笛卡尔积连接) |
outer Join | 返回所有 , 没有数值的行列为null | 全连接 |
注意点 :
(1). 两表 需要有交叉点
(2). 给表设置一个别名 用来清除定义某表的字段
-
例 : 查询 姓名(study表) id(study表) 年龄(A表) 价差点name select s.name ,s.id ,age from study s inner Join A a on s.name = a.name ;
select e.name 姓名 , d.name 部门 , d.id 部门id
from emp e inner Join dept d
on e.dept_id=id;
字段分析 :
两表查询字段分析
join (连接的表) on (连接的交叉点条件) where (判断查询)
查询 姓名 id 年龄 : select s.name ,s.id ,age
查询什么表 : from study s inner Join A a
查询条件 : on s.name = a.name
where(再判断) : where age is null 查询岁数不等于空的
三表查询字段分析
查询 姓名 id 年龄 :
select a.name ,a.id ,age
两表查询
from A a inner Join B b
交叉点查询条件
on a.name = b.name
两表查询后的结果与 第三张表查询
inner Join C c
交叉点查询条件
on b.name = c.name
(2) 自连接
自己的表和自己的表连接 : 核心 一张表拆为两张一样的表即可
例如自己的表中有 一层楼 二层楼 三层楼 每一层楼有不同的公司
结果都在一张表中 , 就需要他自己与自己来排序查询
Aid | pid | name |
---|---|---|
1 | 1 | 一层楼 |
4 | 1(楼) | A公司 |
2 | 7 | 二层楼 |
5 | 3(楼) | B公司 |
3 | 5 | 三层楼 |
6 | 2(楼) | C公司 |
7 | 2(楼) | D公司 |
要求 : 需要有一个对映的字段用来查询
查询条件 :
查询字段
select a.name as ‘楼层’ , a2.name as ‘公司’
查询什么表
From A a ,A a2
查询条件
where a.Aid = a2.pid
五 . 级联
(6). 级联更新
语法 :
on update cascade
概述 :
表(dept表)中的主键发生更新时(例如将销售部的id改为40),
从表(emp表)中的记录的外键数据也会跟着该表(即赵六和刘能的部门编号也会更新为40)
(7). 级联删除
语法 :
on delete cascade
概述:
不添加级联删除时 :
删除部门表中的某一个部门时(例如删除4号部门),
若该部门在员工表中有对应的员工(赵六和刘能),删除会失败!
添加级联删除
删除部门表中的某一个部门时,若该部门在员工表中有对应的员工,
会在删除部门的同时,将员工表中对应的员工也删除!
create table emp(
id int primary key auto_increment, -- 员工编号 -- 主键自增
name varchar(20), -- 员工姓名
dept_id int, -- 部门编号
foreign key(dept_id) references dept(id) -- 指定外键
on update cascade -- 级联更新
on delete cascade -- 级联删除
);
insert into emp values(null, '张三', 1);
insert into emp values(null, '李四', 2);
insert into emp values(null, '老王', 3);
insert into emp values(null, '赵六', 4);