MySQL常用命令学习笔记

学习视频地址

一、虚拟机内：

启动mysql:   systemctl start mysqld  // service mysqld start
重启mysql:   systemctl restart mysqld //service mysqld restart
停止mysql:   systemctl stop mysqld  //service mysqld stop
查看mysql状态： systemctl status mysqld  // service mysqld status
登录mysql:   mysql -u用户 -p密码
查看mysql系统上设置: cat /etc/my.cnf

二、mysql内基本命令：(MySQL关键字不区分大小写)

连接数据库：mysql -u用户名 -p密码
修改密码：   alter user 'toor'@'用户IP' identified by '新密码';
查看密码限制： show variables like 'validate_password&';
修改密码限制： set gobal validate_password.限制名  = 新限制； 
进入切换相关表库：  use  表、库名；
查看mysql库：show databases;
查看mysql表：show tables;
显示数据库中指定表信息：  describe 表名；
创建一个数据库：create database 数据库名；
退出连接：exit 、 quit；
单行注释： --
多行注释： /*。。。。*/；

三、数据库xxx语言：

DDL  --定义
DML  --操作
DQL  --查询
DCL  --控制

四、操作数据库：[可选]，{必选} （[],{}在数据库中使用时不添加）

1. 操作数据库

1.1 创建数据库：

create database [if not exists] 数据库名

1.2 删除数据库：

drop database [if exists] 数据库名

1.3 使用数据库：

--注：如果表名或者字段名是一个特殊字符，就需要带  ``  符号
use 数据库名 
eg: use `user`

1.4 查看数据库：

查看mysql库：show databases;
查看mysql表：show tables;
显示数据库中指定表信息：  describe 表名

2. 数据库的列类型：

2.1 数值：

tinyint         十分小的数据        1个字节
smallint        较小的数据          2个字节
mediumint       中等大小的数据      3个字节
int             **标准的整数**      4个字节         -- 常用的int
bigint          较大的数据          8个字节
float           浮点数              4个字节
double          浮点数              8个字节
decimal         字符串形式的浮点数，常用用金融计算。

2.2 字符串：

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

2.3 时间日期：

date            YYYY-MM-DD  日期格式
time            HH:mm:ss    时间格式
datetime        YYYY-MM-DD HH:mm:ss     最常用的时间格式
year            年份表示

2.4 null

没有值未知
注：不要使用null进行运算，结果为null

3. 数据库的字段属性：

Unsigned:
-- *无符号的整数
-- 声明了该列不能声明为负数
zerofill:
-- 0填充的
-- 不足的位数，使用0来填充， int(3) , 5 --- 005
自增：
-- 通常理解为自增，自动在上一条记录的基础上 + 1（默认）
-- 通常用来设计唯一的主键~index，必须是整数类型
-- 可以自定义主键自增的起始值和步长
非空 NULL not null：
-- 假设设置为 not null ,如果不给他赋值，就会报错
-- null,如果不填写值，默认就是null
默认：
-- 设置默认的值
-- sex,默认的值为男，如果不指定该列的值，则会有默认的值
拓展：
/*每个表都必须存在以下五个字段，在java项目中，表示一个记录存在意义

id          主键
`version`   乐观锁
is_delete   伪删除
gmt_create  创建时间
gmt_update  修改时间
*/

4.创建数据库及其表单：

-- 注意点，使用英文（），表名和字段尽量使用  ``  括起来
-- AUTO_INCREMENT 自增需要与 key 主键配合
-- 语句之间用 ； 隔开
-- 字符串使用 单引号 '' 括起来
-- 所有语句后面加英文的 ， 最后一个不用加
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键；
CREATE DATABASE `student`;
USE student;
CREATE TABLE `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`birthday` datetime DEFAULT NULL COMMENT '出生日期',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式*：

CREATE TABLE [IF NOT EXISTS] '表名'（
`字段名` 列类型 [属性] [索引] [注释]，
………………………………………………………………………………………………，
`字段名` 列类型 [属性] [索引] [注释]
）[表类型][字符集设置][注释]

补充常用命令：

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

5. 数据表的类型

5.1 关于数据库引擎

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

#	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为两倍

常规使用操作：

· MYISAM  -- 节约空间，速度较快

· INNODB  -- 安全性高，事务的处理，多表多用户操作

在物理空间存在的位置：

所有数据库文件都在data目录下，本质还是文件的存储！

MySQL引擎在物理文件上的区别
· INNODB 在数据库表中只有一个*.frm文件，以及上层目录下的ibdata1文件
· MYISAM 对应文件：
    *.frm       表结构的定以文件
    *.MYD       数据文件（data）
    *.MYL       索引文件（index）

5.2 设置数据库字符集编码：

CHARSET=utf8

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

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

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

character-set-server=utf8

6.修改删除表：

6.1 修改

-- 修改表名：ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1

-- 增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)

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

-- 删除表的字段：
ALTER TABLE teacher DROP age1 

6.2 删除

-- 删除表（如果表存在再删除）
DROP TABLE IF EXISTS teacher1

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

注意点：

·  用 `` 包裹字段名
·  注释 -- /**/
·  sql关键字大小写不敏感，建议小写
·  所有的符号全部用英文

五.MySQL数据库管理：

1. 外键（了解）

1.1 方法一：在创建表的时候，增加约束（麻烦，比较复杂）

USE student;
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
-- 给这个外键添加约束 （执行引用）reference 引用

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 '密码',
`birthday` datetime DEFAULT NULL COMMENT '出生日期',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
PRIMARY KEY(`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

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

1.2 方法二：创建表成功后，添加外键约束

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
-- 给这个外键添加约束 （执行引用）reference 引用

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 '密码',
`birthday` datetime DEFAULT NULL COMMENT '出生日期',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 哪个表(哪个字段);

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

最佳实践

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

2. DML语言（全部记住）

数据库的意义：数据存储，数据管理
DML语言：数据操作语言
    · insert
    · update
    · delete

2.1 添加 insert

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

 -- 由于主键自增我们可以省略(如果不写表的字段，他就会一一匹配)
 INSERT INTO `grade` VALUES('大三')
 -- 一般写插入语句，我们一定要数据和字段一一对应!

 -- 插入多个字段
 INSERT INTO `grade` ( `gradename` )
 VALUES('大二'),('大一')
 INSERT INTO `student` ( `name` ) VALUES ('张三')
 INSERT INTO `student` ( `name`，`pwd`，`sex` ) VALUES ('张三'，'aaaa','男')
 INSERT INTO `student` ( `name` ，`pwd` ，s`ex` )
 VALUES ( '李四'，'aaaaaa','男'),('王五','aaaaaa','男')

语法:
insert into 表名([字段名1,字段2,字段3])values(‘值1’),(‘值2’),(‘值3’，…)

注意事项:

1.字段和字段之间使用英文逗号隔开

2.字段是可以省略的，但是后面的值必须要要一一对应, 不能少

3.可以同时插入多条数据，VALUES 后面的值，需要使用，隔开即可 VALUES(),(),…

2.2 修改 update

update 修改谁 (条件) set原来的值=新值
--修改学员名字,带了简介
UPDATE `student` sET `name` ='狂神' WHERE id = 1;
--不指定条件的情况下，会改动所有表!
UPDATE `student` SET `name` ='长江7号'
--修改多个属性，逗号隔开
UPDATE `student` SET `name` = '狂神'，`email` = '123456@qq.com' WHERE id = 1;
--语法:
-- UPDATE 表名 set colnum_ name = value, [colnum_name = value,....] where [条件]

条件: **where子句运算符**id 等于某个值，大于某个值,在某个区间内修改…

操作符会返回布尔值

操作符	含义	范围	结果
=	等于	5=6	false
<> 或 !=	不等于	5<>6	true
>	大于	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 表名 set colnum_name = value, [colnum_name = value,...] where [条件]

注意:

●   colnum_name 是数据库的列，尽量带 ``
●   条件,筛选的条件,如果没有指定，则会修改所有的列
●   value,是一个具体的值，也可以是一个变量
●   多个设置的属性之间，使用英文逗号隔开

 UPDATE `student` SET `birthday` = CURRENT_TIME WHERE `name` ='长江7号' AND sex='女'

2.3 删除 delete 、TRUNEATE

delete

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

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

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

TRUNEATE

TRUNCATE命令
作用：完全清空一个数据库表，表的结构和索引约束不会变!
-- 清空 student表
TRUNCATE `student`

delete的TRUNCATE区别

●相同点:都能删除数据，都不会删除表结构

●不同:

· TRUNCATE 重新设置自增列计数器会归零

· TRUNCATE 不会影响事务

--测试delete和TRUNCATE 区别
CREATE TABLE `test' (
`id` INT(4) NOT NULL AUTO_ INCREMENT ,
`co11` VARCHAR(20) NOT NULL,
PRIMARY KEY ( id' ) 
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `test` (`coll`) VALUES('1'),('2'),('3')
DELETE FROM `test` -- 不会影响自增
TRUNCATE TABLE `test` -- 自增会归零

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

● InnoDB 自增列会重 1开始(存在内存当中的， 断电即失)

● MyISAM 继续从 上一个自增量开始(存在文件中的， 不会丢失)

六. DQL查询数据（*超重点）

1. DQL

(Data Query LANGUAGE :数据查询语言)

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

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

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

● 使用频率最高的语句

SELECT 完整的语法：
注意:[]括号代表可选的，{ )括号代表必选的

SELECT [ALLI 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}];
-- 指定查询的记录从哪条至哪条

select 小结

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

2. 指定查询字段

2.1 查询

-- 查询全部的学生  SELECT 字段 FROM 表
SELECT * FROM student
--查询指定字段
SELECT `StudentNo` , `StudentName` FROM student
--别名，给结果起一个名字 AS 可以给字段起别名，也可以给表起别名
SELECT `StudentNo` AS 学号，`studentName` AS 学生姓名 FROM student AS s
--函数Concat (a, b)
SELECT CONCAT('姓名: ' ，StudentName) AS 新名字 FROM student

语法: SELECT 字段，… FROM 表

有的时候，列名字不是那么的见名知意。我们就可以起别名 AS

字段名 as 别名

表名 as 别名

2.2 去重 distinct

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

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

2.3 数据库的列(表达式)

SELECT VERSION() --查询系统版本 (函数)
SELECT 100*3-1 AS计算结果 --用来计算  (表达式)
SELECT @@auto_ _increment_ increment -- 查询自增的步长 (变量)

--学员考试成绩+ 1分查看
SELECT `studentNo` ，`StudentResult` +1 AS '提分后' FROM result

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

select表达式 from 表

3. where 条件子句

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

3.1 逻辑运算符

运算符	语法	描述
and &&	a and b || a&&b	逻辑与，两个都为真，结果为真
or ||	a or b || a||b	逻辑或，其中一个为真，结果为真
NOT !	not a || !a	逻辑非，真为假，假为真

尽量使用英文字母

例子：
-- ===================where======================
SELECT studentNo, `StudentResult` FROM result
-- 查询考试成绩在95~100 分之间
SELECT `studentNo`,`StudentResult` FROM result
WHERE StudentResult>=95 AND StudentResult<=100
-- and  &&
SELECT `studentNo`, `StudentResult` FROM result
WHERE StudentResu1t>=95 && StudentResult<=100
-- 模糊查询(区间)
SELECT studentNo, `StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100

-- 除了1000号学生之外的同学的成绩
SELECT studentNo, `StudentResult’ FROM result
WHERE studentNo!=1000;

-- !=  not
SELECT studentNo, `StudentResult` FROM result
WHERE NOT studentNo = 1000

3.2 模糊查询： 比较运算符

运算符	运算符	运算符
is null	a is null	如果操作符为NUII, 结果为真
is not null	a is not null	如果操作符为NUII, 结果为真
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,…其中的某一个，结果为真

--  查询姓刘的同学
-- 1ike结合  %(代表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, 1003);

--查询在北京的学生
SELECT `StudentNo`,` StudentName` FROM `student`
WHERE `Address` IN ('安徽'，'河南洛阳');



-- ======null  not nu11====

-- 查询地址为空的学生  nu11 ''
SELECT `StudentNo` ，`StudentName` FROM `Student`
WHERE address='' OR address IS NULL

-- 查询有出生日期的同学   不为空
SELECT `StudentNo`，`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL

--查询没有有出生日期的同学为空
SELECT `StudentNo`, `StudentName` FROM `student`
WHERE `BornDate` IS NULL

4. 联表查询

4.1 联表查询join

操作	描述
lnner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

-- =============联表查询join ==============
-- 查询参加了考试的同学(学号，姓名，科目编号，分数)
SELECT * FROM student
SELECT * FROM result

/*思路
1.分析需求，分析查询的字段来自哪些表， (连接查询)
2.确定便用哪种连接查询? 7种
确定交叉点(这两个表中哪个数据是相同的)
判断的条件:学生表的中 studentNo = 成绩表 studentNo
*/
SELECT s.studentNo, studentName，SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
ON s.studentNo = r.studentNo

-- 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


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

4.2 自连接

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

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', '办公信息') ;

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	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

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

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

--查询父子信息:
SELECT a.`categoryName` AS '父栏目' ,b.`categoryName` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`

5.分页和排序：

5.1 排序(order by)：升序ASC ，降序DESC

排序:  升序ASC ，降序DESC
-- 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.`subjectNo` = sub.`subjectNo`
WHERE subjectName = '数据库结构-1'
ORDER BY StudentResult ASC

5.2 分页（limit）

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

-- 分页，每页只显示五条数据
-- 语法:limit当前页，页面的大小.
-- LIMIT 0,5    1~5
-- LIMIT 1,5    2~6
-- LIMIT 6,5

SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student S
INNER JOIN `result` r
ON s.studentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`subjectNo` = sub.`subjectNo`
WHERE subjectName = '数据库结构-1'
ORDER BY StudentResult ASC
LIMIT 5,5

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

语法: limit( 查询起始下标，pagesize)

--思考:
--查询JAVA第一学年课程成绩排名前十的学生，并且分数要大于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.`SubjectNo`
WHERE SubjectName = 'JAVA第一 学年' AND StudentResult>=80
ORDER BY StudentResult DESC
LIMIT 0,10 

6. 子查询：

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

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

-- ============= where =============
-- 1、查询数据库结构-1的所有考试结果(学号，科目编号，成绩)，降序排列
-- 方式一:使用连接：
SELECT `StudentNo`，r.`SubjectNo` , `StudentResult`
FROM `result`
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo
WHERE subjectName = '数据库结构-1'
ORDER BY StudentResult DESC

方式二:使用子查询(由里及外)
-- 查询所有数据库结构-1的学生学号
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 r.`Subj ectNo` = sub.`SubjectNo`
WHERE `SubjectName` = '高等数学-21' AND StudentResult>=80

--分数不小于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 resu1t WHERE StudentResult>=80 AND SubjectNo = (  
        SELECT SubjectNo FROM `subject` WHERE `SubjectName` = '高等数学-2'
    )
)

7.分组和过滤：

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心: (根据不同的课程分组)
SELECT subjectName, AVG(StudentResu1t) AS 平均分，MAX(StudentResu1t) AS 最高分,MIN(studentResu1t) AS 最低分
FROM result r
INNER J0IN `subject` sub
ON r.`subjectNo` = sub.`SubjectNo`
GROUP BY r.subjectNo -- 通过什么字段来分组
HAVING 平均分>80

七.MySQL函数

官网：MySQL

1. 常用函数：

1.1 数学运算

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

1.2 字符串函数

-- 字符串函数
SELECT CHAR LENGTH('即使再小的帆也能远航') -- 字符串长度
SELECT CONCAT('我， '爱", '你们) -- 拼接字符串，
SELECT INSERT (我爱编程helloworld',1,2,超级热爱') -- 查询，从某个位置开始替换某个长度
SELECT LOWER( 'KuangShen') -- 小写字母
SELECT UPPER('KuangShen') -- 大写字母
SELECT INSTR ('kuangshen', 'h') -- 返回第- -次出现的子串的索引
SELECT REPLACE ('狂神说坚持就能成功，'坚持',努力) -- 替换出现的指定字符串
SELECT SUBSTR(' 狂神说坚持就能成功,4,6) -- 返回指定的子字符串(源字符串,截取的位置，截取的长度)
SELECT REVERSE('清晨我上马) --反转

-- 例子：
查询姓周的同学，名字变为 邹
SELECT REPLACE (studentname,'周','邹') FROM student
WHERE studentname LIKE '周%'

1.3 时间和日期函数

--时间和日期函数(记住)
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 () )

1.4 系统：

SELECT SYSTEM_USER( )
SELECT USER( )          -- 查看系统用户
SELECT VERSION()        -- 查看系统版本

2. 聚合函数(常用)：

2.1 统计函数

都能够统计表中的数据
SELECT COUNT (`字段`) FROM 数据库名; -- Count(字段)， 会忽略所有的 null 值
SELECT COUNT(*) FROM student;        -- Count (*)，不会忽略null 值，本质 计算行数
SELECT COUNT (1) FROM result;        -- Count (1) ，不会忽略所有的null值，本质：计算行数

2.2 求和函数

SELECT SUM(`StudentResult`) AS 总和 FROM result
SELECT AVG(`StudentResult`) AS 平均分 FROM result
SELECT MAX(`StudentResult`) AS 最高分 FROM result
SELECT MIN(`StudentResult`) AS 最低分 FROM result

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

什么是MD5?

主要增强算法复杂度和不可逆性。

MD5不可逆，具体的值的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, 'zhangsan' ,'123456'),(2, 'lisi', '123456 '),(3, 'wangwu', '123456')

--加密
UPDATE testmd5 SET pwd=MD5 (pwd) WHERE id = 1
UPDATE testmd5 SET pwd=MD5(pwd) --加密全部的密码
--插入的时候加密
INSERT INTO testmd5 VALUES(4, 'xiaoming' ,MD5('123456 '))
--如何校验:将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name` ='xiaoming' AND pwd=MD5('123456')

八. 事务

要么都成功，要么都失败
-----------------------
1、SQL执行 A给B转账    A 1000  -->200   B 200 

2、SQL执行 B收到A的钱  A 800 --> B 400
----------------------
将一组SQL放在一个批次中去执行~

1. 事务原则: ACID原则 原子性，一致性，隔离性，持久性 (脏读，幻读…）

参考博客连接: https://blog.youkuaiyun.com/dengjili/article/details/82468576

• 原子性(Atomicity)
• 要么都成功，要么都失败
• 一致性 (Consistency)
• 事务前后的数据完整性要保证一致，1000
• 持久性(Durability) --（事务提交）
• 事务一旦提交则不可逆，被持久化到数据库中!
• 隔离性(Isolation)
• 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务, 不能被其他事务的操作数据所干扰，事务之间要相互隔离。

2. 隔离所导致的一些问题

• 脏读:
• 指一个事务读取了另外一个事务未提交的数据。
• 不可重复读:
• 在一个事务内读取表中的某一行数据，多次读取结果不同。(这个不一 定是错误，只是某些场合不对)
• 虚读(幻读)
• 是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

3.执行事务

--====================事务=========================

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

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

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

INSERT xx
INSERT xx
--提交: 持久化(成功! )
COMMIT
--回滚: 回到的原来的样子(失败! )
ROLLBACKI
--事务结束
SET autocommit = 1 -- 开启自动提交
--了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK To SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点

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( `name`,`money` )
VALUES ('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' -- A加500

COMMIT; -- 提交事务，就被持久化了!
ROLLBACK; -- 回滚
SET autocommit = 1; -- 恢复默认值

九. 索引

索引参考博文

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

1. 索引的分类

• 主键索引(PRIMARY KEY )

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

• 唯一索引(UNIQUE KEY)

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

• 常规索引(KEY/INDEX)

1. 默认的，index。 key 关键字来设置

• 全文索引(FullText)

1. 在特定的数据库引擎下才有，MyISAM
2. 快速定位数据

基础语法：

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

-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个全文索引 (索引名) 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName` ( `studentName` ) ;

-- EXPLAIN 分析sql执行的状况

EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');

2. 测试索引：

-- 创建数据库表
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 $$
set global log_bin_trust_function_creators=TRUE;
-- 写函数之前必须要写，标志
CREATE FUNCTION mock_data ()
RETURNS INT DETERMINISTIC
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 1;
	WHILE i<num DO
		INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'123456789@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() -- 执行此函数 生成一百万条数据


-- 测试案例：
SELECT * FROM app_user WHERE `name` = '用户123654' ; -- 0.496s

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户99999' ;
-- 创建索引：
-- id_表名_字段名 
-- CREATE INDEX 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name` = '用户99999' ;  -- 0.031s

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

3. 索引原则

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

索引的数据结构

Hash类型的索引
Btree : InnoDB 的默认数据结构~

十. 权限管理和备份

1. 用户管理

1.1 MySQL工具界面话操作即可

1.2 sql命令

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER kuangshen IDENTIFIED BY '123456'
-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('123456')
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR kuangshen = PASSWORD('123456')
-- 重命名 RENAME USER 原来名字 TO 新的名字
RENAME USER kuangshen TO kuangshen2
-- 用户授权 ALL PRIVILEGES 全部的权限，库.表
-- ALL PRIVILEGES 除了给别人授权，其他都能够干
GRANT ALL PRIVILEGES ON *.* TO kuangshen2
-- 查询权限
SHOW GRANTS FOR kuangshen2 -- 查看指定用户的权限
SHOW GRANTS FOR root@1ocalhost
-- ROOT用户权限: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- 撤销权限 REVOKE 哪些权限. 在哪个库撇销. 给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2
--删除用户
DROP USER kuangshen

2.MySQL备份

为什么要备份:

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

MySQL数据库备份的方式

• 直接拷贝物理文件
• 在Sqlyog这种可视化工具中手动导出
• 使用命令行导出mysqldump 命令行使用

# mysqldump -h主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student > D:/a.sql
# mysqldump -h主机 -u 用户名 -p 密码 数据库 表1 表2 表3 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 schoo1 student > D:/b.sql
# mysqldump -h主机-u 用户名-p密码数据库> 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school > D:/c.sql

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

-- 未登录的时候：
mysql -u用户名-p密码 库名< 备份文件

假设你要备份数据库，防止数据丢失。
把数据库给朋友! sql文件给别人即可!

十一.规范数据库设计

1. 为什么需要设计

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

糟糕的数据库设计：

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

良好的数据库设计:

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

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

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

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

● 收集信息，分析需求

• 用户表(用户登录注销,用户的个人信息，写博客,创建分类)
• 分类表(文章分类，谁创建的)
• 文章表(文章的信息)
• 评论表
• 友链表(友链信息)
• 自定义表(系统信息,某个关键的字，或者一些主字段) key: value
• 说说表(发表心情… id… conte…create_time)

● 标识实体(把需求落地到每个字段)

● 标识实体之间的关系

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

2.三大范式

为什么需要数据规范化?
    ●信息重复
    ●更新异常
    ●插入异常
无法正常显示信息
    ●删除异常
丢失有效的信息

2.1 第一范式(1NF)

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

2.2第二范式(2NF)

前提:满足第一范式

每张表只描述一件事情

2.3第三范式(3NF)

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

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

(规范数据库的设计)
规范性和性能的问题
关联查询的表不得超过三张表
●考虑商业化的需求和目标， (成本,用户体验! )数据库的性能更加重要
●在规范性能的问题的时候，需要适当的考虑一下规范性!
●故意给某些表增加一些冗余的字段。 (从多表查询中变为单表查询)
●故意增加一些计算列(从大数据量降低为小数据量的查询:索引)

十二、JDBC(重点)

1.

驱动:卡,显卡,数据库

2. JDBC

SUN公司为了简化开发人员的(对数据库的统一)操作,提供了一个java操作数据库的)规范，俗称JDBC

这些规范的实现由具体的厂商去做~

对于开发人员来说，我们只需要掌握JDBC接口的操作即可！

java.sql

javax.sql

还需要导入一个数据库驱动包mysql-connector-java-5.1.47.jar

3.第一个JDBC程序:

3.1 创建测试数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE `users`(
	id INT PRIMARY KEY,
	NAME VARCHAR(40),
	PASSWORD VARCHAR(40),
	email VARCHAR(60),
	birthday DATE
);

INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')

操作过程：https://www.bilibili.com/video/BV1NJ411J79W?p=38

步骤总结:

1、加载驱动

2、连接数据库DriverManager

3、获得执行sq的对象Statement

4、获得返回的结果集

5、释放连接.