7.2.2非关系型数据库

非关系型数据库定义：非关系型数据库是一个新的理念，以key-value形式呈现

非关系型数据库有哪些？

1、hbase(列模型)   2、Redis数据库  3、MongoDB数据库

★非关系型数据库类型

1、hbase（列模型）     2、redis（键值对模型）

3、mongodb（文档类模型）

not only sql 翻译：不仅是sql

可以使用json的方式来进行数据存储，没有表结构

★非关系型数据库特点：

1、没有表结构

2、可通过阶层形式进行存储

3、一般还以对象形式的形式进行存储，比如键值对，根据这个键获取对应值。

7.2.3 MySQL数据库原理：

用户平台输入注册信息，然后进行提交，注册成功提交后，系统会通过sql插入语句往服务器去插入这些信息，京东服务器就会生成这些表，下次进行登录的时候就会读取这些表，如果表中有你的注册信息，就能输入信息直接登录。

约束定义：约束用于对表中字段进行限制，保证表中数据的正确性和唯一性

★常用的sql语句

1、查看当前服务状态：service mysqld status

2、开启服务：service mysqld start  停止服务：net stop mysql

3、修改MySQL数据库密码命令：

mysqladmin -uroot password“123456”

4、创建一个数据库  create database +库名

5、显示有哪些数据库   show databases

6、 进入某个数据库    use +库名

7、删除某个数据库    drop database +库名

8、创建一个表    create table +表名(字段1名称，数据类型,约束,字段2名称,数据类型,约束,字段3名称,数据类型，约束,）

9、退出数据库操作：exit

7.2.4 mysql常用的数据类型

1、int    存储整数     ==》超过2147483647用bigint

2、float    默认保存6位精度（包括小数位和整数位）Float(20,2)指小数点后面2位

3、char      存储字符        char(10)可能占用了10个字节

4、varchar(10)   存储字符    varchar(10)只占了6个字节，把其余的4个字节留给了下一个字符

5、date   建表的时候在日期的后面接 date，并且加引号

注意：  int(3) int(M) M指最大显示宽度

        char(20) char(M) M指最大能存储20个字符

7.3  约束

7.3.1★约束定义

约束定义：用于对表中字段进行限制，保证表中数据的正确性和唯一性。

7.3.2★约束类型

primary key        主键约束

说明：非空，唯一，用于唯一标识对应的记录。类似身份证。

foreign key        外键约束

说明：用于表与表建立关系模型，使表与表紧密的结合起来。

Default            默认值约束   

说明：默认给字段指定默认值

auto_increment     自增长约束 

说明：作用在整数类型，字段默认从1开始自增

7.4.修改表结构

1.查看表结构            desc +表名

显示字段名称，数据类型，约束，备注

field---字段名称     extra----额外的备注

type ----数据类型    date格式：“2022-07-10"

null---是否可以为空

2、修改表名    alter table +表名 rename + 新表名

3.修改表字段

alter table +表名 change +原字段名 +新字段名 数据类型，约束

4.添加表字段，并放到第一个字段前

alter table +表名 add +字段名 数据类型 约束 first

5.添加表字段，并放到某个字段后

alter table +表名 add +字段名 数据类型 约束 after +字段名

 

6、同时添加两个字段，默认添加到字段最后

alter table +表名 add(字段1 数据类型,字段2 数据类型)

7. 删除表字段          alter table +表名 drop + 字段 

8.删除表两个字段    alter table +表名 drop 字段1,drop 字段2

9.修改主键id为自增长

alter table +表名 change +字段名 数据类型 auto_increment

10.删除表                 drop table +表名

Mysql增删改语句

1.表中插入数据

insert into +表名 values(字段1value,字段2value,字段3value...)

查询表中插入数据：select * from+表名

2、一次性表中插入多条数据:

 insert into +表名 values(字段1value,字段2value,字段 3value...),(字段1value,字段2value,字段3value...)

3.对表中指定字段插入数据

insert into +表名(字段1，字段2) values(字段1值，字段2值)

4. 删除表中指定数据     delete from +表名 where 条件

5. 删除表（清除表中数据，表结构在）  truncate +表名

6. 删除表（清除数据表结构不在）     drop table +表名

注：TRUNCATE,DELETE,DROP放在一起比较：

TRUNCATE TABLE：快速删除表数据，不删除表结构。

DELETE TABLE: 按条件删除表数据

DROP TABLE：删除表结构和表数据

7.更新表中指定字段数据

update +表名 set 字段名=值 where 条件

★测试字段需要测试哪些内容(数据库中给你一个字段怎么测试)？

数据类型，约束类型、存储的值取边界值

★备份表，备份数据，备份数据库,还原数据库

1.备份表(备份表的结构)，创建一个表与某个表结构相同，但数据没有备份                create table +表1 like +表2

 

2.备份数据，把一个表的数据插入到另一个表

insert into +表名 select * from +表名

 

注意①插入的表必须要存在②插入的表是新表,没有数据。

3、把一个表的某些字段插入到一个新表中

insert into +表1(字段1，字段2) select 字段1，字段2 from 表2

 

注意1. 插入的表必须存在 2. 插入的表是新表,没有数据。

4、备份数据库

mysqldump -uroot -p 新数据库名>原脚本名

5、还原数据库

mysql -uroot -p +新数据库名<(已备份)脚本名

7.5、单表查询

1.查询表中所有数据：     select * from +表名

“*”代表所有

2. 查询某个字段的数据    select 字段 from +表名

3. 查询多个字段的数据

select 字段1，字段2 from +表名

4.查询满足某个条件的所有数据

select * from +表名 where 字段=值

注：where 后面接满足的条件

5.查询不满足某个条件的所有数据

select * from +表名 where 字段！=value

“！=” 代表不等于 ，也可以用符号 ”<>“代表不等于

6.查询同时满足多个条件数据

select * from +表名 where 条件1 and 条件2

and 关键字左右的两个条件必须同时满足

7.查询满足至少1个条件的数据

select * from +表名 where 条件1 or 条件2

or 关键字左右的两个条件至少满足1个，否则返回空

8.查询一个条件范围内的数据

select * from +表名 where 字段 between m and n

between...and ... 指定一个范围

9.查询字段满足在指定的集合中的数据

select * from +表名 where 字段 in(值1，值2，值3)

10.查询字段不满足在指定集合中的数据

select * from +表名 where 字段 not in (值1,值2,值3)

11.查询字段值为空的数据

select * from +表名 where 字段 is null

注意：字段是空不能写成 字段=null

12.查询字段不为空的数据

select * from +表名 where 字段 is not null

13.查询某个字段模糊匹配成功的数据

select * from +表名 where 字段 like “%值%”

%用于匹配字段开头和结尾

14.查询限定的数量的数据

select * from +表名 where 字段 limit m,n

m 指下标，n指限定的数量，从下标为m下一个的开始的n条数据

15.查询的数据根据某个字段从小到大排序

select * from +表名 order by 字段 asc

order by ...asc 从小到大排序

16.查询的数据根据某个字段从大到小排序

select * from +表名 order by 字段 desc

order by ... desc 从大到小排序

1. 查询的数据根据某个字段进行分组

select * from +表名 group by 字段

group by ... 根据条件分组

18.查询的数据根据某个字段进行分组再条件过滤

select * from +表名 group by 字段 having 条件

having跟在group by 后面，作用相当于where

★mysql聚合函数

19. 统计查询数据的数量   select count(*) from +表名

20. 查询某个字段求和     select sum(字段) from +表名

21. 查询某个字段进行平均值

select avg(字段) from +表名

22. 查询某个字段最大值   select max(字段) from +表名

23. 查询某个字段最小值   select min(字段) from +表名

24 对某个字段进行 (distinct)去重

select distinct(字段) from +表名

25、&&   和，且

★单表查询总结

1、where 不能放在GROUP BY 后面

where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数比如SUM(),AVG()等，使用where条件显示特定的行。

2、having 是跟GROUP BY 连在一起用的，放在GROUP BY 后面，此时的作用相当于WHERE。

3、having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

例1：求出每个班级中数学成绩大于80分的人数？

 

2.求出每个班级中性别为1的数学总成绩

 

3.求出每个班级的数学总成绩大于200的班级和成绩信息 

★数据库用户权限操作

如何给普通的数据库用户赋予相应的权限（如增删改查）：

1.进入mysql数据库：use mysql;

2.查询mysql数据库服务器已经创建了哪些用户：

select host,user from user;

3.创建用户但是未授权（方法一）

Insert into user(host,user,password)values('localhost','wang',password('123456'));

创建用户后需要刷新下:flush privileges;

4、创建用户后进行授权（方法二） 

%  代表远程权限     local代表本地权限

grant select,update,delete,drop on *.* to 'wang'@'localhost' identified by '123456'

5、创建用户同时授权

grant all privileges on *.* to 'dcs'@'%' identified by '123456';

授予一个普通用户dcs及密码为123456，允许其可以通过所有客户机访问本数据库下所有的库及所有的表,假如为localhost只能在本地进行访问其下面所有的表，权限为所有权限。

6、flush privileges ---刷新

show grants for 'wang'@'%'; //查看数据库的指定授权用户的权限

revoke all on *.* from 'dcs'@'%' ;//取消所有权限

删除用户:delete from user where user='zhongguo'and host='localhost';

修改root用户的密码：update user set password=password('123456') where user='root';

◆安装报错注意：

 

1、输入service 关闭防护墙  service iptables stop

2、grant all privileges on *.* to 'root'@'%' identified by '123456';

◆取别名

 

 

或

 

 

或

 

7.6 多表关联查询

7.6.1. 内连接查询

(查询两个表都符合条件的数据) 两个表中有字段相同的值进行拼接                  关键字    inner join

select 字段列表 from 表1 inner join 表2 on 表1.字段=表2.字段

 

 

7.6.2. 左连接查询

左连接：(左边表中数据显示全部) 以左表为主显示全部，右边表符合条件的显示，右表在左表没有的数据自动以null填充。                      关键字:left join

 

 

基本格式：select 字段列表 from 表1 left join 表2 on 表1.字段=表2.字段

7.6.3. 右连接查询

右连接查询：以右表为主显示全部，左表显示符合条件的数据，左表在右表没有的数据以null填充，关键字:right join

基本格式:select 字段列表 from 表1 right join 表2 on 表1.字段=表2.字段

 

 

7.6.4. 基本连接

select * from aa,cc where aa.id=cc.s_id; //基本连接查询

7.6.5. 硬连接

select * from aa union select * from cc; //union连接（前提条件，多个关系表的表字段数目必须相同） 

7.6.6. 交叉连接

7.7、索引

7.7.1.索引定义

一个索引是存储在表中的数据结构，索引在表的列名上创建。索引中包含了一个列的值，这些值保存在一个数据结构中。数据库使用索引的方式与使用书的目录很相似：通过搜索索引找到特定的值，然后随指针到达包含该值的行。

重点：索引是一种数据结构

7.7.2.索引作用

可以利用索引快速访问数据库中的特定信息. 

7.7.3.索引分类

1、普通索引2、唯一索引3、主键索引

7.7.4.索引语句

◆查索引:show * from dcs  

◆创建索引：单个字段创建索引：create index tt on dcs(sid);    给sid创建索引

多个字段创建索引：create unique(唯一）index tt on dcs(sid，name,id);

◆删除索引：

alter table table_name drop index index_name ;

◆普通索引：是最基本的索引，它没有任何限制

◆唯一索引：它与前面的普通索引类似，不同的就是：索引列的值必须唯一,但允许有空值

◆主键索引：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引

注意：主键索引和主键约束其实是绑在一起的，如果有了主键索引，那么必会产生主键约束，或者说主键约束是主键索引的一个特征。添加一个主键就是添加了主键索引，添加主键索引就添加了主键。

7.8、数据库视图

7.8.1视图定义

视图是一个虚拟的表，它不在数据库中以存储数据的

形式保存，是在使用视图的时候动态生成。 

7.8.2视图特点

1、视图是由基本表产生的虚表

2、视图的更新和删除会影响基础表

3、基础表的更新和删除也会影响到视图

7.8.3.视图的作用

对视图的操作与对基本表的操作都是一样的（包括alter,create,insert into ,update ,delete ,select）,且二者任意一方的字段值被修改，都会实时影响到对方（如修改view的字段值，会同步修改table相应的字段值）；但是视图的创建是基于基本表的，它的作用可以对基本表的敏感信息进行保护；在实际工作中，出于安全考虑，将用户常用的信息创建成视图给用户调用，避免了直接操作基本表！

7.8.4.视图命令 

1、显示目前有哪些视图:show tables;

创建的视图都在表的最下面。数据库视图 VIEW

2、将查询结果集创建为视图yy

create view yy as (select id,name from dcs)

3、查看视图show CREATE VIEW yy（视图名）

查看视图：Show tables      select * from 视图名

删除视图：drop view 视图名

7.8.5数据库中的DDL和DML

◆DDL //数据库定义语言 （对表结构和表字段进行操作）     

如：alter、create、table、drop

◆DML //数据库操作语言 （对表数据进行操作）      

如：insert into、delete、select、update，truncate

7.8.6数据的约束类型

◆对表结构进行约束：主键约束：primary key    非空约束：not null     默认值约束：default自增长约束：auto_increment    

◆对表与表之间进行约束：  外键约束：foreign key

 

7.8.7外键约束

◆外键用来干什么

为了一张表记录的数据不要太过冗余。这和软件project的模块化思想类似，仅仅只是在数据库中是对表关系进行解耦，尽量让表记录的数据单一化。

★外键的主要作用

保持数据的一致性、完整性

注意：

1、主表一般存放的是基础数据，如果主表不存在的数据，在任何一张子表是无法插入跟该数据相关的任何数据；

2、如果要删除主表的数据，需要删除与主表相关的任何子表数据

实例讲解： 

1、现在我们创建两个表dcs1、dcs2

2、所以在设计的时候。就给表1加入一个外键，这个外键就是表2中的sid字段。那

么这样表1就是主表，表2就是子表

3、创建dcs1表

create table dcs1(id int(4) PRIMARY key,name varchar(10))

ENGINE=INNODB ;

4、创建dcs2表（创表时创建外键）

dcs2表，里面包含sid，name，然后建立外键

create table dcs2(sid int(4)primary key,sname varchar(10),CONSTRAINT fk_sid

FOREIGN key(sid)REFERENCES dcs1(id))ENGINE=INNODB;

给子表创建一个外键，外键名字叫xx 并且子表的外键字段为sid，对应主表的主键名字为id

FK_***：是外键名 --对应dcs2表中的：fk_sid

foreign key(子表的外键字段) --对应dcs2表中的：sid

references 父表的表名（父表的主键的字段名） ---父表名对应dcs1，父表的主键的字段名对应dcs1中的id字段

外键之增加数据：

如果表1和表2之间有外键约束 且表1是主表 表2为子表 如果主表不存在的数据 在任何一张子表是无法插入跟该数据相关的任何数据。

5、数据库有的版本有时默认存储引擎为MyISAM，这时我们外键就会不起作用，所有我们在建表时要将存储引擎改为Innodb;具体操作见上创表的sql语句中有体现

删除外键: alter table 子表名drop foreign key 外键名

删除主键：删除之前先删除自增长 alter table dcs1 change id id int(10);

alter table 表名 drop primary key;

注意:只有在定义外键时,用constraint 外键名 foreign key .... 方便进行外键的删除

6、为什么说外键能保持数据的一致性、完整性

外键之数据删除场景：

7.9、存储过程

7.9.1.存储过程定义 

存储过程是完成特定功能的sql语句集合。通过编译后存储在数据库中，通过指定的存储过程名称调用执行它。

存储过程=sql语句集合+控制语句

 Mysql5.0版本后支持存储过程

7.9.2使用存储过程的优点

◆存储过程创建可以多次调用，不需要重新编写存储过程语句。

◆假如要往表中插入大量的测试数据，用insert into语法执行速度太慢，效率较低，那么这个时候可以通过编写存储过程，把需要插入的数据sql集合放入存储过程的函数体当中，创建存储过程，下次即可调用。

◆存储过程加快程序的运行速度

◆存储过程增加sql语句的功能和灵活性、创建的存储过程可以重复使用。

7.9.3存储相关命令

1>◆创建一个存储过程：

create procedure 存储过程名称()

    create procedure 存储过程名称(参数名，数据类型)

◆删除一个存储过程：drop procedure 存储过程名称

◆加强代码的健壮性：

drop procedure if exists 存储过程名称

◆增强存储的健壮性:

drop TABLE if EXISTS+存储过程名称

◆自动检测统计数据行数:DECLARE i int DEFAULT        

2>调用存储过程：call 存储过程名称()

注意:在定义存储的时候带参数，必须用参数查询。如果定义了形式参数、不传入实际参数会报错

3>begin...end 代表存储过程体的开始和结束

4>if语句的格式：  

◆单分支

if 条件 THEN

执行sql

else

执行sql

end if

◆多分支 （有多少的if 就需要写多少个end if）

if 条件 THEN

执行sql

else if 条件 THEN

执行sql

else if 条件 THEN

执行sql

.............

else

执行sql

end if

end if

end if

5>while语句（循环语句）

while 语句的格式：

while 条件 do

执行循环体（

sql）

end while；

★注意:什么时候进入(退出)循环：当条件成立时，进入循环；当条件不成立时，退出循环。

7.9.4创建存储过程 

create procedure 存储过程名称(参数名，数据类型)

begin

存储过程体

end

 例：

如果传入得参数大于表中实际总记录的数量，则往表中补齐