目录
一.数据库概述
1.为什么学习数据库:
实现数据持久化到本地;
使用完整的管理系统统一管理,可以实现结构化查询,方便管理;
2.数据库概述
数据库(
DataBase
)为了方便数据的存储和管理,它将数据按照特定的
规则存储在磁盘上
,
就是一个存储数据的仓库。
数据库的相关概念
DB
:数据库(
DataBase
)
存储数据的容器,它保存了一系列有组织的数据。
DBMS
:数据库管理系统(
DataBase Management System
)
又称为数据库软件或数据库产品,用于创建或管理
DB
。
3.Mysql数据库
MySQL
是一个关系型数据库管理系统
,
由瑞典
MySQL AB
公司开发,目
前属于
Oracle
旗下产品。
MySQL
流行的关系型数据库管理系统。
MySql
是一种关系数据库管理系统。
MySql
软件是一种开放源码软件
,
你可以修改源码来开发自己的
Mysql
系统。
MySql
数据库服务器具有快速、可靠和易于使用的特点。
MySql
使用标准的
sql
语言
,
并且支持多种操作系统
,
支持多种语言
.
mysql
商业版与社区版
MySQL
商业版是由
MySQL AB
公司负责开发与维护
,
需要付费才能使用
MySQL
社区版是由分散在世界各地的
MySQL
开发者、爱好者一起开发与维
护,可以免费使用
4.命令行方式连接mysql
登录:
mysql [-hlocalhost -P3306]
(本机可省略)
-uroot -p
(可以直
接写密码,不能有空格)
-h
:主机名
-P
:端口号
-u
:用户名
-p
:密码
退出:
exit
二.sql
1.DDL
数据
(
结构
)
定义语言
DDL(Data Definition Language)
,是用于创
建和修改数据库表结构的语言。
常用的语句:
create ,alter,drop,rename
创建
,
删除数据库
创建数据库并设置编码格式
CREATE DATABASE [if not exists]
数据库名
[ CHARSET utf8]
删除数据库
DROP DATABASE
数据库名
/ [IF EXISTS
数据库名
];
修改字符集
ALTER DATABASE
数据库名
CHARSET gbk;
数据库表的基本概念
1、数据表
表(table)是数据存储的最常见和最简单的形式,是构成关系型数据库的基本元素。
表的最简单形式是由行和列组成,分别都包含着数据。 每个表都有一个表头和表体,表头定
义表名和列名 .表中的行被看作是文件中的记录,表中的列被看作是这些记录的字段。
2、字段
字段是表里的一列,用于保存每条记录的特定信息。如客户订单表的字段包括“订单
ID”、“姓名”、“客户ID”、“职务”、“上级”、“地区”、“运货商”、“国家”等。
数据表的一列包含了特定字段的全部信息。
3、记录
记录也被称为一行数据,是表里的一行数据。
2.设 计 表
对于具体的某一个表,在创建之前,需要确定表的下列特征:
表名
(
表信息
)
表中的字段
字段的数据类型和长度
哪些约束
char
(
n
) 长度为
n
的定长字符串
,
最大长度
255
个字符
varchar(n
) 最大长度为
n
的可变长字符串
date
日期, 包含年月日
datetime
![](https://img-blog.csdnimg.cn/direct/8faeff5946ca4d4684ae12404f51eb57.png)
浮点
decimal
数据类型
(M,D)
M
:精度,数据的总长度;
D
:标度,小数点后的长度
TEXT
列字符字符串
有
4
种
TEXT
类型:
TINYTEXT
、
TEXT
、
MEDIUMTEXT
和
LONGTEXT
。
它的长度:
TINYTEXT
:最大长度
255
个字符
(2^8-1)
TEXT
:最大长度
65535(2^16-1)
MEDIUMTEXT
:最大长度
16777215(2^24-1)
LONGTEXT
最大长度
4294967295(2^32-1)
主键:
在一张表中代表唯一的一条记录
,
不能为空
,
不能重复
约束
:
PRIMARY KEY
设置主键约束
NOT NULL
不能为空约束
UNIQUE
唯一性约束
检查约束 设置条件
外键约束
主键自动增长
,
设置为自动增长时
,
只能为整数类型
AUTO_INCREMENT
默认值
DEFAULT default_value
字段注释
:
comment '
注释
3.表语法.
(1)创建
CREATE TABLE
表名
(
列名 数据类型
[
约束
] [
默认值
] [
注释
],......)
CREATE TABLE t_user(
id INT [PRIMARY KEY NOT NULL AUTO_INCREMENT],
number INT(5) NOT NULL,
stuname VARCHAR(10) NOT NULL,
age INT(3) CHECK(age>18),
birthday DATE,
weight DOUBLE,
opertime datetime,
[CONSTRAINT
约束名 约束规则
]
)
(2)删除
删除表,修改表名
删除表
DROP TABLE [if exists ]表名
修改表名
RENAME TABLE 旧表名 TO 新表名
复制表结构
CREATE TABLE 新表名 LIKE 被复制表名;
三.DML
1.概念
数据操纵语言
DML
(
Data Manipulation Language
)
●
常用语句:
insert,delete,update
2.插入数据
方式
1: INSERT INTO
表名
(
列
1,
列
2……,
列
n) VALUES(
值
1,
值
2…..,
值
n);
方式
2: INSERT INTO
表名
set
列名
1=
值
1,..
列名
n=
值
n;
方式
3: INSERT INTO
表名
(
列
1,
列
2……,
列
n) VALUES(
值
1,
值
2…..,
值
n),(
值
1,
值
2…..,
值
n);
方式
4:INSERT INTO
表名
(
列
1,
列
2……,
列
n)
查询语句
(
查询的列数与插入列数匹配
)
3.修改数据
UPDATE 表名 SET 列名 = ‘新值’WHERE 条件
4.删除数据
删除数据
DELETE FROM
表名
WHERE
条件
TRUNCATE TABLE
表名
;
清空整张表
四.DQL-基础查询
1.概念
DQL
(
Data Query Language
)数据查询语言查询是使用频率最高的一个操作,
可以从一个表中查询数据,也可以从多个表中查询数据。
2.基础查询
语法:
select
查询列表
from
表名
;
特点:
查询列表可以是:表中的字段、常量、表达式、函数
查询的结果是一个虚拟的表格
查询结果处理:
特定列查询
:select column1,column2 from table
全部列查询
: select * from table
算数运算符
:+ - * /
排除重复行
: select distinct column1,column2 from table
查询函数:
select
函数
; /
例如
version()
函数
:
类似于
java
中的方法,将一组逻辑语句事先在数据库中定义好
,
可以直接调
用
分类:
单行函数:如
concat
、
length
、
ifnull
等
分组函数:做统计使用,又称为统计函数、聚合函数、组函数
3.函数
单行函数
字符函数
length()
:获取参数值的字节个数
char_length()
获取参数值的字符个数
concat(str1,str2,.....)
:拼接字符串
upper()/lower()
:将字符串变成大写
/
小写
substring(str,pos,length)
:截取字符串 位置从
1
开始
instr(str,
指定字符
)
:返回子串第一次出现的索引,如果找不到返回
0
trim(str)
:去掉字符串前后的空格或子串
,trim(
指定子串
from
字符串
)
lpad(str,length,
填充字符
)
:用指定的字符实现左填充将
str
填充为指定长度
rpad(str,length,
填充字符
)
:用指定的字符实现右填充将
str
填充为指定长度
replace(str,old,new)
:替换,替换所有的子串
逻辑处理
case when 条件 then 结果1 else 结果2 end; 可以有多个when
ifnull(被检测值,默认值)函数检测是否为null,如果为null,则返回指定的值,否则返回
原本的值
if函数:if else的 效果 if(条件,结果1,结果2)
数学函数
round(
数值
)
:四舍五入
ceil(
数值
)
:向上取整,返回
>=
该参数的最小整数
floor(
数值
)
:向下取整,返回
<=
该参数的最大整数
truncate(
数值
,
保留小数的位数
)
:截断,小数点后截断到几位
mod(
被除数
,
除数
)
:取余,被除数为正,则为正;被除数为负,则为负
rand()
:获取随机数,返回
0-1
之间的小数
日期函数
now()
:返回当前系统日期
+
时间
curdate()
:返回当前系统日期,不包含时间
curtime()
:返回当前时间,不包含日期
可以获取指定的部分,年、月、日、小时、分钟、秒
YEAR(
日期列
),MONTH(
日期
列
),DAY(
日期
列
) ,HOUR(
日期
列
) ,MINUTE(
日期
列
)
SECOND(
日期
列
)
str_to_date(
字符串格式日期
,
格式
)
:将日期格式的字符转换成指定格式的日期
date_format(
日期列
,
格式
)
:将日期转换成字符串
datediff(big,small)
:返回两个日期相差的天数
分组函数
功能:用作统计使用,又称为聚合函数或统计函数或组函数
分类:
sum
求和、
avg
平均值、
max
最大值、
min
最小值、
count
计数
(非空)
1.sum
,
avg
一般用于处理数值型
max
,
min
,
count
可以处理任何类型
2.
以上分组函数都忽略
null
值
3.count
函数的一般使用
count
(
*
)用作统计行数
4.
和分组函数一同查询的字段要求是
group by
后的字段
4.条件查询
使用WHERE 子句,将不满足条件的行过滤掉,WHERE 子句紧随 FROM 子句。
语法
:select <
结果
> from <
表名
> where <
条件
>
比较
=, !=
或
<>, >, <, >=, <=
逻辑运算
and
与
or
或
not
非
模糊查询
LIKE
:是否匹配于一个模式 一般和通配符搭配使用,可以判断字符型数值
或数值型.
通配符: % 任意多个字符
between and 两者之间,包含临界值;
in 判断某字段的值是否属于in列表中的某一项
IS NULL(为空的)或 IS NOT NULL(不为空的)
1
、
UNION
的语法如下:
[SQL
语句
1]
UNION
[SQL
语句
2]
2
、
UNION ALL
的语法如下:
[SQL
语句
1]
UNION ALL
[SQL
语句
2]
当使用union 时,mysql 会把结果集中重复的记录删掉,而使用union all ,
mysql 会把所有的记录返回,且效率高于union 。
排序
查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC
asc代表的是升序,desc代表的是降序,如果不写,默认是升序
order by子句中可以支持单个字段、多个字段
•
数量限制
limit子句:对查询的显示结果限制数目 (sql语句最末尾位置)
SELECT * FROM table LIMIT offset rows;
SELECT * from table LIMIT 0,5;
分组查询
语法:
select
分组函数,列(要求出现在
group by
的后面)
from
表
[where
筛选条件
]
group by
分组的列表
[having
分组后的筛选
]
[order by
子句
]
注意:查询列表比较特殊,要求是分组函数和
group by
后出现的字段
分组查询中的筛选条件分为两类
:
数据源 源位置 关键字
分组前筛选 原始表
group by
子句的前面
where
分组后筛选 分组后的结果集
group by
的后面
having
五.多表设计_关联查询
1.数据库设计范式
第一范式(确保每列保持原子性)
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值,就
说明该数据库表满足了第一范式。
第二范式
第二范式就是要有主键,要求其他字段都依赖于主键。
•
没有主键就没有唯一性,没有唯一性在集合中就定位不到这行记录,所以要主键。
•
其他字段为什么要依赖于主键?因为不依赖于主键,就找不到他们。更重要的是,其
他字段组成的这行记录和主键表示的是同一个东西,而主键是唯一的,它们只需要依
赖于主键,也就成了唯一的
第三范式
第三范式
,
确保每列都和主键列直接相关
,
而不是间接相关
,
要求一个数据库表中不包含
已在其它表中包含的非主关键字信息
2.外键
引用另外一个数据表的某条记录。
外键列类型与主键列类型保持一致
数据表之间的关联/引用关系是依靠具体的主键(primary key)和外键
(foreign key)建立起来的。
create table student(
id int not null auto_increment primary key,
num int,
name varchar(10)
majorid int,
CONSTRAINT 约束名 foreign key(majorid ) references major(id)
);
约束名规则:
例:FK_ForeignTable_PrimaryTable_On_ForeignColumn
1、当主表中没有对应的记录时,不能将记录添加到从表
2、不能更改主表中的值而导致从表中的记录孤立
3、从表存在与主表对应的记录,不能从主表中删除该行
4、删除主表前,先删从表
3.关联查询
含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
笛卡尔乘积现象:表
1
有
m
行,表
2
有
n
行,结果
=m*n
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件
按功能分类:
自连接
内连接
外连接
左外连接
右外连接
4.子查询
含义:出现在其他语句中的
select
语句,称为子查询或内查询;外部的查询语句,称为主查询或
外查询
.
分类:
按子查询出现的位置:
from
后面:支持表子查询
where
:支持标量子查询,列子查询
按功能、结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
表子查询(结果集一般为多行多列)