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

数据库

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

数据管理系统(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);}}}
}}