目录
1、初始MySQl
JavaEE:企业级java开发 Web
前端(页面:展示,数据!)
后台(连接点:链接数据库JDBC,连接前端(控制,控制试图跳转,和给前段传递数据))
数据库(存数据,Txt,Excel,word)
操作系统,数据结构和算法 离散数学,数字电路,体系结构,编译原理+实战经验
1.1、为什么学习数据库
1.岗位需求
2.大数据时代
3.被迫需求:存数据
4.数据库是所有软件体系中最核心的存在
1.2、什么是数据库
数据库(DB, DataBase)
概念:数据仓库,软件,安装在操作系统(Windows,Linux,Mac...)之上 SQL:可以存储大量的数据
作用:存储数据,管理数据
1.3、DBMS
关系型数据库:
-
MySQL, Oracle, Sql Server,DB2,SQLLlite
-
通过表和表之间,行列之间的关系进行数据的存储, 学员信息表,考勤表...
非关系型数据库
-
Redis ,MongoDB
-
非关系型数据库,对象存储,通过对象的自身的属性来决定
DBMS(数据库管理系统)
-
数据库的管理软件,科学有效的管理我们的数据。维护和获取数据;
-
MySQL本质:数据库管理系统!
1.4、MySQL简介
MySQL是一个关系型数据库管理系统
前世:瑞典MySQL AB 公司开发
今生:属于 Oracle 旗下产品
在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。
开源的数据库软件
体积小、速度快、总体拥有成本低,
中小型网站、或者大型网站、集群!
官网:MySQL
安装建议:
1.尽量不要使用exe,注册表
2.尽可能使用安装包下载。
1.5连接数据库
命令行连接
mysql -uroot -p123456 --连接数据库
flush privileges; --刷新权限
------------------------------------
show databases; --查看所有的数据库
mysql> use school --切换数据库 use 数据库名
Database changed
show tables; --查看数据库中所有的表
describe student;--显示数据库中所有的表的信息
create database westos; --创建一个数据库
exit --退出连接
--单行注释
/*
多行注释
*/
数据库xxx语言 CRUD增删改查
DDL 定义
DML 操作
DQL 查询
DCL 控制
2、操作数据库
操作数据库>操作数据库中的表>操作数据库中表的数据
2.1、操作数据库(了解)
1.创建数据库
create database westos;
2.删除数据库
DROP DATABASE IF EXISTS westos;
3.使用数据库
USE `school`
--tab键上面,如果你的表名或者字段名是一个特殊字符,就需要带
4.查看数据库
show databases --查看所有的数据库
学习思路:
-
对照sqlyog可视化历史记录查看sql
-
固定的语法或关键字必须要强行记住!
2.2、数据库的列类型
数值
-
tinyint 十分小的数据 1个字节
-
smallint 较小的数据 2个字节
-
mediumint 中等大小的数据 3个字节
-
int 标准的整数 4个字节 常用的
-
bigint 较大的数据 8个字节
-
float 浮点数 4个字节
-
double 浮点数 8个字节
-
decimal 字符串形式的浮点数 (金融计算的时候,一般使用decimal)
字符串
-
char 字符串固定大小的 0~255
-
varchar 可变字符串 0~65535 常用的 String
-
tinytext 微型文本 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 时间戳, 19701.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,默认值为男,如果不指定该列的值,则会有默认的值!
拓展:听听就好
/*每一个表,都必须存在以下5个字段!未来做项目,表示一个记录存在的意义
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_creat 创建时间
gmt_update 修改时间
*/
2.4、创建数据库表(重点)
/*
创建学生表(列,字段)使用sql创建
学号int,登陆密码varchar(20),姓名,性别varchar(2),出生日期(datetime),家庭住址,email
注意点:使用英文(),表的名称和字段尽量使用``括起来
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(20) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
格式
CREATE TABLE [IF NOT EXISTS] `表名`( `字段名` 列类名 [属性] [索引][注释], `字段名` 列类名 [属性] [索引][注释], `字段名` 列类名 [属性] [索引][注释], ...... `字段名` 列类名 [属性] [索引][注释] )[表类型][字符集设置][注释]
常用命令
show create database school -- 查看创建数据库的语句
show create table student -- 查看student数据表的定义语句
desc student -- 显示表的结构
2.5、数据表的类型
-- 关于数据库的引擎
/*
INNODB 默认使用
MYISAM 早些年使用
*/
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为MYISAM两倍 |
常规使用操作:
-
MYISAM 节约空间,速度较快
-
INNODB 安全性高,事务的处理,多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下 一个文件夹对应一个数据库
本质还是文件的存储
MySQL引擎在物理文件上的区别
-
InnoDB 在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
-
MYISAM对应的文件
-
*.frm -表结构的定义文件
-
*.MYD 数据文件(data)
-
*.MYI 索引文件(index)
-
设置数据库表的字符集编码
CHARSET=utf8
不设置的话,会是mysql默认的字符集编码~(不支持中文!)
MySQL的默认编码是Latin1,不支持中文
在my.ini中配置默认的编码
character-set-server=utf8
2.6、修改和删除
修改
-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)
-- 修改表的字段 (重命名,修改约束)
ALTER TABLE 表名 MODIFY 字段名 列属性
ALTER TABLE teacher1 MODIFY age VARCHAR(11)-- 修改约束
ALTER TABLE teacher1 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age agel INT(11) -- 字段重命名
DESC teacher1
-- 删除表的字段 ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP agel
删除
-- 删除表(如果表存在再删除)
DROP TABLE IF EXISTS teacher1
所有的创建和删除操作尽量加上判断,以免报错。
注意:
-
``字段名,使用这个包裹!
-
注释 -- /**/
-
sql关键字大小写不敏感,建议用小写
-
所有的符号用英文
3、MySQL数据管理
3.1、外键(了解)
3.2、DML语言(重点)
数据库意义:数据存储,数据管理
DML语言:数据操作语言
-
insert
-
update
-
delete
3.3、添加
insert
-- 插入语句(添加)
-- insert into 表名([字段名1,字段2,字段3])values('值1'),('值2'),('值3'),(....)
INSERT INTO `grade`(`gradename`)VALUES('大四')
-- 由于主键自增我们可以省略(如果不写表的字段,他就会一一匹配)
INSERT INTO `grade` VALUES('大三')
-- 一般写插入语句,我们一定要数据和字段一一对应!
-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES('大二'),('大一')
INSERT INTO `student`(`name`) VALUES('张三')
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES('张三','aaaaaa','男')
INSERT INTO `student`(`name`,`pwd`,`sex`)
VALUES('李四','bbbbbb','男'),('王五','ccccccc','男')
语法:insert into 表名([字段名1,字段2,字段3])values('值1'),('值2'),('值3'),(....)
注意事项:
-
字段和字段之间使用英文逗号隔开
-
字段可以省略的,但是后面的值必须要一一对应,不能少
-
可以同时插入多条数据,values后面的值,需要使用,隔开始用(''),(''),('')
3.4、修改
update
-- 修改学员名字 指定条件
UPDATE `student` SET `name`='派大星' WHERE id=1;
-- 不知指定的情况下,会改动所有表!
UPDATE `student` SET `name`='海绵宝宝'
-- 语法:
-- UPDATE 表名 set colnum_name=value where [条件]
-- 修改多个属性
UPDATE `student` SET `name`='章鱼哥',`sex`='男' WHERE id=3;
-- 语法:
-- UPDATE 表名 set colnum_name=value,[colnum_name=value,....] where [条件]
条件:where子句 运算符 id等于某个值,大于某个值,在某个区间内修改。。。
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | ||
<> , != | 不等于 | ||
> | |||
< | |||
>= | |||
<= | |||
between...and... | [2,5] | ||
AND | && | 5>1and1>2 | false |
or | 5>1or1>2 | true |
语法:UPDATE 表名 set colnum_name=value,[colnum_name=value,....] where [条件]
注意:
-
colnum_name是数据库的列,尽量带上``
-
条件,筛选的条件,如果没有指定,则会修改是所有的列
-
value,是一个具体的值,可以是一个变量
-
多个设置的属性之间,使用英文逗号隔开
UPDATE `student` SET `pwd`=CURRENT_TIME WHERE id=4 AND sex='男'
3.5、删除
delete命令
语法:delete from 表名[where 条件]
DELETE FROM `student` WHERE id=1;
TRUNCATE命令
作用:完全清空一个数据库,表的结构和索引约束不会变
-- 清空 student 表
TRUNCATE `student`
delete和TRUNCATE的区别
-
都可以删除数据,都不会删除表结构
-
不同
-
TRUNCATE 重新设置 自增列 计数器会归零
-
TRUNCATE 不会影响事务
-
-- 测试delete和truncate 区别
CREATE TABLE`test`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `test`(`coll`)VALUES('1'),('2'),('3')
DELETE FROM `test`-- 不会影响自增
TRUNCATE TABLE `test`-- 自增量会归零
了解即可:delete删除问题,重启数据库,现象
-
INNODB 自增列会从1开始(存在内存当中,断电即失)
-
MyISAM 继续从上一个自增开始(存在文件中,不会丢失)
4、DQL查询数据(超重点)
4.1、DQL
(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
语法:select 字段....from 表
有的时候,列的名字不是那么的见名知意。我们就起别名 AS 字段名 AS 别名 表名 AS 别名
去重:distinct
作用:去除select查询出来的结果中重复的数据,重复的数据只显示一条
-- 查询那些同学参加了考试
SELECT *FROM result-- 查询全部的考试成绩
SELECT `studentno` FROM result-- 查询有哪些同学参加了考核
-- 发现重复数据,去重
SELECT DISTINCT`studentno`FROM result
数据库的列
SELECT VERSION()-- 查询系统版本(函数)
SELECT 100*3-1 AS 计算结果-- 用来计算(表达式)
SELECT @@auto_increment_increment-- 查询自增的步长(变量)
-- 学员考试成绩+1分查看
SELECT `studentno`,`studentresult`+1 AS '提分后' FROM result
数据库中的表达式:文本值,列,null,函数,计算表达式,系统变量.....
select 表达式 from 表
4.3、where条件子句
作用:检索数据中符合条件的值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 逻辑与 |
or || | a or b a||b | 逻辑或 |
Not ! | not a !a | 逻辑非 |
-- 查询考试成绩在95~100分之间
SELECT studentno,`studentresult`FROM result
WHERE studentresult>=95 AND studentresult<=100
-- 模糊查询(区间)
SELECT studentno,`studentresult`FROM result
WHERE studentresult BETWEEN 95 AND 100
-- 除了1000号学生之外的同学的成绩
SELECT studentno,`studentresult`FROM result
WHERE studentno!=1000
-- != not
SELECT studentno,`studentresult`FROM result
WHERE NOT studentno=1000
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
is null | a is 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...其中的一个,则结果为真 |
-- ===================模糊查询===================
-- 查询姓赵的同学
SELECT `studentno`,`studentname`FROM student
WHERE studentname LIKE '赵%'
-- 查询姓赵的同学,名字后面只有1个字
SELECT `studentno`,`studentname`FROM student
WHERE studentname LIKE '赵_'
-- 查询姓赵的同学,名字后面只有2个字
SELECT `studentno`,`studentname`FROM student
WHERE studentname LIKE '赵__'
-- 查询名字中间有嘉字的同学 '_%嘉_%'
SELECT `studentno`,`studentname`FROM student
WHERE studentname LIKE '_%嘉_%'
-- ===== in(具体的一个或多个值) =====
-- 查询1001,1002,1003号学员
SELECT `studentno`,`studentname`FROM student
WHERE studentno IN(1001,1002,1003);
-- 查询在北京的学员
SELECT `studentno`,`studentname`FROM student
WHERE address IN('北京');
-- =====null not null=====
-- 查询地址为空的学生null
SELECT `studentno` studentname FROM student
WHERE address IS NULL OR address=''
-- 查询有出生日期的学员 不为空
SELECT `studentno` studentname FROM student
WHERE borndate IS NOT NULL
4.4、联表查询
-- ===========联表查询 join============
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
SELECT *FROM student
SELECT *FROM result
SELECT s.studentno,studentname,studentresult,subjectno
FROM student AS s
INNER JOIN result AS r
ON s.studentno=r.studentno
-- right join
SELECT s.studentno,studentname,studentresult,subjectno
FROM student s
RIGHT JOIN result r
ON s.studentno=r.studentno
-- left join
SELECT s.studentno,studentname,studentresult,subjectno
FROM student s
LEFT JOIN result r
ON s.studentno=r.studentno
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就返回 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值 |
-- ===========联表查询 join============
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
-- join on 连接查询
-- where 等值查询
SELECT *FROM student
SELECT *FROM result
SELECT s.studentno,studentname,studentresult,subjectno
FROM student AS s
INNER JOIN result AS r
where s.studentno=r.studentno
-- right join
SELECT s.studentno,studentname,studentresult,subjectno
FROM student s
RIGHT JOIN result r
ON s.studentno=r.studentno
-- left join
SELECT s.studentno,studentname,studentresult,subjectno
FROM student s
LEFT JOIN result r
ON s.studentno=r.studentno
-- 查询缺考同学
SELECT s.studentno,studentname,studentresult,subjectno
FROM student s
LEFT JOIN result r
ON s.studentno=r.studentno
WHERE studentresult IS NULL
-- 思考题(查询了参加考试的同学信息:学号,学生姓名,科目名,分数)
/*
1.分析需求,分析查询的字段来自那些表,student,result,subject(连接查询)
2.确定使用的哪种连接查询?(7种)
确定交叉点(这两个表中哪个数据是相同的)
判断的条件:学生表中的studentno=成绩表 studentno
*/
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
-- 我要查询那些数据 select...
-- 从哪几个表中查 from 表 xxx join 连接的表 on 交叉条件
-- 假设存在多张表查询,慢慢来,先查询两张表然后在慢慢增加
-- =========== 自连接 ============
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` AS '父类',b.`categoryName` AS '子类'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`
-- 查询学员所属的年级(学号,学生姓名,年级名称)
SELECT studentno,studentname,`gradename`
FROM student s
INNER JOIN `grade` g
ON s.`gradeid`=g.`gradeid`
-- 查询科目所属的年级(科目名称,年级名称)
SELECT subjectname,gradename
FROM grade g
INNER JOIN `subject` s
ON g.`gradeid`=s.`gradeid`
-- 查询参加数据结构-1考试的同学信息:学号,学生姓名,科目名,分数
SELECT s.`studentno`,studentname,subjectname,studentresult
FROM student s
RIGHT JOIN result r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE subjectname = '数据结构一'
4.5、分页和排序
排序:ORDER BY
-- 排序:升序 ASC,降序 DESC
-- order by 通过哪个字段排序,怎么排
-- 查询的结果根据 成绩降序 排序
SELECT s.`studentno`,studentname,subjectname,studentresult
FROM student s
RIGHT JOIN result r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE subjectname = '数据结构一'
order by studentresult asc
分页:LIMIT
-- 分页,每页只显示五条数据
-- 语法:limit 起始值,页面的大小
-- limit 0,5 1~5
-- limit 1,5 2~5
-- limit 5,5 6~10
SELECT s.`studentno`,studentname,subjectname,studentresult
FROM student s
RIGHT JOIN result r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE subjectname = '数据结构一'
ORDER BY studentresult ASC
LIMIT 5,5
4.6、子查询
where(这个值是计算出来的)
本质:在where语句中嵌套一个子查询语句
-- 练习:查询c语言-1 前5名同学的成绩的信息(学号,姓名,分数)
-- 使用子查询
SELECT s.`studentno`,`studentname`,`studentresult`
FROM student s
INNER JOIN result r
ON s.`studentno`=r.`studentno`
WHERE `subjectno`=(
SELECT `subjectno` FROM `subject`
WHERE `subjectname`='c语言-1'
)
LIMIT 0,5
4.7、分组和过滤
-- 查询不同课程的平均分,最高分,最低分
-- 核心:(根据不同的课程分组)
SELECT subjectname,AVG(studentresult),MAX(studentresult),MIN(studentresult)
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
GROUP BY r.subjectno -- 通过什么字段来分组
HAVING AVG(studentresult)>80
5、MySQL函数
5.1、常用函数
-- =============== 常用函数 ==================
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4)-- 向下取整
SELECT RAND()-- 返回0~1之间的随机数
SELECT SIGN(1)-- 判断一个数的符号 0-0 负数返回-1,正数返回 1
-- 字符串函数
SELECT CHAR_LENGTH('即使最小的帆也能远航')-- 字符串长度
SELECT CONCAT('周','杰','伦')-- 拼接字符串
SELECT INSERT('周杰伦',2,1,'月半')-- 查询,替换
SELECT LOWER('PXXPDX')-- 转为小写
SELECT UPPER('pxxpdx')-- 转为大写
SELECT INSTR('pxxpdx','d')-- 返回在字符串中第一次出现的索引
SELECT REPLACE('你我山前没相见','山前没相见','山后别相逢')-- 替换出现的指定的字符串
SELECT SUBSTR('你我山前没相见,山后别相逢',3,11)-- (截取字符串,截取位置,截取长度)
SELECT REVERSE('你我山前没相见,山后别相逢')-- 反转
-- 查询姓周的同学 替换邹
SELECT REPLACE(studentname,'周','邹')FROM student
WHERE studentname LIKE '周%'
-- 时间和日期函数(记住)
SELECT CURRENT_DATE()-- 获取当前日期
SELECT CURDATE()-- 获取当前日期
SELECT NOW()-- 获取此时的时间
SELECT LOCALTIME()-- 获取此时的日期
SELECT SYSDATE()-- 系统时间
SELECT YEAR()-- 年
SELECT MONTH()-- 月
SELECT DAY()-- 日
SELECT HOUR()-- 时
-- 系统
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
5.3、数据库级别的MD5加密(扩展)
什么是MD5?
主要增强算法复杂度和不可逆性
MD5不可逆,具体的值的MD5是一样的
MD5破解网站的原理,背后有一个字典,MD5加密后的值,加密前的值
-- ================ 测试MD5加密 ================
CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(15) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 明文密码
INSERT INTO testmd5 VALUES(1,'san','123456'),(2,'si','123456'),(3,'wu','123456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd)-- 加密全部的密码
-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'liu',MD5('123456'))
-- 如何校验:将用户传递进来的密码,进行MD5加密,然后比对加密后的值
SELECT *FROM testmd5 WHERE `name`='liu' AND pwd=MD5('123456')
SELECT各子句的顺序和功能
语法结构
SELECT select_list # 1个或多个列名,之间用逗号隔开,列也称作投影
[ INTO new_table ] # 结果集放入指定文件
FROM [table_source] #表名
[ WHERE search_condition ] #表行过滤条件
[ GROUP BY group_by_expression ] #按照指定的列将表行分组,形成新的行
[ HAVING search_condition ] #对分组后的新行进行过滤
[ ORDER BY order_expression [ ASC | DESC ] ] #按照指定的1个或多个列进行排序,ASC=增序,DESC=降序
[LIMIT 0,5] #分页
6、事务
6.1、什么是事务
要么都成功,要么都失败
1、SQL执行 A 转给 B 200 A 1000 B 200
2、SQL执行 B收到A的钱 A 800 B 400
将一组SQL放在一个批次中去执行~
事务原则:ACID原则 原子性、一致性、隔离性、持久性 (脏读、幻读...)
原子性(atomicity)
要么都成功,要么都失败
一致性(consistency)
事务前后的数据完整性要保持一致
隔离性(isolation)
多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,事物之间要互相隔离。
持久性(durability)
事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作或故障不应该对其有任何影响
-- 隔离所导致的一些问题
脏读:
指一个事务读取了另外一个事务未提交的数据。
不可重复读:
在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据,导致前后读取数量总量不一致。
执行事务
-- ======================== 事务 ========================
-- mysql 是默认开始事务自动提交的
SET autocommit=0 /*关闭*/
SET autocommit=1 /*开启(默认的)*/
-- 手动事务处理
SET autocommit=0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始,从这个之后sql都在同一个事务内
-- 提交:持久化(成功)
COMMIT
-- 回滚:回到原来的样子(失败)
ROLLBACK
-- 事务结束
SET autocommit=1 -- 开启自动提交
-- 了解
SAVEPOINT 保存点 -- 设置一个事务的保存点
ROLLBACK SAVEPOINT 保存点 -- 回滚到保存点
RELEASE SAVEPOINT 保存点 -- 撤销保存点
-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `count`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL,
`money` DECIMAL(8,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `count`(`name`,`money`)
VALUES('A','2000.00'),('B','10000.00')
-- 模拟转账:事务
SET autocommit=0 -- 关闭自动提交
START TRANSACTION -- 开启事务
UPDATE `count` SET money=money+500 WHERE `name`='A' -- A加500
UPDATE `count` SET money=money-500 WHERE `name`='B' -- b减500
COMMIT -- 提交事务 ,被持久化
ROLLBACK -- 回滚
SET autocommit=1 -- 回复默认值
7、索引
官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。
7.1、索引的分类
-
主键索引(PRIMARY KEY)
-
唯一的标识,主键不可重复,一张表只能有一个主键
-
-
唯一索引(UNIQUE KEY)
-
避免重复的列出现,一个表中可以标识多个唯一索引
-
-
常规索引(KEY/INDEX)
-
默认的,index,key关键字设置
-
-
全文索引(FULLTEXT)
-
在特定的数据库引擎下才有,MyISAM
-
快速定位数据
-
基础语法
-- 索引的使用
-- 1.在创建表的时候给字段添加索引
-- 2.创建完毕后,增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个全文索引 列名(索引名)
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`)
-- explain 分析sql执行的状况
EXPLAIN SELECT *FROM student;-- 非全文索引
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('赵')
7.2、测试索引
-- 创建用户表
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 DEFAULT '' COMMENT '密码',
`age` TINYINT(4) DEFAULT NULL 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
-- 插入100万条数据
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
DETERMINISTIC
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';-- 1.034 sec
SELECT *FROM app_user WHERE `name`='用户9999';-- 1.025 sec
SELECT *FROM app_user WHERE `name`='用户9999';-- 0.025 sec
SELECT *FROM student
-- id_表名_字段名
-- CREATE INDEX 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT *FROM app_user WHERE `name`='用户9999';-- 0.002 sec
SELECT *FROM app_user WHERE `name`='用户9999';-- 0 sec
索引在小数据量的时候,区别不大,但是在大数据的时候,区别十分明显~
7.3、索引原则
-
索引不是越多越好
-
不要对进程变动数据加索引
-
小数据的量的表不需要加索引
-
索引一般加在常用来查询的字段上
索引的数据结构:
Hash类型的索引
Btree:INNODB的默认的数据结构
阅读:CodingLabs - MySQL索引背后的数据结构及算法原理
8、权限管理和备份
8.1、用户管理
SQL命令操作:
用户表:mysql. user
本质:读这张进行增删改查
8.2、Mysql备份
为什么要备份:
-
保证重要的数据不丢失
-
数据转移
MySQL数据库备份方式
-
直接拷贝物理文件
-
在sqlyog这种可视化工具中动手导出
-
使用命令行导出 mysqldump 命令行使用
9、规范数据库设计
9.1、为什么是需要设计
当数据库比较负责的时候,我们就需要设计了
糟糕的数据库设计:
-
数据冗余,浪费空间
-
数据库插入和删除都会麻烦、异常[屏蔽使用物理外键]
-
程序的性能差
良好的数据库设计
-
节省内存空间
-
保证数据库的完整性
-
方便我们的系统
软件开发中,关于数据库的设计
-
分析需求:分析业务和需求处理的数据库的需求
-
概要设计:设计关系图 E-R图
设计数据库的步骤:个人博客
-
手机信息,分析需求
-
用户表(用户登录,用户id,用户的个人信息,写博客,创建分类)
-
分类表(文章分类,谁创建的)
-
文章表
-
评论表
-
友链表
-
自定义表(系统信息,某个关键字,或者一些主字段) key:value
-
-
标识实体(把需求落地到每个字段)
-
标识实体 之间的关系
-
写博客:user--blog
-
创建分类:user---category
-
关注:user---user
-
友联:links
-
评论:user--user--blog
-
10、数据库的归约,三大范式
为什么需要数据规范化
-
信息重复
-
更新异常
-
插入异常
-
无法正常的显示信息
-
-
删除异常
-
丢失有效的信息
-
三大范式
第一范式(1NF)
原子性:保证每一列不可再分
第二范式(1NF)
前提:满足第一范式
需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。
第三范式(1NF)
前提:满足第一范式和第二范式
需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
规范性和性能的问题
关联查询的表不得超过三张
-
考虑商业化的需求和目标,(成本,用户体验)数据库的性能更加重要
-
在规范性能问题的时候,需要适当地考虑一下规范性
-
故意给某些表增加一些冗余的字段(从多表查询变为单表查询)
11、JDBC(重点)
11.1、数据库驱动
驱动:声卡、显卡、数据库
我们的程序会通过数据库驱动和数据库打交道!
11.2、JDBC
SUN公司为了简化开发人员的(对数据库的统一)操作,提供了一个(java操作数据库的)规范,俗称JDBC
这些规范的实现由具体的厂商去做~
对于开发人员来说,我们只需要掌握JDBC接口的操作即可!
java.sql
javax.sql
还需要导入一个数据库驱动包mysql-connector-java-8.0.26.jar
11.3、第一个JDBC程序
1、创建一个普通项目
2、导入数据驱动
3、编写测试代码
package www.pxx.lesson01;
import java.sql.*;
/**
*我的第一个JDBC程序
*/
public class JDBC01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");//固定写法,加载驱动
//2.用户信息和url
//useUnicode=true&characterEncoding=utf8&useSSL=true
String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf8&useSSL=true";
String usename = "root";
String password="123456";
//3.连接成功,数据库对象 Connection 代表数据库
Connection connection = DriverManager.getConnection(url, usename, password);
//4.执行SQL的对象 Statement 执行SQL的对象
Statement statement = connection.createStatement();
//5.执行SQL的对象 去 执行SQL,可能存在结果,查看返回结果
String sql="select *from test";
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("sex="+resultSet.getObject("sex"));
System.out.println("pwd="+resultSet.getObject("pwd"));
System.out.println("---------------------------------------------------");
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
1、加载驱动
2、连接数据库DriverManager
3、获得执行sql的对象Statement
4、获得返回的结果集
5、释放连接
DriverManager
//DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
Class.forName("com.mysql.cj.jdbc.Driver");//固定写法,加载驱动
Connection connection = DriverManager.getConnection(url, usename, password);
//connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.commit();
connection.rollback();
connection.setAutoCommit();
URL
String url="jdbc:mysql://localhost:3306/jdbc?
useUnicode=true&characterEncoding=utf8&useSSL=true";
//mysql -- 3306
//协议://主机地址:端口号/数据库名?参数1&参数2&参数3
//oralce -- 1521
//jdbc:oralce:thin@localhost:1521:sid
Statement 执行SQL的对象 prepareStatement执行SQL的对象
String sql="select *from test";//编写sql
statement.executeQuery();//查询操作返回的 resultSet
statement.execute();//执行所有的SQL
statement.executeUpdate();//更新、插入、删除。都是用这个。返回一个受影响的行数
ResultSet 查询的结果集:封装了所有的查询结果
获得执行的数据类型
resultSet.getObject();//在不知道类型的情况下使用
//如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getTime();
resultSet.getBigDecimal();
遍历,指针
resultset.next();//移动到下一个数据
释放资源
resultSet.close();
statement.close();
connection.close();//耗资源,使用完关闭
11.4、statement对象
11.9、数据库连接池
数据库连接--执行完毕--释放
连接--释放--十分浪费系统资源
池化技术:准备一些预先的资源,过来就连接预先准备好的
最少连接数:10
最大连接数:15
等待超时:100ms
编写连接池,实现一个接口: DataSource
开源数据源实现
DBCP
C3P0
Druid:阿里巴巴
使用这些数据库之后,我们在项目开发中就不需要编写连接数据库的代码了。
DBCP
需要用到的jar包
commons-dbcp- commons-pool-