1.MySQL数据库
1.1数据库相关概念
1.1.1 DB:数据库
database
保存一组有组织的数据的容器,数据仓库,软件,安装在操作系统上。用来存储数据,管理数据。
数据库分类:
-
关系型数据库:(SQL)
MySQL、Oracle、SqlServer、DB2、SQLite等,通过表和表之间,行和列之间的关系进行数据的存储
-
非关系型数据库:(No SQL)
Redis,MongDB,对象存储,通过对象的自身的属性来决定
所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。
1.1.2 DBMS:数据库管理系统
Database Management System
数据管理软件,用于管理DB中的数据
DBMS分为两类:
①基于共享文件系统的DBMS (Access);
②基于客户机—服务器(C/S架构)的DBMS(MySQL、Oracle、SqlServer)。
1.1.3SQL:结构化查询语言
Structured Query Language
用于和DBMS通信的语言
-
sql的优点不是某个特定数据库供应商专有的语言,几乎所有DBMS都支持
-
sql虽然简单易学,但实际上是一种强有力的语言,灵活使用可以进行复杂和高级的数据库操作
1.1.4关系型数据库的数据结构
-
关系:一个关系对应一张规范的二维表,每一个关系有一个名称即关系名
-
元组:表中的一行称为一个元组
-
属性/字段:表中的一列称为一个属性/一个字段
-
主键:唯一标识一行的字段或字段组,例如学生表中的学号可以唯一标识一个学生
-
外键:表中的某个字段,用来描述本关系中的元组与另一个关系中的元组之间的联系
-
域:字段的取值范围
-
分量:元组的一个属性值
-
关系模式:通过关系名和属性名列表对关系进行描述。
一般形式为:
关系名:(属性名1,属性名2,……,属性名n)
例如
student(学号,姓名,性别,出生日期,所学专业)
course(课程号,课程名称,学时,学分)
score(学号,课程号,学期,成绩)
说明:加粗为主键,斜体为外键,其中score关系学号+课程号+学期组成的字段组为主键
关系模型要求关系必须是规范化的,即要求关系必须满足一定的规范条件,这些规范条件中最基本的是
(1)关系中的每一个元组必须是可区分的
(2)关系中的每一个分量必须是不可分的,即不允许表中有表
1.2MySQL的字段类型
数值
-
tinyint 1个字节
-
smallint 2个字节
-
mediumint 3个字节
-
int 4个字节
-
bigint 8个字节
-
float 4个字节 浮点型
-
double 8个字节 双精度实型
-
decimal 16个字节 数字型 字符串形式的浮点数,不存在精度损失,常用于金融计算,银行账目计算
因此建表时价格字段就用decimal,float,double等非标准类型,在数据库中保存的是近似值,而DECIMAL是以字 符串的形式保存数值。打个比方,我存的是整数的时候,他就整数给我处理了,我存0.00给我实际存个0,我存 14.00实际给我存个14,是都可以存浮点数,但是涉及到钱,金额这方面还是给我精准着来。
字符串
- char 字符串固定大小的 0~255
- varchar 可变长字符串 0~65535 对应java里的String类型
- tinytext 微型文本 28-1
- text 文本串 216-1
时间日期
- date YYYY-MM-DD 日期格式
- time 时间格式
- HH:mm:ss(24小时制)
- hh:mm:ss(12小时制)
- datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp 时间戳 1970.01.01到现在的毫秒数
- year 年份表示
1.3数据库的字段属性
Unsigned:无符号整数。声明了该列不能声明为负数
Zerofill:0填充,即不足的位数。使用0来填充
比如长度为3的int类型,只写了一个5,会被填充为005
自增:自动在上一条记录的基础上+1(默认),通常用来设计唯一的主键。必须是整数类型。可以自定义主键的自增起始值和步长。
非空:
- 如果设置为了not null,不给它赋值就会报错
- NULL,如果不填写值,默认就是null
1.4关于MySQL数据库引擎
- INNODB:现在默认使用 安全性高,能够支持事务的处理,多表多用户操作
- MYISAM:早些年使用的 节约空间,速度较快
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为MYISAM的两倍 |
1.5安装sqlyog
链接: https://pan.baidu.com/s/1BkJ9OTTNMQ9YeVz2W5QLKg
提取码: tfxt
2.MySQL数据库操作
2.1.连接MySQL数据库
mysql -u root -p --登录数据库
flush privileges; --刷新权限
show databases; --查看所有的数据库
use 数据库名 --切换数据库
show tables;--查看所有的表
describe 表名;/desc 表名; --查看表结构
查看服务器的版本
方式一:登录到mysql服务端
select version();
方式二:没有登录到mysql服务端
mysql --version
或
mysql --V
单行注释:-- 注释文字
多行注释:/* 注释文字 */
2.2增删改查
SQL的语言分类
DQL(Data Query Language): 查询 select
DML(Data Manipulate Language): 操作 insert 、update、delete
DDL(Data Define Languge): 定义 create、drop、alter
TCL(Transaction Control Language): 事务控制 commit、rollback
2.2.1增
建表
CREATE TABLE IF NOT EXISTS `account_class` (
`fID` varchar(32) NOT NULL,
`fType` varchar(32) NOT NULL,
`fClass` varchar(32) NOT NULL,
PRIMARY KEY (`fID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `account_class` (`fID`, `fType`, `fClass`) VALUES
('C6CF7622B43000017EA41D601D507130', '支出', '餐费'),
('C6CF7626E24000014B31526A1E3B135F', '支出', '交通'),
('C6CF762966B000019EA81DC015809660', '收入', '工资'),
('C6CF763063B00001A345B2EFD6901C95', '收入', '奖金'),
('C6CFC399428000015E1A8F6017B01034', '支出', '购物');
插入多条数据
INSERT INTO table_name (field1, field2,...fieldN) VALUES (valueA1,valueA2,...valueAN),(valueB1,valueB2,...valueBN),(valueC1,valueC2,...valueCN)......;
2.2.2删
1.delete
delete from table_name;
属于DML语言,每次删除一行,都在事务日志中为所删除的每行记录一项。产生rollback,事务提交之后才生效;如果有相应的 trigger,执行的时候将被触发,如果删除大数据量的表速度会很慢。
删除表中数据而不删除表的结构(定义),同时也不释放空间。
1)、属于数据库DML操作语言,只删除数据不删除表的结构(定义),会走事务,执行时会触发trigger,如果删除的数据量大,表速度会很慢;DELETE执行时,会先将所删除数据缓存到rollback segement中,事务commit之后生效;
2)、在 InnoDB 中,DELETE其实并不会真的把数据删除,mysql 实际上只是给删除的数据打了个标记为已删除,因此 delete 删除表中的数据时,表文件在磁盘上所占空间不会变小,存储空间不会被释放,只是把删除的数据行设置为不可见。虽然未释放磁盘空间,但是下次插入数据的时候,仍然可以重用这部分空间(重用 → 覆盖)。
3)
-
delete from table_name删除表的全部数据,对于MyISAM 会立刻释放磁盘空间,InnoDB 不会释放磁盘空间;
-
对于delete from table_name where xxx 带条件的删除, 不管是InnoDB还是MyISAM都不会释放磁盘空间;
-
delete操作以后使用 optimize table table_name 会立刻释放磁盘空间。不管是InnoDB还是MyISAM 。所以要想达到释放磁盘空间的目的,delete以后执行optimize table 操作。
4)、delete 操作是一行一行执行删除的,并且同时将该行的的删除操作日志记录在redo和undo表空间中以便进行回滚(rollback)和重做操作,生成的大量日志也会占用磁盘空间。
5)、delete删除后auto_increment(表自增)不变,truncate删除后同时删除auto_increment
- innodb引擎下 delete 后 重启数据库,自增重新计数。(存在内存中,断电即失 || 8.0 版本的数据库测试没有重新计数。8.0 之前的数据库计数器失效)
- myisam引擎下继续从上个自增开始。(存在文件中,不会丢失)
2.truncate
truncate table table_name;
1、无法根据条件来删除,只能将表中数据全部删除,在功能上和不带where子句的delete语句相同:
2、说明
1)、默认情况下,truncate通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。所以使用的系统和事务日志资源少,可以使用reuse storage; truncate会将高水线复位(回到最开始).
2 )、 truncate是DDL语言,删除内容、释放空间但不删除表的结构(定义)。 操作立即生效,自动提交,无法找回,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.
3 )、truncate table table_name 立刻释放磁盘空间 ,不管是 InnoDB和MyISAM 。
truncate语句实际是删除原来的表然后重新建立一个新表;这个重新建表的过程做了优化,比如表结构文件之前已经有了等等。所以速度上应该是接近drop table的速度;
4)、truncate能够快速清空一个表。并且重置auto_increment的值。
3.drop
drop table table_name;
1)、drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index); 依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。
2)、drop也属于DDL语言, 操作立即执行生效,无法找回
3)、删除内容和表结构,释放空间。
区别
1、表和索引所占空间:
当表被TRUNCATE 后,这个表和索引所占用的空间会恢复到初始大小;
DELETE操作不会减少表或索引所占用的空间;
DROP语句将表所占用的空间全释放掉。
2、应用范围:
TRUNCATE 只能对table;
DELETE可以是table和view。
3、执行速度:
drop > truncate > delete
4、delete from删空表后,会保留一个空的页,truncate在表中不会留有任何页。
5、DELETE语句执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存,删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
6、当使用行锁执行 DELETE 语句时,将锁定表中各行以便删除。truncate始终锁定表和页,而不是锁定各行。
总结
- truncate不能加where条件只能全部删除,而delete可以加where条件;
- truncate删除不能回滚,delete删除可以回滚
- delete from后自增列仍然从上次的数开始增加,truncate后自增列从头开始。
- 执行速度:
drop > truncate > delete - 对于由 FOREIGN KEY 约束引用的表,不能使用 TRUNCATE TABLE,而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中,所以它不能激活触发器。
2.2.3改
修改属性值
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
当我们需要修改数据表名或者修改数据表字段时,就需要使用到MySQL ALTER命令。
ALTER TABLE 旧表名 RENAME AS 新表名; --修改表名
--FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句,所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。
ALTER TABLE 表名 ADD 字段名 字段属性; --增加表的字段,字段会自动添加到数据表字段的末尾
ALTER TABLE 表名 ADD 字段名 字段属性 FIRST;--增加表的字段,并位于表的第一列
ALTER TABLE 表名 ADD 字段名 字段属性 AFTER 另一字段名;--增加表的字段,设定位于某个字段之后
ALTER TABLE 表名 MODIGY 字段名 新字段属性; --修改字段属性
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新字段属性; --可以同时修改字段名和字段属性
ALTER TABLE 表名 DROP 字段名; --删除表的字段
2.2.4查
SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[left | right | inner join table_name] --多表连接查询
[WHERE…] --指定结果需满足的条件
[GROUP BY…] --指定结果按哪几个字段分组
[HAVING…] --对GROUP BY分组的的结果进行选择,仅输出满足条件的组
[ORDER BY…] --指定查询结果按一个或多个条件排序,默认升序ASC, 若要按降序排序,必须指明DESC选项
[LIMIT {[offset,]row_count | row_countOFFSET offset}];--指定查询记录从哪条到那条
分页
limit offset ,size;(起始索引从0开始)
SELECT * FROM table_name LIMIT 1,3; // 检索记录行2到4,即从1开始取3条记录。
SELECT * FROM table_name LIMIT 5; //检索前 5 个记录行
在MySQL中!=
和 <>
的功能一致,在sql92规范中建议是:!=
,新的规范中建议为: <>
SELECT * FROM t_user WHERE username != “陈哈哈”;
SELECT * FROM t_user WHERE username <> “陈哈哈”;
拼接字符串函数 concat(a,b)
SELECT USER,HOST,CONCAT('加密方式:',PLUGIN) 加密方式 FROM mysql.user;
2.3事务
事务:一个或一组sql语句组成一个执行单元
2.3.1事务的特性:
- 原子性:要么全部执行,要么全部不执行
- 一致性:事务中的操作保证数据库中的数据不会出现逻辑上不一致的情况
- 隔离性:并发执行的各个事务之间互不干扰
- 持久性:事务成功提交后不可逆,被持久化到数据库中,即使随后系统出现故障也不会受到影响
--mysql默认开启事务自动提交
set autocommit = 0; --关闭事务提交
set autocommit = 1; --开启事务提交
--模拟转账
set autocommit = 0; --关闭自动提交
start transaction --开启一个事务
update account set money=money-500 where name ='a';
update account set money=money+500 where name ='b';
commit; --提交事务
set autocommit = 1; --恢复默认值
2.3.2事务的分类:
隐式事务:事务提交或回滚之后,dbms自动开始新的事务
不需要采用begin transactioon语句标识事务的开始
比如
insert、update、delete语句本身就是一个事务
显式事务:使用Transact-SQL事务语句所定义的事务
具有明显的开启和结束事务的标志
BEGIN TRANSACTION --开启事务,取消自动提交事务的功能
COMMIT TRANSACTION --提交事务
ROLLBACK TRANSACTION --回滚事务
使用到的关键字
set autocommit=0;
start transaction;
commit;
rollback;
savepoint 断点
commit to 断点
rollback to 断点
2.3.3事务的隔离级别:
2.3.3.1事务并发问题如何发生?
当多个事务同时操作同一个数据库的相同数据时
2.3.3.2事务的并发问题有哪些?
对于两个事务T1,T2
**脏读:**一个事务读取到了另外一个事务未提交的数据。例如T1读取了T2已更新但未提交的数据,若T2回滚,T1读取到的内容是临时且无效的
不可重复读:同一个事务中,多次读取到的数据不一致。例如T1读取了一个字段,然后T2更新了该字段之后,T1再次读取同一个字段,值就不同了
**幻读:**一个事务读取数据时,另外一个事务进行更新,导致第一个事务读取到了没有更新的数据。比如T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行。如果T1再次读取同一个表,就会多出几行。
脏读与幻读非常类似,脏读一般针对更新,幻读一般针对插入,删除
2.3.3.3如何避免事务的并发问题?
数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.
**隔离级别:**一个事务与其他事务隔离的程度。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱.
数据库提供的 4 种事务隔离级别:
- Oracle 支持的 2 种事务隔离级别:READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED
- Mysql 支持 4 种事务隔离级别, Mysql 默认的事务隔离级别 为: REPEATABLE READ
通过设置事务的隔离级别来避免事务的并发问题
1、READ UNCOMMITTED
2、READ COMMITTED 可以避免脏读
3、REPEATABLE READ 可以避免脏读、不可重复读和一部分幻读
4、SERIALIZABLE 可以避免脏读、不可重复读和幻读
查看当前隔离级别:
select @@transaction_isolation;
设置隔离级别:
- 设置当前MySQL连接的隔离级别:set session transaction isolation level 隔离级别名;
- 设置数据库系统全局的隔离级别:set global transaction isolation level 隔离级别名;
2.4视图
含义:理解成一张虚拟的表
视图和表的区别: | 使用方式 | 占用物理空间 |
---|---|---|
视图 | 完全相同 | 不占用,视图是在使用视图时动态生成的,只保存了sql逻辑 |
表 | 完全相同 | 占用 |
视图的好处:
- 将复杂查询或常用查询封装到视图,sql语句提高重用性,效率高
- 和表实现了分离,提高了安全性
视图的创建
CREATE OR REPLASE VIEW 视图名
AS
查询语句;
视图的增删改查
对视图的insert,update,delete会更新原始表的数据。
--查看视图的数据
SELECT * FROM my_v4;
SELECT * FROM my_v1 WHERE last_name='Partners';
INSERT INTO my_v4(last_name,department_id) VALUES('虚竹',90);--插入视图的数据
UPDATE my_v4 SET last_name ='梦姑' WHERE last_name='虚竹';--修改视图的数据
DELETE FROM my_v4;--删除视图的数据
DROP VIEW test_v1,test_v2,test_v3;--视图的删除,删除视图不会影响基表的数据。
--视图结构的查看
DESC test_v7;
SHOW CREATE VIEW test_v7;
视图的可更新性
视图的可更新性(即insert,update,delete)和视图中查询的定义有关系,以下类型的视图是不能更新的。
- 包含以下关键字的sql语句:分组函数、distinct、group by 、having、union或者union all
- 常量视图
- Select中包含子查询
- join
- from一个不能更新的视图
- where子句的子查询引用了from子句中的表
修改视图
--方式一
ALTER VIEW 视图名
AS
查询语句;
--方式二
CREATE OR REPLACE VIEW 视图名
AS
查询语句; --调用CREATE OR REPLACE VIEW语句删除原来的视图并重建该视图
-
对于Oracle数据库:
调用ALTER VIEW语句可以添加、删除视图上的约束,但无法修改视图的结构
如果要修改视图的结构,只能调用CREATE OR REPLACE VIEW语句删除原来的视图并重建该视图
-
对于MySQL数据库:
ALTER VIEW语句与CREATE OR REPLACE VIEW语句都能重新定义查询语句
2.5变量
2.5.1系统变量
注意:如果是全局级别,需要加GLOBAL;如果是会话级别,需要加SESSION;如果不写,则默认SESSION。
一、全局变量
作用域:针对于所有会话(连接)有效,但不能跨重启,因为服务器每次启动将为所有的全局变量赋初始值。
--查看所有全局变量
SHOW GLOBAL VARIABLES;
--查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
--查看指定的系统变量的值
SELECT @@global.autocommit;
--为某个系统变量赋值
SET @@global.autocommit=0;--方式一
SET GLOBAL autocommit=0;--方式二
二、会话变量
作用域:针对于当前会话(连接)有效
--查看所有会话变量
SHOW SESSION VARIABLES;
--查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
--查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.transaction_isolation;
--为某个会话变量赋值
SET @@session.transaction_isolation='read-uncommitted';--方式一
SET SESSION transaction_isolation='read-committed';--方式二
2.5.2自定义变量
一、用户变量
作用域:针对于当前会话(连接)有效,与会话变量作用域相同;在begin end里或外都可以用。
- 声明并初始化:
SET @变量名=值;--方式一
SET @变量名:=值;--方式二
SELECT @变量名:=值;--方式三
- 赋值(更新),弱类型,赋什么值就是什么类型,类型随赋值而改变:
方式一:一般用于赋简单的值
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
方式二:一般用于赋表中的字段值
SELECT 字段名或表达式 INTO @变量名
FROM 表;
-
使用(查看用户变量的值):
select @变量名;
二、局部变量
作用域:仅在定义它的begin end中有用,只能放在begin end中第一句话
声明:
declare 变量名 类型 【default 值】;--若声明的时候赋值则加上default关键字指定初始化默认值
赋值:
方式一:一般用于赋简单的值
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT @局部变量名:=值;
方式二:一般用于赋表中的字段值
SELECT 字段名或表达式 INTO 局部变量名
FROM 表;
使用:
select 局部变量名;
二者的区别: | 作用域 | 定义位置 | 语法 |
---|---|---|---|
用户变量 | 当前会话 | 会话的任何地方 | 加@符号,不用指定类型 |
局部变量 | 定义它的BEGIN END中 | BEGIN END的第一句话 | 一般不用加@,需要指定类型 |
- 赋值(更新),弱类型,赋什么值就是什么类型,类型随赋值而改变:
方式一:一般用于赋简单的值
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
方式二:一般用于赋表中的字段值
SELECT 字段名或表达式 INTO @变量名
FROM 表;
-
使用(查看用户变量的值):
select @变量名;
二、局部变量
作用域:仅在定义它的begin end中有用,只能放在begin end中第一句话
声明:
declare 变量名 类型 【default 值】;--若声明的时候赋值则加上default关键字指定初始化默认值
赋值:
方式一:一般用于赋简单的值
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT @局部变量名:=值;
方式二:一般用于赋表中的字段值
SELECT 字段名或表达式 INTO 局部变量名
FROM 表;
使用:
select 局部变量名;
二者的区别: | 作用域 | 定义位置 | 语法 |
---|---|---|---|
用户变量 | 当前会话 | 会话的任何地方 | 加@符号,不用指定类型 |
局部变量 | 定义它的BEGIN END中 | BEGIN END的第一句话 | 一般不用加@,需要指定类型 |