Mysql数据库学习笔记

发布时间:2023年12月20日

数据库分为关系型数据库和非关系型数据库,我们要学的MySQL数据库是关系型数据库。

Mysql的介绍这里不做赘述,网上一大片,这里直接步入正题!!!

一、SQL语言

SQL语言是一种结构化查询语言(Structured Query Language),结构化查询语言是关系型数据库标准语言,其特点是:简单,灵活,功能强大。

SQL包含6个部分:

  • 数据查询语言(DQL): 其语句,也称为“数据检索语句”,用以从表中获得数据,确定数据怎样在应用程序给出。保留字SELECT是DQL(也是所有SQL)用得最多的动词,其他DQL常用的保留字有WHERE,ORDER
    BY,GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。
  • 数据操作语言(DML): 其语句包括动词INSERT,UPDATE和DELETE。它们分别用于添加,修改和删除表中的行。也称为动作查询语言。
  • 事务处理语言(TPL): 它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION,COMMIT和ROLLBACK。
  • 数据控制语言(DCL): 它的语句通过GRANT或REVOKE获得许可,确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
  • 数据定义语言(DDL): 其语句包括动词CREATE和DROP。在数据库中创建新表或删除表(CREAT TABLE 或 DROP TABLE);为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。
  • 指针控制语言(CCL): 它的语句,像DECLARE CURSOR,FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

我们使用过程中主要使用数据查询语言(DQL)数据定义语言(DDL)数据操作语言(DML)

1、数据定义语言DDL

对象:数据库

  • 关键字:createalterdroptruncate(删除当前表再新建一个一模一样的表结构)

  • 创建数据库:create database 库名;

  • 删除数据库:drop database 库名;

  • 切换数据库:use 库名;

  • 创建表:create table 表名(
          字段名 类型(大小) 关系描述);

  • 查看数据库里存在的表:show tables;

  • 修改表:alter table 表名 约束;

  • 删除表:drop table 表名 ;

  • 查看生成表的sql语句:show create table 表名 ;

  • 查看表结构:desc 表名;

例:

#注意在MySQL数据库里写可以不带";",但是在DOS命令下写必须带。
create database school; #建库
drop database school; #删库
use school; #用库
create table student( #建表
	id int(4) primary key auto_increment,
	name varchar(20),
	socre int(3),
);
show tables;
alter table student rename studentplus; #修改表名 rename
alter table student add password varchar(20); #添加字段 add
alter table student change password pwd varchar(20);
alter table student modify pwd int;
alter table student drop pwd;
drop table student;
show create table student;
desc student;

2、数据操纵语言DML

对象:纪录(行)

  • 关键词:insert?update?、delete

  • 插入:insert into 表名 values(全属性); (插入所有的字段)

insert into 表名(属性1,属性2) values (属性1,属性2); (插入指定的字段)

  • 更新:update 表名 set 属性1 = ‘值’,属性2= ‘值’ where 主键(通常) = 值;

  • 删除:delete from 表名 where 主键(通常) = 值;

注意:
truncate和delete的区别:truncate是删除表,再重新创建这个表。属于DDL,delete是一条一条删除表中的数据,属于DML。

insert into student values(01,'Tonny',90);
insert into student(name,score) values ('Keby',98),('somenne',78);
update student set name = 'Tonn',score = '99' where id = 01;
delete from student where id = 01;

3、数据查询语言DQL

select ... from student where 条件 group by 分组字段 having 条件 order by 排序字段 #最复杂不过如此

执行顺序:from --> where --> group by --> having --> order by

3.1、条件查询

简单条件运算符:> < = != <> >= <=

逻辑运算符:&& || ! and or not (用于连接条件表达式)

模糊查询:like、between and、in、not in、is null

3.2、排序查询

  • 语法:order by 子句
    • order by 排序字段1 排序方式1 , 排序字段2 排序方式2…
  • 排序方式:
    • ASC:升序,默认的。
    • DESC:降序。
  • 注意:
    • 如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件。

3.3、聚合函数

将一列数据作为一个整体,进行纵向的计算。

  1. count:计算个数
    一般选择非空的列:主键
    select count(id) from 表名
  2. max:计算最大值
  3. min:计算最小值
  4. sum:计算和
  5. avg:计算平均值

注意:聚合函数的计算,排除null值。

3.4、分组查询

  1. 语法:group by 分组字段;
  2. 注意:
    1. 分组之后查询的字段:分组字段、聚合函数
    2. where 和 having 的区别?
      • where 在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来
      • where 后不可以跟聚合函数,having可以进行聚合函数的判断。

注意:

例如:

#所有字段查询
select * from student;
#带条件查询
SELECT * FROM `student` WHERE id > 1;
SELECT * FROM `student`  WHERE name like '%on%'; #模糊查询
select count(id) from student;
# 综合
SELECT *,AVG(score) FROM `student`  WHERE id > 1 GROUP BY id HAVING AVG(score)>20 ORDER BY id DESC;

4、高级查询

4.1、嵌套查询

嵌套查询也叫子查询,是把内层的查询结果作为外层的查询条件,并且可以多层嵌套。

语法格式:

select 字段名列表 from 表名 where 字段名 运算符(select 字段名 from 表名 where 条件) # select * ...就是查询全部

**注意:**外层的where的条件必须和内层的select查询的字段名一样,个数也一样。

4.2、多表查询

语法格式:

select 字段名列表 from 表一,表二 where 条件;

但是这种查询,很容易出现笛卡尔积,为了避免,我们可以将条件尽量细致化。

笛卡尔积
1.什么是笛卡尔积:两个集合的乘积,产生一个 新的集合。表示两个集合所有的可能的组合情况
2.笛卡尔积和关系:笛卡尔积中,去掉没有意义 或不存在的组合,就是关系(规范的二维表)

  • 笛卡尔积的匹配规则(面试常考点):
    • 记录多的表的每一天记录,去匹配另一张表的所有记录。
    • 两张表的记录条数相同时,则后表的每一条记录去匹配前表。

4.3、连接查询

  1. 内连接 inner join
  2. 外连接
    1>. 左连接 left join
    2>. 右连接 right join

语法格式:

select 字段名列表 from 表1 连接方式 join 表2 on 表1.字段=表2.字段;

左连接:left join?( 以左表为基准,匹配右表的所有内容,不够的显示NULL )
右连接:right join?(以右表为基准,去匹配左表的内容,不够的显示NULL )

4.4、联合查询

联合查询是可合并多个相似的选择查询的结果集。等同于将一个表追加到另一个表,从而实现将两个表的查询组合在一起,使用为此为UNINO或UNION ALL

联合查询:将多个查询的结果合并到一起(纵向合并):字段数不变,多个查询的记录数合并.

select 语句1
union[union 选项]
select 语句2
union|[union 选项]
select 语句n
//all:表示无论重复都输出
//distinct: 去重(整个重复)(默认的)
select *from addr
union all
select *from addr;

二、主外键

1、主外键的定义

主关键字(primary key ):?简称主键,它是表中的一个字段,它的值用于唯一地标识表中的某一条记录。

外关键字(foreign key) :?简称外键,是用于建立或加强两个表数据之间的链接的一列。

如果公共关键字在一个关系中是主关键字,那么这个公共关键字被称为另一个关系的外键。由此可见,外键表示了两个关系之间的相关联系。
以另一个关系的外键作主关键字的表被称为主表(父表)具有此外键的表被称为主表的从表(子表)
外键又称作外关键字

2、主外键的区别

主键:
定义:唯一标识一条记录,不能有重复的,不允许为空。
作用:用来保证数据完整性
个数:只有一个

外键:
定义:表的外键是另一个表的主键,外键可以有重复的,可以是空值。
作用:用来和其他表建立联系。
个数:一个表可以有许多外键。

3、创建主外键

3.1、创建主键

# 创建学生表加主键方法一
create table student(
	sid char(4) primary key,# 设置主键
	sname char(20),
	sage int,
	ssex char(2)
);
# 创建课程表加主键方法二
create table course(
	cid char(4),
	cname char(8),
	primary key(cid)
);

3.2、创建外键

# 创建成绩表设置外键
create table score(
	sid char(4),
	cid char(4),
	score int(3),
	foreign key(sid)references student(sid),
	foreign key(cid)references course(cid),
)

4、增删主外键

4.1、增删主键

alter table 表名 drop primary key;# 删除主键
alter table 表名 add primary key (列名);# 现有列上添加主键

4.2、增删外键

alter table 子表名 add [constraint fk_son]foreign key(子表的外键名称)references 父表名(父表的主键名称); #添加外键约束。fk_sno为外键ID名,若不添加,系统会自动配一个。
alter table 子表名 drop foreign key fk_sno; #删除外键约束。fk_sno为外键ID名, 若不知,可查询建表明细

注:外键一般不在正常工作中使用,一般使用的都是虚拟外键,即有外键功能,不真正设置表外键。?

?三、JDBC

前言:

在JDK1.0刚开始发布时,并没有跟着发布一个连接数据库的API,那么那时的开发人员,如果想要连接到数据库的话,该怎么做呢?那时的通常做法是使用另一个数据库驱动管理器——ODBC

ODBC(Open Database Connectivity?开放数据库互连)提供了一种标准的API(应用程序编程接口)方法来访问数据库管理系统(DBMS)。

这些API利用SQL来完成其大部分任务。ODBC本身也提供了对SQL语言的支持,用户可以直接将SQL语句送给ODBC。

ODBC的设计者们努力使它具有最大的独立性和开放性:与具体的编程语言无关,与具体的数据库系统无关,与具体的操作系统无关。

虽然使用ODBC的确是让Java开发人员也可以在应用中连接上数据库了,但由于ODBC毕竟并非是为Java而准备的,ODBC自身是使用C语言开发,标准接口也是对于C语言而言。因此那时的Java开发人员如果需要使用ODBC则不得不在Java程序中添加C语言的ODBC函数调用,使得许多Java自身的特性无法充分发挥,比如平台无关性、面向对象特性等。

因此在众多开发人员的强烈需求下,JDBC 1.0 随JDK1.1发布了。

众所周知,JDBC技术的实施需要六个步骤,我们一个个分析:

1、注册驱动

首先,我们需要使用配置文件配置数据库连接信息,用properties文件:

jdbc.url=jdbc:mysql://localhost:3309/pbip?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.username=root
jdbc.password=root

在连接数据库之前,首先要加载想要连接的数据库的驱动到JVM(Java虚拟机),
这通过java.lang.Class类的静态方法forName(String className)实现。
代码如下:

	/**
     * 注册驱动
     */
    private void driver(){
        try {
            Class.forName(properties.getProperty("jdbc.driver"));
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }
    }

2、建立连接

?要连接数据库,需要向java.sql.DriverManager请求并获得Connection对象,该对象就代表一个数据库的连接。
?使用DriverManager的getConnectin(String url , String username , String password )方法传入指定的欲连接的数据库的路径、数据库的用户名和密码来获得。
代码如下:

 	/**
     * 建立连接
     */
    private Connection conn;
     
    public void conn(){
        try {
            conn = DriverManager.getConnection(
                    properties.getProperty("jdbc.url"),
                    properties.getProperty("jdbc.username"),
                    properties.getProperty("jdbc.password")
            );
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

3、创建PreparedStatement对象

4、执行sql

?要执行SQL语句,必须获得java.sql.Statement实例,Statement实例分为以下3种类型:
1、执行静态SQL语句。通常通过Statement实例实现。
2、执行动态SQL语句。通常通过PreparedStatement实例实现。
3、执行数据库存储过程。通常通过CallableStatement实例实现。

具体的实现方式:

	private PreparedStatement ps;
	/**
     * pst对象
     * @param sql
     * @param values
     */
    public void getPst(String sql,Object...values){ //不定参数---数组
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0;i<values.length;i++){
                ps.setObject(i+1,values[i]);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

5、处理结果ResultSet

两种情况:
1、执行更新返回的是本次操作影响到的记录数。
2、执行查询返回的结果是一个ResultSet对象。
? ResultSet包含符合SQL语句中条件的所有行,并且它通过一套get方法提供了对这些
行中数据的访问。
? 使用结果集(ResultSet)对象的访问方法获取数据:

	//增删改
	public void executeUpdate(){
        try {
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
	//查
    public ResultSet executeQuery(){
        try {
            rs = ps.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

6、关闭连接

	/**
     * 关闭连接
     */
    public void close(){
        try {
            if(rs!=null){
                rs.close();
            }
            if(ps!=null){
                ps.close();
            }
            if(conn!=null){
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

测试代码

public class JdbcTest {

    JdbcUtil jdbcUtil = JdbcUtil.getInstance();
    @Test
    public void jdbcTest() throws Exception{
        jdbcUtil.conn();
        String sql = "SELECT * FROM category_tab";
        jdbcUtil.getPst(sql);

        ResultSet rs = jdbcUtil.executeQuery();

        while (rs.next()){
            System.out.println(
                    rs.getInt(1)+"-"+
                    rs.getString(2)+
                    rs.getString(3)
            );
        }
    }
}

这是JDBC最为简单的交互数据库的方式,我们一般项目中还需要用到其他配置,一般不再手写sql,而是放在mapper中,后续学习mybatis中的时候就会知道。

四、datasourse——Druid

DRUID是阿里巴巴开源平台上一个数据库连接池实现,它结合了C3P0、DBCP、PROXOOL等DB池的优点,同时加入了日志监控,可以很好的监控DB池连接和SQL的执行情况,可以说是针对监控而生的DB连接池(据说是目前最好的连接池。

1、配置参数

和其它连接池一样DRUID的DataSource类为com.alibaba.druid.pool.DruidDataSource,基本配置参数如下:(主要介绍几个非常常用的)

配置缺省值说明
name配置这个属性的意义在于,如果存在多个数据源,监控的时候可以通过名字来区分开来。 如果没有配置,将会生成一个名字,格式是:“DataSource-” + System.identityHashCode(this)
jdbcUrl连接数据库的url,不同数据库不一样。例如: mysql : jdbc:mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto
username连接数据库的用户名
password连接数据库的密码。
driverClassName根据url自动识别这一项可配可不配,如果不配置druid会根据url自动识别dbType,然后选择相应的driverClassName(建议配置下)
initialSize0初始化时建立物理连接的个数。初始化发生在显示调用init方法,或者第一次getConnection时
maxActive8最大连接池数量

2、使用方法

2.1、属性文件配置

druid.url=jdbc:mysql://localhost:3306/blog?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
druid.username=root
druid.password=root
druid.driverClassName=com.mysql.cj.jdbc.Driver

2.2、xml文件配置

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.datasource.unpooled.UnpooledDataSourceFactory;

/**
 * 使用druid作为数据源
 */
public class MyDateSource extends UnpooledDataSourceFactory {
    public MyDateSource(){
        this.dataSource= new DruidDataSource();
    }
}
<configuration>
	<!-- properties文件-->
	<properties resource="config/druid.properties"/>
	<!--实体类地址-->
	<typeAliases>
		<package name="com.dyit.mybatis.domain.entity"/>
	</typeAliases>

	<!--数据库配置-->
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="com.dyit.mybatis.druid.util.MyDateSource">
				<property name="driverClassName" value="${druid.driverClassName}" />
				<property name="url" value="${druid.url}" />
				<property name="username" value="${druid.username}" />
				<property name="password" value="${druid.password}" />
			</dataSource>
		</environment>
	</environments>

	<mappers>
		<mapper resource="mapper/Car.mapper.xml" />
	</mappers>
</configuration>

2.3、书写实体类

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.ibatis.type.Alias;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Alias("Car")
public class Car {

    private Integer id;
    private String color;
    private Double price;
}
package com.dyit.mybatis.druid.mapper;

import com.dyit.mybatis.domain.entity.Car;

import java.util.List;

public interface ICarMapper {
    void save(Car car);
    void delete(int id);
    void update(Car car);
    List<Car> findAll();
}
<mapper namespace="com.dyit.mybatis.druid.mapper.ICarMapper" >

	<!--表和类的映射关系-->
    <resultMap id="CarMapper" type="Car">
        <id property="id" column="car_id"/> <!--主键-->
        <result property="color" column="car_color"/>
        <result property="price" column="car_price"/>
    </resultMap>

    <insert id="save">
        insert INTO car_tab(car_color,car_price) values (#{color},#{price})
    </insert>

    <delete id="delete">
        delete from car_tab where car_id = #{id}
    </delete>

    <update id="update">
        update car_tab set car_color = #{color} where car_id = #{id}
    </update>

    <select id="findAll" resultMap="CarMapper">
        select * from car_tab
    </select>
</mapper>

2.4、测试代码

@Test
    public void testSelect()throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatisConfiguration.xml");
        
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = sessionFactory.openSession();
        
        ICarMapper mapper = sqlSession.getMapper(ICarMapper.class);

        List<Car> all = mapper.findAll();
        
        sqlSession.commit();
        sqlSession.close();
    }

这是最简易和原始的方法,我们可以封装SqlSession一系列操作,使用更加方便:

public class MybatisUtil {
    private static MybatisUtil instance;
    private SqlSessionFactory SessionFactory;

    private MybatisUtil(String fileName){
        init(fileName);
    }

    public void init(String fileName){
        try{
            SessionFactory = new SqlSessionFactoryBuilder().build(
                    Resources.getResourceAsReader(fileName)
        );
        }catch (Exception e){
            LogUtil.INSTANCE.debug(MybatisUtil.class,e);
        }
    }

    public static MybatisUtil getInstance(String fileName){
        if (instance == null){
            instance = new MybatisUtil(fileName);
        }
        return instance;
    }

    public SqlSession OpenSession(){
        SqlSession sqlSession = SessionFactory.openSession();
        return sqlSession;
    }

    public void sessionClose(SqlSession sqlSession){
        sqlSession.commit();
        sqlSession.close();
    }
}
	@Test
    //插入一辆车
    public void testSave2() {
        SqlSession sqlSession = m.OpenSession();
        ICarMapper mapper = sqlSession.getMapper(ICarMapper.class);
        mapper.save2("银白色",4000000.00,"阿斯顿马丁");
        m.sessionClose(sqlSession);
    }

以上就是他们的全部使用方法,当然,不是唯一的。

为什么用datasourse而不用JDBC直连?

DriverManager

  1. 在java类中创建/关闭连接时会妨碍应用程序性能,而且创建连接到关闭连接的步骤重复发生,浪费资源。
  2. 不支持连接池。

数据源

  1. 由于不在类中创建/关闭连接,因此它们可以提高应用程序性能,它们由应用程序服务器管理,并且可以在运行时获取。
  2. 它提供了一个创建连接池的工具。
  3. 有助于企业应用程序
文章来源:https://blog.csdn.net/qq_45901741/article/details/135079038
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。