1.MySQL基础
MySQL前言
1、前端(页面,展示数据!) 2、后端(连接数据库,链接前端) 3、数据库(mysql 存数据)
只会写代码,学好数据库,基本混饭吃! 操作系统。数据结构与算法!当一个不错的程序猿! 离散数学,数字电路,体系结构,编译原理。+实战经验,高级程序猿~优秀的程序猿~
为什么学习MySQL?
1、存数据 2、岗位需求 3、数据库是所有软件体系最核心的存在
什么是数据库?
数据库(DB DataBase) 作用:存储数据,管理数据 概念:数据仓库,安装在操作系统上
数据库分类
关系数据库:(SQL) Mysql,Oracle,SqlServer,DB2 通过表与表,行与列的关系进行数据存储
非关系数据库:(NO SQL) 通过对象的属性来决定
DBMS(数据库管理系统)
-
数据库管理软件,科学有效的管理我们的数据库,维护和获取数据
-
数据库管理系统管理存储在数据库中的数据(数据库管理系统------>数据库)
-
Mysql软件,数据库管理系统
1.1 MySQL简介
Mysql是一个关系型数据管理系统。前世:瑞典MySQL AB公司。今生:属于Oracle旗下产品。适用于:中小型网站,集群可以查看官网。MySQL:5.7稳定 8.0(这两个版本使用较多)
建议安装: 1、不用使用exe,注册表 2、尽可能使用压缩包
1.2 安装MySQL
安装教程(安装包):MySQL学习笔记(狂神说) - 你我不在年少 - 博客园 (cnblogs.com)
安装MySQL步骤
1.下载MySQL安装包并解压 2.把这个包放到自己的电脑环境目录下~ 3.配置环境变量 4.新建mysql配置文件ini [mysqld] #目录一定换成自己的 basedir=C:\Program Files\MySQL\mysql-5.7.19\ datadir=C:\Program Files\MySQL\mysql-5.7.19\data\ port=3306 skip-grant-tables 5.启动管理员模式下的CMD,运行所有的命令 6.安装mysql服务 7.初始化数据库文件 8.启动mysql,进去修改密码~ 9.进入mysql,通过命令行(-p后面不要加空壳),修改密码(sql语句后面一定要加分号! ) 10.注掉ini中的跳过密码 11.重启mysql,连接测试,如果连接成功就OK了!
CMD安装
清空服务:sc delete mysql
1.3基本命令操作
-
查看所有的数据库 show databases;
-
切换数据库 use 表名;
-
查看所有表 show tables;
-
显示数据库中所有表的信息 describe 库名;
-
创建数据库 create database 库名;
-
退出连接 exit;
-
单行注释 “--”或者”#“,"--"是sql本来注释,多行注释“/**/''
--所有的语句都要以分号结尾 show databases; --查看当前所有的数据库 use 数据库名; --打开指定的数据库 show tables; --查看所有的表 describe/desc 表名; --显示表的信息 create database 数据库名; --创建一个数据库 exit --退出连接
1.4 操作数据库
操作数据库、操作数据库中表的数据、操作数据库中的表的数据
数据库名若为特殊的字段,则可以加” `“,tab键上面;
-
创建数据库
create database [if not exists] 库名;
-
删除数据库
drop [if exists] 库名;
-
使用数据库
use 库名;
-
查看数据库
show databases;
-
标准的 SQL 语句分类:
-
数据查询语言(DQL):从表中获得数据,主要关键字:select
-
数据定义语言(DDL):创建、删除和修改数据库对象,主要关键字:create、alter、drop 和 truncate
-
数据操作语言(DML):添加、删除和修改表中的行,由 insert into、update 和 delete from 三个命令组成
-
事务处理语言(TPL):确保被 DML 语句影响的表的所有行及时得以更新,主要关键字:commit、rollback 和 savepoint
-
数据控制语言(DCL):为数据库用户授权,或者回收指定用户的权限,主要关键字:grant 和 revoke
-
指针控制语言(CCL):对一个或多个表单独行的操作
-
1.5 数据库列类型
数值
数据类型 | 描述 | 大小 |
tinyint | 十分小的数据 | 1个字节 |
smallint | 较小的数据 | 2个字节 |
mediumint | 中等大小的数据 | 3个字节 |
int | 标准的整数 | 4个字节 |
bigint | 较大的数据 | 8个字节 |
float | 浮点数 | 4个字节 |
double | 浮点数 | 8个字节 |
decimal | 字符串形式的浮点数,一般用于金融计算 |
字符串
数据类型 | 描述 | 大小 |
---|---|---|
char | 字符串固定大小 | 0~255 |
varchar | 可变字符串 | 0~65535 |
tinytext | 微型文本 | 2^8-1 |
text | 文本串 | 2^16-1 |
时间日期 java.util.Date
数据类型 | 描述 | 格式 |
date | 日期格式 | YYYY-MM-DD |
time | 时间格式 | HH:mm:ss |
datetime | 最常用的时间格式 | YYYY-MM-DD HH:mm:ss |
timestamp | 时间戳,1970.1.1到现在的毫秒数 | |
year | 年份表示 |
null
-
没有值,未知
-
注意,不要使用null进行运算,结果为null
1.6 数据库字段属性
unsigened:
-
无符号的整数
-
声明该列不能声明负数
zerofill:
-
0填充的
-
10的长度 1 – 0000000001 不足位数用0 填充
自增:
-
通常理解为自增,自动在上一条记录的基础上+1
-
通常用来设计唯一的主键 index,必须是整数类似
-
可以自定义设置主键自增的起始值和步长
NULL和not Null
-
假设设置为 not null,如何不给他赋值,就会报错
-
NULL 如果不填写,默认为NULL 默认:
-
设置默认的值!
Default:
-
用于设置默认值
-
例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值
auto_increment 自增 字符串 一般使用单引号 comment 注释 creat table studdent( 字段名 类型 [属性][索引][注释] )表类型 字符集设置 注释 SHOW CREATE DATABASE school -- 查看创建数据库的语句 SHOW CREATE TABLE student -- 查看student数据表的定义语句 DESC student -- 显示表的结构# create table <表名> ([列名] [类型(长度)] [not null] [auto_increment] [primary key (id)], [key <约束名> (外键字段)], [constraint <约束名> foreign key (<外键字段>) references <主键表名> (<主键字段>)] ) default charset=utf8; 其中:not null 表示该列不允许为空,如果为空,那么我们在插入数据的时候就会报错; auto_increment 表示该列支持自增长 primary key 指定一列的主键 如: create table clazz( id int(11) not null auto_increment, name varchar(20) not null, primary key (id) ); drop table if exists student; create table student( id int(11) not null auto_increment, name varchar(20) not null, sex char(1) not null, age int(3) not null, address varchar(500), clazz_id int(11) not null, primary key (id), key fk_clazz (clazz_id), constraint fk_clazz foreign key (clazz_id) references clazz (id) );
建议:
-
字段名 加
-
注释 comment
-
primary key最好追加在最后
1.7 数据库索引
INNODB
-
默认使用,安全性高,支持事务的处理,多表多用户操作
MYISAM
-
早些年使用,节约空间,速度较快
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间大小 | 较小 | 较大,约为2倍 |
在物理空间存在的位置 所有的数据库文件都存在data目录下,一个文件夹就对应一个数据库
本质还是文件的存储
MySQL 引擎在物理文件上的区别
-
innoDB 在数据库表中,只有一个*.frm文件,以及上级目录下的ibdata1文件
-
MYISAM 对应的文件
-
*.frm - 表结构的定义文件
-
*. MYD -数据文件
-
*.MYI 索引文件
-
设置数据库字符集编码
CHARTSET=UTF8
不设置的话,会是mysql默认的字符集编码-(不支持中文) 可以在my.ini中配置默认的编码
character-set-server=utf8
1.8 常见函数库
18.1 比较函数
-
expr is null
-
isnull(expr):判断 expr 是否为 null,如果为 null 则返回 1,否则返回 0
-
expr is not null:检验一个值是否为 null
-
expr not between min and max
18.2 聚合函数
-
多行函数(聚合函数、分组函数):对多行输入值(一组记录)整体计算,最后只会得到一个结果
-
count(expr):统计某个列值不为 NULL 的数量,用 * 表示统计该表内的所有行数(包括为 NULL 的行),注意:在 Java 中必须使用 long 接收
-
avg(expr):计算多行 expr 的平均值
-
sum(expr):计算多行 expr 的总和,需注意 NPE 问题:
ifnull(sum(col), 0)
-
max(expr):计算多行 expr 的最大值
-
min(expr):计算多行 expr 的最小值
-
group_concat([distinct] expr [, expr …] [order by {unsigned_integer | col_name | expr} [asc | desc] [, col_name …]] [separator str_val]):用 str_val(默认“,”)连接一个组内的 expr 指定的非 null 值的字符串,可用 col_name 进行组内排序
18.3 控制流函数
-
case value when compare-value then result [when compare-value then result …] [else result] end:返回与 compare-value 匹配的结果值,如果没有匹配的结果值,则返回结果为 else 后的结果,如果没有 else 部分,则返回值为 null
-
case when condition then result [when condition then result …] [else result] end:返回第一种情况的真实结果,如果没有匹配的结果值,则返回结果为 else 后的结果,如果没有 else 部分,则返回值为 null
-
if[expr1, expr2, expr3):如果 expr1 为 true(expr1 不等于 0 且不等于 null),则返回值为 expr2,否则返回值为 expr3
-
isnull(expr1):判断 expr1 是否为 null,如果为 null 则返回 true,否则返回 false
18.4 字符串函数
-
lower(str):将 str 中的字母全部变为小写
-
upper(str):将 str 中的字母全部变为大写
-
concat(str1, str2, …):字符串连接(若有任何一个参数为 null,则返回值为 null)
-
concat_ws(separator, str1, str2, …):第一个参数是其它参数的分隔符,用分隔符连接字符串,如果分隔符为 null,则结果为 null,函数会忽略任何分隔符参数后的 null 值
-
repeat(str, count):重复的 str 字符串 count 次
-
char_length(str):求 str 的字符个数
-
length(str):求 str 的字节个数
-
lpad(str, len, padstr):用 padstr 左填补 str 使其字符长度 len(若 str 的长度大于 len,则缩短 str 至 len 个字符)
-
rpad(str, len, padstr):用 padstr 右填补 str 使其字符长度 len(若 str 的长度大于 len,则缩短 str 至 len 个字符)
-
ltrim(str):删除 str 左边空格
-
rtrim(str):删除 str 右边空格
-
trim(str):删除 str 左右两边空格
-
replace(str, from_str, to_str):将 str 中的 from_str 全部替换为 to_str(大小写敏感)
-
left(str, len):返回从字符串 str 最左开始的长度为 len 的子字符串
-
right(str, len):返回从字符串 str 最右开始的长度为 len 的子字符串
-
substring(str, pos):从 str 返回一个子字符串,起始于位置 pos(若 pos 为负数,从 str 尾部开始计算),至 str 最后
-
substring(str, pos, len):从 str 返回一个字符长度为 len 的子字符串,起始于位置 pos,同义词:mid(str, pos, len)
18.4 数值函数
-
abs(x):求 x 的绝对值
-
mod(n, m):求 n 除以 m 的余数
-
ceil(x):求大于 x 的最小整数(向上取整)
-
floor(x):求小于 x 的最大整数(向下取整)
-
round(x) :用四舍五入对 x 取整
-
round(x, d):用四舍五入对 x 值保留到小数点后 d 位;若 d 为负数,表示对 x 的整数部位
-
truncate(x, d):截去 x 值第 d 位小数后的数字;若 d 为负数,截去(归零)小数点左起第 d 位开始后面所有低位的值
-
rand()、rand(n):返回一个随机浮点值,其范围为 0 ≤ v ≤ 1.0,若已指定一个整数参数 n,则它被用作种子值,用来产生重复序列;若要在 i ≤ r ≤ j 范围得到一个随机整数 r,需要用到表达式
18.5 日期时间函数
-
now():返回当前系统日期及时间
-
curdate()、current_date():返回当前系统日期
-
curtime()、current_time():返回当前系统时间
-
adddate(date, interval expr type)、date_add(date, interval expr type):将 date 值添加指定的时间间隔值
-
subdate(date, interval expr type)、date_sub(date, interval expr type):将 date 值减去指定的时间间隔值(说明:expr 是一个字符串表达式,用来指定从起始日期添加或减去的时间间隔值;type 为关键词,它指示了表达式被解释的方式,常见 type 值:year、quarters、month、week、day、hour、minute、second)
-
datediff(expr1, expr2):求 expr1 减去 expr2 得到的天数
-
period_add(p, n):将 n 个月添加到时段 p(格式为 YYMM 或 YYYYMM)上,返回值格式为 YYYYMM
-
period_diff(p1, p2):返回时段 P1 和 P2 之间的月份差值,P1 和 P2 的格式应为 YYMM 或 YYYYMM
-
获取日期时间中某个段
-
date(expr) :提取日期或时间日期表达式 expr 中的日期部分
-
year(date):返回 date 对应的年份,范围是从1000 到9999
-
quarter(date):返回 date 所对应的年中某季度,取值范围为 1 到 4
-
month(date):返回 date 对应的月份,范围时从 1 到 12
-
week(date[, mode]):返回 date 所对应的星期序号
-
yearweek(date[, mode]):返回 date 的年份及星期序号(mode 参数可以指定每星期起始日究竟是星期天还是星期一,以及返回值范围究竟是 0-53,还是从 1-53,如果忽略 mode 参数,就采用 default_week_format 系统变量值,默认为 0,即第一天是周日,返回值范围 0-53)
-
day(date)、dayofmonth(date):返回 date 参数所对应的一月中的第几天
-
dayofweek(date):返回 date 参数所对应的一年中的某一天,取值范围从 1 到 366
-
hour(time):返回 time 对应的小时数,范围是从 0 到 23
-
minute(time):返回 time 对应的分钟数,范围是从 0 到 59
-
last_day(date):返回该月最后一天对应的日期值
-
1.9操作表(增删查改)
改名:ALTER TABLE 旧表名 RENAME AS 新表名 增列:ALTER TABLE 表名 ADD字段名 列属性[属性] 修改:ALTER TABLE 表名 MODIFY 字段名 列类型[属性] 更改:ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性] 删除:ALTER TABLE 表名 DROP 字段名
修改表:alter table teacher rename as student; 添加字段:alter table teacher add age int(11); 修改约束:alter table teacher modify age varchar(10); 修改字段名:alter table teachar change age age1 int(11); 删除字段名:alter table teacher drop age; 删除表:drop table if exists teacher;
所有的创建与删除尽量加上判断,以免报错
注意点:
-
字段名,使用这个包裹
-
注释 – /**/
-
sql 关键字大小写不敏感,建议写小写
-
所有的符号全部用英文
19.1 数据库 DDL
-- 每个列定义之间用逗号隔开,最后一个列定义后**不需要**写逗号
create table student (
--列名 列类型 [默认值]
id bigint primary key auto_increment,
name varchar(20),
age int
) engine=InnoDB default charset=utf8mb4;
-- 复制表结构(不包括外键约束)
create table student_bak like student;
-- 只复制表数据到新表
create table student_bak select * from student;
-- 增加列定义
alter table 表名 add (
新列名 列类型 [默认值],
...
);
-- 修改列定义
alter table 表名
modify 列名 列类型 [默认值];
-- 删除列
alter table 表名
drop 列名;
19.2 表的约束
-
default '值':默认值
-
not null:非空约束,该列的内容不能为空
-
unique:唯一约束,在该表中,该列的内容必须唯一,但可以出现多个 null 值
-
primary key:主键约束,相当于非空约束和唯一约束
-
auto_increment[=值]:自增长,只能用于指定整型主键列,默认从 1 开始,步长为 1,向该表插入记录时可不为该列指定值,或指定为 null 或 0(可以通过设置
sql_mode = 'NO_AUTO_VALUE_ON_ZERO'
将自增值设置为 0)(只能有一个自增列,且必须被定义为主键) -
foreign key (外键列) references 主表 (参考列):外键约束从表外键列的值必须在主表被参照列的值范围之内,或者为 null,要求从表和主表的存储引擎都为 InnoDB
19.3 表 DML
数据库意义:数据存储,数据管理
DML语言:数据操作语言 Insert、update、delete
添加 Insert
insert
# insert into <表名> values(default,列1的值,列2的值,列n的值...); 如: insert into clazz values(default, '三年级1班');
语法:-- insert into 表名([字段一], [字段二])values(‘值1’),(‘值2’)
注意事项: 1.字段和字段之间用逗号分开 2.字段可以省略,但是后面的值必须一一对应 3.可以同时插入多条数据,VALUES后面的值需要使用,隔开即可
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('李四','aaaaa','男'),('王五','23232','女')
修改 update
update 修改谁(条件) set 原来的值=新值
-- 修改学员名字 UPDATE `student` SET `name`='囷' WHERE id =1; -- 不指定条件的情况下,会改动所有表UPDATE `student` SET `name`='233' -- 语法; -- UPDATE 表名 set column_name,[] = value where 条件
条件:where 子句 运算符 id 等于 某个值,大于某个值,在某个区间内修改
操作符返回布尔值
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 5=6 | false |
!= <> | 不等于 | 5!=6 | true |
> | 大于 | ||
< | 小于 | ||
>= | |||
<= | |||
between and | 在某个范围内,闭合区间 | ||
and | && | 5>1and1>2 | false |
or | || | 5>1or1>2 | true |
注意:
-
column_name 是数据库的列,带上``
-
条件,是筛选的条件,如果没有指定,则会修改所有的列
-
value 是一个具体的值,也可以是一个变量
-
多个设置的属性之间,使用英文逗号隔开
删除 delete
delete 命令
语法 delete from 表名 [where 条件]
-- 删除数据 (避免这样写)DELETE FROM `student`-- 删除指定DELETE FROM `student` where id= 1
TRUNCATE 命令
作用:完全清空一个数据库,表的结构和索引不会变
delete 和 TRUNCATE 区别
-
相同点: 都能删除数据,都不会删除表结构
-
不同:
-
TRUNCATE 重新设置自增列 计数器会归零
-
DELETE不会影响事务
-
DELETE和TRUNCATE区别
DELETE和TRUNCATE 的区别: DELETE可以条件删除(where子句),而TRUNCATE只能删除整个表 TRUNCATE 重新设置自增列,计数器会归零,而DELETE不会影响自增 DELETE是数据操作语言(DML - Data Manipulation Language),操作时原数据会被放到 rollback segment中,可以被回滚;而TRUNCATE是数据定义语言(DDL - Data Definition Language),操作时不会进行存储,不能进行回滚。
了解即可:delete删除的问题
重启数据库,现象
-
innoDB 自增列会从1开始(存在内存当中,断电即失)
-
MyISAM 继续从上一个自增量开始(存在文件中,不会丢失)
替换查询结果REPLACE
-- 如果表中的一个旧记录与一个用于 primary key 或一个 unique 索引的新记录具有相同的值,则在新记录被插入之前,旧记录被删除
-- 返回被删除和被插入的行数的和
replace [into] tbl_name [(col_name, ...)]
values ({expr | default}, ...), (...), ...
replace [into] tbl_name
set col_name = {expr | default}, ...
-- 替换查询结果
replace [into] tbl_name [(col_name,...)]
select ...
19.4 查询 DQL
单表查询格式
(8)select (9)distinct select_list -- 确定选择的列 (1)from left_table -- 确定查询哪一张表 (3)join_tpye join right_table (2)on join_condition (4)where where_condition -- 确定选择的行(不能使用 select 中定义的别名) (5)group by group_by_list -- 对结果集分组(MySQL 中对查询做了加强,可以使用 select 中定义的别名) (6)with cube|rollup (7)having having_condition -- 对分组过滤 (10)order by order_by_list -- 对结果集按照某列排序 (11)limit start_number, limit_number -- 对结果集分页
-
简单查询
select {*, 列 [[as] 别名], ...}
from 表名;
-- 如果列别名中使用关键字,或强制区分大小写,或有空格时,需使用 "" 或 '' 括起来
-- 使用 distinct 关键字从査询结果中清除重复行,必须放在要查询字段的开头
select distinct 列名, ...
from 表名;
-- 实现数学运算查询
-- 对数值型数据列可以使用算算术运算符(+ - * /)创建表达式
-- 两个日期之间可以进行减法运算,日期和数值之间可以进行加、减运算
-- 包括空值的任何算术表达式都等于空
-
where查询
select <selectList>
from 表名
where 条件表达式;
-- 优先级:所有的比较运算符、not(!)、and(&&)、or(||)
-- 可以使用 >、>=、<、<=、= 和 <> 等基本的比较运算符比较数值、字符串、日期之间的大小
-- SQL 中判断两个值是否相等的比较运算符是单等号,判断不相等的运算符是 <> 或 !=,SQL 中的赋值运算符是冒号等号(:=)
-- 特殊的比较运算符:between、in、is null、like
-- between 比较运算符,选出某一值域范围(闭区间)的记录
where 列名 between val1 and val2;
-- in 比较运算符,判断列的值是否在指定的集合中
where 列名 in (值1, 值2, ...);
-- is null 比较运算符、is not null 比较运算符,判断列的值是否为空
where 列名 is null;
-- like 比较运算符,执行通配查询/模糊查询
-- % 表示零或多个任意的字符
-- _ 表示一个任意的字符
where 列名 like '_%';
-
order by查询(asc:升序,缺省;desc:降序)
select <selectList>
from table_name
-- order by field(列名, val1, val2, val3) [asc|desc]:将获取出来的数据根据指定的顺序排序,即该列的其它值(视为相等) < val1 < val2 < val3,其中列名后面的参数自定义,不限制参数个数
order by 列1 [asc|desc], 列2 [asc|desc], field(列3, 值1, 值2, 值3, ...) [asc|desc], ...;
-
limit查询
-- MySQL 特有
-- limit 子句中不能进行数学运算
-- beginIndex:从结果集中的哪一条索引开始显示(beginIndex 从 0 开始)
-- beginIndex = (当前页数 - 1) * pageSize
-- pageSize:页面大小(每页最多显示多少条记录)
select <selectList>
from 表名
[where condition]
limit beginIndex, pageSize;
-
group by查询和(having查询一起使用)
select [distinct] *|分组字段1[, 分组字段2, …] | 统计函数
from 表名
[where 条件]
group by 分组字段1[, 分组字段2, …] [with cube|rollup]
[having 过滤条件(可以使用统计函数)]
-
多表联查
多表联查结构图(别人的)
-
内连接查询
-- 1. 隐式内连接:使用 where 指定连接条件,如等值连接(如果没有连接条件,会得到笛卡尔积)
select <selectList>
from A, B
where 连接条件;
-- 2. 显式内连接查询
select <selectList>
from A [inner] join B on 连接条件;
-- 在做等值连接的时候,若 A 表中和 B 表中的列名相同,则可以简写:
select <selectList>
from A [inner] join B using(同名列);
-
外连接查询
1、左外连接(left [outer] join):查询出 join 左边表的全部数据,右边的表不能匹配的数据使用 null 来填充数据 2、右外连接(right [outer] join):查询出 join 右边表的全部数据,左边的表不能匹配的数据使用 null 来填充数据 3、全外连接(full [outer] join):MySQL 不支持,可以通过 union + 左右连接查询来完成
select <selectList>
from A left|right [outer] join B on 连接条件;
-
自连接查询
1、如果同一个表中的不同记录之间存在主、外键约束关联,则需要使用自连接查询 2、本质:把一个表当成两个表来用,使用别名区分
select <selectList> from A a inner join A b on a.id=b.id
-
子查询
1、子查询必须要位于圆括号中 2、不能把同一个表既用于子查询的 from 子句,又用于更新目标,如 update t1 set column2 = (select max(column1) from t1);
2.0 MD5加密数据库级别
什么是MD5? 主要增强算法复杂度不可逆性。 MD5不可逆,具体的MD5是一样的 MD5破解原理,背后有一个字典,MD5加密后的值,加密前的值
CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=UTF8-- 明文密码
INSERT INTO testmd5 VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')-- 加密UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1UPDATE testmd5 SET pwd=MD5(pwd) WHERE id !=1 -- 加密全部-- 插入时加密
INSERT INTO testmd5 VALUES(4,'小明',MD5('123456'))INSERT INTO testmd5 VALUES(5,'红',MD5('123456'))-- 如何校验,将用户传递过来的密码,进行MD5加密,然后对比加密后的值
SELECT * FROM testmd5 WHERE `name`='红' AND pwd=MD5('123456')
2.1 事务(ACID)
要么都成功,要么都失败
SQL执行, A给B转账 A 1000–> 200 B200
SQL 执行, B收到A的钱 A800 — B400
将一组SQL放在一个批次中执行
事务原则 : ACID原则 原子性,一致性,隔离性,持久性 (脏读,幻读…)
原子性(Atomicity)
要么都成功,要么都失败
一致性(Consistency)
事务前后的数据完整性要保持一致
持久性(Durability)–事务提交
事务一旦提交就不可逆转,被持久化到数据库中
隔离性
事务产生多并发时,互不干扰
隔离产生的问题
脏读:
指一个事务读取了另外一个事务未提交的数据。
不可重复读:
在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。 (一般是行影响,多了一行)
执行事务
-- mysql 自动开启事务提交
SET autocommit=0 -- 关闭
SET autocommit=1 -- 开启(默认的)
-- 手动处理事务
SET autocommit =0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始,从这个之后的SQP都在同一个事务内
INSERT XX
INSERT XX
-- 提交 : 持久化(成功)
COMMIT
-- 回滚: 回到原来的样子(失败)
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
-- 了解
SAVEPOINT 保存点名称 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点 -- 删除保存点
2.2 账户管理
-- 创建用户
create user 'guest'@'localhost' identified by '1234';
-- 修改用户密码
alter user 'guest'@'localhost' identified by '123';
-- 授予用户权限
-- 注意:在授权操作之后,需要使用 flush privileges 命令刷新权限
grant 权限 on 数据库.数据库对象 to 用户名@'主机'
identified by '密码' -- 创建用户,设置密码
with grant option; -- 允许用户继续授权
grant all [privileges] on *.* to guest@'localhost'
identified by '1234'
with grant option;
-- 创建一个超级管理员,用户名为 dev,密码为 1234,只能在 192.168.%.% 登陆,可以给别人授权
grant all privileges on `edu-crm`.* to dev@'192.168.%.%' identified by '1234' with grant option;
flush privileges;
-- 查看用户的权限
show grants [for root@localhost]
-- 回收对用户的授权
revoke 权限 on 数据库对象 from 用户;
revoke all on *.* from guest@localhost;
- 删除用户
drop user 用户名@'主机';
drop user guest@'%';
2.3 其他
SHOW 语法
-
提供有关数据库、表、列或服务器状态的信息
-
show [full] processlist:查看哪些线程正在运行,如果不使用 full 关键词,则只显示每个查询的前 100 个字符(如果有 process 权限,可以看到所有线程,否则只能看到自己的线程)
-
User:发送 sql 语句到当前 MySQL 使用的是哪个用户
-
Host: 发送 sql 语句到当前 MySQL 的主机 ip 和端口
-
Db: 连接哪个数据库
-
Command: 连接状态,一般是 sleep(休眠空闲)、query(查询)、connect(连接)
-
Time: 连接执行时间
-
State: 当前 sql 语句的执行状态,如 Checking table(正在检查数据表)、Sending data(正在处理 select 查询的记录,返回数据)、Sorting for group(正在为 group by 做排序)、Sorting for order(正在为 order by 做排序)、Updating(正在搜索匹配的记录,并且修改它们)、Locked(被其它查询锁住了)
-
-
show [global | session] variables [like 'pattern']:查看服务器系统变量的值,如 '%query_cache%'、'validate_password%'
-
show [global | session] status [like 'pattern']:查看服务器状态信息,如 'Qcache%'、'Innodbbuffer_pool%'
其它管理语句
-
kill [connection | query] thread_id:终止线程,kill connection 与不含修改符的 kill 一样,它会终止与给定的 thread_id 有关的连接;kill query 会终止连接当前正在执行的语句,但是会保持连接的原状(如果有 super 权限,可以终止所有线程和语句,否则只能终止自己的线程)
3. 练习
/*
创建student表,有字段id,studentName,password,birthday,classID
创建class表,有字段id,className
student表中的classID是class中的id
*/
--创建表
create table [if not exists] `student`(
`id` int(20) not null comment '编号',
`studentName` varchar(20) not null comment '姓名',
`password` varchar(50) not null comment '密码',
`birthday` datetime default current_timestamp comment '生日',
primary key(`id`)
)engine=InnoDB default charset =utf8;
create table [if not exists] `class`(
`id` int(11) not null comment '班级编号',
`className` varchar(20) not null comment '班级名',
primary key(`id`)
)engine=InnoDB default charset=utf8;
--字段添加
alter table `student` add classID int(11);
--字段名更改
alter table `student` change studentName sname varchar(20);
--字段属性修改
alter table `student` modify birthday varchar(20);
--添加字段
alter table `student` add `className` varchar(20) comment '班级名';
--表名更改
alter table `student` rename to `student1`;
--添加外键
alter table `student` add constraint class_id foreign key(classID) references class(id);
--删除字段
alter table `student` drop studentName;
--查看表(结构)
show create table `student`;
--查看表(格式)
desc `student`;
--查看当前数据库中所有表
show tables;
--添加唯一约束
alter table `student` add constraint student_password unique (password);--索引名与字段名不一致
alter table `student` add uniuque key(password);--索引名与字段名一致
--删除唯一约束
alter table `student` drop index student_password;
--查看表索引
show index from `student`;
--CRUD操作
--1、添加
insert into `student`(`id`,`studentName`,...) values (组值1),(组值2)...
--2、查询(所有,单个,不同字段,别名显示,distinct,in,as,between and,or,and,%,_)
select * from `student`;
select * from `student` where id=1;
select id,studentName from student;
select id as `ID`,studentName as `name` from student;
select distinct password,id from `student`;--distinct将password作为首列输出
select * from student where id in (2,4);--区间查询
select * from student where id between 2 and 3;--区间查询
select * from student where id password=md5('123456') and id=4;--两者都
select * from student where id =3 or password=md5('234567');--单和双
select * from student where studentName like '%张';--%任意字符任意长度
select * from student where studentName like '张_';--任意字符单个长度
--3、更改(单个,全部)
update `student` set classID=(select id from class where className='语文') where id=1;--单个更改
update `student` set birthday='2022-09-09';--全部更改
--删除
delete from `student`;
truncate table `student`;
/*
一、delete 删除数据的速度慢,truncate 删除数据的速度快
二、delete 支持事务,可以进行事务回滚;truncate 不支持事务,不能执行事务回滚
三、delete 支持带条件的删除,可以只删除某一条数据;truncate 则不行,只能用于删除表中的所有数据
四、delete 删除不会重置自动增长(auto_increment),truncate 则会重置自动增长的值,重新以 1 开始
五、delete 可以触发 触发器,truncate 则不行
*/
delete from `student` where id=1;
--删除表
drop table `student`;--结构等等全删
delete from `student`;--保留结构
truncate table `student`;--保留结构
--关联表(两表,多表)
select s.studentName,c.className from student as s inner join class as c where s.classID=c.id;
--1、inner join
select s.studentName,c.className from student as s inner join class as c on s.classID=c.id;
--2、left join 会从左表中返回值,即使右表中没有匹配
select s.studentName,c.className from student as s left join class as c on s.classID=c.id;
--3、right join 会从右表中返回值,即使左表中没有匹配
select s.studentName,c.className from student as s right join class as c on s.classID=c.id;
--多表
/*
select s.studentName,c.className from student as s right join class as c on s.classID=c.id inner join 另个表 on 字段;
*/
--备份'<','>',尖尖朝向谁加导入到谁
--cmd(命令界面运行)
mysqldump -hlocalhost -uroot -p123456 sql_study stuednt>D:/a.sql
--导入
mysqldump -uroot -p123456 sql_study student<D:/a.sql
--创建用户
create user 名字 identified by '密码';
--修改当前用户密码
set password='密码';
--修改指定用户密码
set password for 用户=password('密码');
--权限
grant all privileges on *.* to 用户
--查看
select user from mysql.user;
--查看当前用户权限
show grants for 用户
--撤销权限
revoke all privileges on *.* from 用户
--删除用户
drop user 用户