目录
1.数据库概述
学习数据库的目的:
1.实现数据持久化到本地;
2.使用完整的管理系统统一管理,可以实现结构化查询,方便管理;
数据库(DataBase)为了方便数据的存储和管理,它将数据按照特定的规则存储在磁盘上,就是个存储数据的仓库。通过数据库管理系统,可以有效的组织和管理存储在数据库中的数据。
数据库的相关概念
DB:数据库(DataBase)
存储数据的容器,它保存了一系列有组织的数据。
DBMS:数据库管理系统(DataBase Management System)
又称为数据库软件或数据库产品,用于创建或管理DB。
根据不同的存储类型可以分为:
1.关系型数据库
关系型数据库管理系统称为RDBMS
Oracle:功能强大,收费.
MySQL 快捷、可靠 开源、免费
SQL Server(微软): 只能安装在Windows操作系统
DB2 (IBM):适合处理海量数据,收费.
2.非关系型数据库
MongdoDB
Redis
2.MySQL数据库
MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目
前属于 Oracle旗下产品。MySQL 流行的关系型数据库管理系统。
● MySql是一种关系数据库管理系统。
● MySql软件是一种开放源码软件,你可以修改源码来开发自己的 Mysql 系统。
● MySql数据库服务器具有快速、可靠和易于使用的特点。
● MySql使用标准的sql语言,并且支持多种操作系统,支持多种语言.
● mysql商业版与社区版
● MySQL商业版是由MySQL AB公司负责开发与维护,需要付费才能使用
● MySQL社区版是由分散在世界各地的MySQL开发者、爱好者一起开发与维护,可以免费使用
MySQL的安装(参考安装资料)
命令行方式连接mysql
登录:mysql [-hlocalhost -P3306](本机可省略) -uroot -p(可以直接写密码,不能有空格)
-h:主机名
-P:端口号
-u:用户名
-p:密码
退出:exit
3.SQL--DDL
结构化查询语言(Structured Query Language)简称SQL,是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统.
SQL优点:
不是某个特定数据库供应商专有的语是言,几乎所有DBMS都支持SQL简单易学,灵活使用可以进行非常复杂和高级的数据库操作
数据(结构)定义语言DDL(Data Definition Language),是用于创建和修改数据库表结构的语言。
常用的语句:create ,alter,drop,rename
创建数据库并设置编码格式
CREATE DATABASE [if not exists] 数据库名 [ CHARSET utf8]
删除数据库
DROP DATABASE 数据库名 / [IF EXISTS数据库名];
修改字符集
ALTER DATABASE 数据库名 CHARSET gbk;
数据库存储数据的特点:
● 将数据放到表中,表再放到库中
● 一个数据库中可以有多个表,每个表都有一个名字,用来标识自己。表名具有
唯一性。
● 表具有一些特性,这些特性定义了数据在表中如何存储,类似Java中“类”的
设计。
● 表由列组成,我们也称列为字段。所有表都是由一个或多个列组成的,每一列
类似java中的”属性”
● 表中的数据是按行存储的,每一行类似于Java中的“对象”。
数据库表的基本概念:
1、数据表
表(table)是数据存储的最常见和最简单的形式,是构成关系型数据库的基本元素。
表的最简单形式是由行和列组成,分别都包含着数据。 每个表都有一个表头和表体,表头定
义表名和列名 .表中的行被看作是文件中的记录,表中的列被看作是这些记录的字段。
2、字段
字段是表里的一列,用于保存每条记录的特定信息。如客户订单表的字段包括“订单
ID”、“姓名”、“客户ID”、“职务”、“上级”、“地区”、“运货商”、“国家”等。
数据表的一列包含了特定字段的全部信息。
3、记录
记录也被称为一行数据,是表里的一行。在关系型数据库的表里,一行数据是指一条完
整的记录。
设 计 表
对于具体的某一个表,在创建之前,需要确定表的下列特征:
1.表名(表信息)
2.表中的字段
3.字段的数据类型和长度
4.哪些约束
表中的数据类型:
char(n) 长度为n的定长字符串,
varchar(n) 最大长度为n的可变长字符串,
date 日期, 包含年月日,
datetime 包含 年月日 时分秒,
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)
主键
在一张表中唯一的代表一条记录,不能为空,不能重复。
约束:
1.PRIMARY KEY 设置主键约束
2.NOT NULL 不能为空约束
3.UNIQUE 唯一性约束
4.检查约束(check) 设置条件
5.外键约束 foreign key
6.主键自动增长: AUTO_INCREMENT
7.默认值: DEFAULT default_value
8.字段注释: comment '注释'
对表的基本操作:
创建表语法:
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 约束名 约束规则] 表级完整性约束
)
删除表,修改表名
删除表
DROP TABLE [if exists ]表名
修改表名
RENAME TABLE 旧表名 TO 新表名
复制表结构
CREATE TABLE 新表名 LIKE 被复制表名;
4.SQL--DML
数据操纵语言DML(Data Manipulation Language)
常用语句: insert,delete,update
插入数据
方式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) 查询语句(查询的列数与插入列数匹配)
INSERT INTO dept (dept_name, dept_desc, dept_date)
VALUES
(
'研发部',
'开发软件',
'2014-12-14'
),
(
'财务部',
'发工资',
'2014-10-14'
),
(
'市场部',
'销售软件',
'2015-11-02'
)
修改数据
UPDATE 表名 SET 列名 = ‘新值’WHERE 条件
删除数据
DELETE FROM 表名 WHERE 条件
TRUNCATE TABLE 表名;清空整张表
5.SQL--DQL
DQL(Data Query Language)数据查询语言查询是使用频率最高的一个操作,可以从一个表中查询数据,也可以从多个表中查询数据。
基础查询
语法:
select 查询列表 from 表名;
特点:
查询列表可以是:表中的字段、常量、表达式、函数。
查询的结果是一个虚拟的表格。
查询结果处理:
特定列查询:select column1,column2 from table
全部列查询: select * from table
算数运算符:+ - * /
排除重复行: select distinct column1,column2 from table
查询函数:select 函数; / 例如version()
函数:
类似于java中的方法,将一组逻辑语句事先在数据库中定义好,可以直接调用
分类:
单行函数:如concat、length、ifnull等
分组函数:做统计使用,又称为统计函数、聚合函数、组函数
字符函数
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):替换,替换所有的子串
-- 字符函数
-- length():获取参数值的字节个数
SELECT
LENGTH (NAME)
FROM
student
-- char_length()获取参数值的字符个数
SELECT
CHAR_LENGTH (NAME)
FROM
student
-- upper()/lower():将字符串变成大写/小写
SELECT
UPPER (NAME),
LOWER (NAME)
FROM
student
-- substring(str,pos,length):截取字符串 位置从1开始
SELECT
SUBSTRING (NAME, 1, 2)
FROM
student
-- instr(str,指定字符):返回子串第一次出现的索引,如果找不到返回0
SELECT
INSTR (NAME, 'i')
FROM
student
-- trim(str):去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
SELECT
TRIM (NAME)
FROM
student
SELECT
TRIM ("a" FROM NAME)
FROM
student
-- lpad(str,length,填充字符):用指定的字符实现左填充将str填充为指定长度
SELECT
LPAD (NAME, 5, 'a')
FROM
student
-- rpad(str,length,填充字符):用指定的字符实现右填充将str填充为指定长度
SELECT
RPAD (NAME, 5, 'a')
FROM
student
-- replace(str,old,new):替换,替换所有的子串
SELECT
REPLACE (NAME, 'i', 'I')
FROM
student
逻辑处理
case when 条件 then 结果1 else 结果2 end; 可以有多个when
ifnull(被检测值,默认值)函数检测是否为null,如果为null,则返回指定的值,否则返回
原本的值
if函数:if else的 效果 if(条件,结果1,结果2)
-- 逻辑处理
-- case when 条件 then 结果1 else 结果2 end; 可以有多个when
SELECT
id,
NAME,
(
CASE
WHEN height >= 1.9
THEN "是大高个"
WHEN height >= 1.7 AND height < 1.9
THEN "是高个"
ELSE "不是高个"
END
)AS '身高'
FROM
student
-- ifnull(被检测值,默认值)函数检测是否为null,如果为null,则返回指定的值,否则返回原本的值
SELECT
IFNULL (address, '未输入数据')
FROM
student
-- if函数:if else的 效果 if(条件,结果1,结果2)
SELECT
IF (
height > 1.9,
"是大高个",
"不是高个"
)
FROM
student
数学函数
round(数值):四舍五入
ceil(数值):向上取整,返回>=该参数的最小整数
floor(数值):向下取整,返回<=该参数的最大整数
truncate(数值,保留小数的位数):截断,小数点后截断到几位
mod(被除数,除数):取余,被除数为正,则为正;被除数为负,则为负
rand():获取随机数,返回0-1之间的小数
-- 数字函数
-- round(数值):四舍五入
SELECT
ROUND (height)
FROM
student
-- ceil(数值):向上取整,返回>=该参数的最小整数
SELECT
CEIL (height)
FROM
student
-- floor(数值):向下取整,返回<=该参数的最大整数
SELECT
FLOOR (height)
FROM
student
-- truncate(数值,保留小数的位数):截断,小数点后截断到几位
SELECT
TRUNCATE (height,1)
FROM
student
-- mod(被除数,除数):取余,被除数为正,则为正;被除数为负,则为负
SELECT
MOD (height,1)
FROM
student
-- rand():获取随机数,返回0-1之间的小数
SELECT
RAND(height)AS '随机数'
FROM
student
日期函数
now():返回当前系统日期+时间
curdate():返回当前系统日期,不包含时间
curtime():返回当前时间,不包含日期
可以获取指定的部分,年、月、日、小时、分钟、秒
YEAR(日期列),MONTH(日期 列 ),DAY(日期 列 ) ,
HOUR(日期 列 ) ,MINUTE(日期 列 ) SECOND(日期 列 )
str_to_date:将日期格式的字符转换成指定格式的日期
date_format:将日期转换成字符串
datediff(big,small):返回两个日期相差的天数
-- 日期函数
-- now():返回当前系统日期+时间
SELECT NOW()
-- curdate():返回当前系统日期,不包含时间
SELECT CURDATE()
-- curtime():返回当前时间,不包含日期
SELECT CURTIME()
-- YEAR(日期列),MONTH(日期 列 ),DAY(日期 列 )
SELECT YEAR(birthday),MONTH(birthday),DAY(birthday) FROM student
-- HOUR(日期 列 ) ,MINUTE(日期 列 ) SECOND(日期 列 )
SELECT HOUR(rig_time),MINUTE(rig_time),SECOND(rig_time) FROM student
-- str_to_date:将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('2023-2-3','%Y-%m-%d')
-- date_format:将日期转换成字符串
SELECT DATE_FORMAT(birthday,'%Y-%m-%d')FROM student
-- datediff(big,small):返回两个日期相差的天数
SELECT DATEDIFF(NOW(),birthday)FROM studl cxfgent
分组函数
功能:用作统计使用,又称为聚合函数或统计函数或组函数
分类:sum 求和、avg 平均值、max 最大值、min 最小值、count 计数
(非空)
1.sum,avg一般用于处理数值型max,min,count可以处理任何类型
2.以上分组函数都忽略null值
3.count函数的一般使用count(*)用作统计行数
4.和分组函数一同查询的字段要求是group by后的字段
-- 分组函数
-- sum 求和、avg 平均值、max 最大值、min 最小值、count 计数(非空)
SELECT
SUM(height),
AVG(height),
MAX(height),
MAX(NAME),
MIN(height),
COUNT(*),
COUNT(address)
FROM
student
条件查询
使用WHERE 子句,将不满足条件的行过滤掉,WHERE 子句紧随 FROM 子句。
语法:select <结果> from <表名> where <条件>
比较运算
=, != 或<>, >, <, >=, <=
逻辑运算
and 与
or 或
not 非
模糊查询
LIKE :是否匹配于一个模式 一般和通配符搭配使用,可以判断字符型数值或数值型.
通配符: % 任意多个字符,包含0个字符 _ 任意单个字符。
between and 两者之间,包含临界值;
in 判断某字段的值是否属于in列表中的某一项。
IS NULL(为空的)或 IS NOT NULL(不为空的)。
-- 逻辑运算 and 与 or 或 not 非
SELECT * FROM student WHERE gender='男' AND id>3
SELECT * FROM student WHERE gender='男' OR id>3
SELECT * FROM student WHERE (gender='男' OR id>3) AND height>=1.7
SELECT * FROM student WHERE gender<>'男'
-- LIKE :是否匹配于一个模式 一般和通配符搭配使用,可以判断字符型数值或数值型.
SELECT * FROM student WHERE NAME LIKE '张%'
SELECT * FROM student WHERE NAME LIKE '%三'
SELECT * FROM student WHERE NAME LIKE '%三%'
SELECT * FROM student WHERE NAME LIKE '张_'
-- between and 两者之间,包含临界值
SELECT * FROM student WHERE height BETWEEN 1.65 AND 1.80
-- in 判断某字段的值是否属于in列表中的某一项
SELECT * FROM student WHERE height IN(1.78,1.80,1.98)
SELECT * FROM student WHERE height NOT IN(1.78,1.80,1.98)
-- IS NULL(为空的)或 IS NOT NULL(不为空的)
SELECT * FROM student WHERE address IS NULL
SELECT * FROM student WHERE address 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 。
-- UNION
SELECT *FROM student WHERE gender='男'
UNION
SELECT *FROM student WHERE gender='女'
UNION
SELECT *FROM student WHERE gender='女'
-- UNIONALL
SELECT *FROM student WHERE gender='男'
UNION ALL
SELECT *FROM student WHERE gender='女'
UNION ALL
SELECT *FROM student WHERE gender='女'
排序
查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC。
asc代表的是升序,desc代表的是降序,如果不写,默认是升序。
order by子句中可以支持单个字段、多个字段。
SELECT * FROM student ORDER BY height
SELECT * FROM student ORDER BY height DESC LIMIT 0,3;
数量限制
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
-- 分组查询
SELECT SUM(height),gender FROM student GROUP BY gender
SELECT COUNT(height)c,gender FROM student GROUP BY gender HAVING c>=3
SELECT * FROM(SELECT COUNT(height)c,gender FROM student GROUP BY gender)m WHERE c>=3
6.多表设计_关联查询
使用多表关联的原因是解决数据冗余的问题。
数据库设计范式
● 为了建立冗余较小、结构合理的数据库,设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。
● 目前关系数据库有5种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。
● 满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF),其余范式以次类推。一般来说,数据库只需满足第三范式(3NF)就行了。
数据库设计范式
1.第一范式(确保每列保持原子性)
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值,就
说明该 数据库表满足了第一范式。
2. 第二范式就是要有主键,要求其他字段都依赖于主键。
没有主键就没有唯一性,没有唯一性在集合中就定位不到这行记录,所以要主键。
其他字段为什么要依赖于主键?因为不依赖于主键,就找不到他们。更重要的是,其
他字段组成的这行记录和主键表示的是同一个东西,而主键是唯一的,它们只需要依
赖于主键,也就成了唯一的。
3.第三范式就是要消除传递依赖,方便理解,可以看做是“消除冗余”。
外键
引用另外一个数据表的某条记录。
外键列类型与主键列类型保持一致.
数据表之间的关联/引用关系是依靠具体的主键(primary key)和外键(foreign key)建立起来
的。
create table score(
cid int not null auto_increment primary key,
score int,
sid int,
CONSTRAINT 约束名 foreign key(sid) references student(sid)
);
约束名规则:
例:FK_ForeignTable_PrimaryTable_On_ForeignColumn
外键约束:
1、当主表中没有对应的记录时,不能将记录添加到从表。
2、不能更改主表中的值而导致从表中的记录孤立。
3、从表存在与主表对应的记录,不能从主表中删除该行。
4、删除主表前,先删从表
关联查询
含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询。
笛卡尔乘积现象:表1有m行,表2有n行,结果=m*n。
发生原因:没有有效的连接条件。
如何避免:添加有效的连接条件。
按功能分类:
内连接
外连接
左外连接
右外连接
内连接(inner join)
把满足了条件的两张表中的交集数据查询出来
语法:Select 结果 from 表1,表2 where 表1.column1 = 表2.column2
左外连接(left join)
select 结果 from
表1 left join 表2 on
表1.column1 = 表2.column2
右外连接(right join)
select 结果 from
表1 right join 表2 on
表1.column1 = 表2.column2
子查询
含义:出现在其他语句中的select语句,称为子查询或内查询;外部的查询语句,称为主查询或外查询.
分类:
按子查询出现的位置:
select后面:支持标量子查询
from后面:支持表子查询
where:支持标量子查询,列子查询
按功能、结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
表子查询(结果集一般为多行多列)
-- 2.查询出工资最高的员工,并显示员工信息
SELECT *
FROM customer
WHERE money =(SELECT MAX(money) FROM customer)
-- 3.查询出每个部门工资最高的员工信息
SELECT id,NAME,gender,age,money,deptid
FROM (SELECT MAX(money)s FROM customer GROUP BY deptid)a,customer
WHERE a.s=customer.money
select id ,
name,
(select name from major where id=1)a
from student
where id=1