MySQL学习笔记

1、命令行连接数据库

net start mysql -- 启动MySQL服务

mysql -uroot -p123456 -- 连接数据库

update mysql.user set authentication_string=password('123456') where user='root' and Host= 'localhost'; -- 修改用户密码

flush privileges; -- 刷新权限

net stop mysql -- 停止MySQL服务
-----------------------------------------------------------------
show databases; -- 注意分号结尾,查看所有的数据库

 use mysql -- 使用mysql表
 
 show tables; -- 查看数据库中所有表
 
 use student;
 describe stu; -- 显示student数据库的stu表的所有信息
 
 create database westos; -- 创建westos数据库
 
 show create database school; -- 查看创建数据库的语句
 
 show create table student; -- 查看创建表的定义语句
 
 desc student; -- 查看表结构
 
 EXPLAIN select * from user -- 查看SQL执行的状况EXPLAIN
 
 exit; -- 退出连接
 
 -- sql单行注释
 /*
 	sql多行注释
 */

2、操作数据库

MySQL关键字不区分大小写

2.1、创建数据库

CREATE DATABASE IF NOT EXISTS `school` CHARACTER SET utf8 COLLATE utf8_general_ci; -- 创建数据库并设置字符编码与字符集校对
CREATE DATABASE westos; -- 如果已经存在westos数据库,会报错
CREATE DATABASE IF NOT EXISTS westos; -- 如果已经存在westos数据库,不会报错只会警告

2.2、删除数据库

DROP DATABASE [IF EXISTS] westos;

2.3、使用数据库

USE `student`; -- 如果你的表名或者字段名是一个特殊字符,则需要带 `` 

2.4、查看数据库

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

3、数据库的列类型

3.1、数值

tinyint -- 十分小的数据 1个字节
smallint -- 较小数据 2个字节
mediumint -- 中等大小的数据 2个字节
int -- 标准的整数 4个字节 常用的
bigint -- 较大的数据 8个字节

3.2、浮点数

float -- 浮点数 4个字节
double -- 浮点数 8个字节(存在精度问题)

-- 金融计算的时候一般使用decimal:
DECIMAL(M,N) -- 字符串形式的浮点数
-- M表示:不包括小数点,不包括符号位,所能存数字的总位数(包括小数部分和整数部分) 
-- N表示:小数部分数字的位数,由此可知整数部分的位数为M-N
DECIMAL(4,2) -- 能够表示的数的范围 -99.99到99.99

3.3、字符串

char 字符串固定大小 0~255
varchar 可变字符串 0~65535 常用的 对应Java中的String
tinytext 微型文本 2^8 - 1
text 文本串 2^16 - 1 保持大文本

3.4、时间格式

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

3.5、null

-- 没有值 未知
-- 注意:不要使用null进行运算

4、数据库的字段属性(重点)

MySQL学习笔记

4.1、Unsgned:

-- 无符号的整数
-- 声明了该列不能声名为负数

4.2、zerofill:

-- 0填充的
-- 不足位数,使用0来填充 如int(3),5 --> 005

4.3、自增(Auto Inor):

-- 通常理解为自增,自动在上一条记录的基础上+1(默认)
-- 通常用来设计唯一的主键 index, 必须是整数类型
-- 可以自定义设计主键自增的起始值和步长

4.4、非空(Not Null):

-- 若勾选PK设置为主键,则自动勾选Not Null
-- 若选择非空,则不允许字段为空(空指的是null,什么都不填指空字符串""),如果为空会报错
-- 若不选择非空,如果不填写值,默认就是null

4.5、defult:

-- 设置默认值
-- sex,默认值为男,如果不指定该列的值,则会有默认的值

4.6、PK(主键):

-- 是否为主键

4.7、创建数据库表实例

USE  school; -- 先使用到数据库再创建表
CREATE TABLE IF NOT EXISTS `student` ( -- 使用 `` 包括起来避免不小心与MySQL关键字重名
    `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`) -- 设置id为主键
)ENGINE = INNODB DEFAULT CHARSET = utf8

4.8、创建数据库表的字符集编码

CHARSET = utf8

不设置的话,会是MySQL默认的字符集编码Latin1,不支持中文。

也可以在 my.ini 文件中配置默认的编码,不建议这样做,修改物理文件会导致代码通用性降低,即在自己电脑上能运行,在别人电脑上可能会失败。

character-set-server = utf8

5、MyISAM 和 InnoDB区别

-- 关于数据库引擎
/*
    INNODB 默认使用
    MYISAM 早些年使用
*/

MySQL学习笔记

常规使用

  • MYISAM:节约空间,速度最快

  • InnoDB:安全性高,事务的处理,多表多用户操作

在物理空间的位置

  • 所有的数据库文件都存放在data目录下,一个文件夹就代表一个数据库

本质还是文件存储

  • MySQL引擎在屋里文件上的区别:
    • InnoDB 在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
    • MYISAM 对应的文件:*.frm-表结构的定义文件, * .MYD-数据文件(data), *.MYI-索引文件(index)

6、修改删除表

-- 修改表
ALTER TABLE `student` RENAME AS `student1`; -- 修改表名
ALTER TABLE `student1` ADD age INT(11); -- 添加表的字段
ALTER TABLE `student1` MODIFY age VARCHAR(11); -- 修改表的字段(修改约束)
ALTER TABLE `student1` CHANGE age age1 INT(11); -- 修改表的字段(字段重命名)
ALTER TABLE `student1` DROP age1; -- 删除表的字段

-- 删除表(所有的创建和删除操作尽量加上判断,以免出错)
DROP TABLE IF EXISTS `student1`; -- 删除表(如果表存在再删除)

7、MySQL数据管理

7.1、外键

概念
如果公共关键字在一个关系中是主关键字,那么这个公共关键字被称为另一个关系的外键。由此可见,外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表,具有此外键的表被称为主表的从表

在实际操作中,将一个表的值放入第二个表来表示关联,所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时,第二个表中保存这些值的属性称为外键(foreign key)。

外键作用

保持数据一致性,完整性,主要目的是控制存储在外键表中的数据约束。使两张表形成关联,外键只能引用外表中的列的值或使用空值。

外键的创建方式

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
CREATE TABLE IF NOT EXISTS `student` ( -- 使用 `` 包括起来避免不小心与MySQL关键字重名
    `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 '生日',
    `gradeid` INT(10) NOT NULL COMMENT '年级id',
     `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
     `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
     PRIMARY KEY (`id`), -- 设置id为主键
     -- 方式一:在创建表的时候添加外键约束
     CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`) -- 设置外键约束
)ENGINE = INNODB DEFAULT CHARSET = utf8

-- 方式二:创建表的时候没有外键关系,修改从表添加外键
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

删除具有主外键关系的表时 , 必须先删除具有外键的表(从表),再删除被引用的表(主表)。

-- 删除外键
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 发现执行完上面的,索引还在,所以还要删除索引
-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE student DROP INDEX FK_gradeid;

以上的操作都是物理外键,数据库级别的外键,不建议使用,避免数据库过多造成困扰。

最佳实现:

  • 数据库就是单纯的表,只用来存储数据,只有行(数据)和列(字段)

  • 我们想使用多张表的数据,想使用外键(程序去实现)

7.2、DML语言(全部记住)

数据库意义 : 数据存储、数据管理

管理数据库数据方法:

  1. 通过SQLyog等管理工具管理数据库数据
  2. 通过DML语句管理数据库数据

DML语言 :数据操作语言,用于操作数据库对象中所包含的数据

包括 :

  • INSERT (增加数据语句)

语法:insert into [表名]([字段名],[字段名]) values([值],[值]);

-- DML 添加语句(插入)
INSERT INTO `grade` (`gradename`) VALUES('大四'); -- 由于主键自增,我们可以省略
INSERT INTO `grade` (`gradename`) VALUES('大三'),('大四'); -- 可以同时插入多个值
INSERT INTO `grade` VALUES(4,'大四'); -- 如果不写表的字段,他就会一一匹配
INSERT INTO `student` (`name`,`pwd`,`sex`,`birthday`,`gradeid`,`address`,`email`)
VALUES('张三','1213','男','20210708160600',1,'江西','123456789@qq.com');

注意事项:

  1. 一般写插入语句,字段和数据一定要一一对应
  2. 字段或值之间用英文逗号隔开
  3. ’ 字段1,字段2…’ 该部分可省略,但添加的值务必与表结构,数据列,顺序相对应,且数量一致
  4. 可同时插入多条数据,values 后用英文逗号隔开,即VALUES(),()
  • UPDATE (修改数据语句)

语法:UPDATE [表名] SET [字段名]=[修改值] WHERE [条件];

-- DML 修改语句(更新)
UPDATE `student` SET `name` = '刘翔'; -- 不指定条件的情况下,会改动所有表
UPDATE `student` SET `name` = '刘翔' WHERE `id` = 1; -- 修改id为1的name字段数据
UPDATE `student` SET `name` = '刘翔',`email` = '1232421342@qq.com' WHERE id = 1; -- 修改多个字段数据
UPDATE `student` SET `sex` = '女' WHERE `id` BETWEEN 2 AND 3; -- between ... and ... 表示一个区间
UPDATE `student` SET `birthday` = CURRENT_TIME WHERE `name` = '刘翔' AND `sex` = '女'; -- 修改值也可以是一个变量,此处将出生日期修改为了当前时间
UPDATE `student` SET `address` = '南昌' WHERE `id` <> 2; -- <> 与 != 一个意思
  • DELETE (删除数据语句)

语法:DELETE FROM 表名 [WHERE 条件匹配];

-- DML 删除语句(删除)
DELETE FROM `student`; -- 删除全部数据(避免这样写),不会影响自增
DELETE FROM `student` WHERE `id` = 1; -- 删除id为1的数据
TRUNCATE `student` -- 删除全部数据,自增会归零,不会影响事务

MySQL学习笔记

8、DQL查询数据(最重点)

DQL( Data Query Language 数据查询语言 )

  1. 所有的查询操作都用它 select
  2. 简单的单表查询或多表的复杂查询和嵌套查询它都能做
  3. 是数据库语言中最核心,最重要的语句
  4. 使用频率最高的语句

语法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2] -- 联合查询
  [WHERE ...] -- 指定结果需满足的条件
  [GROUP BY...] -- 指定结果按照哪几个字段来分组
  [HAVING] -- 过滤分组的记录必须满足的次要条件
  [ORDER BY  ...] -- 指定查询记录按一个或多个条件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条

8.1、简单查询及表达式

-- DQL查询数据
SELECT * FROM student; -- 查询全部的学生
SELECT `studentno`,`studentname` FROM student; -- 查询指定字段
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student AS 学生; -- 别名,给结果起个名字,AS
SELECT CONCAT('姓名:',`studentname`) AS 新名字 FROM student; -- 字符串拼接函数 Concat(a,b)
SELECT DISTINCT `studentno` FROM `result`; -- 去除重复数据 关键字:DISTINCT
SELECT VERSION(); -- 查询系统版本
SELECT 100*3-1 AS 计算结果; -- 用来计算
SELECT @@auto_increment_increment; -- 查询自增的步长(变量)
SELECT `studentno`,`studentresult`+1 AS 提分后 FROM result; -- 学员考试成绩 +1 分查看

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

where条件子句

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

-- where条件子句
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`>=80 AND `studentresult`<=100;
-- 模糊查询(区间)
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` BETWEEN 80 AND 100;
-- 除了1000号学生之外的同学成绩
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentno` != 1000;
SELECT `studentno`,`studentresult` FROM `result`
WHERE NOT `studentno` = 1000;

MySQL学习笔记

MySQL学习笔记

8.2、模糊查询

  • 数值数据类型的记录之间才能进行算术运算
  • 相同数据类型的数据之间才能进行比较
-- 模糊查询
-- like结合 %(代表0到任意个字符) _(一个字符)
SELECT `studentname`,`sex` FROM `student`
WHERE  `studentname` LIKE '周%'; -- 查询姓周的同学
SELECT `studentname`,`sex` FROM `student`
WHERE  `studentname` LIKE '%州%'; -- 查询名字中带州的同学
-- in
SELECT `studentno`,`studentname`,`sex` FROM `student`
WHERE  `studentno` IN (1000,1001,1002); -- 查询1000,1001,1002号同学
SELECT `studentno`,`studentname`,`sex` ,`address` FROM `student`
WHERE  `address` IN ('江西南昌'); -- 查询地址在江西南昌的同学
-- null 和 not null
SELECT `studentno`,`studentname`,`sex` ,`address` FROM `student`
WHERE  `address` = '' OR `address` IS NULL; -- 查询地址为 '' 或 null 的同学
SELECT `studentno`,`studentname`,`sex` ,`address` FROM `student`
WHERE  `borndate` IS NOT NULL; -- 查询有出生日期的同学

MySQL学习笔记

8.3、联表查询

  • 七种JOIN理论

MySQL学习笔记

  • 连接查询:如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
操作 描述
inner join 如果表中至少有一个匹配,就返回行
left join 会从左表中返回所有的值,即使右表中没有匹配,对于要查询的右表字段则默认为null
right join 会从右表中返回所有的值,即使左表中没有匹配,对于要查询的左表字段则默认为null
-- 联表查询 join
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
-- inner join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s -- 起别名
INNER JOIN result AS r
ON s.`studentno` = r.`studentno`;

-- right join 使用right join会查出result表中存在但student表中不存在的学生,在此情景下参与考试的学生一定也在student表中,因此查询结果与使用inner join一致
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s -- AS可省略 
RIGHT JOIN result r
ON s.`studentno` = r.`studentno`;

-- left join 使用left join会查出student表中存在但result表中不存在的学生,即缺考的学生,subjectno与studentresult值均默认为null
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s -- AS可省略 
LEFT JOIN result r
ON s.`studentno` = r.`studentno`

-- 查询缺考学生
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s -- AS可省略 
LEFT JOIN result r
ON s.`studentno` = r.`studentno`
WHERE `studentresult` IS NULL; 
  • 等值查询

-- 等值查询
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s , result AS r -- 起别名
WHERE s.`studentno` = r.`studentno`;

-- 思考题,三表查询(查询参加了考试的同学信息:学号,学生姓名,科目名,分数)
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r -- 因为是要查询参加了考试的同学信息,所以以右表result为基准
ON r.`studentno` = s.`studentno`
LEFT JOIN SUBJECT sub -- 同理以左表为基准
ON r.`subjectno` = sub.`subjectno`;
  • 自连接

    • 数据表与自身进行连接

    • 将一张表拆成两张表

-- 创建一个表
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','办公信息');

8.4、分组和过滤

-- 查询不同课程的平均分,最高分,最低分,平均分大于80
-- 核心:根据不同的课程分组
SELECT `subjectname`,AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.`subjectno` -- 通过什么字段来分组
HAVING AVG(`studentresult`)>80; -- 过滤分组的记录必须满足的次要条件

8.5、排序和分页

  • 排序
-- 排序:升序 ASC , 降序 DESC
-- ORDER BY 通过哪个字段排序,怎么排
-- 查询参加了C语言-2考试的同学信息:学号,学生姓名,科目名,分数,查询的结果根据成绩降序排序
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.`subjectno`
WHERE `subjectname` = 'C语言-2'
ORDER BY studentresult DESC;
  • 分页
-- 分页:缓解数据库压力,给人更好的体验
-- limit 起始值,页面的大小
-- 第一页 : limit 0,5 
-- 第二页 : limit 5,5 
-- 第三页 : limit 10,5 
-- …
-- 第N页 : limit (N-1)*5,5
-- 查找规律可以推导出计算每页的公式:
-- limit (pageNo-1)*pageSzie,pageSzie
-- [pageNo:页码,pageSize:单页面显示条数]
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.`subjectno`
WHERE `subjectname` = 'C语言-2'
ORDER BY studentresult DESC
LIMIT 1,5;

8.6、嵌套查询(子查询)

  • 在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句

  • 嵌套查询可由多个子查询组成,求解的方式是由里及外

  • 子查询返回的结果一般都是集合,故而建议使用IN关键字

语法:

select * from [表名] 
where id in ( select id from [表名] )
-- ====================================== 嵌套查询(子查询) ================================
-- 创建一个表
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','办公信息');

-- 查询C语言-2的所有考试结果(学号,科目编号,科目名称,成绩),降序排列
 SELECT `studentno`,`subjectno`,`studentresult`
 FROM `result`
 WHERE `subjectno` = (
     SELECT `subjectno`
     FROM `subject`
     WHERE `subjectname` = 'C语言-2'
 )
 ORDER BY `studentresult` DESC;

-- 查询C语言-2科目分数不小于80分的学号和姓名
-- 嵌套查询
SELECT `studentno`,`studentname`
FROM student
WHERE `studentno` IN (
     SELECT `studentno`
     FROM `result`
     WHERE `studentresult`>=80 AND `subjectno` = (
          SELECT `subjectno`
          FROM `subject`
          WHERE `subjectname` = 'C语言-2'   
     )   
);
-- 联表查询
SELECT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno` 
WHERE sub.`subjectname` = 'C语言-2' AND `studentresult` >= 80 -- 即使有两个INNER JOIN也只能有一个WHERE

9、MySQL函数

9.1、常用函数

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

-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航');  -- 返回字符串长度   
SELECT CONCAT('我','爱','你');               -- 拼接字符串
SELECT INSERT('我爱你',2,1,'超级喜欢');      -- 查询,从某个位置开始替换某个长度 
SELECT LOWER('LIUXIANF');                    -- 转小写
SELECT UPPER('liuxiang');                    -- 转大写
SELECT INSTR('liuxiang','g');                -- 返回第一次出现的子字符串的索引
SELECT REPLACE('喜欢','喜欢','爱');          -- 替换出现的指定字符串
SELECT SUBSTR('我喜欢你',1,3);               -- 返回指定的字符串
SELECT REVERSE('赵兄托你帮我办点事');        -- 反转字符串

-- 查询姓周的同学 替换姓为邹
SELECT REPLACE(`studentname`,'周','邹') -- 数据库中数据并没有变化
FROM `student`
WHERE `studentname` LIKE '周%';

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

SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

-- 系统
SELECT SYSTEM_USER();     -- 系统当前用户
SELECT USER();            -- 系统当前用户
SELECT VERSION();         -- 系统版本

9.2、聚合函数(常用)

函数名称 描述
COUNT() 计数
SUM() 求和
AVG() 平均值
MAX() 最大值
MIN() 最小值
... ...
-- ==================================== 聚合函数 =========================================
-- 以下三种都能够统计表中的数据
SELECT COUNT(`studentname`) FROM student; -- count(指定列),会忽略所有的 null 值
SELECT COUNT(*) FROM student; -- count(*),不会忽略所有的 null 值 变质:计算行数          
SELECT COUNT(1) FROM student; -- count(1),不会忽略所有的 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;

-- 查询不同课程的平均分,最高分,最低分,平均分大于80
-- 核心:根据不同的课程分组
SELECT `subjectname`,AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.`subjectno` -- 通过什么字段来分组
HAVING AVG(`studentresult`)>80; -- 过滤分组的记录必须满足的次要条件

9.3、数据库级别的MD5加密(扩展)

  • MD5信息摘要算法:(英语:MD5 Message-Digest Algorithm),一种被广泛使用的密码散列函数,可以产生出一个128位(16字节)的散列值(hash value),用于确保信息传输完整一致。主要增强算法复杂度和不可逆性。
  • 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');

MySQL学习笔记

加密后:

-- 加密
UPDATE `testmd5` SET `pwd` = MD5(`pwd`);

-- 插入的时候加密
INSERT INTO `testmd5` VALUES(4,'小明',MD5('123456'));

-- 如何校验:将用户传递进来的密码进行MD5加密,然后比对加密后的值
SELECT * FROM `testmd5` WHERE `name` = '小明' AND `pwd` = MD5('123456');

MySQL学习笔记

10、事务

  • 事务就是将一组SQL语句放在同一批次内去执行

  • 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行

  • MySQL事务处理只支持InnoDB和BDB数据表类型

10.1、ACID

  • 原子性(Atomic)

要么全部成功,要么全部失败
整个事务中的所有操作,要么全部完成,要么全部不完成,不可能停滞在中间某个环节。事务在执行过程中发生错误,会被回滚(ROLLBACK)到事务开始前的状态,就像这个事务从来没有执行过一样。

  • 一致性(Consist)

事务执行前后数据的完整性要保持一致
一个事务可以封装状态改变(除非它是一个只读的)。事务必须始终保持系统处于一致的状态,不管在任何给定的时间并发事务有多少。也就是说:如果事务是并发多个,系统也必须如同串行事务一样操作。
其主要特征是保护性和不变(Preserving an Invariant),以转账案例为例,假设有五个账户,每个账户余额是100元,那么五个账户总额是500元,如果在这个5个账户之间同时发生多个转账,无论并发多少个,比如在A与B账户之间转账5元,在C与D账户之间转账10元,在B与E之间转账15元,五个账户总额也应该还是500元,这就是保护性和不变性。

  • 持久性(Durable)

事务一旦提交便不可逆,被持久化到数据库中
在事务完成以后,该事务对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。

  • 隔离性(Isolated)

事务在并发访问的时候,为每个用户开启单独事务,事务与事务之间要保持隔离
隔离状态执行事务,使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务,运行在相同的时间内,执行相同的功能,事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化,为了防止事务操作间的混淆,必须串行化或序列化请求,使得在同一时间仅有一个请求用于同一数据。

10.2、隔离导致的问题

  • 脏读

一个事务读取了另一个没有提交的事务
事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数

  • 幻读

在同一个事务内,读取到了别人插入的数据,导致前后读出来结果不一致
系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。

  • 不可重复读

在同一个事务内,重复读取表中的数据,表数据发生了改变
事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果不一致。

10.3、基本语法

-- ========================================== 事务 ======================================
-- MySQL 是默认开启事务自动提交的
SET autocommit = 0; -- 关闭
SET autocommit = 1; -- 开启(默认的)

-- 手动处理事务流程
SET autocommit = 0; -- 关闭自动提交

-- 事务开启
START TRANSACTION; -- 标记一个事务的开始,从这个之后的 sql 都在同一个事务内
INSERT xx
INSERT xx

-- 提交:持久化(成功!)
COMMIT

-- 回滚:回到原来的样子(失败!)、
ROLLBACK

-- 事务结束
SET autocommit = 1; -- 开启自动提交

-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点

10.4、测试事务,转账案例

-- 转账测试
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` VALUE(1,'A',2000.00),(2,'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; -- 恢复默认值

11、索引

MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构

11.1、索引分类

  • 主键索引 (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`; -- 非全文索引

-- 删除索引
DROP INDEX `studentname` ON student;
ALTER TABLE student DROP INDEX `studentname`
ALTER TABLE student DROP PRIMARY KEY;

11.2、测试索引

-- ===================================== 插入100万条数据 ===============================
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_data1 ()
RETURNS INT
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),'19224305@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
		SET i = i+1;
	END WHILE;
	RETURN i;
END;

SELECT mock_data1(); -- 执行此函数 生成一百万条数据

-- 没有添加索引查询
SELECT * FROM `app_user` WHERE `name` = '用户9999';
EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户9999';

-- 添加索引 CREATE INDEX 索引名 ON 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM `app_user` WHERE `name` = '用户9999';
EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户9999';
  • 没有添加索引查询耗时:

MySQL学习笔记

MySQL学习笔记

  • 添加索引查询耗时:

MySQL学习笔记

MySQL学习笔记

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

11.3、索引原则

  1. 索引不是越多越好
  2. 不要对经常变动的数据加索引
  3. 小数据量的表建议不要加索引
  4. 索引一般加在常用来查询的字段上

11.4、索引数据结构

  1. hash类型的索引:查询单条快,范围查询慢
  2. Btree类型的索引:b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它)

12、权限管理和备份

12.1、用户管理

  • SQLyog创建用户

MySQL学习笔记

  • 使用命令创建:本质是操作mysql数据库下的user

MySQL学习笔记

-- ==================================== 用户管理 ======================================
-- 创建用户
CREATE USER `liuxiang` IDENTIFIED BY '123456';

-- 修改当前用户密码
SET PASSWORD = PASSWORD('123456');
-- 修改指定用户密码
SET PASSWORD FOR liuxiang = PASSWORD('123456');

-- 重命名
RENAME USER liuxiang TO liuxiang1;

-- 用户授权 ALL PRIVILEGES 全部的权限 , 库.表 
-- ALL PRIVILEGES 全部的权限,与root用户区别:不包含GRANT权限
GRANT ALL PRIVILEGES ON *.* TO liuxiang1;

-- 查看指定用户权限
SHOW GRANTS FOR liuxiang1;
-- 查看root用户权限
SHOW GRANTS FOR root@localhost;

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

-- 删除用户
DROP USER liuxiang1;

12.2、MySQL备份

  1. 保证重要数据不丢失
  2. 数据转移

MySQL数据库备份方法:

  1. 直接拷贝物理文件
  2. 在 SQLyog 这种可视化工具中手动导出

MySQL学习笔记

  1. 使用命令行导出 mysqldump
# 导出
mysqldump -h主机 -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
mysqldump -hlocalhost -uroot -p123456 school testmd5 > D:/a.sql

# 导入 在登陆 MySQL 的情况下,使用source d:/a.sql 进行导入
mysql -uroot -p123456
use school #将表导入到 school 数据库下
source d:/a.sql

mysql -uroot -p123456 school < d:/a.sql # 另一种写法

MySQL学习笔记

MySQL学习笔记

13、规范数据库设计

13.1、为什么需要设计

当数据库比较复杂的时候,我们就需要设计了糟糕的数据库设计
糟糕的数据库设计:

  • 数据冗余,浪费空间
  • 数据库插入和删除都会麻烦、异常(屏蔽使用物理外键)
  • 程序的性能差

良好的数据库设计:

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

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

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

设计数据库的步骤:(个人博客)

  • 收集信息,分析需求

    • 用户表(用户登录注销,用户的个人信息,写博客,创建分类)
    • 分类表(文章分类,谁创建的)
    • 文章表(文章的信息)
    • 评论表
    • 友链表(友链信息)
    • 自定义表(系统信息,某个关键的字,或者一些主字段)key : value
    • 说说表(发表心情.. id... content....create_time)
  • 标识实体(把需求落地到每个字段)

  • 标识实体之间的关系

    • 写博客:user --> blog
    • 创建分类:user --> category
    • 关注:user --> user
    • 友联:links
    • 评论:user --> user --> blog

13.2、三大范式

  • 第一范式 (1st NF)

第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式

  • 第二范式(2nd NF)

第二范式是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。

第二范式要求每个表只描述一件事情

  • 第三范式(3rd NF)

如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式。

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

  • 规范化和性能的关系

阿里规范:关联查询的表不得超过三张表

  1. 为满足某种商业目标,数据库性能比规范化数据库更重要(成本,用户体验)
  2. 在数据规范化的同时,要综合考虑数据库的性能
  3. 通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间(增加冗余字段,,多表查询变单表查询)
  4. 通过在给定的表中插入计算列,以方便查询
上一篇:使用反射 和接口 做一个计算器


下一篇:MySQL入门语法(视频学习笔记)