mysql

MySQL

  • java开放分三部分
  • 前端 页面，展示数据
  • 后台 链接中枢，操作前端，操作数据库
  • 数据库 存数据
  • SQL是一种语言，而MySQL则是一个具体的关系型数据库管理系统，它实现并扩展了SQL语言

数据库（Database）

数据库是一个有组织的、可管理的数据集合，用于存储和检索数据。它可以是关系型数据库（如MySQL、SQL Server）或非关系型数据库（如MongoDB、Redis）。

SQL（Structured Query Language）

SQL是一种标准化的编程语言，用于管理和操作关系数据库。它用于编写查询、更新、删除和插入数据的命令。

MySQL

MySQL是一个开源的关系型数据库管理系统（RDBMS），使用SQL作为查询语言。它用于存储、检索和管理数据，广泛应用于Web应用程序和各种数据驱动项目中。

SQLyog

SQLyog是一个用于管理MySQL数据库的图形化用户界面（GUI）工具。它提供了一个友好的界面，方便用户执行SQL查询、管理数据库结构和进行数据操作。

关系图示

让我们用一张图来展示它们之间的关系：

数据库（Database）
└── 关系型数据库（RDB）
     └── MySQL（一个具体的RDBMS）
           └── SQLyog（管理MySQL的GUI工具）
└── 非关系型数据库（NoSQL）

在这张图中：

  • 数据库是最广泛的概念，包括关系型和非关系型数据库。
  • 关系型数据库是数据库的一种类型，使用表格结构来存储数据。
  • MySQL是具体的关系型数据库管理系统，使用SQL语言进行数据操作。
  • SQLyog是一个帮助管理MySQL数据库的工具，通过图形界面简化数据库管理操作。

个人总结

  • 数据库DB分为关系型和非关系型数据库
  • 数据库下面是数据库管理系统
  • mysql是具体的一个关系型数据库管理系统
  • sqlyog和navicat都是帮助管理mysql的GUI工具
数据库（Database）
  ├── 关系型数据库（Relational Database）
  │     └── 关系型数据库管理系统（RDBMS）
  │           ├── MySQL（一个具体的RDBMS）
  │           │     └── SQLyog（管理MySQL的GUI工具）
  │           ├── PostgreSQL
  │           ├── Oracle
  │           └── SQL Server
  └── 非关系型数据库（NoSQL Database）
        ├── 文档数据库（Document Database）
        │     └── MongoDB
        ├── 键值数据库（Key-Value Store）
        │     └── Redis
        ├── 列族数据库（Column-Family Store）
        │     └── Cassandra
        └── 图数据库（Graph Database）
              └── Neo4j

数据库

  • DB DataBase
  • 概念，数据仓库，是软件，安装在操作系统上
  • 功能就是存数据（500万以下）

安装mysql和sqlyog

  • 搜就行

sql基本

  • 连接数据库

    1. 用sqlyog连
    2. 用cmd，进入mysql连

    本质都是执行sql语句

  • 一些基本 cmd 的 sql 语句操作，基本都要带;分号

    --注释
/*
多行
*/
--连接数据库
C:\Users\zhm>mysql -uroot -p123456

--刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

--展示所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

--切换到school数据库
mysql> use school
Database changed

--展示所有表
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

--展示某一个表详情
mysql> describe student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | NO   | PRI | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | int(3)       | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

--创建数据库
mysql> create database zhms;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
| zhms               |
+--------------------+
6 rows in set (0.00 sec)

--退出数据库
mysql> exit;
Bye

数据库-->数据库表-->数据库表内数据

sql语句分号结尾

操作数据库

  • 创建数据库

    CREATE DATABASE [IF NOT EXISTS] zhms;
-- []代表可选

  • 删除数据库

    DROP DATABASE [IF EXISTS] zhms;

  • 使用数据库

    USE zhms;
-- ``tap上面的飘号，有些关键字和属性一样，就得用``括住，类似user
USE `school`;

  • 查看数据库

    SHOW DATABASES;
SHOW TABLES;
DESCRIBE student;

操作数据库表

数据库表的列数据类型

  • 数字

    • tinyint 最小 1字节
    • smallint 较小 2字节
    • mediumint 中等 3字节
    • int 常用 4字节
    • float 浮点数 4字节
    • double 浮点数 8字节
    • bigdecimal 字符串形式浮点数 金融计算常用

  • 字符串

    char 字符串 固定大小 0-255字节

    varchar 可变长字符串 常用 0-65535字节

    tinytext 短文 0-2^8-1(255)字节

    text 大文本 常用 0-2^16-1(65535)字节

  • 时间日期

    data YYYY-MM-DD，日期格式，年月日

    time HH:mm:ss，时间格式，时分秒

    datatime YYYY-MM-DD HH:mm:ss 最常用的时间格式

    timestamp 时间戳 从1970-1-1 00:00:00到现在的ms数，所有地区一样，较为常用

    year 年份

  • null

    没有值，未知

    一般不参与运算 运算结果必为null

数据库的字段属性（重要）

  • 长度

    varchar（255）可以存255个字符，255位

    int（100）100位数字

  • 默认

    不设置值插入，就会自动设置为默认值

  • 非空

    不勾可以为null，无默认就是null

    勾了不能为null

  • Unsigned 无符号

    勾了就不能设置为负数

  • 自增

    一般是主键使用，只能用于整数类型，设置的主键话默认自增1

    勾选自增，默认下一个相对上一个自增1，可以在高级中改起始值和步

  • zerofill 0填充

    int（3） 设置5 填充为005

sql语句创建数据库表

/*
注意点
1.表名和表里字段名用飘号``括起来
2.只有字段名和类型是必须的
3.主键最好最后设置，也要``括起来
4.符号都是英文，()内最后一句字段不能用,结尾，其他的要用,结尾，下面的PRIMARY KEY(`id`)是最后一句
*/
CREATE TABLE IF NOT EXISTS `student`(
  `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `pwd` VARCHAR(10) NOT NULL COMMENT '学生密码',
  `name` VARCHAR(10) NOT NULL DEFAULT 'noname' COMMENT '学生姓名',
  `birthday` DATETIME DEFAULT NULL COMMENT '学生生日',
  PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

  • 如果不会sql，可以使用先用sqlyog创建，再show出来sql语句

    -- desc=describe 
DESC `student`
show CREATE DATABASE `school`
SHOW CREATE TABLE `student`

数据库引擎和字符编码（补充）

  • 常用 innodb和myisam

  • myisam优势 存储读取快，占用空间小

  • innodb优势 安全，可以多人操作一张表

  • 每个数据库都是一个文件夹，表都是对应文件夹下面的文件，数据库存本质还是文件存储

  • 字符编码要设置为utf8，只有utf8才能识别中文

sql修改，删除数据库表

-- 改表名字
ALTER TABLE `teacher13` RENAME AS `teacher`
-- 删除表
DROP TABLE IF EXISTS `teacher` 

-- 以下为修改删除表字段
-- 增加字段
ALTER TABLE `teacher` ADD `age` INT(3) NOT NULL DEFAULT 10 COMMENT 'age'
-- 修改表字段的属性
ALTER TABLE `teacher` MODIFY `age` VARCHAR(8) DEFAULT '90'
-- 修改表字段的名字
ALTER TABLE `teacher` CHANGE `age` `newage` INT(6)
-- 删除表字段
ALTER TABLE `teacher` DROP `newage`

注意点

  • 所有符号英文符号
  • 表名字和表字段名用``括起来

操作数据库表内数据

补充外键相关

  • 首先声明，这是数据库物理外键，物理外键会导致数据库不能随意删除，结构很混乱，一般外键用后台代码实现

  • 数据库物理外键有两种方法实现

    1. 定义表的时候定义

      先定义键，再定义外键

      CREATE TABLE IF NOT EXISTS `grade`(
  `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '班级id',
  `gradename` VARCHAR(10) COMMENT '班级名',
  PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

CREATE TABLE IF NOT EXISTS `student`(
  `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `pwd` VARCHAR(10) NOT NULL COMMENT '学生密码',
  `name` VARCHAR(10) NOT NULL DEFAULT 'noname' COMMENT '学生姓名',
  `birthday` DATETIME DEFAULT NULL COMMENT '学生生日',
  `gradeid` INT(10) NOT NULL COMMENT '学生在的班级id',
  PRIMARY KEY(`id`),
  KEY `FK_gradeid` (`gradeid`),
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

    2. 定义完表后，再改变表，加一个外键

      CREATE TABLE IF NOT EXISTS `grade`(
  `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '班级id',
  `gradename` VARCHAR(10) COMMENT '班级名',
  PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

CREATE TABLE IF NOT EXISTS `student`(
  `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `pwd` VARCHAR(10) NOT NULL COMMENT '学生密码',
  `name` VARCHAR(10) NOT NULL DEFAULT 'noname' COMMENT '学生姓名',
  `birthday` DATETIME DEFAULT NULL COMMENT '学生生日',
  `gradeid` INT(10) NOT NULL COMMENT '学生在的班级id',
  PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)

DML语言（重要）

  • 增加 insert into
  • 修改 update
  • 删除 delete from

增加

  • 语法

    insert into `表名` (`字段名`),(`字段名`)... 
values ('值','值'...),('值','值'...)...

  • 注意点

    1. 字段名可以多个，多个对应值也要多个，类型要对应
    2. 字段名省略的话，就会把值顺序赋值给所有字段，没有值就是null
    3. 一次可以插入多条数据，方法就是(),()...，一个()内代表一条数据
    4. 符号用英文符号
  • INSERT INTO `student`(`pwd`,`gradeid`)
VALUES ('13579','1'),('246810','2')

INSERT INTO `student`
VALUES ('3','19735','xiaozhang','2019-1-1','3')

INSERT INTO `student`(`name`)
VALUES ('xiaohong'),('xiaolan'),('xiaohuang')

INSERT INTO `student`(`pwd`,`name`)
VALUES ('05646','xiaobai')

修改

  • 语法

    update `表名字` set `字段名`='值',`字段名`='值'... where 条件

  • 注意事项

    1. 条件不写就是修改所有
    2. 一次可以set修改多个字段
    3. 条件常用的有=，<>（！=），>，<，>=，<=，between ... and ...（包含）
    4. 条件可以多个，AND（与，两真才执行）连接，OR（或，两假才不执行）连接
  • UPDATE `student` SET `name`='wsnl' WHERE `id`=1

UPDATE `student` SET `birthday`=CURRENT_TIME

UPDATE `student` SET `pwd`='369',`name`='jyxgss' 
WHERE `gradeid`=0 AND `name`='xiaohuang'

UPDATE `student` SET `pwd`='13579268'
WHERE `id`=8 OR `id`=7

删除

  • 语法

    delete from `表名` where 条件

  • 注意事项

    1. 条件不写就是删除表全部数据
    2. delete from 表名和truncate表名有区别
    3. 清空用truncate更好，自增会从1开始，delete的自增不会回1
  • CREATE TABLE `test`(
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `col` INT(10) NOT NULL,
  PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `test`(`col`)
VALUES (1),(2),(3)

DELETE FROM `test` WHERE `id`=2

TRUNCATE TABLE `test`

DQL查询数据（最重要）

简单查询

  • 语法

    select `查询字段名`,`查询字段名`... from `表名`

  • 注意事项

    1. select * 代表查询所有字段
    2. as别名，字段名 as 自定义字段名，表名 as 自定义表名，结果查询表头为自定义字段（表名）
    3. CONCAT函数，可以将所有查询结果修改为自定义拼接字符串
  • SELECT * FROM `student`

SELECT * FROM `subject`

SELECT `studentname`,`gradeid` FROM `student`

SELECT `studentname` AS `学生姓名`,`gradeid` AS `年级id` FROM `student` AS `学生`

SELECT CONCAT('姓名','=',`studentname`) AS `学生姓名` FROM `student`

补充

  • distinct去重

    查询结果如果有重复，只保留一条

SELECT * FROM `result`

SELECT DISTINCT `studentno` AS `学生编号` FROM `result`

  • select 表达式 from

    表达式可以为列，变量，函数，数学表达式...

SELECT VERSION()

SELECT `studentno`,`studentresult`+1 AS `提分后` FROM `result`

where条件相关

  • 运算符

    返回布尔

    运算符 语法 描述
    and && a and b 与
    or || a or b 或
    not= != not a 非

  • 尽量用前面的英文字母，比较好观察和理解

    not需要注意，not语法是 where [not] studentresult=98

    SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`>80 AND `studentresult`<100

SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` BETWEEN 85 AND 98

SELECT `studentno` AS `学生编号`,`studentresult` AS `学生成绩` FROM `result`
WHERE NOT `studentresult`=98

模糊查询操作符

  • like 模糊

    a like '%b%'，返回a中有b的结果，%代表0-无限个字符

    a like 'b_'，返回a中两个字，b开头的结果，下划线代表一个字符

  • in 具体

    a in('b','c')，返回有b和c的结果

  • is null

  • is not null

    SELECT `studentno` AS `学生编号`,`studentname` AS `学生名字` FROM `student`
WHERE `studentname` LIKE '%赵%'

SELECT `studentno` AS `学生编号`,`studentname` AS `学生名字` FROM `student`
WHERE `studentname` LIKE '赵_'

SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN('1000','1001')

SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NULL

SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL

连表查询

  • join操作符

  • 以学生表s和成绩表r举例

    1. s left join r，查询结果为所有学生，包括不考试的
    2. s inner join r，查询结果为参考学生且有学生的成绩，两表都存在的
    3. s right join r，查询结果为所有成绩，包括没有学生信息的成绩

  • 连表查询步骤

    1. 确定查询数据，select 数据

    2. 确定要的数据对应的表

    3. 确定join方式，一般是inner join

    4. 语法

      SELECT 数据
      FROM 表1名 AS 代称1
      LEFT JOIN 表2名 AS 代称2
      ON 连接条件
      where 过滤条件

  • 3表查询要慢慢来，先两表出结果，再结果和另一个表

    -- 查询参加考试的学生，学生姓名，学生编号，考试成绩，考试科目编号
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
INNER 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

-- 查询未考的，学生姓名，学生编号，考试成绩，考试科目编号
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.studentno=r.studentno
WHERE `studentresult` IS NULL

-- 3表，查询参加考试的学生，学生姓名，学生编号，考试成绩，考试科目名
SELECT s.`studentno`,`studentname`,`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

    -- 创建一个表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

-- 自联结表，把一张表分为两张表看
SELECT c1.`categoryName` AS `父分类名`,c2.`categoryName` AS `子分类名`
FROM `category` c1,`category` c2
WHERE c1.`categoryid`=c2.`pid`

排序和分页

-- 排序 orderby desc降序 asc升
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
INNER JOIN `student` s
ON r.`studentno`=s.`studentno`
WHERE `subjectname` LIKE '%高等%'
ORDER BY `studentresult` DESC

-- limit a（起始值下标，0开始） b（每页条数）
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
INNER JOIN `student` s
ON r.`studentno`=s.`studentno`
WHERE `subjectname` LIKE '%高等%'
ORDER BY `studentresult` DESC
LIMIT 0,2
-- LIMIT 2,2

常用函数

  • 时间
  • 算数
  • 字符串

聚合函数和分组过滤

  • 聚合

    count，avf，max，min，sum

  • 分组过滤

    group by，分组后过滤用having，在group by后面

    一般跟avg结合，类似查询不同科目平均

SELECT COUNT(*) 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
-- where
GROUP BY(r.subjectno)
HAVING 平均分>80
-- order by() desc/asc
-- limit a,b

md5加密

  • md()
  • 加密不可逆，但相同值加密结果一样
  • 校验可以先存入用户插入的md加密密码，再md(输入)=存入md加密值，比对

详细语法总结

-- 顺序很重要，不能乱
SELECT
    [ALL | DISTINCT | DISTINCTROW]
    column1 [AS alias1],
    column2 [AS alias2],
    ...
    [FROM table1 [AS table_alias1]
    [JOIN table2 ON join_condition] -- 连表查询
    [WHERE condition] -- 过滤 
    [GROUP BY group_by_expression] -- 分组 
    [HAVING having_condition] -- 分组后过滤
    [ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...] -- 排序
    [LIMIT row_count [OFFSET offset]] -- 显示信息条数
    [FOR UPDATE | LOCK IN SHARE MODE];

补充

在 SQL 的语法描述中，中括号 [ ] 和大括号 { } 是用于表示语法规则的符号，它们的含义如下：

1. 中括号 [ ]

  • 含义：表示括号内的内容是可选的。

  • 说明：你可以根据需要选择是否使用括号内的部分。

  • 示例：

    SELECT column1 [, column2, ...]
FROM table_name
[WHERE condition];
    • [, column2, ...] 表示可以选择是否查询多个列。
    • [WHERE condition] 表示 WHERE 子句是可选的。

2. 大括号 { }

  • 含义：表示括号内的内容是必选的，并且需要从多个选项中选择一个。

  • 说明：大括号内的选项用竖线 | 分隔，表示“或”的关系。

  • 示例：

    SELECT column1
FROM table_name
ORDER BY {column1 | column2} [ASC | DESC];
    • {column1 | column2} 表示必须选择 column1 或 column2 中的一个。
    • [ASC | DESC] 表示可以选择 ASC 或 DESC，但也可以不写（因为用中括号括起来了）。

3. 竖线 |

  • 含义：表示“或”的关系，用于分隔多个可选项。

  • 示例：

    SELECT column1
FROM table_name
WHERE column2 = {value1 | value2 | value3};
    • {value1 | value2 | value3} 表示必须选择 value1、value2 或 value3 中的一个。

综合示例

SELECT column1 [, column2]
FROM table_name
[WHERE condition]
ORDER BY {column1 | column2} [ASC | DESC];
  • column1 是必选的，column2 是可选的。
  • WHERE 子句是可选的。
  • ORDER BY 必须选择 column1 或 column2 中的一个。
  • ASC 或 DESC 是可选的。

实际 SQL 示例

SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 10
ORDER BY last_name ASC;
  • employee_id, first_name, last_name 是必选的列。
  • WHERE department_id = 10 是可选的过滤条件。
  • ORDER BY last_name ASC 中，last_name 是必选的排序字段，ASC 是可选的排序方式。

总结

  • [ ]：可选内容。
  • { }：必选内容，且需要从多个选项中选择一个。
  • |：表示“或”的关系。

事务ACID原则

  • 原子性 A

    指同一事务语句，要么都发生，要么都不发生

  • 一致性 C

    前后结果总数据一致，转钱前和为1000，之后不变

  • 隔离性 I

    多个用户同时访问数据库，数据库为每个用户开事务，不能被其他事务干扰

  • 持久性 D

    事务一旦提交，结果不可逆

  • 可能出现的问题，脏读，重复读，幻读

事务代码

CREATE DATABASE IF NOT EXISTS `account` CHARACTER SET utf8 COLLATE utf8_general_ci

USE account

CREATE TABLE `user`(
  `id` INT(3) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(10) NOT NULL,
  `money` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `user`(`name`,`money`)
VALUES ('A',5000),('B',10000)

SET autocommit=0 -- 关自动提交
START TRANSACTION -- 开启事务
UPDATE `user` SET money=money-500 WHERE `name`='A'
UPDATE `user` SET money=money+500 WHERE `name`='B'
COMMIT -- 提交
ROLLBACK -- 回滚数据
SET autocommit=1 -- 开启自动提交

索引

  • 主键索引 primary key

    只能有一个属性有，存数据时不能重复

  • 唯一索引 unique key

    可以有多个属性有，存数据不能重复

  • 普通索引 key/index

    默认key

  • 创建索引方式

    1. 创表时加上

        -- 用法
  PRIMARY KEY(id),
  unique key `id`(`id`) -- 前面索引名，后面索引的属性列名
  key `id`(`id`)

      CREATE TABLE `user`(
  `id` INT(3) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(10) NOT NULL,
  `money` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY(id),
  -- unique key `id`(`id`) -- 前面索引名，后面索引的属性列名
)ENGINE=INNODB DEFAULT CHARSET=utf8

    2. 修改表add的时候加上

      ALTER TABLE `user` ADD 
PRIMARY KEY(`id`)
-- unique key `id`(`id`) -- 前面索引名，后面索引的属性列名
-- key `id`(`id`)

    3. 创建索引

      create index 索引名 on 表名（字段名）

  • 索引主要用于数据量大的查询

用户权限

-- 操作用户，本质是操作mysql.user表的数据
DROP USER IF EXISTS kuangshen2

CREATE USER IF NOT EXISTS kuangshen IDENTIFIED BY '123456'

-- 设置当前连接用户密码
SET PASSWORD = PASSWORD('123456')
-- 修改指定
SET PASSWORD FOR kuangshen = PASSWORD('123456')
-- 重命名
RENAME USER kuangshen TO kuangshen2

-- 授权 all privileges代表所有权限，除了grant权限
GRANT ALL PRIVILEGES ON *.* TO kuangshen2 

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2

数据库备份

  1. 物理层面，data文件夹

    image-20250207113414222

  2. sqlyog可视化操作

    恢复直接拖sql文件，执行查询

    image-20250207113550039

  3. mysqldump命令行

    # mysqldump -h主机地址 -u用户名 -p密码 备份表
C:\Users\zhm>mysqldump -hlocalhost -uroot -p123456 school student >F:/1.sql

# 备份数据库
C:\Users\zhm>mysqldump -hlocalhost -uroot -p123456 --databases school > F:2.sql

# 恢复，先登录
C:\Users\zhm>mysql -uroot -p123456
mysql> source F:/2.sql

数据库设计

  • 数据库设计步骤：（个人博客）
  • 收集信息，分析需求
    • 用户表
      • 用户id
      • 用户名
      • 用户信息
    • 分类表
      • 分类id
      • 分类名
      • 创建人id
    • 文章表
      • 文章id
      • 文章名
      • 文章内容
      • 创建人id
      • 分类id
    • 评论表
      • 评论id
      • 评论内容
      • 评论人id
      • 评论文章id
    • 友情链表
      • 友情链id
      • 友情链标题
      • 友情链地址
  • 标识实体，创建数据库，把需求落实到字段
  • 标识实体间关系
    • 写博客：user----blog blog----categories
    • 创建分类：user----categories
    • 评论：user----user----blog
    • 友情链：links

三大范式

主要用于规范数据库

  • 第一范式 1NF

    原子性，保证每一列不可再分

  • 第二范式 2NF

    前提：满足第一范式

    每张表只描述一件事，类似采购表和订单表，采购表只存采购相关，订单表存订单详细

  • 第三范式 3NF

    前提：满足第一第二范式

    每一列数据与主键直接相关，不能间接相关

  • 规范性和性能不能兼得

  • 为了性能，关联表查询最多3张表

  • 考虑商业化需求和目标（成本），性能更为重要

  • 有时候为了效率故意增加冗余字段，从多表查询变为单表查询

JDBC

  • 数据库和java代码想要连接，各个数据库就要规定规范，开发人员也要学习导入每一种数据库规范

  • JDBC相对于是java和数据库规范之间的一层，简化开发人员，开发人员只需要安装JDBC驱动，JDBC总结多个数据库规范

  • 第一个JDBC连接java程序

  • 先要新建lib(library文件夹)，再复制mysql的jar包，再右键添加为库，才可以用

    image-20250208171845071 image-20250208173333506 image-20250208173337236

  • 步骤

    1. 加载JDBC驱动
    2. DriverManger.getConnection(url,user,pwd) 连接数据库，返回数据库对象
    3. connection.createStatement() 数据库对象创建执行sql对象
    4. statement.excuteQuery(sql) 执行sql语句，返回执行结果 resultset
    5. 顺序关闭连接 resultset statement connection

  • 代码

    package com.zhm.Demo01;

import java.sql.*;

public class JDBCTest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1.加载JDBC
        Class.forName("com.mysql.jdbc.Driver");

        // 2.连接数据库，返回数据库对象
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&CharacterEncoding=utf8&useSSL=true";
        String username = "root";
        String pwd = "123456";
        Connection connection = DriverManager.getConnection(url, username, pwd);

        // 3.用数据库对象创建执行sql的对象
        Statement statement = connection.createStatement();

        // 4.用执行sql的对象执行sql，获得执行结果
        String sql = "SELECT * FROM `users`";
        ResultSet resultSet = statement.executeQuery(sql);

        // 查询结果
        System.out.println(resultSet);
        while (resultSet.next()){
            System.out.println("id\t"+resultSet.getObject("id"));
            System.out.println("NAME\t"+resultSet.getObject("NAME"));
            System.out.println("PASSWORD\t"+resultSet.getObject("PASSWORD"));
            System.out.println("email\t"+resultSet.getObject("email"));
            System.out.println("birthday\t"+resultSet.getObject("birthday"));
            System.out.println("------------------------------------------------------------");
        }

        // 5.关闭连接
        resultSet.close();
        statement.close();
        connection.close();

    }
}

  • 各个对象

  • URL

    String url = "jdbc:mysql://localhost:3306/jdbcstudy? useUnicode=true&CharacterEncoding=utf8&useSSL=true";
// 语法 
// mysql---3306
// jdbc:mysql://ip地址:端口号/数据库名?参数1&参数2&参数3
// oracle---1521
// jdbc:oracle:thin:@loaclhost:1521:sid

  • statement

    resultset=statement.executeQuery(sql);// 返回查询结果
statement.execute(sql);// 返回执行结果，t或f
statement.executeUpdate(sql);// 返回受影响行数

  • resultset

    可以获得对应类型查询结果，不确定类型就直接返回object类

    一般配合next遍历

    resultSet.next();// 返回指针下一个指向的数字情况，如果有就返回true，没有就false
resultSet.afterLast();// 指针移到最后一个数据的后面
resultSet.beforeFirst();// 指针移到第一个数据的前面

  • 关闭资源

    先用后关，connection最占用资源

封装工具类和配置文件导入

配置文件

  • 在src目录新建db.properties

    image-20250211114449600

    写入常用配置

    driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username = root
password = 123456

  • 导入配置步骤

    都是通过反射获取

    // 类名.class.getClassLoader().getResourceAsStream("配置文件名（或者地址）")
// 以流形式获得配置文件
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
// 加载流到Properties类对象里
properties.load(in);

// 调用方法，通过键值对，键名获得值
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");

    获取getResourceAsStream("db.properties")时

    要看配置文件位置，如果在src下（根目录下），可以直接获取到，就可以直接写db.properties文件名

    否则要写对应地址

封装工具类

  • 一般先建一个utils文件夹，在utils文件夹里面写工具类

    image-20250211114435112

  • 工具类一般由静态变量和静态代码块和静态方法组成，使用直接用类名.方法名

    静态代码块在类加载就会执行里面的代码，所以驱动加载写在里面，驱动只用加载一次

  • 主要封装驱动加载（一次，写在静态态代码块），获得数据库对象Connect（方法），释放资源（方法）

    package com.zhm.Demo02.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    static String driver = null;
    static String url = null;
    static String username = null;
    static String password = null;
    static {
        try {
            // 类名.class.getClassLoader().getResourceAsStream("配置文件名（或者地址）")
            // 以流形式获得配置文件
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            // 加载流到Properties类对象里
            properties.load(in);

            // 调用方法，通过键值对，键名获得值
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            
            // 加载驱动，只用一次
            Class.forName(driver);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    // 连接
    public static Connection conn() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }

    // 释放资源
    public static void release(ResultSet rs,Statement st,Connection conn){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (st != null){
            try {
                st.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

    }
}

调用工具类测试

  • 通过类名.方法名调用静态方法，会自动加载工具类静态代码块

    package com.zhm.Demo02;

import com.zhm.Demo02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SelectTest {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.conn();
            st = conn.createStatement();
            String sql = "select * from `users`";
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getInt("id"));
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("PASSWORD"));
                System.out.println(rs.getString("email"));
                System.out.println(rs.getString("birthday"));
                System.out.println("--------------------------------------");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.release(rs,st,conn);
        }
    }
}

sql注入问题

  • 利用字符串拼接，让sql执行某些拼接语句

  • 不安全，所以要用preparedStatement执行sql

    ​ login("'or'1=1","'or'1=1");// sql注入

    package com.zhm.Demo02;

import com.zhm.Demo02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQL注入 {
    public static void main(String[] args) {
//        login("zhm","135792");
        login("'or'1=1","'or'1=1");// sql注入
    }

    public static void login(String username,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.conn();
            st = conn.createStatement();
            String sql = "select * from `users` where `NAME`='" + username + "' AND `PASSWORD`='" + password + "'";
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getInt("id"));
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("PASSWORD"));
                System.out.println("--------------------------------------");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.release(rs,st,conn);
        }
    }
}

PreparedStatement

  • 继承statement类

    image-20250211151521772

  • 效率更高，且可以用预加载sql实现防止sql注入问题

    预加载会自动转义字符，PreparedStatement 自动对参数进行转义，确保特殊字符不会被解释为 SQL 代码的一部分

  • 与普通statement代码差别在预加载上

    参数用？当占位符先占位，再赋值，再执行sql

    package com.zhm.Demo03;

import com.zhm.Demo02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SelectTest {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.conn();
            String sql = "select * from `users` where `NAME`=?;";
            st = conn.prepareStatement(sql);
            st.setString(1,"zhm");

            rs = st.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("email"));
                System.out.println(rs.getDate("birthday"));
                System.out.println("-----------------------------------");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.release(rs,st,conn);
        }
    }
}

    package com.zhm.Demo03;

import com.zhm.Demo02.utils.JdbcUtils;

import java.sql.*;

public class SQL注入 {
    public static void main(String[] args) {
//        login("mhz","135792");
        login("''or 1=1","''or 1=1");// sql注入
    }

    public static void login(String username,String password){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.conn();
            String sql = "select * from `users` where `NAME`=? AND `PASSWORD`=?;";
            // 1预编译
            st = conn.prepareStatement(sql);

            // 2再赋值
            st.setString(1,username);
            st.setString(2,password);

            // 3再执行sql
            rs = st.executeQuery();
            while (rs.next()){
                System.out.println(rs.getInt("id"));
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("PASSWORD"));
                System.out.println("--------------------------------------");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.release(rs,st,conn);
        }
    }
}

IDEA连接数据库

  1. 打开idea右边数据库按钮

  2. 选择加号新建，添加数据源，选mysql

    image-20250212130108253

  3. 改驱动程序为mysql5.1，输入用户密码，测试连接，成功就应用，然后确认

    image-20250212130326936

  4. 改数据库，右键属性，选择架构，再选择要使用的数据库，应用，确定

    image-20250212130428130

    image-20250212130500726

  5. 双击表可以看表数据库

    image-20250212130628551

  6. 双击表数据可修改表数据，修改完回车，记得点绿色提交箭头

    image-20250212130756076

  7. 执行sql语句控制台，右上角控制台，默认控制台

    image-20250212130856691

JDBC操作事务

  1. 关闭自动提交
  2. 写事务代码
  3. 提交
  4. 显式定义回滚
package com.zhm.Demo04;

import com.zhm.Demo02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

// 事务就是要么都成功，要么都失败，acid原则，原子性，一致性，隔离性，持久性
public class TestTransAction {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.conn();
            // 1.关闭自动提交，开启事务
            conn.setAutoCommit(false);
            String sql1 = "update `user` set `money`=`money`-500 where `name`='A';";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

//            int x = 1/0;

            String sql2 = "update `user` set `money`=`money`+500 where `name`='B';";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            // 2.提交
            conn.commit();
            System.out.println("提交成功");
        } catch (SQLException e) {
            try {
                // 3.显示回滚，不定义也会隐式回滚
                conn.rollback();
                System.out.println("失败");
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.release(rs,st,conn);
        }
    }
}

连接池

  • 连接--执行--释放，连接和释放很消耗资源
  • 池化技术
  • 节省资源，不用每次执行sql都获取connect
  • 本质都是DateSource接口实现类
posted @ 2025-04-22 22:08  学习java的白菜  阅读(20)  评论(0)    收藏  举报