MySQL
什么是数据库
简称：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(同上)
服务名查询：
- 右击此电脑
- 管理
- 服务与应用程序
- 服务
- 找到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个字段（项目）
- id-----主键
- ”
version“ --------乐观锁- is_delect-------伪删除
- gmt_create------创建时间
- 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
联表查询
| 操作 | 描述 |
|---|---|
| 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注入。