数据库笔记

狂神数据库学习笔记

关于配置我不会记得太详细，因为这些东西也不需要我们记，也不会经常用，记个大概，需要用的话百度就行。

重点记录sql命令

1、初识Mysql

1.1、Mysql简介

mysql是最流行的关系型数据库管理系统

现在是Oracle公司的

中小型网站，甚至是大型网站都在用

1.2、Mysql安装

  1. 不建议使用.exe安装，建议使用安装包安装
  2. 官网下载好了以后，在安装根目录下创建配置文件my.ini(就是与bin同级的目录)
  3. 添加一些初始化内容
  4. 进入到bin目录下，执行初始化命令（该命令就是执行my.ini的东西），创建Mysql的数据存储目录/data
  5. 开启mysql服务 net mysql start(不确定是不是这条语句)
  6. 第一次进入MySQL命令行 myql -u root -p
  7. 狂神在my.ini设置了跳过验证，直接回车不输密码
  8. 弹出欢迎语句代表成功进入，第一件事就是改密码。（这里我想起来，有进入的情况是界面给一串密码作为初始密码，下一次进入就用这初始密码）
  9. exit离开
  10. 在my.ini注释掉跳过验证那行代码。用新密码再次验证mysql -u root -p
  11. 完毕

这里记录的是狂神安装过程简介，当然也有其他方法。

1.3、图形化界面使用

本质：图形化界面的每一个操作都代表这一条sql语句

创建数据库: 数据库排序规则选择：utf8_general_ci

1.4、命令行连接常用命令

net start mysql  --开启mysql服务 注意：在mysql/bin 目录下执行

mysql -uroot -p   --数据库连接
update mysql.user set authentication_string=password('123456') where user='root' and Host ='localhost';--修改用户密码
flush privileges; --刷新权限
------------------------------------------------
show databases;--显示所有的数据库
mysql> use crm --切换数据库
Database changed
show tables; --显示所有表
describe tbl_clue; --显示表结构
exit --退出连接

--  这是单行注释
/*
这是多行注释
*/

DDL 定义

DML 管理

DQL 查询

DCL 控制

2、操作数据库

操作数据库-->操作数据库中的表-->操作的数据库中表的字段

mysql关键字不区分大小写

2.1、 操作数据库（了解）

1、创建数据库

create database if not exists db1 ;
CREATE DATABASE IF NOT EXISTS db1 CHARACTER SET utf8 COLLATE utf8_general_ci;

2、删除数据库

drop database if exists db1;

3、使用数据库

-- tab键的上面，如果你的表名或字段名是一个特殊字符，就需要加
user `db1`

4、显示所有数据库

show databases;

学习方法：

对比：SQLyog的可视化操作

不会就去点击操作，在历史记录里看相关sql代码

2.2 、数据库的列类型

数字

  • tinyint 1
  • smallint 2
  • mediumint 3
  • int 4字节
  • bigint 8
  • float 4
  • double 8
  • decimal 字符串形式的浮点数 2^16-1 金融计算需要使用

字符串

  • char 固定长度的字符串 0~255 1个字节

  • varchar 可变长字符串 0~65535 16字节

  • tinytext 微型文本 2^8-1

  • text 文本串 2^16-1 保存大文本

时间

  • date YYYY-MM--DD 日期

  • time HH:mm:ss 时间格式

  • datatime YYYY-MM--DD HH:mm:ss 最常用的时间格式

  • timestamp 时间戳 1970.1.1到现在的毫秒数

  • year 年份

null

  • 没有值
  • 不要用null进行运算，结果为null

2.3、数据库的字段属性（重点）

Unsigned

  • 无符号整数
  • 声明了该列不能为负数

zerofill

  • 零填充
  • 不足的位数用0来填充 int(3) 5-->005

默认

  • 不指定值时的默认取值

非空

自增

2.4、创建数据库表

--创建数据库表
CREATE TABLE student (
	`id` INT(4) NOT NULL AUTO_INCREMENT  COMMENT "学号",
	`name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT "姓名",
	`age` INT(3)  DEFAULT NULL COMMENT "年龄",
	`sex` VARCHAR(1) NOT NULL DEFAULT '女' COMMENT "性别",
	`email` VARCHAR(20) DEFAULT NULL COMMENT "邮箱",
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


--俄式
CREATE TABLE student (
	`字段名` 数据类型 属性  索引 注释
    ......
    `字段名` 数据类型 属性  索引 注释
	PRIMARY KEY(`id`)
)表类型 字符集设置 注释

--常用命令、
SHOW CREATE TABLE student;
SHOW CREATE DATABASE testSQL;
DESC student;--DESCRIBE student;

2.5、修改和删除表（针对结构）

-- 删除 修改表的结构 

-- 修改
-- 	表重命名 rename as 
--格式 ALTER TABLE 旧名字  RENAME AS 新名字
ALTER TABLE teacher1 RENAME AS teacher2; 

--	添加字段 add
--格式 ALTER TABLE 表名 add 新字段  类型
ALTER TABLE teacher2 ADD birthday VARCHAR(12);

--	修改字段类型，不能给字段重命名
--格式  ALTER TABLE 表名 MODIFY 字段 新类型;
ALTER TABLE teacher2 MODIFY sex INT(1);

--	修改字段类型，而且可以给字段重命名
--格式  ALTER TABLE 表名 change 旧字段 新字段 新类型;
ALTER TABLE teacher2 CHANGE sex gender VARCHAR(1);

-- 删除表
DROP TABLE IF EXISTS teacher2;

tab键的上面``，如果你的表名或字段名是一个特殊字符，就需要加

3、Mysql数据管理

3.1、外键（了解）

方式1

CREATE TABLE `grade`(
    `gradeid` INT(3) NOT NULL AUTO_INCREMENT COMMENT '年级id',
    `name` VARCHAR(6) NOT NULL COMMENT '年级名字',
    PRIMARY KEY(`gradeid`)
)


CREATE TABLE `student` (
  `id` INT(3) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `age` INT(3) DEFAULT NULL COMMENT '年龄',
  `sex` VARCHAR(1) NOT NULL DEFAULT '女' COMMENT '性别',
  `email` VARCHAR(20) DEFAULT NULL COMMENT '邮箱',
  `gradeid` INT(3) DEFAULT NULL COMMENT '年级id',
  PRIMARY KEY (`id`),
  KEY `FK_gradeid` (`gradeid`),
  CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

方式2

CREATE TABLE `grade`(
    `gradeid` INT(3) NOT NULL AUTO_INCREMENT COMMENT '年级id',
    `name` VARCHAR(6) NOT NULL COMMENT '年级名字',
    PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE TABLE `student` (
  `id` INT(3) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `age` INT(3) DEFAULT NULL COMMENT '年龄',
  `sex` VARCHAR(1) NOT NULL DEFAULT '女' COMMENT '性别',
  `email` VARCHAR(20) DEFAULT NULL COMMENT '邮箱',
  `gradeid` INT(3) DEFAULT NULL COMMENT '年级id',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

ALTER TABLE student ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

以上操作都是物理外键，数据库级别的外键，我们不建议使用（避免数据库过多造成困扰，这里了解即可）

最佳实践

  • 数据库就是单纯的表，只是用来存数据
  • 我们想使用多张表的数据，想使用外键（程序去实现）

3.2、DML语言（全部记住）

  • insert
  • update
  • delete

3.3、insert

--插入一条
insert into 表名(字段1,字段2,字段3,...)values(值1,值2,值3,...)
--插入多条
insert into 表名(字段1,字段2,字段3,...)values(值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...)...

INSERT INTO student(`name`,`age`,`sex`) VALUES("张三",18,"男");
INSERT INTO student(`name`,`age`,`sex`) VALUES("王五",19,"女"),("赵六",20,"男");

3.4、update

--不设置条件会修改整个表
UPDATE student SET `name`="张楚岚";

--设置条件 where子句，返回布尔值
UPDATE student SET `name`="王也" WHERE id<>2;

--修改多个字段
UPDATE student SET `name`="冯宝宝" ,age=50,`sex`='女' WHERE id=2;

3.4.1、运算符

运算符 含义 范围
= 等于
<>或!=或not 不等于
> 大于
< 小于
between...and... 在什么范围内 [2,5]
and 与
or 或

3.5、delete和truncate

--删除部分数据
DELETE FROM student WHERE id=2;

--删除全部数据
DELETE FROM student --方式一
TRUNCATE table student;--方式二

delete和truncate区别

truncate清空表数据后自增会归零而delete保留

（truncate先删除表，再建一张一模一样的表）

delete删除的问题，重启数据库现象

INNODB：重启数据库，自增会归零（存在内存中，断电即失）

MyISAM：自增保持原来数值（存在文件中，不会丢失）

4、DQL查询数据（最重点）

4.1、起别名、concat函数、去重

起别名可以省略as关键字

SELECT NAME name1 ,age age1,sex sex1 FROM student;

concat函数

SELECT CONCAT("名字：",NAME) name1 FROM student;

image-20211117223905513

去重distinct

SELECT DISTINCT NAME name1 ,age age1,sex sex1 FROM student;

查看系统版本、计算表达式、自增步长

SELECT VERSION() -- 查看系统版本
SELECT 100*3-1 ; -- 计算表达式
SELECT @@auto_increment_increment_ -- 自增步长
运算符 含义 范围
= 等于
<>或!=或not 不等于
> 大于
< 小于
between...and... 在什么范围内 [2,5]
and 与
or 或

4.2、where子句使用的运算符

运算符 含义 范围
= 等于
<>或!=或not 不等于
> 大于
< 小于
between...and... 在什么范围内 [2,5]
and 与
or 或
is null 为空
is not null 不为空
in（值1,值2,值3） 在什么中

4.3、模糊查询like

占位符：

  • %：代表多个字符
  • _：代表单个字符

4.4、多表联查

简单理解：多表之间的连接就是通过笛卡尔积的方式（表×表），将满足某些条件的记录拼接成更大的表，我们从这个更大的表中获取我们想要的所有字段

image-20211117223905513

4.5、自连接（了解）

简单说就是 一张表中的数据有两张表的内容

比如大概类型 和具体类型放在一块，可以通过查询判断具体类型是哪种大概类型

CREATE TABLE `category`(
 `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
 `pid` INT(10) NOT NULL COMMENT '父id',
 `categoryname` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`) 
 ) ENGINE=INNODB  AUTO_INCREMENT=9 DEFAULT CHARSET=utf8; 

INSERT INTO `category` (`categoryid`, `pid`, `categoryname`) 
VALUES (2,1,'信息技术'),
(3,1,'软件开发'),
(5,1,'美术设计'),
(4,3,'数据库'),
(8,2,'办公信息'),
(6,3,'web开发'),
(7,5,'ps技术');

SELECT c1.categoryname 分类 ,c2.categoryname 具体类型 FROM category c1,category c2 WHERE c1.`categoryid`=c2.`pid`;

4.6、分页和排序

分页

mysql的方言：limit

语法：limit 索引（或者是跳过的记录数）,每页显示条数

SELECT DISTINCT `name` ,age,sex FROM student ORDER BY age ASC LIMIT 0,2;

排序

语法：order by 字段名 排序方式（desc,asc）

-- 按年龄升序排序
SELECT DISTINCT `name` ,age,sex FROM student ORDER BY age ASC;

4.7、子查询和嵌套查询

简单理解，子查询就是： 子查询查询的结果作为父查询的查询条件

嵌套查询就是像函数一样嵌套，爷爷查询的条件里有爸爸查询，爸爸查询的条件里有儿子查询

由里及外

image-20211118113250888

4.8、分组

group by 字段

having

分组的注意事项：

**1. where后不可以用聚合函数，而having 可用聚合函数 **

2.having处理的是分组后的数据

5、Mysql函数

5.1、常用函数（了解）

image-20211118115151619

时间戳有个CURRENT_TIMESTAMP 可以用于sql语句创建表设置时间默认值

5.2、聚合函数

函数名 使用 描述
count() count(*) ,count(1),count(字段) 计数，count(字段)会忽略null，其他不会
sum() sum(字段)
avg() avg(字段)
max() max(字段)
min() min(字段)

5.3、数据库级别的MD5加密（扩展）

md5()

SELECT MD5(1),MD5('1');

6、事务

6.1、ACID

  • atomicity 原子性

  • consistency 持久性

  • isolation 隔离性

  • durability 持久性

事务之间需要隔离，不能互相影响。比如转账。

事务未隔离引发的问题

  • 脏读

读了未提交的事务数据

  • 不可重复读

多次读的结果不会，数据修改（但不一定是错误，只是场合不会）

  • 虚读（幻读）

读着读着多了一行，前后数据总量不一致

6.2、事务命令

-- mysql是默认开启事务自动提交的
set autocommit =0 -- 关闭
set autocommit =1 -- 开启（默认的）


--查看事务的自动提交等级
SELECT @@autocommit;

-- 开启事务
start transaction;

-- 提交 ，持久化
commit;
-- 回滚
rollback;

7、索引

7.1、索引的分类

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

在一个表中，主键索引只有1个，而唯一索引可以有多个

  • 主键索引（PRIMARY KEY）

    • 唯一的标识，主键唯一

  • 唯一索引（UNIQUE KEY）

    • 列中不可出现重复值，比如身份证。多个列都可以标识为唯一索引

  • 常规索引（KEY/INDEX）

    • 默认的。key,index关键字来设置。

  • 全文索引（FullText）

    • 在特定的数据库引擎下才有，MyISAM。最新的InnoDB可能支持了
    • 快速定位数据
--显示表中所有字段的索引信息
SHOW INDEX FROM student;
--增加索引 方式1  创建表后添加全文索引
alter table 表名 add fulltext index `索引名`(`字段`);
--增加索引 方式2  CREATE INDEX 索引名 on 表(字段)
CREATE INDEX `studnet_age` ON `student`(`age`);
--分析索引执行状况 explain 
explain 查询语句

7.2、测试索引

这里不写代码

说一下测试流程：

  1. 创建表，通过sql编程插入一万条数据

  2. 查询某一条数据某个字段值，比如查询第9999条数据的用户名

  3. 使用explain查看查询情况

  4. 给用户名字段添加索引

    CREATE INDEX `studnet_name` ON `student`(`name`);

  5. 执行查看时间以及使用explain查看查询情况

  6. 比较两者用时，及查询行数

索引在小数量的时候用处不大，但是在大数据量的时候区别十分明显

7.3、索引原则

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表不需要加索引
  • 索引一般加在常用来查询的字段上

索引的数据结构

Hash类型的索引

Btree：InnoDB的默认数据结构~

8、权限管理与备份

8.1、用户管理

用户表：mysql.user

本质：对这种表进行增删改查

-- 创建用户：CREATE USER 用户名
CREATE USER `powerful` IDENTIFIED BY '123456';

-- 修改当前用户密码
SET PASSWORD='123' -- 或 
SET PASSWORD = PASSWORD('onmyown123');

-- 修改指定用户密码
SET PASSWORD FOR  powerful='123456' ;-- 成功
SET PASSWORD FOR  powerful=PASSWORD('123456') ; -- 失败

-- 用户重命名
RENAME USER `power` TO powerful;

-- 用户授权
GRANT ALL PRIVILEGES ON *.* TO powerful

-- 查询权限
SHOW GRANTS FOR powerful

-- root用户权限
all privileges + grant权限

-- 撤销权限 
REVOKE ALL PRIVILEGES ON *.* FROM powerful

-- 删除用户
DROP USER powerful

8.2、数据备份

Mysql数据库备份的方式

  • 直接拷贝物理文件

  • 在Sqlyog可视化工具中手动导出

    • 在想要导出的表或库中。右键，选择备份或导出

      image-20211118193109473

  • 使用命令行导出 mysqldump 命令行使用

# 在mysql的bin目录下：mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 表3... >物理磁盘位置/文件名
D:\mysql\mysql8\bin>mysqldump -hlocalhost -uroot -ponmyown123 testsql student >d:/unimportant/2.sql
# 在mysql的bin目录下：mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 表3... >物理磁盘位置/文件名

# 在mysql的bin目录下：mysqldump -h 主机 -u 用户名 -p 密码 数据库 >物理磁盘位置/文件名

# 导入
# 登录的情况下，切换到指定的数据库
# source 备份文件
source d:/a.sql

#未登录
mysql -u用户名 -p密码 库名< 备份文件

9、规范数据库设计

9.1、如何设计一个数据库

  • 收集信息、分析需求
  • 标识实体（把需求落实到字段）
  • 标识实体之间的关系

9.2、三大范式

第一范式（1NF）

原子性：保证每一列不可再分

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

主键由多个字段（假设两个）构成，其余所有字段必须都和两个字段有关，不能只和其中一个有关

第三范式（3NF）

前提：满足第一范式 和 第二范式

第三范式需要确保数据库表中的每一列数据都和主键直接相关，而不能间接相关

举例：学生表中可以由老师id，不能还由老师姓名，地址，电话这种信息

（规范数据库的设计）

规范性 和 性能的问题

关联查询的表不能超过三张表

  • 考虑到商业化的需求和目标，（成本和用户体验！），数据库的性能更加重要
  • 在规范性能的问题的时候，需要适当的考虑一些规范性！
  • 故意给某些表增加一些冗余的字段（从多表查询到单表查询）
  • 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）
posted @ 2021-11-20 10:44  让时间变成力量  阅读(82)  评论(0)    收藏  举报