数据库的字段属性（重点）
Unsignde
- 无符号的整数
- 声明了该列不能声明为负数
zerofill
- 0填充
- 不足的为数用0填充 比如定义一个长度为3的整数 只输入了1 结果就会显示001
自增
- 通常理解为自增，自动在上一条记录的基础上+1（默认）
- 通常用来设计唯一的主键。必须是整数类型
- 可以自定义设计的主键自增的起始值和步长
非空 NULL NOTNULL
- 如果设置了not null ,不赋值就会报错
- 如果设置了null，不赋值就是默认null
默认
-
设置默认的值
创建数据库表
CREATE TABLE IF NOT EXISTS `shudent2`( `id` INT(10) NOT NULL COMMENT'id', `name` VARCHAR(10) NOT NULL DEFAULT'niming' COMMENT'name', `age` INT(3) NOT NULL COMMENT'age', `sex` VARCHAR(1) NOT NULL DEFAULT'男' COMMENT'sex', PRIMARY KEY(`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8格式
create table [if not exists] `表的名称`(--创建格式 `列的名字` 列的类型(长度) [属性] [索引] [注释], `列的名字` 列的类型(长度) [属性] [索引] [注释], `列的名字` 列的类型(长度) [属性] [索引] [注释], ······ `列的名字` 列的类型(长度) [属性] [索引] [注释], primary key(`主键名字`) )[engine=innodb 表的类型] [default charset=utf8 字符集设置][注释]DESC student2; 查看表的结构
数据表的类型
INNODB 默认使用
MYISAM 早些年使用的
| INNODB | MYISAM | |
|---|---|---|
| 事务支持 | 支持 | 不支持 |
| 数据行锁定 | 支持 | 不支持 |
| 外键约束 | 支持 | 不支持 |
| 全文索引 | 不支持 | 支持 |
| 表空间的大小 | 较大 约为MYISAM的2倍 | 较小 |
常规使用操作：
- MYISAM 节约空间，速度较快
- INNODB 安全性高，事务的处理，多表多用户操作
在物理空间存放的位置
所有的数据库文件都存在data目录下，一个文件夹对应一个数据库
其本质还是文件的存储
MySQL 引擎在物理文件上的区别
- INNODB 在数据表中只有一个*.frm文件，以及上级目录下的ibdata1文件
- MYISAM对应的文件
- *.frm 表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
修改删除表
修改
ALTER TABLE shudent2 RENAME AS student2;-- 增加表的名称 修改表的名称
ALTER TABLE student2 ADD `email` VARCHAR(15);-- 增加表的字段
ALTER TABLE student2 MODIFY `email` INT(3);-- 修改表的约束
ALTER TABLE student2 CHANGE `email` phonenum VARCHAR(10);-- 修改表的字段名称
ALTER TABLE student2 DROP `phonenum`;-- 删除表的字段
删除
DROP TABLE IF EXISTS student;-- 删除表 （如果存在）
所有的创建和删除操作尽量加上判断，以免报错
注意点
- ``字段名使用这个包裹
- 注释使用-- /**/
- sql 关键字大小写不敏感，一般小写
- 符号一定是英文
MySQL的数据管理
外键（了解）
DML语言（全部记住）
DML语言：数据操作语言
- inset
- update
- delete
添加
-- 格式：insert into `表名`([`字段名一`,`字段名二`,`字段名三`,`字段名四`])values('值1，值2，值3，值4')
INSERT INTO `student`(`id`,`name`,`age`,`adrees`) VALUES('7','wang','18','重庆')-- 插入一个数据
INSERT INTO `student`(`id`,`name`,`age`,`adrees`) VALUES('11','li','18','重庆'),('12','liu','66','北京')-- 一次插入多个数据
INSERT INTO `student` VALUES('99','sun','99','阿尔法')-- 可以不添加字段 但是值必须一一对应
修改
-- 修改语句
UPDATE `student` SET `name`='lvchao' WHERE id=1-- 修改单个
UPDATE `student` SET `name`='lvchao',`age`='18' WHERE id=1-- 修改多个
UPDATE `student` SET `name`='dog' WHERE id BETWEEN 11 AND 99-- 在某个区间
UPDATE `student` SET `name`='wangfang' WHERE `name`='wang' AND `age`='18'-- 两个条件都成立
UPDATE `student` SET `name`='wangfang' WHERE `name`='wnag' OR`age`='8'-- 两个条件有一个成立
删除
DELETE FROM `student` WHERE id=99
清空表
truncate `student`
delete和truncate区别
- 相同点 ：都能删除数据，不会影响表的结构与
- 不同
- truncate 重新设置自增序列 计数器归零
- truncate 不会影响事务
DQL查询数据（超级重点）
数据查询语言
- 所有的查询操作都用他 Select
- 简单和复杂的都能查询
- 数据库最核心的语言，最重要的语句
- 使用频率最高的语句
指定查询的字段
SELECT * FROM`student`-- 查询全部的学生
SELECT `studentno`,`studentname` FROM `student`-- 查询指定字段
SELECT`studentno`AS 学生学号,`studentname`AS 学生姓名 FROM `student`-- 别名 给结果列另起一个名字
SELECT CONCAT('姓名：',studentname) AS 新名字 FROM `student`-- 函数concat(a,b)
去重distinct
作用：去除查询出来的重复的数据 只显示一条
SELECT * FROM `result`
SELECT `studentno` FROM `result`
SELECT DISTINCT `studentno` FROM `result`-- 去重
数据库的列(表达式)
SELECT VERSION()AS 版本号-- 查询版本号（函数）
SELECT 100*2-1 AS 结果为-- 用来计算（）表达式
SELECT @@auto_increment_increment-- 查询自增的步长（变量）
SELECT * FROM `result`
SELECT `studentresult`+1 AS 加一分的成绩为 FROM `result`-- 给所有成绩增加一分
数据库中的表达式： 文本值，列，Null，函数，计算表达式，系统变量……
select 表达式from表
where条件子句
作用：检索数据中符合条件的值
搜索的条件由一个或者多个表达式组成，结果一般为布尔值
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a&&b | 逻辑与，两个都为真则为真 |
| or || | a or b a||b | 逻辑或，有一个为真则为真 |
| Not ！ | not a !a | 逻辑非，真为假，假为真 |
尽量使用字母表达
---------------------- 查询成绩在60到80的成绩 -----------------------------
SELECT`studentno`,`studentresult` FROM `result`
WHERE `studentresult`>=60 AND `studentresult`<=80
------------------------ 查询除了1000号的成绩 -----------------------------
SELECT`studentno`,`studentresult` FROM `result`
WHERE NOT `studentno`=1000
SELECT`studentno`,`studentresult` FROM `result`
WHERE `studentno`!=1000
模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | a is null | 如果操作符为NULL，结果为真 |
| IS NOT NULL | a is not null | 如果操作符不为NULL，结果为真 |
| BETWEEN AND | a between b and c | 如果a在b和c之间，结果为真 |
| Like | a like b | SQL匹配，如果a匹配到b，结果为真 |
| in | a in (a1,a2,a3……) | 假设a在a1,或者a2……其中的某一个值中，结果为真 |
---------------------------模糊查询-----------------------------------
-- ========================like========================
查询姓张的同学
使用like 结合 %(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 '%强%'
-- ========================in（具体的值）========================
查询学号为1000到1001的学生
SELECT`studentno`,`studentname` FROM `student`
WHERE `studentno`IN(1000,1001)
查询在北京的学生
SELECT `studentno`,`studentname`,`address` FROM `student`
WHERE `address` IN ('北京朝阳','湖北武汉')
查询email为空的同学 为空就是空字符串''或者null
SELECT`studentno`,`studentname`,`email` FROM `student`
WHERE `email`='' OR `email`IS NULL
查询id不为空的同学
SELECT`studentno`,`studentname`,`identitycard` FROM `student`
WHERE `identitycard`IS NOT NULL OR `identitycard`!=''
联表查询
JOIN 对比
-- ================== 联表查询 JOIN =======================
-- ============ inner join ==============
查询 参加考试了的同学的 学号+姓名+学科+成绩
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 AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno
-- ============ left join ==============
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一个匹配，就返回值 |
| left join | 即使左表中没有匹配，也会从左表中返回所有的值 |
| right join | 即使右表中没有匹配，也会从右表中返回所有的值 |
-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno
WHERE studentresult IS NULL
-- =============== 练习 参加可考试的学生的 学号 姓名 科目 分数 ==========
SELECT s.studentno,studentname,subjectname,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
联表查询多个表时 先查询两个表再与下一个表查询
自连接
自己的表和自己的表连接，核心：一张表拆为两张一样的表
父类
| categoryid | categoryname |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类
| pid | categoryname | categoryid |
|---|---|---|
| 3 | 数据库 | 4 |
| 2 | 办公信息 | 8 |
| 3 | web开发 | 6 |
| 5 | ps技术 | 7 |
操作：查询父子对应关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
SELECT a.`categoryname`AS 父类, b.`categoryname` AS 子类
FROM `category`AS a,`category`AS b
WHERE a.`categoryid`=b.`pid`
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QMXU8I6d-1614852161045)(C:\Users\lcj\AppData\Roaming\Typora\typora-user-images\image-20210302102518943.png)]
-- 查询学员所属的年级（学号 姓名 年纪）
SELECT `studentno`,`studentname`,`gradename`
FROM`student` s
LEFT JOIN`grade` g
ON s.`gradeid`=g.`gradeid`
-- 查询科目所属的年级（科目id 科目name 年纪）
SELECT `subjectno`,`subjectname`,`gradename`
FROM `subject` s
INNER JOIN `grade` g
ON s.`gradeid`=g.`gradeid`
-- 查询参加高等数学-1考试的学生信息（ 学号 姓名 科目 分数）
SELECT s.studentno,studentname,subjectname,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno -- 分析 首先需要查询出所有参加考试的学生然后在参加的考试的学生中通过where再查询参加高等数学-1考试的学生
WHERE `subjectname`='高等数学-1'
分页和排序
排序
-- 查询参加高等数学-1考试的学生信息（ 学号 姓名 科目 分数） 将查询出来的成绩按照降序排列 升序 ASC 降序 DESC
SELECT s.studentno,studentname,subjectname,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE `subjectname`='高等数学-1'
ORDER BY `studentresult` DESC
分页
为什么要分页 缓解数据库压力 良好的体验
-- 分页
SELECT s.studentno,studentname,subjectname,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
ORDER BY `studentresult` DESC
LIMIT 0,3
-- (n-1)*pagesize,pagesize
-- 查询 java第一学年的 课程成绩排名前3的学生信息（学号 姓名 科目 分数）
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`='Java程序设计-1'
ORDER BY `studentresult` DESC
LIMIT 0,3
子查询
-- ===================== 子查询 ======================
-- 查询参加Java程序设计-1的学生信息 （学号 科目编号 成绩） 降序
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `subjectno` =(
SELECT `subjectno` FROM `subject`
WHERE `subjectname` = 'Java程序设计-1'
)
order by `studentresult` desc
-- 查询成绩不小于80分的学生的学号和姓名
SELECT `studentno`,`studentname`
FROM student s
WHERE studentno IN (
SELECT `studentno` FROM `result` r
WHERE `studentresult`>=80
)
SELECT DISTINCT s.`studentno`,`studentname`
FROM student s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
WHERE `studentresult`>=80
MySQL函数
常用函数
-- =========================== 常用函数 ==========================
-- 数学运算函数
SELECT ABS(-8)-- 取绝对值
SELECT CEILING(9.4)-- 向上取整 10
SELECT FLOOR(9.9)-- 向下取整 9
SELECT RAND() -- 返回一个0~1的随机数
SELECT SIGN(11) -- 判断一个数的符号 正数 1 负数 -1 0 0
-- 字符串函数
SELECT CHAR_LENGTH('jhadsklvnbj')-- 返回字符串的长度
SELECT CONCAT('你','好','呀')-- 拼接字符串
SELECT INSERT('你好呀',1,2,'我')-- 插入字符出啊 替换 从1到2的位置替换为
SELECT LOWER('HGDJKSLDVJG')-- 把大写变成小写
SELECT UPPER('asdghjkvl')-- 把小写变成大写
SELECT INSTR('gadshjk','g')-- 第一次出现的子串的索引
SELECT REPLACE('达河之水天上来','达','大')-- 替换出现的子字符串
SELECT SUBSTR('大河之水天上来',2,2) -- 截取 从2开始的2个字符串
SELECT REVERSE('abcdefg')-- 反转字符串
-- 时间和日期
SELECT CURRENT_DATE() AS 日期-- 查询当前日期
SELECT CURRENT_TIME()-- 查询当前时间
SELECT NOW()-- 获取当前时间日期
SELECT LOCALTIME()-- 获取本地时间
SELECT SYSDATE()-- 获取系统时间
-- 系统
SELECT SYSTEM_USER()--
SELECT USER()
SELECT VERSION()
分组和过滤
-- 查询不同科目的平均分,最高分，最低分 且平均分大于80 的
SELECT `subjectname`AS 科目,AVG(studentresult) AS 平均分,MAX(studentresult)AS 最高分,MIN(studentresult)AS 最低分
FROM `subject` sub
INNER JOIN `result` r
ON sub.subjectno = r.subjectno
GROUP BY `subjectname` -- 通过什么分组
-- where AVG(studentresult)>80 聚合函数不能使用where，
HAVING 平均分 > 80 -- 必须写在 group by 下面 和 order by 上面
ORDER BY AVG(studentresult) DESC
聚合函数(常用)
| 函数名称 | 描述 |
|---|---|
| count() | 计数 |
| sum() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min() | 最小值 |
| …… | …… |
SELECT COUNT(`studentname`) FROM `student` -- 查询 学生个数 查询指定列 会忽略null值，对null不进行计数
SELECT COUNT(*) FROM `student`-- 不会忽略null值，
SELECT COUNT(1) FROM `student`-- 不会忽略null值，
MD5加密（拓展）
-- ===================== 测试md5加密 ==========================
CREATE TABLE `testmd5`(
`id` INT(3) NOT NULL ,
`name` VARCHAR(20) NOT NULL,
`password` VARCHAR(15) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
TRUNCATE `testmd5`
DROP TABLE`testmd5`
ALTER TABLE `testmd5` MODIFY pwd VARCHAR(50)
ALTER TABLE pwd RENAME AS `testmd5`
INSERT INTO `testmd5` VALUES (1,'lv','123456'),(2,'yang','123456'),(3,'zhang','123456')
UPDATE `testmd5` SET pwd=MD5(pwd) -- 加密全部的密码
UPDATE `testmd5` SET pwd=MD5(pwd) WHERE id=1 -- 加密部分的密码
-- 这里时放入数据库之后才加密不符合逻辑
-- 所以要在插入的时候加密
INSERT INTO `testmd5` VALUES(4,'小兰',MD5('123456'))
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WwDWbKCA-1614852161052)(C:\Users\lcj\Desktop\select语法.jpg)]
事务
什么时事务
要么都成功，要么都失败
事务原则：ACID 原子性、一致性、隔离性、持久性 （脏读、幻读……）
原子性：要么都成功，要么都失败
一致性：事务前后的数据完整性要保持一致
隔离性：多个用户并发访问同一个数据库时，为每个开启的事务，操作数据时不会相互干扰
持久性：事务一旦提交，就会被持久化到数据库中
隔离所导致的问题
脏读：
指的是一个事务读取了另一个事务未提交的数据
不可重复度：
在一个事务内读取表的某一行数据，多次读取结果不同（不一定是错误，知识某些场合不对）
续读（幻读）：
是指在一个事务内读取到了别人事务插入的数据，导致前后不一致
执行事务
-- ========================== 事务 ==========================
-- MySQL默认开启事务，自动提交的
SET autocommit=0 -- 设置自动提交关闭
SET autocommit=1 -- 设置自动提交开启
-- 手动处理事务
SET autocommit=0 -- 设置自动提交关闭
-- 事务开启
START TRANSACTION -- 标记事务的开始，从这个之后的sql都在同一个事务内
-- 提交：持久化
COMMIT
-- 回滚：回到原来的位置
ROLLBACK
-- 事务结束
SET autocommit=1 -- 设置自动提交开启
-- 了解
SAVEPOINT 保存点名 -- 保存点
ROLLBACK TO SAVEPOINT 保存点名-- 回滚到保存点
RELEASE SAVEPOINT 保存点名-- 撤销保存点
模拟事务
-- ==================== 模拟转账事务 ======================
-- 创建一个数据库
CREATE DATABASE `shop`
USE shop
-- 在数据库中创建一个表
CREATE TABLE account(
`id` INT(4) 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',1000),('b',2000)
-- 事务
SET autocommit=0;
START TRANSACTION -- 开启一个事务
UPDATE `account` SET `money`=`money`-500 WHERE `name`='a'
UPDATE `account` SET `money`=`money`+500 WHERE `name`='b'
COMMIT;-- 提交事务，，数据被永久化
ROLLBACK;
SET autocommit=1;
索引
MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。
提取句子的主干，就可以得到索引的本质：索引的数据结构
索引的分类
在一个表中，主键索引只能有一个，唯一索引有多个
- 主键索引 (PRIMARY KEY)
- 唯一标识，主键不可重复，只能右一个列作为主键
- 唯一索引(UNIQUE KEY)
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
- 常规索引(KEY/INDEX)
- 默认的，可以通过index/key来设置
- 全文索引(FullText)
- 在特定的数据库引擎下才有，MyISAM
- 快速定位数据
基础语法
-- 索引的使用
-- 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('张') -- 当数据量较小的时候失效
测试索引
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 $$ -- 写函数之前必须要自己写 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),'32626565@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'
EXPLAIN SELECT * FROM app_user WHERE `name`='用户名9999'
-- create index 索引名 on 表名(`字段`)
-- id_表名_字段名
CREATE INDEX id_app_user_name ON app_user(`name`)
索引查询只需要查询一个结果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bBmzpGOB-1614852161056)(C:\Users\lcj\AppData\Roaming\Typora\typora-user-images\image-20210302224032425.png)]
索引在小数据下用处不大
索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 一般加在常用来查询的字段上
数据库备份
- 保证重要的数据不丢失
- 数据转移
MySQL数据备份的方式
-
直接拷贝物理文件
-
使用在sqlyog可视化工具中手动导出
- 在要备份的库或者表选中右键选中sql备份，选中转为sql存储
-
使用命令行导出 mysqldump 命令行导出
-
mysqldump -hlocalhost -uroot -p123456 school student > D:/a.sql -
导入
-
先登录mysql然后选中到导入的库中 然后 source d:/a.sql
-
-
权限管理
可视化 略
sql语句
用户表：mysql.user
本质：对这张表的增删改
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UHPJNp1v-1614852161059)(C:\Users\lcj\AppData\Roaming\Typora\typora-user-images\image-20210303105230586.png)]
-- CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER lv IDENTIFIED BY '123456'-- 创建一个用户
-- 修改当前用户密码
SET PASSWORD=PASSWORD('新密码')
-- 修改指定用户密码
SET PASSWORD FOR 要修改的密码的用户名 = PASSWORD('新密码')
-- 用户重命名
RENAME USER 需要重命名的用户名 TO 改变后的用户名
-- 用户授权
-- ALL PRIVILEGES 不能给用户授权 让它取授权的权力
GRANT ALL PRIVILEGES ON *.* TO 用户名 -- 授予最高的权限 但是最高也不会和root一样
GRANT ALL PRIVILEGES ON *.* TO lv
-- 查看用户有哪些权限
SHOW GRANTS FOR lv
-- 查看root用户的权限
SHOW GRANTS FOR root@localhost
-- GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION root用户的权限
-- 撤销权限
REMOVE ALL PRIVILEGES ON *.* FROM lv
-- 删除用户
DROP USER lv
规范数据库设计
当数据库比较复杂的时候，我们就需要设计数据库
糟糕的数据库设计：
- 数据冗余，浪费空间
- 数据库插入和删除麻烦、异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计：
- 节省内存空间
- 保证数据的完整性
- 方便我们开发系统
软件开发中，关于数据库的的设计
- 分析需求： 分析业务和需要处理的数据和需求
- 概要设计：设计关系图E-R图
设计数据库的步骤：（个人博客）
-
收集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 评论表（）
- 友链表（友链信息）
- 自定义表（系统信息，某个关键字，或者一些主题）key:value
-
标识实体（把需求落地到每个字段）
- user->user
- user->blod
- ……
数据库的规约，三大范式
为什么需要数据规范化
- 数据会重复
- 更新会导致异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效信息
三大范式
第一范式（1NF）
原子性：保证每一列不可再分
第二范式（2NF）
前提：满足第一范式
每张表只描述一件事情
第三范式（3NF）
前提：满足第一和第二范式
选哟确保数据表中的每一列数据都和主键直接相关，不能间接相关
（规范数据库的设计）
规范性和性能表的问题
关联查询不得超过三张表
- 考虑商业化的需求和目标（成本，用户体验） 数据库的性能更加重要
- 在规范性能的问题的时候，需要适当的考虑一下规范性
- 故意给某些表增加一些字段（从多表查询边单表查询）
- 故意增加一些计算列（从大学数据量降低为小数据量的查询）
JDBC( 重点)
数据库驱动
应用程序不能直接访问数据库，，需要通过数据库驱动来访问，不通过的数据库有不同的驱动，这个驱动时由数据库厂商提供的
JDBC
sun公司为了简化开发人员的（对数据库的统一）操作，提供了一个（Java操作数据库的）规范 ，俗称：JDBC
需要导入数据库驱动包： mysql-connector-java-5.1.47.jar
第一个JDBC程序
package com.lv.lesson01;
import java.sql.*;
/*
我的第一个JDBC程序
*/
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");//固定写法加载驱动
//2.用户信息和url
String url="jdbc:mysql://localhost:3306/jdbcstudy?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的对象 去执行sql，可能存在结果，产看返回结果
String sql="select * from user";
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集，结果集中封装了我们全部查询出来的结果
while(resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));
System.out.println("name="+resultSet.getObject("NAME"));
System.out.println("password="+resultSet.getObject("PASSWORD"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birthday="+resultSet.getObject("birthday"));
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结：
- 加载驱动
- 连接数据库DriverManager
- 获取执行sql的对象 Statement
- 获得返回的结果集
- 释放连接
DriverManager
Class forname("com.mysql.jdbc.Driver");//固定写法加载驱动
Connection connection = DriverManager.getConnection(url,username,password)
//connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.setAutoCommit();
connection.commit();
connection.rollback();
URL
String url="ldbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true"
//mysql--3306
// 协议://主机地址:端口号/数据库名?参数1&参数2&参数3
Statement 执行sql的对象 还有prepareStatement
String sql="select * from users"//编写sql语句
statement.executeQuery();//查询操作，返回一个结果集
statement.execute();//执行所有的sql语句 其中有判断所以执行效果就会低一点
statement.executeUpdate();//更新、插入、删除、都用这个语句，返回一个受影响的行数
ResultSet查询的结果集：封装了所有的查询结果
获得 指定的数据类型
resultSet.getObject();//不知道列类型的时候使用
//知道指定类型就使用指定类型
resultSet.getString();
resultSet.getDate();
……
遍历指针
resultSet.beforeFirst();//将光标移动到最前面
resultSet.afterLast();//将光标移动到最后面
resultSet.next();//移动到下一个数据
释放资源
resultSet.close();// 释放资源
statement.close();
connection.close();
statement对象
JDBC中的statement对象用于向数据库发送sql语句，想完成对数据库的增删改查，只需要通过这个对现象向数据库发送增删改查语句即可
statement对象的executeUpdate方法，用于向数据库发送 增、删、改 的sql语句，execute Update执行完后，将会返回一个整数（即增删改导致数据库发生了变化的行数）
statement.executeQuery方法用于数据库发送查询语句，返回一个查询结果的resultset对象
编写工具类
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456
package com.lv.lesson02.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver=null;
private static String url=null;
private static String username=null;
private static String password=null;
static{
try{
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver=properties.getProperty("driver");
url=properties.getProperty("url");
username=properties.getProperty("username");
password=properties.getProperty("password");
//1.驱动只用加载一次
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//释放连接资源
public static void release(Connection connection, Statement statement, ResultSet resultSet){
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement!=null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
利用工具类编写增、删、改
/*
增加数据
*/
package com.lv.lesson02;
import com.lv.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
Statement state = null;
ResultSet result = null;
try {
conn = JdbcUtils.getConnection();//获取数据库连接
state = conn.createStatement();//获得sql执行的对象
String sql = "INSERT INTO users(`id`,`name`,`PASSWORD`,`email`,`birthday`)\n" +
"VALUES(4,'lv','123456','lv@qq.com','2000-7-13')";
int i = state.executeUpdate(sql);
if (i > 0) {
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.release(conn, state, result);
}
}
}
/*
删除数据
*/
package com.lv.lesson02;
import com.lv.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection connection=null;
Statement statement=null;
ResultSet resultSet=null;
try {
connection=JdbcUtils.getConnection();
statement=connection.createStatement();
String sql="DELETE FROM `users` WHERE id=4";
int i = statement.executeUpdate(sql);
if (i>0){
System.out.println("删除数据成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
/*
修改数据
*/
package com.lv.lesson02;
import com.lv.lesson02.utils.JdbcUtils;
import javax.swing.plaf.nimbus.State;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {
public static void main(String[] args) {
Connection connection=null;
Statement statement=null;
ResultSet resultSet=null;
try {
connection= JdbcUtils.getConnection();
statement=connection.createStatement();
String sql="UPDATE `users`SET `name`= 'chao' WHERE id=4";
int i = statement.executeUpdate(sql);
if(i>0){
System.out.println("修改数据成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
利用工具类编写查询
package com.lv.lesson02;
import com.lv.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) {
Connection connection=null;
Statement statement=null;
ResultSet resultSet=null;
try {
connection= JdbcUtils.getConnection();
statement=connection.createStatement();
String sql="SELECT `NAME` FROM`users`";
resultSet=statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println("name:"+resultSet.getString("NAME"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
sql注入的问题
sql存在漏洞会被攻击，导致数据泄露 sql会被拼接
PreparedStatement对象
可以防止sql注入，并且效率更高
PreparedStatement 增、删、改、查
package com.lv.lesson03;
import com.lv.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestInsert {
public static void main(String[] args) {
Connection connection=null;
PreparedStatement preparedStatement=null;
try {
connection= JdbcUtils.getConnection();
String sql="insert into users(`id`,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)";
preparedStatement=connection.prepareStatement(sql);
preparedStatement.setInt(1,5);
preparedStatement.setString(2,"jun");
preparedStatement.setString(3,"147258369");
preparedStatement.setString(4,"chao@qq.com");
preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("插入数据成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
JdbcUtils.release(connection,preparedStatement,null);
}
}
}
package com.lv.lesson03;
import com.lv.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestDelete {
public static void main(String[] args) {
Connection connection=null;
PreparedStatement preparedStatement=null;
try{
connection= JdbcUtils.getConnection();
String sql="delete from users where id=?";
preparedStatement=connection.prepareStatement(sql);
preparedStatement.setInt(1,5);
int i = preparedStatement.executeUpdate();
if (i > 0) {
System.out.println("删除数据成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
JdbcUtils.release(connection,preparedStatement,null);
}
}
}
package com.lv.lesson03;
import com.lv.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestUpdate {
public static void main(String[] args) {
Connection connection=null;
PreparedStatement preparedStatement=null;
try {
connection= JdbcUtils.getConnection();
String sql="update users set `name`='yang' where id=?";
preparedStatement=connection.prepareStatement(sql);
preparedStatement.setInt(1,4);
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("修改数据成");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,preparedStatement,null);
}
}
}
package com.lv.lesson03;
import com.lv.lesson02.utils.JdbcUtils;
import com.mysql.jdbc.JDBC4Connection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect {
public static void main(String[] args) {
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
try {
connection= JdbcUtils.getConnection();
String sql="select name from users where id=?";
preparedStatement=connection.prepareStatement(sql);
preparedStatement.setInt(1,4);
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
System.out.println("name:"+resultSet.getString("name"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,preparedStatement,resultSet);
}
}
}
事务
要么都成功要么都失败
package com.lv.lesson04;
import com.lv.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction {
public static void main(String[] args) {
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
try {
connection= JdbcUtils.getConnection();
//关闭数据库自动提交 这里不需要开启事务，当自动提交关闭时就会自动打开事务
connection.setAutoCommit(false);
String sql1="update account set money=money-200 where name ='A'";
preparedStatement=connection.prepareStatement(sql1);
preparedStatement.executeUpdate();
String sql2="update account set money=money+200 where name ='B'";
preparedStatement=connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
connection.commit();
System.out.println("提交成功");
} catch (SQLException throwables) {
throwables.printStackTrace();
try {
connection.rollback();//如果失败则回滚事务 这里如果不写也会自动回滚
} catch (SQLException e) {
e.printStackTrace();
}
}finally {
JdbcUtils.release(connection,preparedStatement,resultSet);
}
}
}
数据库连接池
数据库连接—执行完毕----释放 ，
从连接到释放 十分浪费系统资源
池化技术：准备一些预先的资源，过来就连接预先准备好的
一般按照常用连接数来设置最小连接数，最大连接数是业务承载上线，当超过时就需要排队等待
当等待超过一定的时间就会断开：等待超时
编写连接池，实现接口DateSource
开源数据源实现（拿来就可以使用）
DBCP
C3P0
Druid:阿里巴巴
使用了这些数据库连接池之后，在项目的开发中就不需要编写连接数据库的代码了！
需要用到的jar包
commons-dbcp-1.4.jar
commons-pool-1.6.jar
C3P0
需要的jar包
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yA9UaDbP-1614852161061)(C:\Users\lcj\AppData\Roaming\Typora\typora-user-images\image-20210304112836019.png)]
扫一扫
766
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
