【傲霜凌雪丶】MySQL笔记

MySQL

连接数据库

命令行连接

mysql -u root -p(或者mysql -u root -p密码)
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';--修改密码
flash privileges;--刷新权限
show databases：--显示所有数据库
use Test;--切换数据库
show tables;--显示所有表
describe student;--显示表内容
ctrl+c --强行终止
create database 数据库名;--创建一个数据库
--单行注释
/*
多行注释
*/

软件自动连接

操作数据库

增删改查 DDL 定义 DML 操作 DQL 查询 DCL 控制

不区分大小写

如果表名或者库名是关键字 则加数据库名

show database;--查看所有数据库
creat databases [if not exists] 数据库名;--创建数据库
drop database [if exists] 数据库名;--删除数据库
use 数据库名;--使用数据库

alter table 旧表名 rename as 新表名;--修改表名
alter table 表名 add 属性名 数据类型;--新增属性
alter table 表名 modify 属性名 数据类型;--修改数据类型
alter table 表名 change 属性名 属性名 数据类型;--重命名
alter table 表名 drop age1;--删除属性
drop table [if exists] 表名;-删除表

数值

• 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

时间日期

• date YYYY-MM-DD

• time HH:mm:ss

• datetime YYYY-MM-DD HH:mm:ss

• timestamp 时间戳 1970.1.1到现在的毫秒数

• year 年份

字段属性

• unsigned 无符号的整数
• zerofill 不足的位数用0填充
• auto increment 自增 默认在上一次记录上加一（经常主键）
• notnull 非空
• default 默认值

阿里巴巴标准

• id 主键
• version 乐观锁
• is_delete 伪删除
• gmt_create 创建时间
• gmt_update 修改时间

创建数据库

create table if not exists studen(
    id int(4) not null auto_increment comment '学号',
    name varchar(20) not null default '匿名' comment '姓名',
    pwd varchar(20) not null default '123456' comment '密码',
    sex varchar(2) not null default '男' comment '性别',
    birthday datetime default null comment '生日',
    address varchar(100) default null comment '住址',
    email varchar(50) default null comment '邮箱',
   	primary key (id)
)engine=innodb default charset=utf8

show create database 数据库名;--显示数据库的定义语句
show create table 表名;--显示数据表的定义语句
desc 表名;--显示数据表内容

数据库引擎

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

常规使用操作

• MYISAM 节约空间 速度较快
• INNODB 安全性高 事务的处理 多表多用户的操作

引擎区别

• MYISAM

  • *.frm 表结构定义文件
  • *.MYD 数据文件data
  • *.MYI 索引文件index

• INNODB 只有一个*.frm文件以及上级目录下的ibdata1文件

字符编码

charset=utf-8 支持中文

数据管理

外键

定义：

1. 定义外键 primary key(id)
2. 添加约束 key ‘FK_gradeid’ (gradeid)
3. 引用外键 constraint FK_gradeid foreign key (gradeid) references grade(gradeid)

增加：

1. alter table student add constraint FK_gradeid foreign key(gradeid) references grade (gradeid)

alter table 表名 add constraint 约束名 foreign key(外键属性) references 表名(属性名)

阿里巴巴：不得使用外键与级联 一切外键概念必须在应用层解决

数据库管理语言

• insert

  insert into 表名 ([字段1,字段2,…]) values(‘值1‘,’值2‘,……)

  insert into grade(gradename) value(‘garde1’)

  如果主键自增 可以省略主键

  不写字段则默认插入所有字段

  插入多个：insert into 表名 ([字段1,字段2,…]) values(‘值1‘,’值2‘,……),(‘值1’,‘值2’…)

  一般写代码都一一对应

• update

  update 表名 set 属性名 = ’需改的属性‘ where 条件

  update student set name = ‘asd’ where id =1

  多个属性

  update 表名 set 属性名 = ’需改的属性‘ ,属性名=’需改的属性’,… where 属性名 = ’属性值‘

• delete

  delete from 表名 where 条件

  truncate 表名 清空某一张表

  区别 truncate重新设置 自增列 计数器归零 不会影响事务

• select

  select * from 表 //查询表中所有的数据

  select 属性名,属性名… from 表 where 条件

  别名 select 属性名 as 别名 from 表

  concat(str1,str2)拼接字符串

  distinct 属性名 //去重

  select studentno,studenresult+1 as ‘提分后’ from result

  where studentname like ‘刘%’//查询姓刘的同学

  where studentname like ‘刘_’//查询姓刘的同学 名字后面有1个字的

  where studentname like ‘刘__’//查询姓刘的同学 名字后面有2个字的

  where studentname like ‘%嘉%’//查询名字在有嘉的

  where studentno in(1001,1002,1003);查询studentno是1001或者1002或者1003

• join

  join 连接的表 on 条件 where 等值查询

  select 属性(不能模棱两可) from 表一 as a inner join 表二 as b where a.属性 = b.属性

  left join和right join要用on

  可以自连接

  操作	描述
  inner join	如果表中至少有一个匹配 就返回行
  left join	会从左表中返回所有的值 即使右表没有匹配
  right join	会从右表中返回所有的值 即使左表没有匹配

  子查询：

  where 属性={

  ​ select 属性 from 表名

  ​ where 条件

  }

运算符

运算符 等于：= 不等于：<> != 在某个范围里between … and …

&&:and ||:or !:Not

is null;is not null

like:a like b 如果a匹配到b则为真

in:a in b 假设a在b中为真

%：0-任意字符

_：一个字符

select [all|distinct]
{*|table.*|[table.field1[as alias1][,table field2[as alias2][,...]]]}
from table_name [as table_alias]
	[left|right|inner join table_name2] --联合查询
	[where ...]--条件
	[group by ...]--指定结果按照哪些属性来分组
	[having]--过滤分组的记录必须满足的次要条件
	[order by field {asc|desc}]--指定查询记录按照一个或多个条件排序
	[limit {[offset,]row_count|row_countOFFSER offset}];--指定查询的记录从哪条到哪条
[]可选{}必选

分页和排序

排序：asc 升序 desc 降序

分页：limit

常用函数

绝对值：abs(n)

向上取整：ceiling(n)

向下取整：floor(n)

随机数：rand() （0~1）

判断符号：sign() -1 0 1

字符串长度：char_length(str)

拼接字符串：concat(str1,str2,str3…)

插入：insert(str,begin,length,in_str)

替换：replace(str,str1,str2) str中的str1换成str2

转大写：upper(str)

转小写：lower(str)

反转：reverse(str)

当前日期：current_date()

获取当前时间：now()

本地时间：localtime()

系统时间：sysdate()

系统用户：system_user()

版本：version()

聚合函数

函数名	作用
sum()	求和
avg()	平均值
count()	计数
max()	最大值
min()	最小值
…	…

select count(属性名) from 表名 //表中属性非null属性计数

select count(*) from 表名 //不会忽略null值

select count(1) from 表名 //不会忽略null值

select sum(score) as 总和 from 表

select avg(score) as 平均分 from 表

select max(score) as 最高分 from 表

select min(score) as 最低分 from 表

查询不同课程的平均分 最高分 最低分

select subjectname, avg(studentresult) as a, max(studentresult),min(studentresult)

from result r

inner join subject sub

on r.subjectno = sub.subjectno

group by r.subjectno

having a>=80

MD5加密

不可逆性

md5是个函数

pwd=md5(pwd);

事务

要么都成功 要么都失败

将一组sql放在一个批次中执行

ACID

原子性：要么都成功要么都失败

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

隔离性：不可相互干扰 并发

持久性：事务提交后不可再逆

脏读：相当于没锁

不可重复读：多次读取数据不同

幻读：读取前后数据不一致

set autocommit=0 //关闭
set autocommit=1 //开启（默认）

set autocommit=0 --关闭自动提交

start transaction --开启事务

sql语句1

sql语句2

...

commit--提交（成功）

rollback--回滚（失败）

set autocommit=1 --开启事务提交

savepoint 保存点名--保存点
rollback to savepoint 保存点名--回滚到保存点
release savepoint 保存点名--撤销保存点

索引

通过索引可以更快获取数据结构

没加索引就是遍历 添加索引就是二分

原则：

• 索引不是越多越好

• 不要对进程变动数据加索引

• 主键索引 primary key

• 唯一索引 unique key

• 常规索引 key/index

• 全文索引 fulltext

show index from 表--显示所有索引信息
alter table 表名.属性 add fulltext index '属性名'('属性名');
//id_ 表名 _字段名
create index id on app_user(属性名)

//插入100万个数据
delimiter $$ --写函数之前必须写
create function mock_data()
returns int
begin
	declare num int default 1000000;
	declare i int default 1;
	
	while i<=num do
		insert into student('name','age')
		values (i,floor(rand()*100));
		set i = i+1;
	end while
end;

权限和备份

CREATE USER dog@'localhost' IDENTIFIED BY '123456';--创建用户
GRANT SELECT, INSERT ON test.user TO 'pig'@'localhost'[with grant opition];--给localhost上的pig用户授权test数据库中user表的select和insert权限 all是给所有的权限 除了创建新用户和授权
revoke SELECT, INSERT ON test.user from 'pig'@'localhost'[with grant opition];--撤销权限
set password = password('123456');--修改当前用户密码
set password for name1 = password('123456');--给user修改密码
rename user name1 to name2;--重命名
show grants for name1;--查看某人权限
drop user dog;--删除用户

备份方式：

• 拷data里面的数据

• 手动导出

• 命令行导出

  mysqldump -hlocalhost -uroot -p123456 test student >C:/a.sql
  多张表 student teacher ... 数据库 不带表名
  

三大范式

• 1nf：要求数据库的每个属性都是不可再分割的数据
• 2nf：每张表只做一件事情 完全依赖于主键
• 3nf：每一个数据必须与主键直接相关 不能间接相关

阿里巴巴：关联查询的表不得超过三张表

JDBC

数据库驱动：JDBC接口 java.sql javax.sql包

导入mysql-connector.jar

add as library

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Test {
    public static void main(String[] args) throws Exception{
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.连接信息
        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC";//支持中文编码 设置字符集 安全连接
        String username="root";
        String password="123456";
        //3.连接成功
        Connection connection = DriverManager.getConnection(url, username, password);//代表数据库
        //4.执行sql对象
        Statement statement = connection.createStatement();//代表数据库的console
        String sql="select * from student";
        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("age:"+resultSet.getObject("age"));
            System.out.println("id_teacher:"+resultSet.getObject("id_teacher"));
        }
        //5.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

url：jdbc:mysql://localhost:3306/test 地址

mysql：协议://主机地址:端口号/数据库名&参数1&参数2…

oracle：jdbc:oracle:think:@localhost:1512:sid

connect：代表数据库

statement：代表数据库的console

executeQuery() 返回结果集

execute() 返回是否成功

executeUpdate() 返回受影响的行数

获取数据

getObject() //不知道的话使用这个

getString()

getInt()

getDouble()

getDate()

指针

resultSet.next()下一个

resultSet.pervious()上一个

resultSet.beforeFirst()最前

resultSet.afterLast()最后

resultSet.absolute(row)移动到指定行

一定要释放资源

Statement对象

db.properties 数据库工具类

//封装工具类
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
    static {
        try{
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);
            driver=properties.getProperty("driver");
            url=properties.getProperty("url");
            username=properties.getProperty("username");
            password=properties.getProperty("password");
            Class.forName(driver);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection()throws Exception{
        return DriverManager.getConnection(url, username, password);
    }
    //释放资源
    public static void release(Connection conn,Statement st,ResultSet rs){
        if(rs!=null){
            try{
                rs.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        if(st!=null){
            try{
                st.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try{
                conn.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }
}

public class Test {
    public static void main(String[] args) throws Exception{
        Connection conn = null;
        PreparedStatement st =null;
        ResultSet rs=null;
        try{
            conn = JdbcUtils.getConnection();
            String sql="insert into teacher(id,name,age) value(?,?,?);";
            st=conn.prepareStatement(sql);
            st.setInt(1,3);
            st.setString(2,"张三爷");
            st.setInt(3,30);
            st.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
        }
    }
}

src下的db.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC
username=root
password=123456

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

PreparaStatement可以防止sql注入 并且不用被创建

import java.sql.*;

public class Test {
    public static void main(String[] args) throws Exception{
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.连接信息
        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC";//支持中文编码 设置字符集 安全连接
        String username="root";
        String password="123456";
        //3.连接成功
        Connection connection = DriverManager.getConnection(url, username, password);//代表数据库
        //4.执行sql对象
        String sql="insert into student (id,name,age,id_teacher) value (?,?,?,?);";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);//预编译
        //手动赋值
        preparedStatement.setInt(1,7);
        preparedStatement.setString(2,"小王");
        preparedStatement.setInt(3,11);
        preparedStatement.setInt(4,1);
        int num=preparedStatement.executeUpdate();
        System.out.println(num);
        //5.释放连接
        preparedStatement.close();
        connection.close();
    }
}

JAVA事务

try{
    conn = JdbcUtils.getConnection();
    //开启事务
    conn.setAutoCommit(false);
    String sql1 = "update account set money= money -100 where name="A";
    st = conn.preparaStatement(sql1);
    st.executeUpdate();
    String sql2 = "update account set money= money +100 where name="B";
    st = conn.preparaStatement(sql1);
    st.executeUpdate();
    
    conn.commit();
}catch (Exception e){
    /*try{
        conn.rollback();
    }catch(Exception e1){
        e1.printStackTrace();
    }*/
 	//不写也可以自动回滚 隐式定义
    e.printStakTrace();
}finally{
    JbdcUtils.release(conn,st,rs);
}

数据库连接池

最小连接数&最大连接数&等待超时

编写连接池 写一个接口DataSource

DBCP C3P0 Druid

使用了这些数据库连接池后 就不需要手动写了