数据库基础

目录

• • 1. 数据库三大范式
  • 2. SQL语言
  • • 2.1 概述
    • 2.2 SQL语句分类
    • 2.3 DDL语句操作数据库
    • • 2.3.1 创建数据库
      • 2.3.2 查看数据库
      • 2.3.3 修改数据库
      • 2.3.4 删除数据库
      • 2.3.5 其他语句
    • 2.4 DDL语句操作表
    • • 2.4.1 创建表
      • 2.4.2 其他表操作
    • 2.5 sql中的运算符
    • • 2.5.1 算术运算符
      • 2.5.1 赋值运算符
      • 2.5.1 逻辑运算符
      • 2.5.1 关系运算符
    • 2.6 DML操作
    • • 2.6.1 插入数据
      • 2.6.2 修改数据
      • 2.6.3 删除数据
    • 2.7 DCL
    • • 2.7.1 创建用户
      • 2.7.2 用户授权
      • 2.7.3 其他
    • 2.8 DQL
    • • 2.8.1 简单查询
      • 2.8.2 条件查询
      • 2.8.3 模糊查询
      • 2.8.4 字段控制查询
      • 2.8.5 排序
      • 2.8.6 聚合函数
      • 2.8.7 分组查询
      • 2.8.8 LIMIT
      • 2.8.9 分页查询

1. 数据库三大范式

第一范式（1NF）：
无重复的列。当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

第二范式（2NF）：
属性完全依赖于主键 [ 消除部分子函数依赖 ]。如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。**第二范（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。**为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。
【2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分】

第三范式（3NF）：
**属性不依赖于其它非主属性 [ 消除传递依赖 ]。**设R是一个满足第一范式条件的关系模式，X 是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF。第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
【非主键列是直接依赖于主键，还是直接依赖于非主键列】

注：关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性；下一层范式都是在上一层范式基础上成立的。

2. SQL语言 2.1 概述

SQL Structuew Query Language(结构化查询语言），一种标准化的语言，允许在数据库上执行CRUD等操作
CRUD --->  Create Read Uodate Delete

2.2 SQL语句分类

1. DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：新建库、表、列等。常用的语句关键字主要包括 create、drop、alter等。
2. DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录、数据增删改。常用的语句关键字包括insert、delete、update、select等。
3. DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。
4. DQL（Data Query Language）：数据查询语言，用来查询记录、数据查询。

1、SQL语句以 ; 结尾
2、MySQL中关键字不区分大小写

2.3 DDL语句操作数据库

在没有Navicat视图化操作数据库之前，可以通过cmd窗口执行mysql命令

win + R  -- 运行cmd 
mysql -u root -p     --连接Mysql

2.3.1 创建数据库

create database -- 用于创建新的数据库
编码方式:gb2312,utf-8,gbk,iso-8859-1
//create database 数据库名 
CREATE DATAbase mydb1; 
//create database 数据库名 character set 编码方式 
CREATE DATAbase mydb2 character SET GBK; 
//create database 数据库名 set 编码方式 collate 排序规则 
CREATE DATAbase mydb3 character SET GBK COLLATE gbk_chinese_ci;

2.3.2 查看数据库

show databases; --查看当前数据库服务器中所有的数据库
show create database 数据库名;  --查看前面创建的数据库的定义信息

2.3.3 修改数据库

alter database 数据库名 character set 编码方式

2.3.4 删除数据库

drop database 数据库名

2.3.5 其他语句

Select database(); --查看当前使用的数据库
use 数据库名 --切换数据库 

2.4 DDL语句操作表 2.4.1 创建表

create table  -- 创建新表

语法：
CREATE TABLE 表名( 
	列名1 数据类型 [约束], 
	列名2 数据类型 [约束], 
	列名n 数据类型 [约束] 
);

多列之间用逗号隔开**，最后一列逗号不能加** ； [约束] 可有可无

常用数据类型：

 int：整型
 double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；默认支持四舍五入
 char：固定长度字符串类型； char(10) 'aaa ' 占10位
 varchar：可变长度字符串类型； varchar(10) 'aaa' 占3位
 text：字符串类型，比如小说信息；
 blob：字节类型，保存文件信息(视频，音频，图片)；
 date：日期类型，格式为：yyyy-MM-dd；
 time：时间类型，格式为：hh:mm:ss
 timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值, 
 	TIMESTAMP值不能早于1970或晚于2037
 	4个字节存储
 	值以UTC格式保存
 	时区转换，存储时对当前的时区进行转换，检索时再转换回当前的时区
 datetime：日期时间类型 yyyy-MM-dd hh:mm:ss 
 	支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'
 	8个字节存储
 	实际格式存储
 	与时区无关

2.4.2 其他表操作

drop table 表名; -- 删除表
show tables; -- 当前数据库中的所有表
desc 表名; -- 查看表的字段信息

alter 包含 add drop change modify 四个功能板块
alter table 表名 add 新列名 新数据类型;    -- 在表中增加一列
alter table 表名 change 旧列名 新列名 新的数据类型;  -- 修改表中某一列的列名/数据类型/数据长度
alter table 表名 modify 列名 数据类型;  -- 修改表中的某一列的数据类型/数据长度
alter table 表名 drop 列名; -- 删除表中某一列   ！！！drop 一次只能删除一列
alter table 旧表名 rename 新表名;   -- 修改表名
alter table 表名 character set 编码方式; -- 修改表的编码格式/字符集(gdk)

show create table 表名; -- 查看表格的创建细节

2.5 sql中的运算符 2.5.1 算术运算符

+，-，*，/(除法),求余(%)

2.5.1 赋值运算符

=   赋值方向：从右往左赋值

2.5.1 逻辑运算符

and(并且),or(或者),not（取非）
作用:用于连接多个条件时使用

2.5.1 关系运算符

 >,<,>=,<=,!=(不等于),=(等于),<>(不等于)

2.6 DML操作

DML是对表中的数据进行增、删、改的操作 DDL是对数据库、数据表进行增删查改的操作

2.6.1 插入数据

insert into 表名(列名) values(数据值);    -- 插入数据
insert into 表名(列名) values(第一行数据),(第二行数据),(),(); --同时添加多行数据

> 注意：列名与列值的类型、个数、顺序要一一对应。 
> 参数值不要超出列定义的长度。 如果插入空值，请使用null
> 插入的日期和字符一样，都使用引号括起来。
> 多列和多个列值之间使用逗号隔开 2.列名要和列值一一对应
> 非数值的列值两侧需要加单引号 常见错误: Data too long for column 'stusex' at row 1
> 添加数据的时候可以将列名省略->当给所有列添加数据的时候
> 此时列值的顺序按照数据表中列的顺序执行

2.6.2 修改数据

update 表名 set 列名1=列值1,列名2=列值2 ... WHERe 列名=值

2.6.3 删除数据

delete from 表名 where 列名=值;

truncate 与 delete 区别
1.delete 可以带where条件删除；而truncate只能删除整个表的数据
2.delete是DML，操作时原数据是被放在rollback segment中，支持事务回滚；而truncate是DDL，操作时不进行存储，不支持回滚
3.数据量较大时，两者的删除速度不同；delete每次删除一行并在事务日志中为所删除的每行记录一项，慢且安全；而truncate不支持回滚，所使用的系统和事务日志资源少，删除速度更快
4.随着不断地进行表记录的DML操作，会不断提高表的高水位线（HWM），DELETE操作之后虽然表的数据删除了，但是并没有降低表的高水位，随着DML操作数据库容量也只会上升，不会下降。
所以如果使用DELETE，就算将表中的数据减少了很多，在查询时还是很和DELETE操作前速度一样；而TRUNCATE操作会重置高水位线，数据库容量也会被重置，之后再进行DML操作速度也会有提升。

truncate、delete、drop 区别 
1. truncate、delete 只删除数据不删除表结构（定义）
   drop 将删除表的结构被依赖的约束（constain)，触发器（tigger)，索引（index），依赖于该表的存储过程/函数将保留为invalid状态
2. delete 是DML，这种操作会放到rollback segement中，事务提交后才生效；如果有相应的tigger，执行的时候将被触发
   truncate、drop 是DDL，操作立即生效，不能回滚，不触发tigger
3. delete 不影响表所占用的extent（内容），HWM保持原位置不动
   drop语句将表的空间全部释放
   truncate 语句缺省情况下将空间释放到minextents个extent，除非使用reuse storage，truncate会将HWM复位
4. 删除速度：drop > truncate > delete
5. 安全性：delete > others  【truncate是高危操作】
6. 删除表  -- drop tableName;
7. 删除数据 
		alter table tableName drop 列名;  --- 删除一列数据
		delete from tableName where [];   --- 一次删除一行符合条件的数据
		delter from tableName; --- 删除一整数据表，一行行进行
		truncate tableName; /truncate table tableName;    --- 删除/截断表中全部数据
8. truncate只作用于表，但truncate不能对有外键约束引用的表使用     
   delete、drop可作用于表、视图等

※ 所有的oracle段(segments，在此，为了理解方便，建议把segment作为表的一个同义词) 都有一个在段内容纳数据的上限，我们把这个上限称为"high water mark"或HWM
※ HWM是一个标记，用来说明已经有多少没有使用的数据块分配给这个segment
※ 在表上使用了truncate命令，则该表的HWM会被重新置为0；而delete只是清空数据不降低HWM

delete 操作的事务回滚

注意事项： 1. 为空的条件： is null or = ' ' 2. 两个单引号表空字符串

2.7 DCL 2.7.1 创建用户

--create user 用户名@指定ip identified by 密码;				//本机用户可登陆
create user test123@localhost identified by 'test123'
--create user 用户名@客户端ip identified by 密码;             //指定IP才能登陆
create user test456@10.4.10.18 identified by 'test456'
--create user 用户名@‘% ’ identified by 密码 				//任意IP均可登陆
create user test789@'%' identified by 'test789'

2.7.2 用户授权

--grant 权限1,权限2,........,权限n on 数据库名.* to 用户名@IP;    //给指定用户授予指定指定数据库指定权限
grant select,insert,update,delete,create on chaoshi.* to 'test456'@'127.0.0.1';
--grant all on 数据库名.* to 用户名@IP 							//给指定用户授予所有数据库所有权限
grant all on 数据库名.* to 'test456'@'127.0.0.1';
--grant all on 数据库名.* to 用户名@IP 						 //给指定用户授予所有数据库所有权限
grant all on 数据库名.* to 'test456'@'127.0.0.1';             -- * 代表数据库中的所有表

2.7.3 其他

//用户权限查询
show grants for 用户名@IP;
//撤销用户权限
revoke 权限1,权限2他,......权限n on 数据库名.* from 用户名@IP;
//删除用户
drop user 用户名@IP:

2.8 DQL

查询返回的结果集是一张虚拟表

SELECT 列名 FROM 表名 [WHERe --> GROUP BY-->HAVINg--> ORDER BY]

SELECt 要查询的列名称 
FROM 表名称 
WHERe 限定条件  
GROUP BY grouping_columns  
HAVINg condition  
ORDER BY sorting_columns  
LIMIT offset_start, row_count 

2.8.1 简单查询

//查询所有列
select * from 表名;
//查询指定列
select 列名 from 表名;

2.8.2 条件查询

=、!=、<>、<、<=、>、>=; BETWEEN…AND; IN(set); IS NULL; AND; OR;  NOT;

[not] in()  -- 范围查询
not is null;
is not null;      --非空

2.8.3 模糊查询

语法: 列名 like '表达式' //表达式必须是字符串
通配符:
_(下划线): 任意一个字符
%: 任意0~n个字符,'张%'

2.8.4 字段控制查询

// 去除重复记录
select distinct 列名 from 表名;
//两个数值型数据 因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL
 --- 把NULL转换成数值0的函数 IFNULL(列名,0)
select sal + IFNULL(comm, 0) from 表名;
//给列添加别名
select 列名 [as] 别名 from 表名;

2.8.5 排序

order by 列名 asc/desc
//asc 升序 desc 降序 默认不写的话是升序
// 多列排序：当前面的列的值相同的时候，才会按照后面的列值进行排序

2.8.6 聚合函数

聚合函数是用来做纵向运算的函数：
 COUNT(列名)：统计指定列不为NULL的记录行数；
 MAX(列名)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
 MIN(列名)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
 SUM(列名)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
 AVG(列名)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

2.8.7 分组查询

注意:如果查询语句中有分组操作，则select后面能添加的只能是聚合函数和被分组的列名

having 与 where 的区别
1.having是在分组后对数据进行过滤 where是在分组前对数据进行过滤
2.having后面可以使用分组函数（统计函数）
  where后面不可以使用分组函数

//多列分组
例子: 查询每个阶段的男生和女生个数（group by 两列） 
select gradeid, sex, count(stuid) from student group by gradeid, sex;

2.8.8 LIMIT

LIMIT用来限定查询结果的起始行，以及总行数。
 limit 开始下标,显示条数；//开始下标从0开始
 limit 显示条数；//表示默认从0开始获取数据

注意: 起始行从0开始，即第一行开始！

2.8.9 分页查询

如果一页记录为10条，希望查看第3页记录应该怎么查呢？
  第一页记录起始行为0，一共查询10行； limit 0,10
  第二页记录起始行为10，一共查询10行；limit 10,10
  第三页记录起始行为20，一共查询10行； limit 20,10
limit (pageindex-1)*pagesize,pagesize;
参数:
    pageIndex 页码值 
    pageSize 每页显示条数

查询语句书写顺序：select – from- where- groupby- having- order by-limit 查询语句执行顺序：from - where -group by -having - select - order by-limit