MYSQL基础语法 快速掌握sql语句

MYSQL

1、 操作数据库

命令行操作数据库

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

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

查看数据库 : show databases;

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

创建数据表

属于DDL的一种，语法 :

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

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

-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
-- 创建表之前 , 一定要先选择数据库

CREATE TABLE IF NOT EXISTS `student` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`address` varchar(100) DEFAULT NULL COMMENT '地址',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

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

ALTER 修改表

ALTER TABLE `student` rename as `student1`

ALTER TABLE `student` add age int(11)

ALTER TABLE `student` change age age1 int(11) 

字段重命名

ALTER TABLE `student` modify  age1 varchar(2)

字段的约束和类型

ALTER TABLE `student`  drop age1 

删除字段
删除表

Drop table if exists `student`
 

1、DML语言

外键

--增加外键
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;

但是一般是在程序级来实现外键,所以了解即可

INSERT

INSERT INTO student (NAME) VALUEs('张三')
INSERT INTO student (NAME) VALUEs('张三'),('张1'),('张2')
INSERT INTO student (NAME,age) VALUEs('张三','11'),

UPDATE

UPDATE `student` SET `name`='张鑫' WHERE `id`=1

delete 命令

 delete from `student` where id=`1`

truncate 命令

 truncate `student` --相当于初始化表  会把自增清0

2 、 DQL语言

SELECT语法

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

注意 : [ ] 括号代表可选的 , { }括号代表必选得

where 和 having的区别一个是位置不同，一个是where不可以过滤聚合函数，having可以。

2.1 select

--查询所有的信息
select * from `表名`

--查询特定字段信息
select `字段名` from `表名`

--查询后去重 distinct
select distinct `字段名` from `表名`

--查询后连接 concat（）
select concat('姓名： ',`字段名` ) from `表名`

--可以查询后进行计算
select `字段名`+1 from `表名`

2.2 as

--给查询的字段起别名
select `字段名` as `别名` from `表名`

--给查询的表起别名  
select `字段名` as `别名` from `表名` as `别名`

2.3 where

检索数据中符合条件的值

逻辑运算符查询

--- > < 大于 或者小于10 or and
select `字段名`  from `表名` where  `字段名` < > 10

-- between and  模糊查询
select `字段名`  from `表名` where between 1 and 2

--  = != 各种逻辑运算
select `字段名`  from `表名` where = !=  10

模糊查询

运算符
is null	查询是否为空
is not null	查询是否不为空
like % -	%是1~多个字符 -1个字符
in	在（值1 , 值2） 中
between	在 x 和 y 间的值

2.4 联表查询 join on

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CkP2IF3S-1618561923466)( image-20201104152427422.png)]

Join on

语句	描述
inner join on	查询两个表中的结果集中的交集
right join on	(以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
left join on	(以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)

--内连接
SELECT `name`,`gradenaem` 
FROM `student` s
INNER JOIN `grade` AS g
ON s.`gradeid` = g.`gradeid`

--右连接
SELECT `name`,`gradenaem` 
FROM `student` s
RIGHT JOIN `grade` AS g
ON s.`gradeid` = g.`gradeid`

--左连接
SELECT `name`,`gradenaem` 
FROM `student` s
LEFT JOIN `grade` AS g
ON s.`gradeid` = g.`gradeid`

• 如果查询多个表的连接的话，依次连接两个两个表查

自连接

--建表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

--自连接
SELECT a.categoryName,b.categoryName FROM
`category` a,`category` b
WHERE a.categoryid=b.pid

• 相当于将一个表拆成2个表来查询 （前提是表里有可以连接的字段）

2.5 分页 limit 和排序 order by

排序

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

分页

/*============== 分页 ================
语法 : 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='数据库结构-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5

2.6 子查询

• 由里及外 很明显 使用子查询的话也可以用表连接查询

/*============== 子查询 ================
什么是子查询?
   在查询语句中的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'
  )
)

3、 MySql函数

3.1 常用函数

数据函数

 SELECT ABS(-8);  /*绝对值*/
 SELECT CEILING(9.4); /*向上取整*/
 SELECT FLOOR(9.4);   /*向下取整*/
 SELECT RAND();  /*随机数,返回一个0-1之间的随机数*/
 SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/

字符串函数

 SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/
 SELECT CONCAT('我','爱','程序');  /*合并字符串,参数可以有多个*/
 SELECT INSERT('我爱编程helloworld',1,2,'超级热爱');  /*替换字符串,从某个位置开始替换某个长度*/
 SELECT LOWER('KuangShen'); /*小写*/
 SELECT UPPER('KuangShen'); /*大写*/
 SELECT LEFT('hello,world',5);   /*从左边截取*/
 SELECT RIGHT('hello,world',5);  /*从右边截取*/
 SELECT REPLACE('狂神说坚持就能成功','坚持','努力');  /*替换字符串*/
 SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/
 SELECT REVERSE('狂神说坚持就能成功'); /*反转
 
 -- 查询姓周的同学,改成邹
 SELECT REPLACE(studentname,'周','邹') AS 新名字
 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 VERSION();  /*版本*/
 SELECT USER();     /*用户*/

3.2 聚合函数

函数名称	描述
COUNT()	返回满足Select条件的记录总和数，如 select count(*) 【不建议使用 *，效率低】
SUM()	返回数字字段或表达式列作统计，返回一列的总和。
AVG()	通常为数值字段或表达列作统计，返回一列的平均值
MAX()	可以为数值字段，字符字段或表达式列作统计，返回最大的值。
MIN()	可以为数值字段，字符字段或表达式列作统计，返回最小的值。

 -- 聚合函数
 /*COUNT:非空的*/
 SELECT COUNT(studentname) FROM student;
 SELECT COUNT(*) FROM student;
 SELECT COUNT(1) FROM student;  /*推荐*/
 
 -- 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。
 -- count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
 -- count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录；
 -- count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录 。
 /*
 很多人认为count(1)执行的效率会比count(*)高，原因是count(*)会存在全表扫描，而count(1)可以针对一个字段进行查询。其实不然，count(1)和count(*)都会对全表进行扫描，统计所有记录的条数，包括那些为null的记录，因此，它们的效率可以说是相差无几。而count(字段)则与前两者不同，它会统计该字段不为null的记录条数。
 
 下面它们之间的一些对比：
 
 1）在表没有主键时，count(1)比count(*)快
 2）有主键时，主键作为计算条件，count(主键)效率最高；
 3）若表格只有一个字段，则count(*)效率较高。
 */
 
 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..
 因为having是从前面筛选的字段再筛选，而where是从数据表中的>字段直接进行的筛选的
 */

MD5 加密

一、MD5简介

MD5即Message-Digest Algorithm 5（信息-摘要算法5），用于确保信息传输完整一致。是计算机广泛使用的杂凑算法之一（又译摘要算法、哈希算法），主流编程语言普遍已有MD5实现。将数据（如汉字）运算为另一固定长度值，是杂凑算法的基础原理，MD5的前身有MD2、MD3和MD4。

二、实现数据加密

新建一个表 testmd5

 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');

小结

 -- ================ 内置函数 ================
 -- 数值函数
 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)    -- 逐字符比较两字串大小
 
 -- 聚合函数
 count()
 sum();
 max();
 min();
 avg();
 group_concat()
 
 -- 其他常用函数
 md5();
 default();

4、事务

要么都成功，要么都失败

事务的原则 ACID

原子性

• 整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。

例如：A有400，B 有600。 如果A给B转账200元

​ A-200=200

​ B+200=800

上述包含两个操作，但是是不能被才开的，原子就是把它看成一个操作

一致性

• 一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。

和上面的例子一样

A和B转账操作后，数额1000在操作前后都是一致的。

比如A在提交事务后突然宕机了，恢复后A还是减少了（因为提交了事务） 如果没有提交就不会减少

隔离性

• 两个事务在进行操作的时候是隔离的，不会相互影响

A给B转钱 C给B转钱 B有200 ，A给B转了100后，B变成300，如果没有隔离性的话C会读到B有200，最后B少了100 出现问题就是脏读

持久性

• 在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

基本语法

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

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

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

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

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

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

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

测试

-- 建库
CREATE DATABASE bank CHARACTER SET =utf8 COLLATE utf8_general_ci;
USE bank;

CREATE TABLE `count` (
id INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL ,
money DECIMAL(9,2) NOT NULL DEFAULT 0.00,
PRIMARY KEY(`id`)
)ENGINE =INNODB DEFAULT CHARSET =utf8

INSERT INTO `count`(`name`,`money`)VALUES('A',800.00),('B',200.00)

-- 关闭自动提交
SET autocommit = 0;
-- 事务开始
START TRANSACTION;

UPDATE `count` SET `money`=`money`-400 WHERE`name`='A';
UPDATE `count` SET `money`=`money`+400 WHERE`name`='B';

-- 回滚
ROLLBACK
-- 提交
COMMIT
SET autocommit = 1;

5、 索引

底层原理 https://www.jianshu.com/p/b49d23a9ccb8

索引的作用

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

分类

• 主键索引 (Primary Key)
• 唯一索引 (Unique)
• 常规索引 (Index)
• 全文索引 (FullText)

主键索引

主键 : 某一个属性组能唯一标识一条记录

特点 :

• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置

··

作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别

• 主键索引只能有一个
• 唯一索引可能有多个

CREATE TABLE `Grade`(
  `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
  `GradeName` VARCHAR(32) NOT NULL UNIQUE
   -- 或 UNIQUE KEY `GradeID` (`GradeID`)
)

常规索引

作用 : 快速定位特定数据

注意 :

• index 和 key 关键字都可以设置常规索引
• 应加在查询找条件的字段
• 不宜添加太多常规索引,影响数据的插入,删除和修改操作

CREATE TABLE `result`(
   -- 省略一些代码
  INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)
-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

全文索引

百度搜索：全文索引

作用 : 快速定位特定数据

注意 :

• 只能用于MyISAM类型的数据表
• 只能用于CHAR , VARCHAR , TEXT数据列类型
• 适合大型数据集

索引准则

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

索引的数据结构

-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
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 等索引；

拓展：测试索引

建表app_user：

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 NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

批量插入数据：100w

DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
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), '24736743@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_data();

索引效率测试

无索引

SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999';
SELECT * FROM app_user WHERE name = '用户9999';

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ALL
possible_keys: NULL
        key: NULL
    key_len: NULL
        ref: NULL
        rows: 992759
    filtered: 10.00
      Extra: Using where
1 row in set, 1 warning (0.00 sec)

创建索引

CREATE INDEX idx_app_user_name ON app_user(name);

测试普通索引

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ref
possible_keys: idx_app_user_name
        key: idx_app_user_name
    key_len: 203
        ref: const
        rows: 1
    filtered: 100.00
      Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

6、权限管理

基本命令

/* 用户和权限管理 */ ------------------
用户信息表：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] ...

7、MySQL备份

MySql备份的方式

• 直接备份物理文件
• 使用命令行导出 mysqldump

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密码 库名 < 备份文件

8、规范数据库设计

为什么需要设计数据库？

糟糕的数据库

• 数据冗余，浪费空间

• 数据库插入和删除会很麻烦甚至出现异常 【屏蔽使用外键】

• 程序的性能差

良好的数据库设计：

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

数据库的设计步骤【个人博客】

• 收集信息 分析需求

  • 用户表
  • 分类表
  • 评论表（使用自连接可以实现评论的嵌套）
  • 自定义表（系统信息，某个关键字）
  • 。。。

• 标识实体（把需求落地到每个字段）

• 标识实体之间的关系

  • 写博客 ： user - blog

  • 。。。。

9、三大范式

为什么需要数据规范化？

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

• 信息重复

• 更新异常

• 插入异常

• • 无法正确表示信息

• 删除异常

• • 丢失有效信息

三大范式

三大范式

第一范式 (1st NF)

第一行是的目标就是确保数据列的原子性，确保每列的信息不可再分

例： 家庭信息： 5人 漳州 ； 我们可以分为家庭人口 和 家庭住址两个列

第二范式(2nd NF)

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

第二范式需要满足每个表字描述一件事

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关 甚至一点关系都没有

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aMi4ESA0-1618561923471)( image-20201106153131808.png)]

可以看到上面这个表 主键是 订单号和产品号 ，但是订单金额和订单时间只和订单号相关，与产品号无关，所以只是部分相关。

第三范式(3rd NF)

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

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

规范化和性能的关系

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

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

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

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

10、JDBC

import com.mysql.jdbc.Connection;

import java.sql.*;

public class jdbctest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
     //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        String url= "jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8&useSSL=true";
        //jdbc：mysql：mysql://localhost:3306/`数据库名字`？useUnicode=true&characterEncoding=utf8&userSSL=true;
        String username="root";
        String password="773080152";
        //连接成功 Connection代表数据库对象
        Connection connection = (Connection) DriverManager.getConnection(url, username, password);
        //Statement执行sql语句的对象
        Statement statement =connection.createStatement();
        String sql = "SELECT * FROM student WHERE id>1";
        ResultSet resultSet = statement.executeQuery(sql);
        while(resultSet.next()){
            System.out.println(resultSet.getObject("id"));
            System.out.println(resultSet.getObject("name"));
            System.out.println(resultSet.getObject("sex"));
        }
        //释放资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结：

• 加载驱动
• 连接数据库 DriverManager
• 获取执行sql的对象 Statement
• 获得返回的结果集
• 释放连接

DriverManager

class.forName("com.mysql.jdbc.Driver"); //固定语句加载驱动
 Connection connection = (Connection) DriverManager.getConnection(url, username, password);

//connection 代表数据库
//数据库设置自动提交
//事务回滚
        connection.commit();
        connection.getAutoCommit();
        connection.rollback();

URL

 String url= "jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8&useSSL=true";
// jdbc:mysql://主机地址:端口号/数据库?参数1&参数2&参数3
 // mysql --3306
 // oracle -- 1521
//jdbc：oracle：thin：@localhos:1521:sid

Starement 执行SQL对象

statement.executeQuery();//执行查询语句
statement.executeUpdate();//执行 增加 删除 修改
statement.execute(); //任何sql语句都能

resultSet 查询的结果集

  resultSet.getObject();
  resultSet.getArray();
  resultSet.getInt();
//遍历
 resultSet.next();

Statement 对象

Statement 对象用于向数据库发送SQL语句，想完成对数据库的增删改查只要通过这个对象想数据库发送SQL语句就可以

创建工具类

// 配置文件 这里我放在src文件夹下找不到配置文件，放在resource文件夹下才行
db.properties

driver = com.mysql.jdbc.Driver
url= jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=773080152

// 工具类

package Util;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;


public class jdbcUtil {
    private static String driver = null;
    private static String username = null;
    private static String password = null;
    private static String url = null;
    static {//初始化
  
        try {
            InputStream in = jdbcUtil.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            System.out.println(driver);
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            url = properties.getProperty("url");

            Class.forName(driver); //加载驱动
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getconnection() throws ClassNotFoundException, SQLException {
        return  DriverManager.getConnection(url,username,password);
    }
    public static void release(Connection connection,Statement statement,ResultSet resultSet) throws SQLException {
       try{
           if(connection!=null)
            connection.close();
           if(statement!=null)
        statement.close();
           if(resultSet!=null)
        resultSet.close();} catch (SQLException throwables) {
           throwables.printStackTrace();
       }

    }
}


//测试
import Util.jdbcUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class insert {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        String sql="INSERT INTO `student`.`student` (`birthday`) VALUES ('2020-11-12 20:43:43') ";
        try{
            connection  = jdbcUtil.getconnection();
            statement = connection.createStatement();
            if(statement.executeUpdate(sql)>0){
                System.out.println("插入成功");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            jdbcUtil.release(connection,statement,resultSet);
        }

    }
}

SQL注入问题

https://blog.csdn.net/weixin_43915762/article/details/87909751

PraparedStatement

• 插入

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tUoiGMW0-1618561923474)( image-20201107211301242.png)]

• 删除

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UBIF0VQs-1618561923475)( image-20201107211350484.png)]

本质：把传递进来的的参数再次包装成字符，其中转义字符被转义，比如`

JDBC操作事务

ACID原则

• 原子性

  要么都成功要么都失败

• 一致性

  钱都一样，不会变多也不会变少

  比如A在提交事务后突然宕机了，恢复后A还是减少了（因为提交了事务） 如果没有提交就不会减少

• 隔离性

  事务之间不会互相干扰

​ 幻读

​ 脏读

• 持久性 永久性存在数据库中 改变后无法回滚

代码实现

public class shiwu {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        String sql="INSERT INTO `student`.`student` (`birthday`) VALUES ('2020-11-12 20:43:43') ";
        try{

            connection  = jdbcUtil.getconnection();
            statement = connection.createStatement();
            connection.setAutoCommit(false);//开启事务

            statement.executeUpdate("UPDATE bank.count t SET t.money = money-200.00 WHERE t.id = 1");
            int i=1/0;//报错 就回滚
            statement.executeUpdate("UPDATE bank.count t SET t.money = money+200.00 WHERE t.id = 2");
            connection.commit();
            System.out.println("成功");
        } catch (Exception e) {
            try {
                connection.rollback();//回滚
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

            e.printStackTrace();
        }
        finally {
            jdbcUtil.release(connection,statement,resultSet);
        }

    }
}

数据库连接池

池化技术 ： 准备一些预先的资源，过来就连接预先准备好的

最小连接数 ： 10个

最大连接数 ：15

等待超时： 100ms

编写连接池 实现一个接口 DataSouce

开源数据实现

DBCP

CP30

Druid：阿里

使用这些数据库连接池之后我们就不用在开发项目中编写数据库连接的代码了

DBCP

• 隔离性

  事务之间不会互相干扰

​ 幻读

​ 脏读

• 持久性 永久性存在数据库中 改变后无法回滚

代码实现

public class shiwu {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        String sql="INSERT INTO `student`.`student` (`birthday`) VALUES ('2020-11-12 20:43:43') ";
        try{

            connection  = jdbcUtil.getconnection();
            statement = connection.createStatement();
            connection.setAutoCommit(false);//开启事务

            statement.executeUpdate("UPDATE bank.count t SET t.money = money-200.00 WHERE t.id = 1");
            int i=1/0;//报错 就回滚
            statement.executeUpdate("UPDATE bank.count t SET t.money = money+200.00 WHERE t.id = 2");
            connection.commit();
            System.out.println("成功");
        } catch (Exception e) {
            try {
                connection.rollback();//回滚
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

            e.printStackTrace();
        }
        finally {
            jdbcUtil.release(connection,statement,resultSet);
        }

    }
}

数据库连接池

池化技术 ： 准备一些预先的资源，过来就连接预先准备好的

最小连接数 ： 10个

最大连接数 ：15

等待超时： 100ms

编写连接池 实现一个接口 DataSouce

开源数据实现

DBCP

CP30

Druid：阿里

使用这些数据库连接池之后我们就不用在开发项目中编写数据库连接的代码了

DBCP