MySql教程

MySQL学习

本文主要是介绍MySQL学习,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

MySQL

学习网站:https://www.bilibili.com/video/BV1NJ411J79W?from=search&seid=1914316698934444374&spm_id_from=333.337.0.0

什么是数据库

简称:DB(DataBase)

概念:数据仓库,软件,安装在操作系统上(window,linux,mac),主要是学SQL,可以存储大量的数据。

作用:存储数据、管理数据

数据库在物理空间存在的位置

所有的数据库文件都存在data目录下,一个文件夹对应一个数据库

本质上还是文件的存储

数据库的分类

关系型数据库:(SQL)

  • MySQL、Oracle、SQL、DB2、SQLlite
  • 通过表与表之间、行与列之间的关系进行数据的存储,

非关系型数据库:(NoSQL-----不仅仅是SQL)

  • Redis、MongDB
  • 通过对象存储,通过对象的自身属性决定

DBMS(数据库管理系统)

  • 数据库的管理软件、科学有效的管理、维护、获取数据

MySQL

  • 关系型数据库管理系统、
  • 最好的RDBMS应用软件之一
  • 开源的数据库软件
  • 体积小、速度快,成本低

MySQL通过cmd窗口启动和关闭

关闭:

​ net stop mysql(这个是你的服务名)

开启

​ net start mysql(同上)

服务名查询:

  1. 右击此电脑
  2. 管理
  3. 服务与应用程序
  4. 服务
  5. 找到mysql(或者是其他的,一般都是mysql+....)

连接数据库

mysql -u root -p(密码)		————cmd连接数据库
    
update mysql.user set authentication_string=password('密码') where user='root' and Host = 'localhost';
																		————修改用户密码
————————————————————————————————————————————————
	1. 在cmd中,使用数据库的所有语句必须使用";"结尾
    2. show databases;		————查看所有数据库
    3. 输入: user school	  ————切换数据库 user 数据库名
       下一行跳出: Database changed
    4. show tables;			————查看数据库所有表
    5. describe *****(表的名字)————显示数据库中表的所有信息
    6. cerate database ****(数据库的名字)————创建一个数据库
    7. exit;				————退出连接
    8. "--"	单行注释
    9. "/**/" 多行注释    

操作数据库

**mysql关键字不区分大小写 **

创建数据库

CREATE DATABASE [IF NOT EXISTS] ***(库名)
[]内的是可选的

删除数据库

DROP DATABASE [IF EXISTS] ****(库名)

使用数据库

-- "`",tab键的上面,如果表明或者字段名是一个特殊字符,需要带"`"
USER `***(库名)`

查看数据库

SHOW DATABASES 		查看所有的数据库

数据库中的列类型

数值:

  • tinyint ————十分小的数据,占一个字节
  • smallint ————较小的数据,占2个字节
  • mediumint ————中等大小的数据,占3个字节
  • int ————标准整数,4个字节
  • big ————较大的数据,占8个字节
  • float ————浮点数,占4个字节
  • double ————浮点数,占8个字节
  • decimal ————字符串形式的浮点数,金融计算是常用

字符串:

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

时间日期:

  • date ————YYYY-MM--DD,日期格式
  • time ————HH:mm:ss,时间格式
  • date ————YYYY-MM-DD-HH:mm:ss,最常用的时间格式
  • timestamp ————时间戳,1970.1.1到现在的毫秒数!(全球统一),较为常用
  • year ——年

null:

  • 没有值,未知的
  • ==注意,不要使用NULL进行运算,否则结果一定为NULL

数据库的字段属性

Unsign

  • 无符号的证书
  • 声明了该列不能声明为负数

Zerofill

  • 0填充
  • 不足的位数使用0填充,int(3[此处表示长度]), 5——>005

自增

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

非空(NUll not null)

  • 假设设置为not null,如果不给它赋值,会报错
  • NUll,如果不填写,默认是null

默认

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

每一个表必须存在以下5个字段(项目)

  1. id-----主键
  2. version“ --------乐观锁
  3. is_delect-------伪删除
  4. gmt_create------创建时间
  5. gmt_update------修改时间

创建一个数据库表

表名:student

学号:int,姓名:varchar,性别sex,出生日期birthday

CREATE TABLE IF NOT EXISTS `student` (
`ID` INT(15) NOT NULL AUTO_INCREMENT COMMENT '学号',
`NAME` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期', 
PRIMARY KEY(`ID`)
)ENGINE`student`=INNODB DEFAULT CHARSET=utf8

格式:

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

常用命令

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

关于数据库表的类型

关于数据库的引擎

INNODB

  • 默认使用
  • 节约空间
  • 速度较快

MYISAM(早些年使用)

  • 早些年使用
  • 安全性高
  • 事务处理,多表多用户操作
MYISAM INNODB
事务支持 不支持 支持
数据行锁定 不支持(表锁) 支持
外键 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大,约为MYISAM两倍

MYSQL引擎在物理文件的区别

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

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

CHARSET=UTF8
  • 不设置的话,会是mysql默认的字符集编码——(不支持中文)
  • MySQL的默认编码是Latin1,不支持中文
  • 在my.ini中配置默认的编码

character -set-server=utf8

修改、删除表

修改

  • 修改表名
ALTER TABLE 旧表名 RENAME AS 新表名
  • 增加表的字段
ALTER TABLE 表名 ADD 字段名 列属性
  • 修改表的字段(重命名、修改约束!)
ALTER TABLE *****(表名) MODIFY **(字段名) **(列属性) ---修改约束
ALTER TABLE *****(表名) CHANGE ***(旧名字) ***(新名字) ***(列属性)  --字段重命名

​ 结论:

  • CHANGE用来字段重命名,不能修改字段类型和约束

  • MODIFY不能用于字段,只能修改字段类型和约束

  • 删除表的字段
ALTER TABLE ***(表名) DROP ***(列属性)
  • 删除表
DROP TABLE IF EXISTS ****(表名)
创建和删除操作尽量加上判断,以免报错

注意点

  • `` 字段名,使用其包裹起来
  • 注释:“--”、/**/
  • sql大小写不敏感
  • 所有的符号用英文

MySQL数据管理

外键(物理外键——数据库级别)

方式一

在创建表的时候增加约束(麻烦、复杂)

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


CREATE TABLE `student`(
`ID` INT(15) NOT NULL AUTO_INCREMENT COMMENT '学号',
`NAME` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`SEX` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`BIRTHDAY` DATETIME 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

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

方式二

创建表成功后在添加外键约束

创建表是没有外键,后续添加外键关系公式:
ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES `哪一个表名`(`哪个字段`)

最佳实践:

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

DML语言

添加

定义:数据操作语言

-- 插入语句语法:
INSERT INTO 表名([字段名1,字段2,字段3])VALUES('值1'),('值2'),('值3')

注意事项:

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

修改

update 修改谁? (条件) set原来的值=新值

-- 语法:
UPDATE `表名` SET COLNUM_NAME = VALUE,[COLNUM_NAME=VALUE....] WHERE [条件]

条件:where子句运算符,例如:id等于某个值、大于某个值,在某个区间内修改......

操作符会返回布尔值

操作符 含义 范围 结果
= 等于 5=6 false
<>或!= 不等于 5<>3 true
> 大于 5>3 true
< 小于 5<3 false
<= 小于等于 2<=3 true
>= 大于等于 3>=2 true
BETWEEN...AND 在某个范围内 [2,5]
AND &&(我和你) 5>1and1>2 false
OR ||(我或你) 5>1or 1>2 true

注意事项:

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

删除

DELETE 命令

语法:

DELETE FROM ***(表名) [WHERE 条件]

TRUNCATE 命令

语法:

TRUNCATE ***(表名)

作用:

完全清空一个数据库表,表的结构和索引约束不变!

DELETE 与 TRUNCATE 的区别

  • 相同点:
    • 都能上次删除数据
    • 都不会删除表的结构
  • 不同点
    • TRUNCATE 重新设置自增列,计数器归0
    • TRUNCATE 不会影响事务
CREATE TABLE `teacher`(
`id` INT(8) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `teacher`(`name`) VALUE('1'),('2'),('3')

DELETE FROM `teacher`    --不会影响自增

TRUNCATE TABLE `teacher`   --自增归0

DELETE删除的问题,重启数据库的现象:

  • INNODB,自增列会从1开始 (存在内存当中,断电即失)
  • MyISAM, 继续从上一个自增量开始(存在文件中的,不会丢失)

DQL

DQL查询数据

查询
  • 所有的查询操作都用它
  • 简单、复杂的查询都可以完成
  • 数据库中最核心的语言,最核心的语句
  • 使用频率最高的语句
查询全部字段:
SELECT * FROM *****(表名)
查询指定字段
SELECT ` * `(表中字段),` * `,.... FROM ***(表名)
别名,给结果起一个名字(字段或表)
SELECT ` * `(表中字段) AS ***(别名),` * ` AS ***(别名),.... FROM ***(表名) AS ****(别名)
函数 CONCAT(A,B)
SELECT CONCAT('**(连接的字符串)',****(表中字段)) AS ***(别名) FROM ****(表名)
注意:
起别名只在自己展示界面窗口那边展示别名,但是重新打开表后,展示的不是别名而是自己刚开始定义的变量名;也就是说,别名只是我们在查询的时候看的。
CONCAT中的A,使用英文单引号框起来的,不是``!。
语法:
SELECT ***(字段) .....FROM ****(表)
有的时候,列名字不是那么见名知意,我们起别名 AS: 字段名 AS 别名 表名 AS 别名
去重 distanct
作用:
去除SELECT查询结果中重复的数据
语法:
SELECT DISTANCT `表中字段` FROM `表名`
SELECT * FROM `result`     --查询全部

SELECT `studentno` FROM result		--查询某一列

SELECT DISTINCT `studentno` FROM result   --去重
数据列的表达式:
SELECT VERSION()   --查询系统版本(函数)
SELECT 100*3-1 AS 计算结果   --用于计算(表达式)
SELECT @@AUTO_INCREMENT_INCREMENT --查询自增步长
SELECT `表中字段`+1  AS '别名' FROM 表名

数据库中的表达式:

  • 文本值
  • NULL
  • 函数
  • 计算表达式
  • 系统变量

SELECT + 表达式

where条件子句

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

搜索的结果由一个或者多个表达式组成! 结果 布尔值

逻辑运算符:

运算符 语法 描述
and && A and B A&&B 逻辑与,两个都为真,结果为真
or || A or B A||B 逻辑或,一个为真,结果为真
Not ! NOT A !A 逻辑非,真为假,假为真
模糊查询

比较运算符

运算符 语法 描述
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,则结果为真
IN A IN(A1,A2,A3.....) 假设a在a1,或者a2......其中的某一个值中,结果为真

‘ **% ** ’ ‘ _ ’:

  • %:代表0到任意个字符
  • _:代表一个字符
SELECT `表中字段`, `表中字段` ,....FROM `表名`
WHERE `表中字段` LIKE '*(模糊查询的内容)%'
注:出现的内容为以*开头,后面的长度不限


SELECT `表中字段`, `表中字段` ,....FROM `表名`
WHERE `表中字段` LIKE '*(模糊查询的内容)_'
注:出现的内容以*开头,后面只有一个字符,如果后面又两个字符则用两个__,或者用%

说明:
_    % 可以放在模糊查询的前面,也可以放在后面,根据自己的需求进行代码的书写。

IN

  • 后面加一个或者多个具体的值
SELECT `表中字段`, `表中字段` FROM `表名`
WHERE `表中字段` IN ('***(查询的具体内容)',`****`.....);

NULL NOT NULL

查询表中字段1为空的值
SELECT `表中字段`, `表中字段` FROM `表名`
WHERE 表中字段1='' OR 表中字段1 IS NULL

查询表中字段1不为空的值
SELECT `表中字段`, `表中字段` FROM `表名`
WHERE 表中字段1 IS NOT NULL
联表查询

img

操作 描述
INNER JOIN 如果表中至少有一个匹配,返回行
LEFT JOIN 会从左表中返回所有的值,即使右表中没有匹配
RIGHT JOIN 会从右表中返回所有的值,即使左表中没有匹配

JOIN(连接的表) ON(判断的条件) -- 连接查询

WHERE --等值查询

自连接

定义:自己与自己的表连接

核心:一张表拆分成两张一样的表

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

查询弗雷对应子类的关系

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

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_COUNTOFFSET OFFSET}];
	--指定查询记录从那条到那条
}
上述顺序不能变

注意:[]:表示可选,{}:表示必选

SELECT 去重 要查询的字段 from 表名(表和字段可以取别名)

XX JOIN 要连接的表 on 等值判断

where (具体的值, 子查询语句)

group by (通过那个字段来分组)

having (过滤分组后的信息,条件和where一样,位置不同)

order by ...(通过那个字段排序)【升序/降序】

limit startindex pagesize

--测试代码:
CREATE TABLE `test`(
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`pid` INT(10) NOT NULL,
`categoryName` VARCHAR(50) NOT NULL,
PRIMARY KEY (`categoryid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

SELECT t.`categoryName`, s.`categoryName` 
FROM `test` AS t, `test` AS s
WHERE s.pid=t.categoryid
分页和排序

排序:

升序:ASC

降序:DESC

语法:

ORDER BY `表中字段` ASC[DESC]

分页:

作用:缓解数据库压力,给人体验更好,避免瀑布流

语法:

LIMIT 起始值, 页面大小(pageSize)
计算:
--【pageSize:页面大小】
--【(n-1)*passageSize:起始值】
--【n:当前页】
--【数据总数/页面大小=总页数】
子查询

WHERE (这个值是计算出来的)

本质:

在WHERE语句中嵌套一个子查询语句

WHERE(SELECT FROM *)

由里及外

MySQL函数

常用函数

**常用函数:**
数学运算:
SELECT ABS(-8)     --取绝对值

SELECT CEILING(9.4)   --向上取整

SELECT FLOOR(9.4)    --向下取整

SELECT RAND()   --返回一个0-1之间的随机数

SELECT SIGN(0)   --判断一个数的符号,负数返回-1,正数返回1

字符串函数
SELECT CHAR_LENGTH('*****')   --字符串长度

SELECT CONCAT('**','**','**')   --拼接字符串

SELECT INSERT('****',1,2,'***')   --查询,从某个位置开始替换某个长度

SELECT LOWER('ADSAFSF')    --转换成小写

SELECT UPPER('qadaddf')    --转换成大写

SELECT INSER('***','*')    --返回第一次出现的子串的索引、

SELECT REPLACE('*****','****1','****2')  --替换出现的指定字符串

SELECT SUBSTR('******',4(开始位置),2(截取长度))  --截取原字符串

SELECT REVERSE('********')  --反转

时间和日期函数:
SELECT CURRENT_DATE()  --获取当前日期

SELECT CURDATE()  --获取当前日期

SELECT NOW()   --获取当前时间

SELECT LOCALTIME()  --返回本地时间

SELECT SYSDATE()   --返回系统时间

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

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

聚合函数

函数名称 描述
SUM() 求和
COUNT() 计数
AVG() 平均数
MAX() 最大值
MIN() 最小值
...... 其他
SELECT COUNTz(指定列,表中字段) FROM ***(表名)   --会忽略所有的null值

SELECT COUNT(*) FROM ***(表名)   --不会忽略null值,本质:计算行数

SELECT COUNT(1) FROM ***(表名)   --不会忽略所有的null值,本质:计算行数

数据库级别的MD5加密

MD5:增强算法复杂度和不可逆性

破解原理:网站后有一个字典,MD5加密后的值,加密的前值

CREATE TABLE `test`(
 `id` int(4) NOT NULL,
 `name` VARCHAR(20) NOT NULL,
 `pwd` VARCHAR(20) NOT NULL,
 PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

--明文密码
INSERT INTO test VALUES(1,'张','123456'),(2,'李','123456'),
(3,'王','123456')

--加密
UPDATE test SET pwd=MD5(pwd) WHERE id=1

UPDATE test SET pwd=MD5(pwd)---加密全部的密码
--插入时加密
INSERT INTO test VALUES(4,'XX',MD5('123456'))
-- 校验:将用户传递的密码进行MD5加密,然后比对加密后的值
SELECT * FROM test WHERE `name`='XX' AND pwd=MD5('123456')

事务

什么是事务:要么都成功,要么都失败

事务原则:

  • ACID原则(如下)
  • 原子性:两个步骤一起成功或者一起失败,不能只发生其中一个动作(要么都成功,要么都失败)
  • 一致性:针对一个事务操作前与操作后的状态一致
  • 隔离性:多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰
  • 持久性:事务结束后数据不会随着外界原因导致数据丢失,事务没有提交,便恢复到原状;事务提交便持久化到数据库中。事务一旦提交不可逆。

脏读:

定义:一个事务读取了另外一个事务未提交的数据

不可重复读:

定义:在一个事务内读取表中的某一行数据,多次读取结果不同(不一定错,场合不对)

虚读(幻读):

定义:一个事务内读取到别的事务插入的数据,导致前后读取不一致

MySQL默认开启事务自动提交
SET autocommit = 0    /*关闭*/
SET autocommit = 1    /*开启*/

--手动处理事务
START TRANSACTION   --标记一个事务的开始,从这个之后的sql都在同一个事务当中
INSERT XX
INSERT XX
(上面两行都成功才算成功,否则是失败的)

--提交:持久化(成功!)
COMMIT
--回滚:回到原来的样子(失败!)
ROLLBACK

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

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

索引

定义:帮助MySQL高效获取数据的数据结构,提取句子主干,九二一获得索引的本质:索引就是数据结构

索引的分类:

  • 主键索引:PRIMARY KEY
    • 唯一标识,不可重复,只能有一个列作为主键
  • 唯一索引:UNIQUE KEY
    • 避免重复的列出现,可以重复,多个列都可以标识位唯一索引
  • 常规索引:KEY/INDEX
    • 默认的,index、key关键字来设置
  • 全文索引:FULLTEXT
    • 在特定的数据库引擎下才有,MyISAM
    • 快速定位数据
--显示所有的索引信息:
SHOW INDEX FROM ***(表名)

--增加一个全文索引
ALTER TABLE ***(库名).****(表名) ADD FULLTEXT INDEX `******(索引名)`(`****(列名)`)

--EXPLAIN 分析sql执行状况
EXPLAIN SELECT * FROM ****(表名)   --非全文索引

EXPLAIN SELECT * FROM ***(表名) WHERE MATCH(****(列名)) AGAINST('全文索引')

索引测试


索引在小数据量师,用处差别不大,在大数据时差别很明显。

索引原则

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

索引的数据机构:

  • Hash类型的索引
  • Btree:INNODB的默认数据结构

权限管理和备份

用户管理

SQL命令操作:

用户表:mysql.user

本质:对这张表进行增删改查

--创建用户 
CREATE USER ***(用户名) IDENTIFIED BY '密码'
--修改当前用户的密码
SET PASSWORD = PASSWORD('******(密码)')
--修改指定用户的密码
SET PASSWORD FOR *****(用户名)= PASSWORD('******(密码)')

--用户重命名
RENAME USER ****(原名) TO *****(新名)
--用户授权命令,授予全部的权限,库.表(一般不用,用勾选),不能给别人授权
GRANT ALL PRIVILEGES ON *.*
--查询权限(指定用户)
SHOW GRANTS FOR ****(用户名)
SHOW GRANTS FOR root@localhost
--撤销权限  REVOKE 那些权限 在那个库 给谁撤销
REVOKE ALL PRIVILEGES ON *.*(指定的,否则是全局的) FROM ****(用户名)
--删除用户
DROP USER ****(用户名)

数据库的备份

  • 保证重要数据不丢失
  • 数据转移A-->B

MySQL数据库备份的方式:

  • 拷贝物理文件
  • 在可视化工具中手动导出
    • 在想要导出的库/表,右键选择备份
  • 使用命令行导出(mysqldump)
mysqldump -hlocalhost -uroot -p******(密码) **(库名) **(表名)>D:/...(导出位置)
mysqldump -h(主机) -u(用户名) -p(密码) 数据库 表1 表2 表3 ... >物理磁盘位置/文件名
#不加表名就是导出数据库

#导入
#登录的情况下,切换到指定的数据库
source ****(备份文件)

mysql -u用户名 -p密码 库名< 备份文件

数据库的设计

糟糕的数据库:

  • 数据冗余,浪费空间
  • 数据库插入和删除比较复杂,容易出现异常(屏蔽使用物理外键)
  • 程序的性能差

良好的数据库设计:

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

三大范式

为什么:

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

三大范式

  • 第一范式
    • 原子性:保证每一列不可再分
  • 第二范式
    • 前提满足第一范式
    • 每张表只描述一件事情
  • 第三范式
    • 前提满足第二范式
    • 确保数据表中每一列数据和主键直接相关,而不能间接相关

规范和性能的问题:

关联查询的表不能超过3张表

  • 考虑商业化的需求和目标(成本和目标体验),数据库的性能更重要
  • 在规范性能的问题的时候,适当考虑一下规范性
  • 故意增加某些表的冗余字段(从多张表查询变成单表查询)
  • 故意增加一些计算列(从大数据量降低为小数据量的查询:索引)

JDBC

定义:java数据库的连接,是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口,提供了诸如查询和更新数据库中数据的方法。JDBC时Sun Microsystem的商标。通常说JDBC是面向关系型数据库的。

第一个JDBC程序

import java.sql.*;
public class test {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");      //固定写法,加载驱动

        //2.用户信息和url                    school:这个是数据库
        String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username="root";
        String password="123456";
        //3.连接成功,数据库对象,Connection 代表数据库
        Connection connection=DriverManager.getConnection(url,username,password);
        //4.执行SQL的对象statement   执行sql的对象
        Statement statement=connection.createStatement();
        //5.执行SQL对象
        String sql="SELECT * FROM test";
        ResultSet resultSet=statement.executeQuery(sql);//返回结果集
        while(resultSet.next()){
            System.out.println("categoryid="+resultSet.getObject("categoryid"));
            System.out.println("pid="+resultSet.getObject("pid"));
            System.out.println("categoryName="+resultSet.getObject("categoryName"));
        }
    }
}

步骤总结:

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

DriverManager:

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());   //不推荐,这样的话会注册两次
Class.forName("com.mysql.jdbc.Driver");      //固定写法,加载驱动
//connection:代表数据库
connection.rollback();
connection.commit();
connection.setAutoCommit();

URL

 String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";

//mysql   默认端口号:3306
//jbdc:mysql(协议)://主机地址:端口号/数据库名?参数1&参数2&参数3

//oralce   默认端口号:1521
//jbdc:oracle:thin:@localhost:1521:sid

Statement执行SQL的对象 PrepareStatement执行SQL对象

String sql="SELECT * FROM test";//编写SQL

statement.excuteQuery();//查询操作,返回一个ResultSet(结果集)
statement.excute();//执行任何SQL

statement.executeUpdate();//更新、插入、删除、都用这个,返回一个受影响的函数

ReseltSet查询结果集:封装了所有的查询结果

获得指定的数据类型:

resultset.getobject();//在不知道的情况下使用
//如果知道列的类型使用指定的类型
resultset.getString();
resultset.getInt();
resultset.getFloat();
resultset.getDate();
...
    //遍历,指针
resultSet.beforeFirst(); //	移动到最前面
resultSet.afterLast();   //	移动到最后面
resultSet.next();        //	移动到下一个数据
resultSet.previous();    //	移动到前一行
resultSet.absolute(row); //	移动到指定行

释放资源

//释放连接
resultSet.close();
statement.close();
connection.close();//耗费资源,用完关闭

Statement对象

定义:jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。

statement对象的方法:

  • executeUpdate(),用于向数据库发送增删改的SQL语句,executeUpdate()执行完后,将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
  • executeQuery(),用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultSet对象。
  • 使用executeUpdate(String sql)方法完成数据添加操作:
statement st=connection.creatstatement();
String sql="insert into user(...) values(...)";
int num=st.executeUpdate(sql);
if(num>0){
    System.out.println("success");
}
  • 使用executeUpdate(String sql)方法完成数据删除操作:
statement st=connection.creatstatement();
String sql="delect from user where id=1";
int num=st.executeUpdate(sql);
if(num>0){
    System.out.println("success");
}
  • 使用executeUpdate(String sql)方法完成数据修改操作:
statement st=connection.creatstatement();
String sql="update user set name='***' where name='***'";
int num=st.executeUpdate(sql);
if(num>0){
    System.out.println("success");
}
  • 使用executeUpdate(String sql)方法完成数据查询操作:
statement st=connection.creatstatement();
String sql="select * from user where id=1";
ResultSet re=st.executeQuery(sql);
where(rs.next()){
    //根据获取的数据类型,分别调用rs相应方法映射到java对象中。
}

//插入
public class TestInsert {
    public static void main(String[] args) throws SQLException {
        Connection conn=null;//获取数据库的连接
        Statement st=null;//获取sql的执行对象
        ResultSet rs=null;
        try {
            conn=JdbcUtils.getConnection();
            st=conn.createStatement();
            String sql="INSERT INTO test(categoryid,pid,categoryName)" +
                    "VALUES (10,4,'美术')";
            int i=st.executeUpdate(sql);     //受影响的行数
            if(i>0){
                System.out.println("Success");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

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

SQL注入

SQL存在漏洞,会被攻击导致数据泄露, SQL会被拼接

PreparedStatement对象

防止SQL注入的本质:把传递进来的参数当作字符,如果其中存在转文字,会被直接转义,可以有效的避免SQL注入。

这篇关于MySQL学习的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!