MySQL数据库学习

2022-09-11 14:48:11

1.初识MySQL

1.1数据库的定义

数据库(DataBase,简称DB)，是一个按数据结构来存储和管理数据的计算机软件系统。每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。

概念 : 长期存放在计算机内,有组织,可共享的大量数据的集合,是一个数据"仓库"

作用 : 保存,并能安全管理数据(如:增删改查等),减少冗余...

1.2MySQL简介

MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。

概念 : 是现在流行的开源的,免费的 关系型数据库

历史 : 由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。

特点 :

  • 免费 , 开源数据库
  • 小巧 , 功能齐全
  • 使用便捷
  • 可运行于Windows或Linux操作系统
  • 可适用于中小型甚至大型网站应用

官网 : https://www.mysql.com/

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：

  • 1.数据以表格的形式出现
  • 2.每行为各种记录名称
  • 3.每列为记录名称所对应的数据域
  • 4.许多的行和列组成一张表单
  • 5.若干的表单组成database

1.3关系型数据库

(SQL)

  • MySQL , Oracle , SQL Server , SQLite , DB2 , ...
  • 关系型数据库通过外键关联来建立表与表之间的关系

1.4非关系型数据库

(NOSQL)

  • Redis , MongoDB , ...
  • 非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定

1.5连接数据库

打开MySQL命令窗口

  • 在DOS命令行窗口

  • 在窗口中进入

    安装目录\mysql\bin

    • 可设置环境变量

连接数据库语句 : mysql -h 服务器主机地址 -u 用户名 -p 用户密码

注意 : -p后面不能加空格,否则会被当做密码的内容,导致登录失败 !

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
? 命令关键词 : 寻求帮助
# 表示注释

（1）结构化查询语句SQL

结构化查询语句分类 :

名称 解释 命令
DDL 数据定义语言 定义和管理数据对象，如数据库，数据表等 CREATE、DROP ALTER
DML 数据操作语言 用于操作对象中所包含的数据 INSERT UPDATE DELETE
DQL 数据查询语言 用于查询数据表结构 SELECT
DCL 数据控制语言 用来管理数据库的语言，包括管理权限及数据更改 GRANT COMMIT ROLLBACK

（2）命令行操作数据库

创建数据库 : create database [if not exists] 数据库名;

删除数据库 : drop database [if exists] 数据库名;

查看数据库 : show databases;

使用数据库 : use 数据库名;

CREATE DATABASE java;
DROP DATABASE IF EXISTS java;
CREATE DATABASE IF NOT EXISTS class01;
DROP DATABASE IF EXISTS	class01;
USE `class01` ;
SHOW DATABASES -- 查看所有的数据表

1.6创建数据表

属于DDL的一种

语法 :

create table [if not exists] `表名`(
    ‘字段名1‘ 列类型 [属性][索引][注释],
    ‘字段名2‘ 列类型 [属性][索引][注释],
    #...
    ‘字段名n‘ 列类型 [属性][索引][注释]
)[表类型][表字符集][注释];

说明 : 反引号用于区别MySQL保留字与普通字符而引入的 (键盘esc下面的键).

（1）数据值和列类型

列类型 : 规定数据库中该列存放的数据类型

  1. 数值类型

  2. 字符串类型

  3. 日期和时间型

  4. NULL值

  • 理解为"没有值"或"未知值"
  • 不要用NULL进行算术运算 , 结果仍为NULL

（2）数据字段属性

UnSigned :

  • 无符号的整数
  • 声明该数据列不允许负数 .

ZEROFILL :

  • 0填充的
  • 不足位数的用0来填充 , 如int(3),5则为005

Auto_InCrement :

  • 自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)
  • 通常用于设置主键 index,必须为整数类型。
  • 可定义起始值和步长
    • 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
    • SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)

NULL 和 NOT NULL :

  • 默认为NULL , 即没有插入该列的数值
  • 如果设置为NOT NULL , 则该列必须有值

DEFAULT :

  • 默认的
  • 用于设置默认值
    • 例如,性别字段,默认为"男" , 否则为 "女" ; 若无指定该列的值 , 则默认值为"男"的值
# 创建学生表(列,字段)
# 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
# 创建表之前 , 一定要先选择数据库

CREATE TABLE IF NOT EXISTS `student02` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT ‘学号‘,
  `name` VARCHAR(30) NOT NULL DEFAULT ‘同学‘ 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

# 查看数据库的定义
SHOW CREATE DATABASE school;
# 查看数据表的定义
SHOW CREATE TABLE student;
# 显示表结构
DESC student;
# 设置严格检查模式(不能容错了)
SET sql_mode=‘STRICT_TRANS_TABLES‘;

MySQL的数据表的类型 : MyISAM , InnoDB , HEAP , BOB , CSV等...

常见的 MyISAM 与 InnoDB 类型

名称 MyISAM InnoDB
事务处理 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间大小 较小 较大，约2倍
  • 适用MyISAM : 节约空间及相应速度
  • 适用InnoDB : 安全性 , 事务处理及多用户操作数据表

1.7数据表的存储位置

  • MySQL数据表以文件方式存放在磁盘中
    • 包括表文件 , 数据文件 , 以及数据库的选项文件
    • 位置 : Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表 .
  • 注意 :
    • InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的.ibd文件，
    • MyISAM类型数据表对应三个文件 :
      • * . frm -- 表结构定义文件
      • * . MYD -- 数据文件(data)
      • * . MYI -- 索引文件(index)

1.8修改数据库

(1)修改表( ALTER TABLE )

  • 修改表名 :

    • ALTER TABLE 旧表名 RENAME AS 新表名

  • 添加字段 :

    • ALTER TABLE 表名 ADD 字段名 列属性[属性]

  • 修改字段 :

    • ALTER TABLE 表名 MODIFY 字段名 列类型[属性]

    • ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]

  • 删除字段 :

    • ALTER TABLE 表名 DROP 字段名
-- 修改表名 `ALTER TABLE 旧表名 RENAME AS 新表名`

ALTER TABLE student03 RENAME AS 03student;

-- 增加字段 ALTER TABLE 表名 ADD字段名 列属性[属性]

ALTER TABLE 03student ADD age INT(2);

-- 修改表的字段01 ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE 03student MODIFY age VARCHAR(4);-- 修改约束

-- 修改表的字段02 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
ALTER TABLE 03student CHANGE age setage INT(2); -- 字段重命名

-- 删除字段 ALTER TABLE 表名 DROP 字段名
ALTER TABLE 03student DROP setage;

(2)删除数据表

  • 语法 :

    DROP TABLE [IF EXISTS] 表名

    • IF EXISTS为可选 , 判断是否存在该数据表
    • 如删除不存在的数据表会抛出错误

小结：

1. 可用反引号（`）为标识符（库名、表名、字段名、索引、别名）包裹，以避免与关键字重名！中文也可以作为标识符！
2. 每个库目录存在一个保存当前数据库的选项文件db.opt。
3. 注释：
    单行注释 # 注释内容
    多行注释 /* 注释内容 */
    单行注释 -- 注释内容        (标准SQL注释风格，要求双破折号后加一空格符（空格、TAB、换行等）)
4. 模式通配符：
    _    任意单个字符
    %    任意多个字符，甚至包括零字符
    单引号需要进行转义 \‘
5. CMD命令行内的语句结束符可以为 ";", "\G", "\g"，仅影响显示结果。其他地方还是用分号结束。delimiter 可修改当前对话的语句结束符。
6. SQL对大小写不敏感
7. 清除已有语句：\c

2.MySQL数据管理

2.1 外键管理（物理外键）

外键概念

　　如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。(引用别人的表：从表 ;被引用的表：主表)

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

外键作用

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

创建外键

方式一：在创建表的时候添加外键

-- 创建年级表
CREATE TABLE `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`
-- 定义外键key
-- 给这个外键添加约束（执行引用）

-- 创建学生表
CREATE TABLE `person` (
  `studentid` INT(4) NOT NULL COMMENT ‘学号‘, -- comment 注释，备注
  `studentname` VARCHAR(20) NOT NULL DEFAULT ‘学生姓名‘ COMMENT ‘姓名‘,
  `sex` TINYINT(1) DEFAULT ‘1‘ COMMENT ‘性别‘, -- 微整型
  `gradeid` INT(10) NOT NULL COMMENT ‘学生的年级‘,
  PRIMARY KEY (`studentid`), -- 设置该表主键
  KEY `FK_gradeid` (`gradeid`), -- 添加外键，`FK_gradeid`（FOREIGN KEY） 连接两个表之间的关系
  -- constraint 添加约束条件
  -- FOREIGN 外来的 ， FOREIGN KEY（字段）
  -- REFERENCES 引用，REFERENCES `表名`（字段名）
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`) 
) ENGINE=INNODB DEFAULT CHARSET=utf8

方式二：创建表时没有外键，通过修改表添加外键

ALTER TABLE `person`
ADD CONSTRAINT `FK_gradeid`FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`) 
-- 代码过长可用回车换行，单行执行可不在句尾添加;

ALTER TABLE 表名 
ADD CONSTRAINT 约束名 ADD CONSTRAINT（作为外键的列）REFERENCES 被引用的表名（字段名）;

备注：被引用的表不能直接删除，要先删除引用它的表才能正确删除

引用别人的表：从表

被引用的表：主表

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

以上两种方式都是物理外键，数据库级别的外键，一般不建议使用，避免数据库过多造成困扰

最佳实现：

 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）

? 我们想使用多张表的数据，想使用外键（通过程序去实现）

2.2 数据库数据管理

数据库意义

  • 数据存储
  • 数据管理

管理数据库数据方法

  • 通过SQLyog等管理工具管理数据库数据
  • 通过DML语句管理数据库数据

DML语言

  • DML(数据操作语言)
  • 用于操作数据库对象中所包含的数据
  • 包括 :
    • INSERT (添加数据语句)
    • UPDATE (更新数据语句)
    • DELETE (删除数据语句)

添加数据

INSERT命令

  • 语法 :
  INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES(（‘值1‘）,（‘值2‘）,（‘值3‘）,...)
/*
注意：
- 字段或值之间用英文逗号隔开.
- ‘字段1,字段2...‘该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致.
- 可同时插入多条数据 , values 后用英文逗号隔开.
*/

创建一个表：

-- DML(数据操作语言) 练习
-- 创建课程表
CREATE TABLE `subject` (
  `subjectid` INT(10) NOT NULL AUTO_INCREMENT COMMENT ‘课程序号‘,
  `subjectname` VARCHAR(50) NOT NULL COMMENT ‘课程名称‘,
  PRIMARY KEY (`subjectid`) -- 设置主键
) ENGINE=INNODB DEFAULT CHARSET=utf8


-- 插入一个字段
INSERT INTO `subject`(subjectname) VALUES (‘java‘);

-- 一个字段插入多个值
INSERT INTO `subject`(subjectname) VALUES (‘c语言‘),(‘HTML5‘);

-- 为多个列同时插入字段
INSERT INTO `subject`(`subjectname`,`subjectrank`,`subjectstar`) VALUES(‘java‘,‘hard‘,‘8‘)

-- 为多个列插入多个字段
INSERT INTO `subject`(`subjectname`,`subjectrank`,`subjectstar`) 
VALUES(‘java‘,‘hard‘,‘8‘),(‘C++‘,‘just‘,‘6‘),(‘HTML5‘,‘easy‘,‘4‘)

错误收藏栏：

-- 由于主键自增，我们可以省略（如果不写表的字段，它就会一一匹配）
INSERT INTO `subject` VALUES (‘c++‘);

1 queries executed, 0 success, 1 errors, 0 warnings

查询：INSERT INTO `subject` VALUES (‘c++‘)

错误代码： 1136
Column count doesn‘t match value count at row 1

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0 sec

修改数据

UPDATE命令

  • 语法 :
  UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];

注意 :

  • column_name 为要更改的数据列
  • value 为修改后的数据 , 可以为变量 , 具体指 , 表达式或者嵌套的SELECT结果
  • condition 为筛选条件 , 如不指定则修改该表的所有列数据
update 修改谁 (条件) set 原来的值=新值

-- 修改`subjectname`
UPDATE `subject` SET `subjectname`=‘计科课表‘WHERE `subjectid`=1;

-- 不指定条件下会改变所有表
UPDATE `subject` SET `subjectname`=‘新思路‘

-- 修改多个属性，用逗号,隔开
UPDATE `subject` SET `subjectname`=‘java‘,`subjectrank`=‘hard‘,`subjectstar`=‘8‘WHERE `subjectid`=2;


-- 通过多个条件定位数据
UPDATE `subject` SET `subjectname`=‘java‘WHERE `subjectrank`=‘hard‘AND `subjectstar`=‘8‘;

修改数据：

-- DML(数据操作语言) 练习
-- 创建课程表
CREATE TABLE `subject` (
  `subjectid` INT(10) NOT NULL AUTO_INCREMENT COMMENT ‘课程序号‘,
  `subjectname` VARCHAR(50) NOT NULL COMMENT ‘课程名称‘,
  `subjectrank`VARCHAR(50) NOT NULL COMMENT ‘课程难度‘,
  `subjectstar`INT(10) NOT NULL COMMENT ‘课程评分‘,
  PRIMARY KEY (`subjectid`) -- 设置主键
) ENGINE=INNODB DEFAULT CHARSET=utf8
UPDATE `subject` SET `subjectname`=‘计科课表‘WHERE `subjectid`=1;
-- 不指定条件下会改变所有表
UPDATE `subject` SET `subjectname`=‘新思路‘
-- 修改多个属性，用逗号,隔开
UPDATE `subject` SET `subjectname`=‘java‘,`subjectrank`=‘hard‘,`subjectstar`=‘8‘WHERE `subjectid`=2;
-- 通过多个条件定位数据
UPDATE `subject` SET `subjectname`=‘java‘WHERE `subjectrank`=‘hard‘AND `subjectstar`=‘8‘;

删除数据

DELETE命令

  • 语法

    DELETE FROM 表名 [WHERE condition];

注意 : condition为筛选条件 , 如不指定则删除该表的所有列数据

TRUNCATE命令

  • 用于完全清空表数据 , 但表结构 , 索引 , 约束等不变 ;
  • 语法 : TRUNCATE [TABLE] table_name;

注意 : 区别于DELETE命令

  • 相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
  • 不同 :
    • 使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器
    • 使用TRUNCATE TABLE不会对事务有影响
-- 1.删除全部数据
DELETE FROM `subject` --不会影响自增
-- 2.删除指定数据
DELETE FROM `subject`WHERE `subjectid`=1;

-- TRUNCATE
截断表;截除;删除记录;语句
-- 清空 `subject` 表
TRUNCATE `subject` --自增会归零

3.Mysql：使用DQL查询数据

3.1DQL语言

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

  • 查询数据库数据 , 如SELECT语句
  • 简单的单表查询或多表的复杂查询和嵌套查询
  • 是数据库语言中最核心,最重要的语句
  • 使用频率最高的语句
-- 创建学生信息表
DROP TABLE IF EXISTS `student_table`;
CREATE TABLE `student_table` (
`id`
BIGINT(64) NOT NULL AUTO_INCREMENT COMMENT ‘id‘,
`number`
VARCHAR(20) DEFAULT NULL COMMENT ‘学生编号‘,
`name`
VARCHAR(20) DEFAULT NULL COMMENT ‘学生姓名‘,
`birthday` VARCHAR(20) DEFAULT NULL COMMENT ‘出生年月日‘,
`sex`
INT(1) DEFAULT NULL COMMENT ‘性别(0 男 1 女 2 未知)‘,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT=‘学生信息表‘;

INSERT INTO student_table(number,NAME,birthday,sex) VALUES(‘B190901‘,‘小王
‘,‘1995-01-01‘,0);
INSERT INTO student_table(number,NAME,birthday,sex) VALUES(‘B190902‘,‘小张
‘,‘1994-03-06‘,1);
INSERT INTO student_table(number,NAME,birthday,sex) VALUES(‘B190903‘,‘小李
‘,‘1995-02-07‘,1);
INSERT INTO student_table(number,NAME,birthday,sex) VALUES(‘B190904‘,‘小红
‘,‘1997-08-22‘,0);

-- 创建课程信息表
DROP TABLE IF EXISTS `course_table`;
CREATE TABLE `course_table` (
`id` BIGINT(64) NOT NULL AUTO_INCREMENT COMMENT ‘id‘,
`number` VARCHAR(20) DEFAULT NULL COMMENT ‘课程编号‘,
`name` VARCHAR(20) DEFAULT NULL COMMENT ‘课程名称‘,
`teacherNumber` VARCHAR(20) DEFAULT NULL COMMENT ‘教师编号‘,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT=‘课程信息表‘;

INSERT INTO course_table(number,NAME,teacherNumber) VALUES(‘100‘,‘英语‘,‘T001‘);
INSERT INTO course_table(number,NAME,teacherNumber) VALUES(‘101‘,‘高数‘,‘T002‘);
INSERT INTO course_table(number,NAME,teacherNumber) VALUES(‘102‘,‘思修‘,‘T003‘);

-- 创建成绩信息表
DROP TABLE IF EXISTS `score_table`;
CREATE TABLE `score_table` (
`id`
BIGINT(64) NOT NULL AUTO_INCREMENT COMMENT ‘id‘,
`st_number` VARCHAR(20) DEFAULT NULL COMMENT ‘学生编号‘,
`co_number` VARCHAR(20) DEFAULT NULL COMMENT ‘课程编号‘,
`score`
INT(20) DEFAULT NULL COMMENT ‘课程分数‘,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT=‘成绩信息表‘;
INSERT INTO score_table(st_number,co_number,score) VALUES(‘B190901‘,‘100‘,80);
INSERT INTO score_table(st_number,co_number,score) VALUES(‘B190901‘,‘101‘,90);
INSERT INTO score_table(st_number,co_number,score) VALUES(‘B190902‘,‘100‘,95);
INSERT INTO score_table(st_number,co_number,score) VALUES(‘B190902‘,‘101‘,60);
INSERT INTO score_table(st_number,co_number,score) VALUES(‘B190902‘,‘102‘,70);
INSERT INTO score_table(st_number,co_number,score) VALUES(‘B190903‘,‘100‘,90);
INSERT INTO score_table(st_number,co_number,score) VALUES(‘B190903‘,‘101‘,79);
INSERT INTO score_table(st_number,co_number,score) VALUES(‘B190903‘,‘102‘,85);
INSERT INTO score_table(st_number,co_number,score) VALUES(‘B190904‘,‘100‘,60);
INSERT INTO score_table(st_number,co_number,score) VALUES(‘B190904‘,‘101‘,70);

-- 创建教师信息表
DROP TABLE IF EXISTS `teacher_table`;
CREATE TABLE `teacher_table` (
`id` BIGINT(64) NOT NULL AUTO_INCREMENT COMMENT ‘id‘,
`number` VARCHAR(20) DEFAULT NULL COMMENT ‘教师编号‘,
`name` VARCHAR(20) DEFAULT NULL COMMENT ‘教师姓名‘,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT=‘教师信息表‘;

INSERT INTO teacher_table(number,NAME) VALUES(‘T001‘,‘张三‘);
INSERT INTO teacher_table(number,NAME) VALUES(‘T002‘,‘张四‘);
INSERT INTO teacher_table(number,NAME) VALUES(‘T003‘,‘王五‘);

指定查询字段

查询表结果时 , 可指定查询结果的数据列

  • 查询表中所有的数据列结果 , 采用 " * "符号;
    • 如 : SELECT * FROM student;
    • 但是效率低 , 不推荐 。
-- 指定查询字段
-- 查询全部的学生 
SELECT * FROM `student_table`

-- 查询指定字段
SELECT `number`,`name`,`birthday` FROM `student_table`

AS子句作为别名

AS子句作用 :

  • 可给数据列取一个新别名
  • 可给表去一个新别名
  • 可把经计算或总结的结果用另一个新名称来代替
-- 给结果起别名 AS 可以给字段起别名，也可以给表起别名
SELECT `number` AS ‘学生学号‘ ,`name` AS 学生姓名,`birthday`AS 出生日期 FROM `student_table`AS 学生信息表

经我验证，发现 AS 别名可以加单引号，也可以不加.

-- 函数 Concat(a,b)
-- 单个拼接
SELECT CONCAT(‘姓名: ‘,`name`)AS 学生姓名 FROM `student_table`
-- 多个拼接
SELECT CONCAT(‘姓名: ‘,`name`)AS 学生姓名,CONCAT(‘学号: ‘,`number`) AS 学生信息 FROM `student_table`

SQL练习：

  • 查询出每门课都大于等于 80 分的学生学号和姓名？
-- 查询出每门课都大于等于 80 分的学生学号和姓名
SELECT a.number,a.name
FROM student_table a
LEFT JOIN score_table AS b ON b.st_number=a.number
GROUP BY b.st_number HAVING MIN(b.score)>=80;
  • 查询课程英语的成绩比课程高数的成绩高的所有学生的学号？
-- 查询课程英语的成绩比课程高数的成绩高的所有学生的学号
SELECT a.st_number FROM(SELECT st_number,score FROM score_table WHERE co_number=‘100‘) a,
(SELECT st_number,score FROM score_table WHERE co_number=‘101‘) b
WHERE a.score>b.score AND a.st_number=b.st_number;
  • 查询平均成绩大于 60 分的同学的学号和平均成绩？
-- 查询平均成绩大于 60 分的同学的学号和平均成绩
SELECT a.st_number AS ‘学号‘,AVG(a.score) AS ‘平均成绩‘
FROM (SELECT st_number,score FROM score_table) a
GROUP BY a.st_number HAVING AVG(a.score)>60;
  • 查询所有同学的学号、姓名、选课数、总成绩 ？
-- 查询所有同学的学号、姓名、选课数、总成绩
SELECT a.st_number AS 学号, b.name AS 姓名,
COUNT(a.co_number) AS 选课数, SUM(a.score) AS 总成绩
FROM score_table a,student_table b
WHERE a.st_number = b.number
GROUP BY a.st_number, b.name
  • 查询姓“张”的老师的个数？
-- 查询姓“张”的老师的个数
SELECT COUNT(*) FROM teacher_table WHERE NAME LIKE ‘张%‘;
  • 查询同时学过英语课程和思修课程的同学的学号、姓名？
-- 查询同时学过英语课程和思修课程的同学的学号、姓名
SELECT number, NAME FROM student_table
WHERE number IN (SELECT st_number FROM score_table sc WHERE sc.co_number =100)
AND number IN (SELECT st_number FROM score_table sc WHERE sc.co_number =102);
  • 查询学过”王五“老师所教课程的所有同学的学号、姓名？
-- 查询学过”王五“老师所教课程的所有同学的学号、姓名
SELECT a.number,a.name FROM student_table a,score_table b
WHERE a.number = b.st_number AND b.co_number
IN(SELECT c.number FROM course_table c,teacher_table d WHERE c.teacherNumber = d.number AND
d.name =‘王五‘);
  • 查询各科成绩最高分和最低分？
-- 查询各科成绩最高分和最低分
SELECT ct.name AS 课程, MAX(st.score) AS 最高分,MIN(st.score) 最低分
FROM score_table st
LEFT JOIN course_table ct ON ct.number=st.co_number
GROUP BY st.co_number;

去重distinct

--distinct:去除SELECT查询出来的结果中重复的数据，重复的数据只显示一条
-- 查询全部的学生 
SELECT * FROM `student_table`
-- 查询全部的成绩
SELECT *FROM `score_table`
-- 查询有哪些同学参加了考试
SELECT `st_number` FROM `score_table`
-- 去掉重复项
SELECT DISTINCT `st_number` FROM `score_table`

数据库中的列（表达式）

-- 数据库的列（表达式）
SELECT VERSION() -- 查询系统版本（函数） -- 5.7.19
SELECT 100*3-1 AS 计算结果 -- 用来计算（表达式） -- 299
SELECT @@auto_increment_increment -- 查询自增的步长（变量）-- 1
-- 学生考试成绩+1分查看
SELECT `st_number`,`score` +1 AS ‘学生提分后成绩‘ FROM `score_table`
-- 数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量...
-- select 表达式 from 表

3.2where条件语句

  • 用于检索数据表中 符合条件 的记录

  • 搜索条件可由一个或多个逻辑表达式组成 , 结果一般为真或假.

  • 搜索条件的组成

    • 逻辑操作符

    ? MySQL数据库学习

    • 比较操作符
    • MySQL数据库学习
    • 用于模糊查询 .

  • 注意 :

    • 数值数据类型的记录之间才能进行算术运算;
    • 相同数据类型的数据之间才能进行比较;
  
  -- 查询学生成绩
  SELECT `st_number`,`score`FROM `score_table`
  -- 查询成绩在60-80的学生
  SELECT `st_number`,`score`FROM `score_table`
  WHERE `score`>=60 AND `score`<=80
  -- 等价
  SELECT `st_number`,`score`FROM `score_table`
  WHERE `score`>=60 && `score`<=80
  -- 模糊查询（区间）
  SELECT `st_number`,`score`FROM `score_table`
  WHERE `score` BETWEEN 75 AND 100
  
  -- 除了`st_number`= B190903 号学生之外的同学的成绩
  SELECT `st_number`,`score`FROM `score_table`
  WHERE NOT `st_number`= ‘B190903‘

模糊查询

-- 模糊查询 

-- 查询姓新的同学
SELECT `number`,`name`FROM`student_table`
WHERE `name` LIKE ‘新%‘
-- like结合使用的通配符：%（代表0~任意个字符） _(代表一个字符)
-- 查询姓新的同学后面只有一个字的
SELECT `number`,`name`FROM`student_table`
WHERE `name` LIKE ‘新_‘
-- 查询姓新的同学后面只有两个字的
SELECT `number`,`name`FROM`student_table`
WHERE `name` LIKE ‘新__‘
-- 查询名字中含有新字的
SELECT `number`,`name`FROM`student_table`
WHERE `name` LIKE ‘%新%‘
-- 查询学号为 B190903，B190904，B190905的学生姓名
SELECT `number`,`name`FROM`student_table`
WHERE `number`IN(‘B190903‘,‘B190904‘,‘B190905‘)
-- 查询未知性别的同学
SELECT `number`,`name`,`sex`FROM`student_table`
WHERE `sex`IN(‘2‘)
-- 查询生日日期没有填写的同学
SELECT `name`FROM `student_table`
WHERE `birthday` IS NULL

-- 查询生日日期没有填写的同学
SELECT `name`FROM `student_table`
WHERE `birthday`=‘‘OR`birthday`IS NULL

-- 查询生日日期已经填写的同学
SELECT `name`FROM `student_table`
WHERE `birthday` IS NOT NULL

连接查询

join 对比

联表查询

连接查询
    如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
内连接 inner join
    查询两个表中的结果集中的交集
外连接 outer join
    左外连接 left join
        (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
    右外连接 right join
        (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
        
等值连接和非等值连接

#查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;

/*思路:
(1):分析需求,确定查询的列来源于两个类,student  result,连接查询
(2):确定使用哪种连接查询?(内连接)
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno

# 右连接(也可实现)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno

# 等值连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno

# 左连接 (查询了所有同学,不考试的也会查出来)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno

#查一下缺考的同学(左连接应用场景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL

#思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

来自网络截图:
MySQL数据库学习

操作符名称 功能描述
INNER JOIN
LEFT JOIN
RIGHT JOIN 
Column ‘StudentNo‘ in field list is ambiguous
字段列表中的“StudentNo”列不明确

-- 联表查询练习
-- 查询参加了考试的同学（姓名，学号，科目编号，成绩）

SELECT *FROM`student`
SELECT *FROM `result`
/*
1.分析需求，分析查询的字段来自哪些表
2.确定使用哪种连接查询（7种）
3.确定交集（数据相同）
4.判断的条件 学生表中的 `StudentNo`= 成绩表中的 `StudentNo`
*/
-- join 连接的表 on (判断的条件) 连接查询
-- where 等值查询
SELECT s.`StudentNo`, `StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS s		-- 两张表都有`StudentNo`,为避免模棱两可，取别名加以区分
INNER JOIN `result` AS r
-- 添加判断条件，通过共有的`StudentNo`建立连接关系
WHERE s.`StudentNo`=r.`StudentNo`

-- right join=============
SELECT s.`StudentNo`, `StudentName`,`SubjectNo`,`StudentResult`
FROM `student` s    -- 取别名AS可以省略，空格分隔
RIGHT JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`

-- lift join=============
SELECT s.`StudentNo`, `StudentName`,`SubjectNo`,`StudentResult`
FROM `student` s    -- 取别名AS可以省略，空格分隔
LEFT JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`

自连接

categoryid	pid	categoryName
2	1	信息技术
3	1	软件开发
4	3	数据库
5	1	美术设计
6	3	web开发
7	5	ps技术
8	2	办公信息

父类

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

子类

pid categoryid categoryName
3 4 数据库
3 6 web开发
5 7 ps技术
2 8 办公信息
父类 子类
信息技术 办公信息
软件开发 数据库
软件开发 web开发
美术设计 ps技术 
# 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
# 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)

SELECT a.categoryName AS ‘父栏目‘,b.categoryName AS ‘子栏目‘
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

/*
自连接
    数据表与自身进行连接

需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中 
     查询父栏目名称和其他子栏目名称
*/

# 创建一个表
-- categoryid 类别
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‘,‘办公信息‘);

# 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
# 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)

SELECT a.categoryName AS ‘父栏目‘,b.categoryName AS ‘子栏目‘
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

#思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

# 查询学员及所属的年级(学号,学生姓名,年级名)
SELECT studentno AS 学号,studentname AS 学生姓名,gradename AS 年级名称
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`

# 查询科目及所属的年级(科目名称,年级名称)
SELECT subjectname AS 科目名称,gradename AS 年级名称
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 r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname=‘数据库结构-1‘

排序和分页

/*============== 排序 ================
语法 : ORDER BY --以...排序
    ORDER BY 语句用于根据指定的列对结果集进行排序。
    ORDER BY 语句默认按照ASC升序对记录进行排序。  -- 升序 ascending order
    如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。 --降序 descending order 
*/

# 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
# 按成绩降序排序
# 按成绩升序排序
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname=‘数据库结构-1‘
#ORDER BY StudentResult DESC , studentno
ORDER BY StudentResult ASC , studentno
/*============== 分页 ================
语法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
好处 : (用户体验,网络传输,查询压力)
推导: 
    第一页 : limit 0,5
    第二页 : limit 5,5
    第三页 : limit 10,5
    ......
    第N页 : limit (pageNo-1)*pageSzie,pageSzie
    [pageNo:页码,pageSize:单页面显示条数]
    
*/

# 每页显示5条数据
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname=‘C语言-1‘
ORDER BY StudentResult DESC , studentno
LIMIT 0,5
-- LIMIT 5,5

#查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname=‘JAVA第一学年‘AND`StudentResult`>80 -- and `StudentResult`<=100
ORDER BY StudentResult DESC
LIMIT 0,10

子查询

/*============== 子查询 ================
什么是子查询?
    在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
    嵌套查询可由多个子查询组成,求解的方式是由里及外;
    子查询返回的结果一般都是集合,故而建议使用IN关键字;
*/

# 查询 数据库结构-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;

#方法二:使用子查询(执行顺序:由里及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
    SELECT subjectno FROM `subject` 
    WHERE subjectname = ‘数据库结构-1‘
)
ORDER BY studentresult DESC;

#查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名

#方法一:使用连接查询
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 subjectname = ‘高等数学-2‘ AND StudentResult>=80

#方法二:使用连接查询+子查询
#分数不小于80分的学生的学号和姓名
SELECT r.studentno,studentname
FROM student s
INNER JOIN result r 
ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80

#在上面SQL基础上,添加需求:课程为 高等数学-2
SELECT r.studentno,studentname 
FROM student s
INNER JOIN result r 
ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
    SELECT subjectno FROM `subject`
    WHERE subjectname = ‘高等数学-2‘
)
#分步写简单sql语句,然后将其嵌套起来
SELECT studentno,studentname FROM student WHERE studentno IN(
    SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
        SELECT subjectno FROM `subject` WHERE subjectname = ‘高等数学-2‘
    )
)
#方法三:使用子查询


/*
练习题目:
    查 C语言-1 的前5名学生的成绩信息(学号,姓名,分数)
    使用子查询,查询郭靖同学所在的年级名称
*/

4.Mysql：MySQL函数

官方文档 : https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

常用函数

分类: 数学函数 , 字符串函数 , 日期和时间函数 , 系统信息函数

# 数学函数 (这里只列出一些常用的)

SELECT ABS(-8);  /*绝对值*/
-- absolute value
SELECT CEILING(9.4);  /*向上取整*/   
-- 天花板 上限 上升限度
SELECT FLOOR(9.4);  /*向下取整*/
-- 地板 楼层 *体操
SELECT RAND();  /*随机数,返回一个0-1之间的随机数*/
-- 产生均匀分布随机数
SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/
-- 符号
SELECT SIGN(-4); 
SELECT SIGN(8); 

#字符串函数

SELECT CHAR_LENGTH(‘新思路团队都是大佬‘); /*返回字符串包含的字符数*/

SELECT CONCAT(‘我‘,‘相信‘,‘自己‘);  /*合并字符串,参数可以有多个*/

SELECT INSERT(‘我想进新思路‘,2,1,‘一定努力‘);  /*替换字符串,从某个位置开始替换某个长度*/

SELECT LOWER(‘WANWAN‘); /*小写*/

SELECT UPPER(‘wanwan‘); /*大写*/

SELECT LEFT(‘hello,world‘,5);  /*从左边截取*/

SELECT RIGHT(‘hello,world‘,5);  /*从右边截取*/

SELECT REPLACE(‘在新思路能学习到很多东西‘,‘很多东西‘,‘新知识‘);  /*替换字符串*/

SELECT SUBSTR(‘新思路是一个很棒的团队‘,4,8); /*截取字符串,开始和长度*/

SELECT REVERSE(‘Java EE‘); /*反转，逆向*/


#日期和时间函数

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 VERSION();  /*版本*/
SELECT USER();  /*用户*/


# 查询姓李的同学,改成立
SELECT REPLACE(studentname,‘李‘,‘立‘) AS 新名字
FROM student WHERE studentname LIKE ‘李%‘;

聚合函数

函数名称 描述
count() 计数
sum() 求和
vag() 求平均值
max() 求最大值
min() 求最小值 
#聚合函数

/*COUNT:非空的*/
SELECT COUNT(studentname) FROM student;
-- 返回记录的总数
SELECT COUNT(*) FROM student;
-- 效率低，不建议使用
SELECT COUNT(1) FROM student; 
-- 推荐
SELECT SUM(StudentResult) AS 总和 FROM result;
-- 求一列的总和
SELECT AVG(StudentResult) AS 平均分 FROM result;
-- 求一列的平均分
SELECT MAX(StudentResult) AS 最高分 FROM result;
-- 求一列的最高分
SELECT MIN(StudentResult) AS 最低分 FROM result;
-- 求一列的最低分

# 查询不同课程的平均分,最高分,最低分
# 前提:根据不同的课程进行分组

SELECT subjectname,AVG(studentresult) 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..
*/

count分析

执行效果：
1.  count(1) and count(*)
当表的数据量大些时，对表作分析之后，使用count(1)还要比使用count(*)用时多了！ 
从执行计划来看，count(1)和count(*)的效果是一样的。 但是在表做过分析之后，count(1)会比count(*)的用时少些（1w以内数据量），不过差不了多少。 
 
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。 
因为count(*),自动会优化指定到那一个字段。所以没必要去count(1)，用count(*)，sql会帮你完成优化的 因此：count(1)和count(*)基本没有差别！ 
 
2. count(1) and count(字段)
两者的主要区别是
（1） count(1) 会统计表中的所有的记录数，包含字段为null 的记录。
（2） count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。 

3. count(*) 和 count(1)和count(列名)区别  
执行效果上：  
count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL  
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL  
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。
执行效率上：  
列名为主键，count(列名)会比count(1)快  
列名不为主键，count(1)会比count(列名)快  
如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）  
如果有主键，则 select count（主键）的执行效率是最优的  
如果表只有一个字段，则 select count（*）最优。

MD5 加密

MD5 百度百科:https://baike.baidu.com/item/MD5/212708?fr=aladdin

简介：

MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。

原理：

MD5算法的原理可简要的叙述为：MD5码以512位分组来处理输入的信息，且每一分组又被划分为16个32位子分组，经过了一系列的处理后，算法的输出由四个32位分组组成，将这四个32位分组级联后将生成一个128位散列值。

总体流程如下图所示，每次的运算都由前一轮的128位结果值和当前的512bit值进行运算
MySQL数据库学习

sql:MD5测试

CREATE TABLE testMD5(
`id` INT (6)NOT NULL AUTO_INCREMENT  COMMENT ‘序号‘,
`name` VARCHAR (4) NOT NULL DEFAULT ‘姓名‘COMMENT‘姓名测试‘,
`age` INT(3) DEFAULT NULL,
`birthday` VARCHAR (60)NOT NULL DEFAULT ‘时间‘COMMENT‘出生日期‘,
`password`VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)

)ENGINE = INNODB DEFAULT CHARSET =utf8

DROP TABLE`testmd5`

INSERT INTO `testmd5` VALUE(1,‘张三‘,‘21‘,‘1999-04-23‘,‘123456‘)
INSERT INTO `testmd5` VALUE(2,‘李四‘,‘22‘,‘1998-03-13‘,‘321431‘)
INSERT INTO `testmd5` VALUE(3,‘王五‘,‘23‘,‘1997-06-18‘,‘425633‘)
INSERT INTO `testmd5` VALUE(4,‘小张‘,‘20‘,‘2000-09-13‘,‘327268‘)
INSERT INTO `testmd5` VALUE(5,‘小李‘,‘20‘,‘2001-12-13‘,‘526825‘)
INSERT INTO `testmd5` VALUE(6,‘小王‘,‘20‘,‘2001-12-13‘,MD5(‘424145‘))
-- 加密`password`
UPDATE `testmd5`SET `password`=MD5(`password`)WHERE `id`=1
UPDATE `testmd5`SET `password`=MD5(`password`)
-- 加密全部`birthday`
UPDATE `testmd5`SET `birthday`=MD5(`birthday`)
UPDATE `testmd5`SET `birthday`=MD5(`birthday`)WHERE `name`=‘小张‘
-- 
-- UPDATE `testmd5`SET `testmd5`=MD5(`testmd5`)
-- > Unknown column ‘testmd5‘ in ‘field list‘-->“字段列表”中的未知列“testmd5”

-- 如何校正？ 将用户传来的信息进行MD5加密，然后比对加密后的值
SELECT * FROM `testmd5`WHERE `name`=‘小张‘AND`age`=‘20‘AND`birthday`=‘2000-09-13‘AND`password`=‘327268‘

SELECT * FROM `testmd5`WHERE `name`=‘张三‘AND`birthday`=‘1999-04-23‘

SELECT * FROM `testmd5`WHERE `name`=‘李四‘AND`birthday`=‘1999-04-23‘

mysql之自定义函数

什么是函数：

函数存储着一系列SQL语句，调用函数就是一次性执行这些语句。所以函数可以降低语句重复。
但注意的是函数注重返回值，不注重执行过程，所以一些语句无法执行。所以函数并不是单纯的SQL语句集合。

函数与存储过程的区别：函数只会返回一个值，不允许返回一个结果集。函数强调返回值，所以函数不允许返回多个值的情况，即使是查询语句。

5.事务ACID理解

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

CSDN:https://blog.csdn.net/weixin_42181824/article/details/82261988

MySQL事务

  • 事务就是将一组SQL语句放在同一批次内去执行
  • 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
  • MySQL事务处理只支持InnoDB和BDB数据表类型

事务的ACID原则

原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性（Consistency）
事务前后数据的完整性必须保持一致。

持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。（如果隔离失败，则会失败，导致产生 脏读，不可重复读，幻读等）

事务的理解：模拟转账

-- =========事务的理解========

-- 1.创建一个数据库
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci; 
USE `shop`; 
-- 2.创建一个`account`表
CREATE TABLE `account` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL,
  `cash` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 3. 添加数据
INSERT INTO `account`(`name`,`cash`)
VALUES (‘A‘,800),(‘B‘,200)
-- 4.模拟转账事务
SET autocommit = 0  -- 关闭自动提交
START TRANSACTION -- 开启一组事务
-- 5.开始转账 A 转账200给B
UPDATE `account` SET `cash`=`cash`-200 WHERE `name`=‘A‘
UPDATE `account` SET `cash`=`cash`+200 WHERE `name`=‘B‘
-- 6. 提交与回滚
COMMIT; -- 提交事务
ROLLBACK; -- 回滚
-- 7.恢复默认值
SET autocommit = 1 ;-- 恢复默认值

补充说明

-- =========事务的理解========

-- mysql 是默认开启事务提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启
-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 开启事务
START TRANSACTION -- 标记这个事务的开始，从这以后的sql语句都在同一个事务中
INSERT **
INSERT **
-- 提交，持久化（成功）
COMMIT;
-- 回滚，回到原来的样子（失败）
ROLLBACK;
-- 事务结束
SET autocommit = 1 -- 开启自动提交

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

数据库索引

推荐博文:https://www.cnblogs.com/chenshishuo/p/5030029.html

作用 :

  • 提高查询速度
  • 确保数据的唯一性
  • 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
  • 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
  • 全文检索字段进行搜索优化.

分类 :

  • 主键索引 (Primary Key)

    唯一标识，不可重复，只能有一个列作为主键

  • 唯一索引 (Unique)

    避免出现重复列，唯一索引可以重复，多个列都可以标识唯一索引

  • 常规索引 (Index/key)

    默认的是index，key关键字可以设置

  • 全文索引 (FullText)

    在特定的数据库引擎下才有，可实现快速定位数据

作用 : 快速定位特定数据
注意 :
只能用于MyISAM类型的数据表
只能用于CHAR , VARCHAR , TEXT数据列类型
适合大型数据集

索引简介

-- ========索引练习=========
-- 1.在创建表的时候使用
-- 2.创建完表后增加索引
-- 3.创建完表后修改索引

-- 显示所有的索引信息
SHOW INDEX FROM `student_table`
-- 增加一个全文索引 （索引名）列名
ALTER TABLE `indextest`.`student_table`ADD FULLTEXT INDEX `name`(`name`);
-- EXPLAIN 分析sql执行得情况
EXPLAIN SELECT * FROM `student_table`; -- 全文索引
EXPLAIN SELECT * FROM `student_table` WHERE MATCH (`name`)AGAINST(‘李‘)

推荐博客 【MySQL优化】——看懂explain ：https://blog.csdn.net/jiadajing267/article/details/81269067?ops_request_misc=%7B%22request%5Fid%22%3A%22158330065319725211909587%22%2C%22scm%22%3A%2220140713.130056874..%22%7D&request_id=158330065319725211909587&biz_id=0&utm_source=distribute.pc_search_result.none-task

索引准则

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表建议不要加索引
  • 索引一般应加在查找条件的字段

用户管理

-- 创建用户  CREATE USER 用户名 IDENTIFIED BY 密码

CREATE USER ZhengWanfu IDENTIFIED BY ‘123456‘

-- 修改密码(修改当前用户密码)->root

SET PASSWORD = ‘654321‘

-- 修改密码（修改指定用户密码）

SET PASSWORD FOR ZhengWanfu =PASSWORD(‘654321‘)

-- 重命名 

RENAME USER ZhengWanfu TO ZhengWanfu2

-- 用户授权 ALL PRIVILEGES 全部的权限 除了GRANT（给别人授权）

GRANT ALL PRIVILEGES ON *.* TO ZhengWanfu2

-- 查询权限
SHOW GRANTS FOR ZhengWanfu2 -- 查看指定用户权限

SHOW GRANTS FOR root@127.0.0.1 
-- There is no such grant defined for user ‘root‘ on host ‘127.0.0.1‘
-- 主机“127.0.0.1”上没有为用户“root”定义此类授予

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

-- 删除用户
DROP USER ZhengWanfu2

MySQL备份

  • 数据库备份必要性

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

  • MySQL数据库备份方法

    • mysqldump备份工具
    • 数据库管理工具,如SQLyog
    • 直接拷贝数据库文件和相关配置文件

MySQL数据库学习

6.数据库的设计

规范化数据库设计

为什么需要设计数据库 ?

答:当数据库比较复杂时我们需要设计数据库

糟糕的数据库设计 :

  • 数据冗余,存储空间浪费
  • 数据更新和插入的异常
  • 程序性能差

良好的数据库设计 :

  • 节省数据的存储空间
  • 能够保证数据的完整性
  • 方便进行数据库应用系统的开发

软件项目开发周期中数据库设计 :

  • 需求分析阶段: 分析客户的业务和数据处理需求
  • 概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.

设计数据库步骤

  • 收集信息

    • 与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.

  • 标识实体[Entity]

    • 标识数据库要管理的关键对象或实体,实体一般是名词

  • 标识每个实体需要存储的详细信息[Attribute]

  • 标识实体之间的关系[Relationship]

绘制E-R图和数据库模型图

E-R图即数据库设计的图形化表达方式 , 也称为实体-关系图

MySQL数据库学习

关系模式 : 用二维表的形式表示实体和实体间联系的数据模型即关系模式R(U)

绘制数据库模型图的步骤 :

  • 新建数据库模型图
  • 添加实体
  • 添加数据列及相应的属性
  • 添加实体之间的映射关系

三大范式

推荐博客：https://www.cnblogs.com/xietianjiao/p/10972285.html

问题 : 为什么需要数据规范化?

不合规范的表设计

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

第一范式 (1st NF)

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

第二范式(2nd NF)

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

第三范式(3rd NF)

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

规范化和性能的关系

为满足某种商业目标 , 数据库性能比规范化数据库更重要

在数据规范化的同时 , 要综合考虑数据库的性能

通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间

通过在给定的表中插入计算列,以方便查询

7.JDBC(重点)

数据库驱动

驱动包下载地址：http://mvnrepository.com/artifact/mysql/mysql-connector-java

MySQL数据库学习

上一篇：MySQL数据库


下一篇：数据库基本语法