数据库分类
🍍一. 启动mysql服务器流程
安装: 狂神说MySQL01:初识MySQL (qq.com)
🍉1. 先终端启动
- 以管理员身份打开终端
- 进入mysql文件的bin目录
cd /d D:\Environment\mysql-5.7.19\bin
- 启动服务器
net start mysql
mysql -u root -p
- 输入密码(默认123456)
🍉2. 然后打开SQLyog可视化工具
- 可以在历史记录里查看你刚刚可视化操作的sql语句样式
- 新建数据库
- 新建表
- 查看表,新建记录
添加完需要点刷新,就添加上了
- 连接数据库
首先确保上面启动了数据库了(上面写了)
在这个基础上
🫐几个基本的命令行操作
每句后面必须加分号
update user set password=password('123456')where user='root'; -- 修改密码
flush privileges; -- 刷新数据库
show databases; -- 显示所有数据库
use dbname;-- 打开某个数据库
show tables; --显示数据库mysql中所有的表
describe user; -- 显示表mysql数据库中user表的列信息
create database name; -- 创建数据库
use databasename; -- 选择数据库
`exit;` 退出Mysql
? 命令关键词 : 寻求帮助
-- ,/* */ 表示注释
🫐数据库语言
- DDL 定义语言
- DML 操作
- DQL 查询
- DCL 控制
🍍二. 操作数据库
参考文档: mp.weixin.qq.com/s/a4jA3B2j4…
mysql关键词不区分大小写
- 创建数据库
CREATE DATABASE [IF NOT EXISTS] ... -- 加了一个判断条件,括号里面是可选的,可写可不写,是为了防止重复创建报错的
- 删除数据库
DROP DATABASE [IF EXISTS] ...
- 使用数据库
USE ...
-- 如果数据库的名字是个关键词(特殊字符,比如user),就用反引号``括住
- 查看数据库
SHOW DATABASES -- 查看所有数据库
🍉2.1 数据库的数据类型
和字段属性
详情见上面参考文档
- 数值
- 字符串
- 时间日期
- null
TIP: 需要这几个字段来保证它的健壮性和安全性
🍉2.2 创建数据库表
🫐2.1.1 自己写
-- 所有语句后面加逗号,最后一句可以不加
-- 表名和字段用反引号括
-- comment是注释,后面字符串用单引号括起来
create table if not exists `students` (
`id` int(4) not null auto_increment comment '学号', -- id列,int类型,四个字段,非空,自增,注释
`name` varchar(30) not null default '匿名' comment '姓名',
`pwd` varchar(20) not null default '123456' comment '密码',
`gender` varchar(2) not null default '女' comment '性别',
`birthday` datetime default null comment '出生日期',
`address` varchar(100) default null comment '家庭住址',
`email` varchar(50) default null comment '邮箱',
-- 把id设置为主键,一般一个表只有一个唯一的主键
primary key (`id`)
)engine=innodb default charset=utf8
🫐2.1.2 根据已经有的表,复制
- ———————— 懒人版
SHOW CREATE DATABASE school
-- 查看创建数据库的语句SHOW CREATE TABLE students
-- 查看student数据表的定义语句DESC students
-- 显示表的结构
👇
🫐2.1.3 TIP
关于数据库引擎
- INNOB 默认使用
- MYISAM
(所有的数据库文件都存在data目录下
本质还是文件的存储)
🍉2.3. 修改删除表
-- 与表相关——alter
ALTER TABLE students RENAME teacher -- 修改表的名称
ALTER TABLE teacher ADD hobby VARCHAR(100) -- 增加表的字段
-- 修改表的字段(分为两种:1. 重命名change 2. 修改约束modify)
ALTER TABLE teacher MODIFY hobby INT(11) -- 修改约束
ALTER TABLE teacher CHANGE hobby hobby1 VARCHAR(100) -- 重命名
ALTER TABLE teacher DROP hobby1 -- 删除表的字段
ALTER TABLE teacher DROP hobby1 -- 删除表的字段
TIP:
- 所有的创建和删除操作建议加上判断,以免报错
🍍三. MYSQL数据管理
🍉3.1 数据库级别的外键(约束)
TIP:
- 删除有外键关系的表的时候,必须先删除从表,再删除主表(被引用的表)
🫐方式一: 在创建表的时候,增加约束(麻烦)
CREATE TABLE IF NOT EXISTS `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生表的 gradeid 字段要去引用年级表的 gradeid
-- 1. 定义外键key
-- 2. 给这个外键添加约束(执行引用)
CREATE TABLE IF NOT EXISTS `students` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号', -- id列,int类型,四个字段,非空,自增,注释
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`gender` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
-- 👇这个
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`),
-- 定义了一个key,代表想把gradeid作为外键
KEY `FK_gradeid` (`gradeid`),
-- 约束
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
其实就是这两句👇
(
...
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
...
-- 定义了一个key,代表想把gradeid作为外键
KEY `FK_gradeid` (`gradeid`),
-- 约束
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
...
)
🫐方式二:建表的时候不加约束,额外在外面单独写
在创建表成功后,再添加约束
(
...
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
...
)
ALTER TABLE `students`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);
- 以上的操作都是物理外键,数据库级别的外键,不建议使用(避免数据库过多,删除造成困扰)
- 所以最佳实践是:
- 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
- 而我们想用多张表的数据,想使用外键(之后用程序来实现)
🍉3.2 DML语言(数据操作语言,增删改,全部记住,背下来)
参考文档: mp.weixin.qq.com/s/F3fPma7y8…
- insert
- update
- delete
🫐3.2.1 添加insert
关键:"一一对应"
INSERT INTO 表名([字段名1,字段名2,字段名3])VALUES('值1'),('值2'),('值3'),(...)
-- 可以同时给一个字段,插入多个值,相当于插入多条数据
-- 如果主键自增,那么我们就可以省略主键
-- 一般写插入语句,一定要数据和字段一一对应
这种是省略主键的正确写法👇
这种是错的👇
🫐3.2.2 修改update
逻辑
update 修改谁(哪个表) set字段=新值 修改的哪一条
UPDATE `students` SET `name`='小明' WHERE id=1; -- 改变一条
UPDATE `students` SET `name`='小美' -- 不指定条件,改变所有的数据
UPDATE `students` SET `name`='小石',`email`='123456@qq.com' WHERE id=1;-- 修改多的属性,逗号隔开
where 条件子句
🫐3.2.3 删除delete
delete逻辑
DELETE FROM 表名 WHERE 条件
- 条件可写可不写,但是如果不写,就相当于删除了表内所有数据,不建议
- 因为删除全部数据有别的方法👇
-- 删除最后一个数据
DELETE FROM grade WHERE gradeid = 5
TRUNCATE命令——清空一个数据库表
- 清空一个数据库表,表的结构和索引约束不会变
TRUNCATE [TABLE] 表名;
-- 清空年级表
TRUNCATE `grade`
注意:区别于DELETE命令
- 相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
- 不同 :
- 使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器
- 而delete删除完,不影响原来的自增记录
- 使用TRUNCATE TABLE不会对事务有影响 (事务后面会说)
但是delete也有下面的情况:
- 同样使用DELETE清空不同引擎的数据库表数据.重启数据库服务后
- InnoDB : 自增列从初始值重新开始 (因为是存储在内存中,断电即失)
- MyISAM : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)
🍍四. DQL查询数据SELECT
参考文档: mp.weixin.qq.com/s/0UnSJDRpd…
DQL( Data Query Language 数据查询语言 )
- 所有查询操作都用它 , 如SELECT语句
- 简单的单表查询或多表的复杂查询和嵌套查询
- 是数据库语言中最核心,最重要的语句
- 使用频率最高的语句
完整代码:
测试源码:
CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`loginpwd` VARCHAR(20) DEFAULT NULL,
`studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`sex` TINYINT(1) DEFAULT NULL COMMENT '性别,0或1',
`gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
`phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空',
`address` VARCHAR(255) NOT NULL COMMENT '地址,允许为空',
`borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
`email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
`identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
UNIQUE KEY `identitycard`(`identitycard`),
KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`classhour` INT(4) DEFAULT NULL COMMENT '学时',
`gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;
-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`subjectno` INT(4) NOT NULL COMMENT '课程编号',
`examdate` DATETIME NOT NULL COMMENT '考试日期',
`studentresult` INT (4) NOT NULL COMMENT '考试成绩',
KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');
-- 插入成绩数据 这里仅插入了一组,其余自行添加
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);
-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
-- 插入科目数据
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);
🍉4.1 查询字段
-- 查询所有学生信息
SELECT * FROM student;
-- 查询指定列(学号 , 姓名)
SELECT `studentno`,`studentname` FROM student;
🍉4.2 别名AS——给查出来的列的表头起名字
-- 查询指定列(学号 , 姓名)
-- 可以给字段起别名,也可以给表起别名
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student AS s;
-- 函数 Concat(a,b)
-- CONCAT()函数拼接字符串
SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;
🍉4.3 去重DISTINCT
比如一个同学考了多次,所以会有多条相同学号的数据,但是我们现在想统计有多少人来考试,所以就需要用到去重
基础:👇
-- # 查看哪些同学参加了考试(学号) 去除重复项
SELECT * FROM result; -- 查看考试成绩
SELECT studentno FROM result; -- 查看哪些同学参加了考试
👇
SELECT DISTINCT studentno FROM result; -- 了解:DISTINCT 去除重复项 , (默认是ALL)
拓展:其他一些查询语句👇
SELECT @@auto_increment_increment; -- 查询自增步长
SELECT VERSION(); -- 查询版本号
SELECT 100*3-1 AS 计算结果; -- 表达式
-- 学员考试成绩集体提分一分查看
SELECT `studentno`,`StudentResult`+1 AS '提分后' FROM result;
🍉4.4 where条件子句
逻辑操作符👇
模糊查询:比较查询符👇
通配符:
- %
- _
-- 模糊查询
-- LIKE
-- =============================================
-- 查询姓刘的同学的学号及姓名
-- like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符)
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘%';
-- 查询姓刘的同学,后面只有一个字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘_';
-- 查询姓刘的同学,后面只有两个字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘__';
-- 查询姓名中含有 嘉 字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%嘉%';
-- 查询姓名中含有特殊字符的需要使用转义符号 ''
-- 自定义转义符关键字: ESCAPE ':'
-- =============================================
-- IN
-- =============================================
-- 查询学号为1000,1001,1002的学生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);
-- 查询地址在北京,南京,河南洛阳的学生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛阳');
-- =============================================
-- NULL 空
-- =============================================
-- 查询出生日期没有填写的同学
-- 不能直接写=NULL , 这是代表错误的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;
-- 查询出生日期填写的同学
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;
-- 查询没有写家庭住址的同学(空字符串不等于null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;
🍉4.5 联表查询(连接查询
狂神说MySQL04:使用DQL查询数据 (qq.com))
关键词:JOIN
- JOIN对比
应用场景:
- 就是你需要的数据,不止在一个表中,所以需要用到联表查询
思路:
- 分析需求,分析查询的字段分别来自哪些表
- 确定使用哪种连接查询——有七种
- 确定交叉点(这两个表中哪些数据是相同的)
- 判断条件比如:学生表中studentNov=成绩表中studentNov
🫐1. 左右中
上面是左连接查询👆
-
以左表为主根据条件(from后面为左表,也就是students)去查询右表数据(result),如果根据条件查询右表数据不存在,就使用null填充
-
如果把上面变成右连接查询,就变成了返回右表result中的所有数据,而kuangshen这条信息只在students里有,所以就不会显示了
怎么区分哪个是左表右表
- 不管左右连接,都是这样看的
- 以哪个表查,结果就以哪个表为基准
- join on 连接查询
- where 等值查询
🫐2. 自连接
自己和自己连接,核心:一张表拆成两张一样的表
比如:
子类的pid和父类的categoryid连起来了
对应关系也就是:
代码表示就是:
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`
练习:
🫐3. 分页limit和排序order by
-
排序:升序ASC,降序DESC
- 语法:
order by 用什么来排序 怎么排
- 语法:
-
分页
- 注意:limit是select语句的最后一句
- 语法:
limit 起始数据的位置(下标从0开始),页面的大小
- 公式:
-
🍉4.6 子查询
查询时不想写具体的数,比如WHERE
studentresult>80
,而希望是计算的值
本质: 在where语句中嵌套一个子查询语句select()
由里及外
🍉4.7 嵌套查询
不喜欢这个
🍉4.8 MySQL函数
🫐4. 分组和过滤
group by ,having
-- 查询不同课程的平均分,最高分,最低分
-- 前提:根据不同的课程进行分组
SELECT subjectname,AVG(studentre#sult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result AS r
INNER JOIN `subject` AS s
ON r.subjectno = s.subjectno
GROUP BY r.subjectno
HAVING 平均分>80;
/*
where写在group by前面.
要是放在分组后面的筛选
要使用HAVING..
因为having是从前面筛选的字段再筛选,而where是从数据表中的>字段直接进行的筛选的
*/
🍉4.9 数据库级别的MD5加密
MD5不可逆
-md5()
🍍五. 事务
要么都成功,要么都失败
- 事务就是:将一组SQL放在一个批次中去执行
- 事务有ACID原则——原子性,一致性,隔离性,持久性
- MySQL中默认是自动提交
- 使用事务时应先关闭自动提交
-- 使用set语句来改变自动提交模式
SET autocommit = 0; /*关闭*/
SET autocommit = 1; /*开启*/
流程👇
——————————————————————————————————————
-- 开始一个事务,标记事务的起始点
START TRANSACTION
....
-- 成功就提交,失败就回滚
-- 提交一个事务给数据库
COMMIT
-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK
-- 还原MySQL数据库的自动提交
SET autocommit =1;
——————————————————————————————————————
-- 保存点(存档读档)
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点
模拟转账例子👇:
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `account`(
`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 account(`name`,`money`)
VALUES('A',2000.00),('B',10000.00)
-- 模拟转账——事务
SET autocommit=0;
START TRANSACTION
UPDATE account SET money=money-500 WHERE `name`='A'
UPDATE account SET money=money+500 WHERE `name`='B'
COMMIT;-- 事务一旦提交,就被持久化了
ROLLBACK;
SET autocommit=1;
🍍六. 索引
- MySQL官方对索引的定义为:索引(Index)是帮助MysQL高效获取数据的数据结构。
- 提取句子主干,就可以得到索引的本质:索引是数据结构。
- 狂神说MySQL06:事务和索引 (qq.com)
- CodingLabs - MySQL索引背后的数据结构及算法原理
分类:👇
- 主键索引 (Primary Key) ——只能有一个
- 唯一索引 (Unique) ——可以有多个
- 常规索引 (key/Index)
- 全文索引 (FullText)
作用: 加速用户查询的速度
索引准则:👇
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表建议不要加索引
- 索引一般应加在查找条件的字段
🍍七. 权限管理和备份
🍉7.1 用户管理
可视化操作
SQL命令行操作
-- 创建用户 CREATE USER用户名IDENTIFIED BY'密码'
CREATE USER kuangshen IDENTIFIED BY '123456'
-- 修改密码(修改当前用户密码)
SET PASSWORD=PASSWORD ('123456')
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR kuangshen = PASSWORD ('123456')
-- 重命名 RENAME USER 原来名字 T0 新的名字
RENAME USER kuangshen TO kuangshen2
-- 用户授权ALL PRIVILEGES全部的权限,库.表
-- 除了给别人授权(GRANT)
GRANT ALL PRIVILEGES ON *.* TO kuangshen2
-- 查询权限
SHOW GRANTS FOR kuangshen2 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost
-- 撤销权限 REVOKE 哪些权限 在哪个库撤销 给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2
-- 删除用户
DROP USER kuangshen
🍉7.2 MySQL备份
数据库备份必要性
- 保证重要数据不丢失
- 数据转移
MySQL数据库备份方法
- 直接拷贝数据库文件data和相关配置文件
- 数据库管理工具(右键备份/导出——SQL转储),如SQLyog
- 使用命令行导出 mysqldump备份工具
- 比如:(管理员模式)
- 其他看文档
🍍八. 如何设计一个项目的数据库和数据库的三大范式
当数据库比较复杂时我们需要设计数据库