一. 数据库基础
存储数据用文件就可以了,为什么还要弄个数据库?
文件存储数据有下面几个缺点:
- 文件的安全性问题
- 文件不利于数据查询和管理
- 文件不利于存储海量数据
- 文件在程序中控制不方便
存储引擎:
存储引擎是:数据库管理系统如何存储数据,如何为存储的数据建立索引和如何更新,查询数据等技术的实现方法.
mysql的核心就是插件式存储引擎,支持多种存储引擎.
(一).有关mariadb
- 切换到root用户 su -root
- 查看本地是否安装了mariadb的状态 rpm -qa | grep mariadb
- 查看mariadb的状态 service mariadb status
- 启动mariadb service mariadb start
- 关闭mariadb service mariadb stop
(二).MySQL启动时
MySQL数据库启动时,会先启动一个守护进程.守护进程在判断MySQL服务器是否运行正常,如果MySQL使用过程当中服务端挂掉了,守护进程就会重新拉起MySQL服务端.
(三).MySQL服务端
(1).连接层
- 用户验证
- 选择连接协议(TCP,本地域套接字)
- 创建线程对客户端进行服务
(2).server层
- 检查SQL语句的语法-SQL语句的语法是否规范
- 语义分析
- 权限检查-查看当前用户是否有操作该表的权限
- 分析SQL语句的查找方案,选出一个执行效率最高的方案
- 执行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.关于日期的函数
- a.获取当前的日期 current_date();
- b.获取当前时间 current_time();
- c.获取当前时间戳 current_timestamp();
- d.date(datetime):返回datetime当中的日期部分
- e.date_sub(date,interval [数值] [数值的单位]):对日期进行减操作,数值单位可以为year,minute,day
- f.date_add(date,interval [数值] [数值的单位]):对日期进行加操作,数值单位可以为year,minute,day
- g.datediff(date1,date2):计算两个时间的差值,单位是天 计算方式是:date1-date2
- h. now();返回当前的日期时间
注意:记录一条数据的插入时间,我们尽量去使用datestamp这样的类型.
2.字符串函数:
- charset(str):获取str的字符集
- concat(str1,str2,str3,...):将字符串连接起来
- instr(string,substring):返回substring在string当中的位置,没有找到则返回0
- ucase(str):将str转换成大写
- lcase(str):将str转换成小写
- left(str,len):从str当中的左边开始取len个字符
- length(str):计算str的长度,并非字节数量
- replace(源字符串,待替换的字符串,替换的字符串):字符串替换
- strcmp(str1,str2):比较字符串 注意:根据校对规则的不同,大小写敏感度的不同,大小写的字符串比较结果会有不同.
- substring(str.pos,len):从str的pos位置开始取len个字符
3.数学函数:
- abs(num):取绝对值
- ceiling:向上取整
- floor:向下取整
- rand:获取随机数
- format(num,要保存的小数位数) 注意:超过的部分要四舍五入
4.其他函数:
- user();获取当前用户
- database():获取当前正在使用的数据库
- md5(str):计算MD5
- password(用户名称):查询用户密码,只不过密码已经被加密过了,无法正常查看
八.索引特性:
索引:提高数据库的性能,查询速度的提高是以插入,更新,删除的速度为代价的
常见索引分类:
主键索引(primary key):在一个表当中设置了主键,则就会创建一个主键索引
唯一索引(unique):在一个表中设置了唯一键,则就会创建一个唯一索引
普通索引(index):
全文索引(fulltext):
2.建立了一二叉树的结构,将设置成为索引的字段的内容,通过二叉树维护起来;
主键索引:
主键的特性:不能重复,不能为NULL
按照主键的值,构建一棵二叉树,准确来讲是b+树 (如果一张表当中创建了索引,就会创建二叉树结构);
创建索引对增删查改效率的影响:
- 增:有影响,因为需要更新二叉树
- 删:有影响,因为需要更新二叉树
- 改:有影响,因为需要更新二叉树
- 查:查询的时候如果用到索引字段的值作为查询条件,效率会大大的增加
九.事务管理:
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 '用户名'@'登录位置';