AI专业者开发社区 1 号成员
  •  0 回帖  •  5 浏览  •  4 个月前
   

Mysql学习

数据库分类

关系型数据库 行列

MySQL、Oracle、Sql Server，DB2，SQLite

非关系型数据库 key value （NoSQL）Not only

  • Redis，MongDB

  • 对象存储，通过对象自身的属性来决定

DBMS 数据库管理系统

-- 单行注释
/* 多行注释 */ 
show databases;
use school；
show tables；
describe student;
create database west;
exit;

  • DDL 数据库定义语言

  • DML 数据库操作语言

  • DQL 数据库查询语言

  • DCL 数据库控制语言

操作数据库

mysql 关键字不区分大小写

操作数据库

CREATE DATABASE [IF NOT EXISTS] school;
DROP DATABASE [IF EXISTS] hello;
USE `school`;
SHOW DATABASES;
SHOW DATABASE school;

数据库列类型

  • 数值

    tinyint 1 字节

    smallint 2 字节

    mediumint 3 字节

    int 4 字节

    bigint 8 字节

    float 4 字节

    double 8 字节

    decimal 字符串形式的浮点数

  • 字符串

    char 定长 0-255 字符

    varchar 可变字符串 0-65535 字节

    tinytext 微型文本 2^8 - 1

    text 文本串 2^16 - 1 即 65535

  • 时间日期

    date YYYY-MM-DD，日期格式

    time HH：mm：ss，时间格式

    datetime YYYY-MM-DD HH：mm：ss 最常用的时间格式

    timestamp 时间戳，1970.1.1 至今的毫秒数

    year 年份表示

     

创建 & 操作表

创建表

CREATE TABLE IF NOT EXISTS student(
    id INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(5) NOT NULL DEFAULT '匿名' COMMENT '名字',
    `birthday` DATE DEFAULT NULL COMMENT '出生日期',
    PRIMARY KEY (id)
) ENGINE=INNODB  DEFAULT CHARSET=utf8;
-- 显示表的详细信息
DESC student;
-- 显示创建表或数据库的详细信息
SHOW CREATE DATABASE school;
SHOW CREATE TABLE student;

操作表

-- 重命名表
ALTER TABLE student RENAME AS student1;
-- 给表添加字段
ALTER TABLE student1 ADD age INT(11);
-- 给表修改字段
ALTER TABLE student1 MODIFY age VARCHAR(11);
ALTER TABLE student1 CHANGE age age1 INT(10);
-- 删除字段
ALTER TABLE student1 DROP age1;
-- 删除表
DROP TABLE IF EXISTS student2;

 

操作列（DML）

unsigned、zerofill、自增、非空、默认

-- 增加物理外键，不便管理
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 主表 ('stu_id');
ALTER TABLE student1
ADD CONSTRAINT 'FK_id' FOREIGN KEY ('id') REFERENCES student2('stu_id');

DML 数据库操作语言

-- 添加
INSERT INTO student1 (`name`,`birthday`) VALUES ('wang','1999-1-1'),('w','1998-1-1');
-- 修改
UPDATE student1 SET `name`='狂' WHERE id = 1;
UPDATE student1 SET `name`='wang',`birthday`='1998-1-1'  WHERE id >= 4;
-- 删除 1，delete from 表
DELETE FROM student1 WHERE `name` = '匿名';
-- 删除 2，计数器会归零，不会影响事务
TRUNCATE TABLE student2

重启数据库后，delete 的现象：

  • InnoDB 自增列从 1 开始，存在内存中，断电即失

  • MyISAM 从上一个子增量开始，存在文件中，不会丢失

 

DQL 查询语言

<> 或 != （不等于）、between ... and ... 、AND、OR、Not

IS NULL，IS NOT NULL，LIKE（模糊查询，% 表 0 到任意个字符，_ 表 1 个字符）、IN（in (1001, 1002)）

-- 查询的基本语言
SELECT * FROM student1;
SELECT `name` AS 名字 FROM student1;
-- 去重  distinct
SELECT DISTINCT `name` AS 名字 FROM student1;
​
-- 查询当前用户
SELECT USER()
-- 查询系统版本
SELECT VERSION()
-- 查询自增的步长
SELECT @@auto_increment_increment;
-- 用来计算表达式
SELECT 100 * 3 / 4;
​
-- 查询日期
SELECT CURRENT_DATE()
SELECT CURDATE()
SELECT NOW()
SELECT LOCALTIME()
SELECT SYSDATE()
-- now 作参数
SELECT YEAR(NOW())

联表查询

join （连接的表） on（连接条件） 连接查询

where 等值查询

-- Inner join on/where 返回左右表匹配的笛卡尔积
-- Left join on 返回左表有，即使右表没有的笛卡尔积
-- Right join on 返回右表有，即使左表没有的笛卡尔积
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` 
FROM student AS s INNER JOIN result AS r
WHERE s.studentno = r.studentno

自连接

-- 一张表拆成两张表
SELECT A.`categoryname` AS 父,  B.`categoryname` AS 子
FROM `category` AS A, `category` AS B
WHERE B.pid = A.`categoryid`

子查询

SELECT a.`studentno`,`studentname`,r.`studentresult`
FROM student AS a
INNER JOIN result AS r  
ON a.`studentno` = r.`studentno`
WHERE r.`studentresult` > 50 AND `subjectno` = (
    SELECT `subjectno` FROM `subject` WHERE `subjectname` = '高等数学 -2');

聚合函数

-- count 函数
/*
    列名为主键，count(列名) 比 count(1) 快，不为主键则相反
    多列且无主键，count(1) 效率优于 count(*)
    有主键，count(主键) 最优
    只有一列，count(*) 最优
*/
-- 忽略字段的所有 null 值
SELECT COUNT(`birthday`) FROM `student1`
-- 不忽略 null 值
SELECT COUNT(*) FROM `student1`
SELECT COUNT(1) FROM `student1`
​
-- SUM, AVG, MAX, MIN

select

select [all | distinct]
{* | table.* | [table.field1 [as alias1] [,table.field2 [as alias2]] [,...]]}
from table_name [as table_alias]
    [left | right | inner join table_name2 on 等值判断]  -- 联合查询
    [where ...]  -- 具体的值 或者 子查询
    [group by ...]  -- 通过某个字段分组
    [having ...]    -- 过滤分组信息，条件和where一样
    [order by ...]  -- 通过某个字段排序，升序or降序
    [limit startIndex, pagesize]  -- 显示从哪到哪的查询记录

事务

一组 SQL 放在一个批次中去执行

事务原则 ACID：

原子性：事务要么都成功，要么都失败

一致性：事务前后的数据完整性保持一致

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

隔离性：事务互不干扰，相互隔离

隔离级别：

脏读：一个事务读取了另一个事务未提交的数据

不可重复读：某个事务读取数据时，多次结果不一样，场合不对（修改）

虚读（幻读）：某个事务读取数据时，别的事务插入一行，前后读取不一致（插入）

-- 关闭自动提交
SET autocommit = 0;
-- 事务开启
START TRANSACTION;
UPDATE act SET money = money - 500 WHERE `name`='a';
UPDATE act SET money = money + 500 WHERE `name`='b';
-- 提交，持久化
COMMIT;
-- 回滚
ROLLBACK;
-- 事务结束，开启自动提交
SET autocommit = 1;

索引

主键索引 primary key：唯一标识、主键不可重复

唯一索引 unique key：避免重复的列出现，多个列可标识为唯一索引

常规索引 key / index：index 或 key 关键字定义索引

全文索引 fulltext ： 引擎支持

alter table table1 add key / index 索引名 (列名)

create key / index 索引名 on table(列名)

索引加在常用来查询的字段上，默认 Btree

-- 导出数据库的表
mysqldump -hlocalhost -uroot -p123456 school grade >D://a.sql
-- 登录，导入数据库的表
source D://a.sql

范式

第一范式：无重复的列 （原子性）

第二范式：属性完全依赖于主键，消除部分子函数依赖（完全依赖，非部分依赖）只描述一件事情

第三范式：属性不依赖于其他非主属性，消除传递依赖（直接依赖，非间接依赖）

规范性 和 性能 不可兼得

JDBC

JDBC 是 Java 数据库操作规范，java 安装数据库驱动，来操作数据库

import java.sql.*;
​
public class jdbc1 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1. 加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        
        // 2. 新建 url 和 用户登录信息
        // 协议:// 主机地址: 端口号 / 数据库名? 参数 1& 参数 2& 参数 3
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&usessl=true";
        String name = "root";
        String pwd = "wang";
​
        // 3. 建立连接，数据库对象
        Connection connection = DriverManager.getConnection(url, name, pwd);
        // 4. 新建执行 sql 的对象
        Statement statement = connection.createStatement();
        // 5.sql 语句，执行并返回结果集
        String sql = "select * from users";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            System.out.println(resultSet.getInt("id"));
            System.out.println(resultSet.getObject("NAME"));
            System.out.println(resultSet.getObject("PASSWORD"));
        }
        // 6. 释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

增、删、改均用 statement.executeUpdate()，查找用 statement.executeQuery()；

prepareStatement 可防止 mysql 注入问题，把传进来的参数当作字符来处理

// prepareStatement
String sql1 = "insert into users(id, `NAME`, `PASSWORD`, `email`,`birthday`) values (?,?,?,?,?)";
// 预编译 sql
PreparedStatement preparedStatement = connection.prepareStatement(sql1);
// 再设置参数
preparedStatement.setInt(1, 4);
preparedStatement.setString(2, "zhaoliu");
preparedStatement.setString(3, "123456");
preparedStatement.setString(4, "88888@qq.com");
preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));
int i = preparedStatement.executeUpdate();
if(i > 0) {
    System.out.println("插入成功！");
}

Java 中 处理事务，只需要前期 关闭自动提交，后期会自动恢复

Connection connection = DriverManager.getConnection(url, name, pwd);
// 关闭自动提交，开启事务
connection.setAutoCommit(false);
String sql1 = "update act set money = money - 50 where `name` ='a'";
String sql2 = "update act set money = money + 50 where `name` ='b'";
PreparedStatement ps1 = connection.prepareStatement(sql1);
PreparedStatement ps2 = connection.prepareStatement(sql2);
ps1.executeUpdate();
ps2.executeUpdate();
// 事务提交，事后会自动恢复 自动提交
connection.commit();
// 关闭资源，事务失败的话默认也会回滚
ps2.close();
ps1.close();
connection.close();

数据库连接池

编写连接池，实现 DataSource 接口，getConnection 方法不会变

设置最小连接数、最大连接数、等待超时 100ms

连接池：准备一些预先的资源，等待连接，如 DBCP、C3P0、Druid