数据库技术

什么是数据库技术

 科学的组织和存储数据；
 高效的获取和处理数据；

SQL

 结构化查询语言
 专为数据库建立的操作命令集，数据库专用
 使用时，只需要发出”做什么“的命令

DB,DBS,DBMS之间的关系

数据(Data)：

 客观事物的符号表示。图形符号、数字、字母、数据
 数据是数据库存储的基本对象

数据库(DB)

 长期储存在计算机内，有组织、可共享的数据集合
 较小的冗余度(重复)、较高的数据独立性和易扩展性(可修改)，并为各个用户共享

数据库系统(DBS)

 一般由数据库、数据库管理系统(开发工具)、应用程序、数据库管理员和用户构成
DBS = DB + DBMS + 应用系统 + DBA + 用户

 网状模型(图形结构)、层次模型(树形结构)、关系模型(简单二维表)
 信息：以某种数据表示。
  信息的3种世界：现实世界、信息世界(观念世界)、数据世界(信息世界的产物)

数据库管理系统(DBMS)

 位于应用程序和存储数据之间的数据管理软件。基础软件、大型软件系统。

DBMS优点

 较少数据冗余，相互关联数据集合
 程序与数据相互独立
 数据安全、可靠、正确数据并发使用，保证一致性

数据库定义功能—DDL语言(表)

 create
 alter
 drop

数据库操作(纵)功能—DML语言：

 insert
 delete
 update
 select

数据库保护功能

 数据库数据恢复、数据库并发控制
 数据完整性；数据安全性

数据库维护功能

 数据库数据导入、转换、存储、数据库性能监控

关系模型

实体—联系—模型

 实体：客观事物在信息世界并可以相互区别的事物，被称为实体。实体构成的集合是实体集。具有相同属性
 属性：描述实体的数据项
 联系：事物内部或事物之间的关联集合；一对一(家教和孩子)；一对多(老师和学生)；多对多(学生和课程)

关系：没有重复行、重复列的二维表。

  每个关系都有一个关系名(表明)。每一行在关系中称为元组，一个元组对应表中一个记录。每一列在关系中称为属性，也叫字段。
  域：属性的取值范围。属性值的集合。同一属性只能在相同域中取值。
  关键字：唯一能区分确定不同元组的属性或属性组和。可以把一个列或者多个列设为关键字。唯一，不重复、不为空—可互推
  关系模式：对关系的描述。关系名(属性1,属性2,……)
  关系运算：选择(行)，投影(列，指定若干属性组合成新表)和连接(笛卡尔积选取满足一定条件的元组)

关系型数据库

 行和列交叉组成的二维表格
 表中一行称为元组，或者记录
 表中列称为属性，或者字段，属性名不重复
 列取值范围称为域。
 表中任意两行不能相同
 能唯一标识表中不同属性或属性组称为主键或者联合主键

关系的完整性约束(表数据限制)

实体完整性

 主键(主属性)不为空；一个表中有且只有一个主键约束
 联合主键：多个列为一个主键整体
 unique唯一约束：可为空，可以有多个。

--已经存在的表设置主键:3种
alter table teacher modify id int primary key;
alter table teacher add primary key (id);
alter table teacher add constraint pk_id primary key(id,name);--联合主键
--删除主键约束
alter table teacher drop primary key;

 索引，相当于书本目录

参照完整性

 两个表。其中一个表的操作参照另一个表，主表有，则可操作。要求主表的列必须是主键或唯一。

--创建表时，外键约束
--主表
create table 表1(
    列1 数据类型 primary key,
    列2 数据类型
);
--从表
create table 表2(
    列1 数据类型，
    列2 数据类型，
    constraint 外键约束名 foreign key(从表列名1) references 主表表名1(列名1)--两个列名可以不一致，数据类型和内容必须一致

--删除外键约束
alter table 表名 drop foreign key 外键名字;
);

用户定义(域)完整性

 限制某个列的取值要求。固定在有效集合范围之内。

--唯一约束，生成索引
create table 表明(
    列名1,数据类型,--/列名1，数据类型 unique
    列名2,数据类型,
    constraint 唯一约束名字 unique(列名)
);

--删除唯一约束
alter table 表明 drop index 约束名
--eg:
alter table teacher drop index addess;

--默认约束，创建表时
create table 表名(
    列1 数据类型 default'字符串或日期形式加单引号，数字直接写'
);
--默认约束，已经存在的表
alter table 表名 modify 列名 数据类型 default 值;

--删除默认约束
alter table 表名 modify 列名 数据类型;

--非空约束
create table 表名(
    列1 数据类型 not null
);

--已经存在的表
alter table 表名 modify 列名 数据类型 not null;

--删除非空约束
alter table 表名 modify 列名 数据类型;

三范式

 关系模式(表)要满足的条件称为规范化形式，简称范式
 目的是消除存储异常，减少数据冗余，保证数据完整性和存储效率。

第一范式:1NF

 关系R中，每个属性不可再分，无重复列则满足1NF

第二范式:2NF

 1NF基础之上，每一个非主键字段完全依赖主键。通过主键可以查询其他非主键字段。

第三范式:3NF

 2NF基础之上，非主属性之间不存在依赖关系。表中不包含已经在其他表中包含的非主属性关键字
一个基本的关系型数据库要满足第一范式。一个完整的关系型数据库要满足第三范式

E-R图：实体联系图

 描述现实世界的概念模型
 矩形表示实体型；椭圆表示实体属性；菱形表示实体间关系。用线段连接。m,n,1表示关系。eg: 1:1 ，1:m或1:n，m:n

常见数据库

oracle

 分布式数据库系统
 可移植性好

Mysql

 开源、免费、快速、可靠和易于使用

SQL Server

 web流行的用于存储数据的数据库
 易用性好但只能在Windows上运行

Mysql数据库的使用

 体积小、速度快，成本低，可移植性(跨平台)

数据类型

数值型

 int 整型
 float 单精度浮点数
 double 双精度浮点数
 decima(总长度，小数位)l 小数值
  decimal(5,2) 123.45

时间

 date
 time
 year
 datetime:YYYY-MM-DD HH:MM:SS；混合日期和时间值

字符串

 CHAR(x)
 VARCHAR(x)
 TEXT

基本操作

1)查看现有数据库

show databases;

2)新建数据库

create database stu;

3)删除数据库

drop database stu;

4)注释

--注释
/*多行注释*/

5)创建表

create table student(
    stu_no int primary key,
    stu_name varchar(5),
    stu_sex char(2),
    stu_age int
); 

create table employee(
    ep_no varchar(7) primary key,
    ep_name varchar(10),
    salary decimal(5,2),
    ep_date datetime
);  

create table teacher(
    id int,
	name char(20),
	address varchar(50),
	salary double(10,2),
	birthday date,
	constraint pk_id primary key(id)
);

6)删除表

drop table student,employee;--删除多个表

7)修改表

--添加列
alter table student add class varchar(10);
--删除列
alter table student drop class;
--修改列数据类型
alter table employee modify ep_no int;
--修改字段名
alter table employee change ep_no ep_ord int;

8)查看表结构

desc student;

9)增删改查表内容

--插入记录，值和列一一对应
insert
into teacher(全插可省略具体列名)
values(1000,'wang','xian',100,'2000-01-30'),
(1001,'zhang','xian',150,'1999-01-01');

--删除记录(删除数据，表存在)
delete
from 表;--删除全部表数据

delete 
from 表;
where 条件;--带条件删除
--被从表引用的主表数据不可删除

--修改记录
update 表名
set 列名1 = 值1,列名2 = 值2;

update 表名
set 列名 = 值
where 条件;

--查询记录。按特定组合、条件或次序进行检索
select 列1,列2
from 表名
where 条件;

select *--全部列
from 表名;

--去重查询(distinct)
select distinct 列名
from 表名
where 条件1 and/or 条件2;

--别名查询
select 列 '列别名'--select 列 as '新列名'
from 表名;

--范围搜索查询
select *
from 表名
where age (not)between x and y;--x<=i<=y

--列表搜索条件
select *
from 表名
where 列名 IN/NOT IN (值1,值2);

--模糊查询:%代表0个多个字符。_代表一个字符
select *
from 表名
where 列名 LIKE/(NOT LIKE) %/_ 

--空值查询-不确定的值
select *
from 表名
where 列名 IS (NOT) null

--聚合函数查询
select 聚合函数 (as)别名
from 表名;
--多个聚合函数查询
select max(grade)'最高分',min(grade)'最低分'
from 表名
--除count外，其他函数均忽略空值

--查询行数限定。mysql行数从0开始
select *
from 表名
LIMIT 5;(取5行)
LIMIT [开始，不写为0],nums(总共查几行):limit 2,6(从3行开始，取6行)

--分组查询,按列名分组。先查询，后分组
select *
from 表名
group by 列
having 条件;
--group by 一般和聚合函数结合使用，对查询结果进行分组.
--having子句可以包含聚合函数，条件必须是出现在查询结果中的。

--排序查询
select *
from 表名
order by 列名1 ASC(默认升序)/DESC(降序),列名2 ASC(默认升序)/DESC(降序);
eg:order by grade ASC,sno DESC;

10)表连接

 所需数据不止在一张表中，需要多个表做结合的查询。
 确定表；确定列，哪张表的哪个列；确定关联的列；确定题目额外要求；

--内部连接：内连接(两个表求交集)
--列名可不一样，数据类型和内容必须一致
select 表1.列1,表1.列2,表2.列1,……
from 表1,表2
where 表1.列 = 表2.列;

--等值连接，条件中只有=
select a.列1,列2,b.列1,列3……
from 表1 a [INNER] JOIN 表2 b
ON a.列 = b.列 ;

--非等值连接，条件中除=外，有其他比较运算符
select s.学号,姓名,班级编号,g.成绩
from 学生信息 s inner join 成绩表 g
on s.学号 = g.学号
where g.成绩>=40
order by g.成绩 DESC;

--多表连接
表1关联表2，表2关联表3，依次关联，最终实现多表互相连接。
select student.sno,sname,grade,cname
from student inner join sc
on student.sno = sc.sno inner join course on sc.cno = course.cno

--外连接：至少返回一个表的所有内容
--左外连接，左表不限制，返回左表所有行
--返回a表所有行,b表无匹配到的显示为null。
select a.列1,列2,b.列1,列3
from a left [outer] join b 
on a.列1 = b.列2;
--右外连接，右表不限制，返回右表所有行
--返回b表所有行，a表无匹配到的显示为null。
select a.列1,列2,b.列1,列3
from a right [outer] join b 
on a.列1 = b.列2;

11)子查询

 不等于：<>
 子查询可以嵌套在select,insert,update,delete语句中，小括号表示
 where要什么，子查询就查询什么
 子查询结果唯一，where后用=；反之多数情况下使用 IN

--聚合函数多使用子查询
--嵌套子查询。子查询中还包含其他子查询
select 列名
from 表名
where 列名 in(select 列名
              from 表名
			  where 列名 in(select 列名
                            from 表名
			                where 列名))
--相关子查询，查询结果有且只有一个
select 列名
from 表名
where grade = (select avg(grade)
               from sc);
               
--insert和select结合使用
insert 
into 表名
select *
from 表名
where 籍贯 = 'beijing';

--update和select结合
update sc
set grade = grade + 5
where sno in (select sno
              from student
              where daept = '计算机系');

12)视图

 视图:根据某个实际表查询出来，生成的虚表

--创建视图
create view 视图名 as select 语句

--查询视图
select *
from view_1;

--更新视图
update view_1
set 人数 = 100
where 年级 = 'er';

--插入数据
insert 
into view_1
values(值1,值2,值3……)

--修改视图
alter view view_1 as select * from 表;

--删除视图数据
delete from view_1 where 条件
--删除数据
drop view view_1

13)索引

 类似于书本目录
 对数据库表中一个或多个列的值进行排序的结构。
 加快查询速度
 保证数据的唯一性。(唯一约束)
 实现表与表的参照完整性。(外键约束参照列)
 在使用group by和order by时，索引减少分组和排序的时间。(列设置索引)

Mysql函数

字符串函数

 length():字符串字节长度

--字节长度，mysql中一个汉字占3个字节，字母占1个字节
select length(adept)
from student;

 char_length():字符串长度

select sdept,char_length(sdept)'长度'
from student;

 MID()函数:从某个位置获取某个长度的字符

--从sname字符串中第2个位置开始(最小为1)，截取1个
select sname,MID(sname,2,1)
from student;

数学函数

 round()函数

--mysql数据库中，默认保留4位小数
--round(值,3)——(数据，保留小数位)
select round(34.5678,3)--34.568
select round(34.5678)--35,保留整数部分

select round(avg(grade),2)
from sc;

 least()函数:求取最小数字
 greatest()函数:求取最大数字

--后面必须传具体值
select least(1,2,3,4,5)
select greatest(1,2,3,4,5)

日期时间函数

 now()函数:当前日期和时间
 current_date()函数:获取日期
 current_time()函数:获取时间
 to_days()函数:日期转化成总天数

select to_days(now());

select dayofyear(now());

select week(now());

控制函数

 IF()函数:

select if(布尔表达式,'你好','你不好');

select if(null,'你好','你不好');

 IFNULL()函数

--第一个参数为空输出第二个参数，否则输出第一个
select ifnull(null,'你不好')

select ifnull('你好','你不好')