当前位置：首页 > news >正文

木舟0基础学习Java的第二十一天(数据库,MySQL,SQLyog)

news 2025/7/4 14:50:39

数据库

数据库：按照数据结构来组织存储数据的厂库

数据管理系统(Database Management System,DBMS)：一套操作和管理数据库的软件用于简历使用维护数据库

关系型数据库：采用关系模型作为数据组织方式逻辑结构是一张二维表由行和列组成表的每一行为一个元组每一列为一个属性

应用系统：建立在用户层和数据层之间的展现和交互部分

数据库设计的三范式

1.数据库表的每一列都是不可分割的基本数据项

2.首先满足1 要求数据库表中的每个实体必须可以被唯一的区分（主关键字）要求主体的非主属性完全依赖于主关键字

3.首先满足2 每一列数据都和主键相关不能间接相关既不能传递依赖与主关键字

保证数据完整性和一致性

例子：School(数据库)，students（表）

MySQL每句代码后一定要加;

创建代码：

建库：

//创建数据库

CREATE DATABASE School(数据库名);

//创建前判断有无该数据库没有就创建(常用)

CREATE DATABASE IF NOT EXISTS School;

//创建数据库判断有无该数据库同时指定编码格式

CREATE DATABASE IF NOT EXISTS(不写没判断) School CHARACTER SET utf8（编码格式）;

建表：

CHAR(8)//指定字符长度

NOT NULL//不为空

UNIQUE//唯一的

VARCHAR(255)//长度大于CHAR最大到255

INT//同java一样

每句话需要用 , 隔开最后一句话不需要

一定记得要在括号外加;

CREATE TABLE students(stu_num CHAR(8) NOT NULL UNIQUE,stu_name VARCHAR(20) NOT NULL,stu_gender CHAR(2) NOT NULL,stu_age INT NOT NULL,stu_tel CHAR(11) NOT NULL UNIQUE,stu_qq VARCHAR(11) UNIQUE);

//添加列(字段)

ALTER TABLE students ADD stu_wx VARCHAR(20);

插入数据：

语法：INSERT INTO 表名<列名,列名,...> VALUES<'数据','数据',...>;

INSERT INTO

//在开发中推荐使用将全部列名写出

//添加数据数据位置要与列名位置匹配

INSERT INTO students(stu_num,stu_name,stu_gender,stu_age,stu_tel)VALUES('20240101','张三',' ',23,'182XXXX8780');//' ' 不是null 是数据表没有值

//全部插入

INSERT INTO students VALUES('20240102','李四','女',24,'182XXXX8781','qq488085218');//数据必须与表中列的位置数量一一对应

删除代码：

删库：

//删除School数据库 敏感操作

DROP DATABASE School(数据库名);

删表：

//删除表 敏感操作会删除表中所有数据

DROP TABLE students(表名);

//删除前判断有无该数据表没有就删除(常用)

DROP TABLE IF EXISTS students;

//删除列

ALTER TABLE students DROP stu_ww;

删数据：

//根据名字删除数据要加上WHERE条件不然会删除全部数据

DELETE FROM students WHERE stu_name='张三';//name可以替换成gender 就是根据性别删除

TRUNCATE FROM students WHERE stu_name='张三';

//DELETE和TRUNCATE的区别是自动增长

//DELETE删除后自动增长继续

//TRUNCATE删除后自动增长从1开始并且不能用在主外键关系表上

修改代码：

改库：

//修改数据库字符集（不常用）

ALTER DATABASE School CHARACTER SET gbk（编码格式）;

改表：

//修改表名将studengs改为stus RENAME TO 重命名

ALTER TABLE students RENAME TO stus;

//修改列的列表和类型 CHANGE 改变

ALTER TABLE students CHANGE stu_wx stu_ww VARCHAR(11);

//修改列类型 MODIFY 修改

ALTER TABLE students MODIFY stu_ww CHAR(11);

改数据：

//修改数据 敏感操作 会把students表中所有数据性别改为男 一定要加WHERE条件

UPDATE students SET stu_gender='男';

//修改一列数据根据num来改

UPDATE students SET stu_gender='男' WHERE stu_num='20240101';//基本都是通过主键修改

//修改多列数据同时根据名字修改

UPDATE students SET stu_gender='女',stu_name='李四',stu_age=24 WHERE stu_name='张三';

查询代码：

查库：

//查询所有数据库

SHOW DATABASES;

查表：

//查询数据表

SHOW TABLES;

//查询表结构

DESC students;

//查询表中所有数据 *代表所有的

SELECT * FROM students;

//指定列查询

SELECT stu_name FROM students;

查数据：

单表查询：

//查询性别为女的数据

SELECT * FROM students WHERE stu_gender='女';

//查询数据取别名并且算出生年份 AS 别名不建议取中文

SELECT stu_name AS 学生姓名,今年(2024)年份-stu_age AS 出生年份 FROM students;

SELECT stu_name,2024-stu_age AS stu_birth_year FROM students;

//去重并查询 DISTINCT 去重

SELECT DISTINCT stu_age FROM students;

//排序(升序，降序)ORDER BY 排列

SELECT * FROM students ORDER BY stu_age;//没写升序降序默认升序

//升序 ASC

SELECT * FROM students ORDER BY stu_age ASC;

//降序 DESC也可以查表结构

SELECT * FROM students ORDER BY stu_age DESC;

//多字段排序先排序性别再排序年龄

SELECT * FROM students ORDER BY stu_gender ASC,stu_age DESC;

区间查询：

BETWEEN AND 在...之间

//查询年龄在18到29之间的数据

SELECT * FROM students WHERE stu_age BETWEEN 18 AND 29;

//查询年龄不在18到29之间的数据

SELECT * FROM students WHERE stu_age NOT BETWEEN 18 AND 29;

//查询性别为女并且年龄小于25岁的数据

SELECT * FROM students WHERE stu_gender='女' AND stu_age<25;

//查询性别为男或者年龄大于25岁的数据

SELECT * FROM students WHERE stu_gender='男' OR stu_age>25;

模糊查询：

LIKE 类似于百度搜索

//查询名字中有张字的数据

SELECT * FROM students WHERE stu_name LIKE '%张%';

//查询名字中最后一个字为三字的数据

SELECT * FROM students WHERE stu_name LIKE '%三';//查第一个字为张 LIKE '张%'

//查询名字中第二个字为三字的数据

SELECT * FROM students WHERE stu_name LIKE '_三%';

分组查询：

GROUP BY 分组（后面只能跟一个列名）

//分组查询先对性别进行分组然后统计每组数据个数

SELECT stu_gender,COUNT(stu_num) FROM students GROUP BY stu_gender;

//分组查询先对性别进行分组然后计算每组的平均年龄

SELECT stu_gender,AVG(stu_age) FROM students GROUP BY stu_gender;

//分组查询先对年龄进行分组再统计各组人数还可以对最终的结果进行排序

SELECT stu_age,COUNT(stu_num)FROM students GROUP BY stu_age ORDER BY stu_age;
//分组查询先对年龄进行分组再统计男生的人数还可以对最终的结果进行排序

SELECT stu_age,COUNT(stu_num)FROM students WHERE stu_gender='男' GROUP BY stu_age ORDER BY stu_age;

//先查询所有数据按年龄分组然后统计每组男生人数

//再筛选当前组人数大于1的组 HAVING 拥有 COUNT(*)>1 按年龄升序显示

SELECT stu_age,COUNT(*)FROM students WHERE stu_gender='男' GROUP BY stu_age HAVING COUNT(*)>1 ORDER BY stu_age;//这里不能使用WHERE

WHERE是筛选分组前的条件 HAVING是筛选分组后的条件

优先级：WHERE > GROUP BY > HAVINE > ORDER BY

分页查询：

LIMIT

//第一页分页查询每页两条数据

SELECT * FROM student LIMIT 0,2; // LIMIT (1-1)*每页有几条数据,每页有几条数据；

//第二页

SELECT * FROM student LIMIT 2,2; //(2-1)*2

两表连接查询：

数据表的别名：（开发常用）

SELECT s.*,c.class_name FROM students s INNER JOIN classes c ON s.cid=c.class_id;

内连接查询：

内连接查询关键字 INNER JOIN 如果不加其他条件查询是没有意义的需要添加条件查询

//使用WHERE作为过滤条件连接查询效率很低

//WHERE 先生成笛卡尔积再从笛卡尔积中过滤数据

SELECT * FROM students INNER JOIN classes WHERE students.cid=classes.class_id;

//使用ON作为过滤条件连接查询 INNER JOIN 一般都匹配ON使用

//ON 先判断连接条件是否成立如果成立两张表的数据进行组合生成一条结果记录

SELECT * FROM students INNER JOIN classes ON students.cid=classes.class_id;

左连接：

//左连接LEFT JOIN 显示左表所有数据如果在右表中存在与左表记录满足匹配条件的数据则进行匹配如果右表中不存在匹配记录则显示null

SELECT * FROM students LEFT JOIN classes ON students.cid=classes.class_id;

右连接：

//右连接 RIGHT JOIN

SELECT * FROM students RIGHT JOIN classes ON students.cid=classes.class_id;

子查询：

//先进行一次查询第一次查询的结果作为第二次查询的源/条件第二次查询是基于第一次查询的结果来进行的

//查询班级名称为Java2024 班级中的学生信息（只知道班级名称不知道班级id）

SELECT class_id FROM classes WHERE class_name='Java2024';

//class_id 已经被记录

SELECT * FROM students WHERE cid=1;

//子查询

SELECT * FROM students WHERE cid=

(SELECT class_id FROM classes WHERE class_name='Java2024');

//子查询返回多个值 方法1 UNION 多行单列（查询所有Java班级中的学生信息）

SELECT class_id FROM classes WHERE class_name LIKE '%Java%';
SELECT * FROM students WHERE cid=1
UNION
SELECT * FROM students WHERE cid=2
UNION
SELECT * FROM students WHERE cid=3

//子查询返回多个值 方法2 IN 多行单列（查询所有Java班级中的学生信息）

SELECT * FROM students WHERE

cid IN(SELECT class_id FROM classes WHERE class_name LIKE '%Java%');

//拓展查询cid=1的班级中性别为男的同学

SELECT * FROM(SELECT * FROM students WHERE cid=1) t WHERE t.stu_gender='男';

聚合函数：

//查询表中数据总条数 COUNT 计数（括号中不能为空一般放* 或者放列名）

SELECT COUNT(*)AS 总条数 FROM students;

//查询总数据中有多少数据为男性

SELECT COUNT(*)AS 总条数 FROM students WHERE stu_gender='男';

//查询最大年龄 MAX

SELECT MAX(stu_age)AS 最大年龄 FROM students;

//查询最小年龄 MIN

SELECT MIN(stu_age)AS 最大年龄 FROM students;

//计算总和(年龄)SUM

SELECT SUM(stu_age)AS 年龄总和 FROM students;

//计算数据男性年龄总和

SELECT SUM(stu_age)AS 年龄总和 FROM students WHERE stu_gender='男';

//计算平均值(年龄)AVG

SELECT AVG(stu_age)AS 年龄平均值 FROM students;

//计算数据女性年龄平均值

SELECT AVG(stu_age)AS 年龄平均值 FROM students WHERE stu_gender='女';

日期函数：

//获取当前系统时间

SELECT NOW();
SELECT SYSDATE();

INSERT INTO students(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)
VALUES('20241001','张三','男',23,'18624170526','4880085218','2024-07-28 9:29:29');//正常的

//通过NOW()获取当前时间

INSERT INTO students(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)
VALUES('20241003','王五','男',25,'18624170528','4880085220',NOW());

//通过SYSDATE()获取当前时间

INSERT INTO students(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)
VALUES('20241002','李四','女',24,'18624170527','4880085219',SYSDATE());

字符串函数：

//拼接使用CONCAT()将数据拼接后查询

SELECT CONCAT(stu_name,'-',stu_age) FROM students;

//转换将字段的值转换为大写UPPER()

SELECT UPPER(stu_name)FROM students;

//转换将字段的值转换为小写LOWER()

SELECT LOWER(stu_name)FROM students;

//截取从指定列中截取部分显示 SUBTRING(列名，从第几位开始，截取几位)

SELECT stu_name,SUBSTRING(stu_tel,8,4)FROM student;

使用代码：

//使用或切换数据库（常用）

USE School;

//自动增长代码一般加在主键后建表时可以额外定义一个与数据无关的列做id 便于以后删改查

AUTO_INCREMENT //只保证唯一性不保证连续性

CREATE TABLE stu(stu_id INT PRIMARY KEY AUTO_INCREMENT,stu_name VARCHAR(6) NOT NULL,stu_remark VARCHAR(100)
);

// 如果自动增长的数据因为删除导致不连续想恢复成连续状态 TRUNCATE

TRUNCATE TABLE students;//删除表中的数据让自动增长从1开始

约束：

约束：限制保证数据有效性完整性正确性

常见约束：

NOT NULL(非空) 数据不能为空

UNIQUE(唯一) 数据不能重复

FOREIGN KEY(外键) 两表之间的关联关系

PRIMARY KEY(主键) 数据不能为空也不能重复

主键：一般用在表的第一例表里可以有多个主键

添加主键的第一种方式：

CREATE TABLE IF NOT EXISTS books (
book_isbn CHAR(4)PRIMARY KEY,
book_name VARCHAR(11)NOT NULL,
book_author VARCHAR(6)NOT NULL
);

//删除主键

ALTER TABLE students DROP PRIMARY KEY;

//修改建表后添加主键约束 stu_num CHAR(4) 必须与表一致

ALTER TABLE books MODIFY book_isbn CHAR(4) PRIMARY KEY;

联合主键：

将数据表中的多列组合在一起设置成主键

外键约束：

数据于数据的四种关系：一对一，一对多，多对多，多对一

外键关联主键（顺序不能乱）

外键关联主键类型一般为INT 如果为CHAR或者其他类型 数据类型与长度必须一致

CONSTRAINT KF_大写的外键表名_大写的主键表名 FOREIGN KEY(外键列名)

REFERENCES 表名(主键列名)；

//在建表时关联主键

CREATE TABLE IF NOT EXISTS classes(class_id INT PRIMARY KEY AUTO_INCREMENT,class_name VARCHAR(40) NOT NULL,class_remark VARCHAR(200)
);
CREATE TABLE IF NOT EXISTS students(stu_num CHAR(8) PRIMARY KEY,stu_name VARCHAR(20) NOT NULL,stu_age INT NOT NULL,stu_gender CHAR(2) NOT NULL,cid INT,CONSTRAINT KF_STUDENTS_CLASSES FOREIGN KEY(cid) REFERENCES classes(class_id);
);

//建表后关联主键

ALTER TABLE students ADD CONSTRAINT KF_STUDENTS_CLASSES FOREIGN KEY(cid) REFERENCES students(class_id)；

//删除外键约束

ALTER TABLE students DROP FOREIGN KEY KF_STUDENTS_CLASSES;

//级联关系

副表必须与主表里面没有关联的时候才可以删除

必须先解决关联先删除副表中的数据再删除主表中的数据

//在添加外键时设置级联修改和级联删除

//1.先删除原来的外键

ALTER TABLE students DROP FOREIGN KEY FK_STUDENTS_CLASSES;

//2.重新添加外键并设置级联修改和级联删除

ALTER TABLE students ADD CONSTRAINT FK_STUDENTS_CLASSES FOREIGN KEY(cid)

REFERENCES classes(class_id) ON UPDATE CASCADE ON DELETE CASCADE;

关键字：

WHERE(子句)后面跟条件用于筛选满足特定的添加的数据进行删除修改查询

数值类型：

大小范围类型其他

整数型：

tinyint 1byte 有符号-128~127 无符号0~255 （特小型整数）（年龄）

smallint 2byte （16bit） -32768~32767 D-65535（小整数型）

mediumint 3byte -2^31~2^31-1 0~2^32-1 (中型整数)

int/integer 4byte （整数）

bigint 8byte （大型整数）

小数型：

float 4byte （单精度）

double 8byte （双精度）

decimal 第一参数+2 写法:decimal(10,2)表示数值一个有10位，小数位2位

字符串：

char 0~255 定长字符串最多255个字符当我们去指定数据表字段char 10个长度没用到会补\u000

varchar 0~65536 可变长度字符串 65536

二进制字符串：tinyblob（0~255），blob（0~65535），mediumbolb（0~1677215），longblob （0~4294967295）

文本数据（字符串）：tinytext（0~255），text（0~65535），mediumtext（0~1677215），longtext（0~4294967295）

日期类型：

date 2022-02-26

time 21：56：50

year 2022

datetime 2022-02-26 21：56：59

timestamp 20220226 215659（时间戳）

条件关系运算符：

= 等于

!= 不等于

<> 不等于

> 大于

< 小于

>= 大于等于

<= 小于等于

事物：

转帐案例：

CREATE DATABASE IF NOT EXISTS day06;
USE day06;
CREATE TABLE IF NOT EXISTS account(id INT PRIMARY KEY,uname VARCHAR(20) NOT NULL UNIQUE,money DOUBLE
);
INSERT INTO account(id,uname,money)VALUES(1,'张三',1000);
INSERT INTO account(id,uname,money)VALUES(2,'李四',1000);
SELECT * FROM account;

//mysql事物自动提交

//设置事物为手动提交

SET @@autocommit=0;//设置手动提交
SELECT @@autocommit;
UPDATE account SET money=money-100 WHERE uname='张三';
UPDATE account SET money=money+100 WHERE uname='李四';
ROLLBACK;//回滚
COMMIT;//手动永久提交不可以回滚

JAVA连接数据库

JDBC

public class MainTest {public static void main(String[] args) {//jdbcConnection conn = null;Statement st = null;ResultSet rs = null;//jdbc必须提供驱动 jar包try {//注册驱动 com.mysql.jdbc.DriverDriverManager.registerDriver(new com.mysql.jdbc.Driver());//Class.forName("com.mysql.jdbc.Driver");//建立连接 协议+访问的数据库名字 用户名 root  用户名密码 123456conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/student?verifyServerCertificate=false&useSSL=false", "root", "123456");//跟数据库打交道   Statement 这个对象一定要写st = conn.createStatement();//执行查询st.executeQuery()           //执行增删改 st.executeUpdate()String sql ="select * from t_stu";rs = st.executeQuery(sql);//返回一个结果集 是一块数据//需要一条一条的数据 要遍历while (rs.next()) {int id = rs.getInt("id");String uname = rs.getString("uname");String pwd = rs.getString("pwd");System.out.println("id=" + id + "\tuname=" + uname + "\tpwd=" + pwd);}} catch (SQLException e) {throw new RuntimeException(e);}finally{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);}}}
}}

数据库