MySQL基础

MySQL

1、连接数据库

命令行连接

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

update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; --修改密码
flush privileges;  --刷新权限

-----------------------
--所有的语句都使用;结尾
show databases; --查看所有的数据库

mysql>  use school -- 切换数据库 use （数据库名）
Database changed

show tabales; -- 查看数据库中所有的表
described student; -- 显示数据库中所有表的信息/desc

create database westos; --创建一个数据库

exit; --退出连接

--单行注释
/*(多行注释)
asd
asd
*/

数据库xxx语言

DDL 定义

DML 操作

DQL 查询

DCL 控制

2、操作数据库

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

2.1、操作数据库

1、创建数据库

create database [if no exists] westos;

2、删除数据库

drop database if exists westos;

3、使用数据库

--如果表名或字段名是一个特殊字符，就需要带` `
use `westos`;

4、查看数据库

show database;--查看所有的数据库

2.2、数据库的列类型

数值

tinyint 十分小的数据 1个字节

smallint 较小的数据 2个字节

mediumint 中等大小的数据 3个字节

int 标准的整数 4个字节 常用

bigint 较大的数据 8个字节

float 浮点数 4个字节

double 浮点数 8个字节 （精度问题）

decimal 字符串形式的浮点数 金融计算的时候，一般使用decimal

字符串

• char 字符串固定大小 0~255

• varchar 可变字符串 0~65535 常用的变量 String

• tinytext 微型文本 2^8-1

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

时间日期

java.util.Date

• date YYYY-MM-DD 日期格式

• time HH:mm:ss 时间格式

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

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

• year 年份表示

null

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

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

Unsigned:

• 无符号的整数

• 声明了该列不能为负数

zerofill

• 0填充的
• 不足的位数，使用0来填充 eg: int(3) , 5–005

auto_increment

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

Null & not null

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

默认 default

• 设置默认的值

commet

• 注释

拓展：

--每一个表都必须存在以下五个字段。做项目用的，表示一个记录存在意义
id  -- 主键
'version'  -- 乐观锁
is_delete  -- 伪删除
gmt_create -- 创建时间
gmt_update -- 修改时间 

2.4、创建数据库表(重点)

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

-- 注意点，使用英文()， 表的名称和 字段 尽量使用``括起来
-- AUTO_INCREMENT 自增
-- 字符串的使用用''括起来 eg:comment '注释'
-- 所有的语句后面加, 最后一个不用加
-- 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`)
)ENDINE=INNODB DEFAULT CHARSET=utf8

格式

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

常用命令

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

2.5、数据库表的类型

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

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持(表锁定)	支持(两个都)
外键约束	不支持	支持
全文索引	支持	不支持
表空间的的大小	较小	较大，约2倍MYISAM

常规使用操作：

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

在物理空间存在的位置

所有的数据库文件都存在data目录下

本质还是文件的存储

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

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

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

CHARSET=utf8

不设置的话，会是mysql默认的字符集编码(不支持中文)

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

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

character-set-server=utf8

2.6、修改删除表

修改

-- 修改表名: 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 VARCHAR(11)  --修改约束
-ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[]
ALTER TABLE teacher1 CHANGE age age1 INT(1) --字段重命名


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

删除

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

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

3、MySQL数据管理

3.1、外键(了解)

方式一、在创建表的时候，增加约束

CREATE TABLE `grade`(
  `gradeId` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
  `gradeName` VARCHAR(50) NOT NULL COMMENT '年级名称',
   PRIMARYKEY(`gradeId`)
)ENGINE=INNODB　DEFAULT CHARSET=utf8

-- 学生表的gradeId字段 要去引用年级表的gradeId
-- 定义外键key
-- 给这个外键添加约束(执行引用) references 引用

CREATE TABLE IF NOT EXISTS `student`(
   `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `gradeId` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
   `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`),
    KEY `FK_gradeId` (`gradeId`),
    CONSTRAINT `FK_gradeId` FOREIGN KEY (`gradeId`) REFERENCES `grade`(`gradeId`)
)ENDINE=INNODB DEFAULT CHARSET=utf8

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

方式二、创建表成功后，添加外键约束

CREATE TABLE IF NOT EXISTS `student`(
   `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `gradeId` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
   `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`)
)ENDINE=INNODB DEFAULT CHARSET=utf8

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

-- ALTER TABLE 从表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 主表(列)

以上操作都是物理外键，数据库级别的外键，不建议使用。

最佳实践

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

3.2、DML语言(记住)

DML语言:数据操作语言

• insert
• updat
• delete

3.3、添加

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('张三'，'123456','男');

INSERT INTO `student`(`name`,`pwd`,`sex`);
VALUES('张三'，'123456','男'),('李四'，'123456','女');

语法：insert into 表名([字段1，字段2，字段3,...])values('值1','值2','值3',...);

注意事项：

• 字段和字段之前使用英文逗号隔开
• 字段是可以省略的，但是后面的值必须要一一对应
• 可以同时插入多条数据，VALUES后面的值，需要用,隔开

3.4、修改

update

-- 修改学员名字
UPDATE `student` SET `name`='chan' WHERE id =1;

-- 不指定条件的情况下，会改动所有表
UPDATE `student` SET `name`='chan';

-- 修改多个属性，逗号隔开
UPDATE `student` SET `name`='chan',`email`='123@163.com' WHERE id =1;

语法：UPDATE 表名 set column_name = value,[cloumn_name = value,...] where [条件];

注意:

• column_name是数据库的列，带上``
• 条件，筛选的条件，如果没要指定，则会修改所有的列
• value，是一个具体的值，也可以是一个变量

UPDATE `student` SET `birthday` = CURRENT_TIME WHERE `name`='chan' AND sex='男';

3.5、删除

delete

-- 删除数据
DELETE FROM `student`

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

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

TRUNCATE

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

-- 清空student表
TRUNCATE TABLE `student`;

• 相同的：都能删除数据，不会删除表结构
• 不同：
  • TRUNCATE 重新设置自增列 计数器会归零
  • TRUNCATE 不会影响事务

了解：DETELE删除的问题,重启数据库，现象

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

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

4.1、DQL

• 所有的查询操作都用它 Select
• 简单的查询，复杂的查询都能做
• 数据库中最核心的语言
• 使用频率最高的语句

Select完整的语法：

SELECT

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as lias1]],[table.field2[as lias2]],[....]}
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}];
	-- 指定查询的记录从哪条至哪条

注意：[]括号代表可选，{}括号代表必选

4.2、指定查询字段

-- 查询全部的学生 SELETE 字段 FROM 表
SELETE * 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

语法：SELETE 字段，... FROM 表

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

去重 distinct

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

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

数据库的列（表达式）

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


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

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

select 表达式 from 表

4.3、where条件字句

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

搜索条件由一个或多个表达式组成，结果为布尔值

逻辑运算符

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

尽量使用英文字母

-- 查询考试成绩在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	如果操作符不为 null ，结果为真
BETWEEN	a between b and c	若a 在 b和c之间，结果为真
Like	a like b	SQL匹配，如果a匹配到b，结果为真8
In	a in (a1,a2,a3…)	假设a在a1，或者a2…其中的某一个值中，结果为真

-- =========== 模糊查询 ===========
-- 查询姓刘的同学
-- like结合 %(代表0到任意个字符)  _(一个字符)
SELECT `sutdentNo`,`studentName` FROM `student`
WHERE studentName LIKE '刘%';

-- 查询姓刘的同学，名字后面只有一个字的
SELECT `sutdentNo`,`studentName` FROM `student`
WHERE studentName LIKE '刘_';

-- 查询姓刘的同学，名字后面只有两个字的
SELECT `sutdentNo`,`studentName` FROM `student`
WHERE studentName LIKE '刘__';


-- 查询名字中间有嘉字的  %嘉%
SELECT `sutdentNo`,`studentName` FROM `student`
WHERE studentName LIKE '%嘉%';


-- ====== in （具体的一个或多个值）======
--  查询1001,1002,1003号学员
SELECT `sutdentNo`,`studentName` FROM `student`
WHERE studentNo IN (1001,1002,1003);

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

-- ======= null    not null =======
-- 查询地址为空的学生 null ''
SELECT `sutdentNo`,`studentName` FROM `student`
WHERE address='' OR address IS NULL;

-- 查询有出生日期的同学，不为空
SELECT `sutdentNo`,`studentName` FROM `student`
WHERE `birthday` IS NOT NULL;

-- 查询没有出生日期的同学，为空
SELECT `sutdentNo`,`studentName` FROM `student`
WHERE `birthday` IS NULL;

4.4、联表查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-osxfjV47-1603975082603)(E:\桌面\images\image-20201018155502032.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HjgD37OY-1603975082605)(E:\桌面\images\timg.jpg)]

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

-- ======== 联表查询 join =========

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student;
SELECT * FROM result;

/* 思路
1.分析需求，分析查询的字段来自哪些表（连接查询）
2.确定使用哪种连接查询？7种
确定交叉带你（这两个表中哪个数据是相同的）
判断条件：学生表中 studentNo = 成绩表 studentNo
*/

-- join (连接的表) on （判断的条件） 连接查询
-- where 等值查询

SELECT s.studentNo,studentName,subjectNo,sutdentResult
FROM sutdent AS s
INNER JOIN result AS r
ON s.studentNo = r.studentNo;

-- Right Join 
SELECT s.studentNo,studentName,subjectNo,sutdentResult
FROM sutdent s
RIGHT JOIN result r
ON s.studentNo = r.studentNo;


-- Left Join 
SELECT s.studentNo,studentName,subjectNo,sutdentResult
FROM sutdent s
LEFT JOIN result r
ON s.studentNo = r.studentNo;


-- 查询缺考的同学
SELECT s.studentNo,studentName,subjectNo,sutdentResult
FROM sutdent 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,sutdentResult
FROM sutdent s
LEFT JOIN result r
ON s.studentNo = r.studentNo
INNER JOIN `subject` sub
ON r.subjectNo=sub.subjectNo;



-- 我要查询哪些数据 select。。
-- 从哪几个表查 FROM 表 xxx join 连接的表  on  交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加8

自连接

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

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

4.5、分页和排序

排序

-- ================ 分页 limit 和 排序 order by ===============
-- ORDER BY 通过哪个字段排序，怎么排
-- 排序： 升序 ASC ，降序 DESC
SELECT s.studentNo,studentName,subjectName,sutdentResult
FROM sutdent s
LEFT JOIN result r
ON s.studentNo = r.studentNo
INNER JOIN `subject` sub
ON r.subjectNo=sub.subjectNo
ORDER BY  sutdentResult ASC;  -- or DESC

分页

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

-- 分页，每页只显示五条数据
-- 语法：limit 起始页，页面的大小
-- 网页应用：当前，总的页数，页面的大小
-- LIMIT 0,5 1-5
-- LIMIT 1,5 2-6
-- LIMIT 6,5 7-11
SELECT s.studentNo,studentName,subjectName,sutdentResult
FROM sutdent s
LEFT JOIN result r
ON s.studentNo = r.studentNo
INNER JOIN `subject` sub
ON r.subjectNo=sub.subjectNo
ORDER BY  sutdentResult ASC
LIMIT 0,5;

-- 第一页，limit 0,5
-- 第二页，limit 5,5
-- 第三页，limit 10,5
-- 第n页， limit (n-1)*pagesize,pagesize
-- 【pagesize ；页面大小】
-- 【(n-1)*pagesize：起始值】
-- 【n：当前页】
-- 【数据总数/页面大小 = 总页数】向上取整

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

4.6、子查询

where（值是固定的）

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

where(子查询。。。)

理解：需要显示多个表数据用联表查询，只需一个表数据用子查询。

4.7、分组和过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于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;

5、MySQL 函数

5.1、常用函数

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

-- 字符串函数
SELECT CHAR_LENGTH('毕业即失业') -- 字符串长度
SELECT CONCAT('我','没','工作') -- 拼接字符串
SELECT INSERT('helloword',1,2,'java') -- 查询，替换 从某个位置开始替换某个长度
SELECT LOWER ('Chan') -- 变成小写
SELECT UPPER ('chan') -- 变成大写
SELECT INSTR ('chan','c') -- 返回第一次出现的索引
SELECT REPLACE ('chan','ch','aa') -- 替换指定字符

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

-- 系统
SELECT SYSTEM_USER()
SELECT USER ()
SELECT VERSION ()

-- 数值函数
 abs(x)            -- 绝对值 abs(-10.9) = 10
 format(x, d)    -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
 ceil(x)            -- 向上取整 ceil(10.1) = 11
 floor(x)        -- 向下取整 floor (10.1) = 10
 round(x)        -- 四舍五入去整
 mod(m, n)        -- m%n m mod n 求余 10%3=1
 pi()            -- 获得圆周率
 pow(m, n)        -- m^n
 sqrt(x)            -- 算术平方根
 rand()            -- 随机数
 truncate(x, d)    -- 截取d位小数
 
 -- 时间日期函数
 now(), current_timestamp();     -- 当前日期时间
 current_date();                    -- 当前日期
 current_time();                    -- 当前时间
 date('yyyy-mm-dd hh:ii:ss');    -- 获取日期部分
 time('yyyy-mm-dd hh:ii:ss');    -- 获取时间部分
 date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j');    -- 格式化时间
 unix_timestamp();                -- 获得unix时间戳
 from_unixtime();                -- 从时间戳获得时间
 
 -- 字符串函数
 length(string)            -- string长度，字节
 char_length(string)        -- string的字符个数
 substring(str, position [,length])        -- 从str的position开始,取length个字符
 replace(str ,search_str ,replace_str)    -- 在str中用replace_str替换search_str
 instr(string ,substring)    -- 返回substring首次在string中出现的位置
 concat(string [,...])    -- 连接字串
 charset(str)            -- 返回字串字符集
 lcase(string)            -- 转换成小写
 left(string, length)    -- 从string2中的左边起取length个字符
 load_file(file_name)    -- 从文件读取内容
 locate(substring, string [,start_position])    -- 同instr,但可指定开始位置
 lpad(string, length, pad)    -- 重复用pad加在string开头,直到字串长度为length
 ltrim(string)            -- 去除前端空格
 repeat(string, count)    -- 重复count次
 rpad(string, length, pad)    --在str后用pad补充,直到长度为length
 rtrim(string)            -- 去除后端空格
 strcmp(string1 ,string2)    -- 逐字符比较两字串大小

5.2、聚合函数

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值

查询一个表中有多少记录
SELECT COUNT(`birthday`) FROM student;-- count(字段),会忽略所有的null值
SELECT COUNT(*) FROM student; -- count(*)，不会忽略null值   本质：计算行数 查全部列
SELECT COUNT(1) FROM student; -- count(1)，不会忽略null值   本质：计算行数 查第一列

5.3、数据库级别的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,'kuangshen','123456'),(2,'qinjiang','456789')
  
-- 如果我们要对pwd这一列数据进行加密，语法是：
update testmd5 set pwd = md5(pwd);

-- 如果单独对某个用户(如kuangshen)的密码加密：
INSERT INTO testmd5 VALUES(3,'kuangshen2','123456')
update testmd5 set pwd = md5(pwd) where name = 'kuangshen2';

-- 插入新的数据自动加密
INSERT INTO testmd5 VALUES(4,'kuangshen3',md5('123456'));
-- 查询登录用户信息（md5对比使用，查看用户输入加密后的密码进行比对）
SELECT * FROM testmd5 WHERE `name`='kuangshen' AND pwd=MD5('123456');

6、事务

6.1、什么是事务

要么都成功，要么都失败

什么是事务

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

6.2、事务的原则

事务的ACID原则 百度 ACID

原子性(Atomic)

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

一致性(Consist)

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

隔离性(Isolated)

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

持久性(Durable)

• 事务一旦提交则不可逆转，被持久化到数据库
• 表示事务结束后的数据不会随着外界原因导致数据丢失
• 在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
• 事务没有提交，恢复到原状
• 事务已经提交，持久化到数据库

6.3、事务的隔离级别

脏读

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

不可重复读

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

幻读

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

6.4、事务语法

基本语法

-- 使用set语句来改变自动提交模式
SET autocommit = 0;   /*关闭*/
SET autocommit = 1;   /*开启，默认*/

-- 注意:
--- 1.MySQL中默认是自动提交
--- 2.使用事务时应先关闭自动提交

-- 开始一个事务,标记事务的起始点
START TRANSACTION  

-- 提交一个事务给数据库
COMMIT

-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK

-- 还原MySQL数据库的自动提交
SET autocommit =1;

-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点

测试

/*
课堂测试题目

A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000

创建数据库shop和创建表account并插入2条数据
*/

CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;

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

INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)

-- 转账实现
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION;  -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
U PDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务
# rollback;
SET autocommit = 1; -- 恢复自动提交

7、索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

7.1、索引的分类

• 主键索引（PRIMARY KEY）
  • 唯一标识，不可重复
  • 最常见的索引类型
  • 确保数据记录的唯一性
  • 确定特定数据记录在数据库中的位置
• 唯一索引 （UNIQUE KEY）
  • 避免同一个表中某数据列中的值重复
  • 可以多个列标识为唯一索引
• 常规索引 （KEY/INDEX）
  • 默认的，index/key 关键字来设置
  • 快速定位特定数据，提高这个字段的查询效率
• 全文索引 （FULLTEXT）
  • 快速定位数据
  • 只能用于CHAR , VARCHAR , TEXT数据列类型
  • 适合大型数据集

/*
#方法一：创建表时
  　　CREATE TABLE 表名 (
               字段名1 数据类型 [完整性约束条件…],
               字段名2 数据类型 [完整性约束条件…],
               [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
               [索引名] (字段名[(长度)] [ASC |DESC])
               );


#方法二：CREATE在已存在的表上创建索引
       CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
                    ON 表名 (字段名[(长度)] [ASC |DESC]) ;


#方法三：ALTER TABLE在已存在的表上创建索引
       ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                            索引名 (字段名[(长度)] [ASC |DESC]) ;
                           
                           
#删除索引：DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;


#显示索引信息: SHOW INDEX FROM student;
*/

/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);

/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';

/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');

/*
开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况

MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
*/

7.2、索引原则

索引准则

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

索引的数据结构

-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

8、用户管理

使用SQLyog 创建用户，并授予权限演示

基本命令

/* 用户和权限管理 */ ------------------
用户信息表：mysql.user

-- 刷新权限
FLUSH PRIVILEGES

-- 增加用户 CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
  - 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
  - 只能创建用户，不能赋予权限。
  - 用户名，注意引号：如 'user_name'@'192.168.1.1'
  - 密码也需引号，纯数字密码也要加引号
  - 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD

-- 重命名用户 RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user

-- 设置密码
SET PASSWORD = PASSWORD('密码')    -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码')    -- 为指定用户设置密码

-- 删除用户 DROP USER kuangshen2
DROP USER 用户名

-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
  - all privileges 表示所有权限
  - *.* 表示所有库的所有表
  - 库名.表名 表示某库下面的某表

-- 查看权限   SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用户名
   -- 查看当前用户权限
  SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();

-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名    -- 撤销所有权限

权限解释

-- 权限列表
ALL [PRIVILEGES]    -- 设置除GRANT OPTION之外的所有简单权限
ALTER    -- 允许使用ALTER TABLE
ALTER ROUTINE    -- 更改或取消已存储的子程序
CREATE    -- 允许使用CREATE TABLE
CREATE ROUTINE    -- 创建已存储的子程序
CREATE TEMPORARY TABLES        -- 允许使用CREATE TEMPORARY TABLE
CREATE USER        -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW        -- 允许使用CREATE VIEW
DELETE    -- 允许使用DELETE
DROP    -- 允许使用DROP TABLE
EXECUTE        -- 允许用户运行已存储的子程序
FILE    -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX     -- 允许使用CREATE INDEX和DROP INDEX
INSERT    -- 允许使用INSERT
LOCK TABLES        -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS     -- 允许使用SHOW FULL PROCESSLIST
REFERENCES    -- 未被实施
RELOAD    -- 允许使用FLUSH
REPLICATION CLIENT    -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE    -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT    -- 允许使用SELECT
SHOW DATABASES    -- 显示所有数据库
SHOW VIEW    -- 允许使用SHOW CREATE VIEW
SHUTDOWN    -- 允许使用mysqladmin shutdown
SUPER    -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE    -- 允许使用UPDATE
USAGE    -- “无权限”的同义词
GRANT OPTION    -- 允许授予权限


/* 表维护 */

-- 分析和存储表的关键字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
-- 检查一个或多个表是否有错误
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- 整理数据文件的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

9、MySQL备份

数据库备份必要性

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

MySQL数据库备份方法

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

mysqldump客户端

作用 :

• 转储数据库
• 搜集数据库进行备份
• 将数据转移到另一个SQL服务器,不一定是MySQL服务器

-- 导出
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
　　mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)

可以-w携带备份条件

-- 导入
1. 在登录mysql的情况下：-- source D:/a.sql
　　source 备份文件
2. 在不登录的情况下
　　mysql -u用户名 -p密码 库名 < 备份文件

10、三大范式

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

不合规范的表设计会导致的问题：

• 信息重复

• 更新异常

• 插入异常

• • 无法正确表示信息

• 删除异常

• • 丢失有效信息

三大范式

第一范式 (1st NF)

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

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

第二范式(2nd NF)

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

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

第三范式(3rd NF)

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

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

规范化和性能的关系

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

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

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

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

11、JDBC

url：mysql://localhost:3306/数据库名?useUnicode=true&characterEncoding=utf8&useSSL=true