初始数据库
什么是数据库
数据库(DB database)
概念:数据仓库,软件,安装在操作系统上的,SQL,可以存储大量的数据,500万
作用:存储数据,管理数据
数据库分类
关系数据库(SQL)
- mysql,oracle,sqlserver
- 通过表和表之间,行和列之间的关系进行数据的存储
非关系数据库(NOSQL)
- Redis,mongDB
- 非关系数据库,对象存储,通过对象的自身的属性来决定
DBMS(数据库管理系统)
- 数据库管理软件,科学有效的管理我们的数据。维护和获取数据
连接数据库
命令行链接
mysql -u用户名 -p密码 --连接数据库
flush privileges;--刷新权限
----------------------------------------
--所有的语句都是用分号结束;
show databases;--查看所有数据库
use school;--切换数据库 use 数据库名
show tables;--展示库中所有表
describe student;--显示数据库中所有的表的信息,可用缩写desc 表名
create database study;--创建数据库
exit;--退出
--单行注释
/*
多行注释
*/
操作数据库
操作数据库>操作数据库中的表>操作数据库中表的数据
mysql不区分大小写
操作数据库
1.创建数据库
CREATE DATABASE IF NOT EXISTS study;
2.删除数据库
DROP DATABASE IF EXISTS study;
3.使用数据库
--如果你的表名或字段名是一个特殊符号,既需要带``(英文状态下的小波浪)
USE `study`;
4.查看数据库
SHOW DATABASES;
列的类型
数值
- tinyint 十分小的数据 1个字节
- smallint 比较小的数据 2个字节
- mediumint 中等大小的数据 3个字节
- int 标准的整数 4个字节
- bigint 比较大的数据 8个字节
- float 浮点数 4个字节
- double 浮点数 8个字节
- decimal 字符串形式的浮点数 金融计算
字符串
- char 字符串固定大小的 0~255
- varchar 可变字符串 0~65535 常用的变量 String
- tinytext 微型文本 2^8-1
- text 文本串 2^6-1 保存大文本
时间日期
- date YYYY-MM-DD 日期格式
- time HH:mm:ss 时间格式
- datetime YYYY-MM-DD HH:mm:ss 常用的时间格式
- timestamp 时间戳,1970 1,1到现在的毫秒数!
- year 年份
null
- 没有值,未知
注意:不要使用null进行运算,结果为null
字段属性
unsigned:
- 无符号的整数
- 声明了该列不能声明为负数
zerofill:
- 0填充的
- 不足的位数,使用0来填充,int(3) 6—006
自增:
- 通常理解为自增,自动在上一条记录的基础上+1(默认)
- 通常用来设计唯一的主键~index ,必须是整数类型
- 可以自定义设计主键自增的起始值和步长
非空: NULL not null
- 假设设置为not null,如果不给它赋值,就会报错
- null,如果不填写值,就默认为null
默认:
- 设置默认的值
- sex 默认为男,如果不指定该列的值,则会有默认的值
扩展:
/*
每一个表,都必须存在以下五个字段!表示一个记录存在的意义
id 主键
`version` 乐观锁
is_deletee 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
创建数据库表
--目标创建一个school数据库
--创建学生表(列,字段)使用SQL创建
--学号int 登陆密码varchar,姓名,性别varchar,出生日期datatime,家庭住址,email
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT --字段自动增加COMMENT'学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '1234' COMMENT'密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT'性别',
`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT'家庭地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT'邮箱',
PRIMARY KEY(`id`)--设置主键
)ENGINE=INNODB DEFAULT CHARSET=utf8;
格式
create table[if not exists] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
......
`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]
常用命令
SHOW CREATE DATABASE school --查看创建数据库的语句
SHOW CREATE TABLE student --查看student数据表的定义语句
DESC student --查看表的结构
数据表的类型
--关于数据库引擎
/*
INNODB 默认使用
MYISAM 早年使用
*/
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 比较小 | 比较大约为2倍 |
常规使用操作
- MYISAM 节约空间,速度较快
- INNODB 安全性高,事务的处理,多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下,一个文件夹就对应一个数据库本质还是文件的存储
MYSQL引擎在物理文件上的区别
- innoDB在数据库中只有一个*.frm文件,以及上级目录下的ibdata1文件
- MYISAM对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件(data)
- *. 索引文件(index)
设置数据库表的字符集编码
CHARSET=utf8
不设置的话,会是mysql默认的字符集编码不支持中文
MySQL的默认编码是latin1,不支持中文
在my.ini中配置默认的编码
CHARSET-set-server=utf8
修改删除表
修改
USE `school`
-- 修改表名:alter table 就表名 rename as 新表名
ALTER TABLE student RENAME AS stu
-- 增加表的字段:alter table 表名 add
ALTER TABLE stu ADD age INT(11)
-- 修改表的字段(重命名,修改约束)
-- alter table 表名 modify 字段名 列属性
ALTER TABLE stu MODIFY age VARCHAR(6)-- 修改约束
-- alter table 表名 change 旧名字 新名字 列属性
ALTER TABLE stu CHANGE age age1 INT(11) -- 字段重名
-- 删除表的字段:alter table 表名 drop 字段名
ALTER TABLE stu DROP sex
删除
-- 删除表(如果存在在删除)
DROP TABLE IF EXISTS stu
所有的创建和删除操作尽量加上判断,以免报错
MYSQL数据管理
外键
方式一,在创建表的时候,增加约束
-- 学生表的gradeid字段,要去引用年纪表的gradeid
-- 定义 外键key
--给外键添加约束 references引用
CREATE TABLE IF NOT EXISTS `student`( `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT'学号', `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名', `pwd` VARCHAR(20) NOT NULL DEFAULT '1234' COMMENT'密码', `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT'性别', `birthday` DATETIME DEFAULT NULL COMMENT'出生日期',gradeid INT(10) NOT NULL, `address` VARCHAR(100) DEFAULT NULL COMMENT'家庭地址', `email` VARCHAR(50) DEFAULT NULL COMMENT'邮箱', PRIMARY KEY(`id`),KEY fk_gradeid(`gradeid`),CONSTRAINT fy_gradeid FOREIGN KEY(`gradeid`) REFERENCES grade(`gradeid`) )ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
PRIMARY KEY(`gradeid`)
)
删除有外键关系的表的时候,必须先删除引用别人的表,再删除被引用的表
方式二:创建表成功后,添加外键约束
--创建表时没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `fk_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)
-- alter table 表名 add constraint 约束名 foreign key(作为外键的例)references 那个表(那个字段)
DML语言
- 数据库的意义:存储数据,数据管理
- DML语言:数据操作语言
- insert
- delete
- update
添加
insert
-- 插入语句
-- insert into 表名 (`字段名`,`字段名`,`字段名`)values(`值1`,`值1`,`值1`),(`值1`,`值1`,`值1`),(`值1`,`值1`,`值1`)
INSERT INTO `grade`(`name`) VALUES('藏十三'),('十三')
-- 由于主键自增我们可以省略(如果不写表的字段,他会一一匹配)
INSERT INTO `grade` VALUES('藏十三'),('十三')
-- 插入语句,我们一定要数据和字段一一对应
语法:insert into 表名 (字段名
,字段名
,字段名
)values(值1
,值1
,值1
),(值1
,值1
,值1
),(值1
,值1
,值1
)
注意事项:
- 字段和字段之间使用逗号隔开
- 字段可以省略的,但是后面的值必须要一一对应,不能少
- 可以同时插入多条数据,values后面的值,需要使用,逗号隔开
- 用英文状态下的单引号括住
修改
update
-- 带条件,进行修改
UPDATE `grade` SET `name`='学柯君' WHERE `gradeid`=1
-- 模糊修改
UPDATE `grade` SET `name`='学柯君'
-- 修改多个属性,逗号隔开
UPDATE `grade` SET `name`='学',`age`=1
-- 语法
UPDATE 表名 SET 字段名=值,字段名=值 WHERE [条件]
条件:where子句运算符id等于某个值,大于某个值,在某个区间内修改
操作符返回值
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 5=6 | false |
<>或者!= | 不等于 | 5<>6 | true |
> | |||
< | |||
<= | |||
>= | |||
BETWEEN…AND… | 在某个范围 | [2.5] | |
and | 我和你 | ||
or | 我或你 |
语法:UPDATE 表名 SET 字段名=值,字段名=值 WHERE [条件]
注意:
-
字段名尽量带上``
-
条件,筛选的条件,如果没有指定。则会修改所有的列
-
value,是一个具体的值,也可以是一个变量
-
多个设置的属性之间,使用英语逗号隔开
UPDATE `grade` SET `date`=CURRENT_TIME
删除
delete 命令
语法:delete from 表名 [where 条件]
-- 删除数据(避免这样写,会全部删除)
DELETE FROM `grade`
-- 删除指定数据
DELETE FROM `grade` WHERE gradeid=1;
truncate命令
作用:完全清空一个数据库表,表结构和索引约束不会变,自动增列归零
-- 清空表
TRUNCATE `grade`
delete的 trunncate区别
相同点:都能删除数据,不会删除表结构
- 不同点:
- trunncate 重新设置自动增列归零
- delete在删除表数据完,重启数据库自动增值才会归零
- innoDB: 自增列会重1开始(存在内存中的,断电即失)
- myisam:继续从上一个自增量开始(存在文件中的,不会丢失)
- trunncate不会影响事务
DQL查询数据
DQL
(Data Query LANGUAGE:数据查询语言)
- 所有的查询操作都用它:select
- 简单查询,复杂查询
- 数据库中最核心的语言
SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]
查询指定字段
-- 查询全部的学生, select 字段 from表
SELECT * FROM `student`
-- 查询指定字段
SELECT `studentno`,`studentname` FROM`student`
-- 别名,给结果起一个名字,as 可以给字段起别名,也可以给表起别名
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student AS s
-- 函数 concat(a,b)
SELECT CONCAT('姓名:',studentname) AS 新名字 FROM `student`
语法:selete 字段… from 表
有时候,列名不是那么的见名之一,我们起别名,AS 字段名as别名
去重 distinct
作用:去除select查询出来的结果中重复的数据
-- 查询成绩
SELECT * FROM result-- 全部成绩
SELECT `studentno` FROM result-- 查询那些学生有成绩
SELECT DISTINCT `studentno` FROM `result`-- 发现重复数据,去重
数据库的列 (表达式)
SELECT VERSION() -- 查询系统版本号(函数)
SELECT 100*2-1 AS 结果 -- 用来计算(表达式)
SELECT @@auto_increment_increment -- 查询自增的步长(变量)
SELECT `studentno`,`studentresult`+1 AS '提分后' FROM result
数据库中的表达式:文本值,列,null等等
select 表达式 from 表
where条件子句
作用:检索数据中符合条件的值
搜索的条件有一个或者多个表达式组成结果为布尔值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and&& | 逻辑与,两个都为真,结果为真 | |
or|| | 逻辑或,其中一个为真,结果为真 | |
not != | 逻辑非,真为假,假为真 |
SELECT `studentno`,`studentresult` FROM result
-- 查询成绩在95到100之间键的
SELECT * FROM result WHERE `studentresult`>=95 AND `studentresult`<=100
-- and &&
SELECT * FROM result WHERE `studentresult`>=95 && `studentresult`<=100
-- 某糊查询
SELECT * FROM result WHERE `studentresult` BETWEEN 5 AND 100
-- 查询除了1000好的学生 !=
SELECT * FROM `student` WHERE `studentno`!=1000
-- 查询除了1000好的学生 not
SELECT * FROM `student` WHERE NOT `studentno` != 1000
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
is null | a is null | 如果操作符为null,结果为真 |
is not null | a is not null | 如果操作符不为null,结果为真 |
between | a between b and c | 若a在b和c之间则为真 |
like | a like b | sql查询,如果a匹配b,则结果为真 |
in | a in(a,s,d,) | 如果a在a,s,d之中则为真 |
-- 查询姓刘的同学
-- like结合 %(任一个字符) _(一个字符)
SELECT * FROM `student` WHERE `studentname` LIKE '赵%'
-- 查询姓赵两个字的
SELECT * FROM `student` WHERE `studentname` LIKE '赵__'
-- 查询姓赵一个字的
SELECT * FROM `student` WHERE `studentname` LIKE '赵_'
-- 查询名字中间有强字的
SELECT * FROM `student` WHERE `studentname` LIKE '%强%'
-- in(一个值或者多个值)
SELECT * FROM `student` WHERE `studentno` IN(1001,1000)
-- 查询在北京的
SELECT * FROM `student` WHERE `address` IN('北京朝阳')
-- 查询地址为空的学生
SELECT * FROM `student` WHERE `address` IS NULL OR `address`=''
-- 查询生日不为空的
SELECT * FROM `student` WHERE `borndate` IS NOT NULL
-- 查询生日为空的
SELECT * FROM `student` WHERE `borndate` IS NULL
联表查询
查询方式
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Jr7bg9Jr-1627029003225)(C:\Users\surface\Desktop\学习\截图\ss.jpg)]
-- 查询参加考试的同学的(学号,姓名,科目编号,分数)
/*思路
1.分析需求:分析查询字段来自那些表(连接查询)
2.使用哪种连接查询
确认交叉点(两个表有哪些相同字段)
判断条件:学生表中的studentno和成绩表中的studentno
*/
-- inner join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student`AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
-- right join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student`AS s
RIGHT JOIN `result` AS r
ON s.`studentno`=r.`studentno`
-- left join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student`AS s
LEFT JOIN `result` AS r
ON s.`studentno`=r.`studentno`
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就会返回行 |
left join | 会从左表中返回所有的值,即使右表没有匹配 |
right join | 会从右表中返回所有的值 |
语法:select 字段名 from 表名 [inner,left,right] join [on,where]
where:等值查询
join (连接表)on(判断条件)连接查询
-- 三表查询
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON r.studentno=s.studentno
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
-- 我要查询哪些数据
-- 从哪几个表中查 from 表 xx join 连接的表 on 交叉条件
-- 假设存在一种多表查询,慢慢来,先查两张表然后在慢慢增加
自连接
自己的表和自己的表连接,核心:一张表拆为两张一样的表即可
父类:
categoryid | categoryname |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类
pid | categoryid | categoryname |
---|---|---|
4 | 数据库 | |
2 | 8 | 办公信息 |
3 | 6 | web技术 |
5 | 7 | ps技术 |
操作:查询父类对应的子类关系
父类 | 子类 |
---|---|
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | ps技术 |
-- 查询父子信息:把一张表看成两个一模一样的表
SELECT a.`categoryname` AS '父类目',b.`categoryname` AS '子类目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`
分页和排序
排序
-- 排序,升序asc 降序desc
-- order by通过那个字段排序
-- 查询结果根据成绩排序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON r.studentno=s.studentno
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
ORDER BY subjectname DESC
分页
--为什么要分页?
-- 缓解数据库压力,给人更好的体验
-- 分页,每页只显示五条数据
--语法:limit 起始值,页面大小
--网页应用:当前,总页数,页面大小
--limit (n-1)*5(初始数),5(页面大小)
--总页数=总数据数/页面大小
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON r.studentno=s.studentno
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
ORDER BY subjectname DESC
LIMIT 4,5
子查询
where(这个值是计算出来的)
本质:在where语句中嵌套一个子查询语句
where(select * from)
/*使用子查询(由里及外)
查询所有高等数学-3的学生学号
*/
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `subjectno`=(
SELECT `subjectno` FROM `subject`
WHERE `subjectname`='高等数学-3'
)
ORDER BY studentresult DESC
分组和过滤
-- 查询不同的课程的平均值,最高分,最低分,平均分大于80
-- 核心:课程分组
SELECT `subjectname`,AVG(`studentresult`)AS 平均值,MAX(`studentresult`),MIN(`studentresult`)AS 最低分
FROM result r
INNER JOIN `subject` s
ON r.`subjectno`=s.`subjectno`
GROUP BY r.`subjectno`
HAVING 平均值>80
MySQL函数
常用函数
参考网站:MySQL函数大全,MySQL常用函数汇总 (biancheng.net)
MySQL 数值型函数
函数名称 作 用
ABS 求绝对值
SQRT 求二次方根
MOD 求余数
CEIL 和 CEILING 两个函数功能相同,都是返回不小于参数的最小整数,即向上取整
FLOOR 向下取整,返回值转化为一个BIGINT
RAND 生成一个0~1之间的随机数,传入整数参数是,用来产生重复序列
ROUND 对所传参数进行四舍五入
SIGN 返回参数的符号
POW 和 POWER 两个函数的功能相同,都是所传参数的次方的结果值
SIN 求正弦值
ASIN 求反正弦值,与函数 SIN 互为反函数
COS 求余弦值
ACOS 求反余弦值,与函数 COS 互为反函数
TAN 求正切值
ATAN 求反正切值,与函数 TAN 互为反函数
COT 求余切值
MySQL 字符串函数
函数名称 作 用
LENGTH 计算字符串长度函数,返回字符串的字节长度
CONCAT 合并字符串函数,返回结果为连接参数产生的字符串,参数可以使一个或多个
INSERT 替换字符串函数
LOWER 将字符串中的字母转换为小写
UPPER 将字符串中的字母转换为大写
LEFT 从左侧字截取符串,返回字符串左边的若干个字符
RIGHT 从右侧字截取符串,返回字符串右边的若干个字符
TRIM 删除字符串左右两侧的空格
REPLACE 字符串替换函数,返回替换后的新字符串
SUBSTRING 截取字符串,返回从指定位置开始的指定长度的字符换
REVERSE 字符串反转(逆序)函数,返回与原始字符串顺序相反的字符串
MySQL 日期和时间函数
函数名称 作 用
CURDATE 和 CURRENT_DATE 两个函数作用相同,返回当前系统的日期值
CURTIME 和 CURRENT_TIME 两个函数作用相同,返回当前系统的时间值
NOW 和 SYSDATE 两个函数作用相同,返回当前系统的日期和时间值
UNIX_TIMESTAMP 获取UNIX时间戳函数,返回一个以 UNIX 时间戳为基础的无符号整数
FROM_UNIXTIME 将 UNIX 时间戳转换为时间格式,与UNIX_TIMESTAMP互为反函数
MONTH 获取指定日期中的月份
MONTHNAME 获取指定日期中的月份英文名称
DAYNAME 获取指定曰期对应的星期几的英文名称
DAYOFWEEK 获取指定日期对应的一周的索引位置值
WEEK 获取指定日期是一年中的第几周,返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR 获取指定曰期是一年中的第几天,返回值范围是1~366
DAYOFMONTH 获取指定日期是一个月中是第几天,返回值范围是1~31
YEAR 获取年份,返回值范围是 1970〜2069
TIME_TO_SEC 将时间参数转换为秒数
SEC_TO_TIME 将秒数转换为时间,与TIME_TO_SEC 互为反函数
DATE_ADD 和 ADDDATE 两个函数功能相同,都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE 两个函数功能相同,都是向日期减去指定的时间间隔
ADDTIME 时间加法运算,在原始时间上添加指定的时间
SUBTIME 时间减法运算,在原始时间上减去指定的时间
DATEDIFF 获取两个日期之间间隔,返回参数 1 减去参数 2 的值
DATE_FORMAT 格式化指定的日期,根据参数返回指定格式的值
WEEKDAY 获取指定日期在一周内的对应的工作日索引
聚合函数
MySQL 聚合函数
函数名称 作用
MAX 查询指定列的最大值
MIN 查询指定列的最小值
COUNT 统计查询结果的行数
SUM 求和,返回指定列的总和
AVG 求平均值,返回指定列数据的平均值
-- 都能统计,表中数据(查询一个表中有多少记录)
SELECT COUNT(`studentno`) FROM `result`-- count(字段) 会忽略所有的null值
SELECT COUNT(*) FROM `result`-- count(*),不会忽略null值,本质计算行数
SELECT COUNT(1) FROM `result`-- count(*),不会忽略所有的null值,本质计算行数
SELECT SUM(`studentresult`) AS 总和 FROM `result`
SELECT AVG(`studentresult`) AS 平均值 FROM `result`
SELECT MAX(`studentresult`) AS 最大值 FROM `result`
SELECT MIN(`studentresult`) AS 最小值 FROM `result`
MySQL 流程控制函数
函数名称 作用
IF 判断,流程控制
IFNULL 判断是否为空
CASE 搜索语句
MD5加密
什么是MD5
主要增强算法复杂度和不可逆性。
MD5不可逆,具体的值的MD5一样的
MD5破解网站的原理,背后有一个字典,MD5加密后的值,加密前的值
-- 测试MD5 加密
CREATE TABLE test(
`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 test VALUES(1,'sdad','dfdsfsd'),(2,'sdad','dfdsfsd'),(3,'sdad','dfdsfsd')
-- 加密
UPDATE test SET pwd=MD5(pwd) WHERE id=1
UPDATE test SET pwd=MD5(pwd)-- 全部加密
事务
什么是事务
要么都成功,要么都失败
1.sql执行 a给b转账 a1000 ->200 b200
2.sql执行 b收到a的钱 a800 -》b400
将一组sql放在一批次中执行
事务原则:ACID原则 :
**原子性:**要么都成功要么都失败
**一致性:**事务前后的数据完整性要保证一致
持久性(事务提交):事务一旦提交则不可逆,被持久化到数据库中
**隔离性:**事务隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的任务干涉之间相互隔离
隔离所导致的一些问题
**脏读:**指一个事务读取了另外一个事务未提交的数据。
**不可重复读:**在一个事务内读取表中的某一行数据,多次读取结果不同。
**虚读:**是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致
命令
-- mysql是默认开启事务自动提交的
SET autocommit=0 -- 关闭
SET autocommit=1 -- 开启默认
-- 手动提交事务
SET autocommit=0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开启,从这个之后的sql都在同一个事物内
-- sql语句
-- 提交:持久化(成功)
COMMIT
-- 回滚:回到原来的样子(失败)
ROLLBACK
-- 事务结束
SET autocommit=1 -- 开启自动提交
模拟场景
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
CREATE TABLE `acount`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `acount`(`name`,`money`) VALUES('a',1000.00),('b',200.00)
SET autocommit=0
START TRANSACTION -- 开启事务
UPDATE acount SET money=money-500 WHERE `name`='a'
UPDATE acount SET money=money+500 WHERE `name`='b'
COMMIT
ROLLBACK
SET autocommit=1
索引
什么是索引
它是存储引擎用于快速找到记录的一种数据结构。
索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对性能的影响就愈发重要。
索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
- 主键索引(primary key)
- 唯一的标识,主键不可重复,只能有一个列作为主键
- 唯一索引(UNIQUE KEY)
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识位唯一索引
- 常规索引(KEY/INDEX)
- 默认的,index。key关键字来设置
- 全文索引(FullText)
- 在特定的数据库引擎下才有,MyiSAM
- 快速定位数据
基本语法
/* 索引的使用
1.在创建表的时候给字段增加索引
2.创建完毕后,增加索引
*/
-- 显示所有的索引信息
SHOW INDEX FROM `student`
-- 增加一个全文索引(索引名) 列名
ALTER TABLE school.`student` ADD FULLTEXT INDEX `studentname`(`studentname`);
-- explain 分析sql执行的状况
EXPLAIN SELECT * FROM `student`-- 非全文索引
EXPLAIN SELECT * FROM `student` WHERE MATCH(`studentname`) AGAINST('周')
--create index 索引名 on表名 (字段名)
索引在小数据量的时候,用处不大,但在大数据的时候区别十分大
索引原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
hash类型的索引
btree:innoDB的默认数据结构
存储过程
介绍
存储过程:用于数据库中存储复杂程序,方便外部调用的一种数据库对象。
是特定功能的SQL语句集,经编译创建并保存在数据库中,用户通过存储过程名称来调用,需要参数在名称后的括号里加参数,(相当于)
权限管理和备份
用户管理
SQL操作命令
用户表:MySQL:user
本质:读这张表进行增删改查
-- 创建用户CREATE USER 用户名 IDENTIFIED BY 密码
CREATE USER lj IDENTIFIED BY '123'
-- 修改密码(修改当前用户密码)
SET PASSWORD =PASSWORD('1234')
-- 修改指定用户的密码
SET PASSWORD FOR lj=PASSWORD('456')
-- 重命名RENAME USER 旧名字 TO 新名字
RENAME USER lj TO ljj
-- 用户授权 GRANT ALL PRIVILEGES ON 权限 TO 库名
-- ALL PRIVILEGES 除了给别人授权,其他什么都能干
GRANT ALL PRIVILEGES ON *.* TO ljj
-- 查询权限
SHOW GRANTS FOR ljj -- 普通用户权限 GRANT ALL PRIVILEGES ON *.* TO 'ljj'@'%'
SHOW GRANTS FOR root@localhost -- root用户权限 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
-- 撤消权限 REVOKE 哪些权限,在哪个库撤销,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM ljj
-- 删除用户
DROP USER ljj
MySQL备份
为什么要备份:
- 保证重要的数据不会丢失
- 数据转移
mysql备份方式
- 直接拷贝物理文件
- 在sqlyog这种可视化工具中手动导出
- 在想要导出的表或者库中,右键选择备份或导出[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0IMe9pJY-1627029003236)(C:\Users\surface\Desktop\学习\截图\存储.JPG)]
- 使用命令行导出 mysqldump 命令使用
# mysqldump -h主机 -u用户名 -p密码 数据库 [表名1 表名1 表名1] >物理位置/文件名
C:\Users\surface>mysqldump -hlocalhost -uroot -p1234 school >C:\Users\surface\Downloads\aaa.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
#导入
#登陆情况下,切换到指定数据库
#source 备份文件
source C:\Users\surface\Downloads\aaa.sql
-- 二种方式
Mysql -u用户 -p密码 库名<备份文件
规范数据库设计
当数据库比较复杂时,我们需要设计了
糟糕的数据库设计:
- 数据冗余,浪费空间
- 数据库插入和删除都会麻烦,异常(屏蔽使用物理外键)
- 程序的性能差
良好的数据库设计:
- 节省内存空间
- 保证数据库的完整性
- 方便开发数据库
软件开发中,关于数据库的设计:
- 分析需求:分析业务和需求处理的数据库的需求
- 概要设计:设计关系图E-R图
为什么需要数据规范
- 信息异常
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
三大范式
第一范式:
原子性:保证每一列不可再分
第二范式:
前提:满足第一范式
每一张表只描述一件事情
第三范式:
前提:满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据和主键直接相关,而不能间接相关
(规范数据库设计)
规范性和性能问题:
关联查询的表不得超过三张表
- 考虑商业化的需求和目标,(成本,用户体验)数据库的性能更加重
- 在规范性能的问题的时候,需要适当考虑一下规范性
- 故意给某些表增加一些冗余的字段。(从多表查询变为单表查询)
- 故意增加一些计算列(从大数据量降为小数据量的查询:索引)