mysql数据库
1.初识MySQL数据库
前端：页面展示数据
后台：连接点-数据库JDBC、链接前端-控制视图跳转和给前端传数据
数据库：存储数据
2. mysql的安装
选择绿色压缩包版，以后卸载方便
https://dev.mysql.com/downloads/mysql/5.7.html， 下载zip包
- 解压
- 配置环境变量，直接在path中添加mysql的bin文件夹
- 在bin文件夹同级文件夹新建一个文件，my.ini
[mysqld]
basedir=E:\mysql\mysql-5.7.33\mysql-5.7.33-winx64
datadir=E:\mysql\mysql-5.7.33\mysql-5.7.33-winx64\data\
port=3306
skip-grant-tables
- 第二行基础文件夹，是mysql的bin目录同级位置
- 第三行的数据文件夹，这是以后数据存储的地方，自己不用新建，电脑会在初始化的时候帮我们创建完成
- 第四行是MySQL的端口号
- 第五行是跳过权限验证，因为我们第一次安装，因此我们不能添加权限。
- 管理员模式下启动CMD
- 前往mysql的bin目录(cd /d 路径)，输入mysqld install
- 输出，Service successfully installed.安装成功
- 在输入命令初始化数据库文件，mysqld --initialize-insecure --user=mysql，执行完会自己创建data目录
- 通过命令(net start mysql)，进入mysql
- 用命令mysql -u root -p，进入mysql管理页面（密码可以为空）
- p后面不要有空格
- enter后的密码直接空格
- 已进入mysql，输入以下命令设置密码等
//update mysql.user set authentication_string = password ('新密码') where user='root' and Host='localhost';
update mysql.user set authentication_string = password ('123456') where user='root' and Host='localhost';
- 不要忘记分号结尾
- 输入，flush privileges;，刷新权限
- 打开my.ini，#skip-grant-tables，最后一行注释掉
- 重启mysql，即可正常使用
- net stop mysql
- net start mysql
- 安装有问题输入，sc delete mysql，清空服务
- 安装sqlyog
- 每一个sqlrog的命令就是一句sql命令
3.连接数据库
命令行链接
mysql -u root -p密码 --连接数据库
update mysql.user set authentication_string = password ('123456') where user='root' and Host='localhost'; --修改用户密码
flush privileges; --刷新权限
-- 所有命令用;结尾
show databases; --查看所有数据库、
Database changed
mysql> use school; --切换表
show tables; --查看表
describe student; --显示数据库中所有表的信息
create database westos; --创建数据库
exie; --退出连接
--单行注释
/*
多行注释
*/
数据库xxxx语言
- DDL，数据库定义语言
- DML，数据库操作语言
- DQL，数据库查询语言
- DCL，数据库控制语言
4.数据库操作
- mysql数据库不区分大小写
4.1操作数据库
- 创建数据库
CREATE DATABASE [IF NOT EXISTS] westos;
--[]中的为可选，{}为必选
- 删除数据库
DROP DATABASE IF EXISTS westos;
- 使用数据库
-- 如果你的表名或者字段名是一个特殊字符，就要带``
USE `school`;
- 查看所有的数据库
SHOW DATABASES;
4.2数据库的数据类型
- 数值
tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中等 3个字节
int 标准的整数 4个字节
bigint 大 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，时分秒
datetime YYYY-MM-DD HH:mm:ss
timestamp 时间戳，1970 1.1到现在的毫秒数
year 年 - null
没有值，为止
不要使用NULL进行计算，因为结果一定为NULL
4.3数据库的字段属性（重点）
- unsigned
无符号的整数，不能为负值 - zerofill
0填充
不足的位数用0填充 - 自增
自动在上一条记录上加1
通常用来设置为一的主键，必须是整数
可以自己设定自增的起始值和步长 - 非空 Null not null
如果不给他赋值就会报错 - 默认
如果不给定值，就会给其赋值为默认值 - 创建表必须要有的几个属性
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_uptate 修改时间
4.4创建数据库表
-- AUTO_INCREMENT，自增
-- COMMENT，注释
-- DEFAULT，默认值
-- PRIMARY KEY，主键
-- CHARSET，设置字符集编码
-- ENGINE，引擎
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 CHARSET=utf8;
4.5数据表的类型
SHOW CREATE DATABASE `school` -- 查看创建数据库的语句
SHOW CREATE TABLE `student` -- 查看创建表的语句
DESC `student` -- 显示表数据
-- 关于数据库引擎
/*
INNODB 默认使用，安全性高、事务处理、多表多用户操作
MYISAM 很早之前，节省空间、速度较快
CHARSET=utf8 设置数据库的编码集，默认是Latin1，不支持中文。可以在my.ini文件中设置默认编码集，character-set-server=utf8
*/
4.6修改和删除表
-- 修改表名，ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段
ALTER TABLE teacher1 ADD age INT(11)
-- 修改表的字段
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
ALTER TABLE teacher1 CHANGE age age1 INT(11) --字段重命名
-- 删除表的字段
ALTER TABLE teacher1 DROP age1
-- 删除表
DROP TABLE IF EXISTS teacher1
-- 所有的创建删除操作加上判断条件
- 注意点
- 所有的表名、数据库名，用反引号括起来
- 注释 --、/**/
- sql的大小写书写不敏感
5.mysql的数据管理
5.1外键
- 删除有外键的表，要先删除被引用的表
- 一下都是物理外键，数据库级别的外键，不建议使用，避免数据库过多造成困扰
-- 方式一在创建表是，增加约束。麻烦
CREATE TABLE `gread`(
`greadid` INT(10) NOT NULL COMMENT '年级id',
`greadname` VARCHAR(50) NOT NULL COMMENT '年级名',
PRIMARY KEY(`greadid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
CREATE TABLE `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 '出生日期',
`greadid` INT(10) NOT NULL COMMENT '学生年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`),
KEY `FK_greadid` (`greadid`),
CONSTRAINT `FK_greadid` FOREIGN KEY (`greadid`) REFERENCES `gread`(`greadid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 方式二在创建表之后
-- 创建表时没有外键关系，现在添加外键关系
-- ALTER TABLE `表名` ADD CONSTRAINT `FK_greadid` FOREIGN KEY(`作为外键的列`) REFERENCES `另一个表`(`那一列`);
ALTER TABLE `student` ADD CONSTRAINT `FK_greadid` FOREIGN KEY (`greadid`) REFERENCES `gread`(`greadid`);
- 最佳实践
- 数据库就是单纯的表，只用来存数据，只有行(数据)和列(属性)
- 我们想使用多张表，就用程序实现
5.2DML语言（全部记住）
- 数据操作语言
- insert
- delete
- update
5.3添加
- insert
- 语法
- insert into 表名(字段)values(字段值)
-- 插入数据
-- insert ioto 表名([字段名1,字段名2,字段名3])values('值1'),('值2'),('值3')
INSERT INTO `gread`(`greadname`)VALUES('大四')
-- 由于主键自增我们可以省略(如果不写表的字段，他会一一匹配)
INSERT INTO `gread` VALUES('大三')
-- 一般写插入语句，我们一定要数据和字段一一对应
-- 插入多行数据
INSERT INTO `gread`(`greadname`)VALUES('大二'),('大一')
-- 添加一个属性
INSERT INTO `student`(`name`)VALUES('张三')
-- 添加一串属性
INSERT INTO `student`(`name`,`pwd`,`sex`)VALUES('张三','dweewfwe','男')
-- 添加几组数据
INSERT INTO `student`(`name`,`pwd`,`sex`)VALUES('李四','dweewfwe','男'),('王五','dweewfwe','男')
- 注意事项
- 字段之间用英文逗号
- 字段值可以省略，但是属性值需要一一对应
- 可以同时插入多条数据，values后面的数据用“,”隔开values(),(),()
5.4修改
- update
- 语法
- UPDATE
表名SET字段=‘新值’,字段=‘新值’,字段=‘新值’ WHERE 判断条件;
- UPDATE
-- 修改学员名称
UPDATE `student` SET `name`='鱼汤' WHERE id = 1;
-- 不指定条件会修改所有的数据
UPDATE `student` SET `name`='所有'
-- 修改多个属性，逗号隔开
UPDATE `student` SET `name`='鱼汤',`sex`='男',`pwd`='22222' WHERE id = 1;
-- 语法
UPDATE `表名` SET `字段`='新值',`字段`='新值',`字段`='新值' WHERE 判断条件;
- where子句的作用是按条件查找
- =，等于
- <>、!=，不等于
- between a and b，在a-b区间之内[a,b]
- and，多个条件都成立才成立
- or，或
- <、>、<=、>=，
- 注意
- 没有筛选条件，就会修改所有的列
- value是一个值，也可以是一个变量
5.5删除
- delete
- 语法
- delete from 表名 [where 表名]
- 无条件就会全部删除
-- 删除数据
DELETE FROM `student` WHERE id = 1;
- truncate，清空数据库表
-- 清空表
TRUNCATE table `student`
- delete与truncate
- 相同点，都能删除所有数据
- 不同点，truncate会重新设置自增列，是计数器归零，并且不会影响事务
-- 测试delete与TRUNCATE
CREATE TABLE `test`(
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 添加数据
INSERT INTO `test`(`name`)VALUES('koqdjoi')
DELETE FROM `test` -- 计数器没有归0
TRUNCATE `test` -- 自增归0
6.DQL(重点)
6.1DQL
- 数据查询语言
- 所有的查询都要使用
- 简单查询与复杂查询都需要使用到
6.2查询所有字段
- 语法
- select 字段名 from 表名
CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`loginpwd` VARCHAR(20) DEFAULT NULL,
`studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
`gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
`phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
`address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
`borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
`email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
`identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
UNIQUE KEY `identitycard`(`identitycard`),
KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`classhour` INT(4) DEFAULT NULL COMMENT '学时',
`gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;
-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`subjectno` INT(4) NOT NULL COMMENT '课程编号',
`examdate` DATETIME NOT NULL COMMENT '考试日期',
`studentresult` INT (4) NOT NULL COMMENT '考试成绩',
KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);
-- 查询全部的学生
SELECT * FROM `student`
-- 查询指定字段
SELECT `studentno`,`studentname` FROM `student`
-- 别名，给结果起一个名字 AS 可以给字段起别名也可以给表起别名
SELECT `studentno` AS 学号,`studentname` AS 姓名 FROM `student` AS sss
-- 函数 concat(a,b)
SELECT CONCAT('姓名：',studentname) AS 新名字 FROM `student`
- 有时候字段名、表名不能见名知意，可以用as起别名
6.3去重复
- distinct
-- 查询全部考试成绩
SELECT * FROM result -- 查询全部考试成绩
-- 查询有哪些同学参加了考试
SELECT `studentno` FROM `result`
-- 发现重复数据，去重
SELECT DISTINCT `studentno` FROM `result`
SELECT VERSION() -- 查询系统版本
- 数据库中的表达式，文本值、列、null、函数、计算表达式、系统变量……
6.4where条件子句
- 检索数据中符合条件的值
- 逻辑运算符，尽量使用英文字母
- and、&&
- or、||
- not、!
-- 查询考试成绩在95-100之间的
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`<=100 AND `studentresult`>=95
-- 模糊查询，between and
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` BETWEEN 95 AND 100
-- 查询学号不是1000的
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentno`!=100
- 模糊查询，比较运算符
- is null。A is null，A为null结果为真
- is not null。A is not null，A不为null结果为真
- between a and b。结果在a-b之间为真
- like。A like B，sql匹配，如果A匹配B则结果为真。
- in。A in(A1,A2,A3……)，假设A在里面就为真。
-- 模糊查询
-- %-->代表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 '%强%'
-- 查询学号为1001、1002、1003的人
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN(1001,1002,1003)
6.5联表查询
- join，对比
-- 联表查询，join
-- 查询参加考试同学的，学号、姓名、科目编号、分数
/*思路
1. 分析需求，分析需要查询的字段来自哪个表
2. 确定使用那种连接方式？7种
确定交叉点（这两个表中的哪些数据相同）
判断条件：学生表中的`studentno`=成绩表中的`studentno`
*/
-- 交集
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno` -- on也可以换成where只能在inner join中使用，on先筛选后关联、where先关联后筛选
-- left join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT 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`
-- 查询缺考同学
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` AS sub
ON r.`subjectno`=sub.`subjectno`
-- 我需要查询哪些数据，select...
-- 从哪几个表中查，from 表 xxx jion 连接的表 on 交叉条件
-- 加入有多张表连接查询，先查询两张，慢慢来
-- from a RIGHT JOIN b 以b为基准
-- from a LEFT JOIN b 以a为基础
- inner join，如果表中有一个匹配就返回行
- left join，即使左表中没有匹配的值，也会从右表中返回所有的值
- right join，即使右表中没有匹配的值，也会从左表中返回所有的值
- 自联接
自己的表和自己的表连接，一张表拆成两张一样的表即可
感觉类似多级结构
CREATE TABLE `school`.`category`( `categoryid` INT(3) NOT NULL COMMENT 'id', `pid` INT(3) NOT NULL COMMENT '父id 没有父则为1', `categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字', PRIMARY KEY (`categoryid`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
insert into `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) values ('3', '1', '软件开发');
insert into `school`.`category` (`categoryid`, `PId`, `categoryname`) values ('5', '1', '美术设计');
insert iNTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VAlUES ('4', '3', '数据库');
insert into `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) values ('8', '2', '办公信息');
insert into `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) values ('6', '3', 'web开发');
inserT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) valueS ('7', '5', 'ps技术');
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`
6.6分页和排序
- limit，分页，缓解数据库压力
- order by，排序
-- 分页排序
-- 排序，升序 asc、降序 desc
-- order by 通过那个字段排序，怎么排
-- limit 当前页 页面大小
-- LIMIT 起始页,之后还有几页
-- LIMIT 0,2 1-2
-- LIMIT 1,2 2-3
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
ORDER BY studentresult ASC
LIMIT 0,2
6.7分组和过滤
-- 查询不同课程的平均分、最高分、最低分
SELECT `subjectname`,AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.`subjectno` -- 通过什么分组
HAVING AVG(`studentresult`)>80 -- 分组之后过滤，必须使用having
6.8子查询
where(这个值是计算出来的)，本质是在where语句中添加select语句
where(select *)
-- 查询 数据库结构-1 的所有考试结果（学号、科目编号、成绩），降序排列
-- 方式一
SELECT s.`studentno`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='数据库结构-1'
ORDER BY `studentresult` DESC
-- 方式二 子查询（有里及外）
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `subjectno`=(
SELECT `subjectno` FROM `subject`
WHERE `subjectname` = '数据库结构-1'
)
ORDER BY `studentresult` DESC
7.MySQL函数
7.1mysql常用函数
- 去网站查询，不常用
- https://dev.mysql.com/doc/refman/5.6/en/functions.html
-- MySQL常用函数
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(3.9) -- 向上取整
SELECT FLOOR(3.9) -- 向下取整
SELECT RAND() -- 随机数
SELECT SIGN(-10) -- 判断正负，正数返回1，负数返回-1
-- 字符串函数
SELECT CHAR_LENGTH('即使在校的饭') -- 判断字符串长度
SELECT CONCAT('我','大','一') -- 拼接字符串
SELECT INSERT('我是你',2,1,'不是') -- 查询，替换
SELECT LOWER('juihduhdjHHHHHH') -- 转小写
SELECT UPPER('djijiojjj') -- 转大写
-- 时间函数（记住）
SELECT CURRENT_DATE() -- 获取当前时间，2021-04-06
SELECT NOW() -- 现在的时间，2021-04-06 19:42:41
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() -- 版本
7.2mysql聚合函数（常用）
- count()，计数
- sum()，求和
- avg()，平均数
- max()，最大值
- min()，最小值
-- 聚合函数
-- 以下三个都能统计表中数据
-- count(指定列)，会忽略null
-- COUNT(*)、COUNT(1)，不会忽略null值
SELECT COUNT(`studentname`) FROM `student` -- count(指定列)
SELECT COUNT(*) FROM `student`
SELECT COUNT(1) FROM `student`
SELECT SUM(`studentresult`) AS '总和' FROM `result`
SELECT AVG(`studentresult`) AS '平均分' FROM `result`
SELECT MAX(`studentresult`) AS '最大值' FROM `result`
SELECT MIN(`studentresult`) AS '最小值' FROM `result`
7.3数据库的md5（扩展）
- md5不可逆，具体的值的md5一样
- md5破解网站，破解方法是字典对照
-- md5加密
CREATE TABLE `test2`(
`id` INT(10) NOT NULL,
`name` VARCHAR(30) NOT NULL,
`pwd` VARCHAR(100) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 明文密码
INSERT INTO `test2` VALUES(1,'zs','123'),(2,'ls','123'),(3,'wu','123')
-- md5加密所有密码
UPDATE `test2` SET pwd=MD5(pwd)
-- 插入时加密
INSERT INTO `test2` VALUES(4,'rr',MD5('123'))
-- 如何校验，将用户传递进来的密码，进行md5加密，然后再进行比对
SELECT * FROM `test2` WHERE `name`='rr' AND pwd=MD5('123')
8.事务
要么都成功，要么都失败
将一组sql放在一个批次中去执行
- 事务原则(ACID)原则，原子性、一致性、隔离性、持久性
- 原子性，要么都成功要么都失败
- 一致性，事务前后的数据完整性要保持一致
- 持久性，事务未提交是可回滚，事务已则持久化存储
- 隔离性，多用户操作时，开启一个单独的事务，不能被其他事务干扰
- 脏读，读取了前一事务未提交的数据
- 不可重复读，前后多次读取，数据内容不一致
- 幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。
-- 事务
-- mysql是默认开启事务自动提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启
-- 手动处理事务
-- 事务开启
START TRANSACTION -- 标记事务开启
COMMIT -- 提交，持久化
ROLLBACK -- 如果失败就执行回滚，回到原来的样子
-- 事务结束
9.索引
- 索引是一种数据结构，可以快速访问数据库表提取信息
- 在小数据用处不大，大数据时效果明显
9.1索引的分类
- 主键索引(primary key)
- 唯一标识，只能有一个不可重复
- 唯一索引(unique key)
- 避免重复的列出现，同一个表可以有多个唯一索引
- 常规索引(key、index)
- 默认的，用key、index来设置
- 全文索引(fulltext)
- 快速定位数据
-- 索引的使用
-- 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('刘');
9.2索引的测试
- 在小数据用处不大，大数据时效果明显
-- 生成百万数据
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '',
`eamil` VARCHAR(50) NOT NULL,
`phone` VARCHAR(20) DEFAULT '',
`gender` TINYINT(4) UNSIGNED DEFAULT '0',
`password` VARCHAR(100) NOT NULL DEFAULT '',
`age` TINYINT(4) DEFAULT NULL,
`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万数据.
SET GLOBAL log_bin_trust_function_creators=1; -- 开启创建函数功能
/*
第一个语句 delimiter 将 mysql 解释器命令行的结束符由”;” 改成了”$$”，
让存储过程内的命令遇到”;” 不执行
*/
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`,`eamil`,`phone`,`gender`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
SET i=i+1;
END WHILE;
RETURN i;
END;$$
SELECT mock_data()$$ -- 执行此函数 生成一百万条数据
9.3索引的原则
- 索引不是越多越好
- 不要对经常变化的数据加索引
- 小数据量的表不用加索引
- 索引应加在长时间查询的字段
- 索引的数据结构
- hash类型的索引
- Btree（innodb默认数据结构）
10.权限管理和备份
10.1用户管理
-
sqlyog
-
sql命令
本质，对mysql.user表进行增删改查
-- 创建用户
CREATE USER yutang IDENTIFIED BY '123456'
-- 修改指定用户密码
SET PASSWORD FOR yutang = PASSWORD('111111')
-- 重命名
RENAME USER yutang TO yutang2
-- 用户权限 ALL PRIVILEGES 全部权限库和表
GRANT ALL PRIVILEGES ON *.* TO yutang
-- 查看权限
SHOW GRANTS FOR yutang
-- 撤销权限
REMOVE ALL PRIVILEGES ON *.* FROM yutang
-- 删除用户
DROP USER yutang
10.2数据库备份
- 保证数据不丢失
- 数据转移
MySQL数据库备份的方式
- 直接拷贝物理文件，data
- sqlyog里面的导出方式
- 使用命令行，mysqldump导出，source导入
11.规范数据库设计
11.1为什么要设计数据库
- 当数据库比较复杂时就需要设计
糟糕的数据库设计
- 数据冗余，浪费空间
- 数据插入和删除比较麻烦、异常（警惕物理外键）
- 程序性能差
优秀的数据库设计
- 节省用户空间
- 保证数据库的完整性
软件开发中数据库的设计
- 分析需要，分析业务和需要处理数据库的需求
- 概要设计，设计关系E-R图
设计数据库步骤（个人博客）
- 收集信息，分析需求
- 用户表（用户登录、注销，用户分类，写博客，创建分类）
- 文章分类，谁创建的
- 文章表（文章信息）
- 友链表（友链信息）
- 自定义表（系统信息，某个关键字，一些主字段）key：value
- 标识实体（把需求落实）、
- 标识实体之间的关系
11.2三大范式
数据规范化的原因，避免信息重复，更新异常，插入异常，删除异常（丢失有效信息）
- 第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。
- 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
- 第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
12.JDBC(重点)
12.1数据库驱动
我们的程序会用数据库驱动与数据库交互
12.2JDBC
sun公司为了简化对数据库的统一操作，提供了一个操作数据库的规范（JDBC）。
开发人员只需了解JDBC接口就可以。
- java.sql
- javax.sql
- mysql-connector-java-5.1.48.zip
12.3第一个JDBC程序
-- 创建数据库
CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `jdbcStudy`;
CREATE TABLE `users`(
`id` INT PRIMARY KEY,
`NAME` VARCHAR(40),
`PASSWORD` VARCHAR(40),
`email` VARCHAR(60),
birthday DATE
);
INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES(1,'zhangsan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')
- 创建项目
- 导入驱动jar包
- 编写测试代码
package com.yutang.lesson01;
import java.sql.*;
// 第一个jdbc
public class JdbcFristDemo01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1.加载驱动
Class.forName("com.mysql.jdbc.Driver"); //固定写法
// 2.用户信息和url
// jdbc:mysql://地址:端口号/数据库名?参数一&参数二&参数三
// mysql端口号：3306
// oralce 1521
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
String username = "root";
String password = "123456";
// 3.连接成功，数据库对象 connection代表数据库对象
// connection，可以设置数据库自动提交、设置提交、设置回滚
// connection.commit();
// connection.setAutoCommit();
// connection.rollback();
Connection connection = DriverManager.getConnection(url, username, password);
// 4.执行sql的对象 statement执行sql的对象
// prepareStatement与statement的区别
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();
}
}
connection对象
// connection，可以设置数据库自动提交、设置提交、设置回滚
// connection.commit();
// connection.setAutoCommit();
// connection.rollback();
prepareStatement与statement的区别
statement.executeQuery(); //执行查询，返回resultSet结果集
statement.execute(); //执行sql
statement.executeUpdate(); //执行更新、插入、删除的sql，返回受影响的行数
ResultSet查询的结果集
resultSet.absolute();//移动到指定行
- 可以把加载驱动和释放，放在一个类里面。避免重复操作。
12.4SQL注入
prepareStatement与statement的区别
- statement存在sql注入问题
- prepareStatement，可以防止sql注入
package com.yutang.lesson01;
import java.util.Date;
import java.sql.*;
public class perpstatement {
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=false";
String username = "root";
String password = "123456";
// 3.连接成功，数据库对象 connection代表数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
// 4.执行sql的对象 PreparedStatement执行sql的对象
// 使用？进行占位
String sql = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)VALUES(?,?,?,?,?)";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1,4);
statement.setString(2,"juhjj");
statement.setString(3,"124345");
statement.setString(4,"2222222@qq.com");
// 传入的date是数据库的data类型
// new Date().getTime()，是java。util包下的date，返回值是毫秒数
statement.setDate(5,new java.sql.Date(new Date().getTime()));
// 现在才开始执行，影响行数不为0，则执行成功
int i = statement.executeUpdate();
if(i>0){
System.out.println("chenggong");
}
statement.close();
connection.close();
}
}
12.5IDEA连接数据库
- 右侧dababase里面，选择mysql
- 如果让你下载驱动，可以添加自己的驱动
- 在左侧Driver里面选mysql
- 进入之后添加驱动地址，选择正确的class名
- 登录即可
12.6事务
- 原子性，要成功都成功
- 持久性，不提交可回退，提交后就持久保存
- 一致性，总数不变
- 隔离性，多用户操作不会相互影响
隔离性产生的问题
- 脏读，读取到别人未提交的代码
- 不可重复读，多次读取到的结果不一致
- 幻读，在一个事务内读取到别人插入的数据，使前后结果不一致
// 取消自动提交，开启事务
connection.setAutoCommit(false);
// 提交事务
connection.commit();
// 开启自动提交，关闭事务
connection.setAutoCommit(true);
// 如果失败会自己回滚，也可以在catch中自己显示回滚代码
12.7数据连接池
数据库连接–执行完毕–释放。
连接–释放，十分浪费系统资源
池化技术：准备一些预先准备好的，过来就连接预先准备好的。
- 编写连接池要实现一个接口，datasouce
- 开源的数据源实现
- C3P0
- DBCP
- Druid：阿里巴巴
- 使用这些数据库连接池之后，就不用写数据库连接代码了、
- 无论使用什么数据源，都是实现DataSouce接口
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
