2021-08-07

最新推荐文章于 2024-07-15 15:40:07 发布

JiangHuFengZi

最新推荐文章于 2024-07-15 15:40:07 发布

阅读量100

点赞数

文章标签： java 数据库

本文链接：https://blog.csdn.net/JiangHuFengZi/article/details/119491988

版权

SQL语句

操作数据库

-- 创建数据库
create database 数据库名称
-- 删除数据库
drop database 数据库名称
-- 使用数据库
user 数据库名称

-- 查看所有数据库
show databases

操作表

创建表

--auto_increment
--字符串使用 单引号括起来
--所有的语句后面加,(英文逗号)，最后一个不用加
--primary key 主键 ，一般一个表只有一个唯一的主键
create table student
(
   id int 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
)

create table 表名(
	字段名  列类型 [属性] [索引] [注释],
    字段名  列类型 [属性] [索引] [注释],
    ......
    字段名  列类型 [属性] [索引] [注释]
)

外键

方式一：创建表时创建外键

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 '出生日期',
  -- `gradeid` INT(10) NOT NULL COMMENT '学生的年纪', -- 学生表的gradeid字段要去引用年纪表的gradeid
  -- `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
  -- `email`  VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  -- PRIMARY KEY (`id`),
   KEY `FK_gradeid` (`gradeid`),
   CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
)-- ENGINE=INNODB DEFAULT CHARSET=utf8

KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)

方式二：创建表后在添加外键

-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)	
-- ALTER TABLE `表名` ADD CONSTRAINT `约束名` FOREIGN KEY (`作为外键的列名`) REFERENCES `哪各表` (`哪个字段`)

以上的操作都是物理外键，是数据库级别的外键，不建议使用！（避免数据库过多造成困扰）

最好的实现外键的方式:

数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
如果想使用多张表的数据，想使用外键（用程序去实现）

添加

insert

-- 插入语句（添加）
-- insert into `表名` (`字段名1`,`字段名2`) VALUES ('插入的值1','插入的值2')
INSERT INTO `grade` (`gradename`) VALUES ('大四')

-- 由于主键自增我们可以省略

-- 同一个字段一次插入多个值
INSERT INTO `grade` (`gradename`)
VALUES ('大一'),('大二')

-- 一般写插入语句数据和字段要一一对应
INSERT INTO `student` (`name`,`pwd`,`sex`) VALUES ('张三','12345','男')
INSERT INTO `student` VALUES ( 2,'王五','12345','男','2001-06-08',1,'上海','email')

-- 一次插入多条数据
INSERT INTO `student` (`name`,`pwd`,`sex`) 
VALUES ('张三','12345','男'),('李四','123456','男')

语法：insert into 表名 (字段名1,字段名2) VALUES (‘插入的值1’,‘插入的值2’)

修改

uplate

-- 修改学员的名字 带了简介
UPDATE `student` SET `name` = '小明' WHERE id = 2

-- 不指定条件的情况下，会改动所有表！
UPDATE `student` SET `name` = '小航'

-- 修改多个属性，逗号隔开
UPDATE `student` SET `name` = '小明',`email` ='2597845QQ.com' WHERE id = 2;

-- 语法：
-- UPDATE 表名 set colnum_name = value,[colnum_name = value...] where 条件

UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name` ='小明'AND `sex` = '男'; -- CURRENT_TIME 当前时间

删除

delete

语法：delete from 表名 [where 条件]

-- 删除数据
DELETE FROM `student` -- 避免这样删除，这样会全部删除
-- 删除指定数据
DELETE FROM `student` WHERE id = 1

TRUNCATE 命令

作用：完全清空一个数据库表，表的结构和索引约束不会变！

-- 清空数据库表
TRUNCATE `student`

查询数据

在这里插入图片描述

-- 去重数据：distinct
-- 查询一下有哪些同学参加考试，成绩
SELECT * FROM result  -- 查询全部的考试成绩
SELECT `StudentNo` FROM result -- 查询有哪些同学参加了考试

-- 发现重复数据太多，影响观察，去重
SELECT DISTINCT `StudentNo` FROM result -- 去查询结果中的重复的数据




-- 模糊查询
-- 查询姓刘的同学
-- like 结合  %(代表任意个字符)  _(一个字符)
SELECT `id` ,`name` FROM `student`
WHERE `name` LIKE '刘%'  -- %(代表任意个字符)
-- 查询 1 ，2，3号的学生
-- 方式一：
SELECT `id` ,`name` FROM `student`
WHERE id =1 OR id =2 OR id =3 
-- 方式二：
SELECT `id` ,`name` FROM `student`
WHERE id IN (1,2,3)



-- 自联接
-- 查询父子信息:把一张表看为两张一模一样的表
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`



-- 排序，分页，联表查询和子查询
-- 查询Java第一学年课程成绩排名前十的学生，并且分数要大于80的学生信息（学号，姓名，课程名称，分数）
SELECT s.studentNo,studentName,SubjectName,StudentResult -- 查询的四个字段
FROM student s
INSERT JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
-- 去除条件不符合的 
WHERE SubjectName = 'Java第一学年' AND StudentResult >80
-- 按成绩排下序
ORDER BY StudentResult DESC -- 降序，升序就写 ASC 即可
-- 前十名 分页
LIMIT 0,10
-- 第一页  limit 0,5 			   (1-1)*5	
-- 第二页  limit 5,5 			   (2-1)*5
-- 第三页  limit 10,5 			   (3-1)*5
-- 第N页   limit (n-1)*pageSize, pageSize  (n-1)*pageSize, pageSize  
-- [pageSize：页面是显示数据的条数]
-- [总页数 = 数据总数/页面显示大小]

-- 子查询
SELECT DISTINCT s.`studentNo`,`studentName`
FROM student s
INNER JOIN result r
ON r.studentNo = s.studentNo
WHERE studentResult >=80 AND `subjectNo` = (
	SELECT subjectNo FROM `subject`
	WHERE `subjectName` = '高等数学'
)

-- 二：
SELECT DISTINCT s.`studentNo`,`studentName`
FROM student
WHERE studentNo = (
	SELECT studentNo FROM result -- 看题中条件是哪个表的内容(这里是studentResult 属于result表)，就要联接哪个表（result表），就查询这两个表公共内容（这里是studentNo）用做联接两个表
	WHERE studentResult >=80 AND subjectNo = (-- 在判断从这个表中查询公共内容的条件（studentResult >=80）
		SELECT subjectNo FROM `subject`
		WHERE subjectName = '高等数学'
	
	)
)

-- 合起来题目就是 查询课程为 高等数学 且分数不小于 80 的同学的学号和姓名

-- 用联接查询
SELECT  s.studentNo ,studentName
FROM student s
INNER JOIN result r
ON s.studentNo = r.studentNo
INNER JOIN `subject` sub
ON sub.subjectNo = r.subjectNo
WHERE subjectName = '高等数学' AND studentResult >=80






-- 分组过滤
-- 查询不同课程的平均分，最高分，最低分，平均分大于80的
-- 核心：（根据不同的课程分组）

SELECT  subjectName , AVG(`subjectResult`),MAX(subjectResult),MIN(subjectResult)
FROM `result` r
INNER JOIN `subject` s
ON r.subjetNo = s.subjetNo
GROUP BY r.subjetNo  -- 通过什么字段来分组
HAVING subjectResult>80 -- 平均分大于八十

在这里插入图片描述

事务

什么是事务

要么都成功，要么都失败

==================

1. SQL执行 A  给 B 转账    A 最开始有1000  A 转200 给 B   B  最开始有 200   2. SQL执行 B 收到 A 的钱  A  要变成 800  B  要变成 400

==================

将一组SQL 放在一个批次中去执行，（即让它们，要么都成功，要么都失败，不能一条成功，一条失败，）

INNODB 是支持事务的

事务原则：ACID原则原子性，一致性，隔离性，持久性（脏读，幻读…）

原子性（Atomicity）

要么都成功，要么都失败。

一致性（Consistency）

事务前后的数据完整性要保证一致。

隔离性（Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其它事务的操作数据所干扰，事务之间要隔离。

持久性（Durability）

事务一旦提交则不可逆，被持久化到数据库中！

隔离所导致的一些问题

脏读

值一个事务读取了另外一个事务未提交的数据。

不可重复读

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读（幻读）

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

-- =============== 事务 =================

-- MySQL 是默认开启事务自动提交的
 SET autocomimt = 0 -- 关闭
 SET autocomimt = 1 -- 开启
-- === 事务执行的完整过程 ===
-- 手动处理事务
 SET autocomimt = 0 -- 关闭自动提交
-- 事务开始
START TRANSACTION -- 标记一个事务的开始，从这个之后的SQL，都在同一个事务内
COMMIT -- 提交：持久化 （执行成功就提交）
ROLLBACK -- 回滚：回到原来的样子 （执行失败就回滚）
-- 事务结束
SET autocomimt = 1 -- 开启自动提交

事务处理案例：

-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `account` (
    `id` INT(3) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(30) NOT NULL,
    `money` DECIMAL(9,2) NOT NULL, -- 钱定义时就用 decimal类型
    PRIMARY KEY (id)
)ENGINE = INNODB DEFAULT CHARSET =utf8
INSERT INTO `account`(`name`,`money`)
VALUES('A',2000.0),('B',10000.0)


-- 模拟转账：事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务 （一组事务）
-- 下面两条语句就是一组事务
UPDATE account SET money = money - 500  WHERE `name` = 'A' -- A减五百
UPDATE account SET money = money + 500  WHERE `name` = 'B' -- B加五百

COMMIT; -- 提交事务，事务一旦提交就被持久化了，回滚无效
ROLLBACK; -- 回滚

SET autocommit = 1; -- 恢复默认值

三大范式

为什么需要数据规范化？
- 信息重复
- 更新异常
- 插入异常
  - 无法正常显示信息
- 删除异常
  - 丢失有效的信息

三大范式

第一范式（1NF）

原子性：要求数据库表的每一列都是不可分割的原子数据项

第二范式（2NF）

前提：满足第一范式。
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

在这里插入图片描述

每张表只能描述一件事情。

第三范式（3NF）

前提：满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能简介相关。

在这里插入图片描述

（规范数据库的设计）

规范性和性能的问题

想要满足规范的话，性能就不一定高。
关联查询的表不得超过三张表
- 考虑商业化的需求和目标，（成本和用户的体验问题）。这时数据库的性能更加重要。
- 在规范性能的问题的时候，需要适当的考虑一下规范性！
- 有时需要故意给某些表增加一些冗余的字段。（使得从多表查询变为单表查询）
- 故意增加一些计算列（从大数据量降低为小数据量的查询）

JDBC

数据库驱动

应用程序不能和数据库直接挂钩，所以需要数据库驱动（不同的数据库有不同的数据库驱动，由数据库厂商提供）来将二者进行连接。
程序会通过数据库驱动，和数据库打交道！

JDBC

如果开发时所用到的数据库很多，就会有很多的数据库驱动，相应的就要写多种程序，就带来很多不便。
所以 SUN 公司为了简化开发人员的（对数据库统一）操作，就提供了一个（Java操作数据库的）规范，俗称 JDBC。这些规范的具体实现由具体的厂商去做！
对于开发人员来说，我们只需要掌握 JDBC 接口的操作即可！

在这里插入图片描述

知道两个包

java.sql

javax.sql

还需要导入一个数据库驱动包 mysql-connector-java-5.1.47.jar

数据库连接类

package com.test01;

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

public class Test01 {
    //创建一个数据库连接类
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    static {
        try {
            //实例化出来的Test()类的对象调用，getclass()方法获得Class类的对象，
            //Test01 test01 = new Test01();
            //Class<? extends Test01> aClass = test01.getClass();
            //Class类的对象在调用Class类的getClassLoader()方法，获得ClassLoader类的对象，
            //ClassLoader classLoader = aClass.getClassLoader();
            //ClassLoader类的对象在调用ClassLoader类的getResourceAsStream()方法，返回一个InputStream类型的对象。
            //InputStream resourceAsStream = classLoader.getResourceAsStream("db.properties");
            InputStream in = new Test01().getClass().getClassLoader().getResourceAsStream("db.properties");
            //Properties是Map类型的集合，主要用来操作配置文件的，用来读取配置文件中的键值对信息。
            Properties properties = new Properties();
            properties.load(in);

            //通过getProperty()方法获得键对应的值
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            Class.forName(driver);//加载MySQL的驱动，只用执行一次，所以将它放在static{}（静态代码块中，静态代码块也是只执行一次的）
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }

    //获得数据库对象
    public static Connection getConnection() throws SQLException {
        //DriverManager 试图从已注册的 JDBC 驱动程序集中选择一个适当的驱动程序
        //getConnection() 方法用于获得试图建立到指定数据库 URL 的连接
        return DriverManager.getConnection(url,username,password);
    }

    //释放联接资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

JiangHuFengZi

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
2021-08-07

SQL语句操作数据库-- 创建数据库create database 数据库名称-- 删除数据库drop database 数据库名称-- 使用数据库user 数据库名称-- 查看所有数据库show databases操作表创建表--auto_increment--字符串使用单引号括起来--所有的语句后面加,(英文逗号)，最后一个不用加--primary key 主键，一般一个表只有一个唯一的主键create table student( id int no
复制链接

扫一扫