数据库学习过程

一. 数据库基础

存储数据用文件就可以了,为什么还要弄个数据库?

文件存储数据有下面几个缺点:

• 文件的安全性问题
• 文件不利于数据查询和管理
• 文件不利于存储海量数据
• 文件在程序中控制不方便

存储引擎:

存储引擎是:数据库管理系统如何存储数据,如何为存储的数据建立索引和如何更新,查询数据等技术的实现方法.

mysql的核心就是插件式存储引擎,支持多种存储引擎.

(一).有关mariadb

1. 切换到root用户      su -root
2. 查看本地是否安装了mariadb的状态     rpm -qa | grep mariadb
3. 查看mariadb的状态     service mariadb status
4. 启动mariadb       service mariadb start
5. 关闭mariadb        service mariadb stop

(二).MySQL启动时

MySQL数据库启动时,会先启动一个守护进程.守护进程在判断MySQL服务器是否运行正常,如果MySQL使用过程当中服务端挂掉了,守护进程就会重新拉起MySQL服务端.

(三).MySQL服务端

(1).连接层

1. 用户验证
2. 选择连接协议(TCP,本地域套接字)
3. 创建线程对客户端进行服务

(2).server层

1. 检查SQL语句的语法-SQL语句的语法是否规范
2. 语义分析
3. 权限检查-查看当前用户是否有操作该表的权限
4. 分析SQL语句的查找方案,选出一个执行效率最高的方案
5. 执行SQL

 

二. 库的操作

注意:SQL语句都是以;结尾的,SQL后面结束的时候必须加上;

(1).如何创建一个数据库     

• create database [数据库的名称];

(2).查看已经创建好的数据库的语句     

• show create database [数据库的名称];

字符集和校验规则:

查看数据库支持的字符集:

• show charset;

查看数据库支持的校验规则:

• show collation;

• ASCII字符集(1字节):采用的是1个字节的低7位表示字符,高位始终是0
• LATIN1字符集(1字节):相对于ASCII字符集做了一个扩展,启用了最高位
• GBK字符集:支持中文,字符可以使用1个字节,也可以使用2个字节
• UTF8:Unicode字符集(可以表示很多语言),支持了所有国家的文字字符,采用1-4字节

(3).创建数据库的时候,就需要指定字符集     

• create database [数据库名称] charset=[字符集];

注意:创建数据库的时候,数据的字符集尽量设置成UTF8

(4).删除数据库     

• drop database [数据库名称];

注意:删除数据库也会将数据库当中的表删掉,删除数据库这的操作一定要谨慎!!!

校验规则:

1.影响了用户对数据查询的排序;

2.后缀为_cs:大小写敏感的校对规则;后缀为_ci:大小写不敏感的校对规则;后缀为_bin:二进制校对规则,大小写敏感.

3.校对规则的特征

• 不同的字符集有不同的校对规则
• 每一个字符集都有一个默认的校对规则 (eg:utf8 --> utf8_general_ci(大小写不敏感) )

数据库备份:

•  mysqldump -P[端口] -u[用户] -p[密码] -B[数据库名称] > [文件]
• mysqldump -u[用户] -p[密码]  [数据库名称] 数据表1 数据表2... > [文件]

查看连接情况:

show processlist;

 

 

三.表的操作:

创建表的时候,设置列的名称以及列的属性 

插入数据的时候,插入一行数据,一行的数据包含多个列的属性

1.创建表

• create table [表名称] (字段的名称 字段的类型,...)character set 字符集 collation 校验规则 engine 存储引擎;

2.查看表字段

• desc [表名称];
• show create table [表名称];

| Field    :字段名称

| Type    :字段类型

| Null      :是否可以为NULL

| Key      :是否是主键

| Default : 默认值

3.修改表字段

3.1新增字段

•  alter table [表名称] add [字段的名称] [字段的类型] after [字段的名称];

3.2删除字段

• alter table [表名称] drop [字段的名称];

注意:对于删除而言,如果删除了某一个列,则该列的数据也就随之删除了.

3.3修改字段(类型)

• alter table [表名称] modify [字段的名称][字段的类型];

3.4表的重命名

• alter table [原始的表名称] rename (to) [重命名后的表名称];

3.5列名称的重命名

• alter table [表名称] change [字段名] [修改后的名称][字段的类型];

3.5表的删除

• drop table [表的名称];

 

四.数据类型:

数据类型分类:

 1.thinyint类型(占用了1个字节,表示范围(-128~127)):

1.1 在my.cnf当中[mysql]标签下增加"sql_mode=STRICT_ALL_TABLES",增强数据库对插入数据的类型范围的校验

1.2 如果在设计数据库表字段的时候,涉及到整数数据,尽量不要用thinyint(根据实际情况),而是用表示范围大的整数类型

1.3 对于整形数据,在设计的时候,尽量少用unsigned,因为一个小的数字,减去一个大的数字,有可能会导致查询结果出错,因为无符号表示不了负数

2.bit类型

2.1 如果设置BIT类型,BIT类型的值是按照ASCII码来表示的

2.2 如果只想存储0或者1,则BIT类型的比特位数设置为1

3.小数类型

float: 精确到小数点后6位

double,decimal:精确到小数点后16位(精度更高)

4.字符类型

4.1char(L):固定长度的字符串,L不是占用字节的大小,而是字符串的长度;字符占用字节的数量和数据库的编码格式息息相关;LATIN1:1个字符占用1个字节 UTF8:一个字符占用3个字节

4.2 varchar(L):可变长度的字符串,L表示字符长度;到底可以存储多少个字符,和数据库的编码格式息息相关;

     理论上可以存储65535个字符(一个字符占用一个字节),但是如果编码格式表示字符并不是用1个字节来表示,而是用n个字符来表示,则字符串的长度为(65535-(1~3)/n);

4.3 两者的区别:

• 对于char而言,定义了之后就开辟好了空间,对于varchar而言,在定义的范围内,存储多少个字符就开辟多大的空间.(固定长度与可变长度的体现);
• 但是对于varchar类型,并不是可以超过固定的字符串长度;
• 对于varchar类型需要用1~3个字节来保存字符串长度;

5.日期和时间类型

5.1 datetime:时间日期类型  表示的格式为:YYYY--MM--DD HH::mm::SS

该类型需要程序员自己手动插入时间

5.2 date类型:志表示年月日 占用三个字节

5.3 timestamp:时间戳类型 从1970年开始 表示的格式:YYYY--MM--DD HH::mm::SS

该类型可以自动计算出插入的时间,不用程序员手动计算

6.enum和set集合类型:

enum:枚举出了很多值,但是只能在其中选择一个,如果插入不是枚举出来的值,则会报错.

set:列举出来很多值,可以选择其中的任意多个值进行组合保存,但是如果不在列举出来的值,则会报错.

集合查询函数:

find_in_set(sub,str_list) :如果sub在str_list中,则返回下标,否则返回0;

 

 

五.表的约束

1.空属性:

NULL:表示当前字段可以为NULL

NOT NULL:表示当前字段不可以为NULL,列的字段指定该属性之后,在插入数据的时候,如果不插入该列的数据,则会报错.

2.默认值(缺省值)

关键字:default

可以在创建表字段的时候,指定一个默认值,当我们插入数据的时候,没有插入该字段的时候,就采用默认属性;

如果插入数据的时候指定值,则采用插入值,而并非是默认值.

3.列描述

关键字:comment

相当于列的备注,告诉查看该表的用户,该字段是什么含义,对于存储数据而言,并没有什么实质的约束;只是供人们来查看该列的含义.(相当于c++代码的日志)

查看表信息: show create table [表名称]\G;

4.zerofill:

约束了数字的宽度,如果宽度不够则用0填充

如果指定了zerofill,则默认会加上unsigned(无符号属性);在数据库当中还是存储的是原本数据的值;只不过在查询的时候,会按照宽度输出,宽度不够用0填充,相当于格式化输出;

5.主键

• 5.1 对于主键而言,并不是重新开辟了一个新的列,而是表当中的一列被设置的属性,这种属性被称之为主键;
• 5.2 主键的属性保证了该列数据不能为空,不能重复;
• 5.3 一个表当中只能有一个主键,不能有多个主键;
• 5.4 通常情况下,主键在设置的时候,都是整形类型,主键的这一列数据可以唯一标识一行数据;
• 5.5 设置主键:

a.在创建表的时候,直接在字段的后面加上关键字 "primary key" ,就可以设置该字段为主键

• create table t6(id int primary key,name varchar(20));

b.在创建表的时候,将所有的字段全部指定完毕之后,在所有字段的后面加上设置主键的关键词,并且加上该字段的名称;

• create table t7(id int,name varchar(20),primary key(id));

c.在创建表的时候,可以不指定主键列,创建完成之后,使用alter关键字来增加主键;

• alter table t7 add primary key(id);

5.6删除主键:

使用alter关键字: alter table [表名称] drop primarykey;

主键列的值可以唯一标识该行数据,因为主键不能为空,不能重复.

主键索引:通过主键来构建一棵B+树,在用户进行检索数据的时候,如果约束条件(查找条件)为主键,则会大大的提高搜索效率,因为B+树的搜索效率高,可以找到主键对应的一行数据.

6.自增长

当我们设置一个字段为自增长的时候,如果插入数据的时候,不给该列数据,则系统会自动填写一个比上一行数值加1的数值填写进去;通常自增长搭配主键来使用,主键不可以重复,每次自增长都会给新的数据分配比上一行+1的数值.

条件:

首先字段必须是一个整数,字段类型需要是一个整数类型;

一整表当中最多有一个自增长的列

搭配主键来使用

关键字: auto_increment

获取上次自增id的值: select last_insert_id();

7.唯一约束键:

• 关键字:unique ,同样是给某一列设置一个属性,该属性约束的字段内容不能重复,但可以为空.
• 主键比唯一键多了一个约束字段不能为NULL,但是一张表当中可以有多个字段设置为unique.

8.外键:

用于定义主表和从表的一个约束关系;

主表:存放基础属性的表,当前表当中的数据用来描述基础属性

例如: 班级属性 (班级id  班级属性(网络,计算机) 班级名称)

                             33          网络                           网络181

                             34          计算机                        计算机181

从表:存放具体用户产生的信息

例如:学生属性 (学生id 学生名字 班级id)

                              1         张三       33

                               2        李四       34

1.定义主表当中可能成为其它表外键的字段,一定需要将该字段设置成为主键或者唯一键,因为主键不能重复,不能为空,可以唯一标识一行数据

2.定义从表的时候,设置外键其实就是设置了一个关系,当前从表的字段和主表当中字段的关系,在从表当中插入数据的时候,会对插入的数据进行检验,检验的标准插入的数据是否存在在主表列字段当中;

外键就是添加了表与表之间的约束关系.

 

六.表的增删改查

一:增加数据:

1.1单行数据+全列插入

• insert into [表名称] values (表字段对应的值,...);

1.2单行数据+指定列插入

• insert into [表名称] (表中要增加的列的名称) values (指定表字段对应的值);

1.3多行数据+全列插入

• insert into [表名称] values (表字段对应的值,...),(表字段对应的值,...),...;

1.4多行数据+指定列插入

• insert into [表名称] (表中要增加的列的名称) values (指定表字段对应的值),(指定表字段对应的值)...;

1..5替换

• replace into [表名称] [字段名称] values ([字段对应的值],[字段对应的值],...);

注意: 如果要替换的数据不存在主键或者唯一键冲突,表的数据没有冲突,则插入一行数据

          如果表当中有冲突的数据,则删除之后再插入

二.简单查询

2.1全列查询

• select * from [表名称];    "*":表示表的所有列

注意:对于字段较少的表并且插入的数据比较少的表可以使用全列查询,查询的效率不会受到太大的影响;

但是,如果一个表中字段比较多或者数据量大,则全列查询会导致查询的效率降低;

2.2指定列查询

• select [列名称1],[列名称2],...from [表名称];

2.3表达式当中不包含列字段

• select [列名称1],[列名称2],[表达式]...from [表名称];

2.4表达式包含多个字段

• select [列名称1]+[列名称2]+[列名称3]...from [表名称];

2.5为查询结果定义别名

• select [列名称1]+[列名称2]+[列名称3] [别名] from [表名称];

2.5去重查询

关键字:distinct

select distinct [字段名称] from [表的名称];

以上SQL语句的特点:查询当前表当中所有行的数据.

在全列和指定列的基础上,查询数据时,可以指定只查询某个具体行的数据,或者具体满足某个条件的数据查询的时候,可以加上约束条件来约束查询结果,满足结果的数据才会输出;

2.6 where条件语句:对查询结果进行约束

where字句: where [字段名称] [运算符] [比较的值]

eg:where 英语<60;

比较运算符:

①.NULL查询:

IS NOT:不是....

= 和 <=>:都是表示等于的运算符

区别:=在数据库查询的时候并不是安全的,空和空使用"="进行比较的时候,结果还是为NULL

<=>在数据库查询的时候是安全的,空和控使用"<=>"进行比较的时候,接货还是为1

②.对查询结果进行排序:

关键字:order by [字段名称]

规则:按照order by后面的字段的内容进行排序,将结果输出

默认的排序规则是升序(ASC),如果想用降序规则进行排序,则需使用(DESC)

注意:返回排序的顺序是未定义的,所以在实际应用当中,不要依赖其结果

③.筛选分页结果:

方式一:limit n:从0开始,筛选n条结果

方式二:limit s,n:从s开始,筛选n条结果

方式三:limit n offset s;从s开始筛选n条结果

④.update:更新表数据

• update [表名称] set [字段名称]=[更改后的值] where 约束条件;

注意:更改一行数据的时候,一定要加上约束条件,否则就全表更新.

⑤.删除表数据

• delete from [表名称] where 约束条件

如果不加where字句,表示删除整张表当中的数据,但是不会对表结构造成影响,如果有自增长的属性,不会进行清空.

⑥.截断表:

注意:不能针对某一行的数据进行操作,而是针对整个表的数据进行操作的;

重置auto_increment 项

语法:truncate table [表的名称];

⑦.聚合函数:

count函数:查询表当中数据的个数(记录);

sum函数:查询数据的总和;

avg函数:计算数据的平均值

max函数:计算最大值

min函数:计算最小值

⑧.分组查询: group by 字句

group by 配套having 使用,进行过滤使用

 

七 .mysql的内置函数:

1.关于日期的函数

1. a.获取当前的日期 current_date();
2. b.获取当前时间 current_time();
3. c.获取当前时间戳 current_timestamp();
4. d.date(datetime):返回datetime当中的日期部分
5. e.date_sub(date,interval [数值] [数值的单位]):对日期进行减操作,数值单位可以为year,minute,day
6. f.date_add(date,interval [数值] [数值的单位]):对日期进行加操作,数值单位可以为year,minute,day
7. g.datediff(date1,date2):计算两个时间的差值,单位是天   计算方式是:date1-date2
8. h. now();返回当前的日期时间

注意:记录一条数据的插入时间,我们尽量去使用datestamp这样的类型.

2.字符串函数:

1. charset(str):获取str的字符集
2. concat(str1,str2,str3,...):将字符串连接起来
3. instr(string,substring):返回substring在string当中的位置,没有找到则返回0
4. ucase(str):将str转换成大写
5. lcase(str):将str转换成小写
6. left(str,len):从str当中的左边开始取len个字符
7. length(str):计算str的长度,并非字节数量
8. replace(源字符串,待替换的字符串,替换的字符串):字符串替换
9. strcmp(str1,str2):比较字符串    注意:根据校对规则的不同,大小写敏感度的不同,大小写的字符串比较结果会有不同.
10. substring(str.pos,len):从str的pos位置开始取len个字符

3.数学函数:

1. abs(num):取绝对值
2. ceiling:向上取整
3. floor:向下取整
4. rand:获取随机数
5. format(num,要保存的小数位数)  注意:超过的部分要四舍五入

4.其他函数:

1. user();获取当前用户
2. database():获取当前正在使用的数据库
3. md5(str):计算MD5
4. password(用户名称):查询用户密码,只不过密码已经被加密过了,无法正常查看

八.索引特性:

索引:提高数据库的性能,查询速度的提高是以插入,更新,删除的速度为代价的

常见索引分类:

主键索引(primary key):在一个表当中设置了主键,则就会创建一个主键索引

唯一索引(unique):在一个表中设置了唯一键,则就会创建一个唯一索引

普通索引(index):

全文索引(fulltext):

2.建立了一二叉树的结构,将设置成为索引的字段的内容,通过二叉树维护起来;

主键索引:

主键的特性:不能重复,不能为NULL

按照主键的值,构建一棵二叉树,准确来讲是b+树 (如果一张表当中创建了索引,就会创建二叉树结构);

创建索引对增删查改效率的影响:

1. 增:有影响,因为需要更新二叉树
2. 删:有影响,因为需要更新二叉树
3. 改:有影响,因为需要更新二叉树
4. 查:查询的时候如果用到索引字段的值作为查询条件,效率会大大的增加

九.事务管理:

1.相当于多线程当中的线程互斥.

2.保证多个客户端访问数据库的同一张表的时候,不会产生数据二义性的问题.

3.事务的基本操作:

• 开始事务   start transaction;
• 创建一个保存点   savepoint[保存节点名称];
• 回滚 rollback to [保存节点名称];
• 提交事务 commit;

注意:

1.当提交事务完成之后,就不能再回到之前保存的保存点,因为事务已提交.

2.InnoDB是支持事务,MyISAM不支持事务

事务的隔离级别:

当mysql表被多个线程或者客户端开启各自事务操作数据库中的数据时,mysql提供了一种机制,可以让不同的事务在操作数据时,具有隔离性,从而保证数据的一致性.

1.无隔离级性的问题:

脏读:读到数据并不是真实(有效)的数据

不可重复读: 前后两次读到的结果是不一致的

幻读:前后读到的数据条目数都是不一致的,在一个事务中"第一次读到了x条,第二次读到了x+n条"

事务的隔离级别:

 

设置事务的隔离级别: set session transaction islation level [事务的隔离级别];

查看当前的隔离级别: select @@tx_isolation;

事务的ACID特性:

• 原子性:事务是应用中最小的执行单位,就如原子是自然界的最小颗粒,具有不可再分的特征一样,事务是应用中不可再分的最小逻辑执行体.
• 一致性:事务执行的结果,必须使数据库从一个一致性状态,变到另一个一致性状态.当数据库只包含事务成功提交的结果时,数据库处于一致性状态.如果系统运行发生中断,某个事务尚未完成而被迫中断,而改未完成的事务对数据库所做的修改已被写入数据库,此时数据库就处于一种不正确(不一致)的状态,因此一致性是通过原子性来保证的.
• 隔离性:各个事务的执行互不干扰,任意一个事务的内部操作对其他并发事务都是隔离的.也就是说,并发执行的事务之间不能看到对方的中间状态,并发执行的事务之间不能互相影响.
• 持久性:持久性是指一个事务一旦被提交,它对数据库所做的改变都要记录到永久存储其中(如:磁盘)

 

十.视图:

1.视图就是一个虚拟的表,虚拟表当中的数据来源于真实存在的表.

2.视图可以当做是一个表来进行操作.

3.修改视图当中的内容也会同步更新真实表中的数据,同理修改真实表中的内容也会同步更新视图中的数据

基本使用:

①.创建视图:

• create view [视图的名称] as select语句;

②.删除视图:

• drop view [视图名称];

视图规则与限制:

• 与表一样,必须唯一命名(不能出现同名视图或表名)
• 创建视图数目无限制,但要考虑复杂查询创建为视图之后的性能影响
• 视图不能添加索引,也不能有关联的触发器或者默认值
• 视图可以提高安全性,必须具有足够的访问权限
• 视图和表可以一起使用

 

十一.用户管理:

mysql的用户管理相当于Linux操作系统的多用户管理

原因:不想给所有的数据库操作人员所有数据库的操作权限,所以分用户; 可以设置用户只能看到某一个具体的数据库

mysql当中的用户名称表现形式: root@localhost

• localhost:表示允许root用户从本地(数据库所在的机器)连接数据库
• 127.0.0.0:表示允许root用户从本地(数据库所在的机器)连接数据库
• % :表示可以从任意的IP地址连接到数据库
• ::1 : ipv6版本的IP地址

创建用户: create user '用户名'@'登录主机/ip' identified by '密码';

删除用户:drop user '用户名'@'主机名';

修改用户密码:

自己改自己的密码:

• set password=password('新的密码');

root用户修改指定用户的密码:

• set password for '用户名'@'主机名'=password('新密码');

给用户授权:

刚创建的用户没有任何权限,需要给用户授权.

• grant 权限列表 on 库.对象名 to '用户名'@'登录位置' [identified by '密码'];

回收权限:

• revoke 权限列表 on 库.对象名 from '用户名'@'登录位置';