MySQL


MySQL


概述

MySQL是一个关系型数据库管理系统,由瑞典MySQL AB公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。我这边用的就是Oracle下的环境加上sqlyog来连接。


命令行操作

mysql -u root -p	-- 连接数据库
flush privileges;	-- 刷新权限
mysql> use name		-- 使用数据库
show tables;		-- 查看所有的表
describe student;	-- 显示数据库表的所有信息
create database name;-- 创建表
exit;				-- 退出连接
-- 单行注释

操作数据库

CREATE DATABASE [IF NOT EXISTS] name -- 创建数据库
DROP DATABASE [IF EXISTS] name -- 删除数据库
use name	-- 使用数据库
show databases;		-- 查看所有数据库

列的类型

tinyint    十分小的数据     1字节
smallint   较小的数据    2个字节
mediumint  中等大小的数据  3个字节
* int     标准的整数    4个字节   常用的int
bigint    较大的数据    8个字节
float     浮点数      4个字节
double    浮点数      8个字节
decimal    字符串形式的浮点数  金融计算时候一般使用这个
*char    字符串固定大小   0~255
*varchar    可变字符串  0~65535  常用的变量  String
*tinytext   微型文本    2^8 -1
*text     文本串     2^16-1   保存大文本
*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] `表名`(
	`字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    ......
    `字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]

也可用反查询:

SHOW CREATE TABLE `info`

eg:

CREATE TABLE IF NOT EXISTS `info`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT "学号",
	`names` VARCHAR(10) NOT	NULL DEFAULT "匿名" COMMENT "姓名",
	`passwd` VARCHAR(20) NOT NULL DEFAULT "123456" COMMENT "密码",
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8`info`

数据库引擎

MYISAM innoDB
事物支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间大小 较小 较大,2倍

修改表

-- 修改表名: alter table 旧表名 rename as 新表名
ALTER TABLE `info` RENAME AS `info1`
-- 增加表的字段: alter table 旧表名 add 字段名 列属性
ALTER TABLE `info` ADD age INT(11)

-- 修改表的字段: alter table 表名 modify 字段名 列属性
ALTER TABLE `info` MODIFY age VARCHAR(11)
-- 修改表的字段: alter table 表名 change 字段名 列属性
ALTER TABLE `info` change age age1 INT(11)

-- 删除字段 ALTER TABLE 表名 DROP 字段名
ALTER TABLE `info` DROP `age`
-- 删除数据库
DROP DATABASE [IF EXISTS] name

外键

MySQL可以设置物理外键但是因为关联不好直接删除table,因此不常用。

ALTER TABLE 主表名 ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(主表字段) REFERENCES 从表(字段)

DML语句

insert

-- insert into 表名([字段名1,字段名2,字段名3]) values('值1'),('值2'),('值3')
INSERT INTO `info`(`id`,`names`,`passwd`)
VALUES('1','11','123'),
('2','22','456'),
('3','33','789')

update

-- update 表名 set colnum_name=value,[colnum_name=value...] where [条件]
UPDATE `info` SET `names`='22',`passwd`='aaa' WHERE `id`=1;

where

where between num1 and num2
where name='1' and str='2'
where name='1' or str='2'
where name like 'a%'	-- 查询名字中a开头后面任意
where name like 'a_'	-- 查询名字中a开头后面一个字符_
where name like '%a%'	-- 查询名字中有a后面任意
where name in (value1,value2,value3...)	-- 查询名字在value范围中的具体值
where name is null 		-- 查询名字为null的
where name is not null 		-- 查询名字不为null的

delete

-- delete from 表名 [where 条件]
DELETE FROM `info` WHERE id=3;

Delete删除的问题:

innodb 自增列会重1开始(存在内存中,断电即失)

myisam 继续从上一个自增量开始(存在文件中,不会丢失)

truncate

truncate table name    -- 删除自增

DQL语句

select

-- select 字段 (算数) [as rename] from 表 [as rename] [where 条件]
SELECT `gradeid` AS 序号 FROM `grade`
-- 去重查询 select distinct [字段名] from 表
SELECT DISTINCT `gradeid` FROM `grade`
-- select version
select version()

JoinOn

连表查询可以由下面的图来解释,但是总结起来还是可以归为3类,left,right,inner查询,可以理解为以交点为中心,向指定方向查询。

-- 选择字段名注意有些要加前缀
-- 进行左右表排序,选择左右或内join
-- on选择关联条件
-- 可以加where语句结尾
SELECT a.studentno,loginpwd,sex,digit
FROM `student` AS a
LEFT JOIN `score` AS b
ON a.studentno = b.studentno

排序和分页

order by name desc			-- 降序
order by name asc			-- 升序
limit num1,num2				-- limit(起始下标,页面大小)

子查询

在两个以上的连表时我们可以使用where子查询去,有一种递归的感觉。

select distinct 字段名
from 表名
where 字段名 = (
	select distinct 字段名	from 表名	where 字段名 = (
    	...
    )
)

总和:

select [all|distinct] 字段名
from 表名 [as name]
[left|right|inner join 表名 as name]
[where ...]
[group by ...]
[having ...]
[order by ...]
[limit num1 num2]

函数

-- ===========================聚合函数============================
select count(name) from table_name	-- 查询个数,不算null
select count(*) from table_name		-- 查询个数,算null
select count(1) from table_name		-- 查询个数,算null
select sum(name) from table_name	-- 总和
select avg(name) from table_name	-- 平均值
select max(name) from table_name	-- 最大值
select min(name) from table_name	-- 最小值

事务

ACID:

原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
一致性(Consistency)
事务前后数据的完整性必须保持一致。
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响

贴一个链接:事务ACID理解_dengjili的专栏-CSDN博客_acid

流程:关闭自动提交—–>开启一个事物—–>(提交||回滚)—–>开启自动提交

-- SET autocommit=0   关闭自动提交
-- START TRANSACTION  开启交易
-- 执行语句
-- COMMIT  提交事物
-- ROLLBACK  回滚
-- SET autocommit=1 开启自动提交
SET autocommit=0
START TRANSACTION
UPDATE account SET money=money-500 WHERE `name`='A'
UPDATE account SET money=money+500 WHERE `name`='B'
COMMIT -- 提交事物
ROLLBACK -- 回滚
SET autocommit =1

索引

索引是帮助MySQL高效获取数据的数据结构。

  • 主键索引(primary key)
  • 唯一索引 (unique key)
  • 常规索引 (key|index)
  • 全文索引 (FullText)
-- 显示全部索引
SHOW INDEX FROM account
-- 添加索引
ALTER TABLE `test`.`account` ADD FULLTEXT INDEX `name`(`name`)
CREATE INDEX id_app_user_name ON app_user(`name`);
-- explain执行sql情况(非全文)
EXPLAIN SELECT * FROM `account`
-- explain执行sql情况
EXPLAIN SELECT * FROM `account` WHERE MATCH(`name`) AGAINST('A')

用户权限

以下为8.0以下的版本:

CREATE USER user_name IDENTIFIED BY 'passwd'		-- 创建用户
SET PASSWORD = PASSWORD('passwd')				   -- 设置密码
SET PASSWORD FOR user_name = PASSWORD(passwd)		-- 设置用户密码
RENAME USER user_name1 TO user_name2			   -- 重命名
GRANT ALL PRIVILEGES ON *.* to user_name		    -- 赋予权限
show grants for user_name						  -- 显示权限
revoke ALL PRIVILEGES ON *.* from user_name			-- 删除权限
drop user user_name								 -- 删除用户

数据备份

1.可以直接在sqlyog中去导出

2.选择命令行导出db

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 [表名] > 位置
mysqldump -hlocalhost -uroot -p123456 school md5 > D:/a.sql
# 导入数据库
source dir.sql

三大范式

第一范式

原子性:保证每一列不可分。

第二范式

在满足第一范式前提下,每张表只描述一件事情。

第三范式

满足一二范式的前提下,与主键要直接相关。


JDBC

一般是作为中间件去连接驱动和数据库,方便操作。

jar包下载地址:http://mvnrepository.com/artifact/mysql/mysql-connector-java

import java.sql.*;

public class mysql {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //用户信息和url
        String url = "jdbc:mysql://127.0.0.1:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=true";
        String username = "root";
        String password = "123456";
        //连接数据库,connection为数据库对象
        Connection connection = DriverManager.getConnection(url,username,password);
        //执行sql对象
        Statement statement = connection.createStatement();
        //操作并接受对象,result只有查询才有
        String sql = "SELECT * FROM users;";
        ResultSet resultSet = statement.executeQuery(sql);
        while(resultSet.next())
        {
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("NAME"));
            System.out.println("pwd="+resultSet.getObject("PASSWORD"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birth="+resultSet.getObject("birthday"));
        }
        //关闭连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

很多时候为了避免使用的代码重复我们可以这样写:

import java.sql.*;

public class JdbcUtils {
    private static String driver = "com.mysql.cj.jdbc.Driver";
    private static String url = "jdbc:mysql://127.0.0.1:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=true";
    private static String username= "root";
    private static String password = "123456";
    static {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }

    public static void release(Connection conn, Statement sta,ResultSet res){
        if(res!=null)
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if(sta!=null)
        try {
            sta.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if(conn!=null)
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void release(Connection conn, Statement sta){
        if(sta!=null)
            try {
                sta.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        if(conn!=null)
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }
}
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class test {
    public static void main(String[] args) {
        Connection conn = null;
        Statement sta = null;
        try {
            conn=JdbcUtils.getConnection();
            sta= conn.createStatement();
            String sql = "INSERT INTO `users` (`id`,`NAME`,`PASSWORD`,`email`,`birthday`)\n" +
                    "VALUES (11,\"dyd\",\"321321\",\"213@qq.com\",\"2022-02-22\")";
            sta.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.release(conn,sta);
        }
    }
}

防止sql注入的语句

public class test1 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement st = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "INSERT INTO `users` (`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";
            st = connection.prepareStatement(sql);
            st.setInt(1,7);
            st.setString(2,"hahahaha");
            st.setString(3,"13321");
            st.setString(4,"133@123.com");
            st.setString(5,"1999-03-03");
            int i = st.executeUpdate();
            if(i>0) System.out.println("success");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,st,null);
        }
    }
}


文章作者: Dydong
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 Dydong !
  目录