SQL语句的学习

操作数据库

操作数据库>操作数据库中的表>操作数据库中的表的数据

MySQL的关键字不区分大小写

操作数据库（了解）

注意：[]里面的空格是可选的

1、创建数据库

CREATE DATABASE [IF NOT EXISTS ]westos;

2、删除数据库

DROP DATABASE [IF EXISTS] westos;

3、使用数据库

USE `school`;
--  tab键的上面那个键，如果你的表名或者字段名是一个特殊字符，就需要带 ` `

4、 查看数据库

SHOW DATABASES  --查看所有的数据库。

对比：SQLyog的可视化操作

学习思路：

  • 不会的可以对照SQLyog的可视化历史记录查看sql。

  • 固定的语法或关键字必须要强行记住！

这里我是用的Navicat 也有历史记录：

点击左上角的【工具】，选择历史日志选项，或者直接[ctrl+H]。

数据库的数据类型（数据库的列类型）

数值

常用int

金融用：decimal

类型 字节
tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中等大小的数据 3个字节
int 标准的整数 4个字节
bigint 较大的数据 8个字节
float 浮点数 4个字节
double 浮点数 8个字节
decimal 字符串形式的浮点数 金融计算的时候，一般使用decimal

字符串

**常用varchar **：对应java的String类型

  • char 字符串固定大小 0~255
  • varchar 可变字符串 0~65535
  • tinytext 微型文本 2^8-1
  • text 文本串 2^16-1 保存大本文

时间日期

常用：datetime与timestamp

  • date YYY-MM-DD 日期格式
  • time HH:mm:ss 时间格式
  • datetime YYY-MM-DD HH:mm:ss
  • timestamp 时间戳 1970.1.1 到现在的毫秒数
  • year 年份表示

null

  • 没有值，未知
  • 注意，不要使用NULL进行运算，因为结果为NULL

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

Unsigned:

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

Zerofill：

  • 0填充
  • 不足的位数，使用0来填充， 如：int（3） 我们就输入了5 那么就会变成 005

自增：auto_increment

  • 通常理解为自增，自动在上一条记录的基础上+1（默认）
  • 通常用来设计唯一主键~ index，必须是整数类型。
  • 可以自定义设计主键自增的起始值与步长。

非空：NULL not null

  • 假设设置为 not null ，如果不给它赋值，就会报错！
  • null ， 如果不填写值，默认就是null

默认：

  • 设置默认的值！
  • sex，默认值为 男，如果不指定该列的值，则会默认是男。

扩展：就好**

/*每个表，都必须存在以下五个字段！未来做项目用的，表示一个记录存在的意思！
id 主键
`version` 乐观锁
is_delete  伪删除
gmt_create   创建时间
gmt_updata 修改时间
*/

创建数据库表(重点)

-- 目标：创建一个school数据库
-- 创建学生表（列，字段） 使用sql创建
-- 学号 int 登录密码：carchar(20) 姓名，性别 varchar（2），出生日期（datatime）,家庭住址，email

-- 注意点：使用英文()，表的名称和字段尽量使用` ` 括起来
-- AUTO_INCREMENT 自增
-- 字符串使用 单引号括起来！
-- 所有的语句后面加,(逗号） 英文的， 最后一个字段不用加
-- PRIMARY KEY 主键,一般一个表只有唯一的一个主键
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 '123456' 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=utf8mb4;

注意以上代码 我在SQLyog中可以运行，但是在Navicat中却不行。

格式：

CREATE TABLE [IF NOT EXISTS] `表名`(
	`字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    .....
    `字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]

常用命令：

SHOW CREATE DATABASE school  -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示表的结构

数据表的类型

-- 关于数据库引擎
/*
INNODB 默认使用~
MYISAM   早些年使用
*/
MYISAM INNODB
事物支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大，约为MYISAM的2倍

常规的使用操作：

  • MYISAM 节约空间，速度较快
  • INNODB 安全性高，事物的处理， 多表多用户操作

在物理空间存在的位置：

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库。

本质还是文件的存储！

MySQL引擎在物理文件上的区别：

  • INNODB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
  • MYISAM对应的文件
    • *.frm -表结构的定义文件
    • *.MYS 数据文件（data）
    • *.MYI 索引文件（index）

设置数据库表的字符集编码

CHARSET=utf8mb4;

不设置的话，会是mysql默认的字符集编码~（不支持中文！）

MySQL的默认编码是Latin1，不支持中文。

在my.ini中配置默认的编码

character-set-server=utf8mb4

但是不建议使用，因为以后要和别人一起做项目有可以能别人没有配置，那么会报错。 推荐在创建表的时候设置字符集。

修改表和删除表

修改

-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE `teacer1` RENAME AS `teacher1`;
-- 添加表的字段 ALTER TABLE 表名 ADD 字段名 列属性；
ALTER TABLE teacher1 ADD age INT(10);

-- 修改表的字段（重命名，修改约束！）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER	TABLE teacher1 MODIFY `age` VARCHAR(11);       -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER	TABLE teacher1 CHANGE `age` `age1` INT(3);  -- 字段重命名

-- 删除表的字段： ALTER TABLE 表名 DROP 字段名
ALTER TABLE	teacher1 DROP age1;

删除

-- 删除表(如果表存在再删除)
DROP TABLE [IF EXISTS] teacher1;

所有的创建和删除操作尽量加上判断，以免报错

注意点：

  • ``所有的字段名，使用这个包裹！
  • 注释 -- /* */
  • SQL关键字大小写不敏感，推荐写小写
  • 所有的符号全部用英文。

MySQL的数据管理

外键（了解即可）

  • 在创建表的时候增加约束（麻烦，复杂）

删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）

 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 '123456' 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 '邮箱',
`gradeid` INT(10) NOT NULL COMMENT '年级', 
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

--创建表的时候没有外键关系，之后再添加
-- ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 哪个表(哪个字段);

以上的操作都是物理外键，数据库级别的外键，我们不建议使用!（避免数据库过多造成困扰)

最佳实践

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

DML语言（数据库管理语言）（全部记住）

数据库的意义：数据存储，数据管理

DLM语言：数据操作语言

  • Insert
  • update
  • delete

添加

insert

 -- 插入语句（添加）
 -- Insert into 表名([字段名1，字段名2...]) values('值1'),('值2'),('值3'),....('grade')
INSERT INTO `grade` (`gradename`) VALUES('大四')

-- 由于主键自增我们可以省略（如果不写表的字段，它就会一一匹配 如下：）
-- INSERT INTO `grade`(`gradename``gradeid`) VALUES('大四',null)

-- 一般写插入语句，我们一定要数据和字段一一对应！

-- 插入多个字段
INSERT INTO `grade` (`gradename`) VALUES ('大二'),('大一');

-- 插入一条信息，三个字段（名字，密码，性别）
INSERT INTO `student` (`name`,`pwd`,`sex`) VALUES ('张三','123456789','女');

-- 插入两条信息，三个字段（名字，密码，性别）
INSERT INTO `student` (`name`,`pwd`,`sex`) 
VALUES ('张三','123456789','女'),('李四','987654','男');

语法：

insert into 表名(字段名,字段名) values('值'),('值')`
注意事项：

  • 字段和字段之间使用英文逗号隔开
  • 字段是可以省略的，但是后面的值必须要一一对应，不能少
  • 同时可以插入多条数据，values后面的值，需要使用逗号隔开即可。

修改（updata）

updata 修改谁（条件） se 字段名=新值

 -- 修改学员的名字
 -- 有where条件  就修改对应where条件下的字段的值
 UPDATE `student` SET `name` = 'ljy' WHERE id =1;
 
 -- 不指定条件的情况下，会改动所有的表
  UPDATE `student` SET `name` = 'ljy'

 -- 修改多个属性
 UPDATE `student` SET `name`='xxxx',`email`='qq!' WHERE id = 1;

语法：

update 表名 set colnum_name（列） = value,[colnum_name（列） = value],[....] where[条件]

条件：where字句 运算法 id等于某个值，大于某个值，在某个区间修改...

操作符会返回布尔值

操作符 含义 范围 结果
= 等于 5=6 false
<>或者!= 不等于 5<>6 ture
> 大于 5>6 false
< 小于 5<6 true
<= 小于等于 5<=6 true
>= 大于等于 5>=6 false
between..and.. 在某个范围内 闭区间 [2,5]
and 我和你 && 5>1 and 1>2 false
or 我或你 || 5>1 or 1>2 true 
-- 通过多个条件定位数据
update `student` set `name`='xxx' where `name`='ljy' and sex='女';

语法：

update 表名 set colnum_name = value,[colnum_name = value] where[条件]

注意：

  • colnum_name 是数据库的列 尽量带上``
  • 条件，筛选的条件，如果没有指定，则会修改所有的列
  • value，是一个具体的值，也可以是一个变量
UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `id`='1';
  • 多个设置的属性之间，使用英文逗号隔开。

删除

delete命令

语法：delete from 表名 [where 条件]

-- 删除数据（避免这样写，会全部删除）
DELETE FROM `student`

-- 删除指定数据
DELETE FROM `student` WHERE id=1;

truncate 命令

作用：完全清空一个数据表，表的结构和索引约束不会变！

-- 清空student表
TRUNCATE TABLE	`student`

delete与truncate的区别

  • 相同点：都能删除数据，都不会删除表结构
  • 不同点：
    • truncate重新设置自增列 计数器会归零
    • truncate 不会影响事务
DELETE FROM `test` -- 不会影响自增
TRUNCATE TABLE `test`  -- 自增会归零

了解即可：DELETE删除的问题，重启数据库，现象：

  • INNODB 自增量会重1开始（存在内存当中的，断电即失）
  • MYISAM 继续从上一个自增量开始，（存在文件中的，不会丢失）

DQL查询数据（最重点）

DQL(注意下图的语句顺序。)

（Data Query Language:数据查询语言）

  • 所有的查询操作都用它 Select

  • 简单的查询，复杂的查询它都能做~

  • 数据库中最核心的语言，最重要的语句

  • 使用频率最高的语句

image-20201211102910165

指定查询字段

-- 查询全部的学生      select 字段 from 表名
SELECT * FROM student
-- 查询全部的成绩
SELECT * FROM result

-- 查询指定字段
SELECT `studentno`,`studentname` FROM `student`

-- 别名，给结果起一个名字 AS   可以给字段起别名，也可以给表起别名。
SELECT `studentno` AS '学号',`studentname` AS '名字' FROM `student` AS SSS
(as和单引号 可以省略)
-- 函数 Concat(a,b)
SELECT CONCAT('姓名：',`studentname`)AS 新名字 FROM `student`

语法：SELECT 字段1,... from 表

有时候，列名字不是那么见名知意。那么我们就起别名 AS 字段名 as 别名 表名 as 别名

去重 （distinct）

作用：去除select查询出来的结果中重复的数据，重复的数据只显示一条

-- 查询有哪些同学参加了考试，成绩
SELECT *FROM `result`  -- 查询全部的成绩
SELECT `studentno` FROM `result`-- 查询有哪些同学参加了考试
SELECT DISTINCT `studentno` FROM `result`-- 发现重复数据，去重

数据库的列（表达式）

SELECT VERSION()  -- 查询系统版本 （函数）
SELECT 100*3 AS 结果：;  -- 计算结果 （计算表达式）
SELECT @@auto_increment_increment   -- 查询自增的步长  （变量）

-- 学员考试成绩  +1分查看
SELECT `studentno`,`studentresult`+1 AS '+1分后' FROM `result`

数据库中的表达式：文本值，列，NULL，函数，计算表达式，系统变量....

select 表达式 from 表

where条件子句

作用：检索数据中符合条件的值。

搜索的条件由一个或者多个表达式组成！ 结果 布尔值。

逻辑运算法

运算符 语法 描述
and && a and b a&&b 逻辑与，两真则真
or || a or b a||b 逻辑与，有一真即为真
not ！ not a ！a 逻辑非，真为假，假为真！

尽量使用英文符合

SELECT studentNO,`studentresult` FROM `result`

-- 查询考试成绩在95~100的
SELECT studentNO,`studentresult` FROM `result`
WHERE `studentresult`>=95 AND `studentresult`<=100;

SELECT studentNO,`studentresult` FROM `result`
WHERE `studentresult`>=95 &&`studentresult`<=100;


-- 模糊查询（区间）
SELECT studentNO,`studentresult` FROM `result`
WHERE `studentresult` BETWEEN 95 AND 100

-- 除了1000号学生之外的同学的成绩
SELECT studentNO,`studentresult` FROM `result`
WHERE `studentno`!=1000

SELECT studentNO,`studentresult` FROM `result`
WHERE  NOT`studentno`=1000

模糊查询：比较运算符

运算符 语法 描述
IS NULL a is null 如果操作符为null，则结果为真
IS NOT NULL a is not null 如果操作符为not null，则为真
between a between b and c 若a在b和c之间，则结果为真
like a like b SQL匹配，如果a匹配b，则结果为真
In a in （a1,a2,a3） 假设a在a1，或者a2，或者a3其中的某一个值中，结果为真 
-- =====================模糊查询-===============================

-- 查询姓张的同学
-- 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 '%嘉%'

-- ===================In(具体的一个或者多个值)==============================
-- 查询 1001,1002,1003号学员信息
SELECT `studentno` ,`studentname` FROM `student`
WHERE `studentno`IN(1001,1002);

-- 查询在北京的学生
SELECT `studentno` ,`studentname` FROM `student`
WHERE `address` IN('北京'，'安徽');        -- in后面要具体的值


-- ==================NULL     NOT  NULL======================
-- 查询地址为空的学生 null ''
SELECT `studentno` ,`studentname` FROM `student`
WHERE `address`=''      -- 或者 `address` is null

-- 查询有出生日期的同学
SELECT `studentno` ,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL;

-- 查询没有出生日期的同学
SELECT `studentno` ,`studentname` FROM `student`
WHERE `borndate` IS  NULL;

联表查询 join

img

-- ============联表查询  join=================
-- 查询参加了考试的同学(学号，姓名，科目编号，分数）
 /*
思路：
1. 分析需求，分析查询的字段来自哪些表，(连接查询)
2. 确定使用哪种连接查询?
确定一个交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表的中studentNo =成绩表studentNo
 */
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
WHERE s.`studentno` = r.`studentno`
 -- as 可以省略
-- right Join   
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON s.`studentno` = r.`studentno`

-- left Join   
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s
LEFT JOIN `result` r
ON s.`studentno` = r.`studentno`

-- 查询缺考的同学
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s
LEFT JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE `studentresult` IS NULL
  -- join （连接的表） on（判断的条件） 连接查询
 -- where 等值查询
操作 描述
Inner join 如果表中至少有一个匹配，就返回行
left join 会从左表中返回所有的值，即使右表中没有匹配
right join 会从右表中返回所有的值，即使左表中没有匹配 
 -- join （连接的表） on（判断的条件） 连接查询
 -- where 等值查询

练习

 -- 思考题（查询了参加考试的同学信息：学号，学生姓名，科目名，分数）
  /*
思路：
1. 分析需求，分析查询的字段来自哪些表，student result,subject(连接查询)
2. 确定使用哪种连接查询?          7种  
确定一个交叉点（这两个表中哪个数据是相同的）    student 与result表学号和姓名相同，result与subject表中subjectno相同
判断的条件：学生表的中studentNo =成绩表studentNo
 */
 
 SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
 FROM student AS s
 RIGHT JOIN `result` AS r
 ON r.`studentno`=s.`studentno`
 INNER JOIN `subject` AS sub
 ON r.`subjectno` = sub.`subjectno`
 
 -- 我要查询哪些数据    select。。。
 -- 要从哪几个表中查  from 表 xxx join 连接的表   on 交叉条件
 -- 假设存在一种多张表查询，慢慢来，先查询两张表，然后再慢慢增加

自连接（了解）

自动的表和自己的表连接，核心：一张表拆为两张一样的表即可

image-20201211095326999

父类表（本题为：顶级id）

categoryid categoryName
2 信息技术
3 软件开发
5 美术设计

子类：

categoryid pid categoryName
4 3 数据库
8 2 办公信息
6 3 web开发
7 5 PS技术

子类的pid是父类的categoryID

操作：查询父类对应的子类关系

父类 子类
信息技术 办公信息
软件开发 数据库
软件开发 web开发
美术设计 PS 
 -- ==================================自连接=========================================================
 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','软件开发'),('4','3','数据库'),
('5','1','美术设计'),('6','3','web开发'),('7','5','PS技术'),('8','2','办公信息');

-- 查询父子信息,把一张表拆分为两张一模一样的表
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryId` = b.`pid`

再联系联表查询（join）

-- 查询学员所属的年级（学号，学生的姓名，年级名称）
SELECT studentNO,`studentname`,`gradename`
FROM student s 
INNER JOIN `grade` g
ON s.`gradeid`=g.`gradeid`


-- 查询科目所属的年级（科目名称，年级名次）
SELECT `subjectname`,`gradename`
FROM `subject` sub
INNER JOIN `grade` g
ON sub.`gradeid`=g.`gradeid`

-- 查询了参加高等数学-1考试的同学信息:学号，学生姓名，科目名，分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`studentno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1'

分页与排序

排序

-- order by 通过那个字段排序，怎么排
-- 查询的结果根据 成绩降序，排序 
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`studentno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1'
ORDER BY `studentresult` DESC    //  ASC(升序)

分页

-- 为什么要分页？     缓解数据库的压力，给人的体验更好      ，      瀑布流

-- 分页 每页只显示五条数据
-- 语法：limit 起始值 ， 页面的大小
-- 网页应用：当前，总的页数，页面的带下
-- limit 0,5    第1条数据到第5条数据
-- limit 1,5    2~6	 	
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`studentno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1'
ORDER BY `studentresult` ASC
LIMIT 0,5

-- 第一页 limit 0,5 （1-1）*5
-- 第二页 limit 5,5  （2-1）*5
-- 第三页 limit 10,5  （3-1）*5
-- 第N页  limit  n，5   （n-1）*pagesize，    pagesize，
-- 【pagesize，页面大小，（n-1）*pagesize起始值，n 代表当前页  】
-- 【数据总数/页面大小=总页数】

语法：

limit(查询起始下标，pagesize)

练习题：

-- 思考：查询 Java第一学年课程成绩排名前10的学生，并且分数要大于80的学生信息（学号，姓名，课程名称，分数）
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student`  s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`studentno`  
WHERE `subjectname`='Java第一学年' AND `studentresult`>=80
ORDER BY studentresult DESC 
LIMIT 0,10

子查询(Where)

where(这个值是计算出来的)

本质：在where语句中嵌套一个子查询语句

-- ===============where==========================
-- 1.查询数据库结构-1的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式1：使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='据库结构-1'
ORDER BY studentresult DESC 

-- 方式2  使用子查询（由里及外）
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result` 
WHERE studentno = (
SELECT subjectno FROM `subject`
 WHERE `subjectname`='据库结构-1')
 ORDER BY studentresult DESC 
-- 查询所有数据库结构-1 的学号学号

-- 分数不小于80分的学生的学号与姓名
SELECT DISTINCT s.`studentno`,`studentname` 
FROM `student` s
INNER JOIN `result` r
ON r.studentno=s.studentno
WHERE `studentresult`>=80

-- 在上面列子上增加一个科目，高等数学-2
-- 查询高等数学-2的编号
SELECT DISTINCT s.`studentno`,`studentname` 
FROM `student` s
INNER JOIN `result` r
ON r.studentno=s.studentno
WHERE `studentresult`>=80 AND `subjectno`=(
SELECT `subjectno`
FROM `subject`
WHERE `subjectname`='高等数学-2'
)

-- 进阶版(由里及外)
SELECT studentno,studentname FROM student WHERE studentno IN(
	SELECT studentno FROM result WHERE `studentresult` >=80 AND subjectno =(
		SELECT subjectno FROM `subject` WHERE `subjectname`='高等数学-2'
		)
)

分组和过滤

-- 查询不同课程的平均分，最高分，最低分 平均分大于80分的
-- 核心：（根据不同的课程分组）
SELECT subjectname,AVG(studentresult) 平均分,MAX(studentresult) AS 最高分,MIN(studentresult)
FROM result AS r
INNER JOIN `subject` AS sub
ON r.`subjectno`= sub.`subjectno`
GROUP BY r.subjectno -- 通过什么字段来分组
HAVING 平均分>=80

select小结

顺序很重要：
select 去重 要查询的字段 from 表 （注意：表和字段可以取别名）
xxx join 要连接的表 on 等值判断
where（具体的值，子查询语句）
Group By（通过哪个字段来分组）
Having（过滤分组后的信息，条件和where是一样的，位置不同）
Order by ... （通过哪个字段来排序）[升序/降序]
Limit startindex ，pagesize


业务层面
查询：跨表，跨数据库...

MySQL函数

常用函数

-- ============常用函数==================
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4)	 -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND()   -- 返回一个0~1之间的随机数。
SELECT SIGN()  -- 判断一个数的符号	 负数返回-1， 正数返回1    0-0 

-- 字符串函数
SELECT CHAR_LENGTH('即使再小的') -- 字符串长度
SELECT CONCAT ('w','a','asd')  -- 拼接字符串
SELECT INSERT('wqerr',2,3,'11111')  -- 查询，从某个位置开始替换某个长度
SELECT LOWER('safw')  -- 小写字母
SELECT UPPER('qwer')  -- 大写字母
SELECT INSTR('ljy','j')  -- 返回第一次出现的子串索引
SELECT REPLACE('坚持就能成功','坚持','努力')   -- 替换出现的指定字符串
SELECT SUBSTR('坚持就能成功',4,2)   -- 返回指定的子字符串(源字符串，截取的位置，截取的长度)
SELECT REVERSE('赵兄托我帮办点事') -- 反转字符串

重要：

-- 时间和日期函数（重要  记住）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE()  -- 获取当前日期
SELECT NOW()      -- 获取当前的时间
SELECT LOCALTIME()   --  本地时间
SELECT SYSDATE()  -- 	系统时间

-- 系统
SELECT SYSTEM_USER()

聚合函数（常用）

函数名称 描述
count（） 计数
sum（） 求和
avg（） 平均值
max（） 最大值
min（） 最小值
......... .............. 
-- =================聚合函数====================================
 -- 都能够统计 表中的数据 (想查询一个表中有多少个记录，就使用这个count ())
 
SELECT COUNT(studentname) FROM student     -- count(字段) ，会忽略所有的null值	
SELECT * FROM student             -- coun(*) ，不会忽略null值      本质  计算行数
SELECT COUNT(1) FROM result;         -- count(1)，不会忽略所有的null值    本质  计算行数
  
SELECT SUM(`studentresult`) AS 总和 FROM `result`
SELECT AVG(studentresult) AS 平均分 FROM result
SELECT MAX(studentresult) AS 最低分 FROM resul

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

什么是MD5？

主要增强算法复杂和不可逆行

MD5 不可逆，具体的值的MD5值是一样的

MD5破解网站的原来，背后有一个字典，MD5加密后的值，加密的前值

-- ============================测试MD5 加密========
CREATE TABLE `testmd5`(
`id` INT(5) NOT NULL,
`name` VARCHAR(50) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4

-- 明文密码
INSERT INTO `testmd5` VALUES(1,'张三','126'),(2,'李四','5456'),(3,'王麻子','1456')

-- 加密：
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=1  -- 这里要注意varchar字段的长度要足够 我设置30不行 后面改成50就可以了

UPDATE testmd5 SET pwd=MD5(pwd)  -- 加密全部的密码

-- 插入的时候加密
INSERT INTO `testmd5` VALUES(4,'ljy',MD5('8423450'))

-- 何如校验，将用户传递进来的密码，进行md5加密，然后对比加密后的值
SELECT * FROM testmd5 WHERE `name`='ljy' AND pwd =MD5('8423450')

事务

什么是事务

要么都成功，要么都失败

  1. SQL执行 |A给B 转账
  2. SQL执行 B收到A的钱

将一组SQL放在一个批次中去执行~

事务原则：ACID原则 ： 原子性 一致性 隔离性 持久性

博客参考链接：https://blog.csdn.net/dengjili/article/details/82468576

原子性（Atomicity）

要么都成功，要么都失败

一致性（Consistency）

事务前后的数据完整性要保证一致

持久性（Durability）

事务一旦提交则不可逆，被持久化到数据库中!

隔离性（Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

隔离所导致的一些问题：

脏读：

指一个事务读取了另外一个事务未提交的数据。

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

执行事务

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

 -- 手动处理事务
 SET autocommit = 0 -- 关闭自动条件
 -- 事务开启
 START TRANSACTION  -- 标记一个事务的开始， 从这个之后的sql都在同一个事务内
 INSERT xxx
 
 
 -- 提交：持久化
 COMMIT
 
 -- 回滚:回到原来的样子（失败就回滚）
 ROLLBACK
 
 
 -- 事务结束
 SET autocommit = 1   -- 开启自动提交
 
 
 SAVEPOINT 保存点名    -- 设置一个事务的保存点
 ROLLBACK TO SAVEPOINT 保存点名

模拟场景

-- 转账
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
USE shop
CREATE TABLE `account`(
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	`money` DECIMAL(9,2) NOT NULL,  -- 一共9位数，小数是两位
	PRIMARY KEY(`id`)
	)ENGINE = INNODB DEFAULT CHARSET=utf8mb4


INSERT INTO account (`name`,`money`) 
VALUE('A',2000.00),('B',10000.00)

-- 模拟转账：事务
SET autocommit = 0;  -- 关闭自动提交
START TRANSACTION  -- 开启一个事务（一组事务）
UPDATE account SET money=money-500 WHERE `name`='A'   -- A减少500
UPDATE account SET money=money+500 WHERE `name`='B'    -- B增加500

COMMIT;   -- 提交事务,持久化了
ROLLBACK;  -- 回滚

SET autocommit =1;  -- 恢复默认值

索引

本质：

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质:索引是数据结构。

索引的分类

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

  • 主键索引 (PRIMARY KEY)
    • 唯一的标识，主键不可重复，只能有一个列作为主键
  • 唯一索引（UNIQUE KEY）
    • 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
  • 常规索引（KEY/INDEX）
    • 默认的，index/key关键字来设置
  • 全文索引（FullText）
    • 在特定的数据库引擎下才有，MyISAM
    • 快速定位数据

基础语法：

-- 索引的使用
-- 1. 在创建表的时候给字段增加索引
-- 2. 创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student

-- 增加一个索引
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(studentname);

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student    -- 非全文索引

EXPLAIN SELECT * FROM student  WHERE MATCH(studentname) AGAINST('刘');

测试索引

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION `mock_data`()
RETURNS INT
DETERMINISTIC
BEGIN
	DECLARE num INT DEFAULT 1000000;
    DECLARE i INT DEFAULT 0;
    WHILE i<num DO
		INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`) VALUES(CONCAT('用户',i),'xxxxxx@qq.com',CONCAT('18',FLOOR(RAND()*999999999)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
        SET i = i+1;
	END WHILE;
    RETURN i;
END;
-- 查询 执行函数
SELECT mock_data();

语法

-- id_表名_字段名
-- create index 索引名 on 表(字段)
 create index id_app_user_name on app_user(`name`)

索引在小数据的数据量的时候不需要，但是在大数据的时候是需要的，提高效率，（重要！）

索引原则：

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

索引的数据结构

Hash数据的索引

Btree：INNODB的默认数据结构~

权限管理和备份

用户管理

SQL yog可视化管理

SQL命令操作

用户表：mysql.user

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

-- 创建用户  CREATE USER  用户名 IDENTIFIED by '密码'
CREATE USER lijinyu IDENTIFIED BY '123456'

-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('xxxx');

-- 修改密码（指定用户的密码）
SET PASSWORD FOR ljy =PASSWORD('xxxx')

-- 重命名   RENAME USER  原来的名字 TO 新的名字
RENAME USER lijinyu TO ljy2


-- 用户授权  ALL PRIVILEGES 全部的权限,库.表
-- ALL PRIVILEGES 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO ljy2

-- 查询权限
SHOW  GRANTS FOR ljy 2  -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost 

-- 撤销权限 revoke 哪些权限， 在哪里库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM ljy2

-- 删除用户
DROP USER ljy2

MySQL备份

为什么要备份：

  • 保证重要的数据不丢失
  • 数据转移

MySQL数据库备份的方式

  • 直接拷贝物理文件
  • 在Sqlyog这种可视化工具中手动导出
    • 在想要导出的表或者库中，右键，选择备份或导出
  • 使用命令行导出 mysqldump 命令行使用(dos命令)
# mysqldump -h  主机 -u 用户名 -p 密码  数据库 表名1 表2 表3.... >  物理磁盘位置/文件名  （导出多张表）
mysqldump -hlocalhost -uroot -p123456 school student >D:a.sql

#导入
#登录的情况下，切换到指定的数据库

# sourcce  备份文件（磁盘加文件名.sql）

假设你要备份数据库，防止数据丢失

sql文件给别人。

规范数据库设计

为什么需要设计

当数据库比较复杂的时候，我们就需要设计了

糟糕的数据库设计：

  • 数据冗余，浪费空间

  • 数据库插入和删除都会麻烦、异常 [屏蔽使用物理外键]

  • 程序的性能差

良好的数据库设计：

  • 节省内存看见
  • 保证数据库的完整性
  • 方便我们开发系统

软件开发中，关于数据库的设计

  • 分析需求：分析业务和需要处理的数据库的需求
  • 概要设计：设计关系图E-R图

设计数据库的步骤：（个人博客）

  • 收集信息。分析需求
    • 用户表（登录，注销，个人信息，写博客，创建分类）
    • 分类表（文章分类，谁创建的）
    • 文章表（文章的信息）
    • 评论表
    • 友链表（友链信息）
    • 自定义表（系统信息，某个关键的字，或者一些主字段） key：value （可无）
    • 说说表（发表心情...id。。content...create_time）
  • 标识实体（把需求落地到每个字段）
  • 标识实体 之间的关系
    • user-->blog（写博客）
    • user-->category（创建分类）
    • uer-->user（关注）
    • links(友链)
    • user-user-blog(评论表)

三大范式（了解，但是要记一下具体的）

为什么需要数据规范化？

  • 信息重复
  • 更新异常
  • 插入异常
    • 无法正常显示信息
  • 删除异常
    • 丢失有效的信息

三大范式：

第一范式（1NF）

原子性：保证每列都不可再分了。

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情。

第三范式（3NF）

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

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

（规范数据库的设计）

规范性 和 性能的问题

关联查询的表不得超过三张表。

  • 考虑商业化的需求和目标，（成本，用户体验! ）数据库的性能更加重要
  • 在规范性能的问题的时候，需要适当的考虑一下规范性！
  • 故意给某些表增加一下冗余的字段（从多表查询中变为单表查询）
  • 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）
posted @ 2020-12-16 22:18  喂s别闹  阅读(230)  评论(0)  编辑  收藏  举报