Mysql
列表规范:每个表都必须存在一下五个字段,未来做项目使用,表示一个纪录存在的意义
Id 主键
Version 乐观锁
Is_delete 伪删除
gmt_creatr 创建时间
gmt_update 修改时间
1、命令行链接
Mysql -uroot -p123456
Update mysql.user set authentication_string=password(‘123456’) where user =’root’ and host = ‘localhost’;--修改用户密码
Flush privileges;--刷新权限
Show datebases;--查看所有数据库
Show tables –查看数据库中所有表
Describe student –显示数据库中所有的表的信息
Creat database westos;--创建一个数据库
Exit;--退出链接
数据库xxx语言 CRUD增删改查
DDL 定义
DML 操作
DQL 查询
DCL 控制
2、操作数据库
1.创建数据库
CREATE DATABASE if NOT EXISTS westos;
2.删除数据库
DROP DATABASE if EXISTS westos;
3.使用数据库
如果你的表名或者字段名是一个特殊字符,就需要使用`
use `school`;
4.查看数据库
Show datebases;
2.2、数据库的列类型
数值
- Tinyint 1字节
- Smallint 2字节
- Mediumint 3字节
- Int 4字节(常用)
- Bigint 8字节
- Float 浮点数 4字节
- Double 浮点数 8字节
- Decimal 字符串形式的浮点数 金融计算的时候使用
字符串
- Char 字符串 0~255
- Varchar 可变字符串 0~65535
- Tingtext 微型文本 2^8-1
- Text 文本串 2^16-1
时间日期
Java.util.date
- Date YYYY-MM-DD,日期
- Time HH:mm:ss时间格式
- Datetime YYYY-MM-DD HH:mm:ss最常用时间格式
- Timestamp 时间戳 1970.1.1到现在的毫秒数,较为常用
- Year 年份表示
Null
- 没有值,未知
- ==注意,不要使用NULL进行运算,结果一定为null
2.3、数据库的字段属性(重点)
Unsigned:
- 无符号正数
- 不能申明为负数
Zerofill:
- 0填充的
- 不足的位数,使用0来填充,int(3)---5---005
自增:
- 通常理解为自增,自动在上一条纪录的基础上+1(默认)
- 通常用来设计唯一的主键~index,必须是整数类型
- 可以自定义设计主键自增的起始值和步长
非空NULL not null
- 假设设置not null ,如果不给赋值,就会报错
- NULL,不填写值,默认为NULL
默认:
- 设置默认值
- Sex,默认值为男,如果不指定该列的值,则会有默认的值
2.4、创建表
--注意点,使用英文(),表的名称和字段尽量使用``括起来
--AUTO_INCREMENT 自增
--字符串用单引号括起来!
--所有的语句后面加,(英文的),最后一个不用加
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
创建表的格式
Creat table [if not exists] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
………
)[表类型][字符集设置][注释]
常用命令
SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student --显示表的结构
2.5数据表的类型
-- 关于数据库引擎
INNODB 默认使用
myisam 早些年使用
MYISAM | INNODB | |
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为两倍 |
常规使用操作:
- MYISAM 节约空间,速度较快
- INNODB 安全性高,事务的处理,多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下,一个文件夹就对应一个数据库
本质还有文件的存储
Mysql引擎在物理文件上的区别
- Innodb在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
- MYISAM对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件(data)
- *.MYI 索引文件(index)
2.6、修改删除表·
-- 修改表:ALTER TABLE 旧表名 RENAME AS 新表命名
ALTER TABLE student RENAME AS student1
-- 增加表的字段:ALTER TABLE 表名 ADD 字段名 字段的列属性
ALTER TABLE student1 ADD age INT(11)
-- 修改表的字段(重命名,修改约束!)
ALTER TABLE student1 MODIFY age VARCHAR(11)-- 修改约束
ALTER TABLE student1 CHANGE age age1 int(11)-- 重命名
-- 删除表的字段
ALTER TABLE student1 DROP age1
--删除表(如果表存在删除)
DROP TABLE if EXISTS student1
所有的创建和删除操作尽量加上判断,以免报错~
3、MySQL数据管理
3.1外键(了解即可)
阿里规范:【强制】不得使用外键与级联,一切外键概念必须在应用层解决
方式一、在创建表的时候,增加约束(麻烦,比较复杂)
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 '出生日期',
`gradeid` int(10) NOT NULL COMMENT '学生的年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
删除有外键关系的表的时候,必须要先删除应用别人的表,在删除被应用的表
方式二:创建表成功后添加外键约束
-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);
公式:-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列)REFERENCES 那个表(哪个字段)
以上操作都是物理外键,数据库级别的外键,不建议使用(避免数据库过多造成困扰)
最佳实践
- 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
- 我们想使用多张表的数据,想使用外键(程序去实现)
3.2DML语言(全部记下来,背下来)
数据库意义:数据存储,数据管理
DML语言:数据操作语言
- Insert
- Update
- Delete
3.3、添加
-- 插入语句(添加)
INSERT INTO 表名 ([字段名1],[字段名2],......)VALUES('值1'),('值2'),(....)
INSERT INTO `grade` (`gradename`) VALUES ('zhang')
注意事项:
- 字段和字段之间使用英文逗号隔开
- 字段是可以省略的,但是后面的值必须要一一对应,不能少
- 可以同时插入多条数据,values后面的值,需要使用,隔开即可
3.4、修改
Update
-- 修改学员的名字
UPDATE `student` SET `name`='张三' WHERE id=1;
UPDATE `student` SET `name`='张三';-- 将所有name都改为张三
-- 修改多个属性
UPDATE `student` SET `name`='张三',`email` = '123456789' WHERE id=1;
语法:
Update 表名 set colnum_name = value where 条件[]
注意:
- colnum_name是数据库的列,尽量带上``
- 条件,筛选的条件,如果没有指定,则会修改所有的列
- Value,是一个具体的值,也可以是一个变量
条件:where子句 运算符
操作符 | 含义 | 范围 | 结果 |
= | 等于 | 5=6 | False |
<>或!= | 不等于 | 5<>6 | True |
> | 大于 | ||
< | 小于 | ||
>= | 大于等于 | ||
<= | 小于等于 | ||
BETWEEN..AND.. | 在什么区间 | [2,5] | |
AND | &&我和你 | 5>1and1>2 | False |
OR | ||我或你 | 5>1or1>2 | true |
3.5、删除
Delete命令
语法:delete from 表名 [where 条件]
TRUNCATE 命令
作用:完全清空一个数据库,表的索引和约束不会变
Delete和TRUNCATE的区别
- 相同点:都会删除数据,都不会删除表结构
- 不同点:
- TRUNCATE 重新设置自增列计数器归零
- TRUNCATE 不会影响事务
了解即可:delete删除的问题,重启数据库,现象
- Innodb 自增列会重1开始(存在内存当中的,断电即失)
- Myisam 继续从上一个自增量开始(存在文件中的,不会丢失)
4、DQL查询数据(最重点)
[left|right|inner join table_name2] –链表查询
[WHERE…..]--指定结果满足的条件
[GROUP BY….] –指定结果按照那几个字段来分组
[HAVING..]—过滤分组的纪录必须满足的次要条件
[ORDER BY…]—指定查询纪录按一个或多个条件排序
(Date Query LANGUAGE:数据查询语句)
- 所有的数据操作都用他 select
- 简单的查询,复杂的查询他都可以做
- =数据库最为核心的语言,最重要的语言=
- 使用评率最高
4.2指定查询字段
-- 查询所有学生
SELECT * FROM student
-- 查询指定字段
SELECT `studentno`,`studentname` FROM student
-- 别名,给结果起一个名字 As 可以给字段起别名,也可以给表起别名
SELECT `studentno` AS 学号,`studentname` AS 姓名 FROM student AS s
-- 函数 Concat (a,b),拼接
SELECT CONCAT('姓名',studentname) AS 新名字 FROM student
-- 去重复 DISTINCT,查询哪些同学参加考试,成绩
SELECT DISTINCT `studentno` FROM result
SELECT VERSION() -- 查询系统版本
SELECT 300-1 as 计算结果 -- 可以用来计算
SELECT @@auto_increment_increment -- 查询自增步长
-- 学员考试成绩+1分查看
SELECT `studentno`,`studentresult`+1 AS 增分后结果 FROM result
4.3、where条件子句
作用:检索数据中符合条件的值
逻辑运算符
运算符 | 语句 | 描述 |
And && | A and b a&&b | 逻辑与 |
Or || | A or b a||b | 逻辑或 |
Not ! | Not a !a | 逻辑非 |
尽量使用英文字母
查询考试分数在80-90之间的
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult`>=80 and `studentresult`<=90
--模糊查询
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult` BETWEEN 80 AND 90
-- 查询学号1000以外的学生
SELECT `studentno`,`studentresult` FROM result
WHERE `studentno` !=1000
模糊查询:比较运算符
运算符 | 语法 | 描述 |
IS NULL | A is null | 如果操作符为null,结果为真 |
IS NOT NULL | A is not null | 如果操作符不为null,结果为真 |
BETWEEN | A between b and c | 若a在b和c之间,则结果为真 |
LIKE | A like b | Sql 匹配,如果a匹配到b,则结果为真 |
IN | A in (a1,a2,a3) | 假设a在a1,或者a2…….其中的某一个值中,结果为真 |
-- ================模糊查询====================
-- 查询姓什么的同学
-- 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 '赵__'
-- 查询名字当中有一的同学
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%一%'
-- ===========in(具体的一个或多个值)============
-- 查询1000,1001,1002号学员
SELECT studentno,studentname FROM student
WHERE studentno IN (1001,1002,1000)
-- 查询在北京的学生
SELECT studentno,studentname FROM student
WHERE address IN('北京朝阳')
-- ========null not null==============
-- 查询地址为空的同学
SELECT studentno,studentname FROM student
WHERE address ='' or address IS NULL
-- 查询生日不为空的学生
SELECT studentno,studentname FROM student
WHERE borndate is not null
4.4、连表查询
JOIN对比
-- ================连表查询 join ===================
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
/*思路
1.分析需求,分析查询的字段来自哪些表,(链接查询)
2.确定使用哪种链接查询?7种
确定交叉点(这两个表中哪些数据是相同的)
判断条件:学生表中的studentno = 成绩表中的studentno
*/
SELECT s.studentno,studentname,subjectno,studentresult
from student as s
INNER JOIN result as r
WHERE s.studentno = r.studentno
-- RIGHT JOIN
SELECT s.studentno,studentname,subjectno,studentresult
from student s
RIGHT JOIN result r
ON s.studentno = r.studentno
-- LEFT JOIN
SELECT s.studentno,studentname,subjectno,studentresult
from student s
RIGHT JOIN result r
ON s.studentno = r.studentno
操作 | 描述 |
Inner join | 如果表中至少有一个匹配,就返回 |
Left join | 会从左表中返回所有的值,即使右表中没有匹配 |
Right join | 会从右表中返回所有的值,即使左表中没有匹配 |
-- ================连表查询 join ===================
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
/*思路
1.分析需求,分析查询的字段来自哪些表,(链接查询)
2.确定使用哪种链接查询?7种
确定交叉点(这两个表中哪些数据是相同的)
判断条件:学生表中的studentno = 成绩表中的studentno
*/
SELECT s.studentno,studentname,subjectno,studentresult
from student as s
INNER JOIN result as r
WHERE s.studentno = r.studentno
-- RIGHT JOIN
SELECT s.studentno,studentname,subjectno,studentresult
from student s
RIGHT JOIN result r
ON s.studentno = r.studentno
-- LEFT JOIN
SELECT s.studentno,studentname,subjectno,studentresult
from student s
RIGHT JOIN result r
ON s.studentno = r.studentno
-- 查询参考同学的信息:学号,学生姓名,科目名,分数(三表)
SELECT s.studentno,studentname,subjectname,`studentresult`
FROM student s
RIGHT JOIN result r
on r.studentno=s.studentno
INNER JOIN `subject` sub
on r.subjectno=sub.subjectno
自连接
自己的表和自己的表链接:核心:一张表拆为两张表即可
父类
catrgoryid | Categoryname |
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类
Pid | categoruid | categoryname |
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | Web开发 |
5 | 7 | Ps技术 |
操作:查询父类对应的子类关系
父类 | 子类 |
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | Web开发 |
美术设计 | Ps技术 |
SELECT a.categoryname as '父栏目', b.categoryname as '子栏目'
FROM category as a,category as b
WHERE a.categoryid=b.pid
4.5、分页和排序
-- =============分页limit和排序order by============
-- 排序:升序asc ,降序desc
-- ORDER BY 通过哪个字段排序
-- 根据结果降序排
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 = '数据库结构-1'
ORDER BY studentresult DESC
-- ==========分页============
-- 为什么要用分页
-- 缓解数据库压力,给人的体验更好,瀑布流
-- 分页,每页只显示2条
-- 语法:Limite 起始值,页面的大小
-- LIMIT 0,2 1~2
-- LIMIT 1,2 2~2
-- LIMIT (第n条数据),(一页显示x个)
-- 第N页 [(n-1)*x],x
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 = '数据库结构-1'
ORDER BY studentresult DESC
LIMIT 0,2
4.6、子查询(嵌套查询)
Select……from…….where……xxx=(Select……from…….where……)
Select……from…….where……xxx in (Select……from…….where……)
5、Mysql函数
5.1、常用函数
-- ========常用函数=========
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() --返回一个0~1之间的随机数
SELECT SIGN(8) --判断一个数的符号
-- 字符长函数
SELECT CHAR_LENGTH('请问而同样与偶') -- 字符串长度
SELECT CONCAT('我','我','我') -- 拼接字符串
SELECT INSERT('helloworld',1,5,'world') -- 查询,替换
SELECT LOWER('Cheng') -- 全部转换为小写
SELECT UPPER('CHENG') -- 全部转换为大写
SELECT INSTR('cehng','h') -- 字母在第几个出现
SELECT REPLACE('坚持努力','坚持','持续') -- 将指定位置的字符替换
SELECT SUBSTR('坚持努力',2,1) -- 返回指定的字符串(原字符串,第几个开始,截取几个)
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()
5.2、聚合函数(常用)
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
……….. |
-- =========聚合函数==========
-- 都能够统计表中的数据
SELECT COUNT(studentname) FROM student -- COUNT(指定列),会忽略所有的null值
SELECT COUNT(*) FROM student -- COUNT(*)不会忽略所有的null值,本质,计算行数
SELECT COUNT(1) FROM student -- COUNT(1)不会忽略所有的null值,本质,计算行数
SELECT sum(studentresult) as '总和' FROM result
SELECT avg(studentresult) as '平均分' FROM result
SELECT max(studentresult) as '最高分' FROM result
SELECT min(studentresult) as '最低分' FROM result
分组和过滤
-- 查询不同课程的平均分,最高分,最低分,平均分大于80
-- 核心:(根据不同的课程分组)
SELECT subjectname,AVG(studentresult) AS '平均分',MAX(studentresult),MIN(studentresult)
FROM result r
INNER JOIN `subject` sub
on r.subjectno = sub.subjectno
GROUP BY r.subjectno -- 通过什么字段来分组
HAVING 平均分 >80
5.3、数据库级别的MD5加密(扩展)
MD5:主要增强算法复杂度和不可逆性
-- 加密
UPDATE testma5 SET pwd = MD5(pwd) WHERE id=1
UPDATE testma5 SET pwd = MD5(pwd)
-- 插入的时候加密
INSERT INTO testma5 VALUES(5,'xiao',MD5('123456'))
-- 如何校验:将用户传递进来的密码,进行md5加密,然后对比加密后的值
SELECT *FROM testma5 WHERE `name`='xiao' AND pwd =MD5('123456')
6、事务
事务原则:ACID原则,原子性,一致性,隔离性,持久性
事务的隔离级别:
脏读:一个事务读取了另一个事务未提交的数据
不可重复度:
幻读:是指在 一个事务内读取到了别的事务插入的数据,导致前后读取不一致
-- ==============事务===============
-- MYSQL 是默认开启事务自动提交的
SET autoconnit = 0 -- 关闭
SET autoconnit = 1 -- 开始(默认)
-- 手动处理事务
SET autoconnit = 0
-- 事务开启
START TRANSACTION -- 标记事务开始
-- 提交:持久化(成功)
COMMIT
-- 回滚:回到原来样子(失败)
ROLLBACK
-- 事务结束
CLOSE TRANSACTION -- 标记事务结束
SAVEPOINT -- 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT -- 保存点名--回滚到保存点
RELEASE SAVEPOINT -- 删除保存点
模拟场景
set autocommit = 0;-- 关闭自动提交
START TRANSACTION;-- 开启一个事务
UPDATE account set money = money-500 WHERE `name` ='A' -- A减500
UPDATE account set money = money+500 WHERE `name` ='B' -- B加500
COMMIT -- 提交
ROLLBACK -- 回滚
set autocommit = 1 -- 恢复默认值
7、索引
7.1、索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
- 主键索引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; -- 非全文索引
测试索引:
-- 插入100万数据.
DELIMITER $$ -- 写函数之前必须要写,标志
set global log_bin_trust_function_creators=TRUE;
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`,`eamil`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'19224305@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'
-- id_表名_ 索引名
-- CREATE INDEX 索引名 on 表()
CREATE INDEX id_app_user_name ON app_user(`name`);
7.3、索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引常加在常用来查询的字段上
索引的数据类型
Hash类型的索引
Btree:innodb的默认数据结构
8、权限管理
8.1、用户管理
Sql命令操作
用户表:mysql.user
本质:读这张表进行增删改查
-- 创建用户 CREATE USER 用户名 IDENTIFIED by '密码'
CREATE USER cheng IDENTIFIED by '123456'
-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('111111')
-- 修改密码(修改指定用户的密码)
SET PASSWORD FOR 用户 =PASSWORD('111111')
-- 重命名
RENAME USER 用户 TO 新名
-- 用户授权ALL PRIVILEGES 全部的
-- ALL PRIVILEGES 除了给别人授权,其他权限都有
GRANT ALL PRIVILEGES ON *.* TO 用户
-- 查看权限
SHOW GRANT FOR 用户
-- 撤销权限 REVOKE 哪些权限,在哪个库,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM 用户
-- 删除用户
DROP USER 用户
8.2、mysql备份
为什么要备份:
保证重要的数据不丢失
数据转移
MYSQL数据库备份的方式
直接拷贝物理文件
在可视化工具中手动导出
使用命令行导出mysqldump
9、规范数据库设计
9.1、为什么需要设计数据库
当数据库比较复杂的时候,我们就需要设计了
糟糕的数据库设计:
- 数据的冗余,浪费空间
- 数据库插入和删除都有麻烦、异常【避免使用物理外键】
- 程序的性能差
良好的数据库设计:
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中,关于数据库的设计
- 分析需求:分析业务和需要处理的数据库的需求
- 概要设计:设计关系图E-R图
设计数据库的步骤:
- 收集信息,分析需求
- 标识实体(把需求落地到每个字段上)
9.2、三大范式
为什么要数据规范化?
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效信息
三大范式
第一范式(1NF)
原子性:保证每一列不可再分
第二范式(2NF)
前提:必须满足第一范式
每张表只描述一件事情
第三范式(3NF)
前提满足第一第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关
规范性 和 性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标,数据库的性能更为重要
- 在规范性能的问题时候,需要适当考虑规范性
- 故意给某表增加一些冗余的字段。(从多表变为单表)
- 故意增加一些计算列(从大数量降低为小数据量的查询:索引)
10、JDBC
10.1、JDBC
需要三个包
Java.sql(java自带)
Javax.sql(java自带)
还需要导入一个数据库驱动包
mysql-connector-java-8.0.16.jar
10.3、第一个JDBC程序
1.加入驱动
建立lib目录,将驱动jar包复制到该目录中,然后点击ADD AS LIBIARY,这才算将驱动完全加入idea
2.编写测试代码
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");//固定写法,加载驱动
//2.用户信息和url
String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
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 users";
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("pwd=" + resultSet.getObject("PASSWORD"));
System.out.println("email=" + resultSet.getObject("email"));
System.out.println("birthday=" + resultSet.getObject("birthday"));
System.out.println("=====================================================");
}
//6.释放链接
resultSet.close();
statement.close();
connection.close();
}
//statement.executeQuery()// 查询操作返回ResultSet
//statement.execute()可以执行任何sql
//statement.executeUpdate()更新,插入,删除,都用这个,返回受影响的行数
解决代码中冗余的部分
创建db.properties文件,将一下代码放入
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
username=root
password=123456
将1.加载驱动3.链接成功,4.执行sql的对象statement执行sql的对象5.执行sql的对象去执行sql6.释放链接这些部分封装成代码如下
删除,改动,增加,只需要改动sql语句
public static void main(String[] args) {
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
conn = jdbcUtils.getConnection();//获取数据库链接
st = conn.createStatement();//获得sql的执行对象
String sql="UPDATE users SET `NAME`= 'cheng' WHERE id=1";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("更新成功");
}
jdbcUtils.release(conn,st,rs);
} catch (SQLException e) {
e.printStackTrace();
}
}
查看方法加入ResultSet
public static void main(String[] args) {
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
conn = jdbcUtils.getConnection();
st= conn.createStatement();
//SQL
String sql="select * from users where id=1";
rs= st.executeQuery(sql);//查询完毕,返回一个结果集
if (rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,rs);
}
}
10.5、PreparedStatement对象
PreparedStatement对象可以防止sql注入,效率更高
public static void main(String[] args) {
Connection conn=null;
PreparedStatement st=null;
try {
conn = jdbcUtils.getConnection();
//区别
//使用问号占位符代替参数
String sql="INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";
st= conn.prepareStatement(sql);//预编译sql,先写sql,然后不执行
//手动赋值
st.setInt(1,4);//id
st.setString(2,"jia");
st.setString(3,"123456");
st.setString(4,"231564@qq.com");
//注意点:sql.Date 数据库使用
// util.Date java使用的new Date().getTime()获得时间戳
st.setDate(5,new java.sql.Date(new Date().getTime()));
//执行
int i = st.executeUpdate();
if (i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,null);
}
}
10.6、JDBC事务
代码实现
1、//关闭数据库的自动提交,自动会开启事务
conn.setAutoCommit(false);
2、//提交事务
conn.commit();
案例代码:
public static void main(String[] args) {
Connection conn=null;
PreparedStatement st=null;
ResultSet rs=null;
try {
conn= jdbcUtils.getConnection();
//关闭数据库的自动提交,自动会开启事务
conn.setAutoCommit(false);
String sql1="update account set money=money-500 where name='A'";
st=conn.prepareStatement(sql1);
st.executeUpdate();
int x= 1/0;//失败测试
String sql2="update account set money=money+500 where name='B'";
st=conn.prepareStatement(sql2);
st.executeUpdate();
//提交事务
conn.commit();
System.out.println("成功!");
} catch (SQLException e) {
try {
conn.rollback();//如果失败就回滚,如果失败默认回滚
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,rs);
}
}