数据库

 

 

数据库 E-R模型

1、什么是数据库？

数据库是数据的仓库。

 

（1）、与普通的“数据仓库”不同的是，数据库依据“数据结构”来组织数据，因为“数据结构”，所以我们看到的数据是比较“条理化”的

（比如不会跟以前的普通文件存储式存储成一个文件那么不条理化，我们的数据库分成一个个库，分成一个个表，分成一条条记录，

（2）、这些记录是多么分明）也因为其“数据结构”式，所以有极高的查找速率（比如B-Tree查找法），（由于专精，可以根据自己的结构特性来快速查找，

所以对于数据库的查找会比较快捷；不像普通文件系统的“查找”那么通用）

（3）、如果与EXCEL来比的话，能明显的看出数据库的好处，我们能给一个个“字段”添加“约束”（比如约束一列的值不能为空）

（4）、数据库与普通的文件系统的主要区别（起因）：数据库能快速查找对应的数据

（5）、常说的XX数据库，其实实质上是XX数据库管理系统。数据库管理系统是一个软件，是数据库管理的程序实现。

 

数据库管理系统（DBMS database management system ）

 

数据库和数据库管理系统有什么关系？

Excel表格，和Excel软件，的关系？

数据库是数据的有规则、有序的集合

数据库管理系统是一个软件系统，提供操作数据库的相关接口。

 

2、数据库的分类：

（1）、关系型的数据库。

所谓关系型数据库，是指采用了关系模型来组织数据的数据库。

 

关系：通常说的表名。

元组：可以理解为二维表中的一行，在数据库里面通常称作一个记录。

属性：可以理解为二维表中的一列，在数据库里面通常称作一个字段。

域：  是属性的取值范围。

主键（关键字）：一组可以唯一标识元组的属性。数据库里面通常称为主键，由一个或者多个列组成。

面试常考点：

ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、

持久性（Durability）。一个支持事务（Transaction）的数据库，必须要具有这四种特性，否则在事务过程（Transaction processing）

当中无法保证数据的正确性，交易过程极可能达不到交易方的要求。

 

原子性

整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，

会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

 

一致性

一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并

发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性

(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，

在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。

 

隔离性

隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，

执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止

事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

 

持久性

在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。//数据库建了以后就会永久存在那里，除非被删除；

 

（2）、非关系型数据库。

非关系型数据库，又被称为NoSQL（Not Only SQL )，意为不仅仅是SQL（ Structured QueryLanguage，结构化查询语言)，更为恰当

当然，我们也不能否认，这类数据库确实在数据关联之间更为自由，约束条件更少，（甚至没有）

 

课外阅读：

相关连接：https://www.cnblogs.com/progor/p/8729798.html

相关连接：https://blog.youkuaiyun.com/longxingzhiwen/article/details/53896702

 

3、表（关系，relation）

表是行和列是怎么样组织起来的。

 

 

 

4、E-R模型（面试常考点）

E：Entity 代表一个实体。指的是要描述的对象以及对象的属性。

R：Relation，关系，实体和实体之间的关系。

 

 

 

5、SQL语句的使用。

 

（1）、DDL （data define language）数据定义语言。

对于数据库的操作

创建一个数据库/删除数据库

创建一个表/删除一个表

创建一个索引/删除一个索引。

 

 

（2）、DML（data manipulate language） 数据操作语言

 

增、删、查、改

 

 

创建一个表：

 

语法：

CREATE TABLE  表名

(

列名1  数据类型  约束，

列名1  数据类型  约束，

列名1  数据类型  约束，

。。。。。

);

 

数据类型：

整数：

interger(size) 保存整形的数据。

int(size)

smallint(size)

tinyint(size)

 

小数：

decimal(size,d) size规定数字的最大位数，d代表有多少位小数

numric(size,d)

字符：

char（size） 固定长度，size表示长度

varchar（size）  可变长度，size表示是最大的长度

 

日期：

date（yyyymmdd） 20180808

 

约束：

NOT NULL  不能为空

UNIQUE    唯一

PRIMARY KEY 主键：每一个表都必须有且只有一个主键，用来标识唯一的一条记录。

 

CHECK   用于限制列值的范围

 

DEFAULT 默认值

 

 

创建一个表：CREATE TABLE score(num int(4) PRIMARY KEY, name varchar(32) NOT NULL, score  numric(4,2));//注意大都需要在结尾加分号；

 

查看表是否创建成功： sqlite> .tables

查看数据库文件： sqlite> .databases

  将数据库文件导出： sqlite3  xjm.db .dump  > xjm.sql

删除一个表： drop table abcd（表名）

往表中插入数据： insert into score values(1,'xjm',90);//这些命令笔试会考

 

语法：INSERT INTO 表名  VALLUES（值1，值2，值3，....）；

      INSERT INTO 表名  （列名1，列名2，列名3...）VALUES（值1，值2，值3，....）；

 

查看表中的数据： select * from score；

语法：

SELECT * FROM 表名  （*号代表查看表中所有的数据）

SELECT * FROM 表名  where 条件  {如果有多个条件，可以使用 AND/OR 进行组合查询 }

select * from score where name='xjm' AND num=1;

 

删除数据：

语法：

DELETE FROM 表名 WHERE 列名 运算符 值；

delete from score where num=3; {如果有多个条件，可以使用 AND/OR 进行组合查询 }

 

修改数据：

语法：  UPDATE 表名 SET 列名1=新值，列名2=新值，。。。 WHERE  列名=哪一行；

update score set name='abc' where num=2;

 

运算符：

=   等于

<>  不等于

>   大于

<   小于

BETWEEN  *  AND *

AND 这是一个逻辑运算，并且的意思

OR  这是一个逻辑运算，或的意思。

 

1、小于60分的同学，让其分数变为60分。

2、比如有一个同学叫abc，将这个同学的分数改为80。

3、比如在90-100分的同学，等级改为A;

 

update  score set score=60 where score < 60;

update  score set score=60 where name='abc';

 

update  score set level='a' where  score  between 90 and 100

update  score set level='a' where  score > 90 and  score < 100//记住，面试可能会考；

 

 

虚拟机示例代码：(注意数据库编程这里不能移动光标，错了只能删除，但是可以复制，不能靠光标查找上条命令，所以写的时候要特别小心，退出用.exit/ctrl+d/三次ctl+c

gec@ubuntu:~$ cd /mnt/hgfs/share/sql

gec@ubuntu:/mnt/hgfs/share/sql$ ls

caobo.c   caobo.sql  ll.db                 sqlite-autoconf-3110100.tar.gz

caobo.db  hh.db      sqlite3.11.1编译.txt  sqlite-doc-3110100.zip

gec@ubuntu:/mnt/hgfs/share/sql$ sqlite3 qw.db//创建数据库文件

 CREATE TABLE score(num int(4) PRIMARY KEY,system char(1) NOT NULL,name varchar(32) NOT NULL,score numric(4,2));//创建名字为score的表

sqlite> .tables

score

sqlite> .databases

seq  name             file                                                      

---  ---------------  ----------------------------------------------------------

0    main             /mnt/hgfs/share/sql/qw.db                                 

sqlite> insert into score values(1,'A','CAOBO',90);//插入数据

sqlite>  insert into score values(2,'B','HREHUAN',80);

sqlite> insert into  score values(3,'C','XOOP',50);

sqlite>  insert into  score values(4,'D','ASAS',10);

sqlite> select *from score//查看表

   ...> ;//结尾需要分号;

1|A|CAOBO|90

2|B|HREHUAN|80

3|C|XOOP|50

4|D|ASAS|10

sqlite> select *from score where name='caobo';//名字打错所以没反应

sqlite> select * from score where name='CAOBO';

1|A|CAOBO|90

sqlite>

sqlite>

sqlite> update score set score=100 where name='CAOBO';//依据条件查找修改

sqlite> select *from score;

1|A|CAOBO|100

2|B|HREHUAN|80

3|C|XOOP|50

4|D|ASAS|10

sqlite>

 

 

 

设置主键为，自动增加：

create table TEST (id INTEGER PRIMARY KEY AUTOINCREMENT,name varchar(12));

insert into TEST(10,'xjm'); //第一次添加，可以设置初值，也可以不设置。

insert into TEST(NULL,'abc'); //后面就不需要再设置ID的值了。

 

查找最大的ID：

select * from TEST order by id desc limit 1;   

order by id desc 表示按id进行降序排列，limit 1 表示只取记录的第一条，

如果想取前3条可以写成 limit 3；

如果想取第10条到20条，则改为： limit 10,20;

 

 

SQLITE LIKE 语句

LIKE语句用来模糊搜索文本

有两个符号：

% 百分号表示零个，一个或者多个数字或者字符

_ 下划线代表一个单一的数字或者字符。可以组合使用。

 

select * from TABLE where name  like '200%' 查找以200开头的任意值

select * from TABLE where name  like '%200%' 查找任意包含200的任意值//面试时可能会考，记住这种命令

select * from TABLE where name  like '_00%' 表示查找第二位和第三位为00的任意值

select * from TABLE where name  like '2_%_%' 表示查找以2开头，且长度至少为3个字符的任意值

select * from TABLE where name  like '%2' 表示查找以2结尾的任意字符

select * from TABLE where name  like '_2%3' 表示查找第二位为2，且以3结尾的任意值

select * from TABLE where name  like '2___3' 表示长度为5位数，且以2开头以3结尾的任意值

 

 

 

 

6、使用sqlite3提供的接口使用C语言去实现对数据库的操作。

 

#includle <sqlite3.h>

编译命令： gcc -o sqlite sqlite.c -lsqlite3

 

（1）、打开或者创建一个数据库对象。

int sqlite3_open(

   const char *filename,   /*  数据库的文件名 Database filename (UTF-8) */

   sqlite3 **ppDb          /*  返回数据库的对象。  OUT: SQLite db handle */

);

 

sqlite3 *ppdb;

int ret = sqlite3_open("test.db", &ppdb );

 

返回值：如果成功，返回这个：SQLITE_OK

 

 

（2）、创建好这个数据库之后，我们调用exec函数去执行sql语句就可以了。

 

int sqlite3_exec(

  sqlite3*,                                  /* An open database 对应我们第一步打开的数据库 */

  const char *sql,                           /* SQL to be evaluated 将SQL语句写在这个地方 */

  int (*callback)(void*,int,char**,char**),  /* Callback function 这是一个回调函数，不需要回调的时候设置为NULL，比如说查询的时候，需要回调，就写一个函数，把其函数指针放在这里 */

  void *,                                    /* 1st argument to callback 回调函数的第一个参数 */

  char **errmsg                              /* Error msg written here 返回错误信息 */

);

 

返回值：如果成功，返回这个：SQLITE_OK

 

（3）、处理完成之后，关闭数据库对象。

 

int sqlite3_close(sqlite3*);

 

 

 

（4）、回调函数

为什么需要这个回调函数？它有什么作用？

 

int (*callback)(void*pv,int argc,char **argv,char **col);

第一个参数：是你传递给这个回调函数的参数。

后面三个是sqlite3_exec在调用这个回调函数传递的。

第二个参数：表头的列数。

第三个参数：数据的数组指针。

第四个参数：表的表头数组指针。

 

这个回调函数不是一定需要用到的，当我执行的是查询语句的时候，才需要用到，把查询到的结果显示出来。

像插入、删除、修改这些操作不需要用到回调函数，在sqlite3_exec函数的参数里面，回调函数的位置给NULL。

 

 

int main(int argc,char **argv)

{

 

}

 

int callback( void *pv, int argc ,char **argv, char ** col)

{

int i;

for(i=0;i<argc;i++)

{

printf("%s = %s \n",col[i],argv[i]); //col[i]对应是表头 ，argv[i]对应是查询到的数据

}

return 0;  //返回0表示查询成功，如果返回其他值，表示中断查询

}

 

 

 

 

 

 

 

4、SQL问答题
SELECT * FROM TABLE
和
SELECT * FROM TABLE 
WHERE NAME LIKE '%%' AND ADDR LIKE '%%' 
AND (1_ADDR LIKE '%%' OR 2_ADDR LIKE '%%' 
OR 3_ADDR LIKE '%%' OR 4_ADDR LIKE '%%' ) 
的检索结果为何不同？
答: 前者检索全部，后者有三种情况检索不出：NAME＝null或ADDR=null或1_ADDR LIKE  2_ADDR 3_ADDR 4_ADDR其一为null.

前者检索所有记录，后者只能检索出 NAME 和ADDR中非Null的记录。

 

5、SQL问答题
表结构：
 1、 表名：g_cardapply
 字段(字段名/类型/长度)：
 g_applyno varchar 8；//申请单号（关键字）
 g_applydate bigint 8；//申请日期
 g_state varchar 2；//申请状态
 2、 表名：g_cardapplydetail
 字段(字段名/类型/长度)：
 g_applyno varchar 8；//申请单号（关键字）
 g_name varchar 30；//申请人姓名
 g_idcard varchar 18；//申请人身份证号
 g_state varchar 2；//申请状态
其中，两个表的关联字段为申请单号。
题目：
1、 查询身份证号码为440401430103082的申请日期
select A.g_applydate 
from g_cardapply A inner join g_cardapplydetail B on A.g_applyno = B.g_applyno
where B.g_idCard = '440401430103082'

2、 查询同一个身份证号码有两条以上记录的身份证号码及记录个数
select g_idCard,count(*) as Cnt from g_cardapplydetail 
group by g_idcard
having count(*) > 1

3、 将身份证号码为440401430103082的记录在两个表中的申请状态均改为07
update g_cardapplydetail set g_state = '07'
where g_idcard = '440401430103082'

update A set g_state = '07'
from g_cardapply A inner join g_cardapplydetail B on A.g_applyno = B.g_applyno 
where B.g_idcard = '440401430103082'

4、 删除g_cardapplydetail表中所有姓李的记录
delete from g_cardapplydetail 
where g_name like '李%'

3、 将身份证号码为440401430103082的记录在两个表中的申请状态均改为07
update g_cardapplydetail set g_state = '07'
where g_idcard = '440401430103082'

update A set g_state = '07'
from g_cardapply A inner join g_cardapplydetail B on A.g_applyno = B.g_applyno
where B.g_idcard = '440401430103082'