数据库MYSQL

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

小照灯

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

阅读量306

点赞数

文章标签： java

本文链接：https://blog.csdn.net/weixin_45285317/article/details/103310330

版权

数据库的最基本操作语法：
查看数据库：SHOW DATABASES;
查看数据库的字符集：SHOW CREATE DATABASE 数据库名；
修改数据库的字符集：ALTER DATABASE 数据库名 CHARACTER SET UTF8/GBK;
查看现在正在使用的库：SELECT DATABASE();
创建数据库：CREATE DATABASE 表名；
删除数据库：DROP DATABSE 表名；
查看数据库中的表：SHOW TABLES;
查看表结构：DESC 表名；
创建一个表：

create table student
( id int ,
name varchar(20),
age int,
birthday date,
insert_time timestamp
);

查看表的字符集：SHOW CREATE TABLE 表名；
修改表的字符集：ALTER TABLE 表名 CHARACTER SET UTF8/GBK;
修改表的名称：ALTER TABLE 表名 RENAME TO 新表名；
在表中添加一列：ALTER TABLE 表名 ADD 列名数据类型；
修改列名：ALTER TABLE 表名 CHANGE 原列名新列名数据类型;
只修改列的类型：ALTER TABLE 表名 MODIFY 列名数据类型；
删除列：ALTER TABLE 表名 DROP 列名；
DML
增删改表中的数据
增添数据：INSERT INTO 表名(列名1，列名2，…列名n) VALUES(值1，值2…值n)；
1.列名与值需要一一对应
2.如果表名后不添加列名：INSERT INTO 表名 VALUES(值1，值2…值n)；代表的是给所有的列添加元素。
3.除了int其他的数据类型需要用引号。
删除数据 DELETE FROM 表名 WHERE 条件；
1.注意如果不加WHERE 条件就会将表中的所有数据全部删除。不推荐使用效率低。
如果要删除一个表的建议使用TRUNCATE TABLE 表名；这样删除后会从先创建一个结构一模一样的空表。
修改数据 UPDATA 表名 SET 列1=值1，列2=值2…WHERE 条件；

例子：UPDATE student SET age=28，score=99.99 WHERE id=10；

注意如果不加条件会将表内的目前所要修改的列全部进行修改
查询Retrieve
LIMTI：分页查询其实语法就是limit +我们从那个位置开始 , 要查询几个数据。
修改Update
update 表名 set 我们修改的数据 where 修改的是那些部分的。例name
删除（Delete）
delete from 表名 where （id name…）
DQL
SELECT * FROM 表名；
将全表的数据全部查出来
SELECT 字段（id，name…）FROM 表名
WHERE 条件（）
GROUP BY 分组字段
HAVING 分组之后的条件
ORDER BY 排序（比如按分数等）
LIMIT 分页的限定
基础查询：

多个字段的查询：SELECT 字段（id，name…）FROM 表名
去除重复：SELECT DISTINCT 字段（id，name…）FROM 表名去重需要注意，只有结果集完全相同的情况下才可以去重
计算列：
SELECT NAME,MATH,ENGLIST,CHINESE,MATH+ENGLIST+CHINESE
FROM 表名这里需要注意如果说我们相加过程中有一个值为null 则所有name所对应的数值和就为null 处理方法我们在为null的科目前面加 if null（科目，0）使用0来替代进行加和。
起别名：接上面如果取和后，在表中显示的名字也是MATH+ENGLIST+CHINESE 我们觉得不好看可以起个别名MATH+ENGLIST+CHINESE AS 总分

SELECT NAME,MATH,ENGLIST,CHINESE,
IFNULL(MATH,0)+ENGLIST+CHINESE
AS（AS也可以省略用空字符代替）总分

FROM 表名   这样就起到了**别名**

条件查询：
1 . WHERE 字句后跟条件
2 .运算符：
在这里插入图片描述

BETWEEN AND 可以简化频繁使用 and ，IN（集合）可以简化or
LIKE 讲一下模糊查询：
语法：SELECT *FROM 表名 WHERE 条件例： name LIKE ‘张%’
有两个占位符一个是
_表示占一个位的
%表示占多个位

SELECT *FROM ST1 WHERE NAME LIKE “_化%”

这种是查询中间字段的方法
如果是进行查询字段个数的话，我们直接进行_来表示；
常用操作查询名字中包含的即可：

SELECT *FROM ST1 WHERE NAME LIKE “%马%”；

DQL：查询语句
1.排序查询
2.聚合函数
3.分组查询
4.分页查询
约束：
多表之间的关系
范式
数据库的备份和还原
1.排序查询：
排序语法 ORDER BY 子句
order by 排序字段1 排序方式1，排序字段2，排序方式2…
单个字段排序

SELECT *FROM 表名 ORDER BY 列名

会默认进行一个升序的排序+ ASC / DESC

ASC :升序
DESC ：降序

多个字段排序
例子：

SELECT *FROM stu ORDER BY math ASC,ENGLISH ASC;

只有当我们第一排序排完之后才会进行第二排序。
2.聚合函数
注意：聚合函数计算会排除 null 的数据如果计算个数中有null这样我们有两种解决方案
1.一般选择非空的列：主键
2. (IF NULL 列名，随便附一个值即可)
将一列数据作为一个整体，进行纵向的计算。比如求平均数
1.count：数量表示计算个数

SELECT COUNT(列名)FROM 表名；
简便写法 count（*）

求得有多少行。
2.max：计算最大值

SELECT MAX(列名)FROM 表名；

3.min：计算最小值

SELECT MIN(列名) FROM 表名

4.sum：计算和

SELECT SUM(列名) FROM 表名

5.avg：计算平均值

SELECT AVG(列名) FROM 表名

我们发现聚合函数其实就是一些简单的加减平均值而且语法很简单
并且我们查询出来的数据就是一个仅仅的单行单列。因为就一个答案很好理解。
3.分组查询
语法：GROUP BY 分组的字段
注意：
1.分组之后查询的字段：分组字段+聚合函数
2.where 和having的区别：
1.where在分组之前进行限定如果不满足条件则不参与分组，having是在分组之后进行限定，如果不满足结果则不会被查询出来。
2 . where 后不可以跟聚合函数，having可以进行聚合函数的判断
SELECT sex , AVG (math)FROM stu GROUP BY sex ；

SELECT 分组信息，聚合函数 FROM
分组信息，聚合函数可以添加多个分组信息就添加自己用什么分组的信息就可以了

SELECT sex ,AVG (math),COUNT(ID) FROM stu GROUP BY sex

例子：
–按照性别分类，分别查询男，女同学的平均分，人数，要求：分数低于70的人不参与分组，分组之后，人数要大于两个人
**

SELECT** sex ,AVG (math),COUNT(ID) person FROM stu WHERE
math>=70 GROUP BY sex HAVING person>2 ；

分页查询
语法：LIMIT 开始的索引，每页查询的条数
每页想显示3条记录

SELECT *FROM 表名 LIMIT 0,3–第一页
SELECT *FROM 表名 LIMIT 3,3–第二页

公式比较重要，开始的索引=<当前的页码-1> X 每页显示的条数
LIMIT 是一个MYSQL的“方言”

约束

概念：对表中的数据进行限定，保证数据的正确性，有效性和完整性。
1.约束我们是对表的结构进行一个约束。保证表中的数据高效
2.分类

主键约束：primary key
非空约束： not null
唯一的约束： unique
外键约束：foreign key
非空约束：not null
1.在创建表时添加约束

CREATE TABLE stu (
id INT,
NAME VARCHAR(20) NOT NULL – name不能为空
);

.删除表中的一个约束

ALTER TABLE stu CHANGE NAME NAME VARCHAR(20) 也可以使用modify

删除约束后我们就可以添加name为null的数据
2 .创建表完后添加约束条件

ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL

这个理解上来说就是我们去修改了列

唯一的约束： unique
被unique所修饰的列不能出现重复元素
注意MYSQL 唯一约束限定的列的值可以有多个null。
创建表时添加唯一约束：

CREATE TABLE stu (id INT,phone_number VARCHAR(20)UNIQUE)；

添加一下数据说明问题：

INSERT INTO stu(id,phone_number) VALUES(1,12345)； INSERT INTO
stu(id,phone_number) VALUES(1,12345)第二次是不能插入的。数据不允许重复

删除唯一约束：
不能使用我们

ALTER TABLE stu MODIFY phone_number VARCHAR(20)不能使用这样的操作 ALTER TABLE
stu DROP INDEX phone_number 我们需要删除它的索引

创建表后我们添加唯一约束

ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE

在添加唯一约束前需要保证你的表中是不能有重复的对应列的值，否则是有冲突的
先去消除表中需要添加唯一约束的列中清理掉相同元素，最后再次执行命令，就完成操作了。
**主键约束：primary key ** +自动增长
1.注意事项：
*1.含义：非空且唯一，综合了以上两种约束
*2 .一张表只能有一个字段为主键；一般赋值给id。
*3.主键就是表中记录的唯一标识。例如身份证。
2. 在创建表时，
添加主键约束：

CREATE TABLE stu(id INT PRIMARY KEY,NAME VARCHAR(20))

测试即可需要明白设么叫做主键即唯一+不为null
INSERT INTO stu(id,NAME)VALUES(1,‘张飞’)
删除主键

ALTER TABLE stu MODIFY id INT
ALTER TABLE stu DROP INDEX id
这两种方法同样不可以实现

ALTER TABLE stu DROP PRIMARY KEY 我们删除主键

你会发现你可以添加id是相同的元素但是我们无法去添加id为null的情况
因为在设置主键的时候会同时设定主键位置和 not null是否为空的位置，仅删除主键也代表只删除一个键位，不能够添加主键位为null

创建完表后，增加主键

ALTER TABLE stu MODIFY id INT PRIMARY KEY 即可轻松完成

个人总觉想要搞定约束 ->去选择desc 搞清楚它每一变化的表结构。
自动增长
概念：如果某一列是数值类型的，使用auto_increment 可以完成值的自动增长
在创建表的时候添加：

CREATE TABLE stu(id INT PRIMARY KEY auto_increment, – 给主键添加自动增长 NAME
VARCHAR(20) )

在这里插入图片描述
可以看到是不能为NULL 但是我们插入：

INSERT INTO stu VALUES(NULL,‘aaa’);

不会报错会进行自动的一个升序。结构显示是没有问题的，就是在你自己输入null时，是它自动调整为auto_increment 就不会报错了
但此时是不可以出现重复数据。
注意：自动增长他只会联系到你上一条输入的值进行增长
删除自动增长

ALTER TABLE stu MODIFY id INT

就可以了主键这个方式是删不掉的会删除掉自动增长
添加自动增长

ALTER TABLE stu MODIFY id INT AUTO_INCREMENT

外键约束
在这里插入图片描述
1.在创建表时，可以添加外键 foreign key 让表与表中直接产生关系，从而保证数据的正确性。
外键的名称一般是由字表_主表_fk 组成
建立关系我们回提前在字表内建立一个主表主键的一个信息
主表的名称我们需要关联的是唯一性的列 但更多一般都是关联主键
语法：

create rable 表名（
…
外键列
constraint 外键名称 foreign key（外键列名称）references 主表名称（主表列的名称）

CONSTRAINT emp_dept_fk FOREIGN KEY(dep_id)REFERENCES department(id)
）；

2.删除外键
查看两个表中已经没有外键时可以通过子表中添加与父表中不相同的id就是主键信息，看是否成功。也可以在SQLyog 中的架构设计器中查看

ALTER TABLE 表名 DROP FOREIGN KEY emp_dept_fk （外键名称）

3.创建表之后，添加外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段名称) REFERENCES department(主表主键名称)

我们需要了解到外键可以做些什么更加的安全，合理
我们的有外键连接到另一张表的时候，是无法直接删除主表，并且再添加数据的过程中我们的外键填入的值必须是主键中有的 或者也可以为null 否则是无法添加的

4.级联操作
操作主表的主键使子表内主表名称内容 直接进行更改
创建表之后，添加外键时候设置级联更新。

ALTER TABLE employee ADD
CONSTRAINT emp_dept_fk FOREIGN KEY(dep_id)REFERENCES department(id)
ON UPDATE CASCADE ;

在这里插入图片描述

级联删除操作：
级联删除也是需要我们先进行将我们的外键进行删除，然后在从新建立外键连接的过程中进行级联删除操作，级联删除指的是在父表中删除对应的主键，相对应的字表中的与父表想连接的数据全部被删除掉。同时级联更新与级联删除可以同时加载进去

对应的语法

ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk – 删除外键操作
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY(dep_id)REFERENCES
department(id) ON UPDATE CASCADE ON DELETE CASCADE ; – 建立外键和级联更新删除操作
DELETE FROM department WHERE id=2 – 删除主表中的主键
SELECT *FROM employee – 显示到字表中的数据情况

在这里插入图片描述
级联如果在数据联系多的情况下要小心使用小心使用小心使用

数据库的设计

1.多表之间的关系
一对一：
如人和身份证的关系
分析：一个人只有一个身份证，一个身份证也只能对应一个人
一对多（多对一）：
如：部门和员工
分析：一个部门有多个员工，一个员工只能对应一个部门
多对多的关系：
如：学生和课程
分析：一个学生可以选择很多门课程，一个课程也可以被很多学生所选择
2.实现关系：
1.一对多（多对一）：
如：部门和员工来实例分析
在这里插入图片描述
实现方式：在多的一方去建立外键，指向一的一方的主键。
2.多对多的关系：
如：学生和课程

实现方式：多对多关系的实现需要借助第三张中间表。中间表至少包含两个字段
，这两个字段作为第三张表的外键，分别指向两长表的主键。同时中间表不能出现两个一模一样相对应的关系我们在中间表中需要去创建一个复合主键
3.一对一的关系：
在这里插入图片描述
实现的方式：一对一的关系实现可以在任意一方添加唯一外键指向另一方的主键一般一对一的关系，我们会将数据整合在一个表中。

现在我们来完成一个旅游的选择说明问题：
内容:
旅游路线分类：创建旅游线路分类表 tab_category

– 创建旅游线路分类表 tab_category
– cid 旅游线路分类主键，自动增长
– cname 旅游线路分类名称非空，唯一，字符串 100
CREATE TABLE tab_category (
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(100) NOT NULL UNIQUE )

旅游路线：创建旅游线路表 tab_route

– 创建旅游线路表 tab_route
/* rid 旅游线路主键，自动增长
rname 旅游线路名称非空，唯一，
字符串 100 price 价格 rdate 上架时间，
日期类型 cid 外键，
所属分类
*/ CREATE TABLE tab_route(
rid INT PRIMARY KEY AUTO_INCREMENT,
rname VARCHAR(100) NOT NULL UNIQUE,
price DOUBLE,
rdate DATE, cid INT,
FOREIGN KEY (cid) REFERENCES tab_category(cid) – 省略写法在字表中先建立父表的列
然后使用外键去关联父表的主键 )

用户：创建用户表 tab_user

/* 24 / 26 创建用户表 tab_user
uid 用户主键，
自增长 username 用户名长度 100，
唯一，非空
password 密码长度 30，非空
name 真实姓名长度 100
birthday 生日
sex 性别，定长字符串 1
telephone 手机号，
字符串 11 email 邮箱，
字符串长度 100
*/ CREATE TABLE tab_user (
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(100) UNIQUE NOT NULL,
PASSWORD VARCHAR(30) NOT NULL,
NAME VARCHAR(100),
birthday DATE,
sex CHAR(1) DEFAULT ‘男’,
telephone VARCHAR(11),
email VARCHAR(100)
)

存在多对多的的关系我们需要建立一张中间表：

/* 创建收藏表 tab_favorite
rid 旅游线路 id，外键
date 收藏时间
uid 用户 id，
外键 rid 和 uid不能重复，
设置复合主键，
同一个用户不能收藏同一个线路两次
*/ create table tab_favorite (
rid int,
date datetime,
uid int,
– 创建复合主键
primary key(rid,uid),
foreign key (rid) references tab_route(rid),
foreign key(uid) references tab_user(uid)
)

在这里插入图片描述

2.数据库设计的范式（准则）

JDBC 编程

在这里插入图片描述
**面向对象：**将现实实物抽象为我们的类，对现实实物的行为抽象为方法或接口：三大特征：继承，封装，多态
封装：基本概念，开闭原则（修改关闭，扩展开放）
继承：单继承，继承父类的属性和方法。共性：共有的属性。个性：存在于子类中特有的
多态：运行时决定具体类型或方法，表现形式：动态绑定向上转型，重载，重写。
面向接口编程：
API
面向切面编程
对应动态代理，具体实现有多种
API公共的程序接口：
在Java JDBC编程中对数据库的操作均使用JDK自带的API统一处理，通常与特定数据库的驱动类是完全
解耦的。所以掌握Java JDBC API （位于 java.sql 包下）即可掌握Java数据库编程。

DriverManager类来获取的Connection连接，是无法重复利用的，每次使用完以后释放资源
时，通过connection.close()都是关闭物理连接。使用Statement。

package com.bit.util;
import java.math.BigDecimal;
import java.sql.*;
public class DBUtil {
    public static final String URL="jdbc:mysql://localhost:3306/test";
    public static final String USERNAME="root";
    public  static final String PASSWORD="123456";
    public static void main(String[] args)  {
        //加载JDBC驱动程序，调用初始化com.mysql.jdbc.Driver类将该类
        //加载到JVM方法去，并执行该类的静态方法块，静态属性
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            //创建数据库连接
            connection = DriverManager.
                    getConnection(URL,USERNAME,PASSWORD);
            statement = connection.createStatement();
            String sql="select id,name,chinese,math,english from exam";
            resultSet= statement.executeQuery(sql);
            while (resultSet.next()){
                //K val 形式
                int id=resultSet.getInt("id");
                String name=resultSet.getString("name");
                BigDecimal chinese=resultSet.getBigDecimal("chinese");
                BigDecimal math=resultSet.getBigDecimal("math");
                BigDecimal english=resultSet.getBigDecimal("english");
                System.out.printf("id=%s,name=%s,chinese=%s,math=%s,english=%s"
                        ,id,name,chinese,math,english);
                System.out.println();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet!=null)
                    resultSet.close();
                if (statement!=null)
                    statement.close();
                if (connection!=null)
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
     }
    }
    }
}

DataSource提供连接池的支持。连接池在初始化时将创建一定数量的数据库连接，这些连接
是可以复用的，每次使用完数据库连接，释放资源调用connection.close()都是将
Conncetion连接对象回收。代码使用PreparedStatement

package com.bit.util;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.math.BigDecimal;
import java.sql.*;

public class DBUtil2 {
    public static final String URL="jdbc:mysql://localhost:3306/test";
    public static final String USERNAME="root";
    public  static final String PASSWORD="123456";
    public static void main(String[] args)  {
        //加载JDBC驱动程序，调用初始化com.mysql.jdbc.Driver类将该类
        //加载到JVM方法去，并执行该类的静态方法块，静态属性
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        try {
            //获取数据库连接池，由连接池获取连接
            DataSource ds=new MysqlDataSource();
            ((MysqlDataSource)ds).setURL(URL);
            ((MysqlDataSource)ds).setUser(USERNAME);
            ((MysqlDataSource)ds).setPassword(PASSWORD);
            connection =ds.getConnection();
            String sql="select id,name,chinese,math,english from exam where id=?";
            statement = connection.prepareStatement(sql);
            //我们使用statement.setInt（）1，2表示的是第一个占位符？，放入2，
            //语法使用过程中要在resultSet= statement.executeQuery(sql);中去掉sql
            statement.setInt(1, 2);
            resultSet= statement.executeQuery();
            while (resultSet.next()){
                //K val 形式
                int id=resultSet.getInt("id");
                String name=resultSet.getString("name");
                BigDecimal chinese=resultSet.getBigDecimal("chinese");
                BigDecimal math=resultSet.getBigDecimal("math");
                BigDecimal english=resultSet.getBigDecimal("english");
                System.out.printf("id=%s,name=%s,chinese=%s,math=%s,english=%s"
                        ,id,name,chinese,math,english);
                System.out.println();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet!=null)
                    resultSet.close();
                if (statement!=null)
                    statement.close();
                if (connection!=null)
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
     }
    }
    }
}

在这里插入图片描述
PreparedStatement对象

小照灯

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
数据库MYSQL

查询RetrieveLIMTI：分页查询其实语法就是limit +我们从那个位置开始 , 要查询几个数据。修改Updateupdate 表名 set 我们修改的数据 where 修改的是那些部分的。例name删除（Delete）delete from 表名 where （id name…）JDBC 编程**面向对象：**将现实实物抽象为我们的类，对现实实物的行为抽象为方法或...
复制链接

扫一扫