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);
    }
}

数据库连接池