开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> mysql全面学习 -> 正文阅读

[大数据]mysql全面学习

Mysql

shell查看数据库常用命令

net start mysql   //启动mysql服务
mysql -u root -p //输入密码登录mysql
show databases； //展示所有的数据库
use databaseName; //选中某个数据库
show tabels; //展示所有的表
describe tableName; //查看某个表的属性
exit  //退出mysql
net stop mysql //关闭mysql服务

用sql语句创建一个表

//在school中创建teacher表的写法
USE school;
CREATE TABLE IF NOT EXISTS `teacher`(
   `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '编号',
   `name` VARCHAR(100) NOT NULL DEFAULT '匿名' COMMENT '姓名',
   `age` VARCHAR(2) NOT NULL DEFAULT '18' COMMENT '年龄',
   `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
   PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

//用可视化界面创建完 数据库/表 之后可以查看创建 数据库/表 的语句
SHOW CREATE DATABASE school
SHOW CREATE TABLE student
DESC student //查看表的结构

//给student表添加外键
/*ALTER TABLE `表名` CONSTRAINT `约束名` 
FOREIGN KEY(`外键`) REFERENCES `要引用的表名`(`表的列名`)*/
ALTER TABLE `student` ADD CONSTRAINT `FK_aid` 
FOREIGN KEY(`aid`) REFERENCES `aaa`(`aid`);

注意：上面的添加外键是物理层面的写法，一般不使用
真实项目中，数据库只用来当做存储数据的表，外键都是用程序来实现【注意】

表中数据的增删改

添加 insert
insert into 表名（属性名1，属性名2，...） values(值1，值2，...)

//添加一行数据
INSERT INTO `teacher`(`name`,`age`,`sex`)
VALUES('许鹏程','十岁','男')
//添加多行数据 
INSERT INTO `teacher`(`name`,`age`,`sex`)
VALUES('许鹏程','十岁','男'),('顾慧','三岁','女')

修改 update
update表名set属性名='替换的数据' where id='筛选的条件'

//修改一个属性用一个where条件
UPDATE `teacher` SET `name`='xxx' WHERE id='1'
//修改多个属性用多个where条件
//条件可以有`AND OR BETWEEN AND 各种运算符`
UPDATE `teacher` SET `name`='xxx',`age`='99'
 WHERE id!=3 AND `age`='十岁'

删除delete/truncate
delete from 表名 where 删除的条件
delete和truncate的区别是：
- delete清空表不会改变表的自增量
- truncate清空表会改变表的自增量

//删除表中的某些数据
DELETE FROM `teacher` WHERE id=1
//清空表
DELETE FROM `teacher`  //方式一
TRUNCATE `teacher` //方式二

数据库查询DQL（Data Query Language）【重点】

// AS 将列的名字改变
SELECT studentno AS '编号',`studentname` AS '姓名' FROM student 
WHERE sex='男'

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SqO3lfi6-1631888341563)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210915214252336.png)]

// concat
SELECT CONCAT('编号',studentno) AS '标号' FROM student 
WHERE sex='男'

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gFzYc2Se-1631888341568)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210915213955825.png)]

//distinct
SELECT `studentno` FROM result
SELECT DISTINCT `studentno` FROM result

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DazPRKZn-1631888341570)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210915220057415.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DsMTsFYz-1631888341574)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210915220137297.png)]

//模糊查询 like/in/is null/is not null
SELECT `studentno`,`studentname` FROM `student` 
WHERE `studentname` LIKE '张%'

SELECT `studentno`,`studentname` FROM `student` 
WHERE `studentname` LIKE '张_'

SELECT `studentno`,`subjectno`,`studentresult` FROM `result`
WHERE `studentresult` IN (88,89)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WcauSf1W-1631888341577)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210915220541587.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rYmwTCdl-1631888341580)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210915220608500.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qEvpT7Y0-1631888341581)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210915221612070.png)]

//连表查询join on/inner join on/left join on/right join on

//分页和排序 order by / limit

//子查询 where in()

//分组和过滤 group by / having

mysql函数

select abs(-9) //9
select ceiling(3.4) //4
select floor(3.4)  //3

聚合函数

count/sum/avg/max/min

SELECT COUNT(`studentno`) FROM `student`
SELECT AVG(`studentresult`),SUM(`studentresult`)
,MAX(`studentresult`),MIN(`studentresult`) FROM `result`

测试 MD5 加密

INSERT INTO TestMD5 VALUES('1','许鹏程',MD5(123456)),
('2','顾慧',MD5(12345678))

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-g5K7Ml0Q-1631888341584)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210916151310861.png)]

SELECT * FROM TestMD5 WHERE NAME='许鹏程' AND pwd=MD5(123456)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q8Y4TPAB-1631888341586)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210916153218385.png)]

事务的ACID原则

原子性 一致性 隔离性 持久性

执行事务的模拟场景

INSERT INTO `account` (`id`,`name`,`money`) 
VALUES ('1','张三','1000'),('2','李四','5000')

SET autocommit=0  //因为mysql是默认自动提交的，先关闭自动提交

START TRANSACTION //开启事务
UPDATE account SET money=money-500 WHERE `name`='张三'
UPDATE account SET money=money+500 WHERE `name`='李四'

COMMIT  //提交，注意：提交之后不能再回滚
ROLLBACK //只要还没提交，就可以回滚

SET autocommit=1 //开启自动提交

索引的分类

主键索引 primary key（属性名）
唯一索引 unique key 索引名（属性名）
常规索引 key 索引名（属性名）
全文索引 fulltext 索引名（属性名）

CREATE TABLE `student` (
  `studentno` INT(4) NOT NULL COMMENT '学号',
  `loginpwd` VARCHAR(20) DEFAULT NULL,
  `studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
  `sex` CHAR(2) DEFAULT NULL COMMENT '性别，0或1',
  `gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
  `phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
  `address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
  `borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
  `email` VARCHAR(50) NOT NULL COMMENT '邮箱账号允许为空',
  `identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    /*此处是加索引的语法*/
  PRIMARY KEY (`studentno`),
  UNIQUE KEY `identitycard` (`identitycard`),
  KEY `email` (`email`)
    
) ENGINE=MYISAM DEFAULT CHARSET=utf8

//显示索引信息
show index from `表名`
//查看索引过程
explain 查询语句
//给某个表的某个属性添加索引
create index `索引名` on 表名（属性名）

测试索引

//创建app_user表
CREATE TABLE `app_user` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT NULL COMMENT '用户昵称',
  `email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
  `phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号',
  `gender` TINYINT(4) UNSIGNED DEFAULT NULL COMMENT '性别（0：男;1:女',
  `password` VARCHAR(100) NOT NULL COMMENT '密码',
  `age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 COMMENT='app用户表'

//插入100万条数据
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
DETERMINISTIC
BEGIN
 DECLARE num INT DEFAULT 1000000;
 DECLARE i INT DEFAULT 0;
 WHILE i < num DO
  INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
  VALUES(CONCAT('用户',i),'24736743@qq.com',CONCAT('18',FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
  SET i = i + 1;
 END WHILE;
 RETURN i;
END $$
SELECT mock_data();

//测试没加索引需要的时间
//测试没加索引寻找的行数
SELECT * FROM app_user WHERE `name`='用户9999'
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999'

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-33iGnLuL-1631888341588)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210917091658281.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NOSTuDR1-1631888341589)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210917091533384.png)]

//测试加索引需要的时间
//测试加索引寻找的行数
CREATE INDEX `key_app_user_name` ON `app_user`(`name`)
SELECT * FROM app_user WHERE `name`='用户9999'
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999'

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6rPWb1au-1631888341590)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210917092410411.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gwb2yFaA-1631888341591)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210917092444025.png)]

mysql最原始的增删改查

public class testjdbc {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //写好url 用户信息
        String url = "jdbc:mysql://localhost:3306/school?useUnicode=true" +
                "&characterEncoding=utf8&useSSl=true";
        String username = "root";
        String password = "123456";
        //获取数据库连接对象
        Connection conn = DriverManager.getConnection(url, username, password);
        //获取可以操作数据库的对象
        Statement statement=conn.createStatement();
        //执行sql语句
        statement.executeQuery("SELECT * FROM `testmd5`");
        //获取数据并输出
        ResultSet res=statement.getResultSet();
        while (res.next()) {
            System.out.println("id:" + res.getObject("id"));
            System.out.println("name:" + res.getObject("name"));
            System.out.println("password:" + res.getObject("pwd"));
        }
        res.close();
        statement.close();
        conn.close();
    }
}

封装之后的数据库增删改查

//TestJdbc类
public class TestJdbc2 {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        Connection connection=new JdbcUtils().start();
        Statement statement=connection.createStatement();
        //数据库的查询
        /*ResultSet res=statement.executeQuery("SELECT * FROM `testmd5`");
        while (res.next()) {
            System.out.println("id:" + res.getObject("id"));
            System.out.println("name:" + res.getObject("name"));
            System.out.println("password:" + res.getObject("pwd"));
        }
        JdbcUtils.end(connection,statement,res);*/
        //数据库的插入
        /*int res=statement.executeUpdate("INSERT INTO testmd5(`name`,`pwd`)" +
                " VALUES('李四',MD5(123456))");
        if (res > 0) {
            System.out.println("插入成功");
        }
        JdbcUtils.end(connection,statement,null);*/
        //数据库的修改
        /*int res=statement.executeUpdate("UPDATE testmd5 SET `name`='李四' WHERE id=4");
        if (res > 0) {
            System.out.println("修改成功");
        }
        JdbcUtils.end(connection,statement,null);*/
        //数据库的删除
        int res=statement.executeUpdate("DELETE FROM testmd5 WHERE id=2");
        if (res > 0) {
            System.out.println("删除成功");
        }
        JdbcUtils.end(connection,statement,null);

    }
}

//JdbcUtils类
public class JdbcUtils {
    private static String driver=null;
    private static String url=null;
    private static String username=null;
    private static String password=null;
    static{
        //注意此处Properties的使用
        InputStream resourceAsStream = 			            JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        try {
            properties.load(resourceAsStream);
            driver=properties.getProperty("driver");
            url=properties.getProperty("url");
            username=properties.getProperty("username");
            password=properties.getProperty("password");
            //加载驱动
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection start() throws ClassNotFoundException, SQLException {
        Connection connection = DriverManager.getConnection(url, username, password);
        return connection;
    }

    public static void end(Connection conn, Statement st, ResultSet res) throws SQLException {
        if (res != null) {
            conn.close();
        }
        if (st != null) {
            conn.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
}

//db.properties的内容
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

sql注入问题

利用OR 改变sql语句，使得where后面的条件为真之后就可以盗取数据库的数据

public class TestJdbc2 {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        String name="'or '1=1";  
        String pass = "' or '1=1";
        log(name,pass);
    }
    public static void log(String myname,String pass) throws SQLException, ClassNotFoundException {
        Connection connection=new JdbcUtils().start();
        Statement statement=connection.createStatement();
        //数据库的查
        String sql="SELECT * FROM testmd5 WHERE `name`='"+myname+"' AND `pwd`='"+pass+"'"; //注意此处的字符串拼接
        ResultSet res=statement.executeQuery(sql);
        while (res.next()) {
            System.out.println("id:" + res.getObject("id"));
            System.out.println("name:" + res.getObject("name"));
            System.out.println("password:" + res.getObject("pwd"));
        }
        JdbcUtils.end(connection,statement,res);
    }
}

PreparedStatem解决防注入问题

public class TestJdbc2 {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        String name="张三";
        String pass = "123456";
        log(name,pass);
    }
    public static void log(String myname,String pass) throws SQLException, ClassNotFoundException {
        Connection connection=new JdbcUtils().start();
        //数据库的查
        //此处是PreparedStatement的使用方法
        String sql="select * from testmd5 where `name`=? and `pwd`=?"; //先写sql语句
        PreparedStatement st = connection.prepareStatement(sql);//再预编译
        st.setString(1,"张三");  //再设置参数
        st.setString(2,"123456");
        ResultSet res=st.executeQuery();//最后执行
        
        while (res.next()) {
            System.out.println("id:" + res.getObject("id"));
            System.out.println("name:" + res.getObject("name"));
            System.out.println("password:" + res.getObject("pwd"));
        }
        JdbcUtils.end(connection,st,res);
    }
}