数据库（DB）

大家好，欢迎来到IT知识分享网。

数据库管理系统(DBMS)

为了方便数据的存储和管理，将数据按照特定的规则存储在硬盘上，是一个存储数据的仓库

关系型数据库

以数据表为单位，表与表之间存在关联关系

Mysql是一个关系型数据库

具有快速，可靠和易于使用的特点

非关系数据库 redis

缓存 Key：value

SQL（结构化查询语言）

是一种特殊目的的编程语言；

是一种标准的数据库操作语言；

用于数据的存取，查询，更新，和管理关系型数据库

SQL语句

根据操作的不同，分为不同类型的SQL语句

1.DDL

数据(结构)定义语言

创建和修改数据库表结构的语言

常用的语句：create ,alter,drop,rename

数据库名一旦创建就不能被修改，只能修改字符集

-- 创建数据库 create database test; -- 创建数据库并设置字符集编码,判断数据库是否存在 create database if not exists test charset utf8; -- 删除数据库 drop database test; -- 修改字符集 alter database test charset utf8; 

数据表

表是数据存储的最常见和最简单的形式，是构成关系型数据库的基本元素。

表的最简单形式是由行和列组成，分别都包含着数据。

每个表都有一个表头和表体，表头定义表名和列名 .表中的行被看作是文件中的记录，表中的列被看作是这些记录的字段。

设计表(数据类型)

1.字符型

char（n） 长度为n的定长字符串,最大长度255个字符

varchar(n） 最大长度为n的可变长字符串

date 日期， 包含年月日

datetime 年月日 时分秒

2.整型

signed 有符号 默认是有符号

unsigned 无符号

3.浮点型

decimal

数据类型(M,D)

M：精度，数据的总长度；

D：标度，小数点后的长度

4.TEXT列字符字符串

长文本类型

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。

-- 创建学生表 (学号，姓名，性别，电话，体重，地址，出生年月) CREATE TABLE student( num INT, NAME VARCHAR(10), gender CHAR(1), phone CHAR(11), height DECIMAL(3,2), address VARCHAR(30), brith_time DATETIME ) -- 删除表 DROP TABLE student -- 修改表名 RENAME TABLE student TO stu -- 复制表结构 CREATE TABLE stu LIKE student;

添加约束

PRIMARY KEY -- 设置为主键 AUTO_INCREMENT -- 设置主键列自然增长，只能修饰主键列，而且主键列类型为整数 not null -- 不能为空约束 可以添加到多个普通列 unique -- 唯一性约束 可以添加到多个普通列 check (height<2.60) -- 检查约束

2.DML

数据操纵语言DML（Data Manipulation Language）

常用语句： insert,delete,update

-- 修改语句 需要注意条件的准确性，否则修改全部数据 update student set address='陕西',height=1.23 where num = 1 -- 删除语句 delete from student -- 没有条件全部删除 delete from student where num=1

/*插入数据 方式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 student (num,NAME,gender,phone,height) VALUES(1,'张三','男',,1.72); -- 方法二 INSERT INTO student SET num=2,NAME='张三',gender='男',phone=,height=1.82; -- 方法三 INSERT INTO student (num,NAME,gender,phone,height) VALUES(3,'张三','男',,1.72), (4,'张三','男',,1.92), (5,'张三','男',,1.42); -- 方法四 INSERT INTO stu (num,NAME,gender,phone,height) SELECT num,NAME,gender,phone,height FROM student

3.DQL

查询列表可以是：表中的字段、常量、表达式、函数

查询的结果是一个虚拟的表格

查询结果处理：

特定列查询:select column1,column2 from table

全部列查询: select * from table

算数运算符:+ – * /

排除重复行: select distinct column1,column2 from table

查询函数：select 函数; / 例如version()

sum()称为聚合函数，把多行数据放到一个中

sql中 +-*/ 只能用做算术运算， +不能连接字符串

字符函数

-- length()：获取参数值的字节个数 SELECT NAME,LENGTH(NAME) FROM student -- char_length()获取参数值的字符个数 SELECT NAME,CHAR_LENGTH(NAME)AS NAME,gender FROM student -- concat(str1,str2,.....)：拼接字符串 SELECT num,CONCAT(NAME,':',gender)AS NAME FROM student -- upper()/lower()：将字符串变成大写/小写 SELECT UPPER(NAME),LOWER(gender) FROM student -- substring(str,pos,length)：截取字符串 位置从1开始 SELECT SUBSTRING(NAME,2,3) FROM student -- instr(str,指定字符)：返回子串第一次出现的索引，如果找不到返回0 SELECT INSTR(NAME,'张') FROM student -- trim(str)：去掉字符串前后的空格或子串,trim(指定子串 from 字符串) SELECT CHAR_LENGTH(TRIM(NAME)) FROM student -- lpad(str,length,填充字符)：用指定的字符实现左填充将str填充为指定长度 SELECT LPAD(NAME,5,'a') FROM student -- rpad(str,length,填充字符)：用指定的字符实现右填充将str填充为指定长度 SELECT RPAD(NAME,5,'b') FROM student -- replace(str,old,new)：替换，替换所有的子串 SELECT REPLACE(NAME,'张','王') FROM student

逻辑处理

case when 条件 then 结果1 else 结果2 end; 可以有多个when

ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回

原本的值

if函数：if else的 效果 if(条件,结果1,结果2)

SELECT NAME, (CASE WHEN (height>=1.80) THEN '高个子' WHEN (height>=1.60) THEN '正常身高' ELSE '低个子' END)AS height, gender FROM student SELECT NAME, IFNULL(address,'暂未确认')AS address FROM student SELECT NAME, IF(height>=1.80,'高个子','低个子')AS height FROM student

数学函数

round(数值)：四舍五入

ceil(数值)：向上取整，返回>=该参数的最小整数

floor(数值)：向下取整，返回<=该参数的最大整数

truncate(数值,保留小数的位数)：截断，小数点后截断到几位

mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负

rand()478*89：获取随机数，返回0-1之间的小数

日期函数

now()：返回当前系统日期+时间

curdate()：返回当前系统日期，不包含时间

curtime()：返回当前时间，不包含日期

可以获取指定的部分，年、月、日、小时、分钟、秒

YEAR(日期列),MONTH(日期列),DAY(日期列) ,HOUR(日期列) ,MINUTE(日期列)

SECOND(日期列)

str_to_date(字符串格式日期,格式)：将日期格式的字符转换成指定格式的日期

date_format(日期列,格式)：将日期转换成字符串

datediff(big,small)：返回两个日期相差的天数

-- 日期格式化 SELECT NAME, YEAR(brith_time),MONTH(brith_time),DAY(brith_time) FROM student -- 将字符串格式转化为日期类型 SELECT NAME, STR_TO_DATE('1982-01-17','%Y-%m-%d')AS birthday FROM player -- 返回当前系统日期 SELECT NAME, NOW() FROM student -- 返回当前系统日期，不包括时间 SELECT NAME, CURDATE() FROM student -- 将日期转换为字符串 SELECT NAME, DATE_FORMAT(birthday,'%Y-%m-%d-%H') FROM player -- 返回两个日期差的天数 SELECT NAME, DATEDIFF(NOW(),birthday) FROM player -- SECOND(日期列) SELECT NAME,SECOND(birthday) FROM player

分组函数 /聚合函数 /统计函数

sum()求和，avg()平均值，max()最大值，min()最小值，count()计数

1.sum，avg一般用于处理数值型max，min，count可以处理任何类型

2.以上分组函数都忽略null值

3.count函数的一般使用count（*）用作统计行数

4.和分组函数一同查询的字段要求是group by后的字段

5.count(列名)，如果数据中存在null，就不会统计它

条件查询

select 结果列 from 表名 where 条件

比较

=, != 或<>, >, <, >=, <=

逻辑运算

and 与

or 或

not 非

模糊查询

like %字符%

%：通配符，任意多个字符

between …. and … 表示在两个区间，包含临界值

in 判断某字段的值是否属于in列表中的某一项

in(… , … , …)

IS NULL（为空的）或 IS NOT NULL（不为空的）

UNION

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 开始位置（开始位置为0），查询的数量

SELECT * from table LIMIT 0,5

分组查询

语法：

select 分组函数，列（要求出现在group by的后面）

from 表

[where 筛选条件]

group by 分组的列表

[having 分组后的筛选]

[order by 子句]

注意：查询列表比较特殊，要求是分组函数和group by后出现的字段

-- 分组查询 -- 性别中的查询 SELECT MAX(height),gender FROM student GROUP BY gender SELECT COUNT(*),gender FROM student WHERE height>=1.80 GROUP BY gender -- 每个位置平均体重的查询 SELECT AVG(weight),lamp FROM player GROUP BY lamp -- 每个位置总共体重的查询 SELECT SUM(weight),lamp FROM player GROUP BY lamp -- 重复姓名查询 SELECT COUNT(*), NAME FROM student GROUP BY NAME HAVING COUNT(*)>1 -- 年份重复查询 SELECT COUNT(*), DATE_FORMAT(birthday,'%Y') FROM player GROUP BY DATE_FORMAT(birthday,'%Y') 

数据库设计范式

第一范式：列保证原子性

例如：联系方式：，地址，邮箱……

第二范式要有主键，要求其他字段都依赖于主键

第三范式：要求一个数据库表中不包含已在其他表中包含的非主关键字信息

-- 修改表，添加列 ALTER TABLE player ADD senum INT

这里的senum 称为外键，外键必须与另一个表的主键相关联，且数据类型一致

添加外键约束，如果没有添加约束，数据与数据之间没有任何束缚，可以随意操作

添加外键约束后，操作时就不能随意操作，要保证数据的完整性

关联查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

按功能分类：

1.自连接

2.内连接

3.外连接

左外连接：将左边的表中的全部列都列出来，右边的只列出满足条件的

右外连接：将右边的表中的全部列都列出来，左边的只列出满足条件的

子查询

含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询.

在一个查询语句中又出现了查询语句

分类：

按子查询出现的位置：

from后面：支持表子查询

where：支持标量子查询，列子查询

按功能、结果集的行列数不同：

标量子查询（结果集只有一行一列）

列子查询（结果集只有一列多行）

表子查询（结果集一般为多行多列