MySQL数据库
注意事项
1.where查询=号,不是==
2.MySQL 字符串下标从1开始,LIMIT限定查询,下标从0开始
3,教学视频的sql文件,salary是varchar类型,所以+0,表示int类型
4.像subject这种关键字的,用``,`subject`,即可
5.MD5加密演示
CREATE TABLE testbd(
ID INT(4),
name1`testbd` VARCHAr(20),
pwd VARCHAr(50)
)CHARSET=utf8;
#明文密码
INSERT INTO testbd(ID,name1,pwd) VALUES (1,'zhansgan','123456');
INSERT INTO testbd(ID,name1,pwd) VALUES (2,'lisi','123456');
INSERT INTO testbd(ID,name1,pwd) VALUES (3,'wangwu','123456');
#插入加密密码
INSERT INTO testbd(ID,name1,pwd) VALUES (4,'JDBC',MD5('123456'));
#更新MD5密码
UPDATe testbd SET pwd=MD5(pwd) WHERe ID=1;
#如何校验,将用户传进来的密码,进行md5加密,然后比对加密后的值
SELECT * FROM testbd WHERe name1='lisi' AND pwd=MD5('123456'); #括号里就是用户传进来的密码值
一、前言
MySQL目录介绍
bin文件夹 | 存放可执行的命令文件 |
---|
lib文件夹 | 存放库文件 |
include文件夹 | 存放头文件 |
share文件夹 | 存放字符集 语言等信息 |
MySQL自带数据库
information_schema | 信息数据库 |
---|
mysql | 核心数据库 |
performance_schema | 性能优化的数据库 |
sys | 系统数据库 |
命令行操作数据库
#创建数据库mydb3
CREATE DATAbase mydb3 CHARACTER SET utf8;
#查看数据库创建时候信息
show create database mydb3;
#修改数据库字符集为utf8
alter database mydb3 character set utf8;
#删除数据库
drop database mydb3;
#看当前正在使用哪个数据库
select database();
#我们要使用数据库mydb3
use mydb3;
执行外部sql脚本文件
#先创建数据库
CREATE DATAbase companydb CHARACTER SET utf8;
#刷新对象浏览器,再对着companydb数据库-右键-导入-执行SQL脚本-选择存放位置-执行-是-完成
二、数据库的基础查询
数据库的基本查询
- 执行查询语句返回的结果集是一张虚拟表,结果不影响原表
查询部分列
select 列名 from 表名;
查询所有列
#效率比较低,系统还是会转换到全列名查询
select * from 表名;
对列中的数据进行运算
#这一列是数字,列名加减乘除
SELECt EMPLOYEE_ID,SALARY*12 FROM t_employees;
列的别名(AS)
#AS ''
SELECt EMPLOYEE_ID AS '工号',SALARY*12 AS '年薪' FROM t_employees;
查询结构去重(DISTINCT)
#去掉重复DISTINCT
SELECt DISTINCT MANAGER_ID FROM t_employees;
排序查询(order by)
语法 | select 列名 from 表名 order by 排序列 [排序规则]; |
---|
排序规则 | ASC升序 DESC降序 |
依据单列排序
#salary+0,让他被mysql认为int类型,按照数字大小比较而不是ASCII码比较
#输出员工姓名对应的工资,按照工资升序排列
SELECt FIRST_NAME AS '姓名',SALARY AS '月薪'
FROM t_employees
ORDER BY SALARY+0 ASC;
依据多列排序
#多列排序,先满足排序第一个salary工资,再比较第二个工号排序
#查询员工工号、名字、工资,按照工资进行升序排序(工资相同,按照工号升序排序)
SELECt EMPLOYEE_ID AS '工号',FIRST_NAME AS '姓名',SALARY AS '月薪'
FROM t_employees
ORDER BY SALARY+0 ASC,EMPLOYEE_ID ASC;
条件查询
关键字 | 描述 |
---|
WHERe 条件 | 在查询结果中,筛选符合条件的查询结果,条件为布尔表达式 |
语法 | SELECt 列名 FROM 表名 WHERe 条件; |
等值判断(=)
#查询薪资是11000的员工信息(工号名字)
SELECt EMPLOYEE_ID AS '工号',FIRST_NAME AS '姓名'
FROM t_employees
WHERe SALARY=11000;
逻辑判断(and or not)
#查询工资是11000并且提成是0.30的员工信息(工号名字)
SELECt EMPLOYEE_ID AS '工号',FIRST_NAME AS '姓名'
FROM t_employees
WHERe SALARY=11000 AND COMMISSION_PCT=0.30;
不等值判断(>,<,>=,<=,!=,<>)
#查询员工的工资在6000-10000之间的员工信息(工号名字工资)
SELECt EMPLOYEE_ID AS '工号',FIRST_NAME AS '姓名',SALARY AS '工资'
FROM t_employees
WHERe SALARY>=6000 AND SALARY<=10000;
区间判断(between,and)
#查询员工的工资在6000-10000之间的员工信息(工号名字工资)
SELECt EMPLOYEE_ID AS '工号',FIRST_NAME AS '姓名',SALARY AS '工资'
FROM t_employees
WHERe SALARY BETWEEN 6000 AND 10000;
NULL值判断(IS NULL,IS NOT NULL)
#查询没有提成的员工信息(工号工资月薪提成)
SELECt EMPLOYEE_ID AS '工号',FIRST_NAME AS '姓名',SALARY AS '工资' ,COMMISSION_PCT AS '提成'
FROM t_employees
WHERe COMMISSION_PCT IS NULL;
枚举查询(IN (a,b,c))
#查询部门编号为70,80,90的员工信息
SELECt EMPLOYEE_ID AS '工号',FIRST_NAME AS '姓名',DEPARTMENT_ID AS '部门编号'
FROM t_employees
WHERe DEPARTMENT_ID IN(70,80,90);
模糊查询
模糊查询只能和LIKE关键字结合使用 | |
---|
LIKE_(单个任意字符) | 列名 LIKE ‘张_’ |
LIKE%(任意长度的任意字符) | 列名 LIKE ‘张%’ |
#查询名字以"L"开头的员工信息
SELECt EMPLOYEE_ID AS '工号',FIRST_NAME AS '姓名',SALARY AS '工资',DEPARTMENT_ID AS '部门编号'
FROM t_employees
WHERe FIRST_NAME LIKE 'L%';
#查询名字以"L"开头并且长度为4的员工信息,3个下划线
SELECt EMPLOYEE_ID AS '工号',FIRST_NAME AS '姓名',SALARY AS '工资',DEPARTMENT_ID AS '部门编号'
FROM t_employees
WHERe FIRST_NAME LIKE 'L___'; #3个下划线,前面L,长度就为4了
分支结构查询
#select句子加逗号
SELECT 列名 ,
CASE
WHEN 条件1 THEN 结果1
WHEN 条件1 THEN 结果1
WHEN 条件1 THEN 结果1
ELSE 结果
END
FROM 表名
========================================================================================
#查询员工信息,输出薪资级别
SELECt EMPLOYEE_ID AS '工号',FIRST_NAME AS '姓名',SALARY AS '工资',DEPARTMENT_ID AS '部门编号',
CASE
WHEN SALARY>=10000 THEN 'A'
WHEN SALARY>=8000 AND SALARY<10000 THEN 'B'
WHEN SALARY>=6000 AND SALARY<8000 THEN 'C'
WHEN SALARY>=4000 AND SALARY<6000 THEN 'D'
ELSE 'E'
END AS '薪资级别'
FROM t_employees ;
时间查询
#当前系统时间
SELECt SYSDATE();
#2021-09-14 21:12:22
#获取当前日期
SELECT CURDATE();
#2021-09-14
#获取当前时间
SELECT CURTIME();
#21:13:46
#获取指定日期为一年中的第几周
SELECT WEEK(SYSDATE());
#37
#获取指定日期的年份
SELECT YEAR('2020-4-1');
#2020
#获取指定时间的小时值
SELECT HOUR(CURTIME());
#21
#获取DATE1和DATE2之间相隔的天数
SELECT DATEDIFF('2020-4-1','2019-4-1');
#366
#计算DATE加上N天后的日期
SELECT ADDDATE('2021-9-14',6);
#2021-09-20
字符串查询
#将多个字符串连接
SELECT CONCAt('My','S','QL');
#MySQL
SELECT CONCAt(FIRST_NAME,LAST_NAME)FROM t_employees;
#把外国人名字拼接,做成姓名
#将str中指定pos位置开始len长度的内容替换为newStr
SELECt INSERT('这是一个数据库',3,2,'MySQL');
#这是MySQL数据库
#将指定字符串转换为小写
SELECT LOWER('MYSQL');
#mysql
#将指定字符串转换为大写
SELECT UPPER('mysql');
#MYSQL
#将str字符串指定num位置开始截取len个内容
SELECT SUBSTRINg('JavaMySQLOracle',5,5);
#MySQL
聚合函数(自动忽略null值,不进行统计)
单列总和
#求工资总和
SELECT SUM(SALARY) FROM t_employees;
单列平均值
#求平均工资
SELECt AVG(SALARY+0) FROM t_employees;
单列最大值
#求最高工资
SELECt MAX(SALARY+0) FROM t_employees;
单列最小值
#求最低工资
SELECt MIN(SALARY+0) FROM t_employees;
总行数
#查询有多少员工,count就是求这一列数据有多少行
SELECt COUNT(EMPLOYEE_ID) FROM t_employees;
分组查询(GROUP BY)
语法 | SELECt 列名 FROM 表名 WHERe 条件 GROUP BY 分组依据(列); |
---|
GROUP BY | 分组依据,必须在WHERe之后生效 |
查询各部门的总人数
#按照部门编号进行分组
#再针对各部门的人数进行统计
SELECt DEPARTMENT_ID,COUNT(EMPLOYEE_ID)
FROM t_employees
GROUP BY DEPARTMENT_ID;
查询各部门的平均工资
SELECt DEPARTMENT_ID,AVG(SALARY+0)
FROM t_employees
GROUP BY DEPARTMENT_ID;
查询各部门各岗位人数
#按照部门编号进行分组(分组依据:DEPARTMENT_ID)
#按照岗位名称进行分组(分组依据:JOB_ID)
#再针对每个部门中,各个岗位人数进行统计,count
SELECt DEPARTMENT_ID,JOB_ID,COUNT(EMPLOYEE_ID) AS '总人数'
FROM t_employees
GROUP BY DEPARTMENT_ID,JOB_ID;
分组过滤查询(HAVINg)
#统计60 70 90部门的最高工资
#确定分组依据DEPARTMENT_ID
#对分组后的数据,过滤出60 70 90信息
SELECt DEPARTMENT_ID,MAX(SALARY) AS '最高工资'
FROM t_employees
GROUP BY DEPARTMENT_ID
HAVINg DEPARTMENT_ID IN (60,70,90);
限定查询(LIMIT)
语法 | SELECt 列名 FROM 表名 LIMIT 起始行,查询行数 |
---|
LIMIT offset_start,row_count | 限定查询结果的起始行和总行数 |
查询前5行记录
SELECt * FROM t_employees LIMIT 0,5;#第一行到第五行
查询范围记录
#查询表中从第四行开始,查询10行
SELECt * FROM t_employees LIMIT 3,10;
LIMIT典型应用(分页查询)
#分页查询:一页显示10条,一共查询3页
SELECt * FROM t_employees LIMIT 0,10;#第一页从第0条开始显示10条
SELECt * FROM t_employees LIMIT 10,10;#第二页从第10条开始显示10条
SELECt * FROM t_employees LIMIT 20,10;#第三页从第20条开始显示10条
查询总结
SQL语句编写顺序
select 列名 from 表名 where 条件 group by 分组 Having 过滤条件 order by 排序 LIMIT 起始行,总行数 ;
SQL语句执行顺序
1 | FROM | 指定数据来源表 |
---|
2 | WHERe | 对查询数据做第一次过滤 |
3 | GROUP BY | 分组 |
4 | HAVINg | 对分组后的数据第二次过滤 |
5 | SELECt | 查询各字段的值 |
6 | ORDER BY | 排序 |
7 | LIMIT | 限定查询结果 |
三、数据库的进阶查询
子查询
子查询(作为条件判断,一行一列)
select 列名 from 表名 where 条件{子查询结果}
查询工资大于Bruce的员工信息
#先查询到Bruce工资
SELECt SALARY FROM t_employees WHERe FIRST_NAME = 'Bruce';#工资6000
#查询工资大于6000的员工信息
SELECt * FROM t_employees WHERe SALARY>6000;
#上面两条语句整合成一句
SELECt * FROM t_employees WHERe SALARY>(SELECt SALARY FROM t_employees WHERe FIRST_NAME = 'Bruce');
#一行一列
子查询(作为枚举查询条件ALL,ANY,多行一列)
查询与姓King同一部门的员工信息
#先查询到名字叫King的
SELECt DEPARTMENT_ID FROM t_employees WHERe LAST_NAME = 'King';
#查询到2个人,一个部门80,一个部门90
#查询80 90部门员工信息
SELECt * FROM t_employees WHERe DEPARTMENT_ID IN (80,90);
#上面两条语句整合成一句
SELECt * FROM t_employees WHERe DEPARTMENT_ID IN (SELECt DEPARTMENT_ID FROM t_employees WHERe LAST_NAME = 'King');
#多行一列
查询工资高于部门60所有人的工资信息
#先查询到部门60所有人的工资
SELECt SALARY FROM t_employees WHERe DEPARTMENT_ID = 60;
#高于最高的ALL
SELECt SALARY FROM t_employees WHERe SALARY >ALL(SELECt SALARY FROM t_employees WHERe DEPARTMENT_ID = 60);
#高于最低的Any
SELECt SALARY FROM t_employees WHERe SALARY >ANY(SELECt SALARY FROM t_employees WHERe DEPARTMENT_ID = 60);
#多行一列
子查询(作为一张表,多行多列)
要对其赋予临时表名
查询员工表中工资排名前5名的员工信息
#先对所有人的工资进行降序排序
(SELECt EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees ORDER BY SALARY DESC) AS temp;#要命名临时表
#在查询临时表中前五行员工信息
SELECt EMPLOYEE_ID,FIRST_NAME,SALARY FROM (临时表) LIMIT 0,5;
#合并
SELECt EMPLOYEE_ID,FIRST_NAME,SALARY FROM ((SELECt EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees ORDER BY SALARY DESC) AS temp) LIMIT 0,5;
合并查询(了解即可)
合并两张表的结果(UNIOn去除重复记录)
SELECt * FROM t_departments UNIOn SELECt * FROM t_jobs;
#要求列数必须相同,列的数据类型可以不同
合并两张表的结果(UNIOn ALL保留重复记录)
SELECt * FROM t_departments UNIOn ALL SELECt * FROM t_jobs;
表连接查询
内连接查询(INNER JOIN)
- SELECt * FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号
#查询所有有部门的员工信息
#SQL标准
SELECt * FROM t_employees
INNER JOIN t_jobs
ON t_employees.`JOB_ID`=t_jobs.`JOB_ID`;
#MySQL标准
SELECt * FROM t_employees,t_jobs WHERe t_employees.`JOB_ID`=t_jobs.`JOB_ID`;
三表连接查询
#查询所有员工工号,名字,部门名称,部门所在国家ID
SELECt * FROM t_employees
INNER JOIN t_departments
ON t_employees.`DEPARTMENT_ID`=t_departments.`DEPARTMENT_ID`
INNER JOIN t_locations
ON t_locations.`LOCATION_ID`=t_departments.`LOCATION_ID`;
左外连接(LEFT JOIN)
#查询所有员工信息,以及所对应的部门名称
SELECt EMPLOYEE_ID,FIRST_NAME,SALARY,DEPARTMENT_NAME FROM t_employees
LEFT JOIN t_departments
ON t_departments.`DEPARTMENT_ID`=t_employees.`DEPARTMENT_ID`;
#左外连接,以左表为主表,向右表去匹配,匹配到返回结果,匹配不到返回NULL
右外连接(RIGHT JOIN)
#查询所有员工信息,以及所对应的部门名称
SELECt EMPLOYEE_ID,FIRST_NAME,SALARY,DEPARTMENT_NAME FROM t_employees
RIGHT JOIN t_departments
ON t_departments.`DEPARTMENT_ID`=t_employees.`DEPARTMENT_ID`;
#右外连接,以右表为主表,向左表去匹配,匹配到返回结果,匹配不到返回NULL
四、DML操作
新增(insert)
#添加一条工作岗位的信息
INSERT INTO t_jobs(job_id,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES ('Java_Le','Java_Lecturer',2500,9000);
修改(UPDATe)
#修改编号为100的员工的工资为25000
UPDATE t_employees SET SALARY=25000 WHERe EMPLOYEE_ID='100';
#绝大部分情况下,都要加where,否则为整表更新
删除(DELETE)
#删除编号为135的员工
DELETE FROM t_employees WHERe EMPLOYEE_ID = '135';
#绝大部分情况下,都要加where,否则为删除整张表的数据
清空整表数据(TRUNCATE)
#清空mydb整张表
TRUNCATE TABLE mydb;
#delete是删除表的数据,TRUNCATE是把表销毁,再按照原表格式创建一张新表,drop是删除数据库
五、数据表操作
数据类型
数值类型
类型 | 大小 | 用途 |
---|
int | 4字节 | 大整数值 |
double | 8字节 | 双精度浮点数值 |
double(m,d) | 8字节,m表示长度d表示小数位数 | 双精度浮点数值,double(5,2)代表-999.99-999.99 |
decimal(m,d) | m最大值为65 | 小数值 |
日期类型
类型 | 大小 | 格式 | 用途 |
---|
DATE | 3 | YYYY-MM-DD | 日期值 |
TIME | 3 | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | YYYY | 年份值 |
DATETIME | 8 | YYYY-MM-DD/HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | YYYYMMDDHHMMSS | 混合日期和时间值,时间戳 |
字符串类型
类型 | 大小 | 用途 |
---|
CHAR | 0-255字符 | 定长字符串 |
VARCHAR | 0-65535字节 | 可变成字符串 |
BLOB(视频,图片) | 0-65535字节 | 二进制形式的长文本数据 |
TEXT | 0-65535字节 | 长文本数据 |
数据表的创建(create)
列名 | 数据类型 | 说明 |
---|
subjectID | INT | 课程编号 |
subjectName | VARCHAR | 课程名称 |
subjectHours | INT | 课程时长 |
CREATE TABLE subject1(
subjectID INT,
subjectName VARCHAr(20),
subjectHours INT
)CHARSET=utf8;
INSERT INTO subject1(subjectID,subjectName,subjectHours) VALUES (1,'Java',40);
INSERT INTO subject1(subjectID,subjectName,subjectHours) VALUES (2,'MySQL',20);
INSERT INTO subject1(subjectID,subjectName,subjectHours) VALUES (3,'JDBC',30);
数据表的修改(alter)
#向现有表中添加列
#在课程表基础上添加gradeID列
ALTER TABLE subject1 ADD gradeID INT;
#修改表中的列
#修改课程表中课程名称长度为10个字符
ALTER TABLE subject1 MODIFY subjectName VARCHAr(10);
#删除表中的列,每次只能删除一列
#删除课程表中gradeID列
ALTER TABLE subject1 DROP gradeID;
#修改列名
#修改课程表中subjectHours列为classHours
ALTER TABLE subject1 CHANGE subjectHours classHours INT;
#修改表名
#修改课程表subject1为sub
ALTER TABLE subject1 RENAME sub;
数据表的删除(drop)
#删除mydb表
DROP TABLE mydb;
六、约束
实体完整性约束
- 表中的一行数据代表一个实体,实体完整性的作用即是标识每一行数据不重复、实体唯一
主键约束(PRIMARY KEY )
- PRIMARY KEY 唯一,标识表中的一行数据,此列的值不可重复,且不能为NULL
CREATE TABLE subject1(
subjectID INT PRIMARY KEY, #把id作为主键,不唯一
subjectName VARCHAr(20),
subjectHours INT
)CHARSET=utf8;
#一般加主键的都是id,工号,编号这种
唯一约束(UNIQUE)
- UNIQUE 唯一,标识表中的一行数据,不可重复,可以为NULL
CREATE TABLE subject1(
subjectID INT PRIMARY KEY, #把id作为主键,不唯一
subjectName VARCHAr(20) UNIQUE, #课程名字不能重复,不能id=1,java课程和id=2,java课程同时存在
subjectHours INT
)CHARSET=utf8;
自动增长列(AUTO_INCREMENT)
- 给主键的数值列添加自动增长,从1开始,每次加1,不能单独使用,和主键配合
CREATE TABLE subject1(
subjectID INT PRIMARY KEY AUTO_INCREMENT, #自动增长列,只能在主键后面加AUTO_INCREMENT
subjectName VARCHAr(20) UNIQUE, #课程名字不能重复,不能id=1,java课程和id=2,java课程同时存在
subjectHours INT
)CHARSET=utf8;
INSERT INTO subject1(subjectName,subjectHours) VALUES ('Java',20); #自动增长,id列不用写了
INSERT INTO subject1(subjectName,subjectHours) VALUES ('HTML',20); #自动增长,id列不用写了
域完整性约束
非空约束(NOT NULL)
CREATE TABLE subject1(
subjectID INT PRIMARY KEY AUTO_INCREMENT,
subjectName VARCHAr(20) UNIQUE NOT NULL, #课程名称不允许为null
subjectHours INT
)CHARSET=utf8;
INSERT INTO subject1(subjectName,subjectHours) VALUES (NULL,20); #NULL无法插入
默认值约束(default)
- default值 为列赋予默认值,当新增数据不指定值时,书写default,以指定的默认值进行填充
CREATE TABLE student(
studentAge CHAr(10) DEFAULT 20,
studentSex CHAr(10) DEFAULT '男' #default一般用于性别,默认男
)CHARSET=utf8;
INSERT INTO student(studentAge,studentSex) VALUES (30,DEFAULT); #默认default
引用完整性约束(引用)
#语法:ConSTRAINT fk_引用表名_引用列名 FOREIGN KEY(引用列名) REFERENCES 被引用表名(被引用列名)
#当两张表存在引用关系时,要执行删除操作,一定要先删除从表(引用表),再删除主表(被引用表)
#专业表
CREATE TABLE Speciality(
id INT PRIMARY KEY AUTO_INCREMENT,
SpecialName VARCHAr(20) UNIQUE NOT NULL
)CHARSET=utf8;
INSERT INTO Speciality(SpecialName) VALUES ('Java');
INSERT INTO Speciality(SpecialName) VALUES ('HTML');
#课程表
CREATE TABLE subject1(
subjectID INT PRIMARY KEY AUTO_INCREMENT,
subjectName VARCHAr(20) UNIQUE NOT NULL, #课程名称不允许为null
subjectHours INT DEFAULT 10,
specialid INT NOT NULL,
ConSTRAINT fk_subject1_specialid FOREIGN KEY(specialid) REFERENCES Speciality(id)
)CHARSET=utf8;
INSERT INTO subject1(subjectName,subjectHours,specialid) VALUES ('JavaSe',30,1);
七、事务
模拟转账
正常转账
CREATE TABLE account(
id INT,
money INT
)CHARSET=utf8;
INSERT INTO account(id,money) VALUES (1,10000);
INSERT INTO account(id,money) VALUES (2,1000);
#模拟转账,账户1给账户2转账1000
#账户1减1000
UPDATE account SET money=money-1000 WHERe id=1;
#账户2加1000
UPDATE account SET money=money+1000 WHERe id=2;
错误转账
CREATE TABLE account(
id INT,
money INT
)CHARSET=utf8;
INSERT INTO account(id,money) VALUES (1,10000);
INSERT INTO account(id,money) VALUES (2,1000);
#模拟转账,账户1给账户2转账1000
#账户1减1000
UPDATE account SET money=money-1000 WHERe id=1;
#如果上一条减钱操作执行完了,程序出现异常或错误,没有执行加钱,而减钱是成功的,加钱失败的
#账户2加1000
UPDATE account SET money=money+1000 WHERe id=2;
事务的概念
事务是一个原子操作,是一个最小执行单元,在一个事务中,所有SQL语句都执行成功,整个事务成功,有一条语句执行失败,则整个事务都失败
事务的边界(commit,rollback)
开始:连接到数据库,执行一条DML语句,上一个事务结束后,又输入了一条DML语句,即事务的开始
结束:
(1)提交:
a:显示提交,commit
b:隐式提交,一条创建、删除的语句,正常退出客户端连接
(2)回滚:
a:显示回滚,rollback
b:隐式回滚,非正常退出(断电 宕机),执行了创建、删除语句,但是失败了,会为这个无效的语句执行回滚
事务的原理
数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段),一个事务中所有的增删改语句的执行结果都会缓存在回滚段中,只有当事务中所有SQL语句都正常结束,才会将回滚段中的数据同步到数据库,否则无论因为哪种原因失败,整个事务将回滚
事务的特性(acid)
- 原子性:表示事务内部所有操作是一个整体,要么全部成功,要么全部失败
- 一致性:表示事务内部有一个操作失败,所有更改过的数据都必须回滚到修改前状态
- 隔离性:事务查看数据操作时数据所处的状态,要么是另一并发事务修改它之前的状态,要么是另一事务修改它之后的状态,事务不会查看中间状态的数据
- 持久性:持久性事务完成之后,它对于系统的影响是永久性的
事务应用(转账)
CREATE TABLE account(
id INT,
money INT
)CHARSET=utf8;
INSERT INTO account(id,money) VALUES (1,10000);
INSERT INTO account(id,money) VALUES (2,1000);
#模拟转账,账户1给账户2转账1000
#1开启事务
START TRANSACTION;
#2事务内部数据操作语句
UPDATE account SET money=money-1000 WHERe id=1;
UPDATE account SET money=moneys+1000 WHERe id=2;
#3事务内部语句都成功了,执行commit
COMMIT;
#4事务内部语句出现错误,执行rollback
ROLLBACK;
八、权限管理
创建用户
#语法:CREATE USER 用户名 IDENTIFIED BY 密码;
CREATE USER `zhangsan` IDENTIFIED BY '123';
用户授权
#语法:GRANT ALL ON 数据库.表 TO 用户名;
GRANT ALL ON companydb.* TO `zhangsan`;
撤销用户权限
#语法:REVOKE ALL ON 数据库.表 FROM 用户名;
#zhangsan要重新连接客户端,才会生效
REVOKE ALL ON companydb.* FROM `zhangsan`;
删除用户
DROp USER `zhangsan`;
九、视图(从表中拉数据创建真实表)
概念,特点
- 视图,是一张虚拟表,作用和真实表一样,视图中可以用select,增删改查询修改数据,视图可以使用户操作更方便,并保障数据库系统安全
- 优点:简单化,数据所见即所得;安全性,用户只能查询或修改他们所能见到的数据;逻辑独立性,可以屏蔽真实表结构变化带来的影响
- 缺点:性能相对较差,简单的查询也会变得稍显复杂;修改不方便,特别是复杂的聚合视图基本无法修改
- 视图不会独立存储数据,原表发生改变,视图也发生改变,视图没有优化任何查询性能
- 如果视图包含以下结构中的一种,则视图不可更新
- 聚合函数结果;DISTINCT去重后的结果;GROUP BY分组后的结果;HAVINg帅选过滤后的结果;UNIOn UNIOn ALL联合后的结果
创建、使用视图
#创建视图
#语法:CREATE VIEW 视图名 AS 来源;
#创建t_empInfo视图,其视图从t_employees表中查询到员工工号 姓名 邮箱 工资
CREATE VIEW t_empInfo
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY FROM t_employees;
#使用视图
SELECt * FROM t_empInfo;
修改视图
#方式一:CREATE OR REPLACe VIEW 视图名 AS 查询语句 如果视图存在则修改,反之进行创建
#方式二:ALTER VIEW 视图名 AS 查询语句 直接对已存在视图进行修改
#如果视图存在则修改,反之进行创建
CREATE OR REPLACE VIEW t_empInfo
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY,DEPARTMENT_ID FROM t_employees;
#直接对已存在视图进行修改
ALTER VIEW t_empInfo
AS
SELECt EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY FROM t_employees;
删除视图
#语法:DROP VIEW 视图名;
DROP VIEW t_empInfo;
UML类图
- 类图描述了系统中的类与类之间的关系,即系统的逻辑结构,这个图是构建其他图形的基础
- 一个类由类名称(Name) 属性(Attribute) 方法(Operation)
- 带三角的实线表示继承 ———▷
- 带箭头的虚线表示依赖 ——>
- 直线上面加方法名,见名知意 -----eat-----
- 空心菱形,整体和部分的关系,部分存在于整体之外叫聚合——◇
- 实心菱形,部分不能存在于整体之外叫组合,比如动物园有访客中心,如果父类动物园没了,子类访客中心也就没了 ——◆
- 直线上,可以有1 ,n 代表一对多,多对多关系