数据库基础

数据（Date)
数据库(Database)
数据库管理系统(DBMS)
数据库系统(DBS)

1、实体-联系-模型

• 实体：客观事物在信息世界中称为实体(Entity)，它是现实世界中任何可区分、识别的事物
• 属性：描述实体或者联系的性质特征的数据项
• 联系：反映事物内部或事物之间的关联集合。（实体与实体关联）
  • 常见的实体联系有3种：一对一联系、一对多联系和多对多联系。

2、关系模型

(1)关系的基本概念

• 关系：一个关系就是一张二维表，通常将一个没有重复行、重复列的二维表看成一个关系，每个关系都有一个关系名。
• 元组：二维表的每一行在关系中称为元组。在MySQL中，一个元组对应表中一个记录。
• 属性：二维表的每一列在关系中称为属性，每个属性都有一个属性名，属性值则是各个元组属性的取值。
• 域：属性的取值范围称为域。域作为属性的集合，其类型与范围具体由属性的性质及其所表示的意义确定。同一属性只能在相同域中取值。
• 关键字：关系中能唯一区分、确定不同元组的属性或属性组合，称为该关系的一个关键字。

(2)关系的基本特点

• 关系必须规范化，属性不可再分割。
• 在同一关系中不允许出现相同的属性名。
• 在同一关系中元组的顺序可以任意。
• 在同一关系中属性的顺序可以任意。

(3)关系运算

• 选择：从关系模型中找出满足给定条件的元组组成新的关系(从行的角度进行运算)
• 投影：从关系模型中指定若干属性组成新的关系(从列的角度进行运算)
• 连接：笛卡尔积 把两个表中所能匹配到的结果全部罗列出来 冗余

3、关系的完整约束性

1、实体完整性：主属性值(主关系键的值)不能取空值。
如：学生信息表(学号，姓名，年龄)中学号不能为空。
2、参照完整性：简单来说就是主键与外键的关系。 如：学生表与选课表之间用学号建立关系。学生表是主表，选课表是从表。若向从表中输入一条新纪录，系统会检查记录的学号是否在主表中存在。若存在，则允许操作，否则拒绝输入。
3、域完整性：限制了某些属性中出现的值，把属性限制在一个有限的集合中。
如：若属性类型是正整数，那么输入值则不能为小数等其它任何非整数。

4、关系模型的规范化

• 关系模式要满足的条件称为规范化式，称为范式。
  第一范式(1NF):如果关系R的所有属性均为简单属性，即每个属性都是不可再分的，则称R满足第一范式。 第一范式就是无重复的列。
  第二范式(2NF): 如果关系满足第一范式，且每一个非主键字段完全依赖于主键，则称R满足第二范式。
  第三范式(3NF):如果关系R满足第二范式，且非主键字段之间不存在依赖关系，则称R满足第三范式。
  存在一个部门信息表，其中每个部门有部门编号(dept_id)、部门名称、部门简介信息。
  那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等于部门有关的信息再加入员工信息表中。

5、E-R图

E-R图也称实体-联系图，提供了表示实体类型、属性和联系的方法。
矩形表示实体型。
椭圆表示实体属性。
菱形表示实体型间的联系。

6、数据库命令

1、查看现有数据库

show databases;

2、新建数据库

语法：
create database 库名;
例子：
create database mybase;

• 使用数据库

use mybase;

3、删除数据库

语法：
drop database 库名;
例子：
drop database mybase;  

4、MySQL的数据类型

• 数值
• 日期/时间
• 字符串(字符)类型

5、数据库建表

例子
create table student(
学号 varchar(20),
姓名 varchar(10),
年龄 int,
性别 char(5)
);

• 注释：
• 单行注释

--注释内容

• 多行注释

/*注释的内容*/

7、删除表

例子
drop table student;

8、修改表

1、添加列(属性)  
 alter table 表名 add 属性名 数据类型;  
2、删除列(属性)
 alter table 表名 drop 属性名;  
3、修改属性：  
 alter table 表名 modify 属性名 数据类型;  
4、修改字段名:
 alter table 表名 change 旧字段名 新字段名 数据类型

9、显示表

1、显示表结构
desc表名;

10、实体完整性

• 实体完整性是对关系中的记录唯一性。
• 定义表中的所有行能唯一的标识
• 表中主属性(字段)不能Null且不能有相同值
• 一般用主键、唯一索引、unique关键字来实现

11、添加主键约束

格式一：  
create table 表名(
    列名1 数据类型 primary keey,  
    列名2 数据类型
);  
格式二：  
create table 表名(  
    列名1 ,  
    列名2 数据类型,  
    constrint 主键约束的名字 primary key(列名1)  
);  
格式三：  
create table 表名(  
    列名1 数据类型,  
    类名2 数据类型,  
    primary key(列名1)
)

12、在创建好表的情况下,如何添加主键约束

格式一：  
alter table 表名 modify 列名 数据类型 primary key：  
格式二：  
alter table 表名 add primary key(列名);  
格式三：  
alter table 表名 add constraint 主键约束的名字 primary key(列名);  
3、删除主键约束  
alter table 表名 drop primary key;

12、实体完整性--唯一约束

• 唯一约束是指定table的列或列组合不能重复，保证数据唯一性
• 唯一约束不允许出现重复的值，但是可以为多个null;
• 同一个表可以有多个唯一约束,多个列组合的约束;
• 如果不给唯一约束名称,就默认和列名相同;
• MySQL会给唯一约束的列上默认创建一个唯一索引;

13、唯一约束 unique

1、唯一约束 unique
创建表的同时创建唯一约束

格式一：  
create table 表名(  
 列名1 数据类型 unique,  
 列名 数据类型  
):  
格式二：  
create table 表名(  
 列名1 数据类型,  
 列名2 数据类型,  
 constraint 唯一约束的名字 unique(列名1)  
);  

排空约束：not null 格式：列名 数据类型 not null 一个表里也可以有多个非空约束 `` 针对已经创建好的表的情况下,添加唯一约束 alter table 表名 add unique(列名); 删除唯一约束 alter table 表名 drop index 唯一约束的名字

###### 14、域完整性
- 域完整性是对数据表中字段属性的约束  
- 它是由确定表结构时所定义的字段的属性决定的  
- 限制数据类型,缺省值,规则,约束,是否可以为空
- 域完整性可以确保不会输入无效的值  
1、默认约束 default 一个表中可以由多个默认约束  
在创建表的同时添加默认约束

create table 表名( 列名1 数据类型, 列名2 数据类型 defaulet '默认值' )

2、在创建好表的情况下添加默认约束

alter table 表名 modify 列名 数据类型 fefaule '默认值';

删除默认约束 alter table 表名 modify 列名 数据类型; 非空约束: 1、针对已经创建好表的情况下添加非空约束 alter table表名 modify列名 数据类型 not null; 2、默认情况下每个列都是可以为空(删除非空约束) alter table 表名 modify 列名 数据类型; 检查约束 check (mysql不支持check约束)

###### 15、参照完整性--外键约束
```在创建表的同时创建外键约束
参照的表：
create table 主表1(
主表列名1 数据类型 primary key,
主表列名2 数据类型 unqiue
);
从表;
create table 从表2(
从表列名1 数据类型,
从表列名2 数据类型,
constraint 外键约束的名字 foreign key(从表列名1) references 主表1(主表列1)
);

-参照完成性

外键约束 foreginn key
主表：被引用的表 （主）
从表：引用的表 外键约束建立在从表里面 （从表）
被引用的列>要么主键约束，要么唯一约束

例子
creat table 学生(
sno varchar(20) primary key,
name varchar(10) unique,
age int not null
);

例子
create table 成绩(
sno varchar(20),
grade int,
constraint FK_sno foreign key(son) references 学生(sno)
);

例子
create table t_user01(
name varchar(10),
qq int
);

16添加外键约束

针对已经创建好的表的情况下添加外键约束

alter table从表名 add constraint 外键约束的名字 foregin key(从列表名)
references 主表(主表列名）；

在一个表中可以存在多个外键约束

删除外键约束
alter table 表名 dropforeign key 外键约束的名字;

例子
alter table t_user01 add constraint Fk_NAME foreign key (name) references 学生(name);

例子
alter table t_user01 drop forign key FK_NAME;

在默情况下每个列都时可以为空(删除非空约束)
alter table 表名 modify 列名 数据类型;

检查约束 check (mysql不支持check约束)
描述：限制某个列的取值范围 年龄18-25 性别 要么男 要么女

创建表的同时创建检查约束
create table 表名(
列名1 数据类型
列名2 数据类型 check(条件说明)
);

针对创建好表的情况 添加检查约束
alter table 表名 add constraint 检查约束的名字 check(条件);

删除检查的约束
alter table 表名 drop constraint 检查约束的名字;

参照完整性
外键约束 foreign key
主表：被引用的表(被参照的表) zhu
从表：引用的表 外键约束建立在从表里面 cong

被引用的列-->要么主键约束,要么唯一约束

在创建表的同时创建外键约束
参照的表：
create table 主表1(
主表列名1 数据类型 primary key

create alter drop 主要针对表结构来说的

insert delete update select 主要针对的时表中数据来进行操作的

17、SQL语言概念

SQL是一种数据库查询和设计语言 使得数据库可以通过命令的方式而非图形化界面的方式对表进行增删改查等操作

18、DML-INSERT

• INSERT命令可以对表进行新增记录

create alter drop主要针对表结构来说的

insert delete update select 主要针对表中的数据来进行操作
insert 插入 值和列一一对应的关系

格式一：
insert into 表名(列名1,列名2...) values(值1,值2...)
格式二：
insert into 表名 values(值1,值2...)
格式三：
insert into 表名 values(值1,值2,...)(

删除 delete
格式一：
delete from 表名：
格式二：
有条件的进行删除：
delete from 表名 wahere 条件;

例子
--删除学号是104
delete from 学生 where sno='104';
--删除教师表中 住址是济南的 老师信息
delete from 教师表 where address='济南';
--向成绩表中添加一条记录 101
desc 成绩;
insert into 成绩 values('101',98);
insert into 成绩 values('102',68);

19、DML--UPDATE

• UPDATE命令可以对表的数据进行修改

例子：把教师表的工资全部改为1000
update 教师表 set salaary=1000;
例子：把教师表的工资在原基础上加1000
update 教师表 set salary=salary+1000;

20、查询数据

SELECT语句语法如下
SELECT select_list
FROM table_list
[WHERE search_conditions]
[GROUP BY group_by_list]
[HAVING search_conditions]
[ORDER BY order_list[ASC|DESC]]

查询：select

基本格式：
select 列名 from表名;
select 列名1,列名,列名3... from 表名

select字句-->要查询的的列
from字句-->要用到的表

*所有的意思
select*from 表名;

21、使用关键字DISTINCT查询

• 如果用户希望在查询返回结果中删除重复就行,就可以在SELECT字句中使用DISTINCT关键字

22、使用别名查询

• 使用select语句查询数据时,可以使用别名的方法根据需要对数据显示的标题进行修改。
  1、在列的表达式中给出别名
  SELECT 列名 '别名' from 表名;
  2、使用AS关键字来链接列表达式和指定的别名 SELECT 列名 as '别名' FROM 表名;

23、选择查询

选择行的基本语法： SELECT SELECT_LIST
FROM TABLE_LIST WHERE SEARCH_CONDITIONS;
查询条件：
select 列名 from 表名 where 条件;
如果在查询过程中,有多个条件 可以使用and或者or进行连接
and连接同时满足 , or连接只需要满足其中一个

• 比较运算符

24、运算符

查询籍贯式beijing并且性别是nan的学生信息；

SELECT学号,班级编号,姓名,性别,籍贯  
FROM学生信息  
WHERE 籍贯= 'beijing' AND 性别= 'nan'

25、选择查询

范围查询搜素的条件

• 范围搜索返回介于两个指定值之间的所有值,可分为包括范围和排除范围两种类型。用BETWEEN,NOT BETWEEN

查询成绩在70到80之间的学生信息：
SELECT 学号,课程编号，成绩
FROM 成绩表
WHERE成绩 BETWEEN 70 AND 80;
(WHERE 成绩 NOT BETWEEN 70 AND 80)

• 列表搜素条件
  IN关键字使用户可以选择与列表中的任意值匹配的行。

查询籍贯是beijing和上海的学生
SELECT学号,班级编号,姓名,性别,籍贯
FROM 学生信息
WHERE籍贯IN('beijng','shenzhen')

and连接 是同时满足 or连接 只需满足其中一个。
范围搜索的条件：
在范围之内：
select 列名 from 表名 where 列名 between 开始值 and 结束值;
不在范围之内：
select 列名 from 表名 where 列名 not between 开始值 and 结束值;

列表搜素条件
in 只要匹配到括号里面任意一个值就会有查询结果。
select 列名 from 表名 where 列名 in(值1,值2,值3...);
not in:格式：where 列名 not in (值1,值,值3...)

26、选择查询

例子
--查询籍贯是beijing和shanghai的学生
select * from 学生信息 where 籍贯 in ('beijing','shanghai');
--查询姓张的学生个人信息
select *from student where sname like '张%';

27、涉及空值的查询

28、聚合函数

例子
--在sc表中，分别求出总成绩的 总和 平均值 最大值 最小值
select sum(grade) as '总和' from sc;
select avg(grade)as '平均成绩' from sc;
select max(grade)as '最高分' from sc;
select min(grade)as '最低分' from sc;

29、MySQL的行数限定

30、数据分组

GROUP BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组。

语法格式：
SELECT column_name,aggregate_function(column_name)
FROM table_name
GEOUP BY column_name
分组:select 列名·,聚合函数 from 表名 group by 列名：having 条件(聚合函数);

31、分组条件

• having 通常与group by字句一起使用。相当于一个用于数组的where字句,制定组的搜素条件。
• having字句可以包含聚合函数，where不可以

32、排序

• ORDER BY 语句用于对指定的结果集进行排序
• ORDER BY 语句默认升序
• 降序排序可以用DESC关键字
• 查询成绩表并进行排序： select * from成绩表 oder by成绩[desc];

排序：
select 列名 from 表名 order by 列 desc|asc;

desc 降序
asc升序(默认)

33、MySQL函数

MYSQL中函数分为四大类：

• 字符串函数
• 数字函数
• 日期时间函数
• 控制楼函数 1、 字符串函数

2、数学函数

3、日期时间函数

4、控制函数

select now();
select current_date();
select current_time();
select to_days(now());  --0
select dayofyear(now());
select week(now());
select if(9<8,'你好','你不好'）；
select if(null, '你好','你不好');

34、表连接

在实际查询应用中,用户所需的数据并不全在一个表中,可能存在多个表中,这时就需要使用多表查询。

在数据库应用中,经常需要从多个相关的表中查询数据,这旧需要进行表连接。

内部链接两种语法形式：
SELECT select_list FROM 表名1,表名2,
WHERE表1.列=表2.列
或者
SELECT select_list FROM 表一 [INNER] JOIN 表2 ON 表1.列=表2.列

表连接：
1、内连接(最常用)
格式一：
select 表名1.列名1.表名1.列名2.表名2.列名1.表名2，列名2
from 表名1，表名2
where 表名1.列=表名2.列;
格式二;
select 表名1.列名1.表名1.列名2.表名2.列名1.表名2.列名2..
from 表名1 inner join 表名2
on 表名1.列=表名2.列;

夺表查询首先要在各个表之间进行连接。 用户在进行基本连操作时,可以遵循以下基本原则

select字间距列表中,每个目标列前都要加上基表名称
from字句应包括所有使用的基表
where 自己应定义一个连接

使用外部链接

• 外部链接会返回FROM字句中提到的至少一个表或视图中所有行。
• 外部连接分为做左外部链接,右外部连接

35、外部链接

左外部连接对连接条件中左边的表不加限制;
右外部连接对连接条件中右边的表不加限制。 1、左外连接 使用关键字LEFT OUTERJOIN 关键字对两个表进行连接左外连接的查询结果中包含指定左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有找到匹配的行，则结果集中的右表的相对应的位置为NULL.

外连接 左连接格式:left outer join 
select表名1列名1表名1列名2表名2列名1，表名2列名2...
from 表名1leftouter join表名2
 on 表名1.列=表名2.列:

2、右外连接
使用RIGHT OUTERJOIN 关键字对两个表进行连接，右外连接是左外连接的反向连接，只不过在查询结果集中包括的是指定右表的所有行。如果右边的某行在左表中没有找到匹配的行，则结果集中的左表的相对应位置为NULL.

36、子查询

相关子查询(单值子查询) 这样子的子查询只返回一个值，然后将一列值与查询返回的值进行比价。

在INSERT语句中使用SELECT字句可以将一个或多个表或视图中的值添加到另一个表中。使用SELECT字句还可以同时插入多行
INSERT语句中使用SELECT字句的语法形式为：
INSERT[INTO]table_name[(column_list)]
SELECT select_list FROM table_name

on 表名1.列=表名2.列
子查询在其他查询结果的基础上提供了一种有效的方式来表示where字句的条件。
子查询的 SELECT 查询总是使用圆括号起来。
格式：select 列名 from 表名 where 列名 in(select 列名 from 表名 where 条件);

37、子查询分为两种：

• 嵌套子查询
  在MySQL中子查询是可以嵌套使用的,并且可以在一个查询中嵌套任意多个子查询,即一个子查询中还可以包含另一子查询这种方式称嵌套子查询

38、使用SQL语句创建视图

使用CREATE VIEW语句来创建视图,语法如下
CREATE VIEW 视图名字 AS SELECT 语句

39、修改视图

语法：ALTER VIEW 视图名
AS
SELECT

视图的插入
insert into 视图的名字 values(值1,值2...)
视图的数据修改也会影响原表的数据。
update 视图的名字 set 列=值 where 条件;
删除视图中的数据：
delete from 视图的名字 where条件;
删除视图：
drop view 视图的名字

40、删除视图

带有条件删除视图中的数据: DELETE FROM VIEW班级信息2 WHERE人数=80

删除视图
语法:DROPVIEW<视图名>

例:删除视图VIEW 班级信息2
DROP VIEW VIEW 班级信息2;

删除两个视图

DROP VIEW 视图1 视图2;

删除视图中的数据:
delete from 视图的名字where 条件;
删除视图:
drop view视图的名字:

41、索引的基本概念

MySQL的索引是对数据库表中一个或多个列的值进行排序的结构。索引有助于更快的获取信息。是用来定位的。

对表中的列是否创建索引，以及创建何种索引，对于查询的响应速度会有很大差别。创建了索引的列几乎是立即响应，而不创建索引的列则需要较长时间的等待。

索引的作用

①加快数据检索;
②保证数据的唯一性;
③ 实现表与表之间的参照完整性;
④在使用GROUPBY、ORDERBY子句进行查询时，利用索引可以减少排序和分组的时间。

• 2、索引的基本类型
  1.普通索引
  2.唯一性索引
  3.全文索引
  4.单列索引
  5.多列索引
  6.空间索引
• 3、设计索引
  设计索引时，应考虑以下数据库准则。 (1)一个表如果建有大量索引，会影响INSERT、UPDATE和 DELETE语句的性能。
  (2)避免对经常更新的表进行过多的索引，并且索引应保持较窄。
  (3)使用多个索引可以提高更新少而数据量大的查询的性能。
  (4)对小表进行索引可能不会产生优化效果。
• 4 创建索引 ```

使用T-SQL语句创建索引
也可以使用CREATEINDEX语句来创建索引，语法如下:
CREATEUNIOUE INDEXindexname
ON表名(column[...n])

创建索引 create index索引的名字 on表名(列名); 创建唯一索引: create unique index索引的名字on表名(列名);

1、为student表的sno列创建普通索引 create index IX stu sno on student(sno);

2、为student表的sno列创建唯一索引 create unique index IX_stu_sno on student(sno);

- 5、 删除索引  
可以通过DROP INDEX语句删除索引，

DROP INDEX index_name on table_name; drop index 索引的名字 on 表名;