mysql基础知识

最新推荐文章于 2024-11-16 12:03:57 发布

sw_eet

最新推荐文章于 2024-11-16 12:03:57 发布

阅读量241

点赞数

文章标签： mysql java sql 数据库

本文链接：https://blog.csdn.net/sw_eet/article/details/115487210

版权

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 `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中自己显示回滚代码